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

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 getSalesOrders = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;

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

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

//     const search = req.query.search || "";

//     // 🔹 BASE QUERY
//     const baseQuery = `
//       FROM sales_orders so
//       LEFT JOIN sales_order_items soi
//         ON soi.sales_order_id = so.id
//       WHERE so.org_id = $1
//         AND so.is_deleted = false
//         AND (
//           so.so_number ILIKE $2 OR
//           so.customer_name ILIKE $2 OR
//           soi.product_name ILIKE $2
//         )
//     `;

//     // 🔹 COUNT QUERY
//     const countRes = await pool.query(`SELECT COUNT(*) ${baseQuery}`, [
//       orgId,
//       `%${search}%`,
//     ]);

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

//     // 🔹 DATA QUERY
//     const dataRes = await pool.query(
//       `
//       SELECT
//         so.id,
//         so.so_number,
//         so.quotation_number,
//         so.customer_name,
//         so.total_amount,
//         so.status,
//          so.created_at,

//         soi.product_name,
//         soi.qty,
//         soi.rate,
//         soi.uom_symbol

//       ${baseQuery}
//       ORDER BY so.created_at DESC
//       LIMIT $3 OFFSET $4
//       `,
//       [orgId, `%${search}%`, limit, offset],
//     );

//     // 🔹 FORMAT RESPONSE (S.NO)
//     // const data = dataRes.rows.map((row, index) => ({
//     //   id: row.id,
//     //   s_no: offset + index + 1,
//     //   so_id: row.so_number,
//     //   quotation_ref: row.quotation_number,
//     //   customer: row.customer_name,
//     //   product: row.product_name,
//     //   qty: row.qty,
//     //   rate: row.rate,
//     //   total: row.total_amount,
//     //   status: row.status,
//     // }));
//     const data = dataRes.rows.map((row, index) => ({
//       id: row.id,
//       s_no: offset + index + 1,
//       so_id: row.so_number,
//       quotation_ref: row.quotation_number,
//       customer: row.customer_name,
//       product: row.product_name,
//       uom: row.uom_symbol || "",
//       qty: Number(row.qty), // ✅ FIX
//       rate: Number(row.rate), // ✅ FIX
//       total: Number(row.total_amount), // ✅ FIX
//       status: row.status,
//       created_at: row.created_at,
//     }));

