namespace TMSDAL.Query.LearnerDashboard
{
    /// <summary>
    /// EnrollmentStatus : 0=Nominated, 1=ManagerApproved, 2=HRApproved, 3=Waitlisted,
    ///                    4=Enrolled, 5=Completed, 6=NoShow, 7=Withdrawn, 8=Rejected
    /// CompletionStatus : 0=Completed, 1=PartialComplete, 2=Failed, 3=NoShow, 4=Withdrawn
    /// CertStatus       : 0=Active, 1=Expired, 2=Revoked, 3=RenewalInProgress
    /// AttendanceStatus : 0=Present, 1=Absent, 2=PartialAttendance, 3=Excused
    /// EvalStatus       : 0=Pending, 1=Sent, 2=Completed, 3=Overdue, 4=Cancelled
    ///
    /// Required Indexes:
    ///
    ///   CREATE INDEX IX_TTRAININGENROLLMENT_EMPLOYEEID_TENANTID_STATUS
    ///       ON DBO.TTRAININGENROLLMENT (EMPLOYEEID, TENANTID, ENROLLMENTSTATUS)
    ///       INCLUDE (INSTANCEID, ENROLLMENTCODE, NOMINATIONSOURCE);
    ///
    ///   CREATE INDEX IX_TTRAININGCERTIFICATE_EMPLOYEEID_TENANTID_CERTSTATUS
    ///       ON DBO.TTRAININGCERTIFICATE (EMPLOYEEID, TENANTID, CERTSTATUS)
    ///       INCLUDE (CERTIFICATENUMBER, CERTIFICATETITLE, PROGRAMMEID, ISSUEDON, EXPIRYDATE, CERTIFICATEURL);
    ///
    ///   CREATE INDEX IX_TTRAININGCOMPLETION_ENROLLMENTID
    ///       ON DBO.TTRAININGCOMPLETION (ENROLLMENTID)
    ///       INCLUDE (COMPLETIONSTATUS, POSTASSESSMENTSCORE, ISPASSED, COMPLETEDON, CERTIFICATEISSUED);
    ///
    ///   CREATE INDEX IX_TTRAININGATTENDANCE_ENROLLMENTID
    ///       ON DBO.TTRAININGATTENDANCE (ENROLLMENTID)
    ///       INCLUDE (INSTANCESESSIONID, ATTENDANCESTATUS);
    ///
    ///   CREATE INDEX IX_TEVALUATIONINSTANCE_EMPLOYEEID_EVALSTATUS
    ///       ON DBO.TEVALUATIONINSTANCE (EMPLOYEEID, EVALSTATUS)
    ///       INCLUDE (ENROLLMENTID);
    /// </summary>
    public static class LearnerDashboardQB
    {
        public const string GET_LEARNER_SUMMARY = @"
DECLARE @YearStart DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);
DECLARE @YearEnd   DATE = DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1);

