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

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

// ==================== HELPER: Send PO Email to Vendor ====================
const sendPOEmail = async (po, vendor, companyData, pdfBuffer, items) => {
  try {
    const poNumber = po.po_number;
    const poDate = po.po_date
      ? new Date(po.po_date).toLocaleDateString("en-GB")
      : "-";

    let grandTotal = 0;
    const itemsTableHtml = items
      .map((item, index) => {
        const unitPrice = Number(item.unit_price) || 0;
        const quantity = Number(item.quantity) || 0;
        const total = unitPrice * quantity;
        grandTotal += total;

        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;">${quantity}</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;">₹ ${unitPrice.toFixed(2)}</td>
          <td style="padding: 8px; border: 1px solid #ddd; text-align: right;">₹ ${total.toFixed(2)}</td>
        </tr>
      `;
      })
      .join("");

    const totalAmount = grandTotal.toLocaleString("en-IN", {
      minimumFractionDigits: 2,
    });

    const mailOptions = {
      from: `"Procurement Team" <${process.env.MAIL_USER}>`,
      to: vendor.email,
      subject: `Purchase Order ${poNumber} - ${companyData.name}`,
      html: `
        <div style="font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto;">
          <h2 style="color: #333; text-align: center;">PURCHASE ORDER</h2>
          <p style="text-align: center; font-size: 14px; color: #666;">${companyData.name}</p>
          
          <p>Dear <strong>${vendor.vendor_name}</strong>,</p>
          
          <p>Please find attached the Purchase Order <strong>${poNumber}</strong> for your reference and necessary action.</p>
          
          <table style="width: 100%; border-collapse: collapse; margin: 15px 0;">
            <tr>
              <td style="padding: 5px;"><strong>PO Number:</strong></td>
              <td style="padding: 5px;">${poNumber}</td>
              <td style="padding: 5px;"><strong>PO Date:</strong></td>
              <td style="padding: 5px;">${poDate}</td>
            </tr>
            <tr>
              <td style="padding: 5px;"><strong>Vendor Name:</strong></td>
              <td style="padding: 5px;">${vendor.vendor_name}</td>
              <td style="padding: 5px;"><strong>Vendor GST:</strong></td>
              <td style="padding: 5px;">${vendor.gstin || "N/A"}</td>
            </tr>
            <tr>
              <td style="padding: 5px;"><strong>Total Amount:</strong></td>
              <td style="padding: 5px;"><strong>₹ ${totalAmount}</strong></td>
              <td></td>
              <td></td>
            </tr>
          </table>
          
          <hr style="margin: 20px 0;" />
          
          <h3 style="color: #333;">Items Details:</h3>
          
          <table style="width: 100%; border-collapse: collapse; margin: 10px 0;">
            <thead>
              <tr style="background-color: #f0f0f0;">
                <th style="padding: 10px; border: 1px solid #ddd; text-align: center;">#</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: left;">Item Code</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: left;">Item Name</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: left;">Description</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: center;">QTY</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: center;">UOM</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: right;">Unit Rate (₹)</th>
                <th style="padding: 10px; border: 1px solid #ddd; text-align: right;">Total (₹)</th>
              </tr>
            </thead>
            <tbody>
              ${itemsTableHtml}
            </tbody>
            <tfoot>
              <tr style="background-color: #f9f9f9;">
                <td colspan="7" style="padding: 10px; border: 1px solid #ddd; text-align: right; font-weight: bold;">Grand Total:</td>
                <td style="padding: 10px; border: 1px solid #ddd; text-align: right; font-weight: bold;">₹ ${totalAmount}</td>
              </tr>
            </tfoot>
          </table>
          
          <div style="margin-top: 30px; text-align: right;">
            <p>For ${companyData.name}</p>
            <p style="margin-top: 40px;"><strong>Authorized Signatory</strong></p>
          </div>
          
          <hr style="margin: 20px 0;" />
          
          <div style="font-size: 12px; color: #666; text-align: center;">
            <p>This is a system generated purchase order. Please verify details before processing.</p>
            <p>${companyData.address}</p>
            <p>GSTIN: ${companyData.gst} | CIN: ${companyData.cin}</p>
          </div>
        </div>
      `,
      attachments: [
        {
          filename: `PO_${poNumber}.pdf`,
          content: pdfBuffer,
          contentType: "application/pdf",
        },
      ],
    };

    const info = await transporter.sendMail(mailOptions);
    console.log(`✅ PO email sent to ${vendor.email}: ${info.messageId}`);
    return { success: true, messageId: info.messageId };
  } catch (error) {
    console.error(
      `❌ Failed to send PO email to ${vendor.email}:`,
      error.message,
    );
    return { success: false, error: error.message };
  }
};

const generatePOPDFBuffer = async (po, items, company, vendor, terms = [], isPartialOrder = false, shippingAddress = "") => {
  try {
    const templatePath = path.join(__dirname, "../../templates/po-pdf-template.html");
    if (!fs.existsSync(templatePath)) {
      throw new Error(`Template file not found at ${templatePath}`);
    }
    let html = fs.readFileSync(templatePath, "utf8");

    // Get logo - improved path resolution (SAME AS downloadPOPDF)
    let logoSrc = "";
    try {
      const logoEnv = process.env.COMPANY_LOGO || "";
      const fileName = logoEnv.split("/").pop();
      const ext = fileName.split(".").pop().toLowerCase();
      const mimeMap = {
        png: "image/png",
        jpg: "image/jpeg",
        jpeg: "image/jpeg",
        gif: "image/gif",
        svg: "image/svg+xml",
        webp: "image/webp",
      };
      const mime = mimeMap[ext] || "image/png";
      const logoPath = path.join(__dirname, "../../public", fileName);
      if (fileName && fs.existsSync(logoPath)) {
        const b64 = fs.readFileSync(logoPath).toString("base64");
        logoSrc = `data:${mime};base64,${b64}`;
      } else if (logoEnv) {
        logoSrc = logoEnv;
      }
    } catch (err) {
      console.log("Logo load failed:", err.message);
    }

    // DYNAMIC COMPANY DATA (SAME AS downloadPOPDF)
    const companyName = company?.name || company?.org_name || "VIVEK BIO-PRODUCTS PRIVATE LIMITED";
    let companyAddress = company?.address || company?.org_address || company?.company_address || "";
    
    // Build address similar to downloadPOPDF
    if (!companyAddress && company?.address_line1) {
      companyAddress = [
        company.address_line1,
        company.address_line2,
        company.city,
        company.state,
        company.pin_code,
      ].filter(Boolean).join(", ");
    }
    
    const companyGST = company?.gst || company?.gst_number || company?.gstin || "36AAICV8996R1ZU";
    const companyCIN = company?.cin || company?.cin_number || company?.company_cin || "U24100TG2022PTC165236";
    const companyPAN = company?.pan || company?.pan_number || company?.tax_pan || "AAICV8996R";
    const companyEmail = company?.email || company?.company_email || company?.email_id || "sales@vivekbio.com";
    const companyPhone = company?.phone || company?.phone_number || company?.contact_number || "8019637711";

    // Split address for line1/line2
    const addrParts = companyAddress.split(",");
    const addrLine1 = addrParts.slice(0, 3).join(",").trim();
    const addrLine2 = addrParts.slice(3).join(",").trim();

    // DYNAMIC VENDOR DATA
    let vendorAddress = "";
    if (vendor) {
      const vendorAddrParts = [vendor.address, vendor.city, vendor.state, vendor.pincode].filter(Boolean);
      vendorAddress = vendorAddrParts.join(", ") || "N/A";
    }

    const vendorName = vendor?.vendor_name || vendor?.name || "N/A";
    const vendorPhone = vendor?.phone || vendor?.contact || "N/A";
    const vendorEmail = vendor?.email || "N/A";
    const vendorGST = vendor?.gstin || vendor?.gst || "N/A";

    // Calculate totals and build items rows - FIXED TAX CALCULATION (SAME AS downloadPOPDF)
    let totalGross = 0;
    let totalCGST = 0;
    let totalSGST = 0;
    let totalNet = 0;
    let itemsRows = "";

    for (let i = 0; i < items.length; i++) {
      const item = items[i];
      const qty = Number(item.quantity) || 0;
      const rate = Number(item.unit_price) || 0;
      const taxPct = Number(item.tax_percentage) || 0;
      const baseAmount = qty * rate;
      
      // Calculate tax amounts properly (SAME AS downloadPOPDF)
      const cgstPct = taxPct / 2;
      const sgstPct = taxPct / 2;
      const cgstAmt = (baseAmount * cgstPct) / 100;
      const sgstAmt = (baseAmount * sgstPct) / 100;
      const lineTotal = baseAmount + cgstAmt + sgstAmt;

      totalGross += baseAmount;
      totalCGST += cgstAmt;
      totalSGST += sgstAmt;
      totalNet += lineTotal;

      // Format numbers for display
      const taxDisplay = taxPct > 0 ? taxPct.toFixed(2) : "0.00";
      
      itemsRows += `
        <tr>
          <td class="tc">${i + 1}</td>
          <td class="text-left"><span class="code-val">${escapeHtml(item.item_code || "-")}</span></td>
          <td class="text-left">${escapeHtml(item.item_name || "-")}</td>
          <td class="text-left">${escapeHtml(item.description || "-")}</td>
          <td class="tc">${qty.toFixed(2)}</td>
          <td class="tc">${escapeHtml(item.uom || "-")}</td>
          <td class="tr">${rate.toFixed(2)}</td>
          <td class="tr">${taxDisplay}</td>
          <td class="tr">${cgstAmt.toFixed(2)}</td>
          <td class="tr">${sgstAmt.toFixed(2)}</td>
          <td class="tr">${lineTotal.toFixed(2)}</td>
        </table>
      `;
    }

    // Calculate round off (SAME AS downloadPOPDF)
    const roundOff = Math.round(totalNet) - totalNet;
    const grandTotal = totalNet + roundOff;

    // Format numbers function (SAME AS downloadPOPDF)
    const formatNum = (n) => n.toLocaleString("en-IN", {
      minimumFractionDigits: 2,
      maximumFractionDigits: 2,
    });

    // DYNAMIC TERMS (SAME AS downloadPOPDF)
    let termsHtml = "";
    if (terms && Array.isArray(terms) && terms.length > 0) {
      terms.forEach((term, index) => {
        const termText = term.term || term.title || "";
        const termDesc = term.description || term.content || "";
        if (termText) {
          termsHtml += `${index + 1}. ${escapeHtml(termText)}${termDesc ? " : " + escapeHtml(termDesc) : ""}<br/>`;
        } else if (termDesc) {
          termsHtml += `${escapeHtml(termDesc)}<br/>`;
        }
      });
    }
    // Default terms if none provided (SAME AS downloadPOPDF)
    if (!termsHtml) {
      termsHtml = `1. Goods once sold will not be taken back.<br/>2. Subject to Hyderabad jurisdiction.<br/>3. Payment to be made as per agreed terms.`;
    }

    // Format dates
    const poDate = po.po_date ? new Date(po.po_date).toLocaleDateString("en-GB") : "-";
    const poDueDate = po.po_due_date ? new Date(po.po_due_date).toLocaleDateString("en-GB") : "-";

    // Replace all placeholders (SAME AS downloadPOPDF)
    html = html
      .replace(/{{COMPANY_LOGO}}/g, logoSrc)
      .replace(/{{COMPANY_NAME}}/g, escapeHtml(companyName))
      .replace(/{{COMPANY_ADDRESS_LINE1}}/g, escapeHtml(addrLine1))
      .replace(/{{COMPANY_ADDRESS_LINE2}}/g, escapeHtml(addrLine2))
      .replace(/{{COMPANY_PAN}}/g, escapeHtml(companyPAN))
      .replace(/{{COMPANY_GST}}/g, escapeHtml(companyGST))
      .replace(/{{COMPANY_CIN}}/g, escapeHtml(companyCIN))
      .replace(/{{COMPANY_EMAIL}}/g, escapeHtml(companyEmail))
      .replace(/{{COMPANY_PHONE}}/g, escapeHtml(companyPhone))
      .replace(/{{VENDOR_NAME}}/g, escapeHtml(vendorName))
      .replace(/{{VENDOR_ADDRESS}}/g, escapeHtml(vendorAddress))
      .replace(/{{VENDOR_PHONE}}/g, escapeHtml(vendorPhone))
      .replace(/{{VENDOR_EMAIL}}/g, escapeHtml(vendorEmail))
      .replace(/{{VENDOR_GST}}/g, escapeHtml(vendorGST))
      .replace(/{{PO_NUMBER}}/g, escapeHtml(po.po_number || "-"))
      .replace(/{{PO_DATE}}/g, poDate)
      .replace(/{{PO_DUE_DATE}}/g, poDueDate)
      .replace(/{{PR_NUMBER}}/g, escapeHtml(po.pr_number || "-"))
      .replace(/{{DEPARTMENT}}/g, escapeHtml(po.department || "Production"))
      .replace(/{{REQUESTED_BY}}/g, escapeHtml(po.requested_by || "System"))
      .replace(/{{ITEMS_ROWS}}/g, itemsRows)
      .replace(/{{TERMS_CONTENT}}/g, termsHtml)
      .replace(/{{GROSS_AMOUNT}}/g, formatNum(totalGross))
      .replace(/{{NET_VALUE}}/g, formatNum(totalGross))
      .replace(/{{CGST_AMOUNT}}/g, formatNum(totalCGST))
      .replace(/{{SGST_AMOUNT}}/g, formatNum(totalSGST))
      .replace(/{{ROUND_OFF}}/g, roundOff.toFixed(2))
      .replace(/{{TOTAL_VALUE}}/g, formatNum(grandTotal))
      .replace(/{{AMOUNT_IN_WORDS}}/g, escapeHtml(numberToWords(Math.round(grandTotal))));

    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: "15px", bottom: "15px", left: "15px", right: "15px" },
    });
    await browser.close();
    return pdf;
  } catch (error) {
    console.error("Error generating PO PDF:", error);
    throw error;
  }
};

// ── formatNumber helper (add near numberToWords / escapeHtml) ──
function formatNumber(num) {
  return (Number(num) || 0).toLocaleString("en-IN", {
    minimumFractionDigits: 2,
    maximumFractionDigits: 2,
  });
}

// Helper functions for PDF generation
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) {
    console.log("Logo base64 conversion failed:", err.message);
    return null;
  }
}

function resolveLogoPath() {
  const raw = (process.env.COMPANY_LOGO || "").trim();
  
  // If it's a URL, return as is
  if (raw.startsWith('http://') || raw.startsWith('https://')) {
    return raw;
  }
  
  const candidates = [
    raw ? path.isAbsolute(raw) ? raw : path.join(process.cwd(), raw) : null,
    raw ? path.join(process.cwd(), "public", path.basename(raw)) : null,
    path.join(process.cwd(), "public", "logo.png"),
    path.join(process.cwd(), "public", "favicon.png"),
    path.join(__dirname, "../../public", "logo.png"),
    path.join(__dirname, "../../public", "favicon.png"),
  ].filter(Boolean);
  
  for (const p of candidates) {
    if (p && fs.existsSync(p)) {
      console.log("Logo resolved at:", p);
      return p;
    }
  }
  console.log("Logo not found in any candidate path. Raw:", raw);
  return null;
}

function numberToWords(num) {
  const a = [
    "",
    "ONE",
    "TWO",
    "THREE",
    "FOUR",
    "FIVE",
    "SIX",
    "SEVEN",
    "EIGHT",
    "NINE",
    "TEN",
    "ELEVEN",
    "TWELVE",
    "THIRTEEN",
    "FOURTEEN",
    "FIFTEEN",
    "SIXTEEN",
    "SEVENTEEN",
    "EIGHTEEN",
    "NINETEEN",
  ];
  const b = [
    "",
    "",
    "TWENTY",
    "THIRTY",
    "FORTY",
    "FIFTY",
    "SIXTY",
    "SEVENTY",
    "EIGHTY",
    "NINETY",
  ];
  const inWords = (n) => {
    if (n < 20) return a[n];
    if (n < 100) return b[Math.floor(n / 10)] + " " + a[n % 10];
    if (n < 1000)
      return a[Math.floor(n / 100)] + " HUNDRED " + inWords(n % 100);
    if (n < 100000)
      return inWords(Math.floor(n / 1000)) + " THOUSAND " + inWords(n % 1000);
    if (n < 10000000)
      return inWords(Math.floor(n / 100000)) + " LAKH " + inWords(n % 100000);
    return (
      inWords(Math.floor(n / 10000000)) + " CRORE " + inWords(n % 10000000)
    );
  };
  if (num === 0) return "ZERO";
  return inWords(Math.round(num)).trim();
}

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

// Fixed generatePONumber function
const generatePONumber = async () => {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    const result = await client.query(
      `SELECT prefix_code, last_number 
       FROM public.prefixes 
       WHERE module_name = 'po' 
         AND status = 'ACTIVE' 
         AND is_deleted = false
       FOR UPDATE`,
    );

    let prefix = "PO";
    let nextNumber = 1;

    if (result.rows.length > 0) {
      prefix = result.rows[0].prefix_code;
      nextNumber = result.rows[0].last_number + 1;

      await client.query(
        `UPDATE public.prefixes 
         SET last_number = $1, updated_at = NOW()
         WHERE module_name = 'po' AND status = 'ACTIVE' AND is_deleted = false`,
        [nextNumber],
      );
    } else {
      await client.query(
        `INSERT INTO public.prefixes (module_name, prefix_code, last_number, status, created_at) 
         VALUES ('po', 'PO', 1, 'ACTIVE', NOW())`,
      );
    }

    await client.query("COMMIT");

    const formattedNumber = nextNumber.toString().padStart(4, "0");
    return `${prefix}-${formattedNumber}`;
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error generating PO number:", error);

    const timestamp = Date.now().toString().slice(-6);
    return `PO-${timestamp}`;
  } finally {
    client.release();
  }
};

// ==================== GET PO BY ID INTERNAL ====================
const getPOByIdInternal = async (id, orgId) => {
  try {
    const result = await pool.query(
      `SELECT 
         po.*,
         r.department,
         CASE 
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
             CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
           ELSE 
             COALESCE(r.requested_by, 
               CASE WHEN po.created_by IS NOT NULL THEN 
                 (SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) 
                  FROM users WHERE id = po.created_by::int)
               ELSE 'System' END
             )
         END as requested_by,
         COALESCE(
           (SELECT json_agg(json_build_object(
             'id', poi.id,
             'item_code', poi.item_code,
             'item_name', poi.item_name,
             'description', poi.description,
             'quantity', poi.quantity,
             'uom', poi.uom,
             'unit_price', COALESCE(poi.unit_price, 0),
             'total_price', COALESCE(poi.total_price, 0)
           ) ORDER BY poi.id)
           FROM purchase_order_items poi WHERE poi.po_id = po.id
         ), '[]'::json) as items
       FROM purchase_orders po
       LEFT JOIN requisitions r ON po.pr_id = r.id AND r.is_deleted = false
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
       WHERE po.id = $1 AND po.org_id = $2 AND po.is_deleted = false`,
      [parseInt(id), orgId],
    );

    if (!result.rows[0]) {
      return null;
    }

    const poData = result.rows[0];

    if (poData.vendor_id) {
      const vendorResult = await pool.query(
        `SELECT 
           id, vendor_name, vendor_code, contact_person, phone, email, gstin,
           address, city, state, pincode, country, shipping_addresses
         FROM procurement_vendors 
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [parseInt(poData.vendor_id), parseInt(orgId)],
      );

      if (vendorResult.rows.length > 0) {
        const vendor = vendorResult.rows[0];

        let shippingAddresses = vendor.shipping_addresses;
        if (typeof shippingAddresses === "string") {
          try {
            shippingAddresses = JSON.parse(shippingAddresses);
          } catch (e) {
            console.error("Error parsing shipping_addresses:", e);
            shippingAddresses = [];
          }
        } else if (shippingAddresses === null) {
          shippingAddresses = [];
        }

        poData.vendor_details = {
          id: vendor.id,
          vendor_name: vendor.vendor_name,
          vendor_code: vendor.vendor_code,
          contact_person: vendor.contact_person,
          phone: vendor.phone,
          email: vendor.email,
          gstin: vendor.gstin,
          address: vendor.address,
          city: vendor.city,
          state: vendor.state,
          pincode: vendor.pincode,
          country: vendor.country || "India",
          shipping_addresses: shippingAddresses || [],
        };
      } else {
        poData.vendor_details = {
          vendor_name: poData.vendor_name || "N/A",
          shipping_addresses: [],
        };
      }
    } else {
      poData.vendor_details = {
        vendor_name: poData.vendor_name || "N/A",
        shipping_addresses: [],
      };
    }

    return poData;
  } catch (error) {
    console.error("Error in getPOByIdInternal:", error);
    return null;
  }
};

