namespace AccountsDAL.Query.AccountReports
{
    // GB4 GetOutstandingAnalysisQueryReport (AccountsReportsDAL.cs, a 15-step #temp-table pipeline)
    // migration — reimplemented as a single flat query using OUTER APPLY + window functions (no leading
    // CTE and no materialized #temp tables, per this codebase's QueryPagedAsync constraint and the
    // already-proven OVER(PARTITION BY...) pattern from AccountLedgerReportsQB's RunningBalance). Same
    // bill-level base as AccountOutstandingReportsQB (FNPENDINGBILL + TBILLALLOCATION, same
    // PB/VBILL/VD/V/ACC/BTT aliases) — deliberately kept identical so AccountReportFilterBuilder.
    // BuildOutstandingOnly() (its ReportType/Overdue/InCharge/PriceCategory/Route filters) applies
    // unchanged.
    //
    // Extended BIDIRECTIONAL per the user's request (2026-08-01): legacy's pipeline computes
    // QtrAvg/QtrCollAvg/ExpectedWeeks/NoofBills/NewSaleDebit/OverallAmount/DebitNote for the
    // RECEIVABLE side only (trailing-quarter Sales Invoice/Receipt averages). This version ADDS a
    // symmetric payable-side projection (QtrPayAvg/ExpectedWeeksToPay, from trailing-quarter Payment-
    // class averages) so both "expected weeks to collect" AND "expected weeks until this balance would
    // be paid at historical rate" (projected outflow) are visible on every row, plus a bidirectional
    // cash-discount-for-early-payment benefit (ported from legacy's separate "Suggested Bill To Pay"
    // report, GET_SUGGESTED_BILL_TOPAY — previously payables-only via TMMHEAD.PAYMENTTERMSID; confirmed
    // live that TMMHEAD carries PAYMENTTERMSID for BOTH Purchase Invoice and Sales Invoice vouchers in
    // this schema, so the same join works unmodified for receivable bills too).
    //
    // Deliberately NOT ported (out of scope, receivable-only niche flags from legacy, kept AS-IS
    // rather than bidirectionalized — not part of what was asked): NewSaleDebit, OverallAmount,
    // DebitNote. TotalBalance is kept as legacy's own NET (both-signs) sum for field-catalog/back-compat
    // continuity, but the two ExpectedWeeks calculations use the new direction-PURE
    // TotalReceivableBalance/TotalPayableBalance instead — legacy's own ExpectedWeeks divides net
    // TotalBalance (receivable minus payable) by the collection average, which understates true
    // receivable exposure whenever an account carries offsetting payables; this is a deliberate
    // correctness fix, not behavior preserved from legacy.
    //
    // Cash-discount day-count convention DELIBERATELY differs from legacy's literal SQL
    // (`datediff(d,getdate(),ba.detaildate)` = detaildate-paydate, requiring negative FROMDAYS/TODAYS
    // slab values to ever match a real pending bill). This version uses the standard "days SINCE
    // invoice date" convention (`DATEDIFF(DAY, VBILL.DETAILDATE, @AsOnDate)`), matching how the one real
    // MCASHDISCOUNTDETAIL row on GB5DEMO actually reads (FROMDAYS=0, TODAYS=9999, PERCENTAGE=0 — i.e.
    // "any bill age, no discount configured" only makes sense measured forward from the invoice date).
    //
    // Legacy's dead FROM-clause join (mgcm bank / MBANKBRANCH bankbranch, joined but never selected)
    // is omitted entirely.
    //
    // Window functions (NoofBills/TotalBalance/TotalReceivableBalance/TotalPayableBalance) are computed
    // directly in the SELECT list via OVER(PARTITION BY...) — a T-SQL column alias defined in a SELECT
    // list cannot be referenced by another expression in that SAME SELECT list, so the few places that
    // need a window value twice (NewSaleDebit's noofbills<=1 zeroing, the two ExpectedWeeks CEILINGs)
    // repeat the full OVER(...) expression rather than referencing an alias — verbose but correct; SQL
    // Server computes each distinct window spec once internally regardless of how many times it's written.
    public static class AccountCollectionProjectionReportsQB
    {
        private const string SALES_INVOICE_CLASS_ID = "-1799999904";
        private const string RECEIPT_CLASS_ID = "-1399999988";
        private const string PAYMENT_CLASS_ID = "-1399999997";
        private const string DEBIT_NOTE_CLASS_IDS = "-1399999993,-1399999949";

        private const string OU_ACCESS_CEILING = @"
    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 NOOFBILLS_WINDOW =
            "COUNT(CASE WHEN ABS(PB.BALANCE) > 5 THEN 1 END) OVER (PARTITION BY ACC.ACCOUNTID, VBILL.ROUTEID)";

