SET QUOTED_IDENTIFIER ON

GO

-- ============================================================================
-- PDF Intake (LAB / BYI) catalog seeds. MERGE-based: inserts missing rows and leaves manual edits
-- alone, EXCEPT SUP.catSuppliers.HeatPrefixes, which is authoritative and corrected on match.
-- catElements / catSuppliers are curated vocabularies (the validation gate);
-- grades and sample types are auto-created on insert by the upsert SPs.
-- ============================================================================

-- catElements: ElementName holds the token as printed on the PDFs (C, Mn...) — the parser match key.
MERGE INTO LAB.catElements AS t
USING (VALUES
    ('C',  3,  1, NULL),  ('Mn', 3,  2, NULL),  ('P',  3,  3, NULL),  ('S',  3,  4, NULL),  ('Si', 3,  5, NULL),
    ('Cu', 3,  6, NULL),  ('Ni', 3,  7, NULL),  ('Cr', 3,  8, NULL),  ('Mo', 3,  9, NULL),  ('V',  3, 10, NULL),
    ('Nb', 3, 11, NULL),  ('Sn', 3, 12, NULL),  ('Al', 3, 13, NULL),  ('N',  4, 14, NULL),  ('B',  4, 15, NULL),
    ('Ti', 4, 16, NULL),  ('Ca', 4, 17, NULL),  ('Pb', 4, 18, NULL),  ('As', 4, 19, NULL),  ('Sb', 4, 20, NULL),
    ('H',  4, 21, 'ppm'), ('W',  4, 22, NULL),  ('Co', 4, 23, NULL),  ('Zr', 4, 24, NULL),  ('Bi', 4, 25, NULL),  ('O', 4, 26, 'ppm')
) AS s ([ElementName], [Decimals], [SortOrder], [Unit])
ON t.[ElementName] = s.[ElementName]
WHEN MATCHED AND (t.[SortOrder] <> s.[SortOrder] OR t.[Decimals] <> s.[Decimals] OR ISNULL(t.[Unit], '') <> ISNULL(s.[Unit], '')) THEN
    -- Re-align seeded elements to the authoritative SortOrder/Decimals/Unit (auto-created elements are not
    -- in this list, so they are never touched here). Unit is NULL (= weight %) except 'ppm' for H and O.
    UPDATE SET t.[SortOrder] = s.[SortOrder], t.[Decimals] = s.[Decimals], t.[Unit] = s.[Unit]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([ElementName], [Decimals], [SortOrder], [Unit])
    VALUES (s.[ElementName], s.[Decimals], s.[SortOrder], s.[Unit]);

-- HeatPrefixes are the client-confirmed prefixes (in order of use) used to derive the internal
-- (AS400) heat number. They are AUTHORITATIVE: an existing supplier's HeatPrefixes is corrected on
-- match, so re-deploying fixes rows that predate a prefix change. The rest of the row (name) is left
-- untouched, and new suppliers are inserted.
MERGE INTO SUP.catSuppliers AS t
USING (VALUES
    ('ROANOKE',   'Steel Dynamics - Roanoke Bar Division',   '4,3,8'),
    ('SRD',       'Steel Dynamics - Columbia City (SRD)',    '5,6,8'),
    ('PITTSBORO', 'Steel Dynamics - Pittsboro',              '7'),
    ('ARKANSAS',  'Steel Dynamics - Arkansas',               '7'),
    ('ASW',       'ASW',                                     '7')
) AS s ([SupplierCode], [SupplierName], [HeatPrefixes])
ON t.[SupplierCode] = s.[SupplierCode]
WHEN MATCHED AND ISNULL(t.[HeatPrefixes], '') <> s.[HeatPrefixes] THEN
    UPDATE SET [HeatPrefixes] = s.[HeatPrefixes]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([SupplierCode], [SupplierName], [HeatPrefixes])
    VALUES (s.[SupplierCode], s.[SupplierName], s.[HeatPrefixes]);

GO

-- =====================================================================
-- Heat age thresholds seed.
--    Where each level of the "Since Last Billet" column starts -- the days the front end used
--    to hard-code. The levels and their colours are fixed in the front end; only these days
--    are configurable.
-- =====================================================================

