namespace AccountsDAL.Query.AccountReports
{
    // GB4 AccountsReports.svc AccountRegister / AccountRegisterSummary migration.
    //
    // Tables/columns confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-25/07-27, this session's
    // GB5 Voucher save/get work — TVOUCHER, TVOUCHERDETAIL, TVOUCHERCOSTCENTER, TBILLALLOCATION,
    // TINSTRUMENT, TVOUCHERADDON, TVOUCHERTDS, TBIZDIMENSION, none have a TENANTID column) plus
    // existing GB5 usage of MACCOUNT/MACCOUNTGROUP/MUSERACCESSRIGHTS/MORGANIZATIONGROUPDETAIL/
    // MROLEVSMENU/TMMHEAD elsewhere in this repo (AccountQB.cs, VoucherQB.cs, AccountPostQB.cs,
    // UserAccessRightsQB.cs, OrganizationGroupQB.cs, RoleVsMenuQB.cs, MMheadQB.cs).
    //
    // {DynamicFilter} is replaced by the DAL with zero or more additional "AND ..." clauses built
    // from the caller's criteria (never string-interpolated values — always bound Dapper parameters).
    // The mandatory OU-access ceiling (MUSERACCESSRIGHTS, expanding OUGROUPID grants via
    // MORGANIZATIONGROUPDETAIL) is baked directly into the WHERE clause below, not in
    // {DynamicFilter}, since it is never optional.
    public static class AccountReportsQB
    {
        // GB4's sentinel BizTransactionClassId for "informational" (non-financial) voucher types —
        // excluded from register/summary reports by default, matching legacy's default behavior.
        public const int INFORMATIONAL_BIZ_TRANSACTION_CLASS_ID = -1399999990;

        // Finance Analytics Dashboards, Phase 0c — resolves an OU Group into its member OUs for the
        // FFINANCE-fact-table-backed dashboards (P&L/Ratio/Receivable/Budget), which query a plain
        // OUID list against the fact table directly and don't go through AccountReportFilterBuilder's
        // TVOUCHER-aliased (V/VD/ACC/BTT) fragments at all. Same source table
        // AccountReportFilterBuilder.Build() already uses for the TVOUCHER-based report family's own
        // OUGroupId resolution — kept as a separate, explicit SELECT here since these DAL methods
        // need the actual member-OU list to merge with an explicit OUIds selection, not just a WHERE
        // fragment to embed.
        public const string RESOLVE_OU_GROUP_MEMBERS = @"
            SELECT OGD.OUID
            FROM   MORGANIZATIONGROUPDETAIL OGD
            WHERE  OGD.ORGANIZATIONGROUPID = @OUGroupId";

        // ── AccountRegister (detail) ────────────────────────────────────────────────────────────

