namespace AccountsDAL.Query.Warehouse
{
    // Generic warehouse fact-posting engine — reads declarative rules from
    // MWAREHOUSEMEASUREPOSTINGRULE (see that table's migration header,
    // 20260805_WarehouseMeasurePostingRule_Schema_*.sql, for the full design rationale) and reuses
    // the exact nature-filtered SUM(debit-credit) expression shape already proven live in
    // ProfitLossReportQB/BalanceSheetReportQB/CashFlowReportQB this session — just parameterized by
    // rule data instead of hardcoded per report. Originally built for FFINANCE (FACTID=2), then
    // generalized to any fact table sharing its family (OU+Day grain, TVOUCHER/MACCOUNT/
    // MACCOUNTGROUP source, nature-bucket aggregation) — see the plan file's "Extension —
    // Generalizing to Admin-Defined Fact Tables" section. A fact needing a different grain or
    // source table is a new family, out of this engine's scope by design.
    //
    // Batching: rather than one round-trip per measure (20 NatureBucket measures in the MVP set),
    // WarehouseFactPostingDAL groups rules by their (NatureColumnType, AggregationMode,
    // AccountTypeFilter) key and this file's BUILD_FLAT_BATCH/BUILD_SIGNSPLIT_BATCH templates
    // produce ONE multi-column query per group — a real, structural performance improvement over
    // legacy's design that a per-client opaque-SQL-blob approach could never apply consistently.
    public static class WarehouseFactPostingQB
    {
        // Resolves the physical fact table name for a given FACTID — the one piece of
        // fact-specific identity this generic engine needs. Validated as a safe SQL identifier by
        // the DAL before ever being substituted into DELETE_EXISTING_ROW/INSERT_ROW's
        // {FactTableName} placeholder, same discipline as MeasureCode/NatureValues.
        public const string GET_FACT_TABLE_NAME = @"
SELECT FACTTABLENAME FROM MWAREHOUSEFACT WHERE FACTID = @FactId AND STATUS <> 2";

        public const string GET_POSTING_RULES = @"
SELECT
    r.POSTINGRULEID       AS PostingRuleId,
    r.MEASUREID           AS MeasureId,
    m.MEASURECODE         AS MeasureCode,
    m.COLUMNNAME          AS ColumnName,
    r.SOURCETYPE          AS SourceType,
    r.NATURECOLUMNTYPE    AS NatureColumnType,
    r.NATUREVALUES        AS NatureValues,
    r.ACCOUNTTYPEFILTER   AS AccountTypeFilter,
    r.AGGREGATIONMODE     AS AggregationMode,
    r.SIGNCONVENTION      AS SignConvention,
    r.FORMULAEXPRESSION   AS FormulaExpression,
    r.COMBINATIONMODE     AS CombinationMode,
    r.SORTORDER           AS SortOrder,
    r.SOURCETABLE         AS SourceTable,
    r.SOURCEDATECOLUMN    AS SourceDateColumn,
    r.SOURCEOUCOLUMN      AS SourceOuColumn,
    r.SOURCEAMTCOLUMN     AS SourceAmtColumn,
    r.FILTERJSON          AS FilterJson
FROM MWAREHOUSEMEASUREPOSTINGRULE r
INNER JOIN MWAREHOUSEMEASURE m ON r.MEASUREID = m.MEASUREID
WHERE m.FACTID = @FactId
  AND r.ISACTIVE = 1
  AND r.STATUS <> 2
  AND m.STATUS <> 2
ORDER BY m.MEASUREID, r.SORTORDER";

        // Updates the freshness tracking columns after a successful full posting run.
        // LASTPOSTEDDATEID stores the highest DimDate.DateKey (YYYYMMDD int) that was posted.
        public const string UPDATE_LAST_POSTED = @"
UPDATE MWAREHOUSEFACT
SET    LASTPOSTEDDATEID = @LastPostedDateId,
       LASTPOSTEDON     = GETUTCDATE()
WHERE  FACTID = @FactId";

