sap-erp/services/sales/orders.js

// services/sales/orders.js
// Sales Order lifecycle — the msale workflow, re-implemented on the app DB:
//
//  Customer order (Trade / Institution in-direct)
//    1 Review ──review──▶ 2 Logistics ──logistics_confirm──▶ 4 Approved ──customer pays──▶ 6 / 7
//    (1 is skipped — straight to 2 — when no special price was requested)
//    1/2 ──▶ 5 Modify ──customer resubmits──▶ 1/2
//    6 ──payment_verify──▶ 7 (or back to 4 if rejected)
//  Direct order (raised by BUH/Tender for a customer, no payment step)
//    2 ──logistics_confirm──▶ 3 ──final_approve──▶ 7 ;  2 ──▶ 5 ──creator resubmits──▶ 2
//  Both: 7 ──sap_post──▶ 8 (SAP Sales Order) ──SAP sync / manual close──▶ 9 ; cancel ▶ 10
//
// Every employee action is checked against the actor's Approval Step perms
// AND their sales divisions / visibility scope (see canSee()).
'use strict';
const { query, one, tx, json, round2 } = require('./db');
const { S, statusLabel, customerStatusLabel } = require('./constants');
const masters = require('./masters');
const sap = require('./sap');
const notify = require('./notify');
const { hasStepPerm } = require('../../middleware/auth');
const { runWithSapUser } = require('../sapServiceLayer');

// ── helpers ─────────────────────────────────────────────────────────────────
function stamp(name) {
  const d = new Date(); const p = n => String(n).padStart(2, '0');
  return `${name} (${p(d.getDate())}-${p(d.getMonth() + 1)}-${d.getFullYear()} ${p(d.getHours())}:${p(d.getMinutes())})`;
}
function appendRemark(old, name, text) {
  const t = String(text || '').trim();
  if (!t) return old || null;
  return (old ? old + '\n\n' : '') + stamp(name) + '\n' + t;
}
function refNo(id, date) {
  const d = date ? new Date(date) : new Date();
  return `${String(d.getMonth() + 1).padStart(2, '0')}${String(d.getFullYear()).slice(-2)}-${String(id).padStart(5, '0')}`;
}
function isEmp(actor) { return actor && actor.type === 'user'; }
function can(actor, key, perm) { return isEmp(actor) && hasStepPerm(actor.user, `sales_order:${key}`, perm); }
function fail(msg, code = 400) { const e = new Error(msg); e.status = code; throw e; }

function orderFromRow(r) {
  if (!r) return null;
  return {
    id: r.ID, refNo: r.REF_NO, company: r.COMPANY, soType: r.SO_TYPE, orderType: r.ORDER_TYPE || (r.SO_TYPE === 1 ? 'TRADE' : 'IN-DIRECT'),
    cardCode: r.CARD_CODE, cardName: r.CARD_NAME, divisionId: r.DIVISION_ID, custOrderNo: r.CUST_ORDER_NO || '',
    orderDate: r.ORDER_DATE, deliveryDate: r.DELIVERY_DATE, contactPerson: r.CONTACT_PERSON || '', salesEmployee: r.SALES_EMPLOYEE || '',
    shipToCode: r.SHIP_TO_CODE || '', shipToText: r.SHIP_TO_TEXT || '', shipState: r.SHIP_STATE || '', billToCode: r.BILL_TO_CODE || '', billToText: r.BILL_TO_TEXT || '',
    taxCode: r.TAX_CODE || '', igstRate: Number(r.IGST_RATE), cgstRate: Number(r.CGST_RATE), sgstRate: Number(r.SGST_RATE), tcsRate: Number(r.TCS_RATE),
    igstVal: Number(r.IGST_VAL), cgstVal: Number(r.CGST_VAL), sgstVal: Number(r.SGST_VAL), tcsVal: Number(r.TCS_VAL),
    subTotal: Number(r.SUB_TOTAL), grandTotal: Number(r.GRAND_TOTAL), creditDays: r.CREDIT_DAYS,
    paymentMode: r.PAYMENT_MODE || '', paymentRef: r.PAYMENT_REF || '', paymentDate: r.PAYMENT_DATE, paymentDetail: r.PAYMENT_DETAIL || '',
    paidAmount: r.PAID_AMOUNT == null ? null : Number(r.PAID_AMOUNT), tdsVal: Number(r.TDS_VAL) || 0, walletUsed: r.WALLET_USED == null ? null : Number(r.WALLET_USED),
    rebate: r.REBATE == null ? null : Number(r.REBATE), paidStatus: r.PAID_STATUS || '',
    customerRemarks: r.CUSTOMER_REMARKS || '', employeeRemarks: r.EMPLOYEE_REMARKS || '', internalRemarks: r.INTERNAL_REMARKS || '',
    status: r.STATUS, statusLabel: statusLabel(r.STATUS, r.ORDER_TYPE), customerStatusLabel: customerStatusLabel(r.STATUS),
    isMultiConsignee: !!r.IS_MULTI_CONSIGNEE, needsReview: !!r.NEEDS_REVIEW,
    sapDocEntry: r.SAP_DOC_ENTRY, sapDocNum: r.SAP_DOC_NUM, sapOrderDate: r.SAP_ORDER_DATE, sapPostedAt: r.SAP_POSTED_AT, sapPostedBy: r.SAP_POSTED_BY || '',
    sapError: r.SAP_ERROR || '', invoices: json(r.INVOICE_JSON, null), lastSapSync: r.LAST_SAP_SYNC,
    createdByType: r.CREATED_BY_TYPE, createdBy: r.CREATED_BY, createdByName: r.CREATED_BY_NAME || '', createdAt: r.CREATED_AT,
    updatedAt: r.UPDATED_AT, updatedByName: r.UPDATED_BY_NAME || '', legacy: !!r.LEGACY,
    orderQty: r.ORDER_QTY != null ? Number(r.ORDER_QTY) : undefined,
  };
}
function itemFromRow(r) {
  return { id: r.ID, lineNo: r.LINE_NO, productId: r.PRODUCT_ID, itemCode: r.ITEM_CODE, displayCode: r.DISPLAY_CODE || r.ITEM_CODE, description: r.DESCRIPTION || '',
    qty: Number(r.QTY), nop: r.NOP, price: Number(r.PRICE), specialPrice: Number(r.SPECIAL_PRICE) || 0, commission: Number(r.COMMISSION) || 0,
    hsn: r.HSN || '', lineTotal: Number(r.LINE_TOTAL) };
}

// ── Tax & pricing (msale rules) ─────────────────────────────────────────────
// Ship-to in the company's own state → CGST+SGST, otherwise IGST. TCS on
// (base + GST) when a TCS rate is configured.
function taxFor(settings, shipState) {
  const intra = String(shipState || '').toUpperCase() === String(settings.companyState || '').toUpperCase();
  return intra
    ? { taxCode: settings.intraTaxCode, igst: 0, cgst: Number(settings.cgstRate) || 0, sgst: Number(settings.sgstRate) || 0, tcs: Number(settings.tcsRate) || 0 }
    : { taxCode: settings.interTaxCode, igst: Number(settings.igstRate) || 0, cgst: 0, sgst: 0, tcs: Number(settings.tcsRate) || 0 };
}
function totals(items, tax) {
  const sub = round2(items.reduce((a, it) => a + it.lineTotal, 0));
  const igst = round2(sub * tax.igst / 100), cgst = round2(sub * tax.cgst / 100), sgst = round2(sub * tax.sgst / 100);
  const tcs = tax.tcs > 0 ? round2((sub + igst + cgst + sgst) * tax.tcs / 100) : 0;
  return { sub, igst, cgst, sgst, tcs, grand: round2(sub + igst + cgst + sgst + tcs) };
}
// Re-price every line from the product master (client prices are never
// trusted). specialPrice > 0 and ≠ list price ⇒ the order needs review;
// the line is billed at the special price (that's what msale posted to SAP).
async function priceLines(lines, divisionId, { allowCommission = false } = {}) {
  if (!Array.isArray(lines) || !lines.length) fail('Please add at least one product');
  const out = []; let needsReview = false;
  for (const [i, l] of lines.entries()) {
    const qty = Number(l.qty);
    if (!(qty > 0)) fail(`Line ${i + 1}: quantity must be greater than 0`);
    const p = await masters.resolveProduct(l.key || (l.productId ? `P:${l.productId}` : `S:${l.itemCode}`), divisionId);
    if (!p) fail(`Line ${i + 1}: product not found or inactive`);
    if (Number(p.divisionId) !== Number(divisionId)) fail(`Line ${i + 1}: ${p.displayCode} does not belong to the selected product category`);
    const sp = Math.max(0, round2(l.specialPrice));
    if (sp > 0 && Math.abs(sp - p.price) > 0.0001) needsReview = true;
    const eff = sp > 0 ? sp : p.price;
    if (!(eff > 0)) fail(`Line ${i + 1}: ${p.displayCode} has no price — enter a special price or ask MIPL to set one`);
    out.push({ productId: p.id, itemCode: p.itemCode, displayCode: p.displayCode, description: p.description, qty, nop: Math.max(1, Math.ceil(qty / (p.packSize || 1))),
      price: p.price, specialPrice: sp, commission: allowCommission ? Math.max(0, Number(l.commission) || 0) : 0, hsn: p.hsn, lineTotal: round2(qty * eff) });
  }
  return { items: out, needsReview };
}

