namespace PERMDAL.QueryBuilders;

// TAPPRAISALEVAL / TAPPRAISALEVALKRA / TAPPRAISALDETAIL carry no TENANTID column —
// tenant scoping happens via the TAPPRAISAL/TAPPRAISALPLAN join (AppraisalId), not directly here.
// Indexes required:
//   TAPPRAISALEVAL(APPRAISALID)
//   TAPPRAISALEVALKRA(APPRAISALEVALID)
//   TAPPRAISALDETAIL(APPRAISALEVALID)
public static class AppraisalEvalQB
{
    public const string GET_BY_ID = @"
        SELECT  te.APPRAISALEVALID             AS AppraisalEvalId,
                te.APPRAISALID                  AS AppraisalId,
                te.SLNO                         AS SlNo,
                te.EVALUATORID                  AS EvaluatorId,
                e.EMPLOYEENAME                  AS EvaluatorName,
                te.MEASUREWEIGHTEDSCORE         AS MeasureWeightedScore,
                te.GOALWEIGHTEDSCORE            AS GoalWeightedScore,
                te.EVALREMARKS                  AS EvalRemarks,
                te.EVALSTATUS                   AS EvalStatus,
                te.MODIFIEDBYID                 AS ModifiedById,
                te.MODIFIEDON                   AS ModifiedOn,
                te.VERSION                      AS Version,
                te.APPRAISALACCEPTANCE          AS AppraisalAcceptance,
                te.APPRAISALACCEPTANCEREMARKS   AS AppraisalAcceptanceRemarks,
                te.EVALTYPE                     AS EvalType,
                te.OVERALLSCORE                 AS OverallScore,
                te.SUBMITTEDON                  AS SubmittedOn
        FROM    TAPPRAISALEVAL te
        LEFT JOIN MEMPLOYEE e
               ON  e.EMPLOYEEID   = te.EVALUATORID
        WHERE   te.APPRAISALEVALID = @AppraisalEvalId";

    public const string GET_LIST_BY_APPRAISAL = @"
        SELECT  te.APPRAISALEVALID             AS AppraisalEvalId,
                te.APPRAISALID                  AS AppraisalId,
                te.SLNO                         AS SlNo,
                te.EVALUATORID                  AS EvaluatorId,
                e.EMPLOYEENAME                  AS EvaluatorName,
                te.MEASUREWEIGHTEDSCORE         AS MeasureWeightedScore,
                te.GOALWEIGHTEDSCORE            AS GoalWeightedScore,
                te.EVALREMARKS                  AS EvalRemarks,
                te.EVALSTATUS                   AS EvalStatus,
                te.MODIFIEDBYID                 AS ModifiedById,
                te.MODIFIEDON                   AS ModifiedOn,
                te.VERSION                      AS Version,
                te.APPRAISALACCEPTANCE          AS AppraisalAcceptance,
                te.APPRAISALACCEPTANCEREMARKS   AS AppraisalAcceptanceRemarks,
                te.EVALTYPE                     AS EvalType,
                te.OVERALLSCORE                 AS OverallScore,
                te.SUBMITTEDON                  AS SubmittedOn
        FROM    TAPPRAISALEVAL te
        LEFT JOIN MEMPLOYEE e
               ON  e.EMPLOYEEID   = te.EVALUATORID
        WHERE   te.APPRAISALID = @AppraisalId
        ORDER BY te.EVALTYPE, te.APPRAISALEVALID";

    public const string GET_KRAS_BY_EVAL = @"
        SELECT  tek.APPRAISALEVALID            AS AppraisalEvalId,
                tek.APPRAISALEVALKRAID         AS AppraisalEvalKRAId,
                tek.SLNO                       AS SlNo,
                tek.KRAID                      AS KRAId,
                k.NAME                         AS KRAName,
                tek.KRAMEASUREWEIGHTEDSCORE    AS KRAMeasureWeightedScore,
                tek.KRAGOALWEIGHTEDSCORE       AS KRAGoalWeightedScore,
                tek.KRASCORE                   AS KRAScore,
                tek.REMARKS                    AS Remarks
        FROM    TAPPRAISALEVALKRA tek
        LEFT JOIN MKRA k
               ON  k.KRAID = tek.KRAID
        WHERE   tek.APPRAISALEVALID = @AppraisalEvalId";

    public const string GET_DETAILS_BY_EVAL = @"
        SELECT  td.APPRAISALDETAILID   AS AppraisalDetailId,
                td.SLNO                AS SlNo,
                td.MEASUREID           AS MeasureId,
                m.NAME                 AS MeasureName,
                td.LOVID               AS LovId,
                td.PARTICULARS         AS Particulars,
                td.SCORE               AS Score,
                td.APPRAISALEVALKRAID  AS AppraisalEvalKRAId,
                td.TARGETVALUE         AS TargetValue,
                td.ACHIEVEDVALUE       AS AchievedValue,
                td.APPRAISALEVALID     AS AppraisalEvalId
        FROM    TAPPRAISALDETAIL td
        LEFT JOIN MMEASURE m
               ON  m.MEASUREID = td.MEASUREID
        WHERE   td.APPRAISALEVALID = @AppraisalEvalId";

