================================================================================
PROJECT FULL SOURCE DUMP — AvaloniaApplication1
В этом файле собран весь исходный код проекта на момент создания.
Не включено: папки bin/, obj/ и прочие артефакты сборки.
================================================================================


--------------------------------------------------------------------------------
FILE: Packages.txt
--------------------------------------------------------------------------------
Avalonia, Avalonia.Desktop, Avalonia.Themes.Fluent — одна версия.
Npgsql — PostgreSQL.

Строка подключения: services/DbService.cs
SQL-скрипт: Database/schema.sql


--------------------------------------------------------------------------------
FILE: AvaloniaApplication1.sln
--------------------------------------------------------------------------------
Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio Version 17
VisualStudioVersion = 17.5.2.0
MinimumVisualStudioVersion = 10.0.40219.1
Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "AvaloniaApplication1", "AvaloniaApplication1.csproj", "{4478C37E-E173-8721-0BB7-2A97BAB6EC35}"
EndProject
Global
	GlobalSection(SolutionConfigurationPlatforms) = preSolution
		Debug|Any CPU = Debug|Any CPU
		Release|Any CPU = Release|Any CPU
	EndGlobalSection
	GlobalSection(ProjectConfigurationPlatforms) = postSolution
		{4478C37E-E173-8721-0BB7-2A97BAB6EC35}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
		{4478C37E-E173-8721-0BB7-2A97BAB6EC35}.Debug|Any CPU.Build.0 = Debug|Any CPU
		{4478C37E-E173-8721-0BB7-2A97BAB6EC35}.Release|Any CPU.ActiveCfg = Release|Any CPU
		{4478C37E-E173-8721-0BB7-2A97BAB6EC35}.Release|Any CPU.Build.0 = Release|Any CPU
	EndGlobalSection
	GlobalSection(SolutionProperties) = preSolution
		HideSolutionNode = FALSE
	EndGlobalSection
	GlobalSection(ExtensibilityGlobals) = postSolution
		SolutionGuid = {0BC0D786-F44D-4044-B8DF-051712874244}
	EndGlobalSection
EndGlobal


--------------------------------------------------------------------------------
FILE: AvaloniaApplication1.csproj
--------------------------------------------------------------------------------
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>WinExe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ApplicationManifest>app.manifest</ApplicationManifest>
    <RootNamespace>AvaloniaApplication1</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Avalonia" Version="11.3.11" />
    <PackageReference Include="Avalonia.Desktop" Version="11.3.11" />
    <PackageReference Include="Avalonia.Themes.Fluent" Version="11.3.11" />
    <PackageReference Include="Npgsql" Version="8.0.5" />
  </ItemGroup>
</Project>


--------------------------------------------------------------------------------
FILE: app.manifest
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<assembly manifestVersion="1.0" xmlns="urn:schemas-microsoft-com:asm.v1">
  <!-- This manifest is used on Windows only.
       Don't remove it as it might cause problems with window transparency and embedded controls.
       For more details visit https://learn.microsoft.com/en-us/windows/win32/sbscs/application-manifests -->
  <assemblyIdentity version="1.0.0.0" name="AvaloniaApplication1.Desktop"/>

  <compatibility xmlns="urn:schemas-microsoft-com:compatibility.v1">
    <application>
      <!-- A list of the Windows versions that this application has been tested on
           and is designed to work with. Uncomment the appropriate elements
           and Windows will automatically select the most compatible environment. -->

      <!-- Windows 10 -->
      <supportedOS Id="{8e0f7a12-bfb3-4fe8-b9a5-48fd50a15a9a}" />
    </application>
  </compatibility>
</assembly>


--------------------------------------------------------------------------------
FILE: Program.cs
--------------------------------------------------------------------------------
using System;
using Avalonia;

namespace AvaloniaApplication1;

internal static class Program
{
    [STAThread]
    public static void Main(string[] args) => BuildAvaloniaApp()
        .StartWithClassicDesktopLifetime(args);

    public static AppBuilder BuildAvaloniaApp()
        => AppBuilder.Configure<App>()
            .UsePlatformDetect()
            .LogToTrace();
}


--------------------------------------------------------------------------------
FILE: App.axaml
--------------------------------------------------------------------------------
<Application xmlns="https://github.com/avaloniaui"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             x:Class="AvaloniaApplication1.App"
             RequestedThemeVariant="Default">
  <Application.Styles>
    <FluentTheme />
  </Application.Styles>
</Application>


--------------------------------------------------------------------------------
FILE: App.axaml.cs
--------------------------------------------------------------------------------
using Avalonia;
using Avalonia.Controls.ApplicationLifetimes;
using Avalonia.Markup.Xaml;
using AvaloniaApplication1.Views;

namespace AvaloniaApplication1;

public partial class App : Application
{
    public override void Initialize()
    {
        AvaloniaXamlLoader.Load(this);
    }

    public override void OnFrameworkInitializationCompleted()
    {
        if (ApplicationLifetime is IClassicDesktopStyleApplicationLifetime desktop)
            desktop.MainWindow = new LoginWindow();

        base.OnFrameworkInitializationCompleted();
    }
}


--------------------------------------------------------------------------------
FILE: Models/User.cs
--------------------------------------------------------------------------------
namespace AvaloniaApplication1.Models;

public sealed class User
{
    public int Id { get; set; }
    public string Login { get; set; } = "";
    public string Password { get; set; } = "";
    public string Role { get; set; } = "";
    public string FullName { get; set; } = "";
}


--------------------------------------------------------------------------------
FILE: Models/Product.cs
--------------------------------------------------------------------------------
namespace AvaloniaApplication1.Models;

public sealed class Product
{
    public int ProductId { get; set; }
    public string Article { get; set; } = "";
    public int CategoryId { get; set; }
    public int SupplierId { get; set; }
    public int TypeProductId { get; set; }
    public decimal Price { get; set; }
    public string Discount { get; set; } = "0";
    public string Description { get; set; } = "";
    public string Photo { get; set; } = "";

    public decimal FinalPrice
    {
        get
        {
            if (!decimal.TryParse(Discount.Replace("%", ""), out var discountValue))
            {
                discountValue = 0;
            }

            return Price * (100m - discountValue) / 100m;
        }
    }
}


--------------------------------------------------------------------------------
FILE: Models/Order.cs
--------------------------------------------------------------------------------
namespace AvaloniaApplication1.Models;

public sealed class Order
{
    public int OrderId { get; set; }
    public string OrderArticle { get; set; } = "";
    public string DateOrder { get; set; } = "";
    public string DateDelivery { get; set; } = "";
    public int PickUpPointId { get; set; }
    public string Code { get; set; } = "";
    public string StatusOrder { get; set; } = "Новый";
    public int UserId { get; set; }
    public decimal TotalSum { get; set; }
}


