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

// // /**
// //  * Calculate due date based on vendor payment terms
// //  */
// // const calculateDueDate = (paymentTerms, invoiceDate) => {
// //   if (!paymentTerms || !invoiceDate) return null;
// //   const date = new Date(invoiceDate);
// //   const terms = paymentTerms.toLowerCase();
// //   let days = 30;
// //   const netMatch = terms.match(/net\s*(\d+)/i);
// //   const daysMatch = terms.match(/(\d+)\s*days?/i);
// //   const numberMatch = terms.match(/^(\d+)$/);
// //   if (netMatch) days = parseInt(netMatch[1]);
// //   else if (daysMatch) days = parseInt(daysMatch[1]);
// //   else if (numberMatch) days = parseInt(numberMatch[1]);
// //   else if (terms.includes("immediate") || terms.includes("cod")) days = 0;
// //   const dueDate = new Date(date);
// //   dueDate.setDate(date.getDate() + days);
// //   return dueDate;
// // };

// // /**
// //  * Format date to YYYY-MM-DD
// //  */
// // const formatDate = (date) => {
// //   if (!date) return null;
// //   const d = new Date(date);
// //   if (isNaN(d.getTime())) return null;
// //   return d.toISOString().split("T")[0];
// // };

// // /**
// //  * Derive payment status from total paid vs net payable and due date
// //  */
// // const derivePaymentStatus = (totalPaid, netPayable, dueDate) => {
// //   const paid = parseFloat(totalPaid) || 0;
// //   const net = parseFloat(netPayable) || 0;

// //   if (paid <= 0) {
// //     if (dueDate) {
// //       const today = new Date();
// //       today.setHours(0, 0, 0, 0);
// //       const due = new Date(dueDate);
// //       due.setHours(0, 0, 0, 0);
// //       if (today > due) return "OVERDUE";
// //     }
// //     return "UNPAID";
// //   }
// //   if (paid >= net) return "PAID";
// //   return "PARTIAL";
// // };

// // const getPayableGrns = async (req, res) => {
// //   try {
// //     const { tab = "1", page = 1, limit = 20, search = "", status } = req.query;
// //     const offset = (parseInt(page) - 1) * parseInt(limit);
// //     const orgId = req.user?.orgId;

// //     if (!orgId) {
// //       return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
// //     }

// //     const isEqualTab = tab === "1";

// //     let searchCondition = "";
// //     const queryParams = [orgId];
// //     let paramIndex = 2;

// //     if (search) {
// //       searchCondition += ` AND (g.grn_number ILIKE $${paramIndex} OR g.vendor_name ILIKE $${paramIndex} OR COALESCE(g.invoice_no, '') ILIKE $${paramIndex})`;
// //       queryParams.push(`%${search}%`);
// //       paramIndex++;
// //     }

// //     if (status && status !== "ALL") {
// //       searchCondition += ` AND g.payment_status = $${paramIndex}`;
// //       queryParams.push(status);
// //       paramIndex++;
// //     }

// //     const query = `
// //       WITH grn_totals AS (
// //         SELECT 
// //           gi.grn_id,
// //           COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
// //           COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
// //         FROM grn_items gi
// //         GROUP BY gi.grn_id
// //       )
// //       SELECT 
// //         g.id,
// //         g.grn_number,
// //         g.grn_date,
// //         g.po_number,
// //         g.vendor_name,
// //         g.invoice_no,
// //         g.delivery_challan_no,
// //         g.gate_entry_no,
// //         g.remarks,
// //         g.grn_status,
// //         g.created_at,
// //         g.final_invoice_amount,
// //         g.reconciliation_status,
// //         g.payment_status,
// //         g.approval_status,
// //         g.invoice_file_url,
// //         g.org_id,
// //         cv.vehicle_number,
// //         pv.id AS vendor_id,
// //         pv.vendor_code,
// //         pv.payment_terms,
// //         pv.gstin,
// //         pv.phone AS vendor_phone,
// //         pv.email AS vendor_email,
// //         COALESCE(gt.total_accepted_value, 0) AS total_accepted_value,
// //         COALESCE(gt.total_grand_value, 0) AS total_grand_value,
// //         CASE 
// //           WHEN pv.payment_terms ILIKE '%net%' THEN 
// //             (g.grn_date + (REGEXP_MATCH(pv.payment_terms, '(\\d+)'))[1]::int * INTERVAL '1 day')
// //           WHEN pv.payment_terms ~ '^\\d+$' THEN 
// //             (g.grn_date + (pv.payment_terms)::int * INTERVAL '1 day')
// //           ELSE 
// //             (g.grn_date + INTERVAL '30 days')
// //         END AS calculated_due_date
// //       FROM grn g
// //       LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
// //       LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
// //       LEFT JOIN grn_totals gt ON gt.grn_id = g.id
// //       WHERE g.is_deleted = false
// //         AND g.grn_status = 'Approved'
// //         AND g.org_id = $1
// //         ${searchCondition}
// //       ORDER BY g.grn_date DESC, g.id DESC
// //       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
// //     `;

// //     queryParams.push(parseInt(limit), offset);
// //     const result = await pool.query(query, queryParams);

// //     const processedData = [];

// //     for (const grn of result.rows) {
// //       // Get the invoice amount - prioritize final_invoice_amount, then total_grand_value
// //       let invoiceAmount = parseFloat(grn.final_invoice_amount);
// //       if (isNaN(invoiceAmount) || invoiceAmount === 0) {
// //         invoiceAmount = parseFloat(grn.total_grand_value);
// //       }
// //       if (isNaN(invoiceAmount)) invoiceAmount = 0;
      
// //       const grnAmount = parseFloat(grn.total_accepted_value) || 0;
// //       const hasDiscrepancy = Math.abs(invoiceAmount - grnAmount) > 0.01;

// //       if (isEqualTab && hasDiscrepancy) continue;
// //       if (!isEqualTab && !hasDiscrepancy) continue;

// //       // Get total paid from transactions
// //       const paidResult = await pool.query(
// //         `SELECT COALESCE(SUM(amount), 0) AS total_paid
// //          FROM payment_transactions pt
// //          WHERE pt.grn_id = $1 AND pt.status = 'COMPLETED'`,
// //         [grn.id]
// //       );

// //       const totalPaid = parseFloat(paidResult.rows[0].total_paid) || 0;
      
// //       // Get all transactions for this GRN
// //       const transactionsResult = await pool.query(
// //         `SELECT 
// //           pt.id,
// //           pt.transaction_date,
// //           pt.amount,
// //           pt.payment_mode,
// //           pt.notes
// //          FROM payment_transactions pt
// //          WHERE pt.grn_id = $1 AND pt.status = 'COMPLETED'
// //          ORDER BY pt.transaction_date DESC`,
// //         [grn.id]
// //       );

// //       const transactions = transactionsResult.rows.map(t => ({
// //         id: t.id,
// //         date: formatDate(t.transaction_date),
// //         amount: parseFloat(t.amount) || 0,
// //         mode: t.payment_mode,
// //         note: t.notes,
// //       }));

// //       const dueDateObj = calculateDueDate(grn.payment_terms, grn.grn_date);
// //       const derivedStatus = derivePaymentStatus(totalPaid, invoiceAmount, dueDateObj);

// //       const grnDate = formatDate(grn.grn_date);
// //       const dueDate = formatDate(dueDateObj);

// //       processedData.push({
// //         id: grn.grn_number,
// //         grnId: grn.id,
// //         vendor: grn.vendor_name,
// //         vendor_name: grn.vendor_name,
// //         invoiceDate: grnDate,
// //         dueDate: dueDate,
// //         amount: invoiceAmount,  // This is the key field
// //         grnAmount: grnAmount,
// //         finalInvoiceAmount: grn.final_invoice_amount || invoiceAmount,
// //         paymentTerms: grn.payment_terms,
// //         status: derivedStatus,
// //         approval: grn.approval_status || "PENDING",
// //         reconciliationStatus: grn.reconciliation_status || "PENDING",
// //         invoiceFile: grn.invoice_file_url,
// //         po_number: grn.po_number,
// //         invoice_no: grn.invoice_no,
// //         delivery_challan_no: grn.delivery_challan_no,
// //         gate_entry_no: grn.gate_entry_no,
// //         vehicle_number: grn.vehicle_number,
// //         remarks: grn.remarks,
// //         created_at: formatDate(grn.created_at),
// //         vendor_id: grn.vendor_id,
// //         vendor_code: grn.vendor_code,
// //         vendor_gst: grn.gstin,
// //         vendor_phone: grn.vendor_phone,
// //         vendor_email: grn.vendor_email,
// //         has_discrepancy: hasDiscrepancy,
// //         discrepancy_amount: Math.abs(invoiceAmount - grnAmount),
// //         total_paid: totalPaid,
// //         transactions: transactions, // Include transactions in the response
// //       });
// //     }

// //     // Count query
// //     let countCondition = "";
// //     const countParams = [orgId];
// //     let countParamIndex = 2;

// //     if (search) {
// //       countCondition += ` AND (g.grn_number ILIKE $${countParamIndex} OR g.vendor_name ILIKE $${countParamIndex} OR COALESCE(g.invoice_no, '') ILIKE $${countParamIndex})`;
// //       countParams.push(`%${search}%`);
// //       countParamIndex++;
// //     }

// //     if (status && status !== "ALL") {
// //       countCondition += ` AND g.payment_status = $${countParamIndex}`;
// //       countParams.push(status);
// //       countParamIndex++;
// //     }

// //     const countQuery = `
// //       WITH grn_totals AS (
// //         SELECT 
// //           gi.grn_id,
// //           COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
// //           COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
// //         FROM grn_items gi
// //         GROUP BY gi.grn_id
// //       )
// //       SELECT COUNT(*) AS total
// //       FROM grn g
// //       LEFT JOIN grn_totals gt ON gt.grn_id = g.id
// //       WHERE g.is_deleted = false
// //         AND g.grn_status = 'Approved'
// //         AND g.org_id = $1
// //         ${countCondition}
// //     `;

// //     const countResult = await pool.query(countQuery, countParams);
// //     const totalCount = parseInt(countResult.rows[0].total);

// //     return res.status(200).json({
// //       success: true,
// //       data: processedData,
// //       pagination: {
// //         total: totalCount,
// //         page: parseInt(page),
// //         limit: parseInt(limit),
// //         totalPages: Math.ceil(totalCount / parseInt(limit)),
// //       },
// //     });
// //   } catch (error) {
// //     console.error("Get Payable GRNs Error:", error);
// //     return res.status(500).json({
// //       success: false,
// //       message: "Failed to fetch payable GRNs",
// //       error: process.env.NODE_ENV === "development" ? error.message : undefined,
// //     });
// //   }
// // };