//     res.json({
//       success: true,
//       message: "Sales Orders fetched successfully",
//       data,
//       pagination: {
//         total,
//         page,
//         limit,
//         totalPages: Math.ceil(total / limit),
//       },
//     });
//   } catch (err) {
//     console.error("GET SALES ORDERS ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: "Failed to fetch sales orders",
//     });
//   }
// };
const nodemailer = require("nodemailer");
// const { PDFDocument, rgb, StandardFonts } = require("pdf-lib");

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


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

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

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

    // 🔹 BASE QUERY (ADDED CUSTOMER JOIN)
    // const baseQuery = `
    //   FROM sales_orders so
    //   LEFT JOIN sales_order_items soi
    //     ON soi.sales_order_id = so.id
    //   LEFT JOIN customers c
    //     ON c.customer_code = so.customer_id
    //     AND c.org_id = so.org_id
    //   WHERE so.org_id = $1
    //     AND so.is_deleted = false
    //     AND (
    //       so.so_number ILIKE $2 OR
    //       so.customer_name ILIKE $2 OR
    //       soi.product_name ILIKE $2
    //     )
    // `;
    const baseQuery = `
    FROM sales_orders so

    LEFT JOIN sales_order_items soi 
      ON soi.sales_order_id = so.id

    LEFT JOIN customers c 
      ON c.customer_code = so.customer_id 
      AND c.org_id = so.org_id

    LEFT JOIN users created_user
      ON created_user.id = so.created_by

    LEFT JOIN users approved_user
      ON approved_user.id = so.approved_by

    WHERE so.org_id = $1
      AND so.is_deleted = false
      AND (
        so.so_number ILIKE $2 OR
        so.customer_name ILIKE $2 OR
        soi.product_name ILIKE $2
      )
  `;

    // 🔹 COUNT
    const countRes = await pool.query(
      `SELECT COUNT(DISTINCT so.id) ${baseQuery}`,
      [orgId, `%${search}%`],
    );

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

    // 🔹 DATA QUERY
    const dataRes = await pool.query(
      `
        SELECT 
          so.id,
          so.so_number,
          so.quotation_number,
          so.customer_name,
          so.total_amount,
          so.status,
          so.created_at,
          so.delivery_date,
          so.dispatch_schedule_date,
           so.with_advance_payment,
    so.advance_payment_percent,
    so.advance_payment_amount,
    so.advance_payment_status,
    so.proforma_required,

          
    so.approval_status,
    so.approved_at,
    so.created_by,
    so.approved_by,

  
  created_user.name AS created_by_name,
  approved_user.name AS approved_by_name,

          -- ✅ DELIVERY ADDRESS (PRIORITY LOGIC)
          COALESCE(
            so.customer_address,
            CONCAT_WS(', ',
              c.shipping_address,
              c.ship_city,
              c.ship_state,
              c.ship_pin_code
            ),
            CONCAT_WS(', ',
              c.billing_address,
              c.bill_city,
              c.bill_state,
              c.bill_pin_code
            )
          ) AS delivery_location,

          soi.product_name,
          soi.qty,
          soi.rate,
          soi.uom_symbol

        ${baseQuery}
        ORDER BY so.created_at DESC
        LIMIT $3 OFFSET $4
        `,
      [orgId, `%${search}%`, limit, offset],
    );

    // 🔹 FORMAT RESPONSE
    const data = dataRes.rows.map((row, index) => ({
      id: row.id,
      s_no: offset + index + 1,
      so_id: row.so_number,
      quotation_ref: row.quotation_number,
      customer: row.customer_name,
      delivery_date: row.delivery_date,
      dipatch_date: row.dispatch_schedule_date,

      product: row.product_name,
      uom: row.uom_symbol || "",
      qty: Number(row.qty),
      rate: Number(row.rate),

      approval_status: row.approval_status || "Pending",
      status: row.status,
      approved_at: row.approved_at,

      created_by: row.created_by,
      approved_by: row.approved_by,

      created_by_name: row.created_by_name,
      approved_by_name: row.approved_by_name,

      total: Number(row.total_amount),
      // status: row.status,
      created_at: row.created_at,

      with_advance_payment: row.with_advance_payment,
      advance_payment_percent: row.advance_payment_percent,
      advance_payment_amount: row.advance_payment_amount,
      advance_payment_status: row.advance_payment_status,
      proforma_required: row.proforma_required,

      // ✅ NEW FIELD
      delivery_location: row.delivery_location || "-",
    }));

    res.json({
      success: true,
      message: "Sales Orders fetched successfully",
      data,
      pagination: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    });
  } catch (err) {
    console.error("GET SALES ORDERS ERROR:", err);

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

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

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

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

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

    await client.query("BEGIN");

    // 🔹 CHECK EXISTENCE
    const existing = await client.query(
      `SELECT id FROM sales_orders 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId],
    );

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

    // 🔹 SOFT DELETE HEADER
    await client.query(
      `UPDATE sales_orders
       SET is_deleted = true,
           updated_at = NOW(),
           updated_by = $2
       WHERE id = $1`,
      [id, userId],
    );

    // 🔹 OPTIONAL: also mark items deleted (if column exists)
    await client.query(
      `UPDATE sales_order_items
       SET is_deleted = true
       WHERE sales_order_id = $1`,
      [id],
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "Sales Order deleted successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("DELETE SALES ORDER ERROR:", err);

    res.status(500).json({
      success: false,
      message: "Failed to delete sales order",
    });
  } finally {
    client.release();
  }
};

// const createSalesOrder = 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_code,
//       quotation_number, // optional
//       delivery_date,
//       po_reference,
//       items,
//     } = req.body;

//     // ✅ VALIDATION
//     if (!customer_code) {
//       return res.status(400).json({ message: "Customer is required" });
//     }

//     if (!items || items.length === 0) {
//       return res.status(400).json({ message: "Items required" });
//     }

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

//     // 🔹 STEP 1: PREFIX
//     const prefixRes = await client.query(
//       `
//   UPDATE prefixes
//   SET last_number = COALESCE(last_number, 0) + 1
//   WHERE module_name = 'sales_order'
//     AND org_id = $1
//     AND is_deleted = false
//   RETURNING prefix_code, last_number
//   `,
//       [orgId],
//     );

//     const { prefix_code, last_number } = prefixRes.rows[0];

//     const so_number = `${prefix_code}${String(last_number).padStart(3, "0")}`;

//     // 🔹 STEP 2: CUSTOMER DETAILS
//     const custRes = await client.query(
//       `SELECT company_name, billing_address, gst
//        FROM customers
//        WHERE  customer_code= $1 AND org_id = $2`,
//       [customer_code, orgId],
//     );

//     const customer = custRes.rows[0] || {};

//     // 🔹 STEP 3: INSERT SALES ORDER
//     const orderRes = await client.query(
//       `
//       INSERT INTO sales_orders (
//         so_number,
//         quotation_number,
//         customer_id,
//         customer_name,
//         customer_address,
//         customer_gst,
//         delivery_date,
//         po_reference,
//         status,
//         org_id,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
//       RETURNING id
//       `,
//       [
//         so_number,
//         quotation_number || null,
//         customer_code,
//         customer.company_name || "",
//         customer.billing_address || "",
//         customer.gst || "",
//         delivery_date || null,
//         po_reference || null,
//         "open",
//         orgId,
//         userId,
//       ],
//     );

//     const salesOrderId = orderRes.rows[0].id;

//     let totalAmount = 0;

//     // 🔹 STEP 4: INSERT ITEMS
//     for (const item of items) {
//       const { product_id, product_name, qty, rate } = item;

//       if (!product_id || !qty || !rate) {
//         throw new Error("Invalid item data");
//       }

//       const amount = Number(qty) * Number(rate);
//       totalAmount += amount;

//       await client.query(
//         `
//         INSERT INTO sales_order_items (
//           sales_order_id,
//           product_id,
//           product_name,
//           qty,
//           rate,
//           amount
//         )
//         VALUES ($1,$2,$3,$4,$5,$6)
//         `,
//         [salesOrderId, product_id, product_name, qty, rate, amount],
//       );
//     }

//     // 🔹 STEP 5: UPDATE TOTAL
//     await client.query(
//       `UPDATE sales_orders SET total_amount = $1 WHERE id = $2`,
//       [totalAmount, salesOrderId],
//     );

//     // 🔹 STEP 6: UPDATE PREFIX
//     await client.query(
//       `UPDATE prefixes SET last_number = last_number + 1
//        WHERE module_name = 'sales_order' AND org_id = $1`,
//       [orgId],
//     );

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

//     res.status(201).json({
//       success: true,
//       message: "Sales Order created successfully",
//       data: {
//         sales_order_id: salesOrderId,
//         so_number,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

//     console.error("CREATE SO ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: err.message || "Failed to create sales order",
//     });
//   } finally {
//     client.release();
//   }
// };



// const createSalesOrder = 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_code,
//       quotation_number,
//       delivery_date,
//       po_reference,
//       items,
//     } = req.body;

//     if (!customer_code) {
//       return res.status(400).json({ message: "Customer is required" });
//     }

//     if (!items || items.length === 0) {
//       return res.status(400).json({ message: "Items required" });
//     }

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

//     // 🔹 PREFIX (single update only)
//     // 🔹 STEP 1: LOCK PREFIX ROW
//     const prefixRes = await client.query(
//       `
//   SELECT prefix_code, last_number
//   FROM prefixes
//   WHERE module_name = 'sales_order'
//     AND org_id = $1
//     AND is_deleted = false
//   FOR UPDATE
//   `,
//       [orgId],
//     );

//     if (!prefixRes.rows.length) {
//       throw new Error("Sales Order prefix not configured");
//     }

//     let { prefix_code, last_number } = prefixRes.rows[0];

//     // 🔹 STEP 2: GET MAX EXISTING SO NUMBER
//     const maxRes = await client.query(
//       `
//   SELECT MAX(so_number) as max_so
//   FROM sales_orders
//   WHERE org_id = $1
//   `,
//       [orgId],
//     );

//     let nextNumber = Number(last_number) + 1;

//     if (maxRes.rows[0].max_so) {
//       const lastSo = maxRes.rows[0].max_so;

//       // Extract numeric part safely
//       const numericPart = parseInt(lastSo.replace(prefix_code, "")) || 0;

//       if (numericPart >= nextNumber) {
//         nextNumber = numericPart + 1;
//       }
//     }

//     // 🔹 STEP 3: GENERATE SO NUMBER
//     const so_number = `${prefix_code}${String(nextNumber).padStart(3, "0")}`;

//     // 🔹 STEP 4: UPDATE PREFIX
//     await client.query(
//       `
//   UPDATE prefixes
//   SET last_number = $1
//   WHERE module_name = 'sales_order'
//     AND org_id = $2
//   `,
//       [nextNumber, orgId],
//     );
//     if (!prefixRes.rows.length) {
//       throw new Error("Sales Order prefix not configured");
//     }

//     // const { prefix_code, last_number } = prefixRes.rows[0];
//     // const so_number = `${prefix_code}${String(last_number).padStart(3, "0")}`;

//     // 🔹 CUSTOMER
//     const custRes = await client.query(
//       `SELECT company_name, billing_address, gst
//        FROM customers
//        WHERE customer_code = $1 AND org_id = $2`,
//       [customer_code, orgId],
//     );

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

//     const customer = custRes.rows[0];

//     // 🔹 INSERT ORDER
//     const orderRes = await client.query(
//       `
//       INSERT INTO sales_orders (
//         so_number,
//         quotation_number,
//         customer_id,
//         customer_name,
//         customer_address,
//         customer_gst,
//         delivery_date,
//         po_reference,
//         status,
//         org_id,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
//       RETURNING id
//       `,
//       [
//         so_number,
//         quotation_number || null,
//         customer_code,
//         customer.company_name,
//         customer.billing_address,
//         customer.gst,
//         delivery_date || null,
//         po_reference || null,
//         "open",
//         orgId,
//         userId,
//       ],
//     );

//     const salesOrderId = orderRes.rows[0].id;

//     let totalAmount = 0;

//     // 🔹 ITEMS WITH GST
//     for (const item of items) {
//       const { item_code, item_name, qty, rate, uom_symbol } = item;

//       if (!item_code || !qty || !rate) {
//         throw new Error("Invalid item data");
//       }

//       const quantity = Number(qty);
//       const unitRate = Number(rate);

//       const baseAmount = quantity * unitRate;
//       const gstPercent = 18;
//       const gstAmount = (baseAmount * gstPercent) / 100;
//       const totalItemAmount = baseAmount + gstAmount;

//       totalAmount += totalItemAmount;

//       await client.query(
//         `
//        INSERT INTO sales_order_items (
//   sales_order_id,
//   product_code,   -- ✅ USE THIS COLUMN
//   product_name,
//   qty,
//   rate,
//   uom_symbol,
//   gst_amount,
//   amount,
//   tax_percent
// )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
//         `,
//         [
//           salesOrderId,
//           item_code, // ✅ IMPORTANT
//           item_name,
//           quantity,
//           unitRate,
//           uom_symbol,
//           gstAmount,
//           totalItemAmount,
//           gstPercent,
//         ],
//       );
//     }

//     // 🔹 UPDATE TOTAL
//     await client.query(
//       `UPDATE sales_orders SET total_amount = $1 WHERE id = $2`,
//       [totalAmount, salesOrderId],
//     );

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

//     res.status(201).json({
//       success: true,
//       message: "Sales Order created successfully",
//       data: {
//         sales_order_id: salesOrderId,
//         so_number,
//         total_amount: totalAmount,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

//     console.error("CREATE SO ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: err.message || "Failed to create sales order",
//     });
//   } finally {
//     client.release();
//   }
// };



// const createSalesOrder = 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_code,
//     //   quotation_number,
//     //   delivery_date,
//     //   po_reference,
//     //   items,
//     // } = req.body;
//     const {
//       customer_code,
//       quotation_number,
//       delivery_date,
//       po_reference,
//       items,

//       with_advance_payment,
//       advance_percent,
//       advance_amount,
//       proforma_required,
//     } = req.body;

//     if (!customer_code) {
//       return res.status(400).json({ message: "Customer is required" });
//     }

//     if (!items || items.length === 0) {
//       return res.status(400).json({ message: "Items required" });
//     }

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

//     // ✅ LOCK PREFIX
//     const prefixRes = await client.query(
//       `
//       SELECT prefix_code, last_number
//       FROM prefixes
//       WHERE module_name = 'sales_order'
//         AND org_id = $1
//         AND is_deleted = false
//       FOR UPDATE
//       `,
//       [orgId],
//     );

//     if (!prefixRes.rows.length) {
//       throw new Error("Sales Order prefix not configured");
//     }

//     let { prefix_code, last_number } = prefixRes.rows[0];

//     // ✅ GET MAX SO
//     const maxRes = await client.query(
//       `SELECT MAX(so_number) as max_so FROM sales_orders WHERE org_id = $1`,
//       [orgId],
//     );

//     let nextNumber = Number(last_number) + 1;

//     if (maxRes.rows[0].max_so) {
//       const numericPart =
//         parseInt(maxRes.rows[0].max_so.replace(prefix_code, "")) || 0;

//       if (numericPart >= nextNumber) {
//         nextNumber = numericPart + 1;
//       }
//     }

//     const so_number = `${prefix_code}${String(nextNumber).padStart(3, "0")}`;

//     // ✅ UPDATE PREFIX
//     await client.query(
//       `UPDATE prefixes SET last_number = $1 WHERE module_name='sales_order' AND org_id=$2`,
//       [nextNumber, orgId],
//     );

//     // ✅ CUSTOMER DETAILS
//     const custRes = await client.query(
//       `SELECT company_name, billing_address, gst, email
//        FROM customers
//        WHERE customer_code = $1 AND org_id = $2`,
//       [customer_code, orgId],
//     );

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

//     const customer = custRes.rows[0];
//     // console.log(customer.email, "tyuiopoiuytdfgh")

//     // ✅ INSERT ORDER
//     const orderRes = await client.query(
//       `
//       INSERT INTO sales_orders (
//         so_number,
//         quotation_number,
//         customer_id,
//         customer_name,
//         customer_address,
//         customer_gst,
//         delivery_date,
//         po_reference,
//         with_advance_payment,
// advance_payment_percent,
// advance_payment_amount,
// advance_payment_status,
// proforma_required,
//         status,
//         org_id,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
//       RETURNING id
//       `,
//       [
//         so_number,
//         quotation_number || null,
//         customer_code,
//         customer.company_name,
//         customer.billing_address,
//         customer.gst,
//         delivery_date || null,
//         po_reference || null,
//         with_advance_payment || false,

//         advance_percent || 0,

//         advance_amount || 0,

//         with_advance_payment ? "pending" : null,
//         proforma_required,

//         "open",
//         orgId,
//         userId,
//       ],
//     );

//     const salesOrderId = orderRes.rows[0].id;

//     let totalAmount = 0;

//     // ✅ ITEMS LOOP
//     for (const item of items) {
//       const { item_id, item_code, item_name, qty, rate, uom_symbol,gst } = item;

//       if (!item_code || !qty || !rate) {
//         throw new Error("Invalid item data");
//       }

//       const quantity = Number(qty);
//       const unitRate = Number(rate);

//       const baseAmount = quantity * unitRate;
//       // const gstPercent = 18;
//       const gstPercent = Number(item.gst) || 0;
//       const gstAmount = (baseAmount * gstPercent) / 100;
//       const totalItemAmount = baseAmount + gstAmount;

//       totalAmount += totalItemAmount;

//       await client.query(
//         `
//         INSERT INTO sales_order_items (
//           sales_order_id,
//           product_id,
//           product_code,
//           product_name,
//           qty,
//           rate,
//           uom_symbol,
//           gst_amount,
//           amount,
//           tax_percent
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
//         `,
//         [
//           salesOrderId,
//           item_id,
//           item_code,
//           item_name,
//           quantity,
//           unitRate,
//           uom_symbol,
//           gstAmount,
//           totalItemAmount,
//           gstPercent,
//         ],
//       );
//     }

//     // ✅ UPDATE TOTAL
//     await client.query(
//       `UPDATE sales_orders SET total_amount = $1 WHERE id = $2`,
//       [totalAmount, salesOrderId],
//     );

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

//     //     // =========================
//     //     // 📧 EMAIL AFTER SUCCESS
//     //     // =========================

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

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

//     //     if (customer.email) {
//     //       try {
//     //         await transporter.sendMail({
//     //           from: `"Sales Team" <${process.env.MAIL_USER}>`,
//     //           to: customer.email,
//     //           subject: `Sales Order ${so_number} Created`,

//     //           //           html: `
//     //           // <div style="font-family: Arial; background:#f4f6f8; padding:20px;">
//     //           //   <table style="max-width:700px;margin:auto;background:#fff;border-radius:8px;border:1px solid #ddd;">

//     //           //     <tr style="background:#2563eb;color:#fff;">
//     //           //       <td style="padding:15px;font-size:18px;">
//     //           //         Sales Order Confirmation
//     //           //       </td>
//     //           //     </tr>

//     //           //     <tr>
//     //           //       <td style="padding:20px;">
//     //           //         <p>Dear <strong>${customer.company_name}</strong>,</p>

//     //           //         <p>Your Sales Order has been created successfully.</p>

//     //           //         <table width="100%" border="1" cellspacing="0" cellpadding="8" style="border-collapse:collapse;margin-top:10px;">
//     //           //           <tr>
//     //           //             <th>SO Number</th>
//     //           //             <th>PO Ref</th>
//     //           //             <th>Delivery Date</th>
//     //           //           </tr>
//     //           //           <tr>
//     //           //             <td>${so_number}</td>
//     //           //             <td>${po_reference || "-"}</td>
//     //           //             <td>${delivery_date || "-"}</td>
//     //           //           </tr>
//     //           //         </table>

//     //           //         <p style="margin-top:15px;">
//     //           //           Total Amount: <strong>₹${totalAmount}</strong>
//     //           //         </p>

//     //           //         <p>Regards,<br/><strong>Sales Team</strong></p>
//     //           //       </td>
//     //           //     </tr>
//     //           //   </table>
//     //           // </div>
//     //           //           `,
//     //           html: `
//     // <div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">
//     //   <table width="100%" cellpadding="0" cellspacing="0" style="max-width:750px;margin:auto;background:#ffffff;border-radius:8px;border:1px solid #e5e7eb;">

//     //     <!-- HEADER -->
//     //     <tr style="background:#2563eb;color:#ffffff;">
//     //       <td style="padding:16px 20px;font-size:18px;font-weight:600;">
//     //         Sales Order Confirmation
//     //       </td>
//     //     </tr>

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

//     //         <p>Dear <strong>${customer.company_name}</strong>,</p>

//     //         <p>
//     //           Thank you for your order. Your <strong>Sales Order (${so_number})</strong> has been successfully created.
//     //         </p>

//     //         <!-- ORDER SUMMARY -->
//     //         <table width="100%" style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">
//     //           <tr style="background:#f9fafb;">
//     //             <td colspan="2" style="padding:10px;font-weight:600;">Order Summary</td>
//     //           </tr>

//     //           <tr>
//     //             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">SO Number</td>
//     //             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">${so_number}</td>
//     //           </tr>

//     //           <tr>
//     //             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">PO Reference</td>
//     //             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">${po_reference || "-"}</td>
//     //           </tr>

//     //           <tr>
//     //             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Delivery Date</td>
//     //             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">${formatDate(delivery_date) || "-"}</td>
//     //           </tr>
//     //         </table>

//     //         <!-- ITEMS TABLE -->
//     //         <table width="100%" style="margin-top:20px;border-collapse:collapse;border:1px solid #e5e7eb;">
//     //           <tr style="background:#f9fafb;">
//     //             <th style="padding:8px;border:1px solid #e5e7eb;">Item</th>
//     //             <th style="padding:8px;border:1px solid #e5e7eb;">Qty</th>
//     //             <th style="padding:8px;border:1px solid #e5e7eb;">Rate</th>
//     //               <th style="padding:8px;border:1px solid #e5e7eb;">Base Amt</th>
//     //             <th style="padding:8px;border:1px solid #e5e7eb;">Tax Amt</th>
//     //             <th style="padding:8px;border:1px solid #e5e7eb;">Total</th>
//     //           </tr>

//     //           ${items
//     //             .map((item) => {
//     //               const base = item.qty * item.rate;
//     //               const taxPercent = 18;
//     //               const taxAmt = (base * taxPercent) / 100;
//     //               const total = base + taxAmt;

//     //               return `
//     //               <tr>
//     //                 <td style="padding:8px;border:1px solid #e5e7eb;">${item.item_name}</td>
//     //                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:center;"> ${item.qty}${item.uom_symbol ? ` (${item.uom_symbol})` : ""}</td>
//     //                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${item.rate}</td>

//     //                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${taxAmt.toFixed(2)}</td>
//     //                  <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${base}</td>
//     //                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${total.toFixed(2)}</td>
//     //               </tr>`;
//     //             })
//     //             .join("")}
//     //         </table>

//     //         <!-- TOTAL -->
//     //         <p style="margin-top:15px;font-size:15px;">
//     //           <strong>Total Amount: ₹${totalAmount}</strong>
//     //         </p>

//     //         <!-- NOTES -->
//     //         <p style="margin-top:20px;">
//     //           Our team will process your order and ensure timely delivery.
//     //           If you have any questions, feel free to contact our support team.
//     //         </p>

//     //         <p>
//     //           Regards,<br/>
//     //           <strong>Sales Team</strong>
//     //           <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:#6b7280;">
//     //         This is an automated confirmation email. Please do not reply.
//     //       </td>
//     //     </tr>

//     //   </table>
//     // </div>
//     // `,
//     //         });
//     //       } catch (mailErr) {
//     //         console.error("SO EMAIL ERROR:", mailErr.message);
//     //       }
//     //     }

//     return res.status(201).json({
//       success: true,
//       message: "Sales Order created successfully",
//       data: {
//         sales_order_id: salesOrderId,
//         so_number,
//         total_amount: totalAmount,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CREATE SO ERROR:", err);

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

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

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

const generateSalesOrderPDF = require("../../services/pdf/generateSalesOrderPDF");

const createSalesOrder = 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({
    success: false,
    message: "Unauthorized",
  });
}

const {
  customer_code,
  quotation_number,
  delivery_date,
  po_reference,
  items,

  with_advance_payment,
  advance_percent,
  advance_amount,
  proforma_required,
} = req.body;

// =====================================
// VALIDATIONS
// =====================================

if (!customer_code) {
  return res.status(400).json({
    success: false,
    message: "Customer is required",
  });
}

if (!items || items.length === 0) {
  return res.status(400).json({
    success: false,
    message: "Items required",
  });
}

await client.query("BEGIN");

// =====================================
// LOCK PREFIX
// =====================================

const prefixRes = await client.query(
  `
  SELECT prefix_code, last_number
  FROM prefixes
  WHERE module_name = 'sales_order'
    AND org_id = $1
    AND is_deleted = false
  FOR UPDATE
  `,
  [orgId]
);

if (!prefixRes.rows.length) {
  throw new Error(
    "Sales Order prefix not configured"
  );
}

let {
  prefix_code,
  last_number,
} = prefixRes.rows[0];

// =====================================
// GET NEXT SO NUMBER
// =====================================

const maxRes = await client.query(
  `
  SELECT MAX(so_number) as max_so
  FROM sales_orders
  WHERE org_id = $1
  `,
  [orgId]
);

let nextNumber =
  Number(last_number) + 1;

if (maxRes.rows[0].max_so) {

  const numericPart =
    parseInt(
      maxRes.rows[0].max_so.replace(
        prefix_code,
        ""
      )
    ) || 0;

  if (numericPart >= nextNumber) {
    nextNumber = numericPart + 1;
  }
}

const so_number =
  `${prefix_code}${String(nextNumber).padStart(3, "0")}`;

// =====================================
// UPDATE PREFIX
// =====================================

await client.query(
  `
  UPDATE prefixes
  SET last_number = $1
  WHERE module_name='sales_order'
    AND org_id=$2
  `,
  [nextNumber, orgId]
);

// =====================================
// CUSTOMER DETAILS
// =====================================

const custRes = await client.query(
  `
  SELECT
    company_name,
    billing_address,
    gst,
    email
  FROM customers
  WHERE customer_code = $1
    AND org_id = $2
  `,
  [customer_code, orgId]
);

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

const customer =
  custRes.rows[0];

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

const orderRes = await client.query(
  `
  INSERT INTO sales_orders (

    so_number,
    quotation_number,

    customer_id,
    customer_name,
    customer_address,
    customer_gst,

    delivery_date,
    po_reference,

    with_advance_payment,
    advance_payment_percent,
    advance_payment_amount,
    advance_payment_status,

    proforma_required,

    status,
    approval_status,

    org_id,
    created_by, total_quantity,
    total_amount

  )
  VALUES (
    $1,$2,$3,$4,$5,$6,
    $7,$8,$9,$10,$11,$12,
    $13,$14,$15,$16,$17,0,0
  )
  RETURNING id
  `,
  [
    so_number,
    quotation_number || null,

    customer_code,
    customer.company_name,
    customer.billing_address,
    customer.gst,

    delivery_date || null,
    po_reference || null,

    with_advance_payment || false,

    advance_percent || 0,

    advance_amount || 0,

    with_advance_payment
      ? "Pending"
      : null,

    proforma_required || false,

    "open",
    "Pending",

    orgId,
    userId,
  ]
);

const salesOrderId =
  orderRes.rows[0].id;

// =====================================
// INSERT ITEMS
// =====================================

// let totalAmount = 0;

let totalAmount = 0;
let totalQuantity = 0;

for (const item of items) {

  const {
    item_id,
    item_code,
    item_name,
    qty,
    rate,
    uom_symbol,
    gst,
  } = item;

  if (
    !item_code ||
    !qty ||
    !rate
  ) {
    throw new Error(
      "Invalid item data"
    );
  }

  const quantity =
    Number(qty);

  const unitRate =
    Number(rate);

      totalQuantity += quantity;

  const baseAmount =
    quantity * unitRate;

  const gstPercent =
    Number(gst) || 0;

  const gstAmount =
    (baseAmount * gstPercent) / 100;

  const totalItemAmount =
    baseAmount + gstAmount;

  totalAmount +=
    totalItemAmount;

  await client.query(
    `
    INSERT INTO sales_order_items (

      sales_order_id,

      product_id,
      product_code,
      product_name,

      qty,
      rate,
      uom_symbol,

      gst_amount,
      amount,
      tax_percent

    )
    VALUES (
      $1,$2,$3,$4,$5,
      $6,$7,$8,$9,$10
    )
    `,
    [
      salesOrderId,

      item_id,
      item_code,
      item_name,

      quantity,
      unitRate,
      uom_symbol,

      gstAmount,
      totalItemAmount,
      gstPercent,
    ]
  );
}

// =====================================
// UPDATE TOTAL
// =====================================

await client.query(
  `
  UPDATE sales_orders
  SET total_amount = $1,
      total_quantity = $2,
      updated_at = NOW()
  WHERE id = $3
  `,
  [totalAmount, totalQuantity, salesOrderId]
);

// =====================================
// FETCH FULL ORDER
// =====================================

const orderRes2 = await client.query(
  `
  SELECT

    so.*,

    creator.name AS prepared_by_name,

    approver.name AS approved_by_name

  FROM sales_orders so

  LEFT JOIN users creator
    ON creator.id = so.created_by

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

  WHERE so.id = $1
  `,
  [salesOrderId]
);

const order =
  orderRes2.rows[0];

// =====================================
// FETCH ITEMS
// =====================================

const itemsRes = await client.query(
  `
  SELECT
    product_name,
    qty,
    rate,
    uom_symbol,
    amount,
    gst_amount,
    tax_percent
  FROM sales_order_items
  WHERE sales_order_id = $1
  `,
  [salesOrderId]
);

const orderItems =
  itemsRes.rows;

// =====================================
// FETCH COMPANY
// =====================================

const companyRes = await client.query(
  `
  SELECT *
  FROM organizations
  WHERE id = $1
  `,
  [orgId]
);

const company =
  companyRes.rows[0];

// =====================================
// GENERATE PDF
// =====================================

const pdfBuffer =
  await generateSalesOrderPDF(
    order,
    orderItems,
    company
  );

// =====================================
// SAVE PDF
// =====================================

const pdfFolder = path.join(
  __dirname,
  "../../uploads/sales-orders"
);

if (!fs.existsSync(pdfFolder)) {

  fs.mkdirSync(pdfFolder, {
    recursive: true,
  });
}

const pdfFileName =
  `${so_number}.pdf`;

const pdfFilePath = path.join(
  pdfFolder,
  pdfFileName
);

fs.writeFileSync(
  pdfFilePath,
  pdfBuffer
);

// =====================================
// UPDATE PDF PATH
// =====================================

await client.query(
  `
  UPDATE sales_orders
  SET pdf_path = $1
  WHERE id = $2
  `,
  [
    `uploads/sales-orders/${pdfFileName}`,
    salesOrderId,
  ]
);

// =====================================
// COMMIT
// =====================================

await client.query("COMMIT");

// =====================================
// SEND EMAIL
// =====================================

if (customer.email) {

  const totalGstAmount =
    orderItems.reduce(
      (sum, item) =>
        sum +
        Number(
          item.gst_amount || 0
        ),
      0
    );

  const subtotal =
    totalAmount -
    totalGstAmount;

  const transporter =
    nodemailer.createTransport({
      service: "gmail",

      auth: {
        user:
          process.env.MAIL_USER,

        pass:
          process.env.MAIL_PASS,
      },
    });

  await transporter.sendMail({

    from:
      `"Sales Team" <${process.env.MAIL_USER}>`,

    to: customer.email,

    subject:
      `Sales Order ${so_number} Created`,

    attachments: [
      {
        filename:
          pdfFileName,

        content:
          pdfBuffer,

        contentType:
          "application/pdf",
      },
    ],

    html: `


<div style="font-family:Arial,sans-serif;background:#f4f6f8;padding:20px;">

  <table
    width="100%"
    cellpadding="0"
    cellspacing="0"
    style="
      max-width:800px;
      margin:auto;
      background:#ffffff;
      border-radius:8px;
      overflow:hidden;
      border:1px solid #e5e7eb;
    "
  >


<tr style="background:#2563eb;color:#ffffff;">
  <td style="padding:18px 24px;font-size:20px;font-weight:600;">
    Sales Order Created
  </td>
</tr>

<tr>
  <td style="padding:24px;color:#374151;font-size:14px;line-height:1.7;">

    <p>
      Dear
      <strong>${order.customer_name}</strong>,
    </p>

    <p>
      Your Sales Order has been created successfully.
    </p>

    <p>
      Please find attached Sales Order PDF.
    </p>

    <!-- ORDER SUMMARY -->

    <table
      width="100%"
      style="
        margin-top:20px;
        border-collapse:collapse;
        border:1px solid #e5e7eb;
      "
    >

      <tr style="background:#f9fafb;">
        <td
          colspan="2"
          style="
            padding:10px;
            font-weight:600;
          "
        >
          Order Summary
        </td>
      </tr>

      <tr>
        <td style="padding:10px;border:1px solid #e5e7eb;">
          SO Number
        </td>

        <td style="padding:10px;border:1px solid #e5e7eb;">
          ${order.so_number}
        </td>
      </tr>

      <tr>
        <td style="padding:10px;border:1px solid #e5e7eb;">
          PO Reference
        </td>

        <td style="padding:10px;border:1px solid #e5e7eb;">
          ${order.po_reference || "-"}
        </td>
      </tr>

      <tr>
        <td style="padding:10px;border:1px solid #e5e7eb;">
          Delivery Date
        </td>

        <td style="padding:10px;border:1px solid #e5e7eb;">
          ${
            order.delivery_date
              ? new Date(
                  order.delivery_date
                ).toLocaleDateString("en-GB")
              : "-"
          }
        </td>
      </tr>

      <tr>
        <td style="padding:10px;border:1px solid #e5e7eb;">
          Prepared By
        </td>

        <td style="padding:10px;border:1px solid #e5e7eb;">
          ${order.prepared_by_name || "-"}
        </td>
      </tr>

    </table>

    <!-- TOTALS -->

    <div
      style="
        margin-top:24px;
        text-align:right;
      "
    >

      <p>
        Subtotal :
        <strong>
          ₹${subtotal.toFixed(2)}
        </strong>
      </p>

      <p>
        GST :
        <strong>
          ₹${totalGstAmount.toFixed(2)}
        </strong>
      </p>

      <p style="font-size:17px;">
        Total :
        <strong>
          ₹${totalAmount.toFixed(2)}
        </strong>
      </p>

    </div>

    <p style="margin-top:30px;">
      Regards,<br/>
      <strong>Sales Team</strong>
    </p>

  </td>
</tr>

<tr>
  <td
    style="
      background:#f9fafb;
      padding:14px;
      text-align:center;
      font-size:12px;
      color:#6b7280;
    "
  >
    This is an automated email.
  </td>
</tr>


  </table>

</div>
        `,
      });
    }


// =====================================
// RESPONSE
// =====================================

return res.status(201).json({
  success: true,

  message:
    "Sales Order created successfully",

  data: {

    sales_order_id:
      salesOrderId,

    so_number,

    total_amount:
      totalAmount,

    pdf_url:
      `/uploads/sales-orders/${pdfFileName}`,
  },
});


} catch (err) {


await client.query(
  "ROLLBACK"
);

console.error(
  "CREATE SALES ORDER ERROR:",
  err
);

return res.status(500).json({
  success: false,

  message:
    err.message ||
    "Failed to create sales order",
});


} finally {


client.release();


}
};




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

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

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

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

    const {
      customer_code,
      quotation_number,
      delivery_date,
      po_reference,

      with_advance_payment,
      advance_payment_percent,
      advance_payment_amount,
      advance_payment_status,
      proforma_required,

      items,
    } = req.body;

    if (!customer_code) {
      return res.status(400).json({ message: "Customer is required" });
    }

    if (!items || items.length === 0) {
      return res.status(400).json({ message: "Items required" });
    }

    await client.query("BEGIN");

    //  CHECK EXISTING ORDER
    const existing = await client.query(
      `SELECT id FROM sales_orders 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId],
    );

    if (!existing.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ message: "Sales Order not found" });
    }

    //  FETCH CUSTOMER
    const custRes = await client.query(
      `SELECT company_name, billing_address, gst
       FROM customers
       WHERE customer_code = $1 AND org_id = $2`,
      [customer_code, orgId],
    );

    const customer = custRes.rows[0] || {};

    //  UPDATE HEADER
    await client.query(
      `UPDATE sales_orders SET
        customer_id = $1,
        customer_name = $2,
        customer_address = $3,
        customer_gst = $4,
        quotation_number = $5,
        delivery_date = $6,
        po_reference = $7,
        updated_at = NOW(),
        with_advance_payment = $8,
advance_payment_percent = $9,
advance_payment_amount = $10,
advance_payment_status = $11,
proforma_required = $12,
        updated_by = $13
      WHERE id = $14`,
      [
        customer_code,
        customer.company_name || "",
        customer.billing_address || "",
        customer.gst || "",
        quotation_number || null,
        delivery_date || null,
        po_reference || null,

        with_advance_payment || false,

        with_advance_payment ? Number(advance_payment_percent || 0) : 0,

        with_advance_payment ? Number(advance_payment_amount || 0) : 0,

        with_advance_payment ? advance_payment_status || "pending" : null,

        with_advance_payment ? proforma_required || true : false,

        userId,
        id,
      ],
    );

    //  DELETE OLD ITEMS
    await client.query(
      `DELETE FROM sales_order_items WHERE sales_order_id = $1`,
      [id],
    );

    let totalAmount = 0;

    //  INSERT NEW ITEMS
    for (const item of items) {
      const { item_id, item_code, item_name, qty, rate, uom_symbol,gst } = item;

      if (!item_code || !qty || !rate) {
        throw new Error("Invalid item data");
      }

      const quantity = Number(qty);
      const unitRate = Number(rate);

      const baseAmount = quantity * unitRate;
      const gstPercent = gst || 0; // or dynamic later
      const gstAmount = (baseAmount * gstPercent) / 100;
      const totalItemAmount = baseAmount + gstAmount;

      totalAmount += totalItemAmount;

      await client.query(
        `
  INSERT INTO sales_order_items (
    sales_order_id,
     product_id,
     product_code,
    product_name,
    qty,
    rate,
    uom_symbol,
    gst_amount,
    amount,
    tax_percent
  )
  VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
  `,
        [
          id,
          item_id,
          item_code,
          item_name,
          quantity,
          unitRate,
          uom_symbol,
          gstAmount,
          totalItemAmount,
          gstPercent,
        ],
      );
    }

    //  UPDATE TOTAL
    await client.query(
      `UPDATE sales_orders SET total_amount = $1 WHERE id = $2`,
      [totalAmount, id],
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "Sales Order updated successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("UPDATE SO ERROR:", err);

    res.status(500).json({
      success: false,
      message: err.message || "Failed to update sales order",
    });
  } finally {
    client.release();
  }
};

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

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

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

    // 🔹 GET HEADER
    const orderRes = await pool.query(
      //       `
      //  SELECT
      //   so.id,
      //   so.so_number,
      //   so.quotation_number,
      //   so.customer_id,
      //   so.customer_name,
      //   so.customer_address,
      //   so.customer_gst,

      //   -- ✅ FROM CUSTOMER TABLE
      //   c.payment_terms,
      //   c.billing_address,
      //   c.bill_city,
      //   c.bill_state,
      //   c.bill_pin_code,
      //   c.shipping_address,
      //   c.ship_city,
      //   c.ship_state,
      //   c.ship_pin_code,

      //   so.delivery_date,
      //   so.po_reference,
      //   so.total_amount,
      //   so.status
      //      FROM sales_orders so
      // LEFT JOIN customers c
      //   ON c.customer_code = so.customer_id
      //   AND c.org_id = so.org_id

      // WHERE so.id = $1
      //   AND so.org_id = $2
      //   AND so.is_deleted = false`,
      `
SELECT 
  so.id,
  so.so_number,
  so.quotation_number,
  so.customer_id,
  so.customer_name,
  so.customer_address,
  so.customer_gst,

  
  so.with_advance_payment,
  so.advance_payment_percent,
  so.advance_payment_amount,
  so.advance_payment_status,
  so.proforma_required,

  -- ✅ approval fields
  so.approval_status,
  so.approved_at,
  so.created_at,
  so.created_by,
  so.approved_by,

  -- ✅ user names
  created_user.name AS created_by_name,
  approved_user.name AS approved_by_name,

  -- ✅ FROM CUSTOMER TABLE
  c.payment_terms,
  c.billing_address,
  c.bill_city,
  c.bill_state,
  c.bill_pin_code,
  c.shipping_address,
  c.ship_city,
  c.ship_state,
  c.ship_pin_code,

  so.delivery_date,
  so.po_reference,
  so.total_amount,
  so.status

FROM sales_orders so

LEFT JOIN customers c 
  ON c.customer_code = so.customer_id 
  AND c.org_id = so.org_id

LEFT JOIN users created_user
  ON created_user.id = so.created_by

LEFT JOIN users approved_user
  ON approved_user.id = so.approved_by

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

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

    const order = orderRes.rows[0];

    // 🔹 GET ITEMS
    const itemsRes = await pool.query(
      `SELECT 
        id,
          product_id,
        product_code,
        product_name,
        qty,
        rate,
        uom_symbol,
        tax_percent,
        amount
      FROM sales_order_items
      WHERE sales_order_id = $1`,
      [id],
    );

    // 🔹 FORMAT ITEMS (important for frontend)
    const items = itemsRes.rows.map((item) => ({
      id: item.id,
      // product_id: Number(item.product_id),
      product_id: Number(item.product_id),
      item_code: item.product_code,
      item_name: item.product_name,
      qty: Number(item.qty),
      rate: Number(item.rate),
      uom_symbol: item.uom_symbol,
      gst : item.tax_percent,
      amount: Number(item.amount),
    }));

    // 🔹 FINAL RESPONSE (frontend-friendly)
    res.json({
      success: true,
      message: "Sales Order fetched successfully",
      // data: {
      //   id: order.id,
      //   so_number: order.so_number,
      //   quotation_number: order.quotation_number,

      //   customer_id: String(order.customer_id), // ✅ important for select
      //   customer_name: order.customer_name,
      //   customer_address: order.customer_address,
      //   customer_gst: order.customer_gst,

      //   payment_terms: order.payment_terms || "", // ✅ ADD

      //   billing_address: order.billing_address || "",
      //   bill_city: order.bill_city || "",
      //   bill_state: order.bill_state || "",
      //   bill_pin_code: order.bill_pin_code || "",

      //   shipping_address: order.shipping_address || "",
      //   ship_city: order.ship_city || "",
      //   ship_state: order.ship_state || "",
      //   ship_pin_code: order.ship_pin_code || "",

      //   delivery_date: order.delivery_date
      //     ? order.delivery_date.toISOString().split("T")[0] // ✅ date fix
      //     : null,

      //   po_reference: order.po_reference,

      //   total_amount: Number(order.total_amount),
      //   status: order.status,

      //   items, // ✅ array
      // },
      data: {
        id: order.id,

        so_number: order.so_number,

        quotation_number: order.quotation_number,

        customer_id: String(order.customer_id),

        customer_name: order.customer_name,

        customer_address: order.customer_address,

        customer_gst: order.customer_gst,

        approval_status: order.approval_status || "Pending",

        approved_at: order.approved_at,

        created_at: order.created_at,

        created_by: order.created_by,
        approved_by: order.approved_by,

        created_by_name: order.created_by_name || "-",

        approved_by_name: order.approved_by_name || "-",

        payment_terms: order.payment_terms || "",

        billing_address: order.billing_address || "",

        bill_city: order.bill_city || "",

        bill_state: order.bill_state || "",

        bill_pin_code: order.bill_pin_code || "",

        shipping_address: order.shipping_address || "",

        ship_city: order.ship_city || "",

        ship_state: order.ship_state || "",

        ship_pin_code: order.ship_pin_code || "",

     delivery_date: order.delivery_date
  ? order.delivery_date.toLocaleDateString("en-CA")
  : null,

        po_reference: order.po_reference,

        total_amount: Number(order.total_amount),

        status: order.status,

        with_advance_payment: order.with_advance_payment || false,

        advance_payment_percent: Number(order.advance_payment_percent || 0),

        advance_payment_amount: Number(order.advance_payment_amount || 0),

        advance_payment_status: order.advance_payment_status || "pending",

        proforma_required: order.proforma_required || false,

        items,
      },
    });
  } catch (err) {
    console.error("GET SO BY ID ERROR:", err);

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

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

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

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

    await client.query("BEGIN");

    // 🔹 1. GET SALES ORDER
    const soRes = await client.query(
      `SELECT * FROM sales_orders 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId],
    );

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

    const so = soRes.rows[0];

    // 🔹 2. GET ITEMS
    const itemsRes = await client.query(
      `SELECT * FROM sales_order_items 
       WHERE sales_order_id = $1`,
      [id],
    );

    const items = itemsRes.rows;

    if (!items.length) {
      throw new Error("No items found in Sales Order");
    }

    // 🔹 3. 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")}`;

    // 🔹 4. CALCULATE TOTALS
    let subtotal = 0;
    let totalTax = 0;
    let totalAmount = 0;

    // 🔹 5. 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,
        amount,
        total_tax,
        total_amount,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
      RETURNING id`,
      [
        invoiceNumber,
        orgId,
        so.customer_id,
        so.customer_name,
        so.customer_address,
        so.customer_gst,
        new Date(),
        0,
        0,
        0,
        userId,
      ],
    );

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

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

      const amount = qty * rate;
      const gstAmount = (amount * gst) / 100;
      const total = amount + gstAmount;

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

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

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

    // 🔹 8. UPDATE SO STATUS
    await client.query(
      `UPDATE sales_orders 
       SET status = 'invoiced'
       WHERE id = $1`,
      [id],
    );

    await client.query("COMMIT");

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

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

const getOrganizationDetails = async (req, res) => {
  console.log("iudrftyuidrtfyuio");
  try {
    const orgId = req.session?.user?.orgId;

    const result = await pool.query(
      `
      SELECT
        org_name,
        address,
        city,
        state,
        country,
        pincode,
        cin_number,
        gst_number,
        phone,
        email,
        logo
      FROM organizations
      WHERE id = $1
      LIMIT 1
      `,
      [orgId],
    );

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

    const org = result.rows[0];

    return res.json({
      success: true,

      data: {
        name: org.org_name || "ERP Enterprise",

        subtitle: "(An ISO 9001:2015 Certified Company)",

        address: `
