namespace AccountsDAL.Query.AccountReports
{
    // Trial Balance — genuinely new GB5 design, not a port of a legacy report. Legacy's own
    // GetTrialBalance (Reports.svc.cs -> ReportDAL.GetTrialBalance) is a dead stub that returns an
    // empty list; the real TB-shaped logic in GB4 lives scattered across GetAccountSummary/
    // GetTBOUWiseReport/GetTBDimensionReport, all of which sum TVOUCHERDETAIL by account then roll up
    // through MACCOUNT -> MACCOUNTGROUP -> MACCOUNTSCHEDULE — the same approach used here.
    //
    // ── Phase 2 accounting semantics (added after the MVP shipped) ──────────────────────────────
    // Real Trial Balances distinguish two account natures:
    //   - Balance-Sheet-nature accounts (ACCOUNTSCHEDULENATURE 0=Liability, 1=Asset): cumulative net
    //     balance since inception, unchanged from the MVP.
    //   - P&L-nature accounts (2=Direct Income, 3=Direct Expense, 4=Indirect Income, 5=Indirect
    //     Expense): net balance ONLY from the start of the financial year containing @AsOnDate — they
    //     reset every year. @FYStartDate is resolved once per call via GET_FY_START_DATE (MPERIOD rows
    //     ARE financial years in this schema, confirmed live: e.g. "FY-24-25" spans 2024-04-01 to
    //     2025-03-31 — PERIODGROUP=0 is the real, non-overlapping FY sequence; a PERIODGROUP=1 row
    //     ("NY24-25") spans two fiscal years and must be excluded when resolving "which FY contains
    //     this date").
    // The date-range distinction lives INSIDE each SUM's CASE expression (NET_EXPR), not the WHERE
    // clause — WHERE applies one filter to every row regardless of account nature, but the lower date
    // bound genuinely differs per row's own ACCOUNTSCHEDULENATURE, so it has to be evaluated per-row
    // inside the aggregate.
    //
    // There is no P&L-transfer/retained-earnings mechanism anywhere in this codebase (GB5 or legacy)
    // — confirmed by a full-repo search. Per the user's own accounting design (mirrors how legacy
    // avoided a permanently-unbalanced Balance Sheet before a formal year-end transfer): a real, fixed
    // MACCOUNT row ("Profit and Loss Account (Retained Earnings)", ACCOUNTID -1399900600, under the
    // existing live "RESERVES AND SURPLUS" group) is seeded by migration. Nothing is ever journaled to
    // it — GET_RETAINED_EARNINGS_PLUG computes cumulative net P&L for all P&L-nature activity dated
    // strictly BEFORE @FYStartDate, and the DAL attributes that figure to this account as a synthetic
    // row (C#-side, not a SQL UNION — see AccountTrialBalanceReportDAL.cs for why: the group currently
    // has no other real accounts, so a plain appended row is exactly correct, not an approximation).
    // Once this plug is in place, whole-book TotalDebit should exactly equal TotalCredit — if it
    // doesn't, that's a genuine initial-data-setup gap, surfaced as its own "Difference in Opening
    // Balance" row (also synthetic, computed in the DAL, not its own SQL query).
    //
    // GroupType (reusing AccountReportCriteria.GroupType, whose meaning is already report-local per
    // the existing header comment on that field): 0 = Account, 1 = AccountGroup, 2 = AccountSchedule,
    // 3 = SubAccount (a control-account ROLLUP, not a drill-down — see GET_TRIAL_BALANCE_SUBACCOUNT).
    // Four distinct query constants (not one dynamic GROUP BY) because the SELECT/GROUP BY column
    // list genuinely changes shape per level — same reasoning as legacy's Fund Flow Type variants
    // being separate hardcoded SQL strings, not one query with conditional columns.
    //
    // Debit/Credit split: standard universal TB rule — net (Debit - Credit) per row; positive net
    // goes in the Debit column, negative net (as a positive number) goes in the Credit column. Total
    // Debit column must equal total Credit column across the whole book — the built-in TB sanity
    // check, verified live rather than assumed.
    public static class TrialBalanceReportQB
    {
        // Same currency-basis selector as AccountLedgerReportsQB, reused verbatim for consistency —
        // 0 = OU/base (VOUCHERAMOUNT), 1 = Transaction (VOUCHERAMOUNTFC), 2 = Customer/party
        // (VOUCHERAMOUNTAC), 3 = Group (VOUCHERAMOUNTGC).
        private const string BASE_AMOUNT_EXPR = @"
            CASE @CurrencyBasis
                WHEN 1 THEN VD.VOUCHERAMOUNTFC
                WHEN 2 THEN VD.VOUCHERAMOUNTAC
                WHEN 3 THEN VD.VOUCHERAMOUNTGC
                ELSE VD.VOUCHERAMOUNT
            END";

