namespace FLSDAL.Query.Survey;

public static class SurveyQB
{
    // ── Instrument read (4 result sets, single round trip — no stored proc) ──
    // 1=Config, 2=Sections, 3=Questions (inst+bank+trans JOIN), 4=Options (option+trans JOIN)
    public const string GET_INSTRUMENT_MULTI = @"
        SELECT  C.INSTRUMENTCONFIGID, C.FLSREGISTRATIONID, C.FORMTEMPLATEID, C.INSTRUMENTTYPEID,
                C.INSTRUMENTTITLE, C.INTROTEXT, C.THANKYOUMESSAGE,
                CAST(C.SHOWPROGRESSBAR AS INT) AS SHOWPROGRESSBAR, CAST(C.ALLOWPARTIALSUBMIT AS INT) AS ALLOWPARTIALSUBMIT,
                C.LANGDEFAULT, CAST(C.ISANONYMOUS AS INT) AS ISANONYMOUS, CAST(C.ISMANDATORY AS INT) AS ISMANDATORY,
                CAST(C.ISNPSENABLED AS INT) AS ISNPSENABLED,
                CAST(C.TOKENEXPIRYHOURS AS INT) AS TOKENEXPIRYHOURS, CAST(C.VERSIONNO AS INT) AS VERSIONNO,
                CAST(C.ISLOCKED AS INT) AS ISLOCKED, CAST(C.STATUS AS INT) AS STATUS, C.TENANTID,
                R.REGISTRATIONCODE, R.REGISTRATIONNAME
        FROM    MSURVEYINSTRUMENTCONFIG C
        LEFT JOIN MFLSREGISTRATION      R ON R.FLSREGISTRATIONID = C.FLSREGISTRATIONID
        WHERE   C.INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     C.TENANTID           = @TenantId
        AND     C.STATUS            <> 2;

        SELECT  S.INSTRUMENTSECTIONID, S.INSTRUMENTCONFIGID, CAST(S.SLNO AS INT) AS SLNO,
                S.SECTIONCODE, S.SECTIONNAME, S.DESCRIPTION, CAST(S.ISACTIVE AS INT) AS ISACTIVE
        FROM    MSURVEYINSTRUMENTSECTION S
        WHERE   S.INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     S.TENANTID           = @TenantId
        AND     S.ISACTIVE           = 1
        ORDER BY S.SLNO;

        SELECT  IQ.INSTQUESTIONID, IQ.INSTRUMENTSECTIONID, IQ.INSTRUMENTCONFIGID, IQ.QUESTIONID,
                CAST(IQ.SLNO AS INT) AS DisplayOrder, CAST(IQ.ISREQUIREDOVERRIDE AS INT) AS ISREQUIREDOVERRIDE,
                IQ.WEIGHT, CAST(IQ.ISACTIVE AS INT) AS ISACTIVE, IQ.BRANCHRULESJSON,
                QB.QUESTIONTYPEID, CAST(QB.ISREQUIRED AS INT) AS ISREQUIRED,
                CAST(QB.RATINGMIN AS INT) AS ScaleMin, CAST(QB.RATINGMAX AS INT) AS ScaleMax, CAST(QB.RATINGSTEP AS INT) AS ScaleStep,
                QB.RATINGLABELLOW, QB.RATINGLABELHIGH,
                CAST(QB.TEXTMAXLENGTH AS INT) AS TEXTMAXLENGTH, CAST(QB.TEXTMINLENGTH AS INT) AS TEXTMINLENGTH,
                QB.YESLABEL, QB.NOLABEL,
                COALESCE(QT.QUESTIONTEXT, '') AS QuestionText
        FROM    MSURVEYINSTQUESTION IQ
        JOIN    MSURVEYQUESTIONBANK  QB ON QB.QUESTIONID = IQ.QUESTIONID
        LEFT JOIN MSURVEYQUESTIONTRANS QT ON QT.QUESTIONID = QB.QUESTIONID AND QT.LANGCODE = @LangCode
        WHERE   IQ.INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     IQ.TENANTID           = @TenantId
        AND     IQ.ISACTIVE           = 1
        ORDER BY IQ.SLNO;

        SELECT  IQ.INSTQUESTIONID, QO.OPTIONID AS InstOptionId, QO.OPTIONCODE,
                COALESCE(OT.OPTIONTEXT, '') AS OptionText,
                QO.OPTIONVALUE, CAST(QO.SLNO AS INT) AS DisplayOrder, CAST(QO.ISDEFAULT AS INT) AS ISDEFAULT
        FROM    MSURVEYINSTQUESTION IQ
        JOIN    MSURVEYQUESTIONOPTION QO ON QO.QUESTIONID = IQ.QUESTIONID AND QO.ISACTIVE = 1
        LEFT JOIN MSURVEYOPTIONTRANS  OT ON OT.OPTIONID = QO.OPTIONID AND OT.LANGCODE = @LangCode
        WHERE   IQ.INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     IQ.TENANTID           = @TenantId
        AND     IQ.ISACTIVE           = 1
        ORDER BY IQ.INSTQUESTIONID, QO.SLNO;";