        public const string GET_ACCOUNT_REGISTER_DETAIL_REPORT = @"
SELECT
    V.VOUCHERID AS VoucherId,
    V.STATUS AS Status,
    V.VOUCHERDATE AS VoucherDate,
    V.VOUCHERAMOUNT AS VoucherAmount,
    V.VOUCHERFCAMOUNT AS VoucherFCAmount,
    V.VOUCHERNUMBER AS VoucherNumber,
    V.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber,
    V.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
    V.VOUCHERNARRATION AS VoucherNarration,
    V.ISREVERSE AS IsReverse,
    V.VALIDTILLDATE AS ValidTillDate,
    V.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    VD.VOUCHERDETAILID AS VoucherDetailId,
    VD.SLNO AS Slno,
    VD.VOUCHERAMOUNTFC AS VoucherDetailVoucherAmountFC,
    VD.VOUCHERAMOUNT AS VoucherDetailVoucherAmount,
    VD.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,
    VD.CURRENCYCONVERSION AS ConversionFactor,
    CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END AS Debit,
    CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END AS Credit,
    VD.VOUCHERLINENARRATION AS VoucherLineNarration,
    VD.DETAILTYPE AS DetailType,
    VD.VOUCHERACCOUNTID AS VoucherAccountId,
    ACC.ACCOUNTCODE AS VoucherAccountCode,
    ACC.ACCOUNTNAME AS VoucherAccountName,
    CASE V.STATUS
        WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
        WHEN 3 THEN 'Amended/Rejected' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
        ELSE '' END AS VoucherStatus,
    MCC.COSTCENTERCODE AS CostCenterCode,
    MCC.COSTCENTERNAME AS CostCenterName,
    ISNULL(SUBDIM.V1Code,'') AS V1Code, ISNULL(SUBDIM.V2Code,'') AS V2Code, ISNULL(SUBDIM.V3Code,'') AS V3Code,
    ISNULL(SUBDIM.V4Code,'') AS V4Code, ISNULL(SUBDIM.V5Code,'') AS V5Code,
    ISNULL(SUBDIM.V1Name,'') AS V1Name, ISNULL(SUBDIM.V2Name,'') AS V2Name, ISNULL(SUBDIM.V3Name,'') AS V3Name,
    ISNULL(SUBDIM.V4Name,'') AS V4Name, ISNULL(SUBDIM.V5Name,'') AS V5Name,
    ALLOC.ALLOCATIONNAME AS AllocationName,
    ALLOC.DESCRIPTION AS AllocationDescription,
    CASE MH.MODEOFPAYMENT WHEN 0 THEN 'Cash' WHEN 1 THEN 'Bank' ELSE '' END AS ModeOfPaymentName,
    MH.PAYMENTTERMSREMARKS AS BillType,
    CB.USERNAME AS CreatedByName,
    MB.USERNAME AS ModifiedByName,
    0 AS TotalDebit,
    0 AS TotalCredit
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
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MCOSTCENTER MCC ON VD.COSTCENTERID = MCC.COSTCENTERID
LEFT JOIN MCURRENCY CUR ON VD.CURRENCYID = CUR.CURRENCYID
LEFT JOIN MALLOCATION ALLOC ON VD.ALLOCATIONID = ALLOC.ALLOCATIONID
LEFT JOIN MUSER CB ON V.CREATEDBYID = CB.USERID
LEFT JOIN MUSER MB ON V.MODIFIEDBYID = MB.USERID
LEFT JOIN TMMHEAD MH ON MH.VOUCHERID = V.VOUCHERID
OUTER APPLY (
    -- TOP (1): defensive against TBIZDIMENSION/MBIZDIMENSIONVALUESET ever having more than one row
    -- per (VoucherId, VoucherDetailId) — the reference pattern this was copied from
    -- (VoucherQB.GET_VOUCHER_DETAILS) assumes at-most-one and was not independently re-verified here.
    SELECT TOP (1)
        V1.BIZDIMENSIONVALUECODE AS V1Code, V2.BIZDIMENSIONVALUECODE AS V2Code, V3.BIZDIMENSIONVALUECODE AS V3Code,
        V4.BIZDIMENSIONVALUECODE AS V4Code, V5.BIZDIMENSIONVALUECODE AS V5Code,
        V1.BIZDIMENSIONVALUENAME AS V1Name, V2.BIZDIMENSIONVALUENAME AS V2Name, V3.BIZDIMENSIONVALUENAME AS V3Name,
        V4.BIZDIMENSIONVALUENAME AS V4Name, V5.BIZDIMENSIONVALUENAME AS V5Name
    FROM TBIZDIMENSION TD
    INNER JOIN MBIZDIMENSIONVALUESET VS ON TD.BIZDIMENSIONVALUESETID = VS.BIZDIMENSIONVALUESETID
    LEFT JOIN MBIZDIMENSIONVALUE V1 ON VS.V1 = V1.BIZDIMENSIONVALUEID
    LEFT JOIN MBIZDIMENSIONVALUE V2 ON VS.V2 = V2.BIZDIMENSIONVALUEID
    LEFT JOIN MBIZDIMENSIONVALUE V3 ON VS.V3 = V3.BIZDIMENSIONVALUEID
    LEFT JOIN MBIZDIMENSIONVALUE V4 ON VS.V4 = V4.BIZDIMENSIONVALUEID
    LEFT JOIN MBIZDIMENSIONVALUE V5 ON VS.V5 = V5.BIZDIMENSIONVALUEID
    WHERE TD.OBJECTHEADERTYPEID = -1899999161 AND TD.OBJECTHEADERID = VD.VOUCHERID
      AND TD.OBJECTTYPEID = -1899999138 AND TD.OBJECTID = VD.VOUCHERDETAILID
) SUBDIM
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  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
  )
  {DynamicFilter}
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_ACCOUNT_REGISTER_DETAIL_TOTALS = @"
SELECT
    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 MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN TMMHEAD MH ON MH.VOUCHERID = V.VOUCHERID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  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
  )
  {DynamicFilter};";

        // ── Page-scoped child collections — filtered to the current page's VoucherDetailIds only
        //    (never the whole filtered date range) to avoid the legacy "fetch everything, join in
        //    memory" anti-pattern. Only run when the caller opts in (NeedBillAllocation/etc.).

        // Enriched to match the shape already proven in VoucherQB.GET_VOUCHER_BILL_ALLOCATION_FOR_VOUCHER —
        // bill allocation is overwhelmingly a Debtor/Creditor-account concern, where FullAmount vs
        // AllocatedAmount (partial settlement), due/plan dates (AR/AP aging), and the "allocated
        // against" reference (which original bill a settlement is being applied to, via the
        // ALLOCATIONLINEID self-join) are the fields an actual register reader needs — not just the
        // bare type/reference/amount this started with.
        public const string GET_ACCOUNT_REGISTER_BILL_ALLOCATION = @"
