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 fgStockLedgerService = require('../services/fgStockLedgerService');
const stockLedgerService = require('../services/stockLedgerService');

const toNum = fgStockLedgerService.toNum;

let fgDispatchTableSchema = null;
const hasCol = (schema, col) => Boolean(schema && Object.prototype.hasOwnProperty.call(schema, col));
const getDispatchSchema = async () => {
  if (fgDispatchTableSchema) return fgDispatchTableSchema;
  try {
    fgDispatchTableSchema = await db.sequelize.getQueryInterface().describeTable('fg_dispatch');
  } catch (_) {
    fgDispatchTableSchema = {};
  }
  return fgDispatchTableSchema;
};

const getDispatchAttributes = (schema) => {
  const attrs = [
    'id',
    'company_id',
    'plant_id',
    'fiscal_year_id',
    'dispatch_no',
    'dispatch_date',
    'customer_id',
    'item_id',
    'warehouse_id',
    'available_stock',
    'dispatch_qty',
    'balance_after_dispatch',
    'status',
    'remarks',
    'created_by',
    'updated_by',
    'created_at',
    'updated_at'
  ];
  if (hasCol(schema, 'route_card_id')) attrs.push('route_card_id');
  if (hasCol(schema, 'work_order_id')) attrs.push('work_order_id');
  if (hasCol(schema, 'production_order_id')) attrs.push('production_order_id');
  if (hasCol(schema, 'production_qty')) attrs.push('production_qty');
  if (hasCol(schema, 'accepted_qty')) attrs.push('accepted_qty');
  if (hasCol(schema, 'invoice_id')) attrs.push('invoice_id');
  if (hasCol(schema, 'delivery_status')) attrs.push('delivery_status');
  if (hasCol(schema, 'transporter_name')) attrs.push('transporter_name');
  if (hasCol(schema, 'vehicle_no')) attrs.push('vehicle_no');
  if (hasCol(schema, 'driver_name')) attrs.push('driver_name');
  if (hasCol(schema, 'submitted_at')) attrs.push('submitted_at');
  if (hasCol(schema, 'submitted_by')) attrs.push('submitted_by');
  if (hasCol(schema, 'approved_at')) attrs.push('approved_at');
  if (hasCol(schema, 'approved_by')) attrs.push('approved_by');
  if (hasCol(schema, 'transit_started_at')) attrs.push('transit_started_at');
  if (hasCol(schema, 'transit_started_by')) attrs.push('transit_started_by');
  return attrs;
};

