namespace AccountsDAL.Query.AccountReports
{
    // Consolidates legacy GB4Solution's VoucherStatusReport/VoucherSummaryReport/VoucherListReport/
    // VoucherPeriodTypeReport (ServiceLayer/AccountsService/Voucher.svc.cs -> VoucherBLL.cs ->
    // VoucherDAL.cs -> a dedicated VoucherQueryBuilder.cs) into one shared QB file, per the plan
    // file's "Voucher Report Consolidation" section.
    //
    // The two literal, byte-for-byte-duplicated blocks the legacy research found are pulled out as
    // shared fragments below: the Debit/Credit split (repeated across 6 of legacy's 7 query variants)
    // and the Active/UnApproved voucher-status count split (repeated across 5). Legacy's OTHER major
    // duplication class — the hand-rolled criteria-parsing switchboard and OU-scoping templating,
    // copy-pasted near-verbatim in all four VoucherDAL.cs methods — needs no equivalent here at all,
    // since gb5 already solves it generically via AccountReportCriteria/AccountReportCriteriaParser/
    // AccountReportFilterBuilder.Build(), reused as-is by every query below.
    //
    // Deliberately NOT ported: legacy's VoucherSummaryReport Type=1 (AccountWise) bolts on an
    // undocumented MPERIOD/ISACCOUNTPOST=0/ACCOUNTSCHEDULENATURE special case its 3 sibling GroupTypes
    // don't have (flagged by research as "an unexplained one-off special case," likely a provision-
    // scenario patch) — excluded so all 4 GroupTypes behave uniformly, same "exclude undocumented
    // legacy special-casing" discipline as Budget Comparison's hardcoded-ACCOUNTID debug filter.
    //
    // Alias discipline: AccountReportFilterBuilder.Build()'s {DynamicFilter} text unconditionally may
    // reference V/VD/ACC/BTT depending on which criteria fields the caller set (most commonly the
    // IncludeInformational default, which always references BTT unless the caller opts in). Every
    // query below either joins all four aliases (Summary, List-Account) so Build() is always safe to
    // use verbatim, or is genuinely header-level with no natural VD/ACC join (Status, List-Class,
    // PeriodType) — those three use the new AccountReportFilterBuilder.BuildVoucherHeaderOnly(), a
    // trimmed copy of Build() scoped to V/BTT(+ACC where available)-only fragments, never VD-dependent
    // ones, so there is no dangling-alias risk regardless of which criteria fields a caller sets.
    //
    // No leading CTE in any query — QueryPagedAsync wraps the caller's SQL as
    // `SELECT COUNT(*) FROM (<sql>) AS Total`, which breaks on `;WITH ...` (see
    // AccountLedgerReportsQB.cs's header comment). VOUCHER_PERIODTYPE_REPORT's legacy CTE shape is
    // replaced with buckets (MGBPERIOD/MGBPERIODDETAIL) LEFT JOINed to a pre-filtered voucher derived
    // table (V/BTT-scoped filter applied INSIDE the derived table, avoiding both the CTE restriction
    // and any join-order problem from referencing a not-yet-joined alias in an ON clause).
    public static class VoucherReportQB
    {
        // Repeated verbatim across VoucherSummary (all 4 GroupTypes) and VoucherList-Account in legacy
        // — the single clearest duplication the research found.
        private const string DEBIT_CREDIT_EXPR = @"
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END) AS Debit,
    SUM(CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END) AS Credit";

        // Repeated verbatim across VoucherSummary (all 4 GroupTypes) in legacy.
        private const string ACTIVE_UNAPPROVED_EXPR = @"
    SUM(CASE WHEN V.STATUS IN (1) THEN 1 ELSE 0 END) AS ActiveNumber,
    SUM(CASE WHEN V.STATUS IN (0) THEN 1 ELSE 0 END) AS UnApprovedNumber";

        private const string OU_ACCESS_CEILING = @"
    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";