SELECT
    A.VOUCHERDETAILID AS VoucherDetailId,
    D.VOUCHERID AS VoucherId,
    A.ALLOCATIONTYPE AS BillType,
    A.REFERENCENUMBER AS BillReferenceNumber,
    A.REFERENCEDATE AS BillReferenceDate,
    A.DETAILNUMBER AS BillDetailNumber,
    A.DETAILDATE AS BillDetailDate,
    A.ALLOCATEDAMOUNT AS BillAllocatedAmount,
    A.FULLAMOUNT AS BillFullAmount,
    A.DUEDATE AS BillDueDate,
    A.PLANDATE AS BillPlanDate,
    A.DUEDAYS AS BillDueDays,
    A.DUETYPE AS BillDueType,
    A.STATUS AS BillStatus,
    A.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,
    A.CURRENCYCONVERSION AS ConversionFactor,
    A.FULLAMOUNTFC AS BillFullAmountFC,
    A.ALLOCATEDAMOUNTFC AS BillAllocatedAmountFC,
    A.ALLOCATIONLINEID AS AllocationLineId,
    AGAINST.DETAILNUMBER AS AllocationLineDetailNumber
FROM TBILLALLOCATION A
INNER JOIN TVOUCHERDETAIL D ON A.VOUCHERDETAILID = D.VOUCHERDETAILID
LEFT JOIN MCURRENCY CUR ON A.CURRENCYID = CUR.CURRENCYID
LEFT JOIN TBILLALLOCATION AGAINST ON A.ALLOCATIONLINEID = AGAINST.BILLALLOCATIONLINEID
WHERE A.VOUCHERDETAILID IN @VoucherDetailIds;";

        public const string GET_ACCOUNT_REGISTER_COST_CENTER = @"
SELECT
    A.VOUCHERDETAILID AS VoucherDetailId,
    D.VOUCHERID AS VoucherId,
    C.COSTCENTERCODE AS CostCenterCode,
    C.COSTCENTERNAME AS CostCenterName,
    A.ALLOCATEDAMOUNT AS AllocatedAmount
FROM TVOUCHERCOSTCENTER A
INNER JOIN TVOUCHERDETAIL D ON A.VOUCHERDETAILID = D.VOUCHERDETAILID
LEFT JOIN MCOSTCENTER C ON A.COSTCENTERID = C.COSTCENTERID
WHERE A.VOUCHERDETAILID IN @VoucherDetailIds;";

        // Enriched to match VoucherQB.GET_VOUCHER_INSTRUMENT_FOR_VOUCHER — this is the Cash/Bank
        // account breakup (Cash/Cheque/Card/EFT/DD etc., via MINSTRUMENT.INSTRUMENTNAME) with its full
        // clearing lifecycle (deposit -> realised/bounced/cancelled) and the bank/GL accounts involved,
        // not just a bare number/status/amount.
        public const string GET_ACCOUNT_REGISTER_INSTRUMENT = @"
