namespace AccountsDAL.Query.AccountPost
{
    public static class AccountPostQB
    {
        // Ported from GB4 VoucherBLL.ReAccounPostingFast / ReAccountPostingforAllPeriod /
        // UpdateTAccountDuringPost (Voucher.svc's ReAccountPostFast / ReAccountPostFastAllOU
        // operations) — a full delete + rebuild + recompute of TACCOUNTPOST (the running ledger
        // balance table) for a given OU, optionally scoped to one Period and/or one Account. This
        // is NOT an incremental update — existing rows in scope are deleted and rebuilt from
        // scratch every time.
        //
        // GB4 ran this as a fire-and-forget background job that emailed the user on completion;
        // per explicit direction this port runs synchronously instead (VoucherBLL awaits each step
        // and returns once done) and does not send an email.
        //
        // Schema confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-25):
        //  - TVOUCHER/TVOUCHERDETAIL/TACCOUNTPOST have NO TENANTID column (per-tenant-database
        //    model, not a shared multi-tenant table — matches the same finding already applied to
        //    the Opening Balance Save/Update queries above).
        //  - TVOUCHERDETAIL's account column is VOUCHERACCOUNTID (not ACCOUNTID); amount column
        //    used here is VOUCHERAMOUNT (not AMOUNT).
        //  - TACCOUNTPOST has a real surrogate PK, ACCOUNTPOSTID — used below to make the
        //    correlated UPDATE...FROM joins unambiguous.
        //  - MACCOUNT carries ACCOUNTSCHEDULENATURE directly (no need for GB4's
        //    MACCOUNT->MACCOUNTGROUP->MACCOUNTSCHEDULE join chain to get the same value).
        //
        // "Provisional" split reuses GB4's own BizTransactionClass sentinel id (-1399999991).
        // DETAILTYPE: 0 = Debit, 1 = Credit (same convention already used elsewhere in
        // VoucherQB.cs, e.g. GET_SELECTLIST_VOUCHER's Status mapping / VoucherDetailDTO).
        // Balance-sheet-nature carry-forward uses ACCOUNTSCHEDULENATURE IN (0, 1), matching GB4's
        // FINDING_PREV_YEAR_CLOSING_BALANCE_FOR_BALANCE_SHEET_ACCOUNTS filter.
        //
        // NOT ported from GB4: the redundant "zero out all amount columns" pass GB4 ran before the
        // debit/credit recompute — skipped here since the immediately-preceding INSERT already
        // creates every row with all amount columns at 0, making that pass a no-op.

        public const string GET_ACTIVE_OU_IDS = @"
            SELECT OUID
            FROM MORGANIZATIONUNIT
            WHERE OUID <> -1 AND STATUS = 1
            ORDER BY OUID;";

        // ── Step 1: delete + rebuild skeleton rows (one per distinct Account/SubAccount/Period
        //            combination found in unposted voucher details for this OU) ────────────────

        public const string DELETE_ACCOUNT_POST_FOR_PERIOD = @"
            DELETE FROM TACCOUNTPOST
            WHERE OUID = @OUId AND PERIODID = @PeriodId
              AND (@AccountId = -1 OR ACCOUNTID = @AccountId);";

        public const string INSERT_ACCOUNT_POST_FOR_PERIOD = @"
            INSERT INTO TACCOUNTPOST
            (OUID, PERIODID, ACCOUNTID, SUBACCOUNTID, DEBIT, CREDIT, OPENING, CLOSING,
             PROVISIONALDEBIT, PROVISIONALCREDIT, PROVISIONALOPENING, PROVISIONALCLOSING)
            SELECT @OUId, acc.PeriodId, acc.AccountId, acc.SubAccountId,
                   0, 0, 0, 0, 0, 0, 0, 0
            FROM (
                SELECT DISTINCT D.VOUCHERACCOUNTID AS AccountId, D.SUBACCOUNTID AS SubAccountId, V.PERIODID AS PeriodId
                FROM TVOUCHERDETAIL D
                INNER JOIN TVOUCHER V ON D.VOUCHERID = V.VOUCHERID
                WHERE V.OUID = @OUId AND V.PERIODID = @PeriodId
                  AND (@AccountId = -1 OR D.VOUCHERACCOUNTID = @AccountId)
            ) acc
            ORDER BY acc.AccountId;";

