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 { generateNextNumber } = require('../services/numberFormatService');
const PDFDocument = require('pdfkit');
const fs = require('fs');
const path = require('path');

const generateCQNumber = async (companyId) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `CQ${year}`;
  
  const lastCQ = await db.CustomerQuotation.findOne({
    where: {
      company_id: companyId,
      cq_number: { [Op.like]: `${prefix}%` }
    },
    order: [['cq_number', 'DESC']]
  });

  let sequence = 1;
  if (lastCQ) {
    const lastSequence = parseInt(lastCQ.cq_number.slice(-4)) || 0;
    sequence = lastSequence + 1;
  }

  return `${prefix}${sequence.toString().padStart(4, '0')}`;
};

const generateCustomerQuotationNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'CUSTOMER_QUOTATION', fiscalYearId, transaction);
  } catch (error) {
    console.warn('Customer Quotation number format missing, using fallback:', error.message);
    return generateCQNumber(companyId);
  }
};

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId, customerId } = 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 (customerId) {
    where.customer_id = customerId;
  }

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

  if (search) {
    where[Op.or] = [
      { cq_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.CustomerQuotation.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] }
    ],
    limit: limitNum,
    offset,
    order: [['cq_date', 'DESC'], ['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'Customer Quotations retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const cq = await db.CustomerQuotation.findByPk(id, {
    include: [
      { 
        model: db.Company, 
        as: 'company',
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
        ]
      },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.BusinessPartner, as: 'customer' },
      { 
        model: db.BPAddress, 
        as: 'customerAddress',
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
        ]
      },
      { model: db.Currency, as: 'currency' },
      {
        model: db.CustomerQuotationItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['item_number', 'ASC']]
      }
    ]
  });

  if (!cq) {
    return sendError(res, 'Customer Quotation not found', 404);
  }

  sendResponse(res, 200, true, 'Customer Quotation retrieved successfully', cq);
});

