namespace TMSDAL.Query.AssessmentFlsBridge
{
    public static class AssessmentFlsBridgeQB
    {
        public const string RESOLVE_FORMTEMPLATEID_BY_CODE = @"
            SELECT FORMTEMPLATEID FROM DBO.MFORMTEMPLATE WHERE FORMTEMPLATECODE = @code;";

        // MODULECODE = 'TRAINING' — confirmed live (MODULEID -1399999902 on the verified
        // environment); resolved by code rather than hardcoded, matching FORMTEMPLATEID's own
        // resolve-by-code pattern above, since a raw MMODULE.MODULEID is deployment-generated
        // master data, not something safe to bake into application code.
        public const string RESOLVE_MODULEID_BY_CODE = @"
            SELECT MODULEID FROM DBO.MMODULE WHERE MODULECODE = @code;";

        public const string GET_COORDINATOR_BY_TENANT = @"
            SELECT FLSREGISTRATIONID AS FlsRegistrationId, FLSINSTANCEID AS FlsInstanceId,
                   GROUPID AS GroupId, TENANTID AS TenantId
            FROM   DBO.TASSESSMENTFLSCOORDINATOR
            WHERE  TENANTID = @tenantid;";

        public const string SAVE_COORDINATOR = @"
            INSERT INTO DBO.TASSESSMENTFLSCOORDINATOR (FLSREGISTRATIONID, FLSINSTANCEID, GROUPID, TENANTID)
            VALUES (@FlsRegistrationId, @FlsInstanceId, @GroupId, @TenantId);";

        // Raw INSERT — deliberately bypasses FlsInstanceBLL.CreateInstanceAsync/ScheduleInstanceAsync/
        // OpenInstanceAsync (no existing caller anywhere in this codebase; FlsInstanceQB.INSERT_INSTANCE
        // references MAXSUBMISSIONS/ISANONYMOUS columns that do not exist on MFLSINSTANCE per
        // DB/Migrations/20260627_FLS_Schema.sql — that path would likely throw at runtime).
        // Sets INSTANCESTATUS = 2 (Open) directly since this coordinator instance never needs a
        // scheduled open/close window — it stays open indefinitely for on-demand dispatch.
        public const string INSERT_FLS_INSTANCE_OPEN = @"
            INSERT INTO DBO.MFLSINSTANCE
            (FLSINSTANCEID, FLSREGISTRATIONID, INSTANCECODE, INSTANCENAME, INSTANCESTATUS, CREATEDBYID, MODIFIEDBYID, TENANTID)
            VALUES (@FlsInstanceId, @FlsRegistrationId, @InstanceCode, @InstanceName, 2, @CreatedById, @ModifiedById, @TenantId);";

        public const string INSERT_FLS_INSTANCE_GROUP = @"
            INSERT INTO DBO.MFLSINSTANCEGROUP
            (GROUPID, FLSINSTANCEID, GROUPCODE, GROUPNAME, CREATEDBYID, MODIFIEDBYID, TENANTID)
            VALUES (@GroupId, @FlsInstanceId, @GroupCode, @GroupName, @CreatedById, @ModifiedById, @TenantId);";

        public const string GET_BRIDGE_BY_RESPONDENTID = @"
            SELECT ASSESSMENTFLSBRIDGEID AS AssessmentFlsBridgeId, ASSESSMENTID AS AssessmentId,
                   ENROLLMENTID AS EnrollmentId, FLSRESPONDENTID AS FlsRespondentId,
                   ASSESSMENTATTEMPTID AS AssessmentAttemptId, DELIVERYSTATUS AS DeliveryStatus,
                   CREATEDON AS CreatedOn, TENANTID AS TenantId
            FROM   DBO.TASSESSMENTFLSBRIDGE
            WHERE  FLSRESPONDENTID = @flsrespondentid;";

        public const string SAVE_BRIDGE = @"
            INSERT INTO DBO.TASSESSMENTFLSBRIDGE
            (ASSESSMENTID, ENROLLMENTID, FLSRESPONDENTID, ASSESSMENTATTEMPTID, DELIVERYSTATUS, TENANTID)
            VALUES (@AssessmentId, @EnrollmentId, @FlsRespondentId, @AssessmentAttemptId, @DeliveryStatus, @TenantId);";

        public const string MARK_BRIDGE_OPENED = @"
            UPDATE DBO.TASSESSMENTFLSBRIDGE
            SET    ASSESSMENTATTEMPTID = @AssessmentAttemptId, DELIVERYSTATUS = 1
            WHERE  ASSESSMENTFLSBRIDGEID = @AssessmentFlsBridgeId
            AND    DELIVERYSTATUS = 0;";

        public const string MARK_BRIDGE_SUBMITTED = @"
            UPDATE DBO.TASSESSMENTFLSBRIDGE
            SET    DELIVERYSTATUS = 2
            WHERE  ASSESSMENTFLSBRIDGEID = @AssessmentFlsBridgeId;";
    }
}