SET QUOTED_IDENTIFIER ON

GO

-- ============================================================================
-- Pre-Deployment Database Fix Script
-- Generated: 2026-01-08
-- ============================================================================
-- Updated based on current production state
-- ============================================================================

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    PRINT '============================================================================';
    PRINT 'Pre-Deployment Fix Script';
    PRINT 'Run Date: ' + CONVERT(VARCHAR, GETDATE(), 120);
    PRINT '============================================================================';
    PRINT '';

    -- ============================================================================
    -- 1.1 DEACTIVATE ROLLSETS WITHOUT ROLLS (11 rollsets)
    -- ============================================================================
    PRINT '1.1 Deactivating rollsets without rolls...';

    UPDATE M2.Rollsets
    SET IsActive = 0, UpdatedAt = GETUTCDATE()
    WHERE RollsetId IN (64, 65, 74, 76, 94, 97, 153, 261, 276, 310, 311);

    PRINT '    Rollsets deactivated: ' + CAST(@@ROWCOUNT AS VARCHAR);
    PRINT '';

    -- ============================================================================
    -- 1.2 DEACTIVATE ROLLSET 75 (inactive roll UR - 23-069)
    -- ============================================================================
    PRINT '1.2 Deactivating rollset 75 (UR - 23-069 inactive)...';

    UPDATE M2.Rollsets
    SET IsActive = 0, UpdatedAt = GETUTCDATE()
    WHERE RollsetId = 75;

    PRINT '    Rollsets deactivated: ' + CAST(@@ROWCOUNT AS VARCHAR);
    PRINT '';

    -- ============================================================================
    -- 2.1.a ASSIGN DONUTS TO URs IN ACTIVE CARRIERS (4 URs)
    -- URs: 17838 (fc 23), 17784 (FC8), 17858 (FC10), 17859 (FC10)
    -- Using available donuts: 16632, 17234, 17235, 17713
    -- All need SectionFamilyId = 9 (4.25")
    -- ============================================================================
    PRINT '2.1.a Assigning donuts to URs in active carriers...';

    -- Update SectionFamilyId of donuts to 9 if NULL
    UPDATE M2.Donuts
    SET SectionFamilyId = 9
    WHERE PartId IN (16632, 17234, 17235) AND SectionFamilyId IS NULL;

    PRINT '    SectionFamilyId updated on donuts: ' + CAST(@@ROWCOUNT AS VARCHAR);

    -- Assign donut 16632 to UR 17838 (UR - 683-021, fc 23)
    UPDATE M2.UniversalRolls
    SET DonutPartId = 16632, AssembledAt = GETUTCDATE()
    WHERE PartId = 17838 AND DonutPartId IS NULL;

    -- Assign donut 17234 to UR 17784 (UR - 683-019, FC8)
    UPDATE M2.UniversalRolls
    SET DonutPartId = 17234, AssembledAt = GETUTCDATE()
    WHERE PartId = 17784 AND DonutPartId IS NULL;

    -- Assign donut 17235 to UR 17858 (UR - 683-089, FC10)
    UPDATE M2.UniversalRolls
    SET DonutPartId = 17235, AssembledAt = GETUTCDATE()
    WHERE PartId = 17858 AND DonutPartId IS NULL;

    -- Assign donut 17713 to UR 17859 (UR - 683-090, FC10) - already has SectionFamilyId=9
    UPDATE M2.UniversalRolls
    SET DonutPartId = 17713, AssembledAt = GETUTCDATE()
    WHERE PartId = 17859 AND DonutPartId IS NULL;

    PRINT '    URs updated: 4';
    PRINT '';

    -- ============================================================================
    -- 2.1.b CREATE AND ASSIGN DONUTS TO UNPROTECTED URs (21 URs)
    -- Create donuts with name "Donut - {URPartId}"
    -- ============================================================================
    PRINT '2.1.b Creating and assigning donuts to unprotected URs...';

    DECLARE @NewPartId INT;
    DECLARE @URPartId INT;
    DECLARE @SectionFamilyId INT;
    DECLARE @CreatedCount INT = 0;

    DECLARE ur_cursor CURSOR FOR
    SELECT
        ur.PartId AS URPartId,
        sec.SectionFamilyId
    FROM M2.Rollsets rs
    INNER JOIN M2.UniversalRolls ur ON (rs.TopRollId = ur.PartId OR rs.BottomRollId = ur.PartId)
    LEFT JOIN M2.Sections sec ON rs.SectionId = sec.SectionId
    WHERE rs.IsActive = 1
      AND ur.DonutPartId IS NULL
      AND ur.DeletedAt IS NULL
      AND ur.PartId NOT IN (17838, 17784, 17858, 17859)  -- Exclude the 4 already assigned above
    ORDER BY ur.PartId;

    OPEN ur_cursor;
    FETCH NEXT FROM ur_cursor INTO @URPartId, @SectionFamilyId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Create Part
        INSERT INTO M2.Parts (PartName, PartTypeCode, IsActive)
        VALUES ('Donut - ' + CAST(@URPartId AS VARCHAR), 'D', 1);

        SET @NewPartId = SCOPE_IDENTITY();

        -- Create Donut
        INSERT INTO M2.Donuts (PartId, SectionFamilyId, DonutStatusCode)
        VALUES (@NewPartId, @SectionFamilyId, 'R');

        -- Assign to UR
        UPDATE M2.UniversalRolls
        SET DonutPartId = @NewPartId, AssembledAt = GETUTCDATE()
        WHERE PartId = @URPartId;

        SET @CreatedCount = @CreatedCount + 1;

        FETCH NEXT FROM ur_cursor INTO @URPartId, @SectionFamilyId;
    END

    CLOSE ur_cursor;
    DEALLOCATE ur_cursor;

    PRINT '    Donuts created and assigned: ' + CAST(@CreatedCount AS VARCHAR);
    PRINT '';

    -- ============================================================================
    -- 3.1 REACTIVATE ROLLSET 318 FOR FC4 (first deactivating rollset 308)
    -- Rollset 308 has roll 16218 but is NOT in any active carrier
    -- FC 22 / Rollset 312 was already fixed by operators
    -- ============================================================================
    PRINT '3.1 Reactivating rollset 318 for FC4...';

    -- Deactivate rollset 308 (releases roll 16218)
    UPDATE M2.Rollsets
    SET IsActive = 0, UpdatedAt = GETUTCDATE()
    WHERE RollsetId = 308;

    PRINT '    Rollset 308 deactivated: ' + CAST(@@ROWCOUNT AS VARCHAR);

    -- Reactivate rollset 318
    UPDATE M2.Rollsets
    SET IsActive = 1, UpdatedAt = GETUTCDATE()
    WHERE RollsetId = 318;

    PRINT '    Rollset 318 reactivated: ' + CAST(@@ROWCOUNT AS VARCHAR);
    PRINT '';

    -- ============================================================================
    -- 4. SET DeletedAt ON CarrierInstances OF DISCONTINUED CARRIERS
    -- Carriers with Part.IsActive = 0 are discontinued/garbage.
    -- Their CarrierInstances should have DeletedAt set so protection queries
    -- correctly ignore them (they only check ci.DeletedAt IS NULL).
    -- ============================================================================
    PRINT '4. Setting DeletedAt on CarrierInstances of discontinued carriers...';

    UPDATE ci
    SET ci.DeletedAt = GETUTCDATE()
    FROM M2.CarrierInstances ci
    INNER JOIN M2.Parts p ON ci.PartId = p.PartId
    WHERE p.IsActive = 0
      AND ci.DeletedAt IS NULL;

    PRINT '    CarrierInstances updated: ' + CAST(@@ROWCOUNT AS VARCHAR);
    PRINT '';

    -- ============================================================================
    -- POST-FIX VALIDATION
    -- ============================================================================
    PRINT '============================================================================';
    PRINT 'POST-FIX VALIDATION';
    PRINT '============================================================================';

    DECLARE @Issues INT;

    SELECT @Issues = COUNT(*) FROM M2.Rollsets
    WHERE IsActive = 1 AND (TopRollId IS NULL OR BottomRollId IS NULL);
    PRINT '1.1 Rollsets without rolls: ' + CAST(@Issues AS VARCHAR);

    SELECT @Issues = COUNT(*)
    FROM M2.Rollsets rs
    INNER JOIN M2.Parts p ON rs.TopRollId = p.PartId
    WHERE rs.IsActive = 1 AND p.IsActive = 0;
    PRINT '1.2 Rollsets with inactive top roll: ' + CAST(@Issues AS VARCHAR);

    SELECT @Issues = COUNT(*)
    FROM M2.Rollsets rs
    INNER JOIN M2.UniversalRolls ur ON (rs.TopRollId = ur.PartId OR rs.BottomRollId = ur.PartId)
    WHERE rs.IsActive = 1 AND ur.DonutPartId IS NULL AND ur.DeletedAt IS NULL;
    PRINT '2.1 URs without donut in active rollsets: ' + CAST(@Issues AS VARCHAR);

    ;WITH LatestCI AS (
        SELECT ci.*, ROW_NUMBER() OVER (PARTITION BY ci.PartId ORDER BY ci.CarrierInstanceId DESC) AS rn
        FROM M2.CarrierInstances ci WHERE ci.DeletedAt IS NULL
    )
    SELECT @Issues = COUNT(*)
    FROM LatestCI lci
    INNER JOIN M2.Parts cp ON lci.PartId = cp.PartId
    INNER JOIN M2.Rollsets rs ON lci.RollsetId = rs.RollsetId
    WHERE lci.rn = 1 AND lci.CarrierStatusCode IN ('A','C','S','I','U') AND rs.IsActive = 0 AND cp.IsActive = 1;
    PRINT '3.1 Active carriers (IsActive=1) with inactive rollset: ' + CAST(@Issues AS VARCHAR);

    SELECT @Issues = COUNT(*)
    FROM M2.CarrierInstances ci
    INNER JOIN M2.Parts p ON ci.PartId = p.PartId
    WHERE p.IsActive = 0 AND ci.DeletedAt IS NULL;
    PRINT '4.0 CarrierInstances of discontinued carriers without DeletedAt: ' + CAST(@Issues AS VARCHAR);

    COMMIT TRANSACTION;
    PRINT '============================================================================';
    PRINT 'SCRIPT COMPLETED ';
    PRINT '============================================================================';
    


END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    PRINT 'ERROR: ' + ERROR_MESSAGE();
    PRINT 'Changes reverted.';
END CATCH;

-- Permissions

EXEC SEC.UpsertProcedureAndSetToProfile 'M2.ResetDraft', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.ConfirmEvents', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.UpsertDraftEvent', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.UpsertDraftProductionEvent', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetEventsToCompare', 'admin Profile', 'sql'

EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetRollshopNotifications', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.SwapRollsetsRolls', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.AcknowledgeNotification', 'admin Profile', 'sql'

EXEC SEC.UpsertProcedureAndSetToProfile 'M2.RestoreFromSnapshot', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.GetDraftSnapshots', 'admin Profile', 'sql'
EXEC SEC.UpsertProcedureAndSetToProfile 'M2.CreateDraftSnapshot', 'admin Profile', 'sql'

-- Initialize Draft Mode 
EXEC M2.ResetDraft
	@User = 'admin'

-- Snapshots setting
INSERT INTO [UTL].[catSettings] ([SettingName], [Value], [Description])
  VALUES ('SCHEDULING.ENABLE_DRAFT_SNAPSHOTS', '0', 'Enable draft snapshot functionality (Save/Restore buttons) in production events calendar. 0=Disabled, 1=Enabled');