--------------------------------------------------------------------------------
FILE: services/DbService.cs
--------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using AvaloniaApplication1.Models;
using Npgsql;

namespace AvaloniaApplication1.Services;

public sealed class DbService
{
    private readonly string _connectionString =
        "Host=localhost;Port=5432;Username=postgres;Password=postgres;Database=exam_db";

    public User? Login(string login, string password)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();

        using var cmd = new NpgsqlCommand(
            """
            SELECT u.user_id,
                   u.login,
                   u.password,
                   COALESCE(r.name_role, 'client') AS role_name,
                   TRIM(COALESCE(u.name, '') || ' ' || COALESCE(u.second_name, '') || ' ' || COALESCE(u.patronymic, '')) AS full_name
            FROM users u
            LEFT JOIN roles r ON r.role_id = u.role_id
            WHERE u.login = @l AND u.password = @p
            """,
            conn);

        cmd.Parameters.AddWithValue("l", login);
        cmd.Parameters.AddWithValue("p", password);

        using var reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            return new User
            {
                Id = reader.GetInt32(0),
                Login = reader.GetString(1),
                Password = reader.GetString(2),
                Role = NormalizeRole(reader.GetString(3)),
                FullName = reader.GetString(4)
            };
        }

        return null;
    }

    public List<Product> GetProducts()
    {
        var list = new List<Product>();

        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();

        using var cmd = new NpgsqlCommand(
            """
            SELECT product_id, article, category_id, supplier_id, type_products_id, price, discount, description, photo
            FROM products
            ORDER BY product_id
            """,
            conn);

        using var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(new Product
            {
                ProductId = reader.GetInt32(0),
                Article = reader.GetString(1),
                CategoryId = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
                SupplierId = reader.IsDBNull(3) ? 0 : reader.GetInt32(3),
                TypeProductId = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                Price = reader.GetDecimal(5),
                Discount = reader.IsDBNull(6) ? "0" : reader.GetString(6),
                Description = reader.IsDBNull(7) ? "" : reader.GetString(7),
                Photo = reader.IsDBNull(8) ? "" : reader.GetString(8)
            });
        }

        return list;
    }

    public void AddProduct(Product product)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand(
            """
            INSERT INTO products (article, category_id, supplier_id, type_products_id, price, discount, description, photo)
            VALUES (@article, @categoryId, @supplierId, @typeId, @price, @discount, @description, @photo)
            """,
            conn);
        FillProductParameters(cmd, product);
        cmd.ExecuteNonQuery();
    }

    public void UpdateProduct(Product product)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand(
            """
            UPDATE products
            SET article = @article,
                category_id = @categoryId,
                supplier_id = @supplierId,
                type_products_id = @typeId,
                price = @price,
                discount = @discount,
                description = @description,
                photo = @photo
            WHERE product_id = @id
            """,
            conn);
        FillProductParameters(cmd, product);
        cmd.Parameters.AddWithValue("id", product.ProductId);
        cmd.ExecuteNonQuery();
    }

    public void DeleteProduct(int productId)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand("DELETE FROM products WHERE product_id = @id", conn);
        cmd.Parameters.AddWithValue("id", productId);
        cmd.ExecuteNonQuery();
    }

    public List<Order> GetOrders()
    {
        var list = new List<Order>();
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand(
            """
            SELECT order_id, order_article, date_order, date_delivery, pick_up_point_id, code, status_order, user_id, total_sum
            FROM orders
            ORDER BY order_id
            """,
            conn);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            list.Add(new Order
            {
                OrderId = reader.GetInt32(0),
                OrderArticle = reader.IsDBNull(1) ? "" : reader.GetString(1),
                DateOrder = reader.IsDBNull(2) ? "" : reader.GetDateTime(2).ToString("yyyy-MM-dd"),
                DateDelivery = reader.IsDBNull(3) ? "" : reader.GetDateTime(3).ToString("yyyy-MM-dd"),
                PickUpPointId = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                Code = reader.IsDBNull(5) ? "" : reader.GetString(5),
                StatusOrder = reader.IsDBNull(6) ? "Новый" : reader.GetString(6),
                UserId = reader.IsDBNull(7) ? 0 : reader.GetInt32(7),
                TotalSum = reader.IsDBNull(8) ? 0m : reader.GetDecimal(8)
            });
        }

        return list;
    }

    public void AddOrder(Order order)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand(
            """
            INSERT INTO orders (order_article, date_order, date_delivery, pick_up_point_id, code, status_order, user_id, total_sum)
            VALUES (@article, @dateOrder, @dateDelivery, @pickUpPointId, @code, @status, @userId, @totalSum)
            """,
            conn);
        FillOrderParameters(cmd, order);
        cmd.ExecuteNonQuery();
    }

    public void UpdateOrder(Order order)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand(
            """
            UPDATE orders
            SET order_article = @article,
                date_order = @dateOrder,
                date_delivery = @dateDelivery,
                pick_up_point_id = @pickUpPointId,
                code = @code,
                status_order = @status,
                user_id = @userId,
                total_sum = @totalSum
            WHERE order_id = @id
            """,
            conn);
        FillOrderParameters(cmd, order);
        cmd.Parameters.AddWithValue("id", order.OrderId);
        cmd.ExecuteNonQuery();
    }

    public void DeleteOrder(int orderId)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        conn.Open();
        using var cmd = new NpgsqlCommand("DELETE FROM orders WHERE order_id = @id", conn);
        cmd.Parameters.AddWithValue("id", orderId);
        cmd.ExecuteNonQuery();
    }

    private static void FillProductParameters(NpgsqlCommand cmd, Product product)
    {
        cmd.Parameters.AddWithValue("article", product.Article);
        cmd.Parameters.AddWithValue("categoryId", product.CategoryId == 0 ? DBNull.Value : product.CategoryId);
        cmd.Parameters.AddWithValue("supplierId", product.SupplierId == 0 ? DBNull.Value : product.SupplierId);
        cmd.Parameters.AddWithValue("typeId", product.TypeProductId == 0 ? DBNull.Value : product.TypeProductId);
        cmd.Parameters.AddWithValue("price", product.Price);
        cmd.Parameters.AddWithValue("discount", product.Discount);
        cmd.Parameters.AddWithValue("description", string.IsNullOrWhiteSpace(product.Description) ? DBNull.Value : product.Description);
        cmd.Parameters.AddWithValue("photo", string.IsNullOrWhiteSpace(product.Photo) ? DBNull.Value : product.Photo);
    }

    private static void FillOrderParameters(NpgsqlCommand cmd, Order order)
    {
        cmd.Parameters.AddWithValue("article", string.IsNullOrWhiteSpace(order.OrderArticle) ? DBNull.Value : order.OrderArticle);
        cmd.Parameters.AddWithValue("dateOrder", ParseDateOrDbNull(order.DateOrder));
        cmd.Parameters.AddWithValue("dateDelivery", ParseDateOrDbNull(order.DateDelivery));
        cmd.Parameters.AddWithValue("pickUpPointId", order.PickUpPointId == 0 ? DBNull.Value : order.PickUpPointId);
        cmd.Parameters.AddWithValue("code", string.IsNullOrWhiteSpace(order.Code) ? DBNull.Value : order.Code);
        cmd.Parameters.AddWithValue("status", string.IsNullOrWhiteSpace(order.StatusOrder) ? "Новый" : order.StatusOrder);
        cmd.Parameters.AddWithValue("userId", order.UserId == 0 ? DBNull.Value : order.UserId);
        cmd.Parameters.AddWithValue("totalSum", order.TotalSum);
    }

    private static object ParseDateOrDbNull(string date)
    {
        return DateTime.TryParse(date, out var value) ? value : DBNull.Value;
    }

    private static string NormalizeRole(string role)
    {
        var value = role.Trim().ToLowerInvariant();
        return value switch
        {
            "админ" => "admin",
            "администратор" => "admin",
            "manager" => "manager",
            "менеджер" => "manager",
            "client" => "client",
            "клиент" => "client",
            _ => value
        };
    }
}


