SET QUOTED_IDENTIFIER ON

GO

UPDATE M2.TensileRequirementsByGrade 
SET 
    Unit = CASE 
        WHEN Yield LIKE '%N/mm²%' OR Tensile LIKE '%N/mm²%' THEN 'N/mm2'
        WHEN Yield LIKE '%ksi%' OR Tensile LIKE '%ksi%' THEN 'ksi'
    END,
    
    YieldMin = CASE 
        WHEN Yield IS NULL OR Yield = '[NULL]' THEN NULL
        ELSE CAST(LEFT(Yield, PATINDEX('%[^0-9.]%', Yield + ' ') - 1) AS DECIMAL(10,2))
    END,
    
    YieldMax = CASE 
        WHEN Yield LIKE '%[0-9]-[0-9]%' OR Yield LIKE '%[0-9] - [0-9]%'
        THEN CAST(LTRIM(RTRIM(SUBSTRING(
            REPLACE(Yield, ' ', ''),
            CHARINDEX('-', REPLACE(Yield, ' ', '')) + 1,
            PATINDEX('%[^0-9.]%', SUBSTRING(REPLACE(Yield, ' ', ''), CHARINDEX('-', REPLACE(Yield, ' ', '')) + 1, 20) + 'x') - 1
        ))) AS DECIMAL(10,2))
    END,
    
    TensileMin = CASE 
        WHEN Tensile IS NULL OR Tensile = '[NULL]' THEN NULL
        ELSE CAST(LEFT(Tensile, PATINDEX('%[^0-9.]%', Tensile + ' ') - 1) AS DECIMAL(10,2))
    END,
    
    TensileMax = CASE 
        WHEN Tensile LIKE '%[0-9]-[0-9]%' OR Tensile LIKE '%[0-9] - [0-9]%'
        THEN CAST(LTRIM(RTRIM(SUBSTRING(
            REPLACE(Tensile, ' ', ''),
            CHARINDEX('-', REPLACE(Tensile, ' ', '')) + 1,
            PATINDEX('%[^0-9.]%', SUBSTRING(REPLACE(Tensile, ' ', ''), CHARINDEX('-', REPLACE(Tensile, ' ', '')) + 1, 20) + 'x') - 1
        ))) AS DECIMAL(10,2))
    END,
    
    ElongationPercentMin = CASE 
        WHEN Elongation IS NULL OR Elongation = '[NULL]' THEN NULL
        ELSE CAST(LEFT(Elongation, PATINDEX('%[^0-9.]%', Elongation + ' ') - 1) AS DECIMAL(10,2))
    END,
    
    ElongationLength = CASE
        WHEN Elongation LIKE '%(%)%' 
        THEN SUBSTRING(Elongation, CHARINDEX('(', Elongation) + 1, CHARINDEX(')', Elongation) - CHARINDEX('(', Elongation) - 1)
    END,
    
    ImpactMin = CASE 
        WHEN Impact IS NULL OR Impact = '[NULL]' THEN NULL
        ELSE CAST(LEFT(Impact, PATINDEX('%[^0-9.]%', Impact + ' ') - 1) AS DECIMAL(10,2))
    END;
-- ============================================================================
-- FIX SCRIPT: Event 99 Section Interruption Issue
-- ============================================================================
-- Date: 2026-01-27
-- Issue: Section 2725 was interrupted by section 2759, causing Event 99 to
--        have incorrect Start time (moved to after 2759 instead of covering Part 1)
--
-- Current situation:
--   - Event 99 (2725): Start = Jan 23 20:34, End = Jan 26 15:14
--   - Campaigns 1436-1444 (Part 1): Jan 22 22:42 - Jan 23 19:45 → EventId = 99 (WRONG!)
--   - Campaigns 1446-1478 (Part 2): Jan 23 21:05 - Jan 26 15:14 → EventId = 99
--   - Event 99's Start should cover Part 1, not start after Event 126
--
-- Fix:
--   1. Update Event 99 to cover Part 1 only (22:42 - 19:45)
--   2. Create new Event for Part 2 (20:34 - 15:14)
--   3. Link Part 2 campaigns to the new Event
--   4. Leave current 2759 campaigns (1479-1491) alone - sync will handle them
-- ============================================================================

SET NOCOUNT ON;
BEGIN TRANSACTION;

