namespace AccountsDAL.Query.AccountReports
{
    // Fund Flow — a faithful port of legacy's real, working GetFundFlowReport (AccountsReportsDAL.cs
    // :18070+, backed by FUND_FLOW_SERVICE/_GROUP_WISE/_SCHEDULE_WISE in AccountsReportQueryBuilder.cs
    // :13392+). The classic Indian "Sources and Application of Funds" statement — an indirect,
    // balance-sheet-movement-derived method, NOT the Direct/voucher-level method Cash Flow uses.
    //
    // Confirmed via reading legacy's literal SQL, not just its paraphrase: per BS-nature (0,1)
    // account, SUM(debit-credit) over the requested period (no financial-year-reset needed — this is
    // inherently period-bound, same reasoning as Profit & Loss's own NET_EXPR). Raw movement < 0 (net
    // credit) is a SOURCE of funds (shown as its absolute value); raw movement > 0 (net debit) is an
    // APPLICATION of funds. This sign rule holds uniformly across Asset and Liability accounts alike
    // — the double-entry sign convention alone determines Source vs Application, no per-nature
    // branching needed (e.g. a net credit on an Asset account means the asset decreased, freeing up
    // funds = Source; a net credit on a Liability account means the liability increased = also a
    // Source — both cases correctly fall out of the same raw-sign rule).
    //
    // A single balancing "Profit/(Loss) for the Period" row = net P&L-nature (2-5) movement over the
    // same period, same sign rule (net credit = profit = Source; net debit = loss = Application).
    //
    // Legacy pairs Source/Application rows side-by-side via a `numbertally` row-number join purely as
    // a print-layout artifact (confirmed by reading the SQL — the two subqueries are independently
    // computed, joined only by arbitrary row position, no real business relationship between the
    // paired accounts). NOT reproduced here — this returns a single flat list tagged by FlowType
    // (Source/Application) instead, which is both simpler and more honest about what the data means.
    //
    // 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 FundFlowReportQB
    {
        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 (0,1)" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";

        private const string FLOWTYPE_EXPR = "CASE WHEN " + NET_EXPR + " < 0 THEN 'Source' ELSE 'Application' END";
        private const string AMOUNT_EXPR = "ABS(" + NET_EXPR + ")";

        public const string GET_FUND_FLOW_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,
    " + FLOWTYPE_EXPR + @" AS FlowType,
    " + AMOUNT_EXPR + @" AS Amount" + FROM_JOIN + WHERE_CLAUSE + @"
GROUP BY VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME,
         ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME
HAVING " + AMOUNT_EXPR + @" <> 0
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_FUND_FLOW_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,
    " + FLOWTYPE_EXPR + @" AS FlowType,
    " + AMOUNT_EXPR + @" AS Amount" + FROM_JOIN + WHERE_CLAUSE + @"
GROUP BY ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME,
         AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME
HAVING " + AMOUNT_EXPR + @" <> 0
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_FUND_FLOW_ACCOUNTSCHEDULE = @"
SELECT
    AG.ACCOUNTSCHEDULEID AS AccountScheduleId,
    AGS.ACCOUNTSCHEDULECODE AS AccountScheduleCode,
    AGS.ACCOUNTSCHEDULENAME AS AccountScheduleName,
    " + FLOWTYPE_EXPR + @" AS FlowType,
    " + AMOUNT_EXPR + @" AS Amount" + FROM_JOIN + WHERE_CLAUSE + @"
GROUP BY AG.ACCOUNTSCHEDULEID, AGS.ACCOUNTSCHEDULECODE, AGS.ACCOUNTSCHEDULENAME
HAVING " + AMOUNT_EXPR + @" <> 0
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Totals — SUM(Source) must equal SUM(Application) exactly once the P&L balancing row (added
        // by the DAL in C#) is included. Derived-table subquery, not a leading CTE.
        public const string GET_FUND_FLOW_TOTALS = @"
SELECT
    SUM(CASE WHEN X.FlowType = 'Source' THEN X.Amount ELSE 0 END) AS TotalSource,
    SUM(CASE WHEN X.FlowType = 'Application' THEN X.Amount ELSE 0 END) AS TotalApplication
FROM (
    SELECT " + FLOWTYPE_EXPR + @" AS FlowType, " + AMOUNT_EXPR + @" AS Amount" + FROM_JOIN + WHERE_CLAUSE + @"
    GROUP BY VD.VOUCHERACCOUNTID
    HAVING " + AMOUNT_EXPR + @" <> 0
) X";

        // P&L-for-period balancing row — net movement of nature 2-5 (Income/Expense) accounts over
        // the same period, same Source/Application sign rule (net credit = profit = Source of funds;
        // net debit = loss = Application). No financial-year-reset needed (plain period sum, matching
        // the main query's own period-bound design).
        private const string PANDL_NET_EXPR = @"
            SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE -(" + BASE_AMOUNT_EXPR + @") END)";

        public const string GET_FUND_FLOW_PANDL_BALANCING_ROW = @"
SELECT ISNULL(" + PANDL_NET_EXPR + @", 0) AS PandLNet
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 V.VOUCHERDATE BETWEEN @PeriodFromDate AND @PeriodToDate
  AND ACC.ACCOUNTSCHEDULENATURE IN (2,3,4,5)" + OU_ACCESS_FILTER + @"
  {DynamicFilter}";
    }
}