namespace TMSDAL.Query.TmsFeedback;

// Replaces the 4 stored procedures TmsFeedbackDAL used to call (SP_TMS_DISPATCHFEEDBACK's read
// side, SP_TMS_GETBATCHFEEDBACKSTATUS, SP_TMS_GETTRAINERFEEDBACK, SP_TMS_GETNUDGEPENDINGLEARNERS) —
// all of them targeted a phantom schema (MBATCH/TENROLLMENT/TTRAINERSESSION/MTRAINER) that never
// matched the real, deployed tables. Plain parameterized SQL here instead, matching this
// codebase's dominant QB convention. See DB/Migrations/20260815_TMS_Feedback_Fix.sql.
public static class TmsFeedbackQB
{
    // MSURVEYINSTRUMENTCONFIG has no display name of its own — join to MFLSREGISTRATION
    // (REGISTRATIONNAME/REGISTRATIONCODE) for the label the dispatch form's picker shows.
    public const string GET_SELECTLIST_INSTRUMENTCONFIG = @"
        SELECT
            IC.INSTRUMENTCONFIGID   AS InstrumentConfigId,
            R.REGISTRATIONNAME      AS RegistrationName,
            R.REGISTRATIONCODE      AS RegistrationCode
        FROM   MSURVEYINSTRUMENTCONFIG IC
        JOIN   MFLSREGISTRATION R ON R.FLSREGISTRATIONID = IC.FLSREGISTRATIONID
        WHERE  IC.TENANTID = @TenantId
        AND    IC.STATUS   = 1
        ORDER  BY R.REGISTRATIONNAME;";

    // "Batch" = TTRAININGINSTANCE (has BATCHNOTE/PRIMARYTRAINERID/capacity counts — this IS the
    // real batch concept, just never named that in the schema).
    public const string RESOLVE_REGISTRATION_FROM_INSTRUMENT = @"
        SELECT
            IC.FLSREGISTRATIONID                AS FlsRegistrationId,
            R.TOKENEXPIRYHOURS                  AS TokenExpiryHours
        FROM   MSURVEYINSTRUMENTCONFIG IC
        JOIN   MFLSREGISTRATION R ON R.FLSREGISTRATIONID = IC.FLSREGISTRATIONID
        WHERE  IC.INSTRUMENTCONFIGID = @InstrumentConfigId
        AND    IC.TENANTID           = @TenantId;";

    public const string CHECK_DUPLICATE_DISPATCH = @"
        SELECT COUNT(*)
        FROM   MFLSINSTANCE
        WHERE  ENTITYOBJECTID    = @InstanceId
        AND    FLSREGISTRATIONID = @FlsRegistrationId
        AND    INSTANCESTATUS   IN (1, 2)
        AND    TENANTID          = @TenantId;";

    public const string GET_INSTANCE_AND_ENROLLMENTS_FOR_DISPATCH = @"
        SELECT
            TI.INSTANCEID          AS InstanceId,
            TI.INSTANCETITLE       AS InstanceTitle,
            TE.ENROLLMENTID        AS EnrollmentId,
            EMP.EMPLOYEEID         AS EmployeeId,
            EMP.EMPLOYEENAME       AS EmployeeName,
            EMP.PRIMARYEMAILID     AS EmployeeMail,
            EMP.PRIMARYMOBILE      AS EmployeeMobile
        FROM   TTRAININGENROLLMENT TE
        JOIN   TTRAININGINSTANCE   TI  ON TI.INSTANCEID = TE.INSTANCEID
        JOIN   MEMPLOYEE           EMP ON EMP.EMPLOYEEID = TE.EMPLOYEEID
        WHERE  TE.ENROLLMENTID IN @EnrollmentIds
        AND    TE.TENANTID     = @TenantId;";

