WEFEGFDSA/DatabaseClient.cs

using Microsoft.Data.SqlClient;
using System.Data;

namespace TradeApp.WinForms.V08;

public sealed class DatabaseClient : IDisposable
{
    private readonly string _connectionString;

    public DatabaseClient(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void Dispose()
    {
    }

    public AppUser? Authenticate(string login, string password)
    {
        using var connection = Open();
        using var command = new SqlCommand(@"
            SELECT TOP (1) u.UserId, u.FullName, r.Name
            FROM dbo.Users u
            JOIN dbo.Roles r ON r.RoleId = u.RoleId
            WHERE u.Login = @login
              AND u.PasswordHash = HASHBYTES('SHA2_256', CONVERT(NVARCHAR(4000), @password));", connection);
        command.Parameters.Add("@login", SqlDbType.NVarChar, 255).Value = login;
        command.Parameters.Add("@password", SqlDbType.NVarChar, 4000).Value = password;

        using var reader = command.ExecuteReader();
        return reader.Read()
            ? new AppUser(reader.GetInt32(0), reader.GetString(1), reader.GetString(2))
            : null;
    }

    public List<ProductView> LoadProducts(string search, string searchField, string supplier, string sortMode)
    {
        var searchExpression = searchField switch
        {
            "Артикул" => "p.Article",
            "Наименование" => "p.Name",
            "Категория" => "c.Name",
            "Описание" => "p.Description",
            "Производитель" => "m.Name",
            "Поставщик" => "s.Name",
            "Единица измерения" => "u.Name",
            "Цена" => "CONVERT(NVARCHAR(50), p.Price)",
            "Скидка" => "CONVERT(NVARCHAR(50), p.DiscountPercent)",
            "Цена со скидкой" => "CONVERT(NVARCHAR(50), ROUND(p.Price * (100 - p.DiscountPercent) / 100, 2))",
            "Количество на складе" => "CONVERT(NVARCHAR(50), p.StockQuantity)",
            _ => "CONCAT(p.Article, N' ', p.Name, N' ', c.Name, N' ', p.Description, N' ', m.Name, N' ', s.Name, N' ', u.Name, N' ', p.Price, N' ', p.DiscountPercent, N' ', p.StockQuantity)"
        };

        var orderBy = sortMode switch
        {
            "Цена по убыванию" => "p.Price DESC",
            "Цена по возрастанию" => "p.Price ASC",
            "Остаток по убыванию" => "p.StockQuantity DESC",
            "Остаток по возрастанию" => "p.StockQuantity ASC",
            _ => "p.Name ASC"
        };

        using var connection = Open();
        using var command = new SqlCommand($@"
            SELECT p.ProductId, p.Article, p.Name, c.Name, ISNULL(p.Description, N''),
                   m.Name, s.Name, p.Price, p.DiscountPercent, u.Name, p.StockQuantity, p.ImagePath
            FROM dbo.Products p
            JOIN dbo.Categories c ON c.CategoryId = p.CategoryId
            JOIN dbo.Manufacturers m ON m.ManufacturerId = p.ManufacturerId
            JOIN dbo.Suppliers s ON s.SupplierId = p.SupplierId
            JOIN dbo.Units u ON u.UnitId = p.UnitId
            WHERE (@supplier = N'' OR s.Name = @supplier)
              AND (@search = N'' OR {searchExpression} LIKE N'%' + @search + N'%')
            ORDER BY {orderBy};", connection);
        command.Parameters.Add("@search", SqlDbType.NVarChar, 1000).Value = search;
        command.Parameters.Add("@supplier", SqlDbType.NVarChar, 200).Value = supplier == "Все поставщики" ? "" : supplier;

        var result = new List<ProductView>();
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            result.Add(new ProductView
            {
                ProductId = reader.GetInt32(0),
                Article = reader.GetString(1),
                Name = reader.GetString(2),
                Category = reader.GetString(3),
                Description = reader.GetString(4),
                Manufacturer = reader.GetString(5),
                Supplier = reader.GetString(6),
                Price = reader.GetDecimal(7),
                DiscountPercent = reader.GetDecimal(8),
                Unit = reader.GetString(9),
                StockQuantity = reader.GetInt32(10),
                ImagePath = reader.GetString(11)
            });
        }

        return result;
    }

    public List<string> LoadSuppliers() => LoadDictionary("dbo.Suppliers");

    public List<string> LoadCategories() => LoadDictionary("dbo.Categories");

    public List<string> LoadManufacturers() => LoadDictionary("dbo.Manufacturers");

    public List<string> LoadUnits() => LoadDictionary("dbo.Units");

    public List<string> LoadStatuses() => LoadDictionary("dbo.OrderStatuses");

    public List<string> LoadProductArticles()
    {
        using var connection = Open();
        using var command = new SqlCommand("SELECT Article FROM dbo.Products ORDER BY Article;", connection);
        using var reader = command.ExecuteReader();
        var result = new List<string>();
        while (reader.Read())
        {
            result.Add(reader.GetString(0));
        }

        return result;
    }

    public Dictionary<int, string> LoadPickupPoints()
    {
        using var connection = Open();
        using var command = new SqlCommand("SELECT PickupPointId, Address FROM dbo.PickupPoints ORDER BY PickupPointId;", connection);
        using var reader = command.ExecuteReader();
        var result = new Dictionary<int, string>();
        while (reader.Read())
        {
            result[reader.GetInt32(0)] = reader.GetString(1);
        }

        return result;
    }

    public void SaveProduct(ProductView product)
    {
        using var connection = Open();
        using var transaction = connection.BeginTransaction();

        var unitId = EnsureDictionaryValue(connection, transaction, "dbo.Units", "UnitId", product.Unit);
        var supplierId = EnsureDictionaryValue(connection, transaction, "dbo.Suppliers", "SupplierId", product.Supplier);
        var manufacturerId = EnsureDictionaryValue(connection, transaction, "dbo.Manufacturers", "ManufacturerId", product.Manufacturer);
        var categoryId = EnsureDictionaryValue(connection, transaction, "dbo.Categories", "CategoryId", product.Category);

        using var command = new SqlCommand(product.ProductId == 0 ? @"
            INSERT INTO dbo.Products (Article, Name, UnitId, Price, SupplierId, ManufacturerId, CategoryId, DiscountPercent, StockQuantity, Description, ImagePath)
            VALUES (@article, @name, @unitId, @price, @supplierId, @manufacturerId, @categoryId, @discount, @stock, @description, @image);" : @"
            UPDATE dbo.Products
            SET Article=@article, Name=@name, UnitId=@unitId, Price=@price, SupplierId=@supplierId,
                ManufacturerId=@manufacturerId, CategoryId=@categoryId, DiscountPercent=@discount,
                StockQuantity=@stock, Description=@description, ImagePath=@image
            WHERE ProductId=@productId;", connection, transaction);

        command.Parameters.Add("@productId", SqlDbType.Int).Value = product.ProductId;
        command.Parameters.Add("@article", SqlDbType.NVarChar, 50).Value = product.Article;
        command.Parameters.Add("@name", SqlDbType.NVarChar, 1000).Value = product.Name;
        command.Parameters.Add("@unitId", SqlDbType.Int).Value = unitId;
        AddDecimal(command, "@price", product.Price);
        command.Parameters.Add("@supplierId", SqlDbType.Int).Value = supplierId;
        command.Parameters.Add("@manufacturerId", SqlDbType.Int).Value = manufacturerId;
        command.Parameters.Add("@categoryId", SqlDbType.Int).Value = categoryId;
        AddDecimal(command, "@discount", product.DiscountPercent);
        command.Parameters.Add("@stock", SqlDbType.Int).Value = product.StockQuantity;
        command.Parameters.Add("@description", SqlDbType.NVarChar).Value = string.IsNullOrWhiteSpace(product.Description) ? DBNull.Value : product.Description;
        command.Parameters.Add("@image", SqlDbType.NVarChar, 260).Value = string.IsNullOrWhiteSpace(product.ImagePath) ? "picture.png" : product.ImagePath;
        command.ExecuteNonQuery();
        transaction.Commit();
    }

    public bool ProductIsUsedInOrders(int productId)
    {
        using var connection = Open();
        using var command = new SqlCommand("SELECT COUNT(*) FROM dbo.OrderItems WHERE ProductId=@id;", connection);
        command.Parameters.Add("@id", SqlDbType.Int).Value = productId;
        return Convert.ToInt32(command.ExecuteScalar()) > 0;
    }

    public bool ProductImageIsUsed(string imagePath, int exceptProductId)
    {
        if (string.IsNullOrWhiteSpace(imagePath) ||
            imagePath.Equals("picture.png", StringComparison.OrdinalIgnoreCase))
        {
            return true;
        }

        using var connection = Open();
        using var command = new SqlCommand(@"
            SELECT COUNT(*)
            FROM dbo.Products
            WHERE ImagePath=@imagePath AND ProductId<>@productId;", connection);
        command.Parameters.Add("@imagePath", SqlDbType.NVarChar, 260).Value = imagePath;
        command.Parameters.Add("@productId", SqlDbType.Int).Value = exceptProductId;
        return Convert.ToInt32(command.ExecuteScalar()) > 0;
    }

    public void DeleteProduct(int productId)
    {
        using var connection = Open();
        using var command = new SqlCommand("DELETE FROM dbo.Products WHERE ProductId=@id;", connection);
        command.Parameters.Add("@id", SqlDbType.Int).Value = productId;
        command.ExecuteNonQuery();
    }

    public List<OrderView> LoadOrders()
    {
        using var connection = Open();
        using var command = new SqlCommand(@"
            SELECT o.OrderId, ISNULL(u.FullName, N''), pp.Address, os.Name, o.OrderDate, o.DeliveryDate, o.ReceiveCode,
                   STRING_AGG(CONCAT(p.Article, N' x', oi.Quantity), N', ')
            FROM dbo.Orders o
            LEFT JOIN dbo.Users u ON u.UserId = o.UserId
            JOIN dbo.PickupPoints pp ON pp.PickupPointId = o.PickupPointId
            JOIN dbo.OrderStatuses os ON os.OrderStatusId = o.OrderStatusId
            LEFT JOIN dbo.OrderItems oi ON oi.OrderId = o.OrderId
            LEFT JOIN dbo.Products p ON p.ProductId = oi.ProductId
            GROUP BY o.OrderId, u.FullName, pp.Address, os.Name, o.OrderDate, o.DeliveryDate, o.ReceiveCode
            ORDER BY o.OrderId DESC;", connection);

        var result = new List<OrderView>();
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            result.Add(new OrderView
            {
                OrderId = reader.GetInt32(0),
                Customer = reader.GetString(1),
                PickupPoint = reader.GetString(2),
                Status = reader.GetString(3),
                OrderDate = reader.IsDBNull(4) ? null : reader.GetDateTime(4),
                DeliveryDate = reader.IsDBNull(5) ? null : reader.GetDateTime(5),
                ReceiveCode = reader.GetString(6),
                Articles = reader.IsDBNull(7) ? "" : reader.GetString(7)
            });
        }

        return result;
    }

    public void DeleteOrder(int orderId)
    {
        using var connection = Open();
        using var command = new SqlCommand("DELETE FROM dbo.Orders WHERE OrderId=@id;", connection);
        command.Parameters.Add("@id", SqlDbType.Int).Value = orderId;
        command.ExecuteNonQuery();
    }

    public (string Article, int Quantity)? LoadFirstOrderItem(int orderId)
    {
        using var connection = Open();
        using var command = new SqlCommand(@"
            SELECT TOP (1) p.Article, oi.Quantity
            FROM dbo.OrderItems oi
            JOIN dbo.Products p ON p.ProductId = oi.ProductId
            WHERE oi.OrderId=@orderId
            ORDER BY oi.OrderItemId;", connection);
        command.Parameters.Add("@orderId", SqlDbType.Int).Value = orderId;
        using var reader = command.ExecuteReader();
        return reader.Read() ? (reader.GetString(0), reader.GetInt32(1)) : null;
    }

    public int SaveOrder(OrderView order, int pickupPointId, string article, int quantity, string? customerFullName)
    {
        using var connection = Open();
        using var transaction = connection.BeginTransaction();
        var orderId = order.OrderId == 0 ? NextOrderId(connection, transaction) : order.OrderId;
        int? userId = null;
        if (!string.IsNullOrWhiteSpace(customerFullName))
        {
            using var userCmd = new SqlCommand("SELECT TOP (1) UserId FROM dbo.Users WHERE FullName = @name;", connection, transaction);
            userCmd.Parameters.Add("@name", SqlDbType.NVarChar, 250).Value = customerFullName.Trim();
            var existing = userCmd.ExecuteScalar();
            if (existing != null && existing != DBNull.Value)
            {
                userId = Convert.ToInt32(existing);
            }
        }
        var statusId = EnsureDictionaryValue(connection, transaction, "dbo.OrderStatuses", "OrderStatusId", order.Status);

        using (var command = new SqlCommand(order.OrderId == 0 ? @"
            INSERT INTO dbo.Orders (OrderId, UserId, PickupPointId, OrderStatusId, OrderDate, DeliveryDate, ReceiveCode)
            VALUES (@orderId, @userId, @pickupPointId, @statusId, @orderDate, @deliveryDate, @receiveCode);" : @"
            UPDATE dbo.Orders
            SET UserId=@userId, PickupPointId=@pickupPointId, OrderStatusId=@statusId, OrderDate=@orderDate,
                DeliveryDate=@deliveryDate, ReceiveCode=@receiveCode
            WHERE OrderId=@orderId;", connection, transaction))
        {
            command.Parameters.Add("@orderId", SqlDbType.Int).Value = orderId;
            command.Parameters.Add("@pickupPointId", SqlDbType.Int).Value = pickupPointId;
            command.Parameters.Add("@userId", SqlDbType.Int).Value = userId.HasValue ? (object)userId.Value : DBNull.Value;
            command.Parameters.Add("@statusId", SqlDbType.Int).Value = statusId;
            command.Parameters.Add("@orderDate", SqlDbType.Date).Value = order.OrderDate.HasValue ? order.OrderDate.Value : DBNull.Value;
            command.Parameters.Add("@deliveryDate", SqlDbType.Date).Value = order.DeliveryDate.HasValue ? order.DeliveryDate.Value : DBNull.Value;
            command.Parameters.Add("@receiveCode", SqlDbType.NVarChar, 20).Value = string.IsNullOrWhiteSpace(order.ReceiveCode) ? "000" : order.ReceiveCode;
            command.ExecuteNonQuery();
        }

        using (var deleteItems = new SqlCommand("DELETE FROM dbo.OrderItems WHERE OrderId=@orderId;", connection, transaction))
        {
            deleteItems.Parameters.Add("@orderId", SqlDbType.Int).Value = orderId;
            deleteItems.ExecuteNonQuery();
        }

        using (var insertItem = new SqlCommand(@"
            INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, PriceAtOrder)
            SELECT @orderId, ProductId, @quantity, Price
            FROM dbo.Products
            WHERE Article=@article;", connection, transaction))
        {
            insertItem.Parameters.Add("@orderId", SqlDbType.Int).Value = orderId;
            insertItem.Parameters.Add("@quantity", SqlDbType.Int).Value = quantity;
            insertItem.Parameters.Add("@article", SqlDbType.NVarChar, 50).Value = article;
            if (insertItem.ExecuteNonQuery() == 0)
            {
                throw new InvalidOperationException("Товар с указанным артикулом не найден.");
            }
        }

        transaction.Commit();
        return orderId;
    }

    private SqlConnection Open()
    {
        var connection = new SqlConnection(_connectionString);
        connection.Open();
        return connection;
    }

    private List<string> LoadDictionary(string tableName)
    {
        using var connection = Open();
        using var command = new SqlCommand($"SELECT Name FROM {tableName} ORDER BY Name;", connection);
        using var reader = command.ExecuteReader();
        var result = new List<string>();
        while (reader.Read())
        {
            result.Add(reader.GetString(0));
        }

        return result;
    }

    public List<string> LoadUsers()
    {
        using var connection = Open();
        using var command = new SqlCommand("SELECT FullName FROM dbo.Users ORDER BY FullName;", connection);
        using var reader = command.ExecuteReader();
        var result = new List<string>();
        while (reader.Read())
        {
            result.Add(reader.GetString(0));
        }

        return result;
    }

    private static int EnsureDictionaryValue(SqlConnection connection, SqlTransaction transaction, string tableName, string idColumn, string name)
    {
        if (string.IsNullOrWhiteSpace(name))
        {
            throw new InvalidOperationException("Не заполнено обязательное справочное значение.");
        }

        using (var select = new SqlCommand($"SELECT {idColumn} FROM {tableName} WHERE Name=@name;", connection, transaction))
        {
            select.Parameters.Add("@name", SqlDbType.NVarChar, 200).Value = name.Trim();
            var existing = select.ExecuteScalar();
            if (existing is not null && existing != DBNull.Value)
            {
                return Convert.ToInt32(existing);
            }
        }

        using var insert = new SqlCommand($"INSERT INTO {tableName} (Name) OUTPUT INSERTED.{idColumn} VALUES (@name);", connection, transaction);
        insert.Parameters.Add("@name", SqlDbType.NVarChar, 200).Value = name.Trim();
        return Convert.ToInt32(insert.ExecuteScalar());
    }

    private static int NextOrderId(SqlConnection connection, SqlTransaction transaction)
    {
        using var command = new SqlCommand("SELECT ISNULL(MAX(OrderId), 0) + 1 FROM dbo.Orders;", connection, transaction);
        return Convert.ToInt32(command.ExecuteScalar());
    }

    private static void AddDecimal(SqlCommand command, string name, decimal value)
    {
        var parameter = command.Parameters.Add(name, SqlDbType.Decimal);
        parameter.Precision = 18;
        parameter.Scale = 2;
        parameter.Value = value;
    }
}