    public const string INSERT_EVAL = @"
        INSERT INTO TAPPRAISALEVAL
            (APPRAISALEVALID, APPRAISALID, SLNO, EVALUATORID, MEASUREWEIGHTEDSCORE, GOALWEIGHTEDSCORE,
             EVALREMARKS, EVALSTATUS, MODIFIEDBYID, MODIFIEDON, VERSION,
             APPRAISALACCEPTANCE, APPRAISALACCEPTANCEREMARKS, EVALTYPE, OVERALLSCORE, SUBMITTEDON)
        VALUES
            (@AppraisalEvalId, @AppraisalId, @SlNo, @EvaluatorId, 0, 0,
             @EvalRemarks, 0, @ModifiedById, GETUTCDATE(), 0,
             0, NULL, @EvalType, 0, NULL)";

    public const string UPDATE_EVAL = @"
        UPDATE  TAPPRAISALEVAL
        SET     OVERALLSCORE  = @OverallScore,
                EVALREMARKS   = @EvalRemarks,
                SUBMITTEDON   = @SubmittedOn,
                MODIFIEDBYID  = @ModifiedById,
                MODIFIEDON    = GETUTCDATE(),
                VERSION       = VERSION + 1
        WHERE   APPRAISALEVALID = @AppraisalEvalId";

    public const string INSERT_KRA = @"
        INSERT INTO TAPPRAISALEVALKRA
            (APPRAISALEVALID, APPRAISALEVALKRAID, SLNO, KRAID, KRAMEASUREWEIGHTEDSCORE, KRAGOALWEIGHTEDSCORE, KRASCORE, REMARKS)
        VALUES
            (@AppraisalEvalId, @AppraisalEvalKRAId, @SlNo, @KRAId, 0, 0, @KRAScore, @Remarks)";

    public const string UPDATE_KRA = @"
        UPDATE  TAPPRAISALEVALKRA
        SET     KRASCORE = @KRAScore,
                REMARKS  = @Remarks
        WHERE   APPRAISALEVALKRAID = @AppraisalEvalKRAId";

    public const string INSERT_DETAIL = @"
        INSERT INTO TAPPRAISALDETAIL
            (APPRAISALDETAILID, SLNO, MEASUREID, LOVID, PARTICULARS, SCORE, APPRAISALEVALKRAID, TARGETVALUE, ACHIEVEDVALUE, APPRAISALEVALID)
        VALUES
            (@AppraisalDetailId, @SlNo, @MeasureId, -1, @Particulars, @Score, @AppraisalEvalKRAId, @TargetValue, @AchievedValue, @AppraisalEvalId)";

    public const string UPDATE_DETAIL = @"
        UPDATE  TAPPRAISALDETAIL
        SET     TARGETVALUE   = @TargetValue,
                ACHIEVEDVALUE = @AchievedValue,
                SCORE         = @Score
        WHERE   APPRAISALDETAILID = @AppraisalDetailId";

    public const string UPDATE_ACCEPTANCE = @"
        UPDATE  TAPPRAISALEVAL
        SET     APPRAISALACCEPTANCE = @Acceptance,
                MODIFIEDBYID        = @ModifiedById,
                MODIFIEDON          = GETUTCDATE(),
                VERSION             = VERSION + 1
        WHERE   APPRAISALEVALID = @AppraisalEvalId";

    // Used by CMGM to generate competency profiles
    // Returns the latest finalized appraisal KRA scores for an employee
    // Manager eval (EvalType=1) preferred over self (EvalType=0) per PARTITION BY
    // Index: TAPPRAISAL(EMPLOYEEID, STATUS, TENANTID), TAPPRAISALEVALKRA(APPRAISALEVALID)
    public const string GET_LATEST_KRA_SCORES_BY_EMPLOYEE = @"
        WITH LatestAppraisal AS (
            SELECT TOP 1 AppraisalId, FinalPercentage
            FROM   TAPPRAISAL
            WHERE  EmployeeId = @EmployeeId
            AND    Status     = 4
            AND    TENANTID   = @TenantId
            ORDER  BY AppraisalId DESC
        ),
        RankedEvals AS (
            SELECT  tek.KRAID                                        AS KRAId,
                    tek.KRASCORE                                      AS KRAScore,
                    te.EVALTYPE                                       AS EvalType,
                    ROW_NUMBER() OVER (
                        PARTITION BY tek.KRAID
                        ORDER BY CASE te.EVALTYPE WHEN 1 THEN 0 ELSE 1 END
                    ) AS Rn
            FROM    TAPPRAISALEVALKRA tek
            INNER JOIN TAPPRAISALEVAL te
                   ON  te.APPRAISALEVALID = tek.APPRAISALEVALID
                   AND te.APPRAISALID     = (SELECT AppraisalId FROM LatestAppraisal)
        )
        SELECT  re.KRAId,
                k.NAME              AS KRAName,
                re.KRAScore,
                la.FinalPercentage  AS OverallScore,
                la.AppraisalId
        FROM    RankedEvals re
        INNER JOIN MKRA k
               ON  k.KRAID    = re.KRAId
               AND k.TENANTID = @TenantId
        CROSS JOIN LatestAppraisal la
        WHERE   re.Rn = 1";
}