'use strict';
// routes/workOrders.js — Production Work Orders (generated from Batch Intimation)
const express = require('express');
const router  = express.Router();
const { verifyToken, requireApprovalStep, requireWorkflowPerm, hasStepPerm } = require('../middleware/auth');

const store = () => require('../services/workOrderStore');
const notify = () => require('../services/notifyStore');

// MFG/EXP accept any of 6 formats (empty allowed) — same set as the
// picker-only date fields in public/work-order.html, public/batch-issuance.html
// and public/receipt-production.html: DD-MMM-YYYY, DD-MM-YYYY, MMM-YYYY,
// MM-YYYY, YYYY-MMM, YYYY-MM. Kept in sync with those — this backend check
// must never fall behind the frontend's accepted formats again.
const DATE_RES = [
  /^(\d{1,2})[-/]([A-Za-z]{3})[-/](\d{4})$/,   // DD-MMM-YYYY
  /^(\d{1,2})-(\d{1,2})-(\d{4})$/,             // DD-MM-YYYY
  /^([A-Za-z]{3})[-/](\d{4})$/,                // MMM-YYYY
  /^(\d{1,2})-(\d{4})$/,                       // MM-YYYY
  /^(\d{4})-([A-Za-z]{3})$/,                   // YYYY-MMM
  /^(\d{4})-(\d{1,2})$/,                       // YYYY-MM
];
function isValidMEDate(v) { const t = String(v || '').trim(); return !t || DATE_RES.some(re => re.test(t)); }
function badDate(v) { return v && !isValidMEDate(v); }

// This Work Order's main product's SAP Item Group (ItmsGrpCod) — resolved
// fresh per notify() call (rare enough that caching isn't worth it) so
// notifyStore's Item-Group email routing (Admin → System Settings → "Notify
// by Item Group") can reach the right inbox. Never throws — a lookup failure
// just means no group-routed recipients get added, the normal step/module
// recipients still fire regardless.
async function itemGroupOf(itemCode, company) {
  if (!itemCode) return null;
  try {
    const { getPool } = require('../services/sqlPool');
    const pool = await getPool(company || null);
    const r = await pool.request().query(`SELECT "ItmsGrpCod" FROM [dbo].[OITM] WHERE "ItemCode" = '${String(itemCode).replace(/'/g, "''")}'`);
    return r.recordset && r.recordset[0] ? String(r.recordset[0].ItmsGrpCod) : null;
  } catch (e) { console.warn('[WO] itemGroupOf lookup failed:', e.message); return null; }
}

// Batched version — one round trip for a whole list's worth of distinct
// product codes, instead of one query per row. Returns {itemCode: groupCode}.
async function itemGroupsFor(itemCodes, company) {
  const codes = [...new Set((itemCodes || []).filter(Boolean))];
  if (!codes.length) return {};
  try {
    const { getPool } = require('../services/sqlPool');
    const pool = await getPool(company || null);
    const list = codes.map(c => `'${String(c).replace(/'/g, "''")}'`).join(',');
    const r = await pool.request().query(`SELECT "ItemCode","ItmsGrpCod" FROM [dbo].[OITM] WHERE "ItemCode" IN (${list})`);
    const map = {};
    (r.recordset || []).forEach(row => { map[row.ItemCode] = String(row.ItmsGrpCod); });
    return map;
  } catch (e) { console.warn('[WO] itemGroupsFor lookup failed:', e.message); return {}; }
}

