namespace TMSDAL.Query.KpiPosting
{
    // Every KPI here is computed directly against TMS's own tables -- this bypasses
    // AnalyticsBLL.KPIEvaluationBLL entirely (that engine only evaluates FFINANCE.<measure>
    // expressions via MKPI.ACTUALVALUEEXPRESSION, confirmed hardcoded to BICatalogId=2). TMS's 11
    // KPI rows are seeded with ACTUALTYPE=1 (Calculated) specifically so KPIEvaluationBLL's own
    // GetComputableKpis (ACTUALTYPE IN (0,3) only) never picks them up and tries to evaluate a
    // FFINANCE-shaped expression against them.
    public static class TmsKpiPostingQB
    {
        // Current MONTHLY (PERIODTYPE=2) period covering "today" -- TMS has no per-OU/per-calendar
        // period concept of its own, so this always resolves against whichever MONTHLY period row
        // covers the current date in the tenant's own database (MGBPERIOD/MGBPERIODDETAIL have no
        // TENANTID/CALENDARID column at all -- confirmed live -- so no further scoping is possible
        // or needed; each tenant's own database already provides the isolation).
        public const string RESOLVE_CURRENT_MONTHLY_PERIOD = @"
SELECT TOP 1 PD.GBPERIODDETAILID AS PeriodDetailId, PD.SUBPERIODFROMDATE AS FromDate, PD.SUBPERIODTODATE AS ToDate
FROM   MGBPERIOD P
INNER JOIN MGBPERIODDETAIL PD ON PD.GBPERIODID = P.GBPERIODID
WHERE  P.PERIODTYPE = 2
AND    CAST(GETUTCDATE() AS DATE) BETWEEN PD.SUBPERIODFROMDATE AND PD.SUBPERIODTODATE
ORDER BY PD.GBPERIODDETAILID DESC";

        public const string GET_PREVIOUS_MONTHLY_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";

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

        // NOTE: deliberately does NOT filter "AND STATUS <> 2" the way
        // AnalyticsDAL.KPIEvaluationQB.GET_STATUS_BANDS does. Live data confirms MKPISTATUS.STATUS
        // is itself the RAG severity value (1/2/3 = Red/Amber/Green, verified against all 19
        // existing Finance KPI rows' own bands) -- STATUS<>2 there silently drops the Amber tier
        // for every KPI. Not this module's bug to fix, just not one to copy.
        public const string GET_STATUS_BANDS = @"
SELECT KPISTATUSID, VALUEFROM, VALUETO, SCORE
FROM   MKPISTATUS
WHERE  KPIID = @KPIId
ORDER BY SLNO";

        // Scoped by (PeriodId, OUId, KPIID IN the 11 TMS KPI ids) -- deliberately narrower than
        // AnalyticsDAL.KPIEvaluationDAL.DELETE_EXISTING_VALUES (which deletes every KPI row for a
        // (PeriodId, OUId) pair with no KpiId filter at all). Scoping by KpiId here means a TMS
        // posting run can never wipe another module's KPI rows even if they happen to share the
        // same PeriodId/OUId -- and vice versa, this job's own rows are the only ones ever deleted.
        public const string DELETE_EXISTING_VALUES = @"
DELETE FROM TKPIVALUE
WHERE  PERIODID   = @PeriodDetailId
AND    MEMBEROUID = @OUId
AND    KPIID IN @KpiIds";

        // MEMBER1-5ID/DIM1-5ID have no DB-side default and are NOT NULL -- set to -1 (the
        // "dimension slot unused" sentinel already used by every real MKPI/TKPIVALUE row in this
        // catalog). DIMOUID is likewise -1 (MKPI.DIMOUID's own default for all 19 existing Finance
        // KPI rows -- confirmed live via FK_MKPI_DIMOUID's own -1 sentinel row in MDIMENSION).
        public const string INSERT_VALUE = @"
INSERT INTO TKPIVALUE
    (KPIID, SCENARIOID, PERIODID, MEMBEROUID, MEMBER1ID, MEMBER2ID, MEMBER3ID, MEMBER4ID, MEMBER5ID,
     DIMOUID, DIM1ID, DIM2ID, DIM3ID, DIM4ID, DIM5ID, ACTUAL, TARGET, TREND, STATUSID)
VALUES
    (@KPIId, -1, @PeriodDetailId, @OUId, -1, -1, -1, -1, -1,
     -1, -1, -1, -1, -1, -1, @Actual, NULL, @Trend, @StatusId)";

