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

// const transporter = nodemailer.createTransport({
//   host: "smtp.gmail.com",
//   port: 587,
//   secure: false,
//   ignoreTLS: true,
//   auth: {
//     user: process.env.MAIL_USER,
//     pass: process.env.MAIL_PASS,
//   },
//   tls: {
//     rejectUnauthorized: false,
//   },
// });

// transporter.verify((error, success) => {
//   if (error) {
//     console.error("❌ SMTP Error:", error);
//   } else {
//     console.log("✅ SMTP Server is ready");
//   }
// });

// // ========================
// // FILE UPLOAD CONFIGURATION
// // ========================
// const uploadDir = "uploads/quotations";
// if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });

// const storage = multer.diskStorage({
//   destination: (req, file, cb) => cb(null, uploadDir),
//   filename: (req, file, cb) => {
//     const unique = Date.now() + "-" + Math.round(Math.random() * 1e9);
//     const ext = path.extname(file.originalname);
//     cb(null, `quotation-${unique}${ext}`);
//   },
// });

// const fileFilter = (req, file, cb) => {
//   const allowed = ["application/pdf", "image/jpeg", "image/png"];
//   if (allowed.includes(file.mimetype)) cb(null, true);
//   else cb(new Error("Only PDF, JPEG, PNG allowed"));
// };

// const upload = multer({
//   storage,
//   fileFilter,
//   limits: { fileSize: 5 * 1024 * 1024 },
// });
// const uploadQuotationFileMiddleware = upload.single("quotationFile");

// // ========================
// // HELPER: Generate RFQ Number
// // ========================
// const generateRFQNumber = async () => {
//   try {
//     const result = await pool.query(
//       `UPDATE public.prefixes 
//        SET last_number = last_number + 1 
//        WHERE module_name = 'rfq' 
//          AND status = 'ACTIVE' 
//          AND is_deleted = false
//        RETURNING prefix_code, last_number`,
//     );

//     if (result.rows.length === 0) {
//       await pool.query(
//         `INSERT INTO public.prefixes (module_name, prefix_code, last_number, status) 
//          VALUES ('rfq', 'RFQ', 1, 'ACTIVE')
//          ON CONFLICT (prefix_code) DO NOTHING`,
//       );

//       const retryResult = await pool.query(
//         `UPDATE public.prefixes 
//          SET last_number = last_number + 1 
//          WHERE module_name = 'rfq' 
//            AND status = 'ACTIVE' 
//            AND is_deleted = false
//          RETURNING prefix_code, last_number`,
//       );

//       if (retryResult.rows.length > 0) {
//         const prefix = retryResult.rows[0].prefix_code;
//         const nextNumber = retryResult.rows[0].last_number;
//         const formattedNumber = nextNumber.toString().padStart(4, "0");
//         return `${prefix}-${formattedNumber}`;
//       }

//       throw new Error("No prefix configuration found for RFQ");
//     }

//     const prefix = result.rows[0].prefix_code;
//     const nextNumber = result.rows[0].last_number;
//     const formattedNumber = nextNumber.toString().padStart(4, "0");
//     return `${prefix}-${formattedNumber}`;
//   } catch (error) {
//     console.error("Error generating RFQ number:", error);
//     const timestamp = Date.now().toString().slice(-6);
//     return `RFQ-${timestamp}`;
//   }
// };

// // ========================
// // HELPER: Get RFQ by ID with items
// // ========================
// const getRFQByIdInternal = async (id, orgId) => {
//   const result = await pool.query(
//     `SELECT 
//        r.id,
//        r.rfq_number,
//        r.rfq_date,
//        r.status,
//        r.sent_at,
//        r.created_at,
//        r.updated_at,
//        r.org_id,
//        COALESCE(r.pr_number, 'Manual RFQ') as pr_number,
//        r.pr_id,
//        COALESCE(
//          (SELECT json_agg(json_build_object(
//            'id', ri.id, 
//            'item_code', ri.item_code, 
//            'item_name', ri.item_name,
//            'description', ri.description, 
//            'quantity', ri.quantity, 
//            'uom', ri.uom
//          ) ORDER BY ri.id)
//          FROM rfq_items ri WHERE ri.rfq_id = r.id
//        ), '[]'::json) as items,
//        COALESCE(
//          (SELECT json_agg(DISTINCT jsonb_build_object(
//            'id', v.id,
//            'vendor_name', v.vendor_name,
//            'email', v.email,
//            'gstin', v.gstin,
//            'vendor_code', v.vendor_code,
//            'selected_item_ids', qp.selected_item_ids
//          ))
//          FROM rfq_quotations qp
//          JOIN procurement_vendors v ON v.id = qp.vendor_id
//          WHERE qp.rfq_id = r.id AND v.org_id = r.org_id AND v.is_deleted = false
//        ), '[]'::json) as assigned_vendors
//      FROM rfqs r
//      WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
//     [parseInt(id), orgId],
//   );

//   return result.rows[0] || null;
// };

// // ========================
// // HELPER: Generate RFQ PDF
// // ========================
// function fileToBase64DataUri(filePath) {
//   try {
//     if (!filePath || !fs.existsSync(filePath)) return null;
//     const ext = path.extname(filePath).toLowerCase().slice(1);
//     const mime =
//       {
//         png: "image/png",
//         jpg: "image/jpeg",
//         jpeg: "image/jpeg",
//         gif: "image/gif",
//         svg: "image/svg+xml",
//         webp: "image/webp",
//       }[ext] || "image/png";
//     const data = fs.readFileSync(filePath);
//     return `data:${mime};base64,${data.toString("base64")}`;
//   } catch (err) {
//     return null;
//   }
// }

// function resolveLogoPath() {
//   const raw = (process.env.COMPANY_LOGO || "").trim();
//   const candidates = [
//     path.isAbsolute(raw) ? raw : null,
//     raw ? path.join(process.cwd(), raw) : null,
//     raw ? path.join(process.cwd(), "public", path.basename(raw)) : null,
//     path.join(process.cwd(), "public", "favicon.png"),
//     path.join(process.cwd(), "public", "logo.png"),
//     path.join(__dirname, "../../public", path.basename(raw || "favicon.png")),
//   ].filter(Boolean);
//   for (const p of candidates) {
//     if (fs.existsSync(p)) return p;
//   }
//   return null;
// }

// function escapeHtml(str) {
//   if (!str) return "";
//   return str
//     .replace(/&/g, "&amp;")
//     .replace(/</g, "&lt;")
//     .replace(/>/g, "&gt;")
//     .replace(/"/g, "&quot;")
//     .replace(/'/g, "&#39;");
// }

// const generateQuotationPDF = async (
//   rfq,
//   vendor,
//   items,
//   companyData,
//   terms = [],
// ) => {
//   try {
//     const templatePath = path.join(
//       __dirname,
//       "../../templates/rfq-pdf-template.html",
//     );
//     if (!fs.existsSync(templatePath)) {
//       throw new Error(`Template file not found`);
//     }
//     let html = fs.readFileSync(templatePath, "utf8");

//     const logoPath = resolveLogoPath();
//     const logoSrc = logoPath ? fileToBase64DataUri(logoPath) : "";
//     const companyShort = companyData.name
//       ? companyData.name.split(" ").slice(0, 2).join(" ")
//       : "Company";

//     const companyAddress = companyData.address || "";
//     const addressParts = companyAddress.split(",");
//     const addressLine1 = addressParts.slice(0, 2).join(",") || companyAddress;
//     const addressLine2 = addressParts.slice(2).join(",").trim() || "";

//     const rfqDate = rfq.rfq_date
//       ? new Date(rfq.rfq_date).toLocaleDateString("en-GB")
//       : "-";
//     const deadline = rfq.deadline
//       ? new Date(rfq.deadline).toLocaleDateString("en-GB")
//       : "Not specified";
//     const rfqNumber = rfq.rfq_number || "-";

//     let itemsRows = "";
//     for (let i = 0; i < items.length; i++) {
//       const item = items[i];
//       const quantity = Number(item.quantity) || 0;
//       itemsRows += `
//         <tr>
//           <td class="text-center">${i + 1}</td>
//           <td><div class="item-code">${escapeHtml(item.item_code || "-")}</div><div class="item-name">${escapeHtml(item.item_name || "")}</div>${item.description ? `<div class="item-desc">${escapeHtml(item.description)}</div>` : ""}</td>
//           <td class="text-center">${escapeHtml(item.uom || "-")}</td>
//           <td class="text-center">${quantity.toFixed(2)}</td>
//           <td class="text-right">_________</td>
//           <td class="text-right">_________</td>
//           <td class="text-right">_________</td>
//           <td class="text-right">_________</td>
//         </table>
//       `;
//     }

//     let termsHtml = "";
//     if (terms && terms.length > 0) {
//       terms.forEach((term, index) => {
//         termsHtml += `${index + 1}. ${escapeHtml(term.term)} : ${escapeHtml(term.description || "")}<br />`;
//       });
//     } else {
//       termsHtml = `1. Prices should be quoted in INR and are valid for 30 days.<br />2. GST extra as applicable.<br />3. Delivery timeline to be mentioned.<br />4. Payment terms to be negotiated.<br />5. Subject to Hyderabad jurisdiction.`;
//     }

//     const formatNumber = (num) =>
//       num.toLocaleString("en-IN", {
//         minimumFractionDigits: 2,
//         maximumFractionDigits: 2,
//       });

//     html = html
//       .replace(/{{COMPANY_LOGO}}/g, logoSrc)
//       .replace(/{{COMPANY_SHORT}}/g, companyShort)
//       .replace(
//         /{{COMPANY_NAME}}/g,
//         escapeHtml(companyData.name || "VIVEK BIO-PRODUCTS PRIVATE LIMITED"),
//       )
//       .replace(/{{COMPANY_ADDRESS_LINE1}}/g, escapeHtml(addressLine1))
//       .replace(/{{COMPANY_ADDRESS_LINE2}}/g, escapeHtml(addressLine2))
//       .replace(
//         /{{COMPANY_PAN}}/g,
//         escapeHtml(companyData.pan || companyData.pan_number || "AAICV8996R"),
//       )
//       .replace(
//         /{{COMPANY_GST}}/g,
//         escapeHtml(companyData.gst || companyData.gstin || "36AAICV8996R1ZU"),
//       )
//       .replace(
//         /{{COMPANY_CIN}}/g,
//         escapeHtml(
//           companyData.cin || companyData.cin_number || "U24100TG2022PTC165236",
//         ),
//       )
//       .replace(
//         /{{COMPANY_EMAIL}}/g,
//         escapeHtml(
//           companyData.email ||
//             companyData.company_email ||
//             "sales@vivekbio.com",
//         ),
//       )
//       .replace(
//         /{{COMPANY_PHONE}}/g,
//         escapeHtml(
//           companyData.phone || companyData.phone_number || "8019637711",
//         ),
//       )
//       .replace(
//         /{{VENDOR_NAME}}/g,
//         escapeHtml(vendor.vendor_name || vendor.name || "N/A"),
//       )
//       .replace(
//         /{{VENDOR_GST}}/g,
//         escapeHtml(vendor.gstin || vendor.gst || "N/A"),
//       )
//       .replace(
//         /{{VENDOR_ADDRESS}}/g,
//         (vendor.address || "N/A").replace(/\n/g, "<br />"),
//       )
//       .replace(/{{VENDOR_EMAIL}}/g, escapeHtml(vendor.email || "N/A"))
//       .replace(
//         /{{SUPPLY_ADDRESS}}/g,
//         companyAddress.replace(/\n/g, "<br />") || "N/A",
//       )
//       .replace(/{{RFQ_NUMBER}}/g, escapeHtml(rfqNumber))
//       .replace(/{{RFQ_DATE}}/g, rfqDate)
//       .replace(/{{DEADLINE}}/g, deadline)
//       .replace(/{{PR_NUMBER}}/g, escapeHtml(rfq.pr_number || "-"))
//       .replace(/{{DEPARTMENT}}/g, escapeHtml(rfq.department || "Production"))
//       .replace(/{{REQUESTED_BY}}/g, escapeHtml(rfq.requested_by || "System"))
//       .replace(/{{STATUS}}/g, escapeHtml(rfq.status || "Draft"))
//       .replace(/{{ITEMS_ROWS}}/g, itemsRows)
//       .replace(/{{TERMS_CONTENT}}/g, termsHtml);

//     const browser = await puppeteer.launch({
//       headless: "new",
//       args: ["--no-sandbox", "--disable-setuid-sandbox"],
//     });
//     const page = await browser.newPage();
//     await page.setContent(html, { waitUntil: "networkidle0", timeout: 30000 });
//     const pdf = await page.pdf({
//       format: "A4",
//       printBackground: true,
//       margin: { top: "20px", bottom: "20px", left: "20px", right: "20px" },
//     });
//     await browser.close();
//     return pdf;
//   } catch (error) {
//     console.error("Error generating RFQ PDF:", error);
//     throw error;
//   }
// };

// const saveQuotationPrices = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { id: rfq_id } = req.params;
//     const orgId = req.user?.orgId;
//     const userId = req.user?.userId || req.user?.email;

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

//     const { prices, vendorItemSelections, vendorItemQuantities } = req.body;

//     console.log("📋 saveQuotationPrices called with rfq_id:", rfq_id);
//     console.log("📦 prices:", prices);
//     console.log("📦 vendorItemSelections:", vendorItemSelections);
//     console.log("📦 vendorItemQuantities:", vendorItemQuantities);

//     // Fetch RFQ details
//     const rfqCheck = await client.query(
//       `SELECT id, rfq_number, status, rfq_date, deadline, pr_id FROM rfqs 
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [rfq_id, orgId],
//     );

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

//     const rfq = rfqCheck.rows[0];

//     // Get RFQ items
//     const itemsRes = await client.query(
//       `SELECT id, item_code, item_name, description, quantity, uom
//        FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
//       [rfq_id],
//     );
//     const items = itemsRes.rows;

//     if (items.length === 0) {
//       return res
//         .status(400)
//         .json({ success: false, message: "No items found for this RFQ" });
//     }

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

//     // Get all vendor IDs from vendorItemSelections
//     const vendorIdsSet = new Set();
//     if (vendorItemSelections) {
//       for (const vendorId of Object.keys(vendorItemSelections)) {
//         vendorIdsSet.add(parseInt(vendorId));
//       }
//     } else if (prices && prices.length > 0) {
//       for (const item of prices) {
//         if (item.vendorId) vendorIdsSet.add(item.vendorId);
//       }
//     }

//     const vendorIds = Array.from(vendorIdsSet);
//     console.log("🏢 Vendor IDs to process:", vendorIds);

//     if (vendorIds.length === 0) {
//       await client.query("ROLLBACK");
//       return res
//         .status(400)
//         .json({ success: false, message: "No vendors selected" });
//     }

//     // Get vendor details
//     const vendorRes = await client.query(
//       `SELECT id, vendor_name, email, gstin, address, phone 
//        FROM procurement_vendors 
//        WHERE id = ANY($1::int[]) AND org_id = $2 AND is_deleted = false`,
//       [vendorIds, orgId],
//     );
//     const vendors = vendorRes.rows;

//     // For each vendor, insert/update rfq_quotations with selected_item_ids and quantities
//     for (const vendor of vendors) {
//       // Get selected item IDs for this vendor
//       let selectedItemIds = vendorItemSelections
//         ? vendorItemSelections[vendor.id]
//         : null;

//       // If no specific selections, assign all items
//       if (!selectedItemIds || selectedItemIds.length === 0) {
//         selectedItemIds = items.map((i) => i.id);
//       }

//       // Ensure it's an array of numbers
//       let selectedItemIdsArray = Array.isArray(selectedItemIds)
//         ? selectedItemIds
//         : [Number(selectedItemIds)];
//       selectedItemIdsArray = selectedItemIdsArray
//         .map((id) => Number(id))
//         .filter((id) => !isNaN(id));

//       // Get quantities for this vendor's items
//       const itemQuantities = vendorItemQuantities?.[vendor.id] || {};
      
//       // Build quantities object for selected items
//       const quantitiesObj = {};
//       for (const itemId of selectedItemIdsArray) {
//         // If quantity specified in vendorItemQuantities, use that, otherwise use original RFQ quantity
//         const originalItem = items.find(i => i.id === itemId);
//         quantitiesObj[itemId] = itemQuantities[itemId] || (originalItem ? parseFloat(originalItem.quantity) : 0);
//       }

//       console.log(`📝 Vendor ${vendor.id} selected items:`, selectedItemIdsArray);
//       console.log(`📝 Vendor ${vendor.id} quantities:`, quantitiesObj);

//       // Delete existing
//       await client.query(
//         `DELETE FROM rfq_quotations WHERE rfq_id = $1 AND vendor_id = $2`,
//         [rfq_id, vendor.id],
//       );

//       // Insert new record with quantities
//       await client.query(
//         `INSERT INTO rfq_quotations(rfq_id, vendor_id, selected_item_ids, selected_item_quantities, created_by, updated_by)
//          VALUES ($1, $2, $3, $4, $5, $6)`,
//         [rfq_id, vendor.id, selectedItemIdsArray, quantitiesObj, userId, userId],
//       );

//       // Update pr_inventory to mark quantities sent for RFQ
//       if (rfq.pr_id) {
//         for (const item of items) {
//           const sentQuantity = quantitiesObj[item.id] || 0;
//           if (sentQuantity > 0) {
//             // Find the pr_inventory record for this requisition item
//             const inventoryRecord = await client.query(
//               `SELECT id, remaining_quantity, rfq_sent_quantity, status
//                FROM pr_inventory 
//                WHERE requisition_id = $1 
//                  AND item_code = $2
//                ORDER BY id ASC
//                LIMIT 1`,
//               [rfq.pr_id, item.item_code],
//             );

//             if (inventoryRecord.rows.length > 0) {
//               const current = inventoryRecord.rows[0];
//               const currentRfqSent = parseFloat(current.rfq_sent_quantity) || 0;
              
//               await client.query(
//                 `UPDATE pr_inventory 
//                  SET rfq_sent_quantity = $1,
//                      status = CASE 
//                        WHEN $1 >= remaining_quantity THEN 'Fully Quoted'
//                        ELSE 'Partially Quoted'
//                      END,
//                      updated_at = NOW(),
//                      updated_by = $2
//                  WHERE id = $3`,
//                 [currentRfqSent + sentQuantity, userId, current.id],
//               );
//             }
//           }
//         }
//       }

//       console.log(`✅ Vendor ${vendor.vendor_name} (${vendor.id}) assigned with ${selectedItemIdsArray.length} items`);
//     }

//     // Update RFQ status
//     if (rfq.status === "Draft") {
//       await client.query(
//         `UPDATE rfqs SET status = 'Sent', sent_at = NOW(), updated_at = NOW() WHERE id = $1`,
//         [rfq_id],
//       );
//       console.log("📝 RFQ status updated from Draft to Sent");
//     }

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