        public const string BASE_AMOUNT_EXPR = "VD.VOUCHERAMOUNT"; // OU/base currency only for posting — no @CurrencyBasis parameterization needed for a fact table (unlike interactive reports)

        private const string OU_FILTER = "V.OUID = @OUID";

        private const string FROM_JOIN_GROUP = @"
FROM TVOUCHER V
INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
INNER JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID";

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

        // ── Multi-date batching — PeriodFlow only (AGGREGATIONMODE 0) ───────────────────
        // A PeriodFlow measure's value on day N depends ONLY on that day's own vouchers — each
        // day is fully independent, so all requested dates can be computed in ONE query via a plain
        // GROUP BY, instead of the DAL looping targetDates and issuing one round-trip per day.
        // {TargetDates} is a comma-joined list of quoted/parameterized date literals — never raw
        // request input; the DAL builds it from the already-validated targetDates list.
        //
        // AsOfBalance (mode 1) and the sign-split DebitOnly/CreditOnly modes (2/3) are deliberately
        // NOT batched here — a balance "as of day N" is a cumulative running total, and correctly
        // collapsing that into one query needs nested window functions over a carry-forward join
        // that's materially harder to get right for financial data than a straight GROUP BY. Those
        // two draw from BUILD_FLAT_BATCH_GROUP/SCHEDULE and BUILD_SIGNSPLIT_BATCH below exactly as
        // before (once per day, per OU) until that rewrite gets a dedicated verification pass.

        // PeriodFlow, ACCOUNTGROUPNATURE-keyed (BTT/AG family), ALL target dates in one query.
        public const string BUILD_FLAT_BATCH_GROUP_MULTIDATE = @"
SELECT V.VOUCHERDATE AS ActivityDate, {SelectColumns}" + FROM_JOIN_GROUP + @"
WHERE " + OU_FILTER + @"
  AND V.VOUCHERDATE IN ({TargetDates})
GROUP BY V.VOUCHERDATE";

        // PeriodFlow, ACCOUNTSCHEDULENATURE-keyed (no AG join needed), ALL target dates in one query.
        public const string BUILD_FLAT_BATCH_SCHEDULE_MULTIDATE = @"
SELECT V.VOUCHERDATE AS ActivityDate, {SelectColumns}" + FROM_JOIN_SCHEDULE + @"
WHERE " + OU_FILTER + @"
  AND V.VOUCHERDATE IN ({TargetDates})
GROUP BY V.VOUCHERDATE";

        // Flat batched query — single-date, unchanged from the original design. Still used for
        // AGGREGATIONMODE 1 (AsOfBalance, {DateFilter}='V.VOUCHERDATE <= @TargetDate') since that
        // mode is not yet multi-date-batched (see note above). {NatureColumn} is
        // 'AG.ACCOUNTGROUPNATURE' or 'ACC.ACCOUNTSCHEDULENATURE' (never raw user input — resolved
        // from the rule's own NatureColumnType by the DAL, one of exactly two allow-listed strings).
        public const string BUILD_FLAT_BATCH_GROUP = @"
SELECT {SelectColumns}" + FROM_JOIN_GROUP + @"
WHERE " + OU_FILTER + @"
  AND {DateFilter}";

        public const string BUILD_FLAT_BATCH_SCHEDULE = @"
SELECT {SelectColumns}" + FROM_JOIN_SCHEDULE + @"
WHERE " + OU_FILTER + @"
  AND {DateFilter}";

