namespace TMSDAL.Query.TrainerDashboard
{
    /// <summary>
    /// Session/Instance/Enrollment/Completion status reference
    /// (keep in sync with the corresponding C# enums):
    ///
    ///   SessionStatus     : 0=Scheduled, 1=InProgress, 2=Completed, 3=Postponed, 4=Cancelled
    ///   InstanceStatus    : 1,2,3 = Active-family statuses used to filter "active" instances
    ///   EnrollmentStatus  : 0,1,2,4 = statuses counted as "currently enrolled"
    ///   AttendanceStatus  : 0=Present, 1=Absent
    ///   CompletionStatus  : 0=Completed (trainee-level programme completion)
    ///
    /// Required Indexes:
    ///
    ///   CREATE INDEX IX_TINSTANCESESSION_TRAINERID_SESSIONSTATUS_SCHEDULEDDATE
    ///       ON DBO.TINSTANCESESSION (TRAINERID, SESSIONSTATUS, SCHEDULEDDATE)
    ///       INCLUDE (INSTANCEID, INSTANCESESSIONID, STARTTIME, ENDTIME, VENUEID,
    ///                PROGRAMMESESSIONID, ATTENDANCERECORDED, SLNO, ACTUALDURATIONMINS, TRAINERNOTES);
    ///
    ///   CREATE INDEX IX_TTRAININGINSTANCE_TENANTID_INSTANCESTATUS_PRIMARYTRAINERID
    ///       ON DBO.TTRAININGINSTANCE (TENANTID, INSTANCESTATUS, PRIMARYTRAINERID)
    ///       INCLUDE (INSTANCECODE, INSTANCETITLE, PLANNEDSTARTDATE, PLANNEDENDDATE,
    ///                ENROLLEDCOUNT, MAXCAPACITY, PROGRAMMEID);
    ///
    ///   CREATE INDEX IX_TTRAININGATTENDANCE_INSTANCESESSIONID
    ///       ON DBO.TTRAININGATTENDANCE (INSTANCESESSIONID)
    ///       INCLUDE (ATTENDANCESTATUS);
    ///
    ///   CREATE INDEX IX_TTRAININGENROLLMENT_INSTANCEID_ENROLLMENTSTATUS
    ///       ON DBO.TTRAININGENROLLMENT (INSTANCEID, ENROLLMENTSTATUS)
    ///       INCLUDE (ENROLLMENTID);
    ///
    ///   CREATE INDEX IX_TTRAININGCOMPLETION_INSTANCEID_COMPLETIONSTATUS_COMPLETEDON
    ///       ON DBO.TTRAININGCOMPLETION (INSTANCEID, COMPLETIONSTATUS, COMPLETEDON);
    ///
    ///   CREATE INDEX IX_TEVALUATIONRESPONSE_EVALUATIONINSTANCEID
    ///       ON DBO.TEVALUATIONRESPONSE (EVALUATIONINSTANCEID)
    ///       INCLUDE (RESPONSEJSON, CREATEDON);
    /// </summary>
    public static class TrainerDashboardQB
    {
        public const string GET_DASHBOARD_SUMMARY = @"
DECLARE @YearStart DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);
DECLARE @YearEnd   DATE = DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1);
DECLARE @Today     DATE = CAST(GETDATE() AS DATE);

