SET QUOTED_IDENTIFIER ON
GO

/*
    Post-Deployment Script — saw QC per bundle + reclassification flow (#208/#214).

    Per-release: holds only the post-deployment steps for the feature shipping in
    this release. Previous releases' steps are already in main and are not repeated.
*/
PRINT '=== Post-Deployment: START ===';

-- QC per bundle: register the new SPs
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawRecentBundles', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawRecentBundles', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.RevertBundleReclassification', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.RevertBundleReclassification', 'Operator Profile', 'dexteel';
PRINT 'Registered FAB.GetSawRecentBundles + FAB.RevertBundleReclassification to admin + Operator profiles';

-- Saw overview family was only registered to admin (4c2c856); operators got it
-- by hand in prod. Make every environment consistent.
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawOverview', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawQualityChecks', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.UpsertBundleQualityCheck', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.DeleteBundleQualityCheck', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawReclassifyProducts', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.ReclassifyBundle', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetBundleReclassification', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.UpsertSawEntryScan', 'Operator Profile', 'dexteel';
PRINT 'Saw overview SP family registered to Operator Profile';

-- Weld test SPs shipped registered to admin only (#210); operators save from tablets
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetWeldTests', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.UpsertWeldTest', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.DeleteWeldTest', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.DeleteWeldTest', 'Operator Profile', 'dexteel';
PRINT 'Registered weld test SPs to Operator Profile';

-- Live refresh: a weld test saved/deleted anywhere invalidates the manager page.
-- Single IF block on purpose: statement-splitting runners (DBeaver) drop
-- DECLAREd vars between statements.
IF EXISTS (SELECT 1 FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.GetWeldTests')
BEGIN
    DECLARE @WTTargets VARCHAR(255) = (SELECT CAST(ActionId AS VARCHAR) FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.GetWeldTests');
    DECLARE @UpsertWTId INT = (SELECT ActionId FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.UpsertWeldTest');
    DECLARE @DeleteWTId INT = (SELECT ActionId FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.DeleteWeldTest');
    IF @UpsertWTId IS NOT NULL
        EXEC [SYSTEM].[UpsertQueryCacheInvalidations] @SourceId = @UpsertWTId, @TargetsCSV = @WTTargets, @User = 'PostDeployment';
    IF @DeleteWTId IS NOT NULL
        EXEC [SYSTEM].[UpsertQueryCacheInvalidations] @SourceId = @DeleteWTId, @TargetsCSV = @WTTargets, @User = 'PostDeployment';
    PRINT 'Registered: Upsert/DeleteWeldTest -> GetWeldTests cache invalidation';
END
ELSE
    PRINT 'Warning: FAB.GetWeldTests not found in catActions — invalidation not registered';

DELETE FROM UTL.catSettings WHERE SettingName = 'SawQCIntervalMinutes';
PRINT 'Removed dead setting SawQCIntervalMinutes: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

-- Legacy 0/1 bit values are meaningless as measurements; real dimensions are never 0.00/1.00
UPDATE FAB.BundleQualityChecks
SET CheckTL = NULL, CheckTR = NULL, CheckMC = NULL, CheckBL = NULL, CheckBR = NULL
WHERE (CheckTL IS NOT NULL OR CheckTR IS NOT NULL OR CheckMC IS NOT NULL OR CheckBL IS NOT NULL OR CheckBR IS NOT NULL)
  AND ISNULL(CheckTL, 0) IN (0, 1) AND ISNULL(CheckTR, 0) IN (0, 1)
  AND ISNULL(CheckMC, 0) IN (0, 1) AND ISNULL(CheckBL, 0) IN (0, 1)
  AND ISNULL(CheckBR, 0) IN (0, 1);
PRINT 'Cleared legacy pass/fail QC bits: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

-- WOs reverted before UpsertWorkOrder cleared timestamps on de-transition
UPDATE FAB.WorkOrders SET StartedAt = NULL, StartedBy = NULL, CompletedAt = NULL, CompletedBy = NULL
WHERE [Status] = 'Pending' AND StartedAt IS NOT NULL;
PRINT 'Cleared stale start/completion on Pending WOs: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';
UPDATE FAB.WorkOrders SET CompletedAt = NULL, CompletedBy = NULL
WHERE [Status] = 'Running' AND CompletedAt IS NOT NULL;
PRINT 'Cleared stale completion on Running WOs: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

-- #181: new schedulable lines — 30 Bldg saws under Saws(20), Clip Presses as new area (60)
MERGE MES.catAssets AS target
USING (VALUES
    (25, 20, '30 Bldg Saw North', 0, 1),
    (26, 20, '30 Bldg Saw South', 0, 1),
    (60,  1, 'Clip Presses',      1, 1),
    (61, 60, 'Clip Press Ave',    0, 1),
    (62, 60, 'Clip Press River',  0, 1)
) AS source (AssetId, ParentAssetId, AssetName, CanBeDefaultAsset, IsActive)
ON target.AssetId = source.AssetId
WHEN MATCHED THEN
    UPDATE SET ParentAssetId = source.ParentAssetId, AssetName = source.AssetName, CanBeDefaultAsset = source.CanBeDefaultAsset
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 (30 Bldg Saw North/South=25/26, Clip Presses=60, Ave/River=61/62)';

MERGE DEL.catAssetsExtension AS target
USING (VALUES
    (25, 0, 1),
    (26, 0, 1),
    (60, 1, 0),
    (61, 0, 1),
    (62, 0, 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 (new lines visible on Work Orders/Delays screens)';

MERGE UTL.catSettings AS target
USING (VALUES
    ('ClipsAreaId', '60', 'Area AssetId for Clip Presses (work orders)')
) 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 'UTL.catSettings: ClipsAreaId = 60';

-- #190: Galv product categories (offsite — no catAssetsExtension rows on purpose)
MERGE MES.catAssets AS target
USING (VALUES
    (70, 1, 'Galv-Sawed',   0, 1),
    (80, 1, 'Galv-Punched', 0, 1),
    (90, 1, 'Galv-Welded',  0, 1)
) AS source (AssetId, ParentAssetId, AssetName, CanBeDefaultAsset, IsActive)
ON target.AssetId = source.AssetId
WHEN MATCHED THEN
    UPDATE SET ParentAssetId = source.ParentAssetId, AssetName = source.AssetName, CanBeDefaultAsset = source.CanBeDefaultAsset
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 (Galv-Sawed=70, Galv-Punched=80, Galv-Welded=90)';

-- #222: saw quality checks report
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawQualityChecksReport', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetSawQualityChecksReport', 'Operator Profile', 'dexteel';
PRINT 'Registered FAB.GetSawQualityChecksReport to admin + Operator profiles';

-- Live refresh for the saw checks: saving, deleting or reclassifying a check
-- refreshes both the report and the overview card in every open browser.
IF EXISTS (SELECT 1 FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.GetSawQualityChecksReport')
BEGIN
    DECLARE @QCTargets VARCHAR(255) = (
        SELECT STRING_AGG(CAST(ActionId AS VARCHAR), ',')
        FROM [SYSTEM].[catActions]
        WHERE ActionName IN ('FAB.GetSawQualityChecksReport', 'FAB.GetSawQualityChecks')
    );
    DECLARE @UpsertQCId INT = (SELECT ActionId FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.UpsertBundleQualityCheck');
    DECLARE @DeleteQCId INT = (SELECT ActionId FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.DeleteBundleQualityCheck');
    DECLARE @ReclassQCId INT = (SELECT ActionId FROM [SYSTEM].[catActions] WHERE ActionName = 'FAB.ReclassifyBundle');
    IF @UpsertQCId IS NOT NULL
        EXEC [SYSTEM].[UpsertQueryCacheInvalidations] @SourceId = @UpsertQCId, @TargetsCSV = @QCTargets, @User = 'PostDeployment';
    IF @DeleteQCId IS NOT NULL
        EXEC [SYSTEM].[UpsertQueryCacheInvalidations] @SourceId = @DeleteQCId, @TargetsCSV = @QCTargets, @User = 'PostDeployment';
    IF @ReclassQCId IS NOT NULL
        EXEC [SYSTEM].[UpsertQueryCacheInvalidations] @SourceId = @ReclassQCId, @TargetsCSV = @QCTargets, @User = 'PostDeployment';
    PRINT 'Registered: quality check writes -> saw checks cache invalidation';
END
ELSE
    PRINT 'Warning: FAB.GetSawQualityChecksReport not found in catActions — invalidation not registered';

-- SPs never registered to any profile (audited vs prod)
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.DeletePunchSpecs', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetProductAncestorSpecs', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetProductAncestorSpecs', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetPunchBeamPositions', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetPunchBeamPositions', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetPunchPerforations', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetPunchPerforations', 'Operator Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetPunchSpecs', 'admin Profile', 'dexteel';
EXEC [SEC].[UpsertProcedureAndSetToProfile] 'FAB.GetPunchSpecs', 'Operator Profile', 'dexteel';
PRINT 'Registered missing punch/ancestor-spec SPs to their profiles';

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