SET QUOTED_IDENTIFIER ON

GO


/*
    Post-Deployment Script for DEL Schema

    Fully idempotent - safe to run multiple times.
    Uses MERGE with INSERT + UPDATE for all data.

    Order of execution:
    1. Settings (TimeZone, DefaultAssetId, DefaultAreaId)
    2. Users (SEC.catUsers + SEC.catUsersProfiles)
    3. Assets (MES.catAssets + DEL.catAssetsExtension)
    4. Shift Parameters + Generate Shifts
    5. Delay Categories
    6. Delay Codes (with IsSelectable/IsEditable sync)
    7. Register Tracking Jobs (SYSTEM.catJobs)
*/

SET NOCOUNT ON;

-- ============================================================
-- CONFIGURATION - MODIFY THESE VALUES AS NEEDED
-- ============================================================
DECLARE @GrantAdminPermissions BIT = 1;  -- Set to 1 to grant all SP permissions to admin profile

-- Asset IDs (Convention: Plant=1, Areas=10-99, Machines=10+N)
DECLARE @PlantAssetId INT = 1;       -- Root asset
DECLARE @WeldsAreaId INT = 10;       -- DelayArea for all welds
DECLARE @Weld165AssetId INT = 11;    -- DelayAsset for Weld165
-- ============================================================

PRINT '============================================================';
PRINT 'Post-Deployment Script - DEL Schema';
PRINT 'Started: ' + CONVERT(VARCHAR(30), GETDATE(), 120);
PRINT '============================================================';

-- ============================================================
-- 1. SETTINGS (UTL.catSettings)
-- ============================================================
PRINT '';
PRINT '=== Step 1: UTL.catSettings ===';

/*
  Settings explanation:
  - DefaultAssetId = 11 -> Used for operations on the MACHINE (delays, shifts, bundles)
  - DefaultAreaId = 10 -> Used for operations on the AREA (delay codes, categories config)
*/

MERGE UTL.catSettings AS target
USING (VALUES
    ('TimeZone', 'Eastern Standard Time', 'Local time zone for UTC conversions'),
    ('DefaultAssetId', '11', 'Default AssetId for delays/shifts (Weld165 machine)'),
    ('DefaultAreaId', '10', 'DelayAreaAssetId for delay codes config (Welds area)')
) AS source (SettingName, [Value], [Description])
ON target.SettingName = source.SettingName
WHEN MATCHED THEN
    UPDATE SET [Value] = source.[Value], [Description] = source.[Description]
WHEN NOT MATCHED THEN
    INSERT (SettingName, [Value], [Description])
    VALUES (source.SettingName, source.[Value], source.[Description]);

PRINT 'Settings merged (TimeZone, DefaultAssetId, DefaultAreaId)';

-- ============================================================
-- 2. USERS (SEC.catAuthTypes, SEC.catProfiles, SEC.catUsers, SEC.catUsersProfiles)
-- ============================================================
PRINT '';
PRINT '=== Step 2: Users ===';

-- SEC.catAuthTypes (prerequisite for catUsers)
SET IDENTITY_INSERT SEC.catAuthTypes ON;
MERGE SEC.catAuthTypes AS target
USING (VALUES
    (1, 'Local'),
    (2, 'AzureAD'),
    (3, 'Windows Authentication')
) AS source (AuthTypeId, Name)
ON target.AuthTypeId = source.AuthTypeId
WHEN MATCHED THEN
    UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (AuthTypeId, Name)
    VALUES (source.AuthTypeId, source.Name);
SET IDENTITY_INSERT SEC.catAuthTypes OFF;

PRINT 'SEC.catAuthTypes merged (Local, AzureAD, Windows Authentication)';

-- SEC.catProfiles (prerequisite for catUsersProfiles)
SET IDENTITY_INSERT SEC.catProfiles ON;
MERGE SEC.catProfiles AS target
USING (VALUES
    (1, 'Public Profile'),
    (2, 'Operator Profile'),
    (3, 'Supervisor Profile'),
    (4, 'admin Profile')
) AS source (ProfileId, ProfileName)
ON target.ProfileId = source.ProfileId
WHEN MATCHED THEN
    UPDATE SET ProfileName = source.ProfileName