// Validate ship/bill addresses against SAP and build their display text.
async function resolveAddresses(company, cardCode, shipToCode, billToCode) {
  const c = await sap.getCustomer(company, cardCode);
  if (!c) fail('Customer not found in SAP');
  if (c.frozen) fail('This customer is inactive (frozen) in SAP');
  const ship = c.shipTo.find(a => a.code === shipToCode);
  const bill = c.billTo.find(a => a.code === billToCode);
  if (!ship) fail('Please select a valid Ship-To address');
  if (!bill) fail('Please select a valid Bill-To address');
  return { customer: c, ship, bill };
}

// ── Visibility ──────────────────────────────────────────────────────────────
// Employee: needs sales_order:view, the order's division in their profile,
// and their scope (mapped customers / own orders). Customer: own orders only.
async function scopeFor(actor) {
  if (!isEmp(actor)) return null;
  const prof = await masters.getProfile(actor.user);
  let cards = null;
  if (prof.scope === 'mapped') cards = await masters.mappedCardCodesForUser(actor.user.id, actor.user.email || actor.email);
  return { prof, cards };
}
async function canSee(actor, o) {
  if (!o || o.status === S.DELETED) return false;
  if (actor.type === 'customer') return o.cardCode === actor.cardCode;
  if (!can(actor, 'view', 'view')) return false;
  const { prof, cards } = await scopeFor(actor);
  if (!prof.divisions.includes(Number(o.divisionId))) return false;
  if (prof.scope === 'mapped' && !(cards || []).includes(o.cardCode)) return false;
  if (prof.scope === 'own' && !(o.createdByType === 'user' && String(o.createdBy) === String(actor.user.id))) return false;
  return true;
}

// ── Reads ───────────────────────────────────────────────────────────────────
async function getOrderRaw(id, runner) {
  return orderFromRow(await one(`SELECT * FROM dbo.ZSO_ORDERS WHERE ID=?`, [parseInt(id)], runner));
}
async function getItems(id, runner) {
  return (await query(`SELECT * FROM dbo.ZSO_ORDER_ITEMS WHERE ORDER_ID=? AND ACTIVE=1 ORDER BY LINE_NO`, [parseInt(id)], runner)).map(itemFromRow);
}
async function getConsignees(id) {
  return (await query(`SELECT * FROM dbo.ZSO_ORDER_CONSIGNEES WHERE ORDER_ID=? AND ACTIVE=1 ORDER BY ID`, [parseInt(id)]))
    .map(r => ({ id: r.ID, shipToCode: r.SHIP_TO_CODE || '', shipToText: r.SHIP_TO_TEXT || '', seName: r.SE_NAME || '', seEmail: r.SE_EMAIL || '',
      items: json(r.ITEMS_JSON, []), sapDocEntry: r.SAP_DOC_ENTRY, sapDocNum: r.SAP_DOC_NUM }));
}
async function getDocs(entity, id) {
  return (await query(`SELECT * FROM dbo.ZSO_DOCS WHERE ENTITY=? AND ENTITY_ID=? AND ACTIVE=1 ORDER BY ID DESC`, [entity, parseInt(id)]))
    .map(r => ({ id: r.ID, docType: r.DOC_TYPE, title: r.TITLE || '', fileName: r.FILE_NAME, origName: r.ORIG_NAME || '', createdAt: r.CREATED_AT, createdByName: r.CREATED_BY_NAME || '' }));
}
async function getLog(entity, id, { includeInternal = true } = {}) {
  const rows = await query(`SELECT * FROM dbo.ZSO_LOG WHERE ENTITY=? AND ENTITY_ID=? ORDER BY ID`, [entity, parseInt(id)]);
  return rows.map(r => ({ action: r.ACTION, fromStatus: r.FROM_STATUS, toStatus: r.TO_STATUS, remarks: includeInternal || r.ACTION !== 'internal_note' ? (r.REMARKS || '') : '',
    actorType: r.ACTOR_TYPE, actorName: r.ACTOR_NAME || '', at: r.AT })).filter(l => includeInternal || l.action !== 'internal_note');
}
async function log(entity, id, action, from, to, remarks, actor, runner) {
  await query(`INSERT INTO dbo.ZSO_LOG (ENTITY,ENTITY_ID,ACTION,FROM_STATUS,TO_STATUS,REMARKS,ACTOR_TYPE,ACTOR,ACTOR_NAME) VALUES (?,?,?,?,?,?,?,?,?)`,
    [entity, parseInt(id), action, from, to, remarks || null, actor.type, String(actor.type === 'customer' ? actor.cardCode : actor.user.id), actor.name], runner);
}

// Full order for a viewer (customer gets no internal remarks).
async function getOrder(id, actor) {
  const o = await getOrderRaw(id);
  if (!o || !(await canSee(actor, o))) fail('Order not found', 404);
  o.items = await getItems(id);
  o.consignees = await getConsignees(id);
  o.docs = await getDocs('order', id);
  o.log = await getLog('order', id, { includeInternal: isEmp(actor) });
  o.payments = await walletTxnsForOrder(id);
  if (!isEmp(actor)) { delete o.internalRemarks; o.items.forEach(i => { delete i.commission; }); }
  else o.actions = await availableActions(actor, o);
  if (actor.type === 'customer' || o.status === S.APPROVED) o.wallet = await walletSummary(o.cardCode);
  return o;
}

// List with filters; employee lists are scoped (division/scope), customers see their own.
async function listOrders(actor, f = {}) {
  const w = ['o.STATUS<>11']; const p = [];
  if (actor.type === 'customer') { w.push('o.CARD_CODE=?'); p.push(actor.cardCode); }
  else {
    if (!can(actor, 'view', 'view')) fail('You are not assigned "view" on Sales Orders', 403);
    const { prof, cards } = await scopeFor(actor);
    if (!prof.divisions.length) return [];
    w.push(`o.DIVISION_ID IN (${prof.divisions.map(Number).join(',')})`);
    if (prof.scope === 'mapped') { if (!cards.length) return []; w.push(`o.CARD_CODE IN (${cards.map(() => '?').join(',')})`); p.push(...cards); }
    if (prof.scope === 'own') { w.push(`o.CREATED_BY_TYPE='user' AND o.CREATED_BY=?`); p.push(String(actor.user.id)); }
  }
  if (f.soType) { w.push('o.SO_TYPE=?'); p.push(parseInt(f.soType)); }
  if (f.orderType) { w.push('o.ORDER_TYPE=?'); p.push(f.orderType); }
  if (f.divisionId) { w.push('o.DIVISION_ID=?'); p.push(parseInt(f.divisionId)); }
  if (f.statuses) { const st = String(f.statuses).split(',').map(Number).filter(Boolean); if (st.length) w.push(`o.STATUS IN (${st.join(',')})`); }
  if (f.cardCode) { w.push('o.CARD_CODE=?'); p.push(f.cardCode); }
  if (f.from) { w.push('o.CREATED_AT>=?'); p.push(f.from); }
  if (f.to) { w.push('o.CREATED_AT<DATEADD(day,1,CAST(? AS date))'); p.push(f.to); }
  if (f.search) { w.push('(o.REF_NO LIKE ? OR o.CARD_NAME LIKE ? OR o.CARD_CODE LIKE ? OR o.CUST_ORDER_NO LIKE ? OR CAST(o.SAP_DOC_NUM AS nvarchar(20)) LIKE ?)'); const s = `%${f.search}%`; p.push(s, s, s, s, s); }
  const top = Math.min(parseInt(f.limit) || 1000, 5000);
  const rows = await query(`SELECT TOP ${top} o.*, (SELECT SUM(QTY) FROM dbo.ZSO_ORDER_ITEMS i WHERE i.ORDER_ID=o.ID AND i.ACTIVE=1) AS ORDER_QTY
    FROM dbo.ZSO_ORDERS o WHERE ${w.join(' AND ')} ORDER BY o.ID DESC`, p);
  return rows.map(orderFromRow).map(o => { if (!isEmp(actor)) delete o.internalRemarks; return o; });
}

