SET QUOTED_IDENTIFIER ON

GO

/*
    Post-Deployment Script
*/

-- ───────────────────────────────────────────────────────────────────────────
-- Clip module SPs (new and modified): register procedures and grant to 'admin Profile'.
-- ───────────────────────────────────────────────────────────────────────────

EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetClips', 'admin Profile', 'dexteel'
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetClip', 'admin Profile', 'dexteel'
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.UpsertClip', 'admin Profile', 'dexteel'
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.SetClipPunchSlots', 'admin Profile', 'dexteel'

-- Weldline Overview SP (UI-facing)
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetWeldlineOverview', 'admin Profile', 'dexteel'

-- Punch specs (UI-facing)
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.UpsertPunchSpecs', 'admin Profile', 'dexteel'
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.SetPunchBeamPositions', 'admin Profile', 'dexteel'
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.SetPunchPerforations', 'admin Profile', 'dexteel'

-- ───────────────────────────────────────────────────────────────────────────
-- Weldline Overview: seed catalogs (robots + E-Stops) and register tracking job.
-- Idempotent via MERGE / NOT EXISTS guards.
-- ───────────────────────────────────────────────────────────────────────────

IF EXISTS (SELECT 1 FROM [MES].[catAssets] WHERE [AssetName] = 'Weld165')
   AND OBJECT_ID('FAB.catRobots', 'U') IS NOT NULL
BEGIN
    MERGE [FAB].[catRobots] AS target
    USING (
        SELECT asset.[AssetId], robots.[RobotCode], robots.[DisplayName], robots.[Side], robots.[PositionIndex]
        FROM [MES].[catAssets] asset
        CROSS APPLY (VALUES
            ('R1_E1', 'East 1',  'E', 1),
            ('R2_E2', 'East 2',  'E', 2),
            ('R3_E3', 'East 3',  'E', 3),
            ('R4_W1', 'West 1',  'W', 1),
            ('R5_W2', 'West 2',  'W', 2),
            ('R6_W3', 'West 3',  'W', 3)
        ) AS robots ([RobotCode], [DisplayName], [Side], [PositionIndex])
        WHERE asset.[AssetName] = 'Weld165'
    ) AS source (AssetId, RobotCode, DisplayName, Side, PositionIndex)
    ON target.AssetId = source.AssetId AND target.RobotCode = source.RobotCode
    WHEN MATCHED THEN
        UPDATE SET DisplayName = source.DisplayName, Side = source.Side, PositionIndex = source.PositionIndex
    WHEN NOT MATCHED THEN
        INSERT (AssetId, RobotCode, DisplayName, Side, PositionIndex)
        VALUES (source.AssetId, source.RobotCode, source.DisplayName, source.Side, source.PositionIndex);
END

IF EXISTS (SELECT 1 FROM [MES].[catAssets] WHERE [AssetName] = 'Weld165')
   AND OBJECT_ID('FAB.catEStops', 'U') IS NOT NULL
