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

function formatDate(utcDateString) {
  if (!utcDateString) return "--";

  const date = new Date(utcDateString);

  if (isNaN(date.getTime())) return "--";

  return date.toLocaleDateString("en-IN", {
    timeZone: "Asia/Kolkata",
    day: "2-digit",
    month: "2-digit",
    year: "numeric",
  });
}

// const createInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;

//     if (!orgId || !userId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       customer_id,
//       customer_name,
//       customer_address,
//       customer_gst,
//       invoice_date,
//       uom_symbol,
//       due_date,
//       notes,
//       items,
//     } = req.body;

//     if (!customer_id || !items?.length) {
//       return res.status(400).json({
//         message: "Customer and items required",
//       });
//     }

//     await client.query("BEGIN");

//     // 🔹 GENERATE INVOICE NUMBER
//     const invRes = await client.query(
//       `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
//       [orgId],
//     );

//     const count = Number(invRes.rows[0].count) + 1;

//     // pad with zeros → 001, 002, 010
//     const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;
//     let subtotal = 0;
//     let totalDiscount = 0;
//     let totalTax = 0;
//     let totalAmount = 0;

//     // 🔹 INSERT HEADER
//     const invoiceInsert = await client.query(
//       `INSERT INTO sales_invoices (
//         invoice_number,
//         org_id,
//         customer_code,
//         customer_name,
//         customer_address,
//         customer_gst,
//         invoice_date,
//         due_date,
//         amount,
//         total_discount,
//         total_tax,
//         total_amount,
//         notes,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
//       RETURNING id`,
//       [
//         invoiceNumber,
//         orgId,
//         customer_id,
//         customer_name,
//         customer_address,
//         customer_gst,
//         invoice_date || new Date(),
//         due_date || null,
//         0,
//         0,
//         0,
//         0,
//         notes || "",
//         userId,
//       ],
//     );

//     const invoiceId = invoiceInsert.rows[0].id;

//     // 🔹 INSERT ITEMS
//     for (const item of items) {
//       const qty = Number(item.qty) || 0;
//       const rate = Number(item.rate) || 0;
//       const gst = Number(item.gst) || 18;
//       const discount = Number(item.discount) || 0;

//       const amount = qty * rate;
//       const discountAmount = Math.min(discount, amount);
//       const taxable = Math.max(amount - discountAmount, 0);
//       const gstAmount = (taxable * gst) / 100;
//       const total = taxable + gstAmount;

//       subtotal += amount;
//       totalDiscount += discountAmount;
//       totalTax += gstAmount;
//       totalAmount += total;

//       await client.query(
//         `INSERT INTO sales_invoice_items (
//           invoice_id,
//           product_id,
//           product_name,
//           qty,
//            uom_symbol,
//           rate,
//           amount,
//           discount_amount,
//           gst_percent,
//           gst_amount,
//           total_amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
//         [
//           invoiceId,
//           item.product_id,
//           item.product_name,
//           qty,
//           item.uom_symbol,
//           rate,
//           amount,
//           discountAmount,
//           gst,
//           gstAmount,
//           total,
//         ],
//       );
//     }

//     // 🔹 UPDATE TOTALS
//     await client.query(
//       `UPDATE sales_invoices SET
//         amount = $1,
//         total_discount = $2,
//         total_tax = $3,
//         total_amount = $4
//       WHERE id = $5`,
//       [subtotal, totalDiscount, totalTax, totalAmount, invoiceId],
//     );

//     await client.query("COMMIT");

//     res.json({
//       success: true,
//       message: "Invoice created",
//       invoiceId,
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE INVOICE ERROR:", err);
//     res.status(500).json({ message: "Failed to create invoice" });
//   } finally {
//     client.release();
//   }
// };

const generateInvoicePDF = require("../../services/pdf/generateInvoicePdf");

// const createInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;

//     if (!orgId || !userId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       customer_id,
//       customer_name,
//       customer_address,
//       customer_gst,
//       invoice_date,
//       due_date,
//       notes,
//       items,
//     } = req.body;

//     if (!customer_id || !items?.length) {
//       return res.status(400).json({
//         message: "Customer and items required",
//       });
//     }

//     await client.query("BEGIN");

//     // =========================
//     // 🔹 GENERATE INVOICE NUMBER
//     // =========================
//     const invRes = await client.query(
//       `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
//       [orgId],
//     );

//     const count = Number(invRes.rows[0].count) + 1;
//     const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;

//     let subtotal = 0;
//     let totalDiscount = 0;
//     let totalTax = 0;
//     let totalAmount = 0;

//     // =========================
//     // 🔹 INSERT HEADER
//     // =========================
//     const invoiceInsert = await client.query(
//       `INSERT INTO sales_invoices (
//         invoice_number,
//         org_id,
//         customer_code,
//         customer_name,
//         customer_address,
//         customer_gst,
//         invoice_date,
//         due_date,
//         amount,
//         total_discount,
//         total_tax,
//         total_amount,
//         notes,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
//       RETURNING id`,
//       [
//         invoiceNumber,
//         orgId,
//         customer_id,
//         customer_name,
//         customer_address,
//         customer_gst,
//         invoice_date || new Date(),
//         due_date || null,
//         0,
//         0,
//         0,
//         0,
//         notes || "",
//         userId,
//       ],
//     );

//     const invoiceId = invoiceInsert.rows[0].id;
//     await client.query(
//       `
//   INSERT INTO invoice_approvals (
//     invoice_id,
//     user_id,
//     status,
//     comments
//   )
//   VALUES ($1, $2, 'pending', 'Awaiting Approval')
//   `,
//       [invoiceId, userId],
//     );

//     // =========================
//     // 🔹 INSERT ITEMS
//     // =========================
//     for (const item of items) {
//       const qty = Number(item.qty) || 0;
//       const rate = Number(item.rate) || 0;
//       const gst = Number(item.gst) || 18;
//       const discount = Number(item.discount) || 0;

//       const amount = qty * rate;
//       const discountAmount = Math.min(discount, amount);
//       const taxable = Math.max(amount - discountAmount, 0);
//       const gstAmount = (taxable * gst) / 100;
//       const total = taxable + gstAmount;

//       subtotal += amount;
//       totalDiscount += discountAmount;
//       totalTax += gstAmount;
//       totalAmount += total;

//       await client.query(
//         `INSERT INTO sales_invoice_items (
//           invoice_id,
//           product_id,
//           product_name,
//           qty,
//           uom_symbol,
//           rate,
//           amount,
//           discount_amount,
//           gst_percent,
//           gst_amount,
//           total_amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
//         [
//           invoiceId,
//           item.product_id,
//           item.product_name,
//           qty,
//           item.uom_symbol,
//           rate,
//           amount,
//           discountAmount,
//           gst,
//           gstAmount,
//           total,
//         ],
//       );
//     }

//     // =========================
//     // 🔹 UPDATE TOTALS
//     // =========================
//     await client.query(
//       `UPDATE sales_invoices SET
//         amount = $1,
//         total_discount = $2,
//         total_tax = $3,
//         total_amount = $4
//       WHERE id = $5`,
//       [subtotal, totalDiscount, totalTax, totalAmount, invoiceId],
//     );

//     await client.query("COMMIT");

//     // =========================
//     // 📧 EMAIL + PDF
//     // =========================

//     const nodemailer = require("nodemailer");

//     const transporter = nodemailer.createTransport({
//       service: "gmail",
//       auth: {
//         user: process.env.MAIL_USER,
//         pass: process.env.MAIL_PASS,
//       },
//     });

//     // 🔹 GET CUSTOMER EMAIL
//     const custRes = await pool.query(
//       `SELECT email FROM customers WHERE customer_code = $1`,
//       [customer_id],
//     );

//     const customerEmail = custRes.rows[0]?.email;

//     const itemsRes = await client.query(
//       `SELECT * FROM sales_invoice_items WHERE invoice_id = $1`,
//       [invoiceId],
//     );

//     const dbItems = itemsRes.rows;

//     // fetch company
//     const companyRes = await client.query(
//       `SELECT org_name, address, gstin, cin, phone, email, bank_details FROM organizations WHERE id = $1`,
//       [orgId],
//     );
//     const company = companyRes.rows[0] || {};

//     const userRes = await client.query(`SELECT name FROM users WHERE id = $1`, [
//       userId,
//     ]);

//     const currentUser = userRes.rows[0] || {};

//     const soRes = await client.query(
//       `
//   SELECT so_number, po_reference, created_at
//   FROM sales_orders
//   WHERE customer_id = $1
//   ORDER BY created_at DESC
//   LIMIT 1
//   `,
//       [customer_id],
//     );

//     const salesOrder = soRes.rows[0] || {};

//     let pdfBuffer = null;

//     // 🔹 GENERATE PDF (from utils)
//     try {
//       pdfBuffer = await generateInvoicePDF(
//         {
//           invoice_number: invoiceNumber,
//           invoice_date,
//           due_date,
//           customer_name,
//           customer_address,
//           customer_gst,
//           order_no: salesOrder.so_number || "",
//           po_no: salesOrder.po_reference || "",
//           po_date: salesOrder.created_at || "",

//           // Invoice Summary ADD THESE
//           gross: subtotal,
//           discount: totalDiscount,
//           net_value: subtotal - totalDiscount,
//           freight: 0, // or dynamic later
//           cgst: totalTax / 2,
//           sgst: totalTax / 2,
//           round_off: 0,
//           total: totalAmount,
//           prepared_by: currentUser.name || "-",
//           verified_by: currentUser.name || "-",
//           approved_by: currentUser.name || "-",
//           authorized_signatory: company.org_name || "-",
//         },
//         dbItems,
//         {
//           org_name: company.org_name,
//           address: company.address,
//           gstin: company.gstin,
//           cin: company.cin,
//           phone: company.phone,
//           email: company.email,
//           bank_details: company.bank_details,
//         },
//       );
//     } catch (err) {
//       console.error("PDF ERROR:", err);
//     }

//     // 🔹 EMAIL TEMPLATE
//     //     const emailHtml = `
//     // <div style="font-family: Arial; background:#f4f6f8; padding:20px;">
//     //   <table style="max-width:650px;margin:auto;background:#fff;border-radius:10px;border:1px solid #e5e7eb;overflow:hidden;">

//     //     <tr style="background:linear-gradient(90deg,#0f172a,#1e293b);color:#fff;">
//     //       <td style="padding:18px;font-size:18px;">
//     //         Tax Invoice Generated
//     //       </td>
//     //     </tr>

//     //     <tr>
//     //       <td style="padding:25px;">
//     //         <p>Dear <strong>${customer_name}</strong>,</p>

//     //         <p>
//     //           Your invoice <strong>${invoiceNumber}</strong> has been generated successfully.
//     //         </p>

//     //         <div style="background:#f1f5f9;padding:12px;border-radius:8px;margin:15px 0;">
//     //           📎 Invoice PDF is attached
//     //         </div>

//     //         <table width="100%">
//     //           <tr>
//     //             <td><strong>Total Amount</strong></td>
//     //             <td style="text-align:right;">₹${totalAmount.toFixed(2)}</td>
//     //           </tr>
//     //           <tr>
//     //             <td><strong>Due Date</strong></td>
//     //             <td style="text-align:right;">${due_date || "-"}</td>
//     //           </tr>
//     //         </table>

//     //         <p style="margin-top:20px;">
//     //           Kindly ensure timely payment.
//     //         </p>

//     //         <p>
//     //           Regards,<br/>
//     //           <strong>Accounts Team</strong>
//     //         </p>
//     //       </td>
//     //     </tr>

//     //   </table>
//     // </div>
//     // `;
//     const emailHtml = `
// <div style="font-family: 'Segoe UI', Arial, sans-serif; background:#f4f6f8; padding:30px 0;">

//   <table width="100%" cellpadding="0" cellspacing="0"
//          style="max-width:680px;margin:auto;background:#ffffff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb;box-shadow:0 8px 20px rgba(0,0,0,0.04);">

//     <!-- HEADER -->
//     <tr>
//       <td style="background:linear-gradient(135deg,#2563eb,#1e3a8a);padding:22px 28px;color:#fff;">
//         <div style="font-size:18px;font-weight:600;">
//           Tax Invoice Generated
//         </div>
//         <div style="font-size:12px;opacity:0.85;margin-top:4px;">
//           ERP Enterprise
//         </div>
//       </td>
//     </tr>

//     <!-- BODY -->
//     <tr>
//       <td style="padding:28px;color:#374151;font-size:14px;line-height:1.6;">

//         <p style="margin:0 0 12px;">
//           Dear <strong>${customer_name}</strong>,
//         </p>

//         <p style="margin:0 0 16px;">
//           Your invoice <strong style="color:#111827;">${invoiceNumber}</strong> has been successfully generated.
//         </p>

//         <!-- INFO CARD -->
//         <div style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;padding:16px;margin:18px 0;">
//           <table width="100%" style="font-size:13px;">
//             <tr>
//               <td style="color:#6b7280;">Invoice Number</td>
//               <td style="text-align:right;font-weight:600;">${invoiceNumber}</td>
//             </tr>
//             <tr>
//               <td style="color:#6b7280;padding-top:8px;">Total Amount</td>
//               <td style="text-align:right;font-weight:600;color:#059669;padding-top:8px;">
//                 ₹${totalAmount.toFixed(2)}
//               </td>
//             </tr>
//             <tr>
//               <td style="color:#6b7280;padding-top:8px;">Due Date</td>
//               <td style="text-align:right;padding-top:8px;">
//                 ${due_date || "-"}
//               </td>
//             </tr>
//           </table>
//         </div>

//         <!-- ATTACHMENT NOTE -->
//         <div style="background:#eef2ff;border:1px solid #c7d2fe;border-radius:8px;padding:12px 14px;font-size:13px;">
//           📎 The invoice PDF is attached with this email for your reference.
//         </div>

//         <!-- MESSAGE -->
//         <p style="margin-top:20px;">
//           Kindly review the invoice and ensure payment is completed on or before the due date.
//         </p>

//         <!-- CTA BUTTON -->
//         <div style="margin:25px 0;">
//           <a href="#"
//              style="background:#2563eb;color:#fff;padding:10px 18px;border-radius:6px;text-decoration:none;font-size:13px;display:inline-block;">
//              Contact Support
//           </a>
//         </div>

//         <!-- SIGNATURE -->
//         <p style="margin-top:20px;">
//           Regards,<br/>
//           <strong>Accounts Team</strong><br/>
//           <span style="font-size:12px;color:#6b7280;">
//             ERP Enterprise
//           </span>
//         </p>

//       </td>
//     </tr>

//     <!-- FOOTER -->
//     <tr>
//       <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#9ca3af;">
//         This is an automated invoice notification. Please do not reply.
//       </td>
//     </tr>

//   </table>
// </div>
// `;

//     // 🔹 SEND EMAIL
//     if (customerEmail) {
//       await transporter.sendMail({
//         from: `"Sales Team" <${process.env.MAIL_USER}>`,
//         to: customerEmail,
//         subject: `Invoice ${invoiceNumber}`,
//         html: emailHtml,
//         // attachments: [
//         //   {
//         //     filename: `Invoice-${invoiceNumber}.pdf`,
//         //     content: pdfBuffer,
//         //   },
//         // ],
//         attachments: pdfBuffer
//           ? [
//               {
//                 filename: `Invoice-${invoiceNumber}.pdf`,
//                 content: pdfBuffer,
//               },
//             ]
//           : [],
//       });
//     }

//     return res.json({
//       success: true,
//       message: "Invoice created & sent successfully",
//       invoiceId,
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE INVOICE ERROR:", err);

//     return res.status(500).json({
//       message: err.message || "Failed to create invoice",
//     });
//   } finally {
//     client.release();
//   }
// };

const validateInvoiceCreation = async (client, sales_order_id, items, orgId) => {
  // Get sales order details
  const soRes = await client.query(
    `
    SELECT 
      so.id,
      so.so_number,
      so.with_advance_payment,
      COALESCE(SUM(soi.qty), 0) as total_qty
    FROM sales_orders so
    LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
    WHERE so.id = $1 AND so.org_id = $2
    GROUP BY so.id
    `,
    [sales_order_id, orgId]
  );
  
  if (!soRes.rows.length) {
    throw new Error("Sales order not found");
  }
  
  const salesOrder = soRes.rows[0];
  const totalOrderQty = Number(salesOrder.total_qty);
  
  // Get existing invoices
  const existingInvoices = await client.query(
    `
    SELECT 
      si.id,
      si.invoice_number,
      si.status,
      si.total_amount,
      COALESCE(SUM(sii.qty), 0) as invoiced_qty
    FROM sales_invoices si
    LEFT JOIN sales_invoice_items sii ON sii.invoice_id = si.id
    WHERE si.sales_order_id = $1 
      AND si.status != 'cancelled'
      AND si.is_deleted = false
    GROUP BY si.id, si.invoice_number, si.status, si.total_amount
    `,
    [sales_order_id]
  );
  
  let totalInvoicedQty = 0;
  let hasApprovedInvoice = false;
  let hasPendingInvoice = false;
  
  for (const inv of existingInvoices.rows) {
    totalInvoicedQty += Number(inv.invoiced_qty || 0);
    if (inv.status === 'approved') hasApprovedInvoice = true;
    if (inv.status === 'pending') hasPendingInvoice = true;
  }
  
  // Calculate requested quantity
  const requestedQty = items.reduce((sum, item) => sum + (Number(item.qty) || 0), 0);
  const remainingQty = totalOrderQty - totalInvoicedQty;
  
  // Validation results
  const validation = {
    isValid: true,
    message: null,
    data: {
      total_order_qty: totalOrderQty,
      total_invoiced_qty: totalInvoicedQty,
      remaining_qty: remainingQty,
      requested_qty: requestedQty,
      has_approved_invoice: hasApprovedInvoice,
      has_pending_invoice: hasPendingInvoice,
      existing_invoices: existingInvoices.rows
    }
  };
  
  // Check 1: Full quantity already invoiced
  if (hasApprovedInvoice && totalInvoicedQty >= totalOrderQty) {
    validation.isValid = false;
    validation.message = `Invoice already raised for full quantity (${totalOrderQty} units). Cannot create another invoice.`;
    return validation;
  }
  
  // Check 2: Requested quantity exceeds remaining
  if (requestedQty > remainingQty) {
    validation.isValid = false;
    validation.message = `Requested quantity (${requestedQty}) exceeds remaining quantity (${remainingQty}).`;
    return validation;
  }
  
  // Check 3: Pending invoice exists (optional - can be configurable)
  if (hasPendingInvoice) {
    validation.isValid = false;
    validation.message = `A pending invoice already exists. Please approve or cancel it first.`;
    return validation;
  }
  
  // Check 4: Zero quantity validation
  if (requestedQty <= 0) {
    validation.isValid = false;
    validation.message = `Invoice quantity must be greater than 0.`;
    return validation;
  }
  
  return validation;
};

const createInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      sales_order_id,
      sales_order_number,
      customer_id,
      customer_name,
      customer_address,
      customer_gst,
      invoice_date,
      due_date,
      notes,
      freight_charges,
      items,
    } = req.body;

    console.log(freight_charges, "why am i not geting")

    if (!customer_id || !items?.length) {
      return res.status(400).json({
        message: "Customer and items required",
      });
    }

    await client.query("BEGIN");

    // =========================
    // 🔹 GENERATE INVOICE NUMBER
    // =========================
    const invRes = await client.query(
      `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
      [orgId],
    );

    const count = Number(invRes.rows[0].count) + 1;
    const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;

    let subtotal = 0;
    let totalDiscount = 0;
    let totalTax = 0;
    let totalAmount = 0;

    let advanceAdjustedAmount = 0;

    let balancePayableAmount = 0;

    let proformaInvoiceId = null;

    const soResult = await client.query(
  `
  SELECT
    id,
    with_advance_payment
  FROM sales_orders
  WHERE id = $1
  LIMIT 1
  `,
  [sales_order_id]
);

const salesOrders = soResult.rows[0];

if (!salesOrders) {
  throw new Error("Sales Order not found");
}
const validation = await validateInvoiceCreation(
  client,
  sales_order_id,
  items,
  orgId
);
if (!validation.isValid) {
  await client.query("ROLLBACK");
  return res.status(400).json({
    success: false,
    message: validation.message,
    data: validation.data
  });
}

    // =========================
    // 🔹 INSERT HEADER
    // =========================
    const invoiceInsert = await client.query(
      `INSERT INTO sales_invoices (
       sales_order_id,
  sales_order_number,
        invoice_number,
        org_id,
        customer_code,
        customer_name,
        customer_address,
        customer_gst,
        invoice_date,
        due_date,
        amount,
        freight_charges,
        total_discount,
        total_tax,
        total_amount,
        notes,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
      RETURNING id`,
      [
        sales_order_id,
        sales_order_number,
        invoiceNumber,
        orgId,
        customer_id,
        customer_name,
        customer_address,
        customer_gst,
        invoice_date || new Date(),
        due_date || null,
        0,
        freight_charges,
        0,
        0,
        0,
        notes || "",
        userId,
      ],
    );

    const invoiceId = invoiceInsert.rows[0].id;
    await client.query(
      `
  INSERT INTO invoice_approvals (
    invoice_id,
    user_id,
    status,
    comments
  )
  VALUES ($1, $2, 'pending', 'Awaiting Approval')
  `,
      [invoiceId, userId],
    );

    // =========================
    // 🔹 INSERT ITEMS
    // =========================
    for (const item of items) {
      const qty = Number(item.qty) || 0;
      const rate = Number(item.rate) || 0;
      const gst = Number(item.gst) || 18;
      const discount = Number(item.discount) || 0;

      const amount = qty * rate;
      const discountAmount = Math.min(discount, amount);
      const taxable = Math.max(amount - discountAmount, 0);
      const gstAmount = (taxable * gst) / 100;
      const total = taxable + gstAmount;

      subtotal += amount;
      totalDiscount += discountAmount;
      totalTax += gstAmount;
      totalAmount += total;

      await client.query(
        `INSERT INTO sales_invoice_items (
          invoice_id,
          product_id,
          product_name,
          qty,
          uom_symbol,
          rate,
          amount,
          discount_amount,
          gst_percent,
          gst_amount,
          total_amount
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
        [
          invoiceId,
          item.product_id,
          item.product_name,
          qty,
          item.uom_symbol,
          rate,
          amount,
          discountAmount,
          gst,
          gstAmount,
          total,
        ],
      );
    }

    if (salesOrders.with_advance_payment) {

  const proformaRes = await client.query(
    `
    SELECT
      id,
      advance_amount,
      status
    FROM proforma_invoices
    WHERE sales_order_id = $1
      AND is_deleted = false
    ORDER BY id DESC
    LIMIT 1
    `,
    [sales_order_id]
  );

  const proforma = proformaRes.rows[0];

  if (!proforma) {
    throw new Error(
      "Proforma invoice not found"
    );
  }

  if (
    proforma.status?.toLowerCase() !==
    "paid"
  ) {
    throw new Error(
      "Advance payment pending"
    );
  }

  advanceAdjustedAmount = Number(
    proforma.advance_amount || 0
  );

  proformaInvoiceId = proforma.id;

  balancePayableAmount =
    totalAmount - advanceAdjustedAmount;

} else {

  balancePayableAmount = totalAmount;
}

    // =========================
    // 🔹 UPDATE TOTALS
    // =========================
    await client.query(
      `UPDATE sales_invoices SET
  amount = $1,
  total_discount = $2,
  total_tax = $3,
  total_amount = $4,
  advance_adjusted_amount = $5,
  balance_payable_amount = $6,
  proforma_invoice_id = $7
WHERE id = $8`,
      [
  subtotal,
  totalDiscount,
  totalTax,
  totalAmount,
  advanceAdjustedAmount,
  balancePayableAmount,
  proformaInvoiceId,
  invoiceId
],
    );

    await client.query("COMMIT");

    // 🔹 GET CUSTOMER EMAIL
    const custRes = await pool.query(
      `SELECT email FROM customers WHERE customer_code = $1`,
      [customer_id],
    );

    const customerEmail = custRes.rows[0]?.email;

    const itemsRes = await client.query(
      `SELECT * FROM sales_invoice_items WHERE invoice_id = $1`,
      [invoiceId],
    );

    const dbItems = itemsRes.rows;

    // fetch company
    const companyRes = await client.query(
      `SELECT org_name, address, gstin, cin, phone, email, bank_details FROM organizations WHERE id = $1`,
      [orgId],
    );
    const company = companyRes.rows[0] || {};

    const userRes = await client.query(`SELECT name FROM users WHERE id = $1`, [
      userId,
    ]);

    const currentUser = userRes.rows[0] || {};

    const soRes = await client.query(
      `
  SELECT so_number, po_reference, created_at
  FROM sales_orders
  WHERE customer_id = $1
  ORDER BY created_at DESC
  LIMIT 1
  `,
      [customer_id],
    );

    const salesOrder = soRes.rows[0] || {};

    let pdfBuffer = null;
    return res.json({
      success: true,
      message: "Invoice created & sent successfully",
      invoiceId,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CREATE INVOICE ERROR:", err);

    return res.status(500).json({
      message: err.message || "Failed to create invoice",
    });
  } finally {
    client.release();
  }
};

const getInvoices = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    // 🔹 QUERY PARAMS
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const offset = (page - 1) * limit;

    const status = req.query.status; // paid | pending
    const search = req.query.search || "";

    // 🔹 BASE WHERE
    // let where = `WHERE org_id = $1 AND is_deleted = false`;
    let where = `
WHERE si.org_id = $1 
AND si.is_deleted = false
`;
    const params = [orgId];

    if (status) {
      params.push(status);
      where += ` AND status = $${params.length}`;
    }

    if (search) {
      params.push(`%${search}%`);
      where += ` AND customer_name ILIKE $${params.length}`;
    }

    // 🔹 TOTAL COUNT
    const countRes = await pool.query(
      `SELECT COUNT(*) FROM sales_invoices si ${where}`,
      params,
    );

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

    // 🔹 FETCH DATA
    params.push(limit, offset);

    const dataRes = await pool.query(
      `
     SELECT 
  si.id,
  si.invoice_number,
  si.sales_order_number,
  si.customer_name,
  si.total_amount,
  si.status,
  si.invoice_date,
  si.due_date,

  -- ✅ FULL ITEMS DATA
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'product_name', sii.product_name,
      'qty', sii.qty,
      'rate', sii.rate,
      'amount', sii.amount,
      'discount_percent', sii.discount_percent,
      'discount_amount', sii.discount_amount,
      'gst_percent', sii.gst_percent,
      'gst_amount', sii.gst_amount
    )
  ) FILTER (WHERE sii.id IS NOT NULL) AS items,

  -- (optional) still keep this for quick UI
  STRING_AGG(sii.product_name, ', ') AS product_names

FROM sales_invoices si

LEFT JOIN sales_invoice_items sii 
  ON si.id = sii.invoice_id 
  AND (sii.is_deleted = false OR sii.is_deleted IS NULL)

${where}

GROUP BY si.id
ORDER BY si.id DESC
LIMIT $${params.length - 1}
OFFSET $${params.length}`,
      params,
    );

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

const getInvoiceStats = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    const result = await pool.query(
      `SELECT 
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE si.status = 'paid') AS paid,
    COUNT(*) FILTER (WHERE si.status = 'pending') AS pending
   FROM sales_invoices si
   WHERE si.org_id = $1 AND si.is_deleted = false`,
      [orgId],
    );

    res.json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("STATS ERROR:", err);
    res.status(500).json({ message: "Failed to fetch stats" });
  }
};

const deleteInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const { id } = req.params;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    if (!id) {
      return res.status(400).json({ message: "Invoice ID required" });
    }

    await client.query("BEGIN");

    // 🔹 Soft delete invoice
    await client.query(
      `UPDATE sales_invoices
       SET is_deleted = true,
           updated_by = $1,
           updated_at = NOW()
       WHERE id = $2 AND org_id = $3`,
      [userId, id, orgId],
    );

    // 🔹 Soft delete invoice items
    await client.query(
      `UPDATE sales_invoice_items
       SET is_deleted = true
       WHERE invoice_id = $1`,
      [id],
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "Invoice deleted successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("DELETE INVOICE ERROR:", err);
    res.status(500).json({ message: "Failed to delete invoice" });
  } finally {
    client.release();
  }
};

const updateInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const { id } = req.params;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      customer_id,
      customer_name,
      customer_address,
      customer_gst,
      invoice_date,
      due_date,
      notes,
      items,
    } = req.body;

    if (!id || !customer_id || !items?.length) {
      return res.status(400).json({
        message: "Invoice ID, customer and items required",
      });
    }

    await client.query("BEGIN");

    // 🔹 UPDATE HEADER
    await client.query(
      `UPDATE sales_invoices SET
        customer_code = $1,
        customer_name = $2,
        customer_address = $3,
        customer_gst = $4,
        invoice_date = $5,
        due_date = $6,
        notes = $7,
        updated_by = $8,
        updated_at = NOW()
      WHERE id = $9 AND org_id = $10`,
      [
        customer_id,
        customer_name,
        customer_address,
        customer_gst,
        invoice_date || new Date(),
        due_date || null,
        notes || "",
        userId,
        id,
        orgId,
      ],
    );

    // 🔹 DELETE OLD ITEMS (soft OR hard)
    await client.query(
      `UPDATE sales_invoice_items
   SET is_deleted = true,
       updated_at = NOW()
   WHERE invoice_id = $1`,
      [id],
    );

    // 🔹 RE-INSERT ITEMS
    let subtotal = 0;
    let totalDiscount = 0;
    let totalTax = 0;
    let totalAmount = 0;

    for (const item of items) {
      const qty = Number(item.qty) || 0;
      const rate = Number(item.rate) || 0;
      const gst = Number(item.gst) || 18;
      const discount = Number(item.discount) || 0;

      const amount = qty * rate;
      const discountAmount = Math.min(discount, amount);
      const taxable = Math.max(amount - discountAmount, 0);
      const gstAmount = (taxable * gst) / 100;
      const total = taxable + gstAmount;

      subtotal += amount;
      totalDiscount += discountAmount;
      totalTax += gstAmount;
      totalAmount += total;

      await client.query(
        `INSERT INTO sales_invoice_items (
          invoice_id,
          product_id,
          product_name,
          qty,
           uom_symbol,
          rate,
          amount,
          discount_amount,
          gst_percent,
          gst_amount,
          total_amount
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
        [
          id,
          item.product_id,
          item.product_name,
          qty,
          item.uom_symbol,
          rate,
          amount,
          discountAmount,
          gst,
          gstAmount,
          total,
        ],
      );
    }

    // 🔹 UPDATE TOTALS
    await client.query(
      `UPDATE sales_invoices SET
        amount = $1,
        total_discount = $2,
        total_tax = $3,
        total_amount = $4
      WHERE id = $5`,
      [subtotal, totalDiscount, totalTax, totalAmount, id],
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "Invoice updated successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("UPDATE INVOICE ERROR:", err);
    res.status(500).json({ message: "Failed to update invoice" });
  } finally {
    client.release();
  }
};

const getInvoiceById = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { id } = req.params;

    const invoice = await pool.query(
      `
  SELECT 
    si.*,

    u.first_name,
    u.last_name,

    CONCAT(
      COALESCE(u.first_name, ''),
      ' ',
      COALESCE(u.last_name, '')
    ) AS created_by_name,

      CONCAT(
        COALESCE(approver.first_name, ''),
        ' ',
        COALESCE(approver.last_name, '')
    ) AS approved_by_name


  FROM sales_invoices si

  LEFT JOIN users u
    ON u.id = si.created_by

    LEFT JOIN users approver
    ON approver.id = si.approved_by

  WHERE si.id = $1
    AND si.org_id = $2
    AND si.is_deleted = false
  `,
      [id, orgId],
    );

    const items = await pool.query(
      `SELECT * FROM sales_invoice_items 
       WHERE invoice_id = $1 AND is_deleted = false`,
      [id],
    );

    res.json({
      success: true,
      data: {
        ...invoice.rows[0],
        items: items.rows,
      },
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Failed to fetch invoice" });
  }
};

const downloadInvoicePDF = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;

    const invoiceRes = await client.query(
      `
      SELECT
        invoice_number,
        pdf_path,
        status
      FROM sales_invoices
      WHERE id = $1
      `,
      [id],
    );

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

    const invoice = invoiceRes.rows[0];

    if (!invoice.pdf_path) {
      return res.status(400).json({
        success: false,
        message: "Invoice PDF not generated yet",
      });
    }

    const absolutePath = path.join(process.cwd(), invoice.pdf_path);

    if (!fs.existsSync(absolutePath)) {
      return res.status(404).json({
        success: false,
        message: "PDF file not found on server",
      });
    }

    return res.download(absolutePath, `Invoice-${invoice.invoice_number}.pdf`);
  } catch (err) {
    console.error("DOWNLOAD PDF ERROR:", err);

    return res.status(500).json({
      success: false,
      message: "Failed to download invoice PDF",
    });
  } finally {
    client.release();
  }
};

const superApproveInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const { id } = req.params;

    const userId = req.session?.user?.id;
    const role = req.session?.user?.role?.toLowerCase();

    // ROLE CHECK

    if (role !== "admin" && role !== "superadmin" && role !== "ceo") {
      return res.status(403).json({
        success: false,
        message: "Unauthorized for super approval",
      });
    }

    // CHECK INVOICE

    const invoiceRes = await client.query(
      `
      SELECT *
      FROM sales_invoices
      WHERE id = $1
      AND is_deleted = false
      `,
      [id],
    );

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

    // UPDATE ALL APPROVALS

    // await client.query(
    //   `
    //   UPDATE invoice_approvals
    //   SET
    //     status = 'approved',
    //     approved_at = NOW(),
    //     super_approved = true,
    //     super_approved_by = $1,
    //     comments = 'Super Approved'
    //   WHERE invoice_id = $2
    //   `,
    //   [userId, id]
    // );
    const existingApproval = await client.query(
      `
  SELECT id
  FROM invoice_approvals
  WHERE invoice_id = $1
  `,
      [id],
    );

    if (existingApproval.rows.length === 0) {
      await client.query(
        `
    INSERT INTO invoice_approvals (
      invoice_id,
      user_id,
      status,
      approved_at,
      super_approved,
      super_approved_by,
      comments
    )
    VALUES ($1, $2, 'approved', NOW(), true, $2, 'Super Approved')
    `,
        [id, userId],
      );
    } else {
      await client.query(
        `
    UPDATE invoice_approvals
    SET
      status = 'approved',
      approved_at = NOW(),
      super_approved = true,
      super_approved_by = $1,
      comments = 'Super Approved'
    WHERE invoice_id = $2
    `,
        [userId, id],
      );
    }

    // UPDATE INVOICE STATUS

    await client.query(
      `
      UPDATE sales_invoices
      SET
        status = 'approved',
        updated_at = NOW(),
        updated_by = $1
      WHERE id = $2
      `,
      [userId, id],
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "Invoice super approved successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");

    console.error(err);

    res.status(500).json({
      success: false,
      message: "Failed to super approve invoice",
    });
  } finally {
    client.release();
  }
};

const getInvoiceApprovalStatus = async (req, res) => {
  try {
    const { id } = req.params;

    const orgId = req.session?.user?.orgId;

    // CHECK INVOICE EXISTS

    const invoiceCheck = await pool.query(
      `
      SELECT id
      FROM sales_invoices
      WHERE id = $1
      AND org_id = $2
      AND is_deleted = false
      `,
      [id, orgId],
    );

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

    // FETCH APPROVAL STATUS

    const approvals = await pool.query(
      `
      SELECT
        ia.id,
        ia.invoice_id,
        ia.user_id,
        ia.status,
        ia.comments,
        ia.rejection_reason,
        ia.approved_at,
        ia.rejected_at,
        ia.super_approved,
        ia.super_approved_by,

        u.first_name,
        u.last_name,
        u.role,

        CONCAT(
          COALESCE(u.first_name, ''),
          ' ',
          COALESCE(u.last_name, '')
        ) AS full_name

      FROM invoice_approvals ia

      LEFT JOIN users u
        ON u.id = ia.user_id

      WHERE ia.invoice_id = $1

      ORDER BY ia.id ASC
      `,
      [id],
    );

    res.json({
      success: true,
      data: {
        approvals: approvals.rows,
      },
    });
  } catch (err) {
    console.error(err);

    res.status(500).json({
      success: false,
      message: "Failed to fetch approval status",
    });
  }
};

// const generateDeliveryChallanPDF = require("../../services/pdf/generateDeliveryChallanPDF");
const generateEWayPDF = require("../../services/pdf/generateEWayPDF");
// const eInvoicePdf = await generateEInvoicePDF();
const generateEInvoicePDF = require("../../services/pdf/generateEInvoicePDF");
const generateGatePassPDF = require("../../services/pdf/generateGatePassPDF");
const path = require("path");
const fs = require("fs");
const mergePDFBuffers = require("../../services/pdf/mergePDFBuffers");

// const approveInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const invoiceId = req.params.id;

//     const userId = req.session?.user?.id;
//     const orgId = req.session?.user?.orgId;

//     if (!userId || !orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized",
//       });
//     }

//     await client.query("BEGIN");

//     // ==========================================
//     // VALIDATE INVOICE
//     // ==========================================

//     const invoiceRes = await client.query(
//       `
//       SELECT *
//       FROM sales_invoices
//       WHERE id = $1
//       AND org_id = $2
//       `,
//       [invoiceId, orgId],
//     );

//     if (!invoiceRes.rows.length) {
//       throw new Error("Invoice not found");
//     }

//     const invoice = invoiceRes.rows[0];

//     if (invoice.status === "approved") {
//       throw new Error("Invoice already approved");
//     }

//     // ==========================================
//     // UPDATE APPROVAL DETAILS
//     // ==========================================

//     await client.query(
//       `
//       UPDATE sales_invoices
//       SET
//         status = 'approved',
//         approved_by = $1,
//         approved_at = NOW()
//       WHERE id = $2
//       `,
//       [userId, invoiceId],
//     );

//     // approval history table
//     await client.query(
//       `
//       UPDATE invoice_approvals
//       SET
//         status = 'approved',
//         comments = 'Invoice Approved',
//         approved_at = NOW()
//       WHERE invoice_id = $1
//       `,
//       [invoiceId],
//     );

//     // ==========================================
//     // GET ITEMS
//     // ==========================================

//     const itemsRes = await client.query(
//       `
//       SELECT *
//       FROM sales_invoice_items
//       WHERE invoice_id = $1
//       `,
//       [invoiceId],
//     );

//     const dbItems = itemsRes.rows;

//     // ==========================================
//     // COMPANY
//     // ==========================================

//     const companyRes = await client.query(
//       `
//       SELECT
//         org_name,
//         address,
//         gst_number AS gstin,
//   cin_number AS cin,
//         phone,
//         email,
//         bank_details,
//          bank_name,
//         account_number,
//         ifsc_code,
//         branch
//       FROM organizations
//       WHERE id = $1
//       `,
//       [orgId],
//     );

//     const company = companyRes.rows[0] || {};

//       const creatorRes = await client.query(
//       `
//       SELECT u.name as created_by_name
//       FROM sales_invoices si
//       LEFT JOIN users u ON si.created_by = u.id
//       WHERE si.id = $1
//       `,
//       [invoiceId],
//     );

//     const creatorName = creatorRes.rows[0]?.created_by_name || "-";

//       const approverRes = await client.query(
//       `
//       SELECT name
//       FROM users
//       WHERE id = $1
//       `,
//       [userId],
//     );

//     const approverName = approverRes.rows[0]?.name || "-";


//         // ==========================================
//     // GET TRANSPORT DETAILS (from sales_order or shipment)
//     // ==========================================
    
//     let transportDetails = {
//       transporter_name: "-",
//       vehicle_number: "-",
//       from_location: "-",
//       to_location: "-"
//     };

//     let vehicleData = null;


//      if (invoice.sales_order_id) {
//       const vehicleRes = await client.query(
//         `
//          SELECT 
//       cv.id as vehicle_id,
//       cv.vehicle_number,
//       cv.vehicle_type,
//       cv.driver_name,
//       cv.driver_number,
//       cv.transporter_name as agency_name,
//       cv.sales_order_number,
//       cv.gate_status,
//       sd.shipment_number,
//       ta.agency_name as transporter_name,
//       so.so_number
     
//     FROM sales_orders so
//     LEFT JOIN shipment_dispatches sd ON sd.sales_order_id = so.id
//     LEFT JOIN commercial_vehicles cv ON cv.id = sd.vehicle_id
//     LEFT JOIN transport_agencies ta ON ta.id = sd.transport_agency_id
//     WHERE so.id = $1
//         `,
//         [invoice.sales_order_id]
//       );
      
//       if (vehicleRes.rows[0]) {
//         vehicleData = vehicleRes.rows[0];
//         transportDetails = {
//           transporter_name: vehicleData.transporter_name || "-",
//           vehicle_number: vehicleData.vehicle_number || "-",
//           from_location: vehicleData.shipping_address || "-",
//           to_location: vehicleData.delivery_address || "-"
//         };
//       }
//     }

//     // ==========================================
//     // CREATE GATE PASS (if vehicle exists)
//     // ==========================================
    
//     let gatePassData = null; 
//     // let gatePassPdf = null;

//     if (vehicleData && vehicleData.vehicle_id) {
      
//       // Generate Gate Pass Number
//       const year = new Date().getFullYear();
//       const prefix = `GP-${year}-`;

//       const lastGatePass = await client.query(
//         `
//         SELECT pass_number 
//         FROM gate_passes 
//         WHERE pass_number LIKE $1 
//         ORDER BY id DESC 
//         LIMIT 1
//         `,
//         [`${prefix}%`]
//       );

//         let nextNumber = 1;
//       if (lastGatePass.rows.length > 0) {
//         const lastNum = parseInt(lastGatePass.rows[0].pass_number.replace(prefix, ''), 10);
//         if (!isNaN(lastNum)) nextNumber = lastNum + 1;
//       }

//       const gatePassNo = `${prefix}${String(nextNumber).padStart(4, '0')}`;
//   // Insert Gate Pass
//       const insertResult = await client.query(
//         `
//         INSERT INTO gate_passes (
//           commercial_vehicle_id,
//           pass_number,
//           pass_type,
//           pass_status,
//           generated_at,
//           generated_by,
//           org_id,
//           created_at,
//           updated_at
//         )
//         VALUES ($1, $2, 'ENTRY', 'ACTIVE', NOW(), $3, $4, NOW(), NOW())
//         RETURNING id, pass_number, generated_at
//         `,
//         [vehicleData.vehicle_id, gatePassNo, userId, orgId]
//       );

//       const insertedGatePass = insertResult.rows[0];

//       // Fetch complete gate pass data for PDF
//      // Replace your existing gate pass fetching query with this:

// const gatePassWithDetails = await client.query(
//   `
//   SELECT 
//     gp.id,
//     gp.pass_number,
//     gp.pass_type,
//     gp.pass_status,
//     gp.generated_at,
//     gp.exited_at,
    
//     cv.vehicle_number,
//     cv.vehicle_type,
//     cv.driver_name,
//     cv.driver_number,
//     cv.ticket_no,
//     cv.gate_status,
//     cv.sales_order_number,  
//     cv.customer_name,
    
//     sd.shipment_number,
//     ta.agency_name as transporter_name,
    
//     u.name AS created_by_name
    
//   FROM gate_passes gp
  
//   LEFT JOIN commercial_vehicles cv 
//     ON cv.id = gp.commercial_vehicle_id
    
//   LEFT JOIN shipment_dispatches sd 
//     ON sd.vehicle_id = cv.id
    
//   LEFT JOIN transport_agencies ta 
//     ON ta.id = sd.transport_agency_id
    
//   LEFT JOIN users u 
//     ON u.id = gp.generated_by
    
//   WHERE gp.id = $1
//   `,
//   [insertedGatePass.id]
// );

//       gatePassData = gatePassWithDetails.rows[0];

//       // Generate Gate Pass PDF
//       // if (gatePassData) {
//       //   try {
//       //     gatePassPdf = await generateGatePassPDF(gatePassData);
          
//       //     // Save PDF to disk
//       //     // const uploadDir = path.join(process.cwd(), "uploads", "gatepasses");
//       //     // if (!fs.existsSync(uploadDir)) {
//       //     //   fs.mkdirSync(uploadDir, { recursive: true });
//       //     // }
          
//       //     // const fileName = `GatePass-${gatePassData.pass_number.replace(/\//g, '-')}.pdf`;
//       //     // const filePath = path.join(uploadDir, fileName);
//       //     // const relativePath = `uploads/gatepasses/${fileName}`;
          
//       //     // fs.writeFileSync(filePath, gatePassPdf);
          
//       //     // Update PDF path in database
//       //     // await client.query(
//       //     //   `
//       //     //   UPDATE gate_passes
//       //     //   SET pdf_path = $1
//       //     //   WHERE id = $2
//       //     //   `,
//       //     //   [relativePath, insertedGatePass.id]
//       //     // );
          
//       //     // console.log(`Gate pass PDF generated: ${relativePath}`);
//       //   } catch (pdfErr) {
//       //     console.error("Gate pass PDF generation failed:", pdfErr.message);
//       //     // Don't throw error, continue with invoice approval
//       //   }
//       // }
//     }

//     const soRes = await client.query(
//       `
//       SELECT
//         so_number,
//         po_reference,
//         created_at
//       FROM sales_orders
//       WHERE id = $1
//       `,
//       [invoice.sales_order_id],
//     );

//     const salesOrder = soRes.rows[0] || {};

//     if (gatePassData) {
//   gatePassData = {
//     ...gatePassData,
//     // Add sales order number if not present
//     sales_order_number: gatePassData.sales_order_number || salesOrder.so_number,
//     // Add customer details from invoice
//     customer_name: gatePassData.customer_name || invoice.customer_name,
//     // Add transport details
//     transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
//     // Add any other fields your gate pass PDF needs
//     from_location: transportDetails.from_location,
//     to_location: transportDetails.to_location,
//   };
// }



//     // if (invoice.sales_order_id) {
//     //   const transportRes = await client.query(
//     //     `
//     //     SELECT 
//     //       ta.agency_name as transporter_name,
//     //       tv.vehicle_number,
//     //       so.shipping_address as from_location,
//     //       so.delivery_address as to_location
//     //     FROM sales_orders so
//     //     LEFT JOIN transport_agencies ta ON so.transport_agency_id = ta.id
//     //     LEFT JOIN transport_vehicles tv ON so.vehicle_id = tv.id
//     //     WHERE so.id = $1
//     //     `,
//     //     [invoice.sales_order_id]
//     //   );
      
//     //   if (transportRes.rows[0]) {
//     //     transportDetails = transportRes.rows[0];
//     //   }
//     // }

//     // ==========================================
//     // APPROVER
//     // ==========================================

//     const userRes = await client.query(
//       `
//     SELECT
//   si.*,
//   u.name AS created_by_name
// FROM sales_invoices si
// LEFT JOIN users u
// ON si.created_by = u.id
// WHERE si.id = $1
//       `,
//       [invoiceId],
//     );

//     const currentUser = userRes.rows[0] || {};

//     // Approver
// //     const approverRes = await client.query(
// //       `
// // SELECT name
// // FROM users
// // WHERE id = $1
// // `,
// //       [userId],
// //     );

// //     const approver = approverRes.rows[0] || {};

//     // Creator
//     const invoiceUserRes = await client.query(
//       `
// SELECT
//   si.*,
//   u.name AS created_by_name
// FROM sales_invoices si
// LEFT JOIN users u
// ON si.created_by = u.id
// WHERE si.id = $1
// `,
//       [invoiceId],
//     );

//     const invoiceData = invoiceUserRes.rows[0] || {};

//     // ==========================================
//     // CUSTOMER EMAIL
//     // ==========================================

//     const customerRes = await client.query(
//       `
//       SELECT email
//       FROM customers
//       WHERE customer_code = $1
//       `,
//       [invoice.customer_code],
//     );

//     const customerEmail = customerRes.rows[0]?.email;

//     // ==========================================
//     // SALES ORDER
//     // ==========================================

    

//     // After getting invoice data, fetch the associated gate pass

//     // ==========================================
//     // PDF GENERATION
//     // ==========================================
//     let gatePassPdf = null;

//     const eWayPdf = await generateEWayPDF();

//     const eInvoicePdf = await generateEInvoicePDF();

//   //  const gatePassPdf = await generateGatePassPDF(gatePassData);
//  if (gatePassData) {
//   try {
//     gatePassPdf = await generateGatePassPDF(gatePassData);
//     console.log(`✅ Gate pass PDF generated: ${gatePassData.pass_number}`);
//   } catch (err) {
//     console.error("❌ Gate pass PDF generation failed:", err.message);
//     // Don't throw, just continue without it
//   }
// } else {
//   console.log("⚠️ No gate pass data available, skipping gate pass PDF");
// }
//     // const pdfBuffer = await generateInvoicePDF(
//     //   {
//     //     invoice_number: invoice.invoice_number,
//     //     invoice_date: invoice.invoice_date,
//     //     due_date: invoice.due_date,

