const { pool } = require("../../config/db");

/* ─────────────────────────────────────────────────────────────────────────────
   SALES INVOICES
───────────────────────────────────────────────────────────────────────────── */

const getReceivableInvoices = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized - No orgId" });

    const page   = parseInt(req.query.page)  || 1;
    const limit  = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;
    const status = req.query.status;
    const search = req.query.search || "";

    let whereClause = `si.org_id = $1 AND si.is_deleted = false`;
    const params = [orgId];
    let p = 1;

    if (status && status !== "ALL") {
      params.push(status.toUpperCase());
      whereClause += ` AND UPPER(si.status) = $${++p}`;
    }
    if (search) {
      params.push(`%${search}%`);
      whereClause += ` AND (si.invoice_number ILIKE $${++p} OR si.customer_name ILIKE $${p})`;
    }

    const countResult = await pool.query(
      `SELECT COUNT(*) FROM sales_invoices si WHERE ${whereClause}`,
      params.slice(0, p)
    );
    const total = parseInt(countResult.rows[0].count);

    params.push(limit, offset);
    const limitP = ++p, offsetP = ++p;

    const invoicesQuery = `
      SELECT
        si.id, si.invoice_number, si.sales_order_id, si.sales_order_number,
        si.customer_code, si.customer_name, si.customer_address, si.customer_gst,
        si.invoice_date, si.due_date,
        si.amount, si.total_discount, si.total_tax, si.total_amount,
        si.advance_adjusted_amount, si.balance_amount, si.balance_payable_amount,
        si.status, si.notes, si.created_at,
        COALESCE(SUM(ar.amount), 0) AS paid_amount,
        COALESCE(
          json_agg(
            json_build_object(
              'id',           ar.id,
              'payment_date', ar.payment_date,
              'amount',       ar.amount,
              'payment_mode', ar.payment_mode,
              'reference_no', ar.reference_no,
              'notes',        ar.notes,
              'status',       ar.status,
              'created_at',   ar.created_at
            ) ORDER BY ar.payment_date DESC
          ) FILTER (WHERE ar.id IS NOT NULL AND ar.is_deleted = false),
          '[]'::json
        ) AS payments
      FROM sales_invoices si
      LEFT JOIN accounts_receivable ar
             ON ar.invoice_id = si.id
            AND ar.is_deleted = false
            AND ar.payment_type = 'regular'
      WHERE ${whereClause}
      GROUP BY si.id
      ORDER BY si.created_at DESC
      LIMIT $${limitP} OFFSET $${offsetP}
    `;

    const invoicesResult = await pool.query(invoicesQuery, params);

    const invoices = invoicesResult.rows.map(inv => {
      const paidAmount  = parseFloat(inv.paid_amount)  || 0;
      const totalAmount = parseFloat(inv.total_amount) || 0;
      let derivedStatus = "UNPAID";
      if (paidAmount >= totalAmount && totalAmount > 0) derivedStatus = "PAID";
      else if (paidAmount > 0) derivedStatus = "PARTIAL";
      if (inv.due_date && derivedStatus !== "PAID") {
        const due   = new Date(inv.due_date); due.setHours(0, 0, 0, 0);
        const today = new Date();             today.setHours(0, 0, 0, 0);
        if (due < today) derivedStatus = "OVERDUE";
      }
      return {
        ...inv,
        paid_amount:      paidAmount,
        remaining_amount: totalAmount - paidAmount,
        derived_status:   derivedStatus,
      };
    });

    res.json({
      success: true,
      data: invoices,
      pagination: { total, page, limit, totalPages: Math.ceil(total / limit) },
    });
  } catch (err) {
    console.error("getReceivableInvoices ERROR:", err.stack);
    res.status(500).json({ success: false, message: "Failed to fetch invoices", error: err.message });
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const getInvoiceWithPayments = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { id } = req.params;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    const result = await pool.query(
      `SELECT
        si.id, si.invoice_number, si.sales_order_id, si.sales_order_number,
        si.customer_code, si.customer_name, si.customer_address, si.customer_gst,
        si.invoice_date, si.due_date,
        si.amount, si.total_discount, si.total_tax, si.total_amount,
        si.advance_adjusted_amount, si.balance_amount, si.balance_payable_amount,
        si.status, si.notes, si.created_at,
        COALESCE(SUM(ar.amount), 0) AS paid_amount,
        COALESCE(
          json_agg(
            json_build_object(
              'id',           ar.id,
              'payment_date', ar.payment_date,
              'amount',       ar.amount,
              'payment_mode', ar.payment_mode,
              'reference_no', ar.reference_no,
              'notes',        ar.notes,
              'status',       ar.status,
              'created_at',   ar.created_at
            ) ORDER BY ar.payment_date DESC
          ) FILTER (WHERE ar.id IS NOT NULL AND ar.is_deleted = false),
          '[]'::json
        ) AS payments
       FROM sales_invoices si
       LEFT JOIN accounts_receivable ar
              ON ar.invoice_id = si.id
             AND ar.is_deleted = false
             AND ar.payment_type = 'regular'
       WHERE si.id = $1 AND si.org_id = $2 AND si.is_deleted = false
       GROUP BY si.id`,
      [id, orgId]
    );

    if (!result.rows.length)
      return res.status(404).json({ success: false, message: "Invoice not found" });

    const inv        = result.rows[0];
    const paidAmount = parseFloat(inv.paid_amount) || 0;
    res.json({
      success: true,
      data: {
        ...inv,
        paid_amount:      paidAmount,
        remaining_amount: parseFloat(inv.total_amount) - paidAmount,
        payments:         inv.payments || [],
      },
    });
  } catch (err) {
    console.error("getInvoiceWithPayments ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to fetch invoice" });
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const addPayment = async (req, res) => {
  const client = await pool.connect();
  try {
    const userId = req.session?.user?.id;
    const orgId  = req.session?.user?.orgId;
    const { id } = req.params;
    const { payment_date, amount, payment_mode, reference_no, notes } = req.body;

    if (!userId || !orgId) return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!payment_date || !amount || !payment_mode)
      return res.status(400).json({ success: false, message: "payment_date, amount and payment_mode are required" });

    await client.query("BEGIN");

    const invoiceCheck = await client.query(
      `SELECT si.id, si.total_amount,
        COALESCE(SUM(ar.amount), 0) AS paid_amount
       FROM sales_invoices si
       LEFT JOIN accounts_receivable ar
              ON ar.invoice_id = si.id
             AND ar.is_deleted = false
             AND ar.payment_type = 'regular'
       WHERE si.id = $1 AND si.org_id = $2 AND si.is_deleted = false
       GROUP BY si.id`,
      [id, orgId]
    );
    if (!invoiceCheck.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Invoice not found" });
    }

    const invoice       = invoiceCheck.rows[0];
    const currentPaid   = parseFloat(invoice.paid_amount)  || 0;
    const totalAmount   = parseFloat(invoice.total_amount) || 0;
    const newPaidAmount = currentPaid + parseFloat(amount);

    if (newPaidAmount > totalAmount) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Exceeds remaining balance. Remaining: ${(totalAmount - currentPaid).toFixed(2)}`,
      });
    }

    await client.query(
      `INSERT INTO accounts_receivable
         (invoice_id, payment_date, amount, payment_mode, reference_no, notes,
          status, payment_type, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, 'completed', 'regular', $7, $8)`,
      [id, payment_date, parseFloat(amount), payment_mode,
       reference_no || null, notes || null, orgId, userId]
    );

    const newStatus = newPaidAmount >= totalAmount ? "PAID"
                    : newPaidAmount > 0            ? "PARTIAL"
                    : "PENDING";
    await client.query(
      `UPDATE sales_invoices
       SET status = $1, updated_at = NOW(), updated_by = $2
       WHERE id = $3`,
      [newStatus, userId, id]
    );

    await client.query("COMMIT");
    res.json({ success: true, message: "Payment recorded successfully" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("addPayment ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to add payment" });
  } finally {
    client.release();
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const getPaymentSummary = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    const result = await pool.query(
      `SELECT
        COALESCE(SUM(si.total_amount), 0)                                                                           AS total_receivable,
        COALESCE(SUM(ar.amount), 0)                                                                                 AS total_collected,
        COUNT(DISTINCT si.id)                                                                                       AS total_invoices,
        COUNT(DISTINCT si.id) FILTER (WHERE UPPER(si.status) = 'PAID')                                             AS paid_invoices,
        COUNT(DISTINCT si.id) FILTER (WHERE UPPER(si.status) != 'PAID')                                            AS pending_invoices,
        COUNT(DISTINCT si.id) FILTER (WHERE si.due_date < CURRENT_DATE AND UPPER(si.status) != 'PAID')             AS overdue_invoices,
        COALESCE(SUM(si.total_amount) FILTER (WHERE si.due_date < CURRENT_DATE AND UPPER(si.status) != 'PAID'), 0) AS overdue_amount,
        COALESCE(SUM(si.total_amount - COALESCE(ar.amount, 0)) FILTER (WHERE UPPER(si.status) != 'PAID'), 0)       AS outstanding_balance
       FROM sales_invoices si
       LEFT JOIN (
         SELECT invoice_id, SUM(amount) AS amount
         FROM accounts_receivable
         WHERE is_deleted = false AND payment_type = 'regular'
         GROUP BY invoice_id
       ) ar ON si.id = ar.invoice_id
       WHERE si.org_id = $1 AND si.is_deleted = false`,
      [orgId]
    );
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("getPaymentSummary ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to fetch summary" });
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const deleteInvoice = async (req, res) => {
  const client = await pool.connect();
  try {
    const userId = req.session?.user?.id;
    const orgId  = req.session?.user?.orgId;
    const { id } = req.params;
    if (!userId || !orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    await client.query("BEGIN");

    const check = await client.query(
      `SELECT id, invoice_number FROM sales_invoices
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );
    if (!check.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Invoice not found" });
    }

    await client.query(
      `UPDATE sales_invoices
       SET is_deleted = true, updated_at = NOW(), updated_by = $1
       WHERE id = $2`,
      [userId, id]
    );
    await client.query(
      `UPDATE accounts_receivable
       SET is_deleted = true, updated_at = NOW(), updated_by = $1
       WHERE invoice_id = $2 AND is_deleted = false`,
      [userId, id]
    );

    await client.query("COMMIT");
    res.json({ success: true, message: `Invoice ${check.rows[0].invoice_number} deleted successfully` });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("deleteInvoice ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to delete invoice", error: err.message });
  } finally {
    client.release();
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const deletePayment = async (req, res) => {
  const client = await pool.connect();
  try {
    const userId        = req.session?.user?.id;
    const orgId         = req.session?.user?.orgId;
    const { paymentId } = req.params;
    if (!userId || !orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    await client.query("BEGIN");

    const check = await client.query(
      `SELECT ar.id, ar.amount, ar.invoice_id, si.total_amount,
              COALESCE(SUM(ar2.amount), 0) AS remaining_paid
       FROM accounts_receivable ar
       JOIN sales_invoices si ON ar.invoice_id = si.id
       LEFT JOIN accounts_receivable ar2
              ON ar2.invoice_id = si.id
             AND ar2.is_deleted = false
             AND ar2.payment_type = 'regular'
             AND ar2.id != ar.id
       WHERE ar.id = $1 AND ar.org_id = $2 AND ar.is_deleted = false
       GROUP BY ar.id, ar.amount, ar.invoice_id, si.total_amount`,
      [paymentId, orgId]
    );
    if (!check.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Payment not found" });
    }

    const payment   = check.rows[0];
    const newPaid   = parseFloat(payment.remaining_paid);
    const total     = parseFloat(payment.total_amount);
    const newStatus = newPaid >= total ? "PAID" : newPaid > 0 ? "PARTIAL" : "PENDING";

    await client.query(
      `UPDATE accounts_receivable
       SET is_deleted = true, updated_at = NOW(), updated_by = $1
       WHERE id = $2`,
      [userId, paymentId]
    );
    await client.query(
      `UPDATE sales_invoices
       SET status = $1, updated_at = NOW(), updated_by = $2
       WHERE id = $3`,
      [newStatus, userId, payment.invoice_id]
    );

    await client.query("COMMIT");
    res.json({ success: true, message: "Payment deleted and invoice status updated" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("deletePayment ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to delete payment" });
  } finally {
    client.release();
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const hardDeleteReceivable = async (req, res) => {
  const client = await pool.connect();
  try {
    const userId = req.session?.user?.id;
    const orgId  = req.session?.user?.orgId;
    const { id } = req.params;
    if (!userId || !orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    await client.query("BEGIN");

    const check = await client.query(
      `SELECT ar.id FROM accounts_receivable ar
       JOIN sales_invoices si ON ar.invoice_id = si.id
       WHERE ar.id = $1 AND si.org_id = $2`,
      [id, orgId]
    );
    if (!check.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Receivable entry not found" });
    }

    await client.query(`DELETE FROM accounts_receivable WHERE id = $1`, [id]);
    await client.query("COMMIT");
    res.json({ success: true, message: "Receivable entry permanently deleted" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("hardDeleteReceivable ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to permanently delete" });
  } finally {
    client.release();
  }
};

/* ─────────────────────────────────────────────────────────────────────────────
   PROFORMA INVOICES
───────────────────────────────────────────────────────────────────────────── */

const getProformaInvoices = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    const page   = parseInt(req.query.page)  || 1;
    const limit  = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;
    const search = req.query.search || "";
    const status = req.query.status;

    let where = `pi.org_id = $1 AND pi.is_deleted = false`;
    const params = [orgId];
    let p = 1;

    if (search) {
      params.push(`%${search}%`);
      where += ` AND (pi.proforma_number ILIKE $${++p} OR pi.customer_name ILIKE $${p})`;
    }

    const countResult = await pool.query(
      `SELECT COUNT(*) FROM proforma_invoices pi WHERE ${where}`,
      params.slice(0, p)
    );
    const total = parseInt(countResult.rows[0].count);

    params.push(limit, offset);
    const limitP = ++p, offsetP = ++p;

    const query = `
      SELECT
        pi.id,
        pi.proforma_number,
        pi.sales_order_id,
        pi.sales_order_number,
        pi.customer_code,
        pi.customer_name,
        pi.proforma_date,
        pi.advance_percent,
        pi.advance_amount,
        pi.notes,
        pi.status,
        pi.created_at,

        -- Linked sales invoice via direct FK
        si.id                      AS sales_invoice_id,
        si.invoice_number          AS sales_invoice_number,
        si.total_amount            AS sales_invoice_total,
        si.advance_adjusted_amount,
        si.balance_amount,
        si.balance_payable_amount,

        -- Sales order advance status
        so.advance_payment_status,
        so.advance_payment_amount  AS so_advance_payment_amount,

        -- Advance payments via accounts_receivable (payment_type = 'advance')
        COALESCE(SUM(ar.amount), 0) AS paid_advance,
        COALESCE(
          json_agg(
            json_build_object(
              'id',           ar.id,
              'payment_date', ar.payment_date,
              'amount',       ar.amount,
              'payment_mode', ar.payment_mode,
              'reference_no', ar.reference_no,
              'notes',        ar.notes,
              'status',       ar.status,
              'created_at',   ar.created_at
            ) ORDER BY ar.payment_date DESC
          ) FILTER (WHERE ar.id IS NOT NULL AND ar.is_deleted = false),
          '[]'::json
        ) AS payments

      FROM proforma_invoices pi
      LEFT JOIN sales_invoices si
             ON si.proforma_invoice_id = pi.id
            AND si.is_deleted = false
      LEFT JOIN sales_orders so
             ON so.id = pi.sales_order_id
            AND so.is_deleted = false
      LEFT JOIN accounts_receivable ar
             ON ar.proforma_invoice_id = pi.id
            AND ar.is_deleted = false
            AND ar.payment_type = 'advance'
      WHERE ${where}
      GROUP BY
        pi.id,
        si.id, si.invoice_number, si.total_amount,
        si.advance_adjusted_amount, si.balance_amount, si.balance_payable_amount,
        so.advance_payment_status, so.advance_payment_amount
      ORDER BY pi.created_at DESC
      LIMIT $${limitP} OFFSET $${offsetP}
    `;

    const result = await pool.query(query, params);

    const rows = result.rows.map(row => {
      const advanceAmount = parseFloat(row.advance_amount) || 0;
      const paidAdvance   = parseFloat(row.paid_advance)   || 0;
      let derivedStatus = "UNPAID";
      if (paidAdvance >= advanceAmount && advanceAmount > 0) derivedStatus = "PAID";
      else if (paidAdvance > 0) derivedStatus = "PARTIAL";
      return {
        ...row,
        paid_advance:      paidAdvance,
        remaining_advance: advanceAmount - paidAdvance,
        derived_status:    derivedStatus,
      };
    });

    const filtered = status && status !== "ALL"
      ? rows.filter(r => r.derived_status === status.toUpperCase())
      : rows;

    res.json({
      success: true,
      data: filtered,
      pagination: { total, page, limit, totalPages: Math.ceil(total / limit) },
    });
  } catch (err) {
    console.error("getProformaInvoices ERROR:", err.stack);
    res.status(500).json({ success: false, message: "Failed to fetch proforma invoices", error: err.message });
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const addProformaPayment = async (req, res) => {
  const client = await pool.connect();
  try {
    const userId = req.session?.user?.id;
    const orgId  = req.session?.user?.orgId;
    const { id } = req.params; // proforma_id
    const { payment_date, amount, payment_mode, reference_no, notes } = req.body;

    if (!userId || !orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!payment_date || !amount || !payment_mode)
      return res.status(400).json({ success: false, message: "payment_date, amount, and payment_mode are required" });

    await client.query("BEGIN");

    /* 1. Fetch proforma + already paid total from accounts_receivable */
    const proformaCheck = await client.query(
      `SELECT pi.id, pi.advance_amount, pi.sales_order_id, pi.customer_code,
              COALESCE(SUM(ar.amount), 0) AS already_paid
       FROM proforma_invoices pi
       LEFT JOIN accounts_receivable ar
              ON ar.proforma_invoice_id = pi.id
             AND ar.is_deleted = false
             AND ar.payment_type = 'advance'
       WHERE pi.id = $1 AND pi.org_id = $2 AND pi.is_deleted = false
       GROUP BY pi.id`,
      [id, orgId]
    );
    if (!proformaCheck.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Proforma invoice not found" });
    }

    const proforma      = proformaCheck.rows[0];
    const advanceAmount = parseFloat(proforma.advance_amount) || 0;
    const alreadyPaid   = parseFloat(proforma.already_paid)   || 0;
    const newPaid       = alreadyPaid + parseFloat(amount);

    if (newPaid > advanceAmount) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Payment exceeds advance amount. Remaining: ${(advanceAmount - alreadyPaid).toFixed(2)}`,
      });
    }

    /* 2. Insert into accounts_receivable with payment_type = 'advance' */
    await client.query(
      `INSERT INTO accounts_receivable
         (proforma_invoice_id, payment_date, amount, payment_mode,
          reference_no, notes, status, payment_type, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, 'completed', 'advance', $7, $8)`,
      [id, payment_date, parseFloat(amount), payment_mode,
       reference_no || null, notes || null, orgId, userId]
    );

    /* 3. Insert into advance_payments table */
    await client.query(
      `INSERT INTO advance_payments
         (sales_order_id, proforma_invoice_id, customer_code,
          paid_amount, payment_date, payment_mode,
          reference_number, remarks, payment_status, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'Received', $9)`,
      [
        proforma.sales_order_id,
        id,
        proforma.customer_code || null,
        parseFloat(amount),
        payment_date,
        payment_mode,
        reference_no || null,
        notes || null,
        userId,
      ]
    );

    /* 4. Update proforma status */
    const proformaStatus = newPaid >= advanceAmount ? "Paid" : "Partial";
    await client.query(
      `UPDATE proforma_invoices
       SET status = $1, updated_at = NOW(), updated_by = $2
       WHERE id = $3`,
      [proformaStatus, userId, id]
    );

    /* 5. Update sales_order advance_payment_status */
    if (proforma.sales_order_id) {
      const soResult = await client.query(
        `SELECT advance_payment_amount FROM sales_orders
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [proforma.sales_order_id, orgId]
      );

      if (soResult.rows.length) {
        const soAdvanceAmount = parseFloat(soResult.rows[0].advance_payment_amount) || 0;
        const soAdvanceStatus = newPaid >= soAdvanceAmount ? "paid"
                              : newPaid > 0               ? "partial"
                              : "pending";

        await client.query(
          `UPDATE sales_orders
           SET advance_payment_status = $1,
               updated_at = NOW(),
               updated_by = $2
           WHERE id = $3`,
          [soAdvanceStatus, userId, proforma.sales_order_id]
        );
      }
    }

    /* 6. Reflect on linked sales invoice via proforma_invoice_id FK */
    const siResult = await client.query(
      `SELECT id, total_amount, advance_adjusted_amount
       FROM sales_invoices
       WHERE proforma_invoice_id = $1 AND org_id = $2 AND is_deleted = false
       LIMIT 1`,
      [id, orgId]
    );

    if (siResult.rows.length) {
      const si          = siResult.rows[0];
      const siTotal     = parseFloat(si.total_amount)            || 0;
      const existingAdj = parseFloat(si.advance_adjusted_amount) || 0;
      const newAdjusted = existingAdj + parseFloat(amount);
      const newBalance  = Math.max(0, siTotal - newAdjusted);

      await client.query(
        `UPDATE sales_invoices
         SET advance_adjusted_amount = $1,
             balance_amount          = $2,
             balance_payable_amount  = $3,
             updated_at              = NOW(),
             updated_by              = $4
         WHERE id = $5`,
        [newAdjusted, newBalance, newBalance, userId, si.id]
      );
    }

    await client.query("COMMIT");
    res.json({ success: true, message: "Advance payment recorded and sales invoice updated" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("addProformaPayment ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to record advance payment", error: err.message });
  } finally {
    client.release();
  }
};

/* ─────────────────────────────────────────────────────────────────────────────── */

const getProformaSummary = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    const result = await pool.query(
      `SELECT
        COUNT(pi.id)                                                       AS total_proformas,
        COALESCE(SUM(pi.advance_amount), 0)                               AS total_advance_expected,
        COALESCE(SUM(ar.paid), 0)                                         AS total_advance_collected,
        COALESCE(SUM(pi.advance_amount) - SUM(COALESCE(ar.paid, 0)), 0)  AS total_advance_outstanding
       FROM proforma_invoices pi
       LEFT JOIN (
         SELECT proforma_invoice_id, SUM(amount) AS paid
         FROM accounts_receivable
         WHERE is_deleted = false AND payment_type = 'advance'
         GROUP BY proforma_invoice_id
       ) ar ON ar.proforma_invoice_id = pi.id
       WHERE pi.org_id = $1 AND pi.is_deleted = false`,
      [orgId]
    );
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("getProformaSummary ERROR:", err);
    res.status(500).json({ success: false, message: "Failed to fetch proforma summary" });
  }
};

/* ─────────────────────────────────────────────────────────────────────────────
   EXPORTS
───────────────────────────────────────────────────────────────────────────── */
module.exports = {
  getReceivableInvoices,
  getInvoiceWithPayments,
  addPayment,
  getPaymentSummary,
  deleteInvoice,
  deletePayment,
  hardDeleteReceivable,
  getProformaInvoices,
  addProformaPayment,
  getProformaSummary,
};