'use strict';
// routes/board.js — Board Member analytics: company-growth summary numbers
// straight from the SAP company DB (net sales = AR Invoices − AR Credit
// Memos; purchases = AP Invoices). Read-only, one endpoint, gated to the
// board_member role (and admin).
const express = require('express');
const router  = express.Router();
const { verifyToken } = require('../middleware/auth');
const { getPool } = require('../services/sqlPool');
const appSettings = require('../services/appSettingsStore');

async function hana(sqlText) {
  const pool = await getPool();
  return (await pool.request().query(sqlText)).recordset || [];
}

// FG EQUIPMENT (item group 103) holds both blood-bag and CAPD machines. These
// two item codes are the CAPD ones; everything else in 103 is BB. Used to
// split "FG EQUIPMENT" into "FG EQUIPMENT BB" / "FG EQUIPMENT CAPD" in the
// Sales-by-Product-Group card and its multi-select filter.
const EQUIP_GRP  = 103;
const CAPD_EQUIP = "'MEAPD20','m.CYCLER'";

// Resolves the effective Item Group filter for a request: the caller's own
// ?groups= selection, narrowed by (or defaulted to) the admin-configured
// restriction (System Settings → Company Growth Dashboard), which is always
// a HARD CEILING — applied even when the caller picked no filter of their
// own. Shared by every endpoint that needs to respect it (breakdown, monthly
// trend, …) so the restriction can never be bypassed by hitting a route that
// forgot to check it. `t1Alias`/`t2Alias` let callers match whatever line/
// OITM table aliases their own query already uses (T1/T2 in /breakdown, but
// /monthly's UNION query needs its own since it has 3 separate branches).
function resolveGroupFilter(req, t1Alias = 'T1', t2Alias = 'T2') {
  let rawGroups = (req.query.groups || '').split(',').map(s => s.trim()).filter(Boolean);
  const allowedGroups = appSettings.boardProductGroups();
  if (allowedGroups.length) {
    rawGroups = rawGroups.length ? rawGroups.filter(g => allowedGroups.includes(g)) : allowedGroups.slice();
  }
  const plainIds = rawGroups.filter(g => /^\d+$/.test(g)).map(Number).filter(n => n !== EQUIP_GRP);
  const gClauses = [];
  if (plainIds.length) gClauses.push(`${t2Alias}.ItmsGrpCod IN (${plainIds.join(',')})`);
  if (rawGroups.includes(String(EQUIP_GRP))) gClauses.push(`${t2Alias}.ItmsGrpCod=${EQUIP_GRP}`);
  if (rawGroups.includes('103bb'))   gClauses.push(`(${t2Alias}.ItmsGrpCod=${EQUIP_GRP} AND ${t1Alias}.ItemCode NOT IN (${CAPD_EQUIP}))`);
  if (rawGroups.includes('103capd')) gClauses.push(`(${t2Alias}.ItmsGrpCod=${EQUIP_GRP} AND ${t1Alias}.ItemCode IN (${CAPD_EQUIP}))`);
  const hasGrpFilter = gClauses.length > 0;
  return { rawGroups, hasGrpFilter, grpCond: hasGrpFilter ? ` AND (${gClauses.join(' OR ')})` : '' };
}

function requireBoard(req, res, next) {
  const u = req.user || {};
  if (['board_member', 'admin'].includes(u.role)) return next();
  // Any role explicitly granted the Board Dashboard MODULE may view it too —
  // the module checkbox is how admins hand out this dashboard.
  if (Array.isArray(u.modules) && u.modules.includes('board-dashboard')) return next();
  return res.status(403).json({ success: false, message: 'Board Dashboard access requires the Board Member role or the "Board Dashboard" module' });
}