${org.address || ""}
${org.city || ""}
${org.state || ""}
${org.country || ""}
${org.pincode || ""}
        `.trim(),

        cin: org.cin_number || "-",

        gst: org.gst_number || "-",

        phone: org.phone || "-",

        email: org.email || "-",

        logo: org.logo || null,
      },
    });
  } catch (err) {
    console.error("GET ORGANIZATION DETAILS ERROR:", err);

    return res.status(500).json({
      success: false,
      message: "Failed to fetch organization details",
      error: err.message,
    });
  }
};

//   const getSalesOrderByNumber = async (req, res) => {
//     try {
//       const { soNumber } = req.params;
//       const orgId = req.session?.user?.orgId;

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

//       // Header Details
//       const orderResult = await pool.query(
//         `
//         SELECT
//           so.id,
//           so.so_number,
//           so.po_reference,
//           so.customer_id,
//           so.customer_name,
//           so.customer_address,
//           so.customer_gst,
//           so.delivery_date,
//           so.total_amount,
//           so.with_advance_payment,
//   so.advance_payment_percent,
//   so.advance_payment_amount,
//   so.advance_payment_status,
//   so.proforma_required,

//           c.payment_terms,
//           c.billing_address,
//           c.bill_city,
//           c.bill_state,
//           c.bill_pin_code,

