-- ============================================================
-- BATCH 1: Add new columns
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 1: Add new columns ===';

    IF COL_LENGTH('dbo.ATButton', 'QueueId') IS NULL
    ALTER TABLE [dbo].[ATButton] ADD [QueueId] INT NULL;

    IF COL_LENGTH('dbo.ATWorkItemReference', 'QueueId') IS NULL
    ALTER TABLE [dbo].[ATWorkItemReference] ADD [QueueId] INT NULL;

    IF COL_LENGTH('dbo.ATWorkItemReference', 'SourceQueueId') IS NULL
    ALTER TABLE [dbo].[ATWorkItemReference] ADD [SourceQueueId] INT NULL;

    IF COL_LENGTH('dbo.ATWorkItemReference', 'PendQueueId') IS NULL
    ALTER TABLE [dbo].[ATWorkItemReference] ADD [PendQueueId] INT NULL;

    IF COL_LENGTH('dbo.ATInboxEvent', 'InboxId') IS NULL
    ALTER TABLE [dbo].[ATInboxEvent] ADD [InboxId] INT NULL;

    COMMIT TRANSACTION;
    PRINT '=== Step 1 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 1 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 2: Populate new columns with data
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 2: Populate new columns with data ===';

    IF EXISTS (SELECT 1 FROM [dbo].[ATButton] WHERE [QueueId] IS NULL AND [Queue] IS NOT NULL)
        BEGIN
            UPDATE b SET b.[QueueId] = q.[Id]
            FROM [dbo].[ATButton] b
                     INNER JOIN [dbo].[ATQueue] q ON b.[Queue] = q.[Queue];
        END

    IF EXISTS (SELECT 1 FROM [dbo].[ATWorkItemReference] WHERE [QueueId] IS NULL AND [Queue] IS NOT NULL)
        BEGIN
            UPDATE wir SET wir.[QueueId] = q.[Id]
            FROM [dbo].[ATWorkItemReference] wir
                     INNER JOIN [dbo].[ATQueue] q ON wir.[Queue] = q.[Queue];
        END

    IF EXISTS (SELECT 1 FROM [dbo].[ATWorkItemReference] WHERE [SourceQueueId] IS NULL AND [SourceQueue] IS NOT NULL)
        BEGIN
            UPDATE wir SET wir.[SourceQueueId] = q.[Id]
            FROM [dbo].[ATWorkItemReference] wir
                     INNER JOIN [dbo].[ATQueue] q ON wir.[SourceQueue] = q.[Queue];
        END

    IF EXISTS (SELECT 1 FROM [dbo].[ATWorkItemReference] WHERE [PendQueueId] IS NULL AND [PendQueue] IS NOT NULL)
        BEGIN
            UPDATE wir SET wir.[PendQueueId] = q.[Id]
            FROM [dbo].[ATWorkItemReference] wir
                     INNER JOIN [dbo].[ATQueue] q ON wir.[PendQueue] = q.[Queue];
        END

    IF EXISTS (SELECT 1 FROM [dbo].[ATInboxEvent] WHERE [InboxId] IS NULL AND [Inbox] IS NOT NULL)
        BEGIN
            UPDATE ie SET ie.[InboxId] = q.[Id]
            FROM [dbo].[ATInboxEvent] ie
                     INNER JOIN [dbo].[ATQueue] q ON ie.[Inbox] = q.[Queue];
        END

    COMMIT TRANSACTION;
    PRINT '=== Step 2 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 2 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 3: Drop foreign key constraints
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 3: Drop foreign key constraints ===';

    IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATButton_ATQueue' AND parent_object_id = OBJECT_ID('dbo.ATButton'))
    ALTER TABLE [dbo].[ATButton] DROP CONSTRAINT [FK_ATButton_ATQueue];

    IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'ATWorkItemReference_Queue' AND parent_object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    ALTER TABLE [dbo].[ATWorkItemReference] DROP CONSTRAINT [ATWorkItemReference_Queue];

    IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'ATWorkItemReference_SourceQueue' AND parent_object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    ALTER TABLE [dbo].[ATWorkItemReference] DROP CONSTRAINT [ATWorkItemReference_SourceQueue];

    IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATInboxEvent_ATQueue' AND parent_object_id = OBJECT_ID('dbo.ATInboxEvent'))
    ALTER TABLE [dbo].[ATInboxEvent] DROP CONSTRAINT [FK_ATInboxEvent_ATQueue];

    COMMIT TRANSACTION;
    PRINT '=== Step 3 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 3 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 4: Drop indexes on old columns
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 4: Drop indexes on old columns ===';

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATWorkItemReference_IX_Queue' AND object_id = OBJECT_ID('dbo.ATWorkItemReference'))
        DROP INDEX [ATWorkItemReference_IX_Queue] ON [dbo].[ATWorkItemReference];

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATWorkItemReference_IX_SQueue' AND object_id = OBJECT_ID('dbo.ATWorkItemReference'))
        DROP INDEX [ATWorkItemReference_IX_SQueue] ON [dbo].[ATWorkItemReference];

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATWorkItemReference_Composite1' AND object_id = OBJECT_ID('dbo.ATWorkItemReference'))
        DROP INDEX [ATWorkItemReference_Composite1] ON [dbo].[ATWorkItemReference];

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ATInboxEvent_Inbox' AND object_id = OBJECT_ID('dbo.ATInboxEvent'))
        DROP INDEX [IX_ATInboxEvent_Inbox] ON [dbo].[ATInboxEvent];

    COMMIT TRANSACTION;
    PRINT '=== Step 4 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 4 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 5: Drop unique constraints on old columns
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 5: Drop unique constraints on old columns ===';

    IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'UK_Name_Queue' AND parent_object_id = OBJECT_ID('dbo.ATButton'))
    ALTER TABLE [dbo].[ATButton] DROP CONSTRAINT [UK_Name_Queue];

    COMMIT TRANSACTION;
    PRINT '=== Step 5 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 5 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 6: Drop old columns
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 6: Drop old columns ===';

    IF COL_LENGTH('dbo.ATButton', 'Queue') IS NOT NULL
    ALTER TABLE [dbo].[ATButton] DROP COLUMN [Queue];

    IF COL_LENGTH('dbo.ATWorkItemReference', 'Queue') IS NOT NULL
    ALTER TABLE [dbo].[ATWorkItemReference] DROP COLUMN [Queue];

    IF COL_LENGTH('dbo.ATWorkItemReference', 'SourceQueue') IS NOT NULL
    ALTER TABLE [dbo].[ATWorkItemReference] DROP COLUMN [SourceQueue];

    IF COL_LENGTH('dbo.ATWorkItemReference', 'PendQueue') IS NOT NULL
    ALTER TABLE [dbo].[ATWorkItemReference] DROP COLUMN [PendQueue];

    IF COL_LENGTH('dbo.ATInboxEvent', 'Inbox') IS NOT NULL
    ALTER TABLE [dbo].[ATInboxEvent] DROP COLUMN [Inbox];

    COMMIT TRANSACTION;
    PRINT '=== Step 6 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 6 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 7: Set new columns to NOT NULL where required
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 7: Set new columns to NOT NULL where required ===';

    IF COLUMNPROPERTY(OBJECT_ID('dbo.ATButton'), 'QueueId', 'AllowsNull') = 1
    ALTER TABLE [dbo].[ATButton] ALTER COLUMN [QueueId] INT NOT NULL;

    COMMIT TRANSACTION;
    PRINT '=== Step 7 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 7 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 8: Drop old primary key and constraints from ATQueue
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 8: Drop old primary key and constraints from ATQueue ===';

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

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

    IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'PK_Queue' AND parent_object_id = OBJECT_ID('dbo.ATQueue') AND type = 'PK')
    ALTER TABLE [dbo].[ATQueue] DROP CONSTRAINT [PK_Queue];

    IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'UK_Queue_ProcessId' AND parent_object_id = OBJECT_ID('dbo.ATQueue'))
    ALTER TABLE [dbo].[ATQueue] DROP CONSTRAINT [UK_Queue_ProcessId];

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATQueue_IX_ProcessId' AND object_id = OBJECT_ID('dbo.ATQueue'))
        DROP INDEX [ATQueue_IX_ProcessId] ON [dbo].[ATQueue];

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATQueue_ObjectID' AND object_id = OBJECT_ID('dbo.ATQueue'))
        DROP INDEX [ATQueue_ObjectID] ON [dbo].[ATQueue];

    COMMIT TRANSACTION;
    PRINT '=== Step 8 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 8 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 9: Create new primary key on ATQueue
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 9: Create new primary key on ATQueue ===';

    IF NOT EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'PK_ATQueue' AND parent_object_id = OBJECT_ID('dbo.ATQueue') AND type = 'PK')
    ALTER TABLE [dbo].[ATQueue] ADD CONSTRAINT [PK_ATQueue] PRIMARY KEY CLUSTERED ([Id]);

    COMMIT TRANSACTION;
    PRINT '=== Step 9 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 9 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 10: Add unique constraints back on ATQueue
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 10: Add unique constraints back on ATQueue ===';

    IF NOT EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'UK_ATQueue_Queue' AND parent_object_id = OBJECT_ID('dbo.ATQueue'))
    ALTER TABLE [dbo].[ATQueue] ADD CONSTRAINT [UK_ATQueue_Queue] UNIQUE NONCLUSTERED ([Queue]);

    IF NOT EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'UK_ATQueue_Queue_ProcessId' AND parent_object_id = OBJECT_ID('dbo.ATQueue'))
    ALTER TABLE [dbo].[ATQueue] ADD CONSTRAINT [UK_ATQueue_Queue_ProcessId] UNIQUE ([Queue], [ProcessId]);

    COMMIT TRANSACTION;
    PRINT '=== Step 10 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 10 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 11: Recreate indexes on ATQueue
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 11: Recreate indexes on ATQueue ===';

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATQueue_IX_ProcessId' AND object_id = OBJECT_ID('dbo.ATQueue'))
    CREATE NONCLUSTERED INDEX [ATQueue_IX_ProcessId] ON [dbo].[ATQueue]([ProcessId] ASC);

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATQueue_ObjectID' AND object_id = OBJECT_ID('dbo.ATQueue'))
    CREATE NONCLUSTERED INDEX [ATQueue_ObjectID] ON [dbo].[ATQueue]([ObjectId] ASC);

    COMMIT TRANSACTION;
    PRINT '=== Step 11 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 11 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 12: Create new foreign key constraints
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 12: Create new foreign key constraints ===';

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATButton_ATQueue' AND parent_object_id = OBJECT_ID('dbo.ATButton'))
    ALTER TABLE [dbo].[ATButton] ADD CONSTRAINT [FK_ATButton_ATQueue]
        FOREIGN KEY ([QueueId]) REFERENCES [dbo].[ATQueue]([Id]);

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATWorkItemReference_Queue' AND parent_object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    ALTER TABLE [dbo].[ATWorkItemReference] ADD CONSTRAINT [FK_ATWorkItemReference_Queue]
        FOREIGN KEY ([QueueId]) REFERENCES [dbo].[ATQueue]([Id]);

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATWorkItemReference_SourceQueue' AND parent_object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    ALTER TABLE [dbo].[ATWorkItemReference] ADD CONSTRAINT [FK_ATWorkItemReference_SourceQueue]
        FOREIGN KEY ([SourceQueueId]) REFERENCES [dbo].[ATQueue]([Id]);

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATWorkItemReference_PendQueue' AND parent_object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    ALTER TABLE [dbo].[ATWorkItemReference] ADD CONSTRAINT [FK_ATWorkItemReference_PendQueue]
        FOREIGN KEY ([PendQueueId]) REFERENCES [dbo].[ATQueue]([Id]);

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATInboxEvent_ATQueue' AND parent_object_id = OBJECT_ID('dbo.ATInboxEvent'))
    ALTER TABLE [dbo].[ATInboxEvent] ADD CONSTRAINT [FK_ATInboxEvent_ATQueue]
        FOREIGN KEY ([InboxId]) REFERENCES [dbo].[ATQueue]([Id]);

    COMMIT TRANSACTION;
    PRINT '=== Step 12 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 12 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 13: Recreate indexes on referencing tables
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 13: Recreate indexes on referencing tables ===';

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATWorkItemReference_IX_QueueId' AND object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_QueueId]
        ON [dbo].[ATWorkItemReference]([QueueId] ASC);

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATWorkItemReference_IX_SourceQueueId' AND object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_SourceQueueId]
        ON [dbo].[ATWorkItemReference]([SourceQueueId] ASC);

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ATWorkItemReference_Composite1' AND object_id = OBJECT_ID('dbo.ATWorkItemReference'))
    CREATE NONCLUSTERED INDEX [ATWorkItemReference_Composite1]
        ON [dbo].[ATWorkItemReference]([QueueId] ASC, [SourceQueueId] ASC, [ServerRequest] ASC);

    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ATInboxEvent_InboxId' AND object_id = OBJECT_ID('dbo.ATInboxEvent'))
    CREATE NONCLUSTERED INDEX [IX_ATInboxEvent_InboxId]
        ON [dbo].[ATInboxEvent]([InboxId] ASC);

    COMMIT TRANSACTION;
    PRINT '=== Step 13 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 13 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

-- ============================================================
-- BATCH 14: Recreate unique constraints
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
    PRINT '=== Step 14: Recreate unique constraints ===';

    IF NOT EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'UK_ATButton_Name_QueueId' AND parent_object_id = OBJECT_ID('dbo.ATButton'))
    ALTER TABLE [dbo].[ATButton] ADD CONSTRAINT [UK_ATButton_Name_QueueId]
        UNIQUE ([ButtonName], [QueueId]);

    COMMIT TRANSACTION;
    PRINT '=== Step 14 completed ===';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Step 14 failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

PRINT '=== ALL MIGRATION STEPS COMPLETED SUCCESSFULLY ===';