// Generate PDF for Customer Quotation - aligned to PO layout style
exports.generatePDF = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const cq = await db.CustomerQuotation.findByPk(id, {
    include: [
      {
        model: db.Company,
        as: 'company',
        attributes: ['id', 'code', 'name', 'legal_name', 'email', 'phone'],
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code', 'state_name'] }
        ]
      },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name', 'email', 'phone'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      {
        model: db.BPAddress,
        as: 'customerAddress',
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'state_name', 'gst_state_code', 'state_code'] },
          { model: db.City, as: 'city', attributes: ['id', 'city_name'] }
        ]
      },
      { model: db.Currency, as: 'currency', attributes: ['id', 'currency_code', 'currency_symbol'] },
      { model: db.User, as: 'creator', attributes: ['id', 'username', 'first_name', 'last_name'] },
      {
        model: db.CustomerQuotationItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
        ]
      }
    ],
    order: [[{ model: db.CustomerQuotationItem, as: 'items' }, 'item_number', 'ASC']]
  });

  if (!cq) {
    return sendError(res, 'Customer Quotation not found', 404);
  }

  const customerGSTIN = await db.BPGSTIN.findOne({
    where: { bp_id: cq.customer_id, status: 'ACTIVE' },
    include: [{ model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }],
    order: [['is_default', 'DESC'], ['id', 'ASC']]
  });

  const printHeader = await db.PrintHeader.findOne({
    where: { company_id: cq.company_id, status: 'ACTIVE' },
    order: [['id', 'DESC']]
  });

  let companyLogoPath = null;
  if (printHeader?.logo_path) {
    let fullPath = path.join(__dirname, '..', 'uploads', printHeader.logo_path);
    if (fs.existsSync(fullPath)) {
      companyLogoPath = fullPath;
    } else {
      fullPath = path.join(__dirname, '..', printHeader.logo_path);
      if (fs.existsSync(fullPath)) companyLogoPath = fullPath;
    }
  }

  let certLogoPath = null;
  if (printHeader?.quality_certification_logo_path) {
    let fullPath = path.join(__dirname, '..', 'uploads', printHeader.quality_certification_logo_path);
    if (fs.existsSync(fullPath)) {
      certLogoPath = fullPath;
    } else {
      fullPath = path.join(__dirname, '..', printHeader.quality_certification_logo_path);
      if (fs.existsSync(fullPath)) certLogoPath = fullPath;
    }
  }

  const normalizeStateCode = (value) => {
    const raw = String(value || '').trim();
    if (!raw) return '';
    return raw.padStart(2, '0').slice(-2);
  };

  const companyStateCode = normalizeStateCode(cq.company?.state?.gst_state_code || cq.company?.state?.state_code);
  const customerStateCode = normalizeStateCode(
    cq.customerAddress?.state?.gst_state_code
      || customerGSTIN?.state?.gst_state_code
      || cq.customerAddress?.state?.state_code
      || customerGSTIN?.state?.state_code
  );
  const isInterStateSupply = Boolean(companyStateCode && customerStateCode && companyStateCode !== customerStateCode);

  const gstSummary = (cq.items || []).reduce((acc, item) => {
    acc.taxable += parseFloat(item.net_amount || 0) || 0;
    acc.cgst += parseFloat(item.cgst_amount || 0) || 0;
    acc.sgst += parseFloat(item.sgst_amount || 0) || 0;
    acc.igst += parseFloat(item.igst_amount || 0) || 0;
    return acc;
  }, { taxable: 0, cgst: 0, sgst: 0, igst: 0 });

  const formatDate = (dateString) => {
    if (!dateString) return '';
    const d = new Date(dateString);
    if (Number.isNaN(d.getTime())) return '';
    const day = String(d.getDate()).padStart(2, '0');
    const month = String(d.getMonth() + 1).padStart(2, '0');
    const year = d.getFullYear();
    return `${day}.${month}.${year}`;
  };

  const formatCurrency = (value) => {
    if (!value) return '0.00';
    return (parseFloat(value) || 0).toFixed(2);
  };

  const sanitizeForFilename = (value) => String(value || '')
    .trim()
    .replace(/[<>:"/\\|?*\x00-\x1F]/g, '')
    .replace(/\s+/g, '_')
    .replace(/_+/g, '_')
    .replace(/^_+|_+$/g, '');

  const doc = new PDFDocument({ margin: 30, size: 'A4', bufferPages: true });
  const cqNumberPart = sanitizeForFilename(cq.cq_number || cq.id);
  const customerNamePart = sanitizeForFilename(cq.customer?.name || cq.customer?.legal_name || 'Customer');
  const filename = `CQ_${cqNumberPart}_${customerNamePart}.pdf`;

  res.setHeader('Content-Type', 'application/pdf');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  doc.pipe(res);

  const pageWidth = 595.28;
  const pageHeight = 841.89;
  const margin = 30;
  const contentWidth = pageWidth - (margin * 2);
  const footerMetaHeight = 70;
  const footerMetaY = pageHeight - margin - footerMetaHeight;
  const footerSpecialNoteHeight = 80;
  const footerSpecialNoteY = footerMetaY - footerSpecialNoteHeight;

  const drawBorder = (x, y, width, height) => {
    doc.rect(x, y, width, height).stroke('#000000');
  };

  const drawPageOutline = () => {
    doc.rect(margin, margin, contentWidth, pageHeight - (margin * 2)).stroke('#000000');
  };

  function drawHeader(y) {
    const headerHeight = 112;
    const sectionPadding = 3;
    const leftBlockW = Math.min(170, contentWidth * 0.31);
    const rightBlockW = Math.min(130, contentWidth * 0.24);
    const centerX = margin + leftBlockW;
    const centerWidth = contentWidth - leftBlockW - rightBlockW;
    const rightX = centerX + centerWidth;

    drawBorder(margin, y, contentWidth, headerHeight);

    const logoX = margin + sectionPadding;
    const logoY = y + sectionPadding;
    const logoW = leftBlockW - (sectionPadding * 2);
    const logoH = headerHeight - (sectionPadding * 2);
    if (companyLogoPath) {
      try {
        doc.image(companyLogoPath, logoX, logoY, {
          fit: [logoW, logoH],
          align: 'center',
          valign: 'center'
        });
      } catch (error) {
        console.error('Logo error:', error);
      }
    }

    const certX = rightX + sectionPadding;
    const certY = y + sectionPadding;
    const certW = rightBlockW - (sectionPadding * 2);
    const certH = headerHeight - (sectionPadding * 2);
    if (certLogoPath) {
      try {
        doc.image(certLogoPath, certX, certY, {
          fit: [certW, certH],
          align: 'center',
          valign: 'center'
        });
      } catch (error) {
        console.error('Cert logo error:', error);
      }
    }

    const companyName = printHeader?.company_name || cq.company?.name || 'Company Name';
    const formerName = printHeader?.former_name ? `(Formerly Known as ${printHeader.former_name})` : '';
    const addressLine = String(printHeader?.address || '')
      .replace(/\s*\r?\n\s*/g, ', ')
      .replace(/\s+/g, ' ')
      .trim();
    const headerEmail = printHeader?.email || cq.company?.email || cq.plant?.email || '';
    const headerPhone = printHeader?.phone || cq.company?.phone || cq.plant?.phone || '';
    const gstin = printHeader?.gstin || '';
    const website = printHeader?.website || '';

    const infoLine1 = [headerEmail ? `Email : ${headerEmail}` : '', headerPhone ? `Contact : ${headerPhone}` : '']
      .filter(Boolean)
      .join('    |    ');

    const centerLines = [
      { text: companyName, font: 'Helvetica-Bold', size: 19 },
      formerName ? { text: formerName, font: 'Helvetica-Oblique', size: 9.2 } : null,
      addressLine ? { text: addressLine, font: 'Helvetica', size: 9 } : null,
      infoLine1 ? { text: infoLine1, font: 'Helvetica', size: 8.8 } : null,
      gstin ? { text: `GSTIN : ${gstin}`, font: 'Helvetica', size: 8.8 } : null,
      website ? { text: `Website : ${website}`, font: 'Helvetica', size: 8.2 } : null
    ].filter(Boolean);

    const lineGap = 2;
    const textHeights = centerLines.map((line) => line.size + lineGap);
    const totalTextHeight = textHeights.reduce((acc, h) => acc + h, 0);
    let textY = y + Math.max(8, (headerHeight - totalTextHeight) / 2);

    centerLines.forEach((line, idx) => {
      let fontSize = line.size;
      const minFontSize = idx === 0 ? 12 : 6.4;
      doc.font(line.font);
      while (fontSize > minFontSize) {
        doc.fontSize(fontSize);
        if (doc.widthOfString(line.text) <= (centerWidth - 6)) break;
        fontSize -= 0.2;
      }
      doc.text(line.text, centerX + 3, textY, {
        width: centerWidth - 6,
        align: 'center',
        lineBreak: false
      });
      textY += fontSize + lineGap;
    });

    return y + headerHeight;
  }

  function drawTitle(y) {
    const titleHeight = 20;
    drawBorder(margin, y, contentWidth, titleHeight);
    doc.fontSize(12).font('Helvetica-Bold');
    doc.text('CUSTOMER QUOTATION', margin, y + 6, { width: contentWidth, align: 'center' });
    return y + titleHeight;
  }

  function drawDetailsSection(y) {
    const sectionHeight = 100;
    const leftWidth = contentWidth * 0.5;
    const rightWidth = contentWidth * 0.5;
    const rightX = margin + leftWidth;

    drawBorder(margin, y, leftWidth, sectionHeight);
    drawBorder(rightX, y, rightWidth, sectionHeight);

    let leftY = y + 5;
    doc.fontSize(8).font('Helvetica');
    doc.text('To,', margin + 5, leftY);
    leftY += 10;

    doc.fontSize(8).font('Helvetica-Bold');
    const customerName = cq.customer?.name || cq.customer?.legal_name || 'Customer';
    doc.text(customerName.toUpperCase(), margin + 5, leftY, { width: leftWidth - 10 });
    leftY += 10;

    const drawLeftKV = (label, value) => {
      if (!value) return;
      const labelW = 45;
      const colonW = 8;
      const x = margin + 5;
      const valueW = leftWidth - 10 - labelW - colonW;
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(label, x, leftY, { width: labelW });
      doc.text(':', x + labelW, leftY, { width: colonW });
      doc.fontSize(7).font('Helvetica');
      doc.text(String(value), x + labelW + colonW, leftY, { width: valueW });
      leftY += 9;
    };

    const drawLeftMultiLineKV = (label, lines) => {
      const safeLines = (lines || []).map((l) => String(l || '').trim()).filter(Boolean);
      if (safeLines.length === 0) return;
      const labelW = 45;
      const colonW = 8;
      const x = margin + 5;
      const valueX = x + labelW + colonW;
      const valueW = leftWidth - 10 - labelW - colonW;

      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(label, x, leftY, { width: labelW });
      doc.text(':', x + labelW, leftY, { width: colonW });
      doc.fontSize(7).font('Helvetica');
      doc.text(safeLines[0], valueX, leftY, { width: valueW });
      leftY += 9;

      for (let i = 1; i < safeLines.length; i += 1) {
        doc.text(safeLines[i], valueX, leftY, { width: valueW });
        leftY += 9;
      }
    };

    const addressLines = [];
    if (cq.customerAddress?.address_line1) addressLines.push(cq.customerAddress.address_line1);
    if (cq.customerAddress?.address_line2) addressLines.push(cq.customerAddress.address_line2);
    const cityBits = [];
    if (cq.customerAddress?.city?.city_name) cityBits.push(cq.customerAddress.city.city_name);
    if (cq.customerAddress?.pincode) cityBits.push(cq.customerAddress.pincode);
    if (cityBits.length > 0) addressLines.push(cityBits.join(' - '));
    drawLeftMultiLineKV('Address', addressLines);
    drawLeftKV('GSTIN', customerGSTIN?.gstin || '');
    drawLeftKV('State', cq.customerAddress?.state?.state_name || '');
    drawLeftKV('State Code', customerStateCode || '');

    const handledBy = cq.creator
      ? `${cq.creator.first_name || ''} ${cq.creator.last_name || ''}`.trim() || cq.creator.username || ''
      : '';
    const details = [
      { label: 'CQ No', value: cq.cq_number || '' },
      { label: 'Date', value: formatDate(cq.cq_date) },
      { label: 'Valid Until', value: formatDate(cq.valid_until) },
      { label: 'Handled By', value: handledBy },
      { label: 'Currency', value: cq.currency?.currency_code || 'INR' }
    ];

    let rightY = y + 5;
    const labelW = 62;
    const colonW = 8;
    const valueW = rightWidth - labelW - colonW - 15;

    details.forEach(item => {
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(item.label, rightX + 5, rightY, { width: labelW });
      doc.text(':', rightX + labelW + 5, rightY, { width: colonW });
      doc.fontSize(7).font('Helvetica');
      doc.text(item.value, rightX + labelW + colonW + 5, rightY, { width: valueW });
      rightY += 10;
    });

    doc.fontSize(7).font('Helvetica-Bold');
    doc.text('Supply Type', rightX + 5, rightY, { width: labelW });
    doc.text(':', rightX + labelW + 5, rightY, { width: colonW });
    doc.fontSize(7).font('Helvetica');
    doc.text(isInterStateSupply ? 'IGST (Inter-State)' : 'CGST + SGST (Intra-State)', rightX + labelW + colonW + 5, rightY, { width: valueW });

    return y + sectionHeight;
  }

  function drawMetadataRow(y) {
    const rowHeight = 42;
    drawBorder(margin, y, contentWidth, rowHeight);

    const colWidth = contentWidth / 4;
    for (let i = 1; i < 4; i += 1) {
      doc.moveTo(margin + (colWidth * i), y).lineTo(margin + (colWidth * i), y + rowHeight).stroke();
    }

    const currencyCode = cq.currency?.currency_code || 'INR';
    const taxableAmount = gstSummary.taxable || 0;
    const grandTotal = parseFloat(cq.total_amount || 0) || 0;
    const cgstAmount = gstSummary.cgst || 0;
    const sgstAmount = gstSummary.sgst || 0;
    const igstAmount = gstSummary.igst || 0;
    const cgstRate = taxableAmount > 0 ? (cgstAmount / taxableAmount) * 100 : 0;
    const sgstRate = taxableAmount > 0 ? (sgstAmount / taxableAmount) * 100 : 0;
    const igstRate = taxableAmount > 0 ? (igstAmount / taxableAmount) * 100 : 0;

    doc.fontSize(7).font('Helvetica');
    doc.text('Customer code:', margin + 3, y + 5, { continued: true }).font('Helvetica-Bold').text(` ${cq.customer?.bp_number || ''}`);
    doc.font('Helvetica').text('Quotation Type:', margin + colWidth + 3, y + 5, { continued: true }).font('Helvetica-Bold').text(' CUSTOMER');
    doc.font('Helvetica').text('Dur CIN:', margin + (colWidth * 2) + 3, y + 5, { continued: true }).font('Helvetica-Bold').text(` ${currencyCode}`);

    const valueColX = margin + (colWidth * 3) + 3;
    const valueColW = colWidth - 6;
    doc.font('Helvetica').text(`Taxable : ${formatCurrency(taxableAmount)} ${currencyCode}`, valueColX, y + 5, { width: valueColW, align: 'left' });
    if (isInterStateSupply) {
      doc.text(`IGST ${formatCurrency(igstRate)}% : ${formatCurrency(igstAmount)} ${currencyCode}`, valueColX, y + 14, { width: valueColW, align: 'left' });
      doc.font('Helvetica-Bold').text(`Value : ${formatCurrency(grandTotal)} ${currencyCode}`, valueColX, y + 23, { width: valueColW, align: 'left' });
    } else {
      doc.text(`CGST ${formatCurrency(cgstRate)}% : ${formatCurrency(cgstAmount)} ${currencyCode}`, valueColX, y + 14, { width: valueColW, align: 'left' });
      doc.text(`SGST ${formatCurrency(sgstRate)}% : ${formatCurrency(sgstAmount)} ${currencyCode}`, valueColX, y + 23, { width: valueColW, align: 'left' });
      doc.font('Helvetica-Bold').text(`Value : ${formatCurrency(grandTotal)} ${currencyCode}`, valueColX, y + 32, { width: valueColW, align: 'left' });
    }

    return y + rowHeight;
  }

  function drawTableHeader(y) {
    const headerHeight = 25;
    doc.rect(margin, y, contentWidth, headerHeight).fillAndStroke('#e6e6e6', '#000000');

    const cols = [
      { label: 'No', x: margin + 2, width: 20, align: 'center' },
      { label: 'Description', x: margin + 22, width: 230, align: 'left' },
      { label: 'UM', x: margin + 252, width: 35, align: 'center' },
      { label: 'Quantity', x: margin + 287, width: 55, align: 'right' },
      { label: 'Rate\nINR', x: margin + 342, width: 60, align: 'right' },
      { label: 'Delivery', x: margin + 402, width: contentWidth - 372, align: 'center' }
    ];

    doc.fillColor('#000000').fontSize(7).font('Helvetica-Bold');
    cols.forEach((col, i) => {
      if (i > 0) {
        doc.moveTo(col.x, y).lineTo(col.x, y + headerHeight).stroke();
      }
      const lines = col.label.split('\n');
      const lineH = 8;
      const startY = y + (headerHeight - (lines.length * lineH)) / 2 + 2;
      lines.forEach((line, idx) => {
        doc.text(line, col.x + 2, startY + (idx * lineH), { width: col.width - 4, align: col.align });
      });
    });

    return y + headerHeight;
  }

  function drawTableRow(y, item, index) {
    const rowHeight = 18;
    drawBorder(margin, y, contentWidth, rowHeight);

    const cols = [
      { x: margin + 2, width: 20, value: String(index + 1), align: 'center' },
      { x: margin + 22, width: 230, value: `${item.material?.material_number || ''} ${item.material?.description || item.description || ''}`.trim(), align: 'left' },
      { x: margin + 252, width: 35, value: item.uom?.uom_code || '', align: 'center' },
      { x: margin + 287, width: 55, value: formatCurrency(item.quantity || 0), align: 'right' },
      { x: margin + 342, width: 60, value: formatCurrency(item.unit_price || 0), align: 'right' },
      { x: margin + 402, width: contentWidth - 372, value: formatDate(item.delivery_date) || '-', align: 'center' }
    ];

    cols.forEach((col, i) => {
      if (i > 0) {
        doc.moveTo(col.x, y).lineTo(col.x, y + rowHeight).stroke();
      }
    });

    doc.fontSize(7).font('Helvetica').fillColor('#000000');
    cols.forEach(col => {
      doc.text(col.value, col.x + 2, y + 5, { width: col.width - 4, align: col.align, ellipsis: true });
    });

    return y + rowHeight;
  }

  function drawFooter(pageNum) {
    doc.save();
    doc.rect(margin, footerSpecialNoteY, contentWidth, footerSpecialNoteHeight).stroke('#000000');
    doc.rect(margin, footerMetaY, contentWidth, footerMetaHeight).stroke('#000000');
    doc.restore();

    const leftX = margin + 5;
    const midX = margin + (contentWidth / 2);
    const leftColWidth = (contentWidth / 2) - 10;
    const rightColWidth = (contentWidth / 2) - 10;

    doc.save();
    doc.fontSize(8).font('Helvetica-Bold').fillColor('#000000');
    doc.text('Special Note', leftX, footerSpecialNoteY + 5);

    const footerNotes = [
      { label: 'Payment Terms', value: cq.payment_terms || '-' },
      { label: 'Validity', value: formatDate(cq.valid_until) || '-' },
      { label: 'Remarks', value: cq.remarks || '-' }
    ];

    let snY = footerSpecialNoteY + 17;
    doc.fontSize(7).font('Helvetica').fillColor('#000000');
    footerNotes.forEach((note) => {
      doc.text(`${note.label}:`, leftX + 5, snY, { continued: true, width: contentWidth - 20 }).text(` ${note.value}`);
      snY += 10;
    });

    const totalTaxAmount = gstSummary.cgst + gstSummary.sgst + gstSummary.igst;
    const effectiveTaxRate = gstSummary.taxable > 0 ? (totalTaxAmount / gstSummary.taxable) * 100 : 0;
    const perSideRate = effectiveTaxRate / 2;
    const currencyCode = cq.currency?.currency_code || 'INR';
    let gstInfoY = footerSpecialNoteY + 17;

    doc.fontSize(7).font('Helvetica-Bold').fillColor('#000000');
    doc.text('GST Summary', midX, gstInfoY, { width: rightColWidth, align: 'left' });
    gstInfoY += 10;
    doc.font('Helvetica');
    doc.text(`Tax Type: ${isInterStateSupply ? 'Inter-State' : 'Intra-State'}`, midX, gstInfoY, { width: rightColWidth });
    gstInfoY += 10;

    if (isInterStateSupply) {
      doc.text(`IGST Rate: ${formatCurrency(effectiveTaxRate)}%`, midX, gstInfoY, { width: rightColWidth });
      gstInfoY += 10;
      doc.text(`IGST Amount: ${formatCurrency(gstSummary.igst)} ${currencyCode}`, midX, gstInfoY, { width: rightColWidth });
    } else {
      doc.text(`CGST Rate: ${formatCurrency(perSideRate)}%`, midX, gstInfoY, { width: rightColWidth });
      gstInfoY += 10;
      doc.text(`SGST Rate: ${formatCurrency(perSideRate)}%`, midX, gstInfoY, { width: rightColWidth });
      gstInfoY += 10;
      doc.text(`CGST/SGST Amount: ${formatCurrency(gstSummary.cgst)} / ${formatCurrency(gstSummary.sgst)} ${currencyCode}`, midX, gstInfoY, { width: rightColWidth });
    }

    doc.moveTo(margin, footerMetaY).lineTo(margin + contentWidth, footerMetaY).stroke();
    const metaTop = footerMetaY;

    doc.fontSize(7).font('Helvetica');
    doc.text('SAC No. :', leftX, metaTop + 6, { width: leftColWidth, height: 10, lineBreak: false });
    doc.text('ARN Code :', midX, metaTop + 6, { width: rightColWidth, height: 10, lineBreak: false });
    doc.text('HSN Code :', leftX, metaTop + 16, { width: leftColWidth, height: 10, lineBreak: false });

    doc.font('Helvetica-Oblique');
    doc.text('Registered Office:', leftX, metaTop + 28, { width: leftColWidth, height: 10, lineBreak: false });
    doc.font('Helvetica').fontSize(7);
    const regOffice = printHeader?.address || '';
    doc.text(regOffice, leftX, metaTop + 37, { width: leftColWidth, height: 12, lineBreak: false });

    const companyName = printHeader?.company_name || cq.company?.name || 'RAM INDUSTRIES';
    doc.text(`For ${companyName.toUpperCase()}`, midX, metaTop + 28, { width: rightColWidth, height: 10, lineBreak: false });

    const refCode = printHeader?.customer_quotation_form_code || 'CQ01';
    doc.text(refCode, leftX, metaTop + 53, { width: leftColWidth, height: 10, lineBreak: false });
    doc.text(`Page No      ${pageNum}     :`, midX, metaTop + 53, { width: rightColWidth, height: 10, lineBreak: false });
    doc.restore();
  }

  let y = margin;
  let currentPage = 1;
  drawPageOutline();
  y = drawHeader(y);
  y = drawTitle(y);
  y = drawDetailsSection(y);
  y = drawMetadataRow(y);
  y = drawTableHeader(y);

  const items = Array.isArray(cq.items) ? cq.items : [];
  items.forEach((item, index) => {
    if (y + 18 > footerSpecialNoteY - 10) {
      drawFooter(currentPage);
      doc.addPage();
      currentPage += 1;
      y = margin;
      drawPageOutline();
      y = drawHeader(y);
      y = drawTitle(y);
      y = drawTableHeader(y);
    }
    y = drawTableRow(y, item, index);
  });

  drawFooter(currentPage);
  doc.end();
});