//     res.json({
//       success: true,
//       message: `RFQ sent to ${vendors.length} vendor(s)`,
//       data: { vendorsCount: vendors.length, rfqNumber: rfq.rfq_number },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in saveQuotationPrices:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // GET VENDOR-SPECIFIC RFQ ITEMS
// // ========================
// const getVendorRFQItems = async (req, res) => {
//   try {
//     const { rfqId, vendorId } = req.params;
//     const orgId = req.user?.orgId;

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

//     // Get selected_item_ids for this vendor
//     const vendorItemsResult = await pool.query(
//       `SELECT selected_item_ids FROM rfq_quotations 
//        WHERE rfq_id = $1 AND vendor_id = $2`,
//       [rfqId, vendorId],
//     );

//     const selectedItemIds = vendorItemsResult.rows[0]?.selected_item_ids || [];

//     // Get all RFQ items
//     const allItems = await pool.query(
//       `SELECT id, item_code, item_name, description, quantity, uom
//        FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
//       [rfqId],
//     );

//     // Filter items based on selection
//     let items = allItems.rows;
//     if (selectedItemIds.length > 0) {
//       items = items.filter((item) => selectedItemIds.includes(item.id));
//     }

//     res.json({
//       success: true,
//       data: {
//         items,
//         total_items: allItems.rows.length,
//         selected_items_count: items.length,
//         is_partial:
//           selectedItemIds.length > 0 &&
//           selectedItemIds.length < allItems.rows.length,
//       },
//     });
//   } catch (err) {
//     console.error("Error in getVendorRFQItems:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

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

//   try {
//     const { pr_id, items, vendorIds = [] } = req.body;
//     const orgId = req.user?.orgId;
//     const userId = req.user?.userId || req.user?.email;

//     console.log("📋 createRFQFromPRInventory called with:", { pr_id, items, vendorIds });

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

//     if (!pr_id) {
//       return res.status(400).json({
//         success: false,
//         message: "PR ID is required",
//       });
//     }

//     if (!items || !Array.isArray(items) || items.length === 0) {
//       return res.status(400).json({
//         success: false,
//         message: "At least one item is required",
//       });
//     }

//     // Check if RFQ already exists for this PR
//     const existingRFQ = await client.query(
//       `SELECT id, rfq_number, status FROM rfqs 
//        WHERE pr_id = $1 AND org_id = $2 AND is_deleted = false`,
//       [pr_id, orgId],
//     );

//     if (existingRFQ.rows.length > 0) {
//       const existing = existingRFQ.rows[0];
//       return res.status(400).json({
//         success: false,
//         message: `RFQ already exists for this PR: ${existing.rfq_number}`,
//         data: {
//           id: existing.id,
//           rfq_number: existing.rfq_number,
//           status: existing.status,
//         },
//       });
//     }

//     // Get PR details
//     const prResult = await client.query(
//       `SELECT pr_number, department, requested_by 
//        FROM requisitions 
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [pr_id, orgId],
//     );

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

//     const prData = prResult.rows[0];
//     const rfq_number = await generateRFQNumber();
//     const rfq_date = new Date().toISOString().split("T")[0];

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

//     // Create RFQ
//     const rfqResult = await client.query(
//       `INSERT INTO rfqs (
//         rfq_number, rfq_date, pr_number, pr_id, 
//         status, org_id, created_by, updated_by
//       ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
//       RETURNING id, rfq_number, rfq_date, status`,
//       [
//         rfq_number,
//         rfq_date,
//         prData.pr_number,
//         pr_id,
//         "Draft",
//         String(orgId),
//         userId,
//         userId,
//       ],
//     );

//     const rfq = rfqResult.rows[0];

//     // Insert RFQ items and UPDATE pr_inventory
//     for (const item of items) {
//       // Calculate quantity - ensure it's not zero
//       let quantity = Number(item.quantity) || 0;
      
//       // If quantity is zero, try to get from remaining_quantity
//       if (quantity === 0 && item.remaining_quantity) {
//         quantity = Number(item.remaining_quantity) || 0;
//       }
      
//       // If still zero, log warning and continue with a default of 1
//       if (quantity === 0) {
//         console.warn(`⚠️ Quantity is 0 for item: ${item.item_name || item.item_code}, using default 1`);
//         quantity = 1;
//       }
      
//       console.log(`📝 Inserting RFQ item: ${item.item_code} - ${item.item_name}, Quantity: ${quantity} ${item.uom}`);
      
//       // Insert into rfq_items
//       await client.query(
//         `INSERT INTO rfq_items (
//           rfq_id, item_code, item_name, description, quantity, uom
//         ) VALUES ($1, $2, $3, $4, $5, $6)`,
//         [
//           rfq.id,
//           item.item_code,
//           item.item_name,
//           item.description || null,
//           quantity,
//           item.uom || null,
//         ],
//       );

//       // Update or create pr_inventory record
//       const requisitionItemId = item.requisition_item_id || item.id;
      
//       const inventoryRecord = await client.query(
//         `SELECT id, remaining_quantity, po_requested_quantity, rfq_requested_quantity, status
//          FROM pr_inventory 
//          WHERE requisition_item_id = $1 
//            AND requisition_id = $2
//          ORDER BY id ASC
//          LIMIT 1`,
//         [requisitionItemId, pr_id],
//       );

//       if (inventoryRecord.rows.length > 0) {
//         const current = inventoryRecord.rows[0];
//         const currentRfqRequested = parseFloat(current.rfq_requested_quantity) || 0;
//         const currentPoRequested = parseFloat(current.po_requested_quantity) || 0;
//         const currentRemaining = parseFloat(current.remaining_quantity) || 0;
        
//         // Calculate new remaining quantity after RFQ request
//         const newRemaining = currentRemaining - quantity;
        
//         await client.query(
//           `UPDATE pr_inventory 
//            SET rfq_requested_quantity = $1,
//                po_requested_quantity = $2,
//                remaining_quantity = $3,
//                status = $4,
//                updated_at = NOW(),
//                updated_by = $5
//            WHERE id = $6`,
//           [
//             currentRfqRequested + quantity, 
//             currentPoRequested + quantity,
//             newRemaining,
//             newRemaining <= 0 ? 'Fully Quoted' : 'Partially Quoted',
//             userId, 
//             current.id
//           ],
//         );
        
//         console.log(`✅ Updated pr_inventory: item=${item.item_name}, RFQ Qty=${quantity}, New Remaining=${newRemaining}`);
//       } else {
//         // If no pr_inventory record exists, create one
//         await client.query(
//           `INSERT INTO pr_inventory (
//             requisition_id, requisition_item_id, item_code, item_name,
//             requested_quantity, remaining_quantity, 
//             issued_quantity, po_requested_quantity, rfq_requested_quantity, uom, org_id, status
//           ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`,
//           [
//             pr_id,
//             requisitionItemId,
//             item.item_code,
//             item.item_name,
//             quantity,
//             quantity,  // remaining_quantity
//             0,  // issued_quantity
//             quantity,  // po_requested_quantity
//             quantity,  // rfq_requested_quantity
//             item.uom || null,
//             orgId,
//             'Partially Quoted'
//           ],
//         );
//         console.log(`✅ Created new pr_inventory record: item=${item.item_name}, RFQ Qty=${quantity}`);
//       }
//     }

//     // If vendors are provided, assign them to the RFQ
//     if (vendorIds && vendorIds.length > 0) {
//       for (const vendorId of vendorIds) {
//         const rfqItems = await client.query(
//           `SELECT id FROM rfq_items WHERE rfq_id = $1`,
//           [rfq.id],
//         );

//         const selectedItemIds = rfqItems.rows.map((row) => row.id);

//         await client.query(
//           `INSERT INTO rfq_quotations(rfq_id, vendor_id, selected_item_ids, selected_item_quantities, created_by, updated_by)
//            VALUES ($1, $2, $3, $4, $5, $6)
//            ON CONFLICT (rfq_id, vendor_id) DO NOTHING`,
//           [rfq.id, vendorId, selectedItemIds, '{}', userId, userId],
//         );
        
//         console.log(`✅ Vendor ${vendorId} assigned to RFQ ${rfq.rfq_number}`);
//       }
//     }

//     // Update requisition po_status
//     await client.query(
//       `UPDATE requisitions 
//        SET po_status = 'RFQ Requested', updated_at = NOW()
//        WHERE id = $1`,
//       [pr_id],
//     );

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

//     const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);
    
//     console.log("✅ RFQ created successfully:", completeRFQ);

//     res.status(201).json({
//       success: true,
//       message: `RFQ ${rfq.rfq_number} created successfully from PR`,
//       data: completeRFQ,
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in createRFQFromPRInventory:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//       stack: process.env.NODE_ENV === "development" ? err.stack : undefined,
//     });
//   } finally {
//     client.release();
//   }
// };


// const getQuotationPrices = async (req, res) => {
//   try {
//     const { id: rfq_id } = req.params;
//     const orgId = req.user?.orgId;

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

//     // Get vendors with their selected_item_ids
//     const result = await pool.query(
//       `SELECT
//          qp.id,
//          qp.rfq_id,
//          qp.vendor_id,
//          qp.selected_item_ids,
//          v.vendor_name,
//          v.vendor_code,
//          v.gstin,
//          v.email,
//          v.address,
//          v.phone,
//          'internal' as vendor_type
//        FROM rfq_quotations qp
//        JOIN procurement_vendors v ON v.id = qp.vendor_id
//        JOIN rfqs r ON r.id = qp.rfq_id
//        WHERE qp.rfq_id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
//       [rfq_id, orgId],
//     );

//     console.log(
//       `📋 getQuotationPrices returned ${result.rows.length} vendor assignments`,
//     );

//     res.json({
//       success: true,
//       data: result.rows,
//     });
//   } catch (err) {
//     console.error("Error in getQuotationPrices:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
// // ========================
// const getUploadedVendors = async (req, res) => {
//   try {
//     const { id: rfqId } = req.params;
//     const orgId = req.user?.orgId;

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

//     const internalResult = await pool.query(
//       `SELECT DISTINCT vendor_id 
//        FROM rfq_uploaded_quotations 
//        WHERE rfq_id = $1 AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $2)`,
//       [rfqId, orgId],
//     );

//     const externalResult = await pool.query(
//       `SELECT id as vendor_id 
//        FROM rfq_external_vendors 
//        WHERE rfq_id = $1 AND quotation_uploaded = true`,
//       [rfqId],
//     );

//     const uploadedVendorIds = [
//       ...internalResult.rows.map((row) => row.vendor_id),
//       ...externalResult.rows.map((row) => row.vendor_id),
//     ];

//     res.json({ success: true, data: uploadedVendorIds });
//   } catch (err) {
//     console.error("Error in getUploadedVendors:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // GET QUOTATION COMPARISON (Includes vendor_terms) - MODIFIED to only show quoted items
// // ========================
// const getQuotationComparison = async (req, res) => {
//   try {
//     const { id: rfqId } = req.params;
//     const { vendorId } = req.params; // Optional vendorId from URL params
//     const orgId = req.user?.orgId;

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

//     const parsedId = parseInt(rfqId, 10);
//     if (isNaN(parsedId)) {
//       return res
//         .status(400)
//         .json({ success: false, message: "Invalid RFQ ID" });
//     }

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

//     // Get ALL RFQ items (for reference)
//     const allItemsResult = await pool.query(
//       `SELECT id, item_code, item_name, description, quantity, uom
//        FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
//       [parsedId],
//     );
//     const allItems = allItemsResult.rows;

//     if (allItems.length === 0) {
//       return res.json({
//         success: true,
//         data: { items: [], vendors: [], vendorPrices: {} },
//       });
//     }

//     // If vendorId is provided, get only that vendor's data
//     let vendors = [];
//     let priceMatrix = {};
//     // Track which items have been quoted for each vendor
//     let quotedItemIdsPerVendor = {};

//     if (vendorId) {
//       // Get specific internal vendor
//       const internalVendorResult = await pool.query(
//         `SELECT DISTINCT 
//            v.id, v.vendor_name, 'internal' as vendor_type, v.email, v.gstin,
//            uq.total_amount, uq.vendor_terms, uq.uploaded_at
//          FROM rfq_uploaded_quotations uq
//          JOIN procurement_vendors v ON v.id = uq.vendor_id
//          WHERE uq.rfq_id = $1 AND uq.vendor_id = $2`,
//         [parsedId, vendorId],
//       );

//       // Check external vendor if not found internally
//       let externalVendorResult = { rows: [] };
//       if (internalVendorResult.rows.length === 0) {
//         externalVendorResult = await pool.query(
//           `SELECT ev.id, ev.vendor_name, 'external' as vendor_type, ev.email,
//             NULL as gstin, ev.total_amount, ev.vendor_terms, ev.uploaded_at
//            FROM rfq_external_vendors ev
//            WHERE ev.rfq_id = $1 AND ev.id = $2 AND ev.quotation_uploaded = true`,
//           [parsedId, vendorId],
//         );
//       }

//       vendors = [...internalVendorResult.rows, ...externalVendorResult.rows];

//       if (vendors.length > 0) {
//         // Get prices for this vendor - ONLY items that have been quoted
//         const internalPrices = await pool.query(
//           `SELECT uq.vendor_id, uqi.rfq_item_id, uqi.unit_price
//            FROM rfq_uploaded_quotations uq
//            JOIN rfq_uploaded_quotation_items uqi ON uqi.uploaded_quotation_id = uq.id
//            WHERE uq.rfq_id = $1 AND uq.vendor_id = $2`,
//           [parsedId, vendorId],
//         );

//         const externalPrices = await pool.query(
//           `SELECT eqi.external_vendor_id as vendor_id, eqi.rfq_item_id, eqi.unit_price
//            FROM rfq_external_quotation_items eqi
//            JOIN rfq_external_vendors ev ON ev.id = eqi.external_vendor_id
//            WHERE ev.rfq_id = $1 AND ev.id = $2 AND ev.quotation_uploaded = true`,
//           [parsedId, vendorId],
//         );

//         // Store quoted item IDs for this vendor
//         const quotedIds = new Set();

//         for (const row of internalPrices.rows) {
//           if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
//           priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
//             row.unit_price,
//           );
//           quotedIds.add(row.rfq_item_id);
//         }
//         for (const row of externalPrices.rows) {
//           if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
//           priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
//             row.unit_price,
//           );
//           quotedIds.add(row.rfq_item_id);
//         }

//         quotedItemIdsPerVendor[vendorId] = quotedIds;
//       }
//     } else {
//       // Get all internal vendors who uploaded quotations
//       const internalVendorsResult = await pool.query(
//         `SELECT DISTINCT 
//            v.id, v.vendor_name, 'internal' as vendor_type, v.email, v.gstin,
//            uq.total_amount, uq.vendor_terms, uq.uploaded_at
//          FROM rfq_uploaded_quotations uq
//          JOIN procurement_vendors v ON v.id = uq.vendor_id
//          WHERE uq.rfq_id = $1 
//          ORDER BY v.vendor_name`,
//         [parsedId],
//       );

//       // Get external vendors who uploaded quotations
//       const externalVendorsResult = await pool.query(
//         `SELECT 
//            ev.id, ev.vendor_name, 'external' as vendor_type, ev.email,
//            NULL as gstin, ev.total_amount, ev.vendor_terms, ev.uploaded_at
//          FROM rfq_external_vendors ev
//          WHERE ev.rfq_id = $1 AND ev.quotation_uploaded = true
//          ORDER BY ev.vendor_name`,
//         [parsedId],
//       );

//       vendors = [...internalVendorsResult.rows, ...externalVendorsResult.rows];

//       // Get prices from all internal vendors - ONLY items that have been quoted
//       const internalPricesResult = await pool.query(
//         `SELECT uq.vendor_id, uqi.rfq_item_id, uqi.unit_price
//          FROM rfq_uploaded_quotations uq
//          JOIN rfq_uploaded_quotation_items uqi ON uqi.uploaded_quotation_id = uq.id
//          WHERE uq.rfq_id = $1`,
//         [parsedId],
//       );

//       // Get prices from all external vendors
//       const externalPricesResult = await pool.query(
//         `SELECT eqi.external_vendor_id as vendor_id, eqi.rfq_item_id, eqi.unit_price
//          FROM rfq_external_quotation_items eqi
//          JOIN rfq_external_vendors ev ON ev.id = eqi.external_vendor_id
//          WHERE ev.rfq_id = $1 AND ev.quotation_uploaded = true`,
//         [parsedId],
//       );

//       for (const row of internalPricesResult.rows) {
//         if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
//         priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
//           row.unit_price,
//         );

//         if (!quotedItemIdsPerVendor[row.vendor_id]) {
//           quotedItemIdsPerVendor[row.vendor_id] = new Set();
//         }
//         quotedItemIdsPerVendor[row.vendor_id].add(row.rfq_item_id);
//       }
//       for (const row of externalPricesResult.rows) {
//         if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
//         priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
//           row.unit_price,
//         );

//         if (!quotedItemIdsPerVendor[row.vendor_id]) {
//           quotedItemIdsPerVendor[row.vendor_id] = new Set();
//         }
//         quotedItemIdsPerVendor[row.vendor_id].add(row.rfq_item_id);
//       }
//     }

//     // IMPORTANT: Filter items to ONLY those that have been quoted by each vendor
//     // For each vendor, we only show the items they actually quoted (have prices)
//     const vendorItemsMap = {};
//     for (const vendor of vendors) {
//       const quotedIds = quotedItemIdsPerVendor[vendor.id] || new Set();
//       // Only include items that have a price in priceMatrix for this vendor
//       const quotedItems = allItems.filter((item) => quotedIds.has(item.id));
//       vendorItemsMap[vendor.id] = quotedItems;
//     }

//     // Build vendorPrices array - only for quoted items
//     const vendorPrices = {};
//     for (const vendor of vendors) {
//       vendorPrices[vendor.id] = allItems.map(
//         (item) => priceMatrix[vendor.id]?.[item.id] || null,
//       );
//     }

//     // For the response, also include the filtered items per vendor
//     res.json({
//       success: true,
//       data: {
//         items: allItems, // All items for reference
//         vendors,
//         vendorPrices,
//         vendorItemsMap, // NEW: Items actually quoted by each vendor
//         rfq_id: parsedId,
//         specific_vendor: vendorId || null,
//       },
//     });
//   } catch (err) {
//     console.error("Error in getQuotationComparison:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // UPLOAD QUOTATION (Vendor submits)
// // ========================
// const uploadQuotation = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { id: rfqId } = req.params;
//     const { vendorId, notes, totalAmount, items, vendorTerms } = req.body;
//     const orgId = req.user?.orgId;
//     const userId = req.user?.userId || req.user?.email;

//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     if (!vendorId)
//       return res
//         .status(400)
//         .json({ success: false, message: "Vendor ID required" });
//     if (!req.file)
//       return res
//         .status(400)
//         .json({ success: false, message: "Quotation file required" });

//     // Verify RFQ
//     const rfqCheck = await client.query(
//       `SELECT id, status FROM rfqs WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [rfqId, orgId],
//     );
//     if (rfqCheck.rows.length === 0)
//       return res.status(404).json({ success: false, message: "RFQ not found" });

