const { pool } = require("../../config/db");
const nodemailer = require("nodemailer");
const fs = require("fs");
const path = require("path");
const { PDFDocument, StandardFonts } = require("pdf-lib");
const generateSalesQuotationPDF = require("../../services/pdf/generateSalesQuotationPDF"); // create this file

const buildTemplate = require("../../templates/quotationTemplate");
const generatePDF = require("../../utils/generatePDF");

const transporter = nodemailer.createTransport({
  service: "gmail", // or use SMTP config
  auth: {
    user: process.env.MAIL_USER, // your email
    pass: process.env.MAIL_PASS, // app password
  },
});

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

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

//     const { customer_id, items } = req.body;

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

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

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

//     // PREFIX
//     const prefixResult = await client.query(
//       `UPDATE prefixes
//    SET last_number = last_number + 1
//    WHERE module_name = 'sales_quotation'
//      AND org_id = $1
//      AND is_deleted = false
//    RETURNING prefix_code, last_number`,
//       [orgId],
//     );
//     const { prefix_code, last_number } = prefixResult.rows[0];
//     const quotation_number = `${prefix_code}${String(last_number).padStart(3, "0")}`;

//     //  CUSTOMER SNAPSHOT
//     // const customerRes = await client.query(
//     //   `SELECT company_name, billing_address, gst
//     //    FROM customers
//     //    WHERE id = $1 AND is_deleted = false`,
//     //   [customer_id],
//     // );
//     const customerRes = await client.query(
//       `SELECT company_name, billing_address, gst
//    FROM customers
//    WHERE customer_code = $1 AND is_deleted = false`,
//       [customer_id],
//     );

//     if (customerRes.rows.length === 0) {
//       throw new Error("Customer not found");
//     }

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

//     //  INSERT HEADER
//     const quotationRes = await client.query(
//       `INSERT INTO sales_quotations (
//         quotation_number,
//         customer_id,
//         customer_name,
//         customer_address,
//         customer_gst,
//         org_id,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7)
//       RETURNING id`,
//       [
//         quotation_number,
//         customer_id,
//         customer.company_name,
//         customer.billing_address,
//         customer.gst,
//         orgId,
//         userId,
//       ],
//     );

//     const quotation_id = quotationRes.rows[0].id;

//     // BULK INSERT ITEMS
//     let grandTotal = 0;

//     const values = [];
//     const placeholders = [];

//     items.forEach((item, i) => {
//       const amount = item.qty * item.rate;
//       grandTotal += amount;

//       const baseIndex = i * 10;

//       placeholders.push(
//         `($${baseIndex + 1},$${baseIndex + 2},$${baseIndex + 3},$${baseIndex + 4},$${baseIndex + 5},$${baseIndex + 6},$${baseIndex + 7},$${baseIndex + 8},$${baseIndex + 9},$${baseIndex + 10})`,
//       );

//       values.push(
//         quotation_id,
//         item.product_id,
//         item.product_code,
//         item.product_name,
//         item.description,
//         item.qty,
//         item.uom_id,
//         item.uom_symbol,
//         item.rate,
//         amount,
//       );
//     });

//     await client.query(
//       `INSERT INTO sales_quotation_items (
//         quotation_id,
//         product_id,
//         product_code,
//         product_name,
//         description,
//         qty,
//         uom_id,
//         uom_symbol,
//         rate,
//         amount
//       ) VALUES ${placeholders.join(",")}`,
//       values,
//     );

//     //  UPDATE TOTAL
//     await client.query(
//       `UPDATE sales_quotations
//        SET grand_total = $1
//        WHERE id = $2`,
//       [grandTotal, quotation_id],
//     );

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

//     res.status(201).json({
//       message: "Quotation created successfully",
//       quotation_number,
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error(error);