//     //     customer_name: invoice.customer_name,
//     //     customer_address: invoice.customer_address,
//     //     customer_gst: invoice.customer_gst,

//     //     order_no: salesOrder.so_number || "",
//     //     po_no: salesOrder.po_reference || "",
//     //     po_date: salesOrder.created_at || "",

//     //     gross: invoice.amount,
//     //     discount: invoice.total_discount,
//     //     net_value:
//     //       Number(invoice.amount) -
//     //       Number(invoice.total_discount),

//     //     freight: 0,

//     //     cgst: Number(invoice.total_tax) / 2,
//     //     sgst: Number(invoice.total_tax) / 2,

//     //     round_off: 0,
//     //     total: invoice.total_amount,

//     //     prepared_by: currentUser.name,
//     //     verified_by: currentUser.name,
//     //     approved_by: currentUser.name,

//     //     authorized_signatory:
//     //       company.org_name || "-",
//     //   },
//     //   dbItems,
//     //   company
//     // );

//     // =========================================
//     // MERGE PDFs
//     // =========================================
//     const pdfBuffer = await generateInvoicePDF(
//       {
//         invoice_number: invoice.invoice_number,
//         invoice_date: invoice.invoice_date,
//         due_date: invoice.due_date,
//         advance_adjusted_amount:
//   Number(invoice.advance_adjusted_amount || 0),

// balance_payable_amount:
//   Number(invoice.balance_payable_amount || 0),

// proforma_invoice_id:
//   invoice.proforma_invoice_id || null,

//         customer_name: invoice.customer_name,
//         customer_address: invoice.customer_address,
//         customer_gst: invoice.customer_gst,

//         order_no: salesOrder.so_number || "",
//         po_no: salesOrder.po_reference || "",
//         po_date: salesOrder.created_at || "",

//         gross: Number(invoice.amount || 0),
//         discount: Number(invoice.total_discount || 0),

//         net_value:
//           Number(invoice.amount || 0) - Number(invoice.total_discount || 0),

//         freight: Number(invoice.freight || 0),

//         cgst: Number(invoice.total_tax || 0) / 2,
//         sgst: Number(invoice.total_tax || 0) / 2,

//         round_off: Number(invoice.round_off || 0),

//         total: Number(invoice.total_amount || 0),

//         // prepared_by: invoice.created_by_name || "-",
//         // verified_by: invoice.created_by_name || "-",
//         // approved_by: currentUser.name || "-",
        
//         prepared_by: creatorName,  // ← FIXED
//         verified_by: creatorName,  // ← FIXED
//         approved_by: approverName, // ← FIXED

//           // Transport Details
//         transport_details: transportDetails,

//         authorized_signatory: company.org_name || "-",
//       },
//       dbItems,
//       company,
//     );

//     // const pdfBuffers = [
//     //   // dispatchInvoicePdf,
//     //   // dispatchWeighmentPdf,
//     //   // deliveryChallanPdf,
//     //   pdfBuffer,
//     //   eWayPdf,
//     //   eInvoicePdf,
//     //   gatePassPdf
//     // ];
// const pdfBuffers = [
//   pdfBuffer,
//   eWayPdf,
//   eInvoicePdf,
//   gatePassPdf
// ].filter(pdf => pdf !== null && pdf !== undefined);

// console.log(`📄 Total PDFs to merge: ${pdfBuffers.length}`);

// if (pdfBuffers.length === 0) {
//   throw new Error("No PDFs were generated successfully. Cannot proceed.");
// }

// const finalMergedPdf = await mergePDFBuffers(pdfBuffers);
//     // const finalMergedPdf = await mergePDFBuffers(pdfBuffers);

//     // ==========================================
//     // SAVE PDF TO DISK
//     // ==========================================

//     // const uploadDir = path.join(
//     //   __dirname,
//     //   "../uploads/invoices"
//     // );
//     const uploadDir = path.join(process.cwd(), "uploads", "invoices");

//     if (!fs.existsSync(uploadDir)) {
//       fs.mkdirSync(uploadDir, { recursive: true });
//     }

//     const fileName = `Invoice-${invoice.invoice_number}.pdf`;

//     const filePath = path.join(uploadDir, fileName);

//     fs.writeFileSync(filePath, finalMergedPdf);

//     // ==========================================
//     // SAVE PATH IN DB
//     // ==========================================

//     await client.query(
//       `
//   UPDATE sales_invoices
//   SET pdf_path = $1
//   WHERE id = $2
//   `,
//       [`uploads/invoices/${fileName}`, invoiceId],
//     );
//     // ==========================================
//     // EMAIL
//     // ==========================================
//     // const nodemailer = require("nodemailer");

//     //     const transporter = nodemailer.createTransport({
//     //       service: "gmail",
//     //       auth: {
//     //         user: process.env.MAIL_USER,
//     //         pass: process.env.MAIL_PASS,
//     //       },
//     //     });



//     // const transporter = nodemailer.createTransport({
//     //   service: "gmail",
//     //   auth: {
//     //     user: process.env.MAIL_USER,
//     //     pass: process.env.MAIL_PASS,
//     //   },
//     // });
//      const transporter = transporter(); 
//     // const emailHtml = `
//     //   <h3>Invoice Approved</h3>

//     //   <p>Dear ${invoice.customer_name},</p>

//     //   <p>
//     //     Your invoice
//     //     <strong>${invoice.invoice_number}</strong>
//     //     has been approved and is attached.
//     //   </p>

//     //   <p>
//     //     Total Amount:
//     //     ₹${Number(invoice.total_amount).toFixed(2)}
//     //   </p>

//     //   <p>
//     //     Regards,<br/>
//     //     Accounts Team
//     //   </p>
//     // `;

//     const emailHtml = `
// <div style="font-family: 'Segoe UI', Arial, sans-serif; background:#f4f6f8; padding:30px 0;">

//   <table width="100%" cellpadding="0" cellspacing="0"
//          style="max-width:680px;margin:auto;background:#ffffff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb;box-shadow:0 8px 20px rgba(0,0,0,0.04);">

//     <!-- HEADER -->
//     <tr>
//       <td style="background:linear-gradient(135deg,#2563eb,#1e3a8a);padding:22px 28px;color:#fff;">
//         <div style="font-size:18px;font-weight:600;">
//           Tax Invoice Generated
//         </div>
//         <div style="font-size:12px;opacity:0.85;margin-top:4px;">
//           ERP Enterprise
//         </div>
//       </td>
//     </tr>

//     <!-- BODY -->
//     <tr>
//       <td style="padding:28px;color:#374151;font-size:14px;line-height:1.6;">

//         <p style="margin:0 0 12px;">
//           Dear <strong>${invoice.customer_name}</strong>,
//         </p>

//         <p style="margin:0 0 16px;">
//           Your invoice <strong style="color:#111827;">${invoice.invoice_number}</strong> has been successfully generated.
//         </p>

//         <!-- INFO CARD -->
//         <div style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;padding:16px;margin:18px 0;">
//           <table width="100%" style="font-size:13px;">
//             <tr>
//               <td style="color:#6b7280;">Invoice Number</td>
//               <td style="text-align:right;font-weight:600;">${invoice.invoice_number}</td>
//             </tr>
//             <tr>
//               <td style="color:#6b7280;padding-top:8px;">Total Amount</td>
//               <td style="text-align:right;font-weight:600;color:#059669;padding-top:8px;">
//                 ₹${invoice.total_amount}
//               </td>
//             </tr>
//             <tr>
//               <td style="color:#6b7280;padding-top:8px;">Due Date</td>
//              <td style="text-align:right;padding-top:8px;">
//   ${formatDate(invoice.due_date)}
// </td>
//             </tr>
//           </table>
//         </div>

//         <!-- ATTACHMENT NOTE -->
//         <div style="background:#eef2ff;border:1px solid #c7d2fe;border-radius:8px;padding:12px 14px;font-size:13px;">
//           📎 The invoice PDF is attached with this email for your reference.
//         </div>

//         <!-- MESSAGE -->
//         <p style="margin-top:20px;">
//           Kindly review the invoice and ensure payment is completed on or before the due date.
//         </p>

//         <!-- CTA BUTTON -->
//         <div style="margin:25px 0;">
//           <a href="#"
//              style="background:#2563eb;color:#fff;padding:10px 18px;border-radius:6px;text-decoration:none;font-size:13px;display:inline-block;">
//              Contact Support
//           </a>
//         </div>

//         <!-- SIGNATURE -->
//         <p style="margin-top:20px;">
//           Regards,<br/>
//           <strong>Accounts Team</strong><br/>
//           <span style="font-size:12px;color:#6b7280;">
//             ERP Enterprise
//           </span>
//         </p>

//       </td>
//     </tr>

//     <!-- FOOTER -->
//     <tr>
//       <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#9ca3af;">
//         This is an automated invoice notification. Please do not reply.
//       </td>
//     </tr>

//   </table>
// </div>
// `;

//     if (customerEmail) {
//       await transporter.sendMail({
//         from: `"Accounts Team" <${process.env.MAIL_USER}>`,
//         to: customerEmail,
//         subject: `Approved Invoice ${invoice.invoice_number}`,
//         html: emailHtml,
//         attachments: [
//           {
//             filename: `Invoice-${invoice.invoice_number}.pdf`,
//             content: finalMergedPdf,
//           },
//         ],
//       });
//     }

//     await client.query("COMMIT");

//     return res.status(200).json({
//       success: true,
//       message: "Invoice approved and emailed successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

//     console.error("APPROVE INVOICE ERROR:", err);

//     return res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };



// ==============================================
// CREATE MAIN INVOICE (with approval flow & email)
// ==============================================
// const createMainInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;

//     if (!orgId || !userId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       sales_order_id,
//       sales_order_number,
//       customer_id,
//       customer_name,
//       customer_address,
//       customer_gst,
//       invoice_date,
//       due_date,
//       notes,
//       items,
//       weighment,
//       advance_adjusted_amount,
//       balance_payable_amount,
//     } = req.body;

//     if (!customer_id || !items?.length) {
//       return res.status(400).json({
//         message: "Customer and items required",
//       });
//     }

//     await client.query("BEGIN");

//     // GENERATE INVOICE NUMBER
//     const invRes = await client.query(
//       `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
//       [orgId]
//     );

//     const count = Number(invRes.rows[0].count) + 1;
//     const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;

//     let subtotal = 0;
//     let totalDiscount = 0;
//     let totalTax = 0;
//     let totalAmount = 0;

//     // INSERT HEADER
//     const invoiceInsert = await client.query(
//       `INSERT INTO sales_invoices (
//         sales_order_id, sales_order_number, invoice_number, org_id,
//         customer_code, customer_name, customer_address, customer_gst,
//         invoice_date, due_date, amount, total_discount, total_tax,
//         total_amount, advance_adjusted_amount, balance_payable_amount,
//         weighment_data, notes, created_by, status
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,'pending')
//       RETURNING id`,
//       [
//         sales_order_id, sales_order_number, invoiceNumber, orgId,
//         customer_id, customer_name, customer_address, customer_gst,
//         invoice_date || new Date(), due_date || null,
//         0, 0, 0, 0,
//         advance_adjusted_amount || 0, balance_payable_amount || totalAmount,
//         JSON.stringify(weighment || {}), notes || "", userId
//       ]
//     );

//     const invoiceId = invoiceInsert.rows[0].id;

//     // INSERT ITEMS
//     for (const item of items) {
//       const qty = Number(item.qty) || 0;
//       const rate = Number(item.rate) || 0;
//       const gst = Number(item.gst) || 18;
//       const discount = Number(item.discount) || 0;

//       const amount = qty * rate;
//       const discountAmount = Math.min(discount, amount);
//       const taxable = Math.max(amount - discountAmount, 0);
//       const gstAmount = (taxable * gst) / 100;
//       const total = taxable + gstAmount;

//       subtotal += amount;
//       totalDiscount += discountAmount;
//       totalTax += gstAmount;
//       totalAmount += total;

//       await client.query(
//         `INSERT INTO sales_invoice_items (
//           invoice_id, product_id, product_name, qty, uom_symbol,
//           rate, amount, discount_amount, gst_percent, gst_amount, total_amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
//         [invoiceId, item.product_id, item.product_name, qty, item.uom_symbol,
//          rate, amount, discountAmount, gst, gstAmount, total]
//       );
//     }

//     // UPDATE TOTALS
//     await client.query(
//       `UPDATE sales_invoices SET
//         amount = $1, total_discount = $2, total_tax = $3, total_amount = $4
//       WHERE id = $5`,
//       [subtotal, totalDiscount, totalTax, totalAmount, invoiceId]
//     );

//     // CREATE APPROVAL RECORD
//     await client.query(
//       `INSERT INTO invoice_approvals (invoice_id, user_id, status, comments)
//        VALUES ($1, $2, 'pending', 'Awaiting Approval')`,
//       [invoiceId, userId]
//     );

//     await client.query("COMMIT");

//     // SEND EMAIL with PDF (async - don't await)
//     sendInvoiceEmail(invoiceId, client, orgId, userId).catch(err => {
//       console.error("Email send error:", err);
//     });

//     return res.json({
//       success: true,
//       message: "Main Invoice created and sent for approval",
//       invoiceId,
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE MAIN INVOICE ERROR:", err);
//     return res.status(500).json({ message: err.message || "Failed to create invoice" });
//   } finally {
//     client.release();
//   }
// };

// ==============================================
// CREATE PROFORMA INVOICE
// ==============================================
// const createProformaInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;

//     if (!orgId || !userId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       sales_order_id,
//       sales_order_number,
//       customer_id,
//       customer_name,
//       customer_address,
//       customer_gst,
//       invoice_date,
//       due_date,
//       notes,
//       items,
//       weighment,
//     } = req.body;

//     if (!customer_id || !items?.length) {
//       return res.status(400).json({ message: "Customer and items required" });
//     }

//     await client.query("BEGIN");

//     // GENERATE PROFORMA NUMBER
//     const invRes = await client.query(
//       `SELECT COUNT(*) FROM proforma_invoices WHERE org_id = $1`,
//       [orgId]
//     );

//     const count = Number(invRes.rows[0].count) + 1;
//     const proformaNumber = `PI-${String(count).padStart(3, "0")}`;

//     let subtotal = 0;
//     let totalTax = 0;
//     let totalAmount = 0;

//     // GET ADVANCE AMOUNT from SO
//     const soRes = await client.query(
//       `SELECT advance_payment_amount, with_advance_payment FROM sales_orders WHERE id = $1`,
//       [sales_order_id]
//     );
//     const advanceAmount = soRes.rows[0]?.advance_payment_amount || 0;

//     // INSERT PROFORMA
//     const proformaInsert = await client.query(
//       `INSERT INTO proforma_invoices (
//         proforma_number, sales_order_id, sales_order_number, org_id,
//         customer_code, customer_name, customer_address, customer_gst,
//         invoice_date, due_date, advance_amount, notes,
//         weighment_data, created_by, status
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'pending')
//       RETURNING id`,
//       [
//         proformaNumber, sales_order_id, sales_order_number, orgId,
//         customer_id, customer_name, customer_address, customer_gst,
//         invoice_date || new Date(), due_date || null,
//         advanceAmount, notes || "",
//         JSON.stringify(weighment || {}), userId
//       ]
//     );

//     const proformaId = proformaInsert.rows[0].id;

//     // INSERT ITEMS
//     for (const item of items) {
//       const qty = Number(item.qty) || 0;
//       const rate = Number(item.rate) || 0;
//       const gst = Number(item.gst) || 18;
//       const discount = Number(item.discount) || 0;

//       const amount = qty * rate;
//       const discountAmount = Math.min(discount, amount);
//       const taxable = Math.max(amount - discountAmount, 0);
//       const gstAmount = (taxable * gst) / 100;
//       const total = taxable + gstAmount;

//       subtotal += amount;
//       totalTax += gstAmount;
//       totalAmount += total;

//       await client.query(
//         `INSERT INTO proforma_invoice_items (
//           proforma_id, product_id, product_name, qty, uom_symbol,
//           rate, amount, discount_amount, gst_percent, gst_amount, total_amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
//         [proformaId, item.product_id, item.product_name, qty, item.uom_symbol,
//          rate, amount, discountAmount, gst, gstAmount, total]
//       );
//     }

//     // UPDATE TOTALS
//     await client.query(
//       `UPDATE proforma_invoices SET
//         amount = $1, total_tax = $2, total_amount = $3
//       WHERE id = $4`,
//       [subtotal, totalTax, totalAmount, proformaId]
//     );

//     await client.query("COMMIT");

//     // SEND PROFORMA EMAIL
//     sendProformaEmail(proformaId, client, orgId, userId).catch(err => {
//       console.error("Proforma email error:", err);
//     });

//     return res.json({
//       success: true,
//       message: "Proforma Invoice created and sent for approval",
//       proformaId,
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE PROFORMA INVOICE ERROR:", err);
//     return res.status(500).json({ message: err.message || "Failed to create proforma" });
//   } finally {
//     client.release();
//   }
// };
// ==============================================
// CREATE MAIN INVOICE (with approval flow & email)
// ==============================================
// const createMainInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;

//     if (!orgId || !userId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       sales_order_id,
//       sales_order_number,
//       customer_code,        // Changed from customer_id
//       customer_name,
//       customer_address,
//       customer_gst,
//       customer_email,       // New field
//       customer_phone,       // New field
//       customer_city,        // New field
//       customer_state,       // New field
//       customer_pincode,     // New field
//       contact_person,       // New field
//       shipping_address,     // New field
//       invoice_date,
//       due_date,
//       notes,
//       items,
//       weighment,
//       advance_adjusted_amount,
//       balance_payable_amount,
//     } = req.body;

//     // Check for required fields
//     if (!customer_code || !items?.length) {
//       console.log("Missing required fields:", { customer_code, itemsLength: items?.length });
//       return res.status(400).json({
//         message: "Customer code and items are required",
//         received: { customer_code, itemsCount: items?.length }
//       });
//     }

//     await client.query("BEGIN");

//     // GENERATE INVOICE NUMBER
//     const invRes = await client.query(
//       `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
//       [orgId]
//     );

//     const count = Number(invRes.rows[0].count) + 1;
//     const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;

//     let subtotal = 0;
//     let totalDiscount = 0;
//     let totalTax = 0;
//     let totalAmount = 0;

//     // INSERT HEADER with all customer fields
//     const invoiceInsert = await client.query(
//       `INSERT INTO sales_invoices (
//         sales_order_id, 
//         sales_order_number, 
//         invoice_number, 
//         org_id,
//         customer_code, 
//         customer_name, 
//         customer_address, 
//         customer_gst,
//         customer_email,
//         customer_phone,
//         customer_city,
//         customer_state,
//         customer_pincode,
//         contact_person,
//         shipping_address,
//         invoice_date, 
//         due_date, 
//         amount, 
//         total_discount, 
//         total_tax,
//         total_amount, 
//         advance_adjusted_amount, 
//         balance_payable_amount,
//         weighment_data, 
//         notes, 
//         created_by, 
//         status
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,'pending')
//       RETURNING id`,
//       [
//         sales_order_id, 
//         sales_order_number, 
//         invoiceNumber, 
//         orgId,
//         customer_code, 
//         customer_name, 
//         customer_address, 
//         customer_gst,
//         customer_email || null,
//         customer_phone || null,
//         customer_city || null,
//         customer_state || null,
//         customer_pincode || null,
//         contact_person || null,
//         shipping_address || null,
//         invoice_date || new Date(), 
//         due_date || null,
//         0,  // amount
//         0,  // total_discount
//         0,  // total_tax
//         0,  // total_amount
//         advance_adjusted_amount || 0, 
//         balance_payable_amount || 0,
//         JSON.stringify(weighment || {}), 
//         notes || "", 
//         userId
//       ]
//     );

//     const invoiceId = invoiceInsert.rows[0].id;

//     // INSERT ITEMS
//     for (const item of items) {
//       const qty = Number(item.qty) || 0;
//       const rate = Number(item.rate) || 0;
//       const gst = Number(item.gst) || 18;
//       const discount = Number(item.discount) || 0;

//       const amount = qty * rate;
//       const discountAmount = Math.min(discount, amount);
//       const taxable = Math.max(amount - discountAmount, 0);
//       const gstAmount = (taxable * gst) / 100;
//       const total = taxable + gstAmount;

//       subtotal += amount;
//       totalDiscount += discountAmount;
//       totalTax += gstAmount;
//       totalAmount += total;

//       await client.query(
//         `INSERT INTO sales_invoice_items (
//           invoice_id, 
//           product_id, 
//           product_name, 
//           qty, 
//           uom_symbol,
//           rate, 
//           amount, 
//           discount_amount, 
//           gst_percent, 
//           gst_amount, 
//           total_amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
//         [
//           invoiceId, 
//           item.product_id, 
//           item.product_name, 
//           qty, 
//           item.uom_symbol || "TONS",
//           rate, 
//           amount, 
//           discountAmount, 
//           gst, 
//           gstAmount, 
//           total
//         ]
//       );
//     }

//     // UPDATE TOTALS
//     await client.query(
//       `UPDATE sales_invoices SET
//         amount = $1, 
//         total_discount = $2, 
//         total_tax = $3, 
//         total_amount = $4
//       WHERE id = $5`,
//       [subtotal, totalDiscount, totalTax, totalAmount, invoiceId]
//     );

//     // CREATE APPROVAL RECORD
//     await client.query(
//       `INSERT INTO invoice_approvals (invoice_id, user_id, status, comments)
//        VALUES ($1, $2, 'pending', 'Awaiting Approval')`,
//       [invoiceId, userId]
//     );

//     await client.query("COMMIT");

//     // SEND EMAIL with PDF (async - don't await)
//     sendInvoiceEmail(invoiceId, client, orgId, userId, customer_email, customer_name).catch(err => {
//       console.error("Email send error:", err);
//     });

//     return res.json({
//       success: true,
//       message: "Main Invoice created and sent for approval",
//       invoiceId,
//       invoiceNumber,
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE MAIN INVOICE ERROR:", err);
//     return res.status(500).json({ 
//       message: err.message || "Failed to create invoice",
//       error: err.toString()
//     });
//   } finally {
//     client.release();
//   }
// };
// Function to check remaining quantity and create invoice


// const createMainInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;

//     const {
//       sales_order_id,
//       sales_order_number,
//       customer_code,
//       customer_name,
//       customer_address,
//       customer_gst,
//       invoice_date,
//       due_date,
//       notes,
//       items,
//       weighment,
//       advance_adjusted_amount,
//       balance_payable_amount,
//     } = req.body;

//     await client.query("BEGIN");

//     // 🔹 CHECK IF SO EXISTS AND GET CURRENT INVOICED QUANTITY
//     const soCheck = await client.query(
//       `SELECT 
//         so.*,
//         COALESCE(SUM(si.total_quantity_invoiced), 0) as total_invoiced_qty,
//         COALESCE(SUM(si.total_amount), 0) as total_invoiced_amount
//        FROM sales_orders so
//        LEFT JOIN sales_invoices si ON si.sales_order_id = so.id 
//          AND si.is_deleted = false
//        WHERE so.id = $1 AND so.org_id = $2
//        GROUP BY so.id`,
//       [sales_order_id, orgId]
//     );

//     if (!soCheck.rows.length) {
//       throw new Error("Sales Order not found");
//     }

//     const salesOrder = soCheck.rows[0];
//     const originalTotalQty = Number(salesOrder.total_quantity) || 0;
//     const invoicedSoFar = Number(salesOrder.total_invoiced_qty) || 0;
//     const currentInvoiceQty = items.reduce((sum, item) => sum + Number(item.qty), 0);
//     const remainingAfterInvoice = originalTotalQty - (invoicedSoFar + currentInvoiceQty);

//     // 🔹 VALIDATE QUANTITY LIMITS
//     if (invoicedSoFar + currentInvoiceQty > originalTotalQty) {
//       throw new Error(`Cannot invoice more than order quantity. 
//         Order total: ${originalTotalQty} TONS, 
//         Already invoiced: ${invoicedSoFar} TONS,
//         Current invoice: ${currentInvoiceQty} TONS`);
//     }

//     // 🔹 GENERATE INVOICE NUMBER
//     const invRes = await client.query(
//       `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
//       [orgId]
//     );
//     const count = Number(invRes.rows[0].count) + 1;
//     const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;

