SET QUOTED_IDENTIFIER ON

GO

exec SEC.UpsertProcedureAndSetToProfile '[M2].[GetCampaignDelays]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[M2].[GetHeatTestDispositions]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[M2].[InsertHeatTestDispositions]', 'admin Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile '[SYSTEM].[GetLogTags]', 'admin Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[SYSTEM].[GetLogTagValues]', 'admin Profile', 'devops'

exec SEC.UpsertProcedureAndSetToProfile '[M2].[GetTensileRequirementsByGrade]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[M2].[GetTensileRequirementVersions]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[M2].[UpsertTensileRequirements]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[M2].[GetHeatMechanicalResults]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[M2].[SyncHeatMechanicalResultsJob]', 'admin Profile', 'devops'


-- =============================================================
-- Seed data for M2.TensileRequirementVersions + TensileRequirementsByGrade
-- Source: Table 15 - TENSILE REQUIREMENTS BY GRADE (REV 31- 05/08/2025)
--
-- Step 1: Ensure all required grades exist in M2.catGrades
-- Step 2: Insert version record
-- Step 3: Insert requirements referencing VersionId
-- =============================================================

-- =============================================
-- STEP 1: Insert missing grades into catGrades
-- =============================================

-- All SWV numbers from the PDF
DECLARE @SWVNumbers TABLE (SWV INT);
INSERT INTO @SWVNumbers VALUES
    (1),(2),(3),(4),(6),(7),(8),(9),(10),(12),(13),(14),(15),(16),(17),(18),(19),(20),
    (21),(23),(24),(25),(26),(27),(30),(31),(37),(38),(39),(41),(42),(43),(44),(46),
    (49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(63),(64),(65),(66),
    (67),(68),(70),(72),(75),(76),(85),(90),(97),(98);

INSERT INTO M2.catGrades (GradeName, PLCName)
SELECT
    'SWV' + RIGHT('000' + CAST(s.SWV AS VARCHAR(3)), 3),
    CAST(s.SWV AS VARCHAR(50))
FROM @SWVNumbers s
WHERE NOT EXISTS (
    SELECT 1 FROM M2.catGrades g
    WHERE g.GradeName = 'SWV' + RIGHT('000' + CAST(s.SWV AS VARCHAR(3)), 3)
);

PRINT 'Inserted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' new grades into M2.catGrades';

-- =============================================
-- STEP 2: Clear and insert Version
-- =============================================

DELETE FROM [M2].[TensileRequirementsByGrade];
DBCC CHECKIDENT ('[M2].[TensileRequirementsByGrade]', RESEED, 0);

DELETE FROM [M2].[TensileRequirementVersions];
DBCC CHECKIDENT ('[M2].[TensileRequirementVersions]', RESEED, 0);

INSERT INTO [M2].[TensileRequirementVersions] ([Rev], [EffectiveDate], [Comments], [CreatedBy])
VALUES (31, '2025-05-08', '* Minimum may vary with section rolled.
*uncontrolled when printed', 'SEED');

DECLARE @VersionId INT = SCOPE_IDENTITY();

PRINT 'Inserted version Rev 31 (Id = ' + CAST(@VersionId AS VARCHAR) + ')';

-- =============================================
-- STEP 3: Insert TensileRequirements for Version
-- =============================================

INSERT INTO [M2].[TensileRequirementsByGrade]
    ([TensileRequirementVersionId], [GradeId], [Yield], [Tensile], [Elongation], [PercentRA], [Impact], [TestsPerHeat],
     [YieldMin], [YieldMax], [TensileMin], [TensileMax], [ElongationMin], [ElongationMax],
     [PercentRAMin], [PercentRAMax], [ImpactMin], [ImpactMax])
SELECT @VersionId, g.GradeId, v.Yield, v.Tensile, v.Elongation, v.PercentRA, v.Impact, v.TestsPerHeat,
       v.YieldMin, v.YieldMax, v.TensileMin, v.TensileMax, v.ElongationMin, NULL,
       v.PercentRAMin, NULL, v.ImpactMin, NULL
FROM (VALUES
    (1,  '60 ksi Min',       '75 ksi Min',         '16% Min (8")*',      NULL,       NULL,                    '2 Pull',                60,    NULL,  75,    NULL,  16,   NULL, NULL),
    (2,  '36 ksi Min',       '58 - 80 ksi',        '20% Min (8") *',     NULL,       NULL,                    '2 Pulls',               36,    NULL,  58,    80,    20,   NULL, NULL),
    (3,  '65.4 ksi Min',     '82.5 - 103.7 ksi',   '26% Min (2")',       NULL,       NULL,                    '1 Pull',                65.4,  NULL,  82.5,  103.7, 26,   NULL, NULL),
    (4,  '50 ksi Min',       '70 ksi Min',         '18% Min (8") *',     NULL,       NULL,                    '2 Pulls',               50,    NULL,  70,    NULL,  18,   NULL, NULL),
    (6,  '50 ksi Min',       '65 ksi Min',         '18% Min (8") *',     NULL,       NULL,                    '2 Pulls',               50,    NULL,  65,    NULL,  18,   NULL, NULL),
    (7,  '65 ksi Min',       '80 ksi Min',         '15% Min (8") *',     NULL,       NULL,                    '2 Pulls',               65,    NULL,  80,    NULL,  15,   NULL, NULL),
    (8,  '70 ksi Min',       '90 ksi Min',         '12% Min (2")',       NULL,       NULL,                    '1 Pull',                70,    NULL,  90,    NULL,  12,   NULL, NULL),
    (9,  '70 ksi Min',       '100 ksi Min',        '12% Min (2")',       '25% min',  NULL,                    '1 Pull + Bend Test',    70,    NULL,  100,   NULL,  12,   25,   NULL),
    (10, '42 ksi Min',       '63 ksi Min',         '20% Min (2")',       NULL,       NULL,                    '1 Pull',                42,    NULL,  63,    NULL,  20,   NULL, NULL),
    (12, '36 ksi Min',       '58 - 80 ksi',        '20.0% Min (8")',     NULL,       NULL,                    '1 Pull',                36,    NULL,  58,    80,    20,   NULL, NULL),
    (13, '73.9-95 ksi',      '99.5 ksi Min',       '20% Min (2")',       NULL,       NULL,                    '1 Pull',                73.9,  95,    99.5,  NULL,  20,   NULL, NULL),
    (14, '50 ksi Min',       '65 ksi Min',         '18% Min (8")',       NULL,       NULL,                    '1 Pull',                50,    NULL,  65,    NULL,  18,   NULL, NULL),
    (15, NULL,               NULL,                  NULL,                 NULL,       NULL,                    'Grain Size Only',       NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (16, NULL,               NULL,                  NULL,                 NULL,       NULL,                    NULL,                    NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (17, '70 ksi Min',       '90 ksi Min',         '12% Min (2")',       NULL,       NULL,                    '1 Pull',                70,    NULL,  90,    NULL,  12,   NULL, NULL),
    (18, NULL,               NULL,                  NULL,                 NULL,       NULL,                    'Grain Size Only',       NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (19, '50-65 ksi',        '65 - 80 ksi',        '18% Min (8")*',     NULL,       NULL,                    '2 Pulls',               50,    65,    65,    80,    18,   NULL, NULL),
    (20, '70 ksi Min',       '90 ksi Min',         '12% Min (2")',       NULL,       NULL,                    '1 Pull',                70,    NULL,  90,    NULL,  12,   NULL, NULL),
    (21, '50 ksi Min',       '70 - 80 ksi',        '20% Min (8")',       NULL,       NULL,                    '2 Pulls',               50,    NULL,  70,    80,    20,   NULL, NULL),
    (23, '50-65 ksi',        '65 ksi Min',         '18% Min (8")*',     NULL,       NULL,                    '2 Pulls',               50,    65,    65,    NULL,  18,   NULL, NULL),
    (24, '77 ksi Min',       '110 ksi Min',        '12% Min (2")',       '25% Min',  NULL,                    '1 Pull + Bend Test',    77,    NULL,  110,   NULL,  12,   25,   NULL),
    (25, '60 ksi Min',       NULL,                  NULL,                 NULL,       NULL,                    '1 Pull + Grain Size',   60,    NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (26, '72 ksi Min',       '94-116 ksi',         '18% Min (2")',       NULL,       '15 lb-ft Min @ -4° F', '1 Pull + Impacts',      72,    NULL,  94,    116,   18,   NULL, 15),
    (27, '70 ksi Min',       '100 ksi Min',        '12% Min (2")',       '25% min',  NULL,                    '1 Pull + Bend Test',    70,    NULL,  100,   NULL,  12,   25,   NULL),
    (30, '355 N/mm² Min',    '490 - 620 N/mm²',    '21% Min (200mm)',    NULL,       '34 J Min @ 0° C',      '2 Pulls + Impacts',     51.5,  NULL,  71.1,  89.9,  21,   NULL, 25.1),
    (31, '62.6-85 ksi',      '85.3 ksi Min',       '25% Min (2")',       NULL,       NULL,                    '1 Pull',                62.6,  85,    85.3,  NULL,  25,   NULL, NULL),
    (37, NULL,               NULL,                  NULL,                 NULL,       NULL,                    'Hardness- 201 Min',     NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (38, '50-65 ksi',        '65-80 ksi',          '20% Min (8")*',     NULL,       NULL,                    '2 Pulls',               50,    65,    65,    80,    20,   NULL, NULL),
    (39, '50 ksi Min',       '65 - 100 ksi',       '18% Min (8") *',    NULL,       NULL,                    '2 Pulls',               50,    NULL,  65,    100,   18,   NULL, NULL),
    (41, '80 ksi Min',       NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                80,    NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (42, '65.4 ksi Min',     '82.5-103.7 ksi',     '26% Min (2")',       NULL,       NULL,                    '1 Pull',                65.4,  NULL,  82.5,  103.7, 26,   NULL, NULL),
    (43, '70 ksi Min',       NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                70,    NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (44, '60 ksi Min',       '78 ksi Min',         '20% Min (2")',       NULL,       NULL,                    '1 Pull',                60,    NULL,  78,    NULL,  20,   NULL, NULL),
    (46, '50-65 ksi',        '65 - 80 ksi',        '20% Min (8")*',     NULL,       NULL,                    '2 Pulls',               50,    65,    65,    80,    20,   NULL, NULL),
    (49, NULL,               NULL,                  NULL,                 NULL,       NULL,                    'Hardness-220 min BHN',  NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (50, '30 ksi Min',       '60-72 ksi',          '22% Min (2")*',     NULL,       NULL,                    '2 Pull',                30,    NULL,  60,    72,    22,   NULL, NULL),
    (51, NULL,               NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (52, '36 ksi Min',       '58-80 ksi',          '20% Min (8")*',     NULL,       NULL,                    '2 Pulls',               36,    NULL,  58,    80,    20,   NULL, NULL),
    (53, '51 ksi Min',       '71-91 ksi',          '22% Min (2")',       NULL,       NULL,                    '1 Pull',                51,    NULL,  71,    91,    22,   NULL, NULL),
    (54, '50 ksi Min',       '82.5 ksi Min',       '18% Min (2")',       NULL,       NULL,                    '1 Pull',                50,    NULL,  82.5,  NULL,  18,   NULL, NULL),
    (55, '235 N/mm² Min',    '400 - 520 N/mm²',    '22% Min (200 mm)',   NULL,       '27 J Min @ -20° C',    '2 Pulls + Impacts',     34.1,  NULL,  58.0,  75.4,  22,   NULL, 19.9),
    (56, '30 ksi Min',       NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                30,    NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (57, '64 ksi Min',       '78 - 102 ksi',       '18% Min (2")',       NULL,       '20 ft-lb Min @ -20°C', '2 Pulls + Impacts',     64,    NULL,  78,    102,   18,   NULL, 20),
    (58, '88 ksi Min',       '125 ksi Min',        '12% Min (2")',       '25% Min',  NULL,                    '1 Pull + Bend Test',    88,    NULL,  125,   NULL,  12,   25,   NULL),
    (59, '50 ksi Min',       '63.5-80 ksi',        '18% Min (8")',       NULL,       NULL,                    '2 Pulls',               50,    NULL,  63.5,  80,    18,   NULL, NULL),
    (60, '235 N/mm² Min',    '400 - 520 N/mm²',    '22% Min (200 mm)',   NULL,       '27 J Min @ 0° C',      '2 Pulls + Impacts',     34.1,  NULL,  58.0,  75.4,  22,   NULL, 19.9),
    (63, NULL,               NULL,                  NULL,                 NULL,       NULL,                    '1 Pull (505) + Bend Test', NULL, NULL, NULL, NULL,  NULL, NULL, NULL),
    (64, '80 ksi Min',       '100 ksi Min',        '13% Min (8")',       NULL,       NULL,                    '1 Pull',                80,    NULL,  100,   NULL,  13,   NULL, NULL),
    (65, '50 ksi Min',       '65 - 80 ksi',        '20% Min (8") *',    NULL,       NULL,                    '2 Pulls',               50,    NULL,  65,    80,    20,   NULL, NULL),
    (66, '235 N/mm² Min',    '400 - 520 N/mm²',    '22% Min (200 mm)',   NULL,       NULL,                    '2 Pulls',               34.1,  NULL,  58.0,  75.4,  22,   NULL, NULL),
    (67, '36 ksi Min',       '58 - 80 ksi',        '20% Min (8") *',    NULL,       NULL,                    '2 Pulls',               36,    NULL,  58,    80,    20,   NULL, NULL),
    (68, '63 ksi Min',       '115 ksi Min',        NULL,                 NULL,       NULL,                    '1 Pull',                63,    NULL,  115,   NULL,  NULL, NULL, NULL),
    (70, '70 ksi Min',       NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                70,    NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (72, '24 ksi Min',       '43 ksi Min',         '25% Min (2")',       NULL,       NULL,                    'Hardness 90Min & 1 Pull', 24,  NULL,  43,    NULL,  25,   NULL, NULL),
    (75, '70 ksi Min',       NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                70,    NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (76, NULL,               '156 ksi Min',        '8% (2")',            NULL,       NULL,                    'Hardness 320-360 & 1 Pull', NULL, NULL, 156,  NULL,  8,    NULL, NULL),
    (85, '235 N/mm² Min',    '400 - 520 N/mm²',    '22% Min (200 mm)',   NULL,       '27 J Min @ -40° C',    '2 Pulls + Impacts',     34.1,  NULL,  58.0,  75.4,  22,   NULL, 19.9),
    (90, NULL,               NULL,                  NULL,                 NULL,       NULL,                    'Hardness 285 Min',      NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (97, NULL,               NULL,                  NULL,                 NULL,       NULL,                    '1 Pull',                NULL,  NULL,  NULL,  NULL,  NULL, NULL, NULL),
    (98, '50 ksi Min',       '65 - 80 ksi',        '21% Min (2") *',    NULL,       NULL,                    '2 Pull',                50,    NULL,  65,    80,    21,   NULL, NULL)
) AS v(SWV, Yield, Tensile, Elongation, PercentRA, Impact, TestsPerHeat, YieldMin, YieldMax, TensileMin, TensileMax, ElongationMin, PercentRAMin, ImpactMin)
INNER JOIN M2.catGrades g ON g.GradeName = 'SWV' + RIGHT('000' + CAST(v.SWV AS VARCHAR(3)), 3)
ORDER BY v.SWV;

PRINT 'Inserted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows into M2.TensileRequirementsByGrade';
GO

-- =============================================================
-- One-time fix: Convert existing HeatMechanicalResults from PSI to ksi
-- Lab reports Yield/Tensile in PSI, our requirements are in ksi (1 ksi = 1000 PSI)
-- Only convert values > 500 to avoid re-converting already fixed data
-- =============================================================
UPDATE M2.HeatMechanicalResults
SET YieldValue = YieldValue / 1000.0
WHERE YieldValue > 500 AND IsActive = 1;

UPDATE M2.HeatMechanicalResults
SET TensileValue = TensileValue / 1000.0
WHERE TensileValue > 500 AND IsActive = 1;

PRINT 'Converted existing HeatMechanicalResults Yield/Tensile from PSI to ksi';
GO

-- =============================================================
-- Initial sync: from earliest HeatUsagePeriod to now
-- =============================================================
DECLARE @SyncStart DATETIME;
DECLARE @SyncEnd DATETIME = GETUTCDATE();

SELECT TOP 1 @SyncStart = Start FROM M2.HeatUsagePeriods ORDER BY Start ASC;

IF @SyncStart IS NOT NULL
BEGIN
    PRINT 'Running initial sync from ' + CONVERT(VARCHAR(23), @SyncStart, 121) + ' to ' + CONVERT(VARCHAR(23), @SyncEnd, 121);
    EXEC [M2].[SyncHeatMechanicalResultsJob] @Start = @SyncStart, @End = @SyncEnd;
END
ELSE
BEGIN
    PRINT 'No HeatUsagePeriods found, skipping initial sync';
END
GO

-- =============================================================
-- Register SyncHeatMechanicalResultsJob in SYSTEM.catJobs
-- Runs every 10 minutes (600 seconds) to sync new mechanical samples
-- =============================================================
EXEC [SYSTEM].InitializeJob
    @sProcedure = 'M2.SyncHeatMechanicalResultsJob',
    @PeriodTime = 600,
    @RestartAfterFailure = 1,
    @Enabled = 1;
GO

