SET QUOTED_IDENTIFIER ON;

GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Heat.UpdateTapTemperature', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetById', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetPlantOverview', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.AcknowledgeReadyForUseAlarm', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetAlarmConfiguration', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.UpdateAlarmConfiguration', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetDetailsById', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetTasks', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetOverview', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetComponents', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.ChangeLocation', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.ChangeCurrentStatus', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetCampaigns', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Tundish.GetComponentCampaigns', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'Ladle.GetLadleCampaigns', 'admin Profile', 'devops'
GO

-- Job management screen (mesf-core) procs: ensure 'admin Profile' can list/manage jobs.
-- GetJobsStatus was assigned to Public/Operator/Supervisor but NOT admin -> admins could not list jobs.
-- Idempotent (UpsertProcedureAndSetToProfile does not duplicate the action or the assignment).
EXEC SEC.UpsertProcedureAndSetToProfile 'SYSTEM.GetJobsStatus', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'SYSTEM.SetJobEnable', 'admin Profile', 'devops'
GO

EXEC SEC.UpsertProcedureAndSetToProfile 'SYSTEM.RestartJob', 'admin Profile', 'devops'
GO

-- Backfill the derived ladle campaign business key (NSB.LadleCampaigns).
-- Idempotent: preserves existing LadleCampaignId values across deployments.
EXEC NSB.RecalculateLadleCampaigns;
GO

-- Register the periodic recalculation of NSB.LadleCampaigns as a SYSTEM job
-- (every 10 minutes = 600 s). Idempotent: guarded by IF NOT EXISTS so re-running
-- this deployment script does not create duplicate job rows.
-- NOTE: requires the JobManager Windows service running against this DB to actually execute.
IF NOT EXISTS (SELECT 1 FROM SYSTEM.catJobs WHERE sProcedure = 'NSB.RecalculateLadleCampaigns')
    INSERT INTO SYSTEM.catJobs (sProcedure, PeriodTime, RestartAfterFailure, [Enabled])
    VALUES ('NSB.RecalculateLadleCampaigns', 600, 1, 1);
GO

IF NOT EXISTS (SELECT 1 FROM REF.RootEventType WHERE RootEventTypeId = 303)
    INSERT INTO REF.RootEventType (RootEventTypeId, RootEventTypeName, RootTypeId) VALUES (303, 'ReadyForUseAlarmTriggered', 3);
GO

IF NOT EXISTS (SELECT 1 FROM REF.RootEventType WHERE RootEventTypeId = 304)
    INSERT INTO REF.RootEventType (RootEventTypeId, RootEventTypeName, RootTypeId) VALUES (304, 'ReadyForUseAlarmAcknowledged', 3);
GO

IF NOT EXISTS (SELECT 1 FROM REF.RootEventType WHERE RootEventTypeId = 305)
    INSERT INTO REF.RootEventType (RootEventTypeId, RootEventTypeName, RootTypeId) VALUES (305, 'ReadyForUseAlarmCleared', 3);
GO

DECLARE @Now DATETIME = GETUTCDATE();
DECLARE @RootId INT;
DECLARE @RootName VARCHAR(50);
DECLARE @ReadyForUseSince DATETIME;
DECLARE @RootEventId INT;
DECLARE @Description VARCHAR(MAX);

DECLARE clear_car_alarm_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT a.RootId, r.RootName, a.ReadyForUseSince
    FROM REF.TundishReadyForUseAlarm a
    INNER JOIN REF.Root r ON r.RootId = a.RootId
    INNER JOIN REF.vTundishLocation v ON v.RootId = a.RootId AND v.ReadyForUseSince = a.ReadyForUseSince AND v.LocationStateId = 8
    INNER JOIN REF.Location l ON l.LocationId = v.LocationId
    WHERE a.IsActive = 1
      AND a.ClearedRootEventId IS NULL
      AND l.LocationTypeId = 6;

OPEN clear_car_alarm_cursor;
FETCH NEXT FROM clear_car_alarm_cursor INTO @RootId, @RootName, @ReadyForUseSince;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Description = CONCAT('Tundish ', ISNULL(@RootName, CONVERT(VARCHAR(20), @RootId)), ' Ready For Use expiration alarm cleared. Ready For Use since ', CONVERT(VARCHAR(19), @ReadyForUseSince, 120), ' UTC.');

    INSERT INTO REF.RootEvent (RootId, RootEventTypeId, Timestamp, Description, Comments)
    VALUES (@RootId, 305, @Now, @Description, 'Ready For Use alarm cleared by post-deployment cleanup because tundish is already in a tundish car.');

    SET @RootEventId = SCOPE_IDENTITY();

    UPDATE REF.TundishReadyForUseAlarm
    SET IsActive = 0,
        ClearedAt = @Now,
        ClearedRootEventId = @RootEventId,
        UpdateTimestamp = @Now
    WHERE RootId = @RootId
      AND ReadyForUseSince = @ReadyForUseSince;

    FETCH NEXT FROM clear_car_alarm_cursor INTO @RootId, @RootName, @ReadyForUseSince;
END

CLOSE clear_car_alarm_cursor;
DEALLOCATE clear_car_alarm_cursor;
GO
