/**
 * Customer PO process flow — end-to-end, parent-driven tracking.
 *
 * Stages derive state/quantities from linked downstream documents:
 * Production -> Stores/MRP -> Purchase -> Inward/GRN -> QC (inward) ->
 * Stores issues -> Production receipts -> Work/Route -> In-process QC -> Final QC ->
 * Packing -> FG Inward -> Dispatch -> Sales Invoice.
 */
const { Op } = require('sequelize');
const db = require('../models');

const EPS = 0.0005;
const round3 = (v) => Number((parseFloat(v || 0) || 0).toFixed(3));

const STEP_STATUS = {
  NOT_STARTED: 'NOT_STARTED',
  IN_PROGRESS: 'IN_PROGRESS',
  COMPLETED: 'COMPLETED',
  ON_HOLD: 'ON_HOLD',
};

const STAGE_KEYS = [
  'CUSTOMER_PO_CREATION',
  'PRODUCTION_ORDER_CREATION',
  'NESTING',
  'MRP_MATERIAL_REQUEST',
  'MATERIAL_AVAILABILITY_CHECK',
  'MATERIAL_PURCHASE',
  'MATERIAL_INWARD_ENTRY',
  'GRN',
  'INWARD_INSPECTION_QC',
  'STOCK_REGISTER_UPDATE',
  'MATERIAL_ISSUE_TO_PRODUCTION',
  'PRODUCTION_MATERIAL_RECEIPT',
  'WORK_ORDER_CREATION',
  'ROUTE_CARD_EXECUTION',
  'IN_PROCESS_QC',
  'FINAL_QC',
  'FINISHED_GOODS_ENTRY',
  'PACKING',
  'DISPATCH',
  'SALES_INVOICE',
];

const STAGE_LABELS = {
  CUSTOMER_PO_CREATION: 'Customer PO Creation',
  PRODUCTION_ORDER_CREATION: 'Production Order Creation',
  MRP_MATERIAL_REQUEST: 'MRP → Material Requests',
  MATERIAL_AVAILABILITY_CHECK: 'Stores Stock Availability',
  MATERIAL_PURCHASE: 'Material Purchase (PO)',
  MATERIAL_INWARD_ENTRY: 'Material Inward Entry',
  GRN: 'GRN (Goods Receipt Note)',
  INWARD_INSPECTION_QC: 'Inward Inspection (QC)',
  STOCK_REGISTER_UPDATE: 'Stock Register Update',
  NESTING: 'Nesting',
  MATERIAL_ISSUE_TO_PRODUCTION: 'Stores → Production Issue',
  PRODUCTION_MATERIAL_RECEIPT: 'Production Material Receive',
  WORK_ORDER_CREATION: 'Work Order Creation',
  ROUTE_CARD_EXECUTION: 'Route Card Execution',
  IN_PROCESS_QC: 'In-Process Inspection (QC)',
  FINAL_QC: 'Final Inspection (QC)',
  FINISHED_GOODS_ENTRY: 'Finished Goods Entry',
  PACKING: 'Packing',
  DISPATCH: 'Dispatch',
  SALES_INVOICE: 'Sales Invoice Generation',
};

function isTerminalStatus(status, terminalStatuses) {
  return terminalStatuses.has(String(status || ''));
}

function safeDate(v) {
  if (!v) return null;
  const d = new Date(v);
  return Number.isNaN(d.getTime()) ? null : d;
}

function uniqueIds(arr) {
  return Array.from(
    new Set(
      (arr || [])
        .map((x) => {
          const n = Number(x);
          return Number.isFinite(n) && n > 0 ? n : null;
        })
        .filter(Boolean)
    )
  );
}

/**
 * @param {number} customerPoId
 * @param {{ companyId?: number, isSuperAdmin?: boolean }} ctx
 */