WHEN NOT MATCHED THEN
    INSERT (ProfileId, ProfileName)
    VALUES (source.ProfileId, source.ProfileName);
SET IDENTITY_INSERT SEC.catProfiles OFF;

PRINT 'SEC.catProfiles merged (Public, Operator, Supervisor, admin)';

-- SEC.catUsers (UserId IS an IDENTITY column)
SET IDENTITY_INSERT SEC.catUsers ON;
MERGE SEC.catUsers AS target
USING (VALUES
    (1, 'public', 'public', 'access', '$2a$11$xRdglnOuFLKwlWCA5eh9o.IjmlMG9LGRI87I2ElG1qDoViSMR6B52', 1, NULL, 1),
    (4, 'dexteel', 'Admin', 'Access', '$2a$11$q/zybA5drI5JkdqiHZraEegD7BICjWgfpZGeYOG9mcOhE78eepsk.', 1, NULL, 1),
    (11, 'rogerh', 'Roger', 'Hutchinson', '$2a$11$1G6N5Efr6lOfAO4aEftMNut5fNmFzlsTCY54wllHJlR8CdVxigJzq', 1, NULL, 1)
) AS source (UserId, UserName, FirstName, LastName, PasswordHash, IsActive, DefaultAssetId, AuthTypeId)
ON target.UserId = source.UserId
WHEN MATCHED THEN
    UPDATE SET UserName = source.UserName, FirstName = source.FirstName, LastName = source.LastName,
               PasswordHash = source.PasswordHash, IsActive = source.IsActive, AuthTypeId = source.AuthTypeId
WHEN NOT MATCHED THEN
    INSERT (UserId, UserName, FirstName, LastName, PasswordHash, IsActive, DefaultAssetId, AuthTypeId)
    VALUES (source.UserId, source.UserName, source.FirstName, source.LastName, source.PasswordHash, source.IsActive, source.DefaultAssetId, source.AuthTypeId);
SET IDENTITY_INSERT SEC.catUsers OFF;

PRINT 'SEC.catUsers merged (public, dexteel, rogerh)';

-- SEC.catUsersProfiles
MERGE SEC.catUsersProfiles AS target
USING (VALUES
    (1, 1),   -- public: Profile 1
    (4, 1),   -- dexteel: Profile 1
    (4, 2),   -- dexteel: Profile 2
    (4, 3),   -- dexteel: Profile 3
    (4, 4),   -- dexteel: Profile 4
    (11, 1),  -- rogerh: Profile 1
    (11, 2),  -- rogerh: Profile 2
    (11, 3),  -- rogerh: Profile 3
    (11, 4)   -- rogerh: Profile 4
) AS source (UserId, ProfileId)
ON target.UserId = source.UserId AND target.ProfileId = source.ProfileId
WHEN NOT MATCHED THEN
    INSERT (UserId, ProfileId)
    VALUES (source.UserId, source.ProfileId);

PRINT 'SEC.catUsersProfiles merged';

-- Grant all SP permissions to admin profile (optional)
IF @GrantAdminPermissions = 1
BEGIN
    PRINT '';
    PRINT '=== Granting admin permissions for all SPs ===';

    DECLARE @ProcName NVARCHAR(500);
    DECLARE @Counter INT = 0;

    DECLARE proc_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT QUOTENAME(s.name) + '.' + QUOTENAME(p.name)
        FROM sys.procedures p
        INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
        WHERE s.name IN ('DEL', 'FAB', 'LB', 'MES', 'SEC', 'SYSTEM', 'UTL')
        ORDER BY s.name, p.name;

    OPEN proc_cursor;
    FETCH NEXT FROM proc_cursor INTO @ProcName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            EXEC [SEC].[UpsertProcedureAndSetToProfile] @ProcName, 'admin Profile', 'PostDeployment';
            SET @Counter = @Counter + 1;
        END TRY
        BEGIN CATCH
            PRINT 'Warning: Could not grant permission for ' + @ProcName + ': ' + ERROR_MESSAGE();
        END CATCH

        FETCH NEXT FROM proc_cursor INTO @ProcName;
    END

    CLOSE proc_cursor;
    DEALLOCATE proc_cursor;

    PRINT 'Granted permissions for ' + CAST(@Counter AS VARCHAR(10)) + ' stored procedures';
