namespace CMGMDAL.QueryBuilders;

// Indexes required:
//   TEMPLOYEECOMPETENCYPROFILE(EMPLOYEEID, TENANTID, STATUS), (FRAMEWORKID, TENANTID)
public static class CompetencyProfileQB
{
    public const string GET_BY_EMPLOYEE = @"
        SELECT  p.CompetencyProfileId, p.EmployeeId,
                e.EMPLOYEENAME    AS EmployeeName,
                p.FrameworkId,
                f.Name            AS FrameworkName,
                p.CareerPathId,
                cp.PathName       AS CareerPathName,
                p.TargetRoleId,
                d.DESIGNATIONNAME AS TargetRoleName,
                p.GeneratedOn, p.OverallCompetencyScore, p.SkillGapsJson, p.Notes, p.Status,
                p.TENANTID AS TenantId, p.CreatedBy
        FROM    TEMPLOYEECOMPETENCYPROFILE p
        INNER JOIN MEMPLOYEE e
               ON  e.EMPLOYEEID = p.EmployeeId AND e.TENANTID = p.TENANTID
        INNER JOIN MCOMPETENCYFRAMEWORK f
               ON  f.FrameworkId = p.FrameworkId AND f.TENANTID = p.TENANTID
        LEFT  JOIN MCAREERPATH cp
               ON  cp.CareerPathId = p.CareerPathId AND cp.TENANTID = p.TENANTID
        LEFT  JOIN MDESIGNATION d
               ON  d.DESIGNATIONID = p.TargetRoleId AND d.TENANTID = p.TENANTID
        WHERE   p.EmployeeId = @EmployeeId
        AND     p.TENANTID   = @TenantId
        AND     p.Status     = 1
        ORDER BY p.CompetencyProfileId DESC";

    public const string GET_EXISTING = @"
        SELECT CompetencyProfileId FROM TEMPLOYEECOMPETENCYPROFILE
        WHERE  EmployeeId   = @EmployeeId
        AND    FrameworkId  = @FrameworkId
        AND    TENANTID     = @TenantId";

    public const string INSERT = @"
        INSERT INTO TEMPLOYEECOMPETENCYPROFILE
            (EmployeeId, FrameworkId, CareerPathId, TargetRoleId, GeneratedOn,
             OverallCompetencyScore, SkillGapsJson, Notes, Status, TENANTID, CreatedBy, CreatedDate)
        VALUES
            (@EmployeeId, @FrameworkId, @CareerPathId, @TargetRoleId, GETUTCDATE(),
             @OverallCompetencyScore, @SkillGapsJson, @Notes, @Status, @TenantId, @CreatedBy, GETUTCDATE());
        SELECT SCOPE_IDENTITY();";

    public const string UPDATE = @"
        UPDATE  TEMPLOYEECOMPETENCYPROFILE
        SET     CareerPathId          = @CareerPathId,
                TargetRoleId          = @TargetRoleId,
                GeneratedOn           = GETUTCDATE(),
                OverallCompetencyScore= @OverallCompetencyScore,
                SkillGapsJson         = @SkillGapsJson,
                Notes                 = @Notes,
                Status                = @Status
        WHERE   CompetencyProfileId = @CompetencyProfileId
        AND     TENANTID            = @TenantId";
}