-- =============================================================================
-- Migration: FLS-as-coordinator support for TMS assessment delivery
--
-- FLS is used here purely as a link/lifecycle coordinator (mint a tokenized respondent
-- link, track opened/submitted) — TMS renders and scores the real assessment via its own
-- existing endpoints, never via FLS's Survey/Instrument renderer. This avoids the
-- question-type/assessment-mode limitations a "project into FLS's Survey model" approach
-- would have hit (FLS has no equivalent for Matching/Ordering question types or
-- Structured/random-selection papers).
--
-- 1. Widens MFLSREGISTRATION.COMPLETIONMETHOD's CHECK constraint to allow a new value 4
--    ("ExternalModule") — none of the existing values (0=Native,1=Webhook,2=Polling,
--    3=Manual) mean "another internal module owns the form and reports completion";
--    reusing 3=Manual would misdescribe an automated module-to-module signal as a human
--    clicking "done" inside FLS's own UI.
-- 2. Seeds one MFORMTEMPLATE row to satisfy MFLSREGISTRATION.FORMTEMPLATEID's NOT NULL FK
--    — shared across every TMS assessment-coordinator registration, since FLS never
--    renders anything here. MODULEID uses MMODULE's real Training row (MODULECODE =
--    'TRAINING', confirmed live — NOT 'TMS' as originally assumed before live verification).
-- 3. TASSESSMENTFLSBRIDGE — maps an FLS respondent (the link) to the TMS attempt it's
--    delivering. AssessmentAttemptId starts at -1 and is filled in when the respondent
--    first opens the link (the attempt is created at that point, mirroring StartAssessmentAttempt,
--    not before — TASSESSMENTATTEMPT.STARTEDON is NOT NULL with no "not yet started" state).
-- 4. Seeds MAUTONUMBER rows for FLSREGISTRATION/FLSINSTANCE/FLSINSTANCEGROUP — confirmed live
--    that FLS's own module has ZERO MAUTONUMBER rows for any of its entities (the one existing
--    MFLSREGISTRATION/MFLSINSTANCE row on TRANSDEV is a manually-seeded -1 sentinel, not real
--    usage) — FlsRegistrationBLL.CreateRegistrationAsync and this coordinator's own raw instance/
--    group inserts would both throw "AutoNumber entry not found" without these.
-- =============================================================================

-- DeliveryChannel distinguishes how an attempt was taken (0 InApp, 1 Fls, 2 Manual) — useful for
-- reporting and is what the manual/offline attempt-entry endpoint (RecordManualAttempt) stamps.
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TASSESSMENTATTEMPT' AND COLUMN_NAME = 'DELIVERYCHANNEL')
BEGIN
    ALTER TABLE DBO.TASSESSMENTATTEMPT ADD DELIVERYCHANNEL TINYINT NOT NULL CONSTRAINT DF_TASSESSMENTATTEMPT_DELIVERYCHANNEL DEFAULT 0;
END
GO

IF EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_MFLSREGISTRATION_COMPLETION')
BEGIN
    ALTER TABLE MFLSREGISTRATION DROP CONSTRAINT CK_MFLSREGISTRATION_COMPLETION;
    ALTER TABLE MFLSREGISTRATION ADD CONSTRAINT CK_MFLSREGISTRATION_COMPLETION CHECK (COMPLETIONMETHOD IN (0,1,2,3,4));
END
GO

IF NOT EXISTS (SELECT 1 FROM MFORMTEMPLATE WHERE FORMTEMPLATECODE = 'TMS_FLS_COORDINATOR')
BEGIN
    INSERT INTO MFORMTEMPLATE
    (
        FORMTEMPLATECODE, FORMTEMPLATENAME, REMARKS, FORMTYPE, MODULEID,
        FORMNATUREID, FORMTEMPLATETYPEID, FORMTEMPLATEPROGRAMID, DATATABLETYPE,
        ENTITYID, FORMTEMPLATEDATA, HEADERPARAMETERSETID, FOOTERPARAMETERSETID,
        SOURCETYPE, SORTORDER, VERSION, STATUS, TENANTID
    )
    VALUES
    (
        'TMS_FLS_COORDINATOR', 'TMS External Assessment Coordinator',
        'Placeholder form template — FLS never renders a form for this registration; TMS delivers and scores the assessment via its own endpoints.',
        0, (SELECT MODULEID FROM MMODULE WHERE MODULECODE = 'TRAINING'),
        -1, -1, -1, 0,
        -1, '', -1, -1,
        5, 9999, 0, 1, -1
    );