//     // 🔹 CALCULATE TOTALS
//     let subtotal = 0;
//     let totalDiscount = 0;
//     let totalTax = 0;
//     let totalAmount = 0;

//     // 🔹 INSERT INVOICE HEADER with quantity tracking
//     const invoiceInsert = await client.query(
//       `INSERT INTO sales_invoices (
//         sales_order_id, sales_order_number, invoice_number, org_id,
//         customer_code, customer_name, customer_address, customer_gst,
//         customer_email, customer_phone, customer_city, customer_state,
//         invoice_date, due_date, amount, total_discount, total_tax,
//         total_amount, advance_adjusted_amount, balance_payable_amount,
//         total_quantity_invoiced, remaining_quantity, is_partial_invoice,
//         weighment_data, notes, created_by, status
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,'pending')
//       RETURNING id`,
//       [
//         sales_order_id, sales_order_number, invoiceNumber, orgId,
//         customer_code, customer_name, customer_address, customer_gst,
//         null, null, null, null,
//         invoice_date || new Date(), due_date || null,
//         0, 0, 0, 0,
//         advance_adjusted_amount || 0, balance_payable_amount || 0,
//         currentInvoiceQty, remainingAfterInvoice,
//         remainingAfterInvoice > 0 ? true : false,  // is_partial_invoice
//         JSON.stringify(weighment || {}), notes || "", userId
//       ]
//     );

//     const invoiceId = invoiceInsert.rows[0].id;

//     // 🔹 INSERT INVOICE ITEMS
//     for (const item of items) {
//       const qty = Number(item.qty) || 0;
//       const rate = Number(item.rate) || 0;
//       const gst = Number(item.gst) || 18;
//       const discount = Number(item.discount) || 0;

//       const amount = qty * rate;
//       const discountAmount = Math.min(discount, amount);
//       const taxable = Math.max(amount - discountAmount, 0);
//       const gstAmount = (taxable * gst) / 100;
//       const total = taxable + gstAmount;

//       subtotal += amount;
//       totalDiscount += discountAmount;
//       totalTax += gstAmount;
//       totalAmount += total;

//       await client.query(
//         `INSERT INTO sales_invoice_items (
//           invoice_id, product_id, product_name, qty, uom_symbol,
//           rate, amount, discount_amount, gst_percent, gst_amount, total_amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
//         [invoiceId, item.product_id, item.product_name, qty, "TONS",
//          rate, amount, discountAmount, gst, gstAmount, total]
//       );
//     }

//     // 🔹 UPDATE INVOICE TOTALS
//     await client.query(
//       `UPDATE sales_invoices SET
//         amount = $1, total_discount = $2, total_tax = $3, total_amount = $4
//       WHERE id = $5`,
//       [subtotal, totalDiscount, totalTax, totalAmount, invoiceId]
//     );

//     // 🔹 UPDATE SALES ORDER with invoiced quantity
//     const newTotalInvoiced = invoicedSoFar + currentInvoiceQty;
//     const isFullyInvoiced = remainingAfterInvoice === 0;
    
//     await client.query(
//       `UPDATE sales_orders 
//        SET 
//          total_invoiced_qty = $1,
//          total_invoiced_amount = total_invoiced_amount + $2,
//          remaining_qty = $3,
//          fully_invoiced = $4,
//          updated_at = NOW()
//        WHERE id = $5`,
//       [newTotalInvoiced, totalAmount, remainingAfterInvoice, isFullyInvoiced, sales_order_id]
//     );

//     // 🔹 RECORD WEIGHMENT HISTORY
//     await client.query(
//       `INSERT INTO sales_order_weighment_history (
//         sales_order_id, sales_order_number, invoice_id, invoice_number,
//         weighment_date, in_weight, out_weight, net_weight,
//         quantity_invoiced, amount_invoiced,
//         remaining_quantity_before, remaining_quantity_after,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)`,
//       [
//         sales_order_id, sales_order_number, invoiceId, invoiceNumber,
//         new Date(), weighment.in_weight, weighment.out_weight, weighment.net_weight,
//         currentInvoiceQty, totalAmount,
//         originalTotalQty - invoicedSoFar, remainingAfterInvoice,
//         userId
//       ]
//     );

//     // 🔹 CREATE APPROVAL RECORD
//     await client.query(
//       `INSERT INTO invoice_approvals (invoice_id, user_id, status, comments)
//        VALUES ($1, $2, 'pending', 'Awaiting Approval')`,
//       [invoiceId, userId]
//     );

//     await client.query("COMMIT");

//     // Send success message with partial invoice info
//     let message = "Main Invoice created and sent for approval!";
//     if (remainingAfterInvoice > 0) {
//       message = `Invoice created for ${currentInvoiceQty} TONS. Remaining: ${remainingAfterInvoice} TONS left to invoice.`;
//     } else if (remainingAfterInvoice === 0) {
//       message = `Final invoice created! Full order quantity (${originalTotalQty} TONS) has been invoiced.`;
//     }

//     return res.json({
//       success: true,
//       message,
//       invoiceId,
//       invoiceNumber,
//       invoicedQuantity: currentInvoiceQty,
//       remainingQuantity: remainingAfterInvoice,
//       isFullyInvoiced: remainingAfterInvoice === 0
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE MAIN INVOICE ERROR:", err);
//     return res.status(500).json({ 
//       message: err.message || "Failed to create invoice",
//       error: err.toString()
//     });
//   } finally {
//     client.release();
//   }
// };




// const approveInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const invoiceId = req.params.id;
//     const userId = req.session?.user?.id;
//     const orgId = req.session?.user?.orgId;

//     if (!userId || !orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized",
//       });
//     }

//     await client.query("BEGIN");

//     // ==========================================
//     // VALIDATE INVOICE
//     // ==========================================

//     const invoiceRes = await client.query(
//       `
//       SELECT *
//       FROM sales_invoices
//       WHERE id = $1
//       AND org_id = $2
//       `,
//       [invoiceId, orgId],
//     );

//     if (!invoiceRes.rows.length) {
//       throw new Error("Invoice not found");
//     }

//     const invoice = invoiceRes.rows[0];

//     if (invoice.status === "approved") {
//       throw new Error("Invoice already approved");
//     }

//     // ==========================================
//     // UPDATE APPROVAL DETAILS
//     // ==========================================

//     await client.query(
//       `
//       UPDATE sales_invoices
//       SET
//         status = 'approved',
//         approved_by = $1,
//         approved_at = NOW()
//       WHERE id = $2
//       `,
//       [userId, invoiceId],
//     );

//     // approval history table
//     await client.query(
//       `
//       UPDATE invoice_approvals
//       SET
//         status = 'approved',
//         comments = 'Invoice Approved',
//         approved_at = NOW()
//       WHERE invoice_id = $1
//       `,
//       [invoiceId],
//     );

//     // ==========================================
//     // GET ITEMS
//     // ==========================================

//     const itemsRes = await client.query(
//       `
//       SELECT *
//       FROM sales_invoice_items
//       WHERE invoice_id = $1
//       `,
//       [invoiceId],
//     );

//     const dbItems = itemsRes.rows;

//     // ==========================================
//     // COMPANY
//     // ==========================================

//     const companyRes = await client.query(
//       `
//       SELECT
//         org_name,
//         address,
//         gst_number AS gstin,
//         cin_number AS cin,
//         phone,
//         email,
//         bank_details,
//         bank_name,
//         account_number,
//         ifsc_code,
//         branch
//       FROM organizations
//       WHERE id = $1
//       `,
//       [orgId],
//     );

//     const company = companyRes.rows[0] || {};

//     const creatorRes = await client.query(
//       `
//       SELECT u.name as created_by_name
//       FROM sales_invoices si
//       LEFT JOIN users u ON si.created_by = u.id
//       WHERE si.id = $1
//       `,
//       [invoiceId],
//     );

//     const creatorName = creatorRes.rows[0]?.created_by_name || "-";

//     const approverRes = await client.query(
//       `
//       SELECT name
//       FROM users
//       WHERE id = $1
//       `,
//       [userId],
//     );

//     const approverName = approverRes.rows[0]?.name || "-";

//     // ==========================================
//     // GET TRANSPORT DETAILS & VEHICLE DATA
//     // ==========================================
    
//     let transportDetails = {
//       transporter_name: "-",
//       vehicle_number: "-",
//       from_location: "-",
//       to_location: "-"
//     };

//     let vehicleData = null;
//     let gatePassData = null;

//     if (invoice.sales_order_id) {
//       const vehicleRes = await client.query(
//         `
//         SELECT 
//           cv.id as vehicle_id,
//           cv.vehicle_number,
//           cv.vehicle_type,
//           cv.driver_name,
//           cv.driver_number,
//           cv.transporter_name as agency_name,
//           cv.sales_order_number,
//           cv.gate_status,
//           sd.shipment_number,
//           ta.agency_name as transporter_name,
//           so.so_number
//         FROM sales_orders so
//         LEFT JOIN shipment_dispatches sd ON sd.sales_order_id = so.id
//         LEFT JOIN commercial_vehicles cv ON cv.id = sd.vehicle_id
//         LEFT JOIN transport_agencies ta ON ta.id = sd.transport_agency_id
//         WHERE so.id = $1
//         `,
//         [invoice.sales_order_id]
//       );
      
//       if (vehicleRes.rows[0]) {
//         vehicleData = vehicleRes.rows[0];
//         transportDetails = {
//           transporter_name: vehicleData.transporter_name || "-",
//           vehicle_number: vehicleData.vehicle_number || "-",
//           from_location: vehicleData.shipping_address || "-",
//           to_location: vehicleData.delivery_address || "-"
//         };
//       }
//     }

//     // ==========================================
//     // GET SALES ORDER DETAILS
//     // ==========================================

//     const soRes = await client.query(
//       `
//       SELECT
//         so_number,
//         po_reference,
//         created_at
//       FROM sales_orders
//       WHERE id = $1
//       `,
//       [invoice.sales_order_id],
//     );

//     const salesOrder = soRes.rows[0] || {};

//     // ==========================================
//     // CREATE GATE PASS (if vehicle exists)
//     // ==========================================
    
//     if (vehicleData && vehicleData.vehicle_id) {
      
//       // Generate Gate Pass Number
//       const year = new Date().getFullYear();
//       const prefix = `GP-${year}-`;

//       const lastGatePass = await client.query(
//         `
//         SELECT pass_number 
//         FROM gate_passes 
//         WHERE pass_number LIKE $1 
//         ORDER BY id DESC 
//         LIMIT 1
//         `,
//         [`${prefix}%`]
//       );

//       let nextNumber = 1;
//       if (lastGatePass.rows.length > 0) {
//         const lastNum = parseInt(lastGatePass.rows[0].pass_number.replace(prefix, ''), 10);
//         if (!isNaN(lastNum)) nextNumber = lastNum + 1;
//       }

//       const gatePassNo = `${prefix}${String(nextNumber).padStart(4, '0')}`;
      
//       // Insert Gate Pass
//       const insertResult = await client.query(
//         `
//         INSERT INTO gate_passes (
//           commercial_vehicle_id,
//           pass_number,
//           pass_type,
//           pass_status,
//           generated_at,
//           generated_by,
//           org_id,
//           sales_order_number,
//           created_at,
//           updated_at
//         )
//         VALUES ($1, $2, 'ENTRY', 'ACTIVE', NOW(), $3, $4, $5, NOW(), NOW())
//         RETURNING id, pass_number, generated_at
//         `,
//         [vehicleData.vehicle_id, gatePassNo, userId, orgId, invoice.sales_order_number || salesOrder.so_number]
//       );

//       const insertedGatePass = insertResult.rows[0];

//       // Fetch complete gate pass data for PDF
//       const gatePassWithDetails = await client.query(
//         `
//         SELECT 
//           gp.id,
//           gp.pass_number,
//           gp.pass_type,
//           gp.pass_status,
//           gp.generated_at,
//           gp.exited_at,
//           gp.sales_order_number,
          
//           cv.vehicle_number,
//           cv.vehicle_type,
//           cv.driver_name,
//           cv.driver_number,
//           cv.ticket_no,
//           cv.gate_status,
//           cv.customer_name,
          
//           sd.shipment_number,
//           ta.agency_name as transporter_name,
          
//           u.name AS created_by_name
          
//         FROM gate_passes gp
        
//         LEFT JOIN commercial_vehicles cv 
//           ON cv.id = gp.commercial_vehicle_id
          
//         LEFT JOIN shipment_dispatches sd 
//           ON sd.vehicle_id = cv.id
          
//         LEFT JOIN transport_agencies ta 
//           ON ta.id = sd.transport_agency_id
          
//         LEFT JOIN users u 
//           ON u.id = gp.generated_by
          
//         WHERE gp.id = $1
//         `,
//         [insertedGatePass.id]
//       );

//       gatePassData = gatePassWithDetails.rows[0];

//       // Enhance gate pass data with additional details
//       if (gatePassData) {
//         gatePassData = {
//           ...gatePassData,
//           sales_order_number: gatePassData.sales_order_number || salesOrder.so_number,
//           customer_name: gatePassData.customer_name || invoice.customer_name,
//           transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
//           from_location: transportDetails.from_location,
//           to_location: transportDetails.to_location,
//           driver_name: gatePassData.driver_name || vehicleData.driver_name,
//           driver_number: gatePassData.driver_number || vehicleData.driver_number,
//         };
//       }

//       console.log(`✅ Gate pass created: ${gatePassNo} (ID: ${insertedGatePass.id})`);
//     }

//     // ==========================================
//     // PDF GENERATION
//     // ==========================================
    
//     const eWayPdf = await generateEWayPDF();
//     const eInvoicePdf = await generateEInvoicePDF();
    
//     let gatePassPdf = null;
    
//     if (gatePassData) {
//       try {
//         gatePassPdf = await generateGatePassPDF(gatePassData);
//         console.log(`✅ Gate pass PDF generated: ${gatePassData.pass_number}`);
//       } catch (err) {
//         console.error("❌ Gate pass PDF generation failed:", err.message);
//         // Don't throw, just continue without it
//       }
//     } else {
//       console.log("⚠️ No gate pass data available, skipping gate pass PDF");
//     }

//     // Generate Invoice PDF
//     const pdfBuffer = await generateInvoicePDF(
//       {
//         invoice_number: invoice.invoice_number,
//         invoice_date: invoice.invoice_date,
//         due_date: invoice.due_date,
//         advance_adjusted_amount: Number(invoice.advance_adjusted_amount || 0),
//         balance_payable_amount: Number(invoice.balance_payable_amount || 0),
//         proforma_invoice_id: invoice.proforma_invoice_id || null,

//         customer_name: invoice.customer_name,
//         customer_address: invoice.customer_address,
//         customer_gst: invoice.customer_gst,

//         order_no: salesOrder.so_number || "",
//         po_no: salesOrder.po_reference || "",
//         po_date: salesOrder.created_at || "",

//         gross: Number(invoice.amount || 0),
//         discount: Number(invoice.total_discount || 0),

//         net_value: Number(invoice.amount || 0) - Number(invoice.total_discount || 0),

//         freight: Number(invoice.freight || 0),

//         cgst: Number(invoice.total_tax || 0) / 2,
//         sgst: Number(invoice.total_tax || 0) / 2,

//         round_off: Number(invoice.round_off || 0),

//         total: Number(invoice.total_amount || 0),
        
//         prepared_by: creatorName,
//         verified_by: creatorName,
//         approved_by: approverName,

//         // transport_details: transportDetails,
//          transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
//          vehicle_number : transportDetails.transporter_name,

//         authorized_signatory: company.org_name || "-",
//       },
//       dbItems,
//       company,
//     );

//     // Merge PDFs (filter out null/undefined)
//     const pdfBuffers = [
//       pdfBuffer,
//       eWayPdf,
//       eInvoicePdf,
//       gatePassPdf
//     ].filter(pdf => pdf !== null && pdf !== undefined);

//     console.log(`📄 Total PDFs to merge: ${pdfBuffers.length}`);

//     if (pdfBuffers.length === 0) {
//       throw new Error("No PDFs were generated successfully. Cannot proceed.");
//     }

//     const finalMergedPdf = await mergePDFBuffers(pdfBuffers);

//     // ==========================================
//     // SAVE PDF TO DISK
//     // ==========================================

//     const uploadDir = path.join(process.cwd(), "uploads", "invoices");

//     if (!fs.existsSync(uploadDir)) {
//       fs.mkdirSync(uploadDir, { recursive: true });
//     }

//     const fileName = `Invoice-${invoice.invoice_number}.pdf`;
//     const filePath = path.join(uploadDir, fileName);

//     fs.writeFileSync(filePath, finalMergedPdf);

//     // ==========================================
//     // SAVE PATH IN DB
//     // ==========================================

//     await client.query(
//       `
//       UPDATE sales_invoices
//       SET pdf_path = $1
//       WHERE id = $2
//       `,
//       [`uploads/invoices/${fileName}`, invoiceId],
//     );

//     // ==========================================
//     // EMAIL
//     // ==========================================
    
//     const transporter = getTransporter();
    
//     const customerRes = await client.query(
//       `
//       SELECT email
//       FROM customers
//       WHERE customer_code = $1
//       `,
//       [invoice.customer_code],
//     );

//     const customerEmail = customerRes.rows[0]?.email;

//     const emailHtml = `
//       <div style="font-family: 'Segoe UI', Arial, sans-serif; background:#f4f6f8; padding:30px 0;">
//         <table width="100%" cellpadding="0" cellspacing="0"
//                style="max-width:680px;margin:auto;background:#ffffff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb;box-shadow:0 8px 20px rgba(0,0,0,0.04);">
//           <tr>
//             <td style="background:linear-gradient(135deg,#2563eb,#1e3a8a);padding:22px 28px;color:#fff;">
//               <div style="font-size:18px;font-weight:600;">
//                 Tax Invoice Generated
//               </div>
//               <div style="font-size:12px;opacity:0.85;margin-top:4px;">
//                 ERP Enterprise
//               </div>
//             </td>
//           </tr>
//           <tr>
//             <td style="padding:28px;color:#374151;font-size:14px;line-height:1.6;">
//               <p style="margin:0 0 12px;">
//                 Dear <strong>${invoice.customer_name}</strong>,
//               </p>
//               <p style="margin:0 0 16px;">
//                 Your invoice <strong style="color:#111827;">${invoice.invoice_number}</strong> has been successfully generated.
//               </p>
//               <div style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;padding:16px;margin:18px 0;">
//                 <table width="100%" style="font-size:13px;">
//                   <tr>
//                     <td style="color:#6b7280;">Invoice Number</td>
//                     <td style="text-align:right;font-weight:600;">${invoice.invoice_number}</td>
//                   </tr>
//                   <tr>
//                     <td style="color:#6b7280;padding-top:8px;">Total Amount</td>
//                     <td style="text-align:right;font-weight:600;color:#059669;padding-top:8px;">
//                       ₹${invoice.total_amount}
//                     </td>
//                   </tr>
//                   <tr>
//                     <td style="color:#6b7280;padding-top:8px;">Due Date</td>
//                     <td style="text-align:right;padding-top:8px;">
//                       ${formatDate(invoice.due_date)}
//                     </td>
//                   </tr>
//                 </table>
//               </div>
//               <div style="background:#eef2ff;border:1px solid #c7d2fe;border-radius:8px;padding:12px 14px;font-size:13px;">
//                 📎 The invoice PDF is attached with this email for your reference.
//               </div>
//               <p style="margin-top:20px;">
//                 Kindly review the invoice and ensure payment is completed on or before the due date.
//               </p>
//               <div style="margin:25px 0;">
//                 <a href="#"
//                    style="background:#2563eb;color:#fff;padding:10px 18px;border-radius:6px;text-decoration:none;font-size:13px;display:inline-block;">
//                    Contact Support
//                 </a>
//               </div>
//               <p style="margin-top:20px;">
//                 Regards,<br/>
//                 <strong>Accounts Team</strong><br/>
//                 <span style="font-size:12px;color:#6b7280;">
//                   ERP Enterprise
//                 </span>
//               </p>
//             </td>
//           </tr>
//           <tr>
//             <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#9ca3af;">
//               This is an automated invoice notification. Please do not reply.
//             </td>
//           </tr>
//         </table>
//       </div>
//     `;

//     if (customerEmail) {
//       await transporter.sendMail({
//         from: `"Accounts Team" <${process.env.MAIL_USER}>`,
//         to: customerEmail,
//         subject: `Approved Invoice ${invoice.invoice_number}`,
//         html: emailHtml,
//         attachments: [
//           {
//             filename: `Invoice-${invoice.invoice_number}.pdf`,
//             content: finalMergedPdf,
//           },
//         ],
//       });
//     }

//     await client.query("COMMIT");