exports.getLatestRateByMaterial = asyncHandler(async (req, res) => {
  const materialId = parseInt(req.params.materialId, 10);
  const companyId = req.user?.companyId;
  const plantId = req.query?.plant_id ? parseInt(req.query.plant_id, 10) : null;

  if (!companyId) {
    return sendError(res, 'Company context is required', 400);
  }
  if (!Number.isInteger(materialId) || materialId <= 0) {
    return sendError(res, 'Valid material ID is required', 400);
  }

  const latestCosting = await db.ProductionCosting.findOne({
    where: {
      company_id: companyId,
      finished_material_id: materialId,
      ...(Number.isInteger(plantId) && plantId > 0 ? { plant_id: plantId } : {})
    },
    attributes: ['id', 'production_costing_number', 'estimated_at', 'total_production_cost', 'material_cost', 'operation_cost'],
    order: [['estimated_at', 'DESC'], ['id', 'DESC']]
  });

  if (!latestCosting) {
    return sendResponse(res, 200, true, 'No production costing found for material', {
      found: false,
      material_id: materialId,
      rate: 0
    });
  }

  sendResponse(res, 200, true, 'Latest production costing rate fetched successfully', {
    found: true,
    material_id: materialId,
    rate: parseFloat(latestCosting.total_production_cost || 0) || 0,
    production_costing_id: latestCosting.id,
    production_costing_number: latestCosting.production_costing_number,
    estimated_at: latestCosting.estimated_at,
    material_cost: parseFloat(latestCosting.material_cost || 0) || 0,
    operation_cost: parseFloat(latestCosting.operation_cost || 0) || 0,
    total_production_cost: parseFloat(latestCosting.total_production_cost || 0) || 0
  });
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

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

  if (!data.plant_id || !data.fiscal_year_id || !data.customer_id) {
    return sendError(res, 'Plant ID, Fiscal Year ID, and Customer ID are required', 400);
  }

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

  try {
    if (!data.cq_number) {
      data.cq_number = await generateCustomerQuotationNumber(companyId, data.fiscal_year_id, transaction);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;

    const { items, ...cqData } = data;
    
    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    if (items && Array.isArray(items)) {
      items.forEach(item => {
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        subTotal += lineTotal;
        discountAmount += lineDiscount;
        taxAmount += lineTax;
      });
    }

    cqData.sub_total = subTotal;
    cqData.discount_amount = discountAmount;
    cqData.tax_amount = taxAmount;
    cqData.total_amount = subTotal - discountAmount + taxAmount;

    const cq = await db.CustomerQuotation.create(cqData, { transaction });

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        await db.CustomerQuotationItem.create({
          cq_id: cq.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          discount_percentage: discountPercent,
          discount_amount: lineDiscount,
          net_amount: taxableAmount,
          tax_rate: taxRate,
          tax_amount: lineTax,
          cgst_rate: parseFloat(item.cgst_rate || 0) || 0,
          cgst_amount: parseFloat(item.cgst_amount || 0) || 0,
          sgst_rate: parseFloat(item.sgst_rate || 0) || 0,
          sgst_amount: parseFloat(item.sgst_amount || 0) || 0,
          igst_rate: parseFloat(item.igst_rate || 0) || 0,
          igst_amount: parseFloat(item.igst_amount || 0) || 0,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code
        }, { transaction });
      }
    }

    await transaction.commit();

    const createdCQ = await db.CustomerQuotation.findByPk(cq.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.BusinessPartner, as: 'customer' },
        {
          model: db.CustomerQuotationItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'Customer Quotation created successfully', createdCQ);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const data = req.body;
  const userId = req.user?.userId || null;

  const cq = await db.CustomerQuotation.findByPk(id);

  if (!cq) {
    return sendError(res, 'Customer Quotation not found', 404);
  }

  if (cq.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Customer Quotations can be updated', 400);
  }

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

  try {
    data.updated_by = userId;

    const { items, ...cqData } = data;
    
    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    if (items && Array.isArray(items)) {
      items.forEach(item => {
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        subTotal += lineTotal;
        discountAmount += lineDiscount;
        taxAmount += lineTax;
      });
    }

    cqData.sub_total = subTotal;
    cqData.discount_amount = discountAmount;
    cqData.tax_amount = taxAmount;
    cqData.total_amount = subTotal - discountAmount + taxAmount;

    await cq.update(cqData, { transaction });

    await db.CustomerQuotationItem.destroy({ where: { cq_id: cq.id }, transaction });

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        await db.CustomerQuotationItem.create({
          cq_id: cq.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          discount_percentage: discountPercent,
          discount_amount: lineDiscount,
          net_amount: taxableAmount,
          tax_rate: taxRate,
          tax_amount: lineTax,
          cgst_rate: parseFloat(item.cgst_rate || 0) || 0,
          cgst_amount: parseFloat(item.cgst_amount || 0) || 0,
          sgst_rate: parseFloat(item.sgst_rate || 0) || 0,
          sgst_amount: parseFloat(item.sgst_amount || 0) || 0,
          igst_rate: parseFloat(item.igst_rate || 0) || 0,
          igst_amount: parseFloat(item.igst_amount || 0) || 0,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code
        }, { transaction });
      }
    }

    await transaction.commit();

    const updatedCQ = await db.CustomerQuotation.findByPk(cq.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.BusinessPartner, as: 'customer' },
        {
          model: db.CustomerQuotationItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Customer Quotation updated successfully', updatedCQ);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const cq = await db.CustomerQuotation.findByPk(id);

  if (!cq) {
    return sendError(res, 'Customer Quotation not found', 404);
  }

  if (cq.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Customer Quotations can be deleted', 400);
  }

  await db.CustomerQuotationItem.destroy({ where: { cq_id: cq.id } });
  await cq.destroy();

  sendResponse(res, 200, true, 'Customer Quotation deleted successfully', null);
});

exports.submit = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const cq = await db.CustomerQuotation.findByPk(id, {
    include: [{ model: db.CustomerQuotationItem, as: 'items', attributes: ['id'] }]
  });

  if (!cq) {
    return sendError(res, 'Customer Quotation not found', 404);
  }
  if (cq.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Customer Quotations can be submitted', 400);
  }
  if (!cq.items || cq.items.length === 0) {
    return sendError(res, 'Cannot submit Customer Quotation without items', 400);
  }

  await cq.update({
    status: 'SUBMITTED',
    updated_by: userId
  });

  const updated = await db.CustomerQuotation.findByPk(id);
  sendResponse(res, 200, true, 'Customer Quotation submitted successfully', updated);
});

exports.approve = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const cq = await db.CustomerQuotation.findByPk(id);
  if (!cq) {
    return sendError(res, 'Customer Quotation not found', 404);
  }
  if (cq.status !== 'SUBMITTED') {
    return sendError(res, 'Only SUBMITTED Customer Quotations can be approved', 400);
  }

  await cq.update({
    status: 'APPROVED',
    updated_by: userId
  });

  const updated = await db.CustomerQuotation.findByPk(id);
  sendResponse(res, 200, true, 'Customer Quotation approved successfully', updated);
});