        private const string TOTALBALANCE_WINDOW =
            "SUM(PB.BALANCE) OVER (PARTITION BY ACC.ACCOUNTID, VBILL.ROUTEID)";

        private const string TOTAL_RECEIVABLE_BALANCE_WINDOW =
            "SUM(CASE WHEN PB.BALANCE > 0 THEN PB.BALANCE ELSE 0 END) OVER (PARTITION BY ACC.ACCOUNTID)";

        private const string TOTAL_PAYABLE_BALANCE_WINDOW =
            "SUM(CASE WHEN PB.BALANCE < 0 THEN -PB.BALANCE ELSE 0 END) OVER (PARTITION BY ACC.ACCOUNTID)";

        // Per-account+route trailing-3-month average SALES INVOICE value / 3 (legacy AVG_SALES_UPDATION).
        private const string QTR_AVG_SALES_APPLY = @"
OUTER APPLY (
    SELECT SUM(H2.BILLVALUE) / 3.0 AS QtrAvg
    FROM TMMHEAD H2
    INNER JOIN TVOUCHER V2 ON H2.VOUCHERID = V2.VOUCHERID
    INNER JOIN MBIZTRANSACTIONTYPE BTT2 ON V2.BIZTRANSACTIONTYPEID = BTT2.BIZTRANSACTIONTYPEID
    WHERE H2.PARTYID = ACC.ACCOUNTID AND H2.ROUTEID = VBILL.ROUTEID
      AND BTT2.BIZTRANSACTIONCLASSID = " + SALES_INVOICE_CLASS_ID + @"
      AND V2.OUID = V.OUID
      AND H2.DOCUMENTDATE >= DATEADD(MONTH, -3, @AsOnDate) AND H2.DOCUMENTDATE <= @AsOnDate
) QAS";

        // Per-account+route trailing-3-month average RECEIPT (collection) credit amount (legacy TEMP_AVG_COLLECTION).
        private const string QTR_AVG_COLLECTION_APPLY = @"
OUTER APPLY (
    SELECT ABS(SUM(B2.ALLOCATEDAMOUNT)) AS QtrCollAvg
    FROM TBILLALLOCATION B2
    INNER JOIN TVOUCHERDETAIL C2 ON B2.VOUCHERDETAILID = C2.VOUCHERDETAILID
    INNER JOIN TVOUCHER D2 ON C2.VOUCHERID = D2.VOUCHERID
    INNER JOIN MBIZTRANSACTIONTYPE E2 ON D2.BIZTRANSACTIONTYPEID = E2.BIZTRANSACTIONTYPEID
    WHERE B2.ALLOCATEDAMOUNT < 0 AND C2.VOUCHERACCOUNTID = ACC.ACCOUNTID AND B2.ROUTEID = VBILL.ROUTEID
      AND E2.BIZTRANSACTIONCLASSID = " + RECEIPT_CLASS_ID + @"
      AND D2.OUID = V.OUID
      AND D2.VOUCHERDATE >= DATEADD(MONTH, -3, @AsOnDate) AND D2.VOUCHERDATE <= @AsOnDate
) QAC";

        // Per-account+route trailing-3-month average PAYMENT debit amount — payable-side symmetric
        // addition to QTR_AVG_COLLECTION_APPLY, feeding ExpectedWeeksToPay ("projected outflow").
        private const string QTR_AVG_PAYMENT_APPLY = @"
OUTER APPLY (
    SELECT SUM(B3.ALLOCATEDAMOUNT) AS QtrPayAvg
    FROM TBILLALLOCATION B3
    INNER JOIN TVOUCHERDETAIL C3 ON B3.VOUCHERDETAILID = C3.VOUCHERDETAILID
    INNER JOIN TVOUCHER D3 ON C3.VOUCHERID = D3.VOUCHERID
    INNER JOIN MBIZTRANSACTIONTYPE E3 ON D3.BIZTRANSACTIONTYPEID = E3.BIZTRANSACTIONTYPEID
    WHERE B3.ALLOCATEDAMOUNT > 0 AND C3.VOUCHERACCOUNTID = ACC.ACCOUNTID AND B3.ROUTEID = VBILL.ROUTEID
      AND E3.BIZTRANSACTIONCLASSID = " + PAYMENT_CLASS_ID + @"
      AND D3.OUID = V.OUID
      AND D3.VOUCHERDATE >= DATEADD(MONTH, -3, @AsOnDate) AND D3.VOUCHERDATE <= @AsOnDate
) QAP";