//     return res.status(200).json({
//       success: true,
//       message: "Invoice approved and emailed successfully",
//       gatePass: gatePassData ? {
//         id: gatePassData.id,
//         pass_number: gatePassData.pass_number,
//         generated_at: gatePassData.generated_at
//       } : null
//     });
    
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("APPROVE INVOICE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

// const approveInvoice = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const invoiceId = req.params.id;
//     const userId = req.session?.user?.id;
//     const orgId = req.session?.user?.orgId;

//     if (!userId || !orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized",
//       });
//     }

//     await client.query("BEGIN");

//     // ==========================================
//     // VALIDATE INVOICE
//     // ==========================================

//     const invoiceRes = await client.query(
//       `
//       SELECT *
//       FROM sales_invoices
//       WHERE id = $1
//       AND org_id = $2
//       `,
//       [invoiceId, orgId],
//     );

//     if (!invoiceRes.rows.length) {
//       throw new Error("Invoice not found");
//     }

//     const invoice = invoiceRes.rows[0];

//     if (invoice.status === "approved") {
//       throw new Error("Invoice already approved");
//     }

//     // ==========================================
//     // UPDATE APPROVAL DETAILS
//     // ==========================================

//     await client.query(
//       `
//       UPDATE sales_invoices
//       SET
//         status = 'approved',
//         approved_by = $1,
//         approved_at = NOW()
//       WHERE id = $2
//       `,
//       [userId, invoiceId],
//     );

//     // approval history table
//     await client.query(
//       `
//       UPDATE invoice_approvals
//       SET
//         status = 'approved',
//         comments = 'Invoice Approved',
//         approved_at = NOW()
//       WHERE invoice_id = $1
//       `,
//       [invoiceId],
//     );

//     // ==========================================
//     // GET ITEMS
//     // ==========================================

//     const itemsRes = await client.query(
//       `
//       SELECT *
//       FROM sales_invoice_items
//       WHERE invoice_id = $1
//       `,
//       [invoiceId],
//     );

//     const dbItems = itemsRes.rows;

//     // ==========================================
//     // COMPANY
//     // ==========================================

//     const companyRes = await client.query(
//       `
//       SELECT
//         org_name,
//         address,
//         gst_number AS gstin,
//         cin_number AS cin,
//         phone,
//         email,
//         bank_details,
//         bank_name,
//         account_number,
//         ifsc_code,
//         branch
//       FROM organizations
//       WHERE id = $1
//       `,
//       [orgId],
//     );

//     const company = companyRes.rows[0] || {};

//     const creatorRes = await client.query(
//       `
//       SELECT u.name as created_by_name
//       FROM sales_invoices si
//       LEFT JOIN users u ON si.created_by = u.id
//       WHERE si.id = $1
//       `,
//       [invoiceId],
//     );

//     const creatorName = creatorRes.rows[0]?.created_by_name || "-";

//     const approverRes = await client.query(
//       `
//       SELECT name
//       FROM users
//       WHERE id = $1
//       `,
//       [userId],
//     );

//     const approverName = approverRes.rows[0]?.name || "-";

//     // ==========================================
//     // GET TRANSPORT DETAILS & VEHICLE DATA
//     // ==========================================
    
//     let transportDetails = {
//       transporter_name: "-",
//       vehicle_number: "-",
//       from_location: "-",
//       to_location: "-"
//     };

//     let vehicleData = null;
//     let gatePassData = null;

//     if (invoice.sales_order_id) {
//       const vehicleRes = await client.query(
//         `
//         SELECT 
//           cv.id as vehicle_id,
//           cv.vehicle_number,
//           cv.vehicle_type,
//           cv.driver_name,
//           cv.driver_number,
//           cv.transporter_name as agency_name,
//           cv.sales_order_number,
//           cv.gate_status,
//           sd.shipment_number,
//           ta.agency_name as transporter_name,
//           so.so_number
         
//         FROM sales_orders so
//         LEFT JOIN shipment_dispatches sd ON sd.sales_order_id = so.id
//         LEFT JOIN commercial_vehicles cv ON cv.id = sd.vehicle_id
//         LEFT JOIN transport_agencies ta ON ta.id = sd.transport_agency_id
//         WHERE so.id = $1
//         `,
//         [invoice.sales_order_id]
//       );
      
//       if (vehicleRes.rows[0]) {
//         vehicleData = vehicleRes.rows[0];
//         transportDetails = {
//           transporter_name: vehicleData.transporter_name || vehicleData.agency_name || "-",
//           vehicle_number: vehicleData.vehicle_number || "-",
//           from_location: vehicleData.shipping_address || "-",
//           to_location: vehicleData.delivery_address || "-"
//         };
//       }
//     }

//     // ==========================================
//     // GET SALES ORDER DETAILS
//     // ==========================================

//     const soRes = await client.query(
//       `
//       SELECT
//         so_number,
//         po_reference,
//         created_at
//       FROM sales_orders
//       WHERE id = $1
//       `,
//       [invoice.sales_order_id],
//     );

//     const salesOrder = soRes.rows[0] || {};

//     // ==========================================
//     // CREATE GATE PASS (if vehicle exists)
//     // ==========================================
    
//     if (vehicleData && vehicleData.vehicle_id) {
      
//       // Generate Gate Pass Number
//       const year = new Date().getFullYear();
//       const prefix = `GP-${year}-`;

//       const lastGatePass = await client.query(
//         `
//         SELECT pass_number 
//         FROM gate_passes 
//         WHERE pass_number LIKE $1 
//         ORDER BY id DESC 
//         LIMIT 1
//         `,
//         [`${prefix}%`]
//       );

//       let nextNumber = 1;
//       if (lastGatePass.rows.length > 0) {
//         const lastNum = parseInt(lastGatePass.rows[0].pass_number.replace(prefix, ''), 10);
//         if (!isNaN(lastNum)) nextNumber = lastNum + 1;
//       }

//       const gatePassNo = `${prefix}${String(nextNumber).padStart(4, '0')}`;
      
//       // Insert Gate Pass
//       const insertResult = await client.query(
//         `
//         INSERT INTO gate_passes (
//           commercial_vehicle_id,
//           pass_number,
//           pass_type,
//           pass_status,
//           generated_at,
//           generated_by,
//           org_id,
//           sales_order_number,
//           created_at,
//           updated_at
//         )
//         VALUES ($1, $2, 'ENTRY', 'ACTIVE', NOW(), $3, $4, $5, NOW(), NOW())
//         RETURNING id, pass_number, generated_at
//         `,
//         [vehicleData.vehicle_id, gatePassNo, userId, orgId, invoice.sales_order_number || salesOrder.so_number]
//       );

//       const insertedGatePass = insertResult.rows[0];

//       // Fetch complete gate pass data for PDF
//       const gatePassWithDetails = await client.query(
//         `
//         SELECT 
//           gp.id,
//           gp.pass_number,
//           gp.pass_type,
//           gp.pass_status,
//           gp.generated_at,
//           gp.exited_at,
//           gp.sales_order_number,
          
//           cv.vehicle_number,
//           cv.vehicle_type,
//           cv.driver_name,
//           cv.driver_number,
//           cv.ticket_no,
//           cv.gate_status,
//           cv.customer_name,
          
//           sd.shipment_number,
//           ta.agency_name as transporter_name,
          
//           u.name AS created_by_name
          
//         FROM gate_passes gp
        
//         LEFT JOIN commercial_vehicles cv 
//           ON cv.id = gp.commercial_vehicle_id
          
//         LEFT JOIN shipment_dispatches sd 
//           ON sd.vehicle_id = cv.id
          
//         LEFT JOIN transport_agencies ta 
//           ON ta.id = sd.transport_agency_id
          
//         LEFT JOIN users u 
//           ON u.id = gp.generated_by
          
//         WHERE gp.id = $1
//         `,
//         [insertedGatePass.id]
//       );

//       gatePassData = gatePassWithDetails.rows[0];

//       // Enhance gate pass data with additional details
//       if (gatePassData) {
//         gatePassData = {
//           ...gatePassData,
//           sales_order_number: gatePassData.sales_order_number || salesOrder.so_number,
//           customer_name: gatePassData.customer_name || invoice.customer_name,
//           transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
//           from_location: transportDetails.from_location,
//           to_location: transportDetails.to_location,
//           driver_name: gatePassData.driver_name || vehicleData.driver_name,
//           driver_number: gatePassData.driver_number || vehicleData.driver_number,
//         };
//       }

//       console.log(`✅ Gate pass created: ${gatePassNo} (ID: ${insertedGatePass.id})`);
//     }

//     // ==========================================
//     // PDF GENERATION
//     // ==========================================
    
//     const eWayPdf = await generateEWayPDF();
//     const eInvoicePdf = await generateEInvoicePDF();
    
//     let gatePassPdf = null;
    
//     if (gatePassData) {
//       try {
//         gatePassPdf = await generateGatePassPDF(gatePassData);
//         console.log(`✅ Gate pass PDF generated: ${gatePassData.pass_number}`);
//       } catch (err) {
//         console.error("❌ Gate pass PDF generation failed:", err.message);
//       }
//     } else {
//       console.log("⚠️ No gate pass data available, skipping gate pass PDF");
//     }

//     // Generate Invoice PDF
//     const pdfBuffer = await generateInvoicePDF(
//       {
//         invoice_number: invoice.invoice_number,
//         invoice_date: invoice.invoice_date,
//         due_date: invoice.due_date,
//         advance_adjusted_amount: Number(invoice.advance_adjusted_amount || 0),
//         balance_payable_amount: Number(invoice.balance_payable_amount || 0),
//         proforma_invoice_id: invoice.proforma_invoice_id || null,

//         customer_name: invoice.customer_name,
//         customer_address: invoice.customer_address,
//         customer_gst: invoice.customer_gst,

//         order_no: salesOrder.so_number || "",
//         po_no: salesOrder.po_reference || "",
//         po_date: salesOrder.created_at || "",

//         gross: Number(invoice.amount || 0),
//         discount: Number(invoice.total_discount || 0),

//         net_value: Number(invoice.amount || 0) - Number(invoice.total_discount || 0),

//         freight: Number(invoice.freight || 0),

//         cgst: Number(invoice.total_tax || 0) / 2,
//         sgst: Number(invoice.total_tax || 0) / 2,

//         round_off: Number(invoice.round_off || 0),

//         total: Number(invoice.total_amount || 0),
        
//         prepared_by: creatorName,
//         verified_by: creatorName,
//         approved_by: approverName,

//         transporter_name: (gatePassData && gatePassData.transporter_name) || transportDetails.transporter_name || "-",
//         vehicle_number: transportDetails.vehicle_number || "-",

//         authorized_signatory: company.org_name || "-",
//       },
//       dbItems,
//       company,
//     );

//     // Merge PDFs (filter out null/undefined)
//     const pdfBuffers = [
//       pdfBuffer,
//       eWayPdf,
//       eInvoicePdf,
//       gatePassPdf
//     ].filter(pdf => pdf !== null && pdf !== undefined);

//     console.log(`📄 Total PDFs to merge: ${pdfBuffers.length}`);

//     if (pdfBuffers.length === 0) {
//       throw new Error("No PDFs were generated successfully. Cannot proceed.");
//     }

//     const finalMergedPdf = await mergePDFBuffers(pdfBuffers);

//     // ==========================================
//     // SAVE PDF TO DISK
//     // ==========================================

//     const uploadDir = path.join(process.cwd(), "uploads", "invoices");

//     if (!fs.existsSync(uploadDir)) {
//       fs.mkdirSync(uploadDir, { recursive: true });
//     }

//     const fileName = `Invoice-${invoice.invoice_number}.pdf`;
//     const filePath = path.join(uploadDir, fileName);

//     fs.writeFileSync(filePath, finalMergedPdf);

//     // ==========================================
//     // SAVE PATH IN DB
//     // ==========================================

//     await client.query(
//       `
//       UPDATE sales_invoices
//       SET pdf_path = $1
//       WHERE id = $2
//       `,
//       [`uploads/invoices/${fileName}`, invoiceId],
//     );

//     // ==========================================
//     // EMAIL
//     // ==========================================
    
//     const transporter = getTransporter();
    
//     const customerRes = await client.query(
//       `
//       SELECT email
//       FROM customers
//       WHERE customer_code = $1
//       `,
//       [invoice.customer_code],
//     );

//     const customerEmail = customerRes.rows[0]?.email;

//     const emailHtml = `
//       <div style="font-family: 'Segoe UI', Arial, sans-serif; background:#f4f6f8; padding:30px 0;">
//         <table width="100%" cellpadding="0" cellspacing="0"
//                style="max-width:680px;margin:auto;background:#ffffff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb;box-shadow:0 8px 20px rgba(0,0,0,0.04);">
//           <tr>
//             <td style="background:linear-gradient(135deg,#2563eb,#1e3a8a);padding:22px 28px;color:#fff;">
//               <div style="font-size:18px;font-weight:600;">
//                 Tax Invoice Generated
//               </div>
//               <div style="font-size:12px;opacity:0.85;margin-top:4px;">
//                 ERP Enterprise
//               </div>
//             </td>
//           </tr>
//           <tr>
//             <td style="padding:28px;color:#374151;font-size:14px;line-height:1.6;">
//               <p style="margin:0 0 12px;">
//                 Dear <strong>${invoice.customer_name}</strong>,
//               </p>
//               <p style="margin:0 0 16px;">
//                 Your invoice <strong style="color:#111827;">${invoice.invoice_number}</strong> has been successfully generated.
//               </p>
//               <div style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;padding:16px;margin:18px 0;">
//                 <table width="100%" style="font-size:13px;">
//                   <tr>
//                     <td style="color:#6b7280;">Invoice Number</td>
//                     <td style="text-align:right;font-weight:600;">${invoice.invoice_number}</td>
//                   </tr>
//                   <tr>
//                     <td style="color:#6b7280;padding-top:8px;">Total Amount</td>
//                     <td style="text-align:right;font-weight:600;color:#059669;padding-top:8px;">
//                       ₹${invoice.total_amount}
//                     </td>
//                   </tr>
//                   <tr>
//                     <td style="color:#6b7280;padding-top:8px;">Due Date</td>
//                     <td style="text-align:right;padding-top:8px;">
//                       ${formatDate(invoice.due_date)}
//                     </td>
//                   </tr>
//                 </table>
//               </div>
//               <div style="background:#eef2ff;border:1px solid #c7d2fe;border-radius:8px;padding:12px 14px;font-size:13px;">
//                 📎 The invoice PDF is attached with this email for your reference.
//               </div>
//               <p style="margin-top:20px;">
//                 Kindly review the invoice and ensure payment is completed on or before the due date.
//               </p>
//               <div style="margin:25px 0;">
//                 <a href="#"
//                    style="background:#2563eb;color:#fff;padding:10px 18px;border-radius:6px;text-decoration:none;font-size:13px;display:inline-block;">
//                    Contact Support
//                 </a>
//               </div>
//               <p style="margin-top:20px;">
//                 Regards,<br/>
//                 <strong>Accounts Team</strong><br/>
//                 <span style="font-size:12px;color:#6b7280;">
//                   ERP Enterprise
//                 </span>
//               </p>
//             </td>
//           </tr>
//           <tr>
//             <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#9ca3af;">
//               This is an automated invoice notification. Please do not reply.
//             </td>
//           </tr>
//         </table>
//       </div>
//     `;

//     if (customerEmail) {
//       await transporter.sendMail({
//         from: `"Accounts Team" <${process.env.MAIL_USER}>`,
//         to: customerEmail,
//         subject: `Approved Invoice ${invoice.invoice_number}`,
//         html: emailHtml,
//         attachments: [
//           {
//             filename: `Invoice-${invoice.invoice_number}.pdf`,
//             content: finalMergedPdf,
//           },
//         ],
//       });
//     }

//     await client.query("COMMIT");

//     return res.status(200).json({
//       success: true,
//       message: "Invoice approved and emailed successfully",
//       gatePass: gatePassData ? {
//         id: gatePassData.id,
//         pass_number: gatePassData.pass_number,
//         generated_at: gatePassData.generated_at
//       } : null
//     });
    
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("APPROVE INVOICE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };  

const approveInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const invoiceId = req.params.id;
    const userId = req.session?.user?.id;
    const orgId = req.session?.user?.orgId;
    
    // ✅ Check if this is called from super approve (you can add a flag)
    const isFromSuperApprove = req.headers['x-super-approve'] === 'true' || req.query.fromSuperApprove === 'true';

    if (!userId || !orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized",
      });
    }

    await client.query("BEGIN");

    // ==========================================
    // VALIDATE INVOICE
    // ==========================================

    const invoiceRes = await client.query(
      `
      SELECT *
      FROM sales_invoices
      WHERE id = $1
      AND org_id = $2
      `,
      [invoiceId, orgId],
    );

    if (!invoiceRes.rows.length) {
      throw new Error("Invoice not found");
    }

    const invoice = invoiceRes.rows[0];

    // ✅ MODIFICATION: Skip approval check if called from super approve
    if (invoice.status === "approved" && !isFromSuperApprove) {
      throw new Error("Invoice already approved");
    }

    // ✅ MODIFICATION: Only update approval details if not already approved OR if from super approve
    if (invoice.status !== "approved") {
      await client.query(
        `
        UPDATE sales_invoices
        SET
          status = 'approved',
          approved_by = $1,
          approved_at = NOW()
        WHERE id = $2
        `,
        [userId, invoiceId],
      );

      // approval history table
      await client.query(
        `
        UPDATE invoice_approvals
        SET
          status = 'approved',
          comments = 'Invoice Approved',
          approved_at = NOW()
        WHERE invoice_id = $1
        `,
        [invoiceId],
      );
    } else {
      console.log(`📝 Invoice ${invoiceId} already approved, skipping status update`);
    }

    // ==========================================
    // GET ITEMS (Rest of your existing code continues unchanged)
    // ==========================================
    
    const itemsRes = await client.query(
      `
      SELECT *
      FROM sales_invoice_items
      WHERE invoice_id = $1
      `,
      [invoiceId],
    );

    const dbItems = itemsRes.rows;

    // ==========================================
    // COMPANY
    // ==========================================

    const companyRes = await client.query(
      `
      SELECT
        org_name,
        address,
        gst_number AS gstin,
        cin_number AS cin,
        phone,
        email,
        bank_details,
        bank_name,
        account_number,
        ifsc_code,
        branch
      FROM organizations
      WHERE id = $1
      `,
      [orgId],
    );

    const company = companyRes.rows[0] || {};

    const creatorRes = await client.query(
      `
      SELECT u.name as created_by_name
      FROM sales_invoices si
      LEFT JOIN users u ON si.created_by = u.id
      WHERE si.id = $1
      `,
      [invoiceId],
    );

    const creatorName = creatorRes.rows[0]?.created_by_name || "-";

    const approverRes = await client.query(
      `
      SELECT name
      FROM users
      WHERE id = $1
      `,
      [userId],
    );

    const approverName = approverRes.rows[0]?.name || "-";

    // ==========================================
    // GET TRANSPORT DETAILS & VEHICLE DATA
    // ==========================================
    
    let transportDetails = {
      transporter_name: "-",
      vehicle_number: "-",
      from_location: "-",
      to_location: "-"
    };

    let vehicleData = null;
    let gatePassData = null;

    if (invoice.sales_order_id) {
      const vehicleRes = await client.query(
        `
        SELECT 
          cv.id as vehicle_id,
          cv.vehicle_number,
          cv.vehicle_type,
          cv.driver_name,
          cv.driver_number,
          cv.transporter_name as agency_name,
          cv.sales_order_number,
          cv.gate_status,
          sd.shipment_number,
          ta.agency_name as transporter_name,
          so.so_number
         
        FROM sales_orders so
        LEFT JOIN shipment_dispatches sd ON sd.sales_order_id = so.id
        LEFT JOIN commercial_vehicles cv ON cv.id = sd.vehicle_id
        LEFT JOIN transport_agencies ta ON ta.id = sd.transport_agency_id
        WHERE so.id = $1
        `,
        [invoice.sales_order_id]
      );
      
      if (vehicleRes.rows[0]) {
        vehicleData = vehicleRes.rows[0];
        transportDetails = {
          transporter_name: vehicleData.transporter_name || vehicleData.agency_name || "-",
          vehicle_number: vehicleData.vehicle_number || "-",
          from_location: vehicleData.shipping_address || "-",
          to_location: vehicleData.delivery_address || "-"
        };
      }
    }

    // ==========================================
    // GET SALES ORDER DETAILS
    // ==========================================

    const soRes = await client.query(
      `
      SELECT
        so_number,
        po_reference,
        created_at
      FROM sales_orders
      WHERE id = $1
      `,
      [invoice.sales_order_id],
    );

    const salesOrder = soRes.rows[0] || {};

    // ==========================================
    // CREATE GATE PASS (if vehicle exists)
    // ✅ Check if gate pass already exists for this invoice
    // ==========================================
    
    if (vehicleData && vehicleData.vehicle_id) {
      
      // ✅ Check if gate pass already exists
      const existingGatePass = await client.query(
        `
        SELECT id, pass_number 
        FROM gate_passes 
        WHERE commercial_vehicle_id = $1 
        AND sales_order_number = $2
        ORDER BY id DESC 
        LIMIT 1
        `,
        [vehicleData.vehicle_id, invoice.sales_order_number || salesOrder.so_number]
      );
      
      let insertedGatePass;
      
      if (existingGatePass.rows.length > 0) {
        // Use existing gate pass
        console.log(`✅ Gate pass already exists: ${existingGatePass.rows[0].pass_number}`);
        insertedGatePass = existingGatePass.rows[0];
      } else {
        // Generate Gate Pass Number
        const year = new Date().getFullYear();
        const prefix = `GP-${year}-`;

        const lastGatePass = await client.query(
          `
          SELECT pass_number 
          FROM gate_passes 
          WHERE pass_number LIKE $1 
          ORDER BY id DESC 
          LIMIT 1
          `,
          [`${prefix}%`]
        );

        let nextNumber = 1;
        if (lastGatePass.rows.length > 0) {
          const lastNum = parseInt(lastGatePass.rows[0].pass_number.replace(prefix, ''), 10);
          if (!isNaN(lastNum)) nextNumber = lastNum + 1;
        }

        const gatePassNo = `${prefix}${String(nextNumber).padStart(4, '0')}`;
        
        // Insert Gate Pass
        const insertResult = await client.query(
          `
          INSERT INTO gate_passes (
            commercial_vehicle_id,
            pass_number,
            pass_type,
            pass_status,
            generated_at,
            generated_by,
            org_id,
            sales_order_number,
            created_at,
            updated_at
          )
          VALUES ($1, $2, 'ENTRY', 'ACTIVE', NOW(), $3, $4, $5, NOW(), NOW())
          RETURNING id, pass_number, generated_at
          `,
          [vehicleData.vehicle_id, gatePassNo, userId, orgId, invoice.sales_order_number || salesOrder.so_number]
        );

        insertedGatePass = insertResult.rows[0];
        console.log(`✅ New gate pass created: ${gatePassNo}`);
      }

      // Fetch complete gate pass data for PDF
      const gatePassWithDetails = await client.query(
        `
        SELECT 
          gp.id,
          gp.pass_number,
          gp.pass_type,
          gp.pass_status,
          gp.generated_at,
          gp.exited_at,
          gp.sales_order_number,
          
          cv.vehicle_number,
          cv.vehicle_type,
          cv.driver_name,
          cv.driver_number,
          cv.ticket_no,
          cv.gate_status,
          cv.customer_name,
          
          sd.shipment_number,
          ta.agency_name as transporter_name,
          
          u.name AS created_by_name
          
        FROM gate_passes gp
        
        LEFT JOIN commercial_vehicles cv 
          ON cv.id = gp.commercial_vehicle_id
          
        LEFT JOIN shipment_dispatches sd 
          ON sd.vehicle_id = cv.id
          
        LEFT JOIN transport_agencies ta 
          ON ta.id = sd.transport_agency_id
          
        LEFT JOIN users u 
          ON u.id = gp.generated_by
          
        WHERE gp.id = $1
        `,
        [insertedGatePass.id]
      );

      gatePassData = gatePassWithDetails.rows[0];

      // Enhance gate pass data with additional details
      if (gatePassData) {
        gatePassData = {
          ...gatePassData,
          sales_order_number: gatePassData.sales_order_number || salesOrder.so_number,
          customer_name: gatePassData.customer_name || invoice.customer_name,
          transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
          from_location: transportDetails.from_location,
          to_location: transportDetails.to_location,
          driver_name: gatePassData.driver_name || vehicleData.driver_name,
          driver_number: gatePassData.driver_number || vehicleData.driver_number,
        };
      }
    }

    // ==========================================
    // PDF GENERATION (Rest of your code remains the same)
    // ==========================================
    
    const eWayPdf = await generateEWayPDF();
    const eInvoicePdf = await generateEInvoicePDF();
    
    let gatePassPdf = null;
    
    if (gatePassData) {
      try {
        gatePassPdf = await generateGatePassPDF(gatePassData);
        console.log(`✅ Gate pass PDF generated: ${gatePassData.pass_number}`);
      } catch (err) {
        console.error("❌ Gate pass PDF generation failed:", err.message);
      }
    } else {
      console.log("⚠️ No gate pass data available, skipping gate pass PDF");
    }

    // Generate Invoice PDF
    const pdfBuffer = await generateInvoicePDF(
      {
        invoice_number: invoice.invoice_number,
        invoice_date: invoice.invoice_date,
        due_date: invoice.due_date,
        advance_adjusted_amount: Number(invoice.advance_adjusted_amount || 0),
        balance_payable_amount: Number(invoice.balance_payable_amount || 0),
        proforma_invoice_id: invoice.proforma_invoice_id || null,

        customer_name: invoice.customer_name,
        customer_address: invoice.customer_address,
        customer_gst: invoice.customer_gst,

        order_no: salesOrder.so_number || "",
        po_no: salesOrder.po_reference || "",
        po_date: salesOrder.created_at || "",

        gross: Number(invoice.amount || 0),
        discount: Number(invoice.total_discount || 0),

        net_value: Number(invoice.amount || 0) - Number(invoice.total_discount || 0),

        freight: Number(invoice.freight || 0),

        cgst: Number(invoice.total_tax || 0) / 2,
        sgst: Number(invoice.total_tax || 0) / 2,

        round_off: Number(invoice.round_off || 0),

        total: Number(invoice.total_amount || 0),
        
        prepared_by: creatorName,
        verified_by: creatorName,
        approved_by: approverName,

        transporter_name: (gatePassData && gatePassData.transporter_name) || transportDetails.transporter_name || "-",
        vehicle_number: transportDetails.vehicle_number || "-",

        authorized_signatory: company.org_name || "-",
      },
      dbItems,
      company,
    );

    // Merge PDFs (filter out null/undefined)
    const pdfBuffers = [
      pdfBuffer,
      eWayPdf,
      eInvoicePdf,
      gatePassPdf
    ].filter(pdf => pdf !== null && pdf !== undefined);

    console.log(`📄 Total PDFs to merge: ${pdfBuffers.length}`);

    if (pdfBuffers.length === 0) {
      throw new Error("No PDFs were generated successfully. Cannot proceed.");
    }

    const finalMergedPdf = await mergePDFBuffers(pdfBuffers);

    // ==========================================
    // SAVE PDF TO DISK (Check if already exists)
    // ==========================================

    const uploadDir = path.join(process.cwd(), "uploads", "invoices");

    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }

    const fileName = `Invoice-${invoice.invoice_number}.pdf`;
    const filePath = path.join(uploadDir, fileName);

    // ✅ Only save if PDF doesn't exist
    if (!fs.existsSync(filePath)) {
      fs.writeFileSync(filePath, finalMergedPdf);
      console.log(`✅ PDF saved: ${fileName}`);
    } else {
      console.log(`📄 PDF already exists: ${fileName}`);
    }

    // ==========================================
    // SAVE PATH IN DB (Only if not already set)
    // ==========================================

    if (!invoice.pdf_path) {
      await client.query(
        `
        UPDATE sales_invoices
        SET pdf_path = $1
        WHERE id = $2
        `,
        [`uploads/invoices/${fileName}`, invoiceId],
      );
      console.log(`✅ PDF path saved to database`);
    }

    // ==========================================
    // EMAIL (Skip if already sent)
    // ==========================================
    
    const transporter = transporter();
    
    const customerRes = await client.query(
      `
      SELECT email
      FROM customers
      WHERE customer_code = $1
      `,
      [invoice.customer_code],
    );

    const customerEmail = customerRes.rows[0]?.email;

    // ✅ Check if email already sent (you might need to add a flag in the database)
    // For now, we'll check if PDF exists and was just created, or add a email_sent flag
    
    const emailHtml = `
      <div style="font-family: 'Segoe UI', Arial, sans-serif; background:#f4f6f8; padding:30px 0;">
        <table width="100%" cellpadding="0" cellspacing="0"
               style="max-width:680px;margin:auto;background:#ffffff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb;box-shadow:0 8px 20px rgba(0,0,0,0.04);">
          <tr>
            <td style="background:linear-gradient(135deg,#2563eb,#1e3a8a);padding:22px 28px;color:#fff;">
              <div style="font-size:18px;font-weight:600;">
                Tax Invoice Generated
              </div>
              <div style="font-size:12px;opacity:0.85;margin-top:4px;">
                ERP Enterprise
              </div>
            </td>
          </tr>
          <tr>
            <td style="padding:28px;color:#374151;font-size:14px;line-height:1.6;">
              <p style="margin:0 0 12px;">
                Dear <strong>${invoice.customer_name}</strong>,
              </p>
              <p style="margin:0 0 16px;">
                Your invoice <strong style="color:#111827;">${invoice.invoice_number}</strong> has been successfully generated.
              </p>
              <div style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;padding:16px;margin:18px 0;">
                <table width="100%" style="font-size:13px;">
                  <tr>
                    <td style="color:#6b7280;">Invoice Number</td>
                    <td style="text-align:right;font-weight:600;">${invoice.invoice_number}</td>
                  </tr>
                  <tr>
                    <td style="color:#6b7280;padding-top:8px;">Total Amount</td>
                    <td style="text-align:right;font-weight:600;color:#059669;padding-top:8px;">
                      ₹${invoice.total_amount}
                    </td>
                  </tr>
                  <tr>
                    <td style="color:#6b7280;padding-top:8px;">Due Date</td>
                    <td style="text-align:right;padding-top:8px;">
                      ${formatDate(invoice.due_date)}
                    </td>
                  </tr>
                </table>
              </div>
              <div style="background:#eef2ff;border:1px solid #c7d2fe;border-radius:8px;padding:12px 14px;font-size:13px;">
                📎 The invoice PDF is attached with this email for your reference.
              </div>
              <p style="margin-top:20px;">
                Kindly review the invoice and ensure payment is completed on or before the due date.
              </p>
              <div style="margin:25px 0;">
                <a href="#"
                   style="background:#2563eb;color:#fff;padding:10px 18px;border-radius:6px;text-decoration:none;font-size:13px;display:inline-block;">
                   Contact Support
                </a>
              </div>
              <p style="margin-top:20px;">
                Regards,<br/>
                <strong>Accounts Team</strong><br/>
                <span style="font-size:12px;color:#6b7280;">
                  ERP Enterprise
                </span>
              </p>
            </td>
          </tr>
          <tr>
            <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#9ca3af;">
              This is an automated invoice notification. Please do not reply.
            </td>
          </tr>
        </table>
      </div>
    `;

    if (customerEmail) {
      // ✅ Only send email if not already sent (you may want to track this in DB)
      // For now, we'll send it but you should add an email_sent flag
      await transporter.sendMail({
        from: `"Accounts Team" <${process.env.MAIL_USER}>`,
        to: customerEmail,
        subject: `Approved Invoice ${invoice.invoice_number}`,
        html: emailHtml,
        attachments: [
          {
            filename: `Invoice-${invoice.invoice_number}.pdf`,
            content: finalMergedPdf,
          },
        ],
      });
      console.log(`✅ Email sent to ${customerEmail}`);
    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: invoice.status === "approved" && !isFromSuperApprove ? "Invoice already approved, PDF generated and email sent" : "Invoice approved and emailed successfully",
      gatePass: gatePassData ? {
        id: gatePassData.id,
        pass_number: gatePassData.pass_number,
        generated_at: gatePassData.generated_at
      } : null
    });
    
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("APPROVE INVOICE ERROR:", err);
    return res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

