namespace CMGMDAL.QueryBuilders;

// Indexes required:
//   MCAREERPATH(FRAMEWORKID, TENANTID, STATUS), (CURRENTROLEID, TENANTID), UQ(PATHCODE, TENANTID)
//   MCAREERPATHREQUIREMENT(CAREERPATHID, TENANTID), UQ(CAREERPATHID, SKILLID, TENANTID)
public static class CareerPathQB
{
    public const string GET_BY_ID = @"
        SELECT  p.CareerPathId, p.FrameworkId, f.Name AS FrameworkName,
                p.PathCode, p.PathName,
                p.CurrentRoleId, cr.DESIGNATIONNAME AS CurrentRoleName,
                p.TargetRoleId,  tr.DESIGNATIONNAME AS TargetRoleName,
                p.TypicalMonthsRequired, p.Description, p.Status,
                p.TENANTID AS TenantId, p.CreatedBy, p.ModifiedBy
        FROM    MCAREERPATH p
        INNER JOIN MCOMPETENCYFRAMEWORK f ON f.FrameworkId = p.FrameworkId AND f.TENANTID = p.TENANTID
        INNER JOIN MDESIGNATION cr ON cr.DESIGNATIONID = p.CurrentRoleId AND cr.TENANTID = p.TENANTID
        INNER JOIN MDESIGNATION tr ON tr.DESIGNATIONID = p.TargetRoleId  AND tr.TENANTID = p.TENANTID
        WHERE   p.CareerPathId = @CareerPathId
        AND     p.TENANTID     = @TenantId";

    public const string GET_LIST = @"
        SELECT  p.CareerPathId, p.FrameworkId, f.Name AS FrameworkName,
                p.PathCode, p.PathName,
                p.CurrentRoleId, cr.DESIGNATIONNAME AS CurrentRoleName,
                p.TargetRoleId,  tr.DESIGNATIONNAME AS TargetRoleName,
                p.TypicalMonthsRequired, p.Description, p.Status,
                p.TENANTID AS TenantId, p.CreatedBy, p.ModifiedBy
        FROM    MCAREERPATH p
        INNER JOIN MCOMPETENCYFRAMEWORK f ON f.FrameworkId = p.FrameworkId AND f.TENANTID = p.TENANTID
        INNER JOIN MDESIGNATION cr ON cr.DESIGNATIONID = p.CurrentRoleId AND cr.TENANTID = p.TENANTID
        INNER JOIN MDESIGNATION tr ON tr.DESIGNATIONID = p.TargetRoleId  AND tr.TENANTID = p.TENANTID
        WHERE   p.TENANTID = @TenantId
        AND     (@FrameworkId = -1 OR p.FrameworkId = @FrameworkId)
        AND     (@Status = -1 OR p.Status = @Status)
        ORDER BY p.PathName";

    public const string GET_BY_CURRENT_ROLE = @"
        SELECT  p.CareerPathId, p.FrameworkId, p.PathCode, p.PathName,
                p.CurrentRoleId, p.TargetRoleId, tr.DESIGNATIONNAME AS TargetRoleName,
                p.TypicalMonthsRequired, p.Status, p.TENANTID AS TenantId
        FROM    MCAREERPATH p
        INNER JOIN MDESIGNATION tr ON tr.DESIGNATIONID = p.TargetRoleId AND tr.TENANTID = p.TENANTID
        WHERE   p.CurrentRoleId = @CurrentRoleId
        AND     p.Status        = 1
        AND     p.TENANTID      = @TenantId";

    public const string INSERT = @"
        INSERT INTO MCAREERPATH
            (FrameworkId, PathCode, PathName, CurrentRoleId, TargetRoleId,
             TypicalMonthsRequired, Description, Status, TENANTID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
        VALUES
            (@FrameworkId, @PathCode, @PathName, @CurrentRoleId, @TargetRoleId,
             @TypicalMonthsRequired, @Description, @Status, @TenantId, @CreatedBy, GETUTCDATE(), @ModifiedBy, GETUTCDATE());
        SELECT SCOPE_IDENTITY();";

    public const string UPDATE = @"
        UPDATE  MCAREERPATH
        SET     PathCode              = @PathCode,
                PathName              = @PathName,
                CurrentRoleId         = @CurrentRoleId,
                TargetRoleId          = @TargetRoleId,
                TypicalMonthsRequired = @TypicalMonthsRequired,
                Description           = @Description,
                Status                = @Status,
                ModifiedBy            = @ModifiedBy,
                ModifiedDate          = GETUTCDATE()
        WHERE   CareerPathId = @CareerPathId
        AND     TENANTID     = @TenantId";

    public const string DELETE = @"
        DELETE FROM MCAREERPATH
        WHERE  CareerPathId = @CareerPathId AND TENANTID = @TenantId";

    public const string GET_REQUIREMENTS = @"
        SELECT  r.CareerPathReqId, r.CareerPathId, r.SkillId, s.SKILLNAME AS SkillName,
                r.MinProficiencyLevel,
                sl.LEVELLABEL AS LevelLabel,
                r.IsMandatory, r.SortOrder, r.TENANTID AS TenantId
        FROM    MCAREERPATHREQUIREMENT r
        INNER JOIN MSKILL s
               ON  s.SKILLID  = r.SkillId AND s.TENANTID = r.TENANTID
        LEFT  JOIN MSKILLLEVEL sl
               ON  sl.SKILLID = r.SkillId AND sl.SLNO = r.MinProficiencyLevel
        WHERE   r.CareerPathId = @CareerPathId
        AND     r.TENANTID     = @TenantId
        ORDER BY r.SortOrder, r.IsMandatory DESC, r.CareerPathReqId";

    public const string INSERT_REQUIREMENT = @"
        INSERT INTO MCAREERPATHREQUIREMENT
            (CareerPathId, SkillId, MinProficiencyLevel, IsMandatory, SortOrder, TENANTID)
        VALUES
            (@CareerPathId, @SkillId, @MinProficiencyLevel, @IsMandatory, @SortOrder, @TenantId);
        SELECT SCOPE_IDENTITY();";

    public const string UPDATE_REQUIREMENT = @"
        UPDATE  MCAREERPATHREQUIREMENT
        SET     MinProficiencyLevel = @MinProficiencyLevel,
                IsMandatory         = @IsMandatory,
                SortOrder           = @SortOrder
        WHERE   CareerPathReqId = @CareerPathReqId
        AND     TENANTID        = @TenantId";

    public const string DELETE_REQUIREMENT = @"
        DELETE FROM MCAREERPATHREQUIREMENT
        WHERE  CareerPathReqId = @CareerPathReqId AND TENANTID = @TenantId";
}