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


// ==================== EMAIL CONFIGURATION ====================
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");
  }
});

// ==================== 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 };
  }
};

// ==================== HELPER: Generate PO PDF Buffer ====================
const generatePOPDFBuffer = async (po, items, company, vendor, terms = []) => {
  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");

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

    const subtotal = items.reduce((acc, item) => acc + (Number(item.quantity) || 0) * (Number(item.unit_price) || 0), 0);
    const cgst = subtotal * 0.09;
    const sgst = subtotal * 0.09;
    const rawTotal = subtotal + cgst + sgst;
    const roundOff = Math.round(rawTotal) - rawTotal;
    const grandTotal = rawTotal + roundOff;
    
    const totalInWords = numberToWords(Math.round(grandTotal));
    const poDate = po.po_date ? new Date(po.po_date).toLocaleDateString('en-GB') : '-';
    const poNumber = po.po_number;

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

    let itemsRows = '';
    for (let i = 0; i < items.length; i++) {
      const item = items[i];
      const quantity = Number(item.quantity) || 0;
      const rate = Number(item.unit_price) || 0;
      const total = quantity * rate;
      const gstAmount = total * 0.18;
      const discount = 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">${rate.toFixed(2)}</td>
          <td class="text-right">${discount.toFixed(2)}<span class="sub-note">(0.00%)</span></td>
          <td class="text-right">${gstAmount.toFixed(0)}<span class="sub-note">(18%)</span></td>
          <td class="text-right">${total.toFixed(2)}</td>
        </tr>
      `;
    }

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

    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(company.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(company.pan || company.pan_number || 'AAICV8996R'))
      .replace(/{{COMPANY_GST}}/g, escapeHtml(company.gst || company.gstin || '36AAICV8996R1ZU'))
      .replace(/{{COMPANY_CIN}}/g, escapeHtml(company.cin || company.cin_number || 'U24100TG2022PTC165236'))
      .replace(/{{COMPANY_EMAIL}}/g, escapeHtml(company.email || company.company_email || 'sales@vivekbio.com'))
      .replace(/{{COMPANY_PHONE}}/g, escapeHtml(company.phone || company.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(/{{SUPPLY_ADDRESS}}/g, companyAddress.replace(/\n/g, '<br />') || 'N/A')
      .replace(/{{PO_NUMBER}}/g, escapeHtml(poNumber))
      .replace(/{{PO_DATE}}/g, poDate)
      .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(/{{PO_STATUS}}/g, escapeHtml(po.status || 'Draft'))
      .replace(/{{ITEMS_ROWS}}/g, itemsRows)
      .replace(/{{TERMS_CONTENT}}/g, termsHtml)
      .replace(/{{GROSS_AMOUNT}}/g, formatNumber(subtotal))
      .replace(/{{NET_VALUE}}/g, formatNumber(subtotal))
      .replace(/{{CGST_AMOUNT}}/g, formatNumber(cgst))
      .replace(/{{SGST_AMOUNT}}/g, formatNumber(sgst))
      .replace(/{{ROUND_OFF}}/g, roundOff.toFixed(2))
      .replace(/{{TOTAL_VALUE}}/g, formatNumber(grandTotal))
      .replace(/{{AMOUNT_IN_WORDS}}/g, totalInWords)
      .replace(/{{PREPARED_BY}}/g, escapeHtml(po.requested_by || 'System'))
      .replace(/{{VERIFIED_BY}}/g, escapeHtml(po.requested_by || 'System'))
      .replace(/{{APPROVED_BY}}/g, escapeHtml(po.requested_by || 'System'));

    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 PO PDF:", error);
    throw error;
  }
};

// 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();
  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)) {
      console.log('Logo resolved at:', p);
      return p;
    }
  }
  console.log('Logo not found.');
  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';
  }
};

// ==================== CREATE PO FROM APPROVED PR (WITH EMAIL & APPROVALS) ====================
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` 
      });
    }
    
    // Allow both 'Approved' and 'Partially Issued' status
    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;
      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]
        );
      }

      // Update pr_inventory records
      const requisitionItemId = item.requisition_item_id || item.id;
      
      if (requisitionItemId) {
        const recordCount = await client.query(
          `SELECT COUNT(*) as count
           FROM pr_inventory 
           WHERE requisition_item_id = $1 
             AND requisition_id = $2
             AND remaining_quantity > 0`,
          [requisitionItemId, parseInt(pr_id)]
        );
        
        if (recordCount.rows[0].count > 0) {
          await client.query(
            `UPDATE pr_inventory 
             SET status = 'PO Raised',
                 updated_at = NOW(),
                 updated_by = $1
             WHERE requisition_item_id = $2 
               AND requisition_id = $3
               AND remaining_quantity > 0`,
            [created_by, requisitionItemId, parseInt(pr_id)]
          );
        }
      }
    }

    // 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 status 'PO Requested'
    const remainingItems = await client.query(
      `SELECT COUNT(*) as count 
       FROM pr_inventory 
       WHERE requisition_id = $1 
         AND remaining_quantity > 0
         AND status = 'PO Requested'`,
      [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)]
    );



    // ==================== SEND EMAIL TO VENDOR ====================
    let emailSent = false;
    let emailError = null;

    if (vendorInfo && vendorInfo.email) {
      try {
        // Get company details from organizations table
        let company = {
          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 * FROM organizations WHERE id = $1`,
            [orgIdStr]
          );
          if (companyResult.rows[0]) {
            const org = companyResult.rows[0];
            company = {
              name: org.name || org.org_name || org.company_name || company.name,
              address: org.address || org.org_address || company.address,
              gst: org.gst || org.gst_number || org.gstin || company.gst,
              cin: org.cin || org.cin_number || company.cin
            };
          }
        } catch (err) {
          console.log("Using default company details for email:", err.message);
        }
        
        // Generate PDF buffer
        const pdfBuffer = await generatePOPDFBuffer(purchaseOrder, itemsToInsert, company, vendorInfo);
        
        // Send email
        const emailResult = await sendPOEmail(purchaseOrder, vendorInfo, company, pdfBuffer, itemsToInsert);
        emailSent = emailResult.success;
        emailError = emailResult.error;
        
      } catch (pdfError) {
        console.error("PDF generation or email error:", pdfError);
        emailError = pdfError.message;
      }
    }

    await client.query("COMMIT");

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

    res.status(201).json({
      success: true,
      message: "Purchase Order created successfully",
      data: completePO,
      emailSent: emailSent,
      emailError: emailError || null,
      vendorEmail: vendorInfo?.email || null
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in createPOFromApprovedPR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ==================== CREATE DIRECT PO (WITH APPROVALS) ====================
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 safely
    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",
      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 ====================
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 ====================
const getWaitingForPO = 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 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    const countResult = await pool.query(
      `SELECT COUNT(DISTINCT r.id) as total
       FROM requisitions r
       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.status IN ('PO Requested', 'PO Raised')
             AND pi.remaining_quantity > 0
         )
         AND rfq.id IS NULL
         AND (
           r.pr_number ILIKE $2
           OR r.requested_by ILIKE $2
           OR r.department ILIKE $2
           OR EXISTS (
             SELECT 1 FROM users u
             WHERE u.id = r.requested_by_id
               AND u.is_deleted = false
               AND CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')) ILIKE $2
           )
         )`,
      [orgIdStr, searchTerm]
    );

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

    const result = await pool.query(
      `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,
         
         false as rfq_sent,
         NULL as rfq_id,
         NULL as rfq_number,
         NULL 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, ''),
                 'quantity', SUM(pi.remaining_quantity),
                 'remaining_quantity', SUM(pi.remaining_quantity),
                 'issued_quantity', SUM(COALESCE(pi.issued_quantity, 0)),
                 'issued_to_name', MAX(pi.issued_to_name),
                 'issued_at', MAX(pi.issued_at),
                 'is_po_raised', bool_or(pi.status = 'PO Raised'),
                 'status',
                   CASE
                     WHEN bool_or(pi.status = 'PO Raised') THEN 'PO Raised'
                     ELSE 'PO Requested'
                   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.status IN ('PO Requested', 'PO Raised')
               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.status = 'PO Requested'
              AND pi.remaining_quantity > 0),
           0
         ) AS items_pending,

         COALESCE(
           (SELECT COUNT(DISTINCT pi.requisition_item_id)
            FROM pr_inventory pi
            WHERE pi.requisition_id = r.id
              AND pi.status = 'PO Raised'
              AND pi.remaining_quantity > 0),
           0
         ) AS items_raised

       FROM requisitions r
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.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.status IN ('PO Requested', 'PO Raised')
             AND pi.remaining_quantity > 0
         )
         AND NOT EXISTS (
           SELECT 1 FROM rfqs rfq
           WHERE rfq.pr_id = r.id
             AND rfq.is_deleted = false
         )
         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
       ORDER BY r.created_at DESC
       LIMIT $3 OFFSET $4`,
      [orgIdStr, searchTerm, parseInt(limit), offset]
    );

    const formattedData = result.rows.map(row => ({
      ...row,
      id: parseInt(row.id),
      requested_by: row.requested_by_name,
      items_pending: parseInt(row.items_pending) || 0,
      items_po_raised: parseInt(row.items_raised) || 0,
      rfq_sent: false,
      items: (row.items || []).map(item => ({
        ...item,
        quantity: parseFloat(item.quantity) || 0,
        issued_quantity: parseFloat(item.issued_quantity) || 0,
        remaining_quantity: parseFloat(item.remaining_quantity) || 0
      }))
    }));

    res.json({
      success: true,
      data: formattedData,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit),
      },
    });
  } 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 });
  }
};

// ==================== 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_terms
         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 addrLine2  = addrParts.slice(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 itemsRows = "";
    items.forEach((item, i) => {
      const qty   = Number(item.quantity)   || 0;
      const rate  = Number(item.unit_price) || 0;
      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">${escapeHtml(item.uom || "-")}</td>
          <td class="tc">${qty.toFixed(2)}</td>
          <td class="tr">${rate.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_ADDRESS_LINE2}}/g,  escapeHtml(addrLine2))
      .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(/{{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,
    });
  }
};

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