//     // Verify vendor and get allowed items
//     const vendorItems = await client.query(
//       `SELECT selected_item_ids FROM rfq_quotations 
//        WHERE rfq_id = $1 AND vendor_id = $2`,
//       [rfqId, vendorId],
//     );

//     const allowedItemIds = vendorItems.rows[0]?.selected_item_ids || [];

//     // Parse items JSON
//     let itemsArray = [];
//     try {
//       itemsArray = JSON.parse(items || "[]");
//     } catch (e) {
//       itemsArray = [];
//     }

//     // Validate that vendor is only quoting allowed items
//     for (const it of itemsArray) {
//       if (allowedItemIds.length > 0 && !allowedItemIds.includes(it.rfqItemId)) {
//         return res.status(400).json({
//           success: false,
//           message: `Item ${it.rfqItemId} is not assigned to this vendor`,
//         });
//       }
//     }

//     const filePath = `/uploads/quotations/${req.file.filename}`;
//     const fileName = req.file.originalname;
//     const fileSize = req.file.size;
//     const mimeType = req.file.mimetype;

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

//     const headerResult = await client.query(
//       `INSERT INTO rfq_uploaded_quotations 
//         (rfq_id, vendor_id, file_name, file_path, file_size, mime_type, notes, uploaded_by, status, total_amount, vendor_terms)
//        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'Uploaded', $9, $10)
//        ON CONFLICT (rfq_id, vendor_id) 
//        DO UPDATE SET 
//          file_name = EXCLUDED.file_name,
//          file_path = EXCLUDED.file_path,
//          file_size = EXCLUDED.file_size,
//          mime_type = EXCLUDED.mime_type,
//          notes = EXCLUDED.notes,
//          uploaded_by = EXCLUDED.uploaded_by,
//          status = 'Uploaded',
//          total_amount = EXCLUDED.total_amount,
//          vendor_terms = EXCLUDED.vendor_terms,
//          uploaded_at = NOW()
//        RETURNING id`,
//       [
//         rfqId,
//         vendorId,
//         fileName,
//         filePath,
//         fileSize,
//         mimeType,
//         notes,
//         userId,
//         totalAmount || 0,
//         vendorTerms || null,
//       ],
//     );
//     const uploadedQuotationId = headerResult.rows[0].id;

//     await client.query(
//       `DELETE FROM rfq_uploaded_quotation_items WHERE uploaded_quotation_id = $1`,
//       [uploadedQuotationId],
//     );

//     for (const it of itemsArray) {
//       await client.query(
//         `INSERT INTO rfq_uploaded_quotation_items 
//           (uploaded_quotation_id, rfq_item_id, quantity, unit_price)
//          VALUES ($1, $2, $3, $4)`,
//         [uploadedQuotationId, it.rfqItemId, it.quantity, it.unitPrice],
//       );
//     }

//     // Update RFQ status
//     const assignedCountResult = await client.query(
//       `SELECT COUNT(DISTINCT vendor_id) AS count FROM rfq_quotations WHERE rfq_id = $1`,
//       [rfqId],
//     );
//     const assignedCount = parseInt(assignedCountResult.rows[0].count, 10);

//     const uploadedCountResult = await client.query(
//       `SELECT COUNT(DISTINCT vendor_id) AS count FROM rfq_uploaded_quotations WHERE rfq_id = $1`,
//       [rfqId],
//     );
//     const uploadedCount = parseInt(uploadedCountResult.rows[0].count, 10);

//     if (assignedCount > 0 && uploadedCount === assignedCount) {
//       await client.query(
//         `UPDATE rfqs SET status = 'Quotations Received', updated_at = NOW() WHERE id = $1`,
//         [rfqId],
//       );
//     }

//     await client.query("COMMIT");
//     res
//       .status(201)
//       .json({ success: true, message: "Quotation uploaded successfully" });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in uploadQuotation:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // ADD EXTERNAL VENDORS TO RFQ
// // ========================
// const addExternalVendorsToRFQ = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { id: rfqId } = req.params;
//     const { externalEmails, vendorItemSelections } = req.body;
//     const orgId = req.user?.orgId;
//     const created_by = req.user?.userId || req.user?.email || null;

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

//     if (
//       !externalEmails ||
//       !Array.isArray(externalEmails) ||
//       externalEmails.length === 0
//     ) {
//       return res
//         .status(400)
//         .json({ success: false, message: "At least one email is required" });
//     }

//     // Verify RFQ exists
//     const rfqCheck = await client.query(
//       `SELECT id, rfq_number, rfq_date, status FROM rfqs WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [rfqId, orgId],
//     );
//     if (rfqCheck.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "RFQ not found" });
//     }
//     const rfq = rfqCheck.rows[0];

//     // Get RFQ items
//     const itemsResult = await client.query(
//       `SELECT id, item_code, item_name, description, quantity, uom
//        FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
//       [rfqId],
//     );
//     const items = itemsResult.rows;

//     // Company data
//     let companyData = {
//       name: "VIVEK BIO-PRODUCTS",
//       address:
//         "1-8-303/48/15, KRK House, P.G. Road, Begumpet, Hyderabad – 500003, Telangana, India.",
//       gst: "36AFNPV7082B2Z1",
//       cin: "U31909TG2016PTC112345",
//     };

//     try {
//       const companyResult = await client.query(
//         `SELECT name, address, gst_number, cin_number FROM organizations WHERE id = $1`,
//         [String(orgId)],
//       );
//       if (companyResult.rows[0]) {
//         const org = companyResult.rows[0];
//         companyData = {
//           name: org.name || companyData.name,
//           address: org.address || companyData.address,
//           gst: org.gst_number || companyData.gst,
//           cin: org.cin_number || companyData.cin,
//         };
//       }
//     } catch (err) {
//       console.log("Using default company details");
//     }

//     // Get terms
//     let terms = [];
//     try {
//       const termsResult = await client.query(
//         `SELECT term, description FROM purchase_terms
//          WHERE doc_type = 'quotation' AND is_active = true ORDER BY term_order ASC`,
//       );
//       terms = termsResult.rows;
//     } catch (err) {
//       console.log("No terms found");
//     }

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

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

//     const insertedEmails = [];
//     const sentEmails = [];
//     const failedEmails = [];

//     for (const email of externalEmails) {
//       const trimmedEmail = email.trim();

//       const existing = await client.query(
//         `SELECT id FROM rfq_external_vendors WHERE rfq_id = $1 AND email = $2`,
//         [rfqId, trimmedEmail],
//       );

//       if (existing.rows.length === 0) {
//         const selectedItems =
//           (vendorItemSelections && vendorItemSelections[trimmedEmail]) ||
//           items.map((i) => i.id);

//         const result = await client.query(
//           `INSERT INTO rfq_external_vendors (rfq_id, email, vendor_name, selected_item_ids)
//            VALUES ($1, $2, $3, $4)
//            RETURNING id, email, vendor_name`,
//           [rfqId, trimmedEmail, trimmedEmail.split("@")[0], selectedItems],
//         );
//         insertedEmails.push(result.rows[0]);

//         try {
//           const vendorItems = items.filter((item) =>
//             selectedItems.includes(item.id),
//           );

//           if (vendorItems.length === 0) {
//             console.log(
//               `No items selected for external vendor ${trimmedEmail}, skipping`,
//             );
//             continue;
//           }

//           const vendor = {
//             vendor_name: trimmedEmail.split("@")[0],
//             email: trimmedEmail,
//             address: "N/A",
//             gstin: "N/A",
//           };

//           const pdfBytes = await generateQuotationPDF(
//             rfq,
//             vendor,
//             vendorItems,
//             companyData,
//             terms,
//           );

//           const pdfFileName = `Quotation_${rfq.rfq_number}_${vendor.vendor_name}.pdf`;
//           const pdfFilePath = path.join(tempDir, pdfFileName);
//           fs.writeFileSync(pdfFilePath, pdfBytes);

//           const itemsTableHtml = vendorItems
//             .map((item, index) => {
//               return `
//               <tr>
//                 <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${index + 1}</td>
//                 <td style="padding: 8px; border: 1px solid #ddd;">${item.item_code || "-"}</td>
//                 <td style="padding: 8px; border: 1px solid #ddd;">${item.item_name || "-"}</td>
//                 <td style="padding: 8px; border: 1px solid #ddd;">${item.description || "-"}</td>
//                 <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${item.quantity || 0}</td>
//                 <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${item.uom || "-"}</td>
//                 <td style="padding: 8px; border: 1px solid #ddd; text-align: right;">__________</td>
//                 <td style="padding: 8px; border: 1px solid #ddd; text-align: right;">__________</td>
//               </tr>
//             `;
//             })
//             .join("");

//           const mailOptions = {
//             from: `"Procurement Team" <${process.env.MAIL_USER}>`,
//             to: trimmedEmail,
//             subject: `${rfq.rfq_number} - Request for Quotation - ${companyData.name}`,
//             html: `
//               <div style="font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto;">
//                 <h2 style="color: #1a56db; text-align: center;">REQUEST FOR QUOTATION</h2>
//                 <p style="text-align: center; font-size: 14px; color: #666;">${companyData.name}</p>
                
//                 <p>Dear <strong>${vendor.vendor_name}</strong>,</p>
                
//                 <p>You have been invited to submit a quotation for <strong>${rfq.rfq_number}</strong>.</p>
                
//                 <div style="background-color: #e8f0fe; padding: 10px; border-radius: 5px; margin: 15px 0;">
//                   <strong>📋 Items You Need to Quote:</strong> ${vendorItems.length} of ${items.length} items
//                 </div>
                
//                 <h3>Items You Need to Quote:</h3>
//                 <table style="width: 100%; border-collapse: collapse;">
//                   <thead><tr style="background-color: #f0f0f0;">
//                     <th style="padding: 8px; border: 1px solid #ddd;">#</th>
//                     <th style="padding: 8px; border: 1px solid #ddd;">Item Code</th>
//                     <th style="padding: 8px; border: 1px solid #ddd;">Item Name</th>
//                     <th style="padding: 8px; border: 1px solid #ddd;">QTY</th>
//                     <th style="padding: 8px; border: 1px solid #ddd;">UOM</th>
//                    </tr></thead>
//                   <tbody>${itemsTableHtml}</tbody>
//                 </table>
                
//                 <div style="background-color: #fff3cd; padding: 15px; border-radius: 5px; margin: 20px 0;">
//                   <strong>📌 Instructions:</strong>
//                   <ul><li>Download the attached PDF file</li><li>Fill in your Unit Prices</li><li>Reply with the filled PDF</li></ul>
//                 </div>
                
//                 <p>Regards,<br/><strong>Procurement Team</strong><br/>${companyData.name}</p>
//               </div>
//             `,
//             attachments: [
//               {
//                 filename: pdfFileName,
//                 path: pdfFilePath,
//                 contentType: "application/pdf",
//               },
//             ],
//           };

//           await transporter.sendMail(mailOptions);
//           sentEmails.push(trimmedEmail);

//           setTimeout(() => {
//             if (fs.existsSync(pdfFilePath)) fs.unlinkSync(pdfFilePath);
//           }, 10000);
//         } catch (emailError) {
//           console.error(
//             `Failed to send email to ${trimmedEmail}:`,
//             emailError.message,
//           );
//           failedEmails.push({ email: trimmedEmail, error: emailError.message });
//         }
//       }
//     }

//     if (rfq.status === "Draft") {
//       await client.query(
//         `UPDATE rfqs SET status = 'Sent', sent_at = NOW(), updated_at = NOW() WHERE id = $1`,
//         [rfqId],
//       );
//     }

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

//     let message = `${insertedEmails.length} external vendor(s) added. Emails sent to ${sentEmails.length}.`;
//     res.json({
//       success: true,
//       message,
//       data: { added: insertedEmails, sent: sentEmails, failed: failedEmails },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in addExternalVendorsToRFQ:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // GET ALL RFQS
// // ========================
// const getAllRFQs = async (req, res) => {
//   try {
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }

//     const {
//       search = "",
//       page = 1,
//       limit = 10,
//       sortBy = "created_at",
//       sortOrder = "DESC",
//     } = req.query;
//     const offset = (parseInt(page) - 1) * parseInt(limit);
//     const searchTerm = `%${search}%`;

//     const countResult = await pool.query(
//       `SELECT COUNT(*) AS total
//        FROM rfqs r
//        WHERE r.is_deleted = false AND r.org_id = $1
//          AND (r.rfq_number ILIKE $2 OR COALESCE(r.pr_number, 'Manual RFQ') ILIKE $2)`,
//       [orgId, searchTerm],
//     );

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

//     const result = await pool.query(
//       `SELECT 
//          r.id, r.rfq_number, r.rfq_date, COALESCE(r.pr_number, 'Manual RFQ') as pr_number,
//          r.pr_id, r.deadline, r.status, r.sent_at, r.created_at, r.updated_at,
//          COALESCE(
//            (SELECT json_agg(json_build_object(
//              'id', ri.id, 'item_code', ri.item_code, 'item_name', ri.item_name,
//              'description', ri.description, 'quantity', ri.quantity, 'uom', ri.uom
//            ) ORDER BY ri.id)
//            FROM rfq_items ri WHERE ri.rfq_id = r.id
//          ), '[]'::json) as items,
//          COALESCE((SELECT COUNT(*) FROM rfq_uploaded_quotations WHERE rfq_id = r.id), 0) as uploaded_count,
//          COALESCE((SELECT COUNT(*) FROM rfq_quotations WHERE rfq_id = r.id), 0) as assigned_count
//        FROM rfqs r
//        WHERE r.is_deleted = false AND r.org_id = $1
//          AND (r.rfq_number ILIKE $2 OR COALESCE(r.pr_number, 'Manual RFQ') ILIKE $2)
//        ORDER BY r.${sortBy} ${sortOrder}
//        LIMIT $3 OFFSET $4`,
//       [orgId, searchTerm, parseInt(limit), offset],
//     );

//     res.json({
//       success: true,
//       data: result.rows,
//       pagination: {
//         currentPage: parseInt(page),
//         totalPages: Math.ceil(total / parseInt(limit)),
//         totalEntries: total,
//         itemsPerPage: parseInt(limit),
//       },
//     });
//   } catch (err) {
//     console.error("Error in getAllRFQs:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // CREATE RFQ FROM PR
// // ========================
// const createRFQFromPR = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { pr_id } = req.params;
//     const orgId = req.user?.orgId;
//     const created_by = req.user?.userId || req.user?.email || null;

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

//     const requisitionResult = await client.query(
//       `SELECT r.id, r.pr_number, r.pr_date, r.department, r.requested_by, r.status,
//          COALESCE((SELECT json_agg(json_build_object(
//            'id', ri.id, 'item_code', ri.item_code, 'item_name', ri.item_name,
//            'description', ri.description, 'quantity', ri.quantity, 'uom', ri.uom
//          ) ORDER BY ri.id) FROM requisition_items ri WHERE ri.requisition_id = r.id), '[]'::json) as items
//        FROM requisitions r
//        WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false AND r.status = 'Approved'`,
//       [pr_id, orgId],
//     );

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

//     const requisition = requisitionResult.rows[0];

//     const rfq_number = await generateRFQNumber();
//     const rfq_date = new Date().toISOString().split("T")[0];

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

//     const rfqResult = await client.query(
//       `INSERT INTO rfqs (rfq_number, rfq_date, pr_number, pr_id, status, org_id, created_by, updated_by)
//        VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
//       [
//         rfq_number,
//         rfq_date,
//         requisition.pr_number,
//         requisition.id,
//         "Draft",
//         orgId,
//         created_by,
//         created_by,
//       ],
//     );

//     const rfq = rfqResult.rows[0];

//     for (const item of requisition.items) {
//       await client.query(
//         `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
//          VALUES ($1, $2, $3, $4, $5, $6)`,
//         [
//           rfq.id,
//           item.item_code,
//           item.item_name,
//           item.description || null,
//           item.quantity || 0,
//           item.uom || null,
//         ],
//       );
//     }

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

//     const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);

//     res.status(201).json({
//       success: true,
//       message: "RFQ created successfully",
//       data: completeRFQ,
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in createRFQFromPR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // GET RFQ BY ID
// // ========================
// const getRFQById = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }
//     const rfq = await getRFQByIdInternal(id, orgId);
//     if (!rfq) {
//       return res.status(404).json({ success: false, message: "RFQ not found" });
//     }
//     res.json({ success: true, data: rfq });
//   } catch (err) {
//     console.error("Error in getRFQById:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // GET RFQ ITEMS
// // ========================
// const getRFQItems = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }
//     const result = await pool.query(
//       `SELECT ri.* FROM rfq_items ri
//        JOIN rfqs r ON r.id = ri.rfq_id
//        WHERE ri.rfq_id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
//       [parseInt(id), orgId],
//     );
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("Error in getRFQItems:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // CREATE MANUAL RFQ
// // ========================
// const createManualRFQ = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const orgId = req.user?.orgId;
//     const created_by = req.user?.userId || req.user?.email || null;
//     const { items } = req.body;

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

//     if (!items || !Array.isArray(items) || items.length === 0) {
//       return res
//         .status(400)
//         .json({ success: false, message: "At least one item is required" });
//     }

//     const rfq_number = await generateRFQNumber();
//     const rfqDate = new Date().toLocaleDateString("en-CA");

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

//     const rfqResult = await client.query(
//       `INSERT INTO rfqs (rfq_number, rfq_date, status, org_id, created_by, updated_by)
//        VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, rfq_number, rfq_date, status`,
//       [rfq_number, rfqDate, "Draft", String(orgId), created_by, created_by],
//     );

//     const rfq = rfqResult.rows[0];

//     const insertedItems = [];
//     for (const item of items) {
//       const itemResult = await client.query(
//         `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
//          VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, item_code, item_name, description, quantity, uom`,
//         [
//           rfq.id,
//           item.item_code,
//           item.item_name,
//           item.description || null,
//           Number(item.quantity) || 0,
//           item.uom,
//         ],
//       );
//       insertedItems.push(itemResult.rows[0]);
//     }

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

//     res.status(201).json({
//       success: true,
//       message: `RFQ ${rfq.rfq_number} created successfully`,
//       data: {
//         id: rfq.id,
//         rfq_number: rfq.rfq_number,
//         rfq_date: rfq.rfq_date,
//         status: "Draft",
//         items: insertedItems,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in createManualRFQ:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // DELETE RFQ
// // ========================
// const deleteRFQ = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const orgId = req.user?.orgId;
//     const updated_by = req.user?.userId || req.user?.email;

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

//     const result = await pool.query(
//       `UPDATE rfqs SET is_deleted = true, deleted_at = NOW(), updated_by = $1
//        WHERE id = $2 AND org_id = $3 AND is_deleted = false
//        RETURNING id, rfq_number`,
//       [updated_by, parseInt(id), orgId],
//     );

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

//     res.json({
//       success: true,
//       message: "RFQ deleted successfully",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("Error in deleteRFQ:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // GET COMPARISON RFQ LIST
// // ========================
// const getComparisonRFQList = async (req, res) => {
//   try {
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }

//     const query = `
//       SELECT DISTINCT 
//         r.id, r.rfq_number, r.pr_number, r.rfq_date, r.created_at, r.status,
//         COALESCE((SELECT COUNT(DISTINCT vendor_id) FROM rfq_uploaded_quotations WHERE rfq_id = r.id), 0) as internal_quotations_count,
//         COALESCE((SELECT COUNT(*) FROM rfq_external_vendors WHERE rfq_id = r.id AND quotation_uploaded = true), 0) as external_quotations_count,
//         (COALESCE((SELECT COUNT(DISTINCT vendor_id) FROM rfq_uploaded_quotations WHERE rfq_id = r.id), 0) + 
//          COALESCE((SELECT COUNT(*) FROM rfq_external_vendors WHERE rfq_id = r.id AND quotation_uploaded = true), 0)) as total_quotations
//       FROM rfqs r
//       WHERE r.org_id = $1 AND r.is_deleted = false
//         AND (EXISTS (SELECT 1 FROM rfq_uploaded_quotations uq WHERE uq.rfq_id = r.id)
//              OR EXISTS (SELECT 1 FROM rfq_external_vendors ev WHERE ev.rfq_id = r.id AND ev.quotation_uploaded = true))
//       ORDER BY r.rfq_number DESC
//     `;
//     const result = await pool.query(query, [orgId]);
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("Error in getComparisonRFQList:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // GET EXTERNAL VENDORS FOR RFQ
// // ========================
// const getExternalVendorsForRFQ = async (req, res) => {
//   try {
//     const { id: rfqId } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }
//     const result = await pool.query(
//       `SELECT ev.id, ev.email, ev.vendor_name, ev.quotation_uploaded, ev.uploaded_file_path, ev.uploaded_at, ev.created_at, ev.selected_item_ids
//        FROM rfq_external_vendors ev
//        JOIN rfqs r ON r.id = ev.rfq_id
//        WHERE ev.rfq_id = $1 AND r.org_id = $2 ORDER BY ev.created_at DESC`,
//       [rfqId, orgId],
//     );
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("Error in getExternalVendorsForRFQ:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // UPLOAD EXTERNAL QUOTATION
// // ========================
// const uploadExternalQuotation = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { id: rfqId } = req.params;
//     const { externalVendorId, notes, totalAmount, items, vendorTerms } =
//       req.body;
//     const orgId = req.user?.orgId;
//     const userId = req.user?.userId || req.user?.email;

//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     if (!externalVendorId)
//       return res
//         .status(400)
//         .json({ success: false, message: "External vendor ID required" });
//     if (!req.file)
//       return res
//         .status(400)
//         .json({ success: false, message: "Quotation file required" });

//     const rfqCheck = await client.query(
//       `SELECT id, status FROM rfqs WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [rfqId, orgId],
//     );
//     if (rfqCheck.rows.length === 0)
//       return res.status(404).json({ success: false, message: "RFQ not found" });

//     const externalVendorCheck = await client.query(
//       `SELECT id, email, vendor_name, selected_item_ids FROM rfq_external_vendors 
//        WHERE id = $1 AND rfq_id = $2 AND quotation_uploaded = false`,
//       [externalVendorId, rfqId],
//     );
//     if (externalVendorCheck.rows.length === 0) {
//       return res.status(400).json({
//         success: false,
//         message: "External vendor not found or already uploaded",
//       });
//     }
//     const externalVendor = externalVendorCheck.rows[0];
//     const allowedItemIds = externalVendor.selected_item_ids || [];

//     let itemsArray = [];
//     try {
//       itemsArray = JSON.parse(items || "[]");
//     } catch (e) {
//       itemsArray = [];
//     }

//     // Validate items
//     for (const it of itemsArray) {
//       if (allowedItemIds.length > 0 && !allowedItemIds.includes(it.rfqItemId)) {
//         return res.status(400).json({
//           success: false,
//           message: `Item ${it.rfqItemId} is not assigned to this vendor`,
//         });
//       }
//     }

//     const filePath = `/uploads/quotations/${req.file.filename}`;
//     const fileName = req.file.originalname;
//     const fileSize = req.file.size;
//     const mimeType = req.file.mimetype;

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

//     await client.query(
//       `UPDATE rfq_external_vendors 
//        SET quotation_uploaded = true, uploaded_file_path = $1, uploaded_file_name = $2,
//            uploaded_file_size = $3, uploaded_mime_type = $4, notes = $5, total_amount = $6,
//            vendor_terms = $7, updated_at = NOW(), updated_by = $8
//        WHERE id = $9`,
//       [
//         filePath,
//         fileName,
//         fileSize,
//         mimeType,
//         notes,
//         totalAmount || 0,
//         vendorTerms || null,
//         userId,
//         externalVendorId,
//       ],
//     );

//     for (const it of itemsArray) {
//       await client.query(
//         `INSERT INTO rfq_external_quotation_items (external_vendor_id, rfq_item_id, quantity, unit_price)
//          VALUES ($1, $2, $3, $4)
//          ON CONFLICT (external_vendor_id, rfq_item_id) 
//          DO UPDATE SET quantity = EXCLUDED.quantity, unit_price = EXCLUDED.unit_price`,
//         [externalVendorId, it.rfqItemId, it.quantity, it.unitPrice],
//       );
//     }

//     await client.query("COMMIT");
//     res.status(201).json({
//       success: true,
//       message: "External quotation uploaded successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in uploadExternalQuotation:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // GET EXTERNAL UPLOADED QUOTATIONS
// // ========================
// const getExternalUploadedQuotations = async (req, res) => {
//   try {
//     const { id: rfqId } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }
//     const result = await pool.query(
//       `SELECT ev.id, ev.email, ev.vendor_name, ev.uploaded_file_path, ev.uploaded_file_name,
//               ev.uploaded_at, ev.total_amount, ev.notes, ev.selected_item_ids,
//          COALESCE((SELECT json_agg(json_build_object(
//            'rfq_item_id', eqi.rfq_item_id, 'quantity', eqi.quantity, 'unit_price', eqi.unit_price,
//            'item_code', ri.item_code, 'item_name', ri.item_name, 'quantity_requested', ri.quantity, 'uom', ri.uom
//          )) FROM rfq_external_quotation_items eqi
//            JOIN rfq_items ri ON ri.id = eqi.rfq_item_id
//            WHERE eqi.external_vendor_id = ev.id), '[]'::json) as items
//        FROM rfq_external_vendors ev
//        JOIN rfqs r ON r.id = ev.rfq_id
//        WHERE ev.rfq_id = $1 AND r.org_id = $2 AND ev.quotation_uploaded = true
//        ORDER BY ev.uploaded_at DESC`,
//       [rfqId, orgId],
//     );
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("Error in getExternalUploadedQuotations:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // GET QUOTATION UPLOAD
// // ========================
// const getQuotationUpload = async (req, res) => {
//   try {
//     const { id: rfqId } = req.params;
//     const { vendorId } = req.query;
//     const orgId = req.user?.orgId;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     if (!vendorId)
//       return res
//         .status(400)
//         .json({ success: false, message: "Vendor ID required" });
//     const result = await pool.query(
//       `SELECT * FROM rfq_uploaded_quotations 
//        WHERE rfq_id = $1 AND vendor_id = $2
//        AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $3)`,
//       [rfqId, vendorId, orgId],
//     );
//     res.json({ success: true, data: result.rows[0] || null });
//   } catch (err) {
//     console.error("Error in getQuotationUpload:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // DELETE QUOTATION UPLOAD
// // ========================
// const deleteQuotationUpload = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { id: rfqId } = req.params;
//     const { vendorId } = req.body;
//     const orgId = req.user?.orgId;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     if (!vendorId)
//       return res
//         .status(400)
//         .json({ success: false, message: "Vendor ID required" });
//     const fileRes = await client.query(
//       `SELECT file_path FROM rfq_uploaded_quotations 
//        WHERE rfq_id = $1 AND vendor_id = $2
//        AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $3)`,
//       [rfqId, vendorId, orgId],
//     );
//     if (fileRes.rows.length > 0) {
//       const filePath = path.join(
//         __dirname,
//         "../../",
//         fileRes.rows[0].file_path,
//       );
//       if (fs.existsSync(filePath)) fs.unlinkSync(filePath);
//     }
//     await client.query(
//       `DELETE FROM rfq_uploaded_quotations WHERE rfq_id = $1 AND vendor_id = $2`,
//       [rfqId, vendorId],
//     );
//     res.json({ success: true, message: "Quotation file deleted" });
//   } catch (err) {
//     console.error("Error in deleteQuotationUpload:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // DELETE QUOTATION PRICES
// // ========================
// const deleteQuotationPrices = async (req, res) => {
//   try {
//     const { id: rfq_id } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     const result = await pool.query(
//       `DELETE FROM rfq_quotations
//        WHERE rfq_id = $1 AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $2)`,
//       [rfq_id, orgId],
//     );
//     res.json({
//       success: true,
//       message: "Quotation prices deleted",
//       deletedCount: result.rowCount,
//     });
//   } catch (err) {
//     console.error("Error in deleteQuotationPrices:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // UPDATE RFQ STATUS
// // ========================
// const updateRFQStatus = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { status } = req.body;
//     const orgId = req.user?.orgId;
//     const updated_by = req.user?.userId || req.user?.email;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     const validStatuses = [
//       "Draft",
//       "Sent",
//       "Quotations Received",
//       "Approved",
//       "Rejected",
//     ];
//     if (!validStatuses.includes(status)) {
//       return res
//         .status(400)
//         .json({ success: false, message: "Invalid status" });
//     }
//     const result = await pool.query(
//       `UPDATE rfqs SET status = $1, updated_by = $2, updated_at = NOW()
//        WHERE id = $3 AND org_id = $4 AND is_deleted = false RETURNING *`,
//       [status, updated_by, parseInt(id), orgId],
//     );
//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "RFQ not found" });
//     }
//     res.json({
//       success: true,
//       message: "Status updated",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("Error in updateRFQStatus:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // BULK CREATE RFQS
// // ========================
// const bulkCreateRFQs = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { requisitions } = req.body;
//     const orgId = req.user?.orgId;
//     const created_by = req.user?.userId || req.user?.email;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     if (
//       !requisitions ||
//       !Array.isArray(requisitions) ||
//       requisitions.length === 0
//     ) {
//       return res
//         .status(400)
//         .json({ success: false, message: "Requisitions array required" });
//     }
//     await client.query("BEGIN");
//     const createdRFQs = [];
//     for (const reqData of requisitions) {
//       const { requisitionId, pr_number, items } = reqData;
//       const existing = await client.query(
//         `SELECT id FROM rfqs WHERE pr_id = $1 AND org_id = $2 AND is_deleted = false`,
//         [requisitionId, orgId],
//       );
//       if (existing.rows.length > 0) continue;
//       const rfq_number = await generateRFQNumber();
//       const rfq_date = new Date().toISOString().split("T")[0];
//       const rfqRes = await client.query(
//         `INSERT INTO rfqs (rfq_number, rfq_date, pr_number, pr_id, status, org_id, created_by, updated_by)
//          VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
//         [
//           rfq_number,
//           rfq_date,
//           pr_number,
//           requisitionId,
//           "Draft",
//           orgId,
//           created_by,
//           created_by,
//         ],
//       );
//       const rfq = rfqRes.rows[0];
//       for (const item of items) {
//         await client.query(
//           `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
//            VALUES ($1, $2, $3, $4, $5, $6)`,
//           [
//             rfq.id,
//             item.item_code,
//             item.item_name,
//             item.description,
//             item.quantity,
//             item.uom,
//           ],
//         );
//       }
//       createdRFQs.push(rfq);
//     }
//     await client.query("COMMIT");
//     res.status(201).json({
//       success: true,
//       message: `${createdRFQs.length} RFQs created`,
//       data: createdRFQs,
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in bulkCreateRFQs:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // GET RFQ STATISTICS
// // ========================
// const getRFQStatistics = async (req, res) => {
//   try {
//     const orgId = req.user?.orgId;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     const result = await pool.query(
//       `SELECT COUNT(*) AS total_rfqs,
//          COUNT(CASE WHEN status = 'Draft' THEN 1 END) AS draft_count,
//          COUNT(CASE WHEN status = 'Sent' THEN 1 END) AS sent_count,
//          COUNT(CASE WHEN status = 'Quotations Received' THEN 1 END) AS quotations_received_count,
//          COUNT(CASE WHEN status = 'Approved' THEN 1 END) AS approved_count,
//          COUNT(CASE WHEN status = 'Rejected' THEN 1 END) AS rejected_count
//        FROM rfqs WHERE org_id = $1 AND is_deleted = false`,
//       [orgId],
//     );
//     res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("Error in getRFQStatistics:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // SEND RFQ TO VENDORS (Legacy)
// // ========================
// const sendRFQToVendors = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     const result = await pool.query(
//       `UPDATE rfqs SET status = 'Sent', sent_at = NOW(), updated_at = NOW()
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false RETURNING *`,
//       [parseInt(id), orgId],
//     );
//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "RFQ not found" });
//     }
//     res.json({
//       success: true,
//       message: "RFQ sent to vendors successfully",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("Error in sendRFQToVendors:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // UPDATE RFQ DEADLINE
// // ========================
// const updateRFQDeadline = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { deadline } = req.body;
//     const orgId = req.user?.orgId;
//     const updated_by = req.user?.userId || req.user?.email;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     const result = await pool.query(
//       `UPDATE rfqs SET deadline = $1, updated_by = $2, updated_at = NOW()
//        WHERE id = $3 AND org_id = $4 AND is_deleted = false RETURNING *`,
//       [deadline, updated_by, parseInt(id), orgId],
//     );
//     if (result.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "RFQ not found" });
//     }
//     res.json({
//       success: true,
//       message: "Deadline updated",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("Error in updateRFQDeadline:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// // ========================
// // HARD DELETE RFQ
// // ========================
// const hardDeleteRFQ = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { id } = req.params;
//     const orgId = req.user?.orgId;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     await client.query("BEGIN");
//     await client.query("DELETE FROM rfq_items WHERE rfq_id = $1", [
//       parseInt(id),
//     ]);
//     const result = await client.query(
//       `DELETE FROM rfqs WHERE id = $1 AND org_id = $2 RETURNING id, rfq_number`,
//       [parseInt(id), orgId],
//     );
//     if (result.rowCount === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({ success: false, message: "RFQ not found" });
//     }
//     await client.query("COMMIT");
//     res.json({
//       success: true,
//       message: "RFQ permanently deleted",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in hardDeleteRFQ:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // CREATE RFQ FROM REQUISITION (BODY)
// // ========================
// const createRFQFromRequisition = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const orgId = req.user?.orgId;
//     const created_by = req.user?.userId || req.user?.email;
//     const { requisitionId, pr_number, items } = req.body;
//     if (!orgId)
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     if (!requisitionId || !pr_number) {
//       return res.status(400).json({
//         success: false,
//         message: "Requisition ID and PR number are required",
//       });
//     }
//     const existingRFQ = await pool.query(
//       `SELECT id, rfq_number FROM rfqs WHERE pr_id = $1 AND org_id = $2 AND is_deleted = false`,
//       [requisitionId, orgId],
//     );
//     if (existingRFQ.rows.length > 0) {
//       return res.status(400).json({
//         success: false,
//         message: `RFQ already exists: ${existingRFQ.rows[0].rfq_number}`,
//       });
//     }
//     const rfq_number = await generateRFQNumber();
//     const rfq_date = new Date().toISOString().split("T")[0];
//     await client.query("BEGIN");
//     const rfqResult = await client.query(
//       `INSERT INTO rfqs (rfq_number, rfq_date, pr_number, pr_id, status, org_id, created_by, updated_by)
//        VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
//       [
//         rfq_number,
//         rfq_date,
//         pr_number,
//         requisitionId,
//         "Draft",
//         orgId,
//         created_by,
//         created_by,
//       ],
//     );
//     const rfq = rfqResult.rows[0];
//     if (items && items.length > 0) {
//       for (const item of items) {
//         await client.query(
//           `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
//            VALUES ($1, $2, $3, $4, $5, $6)`,
//           [
//             rfq.id,
//             item.item_code,
//             item.item_name,
//             item.description || null,
//             item.quantity || 0,
//             item.uom || null,
//           ],
//         );
//       }
//     }
//     await client.query("COMMIT");
//     const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);
//     res.status(201).json({
//       success: true,
//       message: "RFQ created successfully",
//       data: completeRFQ,
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Error in createRFQFromRequisition:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// // ========================
// // EXPORTS
// // ========================
// module.exports = {
//   getAllRFQs,
//   createRFQFromPR,
//   createRFQFromRequisition,
//   sendRFQToVendors,
//   getRFQById,
//   updateRFQStatus,
//   updateRFQDeadline,
//   getRFQItems,
//   deleteRFQ,
//   hardDeleteRFQ,
//   getRFQStatistics,
//   bulkCreateRFQs,
//   saveQuotationPrices,
//   getQuotationPrices,
//   deleteQuotationPrices,
//   uploadQuotationFileMiddleware,
//   uploadQuotation,
//   createRFQFromPRInventory,
//   getQuotationUpload,
//   deleteQuotationUpload,
//   getUploadedVendors,
//   getComparisonRFQList,
//   getQuotationComparison,
//   createManualRFQ,
//   addExternalVendorsToRFQ,
//   getExternalVendorsForRFQ,
//   uploadExternalQuotation,
//   getExternalUploadedQuotations,
//   getVendorRFQItems,
// };


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

const transporter = nodemailer.createTransport({
  host: "smtp.gmail.com",
  port: 587,
  secure: false,
  ignoreTLS: true,
  auth: {
    user: process.env.MAIL_USER,
    pass: process.env.MAIL_PASS,
  },
  tls: {
    rejectUnauthorized: false,
  },
});

transporter.verify((error, success) => {
  if (error) {
    console.error("❌ SMTP Error:", error);
  } else {
    console.log("✅ SMTP Server is ready");
  }
});

// ========================
// FILE UPLOAD CONFIGURATION
// ========================
const uploadDir = "uploads/quotations";
if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });

const storage = multer.diskStorage({
  destination: (req, file, cb) => cb(null, uploadDir),
  filename: (req, file, cb) => {
    const unique = Date.now() + "-" + Math.round(Math.random() * 1e9);
    const ext = path.extname(file.originalname);
    cb(null, `quotation-${unique}${ext}`);
  },
});

const fileFilter = (req, file, cb) => {
  const allowed = ["application/pdf", "image/jpeg", "image/png"];
  if (allowed.includes(file.mimetype)) cb(null, true);
  else cb(new Error("Only PDF, JPEG, PNG allowed"));
};

const upload = multer({
  storage,
  fileFilter,
  limits: { fileSize: 5 * 1024 * 1024 },
});
const uploadQuotationFileMiddleware = upload.single("quotationFile");

// ========================
// HELPER: Generate RFQ Number
// ========================
const generateRFQNumber = async () => {
  try {
    const result = await pool.query(
      `UPDATE public.prefixes 
       SET last_number = last_number + 1 
       WHERE module_name = 'rfq' 
         AND status = 'ACTIVE' 
         AND is_deleted = false
       RETURNING prefix_code, last_number`,
    );

    if (result.rows.length === 0) {
      await pool.query(
        `INSERT INTO public.prefixes (module_name, prefix_code, last_number, status) 
         VALUES ('rfq', 'RFQ', 1, 'ACTIVE')
         ON CONFLICT (prefix_code) DO NOTHING`,
      );

      const retryResult = await pool.query(
        `UPDATE public.prefixes 
         SET last_number = last_number + 1 
         WHERE module_name = 'rfq' 
           AND status = 'ACTIVE' 
           AND is_deleted = false
         RETURNING prefix_code, last_number`,
      );

      if (retryResult.rows.length > 0) {
        const prefix = retryResult.rows[0].prefix_code;
        const nextNumber = retryResult.rows[0].last_number;
        const formattedNumber = nextNumber.toString().padStart(4, "0");
        return `${prefix}-${formattedNumber}`;
      }

      throw new Error("No prefix configuration found for RFQ");
    }

    const prefix = result.rows[0].prefix_code;
    const nextNumber = result.rows[0].last_number;
    const formattedNumber = nextNumber.toString().padStart(4, "0");
    return `${prefix}-${formattedNumber}`;
  } catch (error) {
    console.error("Error generating RFQ number:", error);
    const timestamp = Date.now().toString().slice(-6);
    return `RFQ-${timestamp}`;
  }
};

// Update the getRFQByIdInternal function to include po_raised_quantity
const getRFQByIdInternal = async (id, orgId) => {
  const result = await pool.query(
    `SELECT 
       r.id,
       r.rfq_number,
       r.rfq_date,
       r.status,
       r.sent_at,
       r.created_at,
       r.updated_at,
       r.org_id,
       COALESCE(r.pr_number, 'Manual RFQ') as pr_number,
       r.pr_id,
       COALESCE(
         (SELECT json_agg(json_build_object(
           'id', ri.id, 
           'item_code', ri.item_code, 
           'item_name', ri.item_name,
           'description', ri.description, 
           'quantity', ri.quantity,
           'po_raised_quantity', COALESCE(ri.po_raised_quantity, 0),
           'remaining_quantity', ri.quantity - COALESCE(ri.po_raised_quantity, 0),
           'uom', ri.uom
         ) ORDER BY ri.id)
         FROM rfq_items ri WHERE ri.rfq_id = r.id
       ), '[]'::json) as items,
       COALESCE(
         (SELECT json_agg(DISTINCT jsonb_build_object(
           'id', v.id,
           'vendor_name', v.vendor_name,
           'email', v.email,
           'gstin', v.gstin,
           'vendor_code', v.vendor_code,
           'selected_item_ids', qp.selected_item_ids
         ))
         FROM rfq_quotations qp
         JOIN procurement_vendors v ON v.id = qp.vendor_id
         WHERE qp.rfq_id = r.id AND v.org_id = r.org_id AND v.is_deleted = false
       ), '[]'::json) as assigned_vendors
     FROM rfqs r
     WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
    [parseInt(id), orgId],
  );

  return result.rows[0] || null;
};

// ========================
// HELPER: Generate RFQ PDF
// ========================
function fileToBase64DataUri(filePath) {
  try {
    if (!filePath || !fs.existsSync(filePath)) return null;
    const ext = path.extname(filePath).toLowerCase().slice(1);
    const mime =
      {
        png: "image/png",
        jpg: "image/jpeg",
        jpeg: "image/jpeg",
        gif: "image/gif",
        svg: "image/svg+xml",
        webp: "image/webp",
      }[ext] || "image/png";
    const data = fs.readFileSync(filePath);
    return `data:${mime};base64,${data.toString("base64")}`;
  } catch (err) {
    return null;
  }
}

function resolveLogoPath() {
  const raw = (process.env.COMPANY_LOGO || "").trim();
  const candidates = [
    path.isAbsolute(raw) ? raw : null,
    raw ? path.join(process.cwd(), raw) : null,
    raw ? path.join(process.cwd(), "public", path.basename(raw)) : null,
    path.join(process.cwd(), "public", "favicon.png"),
    path.join(process.cwd(), "public", "logo.png"),
    path.join(__dirname, "../../public", path.basename(raw || "favicon.png")),
  ].filter(Boolean);
  for (const p of candidates) {
    if (fs.existsSync(p)) return p;
  }
  return null;
}

function escapeHtml(str) {
  if (!str) return "";
  return str
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
}

// Helper function to generate PDF with requested quantities for external vendors
const generateQuotationPDFForVendor = async (
  rfq,
  vendor,
  itemsWithQuantities,
  companyData,
  terms = [],
  isInternal = false,
) => {
  try {
    const templatePath = path.join(
      __dirname,
      "../../templates/rfq-pdf-template.html",
    );
    if (!fs.existsSync(templatePath)) {
      throw new Error(`Template file not found`);
    }
    let html = fs.readFileSync(templatePath, "utf8");

    const logoPath = resolveLogoPath();
    const logoSrc = logoPath ? fileToBase64DataUri(logoPath) : "";
    const companyShort = companyData.name
      ? companyData.name.split(" ").slice(0, 2).join(" ")
      : "Company";

    const companyAddress = companyData.address || "";
    const addressParts = companyAddress.split(",");
    const addressLine1 = addressParts.slice(0, 2).join(",") || companyAddress;
    const addressLine2 = addressParts.slice(2).join(",").trim() || "";

    const rfqDate = rfq.rfq_date
      ? new Date(rfq.rfq_date).toLocaleDateString("en-GB")
      : "-";
    const deadline = rfq.deadline
      ? new Date(rfq.deadline).toLocaleDateString("en-GB")
      : "Not specified";
    const rfqNumber = rfq.rfq_number || "-";

    let itemsRows = "";
    for (let i = 0; i < itemsWithQuantities.length; i++) {
      const item = itemsWithQuantities[i];
      const quantity = Number(item.requested_quantity) || Number(item.quantity) || 0;
      itemsRows += `
        <tr>
          <td class="text-center">${i + 1}</td>
          <td><div class="item-code">${escapeHtml(item.item_code || "-")}</div><div class="item-name">${escapeHtml(item.item_name || "")}</div>${item.description ? `<div class="item-desc">${escapeHtml(item.description)}</div>` : ""}</td>
          <td class="text-center">${escapeHtml(item.uom || "-")}</td>
          <td class="text-center">${quantity.toFixed(2)}</td>
          <td class="text-right">_________</td>
          <td class="text-right">_________</td>
          <td class="text-right">_________</td>
          <td class="text-right">_________</td>
        </tr>
      `;
    }

    let termsHtml = "";
    if (terms && terms.length > 0) {
      terms.forEach((term, index) => {
        termsHtml += `${index + 1}. ${escapeHtml(term.term)} : ${escapeHtml(term.description || "")}<br />`;
      });
    } else {
      termsHtml = `1. Prices should be quoted in INR and are valid for 30 days.<br />2. GST extra as applicable.<br />3. Delivery timeline to be mentioned.<br />4. Payment terms to be negotiated.<br />5. Subject to Hyderabad jurisdiction.`;
    }

    html = html
      .replace(/{{COMPANY_LOGO}}/g, logoSrc)
      .replace(/{{COMPANY_SHORT}}/g, companyShort)
      .replace(
        /{{COMPANY_NAME}}/g,
        escapeHtml(companyData.name || "VIVEK BIO-PRODUCTS PRIVATE LIMITED"),
      )
      .replace(/{{COMPANY_ADDRESS_LINE1}}/g, escapeHtml(addressLine1))
      .replace(/{{COMPANY_ADDRESS_LINE2}}/g, escapeHtml(addressLine2))
      .replace(
        /{{COMPANY_PAN}}/g,
        escapeHtml(companyData.pan || companyData.pan_number || "AAICV8996R"),
      )
      .replace(
        /{{COMPANY_GST}}/g,
        escapeHtml(companyData.gst || companyData.gstin || "36AAICV8996R1ZU"),
      )
      .replace(
        /{{COMPANY_CIN}}/g,
        escapeHtml(
          companyData.cin || companyData.cin_number || "U24100TG2022PTC165236",
        ),
      )
      .replace(
        /{{COMPANY_EMAIL}}/g,
        escapeHtml(
          companyData.email ||
            companyData.company_email ||
            "sales@vivekbio.com",
        ),
      )
      .replace(
        /{{COMPANY_PHONE}}/g,
        escapeHtml(
          companyData.phone || companyData.phone_number || "8019637711",
        ),
      )
      .replace(
        /{{VENDOR_NAME}}/g,
        escapeHtml(vendor.vendor_name || vendor.name || vendor.email || "N/A"),
      )
      .replace(
        /{{VENDOR_GST}}/g,
        escapeHtml(vendor.gstin || vendor.gst || "N/A"),
      )
      .replace(
        /{{VENDOR_ADDRESS}}/g,
        (vendor.address || "N/A").replace(/\n/g, "<br />"),
      )
      .replace(/{{VENDOR_EMAIL}}/g, escapeHtml(vendor.email || "N/A"))
      .replace(
        /{{SUPPLY_ADDRESS}}/g,
        companyAddress.replace(/\n/g, "<br />") || "N/A",
      )
      .replace(/{{RFQ_NUMBER}}/g, escapeHtml(rfqNumber))
      .replace(/{{RFQ_DATE}}/g, rfqDate)
      .replace(/{{DEADLINE}}/g, deadline)
      .replace(/{{PR_NUMBER}}/g, escapeHtml(rfq.pr_number || "-"))
      .replace(/{{DEPARTMENT}}/g, escapeHtml(rfq.department || "Production"))
      .replace(/{{REQUESTED_BY}}/g, escapeHtml(rfq.requested_by || "System"))
      .replace(/{{STATUS}}/g, escapeHtml(rfq.status || "Draft"))
      .replace(/{{ITEMS_ROWS}}/g, itemsRows)
      .replace(/{{TERMS_CONTENT}}/g, termsHtml);

    const browser = await puppeteer.launch({
      headless: "new",
      args: ["--no-sandbox", "--disable-setuid-sandbox"],
    });
    const page = await browser.newPage();
    await page.setContent(html, { waitUntil: "networkidle0", timeout: 30000 });
    const pdf = await page.pdf({
      format: "A4",
      printBackground: true,
      margin: { top: "20px", bottom: "20px", left: "20px", right: "20px" },
    });
    await browser.close();
    return pdf;
  } catch (error) {
    console.error("Error generating RFQ PDF:", error);
    throw error;
  }
};

// ========================
// SAVE QUOTATION PRICES (Internal Vendors)
// ========================
const saveQuotationPrices = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id: rfq_id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.email;

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

    const { prices, vendorItemSelections, vendorItemQuantities } = req.body;

    console.log("📋 saveQuotationPrices called with rfq_id:", rfq_id);
    console.log("📦 vendorItemSelections:", vendorItemSelections);
    console.log("📦 vendorItemQuantities:", vendorItemQuantities);

    // Fetch RFQ details
    const rfqCheck = await client.query(
      `SELECT id, rfq_number, status, rfq_date, deadline, pr_id FROM rfqs 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [rfq_id, orgId],
    );

    if (rfqCheck.rows.length === 0) {
      await client.release();
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }

    const rfq = rfqCheck.rows[0];

    // Get RFQ items
    const itemsRes = await client.query(
      `SELECT id, item_code, item_name, description, quantity, uom
       FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
      [rfq_id],
    );
    const items = itemsRes.rows;

    if (items.length === 0) {
      await client.release();
      return res
        .status(400)
        .json({ success: false, message: "No items found for this RFQ" });
    }

    // Get all vendor IDs from vendorItemSelections
    const vendorIdsSet = new Set();
    if (vendorItemSelections) {
      for (const vendorId of Object.keys(vendorItemSelections)) {
        vendorIdsSet.add(parseInt(vendorId));
      }
    } else if (prices && prices.length > 0) {
      for (const item of prices) {
        if (item.vendorId) vendorIdsSet.add(item.vendorId);
      }
    }

    const vendorIds = Array.from(vendorIdsSet);

    if (vendorIds.length === 0) {
      await client.release();
      return res
        .status(400)
        .json({ success: false, message: "No vendors selected" });
    }

    // Get vendor details
    const vendorRes = await client.query(
      `SELECT id, vendor_name, email, gstin, address, phone 
       FROM procurement_vendors 
       WHERE id = ANY($1::int[]) AND org_id = $2 AND is_deleted = false`,
      [vendorIds, orgId],
    );
    const vendors = vendorRes.rows;

    if (vendors.length === 0) {
      await client.release();
      return res
        .status(404)
        .json({ success: false, message: "No valid vendors found" });
    }

    // Get company data for email
    let companyData = {
      name: "VIVEK BIO-PRODUCTS",
      address: "Plot no: 108, Survey No: 79, TSFPZ- Lingampally, Sadashivnagar Mandal, Kamareddy District, Telangana, 503145, India",
      gst: "36AFNPV7082B2Z1",
      cin: "U31909TG2016PTC112345",
    };

    try {
      const companyResult = await client.query(
        `SELECT name, address, gst_number, cin_number FROM organizations WHERE id = $1`,
        [String(orgId)],
      );
      if (companyResult.rows[0]) {
        const org = companyResult.rows[0];
        companyData = {
          name: org.name || companyData.name,
          address: org.address || companyData.address,
          gst: org.gst_number || companyData.gst,
          cin: org.cin_number || companyData.cin,
        };
      }
    } catch (err) {
      console.log("Using default company details");
    }

    // Get terms
    let terms = [];
    try {
      const termsResult = await client.query(
        `SELECT term, description FROM purchase_terms
         WHERE doc_type = 'quotation' AND is_active = true ORDER BY term_order ASC`,
      );
      terms = termsResult.rows;
    } catch (err) {
      console.log("No terms found");
    }

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

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

    // For each vendor, insert/update rfq_quotations and send email
    for (const vendor of vendors) {
      try {
        // Get selected item IDs for this vendor
        let selectedItemIds = vendorItemSelections
          ? vendorItemSelections[vendor.id]
          : null;

        // If no specific selections, assign all items
        if (!selectedItemIds || selectedItemIds.length === 0) {
          selectedItemIds = items.map((i) => i.id);
        }

        // Ensure it's an array of numbers
        let selectedItemIdsArray = Array.isArray(selectedItemIds)
          ? selectedItemIds
          : [Number(selectedItemIds)];
        selectedItemIdsArray = selectedItemIdsArray
          .map((id) => Number(id))
          .filter((id) => !isNaN(id));

        if (selectedItemIdsArray.length === 0) {
          console.log(`⚠️ No valid items selected for vendor ${vendor.id}, skipping...`);
          continue;
        }

        // Get quantities for this vendor's items
        const itemQuantities = vendorItemQuantities?.[vendor.id] || {};
        
        // Build quantities object for selected items
        const quantitiesObj = {};
        for (const itemId of selectedItemIdsArray) {
          // If quantity specified in vendorItemQuantities, use that, otherwise use original RFQ quantity
          const originalItem = items.find(i => i.id === itemId);
          quantitiesObj[itemId] = itemQuantities[itemId] || (originalItem ? parseFloat(originalItem.quantity) : 0);
        }

        console.log(`📝 Vendor ${vendor.id} selected items:`, selectedItemIdsArray);
        console.log(`📝 Vendor ${vendor.id} quantities:`, quantitiesObj);

        // Delete existing
        await client.query(
          `DELETE FROM rfq_quotations WHERE rfq_id = $1 AND vendor_id = $2`,
          [rfq_id, vendor.id],
        );

        // Insert new record with quantities
        await client.query(
          `INSERT INTO rfq_quotations(rfq_id, vendor_id, selected_item_ids, selected_item_quantities, created_by, updated_by)
           VALUES ($1, $2, $3, $4, $5, $6)`,
          [rfq_id, vendor.id, selectedItemIdsArray, quantitiesObj, userId, userId],
        );

        // Prepare items with quantities for PDF
        const itemsWithQuantities = items
          .filter(item => selectedItemIdsArray.includes(item.id))
          .map(item => ({
            ...item,
            requested_quantity: quantitiesObj[item.id] || parseFloat(item.quantity) || 0
          }));

        // Send email to internal vendor (don't let email failure break the transaction)
        try {
          const pdfBytes = await generateQuotationPDFForVendor(
            rfq,
            vendor,
            itemsWithQuantities,
            companyData,
            terms,
            true
          );

          const pdfFileName = `Quotation_${rfq.rfq_number}_${vendor.vendor_name.replace(/\s/g, '_')}.pdf`;
          const pdfFilePath = path.join(tempDir, pdfFileName);
          fs.writeFileSync(pdfFilePath, pdfBytes);

          const itemsTableHtml = itemsWithQuantities
            .map((item, index) => {
              return `
              <tr>
                <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${index + 1}</td>
                <td style="padding: 8px; border: 1px solid #ddd;">${escapeHtml(item.item_code || "-")}</td>
                <td style="padding: 8px; border: 1px solid #ddd;">${escapeHtml(item.item_name || "-")}</td>
                <td style="padding: 8px; border: 1px solid #ddd;">${escapeHtml(item.description || "-")}</td>
                <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${item.requested_quantity || 0}</td>
                <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${escapeHtml(item.uom || "-")}</td>
              </tr>
            `;
            })
            .join("");

          const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
          
          const mailOptions = {
            from: `"Procurement Team" <${process.env.MAIL_USER}>`,
            to: vendor.email,
            subject: `${rfq.rfq_number} - Request for Quotation - ${companyData.name}`,
            html: `
              <div style="font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto;">
                <h2 style="color: #1a56db; text-align: center;">REQUEST FOR QUOTATION</h2>
                <p style="text-align: center; font-size: 14px; color: #666;">${companyData.name}</p>
                
                <p>Dear <strong>${escapeHtml(vendor.vendor_name)}</strong>,</p>
                
                <p>You have been invited to submit a quotation for <strong>${rfq.rfq_number}</strong>.</p>
                
                <div style="background-color: #e8f0fe; padding: 10px; border-radius: 5px; margin: 15px 0;">
                  <strong>📋 Items You Need to Quote:</strong> ${itemsWithQuantities.length} of ${items.length} items
                </div>
                
                <h3>Items You Need to Quote:</h3>
                <table style="width: 100%; border-collapse: collapse;">
                  <thead><tr style="background-color: #f0f0f0;">
                    <th style="padding: 8px; border: 1px solid #ddd;">#</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Item Code</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Item Name</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Description</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Qty Required</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">UOM</th>
                  </tr></thead>
                  <tbody>${itemsTableHtml}</tbody>
                </table>
                
                <div style="background-color: #fff3cd; padding: 15px; border-radius: 5px; margin: 20px 0;">
                  <strong>📌 Instructions:</strong>
                  <ul>
                    <li>Download the attached PDF file</li>
                    <li>Fill in your Unit Prices for each item</li>
                    <li>Upload the filled quotation using the link below</li>
                  </ul>
                </div>
                
                <p>Upload Link: <a href="${frontendUrl}/procurement/upload-quotation/${rfq_id}">Click here to upload your quotation</a></p>
                
                <p>Regards,<br/><strong>Procurement Team</strong><br/>${companyData.name}</p>
              </div>
            `,
            attachments: [
              {
                filename: pdfFileName,
                path: pdfFilePath,
                contentType: "application/pdf",
              },
            ],
          };

          await transporter.sendMail(mailOptions);
          console.log(`✅ Email sent to internal vendor: ${vendor.email}`);

          // Clean up temp file
          setTimeout(() => {
            if (fs.existsSync(pdfFilePath)) fs.unlinkSync(pdfFilePath);
          }, 10000);
        } catch (emailError) {
          console.error(`❌ Failed to send email to ${vendor.email}:`, emailError.message);
          // Don't throw error - continue with transaction
        }

        // Update pr_inventory to mark quantities sent for RFQ
        if (rfq.pr_id) {
          for (const item of items) {
            const sentQuantity = quantitiesObj[item.id] || 0;
            if (sentQuantity > 0) {
              const inventoryRecord = await client.query(
                `SELECT id, remaining_quantity, rfq_sent_quantity, status
                 FROM pr_inventory 
                 WHERE requisition_id = $1 
                   AND item_code = $2
                 ORDER BY id ASC
                 LIMIT 1`,
                [rfq.pr_id, item.item_code],
              );

              if (inventoryRecord.rows.length > 0) {
                const current = inventoryRecord.rows[0];
                const currentRfqSent = parseFloat(current.rfq_sent_quantity) || 0;
                
                await client.query(
                  `UPDATE pr_inventory 
                   SET rfq_sent_quantity = $1,
                       status = CASE 
                         WHEN $1 >= remaining_quantity THEN 'Fully Quoted'
                         ELSE 'Partially Quoted'
                       END,
                       updated_at = NOW(),
                       updated_by = $2
                   WHERE id = $3`,
                  [currentRfqSent + sentQuantity, userId, current.id],
                );
              }
            }
          }
        }

        console.log(`✅ Vendor ${vendor.vendor_name} (${vendor.id}) assigned with ${selectedItemIdsArray.length} items`);
      } catch (vendorError) {
        console.error(`❌ Error processing vendor ${vendor.id}:`, vendorError);
        // Continue with other vendors
      }
    }

    // Update RFQ status
    if (rfq.status === "Draft") {
      await client.query(
        `UPDATE rfqs SET status = 'Sent', sent_at = NOW(), updated_at = NOW() WHERE id = $1`,
        [rfq_id],
      );
      console.log("📝 RFQ status updated from Draft to Sent");
    }

    // COMMIT TRANSACTION
    await client.query("COMMIT");
    
    console.log("✅ Transaction committed successfully");

    res.json({
      success: true,
      message: `RFQ sent to ${vendors.length} vendor(s)`,
      data: { vendorsCount: vendors.length, rfqNumber: rfq.rfq_number },
    });
  } catch (err) {
    // ROLLBACK TRANSACTION on error
    try {
      await client.query("ROLLBACK");
      console.log("🔄 Transaction rolled back due to error");
    } catch (rollbackErr) {
      console.error("Error during rollback:", rollbackErr);
    }
    
    console.error("Error in saveQuotationPrices:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    // Always release the client back to the pool
    client.release();
  }
};

// ========================
// GET VENDOR-SPECIFIC RFQ ITEMS
// ========================
const getVendorRFQItems = async (req, res) => {
  try {
    const { rfqId, vendorId } = req.params;
    const orgId = req.user?.orgId;

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

    // Get selected_item_ids for this vendor
    const vendorItemsResult = await pool.query(
      `SELECT selected_item_ids, selected_item_quantities FROM rfq_quotations 
       WHERE rfq_id = $1 AND vendor_id = $2`,
      [rfqId, vendorId],
    );

    const selectedItemIds = vendorItemsResult.rows[0]?.selected_item_ids || [];
    const selectedItemQuantities = vendorItemsResult.rows[0]?.selected_item_quantities || {};

    // Get all RFQ items
    const allItems = await pool.query(
      `SELECT id, item_code, item_name, description, quantity, uom
       FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
      [rfqId],
    );

    // Filter items based on selection and add requested quantity
    let items = allItems.rows;
    if (selectedItemIds.length > 0) {
      items = items.filter((item) => selectedItemIds.includes(item.id));
    }

    // Add requested_quantity to each item
    const itemsWithQuantities = items.map(item => ({
      ...item,
      requested_quantity: selectedItemQuantities[item.id] || parseFloat(item.quantity) || 0,
      original_quantity: parseFloat(item.quantity) || 0
    }));

    res.json({
      success: true,
      data: {
        items: itemsWithQuantities,
        total_items: allItems.rows.length,
        selected_items_count: items.length,
        is_partial:
          selectedItemIds.length > 0 &&
          selectedItemIds.length < allItems.rows.length,
      },
    });
  } catch (err) {
    console.error("Error in getVendorRFQItems:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// CREATE RFQ FROM PR INVENTORY
// ========================
const createRFQFromPRInventory = async (req, res) => {
  const client = await pool.connect();

  try {
    const { pr_id, items, vendorIds = [] } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.email;

    console.log("📋 createRFQFromPRInventory called with:", { pr_id, items, vendorIds });

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

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

    if (!items || !Array.isArray(items) || items.length === 0) {
      return res.status(400).json({
        success: false,
        message: "At least one item is required",
      });
    }

    // Check if RFQ already exists for this PR
    const existingRFQ = await client.query(
      `SELECT id, rfq_number, status FROM rfqs 
       WHERE pr_id = $1 AND org_id = $2 AND is_deleted = false`,
      [pr_id, orgId],
    );

    if (existingRFQ.rows.length > 0) {
      const existing = existingRFQ.rows[0];
      return res.status(400).json({
        success: false,
        message: `RFQ already exists for this PR: ${existing.rfq_number}`,
        data: {
          id: existing.id,
          rfq_number: existing.rfq_number,
          status: existing.status,
        },
      });
    }

    // Get PR details
    const prResult = await client.query(
      `SELECT pr_number, department, requested_by 
       FROM requisitions 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [pr_id, orgId],
    );

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

    const prData = prResult.rows[0];
    const rfq_number = await generateRFQNumber();
    const rfq_date = new Date().toISOString().split("T")[0];

    await client.query("BEGIN");

    // Create RFQ
    const rfqResult = await client.query(
      `INSERT INTO rfqs (
        rfq_number, rfq_date, pr_number, pr_id, 
        status, org_id, created_by, updated_by
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
      RETURNING id, rfq_number, rfq_date, status`,
      [
        rfq_number,
        rfq_date,
        prData.pr_number,
        pr_id,
        "Draft",
        String(orgId),
        userId,
        userId,
      ],
    );

    const rfq = rfqResult.rows[0];

    // Insert RFQ items and UPDATE pr_inventory
    for (const item of items) {
      let quantity = Number(item.quantity) || 0;
      
      if (quantity === 0 && item.remaining_quantity) {
        quantity = Number(item.remaining_quantity) || 0;
      }
      
      if (quantity === 0) {
        console.warn(`⚠️ Quantity is 0 for item: ${item.item_name || item.item_code}, using default 1`);
        quantity = 1;
      }
      
      console.log(`📝 Inserting RFQ item: ${item.item_code} - ${item.item_name}, Quantity: ${quantity} ${item.uom}`);
      
      await client.query(
        `INSERT INTO rfq_items (
          rfq_id, item_code, item_name, description, quantity, uom
        ) VALUES ($1, $2, $3, $4, $5, $6)`,
        [
          rfq.id,
          item.item_code,
          item.item_name,
          item.description || null,
          quantity,
          item.uom || null,
        ],
      );

      const requisitionItemId = item.requisition_item_id || item.id;
      
      const inventoryRecord = await client.query(
        `SELECT id, remaining_quantity, po_requested_quantity, rfq_requested_quantity, status
         FROM pr_inventory 
         WHERE requisition_item_id = $1 
           AND requisition_id = $2
         ORDER BY id ASC
         LIMIT 1`,
        [requisitionItemId, pr_id],
      );

      if (inventoryRecord.rows.length > 0) {
        const current = inventoryRecord.rows[0];
        const currentRfqRequested = parseFloat(current.rfq_requested_quantity) || 0;
        const currentPoRequested = parseFloat(current.po_requested_quantity) || 0;
        const currentRemaining = parseFloat(current.remaining_quantity) || 0;
        
        const newRemaining = currentRemaining - quantity;
        
        await client.query(
          `UPDATE pr_inventory 
           SET rfq_requested_quantity = $1,
               po_requested_quantity = $2,
               remaining_quantity = $3,
               status = $4,
               updated_at = NOW(),
               updated_by = $5
           WHERE id = $6`,
          [
            currentRfqRequested + quantity, 
            currentPoRequested + quantity,
            newRemaining,
            newRemaining <= 0 ? 'Fully Quoted' : 'Partially Quoted',
            userId, 
            current.id
          ],
        );
        
        console.log(`✅ Updated pr_inventory: item=${item.item_name}, RFQ Qty=${quantity}, New Remaining=${newRemaining}`);
      } else {
        await client.query(
          `INSERT INTO pr_inventory (
            requisition_id, requisition_item_id, item_code, item_name,
            requested_quantity, remaining_quantity, 
            issued_quantity, po_requested_quantity, rfq_requested_quantity, uom, org_id, status
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`,
          [
            pr_id,
            requisitionItemId,
            item.item_code,
            item.item_name,
            quantity,
            quantity,
            0,
            quantity,
            quantity,
            item.uom || null,
            orgId,
            'Partially Quoted'
          ],
        );
        console.log(`✅ Created new pr_inventory record: item=${item.item_name}, RFQ Qty=${quantity}`);
      }
    }

    // If vendors are provided, assign them to the RFQ
    if (vendorIds && vendorIds.length > 0) {
      for (const vendorId of vendorIds) {
        const rfqItems = await client.query(
          `SELECT id FROM rfq_items WHERE rfq_id = $1`,
          [rfq.id],
        );

        const selectedItemIds = rfqItems.rows.map((row) => row.id);
        
        const quantitiesObj = {};
        for (const item of items) {
          const rfqItem = rfqItems.rows.find((_, idx) => idx === items.indexOf(item));
          if (rfqItem) {
            quantitiesObj[rfqItem.id] = Number(item.quantity) || 0;
          }
        }

        await client.query(
          `INSERT INTO rfq_quotations(rfq_id, vendor_id, selected_item_ids, selected_item_quantities, created_by, updated_by)
           VALUES ($1, $2, $3, $4, $5, $6)
           ON CONFLICT (rfq_id, vendor_id) DO NOTHING`,
          [rfq.id, vendorId, selectedItemIds, quantitiesObj, userId, userId],
        );
        
        console.log(`✅ Vendor ${vendorId} assigned to RFQ ${rfq.rfq_number}`);
      }
    }

    // Update requisition po_status
    await client.query(
      `UPDATE requisitions 
       SET po_status = 'RFQ Requested', updated_at = NOW()
       WHERE id = $1`,
      [pr_id],
    );

    await client.query("COMMIT");

    const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);
    
    console.log("✅ RFQ created successfully:", completeRFQ);

    res.status(201).json({
      success: true,
      message: `RFQ ${rfq.rfq_number} created successfully from PR`,
      data: completeRFQ,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createRFQFromPRInventory:", err);
    res.status(500).json({
      success: false,
      message: err.message,
      stack: process.env.NODE_ENV === "development" ? err.stack : undefined,
    });
  } finally {
    client.release();
  }
};

// ========================
// GET QUOTATION PRICES
// ========================
const getQuotationPrices = async (req, res) => {
  try {
    const { id: rfq_id } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT
         qp.id,
         qp.rfq_id,
         qp.vendor_id,
         qp.selected_item_ids,
         qp.selected_item_quantities,
         v.vendor_name,
         v.vendor_code,
         v.gstin,
         v.email,
         v.address,
         v.phone,
         'internal' as vendor_type
       FROM rfq_quotations qp
       JOIN procurement_vendors v ON v.id = qp.vendor_id
       JOIN rfqs r ON r.id = qp.rfq_id
       WHERE qp.rfq_id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
      [rfq_id, orgId],
    );

    console.log(`📋 getQuotationPrices returned ${result.rows.length} vendor assignments`);

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("Error in getQuotationPrices:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// GET UPLOADED VENDORS
// ========================
const getUploadedVendors = async (req, res) => {
  try {
    const { id: rfqId } = req.params;
    const orgId = req.user?.orgId;

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

    const internalResult = await pool.query(
      `SELECT DISTINCT vendor_id 
       FROM rfq_uploaded_quotations 
       WHERE rfq_id = $1 AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $2)`,
      [rfqId, orgId],
    );

    const externalResult = await pool.query(
      `SELECT id as vendor_id 
       FROM rfq_external_vendors 
       WHERE rfq_id = $1 AND quotation_uploaded = true`,
      [rfqId],
    );

    const uploadedVendorIds = [
      ...internalResult.rows.map((row) => row.vendor_id),
      ...externalResult.rows.map((row) => row.vendor_id),
    ];

    res.json({ success: true, data: uploadedVendorIds });
  } catch (err) {
    console.error("Error in getUploadedVendors:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// ========================
// GET QUOTATION COMPARISON (with remaining quantities)
// ========================
const getQuotationComparison = async (req, res) => {
  try {
    const { id: rfqId } = req.params;
    const { vendorId } = req.params;
    const orgId = req.user?.orgId;

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

    const parsedId = parseInt(rfqId, 10);
    if (isNaN(parsedId)) {
      return res
        .status(400)
        .json({ success: false, message: "Invalid RFQ ID" });
    }

    const rfqCheck = await pool.query(
      `SELECT id, rfq_number, status, pr_id FROM rfqs 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parsedId, orgId],
    );
    if (rfqCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }

    const rfqInfo = rfqCheck.rows[0];

    // Get ALL RFQ items with remaining quantities
    const allItemsResult = await pool.query(
      `SELECT id, item_code, item_name, description, quantity, uom,
              COALESCE(po_raised_quantity, 0) as po_raised_quantity,
              quantity - COALESCE(po_raised_quantity, 0) as remaining_quantity
       FROM rfq_items 
       WHERE rfq_id = $1 
       ORDER BY id`,
      [parsedId],
    );
    const allItems = allItemsResult.rows;

    if (allItems.length === 0) {
      return res.json({
        success: true,
        data: { items: [], vendors: [], vendorPrices: {} },
      });
    }

    let vendors = [];
    let priceMatrix = {};
    let quotedItemIdsPerVendor = {};

    if (vendorId) {
      // Get specific internal vendor
      const internalVendorResult = await pool.query(
        `SELECT DISTINCT 
           v.id, v.vendor_name, 'internal' as vendor_type, v.email, v.gstin,
           uq.total_amount, uq.vendor_terms, uq.uploaded_at
         FROM rfq_uploaded_quotations uq
         JOIN procurement_vendors v ON v.id = uq.vendor_id
         WHERE uq.rfq_id = $1 AND uq.vendor_id = $2`,
        [parsedId, vendorId],
      );

      // Check external vendor if not found internally
      let externalVendorResult = { rows: [] };
      if (internalVendorResult.rows.length === 0) {
        externalVendorResult = await pool.query(
          `SELECT ev.id, ev.vendor_name, 'external' as vendor_type, ev.email,
            NULL as gstin, ev.total_amount, ev.vendor_terms, ev.uploaded_at
           FROM rfq_external_vendors ev
           WHERE ev.rfq_id = $1 AND ev.id = $2 AND ev.quotation_uploaded = true`,
          [parsedId, vendorId],
        );
      }

      vendors = [...internalVendorResult.rows, ...externalVendorResult.rows];

      if (vendors.length > 0) {
        const internalPrices = await pool.query(
          `SELECT uq.vendor_id, uqi.rfq_item_id, uqi.unit_price
           FROM rfq_uploaded_quotations uq
           JOIN rfq_uploaded_quotation_items uqi ON uqi.uploaded_quotation_id = uq.id
           WHERE uq.rfq_id = $1 AND uq.vendor_id = $2`,
          [parsedId, vendorId],
        );

        const externalPrices = await pool.query(
          `SELECT eqi.external_vendor_id as vendor_id, eqi.rfq_item_id, eqi.unit_price
           FROM rfq_external_quotation_items eqi
           JOIN rfq_external_vendors ev ON ev.id = eqi.external_vendor_id
           WHERE ev.rfq_id = $1 AND ev.id = $2 AND ev.quotation_uploaded = true`,
          [parsedId, vendorId],
        );

        const quotedIds = new Set();

        for (const row of internalPrices.rows) {
          if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
          priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
            row.unit_price,
          );
          quotedIds.add(row.rfq_item_id);
        }
        for (const row of externalPrices.rows) {
          if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
          priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
            row.unit_price,
          );
          quotedIds.add(row.rfq_item_id);
        }

        quotedItemIdsPerVendor[vendorId] = quotedIds;
      }
    } else {
      // Get all internal vendors who uploaded quotations
      const internalVendorsResult = await pool.query(
        `SELECT DISTINCT 
           v.id, v.vendor_name, 'internal' as vendor_type, v.email, v.gstin,
           uq.total_amount, uq.vendor_terms, uq.uploaded_at
         FROM rfq_uploaded_quotations uq
         JOIN procurement_vendors v ON v.id = uq.vendor_id
         WHERE uq.rfq_id = $1 
         ORDER BY v.vendor_name`,
        [parsedId],
      );

      // Get external vendors who uploaded quotations
      const externalVendorsResult = await pool.query(
        `SELECT 
           ev.id, ev.vendor_name, 'external' as vendor_type, ev.email,
           NULL as gstin, ev.total_amount, ev.vendor_terms, ev.uploaded_at
         FROM rfq_external_vendors ev
         WHERE ev.rfq_id = $1 AND ev.quotation_uploaded = true
         ORDER BY ev.vendor_name`,
        [parsedId],
      );

      vendors = [...internalVendorsResult.rows, ...externalVendorsResult.rows];

      const internalPricesResult = await pool.query(
        `SELECT uq.vendor_id, uqi.rfq_item_id, uqi.unit_price
         FROM rfq_uploaded_quotations uq
         JOIN rfq_uploaded_quotation_items uqi ON uqi.uploaded_quotation_id = uq.id
         WHERE uq.rfq_id = $1`,
        [parsedId],
      );

      const externalPricesResult = await pool.query(
        `SELECT eqi.external_vendor_id as vendor_id, eqi.rfq_item_id, eqi.unit_price
         FROM rfq_external_quotation_items eqi
         JOIN rfq_external_vendors ev ON ev.id = eqi.external_vendor_id
         WHERE ev.rfq_id = $1 AND ev.quotation_uploaded = true`,
        [parsedId],
      );

      for (const row of internalPricesResult.rows) {
        if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
        priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
          row.unit_price,
        );

        if (!quotedItemIdsPerVendor[row.vendor_id]) {
          quotedItemIdsPerVendor[row.vendor_id] = new Set();
        }
        quotedItemIdsPerVendor[row.vendor_id].add(row.rfq_item_id);
      }
      for (const row of externalPricesResult.rows) {
        if (!priceMatrix[row.vendor_id]) priceMatrix[row.vendor_id] = {};
        priceMatrix[row.vendor_id][row.rfq_item_id] = parseFloat(
          row.unit_price,
        );

        if (!quotedItemIdsPerVendor[row.vendor_id]) {
          quotedItemIdsPerVendor[row.vendor_id] = new Set();
        }
        quotedItemIdsPerVendor[row.vendor_id].add(row.rfq_item_id);
      }
    }

    const vendorItemsMap = {};
    for (const vendor of vendors) {
      const quotedIds = quotedItemIdsPerVendor[vendor.id] || new Set();
      const quotedItems = allItems.filter((item) => quotedIds.has(item.id));
      vendorItemsMap[vendor.id] = quotedItems;
    }

    const vendorPrices = {};
    for (const vendor of vendors) {
      vendorPrices[vendor.id] = allItems.map(
        (item) => priceMatrix[vendor.id]?.[item.id] || null,
      );
    }

    res.json({
      success: true,
      data: {
        items: allItems,
        vendors,
        vendorPrices,
        vendorItemsMap,
        rfq_id: parsedId,
        rfq_info: {
          rfq_number: rfqInfo.rfq_number,
          status: rfqInfo.status,
          pr_id: rfqInfo.pr_id
        },
        specific_vendor: vendorId || null,
      },
    });
  } catch (err) {
    console.error("Error in getQuotationComparison:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// UPLOAD QUOTATION (Internal Vendor submits)
// ========================
const uploadQuotation = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id: rfqId } = req.params;
    const { vendorId, notes, totalAmount, items, vendorTerms } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.email;

    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!vendorId)
      return res
        .status(400)
        .json({ success: false, message: "Vendor ID required" });
    if (!req.file)
      return res
        .status(400)
        .json({ success: false, message: "Quotation file required" });

    const rfqCheck = await client.query(
      `SELECT id, status FROM rfqs WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [rfqId, orgId],
    );
    if (rfqCheck.rows.length === 0)
      return res.status(404).json({ success: false, message: "RFQ not found" });

    const vendorItems = await client.query(
      `SELECT selected_item_ids FROM rfq_quotations 
       WHERE rfq_id = $1 AND vendor_id = $2`,
      [rfqId, vendorId],
    );

    const allowedItemIds = vendorItems.rows[0]?.selected_item_ids || [];

    let itemsArray = [];
    try {
      itemsArray = JSON.parse(items || "[]");
    } catch (e) {
      itemsArray = [];
    }

    for (const it of itemsArray) {
      if (allowedItemIds.length > 0 && !allowedItemIds.includes(it.rfqItemId)) {
        return res.status(400).json({
          success: false,
          message: `Item ${it.rfqItemId} is not assigned to this vendor`,
        });
      }
    }

    const filePath = `/uploads/quotations/${req.file.filename}`;
    const fileName = req.file.originalname;
    const fileSize = req.file.size;
    const mimeType = req.file.mimetype;

    await client.query("BEGIN");

    const headerResult = await client.query(
      `INSERT INTO rfq_uploaded_quotations 
        (rfq_id, vendor_id, file_name, file_path, file_size, mime_type, notes, uploaded_by, status, total_amount, vendor_terms)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'Uploaded', $9, $10)
       ON CONFLICT (rfq_id, vendor_id) 
       DO UPDATE SET 
         file_name = EXCLUDED.file_name,
         file_path = EXCLUDED.file_path,
         file_size = EXCLUDED.file_size,
         mime_type = EXCLUDED.mime_type,
         notes = EXCLUDED.notes,
         uploaded_by = EXCLUDED.uploaded_by,
         status = 'Uploaded',
         total_amount = EXCLUDED.total_amount,
         vendor_terms = EXCLUDED.vendor_terms,
         uploaded_at = NOW()
       RETURNING id`,
      [
        rfqId,
        vendorId,
        fileName,
        filePath,
        fileSize,
        mimeType,
        notes,
        userId,
        totalAmount || 0,
        vendorTerms || null,
      ],
    );
    const uploadedQuotationId = headerResult.rows[0].id;

    await client.query(
      `DELETE FROM rfq_uploaded_quotation_items WHERE uploaded_quotation_id = $1`,
      [uploadedQuotationId],
    );

    for (const it of itemsArray) {
      await client.query(
        `INSERT INTO rfq_uploaded_quotation_items 
          (uploaded_quotation_id, rfq_item_id, quantity, unit_price)
         VALUES ($1, $2, $3, $4)`,
        [uploadedQuotationId, it.rfqItemId, it.quantity, it.unitPrice],
      );
    }

    const assignedCountResult = await client.query(
      `SELECT COUNT(DISTINCT vendor_id) AS count FROM rfq_quotations WHERE rfq_id = $1`,
      [rfqId],
    );
    const assignedCount = parseInt(assignedCountResult.rows[0].count, 10);

    const uploadedCountResult = await client.query(
      `SELECT COUNT(DISTINCT vendor_id) AS count FROM rfq_uploaded_quotations WHERE rfq_id = $1`,
      [rfqId],
    );
    const uploadedCount = parseInt(uploadedCountResult.rows[0].count, 10);

    if (assignedCount > 0 && uploadedCount === assignedCount) {
      await client.query(
        `UPDATE rfqs SET status = 'Quotations Received', updated_at = NOW() WHERE id = $1`,
        [rfqId],
      );
    }

    await client.query("COMMIT");
    res
      .status(201)
      .json({ success: true, message: "Quotation uploaded successfully" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in uploadQuotation:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// ADD EXTERNAL VENDORS TO RFQ (WITH EMAIL AND QUANTITIES)
// ========================
const addExternalVendorsToRFQ = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id: rfqId } = req.params;
    const { externalEmails, selectedItemIds, requestedQuantities } = req.body;
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId || req.user?.email || null;

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

    if (
      !externalEmails ||
      !Array.isArray(externalEmails) ||
      externalEmails.length === 0
    ) {
      await client.release();
      return res
        .status(400)
        .json({ success: false, message: "At least one email is required" });
    }

    // Verify RFQ exists
    const rfqCheck = await client.query(
      `SELECT id, rfq_number, rfq_date, status FROM rfqs WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [rfqId, orgId],
    );
    if (rfqCheck.rows.length === 0) {
      await client.release();
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }
    const rfq = rfqCheck.rows[0];

    // Get RFQ items
    const itemsResult = await client.query(
      `SELECT id, item_code, item_name, description, quantity, uom
       FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
      [rfqId],
    );
    const items = itemsResult.rows;

    if (items.length === 0) {
      await client.release();
      return res.status(400).json({ success: false, message: "No items found for this RFQ" });
    }

    // Determine which items to send
    let itemsToSend = items;
    if (selectedItemIds && Array.isArray(selectedItemIds) && selectedItemIds.length > 0) {
      itemsToSend = items.filter(item => selectedItemIds.includes(item.id));
    }

    // Build quantities map
    const quantitiesMap = requestedQuantities || {};
    
    const itemsWithQuantities = itemsToSend.map(item => ({
      ...item,
      requested_quantity: quantitiesMap[item.id] || parseFloat(item.quantity) || 0
    }));

    console.log(`📦 Sending ${itemsWithQuantities.length} items to external vendors`);
    console.log(`📦 Quantities:`, quantitiesMap);

    // Company data
    let companyData = {
      name: "VIVEK BIO-PRODUCTS",
      address: "1-8-303/48/15, KRK House, P.G. Road, Begumpet, Hyderabad – 500003, Telangana, India.",
      gst: "36AFNPV7082B2Z1",
      cin: "U31909TG2016PTC112345",
    };

    try {
      const companyResult = await client.query(
        `SELECT name, address, gst_number, cin_number FROM organizations WHERE id = $1`,
        [String(orgId)],
      );
      if (companyResult.rows[0]) {
        const org = companyResult.rows[0];
        companyData = {
          name: org.name || companyData.name,
          address: org.address || companyData.address,
          gst: org.gst_number || companyData.gst,
          cin: org.cin_number || companyData.cin,
        };
      }
    } catch (err) {
      console.log("Using default company details");
    }

    // Get terms
    let terms = [];
    try {
      const termsResult = await client.query(
        `SELECT term, description FROM purchase_terms
         WHERE doc_type = 'quotation' AND is_active = true ORDER BY term_order ASC`,
      );
      terms = termsResult.rows;
    } catch (err) {
      console.log("No terms found");
    }

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

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

    const insertedEmails = [];
    const sentEmails = [];
    const failedEmails = [];

    for (const email of externalEmails) {
      const trimmedEmail = email.trim();

      // Check if external vendor already exists
      const existing = await client.query(
        `SELECT id FROM rfq_external_vendors WHERE rfq_id = $1 AND email = $2`,
        [rfqId, trimmedEmail],
      );

      if (existing.rows.length === 0) {
        // Prepare JSON data properly
        const selectedItemsList = itemsToSend.map(i => i.id);
        
        // Build selected_item_quantities as a proper JSON object
        const selectedItemQuantities = {};
        itemsToSend.forEach(item => {
          selectedItemQuantities[item.id] = quantitiesMap[item.id] || parseFloat(item.quantity) || 0;
        });

        // Convert to JSON strings for PostgreSQL JSONB
        const selectedItemsJson = JSON.stringify(selectedItemsList);
        const selectedQuantitiesJson = JSON.stringify(selectedItemQuantities);

        console.log(`📝 Inserting external vendor: ${trimmedEmail}`);
        console.log(`📝 Selected items JSON: ${selectedItemsJson}`);
        console.log(`📝 Quantities JSON: ${selectedQuantitiesJson}`);

        // Insert with properly formatted JSON
        const result = await client.query(
          `INSERT INTO rfq_external_vendors (rfq_id, email, vendor_name, selected_item_ids, selected_item_quantities)
           VALUES ($1, $2, $3, $4::jsonb, $5::jsonb)
           RETURNING id, email, vendor_name`,
          [rfqId, trimmedEmail, trimmedEmail.split("@")[0], selectedItemsJson, selectedQuantitiesJson],
        );
        insertedEmails.push(result.rows[0]);

        // Send email to external vendor
        try {
          if (itemsWithQuantities.length === 0) {
            console.log(`No items selected for external vendor ${trimmedEmail}, skipping`);
            continue;
          }

          const vendor = {
            vendor_name: trimmedEmail.split("@")[0],
            email: trimmedEmail,
            address: "N/A",
            gstin: "N/A",
          };

          const pdfBytes = await generateQuotationPDFForVendor(
            rfq,
            vendor,
            itemsWithQuantities,
            companyData,
            terms,
            false
          );

          const pdfFileName = `Quotation_${rfq.rfq_number}_${vendor.vendor_name.replace(/[^a-zA-Z0-9]/g, '_')}.pdf`;
          const pdfFilePath = path.join(tempDir, pdfFileName);
          fs.writeFileSync(pdfFilePath, pdfBytes);

          const itemsTableHtml = itemsWithQuantities
            .map((item, index) => {
              return `
              <tr>
                <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${index + 1}</td>
                <td style="padding: 8px; border: 1px solid #ddd;">${escapeHtml(item.item_code || "-")}</td>
                <td style="padding: 8px; border: 1px solid #ddd;">${escapeHtml(item.item_name || "-")}</td>
                <td style="padding: 8px; border: 1px solid #ddd;">${escapeHtml(item.description || "-")}</td>
                <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${item.requested_quantity || 0}</td>
                <td style="padding: 8px; border: 1px solid #ddd; text-align: center;">${escapeHtml(item.uom || "-")}</td>
              </tr>
            `;
            })
            .join("");

          const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
          
          const mailOptions = {
            from: `"Procurement Team" <${process.env.MAIL_USER}>`,
            to: trimmedEmail,
            subject: `${rfq.rfq_number} - Request for Quotation - ${companyData.name}`,
            html: `
              <div style="font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto;">
                <h2 style="color: #1a56db; text-align: center;">REQUEST FOR QUOTATION</h2>
                <p style="text-align: center; font-size: 14px; color: #666;">${companyData.name}</p>
                
                <p>Dear <strong>${escapeHtml(vendor.vendor_name)}</strong>,</p>
                
                <p>You have been invited to submit a quotation for <strong>${rfq.rfq_number}</strong>.</p>
                
                <div style="background-color: #e8f0fe; padding: 10px; border-radius: 5px; margin: 15px 0;">
                  <strong>📋 Items You Need to Quote:</strong> ${itemsWithQuantities.length} of ${items.length} items
                </div>
                
                <h3>Items You Need to Quote:</h3>
                <table style="width: 100%; border-collapse: collapse;">
                  <thead><tr style="background-color: #f0f0f0;">
                    <th style="padding: 8px; border: 1px solid #ddd;">#</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Item Code</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Item Name</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Description</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">Qty Required</th>
                    <th style="padding: 8px; border: 1px solid #ddd;">UOM</th>
                  </tr></thead>
                  <tbody>${itemsTableHtml}</tbody>
                </table>
                
                <div style="background-color: #fff3cd; padding: 15px; border-radius: 5px; margin: 20px 0;">
                  <strong>📌 Instructions:</strong>
                  <ul>
                    <li>Download the attached PDF file</li>
                    <li>Fill in your Unit Prices for each item</li>
                    <li>Upload the filled quotation using the link below</li>
                  </ul>
                </div>
                
                <p>Upload Link: <a href="${frontendUrl}/procurement/upload-quotation/${rfqId}">Click here to upload your quotation</a></p>
                
                <p>Regards,<br/><strong>Procurement Team</strong><br/>${companyData.name}</p>
              </div>
            `,
            attachments: [
              {
                filename: pdfFileName,
                path: pdfFilePath,
                contentType: "application/pdf",
              },
            ],
          };

          await transporter.sendMail(mailOptions);
          sentEmails.push(trimmedEmail);
          console.log(`✅ Email sent to external vendor: ${trimmedEmail}`);

          // Clean up temp file
          setTimeout(() => {
            if (fs.existsSync(pdfFilePath)) fs.unlinkSync(pdfFilePath);
          }, 10000);
        } catch (emailError) {
          console.error(`Failed to send email to ${trimmedEmail}:`, emailError.message);
          failedEmails.push({ email: trimmedEmail, error: emailError.message });
        }
      } else {
        console.log(`External vendor ${trimmedEmail} already exists for RFQ ${rfqId}`);
      }
    }

    // Update RFQ status if it was in Draft
    if (rfq.status === "Draft" && insertedEmails.length > 0) {
      await client.query(
        `UPDATE rfqs SET status = 'Sent', sent_at = NOW(), updated_at = NOW() WHERE id = $1`,
        [rfqId],
      );
      console.log("📝 RFQ status updated from Draft to Sent");
    }

    // COMMIT TRANSACTION
    await client.query("COMMIT");
    
    console.log("✅ External vendors transaction committed successfully");

    let message = `${insertedEmails.length} external vendor(s) added. Emails sent to ${sentEmails.length}.`;
    if (failedEmails.length > 0) {
      message += ` Failed: ${failedEmails.length}`;
    }
    
    res.json({
      success: true,
      message,
      data: { added: insertedEmails, sent: sentEmails, failed: failedEmails },
    });
  } catch (err) {
    // ROLLBACK TRANSACTION on error
    try {
      await client.query("ROLLBACK");
      console.log("🔄 Transaction rolled back due to error");
    } catch (rollbackErr) {
      console.error("Error during rollback:", rollbackErr);
    }
    
    console.error("Error in addExternalVendorsToRFQ:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    // Always release the client back to the pool
    client.release();
  }
};

// ========================
// GET ALL RFQS
// ========================
const getAllRFQs = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const {
      search = "",
      page = 1,
      limit = 10,
      sortBy = "created_at",
      sortOrder = "DESC",
    } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM rfqs r
       WHERE r.is_deleted = false AND r.org_id = $1
         AND (r.rfq_number ILIKE $2 OR COALESCE(r.pr_number, 'Manual RFQ') ILIKE $2)`,
      [orgId, searchTerm],
    );

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

    const result = await pool.query(
      `SELECT 
         r.id, r.rfq_number, r.rfq_date, COALESCE(r.pr_number, 'Manual RFQ') as pr_number,
         r.pr_id, r.deadline, r.status, r.sent_at, r.created_at, r.updated_at,
         COALESCE(
           (SELECT json_agg(json_build_object(
             'id', ri.id, 'item_code', ri.item_code, 'item_name', ri.item_name,
             'description', ri.description, 'quantity', ri.quantity, 'uom', ri.uom
           ) ORDER BY ri.id)
           FROM rfq_items ri WHERE ri.rfq_id = r.id
         ), '[]'::json) as items,
         COALESCE((SELECT COUNT(*) FROM rfq_uploaded_quotations WHERE rfq_id = r.id), 0) as uploaded_count,
         COALESCE((SELECT COUNT(*) FROM rfq_quotations WHERE rfq_id = r.id), 0) as assigned_count
       FROM rfqs r
       WHERE r.is_deleted = false AND r.org_id = $1
         AND (r.rfq_number ILIKE $2 OR COALESCE(r.pr_number, 'Manual RFQ') ILIKE $2)
       ORDER BY r.${sortBy} ${sortOrder}
       LIMIT $3 OFFSET $4`,
      [orgId, searchTerm, parseInt(limit), offset],
    );

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit),
      },
    });
  } catch (err) {
    console.error("Error in getAllRFQs:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// CREATE RFQ FROM PR
// ========================
const createRFQFromPR = async (req, res) => {
  const client = await pool.connect();
  try {
    const { pr_id } = req.params;
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId || req.user?.email || null;

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

    const requisitionResult = await client.query(
      `SELECT r.id, r.pr_number, r.pr_date, r.department, r.requested_by, r.status,
         COALESCE((SELECT json_agg(json_build_object(
           'id', ri.id, 'item_code', ri.item_code, 'item_name', ri.item_name,
           'description', ri.description, 'quantity', ri.quantity, 'uom', ri.uom
         ) ORDER BY ri.id) FROM requisition_items ri WHERE ri.requisition_id = r.id), '[]'::json) as items
       FROM requisitions r
       WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false AND r.status = 'Approved'`,
      [pr_id, orgId],
    );

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

    const requisition = requisitionResult.rows[0];

    const rfq_number = await generateRFQNumber();
    const rfq_date = new Date().toISOString().split("T")[0];

    await client.query("BEGIN");

    const rfqResult = await client.query(
      `INSERT INTO rfqs (rfq_number, rfq_date, pr_number, pr_id, status, org_id, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
      [
        rfq_number,
        rfq_date,
        requisition.pr_number,
        requisition.id,
        "Draft",
        orgId,
        created_by,
        created_by,
      ],
    );

    const rfq = rfqResult.rows[0];

    for (const item of requisition.items) {
      await client.query(
        `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
         VALUES ($1, $2, $3, $4, $5, $6)`,
        [
          rfq.id,
          item.item_code,
          item.item_name,
          item.description || null,
          item.quantity || 0,
          item.uom || null,
        ],
      );
    }

    await client.query("COMMIT");

    const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);

    res.status(201).json({
      success: true,
      message: "RFQ created successfully",
      data: completeRFQ,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createRFQFromPR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// GET RFQ BY ID
// ========================
const getRFQById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }
    const rfq = await getRFQByIdInternal(id, orgId);
    if (!rfq) {
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }
    res.json({ success: true, data: rfq });
  } catch (err) {
    console.error("Error in getRFQById:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// GET RFQ ITEMS
// ========================
const getRFQItems = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }
    const result = await pool.query(
      `SELECT ri.* FROM rfq_items ri
       JOIN rfqs r ON r.id = ri.rfq_id
       WHERE ri.rfq_id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
      [parseInt(id), orgId],
    );
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getRFQItems:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// CREATE MANUAL RFQ
// ========================
const createManualRFQ = async (req, res) => {
  const client = await pool.connect();
  try {
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId || req.user?.email || null;
    const { items } = req.body;

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

    if (!items || !Array.isArray(items) || items.length === 0) {
      return res
        .status(400)
        .json({ success: false, message: "At least one item is required" });
    }

    const rfq_number = await generateRFQNumber();
    const rfqDate = new Date().toLocaleDateString("en-CA");

    await client.query("BEGIN");

    const rfqResult = await client.query(
      `INSERT INTO rfqs (rfq_number, rfq_date, status, org_id, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, rfq_number, rfq_date, status`,
      [rfq_number, rfqDate, "Draft", String(orgId), created_by, created_by],
    );

    const rfq = rfqResult.rows[0];

    const insertedItems = [];
    for (const item of items) {
      const itemResult = await client.query(
        `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
         VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, item_code, item_name, description, quantity, uom`,
        [
          rfq.id,
          item.item_code,
          item.item_name,
          item.description || null,
          Number(item.quantity) || 0,
          item.uom,
        ],
      );
      insertedItems.push(itemResult.rows[0]);
    }

    await client.query("COMMIT");

    res.status(201).json({
      success: true,
      message: `RFQ ${rfq.rfq_number} created successfully`,
      data: {
        id: rfq.id,
        rfq_number: rfq.rfq_number,
        rfq_date: rfq.rfq_date,
        status: "Draft",
        items: insertedItems,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createManualRFQ:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// DELETE RFQ
// ========================
const deleteRFQ = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;

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

    const result = await pool.query(
      `UPDATE rfqs SET is_deleted = true, deleted_at = NOW(), updated_by = $1
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, rfq_number`,
      [updated_by, parseInt(id), orgId],
    );

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

    res.json({
      success: true,
      message: "RFQ deleted successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("Error in deleteRFQ:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// GET COMPARISON RFQ LIST
// ========================
const getComparisonRFQList = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const query = `
      SELECT DISTINCT 
        r.id, r.rfq_number, r.pr_number, r.rfq_date, r.created_at, r.status,
        COALESCE((SELECT COUNT(DISTINCT vendor_id) FROM rfq_uploaded_quotations WHERE rfq_id = r.id), 0) as internal_quotations_count,
        COALESCE((SELECT COUNT(*) FROM rfq_external_vendors WHERE rfq_id = r.id AND quotation_uploaded = true), 0) as external_quotations_count,
        (COALESCE((SELECT COUNT(DISTINCT vendor_id) FROM rfq_uploaded_quotations WHERE rfq_id = r.id), 0) + 
         COALESCE((SELECT COUNT(*) FROM rfq_external_vendors WHERE rfq_id = r.id AND quotation_uploaded = true), 0)) as total_quotations
      FROM rfqs r
      WHERE r.org_id = $1 AND r.is_deleted = false
        AND (EXISTS (SELECT 1 FROM rfq_uploaded_quotations uq WHERE uq.rfq_id = r.id)
             OR EXISTS (SELECT 1 FROM rfq_external_vendors ev WHERE ev.rfq_id = r.id AND ev.quotation_uploaded = true))
      ORDER BY r.rfq_number DESC
    `;
    const result = await pool.query(query, [orgId]);
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getComparisonRFQList:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// GET EXTERNAL VENDORS FOR RFQ
// ========================
const getExternalVendorsForRFQ = async (req, res) => {
  try {
    const { id: rfqId } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }
    const result = await pool.query(
      `SELECT ev.id, ev.email, ev.vendor_name, ev.quotation_uploaded, ev.uploaded_file_path, ev.uploaded_at, ev.created_at, ev.selected_item_ids, ev.selected_item_quantities
       FROM rfq_external_vendors ev
       JOIN rfqs r ON r.id = ev.rfq_id
       WHERE ev.rfq_id = $1 AND r.org_id = $2 ORDER BY ev.created_at DESC`,
      [rfqId, orgId],
    );
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getExternalVendorsForRFQ:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// UPLOAD EXTERNAL QUOTATION
// ========================
const uploadExternalQuotation = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id: rfqId } = req.params;
    const { externalVendorId, notes, totalAmount, items, vendorTerms } =
      req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.email;

    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!externalVendorId)
      return res
        .status(400)
        .json({ success: false, message: "External vendor ID required" });
    if (!req.file)
      return res
        .status(400)
        .json({ success: false, message: "Quotation file required" });

    const rfqCheck = await client.query(
      `SELECT id, status FROM rfqs WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [rfqId, orgId],
    );
    if (rfqCheck.rows.length === 0)
      return res.status(404).json({ success: false, message: "RFQ not found" });

    const externalVendorCheck = await client.query(
      `SELECT id, email, vendor_name, selected_item_ids FROM rfq_external_vendors 
       WHERE id = $1 AND rfq_id = $2 AND quotation_uploaded = false`,
      [externalVendorId, rfqId],
    );
    if (externalVendorCheck.rows.length === 0) {
      return res.status(400).json({
        success: false,
        message: "External vendor not found or already uploaded",
      });
    }
    const externalVendor = externalVendorCheck.rows[0];
    const allowedItemIds = externalVendor.selected_item_ids || [];

    let itemsArray = [];
    try {
      itemsArray = JSON.parse(items || "[]");
    } catch (e) {
      itemsArray = [];
    }

    for (const it of itemsArray) {
      if (allowedItemIds.length > 0 && !allowedItemIds.includes(it.rfqItemId)) {
        return res.status(400).json({
          success: false,
          message: `Item ${it.rfqItemId} is not assigned to this vendor`,
        });
      }
    }

    const filePath = `/uploads/quotations/${req.file.filename}`;
    const fileName = req.file.originalname;
    const fileSize = req.file.size;
    const mimeType = req.file.mimetype;

    await client.query("BEGIN");

    await client.query(
      `UPDATE rfq_external_vendors 
       SET quotation_uploaded = true, uploaded_file_path = $1, uploaded_file_name = $2,
           uploaded_file_size = $3, uploaded_mime_type = $4, notes = $5, total_amount = $6,
           vendor_terms = $7, updated_at = NOW(), updated_by = $8
       WHERE id = $9`,
      [
        filePath,
        fileName,
        fileSize,
        mimeType,
        notes,
        totalAmount || 0,
        vendorTerms || null,
        userId,
        externalVendorId,
      ],
    );

    for (const it of itemsArray) {
      await client.query(
        `INSERT INTO rfq_external_quotation_items (external_vendor_id, rfq_item_id, quantity, unit_price)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (external_vendor_id, rfq_item_id) 
         DO UPDATE SET quantity = EXCLUDED.quantity, unit_price = EXCLUDED.unit_price`,
        [externalVendorId, it.rfqItemId, it.quantity, it.unitPrice],
      );
    }

    await client.query("COMMIT");
    res.status(201).json({
      success: true,
      message: "External quotation uploaded successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in uploadExternalQuotation:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// GET EXTERNAL UPLOADED QUOTATIONS
// ========================
const getExternalUploadedQuotations = async (req, res) => {
  try {
    const { id: rfqId } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }
    const result = await pool.query(
      `SELECT ev.id, ev.email, ev.vendor_name, ev.uploaded_file_path, ev.uploaded_file_name,
              ev.uploaded_at, ev.total_amount, ev.notes, ev.selected_item_ids,
         COALESCE((SELECT json_agg(json_build_object(
           'rfq_item_id', eqi.rfq_item_id, 'quantity', eqi.quantity, 'unit_price', eqi.unit_price,
           'item_code', ri.item_code, 'item_name', ri.item_name, 'quantity_requested', ri.quantity, 'uom', ri.uom
         )) FROM rfq_external_quotation_items eqi
           JOIN rfq_items ri ON ri.id = eqi.rfq_item_id
           WHERE eqi.external_vendor_id = ev.id), '[]'::json) as items
       FROM rfq_external_vendors ev
       JOIN rfqs r ON r.id = ev.rfq_id
       WHERE ev.rfq_id = $1 AND r.org_id = $2 AND ev.quotation_uploaded = true
       ORDER BY ev.uploaded_at DESC`,
      [rfqId, orgId],
    );
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getExternalUploadedQuotations:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// GET QUOTATION UPLOAD
// ========================
const getQuotationUpload = async (req, res) => {
  try {
    const { id: rfqId } = req.params;
    const { vendorId } = req.query;
    const orgId = req.user?.orgId;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!vendorId)
      return res
        .status(400)
        .json({ success: false, message: "Vendor ID required" });
    const result = await pool.query(
      `SELECT * FROM rfq_uploaded_quotations 
       WHERE rfq_id = $1 AND vendor_id = $2
       AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $3)`,
      [rfqId, vendorId, orgId],
    );
    res.json({ success: true, data: result.rows[0] || null });
  } catch (err) {
    console.error("Error in getQuotationUpload:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// DELETE QUOTATION UPLOAD
// ========================
const deleteQuotationUpload = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id: rfqId } = req.params;
    const { vendorId } = req.body;
    const orgId = req.user?.orgId;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!vendorId)
      return res
        .status(400)
        .json({ success: false, message: "Vendor ID required" });
    const fileRes = await client.query(
      `SELECT file_path FROM rfq_uploaded_quotations 
       WHERE rfq_id = $1 AND vendor_id = $2
       AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $3)`,
      [rfqId, vendorId, orgId],
    );
    if (fileRes.rows.length > 0) {
      const filePath = path.join(
        __dirname,
        "../../",
        fileRes.rows[0].file_path,
      );
      if (fs.existsSync(filePath)) fs.unlinkSync(filePath);
    }
    await client.query(
      `DELETE FROM rfq_uploaded_quotations WHERE rfq_id = $1 AND vendor_id = $2`,
      [rfqId, vendorId],
    );
    res.json({ success: true, message: "Quotation file deleted" });
  } catch (err) {
    console.error("Error in deleteQuotationUpload:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// DELETE QUOTATION PRICES
// ========================
const deleteQuotationPrices = async (req, res) => {
  try {
    const { id: rfq_id } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    const result = await pool.query(
      `DELETE FROM rfq_quotations
       WHERE rfq_id = $1 AND EXISTS (SELECT 1 FROM rfqs WHERE id = $1 AND org_id = $2)`,
      [rfq_id, orgId],
    );
    res.json({
      success: true,
      message: "Quotation prices deleted",
      deletedCount: result.rowCount,
    });
  } catch (err) {
    console.error("Error in deleteQuotationPrices:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// UPDATE RFQ STATUS
// ========================
const updateRFQStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    const validStatuses = [
      "Draft",
      "Sent",
      "Quotations Received",
      "Approved",
      "Rejected",
    ];
    if (!validStatuses.includes(status)) {
      return res
        .status(400)
        .json({ success: false, message: "Invalid status" });
    }
    const result = await pool.query(
      `UPDATE rfqs SET status = $1, updated_by = $2, updated_at = NOW()
       WHERE id = $3 AND org_id = $4 AND is_deleted = false RETURNING *`,
      [status, updated_by, parseInt(id), orgId],
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }
    res.json({
      success: true,
      message: "Status updated",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("Error in updateRFQStatus:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// BULK CREATE RFQS
// ========================
const bulkCreateRFQs = async (req, res) => {
  const client = await pool.connect();
  try {
    const { requisitions } = req.body;
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId || req.user?.email;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (
      !requisitions ||
      !Array.isArray(requisitions) ||
      requisitions.length === 0
    ) {
      return res
        .status(400)
        .json({ success: false, message: "Requisitions array required" });
    }
    await client.query("BEGIN");
    const createdRFQs = [];
    for (const reqData of requisitions) {
      const { requisitionId, pr_number, items } = reqData;
      const existing = await client.query(
        `SELECT id FROM rfqs WHERE pr_id = $1 AND org_id = $2 AND is_deleted = false`,
        [requisitionId, orgId],
      );
      if (existing.rows.length > 0) continue;
      const rfq_number = await generateRFQNumber();
      const rfq_date = new Date().toISOString().split("T")[0];
      const rfqRes = await client.query(
        `INSERT INTO rfqs (rfq_number, rfq_date, pr_number, pr_id, status, org_id, created_by, updated_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
        [
          rfq_number,
          rfq_date,
          pr_number,
          requisitionId,
          "Draft",
          orgId,
          created_by,
          created_by,
        ],
      );
      const rfq = rfqRes.rows[0];
      for (const item of items) {
        await client.query(
          `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
           VALUES ($1, $2, $3, $4, $5, $6)`,
          [
            rfq.id,
            item.item_code,
            item.item_name,
            item.description,
            item.quantity,
            item.uom,
          ],
        );
      }
      createdRFQs.push(rfq);
    }
    await client.query("COMMIT");
    res.status(201).json({
      success: true,
      message: `${createdRFQs.length} RFQs created`,
      data: createdRFQs,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in bulkCreateRFQs:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// GET RFQ STATISTICS
// ========================
const getRFQStatistics = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    const result = await pool.query(
      `SELECT COUNT(*) AS total_rfqs,
         COUNT(CASE WHEN status = 'Draft' THEN 1 END) AS draft_count,
         COUNT(CASE WHEN status = 'Sent' THEN 1 END) AS sent_count,
         COUNT(CASE WHEN status = 'Quotations Received' THEN 1 END) AS quotations_received_count,
         COUNT(CASE WHEN status = 'Approved' THEN 1 END) AS approved_count,
         COUNT(CASE WHEN status = 'Rejected' THEN 1 END) AS rejected_count
       FROM rfqs WHERE org_id = $1 AND is_deleted = false`,
      [orgId],
    );
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("Error in getRFQStatistics:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// SEND RFQ TO VENDORS (Legacy)
// ========================
const sendRFQToVendors = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    const result = await pool.query(
      `UPDATE rfqs SET status = 'Sent', sent_at = NOW(), updated_at = NOW()
       WHERE id = $1 AND org_id = $2 AND is_deleted = false RETURNING *`,
      [parseInt(id), orgId],
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }
    res.json({
      success: true,
      message: "RFQ sent to vendors successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("Error in sendRFQToVendors:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// UPDATE RFQ DEADLINE
// ========================
const updateRFQDeadline = async (req, res) => {
  try {
    const { id } = req.params;
    const { deadline } = req.body;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    const result = await pool.query(
      `UPDATE rfqs SET deadline = $1, updated_by = $2, updated_at = NOW()
       WHERE id = $3 AND org_id = $4 AND is_deleted = false RETURNING *`,
      [deadline, updated_by, parseInt(id), orgId],
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }
    res.json({
      success: true,
      message: "Deadline updated",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("Error in updateRFQDeadline:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ========================
// HARD DELETE RFQ
// ========================
const hardDeleteRFQ = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    await client.query("BEGIN");
    await client.query("DELETE FROM rfq_items WHERE rfq_id = $1", [
      parseInt(id),
    ]);
    const result = await client.query(
      `DELETE FROM rfqs WHERE id = $1 AND org_id = $2 RETURNING id, rfq_number`,
      [parseInt(id), orgId],
    );
    if (result.rowCount === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "RFQ not found" });
    }
    await client.query("COMMIT");
    res.json({
      success: true,
      message: "RFQ permanently deleted",
      data: result.rows[0],
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in hardDeleteRFQ:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// CREATE RFQ FROM REQUISITION (BODY)
// ========================
const createRFQFromRequisition = async (req, res) => {
  const client = await pool.connect();
  try {
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId || req.user?.email;
    const { requisitionId, pr_number, items } = req.body;
    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!requisitionId || !pr_number) {
      return res.status(400).json({
        success: false,
        message: "Requisition ID and PR number are required",
      });
    }
    const existingRFQ = await pool.query(
      `SELECT id, rfq_number FROM rfqs WHERE pr_id = $1 AND org_id = $2 AND is_deleted = false`,
      [requisitionId, orgId],
    );
    if (existingRFQ.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: `RFQ already exists: ${existingRFQ.rows[0].rfq_number}`,
      });
    }
    const rfq_number = await generateRFQNumber();
    const rfq_date = new Date().toISOString().split("T")[0];
    await client.query("BEGIN");
    const rfqResult = await client.query(
      `INSERT INTO rfqs (rfq_number, rfq_date, pr_number, pr_id, status, org_id, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
      [
        rfq_number,
        rfq_date,
        pr_number,
        requisitionId,
        "Draft",
        orgId,
        created_by,
        created_by,
      ],
    );
    const rfq = rfqResult.rows[0];
    if (items && items.length > 0) {
      for (const item of items) {
        await client.query(
          `INSERT INTO rfq_items (rfq_id, item_code, item_name, description, quantity, uom)
           VALUES ($1, $2, $3, $4, $5, $6)`,
          [
            rfq.id,
            item.item_code,
            item.item_name,
            item.description || null,
            item.quantity || 0,
            item.uom || null,
          ],
        );
      }
    }
    await client.query("COMMIT");
    const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);
    res.status(201).json({
      success: true,
      message: "RFQ created successfully",
      data: completeRFQ,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createRFQFromRequisition:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ========================
// EXPORTS
// ========================
module.exports = {
  getAllRFQs,
  createRFQFromPR,
  createRFQFromRequisition,
  sendRFQToVendors,
  getRFQById,
  updateRFQStatus,
  updateRFQDeadline,
  getRFQItems,
  deleteRFQ,
  hardDeleteRFQ,
  getRFQStatistics,
  bulkCreateRFQs,
  saveQuotationPrices,
  getQuotationPrices,
  deleteQuotationPrices,
  uploadQuotationFileMiddleware,
  uploadQuotation,
  createRFQFromPRInventory,
  getQuotationUpload,
  deleteQuotationUpload,
  getUploadedVendors,
  getComparisonRFQList,
  getQuotationComparison,
  createManualRFQ,
  addExternalVendorsToRFQ,
  getExternalVendorsForRFQ,
  uploadExternalQuotation,
  getExternalUploadedQuotations,
  getVendorRFQItems,
};