// ── Customer's own order reference ("Your order ref. no.") ──────────────────
// Auto-suggested per customer as <CARDCODE>/<FY>/<0001>, Indian financial
// year (Apr–Mar, e.g. 26-27). The customer may type their own instead.
function finYear(d = new Date()) {
  const y = d.getMonth() >= 3 ? d.getFullYear() : d.getFullYear() - 1;
  return `${String(y).slice(-2)}-${String(y + 1).slice(-2)}`;
}
async function nextCustOrderNo(cardCode, runner) {
  const prefix = `${cardCode}/${finYear()}/`;
  const rows = await query(`SELECT CUST_ORDER_NO FROM dbo.ZSO_ORDERS WITH (UPDLOCK, HOLDLOCK) WHERE CARD_CODE=? AND CUST_ORDER_NO LIKE ?`,
    [cardCode, prefix.replace(/[\[%_]/g, '[$&]') + '%'], runner);
  const max = rows.reduce((m, r) => Math.max(m, parseInt(String(r.CUST_ORDER_NO).slice(prefix.length)) || 0), 0);
  return prefix + String(max + 1).padStart(4, '0');
}
// The same customer may not reuse a reference on another live order.
async function assertCustOrderNoFree(cardCode, no, exceptId, runner) {
  const r = await one(`SELECT TOP 1 REF_NO FROM dbo.ZSO_ORDERS WHERE CARD_CODE=? AND CUST_ORDER_NO=? AND STATUS NOT IN (10,11) AND ID<>?`,
    [cardCode, no, parseInt(exceptId) || 0], runner);
  if (r) fail(`Order ref. no. "${no}" is already used on your order ${r.REF_NO} — please use a different one`);
}

// ── Create ──────────────────────────────────────────────────────────────────
// data: { soType, divisionId, cardCode (employee only), shipToCode, billToCode, custOrderNo, orderDate, deliveryDate,
//         contactPerson, salesEmployee, remarks, lines:[{key,qty,specialPrice,commission}], consignees:[{shipToCode,seName,seEmail,items:[{itemCode,qty}]}],
//         creditDays, paymentDetail }
async function createOrder(actor, data) {
  const settings = await masters.getSettings();
  const direct = isEmp(actor);
  if (direct && !can(actor, 'create_direct', 'add')) fail('You are not assigned "add" on Direct Order creation', 403);
  const soType = direct ? 2 : (parseInt(data.soType) === 2 ? 2 : 1);
  const divisionId = parseInt(data.divisionId);
  const division = await masters.getDivision(divisionId);
  if (!division) fail('Please select a product category');
  if (!division.active) fail(`${division.name} is currently disabled — new orders cannot be placed in this product category`);
  if (direct) { const prof = await masters.getProfile(actor.user); if (!prof.divisions.includes(divisionId)) fail('This product category is not in your divisions', 403); }
  const cardCode = direct ? String(data.cardCode || '').trim() : actor.cardCode;
  if (!cardCode) fail('Please select a customer');
  // Customers: blank or the untouched suggestion → numbered inside the insert
  // transaction (so two simultaneous orders can't take the same number).
  const autoRef = !direct && (!String(data.custOrderNo || '').trim() || !!data.custOrderNoAuto);
  if (!autoRef && !String(data.custOrderNo || '').trim()) fail('Customer reference order no. is required');
  if (!data.deliveryDate) fail('Committed Dispatch Date is required');
  const { customer, ship, bill } = await resolveAddresses(settings.company, cardCode, data.shipToCode, data.billToCode);
  const { items, needsReview } = await priceLines(data.lines, divisionId, { allowCommission: direct });
  const tax = taxFor(settings, ship.state);
  const t = totals(items, tax);
  const consignees = direct && data.isMultiConsignee ? validateConsignees(data.consignees, customer, items) : [];
  const status = direct ? S.LOGISTICS : (needsReview ? S.CREATED : S.LOGISTICS);
  const orderType = direct ? 'DIRECT' : (soType === 2 ? 'IN-DIRECT' : null);
  const remarks = appendRemark(null, actor.name, data.remarks);

  const id = await tx(async (r) => {
    const custOrderNo = autoRef ? await nextCustOrderNo(cardCode, r) : String(data.custOrderNo).trim();
    if (!direct) await assertCustOrderNoFree(cardCode, custOrderNo, 0, r);
    const row = await one(`INSERT INTO dbo.ZSO_ORDERS (COMPANY,SO_TYPE,ORDER_TYPE,CARD_CODE,CARD_NAME,DIVISION_ID,CUST_ORDER_NO,ORDER_DATE,DELIVERY_DATE,CONTACT_PERSON,
        SALES_EMPLOYEE,SHIP_TO_CODE,SHIP_TO_TEXT,SHIP_STATE,BILL_TO_CODE,BILL_TO_TEXT,TAX_CODE,IGST_RATE,CGST_RATE,SGST_RATE,TCS_RATE,IGST_VAL,CGST_VAL,SGST_VAL,TCS_VAL,
        SUB_TOTAL,GRAND_TOTAL,CREDIT_DAYS,PAYMENT_DETAIL,CUSTOMER_REMARKS,STATUS,IS_MULTI_CONSIGNEE,NEEDS_REVIEW,CREATED_BY_TYPE,CREATED_BY,CREATED_BY_NAME)
      OUTPUT INSERTED.ID, INSERTED.CREATED_AT VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
    [settings.company, soType, orderType, cardCode, customer.cardName, divisionId, custOrderNo, data.orderDate || null, data.deliveryDate,
      data.contactPerson || customer.contactPerson || '', data.salesEmployee || customer.slpName || '', ship.code, ship.text, ship.state, bill.code, bill.text,
      tax.taxCode, tax.igst, tax.cgst, tax.sgst, tax.tcs, t.igst, t.cgst, t.sgst, t.tcs, t.sub, t.grand,
      direct ? (parseInt(data.creditDays) || null) : null, direct ? (data.paymentDetail || null) : null, remarks, status, consignees.length > 0, needsReview,
      actor.type, String(actor.type === 'customer' ? actor.cardCode : actor.user.id), actor.name], r);
    const newId = row.ID;
    await query(`UPDATE dbo.ZSO_ORDERS SET REF_NO=? WHERE ID=?`, [refNo(newId, row.CREATED_AT), newId], r);
    await insertItems(newId, items, r);
    for (const c of consignees) {
      await query(`INSERT INTO dbo.ZSO_ORDER_CONSIGNEES (ORDER_ID,SHIP_TO_CODE,SHIP_TO_TEXT,SE_NAME,SE_EMAIL,ITEMS_JSON) VALUES (?,?,?,?,?,?)`,
        [newId, c.shipToCode, c.shipToText, c.seName, c.seEmail, JSON.stringify(c.items)], r);
    }
    await log('order', newId, 'created', null, status, data.remarks, actor, r);
    return newId;
  });
  const o = await getOrderRaw(id);
  notify.orderEvent('created', o);
  return o;
}
async function insertItems(orderId, items, r) {
  for (const [i, it] of items.entries()) {
    await query(`INSERT INTO dbo.ZSO_ORDER_ITEMS (ORDER_ID,LINE_NO,PRODUCT_ID,ITEM_CODE,DISPLAY_CODE,DESCRIPTION,QTY,NOP,PRICE,SPECIAL_PRICE,COMMISSION,HSN,LINE_TOTAL)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`, [orderId, i + 1, it.productId || null, it.itemCode, it.displayCode, it.description, it.qty, it.nop, it.price, it.specialPrice,
      it.commission, it.hsn, it.lineTotal], r);
  }
}
// Multi-consignee (Direct orders): each consignee is one of the customer's
// ship-to addresses with an allocation of the order's items; allocations
// must add up exactly to the ordered qty per item.
function validateConsignees(list, customer, items) {
  if (!Array.isArray(list) || !list.length) fail('Add at least one consignee or untick multi-consignee');
  const alloc = {};
  const out = list.map((c, i) => {
    const ship = customer.shipTo.find(a => a.code === c.shipToCode);
    if (!ship) fail(`Consignee ${i + 1}: select a valid ship-to address`);
    const its = (c.items || []).filter(x => Number(x.qty) > 0).map(x => ({ itemCode: x.itemCode, qty: Number(x.qty) }));
    its.forEach(x => { alloc[x.itemCode] = (alloc[x.itemCode] || 0) + x.qty; });
    return { shipToCode: ship.code, shipToText: ship.text, seName: c.seName || '', seEmail: c.seEmail || '', items: its };
  });
  for (const it of items) {
    if (Math.abs((alloc[it.itemCode] || 0) - it.qty) > 0.0001) fail(`Consignee allocation for ${it.displayCode} (${alloc[it.itemCode] || 0}) must equal the ordered qty (${it.qty})`);
  }
  return out;
}

// ── Modify & resubmit (status 5) ────────────────────────────────────────────
// Customer for its own orders; for Direct orders, a user with create_direct:edit.
async function resubmit(actor, id, data) {
  const settings = await masters.getSettings();
  const o = await getOrderRaw(id);
  if (!o || !(await canSee(actor, o))) fail('Order not found', 404);
  if (o.status !== S.MODIFY) fail('Only orders sent back for modification can be resubmitted');
  const direct = o.orderType === 'DIRECT';
  if (direct ? !can(actor, 'create_direct', 'edit') : actor.type !== 'customer') fail('You cannot modify this order', 403);
  const shipTo = data.shipToCode || o.shipToCode, billTo = data.billToCode || o.billToCode;
  const { customer, ship, bill } = await resolveAddresses(settings.company, o.cardCode, shipTo, billTo);
  const { items, needsReview } = await priceLines(data.lines, o.divisionId, { allowCommission: direct });
  const tax = taxFor(settings, ship.state);
  const t = totals(items, tax);
  const consignees = direct && o.isMultiConsignee ? validateConsignees(data.consignees, customer, items) : null;
  const status = direct ? S.LOGISTICS : (needsReview ? S.CREATED : S.LOGISTICS);
  if (!direct && String(data.custOrderNo || '').trim() && String(data.custOrderNo).trim() !== o.custOrderNo) await assertCustOrderNoFree(o.cardCode, String(data.custOrderNo).trim(), o.id);
  await tx(async (r) => {
    await query(`UPDATE dbo.ZSO_ORDERS SET SHIP_TO_CODE=?, SHIP_TO_TEXT=?, SHIP_STATE=?, BILL_TO_CODE=?, BILL_TO_TEXT=?, TAX_CODE=?, IGST_RATE=?, CGST_RATE=?, SGST_RATE=?, TCS_RATE=?,
      IGST_VAL=?, CGST_VAL=?, SGST_VAL=?, TCS_VAL=?, SUB_TOTAL=?, GRAND_TOTAL=?, DELIVERY_DATE=COALESCE(?,DELIVERY_DATE), CUST_ORDER_NO=COALESCE(?,CUST_ORDER_NO),
      CUSTOMER_REMARKS=?, STATUS=?, NEEDS_REVIEW=?, UPDATED_AT=SYSDATETIME(), UPDATED_BY_NAME=? WHERE ID=? AND STATUS=5`,
    [ship.code, ship.text, ship.state, bill.code, bill.text, tax.taxCode, tax.igst, tax.cgst, tax.sgst, tax.tcs, t.igst, t.cgst, t.sgst, t.tcs, t.sub, t.grand,
      data.deliveryDate || null, data.custOrderNo || null, appendRemark(o.customerRemarks, actor.name, data.remarks), status, needsReview, actor.name, o.id], r);
    await query(`UPDATE dbo.ZSO_ORDER_ITEMS SET ACTIVE=0 WHERE ORDER_ID=?`, [o.id], r);
    await insertItems(o.id, items, r);
    if (consignees) {
      await query(`UPDATE dbo.ZSO_ORDER_CONSIGNEES SET ACTIVE=0 WHERE ORDER_ID=?`, [o.id], r);
      for (const c of consignees) await query(`INSERT INTO dbo.ZSO_ORDER_CONSIGNEES (ORDER_ID,SHIP_TO_CODE,SHIP_TO_TEXT,SE_NAME,SE_EMAIL,ITEMS_JSON) VALUES (?,?,?,?,?,?)`,
        [o.id, c.shipToCode, c.shipToText, c.seName, c.seEmail, JSON.stringify(c.items)], r);
    }
    await log('order', o.id, 'resubmitted', S.MODIFY, status, data.remarks, actor, r);
  });
  const fresh = await getOrderRaw(o.id);
  notify.orderEvent(status === S.LOGISTICS ? 'to_logistics' : 'created', fresh);
  return fresh;
}

// ── Employee workflow actions ───────────────────────────────────────────────
// Each: which status it applies to, which step/perm it needs, where it goes,
// and which remark fields are mandatory (msale validation rules).
const ACTIONS = {
  send_to_logistics:  { from: [S.CREATED], step: 'review', perm: 'approve', to: () => S.LOGISTICS, label: 'Send to Logistics for confirmation', need: [] },
  review_modify:      { from: [S.CREATED], step: 'review', perm: 'approve', to: () => S.MODIFY, label: 'Send back to customer for modification', need: ['employeeRemarks'] },
  logistics_confirm:  { from: [S.LOGISTICS], step: 'logistics_confirm', perm: 'approve', to: o => (o.orderType === 'DIRECT' ? S.QTY_CONFIRMED : S.APPROVED), label: 'Confirm quantity & dispatch time', need: ['internalRemarks'] },
  logistics_modify:   { from: [S.LOGISTICS], step: 'logistics_confirm', perm: 'approve', to: () => S.MODIFY, label: 'Send back for modification', need: ['employeeRemarks'] },
  final_approve:      { from: [S.QTY_CONFIRMED], step: 'final_approve', perm: 'approve', to: o => (o.orderType === 'DIRECT' ? S.READY_FOR_SAP : S.APPROVED), label: 'Approve (order to be placed)', need: [] },
  payment_approve:    { from: [S.PAYMENT_SUBMITTED], step: 'payment_verify', perm: 'approve', to: () => S.READY_FOR_SAP, label: 'Payment received', need: [] },
  payment_reject:     { from: [S.PAYMENT_SUBMITTED], step: 'payment_verify', perm: 'approve', to: () => S.APPROVED, label: 'Reject payment (back to customer)', need: ['employeeRemarks'] },
  close:              { from: [S.IN_SAP], step: 'sap_post', perm: 'approve', to: () => S.CLOSED, label: 'Close order', need: ['internalRemarks'] },
  cancel:             { from: [S.CREATED, S.LOGISTICS, S.QTY_CONFIRMED, S.APPROVED, S.MODIFY, S.PAYMENT_SUBMITTED, S.READY_FOR_SAP], step: 'cancel', perm: 'delete', to: () => S.CANCELLED, label: 'Cancel order', need: ['employeeRemarks', 'internalRemarks'] },
};
async function availableActions(actor, o) {
  if (!isEmp(actor)) return [];
  const out = Object.entries(ACTIONS).filter(([, a]) => a.from.includes(o.status) && can(actor, a.step, a.perm))
    .map(([k, a]) => ({ key: k, label: a.label, need: a.need, to: a.to(o) }));
  if (o.status === S.READY_FOR_SAP && can(actor, 'sap_post', 'approve')) out.unshift({ key: 'sap_post', label: 'Post to SAP', need: [], to: S.IN_SAP });
  if (o.status === S.MODIFY && o.orderType === 'DIRECT' && can(actor, 'create_direct', 'edit')) out.unshift({ key: 'resubmit', label: 'Modify & resubmit', need: [], to: S.LOGISTICS });
  return out;
}

async function act(actor, id, action, data = {}) {
  if (action === 'sap_post') return postToSap(actor, id);
  const a = ACTIONS[action];
  if (!a) fail('Unknown action');
  const o = await getOrderRaw(id);
  if (!o || !(await canSee(actor, o))) fail('Order not found', 404);
  if (!can(actor, a.step, a.perm)) fail(`You are not assigned "${a.perm}" on sales_order:${a.step}`, 403);
  if (!a.from.includes(o.status)) fail(`This action is not allowed while the order is "${statusLabel(o.status, o.orderType)}"`);
  for (const f of a.need) if (!String(data[f] || '').trim()) fail(`${f === 'employeeRemarks' ? 'Remarks (visible to customer)' : 'Internal remarks'} are required`);
  const to = a.to(o);
  await tx(async (r) => {
    const sets = ['STATUS=?', 'EMPLOYEE_REMARKS=?', 'INTERNAL_REMARKS=?', 'UPDATED_AT=SYSDATETIME()', 'UPDATED_BY_NAME=?'];
    const vals = [to, appendRemark(o.employeeRemarks, actor.name, data.employeeRemarks), appendRemark(o.internalRemarks, actor.name, data.internalRemarks), actor.name];
    if (action === 'final_approve' && o.orderType === 'DIRECT' && data.creditDays != null) { sets.push('CREDIT_DAYS=?', 'PAYMENT_DETAIL=?'); vals.push(parseInt(data.creditDays) || null, data.paymentDetail || null); }
    const upd = await query(`UPDATE dbo.ZSO_ORDERS SET ${sets.join(', ')} OUTPUT INSERTED.ID WHERE ID=? AND STATUS=?`, [...vals, o.id, o.status], r);
    if (!upd.length) fail('The order was changed by someone else — please reload', 409);
    if (action === 'payment_approve') await settlePayment(o, 'approve', actor, r);
    if (action === 'payment_reject') await settlePayment(o, 'reject', actor, r);
    if (action === 'cancel') await reverseOrderDebit(o, actor, r);
    await log('order', o.id, action, o.status, to, [data.employeeRemarks, data.internalRemarks].filter(Boolean).join(' | '), actor, r);
  });
  const fresh = await getOrderRaw(o.id);
  const email = await customerEmail(fresh);
  const ev = { send_to_logistics: 'to_logistics', review_modify: 'modify', logistics_modify: 'modify', payment_approve: 'payment_received', payment_reject: 'payment_rejected', cancel: 'cancelled' }[action]
    || (to === S.APPROVED ? 'approved' : to === S.QTY_CONFIRMED ? 'qty_confirmed' : to === S.READY_FOR_SAP ? 'ready_for_sap' : null);
  if (ev) notify.orderEvent(ev, fresh, { customerEmail: email, remarks: data.employeeRemarks });
  if (to === S.READY_FOR_SAP) maybeAutoPost(fresh.id);
  return fresh;
}

async function customerEmail(o) {
  const acc = await masters.getCustomerAccount(o.cardCode);
  if (acc && acc.email) return acc.email;
  try { const c = await sap.getCustomer(o.company, o.cardCode); return (c && c.email) || ''; } catch (_e) { return ''; }
}

// Customer-side cancel (allowed until payment is submitted, as in msale).
async function customerCancel(actor, id, remarks) {
  const o = await getOrderRaw(id);
  if (!o || o.cardCode !== actor.cardCode) fail('Order not found', 404);
  if (![S.CREATED, S.LOGISTICS, S.QTY_CONFIRMED, S.APPROVED, S.MODIFY].includes(o.status)) fail('This order can no longer be cancelled online — please contact MIPL');
  if (!String(remarks || '').trim()) fail('Please enter a reason for cancelling');
  await tx(async (r) => {
    const upd = await query(`UPDATE dbo.ZSO_ORDERS SET STATUS=10, CUSTOMER_REMARKS=?, UPDATED_AT=SYSDATETIME(), UPDATED_BY_NAME=? OUTPUT INSERTED.ID WHERE ID=? AND STATUS=?`,
      [appendRemark(o.customerRemarks, actor.name, remarks), actor.name, o.id, o.status], r);
    if (!upd.length) fail('The order was changed — please reload', 409);
    await reverseOrderDebit(o, actor, r);
    await log('order', o.id, 'cancel', o.status, S.CANCELLED, remarks, actor, r);
  });
  const fresh = await getOrderRaw(o.id);
  notify.orderEvent('cancelled', fresh, { customerEmail: await customerEmail(fresh), remarks });
  return fresh;
}

// ── Wallet ──────────────────────────────────────────────────────────────────
// The wallet is a pure ledger: balance = approved credits − approved debits.
// No separate balance column that can drift (msale's customer_wallet did).
// An order's debit is (grand total − TDS deducted by the customer).
function txnFromRow(r) {
  return { id: r.ID, cardCode: r.CARD_CODE, type: r.TXN_TYPE, amount: Number(r.AMOUNT), tds: Number(r.TDS) || 0, status: r.STATUS, refType: r.REF_TYPE, orderId: r.ORDER_ID,
    paymentMode: r.PAYMENT_MODE || '', paymentRef: r.PAYMENT_REF || '', paymentDate: r.PAYMENT_DATE, paymentDetail: r.PAYMENT_DETAIL || '', remarks: r.REMARKS || '',
    bankApiId: r.BANK_API_ID || '', docId: r.DOC_ID, createdAt: r.CREATED_AT, createdByName: r.CREATED_BY_NAME || '', decidedAt: r.DECIDED_AT, decidedByName: r.DECIDED_BY_NAME || '',
    refNo: r.REF_NO || null };
}
async function walletBalance(cardCode, runner) {
  const r = await one(`SELECT COALESCE(SUM(CASE WHEN TXN_TYPE='credit' THEN AMOUNT ELSE -AMOUNT END),0) AS BAL FROM dbo.ZSO_WALLET_TXN WITH (UPDLOCK, HOLDLOCK)
    WHERE CARD_CODE=? AND STATUS='approved'`, [cardCode], runner);
  return round2(r ? r.BAL : 0);
}
async function walletSummary(cardCode) {
  const bal = await walletBalance(cardCode);
  const pend = await one(`SELECT COALESCE(SUM(CASE WHEN TXN_TYPE='credit' THEN AMOUNT ELSE 0 END),0) AS PC, COALESCE(SUM(CASE WHEN TXN_TYPE='debit' THEN AMOUNT ELSE 0 END),0) AS PD
    FROM dbo.ZSO_WALLET_TXN WHERE CARD_CODE=? AND STATUS='pending'`, [cardCode]);
  const acc = await masters.getCustomerAccount(cardCode);
  return { balance: bal, pendingCredits: round2(pend.PC), pendingDebits: round2(pend.PD), creditLimit: acc ? acc.creditLimit : 0 };
}
async function walletTxnsForOrder(orderId) {
  return (await query(`SELECT * FROM dbo.ZSO_WALLET_TXN WHERE ORDER_ID=? ORDER BY ID`, [parseInt(orderId)])).map(txnFromRow);
}
async function ledger(cardCode, { from, to } = {}) {
  const w = ['t.CARD_CODE=?']; const p = [cardCode];
  if (from) { w.push('t.CREATED_AT>=?'); p.push(from); }
  if (to) { w.push('t.CREATED_AT<DATEADD(day,1,CAST(? AS date))'); p.push(to); }
  const rows = await query(`SELECT t.*, o.REF_NO FROM dbo.ZSO_WALLET_TXN t LEFT JOIN dbo.ZSO_ORDERS o ON o.ID=t.ORDER_ID WHERE ${w.join(' AND ')} ORDER BY t.ID`, p);
  let run = 0;
  if (from) {
    const ob = await one(`SELECT COALESCE(SUM(CASE WHEN TXN_TYPE='credit' THEN AMOUNT ELSE -AMOUNT END),0) AS B FROM dbo.ZSO_WALLET_TXN WHERE CARD_CODE=? AND STATUS='approved' AND CREATED_AT<?`, [cardCode, from]);
    run = round2(ob.B);
  }
  const opening = run;
  const lines = rows.map(txnFromRow).map(t => {
    if (t.status === 'approved') run = round2(run + (t.type === 'credit' ? t.amount : -t.amount));
    return { ...t, runningBalance: run };
  });
  return { opening, closing: run, lines };
}

async function duplicatePaymentRef(paymentRef, cardCode, runner) {
  const r = await one(`SELECT TOP 1 ID FROM dbo.ZSO_WALLET_TXN WHERE TXN_TYPE='credit' AND STATUS IN ('pending','approved') AND PAYMENT_REF=? AND CARD_CODE=?`,
    [String(paymentRef).trim(), cardCode], runner);
  return !!r;
}

// Optional bank auto-match (msale called sapapi/payment_details/check_payment).
async function bankMatch(settings, p) {
  if (!settings.bankApiUrl) return null;
  try {
    const body = new URLSearchParams({ cust_code: p.cardCode, payment_ref_no: p.paymentRef, payment_mode: p.paymentMode, payment_date: p.paymentDate, paid_amount: String(p.amount) });
    const r = await fetch(settings.bankApiUrl, { method: 'POST', headers: { 'MS-API-KEY': settings.bankApiKey || '', 'Content-Type': 'application/x-www-form-urlencoded' }, body, signal: AbortSignal.timeout(15000) });
    if (r.status !== 200) return null;
    const d = await r.json().catch(() => ({}));
    return d && d.id ? String(d.id) : 'matched';
  } catch (e) { console.warn('[SALES] bank match failed:', e.message); return null; }
}

// Customer pays an approved order (status 4).
// data: { paidAmount, tds, paymentMode, paymentRef, paymentDate, paymentDetail, remarks, docId }
async function submitPayment(actor, id, data) {
  const settings = await masters.getSettings();
  const o = await getOrderRaw(id);
  if (!o || o.cardCode !== actor.cardCode) fail('Order not found', 404);
  if (o.status !== S.APPROVED) fail('Payment can only be made for an approved order');
  const tds = Math.max(0, round2(data.tds));
  if (tds >= o.grandTotal) fail('TDS cannot exceed the order value');
  const paid = Math.max(0, round2(data.paidAmount));
  if (paid > 0) {
    if (!data.paymentMode || !String(data.paymentRef || '').trim() || !data.paymentDate) fail('Payment mode, reference no. and payment date are required');
    if (new Date(data.paymentDate) > new Date()) fail('Payment date cannot be in the future');
  }
  let result;
  await tx(async (r) => {
    const bal = await walletBalance(o.cardCode, r);
    const need = round2(o.grandTotal - tds);
    const payable = round2(Math.max(0, need - bal));
    const shortfall = round2(payable - paid);
    if (shortfall > Number(settings.roundingTolerance || 0)) fail(`Payable amount is ₹ ${payable.toFixed(2)} — please pay at least that (wallet balance ₹ ${bal.toFixed(2)} already adjusted)`);
    if (paid > 0 && await duplicatePaymentRef(data.paymentRef, o.cardCode, r)) fail('This payment reference has already been used. If the amount is not credited, please contact MIPL.');
    const rebate = shortfall > 0 ? shortfall : 0;     // msale "Recede" rounding write-off
    const debit = round2(need - rebate);
    let creditId = null;
    if (paid > 0) {
      const c = await one(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,STATUS,REF_TYPE,ORDER_ID,PAYMENT_MODE,PAYMENT_REF,PAYMENT_DATE,PAYMENT_DETAIL,DOC_ID,CREATED_BY_NAME)
        OUTPUT INSERTED.ID VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`, [o.cardCode, 'credit', paid, 'pending', 'topup', o.id, data.paymentMode, String(data.paymentRef).trim(), data.paymentDate,
        data.paymentDetail || '', parseInt(data.docId) || null, actor.name], r);
      creditId = c.ID;
    }
    const covered = paid === 0;   // wallet alone covers it
    await query(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,TDS,STATUS,REF_TYPE,ORDER_ID,CREATED_BY_NAME,DECIDED_AT,DECIDED_BY_NAME)
      VALUES (?,?,?,?,?,?,?,?,?,?)`, [o.cardCode, 'debit', debit, tds, covered ? 'approved' : 'pending', 'order', o.id, actor.name, covered ? new Date() : null, covered ? 'Auto (wallet)' : null], r);
    const status = covered ? S.READY_FOR_SAP : S.PAYMENT_SUBMITTED;
    const paidStatus = covered ? 'Already Paid' : (paid > payable + 0.01 ? 'Exceed' : rebate > 0 ? 'Recede' : (bal > 0 ? 'Adjust Payment' : 'Full Paid'));
    await query(`UPDATE dbo.ZSO_ORDERS SET STATUS=?, PAID_AMOUNT=?, TDS_VAL=?, WALLET_USED=?, REBATE=?, PAID_STATUS=?, PAYMENT_MODE=?, PAYMENT_REF=?, PAYMENT_DATE=?, PAYMENT_DETAIL=?,
      CUSTOMER_REMARKS=?, UPDATED_AT=SYSDATETIME(), UPDATED_BY_NAME=? WHERE ID=?`,
    [status, paid, tds, round2(Math.min(bal, need)), rebate || null, paidStatus, paid > 0 ? data.paymentMode : null, paid > 0 ? String(data.paymentRef).trim() : null,
      paid > 0 ? data.paymentDate : null, data.paymentDetail || null, appendRemark(o.customerRemarks, actor.name, data.remarks), actor.name, o.id], r);
    await log('order', o.id, 'payment', S.APPROVED, status, `Paid ₹ ${paid.toFixed(2)}${tds ? `, TDS ₹ ${tds.toFixed(2)}` : ''}${data.paymentRef ? ` (ref ${data.paymentRef})` : ''}`, actor, r);
    result = { status, creditId };
  });
  // Bank auto-match: approve straight away when the bank confirms the credit.
  if (result.creditId) {
    const match = await bankMatch(settings, { cardCode: o.cardCode, paymentRef: data.paymentRef, paymentMode: data.paymentMode, paymentDate: data.paymentDate, amount: round2(data.paidAmount) });
    if (match) {
      await tx(async (r) => {
        await query(`UPDATE dbo.ZSO_WALLET_TXN SET BANK_API_ID=? WHERE ID=?`, [match, result.creditId], r);
        await query(`UPDATE dbo.ZSO_ORDERS SET STATUS=7, UPDATED_AT=SYSDATETIME() WHERE ID=? AND STATUS=6`, [o.id], r);
        await settlePayment(o, 'approve', { type: 'user', name: 'Bank auto-match', user: { id: 0 } }, r);
        await log('order', o.id, 'payment_auto_matched', S.PAYMENT_SUBMITTED, S.READY_FOR_SAP, `Bank ref ${match}`, { type: 'user', name: 'Bank auto-match', user: { id: 0 } }, r);
      });
    }
  }
  const fresh = await getOrderRaw(o.id);
  notify.orderEvent(fresh.status === S.READY_FOR_SAP ? 'payment_received' : 'payment_submitted', fresh, { customerEmail: await customerEmail(fresh) });
  if (fresh.status === S.READY_FOR_SAP) { notify.orderEvent('ready_for_sap', fresh); maybeAutoPost(fresh.id); }
  return fresh;
}

// Approve/reject every pending wallet txn of an order (Accounts decision).
async function settlePayment(o, decision, actor, r) {
  const pend = await query(`SELECT * FROM dbo.ZSO_WALLET_TXN WHERE ORDER_ID=? AND STATUS='pending'`, [o.id], r);
  for (const t of pend) {
    const st = decision === 'approve' ? 'approved' : (t.TXN_TYPE === 'credit' ? 'rejected' : 'cancelled');
    await query(`UPDATE dbo.ZSO_WALLET_TXN SET STATUS=?, DECIDED_AT=SYSDATETIME(), DECIDED_BY_NAME=? WHERE ID=?`, [st, actor.name, t.ID], r);
  }
  if (decision === 'reject') {
    await query(`UPDATE dbo.ZSO_ORDERS SET PAID_AMOUNT=NULL, WALLET_USED=NULL, REBATE=NULL, PAID_STATUS='Payment Rejected' WHERE ID=?`, [o.id], r);
  }
}
// On cancel: an approved order debit is reversed (money returns to the
// wallet); pending ones are cancelled; a still-pending top-up stays pending
// so Accounts can still verify the money that was actually sent.
async function reverseOrderDebit(o, actor, r) {
  await query(`UPDATE dbo.ZSO_WALLET_TXN SET STATUS='cancelled', DECIDED_AT=SYSDATETIME(), DECIDED_BY_NAME=?, REMARKS=CONCAT(COALESCE(REMARKS,''),' Order cancelled')
    WHERE ORDER_ID=? AND TXN_TYPE='debit' AND STATUS='pending'`, [actor.name, o.id], r);
  const appr = await query(`SELECT ID, AMOUNT FROM dbo.ZSO_WALLET_TXN WHERE ORDER_ID=? AND TXN_TYPE='debit' AND STATUS='approved'`, [o.id], r);
  for (const d of appr) {
    await query(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,STATUS,REF_TYPE,ORDER_ID,REMARKS,CREATED_BY_NAME,DECIDED_AT,DECIDED_BY_NAME)
      VALUES (?,?,?,?,?,?,?,?,SYSDATETIME(),?)`, [o.cardCode, 'credit', Number(d.AMOUNT), 'approved', 'refund', o.id, `Refund — order ${o.refNo} cancelled`, actor.name, actor.name], r);
  }
}