    // ── Answer save (was SP_SURVEY_SAVEDRAFTANSWERS/SP_SURVEY_SUBMITANSWERS — no such procs existed) ──
    // MERGE keyed on (FLSSESSIONID, INSTQUESTIONID) — matches UQ_TSURVEYRESPONSEANSWER_QSTN.
    // QUESTIONID/QUESTIONTYPEID are resolved from MSURVEYINSTQUESTION so the caller never needs
    // to know the underlying bank question; @SurveyAnswerId is only consumed on insert.
    public const string UPSERT_ANSWER = @"
        MERGE TSURVEYRESPONSEANSWER AS target
        USING (SELECT @FlsSessionId AS FLSSESSIONID, @InstQuestionId AS INSTQUESTIONID) AS src
            ON target.FLSSESSIONID = src.FLSSESSIONID AND target.INSTQUESTIONID = src.INSTQUESTIONID
        WHEN MATCHED THEN
            UPDATE SET ANSWERTEXT      = @AnswerText,
                       ANSWERNUMERIC   = @AnswerNumeric,
                       ANSWEROPTIONIDS = @AnswerOptionIds,
                       ISDRAFT         = @IsDraft,
                       ANSWEREDAT      = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (SURVEYANSWERID, FLSSESSIONID, INSTQUESTIONID, QUESTIONID, QUESTIONTYPEID,
                    ANSWERTEXT, ANSWERNUMERIC, ANSWEROPTIONIDS, ISDRAFT, TENANTID)
            SELECT @SurveyAnswerId, @FlsSessionId, @InstQuestionId, IQ.QUESTIONID, IQ.QUESTIONTYPEID,
                   @AnswerText, @AnswerNumeric, @AnswerOptionIds, @IsDraft, @TenantId
            FROM   MSURVEYINSTQUESTION IQ
            WHERE  IQ.INSTQUESTIONID = @InstQuestionId;";

    // ── Summary (was SP_SURVEY_UPDATESUMMARY — no such proc existed) ──────────
    public const string GET_ACTIVE_QUESTION_COUNT = @"
        SELECT COUNT(1)
        FROM   MSURVEYINSTQUESTION
        WHERE  INSTRUMENTCONFIGID = @InstrumentConfigId
        AND    ISACTIVE           = 1";