// Add this function to salesInvoiceController.js
const generateAndSendInvoiceDocuments = async (invoiceId, userId, orgId) => {
  const client = await pool.connect();
  
  try {
   const approveInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const invoiceId = req.params.id;
    const userId = req.session?.user?.id;
    const orgId = req.session?.user?.orgId;

    if (!userId || !orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized",
      });
    }

    await client.query("BEGIN");

    // ==========================================
    // VALIDATE INVOICE
    // ==========================================

    const invoiceRes = await client.query(
      `
      SELECT *
      FROM sales_invoices
      WHERE id = $1
      AND org_id = $2
      `,
      [invoiceId, orgId],
    );

    if (!invoiceRes.rows.length) {
      throw new Error("Invoice not found");
    }

    const invoice = invoiceRes.rows[0];

    if (invoice.status === "approved") {
      throw new Error("Invoice already approved");
    }

    // ==========================================
    // UPDATE APPROVAL DETAILS
    // ==========================================

    await client.query(
      `
      UPDATE sales_invoices
      SET
        status = 'approved',
        approved_by = $1,
        approved_at = NOW()
      WHERE id = $2
      `,
      [userId, invoiceId],
    );

    // approval history table
    await client.query(
      `
      UPDATE invoice_approvals
      SET
        status = 'approved',
        comments = 'Invoice Approved',
        approved_at = NOW()
      WHERE invoice_id = $1
      `,
      [invoiceId],
    );

    // ==========================================
    // GET ITEMS
    // ==========================================

    const itemsRes = await client.query(
      `
      SELECT *
      FROM sales_invoice_items
      WHERE invoice_id = $1
      `,
      [invoiceId],
    );

    const dbItems = itemsRes.rows;

    // ==========================================
    // COMPANY
    // ==========================================

    const companyRes = await client.query(
      `
      SELECT
        org_name,
        address,
        gst_number AS gstin,
        cin_number AS cin,
        phone,
        email,
        bank_details,
        bank_name,
        account_number,
        ifsc_code,
        branch
      FROM organizations
      WHERE id = $1
      `,
      [orgId],
    );

    const company = companyRes.rows[0] || {};

    const creatorRes = await client.query(
      `
      SELECT u.name as created_by_name
      FROM sales_invoices si
      LEFT JOIN users u ON si.created_by = u.id
      WHERE si.id = $1
      `,
      [invoiceId],
    );

    const creatorName = creatorRes.rows[0]?.created_by_name || "-";

    const approverRes = await client.query(
      `
      SELECT name
      FROM users
      WHERE id = $1
      `,
      [userId],
    );

    const approverName = approverRes.rows[0]?.name || "-";

    // ==========================================
    // GET TRANSPORT DETAILS & VEHICLE DATA
    // ==========================================
    
    let transportDetails = {
      transporter_name: "-",
      vehicle_number: "-",
      from_location: "-",
      to_location: "-"
    };

    let vehicleData = null;
    let gatePassData = null;

    if (invoice.sales_order_id) {
      const vehicleRes = await client.query(
        `
        SELECT 
          cv.id as vehicle_id,
          cv.vehicle_number,
          cv.vehicle_type,
          cv.driver_name,
          cv.driver_number,
          cv.transporter_name as agency_name,
          cv.sales_order_number,
          cv.gate_status,
          sd.shipment_number,
          ta.agency_name as transporter_name,
          so.so_number
        FROM sales_orders so
        LEFT JOIN shipment_dispatches sd ON sd.sales_order_id = so.id
        LEFT JOIN commercial_vehicles cv ON cv.id = sd.vehicle_id
        LEFT JOIN transport_agencies ta ON ta.id = sd.transport_agency_id
        WHERE so.id = $1
        `,
        [invoice.sales_order_id]
      );
      
      if (vehicleRes.rows[0]) {
        vehicleData = vehicleRes.rows[0];
        transportDetails = {
          transporter_name: vehicleData.transporter_name || "-",
          vehicle_number: vehicleData.vehicle_number || "-",
          from_location: vehicleData.shipping_address || "-",
          to_location: vehicleData.delivery_address || "-"
        };
      }
    }

    // ==========================================
    // GET SALES ORDER DETAILS
    // ==========================================

    const soRes = await client.query(
      `
      SELECT
        so_number,
        po_reference,
        created_at
      FROM sales_orders
      WHERE id = $1
      `,
      [invoice.sales_order_id],
    );

    const salesOrder = soRes.rows[0] || {};

    // ==========================================
    // CREATE GATE PASS (if vehicle exists)
    // ==========================================
    
    if (vehicleData && vehicleData.vehicle_id) {
      
      // Generate Gate Pass Number
      const year = new Date().getFullYear();
      const prefix = `GP-${year}-`;

      const lastGatePass = await client.query(
        `
        SELECT pass_number 
        FROM gate_passes 
        WHERE pass_number LIKE $1 
        ORDER BY id DESC 
        LIMIT 1
        `,
        [`${prefix}%`]
      );

      let nextNumber = 1;
      if (lastGatePass.rows.length > 0) {
        const lastNum = parseInt(lastGatePass.rows[0].pass_number.replace(prefix, ''), 10);
        if (!isNaN(lastNum)) nextNumber = lastNum + 1;
      }

      const gatePassNo = `${prefix}${String(nextNumber).padStart(4, '0')}`;
      
      // Insert Gate Pass
      const insertResult = await client.query(
        `
        INSERT INTO gate_passes (
          commercial_vehicle_id,
          pass_number,
          pass_type,
          pass_status,
          generated_at,
          generated_by,
          org_id,
          sales_order_number,
          created_at,
          updated_at
        )
        VALUES ($1, $2, 'ENTRY', 'ACTIVE', NOW(), $3, $4, $5, NOW(), NOW())
        RETURNING id, pass_number, generated_at
        `,
        [vehicleData.vehicle_id, gatePassNo, userId, orgId, invoice.sales_order_number || salesOrder.so_number]
      );

      const insertedGatePass = insertResult.rows[0];

      // Fetch complete gate pass data for PDF
      const gatePassWithDetails = await client.query(
        `
        SELECT 
          gp.id,
          gp.pass_number,
          gp.pass_type,
          gp.pass_status,
          gp.generated_at,
          gp.exited_at,
          gp.sales_order_number,
          
          cv.vehicle_number,
          cv.vehicle_type,
          cv.driver_name,
          cv.driver_number,
          cv.ticket_no,
          cv.gate_status,
          cv.customer_name,
          
          sd.shipment_number,
          ta.agency_name as transporter_name,
          
          u.name AS created_by_name
          
        FROM gate_passes gp
        
        LEFT JOIN commercial_vehicles cv 
          ON cv.id = gp.commercial_vehicle_id
          
        LEFT JOIN shipment_dispatches sd 
          ON sd.vehicle_id = cv.id
          
        LEFT JOIN transport_agencies ta 
          ON ta.id = sd.transport_agency_id
          
        LEFT JOIN users u 
          ON u.id = gp.generated_by
          
        WHERE gp.id = $1
        `,
        [insertedGatePass.id]
      );

      gatePassData = gatePassWithDetails.rows[0];

      // Enhance gate pass data with additional details
      if (gatePassData) {
        gatePassData = {
          ...gatePassData,
          sales_order_number: gatePassData.sales_order_number || salesOrder.so_number,
          customer_name: gatePassData.customer_name || invoice.customer_name,
          transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
          from_location: transportDetails.from_location,
          to_location: transportDetails.to_location,
          driver_name: gatePassData.driver_name || vehicleData.driver_name,
          driver_number: gatePassData.driver_number || vehicleData.driver_number,
        };
      }

      console.log(`✅ Gate pass created: ${gatePassNo} (ID: ${insertedGatePass.id})`);
    }

    // ==========================================
    // PDF GENERATION
    // ==========================================
    
    const eWayPdf = await generateEWayPDF();
    const eInvoicePdf = await generateEInvoicePDF();
    
    let gatePassPdf = null;
    
    if (gatePassData) {
      try {
        gatePassPdf = await generateGatePassPDF(gatePassData);
        console.log(`✅ Gate pass PDF generated: ${gatePassData.pass_number}`);
      } catch (err) {
        console.error("❌ Gate pass PDF generation failed:", err.message);
        // Don't throw, just continue without it
      }
    } else {
      console.log("⚠️ No gate pass data available, skipping gate pass PDF");
    }

    // Generate Invoice PDF
    const pdfBuffer = await generateInvoicePDF(
      {
        invoice_number: invoice.invoice_number,
        invoice_date: invoice.invoice_date,
        due_date: invoice.due_date,
        advance_adjusted_amount: Number(invoice.advance_adjusted_amount || 0),
        balance_payable_amount: Number(invoice.balance_payable_amount || 0),
        proforma_invoice_id: invoice.proforma_invoice_id || null,

        customer_name: invoice.customer_name,
        customer_address: invoice.customer_address,
        customer_gst: invoice.customer_gst,

        order_no: salesOrder.so_number || "",
        po_no: salesOrder.po_reference || "",
        po_date: salesOrder.created_at || "",

        gross: Number(invoice.amount || 0),
        discount: Number(invoice.total_discount || 0),

        net_value: Number(invoice.amount || 0) - Number(invoice.total_discount || 0),

        freight: Number(invoice.freight || 0),

        cgst: Number(invoice.total_tax || 0) / 2,
        sgst: Number(invoice.total_tax || 0) / 2,

        round_off: Number(invoice.round_off || 0),

        total: Number(invoice.total_amount || 0),
        
        prepared_by: creatorName,
        verified_by: creatorName,
        approved_by: approverName,

        // transport_details: transportDetails,
         transporter_name: gatePassData.transporter_name || transportDetails.transporter_name,
         vehicle_number : transportDetails.transporter_name,

        authorized_signatory: company.org_name || "-",
      },
      dbItems,
      company,
    );

    // Merge PDFs (filter out null/undefined)
    const pdfBuffers = [
      pdfBuffer,
      eWayPdf,
      eInvoicePdf,
      gatePassPdf
    ].filter(pdf => pdf !== null && pdf !== undefined);

    console.log(`📄 Total PDFs to merge: ${pdfBuffers.length}`);

    if (pdfBuffers.length === 0) {
      throw new Error("No PDFs were generated successfully. Cannot proceed.");
    }

    const finalMergedPdf = await mergePDFBuffers(pdfBuffers);

    // ==========================================
    // SAVE PDF TO DISK
    // ==========================================

    const uploadDir = path.join(process.cwd(), "uploads", "invoices");

    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }

    const fileName = `Invoice-${invoice.invoice_number}.pdf`;
    const filePath = path.join(uploadDir, fileName);

    fs.writeFileSync(filePath, finalMergedPdf);

    // ==========================================
    // SAVE PATH IN DB
    // ==========================================

    await client.query(
      `
      UPDATE sales_invoices
      SET pdf_path = $1
      WHERE id = $2
      `,
      [`uploads/invoices/${fileName}`, invoiceId],
    );

    // ==========================================
    // EMAIL
    // ==========================================
    
    const transporter = transporter();
    
    const customerRes = await client.query(
      `
      SELECT email
      FROM customers
      WHERE customer_code = $1
      `,
      [invoice.customer_code],
    );

    const customerEmail = customerRes.rows[0]?.email;

    const emailHtml = `
      <div style="font-family: 'Segoe UI', Arial, sans-serif; background:#f4f6f8; padding:30px 0;">
        <table width="100%" cellpadding="0" cellspacing="0"
               style="max-width:680px;margin:auto;background:#ffffff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb;box-shadow:0 8px 20px rgba(0,0,0,0.04);">
          <tr>
            <td style="background:linear-gradient(135deg,#2563eb,#1e3a8a);padding:22px 28px;color:#fff;">
              <div style="font-size:18px;font-weight:600;">
                Tax Invoice Generated
              </div>
              <div style="font-size:12px;opacity:0.85;margin-top:4px;">
                ERP Enterprise
              </div>
            </td>
          </tr>
          <tr>
            <td style="padding:28px;color:#374151;font-size:14px;line-height:1.6;">
              <p style="margin:0 0 12px;">
                Dear <strong>${invoice.customer_name}</strong>,
              </p>
              <p style="margin:0 0 16px;">
                Your invoice <strong style="color:#111827;">${invoice.invoice_number}</strong> has been successfully generated.
              </p>
              <div style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;padding:16px;margin:18px 0;">
                <table width="100%" style="font-size:13px;">
                  <tr>
                    <td style="color:#6b7280;">Invoice Number</td>
                    <td style="text-align:right;font-weight:600;">${invoice.invoice_number}</td>
                  </tr>
                  <tr>
                    <td style="color:#6b7280;padding-top:8px;">Total Amount</td>
                    <td style="text-align:right;font-weight:600;color:#059669;padding-top:8px;">
                      ₹${invoice.total_amount}
                    </td>
                  </tr>
                  <tr>
                    <td style="color:#6b7280;padding-top:8px;">Due Date</td>
                    <td style="text-align:right;padding-top:8px;">
                      ${formatDate(invoice.due_date)}
                    </td>
                  </tr>
                </table>
              </div>
              <div style="background:#eef2ff;border:1px solid #c7d2fe;border-radius:8px;padding:12px 14px;font-size:13px;">
                📎 The invoice PDF is attached with this email for your reference.
              </div>
              <p style="margin-top:20px;">
                Kindly review the invoice and ensure payment is completed on or before the due date.
              </p>
              <div style="margin:25px 0;">
                <a href="#"
                   style="background:#2563eb;color:#fff;padding:10px 18px;border-radius:6px;text-decoration:none;font-size:13px;display:inline-block;">
                   Contact Support
                </a>
              </div>
              <p style="margin-top:20px;">
                Regards,<br/>
                <strong>Accounts Team</strong><br/>
                <span style="font-size:12px;color:#6b7280;">
                  ERP Enterprise
                </span>
              </p>
            </td>
          </tr>
          <tr>
            <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#9ca3af;">
              This is an automated invoice notification. Please do not reply.
            </td>
          </tr>
        </table>
      </div>
    `;

    if (customerEmail) {
      await transporter.sendMail({
        from: `"Accounts Team" <${process.env.MAIL_USER}>`,
        to: customerEmail,
        subject: `Approved Invoice ${invoice.invoice_number}`,
        html: emailHtml,
        attachments: [
          {
            filename: `Invoice-${invoice.invoice_number}.pdf`,
            content: finalMergedPdf,
          },
        ],
      });
    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Invoice approved and emailed successfully",
      gatePass: gatePassData ? {
        id: gatePassData.id,
        pass_number: gatePassData.pass_number,
        generated_at: gatePassData.generated_at
      } : null
    });
    
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("APPROVE INVOICE ERROR:", err);
    return res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};
    
    return { success: true, message: "Documents generated and email sent" };
  } catch (error) {
    console.error("Error in generateAndSendInvoiceDocuments:", error);
    return { success: false, message: error.message };
  } finally {
    client.release();
  }
};


const createMainInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

    const {
      sales_order_id,
      sales_order_number,
      customer_code,
      customer_name,
      customer_address,
      customer_gst,
      invoice_date,
      due_date,
      notes,
      items,
      weighment,
      advance_adjusted_amount,
      balance_payable_amount,
    } = req.body;

    await client.query("BEGIN");

    // 🔹 CHECK IF SO EXISTS AND GET CURRENT INVOICED QUANTITY
    const soCheck = await client.query(
      `SELECT 
        so.*,
        COALESCE(SUM(si.total_quantity_invoiced), 0) as total_invoiced_qty,
        COALESCE(SUM(si.total_amount), 0) as total_invoiced_amount
       FROM sales_orders so
       LEFT JOIN sales_invoices si ON si.sales_order_id = so.id 
         AND si.is_deleted = false
         AND si.status != 'cancelled'
       WHERE so.id = $1 AND so.org_id = $2
       GROUP BY so.id`,
      [sales_order_id, orgId]
    );

    if (!soCheck.rows.length) {
      throw new Error("Sales Order not found");
    }

    const salesOrder = soCheck.rows[0];
    const originalTotalQty = Number(salesOrder.total_quantity) || 0;
    const invoicedSoFar = Number(salesOrder.total_invoiced_qty) || 0;
    const currentInvoiceQty = items.reduce((sum, item) => sum + Number(item.qty), 0);
    const remainingAfterInvoice = originalTotalQty - (invoicedSoFar + currentInvoiceQty);

    // 🔹 VALIDATION 1: Check if already fully invoiced
    if (invoicedSoFar >= originalTotalQty && originalTotalQty > 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Cannot create invoice. Sales Order is already fully invoiced. Total quantity: ${originalTotalQty} TONS, Already invoiced: ${invoicedSoFar} TONS`,
        data: {
          order_total_qty: originalTotalQty,
          already_invoiced_qty: invoicedSoFar,
          remaining_qty: 0,
          is_fully_invoiced: true
        }
      });
    }

    // 🔹 VALIDATION 2: Check if current invoice would exceed order quantity
    if (invoicedSoFar + currentInvoiceQty > originalTotalQty) {
      const maxAllowed = originalTotalQty - invoicedSoFar;
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Cannot invoice ${currentInvoiceQty} TONS. Only ${maxAllowed} TONS remaining to invoice.`,
        data: {
          order_total_qty: originalTotalQty,
          already_invoiced_qty: invoicedSoFar,
          requested_qty: currentInvoiceQty,
          max_allowed_qty: maxAllowed,
          remaining_qty: maxAllowed
        }
      });
    }

    // 🔹 VALIDATION 3: Check if trying to create another invoice when only partial is left
    // But if remaining is 0, already caught by validation 1
    if (remainingAfterInvoice === 0 && invoicedSoFar > 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Sales Order is already fully invoiced. No more quantity left to invoice.`,
        data: {
          order_total_qty: originalTotalQty,
          already_invoiced_qty: invoicedSoFar,
          remaining_qty: 0
        }
      });
    }

    // 🔹 GENERATE INVOICE NUMBER
    const invRes = await client.query(
      `SELECT COUNT(*) FROM sales_invoices WHERE org_id = $1`,
      [orgId]
    );
    const count = Number(invRes.rows[0].count) + 1;
    const invoiceNumber = `INV-${String(count).padStart(3, "0")}`;

    // 🔹 CALCULATE TOTALS
    let subtotal = 0;
    let totalDiscount = 0;
    let totalTax = 0;
    let totalAmount = 0;

    // 🔹 INSERT INVOICE HEADER with quantity tracking
    const invoiceInsert = await client.query(
      `INSERT INTO sales_invoices (
        sales_order_id, sales_order_number, invoice_number, org_id,
        customer_code, customer_name, customer_address, customer_gst,
        customer_email, customer_phone, customer_city, customer_state,
        invoice_date, due_date, amount, total_discount, total_tax,
        total_amount, advance_adjusted_amount, balance_payable_amount,
        total_quantity_invoiced, remaining_quantity, is_partial_invoice,
        weighment_data, notes, created_by, status
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,'pending')
      RETURNING id`,
      [
        sales_order_id, sales_order_number, invoiceNumber, orgId,
        customer_code, customer_name, customer_address, customer_gst,
        null, null, null, null,
        invoice_date || new Date(), due_date || null,
        0, 0, 0, 0,
        advance_adjusted_amount || 0, balance_payable_amount || 0,
        currentInvoiceQty, remainingAfterInvoice,
        remainingAfterInvoice > 0 ? true : false,  // is_partial_invoice
        JSON.stringify(weighment || {}), notes || "", userId
      ]
    );

    const invoiceId = invoiceInsert.rows[0].id;

    // 🔹 INSERT INVOICE ITEMS
    for (const item of items) {
      const qty = Number(item.qty) || 0;
      const rate = Number(item.rate) || 0;
      const gst = Number(item.gst) || 18;
      const discount = Number(item.discount) || 0;

      const amount = qty * rate;
      const discountAmount = Math.min(discount, amount);
      const taxable = Math.max(amount - discountAmount, 0);
      const gstAmount = (taxable * gst) / 100;
      const total = taxable + gstAmount;

      subtotal += amount;
      totalDiscount += discountAmount;
      totalTax += gstAmount;
      totalAmount += total;

      await client.query(
        `INSERT INTO sales_invoice_items (
          invoice_id, product_id, product_name, qty, uom_symbol,
          rate, amount, discount_amount, gst_percent, gst_amount, total_amount
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
        [invoiceId, item.product_id, item.product_name, qty, "TONS",
         rate, amount, discountAmount, gst, gstAmount, total]
      );
    }

    // 🔹 UPDATE INVOICE TOTALS
    await client.query(
      `UPDATE sales_invoices SET
        amount = $1, total_discount = $2, total_tax = $3, total_amount = $4
      WHERE id = $5`,
      [subtotal, totalDiscount, totalTax, totalAmount, invoiceId]
    );

    // 🔹 UPDATE SALES ORDER with invoiced quantity
    const newTotalInvoiced = invoicedSoFar + currentInvoiceQty;
    const isFullyInvoiced = remainingAfterInvoice === 0;
    
    await client.query(
      `UPDATE sales_orders 
       SET 
         total_invoiced_qty = $1,
         total_invoiced_amount = total_invoiced_amount + $2,
         remaining_qty = $3,
         fully_invoiced = $4,
         updated_at = NOW()
       WHERE id = $5`,
      [newTotalInvoiced, totalAmount, remainingAfterInvoice, isFullyInvoiced, sales_order_id]
    );

    // 🔹 RECORD WEIGHMENT HISTORY
    await client.query(
      `INSERT INTO sales_order_weighment_history (
        sales_order_id, sales_order_number, invoice_id, invoice_number,
        weighment_date, in_weight, out_weight, net_weight,
        quantity_invoiced, amount_invoiced,
        remaining_quantity_before, remaining_quantity_after,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)`,
      [
        sales_order_id, sales_order_number, invoiceId, invoiceNumber,
        new Date(), weighment.in_weight, weighment.out_weight, weighment.net_weight,
        currentInvoiceQty, totalAmount,
        originalTotalQty - invoicedSoFar, remainingAfterInvoice,
        userId
      ]
    );

    // 🔹 CREATE APPROVAL RECORD
    await client.query(
      `INSERT INTO invoice_approvals (invoice_id, user_id, status, comments)
       VALUES ($1, $2, 'pending', 'Awaiting Approval')`,
      [invoiceId, userId]
    );

    await client.query("COMMIT");

    // Send success message with partial invoice info
    let message = "Main Invoice created and sent for approval!";
    if (remainingAfterInvoice > 0) {
      message = `✅ Invoice created for ${currentInvoiceQty} TONS. Remaining: ${remainingAfterInvoice} TONS left to invoice.`;
    } else if (remainingAfterInvoice === 0) {
      message = `✅ Final invoice created! Full order quantity (${originalTotalQty} TONS) has been invoiced.`;
    }

    return res.json({
      success: true,
      message,
      data: {
        invoiceId,
        invoiceNumber,
        invoicedQuantity: currentInvoiceQty,
        remainingQuantity: remainingAfterInvoice,
        isFullyInvoiced: remainingAfterInvoice === 0,
        totalOrderQty: originalTotalQty,
        alreadyInvoicedQty: newTotalInvoiced
      }
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CREATE MAIN INVOICE ERROR:", err);
    return res.status(500).json({ 
      success: false,
      message: err.message || "Failed to create invoice",
      error: err.toString()
    });
  } finally {
    client.release();
  }
};

