const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const PDFDocument = require('pdfkit');
const fs = require('fs');
const path = require('path');

/**
 * IMPORTANT:
 * VQR creation can generate multiple numbers in one request (one per vendor).
 * If we "query last + 1" per iteration outside the transaction, we can generate duplicates
 * and hit the unique constraint (company_id, vqr_number) -> rollback -> looks like "not working".
 *
 * To avoid that, allocate the next sequence ONCE inside the transaction (with a lock),
 * then increment in-memory for each new VQR.
 */
const getVQRNumberSeed = async (companyId, transaction) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `VQR${year}`;

  const lastVQR = await db.VendorQuotationRequest.findOne({
    where: {
      company_id: companyId,
      vqr_number: { [Op.like]: `${prefix}%` }
    },
    order: [['vqr_number', 'DESC']],
    transaction,
    lock: transaction ? transaction.LOCK.UPDATE : undefined
  });

  let nextSequence = 1;
  if (lastVQR?.vqr_number) {
    const lastSequence = parseInt(String(lastVQR.vqr_number).slice(-4), 10) || 0;
    nextSequence = lastSequence + 1;
  }

  return { prefix, nextSequence };
};

const formatVQRNumber = (prefix, sequence) => `${prefix}${String(sequence).padStart(4, '0')}`;