        // Last settlement (in the SAME direction as this bill) date/days-since — bidirectional reuse of
        // legacy's "LastCollectionDate/Days" field slot: for a receivable row this is the last credit
        // (collection) posted against the bill; for a payable row it's the last debit (payment). Days-
        // since is measured from @AsOnDate (a deliberate improvement over legacy's literal GETDATE(),
        // consistent with the rest of this report being AsOnDate-relative).
        private const string LAST_SETTLEMENT_APPLY = @"
OUTER APPLY (
    SELECT MAX(D4.VOUCHERDATE) AS LastCollectionDate
    FROM TBILLALLOCATION B4
    INNER JOIN TVOUCHERDETAIL C4 ON B4.VOUCHERDETAILID = C4.VOUCHERDETAILID
    INNER JOIN TVOUCHER D4 ON C4.VOUCHERID = D4.VOUCHERID
    WHERE B4.ALLOCATIONLINEID = PB.ALLOCATIONLINEID
      AND ((PB.BALANCE > 0 AND B4.ALLOCATEDAMOUNT < 0) OR (PB.BALANCE <= 0 AND B4.ALLOCATEDAMOUNT > 0))
) LC";

        // Receivable-only niche flags kept verbatim from legacy (not bidirectionalized — out of scope).
        private const string NEW_SALE_DEBIT_APPLY = @"
OUTER APPLY (
    SELECT SUM(A5.ALLOCATEDAMOUNT) AS NewSaleDebit
    FROM TBILLALLOCATION A5
    INNER JOIN TVOUCHERDETAIL B5 ON A5.VOUCHERDETAILID = B5.VOUCHERDETAILID
    INNER JOIN TVOUCHER C5 ON B5.VOUCHERID = C5.VOUCHERID
    INNER JOIN MBIZTRANSACTIONTYPE D5 ON C5.BIZTRANSACTIONTYPEID = D5.BIZTRANSACTIONTYPEID
    WHERE A5.ALLOCATIONTYPE = 0 AND A5.ALLOCATEDAMOUNT > 0
      AND D5.BIZTRANSACTIONCLASSID = " + SALES_INVOICE_CLASS_ID + @"
      AND C5.OUID = V.OUID AND A5.ROUTEID = VBILL.ROUTEID AND B5.VOUCHERACCOUNTID = ACC.ACCOUNTID
      AND C5.VOUCHERDATE = @AsOnDate
) NSD";

        private const string OVERALL_AMOUNT_APPLY = @"
OUTER APPLY (
    SELECT SUM(A6.BALANCE) AS OverallAmount
    FROM FNPENDINGBILL(@AsOnDate, '', @InfoRequired) A6
    INNER JOIN TBILLALLOCATION B6 ON A6.ALLOCATIONLINEID = B6.BILLALLOCATIONLINEID
    INNER JOIN TVOUCHERDETAIL C6 ON B6.VOUCHERDETAILID = C6.VOUCHERDETAILID
    WHERE B6.BILLALLOCATIONLINEID <> -1 AND A6.BALANCE > 0 AND C6.VOUCHERACCOUNTID = ACC.ACCOUNTID
      AND DATEDIFF(DAY, B6.DETAILDATE, GETUTCDATE()) >= @OverallDays
) OA";

        private const string DEBIT_NOTE_APPLY = @"
OUTER APPLY (
    SELECT SUM(A7.BALANCE) AS DebitNote
    FROM FNPENDINGBILL(@AsOnDate, '', @InfoRequired) A7
    INNER JOIN TBILLALLOCATION B7 ON A7.ALLOCATIONLINEID = B7.BILLALLOCATIONLINEID
    INNER JOIN TVOUCHERDETAIL C7 ON B7.VOUCHERDETAILID = C7.VOUCHERDETAILID
    INNER JOIN TVOUCHER D7 ON C7.VOUCHERID = D7.VOUCHERID
    INNER JOIN MBIZTRANSACTIONTYPE E7 ON D7.BIZTRANSACTIONTYPEID = E7.BIZTRANSACTIONTYPEID
    WHERE B7.BILLALLOCATIONLINEID <> -1 AND A7.BALANCE > 0 AND C7.VOUCHERACCOUNTID = ACC.ACCOUNTID
      AND E7.BIZTRANSACTIONCLASSID IN (" + DEBIT_NOTE_CLASS_IDS + @")
) DN";