//           c.shipping_address,
//           c.ship_city,
//           c.ship_state,
//           c.ship_pin_code

//         FROM sales_orders so
//         LEFT JOIN customers c
//           ON c.customer_code = so.customer_id
//           AND c.org_id = so.org_id

//         WHERE so.so_number = $1
//           AND so.org_id = $2
//           AND so.is_deleted = false
//         `,
//         [soNumber, orgId],
//       );

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

//       const order = orderResult.rows[0];

//       let proforma = null;

//   if (order.with_advance_payment) {
//     const proformaRes = await pool.query(
//       `
//       SELECT
//         id,
//         proforma_number,
//         advance_percent,
//         advance_amount,
//         status
//       FROM proforma_invoices
//       WHERE sales_order_id = $1
//         AND is_deleted = false
//       ORDER BY id DESC
//       LIMIT 1
//       `,
//       [order.id]
//     );

//     proforma = proformaRes.rows[0] || null;
//   }

//       // Item Details
//       const itemsResult = await pool.query(
//         `
//         SELECT
//           id,
//           product_id,
//           product_code,
//           product_name,
//           qty,
//           uom_symbol,
//           rate,
//           amount,
//           tax_percent,
//           gst_amount
//         FROM sales_order_items
//         WHERE sales_order_id = $1
//           AND is_deleted = false
//         `,
//         [order.id],
//       );