SELECT
    (
        SELECT COUNT(1)
        FROM   DBO.TINSTANCESESSION ts
        INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = ts.INSTANCEID
        WHERE  ts.TRAINERID      = @TrainerId
          AND  ts.SCHEDULEDDATE > @Today
          AND  ts.SESSIONSTATUS  IN (0, 1)
          AND  ti.TENANTID       = @TenantId
    ) AS TotalUpcomingSessions,

    (
        SELECT COUNT(1)
        FROM   DBO.TINSTANCESESSION ts
        INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = ts.INSTANCEID
        WHERE  ts.TRAINERID      = @TrainerId
          AND  ts.SCHEDULEDDATE  = @Today
          AND  ti.TENANTID       = @TenantId
    ) AS TodaySessionCount,

    (
        SELECT COUNT(1)
        FROM   DBO.TINSTANCESESSION ts
        INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = ts.INSTANCEID
        WHERE  ts.TRAINERID      = @TrainerId
          AND  ts.SESSIONSTATUS  = 2
          AND  ts.SCHEDULEDDATE >= @YearStart
          AND  ts.SCHEDULEDDATE <  @YearEnd
          AND  ti.TENANTID       = @TenantId
    ) AS TotalCompletedSessionsThisYear,

    (
        SELECT COUNT(1)
        FROM   DBO.TINSTANCESESSION ts
        INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = ts.INSTANCEID
        WHERE  ts.TRAINERID      = @TrainerId
          AND  ts.SCHEDULEDDATE <  @Today
          AND  ts.SESSIONSTATUS  IN (0, 1)
          AND  ti.TENANTID       = @TenantId
    ) AS OverdueUnresolvedSessions,

    (
        SELECT COUNT(DISTINCT ti.INSTANCEID)
        FROM   DBO.TTRAININGINSTANCE ti
        WHERE  ti.TENANTID         = @TenantId
          AND  ti.INSTANCESTATUS   IN (1, 2, 3)
          AND
          (
              ti.PRIMARYTRAINERID = @TrainerId
              OR EXISTS
              (
                  SELECT 1 FROM DBO.TINSTANCESESSION ts2
                  WHERE  ts2.INSTANCEID = ti.INSTANCEID
                    AND  ts2.TRAINERID  = @TrainerId
              )
          )
    ) AS TotalActiveInstances,

    (
        SELECT COUNT(DISTINCT te.ENROLLMENTID)
        FROM   DBO.TTRAININGENROLLMENT te
        INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = te.INSTANCEID
        WHERE  ti.TENANTID           = @TenantId
          AND  ti.INSTANCESTATUS     IN (1, 2, 3)
          AND  te.ENROLLMENTSTATUS   IN (0, 1, 2, 4)
          AND
          (
              ti.PRIMARYTRAINERID = @TrainerId
              OR EXISTS
              (
                  SELECT 1 FROM DBO.TINSTANCESESSION ts2
                  WHERE  ts2.INSTANCEID = ti.INSTANCEID
                    AND  ts2.TRAINERID  = @TrainerId
              )
          )
    ) AS TotalEnrolledTrainees,

    (
        SELECT COUNT(1)
        FROM   DBO.TTRAININGCOMPLETION tc
        INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = tc.INSTANCEID
        WHERE  ti.TENANTID           = @TenantId
          AND  tc.COMPLETIONSTATUS   = 0
          AND  tc.COMPLETEDON       >= @YearStart
          AND  tc.COMPLETEDON       <  @YearEnd
          AND
          (
              ti.PRIMARYTRAINERID = @TrainerId
              OR EXISTS
              (
                  SELECT 1 FROM DBO.TINSTANCESESSION ts2
                  WHERE  ts2.INSTANCEID = ti.INSTANCEID
                    AND  ts2.TRAINERID  = @TrainerId
              )
          )
    ) AS TotalCompletedTraineesThisYear,

    ISNULL
    (
        (
            SELECT
                CAST(SUM(CASE WHEN ta.ATTENDANCESTATUS = 0 THEN 1 ELSE 0 END) AS DECIMAL(10,4))
                * 100.0
                / NULLIF(COUNT(ta.TRAININGATTENDANCEID), 0)
            FROM DBO.TTRAININGATTENDANCE ta
            INNER JOIN DBO.TINSTANCESESSION ts ON ts.INSTANCESESSIONID = ta.INSTANCESESSIONID
            INNER JOIN DBO.TTRAININGINSTANCE ti ON ti.INSTANCEID = ts.INSTANCEID
            WHERE ts.TRAINERID  = @TrainerId
              AND ti.TENANTID   = @TenantId
              AND
              (
                  ts.SESSIONSTATUS IN (2, 3, 4)
                  OR (ts.SESSIONSTATUS IN (0, 1) AND ts.SCHEDULEDDATE < @Today)
              )
        ), 0
    ) AS OverallAttendanceRate;";

        public const string GET_TODAY_SESSIONS = @"
DECLARE @Today DATE = CAST(GETDATE() AS DATE);