// 🔹 NEW: Get invoice history for a Sales Order
const getSalesOrderInvoiceHistory = async (req, res) => {
  try {
    const { salesOrderId } = req.params;
    const orgId = req.session?.user?.orgId;

    const invoices = await pool.query(
      `SELECT 
        si.id,
        si.invoice_number,
        si.invoice_date,
        si.total_quantity_invoiced,
        si.total_amount,
        si.is_partial_invoice,
        si.remaining_quantity,
        si.status,
        si.created_at,
        json_agg(
          json_build_object(
            'product_name', sii.product_name,
            'qty', sii.qty,
            'rate', sii.rate,
            'total', sii.total_amount
          )
        ) as items
      FROM sales_invoices si
      LEFT JOIN sales_invoice_items sii ON sii.invoice_id = si.id
      WHERE si.sales_order_id = $1 
        AND si.org_id = $2
        AND si.is_deleted = false
      GROUP BY si.id
      ORDER BY si.created_at ASC`,
      [salesOrderId, orgId]
    );

    // Get SO details with summary
    const soSummary = await pool.query(
      `SELECT 
        so.so_number,
        so.total_quantity as order_quantity,
        so.total_invoiced_qty,
        so.total_invoiced_amount,
        so.remaining_qty,
        so.fully_invoiced,
        so.status
      FROM sales_orders so
      WHERE so.id = $1 AND so.org_id = $2`,
      [salesOrderId, orgId]
    );

    return res.json({
      success: true,
      data: {
        sales_order: soSummary.rows[0] || null,
        invoices: invoices.rows,
        total_invoices: invoices.rows.length
      }
    });
  } catch (err) {
    console.error("GET INVOICE HISTORY ERROR:", err);
    return res.status(500).json({ message: "Failed to fetch invoice history" });
  }
};

// ==============================================
// CREATE PROFORMA INVOICE
// ==============================================
const createProformaInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      sales_order_id,
      sales_order_number,
      customer_code,
      customer_name,
      customer_address,
      customer_gst,
      customer_email,
      customer_phone,
      customer_city,
      customer_state,
      customer_pincode,
      contact_person,
      shipping_address,
      invoice_date,
      due_date,
      notes,
      items,
      weighment,
    } = req.body;

    if (!customer_code || !items?.length) {
      return res.status(400).json({
        message: "Customer code and items required",
      });
    }

    await client.query("BEGIN");

    // GENERATE PROFORMA NUMBER
    const invRes = await client.query(
      `SELECT COUNT(*) FROM proforma_invoices WHERE org_id = $1`,
      [orgId]
    );

    const count = Number(invRes.rows[0].count) + 1;
    const proformaNumber = `PI-${String(count).padStart(3, "0")}`;

    let subtotal = 0;
    let totalTax = 0;
    let totalAmount = 0;

    // GET ADVANCE AMOUNT from SO
    const soRes = await client.query(
      `SELECT advance_payment_amount, with_advance_payment FROM sales_orders WHERE id = $1`,
      [sales_order_id]
    );
    const advanceAmount = soRes.rows[0]?.advance_payment_amount || 0;

    // INSERT PROFORMA
    const proformaInsert = await client.query(
      `INSERT INTO proforma_invoices (
        proforma_number, 
        sales_order_id, 
        sales_order_number, 
        org_id,
        customer_code, 
        customer_name, 
        customer_address, 
        customer_gst,
        customer_email,
        customer_phone,
        customer_city,
        customer_state,
        customer_pincode,
        contact_person,
        shipping_address,
        invoice_date, 
        due_date, 
        advance_amount, 
        notes,
        weighment_data, 
        created_by, 
        status
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,'pending')
      RETURNING id`,
      [
        proformaNumber, 
        sales_order_id, 
        sales_order_number, 
        orgId,
        customer_code, 
        customer_name, 
        customer_address, 
        customer_gst,
        customer_email || null,
        customer_phone || null,
        customer_city || null,
        customer_state || null,
        customer_pincode || null,
        contact_person || null,
        shipping_address || null,
        invoice_date || new Date(), 
        due_date || null,
        advanceAmount, 
        notes || "",
        JSON.stringify(weighment || {}), 
        userId
      ]
    );

    const proformaId = proformaInsert.rows[0].id;

    // INSERT ITEMS
    for (const item of items) {
      const qty = Number(item.qty) || 0;
      const rate = Number(item.rate) || 0;
      const gst = Number(item.gst) || 18;
      const discount = Number(item.discount) || 0;

      const amount = qty * rate;
      const discountAmount = Math.min(discount, amount);
      const taxable = Math.max(amount - discountAmount, 0);
      const gstAmount = (taxable * gst) / 100;
      const total = taxable + gstAmount;

      subtotal += amount;
      totalTax += gstAmount;
      totalAmount += total;

      await client.query(
        `INSERT INTO proforma_invoice_items (
          proforma_id, 
          product_id, 
          product_name, 
          qty, 
          uom_symbol,
          rate, 
          amount, 
          discount_amount, 
          gst_percent, 
          gst_amount, 
          total_amount
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
        [
          proformaId, 
          item.product_id, 
          item.product_name, 
          qty, 
          item.uom_symbol || "TONS",
          rate, 
          amount, 
          discountAmount, 
          gst, 
          gstAmount, 
          total
        ]
      );
    }

    // UPDATE TOTALS
    await client.query(
      `UPDATE proforma_invoices SET
        amount = $1, 
        total_tax = $2, 
        total_amount = $3
      WHERE id = $4`,
      [subtotal, totalTax, totalAmount, proformaId]
    );

    await client.query("COMMIT");

    // SEND PROFORMA EMAIL
    sendProformaEmail(proformaId, client, orgId, userId, customer_email, customer_name).catch(err => {
      console.error("Proforma email error:", err);
    });

    return res.json({
      success: true,
      message: "Proforma Invoice created and sent for approval",
      proformaId,
      proformaNumber,
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CREATE PROFORMA INVOICE ERROR:", err);
    return res.status(500).json({ 
      message: err.message || "Failed to create proforma",
      error: err.toString()
    });
  } finally {
    client.release();
  }
};

// ==============================================
// HELPER: Send Invoice Email
// ==============================================
async function sendInvoiceEmail(invoiceId, client, orgId, userId, customerEmail, customerName) {
  try {
    // Fetch invoice details
    const invoiceRes = await client.query(
      `SELECT * FROM sales_invoices WHERE id = $1`,
      [invoiceId]
    );
    const invoice = invoiceRes.rows[0];

    const email = customerEmail || invoice?.customer_email;
    const name = customerName || invoice?.customer_name;

    if (!email) {
      console.log("No customer email found for invoice:", invoiceId);
      return;
    }

    // Fetch company details
    const companyRes = await client.query(
      `SELECT org_name, address, gstin FROM organizations WHERE id = $1`,
      [orgId]
    );
    const company = companyRes.rows[0] || {};

    // Send email
    // const nodemailer = require("nodemailer");
    // const transporter = nodemailer.createTransport({
    //   service: "gmail",
    //   auth: {
    //     user: process.env.MAIL_USER,
    //     pass: process.env.MAIL_PASS,
    //   },
    // });
     const transporter = transporter(); 

    const emailHtml = `
      <div style="font-family: Arial, sans-serif; padding: 20px;">
        <h2>Invoice Generated: ${invoice.invoice_number}</h2>
        <p>Dear ${name},</p>
        <p>Your invoice has been generated and is pending approval.</p>
        <p><strong>Total Amount:</strong> ₹${Number(invoice.total_amount).toFixed(2)}</p>
        <p><strong>Invoice Number:</strong> ${invoice.invoice_number}</p>
        <p><strong>Invoice Date:</strong> ${new Date(invoice.invoice_date).toLocaleDateString()}</p>
        ${invoice.due_date ? `<p><strong>Due Date:</strong> ${new Date(invoice.due_date).toLocaleDateString()}</p>` : ''}
        <p>You will receive the final invoice once approved.</p>
        <hr />
        <p>Regards,<br/>${company.org_name || "Accounts Team"}</p>
      </div>
    `;

    await transporter.sendMail({
      from: `"${company.org_name || "ERP System"}" <${process.env.MAIL_USER}>`,
      to: email,
      subject: `Invoice ${invoice.invoice_number} Generated`,
      html: emailHtml,
    });
    
    console.log("Email sent to:", email);
  } catch (err) {
    console.error("Email send error:", err);
  }
}

// ==============================================
// HELPER: Send Proforma Email
// ==============================================
async function sendProformaEmail(proformaId, client, orgId, userId, customerEmail, customerName) {
  try {
    const proformaRes = await client.query(
      `SELECT * FROM proforma_invoices WHERE id = $1`,
      [proformaId]
    );
    const proforma = proformaRes.rows[0];

    const email = customerEmail || proforma?.customer_email;
    const name = customerName || proforma?.customer_name;

    if (!email) {
      console.log("No customer email found for proforma:", proformaId);
      return;
    }

    const companyRes = await client.query(
      `SELECT org_name FROM organizations WHERE id = $1`,
      [orgId]
    );
    const company = companyRes.rows[0] || {};

    // const nodemailer = require("nodemailer");
    // const transporter = nodemailer.createTransport({
    //   service: "gmail",
    //   auth: {
    //     user: process.env.MAIL_USER,
    //     pass: process.env.MAIL_PASS,
    //   },
    // });
      const transporter = transporter(); 

    const emailHtml = `
      <div style="font-family: Arial, sans-serif; padding: 20px;">
        <h2>Proforma Invoice: ${proforma.proforma_number}</h2>
        <p>Dear ${name},</p>
        <p>Please find attached your Proforma Invoice.</p>
        <p><strong>Advance Amount:</strong> ₹${Number(proforma.advance_amount || 0).toFixed(2)}</p>
        <p><strong>Total Amount:</strong> ₹${Number(proforma.total_amount || 0).toFixed(2)}</p>
        <p><strong>Proforma Number:</strong> ${proforma.proforma_number}</p>
        <p><strong>Date:</strong> ${new Date(proforma.invoice_date).toLocaleDateString()}</p>
        <hr />
        <p>Regards,<br/>${company.org_name || "Sales Team"}</p>
      </div>
    `;

    await transporter.sendMail({
      from: `"${company.org_name || "ERP System"}" <${process.env.MAIL_USER}>`,
      to: email,
      subject: `Proforma Invoice ${proforma.proforma_number}`,
      html: emailHtml,
    });
    
    console.log("Proforma email sent to:", email);
  } catch (err) {
    console.error("Proforma email error:", err);
  }
}

// ==============================================
// HELPER: Send Invoice Email
// ==============================================
// async function sendInvoiceEmail(invoiceId, client, orgId, userId) {
//   try {
//     // Fetch invoice details
//     const invoiceRes = await client.query(
//       `SELECT * FROM sales_invoices WHERE id = $1`,
//       [invoiceId]
//     );
//     const invoice = invoiceRes.rows[0];

//     // Fetch customer email
//     const customerRes = await client.query(
//       `SELECT email FROM customers WHERE customer_code = $1`,
//       [invoice.customer_code]
//     );
//     const customerEmail = customerRes.rows[0]?.email;

//     if (!customerEmail) return;

//     // Fetch company details
//     const companyRes = await client.query(
//       `SELECT org_name, address, gstin FROM organizations WHERE id = $1`,
//       [orgId]
//     );
//     const company = companyRes.rows[0] || {};

//     // Send email
//     const nodemailer = require("nodemailer");
//     const transporter = nodemailer.createTransport({
//       service: "gmail",
//       auth: {
//         user: process.env.MAIL_USER,
//         pass: process.env.MAIL_PASS,
//       },
//     });

//     const emailHtml = `
//       <div style="font-family: Arial, sans-serif; padding: 20px;">
//         <h2>Invoice Generated: ${invoice.invoice_number}</h2>
//         <p>Dear ${invoice.customer_name},</p>
//         <p>Your invoice has been generated and is pending approval.</p>
//         <p><strong>Total Amount:</strong> ₹${Number(invoice.total_amount).toFixed(2)}</p>
//         <p>You will receive the final invoice once approved.</p>
//         <p>Regards,<br/>${company.org_name || "Accounts Team"}</p>
//       </div>
//     `;

//     await transporter.sendMail({
//       from: `"${company.org_name || "ERP System"}" <${process.env.MAIL_USER}>`,
//       to: customerEmail,
//       subject: `Invoice ${invoice.invoice_number} Generated`,
//       html: emailHtml,
//     });
//   } catch (err) {
//     console.error("Email send error:", err);
//   }
// }

// ==============================================
// HELPER: Send Proforma Email
// ==============================================nodemailer
// async function sendProformaEmail(proformaId, client, orgId, userId) {
//   try {
//     const proformaRes = await client.query(
//       `SELECT * FROM proforma_invoices WHERE id = $1`,
//       [proformaId]
//     );
//     const proforma = proformaRes.rows[0];

//     const customerRes = await client.query(
//       `SELECT email FROM customers WHERE customer_code = $1`,
//       [proforma.customer_code]
//     );
//     const customerEmail = customerRes.rows[0]?.email;

//     if (!customerEmail) return;

//     const companyRes = await client.query(
//       `SELECT org_name FROM organizations WHERE id = $1`,
//       [orgId]
//     );
//     const company = companyRes.rows[0] || {};

//     const nodemailer = require("nodemailer");
//     const transporter = nodemailer.createTransport({
//       service: "gmail",
//       auth: {
//         user: process.env.MAIL_USER,
//         pass: process.env.MAIL_PASS,
//       },
//     });

//     const emailHtml = `
//       <div style="font-family: Arial, sans-serif; padding: 20px;">
//         <h2>Proforma Invoice: ${proforma.proforma_number}</h2>
//         <p>Dear ${proforma.customer_name},</p>
//         <p>Please find attached your Proforma Invoice.</p>
//         <p><strong>Advance Amount:</strong> ₹${Number(proforma.advance_amount || 0).toFixed(2)}</p>
//         <p><strong>Total Amount:</strong> ₹${Number(proforma.total_amount || 0).toFixed(2)}</p>
//         <p>Regards,<br/>${company.org_name || "Sales Team"}</p>
//       </div>
//     `;

//     await transporter.sendMail({
//       from: `"${company.org_name || "ERP System"}" <${process.env.MAIL_USER}>`,
//       to: customerEmail,
//       subject: `Proforma Invoice ${proforma.proforma_number}`,
//       html: emailHtml,
//     });
//   } catch (err) {
//     console.error("Proforma email error:", err);
//   }
// }

const updateVehicleWeighmentStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status, invoiceCreated } = req.body;
    const orgId = req.session?.user?.orgId;

    const query = `
      UPDATE commercial_vehicles
      SET 
        weighment_status = COALESCE($1, weighment_status),
        invoice_created = COALESCE($2, invoice_created),
        updated_at = NOW(),
        updated_by = $3
      WHERE id = $4 AND org_id = $5
      RETURNING id
    `;

    const { rows } = await pool.query(query, [
      status || 'INVOICED',
      invoiceCreated || true,
      req.session?.user?.id,
      id,
      orgId
    ]);

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

    return res.status(200).json({
      success: true,
      message: "Vehicle weighment status updated"
    });
  } catch (error) {
    console.error("Update Status Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update status"
    });
  }
};








// ==============================================
// CANCEL INVOICE (Soft Delete with Email)
// ==============================================
const cancelInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const { cancellation_reason, cancellation_notes } = req.body;
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const userName = req.session?.user?.name || req.session?.user?.first_name;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // Check if invoice exists and get details
    const invoiceCheck = await client.query(
      `SELECT 
        si.*,
        so.so_number as so_number,
        so.id as so_id,
        so.total_invoiced_qty,
        so.total_invoiced_amount,
        so.remaining_qty
      FROM sales_invoices si
      LEFT JOIN sales_orders so ON so.id = si.sales_order_id
      WHERE si.id = $1 AND si.org_id = $2 AND si.is_deleted = false`,
      [id, orgId]
    );

    if (!invoiceCheck.rows.length) {
      throw new Error("Invoice not found");
    }

    const invoice = invoiceCheck.rows[0];

    // Check if already cancelled
    if (invoice.is_cancelled) {
      throw new Error("Invoice is already cancelled");
    }

    // Check if invoice is approved - cannot cancel approved invoices
    if (invoice.status === 'approved') {
      throw new Error("Cannot cancel an approved invoice. Please contact admin.");
    }

    // Update invoice as cancelled
    await client.query(
      `UPDATE sales_invoices 
       SET 
         is_cancelled = true,
         cancelled_at = NOW(),
         cancelled_by = $1,
         cancellation_reason = $2,
         cancellation_notes = $3,
         status = 'cancelled',
         updated_at = NOW()
       WHERE id = $4`,
      [userId, cancellation_reason || 'user_requested', cancellation_notes || null, id]
    );

    // Update Sales Order - revert invoiced quantities
    if (invoice.sales_order_id) {
      const invoicedQty = Number(invoice.total_quantity_invoiced) || 0;
      const invoicedAmount = Number(invoice.total_amount) || 0;
      
      // Get current SO totals
      const soCurrent = await client.query(
        `SELECT total_invoiced_qty, total_invoiced_amount, remaining_qty, total_quantity
         FROM sales_orders WHERE id = $1`,
        [invoice.sales_order_id]
      );
      
      if (soCurrent.rows.length) {
        const current = soCurrent.rows[0];
        const newInvoicedQty = Math.max(0, (current.total_invoiced_qty || 0) - invoicedQty);
        const newInvoicedAmount = Math.max(0, (current.total_invoiced_amount || 0) - invoicedAmount);
        const newRemainingQty = (current.total_quantity || 0) - newInvoicedQty;
        const isFullyInvoiced = newRemainingQty === 0;
        
        await client.query(
          `UPDATE sales_orders 
           SET 
             total_invoiced_qty = $1,
             total_invoiced_amount = $2,
             remaining_qty = $3,
             fully_invoiced = $4,
             updated_at = NOW()
           WHERE id = $5`,
          [newInvoicedQty, newInvoicedAmount, newRemainingQty, isFullyInvoiced, invoice.sales_order_id]
        );
      }
    }

    // Log cancellation
    await client.query(
      `INSERT INTO invoice_cancellation_log (
        invoice_id, invoice_number, invoice_type, 
        cancelled_by, cancelled_by_name, cancellation_reason, 
        cancellation_notes, email_sent
      )
      VALUES ($1, $2, 'main', $3, $4, $5, $6, false)`,
      [id, invoice.invoice_number, userId, userName, cancellation_reason, cancellation_notes]
    );

    await client.query("COMMIT");

    // Send cancellation email (async, don't await)
    sendCancellationEmail(invoice, cancellation_reason, cancellation_notes, orgId).catch(err => {
      console.error("Cancellation email error:", err);
    });

    return res.json({
      success: true,
      message: "Invoice cancelled successfully. Customer has been notified.",
      invoiceId: id,
      invoiceNumber: invoice.invoice_number
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CANCEL INVOICE ERROR:", err);
    return res.status(500).json({ 
      success: false,
      message: err.message || "Failed to cancel invoice" 
    });
  } finally {
    client.release();
  }
};