END
ELSE
BEGIN
    PRINT '';
    PRINT '=== Admin permissions SKIPPED (GrantAdminPermissions = 0) ===';
END

-- ============================================================
-- 3. ASSETS (MES.catAssets + DEL.catAssetsExtension)
-- ============================================================
PRINT '';
PRINT '=== Step 3: Assets ===';

-- MES.catAssets (AssetId is NOT an IDENTITY column, so no SET IDENTITY_INSERT needed)
-- Convention: Plant=1, Areas=10-99, Machines=10+N (e.g., Weld165=11, Weld250=12, etc.)
MERGE MES.catAssets AS target
USING (VALUES
    (@PlantAssetId, NULL, 'Plant', 0, 1),           -- Root (CanBeDefaultAsset=0)
    (@WeldsAreaId, @PlantAssetId, 'Welds', 0, 1),   -- Area (CanBeDefaultAsset=0)
    (@Weld165AssetId, @WeldsAreaId, 'Weld165', 1, 1) -- Machine (CanBeDefaultAsset=1)
) AS source (AssetId, ParentAssetId, AssetName, CanBeDefaultAsset, IsActive)
ON target.AssetId = source.AssetId
WHEN MATCHED THEN
    UPDATE SET ParentAssetId = source.ParentAssetId, AssetName = source.AssetName
WHEN NOT MATCHED THEN
    INSERT (AssetId, ParentAssetId, AssetName, CanBeDefaultAsset, IsActive)
    VALUES (source.AssetId, source.ParentAssetId, source.AssetName, source.CanBeDefaultAsset, source.IsActive);

PRINT 'MES.catAssets merged (Plant=1, Welds=10, Weld165=11)';

-- DEL.catAssetsExtension
MERGE DEL.catAssetsExtension AS target
USING (VALUES
    (@WeldsAreaId, 1, 0),     -- Welds: IsDelayArea=1, IsDelayAsset=0
    (@Weld165AssetId, 0, 1)   -- Weld165: IsDelayArea=0, IsDelayAsset=1
) AS source (AssetId, IsDelayArea, IsDelayAsset)
ON target.AssetId = source.AssetId
WHEN MATCHED THEN
    UPDATE SET IsDelayArea = source.IsDelayArea, IsDelayAsset = source.IsDelayAsset
WHEN NOT MATCHED THEN
    INSERT (AssetId, IsDelayArea, IsDelayAsset)
    VALUES (source.AssetId, source.IsDelayArea, source.IsDelayAsset);

PRINT 'DEL.catAssetsExtension merged';

-- ============================================================
-- 4. SHIFT PARAMETERS (MES.catShiftParameters)
-- ============================================================
PRINT '';
PRINT '=== Step 4: Shift Parameters ===';

DECLARE @Days INT = 90;
DECLARE @PatternStart DATETIME = CAST(CAST(GETUTCDATE() - @Days AS DATE) AS DATETIME);

IF NOT EXISTS (SELECT 1 FROM MES.catShiftParameters WHERE AssetId = @Weld165AssetId)
BEGIN
    INSERT INTO MES.catShiftParameters (
        AssetId, PatternStart, ShiftCodes, Crews, CrewRotation, [User], Comments
    )
    VALUES (
        @Weld165AssetId,
        @PatternStart,
        'D',           -- 1 shift per day
        'AB',          -- 2 crews
        'AB',          -- Alternating daily
        'PostDeployment',
        'Weld165: 1 shift/day (D), 2 crews (A/B), alternating daily'
    );
    PRINT 'Inserted: Shift parameters for Weld165';
    PRINT 'PatternStart: ' + CONVERT(VARCHAR(20), @PatternStart, 120);

    -- Generate shifts
    PRINT 'Generating shifts...';
    EXEC MES.CreateShiftsJob
        @AssetId = @Weld165AssetId,
        @StartDate = @PatternStart,
        @DaysInAdvance = 210;
    PRINT 'Shifts generated';
