const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
require('dotenv').config();

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'lssoft_db',
};

// Default Company Profile Data
const defaultCompanyData = {
  id: 1,
  company_name: 'LSSOFT',
  logo: '',
  favicon: '',
  office_title: 'Bangladesh Office',
  address: 'House No. KA-145, Uttorpara,\nKhilkhet, Dhaka-1229, Bangladesh',
  email_primary: 'support@lssoft.xyz',
  email_secondary: 'abu.sayeed.diu@gmail.com',
  phone_primary: '+880 1968-949468',
  phone_secondary: '+880 1750758262',
  website: 'https://lssoft.xyz',
  tax_id: 'BIN-123456789',
  invoice_footer: 'Thank you for doing business with LSSOFT.',
  currency: '৳'
};

// In-memory mock database store used if MySQL connection fails
let isUsingMockDB = false;
let mockAdminTable = [];
let mockCompanyProfile = { ...defaultCompanyData };
let mockProductsTable = [
  { id: 1, name: 'Sample Product 1', description: 'This is a sample product', price: 99.99, status: 'active', created_at: new Date() }
];

async function initializeDatabase() {
  try {
    // 1. Connect without database selected to ensure DB exists
    const rootConn = await mysql.createConnection({
      host: dbConfig.host,
      user: dbConfig.user,
      password: dbConfig.password,
    });

    await rootConn.query(`CREATE DATABASE IF NOT EXISTS \`${dbConfig.database}\`;`);
    await rootConn.end();

    // 2. Connect to the created database
    const pool = mysql.createPool(dbConfig);

    // 3. Create admin table with avatar column
    const createAdminTableQuery = `
      CREATE TABLE IF NOT EXISTS admin (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL UNIQUE,
        phone VARCHAR(30) NULL,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50) DEFAULT 'Viewer',
        status VARCHAR(20) DEFAULT 'active',
        avatar LONGTEXT NULL,
        permissions LONGTEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `;
    await pool.query(createAdminTableQuery);

    // Try adding avatar column if table already existed without it
    try {
      await pool.query(`ALTER TABLE admin ADD COLUMN avatar LONGTEXT NULL;`);
    } catch (e) { }
    // Try adding permissions column if table already existed without it
    try {
      await pool.query(`ALTER TABLE admin ADD COLUMN permissions LONGTEXT NULL;`);
    } catch (e) { }

    // 4. Create company table
    const createCompanyTableQuery = `
      CREATE TABLE IF NOT EXISTS company (
        id INT AUTO_INCREMENT PRIMARY KEY,
        company_name VARCHAR(255) DEFAULT 'LSSOFT',
        currency VARCHAR(10) DEFAULT '৳',
        logo LONGTEXT NULL,
        favicon LONGTEXT NULL,
        office_title VARCHAR(255) DEFAULT 'Bangladesh Office',
        address TEXT NULL,
        email_primary VARCHAR(255) NULL,
        email_secondary VARCHAR(255) NULL,
        phone_primary VARCHAR(100) NULL,
        phone_secondary VARCHAR(100) NULL,
        website VARCHAR(255) NULL,
        tax_id VARCHAR(100) NULL,
        invoice_footer TEXT NULL,
        mail_mailer VARCHAR(50) DEFAULT 'smtp',
        mail_host VARCHAR(255) NULL,
        mail_port INT DEFAULT 587,
        mail_username VARCHAR(255) NULL,
        mail_password VARCHAR(255) NULL,
        mail_encryption VARCHAR(50) DEFAULT 'tls',
        mail_from_address VARCHAR(255) NULL,
        mail_from_name VARCHAR(255) NULL,
        sms_api_url VARCHAR(255) NULL,
        sms_api_key VARCHAR(255) NULL,
        sms_sender_id VARCHAR(255) NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `;
    await pool.query(createCompanyTableQuery);

    // Auto-migrate new SMTP and SMS columns if the table already existed
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_mailer VARCHAR(50) DEFAULT "smtp"'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_host VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_port INT DEFAULT 587'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_username VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_password VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_encryption VARCHAR(50) DEFAULT "tls"'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_from_address VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN mail_from_name VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN sms_api_url VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN sms_api_key VARCHAR(255) NULL'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN sms_sender_id VARCHAR(255) NULL'); } catch (err) {}

    // 4.5. Create applications table
    const createApplicationsTableQuery = `
      CREATE TABLE IF NOT EXISTS applications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        category VARCHAR(100) DEFAULT 'General',
        product_code VARCHAR(100) NOT NULL UNIQUE,
        url VARCHAR(255) NULL,
        buy_link VARCHAR(255) NULL,
        logo LONGTEXT NULL,
        description TEXT NULL,
        status VARCHAR(20) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `;
    await pool.query(createApplicationsTableQuery);

    // Try adding category and buy_link if table already existed without them
    try {
      await pool.query(`ALTER TABLE applications ADD COLUMN category VARCHAR(100) DEFAULT 'General';`);
    } catch (e) { }
    try {
      await pool.query(`ALTER TABLE applications ADD COLUMN buy_link VARCHAR(255) NULL;`);
    } catch (e) { }

    // Seed / Sync default applications from screenshot
    for (const app of mockApplicationsTable) {
      const [existing] = await pool.query('SELECT id FROM applications WHERE product_code = ? OR name = ?', [app.product_code, app.name]);
      if (existing.length === 0) {
        await pool.query(
          'INSERT INTO applications (name, category, product_code, url, buy_link, logo, description, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
          [app.name, app.category || 'General', app.product_code, app.url, app.buy_link || '', app.logo, app.description, app.status]
        );
      } else {
        await pool.query(
          'UPDATE applications SET name = ?, category = ?, url = ?, buy_link = ?, logo = ?, description = ?, status = ? WHERE id = ?',
          [app.name, app.category || 'General', app.url, app.buy_link || '', app.logo, app.description, app.status, existing[0].id]
        );
      }
    }
    console.log('✅ Default applications (LS Micro Banking, BluePrint, Livo Cloud, DMS Pro) synced in MySQL database');

    // 4.6. Create txn_categories table
    const createTxnCategoriesTableQuery = `
      CREATE TABLE IF NOT EXISTS txn_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        type ENUM('income', 'expense') NOT NULL,
        status TINYINT(1) DEFAULT 1,
        created_by INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `;
    await pool.query(createTxnCategoriesTableQuery);

    // Seed default transaction categories from user list
    for (const [name, type] of seedTxnCategoriesList) {
      const [existing] = await pool.query('SELECT id FROM txn_categories WHERE name = ? AND type = ?', [name, type]);
      if (existing.length === 0) {
        await pool.query(
          'INSERT INTO txn_categories (name, type, status, created_by) VALUES (?, ?, 1, 1)',
          [name, type]
        );
      }
    }
    // 4.6.1 Create payment_accounts table & seed Bank & MFS accounts
    await pool.query(`
      CREATE TABLE IF NOT EXISTS payment_accounts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        type ENUM('Bank', 'MFS') DEFAULT 'Bank',
        bank_name VARCHAR(255) NOT NULL,
        branch_name VARCHAR(255) NULL,
        holder_name VARCHAR(255) NULL,
        account_number VARCHAR(255) NOT NULL,
        routing_number VARCHAR(255) NULL,
        status TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    const seedPaymentAccountsList = [
      { type: 'Bank', bank_name: 'City Bank PLC', branch_name: 'Nikunja Branch', holder_name: 'LS SOFT & ENGINEERING', account_number: '1324877343001', routing_number: '225261279' },
      { type: 'Bank', bank_name: 'Eastern Bank PLC', branch_name: 'Ashkona Branch', holder_name: 'MD ABU SAYEED', account_number: '1241440002054', routing_number: '095260918' },
      { type: 'Bank', bank_name: 'Brac Bank PLC', branch_name: 'Gulshan Branch', holder_name: 'MD ABU SAYEED', account_number: '1062801650001', routing_number: '060261726' },
      { type: 'Bank', bank_name: 'Islami Bank Bangladesh PLC', branch_name: 'Ranisankail Branch', holder_name: 'MD ABU SAYEED', account_number: '20504116700086606', routing_number: '125940829' },
      { type: 'Bank', bank_name: 'Dutch Bangla Bank PLC', branch_name: 'Agent Banking', holder_name: 'MD ABU SAYEED', account_number: '7017414023285', routing_number: 'N/A' },
      { type: 'Bank', bank_name: 'Mercantile Bank PLC', branch_name: 'Uttara Branch', holder_name: 'MD ABU SAYEED', account_number: '1121008431763', routing_number: '140264630' },
      { type: 'MFS', bank_name: 'bKash Merchant', branch_name: 'Payment Option', holder_name: 'LSSOFT', account_number: '01968949468', routing_number: 'N/A' },
      { type: 'MFS', bank_name: 'Nagad Personal', branch_name: 'Personal', holder_name: 'MD ABU SAYEED', account_number: '01318712782', routing_number: 'N/A' },
      { type: 'MFS', bank_name: 'Rocket Personal', branch_name: 'Personal', holder_name: 'MD ABU SAYEED', account_number: '013187127825', routing_number: 'N/A' },
    ];

    for (const acc of seedPaymentAccountsList) {
      const [existing] = await pool.query('SELECT id FROM payment_accounts WHERE bank_name = ? AND account_number = ?', [acc.bank_name, acc.account_number]);
      if (existing.length === 0) {
        await pool.query(
          'INSERT INTO payment_accounts (type, bank_name, branch_name, holder_name, account_number, routing_number) VALUES (?, ?, ?, ?, ?, ?)',
          [acc.type, acc.bank_name, acc.branch_name, acc.holder_name, acc.account_number, acc.routing_number]
        );
      }
    }
    console.log('✅ Bank & MFS accounts (City, EBL, Brac, Islami, DBBL, Mercantile, bKash, Nagad, Rocket) synced in MySQL database');

    // 4.7 Create customers table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        company VARCHAR(255) NULL,
        email VARCHAR(255) NULL,
        phone VARCHAR(100) NULL,
        address TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 4.8 Create invoices table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS invoices (
        id INT AUTO_INCREMENT PRIMARY KEY,
        invoice_no VARCHAR(100) NOT NULL UNIQUE,
        customer_id INT NOT NULL,
        invoice_date DATE NOT NULL,
        due_date DATE NOT NULL,
        subtotal DECIMAL(12,2) DEFAULT 0.00,
        discount DECIMAL(12,2) DEFAULT 0.00,
        tax DECIMAL(12,2) DEFAULT 0.00,
        total_amount DECIMAL(12,2) DEFAULT 0.00,
        paid_amount DECIMAL(12,2) DEFAULT 0.00,
        due_amount DECIMAL(12,2) DEFAULT 0.00,
        status VARCHAR(50) DEFAULT 'Pending',
        notes TEXT NULL,
        terms_conditions TEXT NULL,
        payment_info TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // Auto-migrate existing database tables for new columns
    try { await pool.query('ALTER TABLE invoices ADD COLUMN terms_conditions TEXT NULL AFTER notes'); } catch (err) {}
    try { await pool.query('ALTER TABLE invoices ADD COLUMN payment_info TEXT NULL AFTER terms_conditions'); } catch (err) {}
    try { await pool.query('ALTER TABLE invoice_items ADD COLUMN product_id INT NULL AFTER invoice_id'); } catch (err) {}
    try { await pool.query('ALTER TABLE company ADD COLUMN currency VARCHAR(10) DEFAULT "৳"'); } catch (err) {}


    // 4.9 Create invoice_items table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS invoice_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        invoice_id INT NOT NULL,
        product_id INT NULL,
        description TEXT NOT NULL,
        qty INT DEFAULT 1,
        unit_price DECIMAL(12,2) DEFAULT 0.00,
        discount DECIMAL(12,2) DEFAULT 0.00,
        total DECIMAL(12,2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 4.10 Create transactions table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        txn_no VARCHAR(100) NOT NULL UNIQUE,
        txn_date DATE NOT NULL,
        type VARCHAR(50) NOT NULL,
        source VARCHAR(50) NOT NULL,
        invoice_id INT NULL,
        category_id INT NULL,
        customer_id INT NULL,
        payment_method VARCHAR(50) NOT NULL,
        amount DECIMAL(12,2) NOT NULL,
        reference VARCHAR(100) NULL,
        note TEXT NULL,
        attachment LONGTEXT NULL,
        created_by INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 4.11 Create leads tables
    await pool.query(`
      CREATE TABLE IF NOT EXISTS leads (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NULL,
        phone VARCHAR(100) NULL,
        company VARCHAR(255) NULL,
        address VARCHAR(255) NULL,
        product_id INT NULL,
        source VARCHAR(100) DEFAULT 'Direct',
        status VARCHAR(50) DEFAULT 'New',
        next_followup_date DATE NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    try { await pool.query('ALTER TABLE leads ADD COLUMN product_id INT NULL AFTER company'); } catch (err) {}
    try { await pool.query('ALTER TABLE leads ADD COLUMN address VARCHAR(255) NULL AFTER company'); } catch (err) {}

    // 4.12 Create agreements table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS agreements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        condition_text TEXT NULL,
        description TEXT NULL,
        status VARCHAR(50) DEFAULT 'Pending',
        file_paths JSON NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    try { await pool.query('ALTER TABLE agreements ADD COLUMN status VARCHAR(50) DEFAULT "Pending" AFTER description'); } catch(err) {}
    try { await pool.query('ALTER TABLE agreements DROP COLUMN completed'); } catch(err) {}

    // 4.13 Create tasks table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS tasks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT NULL,
        status VARCHAR(50) DEFAULT 'Pending',
        due_date DATE NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 4.14 Create task_assignments table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS task_assignments (
        task_id INT NOT NULL,
        user_id INT NOT NULL,
        PRIMARY KEY (task_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 4.15 Create task_logs table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS task_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        task_id INT NOT NULL,
        user_id INT NOT NULL,
        type ENUM('note', 'update', 'issue', 'completed') DEFAULT 'note',
        message TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    await pool.query(`
      CREATE TABLE IF NOT EXISTS lead_assignments (
        lead_id INT NOT NULL,
        user_id INT NOT NULL,
        PRIMARY KEY (lead_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    await pool.query(`
      CREATE TABLE IF NOT EXISTS lead_conversations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        lead_id INT NOT NULL,
        user_id INT NOT NULL,
        type ENUM('whatsapp', 'sms', 'email', 'call', 'note') DEFAULT 'note',
        message TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 4.16 Create products table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT NULL,
        price DECIMAL(12,2) DEFAULT 0.00,
        status VARCHAR(50) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // Seed default sample customers if empty
    const [custRows] = await pool.query('SELECT COUNT(*) as count FROM customers');
    if (custRows[0].count === 0) {
      await pool.query(
        'INSERT INTO customers (name, company, email, phone, address) VALUES (?, ?, ?, ?, ?)',
        ['Apex Technologies Ltd', 'Apex Tech', 'finance@apextech.com', '+880 1711-223344', 'Gulshan-2, Dhaka']
      );
      await pool.query(
        'INSERT INTO customers (name, company, email, phone, address) VALUES (?, ?, ?, ?, ?)',
        ['NEXUS Global Solutions', 'NEXUS Inc', 'billing@nexusglobal.com', '+880 1819-556677', 'Banani, Dhaka']
      );
      await pool.query(
        'INSERT INTO customers (name, company, email, phone, address) VALUES (?, ?, ?, ?, ?)',
        ['Green Valley Co-op Society', 'Green Valley', 'info@greenvalley.org', '+880 1912-334455', 'Dhanmondi, Dhaka']
      );
      console.log('✅ Default customers seeded in MySQL database');
    }

    // Seed sample invoices if empty
    const [invRows] = await pool.query('SELECT COUNT(*) as count FROM invoices');
    if (invRows[0].count === 0) {
      const todayStr = new Date().toISOString().split('T')[0];
      await pool.query(
        `INSERT INTO invoices (invoice_no, customer_id, invoice_date, due_date, subtotal, discount, tax, total_amount, paid_amount, due_amount, status, notes)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        ['INV-2026-001', 1, todayStr, todayStr, 1000.00, 0.00, 0.00, 1000.00, 300.00, 700.00, 'Partially Paid', 'LS Micro Banking License & Deployment']
      );
      await pool.query(
        `INSERT INTO invoice_items (invoice_id, description, qty, unit_price, total) VALUES (?, ?, ?, ?, ?)`,
        [1, 'LS Micro Banking Enterprise Subscription', 1, 1000.00, 1000.00]
      );
      await pool.query(
        `INSERT INTO transactions (txn_no, txn_date, type, source, invoice_id, category_id, customer_id, payment_method, amount, reference, note)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        ['TXN-2026-001', todayStr, 'Income', 'Invoice', 1, 1, 1, 'Bkash', 300.00, 'BK-998822', 'First Installment Payment']
      );

      await pool.query(
        `INSERT INTO invoices (invoice_no, customer_id, invoice_date, due_date, subtotal, discount, tax, total_amount, paid_amount, due_amount, status, notes)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        ['INV-2026-002', 2, todayStr, todayStr, 2500.00, 100.00, 0.00, 2400.00, 2400.00, 0.00, 'Paid', 'BluePrint Club Management Software Setup']
      );
      await pool.query(
        `INSERT INTO invoice_items (invoice_id, description, qty, unit_price, total) VALUES (?, ?, ?, ?, ?)`,
        [2, 'BluePrint Annual License', 1, 2500.00, 2500.00]
      );
      await pool.query(
        `INSERT INTO transactions (txn_no, txn_date, type, source, invoice_id, category_id, customer_id, payment_method, amount, reference, note)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        ['TXN-2026-002', todayStr, 'Income', 'Invoice', 2, 2, 2, 'Bank', 2400.00, 'CHQ-778811', 'Full Settlement Payment']
      );
      console.log('✅ Sample Invoices and Ledger Transactions seeded in MySQL database');
    }

    // 5. Seed default admin if empty
    const [rows] = await pool.query('SELECT COUNT(*) as count FROM admin');
    if (rows[0].count === 0) {
      const hashedPassword = await bcrypt.hash('password123', 10);
      const allPerms = JSON.stringify({ menus: { invoice: true, transaction: true, customer: true, lead: true, agreement: true, task: true, settings: true }, actions: { create: true, edit: true, delete: true } });
      await pool.query(
        'INSERT INTO admin (name, email, phone, password, role, status, avatar, permissions) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        ['Super Admin', 'admin@lssoft.com', '+1 234 567 890', hashedPassword, 'Admin', 'active', 'https://ui-avatars.com/api/?name=Super+Admin&background=4f46e5&color=fff', allPerms]
      );
      await pool.query(
        'INSERT INTO admin (name, email, phone, password, role, status, avatar, permissions) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        ['John Doe', 'john@example.com', '+1 987 654 321', hashedPassword, 'Manager', 'active', 'https://ui-avatars.com/api/?name=John+Doe&background=0ea5e9&color=fff', null]
      );
      await pool.query(
        'INSERT INTO admin (name, email, phone, password, role, status, avatar, permissions) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        ['Sarah Smith', 'sarah@example.com', '', hashedPassword, 'Editor', 'inactive', 'https://ui-avatars.com/api/?name=Sarah+Smith&background=10b981&color=fff', null]
      );
      console.log('✅ Default users seeded in MySQL database');
    }

    // 6. Seed default company profile if empty
    const [companyRows] = await pool.query('SELECT COUNT(*) as count FROM company');
    if (companyRows[0].count === 0) {
      await pool.query(
        `INSERT INTO company 
          (company_name, logo, office_title, address, email_primary, email_secondary, phone_primary, phone_secondary, website, tax_id, invoice_footer) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          defaultCompanyData.company_name,
          defaultCompanyData.logo,
          defaultCompanyData.office_title,
          defaultCompanyData.address,
          defaultCompanyData.email_primary,
          defaultCompanyData.email_secondary,
          defaultCompanyData.phone_primary,
          defaultCompanyData.phone_secondary,
          defaultCompanyData.website,
          defaultCompanyData.tax_id,
          defaultCompanyData.invoice_footer
        ]
      );
      console.log('✅ Default company profile seeded in MySQL database');
    }

    console.log(`✅ Connected to MySQL database "${dbConfig.database}" successfully.`);
    return pool;
  } catch (error) {
    console.warn(`⚠️ MySQL connection error (${error.message}).`);
    console.warn('⚡ Activating resilient Mock Database mode for development/testing.');
    isUsingMockDB = true;

    // Seed mock admins
    const hashedPassword = await bcrypt.hash('password123', 10);
    const allPerms = JSON.stringify({ menus: { invoice: true, transaction: true, customer: true, lead: true, agreement: true, task: true, settings: true }, actions: { create: true, edit: true, delete: true } });
    mockAdminTable = [
      { id: 1, name: 'Super Admin', email: 'admin@lssoft.com', phone: '+1 234 567 890', password: hashedPassword, role: 'Admin', status: 'active', avatar: 'https://ui-avatars.com/api/?name=Super+Admin&background=4f46e5&color=fff', permissions: allPerms, created_at: new Date() },
      { id: 2, name: 'John Doe', email: 'john@example.com', phone: '+1 987 654 321', password: hashedPassword, role: 'Manager', status: 'active', avatar: 'https://ui-avatars.com/api/?name=John+Doe&background=0ea5e9&color=fff', permissions: null, created_at: new Date() },
      { id: 3, name: 'Sarah Smith', email: 'sarah@example.com', phone: '', password: hashedPassword, role: 'Editor', status: 'inactive', avatar: 'https://ui-avatars.com/api/?name=Sarah+Smith&background=10b981&color=fff', permissions: null, created_at: new Date() },
      { id: 4, name: 'Mike Ross', email: 'mike@example.com', phone: '+44 123 456 789', password: hashedPassword, role: 'Viewer', status: 'active', avatar: '', permissions: null, created_at: new Date() }
    ];
    return null;
  }
}

let dbPool = null;
const dbInitPromise = initializeDatabase().then((pool) => {
  dbPool = pool;
});

// Helper database functions
async function findAdminByIdentifier(identifier) {
  await dbInitPromise;
  const cleanId = identifier.trim().toLowerCase();

  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query(
      'SELECT * FROM admin WHERE LOWER(email) = ? OR phone = ? LIMIT 1',
      [cleanId, cleanId]
    );
    return rows[0] || null;
  } else {
    return (
      mockAdminTable.find(
        (a) => a.email.toLowerCase() === cleanId || (a.phone && a.phone.trim() === cleanId)
      ) || null
    );
  }
}

async function findAdminById(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT id, name, email, phone, role, status, avatar, permissions, created_at FROM admin WHERE id = ?', [id]);
    return rows[0] || null;
  } else {
    const admin = mockAdminTable.find((a) => a.id === Number(id));
    if (!admin) return null;
    const { password, ...adminData } = admin;
    return adminData;
  }
}

async function getAllUsers() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT id, name, email, phone, role, status, avatar, permissions, created_at FROM admin ORDER BY id ASC');
    return rows.map(r => ({
      ...r,
      status: r.status === 'active' || r.status === true || r.status === 1
    }));
  } else {
    return mockAdminTable.map(u => ({
      id: u.id,
      name: u.name,
      email: u.email,
      phone: u.phone,
      role: u.role,
      status: u.status === 'active' || u.status === true,
      avatar: u.avatar || '',
      permissions: u.permissions || null,
      created_at: u.created_at
    }));
  }
}

async function createUser(userData) {
  await dbInitPromise;
  const hashedPassword = await bcrypt.hash(userData.password || 'password123', 10);
  const statusVal = userData.status ? 'active' : 'inactive';
  const permsStr = userData.permissions ? JSON.stringify(userData.permissions) : null;

  if (!isUsingMockDB && dbPool) {
    const [result] = await dbPool.query(
      'INSERT INTO admin (name, email, phone, password, role, status, avatar, permissions) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [userData.name, userData.email, userData.phone || '', hashedPassword, userData.role || 'Viewer', statusVal, userData.avatar || '', permsStr]
    );
    const newId = result.insertId;
    return {
      id: newId,
      name: userData.name,
      email: userData.email,
      phone: userData.phone || '',
      role: userData.role || 'Viewer',
      status: userData.status,
      avatar: userData.avatar || '',
      permissions: permsStr
    };
  } else {
    const newId = mockAdminTable.length > 0 ? Math.max(...mockAdminTable.map(u => u.id)) + 1 : 1;
    const newUser = {
      id: newId,
      name: userData.name,
      email: userData.email,
      phone: userData.phone || '',
      password: hashedPassword,
      role: userData.role || 'Viewer',
      status: statusVal,
      avatar: userData.avatar || '',
      permissions: permsStr,
      created_at: new Date()
    };
    mockAdminTable.push(newUser);
    return {
      ...newUser,
      status: userData.status
    };
  }
}

async function updateUser(id, userData) {
  await dbInitPromise;
  const statusVal = userData.status ? 'active' : 'inactive';
  const permsStr = userData.permissions ? (typeof userData.permissions === 'string' ? userData.permissions : JSON.stringify(userData.permissions)) : null;

  if (!isUsingMockDB && dbPool) {
    if (userData.password && userData.password.trim() !== '') {
      const hashedPassword = await bcrypt.hash(userData.password, 10);
      await dbPool.query(
        'UPDATE admin SET name = ?, email = ?, phone = ?, role = ?, status = ?, avatar = ?, permissions = ?, password = ? WHERE id = ?',
        [userData.name, userData.email, userData.phone || '', userData.role, statusVal, userData.avatar || '', permsStr, hashedPassword, id]
      );
    } else {
      await dbPool.query(
        'UPDATE admin SET name = ?, email = ?, phone = ?, role = ?, status = ?, avatar = ?, permissions = ? WHERE id = ?',
        [userData.name, userData.email, userData.phone || '', userData.role, statusVal, userData.avatar || '', permsStr, id]
      );
    }
    return { id: Number(id), ...userData, permissions: permsStr };
  } else {
    const index = mockAdminTable.findIndex(u => u.id === Number(id));
    if (index !== -1) {
      if (userData.password && userData.password.trim() !== '') {
        const hashedPassword = await bcrypt.hash(userData.password, 10);
        mockAdminTable[index].password = hashedPassword;
      }
      mockAdminTable[index] = {
        ...mockAdminTable[index],
        name: userData.name,
        email: userData.email,
        phone: userData.phone || '',
        role: userData.role,
        status: statusVal,
        avatar: userData.avatar || '',
        permissions: permsStr
      };
    }
    return { id: Number(id), ...userData, permissions: permsStr };
  }
}

async function deleteUser(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM admin WHERE id = ?', [id]);
  } else {
    mockAdminTable = mockAdminTable.filter(u => u.id !== Number(id));
  }
  return true;
}

// Company Profile DB Functions
async function getCompanyProfile() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM company LIMIT 1');
    if (rows.length > 0) {
      return rows[0];
    }
    return defaultCompanyData;
  } else {
    return mockCompanyProfile;
  }
}

async function updateCompanyProfile(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT id FROM company LIMIT 1');
    if (rows.length > 0) {
      await dbPool.query(
        `UPDATE company SET 
          company_name = ?, currency = ?, logo = ?, favicon = ?, office_title = ?, address = ?, 
          email_primary = ?, email_secondary = ?, phone_primary = ?, phone_secondary = ?, 
          website = ?, tax_id = ?, invoice_footer = ?,
          mail_mailer = ?, mail_host = ?, mail_port = ?, mail_username = ?, mail_password = ?, mail_encryption = ?, mail_from_address = ?, mail_from_name = ?,
          sms_api_url = ?, sms_api_key = ?, sms_sender_id = ?
        WHERE id = ?`,
        [
          data.company_name || 'LSSOFT',
          data.currency || '৳',
          data.logo || '',
          data.favicon || '',
          data.office_title || 'Bangladesh Office',
          data.address || '',
          data.email_primary || '',
          data.email_secondary || '',
          data.phone_primary || '',
          data.phone_secondary || '',
          data.website || '',
          data.tax_id || '',
          data.invoice_footer || '',
          data.mail_mailer || 'smtp',
          data.mail_host || '',
          data.mail_port || 587,
          data.mail_username || '',
          data.mail_password || '',
          data.mail_encryption || 'tls',
          data.mail_from_address || '',
          data.mail_from_name || '',
          data.sms_api_url || '',
          data.sms_api_key || '',
          data.sms_sender_id || '',
          rows[0].id
        ]
      );
    } else {
      await dbPool.query(
        `INSERT INTO company 
          (company_name, currency, logo, favicon, office_title, address, email_primary, email_secondary, phone_primary, phone_secondary, website, tax_id, invoice_footer, mail_mailer, mail_host, mail_port, mail_username, mail_password, mail_encryption, mail_from_address, mail_from_name, sms_api_url, sms_api_key, sms_sender_id) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          data.company_name || 'LSSOFT',
          data.currency || '৳',
          data.logo || '',
          data.favicon || '',
          data.office_title || 'Bangladesh Office',
          data.address || '',
          data.email_primary || '',
          data.email_secondary || '',
          data.phone_primary || '',
          data.phone_secondary || '',
          data.website || '',
          data.tax_id || '',
          data.invoice_footer || '',
          data.mail_mailer || 'smtp',
          data.mail_host || '',
          data.mail_port || 587,
          data.mail_username || '',
          data.mail_password || '',
          data.mail_encryption || 'tls',
          data.mail_from_address || '',
          data.mail_from_name || '',
          data.sms_api_url || '',
          data.sms_api_key || '',
          data.sms_sender_id || ''
        ]
      );
    }
    return await getCompanyProfile();
  } else {
    mockCompanyProfile = {
      ...mockCompanyProfile,
      ...data,
      id: 1
    };
    return mockCompanyProfile;
  }
}

// Applications helper functions
let mockApplicationsTable = [
  {
    id: 1,
    name: 'LS Micro Banking',
    category: 'Finance',
    product_code: 'APP-MICRO-01',
    url: '/products/ls-micro-banking',
    buy_link: 'https://bank.lssoft.xyz/#pricing',
    logo: 'https://st2.depositphotos.com/34422958/47232/i/450/depositphotos_472323394-stock-photo-financial-concept-meaning-microfinance-phrase.jpg',
    description: 'Comprehensive Microfinance Solution for modern banking needs.',
    status: 'active',
    created_at: new Date()
  },
  {
    id: 2,
    name: 'BluePrint',
    category: 'Management',
    product_code: 'APP-CLUB-02',
    url: '/products/blueprint',
    buy_link: 'https://blueprint.lssoft.xyz/pricing',
    logo: '/bluprint.png',
    description: 'Advanced Club Management Software for efficient operations.',
    status: 'active',
    created_at: new Date()
  },
  {
    id: 3,
    name: 'Livo Cloud',
    category: 'Hospitality',
    product_code: 'APP-HOTEL-03',
    url: '/products/livo-cloud',
    buy_link: 'https://livo.lssoft.xyz/pricing/',
    logo: '/livo.png',
    description: 'Streamlined Hotel Management Software for superior guest experiences.',
    status: 'active',
    created_at: new Date()
  },
  {
    id: 4,
    name: 'DMS Pro',
    category: 'DMS',
    product_code: 'APP-DMS-04',
    url: '/products/dms-pro',
    buy_link: 'https://dmspro.cloud/pricing/',
    logo: 'https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTF3-nq4ZJUTdWJ8iSakLgL3B3bjImrAcaDbg&s',
    description: 'All in One Dealership, Retailer Shop, Ecommerce.',
    status: 'active',
    created_at: new Date()
  }
];

async function getAllApplications() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM applications ORDER BY id DESC');
    return rows;
  } else {
    return mockApplicationsTable;
  }
}

async function createApplication(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [result] = await dbPool.query(
      'INSERT INTO applications (name, category, product_code, url, buy_link, logo, description, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [
        data.name,
        data.category || 'General',
        data.product_code || `APP-${Date.now()}`,
        data.url || '',
        data.buy_link || '',
        data.logo || '',
        data.description || '',
        data.status || 'active'
      ]
    );
    const [rows] = await dbPool.query('SELECT * FROM applications WHERE id = ?', [result.insertId]);
    return rows[0];
  } else {
    const newApp = {
      id: mockApplicationsTable.length ? Math.max(...mockApplicationsTable.map(a => a.id)) + 1 : 1,
      name: data.name,
      category: data.category || 'General',
      product_code: data.product_code || `APP-${Date.now()}`,
      url: data.url || '',
      buy_link: data.buy_link || '',
      logo: data.logo || '',
      description: data.description || '',
      status: data.status || 'active',
      created_at: new Date()
    };
    mockApplicationsTable.unshift(newApp);
    return newApp;
  }
}

