namespace AccountsDAL.Query.AccountReports
{
    // GB4 GetSettlementRegisterReport/GetSettlementRegisterWithRate (AccountsReportsDAL.cs) migration —
    // a flat settlement/bill-allocation AUDIT TRAIL, architecturally distinct from
    // AccountOutstandingReportsQB/AccountAgeingReportsQB's as-of-date balance-summing pattern: one row
    // per settlement event (TBILLALLOCATION row sharing an ALLOCATIONLINEID with its root bill), not a
    // dynamically-summed balance. Confirmed live via sqlcmd against GB5DEMO before wiring into C#
    // (12 real settlement rows returned for both the plain and With-Rate variants).
    //
    // Bill trail shape (mirrors legacy exactly):
    //   TBILLALLOCATION VB — the ROOT bill row for this voucher detail line (BILLALLOCATIONLINEID = ALLOCATIONLINEID)
    //   TBILLALLOCATION E  — every row (including VB itself) sharing that ALLOCATIONLINEID — i.e. the
    //                        root bill's own "New" row PLUS every settlement/advance/against/batch event
    //                        posted against it. E.ALLOCATIONTYPE: 0=New/1=Advance/2=Against/3=Unadjusted/4=Batch.
    //   TVOUCHER AV        — the SETTLING voucher for a non-New allocation type (E.ALLOCATIONTYPE NOT IN (0,1,3));
    //                        for New/Advance/Unadjusted rows, "AllocationVoucherNumber" etc. instead surface
    //                        E's own DETAILNUMBER/DETAILDATE/REFERENCENUMBER/REFERENCEDATE (no settling voucher yet).
    //
    // Period filter is on AV.VOUCHERDATE (the SETTLING voucher's date) — confirmed against legacy's own
    // SETTLEMENT_REGISTER_REPORT constant ("av.voucherDATE >=':periodfromdate'... --Changed for TVB as
    // per Mani sir sugesstion"), NOT a.voucherdate (the original bill voucher). Legacy's separate
    // ApplySettlementRegisterWithRate criteria-field mapping maps PeriodFromDate/PeriodToDate to
    // a.voucherdate instead — an inconsistency between the two legacy report variants, most likely a
    // copy-paste artifact rather than an intentional semantic difference (both reports describe the same
    // "settlements posted in this period" concept). This migration uses AV.VOUCHERDATE for BOTH variants,
    // matching the non-rate report's more deliberate-looking comment.
    //
    // Two fixed (non-user-configurable) business-rule filters kept verbatim from legacy, baked directly
    // into the SQL text rather than exposed as criteria fields: C.BILLALLOCATIONTYPE IN (1,2) ("auto and
    // manual bill allocation parties only") and A.ISACCOUNTPOST IN (0) ("only with account post yes").
    //
    // Same no-leading-CTE constraint as every other report in this family (QueryPagedAsync wraps the
    // caller's SQL as `SELECT COUNT(*) FROM (<sql>) AS Total`) — not an issue here since this query has
    // no opening-balance CTE/subquery to begin with.
    public static class AccountSettlementReportsQB
    {
        private const string OU_ACCESS_CEILING = @"
    A.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
    )";

