select 'post_deployment_script';
SET QUOTED_IDENTIFIER ON; -- IMPORTANT TO ADD IN ALL DEPLOYS
GO
-- ============================================================================
-- Script: AddChargeLayerAuditInvalidations.sql
-- Purpose: Add SCRAP.GetChargeLayerAudit as a SignalR invalidation target
--          for mutation SPs that modify the ChargeLayerAudit table.
--
-- Context: The Scrap Layering Report uses GetChargeLayerAudit to display
--          the actual layer audit chart. These mutations were missing the
--          invalidation target, so changes weren't propagated in real-time.
--
-- Mutations affected:
--   SCRAP.UpsertCharge             - Deletes audit when reassigning recipe
--   SCRAP.DeleteCharge             - Deletes audit + layers + charge
--   SCRAP.DeleteChargeLayer        - Deletes audit + layer
--   SCRAP.ReassignRecipeToCharges  - Deletes audit, rebuilds layers
--
-- NOTE: This script is ADDITIVE. It does not use UpsertQueryCacheInvalidations
--       (which replaces all targets for a source). Instead, it inserts only
--       the missing SourceId -> TargetId pairs.
-- ============================================================================
BEGIN


SET NOCOUNT ON;

-- ============================================================================
-- Resolve GetChargeLayerAudit ActionId
-- ============================================================================
DECLARE @GetChargeLayerAudit_Id INT;

SELECT @GetChargeLayerAudit_Id = ActionId
FROM SYSTEM.catActions
WHERE ActionName = 'SCRAP.GetChargeLayerAudit';

IF @GetChargeLayerAudit_Id IS NULL
BEGIN
    PRINT 'ERROR: SCRAP.GetChargeLayerAudit not found in SYSTEM.catActions.';
    PRINT 'Register the SP before running this script.';
    RETURN;
END

-- ============================================================================
-- Mutations that modify ChargeLayerAudit
-- ============================================================================
DECLARE @Mutations TABLE (SPName VARCHAR(100));

INSERT INTO @Mutations (SPName)
VALUES
    ('SCRAP.UpsertCharge'),
    ('SCRAP.DeleteCharge'),
    ('SCRAP.DeleteChargeLayer'),
    ('SCRAP.ReassignRecipeToCharges');

-- ============================================================================
-- Verify all mutations exist in catActions
-- ============================================================================
DECLARE @Missing INT;

SELECT @Missing = COUNT(*)
FROM @Mutations m
LEFT JOIN SYSTEM.catActions a ON m.SPName = a.ActionName
WHERE a.ActionId IS NULL;

IF @Missing > 0
BEGIN
    PRINT 'WARNING: The following mutations are not registered in SYSTEM.catActions:';

    SELECT m.SPName AS 'Missing SP'
    FROM @Mutations m
    LEFT JOIN SYSTEM.catActions a ON m.SPName = a.ActionName
    WHERE a.ActionId IS NULL;
END

-- ============================================================================
-- Insert missing invalidation pairs (additive, skip duplicates)
-- ============================================================================
INSERT INTO SYSTEM.QueryCacheInvalidations (SourceId, TargetId)
SELECT a.ActionId, @GetChargeLayerAudit_Id
FROM @Mutations m
INNER JOIN SYSTEM.catActions a ON m.SPName = a.ActionName
WHERE NOT EXISTS (
    SELECT 1
    FROM SYSTEM.QueryCacheInvalidations qci
    WHERE qci.SourceId = a.ActionId
      AND qci.TargetId = @GetChargeLayerAudit_Id
);

PRINT 'Inserted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' new invalidation mappings.';

-- ============================================================================
-- Summary
-- ============================================================================
PRINT '';
PRINT '=== ChargeLayerAudit Invalidation Mappings ===';

SELECT
    src.ActionName AS 'Mutation (Source)',
    tgt.ActionName AS 'Getter (Target)'
FROM SYSTEM.QueryCacheInvalidations qci
INNER JOIN SYSTEM.catActions src ON qci.SourceId = src.ActionId
INNER JOIN SYSTEM.catActions tgt ON qci.TargetId = tgt.ActionId
WHERE tgt.ActionId = @GetChargeLayerAudit_Id
ORDER BY src.ActionName;

EXEC UTL.SetSetting 'SCRAP_LAYERING_REPORT_REFRESH_TIME', 'SCRAP_LAYERING_REPORT_REFRESH_TIME', '10', 'Polling interval (seconds) for GetChargeLayerAudit on the Scrap Layering Report page';

END
