// services/approvalStepsStore.js
// Generic Approval Step registry: a catalogue of steps grouped by workflow,
// used to grant users fine-grained "which step can this person act on"
// permissions (services/hanaUsers.js stores the per-user assignment array).
// Built-in workflows are seeded on bootstrap (idempotent); admins can also
// add custom steps to a NEW workflow name via the API.
'use strict';
const { getPool } = require('./appSqlPool');

const TABLE = `[dbo].[ZAPPROVAL_STEPS]`;

// Built-in steps, seeded once. STEP_KEY must be unique within a workflow.
const BUILTIN_STEPS = [
  // Work Order — 5-step QA/Production sign-off chain
  { workflow: 'work_order', key: 'prepared_qa',            label: 'Prepared By QA',                        order: 1 },
  { workflow: 'work_order', key: 'checked_qc',              label: 'Checked By QC',                         order: 2 },
  { workflow: 'work_order', key: 'checked_production',      label: 'Checked By Store In-Charge',            order: 3 },
  { workflow: 'work_order', key: 'checked_mgr_production',  label: 'Checked By (Manager Production)',       order: 4 },
  { workflow: 'work_order', key: 'approved_mgr_qa',         label: 'Approved By (Manager QA)',               order: 5 },
  // Corrective, NOT part of the sequential 5-step chain above (same pattern as
  // production_order's return_components): a portal-only sign-off performed
  // AFTER a linked Production Order has been Issued in SAP — there is no such
  // process in SAP itself. The verifier's signature is captured on the printed
  // Work Order's "Verified By" column. Distinct from production_order:verify
  // (kept, unused, for possible future use) — this is the one actually wired
  // to the "Verify Work Order" screen (public/verify-work-order.html).
  { workflow: 'work_order', key: 'verify', label: 'Verify Work Order (post-Issuance sign-off)', order: 6 },
  // Same idea as 'verify' above — a portal-only manual sign-off, independent
  // of whoever performed the actual "Receipt from Production" SAP step. Lets
  // a designated person (e.g. store keeper physically receiving the batch)
  // record "Received By" for the printed Work Order, overriding the
  // SAP-step-derived value. Managed from the same "Verify Work Order" screen.
  { workflow: 'work_order', key: 'receive', label: 'Received By (post-Issuance sign-off)', order: 7 },
  // Per-row (per material line) sign-off — NOT order-wide. 'edit' lets the
  // issuer fill in that row's Weighing Balance ID / AR No. at the moment of
  // physically issuing it; 'approve' lets them stamp "Issued By/Date" on that
  // one row. See public/verify-work-order.html — each row has its own
  // Issued/Received/Verified stamps since different items can be issued,
  // received and verified on different days by different people.
  { workflow: 'work_order', key: 'issue', label: 'Issued By (per-row post-Issuance sign-off)', order: 8 },
  // Send back to QA — previously Admin/System Admin role ONLY, no
  // assignable step at all: recalls a FULLY APPROVED Work Order back to the
  // first QA step for editing (re-uses the normal Reject status, so once
  // edited it restarts the full 5-step chain from Prepared By QA — see
  // routes/workOrders.js's POST /:id/send-to-qa). Now also grantable to a
  // regular user via 'approve' on this key; admin/system_admin still always
  // bypass regardless.
  { workflow: 'work_order', key: 'send_to_qa', label: 'Send back to QA (recall a fully approved WO for editing)', order: 9 },
  // BOM — up to 4 levels (BOM_APPROVAL_LEVELS controls how many are actually used)
  { workflow: 'bom', key: 'level1', label: 'Level 1 (Manager)',              order: 1 },
  { workflow: 'bom', key: 'level2', label: 'Level 2 (Sr. Manager)',          order: 2 },
  { workflow: 'bom', key: 'level3', label: 'Level 3 (SAP Adder)',            order: 3 },
  { workflow: 'bom', key: 'level4', label: 'Level 4 (SAP Adder — Final)',    order: 4 },
  // Customer / Vendor registration — shared shape for both
  { workflow: 'customer_vendor', key: 'verify',  label: 'Verify (Pending → Verified)',        order: 1 },
  { workflow: 'customer_vendor', key: 'approve', label: 'Final Approval (Verified → SAP B1)',  order: 2 },
  // Project approvals — sequential department chain
  { workflow: 'project', key: 'purchase',    label: 'Purchase',              order: 1 },
  { workflow: 'project', key: 'engineering', label: 'Engineering',           order: 2 },
  { workflow: 'project', key: 'qa',          label: 'QA',                    order: 3 },
  { workflow: 'project', key: 'qc',          label: 'QC',                    order: 4 },
  { workflow: 'project', key: 'legal',       label: 'Legal',                 order: 5 },
  { workflow: 'project', key: 'owner',       label: 'Project Owner',         order: 6 },
  { workflow: 'project', key: 'plant',       label: 'Plant Manager',         order: 7 },
  { workflow: 'project', key: 'finance',     label: 'Finance & Accounts',    order: 8 },
  // Production Order — created FROM a Work Order, then a 6-step SAP B1
  // production lifecycle. Each step's 'approve' perm gates performing that
  // step; 'add' on 'create'/'manual_create' gates who can originate an order.
  // Split into two distinct steps so an admin can allow one without the
  // other — e.g. force everyone through the Work Order-linked flow only,
  // with no standalone/manual entry at all.
  { workflow: 'production_order', key: 'create',        label: 'Create (from Work Order)',           order: 1 },
  { workflow: 'production_order', key: 'manual_create',  label: 'Create (Manual Entry, no Work Order)', order: 2 },
  { workflow: 'production_order', key: 'release',     label: 'Release',                       order: 3 },
  { workflow: 'production_order', key: 'issuance',    label: 'Issuance',                       order: 4 },
  { workflow: 'production_order', key: 'receipt',     label: 'Receipt from Production',        order: 5 },
  { workflow: 'production_order', key: 'transfer_fg', label: 'Transfer to Finished Goods',      order: 6 },
  { workflow: 'production_order', key: 'close',       label: 'Close',                          order: 7 },
  // Corrective side-action, NOT part of the sequential stage chain above:
  // after Issuance, defective component quantities can be returned to stock
  // from the Receipt from Production page (SAP's "Return Components").
  { workflow: 'production_order', key: 'return_components', label: 'Return Components (defective, back to stock)', order: 8 },
  // Portal-only sign-off (NOT a SAP transaction): after Issuance, a separate
  // person verifies the issued production order. Their signature is captured on
  // the printed Work Order's "Verified By" column. 'view' shows the Verify
  // screen; 'approve' performs the verification.
  { workflow: 'production_order', key: 'verify', label: 'Verify (post-Issuance sign-off)', order: 9 },
  // Corrective side-action, NOT part of the sequential stage chain: after
  // Issuance, production may hit a Shortage (top up qty), need a
  // Substitution (issue a different item instead), or a Damage/Loss
  // (write off a damaged qty, optionally to a scrap warehouse) — without
  // stopping the run. 'add' gates who can raise one; 'approve' gates the
  // QA post-hoc sign-off (only relevant when Admin → System Settings →
  // "Deviation — Require QA Approval" is on; the SAP posting itself always
  // happens immediately on raise, regardless of that setting).
  { workflow: 'production_order', key: 'deviation', label: 'Deviation (Shortage / Substitution / Damage)', order: 10 },
  // Pre-PWO Store Review (optional, Admin → System Settings → "Pre-PWO —
  // Store Review Before SAP" — a whole-feature on/off switch, since it may
  // not be needed at every site). When enabled, "Create (from Work Order)"
  // first stages a portal-only Pre-PWO instead of writing to SAP; Production
  // can then optionally share it with Store — who may add/remove/substitute
  // component lines outright — before reverting it back (a single round
  // trip). Actually creating the staging record AND pushing the final
  // Pre-PWO to SAP both stay gated on the existing 'create' step above
  // (same authority as today, just relocated); these two steps only gate
  // the NEW capability of the Store detour itself. 'add' on 'prepwo_share'
  // gates who can share; 'add' on 'prepwo_review' gates who can revert.
  { workflow: 'production_order', key: 'prepwo_share',  label: 'Pre-PWO — Share with Store (before SAP)', order: 11 },
  { workflow: 'production_order', key: 'prepwo_review', label: 'Pre-PWO — Store Review & Revert',           order: 12 },
  // The "+ PWO" shortcut on a Released order's component table: raises a
  // separate, standalone Production Order for a component that is itself an
  // SFG with its own BOM, pre-filled with that line's exact required qty.
  // Deliberately its own step (not reusing 'manual_create') so an admin can
  // hand out this one narrow shortcut without granting full freeform Manual
  // Entry creation, or vice versa.
  { workflow: 'production_order', key: 'create_from_component', label: '"+ PWO" — Create from a Component (SFG sub-assembly)', order: 13 },
  // "Consumable Order" — a standalone manual Production Order restricted to
  // Service items (SAP Item Group 132) only, e.g. for consumable/tooling
  // items that need their own PWO but aren't part of a normal finished-goods
  // BOM chain. Its own steps (not 'manual_create'/the general per-stage
  // steps) so an admin can hand out each narrow capability independently —
  // 'add' here = create one; Release/Issue/Close each get their OWN
  // dedicated step below so a user can hold any subset (e.g. Issue only)
  // without gaining the others or bleeding into the general
  // production_order:release/issuance/close steps used by normal PWOs (and
  // vice versa). No creator bypass — even the order's own creator needs the
  // matching step to progress it further. Consumable Orders never go through
  // Receipt (confirmed workflow is Release → Issue → Close only), so there
  // is deliberately no consumable Receipt step at all.
  { workflow: 'production_order', key: 'consumable_create', label: 'Consumable Order — Add (Manual Entry, Service items only, Group 132)', order: 14 },
  { workflow: 'production_order', key: 'consumable_release', label: 'Consumable Order — Release', order: 15 },
  { workflow: 'production_order', key: 'consumable_issue', label: 'Consumable Order — Issue', order: 16 },
  { workflow: 'production_order', key: 'consumable_close', label: 'Consumable Order — Close', order: 17 },
  // Cancel — previously Admin/System Admin role ONLY, no assignable step at
  // all (an explicit, deliberate restriction — see routes/sap.js's
  // isPoAdminOverride()). Now also grantable to a regular user like any
  // other step, via 'approve' on this key; admin/system_admin still always
  // bypass regardless, same as every other production_order step.
  { workflow: 'production_order', key: 'cancel', label: 'Cancel (bypasses all normal Close checks — irreversible)', order: 18 },
  // Man Power — single entry step; its view/add/edit perms gate the whole
  // Man Power module CRUD (routes/manpower.js). 'edit' also gates Cancel.
  { workflow: 'man_power', key: 'entry', label: 'Man Power Data Entry', order: 1 },
  // OEE — single entry step; its view/add/edit perms gate the whole Overall
  // Equipment Efficiency module CRUD (routes/oee.js). 'edit' also gates Cancel.
  { workflow: 'oee', key: 'entry', label: 'OEE Data Entry', order: 1 },
  // Requirement — general CRUD gate (single entry step, same pattern as
  // man_power:entry / oee:entry): 'view' gates seeing the Requirements page
  // (list + Calculator) at all; 'add' gates creating a Requirement (and the
  // Calculator's "send to New Requirement"); 'edit' gates editing one;
  // 'delete' gates deleting one. Previously this was all-or-nothing via the
  // 'production-requirements' module checkbox alone — see backfillRequirementManagePerm()
  // in services/hanaUsers.js, which copies existing module access onto this
  // step on deploy so nobody regresses.
  { workflow: 'requirement', key: 'manage', label: 'Create / Edit / Delete Requirements & Calculator', order: 1 },
  // Requirement — Store Review Workflow (optional, Admin → System Settings →
  // "Requirement — Store Review Workflow" — a whole-feature on/off switch
  // separate from these permission steps, since it may not be needed at
  // every site). A single round trip: Production shares a Requirement with
  // Store, Store cross-checks it against SAP's own MRP Wizard output and
  // edits the line items, then reverts it back to Production — who then
  // raises the Batch Intimation as normal. 'add' on 'production_review'
  // gates who can share; 'add' on 'store_review' gates who can revert.
  { workflow: 'requirement', key: 'production_review', label: 'Production — Share with Store', order: 2 },
  { workflow: 'requirement', key: 'store_review',       label: 'Store — Review & Revert',        order: 3 },
  // Rejection Register — a line/shift user counts in-process rejections
  // against a batch for one stage (EBB/Sheet Welding/Moulding/…), records
  // rework, and submits; QA may then edit a submitted entry (the wizard
  // itself is a one-way "submit" once done). Root-cause assignment (which
  // physical/process cause each rejection cause rolls up to) is its own
  // step so it can be handed to a QA lead separately from day-to-day QA
  // editing. 'entry' with 'view' also gates seeing the Analytics screen —
  // there's no separate analytics-only step, since anyone submitting/editing
  // rejections needs to see the same numbers.
  { workflow: 'rejection_register', key: 'entry',         label: 'Rejection Register — Entry (count & submit)',        order: 1 },
  { workflow: 'rejection_register', key: 'qa_edit',        label: 'Rejection Register — QA Edit After Submit',          order: 2 },
  { workflow: 'rejection_register', key: 'causes_setup',   label: 'Rejection Register — Root Cause Setup',              order: 3 },
  // Production Planning — entry step (view/add/edit/delete gate the plain
  // CRUD list, routes/productionPlanning.js) plus a separate approve step:
  // whoever holds 'approve' on production_planning:approve can sign off a
  // plan, stamping Approved By/Date on it (used in the Excel export).
  { workflow: 'production_planning', key: 'entry',   label: 'Production Planning Data Entry', order: 1 },
  { workflow: 'production_planning', key: 'approve', label: 'Production Planning Approval',    order: 2 },
  // Sales Order module (replacement for the msale portal) — order workflow,
  // sample requests and masters. Defined in services/sales/constants.js
  // (also the source of the per-"Sales User Type" default templates).
  ...require('./sales/constants').STEPS,
];