BEGIN
    MERGE [FAB].[catEStops] AS target
    USING (
        SELECT asset.[AssetId], estops.[EStopCode], estops.[DisplayName], estops.[Category], estops.[ActiveLow], estops.[SortOrder]
        FROM [MES].[catAssets] asset
        CROSS APPLY (VALUES
            -- Destacker
            ('Destacker_East_Estop', 'Destacker East', 'Destacker', CONVERT(BIT, 1),  1),
            ('Destacker_West_Estop', 'Destacker West', 'Destacker', CONVERT(BIT, 1),  2),
            -- Gates
            ('East_Gate1_Estop',     'East Gate 1',    'Gate',      CONVERT(BIT, 1), 10),
            ('East_Gate2_Estop',     'East Gate 2',    'Gate',      CONVERT(BIT, 1), 11),
            ('East_Gate3_Estop',     'East Gate 3',    'Gate',      CONVERT(BIT, 1), 12),
            ('East_Gate4_Estop',     'East Gate 4',    'Gate',      CONVERT(BIT, 1), 13),
            ('West_Gate1_Estop',     'West Gate 1',    'Gate',      CONVERT(BIT, 1), 14),
            ('West_Gate2_Estop',     'West Gate 2',    'Gate',      CONVERT(BIT, 1), 15),
            ('West_Gate3_Estop',     'West Gate 3',    'Gate',      CONVERT(BIT, 1), 16),
            ('West_Gate4_Estop',     'West Gate 4',    'Gate',      CONVERT(BIT, 1), 17),
            -- Weld stations
            ('East_Weld_Estop',      'East Weld',      'Weld',      CONVERT(BIT, 1), 20),
            ('West_Weld_Estop',      'West Weld',      'Weld',      CONVERT(BIT, 1), 21),
            -- Stacker
            ('Stacker_Estop',        'Stacker',        'Stacker',   CONVERT(BIT, 1), 30),
            -- Hydraulics (active HIGH)
            ('Hyd_Estop',            'Hydraulic System', 'Hyd',     CONVERT(BIT, 0), 40),
            -- System (active HIGH for System; LOW for Safety)
            ('System_Estop',         'System',         'System',    CONVERT(BIT, 0), 50),
            ('System_Safety_Estop',  'System Safety',  'System',    CONVERT(BIT, 1), 51)
        ) AS estops ([EStopCode], [DisplayName], [Category], [ActiveLow], [SortOrder])
        WHERE asset.[AssetName] = 'Weld165'
    ) AS source (AssetId, EStopCode, DisplayName, Category, ActiveLow, SortOrder)
    ON target.AssetId = source.AssetId AND target.EStopCode = source.EStopCode
    WHEN MATCHED THEN
        UPDATE SET
            DisplayName = source.DisplayName,
            Category = source.Category,
            ActiveLow = source.ActiveLow,
            SortOrder = source.SortOrder
    WHEN NOT MATCHED THEN
        INSERT (AssetId, EStopCode, DisplayName, Category, ActiveLow, SortOrder)
        VALUES (source.AssetId, source.EStopCode, source.DisplayName, source.Category, source.ActiveLow, source.SortOrder);
END

-- Register the weldline tracking job. Fully idempotent: every deploy resets all
-- configuration columns including Enabled (no manual overrides survive).
MERGE [SYSTEM].[catJobs] AS target
USING (VALUES
    ('FAB.TrackWeldLineJob', 10, CONVERT(BIT, 1), CONVERT(BIT, 0))
) AS source ([sProcedure], [PeriodTime], [RestartAfterFailure], [Enabled])
    ON target.[sProcedure] = source.[sProcedure]
WHEN MATCHED THEN UPDATE SET
    [PeriodTime]          = source.[PeriodTime],
    [RestartAfterFailure] = source.[RestartAfterFailure],
    [Enabled]             = source.[Enabled]
WHEN NOT MATCHED THEN
    INSERT ([sProcedure], [StartTime], [PeriodTime], [RestartAfterFailure], [Enabled])
    VALUES (source.[sProcedure], DATEADD(MINUTE, 1, GETDATE()), source.[PeriodTime], source.[RestartAfterFailure], source.[Enabled]);

GO

-- ───────────────────────────────────────────────────────────────────────────
-- One-shot data migration for the UTC + tracking refactor.
--
-- This deploy ships two coupled changes:
--   1. TrackBundlesJob/TrackDelaysJob now write true UTC (the legacy
--      `-3 * 3600 * 1000` offset is gone).
--   2. TrackBundlesJob now supports open bundles/layers/batches and
--      changes how Bars→Layers→Bundles aggregates are computed.
--
-- Strategy:
--   • Rows OLDER than NOW-7d  → shift StartTime/EndTime/Timestamp by +3h
--                                (fixes the legacy timezone bug).
--   • Rows within last 7d     → DELETE and re-run the new tracking jobs
--                                (historian retention window). Re-tracking
--                                regenerates the data using the new logic.
--
-- Safety guards:
--   • TZ repair aborts if any old row already looks fixed (drift < 9000s
--     between event timestamp and CreatedAt). Prevents double-apply.
--   • Delete + re-track is idempotent: a second deploy finds nothing to
--     delete (CreatedAt now > NOW-7d on the regenerated rows) and the
--     jobs themselves dedupe via TrackingId / unique index.
--   • All failures are caught and logged — they must NOT block the deploy.
-- ───────────────────────────────────────────────────────────────────────────