SELECT
    ts.INSTANCESESSIONID    AS InstanceSessionId,
    ts.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    ps.SESSIONTITLE         AS SessionTitle,
    ISNULL(ts.SLNO, 0)      AS SessionSlNo,
    ISNULL(SessionCount.TotalSessions, 1) AS TotalSessionsInInstance,
    ts.SCHEDULEDDATE        AS ScheduledDate,
    ts.STARTTIME            AS StartTime,
    ts.ENDTIME              AS EndTime,
    ISNULL(ts.ACTUALDURATIONMINS, 0) AS ActualDurationMins,
    mv.VENUENAME            AS VenueName,
    ts.SESSIONSTATUS        AS SessionStatus,
    ISNULL(ps.SESSIONTYPE, 0) AS SessionType,
    ti.ENROLLEDCOUNT        AS EnrolledCount,
    ISNULL(Att.AttendedCount, 0) AS AttendedCount,
    ts.ATTENDANCERECORDED   AS AttendanceRecorded,
    ISNULL(TopicCount.TotalTopics, 0) AS TotalTopics,
    ts.TRAINERNOTES         AS TrainerNotes

FROM DBO.TINSTANCESESSION ts

INNER JOIN DBO.TTRAININGINSTANCE  ti ON ti.INSTANCEID          = ts.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID         = ti.PROGRAMMEID
LEFT  JOIN DBO.MPROGRAMMESESSION  ps ON ps.PROGRAMMESESSIONID  = ts.PROGRAMMESESSIONID
                                     AND ts.PROGRAMMESESSIONID <> 0
LEFT  JOIN DBO.MTRAININGVENUE     mv ON mv.TRAININGVENUEID     = ts.VENUEID

OUTER APPLY
(
    SELECT COUNT(1) AS AttendedCount
    FROM DBO.TTRAININGATTENDANCE ta
    WHERE ta.INSTANCESESSIONID = ts.INSTANCESESSIONID
      AND ta.ATTENDANCESTATUS  = 0
) Att

OUTER APPLY
(
    SELECT COUNT(1) AS TotalSessions
    FROM DBO.TINSTANCESESSION ts2
    WHERE ts2.INSTANCEID = ts.INSTANCEID
) SessionCount

OUTER APPLY
(
    SELECT COUNT(1) AS TotalTopics
    FROM DBO.MSESSIONTOPIC st
    WHERE st.PROGRAMMESESSIONID = ps.PROGRAMMESESSIONID
      AND st.ISACTIVE = 1
) TopicCount

WHERE ts.TRAINERID      = @TrainerId
  AND ts.SCHEDULEDDATE  = @Today
  AND ti.TENANTID       = @TenantId

ORDER BY ts.STARTTIME ASC;";

        public const string GET_UPCOMING_SESSIONS = @"
SELECT TOP 20
    ts.INSTANCESESSIONID    AS InstanceSessionId,
    ts.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    ps.SESSIONTITLE         AS SessionTitle,
    ISNULL(ts.SLNO, 0)      AS SessionSlNo,
    ISNULL(SessionCount.TotalSessions, 1) AS TotalSessionsInInstance,
    ts.SCHEDULEDDATE        AS ScheduledDate,
    ts.STARTTIME            AS StartTime,
    ts.ENDTIME              AS EndTime,
    ISNULL(ts.ACTUALDURATIONMINS, 0) AS ActualDurationMins,
    mv.VENUENAME            AS VenueName,
    ts.SESSIONSTATUS        AS SessionStatus,
    ISNULL(ps.SESSIONTYPE, 0) AS SessionType,
    ti.ENROLLEDCOUNT        AS EnrolledCount,
    ISNULL(Att.AttendedCount, 0) AS AttendedCount,
    ts.ATTENDANCERECORDED   AS AttendanceRecorded

FROM DBO.TINSTANCESESSION ts

INNER JOIN DBO.TTRAININGINSTANCE  ti ON ti.INSTANCEID          = ts.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID         = ti.PROGRAMMEID
LEFT  JOIN DBO.MPROGRAMMESESSION  ps ON ps.PROGRAMMESESSIONID  = ts.PROGRAMMESESSIONID
                                     AND ts.PROGRAMMESESSIONID <> 0
LEFT  JOIN DBO.MTRAININGVENUE     mv ON mv.TRAININGVENUEID     = ts.VENUEID

OUTER APPLY
(
    SELECT COUNT(1) AS AttendedCount
    FROM DBO.TTRAININGATTENDANCE ta
    WHERE ta.INSTANCESESSIONID = ts.INSTANCESESSIONID
      AND ta.ATTENDANCESTATUS  = 0
) Att

