const pool = require("./database");

async function initAccountsTables() {
  try {
    // 1. Create accounts_invoices table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS accounts_invoices (
        invoice_id VARCHAR(50) PRIMARY KEY,
        order_id VARCHAR(50),
        cus_id VARCHAR(50),
        customer_name VARCHAR(255),
        invoice_date DATE DEFAULT CURRENT_DATE,
        due_date DATE,
        subtotal NUMERIC(12, 2) DEFAULT 0,
        discount NUMERIC(12, 2) DEFAULT 0,
        gst_percent NUMERIC(5, 2) DEFAULT 18,
        gst_amount NUMERIC(12, 2) DEFAULT 0,
        net_amount NUMERIC(12, 2) DEFAULT 0,
        paid_amount NUMERIC(12, 2) DEFAULT 0,
        balance NUMERIC(12, 2) DEFAULT 0,
        status VARCHAR(50) DEFAULT 'Unpaid',
        items JSONB,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);
    console.log("Created table accounts_invoices");

    // 2. Create accounts_payments table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS accounts_payments (
        payment_id SERIAL PRIMARY KEY,
        invoice_id VARCHAR(50) REFERENCES accounts_invoices(invoice_id) ON DELETE CASCADE,
        payment_date DATE DEFAULT CURRENT_DATE,
        amount NUMERIC(12, 2) NOT NULL,
        payment_mode VARCHAR(50) DEFAULT 'Bank Transfer',
        reference_no VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);
    console.log("Created table accounts_payments");

    // 3. Insert initial demo invoices if empty
    const countRes = await pool.query("SELECT COUNT(*) FROM accounts_invoices");
    if (parseInt(countRes.rows[0].count) === 0) {
      await pool.query(`
        INSERT INTO accounts_invoices 
        (invoice_id, order_id, cus_id, customer_name, invoice_date, due_date, subtotal, discount, gst_percent, gst_amount, net_amount, paid_amount, balance, status, items)
        VALUES 
        ('INV-2026-0001', 'ORD-000161', 'C-0042', 'Samyu', '2026-07-28', '2026-08-15', 85000, 2000, 18, 14940, 97940, 97940, 0, 'Paid', '[{"subOrderId": "SUB_00180", "desc": "flower print on Polyester", "qty": 1000, "rate": 85}]'::jsonb),
        ('INV-2026-0002', 'ORD-000135', 'C-0001', 'High Breedd Fashions', '2026-07-27', '2026-08-10', 66000, 1500, 18, 11610, 76110, 40000, 36110, 'Partially Paid', '[{"subOrderId": "SUB_00146", "desc": "bow design on white fleece", "qty": 600, "rate": 110}]'::jsonb),
        ('INV-2026-0003', 'ORD-000129', 'C-0004', 'Arrow Tex', '2026-07-26', '2026-08-15', 48000, 0, 18, 8640, 56640, 0, 56640, 'Unpaid', '[{"subOrderId": "SUB_00140", "desc": "h-sts/brown design on S/J", "qty": 400, "rate": 120}]'::jsonb);
      `);

      await pool.query(`
        INSERT INTO accounts_payments (invoice_id, payment_date, amount, payment_mode, reference_no)
        VALUES 
        ('INV-2026-0001', '2026-07-29', 97940, 'Bank Transfer', 'TXN9876543'),
        ('INV-2026-0002', '2026-07-28', 40000, 'GPay', 'UPI9823719');
      `);
      console.log("Seeded initial invoice & payment records.");
    }

  } catch (err) {
    console.error("Error setting up accounts DB tables:", err);
  } finally {
    pool.end();
  }
}

initAccountsTables();