// // /**
// //  * GET /accounts-payable/transactions/:grnId
// //  */
// // const getPaymentTransactions = async (req, res) => {
// //   const { grnId } = req.params;
// //   const orgId = req.user?.orgId;

// //   try {
// //     const result = await pool.query(
// //       `SELECT 
// //         pt.id,
// //         pt.transaction_date,
// //         pt.amount,
// //         pt.payment_mode,
// //         pt.notes,
// //         pt.status,
// //         pt.created_at,
// //         u.first_name AS created_by_name
// //        FROM payment_transactions pt
// //        JOIN grn g ON g.id = pt.grn_id
// //        LEFT JOIN users u ON u.id = pt.created_by
// //        WHERE g.grn_number = $1 AND g.org_id = $2
// //        ORDER BY pt.transaction_date DESC, pt.created_at DESC`,
// //       [grnId, orgId]
// //     );

// //     const formatted = result.rows.map(t => ({
// //       id: t.id,
// //       transaction_date: formatDate(t.transaction_date),
// //       amount: t.amount,
// //       payment_mode: t.payment_mode,
// //       notes: t.notes,
// //       status: t.status,
// //       created_at: formatDate(t.created_at),
// //       created_by_name: t.created_by_name,
// //     }));

// //     res.json({ success: true, data: formatted });
// //   } catch (error) {
// //     console.error("Get transactions error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * POST /accounts-payable/add-transaction
// //  */
// // const addTransaction = async (req, res) => {
// //   const { grnId, transaction } = req.body;
// //   const userId = req.user?.id;
// //   const orgId = req.user?.orgId;

// //   try {
// //     // Lookup GRN
// //     const grnResult = await pool.query(
// //       `SELECT g.id, g.grn_number, g.grn_date, pv.payment_terms,
// //               COALESCE(
// //                 (SELECT SUM(gi.total_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0
// //               ) AS grn_amount
// //        FROM grn g
// //        LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
// //        WHERE g.grn_number = $1 AND g.org_id = $2 AND g.is_deleted = false`,
// //       [grnId, orgId]
// //     );

// //     if (grnResult.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }

// //     const grn = grnResult.rows[0];
// //     const grn_id = grn.id;

// //     // Insert transaction (without transaction_type and reference_number)
// //     const insertResult = await pool.query(
// //       `INSERT INTO payment_transactions (
// //         grn_id, transaction_date, amount, payment_mode,
// //         notes, status, created_by, created_at
// //        ) VALUES ($1, $2, $3, $4, $5, 'COMPLETED', $6, NOW())
// //        RETURNING id`,
// //       [
// //         grn_id,
// //         transaction.date,
// //         transaction.amount,
// //         transaction.mode,
// //         transaction.note || null,
// //         userId,
// //       ]
// //     );

// //     // Sum all completed transactions for this GRN
// //     const paidResult = await pool.query(
// //       `SELECT COALESCE(SUM(amount), 0) AS total_paid
// //        FROM payment_transactions
// //        WHERE grn_id = $1 AND status = 'COMPLETED'`,
// //       [grn_id]
// //     );

// //     const totalPaid = parseFloat(paidResult.rows[0].total_paid) || 0;
// //     const netPayable = parseFloat(grn.grn_amount) || 0;
// //     const dueDateObj = calculateDueDate(grn.payment_terms, grn.grn_date);
// //     const newStatus = derivePaymentStatus(totalPaid, netPayable, dueDateObj);

// //     // Update GRN payment_status
// //     await pool.query(
// //       `UPDATE grn SET payment_status = $1, updated_by = $2, updated_at = NOW() WHERE id = $3`,
// //       [newStatus, userId, grn_id]
// //     );

// //     res.json({
// //       success: true,
// //       message: "Transaction added successfully",
// //       paymentStatus: newStatus,
// //       totalPaid,
// //       transaction: {
// //         id: insertResult.rows[0].id,
// //         date: transaction.date,
// //         amount: transaction.amount,
// //         mode: transaction.mode,
// //         note: transaction.note,
// //       },
// //     });
// //   } catch (error) {
// //     console.error("Add transaction error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * PUT /accounts-payable/update-payment/:id
// //  */
// // const updatePaymentStatus = async (req, res) => {
// //   const { id } = req.params;
// //   const { paymentMode, gst, tds, applyGst, applyTds, status } = req.body;
// //   const userId = req.user?.id;
// //   const orgId = req.user?.orgId;

// //   try {
// //     const updates = [];
// //     const values = [];
// //     let pi = 1;

// //     if (status !== undefined) {
// //       updates.push(`payment_status = $${pi++}`);
// //       values.push(status);
// //     }
// //     if (paymentMode !== undefined) {
// //       updates.push(`payment_mode = $${pi++}`);
// //       values.push(paymentMode);
// //     }
// //     if (gst !== undefined) {
// //       updates.push(`gst_amount = $${pi++}`);
// //       values.push(gst);
// //     }
// //     if (tds !== undefined) {
// //       updates.push(`tds_amount = $${pi++}`);
// //       values.push(tds);
// //     }
// //     if (applyGst !== undefined) {
// //       updates.push(`apply_gst = $${pi++}`);
// //       values.push(applyGst);
// //     }
// //     if (applyTds !== undefined) {
// //       updates.push(`apply_tds = $${pi++}`);
// //       values.push(applyTds);
// //     }

// //     if (updates.length === 0) {
// //       return res.status(400).json({ success: false, message: "No fields to update" });
// //     }

// //     updates.push(`updated_by = $${pi++}`);
// //     values.push(userId);
// //     updates.push(`updated_at = NOW()`);
// //     values.push(id, orgId);

// //     const query = `
// //       UPDATE grn SET ${updates.join(", ")}
// //       WHERE grn_number = $${pi++} AND org_id = $${pi++} AND is_deleted = false
// //       RETURNING id, grn_number, payment_status, payment_mode, gst_amount, tds_amount, apply_gst, apply_tds
// //     `;

// //     const result = await pool.query(query, values);
// //     if (result.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }

// //     res.json({ success: true, message: "Payment details updated successfully", data: result.rows[0] });
// //   } catch (error) {
// //     console.error("Update payment status error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * PUT /accounts-payable/update-final-amount/:id
// //  */
// // const updateFinalAmount = async (req, res) => {
// //   const { id } = req.params;
// //   const { finalAmount } = req.body;
// //   const userId = req.user?.id;
// //   const orgId = req.user?.orgId;

// //   try {
// //     const check = await pool.query(
// //       `SELECT id FROM grn WHERE grn_number = $1 AND org_id = $2 AND is_deleted = false`,
// //       [id, orgId]
// //     );
// //     if (check.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }

// //     const result = await pool.query(
// //       `UPDATE grn 
// //        SET final_invoice_amount = $1,
// //            reconciliation_status = CASE 
// //              WHEN $1 = (SELECT COALESCE(SUM(total_price), 0) FROM grn_items WHERE grn_id = grn.id)
// //              THEN 'RECONCILED' ELSE 'PENDING' END,
// //            updated_by = $2,
// //            updated_at = NOW()
// //        WHERE grn_number = $3 AND org_id = $4 AND is_deleted = false
// //        RETURNING id, grn_number, final_invoice_amount, reconciliation_status`,
// //       [finalAmount, userId, id, orgId]
// //     );

// //     res.json({ success: true, message: "Final amount updated", data: result.rows[0] });
// //   } catch (error) {
// //     console.error("Update final amount error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * POST /accounts-payable/reconcile/:id
// //  */
// // const reconcileGrn = async (req, res) => {
// //   const { id } = req.params;
// //   const { matchedAmount } = req.body;
// //   const userId = req.user?.id;
// //   const orgId = req.user?.orgId;

// //   try {
// //     const result = await pool.query(
// //       `UPDATE grn 
// //        SET reconciled_amount = $1,
// //            final_invoice_amount = $1,
// //            reconciliation_status = 'RECONCILED',
// //            reconciled_by = $2,
// //            reconciled_at = NOW(),
// //            updated_by = $2,
// //            updated_at = NOW()
// //        WHERE grn_number = $3 AND org_id = $4 AND is_deleted = false
// //        RETURNING id, grn_number, reconciliation_status`,
// //       [matchedAmount, userId, id, orgId]
// //     );

// //     if (result.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }

// //     res.json({ success: true, message: "GRN reconciled successfully", data: result.rows[0] });
// //   } catch (error) {
// //     console.error("Reconcile error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * DELETE /accounts-payable/grn/:id
// //  */
// // const deletePayableGrn = async (req, res) => {
// //   const { id } = req.params;
// //   const userId = req.user?.id;
// //   const orgId = req.user?.orgId;

// //   try {
// //     const result = await pool.query(
// //       `UPDATE grn 
// //        SET is_deleted = true, updated_by = $1, updated_at = NOW()
// //        WHERE grn_number = $2 AND org_id = $3 AND is_deleted = false
// //        RETURNING id, grn_number`,
// //       [userId, id, orgId]
// //     );
// //     if (result.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }
// //     res.json({ success: true, message: "GRN deleted successfully", data: result.rows[0] });
// //   } catch (error) {
// //     console.error("Delete GRN error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * GET /accounts-payable/summary
// //  */
// // const getPayableSummary = async (req, res) => {
// //   try {
// //     const orgId = req.user?.orgId;
// //     if (!orgId) {
// //       return res.status(401).json({ success: false, message: "Unauthorized" });
// //     }

// //     const query = `
// //       WITH grn_totals AS (
// //         SELECT 
// //           gi.grn_id,
// //           COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
// //           COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
// //         FROM grn_items gi
// //         GROUP BY gi.grn_id
// //       ),
// //       payable_grns AS (
// //         SELECT 
// //           g.payment_status,
// //           COALESCE(gt.total_accepted_value, 0) AS total_accepted_value,
// //           COALESCE(gt.total_grand_value, 0) AS total_grand_value
// //         FROM grn g
// //         LEFT JOIN grn_totals gt ON gt.grn_id = g.id
// //         WHERE g.is_deleted = false AND g.grn_status = 'Approved' AND g.org_id = $1
// //       )
// //       SELECT 
// //         COUNT(*) AS total_grns,
// //         COALESCE(SUM(total_accepted_value), 0) AS total_payable_amount,
// //         COALESCE(SUM(total_grand_value), 0) AS total_invoice_amount,
// //         COUNT(CASE WHEN ROUND(total_accepted_value::numeric,2) = ROUND(total_grand_value::numeric,2) THEN 1 END) AS matched_grns,
// //         COUNT(CASE WHEN ROUND(total_accepted_value::numeric,2) != ROUND(total_grand_value::numeric,2) THEN 1 END) AS mismatched_grns,
// //         COALESCE(SUM(CASE WHEN ROUND(total_accepted_value::numeric,2) = ROUND(total_grand_value::numeric,2) THEN total_grand_value ELSE 0 END), 0) AS matched_amount,
// //         COALESCE(SUM(CASE WHEN ROUND(total_accepted_value::numeric,2) != ROUND(total_grand_value::numeric,2) THEN total_grand_value ELSE 0 END), 0) AS mismatched_amount,
// //         COUNT(CASE WHEN payment_status = 'PAID' THEN 1 END) AS paid_grns,
// //         COUNT(CASE WHEN payment_status = 'UNPAID' THEN 1 END) AS unpaid_grns,
// //         COUNT(CASE WHEN payment_status = 'PARTIAL' THEN 1 END) AS partial_grns,
// //         COUNT(CASE WHEN payment_status = 'OVERDUE' THEN 1 END) AS overdue_grns
// //       FROM payable_grns
// //     `;