--------------------------------------------------------------------------------
FILE: services/bdService.cs
--------------------------------------------------------------------------------
using System.Collections.Generic;
using AvaloniaApplication1.Models;
using Npgsql;

namespace AvaloniaApplication.service
{
    
}


--------------------------------------------------------------------------------
FILE: Views/LoginWindow.axaml
--------------------------------------------------------------------------------
<Window xmlns="https://github.com/avaloniaui"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="AvaloniaApplication1.Views.LoginWindow"
        Width="420" Height="300"
        Title="Авторизация">
  
  <StackPanel Margin="20" Spacing="12">
    
    <TextBlock Text="Вход в систему" 
               FontSize="20" 
               FontWeight="Bold" 
               HorizontalAlignment="Center"/>
    
    <TextBox x:Name="loginBox" 
             Watermark="Логин"/>
    
    <TextBox x:Name="passwordBox"
             PasswordChar="*" 
             Watermark="Пароль"/>
    
    <TextBlock x:Name="errorText" 
               Foreground="Red" 
               TextWrapping="Wrap"/>
    
    <Button Content="Войти" 
            Click="Login_Click"
            HorizontalAlignment="Stretch"/>
    
    <Button Content="Войти как гость" 
            Click="Guest_Click"
            HorizontalAlignment="Stretch"/>
    
  </StackPanel>
  
</Window>


--------------------------------------------------------------------------------
FILE: Views/LoginWindow.axaml.cs
--------------------------------------------------------------------------------
using System;
using Avalonia;
using Avalonia.Controls;
using Avalonia.Controls.ApplicationLifetimes;
using Avalonia.Interactivity;
using AvaloniaApplication1.Models;
using AvaloniaApplication1.Services;

namespace AvaloniaApplication1.Views;

public partial class LoginWindow : Window
{
    private readonly DbService _dbService = new();

    public LoginWindow()
    {
        InitializeComponent();
    }

    private void Login_Click(object? sender, RoutedEventArgs e)
    {
        errorText.Text = string.Empty;

        var login = loginBox.Text?.Trim() ?? string.Empty;
        var password = passwordBox.Text?.Trim() ?? string.Empty;

        if (string.IsNullOrWhiteSpace(login) || string.IsNullOrWhiteSpace(password))
        {
            errorText.Text = "Введите логин и пароль.";
            return;
        }

        try
        {
            var user = _dbService.Login(login, password);
            if (user is null)
            {
                errorText.Text = "Неверный логин или пароль.";
                return;
            }

            OpenRoleMenu(user);
        }
        catch (Exception ex)
        {
            errorText.Text = $"Ошибка подключения к БД: {ex.Message}";
        }
    }

    private void Guest_Click(object? sender, RoutedEventArgs e)
    {
        errorText.Text = string.Empty;
        OpenRoleMenu(new User
        {
            Login = "guest",
            FullName = "Гость",
            Role = "client"
        });
    }

    private void OpenRoleMenu(User user)
    {
        var roleWindow = new RoleWindow(user, _dbService);
        if (Application.Current?.ApplicationLifetime is IClassicDesktopStyleApplicationLifetime desktop)
        {
            desktop.MainWindow = roleWindow;
        }

        roleWindow.Show();
        Close();
    }
}


--------------------------------------------------------------------------------
FILE: Views/RoleWindow.axaml
--------------------------------------------------------------------------------
<Window xmlns="https://github.com/avaloniaui"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="AvaloniaApplication1.Views.RoleWindow"
        Width="450" Height="260"
        Title="Главное меню">
  <StackPanel Margin="20" Spacing="12">
    <TextBlock x:Name="helloText"
               FontSize="18"
               FontWeight="Bold"/>

    <Button Content="Просмотр товаров"
            Click="OpenProducts_Click"
            HorizontalAlignment="Stretch"/>

    <Button x:Name="ordersButton"
            Content="Просмотр заказов"
            Click="OpenOrders_Click"
            HorizontalAlignment="Stretch"
            IsVisible="False"/>

    <Button Content="Выход"
            Click="Exit_Click"
            HorizontalAlignment="Stretch"/>
  </StackPanel>
</Window>


--------------------------------------------------------------------------------
FILE: Views/RoleWindow.axaml.cs
--------------------------------------------------------------------------------
using Avalonia;
using Avalonia.Controls;
using Avalonia.Controls.ApplicationLifetimes;
using Avalonia.Interactivity;
using AvaloniaApplication1.Models;
using AvaloniaApplication1.Services;

namespace AvaloniaApplication1.Views;

public partial class RoleWindow : Window
{
    private readonly User _user;
    private readonly DbService _dbService;

    public RoleWindow(User user, DbService dbService)
    {
        _user = user;
        _dbService = dbService;
        InitializeComponent();

        helloText.Text = $"Пользователь: {(_user.FullName.Trim() == string.Empty ? _user.Login : _user.FullName)}";
        ordersButton.IsVisible = IsManagerOrAdmin(_user.Role);
    }

