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

const SALES_INVOICE_STATUSES = ['DRAFT', 'APPROVED', 'CANCELLED'];
const DISPATCH_STATUSES = ['NOT_DISPATCHED', 'PARTIALLY_DISPATCHED', 'FULLY_DISPATCHED'];

let salesInvoiceTableSchema = null;
let fgDispatchTableSchema = null;
const hasCol = (schema, col) => Boolean(schema && Object.prototype.hasOwnProperty.call(schema, col));

const getSalesInvoiceSchema = async () => {
  if (salesInvoiceTableSchema) return salesInvoiceTableSchema;
  try {
    salesInvoiceTableSchema = await db.sequelize.getQueryInterface().describeTable('sales_invoice');
  } catch (_) {
    salesInvoiceTableSchema = {};
  }
  return salesInvoiceTableSchema;
};

const getFGDispatchSchema = async () => {
  if (fgDispatchTableSchema) return fgDispatchTableSchema;
  try {
    fgDispatchTableSchema = await db.sequelize.getQueryInterface().describeTable('fg_dispatch');
  } catch (_) {
    fgDispatchTableSchema = {};
  }
  return fgDispatchTableSchema;
};

const parseCreditDays = (paymentTerms) => {
  if (paymentTerms === null || paymentTerms === undefined) return 0;
  const match = String(paymentTerms).match(/\d+/);
  return match ? parseInt(match[0], 10) || 0 : 0;
};

