DatabaseHelper.cs

using Microsoft.Data.Sqlite;
using System.Data;

namespace SalesAPI;

public class DatabaseHelper
{
    private readonly string _connectionString;

    public DatabaseHelper(string dbPath = "salesadmin.db")
    {
        _connectionString = $"Data Source={dbPath};Cache=Shared;Default Timeout=30";
        InitializeDatabase();
        MigrateDatabase();
    }

    private void MigrateDatabase()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        // Clean up any leftover temp table from a failed migration
        using var cleanupCmd = connection.CreateCommand();
        cleanupCmd.CommandText = "DROP TABLE IF EXISTS Clients_New;";
        cleanupCmd.ExecuteNonQuery();

        // Check if UNIQUE constraint exists on Clients table and remove it
        using var pragmaCmd = connection.CreateCommand();
        pragmaCmd.CommandText = "SELECT sql FROM sqlite_master WHERE type='table' AND name='Clients'";
        var tableSql = pragmaCmd.ExecuteScalar()?.ToString() ?? "";

        if (tableSql.Contains("UNIQUE(CompanyName, Email)"))
        {
            using var migrateCmd = connection.CreateCommand();
            migrateCmd.CommandText = @"PRAGMA foreign_keys = OFF;";
            migrateCmd.ExecuteNonQuery();
            migrateCmd.CommandText = @"
            CREATE TABLE Clients_New (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                SearchTerm TEXT,
                AccountId TEXT,
                CompanyName TEXT NOT NULL,
                VATNumber TEXT,
                Address TEXT,
                PhoneNumber TEXT,
                Email TEXT,
                SecondaryEmail TEXT,
                AccountTerms TEXT,
                IsActive INTEGER DEFAULT 1,
                CreatedDate TEXT NOT NULL,
                LastUpdated TEXT NOT NULL
            );";
            migrateCmd.ExecuteNonQuery();
            migrateCmd.CommandText = "INSERT INTO Clients_New SELECT * FROM Clients;";
            migrateCmd.ExecuteNonQuery();
            migrateCmd.CommandText = "DROP TABLE Clients;";
            migrateCmd.ExecuteNonQuery();
            migrateCmd.CommandText = "ALTER TABLE Clients_New RENAME TO Clients;";
            migrateCmd.ExecuteNonQuery();
            migrateCmd.CommandText = @"PRAGMA foreign_keys = ON;";
            migrateCmd.ExecuteNonQuery();
        }

        using var dropIdxCmd = connection.CreateCommand();
        dropIdxCmd.CommandText = @"DROP INDEX IF EXISTS idx_clients_accountid;";
        try { dropIdxCmd.ExecuteNonQuery(); } catch { }

        using var alterCmd = connection.CreateCommand();
        alterCmd.CommandText = @"ALTER TABLE Clients ADD COLUMN SoldToCode TEXT;";
        try { alterCmd.ExecuteNonQuery(); } catch { /* column already exists */ }

        using var uniqueCmd = connection.CreateCommand();
        uniqueCmd.CommandText = @"CREATE UNIQUE INDEX IF NOT EXISTS idx_clients_soldtocode ON Clients(SoldToCode);";
        try { uniqueCmd.ExecuteNonQuery(); } catch { /* index already exists */ }

        // Add ImageData column to Products if it doesn't exist
        using var imageColCmd = connection.CreateCommand();
        imageColCmd.CommandText = "ALTER TABLE Products ADD COLUMN ImageData TEXT;";
        try { imageColCmd.ExecuteNonQuery(); } catch { /* column already exists */ }