    private async void OpenProducts_Click(object? sender, RoutedEventArgs e)
    {
        await new ProductsWindow(_user, _dbService).ShowDialog(this);
    }

    private async void OpenOrders_Click(object? sender, RoutedEventArgs e)
    {
        await new OrdersWindow(_user, _dbService).ShowDialog(this);
    }

    private void Exit_Click(object? sender, RoutedEventArgs e)
    {
        var login = new LoginWindow();
        if (Application.Current?.ApplicationLifetime is IClassicDesktopStyleApplicationLifetime desktop)
        {
            desktop.MainWindow = login;
        }

        login.Show();
        Close();
    }

    private static bool IsManagerOrAdmin(string role)
    {
        return role is "manager" or "admin";
    }
}


--------------------------------------------------------------------------------
FILE: Views/ProductsWindow.axaml
--------------------------------------------------------------------------------
<Window xmlns="https://github.com/avaloniaui"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="AvaloniaApplication1.Views.ProductsWindow"
        Width="1000" Height="650"
        Title="Товары">
  <Grid RowDefinitions="Auto,*,Auto,Auto" Margin="12">
    <TextBlock x:Name="titleText"
               FontSize="18"
               FontWeight="Bold"
               Margin="0,0,0,10"/>

    <ScrollViewer Grid.Row="1">
      <ItemsControl x:Name="productsItemsControl">
        <ItemsControl.ItemsPanel>
          <ItemsPanelTemplate>
            <WrapPanel Orientation="Horizontal"/>
          </ItemsPanelTemplate>
        </ItemsControl.ItemsPanel>
        <ItemsControl.ItemTemplate>
          <DataTemplate>
            <Border BorderBrush="#CCCCCC"
                    BorderThickness="1"
                    CornerRadius="6"
                    Width="240"
                    Margin="6"
                    Padding="10">
              <StackPanel Spacing="6">
                <TextBlock Text="{Binding ProductId, StringFormat='ID: {0}'}" FontWeight="SemiBold"/>
                <TextBlock Text="{Binding Article, StringFormat='Артикул: {0}'}"/>
                <TextBlock Text="{Binding Description}" TextWrapping="Wrap"/>
                <TextBlock Text="{Binding Price, StringFormat='Цена: {0:F2}'}"/>
                <TextBlock Text="{Binding Discount, StringFormat='Скидка: {0}%'}"/>
                <TextBlock Text="{Binding FinalPrice, StringFormat='Итог: {0:F2}'}" FontWeight="Bold"/>
              </StackPanel>
            </Border>
          </DataTemplate>
        </ItemsControl.ItemTemplate>
      </ItemsControl>
    </ScrollViewer>

    <StackPanel Grid.Row="2" x:Name="managePanel" Orientation="Horizontal" Spacing="8" Margin="0,10,0,10" IsVisible="False">
      <Button Content="Добавить товар" Click="AddProduct_Click"/>
      <Button Content="Изменить выбранный товар" Click="EditProduct_Click"/>
      <Button Content="Удалить выбранный товар" Click="DeleteProduct_Click"/>
    </StackPanel>

    <ComboBox Grid.Row="3"
              x:Name="productsComboBox"
              HorizontalAlignment="Stretch"
              DisplayMemberBinding="{Binding Article}"/>
  </Grid>
</Window>


--------------------------------------------------------------------------------
FILE: Views/ProductsWindow.axaml.cs
--------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using Avalonia.Controls;
using Avalonia.Interactivity;
using AvaloniaApplication1.Models;
using AvaloniaApplication1.Services;

namespace AvaloniaApplication1.Views;

public partial class ProductsWindow : Window
{
    private readonly User _user;
    private readonly DbService _dbService;
    private List<Product> _products = [];

    public ProductsWindow(User user, DbService dbService)
    {
        _user = user;
        _dbService = dbService;
        InitializeComponent();

        titleText.Text = $"Товары ({RoleTitle(_user.Role)})";
        managePanel.IsVisible = IsManagerOrAdmin(_user.Role);
        LoadProducts();
    }

    private void LoadProducts()
    {
        try
        {
            _products = _dbService.GetProducts();
            productsItemsControl.ItemsSource = _products;
            productsComboBox.ItemsSource = _products;
            if (_products.Count > 0)
            {
                productsComboBox.SelectedIndex = 0;
            }
        }
        catch (Exception ex)
        {
            _ = ShowError(ex.Message);
        }
    }

    private async void AddProduct_Click(object? sender, RoutedEventArgs e)
    {
        var editWindow = new ProductEditWindow(new Product());
        var result = await editWindow.ShowDialog<Product?>(this);
        if (result is null)
        {
            return;
        }

        try
        {
            _dbService.AddProduct(result);
            LoadProducts();
        }
        catch (Exception ex)
        {
            _ = ShowError(ex.Message);
        }
    }

    private async void EditProduct_Click(object? sender, RoutedEventArgs e)
    {
        if (productsComboBox.SelectedItem is not Product selected)
        {
            return;
        }

        var clone = new Product
        {
            ProductId = selected.ProductId,
            Article = selected.Article,
            CategoryId = selected.CategoryId,
            SupplierId = selected.SupplierId,
            TypeProductId = selected.TypeProductId,
            Price = selected.Price,
            Discount = selected.Discount,
            Description = selected.Description,
            Photo = selected.Photo
        };

        var editWindow = new ProductEditWindow(clone);
        var result = await editWindow.ShowDialog<Product?>(this);
        if (result is null)
        {
            return;
        }

        try
        {
            _dbService.UpdateProduct(result);
            LoadProducts();
        }
        catch (Exception ex)
        {
            await ShowError(ex.Message);
        }
    }

    private async void DeleteProduct_Click(object? sender, RoutedEventArgs e)
    {
        if (productsComboBox.SelectedItem is not Product selected)
        {
            return;
        }

        try
        {
            _dbService.DeleteProduct(selected.ProductId);
            LoadProducts();
        }
        catch (Exception ex)
        {
            await ShowError(ex.Message);
        }
    }

    private async System.Threading.Tasks.Task ShowError(string message)
    {
        var dialog = new Window
        {
            Width = 420,
            Height = 180,
            Title = "Ошибка"
        };
        dialog.Content = new StackPanel
        {
            Margin = new Avalonia.Thickness(16),
            Spacing = 12,
            Children =
            {
                new TextBlock { Text = message, TextWrapping = Avalonia.Media.TextWrapping.Wrap },
                new Button
                {
                    Content = "OK",
                    HorizontalAlignment = Avalonia.Layout.HorizontalAlignment.Right
                }
            }
        };
        ((Button)((StackPanel)dialog.Content).Children[1]).Click += (_, _) => dialog.Close();
        await dialog.ShowDialog(this);
    }