OUTER APPLY
(
    SELECT COUNT(1) AS TotalSessions
    FROM DBO.TINSTANCESESSION ts2
    WHERE ts2.INSTANCEID = ts.INSTANCEID
) SessionCount

WHERE ts.TRAINERID      = @TrainerId
  AND ts.SCHEDULEDDATE > CAST(GETDATE() AS DATE)
  AND ts.SESSIONSTATUS  IN (0, 1)
  AND ti.TENANTID       = @TenantId

ORDER BY ts.SCHEDULEDDATE ASC, ts.STARTTIME ASC;";

        public const string GET_ACTIVE_INSTANCES = @"
SELECT
    ti.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    ti.INSTANCETITLE        AS InstanceTitle,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    ti.PLANNEDSTARTDATE     AS PlannedStartDate,
    ti.PLANNEDENDDATE       AS PlannedEndDate,
    ti.INSTANCESTATUS       AS InstanceStatus,
    ti.ENROLLEDCOUNT        AS EnrolledCount,
    ti.MAXCAPACITY          AS MaxCapacity,
    ISNULL
    (
        (
            SELECT COUNT(1) FROM DBO.TTRAININGCOMPLETION tc
            WHERE  tc.INSTANCEID       = ti.INSTANCEID
              AND  tc.COMPLETIONSTATUS = 0
        ), 0
    )                       AS CompletedCount

FROM DBO.TTRAININGINSTANCE  ti
INNER JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID = ti.PROGRAMMEID

WHERE ti.TENANTID        = @TenantId
  AND ti.INSTANCESTATUS  IN (1, 2, 3)
  AND
  (
      ti.PRIMARYTRAINERID = @TrainerId
      OR EXISTS
      (
          SELECT 1 FROM DBO.TINSTANCESESSION ts
          WHERE  ts.INSTANCEID = ti.INSTANCEID
            AND  ts.TRAINERID  = @TrainerId
      )
  )

ORDER BY ti.PLANNEDSTARTDATE DESC;";

        public const string GET_ALL_SESSIONS = @"
DECLARE @MonthStart DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
DECLARE @MonthEnd   DATE = DATEADD(MONTH, 1, @MonthStart);

SELECT
    ts.INSTANCESESSIONID    AS InstanceSessionId,
    ts.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    ps.SESSIONTITLE         AS SessionTitle,
    ISNULL(ts.SLNO, 0)      AS SessionSlNo,
    ISNULL(SessionCount.TotalSessions, 1) AS TotalSessionsInInstance,
    ts.SCHEDULEDDATE        AS ScheduledDate,
    ts.STARTTIME            AS StartTime,
    ts.ENDTIME              AS EndTime,
    mv.VENUENAME            AS VenueName,
    ts.SESSIONSTATUS        AS SessionStatus,
    ISNULL(ps.SESSIONTYPE, 0) AS SessionType,
    ti.ENROLLEDCOUNT        AS EnrolledCount,
    ISNULL(Att.AttendedCount, 0) AS AttendedCount

FROM DBO.TINSTANCESESSION ts

INNER JOIN DBO.TTRAININGINSTANCE  ti ON ti.INSTANCEID          = ts.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID         = ti.PROGRAMMEID
LEFT  JOIN DBO.MPROGRAMMESESSION  ps ON ps.PROGRAMMESESSIONID  = ts.PROGRAMMESESSIONID
                                     AND ts.PROGRAMMESESSIONID <> 0
LEFT  JOIN DBO.MTRAININGVENUE     mv ON mv.TRAININGVENUEID     = ts.VENUEID

OUTER APPLY
(
    SELECT COUNT(1) AS AttendedCount
    FROM DBO.TTRAININGATTENDANCE ta
    WHERE ta.INSTANCESESSIONID = ts.INSTANCESESSIONID
      AND ta.ATTENDANCESTATUS  = 0
) Att

OUTER APPLY
(
    SELECT COUNT(1) AS TotalSessions
    FROM DBO.TINSTANCESESSION ts2
    WHERE ts2.INSTANCEID = ts.INSTANCEID
) SessionCount

WHERE ts.TRAINERID      = @TrainerId
  AND ts.SCHEDULEDDATE >= @MonthStart
  AND ts.SCHEDULEDDATE <  @MonthEnd
  AND ti.TENANTID       = @TenantId