async function updateApplication(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query(
      'UPDATE applications SET name = ?, category = ?, product_code = ?, url = ?, buy_link = ?, logo = ?, description = ?, status = ? WHERE id = ?',
      [
        data.name,
        data.category || 'General',
        data.product_code,
        data.url || '',
        data.buy_link || '',
        data.logo || '',
        data.description || '',
        data.status || 'active',
        id
      ]
    );
    const [rows] = await dbPool.query('SELECT * FROM applications WHERE id = ?', [id]);
    return rows[0];
  } else {
    const index = mockApplicationsTable.findIndex(a => a.id === Number(id));
    if (index !== -1) {
      mockApplicationsTable[index] = {
        ...mockApplicationsTable[index],
        ...data,
        id: Number(id)
      };
      return mockApplicationsTable[index];
    }
    return null;
  }
}

async function deleteApplication(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM applications WHERE id = ?', [id]);
    return true;
  } else {
    mockApplicationsTable = mockApplicationsTable.filter(a => a.id !== Number(id));
    return true;
  }
}

// Transaction Categories Helper Functions
const seedTxnCategoriesList = [
  ['Software Sales', 'income'],
  ['SaaS Subscription', 'income'],
  ['Website Development', 'income'],
  ['Mobile App Development', 'income'],
  ['Custom Software', 'income'],
  ['Annual Maintenance', 'income'],
  ['Domain & Hosting', 'income'],
  ['Consulting', 'income'],
  ['Training', 'income'],
  ['Commission', 'income'],
  ['Other Income', 'income'],

  ['Salary', 'expense'],
  ['Office Rent', 'expense'],
  ['Internet', 'expense'],
  ['Electricity', 'expense'],
  ['Marketing', 'expense'],
  ['Facebook Ads', 'expense'],
  ['Google Ads', 'expense'],
  ['Domain & Hosting', 'expense'],
  ['VPS / Cloud Server', 'expense'],
  ['Software License', 'expense'],
  ['Office Supplies', 'expense'],
  ['Travel', 'expense'],
  ['Food & Entertainment', 'expense'],
  ['Maintenance', 'expense'],
  ['Miscellaneous', 'expense'],
];

