namespace AnalyticsDAL.Query.KPIEvaluation
{
    public static class KPIEvaluationQB
    {
        // Same OU-access UNION fragment already proven in RatioAnalysisReportQB.GET_ACCESSIBLE_OU_IDS
        // (direct grants + group-expanded grants) — duplicated here rather than shared across
        // modules, matching this codebase's own "no shared base between report families" convention.
        public const string GET_ACCESSIBLE_OU_IDS = @"
SELECT UAR.OUID AS OUID FROM MUSERACCESSRIGHTS UAR WHERE UAR.USERID = @UserId AND UAR.OUID <> -1
UNION
SELECT OGD.OUID AS OUID FROM MUSERACCESSRIGHTS UAR2
INNER JOIN MORGANIZATIONGROUPDETAIL OGD ON OGD.ORGANIZATIONGROUPID = UAR2.OUGROUPID
WHERE UAR2.USERID = @UserId AND UAR2.OUGROUPID <> -1";

        // MGBPERIODDETAIL is the same sub-period table already used by TrialBalanceReportQB's own
        // Periodic mode and TKPIMANUAL/MKPILISTDETAIL's own PERIODID link — TKPIVALUE is keyed by
        // this same PERIODID, not a raw date, so KPI values are posted per sub-period (e.g. one
        // FN-Monthly bucket), not per calendar day like FFINANCE itself.
        public const string GET_PERIOD_RANGE = @"
SELECT SUBPERIODFROMDATE AS PeriodFromDate, SUBPERIODTODATE AS PeriodToDate
FROM   MGBPERIODDETAIL
WHERE  GBPERIODDETAILID = @PeriodDetailId";

        // Same DimDate lookup already proven by WarehouseFactPostingQB.GET_DATE_KEY / Ratio
        // Analysis's own ResolveDateKeyAsync — duplicated rather than cross-referenced from
        // AccountsDAL, avoiding a new project reference for one single-line query.
        public const string GET_DATE_KEY = @"
SELECT DateKey FROM DimDate WHERE CAST([Date] AS DATE) = CAST(@TargetDate AS DATE)";

        // A SemiAdditive/NonAdditive measure (a balance, not a flow) is only posted to FFINANCE on
        // the days its own posting job actually ran -- reading it "as of period end" means the most
        // recently posted value ON OR BEFORE that date, never an exact-date match (which would
        // silently resolve to 0 whenever the period's own last calendar day has no posted row, as
        // is normal for FFINANCE's own real posting cadence). This is a small, direct lookup
        // against FFINANCE itself (not the BI engine) purely to resolve WHICH date to filter on --
        // the actual operand values are still always fetched through IAnalysisAggregationService.
        public const string GET_LATEST_DATE_KEY_ON_OR_BEFORE = @"
SELECT MAX(DATEID) FROM FFINANCE WHERE OUID = @OUId AND DATEID <= @ToDateKey";

        // The prior posted period's own MGBPERIODDETAIL row within the same MGBPERIOD sequence --
        // used to resolve Trend (this run's Actual vs. the immediately preceding sub-period's
        // already-posted Actual for the same KPI/OU), mirroring legacy's own
        // UPDATE_TREND_AFTER_INSERT_NEW comparison.
        public const string GET_PREVIOUS_PERIOD_DETAIL_ID = @"
SELECT TOP 1 PD2.GBPERIODDETAILID
FROM   MGBPERIODDETAIL PD1
INNER JOIN MGBPERIODDETAIL PD2 ON PD2.GBPERIODID = PD1.GBPERIODID
WHERE  PD1.GBPERIODDETAILID = @PeriodDetailId
AND    PD2.SUBPERIODFROMDATE < PD1.SUBPERIODFROMDATE
ORDER BY PD2.SUBPERIODFROMDATE DESC";

        // Portlet Integration (Phase 5) — a dashboard KPI card resolves "current period" dynamically
        // rather than baking a static PeriodDetailId into the portlet config, so it always reflects
        // whatever was most recently posted for that OU, not a frozen snapshot from configure-time.
        public const string GET_LATEST_POSTED_PERIOD_DETAIL_ID = @"
SELECT TOP 1 tv.PERIODID
FROM   TKPIVALUE tv
INNER JOIN MGBPERIODDETAIL pd ON pd.GBPERIODDETAILID = tv.PERIODID
WHERE  tv.MEMBEROUID = @OUId
ORDER BY pd.SUBPERIODFROMDATE DESC";

        public const string GET_PREVIOUS_ACTUAL = @"
SELECT ACTUAL FROM TKPIVALUE WHERE KPIID = @KPIId AND PERIODID = @PeriodDetailId AND MEMBEROUID = @OUId";