router.get('/summary', verifyToken, requireBoard, async (req, res) => {
  try {
    // ── Monthly net sales & purchases, last 24 calendar months ───────────
    const monthly = await hana(`
      SELECT y, m, SUM(sales) AS sales, SUM(purch) AS purch FROM (
        SELECT YEAR(DocDate) y, MONTH(DocDate) m, SUM(DocTotal-VatSum) sales, 0 purch
          FROM OINV WHERE CANCELED='N' AND DocDate>=DATEADD(month,-24,GETDATE()) GROUP BY YEAR(DocDate),MONTH(DocDate)
        UNION ALL
        SELECT YEAR(DocDate), MONTH(DocDate), -SUM(DocTotal-VatSum), 0
          FROM ORIN WHERE CANCELED='N' AND DocDate>=DATEADD(month,-24,GETDATE()) GROUP BY YEAR(DocDate),MONTH(DocDate)
        UNION ALL
        SELECT YEAR(DocDate), MONTH(DocDate), 0, SUM(DocTotal-VatSum)
          FROM OPCH WHERE CANCELED='N' AND DocDate>=DATEADD(month,-24,GETDATE()) GROUP BY YEAR(DocDate),MONTH(DocDate)
      ) x GROUP BY y, m ORDER BY y, m`);

    // ── Top customers & items, trailing 12 months ────────────────────────
    const topCustomers = await hana(`
      SELECT TOP 6 CardName AS name, SUM(DocTotal-VatSum) AS total
      FROM OINV WHERE CANCELED='N' AND DocDate>=DATEADD(month,-12,GETDATE())
      GROUP BY CardName ORDER BY total DESC`);
    const topItems = await hana(`
      SELECT TOP 6 T1.ItemCode AS code, MAX(T1.Dscription) AS name, SUM(T1.LineTotal) AS total
      FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry
      WHERE T0.CANCELED='N' AND T0.DocDate>=DATEADD(month,-12,GETDATE())
      GROUP BY T1.ItemCode ORDER BY total DESC`);

    // ── Headline KPIs ────────────────────────────────────────────────────
    const kpi = (await hana(`
      SELECT
        (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND YEAR(DocDate)=YEAR(GETDATE()))
        - (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM ORIN WHERE CANCELED='N' AND YEAR(DocDate)=YEAR(GETDATE())) AS ytdSales,
        (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND YEAR(DocDate)=YEAR(GETDATE())-1 AND DocDate<=DATEADD(year,-1,GETDATE()))
        - (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM ORIN WHERE CANCELED='N' AND YEAR(DocDate)=YEAR(GETDATE())-1 AND DocDate<=DATEADD(year,-1,GETDATE())) AS lastYtdSales,
        (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND YEAR(DocDate)=YEAR(GETDATE()) AND MONTH(DocDate)=MONTH(GETDATE())) AS monthSales,
        (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OPCH WHERE CANCELED='N' AND YEAR(DocDate)=YEAR(GETDATE())) AS ytdPurchases,
        (SELECT ISNULL(SUM(DocTotal-PaidToDate),0) FROM OINV WHERE DocStatus='O' AND CANCELED='N') AS openAR,
        (SELECT ISNULL(SUM(DocTotal-PaidToDate),0) FROM OPCH WHERE DocStatus='O' AND CANCELED='N') AS openAP,
        (SELECT COUNT(*) FROM OWOR WHERE Status='R') AS openProdOrders,
        (SELECT COUNT(DISTINCT CardCode) FROM OINV WHERE CANCELED='N' AND DocDate>=DATEADD(month,-12,GETDATE())) AS activeCustomers`))[0] || {};

    res.json({ success: true, data: {
      monthly:      monthly.map(r => ({ y: r.y, m: r.m, sales: Number(r.sales) || 0, purchases: Number(r.purch) || 0 })),
      topCustomers: topCustomers.map(r => ({ name: r.name, total: Number(r.total) || 0 })),
      topItems:     topItems.map(r => ({ code: r.code, name: r.name, total: Number(r.total) || 0 })),
      kpi: {
        ytdSales:       Number(kpi.ytdSales) || 0,
        lastYtdSales:   Number(kpi.lastYtdSales) || 0,
        monthSales:     Number(kpi.monthSales) || 0,
        ytdPurchases:   Number(kpi.ytdPurchases) || 0,
        openAR:         Number(kpi.openAR) || 0,
        openAP:         Number(kpi.openAP) || 0,
        openProdOrders: Number(kpi.openProdOrders) || 0,
        activeCustomers:Number(kpi.activeCustomers) || 0,
      },
    }});
  } catch (err) {
    console.error('[BOARD] summary failed:', err.message);
    res.status(500).json({ success: false, message: err.message });
  }
});