    public const string GET_BATCH_FEEDBACK_STATUS = @"
        SELECT
            TE.ENROLLMENTID        AS EnrollmentId,
            EMP.EMPLOYEEID         AS EmployeeId,
            EMP.EMPLOYEENAME       AS EmployeeName,
            R.FLSRESPONDENTID      AS FlsRespondentId,
            R.RESPONDENTSTS        AS RespondentSts,
            R.DELIVERYSTATUS       AS DeliveryStatus,
            R.FIRSTOPENEDAT        AS FirstOpenedAt,
            R.SUBMITTEDAT          AS SubmittedAt,
            CASE WHEN R.RESPONDENTSTS = 3 THEN 1 ELSE 0 END AS FeedbackCompleted,
            ISNULL(G.BLOCKINGFLAG, 0) AS BlocksCertificate
        FROM   TTRAININGINSTANCE  TI
        JOIN   MFLSINSTANCE       FI  ON FI.ENTITYOBJECTID = TI.INSTANCEID AND FI.TENANTID = @TenantId
        JOIN   MFLSINSTANCEGROUP  G   ON G.FLSINSTANCEID   = FI.FLSINSTANCEID
        JOIN   MFLSRESPONDENT     R   ON R.GROUPID          = G.GROUPID
        JOIN   TTRAININGENROLLMENT TE ON TE.ENROLLMENTID    = R.SOURCEOBJECTID
        JOIN   MEMPLOYEE          EMP ON EMP.EMPLOYEEID     = TE.EMPLOYEEID
        WHERE  TI.INSTANCEID = @InstanceId
        AND    TI.TENANTID   = @TenantId
        AND    FI.INSTANCESTATUS IN (1, 2, 3)
        ORDER BY EMP.EMPLOYEENAME;";

    // One row per trainer, aggregated across every FLS instance dispatched for that trainer's
    // batches (TTRAINERFEEDBACKSUMMARY is instance-scoped; SP_TMS_FLS_UPDATESUMMARY writes one row
    // per (TrainerId, FlsInstanceId)). PeriodId filters via MFLSINSTANCE.PERIODID when != -1.
    public const string GET_TRAINER_FEEDBACK = @"
        SELECT
            MTP.TRAINERID                        AS TrainerId,
            MTP.TRAINERNAME                       AS TrainerName,
            COUNT(DISTINCT TFS.FLSINSTANCEID)     AS SessionCount,
            SUM(TFS.RESPONDENTCOUNT)              AS RespondentCount,
            SUM(TFS.SUBMITTEDCOUNT)               AS SubmittedCount,
            AVG(TFS.AVGKNOWLEDGE)                 AS AvgKnowledge,
            AVG(TFS.AVGCLARITY)                   AS AvgClarity,
            AVG(TFS.AVGENGAGEMENT)                AS AvgEngagement,
            AVG(TFS.OVERALLAVG)                   AS OverallAvg,
            MAX(TFS.LASTUPDATED)                  AS LastUpdated
        FROM   MTRAINERPROFILE          MTP
        JOIN   TTRAINERFEEDBACKSUMMARY  TFS ON TFS.TRAINERID = MTP.TRAINERID
        LEFT JOIN MFLSINSTANCE          FI  ON FI.FLSINSTANCEID = TFS.FLSINSTANCEID
        WHERE  MTP.TRAINERID = @TrainerId
        AND    (@PeriodId = -1 OR FI.PERIODID = @PeriodId)
        AND    TFS.TENANTID = @TenantId
        AND    TFS.STATUS  <> 2
        GROUP BY MTP.TRAINERID, MTP.TRAINERNAME;";

    public const string GET_NUDGE_PENDING_RESPONDENTS = @"
        SELECT R.FLSRESPONDENTID AS FlsRespondentId
        FROM   TTRAININGINSTANCE TI
        JOIN   MFLSINSTANCE      FI ON FI.ENTITYOBJECTID = TI.INSTANCEID
                                    AND FI.TENANTID       = @TenantId
                                    AND FI.INSTANCESTATUS = 2   -- Open only
        JOIN   MFLSINSTANCEGROUP G  ON G.FLSINSTANCEID    = FI.FLSINSTANCEID
        JOIN   MFLSRESPONDENT    R  ON R.GROUPID          = G.GROUPID
        WHERE  TI.INSTANCEID = @InstanceId
        AND    TI.TENANTID   = @TenantId
        AND    R.RESPONDENTSTS IN (0, 2);";   // RESPONDENTSTS: 0 Not Started, 2 In Progress
}