//       const hasAdvance =
//   order.with_advance_payment === true ||
//   order.with_advance_payment === "true";


//       res.status(200).json({
//         success: true,
//         data: {
//           id: order.id,
//           so_number: order.so_number,
//           po_reference: order.po_reference,
//           // delivery_date: order.delivery_date,

//  delivery_date: hasAdvance
//       ? null
//       : order.delivery_date,
//           customer: {
//             customer_id: order.customer_id,
//             customer_name: order.customer_name,
//             customer_gst: order.customer_gst,
//             customer_address: order.customer_address,
//           },

//           billing_address: {
//             address: order.billing_address,
//             city: order.bill_city,
//             state: order.bill_state,
//             pin_code: order.bill_pin_code,
//           },

//           shipping_address: {
//             address: order.shipping_address,
//             city: order.ship_city,
//             state: order.ship_state,
//             pin_code: order.ship_pin_code,
//           },

//           payment_terms: order.payment_terms,
//           total_amount: Number(order.total_amount),

//           with_advance_payment:
//     order.with_advance_payment || false,

//   advance_payment_percent:
//     Number(order.advance_payment_percent || 0),

//   advance_payment_amount:
//     Number(order.advance_payment_amount || 0),

//   advance_payment_status:
//     order.advance_payment_status || null,