SELECT
    A.VOUCHERDETAILID AS VoucherDetailId,
    D.VOUCHERID AS VoucherId,
    A.INSTRUMENTNUMBER AS InstrumentNumber,
    A.INSTRUMENTID AS InstrumentId,
    B.INSTRUMENTNAME AS InstrumentName,
    A.INSTRUMENTSTATUS AS InstrumentStatus,
    CASE A.INSTRUMENTSTATUS
        WHEN 0 THEN 'Pending' WHEN 1 THEN 'Deposit' WHEN 2 THEN 'Realised'
        WHEN 3 THEN 'Bounced' WHEN 4 THEN 'Hold' WHEN 5 THEN 'Cancelled'
        ELSE '' END AS StatusName,
    A.DRAWNONBANK AS DrawOnBank,
    A.DRAWNONBRANCH AS DrawOnBranch,
    A.INSTRUMENTDATE AS InstrumentDate,
    A.INSTRUMENTAMOUNT AS InstrumentAmount,
    A.INSTRUMENTFCAMOUNT AS InstrumentFCAmount,
    A.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,
    A.CURRENCYCONVERSION AS ConversionFactor,
    A.PARTICULARS AS Particulars,
    A.DEPOSITID AS DepositId,
    A.DEPOSITNUMBER AS DepositNumber,
    A.DEPOSITDATE AS DepositDate,
    A.REALISEDDATE AS RealisedDate,
    A.BOUNCEDDATE AS BouncedDate,
    A.CANCELDATE AS CancelDate,
    A.SUBACCOUNTID AS SubAccountId,
    SUBACC.ACCOUNTCODE AS SubAccountCode,
    SUBACC.ACCOUNTNAME AS SubAccountName,
    A.BANKACCOUNTID AS BankAccountId,
    BANKACC.ACCOUNTCODE AS BankAccountCode,
    BANKACC.ACCOUNTNAME AS BankAccountName,
    A.ACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName
FROM TINSTRUMENT A
INNER JOIN TVOUCHERDETAIL D ON A.VOUCHERDETAILID = D.VOUCHERDETAILID
LEFT JOIN MINSTRUMENT B ON A.INSTRUMENTID = B.INSTRUMENTID
LEFT JOIN MCURRENCY CUR ON A.CURRENCYID = CUR.CURRENCYID
LEFT JOIN MACCOUNT SUBACC ON A.SUBACCOUNTID = SUBACC.ACCOUNTID
LEFT JOIN MACCOUNT BANKACC ON A.BANKACCOUNTID = BANKACC.ACCOUNTID
LEFT JOIN MACCOUNT ACC ON A.ACCOUNTID = ACC.ACCOUNTID
WHERE A.VOUCHERDETAILID IN @VoucherDetailIds;";

        public const string GET_ACCOUNT_REGISTER_ADDON = @"
SELECT
    A.VOUCHERDETAILID AS VoucherDetailId,
    A.VOUCHERID AS VoucherId,
    A.SLNO AS Slno,
    A.PARAMETERID AS ParameterId,
    P.PARAMETERCODE AS ParameterCode,
    P.PARAMETERNAME AS ParameterName,
    A.INPUTDATE AS InputDate,
    A.INPUTID AS InputId,
    A.INPUTVALUE AS InputValue,
    A.INPUTTEXT AS InputText
FROM TVOUCHERADDON A
LEFT JOIN MPARAMETER P ON A.PARAMETERID = P.PARAMETERID
WHERE A.VOUCHERDETAILID IN @VoucherDetailIds;";

        // Enriched to the full TVOUCHERTDS shape — a TDS register is a tax-compliance artifact, so
        // Section/Certificate/Status and the SC/Cess/Interest/Penalty breakdown are not optional
        // extras, they're the fields the report exists to show.
        public const string GET_ACCOUNT_REGISTER_TDS = @"
SELECT
    A.VOUCHERLINEID AS VoucherDetailId,
    D.VOUCHERID AS VoucherId,
    A.TDSNATURE AS TDSNature,
    A.TDSTYPE AS TDSType,
    A.ACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    A.REFERENCENUMBER AS ReferenceNumber,
    A.REFERENCEDATE AS ReferenceDate,
    A.CREDITDATE AS CreditDate,
    A.PARTICULARS AS Particulars,
    A.AMOUNT AS Amount,
    A.TDSCATEGORYID AS TDSCategoryId,
    A.SECTION AS Section,
    A.REFERENCESECTION AS ReferenceSection,
    A.TDSPERCENT AS TDSPercent,
    A.TDSAMOUNT AS TDSAmount,
    A.SCPERCENT AS SCPercent,
    A.SCAMOUNT AS SCAmount,
    A.CESSPERCENT AS CessPercent,
    A.CESSAMOUNT AS CessAmount,
    A.INTERESTAMOUNT AS InterestAmount,
    A.PENALTYAMOUNT AS PenaltyAmount,
    A.OTHERAMOUNT AS OtherAmount,
    A.ROUNDEDAMOUNT AS RoundedAmount,
    A.TOTALAMOUNT AS TotalAmount,
    A.TYPEOFDEDUCTION AS TypeOfDeduction,
    A.TDSSTATUS AS TDSStatus,
    A.CERTIFICATENUMBER AS CertificateNumber,
    A.CERTIFICATEDATE AS CertificateDate,
    A.REMARKS AS Remarks