ORDER BY ts.SCHEDULEDDATE ASC, ts.STARTTIME ASC;";

        public const string GET_SESSION_HISTORY = @"
SELECT TOP 30
    ts.INSTANCESESSIONID    AS InstanceSessionId,
    ts.INSTANCEID           AS InstanceId,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    ps.SESSIONTITLE         AS SessionTitle,
    ISNULL(ts.SLNO, 0)      AS SessionSlNo,
    ISNULL(ps.SESSIONTYPE, 0) AS SessionType,
    ts.SCHEDULEDDATE        AS ScheduledDate,
    ts.STARTTIME            AS StartTime,
    ts.ENDTIME              AS EndTime,
    ts.SESSIONSTATUS        AS SessionStatus,
    ISNULL(Att.PresentCount, 0) AS PresentCount,
    ISNULL(Att.AbsentCount,  0) AS AbsentCount,
    ISNULL
    (
        CAST(Att.PresentCount AS DECIMAL(10,4)) * 100.0
        / NULLIF(Att.TotalCount, 0),
        0
    )                       AS AttendanceRate

FROM DBO.TINSTANCESESSION ts

INNER JOIN DBO.TTRAININGINSTANCE  ti ON ti.INSTANCEID         = ts.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME mp ON mp.PROGRAMMEID        = ti.PROGRAMMEID
LEFT  JOIN DBO.MPROGRAMMESESSION  ps ON ps.PROGRAMMESESSIONID = ts.PROGRAMMESESSIONID
                                     AND ts.PROGRAMMESESSIONID <> 0

OUTER APPLY
(
    SELECT
        COUNT(1)                                                              AS TotalCount,
        SUM(CASE WHEN ta.ATTENDANCESTATUS = 0 THEN 1 ELSE 0 END)             AS PresentCount,
        SUM(CASE WHEN ta.ATTENDANCESTATUS = 1 THEN 1 ELSE 0 END)             AS AbsentCount
    FROM DBO.TTRAININGATTENDANCE ta
    WHERE ta.INSTANCESESSIONID = ts.INSTANCESESSIONID
) Att

WHERE ts.TRAINERID     = @TrainerId
  AND ti.TENANTID      = @TenantId
  AND
  (
      ts.SESSIONSTATUS IN (2, 3, 4)
      OR (ts.SESSIONSTATUS IN (0, 1) AND ts.SCHEDULEDDATE < CAST(GETDATE() AS DATE))
  )

ORDER BY ts.SCHEDULEDDATE DESC;";

        public const string GET_ATTENDANCE_SUMMARY = @"
SELECT
    mp.PROGRAMMEID          AS ProgrammeId,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    COUNT(DISTINCT ts.INSTANCESESSIONID)         AS TotalSessions,
    SUM(ISNULL(Att.PresentCount, 0))             AS TotalPresent,
    SUM(ISNULL(Att.TotalCount,   0))             AS TotalExpected,
    ISNULL
    (
        CAST(SUM(ISNULL(Att.PresentCount, 0)) AS DECIMAL(10,4)) * 100.0
        / NULLIF(SUM(ISNULL(Att.TotalCount, 0)), 0),
        0
    )                       AS AttendanceRate

FROM DBO.TINSTANCESESSION ts

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

OUTER APPLY
(
    SELECT
        COUNT(1)                                                  AS TotalCount,
        SUM(CASE WHEN ta.ATTENDANCESTATUS = 0 THEN 1 ELSE 0 END) AS PresentCount
    FROM DBO.TTRAININGATTENDANCE ta
    WHERE ta.INSTANCESESSIONID = ts.INSTANCESESSIONID
) Att

WHERE ts.TRAINERID     = @TrainerId
  AND ti.TENANTID      = @TenantId
  AND
  (
      ts.SESSIONSTATUS IN (2, 3, 4)
      OR (ts.SESSIONSTATUS IN (0, 1) AND ts.SCHEDULEDDATE < CAST(GETDATE() AS DATE))
  )

GROUP BY mp.PROGRAMMEID, mp.PROGRAMMETITLE
ORDER BY TotalSessions DESC;";

        // Returns the 10 most-recent L1 evaluation responses linked to instances this trainer delivered.
        // RESPONSEJSON is returned as-is; the BLL or frontend parses individual question scores using
        // the corresponding MEVALUATIONTEMPLATE.QUESTIONSJSON for the question key mapping.
        public const string GET_TRAINER_RATINGS = @"