    // @SummaryIdBatchStart must be pre-allocated for at least GET_ACTIVE_QUESTION_COUNT rows —
    // new rows consume @SummaryIdBatchStart + RowSeq - 1 as their SURVEYSUMMARYID.
    public const string RECOMPUTE_SURVEY_SUMMARY = @"
        WITH Stats AS (
            SELECT  IQ.INSTQUESTIONID,
                    IQ.QUESTIONTYPEID,
                    COUNT(A.SURVEYANSWERID)                                              AS ResponseCount,
                    AVG(CASE WHEN IQ.QUESTIONTYPEID IN (1,6) THEN A.ANSWERNUMERIC END)    AS AvgRating,
                    MIN(CASE WHEN IQ.QUESTIONTYPEID IN (1,6) THEN A.ANSWERNUMERIC END)    AS MinRating,
                    MAX(CASE WHEN IQ.QUESTIONTYPEID IN (1,6) THEN A.ANSWERNUMERIC END)    AS MaxRating,
                    SUM(CASE WHEN IQ.QUESTIONTYPEID = 6 AND A.ANSWERNUMERIC >= 9           THEN 1 ELSE 0 END) AS NpsPromoters,
                    SUM(CASE WHEN IQ.QUESTIONTYPEID = 6 AND A.ANSWERNUMERIC BETWEEN 7 AND 8 THEN 1 ELSE 0 END) AS NpsPassives,
                    SUM(CASE WHEN IQ.QUESTIONTYPEID = 6 AND A.ANSWERNUMERIC <= 6           THEN 1 ELSE 0 END) AS NpsDetractors,
                    SUM(CASE WHEN IQ.QUESTIONTYPEID = 5 THEN 1 ELSE 0 END)                AS FreeTextCount,
                    ROW_NUMBER() OVER (ORDER BY IQ.INSTQUESTIONID)                        AS RowSeq
            FROM    MSURVEYINSTQUESTION IQ
            LEFT JOIN TSURVEYRESPONSEANSWER A ON A.INSTQUESTIONID = IQ.INSTQUESTIONID
                                              AND A.ISDRAFT        = 0
                                              AND A.TENANTID        = @TenantId
            LEFT JOIN TFLSRESPONSESESSION   S ON S.FLSSESSIONID    = A.FLSSESSIONID
                                              AND S.FLSINSTANCEID   = @FlsInstanceId
                                              AND S.GROUPID         = @GroupId
            WHERE   IQ.INSTRUMENTCONFIGID = @InstrumentConfigId
            AND     IQ.ISACTIVE           = 1
            GROUP BY IQ.INSTQUESTIONID, IQ.QUESTIONTYPEID
        )
        MERGE TSURVEYRESPONSESUMMARY AS target
        USING Stats AS src
            ON  target.FLSINSTANCEID  = @FlsInstanceId
            AND target.GROUPID        = @GroupId
            AND target.INSTQUESTIONID = src.INSTQUESTIONID
        WHEN MATCHED THEN
            UPDATE SET QUESTIONTYPEID = src.QUESTIONTYPEID,
                       RESPONSECOUNT  = src.ResponseCount,
                       SKIPPEDCOUNT   = CASE WHEN (SELECT SUBMITTEDCOUNT FROM MFLSINSTANCEGROUP WHERE GROUPID = @GroupId) > src.ResponseCount
                                             THEN (SELECT SUBMITTEDCOUNT FROM MFLSINSTANCEGROUP WHERE GROUPID = @GroupId) - src.ResponseCount
                                             ELSE 0 END,
                       AVGRATING      = src.AvgRating,
                       MINRATING      = src.MinRating,
                       MAXRATING      = src.MaxRating,
                       NPSPROMOTERS   = src.NpsPromoters,
                       NPSPASSIVES    = src.NpsPassives,
                       NPSDETRACTORS  = src.NpsDetractors,
                       NPSSCORE       = CASE WHEN src.ResponseCount > 0
                                             THEN CAST(src.NpsPromoters - src.NpsDetractors AS NUMERIC(6,2)) / src.ResponseCount * 100
                                             ELSE NULL END,
                       FREETEXTCOUNT  = src.FreeTextCount,
                       LASTUPDATED    = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (SURVEYSUMMARYID, INSTRUMENTCONFIGID, FLSINSTANCEID, GROUPID, INSTQUESTIONID,
                    QUESTIONTYPEID, RESPONSECOUNT, SKIPPEDCOUNT, AVGRATING, MINRATING, MAXRATING,
                    NPSPROMOTERS, NPSPASSIVES, NPSDETRACTORS, NPSSCORE, FREETEXTCOUNT, TENANTID)
            VALUES (@SummaryIdBatchStart + src.RowSeq - 1, @InstrumentConfigId, @FlsInstanceId, @GroupId, src.INSTQUESTIONID,
                    src.QUESTIONTYPEID, src.ResponseCount,
                    CASE WHEN (SELECT SUBMITTEDCOUNT FROM MFLSINSTANCEGROUP WHERE GROUPID = @GroupId) > src.ResponseCount
                         THEN (SELECT SUBMITTEDCOUNT FROM MFLSINSTANCEGROUP WHERE GROUPID = @GroupId) - src.ResponseCount
                         ELSE 0 END,
                    src.AvgRating, src.MinRating, src.MaxRating,
                    src.NpsPromoters, src.NpsPassives, src.NpsDetractors,
                    CASE WHEN src.ResponseCount > 0
                         THEN CAST(src.NpsPromoters - src.NpsDetractors AS NUMERIC(6,2)) / src.ResponseCount * 100
                         ELSE NULL END,
                    src.FreeTextCount, @TenantId);";

    // ── Config queries ────────────────────────────────────────
    public const string GET_CONFIG_BY_REGISTRATION = @"
        SELECT  C.INSTRUMENTCONFIGID, C.FLSREGISTRATIONID,
                C.FORMTEMPLATEID, C.INSTRUMENTTYPEID,
                C.INSTRUMENTTITLE, C.INTROTEXT, C.THANKYOUMESSAGE,
                CAST(C.SHOWPROGRESSBAR AS INT) AS SHOWPROGRESSBAR, CAST(C.ALLOWPARTIALSUBMIT AS INT) AS ALLOWPARTIALSUBMIT,
                C.LANGDEFAULT, CAST(C.ISANONYMOUS AS INT) AS ISANONYMOUS, CAST(C.ISMANDATORY AS INT) AS ISMANDATORY,
                CAST(C.ISNPSENABLED AS INT) AS ISNPSENABLED, CAST(C.TOKENEXPIRYHOURS AS INT) AS TOKENEXPIRYHOURS,
                CAST(C.VERSIONNO AS INT) AS VERSIONNO, CAST(C.ISLOCKED AS INT) AS ISLOCKED,
                CAST(C.STATUS AS INT) AS STATUS, C.TENANTID,
                R.REGISTRATIONCODE, R.REGISTRATIONNAME
        FROM    MSURVEYINSTRUMENTCONFIG C
        LEFT JOIN MFLSREGISTRATION      R ON R.FLSREGISTRATIONID = C.FLSREGISTRATIONID
        WHERE   C.FLSREGISTRATIONID = @FlsRegistrationId
        AND     C.TENANTID          = @TenantId
        AND     C.STATUS            <> 2";

