const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const { generateNextNumber } = require('../services/numberFormatService');
const statusValidationService = require('../services/statusValidationService');
const businessRuleValidationService = require('../services/businessRuleValidationService');
const purchaseNotificationService = require('../services/purchaseNotificationService');
const PDFDocument = require('pdfkit');
const path = require('path');
const fs = require('fs');
const {
  resolvePurchaseLineDescription,
  fetchMaterialsForLineDescription,
} = require('../utils/rawMaterialLineDescription');
const { computeLineWeightKg } = require('../utils/lineWeightKg');

const WARNING_THRESHOLD_PERCENT = 10;
const CRITICAL_THRESHOLD_PERCENT = 25;
const APPROVAL_THRESHOLD_PERCENT = 20;
const PO_EDIT_RATE_PERMISSION_CODES = [
  'PURCHASE.PO.EDIT_RATE',
  'PURCHASE_ORDER_EDIT_RATE',
  'PURCHASE.PURCHASE_ORDER.EDIT_RATE'
];

const canEditPostedPoRate = (user) => {
  if (!user) return false;
  if (user.isSuperAdmin || user.is_super_admin) return true;
  const rawPermissions = Array.isArray(user.permissions) ? user.permissions : [];
  if (rawPermissions.length === 0) return true; // fallback for deployments not embedding permission list in auth token
  const normalized = new Set(
    rawPermissions
      .map((p) => String(p?.permission_code || p?.code || p || '').trim().toUpperCase())
      .filter(Boolean)
  );
  return PO_EDIT_RATE_PERMISSION_CODES.some((code) => normalized.has(String(code).toUpperCase()));
};

const getCostingStatus = (percentage) => {
  const value = parseFloat(percentage);
  if (!Number.isFinite(value)) return 'NORMAL';
  if (value > CRITICAL_THRESHOLD_PERCENT) return 'CRITICAL';
  if (value > WARNING_THRESHOLD_PERCENT) return 'WARNING';
  return 'NORMAL';
};

const generatePurchaseCostingNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PURCHASE_COSTING', fiscalYearId, transaction);
  } catch (error) {
    console.warn('Purchase Costing number format missing, using fallback:', error.message);
    const now = new Date();
    const y = now.getFullYear();
    const m = String(now.getMonth() + 1).padStart(2, '0');
    const d = String(now.getDate()).padStart(2, '0');
    const stamp = String(Date.now()).slice(-6);
    const rand = String(Math.floor(Math.random() * 1000)).padStart(3, '0');
    return `PC-${y}${m}${d}-${stamp}${rand}`;
  }
};

const resolveLastPurchaseRateSnapshot = async ({
  companyId,
  materialId,
  vendorId = null,
  excludePoId = null,
  transaction = null
}) => {
  const poWhere = {
    company_id: companyId,
    status: { [Op.ne]: 'CANCELLED' }
  };
  if (Number.isInteger(vendorId) && vendorId > 0) poWhere.vendor_id = vendorId;
  if (Number.isInteger(excludePoId) && excludePoId > 0) poWhere.id = { [Op.ne]: excludePoId };

  const lastCosting = await db.PurchaseCosting.findOne({
    where: {
      material_id: materialId,
      ...(Number.isInteger(vendorId) && vendorId > 0 ? { vendor_id: vendorId } : {})
    },
    include: [
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'po_number', 'po_date', 'company_id', 'status'],
        required: true,
        where: poWhere
      }
    ],
    order: [['date', 'DESC'], ['id', 'DESC']],
    transaction: transaction || undefined
  });

  const material = await db.Material.findByPk(materialId, {
    attributes: ['id', 'last_purchase_rate', 'last_purchase_date'],
    transaction: transaction || undefined
  });

  const lastPoRate = await db.PurchaseOrderItem.findOne({
    where: { material_id: materialId },
    attributes: ['id', 'material_id', 'unit_price', 'created_at'],
    include: [
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'po_number', 'po_date', 'vendor_id', 'status', 'created_at'],
        required: true,
        where: poWhere
      }
    ],
    order: [
      [{ model: db.PurchaseOrder, as: 'purchaseOrder' }, 'po_date', 'DESC'],
      [{ model: db.PurchaseOrder, as: 'purchaseOrder' }, 'created_at', 'DESC'],
      ['id', 'DESC']
    ],
    transaction: transaction || undefined
  });

  const candidates = [];
  const toDate = (value) => {
    if (!value) return null;
    const d = new Date(value);
    return Number.isNaN(d.getTime()) ? null : d;
  };

  if (lastCosting && lastCosting.rate !== null && lastCosting.rate !== undefined) {
    candidates.push({
      source: 'PURCHASE_COSTING',
      rate: parseFloat(lastCosting.rate) || 0,
      date: toDate(lastCosting.date || lastCosting.purchaseOrder?.po_date || lastCosting.created_at),
      source_po_id: lastCosting.purchaseOrder?.id || null,
      source_po_number: lastCosting.purchaseOrder?.po_number || null,
      source_po_date: lastCosting.purchaseOrder?.po_date || null
    });
  }

  if (material?.last_purchase_rate !== null && material?.last_purchase_rate !== undefined) {
    candidates.push({
      source: 'MATERIAL_MASTER',
      rate: parseFloat(material.last_purchase_rate) || 0,
      date: toDate(material.last_purchase_date),
      source_po_id: null,
      source_po_number: null,
      source_po_date: material.last_purchase_date || null
    });
  }

  if (lastPoRate?.unit_price !== null && lastPoRate?.unit_price !== undefined) {
    candidates.push({
      source: 'PURCHASE_ORDER_ITEM',
      rate: parseFloat(lastPoRate.unit_price) || 0,
      date: toDate(lastPoRate.purchaseOrder?.po_date || lastPoRate.purchaseOrder?.created_at || lastPoRate.created_at),
      source_po_id: lastPoRate.purchaseOrder?.id || null,
      source_po_number: lastPoRate.purchaseOrder?.po_number || null,
      source_po_date: lastPoRate.purchaseOrder?.po_date || null
    });
  }

  if (!candidates.length) return null;
  candidates.sort((a, b) => {
    const ta = a.date ? a.date.getTime() : 0;
    const tb = b.date ? b.date.getTime() : 0;
    return tb - ta;
  });
  return candidates[0];
};

const upsertPurchaseCostingAndMaterialLastRate = async ({
  companyId,
  fiscalYearId = null,
  vendorId,
  poId,
  poDate,
  items,
  transaction
}) => {
  const costingRows = [];
  const materialLatestMap = new Map();

  for (const item of (items || [])) {
    const materialId = parseInt(item.material_id, 10);
    if (!Number.isInteger(materialId) || materialId <= 0) continue;
    const currentRate = parseFloat(item.unit_price) || 0;

    const snapshot = await resolveLastPurchaseRateSnapshot({
      companyId,
      materialId,
      vendorId,
      excludePoId: poId,
      transaction
    });
    const previousRate = snapshot?.rate ?? null;
    const difference = previousRate === null ? null : (currentRate - previousRate);
    const percentage = (previousRate && previousRate !== 0 && difference !== null)
      ? ((difference / previousRate) * 100)
      : null;
    const status = getCostingStatus(percentage);
    const reason = item.price_change_reason || item.rate_change_reason || null;
    const requiresApproval = percentage !== null && percentage > APPROVAL_THRESHOLD_PERCENT;
    if (requiresApproval && !reason) {
      throw new Error(`Reason for price change is required for material ${materialId} when increase exceeds ${APPROVAL_THRESHOLD_PERCENT}%`);
    }

    const purchaseCostingNumber = await generatePurchaseCostingNumber(companyId, fiscalYearId, transaction);

    costingRows.push({
      material_id: materialId,
      vendor_id: vendorId,
      po_id: poId,
      purchase_costing_number: purchaseCostingNumber,
      rate: currentRate,
      previous_rate: previousRate,
      difference,
      percentage,
      status,
      reason,
      approval_status: requiresApproval ? 'PENDING' : 'NOT_REQUIRED',
      date: poDate,
      created_date: poDate
    });

    materialLatestMap.set(materialId, {
      last_purchase_rate: currentRate,
      last_purchase_date: poDate
    });
  }

  if (costingRows.length > 0) {
    await db.PurchaseCosting.bulkCreate(costingRows, { transaction });
  }

  for (const [materialId, payload] of materialLatestMap.entries()) {
    await db.Material.update(payload, {
      where: { id: materialId },
      transaction
    });
  }
};

const generatePONumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PURCHASE_ORDER', fiscalYearId, transaction);
  } catch (error) {
    console.warn('Number format not found, using fallback method:', error.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `PO${year}`;
    const lastPO = await db.PurchaseOrder.findOne({
      where: { company_id: companyId, po_number: { [Op.like]: `${prefix}%` } },
      order: [['po_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (lastPO) {
      const lastSequence = parseInt(lastPO.po_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, sortBy, sortOrder, grnEligible } = 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 (status) {
    where.status = status;
  }

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

  // GRN eligibility filter:
  // show only POs having Gate Entry completed (status GRN_READY) and not yet linked to a GRN
  if (String(grnEligible || '').toLowerCase() === 'true') {
    const gateWhere = {
      status: 'GRN_READY',
      grn_id: null,
      po_id: { [Op.not]: null }
    };
    if (where.company_id) gateWhere.company_id = where.company_id;
    if (where.plant_id) gateWhere.plant_id = where.plant_id;

    const eligibleGateEntries = await db.GateEntry.findAll({
      where: gateWhere,
      attributes: ['po_id'],
      group: ['po_id']
    });

    const eligiblePoIds = eligibleGateEntries.map((g) => g.po_id).filter(Boolean);
    if (eligiblePoIds.length === 0) {
      // Return empty list quickly
      where.id = -1;
    } else {
      where.id = { [Op.in]: eligiblePoIds };
    }
  }

  const allowedSortFields = new Set(['id', 'po_number', 'po_date', 'total_amount', 'status', 'created_at']);
  const normalizedSortBy = allowedSortFields.has(sortBy) ? sortBy : 'po_date';
  const normalizedSortOrder = String(sortOrder || 'DESC').toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

  const records = await db.PurchaseOrder.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'] }
    ],
    limit: limitNum,
    offset,
    order: [[normalizedSortBy, normalizedSortOrder], ['id', 'DESC']]
  });

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

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

  const po = await db.PurchaseOrder.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.RequestForQuotation, as: 'requestForQuotation' },
      { model: db.PurchaseRequisition, as: 'purchaseRequisition' },
      { model: db.Currency, as: 'currency' },
      {
        model: db.PurchaseOrderItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  sendResponse(res, 200, true, 'Purchase Order retrieved successfully', po);
});

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

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

  const snapshot = await resolveLastPurchaseRateSnapshot({
    companyId,
    materialId,
    vendorId,
    excludePoId
  });
  const recentRates = await db.PurchaseCosting.findAll({
    where: {
      material_id: materialId,
      ...(Number.isInteger(vendorId) && vendorId > 0 ? { vendor_id: vendorId } : {})
    },
    include: [
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'po_number', 'po_date', 'company_id', 'status'],
        required: true,
        where: {
          company_id: companyId,
          status: { [Op.ne]: 'CANCELLED' },
          ...(Number.isInteger(excludePoId) && excludePoId > 0 ? { id: { [Op.ne]: excludePoId } } : {})
        }
      }
    ],
    attributes: ['id', 'rate', 'date', 'po_id'],
    order: [['date', 'DESC'], ['id', 'DESC']],
    limit: 3
  });
  const lastThreeRates = (recentRates || []).map((r) => ({
    rate: parseFloat(r.rate) || 0,
    date: r.date || null,
    po_id: r.purchaseOrder?.id || r.po_id || null,
    po_number: r.purchaseOrder?.po_number || null
  }));

  if (!snapshot) {
    return sendResponse(res, 200, true, 'Last purchase rate not found', {
      material_id: materialId,
      last_purchase_rate: null,
      source_po_id: null,
      source_po_number: null,
      source_po_date: null,
      source: null,
      last_three_rates: lastThreeRates
    });
  }

  sendResponse(res, 200, true, 'Last purchase rate retrieved successfully', {
    material_id: materialId,
    last_purchase_rate: snapshot.rate,
    source_po_id: snapshot.source_po_id || null,
    source_po_number: snapshot.source_po_number || null,
    source_po_date: snapshot.source_po_date || null,
    source: snapshot.source || null,
    last_three_rates: lastThreeRates
  });
});

exports.getMaterialVendorComparison = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId;
  const materialId = parseInt(req.params.materialId, 10);
  if (!companyId) return sendError(res, 'Company ID is required', 400);
  if (!Number.isInteger(materialId) || materialId <= 0) return sendError(res, 'Valid material ID is required', 400);

  const history = await db.PurchaseCosting.findAll({
    where: { material_id: materialId },
    include: [
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'company_id', 'po_number', 'po_date', 'status'],
        required: true,
        where: { company_id: companyId, status: { [Op.ne]: 'CANCELLED' } }
      },
      {
        model: db.BusinessPartner,
        as: 'vendor',
        attributes: ['id', 'bp_number', 'name', 'legal_name'],
        required: true
      }
    ],
    order: [['date', 'DESC'], ['id', 'DESC']]
  });

  const vendorMap = new Map();
  for (const row of history) {
    const vendorKey = String(row.vendor_id);
    if (vendorMap.has(vendorKey)) continue;
    vendorMap.set(vendorKey, {
      vendor_id: row.vendor_id,
      vendor_code: row.vendor?.bp_number || null,
      vendor_name: row.vendor?.name || row.vendor?.legal_name || '-',
      last_rate: parseFloat(row.rate) || 0,
      date: row.date || row.purchaseOrder?.po_date || null,
      po_number: row.purchaseOrder?.po_number || null
    });
  }
  sendResponse(res, 200, true, 'Vendor comparison retrieved successfully', Array.from(vendorMap.values()));
});

exports.getMaterialPriceTrend = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId;
  const materialId = parseInt(req.params.materialId, 10);
  const limit = Math.min(parseInt(req.query.limit || 30, 10) || 30, 365);
  if (!companyId) return sendError(res, 'Company ID is required', 400);
  if (!Number.isInteger(materialId) || materialId <= 0) return sendError(res, 'Valid material ID is required', 400);

  const trendRows = await db.PurchaseCosting.findAll({
    where: { material_id: materialId },
    include: [
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'company_id', 'po_number', 'po_date', 'status'],
        required: true,
        where: { company_id: companyId, status: { [Op.ne]: 'CANCELLED' } }
      }
    ],
    attributes: ['id', 'rate', 'date', 'vendor_id'],
    order: [['date', 'ASC'], ['id', 'ASC']],
    limit
  });

  const points = trendRows.map((row) => ({
    date: row.date || row.purchaseOrder?.po_date || null,
    rate: parseFloat(row.rate) || 0,
    po_number: row.purchaseOrder?.po_number || null,
    vendor_id: row.vendor_id || null
  }));
  sendResponse(res, 200, true, 'Material price trend retrieved successfully', points);
});

exports.getPurchaseCosting = asyncHandler(async (req, res) => {
  const {
    page = 1,
    limit = 25,
    companyId,
    plantId,
    vendorId,
    materialId,
    status,
    approvalStatus,
    changePercentMin,
    changePercentMax,
    dateFrom,
    dateTo,
    search
  } = req.query;

  const scopedCompanyId = companyId || (req.user && !req.user.isSuperAdmin ? req.user.companyId : null);
  if (!scopedCompanyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  const { limit: limitNum, offset } = getPagination(page, limit);
  const where = {};
  if (vendorId) where.vendor_id = vendorId;
  if (materialId) where.material_id = materialId;
  if (status) where.status = status;
  if (approvalStatus) where.approval_status = approvalStatus;
  if (changePercentMin || changePercentMax) {
    where.percentage = {};
    if (changePercentMin) where.percentage[Op.gte] = parseFloat(changePercentMin);
    if (changePercentMax) where.percentage[Op.lte] = parseFloat(changePercentMax);
  }
  if (dateFrom || dateTo) {
    where.date = {};
    if (dateFrom) where.date[Op.gte] = dateFrom;
    if (dateTo) where.date[Op.lte] = dateTo;
  }
  if (search) {
    where[Op.or] = [
      { '$material.material_number$': { [Op.like]: `%${search}%` } },
      { '$material.description$': { [Op.like]: `%${search}%` } },
      { '$vendor.bp_number$': { [Op.like]: `%${search}%` } },
      { '$vendor.name$': { [Op.like]: `%${search}%` } },
      { '$purchaseOrder.po_number$': { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.PurchaseCosting.findAndCountAll({
    where,
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description'],
        required: true
      },
      {
        model: db.BusinessPartner,
        as: 'vendor',
        attributes: ['id', 'bp_number', 'name', 'legal_name'],
        required: true
      },
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'po_number', 'po_date', 'company_id', 'plant_id'],
        required: true,
        where: {
          company_id: scopedCompanyId,
          ...(plantId ? { plant_id: plantId } : {})
        }
      }
    ],
    distinct: true,
    subQuery: false,
    limit: limitNum,
    offset,
    order: [['date', 'DESC'], ['id', 'DESC']]
  });

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

exports.getPurchaseCostingById = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const scopedCompanyId = req.user && !req.user.isSuperAdmin ? req.user.companyId : null;
  if (!Number.isInteger(id) || id <= 0) {
    return sendError(res, 'Valid purchase costing ID is required', 400);
  }

  const record = await db.PurchaseCosting.findByPk(id, {
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description'],
        required: false
      },
      {
        model: db.BusinessPartner,
        as: 'vendor',
        attributes: ['id', 'bp_number', 'name', 'legal_name'],
        required: false
      },
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'po_number', 'po_date', 'company_id', 'plant_id'],
        required: true
      }
    ]
  });

  if (!record) return sendError(res, 'Purchase costing record not found', 404);
  if (scopedCompanyId && Number(record.purchaseOrder?.company_id) !== Number(scopedCompanyId)) {
    return sendError(res, 'Purchase costing record not found', 404);
  }
  sendResponse(res, 200, true, 'Purchase costing retrieved successfully', record);
});

exports.updatePurchaseCostingApproval = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { action } = req.body || {};
  const normalizedAction = String(action || '').toUpperCase();
  if (!['APPROVE', 'REJECT'].includes(normalizedAction)) {
    return sendError(res, 'Action must be APPROVE or REJECT', 400);
  }
  const record = await db.PurchaseCosting.findByPk(id);
  if (!record) return sendError(res, 'Purchase costing record not found', 404);
  await record.update({
    approval_status: normalizedAction === 'APPROVE' ? 'APPROVED' : 'REJECTED'
  });
  sendResponse(res, 200, true, `Purchase costing ${normalizedAction === 'APPROVE' ? 'approved' : 'rejected'} successfully`, record);
});