//   proforma_required:
//     order.proforma_required || false,

//   proforma_invoice: proforma
//     ? {
//         id: proforma.id,
//         proforma_number:
//           proforma.proforma_number,

//         advance_percent: Number(
//           proforma.advance_percent || 0
//         ),

//         advance_amount: Number(
//           proforma.advance_amount || 0
//         ),

//         status: proforma.status || "Pending",
//       }
//     : null,

//           items: itemsResult.rows.map((item) => ({
//             product_id: item.product_id,
//             product_code: item.product_code,
//             product_name: item.product_name,
//             qty: Number(item.qty),
//             uom: item.uom_symbol,
//             rate: Number(item.rate),
//             amount: Number(item.amount),
//             tax_percent: Number(item.tax_percent),
//             gst_amount: Number(item.gst_amount),
//           })),
//         },
//       });
//     } catch (error) {
//       console.error("GET SO BY NUMBER ERROR:", error);

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

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

//     await pool.query(
//       `
//       UPDATE sales_orders
//       SET
//         approval_status = 'Approved',
//         approved_at = NOW(),
//         approved_by = $1
//       WHERE id = $2
//       `,
//       [req.user.id, id]
//     );

//       // =========================
//     // 📧 EMAIL AFTER SUCCESS
//     // =========================

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

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

//     if (customer.email) {
//       try {
//         await transporter.sendMail({
//           from: `"Sales Team" <${process.env.MAIL_USER}>`,
//           to: customer.email,
//           subject: `Sales Order ${so_number} Approved`,

//           html: `
// <div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">
//   <table width="100%" cellpadding="0" cellspacing="0" style="max-width:750px;margin:auto;background:#ffffff;border-radius:8px;border:1px solid #e5e7eb;">

//     <!-- HEADER -->
//     <tr style="background:#2563eb;color:#ffffff;">
//       <td style="padding:16px 20px;font-size:18px;font-weight:600;">
//         Sales Order Confirmation
//       </td>
//     </tr>

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

//         <p>Dear <strong>${customer.company_name}</strong>,</p>

//         <p>
//           Thank you for your order. Your <strong>Sales Order (${so_number})</strong> has been successfully created.
//         </p>

//         <!-- ORDER SUMMARY -->
//         <table width="100%" style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">
//           <tr style="background:#f9fafb;">
//             <td colspan="2" style="padding:10px;font-weight:600;">Order Summary</td>
//           </tr>

//           <tr>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">SO Number</td>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">${so_number}</td>
//           </tr>

//           <tr>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">PO Reference</td>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">${po_reference || "-"}</td>
//           </tr>

//           <tr>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Delivery Date</td>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">${formatDate(delivery_date) || "-"}</td>
//           </tr>
//         </table>

//         <!-- ITEMS TABLE -->
//         <table width="100%" style="margin-top:20px;border-collapse:collapse;border:1px solid #e5e7eb;">
//           <tr style="background:#f9fafb;">
//             <th style="padding:8px;border:1px solid #e5e7eb;">Item</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Qty</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Rate</th>
//               <th style="padding:8px;border:1px solid #e5e7eb;">Base Amt</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Tax Amt</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Total</th>
//           </tr>

//           ${items
//             .map((item) => {
//               const base = item.qty * item.rate;
//               const taxPercent = 18;
//               const taxAmt = (base * taxPercent) / 100;
//               const total = base + taxAmt;

//               return `
//               <tr>
//                 <td style="padding:8px;border:1px solid #e5e7eb;">${item.item_name}</td>
//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:center;"> ${item.qty}${item.uom_symbol ? ` (${item.uom_symbol})` : ""}</td>
//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${item.rate}</td>

//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${taxAmt.toFixed(2)}</td>
//                  <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${base}</td>
//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">₹${total.toFixed(2)}</td>
//               </tr>`;
//             })
//             .join("")}
//         </table>

//         <!-- TOTAL -->
//         <p style="margin-top:15px;font-size:15px;">
//           <strong>Total Amount: ₹${totalAmount}</strong>
//         </p>

//         <!-- NOTES -->
//         <p style="margin-top:20px;">
//           Our team will process your order and ensure timely delivery.
//           If you have any questions, feel free to contact our support team.
//         </p>