-- Insert-only per level, so a plant that has moved a day is never overwritten and a level
-- added later still gets its default on the next deploy.
MERGE INTO [LAB].[HeatAgeThresholds] AS t
USING (VALUES
    ('Watch', 1), ('Warning', 2), ('Critical', 3)
) AS s ([Level], [Days])
ON t.[Level] = s.[Level]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Level], [Days]) VALUES (s.[Level], s.[Days]);

GO

-- =====================================================================
-- Lab Profile.
--    Gets the union of Public + Operator + Supervisor, plus the LAB screens
--    (chemistries report, lab dashboard, tests by section and tensile requirements) with the
--    procedures those screens call.
--    Every step is idempotent, so re-running the script is safe.
-- =====================================================================

-- Create the profile. UpsertProfile throws on a duplicate name, hence the guard.
IF NOT EXISTS (SELECT 1 FROM SEC.catProfiles WHERE ProfileName = 'Lab Profile')
    EXEC [SEC].[UpsertProfile] @ProfileId = NULL, @ProfileName = 'Lab Profile', @User = 'devops';

GO

-- Union of the source profiles' actions. A source profile that does not exist
-- simply contributes nothing, and already granted actions are skipped.
INSERT INTO SEC.catProfilesActions (ProfileId, ActionId)
SELECT lab.ProfileId, src.ActionId
FROM (SELECT ProfileId FROM SEC.catProfiles WHERE ProfileName = 'Lab Profile') lab
CROSS JOIN (SELECT DISTINCT pa.ActionId
            FROM SEC.catProfilesActions pa
            JOIN SEC.catProfiles p ON p.ProfileId = pa.ProfileId
            WHERE p.ProfileName IN ('Public Profile', 'Operator Profile', 'Supervisor Profile')) src
WHERE NOT EXISTS (SELECT 1
                  FROM SEC.catProfilesActions x
                  WHERE x.ProfileId = lab.ProfileId
                    AND x.ActionId = src.ActionId);

GO

-- The LAB screens on top of the union: navbar entries and the procedures they call, for the only
-- two profiles that reach them. UpsertProcedureAndSetToProfile registers the action if it is
-- missing and links it if it is not linked, so every line is idempotent; ui.navbar.* actions are
-- type 'Procedure' just like the SPs, so the same SP fits.

-- navbar
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.chemistries.report', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.chemistries.report', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.chemistries', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.chemistries', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.dashboard', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.dashboard', @Profile = 'Lab Profile'

-- The two Configuration screens the lab reads while working, mirrored read-only under LAB. Each has
-- its own action, so granting them never grants Configuration.
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.tests.by.section', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.tests.by.section', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.tensile.requirements', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'ui.navbar.lab.tensile.requirements', @Profile = 'Lab Profile'

-- chemistry (SUP.GetSuppliers is the supplier selector of the report)
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetChemistryResults', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetChemistryResults', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'SUP.GetSuppliers', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'SUP.GetSuppliers', @Profile = 'Lab Profile'

-- mechanical tests: read, manual correction and disposition
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetHeatMechanicalResults', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetHeatMechanicalResults', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.UpdateHeatMechanicalResult', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.UpdateHeatMechanicalResult', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.InsertMechanicalTestRunDisposition', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.InsertMechanicalTestRunDisposition', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetMechanicalTestRunDispositions', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetMechanicalTestRunDispositions', @Profile = 'Lab Profile'

-- the by-mill and dashboard grids list heats from the campaigns of each mill
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'M1.GetCampaigns', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'M2.GetCampaigns', @Profile = 'Lab Profile'

-- the age alert levels the "Since Last Billet" column paints with, and the configuration screen
-- that edits them (SubAdmin / Builder own that screen, and only read what they write)
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetHeatAgeThresholds', @Profile = 'admin Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetHeatAgeThresholds', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.UpsertHeatAgeThresholds', @Profile = 'admin Profile'

-- tests by section & tensile requirements: read only, they back the two LAB screens above
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetTestsBySectionVersions', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetTestsBySection', @Profile = 'Lab Profile'
EXEC [SEC].[UpsertProcedureAndSetToProfile] @Procedure = 'LAB.GetTensileRequirementsByGrade', @Profile = 'Lab Profile'

GO

-- =====================================================================
-- The lab users. Lab Profile is ADDED to whatever they already have (it is a superset of
-- Public + Operator + Supervisor, so nothing is lost either way).
-- A user that does not exist in this environment is simply skipped.
-- =====================================================================