async function buildProcessFlow(customerPoId, ctx = {}) {
  const cpo = await db.CustomerPO.findByPk(customerPoId, {
    attributes: ['id', 'company_id', 'plant_id', 'po_number', 'status', 'created_at', 'updated_at'],
    include: [
      {
        model: db.CustomerPOItem,
        as: 'items',
        attributes: ['id', 'item_number', 'material_id', 'quantity', 'delivered_quantity', 'description'],
        required: false,
      },
    ],
  });

  if (!cpo) return null;

  if (
    ctx.companyId != null &&
    !ctx.isSuperAdmin &&
    Number(cpo.company_id) !== Number(ctx.companyId)
  ) {
    return { forbidden: true };
  }

  const cpoJson = typeof cpo.toJSON === 'function' ? cpo.toJSON() : cpo;
  const items = (cpoJson.items || []).map((i) => i);
  const orderedQty = items.reduce((s, it) => s + round3(it.quantity), 0);

  // Production orders linked to this Customer PO.
  const poRows = await db.ProductionOrder.findAll({
    where: { customer_po_id: customerPoId },
    attributes: [
      'id',
      'production_order_number',
      'status',
      'is_sent_to_nesting',
      'nesting_number',
      'created_at',
      'updated_at',
      'released_at',
      'confirmed_quantity',
      'scrapped_quantity',
    ],
  });
  const activePOs = (poRows || []).filter((p) => String(p.status || '') !== 'CANCELLED');
  const poIdList = activePOs.map((p) => p.id);

  // If PO isn't released/converted, still return stepper with PO creation + MR/production not started.
  const poActiveCount = poIdList.length;

  // Stage queries.
  const [
    workOrders,
    routeCards,
    materialRequests,
    materialIssues,
    receiptAgg,
    nestingStores,
    nestingConfirmRows,
    productionPackings,
    productionQCList,
    finalInspections,
    inProgressMaterialRequests,
    salesInvoices,
  ] = await Promise.all([
    poIdList.length
      ? db.WorkOrder.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'work_order_number', 'production_order_id', 'status', 'created_at', 'updated_at'],
        })
      : [],
    poIdList.length
      ? db.RouteCard.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'route_card_number', 'work_order_id', 'production_order_id', 'status', 'created_at', 'updated_at'],
        })
      : [],
    poIdList.length
      ? db.MaterialRequest.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: [
            'id',
            'request_number',
            'production_order_id',
            'status',
            'purchase_requisition_id',
            'created_at',
            'updated_at',
            'sent_to_stores_at',
            'reviewed_at',
            'stores_reviewed_issue_at',
            'sent_to_purchase_at',
          ],
        })
      : [],
    poIdList.length
      ? db.MaterialIssue.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'issue_number', 'production_order_id', 'status', 'created_at', 'updated_at', 'posted_at', 'received_at'],
        })
      : [],
    poIdList.length
      ? db.ProductionOrderMaterialReceiptLine.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'production_order_id', 'received_qty', 'created_at'],
        })
      : [],
    poIdList.length
      ? db.NestingStoresRequest.findAll({
          where: {
            [Op.or]: [{ customer_po_id: customerPoId }, { production_order_id: { [Op.in]: poIdList } }],
          },
          attributes: ['id', 'production_order_id', 'customer_po_id', 'created_at'],
        })
      : db.NestingStoresRequest.findAll({
          where: { customer_po_id: customerPoId },
          attributes: ['id', 'production_order_id', 'customer_po_id', 'created_at'],
        }),
    poIdList.length
      ? db.ProductionNestingConfirmation.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'production_order_id', 'created_at'],
        })
      : [],
    poIdList.length
      ? db.ProductionPacking.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: [
            'id',
            'packing_number',
            'production_order_id',
            'route_card_id',
            'packed_qty',
            'actual_start',
            'actual_end',
            'status',
            'created_at',
          ],
        })
      : [],
    poIdList.length
      ? db.ProductionQC.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'production_qc_number', 'production_order_id', 'route_card_id', 'status', 'created_at', 'updated_at', 'moved_to_qc_at', 'qc_at'],
        })
      : [],
    poIdList.length
      ? db.ProductionFinalInspection.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'final_inspection_number', 'production_order_id', 'route_card_id', 'status', 'created_at', 'started_at', 'completed_at', 'accepted_qty', 'rework_qty'],
        })
      : [],
    db.InProgressMaterialRequest.findAll({
      where: { customer_po_id: customerPoId },
      attributes: ['id', 'ipmr_number', 'material_request_number', 'status', 'created_at', 'updated_at'],
    }),
    db.SalesInvoice.findAll({
      where: { customer_po_id: customerPoId },
      attributes: ['id', 'invoice_number', 'status', 'posted_at', 'dispatch_status', 'dispatched_qty', 'created_at', 'updated_at'],
    }),
  ]);

  const woList = (workOrders || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const rcList = (routeCards || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const mrList = (materialRequests || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const miList = (materialIssues || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const rcptList = (receiptAgg || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const nsrList = (nestingStores || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const nestConf = (nestingConfirmRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const packList = (productionPackings || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const qcList = (productionQCList || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const faiList = (finalInspections || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const invList = (salesInvoices || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const ipMrList = (inProgressMaterialRequests || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));

  const mrIds = uniqueIds(mrList.map((m) => m.id));
  const ipmrIds = uniqueIds(ipMrList.map((m) => m.id));
  const mrTerminalStatuses = new Set(['COMPLETED', 'CANCELLED']);
  const ipmrTerminalStatuses = new Set(['COMPLETED', 'CANCELLED']);

  // Material request item-level details (for shortages / availability).
  const [mrItems, ipmrItems] = await Promise.all([
    mrIds.length
      ? db.MaterialRequestItem.findAll({
          where: { request_id: { [Op.in]: mrIds } },
          attributes: ['id', 'request_id', 'required_quantity', 'available_stock', 'shortfall_quantity', 'issued_quantity', 'pending_quantity'],
        })
      : [],
    ipmrIds.length
      ? db.InProgressMaterialRequestItem.findAll({
          where: { in_progress_request_id: { [Op.in]: ipmrIds } },
          attributes: ['id', 'in_progress_request_id', 'required_quantity', 'available_quantity', 'shortage_quantity', 'status'],
        })
      : [],
  ]);

  const mrItemList = (mrItems || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const ipmrItemList = (ipmrItems || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));

  const shortageTotal =
    mrItemList.reduce((s, it) => s + round3(it.shortfall_quantity), 0) +
    ipmrItemList.reduce((s, it) => s + round3(it.shortage_quantity), 0);

  const requiredTotal =
    mrItemList.reduce((s, it) => s + round3(it.required_quantity), 0) +
    ipmrItemList.reduce((s, it) => s + round3(it.required_quantity), 0);

  // Purchase -> inward -> GRN (stores).
  const purchaseRequisitionIds = uniqueIds(mrList.map((m) => m.purchase_requisition_id));
  const purchaseOrderRows = purchaseRequisitionIds.length
    ? await db.PurchaseOrder.findAll({
        where: { pr_id: { [Op.in]: purchaseRequisitionIds } },
        attributes: ['id', 'po_number', 'status', 'posted_at', 'approved_at', 'submitted_at', 'created_at'],
      })
    : [];
  const poPurchaseList = (purchaseOrderRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const purchaseOrderIds = uniqueIds(poPurchaseList.map((p) => p.id));

  const gateEntryRows = purchaseOrderIds.length
    ? await db.GateEntry.findAll({
        where: { po_id: { [Op.in]: purchaseOrderIds } },
        attributes: [
          'id',
          'gate_entry_number',
          'status',
          'receiving_status',
          'destination_type',
          'qc_required',
          'qc_approved',
          'qc_approved_at',
          'qc_rejection_reason',
          'checked_at',
          'created_at',
          'updated_at',
        ],
      })
    : [];
  const gateEntryList = (gateEntryRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const gateEntryIds = uniqueIds(gateEntryList.map((g) => g.id));

  const grnRows = purchaseOrderIds.length
    ? await db.GoodsReceiptNote.findAll({
        where: { po_id: { [Op.in]: purchaseOrderIds } },
        attributes: ['id', 'grn_number', 'status', 'posted_at', 'created_at', 'grn_date'],
      })
    : [];
  const grnList = (grnRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const grnIds = uniqueIds(grnList.map((g) => g.id));

  const inventoryLedgerRows = grnIds.length
    ? await db.InventoryStockLedger.findAll({
        where: { reference_type: 'GRN', reference_id: { [Op.in]: grnIds } },
        attributes: ['id', 'reference_id', 'quantity_in', 'transaction_type', 'transaction_date'],
      })
    : [];

  const ledgerList = (inventoryLedgerRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));

  // Production-side additional quantities + rejected tracking.
  const rcIds = uniqueIds(rcList.map((r) => r.id));

  const [routeCardOperations, productionFgInwardRows, fgDispatchRows] = await Promise.all([
    rcIds.length
      ? db.RouteCardOperation.findAll({
          where: { route_card_id: { [Op.in]: rcIds } },
          attributes: ['id', 'route_card_id', 'status', 'rejected_qty', 'rework_qty', 'fr_rejected_qty', 'mr_rejected_qty'],
        })
      : [],
    poIdList.length
      ? db.ProductionFGInward.findAll({
          where: { production_order_id: { [Op.in]: poIdList } },
          attributes: ['id', 'fg_inward_number', 'production_order_id', 'route_card_id', 'status', 'packed_qty', 'accepted_qty', 'inward_entry_qty', 'balance_inward_qty', 'inward_entry_date', 'created_at'],
        })
      : [],
    invList.length
      ? db.FGDispatch.findAll({
          where: { invoice_id: { [Op.in]: invList.map((i) => i.id) } },
          attributes: ['id', 'dispatch_no', 'dispatch_date', 'status', 'delivery_status', 'dispatch_qty', 'balance_after_dispatch', 'invoice_id'],
        })
      : [],
  ]);

  const routeCardOperationList = (routeCardOperations || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const fgInwardList = (productionFgInwardRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));
  const fgDispatchList = (fgDispatchRows || []).map((x) => (typeof x.toJSON === 'function' ? x.toJSON() : x));

  // Quantities (Customer PO level).
  const producedQty = fgInwardList.reduce((s, x) => s + round3(x.packed_qty), 0);
  const rejectedQty = routeCardOperationList.reduce((s, x) => s + round3(x.rejected_qty), 0);
  // Dispatched quantity can be tracked on invoice OR on FG Dispatch depending on workflow configuration.
  const dispatchedFromInvoice = invList.reduce((s, x) => s + round3(x.dispatched_qty), 0);
  const dispatchedFromFgDispatch = fgDispatchList.reduce((s, x) => s + round3(x.dispatch_qty), 0);
  const dispatchedQty = Math.max(dispatchedFromInvoice, dispatchedFromFgDispatch);
  const balanceQty = Math.max(0, round3(orderedQty - dispatchedQty));

  // ---------- Stage status derivation ----------

  // 1) Customer PO creation
  let customerPoStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'Customer PO created.' };
  if (['DRAFT'].includes(String(cpoJson.status || ''))) {
    customerPoStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'Customer PO is in Draft.' };
  } else if (['SUBMITTED', 'APPROVED', 'RELEASED'].includes(String(cpoJson.status || ''))) {
    customerPoStage = { status: STEP_STATUS.IN_PROGRESS, detail: `Customer PO status: ${cpoJson.status}.` };
  } else if (['PARTIALLY_DELIVERED', 'DELIVERED', 'CLOSED', 'REJECTED'].includes(String(cpoJson.status || ''))) {
    customerPoStage = { status: STEP_STATUS.COMPLETED, detail: `Customer PO reached: ${cpoJson.status}.` };
  }

  // 2) Production Order Creation
  let prodOrderStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No Production Orders linked.' };
  if (poActiveCount > 0) {
    const allConfirmedOrClosed = activePOs.every((p) => ['CONFIRMED', 'CLOSED'].includes(String(p.status || '')));
    const anyLive = activePOs.some((p) =>
      ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED'].includes(String(p.status || ''))
    );
    prodOrderStage = allConfirmedOrClosed
      ? { status: STEP_STATUS.COMPLETED, detail: `${poActiveCount} production order(s) confirmed/closed.` }
      : anyLive
        ? { status: STEP_STATUS.IN_PROGRESS, detail: `${poActiveCount} production order(s) are executing.` }
        : { status: STEP_STATUS.IN_PROGRESS, detail: `${poActiveCount} production order(s) created.` };
  }

  // 3) MRP → Material Requests
  const mrAnyOpen = mrList.some((m) => !mrTerminalStatuses.has(String(m.status || '')));
  const ipmrAnyOpen = ipMrList.some((m) => !ipmrTerminalStatuses.has(String(m.status || '')));
  let mrStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No Material Requests found.' };
  if (mrList.length > 0 || ipMrList.length > 0) {
    const mrAllTerminal = mrList.length > 0 && mrList.every((m) => mrTerminalStatuses.has(String(m.status || '')));
    const ipmrAllTerminal = ipMrList.length === 0 || ipMrList.every((m) => ipmrTerminalStatuses.has(String(m.status || '')));
    if (mrAllTerminal && ipmrAllTerminal) {
      mrStage = { status: STEP_STATUS.COMPLETED, detail: 'Material Requests completed/cancelled.' };
    } else if (mrAnyOpen || ipmrAnyOpen) {
      mrStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'MRP/Material Requests in progress.' };
    } else {
      mrStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'Material Requests exist (processing).' };
    }
  }

  // 4) Material Availability Check
  const shortageNeeded = shortageTotal > EPS;
  let availabilityStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Stores availability not evaluated.' };
  if (mrStage.status !== STEP_STATUS.NOT_STARTED || mrList.length > 0 || ipMrList.length > 0) {
    if (!shortageNeeded) {
      availabilityStage = { status: STEP_STATUS.COMPLETED, detail: 'Stores ATP available. Purchase not required.' };
    } else {
      const hasPurchaseFlow = poPurchaseList.length > 0;
      availabilityStage = hasPurchaseFlow
        ? { status: STEP_STATUS.IN_PROGRESS, detail: 'Stock shortage detected. Purchase workflow activated.' }
        : { status: STEP_STATUS.ON_HOLD, detail: 'Stock shortage detected. Material purchase is pending.' };
    }
  }

  // 5) Material Purchase
  let purchaseStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Material Purchase not started.' };
  if (!shortageNeeded) {
    purchaseStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Not required: no shortage detected (no purchase documents).' };
  } else if (poPurchaseList.length === 0) {
    purchaseStage = { status: STEP_STATUS.ON_HOLD, detail: 'Material shortage exists. Purchase PO creation pending.' };
  } else {
    const terminal = new Set(['CLOSED', 'CANCELLED']);
    const allDone = poPurchaseList.length > 0 && poPurchaseList.every((p) => terminal.has(String(p.status || '')));
    purchaseStage = allDone
      ? { status: STEP_STATUS.COMPLETED, detail: 'Purchase Orders closed/cancelled.' }
      : { status: STEP_STATUS.IN_PROGRESS, detail: 'Purchase Orders in workflow (submitted/approved/posted).' };
  }

  // 6) Material Inward Entry
  let inwardEntryStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No Gate Entry found.' };
  if (!shortageNeeded && gateEntryList.length === 0) {
    inwardEntryStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Not required: no shortage detected (no inward documents).' };
  } else if (gateEntryList.length > 0) {
    if (gateEntryList.some((g) => String(g.status || '') === 'QC_REJECTED')) {
      inwardEntryStage = { status: STEP_STATUS.ON_HOLD, detail: 'Inward QC rejected at Gate Entry.' };
    } else {
      const allReadyOrReceived = gateEntryList.every((g) =>
        ['GRN_READY', 'GRN_CREATED'].includes(String(g.status || '')) ||
        ['PARTIAL_RECEIVED', 'FULLY_RECEIVED'].includes(String(g.receiving_status || ''))
      );
      inwardEntryStage = allReadyOrReceived
        ? { status: STEP_STATUS.COMPLETED, detail: 'Gate Entry checked; inward moved toward stores/GRN.' }
        : { status: STEP_STATUS.IN_PROGRESS, detail: 'Gate Entry created/checked; inward workflow running.' };
    }
  }

  // 7) GRN
  let grnStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No GRN created.' };
  if (!shortageNeeded && grnList.length === 0) {
    grnStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Not required: no shortage detected (no GRN documents).' };
  } else if (grnList.length > 0) {
    const anyPosted = grnList.some((g) => String(g.status || '') === 'POSTED');
    const allTerminal = grnList.every((g) => ['POSTED', 'CANCELLED'].includes(String(g.status || '')));
    grnStage = allTerminal && anyPosted
      ? { status: STEP_STATUS.COMPLETED, detail: `${grnList.length} GRN(s) posted.` }
      : { status: STEP_STATUS.IN_PROGRESS, detail: 'GRN in draft/posted/cancelled states.' };
  }

  // 8) Inward Inspection QC
  let inwardQcStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Inward QC not started.' };
  if (!shortageNeeded && gateEntryList.length === 0) {
    inwardQcStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Not required: no shortage detected (no inward QC documents).' };
  } else if (gateEntryList.length > 0) {
    if (gateEntryList.some((g) => String(g.status || '') === 'QC_REJECTED')) {
      inwardQcStage = { status: STEP_STATUS.ON_HOLD, detail: 'Inward QC rejected; needs resolution/rework.' };
    } else if (gateEntryList.every((g) => String(g.status || '') === 'QC_APPROVED' || Boolean(g.qc_approved))) {
      inwardQcStage = { status: STEP_STATUS.COMPLETED, detail: 'All Gate Entries QC approved.' };
    } else {
      inwardQcStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'Inward QC pending/processing.' };
    }
  }

  // 9) Stock Register Update
  let stockUpdateStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Stock ledger not updated yet.' };
  if (!shortageNeeded && grnList.length === 0) {
    stockUpdateStage = { status: STEP_STATUS.NOT_STARTED, detail: 'Not required: no shortage detected (no stock update documents).' };
  } else if (grnList.length > 0) {
    const anyLedger = ledgerList.length > 0;
    stockUpdateStage = anyLedger
      ? { status: STEP_STATUS.COMPLETED, detail: 'Inventory Stock Ledger updated from GRN(s).' }
      : { status: STEP_STATUS.IN_PROGRESS, detail: 'GRN exists, waiting for ledger update.' };
  }

  // 10) Nesting
  let nestingStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No nesting activity.' };
  if (poActiveCount > 0) {
    const hasSignal = (p) =>
      Boolean(p.is_sent_to_nesting) ||
      nsrList.some((n) => n.production_order_id === p.id) ||
      nestConf.some((c) => c.production_order_id === p.id) ||
      (p.nesting_number && String(p.nesting_number).trim() !== '');
    const anySignal = activePOs.some(hasSignal);
    const nestingDone = (p) =>
      (p.nesting_number && String(p.nesting_number).trim() !== '') ||
      nestConf.some((c) => c.production_order_id === p.id) ||
      ['CONFIRMED', 'CLOSED'].includes(String(p.status || ''));
    const allNestingDone = activePOs.length > 0 && activePOs.every(nestingDone);
    nestingStage = !anySignal
      ? { status: STEP_STATUS.NOT_STARTED, detail: 'No nesting signals recorded.' }
      : allNestingDone
        ? { status: STEP_STATUS.COMPLETED, detail: 'Nesting completed for all linked production orders.' }
        : { status: STEP_STATUS.IN_PROGRESS, detail: 'Nesting started; waiting for completion.' };
  }

  // 11) Stores issue to production
  let materialIssueStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No Material Issues.' };
  if (miList.length > 0) {
    const terminal = new Set(['RECEIVED', 'POSTED', 'CANCELLED']);
    const allDone = miList.every((m) => terminal.has(String(m.status || ''))) && miList.some((m) => ['RECEIVED', 'POSTED'].includes(String(m.status || '')));
    materialIssueStage = allDone
      ? { status: STEP_STATUS.COMPLETED, detail: 'Material Issues received/posted into production.' }
      : { status: STEP_STATUS.IN_PROGRESS, detail: 'Material issue workflow in progress.' };
  }

  // 12) Production material receipt
  let pmrStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No production receipt lines.' };
  if (rcptList.length > 0) {
    const qty = rcptList.reduce((s, l) => s + round3(l.received_qty), 0);
    pmrStage = qty > EPS
      ? { status: STEP_STATUS.COMPLETED, detail: `Received qty recorded: ${qty.toFixed(3)}.` }
      : { status: STEP_STATUS.IN_PROGRESS, detail: 'Production receipt lines created; awaiting received qty.' };
  } else if (miList.some((m) => ['RECEIVED', 'POSTED'].includes(String(m.status || '')))) {
    pmrStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'Materials received; record receipt lines if not created yet.' };
  }

  // 13) Work order creation/execution
  let woStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No work orders.' };
  if (woList.length > 0) {
    const allDone = woList.every((w) => ['COMPLETED', 'CLOSED'].includes(String(w.status || '')));
    const anyLive = woList.some((w) => ['RELEASED', 'IN_PROGRESS'].includes(String(w.status || '')) || String(w.status || '') === 'PLANNED');
    woStage = allDone
      ? { status: STEP_STATUS.COMPLETED, detail: `${woList.length} work order(s) completed/closed.` }
      : anyLive
        ? { status: STEP_STATUS.IN_PROGRESS, detail: 'Work order(s) are planned/released/in progress.' }
        : { status: STEP_STATUS.IN_PROGRESS, detail: `${woList.length} work order(s) exist.` };
  }

  // 14) Route card execution
  let rcStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No route cards.' };
  if (rcList.length > 0) {
    const allClosed = rcList.every((r) => String(r.status || '') === 'CLOSED');
    const anyOpen = rcList.some((r) => String(r.status || '') === 'OPEN' || String(r.status || '').includes('OPEN'));
    rcStage = allClosed
      ? { status: STEP_STATUS.COMPLETED, detail: `${rcList.length} route card(s) closed.` }
      : anyOpen
        ? { status: STEP_STATUS.IN_PROGRESS, detail: `${rcList.length} route card(s) executing.` }
        : { status: STEP_STATUS.IN_PROGRESS, detail: `${rcList.length} route card(s) exist.` };
  }

  // 15) In-process QC
  let inProcessQcStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No in-process QC records.' };
  if (qcList.length > 0) {
    if (qcList.some((q) => String(q.status || '') === 'FAILED')) {
      inProcessQcStage = { status: STEP_STATUS.ON_HOLD, detail: 'One or more in-process QC records failed.' };
    } else if (qcList.every((q) => String(q.status || '') === 'APPROVED')) {
      inProcessQcStage = { status: STEP_STATUS.COMPLETED, detail: 'All in-process QC records approved.' };
    } else {
      inProcessQcStage = { status: STEP_STATUS.IN_PROGRESS, detail: 'In-process QC pending/processing.' };
    }
  }

  // 16) Final QC
  let finalQcStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No final inspections.' };
  if (faiList.length > 0) {
    const allApproved = faiList.every((f) => String(f.status || '') === 'APPROVED');
    finalQcStage = allApproved ? { status: STEP_STATUS.COMPLETED, detail: 'Final inspection approved.' } : { status: STEP_STATUS.IN_PROGRESS, detail: 'Final inspection in progress/pending.' };
  }

  // 17) Finished goods entry (FG inward)
  let fgEntryStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No finished goods entry yet.' };
  if (fgInwardList.length > 0) {
    const allFull = fgInwardList.every((e) => String(e.status || '') === 'FULLY_RECEIVED');
    fgEntryStage = allFull ? { status: STEP_STATUS.COMPLETED, detail: 'Finished goods fully received into FG storage.' } : { status: STEP_STATUS.IN_PROGRESS, detail: 'Finished goods inward partially received.' };
  }

  // 18) Packing
  let packingStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No packing records.' };
  if (packList.length > 0) {
    const totalPacked = packList.reduce((s, p) => s + round3(p.packed_qty), 0);
    const allPackedDone = packList.every((p) => String(p.status || '') === 'COMPLETED');
    packingStage = totalPacked > EPS && allPackedDone
      ? { status: STEP_STATUS.COMPLETED, detail: `Packed qty: ${totalPacked.toFixed(3)}.` }
      : totalPacked > EPS
        ? { status: STEP_STATUS.IN_PROGRESS, detail: `Packing running; packed qty so far: ${totalPacked.toFixed(3)}.` }
        : { status: STEP_STATUS.IN_PROGRESS, detail: 'Packing records created.' };
  }

  // 19) Dispatch
  let dispatchStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No dispatch records.' };
  if (invList.length > 0) {
    const allFully = invList.every((i) => String(i.dispatch_status || '') === 'FULLY_DISPATCHED');
    const any = invList.some((i) => Number(i.dispatched_qty || 0) > EPS);
    dispatchStage = allFully && any ? { status: STEP_STATUS.COMPLETED, detail: 'Dispatch fully completed.' } : { status: STEP_STATUS.IN_PROGRESS, detail: 'Dispatch partially completed/pending.' };
  }

  // 20) Sales invoice generation
  let salesInvoiceStage = { status: STEP_STATUS.NOT_STARTED, detail: 'No sales invoices created.' };
  if (invList.length > 0) {
    const allApprovedAndDispatched = invList.every(
      (i) =>
        String(i.status || '') === 'APPROVED' &&
        i.posted_at &&
        String(i.dispatch_status || '') === 'FULLY_DISPATCHED'
    );
    salesInvoiceStage = allApprovedAndDispatched
      ? { status: STEP_STATUS.COMPLETED, detail: 'Invoices approved/posted and dispatch fully completed.' }
      : { status: STEP_STATUS.IN_PROGRESS, detail: 'Invoices created but not fully posted/closed.' };
  }

  const stageMap = {
    CUSTOMER_PO_CREATION: customerPoStage,
    PRODUCTION_ORDER_CREATION: prodOrderStage,
    MRP_MATERIAL_REQUEST: mrStage,
    MATERIAL_AVAILABILITY_CHECK: availabilityStage,
    MATERIAL_PURCHASE: purchaseStage,
    MATERIAL_INWARD_ENTRY: inwardEntryStage,
    GRN: grnStage,
    INWARD_INSPECTION_QC: inwardQcStage,
    STOCK_REGISTER_UPDATE: stockUpdateStage,
    NESTING: nestingStage,
    MATERIAL_ISSUE_TO_PRODUCTION: materialIssueStage,
    PRODUCTION_MATERIAL_RECEIPT: pmrStage,
    WORK_ORDER_CREATION: woStage,
    ROUTE_CARD_EXECUTION: rcStage,
    IN_PROCESS_QC: inProcessQcStage,
    FINAL_QC: finalQcStage,
    FINISHED_GOODS_ENTRY: fgEntryStage,
    PACKING: packingStage,
    DISPATCH: dispatchStage,
    SALES_INVOICE: salesInvoiceStage,
  };

  // ---------- Strict linear sequencing enforcement ----------
  // Even if multiple downstream modules show activity, the Customer PO timeline
  // must remain disciplined: only the first incomplete stage becomes IN_PROGRESS/ON_HOLD.
  const stageDone = (key) => {
    // Guard: if there is no Production Order linked yet, do NOT allow downstream stages
    // to become "done" via skip logic or unrelated docs (prevents jumping to Invoice).
    if (key !== 'CUSTOMER_PO_CREATION' && poActiveCount === 0) return false;

    switch (key) {
      case 'CUSTOMER_PO_CREATION':
        return Boolean(cpoJson?.id);
      case 'PRODUCTION_ORDER_CREATION':
        return poActiveCount > 0;
      case 'MRP_MATERIAL_REQUEST':
        return mrList.length > 0 || ipMrList.length > 0;
      case 'MATERIAL_AVAILABILITY_CHECK':
        // Availability is considered evaluated once MR/IPMR exists (ATP fields computed in those docs).
        return mrList.length > 0 || ipMrList.length > 0;
      case 'MATERIAL_PURCHASE':
        // Done only when purchase docs exist (do not auto-complete when not required).
        return poPurchaseList.length > 0;
      case 'MATERIAL_INWARD_ENTRY':
        return gateEntryList.length > 0;
      case 'GRN':
        return grnList.length > 0;
      case 'INWARD_INSPECTION_QC':
        return gateEntryList.length > 0;
      case 'STOCK_REGISTER_UPDATE':
        return ledgerList.length > 0;
      case 'NESTING':
        return (
          activePOs.some((p) => Boolean(p.is_sent_to_nesting) || (p.nesting_number && String(p.nesting_number).trim() !== '')) ||
          nsrList.length > 0 ||
          nestConf.length > 0
        );
      case 'MATERIAL_ISSUE_TO_PRODUCTION':
        return miList.length > 0;
      case 'PRODUCTION_MATERIAL_RECEIPT':
        return rcptList.length > 0 || miList.some((m) => ['RECEIVED', 'POSTED'].includes(String(m.status || '')));
      case 'WORK_ORDER_CREATION':
        return woList.length > 0;
      case 'ROUTE_CARD_EXECUTION':
        return rcList.length > 0;
      case 'IN_PROCESS_QC':
        return qcList.length > 0;
      case 'FINAL_QC':
        return faiList.length > 0;
      case 'FINISHED_GOODS_ENTRY':
        return fgInwardList.length > 0;
      case 'PACKING':
        return packList.length > 0;
      case 'DISPATCH':
        return fgDispatchList.length > 0 || invList.some((i) => String(i.dispatch_status || '') !== 'NOT_DISPATCHED');
      case 'SALES_INVOICE':
        return invList.length > 0;
      default:
        return false;
    }
  };

  const stageBlocked = (key) => {
    // Only return ON_HOLD when a real blocking signal exists in the linked transactions.
    if (key !== 'CUSTOMER_PO_CREATION' && poActiveCount === 0) return false;
    switch (key) {
      case 'MATERIAL_AVAILABILITY_CHECK':
      case 'MATERIAL_PURCHASE':
        return Boolean(shortageNeeded && poPurchaseList.length === 0);
      case 'MATERIAL_INWARD_ENTRY':
      case 'INWARD_INSPECTION_QC':
        return gateEntryList.some((g) => String(g.status || '') === 'QC_REJECTED');
      case 'IN_PROCESS_QC':
        return qcList.some((q) => String(q.status || '') === 'FAILED');
      default:
        return false;
    }
  };

  const stageStarted = (key) => {
    // A stage is "started" only when there is evidence of activity/records.
    // This prevents auto-marking the next stage as IN_PROGRESS when nothing exists yet.
    if (key !== 'CUSTOMER_PO_CREATION' && poActiveCount === 0) return false;
    switch (key) {
      case 'CUSTOMER_PO_CREATION':
        return true;
      case 'PRODUCTION_ORDER_CREATION':
        return poActiveCount > 0;
      case 'NESTING':
        return (
          activePOs.some((p) => Boolean(p.is_sent_to_nesting) || (p.nesting_number && String(p.nesting_number).trim() !== '')) ||
          nsrList.length > 0 ||
          nestConf.length > 0
        );
      case 'MRP_MATERIAL_REQUEST':
      case 'MATERIAL_AVAILABILITY_CHECK':
        return mrList.length > 0 || ipMrList.length > 0;
      case 'MATERIAL_PURCHASE':
      case 'MATERIAL_INWARD_ENTRY':
      case 'GRN':
      case 'INWARD_INSPECTION_QC':
      case 'STOCK_REGISTER_UPDATE':
        // Purchase / inward stages are "started" only if shortage path is actually used.
        return Boolean(poPurchaseList.length > 0 || gateEntryList.length > 0 || grnList.length > 0 || ledgerList.length > 0);
      case 'MATERIAL_ISSUE_TO_PRODUCTION':
        return miList.length > 0;
      case 'PRODUCTION_MATERIAL_RECEIPT':
        return rcptList.length > 0 || miList.some((m) => ['RECEIVED', 'POSTED', 'IN_TRANSIT'].includes(String(m.status || '')));
      case 'WORK_ORDER_CREATION':
        return woList.length > 0;
      case 'ROUTE_CARD_EXECUTION':
        return rcList.length > 0;
      case 'IN_PROCESS_QC':
        return qcList.length > 0;
      case 'FINAL_QC':
        return faiList.length > 0;
      case 'FINISHED_GOODS_ENTRY':
        return fgInwardList.length > 0;
      case 'PACKING':
        return packList.length > 0;
      case 'DISPATCH':
        return fgDispatchList.length > 0;
      case 'SALES_INVOICE':
        return invList.length > 0;
      default:
        return false;
    }
  };

  const stageEval = STAGE_KEYS.map((key) => ({
    key,
    done: stageDone(key),
    blocked: stageBlocked(key),
    detail: stageMap[key]?.detail || '',
  }));

  const firstIncompleteIndex = stageEval.findIndex((s) => !s.done);
  const allCompleted = firstIncompleteIndex === -1;

  // Pick current stage only if it has started activity (or is blocked).
  // If nothing has started yet beyond completed stages, keep everything as NOT_STARTED.
  let currentIndex = allCompleted ? STAGE_KEYS.length : firstIncompleteIndex;
  if (!allCompleted) {
    const startedIdx = stageEval.findIndex((s) => !s.done && (stageStarted(s.key) || s.blocked));
    currentIndex = startedIdx >= 0 ? startedIdx : -1;
  }

  const sequentialStageMap = {};
  STAGE_KEYS.forEach((key, idx) => {
    if (allCompleted) {
      sequentialStageMap[key] = { ...(stageMap[key] || {}), status: STEP_STATUS.COMPLETED };
      return;
    }

    if (currentIndex === -1) {
      // No active stage yet — only mark true "done" as completed, keep rest pending.
      const done = stageEval[idx]?.done;
      const blocked = stageEval[idx]?.blocked;
      sequentialStageMap[key] = {
        ...(stageMap[key] || {}),
        status: done ? STEP_STATUS.COMPLETED : blocked ? STEP_STATUS.ON_HOLD : STEP_STATUS.NOT_STARTED,
      };
      return;
    }

    if (idx < currentIndex) {
      sequentialStageMap[key] = { ...(stageMap[key] || {}), status: STEP_STATUS.COMPLETED };
      return;
    }

    if (idx === currentIndex) {
      const ev = stageEval[idx];
      sequentialStageMap[key] = { ...(stageMap[key] || {}), status: ev.blocked ? STEP_STATUS.ON_HOLD : STEP_STATUS.IN_PROGRESS };
      return;
    }

    sequentialStageMap[key] = { ...(stageMap[key] || {}), status: STEP_STATUS.NOT_STARTED };
  });

  const progressPercent = Math.round(
    (STAGE_KEYS.filter((k) => sequentialStageMap[k]?.status === STEP_STATUS.COMPLETED).length / STAGE_KEYS.length) * 100
  );

  // ---------- Pending actions (UI copy requirements) ----------
  const pending_actions = [];
  const currentStageKey =
    currentIndex >= 0 && currentIndex < STAGE_KEYS.length ? STAGE_KEYS[currentIndex] : null;

  if (currentStageKey === 'MRP_MATERIAL_REQUEST') pending_actions.push('MRP Pending');
  if (['MATERIAL_AVAILABILITY_CHECK', 'MATERIAL_PURCHASE'].includes(currentStageKey)) pending_actions.push('Material Shortage');
  if (['INWARD_INSPECTION_QC', 'IN_PROCESS_QC', 'FINAL_QC'].includes(currentStageKey)) pending_actions.push('QC Pending');
  if (['DISPATCH', 'SALES_INVOICE'].includes(currentStageKey)) pending_actions.push('Dispatch Pending');

  if (balanceQty > EPS) pending_actions.push(`Balance Quantity: ${balanceQty.toFixed(3)}`);

  // ---------- Stage links ----------
  const links = {
    CUSTOMER_PO_CREATION: [
      { label: `Customer PO ${cpoJson.po_number}`, path: `/customer/po/${cpoJson.id}/view`, refType: 'customer_po', refId: cpoJson.id },
    ],
    PRODUCTION_ORDER_CREATION: poActiveCount
      ? activePOs.map((p) => ({
          label: `Production Order ${p.production_order_number}`,
          path: `/production/production-order/${p.id}/view`,
          refType: 'production_order',
          refId: p.id,
        }))
      : [],
    MRP_MATERIAL_REQUEST: [
      ...mrList.map((m) => ({
        label: `Material Request ${m.request_number}`,
        path: `/stores/materialrequest/${m.id}/view`,
        refType: 'material_request',
        refId: m.id,
      })),
      ...ipMrList.map((m) => ({
        label: `IPMR ${m.ipmr_number || m.id}`,
        path: `/production/inprogress-material-request/${m.id}/view`,
        refType: 'in_progress_material_request',
        refId: m.id,
      })),
    ],
    MATERIAL_AVAILABILITY_CHECK: [
      ...mrList.map((m) => ({
        label: `Material Request ${m.request_number}`,
        path: `/stores/materialrequest/${m.id}/view`,
        refType: 'material_request',
        refId: m.id,
      })),
    ],
    MATERIAL_PURCHASE: poPurchaseList.map((p) => ({
      label: `Purchase Order ${p.po_number}`,
      path: `/purchase/purchaseorder/${p.id}/view`,
      refType: 'purchase_order',
      refId: p.id,
    })),
    MATERIAL_INWARD_ENTRY: gateEntryList.map((g) => ({
      label: `Gate Entry ${g.gate_entry_number}`,
      path: `/purchase/gateentry/${g.id}/view`,
      refType: 'gate_entry',
      refId: g.id,
    })),
    GRN: grnList.map((g) => ({
      label: `GRN ${g.grn_number}`,
      path: `/purchase/goodsreceiptnote/${g.id}/view`,
      refType: 'grn',
      refId: g.id,
    })),
    INWARD_INSPECTION_QC: gateEntryList.map((g) => ({
      label: `Inward QC ${g.gate_entry_number}`,
      path: `/quality-control/inward-inspection/${g.id}`,
      refType: 'inward_qc',
      refId: g.id,
    })),
    STOCK_REGISTER_UPDATE: [
      { label: 'Stock Register', path: '/inventory/stockregister', refType: 'stock_register', refId: null },
    ],
    NESTING: poActiveCount
      ? uniqueIds(activePOs.map((p) => p.id)).map((pid) => ({
          label: `Nesting (PO #${activePOs.find((x) => x.id === pid)?.production_order_number || pid})`,
          path: `/production/nesting/${pid}/view`,
          refType: 'nesting',
          refId: pid,
        }))
      : [],
    MATERIAL_ISSUE_TO_PRODUCTION: miList.map((m) => ({
      label: `Material Issue ${m.issue_number}`,
      path: `/production/materialissue/${m.id}/view`,
      refType: 'material_issue',
      refId: m.id,
    })),
    PRODUCTION_MATERIAL_RECEIPT: poActiveCount
      ? uniqueIds(poIdList).map((pid) => ({
          label: `Material Receive (PO #${activePOs.find((x) => x.id === pid)?.production_order_number || pid})`,
          path: `/production/materialreceive?production_order_id=${pid}`,
          refType: 'production_material_receipt',
          refId: pid,
        }))
      : [],
    WORK_ORDER_CREATION: woList.map((w) => ({
      label: `Work Order ${w.work_order_number}`,
      path: `/production/workorder/${w.id}/view`,
      refType: 'work_order',
      refId: w.id,
    })),
    ROUTE_CARD_EXECUTION: rcList.map((r) => ({
      label: `Route Card ${r.route_card_number}`,
      path: `/production/routecard/${r.id}/view`,
      refType: 'route_card',
      refId: r.id,
    })),
    IN_PROCESS_QC: qcList.map((q) => ({
      label: q.production_qc_number ? `QC ${q.production_qc_number}` : `QC #${q.id}`,
      path: `/production/quality-checking/production-qc/${q.id}/view`,
      refType: 'production_qc',
      refId: q.id,
    })),
    FINAL_QC: faiList.map((f) => ({
      label: f.final_inspection_number ? `FAI ${f.final_inspection_number}` : `FAI #${f.id}`,
      path: `/production/quality-checking/final-inspection/${f.id}/view`,
      refType: 'final_inspection',
      refId: f.id,
    })),
    FINISHED_GOODS_ENTRY: [
      { label: 'FG Inward Entry', path: '/stores/fg-inward-entry', refType: 'fg_inward_entry', refId: null },
    ],
    PACKING: packList.map((p) => ({
      label: `Packing ${p.packing_number}`,
      path: `/production/packing/${p.id}/view`,
      refType: 'production_packing',
      refId: p.id,
    })),
    DISPATCH: fgDispatchList.length
      ? fgDispatchList.map((d) => ({
          label: `FG Dispatch ${d.dispatch_no}`,
          path: `/stores/fg-dispatch/${d.id}/view`,
          refType: 'fg_dispatch',
          refId: d.id,
        }))
      : invList.map((i) => ({
          label: `Invoice ${i.invoice_number}`,
          path: `/sales/invoice/${i.id}/view`,
          refType: 'sales_invoice',
          refId: i.id,
        })),
    SALES_INVOICE: invList.map((i) => ({
      label: `Invoice ${i.invoice_number}`,
      path: `/sales/invoice/${i.id}/view`,
      refType: 'sales_invoice',
      refId: i.id,
    })),
  };

  // ---------- Audit trail ----------
  const trail = [];
  const pushTrail = (at, label, refType, refId, path) => {
    const d = safeDate(at);
    if (!d) return;
    trail.push({ at: d.toISOString(), label, refType: refType || null, refId: refId || null, path: path || null });
  };

  pushTrail(cpoJson.created_at, `Customer PO ${cpoJson.po_number} created`, 'customer_po', cpoJson.id, `/customer/po/${cpoJson.id}/view`);

  for (const p of activePOs) {
    pushTrail(p.created_at, `Production Order ${p.production_order_number} created`, 'production_order', p.id, `/production/production-order/${p.id}/view`);
    if (p.released_at) pushTrail(p.released_at, `Production Order ${p.production_order_number} released`, 'production_order', p.id, `/production/production-order/${p.id}/view`);
  }

  for (const m of mrList) {
    pushTrail(m.created_at, `Material Request ${m.request_number} created`, 'material_request', m.id, `/stores/materialrequest/${m.id}/view`);
    if (m.sent_to_stores_at) pushTrail(m.sent_to_stores_at, `MR ${m.request_number} sent to stores`, 'material_request', m.id, `/stores/materialrequest/${m.id}/view`);
    if (m.stores_reviewed_issue_at) pushTrail(m.stores_reviewed_issue_at, `Stores ATP refreshed for MR ${m.request_number}`, 'material_request', m.id, `/stores/materialrequest/${m.id}/view`);
    if (m.sent_to_purchase_at) pushTrail(m.sent_to_purchase_at, `MR ${m.request_number} sent to purchase`, 'material_request', m.id, `/stores/materialrequest/${m.id}/view`);
  }
  for (const ip of ipMrList) {
    pushTrail(ip.created_at, `In-Progress Material Request ${ip.ipmr_number || ip.id} created`, 'in_progress_material_request', ip.id, `/production/inprogress-material-request/${ip.id}/view`);
  }

  for (const p of poPurchaseList) {
    pushTrail(p.created_at, `Purchase Order ${p.po_number} created`, 'purchase_order', p.id, `/purchase/purchaseorder/${p.id}/view`);
    if (p.posted_at) pushTrail(p.posted_at, `Purchase Order ${p.po_number} posted`, 'purchase_order', p.id, `/purchase/purchaseorder/${p.id}/view`);
  }

  for (const g of gateEntryList) {
    pushTrail(g.created_at, `Gate Entry ${g.gate_entry_number} created`, 'gate_entry', g.id, `/purchase/gateentry/${g.id}/view`);
    if (g.checked_at) pushTrail(g.checked_at, `Gate Entry ${g.gate_entry_number} checked`, 'gate_entry', g.id, `/purchase/gateentry/${g.id}/view`);
    if (g.qc_approved_at) pushTrail(g.qc_approved_at, `Inward QC approved for ${g.gate_entry_number}`, 'inward_qc', g.id, `/quality-control/inward-inspection/${g.id}`);
  }

  for (const g of grnList) {
    if (g.posted_at) pushTrail(g.posted_at, `GRN ${g.grn_number} posted`, 'grn', g.id, `/purchase/goodsreceiptnote/${g.id}/view`);
  }

  for (const mi of miList) {
    if (mi.posted_at) pushTrail(mi.posted_at, `Material Issue ${mi.issue_number} posted`, 'material_issue', mi.id, `/production/materialissue/${mi.id}/view`);
    if (mi.received_at) pushTrail(mi.received_at, `Material Issue ${mi.issue_number} received in production`, 'material_issue', mi.id, `/production/materialissue/${mi.id}/view`);
  }

  for (const w of woList) {
    pushTrail(w.created_at, `Work Order ${w.work_order_number} created`, 'work_order', w.id, `/production/workorder/${w.id}/view`);
  }
  for (const r of rcList) {
    pushTrail(r.created_at, `Route Card ${r.route_card_number} created`, 'route_card', r.id, `/production/routecard/${r.id}/view`);
  }
  for (const q of qcList) {
    if (q.moved_to_qc_at) pushTrail(q.moved_to_qc_at, `In-process QC moved: ${q.production_qc_number || q.id}`, 'production_qc', q.id, `/production/quality-checking/production-qc/${q.id}/view`);
    if (q.qc_at) pushTrail(q.qc_at, `In-process QC completed: ${q.production_qc_number || q.id}`, 'production_qc', q.id, `/production/quality-checking/production-qc/${q.id}/view`);
  }
  for (const f of faiList) {
    if (f.started_at) pushTrail(f.started_at, `Final Inspection started: ${f.final_inspection_number || f.id}`, 'final_inspection', f.id, `/production/quality-checking/final-inspection/${f.id}/view`);
    if (f.completed_at) pushTrail(f.completed_at, `Final Inspection completed: ${f.final_inspection_number || f.id}`, 'final_inspection', f.id, `/production/quality-checking/final-inspection/${f.id}/view`);
  }

  for (const fg of fgInwardList) {
    if (fg.inward_entry_date) pushTrail(fg.inward_entry_date, `FG Inward ${fg.fg_inward_number} (${fg.status})`, 'fg_inward_entry', fg.id, '/stores/fg-inward-entry');
  }

  for (const p of packList) {
    if (p.actual_start) pushTrail(p.actual_start, `Packing started: ${p.packing_number}`, 'production_packing', p.id, `/production/packing/${p.id}/view`);
    if (p.actual_end) pushTrail(p.actual_end, `Packing completed: ${p.packing_number}`, 'production_packing', p.id, `/production/packing/${p.id}/view`);
  }

  for (const d of fgDispatchList) {
    pushTrail(d.dispatch_date, `FG Dispatch ${d.dispatch_no} (${d.delivery_status})`, 'fg_dispatch', d.id, `/stores/fg-dispatch/${d.id}/view`);
  }

  for (const i of invList) {
    pushTrail(i.posted_at || i.created_at, `Invoice ${i.invoice_number} (${i.status})`, 'sales_invoice', i.id, `/sales/invoice/${i.id}/view`);
  }

  trail.sort((a, b) => new Date(a.at).getTime() - new Date(b.at).getTime());

  // ---------- Stage metadata (timestamps / skipped) ----------
  const maxDate = (...vals) => {
    let best = null;
    for (const v of vals.flat()) {
      const d = safeDate(v);
      if (!d) continue;
      if (!best || d > best) best = d;
    }
    return best;
  };

  const stageMeta = {};
  const isSkippedDetail = (detail) => String(detail || '').trim().toLowerCase().startsWith('skipped:');

  const stageLastAt = (key) => {
    switch (key) {
      case 'CUSTOMER_PO_CREATION':
        return maxDate(cpoJson.created_at, cpoJson.updated_at);
      case 'PRODUCTION_ORDER_CREATION':
        return maxDate(activePOs.map((p) => p.released_at), activePOs.map((p) => p.created_at), activePOs.map((p) => p.updated_at));
      case 'MRP_MATERIAL_REQUEST':
        return maxDate(
          mrList.map((m) => m.sent_to_purchase_at),
          mrList.map((m) => m.stores_reviewed_issue_at),
          mrList.map((m) => m.reviewed_at),
          mrList.map((m) => m.sent_to_stores_at),
          mrList.map((m) => m.created_at),
          ipMrList.map((m) => m.created_at)
        );
      case 'MATERIAL_AVAILABILITY_CHECK':
        return maxDate(mrList.map((m) => m.stores_reviewed_issue_at), mrList.map((m) => m.reviewed_at), mrList.map((m) => m.created_at));
      case 'MATERIAL_PURCHASE':
        return maxDate(poPurchaseList.map((p) => p.posted_at), poPurchaseList.map((p) => p.approved_at), poPurchaseList.map((p) => p.submitted_at), poPurchaseList.map((p) => p.created_at));
      case 'MATERIAL_INWARD_ENTRY':
        return maxDate(gateEntryList.map((g) => g.qc_approved_at), gateEntryList.map((g) => g.checked_at), gateEntryList.map((g) => g.created_at));
      case 'GRN':
        return maxDate(grnList.map((g) => g.posted_at), grnList.map((g) => g.created_at), grnList.map((g) => g.grn_date));
      case 'INWARD_INSPECTION_QC':
        return maxDate(gateEntryList.map((g) => g.qc_approved_at), gateEntryList.map((g) => g.checked_at), gateEntryList.map((g) => g.created_at));
      case 'STOCK_REGISTER_UPDATE':
        return maxDate(ledgerList.map((l) => l.transaction_date), grnList.map((g) => g.posted_at));
      case 'NESTING':
        return maxDate(
          nestConf.map((n) => n.created_at),
          nsrList.map((n) => n.created_at),
          activePOs.map((p) => p.sent_to_nesting_at),
          activePOs.map((p) => p.updated_at)
        );
      case 'MATERIAL_ISSUE_TO_PRODUCTION':
        return maxDate(miList.map((m) => m.received_at), miList.map((m) => m.posted_at), miList.map((m) => m.created_at), miList.map((m) => m.updated_at));
      case 'PRODUCTION_MATERIAL_RECEIPT':
        return maxDate(rcptList.map((r) => r.created_at), miList.map((m) => m.received_at));
      case 'WORK_ORDER_CREATION':
        return maxDate(woList.map((w) => w.updated_at), woList.map((w) => w.created_at));
      case 'ROUTE_CARD_EXECUTION':
        return maxDate(rcList.map((r) => r.updated_at), rcList.map((r) => r.created_at));
      case 'IN_PROCESS_QC':
        return maxDate(qcList.map((q) => q.qc_at), qcList.map((q) => q.moved_to_qc_at), qcList.map((q) => q.updated_at), qcList.map((q) => q.created_at));
      case 'FINAL_QC':
        return maxDate(faiList.map((f) => f.completed_at), faiList.map((f) => f.started_at), faiList.map((f) => f.created_at));
      case 'FINISHED_GOODS_ENTRY':
        return maxDate(fgInwardList.map((f) => f.inward_entry_date), fgInwardList.map((f) => f.created_at));
      case 'PACKING':
        return maxDate(packList.map((p) => p.actual_end), packList.map((p) => p.actual_start), packList.map((p) => p.created_at));
      case 'DISPATCH':
        return maxDate(fgDispatchList.map((d) => d.dispatch_date), invList.map((i) => i.posted_at), invList.map((i) => i.created_at));
      case 'SALES_INVOICE':
        return maxDate(invList.map((i) => i.posted_at), invList.map((i) => i.created_at), invList.map((i) => i.updated_at));
      default:
        return null;
    }
  };

  for (const key of STAGE_KEYS) {
    const detail = sequentialStageMap[key]?.detail || stageMap[key]?.detail || '';
    const skipped = isSkippedDetail(detail);
    const lastAt = stageLastAt(key);
    stageMeta[key] = {
      skipped,
      last_at: lastAt ? lastAt.toISOString() : null,
    };
  }

  // ---------- Build response ----------
  const stages = STAGE_KEYS.map((key) => ({
    key,
    label: STAGE_LABELS[key] || key,
    status: sequentialStageMap[key]?.status || STEP_STATUS.NOT_STARTED,
    detail: sequentialStageMap[key]?.detail || '',
    links: links[key] || [],
    meta: stageMeta[key] || {},
  }));

  return {
    customer_po: { id: cpoJson.id, po_number: cpoJson.po_number, status: cpoJson.status },
    quantities: {
      ordered: round3(orderedQty),
      produced: round3(producedQty),
      rejected: round3(rejectedQty),
      dispatched: round3(dispatchedQty),
      balance: round3(balanceQty),
      line_count: items.length,
      required_total: round3(requiredTotal),
      shortage_total: round3(shortageTotal),
    },
    progress_percent: progressPercent,
    pending_actions,
    stages,
    document_trail: trail,
    meta: {
      production_order_count: poActiveCount,
      work_order_count: woList.length,
      route_card_count: rcList.length,
      material_request_count: mrList.length,
      purchase_order_count: poPurchaseList.length,
      gate_entry_count: gateEntryList.length,
      grn_count: grnList.length,
      production_qc_count: qcList.length,
      final_inspection_count: faiList.length,
      fg_inward_count: fgInwardList.length,
      packing_count: packList.length,
      fg_dispatch_count: fgDispatchList.length,
      sales_invoice_count: invList.length,
    },
  };
}

module.exports = {
  buildProcessFlow,
  STAGE_KEYS,
  STAGE_LABELS,
};