    private static bool IsManagerOrAdmin(string role) => role is "manager" or "admin";

    private static string RoleTitle(string role) => role switch
    {
        "manager" => "менеджер",
        "admin" => "админ",
        _ => "клиент/гость"
    };
}


--------------------------------------------------------------------------------
FILE: Views/ProductEditWindow.axaml
--------------------------------------------------------------------------------
<Window xmlns="https://github.com/avaloniaui"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="AvaloniaApplication1.Views.ProductEditWindow"
        Width="460" Height="520"
        Title="Редактор товара">
  <StackPanel Margin="16" Spacing="8">
    <TextBlock Text="Артикул"/>
    <TextBox x:Name="articleBox"/>
    <TextBlock Text="Категория ID"/>
    <TextBox x:Name="categoryBox"/>
    <TextBlock Text="Поставщик ID"/>
    <TextBox x:Name="supplierBox"/>
    <TextBlock Text="Тип товара ID"/>
    <TextBox x:Name="typeBox"/>
    <TextBlock Text="Цена"/>
    <TextBox x:Name="priceBox"/>
    <TextBlock Text="Скидка (число, например 15)"/>
    <TextBox x:Name="discountBox"/>
    <TextBlock Text="Описание"/>
    <TextBox x:Name="descriptionBox" AcceptsReturn="True" Height="70" TextWrapping="Wrap"/>
    <TextBlock Text="Фото (путь)"/>
    <TextBox x:Name="photoBox"/>

    <StackPanel Orientation="Horizontal" Spacing="8" HorizontalAlignment="Right">
      <Button Content="Сохранить" Click="Save_Click"/>
      <Button Content="Отмена" Click="Cancel_Click"/>
    </StackPanel>
  </StackPanel>
</Window>


--------------------------------------------------------------------------------
FILE: Views/ProductEditWindow.axaml.cs
--------------------------------------------------------------------------------
using System.Globalization;
using Avalonia.Controls;
using Avalonia.Interactivity;
using AvaloniaApplication1.Models;

namespace AvaloniaApplication1.Views;

public partial class ProductEditWindow : Window
{
    private readonly Product _source;

    public ProductEditWindow(Product product)
    {
        _source = product;
        InitializeComponent();
        Fill();
    }

    private void Fill()
    {
        articleBox.Text = _source.Article;
        categoryBox.Text = _source.CategoryId.ToString();
        supplierBox.Text = _source.SupplierId.ToString();
        typeBox.Text = _source.TypeProductId.ToString();
        priceBox.Text = _source.Price.ToString(CultureInfo.InvariantCulture);
        discountBox.Text = _source.Discount;
        descriptionBox.Text = _source.Description;
        photoBox.Text = _source.Photo;
    }

    private void Save_Click(object? sender, RoutedEventArgs e)
    {
        if (string.IsNullOrWhiteSpace(articleBox.Text))
        {
            return;
        }

        if (!decimal.TryParse(priceBox.Text, NumberStyles.Any, CultureInfo.InvariantCulture, out var price))
        {
            return;
        }

        var result = new Product
        {
            ProductId = _source.ProductId,
            Article = articleBox.Text.Trim(),
            CategoryId = ParseInt(categoryBox.Text),
            SupplierId = ParseInt(supplierBox.Text),
            TypeProductId = ParseInt(typeBox.Text),
            Price = price,
            Discount = string.IsNullOrWhiteSpace(discountBox.Text) ? "0" : discountBox.Text.Trim(),
            Description = descriptionBox.Text?.Trim() ?? "",
            Photo = photoBox.Text?.Trim() ?? ""
        };
        Close(result);
    }

    private void Cancel_Click(object? sender, RoutedEventArgs e)
    {
        Close(null);
    }

    private static int ParseInt(string? value)
    {
        return int.TryParse(value, out var parsed) ? parsed : 0;
    }
}


--------------------------------------------------------------------------------
FILE: Views/OrdersWindow.axaml
--------------------------------------------------------------------------------
<Window xmlns="https://github.com/avaloniaui"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="AvaloniaApplication1.Views.OrdersWindow"
        Width="980" Height="620"
        Title="Заказы">
  <Grid RowDefinitions="Auto,*,Auto" Margin="12">
    <TextBlock x:Name="titleText" FontSize="18" FontWeight="Bold" Margin="0,0,0,10"/>

    <ListBox Grid.Row="1" x:Name="ordersListBox">
      <ListBox.ItemTemplate>
        <DataTemplate>
          <Border BorderBrush="#CCCCCC" BorderThickness="1" CornerRadius="6" Padding="8" Margin="0,0,0,8">
            <StackPanel Spacing="4">
              <TextBlock Text="{Binding OrderId, StringFormat='ID: {0}'}" FontWeight="Bold"/>
              <TextBlock Text="{Binding OrderArticle, StringFormat='Артикул: {0}'}"/>
              <TextBlock Text="{Binding StatusOrder, StringFormat='Статус: {0}'}"/>
              <TextBlock Text="{Binding TotalSum, StringFormat='Сумма: {0:F2}'}"/>
              <TextBlock Text="{Binding DateOrder, StringFormat='Дата заказа: {0}'}"/>
              <TextBlock Text="{Binding DateDelivery, StringFormat='Дата доставки: {0}'}"/>
            </StackPanel>
          </Border>
        </DataTemplate>
      </ListBox.ItemTemplate>
    </ListBox>

    <StackPanel Grid.Row="2" x:Name="adminActions" Orientation="Horizontal" Spacing="8" Margin="0,10,0,0" IsVisible="False">
      <Button Content="Добавить заказ" Click="AddOrder_Click"/>
      <Button Content="Изменить выбранный заказ" Click="EditOrder_Click"/>
      <Button Content="Удалить выбранный заказ" Click="DeleteOrder_Click"/>
    </StackPanel>
  </Grid>
</Window>


--------------------------------------------------------------------------------
FILE: Views/OrdersWindow.axaml.cs
--------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using Avalonia.Controls;
using Avalonia.Interactivity;
using AvaloniaApplication1.Models;
using AvaloniaApplication1.Services;

namespace AvaloniaApplication1.Views;

public partial class OrdersWindow : Window
{
    private readonly User _user;
    private readonly DbService _dbService;
    private List<Order> _orders = [];