SELECT
    (
        SELECT COUNT(1)
        FROM   DBO.TTRAININGCOMPLETION  tc
        INNER JOIN DBO.TTRAININGENROLLMENT te ON te.ENROLLMENTID    = tc.ENROLLMENTID
        WHERE  te.EMPLOYEEID       = @EmployeeId
          AND  te.TENANTID         = @TenantId
          AND  tc.COMPLETIONSTATUS = 0                              -- Completed
    ) AS ProgrammesCompleted,

    ISNULL
    (
        (
            SELECT CAST(SUM(ISNULL(ts.ACTUALDURATIONMINS, 0)) AS DECIMAL(10,2)) / 60.0
            FROM   DBO.TTRAININGATTENDANCE  ta
            INNER JOIN DBO.TTRAININGENROLLMENT  te ON te.ENROLLMENTID     = ta.ENROLLMENTID
                                                  AND te.EMPLOYEEID      = @EmployeeId
                                                  AND te.TENANTID        = @TenantId
            INNER JOIN DBO.TINSTANCESESSION     ts ON ts.INSTANCESESSIONID = ta.INSTANCESESSIONID
            WHERE  ta.ATTENDANCESTATUS = 0                         -- Present
              AND  ts.SCHEDULEDDATE  >= @YearStart
              AND  ts.SCHEDULEDDATE  <  @YearEnd
        ), 0
    ) AS TrainingHoursYTD,

    (
        SELECT COUNT(1)
        FROM   DBO.TTRAININGCERTIFICATE tc
        WHERE  tc.EMPLOYEEID = @EmployeeId
          AND  tc.CERTSTATUS  = 0                                  -- Active
          AND  tc.TENANTID    = @TenantId
    ) AS ActiveCertificates,

    ISNULL
    (
        (
            SELECT CAST(AVG(tc.POSTASSESSMENTSCORE) AS DECIMAL(10,2))
            FROM   DBO.TTRAININGCOMPLETION  tc
            INNER JOIN DBO.TTRAININGENROLLMENT te ON te.ENROLLMENTID = tc.ENROLLMENTID
            WHERE  te.EMPLOYEEID          = @EmployeeId
              AND  te.TENANTID            = @TenantId
              AND  tc.ISPASSED            = 1
              AND  tc.POSTASSESSMENTSCORE > 0
        ), 0
    ) AS AvgAssessmentScore,

    (
        -- Assessments defined for enrolled programmes that have not yet been completed
        SELECT COUNT(1)
        FROM   DBO.MSESSIONASSESSMENT       sa
        INNER JOIN DBO.TTRAININGINSTANCE    ti ON ti.PROGRAMMEID       = sa.PROGRAMMEID
                                             AND ti.INSTANCESTATUS    IN (1, 2, 3)
        INNER JOIN DBO.TTRAININGENROLLMENT  te ON te.INSTANCEID        = ti.INSTANCEID
                                             AND te.EMPLOYEEID        = @EmployeeId
                                             AND te.TENANTID          = @TenantId
                                             AND te.ENROLLMENTSTATUS  IN (1, 2, 3, 4)
        WHERE  NOT EXISTS
        (
            SELECT 1 FROM DBO.TASSESSMENTATTEMPT aa
            WHERE  aa.ASSESSMENTID = sa.ASSESSMENTID
              AND  aa.ENROLLMENTID = te.ENROLLMENTID
              AND  aa.COMPLETEDON  IS NOT NULL
        )
    ) AS PendingAssessmentCount,

    (
        -- Open evaluation instances (Sent or Overdue) assigned to this learner
        SELECT COUNT(1)
        FROM   DBO.TEVALUATIONINSTANCE ei
        WHERE  ei.EMPLOYEEID  = @EmployeeId
          AND  ei.EVALSTATUS  IN (1, 3)                            -- Sent, Overdue
          AND  EXISTS
          (
              SELECT 1 FROM DBO.TTRAININGENROLLMENT te
              WHERE  te.ENROLLMENTID = ei.ENROLLMENTID
                AND  te.TENANTID     = @TenantId
          )
    ) AS PendingFeedbackCount;";

        public const string GET_ACTIVE_ENROLLMENTS = @"
SELECT
    te.ENROLLMENTID         AS EnrollmentId,
    te.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    mp.PROGRAMMEID          AS ProgrammeId,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    mp.PROGRAMMECODE        AS ProgrammeCode,
    ti.PLANNEDSTARTDATE     AS PlannedStartDate,
    ti.PLANNEDENDDATE       AS PlannedEndDate,
    ISNULL(Venue.VenueName, '') AS VenueName,
    te.ENROLLMENTSTATUS     AS EnrollmentStatus,
    te.NOMINATIONSOURCE     AS NominationSource,
    ISNULL(Sessions.TotalSessions, 0)     AS TotalSessions,
    ISNULL(Sessions.SessionsCompleted, 0) AS SessionsCompleted,
    ISNULL(PreAss.PreAssessmentScore, 0)  AS PreAssessmentScore,
    ISNULL(PreAss.PreAssessmentDone,  0)  AS PreAssessmentDone,
    CASE WHEN ISNULL(PendAss.PendingCount, 0) > 0 THEN 1 ELSE 0 END AS HasPendingAssessment,
    CASE WHEN ISNULL(PendFb.FeedbackPending, 0) > 0 THEN 1 ELSE 0 END AS HasPendingFeedback

FROM DBO.TTRAININGENROLLMENT  te

INNER JOIN DBO.TTRAININGINSTANCE  ti ON ti.INSTANCEID  = te.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID = ti.PROGRAMMEID

OUTER APPLY
(
    -- First session's venue represents the instance venue on the card
    SELECT TOP 1 mv.VENUENAME
    FROM DBO.TINSTANCESESSION ts
    LEFT JOIN DBO.MTRAININGVENUE mv ON mv.TRAININGVENUEID = ts.VENUEID
    WHERE ts.INSTANCEID = ti.INSTANCEID
    ORDER BY ts.SCHEDULEDDATE ASC, ts.STARTTIME ASC
) Venue(VenueName)