FROM TVOUCHERTDS A
INNER JOIN TVOUCHERDETAIL D ON A.VOUCHERLINEID = D.VOUCHERDETAILID
LEFT JOIN MACCOUNT ACC ON A.ACCOUNTID = ACC.ACCOUNTID
WHERE A.VOUCHERLINEID IN @VoucherDetailIds;";

        // ── AccountRegisterSummary — grouped debit/credit by Account/AccountGroup/ControlAccount ──

        public const string GET_ACCOUNT_REGISTER_SUMMARY = @"
SELECT
    ACC.ACCOUNTGROUPID AS AccountGroupId,
    AG.ACCOUNTGROUPCODE AS AccountGroupCode,
    AG.ACCOUNTGROUPNAME AS AccountGroupName,
    ACC.CONTROLACCOUNTID AS ControlAccountId,
    CTRL.ACCOUNTCODE AS ControlAccountCode,
    CTRL.ACCOUNTNAME AS ControlAccountName,
    VD.VOUCHERACCOUNTID AS AccountId,
    ACC.ACCOUNTCODE AS AccountCode,
    ACC.ACCOUNTNAME AS AccountName,
    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,
    CASE V.STATUS
        WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
        WHEN 3 THEN 'Amended/Rejected' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
        ELSE '' END AS VoucherStatus
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 MACCOUNT CTRL ON ACC.CONTROLACCOUNTID = CTRL.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  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
  )
  {DynamicFilter}
GROUP BY ACC.ACCOUNTGROUPID, AG.ACCOUNTGROUPCODE, AG.ACCOUNTGROUPNAME,
         ACC.CONTROLACCOUNTID, CTRL.ACCOUNTCODE, CTRL.ACCOUNTNAME,
         VD.VOUCHERACCOUNTID, ACC.ACCOUNTCODE, ACC.ACCOUNTNAME, V.STATUS
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_ACCOUNT_REGISTER_SUMMARY_TOTALS = @"
SELECT
    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 MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
WHERE V.VOUCHERDATE >= @PeriodFromDate AND V.VOUCHERDATE <= @PeriodToDate
  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
  )
  {DynamicFilter};";

        // ── Access-control lookups (RoleVsMenu day/record limits, per-user report-period restriction) ──

        // MaxDaysNormal/DaysLimit/MaxRecordsNormal/RecordsLimit columns are confirmed present in the
        // legacy MROLEVSMENU table (RoleVsMenuDTO/RoleVsMenuQueryBuilder) but not yet selected by any
        // GB5 query — confirm they still exist physically via gb5-schema/INFORMATION_SCHEMA.COLUMNS
        // before relying on this in production; GB5's own RoleVsMenuQB.cs doesn't select them today.
        public const string GET_REPORT_ACCESS_LIMITS = @"
SELECT
    MAXDAYSNORMAL AS MaxDaysNormal,
    DAYSLIMIT AS DaysLimit,
    MAXRECORDSNORMAL AS MaxRecordsNormal,
    RECORDSLIMIT AS RecordsLimit
FROM MROLEVSMENU
WHERE ROLEID = @RoleId AND MENUID = @MenuId;";

        // Most-restrictive (MIN) NOOFREADPERIOD across the user's access-rights rows, treating 0/absent
        // as "unrestricted" — see AccountReportCriteriaParser/AccountRegisterReportBLL for the caveat
        // that NOOFREADPERIOD's exact semantics are inferred from the one confirmed sibling usage
        // (NOOFPERIOD == 1 in UserAccessRightsDAL.GetUserAccessRightsOnUser), not independently verified.
        public const string GET_NOOFREADPERIOD_FOR_USER = @"
SELECT MIN(NOOFREADPERIOD) AS NoOfReadPeriod
FROM MUSERACCESSRIGHTS
WHERE USERID = @UserId AND NOOFREADPERIOD > 0;";
    }
}