//         <p>
//           Regards,<br/>
//           <strong>Sales Team</strong>
//           <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:#6b7280;">
//         This is an automated confirmation email. Please do not reply.
//       </td>
//     </tr>

//   </table>
// </div>
// `,
//         });
//       } catch (mailErr) {
//         console.error("SO EMAIL ERROR:", mailErr.message);
//       }
//     }

//     res.status(200).json({
//       success: true,
//       message: "Sales Order Approved Successfully",
//     });
//   } catch (err) {
//     console.error(err);

//     res.status(500).json({
//       success: false,
//       message: "Failed to approve sales order",
//     });
//   }
// };


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

//     const approvedBy = req.session?.user?.id;

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

//     // =========================
//     // ✅ APPROVE ORDER
//     // =========================

//     await pool.query(
//       `
//       UPDATE sales_orders
//       SET
//         approval_status = 'Approved',
//         approved_at = NOW(),
//         approved_by = $1
//       WHERE id = $2
//       `,
//       [approvedBy, id],
//     );

//     // =========================
//     // ✅ FETCH ORDER DETAILS
//     // =========================

//     const orderRes = await pool.query(
//       `
//       SELECT
//         so.id,
//         so.so_number,
//         so.po_reference,
//         so.delivery_date,
//         so.total_amount,
//         so.customer_name,
//         so.customer_address,
//         so.customer_gst,

//         c.email

//       FROM sales_orders so

//       LEFT JOIN customers c
//         ON c.customer_code = so.customer_id
//         AND c.org_id = so.org_id

//       WHERE so.id = $1
//       `,
//       [id],
//     );

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

//     const order = orderRes.rows[0];

//     // =========================
//     // ✅ FETCH ITEMS
//     // =========================

//     const itemsRes = await pool.query(
//       `
//       SELECT
//         product_name,
//         qty,
//         rate,
//         uom_symbol,
//         amount,
//         gst_amount,
//         tax_percent

//       FROM sales_order_items
//       WHERE sales_order_id = $1
//       `,
//       [id],
//     );

//     const items = itemsRes.rows;

//     // =========================
//     // ✅ CALCULATIONS
//     // =========================

//     const totalAmount = Number(order.total_amount || 0);

//     const gstRate = 18 / 100;

//     const subtotal = totalAmount / (1 + gstRate);

//     const gstAmount = totalAmount - subtotal;

//     // =========================
//     // ✅ SEND EMAIL
//     // =========================

//     if (order.email) {
//       const nodemailer = require("nodemailer");

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

//       try {
//         await transporter.sendMail({
//           from: `"Sales Team" <${process.env.MAIL_USER}>`,
//           to: order.email,

//           subject: `Sales Order ${order.so_number} Approved`,

//           html: `
// <div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">

//   <table width="100%" cellpadding="0" cellspacing="0"
//     style="max-width:750px;margin:auto;background:#ffffff;border-radius:8px;border:1px solid #e5e7eb;">

//     <!-- HEADER -->
//     <tr style="background:#16a34a;color:#ffffff;">
//       <td style="padding:16px 20px;font-size:18px;font-weight:600;">
//         Sales Order Approved
//       </td>
//     </tr>

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

//         <p>Dear <strong>${order.customer_name}</strong>,</p>

//         <p>
//           Your Sales Order has been approved successfully.
//         </p>

//         <!-- ORDER SUMMARY -->
//         <table width="100%"
//           style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">

//           <tr style="background:#f9fafb;">
//             <td colspan="2"
//               style="padding:10px;font-weight:600;">
//               Order Summary
//             </td>
//           </tr>

//           <tr>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
//               SO Number
//             </td>

//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
//               ${order.so_number}
//             </td>
//           </tr>

//           <tr>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
//               PO Reference
//             </td>

//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
//               ${order.po_reference || "-"}
//             </td>
//           </tr>

//           <tr>
//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
//               Delivery Date
//             </td>

//             <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
//               ${
//                 order.delivery_date
//                   ? new Date(order.delivery_date).toLocaleDateString("en-GB")
//                   : "-"
//               }
//             </td>
//           </tr>
//         </table>

//         <!-- ITEMS -->
//         <table width="100%"
//           style="margin-top:20px;border-collapse:collapse;border:1px solid #e5e7eb;">

//           <tr style="background:#f9fafb;">
//             <th style="padding:8px;border:1px solid #e5e7eb;">Item</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Qty</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Rate</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Base</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">GST</th>
//             <th style="padding:8px;border:1px solid #e5e7eb;">Total</th>
//           </tr>

//           ${items
//             .map((item) => {
//               const total = Number(item.amount || 0);

//               const base = total / 1.18;

//               const gst = total - base;

//               return `
//               <tr>
//                 <td style="padding:8px;border:1px solid #e5e7eb;">
//                   ${item.product_name}
//                 </td>

//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:center;">
//                   ${item.qty} ${item.uom_symbol || ""}
//                 </td>

//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">
//                   ₹${Number(item.rate).toFixed(2)}
//                 </td>

//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">
//                   ₹${base.toFixed(2)}
//                 </td>

//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">
//                   ₹${gst.toFixed(2)}
//                 </td>

//                 <td style="padding:8px;border:1px solid #e5e7eb;text-align:right;">
//                   ₹${total.toFixed(2)}
//                 </td>
//               </tr>
//               `;
//             })
//             .join("")}
//         </table>

//         <!-- TOTALS -->
//         <div style="margin-top:20px;text-align:right;">

//           <p>
//             Subtotal:
//             <strong>₹${subtotal.toFixed(2)}</strong>
//           </p>

//           <p>
//             GST (18%):
//             <strong>₹${gstAmount.toFixed(2)}</strong>
//           </p>

//           <p style="font-size:16px;">
//             Total Amount:
//             <strong>₹${totalAmount.toFixed(2)}</strong>
//           </p>

//         </div>

//         <p style="margin-top:25px;">
//           Regards,<br/>
//           <strong>Sales Team</strong>
//         </p>

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

//     <!-- FOOTER -->
//     <tr>
//       <td
//         style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#6b7280;">
//         This is an automated email.
//       </td>
//     </tr>

//   </table>
// </div>
// `,
//         });
//       } catch (mailErr) {
//         console.error("MAIL ERROR:", mailErr);
//       }
//     }

//     res.status(200).json({
//       success: true,
//       message: "Sales Order Approved Successfully",
//     });
//   } catch (err) {
//     console.error("APPROVE SO ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: "Failed to approve sales order",
//     });
//   }
// };
// In your salesorderController.js - Update the getSalesOrderByNumber function
const getSalesOrderByNumber = async (req, res) => {
  try {
    const { soNumber } = req.params;
    const orgId = req.session?.user?.orgId;

    const query = `
      SELECT 
        so.*,
        c.customer_code,
        c.company_name as customer_name,
        c.email as customer_email,
        c.phone as customer_phone,
        c.contact_person,
        c.gst as customer_gst,
        c.gstin as customer_gstin,
        c.pan as customer_pan,
        c.billing_address as customer_address,
        c.bill_city as customer_city,
        c.bill_state as customer_state,
        c.bill_pin_code as customer_pincode,
        c.shipping_address,
        c.ship_city,
        c.ship_state,
        c.ship_pin_code,
        c.credit_limit,
        c.payment_terms as customer_payment_terms
      FROM sales_orders so
      LEFT JOIN customers c ON c.customer_code = so.customer_id AND c.org_id = so.org_id
      WHERE so.so_number = $1 
        AND so.org_id = $2 
        AND so.is_deleted = false
    `;
    
    const { rows } = await pool.query(query, [soNumber, orgId]);
    
    if (!rows.length) {
      return res.status(404).json({ success: false, message: "SO not found" });
    }
    
    // Also fetch items
    const itemsQuery = `
      SELECT * FROM sales_order_items 
      WHERE sales_order_id = $1
    `;
    const items = await pool.query(itemsQuery, [rows[0].id]);
    
    return res.json({
      success: true,
      data: {
        ...rows[0],
        items: items.rows
      }
    });
  } catch (error) {
    console.error(error);
    return res.status(500).json({ success: false, message: "Server error" });
  }
};


const fs = require("fs");
const path = require("path");

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

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

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

    // =========================
    // ✅ APPROVE ORDER
    // =========================

    await pool.query(
      `
      UPDATE sales_orders
      SET
        approval_status = 'Approved',
        approved_at = NOW(),
        approved_by = $1
      WHERE id = $2
      `,
      [approvedBy, id]
    );

    // =========================
    // ✅ FETCH ORDER DETAILS
    // =========================

    const orderRes = await pool.query(
      `
      SELECT
        so.id,
        so.so_number,
        so.po_reference,
        so.delivery_date,
        so.total_amount,

        so.customer_id,
        so.customer_name,
        so.customer_address,
        so.customer_gst,

        so.with_advance_payment,
        so.proforma_required,
        so.advance_payment_percent,
        so.advance_payment_amount,
        so.advance_payment_status,

        c.email,
         creator.name AS prepared_by_name,
  approver.name AS approved_by_name

      FROM sales_orders so

      LEFT JOIN customers c
        ON c.customer_code = so.customer_id
        AND c.org_id = so.org_id

        LEFT JOIN users creator
  ON creator.id = so.created_by

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

      WHERE so.id = $1
      `,
      [id]
    );

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

    const order = orderRes.rows[0];

    // =========================
    // ✅ FETCH ITEMS
    // =========================

    const itemsRes = await pool.query(
      `
      SELECT
        product_name,
        qty,
        rate,
        uom_symbol,
        amount,
        gst_amount,
        tax_percent

      FROM sales_order_items
      WHERE sales_order_id = $1
      `,
      [id]
    );

    const items = itemsRes.rows;

    // =========================
    // ✅ CALCULATIONS
    // =========================

    // const totalAmount = Number(order.total_amount || 0);