const getDispatchIncludes = (schema) => {
  const includes = [
    { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
    { model: db.Material, as: 'item', attributes: ['id', 'material_number', 'description'] },
    { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'] }
  ];
  if (hasCol(schema, 'invoice_id')) includes.push({ model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number', 'customer_po_no'], required: false });
  if (hasCol(schema, 'route_card_id')) includes.push({ model: db.RouteCard, as: 'routeCard', attributes: ['id', 'route_card_number'], required: false });
  if (hasCol(schema, 'work_order_id')) includes.push({ model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false });
  if (hasCol(schema, 'submitted_by')) includes.push({ model: db.User, as: 'submittedByUser', attributes: ['id', 'username', 'first_name', 'last_name'], required: false });
  if (hasCol(schema, 'approved_by')) includes.push({ model: db.User, as: 'approvedByUser', attributes: ['id', 'username', 'first_name', 'last_name'], required: false });
  if (hasCol(schema, 'transit_started_by')) includes.push({ model: db.User, as: 'transitStartedByUser', attributes: ['id', 'username', 'first_name', 'last_name'], required: false });
  return includes;
};

const toUiStatus = (status) => {
  const st = String(status || '').toUpperCase();
  if (st === 'IN_TRANSIT') return 'IN_TRANSIT';
  if (st === 'COMPLETED') return 'COMPLETED';
  if (st === 'CANCELLED') return 'CANCELLED';
  return 'PENDING';
};

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

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

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

  const schema = await getDispatchSchema();
  if (hasCol(schema, 'invoice_id')) {
    const dispatchedInvoiceRows = await db.FGDispatch.findAll({
      where: {
        invoice_id: { [Op.ne]: null },
        status: { [Op.ne]: 'CANCELLED' }
      },
      attributes: ['invoice_id'],
      group: ['invoice_id'],
      raw: true
    });
    const dispatchedInvoiceIds = dispatchedInvoiceRows
      .map((row) => parseInt(row.invoice_id, 10))
      .filter((id) => Number.isFinite(id) && id > 0);
    if (dispatchedInvoiceIds.length > 0) {
      where.id = { [Op.notIn]: dispatchedInvoiceIds };
    }
  }

  const invoices = await db.SalesInvoice.findAll({
    where,
    attributes: ['id', 'invoice_number', 'invoice_date', 'customer_id', 'customer_po_id', 'customer_po_no', 'total_amount'],
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
      { model: db.CustomerPO, as: 'customerPO', attributes: ['id', 'po_number'], required: false },
      { model: db.SalesInvoiceItem, as: 'items', attributes: ['id', 'quantity'], required: false }
    ],
    order: [['invoice_date', 'DESC'], ['id', 'DESC']]
  });

  const payload = invoices.map((inv) => {
    const totalInvoiceQty = (inv.items || []).reduce((sum, it) => sum + toNum(it.quantity), 0);
    return {
      id: inv.id,
      invoice_number: inv.invoice_number,
      invoice_date: inv.invoice_date,
      customer_id: inv.customer_id,
      customer_name: inv.customer?.name || '',
      customer_code: inv.customer?.bp_number || '',
      customer_po_id: inv.customer_po_id || inv.customerPO?.id || null,
      customer_po_no: inv.customer_po_no || inv.customerPO?.po_number || '',
      total_invoice_qty: totalInvoiceQty,
      total_amount: toNum(inv.total_amount)
    };
  });

  sendResponse(res, 200, true, 'FG dispatch invoice options retrieved successfully', payload);
});

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

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

  const invoice = await db.SalesInvoice.findOne({
    where: {
      id: invoiceId,
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      status: { [Op.in]: ['DRAFT', 'APPROVED'] }
    },
    attributes: ['id', 'invoice_number', 'invoice_date', 'customer_id', 'customer_po_id', 'customer_po_no', 'shipping_address', 'billing_address', 'total_amount'],
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
      { model: db.CustomerPO, as: 'customerPO', attributes: ['id', 'po_number'], required: false },
      {
        model: db.SalesInvoiceItem,
        as: 'items',
        attributes: ['id', 'material_id', 'quantity'],
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'], required: false }
        ],
        required: false
      }
    ]
  });

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

  const dispatchNoPreview = await generateDispatchNumber(companyId, fiscalYearId);
  const activeWarehouses = await db.StorageLocation.findAll({
    where: {
      company_id: companyId,
      plant_id: plantId,
      status: 'ACTIVE',
      storage_location_type: 'WAREHOUSE'
    },
    attributes: ['id', 'storage_location_code', 'description'],
    order: [['storage_location_code', 'ASC']]
  });
  const defaultWarehouseId = activeWarehouses[0]?.id || null;

  const invoiceMaterialIds = (invoice.items || [])
    .map((it) => parseInt(it.material_id, 10))
    .filter((id) => Number.isFinite(id) && id > 0);

  let latestInwardWarehouseByItem = new Map();
  if (invoiceMaterialIds.length > 0) {
    const inwardRows = await db.ProductionFGInward.findAll({
      where: {
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        finished_material_id: { [Op.in]: invoiceMaterialIds },
        storage_location_id: { [Op.ne]: null }
      },
      attributes: ['finished_material_id', 'storage_location_id', 'inward_entry_date', 'id'],
      order: [['inward_entry_date', 'DESC'], ['id', 'DESC']]
    });

    latestInwardWarehouseByItem = new Map();
    inwardRows.forEach((row) => {
      const itemKey = parseInt(row.finished_material_id, 10);
      const whId = parseInt(row.storage_location_id, 10);
      if (!Number.isFinite(itemKey) || itemKey <= 0 || !Number.isFinite(whId) || whId <= 0) return;
      if (!latestInwardWarehouseByItem.has(itemKey)) {
        latestInwardWarehouseByItem.set(itemKey, whId);
      }
    });
  }

  const itemRows = [];
  for (const it of (invoice.items || [])) {
    const materialId = parseInt(it.material_id, 10);
    if (!Number.isFinite(materialId) || materialId <= 0) continue;
    const invoiceQty = toNum(it.quantity);
    const suggestedWarehouseId = latestInwardWarehouseByItem.get(materialId) || defaultWarehouseId;
    let availableStock = 0;
    if (Number.isFinite(suggestedWarehouseId) && suggestedWarehouseId > 0) {
      // eslint-disable-next-line no-await-in-loop
      availableStock = await fgStockLedgerService.getCurrentBalance({
        companyId,
        plantId,
        fiscalYearId,
        itemId: materialId,
        warehouseId: suggestedWarehouseId
      });
    }
    itemRows.push({
      item_id: materialId,
      item_code: it.material?.material_number || '',
      item_description: it.material?.description || '',
      invoice_qty: invoiceQty,
      total_qty: invoiceQty,
      suggested_warehouse_id: suggestedWarehouseId,
      suggested_available_stock: toNum(availableStock),
      suggested_dispatch_qty: Math.min(invoiceQty, toNum(availableStock))
    });
  }

  const totalInvoiceQty = itemRows.reduce((sum, it) => sum + toNum(it.invoice_qty), 0);
  const firstItem = itemRows[0] || null;

  sendResponse(res, 200, true, 'FG dispatch invoice prefill retrieved successfully', {
    dispatch_no: dispatchNoPreview,
    invoice_id: invoice.id,
    invoice_no: invoice.invoice_number,
    invoice_date: invoice.invoice_date,
    customer_id: invoice.customer_id,
    customer_name: invoice.customer?.name || '',
    customer_code: invoice.customer?.bp_number || '',
    delivery_address: invoice.shipping_address || invoice.billing_address || '',
    customer_po_id: invoice.customer_po_id || invoice.customerPO?.id || null,
    customer_po_no: invoice.customer_po_no || invoice.customerPO?.po_number || '',
    total_invoice_qty: totalInvoiceQty,
    items: itemRows,
    first_item: firstItem
  });
});

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

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

  const inwardRows = await db.ProductionFGInward.findAll({
    where: {
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      finished_material_id: { [Op.ne]: null }
    },
    attributes: ['finished_material_id'],
    group: ['finished_material_id']
  });

  const itemIds = inwardRows.map((r) => parseInt(r.finished_material_id, 10)).filter((v) => Number.isFinite(v) && v > 0);
  if (itemIds.length === 0) {
    return sendResponse(res, 200, true, 'FG inward items retrieved successfully', []);
  }

  const items = await db.Material.findAll({
    where: { id: { [Op.in]: itemIds } },
    attributes: ['id', 'material_number', 'description'],
    order: [['material_number', 'ASC']]
  });

  sendResponse(res, 200, true, 'FG inward items retrieved successfully', items);
});

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

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

  const balance = await fgStockLedgerService.getCurrentBalance({
    companyId,
    plantId,
    fiscalYearId,
    itemId,
    warehouseId
  });

  sendResponse(res, 200, true, 'Available stock retrieved successfully', { available_stock: balance });
});

const generateDispatchNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'FG_DISPATCH', fiscalYearId, transaction);
  } catch (e) {
    const year = new Date().getFullYear();
    const prefix = `FGD${year}`;
    const last = await db.FGDispatch.findOne({
      where: {
        company_id: companyId,
        dispatch_no: { [Op.like]: `${prefix}%` }
      },
      attributes: ['dispatch_no'],
      order: [['dispatch_no', 'DESC']],
      ...(transaction ? { transaction } : {})
    });

    let seq = 1;
    if (last?.dispatch_no) {
      const parsed = parseInt(String(last.dispatch_no).slice(-4), 10) || 0;
      seq = parsed + 1;
    }
    return `${prefix}${String(seq).padStart(4, '0')}`;
  }
};

async function completeDispatch(dispatch, userId, transaction, nextStatus = 'COMPLETED', extraPatch = {}) {
  const latestBalance = await fgStockLedgerService.getCurrentBalance({
    companyId: dispatch.company_id,
    plantId: dispatch.plant_id,
    fiscalYearId: dispatch.fiscal_year_id,
    itemId: dispatch.item_id,
    warehouseId: dispatch.warehouse_id,
    transaction
  });

  const dispatchQty = toNum(dispatch.dispatch_qty);
  if (dispatchQty > latestBalance) {
    throw new Error(`Dispatch qty cannot be greater than available stock (${latestBalance})`);
  }

  const balanceAfterDispatch = Math.max(latestBalance - dispatchQty, 0);

  await dispatch.update(
    {
      available_stock: latestBalance,
      balance_after_dispatch: balanceAfterDispatch,
      status: nextStatus,
      updated_by: userId,
      ...extraPatch
    },
    { transaction }
  );

  await fgStockLedgerService.createEntry(
    {
      company_id: dispatch.company_id,
      plant_id: dispatch.plant_id,
      fiscal_year_id: dispatch.fiscal_year_id,
      item_id: dispatch.item_id,
      warehouse_id: dispatch.warehouse_id,
      reference_type: 'DISPATCH',
      reference_id: dispatch.id,
      movement_date: dispatch.dispatch_date,
      inward_qty: 0,
      outward_qty: dispatchQty,
      created_by: userId
    },
    transaction
  );

  const warehouse = await db.StorageLocation.findByPk(dispatch.warehouse_id, {
    attributes: ['storage_location_code'],
    transaction
  });
  await stockLedgerService.createLedgerEntry(
    {
      company_id: dispatch.company_id,
      plant_id: dispatch.plant_id,
      material_id: dispatch.item_id,
      transaction_date: dispatch.dispatch_date,
      transaction_type: 'ISSUE',
      reference_type: 'OTHER',
      reference_id: dispatch.id,
      reference_number: dispatch.dispatch_no,
      storage_location: warehouse?.storage_location_code || null,
      quantity_in: 0,
      quantity_out: dispatchQty,
      remarks: `FG Dispatch: ${dispatch.dispatch_no}`,
      created_by: userId
    },
    transaction
  );
}

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId, itemId, warehouseId, fromDate, toDate } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {};
  if (companyId) where.company_id = companyId;
  else if (req.user && !req.user.isSuperAdmin) where.company_id = req.user.companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (status) where.status = status;
  if (itemId) where.item_id = itemId;
  if (warehouseId) where.warehouse_id = warehouseId;

  if (fromDate || toDate) {
    where.dispatch_date = {};
    if (fromDate) where.dispatch_date[Op.gte] = new Date(fromDate);
    if (toDate) {
      const dt = new Date(toDate);
      dt.setHours(23, 59, 59, 999);
      where.dispatch_date[Op.lte] = dt;
    }
  }

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

  const schema = await getDispatchSchema();
  const records = await db.FGDispatch.findAndCountAll({
    where,
    attributes: getDispatchAttributes(schema),
    include: getDispatchIncludes(schema),
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const rows = records.rows || [];
  const invoiceIds = rows
    .map((r) => parseInt(r.invoice_id, 10))
    .filter((v) => Number.isFinite(v) && v > 0);

  const totalInvoiceQtyByInvoiceId = new Map();
  const invoiceQtyByInvoiceAndItem = new Map();
  if (invoiceIds.length > 0) {
    const invoiceItemRows = await db.SalesInvoiceItem.findAll({
      where: { invoice_id: { [Op.in]: invoiceIds } },
      attributes: ['invoice_id', 'material_id', 'quantity'],
      raw: true
    });
    invoiceItemRows.forEach((it) => {
      const invId = parseInt(it.invoice_id, 10);
      const matId = parseInt(it.material_id, 10);
      const qty = toNum(it.quantity);
      totalInvoiceQtyByInvoiceId.set(invId, toNum(totalInvoiceQtyByInvoiceId.get(invId) || 0) + qty);
      if (Number.isFinite(matId) && matId > 0) {
        const key = `${invId}:${matId}`;
        invoiceQtyByInvoiceAndItem.set(key, toNum(invoiceQtyByInvoiceAndItem.get(key) || 0) + qty);
      }
    });
  }

  const invoiceToPOMap = new Map();
  if (invoiceIds.length > 0) {
    const invoiceRows = await db.SalesInvoice.findAll({
      where: { id: { [Op.in]: invoiceIds } },
      attributes: ['id', 'customer_po_id'],
      raw: true
    });
    invoiceRows.forEach((inv) => {
      const invId = parseInt(inv.id, 10);
      const poId = parseInt(inv.customer_po_id, 10);
      if (Number.isFinite(invId) && invId > 0 && Number.isFinite(poId) && poId > 0) {
        invoiceToPOMap.set(invId, poId);
      }
    });
  }

  const poItemKeySet = new Set();
  rows.forEach((r) => {
    const invId = parseInt(r.invoice_id, 10);
    const itemId = parseInt(r.item_id, 10);
    const poId = invoiceToPOMap.get(invId);
    if (Number.isFinite(poId) && poId > 0 && Number.isFinite(itemId) && itemId > 0) {
      poItemKeySet.add(`${poId}:${itemId}`);
    }
  });

  const orderQtyByPOAndItem = new Map();
  if (poItemKeySet.size > 0) {
    const poItemScopes = Array.from(poItemKeySet).map((k) => {
      const [poId, materialId] = k.split(':').map((n) => parseInt(n, 10));
      return { po_id: poId, material_id: materialId };
    });
    const poItemRows = await db.CustomerPOItem.findAll({
      where: { [Op.or]: poItemScopes },
      attributes: [
        'po_id',
        'material_id',
        [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('quantity')), 0), 'order_qty']
      ],
      group: ['po_id', 'material_id'],
      raw: true
    });
    poItemRows.forEach((it) => {
      const key = `${parseInt(it.po_id, 10)}:${parseInt(it.material_id, 10)}`;
      orderQtyByPOAndItem.set(key, toNum(it.order_qty));
    });
  }

  const scopeRows = rows.map((r) => ({
    company_id: r.company_id,
    plant_id: r.plant_id,
    fiscal_year_id: r.fiscal_year_id,
    item_id: r.item_id,
    warehouse_id: r.warehouse_id
  }));
  const uniqueScopes = Array.from(new Set(scopeRows.map((s) => `${s.company_id}:${s.plant_id}:${s.fiscal_year_id}:${s.item_id}:${s.warehouse_id}`)))
    .map((k) => {
      const [company_id, plant_id, fiscal_year_id, item_id, warehouse_id] = k.split(':').map((n) => parseInt(n, 10));
      return { company_id, plant_id, fiscal_year_id, item_id, warehouse_id };
    });

  const inwardLatestByScope = new Map();
  if (uniqueScopes.length) {
    const inwardRows = await db.ProductionFGInward.findAll({
      where: { [Op.or]: uniqueScopes.map((s) => ({
        company_id: s.company_id,
        plant_id: s.plant_id,
        fiscal_year_id: s.fiscal_year_id,
        finished_material_id: s.item_id,
        storage_location_id: s.warehouse_id
      })) },
      attributes: ['id', 'company_id', 'plant_id', 'fiscal_year_id', 'finished_material_id', 'storage_location_id', 'production_order_id', 'production_qty'],
      order: [['inward_entry_date', 'DESC'], ['id', 'DESC']]
    });
    inwardRows.forEach((ir) => {
      const key = `${ir.company_id}:${ir.plant_id}:${ir.fiscal_year_id}:${ir.finished_material_id}:${ir.storage_location_id}`;
      if (!inwardLatestByScope.has(key)) inwardLatestByScope.set(key, ir);
    });
  }

  const dispatchedByScope = new Map();
  await Promise.all(uniqueScopes.map(async (s) => {
    const total = await db.FGDispatch.sum('dispatch_qty', {
      where: {
        company_id: s.company_id,
        plant_id: s.plant_id,
        fiscal_year_id: s.fiscal_year_id,
        item_id: s.item_id,
        warehouse_id: s.warehouse_id,
        status: { [Op.ne]: 'CANCELLED' }
      }
    });
    dispatchedByScope.set(`${s.company_id}:${s.plant_id}:${s.fiscal_year_id}:${s.item_id}:${s.warehouse_id}`, toNum(total));
  }));

  const hasWorkOrderCol = hasCol(schema, 'work_order_id');
  const hasInvoiceCol = hasCol(schema, 'invoice_id');
  const orderScopeKeys = new Set();
  rows.forEach((r) => {
    const rowJson = r.toJSON();
    const itemId = parseInt(rowJson.item_id, 10);
    if (!Number.isFinite(itemId) || itemId <= 0) return;
    const workOrderId = parseInt(rowJson.work_order_id, 10);
    const invoiceId = parseInt(rowJson.invoice_id, 10);
    if (hasWorkOrderCol && Number.isFinite(workOrderId) && workOrderId > 0) {
      orderScopeKeys.add(`WO:${workOrderId}:${itemId}`);
    } else if (hasInvoiceCol && Number.isFinite(invoiceId) && invoiceId > 0) {
      orderScopeKeys.add(`INV:${invoiceId}:${itemId}`);
    }
  });

  const dispatchedByOrderScope = new Map();
  const dispatchedByInvoiceScope = new Map();
  await Promise.all(Array.from(orderScopeKeys).map(async (key) => {
    const [kind, idPart, itemIdPart] = key.split(':');
    const scopedItemId = parseInt(itemIdPart, 10);
    const scopedId = parseInt(idPart, 10);
    if (!Number.isFinite(scopedItemId) || scopedItemId <= 0 || !Number.isFinite(scopedId) || scopedId <= 0) {
      dispatchedByOrderScope.set(key, 0);
      return;
    }
    const where = {
      item_id: scopedItemId,
      status: { [Op.ne]: 'CANCELLED' }
    };
    if (kind === 'WO') where.work_order_id = scopedId;
    else where.invoice_id = scopedId;

    const total = await db.FGDispatch.sum('dispatch_qty', { where });
    dispatchedByOrderScope.set(key, toNum(total));
  }));

  const invoiceScopeKeys = new Set();
  rows.forEach((r) => {
    const rowJson = r.toJSON();
    const itemId = parseInt(rowJson.item_id, 10);
    const invoiceId = parseInt(rowJson.invoice_id, 10);
    if (Number.isFinite(itemId) && itemId > 0 && Number.isFinite(invoiceId) && invoiceId > 0) {
      invoiceScopeKeys.add(`${invoiceId}:${itemId}`);
    }
  });
  await Promise.all(Array.from(invoiceScopeKeys).map(async (key) => {
    const [invoiceIdPart, itemIdPart] = key.split(':').map((n) => parseInt(n, 10));
    if (!Number.isFinite(invoiceIdPart) || invoiceIdPart <= 0 || !Number.isFinite(itemIdPart) || itemIdPart <= 0) {
      dispatchedByInvoiceScope.set(key, 0);
      return;
    }
    const total = await db.FGDispatch.sum('dispatch_qty', {
      where: {
        invoice_id: invoiceIdPart,
        item_id: itemIdPart,
        status: { [Op.ne]: 'CANCELLED' }
      }
    });
    dispatchedByInvoiceScope.set(key, toNum(total));
  }));

  const productionOrderIds = new Set();
  rows.forEach((r) => {
    const rowJson = r.toJSON();
    const rowScopeKey = `${rowJson.company_id}:${rowJson.plant_id}:${rowJson.fiscal_year_id}:${rowJson.item_id}:${rowJson.warehouse_id}`;
    const inward = inwardLatestByScope.get(rowScopeKey);
    const productionPoId = (hasCol(schema, 'production_order_id') ? rowJson.production_order_id : null) || inward?.production_order_id || null;
    if (productionPoId) productionOrderIds.add(productionPoId);
  });

  const poMap = new Map();
  if (productionOrderIds.size > 0) {
    const pos = await db.ProductionOrder.findAll({
      where: { id: { [Op.in]: Array.from(productionOrderIds) } },
      attributes: ['id', 'production_order_number']
    });
    pos.forEach((po) => poMap.set(po.id, po.production_order_number));
  }

  records.rows = rows.map((r) => {
    const rowJson = r.toJSON();
    const rowScopeKey = `${rowJson.company_id}:${rowJson.plant_id}:${rowJson.fiscal_year_id}:${rowJson.item_id}:${rowJson.warehouse_id}`;
    const inward = inwardLatestByScope.get(rowScopeKey);
    const productionQty = hasCol(schema, 'production_qty') ? toNum(rowJson.production_qty) : toNum(inward?.production_qty);
    const totalDispatched = toNum(dispatchedByScope.get(rowScopeKey));
    const balanceDispatchQty = Math.max(productionQty - totalDispatched, 0);
    const dispatchStatusByProduction = productionQty > 0 && totalDispatched >= productionQty ? 'FULLY_DISPATCHED' : 'PARTIALLY_DISPATCHED';
    const poId = (hasCol(schema, 'production_order_id') ? rowJson.production_order_id : null) || inward?.production_order_id || null;
    const invoiceId = parseInt(rowJson.invoice_id, 10);
    const materialId = parseInt(rowJson.item_id, 10);
    const workOrderId = parseInt(rowJson.work_order_id, 10);
    const invoiceQty = (Number.isFinite(invoiceId) && Number.isFinite(materialId))
      ? toNum(invoiceQtyByInvoiceAndItem.get(`${invoiceId}:${materialId}`) || 0)
      : 0;
    const invoiceScopeKey = (Number.isFinite(invoiceId) && Number.isFinite(materialId))
      ? `${invoiceId}:${materialId}`
      : null;
    const invoiceDispatchQty = invoiceScopeKey ? toNum(dispatchedByInvoiceScope.get(invoiceScopeKey) || 0) : 0;
    const invoiceDispatchStatus = invoiceQty > 0
      ? (invoiceDispatchQty >= invoiceQty ? 'FULLY_DISPATCHED' : 'PARTIALLY_DISPATCHED')
      : dispatchStatusByProduction;
    const customerPoId = invoiceToPOMap.get(invoiceId);
    const orderQty = (Number.isFinite(customerPoId) && Number.isFinite(materialId))
      ? toNum(orderQtyByPOAndItem.get(`${customerPoId}:${materialId}`) || 0)
      : 0;
    const orderScopeKey = (hasWorkOrderCol && Number.isFinite(workOrderId) && workOrderId > 0)
      ? `WO:${workOrderId}:${materialId}`
      : ((hasInvoiceCol && Number.isFinite(invoiceId) && invoiceId > 0) ? `INV:${invoiceId}:${materialId}` : null);
    const orderDispatchQty = orderScopeKey ? toNum(dispatchedByOrderScope.get(orderScopeKey) || 0) : 0;
    const orderBalanceQty = Math.max(orderQty - orderDispatchQty, 0);
    const orderDispatchStatus = orderQty > 0
      ? (orderDispatchQty >= orderQty ? 'FULLY_DISPATCHED' : 'PARTIALLY_DISPATCHED')
      : invoiceDispatchStatus;
    const totalQty = orderQty > 0
      ? orderQty
      : (Number.isFinite(invoiceId) ? toNum(totalInvoiceQtyByInvoiceId.get(invoiceId) || 0) : 0);

    return {
      ...rowJson,
      production_qty: productionQty,
      production_order_id: poId,
      production_order_number: poId ? (poMap.get(poId) || '') : '',
      balance_dispatch_qty: balanceDispatchQty,
      dispatch_status: invoiceDispatchStatus,
      order_qty_status: orderDispatchStatus,
      total_qty: totalQty,
      invoice_qty: invoiceQty,
      invoice_dispatch_qty: invoiceDispatchQty,
      order_dispatch_qty: orderDispatchQty,
      order_balance_qty: orderBalanceQty,
      ui_status: toUiStatus(rowJson.status)
    };
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'FG dispatch entries retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const schema = await getDispatchSchema();
  const record = await db.FGDispatch.findByPk(req.params.id, {
    attributes: getDispatchAttributes(schema),
    include: getDispatchIncludes(schema)
  });
  if (!record) return sendError(res, 'FG dispatch record not found', 404);
  const plain = record.toJSON ? record.toJSON() : { ...record };
  plain.ui_status = toUiStatus(plain.status);
  sendResponse(res, 200, true, 'FG dispatch entry retrieved successfully', plain);
});

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

  const invoiceId = parseInt(data.invoice_id, 10);
  const itemId = parseInt(data.item_id, 10);
  const warehouseId = parseInt(data.warehouse_id, 10);
  let customerId = parseInt(data.customer_id, 10);
  const dispatchQty = toNum(data.dispatch_qty);
  const status = String(data.status || 'PENDING').toUpperCase();

  if (!Number.isFinite(itemId) || itemId <= 0) return sendError(res, 'item_id is required', 400);
  if (!Number.isFinite(warehouseId) || warehouseId <= 0) return sendError(res, 'warehouse_id is required', 400);
  if (dispatchQty <= 0) return sendError(res, 'dispatch_qty should be greater than 0', 400);
  if (status !== 'PENDING') return sendError(res, 'New FG dispatch must start in PENDING status', 400);

  const transaction = await db.sequelize.transaction();
  try {
    const schema = await getDispatchSchema();
    const hasExtendedCols = hasCol(schema, 'route_card_id') && hasCol(schema, 'work_order_id') && hasCol(schema, 'production_qty') && hasCol(schema, 'accepted_qty');
    const hasInvoiceCol = hasCol(schema, 'invoice_id');
    const warehouse = await db.StorageLocation.findByPk(warehouseId, { transaction });
    if (!warehouse) throw new Error('Warehouse not found');
    if (String(warehouse.status || '').toUpperCase() !== 'ACTIVE') throw new Error('Warehouse must be ACTIVE');
    if (String(warehouse.storage_location_type || '').toUpperCase() !== 'WAREHOUSE') throw new Error('Storage location must be of WAREHOUSE type');
    if (reqCompanyId && String(warehouse.company_id) !== String(reqCompanyId)) throw new Error('Access denied for this warehouse');

    const companyId = reqCompanyId || warehouse.company_id;
    const plantId = warehouse.plant_id;
    const fiscalYearId = parseInt(data.fiscal_year_id, 10);
    if (!Number.isFinite(fiscalYearId) || fiscalYearId <= 0) throw new Error('fiscal_year_id is required');

    let invoice = null;
    if (Number.isFinite(invoiceId) && invoiceId > 0) {
      invoice = await db.SalesInvoice.findOne({
        where: {
          id: invoiceId,
          company_id: companyId,
          plant_id: plantId,
          fiscal_year_id: fiscalYearId
        },
        include: [{ model: db.SalesInvoiceItem, as: 'items', attributes: ['material_id', 'quantity'] }],
        transaction
      });
      if (!invoice) throw new Error('Sales invoice not found');
      customerId = invoice.customer_id || customerId;
      const hasItemInInvoice = (invoice.items || []).some((it) => String(it.material_id) === String(itemId));
      if (!hasItemInInvoice) throw new Error('Selected item does not belong to selected invoice');
    }

    if (!Number.isFinite(customerId) || customerId <= 0) throw new Error('customer_id is required');

    const availableStock = await fgStockLedgerService.getCurrentBalance({
      companyId,
      plantId,
      fiscalYearId,
      itemId,
      warehouseId,
      transaction
    });

    let latestInward = null;
    if (hasExtendedCols) {
      latestInward = await db.ProductionFGInward.findOne({
        where: {
          company_id: companyId,
          plant_id: plantId,
          fiscal_year_id: fiscalYearId,
          finished_material_id: itemId,
          storage_location_id: warehouseId
        },
        attributes: ['id', 'route_card_id', 'work_order_id', 'production_qty', 'accepted_qty'],
        order: [['inward_entry_date', 'DESC'], ['id', 'DESC']],
        transaction
      });
    }

    const createData = {
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      dispatch_no: await generateDispatchNumber(companyId, fiscalYearId, transaction),
      dispatch_date: data.dispatch_date ? new Date(data.dispatch_date) : new Date(),
      customer_id: customerId,
      item_id: itemId,
      warehouse_id: warehouseId,
      available_stock: availableStock,
      dispatch_qty: dispatchQty,
      balance_after_dispatch: availableStock,
      remarks: data.remarks || null,
      status,
      ...(hasCol(schema, 'delivery_status') ? { delivery_status: 'NOT_DELIVERED' } : {}),
      ...(hasCol(schema, 'transporter_name') ? { transporter_name: data.transporter_name || null } : {}),
      ...(hasCol(schema, 'vehicle_no') ? { vehicle_no: data.vehicle_no || null } : {}),
      ...(hasCol(schema, 'driver_name') ? { driver_name: data.driver_name || null } : {}),
      created_by: userId,
      updated_by: userId
    };
    if (hasInvoiceCol) createData.invoice_id = Number.isFinite(invoiceId) && invoiceId > 0 ? invoiceId : null;
    if (hasExtendedCols) {
      createData.route_card_id = latestInward?.route_card_id || null;
      createData.work_order_id = latestInward?.work_order_id || null;
      if (hasCol(schema, 'production_order_id')) createData.production_order_id = latestInward?.production_order_id || null;
      createData.production_qty = toNum(latestInward?.production_qty);
      createData.accepted_qty = toNum(latestInward?.accepted_qty);
    }

    const dispatch = await db.FGDispatch.create(createData, { transaction });

    // Business rule: stock gets reduced at dispatch creation itself.
    await completeDispatch(dispatch, userId, transaction, 'PENDING');

    await transaction.commit();
    const schemaAfter = await getDispatchSchema();
    const record = await db.FGDispatch.findByPk(dispatch.id, {
      attributes: getDispatchAttributes(schemaAfter),
      include: getDispatchIncludes(schemaAfter)
    });
    sendResponse(res, 201, true, 'FG dispatch created successfully', record);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to create FG dispatch', 400);
  }
});

