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

// ── GET /api/general-ledger/accounts ─────────────────────────────────────────
router.get('/accounts', verifyToken, async (_req, res) => {
  try {
    const pool = await getPool();
    const result = await pool.request().query(`
      SELECT AcctCode, AcctName, GroupMask,
        CASE GroupMask
          WHEN 1 THEN 'Assets'
          WHEN 2 THEN 'Liabilities'
          WHEN 3 THEN 'Equity'
          WHEN 4 THEN 'Revenue'
          WHEN 5 THEN 'Expenditure'
          ELSE 'Group ' + CAST(GroupMask AS VARCHAR)
        END AS GroupLabel
      FROM OACT
      ORDER BY GroupMask, AcctCode
    `);
    res.json({ success: true, data: result.recordset });
  } catch (err) {
    console.error('[GeneralLedger] accounts error:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

// ── GET /api/general-ledger/data ─────────────────────────────────────────────
router.get('/data', verifyToken, async (req, res) => {
  try {
    const { from, to, accounts } = req.query;
    if (!from || !to) return res.status(400).json({ success: false, message: 'from and to dates required' });
    if (!accounts)    return res.status(400).json({ success: false, message: 'accounts required' });

    // Sanitise: only allow numeric account codes (prevent SQL injection)
    const acctList = accounts.split(',').map(c => c.trim()).filter(c => /^\d+$/.test(c));
    if (acctList.length === 0) return res.status(400).json({ success: false, message: 'No valid account codes provided' });

    const inClause = acctList.map(c => `'${c}'`).join(',');

    const pool = await getPool();

    // Opening balances
    const obResult = await pool.request()
      .input('from', from)
      .query(`
        SELECT T0.Account AS acct_code,
          SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS opening_balance
        FROM JDT1 T0
        WHERE T0.RefDate < @from
          AND T0.Account IN (${inClause})
        GROUP BY T0.Account
      `);

    const openingBalances = {};
    for (const row of obResult.recordset) {
      openingBalances[row.acct_code] = row.opening_balance;
    }

    // Transaction data
    const txResult = await pool.request()
      .input('from', from)
      .input('to', to)
      .query(`
        SELECT
          T2.AcctCode   AS acct_code,
          T2.AcctName   AS acct_name,
          T0.RefDate    AS posting_date,
          T0.DueDate    AS due_date,
          T0.TaxDate    AS document_date,
          T1.Series     AS series,
          T1.Number     AS doc_no,
          T0.TransId    AS trans_no,
          T0.Line_ID    AS seq_no,
          T0.LineMemo   AS remarks,
          T0.ContraAct  AS offset_acct,
          T3.AcctName   AS offset_acct_name,
          (T0.Debit - T0.Credit) AS deb_cred,
          T0.Debit      AS debit,
          T0.Credit     AS credit,
          SUM(T0.Debit - T0.Credit) OVER (
            PARTITION BY T0.Account
            ORDER BY T0.RefDate, T0.TransId, T0.Line_ID
          ) AS cumulative_balance,
          T0.Ref1       AS ref1
        FROM JDT1 T0
        INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
        LEFT  JOIN OACT T2 ON T0.Account  = T2.AcctCode
        LEFT  JOIN OACT T3 ON T0.ContraAct = T3.AcctCode
        WHERE T0.RefDate BETWEEN @from AND @to
          AND T0.Account IN (${inClause})
        ORDER BY T0.Account, T0.RefDate, T0.TransId, T0.Line_ID
      `);

    res.json({ success: true, data: txResult.recordset, openingBalances });
  } catch (err) {
    console.error('[GeneralLedger] data error:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

module.exports = router;