END
ELSE
BEGIN
    PRINT 'Shift parameters already exist for AssetId ' + CAST(@Weld165AssetId AS VARCHAR(10));
END

-- ============================================================
-- 5. DELAY CATEGORIES (DEL.catDelayCategories)
-- ============================================================
PRINT '';
PRINT '=== Step 5: Delay Categories ===';

MERGE DEL.catDelayCategories AS target
USING (VALUES
    (@WeldsAreaId, 'Mechanical', '#5eaf60', 1),
    (@WeldsAreaId, 'Hydraulic', '#a362bf', 1),
    (@WeldsAreaId, 'Operational', '#3280ba', 1),
    (@WeldsAreaId, 'Electrical', '#add8e6', 1)
) AS source (DelayAreaAssetId, DelayCategoryName, DelayCategoryColor, IsActive)
ON target.DelayAreaAssetId = source.DelayAreaAssetId AND target.DelayCategoryName = source.DelayCategoryName
WHEN MATCHED THEN
    UPDATE SET DelayCategoryColor = source.DelayCategoryColor, IsActive = source.IsActive
WHEN NOT MATCHED THEN
    INSERT (DelayAreaAssetId, DelayCategoryName, DelayCategoryColor, IsActive)
    VALUES (source.DelayAreaAssetId, source.DelayCategoryName, source.DelayCategoryColor, source.IsActive);

PRINT 'Delay categories merged (Mechanical, Hydraulic, Operational, Electrical)';

-- Get category IDs for delay codes
DECLARE @OperationalId INT = (SELECT DelayCategoryId FROM DEL.catDelayCategories
    WHERE DelayAreaAssetId = @WeldsAreaId AND DelayCategoryName = 'Operational');
DECLARE @HydraulicId INT = (SELECT DelayCategoryId FROM DEL.catDelayCategories
    WHERE DelayAreaAssetId = @WeldsAreaId AND DelayCategoryName = 'Hydraulic');

-- ============================================================
-- 6. DELAY CODES (DEL.catDelayCodes)
-- ============================================================
PRINT '';
PRINT '=== Step 6: Delay Codes ===';

-- First, rename any existing "Weld165" root to "Welds" (migration for existing DBs)
UPDATE DEL.catDelayCodes
SET DelayCodeName = 'Welds'
WHERE DelayCodeName = 'Weld165' AND ParentDelayCodeId IS NULL AND DelayAreaAssetId = @WeldsAreaId;

-- Rename "Weld165 MicroDelay" to "MicroDelay" (migration for existing DBs)
UPDATE DEL.catDelayCodes
SET DelayCodeName = 'MicroDelay'
WHERE DelayCodeName = 'Weld165 MicroDelay' AND DelayAreaAssetId = @WeldsAreaId;

-- ROOT: Welds (NOT Weld165 - so future WeldXXX can share codes)
MERGE DEL.catDelayCodes AS target
USING (SELECT
    NULL AS ParentDelayCodeId,
    @WeldsAreaId AS DelayAreaAssetId,
    @OperationalId AS DefaultDelayCategoryId,
    'Welds' AS DelayCodeName,
    1 AS SortOrder,
    0 AS IsSystemDelayCode,
    1 AS IsActive
) AS source
ON target.DelayAreaAssetId = source.DelayAreaAssetId
    AND target.DelayCodeName = source.DelayCodeName
    AND target.ParentDelayCodeId IS NULL
WHEN MATCHED THEN
    UPDATE SET DefaultDelayCategoryId = source.DefaultDelayCategoryId, SortOrder = source.SortOrder
WHEN NOT MATCHED THEN
    INSERT (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, IsSystemDelayCode, IsActive)
    VALUES (source.ParentDelayCodeId, source.DelayAreaAssetId, source.DefaultDelayCategoryId, source.DelayCodeName, source.SortOrder, source.IsSystemDelayCode, source.IsActive);

DECLARE @WeldsRootId INT = (SELECT DelayCodeId FROM DEL.catDelayCodes
    WHERE DelayAreaAssetId = @WeldsAreaId AND DelayCodeName = 'Welds' AND ParentDelayCodeId IS NULL);

PRINT 'Root code: Welds (Id=' + CAST(@WeldsRootId AS VARCHAR(10)) + ')';