const getRefreshedDispatch = async (id) => {
  const schema = await getDispatchSchema();
  const refreshed = await db.FGDispatch.findByPk(id, {
    attributes: getDispatchAttributes(schema),
    include: getDispatchIncludes(schema)
  });
  if (!refreshed) return null;
  const plain = refreshed.toJSON ? refreshed.toJSON() : { ...refreshed };
  plain.ui_status = toUiStatus(plain.status);
  return plain;
};

exports.submit = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const transaction = await db.sequelize.transaction();
  try {
    const schema = await getDispatchSchema();
    const record = await db.FGDispatch.findByPk(id, { transaction });
    if (!record) throw new Error('FG dispatch record not found');
    if (String(record.status || '').toUpperCase() !== 'PENDING') throw new Error('Only PENDING dispatch can be submitted');
    if (hasCol(schema, 'submitted_at') && record.submitted_at) throw new Error('Dispatch is already submitted');

    await record.update(
      {
        ...(hasCol(schema, 'submitted_at') ? { submitted_at: new Date() } : {}),
        ...(hasCol(schema, 'submitted_by') ? { submitted_by: userId } : {}),
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();
    const refreshed = await getRefreshedDispatch(id);
    sendResponse(res, 200, true, 'FG dispatch submitted successfully', refreshed);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to submit FG dispatch', 400);
  }
});