        // ── Report 1 — Voucher Status ───────────────────────────────────────────────────────────
        // Header-level (no TVOUCHERDETAIL join) — grouped by BizTransactionClass. Grand total
        // (Status/Summary Totals queries) is computed by a separate non-paged SQL query, matching
        // AccountRegisterSummaryDAL's own GET_ACCOUNT_REGISTER_SUMMARY_TOTALS precedent — NOT baked
        // into the paged SQL as a UNION ALL row the way legacy does (that would double-count the
        // grand total against the current page's own subtotal once QueryPagedAsync's own
        // COUNT(*)-wrapping totals the rows). Uses BuildVoucherHeaderOnly (V/BTT only,
        // hasAccountAlias:false) for {DynamicFilter} — no VD/ACC join exists at this grain, and
        // shouldn't (joining detail lines here would multiply voucher counts per line). The literal
        // -1399999990 below is AccountReportsQB.INFORMATIONAL_BIZ_TRANSACTION_CLASS_ID's own value,
        // hardcoded here since a cross-class const reference isn't compile-time-constant-foldable
        // inside another const string initializer (CS0133).
        public const string GET_VOUCHER_STATUS_REPORT = @"
SELECT
    B.BIZTRANSACTIONCLASSID AS BizTransactionClassId,
    B.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName,
    SUM(CASE WHEN V.STATUS = 1 THEN 1 ELSE 0 END) AS ActiveNumber,
    SUM(CASE WHEN V.STATUS = 0 THEN 1 ELSE 0 END) AS UnApprovedNumber,
    SUM(CASE WHEN V.STATUS = 2 THEN 1 ELSE 0 END) AS CancelledNumber,
    SUM(CASE WHEN V.STATUS = 1 THEN V.VOUCHERAMOUNT ELSE 0 END) AS ActiveAmount,
    SUM(CASE WHEN V.STATUS = 0 THEN V.VOUCHERAMOUNT ELSE 0 END) AS UnApprovedAmount,
    SUM(CASE WHEN V.STATUS = 2 THEN V.VOUCHERAMOUNT ELSE 0 END) AS CancelledAmount,
    SUM(V.VOUCHERAMOUNT) AS TotalValue
FROM MBIZTRANSACTIONTYPE BTT
INNER JOIN MBIZTRANSACTIONCLASS B ON BTT.BIZTRANSACTIONCLASSID = B.BIZTRANSACTIONCLASSID
LEFT JOIN TVOUCHER V ON BTT.BIZTRANSACTIONTYPEID = V.BIZTRANSACTIONTYPEID
    AND V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
    AND V.OUID IN (" + OU_ACCESS_CEILING + @")
WHERE B.BIZTRANSACTIONCLASSID NOT IN (-1399999990)
  {DynamicFilter}
GROUP BY B.BIZTRANSACTIONCLASSID, B.BIZTRANSACTIONCLASSNAME
ORDER BY B.BIZTRANSACTIONCLASSNAME
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_VOUCHER_STATUS_REPORT_TOTALS = @"
SELECT
    SUM(CASE WHEN V.STATUS = 1 THEN 1 ELSE 0 END) AS TotalActiveNumber,
    SUM(CASE WHEN V.STATUS = 0 THEN 1 ELSE 0 END) AS TotalUnApprovedNumber,
    SUM(CASE WHEN V.STATUS = 2 THEN 1 ELSE 0 END) AS TotalCancelledNumber,
    SUM(CASE WHEN V.STATUS = 1 THEN V.VOUCHERAMOUNT ELSE 0 END) AS TotalActiveAmount,
    SUM(CASE WHEN V.STATUS = 0 THEN V.VOUCHERAMOUNT ELSE 0 END) AS TotalUnApprovedAmount,
    SUM(CASE WHEN V.STATUS = 2 THEN V.VOUCHERAMOUNT ELSE 0 END) AS TotalCancelledAmount,
    SUM(V.VOUCHERAMOUNT) AS TotalValue
FROM MBIZTRANSACTIONTYPE BTT
INNER JOIN MBIZTRANSACTIONCLASS B ON BTT.BIZTRANSACTIONCLASSID = B.BIZTRANSACTIONCLASSID
LEFT JOIN TVOUCHER V ON BTT.BIZTRANSACTIONTYPEID = V.BIZTRANSACTIONTYPEID
    AND V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
    AND V.OUID IN (" + OU_ACCESS_CEILING + @")
WHERE B.BIZTRANSACTIONCLASSID NOT IN (-1399999990)
  {DynamicFilter};";