        // ── KPI 1: Compliance Coverage % -- mandatory TTRAININGNEED's own TARGETHEADCOUNT vs
        // actual completed headcount (via TTRAININGINSTANCE.TRAININGNEEDID -> TTRAININGENROLLMENT
        // -> TTRAININGCOMPLETION, COMPLETIONSTATUS=0 Completed). CROSS APPLY avoids the JOIN
        // fan-out that would otherwise multiply-count TN.TARGETHEADCOUNT once per enrollment row.
        public const string GET_COMPLIANCE_COVERAGE_PCT = @"
SELECT CAST(
    SUM(Completed.CompletedCount) * 100.0 / NULLIF(SUM(TN.TARGETHEADCOUNT), 0)
AS DECIMAL(9,4))
FROM DBO.TTRAININGNEED TN
CROSS APPLY (
    SELECT COUNT(1) AS CompletedCount
    FROM   DBO.TTRAININGINSTANCE TI
    INNER JOIN DBO.TTRAININGENROLLMENT TE ON TE.INSTANCEID = TI.INSTANCEID
    INNER JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.COMPLETIONSTATUS = 0
    WHERE  TI.TRAININGNEEDID = TN.TRAININGNEEDID
) AS Completed
WHERE TN.TENANTID = @TenantId
AND   TN.ISMANDATORY = 1
AND   TN.RAISEDON <= @ToDate";

        // ── KPI 2: Nomination Approval TAT (days) -- avg NOMINATEDON -> HRAPPROVEDON, in fractional
        // days (DATEDIFF(HOUR,...)/24.0, never DATEDIFF(DAY,...) which truncates and biases low).
        public const string GET_NOMINATION_APPROVAL_TAT_DAYS = @"
SELECT CAST(AVG(CAST(DATEDIFF(HOUR, TE.NOMINATEDON, TE.HRAPPROVEDON) AS DECIMAL(18,4)) / 24.0) AS DECIMAL(9,4))
FROM   DBO.TTRAININGENROLLMENT TE
WHERE  TE.TENANTID = @TenantId
AND    TE.HRAPPROVEDON IS NOT NULL
AND    TE.HRAPPROVEDON >= @FromDate AND TE.HRAPPROVEDON <= @ToDate";

        // ── KPI 3: Cost per Trained Employee -- TTRAININGINSTANCE.COSTPERPARTICIPANT summed over
        // every completed enrollment in the period, divided by headcount completed.
        public const string GET_COST_PER_TRAINED_EMPLOYEE = @"
SELECT CAST(SUM(TI.COSTPERPARTICIPANT) / NULLIF(COUNT(1), 0) AS DECIMAL(18,4))
FROM   DBO.TTRAININGCOMPLETION TC
INNER JOIN DBO.TTRAININGENROLLMENT TE ON TE.ENROLLMENTID = TC.ENROLLMENTID
INNER JOIN DBO.TTRAININGINSTANCE TI ON TI.INSTANCEID = TE.INSTANCEID
WHERE  TC.TENANTID = @TenantId
AND    TC.COMPLETIONSTATUS = 0
AND    TC.COMPLETEDON >= @FromDate AND TC.COMPLETEDON <= @ToDate";

        // ── KPI 4: Venue Utilization % -- avg(ENROLLEDCOUNT/MAXCAPACITY) for venue-backed instances
        // planned to start within the period.
        public const string GET_VENUE_UTILIZATION_PCT = @"
SELECT CAST(AVG(CASE WHEN TI.MAXCAPACITY > 0 THEN TI.ENROLLEDCOUNT * 100.0 / TI.MAXCAPACITY END) AS DECIMAL(9,4))
FROM   DBO.TTRAININGINSTANCE TI
WHERE  TI.TENANTID = @TenantId
AND    TI.VENUEID IS NOT NULL
AND    TI.PLANNEDSTARTDATE >= @FromDate AND TI.PLANNEDSTARTDATE <= @ToDate";

        // ── KPI 5/6: Pass Rate % (overall / first-attempt-only via @AttemptNoFilter -- passed as a
        // literal SLNO=1 predicate built in the DAL, never string-concatenated from caller input).
        public const string GET_PASS_RATE_PCT = @"
SELECT CAST(
    SUM(CASE WHEN TA.ATTEMPTISPASSED = 1 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(1), 0)
AS DECIMAL(9,4))
FROM   DBO.TASSESSMENTATTEMPT TA
WHERE  TA.TENANTID = @TenantId
AND    TA.COMPLETEDON IS NOT NULL
AND    TA.COMPLETEDON >= @FromDate AND TA.COMPLETEDON <= @ToDate";

