

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: 465,
  secure: true,
  auth: {
    user: process.env.MAIL_USER,
    pass: process.env.MAIL_PASS,
  },
});

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 } = req.body;

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

    // Fetch RFQ details
    const rfqCheck = await client.query(
      `SELECT id, rfq_number, status, rfq_date, deadline 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
    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));
      
      console.log(`📝 Vendor ${vendor.id} selected items:`, selectedItemIdsArray);
      
      // Delete existing
      await client.query(
        `DELETE FROM rfq_quotations WHERE rfq_id = $1 AND vendor_id = $2`,
        [rfq_id, vendor.id]
      );
      
      // ✅ CORRECT FIX: Use pg's built-in array support
      // Just pass the JavaScript array directly - pg will handle the conversion
      if (selectedItemIdsArray.length === 0) {
        await client.query(
          `INSERT INTO rfq_quotations(rfq_id, vendor_id, selected_item_ids, created_by, updated_by)
           VALUES ($1, $2, $3, $4, $5)`,
          [rfq_id, vendor.id, [], userId, userId]
        );
      } else {
        await client.query(
          `INSERT INTO rfq_quotations(rfq_id, vendor_id, selected_item_ids, created_by, updated_by)
           VALUES ($1, $2, $3, $4, $5)`,
          [rfq_id, vendor.id, selectedItemIdsArray, userId, userId]
        );
      }
      
      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 });
  }
};


// =====================================================
// CREATE RFQ FROM PR INVENTORY (When sending RFQ from Purchase Orders page)
// POST /api/procurement/rfqs/create-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;

    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
    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,
          Number(item.quantity) || 0,
          item.uom || null
        ]
      );
    }

    // If vendors are provided, assign them to the RFQ
    if (vendorIds && vendorIds.length > 0) {
      for (const vendorId of vendorIds) {
        // Get all RFQ items for this RFQ
        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, created_by, updated_by)
           VALUES ($1, $2, $3, $4, $5)
           ON CONFLICT (rfq_id, vendor_id) DO NOTHING`,
          [rfq.id, vendorId, JSON.stringify(selectedItemIds), userId, userId]
        );
      }
    }

    await client.query("COMMIT");

    // Get complete RFQ with items
    const completeRFQ = await getRFQByIdInternal(rfq.id, orgId);

    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)
// ========================
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 RFQ items
    const itemsResult = await pool.query(
      `SELECT id, item_code, item_name, description, quantity, uom
       FROM rfq_items WHERE rfq_id = $1 ORDER BY id`,
      [parsedId]
    );
    const items = itemsResult.rows;

    if (items.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 = {};

    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
        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]
        );
        
        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);
        }
        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);
        }
      }
    } 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
      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);
      }
      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);
      }
    }
    
    const vendorPrices = {};
    for (const vendor of vendors) {
      vendorPrices[vendor.id] = items.map(item => priceMatrix[vendor.id]?.[item.id] || null);
    }

    res.json({ 
      success: true, 
      data: { 
        items, 
        vendors,
        vendorPrices, 
        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,
};