// //     const result = await pool.query(query, [orgId]);
// //     const summary = result.rows[0];

// //     return res.status(200).json({
// //       success: true,
// //       data: {
// //         total_grns: parseInt(summary.total_grns) || 0,
// //         total_payable_amount: parseFloat(summary.total_payable_amount) || 0,
// //         total_invoice_amount: parseFloat(summary.total_invoice_amount) || 0,
// //         matched_grns: parseInt(summary.matched_grns) || 0,
// //         mismatched_grns: parseInt(summary.mismatched_grns) || 0,
// //         matched_amount: parseFloat(summary.matched_amount) || 0,
// //         mismatched_amount: parseFloat(summary.mismatched_amount) || 0,
// //         paid_grns: parseInt(summary.paid_grns) || 0,
// //         unpaid_grns: parseInt(summary.unpaid_grns) || 0,
// //         partial_grns: parseInt(summary.partial_grns) || 0,
// //         overdue_grns: parseInt(summary.overdue_grns) || 0,
// //       },
// //     });
// //   } catch (error) {
// //     console.error("Get Payable Summary Error:", error);
// //     return res.status(500).json({ success: false, message: "Failed to fetch summary" });
// //   }
// // };

// // /**
// //  * POST /accounts-payable/upload-invoice
// //  */
// // const uploadInvoiceFile = async (req, res) => {
// //   const { grnId } = req.body;
// //   const file = req.file;
// //   const orgId = req.user?.orgId;
// //   const userId = req.user?.id;

// //   if (!file) {
// //     return res.status(400).json({ success: false, message: "No file uploaded" });
// //   }

// //   try {
// //     const fileUrl = `/uploads/invoices/${Date.now()}_${file.originalname}`;
// //     const result = await pool.query(
// //       `UPDATE grn 
// //        SET invoice_file_url = $1, updated_by = $2, updated_at = NOW()
// //        WHERE grn_number = $3 AND org_id = $4 AND is_deleted = false
// //        RETURNING id, grn_number`,
// //       [fileUrl, userId, grnId, orgId]
// //     );
// //     if (result.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }
// //     res.json({ success: true, message: "File uploaded successfully", fileUrl });
// //   } catch (error) {
// //     console.error("File upload error:", error);
// //     res.status(500).json({ success: false, message: error.message });
// //   }
// // };

// // /**
// //  * GET /accounts-payable/grn/:id
// //  */
// // const getPayableGrnById = async (req, res) => {
// //   try {
// //     const { id } = req.params;
// //     const orgId = req.user?.orgId;
// //     if (!orgId) {
// //       return res.status(401).json({ success: false, message: "Unauthorized" });
// //     }

// //     const query = `
// //       WITH grn_totals AS (
// //         SELECT 
// //           gi.grn_id,
// //           COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
// //           COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
// //         FROM grn_items gi
// //         GROUP BY gi.grn_id
// //       )
// //       SELECT 
// //         g.id, g.grn_number, g.grn_date, g.po_number, g.vendor_name,
// //         g.invoice_no, g.delivery_challan_no, g.gate_entry_no, g.remarks,
// //         g.grn_status, g.created_at, g.final_invoice_amount,
// //         g.reconciliation_status, g.payment_status, g.approval_status,
// //         g.invoice_file_url, g.org_id,
// //         cv.vehicle_number,
// //         pv.id AS vendor_id, pv.vendor_code, pv.payment_terms, pv.gstin,
// //         pv.phone AS vendor_phone, pv.email AS vendor_email,
// //         COALESCE(gt.total_accepted_value, 0) AS grn_amount,
// //         COALESCE(gt.total_grand_value, 0) AS invoice_amount
// //       FROM grn g
// //       LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
// //       LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
// //       LEFT JOIN grn_totals gt ON gt.grn_id = g.id
// //       WHERE g.is_deleted = false AND g.grn_status = 'Approved' AND g.grn_number = $1 AND g.org_id = $2
// //     `;

// //     const result = await pool.query(query, [id, orgId]);
// //     if (result.rows.length === 0) {
// //       return res.status(404).json({ success: false, message: "GRN not found" });
// //     }

// //     const grn = result.rows[0];
// //     const invoiceAmount = parseFloat(grn.final_invoice_amount || grn.invoice_amount || 0);
// //     const grnAmount = parseFloat(grn.grn_amount || 0);
// //     const dueDateObj = calculateDueDate(grn.payment_terms, grn.grn_date);

// //     return res.status(200).json({
// //       success: true,
// //       data: {
// //         id: grn.grn_number,
// //         grn_id: grn.id,
// //         vendor: grn.vendor_name,
// //         vendor_id: grn.vendor_id,
// //         vendor_code: grn.vendor_code,
// //         vendor_gst: grn.gstin,
// //         vendor_phone: grn.vendor_phone,
// //         vendor_email: grn.vendor_email,
// //         invoiceDate: formatDate(grn.grn_date),
// //         dueDate: formatDate(dueDateObj),
// //         amount: invoiceAmount,
// //         grnAmount,
// //         finalInvoiceAmount: grn.final_invoice_amount || invoiceAmount,
// //         paymentTerms: grn.payment_terms,
// //         status: grn.payment_status || "UNPAID",
// //         approval: grn.approval_status || "PENDING",
// //         reconciliationStatus: grn.reconciliation_status || "PENDING",
// //         invoiceFile: grn.invoice_file_url,
// //         po_number: grn.po_number,
// //         invoice_no: grn.invoice_no,
// //         delivery_challan_no: grn.delivery_challan_no,
// //         gate_entry_no: grn.gate_entry_no,
// //         vehicle_number: grn.vehicle_number,
// //         remarks: grn.remarks,
// //         created_at: formatDate(grn.created_at),
// //       },
// //     });
// //   } catch (error) {
// //     console.error("Get Payable GRN By ID Error:", error);
// //     return res.status(500).json({ success: false, message: "Failed to fetch GRN details" });
// //   }
// // };

// // module.exports = {
// //   getPayableGrns,
// //   getPaymentTransactions,
// //   addTransaction,
// //   updatePaymentStatus,
// //   updateFinalAmount,
// //   reconcileGrn,
// //   deletePayableGrn,
// //   getPayableSummary,
// //   uploadInvoiceFile,
// //   getPayableGrnById,
// // };


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

// /**
//  * Calculate due date based on vendor payment terms
//  */
// const calculateDueDate = (paymentTerms, invoiceDate) => {
//   if (!paymentTerms || !invoiceDate) return null;
//   const date = new Date(invoiceDate);
//   const terms = paymentTerms.toLowerCase();
//   let days = 30;
//   const netMatch = terms.match(/net\s*(\d+)/i);
//   const daysMatch = terms.match(/(\d+)\s*days?/i);
//   const numberMatch = terms.match(/^(\d+)$/);
//   if (netMatch) days = parseInt(netMatch[1]);
//   else if (daysMatch) days = parseInt(daysMatch[1]);
//   else if (numberMatch) days = parseInt(numberMatch[1]);
//   else if (terms.includes("immediate") || terms.includes("cod")) days = 0;
//   const dueDate = new Date(date);
//   dueDate.setDate(date.getDate() + days);
//   return dueDate;
// };

// /**
//  * Format date to MM/DD/YYYY
//  */
// const formatDate = (date) => {
//   if (!date) return null;
//   const d = new Date(date);
//   if (isNaN(d.getTime())) return null;
//   const month = d.getMonth() + 1;
//   const day = d.getDate();
//   const year = d.getFullYear();
//   return `${month}/${day}/${year}`;
// };

// /**
//  * Derive payment status from total paid vs net payable and due date
//  */
// const derivePaymentStatus = (totalPaid, netPayable, dueDate) => {
//   const paid = parseFloat(totalPaid) || 0;
//   const net = parseFloat(netPayable) || 0;

//   if (paid <= 0) {
//     if (dueDate) {
//       const today = new Date();
//       today.setHours(0, 0, 0, 0);
//       const due = new Date(dueDate);
//       due.setHours(0, 0, 0, 0);
//       if (today > due) return "OVERDUE";
//     }
//     return "UNPAID";
//   }
//   if (paid >= net) return "PAID";
//   return "PARTIAL";
// };

// /**
//  * GET vendor details by vendor name
//  */
// const getVendorDetails = async (req, res) => {
//   try {
//     const { vendorName } = req.params;
//     const orgId = req.user?.orgId;

//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
//     }

//     const result = await pool.query(
//       `SELECT 
//         id, vendor_code, vendor_name, vendor_type, contact_person,
//         phone, email, gstin, pan, address, city, state, country, pincode,
//         payment_terms, bank_name, account_holder_name, account_number,
//         ifsc_code, branch, rating, status,
//         shipping_addresses, license_number,
//         pan_document_url, license_document_url
//        FROM procurement_vendors
//        WHERE vendor_name = $1 AND org_id = $2 AND is_deleted = false`,
//       [vendorName, orgId]
//     );

//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "Vendor not found" });
//     }

//     const vendor = result.rows[0];
    
//     // Parse shipping_addresses
//     if (vendor.shipping_addresses) {
//       if (typeof vendor.shipping_addresses === 'string') {
//         try {
//           vendor.shipping_addresses = JSON.parse(vendor.shipping_addresses);
//         } catch (e) {
//           vendor.shipping_addresses = [];
//         }
//       }
//     } else {
//       vendor.shipping_addresses = [];
//     }

//     res.json({ success: true, data: vendor });

//   } catch (error) {
//     console.error("Get Vendor Details Error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// const getPayableGrns = async (req, res) => {
//   try {
//     const { tab = "1", page = 1, limit = 20, search = "", status } = req.query;
//     const offset = (parseInt(page) - 1) * parseInt(limit);
//     const orgId = req.user?.orgId;