        // ── Report 2 — Voucher Summary ──────────────────────────────────────────────────────────
        // One query for all 4 legacy GroupTypes — {GroupBySelect}/{GroupByColumns} substituted from
        // VoucherSummaryGroupByBuilder (allow-listed, never string-interpolated from caller input).
        // V/VD/ACC/BTT all present — safe to use the full, unmodified Build().
        public const string GET_VOUCHER_SUMMARY_REPORT = @"
SELECT
    {GroupBySelect}," + ACTIVE_UNAPPROVED_EXPR + "," + DEBIT_CREDIT_EXPR + @"
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
LEFT JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
LEFT JOIN MACCOUNT CTRL ON ACC.CONTROLACCOUNTID = CTRL.ACCOUNTID
WHERE V.STATUS NOT IN (2)
  AND V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (" + OU_ACCESS_CEILING + @")
  {DynamicFilter}
GROUP BY {GroupByColumns}
ORDER BY Debit DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_VOUCHER_SUMMARY_REPORT_TOTALS = @"
SELECT
    SUM(CASE WHEN V.STATUS IN (1) THEN 1 ELSE 0 END) AS TotalActiveNumber,
    SUM(CASE WHEN V.STATUS IN (0) THEN 1 ELSE 0 END) AS TotalUnApprovedNumber,
    SUM(CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END) AS TotalDebit,
    SUM(CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END) AS TotalCredit
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
WHERE V.STATUS NOT IN (2)
  AND V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (" + OU_ACCESS_CEILING + @")
  {DynamicFilter};";

