-- ============================================================
-- BATCH 1: Remove Inbox column from ATUser if it exists
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 1: Remove Inbox column from ATUser if it exists ===';

	IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ATUser') AND name = 'Inbox')
        BEGIN
            DECLARE @ConstraintName NVARCHAR(255)
            SELECT @ConstraintName = fk.name
            FROM sys.foreign_keys fk
                     INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
            WHERE fk.parent_object_id = OBJECT_ID('dbo.ATUser')
              AND COL_NAME(fkc.parent_object_id, fkc.parent_column_id) = 'Inbox'

            IF @ConstraintName IS NOT NULL
                EXEC('ALTER TABLE [dbo].[ATUser] DROP CONSTRAINT [' + @ConstraintName + ']')

            ALTER TABLE [dbo].[ATUser] DROP COLUMN [Inbox]
            PRINT 'Removed Inbox column from ATUser'
        END

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

-- ============================================================
-- BATCH 2: Add Id column if not exists
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 2: Add Id column if not exists ===';

    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ATQueue') AND name = 'Id')
        BEGIN
            ALTER TABLE [dbo].[ATQueue] ADD [Id] INT NOT NULL IDENTITY (1,1)
            PRINT 'Added Id column'
        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: Add ProcessId column if not exists
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 3: Add ProcessId column if not exists ===';

    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ATQueue') AND name = 'ProcessId')
        BEGIN
            ALTER TABLE [dbo].[ATQueue] ADD [ProcessId] INT NULL
            PRINT 'Added ProcessId column'
        END

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

-- ============================================================
-- BATCH 4: Add EventId column if not exists
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 4: Add EventId column if not exists ===';

    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ATQueue') AND name = 'EventId')
        BEGIN
            ALTER TABLE [dbo].[ATQueue] ADD [EventId] INT NULL
            PRINT 'Added EventId column'
        END

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

-- ============================================================
-- BATCH 5: Populate ProcessId and EventId from ATEventDefinition
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 5: Populate ProcessId and EventId from ATEventDefinition ===';

    UPDATE [dbo].[ATQueue]
    SET [ProcessId] = aed.[ProcessID],
        [EventId] = aed.[EventID]
    FROM [dbo].[ATQueue] aq
             INNER JOIN [dbo].[ATEventDefinition] aed ON aq.[Queue] = aed.[EventName]
    WHERE aq.[ProcessId] IS NULL OR aq.[EventId] IS NULL

    PRINT 'Populated ProcessId and EventId: ' + CAST(@@ROWCOUNT AS NVARCHAR(10)) + ' row(s) updated'

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

-- ============================================================
-- BATCH 6: Delete records without ProcessId or EventId
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 6: Delete records without ProcessId or EventId ===';

    DECLARE @DeletedRows INT
    DELETE FROM [dbo].[ATQueue]
    WHERE [ProcessId] IS NULL OR [EventId] IS NULL

    SET @DeletedRows = @@ROWCOUNT
    IF @DeletedRows > 0
        PRINT 'Deleted ' + CAST(@DeletedRows AS NVARCHAR(10)) + ' record(s) without ProcessId/EventId'

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

-- ============================================================
-- BATCH 7: Drop existing indexes and constraints
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 7: Drop existing indexes and constraints ===';

    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_IX_EventId' AND object_id = OBJECT_ID('dbo.ATQueue'))
        DROP INDEX [ATQueue_IX_EventId] ON [dbo].[ATQueue]

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

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

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

-- ============================================================
-- BATCH 8: Make columns NOT NULL
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 8: Make columns NOT NULL ===';

    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ATQueue') AND name = 'ProcessId' AND is_nullable = 1)
    ALTER TABLE [dbo].[ATQueue] ALTER COLUMN [ProcessId] INT NOT NULL

    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ATQueue') AND name = 'EventId' AND is_nullable = 1)
    ALTER TABLE [dbo].[ATQueue] ALTER COLUMN [EventId] INT NOT NULL

    PRINT 'Set ProcessId and EventId to NOT NULL'

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

-- ============================================================
-- BATCH 9: Add foreign key constraints
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 9: Add foreign key constraints ===';

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATQueue_ATProcessDefinition' AND parent_object_id = OBJECT_ID('dbo.ATQueue'))
    ALTER TABLE [dbo].[ATQueue]
        ADD CONSTRAINT [FK_ATQueue_ATProcessDefinition]
            FOREIGN KEY ([ProcessId]) REFERENCES [dbo].[ATProcessDefinition]([ProcessId])

    IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_ATQueue_ATEvent' AND parent_object_id = OBJECT_ID('dbo.ATQueue'))
    ALTER TABLE [dbo].[ATQueue]
        ADD CONSTRAINT [FK_ATQueue_ATEvent]
            FOREIGN KEY ([EventId]) REFERENCES [dbo].[ATEventDefinition]([EventId])

    PRINT 'Added foreign key constraints'

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

-- ============================================================
-- BATCH 10: Create indexes
-- ============================================================
BEGIN TRANSACTION;
BEGIN TRY
	PRINT '=== Step 10: Create indexes ===';

    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_IX_EventId' AND object_id = OBJECT_ID('dbo.ATQueue'))
    CREATE NONCLUSTERED INDEX [ATQueue_IX_EventId] ON [dbo].[ATQueue]([EventId] ASC)

    PRINT 'Created indexes'

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

PRINT 'ATQueue migration completed successfully'