// Generate PDF for Purchase Order - EMR Tap Changers Format
exports.generatePDF = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const po = await db.PurchaseOrder.findByPk(id, {
    include: [
      {
        model: db.Company,
        as: 'company',
        attributes: ['id', 'code', 'name', 'legal_name', 'email', 'phone'],
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }
        ]
      },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name', 'email', 'phone'] },
      { model: db.FiscalYear, as: 'fiscalYear', attributes: ['id', 'fiscal_year'] },
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name', 'vendor_name', 'legal_name', 'phone_numbers'] },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      { model: db.User, as: 'approvedByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      { model: db.PurchaseRequisition, as: 'purchaseRequisition', attributes: ['id', 'pr_number'] },
      { model: db.Currency, as: 'currency', attributes: ['id', 'currency_code', 'currency_symbol'] },
      {
        model: db.PurchaseOrderItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: [
              'id',
              'material_number',
              'description',
              'length',
              'width',
              'net_weight',
              'gross_weight',
              'sheet_type_id',
              'material_type_id',
            ],
            include: [
              {
                model: db.MaterialType,
                as: 'materialType',
                attributes: ['id', 'type_code', 'type_name'],
                required: false,
              },
              {
                model: db.MaterialSheetType,
                as: 'materialSheetType',
                attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
                required: false,
              },
            ],
          },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] },
        ],
      },
    ],
    order: [[{ model: db.PurchaseOrderItem, as: 'items' }, 'line_number', 'ASC']]
  });

  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  // Fetch vendor category
  let vendorCategory = null;
  if (po.vendor) {
    const bp = await db.BusinessPartner.findByPk(po.vendor.id, {
      include: [{ model: db.VendorCategory, as: 'vendorCategory', attributes: ['category_code', 'category_name'] }]
    });
    vendorCategory = bp?.vendorCategory;
  }

  // Fetch vendor address
  let vendorAddress = await db.BPAddress.findOne({
    where: { bp_id: po.vendor_id, status: 'ACTIVE', address_type: 'BILLING' },
    order: [['is_default', 'DESC'], ['id', 'ASC']]
  });

  // Fallback (older data may not have address_type/is_default marked)
  if (!vendorAddress) {
    vendorAddress = await db.BPAddress.findOne({
      where: { bp_id: po.vendor_id, status: 'ACTIVE' },
      order: [['id', 'ASC']]
    });
  }

  // Fetch vendor GSTIN (default if available)
  let vendorGSTIN = await db.BPGSTIN.findOne({
    where: { bp_id: po.vendor_id, status: 'ACTIVE' },
    order: [['is_default', 'DESC'], ['id', 'ASC']]
  });

  // Resolve City/State names + state code (prefer BP Address, fallback to GSTIN)
  let vendorCityName = '';
  let vendorStateName = '';
  let vendorStateCode = '';

  const effectiveCityId = vendorAddress?.city_id || vendorGSTIN?.city_id;
  const effectiveStateId = vendorAddress?.state_id || vendorGSTIN?.state_id;

  if (effectiveCityId) {
    const city = await db.City.findByPk(effectiveCityId);
    vendorCityName = city?.city_name || '';
  }
  if (effectiveStateId) {
    const state = await db.State.findByPk(effectiveStateId);
    vendorStateName = state?.state_name || '';
    vendorStateCode = state?.gst_state_code || state?.state_code || '';
  }

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

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

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

  const normalizeStateCode = (value) => {
    const raw = String(value || '').trim();
    if (!raw) return '';
    return raw.padStart(2, '0').slice(-2);
  };
  const companyStateCode = normalizeStateCode(po.company?.state?.gst_state_code || po.company?.state?.state_code);
  const vendorStateCodeNorm = normalizeStateCode(vendorStateCode);
  const isInterStateSupply = Boolean(companyStateCode && vendorStateCodeNorm && companyStateCode !== vendorStateCodeNorm);

  const gstSummary = po.items.reduce((acc, item) => {
    acc.taxable += parseFloat(item.quantity || 0) * parseFloat(item.unit_price || 0) - (parseFloat(item.discount_amount) || 0);
    acc.cgst += parseFloat(item.cgst_amount) || 0;
    acc.sgst += parseFloat(item.sgst_amount) || 0;
    acc.igst += parseFloat(item.igst_amount) || 0;
    return acc;
  }, { taxable: 0, cgst: 0, sgst: 0, igst: 0 });

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

  // Create PDF
  const doc = new PDFDocument({ margin: 30, size: 'A4', bufferPages: true });
  const poNumberPart = sanitizeForFilename(po.po_number || po.id);
  const vendorNamePart = sanitizeForFilename(po.vendor?.vendor_name || po.vendor?.name || po.vendor?.legal_name || 'Vendor');
  const filename = `PO_${poNumberPart}_${vendorNamePart}.pdf`;

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

  const pageWidth = 595.28;
  const pageHeight = 841.89;
  const margin = 30;
  const contentWidth = pageWidth - (margin * 2);
  const footerMetaHeight = 75;
  const footerMetaY = pageHeight - margin - footerMetaHeight;
  const footerSpecialNoteHeight = 95; // fixed footer Special Note block height
  const footerSpecialNoteY = footerMetaY - footerSpecialNoteHeight;
  // Fixed strip directly above the Special Note box: approver name + PO creator name (no signature lines).
  const approvalNamesBlockHeight = 55;

  /** Sheet SKU: L×W plus sheet-type SHEET / Material Type name “sheet” (matches nesting catalog sheet idea). */
  const isPdfSheetMaterial = (mat) => {
    if (!mat) return false;
    const len = parseFloat(mat.length) || 0;
    const wid = parseFloat(mat.width) || 0;
    if (!(len > 0 && wid > 0)) return false;
    const st = mat.materialSheetType;
    const code = String(st?.sheet_type_code || '').trim().toUpperCase();
    const name = String(st?.sheet_type_name || '').trim().toUpperCase();
    if (code === 'SHEET' || name === 'SHEET') return true;
    if (code.includes('SHEET') || name.includes('SHEET')) return true;
    const mt = mat.materialType;
    const sheetWord = /\bsheet\b/i;
    if (mt && (sheetWord.test(String(mt.type_code || '')) || sheetWord.test(String(mt.type_name || '')))) {
      return true;
    }
    return false;
  };

  /** PDF line text without leading material number (e.g. "MN | spec" → "spec"). */
  const poPdfDescriptionLine = (item) => {
    let raw = String(item.description || item.material?.description || '').trim();
    const mn = String(item.material?.material_number || '').trim();
    if (mn) {
      const prefix = `${mn} | `;
      const prefixTight = `${mn}|`;
      if (raw.startsWith(prefix)) raw = raw.slice(prefix.length).trim();
      else if (raw.startsWith(prefixTight)) raw = raw.slice(mn.length).replace(/^\|\s*/, '').trim();
    }
    return raw;
  };

  /** e.g. 30.00NO'S — qty with 2 decimals + UOM code (no space). */
  const formatPoPdfQtyWithUom = (item) => {
    const q = parseFloat(item.quantity || 0);
    const u = String(item.uom?.uom_code || item.uom?.uom_name || '').trim().replace(/\s+/g, '');
    return `${q.toFixed(2)}${u}`;
  };

  const pdfSheetWeightCell = (item) => {
    if (!isPdfSheetMaterial(item.material)) return '—';
    const kg = computeLineWeightKg({
      quantity: item.quantity,
      line_weight_kg: item.line_weight_kg,
      material: item.material,
    });
    if (kg == null || !Number.isFinite(kg) || kg <= 0) return '—';
    return `${parseFloat(kg).toFixed(3)}KG`;
  };

  const includeWeightColumn = (po.items || []).some((it) => isPdfSheetMaterial(it.material));

  const snX = margin + 2;
  const snW = 20;
  const descX = margin + 22;
  const descW = includeWeightColumn ? 180 : 228;
  const qtyX = descX + descW;
  const qtyW = 74;
  const wtW = 58;
  const rateW = 54;
  const wtX = qtyX + qtyW;
  const rateX = includeWeightColumn ? wtX + wtW : wtX;
  const delX = rateX + rateW;
  const delW = margin + contentWidth - delX;
  const qtySummaryX = qtyX;

  const formatPoUserName = (u) => {
    if (!u) return '';
    const full = `${u.first_name || ''} ${u.last_name || ''}`.trim();
    return full || u.username || '';
  };

  const drawPageOutline = () => {
    // Full outline for the entire printable area (removes the “gap” look)
    doc.rect(margin, margin, contentWidth, pageHeight - (margin * 2)).stroke('#000000');
  };

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

  const formatCurrency = (value) => {
    if (!value) return '0.00';
    return parseFloat(value).toFixed(2);
  };
  const formatINR = (value) => {
    const n = parseFloat(value) || 0;
    return `INR ${n.toLocaleString('en-IN', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;
  };

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

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

    drawBorder(margin, y, contentWidth, headerHeight);

    const logoX = margin + sectionPadding;
    const logoY = y + sectionPadding;
    const logoW = leftBlockW - (sectionPadding * 2);
    const logoH = headerHeight - (sectionPadding * 2);
    if (companyLogoPath) {
      try {
        // Use fit (not cover) so thin logo lines are not clipped in PDF.
        doc.image(companyLogoPath, logoX, logoY, {
          fit: [logoW, logoH],
          align: 'center',
          valign: 'center'
        });
      } catch (error) {
        console.error('Logo error:', error);
      }
    }

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

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

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

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

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

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

    return y + headerHeight;
  }

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

  function drawDetailsSection(y) {
    const leftWidth = contentWidth * 0.5;
    const rightWidth = contentWidth * 0.5;
    const rightX = margin + leftWidth;
    const leftLabelW = 58;
    const colonW = 6;
    const xLeft = margin + 5;
    const valueWLeft = leftWidth - 10 - leftLabelW - colonW;
    const valueXLeft = xLeft + leftLabelW + colonW;

    const details = [
      { label: 'PO No', value: po.po_number || '' },
      { label: 'Your Ref', value: '' },
      { label: 'Date', value: formatDate(po.po_date) },
      { label: 'PR No', value: po.purchaseRequisition?.pr_number || '' },
      { label: 'Handled By', value: po.createdByUser ? `${po.createdByUser.first_name || ''} ${po.createdByUser.last_name || ''}`.trim() : '' }
    ];

    const addressLines = [];
    if (vendorAddress) {
      if (vendorAddress.address_line1) addressLines.push(vendorAddress.address_line1);
      if (vendorAddress.address_line2) addressLines.push(vendorAddress.address_line2);
      const cityBits = [];
      if (vendorCityName) cityBits.push(vendorCityName);
      if (vendorAddress.pincode) cityBits.push(vendorAddress.pincode);
      if (cityBits.length > 0) addressLines.push(cityBits.join(' - '));
    }

    const vendorName = po.vendor?.vendor_name || po.vendor?.name || 'Vendor';
    const vendorNameUpper = vendorName.toUpperCase();

    /** Advance Y by wrapped text height (fixes Perungudi/Chennai overlap from fixed 10pt steps). */
    const measureLeftKV = (label, value) => {
      if (!value) return 0;
      doc.fontSize(7).font('Helvetica-Bold');
      const lh = doc.heightOfString(label, { width: leftLabelW });
      doc.font('Helvetica');
      const vh = doc.heightOfString(String(value), { width: valueWLeft });
      return Math.max(lh, vh, 9) + 3;
    };

    const measureLeftMultiAddress = (label, lines) => {
      const safeLines = (lines || []).map((l) => String(l || '').trim()).filter(Boolean);
      if (safeLines.length === 0) return 0;
      let h = 0;
      doc.fontSize(7).font('Helvetica-Bold');
      const labH = doc.heightOfString(label, { width: leftLabelW });
      doc.font('Helvetica');
      const h0 = doc.heightOfString(safeLines[0], { width: valueWLeft });
      h += Math.max(labH, h0) + 3;
      for (let i = 1; i < safeLines.length; i += 1) {
        h += doc.heightOfString(safeLines[i], { width: valueWLeft }) + 2;
      }
      return h;
    };

    const labelWRight = 55;
    const colonWRight = 8;
    const valueWRight = rightWidth - labelWRight - colonWRight - 15;

    const measureRightBlock = () => {
      let h = 0;
      details.forEach((item) => {
        doc.fontSize(7).font('Helvetica-Bold');
        const lh = doc.heightOfString(item.label, { width: labelWRight });
        doc.font('Helvetica');
        const vh = doc.heightOfString(String(item.value || ''), { width: valueWRight });
        h += Math.max(lh, vh, 9) + 3;
      });
      return h;
    };

    doc.fontSize(8).font('Helvetica-Bold');
    const nameH = doc.heightOfString(vendorNameUpper, { width: leftWidth - 10 });

    let leftContentH =
      10 +
      nameH +
      5 +
      (addressLines.length ? measureLeftMultiAddress('Address', addressLines) : 0) +
      measureLeftKV('Vendor code', po.vendor?.bp_number || '') +
      measureLeftKV('GSTIN', vendorGSTIN?.gstin || '') +
      measureLeftKV('Email', vendorAddress?.email || '') +
      measureLeftKV('Contact', vendorAddress?.phone || vendorAddress?.mobile || po.vendor?.phone_numbers || '') +
      measureLeftKV('State', vendorStateName || '') +
      measureLeftKV('State Code', vendorStateCode || '');

    const rightContentH = measureRightBlock();
    const sectionHeight = Math.max(100, Math.max(leftContentH, rightContentH) + 12);

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

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

    doc.fontSize(8).font('Helvetica-Bold');
    doc.text(vendorNameUpper, margin + 5, leftY, { width: leftWidth - 10 });
    leftY += nameH + 5;

    doc.fontSize(7).font('Helvetica');

    const drawLeftKV = (label, value) => {
      if (!value) return;
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(label, xLeft, leftY, { width: leftLabelW });
      doc.text(':', xLeft + leftLabelW, leftY, { width: colonW });
      doc.font('Helvetica');
      doc.text(String(value), valueXLeft, leftY, { width: valueWLeft });
      const lh = doc.heightOfString(label, { width: leftLabelW });
      const vh = doc.heightOfString(String(value), { width: valueWLeft });
      leftY += Math.max(lh, vh, 9) + 3;
    };

    const drawLeftMultiLineKV = (label, lines) => {
      const safeLines = (lines || []).map((l) => String(l || '').trim()).filter(Boolean);
      if (safeLines.length === 0) return;
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(label, xLeft, leftY, { width: leftLabelW });
      doc.text(':', xLeft + leftLabelW, leftY, { width: colonW });
      doc.font('Helvetica');
      doc.text(safeLines[0], valueXLeft, leftY, { width: valueWLeft });
      const h0 = Math.max(
        doc.heightOfString(label, { width: leftLabelW }),
        doc.heightOfString(safeLines[0], { width: valueWLeft })
      );
      leftY += h0 + 3;
      for (let i = 1; i < safeLines.length; i += 1) {
        doc.text(safeLines[i], valueXLeft, leftY, { width: valueWLeft });
        leftY += doc.heightOfString(safeLines[i], { width: valueWLeft }) + 2;
      }
    };

    if (addressLines.length > 0) {
      drawLeftMultiLineKV('Address', addressLines);
    }

    drawLeftKV('Vendor code', po.vendor?.bp_number || '');
    drawLeftKV('GSTIN', vendorGSTIN?.gstin || '');
    drawLeftKV('Email', vendorAddress?.email || '');
    drawLeftKV('Contact', vendorAddress?.phone || vendorAddress?.mobile || po.vendor?.phone_numbers || '');
    drawLeftKV('State', vendorStateName || '');
    drawLeftKV('State Code', vendorStateCode || '');

    let rightY = y + 5;
    details.forEach((item) => {
      doc.fontSize(7).font('Helvetica-Bold');
      doc.text(item.label, rightX + 5, rightY, { width: labelWRight });
      doc.text(':', rightX + labelWRight + 5, rightY, { width: colonWRight });
      doc.font('Helvetica');
      doc.text(String(item.value || ''), rightX + labelWRight + colonWRight + 5, rightY, { width: valueWRight });
      const lh = doc.heightOfString(item.label, { width: labelWRight });
      const vh = doc.heightOfString(String(item.value || ''), { width: valueWRight });
      rightY += Math.max(lh, vh, 9) + 3;
    });

    return y + sectionHeight;
  }

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

    // Remove empty side boxes: keep only two useful columns.
    const colWidth = contentWidth / 2;
    doc.moveTo(margin + colWidth, y).lineTo(margin + colWidth, y + rowHeight).stroke();

    doc.fontSize(7).font('Helvetica');
    // Vendor code is shown in the vendor block (top-left) as requested.
    doc.font('Helvetica').text('Pr Grp:', margin + 3, y + 5, { continued: true }).font('Helvetica-Bold').text(` ${vendorCategory?.category_code || ''}`);
    doc.font('Helvetica').text('Dur CIN:', margin + colWidth + 3, y + 5, { continued: true }).font('Helvetica-Bold').text(` ${po.currency?.currency_code || 'INR'}`);
    // Amount breakup moved to bottom-right summary block near table end.

    return y + rowHeight;
  }

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

    const lineXs = [descX, qtyX, ...(includeWeightColumn ? [wtX] : []), rateX, delX];
    lineXs.forEach((lx) => {
      doc.moveTo(lx, y).lineTo(lx, y + headerHeight).stroke();
    });

    const cols = [
      { label: 'S.No', x: snX, width: snW, align: 'center' },
      { label: 'Description', x: descX, width: descW, align: 'left' },
      { label: 'Quantity', x: qtyX, width: qtyW, align: 'right' },
      ...(includeWeightColumn ? [{ label: 'Weight\n(KG)', x: wtX, width: wtW, align: 'right' }] : []),
      { label: 'Rate\nINR', x: rateX, width: rateW, align: 'right' },
      { label: 'Delivery', x: delX, width: delW, align: 'center' },
    ];

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

    return y + headerHeight;
  }

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

    const lineXs = [descX, qtyX, ...(includeWeightColumn ? [wtX] : []), rateX, delX];
    lineXs.forEach((lx) => {
      doc.moveTo(lx, y).lineTo(lx, y + rowHeight).stroke();
    });

    const descVal = poPdfDescriptionLine(item);
    const qtyVal = formatPoPdfQtyWithUom(item);
    const wtVal = includeWeightColumn ? pdfSheetWeightCell(item) : '';

    const cols = [
      { x: snX, width: snW, value: String(index + 1), align: 'center' },
      { x: descX, width: descW, value: descVal, align: 'left' },
      { x: qtyX, width: qtyW, value: qtyVal, align: 'right' },
      ...(includeWeightColumn ? [{ x: wtX, width: wtW, value: wtVal, align: 'right' }] : []),
      { x: rateX, width: rateW, value: formatCurrency(item.unit_price || 0), align: 'right' },
      { x: delX, width: delW, value: formatDate(item.delivery_date), align: 'center' },
    ];

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

    return y + rowHeight;
  }

  function drawApprovalAndAuthorizedBy(y) {
    const boxH = approvalNamesBlockHeight;
    const boxW = contentWidth / 2;
    const leftX = margin;
    const rightX = margin + boxW;

    doc.rect(leftX, y, boxW, boxH).stroke('#000000');
    doc.rect(rightX, y, boxW, boxH).stroke('#000000');

    doc.font('Helvetica-Bold').fontSize(9).fillColor('#000000');
    doc.text('Approved By', leftX, y + 4, { width: boxW, align: 'center' });
    doc.text('Authorized By', rightX, y + 4, { width: boxW, align: 'center' });
    // Keep these blocks empty for manual sign (no line/text).
  }

  function drawBottomAmountSummary(tableEndY) {
    const taxableAmount = gstSummary.taxable || 0;
    const cgstAmount = gstSummary.cgst || 0;
    const sgstAmount = gstSummary.sgst || 0;
    const igstAmount = gstSummary.igst || 0;
    const totalAmount = parseFloat(po.total_amount) || 0;
    const totalTax = cgstAmount + sgstAmount + igstAmount;
    // Start summary at Quantity column so top line joins table grid.
    const x = qtySummaryX;
    const rightBlockW = (margin + contentWidth) - x;
    const rowH = 16;
    const rowsCount = isInterStateSupply ? 4 : 5;
    const blockH = rowsCount * rowH;
    const maxY = (footerSpecialNoteY - approvalNamesBlockHeight) - blockH - 8;
    // Attach to table border (merged with top line), while staying above footer area.
    const y = Math.min(maxY, tableEndY);

    // Lined summary table for amount breakup.
    drawBorder(x, y, rightBlockW, blockH);
    // Align summary center divider with table's Delivery column start.
    const splitX = delX;
    doc.moveTo(splitX, y).lineTo(splitX, y + blockH).stroke('#000000');
    for (let i = 1; i < rowsCount; i += 1) {
      const hy = y + (i * rowH);
      doc.moveTo(x, hy).lineTo(x + rightBlockW, hy).stroke('#000000');
    }

    const labelX = x + 6;
    let lineY = y + 4;

    doc.font('Helvetica').fontSize(7.5).fillColor('#000000');
    doc.text('Sub Total', labelX, lineY, { width: 110 });
    doc.text(formatINR(taxableAmount), labelX, lineY, { width: rightBlockW - 12, align: 'right' });
    lineY += rowH;

    if (isInterStateSupply) {
      doc.text('IGST (%)', labelX, lineY, { width: 110 });
      doc.text(formatINR(igstAmount), labelX, lineY, { width: rightBlockW - 12, align: 'right' });
      lineY += rowH;
    } else {
      doc.text('CGST (%)', labelX, lineY, { width: 110 });
      doc.text(formatINR(cgstAmount), labelX, lineY, { width: rightBlockW - 12, align: 'right' });
      lineY += rowH;
      doc.text('SGST (%)', labelX, lineY, { width: 110 });
      doc.text(formatINR(sgstAmount), labelX, lineY, { width: rightBlockW - 12, align: 'right' });
      lineY += rowH;
    }

    doc.text('Total Tax', labelX, lineY, { width: 110 });
    doc.text(formatINR(totalTax), labelX, lineY, { width: rightBlockW - 12, align: 'right' });
    lineY += rowH;

    doc.font('Helvetica-Bold');
    doc.text('Total Amount', labelX, lineY, { width: 110 });
    doc.text(formatINR(totalAmount), labelX, lineY, { width: rightBlockW - 12, align: 'right' });
  }

  function drawFooter(pageNum, totalPages) {

    // Draw footer border first
    doc.save();
    // Special Note footer block (above SAC/HSN)
    doc.rect(margin, footerSpecialNoteY, contentWidth, footerSpecialNoteHeight).stroke('#000000');
    // Footer meta (SAC/HSN/etc.)
    doc.rect(margin, footerMetaY, contentWidth, footerMetaHeight).stroke('#000000');
    doc.restore();

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

    // Save graphics state
    doc.save();

    // --- Special Note in footer (fixed position) ---
    doc.fontSize(8).font('Helvetica-Bold').fillColor('#000000');
    doc.text('Special Note', leftX, footerSpecialNoteY + 5);

    const footerNotes = [
      { label: 'Payment Terms', value: po.payment_terms || '' },
      { label: 'Price basis', value: 'Ex works' },
      { label: 'Conditions Of Delivery', value: po.delivery_terms || '' }
    ];

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

    // Right side GST breakup based on state code (inter-state => IGST, intra-state => CGST+SGST)
    const totalTaxAmount = gstSummary.cgst + gstSummary.sgst + gstSummary.igst;
    const effectiveTaxRate = gstSummary.taxable > 0 ? (totalTaxAmount / gstSummary.taxable) * 100 : 0;
    const perSideRate = effectiveTaxRate / 2;
    const currencyCode = po.currency?.currency_code || 'INR';
    let gstInfoY = footerSpecialNoteY + 17;

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

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

    // Divider line between Special Note and footer meta
    doc.moveTo(margin, footerMetaY).lineTo(margin + contentWidth, footerMetaY).stroke();

    const metaTop = footerMetaY;

    // Line 1: SAC No. and ARN Code (GSTIN moved to header as requested)
    doc.fontSize(7).font('Helvetica').fillColor('#000000');
    doc.text('SAC No. :', leftX, metaTop + 6, {
      width: leftColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });
    doc.text('ARN Code :', midX, metaTop + 6, {
      width: rightColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });

    // Line 2: HSN Code
    doc.text('HSN Code :', leftX, metaTop + 16, {
      width: leftColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });
    doc.text('', midX, metaTop + 16, {
      width: rightColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });

    // Line 3: Registered Office
    doc.font('Helvetica-Oblique');
    doc.text('Registered Office:', leftX, metaTop + 28, {
      width: leftColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });

    // Registered Office address
    doc.font('Helvetica').fontSize(7);
    const regOffice = printHeader?.address || '5122 , BHARATHI STREET CHINNAVEDAMPATTI COIMBATORE - 641049';
    doc.text(regOffice, leftX, metaTop + 37, {
      width: leftColWidth,
      height: 15,
      lineBreak: false,
      continued: false
    });

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

    // Line 4: Form Code and Page No
    doc.font('Helvetica');
    const refCode = printHeader?.purchase_order_form_code || 'MTPL03';
    doc.text(refCode, leftX, metaTop + 53, {
      width: leftColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });
    doc.text(`Page No      ${pageNum}     :`, midX, metaTop + 53, {
      width: rightColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });

    // Line 5: Terms
    doc.fontSize(7);
    doc.text('This Purchase Order is subject to terms and conditions set out on this page and overleaf.', leftX, metaTop + 63, {
      width: leftColWidth,
      height: 10,
      lineBreak: false,
      continued: false
    });

    // ORIGINAL / FILE COPY
    doc.fontSize(10).font('Helvetica-Bold');
    const copyText = 'ORIGINAL / FILE COPY';
    const copyWidth = doc.widthOfString(copyText);
    const copyX = midX + ((contentWidth / 2) - copyWidth) / 2;
    doc.text(copyText, copyX, metaTop + 58, {
      width: rightColWidth,
      height: 15,
      lineBreak: false,
      continued: false
    });

    // Restore graphics state
    doc.restore();
  }

  function drawTermsAndConditionsPage() {
    doc.addPage();

    let y = margin + 5;

    // Title
    doc.fontSize(13).font('Helvetica-Bold').fillColor('#000000');
    doc.text('TERMS AND CONDITIONS', margin, y, { width: contentWidth, align: 'center' });
    y += 22;

    // Two column layout
    const colWidth = (contentWidth - 15) / 2;
    const leftColX = margin;
    const rightColX = margin + colWidth + 15;

    // Left Column Terms
    const leftTerms = [
      {
        title: 'Please quote our reference',
        content: 'Delivery Notes, Invoices and further correspondence should bear this Order number and date as well as any amendments or alterations we may subsequently advise.\n\nDelivery Notes and Invoices should be sent in duplicate Advance Delivery schedules of Invoices should be sent to us immediately after despatch in the case of payment through our Bankers.'
      },
      {
        title: 'Declarations to accompany consignment',
        content: 'Each consignment should be accompanied should be accompanied by the following particulars:\n\n(a) Our Central and State Sales Tax Registration Numbers.\n(b) Supplier\'s Central and Sales Tax Registration Numbers.\n(c) Supplier\'s Sales Tax Group, declarations and gate pass.\n(d) Failure to comply with Sales Tax, Octroi and Excise regulations may result in rejection of goods.'
      },
      {
        title: 'Prices',
        content: 'Prices mentioned in the Order are firm unless otherwise agreed mutually at the time of Order. Unless any request for price increase will be entertained thereafter.'
      },
      {
        title: 'Delivery',
        content: 'Supplier shall strictly adhere to the delivery schedule committed by them in this PO; Suppliers should have their Order or part of the Order if the delivery is not made within the time specified. We also reserve the right to purchase elsewhere the goods ordered and not supplied on time and recover the extra costs incurred from Supplier\'s account.\n\nWe also reserve the right to cancel this Order or or and part not completed or stopped due to any other conditions due to force majeure conditions prevailing with Supplier\'s.'
      },
      {
        title: 'Quality assurance',
        content: 'Goods supplied should strictly conform to our drawings, technical specifications and specified standards. Suppliers should despatch only completely inspected and tested goods to us.\n\nPrior to despatch, suppliers must carry out an inspection of the parts as per the AQL 1 / IS 2500 sampling plan. The sample size and inspection results should be clearly mentioned in the Inspection report. A copy of this report must be sent to us along with the consignment.\n\nNecessary inspection and test reports should accompany consignments. Failure to provide such reports may result in withholding payment against Suppliers invoice.'
      }
    ];

    // Right Column Terms
    const rightTerms = [
      {
        title: 'Acceptance and Rejections',
        content: 'Goods received at our works against this Order will be inspected and tested to our drawings, technical specification and Standards. Goods not conforming to our specifications or goods will be dispatched to suppliers at their cost within a reasonable period. Such rejections will also include goods found in works. Supplier should make good the replacement within 15 days from the date conversion should be sent to us along with Suppliers payments in settlement, immediately after rejections. In case of any penalty, a EMD will be levied if supplier, corresponding deduction on supplier payment will be done accordingly.'
      },
      {
        title: 'Materials and tooling entrusted to Suppliers',
        content: 'Materials and tooling like mandrals, jigs fixtures, dies, patterns, press tools, moulds, gauges, etc., supplied by us for execution of this Order, should be maintained in safe custody of the suppliers as our property. Materials supplied by us should be returned in fabricated or converted forms as per our Order. Any resultant scrap and balance materials shall also be returned intact along with supplies. Tooling entrusted to Supplier shall be returned along with supplies unless otherwise specified by us. Failure to return scrap, excess balance materials and tooling will result in stoppage of payment.'
      },
      {
        title: 'Guarantee',
        content: 'Suppliers will guarantee the materials and equipment supplied for a period of twelve months from the date of delivery in replaced, by the suppliers without extra cost to us of if a complete replacement could not be made, failure.'
      },
      {
        title: 'Inspection Facilities at Suppliers Works',
        content: 'Suppliers should allow our representatives into their works for inspecting goods ordered on them for the purpose of stage inspection and progress follow up.'
      },
      {
        title: 'NON Disclosure',
        content: 'The documents related this P.O which are provided by us are the exclusive property of Easun - MR. and shall not be used, copied or transmitted to third parties without our permission.'
      },
      {
        title: 'Order Acceptance',
        content: 'Suppliers should let us have their order acceptance, confirming delivery period specified in this Order. In the absence of any Order. In the absence of any order acceptance this Order will be completely binding on the Suppliers.'
      }
    ];

    // Draw left column
    let leftY = y;
    leftTerms.forEach(term => {
      if (leftY + 60 > pageHeight - margin - 10) {
        return; // Skip if no space
      }

      doc.fontSize(8).font('Helvetica-Bold').fillColor('#000000');
      doc.text(term.title, leftColX, leftY, { width: colWidth, align: 'left' });
      leftY += 12;

      doc.font('Helvetica').fontSize(7);
      const contentHeight = doc.heightOfString(term.content, { width: colWidth, align: 'justify' });
      doc.text(term.content, leftColX, leftY, { width: colWidth, align: 'justify', lineGap: 1 });
      leftY += contentHeight + 14;
    });

    // Draw right column
    let rightY = y;
    rightTerms.forEach(term => {
      if (rightY + 60 > pageHeight - margin - 10) {
        return; // Skip if no space
      }

      doc.fontSize(8).font('Helvetica-Bold').fillColor('#000000');
      doc.text(term.title, rightColX, rightY, { width: colWidth, align: 'left' });
      rightY += 12;

      doc.font('Helvetica').fontSize(7);
      const contentHeight = doc.heightOfString(term.content, { width: colWidth, align: 'justify' });
      doc.text(term.content, rightColX, rightY, { width: colWidth, align: 'justify', lineGap: 1 });
      rightY += contentHeight + 14;
    });
  }

  // Generate PDF
  let y = margin;
  let currentPage = 1;

  drawPageOutline();
  y = drawHeader(y);
  y = drawTitle(y);
  y = drawDetailsSection(y);
  y = drawMetadataRow(y);
  y = drawTableHeader(y);

  po.items.forEach((item, index) => {
    // Keep table content above the fixed footer area (special note + meta).
    // Do not draw the full footer (Special Note / GST summary) here — that must appear only once after all lines.
    if (y + 18 > footerSpecialNoteY - approvalNamesBlockHeight - 10) {
      doc.addPage();
      currentPage++;
      y = margin;
      drawPageOutline();
      y = drawHeader(y);
      y = drawTitle(y);
      y = drawTableHeader(y);
    }
    y = drawTableRow(y, item, index);
  });

  // Bottom-right amount breakup near the end of item table.
  drawBottomAmountSummary(y);

  // Approver + PO creator names, fixed directly above the Special Note rectangle (top of special-note band).
  drawApprovalAndAuthorizedBy(footerSpecialNoteY - approvalNamesBlockHeight);

  // One T&C page is appended after PO pages — total page count includes it for "Page No" in footer.
  const pdfTotalPages = currentPage + 1;

  // Final page footer only: Special Note + SAC/HSN after all materials (not on continuation breaks).
  drawFooter(currentPage, pdfTotalPages);

  // Add Terms & Conditions Page
  drawTermsAndConditionsPage();

  doc.end();
});

exports.create = asyncHandler(async (req, res) => {
  // Some clients send `{ data: {...} }` while others send the payload directly
  const data = (req.body && typeof req.body === 'object' && req.body.data && typeof req.body.data === 'object')
    ? req.body.data
    : req.body;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

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

  // Normalize keys to support both snake_case and camelCase (live UI payloads can differ)
  data.plant_id =
    data.plant_id ??
    data.plantId ??
    data.plantID ??
    req.query?.plant_id ??
    req.query?.plantId ??
    null;
  data.fiscal_year_id =
    data.fiscal_year_id ??
    data.fiscalYearId ??
    data.fiscal_yearId ??
    req.query?.fiscal_year_id ??
    req.query?.fiscalYearId ??
    null;
  data.vendor_id =
    data.vendor_id ??
    data.vendorId ??
    data.vendorID ??
    req.query?.vendor_id ??
    req.query?.vendorId ??
    null;

  // Also accept nested objects (common Autocomplete payload shapes)
  if (!data.vendor_id && data.vendor && typeof data.vendor === 'object') {
    data.vendor_id = data.vendor.id ?? data.vendor.value ?? data.vendor.vendor_id ?? null;
  }
  if (!data.plant_id && data.plant && typeof data.plant === 'object') {
    data.plant_id = data.plant.id ?? data.plant.value ?? null;
  }
  if (!data.fiscal_year_id && data.fiscalYear && typeof data.fiscalYear === 'object') {
    data.fiscal_year_id = data.fiscalYear.id ?? data.fiscalYear.value ?? null;
  }

  // Resolve defaults from company context if missing (prevents false validation failures in live)
  if (!data.plant_id) {
    const defaultPlant = await db.Plant.findOne({
      where: { company_id: companyId, status: 'ACTIVE' },
      order: [['id', 'ASC']]
    });
    data.plant_id = defaultPlant?.id || null;
  }

  if (!data.fiscal_year_id) {
    const refDate = data.po_date ? new Date(data.po_date) : new Date();
    let fy = await db.FiscalYear.findOne({
      where: {
        company_id: companyId,
        status: 'ACTIVE',
        is_closed: false,
        start_date: { [Op.lte]: refDate },
        end_date: { [Op.gte]: refDate }
      },
      order: [['start_date', 'DESC']]
    });
    if (!fy) {
      fy = await db.FiscalYear.findOne({
        where: { company_id: companyId, status: 'ACTIVE', is_closed: false },
        order: [['start_date', 'DESC']]
      });
    }
    data.fiscal_year_id = fy?.id || null;
  }

  if (!data.plant_id || !data.fiscal_year_id || !data.vendor_id) {
    // Keep strict validation, but avoid false negatives due to naming mismatch
    return sendError(res, 'Plant ID, Fiscal Year ID, and Vendor ID are required', 400);
  }

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

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

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

    const { items, ...poData } = data;

    if (poData.rfq_id && poData.pr_id) {
      await transaction.rollback();
      return sendError(res, 'Select either RFQ or Purchase Requisition (not both)', 400);
    }

    if (!items || !Array.isArray(items) || items.length === 0) {
      await transaction.rollback();
      return sendError(res, 'At least one item is required', 400);
    }

    // Convert empty strings to null for optional foreign keys
    if (poData.currency_id === '' || poData.currency_id === null || poData.currency_id === undefined) {
      poData.currency_id = null;
    }
    if (poData.rfq_id === '' || poData.rfq_id === null || poData.rfq_id === undefined) {
      poData.rfq_id = null;
    }
    if (poData.pr_id === '' || poData.pr_id === null || poData.pr_id === undefined) {
      poData.pr_id = null;
    }
    if (poData.delivery_date === '' || poData.delivery_date === null || poData.delivery_date === undefined) {
      poData.delivery_date = null;
    }

    // Validate vendor exists and is active (Professional ERP Pattern)
    const vendor = await db.BusinessPartner.findByPk(poData.vendor_id, {
      include: [
        {
          model: db.BPAddress,
          as: 'addresses',
          where: { address_type: 'BILLING', is_default: true },
          required: false,
          include: [
            { model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }
          ]
        },
        {
          model: db.BPGSTIN,
          as: 'gstins',
          where: { is_default: true, status: 'ACTIVE' },
          required: false
        }
      ]
    });

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

    // Validate vendor is active and is of type VENDOR
    if (vendor.status !== 'ACTIVE') {
      await transaction.rollback();
      return sendError(res, 'Vendor is not active', 400);
    }

    if (vendor.bp_type !== 'VENDOR' && vendor.bp_type !== 'BOTH') {
      await transaction.rollback();
      return sendError(res, 'Selected business partner is not a vendor', 400);
    }

    const company = await db.Company.findByPk(companyId, {
      include: [
        { model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }
      ]
    });

    const plant = await db.Plant.findByPk(poData.plant_id, {
      include: [
        { model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }
      ]
    });

    // Determine GST type based on state codes
    const companyStateCode = company?.state?.gst_state_code || company?.state?.state_code;
    const vendorStateCode = vendor?.addresses?.[0]?.state?.gst_state_code || vendor?.addresses?.[0]?.state?.state_code;
    const isInterState = companyStateCode && vendorStateCode && companyStateCode !== vendorStateCode;

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

    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;
    let cgstAmount = 0;
    let sgstAmount = 0;
    let igstAmount = 0;

    items.forEach(item => {
      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const discountPercent = parseFloat(item.discount_percent) || 0;
      const taxRate = parseFloat(item.tax_rate) || 0;

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

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

    poData.sub_total = subTotal;
    poData.discount_amount = discountAmount;
    poData.tax_amount = taxAmount;
    poData.cgst_amount = cgstAmount;
    poData.sgst_amount = sgstAmount;
    poData.igst_amount = igstAmount;
    poData.total_amount = subTotal - discountAmount + taxAmount;
    poData.status = 'DRAFT'; // Start with DRAFT status

    const po = await db.PurchaseOrder.create(poData, { transaction });

    const poItems = items.map((item, index) => {
      if (!item.material_id || !item.uom_id) {
        throw new Error(`Invalid item at line ${index + 1}: material and UOM are required`);
      }

      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const discountPercent = parseFloat(item.discount_percent) || 0;
      const taxRate = parseFloat(item.tax_rate) || 0;

      if (quantity <= 0) {
        throw new Error(`Invalid item at line ${index + 1}: quantity must be greater than 0`);
      }
      if (unitPrice < 0) {
        throw new Error(`Invalid item at line ${index + 1}: unit price must be 0 or greater`);
      }

      // Rates: trust state logic, not client selection of tax type
      let cgstRate = parseFloat(item.cgst_rate) || 0;
      let sgstRate = parseFloat(item.sgst_rate) || 0;
      let igstRate = parseFloat(item.igst_rate) || 0;

      if (taxRate > 0 && cgstRate === 0 && sgstRate === 0 && igstRate === 0) {
        // fallback for legacy clients
        if (isInterState) igstRate = taxRate;
        else {
          cgstRate = taxRate / 2;
          sgstRate = taxRate / 2;
        }
      }

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

      // Amounts: always compute server-side (integrity)
      let cgstAmount = 0, sgstAmount = 0, igstAmount = 0;
      if (isInterState) {
        igstAmount = (taxableAmount * (igstRate || taxRate || 0)) / 100;
      } else {
        cgstAmount = (taxableAmount * (cgstRate || (taxRate / 2) || 0)) / 100;
        sgstAmount = (taxableAmount * (sgstRate || (taxRate / 2) || 0)) / 100;
      }

      const lineTax = cgstAmount + sgstAmount + igstAmount;
      const effectiveTaxRate = isInterState ? (igstRate || taxRate || 0) : (cgstRate + sgstRate || taxRate || 0);

      return {
        po_id: po.id,
        line_number: index + 1,
        material_id: item.material_id,
        description: resolvePurchaseLineDescription(
          item,
          lineMaterialMap.get(parseInt(item.material_id, 10))
        ),
        quantity,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        discount_percent: discountPercent,
        discount_amount: lineDiscount,
        tax_rate: effectiveTaxRate,
        tax_amount: lineTax,
        cgst_rate: isInterState ? 0 : (cgstRate || (taxRate / 2) || 0),
        cgst_amount: cgstAmount,
        sgst_rate: isInterState ? 0 : (sgstRate || (taxRate / 2) || 0),
        sgst_amount: sgstAmount,
        igst_rate: isInterState ? (igstRate || taxRate || 0) : 0,
        igst_amount: igstAmount,
        line_total: taxableAmount + lineTax,
        hsn_code: item.hsn_code,
        delivery_date: item.delivery_date || null,
        storage_location_id: item.storage_location_id || null,
        remarks: item.remarks,
        line_weight_kg: computeLineWeightKg({
          quantity,
          line_weight_kg: item.line_weight_kg,
          material: lineMaterialMap.get(parseInt(item.material_id, 10)),
        }),
      };
    });

    await db.PurchaseOrderItem.bulkCreate(poItems, { transaction });

    await upsertPurchaseCostingAndMaterialLastRate({
      companyId,
      fiscalYearId: poData.fiscal_year_id || null,
      vendorId: poData.vendor_id,
      poId: po.id,
      poDate: po.po_date || poData.po_date || new Date().toISOString().slice(0, 10),
      items,
      transaction
    });

    // If PO is created from an RFQ, close the RFQ so it won't be selectable again
    if (poData.rfq_id) {
      const rfq = await db.RequestForQuotation.findByPk(poData.rfq_id, { transaction });
      if (rfq && rfq.status !== 'CLOSED') {
        await rfq.update(
          {
            status: 'CLOSED',
            closed_at: new Date(),
            closed_by: userId
          },
          { transaction }
        );
      }
    }

    await transaction.commit();

    const createdPO = await db.PurchaseOrder.findByPk(po.id, {
      include: [
        { model: db.PurchaseOrderItem, as: 'items', include: [{ model: db.Material, as: 'material' }, { model: db.UOM, as: 'uom' }] }
      ]
    });

    const autoNotificationResult = await purchaseNotificationService.triggerAutoNotificationsForPO({
      poId: po.id,
      userId
    });
    const payload = createdPO?.toJSON ? createdPO.toJSON() : createdPO;
    payload.notification_result = autoNotificationResult;
    sendResponse(res, 201, true, 'Purchase Order created successfully', payload);
  } 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 po = await db.PurchaseOrder.findByPk(id);
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  if (po.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Purchase Orders can be updated', 400);
  }

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

  try {
    const { items, ...poData } = data;
    poData.updated_by = userId;

    if (poData.rfq_id && poData.pr_id) {
      await transaction.rollback();
      return sendError(res, 'Select either RFQ or Purchase Requisition (not both)', 400);
    }

    if (!items || !Array.isArray(items) || items.length === 0) {
      await transaction.rollback();
      return sendError(res, 'At least one item is required', 400);
    }

    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    // Recompute totals server-side for integrity
    items.forEach((item, index) => {
      if (!item.material_id || !item.uom_id) {
        throw new Error(`Invalid item at line ${index + 1}: material and UOM are required`);
      }

      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const discountPercent = parseFloat(item.discount_percent) || 0;
      const taxRate = parseFloat(item.tax_rate) || 0;

      if (quantity <= 0) throw new Error(`Invalid item at line ${index + 1}: quantity must be greater than 0`);
      if (unitPrice < 0) throw new Error(`Invalid item at line ${index + 1}: unit price must be 0 or greater`);

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

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

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

    await po.update(poData, { transaction });

    // If RFQ is assigned/changed on a PO update, close that RFQ to prevent reuse
    const nextRfqId = poData.rfq_id ?? po.rfq_id;
    if (nextRfqId && String(nextRfqId) !== String(po.rfq_id || '')) {
      const rfq = await db.RequestForQuotation.findByPk(nextRfqId, { transaction });
      if (rfq && rfq.status !== 'CLOSED') {
        await rfq.update(
          {
            status: 'CLOSED',
            closed_at: new Date(),
            closed_by: userId
          },
          { transaction }
        );
      }
    }

    await db.PurchaseOrderItem.destroy({ where: { po_id: po.id }, transaction });

    // Determine GST type using current PO vendor (existing PO vendor is authoritative for update)
    const vendor = await db.BusinessPartner.findByPk(poData.vendor_id || po.vendor_id, {
      include: [
        {
          model: db.BPAddress,
          as: 'addresses',
          where: { address_type: 'BILLING', is_default: true },
          required: false,
          include: [
            { model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }
          ]
        }
      ]
    });

    const company = await db.Company.findByPk(po.company_id, {
      include: [{ model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }]
    });

    const companyStateCode = company?.state?.gst_state_code || company?.state?.state_code;
    const vendorStateCode = vendor?.addresses?.[0]?.state?.gst_state_code || vendor?.addresses?.[0]?.state?.state_code;
    const isInterState = companyStateCode && vendorStateCode && companyStateCode !== vendorStateCode;

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

    const poItems = items.map((item, index) => {
      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const discountPercent = parseFloat(item.discount_percent) || 0;
      const taxRate = parseFloat(item.tax_rate) || 0;

      let cgstRate = parseFloat(item.cgst_rate) || 0;
      let sgstRate = parseFloat(item.sgst_rate) || 0;
      let igstRate = parseFloat(item.igst_rate) || 0;

      if (taxRate > 0 && cgstRate === 0 && sgstRate === 0 && igstRate === 0) {
        if (isInterState) igstRate = taxRate;
        else {
          cgstRate = taxRate / 2;
          sgstRate = taxRate / 2;
        }
      }

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

      let cgstAmount = 0, sgstAmount = 0, igstAmount = 0;
      if (isInterState) {
        igstAmount = (taxableAmount * (igstRate || taxRate || 0)) / 100;
      } else {
        cgstAmount = (taxableAmount * (cgstRate || (taxRate / 2) || 0)) / 100;
        sgstAmount = (taxableAmount * (sgstRate || (taxRate / 2) || 0)) / 100;
      }

      const lineTax = cgstAmount + sgstAmount + igstAmount;
      const effectiveTaxRate = isInterState ? (igstRate || taxRate || 0) : (cgstRate + sgstRate || taxRate || 0);

      return {
        po_id: po.id,
        line_number: index + 1,
        material_id: item.material_id,
        description: resolvePurchaseLineDescription(
          item,
          lineMaterialMap.get(parseInt(item.material_id, 10))
        ),
        quantity,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        discount_percent: discountPercent,
        discount_amount: lineDiscount,
        tax_rate: effectiveTaxRate,
        tax_amount: lineTax,
        cgst_rate: isInterState ? 0 : (cgstRate || (taxRate / 2) || 0),
        cgst_amount: cgstAmount,
        sgst_rate: isInterState ? 0 : (sgstRate || (taxRate / 2) || 0),
        sgst_amount: sgstAmount,
        igst_rate: isInterState ? (igstRate || taxRate || 0) : 0,
        igst_amount: igstAmount,
        line_total: taxableAmount + lineTax,
        hsn_code: item.hsn_code,
        delivery_date: item.delivery_date,
        storage_location_id: item.storage_location_id || null,
        remarks: item.remarks,
        line_weight_kg: computeLineWeightKg({
          quantity,
          line_weight_kg: item.line_weight_kg,
          material: lineMaterialMap.get(parseInt(item.material_id, 10)),
        }),
      };
    });

    await db.PurchaseOrderItem.bulkCreate(poItems, { transaction });

    await upsertPurchaseCostingAndMaterialLastRate({
      companyId: po.company_id,
      fiscalYearId: poData.fiscal_year_id || po.fiscal_year_id || null,
      vendorId: poData.vendor_id || po.vendor_id,
      poId: po.id,
      poDate: poData.po_date || po.po_date || new Date().toISOString().slice(0, 10),
      items,
      transaction
    });

    await transaction.commit();

    const updatedPO = await db.PurchaseOrder.findByPk(po.id, {
      include: [
        { model: db.PurchaseOrderItem, as: 'items', include: [{ model: db.Material, as: 'material' }, { model: db.UOM, as: 'uom' }] }
      ]
    });

    sendResponse(res, 200, true, 'Purchase Order updated successfully', updatedPO);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  if (!canEditPostedPoRate(req.user)) {
    return sendError(res, 'You are not authorized to edit rates for posted Purchase Orders', 403);
  }

  const po = await db.PurchaseOrder.findByPk(id, {
    include: [{ model: db.PurchaseOrderItem, as: 'items' }]
  });
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }
  if (companyId && Number(po.company_id) !== Number(companyId)) {
    return sendError(res, 'You do not have access to this Purchase Order', 403);
  }
  if (po.status !== 'POSTED') {
    return sendError(res, 'Rate editing is allowed only for POSTED Purchase Orders', 400);
  }

  const activeGrnCount = await db.GoodsReceiptNote.count({
    where: {
      po_id: po.id,
      status: { [Op.ne]: 'CANCELLED' }
    }
  });
  const activeVendorInvoiceCount = await db.VendorInvoice.count({
    where: {
      po_id: po.id,
      status: { [Op.ne]: 'CANCELLED' }
    }
  });
  if (activeGrnCount > 0 || activeVendorInvoiceCount > 0) {
    return sendError(
      res,
      'Rate edit is restricted because this PO is already linked to GRN or Vendor Invoice',
      400
    );
  }

  const inputItems = Array.isArray(req.body?.items) ? req.body.items : [];
  if (inputItems.length === 0) {
    return sendError(res, 'At least one rate change item is required', 400);
  }

  const existingItems = Array.isArray(po.items) ? po.items : [];
  const existingMap = new Map(existingItems.map((it) => [Number(it.id), it]));
  const requestedItemIds = new Set();
  for (const row of inputItems) {
    const itemId = Number(row?.item_id);
    const newRate = parseFloat(row?.unit_price);
    if (!Number.isInteger(itemId) || itemId <= 0 || !existingMap.has(itemId)) {
      return sendError(res, `Invalid PO item id: ${row?.item_id}`, 400);
    }
    if (requestedItemIds.has(itemId)) {
      return sendError(res, `Duplicate PO item id in payload: ${itemId}`, 400);
    }
    if (!Number.isFinite(newRate) || newRate < 0) {
      return sendError(res, `Invalid rate for item id ${itemId}`, 400);
    }
    requestedItemIds.add(itemId);
  }

  const nextRevisionNo = (parseInt(po.rev_no, 10) || 0) + 1;
  const transaction = await db.sequelize.transaction();
  try {
    const historyRows = [];
    for (const row of inputItems) {
      const itemId = Number(row.item_id);
      const newRate = parseFloat(row.unit_price) || 0;
      const item = existingMap.get(itemId);
      const oldRate = parseFloat(item.unit_price) || 0;
      const qty = parseFloat(item.quantity) || 0;
      const discountPercent = parseFloat(item.discount_percent) || 0;
      const taxableAmount = qty * newRate;
      const discountAmount = (taxableAmount * discountPercent) / 100;
      const taxableAfterDiscount = taxableAmount - discountAmount;
      const cgstRate = parseFloat(item.cgst_rate) || 0;
      const sgstRate = parseFloat(item.sgst_rate) || 0;
      const igstRate = parseFloat(item.igst_rate) || 0;
      const cgstAmount = (taxableAfterDiscount * cgstRate) / 100;
      const sgstAmount = (taxableAfterDiscount * sgstRate) / 100;
      const igstAmount = (taxableAfterDiscount * igstRate) / 100;
      const taxAmount = cgstAmount + sgstAmount + igstAmount;
      const lineTotal = taxableAfterDiscount + taxAmount;

      await item.update({
        unit_price: newRate,
        discount_amount: discountAmount,
        tax_amount: taxAmount,
        cgst_amount: cgstAmount,
        sgst_amount: sgstAmount,
        igst_amount: igstAmount,
        line_total: lineTotal
      }, { transaction });

      if (Math.abs(newRate - oldRate) > 0.0001) {
        historyRows.push({
          po_id: po.id,
          item_id: item.id,
          old_rate: oldRate,
          new_rate: newRate,
          changed_by: userId,
          changed_at: new Date(),
          revision_number: nextRevisionNo
        });
      }
    }

    const refreshedItems = await db.PurchaseOrderItem.findAll({
      where: { po_id: po.id },
      transaction
    });

    const totals = refreshedItems.reduce((acc, item) => {
      const qty = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const lineTotal = qty * unitPrice;
      acc.subTotal += lineTotal;
      acc.discountAmount += parseFloat(item.discount_amount) || 0;
      acc.taxAmount += parseFloat(item.tax_amount) || 0;
      return acc;
    }, { subTotal: 0, discountAmount: 0, taxAmount: 0 });

    await po.update({
      sub_total: totals.subTotal,
      discount_amount: totals.discountAmount,
      tax_amount: totals.taxAmount,
      total_amount: totals.subTotal - totals.discountAmount + totals.taxAmount,
      status: 'DRAFT',
      is_edited: true,
      previous_status: 'POSTED',
      rev_no: nextRevisionNo,
      submitted_at: null,
      submitted_by: null,
      approved_at: null,
      approved_by: null,
      posted_at: null,
      posted_by: null,
      updated_by: userId
    }, { transaction });

    if (historyRows.length > 0) {
      await db.PurchaseOrderRateHistory.bulkCreate(historyRows, { transaction });
    }

    await transaction.commit();

    const updatedPO = await db.PurchaseOrder.findByPk(po.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.BusinessPartner, as: 'vendor' },
        {
          model: db.PurchaseOrderItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'PO rates updated and workflow reset to DRAFT successfully', updatedPO);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const po = await db.PurchaseOrder.findByPk(id, {
    include: [{ model: db.PurchaseOrderItem, as: 'items' }]
  });
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  if (po.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Purchase Orders can be submitted', 400);
  }

  if (!po.items || po.items.length === 0) {
    return sendError(res, 'Cannot submit Purchase Order without items', 400);
  }

  const pendingCostingApprovals = await db.PurchaseCosting.count({
    where: {
      po_id: po.id,
      approval_status: 'PENDING'
    }
  });
  if (pendingCostingApprovals > 0) {
    return sendError(res, 'Price change approval pending in Purchase Costing. Approve/reject before submitting PO.', 400);
  }

  // Update PO status to SUBMITTED
  await po.update({
    status: 'SUBMITTED',
    submitted_at: new Date(),
    submitted_by: userId,
    updated_by: userId
  });

  // Create approval transactions
  try {
    const approvalService = require('../services/approvalService');
    await approvalService.createApprovalTransactions({
      companyId,
      moduleName: 'PURCHASE_ORDER',
      transactionId: po.id,
      transactionNumber: po.po_number,
      transactionAmount: parseFloat(po.total_amount) || 0,
      userId
    });
  } catch (error) {
    console.error('Error creating approval transactions:', error);
    // Don't fail the submission if approval creation fails
  }

  const updatedPO = await db.PurchaseOrder.findByPk(po.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'vendor' }
    ]
  });

  sendResponse(res, 200, true, 'Purchase Order submitted for approval successfully', updatedPO);
});

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

  const po = await db.PurchaseOrder.findByPk(id, {
    include: [{ model: db.PurchaseOrderItem, as: 'items' }]
  });
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  // Validate status transition (Purchase Order may use DRAFT or SUBMITTED)
  const currentStatus = po.status;
  let statusValidation;

  if (currentStatus === 'DRAFT') {
    statusValidation = statusValidationService.validateStatusTransition('purchaseOrder', currentStatus, 'APPROVED');
  } else if (currentStatus === 'SUBMITTED') {
    // SUBMITTED is not in our flow, but allow it for backward compatibility
    statusValidation = { valid: true };
  } else {
    statusValidation = { valid: false, message: `Only DRAFT or SUBMITTED Purchase Orders can be approved. Current status: ${currentStatus}` };
  }

  if (!statusValidation.valid) {
    return sendError(res, statusValidation.message, 400);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.purchaseOrder.canApprove(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  await po.update({
    status: 'APPROVED',
    approved_at: new Date(),
    approved_by: userId,
    updated_by: userId
  });

  const updatedPO = await db.PurchaseOrder.findByPk(po.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'vendor' }
    ]
  });

  sendResponse(res, 200, true, 'Purchase Order approved successfully', updatedPO);
});

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

  const po = await db.PurchaseOrder.findByPk(id);
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  if (po.status !== 'APPROVED') {
    return sendError(res, 'Only APPROVED Purchase Orders can be sent to vendor', 400);
  }

  await po.update({
    status: 'POSTED', // Using POSTED as SENT_TO_VENDOR equivalent
    posted_at: new Date(),
    posted_by: userId,
    updated_by: userId
  });

  const updatedPO = await db.PurchaseOrder.findByPk(po.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'vendor' }
    ]
  });

  sendResponse(res, 200, true, 'Purchase Order sent to vendor successfully', updatedPO);
});

exports.getNotificationSettings = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId || req.query?.company_id;
  const userId = req.user?.userId || null;
  if (!companyId) return sendError(res, 'Company ID is required', 400);
  const settings = await purchaseNotificationService.ensureSettings(companyId, userId);
  sendResponse(res, 200, true, 'Notification settings retrieved successfully', settings);
});

exports.updateNotificationSettings = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId || req.body?.company_id;
  const userId = req.user?.userId || null;
  if (!companyId) return sendError(res, 'Company ID is required', 400);

  const current = await purchaseNotificationService.ensureSettings(companyId, userId);
  const payload = req.body || {};
  const updateData = {
    auto_email_po: payload.auto_email_po !== undefined ? Boolean(payload.auto_email_po) : current.auto_email_po,
    manual_email_po: payload.manual_email_po !== undefined ? Boolean(payload.manual_email_po) : current.manual_email_po,
    auto_whatsapp_po: payload.auto_whatsapp_po !== undefined ? Boolean(payload.auto_whatsapp_po) : current.auto_whatsapp_po,
    manual_whatsapp_po: payload.manual_whatsapp_po !== undefined ? Boolean(payload.manual_whatsapp_po) : current.manual_whatsapp_po,
    email_subject_template: payload.email_subject_template ?? current.email_subject_template,
    email_body_template: payload.email_body_template ?? current.email_body_template,
    whatsapp_message_template: payload.whatsapp_message_template ?? current.whatsapp_message_template,
    smtp_host: payload.smtp_host ?? current.smtp_host,
    smtp_port: payload.smtp_port ?? current.smtp_port,
    smtp_secure: payload.smtp_secure !== undefined ? Boolean(payload.smtp_secure) : current.smtp_secure,
    smtp_user: payload.smtp_user ?? current.smtp_user,
    smtp_password: payload.smtp_password ?? current.smtp_password,
    smtp_from_email: payload.smtp_from_email ?? current.smtp_from_email,
    smtp_from_name: payload.smtp_from_name ?? current.smtp_from_name,
    whatsapp_api_url: payload.whatsapp_api_url ?? current.whatsapp_api_url,
    whatsapp_api_token: payload.whatsapp_api_token ?? current.whatsapp_api_token,
    status: payload.status || current.status || 'ACTIVE',
    updated_by: userId
  };
  await current.update(updateData);
  sendResponse(res, 200, true, 'Notification settings updated successfully', current);
});

exports.sendEmailToVendor = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const result = await purchaseNotificationService.sendEmailForPO({
    poId: id,
    userId,
    isManual: true
  });
  sendResponse(res, 200, true, 'Purchase Order email sent successfully', result);
});

exports.sendWhatsAppToVendor = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const result = await purchaseNotificationService.sendWhatsAppForPO({
    poId: id,
    userId,
    isManual: true
  });
  sendResponse(res, 200, true, 'Purchase Order WhatsApp sent successfully', result);
});

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

  const po = await db.PurchaseOrder.findByPk(id);
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  if (po.status !== 'APPROVED') {
    return sendError(res, 'Only APPROVED Purchase Orders can be posted', 400);
  }

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

  sendResponse(res, 200, true, 'Purchase Order posted successfully', po);
});

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

  const po = await db.PurchaseOrder.findByPk(id);
  if (!po) {
    return sendError(res, 'Purchase Order not found', 404);
  }

  if (po.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Purchase Orders can be deleted', 400);
  }

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

  try {
    await db.PurchaseOrderItem.destroy({ where: { po_id: id }, transaction });
    await po.destroy({ transaction });
    await transaction.commit();

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

/**
 * Create Gate Entry from Purchase Order
 * POST /purchaseorder/:id/create-gate-entry
 */
exports.createGateEntry = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const {
    gate_entry_number,
    gate_entry_date,
    invoice_number,
    invoice_date,
    lr_number,
    lr_date,
    transporter_name,
    vehicle_number,
    driver_name,
    driver_contact,
    destination_type,
    items
  } = req.body;
  const userId = req.user?.userId || null;

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

  try {
    const purchaseOrderToGateEntryService = require('../services/purchaseOrderToGateEntryService');

    const gateEntry = await purchaseOrderToGateEntryService.createGateEntryFromPurchaseOrder(id, {
      gateEntryNumber: gate_entry_number,
      gateEntryDate: gate_entry_date,
      invoiceNumber: invoice_number,
      invoiceDate: invoice_date,
      lrNumber: lr_number,
      lrDate: lr_date,
      transporterName: transporter_name,
      vehicleNumber: vehicle_number,
      driverName: driver_name,
      driverContact: driver_contact,
      destinationType: destination_type,
      items: items,
      userId
    });

    sendResponse(res, 201, true, 'Gate Entry created successfully from Purchase Order', gateEntry);
  } catch (error) {
    console.error('Error creating Gate Entry from Purchase Order:', error);
    sendError(res, error.message || 'Failed to create Gate Entry from Purchase Order', 400);
  }
});