// ── Monthly trend for a caller-chosen date range (defaults 24 months) ─────
// Respects the Product Groups filter (own ?groups= selection, narrowed by
// the admin-configured restriction) the same way /breakdown's KPI cards do —
// otherwise the trend chart would keep showing whole-company figures while
// every other card on the page is filtered, which is exactly the mismatch
// that was reported.
router.get('/monthly', verifyToken, requireBoard, async (req, res) => {
  try {
    const DS = /^\d{4}-\d{2}-\d{2}$/;
    const from = DS.test(req.query.from || '') ? req.query.from : null;
    const to   = DS.test(req.query.to   || '') ? req.query.to   : null;
    const { hasGrpFilter, grpCond } = resolveGroupFilter(req);
    const monthly = hasGrpFilter ? await hana(`
      SELECT y, m, SUM(sales) AS sales, SUM(purch) AS purch FROM (
        SELECT YEAR(T0.DocDate) y, MONTH(T0.DocDate) m, SUM(T1.LineTotal) sales, 0 purch
          FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
          WHERE T0.CANCELED='N' AND ${(from && to) ? `T0.DocDate>='${from}' AND T0.DocDate<='${to}'` : `T0.DocDate>=DATEADD(month,-24,GETDATE())`}${grpCond}
          GROUP BY YEAR(T0.DocDate),MONTH(T0.DocDate)
        UNION ALL
        SELECT YEAR(T0.DocDate), MONTH(T0.DocDate), -SUM(T1.LineTotal), 0
          FROM RIN1 T1 JOIN ORIN T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
          WHERE T0.CANCELED='N' AND ${(from && to) ? `T0.DocDate>='${from}' AND T0.DocDate<='${to}'` : `T0.DocDate>=DATEADD(month,-24,GETDATE())`}${grpCond}
          GROUP BY YEAR(T0.DocDate),MONTH(T0.DocDate)
        UNION ALL
        SELECT YEAR(T0.DocDate), MONTH(T0.DocDate), 0, SUM(T1.LineTotal)
          FROM PCH1 T1 JOIN OPCH T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
          WHERE T0.CANCELED='N' AND ${(from && to) ? `T0.DocDate>='${from}' AND T0.DocDate<='${to}'` : `T0.DocDate>=DATEADD(month,-24,GETDATE())`}${grpCond}
          GROUP BY YEAR(T0.DocDate),MONTH(T0.DocDate)
      ) x GROUP BY y, m ORDER BY y, m`)
    : await hana(`
      SELECT y, m, SUM(sales) AS sales, SUM(purch) AS purch FROM (
        SELECT YEAR(DocDate) y, MONTH(DocDate) m, SUM(DocTotal-VatSum) sales, 0 purch
          FROM OINV WHERE CANCELED='N' AND ${(from && to) ? `DocDate>='${from}' AND DocDate<='${to}'` : `DocDate>=DATEADD(month,-24,GETDATE())`} GROUP BY YEAR(DocDate),MONTH(DocDate)
        UNION ALL
        SELECT YEAR(DocDate), MONTH(DocDate), -SUM(DocTotal-VatSum), 0
          FROM ORIN WHERE CANCELED='N' AND ${(from && to) ? `DocDate>='${from}' AND DocDate<='${to}'` : `DocDate>=DATEADD(month,-24,GETDATE())`} GROUP BY YEAR(DocDate),MONTH(DocDate)
        UNION ALL
        SELECT YEAR(DocDate), MONTH(DocDate), 0, SUM(DocTotal-VatSum)
          FROM OPCH WHERE CANCELED='N' AND ${(from && to) ? `DocDate>='${from}' AND DocDate<='${to}'` : `DocDate>=DATEADD(month,-24,GETDATE())`} GROUP BY YEAR(DocDate),MONTH(DocDate)
      ) x GROUP BY y, m ORDER BY y, m`);
    res.json({ success: true, data: monthly.map(r => ({ y: r.y, m: r.m, sales: Number(r.sales) || 0, purchases: Number(r.purch) || 0 })) });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// Every Item Group SAP actually has items in, with the FG EQUIPMENT (103)
// split applied — the raw, UNRESTRICTED list. Shared by both routes below.
async function allGroups() {
  const rows = await hana(`
    SELECT T3.ItmsGrpCod AS code, T3.ItmsGrpNam AS name
    FROM OITB T3
    WHERE EXISTS (SELECT 1 FROM OITM T2 WHERE T2.ItmsGrpCod=T3.ItmsGrpCod)
    ORDER BY T3.ItmsGrpNam`);
  // Codes are strings; FG EQUIPMENT (103) becomes two virtual selectable
  // entries so BB vs CAPD equipment can be filtered separately.
  const data = [];
  rows.forEach(r => {
    if (Number(r.code) === EQUIP_GRP) {
      data.push({ code: '103bb',   name: 'FG EQUIPMENT BB' });
      data.push({ code: '103capd', name: 'FG EQUIPMENT CAPD' });
    } else {
      data.push({ code: String(r.code), name: r.name });
    }
  });
  return data;
}

// ── Item Group list for the multi-select filter — ADMIN-RESTRICTED ────────
// Admin → System Settings → "Company Growth Dashboard — Product Groups" can
// limit this to a chosen subset; empty configuration = no restriction (every
// group SAP has shows, as before).
router.get('/groups', verifyToken, requireBoard, async (req, res) => {
  try {
    const data = await allGroups();
    const allowed = appSettings.boardProductGroups();
    const restricted = allowed.length ? data.filter(g => allowed.includes(g.code)) : data;
    // `total` = how many groups the company actually has, so the frontend can
    // tell "All (5 of 32 groups)" apart from a genuinely unrestricted "All" —
    // without exposing the unrestricted group NAMES themselves to a
    // restricted viewer, just the count.
    res.json({ success: true, data: restricted, total: data.length });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// ── UNRESTRICTED group list — for the admin settings checklist itself (an
// admin configuring the restriction must see every group to choose from,
// not the already-restricted set — same gate as the dashboard, admin-only in
// practice since only admins reach the System Settings page).
router.get('/groups/all', verifyToken, requireBoard, async (req, res) => {
  try { res.json({ success: true, data: await allGroups() }); }
  catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// ── Filterable breakdown: top customers / products / product groups over a
//    caller-chosen date range (defaults to trailing 12 months) ─────────────
router.get('/breakdown', verifyToken, requireBoard, async (req, res) => {
  try {
    const DS = /^\d{4}-\d{2}-\d{2}$/;
    const iso = d => d.toISOString().slice(0, 10);
    // Resolve to EXPLICIT dates (default trailing 12 months) so the previous
    // equal-length comparison period can be computed for the growth badge.
    let from = DS.test(req.query.from || '') ? req.query.from : null;
    let to   = DS.test(req.query.to   || '') ? req.query.to   : null;
    if (!from || !to) {
      const now = new Date(); to = iso(now);
      const f = new Date(now); f.setMonth(f.getMonth() - 12); from = iso(f);
    }
    const fromD = new Date(from + 'T00:00:00Z'), toD = new Date(to + 'T00:00:00Z');
    const lenDays = Math.max(1, Math.round((toD - fromD) / 86400000) + 1);
    const prevTo   = iso(new Date(fromD.getTime() - 86400000));
    const prevFrom = iso(new Date(fromD.getTime() - lenDays * 86400000));
    const cond  = `T0.DocDate>='${from}' AND T0.DocDate<='${to}'`;
    const condP = `DocDate>='${from}' AND DocDate<='${to}'`;
    const condPrev = `DocDate>='${prevFrom}' AND DocDate<='${prevTo}'`;

    // Optional multi-select Item Group filter (?groups=101,102,103bb,103capd),
    // narrowed by (or defaulted to) the admin-configured restriction — see
    // resolveGroupFilter(). Computed BEFORE the KPI query below so the KPI
    // cards respect it too (not just the Top Customers/Items/Groups cards).
    const { hasGrpFilter, grpCond } = resolveGroupFilter(req);

    // Period KPIs — net sales/purchases/customers for the range + previous
    // period. Two shapes: company-wide (header-level, cheap) when no group
    // filter is active — same as before — or LINE-LEVEL (joined to the
    // item's group, only lines in the selected groups count) when a filter
    // is active, so the KPI cards agree with the Top Customers/Items/Groups
    // cards instead of always showing the whole company regardless of the
    // Product Groups filter.
    // NOTE: Open AR / Open AP (outstanding, unpaid amount) are intentionally
    // NEVER group-filtered — SAP tracks PaidToDate at the DOCUMENT level, not
    // per line, so there's no sound way to allocate "how much of this
    // invoice's unpaid balance belongs to which item group" without making
    // up an allocation rule. They always show the whole company's
    // outstanding balance for docs dated in the period.
    const k = hasGrpFilter ? (await hana(`SELECT
      (SELECT ISNULL(SUM(T1.LineTotal),0) FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND ${cond}${grpCond})
      - (SELECT ISNULL(SUM(T1.LineTotal),0) FROM RIN1 T1 JOIN ORIN T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND ${cond}${grpCond}) AS netSales,
      (SELECT ISNULL(SUM(T1.LineTotal),0) FROM PCH1 T1 JOIN OPCH T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND ${cond}${grpCond}) AS purchases,
      (SELECT COUNT(DISTINCT T0.CardCode) FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND ${cond}${grpCond}) AS activeCustomers,
      (SELECT COUNT(DISTINCT T0.DocEntry) FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND ${cond}${grpCond}) AS invoices,
      (SELECT ISNULL(SUM(T1.LineTotal),0) FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND T0.DocDate>='${prevFrom}' AND T0.DocDate<='${prevTo}'${grpCond})
      - (SELECT ISNULL(SUM(T1.LineTotal),0) FROM RIN1 T1 JOIN ORIN T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND T0.DocDate>='${prevFrom}' AND T0.DocDate<='${prevTo}'${grpCond}) AS prevNetSales,
      (SELECT ISNULL(SUM(DocTotal-PaidToDate),0) FROM OINV WHERE DocStatus='O' AND CANCELED='N' AND ${condP}) AS openAR,
      (SELECT ISNULL(SUM(DocTotal-PaidToDate),0) FROM OPCH WHERE DocStatus='O' AND CANCELED='N' AND ${condP}) AS openAP,
      (SELECT ISNULL(SUM(T1.LineTotal),0) FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND T0.U_CustomerCategory='Direct Export' AND ${cond}${grpCond})
      - (SELECT ISNULL(SUM(T1.LineTotal),0) FROM RIN1 T1 JOIN ORIN T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND T0.U_CustomerCategory='Direct Export' AND ${cond}${grpCond}) AS exportDirect,
      (SELECT ISNULL(SUM(T1.LineTotal),0) FROM INV1 T1 JOIN OINV T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND T0.U_CustomerCategory='Indirect Export' AND ${cond}${grpCond})
      - (SELECT ISNULL(SUM(T1.LineTotal),0) FROM RIN1 T1 JOIN ORIN T0 ON T0.DocEntry=T1.DocEntry JOIN OITM T2 ON T2.ItemCode=T1.ItemCode WHERE T0.CANCELED='N' AND T0.U_CustomerCategory='Indirect Export' AND ${cond}${grpCond}) AS exportIndirect`))[0] || {}
    : (await hana(`SELECT
      (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND ${condP})
      - (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM ORIN WHERE CANCELED='N' AND ${condP}) AS netSales,
      (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OPCH WHERE CANCELED='N' AND ${condP}) AS purchases,
      (SELECT COUNT(DISTINCT CardCode) FROM OINV WHERE CANCELED='N' AND ${condP}) AS activeCustomers,
      (SELECT COUNT(*) FROM OINV WHERE CANCELED='N' AND ${condP}) AS invoices,
      (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND ${condPrev})
      - (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM ORIN WHERE CANCELED='N' AND ${condPrev}) AS prevNetSales,
      (SELECT ISNULL(SUM(DocTotal-PaidToDate),0) FROM OINV WHERE DocStatus='O' AND CANCELED='N' AND ${condP}) AS openAR,
      (SELECT ISNULL(SUM(DocTotal-PaidToDate),0) FROM OPCH WHERE DocStatus='O' AND CANCELED='N' AND ${condP}) AS openAP,
      (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND U_CustomerCategory='Direct Export' AND ${condP})
      - (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM ORIN WHERE CANCELED='N' AND U_CustomerCategory='Direct Export' AND ${condP}) AS exportDirect,
      (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM OINV WHERE CANCELED='N' AND U_CustomerCategory='Indirect Export' AND ${condP})
      - (SELECT ISNULL(SUM(DocTotal-VatSum),0) FROM ORIN WHERE CANCELED='N' AND U_CustomerCategory='Indirect Export' AND ${condP}) AS exportIndirect`))[0] || {};

    // Division Summary — EXACT same segment logic as the Sales Report
    // (routes/reports.js buildRevSubq: G/L-account + item-subtype based,
    // incl. excluded docs). Matches the management figures to the paisa.
    // Now ALSO respects the Product Groups filter: buildRevSubq() is shared
    // with routes/reports.js's Sales Report, so it's never touched directly —
    // instead the group condition is appended into the `dateWhere` string
    // buildRevSubq already splices verbatim into its own WHERE clause, using
    // ITS aliases (t1=OITM, t2=OITB, lowercase — NOT the T1/T2 used by the
    // rest of this route) so it resolves against columns actually in scope.
    let divisions = null;
    try {
      const { buildRevSubq } = require('./reports');
      const divGrp = resolveGroupFilter(req, 't1', 't2');
      const dw = `i.DocDate BETWEEN '${from}' AND '${to}'${divGrp.grpCond}`;
      const div = (await hana(`
        SELECT ROUND(SUM(TM),2) TM, ROUND(SUM(PD),2) PD, ROUND(SUM(ED),2) ED, ROUND(SUM(EI),2) EI,
               ROUND(SUM(TM)+SUM(PD)+SUM(ED)+SUM(EI),2) Total
        FROM ( ${buildRevSubq(dw, false)} UNION ALL ${buildRevSubq(dw, true)} ) CK`))[0];
      if (div) divisions = {
        tm: Number(div.TM) || 0, pd: Number(div.PD) || 0,
        ed: Number(div.ED) || 0, ei: Number(div.EI) || 0,
        total: Number(div.Total) || 0,
      };
    } catch (e) { console.warn('[BOARD] division summary failed (non-fatal):', e.message); }

    // Optional per-card search text (?custQ= / ?itemQ= / ?grpQ=) — searched
    // rows are still ranked by sales; quote-escaped and length-capped.
    const like  = s => String(s || '').replace(/'/g, "''").slice(0, 60).trim();
    const custQ = like(req.query.custQ), itemQ = like(req.query.itemQ), grpQ = like(req.query.grpQ);
    const custCond = custQ ? ` AND T0.CardName LIKE '%${custQ}%'` : '';
    const itemCond = itemQ ? ` AND (T1.ItemCode LIKE '%${itemQ}%' OR T1.Dscription LIKE '%${itemQ}%')` : '';
    const grpNameCond = grpQ ? ` AND T3.ItmsGrpNam LIKE '%${grpQ}%'` : '';

    // Top customers: header totals normally; when a group filter is active the
    // sum has to be line-level (only lines of the selected groups count).
    const topCustomers = hasGrpFilter
      ? await hana(`
        SELECT TOP ${custQ?10:6} T0.CardName AS name, SUM(T1.LineTotal) AS total
        FROM INV1 T1
        JOIN OINV T0 ON T0.DocEntry=T1.DocEntry
        JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
        WHERE T0.CANCELED='N' AND ${cond}${grpCond}${custCond}
        GROUP BY T0.CardName ORDER BY total DESC`)
      : await hana(`
        SELECT TOP ${custQ?10:6} T0.CardName AS name, SUM(T0.DocTotal-T0.VatSum) AS total
        FROM OINV T0 WHERE T0.CANCELED='N' AND ${cond}${custCond}
        GROUP BY T0.CardName ORDER BY total DESC`);
    const topItems = await hana(`
      SELECT TOP ${itemQ?10:6} T1.ItemCode AS code, MAX(T1.Dscription) AS name, SUM(T1.LineTotal) AS total
      FROM INV1 T1
      JOIN OINV T0 ON T0.DocEntry=T1.DocEntry
      JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
      WHERE T0.CANCELED='N' AND ${cond}${grpCond}${itemCond}
      GROUP BY T1.ItemCode ORDER BY total DESC`);
    // Product Groups — same hygiene as the Sales Report (net of credit notes,
    // excluded docs, status condition) and SPLIT Domestic vs Export per group
    // (export = the report's export revenue accounts), so the domestic part of
    // FG BLOOD BAG reconciles with the Division Summary's Blood Bag (TM).
    const { REV_CONST } = require('./reports');
    const EXP_ACCTS = REV_CONST.EXCL_ED + ',' + REV_CONST.EXCL_EI;
    const invExtras = ` AND T0.DocNum NOT IN (${REV_CONST.EXCL_DOCS})
      AND (T3.ItmsGrpNam IN ('FG CAPD','FG CAPD Accessories') OR (T0.DocStatus<>'C' OR T0.InvntSttus='O'))`;
    const grpLeg = (tbl, hdr, sign, extras) => `
      SELECT CASE WHEN T2.ItmsGrpCod=${EQUIP_GRP} AND T1.ItemCode IN (${CAPD_EQUIP}) THEN 'FG EQUIPMENT CAPD'
                  WHEN T2.ItmsGrpCod=${EQUIP_GRP} THEN 'FG EQUIPMENT BB'
                  ELSE T3.ItmsGrpNam END AS name,
             CASE WHEN T1.AcctCode IN (${EXP_ACCTS}) THEN 0 ELSE ${sign}T1.LineTotal END AS domestic,
             CASE WHEN T1.AcctCode IN (${EXP_ACCTS}) THEN ${sign}T1.LineTotal ELSE 0 END AS export
      FROM ${tbl} T1
      JOIN ${hdr} T0 ON T0.DocEntry=T1.DocEntry
      JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
      JOIN OITB T3 ON T3.ItmsGrpCod=T2.ItmsGrpCod
      WHERE T0.CANCELED='N' AND ${cond}${grpCond}${grpNameCond}${extras}`;
    const topGroups = await hana(`
      SELECT TOP ${grpQ?12:8} name, SUM(domestic) AS domestic, SUM(export) AS export, SUM(domestic)+SUM(export) AS total
      FROM ( ${grpLeg('INV1','OINV','',invExtras)} UNION ALL ${grpLeg('RIN1','ORIN','-','')} ) x
      GROUP BY name ORDER BY total DESC`);

    res.json({ success: true, data: {
      from, to, prevFrom, prevTo,
      divisions,
      kpi: {
        netSales:        Number(k.netSales) || 0,
        purchases:       Number(k.purchases) || 0,
        activeCustomers: Number(k.activeCustomers) || 0,
        invoices:        Number(k.invoices) || 0,
        prevNetSales:    Number(k.prevNetSales) || 0,
        openAR:          Number(k.openAR) || 0,
        openAP:          Number(k.openAP) || 0,
        exportDirect:    Number(k.exportDirect) || 0,
        exportIndirect:  Number(k.exportIndirect) || 0,
      },
      topCustomers: topCustomers.map(r => ({ name: r.name, total: Number(r.total) || 0 })),
      topItems:     topItems.map(r => ({ code: r.code, name: r.name, total: Number(r.total) || 0 })),
      topGroups:    topGroups.map(r => ({ name: r.name, domestic: Number(r.domestic) || 0, export: Number(r.export) || 0, total: Number(r.total) || 0 })),
    }});
  } catch (err) {
    console.error('[BOARD] breakdown failed:', err.message);
    res.status(500).json({ success: false, message: err.message });
  }
});

module.exports = router;