const totalAmount = Number(order.total_amount || 0);

const totalGstAmount = items.reduce(
  (sum, item) => sum + Number(item.gst_amount || 0),
  0
);

const subtotal = totalAmount - totalGstAmount;

    // =========================
    // ✅ CHECK PROFORMA REQUIRED
    // =========================

    const shouldAttachProforma =
      order.with_advance_payment ||
      order.proforma_required;

    let attachments = [];

    // =========================
    // ✅ CREATE PROFORMA
    // =========================

    if (shouldAttachProforma) {
      const proformaNumber = `PI-${Date.now()}`;

        const folderPath = path.join(
    __dirname,
    "../../uploads/proforma"
  );

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


  const fileName =
    `${proformaNumber}.pdf`;

  const filePath = path.join(
    folderPath,
    fileName
  );

      await pool.query(
        `
        INSERT INTO proforma_invoices (
          proforma_number,
          sales_order_id,
          sales_order_number,
          customer_code,
          customer_name,
          advance_percent,
          advance_amount,
          notes,
          status,
          created_by,
          pdf_path,
          org_id
        )
        VALUES (
          $1,$2,$3,$4,$5,
          $6,$7,$8,$9,$10,$11,$12
        )
        `,
        [
          proformaNumber,
          order.id,
          order.so_number,
          order.customer_id,
          order.customer_name,

          Number(order.advance_payment_percent || 0),

          Number(order.advance_payment_amount || 0),

          "Generated from Sales Order Approval",

          order.advance_payment_status || "Pending",

          approvedBy,
           filePath,
           orgId
        ]
      );

      // =========================
      // ✅ FETCH COMPANY
      // =========================

      const companyRes = await pool.query(`
        SELECT *
        FROM organizations
        LIMIT 1
      `);

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

      // =========================
      //  GENERATE PDF
      // =========================

      const generateProformaPDF = require("../../services/pdf/generateProformaPDF");

      const pdfBuffer = await generateProformaPDF(
        {
          ...order,
          proforma_number: proformaNumber,
        },
        items,
        company
      );




fs.writeFileSync(filePath, pdfBuffer);

      attachments.push({
        filename: `${proformaNumber}.pdf`,
        content: pdfBuffer,
        contentType: "application/pdf",
      });
    }

    // =========================
    // ✅ SEND EMAIL
    // =========================

    if (order.email) {
      const nodemailer = require("nodemailer");

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

      try {
        await transporter.sendMail({
          from: `"Sales Team" <${process.env.MAIL_USER}>`,
          to: order.email,

          subject: `Sales Order ${order.so_number} Approved`,

          attachments,

          html: `
<div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">

  <table width="100%" cellpadding="0" cellspacing="0"
    style="max-width:750px;margin:auto;background:#ffffff;border-radius:8px;border:1px solid #e5e7eb;">

    <tr style="background:#16a34a;color:#ffffff;">
      <td style="padding:16px 20px;font-size:18px;font-weight:600;">
        Sales Order Approved
      </td>
    </tr>

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

        <p>Dear <strong>${order.customer_name}</strong>,</p>

        <p>
          Your Sales Order has been approved successfully.
        </p>

        ${
          shouldAttachProforma
            ? `
            <p>
              Please find attached Proforma Invoice for advance payment processing.
            </p>
          `
            : ""
        }

        <table width="100%"
          style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">

          <tr style="background:#f9fafb;">
            <td colspan="2"
              style="padding:10px;font-weight:600;">
              Order Summary
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              SO Number
            </td>

            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${order.so_number}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              PO Reference
            </td>

            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${order.po_reference || "-"}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              Delivery Date
            </td>

            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${
                order.delivery_date
                  ? new Date(order.delivery_date).toLocaleDateString("en-GB")
                  : "-"
              }
            </td>
          </tr>

          <tr>
  <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
    Prepared By
  </td>

  <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
    ${order.prepared_by_name || "-"}
  </td>
</tr>

<tr>
  <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
    Approved By
  </td>

  <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
    ${order.approved_by_name || "-"}
  </td>
</tr>
        </table>

        <div style="margin-top:20px;text-align:right;">

          <p>
            Subtotal:
            <strong>₹${subtotal.toFixed(2)}</strong>
          </p>

          <p>
            GST:
            <strong>₹${totalGstAmount.toFixed(2)}</strong>
          </p>

          <p style="font-size:16px;">
            Total Amount:
            <strong>₹${totalAmount.toFixed(2)}</strong>
          </p>

        </div>

        <p style="margin-top:25px;">
          Regards,<br/>
          <strong>Sales Team</strong>
        </p>

      </td>
    </tr>

    <tr>
      <td
        style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#6b7280;">
        This is an automated email.
      </td>
    </tr>

  </table>
</div>
`,
        });
      } catch (mailErr) {
        console.error("MAIL ERROR:", mailErr);
      }
    }

    res.status(200).json({
      success: true,
      message: "Sales Order Approved Successfully",
    });
  } catch (err) {
    console.error("APPROVE SO ERROR:", err);

    res.status(500).json({
      success: false,
      message: "Failed to approve sales order",
    });
  }
};






const resendSalesOrderMail =
  async (req, res) => {
    try {

      const { id } = req.params;

      // =========================
      // FETCH ORDER
      // =========================

      const orderRes = await pool.query(
        `
        SELECT
          so.id,
          so.so_number,
          so.po_reference,
          so.delivery_date,
          so.total_amount,

          so.customer_id,
          so.customer_name,
          so.customer_address,
          so.customer_gst,

          so.with_advance_payment,
          so.proforma_required,

          c.email

        FROM sales_orders so

        LEFT JOIN customers c
          ON c.customer_code = so.customer_id
          AND c.org_id = so.org_id

        WHERE so.id = $1
        `,
        [id]
      );

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

      const order = orderRes.rows[0];

      // =========================
      // CALCULATIONS
      // =========================

      const totalAmount =
        Number(order.total_amount || 0);

      const gstRate = 18 / 100;

      const subtotal =
        totalAmount / (1 + gstRate);

      const gstAmount =
        totalAmount - subtotal;

      // =========================
      // FETCH EXISTING PDF
      // =========================

      const proformaRes =
        await pool.query(
          `
          SELECT
            proforma_number,
            pdf_path
          FROM proforma_invoices
          WHERE sales_order_id = $1
          ORDER BY id DESC
          LIMIT 1
          `,
          [id]
        );

      const proforma =
        proformaRes.rows[0];

      // =========================
      // MAIL CONFIG
      // =========================

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

      // =========================
      // SEND MAIL
      // =========================

      await transporter.sendMail({
        from: `"Sales Team" <${process.env.MAIL_USER}>`,

        to: order.email,

        subject:
          `Sales Order ${order.so_number} Approved`,

        attachments: proforma?.pdf_path
          ? [
              {
                filename:
                  `${proforma.proforma_number}.pdf`,

                path:
                  proforma.pdf_path,
              },
            ]
          : [],

        html: `
<div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">

  <table width="100%" cellpadding="0" cellspacing="0"
    style="max-width:750px;margin:auto;background:#ffffff;border-radius:8px;border:1px solid #e5e7eb;">

    <tr style="background:#16a34a;color:#ffffff;">
      <td style="padding:16px 20px;font-size:18px;font-weight:600;">
        Sales Order Approved
      </td>
    </tr>

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

        <p>
          Dear
          <strong>
            ${order.customer_name}
          </strong>,
        </p>

        <p>
          Your Sales Order has been approved successfully.
        </p>

        ${
          proforma
            ? `
            <p>
              Please find attached Proforma Invoice for advance payment processing.
            </p>
          `
            : ""
        }

        <table width="100%"
          style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">

          <tr style="background:#f9fafb;">
            <td colspan="2"
              style="padding:10px;font-weight:600;">
              Order Summary
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              SO Number
            </td>

            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${order.so_number}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              PO Reference
            </td>

            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${order.po_reference || "-"}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              Delivery Date
            </td>

            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${
                order.delivery_date
                  ? new Date(
                      order.delivery_date
                    ).toLocaleDateString("en-GB")
                  : "-"
              }
            </td>
          </tr>
        </table>

        <div style="margin-top:20px;text-align:right;">

          <p>
            Subtotal:
            <strong>
              ₹${subtotal.toFixed(2)}
            </strong>
          </p>

          <p>
            GST (18%):
            <strong>
              ₹${gstAmount.toFixed(2)}
            </strong>
          </p>

          <p style="font-size:16px;">
            Total Amount:
            <strong>
              ₹${totalAmount.toFixed(2)}
            </strong>
          </p>

        </div>

        <p style="margin-top:25px;">
          Regards,<br/>
          <strong>Sales Team</strong>
        </p>

      </td>
    </tr>

    <tr>
      <td
        style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#6b7280;">
        This is an automated email.
      </td>
    </tr>

  </table>
</div>
`,
      });

      res.status(200).json({
        success: true,
        message:
          "Email resent successfully",
      });

    } catch (err) {

      console.error(
        "RESEND MAIL ERROR:",
        err
      );

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


module.exports = {
  getSalesOrders,
  deleteSalesOrder,
  createSalesOrder,
  updateSalesOrder,
  getSalesOrderById,
  createInvoiceFromSO,
  getOrganizationDetails,
  getSalesOrderByNumber,
  approveSalesOrder,
  resendSalesOrderMail,
};