    public const string GET_CONFIG_BY_ID = @"
        SELECT  C.INSTRUMENTCONFIGID, C.FLSREGISTRATIONID,
                C.FORMTEMPLATEID, C.INSTRUMENTTYPEID,
                C.INSTRUMENTTITLE, C.INTROTEXT, C.THANKYOUMESSAGE,
                CAST(C.SHOWPROGRESSBAR AS INT) AS SHOWPROGRESSBAR, CAST(C.ALLOWPARTIALSUBMIT AS INT) AS ALLOWPARTIALSUBMIT,
                C.LANGDEFAULT, CAST(C.ISANONYMOUS AS INT) AS ISANONYMOUS, CAST(C.ISMANDATORY AS INT) AS ISMANDATORY,
                CAST(C.ISNPSENABLED AS INT) AS ISNPSENABLED, CAST(C.TOKENEXPIRYHOURS AS INT) AS TOKENEXPIRYHOURS,
                CAST(C.VERSIONNO AS INT) AS VERSIONNO, CAST(C.ISLOCKED AS INT) AS ISLOCKED,
                CAST(C.STATUS AS INT) AS STATUS, C.TENANTID,
                R.REGISTRATIONCODE, R.REGISTRATIONNAME
        FROM    MSURVEYINSTRUMENTCONFIG C
        LEFT JOIN MFLSREGISTRATION      R ON R.FLSREGISTRATIONID = C.FLSREGISTRATIONID
        WHERE   C.INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     C.TENANTID           = @TenantId
        AND     C.STATUS            <> 2";

    public const string GET_SECTIONS = @"
        SELECT  INSTRUMENTSECTIONID, INSTRUMENTCONFIGID,
                SLNO, SECTIONCODE, SECTIONNAME,
                DESCRIPTION, ISACTIVE
        FROM    MSURVEYINSTRUMENTSECTION
        WHERE   INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     TENANTID           = @TenantId
        AND     ISACTIVE           = 1
        ORDER BY SLNO";

    public const string GET_ANSWERS_BY_SESSION = @"
        SELECT  A.SURVEYANSWERID, A.FLSSESSIONID, A.INSTQUESTIONID,
                A.QUESTIONID, A.QUESTIONTYPEID,
                A.ANSWERTEXT, A.ANSWERNUMERIC, A.ANSWEROPTIONIDS,
                CAST(A.ISDRAFT AS INT) AS ISDRAFT, A.ANSWEREDAT
        FROM    TSURVEYRESPONSEANSWER A
        WHERE   A.FLSSESSIONID = @FlsSessionId
        AND     A.TENANTID     = @TenantId
        ORDER BY A.INSTQUESTIONID";

    public const string GET_SUMMARY = @"
        SELECT  S.SURVEYSUMMARYID, S.INSTRUMENTCONFIGID,
                S.FLSINSTANCEID, S.GROUPID, S.INSTQUESTIONID,
                S.QUESTIONTYPEID, S.RESPONSECOUNT, S.SKIPPEDCOUNT,
                S.AVGRATING, S.MINRATING, S.MAXRATING,
                S.NPSPROMOTERS, S.NPSPASSIVES, S.NPSDETRACTORS,
                S.NPSSCORE, S.OPTIONDISTJSON, S.FREETEXTCOUNT,
                S.LASTUPDATED
        FROM    TSURVEYRESPONSESUMMARY S
        WHERE   S.FLSINSTANCEID = @FlsInstanceId
        AND     S.GROUPID       = @GroupId
        AND     S.TENANTID      = @TenantId
        ORDER BY S.INSTQUESTIONID";

    // ── Instrument save (insert/update) ────────────────────────
    public const string INSERT_INSTRUMENT_CONFIG = @"
        INSERT INTO MSURVEYINSTRUMENTCONFIG
            (INSTRUMENTCONFIGID, FLSREGISTRATIONID, FORMTEMPLATEID, INSTRUMENTTYPEID,
             INSTRUMENTTITLE, INTROTEXT, THANKYOUMESSAGE, SHOWPROGRESSBAR, ALLOWPARTIALSUBMIT,
             LANGDEFAULT, ISANONYMOUS, STATUS, CREATEDBYID, MODIFIEDBYID, TENANTID)
        VALUES
            (@InstrumentConfigId, @FlsRegistrationId, @FormTemplateId, @InstrumentTypeId,
             @InstrumentTitle, @IntroText, @ThankYouMessage, @ShowProgressBar, @AllowPartialSubmit,
             @LangCode, @IsAnonymous, @Status, @UserId, @UserId, @TenantId)";

    public const string UPDATE_INSTRUMENT_CONFIG = @"
        UPDATE MSURVEYINSTRUMENTCONFIG
        SET    FORMTEMPLATEID     = @FormTemplateId,
               INSTRUMENTTYPEID   = @InstrumentTypeId,
               INSTRUMENTTITLE    = @InstrumentTitle,
               INTROTEXT          = @IntroText,
               THANKYOUMESSAGE    = @ThankYouMessage,
               SHOWPROGRESSBAR    = @ShowProgressBar,
               ALLOWPARTIALSUBMIT = @AllowPartialSubmit,
               LANGDEFAULT        = @LangCode,
               ISANONYMOUS        = @IsAnonymous,
               STATUS             = @Status,
               MODIFIEDBYID       = @UserId,
               MODIFIEDON         = GETDATE(),
               VERSION            = VERSION + 1
        WHERE  INSTRUMENTCONFIGID = @InstrumentConfigId
        AND    TENANTID           = @TenantId
        AND    STATUS            <> 2";