// ==================== HELPER: Update PR Status After PO ====================
const updatePRStatusAfterPO = async (requisitionId, orgId) => {
  try {
    const result = await pool.query(
      `SELECT 
         COUNT(*) as total_items,
         SUM(CASE WHEN COALESCE(quantity_po_raised, 0) >= quantity THEN 1 ELSE 0 END) as fully_raised_count
       FROM requisition_items
       WHERE requisition_id = $1`,
      [requisitionId],
    );

    const totalItems = parseInt(result.rows[0].total_items);
    const fullyRaisedCount = parseInt(result.rows[0].fully_raised_count);

    let poStatus = "Open";
    if (fullyRaisedCount === totalItems && totalItems > 0) {
      poStatus = "Closed";
    } else if (fullyRaisedCount > 0 && fullyRaisedCount < totalItems) {
      poStatus = "Partially Raised";
    }

    await pool.query(
      `UPDATE requisitions 
       SET po_status = $1, updated_at = NOW()
       WHERE id = $2`,
      [poStatus, requisitionId],
    );

    return poStatus;
  } catch (error) {
    console.error("Error updating PR status:", error);
    return "Open";
  }
};

const createPOFromApprovedPR = 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;

    const { vendorIds, po_date, po_due_date, items } = req.body;

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

    const orgIdStr = String(orgId);

    // Check requisition exists
    const checkQuery = await client.query(
      `SELECT id, pr_number, status, is_deleted, org_id 
       FROM requisitions 
       WHERE id = $1`,
      [parseInt(pr_id)],
    );

    if (checkQuery.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: `Requisition with ID ${pr_id} not found`,
      });
    }

    const reqData = checkQuery.rows[0];

    if (reqData.is_deleted) {
      return res.status(400).json({
        success: false,
        message: `Requisition ${reqData.pr_number} is deleted`,
      });
    }

    if (reqData.status !== "Approved" && reqData.status !== "Partially Issued") {
      return res.status(400).json({
        success: false,
        message: `Requisition ${reqData.pr_number} status is '${reqData.status}'. Only 'Approved' or 'Partially Issued' requisitions can create Purchase Orders.`,
      });
    }

    // Get vendor info
    let vendorInfo = null;
    if (vendorIds && vendorIds.length > 0) {
      const vendorResult = 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, orgIdStr],
      );

      if (vendorResult.rows.length > 0) {
        vendorInfo = vendorResult.rows[0];
      }
    }

    const itemsToInsert = items && items.length > 0 ? items : [];

    if (itemsToInsert.length === 0) {
      return res.status(400).json({
        success: false,
        message: "No items selected for Purchase Order",
      });
    }

    // Generate PO number
    const po_number = await generatePONumber();
    const poDate = po_date || new Date().toLocaleDateString("en-CA");
    const poDueDate = po_due_date || null;

    await client.query("BEGIN");

    // Create PO
    const poResult = await client.query(
      `INSERT INTO purchase_orders (
        po_number, po_date, po_due_date, pr_id, pr_number, status, 
        total_amount, currency, notes, org_id, created_by, updated_by,
        vendor_id, vendor_name
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
      RETURNING *`,
      [
        po_number,
        poDate,
        poDueDate,
        parseInt(pr_id),
        reqData.pr_number,
        "Draft",
        0,
        "INR",
        `Auto-generated from PR ${reqData.pr_number}`,
        orgIdStr,
        created_by,
        created_by,
        vendorInfo ? vendorInfo.id : null,
        vendorInfo ? vendorInfo.vendor_name : null,
      ],
    );

    const purchaseOrder = poResult.rows[0];

    let totalAmount = 0;

    // Insert items into purchase_order_items and UPDATE pr_inventory
    for (const item of itemsToInsert) {
      const unitPrice = Number(item.unit_price) || 0;
      const quantity = Number(item.quantity) || 0;  // This is the PO quantity
      const lineTotal = quantity * unitPrice;
      totalAmount += lineTotal;

      // Insert into purchase_order_items
      await client.query(
        `INSERT INTO purchase_order_items (
          po_id, requisition_item_id, item_code, item_name, 
          description, quantity, uom, unit_price, total_price
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [
          purchaseOrder.id,
          item.requisition_item_id || item.id || null,
          item.item_code,
          item.item_name,
          item.description,
          quantity,
          item.uom,
          unitPrice,
          lineTotal,
        ],
      );

      // Update requisition_items - set quantity_po_raised
      if (item.requisition_item_id || item.id) {
        const reqItemId = item.requisition_item_id || item.id;
        await client.query(
          `UPDATE requisition_items 
           SET quantity_po_raised = quantity_po_raised + $1,
               updated_at = NOW()
           WHERE id = $2`,
          [quantity, reqItemId],
        );
      }

      // ========== FIX: Update pr_inventory correctly ==========
      const requisitionItemId = item.requisition_item_id || item.id;

      if (requisitionItemId) {
        // Get the current pr_inventory record for this requisition item
        const inventoryRecord = await client.query(
          `SELECT id, remaining_quantity, issued_quantity, requested_quantity, status
           FROM pr_inventory 
           WHERE requisition_item_id = $1 
             AND requisition_id = $2
           ORDER BY id ASC
           LIMIT 1`,
          [requisitionItemId, parseInt(pr_id)],
        );

        if (inventoryRecord.rows.length > 0) {
          const current = inventoryRecord.rows[0];
          const currentRemaining = parseFloat(current.remaining_quantity) || 0;
          const currentIssued = parseFloat(current.issued_quantity) || 0;
          const currentRequested = parseFloat(current.requested_quantity) || 0;
          
          // Calculate new quantities
          const newRemaining = currentRemaining - quantity;
          const newIssued = currentIssued + quantity;
          
          // Determine new status
          let newStatus = current.status;
          if (newRemaining <= 0) {
            newStatus = 'Fully Issued';
          } else if (current.status === 'PO Requested' || current.status === 'PO Raised') {
            newStatus = 'PO Raised';  // Keep as PO Raised since we're creating PO
          } else {
            newStatus = 'PO Raised';
          }

          // Update pr_inventory
          await client.query(
            `UPDATE pr_inventory 
             SET remaining_quantity = $1,
                 issued_quantity = $2,
                 status = $3,
                 updated_at = NOW(),
                 updated_by = $4
             WHERE id = $5`,
            [newRemaining, newIssued, newStatus, created_by, current.id],
          );

          console.log(`Updated pr_inventory: item=${item.item_name}, PO Qty=${quantity}, Old Remaining=${currentRemaining}, New Remaining=${newRemaining}`);
        } else {
          console.warn(`No pr_inventory record found for requisition_item_id: ${requisitionItemId}`);
        }
      }
    }

    // Update PO total amount
    await client.query(
      `UPDATE purchase_orders SET total_amount = $1 WHERE id = $2`,
      [totalAmount, purchaseOrder.id],
    );

    // Check if there are any remaining items in pr_inventory with remaining_quantity > 0
    const remainingItems = await client.query(
      `SELECT COUNT(*) as count 
       FROM pr_inventory 
       WHERE requisition_id = $1 
         AND remaining_quantity > 0`,
      [parseInt(pr_id)],
    );

    // Update requisition po_status
    let finalPOStatus = "Closed";
    if (parseInt(remainingItems.rows[0].count) > 0) {
      finalPOStatus = "Partially Raised";
    }

    await client.query(
      `UPDATE requisitions 
       SET po_status = $1, updated_at = NOW()
       WHERE id = $2`,
      [finalPOStatus, parseInt(pr_id)],
    );

    await client.query("COMMIT");

    const completePO = await getPOByIdInternal(purchaseOrder.id, orgIdStr);

    res.status(201).json({
      success: true,
      message: "Purchase Order created successfully. It will be sent to vendor once all approvals are completed.",
      data: completePO,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createPOFromApprovedPR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

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

  try {
    const { vendorId, items, po_date, po_due_date } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.id;
    const userEmail = req.user?.email;

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

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

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

    const orgIdStr = String(orgId);
    const vendorIdInt = parseInt(vendorId);

    const vendorCheck = await client.query(
      `SELECT id, vendor_name, email, gstin, address, phone, vendor_code 
       FROM procurement_vendors 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [vendorIdInt, orgIdStr],
    );

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

    const vendor = vendorCheck.rows[0];

    await client.query("BEGIN");

    // Generate PO number
    let poNumber;
    try {
      const prefixResult = await client.query(
        `SELECT prefix_code, last_number 
         FROM public.prefixes 
         WHERE module_name = 'po' 
           AND status = 'ACTIVE' 
           AND is_deleted = false
         FOR UPDATE`,
      );

      let prefix = "PO";
      let nextNumber = 1;

      if (prefixResult.rows.length > 0) {
        prefix = prefixResult.rows[0].prefix_code;
        nextNumber = prefixResult.rows[0].last_number + 1;

        await client.query(
          `UPDATE public.prefixes 
           SET last_number = $1, updated_at = NOW()
           WHERE module_name = 'po' AND status = 'ACTIVE' AND is_deleted = false`,
          [nextNumber],
        );
      } else {
        await client.query(
          `INSERT INTO public.prefixes (module_name, prefix_code, last_number, status) 
           VALUES ('po', 'PO', 1, 'ACTIVE')`,
        );
      }

      poNumber = `${prefix}-${String(nextNumber).padStart(4, "0")}`;
    } catch (prefixError) {
      console.error("Error generating PO number from prefixes:", prefixError);
      const maxResult = await client.query(
        `SELECT po_number FROM purchase_orders 
         WHERE org_id = $1 AND po_number ~ '^PO-[0-9]+$'
         ORDER BY CAST(SUBSTRING(po_number FROM 4) AS INTEGER) DESC 
         LIMIT 1`,
        [orgIdStr],
      );

      let maxNum = 0;
      if (maxResult.rows.length > 0) {
        const match = maxResult.rows[0].po_number.match(/PO-(\d+)/);
        if (match) maxNum = parseInt(match[1]);
      }
      poNumber = `PO-${String(maxNum + 1).padStart(4, "0")}`;
    }

    const poInsert = await client.query(
      `INSERT INTO purchase_orders 
        (po_number, po_date, po_due_date, vendor_id, vendor_name, status, org_id, 
         created_by, updated_by, total_amount)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       RETURNING id, po_number, po_date, po_due_date, vendor_id, vendor_name, status, total_amount`,
      [
        poNumber,
        po_date || new Date().toLocaleDateString("en-CA"),
        po_due_date || null,
        vendor.id,
        vendor.vendor_name,
        "Draft",
        orgIdStr,
        userId || userEmail,
        userId || userEmail,
        0,
      ],
    );

    const purchaseOrder = poInsert.rows[0];

    let totalAmount = 0;
    for (const item of items) {
      const unitPrice = Number(item.unit_price) || 0;
      const quantity = Number(item.quantity) || 0;
      const lineTotal = quantity * unitPrice;
      totalAmount += lineTotal;

      await client.query(
        `INSERT INTO purchase_order_items
          (po_id, item_code, item_name, description, quantity, uom, unit_price, total_price)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
        [
          purchaseOrder.id,
          item.item_code || "",
          item.item_name || "",
          item.description || "",
          quantity,
          item.uom || "",
          unitPrice,
          lineTotal,
        ],
      );
    }

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

    // ==================== SEED APPROVALS ====================
    console.log(
      `[createDirectPO] Seeding approvals for PO ${purchaseOrder.id}...`,
    );
    // await seedPOApprovals(client, purchaseOrder.id, "PO");
    console.log(`[createDirectPO] Approvals seeded successfully`);

    await client.query("COMMIT");

    const completePO = await getPOByIdInternal(purchaseOrder.id, orgIdStr);

    res.status(201).json({
      success: true,
      message:
        "Purchase Order created successfully. It will be sent to vendor once all approvals are completed.",
      data: completePO,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createDirectPO:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

// ==================== GET ALL PURCHASE ORDERS (No Department Filtering) ====================
const getAllPurchaseOrders = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized - Organization ID missing",
      });
    }

    const orgIdStr = String(orgId);

    const {
      search = "",
      page = 1,
      limit = 10,
      sortBy = "created_at",
      sortOrder = "DESC",
      status = "all",
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    let statusCondition = "";
    let queryParams = [orgIdStr, searchTerm, searchTerm];

    if (status !== "all") {
      statusCondition = " AND po.status = $" + (queryParams.length + 1);
      queryParams.push(status);
    }

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM purchase_orders po
       WHERE po.is_deleted = false AND po.org_id = $1
         AND (po.po_number ILIKE $2 OR po.pr_number ILIKE $3)
         ${statusCondition}`,
      queryParams,
    );

    const total = parseInt(countResult.rows[0].total);
    queryParams.push(parseInt(limit), offset);

    const result = await pool.query(
      `SELECT 
         po.*,
         r.department,
         CASE 
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
             CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
           ELSE 
             COALESCE(r.requested_by, 
               CASE WHEN po.created_by IS NOT NULL THEN 
                 (SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) 
                  FROM users WHERE id = po.created_by::int)
               ELSE 'System' END
             )
         END as requested_by,
         COALESCE(
           (SELECT json_agg(json_build_object(
             'id', poi.id,
             'item_code', poi.item_code,
             'item_name', poi.item_name,
             'description', poi.description,
             'quantity', poi.quantity,
             'uom', poi.uom,
             'unit_price', poi.unit_price,
             'total_price', poi.total_price
           ) ORDER BY poi.id)
           FROM purchase_order_items poi WHERE poi.po_id = po.id
         ), '[]'::json) as items
       FROM purchase_orders po
       LEFT JOIN requisitions r ON po.pr_id = r.id AND r.is_deleted = false
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
       WHERE po.is_deleted = false AND po.org_id = $1
         AND (po.po_number ILIKE $2 OR po.pr_number ILIKE $3)
         ${statusCondition}
       ORDER BY po.${sortBy} ${sortOrder}
       LIMIT $${queryParams.length - 1} OFFSET $${queryParams.length}`,
      queryParams,
    );

    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 getAllPurchaseOrders:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ==================== GET WAITING FOR PO (with RFQ status) ====================
const getWaitingForPO = async (req, res) => {
  try {
    const sessionUser = req.session?.user;
    const orgId = sessionUser?.orgId || req.user?.orgId;

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

    const orgIdStr = String(orgId);
    const { search = "", page = 1, limit = 10 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    const mainQuery = `
      SELECT
        r.id,
        r.pr_number,
        r.pr_date,
        r.pr_due_date,
        r.department,
        r.requested_by,
        r.requested_by_id,
        r.status,
        r.created_at,
        r.po_status,
        CASE
          WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN
            CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
          ELSE
            COALESCE(r.requested_by, 'System')
        END AS requested_by_name,
        
        -- RFQ Status
        CASE WHEN rfq.id IS NOT NULL THEN true ELSE false END as rfq_sent,
        rfq.id as rfq_id,
        rfq.rfq_number,
        rfq.status as rfq_status,

        (
          SELECT json_agg(row_data ORDER BY row_data_id)
          FROM (
            SELECT
              MIN(pi.id) AS row_data_id,
              json_build_object(
                'id', MIN(pi.id),
                'requisition_item_id', pi.requisition_item_id,
                'item_code', pi.item_code,
                'item_name', pi.item_name,
                'description', COALESCE(ri.description, ''),
                'uom', COALESCE(ri.uom, ''),
                'total_quantity', SUM(pi.requested_quantity),
                'remaining_quantity', SUM(pi.remaining_quantity),
                'po_requested_quantity', SUM(COALESCE(pi.po_requested_quantity, 0)),
                'already_raised_quantity', SUM(COALESCE(pi.issued_quantity, 0)),
                'rfq_sent_quantity', SUM(COALESCE(pi.rfq_sent_quantity, 0)),
                'status',
                  CASE
                    WHEN bool_or(pi.status = 'PO Raised') THEN 'PO Raised'
                    WHEN bool_or(pi.status = 'PO Requested') THEN 'PO Requested'
                    WHEN bool_or(pi.status = 'RFQ Sent') THEN 'RFQ Sent'
                    ELSE 'Pending'
                  END
              ) AS row_data
            FROM pr_inventory pi
            LEFT JOIN requisition_items ri ON ri.id = pi.requisition_item_id
            WHERE pi.requisition_id = r.id
              AND pi.remaining_quantity > 0
            GROUP BY
              pi.requisition_item_id,
              pi.item_code,
              pi.item_name,
              ri.description,
              ri.uom
          ) agg
        ) AS items,

        COALESCE(
          (SELECT COUNT(DISTINCT pi.requisition_item_id)
           FROM pr_inventory pi
           WHERE pi.requisition_id = r.id
             AND pi.remaining_quantity > 0),
          0
        ) AS items_pending,

        COALESCE(
          (SELECT SUM(COALESCE(pi.remaining_quantity, 0))
           FROM pr_inventory pi
           WHERE pi.requisition_id = r.id),
          0
        ) AS total_remaining_qty

      FROM requisitions r
      LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
      LEFT JOIN rfqs rfq ON rfq.pr_id = r.id AND rfq.is_deleted = false
      WHERE r.is_deleted = false
        AND r.org_id = $1
        AND r.status IN ('Approved', 'Partially Issued')
        AND EXISTS (
          SELECT 1 FROM pr_inventory pi
          WHERE pi.requisition_id = r.id
            AND pi.remaining_quantity > 0
        )
        AND (
          r.pr_number ILIKE $2
          OR r.requested_by ILIKE $2
          OR r.department ILIKE $2
          OR EXISTS (
            SELECT 1 FROM users u2
            WHERE u2.id = r.requested_by_id
              AND u2.is_deleted = false
              AND CONCAT(COALESCE(u2.first_name, ''), ' ', COALESCE(u2.last_name, '')) ILIKE $2
          )
        )
      GROUP BY r.id, u.id, requested_by_name, rfq.id, rfq.rfq_number, rfq.status
      ORDER BY 
        CASE WHEN rfq.id IS NOT NULL THEN 1 ELSE 0 END ASC,
        r.created_at DESC
      LIMIT $3 OFFSET $4
    `;

    const countQuery = `
      SELECT COUNT(DISTINCT r.id) as total
      FROM requisitions r
      WHERE r.is_deleted = false
        AND r.org_id = $1
        AND r.status IN ('Approved', 'Partially Issued')
        AND EXISTS (
          SELECT 1 FROM pr_inventory pi
          WHERE pi.requisition_id = r.id
            AND pi.remaining_quantity > 0
        )
    `;

    const countParams = [orgIdStr];
    const countResult = await pool.query(countQuery, countParams);
    const total = parseInt(countResult.rows[0].total);

    const mainQueryParams = [
      orgIdStr,
      searchTerm,
      parseInt(limit),
      offset,
    ];

    const result = await pool.query(mainQuery, mainQueryParams);

    const formattedData = result.rows.map((row) => ({
      ...row,
      id: parseInt(row.id),
      requested_by: row.requested_by_name,
      items_pending: parseInt(row.items_pending) || 0,
      total_remaining_qty: parseFloat(row.total_remaining_qty) || 0,
      rfq_sent: row.rfq_sent || false,
      rfq_id: row.rfq_id,
      rfq_number: row.rfq_number,
      rfq_status: row.rfq_status,
      items: (row.items || []).map((item) => ({
        ...item,
        total_quantity: parseFloat(item.total_quantity) || 0,
        remaining_quantity: parseFloat(item.remaining_quantity) || 0,
        already_raised_quantity: parseFloat(item.already_raised_quantity) || 0,
        po_requested_quantity: parseFloat(item.po_requested_quantity) || 0,
        rfq_sent_quantity: parseFloat(item.rfq_sent_quantity) || 0,
      })),
    }));

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



// ==================== GET PO BY ID ====================
const getPOById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

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

    const orgIdStr = String(orgId);
    const po = await getPOByIdInternal(id, orgIdStr);

    if (!po) {
      return res.status(404).json({
        success: false,
        message: "Purchase Order not found",
      });
    }

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

// ==================== UPDATE PO STATUS ====================
const updatePOStatus = 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 - Organization ID missing",
      });
    }

    const orgIdStr = String(orgId);
    const validStatuses = [
      "Draft",
      "Sent",
      "Confirmed",
      "Shipped",
      "Delivered",
      "Completed",
      "Cancelled",
    ];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: "Invalid status",
      });
    }

    const result = await pool.query(
      `UPDATE purchase_orders 
       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), orgIdStr],
    );

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

    res.json({
      success: true,
      message: `PO status updated to ${status}`,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("Error in updatePOStatus:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ==================== DELETE/CANCEL PO ====================
const deletePO = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const { cancel_reason } = 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 - Organization ID missing",
      });
    }

    const orgIdStr = String(orgId);
    const poIdInt = parseInt(id);

    const poDetails = await client.query(
      `SELECT po.*, array_agg(poi.requisition_item_id) as requisition_item_ids
       FROM purchase_orders po
       LEFT JOIN purchase_order_items poi ON poi.po_id = po.id
       WHERE po.id = $1 AND po.org_id = $2 AND po.is_deleted = false
       GROUP BY po.id`,
      [poIdInt, orgIdStr],
    );

    if (poDetails.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: `Purchase Order not found`,
      });
    }

    const purchaseOrder = poDetails.rows[0];

    await client.query("BEGIN");

    await client.query(
      `UPDATE purchase_orders
       SET is_deleted = true, 
           updated_by = $1, 
           updated_at = NOW(), 
           deleted_at = NOW(),
           status = 'Cancelled',
           cancel_reason = $2,
           cancelled_by = $3,
           cancelled_at = NOW()
       WHERE id = $4 AND org_id = $5`,
      [updated_by, cancel_reason || null, updated_by, poIdInt, orgIdStr],
    );

    if (
      purchaseOrder.requisition_item_ids &&
      purchaseOrder.requisition_item_ids[0] !== null
    ) {
      for (const reqItemId of purchaseOrder.requisition_item_ids) {
        await client.query(
          `UPDATE requisition_items 
           SET quantity_po_raised = 0,
               updated_at = NOW()
           WHERE id = $1`,
          [reqItemId],
        );
      }
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "Purchase Order cancelled successfully",
      data: { id: poIdInt },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in deletePO:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ==================== GET PO BY NUMBER ====================
const getPOByNumber = async (req, res) => {
  try {
    const { po_number } = req.params;
    const orgId = req.user?.orgId;

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

    const orgIdStr = String(orgId);

    const result = await pool.query(
      `
      SELECT 
        po.id,
        po.po_number,
        po.po_date,
        po_due_date,
        po.vendor_id,
        po.vendor_name,
        po.status,
        po.total_amount,
        po.pr_number,

        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'id', poi.id,
                'item_code', poi.item_code,
                'item_name', poi.item_name,
                'description', poi.description,
                'quantity', poi.quantity,
                'uom', poi.uom,
                'unit_price', COALESCE(poi.unit_price, 0),
                'total_price', COALESCE(poi.total_price, 0)
              )
              ORDER BY poi.id
            )
            FROM purchase_order_items poi
            WHERE poi.po_id = po.id
          ),
          '[]'::json
        ) AS items,

        (
          SELECT json_build_object(
            'id', w.id,
            'warehouseName', w.warehouse_name,
            'warehouseCode', w.warehouse_code,
            'capacity', w.capacity,
            'groundStock', w.ground_stock,
            'alertStock', w.alert_stock,
            'items', w.items
          )
          FROM warehouses w
          WHERE EXISTS (
            SELECT 1
            FROM jsonb_array_elements(w.items) item
            WHERE item->>'item_name' = (
              SELECT poi2.item_name
              FROM purchase_order_items poi2
              WHERE poi2.po_id = po.id
              LIMIT 1
            )
          )
          LIMIT 1
        ) AS warehouse

      FROM purchase_orders po

      WHERE po.po_number = $1
        AND po.org_id = $2
        AND po.is_deleted = false
      `,
      [po_number, orgIdStr],
    );

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

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

// ==================== SEND PO EMAIL ON FULL APPROVAL ====================
const sendPOEmailOnFullApproval = async (req, res) => {
  const client = await pool.connect();

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

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

    // Get PO details
    const poResult = await client.query(
      `SELECT po.*, 
              r.department,
              CASE 
                WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
                  CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
                ELSE 
                  COALESCE(r.requested_by, 'System')
              END as requested_by
       FROM public.purchase_orders po
       LEFT JOIN requisitions r ON po.pr_id = r.id AND r.is_deleted = false
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
       WHERE po.id = $1 AND po.org_id = $2 AND po.is_deleted = false`,
      [parseInt(poId), String(orgId)],
    );

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

    const purchaseOrder = poResult.rows[0];

    // Check if email already sent
    if (purchaseOrder.email_sent === true) {
      return res.status(200).json({
        success: true,
        message: "Email already sent for this PO",
        data: { alreadySent: true, sentAt: purchaseOrder.email_sent_at },
      });
    }

    // Get vendor details
    let vendorInfo = null;
    if (purchaseOrder.vendor_id) {
      const vendorResult = await client.query(
        `SELECT id, vendor_name, email, gstin, address, phone
         FROM public.procurement_vendors 
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [purchaseOrder.vendor_id, String(orgId)],
      );
      if (vendorResult.rows.length > 0) {
        vendorInfo = vendorResult.rows[0];
      }
    }

    if (!vendorInfo || !vendorInfo.email) {
      return res.status(400).json({
        success: false,
        message: "Vendor email not found. Cannot send PO.",
      });
    }

    // Get PO items
    const itemsResult = await client.query(
      `SELECT * FROM public.purchase_order_items WHERE po_id = $1`,
      [parseInt(poId)],
    );
    const items = itemsResult.rows;

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

    // Get company details
    let company = {
      name: "VIVEK BIO-PRODUCTS PRIVATE LIMITED",
      address: "Hyderabad, Telangana, India",
      gst: "36AAICV8996R1ZU",
      cin: "U24100TG2022PTC165236",
    };

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

    await client.query("BEGIN");

    // Generate PDF
    console.log(`📄 Generating PDF for PO ${purchaseOrder.po_number}...`);
    let pdfBuffer;
    try {
      pdfBuffer = await generatePOPDFBuffer(
        purchaseOrder,
        items,
        company,
        vendorInfo,
      );
    } catch (pdfError) {
      console.error("PDF generation failed:", pdfError.message);
      return res.status(500).json({
        success: false,
        message: "Failed to generate PDF: " + pdfError.message,
      });
    }

    // Send email
    console.log(`📧 Sending email to ${vendorInfo.email}...`);
    const emailResult = await sendPOEmail(
      purchaseOrder,
      vendorInfo,
      company,
      pdfBuffer,
      items,
    );

    if (emailResult.success) {
      // Update PO status
      await client.query(
        `UPDATE public.purchase_orders 
         SET status = 'Sent', 
             email_sent = true, 
             email_sent_at = NOW(),
             updated_at = NOW()
         WHERE id = $1`,
        [parseInt(poId)],
      );

      await client.query("COMMIT");

      console.log(
        `✅ Email sent successfully for PO ${purchaseOrder.po_number}`,
      );

      return res.status(200).json({
        success: true,
        message: `PO email sent successfully to ${vendorInfo.email}`,
        data: {
          emailSent: true,
          vendorEmail: vendorInfo.email,
          sentAt: new Date().toISOString(),
        },
      });
    } else {
      await client.query("ROLLBACK");
      throw new Error(emailResult.error || "Failed to send email");
    }
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in sendPOEmailOnFullApproval:", error);
    return res.status(500).json({
      success: false,
      message: error.message || "Failed to send email",
    });
  } finally {
    client.release();
  }
};