        // Drop foreign key from Orders table
        using var checkOrders = connection.CreateCommand();
        checkOrders.CommandText = "SELECT sql FROM sqlite_master WHERE type='table' AND name='Orders'";
        var ordersSql = checkOrders.ExecuteScalar()?.ToString() ?? "";
        if (ordersSql.Contains("FOREIGN KEY (ClientId) REFERENCES Clients(Id)"))
        {
            using var rebuildCmd = connection.CreateCommand();
            rebuildCmd.CommandText = "PRAGMA foreign_keys = OFF;";
            rebuildCmd.ExecuteNonQuery();
            rebuildCmd.CommandText = @"CREATE TABLE Orders_New (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            OrderNumber TEXT NOT NULL UNIQUE,
            ClientId INTEGER NOT NULL,
            ClientName TEXT NOT NULL,
            Status TEXT NOT NULL DEFAULT 'Pending',
            TotalAmount REAL NOT NULL,
            OrderDate TEXT NOT NULL,
            DeliveryDate TEXT,
            Notes TEXT,
            Source TEXT DEFAULT 'Email',
            CreatedBy TEXT,
            CreatedDate TEXT NOT NULL,
            LastUpdated TEXT
        );";
            rebuildCmd.ExecuteNonQuery();
            rebuildCmd.CommandText = "INSERT INTO Orders_New SELECT * FROM Orders;";
            rebuildCmd.ExecuteNonQuery();
            rebuildCmd.CommandText = "DROP TABLE Orders;";
            rebuildCmd.ExecuteNonQuery();
            rebuildCmd.CommandText = "ALTER TABLE Orders_New RENAME TO Orders;";
            rebuildCmd.ExecuteNonQuery();
            rebuildCmd.CommandText = "PRAGMA foreign_keys = ON;";
            rebuildCmd.ExecuteNonQuery();
        }
    }

    private void InitializeDatabase()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var createProductsTable = @"
        CREATE TABLE IF NOT EXISTS Products (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            SAPCode TEXT NOT NULL UNIQUE,
            Category TEXT,
            Category1 TEXT,
            Category2 TEXT,
            RefNo TEXT,
            Selection TEXT,
            RangeName TEXT,
            Status TEXT,
            Barcode TEXT,
            PackBarcode TEXT,
            Description TEXT NOT NULL,
            Unit TEXT,
            PriceExclVAT REAL NOT NULL,
            VATPercent REAL,
            PriceInclVAT REAL NOT NULL,
            RRP REAL,
            CasePack INTEGER,
            InnerMOQ INTEGER,
            LastUpdated TEXT NOT NULL
        );";

        var createClientsTable = @"
        CREATE TABLE IF NOT EXISTS Clients (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            SearchTerm TEXT,
            AccountId TEXT,
            CompanyName TEXT NOT NULL,
            VATNumber TEXT,
            Address TEXT,
            PhoneNumber TEXT,
            Email TEXT,
            SecondaryEmail TEXT,
            AccountTerms TEXT,
            IsActive INTEGER DEFAULT 1,
            CreatedDate TEXT NOT NULL,
            LastUpdated TEXT NOT NULL                
        );";

        var createInventoryTable = @"
        CREATE TABLE IF NOT EXISTS Inventory (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            SAPCode TEXT NOT NULL UNIQUE,
            ProductCode TEXT,
            Description TEXT,
            QuantityAvailable INTEGER NOT NULL DEFAULT 0,
            DateLastReceived TEXT,
            LastUpdated TEXT NOT NULL
        );";

        var createUsersTable = @"
        CREATE TABLE IF NOT EXISTS Users (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Username TEXT NOT NULL UNIQUE,
            PasswordHash TEXT NOT NULL,
            Role TEXT NOT NULL DEFAULT 'Client',
            ClientId INTEGER,
            IsActive INTEGER DEFAULT 1,
            CreatedDate TEXT NOT NULL,
            FOREIGN KEY (ClientId) REFERENCES Clients(Id)
        );";

        var createOrdersTable = @"
        CREATE TABLE IF NOT EXISTS Orders (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            OrderNumber TEXT NOT NULL UNIQUE,
            ClientId INTEGER NOT NULL,
            ClientName TEXT NOT NULL,
            Status TEXT NOT NULL DEFAULT 'Pending',
            TotalAmount REAL NOT NULL,
            OrderDate TEXT NOT NULL,
            DeliveryDate TEXT,
            Notes TEXT,
            Source TEXT DEFAULT 'Email',
            CreatedBy TEXT,
            CreatedDate TEXT NOT NULL,
            LastUpdated TEXT,
            FOREIGN KEY (ClientId) REFERENCES Clients(Id)
        );";

        var createOrderItemsTable = @"
        CREATE TABLE IF NOT EXISTS OrderItems (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            OrderId INTEGER NOT NULL,
            ProductId INTEGER NOT NULL,
            Quantity INTEGER NOT NULL,
            UnitPrice REAL NOT NULL,
            TotalPrice REAL NOT NULL,
            FOREIGN KEY (OrderId) REFERENCES Orders(Id),
            FOREIGN KEY (ProductId) REFERENCES Products(Id)
        );";

        var createBackOrdersTable = @"
        CREATE TABLE IF NOT EXISTS BackOrders (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            ClientId INTEGER NOT NULL,
            ClientName TEXT NOT NULL,
            RepUsername TEXT NOT NULL,
            SAPCode TEXT NOT NULL,
            RefNo TEXT,
            Description TEXT NOT NULL,
            RequestedQty INTEGER NOT NULL,
            AvailableQtyAtTime INTEGER NOT NULL DEFAULT 0,
            Status TEXT NOT NULL DEFAULT 'Pending',
            DateLogged TEXT NOT NULL,
            DateReplenished TEXT,
            LastUpdated TEXT NOT NULL
        );";

        var createPersonalEmailListTable = @"
