namespace QMSDAL.Query.ChecklistAnalytics
{
    // ─────────────────────────────────────────────────────────────────────────
    //  ChecklistAnalyticsQB  – SQL constants for analytics / dashboard queries
    //
    //  Rules:
    //    • All column names UPPERCASE
    //    • All parameters use @ParameterName (Dapper named binding)
    //    • TENANTID filter always included — IQueryExecutor auto-populates @TenantId
    //    • Analytics data is volatile — NO caching (CacheKeyLevel.NOT_REQUIRED)
    //    • GETUTCDATE() for "today" — consistent with server timezone
    //
    //  Indexes required (existing):
    //    TCHECKLISTLOG: IX_TCHKLOG_TENANTID_RESULT_DATE (TENANTID, OVERALLRESULT, LOGDATE, CHECKLISTID)
    //    TCHECKLISTLOGDETAIL: IX_TCHKLOGDETAIL_LOGID (CHECKLISTLOGID)
    // ─────────────────────────────────────────────────────────────────────────
    public static class ChecklistAnalyticsQB
    {
        // ── KPI summary (single row) ──────────────────────────────────────────
        // TotalActive:     active checklists
        // ExecutionsToday: log entries created today (UTC)
        // PendingToday:    OVERALLRESULT=0 (Pending) created today
        // FailuresLast7:   OVERALLRESULT=2 in last 7 days
        // BlockingFails:   OVERALLRESULT=2 where checklist ISBLOCKING=1, last 7 days
        // ComplianceRate:  Pass / (Pass+Fail) * 100, last 30 days; 0 if no executions

        public const string GET_KPI = @"
SELECT
    (SELECT COUNT(*)
     FROM   MCHECKLIST
     WHERE  TENANTID = @TenantId AND STATUS = 1) AS TotalActive,

    (SELECT COUNT(*)
     FROM   TCHECKLISTLOG
     WHERE  TENANTID = @TenantId
     AND    CAST(CREATEDON AS DATE) = CAST(GETUTCDATE() AS DATE)) AS ExecutionsToday,

    (SELECT COUNT(*)
     FROM   TCHECKLISTLOG
     WHERE  TENANTID      = @TenantId
     AND    OVERALLRESULT  = 0
     AND    CAST(CREATEDON AS DATE) = CAST(GETUTCDATE() AS DATE)) AS PendingToday,

    (SELECT COUNT(*)
     FROM   TCHECKLISTLOG
     WHERE  TENANTID     = @TenantId
     AND    OVERALLRESULT = 2
     AND    LOGDATE      >= DATEADD(DAY, -7, GETUTCDATE())) AS FailuresLast7,

    (SELECT COUNT(*)
     FROM   TCHECKLISTLOG   L
     JOIN   MCHECKLIST      CL ON CL.CHECKLISTID = L.CHECKLISTID
     WHERE  L.TENANTID      = @TenantId
     AND    L.OVERALLRESULT  = 2
     AND    CL.ISBLOCKING    = 1
     AND    L.LOGDATE       >= DATEADD(DAY, -7, GETUTCDATE())) AS BlockingFails,

    CASE
        WHEN (SELECT COUNT(*) FROM TCHECKLISTLOG
              WHERE TENANTID = @TenantId
              AND   OVERALLRESULT IN (1,2)
              AND   LOGDATE >= DATEADD(DAY, -30, GETUTCDATE())) = 0
        THEN CAST(0 AS DECIMAL(5,2))
        ELSE CAST(
            100.0 *
            (SELECT COUNT(*) FROM TCHECKLISTLOG
             WHERE TENANTID = @TenantId AND OVERALLRESULT = 1
             AND   LOGDATE >= DATEADD(DAY, -30, GETUTCDATE()))
            /
            (SELECT COUNT(*) FROM TCHECKLISTLOG
             WHERE TENANTID = @TenantId AND OVERALLRESULT IN (1,2)
             AND   LOGDATE >= DATEADD(DAY, -30, GETUTCDATE()))
        AS DECIMAL(5,2))
    END AS ComplianceRate;";

        // ── Daily trend for last N days ───────────────────────────────────────
        // @Days: number of days to look back (e.g. 7, 14, 30)
        // Returns one row per day with Pass (1), Fail (2), Total counts.
        // Uses a date series via MAUTONUMBER hack — replaced with recursive CTE
        // for portability. Days with no executions return 0 counts.

