SET QUOTED_IDENTIFIER ON

GO

INSERT INTO SEC.catProfilesActions (ProfileId, ActionId)
SELECT p.ProfileId, act.ActionId
FROM SEC.catProfiles p
CROSS JOIN (
    SELECT a.ActionId
    FROM SYSTEM.catActions a
    INNER JOIN (VALUES
        ('ui.navbar.mill.stacker.production'),
        ('ui.navbar.m1.mill.stacker.production'),
        ('M2.GetStackerReportByShift'),
        ('M1.GetStackerReportByShift'),
        ('M2.UpsertStackerShiftRemark'),
        ('M2.UpsertStackerBundle'),
        ('M2.DeleteStackerBundle'),
        ('M1.UpsertStackerShiftRemark'),
        ('M1.UpsertStackerBundle'),
        ('M1.DeleteStackerBundle'),
        ('M2.GetSections'),
        ('M1.GetSections'),
        ('M2.GetHeats'),
        ('M1.GetHeats'),
        ('MT.GetGrades'),
        ('SEC.GetUser')
    ) AS wanted(ActionName) ON wanted.ActionName = a.ActionName
) AS act
WHERE p.ProfileName NOT IN ('Scheduler Profile', 'Sales Profile', 'Test Profile') -- exclude Scheduler, Sales & Test
  AND NOT EXISTS (SELECT 1
                  FROM SEC.catProfilesActions pa
                  WHERE pa.ProfileId = p.ProfileId
                    AND pa.ActionId = act.ActionId);

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'Supervisor Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'Supervisor Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.m1.incentive.dashboard', 'SubAdmin Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M1.GetIncentiveByWeek', 'SubAdmin Profile', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.mill.incentive.dashboard', 'SubAdmin Profile', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetIncentiveByWeek', 'SubAdmin Profile', 'devops'

DELETE pa
FROM SEC.catProfilesActions pa
INNER JOIN SYSTEM.catActions a ON a.ActionId = pa.ActionId
INNER JOIN SEC.catProfiles p ON p.ProfileId = pa.ProfileId
WHERE p.ProfileName = 'Supervisor Profile'
  AND a.ActionName IN (
      'ui.navbar.mill.incentive.manager',
      'ui.incentive.edit',
      'M2.GetIncentiveReport',
      'M2.UpsertIncentiveOverride'
  )

GO

exec SEC.UpsertProcedureAndSetToProfile '[LAB].[UpsertTestsBySection]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[DeleteTestsBySection]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[GetTestsBySection]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[GetTestsBySectionVersions]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[CreateTestsBySectionVersion]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[PublishTestsBySectionVersion]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[DeleteTestsBySectionVersion]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[MT].[GetAllSections]', 'admin Profile', 'devops'


SET NOCOUNT ON;

DECLARE @Rev           INT          = 31;
DECLARE @EffectiveDate DATETIME     = '2025-05-08';
DECLARE @Comments      VARCHAR(MAX) = 'Rev 31 - Table 15 Tensile Requirements by Grade. Loaded from controlled PDF dated 05/08/2025.';

BEGIN TRY
BEGIN TRANSACTION;

/*------------------------------------------------------------------
  1. STAGING: data exactly as printed in the PDF
------------------------------------------------------------------*/
DECLARE @Src1 TABLE (
    Swv                  INT           NOT NULL,
    [Yield]              VARCHAR(50)   NULL,
    Tensile              VARCHAR(50)   NULL,
    Elongation           VARCHAR(50)   NULL,
    PercentRA            VARCHAR(50)   NULL,
    Impact               VARCHAR(100)  NULL,
    TestsPerHeat         VARCHAR(100)  NULL,
    YieldMin             REAL          NULL,
    YieldMax             REAL          NULL,
    TensileMin           REAL          NULL,
    TensileMax           REAL          NULL,
    Unit                 VARCHAR(20)   NULL,
    ElongationPercentMin REAL          NULL,
    ElongationLength     VARCHAR(50)   NULL,
    PercentRAMin         REAL          NULL,
    PercentRAMax         REAL          NULL,
    ImpactMin            REAL          NULL,
    ImpactMax            REAL          NULL
);

INSERT INTO @Src1
 (Swv, [Yield], Tensile, Elongation, PercentRA, Impact, TestsPerHeat,
  YieldMin, YieldMax, TensileMin, TensileMax, Unit,
  ElongationPercentMin, ElongationLength, PercentRAMin, PercentRAMax, ImpactMin, ImpactMax)