function normRaw(rows) {
  return (rows || [])
    .filter(r => (r.itemCode || '').trim() || (r.rawMaterial || '').trim())
    .map(r => ({
      itemCode:    (r.itemCode || '').trim(),
      rawMaterial: (r.rawMaterial || '').trim(),
      spec:        (r.spec || '').trim(),
      stdQty:      (r.stdQty || '').toString().trim(),      // Qty Req. (std/per unit)
      uom:         (r.uom || '').trim(),
      ovg:         (r.ovg || '').toString().trim(),
      qtyReq:      (r.qtyReq || '').toString().trim(),       // Qty Req. (total)
      weighingBalanceId: (r.weighingBalanceId || '').trim(),
      arNo:        (r.arNo || '').trim(),
      // Multi-solution support: which Solution this raw material belongs to,
      // and that solution's own Batch Size (Ltr) — different solutions in the
      // same product can have different batch sizes.
      solCode:      (r.solCode || '').trim(),
      solName:      (r.solName || '').trim(),
      solBatchSize: (r.solBatchSize || '').toString().trim(),
      solPerUnitLitres: r.solPerUnitLitres != null && r.solPerUnitLitres !== '' ? Number(r.solPerUnitLitres) : '',
      solBSManual:  !!r.solBSManual,
      // Item Group Rules "RAW" override: shown as itself (not exploded from a
      // Solution), qty calc = Std Qty/Unit × Total Units × (1+Ovg%) — see
      // recalcMaterials() in work-order.html.
      directRaw:    !!r.directRaw,
    }));
}
function normPack(rows) {
  return (rows || [])
    .filter(r => (r.itemCode || '').trim() || (r.packingMaterial || '').trim())
    .map(r => ({
      itemCode:        (r.itemCode || '').trim(),
      packingMaterial: (r.packingMaterial || '').trim(),
      artworkNo:       (r.artworkNo || '').trim(),
      stdQtyPerUnit:   (r.stdQtyPerUnit || '').toString().trim(),
      // Stock UOM from SAP (display-only column) — was missing from this
      // whitelist entirely, so it silently vanished on every save even
      // though it displayed correctly right after loading the BOM.
      uom:             (r.uom || '').trim(),
      // Std. Qty/Unit's own chosen display unit (mg/gm/Kg/ml/etc, or blank
      // for a plain count) and the resulting Qty Req.(Units) unit label —
      // also missing before, so picking a real unit never survived a save.
      stdQtyUnit:      (r.stdQtyUnit || '').trim(),
      qtyUnitLabel:    (r.qtyUnitLabel || '').trim(),
      ovgPercent:      (r.ovgPercent || '').toString().trim(),
      qtyReqUnits:     (r.qtyReqUnits || '').toString().trim(),
      // AR No. is normally set via the separate per-row PATCH on Verify Work
      // Order, but was missing here too — meaning a later edit+save of the
      // WHOLE Work Order (e.g. a QA correction) would silently erase every
      // AR No. already recorded, since this whitelist is what's actually
      // persisted, not just what the client happens to send.
      arNo:            (r.arNo || '').trim(),
      // Per-row Round Up/Exact override (work-order.html's round-pill on a
      // "no unit picked" row) — same class of bug as the ones above: missing
      // from this whitelist, so any edit+save of the Work Order silently
      // reset every row back to the global default, even though the pill
      // itself displayed the override correctly right up until that save.
      roundUp:         r.roundUp != null ? !!r.roundUp : null,
    }));
}
function pickHeader(b) {
  return {
    reference:   b.reference || '',
    productName: b.productName || '',
    productDesc: b.productDesc || '',
    genericName: b.genericName || '',
    productCode: b.productCode || '',
    batchNumber: b.batchNumber || '',
    batchSize:   b.batchSize || '',
    totalUnits:  b.totalUnits || '',
    mfgDate:     b.mfgDate || null,
    expDate:     b.expDate || null,
    packSize:    b.packSize || '',
    type:        b.type || '',
    market:      b.market || '',
    remarks:     b.remarks || '',
    rawMaterials:    normRaw(b.rawMaterials),
    packingMaterials: normPack(b.packingMaterials),
    componentsOnly:  !!b.componentsOnly,
  };
}