// Pending top-ups awaiting Accounts (both order payments and on-account).
async function pendingPayments(actor) {
  if (!can(actor, 'payment_verify', 'view') && !can(actor, 'payment_entry', 'view')) fail('Not allowed', 403);
  const rows = await query(`SELECT t.*, o.REF_NO, o.CARD_NAME, o.GRAND_TOTAL, o.STATUS AS ORDER_STATUS, o.DIVISION_ID FROM dbo.ZSO_WALLET_TXN t
    LEFT JOIN dbo.ZSO_ORDERS o ON o.ID=t.ORDER_ID WHERE t.STATUS='pending' AND t.TXN_TYPE='credit' ORDER BY t.ID`);
  const prof = (await scopeFor(actor)).prof;
  return rows.filter(r => !r.DIVISION_ID || prof.divisions.includes(Number(r.DIVISION_ID)))
    .map(r => ({ ...txnFromRow(r), cardName: r.CARD_NAME || '', orderRef: r.REF_NO || '', orderTotal: r.GRAND_TOTAL == null ? null : Number(r.GRAND_TOTAL), orderStatus: r.ORDER_STATUS }));
}
// On-account payment / adjustment entered by Accounts (msale payment_entry).
async function addOnAccountPayment(actor, data) {
  if (!can(actor, 'payment_entry', 'add')) fail('You are not assigned "add" on sales_order:payment_entry', 403);
  const amt = round2(data.amount);
  if (!data.cardCode) fail('Customer is required');
  if (!(amt > 0)) fail('Amount must be greater than 0');
  if (!data.paymentMode || !String(data.paymentRef || '').trim() || !data.paymentDate) fail('Payment mode, reference and date are required');
  if (!String(data.paymentDetail || '').trim()) fail('Payment remarks are required');
  const type = data.type === 'debit' ? 'debit' : 'credit';
  return tx(async (r) => {
    if (type === 'credit' && await duplicatePaymentRef(data.paymentRef, data.cardCode, r)) fail('This payment reference has already been used for this customer');
    const row = await one(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,STATUS,REF_TYPE,PAYMENT_MODE,PAYMENT_REF,PAYMENT_DATE,PAYMENT_DETAIL,REMARKS,CREATED_BY_NAME,DECIDED_AT,DECIDED_BY_NAME)
      OUTPUT INSERTED.ID VALUES (?,?,?,?,?,?,?,?,?,?,?,SYSDATETIME(),?)`,
    [data.cardCode, type, amt, 'approved', type === 'credit' ? 'topup' : 'adjustment', data.paymentMode, String(data.paymentRef).trim(), data.paymentDate,
      data.paymentDetail, data.remarks || '', actor.name, actor.name], r);
    return row.ID;
  });
}
// Accounts decision on a standalone pending top-up (not tied to an order).
async function decideTopup(actor, txnId, decision, remarks) {
  if (!can(actor, 'payment_verify', 'approve')) fail('You are not assigned "approve" on sales_order:payment_verify', 403);
  const t = await one(`SELECT * FROM dbo.ZSO_WALLET_TXN WHERE ID=?`, [parseInt(txnId)]);
  if (!t || t.STATUS !== 'pending' || t.TXN_TYPE !== 'credit') fail('Payment not found or already processed');
  if (t.ORDER_ID) {
    const o = await getOrderRaw(t.ORDER_ID);
    if (o && o.status === S.PAYMENT_SUBMITTED) return act(actor, o.id, decision === 'approve' ? 'payment_approve' : 'payment_reject', { employeeRemarks: remarks || (decision === 'approve' ? '' : 'Payment not verified') });
  }
  await query(`UPDATE dbo.ZSO_WALLET_TXN SET STATUS=?, DECIDED_AT=SYSDATETIME(), DECIDED_BY_NAME=?, REMARKS=? WHERE ID=? AND STATUS='pending'`,
    [decision === 'approve' ? 'approved' : 'rejected', actor.name, remarks || t.REMARKS || '', t.ID]);
  return { ok: true };
}

// ── SAP posting ─────────────────────────────────────────────────────────────
// The user's own SAP Service-Layer login for `company` (Profile → My SAP Account).
function sapCtxFor(actor, company) {
  const entry = actor && actor.user && actor.user.sapLogins ? actor.user.sapLogins[company] : null;
  if (!entry || !entry.user || !entry.pwdEnc) {
    fail(`You have no SAP login saved for company "${company}". Open Profile → My SAP Account, add your SAP User ID & Password for this company, then log out and back in (or refresh) and try again.`, 400);
  }
  return { sapUser: entry.user, sapPassword: require('../cryptoUtil').decrypt(entry.pwdEnc) };
}
async function postToSap(actor, id, { system = false } = {}) {
  const settings = await masters.getSettings();
  const o = await getOrderRaw(id);
  if (!o) fail('Order not found', 404);
  if (!system) {
    if (!(await canSee(actor, o))) fail('Order not found', 404);
    if (!can(actor, 'sap_post', 'approve')) fail('You are not assigned "approve" on sales_order:sap_post', 403);
  }
  if (o.status !== S.READY_FOR_SAP) fail('Only orders that are ready for SAP can be posted');
  // Guard against double posting: link an existing SAP order instead.
  const existing = (await sap.sapOrdersByWebNo(o.company, [o.id])).filter(x => !x.cancelled);
  let results;
  if (existing.length) {
    results = existing.map(x => ({ docEntry: x.docEntry, docNum: x.docNum, docDate: x.docDate, linked: true }));
  } else {
    const items = await getItems(o.id);
    const consignees = await getConsignees(o.id);
    const division = await masters.getDivision(o.divisionId);
    const payloads = sap.buildSapPayloads(o, items, consignees, settings, division);
    try {
      // Act as the posting user in SAP, using THEIR login for the ORDER's
      // company (from the JWT's per-company sapLogins map). Decided here from
      // the order itself — not from a `company` field the browser may or may
      // not send (the global SAP-context middleware needs that, and silently
      // degrades to "no SAP login" without it). System auto-post keeps the
      // shared .env account (runWithSapUser(null) in maybeAutoPost).
      results = system ? await sap.postOrderToSap(o.company, payloads)
        : await runWithSapUser(sapCtxFor(actor, o.company), () => sap.postOrderToSap(o.company, payloads));
    } catch (e) {
      await query(`UPDATE dbo.ZSO_ORDERS SET SAP_ERROR=?, UPDATED_AT=SYSDATETIME() WHERE ID=?`, [e.message, o.id]);
      await log('order', o.id, 'sap_post_failed', o.status, o.status, e.message, actor);
      throw e;
    }
  }
  const first = results[0];
  await tx(async (r) => {
    await query(`UPDATE dbo.ZSO_ORDERS SET STATUS=8, SAP_DOC_ENTRY=?, SAP_DOC_NUM=?, SAP_ORDER_DATE=?, SAP_POSTED_AT=SYSDATETIME(), SAP_POSTED_BY=?, SAP_ERROR=NULL,
      UPDATED_AT=SYSDATETIME(), UPDATED_BY_NAME=? WHERE ID=? AND STATUS=7`, [first.docEntry, first.docNum, first.docDate || null, actor.name, actor.name, o.id], r);
    for (const x of results) if (x.consigneeId) await query(`UPDATE dbo.ZSO_ORDER_CONSIGNEES SET SAP_DOC_ENTRY=?, SAP_DOC_NUM=? WHERE ID=?`, [x.docEntry, x.docNum, x.consigneeId], r);
    await log('order', o.id, existing.length ? 'sap_linked' : 'sap_posted', S.READY_FOR_SAP, S.IN_SAP, `SAP SO ${results.map(x => x.docNum).join(', ')}`, actor, r);
  });
  const fresh = await getOrderRaw(o.id);
  notify.orderEvent('in_sap', fresh, { customerEmail: await customerEmail(fresh) });
  return fresh;
}
// Optional (Settings → autoPostToSap): post with the shared .env SAP account
// as soon as an order becomes ready. Failures are left on the order
// (SAP_ERROR) for a person to retry from the screen.
function maybeAutoPost(id) {
  masters.getSettings().then(s => {
    if (!s.autoPostToSap) return;
    const sys = { type: 'user', name: 'Auto-post (system)', user: { id: 0, role: 'admin' } };
    runWithSapUser(null, () => postToSap(sys, id, { system: true }).catch(e => console.warn(`[SALES] auto-post of order ${id} failed:`, e.message)));
  }).catch(() => {});
}

// ── SAP status sync (every 30 min + on demand) ──────────────────────────────
//  • status 7 orders already present in SAP (posted manually / by the old
//    job) are linked → 8, so they're never posted twice
//  • status 8 orders whose SAP orders are all closed (fully delivered /
//    invoiced) → 9, with the invoice list cached on the order
let _syncing = false;
async function syncWithSap() {
  if (_syncing) return { skipped: true };
  _syncing = true;
  const stats = { linked: 0, closed: 0, checked: 0 };
  try {
    const rows = await query(`SELECT ID, COMPANY, STATUS FROM dbo.ZSO_ORDERS WHERE STATUS IN (7,8)`);
    const byCo = {};
    rows.forEach(r => { (byCo[r.COMPANY || ''] = byCo[r.COMPANY || ''] || []).push(r); });
    const sysActor = { type: 'user', name: 'SAP sync', user: { id: 0 } };
    for (const [co, list] of Object.entries(byCo)) {
      const company = co || (await masters.getSettings()).company;
      const sapRows = await sap.sapOrdersByWebNo(company, list.map(r => r.ID));
      const byId = {};
      sapRows.filter(x => !x.cancelled).forEach(x => { (byId[x.webNo] = byId[x.webNo] || []).push(x); });
      for (const r of list) {
        stats.checked++;
        const so = byId[r.ID] || [];
        if (!so.length) continue;
        if (r.STATUS === 7) {
          const upd = await query(`UPDATE dbo.ZSO_ORDERS SET STATUS=8, SAP_DOC_ENTRY=?, SAP_DOC_NUM=?, SAP_ORDER_DATE=?, SAP_POSTED_AT=SYSDATETIME(), SAP_POSTED_BY='SAP sync',
            LAST_SAP_SYNC=SYSDATETIME() OUTPUT INSERTED.ID WHERE ID=? AND STATUS=7`, [so[0].docEntry, so[0].docNum, so[0].docDate || null, r.ID]);
          if (upd.length) { stats.linked++; await log('order', r.ID, 'sap_linked', 7, 8, `SAP SO ${so.map(x => x.docNum).join(', ')} found in SAP`, sysActor); }
        } else if (so.every(x => !x.open)) {
          let inv = null;
          try { inv = await sap.invoicesForOrder(company, r.ID); } catch (_e) {}
          const upd = await query(`UPDATE dbo.ZSO_ORDERS SET STATUS=9, INVOICE_JSON=?, LAST_SAP_SYNC=SYSDATETIME() OUTPUT INSERTED.ID WHERE ID=? AND STATUS=8`,
            [inv ? JSON.stringify(inv.map(i => ({ invoiceNo: i.invoiceNo, invoiceDate: i.invoiceDate, total: i.total }))) : null, r.ID]);
          if (upd.length) { stats.closed++; await log('order', r.ID, 'closed_by_sap', 8, 9, 'All SAP sales orders closed', sysActor); }
        } else {
          await query(`UPDATE dbo.ZSO_ORDERS SET LAST_SAP_SYNC=SYSDATETIME() WHERE ID=?`, [r.ID]);
        }
      }
    }
    try { stats.invoiceEmails = await notifyNewInvoices(); } catch (e) { console.warn('[SALES] invoice email check failed:', e.message); }
    return stats;
  } finally { _syncing = false; }
}

// New SAP invoices on orders in SAP (status 8, or closed in the last 3
// days) → one "invoice raised" email each, recorded in ZSO_INVOICE_NOTICES
// so it's never sent twice. Invoices dated before Settings.invoiceEmailSince
// (the day this feature was deployed) are recorded silently — no backlog flood.
async function notifyNewInvoices() {
  const s = await masters.getSettings();
  const rows = await query(`SELECT ID, COMPANY FROM dbo.ZSO_ORDERS WHERE STATUS=8 OR (STATUS=9 AND LAST_SAP_SYNC>=DATEADD(day,-3,SYSDATETIME()))`);
  if (!rows.length) return 0;
  const since = s.invoiceEmailSince ? new Date(s.invoiceEmailSince) : null;
  const byCo = {};
  rows.forEach(r => { (byCo[r.COMPANY || s.company] = byCo[r.COMPANY || s.company] || []).push(r.ID); });
  let sent = 0;
  for (const [company, ids] of Object.entries(byCo)) {
    const invs = await sap.invoicesForWebNos(company, ids);
    if (!invs.length) continue;
    const known = new Set((await query(`SELECT ORDER_ID, INVOICE_DOC_ENTRY FROM dbo.ZSO_INVOICE_NOTICES WHERE ORDER_ID IN (${ids.map(Number).join(',')})`))
      .map(k => `${k.ORDER_ID}:${k.INVOICE_DOC_ENTRY}`));
    for (const inv of invs) {
      if (known.has(`${inv.webNo}:${inv.docEntry}`)) continue;
      const fresh = s.invoiceEmail !== false && (!since || new Date(inv.invoiceDate) >= since);
      // Record first (unique key) — if two sync runs ever race, only one sends.
      const ins = await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_INVOICE_NOTICES WHERE ORDER_ID=? AND INVOICE_DOC_ENTRY=?)
        INSERT INTO dbo.ZSO_INVOICE_NOTICES (ORDER_ID,INVOICE_DOC_ENTRY,INVOICE_NO,INVOICE_DATE,EMAILED) OUTPUT INSERTED.ID VALUES (?,?,?,?,?)`,
      [inv.webNo, inv.docEntry, inv.webNo, inv.docEntry, inv.invoiceNo, inv.invoiceDate, fresh]).catch(() => []);
      if (!ins.length) continue;
      await log('order', inv.webNo, 'invoice_raised', null, null, `SAP invoice ${inv.invoiceNo} (${inv.total})${fresh ? ' — email sent' : ''}`,
        { type: 'user', name: 'SAP sync', user: { id: 0 } });
      if (!fresh) continue;
      const o = await getOrderRaw(inv.webNo);
      if (!o) continue;
      const lines = await sap.invoiceLines(company, inv.docEntry).catch(() => []);
      await notify.invoiceEvent(o, inv, lines, { customerEmail: await customerEmail(o) });
      sent++;
    }
  }
  return sent;
}