        // Nature-aware net: P&L-nature rows (2-5) dated before @FYStartDate contribute 0 (they reset
        // for the year and are instead swept into the Retained Earnings plug); every other row nets
        // normally. Balance-Sheet-nature rows (0,1) are never excluded — cumulative since inception.
        private const string NET_EXPR = @"
            SUM(CASE
                WHEN ACC.ACCOUNTSCHEDULENATURE IN (2,3,4,5) AND V.VOUCHERDATE < @FYStartDate THEN 0
                WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @"
                ELSE -(" + BASE_AMOUNT_EXPR + @")
            END)";

        // Mandatory OU access-rights ceiling, identical block reused verbatim across every report in
        // this family (AccountLedgerReportsQB, AccountRegisterReportsQB, etc.).
        private const string OU_ACCESS_FILTER = @"
  AND V.OUID IN (
        SELECT UAR.OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
        UNION
        SELECT OGD.OUID FROM MUSERACCESSRIGHTS UAR2
        INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
        WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1
  )";

        private const string FROM_JOIN = @"
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
LEFT JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID";

        private const string WHERE_CLAUSE = @"
WHERE V.VOUCHERDATE <= @AsOnDate" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        // GroupType = 0 (Account level)
        public const string GET_TRIAL_BALANCE_ACCOUNT = @"
SELECT
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    AGS.ACCOUNTSCHEDULENATURE AS AccountScheduleNature,
    CASE AGS.ACCOUNTSCHEDULENATURE
        WHEN 0 THEN 'Liability'
        WHEN 1 THEN 'Asset'
        WHEN 2 THEN 'Income'
        WHEN 3 THEN 'Expense'
        WHEN 4 THEN 'Income'
        WHEN 5 THEN 'Expense'
        ELSE 'Asset'
    END AS AccountScheduleTypeName,
    CASE WHEN " + NET_EXPR + @" > 0 THEN " + NET_EXPR + @" ELSE 0 END AS Debit,
    CASE WHEN " + NET_EXPR + @" < 0 THEN -(" + NET_EXPR + @") ELSE 0 END AS Credit" + FROM_JOIN + WHERE_CLAUSE + @"
GROUP BY VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME, ACC.SORTORDER,
         ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.ACCOUNTSCHEDULENATURE, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // GroupType = 1 (AccountGroup level)
        public const string GET_TRIAL_BALANCE_ACCOUNTGROUP = @"
SELECT
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    CASE WHEN " + NET_EXPR + @" > 0 THEN " + NET_EXPR + @" ELSE 0 END AS Debit,
    CASE WHEN " + NET_EXPR + @" < 0 THEN -(" + NET_EXPR + @") ELSE 0 END AS Credit" + FROM_JOIN + WHERE_CLAUSE + @"
GROUP BY ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // GroupType = 2 (AccountSchedule level)
        public const string GET_TRIAL_BALANCE_ACCOUNTSCHEDULE = @"
SELECT
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    CASE WHEN " + NET_EXPR + @" > 0 THEN " + NET_EXPR + @" ELSE 0 END AS Debit,
    CASE WHEN " + NET_EXPR + @" < 0 THEN -(" + NET_EXPR + @") ELSE 0 END AS Credit" + FROM_JOIN + WHERE_CLAUSE + @"
GROUP BY AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // GroupType = 3 (SubAccount level — a control-account ROLLUP, not a drill-down). Corrected
        // after an initial misread of TVOUCHERDETAIL.SUBACCOUNTID (a bare self-referencing FK with no
        // master table, confirmed via research — NOT what "subaccount level" means here). The real
        // mechanism: MACCOUNT.CONTROLACCOUNTID (sentinel -1 = standalone, confirmed live) — a control
        // account (e.g. "SL Control AC Creditors") is a parent; subledger accounts (real customers/
        // vendors) point to it via their own CONTROLACCOUNTID. Standalone accounts show individually;
        // every account WITH a control account collapses into one row for that control account.
        private const string EFFECTIVE_ACCOUNT_EXPR = "CASE WHEN ACC.CONTROLACCOUNTID <> -1 THEN ACC.CONTROLACCOUNTID ELSE ACC.ACCOUNTID END";

