// routes/inventoryPostingList.js
// Mirrors SAP B1's own "Inventory Posting List" report (Inventory →
// Inventory Reports → Inventory Posting List): every stock-moving
// transaction (OINM) for a range of items over a date range, with a running
// quantity Balance per item, filterable by Item Code range, Item Group,
// and Warehouse(s) — same selection-criteria shape as the Inventory Status
// Report.
//
// NOTE — "Split Display by Batch/Serial Numbers" (visible on SAP's own
// selection screen) is deliberately NOT implemented here: OINM has no
// reliable, verified link back to the batch/serial actually posted on each
// transaction in this database (the obvious ITL1→OBTN join came back empty
// against real data), and showing a guessed/wrong batch number would be
// worse than not showing one at all. Every other column is real, verified
// OINM data.
'use strict';
const express = require('express');
const router = express.Router();
const { verifyToken } = require('../middleware/auth');
const { getPool } = require('../services/sqlPool');

const cq = (req) => req.query?.company || req.body?.company || null;

// Real SAP TransType codes seen against this database's own transaction
// history (verified live: 60/59/67/15/18/20/14/16/19/13/21 account for
// nearly every row) — full descriptive labels rather than guessed 2-letter
// SAP abbreviations, since those couldn't be verified. Anything outside this
// list (e.g. a UDO-driven custom type) falls back to "Type <code>" rather
// than asserting a label that might be wrong.
const TRANS_TYPE_LABELS = {
  13: 'AR Invoice', 14: 'AR Credit Memo', 15: 'Delivery', 16: 'AR Return',
  17: 'Reserve Invoice', 18: 'Goods Receipt PO', 19: 'Goods Return',
  20: 'AP Invoice', 21: 'AP Credit Memo',
  59: 'Inventory Receipt', 60: 'Inventory Issue', 67: 'Stock Transfer',
};
function transLabel(t) { return TRANS_TYPE_LABELS[t] || `Type ${t}`; }

router.get('/', verifyToken, async (req, res) => {
  try {
    const co = cq(req);
    const pool = await getPool(co);
    const sqlEsc = (v) => String(v).replace(/'/g, "''");

    const itemFrom = (req.query.itemFrom || '').trim();
    const itemTo = (req.query.itemTo || '').trim();
    const itemGroup = (req.query.itemGroup || '').trim();
    const warehouses = (req.query.warehouses || '').split(',').map((w) => w.trim()).filter(Boolean);
    const dateFrom = (req.query.dateFrom || '').trim();
    const dateTo = (req.query.dateTo || '').trim();
    const hideNoQtyChange = req.query.hideNoQtyChange === '1';
    if (!dateFrom || !dateTo) return res.status(400).json({ success: false, message: 'dateFrom and dateTo are required' });

    // Resolve the matching item set first — same range+group filter as the
    // Inventory Status Report, so the two stay consistent.
    const itemWhere = [];
    if (itemFrom) itemWhere.push(`"ItemCode" >= '${sqlEsc(itemFrom)}'`);
    if (itemTo) itemWhere.push(`"ItemCode" <= '${sqlEsc(itemTo)}'`);
    if (itemGroup) itemWhere.push(`"ItmsGrpCod" = ${parseInt(itemGroup)}`);
    const itemRows = await pool.request().query(`SELECT "ItemCode" FROM [dbo].[OITM] ${itemWhere.length ? 'WHERE ' + itemWhere.join(' AND ') : ''}`);
    const itemCodes = itemRows.recordset.map((r) => r.ItemCode);
    if (!itemCodes.length) return res.json({ success: true, data: [] });
    // Guard against an unbounded range (e.g. both From/To left blank) —
    // matching against every item in SAP would make the OINM scan below
    // enormous. 2000 items is already a generous ceiling for one report run.
    if (itemCodes.length > 2000) return res.status(400).json({ success: false, message: `${itemCodes.length} items match this filter — narrow the Item Code range or Item Group first (max 2000 at a time).` });
    const itemList = itemCodes.map((c) => `'${sqlEsc(c)}'`).join(',');

    const whWhere = warehouses.length ? `AND "Warehouse" IN (${warehouses.map((w) => `'${sqlEsc(w)}'`).join(',')})` : '';

    // Opening balance per item — every transaction strictly BEFORE the
    // selected date range, so "Balance" in the results is a real absolute
    // stock level, not just a delta within the window (matches what SAP's
    // own report shows).
    const openingRows = await pool.request().query(`
      SELECT "ItemCode", SUM(ISNULL("InQty",0)) - SUM(ISNULL("OutQty",0)) AS "Opening"
      FROM [dbo].[OINM]
      WHERE "ItemCode" IN (${itemList}) ${whWhere} AND "DocDate" < '${sqlEsc(dateFrom)}'
      GROUP BY "ItemCode"`);
    const openingByItem = {};
    openingRows.recordset.forEach((r) => { openingByItem[r.ItemCode] = Number(r.Opening) || 0; });

    const qtyFilter = hideNoQtyChange ? `AND (ISNULL("InQty",0)<>0 OR ISNULL("OutQty",0)<>0)` : '';
    const rows = await pool.request().query(`
      SELECT T0."ItemCode", T0."Dscription", T0."DocDate", T0."CreateDate", T0."Warehouse",
             T0."InQty", T0."OutQty", T0."CardCode", T0."CardName", T0."Ref1", T0."Ref2",
             T0."TransType", T0."TransNum", T0."DocLineNum", T0."CalcPrice", T0."Price", T0."Comments"
      FROM [dbo].[OINM] T0
      WHERE T0."ItemCode" IN (${itemList}) ${whWhere}
        AND T0."DocDate" >= '${sqlEsc(dateFrom)}' AND T0."DocDate" <= '${sqlEsc(dateTo)}'
        ${qtyFilter}
      ORDER BY T0."ItemCode", T0."DocDate", T0."TransNum"`);

    // Running balance computed here (not trusted from OINM.Balance, which is
    // only populated on certain valuation checkpoints, not every row —
    // verified live: most receipt/issue rows carry a NULL Balance) — a
    // simple per-item cumulative sum seeded from the opening balance above.
    const runningByItem = {};
    const data = rows.recordset.map((r) => {
      const itemCode = r.ItemCode;
      if (!(itemCode in runningByItem)) runningByItem[itemCode] = openingByItem[itemCode] || 0;
      const inQty = Number(r.InQty) || 0;
      const outQty = Number(r.OutQty) || 0;
      runningByItem[itemCode] += inQty - outQty;
      return {
        itemCode, itemName: r.Dscription || '',
        docDate: r.DocDate, warehouse: r.Warehouse || '',
        transType: r.TransType, docLabel: transLabel(r.TransType),
        docRef: r.Ref1 || '', ref2: r.Ref2 || '',
        docRow: (r.DocLineNum || 0) + 1,
        bpName: r.CardName || '', cardCode: r.CardCode || '',
        inQty, outQty,
        price: (r.CalcPrice != null && r.CalcPrice !== 0) ? Number(r.CalcPrice) : Number(r.Price) || 0,
        balance: runningByItem[itemCode],
        remarks: r.Comments || '',
      };
    });
    res.json({ success: true, data, openingBalances: openingByItem });
  } catch (err) {
    res.status(500).json({ success: false, message: err.message });
  }
});

module.exports = router;