INSERT INTO SEC.catUsersProfiles (UserId, ProfileId)
SELECT u.UserId, lab.ProfileId
FROM SEC.catUsers u
CROSS JOIN (SELECT ProfileId FROM SEC.catProfiles WHERE ProfileName = 'Lab Profile') lab
WHERE u.UserName IN ('JBorad', 'Lab')
  AND NOT EXISTS (SELECT 1 FROM SEC.catUsersProfiles x
                  WHERE x.UserId = u.UserId AND x.ProfileId = lab.ProfileId);

GO

-- The publish adds foreign keys WITH NOCHECK and revalidates them at the end of the schema step,
-- so this is normally already done by the time we get here. It only bites when a publish left a
-- constraint untrusted (the optimizer ignores it and nothing guarantees the data). No-op otherwise;
-- the backfill that makes them pass lives in PreDeployment.sql, which must run before the schema step.
--
-- Every foreign key of the three tables the mechanical-test refactor pre-creates is covered, not a
-- hand-written list: PreDeployment creates them already populated, so the plan adds ALL of their
-- keys against existing rows (MechanicalTestRuns alone gets one to MT.Heats and one to MT.Sections)
-- and any of them can be the one left untrusted.
DECLARE @Revalidate NVARCHAR(MAX) = N'';

SELECT @Revalidate = @Revalidate
    + N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
    + N'.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';' + CHAR(10)
FROM sys.foreign_keys fk
WHERE fk.is_not_trusted = 1
  AND fk.parent_object_id IN (
      OBJECT_ID('LAB.HeatMechanicalResults'),
      OBJECT_ID('LAB.MechanicalTestRuns'),
      OBJECT_ID('LAB.MechanicalTestRunDispositions')
  );

IF @Revalidate <> N''
BEGIN
    PRINT 'PostDeployment: revalidating untrusted foreign keys: ' + REPLACE(@Revalidate, CHAR(10), ' ');
    EXEC sp_executesql @Revalidate;
END

GO

-- The procedures the mechanical-test refactor replaced. They left the project, and the publish
-- does not drop objects that are no longer in source, so they would otherwise stay behind and
-- fail at run time against tables that no longer exist.
--   LAB.InsertHeatTestDispositions -> LAB.InsertMechanicalTestRunDisposition
--   LAB.DeleteHeatTestDispositions -> LAB.DeleteMechanicalTestRunDisposition
--   M1/M2.GetHeatTestDispositions  -> LAB.GetMechanicalTestRunDispositions (one copy, the runs
--                                     live in LAB and say nothing about which mill rolled the heat)
IF OBJECT_ID('LAB.InsertHeatTestDispositions', 'P') IS NOT NULL
    DROP PROCEDURE [LAB].[InsertHeatTestDispositions];

IF OBJECT_ID('LAB.DeleteHeatTestDispositions', 'P') IS NOT NULL
    DROP PROCEDURE [LAB].[DeleteHeatTestDispositions];

IF OBJECT_ID('M1.GetHeatTestDispositions', 'P') IS NOT NULL
    DROP PROCEDURE [M1].[GetHeatTestDispositions];

IF OBJECT_ID('M2.GetHeatTestDispositions', 'P') IS NOT NULL
    DROP PROCEDURE [M2].[GetHeatTestDispositions];

GO

-- Carry the SEC grants of those procedures over to their replacements. The Lab Profile block above
-- grants the new names to that one profile; this moves whatever OTHER profiles were given the old
-- names in earlier releases (the grants live in the database, and these scripts are wiped after
-- every release, so the list is not knowable from source). Two mill copies collapse onto one LAB
-- procedure, which is why the mapping is many-to-one and the copy is DISTINCT.
DECLARE @ActionRenames TABLE (OldName VARCHAR(100), NewName VARCHAR(100));
INSERT INTO @ActionRenames (OldName, NewName) VALUES
    ('LAB.InsertHeatTestDispositions', 'LAB.InsertMechanicalTestRunDisposition'),
    ('LAB.DeleteHeatTestDispositions', 'LAB.DeleteMechanicalTestRunDisposition'),
    ('M1.GetHeatTestDispositions',     'LAB.GetMechanicalTestRunDispositions'),
    ('M2.GetHeatTestDispositions',     'LAB.GetMechanicalTestRunDispositions');