    public OrdersWindow(User user, DbService dbService)
    {
        _user = user;
        _dbService = dbService;
        InitializeComponent();

        titleText.Text = "Панель заказов";
        adminActions.IsVisible = _user.Role == "admin";
        LoadOrders();
    }

    private void LoadOrders()
    {
        try
        {
            _orders = _dbService.GetOrders();
            ordersListBox.ItemsSource = _orders;
        }
        catch (Exception ex)
        {
            _ = ShowError(ex.Message);
        }
    }

    private async void AddOrder_Click(object? sender, RoutedEventArgs e)
    {
        var editor = new OrderEditWindow(new Order());
        var result = await editor.ShowDialog<Order?>(this);
        if (result is null)
        {
            return;
        }

        try
        {
            _dbService.AddOrder(result);
            LoadOrders();
        }
        catch (Exception ex)
        {
            await ShowError(ex.Message);
        }
    }

    private async void EditOrder_Click(object? sender, RoutedEventArgs e)
    {
        if (ordersListBox.SelectedItem is not Order selected)
        {
            return;
        }

        var editor = new OrderEditWindow(new Order
        {
            OrderId = selected.OrderId,
            OrderArticle = selected.OrderArticle,
            DateOrder = selected.DateOrder,
            DateDelivery = selected.DateDelivery,
            PickUpPointId = selected.PickUpPointId,
            Code = selected.Code,
            StatusOrder = selected.StatusOrder,
            UserId = selected.UserId,
            TotalSum = selected.TotalSum
        });
        var result = await editor.ShowDialog<Order?>(this);
        if (result is null)
        {
            return;
        }

        try
        {
            _dbService.UpdateOrder(result);
            LoadOrders();
        }
        catch (Exception ex)
        {
            await ShowError(ex.Message);
        }
    }

    private async void DeleteOrder_Click(object? sender, RoutedEventArgs e)
    {
        if (ordersListBox.SelectedItem is not Order selected)
        {
            return;
        }

        try
        {
            _dbService.DeleteOrder(selected.OrderId);
            LoadOrders();
        }
        catch (Exception ex)
        {
            await ShowError(ex.Message);
        }
    }

    private async System.Threading.Tasks.Task ShowError(string message)
    {
        var dialog = new Window
        {
            Width = 420,
            Height = 180,
            Title = "Ошибка"
        };
        dialog.Content = new StackPanel
        {
            Margin = new Avalonia.Thickness(16),
            Spacing = 12,
            Children =
            {
                new TextBlock { Text = message, TextWrapping = Avalonia.Media.TextWrapping.Wrap },
                new Button
                {
                    Content = "OK",
                    HorizontalAlignment = Avalonia.Layout.HorizontalAlignment.Right
                }
            }
        };
        ((Button)((StackPanel)dialog.Content).Children[1]).Click += (_, _) => dialog.Close();
        await dialog.ShowDialog(this);
    }
}


--------------------------------------------------------------------------------
FILE: Views/OrderEditWindow.axaml
--------------------------------------------------------------------------------
<Window xmlns="https://github.com/avaloniaui"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="AvaloniaApplication1.Views.OrderEditWindow"
        Width="460" Height="520"
        Title="Редактор заказа">
  <StackPanel Margin="16" Spacing="8">
    <TextBlock Text="Артикул заказа"/>
    <TextBox x:Name="articleBox"/>
    <TextBlock Text="Дата заказа (yyyy-MM-dd)"/>
    <TextBox x:Name="dateOrderBox"/>
    <TextBlock Text="Дата доставки (yyyy-MM-dd)"/>
    <TextBox x:Name="dateDeliveryBox"/>
    <TextBlock Text="ID пункта выдачи"/>
    <TextBox x:Name="pickUpBox"/>
    <TextBlock Text="Код"/>
    <TextBox x:Name="codeBox"/>
    <TextBlock Text="Статус"/>
    <TextBox x:Name="statusBox"/>
    <TextBlock Text="ID пользователя"/>
    <TextBox x:Name="userBox"/>
    <TextBlock Text="Сумма"/>
    <TextBox x:Name="sumBox"/>

    <StackPanel Orientation="Horizontal" Spacing="8" HorizontalAlignment="Right">
      <Button Content="Сохранить" Click="Save_Click"/>
      <Button Content="Отмена" Click="Cancel_Click"/>
    </StackPanel>
  </StackPanel>
</Window>


--------------------------------------------------------------------------------
FILE: Views/OrderEditWindow.axaml.cs
--------------------------------------------------------------------------------
using System.Globalization;
using Avalonia.Controls;
using Avalonia.Interactivity;
using AvaloniaApplication1.Models;

namespace AvaloniaApplication1.Views;

public partial class OrderEditWindow : Window
{
    private readonly Order _source;

    public OrderEditWindow(Order order)
    {
        _source = order;
        InitializeComponent();
        Fill();
    }

    private void Fill()
    {
        articleBox.Text = _source.OrderArticle;
        dateOrderBox.Text = _source.DateOrder;
        dateDeliveryBox.Text = _source.DateDelivery;
        pickUpBox.Text = _source.PickUpPointId.ToString();
        codeBox.Text = _source.Code;
        statusBox.Text = _source.StatusOrder;
        userBox.Text = _source.UserId.ToString();
        sumBox.Text = _source.TotalSum.ToString(CultureInfo.InvariantCulture);
    }

    private void Save_Click(object? sender, RoutedEventArgs e)
    {
        if (!decimal.TryParse(sumBox.Text, NumberStyles.Any, CultureInfo.InvariantCulture, out var sum))
        {
            return;
        }

        var result = new Order
        {
            OrderId = _source.OrderId,
            OrderArticle = articleBox.Text?.Trim() ?? "",
            DateOrder = dateOrderBox.Text?.Trim() ?? "",
            DateDelivery = dateDeliveryBox.Text?.Trim() ?? "",
            PickUpPointId = ParseInt(pickUpBox.Text),
            Code = codeBox.Text?.Trim() ?? "",
            StatusOrder = string.IsNullOrWhiteSpace(statusBox.Text) ? "Новый" : statusBox.Text.Trim(),
            UserId = ParseInt(userBox.Text),
            TotalSum = sum
        };
        Close(result);
    }

    private void Cancel_Click(object? sender, RoutedEventArgs e)
    {
        Close(null);
    }

    private static int ParseInt(string? value)
    {
        return int.TryParse(value, out var parsed) ? parsed : 0;
    }
}

================================================================================
ОБНОВЛЕНИЕ: АКТУАЛЬНЫЙ DbService.cs
================================================================================

--------------------------------------------------------------------------------
FILE: services/DbService.cs (UPDATED)
--------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using AvaloniaApplication1.Models;
using Npgsql;

