namespace AccountsDAL.Query.AccountReports
{
    // Profit & Loss — a faithful port of legacy's real, working GetPandLReportVertical
    // (AccountsReportsDAL.cs:9683+), not a from-scratch design like Trial Balance was. Legacy builds
    // four separate nature-filtered queries (Direct Income=2, Direct Expense=3, Indirect Income=4,
    // Indirect Expense=5) that together are functionally the Trading Account section (Direct) and the
    // P&L Account section (Indirect) — same MACCOUNT -> MACCOUNTGROUP -> MACCOUNTSCHEDULE rollup used
    // by Trial Balance, just filtered to ACCOUNTSCHEDULENATURE IN (2,3,4,5) instead of (0,1).
    //
    // Unlike Trial Balance, P&L needs NO financial-year-reset logic and NO Retained Earnings plug —
    // both of those exist specifically because TB computes a point-in-time snapshot (AsOnDate) where
    // P&L-nature accounts have no "since inception" balance of their own. P&L itself is inherently
    // period-bound (the caller supplies PeriodFromDate/PeriodToDate directly, like
    // Register/Ledger/Settlement do), so the period range IS the reset boundary — no extra machinery
    // needed.
    //
    // Per the user's explicit scoping call: ship as ONE report (Account/AccountGroup/AccountSchedule
    // levels), no separate ACCOUNTGROUPNATURE-based dashboard-waterfall variant this pass.
    //
    // GroupType: 0 = Account, 1 = AccountGroup, 2 = AccountSchedule (Trial Balance's SubAccount/
    // Control-Account level is deferred, same as Comparative/Periodic — natural low-cost follow-ups
    // reusing already-built TB Phase 3 machinery once this MVP lands).
    //
    // Debit/Credit split: same universal net-sign rule as Trial Balance — positive net (Debit-normal,
    // i.e. Expense exceeding its own Income-side conventions) goes in Debit, negative net goes in
    // Credit. Section column separates Trading (Direct Income/Expense) from P&L (Indirect
    // Income/Expense) for FE presentation.
    public static class ProfitLossReportQB
    {
        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";

        private const string NET_EXPR = @"
            SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE -(" + BASE_AMOUNT_EXPR + @") END)";

        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 BETWEEN @PeriodFromDate AND @PeriodToDate
  AND ACC.ACCOUNTSCHEDULENATURE IN (2,3,4,5)" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        private const string SECTION_EXPR = "CASE WHEN ACC.ACCOUNTSCHEDULENATURE IN (2,3) THEN 'Trading' ELSE 'P&L' END";

        // Income (nature 2,4) vs Expense (nature 3,5) — nested inside Section as its own
        // ISGROUPCOLUMN level so the existing generic grouping/subtotal engine
        // (GB5Shared.Export.GroupTreeBuilder) produces "Total Income"/"Total Expense" rows per
        // section for free. Also what Phase 3's Horizontal (Income|Expense side-by-side) split uses.
        private const string NATURE_TYPE_EXPR = "CASE WHEN ACC.ACCOUNTSCHEDULENATURE IN (2,4) THEN 'Income' ELSE 'Expense' END";

        // Sub-account/control-account rollup — ported verbatim from TrialBalanceReportQB's
        // EFFECTIVE_ACCOUNT_EXPR/CONTROLACCOUNTID pattern (GET_TRIAL_BALANCE_SUBACCOUNT). Here it
        // gives the "Account" level of the Schedule->Group->Account->Sub-account hierarchy the user
        // asked for: CTRL is the control/parent account ("Account" level); ACC itself (the real
        // posted-to account) is the "Sub-account" level nested beneath it. When an account has no
        // control account (CONTROLACCOUNTID = -1, the standalone sentinel), CTRL and ACC are the
        // same row, so Account and Sub-account collapse to one level for that account — same
        // single-child-chain shape Schedule/Group/Account already show today for a group with only
        // one child, not a new rendering concern.
        private const string EFFECTIVE_ACCOUNT_EXPR = "CASE WHEN ACC.CONTROLACCOUNTID <> -1 THEN ACC.CONTROLACCOUNTID ELSE ACC.ACCOUNTID END";