VALUES
 ( 1,'60 ksi Min','75 ksi Min','16% Min (8")*',NULL,NULL,'2 Pull',                              60,NULL,75,NULL,'ksi',16,'8"',NULL,NULL,NULL,NULL),
 ( 2,'36 ksi Min','58 - 80 ksi','20% Min (8") *',NULL,NULL,'2 Pulls',                           36,NULL,58,80,'ksi',20,'8"',NULL,NULL,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,'ksi',26,'2"',NULL,NULL,NULL,NULL),
 ( 4,'50 ksi Min','70 ksi Min','18% Min (8") *',NULL,NULL,'2 Pulls',                            50,NULL,70,NULL,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 ( 6,'50 ksi Min','65 ksi Min','18% Min (8") *',NULL,NULL,'2 Pulls',                            50,NULL,65,NULL,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 ( 7,'65 ksi Min','80 ksi Min','15% Min (8") *',NULL,NULL,'2 Pulls',                            65,NULL,80,NULL,'ksi',15,'8"',NULL,NULL,NULL,NULL),
 ( 8,'70 ksi Min','90 ksi Min','12% Min (2")',NULL,NULL,'1 Pull',                               70,NULL,90,NULL,'ksi',12,'2"',NULL,NULL,NULL,NULL),
 ( 9,'70 ksi Min','100 ksi Min','12% Min (2")','25% Min',NULL,'1 Pull + Bend Test',             70,NULL,100,NULL,'ksi',12,'2"',25,NULL,NULL,NULL),
 (10,'42 ksi Min','63 ksi Min','20% Min (2")',NULL,NULL,'1 Pull',                               42,NULL,63,NULL,'ksi',20,'2"',NULL,NULL,NULL,NULL),
 (12,'36 ksi Min','58 - 80 ksi','20.0% Min (8")',NULL,NULL,'1 Pull',                            36,NULL,58,80,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (13,'73.9-95 ksi','99.5 ksi Min','20% Min (2")',NULL,NULL,'1 Pull',                            73.9,95,99.5,NULL,'ksi',20,'2"',NULL,NULL,NULL,NULL),
 (14,'50 ksi Min','65 ksi Min','18% Min (8")',NULL,NULL,'1 Pull',                               50,NULL,65,NULL,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 (15,NULL,NULL,NULL,NULL,NULL,'Grain Size Only',                                               NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (16,NULL,NULL,NULL,NULL,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,'ksi',12,'2"',NULL,NULL,NULL,NULL),
 (18,NULL,NULL,NULL,NULL,NULL,'Grain Size Only',                                               NULL,NULL,NULL,NULL,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,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 (20,'70 ksi Min','90 ksi Min','12% Min (2")',NULL,NULL,'1 Pull',                               70,NULL,90,NULL,'ksi',12,'2"',NULL,NULL,NULL,NULL),
 (21,'50 ksi Min','70 - 80 ksi','20 % Min (8")',NULL,NULL,'2 Pulls',                            50,NULL,70,80,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (23,'50-65 ksi','65 ksi Min','18% Min (8")*',NULL,NULL,'2 Pulls',                              50,65,65,NULL,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 (24,'77 ksi Min','110 ksi Min','12 % Min (2")','25% Min',NULL,'1 Pull + Bend Test',            77,NULL,110,NULL,'ksi',12,'2"',25,NULL,NULL,NULL),
 (25,'60 ksi Min',NULL,NULL,NULL,NULL,'1 Pull + grain size',                                    60,NULL,NULL,NULL,'ksi',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,'ksi',18,'2"',NULL,NULL,15,NULL),
 (27,'70 ksi Min','100 ksi Min','12% Min (2")','25% Min',NULL,'1 Pull + Bend Test',             70,NULL,100,NULL,'ksi',12,'2"',25,NULL,NULL,NULL),
 (30,'355 N/mm2 Min','490 - 620 N/mm2','21% Min (200mm)',NULL,'34 J Min @ 0 C','2 Pulls + Impacts', 355,NULL,490,620,'N/mm2',21,'200 mm',NULL,NULL,34,NULL),
 (31,'62.6-85 ksi','85.3 ksi Min','25% Min (2")',NULL,NULL,'1 Pull',                            62.6,85,85.3,NULL,'ksi',25,'2"',NULL,NULL,NULL,NULL),
 (37,NULL,NULL,NULL,NULL,NULL,'Hardness - 201 Min',                                            NULL,NULL,NULL,NULL,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,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (39,'50 ksi Min','65 - 100 ksi','18% Min (8") *',NULL,NULL,'2 Pulls',                          50,NULL,65,100,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 (41,'80 ksi Min',NULL,NULL,NULL,NULL,'2 Pull',                                                 80,NULL,NULL,NULL,'ksi',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,'ksi',26,'2"',NULL,NULL,NULL,NULL),
 (43,'70 ksi Min',NULL,NULL,NULL,NULL,'1 Pull',                                                 70,NULL,NULL,NULL,'ksi',NULL,NULL,NULL,NULL,NULL,NULL),
 (44,'60 ksi Min','78 ksi Min','20% Min (2")',NULL,NULL,'1 Pull',                               60,NULL,78,NULL,'ksi',20,'2"',NULL,NULL,NULL,NULL),
 (46,'50-65 ksi','65 - 80 ksi','20% Min (8")*',NULL,NULL,'2 Pulls',                             50,65,65,80,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (47,'50 ksi Min','65 - 80 ksi','20% Min (8") *',NULL,NULL,'2 Pulls',                           50,NULL,65,80,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (49,NULL,NULL,NULL,NULL,NULL,'Hardness - 220 min BHN',                                        NULL,NULL,NULL,NULL,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,'ksi',22,'2"',NULL,NULL,NULL,NULL),
 (51,NULL,NULL,NULL,NULL,NULL,'1 Pull',                                                         NULL,NULL,NULL,NULL,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,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (53,'51 ksi Min','71-91 ksi','22% Min (2")',NULL,NULL,'1 Pull',                                51,NULL,71,91,'ksi',22,'2"',NULL,NULL,NULL,NULL),
 (54,'50 ksi Min','82.5 ksi Min','18% Min (2")',NULL,NULL,'Hardness - 180 min',                 50,NULL,82.5,NULL,'ksi',18,'2"',NULL,NULL,NULL,NULL),
 (55,'235 N/mm2 Min','400 - 520 N/mm2','22% Min (200 mm)',NULL,'27 J Min @ -20 C','2 Pulls + Impacts', 235,NULL,400,520,'N/mm2',22,'200 mm',NULL,NULL,27,NULL),
 (56,'30 ksi Min',NULL,NULL,NULL,NULL,'1 Pull',                                                 30,NULL,NULL,NULL,'ksi',NULL,NULL,NULL,NULL,NULL,NULL),
 (57,'64 ksi Min','78 ksi Min','18% Min (2")',NULL,NULL,'2 Pulls + Hardness 225 Min',           64,NULL,78,NULL,'ksi',18,'2"',NULL,NULL,NULL,NULL),
 (58,'88 ksi Min','125 ksi Min','12% Min (2")','25% Min',NULL,'1 Pull + Bend Test',             88,NULL,125,NULL,'ksi',12,'2"',25,NULL,NULL,NULL),
 (59,'50 Min','63.5-80 Min','18% min (8")',NULL,NULL,NULL,                                      50,NULL,63.5,80,'ksi',18,'8"',NULL,NULL,NULL,NULL),
 (60,'235 N/mm2 Min','400 - 520 N/mm2','22% Min (200 mm)',NULL,'27 J Min @ 0 C','2 Pulls + Impacts', 235,NULL,400,520,'N/mm2',22,'200 mm',NULL,NULL,27,NULL),
 (63,NULL,NULL,NULL,NULL,NULL,'1 Pull (505) + Bend Test',                                       NULL,NULL,NULL,NULL,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,'ksi',13,'8"',NULL,NULL,NULL,NULL),
 (65,'50 ksi Min','65 - 80 ksi','20% Min (8") *',NULL,NULL,'2 Pulls',                           50,NULL,65,80,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (66,'34 ksi Min','50-80 ksi','22% Min (8")',NULL,NULL,'2 Pulls',                               34,NULL,50,80,'ksi',22,'8"',NULL,NULL,NULL,NULL),
 -- (66,'235 N/mm2 Min','400 - 520 N/mm2','22% Min (200 mm)',NULL,NULL,'2 Pulls',               235,NULL,400,520,'N/mm2',22,'200 mm',NULL,NULL,NULL,NULL),  -- SWV 66 IS DUPLICATED IN THE PDF: collides with the unique index. Determine the correct grade.
 (67,'36 ksi Min','58 - 80 ksi','20% Min (8") *',NULL,NULL,'2 Pulls',                           36,NULL,58,80,'ksi',20,'8"',NULL,NULL,NULL,NULL),
 (68,'63 ksi Min','115 ksi Min',NULL,NULL,NULL,'1 Pull',                                        63,NULL,115,NULL,'ksi',NULL,NULL,NULL,NULL,NULL,NULL),
 (70,'70 ksi Min',NULL,NULL,NULL,NULL,'1 Pull',                                                 70,NULL,NULL,NULL,'ksi',NULL,NULL,NULL,NULL,NULL,NULL),
 (72,'24 ksi Min','43 ksi Min','25% Min (2")',NULL,'Only need tests for rail sections','Hardness 90 Min & 1 Pull', 24,NULL,43,NULL,'ksi',25,'2"',NULL,NULL,NULL,NULL),
 (75,'70 ksi Min',NULL,NULL,NULL,NULL,'1 Pull',                                                 70,NULL,NULL,NULL,'ksi',NULL,NULL,NULL,NULL,NULL,NULL),
 (76,NULL,'156 ksi min','8% (2")',NULL,NULL,'Hardness 320 Min & 1 Pull',                        NULL,NULL,156,NULL,'ksi',8,'2"',NULL,NULL,NULL,NULL),
 (85,'235 N/mm2 Min','400 - 520 N/mm2','22% Min (200 mm)',NULL,'27 J Min @ -40 C','2 Pulls + Impacts', 235,NULL,400,520,'N/mm2',22,'200 mm',NULL,NULL,27,NULL),
 (90,NULL,NULL,NULL,NULL,NULL,'Hardness 285 Min',                                              NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (97,'Minimum between 127,6330 PSI - 149,389 PSI',NULL,NULL,NULL,NULL,'1 Pull',                 NULL,NULL,NULL,NULL,'PSI',NULL,NULL,NULL,NULL,NULL,NULL),
 (98,'50 ksi Min','65 - 80 ksi','21% Min (2") *',NULL,NULL,'2 Pull',                            50,NULL,65,80,'ksi',21,'2"',NULL,NULL,NULL,NULL);

/*------------------------------------------------------------------
  2. SWV -> GradeId MAPPING
     MT.catGrades stores the code as 'SWV' + 3-digit zero-padded number:
         1  -> SWV001
         10 -> SWV010
         98 -> SWV098
     NOTE: verify that [Grade] is the actual column name holding the code.
------------------------------------------------------------------*/

-- Ensure grade SWV047 exists before mapping (it was missing from MT.catGrades)
IF NOT EXISTS (SELECT 1 FROM MT.catGrades WHERE LTRIM(RTRIM(GradeName)) = 'SWV047')
    INSERT INTO MT.catGrades (GradeName, PLCName) VALUES ('SWV047', '47');

DECLARE @Map TABLE (Swv INT NOT NULL, GradeId INT NOT NULL);

INSERT INTO @Map (Swv, GradeId)
SELECT s.Swv, g.GradeId
FROM   (SELECT DISTINCT Swv FROM @Src1) s
JOIN   MT.catGrades g
       ON  LTRIM(RTRIM(g.[GradeName])) = 'SWV' + RIGHT('000' + CAST(s.Swv AS VARCHAR(3)), 3);

-- Report (but do NOT abort) any SWV from the PDF with no matching grade.
-- These rows are simply skipped below, since the load joins on @Map.
IF EXISTS (SELECT 1 FROM @Src1 s WHERE NOT EXISTS (SELECT 1 FROM @Map m WHERE m.Swv = s.Swv))
BEGIN
    SELECT 'SWV skipped - no GradeId in MT.catGrades' AS Issue,
           s.Swv,
           'SWV' + RIGHT('000' + CAST(s.Swv AS VARCHAR(3)), 3) AS ExpectedCode
    FROM   @Src1 s
    WHERE  NOT EXISTS (SELECT 1 FROM @Map m WHERE m.Swv = s.Swv);

    PRINT 'WARNING: Some SWV values from the PDF do not map to MT.catGrades and were skipped (see result set above).';
END;

-- Abort if a SWV code matched more than one grade
IF EXISTS (SELECT 1 FROM @Map GROUP BY Swv HAVING COUNT(*) > 1)
    THROW 50003, 'A SWV code matched multiple rows in MT.catGrades. Review the catalog.', 1;

/*------------------------------------------------------------------
  3. Remove all existing rows and create a single new version
------------------------------------------------------------------*/
DECLARE @VersionId INT;

-- Remove every existing version (grade rows first for the FK) so only the new one remains
DELETE FROM LAB.TensileRequirementsByGrade;
DELETE FROM LAB.TensileRequirementVersions;

INSERT INTO LAB.TensileRequirementVersions (Rev, EffectiveDate, Comments, IsActive, CreatedBy)
VALUES (@Rev, @EffectiveDate, @Comments, 1, 'devops');

SET @VersionId = SCOPE_IDENTITY();

/*------------------------------------------------------------------
  4. Insert the grade rows for the new version
------------------------------------------------------------------*/
INSERT INTO LAB.TensileRequirementsByGrade
    (TensileRequirementVersionId, GradeId, [Yield], Tensile, Elongation, PercentRA, Impact, TestsPerHeat,
     YieldMin, YieldMax, TensileMin, TensileMax, Unit,
     ElongationPercentMin, ElongationLength, PercentRAMin, PercentRAMax, ImpactMin, ImpactMax, IsActive)
SELECT
    @VersionId, m.GradeId, s.[Yield], s.Tensile, s.Elongation, s.PercentRA, s.Impact, s.TestsPerHeat,
    s.YieldMin, s.YieldMax, s.TensileMin, s.TensileMax, s.Unit,
    s.ElongationPercentMin, s.ElongationLength, s.PercentRAMin, s.PercentRAMax, s.ImpactMin, s.ImpactMax, 1
FROM   @Src1 s
JOIN   @Map m ON m.Swv = s.Swv;

COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

------------------

/*==================================================================
  LAB.TensileRequirements - Create Rev 32 with Rev 31 as reference
==================================================================*/
SET NOCOUNT ON;

DECLARE @NewRev           INT          = 32;
DECLARE @NewEffectiveDate DATETIME     = '2026-07-16';
DECLARE @NewComments      VARCHAR(MAX) =
    'Rev 32 - Table 15.';
DECLARE @SourceRev        INT          = 31;

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @SourceVersionId INT =
    (SELECT TOP (1) TensileRequirementVersionId
       FROM LAB.TensileRequirementVersions
      WHERE Rev = @SourceRev
      ORDER BY TensileRequirementVersionId DESC);

IF @SourceVersionId IS NULL
    THROW 50010, 'Rev 31 was not found in LAB.TensileRequirementVersions. Nothing to copy.', 1;

/*------------------------------------------------------------------
  2. Remove any REV 32 existing row.
------------------------------------------------------------------*/
DELETE r
FROM   LAB.TensileRequirementsByGrade r
JOIN   LAB.TensileRequirementVersions v
       ON v.TensileRequirementVersionId = r.TensileRequirementVersionId
WHERE  v.Rev = @NewRev;

DELETE FROM LAB.TensileRequirementVersions WHERE Rev = @NewRev;

/*------------------------------------------------------------------
  3. Deactivate all the existing versions and create Rev 32
------------------------------------------------------------------*/
UPDATE LAB.TensileRequirementVersions SET IsActive = 0;

INSERT INTO LAB.TensileRequirementVersions (Rev, EffectiveDate, Comments, IsActive, CreatedBy)
VALUES (@NewRev, @NewEffectiveDate, @NewComments, 1, 'devops');

DECLARE @NewVersionId INT = SCOPE_IDENTITY();

/*------------------------------------------------------------------
  4. Copy all grade rows from Rev 31 to Rev 32
------------------------------------------------------------------*/
INSERT INTO LAB.TensileRequirementsByGrade
    (TensileRequirementVersionId, GradeId, [Yield], Tensile, Elongation, PercentRA, Impact, TestsPerHeat,
     YieldMin, YieldMax, TensileMin, TensileMax, Unit,
     ElongationPercentMin, ElongationLength, PercentRAMin, PercentRAMax, ImpactMin, ImpactMax, IsActive)
SELECT
    @NewVersionId, GradeId, [Yield], Tensile, Elongation, PercentRA, Impact, TestsPerHeat,
    YieldMin, YieldMax, TensileMin, TensileMax, Unit,
    ElongationPercentMin, ElongationLength, PercentRAMin, PercentRAMax, ImpactMin, ImpactMax, 1
FROM   LAB.TensileRequirementsByGrade
WHERE  TensileRequirementVersionId = @SourceVersionId;

/*------------------------------------------------------------------
  5a. Grade SWV066 -> variante N/mm2
------------------------------------------------------------------*/
UPDATE g
SET [Yield]              = '235 N/mm2 Min',
    Tensile              = '400 - 520 N/mm2',
    Elongation           = '22% Min (200 mm)',
    YieldMin             = 235,
    YieldMax             = NULL,
    TensileMin           = 400,
    TensileMax           = 520,
    Unit                 = 'N/mm2',
    ElongationPercentMin = 22,
    ElongationLength     = '200 mm'
FROM   LAB.TensileRequirementsByGrade g
JOIN   MT.catGrades c ON c.GradeId = g.GradeId
WHERE  g.TensileRequirementVersionId = @NewVersionId
  AND  LTRIM(RTRIM(c.GradeName)) = 'SWV066';

/*------------------------------------------------------------------
  5b. Grade SWV097 -> Tensile in PSI unit (127633 - 149389).
------------------------------------------------------------------*/
UPDATE g
SET [Yield]    = NULL,
    Tensile    = 'Minimum between 127,633 PSI - 149,389 PSI',
    TensileMin = 127633,
    TensileMax = 149389,
    Unit       = 'PSI'
FROM   LAB.TensileRequirementsByGrade g
JOIN   MT.catGrades c ON c.GradeId = g.GradeId
WHERE  g.TensileRequirementVersionId = @NewVersionId
  AND  LTRIM(RTRIM(c.GradeName)) = 'SWV097';

/*------------------------------------------------------------------
  6. Verificacion
------------------------------------------------------------------*/
DECLARE @NewRowCount INT =
    (SELECT COUNT(*) FROM LAB.TensileRequirementsByGrade
      WHERE TensileRequirementVersionId = @NewVersionId);

PRINT 'Rev 32 created as VersionId ' + CAST(@NewVersionId AS VARCHAR(10))
    + ' with ' + CAST(@NewRowCount AS VARCHAR(10))
    + ' grade rows (copy of Rev ' + CAST(@SourceRev AS VARCHAR(10)) + ').';

COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;


------------------

/*
    Seed LAB.TestsBySection (Table 16 Rev 45) as a brand-new version.

    - One source row per Section # that has content in a test column.
    - Chemistry column is ignored.
    - Pull Test mapping:  2" Pull -> Pull2,  8" Pull -> Pull8,  0.505" -> Pull505
      (2" Pull(Grind Flat) -> Pull2, 0.505" Pull -> Pull505, 8" Pull - 1" Width -> Pull8)
    - Impact (Charpy / Charpy - Lloyd's Spec.) -> Impact
    - Bend Test -> Bend,  Hardness -> Hardness
    - Notes column -> Comments (only the Notes text; pull qualifiers like
      1" Width / Grind Flat are not carried over).

    Matching: MT.Sections.SectionName = the Section # verbatim (active sections only).
    Removes all existing versions/rows and creates a single new version (effective
    2026-05-28), mirroring the tensile block.
    Section 3133 appears twice in the source; the aggregation below collapses it.
*/

SET NOCOUNT ON;
DECLARE @TbsRev           INT          = 44;
DECLARE @TbsEffectiveDate DATETIME     = '2025-08-04';
DECLARE @TbsComments      VARCHAR(MAX) = 'Rev 44 - Table 16 Tests by Section. Loaded from controlled PDF.';

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @src TABLE (
    SectionName VARCHAR(50)  NOT NULL,
    GradeName   VARCHAR(50)  NULL,  -- NULL = applies to all grades; otherwise the MT.catGrades code (e.g. SWV072)
    Pull2       BIT          NOT NULL,
    Pull8       BIT          NOT NULL,
    Pull505     BIT          NOT NULL,
    Impact      BIT          NOT NULL,
    Bend        BIT          NOT NULL,
    Hardness    BIT          NOT NULL,
    Comments    VARCHAR(MAX) NULL
);

--            SectionName      P2 P8 P505 Imp Bnd Hrd  Comments
INSERT INTO @src (SectionName, Pull2, Pull8, Pull505, Impact, Bend, Hardness, Comments) VALUES
 ('1000',        1, 0, 0, 0, 0, 0, NULL),
 ('1001',        1, 0, 0, 0, 0, 0, NULL),
 ('1002',        1, 0, 0, 0, 0, 0, NULL),
 ('1003',        1, 0, 0, 0, 0, 0, NULL),
 ('1004',        1, 0, 0, 0, 0, 0, NULL),
 ('1005',        1, 0, 0, 0, 0, 0, NULL),
 ('1006',        1, 0, 0, 0, 0, 0, NULL),
 ('1009',        1, 0, 0, 0, 0, 0, NULL),
 ('1010',        1, 0, 0, 0, 0, 0, NULL),
 ('1011',        1, 0, 0, 0, 0, 0, NULL),
 ('1012',        1, 0, 0, 0, 0, 0, NULL),
 ('1013',        0, 0, 1, 0, 0, 0, NULL),
 ('1208',        0, 0, 0, 1, 0, 0, NULL),
 ('1408',        0, 0, 0, 1, 0, 0, NULL),
 ('1409',        0, 0, 0, 1, 0, 0, NULL),
 ('1410',        0, 0, 0, 1, 0, 0, NULL),
 ('1608',        0, 0, 0, 1, 0, 0, NULL),
 ('1609',        0, 0, 0, 1, 0, 0, NULL),
 ('1611',        0, 0, 0, 1, 0, 0, NULL),
 ('1808',        0, 0, 0, 1, 0, 0, NULL),
 ('1809',        0, 0, 0, 1, 0, 0, NULL),
 ('1810',        0, 0, 0, 1, 0, 0, NULL),
 ('1811',        0, 0, 0, 1, 0, 0, NULL),
 ('2008',        0, 0, 0, 1, 0, 0, NULL),
 ('2009',        0, 0, 0, 1, 0, 0, NULL),
 ('2010',        0, 0, 0, 1, 0, 0, NULL),
 ('2011',        0, 0, 0, 1, 0, 0, NULL),
 ('2012',        0, 0, 0, 1, 0, 0, NULL),
 ('2133',        0, 1, 0, 0, 0, 0, NULL),
 ('2209',        0, 0, 0, 1, 0, 0, NULL),
 ('2210',        0, 0, 0, 1, 0, 0, NULL),
 ('2211',        0, 0, 0, 1, 0, 0, NULL),
 ('2212',        0, 0, 0, 1, 0, 0, NULL),
 ('2409',        0, 0, 0, 1, 0, 0, NULL),
 ('2410',        0, 0, 0, 1, 0, 0, NULL),
 ('2411',        0, 0, 0, 1, 0, 0, NULL),
 ('2412',        0, 0, 0, 1, 0, 0, NULL),
 ('2494',        0, 1, 0, 0, 0, 0, NULL),
 ('2497',        1, 0, 0, 0, 0, 1, NULL),
 ('2527',        0, 1, 0, 0, 0, 0, NULL),
 ('2528',        0, 1, 0, 0, 0, 0, NULL),
 ('2536',        0, 0, 1, 0, 0, 0, NULL),
 ('2538',        0, 1, 0, 0, 0, 0, NULL),
 ('2541',        1, 0, 0, 0, 0, 0, NULL),
 ('2543',        0, 1, 0, 0, 0, 0, NULL),
 ('2548',        0, 1, 0, 0, 0, 0, NULL),
 ('2559',        0, 1, 0, 0, 0, 0, NULL),
 ('2569',        0, 1, 0, 0, 0, 0, NULL),
 ('2581',        0, 0, 1, 0, 0, 0, NULL),
 ('2585',        0, 0, 1, 0, 0, 0, NULL),
 ('2589',        0, 1, 0, 0, 0, 0, NULL),
 ('2636',        1, 0, 0, 0, 0, 0, NULL),
 ('2637',        0, 1, 0, 0, 0, 0, NULL),
 ('2644',        0, 0, 1, 0, 0, 0, NULL),
 ('2654',        0, 1, 0, 0, 0, 0, NULL),
 ('2658',        0, 1, 0, 0, 0, 0, NULL),
 ('2663',        0, 1, 0, 0, 0, 0, NULL),
 ('2673',        0, 1, 0, 0, 0, 0, NULL),
 ('2674',        0, 0, 1, 0, 0, 0, NULL),
 ('2675',        1, 0, 0, 1, 0, 0, NULL),
 ('2678',        0, 0, 0, 0, 0, 1, NULL),
 ('2680',        0, 1, 0, 0, 0, 0, NULL),
 ('2693',        0, 1, 0, 0, 0, 0, NULL),
 ('2695',        0, 1, 0, 0, 0, 0, NULL),
 ('2716',        0, 1, 0, 0, 0, 0, NULL),
 ('2718',        0, 1, 0, 0, 0, 0, NULL),
 ('2720',        0, 1, 0, 0, 0, 0, NULL),
 ('2722',        0, 1, 0, 0, 0, 0, NULL),
 ('2725',        0, 1, 0, 0, 0, 0, NULL),
 ('2734',        0, 1, 0, 0, 0, 0, NULL),
 ('2742',        0, 0, 1, 0, 0, 0, NULL),
 ('2743',        0, 0, 1, 0, 0, 0, NULL),
 ('2744',        0, 1, 0, 0, 0, 0, NULL),
 ('2745',        0, 1, 0, 0, 0, 0, NULL),
 ('2746',        0, 0, 1, 0, 0, 0, NULL),
 ('2749',        0, 1, 0, 0, 0, 0, NULL),
 ('2751',        0, 0, 1, 0, 0, 0, NULL),
 ('2754',        0, 1, 0, 0, 0, 0, NULL),
 ('2758',        0, 0, 1, 0, 0, 0, NULL),
 ('2759',        0, 1, 0, 0, 0, 0, NULL),
 ('2760',        0, 1, 0, 0, 0, 0, NULL),
 ('2761',        0, 0, 1, 0, 0, 0, NULL),
 ('2762',        0, 0, 1, 0, 0, 0, NULL),
 ('2766',        0, 0, 1, 0, 0, 0, NULL),
 ('2776',        0, 1, 0, 0, 0, 0, NULL),
 ('2784',        0, 1, 0, 0, 0, 0, NULL),
 ('2789',        0, 0, 1, 0, 0, 0, NULL),
 ('2802',        0, 0, 1, 0, 0, 0, NULL),
 ('2806',        0, 1, 0, 0, 0, 0, NULL),
 ('2807',        0, 1, 0, 0, 0, 0, NULL),
 ('2811',        0, 0, 1, 0, 1, 0, NULL),
 ('2812',        0, 0, 1, 0, 1, 0, NULL),
 ('2814',        0, 0, 1, 0, 1, 0, NULL),
 ('2816',        0, 0, 1, 0, 1, 0, NULL),
 ('2817',        0, 0, 1, 0, 1, 0, NULL),
 ('2818',        0, 0, 1, 0, 1, 0, NULL),
 ('2821',        0, 0, 1, 0, 1, 0, NULL),
 ('2822',        0, 0, 1, 0, 1, 0, NULL),
 ('2823',        0, 0, 1, 0, 1, 0, NULL),
 ('2824',        0, 0, 1, 0, 1, 0, NULL),
 ('2825',        0, 1, 0, 0, 0, 0, NULL),
 ('2831',        0, 0, 1, 0, 0, 0, NULL),
 ('2832',        0, 0, 1, 0, 0, 0, NULL),
 ('2833',        0, 0, 1, 0, 0, 0, NULL),
 ('2834',        0, 0, 1, 0, 0, 0, NULL),
 ('2836',        0, 1, 0, 0, 0, 0, NULL),
 ('2855',        0, 0, 1, 0, 0, 0, NULL),
 ('2859',        0, 1, 0, 0, 0, 0, NULL),
 ('2863',        0, 1, 0, 0, 0, 0, NULL),
 ('2864',        1, 0, 0, 0, 0, 0, NULL),
 ('2865',        1, 0, 0, 0, 0, 0, NULL),
 ('2866',        1, 0, 0, 0, 0, 0, NULL),
 ('2867',        1, 0, 0, 0, 0, 0, NULL),
 ('2868',        1, 0, 0, 0, 0, 0, NULL),
 ('2869',        0, 0, 1, 0, 1, 0, NULL),
 ('2870',        0, 0, 1, 0, 1, 0, NULL),
 ('2873',        0, 1, 0, 0, 0, 0, NULL),
 ('2874',        0, 1, 0, 0, 0, 0, NULL),
 ('2876',        0, 1, 0, 0, 0, 0, NULL),
 ('2877',        0, 1, 0, 0, 0, 0, NULL),
 ('2881',        1, 0, 0, 0, 0, 0, NULL),
 ('2882',        1, 0, 0, 0, 0, 0, NULL),
 ('2886',        1, 0, 0, 0, 0, 0, NULL),
 ('2888',        0, 1, 0, 0, 0, 0, NULL),
 ('2890',        0, 0, 1, 0, 0, 0, NULL),
 ('2891',        0, 0, 1, 0, 0, 0, NULL),
 ('2892',        0, 0, 1, 0, 0, 0, NULL),
 ('2893',        1, 0, 0, 0, 0, 0, NULL),
 ('2894',        1, 0, 0, 0, 0, 0, NULL),
 ('2895',        1, 0, 0, 0, 0, 0, NULL),
 ('2896',        1, 0, 0, 0, 0, 0, NULL),
 ('2899',        0, 0, 1, 0, 0, 0, NULL),
 ('2900',        0, 0, 1, 0, 0, 0, NULL),
 ('2902',        0, 1, 0, 0, 0, 0, NULL),
 ('2911',        0, 0, 1, 0, 1, 0, NULL),
 ('2912',        0, 0, 1, 0, 1, 0, NULL),
 ('2913',        1, 0, 0, 0, 0, 0, NULL),
 ('2914',        1, 0, 0, 0, 0, 0, NULL),
 ('2915',        0, 0, 1, 0, 0, 0, NULL),
 ('2916',        0, 0, 1, 0, 0, 0, NULL),
 ('2917',        0, 0, 1, 0, 0, 0, NULL),
 ('2918',        0, 0, 1, 0, 0, 0, NULL),
 ('2919',        0, 1, 0, 0, 0, 0, NULL),
 ('2920',        0, 1, 0, 0, 0, 0, NULL),
 ('2921',        0, 1, 0, 0, 0, 0, NULL),
 ('2922',        0, 1, 0, 0, 0, 0, NULL),
 ('2923',        0, 1, 0, 0, 0, 0, NULL),
 ('2925',        0, 1, 0, 0, 0, 0, NULL),
 ('2926',        0, 1, 0, 0, 0, 0, NULL),
 ('2927',        1, 0, 0, 0, 0, 0, NULL),
 ('2928',        1, 0, 0, 0, 0, 0, NULL),
 ('2929',        0, 1, 0, 0, 0, 0, NULL),
 ('2930',        0, 1, 0, 0, 0, 0, NULL),
 ('2931',        0, 1, 0, 0, 0, 0, NULL),
 ('2932',        0, 1, 0, 0, 0, 0, NULL),
 ('2933',        0, 1, 0, 0, 0, 0, NULL),
 ('2934',        0, 1, 0, 0, 0, 0, NULL),
 ('2935',        0, 1, 0, 0, 0, 0, NULL),
 ('2936',        0, 1, 0, 0, 0, 0, NULL),
 ('2937',        1, 0, 0, 0, 0, 0, NULL),
 ('2938',        0, 1, 0, 0, 0, 0, NULL),
 ('2939',        0, 1, 0, 0, 0, 0, NULL),
 ('2940',        1, 0, 0, 0, 0, 0, NULL),
 ('2941',        1, 0, 0, 0, 0, 0, NULL),
 ('2942',        1, 0, 0, 0, 0, 0, NULL),
 ('2943',        1, 0, 0, 0, 0, 0, NULL),
 ('2944',        1, 0, 0, 0, 0, 0, NULL),
 ('2945',        1, 0, 0, 0, 0, 0, NULL),
 ('2946',        0, 1, 0, 0, 0, 0, NULL),
 ('2947',        0, 1, 0, 0, 0, 0, NULL),
 ('2949',        0, 1, 0, 0, 0, 0, NULL),
 ('2950',        0, 1, 0, 0, 0, 0, NULL),
 ('2951',        0, 1, 0, 0, 0, 0, NULL),
 ('2952',        1, 0, 0, 0, 0, 0, NULL),
 ('2953',        0, 1, 0, 0, 0, 0, NULL),
 ('2954',        0, 1, 0, 0, 0, 0, NULL),
 ('2955',        0, 1, 0, 0, 0, 0, NULL),
 ('2956',        0, 1, 0, 0, 0, 0, NULL),
 ('2957',        0, 1, 0, 0, 0, 0, NULL),
 ('2958',        0, 1, 0, 0, 0, 0, NULL),
 ('2959',        0, 1, 0, 0, 0, 0, NULL),
 ('2960',        0, 1, 0, 0, 0, 0, NULL),
 ('2961',        0, 1, 0, 0, 0, 0, NULL),
 ('2962',        0, 1, 0, 0, 0, 0, NULL),
 ('2963',        1, 0, 0, 0, 0, 0, NULL),
 ('2964',        1, 0, 0, 0, 0, 0, NULL),
 ('2965',        1, 0, 0, 0, 0, 0, NULL),
 ('2966',        0, 1, 0, 0, 0, 0, NULL),
 ('2967',        0, 1, 0, 0, 0, 0, NULL),
 ('2968',        0, 1, 0, 0, 0, 0, NULL),
 ('2969',        0, 1, 0, 0, 0, 0, NULL),
 ('2970',        0, 1, 0, 0, 0, 0, NULL),
 ('2971',        0, 1, 0, 0, 0, 0, NULL),
 ('2972',        1, 0, 0, 0, 0, 0, NULL),
 ('2974',        0, 1, 0, 0, 0, 0, NULL),
 ('2977',        0, 1, 0, 0, 0, 0, NULL),
 ('2978',        0, 1, 0, 0, 0, 0, NULL),
 ('2979',        0, 1, 0, 0, 0, 0, NULL),
 ('2982',        0, 1, 0, 0, 0, 0, NULL),
 ('2983',        0, 1, 0, 0, 0, 0, NULL),
 ('2985',        0, 1, 0, 0, 0, 0, NULL),
 ('2990',        0, 1, 0, 0, 0, 0, NULL),
 ('2991',        0, 1, 0, 0, 0, 0, NULL),
 ('2992',        0, 1, 0, 0, 0, 0, NULL),
 ('2993',        0, 1, 0, 0, 0, 0, NULL),
 ('2994',        0, 1, 0, 0, 0, 0, NULL),
 ('2995',        0, 1, 0, 0, 0, 0, NULL),
 ('3005',        1, 0, 0, 0, 0, 0, NULL),
 ('3006',        0, 1, 0, 0, 0, 0, NULL),
 ('3007',        0, 1, 0, 0, 0, 0, NULL),
 ('3008',        0, 0, 1, 0, 0, 0, NULL),
 ('3009',        0, 0, 1, 0, 0, 0, NULL),
 ('3010',        0, 0, 1, 0, 0, 0, NULL),
 ('3011',        0, 0, 1, 0, 0, 0, NULL),
 ('3012',        0, 0, 1, 0, 0, 0, NULL),
 ('3013',        0, 1, 0, 0, 0, 0, NULL),
 ('3014',        0, 1, 0, 0, 0, 0, NULL),
 ('3015',        0, 0, 1, 0, 0, 0, NULL),
 ('3016',        0, 0, 1, 0, 0, 0, NULL),
 ('3017',        0, 0, 1, 0, 0, 0, NULL),
 ('3018',        0, 0, 1, 0, 0, 0, NULL),
 ('3019',        0, 1, 0, 0, 0, 0, NULL),
 ('3021',        0, 1, 0, 0, 0, 0, NULL),
 ('3022',        1, 0, 0, 0, 0, 0, NULL),
 ('3023',        0, 0, 1, 0, 0, 0, NULL),
 ('3029',        0, 0, 1, 0, 0, 0, NULL),
 ('3032',        0, 1, 0, 0, 0, 0, NULL),
 ('3033',        0, 1, 0, 0, 0, 0, NULL),
 ('3036',        0, 1, 0, 0, 0, 0, NULL),
 ('3037',        0, 1, 0, 0, 0, 0, NULL),
 ('3038',        0, 1, 0, 0, 0, 0, NULL),
 ('3042',        0, 0, 1, 0, 1, 0, NULL),
 ('3043',        0, 0, 1, 0, 1, 0, NULL),
 ('3044',        1, 0, 0, 0, 0, 0, NULL),
 ('3045',        1, 0, 0, 0, 0, 0, NULL),
 ('3056',        0, 1, 0, 0, 0, 0, NULL),
 ('3057',        1, 0, 0, 0, 0, 0, NULL),
 ('3059',        0, 0, 1, 0, 0, 0, NULL),
 ('3060',        1, 0, 0, 0, 0, 0, NULL),
 ('3061',        1, 0, 0, 0, 0, 0, NULL),
 ('3064',        0, 0, 1, 0, 0, 0, NULL),
 ('3068',        1, 0, 0, 0, 0, 0, NULL),
 ('3071',        0, 1, 0, 0, 0, 0, NULL),
 ('3072',        0, 1, 0, 0, 0, 0, NULL),
 ('3074',        0, 1, 0, 0, 0, 0, NULL),
 ('3075',        0, 1, 0, 0, 0, 0, NULL),
 ('3077',        0, 0, 1, 0, 1, 0, NULL),
 ('3079',        1, 0, 0, 0, 0, 0, NULL),
 ('3080',        1, 0, 0, 0, 0, 0, NULL),
 ('3081',        1, 0, 0, 0, 0, 0, NULL),
 ('3082',        1, 0, 0, 0, 0, 0, NULL),
 ('3083',        1, 0, 0, 0, 0, 0, NULL),
 ('3084',        1, 0, 0, 0, 0, 0, NULL),
 ('3085',        1, 0, 0, 0, 0, 0, NULL),
 ('3086',        0, 1, 0, 0, 0, 0, NULL),
 ('3087',        0, 1, 0, 0, 0, 0, NULL),
 ('3088',        0, 1, 0, 0, 0, 0, NULL),
 ('3089',        0, 1, 0, 0, 0, 0, NULL),
 ('3090',        0, 1, 0, 0, 0, 0, NULL),
 ('3091',        0, 1, 0, 0, 0, 0, NULL),
 ('3092',        0, 1, 0, 0, 0, 0, NULL),
 ('3093',        0, 1, 0, 0, 0, 0, NULL),
 ('3094',        0, 1, 0, 0, 0, 0, NULL),
 ('3095',        0, 0, 1, 0, 0, 0, NULL),
 ('3096',        0, 0, 1, 0, 0, 0, NULL),
 ('3098',        0, 0, 1, 0, 0, 0, NULL),
 ('3100',        0, 1, 0, 0, 0, 0, NULL),
 ('3101',        0, 1, 0, 0, 0, 0, NULL),
 ('3102',        0, 1, 0, 0, 0, 0, NULL),
 ('3103',        0, 1, 0, 0, 0, 0, NULL),
 ('3104',        0, 0, 1, 0, 1, 0, NULL),
 ('3105',        0, 0, 1, 0, 1, 0, NULL),
 ('3106',        0, 0, 1, 0, 1, 0, NULL),
 ('3107',        0, 0, 1, 0, 1, 0, NULL),
 ('3108',        0, 0, 1, 0, 1, 0, NULL),
 ('3109',        0, 0, 1, 0, 1, 0, NULL),
 ('3110',        1, 0, 0, 0, 0, 0, NULL),
 ('3111',        1, 0, 0, 0, 0, 0, NULL),
 ('3112',        1, 0, 0, 0, 0, 0, NULL),
 ('3113',        0, 0, 1, 0, 1, 0, NULL),
 ('3114',        0, 0, 1, 0, 1, 0, NULL),
 ('3115',        1, 0, 0, 0, 0, 0, NULL),
 ('3116',        1, 0, 0, 0, 0, 0, NULL),
 ('3117',        0, 0, 1, 0, 0, 0, NULL),
 ('3118',        0, 1, 0, 0, 0, 0, NULL),
 ('3119',        0, 1, 0, 0, 0, 0, NULL),
 ('3120',        1, 0, 0, 0, 0, 0, NULL),
 ('3121',        0, 0, 1, 0, 0, 0, NULL),
 ('3122',        1, 0, 0, 0, 0, 0, NULL),
 ('3123',        0, 1, 0, 0, 0, 0, NULL),
 ('3124',        0, 0, 1, 0, 0, 0, NULL),
 ('3125',        0, 1, 0, 0, 0, 0, NULL),
 ('3126',        0, 0, 1, 0, 1, 0, NULL),
 ('3127',        0, 0, 1, 0, 1, 0, NULL),
 ('3128',        1, 0, 0, 1, 1, 0, NULL),
 ('3130',        0, 1, 0, 0, 0, 0, NULL),
 ('3133',        0, 0, 1, 0, 1, 0, 'Decarb'),
 ('8025',        1, 0, 0, 0, 0, 0, NULL),
 ('8026',        1, 0, 0, 0, 0, 0, NULL),
 ('8027',        1, 0, 0, 0, 0, 0, NULL),
 ('8028',        1, 0, 0, 0, 0, 0, NULL),
 ('8029',        1, 0, 0, 0, 0, 0, NULL),
 ('8030',        1, 0, 0, 0, 0, 0, NULL),
 ('8087',        1, 0, 0, 0, 0, 0, NULL),
 ('8100',        1, 0, 0, 0, 0, 0, NULL),
 ('8500',        0, 0, 0, 0, 0, 1, NULL),
 ('8500',        0, 0, 1, 0, 0, 1, 'Grade 72 Only'),
 ('8680',        0, 1, 0, 0, 0, 0, NULL),
 ('8695',        0, 1, 0, 0, 0, 0, NULL),
 ('4400',        1, 0, 0, 0, 0, 0, NULL);

-- Grade-specific overrides: identify the 8500 Grade 72 row (the pull row; the
-- all-grades 8500 row is Hardness-only) and tag it with the SWV072 grade code.
UPDATE @src SET GradeName = 'SWV072' WHERE SectionName = '8500' AND Pull505 = 1;

------------------------------------------------------------
-- Remove all existing rows and create a single new version
------------------------------------------------------------
DECLARE @TbsVersionId INT;

-- Remove every existing version (section rows first for the FK) so only the new one remains
DELETE FROM LAB.TestsBySection;
DELETE FROM LAB.TestsBySectionVersions;

INSERT INTO LAB.TestsBySectionVersions (Rev, EffectiveDate, Comments, IsActive, CreatedBy)
VALUES (@TbsRev, @TbsEffectiveDate, @TbsComments, 1, 'devops');

SET @TbsVersionId = SCOPE_IDENTITY();

------------------------------------------------------------
-- Insert the section rows for the new version
------------------------------------------------------------
INSERT INTO LAB.TestsBySection (TestsBySectionVersionId, SectionId, GradeId, Pull2, Pull8, Pull505, Impact, Bend, Hardness, Comments)
SELECT
    @TbsVersionId,
    s.SectionId,
    g.GradeId,  -- NULL for all-grades rows
    MAX(CAST(x.Pull2 AS INT)), MAX(CAST(x.Pull8 AS INT)), MAX(CAST(x.Pull505 AS INT)),
    MAX(CAST(x.Impact AS INT)), MAX(CAST(x.Bend AS INT)), MAX(CAST(x.Hardness AS INT)),
    MAX(x.Comments)
FROM @src x
INNER JOIN MT.Sections s ON s.SectionName = x.SectionName AND s.IsActive = 1
LEFT JOIN MT.catGrades g ON g.GradeName = x.GradeName
GROUP BY s.SectionId, g.GradeId;

PRINT 'Inserted ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' TestsBySection rows into VersionId '
    + CAST(@TbsVersionId AS VARCHAR(10)) + ', EffectiveDate = ' + CONVERT(VARCHAR(23), @TbsEffectiveDate, 121);

------------------------------------------------------------
-- Diagnostic: source sections that had no matching active MT.Sections row
------------------------------------------------------------
SELECT x.SectionName AS UnmatchedSectionName
FROM @src x
WHERE NOT EXISTS (
    SELECT 1 FROM MT.Sections s WHERE s.SectionName = x.SectionName AND s.IsActive = 1
)
ORDER BY x.SectionName;

COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[UpsertTestsBySection]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[DeleteTestsBySection]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[GetTestsBySection]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[GetTestsBySectionVersions]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[CreateTestsBySectionVersion]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[PublishTestsBySectionVersion]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[LAB].[DeleteTestsBySectionVersion]', 'admin Profile', 'devops'
exec SEC.UpsertProcedureAndSetToProfile '[MT].[GetAllSections]', 'admin Profile', 'devops'


DECLARE @procName varchar(max) = 'PostDeployment'

EXEC [SEC].[UpsertProcedureAndSetToProfile]
    '[DEL].[GetProductionEventsBySection]',
    'admin Profile'

GO

UPDATE DEL.Delays
SET JustifiedByUser = 'auto'
WHERE JustifiedByUser IN ('M1.TrackDelaysJob', 'M2.TrackDelaysJob')

GO