OUTER APPLY
(
    SELECT
        COUNT(1)                                                                 AS TotalSessions,
        SUM(CASE WHEN ta.ATTENDANCESTATUS = 0 THEN 1 ELSE 0 END)                AS SessionsCompleted
    FROM DBO.TINSTANCESESSION    ts
    LEFT JOIN DBO.TTRAININGATTENDANCE ta ON ta.INSTANCESESSIONID = ts.INSTANCESESSIONID
                                        AND ta.ENROLLMENTID     = te.ENROLLMENTID
    WHERE ts.INSTANCEID = ti.INSTANCEID
) Sessions

OUTER APPLY
(
    SELECT TOP 1
        aa.SCOREPCT AS PreAssessmentScore,
        1           AS PreAssessmentDone
    FROM DBO.TASSESSMENTATTEMPT     aa
    INNER JOIN DBO.MSESSIONASSESSMENT sa ON sa.ASSESSMENTID       = aa.ASSESSMENTID
                                        AND sa.ISPREASSESSMENT   = 1
    WHERE aa.ENROLLMENTID = te.ENROLLMENTID
      AND aa.COMPLETEDON  IS NOT NULL
    ORDER BY aa.COMPLETEDON DESC
) PreAss

OUTER APPLY
(
    SELECT COUNT(1) AS PendingCount
    FROM DBO.MSESSIONASSESSMENT sa
    WHERE sa.PROGRAMMEID = mp.PROGRAMMEID
      AND NOT EXISTS
      (
          SELECT 1 FROM DBO.TASSESSMENTATTEMPT aa
          WHERE aa.ASSESSMENTID = sa.ASSESSMENTID
            AND aa.ENROLLMENTID = te.ENROLLMENTID
            AND aa.COMPLETEDON  IS NOT NULL
      )
) PendAss

OUTER APPLY
(
    SELECT COUNT(1) AS FeedbackPending
    FROM DBO.TEVALUATIONINSTANCE ei
    WHERE ei.ENROLLMENTID = te.ENROLLMENTID
      AND ei.EVALSTATUS   IN (1, 3)                                -- Sent, Overdue
) PendFb

WHERE te.EMPLOYEEID      = @EmployeeId
  AND te.TENANTID        = @TenantId
  AND te.ENROLLMENTSTATUS IN (0, 1, 2, 3, 4)                      -- Nominated → Enrolled
  AND ti.INSTANCESTATUS  IN (1, 2, 3)                             -- Active instances

ORDER BY ti.PLANNEDSTARTDATE ASC;";

        public const string GET_LEARNER_CERTIFICATES = @"
SELECT
    tc.TRAININGCERTIFICATEID AS TrainingCertificateId,
    tc.CERTIFICATENUMBER     AS CertificateNumber,
    tc.CERTIFICATETITLE      AS CertificateTitle,
    mp.PROGRAMMETITLE        AS ProgrammeTitle,
    tc.ISSUEDON              AS IssuedOn,
    tc.EXPIRYDATE            AS ExpiryDate,
    tc.CERTSTATUS            AS CertStatus,
    tc.CERTIFICATEURL        AS CertificateUrl

FROM DBO.TTRAININGCERTIFICATE tc
LEFT JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID = tc.PROGRAMMEID

WHERE tc.EMPLOYEEID = @EmployeeId
  AND tc.TENANTID   = @TenantId
  AND tc.STATUS     = 1                                            -- active row (not soft-deleted)

ORDER BY tc.ISSUEDON DESC;";

        public const string GET_TRAINING_HISTORY = @"
SELECT TOP 50
    tc.TRAININGCOMPLETIONID  AS TrainingCompletionId,
    mp.PROGRAMMETITLE        AS ProgrammeTitle,
    ti.INSTANCECODE          AS InstanceCode,
    tc.COMPLETEDON           AS CompletedOn,
    tc.COMPLETIONSTATUS      AS CompletionStatus,
    tc.ISPASSED              AS IsPassed,
    tc.POSTASSESSMENTSCORE   AS PostAssessmentScore,
    tc.CERTIFICATEISSUED     AS CertificateIssued

FROM DBO.TTRAININGCOMPLETION  tc
INNER JOIN DBO.TTRAININGENROLLMENT  te ON te.ENROLLMENTID = tc.ENROLLMENTID
INNER JOIN DBO.TTRAININGINSTANCE    ti ON ti.INSTANCEID   = te.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME   mp ON mp.PROGRAMMEID  = ti.PROGRAMMEID

WHERE te.EMPLOYEEID = @EmployeeId
  AND te.TENANTID   = @TenantId

ORDER BY tc.COMPLETEDON DESC;";
    }
}