    public const string INSERT_SECTION = @"
        INSERT INTO MSURVEYINSTRUMENTSECTION
            (INSTRUMENTSECTIONID, INSTRUMENTCONFIGID, SLNO, SECTIONCODE, SECTIONNAME, ISACTIVE, TENANTID)
        VALUES
            (@InstrumentSectionId, @InstrumentConfigId, @DisplayOrder, @SectionCode, @SectionName, 1, @TenantId)";

    public const string UPDATE_SECTION = @"
        UPDATE MSURVEYINSTRUMENTSECTION
        SET    SLNO        = @DisplayOrder,
               SECTIONCODE  = @SectionCode,
               SECTIONNAME  = @SectionName,
               ISACTIVE     = 1
        WHERE  INSTRUMENTSECTIONID = @InstrumentSectionId
        AND    TENANTID             = @TenantId";

    public const string DEACTIVATE_SECTIONS_NOT_IN = @"
        UPDATE MSURVEYINSTRUMENTSECTION
        SET    ISACTIVE = 0
        WHERE  INSTRUMENTCONFIGID = @InstrumentConfigId
        AND    TENANTID           = @TenantId
        AND    INSTRUMENTSECTIONID NOT IN @KeepIds";

    public const string DEACTIVATE_ALL_SECTIONS = @"
        UPDATE MSURVEYINSTRUMENTSECTION
        SET    ISACTIVE = 0
        WHERE  INSTRUMENTCONFIGID = @InstrumentConfigId
        AND    TENANTID           = @TenantId";

    public const string DELETE_SECTION = @"
        UPDATE MSURVEYINSTRUMENTSECTION
        SET    ISACTIVE = 0
        WHERE  INSTRUMENTSECTIONID = @InstrumentSectionId
        AND    TENANTID            = @TenantId
        AND    ISACTIVE            = 1";

    public const string GET_SECTION_BY_ID = @"
        SELECT  INSTRUMENTSECTIONID, INSTRUMENTCONFIGID, CAST(SLNO AS INT) AS SLNO,
                SECTIONCODE, SECTIONNAME, DESCRIPTION, CAST(ISACTIVE AS INT) AS ISACTIVE
        FROM    MSURVEYINSTRUMENTSECTION
        WHERE   INSTRUMENTSECTIONID = @InstrumentSectionId
        AND     TENANTID            = @TenantId
        AND     ISACTIVE            = 1";

    public const string GET_SECTIONS_BY_CONFIG = @"
        SELECT  INSTRUMENTSECTIONID, INSTRUMENTCONFIGID, CAST(SLNO AS INT) AS SLNO,
                SECTIONCODE, SECTIONNAME, DESCRIPTION, CAST(ISACTIVE AS INT) AS ISACTIVE
        FROM    MSURVEYINSTRUMENTSECTION
        WHERE   INSTRUMENTCONFIGID = @InstrumentConfigId
        AND     TENANTID           = @TenantId
        AND     ISACTIVE           = 1
        ORDER BY SLNO";

    // ── Standalone Section select list (scoped by InstrumentConfigId via {CRITERIA}) ─
    public const string GET_SELECTLIST_SURVEYSECTION = @"
        WITH SurveySectionCTE AS (
            SELECT s.INSTRUMENTSECTIONID AS Id,
                   s.SECTIONCODE         AS Code,
                   s.SECTIONNAME         AS Name,
                   ROW_NUMBER() OVER (ORDER BY s.SLNO) AS RowNum
            FROM   MSURVEYINSTRUMENTSECTION s
            WHERE  s.TENANTID = @TenantId
            AND    s.ISACTIVE = 1 {CRITERIA})
        SELECT Id, Code, Name
        FROM   SurveySectionCTE
        WHERE  (@firstnumber = -1 AND @maxresult = -1)
           OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

    public const string INSERT_INSTQUESTION = @"
        INSERT INTO MSURVEYINSTQUESTION
            (INSTQUESTIONID, INSTRUMENTSECTIONID, INSTRUMENTCONFIGID, QUESTIONID,
             SLNO, ISREQUIREDOVERRIDE, ISACTIVE, TENANTID)
        VALUES
            (@InstQuestionId, @InstrumentSectionId, @InstrumentConfigId, @QuestionId,
             @DisplayOrder, @IsRequiredOverride, 1, @TenantId)";

    public const string UPDATE_INSTQUESTION = @"
        UPDATE MSURVEYINSTQUESTION
        SET    QUESTIONID         = @QuestionId,
               SLNO                = @DisplayOrder,
               ISREQUIREDOVERRIDE  = @IsRequiredOverride,
               ISACTIVE            = 1
        WHERE  INSTQUESTIONID = @InstQuestionId
        AND    TENANTID       = @TenantId";