let mockTxnCategoriesTable = seedTxnCategoriesList.map((item, index) => ({
  id: index + 1,
  name: item[0],
  type: item[1],
  status: 1,
  created_by: 1,
  created_at: new Date()
}));

async function getAllTxnCategories() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM txn_categories ORDER BY id ASC');
    return rows;
  } else {
    return mockTxnCategoriesTable;
  }
}

async function createTxnCategory(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [result] = await dbPool.query(
      'INSERT INTO txn_categories (name, type, status, created_by) VALUES (?, ?, ?, 1)',
      [
        data.name,
        data.type || 'income',
        data.status !== undefined ? (data.status ? 1 : 0) : 1
      ]
    );
    const [rows] = await dbPool.query('SELECT * FROM txn_categories WHERE id = ?', [result.insertId]);
    return rows[0];
  } else {
    const newCategory = {
      id: mockTxnCategoriesTable.length ? Math.max(...mockTxnCategoriesTable.map(c => c.id)) + 1 : 1,
      name: data.name,
      type: data.type || 'income',
      status: data.status !== undefined ? (data.status ? 1 : 0) : 1,
      created_by: 1,
      created_at: new Date()
    };
    mockTxnCategoriesTable.push(newCategory);
    return newCategory;
  }
}

async function updateTxnCategory(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query(
      'UPDATE txn_categories SET name = ?, type = ?, status = ? WHERE id = ?',
      [
        data.name,
        data.type || 'income',
        data.status !== undefined ? (data.status ? 1 : 0) : 1,
        id
      ]
    );
    const [rows] = await dbPool.query('SELECT * FROM txn_categories WHERE id = ?', [id]);
    return rows[0];
  } else {
    const index = mockTxnCategoriesTable.findIndex(c => c.id === Number(id));
    if (index !== -1) {
      mockTxnCategoriesTable[index] = {
        ...mockTxnCategoriesTable[index],
        name: data.name,
        type: data.type || 'income',
        status: data.status !== undefined ? (data.status ? 1 : 0) : 1,
        id: Number(id)
      };
      return mockTxnCategoriesTable[index];
    }
    return null;
  }
}