-- The replacement action, when nothing has registered it yet
INSERT INTO SYSTEM.catActions (ActionName, ActionTypeId)
SELECT DISTINCT ar.NewName, (SELECT ActionTypeId FROM SYSTEM.catActionTypes WHERE Name = 'Procedure')
FROM @ActionRenames ar
WHERE EXISTS (SELECT 1 FROM SYSTEM.catActions a WHERE a.ActionName = ar.OldName)
  AND NOT EXISTS (SELECT 1 FROM SYSTEM.catActions a WHERE a.ActionName = ar.NewName);

-- Every profile that could call the old procedure can call its replacement
INSERT INTO SEC.catProfilesActions (ProfileId, ActionId)
SELECT DISTINCT pa.ProfileId, newA.ActionId
FROM @ActionRenames ar
INNER JOIN SYSTEM.catActions oldA ON oldA.ActionName = ar.OldName
INNER JOIN SYSTEM.catActions newA ON newA.ActionName = ar.NewName
INNER JOIN SEC.catProfilesActions pa ON pa.ActionId = oldA.ActionId
WHERE NOT EXISTS (
    SELECT 1 FROM SEC.catProfilesActions x
    WHERE x.ProfileId = pa.ProfileId AND x.ActionId = newA.ActionId
);

-- Retire the old actions, children first (catProfilesActions and QueryCacheInvalidations both
-- point at catActions)
DELETE pa
FROM SEC.catProfilesActions pa
INNER JOIN SYSTEM.catActions a ON a.ActionId = pa.ActionId
INNER JOIN @ActionRenames ar ON ar.OldName = a.ActionName;

DELETE qci
FROM SYSTEM.QueryCacheInvalidations qci
INNER JOIN SYSTEM.catActions a ON a.ActionId IN (qci.SourceId, qci.TargetId)
INNER JOIN @ActionRenames ar ON ar.OldName = a.ActionName;

DELETE a
FROM SYSTEM.catActions a
INNER JOIN @ActionRenames ar ON ar.OldName = a.ActionName;

PRINT 'PostDeployment: moved SEC grants from the retired disposition procedures to their replacements';

GO

-- =====================================================================
-- M1 Gantt (/m1/gantt): the navbar entry that also guards the route, and the two
-- procedures the screen calls (the gantt itself and the shift header of ShiftChart).
-- Granted to every profile EXCEPT Sales, which only reaches the sales screens.
--
-- The profiles are read from the database rather than spelled out: the list differs per
-- environment and these scripts are wiped after every release, so an "all but Sales" grant
-- is the only form that stays correct. ui.navbar.* actions are of type 'Procedure', same as
-- the SPs, so both go through the same two inserts. Idempotent on both halves.
-- =====================================================================
DECLARE @M1GanttActions TABLE (ActionName VARCHAR(300) PRIMARY KEY);
INSERT INTO @M1GanttActions (ActionName) VALUES
    ('ui.navbar.m1.gantt'),
    ('M1.GetGanttData'),
    ('M1.GetTrendingData');

-- Register the actions that nothing has created yet (GetTrendingData predates the screen,
-- so it normally already exists)
INSERT INTO SYSTEM.catActions (ActionName, ActionTypeId)
SELECT ga.ActionName, (SELECT ActionTypeId FROM SYSTEM.catActionTypes WHERE Name = 'Procedure')
FROM @M1GanttActions ga
WHERE NOT EXISTS (SELECT 1 FROM SYSTEM.catActions a WHERE a.ActionName = ga.ActionName);

INSERT INTO SEC.catProfilesActions (ProfileId, ActionId)
SELECT p.ProfileId, a.ActionId
FROM SEC.catProfiles p
CROSS JOIN SYSTEM.catActions a
WHERE a.ActionName IN (SELECT ActionName FROM @M1GanttActions)
  AND p.ProfileName <> 'Sales Profile'
  AND NOT EXISTS (SELECT 1
                  FROM SEC.catProfilesActions x
                  WHERE x.ProfileId = p.ProfileId AND x.ActionId = a.ActionId);

PRINT 'PostDeployment: granted the M1 gantt actions to every profile except Sales Profile';

GO


EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[DeleteEmailAddress]', 'admin Profile', 'devops'
EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[DeleteEmailTopic]', 'admin Profile', 'devops'
EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[GetEmailAddresses]', 'admin Profile', 'devops'
EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[GetEmailAddressesByTopic]', 'admin Profile', 'devops'
EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[GetEmailTopics]', 'admin Profile', 'devops'
EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[UpsertEmailAddress]', 'admin Profile', 'devops'
EXEC [SEC].[UpsertProcedureAndSetToProfile] '[SYSTEM].[UpsertEmailTopic]', 'admin Profile', 'devops'

GO

-- Address that [SYSTEM].[EnqueueMail] blind-copies on every mail. Empty value disables it.
EXEC [UTL].[SetSetting]
    'ENQUEUE_MAIL_BCC_DEFAULT',
    'ENQUEUE_MAIL_BCC_DEFAULT',
    'swv-mill@dexteel.com',
    'Address always added as BCC by SYSTEM.EnqueueMail. Leave empty to disable.'

GO

-- Turns the Mill #1 shift report mail on now that M1_SHIFT_REPORT has a recipient list.
EXEC [UTL].[SetSetting]
    'M1_SHIFT_REPORT_EMAIL_ENABLED',
    'M1_SHIFT_REPORT_EMAIL_ENABLED',
    '1',
    'Enable Shift Report Email'

GO