        public const string GET_SETTLEMENT_REGISTER_DETAIL = @"
SELECT
    C.ACCOUNTCODE AS AccountCode, C.ACCOUNTNAME AS AccountName,
    A.VOUCHERNUMBER AS VoucherNumber, A.VOUCHERDATE AS VoucherDate,
    A.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber, A.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
    TYP.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode, TYP.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,
    CASE B.DETAILTYPE WHEN 0 THEN B.VOUCHERAMOUNT ELSE 0 END AS VoucherDebit,
    CASE B.DETAILTYPE WHEN 1 THEN B.VOUCHERAMOUNT ELSE 0 END AS VoucherCredit,
    E.SLNO AS SlNo,
    E.ALLOCATIONLINEID AS AllocationLineId,
    E.ALLOCATIONTYPE AS AllocationType,
    CASE E.ALLOCATIONTYPE WHEN 0 THEN 'N' WHEN 1 THEN 'A' WHEN 2 THEN 'T' WHEN 3 THEN 'U' WHEN 4 THEN 'B' END AS Type,
    E.REFERENCENUMBER AS BillReferenceNumber,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.DETAILNUMBER ELSE AV.VOUCHERNUMBER END AS AllocationVoucherNumber,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.DETAILDATE ELSE AV.VOUCHERDATE END AS AllocationVoucherDate,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.REFERENCENUMBER ELSE AV.VOUCHERREFERENCENUMBER END AS AllocationReferenceNumber,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.REFERENCEDATE ELSE AV.VOUCHERREFERENCEDATE END AS AllocationReferenceDate,
    CASE WHEN E.ALLOCATEDAMOUNT > 0 THEN E.ALLOCATEDAMOUNT ELSE 0 END AS AllocationDebit,
    CASE WHEN E.ALLOCATEDAMOUNT < 0 THEN ABS(E.ALLOCATEDAMOUNT) ELSE 0 END AS AllocationCredit
FROM MACCOUNT C
INNER JOIN TVOUCHERDETAIL B ON B.VOUCHERACCOUNTID = C.ACCOUNTID
INNER JOIN TVOUCHER A ON A.VOUCHERID = B.VOUCHERID
INNER JOIN MBIZTRANSACTIONTYPE TYP ON A.BIZTRANSACTIONTYPEID = TYP.BIZTRANSACTIONTYPEID
LEFT OUTER JOIN TBILLALLOCATION VB ON B.VOUCHERDETAILID = VB.VOUCHERDETAILID AND VB.BILLALLOCATIONLINEID = VB.ALLOCATIONLINEID
LEFT OUTER JOIN TBILLALLOCATION E ON VB.ALLOCATIONLINEID = E.ALLOCATIONLINEID
LEFT OUTER JOIN TVOUCHERDETAIL AVD ON E.VOUCHERDETAILID = AVD.VOUCHERDETAILID
LEFT OUTER JOIN TVOUCHER AV ON AVD.VOUCHERID = AV.VOUCHERID
WHERE C.BILLALLOCATIONTYPE IN (1,2)
  AND A.ISACCOUNTPOST IN (0)
  AND " + OU_ACCESS_CEILING + @"
  AND AV.VOUCHERDATE >= @PeriodFromDate AND AV.VOUCHERDATE <= @PeriodToDate
  {DynamicFilter}
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_SETTLEMENT_REGISTER_TOTALS = @"
SELECT
    SUM(CASE B.DETAILTYPE WHEN 0 THEN B.VOUCHERAMOUNT ELSE 0 END) AS TotalVoucherDebit,
    SUM(CASE B.DETAILTYPE WHEN 1 THEN B.VOUCHERAMOUNT ELSE 0 END) AS TotalVoucherCredit,
    SUM(CASE WHEN E.ALLOCATEDAMOUNT > 0 THEN E.ALLOCATEDAMOUNT ELSE 0 END) AS TotalAllocationDebit,
    SUM(CASE WHEN E.ALLOCATEDAMOUNT < 0 THEN ABS(E.ALLOCATEDAMOUNT) ELSE 0 END) AS TotalAllocationCredit
FROM MACCOUNT C
INNER JOIN TVOUCHERDETAIL B ON B.VOUCHERACCOUNTID = C.ACCOUNTID
INNER JOIN TVOUCHER A ON A.VOUCHERID = B.VOUCHERID
INNER JOIN MBIZTRANSACTIONTYPE TYP ON A.BIZTRANSACTIONTYPEID = TYP.BIZTRANSACTIONTYPEID
LEFT OUTER JOIN TBILLALLOCATION VB ON B.VOUCHERDETAILID = VB.VOUCHERDETAILID AND VB.BILLALLOCATIONLINEID = VB.ALLOCATIONLINEID
LEFT OUTER JOIN TBILLALLOCATION E ON VB.ALLOCATIONLINEID = E.ALLOCATIONLINEID
LEFT OUTER JOIN TVOUCHERDETAIL AVD ON E.VOUCHERDETAILID = AVD.VOUCHERDETAILID
LEFT OUTER JOIN TVOUCHER AV ON AVD.VOUCHERID = AV.VOUCHERID
WHERE C.BILLALLOCATIONTYPE IN (1,2)
  AND A.ISACCOUNTPOST IN (0)
  AND " + OU_ACCESS_CEILING + @"
  AND AV.VOUCHERDATE >= @PeriodFromDate AND AV.VOUCHERDATE <= @PeriodToDate
  {DynamicFilter};";