        public const string GET_TREND = @"
WITH DateSeries AS
(
    SELECT CAST(DATEADD(DAY, -(n-1), CAST(GETUTCDATE() AS DATE)) AS DATE) AS DayDate
    FROM
    (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM MCHECKLIST   -- use any table with enough rows
    ) nums
    WHERE n <= @Days
),
Logs AS
(
    SELECT
        CAST(LOGDATE AS DATE)  AS LogDay,
        OVERALLRESULT
    FROM   TCHECKLISTLOG
    WHERE  TENANTID  = @TenantId
    AND    LOGDATE  >= DATEADD(DAY, -@Days, GETUTCDATE())
)
SELECT
    CONVERT(VARCHAR(10), DS.DayDate, 120) AS DayLabel,
    ISNULL(SUM(CASE WHEN L.OVERALLRESULT = 1 THEN 1 ELSE 0 END), 0) AS Pass,
    ISNULL(SUM(CASE WHEN L.OVERALLRESULT = 2 THEN 1 ELSE 0 END), 0) AS Fail,
    ISNULL(COUNT(L.OVERALLRESULT), 0)                                AS Total
FROM   DateSeries DS
LEFT JOIN Logs L ON L.LogDay = DS.DayDate
GROUP BY DS.DayDate
ORDER BY DS.DayDate;";

        // ── Timing phase breakdown ────────────────────────────────────────────
        // Returns execution counts grouped by TIMING (1=Pre,2=During,3=Post,4=Inspection).

        public const string GET_TIMING_BREAKDOWN = @"
SELECT
    CL.TIMING                           AS Timing,
    CASE CL.TIMING
        WHEN 1 THEN 'Pre-Operation'
        WHEN 2 THEN 'During Operation'
        WHEN 3 THEN 'Post-Operation'
        WHEN 4 THEN 'Inspection'
        ELSE        'Unknown'
    END                                 AS TimingLabel,
    COUNT(*)                            AS Count
FROM  TCHECKLISTLOG L
JOIN  MCHECKLIST    CL ON CL.CHECKLISTID = L.CHECKLISTID
WHERE L.TENANTID  = @TenantId
AND   L.LOGDATE  >= DATEADD(DAY, -30, GETUTCDATE())
GROUP BY CL.TIMING
ORDER BY CL.TIMING;";

        // ── Top failing checklist items ───────────────────────────────────────
        // @Days: lookback window (e.g. 7, 30)
        // Returns up to 10 items with highest RESULT=2 count within the window.
        // PctOfExecutions = FailCount / total log entries in window * 100.

        public const string GET_TOP_FAILURES = @"
WITH TotalExec AS
(
    SELECT COUNT(*) AS TotalCount
    FROM   TCHECKLISTLOG
    WHERE  TENANTID = @TenantId
    AND    LOGDATE >= DATEADD(DAY, -@Days, GETUTCDATE())
)
SELECT TOP 10
    LD.CHECKLISTDETAILID AS ChecklistDetailId,
    CL.CHECKLISTNAME     AS ChecklistName,
    D.DETAILTEXT         AS DetailText,
    D.ACTIONTYPE         AS ActionType,
    D.FAILACTION         AS FailAction,
    COUNT(*)             AS FailCount,
    CASE
        WHEN TE.TotalCount = 0 THEN CAST(0 AS DECIMAL(5,2))
        ELSE CAST(100.0 * COUNT(*) / TE.TotalCount AS DECIMAL(5,2))
    END                  AS PctOfExecutions
FROM   TCHECKLISTLOGDETAIL LD
JOIN   TCHECKLISTLOG       L  ON L.CHECKLISTLOGID  = LD.CHECKLISTLOGID
JOIN   MCHECKLISTDETAIL    D  ON D.CHECKLISTDETAILID = LD.CHECKLISTDETAILID
JOIN   MCHECKLIST          CL ON CL.CHECKLISTID    = L.CHECKLISTID
CROSS JOIN TotalExec TE
WHERE  LD.TENANTID  = @TenantId
AND    LD.RESULT    = 2
AND    L.LOGDATE   >= DATEADD(DAY, -@Days, GETUTCDATE())
GROUP BY LD.CHECKLISTDETAILID, CL.CHECKLISTNAME, D.DETAILTEXT,
         D.ACTIONTYPE, D.FAILACTION, TE.TotalCount
ORDER BY FailCount DESC;";
    }
}