    public const string DEACTIVATE_INSTQUESTIONS_NOT_IN = @"
        UPDATE MSURVEYINSTQUESTION
        SET    ISACTIVE = 0
        WHERE  INSTRUMENTSECTIONID = @InstrumentSectionId
        AND    TENANTID             = @TenantId
        AND    INSTQUESTIONID NOT IN @KeepIds";

    public const string DEACTIVATE_ALL_INSTQUESTIONS_IN_SECTION = @"
        UPDATE MSURVEYINSTQUESTION
        SET    ISACTIVE = 0
        WHERE  INSTRUMENTSECTIONID = @InstrumentSectionId
        AND    TENANTID             = @TenantId";

    // ── Question bank save (insert/update) ─────────────────────
    public const string INSERT_QUESTION_BANK = @"
        INSERT INTO MSURVEYQUESTIONBANK
            (QUESTIONID, QUESTIONCODE, QUESTIONTYPEID, RATINGMIN, RATINGMAX,
             RATINGLABELLOW, RATINGLABELHIGH, TEXTMAXLENGTH, TEXTMINLENGTH,
             YESLABEL, NOLABEL, ISREQUIRED, SORTORDER, CREATEDBYID, MODIFIEDBYID, TENANTID)
        VALUES
            (@QuestionId, @QuestionCode, @QuestionTypeId, @RatingMin, @RatingMax,
             @RatingLabelLow, @RatingLabelHigh, @TextMaxLength, @TextMinLength,
             @YesLabel, @NoLabel, @IsRequired, @SortOrder, @UserId, @UserId, @TenantId)";

    public const string UPDATE_QUESTION_BANK = @"
        UPDATE MSURVEYQUESTIONBANK
        SET    QUESTIONCODE     = @QuestionCode,
               QUESTIONTYPEID   = @QuestionTypeId,
               RATINGMIN        = @RatingMin,
               RATINGMAX        = @RatingMax,
               RATINGLABELLOW   = @RatingLabelLow,
               RATINGLABELHIGH  = @RatingLabelHigh,
               TEXTMAXLENGTH    = @TextMaxLength,
               TEXTMINLENGTH    = @TextMinLength,
               YESLABEL         = @YesLabel,
               NOLABEL          = @NoLabel,
               ISREQUIRED       = @IsRequired,
               SORTORDER        = @SortOrder,
               MODIFIEDBYID     = @UserId,
               MODIFIEDON       = GETDATE(),
               VERSION          = VERSION + 1
        WHERE  QUESTIONID       = @QuestionId
        AND    TENANTID         = @TenantId
        AND    STATUS          <> 2";

    // ── Question bank deactivate (soft delete) ─────────────────
    public const string DEACTIVATE_QUESTION_BANK = @"
        UPDATE MSURVEYQUESTIONBANK
        SET    STATUS       = 2,
               MODIFIEDON   = GETDATE()
        WHERE  QUESTIONID   = @QuestionId
        AND    TENANTID     = @TenantId
        AND    STATUS       <> 2";

    // MERGE keyed by (QuestionId, LangCode) — @QuestionTransId is only used on insert.
    public const string UPSERT_QUESTION_TRANS = @"
        MERGE MSURVEYQUESTIONTRANS AS target
        USING (SELECT @QuestionId AS QUESTIONID, @LangCode AS LANGCODE) AS src
            ON target.QUESTIONID = src.QUESTIONID AND target.LANGCODE = src.LANGCODE
        WHEN MATCHED THEN
            UPDATE SET QUESTIONTEXT = @QuestionText
        WHEN NOT MATCHED THEN
            INSERT (QUESTIONTRANSID, QUESTIONID, LANGCODE, QUESTIONTEXT, TENANTID)
            VALUES (@QuestionTransId, @QuestionId, @LangCode, @QuestionText, @TenantId);";

    public const string INSERT_OPTION = @"
        INSERT INTO MSURVEYQUESTIONOPTION
            (OPTIONID, QUESTIONID, SLNO, OPTIONCODE, ISACTIVE, TENANTID)
        VALUES
            (@OptionId, @QuestionId, @DisplayOrder, @OptionCode, 1, @TenantId)";

    public const string UPDATE_OPTION = @"
        UPDATE MSURVEYQUESTIONOPTION
        SET    SLNO       = @DisplayOrder,
               OPTIONCODE = @OptionCode,
               ISACTIVE   = 1
        WHERE  OPTIONID   = @OptionId
        AND    TENANTID   = @TenantId";

    public const string DEACTIVATE_OPTIONS_NOT_IN = @"
        UPDATE MSURVEYQUESTIONOPTION
        SET    ISACTIVE = 0
        WHERE  QUESTIONID = @QuestionId
        AND    TENANTID   = @TenantId
        AND    OPTIONID NOT IN @KeepIds";

    public const string DEACTIVATE_ALL_OPTIONS = @"
        UPDATE MSURVEYQUESTIONOPTION
        SET    ISACTIVE = 0
        WHERE  QUESTIONID = @QuestionId
        AND    TENANTID   = @TenantId";