//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
//     }

//     const isEqualTab = tab === "1";
//     const searchTerm = search ? `%${search}%` : '';
//     const limitNum = parseInt(limit);
//     const offsetNum = parseInt(offset);

//     // First, get all POs with their GRN totals
//     const poQuery = `
//       WITH grn_totals AS (
//         SELECT 
//           g.po_id,
//           COALESCE(SUM(gi.total_price), 0) AS total_grn_amount,
//           COUNT(DISTINCT g.id) AS grn_count
//         FROM grn g
//         LEFT JOIN grn_items gi ON gi.grn_id = g.id
//         WHERE g.is_deleted = false
//           AND g.grn_status IN ('Approved', 'Partially Approved')
//         GROUP BY g.po_id
//       )
//       SELECT 
//         po.id,
//         po.po_number,
//         po.vendor_id,
//         po.vendor_name,
//         po.total_amount AS po_total_amount,
//         po.status AS po_status,
//         po.po_date,
//         po.created_at,
//         po.org_id,
//         pv.payment_terms,
//         pv.vendor_code,
//         pv.gstin,
//         pv.phone AS vendor_phone,
//         pv.email AS vendor_email,
//         pv.address AS vendor_address,
//         pv.city AS vendor_city,
//         pv.state AS vendor_state,
//         pv.pincode AS vendor_pincode,
//         pv.bank_name,
//         pv.account_number,
//         pv.ifsc_code,
//         pv.branch AS bank_branch,
//         pv.contact_person,
//         COALESCE(gt.total_grn_amount, 0) AS grn_total_amount,
//         COALESCE(gt.grn_count, 0) AS grn_count,
//         CASE 
//           WHEN pv.payment_terms ILIKE '%net%' THEN 
//             po.po_date + (REGEXP_MATCH(pv.payment_terms, '(\\d+)'))[1]::int * INTERVAL '1 day'
//           WHEN pv.payment_terms ~ '^\\d+$' THEN 
//             po.po_date + (pv.payment_terms)::int * INTERVAL '1 day'
//           ELSE 
//             po.po_date + INTERVAL '30 days'
//         END AS due_date
//       FROM purchase_orders po
//       LEFT JOIN procurement_vendors pv ON pv.id = po.vendor_id AND pv.org_id = po.org_id AND pv.is_deleted = false
//       LEFT JOIN grn_totals gt ON gt.po_id = po.id
//       WHERE po.is_deleted = false
//         AND po.org_id = $1
//         AND po.po_number IS NOT NULL
//         AND po.po_number != ''
//         AND ($2 = '' OR po.po_number ILIKE $2 OR po.vendor_name ILIKE $2)
//       ORDER BY po.po_date DESC
//       LIMIT $3
//       OFFSET $4
//     `;

//     const poResult = await pool.query(poQuery, [orgId, searchTerm, limitNum, offsetNum]);

//     // For each PO, fetch additional data
//     const processedData = [];
    
//     for (const po of poResult.rows) {
//       // Get GRN details for this PO
//       const grnQuery = `
//         SELECT 
//           g.id,
//           g.grn_number,
//           g.grn_date,
//           COALESCE(SUM(gi.total_price), 0) AS grn_amount
//         FROM grn g
//         LEFT JOIN grn_items gi ON gi.grn_id = g.id
//         WHERE g.po_id = $1
//           AND g.is_deleted = false
//           AND g.grn_status IN ('Approved', 'Partially Approved')
//         GROUP BY g.id, g.grn_number, g.grn_date
//       `;
//       const grnResult = await pool.query(grnQuery, [po.id]);
      
//       // Get total paid from payment transactions for this PO
//       const paidQuery = `
//         SELECT COALESCE(SUM(pt.amount), 0) AS total_paid
//         FROM grn g
//         JOIN payment_transactions pt ON pt.grn_id = g.id
//         WHERE g.po_id = $1 AND pt.status = 'COMPLETED'
//       `;
//       const paidResult = await pool.query(paidQuery, [po.id]);
//       const totalPaid = parseFloat(paidResult.rows[0]?.total_paid) || 0;
      
//       // Get proforma data for this PO
//       const proformaQuery = `
//         SELECT 
//           advance_required,
//           advance_amount,
//           proforma_invoice_number,
//           proforma_invoice_date,
//           total_amount as proforma_total_amount,
//           file_paths
//         FROM upload_Proforma
//         WHERE po_number = $1 AND org_id = $2 AND is_deleted = false
//         ORDER BY created_at DESC
//         LIMIT 1
//       `;
//       const proformaResult = await pool.query(proformaQuery, [po.po_number, orgId]);
//       const proforma = proformaResult.rows[0] || null;
      
//       // Calculate totals
//       const poTotalAmount = parseFloat(po.po_total_amount) || 0;
//       const grnTotalAmount = parseFloat(po.grn_total_amount) || 0;
//       const advanceAmount = proforma ? parseFloat(proforma.advance_amount) || 0 : 0;
//       const remainingAmount = poTotalAmount - advanceAmount - totalPaid;
      
//       // Determine payment status
//       let paymentStatus = "UNPAID";
//       if (remainingAmount <= 0) {
//         paymentStatus = "PAID";
//       } else if (totalPaid > 0 || advanceAmount > 0) {
//         paymentStatus = "PARTIAL";
//       }
      
//       // Check due date for OVERDUE status
//       const dueDate = po.due_date ? new Date(po.due_date) : null;
//       const today = new Date();
//       today.setHours(0, 0, 0, 0);
//       if (dueDate && today > dueDate && paymentStatus !== "PAID") {
//         paymentStatus = "OVERDUE";
//       }
      
//       // ========== CRITICAL: Calculate discrepancy correctly ==========
//       // has_discrepancy = PO amount != Total GRN amount
//       const hasDiscrepancy = Math.abs(poTotalAmount - grnTotalAmount) > 0.01;
      
//       // Format due date
//       const formattedDueDate = po.due_date ? formatDate(po.due_date) : null;
      
//       const vendorAddress = [po.vendor_address, po.vendor_city, po.vendor_state, po.vendor_pincode]
//         .filter(Boolean).join(", ");
      
//       processedData.push({
//         id: po.po_number,
//         po_number: po.po_number,
//         po_id: po.id,
//         vendor: po.vendor_name,
//         vendor_name: po.vendor_name,
//         vendor_code: po.vendor_code,
//         vendor_gst: po.gstin,
//         vendor_phone: po.vendor_phone,
//         vendor_email: po.vendor_email,
//         vendor_address: vendorAddress,
//         vendor_contact_person: po.contact_person,
//         bank_name: po.bank_name,
//         bank_account: po.account_number,
//         bank_ifsc: po.ifsc_code,
//         bank_branch: po.bank_branch,
//         po_date: formatDate(po.po_date),
//         dueDate: formattedDueDate,
//         po_total_amount: poTotalAmount,
//         grnAmount: grnTotalAmount,
//         finalInvoiceAmount: po.final_invoice_amount || poTotalAmount,
//         total_received_amount: grnTotalAmount,
//         total_paid: totalPaid,
//         remaining_amount: remainingAmount < 0 ? 0 : remainingAmount,
//         grns: grnResult.rows,
//         total_grn_count: parseInt(po.grn_count) || 0,
//         status: paymentStatus,
//         po_status: po.po_status,
//         has_proforma: !!proforma,
//         advance_required: proforma?.advance_required || false,
//         advance_amount: advanceAmount,
//         proforma_invoice_number: proforma?.proforma_invoice_number,
//         proforma_invoice_date: proforma?.proforma_invoice_date,
//         proforma_total_amount: proforma?.proforma_total_amount,
//         proforma_files: proforma?.file_paths,
//         has_discrepancy: hasDiscrepancy,
//         discrepancy_amount: Math.abs(poTotalAmount - grnTotalAmount),
//         created_at: formatDate(po.created_at),
//         payment_terms: po.payment_terms,
//         transactions: []
//       });
//     }
    
//     // Get total count for pagination (without filters)
//     const countQuery = `
//       SELECT COUNT(*) AS total
//       FROM purchase_orders po
//       WHERE po.is_deleted = false
//         AND po.org_id = $1
//         AND po.po_number IS NOT NULL
//         AND po.po_number != ''
//         AND ($2 = '' OR po.po_number ILIKE $2 OR po.vendor_name ILIKE $2)
//     `;
//     const countResult = await pool.query(countQuery, [orgId, searchTerm]);
//     const totalCount = parseInt(countResult.rows[0].total);
    
//     // ========== CRITICAL: Apply filters correctly ==========
//     let filteredData = [...processedData];
    
//     // Apply status filter (PAID, UNPAID, PARTIAL, OVERDUE)
//     if (status && status !== "ALL") {
//       filteredData = filteredData.filter(item => item.status === status);
//     }
    
//     // Apply tab filter based on discrepancy
//     if (isEqualTab) {
//       // Tab 1: Payables - ONLY items WITHOUT discrepancy (PO amount == GRN amount)
//       filteredData = filteredData.filter(item => !item.has_discrepancy);
//     } else {
//       // Tab 2: Payables with Modifications - ONLY items WITH discrepancy (PO amount != GRN amount)
//       filteredData = filteredData.filter(item => item.has_discrepancy);
//     }
    
//     // Calculate summary from FILTERED data (not all data)
//     const totalPayableAmount = filteredData.reduce((sum, item) => sum + item.po_total_amount, 0);
//     const totalRemainingAmount = filteredData.reduce((sum, item) => sum + item.remaining_amount, 0);
//     const totalPaidAmount = filteredData.reduce((sum, item) => sum + item.total_paid, 0);
//     const totalAdvanceAmount = filteredData.reduce((sum, item) => sum + item.advance_amount, 0);
//     const matchedCount = filteredData.filter(item => !item.has_discrepancy).length;
//     const mismatchedCount = filteredData.filter(item => item.has_discrepancy).length;
//     const matchedAmount = filteredData.filter(item => !item.has_discrepancy).reduce((sum, item) => sum + item.po_total_amount, 0);
//     const mismatchedAmount = filteredData.filter(item => item.has_discrepancy).reduce((sum, item) => sum + item.po_total_amount, 0);
//     const paidCount = filteredData.filter(item => item.status === "PAID").length;
//     const unpaidCount = filteredData.filter(item => item.status === "UNPAID").length;
//     const partialCount = filteredData.filter(item => item.status === "PARTIAL").length;
//     const overdueCount = filteredData.filter(item => item.status === "OVERDUE").length;
    