namespace AvaloniaApplication1.Services;

public sealed class DbService
{
    private readonly string[] _connectionCandidates =
    [
        "Host=localhost;Port=5432;Username=postgres;Password=123;Database=321"
    ];

    public User? Login(string login, string password)
    {
        using var conn = OpenConnection();

        using var cmd = new NpgsqlCommand(
            """
            SELECT u.user_id,
                   u.login,
                   u.password,
                   COALESCE(r.name_role, 'client') AS role_name,
                   TRIM(COALESCE(u.name, '') || ' ' || COALESCE(u.second_name, '') || ' ' || COALESCE(u.patronymic, '')) AS full_name
            FROM users u
            LEFT JOIN roles r ON r.role_id = u.role_id
            WHERE u.login = @l AND u.password = @p
            """,
            conn);

        cmd.Parameters.AddWithValue("l", login);
        cmd.Parameters.AddWithValue("p", password);

        using var reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            return new User
            {
                Id = reader.GetInt32(0),
                Login = reader.GetString(1),
                Password = reader.GetString(2),
                Role = NormalizeRole(reader.GetString(3)),
                FullName = reader.GetString(4)
            };
        }

        return null;
    }

    public List<Product> GetProducts()
    {
        var list = new List<Product>();

        using var conn = OpenConnection();

        using var cmd = new NpgsqlCommand(
            """
            SELECT product_id, article, category_id, supplier_id, type_products_id, price, discount, description, photo
            FROM products
            ORDER BY product_id
            """,
            conn);

        using var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(new Product
            {
                ProductId = reader.GetInt32(0),
                Article = reader.GetString(1),
                CategoryId = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
                SupplierId = reader.IsDBNull(3) ? 0 : reader.GetInt32(3),
                TypeProductId = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                Price = reader.GetDecimal(5),
                Discount = reader.IsDBNull(6) ? "0" : reader.GetString(6),
                Description = reader.IsDBNull(7) ? "" : reader.GetString(7),
                Photo = reader.IsDBNull(8) ? "" : reader.GetString(8)
            });
        }

        return list;
    }

    public void AddProduct(Product product)
    {
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand(
            """
            INSERT INTO products (article, category_id, supplier_id, type_products_id, price, discount, description, photo)
            VALUES (@article, @categoryId, @supplierId, @typeId, @price, @discount, @description, @photo)
            """,
            conn);
        FillProductParameters(cmd, product);
        cmd.ExecuteNonQuery();
    }

    public void UpdateProduct(Product product)
    {
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand(
            """
            UPDATE products
            SET article = @article,
                category_id = @categoryId,
                supplier_id = @supplierId,
                type_products_id = @typeId,
                price = @price,
                discount = @discount,
                description = @description,
                photo = @photo
            WHERE product_id = @id
            """,
            conn);
        FillProductParameters(cmd, product);
        cmd.Parameters.AddWithValue("id", product.ProductId);
        cmd.ExecuteNonQuery();
    }

    public void DeleteProduct(int productId)
    {
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand("DELETE FROM products WHERE product_id = @id", conn);
        cmd.Parameters.AddWithValue("id", productId);
        cmd.ExecuteNonQuery();
    }

    public List<Order> GetOrders()
    {
        var list = new List<Order>();
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand(
            """
            SELECT order_id, order_article, date_order, date_delivery, pick_up_point_id, code, status_order, user_id, total_sum
            FROM orders
            ORDER BY order_id
            """,
            conn);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            list.Add(new Order
            {
                OrderId = reader.GetInt32(0),
                OrderArticle = reader.IsDBNull(1) ? "" : reader.GetString(1),
                DateOrder = reader.IsDBNull(2) ? "" : reader.GetDateTime(2).ToString("yyyy-MM-dd"),
                DateDelivery = reader.IsDBNull(3) ? "" : reader.GetDateTime(3).ToString("yyyy-MM-dd"),
                PickUpPointId = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                Code = reader.IsDBNull(5) ? "" : reader.GetString(5),
                StatusOrder = reader.IsDBNull(6) ? "Новый" : reader.GetString(6),
                UserId = reader.IsDBNull(7) ? 0 : reader.GetInt32(7),
                TotalSum = reader.IsDBNull(8) ? 0m : reader.GetDecimal(8)
            });
        }

        return list;
    }

    public void AddOrder(Order order)
    {
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand(
            """
            INSERT INTO orders (order_article, date_order, date_delivery, pick_up_point_id, code, status_order, user_id, total_sum)
            VALUES (@article, @dateOrder, @dateDelivery, @pickUpPointId, @code, @status, @userId, @totalSum)
            """,
            conn);
        FillOrderParameters(cmd, order);
        cmd.ExecuteNonQuery();
    }

    public void UpdateOrder(Order order)
    {
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand(
            """
            UPDATE orders
            SET order_article = @article,
                date_order = @dateOrder,
                date_delivery = @dateDelivery,
                pick_up_point_id = @pickUpPointId,
                code = @code,
                status_order = @status,
                user_id = @userId,
                total_sum = @totalSum
            WHERE order_id = @id
            """,
            conn);
        FillOrderParameters(cmd, order);
        cmd.Parameters.AddWithValue("id", order.OrderId);
        cmd.ExecuteNonQuery();
    }

    public void DeleteOrder(int orderId)
    {
        using var conn = OpenConnection();
        using var cmd = new NpgsqlCommand("DELETE FROM orders WHERE order_id = @id", conn);
        cmd.Parameters.AddWithValue("id", orderId);
        cmd.ExecuteNonQuery();
    }

    private static void FillProductParameters(NpgsqlCommand cmd, Product product)
    {
        cmd.Parameters.AddWithValue("article", product.Article);
        cmd.Parameters.AddWithValue("categoryId", product.CategoryId == 0 ? DBNull.Value : product.CategoryId);
        cmd.Parameters.AddWithValue("supplierId", product.SupplierId == 0 ? DBNull.Value : product.SupplierId);
        cmd.Parameters.AddWithValue("typeId", product.TypeProductId == 0 ? DBNull.Value : product.TypeProductId);
        cmd.Parameters.AddWithValue("price", product.Price);
        cmd.Parameters.AddWithValue("discount", product.Discount);
        cmd.Parameters.AddWithValue("description", string.IsNullOrWhiteSpace(product.Description) ? DBNull.Value : product.Description);
        cmd.Parameters.AddWithValue("photo", string.IsNullOrWhiteSpace(product.Photo) ? DBNull.Value : product.Photo);
    }

    private static void FillOrderParameters(NpgsqlCommand cmd, Order order)
    {
        cmd.Parameters.AddWithValue("article", string.IsNullOrWhiteSpace(order.OrderArticle) ? DBNull.Value : order.OrderArticle);
        cmd.Parameters.AddWithValue("dateOrder", ParseDateOrDbNull(order.DateOrder));
        cmd.Parameters.AddWithValue("dateDelivery", ParseDateOrDbNull(order.DateDelivery));
        cmd.Parameters.AddWithValue("pickUpPointId", order.PickUpPointId == 0 ? DBNull.Value : order.PickUpPointId);
        cmd.Parameters.AddWithValue("code", string.IsNullOrWhiteSpace(order.Code) ? DBNull.Value : order.Code);
        cmd.Parameters.AddWithValue("status", string.IsNullOrWhiteSpace(order.StatusOrder) ? "Новый" : order.StatusOrder);
        cmd.Parameters.AddWithValue("userId", order.UserId == 0 ? DBNull.Value : order.UserId);
        cmd.Parameters.AddWithValue("totalSum", order.TotalSum);
    }

    private static object ParseDateOrDbNull(string date)
    {
        return DateTime.TryParse(date, out var value) ? value : DBNull.Value;
    }

    private static string NormalizeRole(string role)
    {
        var value = role.Trim().ToLowerInvariant();
        return value switch
        {
            "админ" => "admin",
            "администратор" => "admin",
            "manager" => "manager",
            "менеджер" => "manager",
            "client" => "client",
            "клиент" => "client",
            _ => value
        };
    }

    private NpgsqlConnection OpenConnection()
    {
        Exception? lastError = null;

        foreach (var connectionString in _connectionCandidates)
        {
            try
            {
                var connection = new NpgsqlConnection(connectionString);
                connection.Open();
                return connection;
            }
            catch (Exception ex)
            {
                lastError = ex;
            }
        }

        throw new InvalidOperationException(
            "Нет подключения к PostgreSQL. Проверьте логин/пароль/название базы в DbService.cs.",
            lastError);
    }
}

