namespace CMGMDAL.QueryBuilders;

// Indexes required:
//   TSKILLDEVPLAN(TARGETID, PLANTYPE, TENANTID, STATUS)
//   TSKILLDEVPLAN(SKILLID, TENANTID, STATUS)
public static class SkillDevPlanQB
{
    public const string GET_BY_ID = @"
        SELECT  p.DevPlanId, p.PlanType, p.TargetId, p.SkillId,
                s.SKILLNAME AS SkillName,
                p.CurrentProficiencyLevel, p.TargetProficiencyLevel,
                cl.LEVELLABEL AS CurrentLevelLabel,
                tl.LEVELLABEL AS TargetLevelLabel,
                p.Priority, p.TrainingMethod, p.TargetDate,
                p.EstimatedHours, p.ActualHours, p.TrainerId,
                ISNULL(tr.EMPLOYEENAME, '') AS TrainerName,
                p.CompletionNotes, p.Status, p.TENANTID AS TenantId,
                p.CreatedBy, p.ModifiedBy
        FROM    TSKILLDEVPLAN p
        INNER JOIN MSKILL s
               ON  s.SKILLID  = p.SkillId AND s.TENANTID = p.TENANTID
        LEFT  JOIN MSKILLLEVEL cl
               ON  cl.SKILLID = p.SkillId AND cl.SLNO = p.CurrentProficiencyLevel
        LEFT  JOIN MSKILLLEVEL tl
               ON  tl.SKILLID = p.SkillId AND tl.SLNO = p.TargetProficiencyLevel
        LEFT  JOIN MEMPLOYEE tr
               ON  tr.EMPLOYEEID = p.TrainerId AND tr.TENANTID = p.TENANTID AND p.TrainerId != -1
        WHERE   p.DevPlanId = @DevPlanId
        AND     p.TENANTID  = @TenantId";

    public const string GET_LIST = @"
        SELECT  p.DevPlanId, p.PlanType, p.TargetId, p.SkillId,
                s.SKILLNAME AS SkillName,
                p.CurrentProficiencyLevel, p.TargetProficiencyLevel,
                p.Priority, p.TrainingMethod, p.TargetDate,
                p.EstimatedHours, p.ActualHours, p.Status,
                p.TENANTID AS TenantId, p.CreatedBy, p.ModifiedBy
        FROM    TSKILLDEVPLAN p
        INNER JOIN MSKILL s ON s.SKILLID = p.SkillId AND s.TENANTID = p.TENANTID
        WHERE   p.TENANTID    = @TenantId
        AND     (@TargetId = -1    OR p.TargetId  = @TargetId)
        AND     (@PlanType = -1    OR p.PlanType  = @PlanType)
        AND     (@Status   = -1    OR p.Status    = @Status)
        ORDER BY p.Priority DESC, p.TargetDate, p.DevPlanId";

    public const string INSERT = @"
        INSERT INTO TSKILLDEVPLAN
            (PlanType, TargetId, SkillId, CurrentProficiencyLevel, TargetProficiencyLevel,
             Priority, TrainingMethod, TargetDate, EstimatedHours, TrainerId,
             Status, TENANTID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
        VALUES
            (@PlanType, @TargetId, @SkillId, @CurrentProficiencyLevel, @TargetProficiencyLevel,
             @Priority, @TrainingMethod, @TargetDate, @EstimatedHours, @TrainerId,
             0, @TenantId, @CreatedBy, GETUTCDATE(), @ModifiedBy, GETUTCDATE());
        SELECT SCOPE_IDENTITY();";

    public const string UPDATE = @"
        UPDATE  TSKILLDEVPLAN
        SET     TargetProficiencyLevel = @TargetProficiencyLevel,
                Priority               = @Priority,
                TrainingMethod         = @TrainingMethod,
                TargetDate             = @TargetDate,
                EstimatedHours         = @EstimatedHours,
                TrainerId              = @TrainerId,
                Status                 = @Status,
                ModifiedBy             = @ModifiedBy,
                ModifiedDate           = GETUTCDATE()
        WHERE   DevPlanId = @DevPlanId
        AND     TENANTID  = @TenantId";

    public const string COMPLETE = @"
        UPDATE  TSKILLDEVPLAN
        SET     Status          = 2,
                ActualHours     = @ActualHours,
                CompletionNotes = @CompletionNotes,
                ModifiedBy      = @ModifiedBy,
                ModifiedDate    = GETUTCDATE()
        WHERE   DevPlanId = @DevPlanId
        AND     TENANTID  = @TenantId";

    public const string DELETE = @"
        DELETE FROM TSKILLDEVPLAN
        WHERE  DevPlanId = @DevPlanId AND TENANTID = @TenantId AND Status = 0";
}