//     return res.status(200).json({
//       success: true,
//       data: filteredData,
//       summary: {
//         total_po_count: filteredData.length,
//         total_payable_amount: totalPayableAmount,
//         total_advance_amount: totalAdvanceAmount,
//         total_paid_amount: totalPaidAmount,
//         total_remaining_amount: totalRemainingAmount,
//         matched_count: matchedCount,
//         mismatched_count: mismatchedCount,
//         matched_amount: matchedAmount,
//         mismatched_amount: mismatchedAmount,
//         paid_count: paidCount,
//         unpaid_count: unpaidCount,
//         partial_count: partialCount,
//         overdue_count: overdueCount
//       },
//       pagination: {
//         total: totalCount,
//         page: parseInt(page),
//         limit: limitNum,
//         totalPages: Math.ceil(totalCount / limitNum),
//       },
//     });
//   } catch (error) {
//     console.error("Get Payable GRNs Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch payable GRNs",
//       error: process.env.NODE_ENV === "development" ? error.message : undefined,
//     });
//   }
// };



// /**
//  * GET /accounts-payable/transactions/:grnId
//  */
// const getPaymentTransactions = async (req, res) => {
//   const { grnId } = req.params;
//   const orgId = req.user?.orgId;

//   try {
//     const result = await pool.query(
//       `SELECT 
//         pt.id,
//         pt.transaction_date,
//         pt.transaction_time,
//         pt.amount,
//         pt.payment_mode,
//         pt.notes,
//         pt.status,
//         pt.created_at,
//         u.first_name AS created_by_name
//        FROM payment_transactions pt
//        JOIN grn g ON g.id = pt.grn_id
//        LEFT JOIN users u ON u.id = pt.created_by
//        WHERE g.grn_number = $1 AND g.org_id = $2
//        ORDER BY pt.transaction_date DESC, pt.transaction_time DESC`,
//       [grnId, orgId]
//     );

//     const formatted = result.rows.map(t => ({
//       id: t.id,
//       transaction_date: formatDate(t.transaction_date),
//       transaction_time: t.transaction_time ? t.transaction_time.slice(0, 5) : null,
//       amount: t.amount,
//       payment_mode: t.payment_mode,
//       notes: t.notes,
//       status: t.status,
//       created_at: formatDate(t.created_at),
//       created_by_name: t.created_by_name,
//     }));

//     res.json({ success: true, data: formatted });
//   } catch (error) {
//     console.error("Get transactions error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /**
//  * POST /accounts-payable/add-transaction
//  */
// const addTransaction = async (req, res) => {
//   const { grnId, transaction } = req.body;
//   const userId = req.user?.id;
//   const orgId = req.user?.orgId;

//   try {
//     // Lookup GRN
//     const grnResult = await pool.query(
//       `SELECT g.id, g.grn_number, g.grn_date, pv.payment_terms,
//               COALESCE(
//                 (SELECT SUM(gi.total_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0
//               ) AS grn_amount
//        FROM grn g
//        LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
//        WHERE g.grn_number = $1 AND g.org_id = $2 AND g.is_deleted = false`,
//       [grnId, orgId]
//     );

//     if (grnResult.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     const grn = grnResult.rows[0];
//     const grn_id = grn.id;

//     // Combine date and time if both provided
//     let transactionDateTime = transaction.date;
//     if (transaction.date && transaction.time) {
//       transactionDateTime = `${transaction.date} ${transaction.time}`;
//     }

//     // Insert transaction with time field
//     const insertResult = await pool.query(
//       `INSERT INTO payment_transactions (
//         grn_id, transaction_date, transaction_time, amount, payment_mode,
//         notes, status, created_by, created_at
//        ) VALUES ($1, $2, $3, $4, $5, $6, 'COMPLETED', $7, NOW())
//        RETURNING id`,
//       [
//         grn_id,
//         transaction.date,
//         transaction.time || null,
//         transaction.amount,
//         transaction.mode,
//         transaction.note || null,
//         userId,
//       ]
//     );

//     // Sum all completed transactions for this GRN
//     const paidResult = await pool.query(
//       `SELECT COALESCE(SUM(amount), 0) AS total_paid
//        FROM payment_transactions
//        WHERE grn_id = $1 AND status = 'COMPLETED'`,
//       [grn_id]
//     );

//     const totalPaid = parseFloat(paidResult.rows[0].total_paid) || 0;
//     const netPayable = parseFloat(grn.grn_amount) || 0;
//     const dueDateObj = calculateDueDate(grn.payment_terms, grn.grn_date);
//     const newStatus = derivePaymentStatus(totalPaid, netPayable, dueDateObj);

//     // Update GRN payment_status
//     await pool.query(
//       `UPDATE grn SET payment_status = $1, updated_by = $2, updated_at = NOW() WHERE id = $3`,
//       [newStatus, userId, grn_id]
//     );

//     res.json({
//       success: true,
//       message: "Transaction added successfully",
//       paymentStatus: newStatus,
//       totalPaid,
//       transaction: {
//         id: insertResult.rows[0].id,
//         date: transaction.date,
//         time: transaction.time,
//         amount: transaction.amount,
//         mode: transaction.mode,
//         note: transaction.note,
//       },
//     });
//   } catch (error) {
//     console.error("Add transaction error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /**
//  * PUT /accounts-payable/update-payment/:id
//  */
// const updatePaymentStatus = async (req, res) => {
//   const { id } = req.params;
//   const { paymentMode, gst, tds, applyGst, applyTds, status } = req.body;
//   const userId = req.user?.id;
//   const orgId = req.user?.orgId;

//   try {
//     const updates = [];
//     const values = [];
//     let pi = 1;

//     if (status !== undefined) {
//       updates.push(`payment_status = $${pi++}`);
//       values.push(status);
//     }
//     if (paymentMode !== undefined) {
//       updates.push(`payment_mode = $${pi++}`);
//       values.push(paymentMode);
//     }
//     if (gst !== undefined) {
//       updates.push(`gst_amount = $${pi++}`);
//       values.push(gst);
//     }
//     if (tds !== undefined) {
//       updates.push(`tds_amount = $${pi++}`);
//       values.push(tds);
//     }
//     if (applyGst !== undefined) {
//       updates.push(`apply_gst = $${pi++}`);
//       values.push(applyGst);
//     }
//     if (applyTds !== undefined) {
//       updates.push(`apply_tds = $${pi++}`);
//       values.push(applyTds);
//     }

//     if (updates.length === 0) {
//       return res.status(400).json({ success: false, message: "No fields to update" });
//     }

//     updates.push(`updated_by = $${pi++}`);
//     values.push(userId);
//     updates.push(`updated_at = NOW()`);
//     values.push(id, orgId);

//     const query = `
//       UPDATE grn SET ${updates.join(", ")}
//       WHERE grn_number = $${pi++} AND org_id = $${pi++} AND is_deleted = false
//       RETURNING id, grn_number, payment_status, payment_mode, gst_amount, tds_amount, apply_gst, apply_tds
//     `;

//     const result = await pool.query(query, values);
//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     res.json({ success: true, message: "Payment details updated successfully", data: result.rows[0] });
//   } catch (error) {
//     console.error("Update payment status error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /**
//  * PUT /accounts-payable/update-final-amount/:id
//  */
// const updateFinalAmount = async (req, res) => {
//   const { id } = req.params;
//   const { finalAmount } = req.body;
//   const userId = req.user?.id;
//   const orgId = req.user?.orgId;

//   try {
//     const check = await pool.query(
//       `SELECT id FROM grn WHERE grn_number = $1 AND org_id = $2 AND is_deleted = false`,
//       [id, orgId]
//     );
//     if (check.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     const result = await pool.query(
//       `UPDATE grn 
//        SET final_invoice_amount = $1,
//            reconciliation_status = CASE 
//              WHEN $1 = (SELECT COALESCE(SUM(total_price), 0) FROM grn_items WHERE grn_id = grn.id)
//              THEN 'RECONCILED' ELSE 'PENDING' END,
//            updated_by = $2,
//            updated_at = NOW()
//        WHERE grn_number = $3 AND org_id = $4 AND is_deleted = false
//        RETURNING id, grn_number, final_invoice_amount, reconciliation_status`,
//       [finalAmount, userId, id, orgId]
//     );

//     res.json({ success: true, message: "Final amount updated", data: result.rows[0] });
//   } catch (error) {
//     console.error("Update final amount error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /**
//  * POST /accounts-payable/reconcile/:id
//  */
// const reconcileGrn = async (req, res) => {
//   const { id } = req.params;
//   const { matchedAmount } = req.body;
//   const userId = req.user?.id;
//   const orgId = req.user?.orgId;

//   try {
//     const result = await pool.query(
//       `UPDATE grn 
//        SET reconciled_amount = $1,
//            final_invoice_amount = $1,
//            reconciliation_status = 'RECONCILED',
//            reconciled_by = $2,
//            reconciled_at = NOW(),
//            updated_by = $2,
//            updated_at = NOW()
//        WHERE grn_number = $3 AND org_id = $4 AND is_deleted = false
//        RETURNING id, grn_number, reconciliation_status`,
//       [matchedAmount, userId, id, orgId]
//     );

//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     res.json({ success: true, message: "GRN reconciled successfully", data: result.rows[0] });
//   } catch (error) {
//     console.error("Reconcile error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /**
//  * DELETE /accounts-payable/grn/:id
//  */
// const deletePayableGrn = async (req, res) => {
//   const { id } = req.params;
//   const userId = req.user?.id;
//   const orgId = req.user?.orgId;

//   try {
//     const result = await pool.query(
//       `UPDATE grn 
//        SET is_deleted = true, updated_by = $1, updated_at = NOW()
//        WHERE grn_number = $2 AND org_id = $3 AND is_deleted = false
//        RETURNING id, grn_number`,
//       [userId, id, orgId]
//     );
//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }
//     res.json({ success: true, message: "GRN deleted successfully", data: result.rows[0] });
//   } catch (error) {
//     console.error("Delete GRN error:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /**
//  * GET /accounts-payable/summary
//  */
// const getPayableSummary = async (req, res) => {
//   try {
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }

//     const query = `
//       WITH grn_totals AS (
//         SELECT 
//           gi.grn_id,
//           COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
//           COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
//         FROM grn_items gi
//         GROUP BY gi.grn_id
//       ),
//       payable_grns AS (
//         SELECT 
//           g.payment_status,
//           COALESCE(gt.total_accepted_value, 0) AS total_accepted_value,
//           COALESCE(gt.total_grand_value, 0) AS total_grand_value
//         FROM grn g
//         LEFT JOIN grn_totals gt ON gt.grn_id = g.id
//         WHERE g.is_deleted = false AND g.grn_status IN('Approved', 'Partially Approved') AND g.org_id = $1
//       )
//       SELECT 
//         COUNT(*) AS total_grns,
//         COALESCE(SUM(total_accepted_value), 0) AS total_payable_amount,
//         COALESCE(SUM(total_grand_value), 0) AS total_invoice_amount,
//         COUNT(CASE WHEN ROUND(total_accepted_value::numeric,2) = ROUND(total_grand_value::numeric,2) THEN 1 END) AS matched_grns,
//         COUNT(CASE WHEN ROUND(total_accepted_value::numeric,2) != ROUND(total_grand_value::numeric,2) THEN 1 END) AS mismatched_grns,
//         COALESCE(SUM(CASE WHEN ROUND(total_accepted_value::numeric,2) = ROUND(total_grand_value::numeric,2) THEN total_grand_value ELSE 0 END), 0) AS matched_amount,
//         COALESCE(SUM(CASE WHEN ROUND(total_accepted_value::numeric,2) != ROUND(total_grand_value::numeric,2) THEN total_grand_value ELSE 0 END), 0) AS mismatched_amount,
//         COUNT(CASE WHEN payment_status = 'PAID' THEN 1 END) AS paid_grns,
//         COUNT(CASE WHEN payment_status = 'UNPAID' THEN 1 END) AS unpaid_grns,
//         COUNT(CASE WHEN payment_status = 'PARTIAL' THEN 1 END) AS partial_grns,
//         COUNT(CASE WHEN payment_status = 'OVERDUE' THEN 1 END) AS overdue_grns
//       FROM payable_grns
//     `;

//     const result = await pool.query(query, [orgId]);
//     const summary = result.rows[0];

//     return res.status(200).json({
//       success: true,
//       data: {
//         total_grns: parseInt(summary.total_grns) || 0,
//         total_payable_amount: parseFloat(summary.total_payable_amount) || 0,
//         total_invoice_amount: parseFloat(summary.total_invoice_amount) || 0,
//         matched_grns: parseInt(summary.matched_grns) || 0,
//         mismatched_grns: parseInt(summary.mismatched_grns) || 0,
//         matched_amount: parseFloat(summary.matched_amount) || 0,
//         mismatched_amount: parseFloat(summary.mismatched_amount) || 0,
//         paid_grns: parseInt(summary.paid_grns) || 0,
//         unpaid_grns: parseInt(summary.unpaid_grns) || 0,
//         partial_grns: parseInt(summary.partial_grns) || 0,
//         overdue_grns: parseInt(summary.overdue_grns) || 0,
//       },
//     });
//   } catch (error) {
//     console.error("Get Payable Summary Error:", error);
//     return res.status(500).json({ success: false, message: "Failed to fetch summary" });
//   }
// };

// const uploadInvoiceFile = async (req, res) => {
//   const { grnId } = req.body;
//   const file = req.file;
//   const orgId = req.user?.orgId;
//   const userId = req.user?.id;

//   if (!file) {
//     return res.status(400).json({ success: false, message: "No file uploaded" });
//   }

//   try {
//     // ✅ Always build a clean relative URL — never use file.path (Windows absolute path)
//     const now = new Date();
//     const year = now.getFullYear();
//     const month = String(now.getMonth() + 1).padStart(2, "0");
//     const fileUrl = `/uploads/docs/${year}/${month}/${file.filename}`;

//     const result = await pool.query(
//       `UPDATE grn 
//        SET invoice_file_url = $1, updated_by = $2, updated_at = NOW()
//        WHERE grn_number = $3 AND org_id = $4 AND is_deleted = false
//        RETURNING id, grn_number`,
//       [fileUrl, userId, grnId, orgId]
//     );

//     if (result.rows.length === 0) {
//       if (fs.existsSync(file.path)) fs.unlinkSync(file.path);
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     res.json({ success: true, message: "File uploaded successfully", fileUrl });
//   } catch (error) {
//     console.error("File upload error:", error);
//     if (file?.path && fs.existsSync(file.path)) fs.unlinkSync(file.path);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };


// /**
//  * GET /accounts-payable/grn/:id
//  */
// const getPayableGrnById = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }

//     const query = `
//       WITH grn_totals AS (
//         SELECT 
//           gi.grn_id,
//           COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
//           COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
//         FROM grn_items gi
//         GROUP BY gi.grn_id
//       )
//       SELECT 
//         g.id, g.grn_number, g.grn_date, g.po_number, g.vendor_name,
//         g.invoice_no, g.delivery_challan_no, g.gate_entry_no, g.remarks,
//         g.grn_status, g.created_at, g.final_invoice_amount,
//         g.reconciliation_status, g.payment_status, g.approval_status,
//         g.invoice_file_url, g.org_id,
//         cv.vehicle_number,
//         pv.id AS vendor_id, pv.vendor_code, pv.payment_terms, pv.gstin,
//         pv.phone AS vendor_phone, pv.email AS vendor_email,
//         pv.address AS vendor_address, pv.city, pv.state, pv.pincode,
//         pv.bank_name, pv.account_number, pv.ifsc_code, pv.branch,
//         COALESCE(gt.total_accepted_value, 0) AS grn_amount,
//         COALESCE(gt.total_grand_value, 0) AS invoice_amount
//       FROM grn g
//       LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
//       LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
//       LEFT JOIN grn_totals gt ON gt.grn_id = g.id
//       WHERE g.is_deleted = false AND g.grn_status IN('Approved', 'Partially Approved') AND g.grn_number = $1 AND g.org_id = $2
//     `;

//     const result = await pool.query(query, [id, orgId]);
//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     const grn = result.rows[0];
//     const invoiceAmount = parseFloat(grn.final_invoice_amount || grn.invoice_amount || 0);
//     const grnAmount = parseFloat(grn.grn_amount || 0);
//     const dueDateObj = calculateDueDate(grn.payment_terms, grn.grn_date);
//     const vendorAddress = [grn.vendor_address, grn.city, grn.state, grn.pincode].filter(Boolean).join(", ");

//     return res.status(200).json({
//       success: true,
//       data: {
//         id: grn.grn_number,
//         grn_id: grn.id,
//         vendor: grn.vendor_name,
//         vendor_id: grn.vendor_id,
//         vendor_code: grn.vendor_code,
//         vendor_gst: grn.gstin,
//         vendor_phone: grn.vendor_phone,
//         vendor_email: grn.vendor_email,
//         vendor_address: vendorAddress,
//         bank_name: grn.bank_name,
//         bank_account: grn.account_number,
//         bank_ifsc: grn.ifsc_code,
//         bank_branch: grn.branch,
//         invoiceDate: formatDate(grn.grn_date),
//         dueDate: formatDate(dueDateObj),
//         amount: invoiceAmount,
//         grnAmount,
//         finalInvoiceAmount: grn.final_invoice_amount || invoiceAmount,
//         paymentTerms: grn.payment_terms,
//         status: grn.payment_status || "UNPAID",
//         approval: grn.approval_status || "PENDING",
//         reconciliationStatus: grn.reconciliation_status || "PENDING",
//         invoiceFile: grn.invoice_file_url,
//         po_number: grn.po_number,
//         invoice_no: grn.invoice_no,
//         delivery_challan_no: grn.delivery_challan_no,
//         gate_entry_no: grn.gate_entry_no,
//         vehicle_number: grn.vehicle_number,
//         remarks: grn.remarks,
//         created_at: formatDate(grn.created_at),
//       },
//     });
//   } catch (error) {
//     console.error("Get Payable GRN By ID Error:", error);
//     return res.status(500).json({ success: false, message: "Failed to fetch GRN details" });
//   }
// };

// module.exports = {
//   getPayableGrns,
//   getPaymentTransactions,
//   addTransaction,
//   updatePaymentStatus,
//   updateFinalAmount,
//   reconcileGrn,
//   deletePayableGrn,
//   getPayableSummary,
//   uploadInvoiceFile,
//   getPayableGrnById,
//   getVendorDetails,
// };


/**
 * accountsPayableController.js
 *
 * All AP operations are now GRN-level, not PO-level.
 *
 * Key design:
 *  - Tab 1 (Payables):         approved GRNs where final_invoice_amount = grn_amount (or IS NULL and reconciled)
 *                               i.e. has_discrepancy = false
 *  - Tab 2 (Modifications):    approved GRNs where final_invoice_amount != grn_amount OR final_invoice_amount IS NULL
 *                               i.e. has_discrepancy = true
 *  - grn_amount:               SUM(accepted_qty * unit_price) across grn_items for that GRN
 *  - final_invoice_amount:     what the vendor actually billed for that GRN delivery (set by AP team)
 *  - Reconcile:                sets has_discrepancy = false, moves GRN to Tab 1
 *
 * DB columns assumed on `grn` table (add via migration if missing):
 *   final_invoice_amount   NUMERIC(15,2)
 *   has_discrepancy        BOOLEAN DEFAULT true
 *   payment_status         VARCHAR(20) DEFAULT 'UNPAID'
 *   apply_gst              BOOLEAN DEFAULT true
 *   apply_tds              BOOLEAN DEFAULT true
 *   payment_mode           VARCHAR(20) DEFAULT 'NEFT'
 *   invoice_file           TEXT
 *
 * DB table assumed: `ap_transactions` (grn_id FK, amount, date, time, mode, note)
 */

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

/* ─── Multer setup for invoice file uploads ──────────────────────────────── */
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    const dir = path.join(__dirname, "../../uploads/invoices");
    fs.mkdirSync(dir, { recursive: true });
    cb(null, dir);
  },
  filename: (req, file, cb) => {
    const ext = path.extname(file.originalname);
    cb(null, `invoice_${Date.now()}${ext}`);
  },
});
const upload = multer({ storage, limits: { fileSize: 10 * 1024 * 1024 } });
const uploadMiddleware = upload.single("invoiceFile");