// ==================== DOWNLOAD PO PDF ====================
const downloadPOPDF = 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 orgIdStr = String(orgId);

    const poResult = await pool.query(
      `SELECT
         po.*,
         r.department,
         CASE
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN
             CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))
           ELSE
             COALESCE(r.requested_by,
               CASE WHEN po.created_by IS NOT NULL THEN
                 (SELECT CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))
                  FROM users WHERE id = po.created_by::int)
               ELSE 'System' END
             )
         END AS requested_by,
         COALESCE(
           (SELECT json_agg(json_build_object(
             'id',          poi.id,
             'item_code',   poi.item_code,
             'item_name',   poi.item_name,
             'description', poi.description,
             'quantity',    poi.quantity,
             'uom',         poi.uom,
             'unit_price',  COALESCE(poi.unit_price, 0),
             'total_price', COALESCE(poi.total_price, 0)
           ) ORDER BY poi.id)
           FROM purchase_order_items poi WHERE poi.po_id = po.id
         ), '[]'::json) AS items
       FROM purchase_orders po
       LEFT JOIN requisitions r ON po.pr_id = r.id AND r.is_deleted = false
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
       WHERE po.id = $1 AND po.org_id = $2 AND po.is_deleted = false`,
      [parseInt(id), orgIdStr],
    );

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

    const purchaseOrder = poResult.rows[0];
    const items = purchaseOrder.items || [];

    let vendorDetails = {
      vendor_name: purchaseOrder.vendor_name || "N/A",
      gstin: "N/A",
      address: "N/A",
      phone: "N/A",
      email: "N/A",
    };

    if (purchaseOrder.vendor_id) {
      const vr = await pool.query(
        `SELECT vendor_name, email, gstin, address, phone, city, state, pincode
         FROM procurement_vendors
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [purchaseOrder.vendor_id, orgIdStr],
      );
      if (vr.rows.length > 0) {
        const v = vr.rows[0];
        const addrParts = [v.address, v.city, v.state, v.pincode].filter(
          Boolean,
        );
        vendorDetails = {
          vendor_name: v.vendor_name || "N/A",
          gstin: v.gstin || "N/A",
          address: addrParts.join(", ") || "N/A",
          phone: v.phone || "N/A",
          email: v.email || "N/A",
        };
      }
    }

    let companyData = {
      name: "VIVEK BIO-PRODUCTS PRIVATE LIMITED",
      address:
        "Flat No. - 406, 4th Floor, Kadiris Apuroopa Urban, Botanical Garden Road Kondapur, Hyderabad, Telangana, India - 500084",
      gst: "36AAICV8996R1ZU",
      cin: "U24100TG2022PTC165236",
      pan: "AAICV8996R",
      phone: "8019637711",
      email: "sales@vivekbio.com",
    };

    try {
      const cr = await pool.query(
        `SELECT * FROM organizations WHERE id = $1 LIMIT 1`,
        [orgIdStr],
      );
      if (cr.rows.length > 0) {
        const org = cr.rows[0];
        let fullAddress =
          org.address || org.org_address || org.company_address || "";
        if (!fullAddress && org.address_line1) {
          fullAddress = [
            org.address_line1,
            org.address_line2,
            org.city,
            org.state,
            org.pin_code,
          ]
            .filter(Boolean)
            .join(", ");
        }
        companyData = {
          name:
            org.name || org.org_name || org.company_name || companyData.name,
          address: fullAddress || companyData.address,
          gst: org.gst || org.gstin || org.gst_number || companyData.gst,
          cin: org.cin || org.cin_number || org.company_cin || companyData.cin,
          pan: org.pan || org.pan_number || org.tax_pan || companyData.pan,
          phone:
            org.phone ||
            org.phone_number ||
            org.contact_number ||
            companyData.phone,
          email:
            org.email || org.company_email || org.email_id || companyData.email,
        };
      }
    } catch (err) {
      console.log("Using default company details:", err.message);
    }

    let terms = [];
    try {
      const tr = await pool.query(
        `SELECT term, description, term_order
         FROM purchase_term_rows
         WHERE doc_type = 'po' AND is_active = true
         ORDER BY term_order ASC`,
      );
      terms = tr.rows;
    } catch (err) {
      console.log("Terms fetch failed, using defaults:", err.message);
    }

    let logoSrc = "";
    try {
      const logoEnv = process.env.COMPANY_LOGO || "";
      const fileName = logoEnv.split("/").pop();
      const ext = fileName.split(".").pop().toLowerCase();
      const mimeMap = {
        png: "image/png",
        jpg: "image/jpeg",
        jpeg: "image/jpeg",
        gif: "image/gif",
        svg: "image/svg+xml",
        webp: "image/webp",
      };
      const mime = mimeMap[ext] || "image/png";
      const logoPath = path.join(__dirname, "../../public", fileName);
      if (fileName && fs.existsSync(logoPath)) {
        const b64 = fs.readFileSync(logoPath).toString("base64");
        logoSrc = `data:${mime};base64,${b64}`;
      } else if (logoEnv) {
        logoSrc = logoEnv;
      }
    } catch (err) {
      console.log("Logo load failed:", err.message);
    }

    const templatePath = path.join(
      __dirname,
      "../../templates/po-pdf-template.html",
    );
    if (!fs.existsSync(templatePath)) {
      return res.status(404).json({
        success: false,
        message: `Template not found: ${templatePath}`,
      });
    }
    let html = fs.readFileSync(templatePath, "utf8");

    const addrParts = companyData.address.split(",");
    const addrLine1 = addrParts.slice(0, 3).join(",").trim();


    const grossTotal = items.reduce(
      (acc, item) =>
        acc + (Number(item.quantity) || 0) * (Number(item.unit_price) || 0),
      0,
    );
    const roundOff = Math.round(grossTotal) - grossTotal;
    const grandTotal = grossTotal + roundOff;

    const formatNum = (n) =>
      n.toLocaleString("en-IN", {
        minimumFractionDigits: 2,
        maximumFractionDigits: 2,
      });
    const poDate = purchaseOrder.po_date
      ? new Date(purchaseOrder.po_date).toLocaleDateString("en-GB")
      : "-";
    const poDueDate = purchaseOrder.po_due_date
      ? new Date(purchaseOrder.po_due_date).toLocaleDateString("en-GB")
      : "-";
    const poNumber = purchaseOrder.po_number || "-";


   let baseAmount ;
    let taxPercentage ;
    let cgstPercentage = taxPercentage / 2;
    let sgstPercentage = taxPercentage / 2;    
    let cgstAmount = (baseAmount * cgstPercentage) / 100;
    let sgstAmount = (baseAmount * sgstPercentage) / 100;

    let itemsRows = "";
    items.forEach((item, i) => {
      const qty = Number(item.quantity) || 0;
      const rate = Number(item.unit_price) || 0;
         baseAmount = qty * (item.unit_price || 0);
       taxPercentage = (item.tax_percentage) || 0;
     cgstPercentage = taxPercentage / 2;
     sgstPercentage = taxPercentage / 2;
     cgstAmount = (baseAmount * cgstPercentage) / 100;
     sgstAmount = (baseAmount * sgstPercentage) / 100;
      const total = qty * rate;
      itemsRows += `
        <tr>
          <td class="tc">${i + 1}</td>
          <td><span class="code-val">${escapeHtml(item.item_code || "-")}</span></td>
          <td>${escapeHtml(item.item_name || "")}</td>
          <td>${escapeHtml(item.description || "-")}</td>
          <td class="tc">${qty.toFixed(2)}</td>
          <td class="tc">${escapeHtml(item.uom || "-")}</td>
        <td class="tr">${rate.toFixed(2)}</td>
            <td class="tr">${taxPercentage.toFixed(2)}</td>
          <td class="tr">${cgstAmount.toFixed(2)}</td>
              <td class="tr">${sgstAmount.toFixed(2)}</td>
          <td class="tr">${total.toFixed(2)}</td>
        </tr>`;
    });

    let termsHtml = "";
    if (terms.length > 0) {
      terms.forEach((t, idx) => {
        termsHtml += `${idx + 1}. ${escapeHtml(t.term)}${t.description ? " : " + escapeHtml(t.description) : ""}<br/>`;
      });
    } else {
      termsHtml =
        `1. Goods once sold will not be taken back.<br/>` +
        `2. Subject to Hyderabad jurisdiction.<br/>` +
        `3. Payment to be made as per agreed terms.`;
    }

    html = html
      .replace(/{{COMPANY_LOGO}}/g, logoSrc)
      .replace(/{{COMPANY_NAME}}/g, escapeHtml(companyData.name))
      .replace(/{{COMPANY_ADDRESS_LINE1}}/g, escapeHtml(addrLine1))
      .replace(/{{COMPANY_PAN}}/g, escapeHtml(companyData.pan))
      .replace(/{{COMPANY_GST}}/g, escapeHtml(companyData.gst))
      .replace(/{{COMPANY_CIN}}/g, escapeHtml(companyData.cin))
      .replace(/{{COMPANY_EMAIL}}/g, escapeHtml(companyData.email))
      .replace(/{{COMPANY_PHONE}}/g, escapeHtml(companyData.phone))
      .replace(/{{VENDOR_NAME}}/g, escapeHtml(vendorDetails.vendor_name))
      .replace(/{{VENDOR_GST}}/g, escapeHtml(vendorDetails.gstin))
      .replace(/{{VENDOR_ADDRESS}}/g, escapeHtml(vendorDetails.address))
      .replace(/{{VENDOR_PHONE}}/g, escapeHtml(vendorDetails.phone))
      .replace(/{{VENDOR_EMAIL}}/g, escapeHtml(vendorDetails.email))
      .replace(/{{PO_NUMBER}}/g, escapeHtml(poNumber))
      .replace(/{{PO_DATE}}/g, poDate)
      .replace(/{{PO_DUE_DATE}}/g, poDueDate)
      .replace(/{{PR_NUMBER}}/g, escapeHtml(purchaseOrder.pr_number || "-"))
      .replace(
        /{{DEPARTMENT}}/g,
        escapeHtml(purchaseOrder.department || "Production"),
      )
      .replace(
        /{{REQUESTED_BY}}/g,
        escapeHtml(purchaseOrder.requested_by || "System"),
      )
      .replace(/{{ITEMS_ROWS}}/g, itemsRows)
      .replace(/{{TERMS_CONTENT}}/g, termsHtml)
      .replace(/{{GROSS_AMOUNT}}/g, formatNum(grossTotal))
      .replace(/{{NET_VALUE}}/g, formatNum(grossTotal))
       .replace(/{{CGST_AMOUNT}}/g, formatNum(cgstAmount))
         .replace(/{{SGST_AMOUNT}}/g, formatNum(sgstAmount))
      .replace(/{{ROUND_OFF}}/g, roundOff.toFixed(2))
      .replace(/{{TOTAL_VALUE}}/g, formatNum(grandTotal))
      .replace(
        /{{AMOUNT_IN_WORDS}}/g,
        escapeHtml(numberToWords(Math.round(grandTotal))),
      );

    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 pdfBuffer = await page.pdf({
      format: "A4",
      printBackground: true,
      margin: { top: "15px", bottom: "15px", left: "15px", right: "15px" },
    });
    await browser.close();

    res.setHeader("Content-Type", "application/pdf");
    res.setHeader(
      "Content-Disposition",
      `attachment; filename="PO_${poNumber}.pdf"`,
    );
    res.setHeader("Content-Length", pdfBuffer.length);
    return res.send(pdfBuffer);
  } catch (error) {
    console.error("Download PO PDF Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to generate PDF",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

// ==================== CREATE PO FROM QUOTATION COMPARISON (WITH REMAINING QUANTITY TRACKING) ====================
const createPOFromComparison = async (req, res) => {
  const client = await pool.connect();

  try {
    const { rfqId, vendorId } = req.params;
    const { items, po_date, po_due_date } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.id;
    const userEmail = req.user?.email;

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

    if (!rfqId || !vendorId) {
      return res.status(400).json({
        success: false,
        message: "RFQ ID and Vendor ID are required",
      });
    }

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

    const orgIdStr = String(orgId);
    const vendorIdInt = parseInt(vendorId);
    const rfqIdInt = parseInt(rfqId);

    // Verify RFQ exists and get its details with remaining quantities
    const rfqCheck = await client.query(
      `SELECT r.id, r.rfq_number, r.pr_id, r.status, r.pr_number,
              ri.id as rfq_item_id, ri.item_code, ri.item_name, 
              ri.description, ri.quantity as rfq_quantity, ri.uom,
              COALESCE(ri.po_raised_quantity, 0) as po_raised_quantity
       FROM rfqs r
       JOIN rfq_items ri ON ri.rfq_id = r.id
       WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
      [rfqIdInt, orgIdStr],
    );

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

    const rfq = rfqCheck.rows[0];
    const rfqItemsMap = new Map();
    
    for (const row of rfqCheck.rows) {
      rfqItemsMap.set(row.rfq_item_id, {
        id: row.rfq_item_id,
        item_code: row.item_code,
        item_name: row.item_name,
        description: row.description,
        rfq_quantity: parseFloat(row.rfq_quantity) || 0,
        po_raised_quantity: parseFloat(row.po_raised_quantity) || 0,
        uom: row.uom,
        remaining_quantity: (parseFloat(row.rfq_quantity) || 0) - (parseFloat(row.po_raised_quantity) || 0)
      });
    }

    // Verify vendor exists
    const vendorCheck = await client.query(
      `SELECT id, vendor_name, email, gstin, address, phone, vendor_code 
       FROM procurement_vendors 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [vendorIdInt, orgIdStr],
    );

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

    const vendor = vendorCheck.rows[0];

    await client.query("BEGIN");

    // Generate PO number
    let poNumber;
    try {
      const prefixResult = await client.query(
        `SELECT prefix_code, last_number 
         FROM public.prefixes 
         WHERE module_name = 'po' 
           AND status = 'ACTIVE' 
           AND is_deleted = false
         FOR UPDATE`,
      );

      let prefix = "PO";
      let nextNumber = 1;

      if (prefixResult.rows.length > 0) {
        prefix = prefixResult.rows[0].prefix_code;
        nextNumber = prefixResult.rows[0].last_number + 1;

        await client.query(
          `UPDATE public.prefixes 
           SET last_number = $1, updated_at = NOW()
           WHERE module_name = 'po' AND status = 'ACTIVE' AND is_deleted = false`,
          [nextNumber],
        );
      } else {
        await client.query(
          `INSERT INTO public.prefixes (module_name, prefix_code, last_number, status) 
           VALUES ('po', 'PO', 1, 'ACTIVE')`,
        );
      }

      poNumber = `${prefix}-${String(nextNumber).padStart(4, "0")}`;
    } catch (prefixError) {
      console.error("Error generating PO number:", prefixError);
      const maxResult = await client.query(
        `SELECT po_number FROM purchase_orders 
         WHERE org_id = $1 AND po_number ~ '^PO-[0-9]+$'
         ORDER BY CAST(SUBSTRING(po_number FROM 4) AS INTEGER) DESC 
         LIMIT 1`,
        [orgIdStr],
      );

      let maxNum = 0;
      if (maxResult.rows.length > 0) {
        const match = maxResult.rows[0].po_number.match(/PO-(\d+)/);
        if (match) maxNum = parseInt(match[1]);
      }
      poNumber = `PO-${String(maxNum + 1).padStart(4, "0")}`;
    }

    // Create PO with reference to RFQ
    const poInsert = await client.query(
      `INSERT INTO purchase_orders 
        (po_number, po_date, po_due_date, vendor_id, vendor_name, status, org_id, 
         created_by, updated_by, total_amount, rfq_id, rfq_number)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
       RETURNING id, po_number, po_date, po_due_date, vendor_id, vendor_name, status, total_amount`,
      [
        poNumber,
        po_date || new Date().toLocaleDateString("en-CA"),
        po_due_date || null,
        vendor.id,
        vendor.vendor_name,
        "Draft",
        orgIdStr,
        userId || userEmail,
        userId || userEmail,
        0,
        rfqIdInt,
        rfq.rfq_number,
      ],
    );

    const purchaseOrder = poInsert.rows[0];
    let totalAmount = 0;

    // Insert items and UPDATE po_raised_quantity on rfq_items
    for (const item of items) {
      const unitPrice = Number(item.unit_price) || 0;
      const quantity = Number(item.quantity) || 0;
      const lineTotal = quantity * unitPrice;
      totalAmount += lineTotal;

      // Get the RFQ item
      const rfqItem = rfqItemsMap.get(item.id);
      
      if (!rfqItem) {
        console.warn(`RFQ item ${item.id} not found, skipping...`);
        continue;
      }

      // Check if quantity exceeds remaining quantity
      const newPoRaisedQuantity = rfqItem.po_raised_quantity + quantity;
      if (newPoRaisedQuantity > rfqItem.rfq_quantity) {
        await client.query("ROLLBACK");
        return res.status(400).json({
          success: false,
          message: `Quantity for ${rfqItem.item_name} exceeds remaining quantity. Available: ${rfqItem.remaining_quantity}`,
        });
      }

      // Insert into purchase_order_items
      await client.query(
        `INSERT INTO purchase_order_items
          (po_id, item_code, item_name, description, quantity, uom, unit_price, total_price, rfq_item_id)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [
          purchaseOrder.id,
          item.item_code || "",
          item.item_name || "",
          item.description || "",
          quantity,
          item.uom || "",
          unitPrice,
          lineTotal,
          item.id,
        ],
      );

      // UPDATE po_raised_quantity on rfq_items
      await client.query(
        `UPDATE rfq_items 
         SET po_raised_quantity = COALESCE(po_raised_quantity, 0) + $1,
             updated_at = NOW()
         WHERE id = $2`,
        [quantity, item.id],
      );

      // If RFQ is linked to a PR, update pr_inventory as well
      if (rfq.pr_id) {
        const inventoryUpdate = await client.query(
          `UPDATE pr_inventory 
           SET issued_quantity = COALESCE(issued_quantity, 0) + $1,
               remaining_quantity = remaining_quantity - $1,
               status = CASE 
                 WHEN remaining_quantity - $1 <= 0 THEN 'Fully Issued'
                 ELSE 'Partially Issued'
               END,
               updated_at = NOW()
           WHERE requisition_id = $2 
             AND item_code = $3
           RETURNING id`,
          [quantity, rfq.pr_id, item.item_code],
        );

        if (inventoryUpdate.rows.length === 0) {
          console.log(`No pr_inventory record found for PR ${rfq.pr_id}, item ${item.item_code}`);
        }
      }
    }

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

    // Check if all RFQ items are fully raised
    const remainingItemsCheck = await client.query(
      `SELECT COUNT(*) as count 
       FROM rfq_items 
       WHERE rfq_id = $1 
         AND COALESCE(po_raised_quantity, 0) < quantity`,
      [rfqIdInt],
    );

    if (parseInt(remainingItemsCheck.rows[0].count) === 0) {
      await client.query(
        `UPDATE rfqs 
         SET status = 'PO Fully Raised', updated_at = NOW()
         WHERE id = $1`,
        [rfqIdInt],
      );
    } else {
      await client.query(
        `UPDATE rfqs 
         SET status = 'Partially PO Raised', updated_at = NOW()
         WHERE id = $1`,
        [rfqIdInt],
      );
    }

    // Seed approvals for the PO
    console.log(`[createPOFromComparison] Seeding approvals for PO ${purchaseOrder.id}...`);

    await client.query("COMMIT");

    const completePO = await getPOByIdInternal(purchaseOrder.id, orgIdStr);

    res.status(201).json({
      success: true,
      message: `Purchase Order ${poNumber} created successfully.`,
      data: completePO,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createPOFromComparison:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

// ==================== CREATE PO FROM COMPARISON FOR EXTERNAL VENDOR ====================
const createPOFromComparisonExternal = async (req, res) => {
  const client = await pool.connect();

  try {
    const { rfqId } = req.params;
    const { items, po_date, po_due_date, vendorEmail, vendorName } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.id;
    const userEmail = req.user?.email;

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

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

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

    if (!vendorEmail) {
      return res.status(400).json({
        success: false,
        message: "Vendor email is required for external vendor",
      });
    }

    const orgIdStr = String(orgId);
    const rfqIdInt = parseInt(rfqId);

    // Verify RFQ exists
    const rfqCheck = await client.query(
      `SELECT r.id, r.rfq_number, r.pr_id, r.status, r.pr_number
       FROM rfqs r
       WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
      [rfqIdInt, orgIdStr],
    );

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

    const rfq = rfqCheck.rows[0];
    const externalVendorName = vendorName || vendorEmail.split('@')[0];

    await client.query("BEGIN");

    // Generate PO number
    let poNumber;
    try {
      const prefixResult = await client.query(
        `SELECT prefix_code, last_number 
         FROM public.prefixes 
         WHERE module_name = 'po' 
           AND status = 'ACTIVE' 
           AND is_deleted = false
         FOR UPDATE`,
      );

      let prefix = "PO";
      let nextNumber = 1;

      if (prefixResult.rows.length > 0) {
        prefix = prefixResult.rows[0].prefix_code;
        nextNumber = prefixResult.rows[0].last_number + 1;

        await client.query(
          `UPDATE public.prefixes 
           SET last_number = $1, updated_at = NOW()
           WHERE module_name = 'po' AND status = 'ACTIVE' AND is_deleted = false`,
          [nextNumber],
        );
      } else {
        await client.query(
          `INSERT INTO public.prefixes (module_name, prefix_code, last_number, status) 
           VALUES ('po', 'PO', 1, 'ACTIVE')`,
        );
      }

      poNumber = `${prefix}-${String(nextNumber).padStart(4, "0")}`;
    } catch (prefixError) {
      console.error("Error generating PO number:", prefixError);
      const maxResult = await client.query(
        `SELECT po_number FROM purchase_orders 
         WHERE org_id = $1 AND po_number ~ '^PO-[0-9]+$'
         ORDER BY CAST(SUBSTRING(po_number FROM 4) AS INTEGER) DESC 
         LIMIT 1`,
        [orgIdStr],
      );

      let maxNum = 0;
      if (maxResult.rows.length > 0) {
        const match = maxResult.rows[0].po_number.match(/PO-(\d+)/);
        if (match) maxNum = parseInt(match[1]);
      }
      poNumber = `PO-${String(maxNum + 1).padStart(4, "0")}`;
    }

    // Create PO with external vendor info (no vendor_id)
    const poInsert = await client.query(
      `INSERT INTO purchase_orders 
        (po_number, po_date, po_due_date, vendor_id, vendor_name, status, org_id, 
         created_by, updated_by, total_amount, rfq_id, rfq_number, is_external_vendor, vendor_email)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
       RETURNING id, po_number, po_date, po_due_date, vendor_id, vendor_name, status, total_amount`,
      [
        poNumber,
        po_date || new Date().toLocaleDateString("en-CA"),
        po_due_date || null,
        null,
        externalVendorName,
        "Draft",
        orgIdStr,
        userId || userEmail,
        userId || userEmail,
        0,
        rfqIdInt,
        rfq.rfq_number,
        true,
        vendorEmail,
      ],
    );

    const purchaseOrder = poInsert.rows[0];
    let totalAmount = 0;

    // Insert items and UPDATE po_raised_quantity on rfq_items
    for (const item of items) {
      const unitPrice = Number(item.unit_price) || 0;
      const quantity = Number(item.quantity) || 0;
      const lineTotal = quantity * unitPrice;
      totalAmount += lineTotal;

      await client.query(
        `INSERT INTO purchase_order_items
          (po_id, item_code, item_name, description, quantity, uom, unit_price, total_price, rfq_item_id)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [
          purchaseOrder.id,
          item.item_code || "",
          item.item_name || "",
          item.description || "",
          quantity,
          item.uom || "",
          unitPrice,
          lineTotal,
          item.id,
        ],
      );

      // UPDATE po_raised_quantity on rfq_items
      await client.query(
        `UPDATE rfq_items 
         SET po_raised_quantity = COALESCE(po_raised_quantity, 0) + $1,
             updated_at = NOW()
         WHERE id = $2`,
        [quantity, item.id],
      );
    }

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

    // Check if all RFQ items are fully raised
    const remainingItemsCheck = await client.query(
      `SELECT COUNT(*) as count 
       FROM rfq_items 
       WHERE rfq_id = $1 
         AND COALESCE(po_raised_quantity, 0) < quantity`,
      [rfqIdInt],
    );

    if (parseInt(remainingItemsCheck.rows[0].count) === 0) {
      await client.query(
        `UPDATE rfqs 
         SET status = 'PO Fully Raised', updated_at = NOW()
         WHERE id = $1`,
        [rfqIdInt],
      );
    } else {
      await client.query(
        `UPDATE rfqs 
         SET status = 'Partially PO Raised', updated_at = NOW()
         WHERE id = $1`,
        [rfqIdInt],
      );
    }

    await client.query("COMMIT");

    const completePO = {
      ...purchaseOrder,
      vendor_name: externalVendorName,
      vendor_email: vendorEmail,
      is_external_vendor: true
    };

    res.status(201).json({
      success: true,
      message: `Purchase Order ${poNumber} created successfully for external vendor ${externalVendorName}.`,
      data: completePO,
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createPOFromComparisonExternal:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

// ==================== EXPORTS ====================
module.exports = {
  createPOFromApprovedPR,
  getAllPurchaseOrders,
  getWaitingForPO,
  getPOById,
  updatePOStatus,
  deletePO,
  createDirectPO,
  getPOByNumber,
  downloadPOPDF,
  sendPOEmailOnFullApproval,
  createPOFromComparison,
  createPOFromComparisonExternal
};