const toDateOnly = (date) => {
  if (!date) return null;
  const d = new Date(date);
  if (Number.isNaN(d.getTime())) return null;
  const pad = (n) => String(n).padStart(2, '0');
  return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())}`;
};

const deriveDueDate = (invoiceDate, paymentTerms) => {
  const base = new Date(invoiceDate || new Date());
  if (Number.isNaN(base.getTime())) return null;
  const creditDays = parseCreditDays(paymentTerms);
  base.setDate(base.getDate() + Math.max(creditDays, 0));
  return toDateOnly(base);
};

const deriveGstStateCodeFromGSTIN = (gstin) => {
  const value = String(gstin || '').trim();
  const m = value.match(/^(\d{2})/);
  return m ? m[1] : null;
};

const resolveGstType = async ({ companyId, plantId, customerId, customerGSTINId = null, transaction = null }) => {
  const companyGST = await db.GSTIN.findOne({
    where: {
      company_id: companyId,
      status: 'ACTIVE',
      [Op.or]: [
        plantId ? { plant_id: plantId } : null,
        { plant_id: null }
      ].filter(Boolean)
    },
    attributes: ['id', 'gstin', 'is_primary', 'state_id'],
    order: [['is_primary', 'DESC'], ['id', 'ASC']],
    transaction
  });

  let customerGST = null;
  if (customerGSTINId) {
    customerGST = await db.BPGSTIN.findOne({
      where: { id: customerGSTINId, bp_id: customerId },
      attributes: ['id', 'gstin', 'is_default', 'state_id'],
      transaction
    });
  }
  if (!customerGST) {
    customerGST = await db.BPGSTIN.findOne({
      where: { bp_id: customerId, status: 'ACTIVE' },
      attributes: ['id', 'gstin', 'is_default', 'state_id'],
      order: [['is_default', 'DESC'], ['id', 'ASC']],
      transaction
    });
  }

  const companyCode = deriveGstStateCodeFromGSTIN(companyGST?.gstin);
  const customerCode = deriveGstStateCodeFromGSTIN(customerGST?.gstin);
  if (!companyCode || !customerCode) return null;
  return companyCode === customerCode ? 'CGST_SGST' : 'IGST';
};

const getAvailableFGStockForItem = async ({ companyId, plantId, fiscalYearId, materialId, transaction = null }) => {
  const ledgerTotals = await db.FGStockLedger.findOne({
    where: {
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      item_id: materialId
    },
    attributes: [
      [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('inward_qty')), 0), 'total_inward'],
      [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('outward_qty')), 0), 'total_outward']
    ],
    transaction
  });
  const inward = toNum(ledgerTotals?.get?.('total_inward'));
  const outward = toNum(ledgerTotals?.get?.('total_outward'));
  return Math.max(inward - outward, 0);
};

const getProducedQtyForItem = async ({ companyId, plantId, fiscalYearId, materialId, transaction = null }) => {
  const total = await db.ProductionFGInward.sum('inward_entry_qty', {
    where: {
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      finished_material_id: materialId
    },
    transaction
  });
  return Math.max(toNum(total), 0);
};

const getPOQuantitySummary = async ({ poId, excludeInvoiceId = null, transaction = null }) => {
  if (!Number.isFinite(parseInt(poId, 10)) || parseInt(poId, 10) <= 0) {
    return { totalOrderQty: 0, totalInvoicedQty: 0, balanceQty: 0, latestInvoiceNo: null };
  }
  const normalizedPoId = parseInt(poId, 10);

  const totalOrderQty = toNum(await db.CustomerPOItem.sum('quantity', {
    where: { po_id: normalizedPoId },
    transaction
  }));

  const invoiceWhere = {
    customer_po_id: normalizedPoId,
    status: { [Op.ne]: 'CANCELLED' }
  };
  if (Number.isFinite(parseInt(excludeInvoiceId, 10)) && parseInt(excludeInvoiceId, 10) > 0) {
    invoiceWhere.id = { [Op.ne]: parseInt(excludeInvoiceId, 10) };
  }

  const invoiceRows = await db.SalesInvoice.findAll({
    where: invoiceWhere,
    attributes: ['id', 'invoice_number', 'invoice_date'],
    include: [{ model: db.SalesInvoiceItem, as: 'items', attributes: ['quantity'], required: false }],
    order: [['invoice_date', 'DESC'], ['id', 'DESC']],
    transaction
  });

  let totalInvoicedQty = 0;
  let latestInvoiceNo = null;
  invoiceRows.forEach((inv, index) => {
    if (index === 0) latestInvoiceNo = inv.invoice_number || null;
    totalInvoicedQty += (inv.items || []).reduce((sum, it) => sum + toNum(it.quantity), 0);
  });

  return {
    totalOrderQty,
    totalInvoicedQty,
    balanceQty: Math.max(totalOrderQty - totalInvoicedQty, 0),
    latestInvoiceNo
  };
};

const validateInvoiceItemsAgainstStock = async ({ companyId, plantId, fiscalYearId, items, transaction = null }) => {
  const grouped = new Map();
  for (const row of items || []) {
    const materialId = parseInt(row.material_id, 10);
    const qty = toNum(row.quantity);
    if (!Number.isFinite(materialId) || materialId <= 0 || qty <= 0) continue;
    grouped.set(materialId, (grouped.get(materialId) || 0) + qty);
  }

  for (const [materialId, totalQty] of grouped.entries()) {
    const [availableStock, producedQty] = await Promise.all([
      getAvailableFGStockForItem({ companyId, plantId, fiscalYearId, materialId, transaction }),
      getProducedQtyForItem({ companyId, plantId, fiscalYearId, materialId, transaction })
    ]);

    if (totalQty > availableStock) {
      throw new Error(`Invoice quantity cannot exceed available FG stock (${availableStock}) for item ${materialId}`);
    }
    if (totalQty > producedQty) {
      throw new Error(`Invoice quantity cannot exceed produced quantity (${producedQty}) for item ${materialId}`);
    }
  }
};

const buildTotals = (items = []) => {
  let subTotal = 0;
  let discountAmount = 0;
  let cgstAmount = 0;
  let sgstAmount = 0;
  let igstAmount = 0;
  let cessAmount = 0;
  let taxAmount = 0;

  items.forEach((item) => {
    const quantity = toNum(item.quantity);
    const unitPrice = toNum(item.unit_price);
    const discountPercent = toNum(item.discount_percentage);
    const taxRate = toNum(item.tax_rate);
    const lineTotal = quantity * unitPrice;
    const lineDiscount = (lineTotal * discountPercent) / 100;
    const taxableAmount = Math.max(lineTotal - lineDiscount, 0);

    const lineTax = (taxableAmount * taxRate) / 100;
    const lineCGST = toNum(item.cgst_rate) > 0 ? (taxableAmount * toNum(item.cgst_rate)) / 100 : toNum(item.cgst_amount);
    const lineSGST = toNum(item.sgst_rate) > 0 ? (taxableAmount * toNum(item.sgst_rate)) / 100 : toNum(item.sgst_amount);
    const lineIGST = toNum(item.igst_rate) > 0 ? (taxableAmount * toNum(item.igst_rate)) / 100 : toNum(item.igst_amount);
    const lineCESS = toNum(item.cess_rate) > 0 ? (taxableAmount * toNum(item.cess_rate)) / 100 : toNum(item.cess_amount);

    subTotal += lineTotal;
    discountAmount += lineDiscount;
    cgstAmount += lineCGST;
    sgstAmount += lineSGST;
    igstAmount += lineIGST;
    cessAmount += lineCESS;
    taxAmount += lineTax || (lineCGST + lineSGST + lineIGST + lineCESS);
  });

  return {
    sub_total: subTotal,
    discount_amount: discountAmount,
    cgst_amount: cgstAmount,
    sgst_amount: sgstAmount,
    igst_amount: igstAmount,
    cess_amount: cessAmount,
    tax_amount: taxAmount,
    round_off: 0,
    total_amount: Math.round(subTotal - discountAmount + taxAmount)
  };
};

const generateInvoiceNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'SALES_INVOICE', fiscalYearId, transaction);
  } catch (err) {
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `INV${year}`;
    const lastInvoice = await db.SalesInvoice.findOne({
      where: { company_id: companyId, invoice_number: { [Op.like]: `${prefix}%` } },
      order: [['invoice_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (lastInvoice) {
      const lastSequence = parseInt(String(lastInvoice.invoice_number || '').slice(-4), 10) || 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, 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] = [
      { invoice_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.SalesInvoice.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'] },
      { model: db.CustomerPO, as: 'customerPO', attributes: ['id', 'po_number'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['invoice_date', 'DESC'], ['id', 'DESC']]
  });

  const invoiceIds = (records.rows || []).map((r) => r.id).filter(Boolean);
  const customerPOIds = (records.rows || []).map((r) => r.customer_po_id).filter(Boolean);

  const invoiceQtyMap = new Map();
  const orderQtyMap = new Map();
  const poInvoicedQtyMap = new Map();
  const workOrderNoByInvoiceId = new Map();

  if (invoiceIds.length > 0) {
    const invoiceQtyRows = await db.SalesInvoiceItem.findAll({
      where: { invoice_id: { [Op.in]: invoiceIds } },
      attributes: [
        'invoice_id',
        [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('quantity')), 0), 'invoice_qty']
      ],
      group: ['invoice_id'],
      raw: true
    });
    invoiceQtyRows.forEach((row) => {
      invoiceQtyMap.set(parseInt(row.invoice_id, 10), toNum(row.invoice_qty));
    });
  }

  if (customerPOIds.length > 0) {
    const orderQtyRows = await db.CustomerPOItem.findAll({
      where: { po_id: { [Op.in]: customerPOIds } },
      attributes: [
        'po_id',
        [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('quantity')), 0), 'order_qty']
      ],
      group: ['po_id'],
      raw: true
    });
    orderQtyRows.forEach((row) => {
      orderQtyMap.set(parseInt(row.po_id, 10), toNum(row.order_qty));
    });

    const invoicedRows = await db.SalesInvoiceItem.findAll({
      attributes: [
        [db.sequelize.col('salesInvoice.customer_po_id'), 'customer_po_id'],
        [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('SalesInvoiceItem.quantity')), 0), 'total_invoiced_qty']
      ],
      include: [
        {
          model: db.SalesInvoice,
          as: 'salesInvoice',
          attributes: [],
          where: {
            customer_po_id: { [Op.in]: customerPOIds },
            status: { [Op.ne]: 'CANCELLED' }
          },
          required: true
        }
      ],
      group: ['salesInvoice.customer_po_id'],
      raw: true
    });
    invoicedRows.forEach((row) => {
      poInvoicedQtyMap.set(parseInt(row.customer_po_id, 10), toNum(row.total_invoiced_qty));
    });
  }

  if (invoiceIds.length > 0) {
    const dispatchSchema = await getFGDispatchSchema();
    if (hasCol(dispatchSchema, 'invoice_id')) {
      const dispatchRows = await db.FGDispatch.findAll({
        where: {
          invoice_id: { [Op.in]: invoiceIds },
          status: { [Op.ne]: 'CANCELLED' }
        },
        attributes: ['id', 'invoice_id', 'work_order_id', 'dispatch_date'],
        include: [
          { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false }
        ],
        order: [['dispatch_date', 'DESC'], ['id', 'DESC']]
      });

      dispatchRows.forEach((row) => {
        const invoiceId = parseInt(row.invoice_id, 10);
        if (!Number.isFinite(invoiceId) || invoiceId <= 0) return;
        if (!workOrderNoByInvoiceId.has(invoiceId)) {
          workOrderNoByInvoiceId.set(invoiceId, row.workOrder?.work_order_number || '');
        }
      });
    }
  }

  records.rows = (records.rows || []).map((row) => {
    const plain = row.toJSON();
    const totalOrderQty = toNum(orderQtyMap.get(plain.customer_po_id) || 0);
    const totalPOInvoicedQty = toNum(poInvoicedQtyMap.get(plain.customer_po_id) || 0);
    const poBalanceQty = plain.customer_po_id ? Math.max(totalOrderQty - totalPOInvoicedQty, 0) : 0;
    return {
      ...plain,
      order_qty: totalOrderQty,
      invoice_qty: toNum(invoiceQtyMap.get(plain.id) || 0),
      balance_qty: poBalanceQty,
      work_order_no: workOrderNoByInvoiceId.get(plain.id) || ''
    };
  });

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

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

  const invoice = await db.SalesInvoice.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.BusinessPartner, as: 'customer' },
      { model: db.BPAddress, as: 'customerAddress' },
      { model: db.BPGSTIN, as: 'customerGSTIN' },
      { model: db.CustomerPO, as: 'customerPO' },
      { model: db.SalesOrder, as: 'salesOrder' },
      { model: db.Currency, as: 'currency' },
      {
        model: db.SalesInvoiceItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['item_number', 'ASC']]
      }
    ]
  });

  if (!invoice) {
    return sendError(res, 'Sales Invoice not found', 404);
  }

  sendResponse(res, 200, true, 'Sales Invoice retrieved successfully', invoice);
});

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.invoice_number) {
      data.invoice_number = await generateInvoiceNumber(companyId, data.fiscal_year_id, transaction);
    }

    if (data.invoice_number) {
      const duplicate = await db.SalesInvoice.findOne({
        where: {
          company_id: companyId,
          plant_id: data.plant_id,
          fiscal_year_id: data.fiscal_year_id,
          invoice_number: data.invoice_number
        },
        transaction
      });
      if (duplicate) {
        throw new Error('Invoice number must be unique');
      }
    }

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

    const { items, ...invoiceData } = data;
    if (!Array.isArray(items) || items.length === 0) {
      throw new Error('Invoice must contain at least one item');
    }

    await validateInvoiceItemsAgainstStock({
      companyId,
      plantId: data.plant_id,
      fiscalYearId: data.fiscal_year_id,
      items,
      transaction
    });

    const status = String(data.status || 'DRAFT').toUpperCase();
    if (!SALES_INVOICE_STATUSES.includes(status)) {
      throw new Error('Invalid status');
    }
    if (status === 'APPROVED' && items.length === 0) {
      throw new Error('Invoice cannot be approved without item rows');
    }

    const incomingInvoiceQty = (items || []).reduce((sum, it) => sum + toNum(it.quantity), 0);
    if (Number.isFinite(parseInt(data.customer_po_id, 10)) && parseInt(data.customer_po_id, 10) > 0) {
      const poSummary = await getPOQuantitySummary({ poId: data.customer_po_id, transaction });
      if (incomingInvoiceQty > poSummary.balanceQty) {
        throw new Error(`Invoice quantity (${incomingInvoiceQty}) cannot exceed PO balance quantity (${poSummary.balanceQty})`);
      }
      if (!data.reference_invoice_no && poSummary.latestInvoiceNo) {
        data.reference_invoice_no = poSummary.latestInvoiceNo;
      }
    }

    const schema = await getSalesInvoiceSchema();
    const dueDate = deriveDueDate(data.invoice_date, data.payment_terms);
    const gstType = await resolveGstType({
      companyId,
      plantId: data.plant_id,
      customerId: data.customer_id,
      customerGSTINId: data.customer_gstin_id || null,
      transaction
    });

    Object.assign(invoiceData, buildTotals(items));
    invoiceData.status = status;
    if (hasCol(schema, 'due_date')) invoiceData.due_date = dueDate;
    if (hasCol(schema, 'gst_type')) invoiceData.gst_type = data.gst_type || gstType || null;
    if (hasCol(schema, 'customer_po_no')) invoiceData.customer_po_no = data.customer_po_no || null;
    if (hasCol(schema, 'reference_invoice_no')) invoiceData.reference_invoice_no = data.reference_invoice_no || null;
    if (hasCol(schema, 'billing_address')) invoiceData.billing_address = data.billing_address || null;
    if (hasCol(schema, 'shipping_address')) invoiceData.shipping_address = data.shipping_address || null;
    if (hasCol(schema, 'transport_details')) invoiceData.transport_details = data.transport_details || null;
    if (hasCol(schema, 'company_bank_detail_id')) invoiceData.company_bank_detail_id = data.company_bank_detail_id || null;
    if (hasCol(schema, 'bank_account_holder_name')) invoiceData.bank_account_holder_name = data.bank_account_holder_name || null;
    if (hasCol(schema, 'bank_name')) invoiceData.bank_name = data.bank_name || null;
    if (hasCol(schema, 'bank_account_no')) invoiceData.bank_account_no = data.bank_account_no || null;
    if (hasCol(schema, 'bank_branch')) invoiceData.bank_branch = data.bank_branch || null;
    if (hasCol(schema, 'bank_ifsc_code')) invoiceData.bank_ifsc_code = data.bank_ifsc_code || null;
    if (hasCol(schema, 'delivery_note_date')) invoiceData.delivery_note_date = data.delivery_note_date || null;
    if (hasCol(schema, 'other_references')) invoiceData.other_references = data.other_references || null;
    if (hasCol(schema, 'motor_vehicle_no')) invoiceData.motor_vehicle_no = data.motor_vehicle_no || null;
    if (hasCol(schema, 'mode_terms_of_payment')) invoiceData.mode_terms_of_payment = data.mode_terms_of_payment || null;
    if (hasCol(schema, 'terms_of_delivery')) invoiceData.terms_of_delivery = data.terms_of_delivery || 'By Road';
    if (hasCol(schema, 'dispatch_status')) invoiceData.dispatch_status = 'NOT_DISPATCHED';
    if (hasCol(schema, 'dispatched_qty')) invoiceData.dispatched_qty = 0;

    const invoice = await db.SalesInvoice.create(invoiceData, { transaction });

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = toNum(item.quantity);
        const unitPrice = toNum(item.unit_price);
        const discountPercent = toNum(item.discount_percentage);
        const taxRate = toNum(item.tax_rate);
        const cgstRate = toNum(item.cgst_rate);
        const sgstRate = toNum(item.sgst_rate);
        const igstRate = toNum(item.igst_rate);
        const cessRate = toNum(item.cess_rate);

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

        const lineCGST = (taxableAmount * cgstRate) / 100;
        const lineSGST = (taxableAmount * sgstRate) / 100;
        const lineIGST = (taxableAmount * igstRate) / 100;
        const lineCess = (taxableAmount * cessRate) / 100;
        const lineTax = taxRate > 0 ? (taxableAmount * taxRate) / 100 : (lineCGST + lineSGST + lineIGST + lineCess);

        await db.SalesInvoiceItem.create({
          invoice_id: invoice.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 || (cgstRate + sgstRate + igstRate + cessRate),
          cgst_rate: cgstRate,
          cgst_amount: lineCGST,
          sgst_rate: sgstRate,
          sgst_amount: lineSGST,
          igst_rate: igstRate,
          igst_amount: lineIGST,
          cess_rate: cessRate,
          cess_amount: lineCess,
          tax_amount: lineTax,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code,
          batch_number: item.batch_number,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

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

    sendResponse(res, 201, true, 'Sales Invoice created successfully', createdInvoice);
  } 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 invoice = await db.SalesInvoice.findByPk(id);

  if (!invoice) {
    return sendError(res, 'Sales Invoice not found', 404);
  }

  if (invoice.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Sales Invoices can be updated', 400);
  }

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

  try {
    const schema = await getSalesInvoiceSchema();
    const dispatchSchema = await getFGDispatchSchema();
    if (hasCol(schema, 'dispatch_status') && DISPATCH_STATUSES.includes(String(invoice.dispatch_status || '').toUpperCase()) && String(invoice.dispatch_status).toUpperCase() !== 'NOT_DISPATCHED') {
      throw new Error('Cannot edit invoice after dispatch started');
    }
    if (hasCol(dispatchSchema, 'invoice_id')) {
      const dispatchCount = await db.FGDispatch.count({
        where: {
          invoice_id: invoice.id,
          status: { [Op.ne]: 'CANCELLED' }
        },
        transaction
      });
      if (dispatchCount > 0) {
        throw new Error('Cannot edit invoice after dispatch started');
      }
    }

    data.updated_by = userId;
    const { items, ...invoiceData } = data;
    if (!Array.isArray(items) || items.length === 0) {
      throw new Error('Invoice must contain at least one item');
    }

    await validateInvoiceItemsAgainstStock({
      companyId: invoice.company_id,
      plantId: invoice.plant_id,
      fiscalYearId: invoice.fiscal_year_id,
      items,
      transaction
    });

    const dueDate = deriveDueDate(data.invoice_date || invoice.invoice_date, data.payment_terms);
    const gstType = await resolveGstType({
      companyId: invoice.company_id,
      plantId: invoice.plant_id,
      customerId: data.customer_id || invoice.customer_id,
      customerGSTINId: data.customer_gstin_id || invoice.customer_gstin_id || null,
      transaction
    });

    Object.assign(invoiceData, buildTotals(items));
    const incomingInvoiceQty = (items || []).reduce((sum, it) => sum + toNum(it.quantity), 0);
    const candidatePOId = data.customer_po_id || invoice.customer_po_id;
    if (Number.isFinite(parseInt(candidatePOId, 10)) && parseInt(candidatePOId, 10) > 0) {
      const poSummary = await getPOQuantitySummary({
        poId: candidatePOId,
        excludeInvoiceId: invoice.id,
        transaction
      });
      if (incomingInvoiceQty > poSummary.balanceQty) {
        throw new Error(`Invoice quantity (${incomingInvoiceQty}) cannot exceed PO balance quantity (${poSummary.balanceQty})`);
      }
      if (!data.reference_invoice_no && poSummary.latestInvoiceNo) {
        data.reference_invoice_no = poSummary.latestInvoiceNo;
      }
    }
    if (hasCol(schema, 'due_date')) invoiceData.due_date = dueDate;
    if (hasCol(schema, 'gst_type')) invoiceData.gst_type = data.gst_type || gstType || null;
    if (hasCol(schema, 'customer_po_no')) invoiceData.customer_po_no = data.customer_po_no || null;
    if (hasCol(schema, 'reference_invoice_no')) invoiceData.reference_invoice_no = data.reference_invoice_no || null;
    if (hasCol(schema, 'billing_address')) invoiceData.billing_address = data.billing_address || null;
    if (hasCol(schema, 'shipping_address')) invoiceData.shipping_address = data.shipping_address || null;
    if (hasCol(schema, 'transport_details')) invoiceData.transport_details = data.transport_details || null;
    if (hasCol(schema, 'company_bank_detail_id')) invoiceData.company_bank_detail_id = data.company_bank_detail_id || null;
    if (hasCol(schema, 'bank_account_holder_name')) invoiceData.bank_account_holder_name = data.bank_account_holder_name || null;
    if (hasCol(schema, 'bank_name')) invoiceData.bank_name = data.bank_name || null;
    if (hasCol(schema, 'bank_account_no')) invoiceData.bank_account_no = data.bank_account_no || null;
    if (hasCol(schema, 'bank_branch')) invoiceData.bank_branch = data.bank_branch || null;
    if (hasCol(schema, 'bank_ifsc_code')) invoiceData.bank_ifsc_code = data.bank_ifsc_code || null;
    if (hasCol(schema, 'delivery_note_date')) invoiceData.delivery_note_date = data.delivery_note_date || null;
    if (hasCol(schema, 'other_references')) invoiceData.other_references = data.other_references || null;
    if (hasCol(schema, 'motor_vehicle_no')) invoiceData.motor_vehicle_no = data.motor_vehicle_no || null;
    if (hasCol(schema, 'mode_terms_of_payment')) invoiceData.mode_terms_of_payment = data.mode_terms_of_payment || null;
    if (hasCol(schema, 'terms_of_delivery')) invoiceData.terms_of_delivery = data.terms_of_delivery || 'By Road';

    await invoice.update(invoiceData, { transaction });

    await db.SalesInvoiceItem.destroy({ where: { invoice_id: invoice.id }, transaction });

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = toNum(item.quantity);
        const unitPrice = toNum(item.unit_price);
        const discountPercent = toNum(item.discount_percentage);
        const taxRate = toNum(item.tax_rate);
        const cgstRate = toNum(item.cgst_rate);
        const sgstRate = toNum(item.sgst_rate);
        const igstRate = toNum(item.igst_rate);
        const cessRate = toNum(item.cess_rate);

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

        const lineCGST = (taxableAmount * cgstRate) / 100;
        const lineSGST = (taxableAmount * sgstRate) / 100;
        const lineIGST = (taxableAmount * igstRate) / 100;
        const lineCess = (taxableAmount * cessRate) / 100;
        const lineTax = taxRate > 0 ? (taxableAmount * taxRate) / 100 : (lineCGST + lineSGST + lineIGST + lineCess);

        await db.SalesInvoiceItem.create({
          invoice_id: invoice.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 || (cgstRate + sgstRate + igstRate + cessRate),
          cgst_rate: cgstRate,
          cgst_amount: lineCGST,
          sgst_rate: sgstRate,
          sgst_amount: lineSGST,
          igst_rate: igstRate,
          igst_amount: lineIGST,
          cess_rate: cessRate,
          cess_amount: lineCess,
          tax_amount: lineTax,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code,
          batch_number: item.batch_number,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

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

    sendResponse(res, 200, true, 'Sales Invoice updated successfully', updatedInvoice);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const invoice = await db.SalesInvoice.findByPk(id);

  if (!invoice) {
    return sendError(res, 'Sales Invoice not found', 404);
  }

  if (invoice.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Sales Invoices can be deleted', 400);
  }

  await db.SalesInvoiceItem.destroy({ where: { invoice_id: invoice.id } });
  await invoice.destroy();

  sendResponse(res, 200, true, 'Sales Invoice deleted successfully', null);
});

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

  const invoice = await db.SalesInvoice.findByPk(id, {
    include: [
      { model: db.SalesInvoiceItem, as: 'items', include: [{ model: db.Material, as: 'material' }] }
    ]
  });

  if (!invoice) {
    return sendError(res, 'Sales Invoice not found', 404);
  }

  const validStatuses = SALES_INVOICE_STATUSES;
  if (!validStatuses.includes(status)) {
    return sendError(res, 'Invalid status', 400);
  }

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

  try {
    const updateData = { status, updated_by: userId };

    if (status === 'APPROVED' && (!Array.isArray(invoice.items) || invoice.items.length === 0)) {
      throw new Error('Invoice cannot be approved without item rows');
    }
    await invoice.update(updateData, { transaction });
    if (status === 'APPROVED') {
      await glPostingService.postSalesInvoiceToGL(invoice, userId);
    }

    await transaction.commit();

    const updatedInvoice = await db.SalesInvoice.findByPk(invoice.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.BusinessPartner, as: 'customer' },
        { model: db.SalesInvoiceItem, as: 'items', include: [{ model: db.Material, as: 'material' }, { model: db.UOM, as: 'uom' }] }
      ]
    });

    sendResponse(res, 200, true, 'Sales Invoice status updated successfully', updatedInvoice);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.getAvailableStockForInvoiceItem = asyncHandler(async (req, res) => {
  const companyId = req.query.companyId || req.user?.companyId;
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);
  const itemId = parseInt(req.query.itemId, 10);

  if (!companyId || !Number.isFinite(plantId) || !Number.isFinite(fiscalYearId) || !Number.isFinite(itemId) || itemId <= 0) {
    return sendError(res, 'companyId, plantId, fiscalYearId and itemId are required', 400);
  }

  const [availableStock, producedQty] = await Promise.all([
    getAvailableFGStockForItem({ companyId, plantId, fiscalYearId, materialId: itemId }),
    getProducedQtyForItem({ companyId, plantId, fiscalYearId, materialId: itemId })
  ]);

  sendResponse(res, 200, true, 'Available stock retrieved successfully', {
    item_id: itemId,
    available_stock: availableStock,
    produced_qty: producedQty
  });
});

exports.getEligibleCustomerPOs = asyncHandler(async (req, res) => {
  const companyId = req.query.companyId || req.user?.companyId;
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);
  const customerId = parseInt(req.query.customerId, 10);
  const includePoId = parseInt(req.query.includePoId, 10);

  if (!companyId || !Number.isFinite(plantId) || !Number.isFinite(fiscalYearId)) {
    return sendError(res, 'companyId, plantId and fiscalYearId are required', 400);
  }

  const where = {
    company_id: companyId,
    plant_id: plantId,
    fiscal_year_id: fiscalYearId,
    status: { [Op.in]: ['RELEASED', 'APPROVED', 'PARTIALLY_DELIVERED'] }
  };
  if (Number.isFinite(customerId) && customerId > 0) where.customer_id = customerId;

  const records = await db.CustomerPO.findAll({
    where,
    attributes: ['id', 'po_number', 'po_date', 'customer_id', 'payment_terms', 'customer_address_id'],
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false }
    ],
    order: [['po_date', 'DESC'], ['id', 'DESC']]
  });

  const poIds = records.map((r) => r.id).filter(Boolean);
  const orderQtyMap = new Map();
  const invoicedQtyMap = new Map();
  if (poIds.length > 0) {
    const qtyRows = await db.CustomerPOItem.findAll({
      where: { po_id: { [Op.in]: poIds } },
      attributes: [
        'po_id',
        [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('quantity')), 0), 'total_order_qty']
      ],
      group: ['po_id'],
      raw: true
    });
    qtyRows.forEach((row) => {
      orderQtyMap.set(parseInt(row.po_id, 10), toNum(row.total_order_qty));
    });

    const invoicedRows = await db.SalesInvoiceItem.findAll({
      attributes: [
        [db.sequelize.col('salesInvoice.customer_po_id'), 'customer_po_id'],
        [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('SalesInvoiceItem.quantity')), 0), 'total_invoiced_qty']
      ],
      include: [
        {
          model: db.SalesInvoice,
          as: 'salesInvoice',
          attributes: [],
          where: {
            customer_po_id: { [Op.in]: poIds },
            status: { [Op.ne]: 'CANCELLED' }
          },
          required: true
        }
      ],
      group: ['salesInvoice.customer_po_id'],
      raw: true
    });
    invoicedRows.forEach((row) => {
      invoicedQtyMap.set(parseInt(row.customer_po_id, 10), toNum(row.total_invoiced_qty));
    });
  }

  const payload = records.map((po) => {
    const plain = po.toJSON();
    const totalOrderQty = toNum(orderQtyMap.get(plain.id) || 0);
    const totalInvoicedQty = toNum(invoicedQtyMap.get(plain.id) || 0);
    const balanceQty = Math.max(totalOrderQty - totalInvoicedQty, 0);
    return {
      ...plain,
      total_order_qty: totalOrderQty,
      total_invoiced_qty: totalInvoicedQty,
      balance_qty: balanceQty,
      latest_invoice_no: null
    };
  });

  const latestInvoices = await db.SalesInvoice.findAll({
    where: {
      customer_po_id: { [Op.in]: poIds.length ? poIds : [-1] },
      status: { [Op.ne]: 'CANCELLED' }
    },
    attributes: ['customer_po_id', 'invoice_number', 'invoice_date', 'id'],
    order: [['invoice_date', 'DESC'], ['id', 'DESC']]
  });
  const latestInvoiceByPO = new Map();
  latestInvoices.forEach((inv) => {
    const key = parseInt(inv.customer_po_id, 10);
    if (!latestInvoiceByPO.has(key)) latestInvoiceByPO.set(key, inv.invoice_number || null);
  });

  const filteredPayload = payload
    .map((po) => ({ ...po, latest_invoice_no: latestInvoiceByPO.get(parseInt(po.id, 10)) || null }))
    .filter((po) => po.balance_qty > 0 || (Number.isFinite(includePoId) && po.id === includePoId));

  sendResponse(res, 200, true, 'Eligible customer POs retrieved successfully', filteredPayload);
});

exports.getCustomerPOPrefill = asyncHandler(async (req, res) => {
  const customerPOId = parseInt(req.params.customerPOId, 10);
  if (!Number.isFinite(customerPOId) || customerPOId <= 0) {
    return sendError(res, 'Valid customer PO id is required', 400);
  }

  const po = await db.CustomerPO.findByPk(customerPOId, {
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'payment_terms'] },
      {
        model: db.BPAddress,
        as: 'customerAddress',
        attributes: ['id', 'address_line1', 'address_line2', 'pincode', 'state_id'],
        include: [{ model: db.State, as: 'state', attributes: ['id', 'state_name', 'gst_state_code'], required: false }]
      },
      {
        model: db.CustomerPOItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description', 'hsn_code', 'base_uom_id'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
        ]
      }
    ]
  });
  if (!po) return sendError(res, 'Customer PO not found', 404);

  const invoicedRows = await db.SalesInvoiceItem.findAll({
    attributes: [
      'material_id',
      [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('SalesInvoiceItem.quantity')), 0), 'total_invoiced_qty']
    ],
    include: [
      {
        model: db.SalesInvoice,
        as: 'salesInvoice',
        attributes: [],
        where: {
          customer_po_id: customerPOId,
          status: { [Op.ne]: 'CANCELLED' }
        },
        required: true
      }
    ],
    group: ['material_id'],
    raw: true
  });

  const invoicedByMaterial = new Map();
  invoicedRows.forEach((row) => {
    const materialId = parseInt(row.material_id, 10);
    if (Number.isFinite(materialId) && materialId > 0) {
      invoicedByMaterial.set(materialId, toNum(row.total_invoiced_qty));
    }
  });

  const plain = po.toJSON();
  plain.items = (plain.items || [])
    .map((item) => {
      const orderedQty = toNum(item.quantity);
      const alreadyInvoiced = toNum(invoicedByMaterial.get(parseInt(item.material_id, 10)) || 0);
      return {
        ...item,
        balance_quantity: Math.max(orderedQty - alreadyInvoiced, 0)
      };
    })
    .filter((item) => toNum(item.balance_quantity) > 0);

  sendResponse(res, 200, true, 'Customer PO prefill retrieved successfully', plain);
});

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

  const invoice = await db.SalesInvoice.findByPk(id, {
    include: [
      {
        model: db.Company,
        as: 'company',
        attributes: ['id', 'name', 'legal_name', 'email', 'phone']
      },
      { model: db.Plant, as: 'plant', attributes: ['id', 'name', 'email', 'phone'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      { model: db.BPAddress, as: 'customerAddress', attributes: ['address_line1', 'address_line2', 'pincode', 'email', 'phone', 'mobile'] },
      { model: db.BPGSTIN, as: 'customerGSTIN', attributes: ['gstin'] },
      { model: db.Currency, as: 'currency', attributes: ['currency_code', 'currency_symbol'] },
      {
        model: db.SalesInvoiceItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material', attributes: ['material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['uom_code'] }
        ]
      }
    ],
    order: [[{ model: db.SalesInvoiceItem, as: 'items' }, 'item_number', 'ASC']]
  });

  if (!invoice) {
    return sendError(res, 'Sales Invoice not found', 404);
  }

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

  const resolveFile = (relativePath) => {
    if (!relativePath) return null;
    const uploadPath = path.join(__dirname, '..', 'uploads', relativePath);
    if (fs.existsSync(uploadPath)) return uploadPath;
    const directPath = path.join(__dirname, '..', relativePath);
    if (fs.existsSync(directPath)) return directPath;
    return null;
  };

  const companyLogoPath = resolveFile(printHeader?.logo_path);
  const certLogoPath = resolveFile(printHeader?.quality_certification_logo_path);

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

  const fmtMoney = (value) => (toNum(value)).toFixed(2);

  const items = invoice.items || [];
  const taxBase = Math.max(toNum(invoice.sub_total) - toNum(invoice.discount_amount), 0);
  const cgstAmt = toNum(invoice.cgst_amount);
  const sgstAmt = toNum(invoice.sgst_amount);
  const igstAmt = toNum(invoice.igst_amount);
  const cgstRate = taxBase > 0 ? (cgstAmt / taxBase) * 100 : 0;
  const sgstRate = taxBase > 0 ? (sgstAmt / taxBase) * 100 : 0;
  const igstRate = taxBase > 0 ? (igstAmt / taxBase) * 100 : 0;

  const doc = new PDFDocument({ margin: 30, size: 'A4' });
  const safeInvoiceNo = String(invoice.invoice_number || invoice.id).replace(/[^\w.-]+/g, '_');
  const safeCustomer = String(invoice.customer?.name || 'Customer').replace(/[^\w.-]+/g, '_');
  const filename = `Sales_Invoice_${safeInvoiceNo}_${safeCustomer}.pdf`;

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

  const pageWidth = 595.28;
  const margin = 30;
  const contentWidth = pageWidth - (margin * 2);

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

  let y = margin;

  const drawHeader = () => {
    const headerHeight = 105;
    drawBorder(margin, y, contentWidth, headerHeight);

    const sidePadding = 4;
    const companyLogoBoxW = Math.min(200, contentWidth * 0.34);
    const companyLogoBoxH = headerHeight - 4;
    const logoX = margin + sidePadding;
    const logoY = y + 2;
    const certBoxW = Math.min(160, contentWidth * 0.26);
    const certBoxH = companyLogoBoxH;

    if (companyLogoPath) {
      try {
        doc.image(companyLogoPath, logoX, logoY, {
          cover: [companyLogoBoxW, companyLogoBoxH],
          align: 'center',
          valign: 'center'
        });
      } catch (_) {}
    }

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

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

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

    doc.fontSize(7).font('Helvetica');
    const addressY = printHeader?.former_name ? y + 32 : y + 22;
    if (printHeader?.address) {
      doc.text(printHeader.address, centerX, addressY, { width: centerWidth, align: 'center', lineGap: 1 });
    }

    const addressHeight = printHeader?.address
      ? doc.heightOfString(printHeader.address, { width: centerWidth, align: 'center', lineGap: 1 })
      : 0;
    let contactY = addressY + Math.max(10, addressHeight + 2);
    const headerEmail = printHeader?.email || invoice.company?.email || invoice.plant?.email || '';
    const headerPhone = printHeader?.phone || invoice.company?.phone || invoice.plant?.phone || '';

    const drawHeaderKV = (label, value) => {
      if (!value) return;
      doc.fontSize(7).fillColor('#000000');
      const labelStr = String(label);
      const valueStr = String(value);
      doc.font('Helvetica-Bold');
      const labelW = doc.widthOfString(labelStr);
      const colonStr = ' : ';
      const colonW = doc.widthOfString(colonStr);
      doc.font('Helvetica');
      const valueW = doc.widthOfString(valueStr);
      const x = centerX + Math.max(0, (centerWidth - (labelW + colonW + valueW)) / 2);
      doc.font('Helvetica-Bold');
      doc.text(labelStr, x, contactY, { lineBreak: false });
      doc.text(colonStr, x + labelW, contactY, { lineBreak: false });
      doc.font('Helvetica');
      doc.text(valueStr, x + labelW + colonW, contactY, { lineBreak: false });
      contactY += 9;
    };

    drawHeaderKV('Email', headerEmail);
    drawHeaderKV('Contact', headerPhone);
    drawHeaderKV('GSTIN', printHeader?.gstin || '');

    const certX = pageWidth - margin - certBoxW - sidePadding;
    const certY = y + 2;
    if (certLogoPath) {
      try {
        doc.image(certLogoPath, certX, certY, { fit: [certBoxW, certBoxH], align: 'center', valign: 'center' });
      } catch (_) {}
    }

    y += headerHeight;
  };

  drawHeader();

  drawBorder(margin, y, contentWidth, 20);
  doc.fontSize(12).font('Helvetica-Bold').text('SALES INVOICE', margin, y + 6, { width: contentWidth, align: 'center' });
  y += 20;

  const sectionHeight = 110;
  const leftW = contentWidth * 0.5;
  const rightW = contentWidth * 0.5;
  const rightX = margin + leftW;
  drawBorder(margin, y, leftW, sectionHeight);
  drawBorder(rightX, y, rightW, sectionHeight);

  let leftY = y + 6;
  doc.fontSize(8).font('Helvetica').text('Bill To,', margin + 5, leftY);
  leftY += 11;
  doc.font('Helvetica-Bold').text((invoice.customer?.name || '').toUpperCase(), margin + 5, leftY, { width: leftW - 10 });
  leftY += 12;
  doc.font('Helvetica').fontSize(7);
  const addressParts = [
    invoice.billing_address,
    invoice.customerAddress?.address_line1,
    invoice.customerAddress?.address_line2
  ].filter(Boolean);
  if (addressParts.length > 0) {
    doc.text(`Address: ${addressParts.join(', ')}`, margin + 5, leftY, { width: leftW - 10 });
    leftY += 20;
  }
  const customerPhone = invoice.customerAddress?.phone || invoice.customerAddress?.mobile || '';
  if (customerPhone) {
    doc.text(`Contact: ${customerPhone}`, margin + 5, leftY, { width: leftW - 10 });
    leftY += 10;
  }
  if (invoice.customerGSTIN?.gstin) {
    doc.text(`GSTIN: ${invoice.customerGSTIN.gstin}`, margin + 5, leftY, { width: leftW - 10 });
  }

  const details = [
    ['Invoice No', invoice.invoice_number || ''],
    ['Invoice Date', fmtDate(invoice.invoice_date)],
    ['Customer PO No', invoice.customer_po_no || invoice.customerPO?.po_number || ''],
    ['Due Date', fmtDate(invoice.due_date)],
    ['Status', invoice.status || ''],
    ['GST Type', invoice.gst_type || '']
  ];
  let rightY = y + 6;
  details.forEach(([label, value]) => {
    doc.fontSize(7).font('Helvetica-Bold').text(label, rightX + 5, rightY, { width: 70 });
    doc.font('Helvetica-Bold').text(':', rightX + 75, rightY, { width: 6 });
    doc.font('Helvetica').text(String(value || ''), rightX + 82, rightY, { width: rightW - 90 });
    rightY += 10;
  });
  y += sectionHeight;

  const tableHeaderH = 20;
  drawBorder(margin, y, contentWidth, tableHeaderH);
  const cols = [
    { key: 'no', label: 'No', w: 24 },
    { key: 'desc', label: 'Description', w: 200 },
    { key: 'hsn', label: 'HSN', w: 55 },
    { key: 'uom', label: 'UOM', w: 40 },
    { key: 'qty', label: 'Qty', w: 50 },
    { key: 'rate', label: 'Rate', w: 62 },
    { key: 'tax', label: 'Tax %', w: 50 },
    { key: 'line', label: 'Line Total', w: contentWidth - 481 }
  ];
  let cursorX = margin;
  cols.forEach((c, idx) => {
    if (idx > 0) doc.moveTo(cursorX, y).lineTo(cursorX, y + tableHeaderH).stroke();
    doc.fontSize(7).font('Helvetica-Bold').text(c.label, cursorX + 2, y + 6, { width: c.w - 4, align: c.key === 'desc' ? 'left' : 'center' });
    cursorX += c.w;
  });
  y += tableHeaderH;

  items.forEach((item, idx) => {
    const rowH = 18;
    if (y + rowH > 760) {
      doc.addPage();
      y = margin;
    }
    drawBorder(margin, y, contentWidth, rowH);
    let x = margin;
    const values = [
      String(idx + 1),
      `${item.material?.material_number || ''} ${item.material?.description || item.description || ''}`.trim(),
      item.hsn_code || '',
      item.uom?.uom_code || '',
      fmtMoney(item.quantity),
      fmtMoney(item.unit_price),
      fmtMoney(item.tax_rate),
      fmtMoney(item.total_amount)
    ];
    cols.forEach((c, i) => {
      if (i > 0) doc.moveTo(x, y).lineTo(x, y + rowH).stroke();
      doc.fontSize(7).font('Helvetica').text(values[i], x + 2, y + 5, { width: c.w - 4, align: c.key === 'desc' ? 'left' : 'right', ellipsis: true });
      x += c.w;
    });
    y += rowH;
  });

  const totalsHeight = 78;
  drawBorder(margin, y, contentWidth, totalsHeight);
  const labelX = margin + contentWidth - 220;
  const valueX = margin + contentWidth - 10;
  let ty = y + 8;
  const drawTotalRow = (label, value, bold = false) => {
    doc.fontSize(8).font(bold ? 'Helvetica-Bold' : 'Helvetica').text(label, labelX, ty, { width: 150, align: 'left' });
    doc.text(value, valueX - 100, ty, { width: 100, align: 'right' });
    ty += 12;
  };

  drawTotalRow('Sub Total', fmtMoney(invoice.sub_total));
  drawTotalRow('Discount', fmtMoney(invoice.discount_amount));
  if (igstAmt > 0) {
    drawTotalRow(`IGST (${fmtMoney(igstRate)}%)`, fmtMoney(igstAmt));
  } else {
    drawTotalRow(`CGST (${fmtMoney(cgstRate)}%)`, fmtMoney(cgstAmt));
    drawTotalRow(`SGST (${fmtMoney(sgstRate)}%)`, fmtMoney(sgstAmt));
  }
  drawTotalRow('Grand Total', fmtMoney(invoice.total_amount), true);

  doc.end();
});
