namespace GB5Shared.Query.Framework.Validation
{
    /// <summary>
    /// Required indexes: IX_MVALIDATIONASSIGNMENT_ENTITY on (ENTITYTYPE, ENTITYID)
    /// </summary>
    public static class ValidationAssignmentQB
    {
        public const string GET_VALIDATIONASSIGNMENT = @"
SELECT  va.VALIDATIONASSIGNMENTID AS ValidationAssignmentId,
        va.ENTITYTYPE              AS EntityType,
        va.ENTITYID                 AS EntityId,
        va.ENTITYSTEPID              AS EntityStepId,
        va.VALIDATIONCRITERIAID       AS ValidationCriteriaId,
        va.ENFORCEMENT                 AS Enforcement,
        va.ISMANDATORY                   AS IsMandatory,
        va.REMARKS                        AS Remarks
FROM    DBO.MVALIDATIONASSIGNMENT va
WHERE   va.VALIDATIONASSIGNMENTID = @ValidationAssignmentId";


        // =====================================================
        // IValidationCriteriaEvaluator's core query — every criterion assigned to one entity
        // (WI, Show, TrainingProgramme, ...), joined with its criteria details.
        // Pass entityStepId = -2 to mean "all levels" (entity-level + every step), matching the same
        // sentinel convention Enablement's ShowContentLinkQB uses for StepId.
        // =====================================================
        public const string GET_ASSIGNMENTS_WITH_CRITERIA_FOR_ENTITY = @"
SELECT  va.VALIDATIONASSIGNMENTID AS ValidationAssignmentId,
        va.ENTITYTYPE              AS EntityType,
        va.ENTITYID                 AS EntityId,
        va.ENTITYSTEPID              AS EntityStepId,
        va.ENFORCEMENT                 AS Enforcement,
        va.ISMANDATORY                   AS IsMandatory,
        vc.VALIDATIONCRITERIAID           AS ValidationCriteriaId,
        vc.VALIDATIONTYPE                   AS ValidationType,
        vc.TARGETID                           AS TargetId,
        vc.MINSCOREPCT                          AS MinScorePct,
        vc.MINLEVEL                               AS MinLevel,
        vc.VALIDITYDAYS                             AS ValidityDays,
        vc.DESCRIPTION                                AS Description
FROM    DBO.MVALIDATIONASSIGNMENT va
INNER JOIN DBO.MVALIDATIONCRITERIA vc ON vc.VALIDATIONCRITERIAID = va.VALIDATIONCRITERIAID
WHERE   va.ENTITYTYPE = @EntityType
AND     va.ENTITYID   = @EntityId
AND     vc.ISACTIVE   = 1
AND    (vc.TENANTID = @TenantId OR vc.TENANTID = -1)
AND    (@EntityStepId = -2 OR va.ENTITYSTEPID = @EntityStepId)";


        public const string SAVE_VALIDATIONASSIGNMENT = @"
INSERT INTO DBO.MVALIDATIONASSIGNMENT
(
    VALIDATIONASSIGNMENTID, ENTITYTYPE, ENTITYID, ENTITYSTEPID, VALIDATIONCRITERIAID,
    ENFORCEMENT, ISMANDATORY, REMARKS
)
VALUES
(
    @ValidationAssignmentId, @EntityType, @EntityId, @EntityStepId, @ValidationCriteriaId,
    @Enforcement, @IsMandatory, @Remarks
)";


        public const string UPDATE_VALIDATIONASSIGNMENT = @"
UPDATE  DBO.MVALIDATIONASSIGNMENT
SET     ENTITYTYPE           = @EntityType,
        ENTITYID             = @EntityId,
        ENTITYSTEPID         = @EntityStepId,
        VALIDATIONCRITERIAID = @ValidationCriteriaId,
        ENFORCEMENT          = @Enforcement,
        ISMANDATORY          = @IsMandatory,
        REMARKS              = @Remarks
WHERE   VALIDATIONASSIGNMENTID = @ValidationAssignmentId";


        public const string DELETE_VALIDATIONASSIGNMENT = @"
DELETE FROM DBO.MVALIDATIONASSIGNMENT
WHERE   VALIDATIONASSIGNMENTID = @ValidationAssignmentId";


        // =====================================================
        // Proof checks — VALIDATIONTYPE=1 Skill. Same table/columns WI's own
        // WiExecutionQB.GET_EMPLOYEE_SKILL_LEVELS already reads live (DBO schema, cross-module
        // direct-SQL read is the established convention for shared DBO tables in this repo).
        // =====================================================
        public const string GET_EMPLOYEE_SKILL_LEVEL = @"
SELECT  CURRENTLEVNO
FROM    DBO.MEMPLOYEESKILLPROFILE
WHERE   EMPLOYEEID = @EmployeeId
AND     SKILLID    = @SkillId
AND     TENANTID   = @TenantId
AND     STATUS     = 1";


        // =====================================================
        // Proof checks — VALIDATIONTYPE=2 Quiz. Reads TMS's DBO.TQUIZATTEMPT directly (DBO schema,
        // same cross-module direct-SQL convention) rather than calling TMSBLL, since this is a plain
        // data read, not business logic — avoids a hard project reference from Framework to TMS.
        // =====================================================
        public const string GET_LATEST_PASSING_QUIZ_ATTEMPT = @"
SELECT TOP (1)
        COMPLETEDON
FROM    DBO.TQUIZATTEMPT
WHERE   QUIZID          = @QuizId
AND     EMPLOYEEID      = @EmployeeId
AND     ATTEMPTISPASSED = 1
AND     COMPLETEDON IS NOT NULL
AND    (TENANTID = @TenantId OR TENANTID = -1)
ORDER BY COMPLETEDON DESC";


        // =====================================================
        // Proof checks — VALIDATIONTYPE=3 ProgrammeAssessment. Reads TMS's DBO.TASSESSMENTATTEMPT,
        // joined through TTRAININGENROLLMENT to resolve EmployeeId (TASSESSMENTATTEMPT links via
        // ENROLLMENTID, since it's always programme-bound — unlike the standalone Quiz engine).
        // =====================================================
        public const string GET_LATEST_PASSING_ASSESSMENT_ATTEMPT = @"
SELECT TOP (1)
        ta.COMPLETEDON
FROM    DBO.TASSESSMENTATTEMPT ta
INNER JOIN DBO.TTRAININGENROLLMENT te ON te.ENROLLMENTID = ta.ENROLLMENTID
WHERE   ta.ASSESSMENTID    = @AssessmentId
AND     te.EMPLOYEEID      = @EmployeeId
AND     ta.ATTEMPTISPASSED = 1
AND     ta.COMPLETEDON IS NOT NULL
AND    (ta.TENANTID = @TenantId OR ta.TENANTID = -1)
ORDER BY ta.COMPLETEDON DESC";
    }
}