async function deleteTxnCategory(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM txn_categories WHERE id = ?', [id]);
    return true;
  } else {
    mockTxnCategoriesTable = mockTxnCategoriesTable.filter(c => c.id !== Number(id));
    return true;
  }
}

async function getAllCustomers() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM customers ORDER BY id DESC');
    return rows;
  } else {
    return [
      { id: 1, name: 'Apex Technologies Ltd', company: 'Apex Tech', email: 'finance@apextech.com', phone: '+880 1711-223344' },
      { id: 2, name: 'NEXUS Global Solutions', company: 'NEXUS Inc', email: 'billing@nexusglobal.com', phone: '+880 1819-556677' },
      { id: 3, name: 'Green Valley Co-op Society', company: 'Green Valley', email: 'info@greenvalley.org', phone: '+880 1912-334455' }
    ];
  }
}

async function getAllPaymentAccounts() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM payment_accounts ORDER BY type ASC, id ASC');
    return rows;
  }
  return [];
}

async function createPaymentAccount(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [res] = await dbPool.query(
      'INSERT INTO payment_accounts (type, bank_name, branch_name, holder_name, account_number, routing_number) VALUES (?, ?, ?, ?, ?, ?)',
      [data.type || 'Bank', data.bank_name, data.branch_name || '', data.holder_name || '', data.account_number, data.routing_number || '']
    );
    const [rows] = await dbPool.query('SELECT * FROM payment_accounts WHERE id = ?', [res.insertId]);
    return rows[0];
  }
  return null;
}