// ==============================================
// CANCEL PROFORMA INVOICE
// ==============================================
const cancelProformaInvoice = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const { cancellation_reason, cancellation_notes } = req.body;
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const userName = req.session?.user?.name || req.session?.user?.first_name;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    await client.query("BEGIN");

    const proformaCheck = await client.query(
      `SELECT * FROM proforma_invoices 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

    if (!proformaCheck.rows.length) {
      throw new Error("Proforma invoice not found");
    }

    const proforma = proformaCheck.rows[0];

    if (proforma.is_cancelled) {
      throw new Error("Proforma invoice is already cancelled");
    }

    await client.query(
      `UPDATE proforma_invoices 
       SET 
         is_cancelled = true,
         cancelled_at = NOW(),
         cancelled_by = $1,
         cancellation_reason = $2,
         cancellation_notes = $3,
         status = 'cancelled',
         updated_at = NOW()
       WHERE id = $4`,
      [userId, cancellation_reason || 'user_requested', cancellation_notes || null, id]
    );

    await client.query(
      `INSERT INTO invoice_cancellation_log (
        invoice_id, invoice_number, invoice_type, 
        cancelled_by, cancelled_by_name, cancellation_reason, 
        cancellation_notes, email_sent
      )
      VALUES ($1, $2, 'proforma', $3, $4, $5, $6, false)`,
      [id, proforma.proforma_number, userId, userName, cancellation_reason, cancellation_notes]
    );

    await client.query("COMMIT");

    sendCancellationEmail(proforma, cancellation_reason, cancellation_notes, orgId, 'proforma').catch(err => {
      console.error("Cancellation email error:", err);
    });

    return res.json({
      success: true,
      message: "Proforma Invoice cancelled successfully. Customer has been notified.",
      proformaId: id
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CANCEL PROFORMA ERROR:", err);
    return res.status(500).json({ 
      success: false,
      message: err.message || "Failed to cancel proforma invoice" 
    });
  } finally {
    client.release();
  }
};

// ==============================================
// SEND CANCELLATION EMAIL
// ==============================================
async function sendCancellationEmail(invoice, reason, notes, orgId, type = 'main') {
  try {
    const customerEmail = invoice.customer_email;
    if (!customerEmail) {
      console.log("No customer email found for cancellation notification");
      return;
    }

    // Fetch company details
    const companyRes = await pool.query(
      `SELECT org_name, address, gstin, phone, email FROM organizations WHERE id = $1`,
      [orgId]
    );
    const company = companyRes.rows[0] || {};

    const invoiceNumber = type === 'main' ? invoice.invoice_number : invoice.proforma_number;
    const invoiceType = type === 'main' ? 'Tax Invoice' : 'Proforma Invoice';

    const emailHtml = `
      <div style="font-family: 'Segoe UI', Arial, sans-serif; max-width: 600px; margin: 0 auto; padding: 20px;">
        <div style="background: linear-gradient(135deg, #dc2626, #991b1b); padding: 20px; text-align: center; color: white; border-radius: 10px 10px 0 0;">
          <h2 style="margin: 0;">Invoice Cancelled</h2>
        </div>
        
        <div style="background: #ffffff; padding: 25px; border: 1px solid #e5e7eb; border-top: none; border-radius: 0 0 10px 10px;">
          <p style="font-size: 16px; margin-bottom: 20px;">Dear <strong>${invoice.customer_name}</strong>,</p>
          
          <div style="background: #fef2f2; border-left: 4px solid #dc2626; padding: 15px; margin-bottom: 20px;">
            <p style="margin: 0; color: #991b1b;">
              <strong>Your ${invoiceType} has been cancelled.</strong>
            </p>
          </div>
          
          <div style="background: #f9fafb; padding: 15px; border-radius: 8px; margin-bottom: 20px;">
            <table style="width: 100%; font-size: 14px;">
              <tr>
                <td style="padding: 6px 0; color: #6b7280;">Invoice Number:</td>
                <td style="padding: 6px 0; font-weight: 600;">${invoiceNumber}</td>
              </tr>
              <tr>
                <td style="padding: 6px 0; color: #6b7280;">Invoice Date:</td>
                <td style="padding: 6px 0;">${new Date(invoice.invoice_date).toLocaleDateString()}</td>
              </tr>
              <tr>
                <td style="padding: 6px 0; color: #6b7280;">Amount:</td>
                <td style="padding: 6px 0; font-weight: 600;">₹${Number(invoice.total_amount || 0).toFixed(2)}</td>
              </tr>
              ${reason ? `
              <tr>
                <td style="padding: 6px 0; color: #6b7280;">Cancellation Reason:</td>
                <td style="padding: 6px 0;">${reason}</td>
              </tr>
              ` : ''}
              ${notes ? `
              <tr>
                <td style="padding: 6px 0; color: #6b7280;">Additional Notes:</td>
                <td style="padding: 6px 0;">${notes}</td>
              </tr>
              ` : ''}
            </table>
          </div>
          
          <div style="background: #eff6ff; padding: 15px; border-radius: 8px; margin-bottom: 20px;">
            <p style="margin: 0; font-size: 14px;">
              <strong>What happens next?</strong><br/>
              • No payment is required against this cancelled invoice<br/>
              • If you have already made a payment, please contact our accounts team<br/>
              • A new invoice will be issued if needed
            </p>
          </div>
          
          <p style="margin-top: 20px;">
            For any questions or concerns, please contact our support team.
          </p>
          
          <hr style="margin: 20px 0; border: none; border-top: 1px solid #e5e7eb;" />
          
          <p style="font-size: 12px; color: #6b7280; text-align: center;">
            This is an automated notification. Please do not reply.<br/>
            © ${new Date().getFullYear()} ${company.org_name || 'ERP System'}. All rights reserved.
          </p>
        </div>
      </div>
    `;

    // const nodemailer = require("nodemailer");
    // const transporter = nodemailer.createTransport({
    //   service: "gmail",
    //   auth: {
    //     user: process.env.MAIL_USER,
    //     pass: process.env.MAIL_PASS,
    //   },
    // });
     const transporter = transporter(); 

    await transporter.sendMail({
      from: `"${company.org_name || "Accounts Team"}" <${process.env.MAIL_USER}>`,
      to: customerEmail,
      subject: `${invoiceType} ${invoiceNumber} has been Cancelled`,
      html: emailHtml,
    });
    
    // Update log that email was sent
    await pool.query(
      `UPDATE invoice_cancellation_log 
       SET email_sent = true, email_sent_at = NOW() 
       WHERE invoice_id = $1 AND invoice_type = $2`,
      [invoice.id, type]
    );
    
    console.log("Cancellation email sent to:", customerEmail);
  } catch (err) {
    console.error("Cancellation email send error:", err);
  }
}




const generateProformaFromSalesOrder = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { 
      sales_order_id, 
      advance_amount_percent, 
      advance_amount_fixed,
      notes,
      invoice_details
    } = req.body;
    
    const userId = req.session?.user?.id;
    const orgId = req.session?.user?.orgId;
    const userName = req.session?.user?.name || "-";

    if (!userId || !orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (!sales_order_id) {
      return res.status(400).json({ success: false, message: "Sales Order ID is required" });
    }

    await client.query("BEGIN");

    // 1. Check if proforma already exists
    const existingProforma = await client.query(
      `
      SELECT id, proforma_number, status, advance_amount, created_at
      FROM proforma_invoices
      WHERE sales_order_id = $1 
        AND is_deleted = false
        AND is_cancelled = false
      ORDER BY created_at DESC
      LIMIT 1
      `,
      [sales_order_id]
    );

    if (existingProforma.rows.length > 0) {
      const lastProforma = existingProforma.rows[0];
      
      if (lastProforma.status?.toLowerCase() === 'paid') {
        await client.query("ROLLBACK");
        return res.status(400).json({
          success: false,
          message: "Advance payment already made for this Sales Order. Cannot create another Proforma Invoice.",
          data: { existing_proforma: lastProforma }
        });
      }
      
      if (lastProforma.status?.toLowerCase() === 'pending') {
        await client.query("ROLLBACK");
        return res.status(400).json({
          success: false,
          message: "A Proforma Invoice is already pending payment. Please complete the payment first.",
          data: { existing_proforma: lastProforma }
        });
      }
    }

    // 2. Fetch Sales Order details
    const orderRes = await client.query(
      `
      SELECT 
        so.*,
        c.email as customer_email,
        c.phone as customer_phone,
        c.gst as customer_gst,
        c.billing_address as customer_address,
        c.shipping_address,
        c.bill_city as customer_city,
        c.bill_state as customer_state,
        c.bill_pin_code as customer_pincode,
        c.contact_person
      FROM sales_orders so
      LEFT JOIN customers c ON c.customer_code = so.customer_id
      WHERE so.id = $1 AND so.org_id = $2
      `,
      [sales_order_id, orgId]
    );

    if (!orderRes.rows.length) {
      throw new Error("Sales Order not found");
    }

    const order = orderRes.rows[0];

    // 3. Get items and calculate totals
    let items = [];
    let subtotal = 0;
    let totalTax = 0;
    let totalAmount = 0;

    if (invoice_details?.items && invoice_details.items.length > 0) {
      // Use items from frontend (weighed items)
      items = invoice_details.items.map(item => ({
        product_id: item.product_id,
        product_name: item.product_name,
        qty: item.qty,
        rate: item.rate,
        uom_symbol: item.uom_symbol || "TONS",
        tax_percent: item.gst || 18
      }));
      
      subtotal = invoice_details.subtotal || 0;
      totalTax = invoice_details.total_gst || 0;
      totalAmount = invoice_details.grand_total || 0;
    } else {
      // Fallback: fetch from sales_order_items
      const itemsRes = await client.query(
        `
        SELECT 
          product_id,
          product_name,
          qty,
          rate,
          uom_symbol,
          amount,
          gst_amount,
          tax_percent
        FROM sales_order_items
        WHERE sales_order_id = $1
        `,
        [sales_order_id]
      );
      items = itemsRes.rows;
      
      for (const item of items) {
        subtotal += Number(item.amount || 0);
        totalTax += Number(item.gst_amount || 0);
      }
      totalAmount = subtotal + totalTax;
    }

    // 4. Calculate advance amount
    let advanceAmount = 0;
    let advancePercent = 0;

    if (invoice_details?.advance_amount && invoice_details.advance_amount > 0) {
      advanceAmount = Number(invoice_details.advance_amount);
      advancePercent = (advanceAmount / totalAmount) * 100;
    } else if (advance_amount_fixed && advance_amount_fixed > 0) {
      advanceAmount = Number(advance_amount_fixed);
      advancePercent = (advanceAmount / totalAmount) * 100;
    } else if (advance_amount_percent && advance_amount_percent > 0) {
      advancePercent = Number(advance_amount_percent);
      advanceAmount = (totalAmount * advancePercent) / 100;
    } else {
      // Default to order's advance settings
      advancePercent = Number(order.advance_payment_percent || 0);
      advanceAmount = Number(order.advance_payment_amount || 0);
    }

    // Validate advance amount
    if (advanceAmount > totalAmount) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Advance amount (₹${advanceAmount}) cannot exceed total amount (₹${totalAmount})`
      });
    }

    // 5. Generate Proforma Number
    const proformaNumber = `PI-${Date.now()}-${Math.floor(Math.random() * 1000)}`;

    // 6. Insert Proforma Invoice
    const insertResult = await client.query(
      `
      INSERT INTO proforma_invoices (
        proforma_number,
        sales_order_id,
        sales_order_number,
        customer_code,
        customer_name,
        customer_email,
        customer_phone,
        customer_address,
        customer_gst,
        customer_city,
        customer_state,
        customer_pincode,
        contact_person,
        shipping_address,
        advance_percent,
        advance_amount,
        amount,
        total_tax,
        total_amount,
        invoice_date,
        due_date,
        notes,
        weighment_data,
        status,
        created_by,
        org_id,
        proforma_date,
        created_at
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, 'pending', $24, $25, CURRENT_DATE, NOW())
      RETURNING id, proforma_number
      `,
      [
        proformaNumber,
        order.id,
        order.so_number,
        order.customer_id,
        invoice_details?.customer_name || order.customer_name,
        invoice_details?.customer_email || order.customer_email || order.customer_email,
        invoice_details?.customer_phone || order.customer_phone || order.customer_phone,
        invoice_details?.customer_address || order.customer_address || order.billing_address,
        invoice_details?.customer_gst || order.customer_gst || order.customer_gst,
        invoice_details?.customer_city || order.customer_city || order.bill_city,
        invoice_details?.customer_state || order.customer_state || order.bill_state,
        invoice_details?.customer_pincode || order.customer_pincode || order.bill_pin_code,
        invoice_details?.contact_person || order.contact_person,
        invoice_details?.shipping_address || order.shipping_address,
        advancePercent,
        advanceAmount,
        subtotal,
        totalTax,
        totalAmount,
        invoice_details?.invoice_date || new Date(),
        invoice_details?.due_date || null,
        notes || req.body.notes || "Proforma Invoice generated from weighment",
        JSON.stringify(invoice_details?.weighment || {}),
        userId,
        orgId
      ]
    );

    const proformaId = insertResult.rows[0].id;

    // 7. Insert Proforma Items
    for (const item of items) {
      const qty = Number(item.qty) || 0;
      const rate = Number(item.rate) || 0;
      const gst = Number(item.tax_percent || item.gst || 18);
      const amount = qty * rate;
      const gstAmount = (amount * gst) / 100;
      const total = amount + gstAmount;

      await client.query(
        `
        INSERT INTO proforma_invoice_items (
          proforma_id,
          product_id,
          product_name,
          qty,
          uom_symbol,
          rate,
          amount,
          discount_amount,
          gst_percent,
          gst_amount,
          total_amount
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
        `,
        [
          proformaId,
          item.product_id,
          item.product_name,
          qty,
          item.uom_symbol || "TONS",
          rate,
          amount,
          0,
          gst,
          gstAmount,
          total
        ]
      );
    }

    // 8. Generate PDF
    const companyRes = await client.query(
      `
      SELECT org_name, address, gst_number, cin_number, phone, email, bank_details,
             bank_name, account_number, ifsc_code, branch
      FROM organizations
      WHERE id = $1
      `,
      [orgId]
    );
    const company = companyRes.rows[0] || {};

    const generateProformaPDF = require("../../services/pdf/generateProformaPDF");
    
    const pdfBuffer = await generateProformaPDF(
      {
        ...order,
        proforma_number: proformaNumber,
        advance_amount: advanceAmount,
        advance_percent: advancePercent,
         so_number: order.so_number,
    po_reference: order.po_reference || "",
    delivery_date: order.delivery_date,
        prepared_by: userName,
        approved_by: null,
        customer_email: invoice_details?.customer_email || order.customer_email,
        customer_name: invoice_details?.customer_name || order.customer_name,
        invoice_date: invoice_details?.invoice_date || new Date(),
        due_date: invoice_details?.due_date || null,
        subtotal: subtotal,
        total_tax: totalTax,
        total_amount: totalAmount,
        balance_amount: totalAmount - advanceAmount,

          total_amount: totalAmount,                    // For {{gross}} in PDF
    advance_payment_percent: advancePercent,      // For {{advance_percent}}
    advance_payment_amount: advanceAmount,        // For {{advance_amount}}
    balance_amount: totalAmount - advanceAmount,  // For {{balance_amount}}

     prepared_by: userName,
    approved_by: null,
    
      },
      items.map(item => ({
        ...item,
        amount  : (Number(item.qty) * Number(item.rate)),
        total_amount: (Number(item.qty) * Number(item.rate)) + 
          ((Number(item.qty) * Number(item.rate) * Number(item.tax_percent || item.gst || 18)) / 100)
      })),
      company
    );

    // 9. Save PDF
    const uploadDir = path.join(process.cwd(), "uploads", "proforma");
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }
    
    const fileName = `${proformaNumber}.pdf`;
    const filePath = path.join(uploadDir, fileName);
    const relativePath = `uploads/proforma/${fileName}`;
    
    fs.writeFileSync(filePath, pdfBuffer);

    // 10. Update PDF path
    await client.query(
      `
      UPDATE proforma_invoices
      SET pdf_path = $1
      WHERE id = $2
      `,
      [relativePath, proformaId]
    );

    // 11. Send email notification
    const customerEmail = invoice_details?.customer_email || order.customer_email;
    if (customerEmail) {
      const transporter = transporter();
      try {
        await transporter.sendMail({
          from: `"Sales Team" <${process.env.MAIL_USER}>`,
          to: customerEmail,
          subject: `Proforma Invoice ${proformaNumber}`,
          html: `
            <div style="font-family: Arial, sans-serif; max-width: 600px;">
              <h3 style="color: #2563eb;">Proforma Invoice Generated</h3>
              <p>Dear <strong>${invoice_details?.customer_name || order.customer_name}</strong>,</p>
              <p>Please find attached the Proforma Invoice <strong>${proformaNumber}</strong>.</p>
              <div style="background: #f3f4f6; padding: 15px; border-radius: 8px; margin: 15px 0;">
                <p><strong>Invoice Total:</strong> ₹${totalAmount.toFixed(2)}</p>
                <p><strong>Advance Amount to Pay:</strong> ₹${advanceAmount.toFixed(2)}</p>
                <p><strong>Balance Amount:</strong> ₹${(totalAmount - advanceAmount).toFixed(2)}</p>
              </div>
              <p>Please complete the advance payment to proceed with the order.</p>
              <br/>
              <p>Regards,<br/>Sales Team</p>
            </div>
          `,
          attachments: [{ filename: fileName, content: pdfBuffer }]
        });
      } catch (mailErr) {
        console.error("Email sending failed:", mailErr.message);
      }
    }

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Proforma Invoice generated successfully",
      data: {
        id: proformaId,
        proforma_number: proformaNumber,
        advance_amount: advanceAmount,
        advance_percent: advancePercent,
        total_amount: totalAmount,
        balance_amount: totalAmount - advanceAmount,
        pdf_path: relativePath
      }
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Generate Proforma Error:", err);
    return res.status(500).json({
      success: false,
      message: err.message || "Failed to generate Proforma Invoice"
    });
  } finally {
    client.release();
  }
};

// GET /api/proforma/check/:salesOrderId
const checkProformaStatus = async (req, res) => {
  try {
    const { salesOrderId } = req.params;
    const orgId = req.session?.user?.orgId;
    
    const result = await pool.query(
      `
      SELECT id, proforma_number, status, advance_amount, created_at
      FROM proforma_invoices
      WHERE sales_order_id = $1 AND org_id = $2 AND is_deleted = false
      ORDER BY created_at DESC
      LIMIT 1
      `,
      [salesOrderId, orgId]
    );
    
    return res.json({
      success: true,
      data: result.rows[0] || null
    });
  } catch (err) {
    return res.status(500).json({ success: false, message: err.message });
  }
};


const getProformaById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session?.user?.orgId;

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

    const result = await pool.query(
      `
      SELECT 
        pi.*,
        so.so_number,
        so.total_amount as order_total
      FROM proforma_invoices pi
      LEFT JOIN sales_orders so ON so.id = pi.sales_order_id
      WHERE pi.id = $1 AND pi.org_id = $2 AND pi.is_deleted = false
      `,
      [id, orgId]
    );

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

    // Fetch items
    const itemsRes = await pool.query(
      `
      SELECT 
        soi.product_id,
        soi.product_name,
        soi.qty,
        soi.rate,
        soi.uom_symbol,
        soi.amount,
        soi.gst_amount,
        soi.tax_percent
      FROM sales_order_items soi
      WHERE soi.sales_order_id = $1
      `,
      [result.rows[0].sales_order_id]
    );

    return res.json({
      success: true,
      data: {
        ...result.rows[0],
        items: itemsRes.rows
      }
    });
  } catch (err) {
    console.error("Get Proforma Error:", err);
    return res.status(500).json({
      success: false,
      message: err.message
    });
  }
};

// ==========================================
// GET ALL PROFORMAS (with filters)
// ==========================================
// const getAllProformas = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;
//     const { status, from_date, to_date, customer_code } = req.query;

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

//     let query = `
//       SELECT 
//         pi.id,
//         pi.proforma_number,
//         pi.sales_order_number,
//         pi.customer_name,
//         pi.advance_amount,
//         pi.status,
//         pi.created_at,
//         pi.pdf_path
//       FROM proforma_invoices pi
//       WHERE pi.org_id = $1 AND pi.is_deleted = false
//     `;
    
//     const params = [orgId];
//     let paramIndex = 2;

//     if (status) {
//       query += ` AND pi.status = $${paramIndex}`;
//       params.push(status);
//       paramIndex++;
//     }

//     if (customer_code) {
//       query += ` AND pi.customer_code = $${paramIndex}`;
//       params.push(customer_code);
//       paramIndex++;
//     }

//     if (from_date) {
//       query += ` AND DATE(pi.created_at) >= $${paramIndex}`;
//       params.push(from_date);
//       paramIndex++;
//     }

//     if (to_date) {
//       query += ` AND DATE(pi.created_at) <= $${paramIndex}`;
//       params.push(to_date);
//       paramIndex++;
//     }

//     query += ` ORDER BY pi.created_at DESC`;

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

//     return res.json({
//       success: true,
//       data: result.rows,
//       count: result.rows.length
//     });
//   } catch (err) {
//     console.error("Get All Proformas Error:", err);
//     return res.status(500).json({
//       success: false,
//       message: err.message
//     });
//   }
// };

const updateProformaStatus = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const { status, payment_details } = req.body;
    const userId = req.session?.user?.id;
    const orgId = req.session?.user?.orgId;

    if (!orgId || !userId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (!['pending', 'paid', 'cancelled', 'expired'].includes(status)) {
      return res.status(400).json({
        success: false,
        message: "Invalid status. Allowed: pending, paid, cancelled, expired"
      });
    }

    await client.query("BEGIN");

    // Get proforma details
    const proformaRes = await client.query(
      `
      SELECT * FROM proforma_invoices
      WHERE id = $1 AND org_id = $2 AND is_deleted = false
      `,
      [id, orgId]
    );

    if (!proformaRes.rows.length) {
      throw new Error("Proforma Invoice not found");
    }

    const proforma = proformaRes.rows[0];

    // Update status
    await client.query(
      `
      UPDATE proforma_invoices
      SET 
        status = $1,
        updated_at = NOW(),
        payment_details = $2,
        paid_at = CASE WHEN $1 = 'paid' THEN NOW() ELSE paid_at END
      WHERE id = $3
      `,
      [status, payment_details || null, id]
    );

    // If marking as paid, update sales order advance payment status
    if (status === 'paid') {
      await client.query(
        `
        UPDATE sales_orders
        SET 
          advance_payment_status = 'paid',
          advance_payment_paid_at = NOW(),
          advance_payment_reference = $1
        WHERE id = $2
        `,
        [payment_details?.reference || null, proforma.sales_order_id]
      );
    }

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: `Proforma Invoice marked as ${status}`,
      data: { id, status }
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Update Proforma Status Error:", err);
    return res.status(500).json({
      success: false,
      message: err.message
    });
  } finally {
    client.release();
  }
};


// ==========================================
// GET INVOICE HISTORY FOR SALES ORDER
// ==========================================
const getInvoiceHistoryBySalesOrder = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { salesOrderId } = req.params;
    const orgId = req.session?.user?.orgId;

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

    // Get Sales Order details with total quantity
    const salesOrderRes = await client.query(
      `
      SELECT 
        so.id,
        so.so_number,
        so.total_amount,
        COALESCE(SUM(soi.qty), 0) as order_quantity
      FROM sales_orders so
      LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
      WHERE so.id = $1 AND so.org_id = $2
      GROUP BY so.id, so.so_number, so.total_amount
      `,
      [salesOrderId, orgId]
    );

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

    const salesOrder = salesOrderRes.rows[0];
    const orderQuantity = Number(salesOrder.order_quantity);

    // Get all invoices for this sales order
    const invoicesRes = await client.query(
      `
      SELECT 
        si.id,
        si.invoice_number,
        si.invoice_date,
        si.total_amount,
        si.status,
        si.created_at,
        COALESCE(SUM(sii.qty), 0) as total_quantity_invoiced
      FROM sales_invoices si
      LEFT JOIN sales_invoice_items sii ON sii.invoice_id = si.id
      WHERE si.sales_order_id = $1 
        AND si.org_id = $2
        AND si.is_deleted = false
      GROUP BY si.id, si.invoice_number, si.invoice_date, si.total_amount, si.status, si.created_at
      ORDER BY si.created_at DESC
      `,
      [salesOrderId, orgId]
    );

    const invoices = invoicesRes.rows;
    
    // Calculate total invoiced quantity and amount
    let totalInvoicedQty = 0;
    let totalInvoicedAmount = 0;
    
    invoices.forEach(inv => {
      totalInvoicedQty += Number(inv.total_quantity_invoiced || 0);
      totalInvoicedAmount += Number(inv.total_amount || 0);
    });
    
    const remainingQty = Math.max(orderQuantity - totalInvoicedQty, 0);
    const percentageInvoiced = orderQuantity > 0 ? (totalInvoicedQty / orderQuantity) * 100 : 0;

    return res.status(200).json({
      success: true,
      data: {
        sales_order: {
          id: salesOrder.id,
          so_number: salesOrder.so_number,
          order_quantity: orderQuantity,
          order_amount: Number(salesOrder.total_amount),
          total_invoiced_qty: totalInvoicedQty,
          total_invoiced_amount: totalInvoicedAmount,
          remaining_qty: remainingQty,
          percentage_invoiced: percentageInvoiced,
          is_fully_invoiced: remainingQty === 0
        },
        invoices: invoices.map(inv => ({
          id: inv.id,
          invoice_number: inv.invoice_number,
          invoice_date: inv.invoice_date,
          total_amount: Number(inv.total_amount),
          status: inv.status,
          total_quantity_invoiced: Number(inv.total_quantity_invoiced),
          created_at: inv.created_at,
          is_partial_invoice: Number(inv.total_quantity_invoiced) < orderQuantity && invoices.length > 1
        }))
      }
    });

  } catch (error) {
    console.error("Get Invoice History Error:", error);
    return res.status(500).json({
      success: false,
      message: error.message || "Failed to fetch invoice history"
    });
  } finally {
    client.release();
  }
};

module.exports = {
  createInvoice,
  getInvoiceStats,
  getInvoices,
  checkProformaStatus,
  deleteInvoice,
  updateInvoice,
  getInvoiceById,
  downloadInvoicePDF,
  superApproveInvoice,
  getInvoiceApprovalStatus,
  approveInvoice,
  createMainInvoice,
  createProformaInvoice,
  updateVehicleWeighmentStatus,
  getSalesOrderInvoiceHistory,
  cancelInvoice,
  cancelProformaInvoice,
  generateProformaFromSalesOrder,
  getProformaById,
  updateProformaStatus,
  getInvoiceHistoryBySalesOrder,
  generateAndSendInvoiceDocuments,
};