/* ─── Helper: calculate grn_amount from grn_items ────────────────────────── */
async function calcGrnAmount(client, grnId) {
  const res = await client.query(
    `SELECT COALESCE(SUM(accepted_qty * unit_price), 0) AS grn_amount
     FROM grn_items WHERE grn_id = $1`,
    [grnId]
  );
  return parseFloat(res.rows[0]?.grn_amount || 0);
}

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

    const tab = req.query.tab === "2" ? 2 : 1;
    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 || "";

    // Discrepancy: true if final_invoice_amount != grn_amount OR final_invoice_amount IS NULL
    const discrepancyFilter = tab === 1 
      ? `(g.final_invoice_amount IS NOT NULL AND ABS(g.final_invoice_amount - COALESCE((SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0)) <= 0.01)`
      : `(g.final_invoice_amount IS NULL OR ABS(COALESCE(g.final_invoice_amount, 0) - COALESCE((SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0)) > 0.01)`;

    const conditions = [
      "g.is_deleted = false",
      "COALESCE(g.grn_status, 'Draft') = 'Approved'",
      discrepancyFilter,
      "g.org_id = $1",
    ];
    const params = [orgId];
    let pi = 2;

    if (search) {
      conditions.push(`(g.grn_number ILIKE $${pi} OR g.po_number ILIKE $${pi} OR g.vendor_name ILIKE $${pi})`);
      params.push(`%${search}%`);
      pi++;
    }

    if (status) {
      conditions.push(`COALESCE(g.payment_status, 'UNPAID') = $${pi}`);
      params.push(status);
      pi++;
    }

    const WHERE = conditions.join(" AND ");

    const dataQuery = `
      SELECT
        g.id AS grn_id,
        g.grn_number,
        g.grn_date,
        g.po_number,
        g.final_invoice_amount,
        COALESCE(g.payment_status, 'UNPAID') AS status,
        COALESCE(g.apply_gst, true) AS apply_gst,
        COALESCE(g.apply_tds, true) AS apply_tds,
        COALESCE(g.payment_mode, 'NEFT') AS payment_mode,
        g.invoice_file,

        /* PO details */
        po.id AS po_id,
        po.total_amount AS po_total_amount,
        po.po_due_date AS "dueDate",
        po.status AS po_status,

        /* Vendor details */
        pv.vendor_name,
        pv.vendor_code,
        pv.gstin AS vendor_gst,
        pv.phone AS vendor_phone,
        pv.email AS vendor_email,
        pv.address AS vendor_address,
        pv.contact_person AS vendor_contact_person,
        pv.bank_name,
        pv.account_number AS bank_account,
        pv.ifsc_code AS bank_ifsc,
        pv.branch AS bank_branch,

        /* Proforma advance amount - using ::text for type casting */
        COALESCE(
          (SELECT up.advance_amount 
           FROM upload_Proforma up 
           WHERE up.po_number::text = g.po_number::text 
             AND up.org_id::text = g.org_id::text 
             AND up.is_deleted = false 
           ORDER BY up.created_at DESC 
           LIMIT 1), 0
        ) AS advance_amount,

        /* Proforma files */
        COALESCE(
          (SELECT up.file_paths 
           FROM upload_Proforma up 
           WHERE up.po_number::text = g.po_number::text 
             AND up.org_id::text = g.org_id::text 
             AND up.is_deleted = false 
           ORDER BY up.created_at DESC 
           LIMIT 1), NULL
        ) AS proforma_files,

        /* Proforma invoice details */
        COALESCE(
          (SELECT json_build_object(
             'proforma_invoice_number', up.proforma_invoice_number,
             'proforma_invoice_date', up.proforma_invoice_date,
             'advance_required', up.advance_required,
             'advance_amount', up.advance_amount
           )
           FROM upload_Proforma up 
           WHERE up.po_number::text = g.po_number::text 
             AND up.org_id::text = g.org_id::text 
             AND up.is_deleted = false 
           ORDER BY up.created_at DESC 
           LIMIT 1), NULL
        ) AS proforma_details,

        /* Calculate GRN amount on the fly */
        COALESCE(
          (SELECT SUM(gi.accepted_qty * gi.unit_price)
           FROM grn_items gi WHERE gi.grn_id = g.id), 0
        ) AS grn_amount,

        /* Calculate discrepancy */
        CASE 
          WHEN g.final_invoice_amount IS NOT NULL 
            AND ABS(g.final_invoice_amount - COALESCE((SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0)) <= 0.01
          THEN false
          ELSE true
        END AS has_discrepancy,

        /* All GRNs under same PO */
        COALESCE(
          (SELECT json_agg(json_build_object(
            'id', sg.id,
            'grn_number', sg.grn_number,
            'grn_date', sg.grn_date,
            'grn_amount', COALESCE((SELECT SUM(si.accepted_qty * si.unit_price) FROM grn_items si WHERE si.grn_id = sg.id), 0),
            'final_invoice_amount', sg.final_invoice_amount
          ) ORDER BY sg.grn_date)
          FROM grn sg
          WHERE sg.po_number = g.po_number 
            AND sg.org_id = g.org_id 
            AND sg.is_deleted = false
            AND COALESCE(sg.grn_status, 'Draft') = 'Approved'
        ), '[]'::json
        ) AS grns,

        /* Payment transactions */
        COALESCE(
          (SELECT json_agg(json_build_object(
            'id', apt.id,
            'amount', apt.amount,
            'date', apt.txn_date,
            'time', apt.txn_time,
            'mode', apt.payment_mode,
            'note', apt.note
          ) ORDER BY apt.txn_date, apt.txn_time)
          FROM ap_transactions apt WHERE apt.grn_id = g.id), '[]'::json
        ) AS transactions

      FROM grn g
      LEFT JOIN purchase_orders po
             ON po.po_number = g.po_number AND po.org_id = g.org_id AND po.is_deleted = false
      LEFT JOIN procurement_vendors pv
             ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
      WHERE ${WHERE}
      ORDER BY g.grn_date DESC, g.grn_number DESC
      LIMIT $${pi} OFFSET $${pi + 1}
    `;
    params.push(limit, offset);

    const countQuery = `SELECT COUNT(*) FROM grn g WHERE ${WHERE}`;
    const countParams = params.slice(0, -2);

    const [dataRes, countRes] = await Promise.all([
      pool.query(dataQuery, params),
      pool.query(countQuery, countParams),
    ]);

    const total = parseInt(countRes.rows[0].count);

    // Summary query
    const summaryRes = await pool.query(
      `SELECT
         COUNT(*) AS total_grn_count,
         COALESCE(SUM(
           (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id)
         ), 0) AS total_grn_amount,
         COALESCE((
           SELECT SUM(up.advance_amount)
           FROM upload_Proforma up
           WHERE up.po_number::text IN (
             SELECT DISTINCT g2.po_number::text 
             FROM grn g2 
             WHERE g2.org_id = $1 
               AND g2.is_deleted = false 
               AND COALESCE(g2.grn_status, 'Draft') = 'Approved'
           )
           AND up.is_deleted = false
         ), 0) AS total_advance_amount
       FROM grn g
       WHERE g.org_id = $1
         AND g.is_deleted = false
         AND COALESCE(g.grn_status, 'Draft') = 'Approved'`,
      [orgId]
    );

    // Process data to add proforma flags
    const processedData = dataRes.rows.map(row => ({
      ...row,
      has_proforma: !!(row.proforma_files || row.proforma_details?.advance_required),
      proforma_invoice_number: row.proforma_details?.proforma_invoice_number,
      proforma_invoice_date: row.proforma_details?.proforma_invoice_date,
      advance_required: row.proforma_details?.advance_required || false,
      proforma_total_amount: row.po_total_amount,
    }));

    return res.status(200).json({
      success: true,
      data: processedData,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit),
      },
      summary: summaryRes.rows[0],
    });

  } catch (error) {
    console.error("getPayableGRNs Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch payable GRNs", error: error.message });
  }
};


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

    const result = await pool.query(
      `SELECT
         COUNT(*) FILTER (
           WHERE g.final_invoice_amount IS NOT NULL 
             AND ABS(g.final_invoice_amount - COALESCE((SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0)) <= 0.01
         ) AS tab1_count,
         COUNT(*) FILTER (
           WHERE g.final_invoice_amount IS NULL 
             OR ABS(COALESCE(g.final_invoice_amount, 0) - COALESCE((SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0)) > 0.01
         ) AS tab2_count
       FROM grn g
       WHERE g.org_id = $1
         AND g.is_deleted = false
         AND COALESCE(g.grn_status, 'Draft') = 'Approved'`,
      [orgId]
    );

    return res.status(200).json({
      success: true,
      tab1Count: parseInt(result.rows[0].tab1_count || 0),
      tab2Count: parseInt(result.rows[0].tab2_count || 0),
    });

  } catch (error) {
    console.error("getTabCounts Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch tab counts" });
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   PUT /accounts-payable/update-final-amount/:grnId
   Set the vendor's invoice amount for this GRN.
   If finalAmount === grn_amount → auto-reconcile (has_discrepancy = false).