IF OBJECT_ID('FAB.Bundles', 'U') IS NOT NULL
   AND OBJECT_ID('FAB.Batches', 'U') IS NOT NULL
   AND OBJECT_ID('FAB.Layers',  'U') IS NOT NULL
   AND OBJECT_ID('FAB.Bars',    'U') IS NOT NULL
   AND OBJECT_ID('DEL.Delays',  'U') IS NOT NULL
   AND OBJECT_ID('FAB.TrackBundlesJob', 'P') IS NOT NULL
   AND OBJECT_ID('FAB.TrackDelaysJob',  'P') IS NOT NULL
BEGIN
    BEGIN TRY
        SET XACT_ABORT ON;

        -- Historian database name — change per environment.
        --   Local:   historian_swv_fab
        --   Staging: (staging historian db)
        --   Prod:    (prod historian db)
        DECLARE @HistorianDb SYSNAME = 'mini-historian';

        DECLARE @MigNow           DATETIME = GETUTCDATE();
        DECLARE @ReTrackStart     DATETIME = DATEADD(DAY, -7, @MigNow);
        DECLARE @ShiftHours       INT      = 3;
        DECLARE @BugDriftSec      INT      = 9000;   -- < 2.5h drift = looks already-fixed

        PRINT '=== TZ + Tracking Migration: START ===';
        PRINT '  Re-track window: ['
            + CONVERT(VARCHAR(30), @ReTrackStart, 121) + ' .. '
            + CONVERT(VARCHAR(30), @MigNow, 121) + ')';

        ----------------------------------------------------------------
        -- PART 1: TZ repair (+3h) for rows older than the re-track window.
        --
        -- Detect "already-fixed" rows in the older partition. If any exist,
        -- skip the +3h update — either the migration already ran, or rows
        -- look post-fix and another +3h would over-shift them.
        ----------------------------------------------------------------
        DECLARE @AlreadyFixed INT = (
            SELECT SUM(cnt) FROM (
                SELECT cnt = COUNT(*) FROM FAB.Bundles
                WHERE CreatedAt < @ReTrackStart
                  AND DATEDIFF(SECOND, StartTime, CreatedAt) < @BugDriftSec
                UNION ALL
                SELECT COUNT(*) FROM FAB.Batches
                WHERE CreatedAt < @ReTrackStart
                  AND DATEDIFF(SECOND, StartTime, CreatedAt) < @BugDriftSec
                UNION ALL
                SELECT COUNT(*) FROM FAB.Layers
                WHERE CreatedAt < @ReTrackStart
                  AND DATEDIFF(SECOND, StartTime, CreatedAt) < @BugDriftSec
                UNION ALL
                SELECT COUNT(*) FROM FAB.Bars
                WHERE CreatedAt < @ReTrackStart
                  AND DATEDIFF(SECOND, [Timestamp], CreatedAt) < @BugDriftSec
                UNION ALL
                SELECT COUNT(*) FROM DEL.Delays d
                JOIN DEL.PreJustification pj ON pj.DelayId = d.DelayId
                WHERE pj.CreatedAt < @ReTrackStart
                  AND DATEDIFF(SECOND, d.[Start], pj.CreatedAt) < @BugDriftSec
            ) x
        );

        IF ISNULL(@AlreadyFixed, 0) > 0
        BEGIN
            PRINT '  [PART 1] TZ repair SKIPPED — '
                + CAST(@AlreadyFixed AS VARCHAR(20))
                + ' rows look already-fixed.';
        END
        ELSE
        BEGIN
            BEGIN TRANSACTION;

            DECLARE @TzBundles INT, @TzBatches INT, @TzLayers INT, @TzBars INT, @TzDelays INT;

            UPDATE FAB.Bundles
            SET StartTime = DATEADD(HOUR, @ShiftHours, StartTime),
                EndTime   = DATEADD(HOUR, @ShiftHours, EndTime)
            WHERE CreatedAt < @ReTrackStart;
            SET @TzBundles = @@ROWCOUNT;

            UPDATE FAB.Batches
            SET StartTime = DATEADD(HOUR, @ShiftHours, StartTime),
                EndTime   = DATEADD(HOUR, @ShiftHours, EndTime)
            WHERE CreatedAt < @ReTrackStart;
            SET @TzBatches = @@ROWCOUNT;

            UPDATE FAB.Layers
            SET StartTime = DATEADD(HOUR, @ShiftHours, StartTime),
                EndTime   = DATEADD(HOUR, @ShiftHours, EndTime)
            WHERE CreatedAt < @ReTrackStart;
            SET @TzLayers = @@ROWCOUNT;

            UPDATE FAB.Bars
            SET [Timestamp] = DATEADD(HOUR, @ShiftHours, [Timestamp])
            WHERE CreatedAt < @ReTrackStart;
            SET @TzBars = @@ROWCOUNT;

            UPDATE d
            SET [Start] = DATEADD(HOUR, @ShiftHours, d.[Start]),
                [End]   = DATEADD(HOUR, @ShiftHours, d.[End])
            FROM DEL.Delays d
            JOIN DEL.PreJustification pj ON pj.DelayId = d.DelayId
            WHERE pj.CreatedAt < @ReTrackStart;
            SET @TzDelays = @@ROWCOUNT;

            COMMIT TRANSACTION;

            PRINT '  [PART 1] +3h applied: '
                + 'Bundles='  + CAST(@TzBundles AS VARCHAR(20))
                + ', Batches=' + CAST(@TzBatches AS VARCHAR(20))
                + ', Layers='  + CAST(@TzLayers AS VARCHAR(20))
                + ', Bars='    + CAST(@TzBars AS VARCHAR(20))
                + ', Delays='  + CAST(@TzDelays AS VARCHAR(20));
        END;

        ----------------------------------------------------------------
        -- PART 2: Delete the last-7d window so the new tracking logic
        -- can regenerate it from historian.
        --
        -- FK-safe delete order: Bars → Layers → Bundles → Batches.
        -- DEL.PreJustification has FK to DEL.Delays → delete it first.
        -- (No operator-justified delays exist yet — confirmed with user.)
        ----------------------------------------------------------------
        BEGIN TRANSACTION;

        DECLARE @DelBars INT, @DelLayers INT, @DelBundles INT, @DelBatches INT,
                @DelPJust INT, @DelDelays INT;

        DELETE FROM FAB.Bars   WHERE CreatedAt >= @ReTrackStart;
        SET @DelBars = @@ROWCOUNT;

        DELETE FROM FAB.Layers WHERE CreatedAt >= @ReTrackStart;
        SET @DelLayers = @@ROWCOUNT;

        DELETE FROM FAB.Bundles WHERE CreatedAt >= @ReTrackStart;
        SET @DelBundles = @@ROWCOUNT;

        DELETE FROM FAB.Batches WHERE CreatedAt >= @ReTrackStart;
        SET @DelBatches = @@ROWCOUNT;

        DELETE pj
        FROM DEL.PreJustification pj
        JOIN DEL.Delays d ON d.DelayId = pj.DelayId
        WHERE d.[Start] >= @ReTrackStart;
        SET @DelPJust = @@ROWCOUNT;

        DELETE FROM DEL.Delays WHERE [Start] >= @ReTrackStart;
        SET @DelDelays = @@ROWCOUNT;

        COMMIT TRANSACTION;

        PRINT '  [PART 2] Last-7d deleted: '
            + 'Bars='     + CAST(@DelBars     AS VARCHAR(20))
            + ', Layers='  + CAST(@DelLayers   AS VARCHAR(20))
            + ', Bundles=' + CAST(@DelBundles  AS VARCHAR(20))
            + ', Batches=' + CAST(@DelBatches  AS VARCHAR(20))
            + ', PreJust=' + CAST(@DelPJust    AS VARCHAR(20))
            + ', Delays='  + CAST(@DelDelays   AS VARCHAR(20));

        ----------------------------------------------------------------
        -- Reseed IDENTITY counters so re-tracking reuses the freed IDs
        -- instead of continuing from the high-water mark. Without this,
        -- deleting rows 6,7 and re-tracking would create 8,9 — leaving
        -- permanent gaps where 6,7 used to be. DBCC CHECKIDENT(t,RESEED,N)
        -- sets the seed to N; the next IDENTITY value generated is N+1.
        -- ISNULL(MAX(),0) handles empty tables (next insert = 1).
        ----------------------------------------------------------------
        DECLARE @ReseedBars    INT, @ReseedLayers  INT, @ReseedBundles INT,
                @ReseedBatches INT, @ReseedDelays  INT;
        SELECT @ReseedBars    = ISNULL(MAX(BarId),    0) FROM FAB.Bars;
        SELECT @ReseedLayers  = ISNULL(MAX(LayerId),  0) FROM FAB.Layers;
        SELECT @ReseedBundles = ISNULL(MAX(BundleId), 0) FROM FAB.Bundles;
        SELECT @ReseedBatches = ISNULL(MAX(BatchId),  0) FROM FAB.Batches;
        SELECT @ReseedDelays  = ISNULL(MAX(DelayId),  0) FROM DEL.Delays;

        DBCC CHECKIDENT('FAB.Bars',    RESEED, @ReseedBars)    WITH NO_INFOMSGS;
        DBCC CHECKIDENT('FAB.Layers',  RESEED, @ReseedLayers)  WITH NO_INFOMSGS;
        DBCC CHECKIDENT('FAB.Bundles', RESEED, @ReseedBundles) WITH NO_INFOMSGS;
        DBCC CHECKIDENT('FAB.Batches', RESEED, @ReseedBatches) WITH NO_INFOMSGS;
        DBCC CHECKIDENT('DEL.Delays',  RESEED, @ReseedDelays)  WITH NO_INFOMSGS;

        PRINT '  [PART 2b] IDENTITY reseeded: '
            + 'Bars→'    + CAST(@ReseedBars    AS VARCHAR(20))
            + ', Layers→' + CAST(@ReseedLayers  AS VARCHAR(20))
            + ', Bundles→'+ CAST(@ReseedBundles AS VARCHAR(20))
            + ', Batches→'+ CAST(@ReseedBatches AS VARCHAR(20))
            + ', Delays→' + CAST(@ReseedDelays  AS VARCHAR(20));

        ----------------------------------------------------------------
        -- PART 3: Re-run tracking jobs for the deleted window.
        -- Each job is wrapped in its own TRY/CATCH — a job failure must
        -- not block the deploy (scheduled jobs will eventually backfill).
        ----------------------------------------------------------------
        -- Dynamic SQL: T-SQL doesn't allow variable-based DB identifiers in
        -- static SQL, so we build the call with @HistorianDb concatenated.
        DECLARE @StartMs BIGINT, @EndMs BIGINT;
        DECLARE @MsSql NVARCHAR(MAX) = N'
            SELECT @s = [' + @HistorianDb + N'].IH.Date2Ms(@a),
                   @e = [' + @HistorianDb + N'].IH.Date2Ms(@b);';
        EXEC sp_executesql @MsSql,
            N'@a DATETIME, @b DATETIME, @s BIGINT OUTPUT, @e BIGINT OUTPUT',
            @a = @ReTrackStart, @b = @MigNow,
            @s = @StartMs OUTPUT, @e = @EndMs OUTPUT;

        PRINT '  [PART 3] Re-tracking offsets: ['
            + CAST(@StartMs AS VARCHAR(20)) + ' .. '
            + CAST(@EndMs   AS VARCHAR(20)) + ']';

        BEGIN TRY
            EXEC [FAB].[TrackBundlesJob] @StartOffset = @StartMs, @EndOffset = @EndMs;
            PRINT '  [PART 3] TrackBundlesJob OK';
        END TRY
        BEGIN CATCH
            PRINT '  [PART 3] TrackBundlesJob FAILED: ' + ERROR_MESSAGE();
        END CATCH

        BEGIN TRY
            EXEC [FAB].[TrackDelaysJob] @StartOffset = @StartMs, @EndOffset = @EndMs;
            PRINT '  [PART 3] TrackDelaysJob OK';
        END TRY
        BEGIN CATCH
            PRINT '  [PART 3] TrackDelaysJob FAILED: ' + ERROR_MESSAGE();
        END CATCH

        PRINT '=== TZ + Tracking Migration: COMPLETE ===';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        PRINT '=== TZ + Tracking Migration FAILED: ' + ERROR_MESSAGE() + ' ===';
    END CATCH
END

-- Run tracking jobs once after deployment. Best effort only: failures should
-- be visible but must not block the deploy.
BEGIN TRY
    EXEC [FAB].[TrackWeldLineJob];
END TRY
BEGIN CATCH
    PRINT 'PostDeployment tracking job FAB.TrackWeldLineJob FAILED: ' + ERROR_MESSAGE();
END CATCH

BEGIN TRY
    EXEC [FAB].[TrackDelaysJob];
END TRY
BEGIN CATCH
    PRINT 'PostDeployment tracking job FAB.TrackDelaysJob FAILED: ' + ERROR_MESSAGE();
END CATCH

GO

