SET QUOTED_IDENTIFIER ON

GO

/*==============================================================================================
  LAB mechanical tests — split the test RUN out of LAB.HeatMechanicalResults.

  Starting shape (what the deployed databases actually hold):
    LAB.HeatMechanicalResults    one flat row per specimen, carrying HeatId, SectionId and the
                                 lab timestamps; MechanicalSampleId is the swv-mes specimen id
    LAB.HeatTestDispositions     the P/R/U history, keyed by (HeatId, Timestamp)
    LAB.HeatSampleStatus         same columns as the above, never read by anything

  Target shape (see lab-sql/LAB/Tables/):
    LAB.MechanicalTestRuns             one row per lab file: heat, section, reception, reported
    LAB.HeatMechanicalResults          one row per specimen: nothing but the measured values
    LAB.MechanicalTestRunDispositions  replaces LAB.HeatTestDispositions, keyed by run
  Retired: LAB.HeatTestDispositions, LAB.HeatSampleStatus.

  MUST run before the schema step, and it must leave LAB.HeatMechanicalResults ALREADY in the
  target shape, for two independent reasons:

    1. Dropping HeatId/SectionId/Reception/Reported/Processed is a data-loss operation, and the
       publish runs with BlockOnPossibleDataLoss on. The schema step would refuse the change.
    2. MechanicalTestRunId is NOT NULL with no default: the plan cannot add it to a table that
       already has rows. It is added NULL here, populated, and only then made NOT NULL.

  The publish also adds foreign keys WITH NOCHECK and revalidates every one of them with
  WITH CHECK CHECK CONSTRAINT at the END of the schema script -- before the post-deployment
  script. So by the time the plan checks FK_LAB_HeatMechanicalResults_MechanicalTestRuns the
  parents already have to be there, or the publish dies with Msg 547.

  The new tables are created here WITHOUT their foreign keys on purpose: the deployment plan adds
  them, and creating them here too would collide with that ADD CONSTRAINT. Columns and indexes
  must stay identical to the table scripts -- the schema step compares them right after, and any
  drift shows up as an unexpected ALTER.

  Every step is guarded on the shape it is about to change, so the script is idempotent: re-running
  it is a no-op and a half-applied publish is repaired on the next run. Statements that touch a
  column which may or may not exist go through EXEC() -- an IF around them is not enough, since
  SQL Server resolves column names when it compiles the batch, not when it runs it.
==============================================================================================*/

-- ---------------------------------------------------------------------------------------------
-- 1. LAB.MechanicalTestRuns
-- ---------------------------------------------------------------------------------------------
IF OBJECT_ID('LAB.MechanicalTestRuns', 'U') IS NULL
BEGIN
    PRINT 'PreDeployment: creating LAB.MechanicalTestRuns';

    CREATE TABLE [LAB].[MechanicalTestRuns] (
        [MechanicalTestRunId] INT NOT NULL IDENTITY(1,1),
        [HeatId]              INT NOT NULL,
        [Reception]           DATETIME NOT NULL,
        [SectionId]           INT NULL,
        [Reported]            DATETIME NULL,
        [SyncedAt]            DATETIME NOT NULL DEFAULT(GETUTCDATE()),
        [IsActive]            BIT NOT NULL DEFAULT(1),
        CONSTRAINT [PK_LAB_MechanicalTestRuns] PRIMARY KEY CLUSTERED ([MechanicalTestRunId] ASC)
    );

    CREATE NONCLUSTERED INDEX [IX_LAB_MechanicalTestRuns_HeatId]
    ON [LAB].[MechanicalTestRuns] ([HeatId] ASC)
    WHERE [IsActive] = 1;

    CREATE UNIQUE NONCLUSTERED INDEX [UX_LAB_MechanicalTestRuns_Heat_Reception_Section]
    ON [LAB].[MechanicalTestRuns] ([HeatId] ASC, [Reception] ASC, [SectionId] ASC)
    WHERE [IsActive] = 1;
END

GO

