namespace CMGMDAL.QueryBuilders;

// Indexes required: MCOMPETENCYKRAMAP(FRAMEWORKID, TENANTID), UQ(FRAMEWORKID, KRAID, SKILLID, TENANTID)
public static class CompetencyKRAMapQB
{
    public const string GET_BY_FRAMEWORK = @"
        SELECT  m.CompetencyKRAMapId, m.FrameworkId, m.KRAId, k.NAME AS KRAName,
                m.SkillId, s.SKILLNAME AS SkillName,
                m.ExpectedProficiencyLevel,
                sl.LEVELLABEL AS LevelLabel,
                m.IsMandatory, m.SortOrder, m.TENANTID AS TenantId
        FROM    MCOMPETENCYKRAMAP m
        INNER JOIN MKRA k
               ON  k.KRAID    = m.KRAId AND k.TENANTID = m.TENANTID
        INNER JOIN MSKILL s
               ON  s.SKILLID  = m.SkillId AND s.TENANTID = m.TENANTID
        LEFT  JOIN MSKILLLEVEL sl
               ON  sl.SKILLID = m.SkillId AND sl.SLNO = m.ExpectedProficiencyLevel
        WHERE   m.FrameworkId = @FrameworkId
        AND     m.TENANTID    = @TenantId
        ORDER BY m.SortOrder, m.CompetencyKRAMapId";

    public const string DELETE_BY_FRAMEWORK = @"
        DELETE FROM MCOMPETENCYKRAMAP
        WHERE  FrameworkId = @FrameworkId AND TENANTID = @TenantId";

    public const string INSERT = @"
        INSERT INTO MCOMPETENCYKRAMAP
            (FrameworkId, KRAId, SkillId, ExpectedProficiencyLevel, IsMandatory, SortOrder, TENANTID)
        VALUES
            (@FrameworkId, @KRAId, @SkillId, @ExpectedProficiencyLevel, @IsMandatory, @SortOrder, @TenantId);
        SELECT SCOPE_IDENTITY();";

    public const string DELETE_ONE = @"
        DELETE FROM MCOMPETENCYKRAMAP
        WHERE  CompetencyKRAMapId = @CompetencyKRAMapId AND TENANTID = @TenantId";
}