SELECT TOP 10
    er.EVALUATIONRESPONSEID AS EvaluationResponseId,
    mp.PROGRAMMETITLE       AS ProgrammeTitle,
    ti.INSTANCECODE         AS InstanceCode,
    er.RESPONSEDATAJSON      AS ResponseJson,
    er.OVERALLRATING         AS OverallRating,
    er.SUBMITTEDON           AS RespondedOn

FROM DBO.TEVALUATIONRESPONSE er
INNER JOIN DBO.TEVALUATIONINSTANCE  ei ON ei.EVALUATIONINSTANCEID = er.EVALUATIONINSTANCEID
INNER JOIN DBO.TTRAININGENROLLMENT  te ON te.ENROLLMENTID         = ei.ENROLLMENTID
INNER JOIN DBO.TTRAININGINSTANCE    ti ON ti.INSTANCEID           = te.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME   mp ON mp.PROGRAMMEID          = ti.PROGRAMMEID

WHERE ti.TENANTID = @TenantId
  AND
  (
      ti.PRIMARYTRAINERID = @TrainerId
      OR EXISTS
      (
          SELECT 1 FROM DBO.TINSTANCESESSION ts
          WHERE ts.INSTANCEID = ti.INSTANCEID
            AND ts.TRAINERID  = @TrainerId
      )
  )
  AND er.RESPONSEDATAJSON IS NOT NULL

ORDER BY er.SUBMITTEDON DESC;";
    

        // Aggregated YTD rating summary for the "Rating Summary" card.
        // JSON key names (ContentQuality, DeliveryStyle, Relevance, TrainerEngagement, OverallRating)
        // must match the keys stored in TEVALUATIONRESPONSE.RESPONSEJSON for this tenant's
        // MEVALUATIONTEMPLATE. TRY_CAST ensures NULL (then 0 via ISNULL) on key-name mismatch.
        public const string GET_TRAINER_RATING_SUMMARY = @"
DECLARE @YearStart DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);
DECLARE @YearEnd   DATE = DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1);

SELECT
    COUNT(1) AS TotalEvaluationsYTD,

    ISNULL(AVG(TRY_CAST(JSON_VALUE(er.RESPONSEDATAJSON, '$.OverallRating')        AS DECIMAL(4,2))), 0) AS AvgOverallScore,
    ISNULL(AVG(TRY_CAST(JSON_VALUE(er.RESPONSEDATAJSON, '$.ContentQuality')       AS DECIMAL(4,2))), 0) AS AvgContentQuality,
    ISNULL(AVG(TRY_CAST(JSON_VALUE(er.RESPONSEDATAJSON, '$.DeliveryStyle')        AS DECIMAL(4,2))), 0) AS AvgDeliveryStyle,
    ISNULL(AVG(TRY_CAST(JSON_VALUE(er.RESPONSEDATAJSON, '$.Relevance')            AS DECIMAL(4,2))), 0) AS AvgRelevance,
    ISNULL(AVG(TRY_CAST(JSON_VALUE(er.RESPONSEDATAJSON, '$.TrainerEngagement')    AS DECIMAL(4,2))), 0) AS AvgTrainerEngagement

FROM DBO.TEVALUATIONRESPONSE er
INNER JOIN DBO.TEVALUATIONINSTANCE  ei ON ei.EVALUATIONINSTANCEID = er.EVALUATIONINSTANCEID
INNER JOIN DBO.TTRAININGENROLLMENT  te ON te.ENROLLMENTID         = ei.ENROLLMENTID
INNER JOIN DBO.TTRAININGINSTANCE    ti ON ti.INSTANCEID           = te.INSTANCEID

WHERE ti.TENANTID = @TenantId
  AND er.SUBMITTEDON >= @YearStart
  AND er.SUBMITTEDON <  @YearEnd
  AND er.RESPONSEDATAJSON IS NOT NULL
  AND
  (
      ti.PRIMARYTRAINERID = @TrainerId
      OR EXISTS
      (
          SELECT 1 FROM DBO.TINSTANCESESSION ts
          WHERE ts.INSTANCEID = ti.INSTANCEID
            AND ts.TRAINERID  = @TrainerId
      )
  );";
    }
}