-- ---------------------------------------------------------------------------------------------
-- 2. LAB.MechanicalTestRunDispositions
-- ---------------------------------------------------------------------------------------------
IF OBJECT_ID('LAB.MechanicalTestRunDispositions', 'U') IS NULL
BEGIN
    PRINT 'PreDeployment: creating LAB.MechanicalTestRunDispositions';

    CREATE TABLE [LAB].[MechanicalTestRunDispositions] (
        [MechanicalTestRunDispositionId] INT NOT NULL IDENTITY(1,1),
        [MechanicalTestRunId]            INT NOT NULL,
        [Disposition]                    CHAR(1) NOT NULL,
        [DisposedBy]                     VARCHAR(50) NOT NULL,
        [DisposedAt]                     DATETIME NOT NULL DEFAULT(GETUTCDATE()),
        [IsActive]                       BIT NOT NULL DEFAULT(1),
        CONSTRAINT [PK_LAB_MechanicalTestRunDispositions] PRIMARY KEY CLUSTERED ([MechanicalTestRunDispositionId] ASC),
        CONSTRAINT [CK_LAB_MechanicalTestRunDispositions_Disposition] CHECK ([Disposition] IN ('P', 'R', 'U'))
    );

    CREATE NONCLUSTERED INDEX [IX_LAB_MechanicalTestRunDispositions_Run_DisposedAt]
    ON [LAB].[MechanicalTestRunDispositions] ([MechanicalTestRunId] ASC, [DisposedAt] DESC)
    WHERE [IsActive] = 1;
END

GO

-- ---------------------------------------------------------------------------------------------
-- 3. Backfill the runs from the results that are about to lose those columns.
--    One run per (HeatId, Reception, SectionId) -- the same grouping the front end was doing by
--    hand on the reception timestamp, now recorded once, plus the section so a lab file that
--    mixed two of them becomes two runs instead of one that keeps a section at random.
--    A run is active when at least one of its specimens is, and was synced when the first of
--    them was -- that SyncedAt is what anchors the disposition migration in step 7.
--    Reception is NOT NULL on the run: a specimen without one cannot say which file it came in,
--    so it gets no run and step 6 stops the deployment rather than guessing.
-- ---------------------------------------------------------------------------------------------
IF COL_LENGTH('LAB.HeatMechanicalResults', 'MechanicalTestRunId') IS NULL
   AND COL_LENGTH('LAB.HeatMechanicalResults', 'HeatId') IS NOT NULL