const WORKFLOW_LABELS = {
  work_order:       'Work Order',
  bom:              'BOM Approvals',
  customer_vendor:  'Customer / Vendor Registration',
  project:          'Project Approvals',
  production_order: 'Production Order',
  man_power:        'Man Power',
  oee:              'Overall Equipment Efficiency',
  requirement:      'Requirements',
  rejection_register: 'Rejection Register',
  production_planning: 'Production Planning',
  sales_order:      'Sales Order',
  sales_sample:     'Sample Request (Domestic)',
  sales_export_sample: 'Sample Request (Export)',
  sales_master:     'Sales Masters',
};

async function exec(sqlQuery, params = []) {
  const pool = await getPool();
  const req  = pool.request();
  let i = 0;
  const text = sqlQuery.replace(/\?/g, () => { const n = `p${i}`; req.input(n, params[i]); i++; return `@${n}`; });
  const result = await req.query(text);
  return result.recordset || [];
}

function isAlreadyExists(e) {
  const m = (e.message || '').toLowerCase();
  return m.includes('already exists') || m.includes('there is already an object');
}

async function bootstrap() {
  console.log('[APPROVAL-STEPS] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID          INT IDENTITY(1,1) PRIMARY KEY,
      WORKFLOW    NVARCHAR(50) NOT NULL,
      STEP_KEY    NVARCHAR(50) NOT NULL,
      STEP_LABEL  NVARCHAR(150) NOT NULL,
      STEP_ORDER  INT DEFAULT 1,
      IS_CUSTOM   BIT DEFAULT 0,
      CREATED_BY  NVARCHAR(50),
      CREATED_AT  DATETIME2,
      CONSTRAINT UQ_APPROVAL_STEP UNIQUE (WORKFLOW, STEP_KEY)
    )
  `).catch(e => { if (!isAlreadyExists(e)) throw e; });

  // Seed built-ins (idempotent — skip any that already exist)
  for (const s of BUILTIN_STEPS) {
    await exec(
      `IF NOT EXISTS (SELECT 1 FROM ${TABLE} WHERE WORKFLOW=? AND STEP_KEY=?)
       INSERT INTO ${TABLE} (WORKFLOW, STEP_KEY, STEP_LABEL, STEP_ORDER, IS_CUSTOM, CREATED_AT)
       VALUES (?,?,?,?,0,SYSUTCDATETIME())`,
      [s.workflow, s.key, s.workflow, s.key, s.label, s.order]
    ).catch(e => console.warn('[APPROVAL-STEPS] seed failed for', s.workflow, s.key, e.message));
  }

  // One-time relabel: "Checked By Production" → "Checked By Store In-Charge".
  // The seed loop above only INSERTs when a row is missing, so an
  // already-seeded row from before this rename would otherwise keep
  // showing the old label forever in the admin permission checkboxes. Only
  // touches the built-in row (IS_CUSTOM=0) and only if it still has the old
  // text — never overwrites an admin's own custom step with this key.
  await exec(
    `UPDATE ${TABLE} SET STEP_LABEL=? WHERE WORKFLOW='work_order' AND STEP_KEY='checked_production' AND IS_CUSTOM=0 AND STEP_LABEL=?`,
    ['Checked By Store In-Charge', 'Checked By Production']
  ).catch(e => console.warn('[APPROVAL-STEPS] checked_production relabel failed:', e.message));

  console.log('[APPROVAL-STEPS] ✅ Ready');
}

function fromRow(row) {
  return {
    id:        row.ID,
    workflow:  row.WORKFLOW,
    key:       row.STEP_KEY,
    label:     row.STEP_LABEL,
    order:     row.STEP_ORDER,
    isCustom:  !!row.IS_CUSTOM,
    fullKey:   `${row.WORKFLOW}:${row.STEP_KEY}`,
  };
}

async function listAll() {
  const rows = await exec(`SELECT * FROM ${TABLE} ORDER BY WORKFLOW, STEP_ORDER, ID`);
  return rows.map(fromRow);
}

async function listGrouped() {
  const all = await listAll();
  const workflows = [...new Set(all.map(s => s.workflow))];
  return workflows.map(w => ({
    workflow: w,
    label: WORKFLOW_LABELS[w] || w,
    steps: all.filter(s => s.workflow === w),
  }));
}

// Create a custom step. `workflow` may be an existing one or a brand-new
// custom workflow name (admin-defined, for future use cases).
async function createCustomStep({ workflow, key, label, order, createdBy }) {
  const wf = String(workflow || '').trim().toLowerCase().replace(/[^a-z0-9_]/g, '_');
  const k  = String(key || '').trim().toLowerCase().replace(/[^a-z0-9_]/g, '_');
  if (!wf || !k || !label) throw new Error('workflow, key and label are required');
  await exec(
    `INSERT INTO ${TABLE} (WORKFLOW, STEP_KEY, STEP_LABEL, STEP_ORDER, IS_CUSTOM, CREATED_BY, CREATED_AT)
     VALUES (?,?,?,?,1,?,SYSUTCDATETIME())`,
    [wf, k, label, parseInt(order) || 99, createdBy || null]
  );
  return { workflow: wf, key: k, label, fullKey: `${wf}:${k}` };
}

async function deleteCustomStep(id) {
  await exec(`DELETE FROM ${TABLE} WHERE ID = ? AND IS_CUSTOM = 1`, [parseInt(id)]);
}

module.exports = {
  bootstrap, listAll, listGrouped, createCustomStep, deleteCustomStep,
  WORKFLOW_LABELS, BUILTIN_STEPS,
};