-- =============================================================================
-- Seed [SYSTEM].EmailTopics / EmailAddresses / EmailAddressTopic
--
-- The lists come from the per-mill recipient tables this release retires (dropped further
-- down), which had no UI and only ever held the rows below, so they are transcribed here
-- rather than migrated with a query. From here on the customer edits the recipients in the
-- configuration UI, and this block never touches them again.
--
-- Topic names are prefixed with the owning schema because EmailTopicName is globally unique
-- and M1/M2 share resource codes. HEAT_TEST_FAILURE has no prefix: only M2 runs heat tests.
-- M1 never had a legacy list, so M1_SHIFT_REPORT is seeded with the single recipient agreed
-- with the customer; the mail itself still only goes out once M1_SHIFT_REPORT_EMAIL_ENABLED
-- is turned on.
--
-- SWV and PDF_INTAKE topics are internal monitoring and go in with IsSystem = 1 so they stay
-- hidden from the customer. Their only legacy recipient was swv-mill@dexteel.com, which is
-- deliberately left out: SYSTEM.EnqueueMail blind-copies it on every mail
-- (ENQUEUE_MAIL_BCC_DEFAULT above), and promotes it to TO when the topic has none, which is how
-- those topics already reach it today.
--
-- Guarded as a whole, not row by row: this file re-runs on every deployment, and the
-- inserts below would collide with their UNIQUE constraints the second time around.
-- =============================================================================
IF NOT EXISTS (SELECT 1 FROM [SYSTEM].[EmailTopics])
BEGIN
    INSERT INTO [SYSTEM].[EmailTopics] ([EmailTopicName], [Description], [IsSystem], [DelayTimeMinutes])
    VALUES
        ('M1_CALENDAR_PUBLISH',     'Sent when the Mill #1 production calendar is published, with the publisher note and how many events went out.', 0, NULL),
        ('M2_CALENDAR_PUBLISH',     'Sent when the Mill #2 production calendar is published, with the publisher note and how many events went out.', 0, NULL),
        ('M1_EVENT_AUTO_CREATED',   'Sent when a Mill #1 production event is created automatically from a campaign.', 0, NULL),
        ('M2_EVENT_AUTO_CREATED',   'Sent when a Mill #2 production event is created automatically from a campaign.', 0, NULL),
        ('HEAT_TEST_FAILURE',       'Sent when a heat fails its mechanical test, as the lab results come in.', 0, NULL),
        ('M1_SHIFT_REPORT',         'Mill #1 shift report PDF, emailed automatically once the shift ends.', 0, 60),
        ('M2_SHIFT_REPORT',         'Mill #2 shift report PDF, emailed automatically once the shift ends.', 0, 60),
        ('SWV_ALERTS',              'Internal monitoring: hourly digest of every alert detected, one mail per run.', 1, NULL),
        ('SWV_WARNINGS',            'Internal monitoring: daily digest of lower-urgency warnings, delivered at 09:00 local.', 1, NULL),
        ('SWV_HISTORIAN_TAG_STALE', 'Internal monitoring: a historian tag stopped updating for longer than its threshold. Covers both mills in one mail.', 1, NULL),
        ('PDF_INTAKE_NEW_ELEMENT',  'Internal monitoring: the PDF intake found an element outside the curated catalog and auto-created it in LAB.catElements.', 1, NULL),
        ('PDF_INTAKE_DEAD_LETTER',  'Internal monitoring: a certificate could not be loaded into the database and was moved to the error folder.', 1, NULL),
        ('PDF_INTAKE_PARSE_ERROR',  'Internal monitoring: a PDF could not be parsed after the maximum number of attempts and was set aside for manual review.', 1, NULL);

    INSERT INTO [SYSTEM].[EmailAddresses] ([Address])
    VALUES
        ('Roger.Hutchinson@steeldynamics.com'),
        ('jsagasti@dexteel.com'),
        ('eramadori@dexteel.com'),
        ('lrueda@dexteel.com'),
        ('Jay.Borad@steeldynamics.com'),
        ('CHamilton@swvaky.com'),
        ('colton.ferguson@steeldynamics.com'),
        ('Drake.Robertson@steeldynamics.com'),
        ('Jason.Murdock@steeldynamics.com'),
        ('Jason.rulen@steeldynamics.com'),
        ('Jason.Smith@steeldynamics.com'),
        ('Michael.Longfellow@steeldynamics.com'),
        ('Nick.Clay@steeldynamics.com'),
        ('Nick.Johnson@steeldynamics.com'),
        ('Roger.Sowards@steeldynamics.com'),
        ('Tim.Hamlin@steeldynamics.com'),
        ('Wilmer.Napier@steeldynamics.com');

    INSERT INTO [SYSTEM].[EmailAddressTopic] ([EmailAddressId], [EmailTopicId], [RecipientType])
    SELECT
        ea.[EmailAddressId],
        et.[EmailTopicId],
        v.[RecipientType]
    FROM (VALUES
        ('M1_CALENDAR_PUBLISH',   'TO', 'Roger.Hutchinson@steeldynamics.com'),
        ('M2_CALENDAR_PUBLISH',   'TO', 'Roger.Hutchinson@steeldynamics.com'),
        ('M2_CALENDAR_PUBLISH',   'TO', 'jsagasti@dexteel.com'),
        ('M2_CALENDAR_PUBLISH',   'TO', 'lrueda@dexteel.com'),
        ('M1_EVENT_AUTO_CREATED', 'TO', 'Roger.Hutchinson@steeldynamics.com'),
        ('M2_EVENT_AUTO_CREATED', 'TO', 'Roger.Hutchinson@steeldynamics.com'),
        ('HEAT_TEST_FAILURE',     'TO', 'Roger.Hutchinson@steeldynamics.com'),
        ('HEAT_TEST_FAILURE',     'TO', 'Jay.Borad@steeldynamics.com'),
        ('M1_SHIFT_REPORT',       'TO', 'eramadori@dexteel.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Roger.Hutchinson@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'CHamilton@swvaky.com'),
        ('M2_SHIFT_REPORT',       'TO', 'colton.ferguson@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Drake.Robertson@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Jason.Murdock@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Jason.rulen@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Jason.Smith@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Michael.Longfellow@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Nick.Clay@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Nick.Johnson@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Roger.Sowards@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Tim.Hamlin@steeldynamics.com'),
        ('M2_SHIFT_REPORT',       'TO', 'Wilmer.Napier@steeldynamics.com')
    ) v ([EmailTopicName], [RecipientType], [Address])
    INNER JOIN [SYSTEM].[EmailTopics] et ON et.[EmailTopicName] = v.[EmailTopicName]
    INNER JOIN [SYSTEM].[EmailAddresses] ea ON ea.[Address] = v.[Address];
END

GO

IF EXISTS (SELECT 1 FROM UTL.catSettings WHERE [SettingName] = 'SHIFT_REPORT_EMAIL_DELAY_MINUTES')
    EXEC [UTL].[DeleteSetting] @SettingName = 'SHIFT_REPORT_EMAIL_DELAY_MINUTES', @User = 'devops';

GO

-- =============================================================================
-- Retire the per-mill recipient lists the SYSTEM.EmailTopics seed above replaced.
--
--   [M1] / [M2] / [SWV].EmailRecipientsResources        -> [SYSTEM].[EmailAddressTopic]
--   [M1] / [M2] / [SWV].GetEmailRecipientsResources     -> [SYSTEM].[GetEmailAddressesByTopic]
--   [M1] / [M2] / [SWV].UpsertEmailRecipientsResources  -> [SYSTEM].[UpsertEmailAddress]
-- =============================================================================
IF OBJECT_ID('M1.GetEmailRecipientsResources', 'P') IS NOT NULL
    DROP PROCEDURE [M1].[GetEmailRecipientsResources];

IF OBJECT_ID('M1.UpsertEmailRecipientsResources', 'P') IS NOT NULL
    DROP PROCEDURE [M1].[UpsertEmailRecipientsResources];

IF OBJECT_ID('M2.GetEmailRecipientsResources', 'P') IS NOT NULL
    DROP PROCEDURE [M2].[GetEmailRecipientsResources];

IF OBJECT_ID('M2.UpsertEmailRecipientsResources', 'P') IS NOT NULL
    DROP PROCEDURE [M2].[UpsertEmailRecipientsResources];

IF OBJECT_ID('SWV.GetEmailRecipientsResources', 'P') IS NOT NULL
    DROP PROCEDURE [SWV].[GetEmailRecipientsResources];

IF OBJECT_ID('SWV.UpsertEmailRecipientsResources', 'P') IS NOT NULL
    DROP PROCEDURE [SWV].[UpsertEmailRecipientsResources];

IF OBJECT_ID('M1.EmailRecipientsResources', 'U') IS NOT NULL
    DROP TABLE [M1].[EmailRecipientsResources];

IF OBJECT_ID('M2.EmailRecipientsResources', 'U') IS NOT NULL
    DROP TABLE [M2].[EmailRecipientsResources];

IF OBJECT_ID('SWV.EmailRecipientsResources', 'U') IS NOT NULL
    DROP TABLE [SWV].[EmailRecipientsResources];

GO

-- The SEC actions of those procedures, with the grants that hang off them. Unlike the
-- disposition procedures above there is nothing to carry over: the replacements are the
-- SYSTEM.* email procedures, already granted where they are needed. Matched with the brackets
-- stripped, because the actions were registered both ways over the releases.
DECLARE @RetiredActions TABLE (ActionName VARCHAR(100));
INSERT INTO @RetiredActions (ActionName) VALUES
    ('M1.GetEmailRecipientsResources'),
    ('M1.UpsertEmailRecipientsResources'),
    ('M2.GetEmailRecipientsResources'),
    ('M2.UpsertEmailRecipientsResources'),
    ('SWV.GetEmailRecipientsResources'),
    ('SWV.UpsertEmailRecipientsResources');

DECLARE @RetiredActionIds TABLE (ActionId INT PRIMARY KEY);
INSERT INTO @RetiredActionIds (ActionId)
SELECT a.ActionId
FROM SYSTEM.catActions a
INNER JOIN @RetiredActions ra
    ON ra.ActionName = REPLACE(REPLACE(a.ActionName, '[', ''), ']', '');

-- Children first (catProfilesActions and QueryCacheInvalidations both point at catActions)
DELETE pa
FROM SEC.catProfilesActions pa
INNER JOIN @RetiredActionIds r ON r.ActionId = pa.ActionId;

DELETE qci
FROM SYSTEM.QueryCacheInvalidations qci
INNER JOIN @RetiredActionIds r ON r.ActionId IN (qci.SourceId, qci.TargetId);

DELETE a
FROM SYSTEM.catActions a
INNER JOIN @RetiredActionIds r ON r.ActionId = a.ActionId;

PRINT 'PostDeployment: dropped the EmailRecipientsResources tables/procedures and their SEC actions';

GO

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.mill.incentive.dashboard', 'Builder Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'Builder Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'Builder Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetIncentiveByWeek', 'Builder Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.mill.incentive.dashboard', 'Operator Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'Operator Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'Operator Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetIncentiveByWeek', 'Operator Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.mill.incentive.dashboard', 'Public Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'Public Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'Public Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetIncentiveByWeek', 'Public Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.mill.incentive.dashboard', 'Scheduler Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'Scheduler Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'Scheduler Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetIncentiveByWeek', 'Scheduler Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.mill.incentive.dashboard', 'Test Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'Test Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'Test Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetIncentiveByWeek', 'Test Profile', 'devops'