// Split RFQ by vendors and create VQRs
exports.createFromRFQ = asyncHandler(async (req, res) => {
  const { rfq_id } = req.body;
  const userId = req.user?.userId || null;

  if (!rfq_id) {
    return sendError(res, 'RFQ ID is required', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    // Fetch the RFQ with items
    const rfq = await db.RequestForQuotation.findByPk(rfq_id, {
      include: [
        {
          model: db.RequestForQuotationItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    if (!rfq) {
      await transaction.rollback();
      return sendError(res, 'RFQ not found', 404);
    }

    // Group items by vendor
    const itemsByVendor = {};
    rfq.items.forEach(item => {
      if (!item.vendor_id) return; // Skip items without vendor
      
      if (!itemsByVendor[item.vendor_id]) {
        itemsByVendor[item.vendor_id] = [];
      }
      itemsByVendor[item.vendor_id].push(item);
    });

    const vendorIdsSorted = Object.keys(itemsByVendor)
      .map((v) => parseInt(v, 10))
      .filter((v) => !Number.isNaN(v))
      .sort((a, b) => a - b);

    // Make this endpoint idempotent:
    // - Update/replace existing DRAFT VQRs per vendor (keep vqr_number)
    // - Create missing DRAFT VQRs
    // - Delete obsolete DRAFT VQRs (vendors removed from RFQ)
    // - Never touch VQRs that are already SENT/RESPONDED/CLOSED
    const existingVQRs = await db.VendorQuotationRequest.findAll({
      where: { rfq_id: rfq.id },
      transaction
    });

    // If there are multiple VQRs per vendor (data issue), prefer latest DRAFT for update;
    // otherwise use latest non-draft for skip behavior.
    const existingIndexByVendorId = new Map();
    existingVQRs.forEach((vqr) => {
      if (vqr.vendor_id == null) return;
      const vendorId = Number(vqr.vendor_id);
      if (Number.isNaN(vendorId)) return;

      const curr = existingIndexByVendorId.get(vendorId) || { draft: null, nonDraft: null };
      if (vqr.status === 'DRAFT') {
        if (!curr.draft || (vqr.id != null && curr.draft.id != null && vqr.id > curr.draft.id)) {
          curr.draft = vqr;
        } else if (!curr.draft) {
          curr.draft = vqr;
        }
      } else {
        if (!curr.nonDraft || (vqr.id != null && curr.nonDraft.id != null && vqr.id > curr.nonDraft.id)) {
          curr.nonDraft = vqr;
        } else if (!curr.nonDraft) {
          curr.nonDraft = vqr;
        }
      }
      existingIndexByVendorId.set(vendorId, curr);
    });

    const targetVendorIds = new Set(Object.keys(itemsByVendor).map((v) => Number(v)));

    const createdOrUpdatedVQRs = [];
    const skippedVQRs = [];

    // Delete obsolete draft VQRs (vendor removed from RFQ)
    const obsoleteDraftVQRs = existingVQRs.filter((vqr) => {
      const vendorId = vqr.vendor_id != null ? Number(vqr.vendor_id) : null;
      return vqr.status === 'DRAFT' && vendorId != null && !targetVendorIds.has(vendorId);
    });

    if (obsoleteDraftVQRs.length > 0) {
      const obsoleteIds = obsoleteDraftVQRs.map((vqr) => vqr.id);
      await db.VendorQuotationRequestItem.destroy({
        where: { vqr_id: { [Op.in]: obsoleteIds } },
        transaction
      });
      await db.VendorQuotationRequest.destroy({
        where: { id: { [Op.in]: obsoleteIds } },
        transaction
      });
    }

    // Seed numbering ONCE so creating multiple VQRs in this transaction never reuses the same number.
    // (Existing DRAFT VQRs keep their number; we only consume numbers for newly created VQRs.)
    const { prefix, nextSequence: initialSequence } = await getVQRNumberSeed(rfq.company_id, transaction);
    let nextSequence = initialSequence;

    // Create/update VQR per vendor
    for (const vendorIdNum of vendorIdsSorted) {
      const items = itemsByVendor[vendorIdNum] || [];
      const existingIndex = existingIndexByVendorId.get(vendorIdNum);
      const existing = existingIndex?.draft || existingIndex?.nonDraft || null;

      let vqr = null;
      let vqrNumber = null;

      if (existing && existing.status !== 'DRAFT') {
        skippedVQRs.push({
          id: existing.id,
          vqr_number: existing.vqr_number,
          vendor_id: vendorIdNum,
          status: existing.status,
          reason: 'Existing VQR is not DRAFT'
        });
        continue;
      }

      if (existing && existing.status === 'DRAFT') {
        vqr = existing;
        vqrNumber = existing.vqr_number;

        await vqr.update({
          company_id: rfq.company_id,
          plant_id: rfq.plant_id,
          fiscal_year_id: rfq.fiscal_year_id,
          rfq_id: rfq.id,
          vendor_id: vendorIdNum,
          vqr_date: rfq.rfq_date,
          valid_until: rfq.valid_until,
          currency_id: rfq.currency_id,
          payment_terms: rfq.payment_terms,
          delivery_terms: rfq.delivery_terms,
          remarks: rfq.remarks,
          updated_by: userId
        }, { transaction });

        await db.VendorQuotationRequestItem.destroy({
          where: { vqr_id: vqr.id },
          transaction
        });
      } else {
        vqrNumber = formatVQRNumber(prefix, nextSequence);
        nextSequence += 1;
        vqr = await db.VendorQuotationRequest.create({
          company_id: rfq.company_id,
          plant_id: rfq.plant_id,
          fiscal_year_id: rfq.fiscal_year_id,
          rfq_id: rfq.id,
          vendor_id: vendorIdNum,
          vqr_number: vqrNumber,
          vqr_date: rfq.rfq_date,
          valid_until: rfq.valid_until,
          currency_id: rfq.currency_id,
          payment_terms: rfq.payment_terms,
          delivery_terms: rfq.delivery_terms,
          remarks: rfq.remarks,
          status: 'DRAFT',
          created_by: userId,
          updated_by: userId
        }, { transaction });
      }

      const vqrItems = items.map((item, index) => ({
        vqr_id: vqr.id,
        rfq_item_id: item.id,
        line_number: index + 1,
        material_id: item.material_id,
        description: item.description,
        quantity: item.quantity,
        uom_id: item.uom_id,
        remarks: item.remarks
      }));

      await db.VendorQuotationRequestItem.bulkCreate(vqrItems, { transaction });

      createdOrUpdatedVQRs.push({
        id: vqr.id,
        vqr_number: vqrNumber,
        vendor_id: vendorIdNum,
        item_count: items.length
      });
    }

    await transaction.commit();

    sendResponse(res, 201, true, `Created/updated ${createdOrUpdatedVQRs.length} vendor quotation request(s)`, {
      vqrs: createdOrUpdatedVQRs,
      total: createdOrUpdatedVQRs.length,
      skipped: skippedVQRs,
      deleted_obsolete_draft: obsoleteDraftVQRs.length
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error creating VQRs from RFQ:', error);
    throw error;
  }
});

// Get all VQRs
exports.getAll = asyncHandler(async (req, res) => {
  const {
    page = 1,
    limit = 25,
    search,
    status,
    rfq_id,
    vendor_id,
    companyId,
    plantId,
    fiscalYearId,
    fromDate,
    toDate,
    overdue
  } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {};
  
  if (companyId) {
    where.company_id = companyId;
  } else if (req.user && !req.user.isSuperAdmin) {
    where.company_id = req.user.companyId;
  }

  if (plantId) {
    where.plant_id = plantId;
  }

  if (fiscalYearId) {
    where.fiscal_year_id = fiscalYearId;
  }

  if (status) {
    where.status = status;
  }

  if (rfq_id) {
    where.rfq_id = rfq_id;
  }

  if (vendor_id) {
    where.vendor_id = vendor_id;
  }

  if (fromDate || toDate) {
    where.vqr_date = {
      ...(fromDate ? { [Op.gte]: fromDate } : {}),
      ...(toDate ? { [Op.lte]: toDate } : {})
    };
  }

  const overdueBool = String(overdue).toLowerCase() === 'true';
  if (overdueBool) {
    // Overdue is defined as valid_until in the past and not yet closed/responded.
    // (If the caller also passes status, we respect it; otherwise we default to DRAFT/SENT.)
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    where.valid_until = { [Op.lt]: today };

    if (!status) {
      where.status = { [Op.in]: ['DRAFT', 'SENT'] };
    }
  }

  if (search) {
    where[Op.or] = [
      { vqr_number: { [Op.like]: `%${search}%` } },
      { '$rfq.rfq_number$': { [Op.like]: `%${search}%` } },
      { '$vendor.bp_number$': { [Op.like]: `%${search}%` } },
      { '$vendor.name$': { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.VendorQuotationRequest.findAndCountAll({
    where,
    // With includes + search-on-include fields, Sequelize's count() can generate bad aliases
    // if `col` is specified (e.g. VendorQuotationRequest->VendorQuotationRequest.id).
    // Let Sequelize count DISTINCT on the model PK safely.
    distinct: true,
    subQuery: false,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.RequestForQuotation, as: 'rfq', attributes: ['id', 'rfq_number'], required: false },
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name'], required: false },
      {
        model: db.VendorQuotationRequestItem,
        as: 'items',
        attributes: ['id'],
        separate: true
      }
    ],
    limit: limitNum,
    offset,
    order: [['vqr_date', 'DESC'], ['id', 'DESC']]
  });

  // Add item count to each record
  const rowsWithCount = records.rows.map(vqr => {
    const vqrJson = vqr.toJSON();
    vqrJson.item_count = vqrJson.items ? vqrJson.items.length : 0;
    delete vqrJson.items; // Remove items array from list view
    return vqrJson;
  });

  const response = getPagingData({ count: records.count, rows: rowsWithCount }, page, limitNum);
  sendResponse(res, 200, true, 'Vendor Quotation Requests retrieved successfully', response);
});

// Get VQR by ID
exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const vqr = await db.VendorQuotationRequest.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.RequestForQuotation, as: 'rfq' },
      { model: db.BusinessPartner, as: 'vendor' },
      { model: db.Currency, as: 'currency' },
      {
        model: db.VendorQuotationRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

  if (!vqr) {
    return sendError(res, 'Vendor Quotation Request not found', 404);
  }

  sendResponse(res, 200, true, 'Vendor Quotation Request retrieved successfully', vqr);
});

// Generate PDF for VQR
exports.generatePDF = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const vqr = await db.VendorQuotationRequest.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.BusinessPartner, as: 'vendor' },
      { model: db.Currency, as: 'currency' },
      { model: db.RequestForQuotation, as: 'rfq' },
      {
        model: db.VendorQuotationRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

  if (!vqr) {
    return sendError(res, 'Vendor Quotation Request not found', 404);
  }

  // Fetch print header
  const printHeader = await db.PrintHeader.findOne({
    where: { company_id: vqr.company_id, status: 'ACTIVE' }
  });

  // Create PDF
  const doc = new PDFDocument({ margin: 50, size: 'A4' });
  const vendorCode = vqr.vendor?.bp_number || 'vendor';
  const vendorName = vqr.vendor?.name || '';
  const filename = `VQR_${vqr.vqr_number}_${vendorCode}.pdf`;
  
  res.setHeader('Content-Type', 'application/pdf');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  
  doc.pipe(res);

  // Constants
  const pageWidth = doc.page.width;
  const pageHeight = doc.page.height;
  const margin = 50;
  const contentWidth = pageWidth - (margin * 2);
  let pageNumber = 1;

  // Helper function to format date
  const formatDate = (dateString) => {
    if (!dateString) return '';
    try {
      const date = new Date(dateString);
      // Check if date is valid
      if (isNaN(date.getTime())) return '';
      const day = String(date.getDate()).padStart(2, '0');
      const month = String(date.getMonth() + 1).padStart(2, '0');
      const year = date.getFullYear();
      return `${day}/${month}/${year}`;
    } catch (e) {
      return '';
    }
  };

  // Helper function to add logo
  const addLogo = (logoPath, x, y, maxWidth = 80) => {
    if (logoPath && printHeader) {
      try {
        const logoFullPath = path.join(__dirname, '../uploads', logoPath);
        if (fs.existsSync(logoFullPath)) {
          doc.image(logoFullPath, x, y, { width: maxWidth, fit: [maxWidth, 100] });
          return true;
        }
      } catch (err) {
        console.error('Error loading logo:', err);
      }
    }
    return false;
  };

  // Function to add header on each page
  const addPageHeader = () => {
    let yPos = 50;
    
    // Logo (left side)
    addLogo(printHeader?.logo_path, margin, yPos, 80);

    // Company Name (centered)
    doc.fontSize(16).font('Helvetica-Bold');
    const companyName = printHeader?.company_name || vqr.company?.name || 'Company Name';
    doc.text(companyName, margin, yPos, { align: 'center', width: contentWidth });
    yPos += 20;

    if (printHeader?.former_name) {
      doc.fontSize(10).font('Helvetica');
      doc.text(`(Formerly Known as ${printHeader.former_name})`, margin, yPos, { align: 'center', width: contentWidth });
      yPos += 15;
    }

    // Address (centered)
    doc.fontSize(9).font('Helvetica');
    const address = printHeader?.address || vqr.company?.address || '';
    if (address) {
      const addressLines = address.split('\n').filter(line => line.trim());
      addressLines.forEach(line => {
        doc.text(line.trim(), margin, yPos, { align: 'center', width: contentWidth });
        yPos += 12;
      });
    }

    // Add GSTIN to address if available
    if (printHeader?.gstin) {
      doc.fontSize(9).font('Helvetica');
      doc.text(`GSTIN: ${printHeader.gstin}`, margin, yPos, { align: 'center', width: contentWidth });
      yPos += 12;
    }

    // Contact Information (centered)
    const contactInfo = [];
    if (printHeader?.phone) contactInfo.push(`Phone: ${printHeader.phone}`);
    if (printHeader?.fax) contactInfo.push(`Fax: ${printHeader.fax}`);
    if (printHeader?.email) contactInfo.push(`E-mail: ${printHeader.email}`);
    if (printHeader?.website) contactInfo.push(`Webpage: ${printHeader.website}`);

    if (contactInfo.length > 0) {
      yPos += 5;
      contactInfo.forEach(info => {
        doc.text(info, margin, yPos, { align: 'center', width: contentWidth });
        yPos += 12;
      });
    }

    // Draw line after header
    yPos += 10;
    doc.moveTo(margin, yPos).lineTo(pageWidth - margin, yPos).stroke();
    yPos += 20;

    // Title: VENDOR QUOTATION REQUEST
    doc.fontSize(14).font('Helvetica-Bold');
    doc.text('VENDOR QUOTATION REQUEST', margin, yPos, { align: 'center', width: contentWidth });
    yPos += 25;

    return yPos;
  };

  // Function to add VQR details
  const addVQRDetails = (yStart) => {
    let yPos = yStart;

    // Two-column layout: VQR Info (left) and Vendor Details (right)
    const leftColumnX = margin;
    const rightColumnX = pageWidth / 2 + 20;
    const columnWidth = (pageWidth - margin * 2) / 2 - 20;

    // Left Column: VQR Information
    doc.fontSize(9).font('Helvetica-Bold');
    doc.text('VQR Information:', leftColumnX, yPos);
    yPos += 12;

    doc.fontSize(9).font('Helvetica');
    doc.text(`VQR Number: ${vqr.vqr_number}`, leftColumnX, yPos);
    yPos += 12;
    doc.text(`VQR Date: ${formatDate(vqr.vqr_date)}`, leftColumnX, yPos);
    yPos += 12;
    
    // Only show Valid Until if it has a valid date
    const validUntilDate = formatDate(vqr.valid_until);
    if (validUntilDate) {
      doc.text(`Valid Until: ${validUntilDate}`, leftColumnX, yPos);
      yPos += 12;
    }
    
    if (vqr.rfq?.rfq_number) {
      doc.text(`RFQ Number: ${vqr.rfq.rfq_number}`, leftColumnX, yPos);
      yPos += 12;
    }

    // Right Column: Vendor Details
    let rightY = yStart;
    doc.fontSize(9).font('Helvetica-Bold');
    doc.text('Vendor Details:', rightColumnX, rightY);
    rightY += 12;

    doc.fontSize(9).font('Helvetica');
    doc.text(`Vendor Code: ${vendorCode}`, rightColumnX, rightY);
    rightY += 12;
    doc.text(`Vendor Name: ${vendorName || 'N/A'}`, rightColumnX, rightY, { width: columnWidth });
    rightY += 12;
    
    if (vqr.vendor?.contact_person) {
      doc.text(`Contact: ${vqr.vendor.contact_person}`, rightColumnX, rightY, { width: columnWidth });
      rightY += 12;
    }
    if (vqr.vendor?.phone) {
      doc.text(`Phone: ${vqr.vendor.phone}`, rightColumnX, rightY);
      rightY += 12;
    }
    if (vqr.vendor?.email) {
      doc.text(`Email: ${vqr.vendor.email}`, rightColumnX, rightY, { width: columnWidth });
      rightY += 12;
    }

    return Math.max(yPos, rightY) + 15;
  };

  // Function to add commercial terms
  const addCommercialTerms = (yStart) => {
    let yPos = yStart;

    doc.fontSize(9).font('Helvetica-Bold');
    doc.text('Commercial Terms:', margin, yPos);
    yPos += 12;

    doc.fontSize(9).font('Helvetica');
    const currencyCode = vqr.currency?.currency_code || vqr.currency?.code || 'N/A';
    doc.text(`Currency: ${currencyCode}`, margin, yPos);
    yPos += 12;
    doc.text(`Payment Terms: ${vqr.payment_terms || 'As per agreement'}`, margin, yPos);
    yPos += 12;
    doc.text(`Delivery Terms: ${vqr.delivery_terms || 'As per agreement'}`, margin, yPos);
    yPos += 12;

    if (vqr.remarks) {
      doc.text(`Remarks: ${vqr.remarks}`, margin, yPos, { width: contentWidth });
      yPos += 20;
    }

    return yPos + 10;
  };

  // Function to add table headers
  const addTableHeaders = (yStart) => {
    let yPos = yStart;
    
    // Draw line before table
    doc.moveTo(margin, yPos).lineTo(pageWidth - margin, yPos).stroke();
    yPos += 10;

    doc.fontSize(9).font('Helvetica-Bold');
    
    // Define column widths (removed remarks column)
    const colWidths = {
      sno: 50,
      materialCode: 120,
      description: 0, // Will be calculated to fill remaining space
      quantity: 80,
      uom: 70
    };
    
    // Calculate description width to fill remaining space
    const usedWidth = colWidths.sno + colWidths.materialCode + colWidths.quantity + colWidths.uom;
    colWidths.description = contentWidth - usedWidth;

    let xPos = margin;
    
    // Print column headers
    doc.text('S.No.', xPos, yPos, { width: colWidths.sno });
    xPos += colWidths.sno;
    doc.text('Material Code', xPos, yPos, { width: colWidths.materialCode });
    xPos += colWidths.materialCode;
    doc.text('Description', xPos, yPos, { width: colWidths.description });
    xPos += colWidths.description;
    doc.text('Quantity', xPos, yPos, { width: colWidths.quantity, align: 'right' });
    xPos += colWidths.quantity;
    doc.text('UOM', xPos, yPos, { width: colWidths.uom, align: 'center' });

    yPos += 15;
    
    // Draw header line
    doc.lineWidth(1.5);
    doc.moveTo(margin, yPos).lineTo(pageWidth - margin, yPos).stroke();
    doc.lineWidth(1);
    
    yPos += 10;
    
    // Reset font for data rows
    doc.fontSize(8).font('Helvetica');

    return { yPos, colWidths };
  };

  // Function to add page footer
  const addPageFooter = () => {
    const footerY = pageHeight - 60;
    
    // Draw line
    doc.lineWidth(1);
    doc.moveTo(margin, footerY).lineTo(pageWidth - margin, footerY).stroke();
    
    // Add company name and authorized signature
    doc.fontSize(8).font('Helvetica');
    doc.text(`For ${printHeader?.company_name || vqr.company?.name || 'Company Name'}`, margin, footerY + 10);
    doc.text('Authorised Signatory', pageWidth - margin - 100, footerY + 10, { width: 100, align: 'right' });
    
    // Add page number
    doc.text(`Page ${pageNumber}`, margin, footerY + 30, { align: 'center', width: contentWidth });
  };

  // Start PDF generation
  let yPosition = addPageHeader();
  yPosition = addVQRDetails(yPosition);
  yPosition = addCommercialTerms(yPosition);

  // Add table headers
  const { yPos: tableYPos, colWidths } = addTableHeaders(yPosition);
  yPosition = tableYPos;

  // Render items
  const rowHeight = 20;
  const bottomMargin = 100;
  const formatQty = (qty) => {
    const num = parseFloat(qty || 0);
    return isFinite(num) ? num.toFixed(3) : '0.000';
  };

  if (vqr.items && vqr.items.length > 0) {
    vqr.items.forEach((item, index) => {
      // Check if we need a new page
      if (yPosition + rowHeight > pageHeight - bottomMargin) {
        addPageFooter();
        doc.addPage();
        pageNumber++;
        yPosition = addPageHeader();
        
        // Add table headers on new page
        const { yPos: newTableYPos } = addTableHeaders(yPosition);
        yPosition = newTableYPos;
      }

      let xPos = margin;
      
      // S.No.
      doc.text((index + 1).toString(), xPos, yPosition, { width: colWidths.sno });
      xPos += colWidths.sno;
      
      // Material Code
      const materialCode = item.material?.material_number || item.material?.code || '-';
      doc.text(materialCode, xPos, yPosition, { width: colWidths.materialCode });
      xPos += colWidths.materialCode;
      
      // Description
      const materialDesc = item.material?.description || '';
      const desc = item.description || materialDesc || '-';
      doc.text(desc, xPos, yPosition, { width: colWidths.description });
      xPos += colWidths.description;
      
      // Quantity
      doc.text(formatQty(item.quantity), xPos, yPosition, { width: colWidths.quantity, align: 'right' });
      xPos += colWidths.quantity;
      
      // UOM
      const uomCode = item.uom?.uom_code || item.uom?.code || '-';
      doc.text(uomCode, xPos, yPosition, { width: colWidths.uom, align: 'center' });
      
      yPosition += rowHeight;
    });
  } else {
    doc.text('No items found.', margin, yPosition);
    yPosition += rowHeight;
  }

  // Draw closing line for table
  doc.moveTo(margin, yPosition).lineTo(pageWidth - margin, yPosition).stroke();
  yPosition += 15;

  // Add vendor response section (space for vendor to fill)
  if (yPosition + 60 > pageHeight - bottomMargin) {
    addPageFooter();
    doc.addPage();
    pageNumber++;
    yPosition = addPageHeader();
  }

  doc.fontSize(9).font('Helvetica-Bold');
  doc.text('For Vendor Response:', margin, yPosition);
  yPosition += 15;
  
  doc.fontSize(8).font('Helvetica');
  doc.text('Vendor Signature: _____________________', margin, yPosition);
  doc.text('Date: _____________________', pageWidth - margin - 150, yPosition);
  yPosition += 20;
  doc.text('Stamp: ', margin, yPosition);

  // Add footer to last page
  addPageFooter();

  doc.end();
});

// Mark VQR as sent
exports.markAsSent = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const vqr = await db.VendorQuotationRequest.findByPk(id);
  if (!vqr) {
    return sendError(res, 'Vendor Quotation Request not found', 404);
  }

  await vqr.update({
    status: 'SENT',
    sent_at: new Date(),
    sent_by: userId,
    updated_by: userId
  });

  sendResponse(res, 200, true, 'VQR marked as sent', vqr);
});

module.exports = exports;