BEGIN
    EXEC('
        INSERT INTO [LAB].[MechanicalTestRuns]
            ([HeatId], [Reception], [SectionId], [Reported], [SyncedAt], [IsActive])
        SELECT
            hmr.[HeatId],
            hmr.[Reception],
            hmr.[SectionId],
            MAX(hmr.[Reported]),
            MIN(hmr.[SyncedAt]),
            MAX(CAST(hmr.[IsActive] AS INT))
        FROM [LAB].[HeatMechanicalResults] hmr
        WHERE hmr.[Reception] IS NOT NULL
          AND NOT EXISTS (
            SELECT 1 FROM [LAB].[MechanicalTestRuns] run
            WHERE run.[HeatId] = hmr.[HeatId]
              AND run.[Reception] = hmr.[Reception]
              AND (run.[SectionId] = hmr.[SectionId]
                   OR (run.[SectionId] IS NULL AND hmr.[SectionId] IS NULL))
        )
        GROUP BY hmr.[HeatId], hmr.[Reception], hmr.[SectionId];

        PRINT ''PreDeployment: backfilled '' + CAST(@@ROWCOUNT AS VARCHAR(10)) + '' mechanical test run(s)'';
    ');
END

GO

-- ---------------------------------------------------------------------------------------------
-- 4. The two new columns on LAB.HeatMechanicalResults.
--    MechanicalTestRunId goes in NULL and is tightened in step 6, once it is populated.
-- ---------------------------------------------------------------------------------------------
IF COL_LENGTH('LAB.HeatMechanicalResults', 'MechanicalTestRunId') IS NULL
BEGIN
    PRINT 'PreDeployment: adding LAB.HeatMechanicalResults.MechanicalTestRunId';
    ALTER TABLE [LAB].[HeatMechanicalResults] ADD [MechanicalTestRunId] INT NULL;
END

GO

IF COL_LENGTH('LAB.HeatMechanicalResults', 'IsManual') IS NULL
BEGIN
    PRINT 'PreDeployment: adding LAB.HeatMechanicalResults.IsManual';
    ALTER TABLE [LAB].[HeatMechanicalResults] ADD [IsManual] BIT NOT NULL DEFAULT(0);
END

GO

-- ---------------------------------------------------------------------------------------------
-- 5. Point every specimen at its run
-- ---------------------------------------------------------------------------------------------
IF COL_LENGTH('LAB.HeatMechanicalResults', 'HeatId') IS NOT NULL
BEGIN
    EXEC('
        UPDATE hmr
        SET hmr.[MechanicalTestRunId] = run.[MechanicalTestRunId]
        FROM [LAB].[HeatMechanicalResults] hmr
        INNER JOIN [LAB].[MechanicalTestRuns] run
            ON run.[HeatId] = hmr.[HeatId]
           AND run.[Reception] = hmr.[Reception]
           AND (run.[SectionId] = hmr.[SectionId]
                OR (run.[SectionId] IS NULL AND hmr.[SectionId] IS NULL))
        WHERE hmr.[MechanicalTestRunId] IS NULL;

        PRINT ''PreDeployment: attached '' + CAST(@@ROWCOUNT AS VARCHAR(10)) + '' result(s) to their run'';
    ');
END

GO

-- ---------------------------------------------------------------------------------------------
-- 6. Tighten MechanicalTestRunId to NOT NULL, which is the shape the plan expects.
--    Step 3 creates a run for every group of results, so an orphan here means the backfill was
--    interfered with; stopping is better than dropping the columns that would let us retry.
-- ---------------------------------------------------------------------------------------------
IF EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID('LAB.HeatMechanicalResults')
      AND name = 'MechanicalTestRunId'
      AND is_nullable = 1
)
BEGIN
    EXEC('
        DECLARE @Orphans INT = (SELECT COUNT(*) FROM [LAB].[HeatMechanicalResults] WHERE [MechanicalTestRunId] IS NULL);
        IF @Orphans > 0
        BEGIN
            DECLARE @Msg VARCHAR(300) = ''PreDeployment: '' + CAST(@Orphans AS VARCHAR(10))
                + '' result(s) have no MechanicalTestRunId. The usual cause is a NULL Reception, which gives no run to attach them to; resolve them by hand before publishing.'';
            THROW 50000, @Msg, 1;
        END

        ALTER TABLE [LAB].[HeatMechanicalResults] ALTER COLUMN [MechanicalTestRunId] INT NOT NULL;
        PRINT ''PreDeployment: LAB.HeatMechanicalResults.MechanicalTestRunId is now NOT NULL'';
    ');
END

GO

-- ---------------------------------------------------------------------------------------------
-- 7. Migrate the heat-level dispositions onto the runs.
--
--    Do NOT reproduce what the front end does today. dispositionGroups.ts pairs runs with
--    dispositions BY INDEX, because the read procedure never gave it a timestamp to pair on, and
--    that pairing is known to slip: the run count and the automatic-disposition count disagree on
--    a real share of the heats. The migration has better information than the screen ever had.
--
--    Automatic ones: the job writes a heat's specimens and then its 'auto' disposition in the same
--    execution, seconds apart. So the run whose SyncedAt is closest to the disposition's timestamp
--    IS the run it judged -- that is a fact about how the rows were written, not a heuristic, and
--    it makes the index mismatches irrelevant since they came from counting, not from time.
--
--    Manual ones: with the automatic ones anchored to real runs, a manual decision belongs to the
--    run that was current when it was taken -- the run of the last automatic disposition at or
--    before it. On production that covers 164 of the 351 manual rows; the other 187 predate any
--    automatic disposition of their heat (the P/R/U checkbox existed before the sync job did), so
--    they fall back to the run itself: the most recent one already synced when the decision was
--    taken, or the earliest run of the heat if the decision predates all of them. That recovers
--    105 more, and it is the same reading -- "which lab file was on screen at that moment" -- just
--    read off the runs instead of off an automatic disposition that is not there.
--
--    The 82 that remain are on heats that have no results at all, so there is no run to point at
--    under any rule. They are counted below and stay readable in LAB.HeatTestDispositions until
--    step 9 drops the table.
-- ---------------------------------------------------------------------------------------------
IF OBJECT_ID('LAB.HeatTestDispositions', 'U') IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM [LAB].[MechanicalTestRunDispositions])
BEGIN
    EXEC('
        -- Automatic: nearest run by write time
        INSERT INTO [LAB].[MechanicalTestRunDispositions]
            ([MechanicalTestRunId], [Disposition], [DisposedBy], [DisposedAt], [IsActive])
        SELECT run.[MechanicalTestRunId], d.[SampleStatusCode], d.[User], d.[Timestamp], d.[IsActive]
        FROM [LAB].[HeatTestDispositions] d
        CROSS APPLY (
            SELECT TOP 1 r.[MechanicalTestRunId]
            FROM [LAB].[MechanicalTestRuns] r
            WHERE r.[HeatId] = d.[HeatId]
            ORDER BY ABS(DATEDIFF(SECOND, r.[SyncedAt], d.[Timestamp])), r.[MechanicalTestRunId]
        ) run
        WHERE LOWER(LTRIM(RTRIM(d.[User]))) = ''auto'';

        DECLARE @Autos INT = @@ROWCOUNT;

        -- Manual: the run that the last automatic disposition before it was judging, and when the
        -- heat has none, the run that was current at that moment.
        INSERT INTO [LAB].[MechanicalTestRunDispositions]
            ([MechanicalTestRunId], [Disposition], [DisposedBy], [DisposedAt], [IsActive])
        SELECT ISNULL(prev.[MechanicalTestRunId], bytime.[MechanicalTestRunId]),
               d.[SampleStatusCode], d.[User], d.[Timestamp], d.[IsActive]
        FROM [LAB].[HeatTestDispositions] d
        OUTER APPLY (
            SELECT TOP 1 a.[MechanicalTestRunId]
            FROM [LAB].[MechanicalTestRunDispositions] a
            INNER JOIN [LAB].[MechanicalTestRuns] r ON r.[MechanicalTestRunId] = a.[MechanicalTestRunId]
            WHERE r.[HeatId] = d.[HeatId]
              AND a.[DisposedBy] = ''auto''
              AND a.[DisposedAt] <= d.[Timestamp]
            ORDER BY a.[DisposedAt] DESC, a.[MechanicalTestRunDispositionId] DESC
        ) prev
        OUTER APPLY (
            -- Runs already synced when the decision was taken win, most recent first; if there are
            -- none the decision predates every run of the heat and the earliest one is the target.
            SELECT TOP 1 r.[MechanicalTestRunId]
            FROM [LAB].[MechanicalTestRuns] r
            WHERE r.[HeatId] = d.[HeatId]
            ORDER BY CASE WHEN r.[SyncedAt] <= d.[Timestamp] THEN 0 ELSE 1 END,
                     CASE WHEN r.[SyncedAt] <= d.[Timestamp] THEN r.[SyncedAt] END DESC,
                     r.[SyncedAt] ASC,
                     r.[MechanicalTestRunId]
        ) bytime
        WHERE LOWER(LTRIM(RTRIM(d.[User]))) <> ''auto''
          AND ISNULL(prev.[MechanicalTestRunId], bytime.[MechanicalTestRunId]) IS NOT NULL;

        DECLARE @Manuals INT = @@ROWCOUNT;
        DECLARE @Total INT = (SELECT COUNT(*) FROM [LAB].[HeatTestDispositions]);

        PRINT ''PreDeployment: migrated '' + CAST(@Autos AS VARCHAR(10)) + '' automatic and ''
            + CAST(@Manuals AS VARCHAR(10)) + '' manual disposition(s) onto their run (of '' + CAST(@Total AS VARCHAR(10)) + '')'';

        IF @Total - @Autos - @Manuals > 0
            PRINT ''PreDeployment: '' + CAST(@Total - @Autos - @Manuals AS VARCHAR(10))
                + '' disposition(s) had no run to anchor to and were NOT migrated -- review them by hand'';
    ');
END

GO

-- ---------------------------------------------------------------------------------------------
-- 8. Drop what moved to the run. The indexes and foreign keys go first: a column cannot be
--    dropped while one of them still references it.
--
--    Every foreign key of the table that touches one of those columns is dropped, found through
--    sys.foreign_key_columns rather than by name. Names are not to be trusted here: an FK lives in
--    the schema of its table, so OBJECT_ID('FK_...', 'F') unqualified resolves against the
--    caller's default schema (dbo), misses the LAB one, and the drop silently does nothing --
--    which then surfaces much later as a data-loss guard in the publish. Going through the
--    catalog also catches any extra FK a hand-fix may have left behind.
-- ---------------------------------------------------------------------------------------------
DECLARE @DropFks NVARCHAR(MAX) = N'';

SELECT @DropFks = @DropFks
    + N'ALTER TABLE [LAB].[HeatMechanicalResults] DROP CONSTRAINT [' + fk.name + N'];' + CHAR(10)
FROM sys.foreign_keys fk
WHERE fk.parent_object_id = OBJECT_ID('LAB.HeatMechanicalResults')
  AND EXISTS (
      SELECT 1
      FROM sys.foreign_key_columns fkc
      INNER JOIN sys.columns c
          ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
      WHERE fkc.constraint_object_id = fk.object_id
        AND c.name IN ('HeatId', 'SectionId')
  );

IF @DropFks <> N''
BEGIN
    PRINT 'PreDeployment: ' + REPLACE(@DropFks, CHAR(10), ' ');
    EXEC sp_executesql @DropFks;
END

IF EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_LAB_HeatMechanicalResults_HeatId'
      AND object_id = OBJECT_ID('LAB.HeatMechanicalResults')
)
    DROP INDEX [IX_LAB_HeatMechanicalResults_HeatId] ON [LAB].[HeatMechanicalResults];

GO

DECLARE @Drop NVARCHAR(MAX) = N'';

SELECT @Drop = @Drop + N', [' + c.name + N']'
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('LAB.HeatMechanicalResults')
  AND c.name IN ('HeatId', 'SectionId', 'Reception', 'Reported', 'Processed');

IF @Drop <> N''
BEGIN
    SET @Drop = N'ALTER TABLE [LAB].[HeatMechanicalResults] DROP COLUMN ' + STUFF(@Drop, 1, 2, N'');
    PRINT 'PreDeployment: ' + @Drop;
    EXEC sp_executesql @Drop;
END

GO

-- ---------------------------------------------------------------------------------------------
-- 9. Retire the tables the run replaced. The publish does not drop objects that left the
--     project (DropObjectsNotInSource is off), so they have to go explicitly or they linger.
-- ---------------------------------------------------------------------------------------------
IF OBJECT_ID('LAB.HeatTestDispositions', 'U') IS NOT NULL
BEGIN
    PRINT 'PreDeployment: dropping LAB.HeatTestDispositions';
    DROP TABLE [LAB].[HeatTestDispositions];
END

-- Never referenced by a procedure or a screen; a stale duplicate of HeatTestDispositions.
IF OBJECT_ID('LAB.HeatSampleStatus', 'U') IS NOT NULL
BEGIN
    PRINT 'PreDeployment: dropping LAB.HeatSampleStatus';
    DROP TABLE [LAB].[HeatSampleStatus];
END

GO