-- E-Stops branch (system code, not editable)
MERGE DEL.catDelayCodes AS target
USING (SELECT
    @WeldsRootId AS ParentDelayCodeId,
    @WeldsAreaId AS DelayAreaAssetId,
    @OperationalId AS DefaultDelayCategoryId,
    'E-Stops' AS DelayCodeName,
    10 AS SortOrder,
    1 AS IsSystemDelayCode,
    1 AS IsActive
) AS source
ON target.DelayAreaAssetId = source.DelayAreaAssetId
    AND target.DelayCodeName = source.DelayCodeName
    AND target.ParentDelayCodeId = source.ParentDelayCodeId
WHEN MATCHED THEN
    UPDATE SET DefaultDelayCategoryId = source.DefaultDelayCategoryId, SortOrder = source.SortOrder
WHEN NOT MATCHED THEN
    INSERT (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, IsSystemDelayCode, IsActive)
    VALUES (source.ParentDelayCodeId, source.DelayAreaAssetId, source.DefaultDelayCategoryId, source.DelayCodeName, source.SortOrder, source.IsSystemDelayCode, source.IsActive);

-- System Delay Codes branch (system code, not editable)
MERGE DEL.catDelayCodes AS target
USING (SELECT
    @WeldsRootId AS ParentDelayCodeId,
    @WeldsAreaId AS DelayAreaAssetId,
    @OperationalId AS DefaultDelayCategoryId,
    'System Delay Codes' AS DelayCodeName,
    99 AS SortOrder,
    1 AS IsSystemDelayCode,
    1 AS IsActive
) AS source
ON target.DelayAreaAssetId = source.DelayAreaAssetId
    AND target.DelayCodeName = source.DelayCodeName
    AND target.ParentDelayCodeId = source.ParentDelayCodeId
WHEN MATCHED THEN
    UPDATE SET DefaultDelayCategoryId = source.DefaultDelayCategoryId, SortOrder = source.SortOrder
WHEN NOT MATCHED THEN
    INSERT (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, IsSystemDelayCode, IsActive)
    VALUES (source.ParentDelayCodeId, source.DelayAreaAssetId, source.DefaultDelayCategoryId, source.DelayCodeName, source.SortOrder, source.IsSystemDelayCode, source.IsActive);

DECLARE @EStopsId INT = (SELECT DelayCodeId FROM DEL.catDelayCodes
    WHERE DelayAreaAssetId = @WeldsAreaId AND DelayCodeName = 'E-Stops' AND ParentDelayCodeId = @WeldsRootId);
DECLARE @SystemDelayCodesId INT = (SELECT DelayCodeId FROM DEL.catDelayCodes
    WHERE DelayAreaAssetId = @WeldsAreaId AND DelayCodeName = 'System Delay Codes' AND ParentDelayCodeId = @WeldsRootId);

PRINT 'E-Stops branch: Id=' + CAST(@EStopsId AS VARCHAR(10));
PRINT 'System Delay Codes branch: Id=' + CAST(@SystemDelayCodesId AS VARCHAR(10));

-- E-Stop children (16 E-Stops) - all system codes
;WITH EStopSource AS (
    SELECT * FROM (VALUES
        (@EStopsId, @WeldsAreaId, @OperationalId, 'Destacker East', 1, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'Destacker West', 2, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'East Gate 1', 3, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'East Gate 2', 4, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'East Gate 3', 5, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'East Gate 4', 6, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'West Gate 1', 7, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'West Gate 2', 8, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'West Gate 3', 9, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'West Gate 4', 10, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'East Weld', 11, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'West Weld', 12, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'Stacker', 13, 1, 1),
        (@EStopsId, @WeldsAreaId, @HydraulicId, 'Hydraulic System', 14, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'System', 15, 1, 1),
        (@EStopsId, @WeldsAreaId, @OperationalId, 'System Safety', 16, 1, 1)
    ) AS v (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, IsSystemDelayCode, IsActive)
)
MERGE DEL.catDelayCodes AS target
USING EStopSource AS source
ON target.DelayAreaAssetId = source.DelayAreaAssetId
    AND target.DelayCodeName = source.DelayCodeName
    AND target.ParentDelayCodeId = source.ParentDelayCodeId