async function deletePaymentAccount(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM payment_accounts WHERE id = ?', [id]);
    return true;
  }
  return false;
}

async function createCustomer(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [res] = await dbPool.query(
      'INSERT INTO customers (name, company, email, phone, address) VALUES (?, ?, ?, ?, ?)',
      [data.name, data.company || '', data.email || '', data.phone || '', data.address || '']
    );
    const [rows] = await dbPool.query('SELECT * FROM customers WHERE id = ?', [res.insertId]);
    return rows[0];
  }
  return null;
}

async function updateCustomer(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query(
      'UPDATE customers SET name = ?, company = ?, email = ?, phone = ?, address = ? WHERE id = ?',
      [data.name, data.company || '', data.email || '', data.phone || '', data.address || '', id]
    );
    const [rows] = await dbPool.query('SELECT * FROM customers WHERE id = ?', [id]);
    return rows[0];
  }
  return null;
}

async function deleteCustomer(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM customers WHERE id = ?', [id]);
    return true;
  }
  return false;
}

async function getAllInvoices() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query(`
      SELECT i.*, c.name as customer_name, c.company as customer_company,
             (SELECT COUNT(*) FROM transactions t WHERE t.invoice_id = i.id) as payment_count
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.id
      ORDER BY i.id DESC
    `);
    return rows;
  } else {
    return [];
  }
}