        public const string GET_TRIAL_BALANCE_SUBACCOUNT = @"
SELECT
    " + EFFECTIVE_ACCOUNT_EXPR + @" AS AccountId,
    CTRL.ACCOUNTCODE AS AccountCode,
    CTRL.ACCOUNTNAME AS AccountName,
    CTRL.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    CASE WHEN " + NET_EXPR + @" > 0 THEN " + NET_EXPR + @" ELSE 0 END AS Debit,
    CASE WHEN " + NET_EXPR + @" < 0 THEN -(" + NET_EXPR + @") ELSE 0 END AS Credit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MACCOUNT CTRL ON CTRL.ACCOUNTID = " + EFFECTIVE_ACCOUNT_EXPR + @"
LEFT JOIN MACCOUNTGROUP AG ON CTRL.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID" + WHERE_CLAUSE + @"
GROUP BY " + EFFECTIVE_ACCOUNT_EXPR + @", CTRL.ACCOUNTCODE, CTRL.ACCOUNTNAME, CTRL.SORTORDER,
         CTRL.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Totals — always at account grain regardless of the requested display GroupType (the total
        // Debit/Credit for the whole book doesn't change based on how it's rolled up for display).
        // Derived-table subquery (not a leading CTE) for consistency with the no-CTE convention,
        // even though this specific query runs via QuerySingleAsync, not QueryPagedAsync. Excludes the
        // Retained Earnings plug (added by the DAL in C#, see class header comment).
        public const string GET_TRIAL_BALANCE_TOTALS = @"
SELECT
    SUM(CASE WHEN X.NetAmount > 0 THEN X.NetAmount ELSE 0 END) AS TotalDebit,
    SUM(CASE WHEN X.NetAmount < 0 THEN -X.NetAmount ELSE 0 END) AS TotalCredit
FROM (
    SELECT " + NET_EXPR + @" AS NetAmount" + FROM_JOIN + WHERE_CLAUSE + @"
    GROUP BY VD.VOUCHERACCOUNTID
) X";

        // Financial-year start for @AsOnDate — MPERIOD rows ARE financial years in this schema
        // (confirmed live), PERIODGROUP=0 is the real non-overlapping FY sequence (a PERIODGROUP=1
        // row is known to span two fiscal years and must be excluded). ORDER BY FROMDATE DESC + TOP 1
        // picks the most specific/latest-starting matching row if more than one somehow overlaps.
        public const string GET_FY_START_DATE = @"
SELECT TOP 1 FROMDATE
FROM MPERIOD
WHERE @AsOnDate BETWEEN FROMDATE AND TODATE
  AND PERIODGROUP = 0
ORDER BY FROMDATE DESC";

        // Retained Earnings plug — cumulative net P&L (all nature 2-5 activity) dated strictly BEFORE
        // @FYStartDate. Same sign convention as NET_EXPR: positive = net Debit (accumulated loss),
        // negative = net Credit (accumulated profit) — the DAL applies the same Debit/Credit split
        // rule used everywhere else in this report to turn this one number into a display row.
        public const string GET_RETAINED_EARNINGS_PLUG = @"
SELECT ISNULL(SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE -(" + BASE_AMOUNT_EXPR + @") END), 0) AS PlugNet
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE ACC.ACCOUNTSCHEDULENATURE IN (2,3,4,5)
  AND V.VOUCHERDATE < @FYStartDate" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        // Lookup for the fixed Retained Earnings account's display fields (code/name/group/schedule),
        // so the DAL can build a correctly-shaped synthetic row without hardcoding display text in C#.
        public const string GET_RETAINED_EARNINGS_ACCOUNT_INFO = @"
SELECT
    ACC.ACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName
FROM MACCOUNT ACC
LEFT JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID
WHERE ACC.ACCOUNTID = @RetainedEarningsAccountId";