        // Sign-split batch — AGGREGATIONMODE 2 (DebitOnly) / 3 (CreditOnly), e.g. BANKDRBALANCE /
        // BANKCRBALANCE. Needs a per-account net FIRST (inner derived table, GROUP BY
        // VOUCHERACCOUNTID), then buckets by that net's sign in the outer query — the same two-step
        // shape Trial Balance/Balance Sheet already use for their own Debit/Credit column split,
        // just computing possibly-multiple sign-split measures from the SAME inner per-account
        // result in one pass when they share the same nature/account-type filter (e.g. BANKDR and
        // BANKCR both read the same "net per Bank account" inner result). Single-date, unchanged.
        public const string BUILD_SIGNSPLIT_BATCH = @"
SELECT {OuterSelectColumns}
FROM (
    SELECT VD.VOUCHERACCOUNTID,
           SUM(CASE WHEN VD.DETAILTYPE = 0 THEN " + BASE_AMOUNT_EXPR + @" ELSE -(" + BASE_AMOUNT_EXPR + @") END) AS NetAmount
    FROM TVOUCHER V
    INNER JOIN TVOUCHERDETAIL VD ON V.VOUCHERID = VD.VOUCHERID
    INNER JOIN MACCOUNT ACC ON VD.VOUCHERACCOUNTID = ACC.ACCOUNTID
    INNER JOIN MACCOUNTGROUP AG ON ACC.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
    INNER JOIN MBIZTRANSACTIONTYPE BTT ON V.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
    WHERE " + OU_FILTER + @"
      AND V.VOUCHERDATE <= @TargetDate
      AND AG.ACCOUNTGROUPNATURE IN ({NatureValues})
      {AccountTypeFilter}
    GROUP BY VD.VOUCHERACCOUNTID
) X";

        // ── Fact-table multi-row upsert ──────────────────────────────────────────────────
        // Scoped DELETE for every DATEID being reposted in this call (never a blanket table
        // truncate), then a single multi-row INSERT ... SELECT FROM a VALUES table — replaces the
        // original DELETE+INSERT-per-day pair, cutting N round-trips down to 2 (one DELETE, one
        // INSERT) for the whole date range, per OU. {FactTableName} comes from
        // MWAREHOUSEFACT.FACTTABLENAME, {DateIdList} is a comma-joined list of resolved
        // DimDate.DateKey ints — both validated as safe by the DAL before substitution, never raw
        // request input.
        public const string DELETE_EXISTING_ROWS = @"
DELETE FROM {FactTableName} WHERE OUID = @OUID AND DATEID IN ({DateIdList})";

        public const string INSERT_ROWS = @"
INSERT INTO {FactTableName} (OUID, DATEID, {ColumnList})
SELECT @OUID, V.DateId, {QualifiedValueColumnList}
FROM (VALUES {ValueRows}) AS V(DateId, {ValueColumnList})";

        // Resolves DimDate.DateKey (YYYYMMDD int PK, confirmed live) for a given calendar date —
        // FFINANCE.DATEID is a soft FK to this (no DDL-enforced constraint, confirmed live, matching
        // legacy's own convention).
        public const string GET_DATE_KEY = @"
SELECT DateKey FROM DimDate WHERE CAST([Date] AS DATE) = CAST(@TargetDate AS DATE)";

        // Batched form of GET_DATE_KEY — resolves every date in the posting range to its
        // DimDate.DateKey in ONE round-trip instead of one query per day. {TargetDates} is a
        // comma-joined list of quoted/parameterized date literals, built by the DAL from the
        // already-validated targetDates list — never raw request input.
        public const string GET_DATE_KEYS = @"
SELECT CAST([Date] AS DATE) AS TargetDate, DateKey
FROM DimDate
WHERE CAST([Date] AS DATE) IN ({TargetDates})";

        // Every active OU for this tenant DB — the default posting scope when the caller doesn't
        // supply an explicit OU list. Not user-access-filtered (unlike every interactive
        // AccountReports query) — this is a system/scheduled job posting for every real OU, not an
        // individual user's own accessible subset.
        public const string GET_ACTIVE_OU_IDS = @"
SELECT OUID FROM MORGANIZATIONUNIT WHERE STATUS = 1";

        // All active fact IDs — used by PostAll (batch) to iterate every registered fact table.
        // Unlike GET_DELTA_ENABLED_FACTS (POSTINGMODE=1 only), this returns every non-deleted fact
        // so the nightly batch reposts ALL facts regardless of whether delta posting is enabled.
        public const string GET_ALL_ACTIVE_FACT_IDS = @"
SELECT FACTID FROM MWAREHOUSEFACT WHERE STATUS <> 2";
    }
}