namespace TCMSDAL.QueryBuilders;

/// <summary>
/// SQL constants for MTCMSMODULE.
/// Required indexes:
///   IX_MTCMSMODULE_PROJECT   (TENANTID, PROJECTID, ISACTIVE)
///   IX_MTCMSMODULE_CODE      (TENANTID, MODULECODE)
/// </summary>
public static class TCMSModuleQB
{
    public const string GET_MODULE = @"
        SELECT M.MODULEID        AS ModuleId,
               M.MODULECODE      AS ModuleCode,
               M.MODULENAME      AS ModuleName,
               M.DESCRIPTION     AS Description,
               M.PROJECTID       AS ProjectId,
               P.PROJECTNAME     AS ProjectName,
               M.PIEMODULEID     AS PieModuleId,
               M.PIEMODULENAME   AS PieModuleName,
               M.PRIORITY        AS Priority,
               M.OWNER           AS Owner,
               M.ISACTIVE        AS IsActive,
               M.TENANTID        AS TenantId,
               M.CREATEDBY       AS CreatedBy,
               M.CREATEDDATE     AS CreatedDate,
               M.MODIFIEDBY      AS ModifiedBy,
               M.MODIFIEDDATE    AS ModifiedDate
        FROM   MTCMSMODULE M
        JOIN   MTCMSPROJECT P ON P.PROJECTID = M.PROJECTID
        WHERE  M.MODULEID = @ModuleId
        AND    M.TENANTID = @TenantId";

    public const string GET_MODULE_LIST = @"
        SELECT M.MODULEID    AS ModuleId,
               M.MODULECODE  AS ModuleCode,
               M.MODULENAME  AS ModuleName,
               P.PROJECTNAME AS ProjectName,
               M.PRIORITY    AS Priority,
               M.OWNER       AS Owner,
               M.ISACTIVE    AS IsActive,
               ISNULL(TC.TOTALCASES, 0)  AS TotalTestCases,
               ISNULL(TC.PASSED, 0)      AS PassedCount,
               ISNULL(TC.FAILED, 0)      AS FailedCount,
               ISNULL(TC.COVPCT, 0)      AS CoveragePct
        FROM   MTCMSMODULE M
        JOIN   MTCMSPROJECT P ON P.PROJECTID = M.PROJECTID AND P.TENANTID = M.TENANTID
        -- Latest coverage snapshot (subquery)
        OUTER APPLY (
            SELECT COUNT(*)                                      AS TOTALCASES,
                   SUM(CASE WHEN LR.LASTRESULT = 'Pass' THEN 1 ELSE 0 END) AS PASSED,
                   SUM(CASE WHEN LR.LASTRESULT = 'Fail' THEN 1 ELSE 0 END) AS FAILED,
                   CAST(
                     SUM(CASE WHEN LR.LASTRESULT = 'Pass' THEN 1.0 ELSE 0 END)
                     / NULLIF(COUNT(*), 0) * 100 AS DECIMAL(5,2))           AS COVPCT
            FROM   MTCMSTESTCASE TC2
            OUTER APPLY (
                SELECT TOP 1 RESULT AS LASTRESULT
                FROM   TTCMSRUNRESULT RR
                WHERE  RR.TESTCASEID = TC2.TESTCASEID
                ORDER  BY RR.EXECUTEDAT DESC
            ) LR
            WHERE  TC2.MODULEID = M.MODULEID
            AND    TC2.ISACTIVE = 1
        ) TC
        WHERE  M.TENANTID  = @TenantId
        AND    (@ProjectId IS NULL OR M.PROJECTID = @ProjectId)
        AND    (@IsActive  IS NULL OR M.ISACTIVE  = @IsActive)
        ORDER BY M.PRIORITY, M.MODULENAME";

    public const string GET_SELECT_LIST = @"
        SELECT MODULEID   AS ModuleId,
               MODULECODE AS ModuleCode,
               MODULENAME AS ModuleName
        FROM   MTCMSMODULE
        WHERE  TENANTID  = @TenantId
        AND    ISACTIVE   = 1
        AND    (@ProjectId IS NULL OR PROJECTID = @ProjectId)
        ORDER BY MODULENAME";

    public const string SAVE_MODULE = @"
        INSERT INTO MTCMSMODULE
            (MODULECODE, MODULENAME, DESCRIPTION, PROJECTID,
             PIEMODULEID, PIEMODULENAME, PRIORITY, OWNER, ISACTIVE,
             TENANTID, CREATEDBY, CREATEDDATE)
        VALUES
            (@ModuleCode, @ModuleName, @Description, @ProjectId,
             @PieModuleId, @PieModuleName, @Priority, @Owner, 1,
             @TenantId, @CreatedBy, GETUTCDATE())";

    public const string UPDATE_MODULE = @"
        UPDATE MTCMSMODULE
        SET    MODULENAME     = @ModuleName,
               DESCRIPTION    = @Description,
               PROJECTID      = @ProjectId,
               PIEMODULEID    = @PieModuleId,
               PIEMODULENAME  = @PieModuleName,
               PRIORITY       = @Priority,
               OWNER          = @Owner,
               ISACTIVE       = @IsActive,
               MODIFIEDBY     = @ModifiedBy,
               MODIFIEDDATE   = GETUTCDATE()
        WHERE  MODULEID  = @ModuleId
        AND    TENANTID  = @TenantId";

    public const string CHECK_CODE_EXISTS = @"
        SELECT COUNT(1) FROM MTCMSMODULE
        WHERE  MODULECODE = @ModuleCode
        AND    PROJECTID  = @ProjectId
        AND    TENANTID   = @TenantId
        AND    MODULEID  <> ISNULL(@ModuleId, 0)";
}