        // Cash-discount-for-early-payment benefit — bidirectional (TMMHEAD.PAYMENTTERMSID carries the
        // actual payment term for BOTH Purchase Invoice and Sales Invoice vouchers, confirmed live).
        // Picks the best (highest) percentage whose slab window currently covers @AsOnDate.
        private const string CASH_DISCOUNT_APPLY = @"
OUTER APPLY (
    SELECT TOP 1 CD.PERCENTAGE, CD.TYPE
    FROM MPAYMENTTERMDETAIL PTD
    INNER JOIN MCASHDISCOUNTDETAIL CD ON PTD.CASHDISCOUNTID = CD.CASHDISCOUNTID
    WHERE PTD.PAYMENTTERMID = H.PAYMENTTERMSID
      AND DATEDIFF(DAY, VBILL.DETAILDATE, @AsOnDate) BETWEEN CD.FROMDAYS AND CD.TODAYS
    ORDER BY CD.PERCENTAGE DESC
) CDISC";

        public const string GET_COLLECTION_PROJECTION_DETAIL = @"
SELECT
    V.VOUCHERID AS VoucherId, V.VOUCHERNUMBER AS VoucherNumber, V.VOUCHERDATE AS VoucherDate,
    VBILL.REFERENCENUMBER AS ReferenceNumber, VBILL.REFERENCEDATE AS ReferenceDate,
    VBILL.DETAILNUMBER AS DetailNumber, VBILL.DETAILDATE AS DetailDate, VBILL.DUEDATE AS DueDate,
    ACC.ACCOUNTID AS AccountId, ACC.ACCOUNTCODE AS AccountCode, ACC.ACCOUNTNAME AS AccountName,
    AD.ADDRESSLINE1 AS AddressLine1, AD.ADDRESSLINE2 AS AddressLine2,
    AD.ADDRESSLINE3 AS AddressLine3, AD.ADDRESSLINE4 AS AddressLine4,
    CONCAT(AD.ADDRESSLINE1, ' ', AD.ADDRESSLINE2, ' ', AD.ADDRESSLINE3) AS Address,
    ACC.CONTROLACCOUNTID AS ControlAccountId, CTRL.ACCOUNTCODE AS ControlAccountCode, CTRL.ACCOUNTNAME AS ControlAccountName,
    VBILL.ROUTEID AS RouteId, RT.ROUTECODE AS RoutingCode, RT.ROUTENAME AS RoutingName,
    EMP.EMPLOYEECODE AS SmCode, EMP.EMPLOYEENAME AS SmName,
    ABS(VBILL.FULLAMOUNT) AS Amount,
    CASE WHEN PB.BALANCE > 0 THEN PB.BALANCE ELSE 0 END AS Receivable,
    CASE WHEN PB.BALANCE < 0 THEN ABS(PB.BALANCE) ELSE 0 END AS Payable,
    CASE WHEN PB.BALANCE < 0 THEN '-' ELSE '+' END AS BalanceType,
    PB.BALANCE AS Balance,
    PB.ALLOCATIONLINEID AS AllocationLineId,
    CASE WHEN VBILL.FULLAMOUNT = 0 THEN 100 ELSE ROUND(PB.BALANCE / ABS(VBILL.FULLAMOUNT) * 100, 2) END AS PercentPending,
    " + NOOFBILLS_WINDOW + @" AS NoofBills,
    " + TOTALBALANCE_WINDOW + @" AS TotalBalance,
    " + TOTAL_RECEIVABLE_BALANCE_WINDOW + @" AS TotalReceivableBalance,
    " + TOTAL_PAYABLE_BALANCE_WINDOW + @" AS TotalPayableBalance,
    ISNULL(NSD.NewSaleDebit, 0) * CASE WHEN " + NOOFBILLS_WINDOW + @" <= 1 THEN 0 ELSE 1 END AS NewSaleDebit,
    ISNULL(QAS.QtrAvg, 0) AS QtrAvg,
    LC.LastCollectionDate,
    CASE WHEN LC.LastCollectionDate IS NULL THEN NULL ELSE DATEDIFF(DAY, LC.LastCollectionDate, @AsOnDate) END AS LastCollectionDays,
    ISNULL(OA.OverallAmount, 0) AS OverallAmount,
    ISNULL(DN.DebitNote, 0) AS DebitNote,
    ISNULL(QAC.QtrCollAvg, 0) AS QtrCollAvg,
    ROUND(ISNULL(QAC.QtrCollAvg, 0) / 12, 2) AS AvgWeekColl,
    CASE WHEN ISNULL(QAC.QtrCollAvg, 0) = 0 THEN 99
         ELSE CEILING(" + TOTAL_RECEIVABLE_BALANCE_WINDOW + @" / (QAC.QtrCollAvg / 12.0)) END AS ExpectedWeeks,
    ISNULL(QAP.QtrPayAvg, 0) AS QtrPayAvg,
    CASE WHEN ISNULL(QAP.QtrPayAvg, 0) = 0 THEN 99
         ELSE CEILING(" + TOTAL_PAYABLE_BALANCE_WINDOW + @" / (QAP.QtrPayAvg / 12.0)) END AS ExpectedWeeksToPay,
    CDISC.PERCENTAGE AS CashDiscountPercentage,
    CASE WHEN CDISC.PERCENTAGE IS NULL OR CDISC.PERCENTAGE = 0 THEN 0
         ELSE ROUND(ABS(PB.BALANCE) * CDISC.PERCENTAGE / 100 *
              CASE WHEN CDISC.TYPE = 0 THEN 1 ELSE DATEDIFF(DAY, VBILL.DETAILDATE, @AsOnDate) / 365.0 END, 2)
    END AS CashDiscountAmount
FROM FNPENDINGBILL(@AsOnDate, '', @InfoRequired) PB
INNER JOIN TBILLALLOCATION VBILL ON PB.ALLOCATIONLINEID = VBILL.BILLALLOCATIONLINEID AND VBILL.BILLALLOCATIONLINEID <> -1
INNER JOIN TVOUCHERDETAIL VD ON VBILL.VOUCHERDETAILID = VD.VOUCHERDETAILID
INNER JOIN TVOUCHER V ON VD.VOUCHERID = V.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MACCOUNT CTRL ON ACC.CONTROLACCOUNTID = CTRL.ACCOUNTID
LEFT JOIN MADDRESS AD ON AD.ADDRESSID = ACC.DEFAULTADDRESSID
LEFT JOIN MROUTE RT ON VBILL.ROUTEID = RT.ROUTEID
LEFT JOIN MEMPLOYEE EMP ON VBILL.INCHARGEID = EMP.EMPLOYEEID
LEFT JOIN TMMHEAD H ON H.VOUCHERID = V.VOUCHERID
" + QTR_AVG_SALES_APPLY + @"
" + QTR_AVG_COLLECTION_APPLY + @"
" + QTR_AVG_PAYMENT_APPLY + @"
" + LAST_SETTLEMENT_APPLY + @"
" + NEW_SALE_DEBIT_APPLY + @"
" + OVERALL_AMOUNT_APPLY + @"
" + DEBIT_NOTE_APPLY + @"
" + CASH_DISCOUNT_APPLY + @"
WHERE " + OU_ACCESS_CEILING + @"
  {DynamicFilter}
  AND (CASE WHEN PB.BALANCE > 0 THEN PB.BALANCE ELSE 0 END > 5 OR CASE WHEN PB.BALANCE < 0 THEN ABS(PB.BALANCE) ELSE 0 END > 5)
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_COLLECTION_PROJECTION_TOTALS = @"
SELECT
    SUM(CASE WHEN PB.BALANCE > 0 THEN PB.BALANCE ELSE 0 END) AS TotalReceivable,
    SUM(CASE WHEN PB.BALANCE < 0 THEN ABS(PB.BALANCE) ELSE 0 END) AS TotalPayable,
    SUM(PB.BALANCE) AS TotalBalance,
    SUM(CASE WHEN CDISC.PERCENTAGE IS NULL OR CDISC.PERCENTAGE = 0 THEN 0
             ELSE ROUND(ABS(PB.BALANCE) * CDISC.PERCENTAGE / 100 *
                  CASE WHEN CDISC.TYPE = 0 THEN 1 ELSE DATEDIFF(DAY, VBILL.DETAILDATE, @AsOnDate) / 365.0 END, 2)
        END) AS TotalCashDiscountAmount
FROM FNPENDINGBILL(@AsOnDate, '', @InfoRequired) PB
INNER JOIN TBILLALLOCATION VBILL ON PB.ALLOCATIONLINEID = VBILL.BILLALLOCATIONLINEID AND VBILL.BILLALLOCATIONLINEID <> -1
INNER JOIN TVOUCHERDETAIL VD ON VBILL.VOUCHERDETAILID = VD.VOUCHERDETAILID
INNER JOIN TVOUCHER V ON VD.VOUCHERID = V.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN TMMHEAD H ON H.VOUCHERID = V.VOUCHERID
" + CASH_DISCOUNT_APPLY + @"
WHERE " + OU_ACCESS_CEILING + @"
  {DynamicFilter}
  AND (CASE WHEN PB.BALANCE > 0 THEN PB.BALANCE ELSE 0 END > 5 OR CASE WHEN PB.BALANCE < 0 THEN ABS(PB.BALANCE) ELSE 0 END > 5);";
    }
}