        // ══════════════════════════════════════════════════════════════════════════════════════
        // ── Phase 3: Detailed / Periodic / Comparative ──────────────────────────────────────────
        // ══════════════════════════════════════════════════════════════════════════════════════

        // Detailed variant — Opening/Debit/Credit/Closing per row, matching the established
        // AccountLedgerSummary convention: OpeningBalance/ClosingBalance are SIGNED net figures
        // (positive = Dr, negative = Cr), while Debit/Credit are RAW period totals (both can be
        // nonzero for the same row — NOT netted against each other, unlike the Summary variant's
        // Debit/Credit). ClosingBalance = OpeningBalance + Debit - Credit, expressed directly in
        // SQL rather than a third big expression.
        //
        // OPENING_NET_EXPR reuses the same nature-aware idea as NET_EXPR (P&L-nature rows before
        // @FYStartDate contribute 0), with an ADDITIONAL ceiling excluding anything on/after
        // @PeriodFromDate — i.e. "the balance immediately before this period started."
        private const string OPENING_NET_EXPR = @"
            SUM(CASE
                WHEN V.VOUCHERDATE >= @PeriodFromDate THEN 0
                WHEN ACC.ACCOUNTSCHEDULENATURE IN (2,3,4,5) AND V.VOUCHERDATE < @FYStartDate THEN 0
                WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @"
                ELSE -(" + BASE_AMOUNT_EXPR + @")
            END)";

        private const string PERIOD_DEBIT_EXPR = @"
            SUM(CASE WHEN V.VOUCHERDATE BETWEEN @PeriodFromDate AND @PeriodToDate AND VD.DETAILTYPE = 0
                     THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END)";

        private const string PERIOD_CREDIT_EXPR = @"
            SUM(CASE WHEN V.VOUCHERDATE BETWEEN @PeriodFromDate AND @PeriodToDate AND VD.DETAILTYPE = 1
                     THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END)";

        // Widest ceiling for the whole query is @PeriodToDate (both Opening and Period expressions
        // apply their own finer-grained bounds inside their CASE, same reasoning as Phase 2's
        // @FYStartDate floor living inside NET_EXPR rather than the WHERE clause).
        private const string WHERE_CLAUSE_DETAILED = @"
WHERE V.VOUCHERDATE <= @PeriodToDate" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        private const string OPENING_CLOSING_COLUMNS = @"
    " + OPENING_NET_EXPR + @" AS OpeningBalance,
    " + PERIOD_DEBIT_EXPR + @" AS Debit,
    " + PERIOD_CREDIT_EXPR + @" AS Credit,
    " + OPENING_NET_EXPR + @" + " + PERIOD_DEBIT_EXPR + @" - " + PERIOD_CREDIT_EXPR + @" AS ClosingBalance";

