namespace AccountsDAL.Query.AccountReports
{
    // Balance Sheet — a faithful port of legacy's real, working GetBalanceSheetReportVertical
    // (AccountsReportsDAL.cs:11787+), same MACCOUNT -> MACCOUNTGROUP -> MACCOUNTSCHEDULE rollup as
    // Trial Balance/Profit&Loss, filtered to ACCOUNTSCHEDULENATURE IN (0,1) (Liability/Asset).
    //
    // Unlike Trial Balance's whole-book query, Balance Sheet's own accounts (Nature 0,1) never reset
    // at a financial-year boundary — cumulative net since inception is the correct figure directly,
    // no nature-aware CASE branching needed inside the main SUM. What DOES need FY-awareness is the
    // pair of P&L-derived plug lines a real Balance Sheet must carry so Assets == Liabilities+Equity:
    //   1. Retained Earnings (Prior Years) — cumulative net P&L-nature activity strictly BEFORE
    //      @FYStartDate. Reuses Trial Balance's exact GET_RETAINED_EARNINGS_PLUG query/account
    //      unchanged (same fixed MACCOUNT row, ACCOUNTID -1399900600, under RESERVES AND SURPLUS).
    //   2. Current Year Profit/(Loss) — net P&L-nature activity from @FYStartDate to @AsOnDate. This
    //      is a NEW plug (Trial Balance's own snapshot never needed it, since TB already covers
    //      P&L-nature rows directly in its main query at Nature 2-5; Balance Sheet excludes them
    //      entirely from its main query, so this in-progress year's movement needs its own line to
    //      keep the sheet balanced before formal year-end closing). Same fixed account is NOT reused
    //      here — a real Balance Sheet shows Prior-Year Retained Earnings and Current-Year P&L as two
    //      distinct lines (the classic "Profit brought forward" vs "Profit for the year" split), so a
    //      SECOND fixed MACCOUNT row is seeded by migration for Current Year P&L.
    //
    // GroupType: 0 = Account, 1 = AccountGroup, 2 = AccountSchedule (SubAccount/Control-Account level
    // deferred, same as every other report in this wave's MVP pass).
    public static class BalanceSheetReportQB
    {
        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 <= @AsOnDate
  AND ACC.ACCOUNTSCHEDULENATURE IN (0,1)" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        // Nature=0 (Liability, includes Equity/Capital in this schema's convention) or Nature=1
        // (Asset) — same universal net-sign rule as Trial Balance/P&L: positive net -> Debit column,
        // negative net -> Credit column. FE presentation splits Asset (Debit side) from
        // Liability+Equity (Credit side) using this same Nature column.
        public const string GET_BALANCE_SHEET_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,
    ACC.ACCOUNTSCHEDULENATURE AS Nature,
    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";

        public const string GET_BALANCE_SHEET_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,
    ACC.ACCOUNTSCHEDULENATURE AS Nature,
    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";

        public const string GET_BALANCE_SHEET_ACCOUNTSCHEDULE = @"
SELECT
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    ACC.ACCOUNTSCHEDULENATURE AS Nature,
    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 — split by Nature (0=Liability+Equity, 1=Asset), Debit/Credit netted per the
        // universal rule. Derived-table subquery, not a leading CTE (established convention).
        public const string GET_BALANCE_SHEET_TOTALS = @"
SELECT
    SUM(CASE WHEN X.Nature = 1 AND X.NetAmount > 0 THEN X.NetAmount ELSE 0 END) AS AssetDebit,
    SUM(CASE WHEN X.Nature = 1 AND X.NetAmount < 0 THEN -X.NetAmount ELSE 0 END) AS AssetCredit,
    SUM(CASE WHEN X.Nature = 0 AND X.NetAmount > 0 THEN X.NetAmount ELSE 0 END) AS LiabilityDebit,
    SUM(CASE WHEN X.Nature = 0 AND X.NetAmount < 0 THEN -X.NetAmount ELSE 0 END) AS LiabilityCredit
FROM (
    SELECT ACC.ACCOUNTSCHEDULENATURE AS Nature, " + NET_EXPR + @" AS NetAmount" + FROM_JOIN + WHERE_CLAUSE + @"
    GROUP BY VD.VOUCHERACCOUNTID, ACC.ACCOUNTSCHEDULENATURE
) X";

        // Financial-year start for @AsOnDate — identical query to Trial Balance's own
        // GET_FY_START_DATE (MPERIOD rows ARE financial years, PERIODGROUP=0 is the real
        // non-overlapping sequence). Duplicated here per this codebase's one-QB-per-report-family
        // convention rather than cross-referencing TrialBalanceReportQB.
        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 (Prior Years) plug — cumulative net P&L (nature 2-5) dated strictly
        // BEFORE @FYStartDate. Same figure and same fixed account as Trial Balance's own
        // GET_RETAINED_EARNINGS_PLUG (ACCOUNTID -1399900600) — a Balance Sheet run exactly at an
        // AsOnDate equal to some FY's TB snapshot date must show the identical Retained Earnings
        // figure TB already shows, since both compute the same underlying number.
        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}";

        // Current Year Profit/(Loss) plug — net P&L (nature 2-5) from @FYStartDate to @AsOnDate.
        // NEW relative to Trial Balance (which never needs this — its own main query already covers
        // nature 2-5 rows directly at Nature=2-5, FY-to-date). Without this, a Balance Sheet run
        // mid-year would not balance: the current year's in-progress P&L movement would have nowhere
        // to land.
        public const string GET_CURRENT_YEAR_PANDL_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 AND V.VOUCHERDATE <= @AsOnDate" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        // Lookup for a fixed plug account's display fields — reused for BOTH the Retained Earnings
        // account (-1399900600, same row Trial Balance seeded) and the new Current Year P&L account
        // (seeded by this report's own migration), selected by the caller via @PlugAccountId.
        public const string GET_PLUG_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 = @PlugAccountId";
    }
}