async function getInvoiceById(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [invoices] = await dbPool.query(`
      SELECT i.*, c.name as customer_name, c.company as customer_company, c.email as customer_email, c.phone as customer_phone, c.address as customer_address
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.id
      WHERE i.id = ?
    `, [id]);

    if (invoices.length === 0) return null;

    const invoice = invoices[0];
    const [items] = await dbPool.query('SELECT * FROM invoice_items WHERE invoice_id = ?', [id]);
    const [transactions] = await dbPool.query(`
      SELECT t.*, cat.name as category_name
      FROM transactions t
      LEFT JOIN txn_categories cat ON t.category_id = cat.id
      WHERE t.invoice_id = ?
      ORDER BY t.id ASC
    `, [id]);

    return {
      ...invoice,
      items,
      transactions
    };
  }
  return null;
}

async function getNextInvoiceNo(dateStr) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const invDate = new Date(dateStr || Date.now());
    const monthStr = String(invDate.getMonth() + 1).padStart(2, '0');
    const yearStr = String(invDate.getFullYear());

    const [countRows] = await dbPool.query(
      `SELECT COUNT(*) as yearCount FROM invoices WHERE invoice_no LIKE ?`,
      [`%/${yearStr}/%`]
    );
    const nextSerial = String((countRows[0].yearCount || 0) + 1).padStart(4, '0');
    return `LSSOFT/${monthStr}/${yearStr}/${nextSerial}`;
  }
  const d = new Date();
  return `LSSOFT/${String(d.getMonth() + 1).padStart(2, '0')}/${d.getFullYear()}/0001`;
}