        public const string GET_FIRST_ATTEMPT_PASS_RATE_PCT = @"
SELECT CAST(
    SUM(CASE WHEN TA.ATTEMPTISPASSED = 1 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(1), 0)
AS DECIMAL(9,4))
FROM   DBO.TASSESSMENTATTEMPT TA
WHERE  TA.TENANTID = @TenantId
AND    TA.ATTEMPTNO = 1
AND    TA.COMPLETEDON IS NOT NULL
AND    TA.COMPLETEDON >= @FromDate AND TA.COMPLETEDON <= @ToDate";

        // ── KPI 7: Skill Upgrade Approval Rate % -- Approved(2) / (Approved(2)+Rejected(3)) among
        // upgrades reviewed within the period.
        public const string GET_SKILL_UPGRADE_APPROVAL_RATE_PCT = @"
SELECT CAST(
    SUM(CASE WHEN TU.UPGRADESTATUS = 2 THEN 1 ELSE 0 END) * 100.0
    / NULLIF(SUM(CASE WHEN TU.UPGRADESTATUS IN (2,3) THEN 1 ELSE 0 END), 0)
AS DECIMAL(9,4))
FROM   DBO.TTXSKILLUPGRADE TU
WHERE  TU.TENANTID = @TenantId
AND    TU.REVIEWEDON IS NOT NULL
AND    TU.REVIEWEDON >= @FromDate AND TU.REVIEWEDON <= @ToDate";

        // ── KPI 8: Certificate Renewal Compliance % -- of certificates whose EXPIRYDATE falls on
        // or before period end, % that are Active/RenewalInProgress (0,3) rather than lapsed (1).
        public const string GET_CERT_RENEWAL_COMPLIANCE_PCT = @"
SELECT CAST(
    SUM(CASE WHEN TC.CERTSTATUS IN (0,3) THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(1), 0)
AS DECIMAL(9,4))
FROM   DBO.TTRAININGCERTIFICATE TC
WHERE  TC.TENANTID = @TenantId
AND    TC.EXPIRYDATE IS NOT NULL
AND    TC.EXPIRYDATE <= @ToDate";

        // ── KPI 9: Expiring Certificates within 30 days -- a forward-looking snapshot as of "now",
        // deliberately not period-bounded (unlike the other 10 KPIs).
        public const string GET_EXPIRING_CERTIFICATES_30D = @"
SELECT COUNT(1)
FROM   DBO.TTRAININGCERTIFICATE TC
WHERE  TC.TENANTID = @TenantId
AND    TC.CERTSTATUS = 0
AND    TC.EXPIRYDATE IS NOT NULL
AND    TC.EXPIRYDATE >= CAST(GETUTCDATE() AS DATE)
AND    TC.EXPIRYDATE <= DATEADD(DAY, 30, GETUTCDATE())";

        // ── KPI 10: Feedback Response Rate % -- TEVALUATIONINSIGHT.RESPONSERATE is already
        // pre-aggregated per instance evaluation plan; this just averages it across the period.
        public const string GET_FEEDBACK_RESPONSE_RATE_PCT = @"
SELECT CAST(AVG(EI.RESPONSERATE) AS DECIMAL(9,4))
FROM   DBO.TEVALUATIONINSIGHT EI
WHERE  EI.TENANTID = @TenantId
AND    EI.GENERATEDON >= @FromDate AND EI.GENERATEDON <= @ToDate";

        // ── KPI 11: Learning Gain % -- TTRAININGCOMPLETION.SCOREIMPROVEMENT (Kirkpatrick L2),
        // averaged across completions in the period.
        public const string GET_LEARNING_GAIN_PCT = @"
SELECT CAST(AVG(TC.SCOREIMPROVEMENT) AS DECIMAL(9,4))
FROM   DBO.TTRAININGCOMPLETION TC
WHERE  TC.TENANTID = @TenantId
AND    TC.COMPLETIONSTATUS = 0
AND    TC.COMPLETEDON >= @FromDate AND TC.COMPLETEDON <= @ToDate";
    }
}