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');
const glPostingService = require('../services/glPostingService');
const stockLedgerService = require('../services/stockLedgerService');
const { generateNextNumber } = require('../services/numberFormatService');
const {
  resolvePurchaseLineDescription,
  fetchMaterialsForLineDescription,
} = require('../utils/rawMaterialLineDescription');
const { computeLineWeightKg } = require('../utils/lineWeightKg');

const generateGRNNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'GOODS_RECEIPT_NOTE', fiscalYearId, transaction);
  } catch (err) {
    console.warn('GRN number from format master failed, using fallback:', err.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `GRN${year}`;
    const lastGRN = await db.GoodsReceiptNote.findOne({
      where: { company_id: companyId, grn_number: { [Op.like]: `${prefix}%` } },
      order: [['grn_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (lastGRN) {
      const lastSequence = parseInt(lastGRN.grn_number.slice(-4)) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
};

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

  if (poId) {
    where.po_id = poId;
  }

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

  if (search) {
    where[Op.or] = [
      { grn_number: { [Op.like]: `%${search}%` } },
      { invoice_number: { [Op.like]: `%${search}%` } },
      { lr_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.GoodsReceiptNote.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: 'vendor', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] },
      { model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code', 'description'], required: false },
      {
        model: db.GoodsReceiptNoteItem,
        as: 'items',
        attributes: ['id', 'quantity', 'excess_quantity', 'remarks', 'storage_location_id'],
        required: false,
        separate: true,
        include: [
          { model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code', 'description'], required: false }
        ]
      }
    ],
    limit: limitNum,
    offset,
    order: [['grn_date', 'DESC'], ['id', 'DESC']],
    distinct: true
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'Goods Receipt Notes retrieved successfully', response);
});

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

  const grn = await db.GoodsReceiptNote.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.PurchaseOrder, as: 'purchaseOrder' },
      { model: db.StorageLocation, as: 'storageLocation' },
      {
        model: db.GateEntry,
        as: 'gateEntries',
        attributes: ['id', 'gate_entry_number', 'status', 'grn_id', 'po_id', 'vendor_id', 'invoice_number'],
        required: false
      },
      {
        model: db.GoodsReceiptNoteItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            include: [
              {
                model: db.MaterialThickness,
                as: 'materialThickness',
                attributes: ['id', 'thickness_value', 'thickness_unit'],
                required: false,
              },
              {
                model: db.MaterialGrade,
                as: 'materialGrade',
                attributes: ['id', 'grade_code', 'grade_name'],
                required: false,
              },
              {
                model: db.MaterialType,
                as: 'materialType',
                attributes: ['id', 'type_code', 'type_name'],
                required: false,
              },
            ],
          },
          { model: db.UOM, as: 'uom' },
          { model: db.PurchaseOrderItem, as: 'purchaseOrderItem' },
          { model: db.StorageLocation, as: 'storageLocation' }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

  if (!grn) {
    return sendError(res, 'Goods Receipt Note not found', 404);
  }

  const plain = grn.toJSON ? grn.toJSON() : { ...grn };

  // Expose a direct gate_entry_number for UI convenience.
  // Prefer linked gate entry (grn_id), then fallback by PO+vendor+invoice for legacy data.
  let gateEntryNumber = null;
  if (Array.isArray(plain.gateEntries) && plain.gateEntries.length > 0) {
    const exactLinked = plain.gateEntries.find((entry) => Number(entry.grn_id) === Number(plain.id));
    const firstLinked = exactLinked || plain.gateEntries[0];
    gateEntryNumber = firstLinked?.gate_entry_number || null;
  }

  if (!gateEntryNumber) {
    const fallbackGateEntry = await db.GateEntry.findOne({
      where: {
        company_id: plain.company_id,
        plant_id: plain.plant_id,
        po_id: plain.po_id,
        vendor_id: plain.vendor_id,
        invoice_number: plain.invoice_number || null
      },
      attributes: ['id', 'gate_entry_number', 'status', 'grn_id'],
      order: [['id', 'DESC']]
    });
    gateEntryNumber = fallbackGateEntry?.gate_entry_number || null;
    if (fallbackGateEntry && (!Array.isArray(plain.gateEntries) || plain.gateEntries.length === 0)) {
      plain.gateEntries = [fallbackGateEntry];
    }
  }

  plain.gate_entry_number = gateEntryNumber;
  sendResponse(res, 200, true, 'Goods Receipt Note retrieved successfully', plain);
});

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

  const grn = await db.GoodsReceiptNote.findByPk(id, {
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name', 'legal_name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name', 'vendor_name', 'legal_name'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] },
      { model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code', 'description'] },
      {
        model: db.User,
        as: 'createdByUser',
        attributes: ['id', 'username', 'first_name', 'last_name'],
        required: false
      },
      {
        model: db.GateEntry,
        as: 'gateEntries',
        attributes: ['id', 'gate_entry_number'],
        required: false
      },
      {
        model: db.GoodsReceiptNoteItem,
        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.GoodsReceiptNoteItem, as: 'items' }, 'line_number', 'ASC']]
  });

  if (!grn) return sendError(res, 'Goods Receipt Note not found', 404);

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

  const fmtNum = (v, d = 2) => {
    const n = parseFloat(v);
    return Number.isFinite(n) ? n.toFixed(d) : (0).toFixed(d);
  };

  const fmtDate = (v) => {
    if (!v) return '';
    const dt = new Date(v);
    if (Number.isNaN(dt.getTime())) return '';
    const dd = String(dt.getDate()).padStart(2, '0');
    const mm = String(dt.getMonth() + 1).padStart(2, '0');
    const yyyy = dt.getFullYear();
    return `${dd}.${mm}.${yyyy}`;
  };

  const createdBy = `${grn.createdByUser?.first_name || ''} ${grn.createdByUser?.last_name || ''}`.trim()
    || grn.createdByUser?.username
    || '-';
  const gateEntryNumber = grn.gateEntries?.[0]?.gate_entry_number || '-';
  const vendorName = grn.vendor?.vendor_name || grn.vendor?.name || grn.vendor?.legal_name || 'Vendor';
  const fileName = `GRN_${safeName(grn.grn_number || grn.id)}_${safeName(vendorName)}.pdf`;

  const [printHeader, vendorAddress, vendorGSTIN] = await Promise.all([
    db.PrintHeader.findOne({
      where: { company_id: grn.company_id, status: 'ACTIVE' },
      order: [['id', 'DESC']]
    }),
    db.BPAddress.findOne({
      where: { bp_id: grn.vendor_id, status: 'ACTIVE' },
      order: [['is_default', 'DESC'], ['id', 'ASC']]
    }),
    db.BPGSTIN.findOne({
      where: { bp_id: grn.vendor_id, status: 'ACTIVE' },
      order: [['is_default', 'DESC'], ['id', 'ASC']]
    })
  ]);

  let vendorCityName = '';
  let vendorStateName = '';
  if (vendorAddress?.city_id) {
    const city = await db.City.findByPk(vendorAddress.city_id);
    vendorCityName = city?.city_name || '';
  }
  if (vendorAddress?.state_id || vendorGSTIN?.state_id) {
    const state = await db.State.findByPk(vendorAddress?.state_id || vendorGSTIN?.state_id);
    vendorStateName = state?.state_name || '';
  }

  let companyLogoPath = null;
  if (printHeader?.logo_path) {
    let fullPath = path.join(__dirname, '..', 'uploads', printHeader.logo_path);
    if (!fs.existsSync(fullPath)) 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)) fullPath = path.join(__dirname, '..', printHeader.quality_certification_logo_path);
    if (fs.existsSync(fullPath)) certLogoPath = fullPath;
  }

  const rows = (Array.isArray(grn.items) ? grn.items : []).map((item, index) => {
    const qty = parseFloat(item.quantity || 0) || 0;
    const unitPrice = parseFloat(item.unit_price || 0) || 0;
    const taxRate = parseFloat(item.tax_rate || 0) || 0;
    const taxAmount = Number.isFinite(parseFloat(item.tax_amount))
      ? parseFloat(item.tax_amount)
      : (qty * unitPrice * taxRate) / 100;
    const lineTotal = Number.isFinite(parseFloat(item.line_total))
      ? parseFloat(item.line_total)
      : (qty * unitPrice) + taxAmount;
    const lineDesc = String(item.description || '').trim();
    const matShort = String(item.material?.description || '').trim();
    return {
      no: index + 1,
      code: item.material?.material_number || '-',
      name: lineDesc || matShort || '-',
      uom: item.uom?.uom_code || item.uom?.uom_name || '-',
      qty,
      unitPrice,
      taxRate,
      taxAmount,
      lineTotal
    };
  });

  const totals = rows.reduce((acc, row) => {
    acc.qty += row.qty || 0;
    acc.tax += row.taxAmount || 0;
    acc.total += row.lineTotal || 0;
    return acc;
  }, { qty: 0, tax: 0, total: 0 });

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

  const doc = new PDFDocument({ margin: 30, size: 'A4' });
  doc.pipe(res);

  const pageWidth = doc.page.width;
  const pageHeight = doc.page.height;
  const margin = 30;
  const contentWidth = pageWidth - (margin * 2);
  const footerReserve = 120;

  const drawBorder = (x, y, w, h) => doc.rect(x, y, w, h).stroke('#000000');

  const tableCols = [
    { key: 'no', label: 'S.No', width: 28, align: 'center' },
    { key: 'name', label: 'Description', width: 234, align: 'left' },
    { key: 'uom', label: 'UOM', width: 34, align: 'center' },
    { key: 'qty', label: 'Qty', width: 44, align: 'right' },
    { key: 'unitPrice', label: 'Rate INR', width: 56, align: 'right' },
    { key: 'taxRate', label: 'Tax %', width: 40, align: 'right' },
    { key: 'taxAmount', label: 'Tax Amt', width: 46, align: 'right' },
    { key: 'lineTotal', label: 'Amount INR', width: 53, align: 'right' }
  ];

  const drawPageOutline = () => {
    drawBorder(margin, margin, contentWidth, pageHeight - (margin * 2));
  };

  const drawHeader = (y) => {
    const headerH = 108;
    drawBorder(margin, y, contentWidth, headerH);

    const sidePadding = 4;
    const companyLogoBoxW = Math.min(200, contentWidth * 0.32);
    const companyLogoBoxH = headerH - 12;
    const logoX = margin + sidePadding;
    const logoY = y + ((headerH - companyLogoBoxH) / 2);

    const certBoxW = Math.min(150, contentWidth * 0.24);
    const certBoxH = companyLogoBoxH;
    const certX = pageWidth - margin - certBoxW - sidePadding;
    const certY = y + ((headerH - certBoxH) / 2);

    if (companyLogoPath) {
      try {
        // Make logo visually prominent similar to Purchase Order format.
        doc.image(companyLogoPath, logoX, logoY, { cover: [companyLogoBoxW, companyLogoBoxH], align: 'center', valign: 'center' });
      } catch (error) {
        // Continue without logo if rendering fails.
      }
    }
    if (certLogoPath) {
      try {
        doc.image(certLogoPath, certX, certY, { fit: [certBoxW, certBoxH], align: 'center', valign: 'center' });
      } catch (error) {
        // Continue without certificate logo if rendering fails.
      }
    }

    const leftBlockW = companyLogoBoxW + (sidePadding * 2);
    const rightBlockW = certBoxW + (sidePadding * 2);
    const centerX = margin + leftBlockW;
    const centerWidth = contentWidth - leftBlockW - rightBlockW;

    doc.fontSize(17).font('Helvetica-Bold').fillColor('#000000');
    doc.text(printHeader?.company_name || grn.company?.name || 'Company Name', centerX, y + 8, { width: centerWidth, align: 'center' });

    let infoY = y + 31;
    if (printHeader?.former_name) {
      doc.fontSize(8.5).font('Helvetica-Oblique');
      doc.text(`(Formerly Known as ${printHeader.former_name})`, centerX, infoY, { width: centerWidth, align: 'center' });
      infoY += 12;
    }

    const addr = String(printHeader?.address || '').replace(/\s*\r?\n\s*/g, ', ').replace(/\s+/g, ' ').trim();
    if (addr) {
      let addrFontSize = 8.2;
      while (addrFontSize > 6) {
        doc.font('Helvetica').fontSize(addrFontSize);
        if (doc.widthOfString(addr) <= centerWidth) break;
        addrFontSize -= 0.2;
      }
      const addrWidth = doc.widthOfString(addr);
      const addrX = centerX + Math.max(0, (centerWidth - addrWidth) / 2);
      doc.text(addr, addrX, infoY, { lineBreak: false });
      infoY += addrFontSize + 2.5;
    }

    const contactParts = [
      (printHeader?.email || '').trim() ? `Email : ${String(printHeader.email).trim()}` : '',
      (printHeader?.phone || '').trim() ? `Contact : ${String(printHeader.phone).trim()}` : ''
    ].filter(Boolean);
    const infoLine = [
      ...contactParts,
      (printHeader?.gstin || '').trim() ? `GSTIN : ${String(printHeader.gstin).trim()}` : ''
    ].filter(Boolean).join('    |    ');
    if (infoLine) {
      let infoFontSize = 8;
      while (infoFontSize > 6) {
        doc.font('Helvetica').fontSize(infoFontSize);
        if (doc.widthOfString(infoLine) <= centerWidth) break;
        infoFontSize -= 0.2;
      }
      const infoWidth = doc.widthOfString(infoLine);
      const infoX = centerX + Math.max(0, (centerWidth - infoWidth) / 2);
      doc.text(infoLine, infoX, infoY, { lineBreak: false });
    }

    // Keep header border visible on top of images and text.
    doc.moveTo(margin, y).lineTo(margin + contentWidth, y).stroke();
    doc.moveTo(margin, y + headerH).lineTo(margin + contentWidth, y + headerH).stroke();

    return y + headerH;
  };

  const drawTitle = (y) => {
    const h = 20;
    drawBorder(margin, y, contentWidth, h);
    doc.fontSize(12).font('Helvetica-Bold').text('GOODS RECEIPT NOTE (GRN)', margin, y + 5, { width: contentWidth, align: 'center' });
    return y + h;
  };

  const drawDetailsSection = (y) => {
    const h = 110;
    const leftW = contentWidth * 0.5;
    const rightW = contentWidth * 0.5;
    const rightX = margin + leftW;

    drawBorder(margin, y, leftW, h);
    drawBorder(rightX, y, rightW, h);

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

    let leftY = y + 5;
    doc.fontSize(8).font('Helvetica');
    doc.text('From,', margin + 5, leftY);
    leftY += 10;
    doc.fontSize(8).font('Helvetica-Bold');
    doc.text(String(vendorName || 'Vendor').toUpperCase(), margin + 5, leftY, { width: leftW - 10 });
    leftY += 11;

    const addrLines = [vendorAddress?.address_line1, vendorAddress?.address_line2].map((v) => String(v || '').trim()).filter(Boolean);
    if (addrLines.length > 0) {
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text('Address', margin + 5, leftY, { width: 46 });
      doc.text(':', margin + 51, leftY, { width: 8 });
      doc.fontSize(7).font('Helvetica');
      doc.text(addrLines[0], margin + 59, leftY, { width: leftW - 64 });
      leftY += 9;
      for (let i = 1; i < addrLines.length; i += 1) {
        doc.text(addrLines[i], margin + 59, leftY, { width: leftW - 64 });
        leftY += 9;
      }
    }

    const cityLineParts = [];
    if (vendorCityName) cityLineParts.push(vendorCityName);
    if (vendorAddress?.pincode) cityLineParts.push(String(vendorAddress.pincode));
    if (cityLineParts.length > 0) {
      doc.fontSize(7).font('Helvetica');
      doc.text(cityLineParts.join(' - '), margin + 59, leftY, { width: leftW - 64 });
      leftY += 9;
    }

    leftY = drawLeftKV('Vendor Code', grn.vendor?.bp_number || '-', leftY);
    leftY = drawLeftKV('GSTIN', vendorGSTIN?.gstin || '-', leftY);
    leftY = drawLeftKV('State', vendorStateName || '-', leftY);

    const drawRightKV = (label, value, posY) => {
      const labelW = 64;
      const colonW = 8;
      const valueW = rightW - 14 - labelW - colonW;
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(label, rightX + 5, posY, { width: labelW });
      doc.text(':', rightX + 5 + labelW, posY, { width: colonW });
      doc.fontSize(7).font('Helvetica');
      doc.text(String(value || '-'), rightX + 5 + labelW + colonW, posY, { width: valueW });
      return posY + 10;
    };

    let rightY = y + 5;
    rightY = drawRightKV('GRN No', grn.grn_number || '-', rightY);
    rightY = drawRightKV('Date', fmtDate(grn.grn_date) || '-', rightY);
    rightY = drawRightKV('PO No', grn.purchaseOrder?.po_number || '-', rightY);
    rightY = drawRightKV('Inward Insp', gateEntryNumber, rightY);
    rightY = drawRightKV('Invoice No', grn.invoice_number || '-', rightY);

    return y + h;
  };

  const drawMetaRow = (y) => {
    const h = 34;
    drawBorder(margin, y, contentWidth, h);
    const colW = contentWidth / 4;
    doc.moveTo(margin + colW, y).lineTo(margin + colW, y + h).stroke();
    doc.moveTo(margin + (colW * 2), y).lineTo(margin + (colW * 2), y + h).stroke();
    doc.moveTo(margin + (colW * 3), y).lineTo(margin + (colW * 3), y + h).stroke();

    const taxable = Math.max(0, (totals.total || 0) - (totals.tax || 0));
    doc.fontSize(7).font('Helvetica').text(`Total Qty : ${fmtNum(totals.qty, 3)}`, margin + 5, y + 7, { width: colW - 10 });
    doc.text(`Taxable Amount : ${fmtNum(taxable, 2)} INR`, margin + colW + 5, y + 7, { width: colW - 10 });
    doc.text(`Tax Amount : ${fmtNum(totals.tax, 2)} INR`, margin + (colW * 2) + 5, y + 7, { width: colW - 10 });
    doc.font('Helvetica-Bold').text(`Total Value : ${fmtNum(totals.total, 2)} INR`, margin + (colW * 3) + 5, y + 7, { width: colW - 10 });
    return y + h;
  };

  const drawTableHeader = (y) => {
    const h = 24;
    doc.rect(margin, y, contentWidth, h).stroke('#000000');
    let x = margin;
    doc.fillColor('#000000').fontSize(7).font('Helvetica-Bold');
    tableCols.forEach((col, idx) => {
      if (idx > 0) {
        doc.moveTo(x, y).lineTo(x, y + h).stroke();
      }
      doc.text(col.label, x + 2, y + 8, { width: col.width - 4, align: col.align });
      x += col.width;
    });
    return y + h;
  };

  const drawTableRow = (y, row) => {
    const h = 18;
    drawBorder(margin, y, contentWidth, h);
    let x = margin;
    const values = {
      no: String(row.no),
      name: String(row.name || '').replace(/\s+/g, ' ').trim(),
      uom: row.uom,
      qty: fmtNum(row.qty, 3),
      unitPrice: fmtNum(row.unitPrice, 2),
      taxRate: fmtNum(row.taxRate, 2),
      taxAmount: fmtNum(row.taxAmount, 2),
      lineTotal: fmtNum(row.lineTotal, 2)
    };
    tableCols.forEach((col, idx) => {
      if (idx > 0) {
        doc.moveTo(x, y).lineTo(x, y + h).stroke();
      }
      doc.fontSize(7).font('Helvetica').fillColor('#000000');
      doc.text(String(values[col.key] || ''), x + 2, y + 5, { width: col.width - 4, align: col.align, ellipsis: true, lineBreak: false });
      x += col.width;
    });
    return y + h;
  };

  const drawSummaryAndSign = (y) => {
    y += 8;

    doc.font('Helvetica').fontSize(8.5);
    if (grn.remarks) {
      doc.text(`Remarks: ${grn.remarks}`, margin + 3, y, { width: contentWidth - 6 });
      y += 14;
    }
    y += 20;

    const signW = 220;
    const signGap = contentWidth - (signW * 2);
    const verifiedX = margin;
    const authorizedX = margin + signW + signGap;
    doc.moveTo(verifiedX, y).lineTo(verifiedX + signW, y).stroke('#000000');
    doc.moveTo(authorizedX, y).lineTo(authorizedX + signW, y).stroke('#000000');
    doc.font('Helvetica-Bold').fontSize(10);
    doc.text('Verified Signature', verifiedX, y + 7, { width: signW, align: 'center' });
    doc.text('Authorised Signature', authorizedX, y + 7, { width: signW, align: 'center' });
  };

  let y = margin;
  drawPageOutline();
  y = drawHeader(y);
  y = drawTitle(y);
  y = drawDetailsSection(y);
  y = drawMetaRow(y);
  y = drawTableHeader(y);

  rows.forEach((row) => {
    if (y + 18 > pageHeight - margin - footerReserve) {
      doc.addPage();
      y = margin;
      drawPageOutline();
      y = drawHeader(y);
      y = drawTitle(y);
      y = drawDetailsSection(y);
      y = drawMetaRow(y);
      y = drawTableHeader(y);
    }
    y = drawTableRow(y, row);
  });

  if (y + 72 > pageHeight - margin) {
    doc.addPage();
    y = margin;
    drawPageOutline();
    y = drawHeader(y);
    y = drawTitle(y);
    y += 8;
  }
  drawSummaryAndSign(y + 6);

  doc.end();
});

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.vendor_id) {
    return sendError(res, 'Plant ID, Fiscal Year ID, and Vendor ID are required', 400);
  }

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

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

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

    const { items, storage_location_id, gate_entry_id, ...grnData } = data;
    let linkedGateEntry = null;
    
    // Validate header storage_location_id if provided
    if (storage_location_id) {
      const headerStorageLoc = await db.StorageLocation.findOne({
        where: {
          id: storage_location_id,
          company_id: companyId,
          status: 'ACTIVE'
        },
        transaction
      });
      if (!headerStorageLoc) {
        await transaction.rollback();
        return sendError(res, 'Header storage location not found or inactive', 400);
      }
      grnData.storage_location_id = storage_location_id;
    }

    // Professional ERP validation: If GRN is against a PO, Gate Entry process must be completed.
    // Gate Entry is considered completed for GRN creation when status is GRN_READY and not yet linked to a GRN.
    if (grnData.po_id) {
      if (gate_entry_id) {
        linkedGateEntry = await db.GateEntry.findOne({
          where: {
            id: gate_entry_id,
            company_id: companyId,
            plant_id: grnData.plant_id,
            po_id: grnData.po_id,
            status: { [Op.in]: ['GRN_READY', 'QC_PENDING'] },
            grn_id: null
          },
          transaction
        });
      }

      if (!linkedGateEntry) {
        linkedGateEntry = await db.GateEntry.findOne({
          where: {
            company_id: companyId,
            plant_id: grnData.plant_id,
            po_id: grnData.po_id,
            status: { [Op.in]: ['GRN_READY', 'QC_PENDING'] },
            grn_id: null
          },
          order: [['id', 'DESC']],
          transaction
        });
      }

      if (!linkedGateEntry) {
        await transaction.rollback();
        return sendError(res, 'Gate Entry process is not completed for this PO. Complete Gate Entry/QC first, then create GRN.', 400);
      }
    }
    
    let totalAmount = 0;

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

        const lineTotal = quantity * unitPrice;
        const lineTax = (lineTotal * taxRate) / 100;

        totalAmount += lineTotal + lineTax;
      });
    }

    grnData.total_amount = totalAmount;

    const grn = await db.GoodsReceiptNote.create(grnData, { transaction });

    if (items && Array.isArray(items)) {
      // Validate item storage_location_id if provided
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        if (item.storage_location_id) {
          const itemStorageLoc = await db.StorageLocation.findOne({
            where: {
              id: item.storage_location_id,
              company_id: companyId,
              status: 'ACTIVE'
            },
            transaction
          });
          if (!itemStorageLoc) {
            await transaction.rollback();
            return sendError(res, `Storage location not found or inactive for item ${i + 1}`, 400);
          }
        }
      }

      const grnLineMaterialMap = await fetchMaterialsForLineDescription(
        db,
        items.map((i) => i.material_id),
        { transaction }
      );

      const grnItems = items.map((item, index) => {
        const quantity = parseFloat(item.quantity) || 0;
        const excessQuantity = Number.isFinite(parseFloat(item.excess_quantity))
          ? parseFloat(item.excess_quantity)
          : 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineTax = (lineTotal * taxRate) / 100;

        return {
          grn_id: grn.id,
          po_item_id: item.po_item_id,
          line_number: index + 1,
          material_id: item.material_id,
          description: resolvePurchaseLineDescription(
            { material_id: item.material_id, description: item.description || '' },
            grnLineMaterialMap.get(parseInt(item.material_id, 10))
          ),
          quantity: quantity,
          excess_quantity: excessQuantity,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          tax_rate: taxRate,
          tax_amount: lineTax,
          line_total: lineTotal + lineTax,
          batch_number: item.batch_number,
          storage_location_id: item.storage_location_id || null,
          remarks: item.remarks,
          line_weight_kg: computeLineWeightKg({
            quantity,
            line_weight_kg: item.line_weight_kg,
            material: grnLineMaterialMap.get(parseInt(item.material_id, 10)),
          }),
        };
      });

      await db.GoodsReceiptNoteItem.bulkCreate(grnItems, { transaction });
    }

    // Link the GRN to Gate Entry so Gate Entry list shows GRN number (not Pending).
    if (linkedGateEntry) {
      await linkedGateEntry.update({
        grn_id: grn.id,
        status: 'GRN_CREATED',
        updated_by: userId
      }, { transaction });
    }

    await transaction.commit();

    const createdGRN = await db.GoodsReceiptNote.findByPk(grn.id, {
      include: [
        { model: db.StorageLocation, as: 'storageLocation' },
        { 
          model: db.GoodsReceiptNoteItem, 
          as: 'items', 
          include: [
            { model: db.Material, as: 'material' }, 
            { model: db.UOM, as: 'uom' },
            { model: db.StorageLocation, as: 'storageLocation' }
          ] 
        }
      ]
    });

    sendResponse(res, 201, true, 'Goods Receipt Note created successfully', createdGRN);
  } 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 grn = await db.GoodsReceiptNote.findByPk(id);
  if (!grn) {
    return sendError(res, 'Goods Receipt Note not found', 404);
  }

  if (grn.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Goods Receipt Notes can be updated', 400);
  }

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

  try {
    const { items, storage_location_id, ...grnData } = data;
    grnData.updated_by = userId;
    
    // Validate header storage_location_id if provided
    if (storage_location_id) {
      const headerStorageLoc = await db.StorageLocation.findOne({
        where: {
          id: storage_location_id,
          company_id: grn.company_id,
          status: 'ACTIVE'
        },
        transaction
      });
      if (!headerStorageLoc) {
        await transaction.rollback();
        return sendError(res, 'Header storage location not found or inactive', 400);
      }
      grnData.storage_location_id = storage_location_id;
    } else {
      grnData.storage_location_id = null;
    }

    let totalAmount = 0;

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

        const lineTotal = quantity * unitPrice;
        const lineTax = (lineTotal * taxRate) / 100;

        totalAmount += lineTotal + lineTax;
      });
    }

    grnData.total_amount = totalAmount;

    await grn.update(grnData, { transaction });

    if (items && Array.isArray(items)) {
      // Validate item storage_location_id if provided
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        if (item.storage_location_id) {
          const itemStorageLoc = await db.StorageLocation.findOne({
            where: {
              id: item.storage_location_id,
              company_id: grn.company_id,
              status: 'ACTIVE'
            },
            transaction
          });
          if (!itemStorageLoc) {
            await transaction.rollback();
            return sendError(res, `Storage location not found or inactive for item ${i + 1}`, 400);
          }
        }
      }

      await db.GoodsReceiptNoteItem.destroy({ where: { grn_id: grn.id }, transaction });

      const grnLineMaterialMapUpdate = await fetchMaterialsForLineDescription(
        db,
        items.map((i) => i.material_id),
        { transaction }
      );

      const grnItems = items.map((item, index) => {
        const quantity = parseFloat(item.quantity) || 0;
        const excessQuantity = Number.isFinite(parseFloat(item.excess_quantity))
          ? parseFloat(item.excess_quantity)
          : 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineTax = (lineTotal * taxRate) / 100;

        return {
          grn_id: grn.id,
          po_item_id: item.po_item_id,
          line_number: index + 1,
          material_id: item.material_id,
          description: resolvePurchaseLineDescription(
            { material_id: item.material_id, description: item.description || '' },
            grnLineMaterialMapUpdate.get(parseInt(item.material_id, 10))
          ),
          quantity: quantity,
          excess_quantity: excessQuantity,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          tax_rate: taxRate,
          tax_amount: lineTax,
          line_total: lineTotal + lineTax,
          batch_number: item.batch_number,
          storage_location_id: item.storage_location_id || null,
          required_date: item.required_date || null,
          remarks: item.remarks,
          line_weight_kg: computeLineWeightKg({
            quantity,
            line_weight_kg: item.line_weight_kg,
            material: grnLineMaterialMapUpdate.get(parseInt(item.material_id, 10)),
          }),
        };
      });

      await db.GoodsReceiptNoteItem.bulkCreate(grnItems, { transaction });
    }

    await transaction.commit();

    const updatedGRN = await db.GoodsReceiptNote.findByPk(grn.id, {
      include: [
        { model: db.StorageLocation, as: 'storageLocation' },
        { 
          model: db.GoodsReceiptNoteItem, 
          as: 'items', 
          include: [
            { model: db.Material, as: 'material' }, 
            { model: db.UOM, as: 'uom' },
            { model: db.StorageLocation, as: 'storageLocation' }
          ] 
        }
      ]
    });

    sendResponse(res, 200, true, 'Goods Receipt Note updated successfully', updatedGRN);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const grn = await db.GoodsReceiptNote.findByPk(id, {
    include: [
      { model: db.StorageLocation, as: 'storageLocation' },
      { 
        model: db.GoodsReceiptNoteItem, 
        as: 'items', 
        include: [
          { model: db.Material, as: 'material', include: [{ model: db.StorageLocation, as: 'storageLocation' }] },
          { model: db.StorageLocation, as: 'storageLocation' }
        ] 
      }
    ]
  });

  if (!grn) {
    return sendError(res, 'Goods Receipt Note not found', 404);
  }

  if (grn.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Goods Receipt Notes can be posted', 400);
  }

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

  try {
    // Update Purchase Order received quantities
    if (grn.po_id) {
      for (const item of grn.items) {
        if (item.po_item_id) {
          const poItem = await db.PurchaseOrderItem.findByPk(item.po_item_id, { transaction });
          if (poItem) {
            const currentReceived = parseFloat(poItem.received_quantity || 0);
            const grnQuantity = parseFloat(item.quantity || 0);
            const newReceivedQty = currentReceived + grnQuantity;
            const orderedQty = parseFloat(poItem.quantity || 0);
            
            // Don't allow receiving more than ordered
            if (newReceivedQty > orderedQty) {
              await transaction.rollback();
              return sendError(res, `Cannot receive ${grnQuantity} for item. Total received would be ${newReceivedQty}, but ordered quantity is ${orderedQty}`, 400);
            }

            await poItem.update({
              received_quantity: newReceivedQty,
              updated_at: new Date()
            }, { transaction });
          }
        }
      }

      // Update Purchase Order status based on received quantities
      const purchaseOrder = await db.PurchaseOrder.findByPk(grn.po_id, {
        include: [{ model: db.PurchaseOrderItem, as: 'items' }],
        transaction
      });

      if (purchaseOrder && purchaseOrder.status !== 'CANCELLED') {
        const allReceived = purchaseOrder.items.every(item => {
          const ordered = parseFloat(item.quantity || 0);
          const received = parseFloat(item.received_quantity || 0);
          return received >= ordered;
        });

        const partiallyReceived = purchaseOrder.items.some(item => {
          const ordered = parseFloat(item.quantity || 0);
          const received = parseFloat(item.received_quantity || 0);
          return received > 0 && received < ordered;
        });

        if (allReceived && purchaseOrder.status !== 'RECEIVED') {
          await purchaseOrder.update({ status: 'RECEIVED' }, { transaction });
        } else if (partiallyReceived && purchaseOrder.status !== 'PARTIALLY_RECEIVED' && purchaseOrder.status !== 'RECEIVED') {
          await purchaseOrder.update({ status: 'PARTIALLY_RECEIVED' }, { transaction });
        }
      }
    }

    // Update stock ledger for each GRN item
    for (const item of grn.items) {
      const quantity = parseFloat(item.quantity || 0);
      const unitPrice = parseFloat(item.unit_price || 0) || 0;
      const totalAmount = quantity * unitPrice;
      
      // Get storage location following priority:
      // Priority 1: GRN Item storage_location_id (from form)
      // Priority 2: GRN Header storage_location_id (common location)
      // Priority 3: Purchase Order Item storage location (if linked to PO)
      // Priority 4: Material master default storage location (if is_default_storage_location is true)
      // Priority 5: Material Plant master default storage location
      // Priority 6: First active WAREHOUSE storage location for the plant
      let storageLocation = null;
      let storageLocRecord = null;
      
      // Priority 1: GRN Item storage_location_id
      if (item.storage_location_id) {
        storageLocRecord = await db.StorageLocation.findOne({
          where: {
            id: item.storage_location_id,
            company_id: grn.company_id,
            plant_id: grn.plant_id,
            status: 'ACTIVE'
          },
          transaction
        });
        if (storageLocRecord) {
          storageLocation = storageLocRecord.storage_location_code;
        }
      }
      
      // Priority 2: GRN Header storage_location_id
      if (!storageLocation && grn.storage_location_id) {
        storageLocRecord = await db.StorageLocation.findOne({
          where: {
            id: grn.storage_location_id,
            company_id: grn.company_id,
            plant_id: grn.plant_id,
            status: 'ACTIVE'
          },
          transaction
        });
        if (storageLocRecord) {
          storageLocation = storageLocRecord.storage_location_code;
        }
      }
      
      // Priority 3: Purchase Order Item storage location (if linked to PO)
      if (!storageLocation && item.po_item_id) {
        const poItem = await db.PurchaseOrderItem.findByPk(item.po_item_id, { transaction });
        if (poItem && poItem.storage_location) {
          storageLocRecord = await db.StorageLocation.findOne({
            where: {
              company_id: grn.company_id,
              plant_id: grn.plant_id,
              storage_location_code: poItem.storage_location,
              status: 'ACTIVE'
            },
            transaction
          });
          if (storageLocRecord) {
            storageLocation = poItem.storage_location;
          }
        }
      }
      
      // Priority 4: Material master default storage location
      if (!storageLocation && item.material_id) {
        const material = await db.Material.findByPk(item.material_id, {
          include: [{ model: db.StorageLocation, as: 'storageLocation' }],
          transaction
        });
        if (material && material.is_default_storage_location && material.storage_location_id) {
          storageLocRecord = await db.StorageLocation.findOne({
            where: {
              id: material.storage_location_id,
              company_id: grn.company_id,
              plant_id: grn.plant_id,
              status: 'ACTIVE'
            },
            transaction
          });
          if (storageLocRecord) {
            storageLocation = storageLocRecord.storage_location_code;
          }
        }
      }
      
      // Priority 5: Material Plant master default storage location
      if (!storageLocation) {
        const materialPlant = await db.MaterialPlant.findOne({
          where: {
            material_id: item.material_id,
            plant_id: grn.plant_id,
            status: 'ACTIVE'
          },
          transaction
        });
        if (materialPlant && materialPlant.storage_location) {
          storageLocRecord = await db.StorageLocation.findOne({
            where: {
              company_id: grn.company_id,
              plant_id: grn.plant_id,
              storage_location_code: materialPlant.storage_location,
              status: 'ACTIVE'
            },
            transaction
          });
          if (storageLocRecord) {
            storageLocation = materialPlant.storage_location;
          }
        }
      }
      
      // Priority 6: Final fallback: First active WAREHOUSE storage location
      if (!storageLocation) {
        storageLocRecord = await db.StorageLocation.findOne({
          where: {
            company_id: grn.company_id,
            plant_id: grn.plant_id,
            storage_location_type: 'WAREHOUSE',
            status: 'ACTIVE'
          },
          order: [['id', 'ASC']],
          transaction
        });
        if (storageLocRecord) {
          storageLocation = storageLocRecord.storage_location_code;
        }
      }
      
      // Validate storage location exists and is active
      if (!storageLocation || !storageLocRecord) {
        await transaction.rollback();
        return sendError(res, `Storage location is required for material ${item.material?.material_number || item.material_id}. Please select a storage location in the GRN form or configure it in Material Master or Material Plant.`, 400);
      }

      // Create stock ledger entry - RECEIPT (incoming stock from vendor)
      await stockLedgerService.createLedgerEntry({
        company_id: grn.company_id,
        plant_id: grn.plant_id,
        material_id: item.material_id,
        transaction_date: grn.grn_date,
        transaction_type: 'RECEIPT',
        // InventoryStockLedger.reference_type is an ENUM; use standard codes for consistency across reports/filters
        reference_type: 'GRN',
        reference_id: grn.id,
        reference_number: grn.grn_number,
        storage_location: storageLocation,
        quantity_in: quantity,
        quantity_out: 0,
        unit_cost: unitPrice,
        total_cost: totalAmount,
        batch_number: item.batch_number || null,
        remarks: `GRN: ${grn.grn_number} - Received from vendor`,
        created_by: userId
      }, transaction);
    }

    await grn.update({
      status: 'POSTED',
      posted_at: new Date(),
      posted_by: userId,
      updated_by: userId
    }, { transaction });

    // Safety backfill: if a GRN was created without linking Gate Entry,
    // attach the latest GRN_READY entry for this PO now.
    if (grn.po_id) {
      const existingLinkedGateEntry = await db.GateEntry.findOne({
        where: {
          company_id: grn.company_id,
          plant_id: grn.plant_id,
          grn_id: grn.id
        },
        transaction
      });

      if (!existingLinkedGateEntry) {
        const pendingGateEntry = await db.GateEntry.findOne({
          where: {
            company_id: grn.company_id,
            plant_id: grn.plant_id,
            po_id: grn.po_id,
            status: { [Op.in]: ['GRN_READY', 'QC_PENDING'] },
            grn_id: null
          },
          order: [['id', 'DESC']],
          transaction
        });

        if (pendingGateEntry) {
          await pendingGateEntry.update({
            grn_id: grn.id,
            status: 'GRN_CREATED',
            updated_by: userId
          }, { transaction });
        }
      }
    }

    await glPostingService.postGRNToGL(grn, userId);

    await transaction.commit();

    const updatedGRN = await db.GoodsReceiptNote.findByPk(id, {
      include: [
        { model: db.StorageLocation, as: 'storageLocation' },
        { 
          model: db.GoodsReceiptNoteItem, 
          as: 'items', 
          include: [
            { model: db.Material, as: 'material' }, 
            { model: db.UOM, as: 'uom' },
            { model: db.StorageLocation, as: 'storageLocation' }
          ] 
        }
      ]
    });

    sendResponse(res, 200, true, 'Goods Receipt Note posted successfully', updatedGRN);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const grn = await db.GoodsReceiptNote.findByPk(id);
  if (!grn) {
    return sendError(res, 'Goods Receipt Note not found', 404);
  }

  if (grn.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Goods Receipt Notes can be deleted', 400);
  }

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

  try {
    await db.GoodsReceiptNoteItem.destroy({ where: { grn_id: id }, transaction });
    await grn.destroy({ transaction });
    await transaction.commit();

    sendResponse(res, 200, true, 'Goods Receipt Note deleted successfully', null);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