        // With-Rate variant — same join shape plus AccountId/Country (via MACCOUNT.DEFAULTADDRESSID ->
        // MADDRESS -> MCOUNTRY, LEFT-joined since not every account has a default address, confirmed live)
        // and Currency (MCURRENCY on TVOUCHERDETAIL.CURRENCYID, INNER-joined — every real TVOUCHERDETAIL
        // row carries a currency) plus the *Fc (transaction-currency) amount columns.
        public const string GET_SETTLEMENT_REGISTER_WITH_RATE_DETAIL = @"
SELECT
    C.ACCOUNTID AS AccountId, C.ACCOUNTCODE AS AccountCode, C.ACCOUNTNAME AS AccountName,
    CT.COUNTRYCODE AS CountryCode, CT.COUNTRYNAME AS CountryName,
    A.VOUCHERNUMBER AS VoucherNumber, A.VOUCHERDATE AS VoucherDate,
    A.VOUCHERREFERENCENUMBER AS VoucherReferenceNumber, A.VOUCHERREFERENCEDATE AS VoucherReferenceDate,
    TYP.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode, TYP.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,
    CASE B.DETAILTYPE WHEN 0 THEN B.VOUCHERAMOUNT ELSE 0 END AS VoucherDebit,
    CASE B.DETAILTYPE WHEN 1 THEN B.VOUCHERAMOUNT ELSE 0 END AS VoucherCredit,
    CASE B.DETAILTYPE WHEN 0 THEN B.VOUCHERAMOUNTFC ELSE 0 END AS VoucherDebitFc,
    CASE B.DETAILTYPE WHEN 1 THEN B.VOUCHERAMOUNTFC ELSE 0 END AS VoucherCreditFc,
    BC.CURRENCYCODE AS CurrencyCode, B.CURRENCYCONVERSION AS CurrencyConversion,
    E.SLNO AS SlNo, E.ALLOCATIONLINEID AS AllocationLineId, E.ALLOCATIONTYPE AS AllocationType,
    CASE E.ALLOCATIONTYPE WHEN 0 THEN 'N' WHEN 1 THEN 'A' WHEN 2 THEN 'T' WHEN 3 THEN 'U' WHEN 4 THEN 'B' END AS Type,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.DETAILNUMBER ELSE AV.VOUCHERNUMBER END AS AllocationVoucherNumber,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.DETAILDATE ELSE AV.VOUCHERDATE END AS AllocationVoucherDate,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.REFERENCENUMBER ELSE AV.VOUCHERREFERENCENUMBER END AS AllocationReferenceNumber,
    CASE WHEN E.ALLOCATIONTYPE IN (0,1,3) THEN E.REFERENCEDATE ELSE AV.VOUCHERREFERENCEDATE END AS AllocationReferenceDate,
    CASE WHEN E.ALLOCATEDAMOUNT > 0 THEN E.ALLOCATEDAMOUNT ELSE 0 END AS AllocationDebit,
    CASE WHEN E.ALLOCATEDAMOUNT < 0 THEN ABS(E.ALLOCATEDAMOUNT) ELSE 0 END AS AllocationCredit
FROM MACCOUNT C
INNER JOIN TVOUCHERDETAIL B ON B.VOUCHERACCOUNTID = C.ACCOUNTID
INNER JOIN TVOUCHER A ON A.VOUCHERID = B.VOUCHERID
INNER JOIN MBIZTRANSACTIONTYPE TYP ON A.BIZTRANSACTIONTYPEID = TYP.BIZTRANSACTIONTYPEID
INNER JOIN MCURRENCY BC ON B.CURRENCYID = BC.CURRENCYID
LEFT JOIN MADDRESS AD ON AD.ADDRESSID = C.DEFAULTADDRESSID
LEFT JOIN MCOUNTRY CT ON AD.COUNTRYID = CT.COUNTRYID
LEFT OUTER JOIN TBILLALLOCATION VB ON B.VOUCHERDETAILID = VB.VOUCHERDETAILID AND VB.BILLALLOCATIONLINEID = VB.ALLOCATIONLINEID
LEFT OUTER JOIN TBILLALLOCATION E ON VB.ALLOCATIONLINEID = E.ALLOCATIONLINEID
LEFT OUTER JOIN TVOUCHERDETAIL AVD ON E.VOUCHERDETAILID = AVD.VOUCHERDETAILID
LEFT OUTER JOIN TVOUCHER AV ON AVD.VOUCHERID = AV.VOUCHERID
WHERE C.BILLALLOCATIONTYPE IN (1,2)
  AND A.ISACCOUNTPOST IN (0)
  AND " + OU_ACCESS_CEILING + @"
  AND AV.VOUCHERDATE >= @PeriodFromDate AND AV.VOUCHERDATE <= @PeriodToDate
  {DynamicFilter}
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_SETTLEMENT_REGISTER_WITH_RATE_TOTALS = @"
SELECT
    SUM(CASE B.DETAILTYPE WHEN 0 THEN B.VOUCHERAMOUNT ELSE 0 END) AS TotalVoucherDebit,
    SUM(CASE B.DETAILTYPE WHEN 1 THEN B.VOUCHERAMOUNT ELSE 0 END) AS TotalVoucherCredit,
    SUM(CASE WHEN E.ALLOCATEDAMOUNT > 0 THEN E.ALLOCATEDAMOUNT ELSE 0 END) AS TotalAllocationDebit,
    SUM(CASE WHEN E.ALLOCATEDAMOUNT < 0 THEN ABS(E.ALLOCATEDAMOUNT) ELSE 0 END) AS TotalAllocationCredit
FROM MACCOUNT C
INNER JOIN TVOUCHERDETAIL B ON B.VOUCHERACCOUNTID = C.ACCOUNTID
INNER JOIN TVOUCHER A ON A.VOUCHERID = B.VOUCHERID
INNER JOIN MBIZTRANSACTIONTYPE TYP ON A.BIZTRANSACTIONTYPEID = TYP.BIZTRANSACTIONTYPEID
INNER JOIN MCURRENCY BC ON B.CURRENCYID = BC.CURRENCYID
LEFT OUTER JOIN TBILLALLOCATION VB ON B.VOUCHERDETAILID = VB.VOUCHERDETAILID AND VB.BILLALLOCATIONLINEID = VB.ALLOCATIONLINEID
LEFT OUTER JOIN TBILLALLOCATION E ON VB.ALLOCATIONLINEID = E.ALLOCATIONLINEID
LEFT OUTER JOIN TVOUCHERDETAIL AVD ON E.VOUCHERDETAILID = AVD.VOUCHERDETAILID
LEFT OUTER JOIN TVOUCHER AV ON AVD.VOUCHERID = AV.VOUCHERID
WHERE C.BILLALLOCATIONTYPE IN (1,2)
  AND A.ISACCOUNTPOST IN (0)
  AND " + OU_ACCESS_CEILING + @"
  AND AV.VOUCHERDATE >= @PeriodFromDate AND AV.VOUCHERDATE <= @PeriodToDate
  {DynamicFilter};";
    }
}