END
GO

-- FLS's own missing autonumber seed rows — see note 4 above. ENTITYID values chosen in the
-- same reserved block as this migration's other new ids; verified live against MAUTONUMBER
-- before choosing them (no existing FLS* entity codes found at all).
IF NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'FLSREGISTRATION')
BEGIN
    INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) VALUES (-1388100201, 'FLSREGISTRATION', -1388199998);
END
GO
IF NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'FLSINSTANCE')
BEGIN
    INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) VALUES (-1388100202, 'FLSINSTANCE', -1388199997);
END
GO
IF NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'FLSINSTANCEGROUP')
BEGIN
    INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) VALUES (-1388100203, 'FLSINSTANCEGROUP', -1388199996);
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TASSESSMENTFLSCOORDINATOR')
BEGIN

-- One row per tenant, lazily created on first FLS-delivered assessment dispatch for that tenant.
CREATE TABLE DBO.TASSESSMENTFLSCOORDINATOR
(
    FLSREGISTRATIONID   INT             NOT NULL,
    FLSINSTANCEID       INT             NOT NULL,
    GROUPID             INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_TASSESSMENTFLSCOORDINATOR_CREATEDON DEFAULT GETDATE(),
    TENANTID            INT             NOT NULL,

    CONSTRAINT PK_TASSESSMENTFLSCOORDINATOR_TENANTID PRIMARY KEY (TENANTID)
);

END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TASSESSMENTFLSBRIDGE')
BEGIN

CREATE TABLE DBO.TASSESSMENTFLSBRIDGE
(
    ASSESSMENTFLSBRIDGEID   INT             IDENTITY(1,1) NOT NULL,
    ASSESSMENTID            INT             NOT NULL,
    ENROLLMENTID            INT             NOT NULL,
    FLSRESPONDENTID         INT             NOT NULL,
    -- -1 until the respondent first opens the link (GetFlsAttempt creates the real attempt then).
    ASSESSMENTATTEMPTID     INT             NOT NULL CONSTRAINT DF_TASSESSMENTFLSBRIDGE_ATTEMPTID DEFAULT -1,
    DELIVERYSTATUS          TINYINT         NOT NULL CONSTRAINT DF_TASSESSMENTFLSBRIDGE_DELIVERYSTATUS DEFAULT 0,  -- 0 LinkPending,1 Opened,2 Submitted
    CREATEDON               DATETIME        NOT NULL CONSTRAINT DF_TASSESSMENTFLSBRIDGE_CREATEDON DEFAULT GETDATE(),
    TENANTID                INT             NOT NULL,

    CONSTRAINT PK_TASSESSMENTFLSBRIDGE_ID PRIMARY KEY (ASSESSMENTFLSBRIDGEID),
    CONSTRAINT UQ_TASSESSMENTFLSBRIDGE_RESPONDENTID UNIQUE (FLSRESPONDENTID),
    CONSTRAINT FK_TASSESSMENTFLSBRIDGE_ASSESSMENTID FOREIGN KEY (ASSESSMENTID) REFERENCES DBO.MSESSIONASSESSMENT(ASSESSMENTID),
    CONSTRAINT FK_TASSESSMENTFLSBRIDGE_ENROLLMENTID FOREIGN KEY (ENROLLMENTID) REFERENCES DBO.TTRAININGENROLLMENT(ENROLLMENTID)
);

CREATE INDEX IX_TASSESSMENTFLSBRIDGE_FLSRESPONDENTID ON DBO.TASSESSMENTFLSBRIDGE(FLSRESPONDENTID);

END
GO