    // MERGE keyed by (OptionId, LangCode) — @OptionTransId is only used on insert.
    public const string UPSERT_OPTION_TRANS = @"
        MERGE MSURVEYOPTIONTRANS AS target
        USING (SELECT @OptionId AS OPTIONID, @LangCode AS LANGCODE) AS src
            ON target.OPTIONID = src.OPTIONID AND target.LANGCODE = src.LANGCODE
        WHEN MATCHED THEN
            UPDATE SET OPTIONTEXT = @OptionText
        WHEN NOT MATCHED THEN
            INSERT (OPTIONTRANSID, OPTIONID, LANGCODE, OPTIONTEXT, TENANTID)
            VALUES (@OptionTransId, @OptionId, @LangCode, @OptionText, @TenantId);";

    // ── Question bank list/search (Edit Existing tab) ──────────
    public const string GET_QUESTION_BANK_LIST = @"
        SELECT  QB.QUESTIONID, QB.QUESTIONCODE, QB.QUESTIONTYPEID,
                CAST(QB.ISREQUIRED AS INT) AS ISREQUIRED, CAST(QB.SORTORDER AS INT) AS SORTORDER,
                COALESCE(QT.QUESTIONTEXT, '') AS QuestionText
        FROM    MSURVEYQUESTIONBANK QB
        LEFT JOIN MSURVEYQUESTIONTRANS QT ON QT.QUESTIONID = QB.QUESTIONID AND QT.LANGCODE = @LangCode
        WHERE   QB.TENANTID = @TenantId
        AND     QB.STATUS  <> 2
        AND     (@SearchText = '' OR QB.QUESTIONCODE LIKE '%' + @SearchText + '%'
                                   OR QT.QUESTIONTEXT LIKE '%' + @SearchText + '%')
        ORDER BY QB.SORTORDER, QB.QUESTIONCODE
        OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";

    // ── Question bank — get single question by ID (Edit form) ──
    public const string GET_QUESTION_BY_ID = @"
        SELECT  QB.QUESTIONID, QB.QUESTIONCODE, QB.QUESTIONTYPEID,
                CAST(QB.ISREQUIRED AS INT) AS ISREQUIRED, CAST(QB.SORTORDER AS INT) AS SORTORDER,
                CAST(QB.RATINGMIN AS INT) AS RATINGMIN, CAST(QB.RATINGMAX AS INT) AS RATINGMAX,
                QB.RATINGLABELLOW, QB.RATINGLABELHIGH,
                CAST(QB.TEXTMAXLENGTH AS INT) AS TEXTMAXLENGTH, CAST(QB.TEXTMINLENGTH AS INT) AS TEXTMINLENGTH,
                QB.YESLABEL, QB.NOLABEL,
                COALESCE(QT.QUESTIONTEXT, '') AS QuestionText
        FROM    MSURVEYQUESTIONBANK QB
        LEFT JOIN MSURVEYQUESTIONTRANS QT ON QT.QUESTIONID = QB.QUESTIONID AND QT.LANGCODE = @LangCode
        WHERE   QB.QUESTIONID = @QuestionId
        AND     QB.TENANTID   = @TenantId
        AND     QB.STATUS    <> 2";

    public const string GET_OPTIONS_BY_QUESTION = @"
        SELECT  QO.OPTIONID, QO.OPTIONCODE, CAST(QO.SLNO AS INT) AS DisplayOrder,
                COALESCE(OT.OPTIONTEXT, '') AS OptionText
        FROM    MSURVEYQUESTIONOPTION QO
        LEFT JOIN MSURVEYOPTIONTRANS  OT ON OT.OPTIONID = QO.OPTIONID AND OT.LANGCODE = @LangCode
        WHERE   QO.QUESTIONID = @QuestionId
        AND     QO.TENANTID   = @TenantId
        AND     QO.ISACTIVE   = 1
        ORDER BY QO.SLNO";

    // ── Select List ─────────────────────────────────────────────
    public const string GET_SELECTLIST_SURVEYQUESTION = @"
        WITH SurveyQuestionCTE AS (
            SELECT qb.QUESTIONID    AS Id,
                   qb.QUESTIONCODE  AS Code,
                   COALESCE(qt.QUESTIONTEXT, qb.QUESTIONCODE) AS Name,
                   ROW_NUMBER() OVER (ORDER BY qb.SORTORDER, qb.QUESTIONCODE) AS RowNum
            FROM   MSURVEYQUESTIONBANK qb
            LEFT JOIN MSURVEYQUESTIONTRANS qt ON qt.QUESTIONID = qb.QUESTIONID AND qt.LANGCODE = @LangCode
            WHERE  qb.TENANTID = @TenantId
            AND    qb.STATUS  <> 2 {CRITERIA})
        SELECT Id, Code, Name
        FROM   SurveyQuestionCTE
        WHERE  (@firstnumber = -1 AND @maxresult = -1)
           OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

