SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------------------
-- KPI fact-table jobs consolidation
-------------------------------------------------------------------------------
-- The KPI fact tables used to be refreshed by 5 individual catJobs entries
-- (mirroring legacy SQL Agent steps). They are now consolidated:
--   * Times / Production / Campaigns / Straightener -> KPI.CalculateKPIsJob
--     (single hourly orchestrator; see that SP's header).
--   * TagScans -> folded into MILL.ImportTagScans (runs after each import,
--     mirroring the legacy "ImportTagScans" job step 2 "Generate KPI").
--
-- So the individual jobs below are no longer used and are removed here.
-- Match both the bracketed and unbracketed sProcedure forms because legacy
-- registrations stored either one.
-------------------------------------------------------------------------------
DELETE FROM [SYSTEM].[catJobs]
WHERE sProcedure IN (
    'KPI.UpsertTimesFactTable',        '[KPI].[UpsertTimesFactTable]',
    'KPI.UpsertProductionFactTable',   '[KPI].[UpsertProductionFactTable]',
    'KPI.UpsertCampaignsFactTable',    '[KPI].[UpsertCampaignsFactTable]',
    'KPI.UpsertStraightenerRuns',      '[KPI].[UpsertStraightenerRuns]',
    'KPI.UpsertTagScansFactTable',     '[KPI].[UpsertTagScansFactTable]'
);
GO

-- Register the consolidated hourly orchestrator (idempotent: UpsertJob keys on
-- @JobId, so guard by sProcedure to avoid inserting a duplicate every deploy).
IF NOT EXISTS (
    SELECT 1 FROM [SYSTEM].[catJobs]
    WHERE sProcedure IN ('KPI.CalculateKPIsJob', '[KPI].[CalculateKPIsJob]')
)
BEGIN
    EXEC [SYSTEM].[UpsertJob]
         @sProcedure          = '[KPI].[CalculateKPIsJob]',
         @StartTime           = '2026-01-01 08:05:00', -- anchor; hourly thereafter (legacy "Shift Ended" started 08:05)
         @PeriodTime          = 3600,                  -- seconds (hourly)
         @RestartAfterFailure = 1,
         @User                = 'devops';
END
GO