BEGIN TRY
    DECLARE @NewEventId INT;
    DECLARE @SectionId_2725 INT = 10;  -- Section 2725
    DECLARE @User VARCHAR(50) = 'Fix_Event99_Script';

    -- Timestamps for Part 1 (from campaigns 1436-1444)
    -- Start should be contiguous with previous event (Event 100 ends at 22:26:25)
    -- This follows the sync design: TrackedStart = TrackedEnd of previous group
    DECLARE @Part1_Start DATETIME = (SELECT [End] FROM M2.Events WHERE EventId = 100);  -- Contiguous with Event 100
    DECLARE @Part1_End DATETIME = '2026-01-23 19:45:25.310';

    -- Timestamps for Part 2 (from campaigns 1446-1478)
    -- Start should be right after Event 126 ends
    DECLARE @Part2_Start DATETIME = '2026-01-23 20:34:23.010';
    DECLARE @Part2_End DATETIME = '2026-01-26 15:14:16.010';

    PRINT '============================================';
    PRINT 'FIX SCRIPT: Event 99 Section Interruption';
    PRINT '============================================';
    PRINT '';

    -- =========================================================================
    -- STEP 1: Update Event 99 to cover Part 1 only
    -- =========================================================================
    PRINT 'STEP 1: Updating Event 99 to cover Part 1 campaigns (1436-1444)';
    PRINT '  Old: Start = 2026-01-23 20:34:23, End = 2026-01-26 15:14:16';
    PRINT '  New: Start = 2026-01-22 22:42:37, End = 2026-01-23 19:45:25';

    UPDATE M2.Events
    SET
        [Start] = @Part1_Start,
        [End] = @Part1_End,
        UpdatedAt = GETUTCDATE(),
        UpdatedBy = @User
    WHERE EventId = 99;

    UPDATE M2.DraftEvents
    SET
        [Start] = @Part1_Start,
        [End] = @Part1_End,
        UpdatedAt = GETUTCDATE(),
        UpdatedBy = @User
    WHERE EventId = 99;

    -- Update FirstBilletTimestamp in ProductionEvents
    UPDATE M2.ProductionEvents
    SET FirstBilletTimestamp = @Part1_Start
    WHERE EventId = 99;

    UPDATE M2.DraftProductionEvents
    SET FirstBilletTimestamp = @Part1_Start
    WHERE EventId = 99;

    PRINT '  Event 99 updated successfully.';
    PRINT '';

    -- =========================================================================
    -- STEP 2: Create new Event for Part 2
    -- =========================================================================
    PRINT 'STEP 2: Creating new Event for Part 2 campaigns (1446-1478)';
    PRINT '  Start = 2026-01-23 20:34:23, End = 2026-01-26 15:14:16';

    -- Insert into DraftEvents first (has IDENTITY)
    INSERT INTO M2.DraftEvents (
        [Start], [End], EventTypeCode, Comments,
        CreatedAt, CreatedBy, UpdatedAt, UpdatedBy, IsActive
    )
    VALUES (
        @Part2_Start, @Part2_End, 'P',
        'Created by fix script - Part 2 of section 2725 after 2759 interruption',
        GETUTCDATE(), @User, GETUTCDATE(), @User, 1
    );

    SET @NewEventId = SCOPE_IDENTITY();
    PRINT '  New EventId: ' + CAST(@NewEventId AS VARCHAR);

    -- Insert into Events with that EventId (Events table has no IDENTITY, uses EventId from DraftEvents)
    INSERT INTO M2.Events (
        EventId, [Start], [End], EventTypeCode, Comments,
        CreatedAt, CreatedBy, UpdatedAt, UpdatedBy, IsActive
    )
    VALUES (
        @NewEventId, @Part2_Start, @Part2_End, 'P',
        'Created by fix script - Part 2 of section 2725 after 2759 interruption',
        GETUTCDATE(), @User, GETUTCDATE(), @User, 1
    );

    -- Insert into ProductionEvents
    INSERT INTO M2.DraftProductionEvents (
        EventId, SectionId, StatusCode, FirstBilletTimestamp, IsMatched
    )
    VALUES (
        @NewEventId, @SectionId_2725, 'C', @Part2_Start, 1
    );

    INSERT INTO M2.ProductionEvents (
        EventId, SectionId, StatusCode, FirstBilletTimestamp, IsMatched
    )
    VALUES (
        @NewEventId, @SectionId_2725, 'C', @Part2_Start, 1
    );

    PRINT '  New Event and ProductionEvent created successfully.';
    PRINT '';

    -- =========================================================================
    -- STEP 3: Link Part 2 campaigns to the new Event
    -- =========================================================================
    PRINT 'STEP 3: Linking Part 2 campaigns (1446-1478) to new Event ' + CAST(@NewEventId AS VARCHAR);

    DECLARE @CampaignsUpdated INT;

    UPDATE M2.Campaigns
    SET EventId = @NewEventId
    WHERE CampaignId >= 1446
      AND CampaignId <= 1478
      AND EventId = 99;  -- Safety check

    SET @CampaignsUpdated = @@ROWCOUNT;
    PRINT '  Campaigns updated: ' + CAST(@CampaignsUpdated AS VARCHAR);
    PRINT '';

    -- =========================================================================
    -- STEP 4: Verify the fix
    -- =========================================================================
    PRINT 'STEP 4: Verification';
    PRINT '';

    PRINT '  Event 99 (Part 1):';
    SELECT '    ' +
           'Start=' + CONVERT(VARCHAR, [Start], 120) +
           ', End=' + CONVERT(VARCHAR, [End], 120) +
           ', Campaigns=' + CAST((SELECT COUNT(*) FROM M2.Campaigns WHERE EventId = 99) AS VARCHAR)
    FROM M2.Events WHERE EventId = 99;

    PRINT '';
    PRINT '  Event ' + CAST(@NewEventId AS VARCHAR) + ' (Part 2):';
    SELECT '    ' +
           'Start=' + CONVERT(VARCHAR, [Start], 120) +
           ', End=' + CONVERT(VARCHAR, [End], 120) +
           ', Campaigns=' + CAST((SELECT COUNT(*) FROM M2.Campaigns WHERE EventId = @NewEventId) AS VARCHAR)
    FROM M2.Events WHERE EventId = @NewEventId;

    PRINT '';
    PRINT '  Campaigns without EventId (2759 - will be handled by sync):';
    SELECT '    Count=' + CAST(COUNT(*) AS VARCHAR) +
           ', Start=' + CONVERT(VARCHAR, MIN([Start]), 120) +
           ', End=' + CONVERT(VARCHAR, MAX([End]), 120)
    FROM M2.Campaigns
    WHERE EventId IS NULL AND [Start] >= '2026-01-26';

    PRINT '';
    PRINT '============================================';
    PRINT 'FIX COMPLETED SUCCESSFULLY';
    PRINT '============================================';

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;

    PRINT '';
    PRINT '!!! ERROR - TRANSACTION ROLLED BACK !!!';
    PRINT 'Error: ' + ERROR_MESSAGE();
    PRINT 'Line: ' + CAST(ERROR_LINE() AS VARCHAR);

    THROW;
END CATCH;
