SET QUOTED_IDENTIFIER ON
GO

/*
    Post-Deployment Script — current release.

    8-hour shift day starts at 7am (was 8am); 20th Street Saw (24) and Wax (30) on 8h.
    Also ships: dashboard/saw quick wins batch (#152, #170, #172, #173, #175);
    products/clips feedback batch (#187-#195); weld tests report page (#202).
    Per-release: previous releases' steps are already in main and are not repeated.
*/
PRINT '=== Post-Deployment: START ===';

-- Weld test tablet (#165): register client SPs
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetWeldTests', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.UpsertWeldTest', 'admin Profile', 'dexteel';
PRINT 'Registered FAB.GetWeldTests + FAB.UpsertWeldTest to admin Profile';

-- Drop residual object removed from the model this release.
DROP PROCEDURE IF EXISTS [FAB].[GetBatchesProduction];
GO

-- 146/170 cycle-time is now published as *_Real (PLC tag added 16-jul-2026).
-- Idempotent: the <> guard makes re-runs report 0 rows.
UPDATE m SET m.TagName = 'FAB.' + a.AssetName + '.Global.Servo_Last_Cycle_Seconds_Real'
FROM FAB.WeldLineTagMap m
INNER JOIN MES.catAssets a ON a.AssetId = m.AssetId
WHERE a.AssetName IN ('Weld146', 'Weld170')
  AND m.Signal = 'CycleSeconds'
  AND m.TagName <> 'FAB.' + a.AssetName + '.Global.Servo_Last_Cycle_Seconds_Real';
PRINT '[TagMap] CycleSeconds -> *_Real (146/170): ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetShiftSchemeAssets', 'admin Profile', 'dexteel';

-- STEP 1: shift day starts at 7am (was 8am) for Welds(10) + Plant(1); regenerate.
UPDATE MES.catShiftParameters
SET PatternStart = DATEADD(HOUR, -1, PatternStart)
WHERE AssetId IN (1, 10) AND DATEPART(HOUR, PatternStart) = 8;
PRINT '[STEP 1] PatternStart -1h (Welds/Plant): ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

EXEC MES.CreateShiftsJob @AssetId = 10, @StartDate = '2026-01-01', @DaysInAdvance = 310;
EXEC MES.CreateShiftsJob @AssetId = 1,  @StartDate = '2026-01-01', @DaysInAdvance = 310;
PRINT '[STEP 1] Regenerated Welds(10) + Plant(1) shifts at 7am boundary';

-- STEP 2: the tracked saw is the 20th Street Saw (AssetId 24, historian FAB.20thStSAW).
-- Rename it, re-point its records off the mislabeled East Saw(21), and free 21 for the real East Saw.
UPDATE MES.catAssets SET AssetName = '20th Street Saw' WHERE AssetId = 24 AND AssetName = 'New Saw';
IF NOT EXISTS (SELECT 1 FROM MES.catAssets WHERE AssetName = '20th Street Saw')
    THROW 51900, '[STEP 2] Asset 24 is not named ''New Saw'' — rename skipped and saw jobs would dead-stop. Fix MES.catAssets first.', 1;

UPDATE FAB.SawTelemetry    SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.SawEntryBundles SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.WorkOrders      SET AssetId = 24 WHERE AssetId = 21;
UPDATE DEL.Delays          SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.Batches             SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.Bundles             SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.Layers              SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.Bars                SET AssetId = 24 WHERE AssetId = 21;
UPDATE FAB.BundleQualityChecks SET AssetId = 24 WHERE AssetId = 21;
PRINT '[STEP 2] Renamed asset 24 -> 20th Street Saw; re-pointed saw records 21 -> 24';

-- STEP 3: 8h scheme (7am M/A/N) for 20th Street Saw(24) + Wax(30).
IF NOT EXISTS (SELECT 1 FROM MES.catShiftParameters WHERE AssetId = 24)
    INSERT INTO MES.catShiftParameters (AssetId, PatternStart, ShiftCodes, Crews, CrewRotation, [User], Comments)
    VALUES (24, '2026-01-01 07:00:00', 'MAN', 'ABC', 'ABCABCABC', 'seed', '20th Street Saw: 3x8h (M=7-15, A=15-23, N=23-07)');
IF NOT EXISTS (SELECT 1 FROM MES.catShiftParameters WHERE AssetId = 30)
    INSERT INTO MES.catShiftParameters (AssetId, PatternStart, ShiftCodes, Crews, CrewRotation, [User], Comments)
    VALUES (30, '2026-01-01 07:00:00', 'MAN', 'ABC', 'ABCABCABC', 'seed', 'Wax: 3x8h (M=7-15, A=15-23, N=23-07)');

EXEC MES.CreateShiftsJob @AssetId = 24, @StartDate = '2026-01-01', @DaysInAdvance = 310;
EXEC MES.CreateShiftsJob @AssetId = 30, @StartDate = '2026-01-01', @DaysInAdvance = 310;
PRINT '[STEP 3] Seeded + generated 8h shifts for 20th Street Saw(24) + Wax(30)';

-- STEP 4: backfill ShiftId for the 8h lines since 2026-01-01 (the STEP 1 regeneration window).
-- Literal cutoff on purpose: statement-splitting runners (DBeaver Alt+X) drop DECLAREd vars.
UPDATE d SET ShiftId = FAB.GetShiftIdByAncestor(d.AssetId, d.[Start])
FROM DEL.Delays d WHERE d.AssetId IN (11,12,13,14,15,24,30) AND d.[Start] >= '2026-01-01';
PRINT '[STEP 4] Delays re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

UPDATE c SET ShiftId = FAB.GetShiftIdByAncestor(c.AssetId, c.[Start])
FROM FAB.CleaningCycles c WHERE c.AssetId IN (11,12,13,14,15,24,30) AND c.[Start] >= '2026-01-01';
PRINT '[STEP 4] CleaningCycles re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

UPDATE e SET ShiftId = FAB.GetShiftIdByAncestor(e.AssetId, e.[Start])
FROM FAB.EStopActivations e WHERE e.AssetId IN (11,12,13,14,15,24,30) AND e.[Start] >= '2026-01-01';
PRINT '[STEP 4] EStopActivations re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

UPDATE r SET ShiftId = FAB.GetShiftIdByAncestor(r.AssetId, r.[Start])
FROM FAB.RobotAlarms r WHERE r.AssetId IN (11,12,13,14,15,24,30) AND r.[Start] >= '2026-01-01';
PRINT '[STEP 4] RobotAlarms re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

UPDATE a SET ShiftId = FAB.GetShiftIdByAncestor(a.AssetId, a.[Start])
FROM FAB.AutoModePeriods a WHERE a.AssetId IN (11,12,13,14,15,24,30) AND a.[Start] >= '2026-01-01';
PRINT '[STEP 4] AutoModePeriods re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

UPDATE q SET ShiftId = FAB.GetShiftIdByAncestor(q.AssetId, q.CheckTime)
FROM FAB.BundleQualityChecks q WHERE q.AssetId IN (11,12,13,14,15,24,30) AND q.CheckTime >= '2026-01-01';
PRINT '[STEP 4] BundleQualityChecks re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

UPDATE s SET ShiftId = FAB.GetShiftIdByAncestor(s.AssetId, s.ScannedAt)
FROM FAB.SawEntryBundles s WHERE s.AssetId IN (11,12,13,14,15,24,30) AND s.ScannedAt >= '2026-01-01';
PRINT '[STEP 4] SawEntryBundles re-stamped: ' + CAST(@@ROWCOUNT AS VARCHAR);

EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetBundleReclassification', 'admin Profile', 'dexteel';
PRINT 'Registered FAB.GetBundleReclassification to admin Profile';

-- Side Gage + keepers moved from the clip setup to the weld product specs.
-- Idempotent: only fills specs that don't have their own value yet.
UPDATE fs SET
    fs.SideGage      = COALESCE(fs.SideGage, cl.SideGage),
    fs.EntryKeeperId = COALESCE(fs.EntryKeeperId, cl.EntryKeeperId),
    fs.ExitKeeperId  = COALESCE(fs.ExitKeeperId, cl.ExitKeeperId),
    fs.BottomKeeper  = COALESCE(fs.BottomKeeper, CASE WHEN cl.BottomKeeperSetup = 1 THEN 'X' END)
FROM FAB.FabricationSpecs fs
INNER JOIN FAB.catClips cl ON cl.ClipId = fs.ClipId
WHERE (fs.SideGage IS NULL AND cl.SideGage IS NOT NULL)
   OR (fs.EntryKeeperId IS NULL AND cl.EntryKeeperId IS NOT NULL)
   OR (fs.ExitKeeperId IS NULL AND cl.ExitKeeperId IS NOT NULL)
   OR (fs.BottomKeeper IS NULL AND cl.BottomKeeperSetup = 1);
PRINT 'Clip side gage/keepers copied to weld specs: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetDrawingViewState', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.SaveDrawingViewState', 'admin Profile', 'dexteel';
PRINT 'Registered FAB.GetDrawingViewState + FAB.SaveDrawingViewState to admin Profile';

IF NOT EXISTS (SELECT 1 FROM FAB.catGrades WHERE GradeName = N'ASTM A572 Gr 50' AND IsActive = 1)
    INSERT INTO FAB.catGrades (GradeName) VALUES (N'ASTM A572 Gr 50');
PRINT 'ASTM A572 Gr 50 seeded';

PRINT '=== Post-Deployment: COMPLETE ===';