WHEN MATCHED THEN
    UPDATE SET DefaultDelayCategoryId = source.DefaultDelayCategoryId, SortOrder = source.SortOrder
WHEN NOT MATCHED THEN
    INSERT (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, IsSystemDelayCode, IsActive)
    VALUES (source.ParentDelayCodeId, source.DelayAreaAssetId, source.DefaultDelayCategoryId, source.DelayCodeName, source.SortOrder, source.IsSystemDelayCode, source.IsActive);

PRINT 'E-Stop codes merged (16 codes)';

-- MicroDelay (under System Delay Codes) - system code
MERGE DEL.catDelayCodes AS target
USING (SELECT
    @SystemDelayCodesId AS ParentDelayCodeId,
    @WeldsAreaId AS DelayAreaAssetId,
    @OperationalId AS DefaultDelayCategoryId,
    'MicroDelay' AS DelayCodeName,
    1 AS SortOrder,
    1 AS IsSystemDelayCode,
    1 AS IsActive
) AS source
ON target.DelayAreaAssetId = source.DelayAreaAssetId
    AND target.DelayCodeName = source.DelayCodeName
    AND target.ParentDelayCodeId = source.ParentDelayCodeId
WHEN MATCHED THEN
    UPDATE SET DefaultDelayCategoryId = source.DefaultDelayCategoryId, SortOrder = source.SortOrder
WHEN NOT MATCHED THEN
    INSERT (ParentDelayCodeId, DelayAreaAssetId, DefaultDelayCategoryId, DelayCodeName, SortOrder, IsSystemDelayCode, IsActive)
    VALUES (source.ParentDelayCodeId, source.DelayAreaAssetId, source.DefaultDelayCategoryId, source.DelayCodeName, source.SortOrder, source.IsSystemDelayCode, source.IsActive);

PRINT 'MicroDelay code merged';

-- ============================================================
-- 6b. PLC TAG MAPPING (DEL.PLCTagMapping)
-- ============================================================
PRINT '';
PRINT '=== Step 6b: PLC Tag Mapping ===';