        public const string DELETE_ACCOUNT_POST_FOR_ALL_PERIODS = @"
            DELETE FROM TACCOUNTPOST
            WHERE OUID = @OUId
              AND (@AccountId = -1 OR ACCOUNTID = @AccountId);";

        public const string INSERT_ACCOUNT_POST_FOR_ALL_PERIODS = @"
            INSERT INTO TACCOUNTPOST
            (OUID, PERIODID, ACCOUNTID, SUBACCOUNTID, DEBIT, CREDIT, OPENING, CLOSING,
             PROVISIONALDEBIT, PROVISIONALCREDIT, PROVISIONALOPENING, PROVISIONALCLOSING)
            SELECT @OUId, acc.PeriodId, acc.AccountId, acc.SubAccountId,
                   0, 0, 0, 0, 0, 0, 0, 0
            FROM (
                SELECT DISTINCT D.VOUCHERACCOUNTID AS AccountId, D.SUBACCOUNTID AS SubAccountId, V.PERIODID AS PeriodId
                FROM TVOUCHERDETAIL D
                INNER JOIN TVOUCHER V ON D.VOUCHERID = V.VOUCHERID
                WHERE V.OUID = @OUId AND V.PERIODID <> -1
                  AND (@AccountId = -1 OR D.VOUCHERACCOUNTID = @AccountId)
            ) acc
            ORDER BY acc.PeriodId, acc.AccountId;";

        // ── Step 2: recompute Debit/Credit/Closing from unposted (ISACCOUNTPOST=0) voucher
        //            details, splitting out the "Provisional" amounts for BizTransactionClass
        //            -1399999991 ─────────────────────────────────────────────────────────────

        public const string UPDATE_ACCOUNT_POST_DEBIT_CREDIT = @"
            UPDATE TACCOUNTPOST
            SET DEBIT = final.DebitAmount,
                CREDIT = final.CreditAmount,
                CLOSING = TACCOUNTPOST.OPENING + final.DebitAmount - final.CreditAmount,
                PROVISIONALDEBIT = final.ProvisionalDebitAmount,
                PROVISIONALCREDIT = final.ProvisionalCreditAmount,
                PROVISIONALCLOSING = TACCOUNTPOST.PROVISIONALOPENING + final.ProvisionalDebitAmount - final.ProvisionalCreditAmount
            FROM (
                SELECT
                    D.VOUCHERACCOUNTID AS AccountId,
                    D.SUBACCOUNTID AS SubAccountId,
                    V.PERIODID AS PeriodId,
                    SUM(CASE WHEN D.DETAILTYPE = 0 THEN D.VOUCHERAMOUNT ELSE 0 END) AS DebitAmount,
                    SUM(CASE WHEN D.DETAILTYPE = 1 THEN D.VOUCHERAMOUNT ELSE 0 END) AS CreditAmount,
                    SUM(CASE WHEN BTC.BIZTRANSACTIONCLASSID = -1399999991 AND D.DETAILTYPE = 0 THEN D.VOUCHERAMOUNT ELSE 0 END) AS ProvisionalDebitAmount,
                    SUM(CASE WHEN BTC.BIZTRANSACTIONCLASSID = -1399999991 AND D.DETAILTYPE = 1 THEN D.VOUCHERAMOUNT ELSE 0 END) AS ProvisionalCreditAmount
                FROM TVOUCHERDETAIL D
                INNER JOIN TVOUCHER V ON D.VOUCHERID = V.VOUCHERID
                INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
                INNER JOIN MBIZTRANSACTIONCLASS BTC ON BTT.BIZTRANSACTIONCLASSID = BTC.BIZTRANSACTIONCLASSID
                WHERE V.ISACCOUNTPOST = 0
                  AND V.OUID = @OUId
                  AND (@PeriodId = -1 OR V.PERIODID = @PeriodId)
                  AND (@AccountId = -1 OR D.VOUCHERACCOUNTID = @AccountId)
                GROUP BY D.VOUCHERACCOUNTID, D.SUBACCOUNTID, V.PERIODID
            ) final
            WHERE TACCOUNTPOST.ACCOUNTID = final.AccountId
              AND TACCOUNTPOST.SUBACCOUNTID = final.SubAccountId
              AND TACCOUNTPOST.OUID = @OUId
              AND TACCOUNTPOST.PERIODID = final.PeriodId;";