        public const string GET_TRIAL_BALANCE_DETAIL_ACCOUNT = @"
SELECT
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    AGS.ACCOUNTSCHEDULENATURE AS AccountScheduleNature,
    CASE AGS.ACCOUNTSCHEDULENATURE
        WHEN 0 THEN 'Liability'
        WHEN 1 THEN 'Asset'
        WHEN 2 THEN 'Income'
        WHEN 3 THEN 'Expense'
        WHEN 4 THEN 'Income'
        WHEN 5 THEN 'Expense'
        ELSE 'Asset'
    END AS AccountScheduleTypeName," + OPENING_CLOSING_COLUMNS + FROM_JOIN + WHERE_CLAUSE_DETAILED + @"
GROUP BY VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME, ACC.SORTORDER,
         ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.ACCOUNTSCHEDULENATURE, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_TRIAL_BALANCE_DETAIL_ACCOUNTGROUP = @"
SELECT
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName," + OPENING_CLOSING_COLUMNS + FROM_JOIN + WHERE_CLAUSE_DETAILED + @"
GROUP BY ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_TRIAL_BALANCE_DETAIL_ACCOUNTSCHEDULE = @"
SELECT
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName," + OPENING_CLOSING_COLUMNS + FROM_JOIN + WHERE_CLAUSE_DETAILED + @"
GROUP BY AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_TRIAL_BALANCE_DETAIL_SUBACCOUNT = @"
SELECT
    " + EFFECTIVE_ACCOUNT_EXPR + @" AS AccountId,
    CTRL.ACCOUNTCODE AS AccountCode,
    CTRL.ACCOUNTNAME AS AccountName,
    CTRL.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName," + OPENING_CLOSING_COLUMNS + @"
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MACCOUNT CTRL ON CTRL.ACCOUNTID = " + EFFECTIVE_ACCOUNT_EXPR + @"
LEFT JOIN MACCOUNTGROUP AG ON CTRL.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID" + WHERE_CLAUSE_DETAILED + @"
GROUP BY " + EFFECTIVE_ACCOUNT_EXPR + @", CTRL.ACCOUNTCODE, CTRL.ACCOUNTNAME, CTRL.SORTORDER,
         CTRL.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.SORTORDER
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Periodic — resolves the MGBPERIODDETAIL sub-period row containing @AsOnDate at the
        // caller's chosen @PeriodType (default 10 = FN-Monthly, always caller-selectable — user's
        // own instruction: never hardcode this). Confirmed live PERIODTYPE values: 0=Daily,
        // 1=Weekly, 2=Monthly, 3=Quarterly, 4=HalfYearly, 5=Yearly, 6=48Week, 7=FN-48WeekFortnight,
        // 8=FN-Daily, 9=FN-Weekly, 10=FN-Monthly, 11=FN-Quarterly, 12=FN-HalfYearly — undocumented
        // anywhere in code, discovered via GROUP BY PERIODTYPE against live GB5DEMO data. Join
        // idiom mirrors legacy's own VOUCHER_PERIODTYPE_REPORT pattern (AccountsReportQueryBuilder.cs)
        // and GB5's own EmployeeQB.GET_HEAD_COUNT_REPORT — both use the same
        // "date BETWEEN sub-period FROMDATE/TODATE" containment idiom.
        public const string GET_PERIOD_RANGE = @"
SELECT TOP 1 PD.SUBPERIODFROMDATE AS PeriodFromDate, PD.SUBPERIODTODATE AS PeriodToDate
FROM MGBPERIOD P
INNER JOIN MGBPERIODDETAIL PD ON P.GBPERIODID = PD.GBPERIODID
WHERE P.PERIODTYPE = @PeriodType
  AND @AsOnDate BETWEEN PD.SUBPERIODFROMDATE AND PD.SUBPERIODTODATE
ORDER BY PD.SUBPERIODFROMDATE DESC";