-- Build mapping using DelayCodeId lookups (not hardcoded IDs)
;WITH PLCTagMappingSource AS (
    SELECT TagName, DelayCodeId FROM (VALUES
        ('Destacker_East_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'Destacker East' AND ParentDelayCodeId = @EStopsId)),
        ('Destacker_West_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'Destacker West' AND ParentDelayCodeId = @EStopsId)),
        ('East_Gate1_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'East Gate 1' AND ParentDelayCodeId = @EStopsId)),
        ('East_Gate2_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'East Gate 2' AND ParentDelayCodeId = @EStopsId)),
        ('East_Gate3_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'East Gate 3' AND ParentDelayCodeId = @EStopsId)),
        ('East_Gate4_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'East Gate 4' AND ParentDelayCodeId = @EStopsId)),
        ('West_Gate1_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'West Gate 1' AND ParentDelayCodeId = @EStopsId)),
        ('West_Gate2_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'West Gate 2' AND ParentDelayCodeId = @EStopsId)),
        ('West_Gate3_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'West Gate 3' AND ParentDelayCodeId = @EStopsId)),
        ('West_Gate4_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'West Gate 4' AND ParentDelayCodeId = @EStopsId)),
        ('East_Weld_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'East Weld' AND ParentDelayCodeId = @EStopsId)),
        ('West_Weld_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'West Weld' AND ParentDelayCodeId = @EStopsId)),
        ('Stacker_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'Stacker' AND ParentDelayCodeId = @EStopsId)),
        ('Hyd_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'Hydraulic System' AND ParentDelayCodeId = @EStopsId)),
        ('System_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'System' AND ParentDelayCodeId = @EStopsId)),
        ('System_Safety_Estop', (SELECT DelayCodeId FROM DEL.catDelayCodes WHERE DelayCodeName = 'System Safety' AND ParentDelayCodeId = @EStopsId))
    ) AS v (TagName, DelayCodeId)
)
MERGE DEL.PLCTagMapping AS target
USING PLCTagMappingSource AS source
ON target.TagName = source.TagName
WHEN MATCHED THEN
    UPDATE SET DelayCodeId = source.DelayCodeId
WHEN NOT MATCHED THEN
    INSERT (TagName, DelayCodeId)
    VALUES (source.TagName, source.DelayCodeId);

PRINT 'PLC Tag mappings merged (16 mappings)';

-- Set IsSelectable/IsEditable based on IsSystemDelayCode
-- All codes: selectable (can be used for justification)
-- System codes: not editable (can't rename/delete)
-- User codes: fully editable
UPDATE DEL.catDelayCodes
SET
    IsSelectable = 1,  -- All codes are selectable
    IsEditable = CASE WHEN IsSystemDelayCode = 1 THEN 0 ELSE 1 END
WHERE DelayAreaAssetId = @WeldsAreaId;

PRINT 'IsSelectable=1 for all codes, IsEditable synced based on IsSystemDelayCode';

-- ============================================================
-- 7. REGISTER TRACKING JOBS (SYSTEM.catJobs)
-- ============================================================
PRINT '';
PRINT '=== Step 7: Register Tracking Jobs ===';

/*
  Jobs are registered in SYSTEM.catJobs for the JobManager to execute automatically.
  - FAB.TrackBundlesJob: Extracts bundles/batches from historian (every 30s)
  - FAB.TrackDelaysJob: Identifies production delays (every 60s)
*/

MERGE [SYSTEM].catJobs AS target
USING (VALUES
    ('FAB.TrackBundlesJob', 1, 30, GETUTCDATE(), 1),
    ('FAB.TrackDelaysJob', 1, 60, GETUTCDATE(), 1)
) AS source (sProcedure, RestartAfterFailure, PeriodTime, StartTime, Enabled)
ON target.sProcedure = source.sProcedure
WHEN MATCHED THEN
    UPDATE SET
        RestartAfterFailure = source.RestartAfterFailure,
        PeriodTime = source.PeriodTime
WHEN NOT MATCHED THEN
    INSERT (sProcedure, RestartAfterFailure, PeriodTime, StartTime, Enabled, CurrentStatus, [Counter])
    VALUES (source.sProcedure, source.RestartAfterFailure, source.PeriodTime, source.StartTime, source.Enabled, 'P', 0);

PRINT 'Registered: FAB.TrackBundlesJob (every 30s)';
PRINT 'Registered: FAB.TrackDelaysJob (every 60s)';

-- ============================================================
-- VERIFICATION
-- ============================================================
PRINT '';
PRINT '=== Verification ===';

SELECT 'MES.catAssets' AS [Table], COUNT(*) AS [Count] FROM MES.catAssets
UNION ALL SELECT 'DEL.catAssetsExtension', COUNT(*) FROM DEL.catAssetsExtension
UNION ALL SELECT 'MES.catShiftParameters', COUNT(*) FROM MES.catShiftParameters
UNION ALL SELECT 'MES.Shifts', COUNT(*) FROM MES.Shifts
UNION ALL SELECT 'DEL.catDelayCategories', COUNT(*) FROM DEL.catDelayCategories
UNION ALL SELECT 'DEL.catDelayCodes', COUNT(*) FROM DEL.catDelayCodes
UNION ALL SELECT 'DEL.PLCTagMapping', COUNT(*) FROM DEL.PLCTagMapping
UNION ALL SELECT 'FAB.Batches', COUNT(*) FROM FAB.Batches
UNION ALL SELECT 'FAB.Bundles', COUNT(*) FROM FAB.Bundles
UNION ALL SELECT 'DEL.Delays', COUNT(*) FROM DEL.Delays
UNION ALL SELECT 'DEL.PreJustification', COUNT(*) FROM DEL.PreJustification;

-- ============================================================
-- 8. ADD NAVBAR CONFIGURATION OPTION
-- ============================================================
PRINT '';
PRINT '=== Step 8: Navbar Configuration ===';

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.settings', 'admin Profile';
PRINT '(configurations option added)';

PRINT '';
PRINT '============================================================';
PRINT 'Post-Deployment Complete!';
PRINT 'Finished: ' + CONVERT(VARCHAR(30), GETDATE(), 120);
PRINT '============================================================';