================================================================================
БАЗА ДАННЫХ: СОЗДАНИЕ + ЗАПОЛНЕНИЕ
================================================================================

--------------------------------------------------------------------------------
FILE: Database/full_schema_and_seed.sql
--------------------------------------------------------------------------------
-- (опционально) создать базу:
-- CREATE DATABASE "321";

-- 1) Таблицы
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    name_role VARCHAR(50) NOT NULL
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    role_id INTEGER REFERENCES roles(role_id),
    login VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(100),
    second_name VARCHAR(100),
    patronymic VARCHAR(100)
);

CREATE TABLE category_products (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE type_products (
    type_products_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    article VARCHAR(50) NOT NULL,
    category_id INTEGER REFERENCES category_products(category_id),
    supplier_id INTEGER REFERENCES suppliers(supplier_id),
    type_products_id INTEGER REFERENCES type_products(type_products_id),
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    discount VARCHAR(10) DEFAULT '0',
    description TEXT,
    photo VARCHAR(255)
);

CREATE TABLE storage (
    store_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE pick_up_points (
    pick_up_point_id SERIAL PRIMARY KEY,
    postcode VARCHAR(10),
    city VARCHAR(100),
    street VARCHAR(100),
    num_house VARCHAR(20)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_article TEXT,
    date_order DATE,
    date_delivery DATE,
    pick_up_point_id INTEGER REFERENCES pick_up_points(pick_up_point_id),
    code VARCHAR(20),
    status_order VARCHAR(50) DEFAULT 'Новый',
    user_id INTEGER REFERENCES users(user_id),
    total_sum DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL DEFAULT 1
);

-- 2) Заполнение
INSERT INTO roles (name_role) VALUES
('Администратор'),
('Менеджер'),
('Клиент');

INSERT INTO users (role_id, login, password, name, second_name, patronymic) VALUES
(1, 'admin', 'admin123', 'Иван', 'Админов', 'Админович'),
(2, 'manager1', 'manager123', 'Петр', 'Менеджеров', 'Петрович'),
(3, 'client1', 'client123', 'Сидор', 'Сидоров', 'Сидорович'),
(3, 'client2', 'client456', 'Анна', 'Иванова', 'Алексеевна');

INSERT INTO category_products (name) VALUES
('Электроника'),
('Одежда'),
('Аксессуары'),
('Дом и сад');

INSERT INTO suppliers (name) VALUES
('ООО "ТехноПром"'),
('ЗАО "Текстиль-Люкс"'),
('ИП Сидоров А.А.'),
('ООО "Глобал Импорт"');

INSERT INTO type_products (name) VALUES
('Новинка'),
('Популярный'),
('Распродажа'),
('Стандартный');

INSERT INTO products (article, category_id, supplier_id, type_products_id, price, discount, description, photo) VALUES
('EL-001', 1, 1, 2, 59990.00, '10', 'Флагманский смартфон с отличной камерой', '/images/phone1.jpg'),
('CL-002', 2, 2, 1, 3500.00, '0', 'Мужская хлопковая футболка белого цвета', '/images/tshirt_white.jpg'),
('AC-003', 3, 3, 4, 1500.00, '5', 'Кожаный ремень классический', '/images/belt_black.jpg'),
('EL-004', 1, 1, 3, 4500.00, '20', 'Беспроводные наушники с шумоподавлением', '/images/headphones.jpg'),
('HS-005', 4, 4, 4, 2500.00, '0', 'Настольная лампа с регулировкой яркости', '/images/lamp.jpg');

INSERT INTO storage (product_id, count) VALUES
(1, 50),
(2, 200),
(3, 75),
(4, 15),
(5, 120);

INSERT INTO pick_up_points (postcode, city, street, num_house) VALUES
('101000', 'Москва', 'ул. Тверская', '10'),
('190000', 'Санкт-Петербург', 'Невский пр.', '25'),
('420000', 'Казань', 'ул. Баумана', '15/2');

INSERT INTO orders (order_article, date_order, date_delivery, pick_up_point_id, code, status_order, user_id, total_sum) VALUES
('ORD-2023-001', '2023-10-01', '2023-10-05', 1, '12345', 'Выдан', 3, 53991.00),
('ORD-2023-002', '2023-10-15', '2023-10-20', 2, '54321', 'В пути', 4, 4925.00),
('ORD-2023-003', '2023-10-20', '2023-10-25', 1, '11111', 'Новый', 3, 3600.00);

INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 1, 1),
(2, 2, 1),
(2, 3, 1),
(3, 4, 1);

================================================================================
КОНЕЦ ФАЙЛА
================================================================================