sap-erp/routes/cashFlow.js

'use strict';
const express = require('express');
const router  = express.Router();
const { verifyToken } = require('../middleware/auth');
const { getPool }     = require('../services/sqlPool');

const store   = () => require('../services/cashFlowStore');
const bsStore = () => require('../services/balanceSheetStore');

store().bootstrap().catch(e => console.error('[CashFlow] bootstrap error:', e));

function dayBefore(dateStr) {
  const d = new Date(dateStr);
  d.setDate(d.getDate() - 1);
  return d.toISOString().split('T')[0];
}

function periodLabel(to) {
  const d = new Date(to);
  const months = ['JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'];
  return `Upto ${months[d.getMonth()]} ${d.getFullYear()}`;
}

// GET /api/cash-flow/items
router.get('/items', verifyToken, async (req, res) => {
  try { res.json({ success: true, data: await store().getItems() }); }
  catch(err) { res.status(500).json({ success: false, message: err.message }); }
});

// POST /api/cash-flow/items  — create or update
router.post('/items', verifyToken, async (req, res) => {
  try {
    const { id, section, item_type, label, acct_codes, sort_order, notes } = req.body;
    if (!label || !section) return res.status(400).json({ success: false, message: 'section and label required' });
    // cash section always forces item_type=cash
    const type = section === 'Cash' ? 'cash' : (item_type || 'working');
    await store().saveItem({ id: id || null, section, item_type: type, label, acct_codes, sort_order, notes });
    res.json({ success: true });
  } catch(err) { res.status(500).json({ success: false, message: err.message }); }
});

// DELETE /api/cash-flow/items/:id
router.delete('/items/:id', verifyToken, async (req, res) => {
  try {
    await store().deleteItem(parseInt(req.params.id));
    res.json({ success: true });
  } catch(err) { res.status(500).json({ success: false, message: err.message }); }
});

// GET /api/cash-flow/data?from=&to=[&snapshotId=][&monthlySnapId=]
router.get('/data', verifyToken, async (req, res) => {
  try {
    const { from, to, snapshotId, monthlySnapId } = req.query;
    if (!from || !to) return res.status(400).json({ success: false, message: 'from and to required' });

    const s     = store();
    const items = await s.getItems();

    const values         = {};  // detailed table values
    const period_values  = {};  // period D-C for all items (used for statement CapEx/WC)
    const opening_values = {};  // opening balance for cash items

    const openDate = dayBefore(from);

    if (snapshotId) {
      // pl_snapshots lives on the app DB — read via costingStore, not the SAP pool.
      const snap = await require('../services/costingStore').getSnapshot(parseInt(snapshotId));
      if (!snap) return res.status(404).json({ success: false, message: 'Snapshot not found' });
      const snapData = JSON.parse(snap.data_json);
      const accounts = snapData.accounts || snapData;

      items.forEach(item => {
        if (item.item_type === 'cash') {
          opening_values[item.id] = s.fetchOpeningBalanceFromSnapshot(accounts, item.acct_codes);
          values[item.id]         = s.fetchItemClosingBalanceFromSnapshot(accounts, item.acct_codes);
          period_values[item.id]  = s.fetchItemValueFromSnapshot(accounts, item.acct_codes);
        } else if (item.item_type === 'capital') {
          values[item.id]        = s.fetchItemClosingBalanceFromSnapshot(accounts, item.acct_codes);
          period_values[item.id] = s.fetchItemValueFromSnapshot(accounts, item.acct_codes);
        } else {
          values[item.id]        = s.fetchItemValueFromSnapshot(accounts, item.acct_codes);
          period_values[item.id] = values[item.id];
        }
      });
    } else {
      const pool = await getPool();
      await Promise.all(items.map(async item => {
        if (item.item_type === 'cash') {
          [opening_values[item.id], values[item.id], period_values[item.id]] = await Promise.all([
            s.fetchItemClosingBalance(pool, item.acct_codes, openDate),
            s.fetchItemClosingBalance(pool, item.acct_codes, to),
            s.fetchItemValue(pool, item.acct_codes, from, to),
          ]);
        } else if (item.item_type === 'capital') {
          [values[item.id], period_values[item.id]] = await Promise.all([
            s.fetchItemClosingBalance(pool, item.acct_codes, to),
            s.fetchItemValue(pool, item.acct_codes, from, to),
          ]);
        } else {
          values[item.id]        = await s.fetchItemValue(pool, item.acct_codes, from, to);
          period_values[item.id] = values[item.id];
        }
      }));
    }

    // Net profit — always from Monthly Accounts snapshot if provided,
    // otherwise from the main snapshot (if selected), otherwise live DB
    const pool = await getPool();
    const profitSnapId = monthlySnapId || (!snapshotId ? null : snapshotId);
    const netProfit = profitSnapId
      ? await bsStore().fetchNetProfitFromSnapshot(pool, profitSnapId)
      : await bsStore().fetchNetProfit(pool, from, to);

    // Aggregate summary components
    let wc_change = 0, capex = 0, opening_cash = 0, closing_cash = 0;
    items.forEach(item => {
      const pv = period_values[item.id] || 0;
      if (item.item_type === 'cash') {
        opening_cash += opening_values[item.id] || 0;
        closing_cash += values[item.id] || 0;
      } else if (item.item_type === 'working') {
        // WC increase = cash decrease → negate period D-C
        wc_change -= pv;
      } else if (item.item_type === 'capital') {
        // CapEx: asset D-C > 0 (bought) = cash out = negative
        capex -= pv;
      }
    });

    const total        = netProfit + wc_change;
    const net_increase = total + capex;

    const summary = {
      net_profit:    netProfit,
      wc_change,
      total,
      capex,
      net_increase,
      opening_cash,
      closing_cash,
      period_label:  periodLabel(to),
    };

    res.json({ success: true, from, to, items, values, period_values, opening_values, summary });
  } catch(err) { res.status(500).json({ success: false, message: err.message }); }
});

// GET /api/cash-flow/snapshots
router.get('/snapshots', verifyToken, async (req, res) => {
  try {
    const snaps = await require('../services/costingStore').getSnapshots();
    res.json({ success: true, data: snaps });
  } catch(err) { res.status(500).json({ success: false, message: err.message }); }
});

// GET /api/cash-flow/accounts?q=  — search accounts for config panel
router.get('/accounts', verifyToken, async (req, res) => {
  try {
    const { q } = req.query;
    const pool  = await getPool();
    const req2  = pool.request();
    let where = `WHERE T0.GroupMask IN (1,2,3,4,5)`;
    if (q) { req2.input('q', `%${q}%`); where += ` AND (T0.AcctCode LIKE @q OR T0.AcctName LIKE @q)`; }
    const r = await req2.query(`
      SELECT TOP 100 T0.AcctCode, T0.AcctName, T0.GroupMask
      FROM OACT T0 ${where} ORDER BY T0.AcctCode
    `);
    res.json({ success: true, data: r.recordset });
  } catch(err) { res.status(500).json({ success: false, message: err.message }); }
});

module.exports = router;