// ── Documents ───────────────────────────────────────────────────────────────
async function addDoc(entity, entityId, docType, title, fileName, origName, byName, runner) {
  if (entity === 'order' && ['payment', 'po_copy'].includes(docType)) {
    // msale kept only the latest file per type — older ones stay but inactive
    await query(`UPDATE dbo.ZSO_DOCS SET ACTIVE=0 WHERE ENTITY=? AND ENTITY_ID=? AND DOC_TYPE=?`, [entity, parseInt(entityId), docType], runner);
  }
  const r = await one(`INSERT INTO dbo.ZSO_DOCS (ENTITY,ENTITY_ID,DOC_TYPE,TITLE,FILE_NAME,ORIG_NAME,CREATED_BY_NAME) OUTPUT INSERTED.ID VALUES (?,?,?,?,?,?,?)`,
    [entity, parseInt(entityId), docType, title || '', fileName, origName || '', byName], runner);
  return r.ID;
}
async function getDoc(docId) { return one(`SELECT * FROM dbo.ZSO_DOCS WHERE ID=? AND ACTIVE=1`, [parseInt(docId)]); }

module.exports = {
  createOrder, resubmit, act, nextCustOrderNo, customerCancel, submitPayment, postToSap, syncWithSap,
  getOrder, getOrderRaw, getItems, listOrders, canSee, availableActions, scopeFor,
  walletSummary, walletBalance, ledger, pendingPayments, addOnAccountPayment, decideTopup,
  addDoc, getDoc, getDocs, log, appendRemark, refNo, taxFor, totals, ACTIONS,
};