router.get('/', verifyToken, requireWorkflowPerm('work_order', 'view'), async (req, res) => {
  try {
    const { mine, company, status } = req.query;
    let data = await store().listWorkOrders({
      mine: mine === '1' ? req.user.username : undefined, company, status,
    });
    // Item Group visibility restriction (Admin → user → Issue Items allowed
    // groups) — same 'issueItemGroups' list already enforced at actual
    // issuance time, reused here purely for list visibility: a user
    // restricted to specific Item Groups shouldn't see OTHER groups' Work
    // Orders in the list at all. Empty list (default) = unrestricted.
    try {
      const acting = await require('../services/hanaUsers').findById(req.user.id);
      const allowedGroups = Array.isArray(acting && acting.issueItemGroups) ? acting.issueItemGroups.map(String) : [];
      if (allowedGroups.length && data.length) {
        const byCompany = {};
        data.forEach(w => { (byCompany[w.company || ''] = byCompany[w.company || ''] || []).push(w.productCode); });
        const groupMaps = {};
        await Promise.all(Object.keys(byCompany).map(async co => {
          groupMaps[co] = await itemGroupsFor(byCompany[co], co || null);
        }));
        const allowSet = new Set(allowedGroups);
        data = data.filter(w => allowSet.has((groupMaps[w.company || ''] || {})[w.productCode]));
      }
    } catch (e) { console.warn('[WO] item-group visibility filter failed (non-fatal):', e.message); }
    res.json({ success: true, data });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

router.get('/:id', verifyToken, requireWorkflowPerm('work_order', 'view'), async (req, res) => {
  try {
    const r = await store().findById(req.params.id);
    if (!r || r.isDeleted) return res.status(404).json({ success: false, message: 'Not found' });
    res.json({ success: true, data: r });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// Create (generate) a work order — this IS the "Prepared By QA" step, so only
// users assigned that approval step (or admin) may create one. Having the
// production-work-order MODULE just lets a user open/view the page; it does
// not by itself grant the right to originate a new work order.
router.post('/', verifyToken, requireApprovalStep('work_order:prepared_qa', 'add'), async (req, res) => {
  try {
    const b = req.body || {};
    if (!(b.productName || b.productCode))
      return res.status(400).json({ success: false, message: 'Product Name / Code is required' });
    if (badDate(b.mfgDate) || badDate(b.expDate))
      return res.status(400).json({ success: false, message: 'Invalid MFG/EXP date — use DD-MMM-YYYY or MMM/YYYY' });
    // A given (Intimation, Product, Batch No.) combination may only ever
    // produce ONE Work Order — its quantity is fully captured the first time.
    // Client-side the picker hides/disables already-used batches, but this is
    // the enforcing check (the client guard alone can be bypassed).
    if (b.intimationId) {
      const existing = await store().listWorkOrders({});
      const dup = existing.find(w => !w.isDeleted
        && String(w.intimationId) === String(b.intimationId)
        && (w.productCode || '') === (b.productCode || '')
        && (w.batchNumber || '') === (b.batchNumber || ''));
      if (dup)
        return res.status(409).json({ success: false, message: `A Work Order (${dup.woNo}) has already been generated for this Intimation's batch "${b.batchNumber || b.productCode}" — its full quantity is already captured.` });
    }
    const saved = await store().insertWorkOrder({
      ...pickHeader(b),
      intimationId: b.intimationId || null,
      company: b.company || '',
      createdBy: req.user.username, createdByName: req.user.name || req.user.username,
    });
    res.json({ success: true, data: saved });
    if (saved.status === 'IN_PROGRESS') {
      const nextKey = WORK_ORDER_STEP_KEYS[saved.stage];
      notify().notify({
        stepFullKey: nextKey ? `work_order:${nextKey}` : null,
        itemGroupCode: await itemGroupOf(saved.productCode, saved.company),
        title: `Work Order ${saved.woNo} — awaiting ${saved.currentStep}`,
        lines: [['Work Order', saved.woNo], ['Product', saved.productName || ''], ['Created By', saved.createdByName], ['Pending Step', saved.currentStep]],
        url: `${process.env.APP_BASE_URL || ''}/work-order?id=${saved.id}`,
        excludeUsernames: [req.user.username],
      });
    }
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// Edit header/materials — normally only while In Progress (requires 'edit'
// perm on whichever step currently owns the record, same step that would
// act next). A REJECTED work order is ALSO editable, but as a combined
// edit+resubmit: the save both applies the changes AND restarts the full
// approval chain from step 1 (see resubmitAfterReject) — since none of the
// original approvers ever saw the edited content. Editing a rejected order
// is gated on the FIRST step's 'edit' permission (the same step it restarts
// at), not the step that happened to reject it.
router.put('/:id', verifyToken, async (req, res) => {
  try {
    const existing = await store().findById(req.params.id);
    if (!existing || existing.isDeleted) return res.status(404).json({ success: false, message: 'Not found' });
    if (badDate(req.body?.mfgDate) || badDate(req.body?.expDate))
      return res.status(400).json({ success: false, message: 'Invalid MFG/EXP date — use DD-MMM-YYYY or MMM/YYYY' });

    if (existing.status === 'REJECTED') {
      const firstStepKey = WORK_ORDER_STEP_KEYS[0];
      if (!hasStepPerm(req.user, `work_order:${firstStepKey}`, 'edit'))
        return res.status(403).json({ success: false, message: `You are not assigned "edit" on approval step: work_order:${firstStepKey}` });
      const updated = await store().resubmitAfterReject(req.params.id, pickHeader(req.body || {}), {
        by: req.user.username, byName: req.user.name || req.user.username,
      });
      res.json({ success: true, data: updated });
      const nextKey = WORK_ORDER_STEP_KEYS[updated.stage];
      notify().notify({
        stepFullKey: nextKey ? `work_order:${nextKey}` : null,
        itemGroupCode: await itemGroupOf(updated.productCode, updated.company),
        title: `Work Order ${updated.woNo} — Resubmitted, awaiting ${updated.currentStep}`,
        lines: [['Work Order', updated.woNo], ['Product', updated.productName || ''], ['Resubmitted By', req.user.name || req.user.username], ['Pending Step', updated.currentStep]],
        url: `${process.env.APP_BASE_URL || ''}/work-order?id=${updated.id}`,
        excludeUsernames: [req.user.username],
      });
      return;
    }

    if (existing.status !== 'IN_PROGRESS')
      return res.status(409).json({ success: false, message: 'Work order is ' + existing.status.toLowerCase() + ' and cannot be edited' });
    const curStepKey = WORK_ORDER_STEP_KEYS[existing.stage];
    if (!curStepKey || !hasStepPerm(req.user, `work_order:${curStepKey}`, 'edit'))
      return res.status(403).json({ success: false, message: `You are not assigned "edit" on approval step: work_order:${curStepKey || '?'}` });
    const updated = await store().updateWorkOrder(req.params.id, pickHeader(req.body || {}));
    res.json({ success: true, data: updated });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// Workflow: approve (advance) or reject
// Index-aligned with services/workOrderStore.js STEPS (both are the 5-step
// QA/Production sign-off chain) and services/approvalStepsStore.js's
// workflow:'work_order' step keys.
const WORK_ORDER_STEP_KEYS = ['prepared_qa', 'checked_qc', 'checked_production', 'checked_mgr_production', 'approved_mgr_qa'];

router.patch('/:id/action', verifyToken, async (req, res) => {
  try {
    const action = (req.body.action || '').toLowerCase();
    if (!['approve', 'reject'].includes(action))
      return res.status(400).json({ success: false, message: 'action must be approve or reject' });
    if (action === 'reject' && !(req.body.remarks || '').trim())
      return res.status(400).json({ success: false, message: 'A reason is required to reject a Work Order' });
    {
      const wo = await store().findById(req.params.id);
      if (!wo) return res.status(404).json({ success: false, message: 'Work order not found' });
      const stepKey = WORK_ORDER_STEP_KEYS[wo.stage];
      if (!stepKey || !hasStepPerm(req.user, `work_order:${stepKey}`, 'approve'))
        return res.status(403).json({ success: false, message: `You are not assigned "approve" on approval step: work_order:${stepKey || '?'}` });
    }
    const updated = await store().workflowAction(req.params.id, {
      action, by: req.user.username, byName: req.user.name || req.user.username,
      remarks: req.body.remarks || '',
    });
    res.json({ success: true, data: updated });
    const woUrl = `${process.env.APP_BASE_URL || ''}/work-order?id=${updated.id}`;
    if (action === 'reject') {
      notify().notify({
        stepFullKey: 'work_order:prepared_qa',
        itemGroupCode: await itemGroupOf(updated.productCode, updated.company),
        title: `Work Order ${updated.woNo} — Rejected`,
        lines: [['Work Order', updated.woNo], ['Product', updated.productName || ''], ['Rejected By', req.user.name || req.user.username], ['Remarks', req.body.remarks || '']],
        url: woUrl,
        excludeUsernames: [req.user.username],
      });
    } else if (updated.status === 'IN_PROGRESS') {
      const nextKey = WORK_ORDER_STEP_KEYS[updated.stage];
      notify().notify({
        stepFullKey: nextKey ? `work_order:${nextKey}` : null,
        itemGroupCode: await itemGroupOf(updated.productCode, updated.company),
        title: `Work Order ${updated.woNo} — awaiting ${updated.currentStep}`,
        lines: [['Work Order', updated.woNo], ['Product', updated.productName || ''], ['Approved By', req.user.name || req.user.username], ['Pending Step', updated.currentStep]],
        url: woUrl,
        excludeUsernames: [req.user.username],
      });
    } else if (updated.status === 'APPROVED') {
      notify().notify({
        stepFullKey: 'work_order:prepared_qa',
        itemGroupCode: await itemGroupOf(updated.productCode, updated.company),
        title: `Work Order ${updated.woNo} — Fully Approved`,
        lines: [['Work Order', updated.woNo], ['Product', updated.productName || ''], ['Final Approval By', req.user.name || req.user.username]],
        url: woUrl,
        excludeUsernames: [req.user.username],
      });
    }
  } catch (err) { res.status(400).json({ success: false, message: err.message }); }
});

// Recall of a FULLY APPROVED Work Order back to QA for editing — not a
// normal in-flight rejection (no per-stage approval step is checked), just
// an override for a document that already finished its whole chain.
// Admin/system_admin always bypass; a regular user may also be granted this
// specifically via 'approve' on the dedicated work_order:send_to_qa step
// (Admin → Edit User → Approval Steps) — previously this action had NO
// assignable step at all. Re-uses the exact same status ('REJECTED') the
// normal reject action sets, so the existing "Edit & Resubmit" flow
// (PUT /:id above) picks it up for free — once edited, it restarts the
// full 5-step chain from Prepared By QA.
router.post('/:id/send-to-qa', verifyToken, async (req, res) => {
  try {
    if (req.user.role !== 'admin' && req.user.role !== 'system_admin' && !hasStepPerm(req.user, 'work_order:send_to_qa', 'approve'))
      return res.status(403).json({ success: false, message: 'You are not assigned to approval step: work_order:send_to_qa' });
    const updated = await store().sendBackToQaForEdit(req.params.id, {
      by: req.user.username, byName: req.user.name || req.user.username,
      remarks: req.body?.remarks || '',
    });
    res.json({ success: true, data: updated });
    notify().notify({
      stepFullKey: 'work_order:prepared_qa',
      itemGroupCode: await itemGroupOf(updated.productCode, updated.company),
      title: `Work Order ${updated.woNo} — Sent back to QA for edit`,
      lines: [['Work Order', updated.woNo], ['Product', updated.productName || ''], ['Sent Back By', req.user.name || req.user.username], ['Remarks', req.body?.remarks || '']],
      url: `${process.env.APP_BASE_URL || ''}/work-order?id=${updated.id}`,
      excludeUsernames: [req.user.username],
    });
  } catch (err) { res.status(400).json({ success: false, message: err.message }); }
});

router.delete('/:id', verifyToken, async (req, res) => {
  try {
    const existing = await store().findById(req.params.id);
    if (!existing) return res.status(404).json({ success: false, message: 'Not found' });
    const curStepKey = WORK_ORDER_STEP_KEYS[existing.stage];
    if (!hasStepPerm(req.user, `work_order:${curStepKey || 'prepared_qa'}`, 'delete'))
      return res.status(403).json({ success: false, message: `You are not assigned "delete" on approval step: work_order:${curStepKey || 'prepared_qa'}` });
    await store().softDelete(req.params.id);
    res.json({ success: true });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// ── Per-row material fields (Weighing Balance ID / AR No.) ─────────────────
// Editable by whoever holds 'edit' on work_order:issue — independent of the
// work order's own approval status/edit-lock (this happens AFTER approval,
// at the moment materials are physically issued).
router.patch('/:id/row/:section/:index', verifyToken, async (req, res) => {
  try {
    const section = req.params.section;
    if (!['raw', 'pack'].includes(section)) return res.status(400).json({ success: false, message: 'section must be "raw" or "pack"' });
    if (!hasStepPerm(req.user, 'work_order:issue', 'edit'))
      return res.status(403).json({ success: false, message: 'You are not assigned "edit" on approval step: work_order:issue' });
    const patch = {};
    if (req.body.weighingBalanceId !== undefined && section === 'raw') patch.weighingBalanceId = String(req.body.weighingBalanceId || '').trim();
    if (req.body.arNo !== undefined) patch.arNo = String(req.body.arNo || '').trim();
    if (!Object.keys(patch).length) return res.status(400).json({ success: false, message: 'Nothing to update' });
    const updated = await store().patchMaterialRow(req.params.id, section, req.params.index, patch);
    res.json({ success: true, data: updated });
  } catch (err) { res.status(400).json({ success: false, message: err.message }); }
});

// ── Per-row Issued/Received/Verified one-click stamps ───────────────────────
// Each is a SEPARATE portal-only sign-off on that ONE material row — not the
// whole work order — because different items can be issued/received/verified
// on different days by different people. Each is gated by 'approve' on its
// own approval step, and can only be stamped once per row.
const ROW_STAMP_STEPS = { issued: 'work_order:issue', received: 'work_order:receive', verified: 'work_order:verify' };
// Enforced order per row: Issued → Received → Verified. Each key names the
// PRECEDING stamp that must already exist before this one can be set.
const ROW_STAMP_PREREQ = { received: 'issued', verified: 'received' };
router.post('/:id/row/:section/:index/mark', verifyToken, async (req, res) => {
  try {
    const section = req.params.section;
    if (!['raw', 'pack'].includes(section)) return res.status(400).json({ success: false, message: 'section must be "raw" or "pack"' });
    const which = (req.body.which || '').toLowerCase();
    const stepKey = ROW_STAMP_STEPS[which];
    if (!stepKey) return res.status(400).json({ success: false, message: 'which must be issued, received, or verified' });
    // Production Order Issuance (production_order:issuance) and Work Order
    // row-stamping are different screens/responsibilities — no longer
    // coupled. Marking a row here always requires its own explicit
    // approval-step grant.
    const allowed = hasStepPerm(req.user, stepKey, 'approve');
    if (!allowed)
      return res.status(403).json({ success: false, message: `You are not assigned "approve" on approval step: ${stepKey}` });
    const wo = await store().findById(req.params.id);
    if (!wo) return res.status(404).json({ success: false, message: 'Work order not found' });
    const arr = (section === 'raw' ? wo.rawMaterials : wo.packingMaterials) || [];
    const i = parseInt(req.params.index);
    if (!(i >= 0) || i >= arr.length) return res.status(404).json({ success: false, message: 'Row not found' });
    const row = arr[i];
    const atField = `${which}At`;
    // A stamp can only be set once — EXCEPT for a woVerifyOverride user (or
    // admin), who may re-stamp ANY of the three (issued/received/verified)
    // even after it's already signed, to correct who it's recorded as
    // signed by and/or its date. That's the actual point of the override:
    // catching up/correcting paperwork on any step, not just Verified, and
    // not just rows nobody has touched yet.
    const canOverrideStamp = req.user.role === 'admin' || req.user.woVerifyOverride;
    if (row[atField] && !canOverrideStamp) return res.status(400).json({ success: false, message: `This row's "${which}" was already stamped by ${row[which + 'ByName'] || row[which + 'By']}` });
    // Per-user item-group restriction (Admin → user → Issue Items) — same rule
    // enforced on the real SAP issuance (routes/sap.js). Marking a row Issued
    // must be blocked for item groups this user isn't allowed to issue, even
    // if they hold Issue for Production generally.
    if (which === 'issued') {
      try {
        const acting = await require('../services/hanaUsers').findById(req.user.id);
        const allowedGroups = Array.isArray(acting && acting.issueItemGroups) ? acting.issueItemGroups.map(String) : [];
        if (allowedGroups.length && row.itemCode) {
          const { getPool } = require('../services/sqlPool');
          // Must query the WO's OWN company database, not whatever the shared
          // pool happens to default to (services/sqlPool.js's getPool() is
          // per-database now — see [[displayed-sap-company-restriction]]) —
          // this route gets no `company` param from the frontend at all, so
          // the Work Order record's own stored company is the source of truth.
          const pool = await getPool(wo.company || null);
          const r = await pool.request().query(`SELECT "ItmsGrpCod" FROM [dbo].[OITM] WHERE "ItemCode" = '${String(row.itemCode).replace(/'/g, "''")}'`);
          const grp = r.recordset && r.recordset[0] ? String(r.recordset[0].ItmsGrpCod) : null;
          if (!grp || !allowedGroups.includes(grp))
            return res.status(403).json({ success: false, message: `You are not permitted to issue this item (${row.itemCode}) — its item group is not in your allowed list.` });
        }
      } catch (e) { console.warn('[WO-ROW-MARK] item-group restriction check failed:', e.message); }
      // Mirrors the client's own gate (public/verify-work-order.html's
      // issCells()) — the 'mark_issued' bypass ONLY applies to Raw Material:
      // under that mode THIS stamp is what writes Qty Issued for a raw row
      // in the first place (checking it first would be circular). Packing
      // Material's Qty Issued always comes from the live SAP posting
      // regardless of this setting (never written by this stamp), so it
      // must always be checked for real — otherwise a Work Order with no
      // Production Order/issuance posted yet would let Packing/Components
      // rows be marked Issued with nothing actually issued. An override
      // user may still catch up paperwork regardless.
      const rawRowBypass = section === 'raw' && require('../services/appSettingsStore').woRawQtyIssuedSource() === 'mark_issued';
      if (!canOverrideStamp) {
        // Even under the raw-material bypass, nothing is issuable before a
        // Production Order actually exists for this Work Order — there's no
        // production event yet for the stamp to be recording. This is the
        // actual fix for a fresh WO with no PO yet still allowing every
        // raw-material row to be marked Issued.
        let iss = null;
        try { iss = await computeIssuance(wo.id, wo.company); } catch (e) { console.warn('[WO-ROW-MARK] issuance lookup failed:', e.message); }
        if (!iss || !iss.hasPO)
          return res.status(400).json({ success: false, message: 'Cannot mark "Issued" — no Production Order has been created for this Work Order yet.' });
        if (!rawRowBypass) {
          const reqQty = parseFloat(section === 'raw' ? row.qtyReq : row.qtyReqUnits) || 0;
          if (reqQty > 0) {
            const issuedQty = section === 'raw'
              ? (parseFloat(row.qtyIssued) || 0)
              : ((iss.qtyByItem && iss.qtyByItem[String(row.itemCode || '').trim()]) || 0);
            if (issuedQty + 0.001 < reqQty)
              return res.status(400).json({ success: false, message: `Cannot mark "Issued" — only ${issuedQty} of ${reqQty} required has actually been issued for this item.` });
          }
        }
      }
    }
    // Issued→Received→Verified order is enforced for EVERYONE, including a
    // woVerifyOverride/admin user — no one may sign a stage before the prior
    // one has genuinely happened. That override only ever applies to
    // RE-SIGNING an already-completed stamp (see the canOverrideStamp check
    // above — correcting who it's recorded as signed by and/or its date),
    // never to skipping straight past a stage that hasn't happened yet.
    const prereq = ROW_STAMP_PREREQ[which];
    if (prereq && !row[`${prereq}At`])
      return res.status(400).json({ success: false, message: `Mark "${prereq}" on this row before "${which}".` });
    // Normal case: the stamp always records the ACTUAL logged-in user, right
    // now — no client input is trusted for who/when. The one narrow
    // exception: a user explicitly granted woVerifyOverride (Admin → Edit
    // User), or anyone with the admin role (same bypass every approval-step
    // check gives admin elsewhere — see hasStepPerm), can, on ANY of the
    // three stamps (issued/received/verified), sign as a different user
    // and/or backdate the sign date — for catching up paperwork signed on
    // paper on an earlier date by someone else. Every other user is
    // unaffected regardless of which stamp.
    let signerUsername = req.user.username;
    let signerName = req.user.name || req.user.username;
    let atIso = new Date().toISOString();
    if (canOverrideStamp) {
      const actAs = String(req.body.actAsUsername || '').trim();
      if (actAs) {
        const actingUser = await require('../services/hanaUsers').findByUsername(actAs);
        if (!actingUser) return res.status(400).json({ success: false, message: `User "${actAs}" not found` });
        signerUsername = actingUser.username;
        signerName = actingUser.fullName || actingUser.username;
      }
      if (req.body.signedAt) {
        const d = new Date(req.body.signedAt);
        if (isNaN(d.getTime())) return res.status(400).json({ success: false, message: 'Invalid sign date' });
        if (d.getTime() > Date.now()) return res.status(400).json({ success: false, message: 'Sign date cannot be in the future' });
        atIso = d.toISOString();
      }
    }
    const patch = {
      [`${which}By`]: signerUsername,
      [`${which}ByName`]: signerName,
      [atField]: atIso,
    };
    // Admin → System Settings → "Raw Material Qty Issued Source" picks which
    // of two decoupled events is authoritative for EVERY material table's
    // Qty Issued (Raw Material, Packing Material, Components alike):
    // 'issue_for_production' (default) calculates it from the real SAP
    // posting instead (see routes/sap.js's /issue-production, and the live
    // qtyByItem lookup for Packing/Components); only under 'mark_issued'
    // does THIS one-click paperwork stamp (the same action that sets Issued
    // By/Date, above) set Qty Issued = the row's full Qty Req.
    if (which === 'issued' && (section === 'raw' || section === 'pack') && require('../services/appSettingsStore').woRawQtyIssuedSource() === 'mark_issued') {
      patch.qtyIssued = parseFloat(section === 'raw' ? row.qtyReq : row.qtyReqUnits) || 0;
    }
    const updated = await store().patchMaterialRow(req.params.id, section, i, patch);
    res.json({ success: true, data: updated });
    // Notify whoever holds the NEXT stamp in the Issued→Received→Verified
    // chain for this row (no next step after Verified — nothing to notify).
    const NEXT_STAMP_STEP = { issued: 'work_order:receive', received: 'work_order:verify' };
    const nextStep = NEXT_STAMP_STEP[which];
    if (nextStep) {
      notify().notify({
        stepFullKey: nextStep,
        title: `Work Order ${wo.woNo} — item ${which}, awaiting ${which === 'issued' ? 'Received By' : 'Verified By'}`,
        lines: [['Work Order', wo.woNo], ['Item', row.itemCode || row.rawMaterial || ''], [which === 'issued' ? 'Issued By' : 'Received By', req.user.name || req.user.username]],
        url: `${process.env.APP_BASE_URL || ''}/verify-work-order`,
        excludeUsernames: [req.user.username],
      });
    }
  } catch (err) { res.status(400).json({ success: false, message: err.message }); }
});

// ── Issuance info for the printable Work Order ─────────────────────────────
// Pulls the linked Production Order(s): per-component issued quantity (live
// from SAP, summed by item code) and the Issuance / Receipt / Close signers
// (name + username + date, from each PO's workflow log). If no PO exists yet,
// or SAP is unreachable, the corresponding pieces come back empty so the print
// view simply leaves those cells blank.
async function computeIssuance(woId, co) {
  const poStore = require('../services/productionOrderStore');
  const sapSL   = require('../services/sapServiceLayer');
  const pos = (await poStore.listProductionOrders({ workOrderId: woId, company: co })) || [];
  const active = pos.filter(p => !p.isDeleted);
  const qtyByItem = {};
  let issued = null, received = null, receivedManual = null, verified = null;
  const pickLatest = (cur, e) => (!cur || new Date(e.at) >= new Date(cur.at)) ? e : cur;
  for (const po of active) {
    for (const e of (Array.isArray(po.workflowLog) ? po.workflowLog : [])) {
      if (e.action === 'rejected') continue;
      if (e.step === 'Issuance') issued = pickLatest(issued, e);
      else if (e.step === 'Receipt from Production') received = pickLatest(received, e);
      else if (e.step === 'Verified') verified = pickLatest(verified, e);   // portal-only post-Issuance sign-off
    }
    // Portal-only "Received By" sign-off — ALWAYS overrides the SAP receipt
    // step's signer above, wherever it exists (see productionOrderStore.receiveManual()).
    if (po.receivedManualAt) {
      receivedManual = pickLatest(receivedManual, { by: po.receivedManualBy, byName: po.receivedManualByName, at: po.receivedManualAt });
    }
    if (po.sapAbsEntry) {
      try {
        const so = await sapSL.sapRequest('GET', `ProductionOrders(${parseInt(po.sapAbsEntry)})`, null, co);
        const lines = (so.ProductionOrderLines || []).filter(l => l.ItemType !== 'pit_Resource');
        for (const l of lines) {
          const code = (l.ItemNo || l.ItemCode || '').toString().trim();
          if (!code) continue;
          qtyByItem[code] = (qtyByItem[code] || 0) + (Number(l.IssuedQuantity) || 0);
        }
      } catch (_e) { /* SAP unreachable → leave issued qty blank */ }
    }
  }
  const sig = (e) => e ? { name: e.byName || e.by || '', user: e.by || '', at: e.at || '' } : null;
  return { hasPO: active.length > 0, qtyByItem, issuedBy: sig(issued), receivedBy: sig(receivedManual || received), verifiedBy: sig(verified) };
}

router.get('/:id/issuance-info', verifyToken, async (req, res) => {
  try {
    const data = await computeIssuance(parseInt(req.params.id), req.query.company || null);
    res.json({ success: true, data });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// ── Server-generated PDF of the Production Work Order (pdfmake) ─────────────
router.get('/:id/pdf', verifyToken, requireWorkflowPerm('work_order', 'view'), async (req, res) => {
  const co = req.query.company || null;
  try {
    const wo = await store().findById(req.params.id);
    if (!wo) return res.status(404).json({ success: false, message: 'Not found' });
    const settings = require('../services/appSettingsStore').getAll();
    const iss = await computeIssuance(parseInt(req.params.id), co);
    // Gather signature images: the 5 approval signers + every material row's
    // own Issued/Received/Verified signer (per-row sign-offs — see
    // public/verify-work-order.html; each row is stamped independently).
    const users = new Set();
    (Array.isArray(wo.workflowLog) ? wo.workflowLog : []).forEach(l => { if (l.by) users.add(l.by); });
    [...(wo.rawMaterials || []), ...(wo.packingMaterials || [])].forEach(r => {
      ['issuedBy', 'receivedBy', 'verifiedBy'].forEach(k => { if (r[k]) users.add(r[k]); });
    });
    const hu = require('../services/hanaUsers');
    const sigs = {};
    for (const u of users) { try { const s = await hu.getSignatureByUsername(u); if (s) sigs[u] = s; } catch (_e) {} }
    // U_NewItemCode (OITM) for the header Product Code only — printed as
    // "(code)" right after it, when SAP has a value (see public/work-order-print.html).
    let newItemCode = '';
    try {
      const { getPool } = require('../services/sqlPool');
      const pool = await getPool(co);
      const code = String(wo.productCode || '').replace(/'/g, "''");
      const r = await pool.request().query(`SELECT "U_NewItemCode" FROM [dbo]."OITM" WHERE "ItemCode"='${code}'`);
      newItemCode = r.recordset?.[0]?.U_NewItemCode || '';
    } catch (_e) {}
    const qtyIssuedSource = require('../services/appSettingsStore').woRawQtyIssuedSource();
    const doc = require('../services/workOrderPdf').generate({ wo, settings, iss, sigs, newItemCode, qtyIssuedSource });
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `inline; filename="WO-${(wo.woNo || wo.id)}.pdf"`);
    doc.pipe(res);
    doc.end();
  } catch (err) {
    if (!res.headersSent) res.status(500).json({ success: false, message: err.message });
  }
});

module.exports = router;