        // ── Step 3a: running-balance Opening/Closing across ALL periods (PeriodId = -1) — each
        //            period's Opening is the cumulative Debit-Credit of every strictly earlier
        //            period (by MPERIOD.FROMDATE) for the same Account/SubAccount ─────────────

        public const string UPDATE_ACCOUNT_POST_OPENING_ALL_PERIODS = @"
            UPDATE c
            SET OPENING = ISNULL(agg.OpeningAmount, 0),
                CLOSING = ISNULL(agg.OpeningAmount, 0) + c.DEBIT - c.CREDIT,
                PROVISIONALOPENING = ISNULL(agg.ProvisionalOpeningAmount, 0),
                PROVISIONALCLOSING = ISNULL(agg.ProvisionalOpeningAmount, 0) + c.PROVISIONALDEBIT - c.PROVISIONALCREDIT
            FROM TACCOUNTPOST c
            INNER JOIN MPERIOD PC ON PC.PERIODID = c.PERIODID
            OUTER APPLY (
                SELECT SUM(a.DEBIT - a.CREDIT) AS OpeningAmount,
                       SUM(a.PROVISIONALDEBIT - a.PROVISIONALCREDIT) AS ProvisionalOpeningAmount
                FROM TACCOUNTPOST a
                INNER JOIN MPERIOD PA ON PA.PERIODID = a.PERIODID
                WHERE a.OUID = c.OUID AND a.ACCOUNTID = c.ACCOUNTID AND a.SUBACCOUNTID = c.SUBACCOUNTID
                  AND PA.FROMDATE < PC.FROMDATE
            ) agg
            WHERE c.OUID = @OUId
              AND (@AccountId = -1 OR c.ACCOUNTID = @AccountId);";

        // ── Step 3b: prior-period Closing carry-forward for a SPECIFIC period — balance-sheet
        //            nature accounts only (ACCOUNTSCHEDULENATURE IN (0,1)); other (P&L) accounts
        //            keep the Opening=0 already set by the rebuild insert, and their Closing is
        //            already correct from Step 2 ──────────────────────────────────────────────

        public const string UPDATE_ACCOUNT_POST_OPENING_SINGLE_PERIOD = @"
            UPDATE c
            SET OPENING = ISNULL(prev.PrevClosing, 0),
                CLOSING = ISNULL(prev.PrevClosing, 0) + c.DEBIT - c.CREDIT,
                PROVISIONALOPENING = ISNULL(prev.PrevProvisionalClosing, 0),
                PROVISIONALCLOSING = ISNULL(prev.PrevProvisionalClosing, 0) + c.PROVISIONALDEBIT - c.PROVISIONALCREDIT
            FROM TACCOUNTPOST c
            INNER JOIN MACCOUNT MA ON MA.ACCOUNTID = c.ACCOUNTID
            INNER JOIN MPERIOD PC ON PC.PERIODID = c.PERIODID
            OUTER APPLY (
                SELECT TOP 1 a.CLOSING AS PrevClosing, a.PROVISIONALCLOSING AS PrevProvisionalClosing
                FROM TACCOUNTPOST a
                INNER JOIN MPERIOD PA ON PA.PERIODID = a.PERIODID
                WHERE a.OUID = c.OUID AND a.ACCOUNTID = c.ACCOUNTID AND a.SUBACCOUNTID = c.SUBACCOUNTID
                  AND PA.FROMDATE < PC.FROMDATE
                ORDER BY PA.FROMDATE DESC
            ) prev
            WHERE c.OUID = @OUId
              AND c.PERIODID = @PeriodId
              AND (@AccountId = -1 OR c.ACCOUNTID = @AccountId)
              AND MA.ACCOUNTSCHEDULENATURE IN (0, 1);";
    }
}