        // Same additivity lookup already proven by RatioAnalysisReportQB.GET_MEASURE_ADDITIVITY --
        // hardcoded to FACTID=2 (FFINANCE) matching that same precedent, since this MVP pass is
        // FFINANCE-scoped only (the same 19 real KPI rows Ratio Analysis's own formulas cover).
        public const string GET_MEASURE_ADDITIVITY = @"
SELECT MEASURECODE AS MeasureCode, ADDITIVITYTYPE AS AdditivityType
FROM   MWAREHOUSEMEASURE
WHERE  FACTID = 2 AND STATUS <> 2";

        // ACTUALTYPE 0 (direct FFINANCE.<measure> expression) and 3 (composite, bracket-referencing
        // other KPIs' own names) are the only two types this engine computes. ACTUALTYPE 2 (manual)
        // has no ACTUALVALUEEXPRESSION to evaluate at all -- its value already lives in TKPIMANUAL,
        // read directly at GetKPIValues time (see GET_KPI_VALUES below), never posted into
        // TKPIVALUE by this engine. ACTUALTYPE 1/4 (Calculated/Derived) are declared on the live
        // MKPI table but have zero real rows and no defined semantics anywhere (confirmed against
        // both gb5 and the GB4 legacy engine) -- excluded, not silently mis-evaluated.
        public const string GET_COMPUTABLE_KPIS = @"
SELECT KPIID, KPINAME, SECTION, ACTUALTYPE, ACTUALVALUEEXPRESSION,
       ANALYSISID, ANALYSISLINKTYPE, TARGETTYPE, TARGETVALUEEXPRESSION, DIMOUID AS DimOuId
FROM   MKPI
WHERE  STATUS <> 2
AND    ACTUALTYPE IN (0, 3)
AND    KPIID IN @KpiIds";

        public const string GET_ALL_COMPUTABLE_KPI_IDS = @"
SELECT KPIID
FROM   MKPI
WHERE  STATUS <> 2
AND    ACTUALTYPE IN (0, 3)";

        public const string GET_STATUS_BANDS = @"
SELECT KPISTATUSID, KPIID, VALUEFROM, VALUETO, SCORE, COLOR
FROM   MKPISTATUS
WHERE  STATUS <> 2
AND    KPIID IN @KpiIds
ORDER BY KPIID, SLNO";

        // Scoped delete before insert, mirroring WarehouseFactPostingDAL's own
        // DELETE-then-INSERT-per-OU-and-period discipline -- never a blanket truncate. Scoped by
        // PeriodId + OU only (not by which specific KpiIds this run happened to touch), since a
        // full repost for that OU/period should replace every previously-posted KPI value for it.
        public const string DELETE_EXISTING_VALUES = @"
DELETE FROM TKPIVALUE
WHERE  PERIODID   = @PeriodDetailId
AND    MEMBEROUID = @OUId";

        // MEMBER1-5ID have no DB-side default and are NOT NULL — set to -1 (the same "dimension
        // slot unused" sentinel MKPI itself uses for these 19 rows' own DIM1-5ID) since this MVP
        // pass only slices by the single OU dimension (MEMBEROUID).
        public const string INSERT_VALUE = @"
INSERT INTO TKPIVALUE
    (KPIID, SCENARIOID, PERIODID, MEMBEROUID, MEMBER1ID, MEMBER2ID, MEMBER3ID, MEMBER4ID, MEMBER5ID,
     DIMOUID, ACTUAL, TARGET, TREND, STATUSID)
VALUES
    (@KPIId, -1, @PeriodDetailId, @OUId, -1, -1, -1, -1, -1,
     @DimOuId, @Actual, @Target, @Trend, @StatusId)";

        // The read path for GetKPIValues -- joins TKPIVALUE back to MKPI (for display) and
        // MKPISTATUS (for the already-posted STATUSID's Score/Color band), and LEFT JOINs
        // TKPIMANUAL for ACTUALTYPE=2 KPIs (which never get a TKPIVALUE row at all -- their value
        // lives only in the manual-entry table, matched by KPIID+PERIODID+MEMBEROUID, same
        // dimension-tuple match legacy's own engine used).
        public const string GET_KPI_VALUES = @"
SELECT  k.KPIID, k.KPINAME, k.SECTION,
        tv.ACTUAL     AS ActualValue,
        tv.TARGET     AS TargetValue,
        tv.TREND      AS Trend,
        s.SCORE       AS Score,
        s.COLOR       AS Color,
        tm.ACTUAL     AS ManualActualValue,
        tm.TARGET     AS ManualTargetValue
FROM    MKPI k
LEFT JOIN TKPIVALUE tv  ON tv.KPIID = k.KPIID AND tv.PERIODID = @PeriodDetailId AND tv.MEMBEROUID = @OUId
LEFT JOIN MKPISTATUS s  ON s.KPISTATUSID = tv.STATUSID
LEFT JOIN TKPIMANUAL tm ON tm.KPIID = k.KPIID AND tm.PERIODID = @PeriodDetailId AND tm.MEMBEROUID = @OUId AND tm.STATUS <> 2
WHERE   k.STATUS <> 2
AND     k.KPIID IN @KpiIds
ORDER BY k.SECTION, k.KPINAME";
    }
}