        // ── Report 3 — Voucher List ─────────────────────────────────────────────────────────────
        // Type=0 "class list": header-level, one row per voucher, main account only — no detail
        // aggregation at all. Genuinely different shape from Type=1 below (confirmed by research),
        // kept as two distinct queries/DTOs/endpoints rather than a forced single shape. Uses
        // BuildVoucherHeaderOnly (hasAccountAlias:true — ACC is joined here, via MAINACCOUNTID).
        public const string GET_VOUCHER_LIST_CLASS = @"
SELECT
    V.VOUCHERID AS VoucherId,
    V.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    V.OUID AS OUId,
    V.VOUCHERDATE AS VoucherDate,
    V.VOUCHERNUMBER AS VoucherNumber,
    V.VOUCHERREFERENCENUMBER AS ReferenceNumber,
    V.VOUCHERREFERENCEDATE AS ReferenceDate,
    ACC.ACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    V.VOUCHERAMOUNT AS Amount,
    V.VOUCHERNARRATION AS VoucherNarration
FROM TVOUCHER V
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
INNER JOIN MACCOUNT ACC ON V.MAINACCOUNTID = ACC.ACCOUNTID
WHERE V.STATUS NOT IN (2)
  AND V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  AND V.OUID IN (" + OU_ACCESS_CEILING + @")
  {DynamicFilter}
ORDER BY V.VOUCHERDATE DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Type=1 "account list": aggregates the caller-selected account's own Debit/Credit for each
        // voucher (via AccountReportCriteria.VoucherDetailAccountIds — already-existing criteria
        // field, no new one needed), then resolves the counter/opposite-side account via gb5's own
        // (confirmed live) TVOUCHER.MAXCREDITACCOUNTID/MAXDEBITACCOUNTID columns — the exact
        // mechanic legacy uses, ports directly, no self-join needed. V/VD/ACC/BTT all present inside
        // the inner subquery — safe to use the full, unmodified Build().
        public const string GET_VOUCHER_LIST_ACCOUNT = @"
SELECT
    IA.VOUCHERID AS VoucherId,
    V.VOUCHERNUMBER AS VoucherNumber,
    V.VOUCHERDATE AS VoucherDate,
    IA.DEBIT AS Debit,
    IA.CREDIT AS Credit,
    V.VOUCHERNARRATION AS VoucherNarration,
    CTR.ACCOUNTID AS CounterAccountId,
    CTR.ACCOUNTCODE AS CounterAccountCode,
    CTR.ACCOUNTNAME AS CounterAccountName
FROM (
    SELECT
        VD.VOUCHERID," + DEBIT_CREDIT_EXPR + @"
    FROM TVOUCHER V
    INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
    INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
    INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
    WHERE V.STATUS NOT IN (2)
      AND VD.VOUCHERACCOUNTID IN @VoucherDetailAccountIds
      AND V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
      AND V.OUID IN (" + OU_ACCESS_CEILING + @")
      {DynamicFilter}
    GROUP BY VD.VOUCHERID
) IA
INNER JOIN TVOUCHER V ON IA.VOUCHERID = V.VOUCHERID
LEFT JOIN MACCOUNT CTR ON CTR.ACCOUNTID =
    CASE WHEN IA.DEBIT >= IA.CREDIT THEN V.MAXCREDITACCOUNTID ELSE V.MAXDEBITACCOUNTID END
ORDER BY V.VOUCHERDATE DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // ── Report 4 — Voucher PeriodType ───────────────────────────────────────────────────────
        // Buckets (MGBPERIOD/MGBPERIODDETAIL sub-periods overlapping the requested range for the
        // given PeriodType) LEFT JOINed to a pre-filtered voucher derived table (V/BTT-scoped filter
        // applied INSIDE the derived table — avoids both the leading-CTE restriction and any
        // join-order problem from referencing a not-yet-joined alias in an ON clause), then
        // TVOUCHERDETAIL LEFT JOINed for Debit/Credit aggregation per bucket. Reuses
        // AccountReportCriteria.PeriodType directly (already exists, MGBPERIOD-based, default
        // 10=FN-Monthly) — the SAME semantics legacy's own VOUCHER_PERIODTYPE_REPORT uses, confirmed
        // by its own PeriodType(0=Daily, 1=Weekly,...) comment — NOT the P&L Dashboard's separate
        // DIMDATE-based bespoke enum. Uses BuildVoucherHeaderOnly (hasAccountAlias:false) for the
        // derived table's {DynamicFilter} — only V/BTT exist inside it.
        public const string GET_VOUCHER_PERIODTYPE_REPORT = @"
SELECT
    PD.SUBPERIODNAME AS SubPeriodName,
    PD.SUBPERIODFROMDATE AS SubPeriodFromDate,
    PD.SUBPERIODTODATE AS SubPeriodToDate,
    COUNT(DISTINCT CASE WHEN FV.STATUS = 1 THEN FV.VOUCHERID END) AS ActiveNumber,
    COUNT(DISTINCT CASE WHEN FV.STATUS = 0 THEN FV.VOUCHERID END) AS UnApprovedNumber,
    ISNULL(SUM(CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END), 0) AS Debit,
    ISNULL(SUM(CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END), 0) AS Credit
FROM MGBPERIOD P
INNER JOIN MGBPERIODDETAIL PD ON P.GBPERIODID = PD.GBPERIODID
LEFT JOIN (
    SELECT V.VOUCHERID, V.VOUCHERDATE, V.STATUS
    FROM TVOUCHER V
    INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
    WHERE V.STATUS IN (0, 1)
      AND V.OUID IN (" + OU_ACCESS_CEILING + @")
      {DynamicFilter}
) FV ON FV.VOUCHERDATE >= PD.SUBPERIODFROMDATE AND FV.VOUCHERDATE <= PD.SUBPERIODTODATE
LEFT JOIN TVOUCHERDETAIL VD ON VD.VOUCHERID = FV.VOUCHERID
WHERE P.PERIODTYPE = @PeriodType
  AND PD.SUBPERIODFROMDATE <= @PeriodToDate AND PD.SUBPERIODTODATE >= @PeriodFromDate
GROUP BY PD.GBPERIODDETAILID, PD.SUBPERIODNAME, PD.SUBPERIODFROMDATE, PD.SUBPERIODTODATE
ORDER BY PD.SUBPERIODFROMDATE";
    }
}