namespace TCMSDAL.QueryBuilders;

/// <summary>
/// SQL constants for coverage reporting.
/// Sources: MTCMSTESTCASE × TTCMSRUNRESULT → LTCMSCOVERAGESNAPSHOT.
/// Required indexes:
///   IX_LTCMSCOVERAGESNAPSHOT_MODULE  (TENANTID, MODULEID, SNAPSHOTDATE DESC)
/// </summary>
public static class TCMSCoverageQB
{
    public const string GET_COVERAGE_REPORT = @"
        SELECT M.MODULEID         AS ModuleId,
               M.MODULECODE       AS ModuleCode,
               M.MODULENAME       AS ModuleName,
               M.PRIORITY         AS Priority,
               COUNT(TC.TESTCASEID) AS TotalTestCases,
               SUM(CASE WHEN TC.PRIORITY = 'P1' THEN 1 ELSE 0 END) AS P1Count,
               SUM(CASE WHEN TC.PRIORITY = 'P2' THEN 1 ELSE 0 END) AS P2Count,
               SUM(CASE WHEN TC.PRIORITY = 'P3' THEN 1 ELSE 0 END) AS P3Count,
               SUM(CASE WHEN TC.TESTTYPE = 'CRUD'       THEN 1 ELSE 0 END) AS CrudCount,
               SUM(CASE WHEN TC.TESTTYPE = 'Validation' THEN 1 ELSE 0 END) AS ValidationCount,
               SUM(CASE WHEN TC.TESTTYPE = 'Functional' THEN 1 ELSE 0 END) AS FunctionalCount,
               SUM(CASE WHEN TC.TESTTYPE = 'Negative'   THEN 1 ELSE 0 END) AS NegativeCount,
               SUM(CASE WHEN TC.TESTTYPE = 'Boundary'   THEN 1 ELSE 0 END) AS BoundaryCount,
               SUM(CASE WHEN TC.TESTTYPE = 'API'        THEN 1 ELSE 0 END) AS ApiCount,
               CAST(
                 SUM(CASE WHEN TC.LASTRUNSTATUS = 'Pass' THEN 1.0 ELSE 0 END)
                 / NULLIF(COUNT(TC.TESTCASEID), 0) * 100 AS DECIMAL(5,2)) AS OverallCoveragePct,
               CAST(
                 SUM(CASE WHEN TC.PRIORITY = 'P1' AND TC.LASTRUNSTATUS = 'Pass' THEN 1.0 ELSE 0 END)
                 / NULLIF(SUM(CASE WHEN TC.PRIORITY = 'P1' THEN 1 ELSE 0 END), 0) * 100 AS DECIMAL(5,2)) AS P1CoveragePct,
               CAST(
                 SUM(CASE WHEN TC.PRIORITY = 'P2' AND TC.LASTRUNSTATUS = 'Pass' THEN 1.0 ELSE 0 END)
                 / NULLIF(SUM(CASE WHEN TC.PRIORITY = 'P2' THEN 1 ELSE 0 END), 0) * 100 AS DECIMAL(5,2)) AS P2CoveragePct,
               ISNULL(LR.LASTPASS, 0)    AS LastRunPass,
               ISNULL(LR.LASTFAIL, 0)    AS LastRunFail,
               ISNULL(LR.LASTSKIP, 0)    AS LastRunSkip,
               LR.LASTRUNDATE            AS LastRunDate
        FROM   MTCMSMODULE M
        LEFT JOIN MTCMSTESTCASE TC ON TC.MODULEID = M.MODULEID AND TC.ISACTIVE = 1
        OUTER APPLY (
            SELECT TOP 1
                   SUM(CASE WHEN RR.RESULT='Pass' THEN 1 ELSE 0 END) AS LASTPASS,
                   SUM(CASE WHEN RR.RESULT='Fail' THEN 1 ELSE 0 END) AS LASTFAIL,
                   SUM(CASE WHEN RR.RESULT='Skip' THEN 1 ELSE 0 END) AS LASTSKIP,
                   TR.ACTUALSTART AS LASTRUNDATE
            FROM   TTCMSTESTRUN TR
            JOIN   TTCMSRUNRESULT RR ON RR.TESTRUNID = TR.TESTRUNID
            WHERE  TR.MODULEID = M.MODULEID AND TR.TENANTID = M.TENANTID
            GROUP BY TR.TESTRUNID, TR.ACTUALSTART
            ORDER BY TR.ACTUALSTART DESC
        ) LR
        WHERE  M.TENANTID  = @TenantId
        AND    (@ProjectId IS NULL OR M.PROJECTID = @ProjectId)
        GROUP BY M.MODULEID, M.MODULECODE, M.MODULENAME, M.PRIORITY,
                 LR.LASTPASS, LR.LASTFAIL, LR.LASTSKIP, LR.LASTRUNDATE
        ORDER BY OverallCoveragePct";

    public const string GET_COVERAGE_HEATMAP = @"
        SELECT M.MODULECODE AS ModuleCode,
               M.MODULENAME AS ModuleName,
               CAST(
                 SUM(CASE WHEN TC.PRIORITY='P1' AND TC.LASTRUNSTATUS='Pass' THEN 1.0 ELSE 0 END)
                 / NULLIF(SUM(CASE WHEN TC.PRIORITY='P1' THEN 1 ELSE 0 END),0)*100 AS DECIMAL(5,2)) AS P1Pct,
               CAST(
                 SUM(CASE WHEN TC.PRIORITY='P2' AND TC.LASTRUNSTATUS='Pass' THEN 1.0 ELSE 0 END)
                 / NULLIF(SUM(CASE WHEN TC.PRIORITY='P2' THEN 1 ELSE 0 END),0)*100 AS DECIMAL(5,2)) AS P2Pct,
               CAST(
                 SUM(CASE WHEN TC.PRIORITY='P3' AND TC.LASTRUNSTATUS='Pass' THEN 1.0 ELSE 0 END)
                 / NULLIF(SUM(CASE WHEN TC.PRIORITY='P3' THEN 1 ELSE 0 END),0)*100 AS DECIMAL(5,2)) AS P3Pct
        FROM   MTCMSMODULE M
        LEFT JOIN MTCMSTESTCASE TC ON TC.MODULEID = M.MODULEID AND TC.ISACTIVE = 1
        WHERE  M.TENANTID  = @TenantId
        AND    M.ISACTIVE  = 1
        AND    (@ProjectId IS NULL OR M.PROJECTID = @ProjectId)
        GROUP BY M.MODULECODE, M.MODULENAME
        ORDER BY M.MODULENAME";

    public const string SAVE_COVERAGE_SNAPSHOT = @"
        INSERT INTO LTCMSCOVERAGESNAPSHOT
            (MODULEID, TESTRUNID, COVERAGEPCT, TOTALCASES, PASSEDCASES, SNAPSHOTDATE, TENANTID)
        VALUES
            (@ModuleId, @TestRunId, @CoveragePct, @TotalCases, @PassedCases, GETUTCDATE(), @TenantId)";

    public const string GET_COVERAGE_TREND = @"
        SELECT SNAPSHOTDATE  AS SnapshotDate,
               COVERAGEPCT   AS CoveragePct,
               TOTALCASES    AS TotalCases,
               PASSEDCASES   AS PassedCases
        FROM   LTCMSCOVERAGESNAPSHOT
        WHERE  MODULEID  = @ModuleId
        AND    TENANTID  = @TenantId
        ORDER BY SNAPSHOTDATE DESC";
}