CREATE TABLE IF NOT EXISTS PersonalEmailList (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Username TEXT NOT NULL,
    ClientName TEXT NOT NULL,
    PrimaryEmail TEXT NOT NULL,
    SecondaryEmail TEXT,
    VendorNumber TEXT,
    SAPCode TEXT,
    PhoneNumber TEXT,
    Address TEXT,
    IsActive INTEGER DEFAULT 1,
    CreatedDate TEXT NOT NULL,
    LastUpdated TEXT NOT NULL
);";

        using var command = connection.CreateCommand();

        command.CommandText = createProductsTable;
        command.ExecuteNonQuery();

        command.CommandText = createClientsTable;
        command.ExecuteNonQuery();

        command.CommandText = createInventoryTable;
        command.ExecuteNonQuery();

        command.CommandText = createUsersTable;
        command.ExecuteNonQuery();

        command.CommandText = createOrdersTable;
        command.ExecuteNonQuery();

        command.CommandText = createOrderItemsTable;
        command.ExecuteNonQuery();

        command.CommandText = createBackOrdersTable;
        command.ExecuteNonQuery();

        command.CommandText = createPersonalEmailListTable;
        command.ExecuteNonQuery();
    }

    #region Product Methods

    public void UpsertProduct(Product product)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            INSERT INTO Products (
                SAPCode, Category, Category1, Category2, RefNo, Selection, RangeName,
                Status, Barcode, PackBarcode, Description, Unit, PriceExclVAT,
                VATPercent, PriceInclVAT, RRP, CasePack, InnerMOQ, LastUpdated
            )
            VALUES (
                @SAPCode, @Category, @Category1, @Category2, @RefNo, @Selection, @RangeName,
                @Status, @Barcode, @PackBarcode, @Description, @Unit, @PriceExclVAT,
                @VATPercent, @PriceInclVAT, @RRP, @CasePack, @InnerMOQ, @LastUpdated
            )
            ON CONFLICT(SAPCode) DO UPDATE SET
                Category = @Category,
                Category1 = @Category1,
                Category2 = @Category2,
                RefNo = @RefNo,
                Selection = @Selection,
                RangeName = @RangeName,
                Status = @Status,
                Barcode = @Barcode,
                PackBarcode = @PackBarcode,
                Description = @Description,
                Unit = @Unit,
                PriceExclVAT = @PriceExclVAT,
                VATPercent = @VATPercent,
                PriceInclVAT = @PriceInclVAT,
                RRP = @RRP,
                CasePack = @CasePack,
                InnerMOQ = @InnerMOQ,
                ImageData = @ImageData,
                LastUpdated = @LastUpdated;";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@SAPCode", product.SAPCode);
        command.Parameters.AddWithValue("@Category", product.Category ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Category1", product.Category1 ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Category2", product.Category2 ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@RefNo", product.RefNo ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Selection", product.Selection ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@RangeName", product.RangeName ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Status", product.Status ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Barcode", product.Barcode ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@PackBarcode", product.PackBarcode ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Description", product.Description);
        command.Parameters.AddWithValue("@Unit", product.Unit ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@PriceExclVAT", product.PriceExclVAT);
        command.Parameters.AddWithValue("@VATPercent", product.VATPercent);
        command.Parameters.AddWithValue("@PriceInclVAT", product.PriceInclVAT);
        command.Parameters.AddWithValue("@RRP", product.RRP ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@CasePack", product.CasePack ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@InnerMOQ", product.InnerMOQ ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@ImageData", product.ImageData ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        command.ExecuteNonQuery();
    }

    public List<Product> GetAllProducts()
    {
        var products = new List<Product>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM Products ORDER BY Description";
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            products.Add(new Product
            {
                Id = reader.GetInt32(0),
                SAPCode = reader.GetString(1),
                Category = reader.IsDBNull(2) ? null : reader.GetString(2),
                Category1 = reader.IsDBNull(3) ? null : reader.GetString(3),
                Category2 = reader.IsDBNull(4) ? null : reader.GetString(4),
                RefNo = reader.IsDBNull(5) ? null : reader.GetString(5),
                Selection = reader.IsDBNull(6) ? null : reader.GetString(6),
                RangeName = reader.IsDBNull(7) ? null : reader.GetString(7),
                Status = reader.IsDBNull(8) ? null : reader.GetString(8),
                Barcode = reader.IsDBNull(9) ? null : reader.GetString(9),
                PackBarcode = reader.IsDBNull(10) ? null : reader.GetString(10),
                Description = reader.GetString(11),
                Unit = reader.IsDBNull(12) ? null : reader.GetString(12),
                PriceExclVAT = reader.GetDouble(13),
                VATPercent = reader.GetDouble(14),
                PriceInclVAT = reader.GetDouble(15),
                RRP = reader.IsDBNull(16) ? null : reader.GetDouble(16),
                CasePack = reader.IsDBNull(17) ? null : reader.GetInt32(17),
                InnerMOQ = reader.IsDBNull(18) ? null : reader.GetInt32(18),
                LastUpdated = reader.GetString(19)
            });
        }

        return products;
    }

    public int GetProductCount()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        using var command = connection.CreateCommand();
        command.CommandText = "SELECT COUNT(*) FROM Products";

        return Convert.ToInt32(command.ExecuteScalar());
    }

    public int DeleteAllProducts()
    {
        // Temporarily disabled to allow upload when orders exist
        // using var connection = new SqliteConnection(_connectionString);
        // connection.Open();
        // using var command = connection.CreateCommand();
        // command.CommandText = "DELETE FROM Products";
        // return command.ExecuteNonQuery();
        return 0;  // Pretend we deleted 0 rows
    }

    #endregion

    #region Client Methods

    public void UpsertClient(Client client)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var sql = @"
        INSERT INTO Clients (
            SearchTerm, SoldToCode, AccountId, CompanyName, VATNumber, Address, PhoneNumber, Email,
            SecondaryEmail, AccountTerms, IsActive, CreatedDate, LastUpdated
        )
        VALUES (
            @SearchTerm, @SoldToCode, @AccountId, @CompanyName, @VATNumber, @Address, @PhoneNumber, @Email,
            @SecondaryEmail, @AccountTerms, @IsActive, @CreatedDate, @LastUpdated
        )
        ON CONFLICT(SoldToCode) DO UPDATE SET
            SearchTerm = @SearchTerm,
            AccountId = @AccountId,
            CompanyName = @CompanyName,
            VATNumber = @VATNumber,
            Address = @Address,
            PhoneNumber = @PhoneNumber,
            Email = @Email,
            SecondaryEmail = @SecondaryEmail,
            AccountTerms = @AccountTerms,
            IsActive = @IsActive,
            LastUpdated = @LastUpdated;";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@SearchTerm", client.SearchTerm ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@SoldToCode", client.SoldToCode ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@AccountId", client.AccountId ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@CompanyName", client.CompanyName);
        command.Parameters.AddWithValue("@VATNumber", client.VATNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Address", client.Address ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@PhoneNumber", client.PhoneNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Email", client.Email ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@SecondaryEmail", client.SecondaryEmail ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@AccountTerms", client.AccountTerms ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@IsActive", client.IsActive ? 1 : 0);
        command.Parameters.AddWithValue("@CreatedDate", client.CreatedDate ?? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.ExecuteNonQuery();
    }

    public int GetActiveClientCount()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        using var command = connection.CreateCommand();
        command.CommandText = "SELECT COUNT(*) FROM Clients WHERE IsActive = 1";
        return Convert.ToInt32(command.ExecuteScalar());
    }

    public int GetTotalClientCount()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        using var command = connection.CreateCommand();
        command.CommandText = "SELECT COUNT(*) FROM Clients";
        return Convert.ToInt32(command.ExecuteScalar());
    }

    public List<Client> GetAllClients()
    {
        var clients = new List<Client>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM Clients WHERE IsActive = 1 ORDER BY CompanyName";
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            clients.Add(new Client
            {
                Id = reader.GetInt32(0),
                SearchTerm = reader.IsDBNull(1) ? null : reader.GetString(1),
                AccountId = reader.IsDBNull(2) ? null : reader.GetString(2),
                CompanyName = reader.GetString(3),
                VATNumber = reader.IsDBNull(4) ? null : reader.GetString(4),
                Address = reader.IsDBNull(5) ? null : reader.GetString(5),
                PhoneNumber = reader.IsDBNull(6) ? null : reader.GetString(6),
                Email = reader.IsDBNull(7) ? null : reader.GetString(7),
                SecondaryEmail = reader.IsDBNull(8) ? null : reader.GetString(8),
                AccountTerms = reader.IsDBNull(9) ? null : reader.GetString(9),
                IsActive = reader.GetInt32(10) == 1,
                CreatedDate = reader.GetString(11),
                LastUpdated = reader.GetString(12),
                SoldToCode = reader.IsDBNull(13) ? null : reader.GetString(13),
            });
        }

        return clients;
    }

    public void UpdateClient(Client client)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            UPDATE Clients SET
                SearchTerm = @SearchTerm,
                AccountId = @AccountId,
                CompanyName = @CompanyName,
                VATNumber = @VATNumber,
                Address = @Address,
                PhoneNumber = @PhoneNumber,
                Email = @Email,
                SecondaryEmail = @SecondaryEmail,
                AccountTerms = @AccountTerms,
                LastUpdated = @LastUpdated
            WHERE Id = @Id;";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@Id", client.Id);
        command.Parameters.AddWithValue("@SearchTerm", client.SearchTerm ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@AccountId", client.AccountId ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@CompanyName", client.CompanyName);
        command.Parameters.AddWithValue("@VATNumber", client.VATNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Address", client.Address ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@PhoneNumber", client.PhoneNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Email", client.Email ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@SecondaryEmail", client.SecondaryEmail ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@AccountTerms", client.AccountTerms ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        command.ExecuteNonQuery();
    }
    public bool DeleteClient(int id)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "UPDATE Clients SET IsActive = 0, LastUpdated = @LastUpdated WHERE Id = @Id";

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Id", id);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        var rowsAffected = command.ExecuteNonQuery();
        return rowsAffected > 0;
    }

    public int DeleteAllClients()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        using var pragmaOff = connection.CreateCommand();
        pragmaOff.CommandText = "PRAGMA foreign_keys = OFF;";
        pragmaOff.ExecuteNonQuery();

        var countSql = "SELECT COUNT(*) FROM Clients";
        using var countCommand = connection.CreateCommand();
        countCommand.CommandText = countSql;
        int count = Convert.ToInt32(countCommand.ExecuteScalar());

        using var deleteUsers = connection.CreateCommand();
        deleteUsers.CommandText = "DELETE FROM Users WHERE ClientId IS NOT NULL";
        deleteUsers.ExecuteNonQuery();

        using var deleteClients = connection.CreateCommand();
        deleteClients.CommandText = "DELETE FROM Clients";
        deleteClients.ExecuteNonQuery();

        using var pragmaOn = connection.CreateCommand();
        pragmaOn.CommandText = "PRAGMA foreign_keys = ON;";
        pragmaOn.ExecuteNonQuery();

        return count;
    }

    #endregion

    #region BackOrder Methods

    public void LogBackOrder(BackOrder backOrder)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
        INSERT INTO BackOrders (
            ClientId, ClientName, RepUsername, SAPCode, RefNo, Description,
            RequestedQty, AvailableQtyAtTime, Status, DateLogged, LastUpdated
        )
        VALUES (
            @ClientId, @ClientName, @RepUsername, @SAPCode, @RefNo, @Description,
            @RequestedQty, @AvailableQtyAtTime, @Status, @DateLogged, @LastUpdated
        );";

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@ClientId", backOrder.ClientId);
        command.Parameters.AddWithValue("@ClientName", backOrder.ClientName);
        command.Parameters.AddWithValue("@RepUsername", backOrder.RepUsername);
        command.Parameters.AddWithValue("@SAPCode", backOrder.SAPCode);
        command.Parameters.AddWithValue("@RefNo", backOrder.RefNo ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Description", backOrder.Description);
        command.Parameters.AddWithValue("@RequestedQty", backOrder.RequestedQty);
        command.Parameters.AddWithValue("@AvailableQtyAtTime", backOrder.AvailableQtyAtTime);
        command.Parameters.AddWithValue("@Status", "Pending");
        command.Parameters.AddWithValue("@DateLogged", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.ExecuteNonQuery();
    }

    public List<BackOrder> GetBackOrders(string? repUsername = null, string? status = null)
    {
        var backOrders = new List<BackOrder>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM BackOrders WHERE 1=1";
        if (!string.IsNullOrEmpty(repUsername)) sql += " AND RepUsername = @RepUsername";
        if (!string.IsNullOrEmpty(status)) sql += " AND Status = @Status";
        sql += " ORDER BY DateLogged DESC";

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        if (!string.IsNullOrEmpty(repUsername)) command.Parameters.AddWithValue("@RepUsername", repUsername);
        if (!string.IsNullOrEmpty(status)) command.Parameters.AddWithValue("@Status", status);

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            backOrders.Add(new BackOrder
            {
                Id = reader.GetInt32(0),
                ClientId = reader.GetInt32(1),
                ClientName = reader.GetString(2),
                RepUsername = reader.GetString(3),
                SAPCode = reader.GetString(4),
                RefNo = reader.IsDBNull(5) ? null : reader.GetString(5),
                Description = reader.GetString(6),
                RequestedQty = reader.GetInt32(7),
                AvailableQtyAtTime = reader.GetInt32(8),
                Status = reader.GetString(9),
                DateLogged = reader.GetString(10),
                DateReplenished = reader.IsDBNull(11) ? null : reader.GetString(11),
                LastUpdated = reader.GetString(12)
            });
        }

        return backOrders;
    }

    public List<BackOrder> CheckReplenishedBackOrders()
    {
        var replenished = new List<BackOrder>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
        SELECT b.* FROM BackOrders b
        INNER JOIN Inventory i ON b.SAPCode = i.SAPCode
        WHERE b.Status = 'Pending' AND i.QuantityAvailable > 0";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            replenished.Add(new BackOrder
            {
                Id = reader.GetInt32(0),
                ClientId = reader.GetInt32(1),
                ClientName = reader.GetString(2),
                RepUsername = reader.GetString(3),
                SAPCode = reader.GetString(4),
                RefNo = reader.IsDBNull(5) ? null : reader.GetString(5),
                Description = reader.GetString(6),
                RequestedQty = reader.GetInt32(7),
                AvailableQtyAtTime = reader.GetInt32(8),
                Status = reader.GetString(9),
                DateLogged = reader.GetString(10),
                DateReplenished = reader.IsDBNull(11) ? null : reader.GetString(11),
                LastUpdated = reader.GetString(12)
            });
        }

        // Mark them as replenished
        foreach (var bo in replenished)
        {
            using var updateCmd = connection.CreateCommand();
            updateCmd.CommandText = @"
            UPDATE BackOrders 
            SET Status = 'Replenished', DateReplenished = @Date, LastUpdated = @Date
            WHERE Id = @Id";
            updateCmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            updateCmd.Parameters.AddWithValue("@Id", bo.Id);
            updateCmd.ExecuteNonQuery();
        }

        return replenished;
    }
       
    #endregion

    #region Inventory Methods

    public void UpsertInventory(Inventory item)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            INSERT INTO Inventory (
                SAPCode, ProductCode, Description, QuantityAvailable, DateLastReceived, LastUpdated
            )
            VALUES (
                @SAPCode, @ProductCode, @Description, @QuantityAvailable, @DateLastReceived, @LastUpdated
            )
            ON CONFLICT(SAPCode) DO UPDATE SET
                ProductCode = @ProductCode,
                Description = @Description,
                QuantityAvailable = @QuantityAvailable,
                DateLastReceived = @DateLastReceived,
                LastUpdated = @LastUpdated;";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@SAPCode", item.SAPCode);
        command.Parameters.AddWithValue("@ProductCode", item.ProductCode ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Description", item.Description ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@QuantityAvailable", item.QuantityAvailable);
        command.Parameters.AddWithValue("@DateLastReceived", item.DateLastReceived?.ToString("yyyy-MM-dd") ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        command.ExecuteNonQuery();
    }

    public List<Inventory> GetAllInventory()
    {
        var inventory = new List<Inventory>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM Inventory ORDER BY SAPCode";
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            inventory.Add(new Inventory
            {
                Id = reader.GetInt32(0),
                SAPCode = reader.GetString(1),
                ProductCode = reader.IsDBNull(2) ? null : reader.GetString(2),
                Description = reader.IsDBNull(3) ? null : reader.GetString(3),
                QuantityAvailable = reader.GetInt32(4),
                DateLastReceived = reader.IsDBNull(5) ? null : DateTime.Parse(reader.GetString(5)),
                LastUpdated = reader.GetString(6)
            });
        }

        return inventory;
    }

    public int GetStockQuantity(string sapCode)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT QuantityAvailable FROM Inventory WHERE SAPCode = @SAPCode";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@SAPCode", sapCode);

        var result = command.ExecuteScalar();
        return result != null ? Convert.ToInt32(result) : 0;
    }

    public int GetInventoryCount()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        using var command = connection.CreateCommand();
        command.CommandText = "SELECT COUNT(*) FROM Inventory WHERE QuantityAvailable > 0";

        return Convert.ToInt32(command.ExecuteScalar());
    }

    public int DeleteAllInventory()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        using var command = connection.CreateCommand();
        command.CommandText = "DELETE FROM Inventory";

        return command.ExecuteNonQuery();
    }

    #endregion

    #region User Methods

    public User? GetUserByUsername(string username)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM Users WHERE Username = @Username";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Username", username);

        using var reader = command.ExecuteReader();
        if (reader.Read())
        {
            return new User
            {
                Id = reader.GetInt32(0),
                Username = reader.GetString(1),
                PasswordHash = reader.GetString(2),
                Role = reader.GetString(3),
                ClientId = reader.IsDBNull(4) ? null : reader.GetInt32(4),
                IsActive = reader.GetInt32(5) == 1,
                CreatedDate = reader.GetString(6)
            };
        }

        return null;
    }

    public bool CreateUser(string username, string password, string role = "Rep")
    {
        var existingUser = GetUserByUsername(username);
        if (existingUser != null && existingUser.IsActive)
            return false;

        if (existingUser != null && !existingUser.IsActive)
        {
            // Reactivate the existing user with new password and role
            string newHash = BCrypt.Net.BCrypt.HashPassword(password);
            using var reactivateConn = new SqliteConnection(_connectionString);
            reactivateConn.Open();
            using var reactivateCmd = reactivateConn.CreateCommand();
            reactivateCmd.CommandText = "UPDATE Users SET PasswordHash = @Hash, Role = @Role, IsActive = 1 WHERE Username = @Username";
            reactivateCmd.Parameters.AddWithValue("@Hash", newHash);
            reactivateCmd.Parameters.AddWithValue("@Role", role);
            reactivateCmd.Parameters.AddWithValue("@Username", username);
            reactivateCmd.ExecuteNonQuery();
            return true;
        }

        string passwordHash = BCrypt.Net.BCrypt.HashPassword(password);

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            INSERT INTO Users (Username, PasswordHash, Role, IsActive, CreatedDate)
            VALUES (@Username, @PasswordHash, @Role, @IsActive, @CreatedDate);";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@Username", username);
        command.Parameters.AddWithValue("@PasswordHash", passwordHash);
        command.Parameters.AddWithValue("@Role", role);
        command.Parameters.AddWithValue("@IsActive", 1);
        command.Parameters.AddWithValue("@CreatedDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        command.ExecuteNonQuery();
        return true;
    }

    /// <summary>
    /// Ensures a user exists with the given role and password.
    /// If the user exists, updates only PasswordHash and Role (preserves Id, ClientId, CreatedDate).
    /// If inactive, reactivates without creating a duplicate username.
    /// </summary>
    public bool EnsureUserRoleAndPassword(string username, string password, string role)
    {
        try
        {
            var existingUser = GetUserByUsername(username);
            if (existingUser == null)
            {
                if (CreateUser(username, password, role))
                    return true;

                // Another process may have created the user between read and insert.
                existingUser = GetUserByUsername(username);
                if (existingUser == null)
                    return false;
            }

            string passwordHash = BCrypt.Net.BCrypt.HashPassword(password);

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var sql = @"
            UPDATE Users
            SET PasswordHash = @PasswordHash, Role = @Role, IsActive = 1
            WHERE Username = @Username";

            using var command = connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.AddWithValue("@Username", username);
            command.Parameters.AddWithValue("@PasswordHash", passwordHash);
            command.Parameters.AddWithValue("@Role", role);

            return command.ExecuteNonQuery() > 0;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[WARN] EnsureUserRoleAndPassword failed for {username}: {ex.Message}");
            return false;
        }
    }

    #endregion

    #region Order Methods

    public int CreateOrder(Order order)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            INSERT INTO Orders (
                OrderNumber, ClientId, ClientName, Status, TotalAmount, OrderDate,
                DeliveryDate, Notes, Source, CreatedBy, CreatedDate
            )
            VALUES (
                @OrderNumber, @ClientId, @ClientName, @Status, @TotalAmount, @OrderDate,
                @DeliveryDate, @Notes, @Source, @CreatedBy, @CreatedDate
            );
            SELECT last_insert_rowid();";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@OrderNumber", order.OrderNumber);
        command.Parameters.AddWithValue("@ClientId", order.ClientId);
        command.Parameters.AddWithValue("@ClientName", order.ClientName);
        command.Parameters.AddWithValue("@Status", order.Status);
        command.Parameters.AddWithValue("@TotalAmount", order.TotalAmount);
        command.Parameters.AddWithValue("@OrderDate", order.OrderDate);
        command.Parameters.AddWithValue("@DeliveryDate", order.DeliveryDate ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Notes", order.Notes ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Source", order.Source);
        command.Parameters.AddWithValue("@CreatedBy", order.CreatedBy ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@CreatedDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        return Convert.ToInt32(command.ExecuteScalar());
    }

    public void AddOrderItem(OrderItem item)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            INSERT INTO OrderItems (
                OrderId, ProductId, Quantity, UnitPrice, TotalPrice
            )
            VALUES (
                @OrderId, @ProductId, @Quantity, @UnitPrice, @TotalPrice
            );";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@OrderId", item.OrderId);
        command.Parameters.AddWithValue("@ProductId", item.ProductId);
        command.Parameters.AddWithValue("@Quantity", item.Quantity);
        command.Parameters.AddWithValue("@UnitPrice", item.UnitPrice);
        command.Parameters.AddWithValue("@TotalPrice", item.LineTotal);

        command.ExecuteNonQuery();
    }

    public List<Order> GetOrders(int? clientId = null)
    {
        var orders = new List<Order>();

        try
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var sql = clientId.HasValue
                ? "SELECT * FROM Orders WHERE ClientId = @ClientId ORDER BY CreatedDate DESC"
                : "SELECT * FROM Orders ORDER BY CreatedDate DESC";

            using var command = connection.CreateCommand();
            command.CommandText = sql;

            if (clientId.HasValue)
                command.Parameters.AddWithValue("@ClientId", clientId.Value);

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                orders.Add(new Order
                {
                    Id = reader.GetInt32(0),
                    OrderNumber = reader.GetString(1),
                    ClientId = reader.GetInt32(2),
                    ClientName = reader.GetString(3),
                    Status = reader.GetString(4),
                    TotalAmount = reader.GetDecimal(5),
                    OrderDate = reader.GetString(6),
                    DeliveryDate = reader.IsDBNull(7) ? null : reader.GetString(7),
                    Notes = reader.IsDBNull(8) ? null : reader.GetString(8),
                    Source = reader.GetString(9),
                    CreatedBy = reader.IsDBNull(10) ? null : reader.GetString(10),
                    CreatedDate = reader.GetString(11),
                    LastUpdated = reader.IsDBNull(12) ? null : reader.GetString(12)
                });
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"GetOrders error: {ex.Message}");
        }

        return orders;
    }

    public List<OrderItem> GetOrderItems(int orderId)
    {
        var items = new List<OrderItem>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            SELECT oi.*, p.SAPCode, p.RefNo, p.Description
            FROM OrderItems oi
            INNER JOIN Products p ON oi.ProductId = p.Id
            WHERE oi.OrderId = @OrderId";

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@OrderId", orderId);

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            items.Add(new OrderItem
            {
                Id = reader.GetInt32(0),
                OrderId = reader.GetInt32(1),
                ProductId = reader.GetInt32(2),
                Quantity = reader.GetInt32(3),
                UnitPrice = reader.GetDecimal(4),
                LineTotal = reader.GetDecimal(5),
                SAPCode = reader.GetString(6),
                ProductCode = reader.GetString(7),
                Description = reader.GetString(8)
            });
        }

        return items;
    }

    public void UpdateOrderStatus(int orderId, string status)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
            UPDATE Orders 
            SET Status = @Status, LastUpdated = @LastUpdated
            WHERE Id = @OrderId;";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@OrderId", orderId);
        command.Parameters.AddWithValue("@Status", status);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        command.ExecuteNonQuery();
    }

    #endregion

    #region User Management Methods

    public List<User> GetAllUsers()
    {
        var users = new List<User>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM Users WHERE IsActive = 1 ORDER BY Username";
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            users.Add(new User
            {
                Id = reader.GetInt32(0),
                Username = reader.GetString(1),
                PasswordHash = reader.GetString(2),
                Role = reader.GetString(3),
                ClientId = reader.IsDBNull(4) ? null : reader.GetInt32(4),
                IsActive = reader.GetInt32(5) == 1,
                CreatedDate = reader.GetString(6)
            });
        }

        return users;
    }

    public bool DeleteUser(string username)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "UPDATE Users SET IsActive = 0 WHERE Username = @Username";

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Username", username);

        var rowsAffected = command.ExecuteNonQuery();
        return rowsAffected > 0;
    }

    public bool ResetUserPassword(string username, string newPassword)
    {
        string passwordHash = BCrypt.Net.BCrypt.HashPassword(newPassword);

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "UPDATE Users SET PasswordHash = @PasswordHash WHERE Username = @Username AND IsActive = 1";

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Username", username);
        command.Parameters.AddWithValue("@PasswordHash", passwordHash);

        var rowsAffected = command.ExecuteNonQuery();
        return rowsAffected > 0;
    }

    #endregion

    #region CallCycle Methods

    public void UpsertCallCycle(CallCycle cycle)

    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
        INSERT INTO CallCycle (WeekNumber, DayOfWeek, ClientName, ClientId, OrderInDay, Notes, IsActive, CreatedDate, LastUpdated)
        VALUES (@WeekNumber, @DayOfWeek, @ClientName, @ClientId, @OrderInDay, @Notes, @IsActive, @CreatedDate, @LastUpdated)";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@WeekNumber", cycle.WeekNumber);
        command.Parameters.AddWithValue("@DayOfWeek", cycle.DayOfWeek);
        command.Parameters.AddWithValue("@ClientName", cycle.ClientName);
        command.Parameters.AddWithValue("@ClientId", cycle.ClientId ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@OrderInDay", cycle.OrderInDay);
        command.Parameters.AddWithValue("@Notes", cycle.Notes ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@IsActive", cycle.IsActive ? 1 : 0);
        command.Parameters.AddWithValue("@CreatedDate", cycle.CreatedDate);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        command.ExecuteNonQuery();
    }

    public void ClearCallCycle()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "DELETE FROM CallCycle";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.ExecuteNonQuery();
    }

    public List<CallCycle> GetAllCallCycles()
    {
        var cycles = new List<CallCycle>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM CallCycle WHERE IsActive = 1 ORDER BY WeekNumber, DayOfWeek, OrderInDay";
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            cycles.Add(new CallCycle
            {
                Id = reader.GetInt32(0),
                WeekNumber = reader.GetInt32(1),
                DayOfWeek = reader.GetString(2),
                ClientName = reader.GetString(3),
                ClientId = reader.IsDBNull(4) ? null : reader.GetInt32(4),
                OrderInDay = reader.GetInt32(5),
                Notes = reader.IsDBNull(6) ? null : reader.GetString(6),
                IsActive = reader.GetInt32(7) == 1,
                CreatedDate = reader.GetString(8),
                LastUpdated = reader.IsDBNull(9) ? null : reader.GetString(9)
            });
        }

        return cycles;
    }

    #endregion

    #region AppointmentRequest Methods

    public List<AppointmentRequest> GetAppointmentRequests(string? status = null)
    {
        var requests = new List<AppointmentRequest>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = "SELECT * FROM AppointmentRequests WHERE IsActive = 1";
        if (!string.IsNullOrEmpty(status))
        {
            sql += " AND Status = @Status";
        }
        sql += " ORDER BY RequestedDate, RequestedTime";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        if (!string.IsNullOrEmpty(status))
        {
            command.Parameters.AddWithValue("@Status", status);
        }

        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            requests.Add(new AppointmentRequest
            {
                Id = reader.GetInt32(0),
                ClientId = reader.GetInt32(1),
                ClientName = reader.GetString(2),
                RequestedDate = reader.GetString(3),
                RequestedTime = reader.GetString(4),
                Reason = reader.IsDBNull(5) ? null : reader.GetString(5),
                Status = reader.GetString(6),
                RequestDate = reader.GetString(7),
                ResponseDate = reader.IsDBNull(8) ? null : reader.GetString(8),
                ResponseNotes = reader.IsDBNull(9) ? null : reader.GetString(9),
                IsActive = reader.GetInt32(10) == 1
            });
        }

        return requests;
    }

    public void UpdateAppointmentStatus(int requestId, string status, string? responseNotes = null)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var sql = @"
        UPDATE AppointmentRequests 
        SET Status = @Status, ResponseDate = @ResponseDate, ResponseNotes = @ResponseNotes
        WHERE Id = @Id";

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        command.Parameters.AddWithValue("@Id", requestId);
        command.Parameters.AddWithValue("@Status", status);
        command.Parameters.AddWithValue("@ResponseDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.Parameters.AddWithValue("@ResponseNotes", responseNotes ?? (object)DBNull.Value);

        command.ExecuteNonQuery();
    }

    // NEW: DTO class to return Product data + current stock quantity
    public class ProductWithStock : Product
    {
        public int QuantityAvailable { get; set; }
    }

    // NEW: Method to fetch all products including current stock quantity from Inventory
    public List<ProductWithStock> GetAllProductsWithStock()
    {
        var products = new List<ProductWithStock>();

        using var connection = new SqliteConnection(_connectionString);
        connection.Open();

        var cmd = connection.CreateCommand();
        cmd.CommandText = @"
        SELECT p.*, COALESCE(i.QuantityAvailable, 0) AS QuantityAvailable
        FROM Products p
        LEFT JOIN Inventory i ON p.SAPCode = i.SAPCode";

        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var p = new ProductWithStock
            {
                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                SAPCode = reader.GetString(reader.GetOrdinal("SAPCode")),
                Description = reader.GetString(reader.GetOrdinal("Description")),
                Category = reader.IsDBNull(reader.GetOrdinal("Category")) ? null : reader.GetString(reader.GetOrdinal("Category")),
                Category1 = reader.IsDBNull(reader.GetOrdinal("Category1")) ? null : reader.GetString(reader.GetOrdinal("Category1")),
                Category2 = reader.IsDBNull(reader.GetOrdinal("Category2")) ? null : reader.GetString(reader.GetOrdinal("Category2")),
                RefNo = reader.IsDBNull(reader.GetOrdinal("RefNo")) ? null : reader.GetString(reader.GetOrdinal("RefNo")),
                Selection = reader.IsDBNull(reader.GetOrdinal("Selection")) ? null : reader.GetString(reader.GetOrdinal("Selection")),
                RangeName = reader.IsDBNull(reader.GetOrdinal("RangeName")) ? null : reader.GetString(reader.GetOrdinal("RangeName")),
                Status = reader.IsDBNull(reader.GetOrdinal("Status")) ? null : reader.GetString(reader.GetOrdinal("Status")),
                Barcode = reader.IsDBNull(reader.GetOrdinal("Barcode")) ? null : reader.GetString(reader.GetOrdinal("Barcode")),
                PackBarcode = reader.IsDBNull(reader.GetOrdinal("PackBarcode")) ? null : reader.GetString(reader.GetOrdinal("PackBarcode")),
                Unit = reader.IsDBNull(reader.GetOrdinal("Unit")) ? null : reader.GetString(reader.GetOrdinal("Unit")),
                PriceExclVAT = reader.GetDouble(reader.GetOrdinal("PriceExclVAT")),
                VATPercent = reader.GetDouble(reader.GetOrdinal("VATPercent")),
                PriceInclVAT = reader.GetDouble(reader.GetOrdinal("PriceInclVAT")),
                RRP = reader.IsDBNull(reader.GetOrdinal("RRP")) ? null : reader.GetDouble(reader.GetOrdinal("RRP")),
                InnerMOQ = reader.IsDBNull(reader.GetOrdinal("InnerMOQ")) ? null : reader.GetInt32(reader.GetOrdinal("InnerMOQ")),
                CasePack = reader.IsDBNull(reader.GetOrdinal("CasePack")) ? null : reader.GetInt32(reader.GetOrdinal("CasePack")),
                ImageData = reader.IsDBNull(reader.GetOrdinal("ImageData")) ? null : reader.GetString(reader.GetOrdinal("ImageData")),
                QuantityAvailable = reader.GetInt32(reader.GetOrdinal("QuantityAvailable"))
            };

            products.Add(p);
        }

        return products;
    }

    #endregion

    #region Personal Email List Methods

    public List<PersonalEmailEntry> GetPersonalEmailList(string username)
    {
        var list = new List<PersonalEmailEntry>();
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var sql = "SELECT * FROM PersonalEmailList WHERE Username = @Username AND IsActive = 1 ORDER BY ClientName";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Username", username);
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            list.Add(new PersonalEmailEntry
            {
                Id = reader.GetInt32(0),
                Username = reader.GetString(1),
                ClientName = reader.GetString(2),
                PrimaryEmail = reader.GetString(3),
                SecondaryEmail = reader.IsDBNull(4) ? null : reader.GetString(4),
                VendorNumber = reader.IsDBNull(5) ? null : reader.GetString(5),
                SAPCode = reader.IsDBNull(6) ? null : reader.GetString(6),
                PhoneNumber = reader.IsDBNull(7) ? null : reader.GetString(7),
                Address = reader.IsDBNull(8) ? null : reader.GetString(8),
                IsActive = reader.GetInt32(9) == 1,
                CreatedDate = reader.GetString(10),
                LastUpdated = reader.GetString(11)
            });
        }
        return list;
    }

    public void UpsertPersonalEmailEntry(PersonalEmailEntry entry)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var sql = @"
    INSERT INTO PersonalEmailList (Username, ClientName, PrimaryEmail, SecondaryEmail, VendorNumber, SAPCode, PhoneNumber, Address, IsActive, CreatedDate, LastUpdated)
    VALUES (@Username, @ClientName, @PrimaryEmail, @SecondaryEmail, @VendorNumber, @SAPCode, @PhoneNumber, @Address, 1, @CreatedDate, @LastUpdated)";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Username", entry.Username);
        command.Parameters.AddWithValue("@ClientName", entry.ClientName);
        command.Parameters.AddWithValue("@PrimaryEmail", entry.PrimaryEmail);
        command.Parameters.AddWithValue("@SecondaryEmail", entry.SecondaryEmail ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@VendorNumber", entry.VendorNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@SAPCode", entry.SAPCode ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@PhoneNumber", entry.PhoneNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Address", entry.Address ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@CreatedDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.ExecuteNonQuery();
    }

    public void UpdatePersonalEmailEntry(PersonalEmailEntry entry)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var sql = @"
    UPDATE PersonalEmailList SET
        ClientName = @ClientName,
        PrimaryEmail = @PrimaryEmail,
        SecondaryEmail = @SecondaryEmail,
        VendorNumber = @VendorNumber,
        SAPCode = @SAPCode,
        PhoneNumber = @PhoneNumber,
        Address = @Address,
        LastUpdated = @LastUpdated
    WHERE Id = @Id AND Username = @Username";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Id", entry.Id);
        command.Parameters.AddWithValue("@Username", entry.Username);
        command.Parameters.AddWithValue("@ClientName", entry.ClientName);
        command.Parameters.AddWithValue("@PrimaryEmail", entry.PrimaryEmail);
        command.Parameters.AddWithValue("@SecondaryEmail", entry.SecondaryEmail ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@VendorNumber", entry.VendorNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@SAPCode", entry.SAPCode ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@PhoneNumber", entry.PhoneNumber ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@Address", entry.Address ?? (object)DBNull.Value);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.ExecuteNonQuery();
    }

    public bool DeletePersonalEmailEntry(int id, string username)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var sql = "UPDATE PersonalEmailList SET IsActive = 0, LastUpdated = @LastUpdated WHERE Id = @Id AND Username = @Username";
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@Id", id);
        command.Parameters.AddWithValue("@Username", username);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        return command.ExecuteNonQuery() > 0;
    }

    public void ClearPersonalEmailList(string username)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        using var command = connection.CreateCommand();
        command.CommandText = "UPDATE PersonalEmailList SET IsActive = 0, LastUpdated = @LastUpdated WHERE Username = @Username";
        command.Parameters.AddWithValue("@Username", username);
        command.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        command.ExecuteNonQuery();
    }
    #endregion
}