    // ── Select List ─────────────────────────────────────────────
    public const string GET_SELECTLIST_SURVEYQUESTIONTYPE = @"
        WITH SurveyQuestionTypeCTE AS (
            SELECT t.QUESTIONTYPEID   AS Id,
                   t.QUESTIONTYPECODE AS Code,
                   t.QUESTIONTYPENAME AS Name,
                   ROW_NUMBER() OVER (ORDER BY t.SORTORDER) AS RowNum
            FROM   MSURVEYQUESTIONTYPE t
            WHERE  t.TENANTID = 0
            AND    t.STATUS  <> 2 {CRITERIA})
        SELECT Id, Code, Name
        FROM   SurveyQuestionTypeCTE
        WHERE  (@firstnumber = -1 AND @maxresult = -1)
           OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

    public const string LOCK_INSTRUMENT = @"
        UPDATE MSURVEYINSTRUMENTCONFIG
        SET    ISLOCKED = 1, LOCKEDON = GETUTCDATE(), MODIFIEDON = GETUTCDATE()
        WHERE  INSTRUMENTCONFIGID = @InstrumentConfigId
        AND    ISLOCKED = 0
        AND    TENANTID = @TenantId";

    public const string GET_ANSWERS_BY_RESPONDENT = @"
        SELECT  A.SURVEYANSWERID, A.FLSSESSIONID, A.INSTQUESTIONID,
                A.QUESTIONID, A.QUESTIONTYPEID,
                A.ANSWERTEXT, A.ANSWERNUMERIC, A.ANSWEROPTIONIDS,
                CAST(A.ISDRAFT AS INT) AS ISDRAFT, A.ANSWEREDAT
        FROM    TSURVEYRESPONSEANSWER A
        JOIN    TFLSRESPONSESESSION   S ON S.FLSSESSIONID = A.FLSSESSIONID
        WHERE   S.FLSRESPONDENTID = @FlsRespondentId
        AND     A.ISDRAFT         = 0
        AND     A.TENANTID        = @TenantId
        ORDER BY A.INSTQUESTIONID";

    // Quiz/Assessment roadmap Phase 2: MSURVEYINSTRUMENTTYPE's QUIZ/TEST rows are retired
    // (STATUS=2/Archived) — this single-row lookup lets SurveyInstrumentBLL.SaveInstrumentAsync
    // reject any attempt to author a new FLS survey against a retired instrument type, redirecting
    // authors to TMS's real Quiz engine instead.
    public const string GET_SURVEYINSTRUMENTTYPE_STATUS = @"
        SELECT t.STATUS
        FROM   MSURVEYINSTRUMENTTYPE t
        WHERE  t.INSTRUMENTTYPEID = @InstrumentTypeId
        AND    t.TENANTID = 0";

    // ── Select List ───────────────────────────────────────────
    public const string GET_SELECTLIST_SURVEYINSTRUMENTTYPE = @"
        WITH SurveyInstrumentTypeCTE AS (
            SELECT t.INSTRUMENTTYPEID   AS Id,
                   t.INSTRUMENTTYPECODE AS Code,
                   t.INSTRUMENTTYPENAME AS Name,
                   ROW_NUMBER() OVER (ORDER BY t.SORTORDER) AS RowNum
            FROM   MSURVEYINSTRUMENTTYPE t
            WHERE  t.TENANTID = 0
            AND    t.STATUS  <> 2 {CRITERIA})
        SELECT Id, Code, Name
        FROM   SurveyInstrumentTypeCTE
        WHERE  (@firstnumber = -1 AND @maxresult = -1)
           OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

    // ── Select List (instrument configs themselves, not instrument TYPE) ──
    public const string GET_SELECTLIST_SURVEYINSTRUMENT = @"
        WITH SurveyInstrumentCTE AS (
            SELECT c.INSTRUMENTCONFIGID AS Id,
                   CAST(c.INSTRUMENTCONFIGID AS VARCHAR(20)) AS Code,
                   c.INSTRUMENTTITLE    AS Name,
                   ROW_NUMBER() OVER (ORDER BY c.INSTRUMENTTITLE) AS RowNum
            FROM   MSURVEYINSTRUMENTCONFIG c
            WHERE  c.TENANTID = @TenantId
            AND    c.STATUS  <> 2 {CRITERIA})
        SELECT Id, Code, Name
        FROM   SurveyInstrumentCTE
        WHERE  (@firstnumber = -1 AND @maxresult = -1)
           OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

    public const string HAS_VIEW_ACCESS = @"
        SELECT COUNT(1)
        FROM   MFLSACCESSRULE AR
        JOIN   MFLSINSTANCE     I ON I.FLSREGISTRATIONID = AR.FLSREGISTRATIONID
        WHERE  I.FLSINSTANCEID = @FlsInstanceId
        AND    AR.ACCESSTYPE   IN (1, 2)           -- 1=ViewSummary, 2=ViewResponses
        AND    AR.TENANTID     = @TenantId
        AND   (
            (AR.PRINCIPALTYPE = 0 AND AR.PRINCIPALID = @UserId)
            OR
            (AR.PRINCIPALTYPE = 1 AND AR.PRINCIPALID = @RoleId)
        )";
}