        // Comparative — Account level, Summary shape (Debit/Credit, not Opening/Closing) for this
        // pass; {AxisJoin}/{AxisIdColumn}/{AxisCodeColumn}/{AxisNameColumn} are substituted by the
        // DAL from a small fixed lookup table (never raw user input — same safety pattern as
        // AccountReportSortBuilder's allow-listed field->column map), one of: OU/Company/Branch/
        // Division/OUGroup. Returns a FLAT (account, axis-value) row per combination, not a
        // pre-pivoted grid — see TrialBalanceReportQB.cs's class header / the plan file for why
        // (dynamic-column SQL PIVOT would need dynamic SQL for an unbounded column count; reshaping
        // into a grid is a presentation-layer concern).
        public const string GET_TRIAL_BALANCE_COMPARATIVE = @"
SELECT
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    {AxisIdColumn} AS AxisValueId,
    {AxisCodeColumn} AS AxisValueCode,
    {AxisNameColumn} AS AxisValueName,
    CASE WHEN " + NET_EXPR + @" > 0 THEN " + NET_EXPR + @" ELSE 0 END AS Debit,
    CASE WHEN " + NET_EXPR + @" < 0 THEN -(" + NET_EXPR + @") ELSE 0 END AS Credit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
{AxisJoin}" + WHERE_CLAUSE + @"
GROUP BY VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME, {AxisIdColumn}, {AxisCodeColumn}, {AxisNameColumn}
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Periodic — Account level, Summary shape. Flat (Account, PeriodBucket) row per combination,
        // same "not pre-pivoted" philosophy as GET_TRIAL_BALANCE_COMPARATIVE (meant to feed
        // GB5Shared.Export.Pivot.DataPivotEngine, not to pre-pivot in SQL). Buckets come from
        // MGBPERIOD/MGBPERIODDETAIL at the caller's chosen @PeriodType (same field/default as the
        // rest of this report family — see AccountReportCriteria.PeriodType), bounded by
        // @PeriodFromDate/@PeriodToDate (the overall range to break down, NOT a single resolved
        // sub-period like GET_PERIOD_RANGE resolves for Detail's as-on mode).
        //
        // Debit/Credit/NetAmount are this bucket's own PERIOD MOVEMENT only (NOT a running/cumulative
        // balance) — same convention as the Comparative query. A running month-end CLOSING BALANCE
        // variant (needed for a true "TB as of each period-end" pivot) is a natural follow-up once
        // this ships, not built here — it needs a window-function running total plus the same
        // nature-aware FY-reset handling NET_EXPR/OPENING_NET_EXPR already do for a single date,
        // which is real added complexity beyond what's been asked for so far.
        public const string GET_TRIAL_BALANCE_PERIODIC = @"
SELECT
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    AGS.ACCOUNTSCHEDULENATURE AS AccountScheduleNature,
    CASE AGS.ACCOUNTSCHEDULENATURE
        WHEN 0 THEN 'Liability'
        WHEN 1 THEN 'Asset'
        WHEN 2 THEN 'Income'
        WHEN 3 THEN 'Expense'
        WHEN 4 THEN 'Income'
        WHEN 5 THEN 'Expense'
        ELSE 'Asset'
    END AS AccountScheduleTypeName,
    PD.GBPERIODDETAILID AS PeriodDetailId,
    PD.SUBPERIODNAME AS PeriodLabel,
    PD.SUBPERIODFROMDATE AS PeriodFromDate,
    PD.SUBPERIODTODATE AS PeriodToDate,
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS Debit,
    SUM(CASE WHEN VD.DETAILTYPE = 1 THEN " + BASE_AMOUNT_EXPR + @" ELSE 0 END) AS Credit,
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE -(" + BASE_AMOUNT_EXPR + @") END) AS NetAmount
FROM MGBPERIOD P
INNER JOIN MGBPERIODDETAIL PD ON P.GBPERIODID = PD.GBPERIODID
INNER JOIN TVOUCHER V ON V.VOUCHERDATE BETWEEN PD.SUBPERIODFROMDATE AND PD.SUBPERIODTODATE
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
LEFT JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE P.PERIODTYPE = @PeriodType
  AND PD.SUBPERIODFROMDATE >= @PeriodFromDate
  AND PD.SUBPERIODTODATE <= @PeriodToDate" + OU_ACCESS_FILTER + @"
  {DynamicFilter}
GROUP BY PD.GBPERIODDETAILID, PD.SUBPERIODNAME, PD.SUBPERIODFROMDATE, PD.SUBPERIODTODATE,
         VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME, ACC.SORTORDER,
         ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME, AG.SORTORDER,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME, AGS.ACCOUNTSCHEDULENATURE, AGS.SORTORDER
ORDER BY PD.SUBPERIODFROMDATE, {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
    }
}