exports.approve = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const transaction = await db.sequelize.transaction();
  try {
    const schema = await getDispatchSchema();
    const record = await db.FGDispatch.findByPk(id, { transaction });
    if (!record) throw new Error('FG dispatch record not found');
    if (String(record.status || '').toUpperCase() !== 'PENDING') throw new Error('Only PENDING dispatch can be approved');
    if (hasCol(schema, 'submitted_at') && !record.submitted_at) throw new Error('Submit dispatch before approval');
    if (hasCol(schema, 'approved_at') && record.approved_at) throw new Error('Dispatch is already approved');

    await record.update(
      {
        ...(hasCol(schema, 'approved_at') ? { approved_at: new Date() } : {}),
        ...(hasCol(schema, 'approved_by') ? { approved_by: userId } : {}),
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();
    const refreshed = await getRefreshedDispatch(id);
    sendResponse(res, 200, true, 'FG dispatch approved successfully', refreshed);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to approve FG dispatch', 400);
  }
});

exports.start = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const body = req.body || {};
  const transaction = await db.sequelize.transaction();
  try {
    const schema = await getDispatchSchema();
    const record = await db.FGDispatch.findByPk(id, { transaction });
    if (!record) throw new Error('FG dispatch record not found');
    if (String(record.status || '').toUpperCase() !== 'PENDING') throw new Error('Only PENDING dispatch can be started');
    if (hasCol(schema, 'approved_at') && !record.approved_at) throw new Error('Approve dispatch before start');

    const transporterName = String(body.transporter_name || record.transporter_name || '').trim();
    const vehicleNo = String(body.vehicle_no || record.vehicle_no || '').trim();
    const driverName = String(body.driver_name || record.driver_name || '').trim();
    if (!transporterName) throw new Error('Transporter Name is required to start dispatch');
    if (!vehicleNo) throw new Error('Vehicle No is required to start dispatch');
    if (!driverName) throw new Error('Driver Name is required to start dispatch');

    await record.update(
      {
        status: 'IN_TRANSIT',
        ...(hasCol(schema, 'transporter_name') ? { transporter_name: transporterName } : {}),
        ...(hasCol(schema, 'vehicle_no') ? { vehicle_no: vehicleNo } : {}),
        ...(hasCol(schema, 'driver_name') ? { driver_name: driverName } : {}),
        ...(hasCol(schema, 'transit_started_at') ? { transit_started_at: new Date() } : {}),
        ...(hasCol(schema, 'transit_started_by') ? { transit_started_by: userId } : {}),
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();
    const refreshed = await getRefreshedDispatch(id);
    sendResponse(res, 200, true, 'FG dispatch started successfully. Status changed to IN_TRANSIT.', refreshed);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to start FG dispatch', 400);
  }
});

