namespace TMSDAL.Query.AssessmentAssignment
{
    public static class AssessmentAssignmentQB
    {
        public const string GET_ASSESSMENTASSIGNMENT = @"
            SELECT
                MA.ASSESSMENTASSIGNMENTID  AS AssessmentAssignmentId,
                MA.ASSESSMENTID             AS AssessmentId,
                SA.ASSESSMENTTITLE          AS AssessmentName,
                MA.ENROLLMENTID             AS EnrollmentId,
                MA.EMPLOYEEID               AS EmployeeId,
                EMP.EMPLOYEECODE             AS EmployeeCode,
                EMP.EMPLOYEENAME             AS EmployeeName,
                MA.ASSIGNEDBYID             AS AssignedById,
                MA.ASSIGNEDON               AS AssignedOn,
                MA.DUEDATE                  AS DueDate,
                MA.ASSIGNMENTSTATUS         AS AssignmentStatus,
                MA.RESOLVEDATTEMPTID        AS ResolvedAttemptId,
                MA.RESOLVEDON               AS ResolvedOn,
                MA.SKIPREASON               AS SkipReason,
                MA.REMARKS                  AS Remarks,
                MA.VERSION                  AS Version,
                MA.STATUS                   AS Status,
                MA.SORTORDER                AS SortOrder,
                MA.SOURCETYPE               AS SourceType,
                MA.CREATEDBYID              AS CreatedById,
                MA.CREATEDON                AS CreatedOn,
                MA.MODIFIEDBYID             AS ModifiedById,
                MA.MODIFIEDON               AS ModifiedOn,
                MA.TENANTID                 AS TenantId
            FROM   DBO.MASSESSMENTASSIGNMENT MA
            JOIN   DBO.MSESSIONASSESSMENT SA ON SA.ASSESSMENTID = MA.ASSESSMENTID
            JOIN   DBO.MEMPLOYEE EMP          ON EMP.EMPLOYEEID  = MA.EMPLOYEEID
            WHERE  MA.ASSESSMENTASSIGNMENTID = @assessmentassignmentid
            AND    MA.TENANTID               = @tenantid;";

        // Covered by IX_MASSESSMENTASSIGNMENT_DUEDATE_STATUS for the sweep; this general list
        // filters by employee/assessment for the UI, so it isn't index-bound the same way.
        public const string GET_ASSESSMENTASSIGNMENT_LIST = @"
            SELECT
                MA.ASSESSMENTASSIGNMENTID  AS AssessmentAssignmentId,
                MA.ASSESSMENTID             AS AssessmentId,
                SA.ASSESSMENTTITLE          AS AssessmentName,
                MA.EMPLOYEEID               AS EmployeeId,
                EMP.EMPLOYEECODE             AS EmployeeCode,
                EMP.EMPLOYEENAME             AS EmployeeName,
                MA.ASSIGNEDON               AS AssignedOn,
                MA.DUEDATE                  AS DueDate,
                MA.ASSIGNMENTSTATUS         AS AssignmentStatus,
                MA.RESOLVEDON               AS ResolvedOn
            FROM   DBO.MASSESSMENTASSIGNMENT MA
            JOIN   DBO.MSESSIONASSESSMENT SA ON SA.ASSESSMENTID = MA.ASSESSMENTID
            JOIN   DBO.MEMPLOYEE EMP          ON EMP.EMPLOYEEID  = MA.EMPLOYEEID
            WHERE  MA.TENANTID = @tenantid
            AND    (@employeeid = -1 OR MA.EMPLOYEEID = @employeeid)
            AND    (@assessmentid = -1 OR MA.ASSESSMENTID = @assessmentid)
            ORDER  BY MA.DUEDATE ASC;";

        public const string SAVE_ASSESSMENTASSIGNMENT = @"
            INSERT INTO DBO.MASSESSMENTASSIGNMENT
            (
                ASSESSMENTASSIGNMENTID, ASSESSMENTID, ENROLLMENTID, EMPLOYEEID, ASSIGNEDBYID,
                ASSIGNEDON, DUEDATE, ASSIGNMENTSTATUS, RESOLVEDATTEMPTID, RESOLVEDON,
                SKIPREASON, REMARKS,
                VERSION, STATUS, SORTORDER, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @AssessmentAssignmentId, @AssessmentId, @EnrollmentId, @EmployeeId, @AssignedById,
                @AssignedOn, @DueDate, @AssignmentStatus, @ResolvedAttemptId, @ResolvedOn,
                @SkipReason, @Remarks,
                @Version, @Status, @SortOrder, @SourceType,
                @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
            );";

        // Only Assigned/InProgress can be skipped.
        public const string SKIP_ASSESSMENTASSIGNMENT = @"
            UPDATE DBO.MASSESSMENTASSIGNMENT
            SET    ASSIGNMENTSTATUS = 3,  -- Skipped
                   SKIPREASON       = @SkipReason,
                   RESOLVEDON       = @ResolvedOn,
                   MODIFIEDBYID     = @ModifiedById,
                   MODIFIEDON       = @ModifiedOn
            WHERE  ASSESSMENTASSIGNMENTID = @AssessmentAssignmentId
            AND    TENANTID               = @TenantId
            AND    ASSIGNMENTSTATUS IN (0,1);";

        // Called from AssessmentAttemptBLL after an attempt finalizes — resolves the matching
        // Assigned/InProgress assignment for this (AssessmentId, EnrollmentId) pair, if any.
        // A no-op (0 rows affected) when no assignment exists for this attempt — expected for
        // the common case of an attempt taken without ever having been formally assigned.
        public const string RESOLVE_ASSESSMENTASSIGNMENT_ON_ATTEMPT = @"
            UPDATE DBO.MASSESSMENTASSIGNMENT
            SET    ASSIGNMENTSTATUS  = 2,  -- Completed
                   RESOLVEDATTEMPTID = @AssessmentAttemptId,
                   RESOLVEDON        = @ResolvedOn,
                   MODIFIEDBYID      = @ModifiedById,
                   MODIFIEDON        = @ModifiedOn
            WHERE  ASSESSMENTID   = @AssessmentId
            AND    ENROLLMENTID   = @EnrollmentId
            AND    TENANTID       = @TenantId
            AND    ASSIGNMENTSTATUS IN (0,1);";

        public const string SWEEP_MISSED_ASSESSMENTASSIGNMENT = @"
            UPDATE DBO.MASSESSMENTASSIGNMENT
            SET    ASSIGNMENTSTATUS = 4,  -- Missed
                   RESOLVEDON       = @ResolvedOn
            WHERE  ASSIGNMENTSTATUS IN (0,1)
            AND    DUEDATE < @ResolvedOn
            AND    TENANTID = @TenantId;";
    }
}