        // Ordering ensures rows are pre-sorted the same way the ISGROUPCOLUMN chain groups them:
        // Section (Trading before P&L) -> NatureType (Income before Expense) -> Schedule -> Group ->
        // Account (control) -> Sub-account (leaf). Section/NatureType need explicit numeric CASE
        // ordering rather than the string alias itself ('P&L' < 'Trading' alphabetically, which is
        // backwards for a Trading-then-P&L statement).
        private const string DEFAULT_ORDER_BY = @"
            CASE WHEN ACC.ACCOUNTSCHEDULENATURE IN (2,3) THEN 0 ELSE 1 END,
            CASE WHEN ACC.ACCOUNTSCHEDULENATURE IN (2,4) THEN 0 ELSE 1 END,
            AGS.ACCOUNTSCHEDULECODE, AG.ACCOUNTGROUPCODE, CTRL.ACCOUNTCODE, ACC.ACCOUNTCODE";

        // Unified hierarchy query — replaces the 3-way GroupType dispatch (GET_PROFIT_LOSS_ACCOUNT/
        // ACCOUNTGROUP/ACCOUNTSCHEDULE below, kept only as legacy fallbacks, no longer wired to the
        // live menu) with one flat, leaf-grain row per real posted account, carrying every hierarchy
        // level's columns so the report-view's ISGROUPCOLUMN chain (seeded in the companion
        // migration) can roll values up correctly at every level instead of showing account-level
        // amounts everywhere. Modeled directly on TrialBalanceReportQB's GET_TRIAL_BALANCE_ACCOUNT/
        // GET_TRIAL_BALANCE_SUBACCOUNT shape.
        public const string GET_PROFIT_LOSS_DETAIL = @"
SELECT
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    " + EFFECTIVE_ACCOUNT_EXPR + @" AS EffectiveAccountId,
    CTRL.ACCOUNTCODE AS EffectiveAccountCode,
    CTRL.ACCOUNTNAME AS EffectiveAccountName,
    CTRL.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    ACC.ACCOUNTSCHEDULENATURE AS AccountScheduleNature,
    " + SECTION_EXPR + @" AS Section,
    " + NATURE_TYPE_EXPR + @" AS NatureType,
    'Detail' AS RowType,
    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 VD.VOUCHERACCOUNTID, ACC.ACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME, ACC.CONTROLACCOUNTID,
         CTRL.ACCOUNTCODE, CTRL.ACCOUNTNAME, CTRL.ACCOUNTGROUPID,
         AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME,
         ACC.ACCOUNTSCHEDULENATURE
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string DETAIL_DEFAULT_ORDER_BY = DEFAULT_ORDER_BY;

        // GroupType = 0 (Account level)
        public const string GET_PROFIT_LOSS_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,
    " + SECTION_EXPR + @" AS Section,
    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.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME,
         ACC.ACCOUNTSCHEDULENATURE
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // GroupType = 1 (AccountGroup level)
        public const string GET_PROFIT_LOSS_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,
    " + SECTION_EXPR + @" AS Section,
    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.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME,
         ACC.ACCOUNTSCHEDULENATURE
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // GroupType = 2 (AccountSchedule level)
        public const string GET_PROFIT_LOSS_ACCOUNTSCHEDULE = @"
SELECT
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    " + SECTION_EXPR + @" AS Section,
    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,
         ACC.ACCOUNTSCHEDULENATURE
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Totals — always at account grain regardless of display GroupType, split by Section so the
        // BLL/FE can show Trading (Gross Profit) and P&L (Net Profit) subtotals in addition to the
        // whole-statement Net Profit. Derived-table subquery, not a leading CTE (QueryPagedAsync's
        // COUNT(*) wrap gotcha — same convention as every report in this family).
        public const string GET_PROFIT_LOSS_TOTALS = @"
SELECT
    SUM(CASE WHEN X.Section = 'Trading' AND X.NetAmount > 0 THEN X.NetAmount ELSE 0 END) AS TradingDebit,
    SUM(CASE WHEN X.Section = 'Trading' AND X.NetAmount < 0 THEN -X.NetAmount ELSE 0 END) AS TradingCredit,
    SUM(CASE WHEN X.Section = 'P&L' AND X.NetAmount > 0 THEN X.NetAmount ELSE 0 END) AS PandLDebit,
    SUM(CASE WHEN X.Section = 'P&L' AND X.NetAmount < 0 THEN -X.NetAmount ELSE 0 END) AS PandLCredit
FROM (
    SELECT " + SECTION_EXPR + @" AS Section, " + NET_EXPR + @" AS NetAmount" + FROM_JOIN + WHERE_CLAUSE + @"
    GROUP BY VD.VOUCHERACCOUNTID, ACC.ACCOUNTSCHEDULENATURE
) X";
    }
}