exports.deliver = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const transaction = await db.sequelize.transaction();
  try {
    const schema = await getDispatchSchema();
    const record = await db.FGDispatch.findByPk(id, { transaction });
    if (!record) throw new Error('FG dispatch record not found');
    if (!hasCol(schema, 'delivery_status')) throw new Error('Delivery status is not configured for FG dispatch');
    if (String(record.status || '').toUpperCase() !== 'IN_TRANSIT') throw new Error('Only IN_TRANSIT dispatch can be marked as delivered');
    if (String(record.delivery_status || '').toUpperCase() === 'FULLY_DELIVERED') throw new Error('Dispatch is already marked as delivered');

    await record.update(
      {
        status: 'COMPLETED',
        delivery_status: 'FULLY_DELIVERED',
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();
    const refreshed = await getRefreshedDispatch(id);
    sendResponse(res, 200, true, 'FG dispatch marked as delivered successfully', refreshed);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to mark FG dispatch as delivered', 400);
  }
});

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

  if (!['IN_TRANSIT', 'COMPLETED', 'CANCELLED'].includes(status)) {
    return sendError(res, 'Only IN_TRANSIT, COMPLETED or CANCELLED status is allowed', 400);
  }

  const transaction = await db.sequelize.transaction();
  try {
    const record = await db.FGDispatch.findByPk(id, { transaction });
    if (!record) throw new Error('FG dispatch record not found');
    if (String(record.status || '').toUpperCase() !== 'PENDING') throw new Error('Only PENDING dispatch can be updated');

    if (status === 'CANCELLED') {
      await record.update({ status: 'CANCELLED', updated_by: userId }, { transaction });
    } else {
      // Stock is already consumed at creation; status updates should not consume again.
      await record.update({ status, updated_by: userId }, { transaction });
    }

    await transaction.commit();
    const refreshed = await getRefreshedDispatch(id);
    sendResponse(res, 200, true, 'FG dispatch status updated successfully', refreshed);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to update FG dispatch status', 400);
  }
});