═══════════════════════════════════════════════════════════════════════════ */
const updateFinalAmount = async (req, res) => {
  const client = await pool.connect();
  try {
    const { grnId }     = req.params;
    const { finalAmount } = req.body;
    const orgId         = req.user?.orgId || req.session?.user?.orgId;

    if (!finalAmount || isNaN(finalAmount) || Number(finalAmount) <= 0) {
      return res.status(400).json({ success: false, message: "Valid finalAmount is required" });
    }

    await client.query("BEGIN");

    // Recalculate grn_amount fresh from grn_items
    const grnAmount = await calcGrnAmount(client, grnId);
    const finalAmt  = parseFloat(finalAmount);
    const hasDiscrepancy = Math.abs(finalAmt - grnAmount) > 0.01;

    const result = await client.query(
      `UPDATE grn
       SET final_invoice_amount = $1,
           has_discrepancy      = $2,
           updated_at           = NOW()
       WHERE id = $3
         AND org_id = $4
         AND is_deleted = false
       RETURNING id, grn_number, final_invoice_amount, has_discrepancy`,
      [finalAmt, hasDiscrepancy, grnId, orgId]
    );

    if (result.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "GRN not found" });
    }

    await client.query("COMMIT");
    return res.status(200).json({
      success:       true,
      message:       hasDiscrepancy ? "Final amount updated (discrepancy remains)" : "Final amount updated and reconciled",
      data:          result.rows[0],
      reconciled:    !hasDiscrepancy,
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("updateFinalAmount Error:", error);
    return res.status(500).json({ success: false, message: "Failed to update final amount" });
  } finally {
    client.release();
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   POST /accounts-payable/reconcile/:grnId
   Explicitly mark a GRN as reconciled (has_discrepancy = false).
   Called when finalInvoiceAmount === grnAmount.
═══════════════════════════════════════════════════════════════════════════ */
const reconcileGRN = async (req, res) => {
  try {
    const { grnId }       = req.params;
    const { matchedAmount } = req.body;
    const orgId           = req.user?.orgId || req.session?.user?.orgId;

    const result = await pool.query(
      `UPDATE grn
       SET has_discrepancy      = false,
           final_invoice_amount = COALESCE($1, final_invoice_amount),
           updated_at           = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, grn_number, has_discrepancy`,
      [matchedAmount || null, grnId, orgId]
    );

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

    return res.status(200).json({
      success: true,
      message: `GRN ${result.rows[0].grn_number} reconciled`,
      data:    result.rows[0],
    });

  } catch (error) {
    console.error("reconcileGRN Error:", error);
    return res.status(500).json({ success: false, message: "Failed to reconcile GRN" });
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   PUT /accounts-payable/update-payment/:grnId
   Update GST/TDS settings, payment mode, and payment status for a GRN.
═══════════════════════════════════════════════════════════════════════════ */
const updatePayment = async (req, res) => {
  try {
    const { grnId }                                          = req.params;
    const { gst, tds, applyGst, applyTds, paymentMode, status } = req.body;
    const orgId = req.user?.orgId || req.session?.user?.orgId;

    const result = await pool.query(
      `UPDATE grn
       SET apply_gst      = COALESCE($1, apply_gst),
           apply_tds      = COALESCE($2, apply_tds),
           payment_mode   = COALESCE($3, payment_mode),
           payment_status = COALESCE($4, payment_status),
           updated_at     = NOW()
       WHERE id = $5 AND org_id = $6 AND is_deleted = false
       RETURNING id, grn_number, payment_status`,
      [applyGst ?? null, applyTds ?? null, paymentMode || null, status || null, grnId, orgId]
    );

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

    return res.status(200).json({ success: true, message: "Payment updated", data: result.rows[0] });

  } catch (error) {
    console.error("updatePayment Error:", error);
    return res.status(500).json({ success: false, message: "Failed to update payment" });
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   POST /accounts-payable/add-transaction
   Add a payment transaction against a GRN.
   Body: { grnId, transaction: { date, time, amount, mode, note } }
═══════════════════════════════════════════════════════════════════════════ */
const addTransaction = async (req, res) => {
  const client = await pool.connect();
  try {
    const { grnId, transaction }   = req.body;
    const orgId                    = req.user?.orgId || req.session?.user?.orgId;

    if (!grnId || !transaction?.date || !transaction?.amount) {
      return res.status(400).json({ success: false, message: "grnId, transaction.date and transaction.amount are required" });
    }

    const amt = parseFloat(transaction.amount);
    if (isNaN(amt) || amt <= 0) {
      return res.status(400).json({ success: false, message: "Transaction amount must be positive" });
    }

    await client.query("BEGIN");

    /* Verify GRN belongs to org */
    const grnCheck = await client.query(
      "SELECT id FROM grn WHERE id = $1 AND org_id = $2 AND is_deleted = false",
      [grnId, orgId]
    );
    if (grnCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "GRN not found" });
    }

    const txnRes = await client.query(
      `INSERT INTO ap_transactions (grn_id, amount, txn_date, txn_time, payment_mode, note, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, NOW())
       RETURNING *`,
      [grnId, amt, transaction.date, transaction.time || null, transaction.mode || "NEFT", transaction.note || null]
    );

    /* Recalculate payment_status based on all transactions vs net payable */
    const grnAmtRes = await client.query(
      `SELECT
         COALESCE(SUM(gi.accepted_qty * gi.unit_price), 0) AS grn_amount,
         COALESCE(g.apply_gst, true)                       AS apply_gst,
         COALESCE(g.apply_tds, true)                       AS apply_tds,
         COALESCE(g.final_invoice_amount,
           (SELECT SUM(si.accepted_qty * si.unit_price) FROM grn_items si WHERE si.grn_id = g.id)
         )                                                  AS base_amt
       FROM grn g
       LEFT JOIN grn_items gi ON gi.grn_id = g.id
       WHERE g.id = $1
       GROUP BY g.id, g.apply_gst, g.apply_tds, g.final_invoice_amount`,
      [grnId]
    );

    let newStatus = "UNPAID";
    if (grnAmtRes.rows.length > 0) {
      const { base_amt, apply_gst, apply_tds } = grnAmtRes.rows[0];
      const baseAmt  = parseFloat(base_amt || 0);
      const gstAmt   = apply_gst ? Math.round(baseAmt * 0.18) : 0;
      const tdsAmt   = apply_tds ? Math.round(baseAmt * 0.02) : 0;
      const net      = baseAmt + gstAmt - tdsAmt;

      const paidRes = await client.query(
        "SELECT COALESCE(SUM(amount), 0) AS total_paid FROM ap_transactions WHERE grn_id = $1",
        [grnId]
      );
      const totalPaid = parseFloat(paidRes.rows[0].total_paid);

      newStatus = totalPaid <= 0 ? "UNPAID" : totalPaid >= net ? "PAID" : "PARTIAL";
    }

    await client.query(
      "UPDATE grn SET payment_status = $1, updated_at = NOW() WHERE id = $2",
      [newStatus, grnId]
    );

    await client.query("COMMIT");
    return res.status(201).json({
      success:     true,
      message:     "Transaction added",
      data:        txnRes.rows[0],
      newStatus,
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("addTransaction Error:", error);
    return res.status(500).json({ success: false, message: "Failed to add transaction" });
  } finally {
    client.release();
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   POST /accounts-payable/upload-invoice
   Upload vendor invoice file for a GRN.
   Form fields: invoiceFile (file), grnId
═══════════════════════════════════════════════════════════════════════════ */
const uploadInvoice = (req, res) => {
  uploadMiddleware(req, res, async (err) => {
    if (err) return res.status(400).json({ success: false, message: "File upload error: " + err.message });

    try {
      const { grnId } = req.body;
      const orgId     = req.user?.orgId || req.session?.user?.orgId;

      if (!grnId || !req.file) {
        return res.status(400).json({ success: false, message: "grnId and file are required" });
      }

      const filePath = `/uploads/invoices/${req.file.filename}`;

      await pool.query(
        "UPDATE grn SET invoice_file = $1, updated_at = NOW() WHERE id = $2 AND org_id = $3",
        [filePath, grnId, orgId]
      );

      return res.status(200).json({ success: true, message: "Invoice file uploaded", filePath });

    } catch (error) {
      console.error("uploadInvoice Error:", error);
      return res.status(500).json({ success: false, message: "Failed to save invoice file" });
    }
  });
};

/* ═══════════════════════════════════════════════════════════════════════════
   DELETE /accounts-payable/grn/:grnId
   Remove a GRN from the payables view.
   Only clears AP-specific fields; does NOT delete the GRN itself.
   (Sets has_discrepancy = true so it drops out of both tabs until re-processed,
    or you can use a separate `in_payables` flag if preferred.)
═══════════════════════════════════════════════════════════════════════════ */
const removeFromPayables = async (req, res) => {
  try {
    const { grnId } = req.params;
    const orgId     = req.user?.orgId || req.session?.user?.orgId;

    /* Strategy: reset payment fields so GRN drops out of payables tabs */
    const result = await pool.query(
      `UPDATE grn
       SET payment_status      = 'UNPAID',
           final_invoice_amount = NULL,
           has_discrepancy      = true,
           invoice_file         = NULL,
           updated_at           = NOW()
       WHERE id = $1 AND org_id = $2 AND is_deleted = false
       RETURNING id, grn_number`,
      [grnId, orgId]
    );

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

    return res.status(200).json({
      success: true,
      message: `GRN ${result.rows[0].grn_number} removed from payables`,
    });

  } catch (error) {
    console.error("removeFromPayables Error:", error);
    return res.status(500).json({ success: false, message: "Failed to remove GRN from payables" });
  }
};


 /**
//  * GET /accounts-payable/summary
//  */
const getPayableSummary = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const query = `
      WITH grn_totals AS (
        SELECT 
          gi.grn_id,
          COALESCE(SUM(gi.total_price), 0) AS total_accepted_value,
          COALESCE(SUM(gi.grand_total), 0) AS total_grand_value
        FROM grn_items gi
        GROUP BY gi.grn_id
      ),
      payable_grns AS (
        SELECT 
          g.payment_status,
          COALESCE(gt.total_accepted_value, 0) AS total_accepted_value,
          COALESCE(gt.total_grand_value, 0) AS total_grand_value
        FROM grn g
        LEFT JOIN grn_totals gt ON gt.grn_id = g.id
        WHERE g.is_deleted = false AND g.grn_status IN('Approved', 'Partially Approved') AND g.org_id = $1
      )
      SELECT 
        COUNT(*) AS total_grns,
        COALESCE(SUM(total_accepted_value), 0) AS total_payable_amount,
        COALESCE(SUM(total_grand_value), 0) AS total_invoice_amount,
        COUNT(CASE WHEN ROUND(total_accepted_value::numeric,2) = ROUND(total_grand_value::numeric,2) THEN 1 END) AS matched_grns,
        COUNT(CASE WHEN ROUND(total_accepted_value::numeric,2) != ROUND(total_grand_value::numeric,2) THEN 1 END) AS mismatched_grns,
        COALESCE(SUM(CASE WHEN ROUND(total_accepted_value::numeric,2) = ROUND(total_grand_value::numeric,2) THEN total_grand_value ELSE 0 END), 0) AS matched_amount,
        COALESCE(SUM(CASE WHEN ROUND(total_accepted_value::numeric,2) != ROUND(total_grand_value::numeric,2) THEN total_grand_value ELSE 0 END), 0) AS mismatched_amount,
        COUNT(CASE WHEN payment_status = 'PAID' THEN 1 END) AS paid_grns,
        COUNT(CASE WHEN payment_status = 'UNPAID' THEN 1 END) AS unpaid_grns,
        COUNT(CASE WHEN payment_status = 'PARTIAL' THEN 1 END) AS partial_grns,
        COUNT(CASE WHEN payment_status = 'OVERDUE' THEN 1 END) AS overdue_grns
      FROM payable_grns
    `;

    const result = await pool.query(query, [orgId]);
    const summary = result.rows[0];

    return res.status(200).json({
      success: true,
      data: {
        total_grns: parseInt(summary.total_grns) || 0,
        total_payable_amount: parseFloat(summary.total_payable_amount) || 0,
        total_invoice_amount: parseFloat(summary.total_invoice_amount) || 0,
        matched_grns: parseInt(summary.matched_grns) || 0,
        mismatched_grns: parseInt(summary.mismatched_grns) || 0,
        matched_amount: parseFloat(summary.matched_amount) || 0,
        mismatched_amount: parseFloat(summary.mismatched_amount) || 0,
        paid_grns: parseInt(summary.paid_grns) || 0,
        unpaid_grns: parseInt(summary.unpaid_grns) || 0,
        partial_grns: parseInt(summary.partial_grns) || 0,
        overdue_grns: parseInt(summary.overdue_grns) || 0,
      },
    });
  } catch (error) {
    console.error("Get Payable Summary Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch summary" });
  }
};
module.exports = {
  getPayableGRNs,
  getTabCounts,
  updateFinalAmount,
  reconcileGRN,
  updatePayment,
  addTransaction,
  uploadInvoice,
  removeFromPayables,
  getPayableSummary,
};