async function createInvoice(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const conn = await dbPool.getConnection();
    try {
      await conn.beginTransaction();

      let customerId = data.customer_id;

      // Auto-create Customer if typed in invoice form
      if ((!customerId || customerId === 'new') && data.new_customer && data.new_customer.name) {
        const [cRes] = await conn.query(
          'INSERT INTO customers (name, company, email, phone, address) VALUES (?, ?, ?, ?, ?)',
          [
            data.new_customer.name,
            data.new_customer.company || '',
            data.new_customer.email || '',
            data.new_customer.phone || '',
            data.new_customer.address || ''
          ]
        );
        customerId = cRes.insertId;
      }

      const invDate = new Date(data.invoice_date || Date.now());
      const monthStr = String(invDate.getMonth() + 1).padStart(2, '0');
      const yearStr = String(invDate.getFullYear());

      // Calculate next sequential serial number for current year (e.g., LSSOFT/07/2026/0001)
      const [countRows] = await conn.query(
        `SELECT COUNT(*) as yearCount FROM invoices WHERE invoice_no LIKE ?`,
        [`%/${yearStr}/%`]
      );
      const nextSerial = String((countRows[0].yearCount || 0) + 1).padStart(4, '0');
      const autoInvoiceNo = `LSSOFT/${monthStr}/${yearStr}/${nextSerial}`;

      const invoice_no = data.invoice_no || autoInvoiceNo;
      const subtotal = Number(data.subtotal || 0);
      const discount = Number(data.discount || 0);
      const tax = Number(data.tax || 0);
      const total_amount = subtotal - discount + tax;
      const paid_amount = 0.00;
      const due_amount = total_amount;
      const status = 'Pending';

      const [res] = await conn.query(
        `INSERT INTO invoices (invoice_no, customer_id, invoice_date, due_date, subtotal, discount, tax, total_amount, paid_amount, due_amount, status, notes, terms_conditions, payment_info)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          invoice_no,
          customerId,
          data.invoice_date || new Date().toISOString().split('T')[0],
          data.due_date || new Date().toISOString().split('T')[0],
          subtotal,
          discount,
          tax,
          total_amount,
          paid_amount,
          due_amount,
          status,
          data.notes || '',
          data.terms_conditions || '',
          data.payment_info || ''
        ]
      );

      const invoiceId = res.insertId;

      if (Array.isArray(data.items)) {
        for (const item of data.items) {
          const itemSubtotal = Number(item.qty || 1) * Number(item.unit_price || 0) - Number(item.discount || 0);
          const fullDesc = item.note ? `${item.description}\nNote: ${item.note}` : item.description;
          await conn.query(
            `INSERT INTO invoice_items (invoice_id, product_id, description, qty, unit_price, discount, total)
             VALUES (?, ?, ?, ?, ?, ?, ?)`,
            [invoiceId, item.product_id || null, fullDesc, item.qty || 1, item.unit_price || 0, item.discount || 0, itemSubtotal]
          );
        }
      }

      await conn.commit();
      return await getInvoiceById(invoiceId);
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  }
  return null;
}

// Record Invoice Payment with ACID Database Transaction
async function recordInvoicePayment(invoiceId, paymentData) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const conn = await dbPool.getConnection();
    try {
      await conn.beginTransaction();

      // Lock row for read/update
      const [invoices] = await conn.query('SELECT * FROM invoices WHERE id = ? FOR UPDATE', [invoiceId]);
      if (invoices.length === 0) throw new Error('Invoice not found');

      const invoice = invoices[0];
      const payAmount = Number(paymentData.amount);

      if (payAmount <= 0) {
        throw new Error('Payment amount must be greater than zero');
      }

      if (payAmount > Number(invoice.due_amount)) {
        throw new Error(`Payment amount ($${payAmount}) cannot exceed remaining due amount ($${invoice.due_amount})`);
      }

      const newPaidAmount = Number(invoice.paid_amount) + payAmount;
      const newDueAmount = Number(invoice.total_amount) - newPaidAmount;
      const newStatus = newDueAmount <= 0 ? 'Paid' : 'Partially Paid';

      // Insert new transaction record
      const txn_no = `TXN-${new Date().getFullYear()}-${Math.floor(1000 + Math.random() * 9000)}`;
      await conn.query(
        `INSERT INTO transactions (txn_no, txn_date, type, source, invoice_id, category_id, customer_id, payment_method, amount, reference, note, attachment, created_by)
         VALUES (?, ?, 'Income', 'Invoice', ?, ?, ?, ?, ?, ?, ?, ?, 1)`,
        [
          txn_no,
          paymentData.txn_date || new Date().toISOString().split('T')[0],
          invoiceId,
          paymentData.category_id || 1,
          invoice.customer_id,
          paymentData.payment_method || 'Cash',
          payAmount,
          paymentData.reference || '',
          paymentData.note || '',
          paymentData.attachment || ''
        ]
      );

      // Update invoice amounts & status
      await conn.query(
        `UPDATE invoices SET paid_amount = ?, due_amount = ?, status = ? WHERE id = ?`,
        [newPaidAmount, newDueAmount, newStatus, invoiceId]
      );

      await conn.commit();
      return await getInvoiceById(invoiceId);
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  }
  return null;
}

async function getAllTransactions() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query(`
      SELECT t.*, 
             i.invoice_no, 
             c.name as customer_name,
             cat.name as category_name
      FROM transactions t
      LEFT JOIN invoices i ON t.invoice_id = i.id
      LEFT JOIN customers c ON t.customer_id = c.id
      LEFT JOIN txn_categories cat ON t.category_id = cat.id
      ORDER BY t.id DESC
    `);
    return rows;
  }
  return [];
}

async function createManualTransaction(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const txn_no = `TXN-${new Date().getFullYear()}-${Math.floor(1000 + Math.random() * 9000)}`;
    const [res] = await dbPool.query(
      `INSERT INTO transactions (txn_no, txn_date, type, source, category_id, customer_id, payment_method, amount, reference, note, attachment, created_by)
       VALUES (?, ?, ?, 'Manual', ?, ?, ?, ?, ?, ?, ?, 1)`,
      [
        txn_no,
        data.txn_date || new Date().toISOString().split('T')[0],
        data.type || 'Expense',
        data.category_id || null,
        data.customer_id || null,
        data.payment_method || 'Cash',
        Number(data.amount || 0),
        data.reference || '',
        data.note || '',
        data.attachment || ''
      ]
    );

    const [rows] = await dbPool.query('SELECT * FROM transactions WHERE id = ?', [res.insertId]);
    return rows[0];
  }
  return null;
}


async function getTransactionById(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query(`
      SELECT t.*, 
             i.invoice_no, 
             c.name as customer_name,
             c.company as customer_company,
             c.phone as customer_phone,
             c.email as customer_email,
             c.address as customer_address,
             cat.name as category_name
      FROM transactions t
      LEFT JOIN invoices i ON t.invoice_id = i.id
      LEFT JOIN customers c ON t.customer_id = c.id
      LEFT JOIN txn_categories cat ON t.category_id = cat.id
      WHERE t.id = ?
    `, [id]);
    return rows[0] || null;
  }
  return null;
}

async function updateTransaction(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const txn = await getTransactionById(id);
    if (!txn) throw new Error('Transaction not found');

    const isInvoicePayment = !!txn.invoice_id;
    let newAmount = txn.amount;

    if (!isInvoicePayment) {
      newAmount = Number(data.amount || txn.amount);
    }

    await dbPool.query(
      `UPDATE transactions 
       SET txn_date = ?, category_id = ?, payment_method = ?, reference = ?, note = ?, amount = ?
       WHERE id = ?`,
      [
        data.txn_date || txn.txn_date,
        data.category_id || txn.category_id,
        data.payment_method || txn.payment_method,
        data.reference || txn.reference,
        data.note || txn.note,
        newAmount,
        id
      ]
    );

    return await getTransactionById(id);
  }
  return null;
}

// Delete Transaction
async function deleteTransaction(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const conn = await dbPool.getConnection();
    try {
      await conn.beginTransaction();

      // Get the transaction to check if it's tied to an invoice
      const [txns] = await conn.query('SELECT * FROM transactions WHERE id = ?', [id]);
      if (txns.length === 0) throw new Error('Transaction not found');
      
      const txn = txns[0];

      // If it's an invoice payment, recalculate invoice
      if (txn.invoice_id) {
        const [invoices] = await conn.query('SELECT * FROM invoices WHERE id = ? FOR UPDATE', [txn.invoice_id]);
        if (invoices.length > 0) {
          const invoice = invoices[0];
          const newPaidAmount = Math.max(0, Number(invoice.paid_amount) - Number(txn.amount));
          const newDueAmount = Number(invoice.total_amount) - newPaidAmount;
          
          let newStatus = 'Pending';
          if (newDueAmount <= 0) {
            newStatus = 'Paid';
          } else if (newPaidAmount > 0) {
            newStatus = 'Partially Paid';
          }

          await conn.query(
            'UPDATE invoices SET paid_amount = ?, due_amount = ?, status = ? WHERE id = ?',
            [newPaidAmount, newDueAmount, newStatus, txn.invoice_id]
          );
        }
      }

      // Delete the transaction itself
      await conn.query('DELETE FROM transactions WHERE id = ?', [id]);

      await conn.commit();
      return true;
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  }
  return false;
}

// Update Invoice
async function updateInvoice(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const conn = await dbPool.getConnection();
    try {
      await conn.beginTransaction();

      const [invoices] = await conn.query('SELECT * FROM invoices WHERE id = ? FOR UPDATE', [id]);
      if (invoices.length === 0) throw new Error('Invoice not found');
      
      const currentInvoice = invoices[0];
      const paid_amount = Number(currentInvoice.paid_amount);

      let customerId = data.customer_id;
      if ((!customerId || customerId === 'new') && data.new_customer && data.new_customer.name) {
        const [cRes] = await conn.query(
          'INSERT INTO customers (name, company, email, phone, address) VALUES (?, ?, ?, ?, ?)',
          [
            data.new_customer.name,
            data.new_customer.company || '',
            data.new_customer.email || '',
            data.new_customer.phone || '',
            data.new_customer.address || ''
          ]
        );
        customerId = cRes.insertId;
      }

      const subtotal = Number(data.subtotal || 0);
      const discount = Number(data.discount || 0);
      const tax = Number(data.tax || 0);
      const total_amount = subtotal - discount + tax;
      const due_amount = total_amount - paid_amount;
      
      let status = 'Pending';
      if (due_amount <= 0) {
        status = 'Paid';
      } else if (paid_amount > 0) {
        status = 'Partially Paid';
      }

      await conn.query(
        `UPDATE invoices SET customer_id = ?, invoice_date = ?, due_date = ?, subtotal = ?, discount = ?, tax = ?, total_amount = ?, due_amount = ?, status = ?, notes = ?, terms_conditions = ?, payment_info = ?
         WHERE id = ?`,
        [
          customerId,
          data.invoice_date || new Date().toISOString().split('T')[0],
          data.due_date || new Date().toISOString().split('T')[0],
          subtotal,
          discount,
          tax,
          total_amount,
          due_amount,
          status,
          data.notes || '',
          data.terms_conditions || '',
          data.payment_info || '',
          id
        ]
      );

      // Refresh items
      await conn.query('DELETE FROM invoice_items WHERE invoice_id = ?', [id]);
      
      if (Array.isArray(data.items)) {
        for (const item of data.items) {
          const itemSubtotal = Number(item.qty || 1) * Number(item.unit_price || 0) - Number(item.discount || 0);
          const fullDesc = item.note ? `${item.description}\nNote: ${item.note}` : item.description;
          await conn.query(
            `INSERT INTO invoice_items (invoice_id, product_id, description, qty, unit_price, discount, total)
             VALUES (?, ?, ?, ?, ?, ?, ?)`,
            [id, item.product_id || null, fullDesc, item.qty || 1, item.unit_price || 0, item.discount || 0, itemSubtotal]
          );
        }
      }

      await conn.commit();
      return await getInvoiceById(id);
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  }
  return null;
}

// Delete Invoice
async function deleteInvoice(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const conn = await dbPool.getConnection();
    try {
      await conn.beginTransaction();

      await conn.query('DELETE FROM transactions WHERE invoice_id = ?', [id]);
      await conn.query('DELETE FROM invoice_items WHERE invoice_id = ?', [id]);
      await conn.query('DELETE FROM invoices WHERE id = ?', [id]);

      await conn.commit();
      return true;
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  }
  return false;
}

let mockLeadsTable = [
  { id: 1, name: 'John Smith', email: 'john@example.com', phone: '+1234567890', company: 'ABC Corp', product_id: null, source: 'Website', status: 'New', next_followup_date: null, created_at: new Date() }
];
let mockLeadAssignmentsTable = [];
let mockLeadConversationsTable = [];

async function getAllLeads() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query(`
      SELECT l.*, 
             (SELECT JSON_ARRAYAGG(user_id) FROM lead_assignments WHERE lead_id = l.id) as assigned_users
      FROM leads l ORDER BY l.id DESC
    `);
    return rows.map(r => ({ ...r, assigned_users: typeof r.assigned_users === 'string' ? JSON.parse(r.assigned_users || '[]') : (r.assigned_users || []) }));
  } else {
    return mockLeadsTable.map(l => ({
      ...l,
      assigned_users: mockLeadAssignmentsTable.filter(a => a.lead_id === l.id).map(a => a.user_id)
    }));
  }
}

async function getLeadById(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM leads WHERE id = ?', [id]);
    if (rows.length === 0) return null;
    const lead = rows[0];
    const [assignments] = await dbPool.query('SELECT user_id FROM lead_assignments WHERE lead_id = ?', [id]);
    lead.assigned_users = assignments.map(a => a.user_id);
    const [conversations] = await dbPool.query('SELECT c.*, a.name as user_name FROM lead_conversations c LEFT JOIN admin a ON c.user_id = a.id WHERE c.lead_id = ? ORDER BY c.created_at DESC', [id]);
    lead.conversations = conversations;
    return lead;
  } else {
    const lead = mockLeadsTable.find(l => l.id === Number(id));
    if (!lead) return null;
    return {
      ...lead,
      assigned_users: mockLeadAssignmentsTable.filter(a => a.lead_id === lead.id).map(a => a.user_id),
      conversations: mockLeadConversationsTable.filter(c => c.lead_id === lead.id).sort((a,b) => b.created_at - a.created_at)
    };
  }
}

async function createLead(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [result] = await dbPool.query(
      'INSERT INTO leads (name, email, phone, company, address, product_id, source, status, next_followup_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [data.name, data.email || null, data.phone || null, data.company || null, data.address || null, data.product_id || null, data.source || 'Direct', data.status || 'New', data.next_followup_date || null]
    );
    return { ...data, id: result.insertId };
  } else {
    const newId = mockLeadsTable.length > 0 ? Math.max(...mockLeadsTable.map(l => l.id)) + 1 : 1;
    const newLead = { id: newId, name: data.name, email: data.email, phone: data.phone, company: data.company, address: data.address || null, product_id: data.product_id || null, source: data.source || 'Direct', status: data.status || 'New', next_followup_date: data.next_followup_date || null, created_at: new Date() };
    mockLeadsTable.unshift(newLead);
    return newLead;
  }
}

async function updateLead(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query(
      'UPDATE leads SET name=?, email=?, phone=?, company=?, address=?, product_id=?, source=?, status=?, next_followup_date=? WHERE id=?',
      [data.name, data.email || null, data.phone || null, data.company || null, data.address || null, data.product_id || null, data.source || 'Direct', data.status || 'New', data.next_followup_date || null, id]
    );
    return true;
  } else {
    const index = mockLeadsTable.findIndex(l => l.id === Number(id));
    if (index !== -1) {
      mockLeadsTable[index] = { ...mockLeadsTable[index], ...data };
      return true;
    }
    return false;
  }
}

async function assignUsersToLead(lead_id, user_ids) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM lead_assignments WHERE lead_id = ?', [lead_id]);
    for (const uid of user_ids) {
      await dbPool.query('INSERT INTO lead_assignments (lead_id, user_id) VALUES (?, ?)', [lead_id, uid]);
    }
    return true;
  } else {
    mockLeadAssignmentsTable = mockLeadAssignmentsTable.filter(a => a.lead_id !== Number(lead_id));
    for (const uid of user_ids) {
      mockLeadAssignmentsTable.push({ lead_id: Number(lead_id), user_id: Number(uid) });
    }
    return true;
  }
}

async function addLeadConversation(lead_id, user_id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query(
      'INSERT INTO lead_conversations (lead_id, user_id, type, message) VALUES (?, ?, ?, ?)',
      [lead_id, user_id, data.type || 'note', data.message]
    );
    if (data.next_followup_date) {
      await dbPool.query('UPDATE leads SET next_followup_date = ?, status = ? WHERE id = ?', [data.next_followup_date, data.status || 'In Progress', lead_id]);
    }
    return true;
  } else {
    mockLeadConversationsTable.unshift({
      id: mockLeadConversationsTable.length + 1,
      lead_id: Number(lead_id),
      user_id: Number(user_id),
      type: data.type || 'note',
      message: data.message,
      created_at: new Date(),
      user_name: 'Admin User'
    });
    const l = mockLeadsTable.find(x => x.id === Number(lead_id));
    if (l && data.next_followup_date) {
      l.next_followup_date = data.next_followup_date;
      l.status = data.status || l.status;
    }
    return true;
  }
}

// --- PRODUCTS DB FUNCTIONS ---
async function getAllProducts() {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM products ORDER BY id DESC');
    return rows;
  } else {
    return mockProductsTable;
  }
}

async function getProductById(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [rows] = await dbPool.query('SELECT * FROM products WHERE id = ?', [id]);
    return rows[0] || null;
  } else {
    return mockProductsTable.find(p => p.id === Number(id)) || null;
  }
}

async function createProduct(data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    const [result] = await dbPool.query(
      'INSERT INTO products (name, description, price, status) VALUES (?, ?, ?, ?)',
      [data.name, data.description, data.price || 0.00, data.status || 'active']
    );
    return await getProductById(result.insertId);
  } else {
    const newId = mockProductsTable.length > 0 ? Math.max(...mockProductsTable.map(p => p.id)) + 1 : 1;
    const newProduct = { id: newId, name: data.name, description: data.description, price: data.price || 0.00, status: data.status || 'active', created_at: new Date() };
    mockProductsTable.push(newProduct);
    return newProduct;
  }
}

async function updateProduct(id, data) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query(
      'UPDATE products SET name = ?, description = ?, price = ?, status = ? WHERE id = ?',
      [data.name, data.description, data.price, data.status, id]
    );
    return await getProductById(id);
  } else {
    const index = mockProductsTable.findIndex(p => p.id === Number(id));
    if (index !== -1) {
      mockProductsTable[index] = { ...mockProductsTable[index], ...data };
      return mockProductsTable[index];
    }
    return null;
  }
}

async function deleteProduct(id) {
  await dbInitPromise;
  if (!isUsingMockDB && dbPool) {
    await dbPool.query('DELETE FROM products WHERE id = ?', [id]);
  } else {
    mockProductsTable = mockProductsTable.filter(p => p.id !== Number(id));
  }
  return true;
}

module.exports = {
  getAllProducts,
  getProductById,
  createProduct,
  updateProduct,
  deleteProduct,
  findAdminByIdentifier,
  findAdminById,
  getAllUsers,
  createUser,
  updateUser,
  deleteUser,
  getCompanyProfile,
  updateCompanyProfile,
  getAllApplications,
  createApplication,
  updateApplication,
  deleteApplication,
  getAllTxnCategories,
  createTxnCategory,
  updateTxnCategory,
  deleteTxnCategory,
  getAllCustomers,
  createCustomer,
  updateCustomer,
  deleteCustomer,
  getAllPaymentAccounts,
  createPaymentAccount,
  deletePaymentAccount,
  getAllInvoices,
  getInvoiceById,
  getNextInvoiceNo,
  createInvoice,
  updateInvoice,
  deleteInvoice,
  recordInvoicePayment,
  getAllTransactions,
  getTransactionById,
  createManualTransaction,
  updateTransaction,
  deleteTransaction,
  isMockDB: () => isUsingMockDB,
  getAllLeads,
  getLeadById,
  createLead,
  updateLead,
  assignUsersToLead,
  addLeadConversation,
  getDbPool: () => dbPool
};

