// routes/batchNoTransaction.js
// Mirrors SAP B1's own standard "Batch Number Transactions Report" (Inventory
// → Inventory Reports → Batch Number Transactions Report): a master grid of
// batches (from OIBT, SAP's per-batch/per-warehouse stock table) and, for a
// selected batch, a detail grid of every stock-affecting document that ever
// touched it (from IBT1, SAP's batch-transaction-log table).
'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;
const sqlEsc = (v) => String(v).replace(/'/g, "''");

// GET / — the "Batches" master grid: one row per Item/Batch/Warehouse,
// exactly the fields SAP's own report shows (Item No, Item Description,
// Batch, Whse, Quantity, Expiration Date, Manufacturing Date, Project,
// Status, COA_Status, Sale Type — all native OIBT columns), plus Committed/
// OnOrder/Available as extra value-add columns (same formula as Inventory
// Status Report) since the data is already on hand.
router.get('/', verifyToken, async (req, res) => {
  try {
    const co = cq(req);
    const pool = await getPool(co);

    const itemFrom = (req.query.itemFrom || '').trim();
    const itemTo = (req.query.itemTo || '').trim();
    const batchNo = (req.query.batchNo || '').trim();
    const itemGroup = (req.query.itemGroup || '').trim();
    const includeZeroQty = req.query.includeZeroQty === '1';
    const warehouses = (req.query.warehouses || '').split(',').map((w) => w.trim()).filter(Boolean);

    const where = [];
    if (itemFrom) where.push(`T0."ItemCode" >= '${sqlEsc(itemFrom)}'`);
    if (itemTo) where.push(`T0."ItemCode" <= '${sqlEsc(itemTo)}'`);
    if (batchNo) where.push(`T0."BatchNum" LIKE '%${sqlEsc(batchNo)}%'`);
    if (itemGroup) where.push(`T2."ItmsGrpCod" = ${parseInt(itemGroup)}`);
    if (warehouses.length) where.push(`T0."WhsCode" IN (${warehouses.map((w) => `'${sqlEsc(w)}'`).join(',')})`);
    if (!includeZeroQty) where.push(`T0."Quantity" <> 0`);

    const query = `
      SELECT T0."ItemCode", T0."ItemName", T0."BatchNum", T0."WhsCode",
             T0."Quantity", T0."IsCommited", T0."OnOrder",
             T0."PrdDate", T0."ExpDate",
             T0."U_Project", T0."U_Status", T0."U_COA_Status", T0."U_SaleType",
             T2."InvntryUom" AS "Uom"
      FROM [dbo].[OIBT] T0
      LEFT JOIN [dbo].[OITM] T2 ON T2."ItemCode" = T0."ItemCode"
      ${where.length ? `WHERE ${where.join(' AND ')}` : ''}
      ORDER BY T0."ItemCode", T0."BatchNum", T0."WhsCode"`;

    const result = await pool.request().query(query);
    const data = result.recordset.map((r) => {
      const qty = Number(r.Quantity) || 0;
      const committed = Number(r.IsCommited) || 0;
      const onOrder = Number(r.OnOrder) || 0;
      return {
        itemCode: r.ItemCode,
        itemName: r.ItemName || '',
        batchNo: r.BatchNum,
        warehouse: r.WhsCode,
        inStock: qty,
        committed,
        ordered: onOrder,
        available: qty - committed + onOrder,
        mfgDate: r.PrdDate,
        expDate: r.ExpDate,
        project: r.U_Project || '',
        status: r.U_Status || '',
        coaStatus: r.U_COA_Status || '',
        saleType: r.U_SaleType || '',
        uom: r.Uom || '',
      };
    });
    res.json({ success: true, data });
  } catch (err) {
    res.status(500).json({ success: false, message: err.message });
  }
});

// BaseType (IBT1) → the SAP document table/label that actually recorded the
// stock movement. Confirmed by cross-checking real BaseEntry values against
// each candidate table in this database (e.g. BaseType 18 entries' BaseEntry
// values are found in OPCH, with matching CardName/DocDate — not any other
// table), not guessed from memory of SAP's BoObjectTypes enum alone.
const DOC_TYPES = {
  13: { label: 'A/R Invoice', table: 'OINV' },
  14: { label: 'A/R Credit Memo', table: 'ORIN' },
  15: { label: 'Delivery', table: 'ODLN' },
  16: { label: 'Return', table: 'ORDN' },
  17: { label: 'Sales Order', table: 'ORDR' },
  18: { label: 'A/P Invoice', table: 'OPCH' },
  19: { label: 'A/P Credit Memo', table: 'ORPC' },
  20: { label: 'Goods Receipt PO', table: 'OPDN' },
  21: { label: 'Goods Return', table: 'ORPD' },
  22: { label: 'Purchase Order', table: 'OPOR' },
  59: { label: 'Goods Receipt', table: 'OIGN' },
  60: { label: 'Goods Issue', table: 'OIGE' },
  67: { label: 'Inventory Transfer', table: 'OWTR' },
  310000001: { label: 'Production Order', table: 'OWOR' },
};

// GET /transactions?itemCode=X&batchNo=Y — the "Transactions for Batch" grid.
// Reads IBT1, SAP's own batch-transaction log, one row per stock-affecting
// document line that ever touched this exact Item+Batch.
//
// Sign/direction rule (reverse-engineered against this database's real IBT1
// data, not assumed from documentation): Direction 0 and 1 ALWAYS store
// Quantity as a positive magnitude — 0 means the document increased batch
// stock (In), 1 means it decreased it (Out), so the sign has to be applied
// here. Direction 2 (used by AR/Sales-side docs — Invoice/Credit
// Memo/Return/Sales Order) stores Quantity ALREADY signed correctly, so it's
// used as-is. Verified against BaseType 60 (Goods Issue: always Direction 1,
// Quantity always ≥0) and BaseType 15 (Delivery: Direction 0/1/2 all appear,
// but only Direction 2 rows have negative Quantity stored).
router.get('/transactions', verifyToken, async (req, res) => {
  try {
    const co = cq(req);
    const pool = await getPool(co);
    const itemCode = (req.query.itemCode || '').trim();
    const batchNo = (req.query.batchNo || '').trim();
    if (!itemCode || !batchNo) return res.json({ success: true, data: [] });

    const query = `
      SELECT T0."ItemCode", T0."BatchNum", T0."WhsCode", T1."WhsName",
             T0."LineNum", T0."BaseType", T0."BaseNum", T0."BaseLinNum",
             T0."DocDate", T0."Quantity", T0."Direction", T0."CardName"
      FROM [dbo].[IBT1] T0
      LEFT JOIN [dbo].[OWHS] T1 ON T1."WhsCode" = T0."WhsCode"
      WHERE T0."ItemCode" = '${sqlEsc(itemCode)}' AND T0."BatchNum" = '${sqlEsc(batchNo)}'
      ORDER BY T0."DocDate", T0."LineNum"`;

    const result = await pool.request().query(query);
    const data = result.recordset.map((r, i) => {
      const rawQty = Number(r.Quantity) || 0;
      const dir = Number(r.Direction);
      const qty = dir === 1 ? -Math.abs(rawQty) : dir === 0 ? Math.abs(rawQty) : rawQty;
      const docType = DOC_TYPES[Number(r.BaseType)] || { label: `Doc Type ${r.BaseType}`, table: '' };
      return {
        no: i + 1,
        docTypeLabel: docType.label,
        docNum: r.BaseNum,
        docRow: r.BaseLinNum,
        date: r.DocDate,
        warehouse: r.WhsCode,
        warehouseName: r.WhsName || '',
        bpName: r.CardName || '',
        qty,
        direction: qty >= 0 ? 'In' : 'Out',
        itemCode: r.ItemCode,
        batchNo: r.BatchNum,
      };
    });
    res.json({ success: true, data });
  } catch (err) {
    res.status(500).json({ success: false, message: err.message });
  }
});

module.exports = router;