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 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,
      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;

    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");
}

    // =========================
    // 🔹 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,
        notes,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
      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,
        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 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
      FROM organizations
      WHERE id = $1
      `,
      [orgId],
    );

    const company = companyRes.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
    // ==========================================

    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] || {};

    // ==========================================
    // PDF GENERATION
    // ==========================================

    const eWayPdf = await generateEWayPDF();

    const eInvoicePdf = await generateEInvoicePDF();

    // 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 || "-",

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

    const pdfBuffers = [
      // dispatchInvoicePdf,
      // dispatchWeighmentPdf,
      // deliveryChallanPdf,
      pdfBuffer,
      eWayPdf,
      eInvoicePdf,
    ];

    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 transporter = nodemailer.createTransport({
    //       service: "gmail",
    //       auth: {
    //         user: process.env.MAIL_USER,
    //         pass: process.env.MAIL_PASS,
    //       },
    //     });

    // const transporter = nodemailer.createTransport({
    //   host: "127.0.0.1",
    //   port: 25,
    //   secure: false,
    //   ignoreTLS: true,
    //   auth: {
    //     user: process.env.MAIL_USER,   // noreply@vivekbioservices.in
    //     pass: process.env.MAIL_PASS,   // your cpanel email password
    //   },
    //   tls: {
    //     rejectUnauthorized: false,
    //   }
    // });

    // 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();
  }
};

// 🔹 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 transporter = nodemailer.createTransport({
    //   host: "127.0.0.1",
    //   port: 25,
    //   secure: false,
    //   ignoreTLS: true,
    //   auth: {
    //     user: process.env.MAIL_USER,   // noreply@vivekbioservices.in
    //     pass: process.env.MAIL_PASS,   // your cpanel email password
    //   },
    //   tls: {
    //     rejectUnauthorized: false,
    //   }
    // });

    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 transporter = nodemailer.createTransport({
    //   host: "127.0.0.1",
    //   port: 25,
    //   secure: false,
    //   ignoreTLS: true,
    //   auth: {
    //     user: process.env.MAIL_USER,   // noreply@vivekbioservices.in
    //     pass: process.env.MAIL_PASS,   // your cpanel email password
    //   },
    //   tls: {
    //     rejectUnauthorized: false,
    //   }
    // });

    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
// ==============================================
// 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 transporter = nodemailer.createTransport({
    //   host: "127.0.0.1",
    //   port: 25,
    //   secure: false,
    //   ignoreTLS: true,
    //   auth: {
    //     user: process.env.MAIL_USER,   // noreply@vivekbioservices.in
    //     pass: process.env.MAIL_PASS,   // your cpanel email password
    //   },
    //   tls: {
    //     rejectUnauthorized: false,
    //   }
    // });

    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);
  }
}


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