//     res.status(500).json({
//       message: error.message || "Failed to create quotation",
//     });
//   } finally {
//     client.release();
//   }
// };
const createQuotation = async (req, res) => {
  const client = await pool.connect();

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

    const { customer_id, items } = req.body;

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

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

    await client.query("BEGIN");

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

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

    // 🔹 CUSTOMER SNAPSHOT
    const customerRes = await client.query(
      `SELECT company_name, billing_address, gst
       FROM customers
       WHERE customer_code = $1 AND is_deleted = false`,
      [customer_id],
    );

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

    const customer = customerRes.rows[0];

    // 🔹 INSERT HEADER
    const quotationRes = await client.query(
      `INSERT INTO sales_quotations (
        quotation_number,
        customer_id,
        customer_name,
        customer_address,
        customer_gst,
        org_id,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7)
      RETURNING id`,
      [
        quotation_number,
        customer_id,
        customer.company_name,
        customer.billing_address,
        customer.gst,
        orgId,
        userId,
      ],
    );

    const quotation_id = quotationRes.rows[0].id;

    // 🔹 ITEMS INSERT
    let grandTotal = 0;
    const values = [];
    const placeholders = [];

    items.forEach((item, i) => {
      const amount = Number(item.qty) * Number(item.rate);
      const gstPercent = 18;
      const gstAmount = (amount * gstPercent) / 100;
      const totalAmount = amount + gstAmount;

      grandTotal += totalAmount;

      const baseIndex = i * 12;

      placeholders.push(
        `($${baseIndex + 1},$${baseIndex + 2},$${baseIndex + 3},$${baseIndex + 4},$${baseIndex + 5},$${baseIndex + 6},$${baseIndex + 7},$${baseIndex + 8},$${baseIndex + 9},$${baseIndex + 10},$${baseIndex + 11}, $${baseIndex + 12})`,
      );

      values.push(
        quotation_id,
        item.product_id,
        item.product_code,
        item.product_name,
        item.description,
        item.qty,
        item.uom_id,
        item.uom_symbol,
        item.rate,
        amount, // ✅ GST INCLUDED
        18, // tax_percent
        totalAmount,
      );
    });

    await client.query(
      `INSERT INTO sales_quotation_items (
        quotation_id,
        product_id,
        product_code,
        product_name,
        description,
        qty,
        uom_id,
        uom_symbol,
        rate,
        amount,
        tax_percent,
        total_amount
      ) VALUES ${placeholders.join(",")}`,
      values,
    );

    // 🔹 UPDATE TOTAL
    await client.query(
      `UPDATE sales_quotations
       SET grand_total = $1
       WHERE id = $2`,
      [grandTotal, quotation_id],
    );

    await client.query("COMMIT");

    res.status(201).json({
      message: "Quotation created successfully",
      quotation_number,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error(error);

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

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

  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;

    //  MAIN QUERY (FIXED JOINS)
    const result = await client.query(
      `
      SELECT 
        sq.id,
        sq.quotation_number,
        sq.customer_name, 
        sq.customer_id,
        sq.status,
          sq.created_at,        -- ✅ ADD
 u.name AS created_by_name,   -- ✅ FIX

        sqi.product_code,
        sqi.product_name,
        sqi.description,

        sp.unit AS uom,
        sp.hsn_code,

        sqi.rate,
        sqi.total_amount

      FROM sales_quotation_items sqi

      INNER JOIN sales_quotations sq
        ON sq.id = sqi.quotation_id

      LEFT JOIN sales_products sp
        ON sqi.product_id = sp.id

        LEFT JOIN users u               -- ✅ ADD THIS
  ON u.id = sq.created_by

      WHERE sq.org_id = $1

      ORDER BY sq.id DESC

      LIMIT $2 OFFSET $3
      `,
      [orgId, limit, offset],
    );

    //  COUNT FIX (COUNT ITEMS NOT QUOTATIONS)
    const countRes = await client.query(
      `
      SELECT COUNT(*) 
      FROM sales_quotation_items sqi
      INNER JOIN sales_quotations sq
        ON sq.id = sqi.quotation_id
      WHERE sq.org_id = $1
      `,
      [orgId],
    );

    //  FORMAT RESPONSE
    const data = result.rows.map((row) => ({
      id: row.id,
      rfq_id: row.quotation_number || "-",
      customer_name: row.customer_name || "-",
      customer_code: row.customer_id || "-",
      status: row.status || "-",
      created_at: row.created_at, //  ADD
      created_by: row.created_by_name, // ✅ ADD
      product_code: row.product_code || "-",
      product: row.product_name || "-",
      category: "-", // not in your DB
      uom: row.uom || "-",
      hsn_code: row.hsn_code || "-",
      reorder_level: "-", // not available
      preferred_vendor: "-", // not available
      rate: row.rate ? `₹${row.rate}` : "-",
      amount: row.total_amount || "-",
    }));

    res.status(200).json({
      message: "Quotations fetched successfully",
      data,
      pagination: {
        total: parseInt(countRes.rows[0].count),
        page,
        limit,
      },
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({
      message: "Failed to fetch quotations",
    });
  } finally {
    client.release();
  }
};

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

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

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

//     // HEADER
//     const headerRes = await client.query(
//       `
//       SELECT
//         id,
//         quotation_number,
//         customer_name,
//         customer_address,
//         customer_gst,
//         created_at,
//         grand_total,
//         status
//       FROM sales_quotations
//       WHERE id = $1 AND org_id = $2
//       `,
//       [id, orgId],
//     );

//     if (headerRes.rows.length === 0) {
//       return res.status(404).json({ message: "Quotation not found" });
//     }

//     const header = headerRes.rows[0];

//     // ITEMS
//     const itemsRes = await client.query(
//       `
//       SELECT
//         sqi.product_id,
//         sqi.product_code,
//         sqi.product_name,
//         sqi.description,
//         sqi.qty,
//         sqi.uom_symbol,
//         sqi.rate,
//         sqi.amount
//       FROM sales_quotation_items sqi
//       WHERE sqi.quotation_id = $1
//       `,
//       [header.id],
//     );

//     res.status(200).json({
//       message: "Quotation fetched successfully",
//       data: {
//         header: {
//           quotation_number: header.quotation_number,
//           customer_name: header.customer_name,
//           customer_address: header.customer_address,
//           customer_gst: header.customer_gst,
//           date: header.created_at,
//           grand_total: header.grand_total,
//           status: header.status,
//         },
//         items: itemsRes.rows.map((item) => ({
//           product_id: item.product_id,
//           code: item.product_code,
//           name: item.product_name,
//           description: item.description,
//           qty: item.qty,
//           unit: item.uom_symbol,
//           unitPrice: item.rate,
//           amount: item.amount,
//         })),
//       },
//     });
//   } catch (err) {
//     console.error(err);
//     res.status(500).json({ message: "Failed to fetch quotation" });
//   } finally {
//     client.release();
//   }
// };

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

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

//     const items = JSON.parse(req.body.items); // from frontend

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

//     // 1 DELETE OLD ITEMS (optional strategy)
//     await client.query(
//       `DELETE FROM sales_quotation_items WHERE quotation_id = $1`,
//       [id]
//     );

//     // 2 INSERT UPDATED ITEMS
//     let grandTotal = 0;

//     for (let item of items) {
//       const amount = item.qty * item.rate;
//       grandTotal += amount;

//       await client.query(
//         `INSERT INTO sales_quotation_items (
//           quotation_id,
//           product_code,
//           product_name,
//           description,
//           qty,
//           uom_symbol,
//           rate,
//           amount
//         ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8)`,
//         [
//           id,
//           item.code,
//           item.name,
//           item.description,
//           item.qty,
//           item.unit,
//           item.rate,
//           amount,
//         ]
//       );
//     }

//     // 3 UPDATE TOTAL
//     await client.query(
//       `UPDATE sales_quotations SET grand_total = $1 WHERE id = $2`,
//       [grandTotal, id]
//     );

//     // 4 HANDLE FILE UPLOAD
//     if (req.files && req.files.length > 0) {
//       for (let file of req.files) {
//         await client.query(
//           `INSERT INTO sales_quotation_files (
//             quotation_id,
//             file_name,
//             file_url,
//             file_type
//           ) VALUES ($1,$2,$3,$4)`,
//           [
//             id,
//             file.originalname,
//             `/uploads/quotations/${file.filename}`,
//             file.mimetype,
//           ]
//         );
//       }
//     }

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

//     res.json({
//       message: "Quotation updated & PO uploaded successfully",
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error(err);
//     res.status(500).json({ message: "Failed to upload quotation" });
//   } finally {
//     client.release();
//   }
// };

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

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

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

//     const items = JSON.parse(req.body.items);

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

//     //  STEP 1: DELETE OLD ITEMS
//     await client.query(
//       `DELETE FROM sales_quotation_items WHERE quotation_id = $1`,
//       [id],
//     );

//     //  STEP 2: INSERT UPDATED ITEMS
//     let grandTotal = 0;

//     for (let item of items) {
//       const amount = item.qty * item.rate;
//       grandTotal += amount;

//       await client.query(
//         `INSERT INTO sales_quotation_items (
//           quotation_id,
//            product_id,
//           product_code,
//           product_name,
//           description,
//           qty,
//           uom_symbol,
//           rate,
//           amount
//         ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8, $9)`,
//         [
//           id,
//           item.product_id,
//           item.code,
//           item.name,
//           item.description,
//           item.qty,
//           item.unit,
//           item.rate,
//           amount,
//         ],
//       );
//     }

//     //  STEP 3: UPDATE TOTAL

//     await client.query(
//       `UPDATE sales_quotations
//    SET grand_total = $1, status = 'uploaded'
//    WHERE id = $2`,
//       [grandTotal, id],
//     );

//     //  STEP 4: STORE FILES
//     // if (req.files?.length > 0) {
//     //   for (let file of req.files) {
//     //     await client.query(
//     //       `INSERT INTO sales_quotation_files (
//     //         quotation_id,
//     //         file_name,
//     //         file_url,
//     //         file_type
//     //       ) VALUES ($1,$2,$3,$4)`,
//     //       [
//     //         id,
//     //         file.originalname,
//     //         `/uploads/quotations/${file.filename}`,
//     //         file.mimetype,
//     //       ]
//     //     );
//     //   }
//     // }
//     if (req.file) {
//       const file = req.file;

//       const relativePath = file.path.split("uploads")[1];

//       const fileUrl = `/uploads${relativePath.replace(/\\/g, "/")}`;

//       await client.query(
//         `INSERT INTO sales_quotation_files (
//       quotation_id,
//       file_name,
//       file_url,
//       file_type
//     ) VALUES ($1,$2,$3,$4)`,
//         [
//           id,
//           file.originalname,
//           fileUrl,
//           // file.path.replace(/\\/g, "/"), // important for windows
//           file.mimetype,
//         ],
//       );
//     }

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

//     res.json({
//       message: "Quotation updated & PO uploaded successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error(err);
//     res.status(500).json({ message: "Failed to upload quotation" });
//   } finally {
//     client.release();
//   }
// };

// const uploadQuotation = 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" });
//     }

//     const { po_reference } = req.body;
//     const items = JSON.parse(req.body.items || "[]");

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

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

//     // 🔹 STEP 1: GET QUOTATION
//     const quotationRes = await client.query(
//       `SELECT * FROM sales_quotations WHERE id = $1 AND org_id = $2`,
//       [id, orgId],
//     );

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

//     const quotation = quotationRes.rows[0];

//     // 🔹 STEP 2: DELETE OLD ITEMS
//     await client.query(
//       `DELETE FROM sales_quotation_items WHERE quotation_id = $1`,
//       [id],
//     );

//     // 🔹 STEP 3: INSERT UPDATED ITEMS
//     let grandTotal = 0;

//     for (let item of items) {
//       // const amount = Number(item.qty) * Number(item.rate);
//       // grandTotal += amount;
//       const baseAmount = Number(item.qty) * Number(item.rate);
// const gstPercent = 18;
// const gstAmount = (baseAmount * gstPercent) / 100;
// const totalAmount = baseAmount + gstAmount;

// grandTotal += totalAmount;

//       await client.query(
//         `INSERT INTO sales_quotation_items (
//             quotation_id,
//             product_id,
//             product_code,
//             product_name,
//             description,
//             qty,
//             uom_symbol,
//             rate,
//             amount,
//             tax_percent,
//           ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
//         [
//           id,
//           item.product_id,
//           item.code,
//           item.name,
//           item.description || null,
//           item.qty,
//           item.unit,
//           item.rate,
//           amount,
//           18,
//         ],
//       );
//     }

//     // 🔹 STEP 4: UPDATE QUOTATION (TOTAL + STATUS + PO)
//     await client.query(
//       `UPDATE sales_quotations
//         SET grand_total = $1,
//             status = 'uploaded',
//             po_reference = $3,
//             updated_at = NOW()
//         WHERE id = $2`,
//       [grandTotal, id, po_reference || null],
//     );

//     // 🔹 STEP 5: STORE FILE
//     let fileUrl = null;
//     let fileName = null;

//     if (req.file) {
//       const file = req.file;

//       const relativePath = file.path.split("uploads")[1];
//       fileUrl = `/uploads${relativePath.replace(/\\/g, "/")}`;
//       fileName = file.originalname;

//       await client.query(
//         `INSERT INTO sales_quotation_files (
//             quotation_id,
//             file_name,
//             file_url,
//             file_type
//           ) VALUES ($1,$2,$3,$4)`,
//         [id, fileName, fileUrl, file.mimetype],
//       );
//     }

//     // 🔹 STEP 6: GENERATE SO NUMBER (PREFIX BASED)
//     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`,
//       [orgId],
//     );

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

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

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

//     // 🔹 STEP 7: CREATE SALES ORDER
//     const salesOrderRes = await client.query(
//       `INSERT INTO sales_orders (
//           quotation_id,
//           quotation_number,
//           so_number,
//           po_reference,
//           customer_id,
//           customer_name,
//           customer_address,
//           customer_gst,
//           total_amount,
//           status,
//           po_file_url,
//           po_file_name,
//           org_id,
//           created_by
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
//         RETURNING id`,
//       [
//         id,
//         quotation.quotation_number,
//         so_number,
//         po_reference || null,
//         quotation.customer_id,
//         quotation.customer_name,
//         quotation.customer_address,
//         quotation.customer_gst,
//         grandTotal,
//         "open",
//         fileUrl,
//         fileName,
//         orgId,
//         userId,
//       ],
//     );

//     const sales_order_id = salesOrderRes.rows[0].id;

//     // 🔹 STEP 8: INSERT SALES ORDER ITEMS (SNAPSHOT)
//     for (let item of items) {
//       const amount = Number(item.qty) * Number(item.rate);

//       await client.query(
//         `INSERT INTO sales_order_items (
//             sales_order_id,
//             product_id,
//             product_code,
//             product_name,
//             description,
//             qty,
//             uom_symbol,
//             rate,
//             amount
//           ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)`,
//         [
//           sales_order_id,
//           item.product_id,
//           item.code,
//           item.name,
//           item.description || null,
//           item.qty,
//           item.unit,
//           item.rate,
//           amount,
//         ],
//       );
//     }

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

//     res.status(200).json({
//       success: true,
//       message: "Quotation uploaded & Sales Order created successfully",
//       data: {
//         sales_order_id,
//         so_number,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("UPLOAD QUOTATION ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: err.message || "Failed to upload quotation",
//     });
//   } finally {
//     client.release();
//   }
// };
const getQuotationById = async (req, res) => {
  const client = await pool.connect();

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

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

    //  HEADER WITH AUDIT
    const headerRes = await client.query(
      `
      SELECT 
        sq.id,
        sq.quotation_number,
        sq.customer_name,
        sq.customer_address,
        sq.customer_gst,
        sq.created_at,
        sq.updated_at,
        sq.approved_at,
        sq.grand_total,
        sq.status,

        uc.name AS created_by_name,
        uu.name AS updated_by_name,
        ua.name AS approved_by_name

      FROM sales_quotations sq

      LEFT JOIN users uc ON uc.id = sq.created_by
      LEFT JOIN users uu ON uu.id = sq.updated_by
      LEFT JOIN users ua ON ua.id = sq.approved_by

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

    if (!headerRes.rows.length) {
      return res.status(404).json({ message: "Quotation not found" });
    }

    const header = headerRes.rows[0];

    //  ITEMS
    const itemsRes = await client.query(
      `
      SELECT 
        sqi.product_id,
        sqi.product_code,
        sqi.product_name,
        sqi.description,
        sqi.qty,
        sqi.uom_symbol,
        sqi.rate,
        sqi.amount,
        sqi.total_amount
      FROM sales_quotation_items sqi
      WHERE sqi.quotation_id = $1
      ORDER BY sqi.id ASC
      `,
      [header.id],
    );

    //  RESPONSE
    res.status(200).json({
      success: true,
      message: "Quotation fetched successfully",
      data: {
        header: {
          id: header.id,
          quotation_number: header.quotation_number,

          customer_name: header.customer_name,
          customer_address: header.customer_address,
          customer_gst: header.customer_gst,

          date: header.created_at,
          grand_total: header.grand_total,
          status: header.status,

          // ✅ AUDIT
          created_by_name: header.created_by_name,
          created_at: header.created_at,

          updated_by_name: header.updated_by_name,
          updated_at: header.updated_at,

          approved_by_name: header.approved_by_name,
          approved_at: header.approved_at,
        },

        items: itemsRes.rows.map((item) => ({
          product_id: item.product_id,
          code: item.product_code,
          name: item.product_name,
          description: item.description,
          qty: item.qty,
          unit: item.uom_symbol,
          unitPrice: item.rate,
          amount: item.amount,
          total_amount: item.total_amount,
        })),
      },
    });
  } catch (err) {
    console.error("GET QUOTATION ERROR:", err);

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

// const uploadQuotation = 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" });
//     }

//     const { po_reference } = req.body;
//     const items = JSON.parse(req.body.items || "[]");

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

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

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

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

//     const quotation = quotationRes.rows[0];

//     await client.query(
//       `DELETE FROM sales_quotation_items WHERE quotation_id = $1`,
//       [id],
//     );

//     let grandTotal = 0;

//     for (let item of items) {
//       const baseAmount = Number(item.qty) * Number(item.rate);
//       const gstAmount = (baseAmount * 18) / 100;
//       const totalAmount = baseAmount + gstAmount;

//       grandTotal += totalAmount;

//       await client.query(
//         `INSERT INTO sales_quotation_items (
//           quotation_id,
//           product_id,
//           product_code,
//           product_name,
//           description,
//           qty,
//           uom_symbol,
//           rate,
//           amount,
//           tax_percent
//         ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
//         [
//           id,
//           item.product_id,
//           item.code,
//           item.name,
//           item.description || null,
//           item.qty,
//           item.unit,
//           item.rate,
//           totalAmount, // ✅ FIXED
//           18,
//         ],
//       );
//     }

//     await client.query(
//       `UPDATE sales_quotations
//    SET grand_total = $1,
//        status = 'uploaded',
//        po_reference = $3,
//        updated_at = NOW(),
//        updated_by = $4
//    WHERE id = $2`,
//       [grandTotal, id, po_reference || null, userId],
//     );

//     // 1. Generate SO Number (reuse same logic)
//     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];

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

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

//     const customer_code = quotation.customer_id;

//     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];

//     // 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.quotation_number || null,
//         customer_code,
//         customer.company_name,
//         customer.billing_address,
//         customer.gst,
//         quotation.delivery_date || null,
//         po_reference || null,
//         "open",
//         orgId,
//         userId,
//       ],
//     );

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

//     // 4. Insert SO items
//     let soTotal = 0;

//     for (let item of items) {
//       const base = Number(item.qty) * Number(item.rate);
//       const gst = (base * 18) / 100;
//       const total = base + gst;

//       soTotal += total;

//       await client.query(
//         `
//     INSERT INTO sales_order_items (
//       sales_order_id,
//       product_id,
//       product_name,
//       qty,
//       rate,
//       uom_symbol,
//       gst_amount,
//       amount,
//       tax_percent
//     )
//     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
//     `,
//         [
//           salesOrderId,
//           item.product_id,
//           item.name,
//           item.qty,
//           item.rate,
//           item.unit,
//           gst,
//           total,
//           18,
//         ],
//       );
//     }

//     // 5. Update SO total
//     await client.query(
//       `UPDATE sales_orders SET total_amount = $1 WHERE id = $2`,
//       [soTotal, salesOrderId],
//     );

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

//     res.status(200).json({
//       success: true,
//       message: "Quotation uploaded successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("UPLOAD QUOTATION ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: err.message || "Failed to upload quotation",
//     });
//   } finally {
//     client.release();
//   }
// };
const uploadQuotation = 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" });
    }

    const { po_reference } = req.body;
    const items = JSON.parse(req.body.items || "[]");

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

    await client.query("BEGIN");

    // 🔹 1. Get quotation
    const quotationRes = await client.query(
      `SELECT * FROM sales_quotations WHERE id = $1 AND org_id = $2 FOR UPDATE`,
      [id, orgId],
    );

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

    const quotation = quotationRes.rows[0];

    // 🔥 Prevent duplicate SO
    if (quotation.sales_order_id) {
      throw new Error("Sales Order already created for this quotation");
    }

    // 🔹 2. Delete old items
    await client.query(
      `DELETE FROM sales_quotation_items WHERE quotation_id = $1`,
      [id],
    );

    // 🔹 3. Insert new quotation items
    let grandTotal = 0;

    for (let item of items) {
      console.log(item, "am i getting here........")
      const qty = Number(item.qty);
      const rate = Number(item.rate);

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

      const base = qty * rate;
      const gst = (base * 18) / 100;
      const total = base + gst;

      grandTotal += total;

      // await client.query(
      //   `INSERT INTO sales_quotation_items (
      //     quotation_id,
      //     product_id,
      //     product_code,
      //     product_name,
      //     description,
      //     qty,
      //     uom_symbol,
      //     rate,
      //     amount,
      //     tax_percent
      //   ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
      //   [
      //     id,
      //     item.product_id,
      //     item.code || null,
      //     item.name,
      //     item.description || null,
      //     qty,
      //     item.uom_symbol || item.unit || null,
      //     rate,
      //     base,
      //     18,
      //   ],
      // );
     await client.query(
  `INSERT INTO sales_quotation_items (
    quotation_id,
    product_id,
    product_code,
    product_name,
    description,
    qty,
    uom_symbol,
    rate,
    amount,
    tax_percent,
    total_amount
  ) VALUES (
    $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11
  )`,
  [
    id,
    item.product_id,
    item.code || null,
    item.name,
    item.description || null,
    qty,
    item.uom_symbol || item.unit || null,
    rate,
    base,
    18,
    total, // ✅ IMPORTANT
  ],
);
    }

    // 🔹 4. Update quotation
    await client.query(
      `UPDATE sales_quotations 
       SET grand_total = $1,
           status = 'uploaded',
           po_reference = $3,
           updated_at = NOW(),
           updated_by = $4
       WHERE id = $2`,
      [grandTotal, id, po_reference || null, userId],
    );

    // 🔹 5. Generate SO number (SAFE)
    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];

    // 🔥 Safe next number logic
    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;
      const numericPart = parseInt(lastSo.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],
    );

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

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

    const customer = custRes.rows[0];

    // 🔹 7. Create 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.quotation_number || null,
        quotation.customer_id,
        customer.company_name,
        customer.billing_address,
        customer.gst,
        quotation.delivery_date || null,
        po_reference || null,
        "open",
        orgId,
        userId,
      ],
    );

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

    // 🔹 8. Insert SO items
    let soTotal = 0;

    for (let item of items) {
      const qty = Number(item.qty);
      const rate = Number(item.rate);

      const base = qty * rate;
      const gst = (base * 18) / 100;
      const total = base + gst;

      soTotal += total;

      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.product_id,
          item.code,
          item.name,
          qty,
          rate,
          item.uom_symbol || item.unit || null,
          gst,
          total,
          18,
        ],
      );
    }

    // 🔹 9. Update SO total
    await client.query(
      `UPDATE sales_orders SET total_amount = $1 WHERE id = $2`,
      [soTotal, salesOrderId],
    );

    // 🔥 10. Link quotation → SO
    await client.query(
      `UPDATE sales_quotations 
       SET sales_order_id = $1 
       WHERE id = $2`,
      [salesOrderId, id],
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Quotation uploaded & Sales Order created successfully",
      data: {
        sales_order_id: salesOrderId,
        so_number,
        total_amount: soTotal,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");

    console.error("UPLOAD QUOTATION ERROR:", err);

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

// const approveQuotation = 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");

//     const qRes = await client.query(
//       `
//   SELECT
//     sq.id,
//     sq.quotation_number,
//     sq.customer_name,
//     c.email AS customer_email,
//     sq.status
//   FROM sales_quotations sq
//   LEFT JOIN customers c
//     ON c.customer_code = sq.customer_id
//   WHERE sq.id = $1
//     AND sq.org_id = $2
//   `,
//       [id, orgId],
//     );

//     if (qRes.rows.length === 0) {
//       throw new Error("Quotation not found");
//     }

//     const quotation = qRes.rows[0];

//     //  Prevent duplicate approval
//     if (quotation.status === "approved") {
//       throw new Error("Already approved");
//     }

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

//     // =========================
//     //  SEND EMAIL
//     // =========================
//     // if (quotation.customer_email) {
//     await transporter.sendMail({
//       from: `"Sales Team" <${process.env.MAIL_USER}>`,
//       to: quotation.customer_email,
//       // to: "vittal.yadav9133@gmail.com",
//       subject: `Quotation ${quotation.quotation_number} Approved`,
//       html: `
//   <div style="font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto;">

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

//     <p>Your quotation <strong>${quotation.quotation_number}</strong> has been approved successfully.</p>

//     <div style="background-color: #f5f5f5; padding: 15px; border-radius: 5px; margin: 15px 0;">
//       <h4 style="margin-top: 0;">Quotation Details:</h4>
//       <p><strong>Quotation Number:</strong> ${quotation.quotation_number}</p>
//       <p><strong>Status:</strong> Approved</p>
//     </div>

//     <p>You can now proceed with further steps.</p>

//     <div style="background-color: #e7f3ff; padding: 10px; border-radius: 5px; margin: 15px 0;">
//       <strong>📌 Note:</strong> Please review the quotation and continue with order processing if applicable.
//     </div>

//     <br/>
//     <p>Regards,<br/><strong>Sales Team</strong></p>

//     <hr style="margin: 20px 0;" />
//     <p style="font-size: 12px; color: #666;">This is an automated email.</p>
//   </div>
// `,
//     });
//     // }

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

//     res.json({
//       success: true,
//       message: "Quotation approved & email sent successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("APPROVE ERROR:", err);

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

// const approveQuotation = 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 QUOTATION
//     const qRes = await client.query(
//       `
//       SELECT
//         sq.id,
//         sq.quotation_number,
//         sq.customer_name,
//         c.email AS customer_email,
//         sq.status
//       FROM sales_quotations sq
//       LEFT JOIN customers c
//         ON c.customer_code = sq.customer_id
//       WHERE sq.id = $1
//         AND sq.org_id = $2
//       `,
//       [id, orgId]
//     );

//     if (!qRes.rows.length) throw new Error("Quotation not found");

//     const quotation = qRes.rows[0];

//     if (quotation.status === "approved") {
//       throw new Error("Already approved");
//     }

//     // 🔹 2. GET ITEMS
//     const itemsRes = await client.query(
//       `
//       SELECT product_name, qty, rate
//       FROM sales_quotation_items
//       WHERE quotation_id = $1
//       `,
//       [id]
//     );

//     const items = itemsRes.rows;

//     if (!items.length) throw new Error("No items found");

//     // 🔹 3. UPDATE STATUS
//     await client.query(
//       `UPDATE sales_quotations
//        SET status = 'approved',
//            approved_at = NOW(),
//            approved_by = $1,
//            updated_at = NOW()
//        WHERE id = $2`,
//       [userId, id]
//     );

//     // 🔹 4. GENERATE PDF
//     const pdfBytes = await generateSalesQuotationPDF(
//       quotation,
//       items,
//       {
//         name: "ERP Enterprise",
//         address: "1-8-303/48/15, KRK House, P.G. Road, Begumpet, Hyderabad – 500003, Telangana, India",
//       }
//     );

//     // 🔹 5. SAVE TEMP FILE
//     const tempDir = path.join(__dirname, "../../temp");
//     if (!fs.existsSync(tempDir)) {
//       fs.mkdirSync(tempDir, { recursive: true });
//     }

//     const fileName = `Quotation-${quotation.quotation_number}.pdf`;
//     const filePath = path.join(tempDir, fileName);

//     fs.writeFileSync(filePath, pdfBytes);

//     // 🔹 6. SEND EMAIL WITH PDF
//     if (quotation.customer_email) {
//       await transporter.sendMail({
//         from: `"Sales Team" <${process.env.MAIL_USER}>`,
//         to: quotation.customer_email,
//         subject: `Quotation ${quotation.quotation_number} Approved`,
//         html: `
//           <div style="font-family: Arial, sans-serif;">
//             <p>Dear <strong>${quotation.customer_name}</strong>,</p>

//             <p>Your quotation <strong>${quotation.quotation_number}</strong> has been approved.</p>

//             <p>Please find the attached quotation PDF.</p>

//             <p>Regards,<br/>Sales Team</p>
//           </div>
//         `,
//         attachments: [
//           {
//             filename: fileName,
//             path: filePath,
//           },
//         ],
//       });
//     }

//     // 🔹 7. CLEANUP FILE (optional)
//     setTimeout(() => {
//       if (fs.existsSync(filePath)) {
//         fs.unlinkSync(filePath);
//       }
//     }, 10000);

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

//     res.json({
//       success: true,
//       message: "Quotation approved & PDF sent successfully",
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("APPROVE ERROR:", err);

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

const approveQuotation = 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 QUOTATION
    const qRes = await client.query(
      `
      SELECT 
        sq.id,
        sq.quotation_number,
        sq.customer_name,
          sq.customer_name,
  sq.customer_address,
  sq.customer_gst,
        c.email AS customer_email,
        sq.status
      FROM sales_quotations sq
      LEFT JOIN customers c 
        ON c.customer_code = sq.customer_id 
      WHERE sq.id = $1 
        AND sq.org_id = $2
      `,
      [id, orgId],
    );

    if (!qRes.rows.length) throw new Error("Quotation not found");

    const quotation = qRes.rows[0];

    if (quotation.status === "approved") {
      throw new Error("Already approved");
    }

    // 🔹 2. GET ITEMS
    const itemsRes = await client.query(
      `
      SELECT product_name, qty, rate, uom_symbol, (qty * rate) AS amount
      FROM sales_quotation_items
      WHERE quotation_id = $1
      `,
      [id],
    );

    const items = itemsRes.rows;

    if (!items.length) throw new Error("No items found");

    // =========================
    // 🔹 3. GET COMPANY
    // =========================
    const companyRes = await client.query(
      `
      SELECT 
        org_name,
        address,
        gstin,
        cin,
        phone,
        email,
        bank_details
      FROM organizations
      WHERE id = $1
      `,
      [orgId],
    );

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

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

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

    // =========================
    // 🔹 5. CALCULATE SUMMARY
    // =========================
    const subtotal = items.reduce((acc, i) => acc + Number(i.amount), 0);
    const discount = 0;
    const net = subtotal - discount;

    const gstPercent = 18;
    const gst = net * (gstPercent / 100);

    const total = net + gst;

    // =========================
    // 🔹 6. BUILD PDF DATA
    // =========================
    // const pdfData = {
    //   customer_name: quotation.customer_name,
    //   customer_address: quotation.customer_address,
    //   customer_gst: quotation.customer_gst,

    //   invoice_number: quotation.quotation_number,
    //   invoice_date: quotation.created_at,
    //   order_no: "-",
    //   po_no: "-",
    //   po_date: "-",

    //   gross: subtotal,
    //   discount,
    //   net_value: net,
    //   cgst: gst / 2,
    //   sgst: gst / 2,
    //   round_off: 0,
    //   total,

    //   prepared_by: currentUser.name || "-",
    //   verified_by: currentUser.name || "-",
    //   approved_by: currentUser.name || "-",
    //   authorized_signatory: company.org_name || "-",
    // };
    const pdfData = {
      // 🔹 CUSTOMER
      customer_name: quotation.customer_name || "-",
      customer_address: quotation.customer_address || "-",
      customer_gst: quotation.customer_gst || "-",

      // 🔹 ORDER DETAILS
      order_number: quotation.quotation_number,
      order_date: quotation.created_at,
      ref_no: "-", // optional
      ref_date: quotation.created_at,

      // 🔹 SUMMARY
      gross: subtotal,
      discount,
      net_value: net,
      cgst: gst / 2,
      sgst: gst / 2,
      round_off: 0,
      total,

      // 🔹 USERS
      prepared_by: currentUser.name || "-",
      verified_by: currentUser.name || "-",
      approved_by: currentUser.name || "-",
    };

    // =========================
    // 🔹 7. GENERATE PDF
    // =========================
    // const pdfBuffer = await generateSalesQuotationPDF(
    //   pdfData,
    //   items,
    //   company
    // );

    // if (!pdfBuffer) {
    //   throw new Error("PDF generation failed");
    // }

    let pdfBuffer = null;

    try {
      pdfBuffer = await generateSalesQuotationPDF(pdfData, items, company);
    } catch (pdfErr) {
      console.error("PDF ERROR:", pdfErr);
    }

    // 🔹 3. UPDATE STATUS
    await client.query(
      `UPDATE sales_quotations
       SET status = 'approved',
           approved_at = NOW(),
           approved_by = $1,
           updated_at = NOW()
       WHERE id = $2`,
      [userId, id],
    );

    if (quotation.customer_email) {
      await transporter.sendMail({
        from: `"Sales Team" <${process.env.MAIL_USER}>`,
        to: quotation.customer_email,
        subject: `Quotation ${quotation.quotation_number} Sent`,

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

    <!-- HEADER -->
    <tr style="background:linear-gradient(90deg,#2563eb,#1e40af);color:#ffffff;">
      <td style="padding:18px 20px;font-size:18px;font-weight:600;">
        Quotation
      </td>
    </tr>

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

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

        <p style="margin:0 0 15px;">
          We are pleased to inform you that your quotation 
          <strong>${quotation.quotation_number}</strong> has been sent successfully .
        </p>

        <!-- HIGHLIGHT BOX -->
        <div style="background:#f1f5f9;border:1px solid #e2e8f0;border-radius:8px;padding:12px 15px;margin:15px 0;">
          <p style="margin:0;font-size:13px;">
            📎 The quotation is attached to this email as a PDF for your reference.
          </p>
        </div>

        <!-- INFO -->
        <p style="margin:15px 0;">
          Please review the attached document for complete details including pricing, taxes, and terms.
        </p>

        <p style="margin:15px 0;">
          If you have any questions or need further clarification, feel free to reach out to us.
        </p>

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

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

      </td>
    </tr>

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

  </table>
</div>
`,
        attachments: pdfBuffer
          ? [
              {
                filename: `Quotation-${quotation.quotation_number}.pdf`,
                content: pdfBuffer,
              },
            ]
          : [],
      });
    }

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Quotation approved & PDF sent successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("APPROVE ERROR:", err);

    return res.status(500).json({
      success: false,
      message: err.message || "Something went wrong",
    });
  } finally {
    client.release();
  }
};

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

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

    const result = await pool.query(
      `SELECT 
        org_name,
        address,
        gstin,
        cin
      FROM organizations
      WHERE id = $1 AND is_deleted = false`,
      [orgId],
    );

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

    const org = result.rows[0];

    res.json({
      success: true,
      data: {
        name: org.org_name,
        address: org.address,
        gst: org.gstin,
        cin: org.cin ? `CIN: ${org.cin}` : "",
      },
    });
  } catch (err) {
    console.error("GET ORG ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Failed to fetch organization",
    });
  }
};

module.exports = {
  createQuotation,
  getQuotations,
  getQuotationById,
  uploadQuotation,
  approveQuotation,
  getOrganization,
};
