using Npgsql;
using System.Data;

namespace GmRelay.Bot.Tests.Web;

[Collection(PortfolioMigrationPostgresCollection.Name)]
public sealed class PortfolioMigrationPostgresTests(PortfolioMigrationPostgresFixture fixture)
{
    private static readonly TimeSpan CommandTimeout = TimeSpan.FromSeconds(10);
    private static long nextLegacyId = 1000;

    [Fact]
    public async Task MigrationsV001ThroughV029_ShouldApplyToPostgres17()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();

        Assert.Equal(29, database.AppliedMigrationCount);

        await using var connection = await database.OpenConnectionAsync();
        Assert.Equal(4, await ExecuteScalarAsync<long>(
            connection,
            """
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = 'public'
              AND table_name IN (
                  'portfolio_games',
                  'portfolio_game_sessions',
                  'portfolio_game_masters',
                  'portfolio_game_reviews')
            """));
    }

    [Theory]
    [InlineData("portfolio_game_sessions")]
    [InlineData("portfolio_game_masters")]
    public async Task DirectRequiredLinkDeletion_ShouldFailCommitForPublishedCard(string linkTable)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: true);
        await using var transaction = await connection.BeginTransactionAsync();

        await ExecuteNonQueryAsync(
            connection,
            $"DELETE FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId",
            transaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId));

        var exception = await Assert.ThrowsAsync<PostgresException>(
            () => transaction.CommitAsync().WaitAsync(CommandTimeout));

        Assert.Equal(PostgresErrorCodes.CheckViolation, exception.SqlState);
    }

    [Fact]
    public async Task ExplicitUnpublishThenSessionDelete_ShouldCommitAndPreserveFirstPublishedAt()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: true);
        await using var transaction = await connection.BeginTransactionAsync();

        await ExecuteNonQueryAsync(
            connection,
            """
            UPDATE portfolio_games
            SET is_public = false,
                updated_at = now()
            WHERE id = @portfolioGameId
            """,
            transaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId));
        await ExecuteNonQueryAsync(
            connection,
            "DELETE FROM sessions WHERE id = @sessionId",
            transaction,
            new NpgsqlParameter("sessionId", seed.SessionIds[0]));

        await transaction.CommitAsync().WaitAsync(CommandTimeout);

        Assert.False(await ExecuteScalarAsync<bool>(
            connection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(seed.PublishedAt, await ExecuteScalarAsync<DateTime>(
            connection,
            "SELECT published_at FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(0, await ExecuteScalarAsync<long>(
            connection,
            "SELECT COUNT(*) FROM portfolio_game_sessions WHERE portfolio_game_id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Theory]
    [InlineData(true)]
    [InlineData(false)]
    public async Task ConcurrentPublishAndLinkDelete_ShouldSerializeBeforeRowsAndRejectInvalidPublicCard(
        bool publishMutatesFirst)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var publishConnection = await database.OpenConnectionAsync();
        await using var deleteConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(publishConnection, isPublic: false);
        await using var publishTransaction = await publishConnection.BeginTransactionAsync();
        await using var deleteTransaction = await deleteConnection.BeginTransactionAsync();
        var publishPid = await GetBackendPidAsync(publishConnection, publishTransaction);
        var deletePid = await GetBackendPidAsync(deleteConnection, deleteTransaction);

        if (publishMutatesFirst)
        {
            Assert.Equal(1, await PublishPortfolioGameAsync(
                publishConnection,
                publishTransaction,
                seed.PortfolioGameId));
            var deleteTask = DeletePortfolioGameLinksAsync(
                deleteConnection,
                deleteTransaction,
                "portfolio_game_sessions",
                seed.PortfolioGameId);

            await WaitUntilBlockedByAsync(observerConnection, deletePid, publishPid);
            Assert.Null(await CommitAndCaptureSqlStateAsync(publishTransaction).WaitAsync(CommandTimeout));
            Assert.Equal(1, await deleteTask.WaitAsync(CommandTimeout));
            Assert.Equal(
                PostgresErrorCodes.CheckViolation,
                await CommitAndCaptureSqlStateAsync(deleteTransaction).WaitAsync(CommandTimeout));
        }
        else
        {
            Assert.Equal(1, await DeletePortfolioGameLinksAsync(
                deleteConnection,
                deleteTransaction,
                "portfolio_game_sessions",
                seed.PortfolioGameId));
            var publishTask = PublishPortfolioGameAsync(
                publishConnection,
                publishTransaction,
                seed.PortfolioGameId);

            await WaitUntilBlockedByAsync(observerConnection, publishPid, deletePid);
            Assert.Null(await CommitAndCaptureSqlStateAsync(deleteTransaction).WaitAsync(CommandTimeout));
            Assert.Equal(1, await publishTask.WaitAsync(CommandTimeout));
            Assert.Equal(
                PostgresErrorCodes.CheckViolation,
                await CommitAndCaptureSqlStateAsync(publishTransaction).WaitAsync(CommandTimeout));
        }

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.Equal(publishMutatesFirst, await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(publishMutatesFirst ? 1L : 0L, await ExecuteScalarAsync<long>(
            verificationConnection,
            "SELECT COUNT(*) FROM portfolio_game_sessions WHERE portfolio_game_id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Theory]
    [InlineData("portfolio_game_sessions", "session_id")]
    [InlineData("portfolio_game_masters", "player_id")]
    public async Task ConcurrentRequiredLinkDeletes_ShouldSerializeAndRejectInvalidPublicCard(
        string linkTable,
        string linkColumn)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(
            seedConnection,
            isPublic: true,
            sessionCount: linkTable == "portfolio_game_sessions" ? 2 : 1,
            masterCount: linkTable == "portfolio_game_masters" ? 2 : 1);
        await using var firstConnection = await database.OpenConnectionAsync();
        await using var secondConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var firstTransaction = await firstConnection.BeginTransactionAsync();
        await using var secondTransaction = await secondConnection.BeginTransactionAsync();
        var firstPid = await GetBackendPidAsync(firstConnection, firstTransaction);
        var secondPid = await GetBackendPidAsync(secondConnection, secondTransaction);
        var linkIds = linkTable == "portfolio_game_sessions" ? seed.SessionIds : seed.MasterIds;

        await ExecuteNonQueryAsync(
            firstConnection,
            $"DELETE FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId AND {linkColumn} = @linkId",
            firstTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("linkId", linkIds[0]));
        var secondDeleteTask = ExecuteNonQueryAsync(
            secondConnection,
            $"DELETE FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId AND {linkColumn} = @linkId",
            secondTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("linkId", linkIds[1]));

        await WaitUntilBlockedByAsync(observerConnection, secondPid, firstPid);
        Assert.Null(await CommitAndCaptureSqlStateAsync(firstTransaction).WaitAsync(CommandTimeout));
        Assert.Equal(1, await secondDeleteTask.WaitAsync(CommandTimeout));
        Assert.Equal(
            PostgresErrorCodes.CheckViolation,
            await CommitAndCaptureSqlStateAsync(secondTransaction).WaitAsync(CommandTimeout));

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.True(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(1, await ExecuteScalarAsync<long>(
            verificationConnection,
            $"SELECT COUNT(*) FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Theory]
    [InlineData("portfolio_game_sessions", "session_id")]
    [InlineData("portfolio_game_masters", "player_id")]
    public async Task RepeatableReadConcurrentRequiredLinkDeletes_ShouldBeRejectedWithoutInvalidPublicCard(
        string linkTable,
        string linkColumn)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(
            seedConnection,
            isPublic: true,
            sessionCount: linkTable == "portfolio_game_sessions" ? 2 : 1,
            masterCount: linkTable == "portfolio_game_masters" ? 2 : 1);
        await using var firstConnection = await database.OpenConnectionAsync();
        await using var secondConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var firstTransaction = await firstConnection.BeginTransactionAsync(IsolationLevel.RepeatableRead);
        await using var secondTransaction = await secondConnection.BeginTransactionAsync(IsolationLevel.RepeatableRead);
        var firstPid = await GetBackendPidAsync(firstConnection, firstTransaction);
        var secondPid = await GetBackendPidAsync(secondConnection, secondTransaction);
        var linkIds = linkTable == "portfolio_game_sessions" ? seed.SessionIds : seed.MasterIds;

        await ExecuteNonQueryAsync(
            firstConnection,
            $"DELETE FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId AND {linkColumn} = @linkId",
            firstTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("linkId", linkIds[0]));
        var secondDeleteTask = ExecuteNonQueryAsync(
            secondConnection,
            $"DELETE FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId AND {linkColumn} = @linkId",
            secondTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("linkId", linkIds[1]));

        await WaitUntilBlockedByAsync(observerConnection, secondPid, firstPid);
        Assert.Equal(
            PostgresErrorCodes.FeatureNotSupported,
            await CommitAndCaptureSqlStateAsync(firstTransaction).WaitAsync(CommandTimeout));
        Assert.Equal(1, await secondDeleteTask.WaitAsync(CommandTimeout));
        Assert.Equal(
            PostgresErrorCodes.FeatureNotSupported,
            await CommitAndCaptureSqlStateAsync(secondTransaction).WaitAsync(CommandTimeout));

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.True(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(2, await ExecuteScalarAsync<long>(
            verificationConnection,
            $"SELECT COUNT(*) FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Theory]
    [InlineData(IsolationLevel.RepeatableRead)]
    [InlineData(IsolationLevel.Serializable)]
    public async Task NonReadCommittedPublishedCardLinkDelete_ShouldBeRejected(IsolationLevel isolationLevel)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: true);
        await using var transaction = await connection.BeginTransactionAsync(isolationLevel);

        await ExecuteNonQueryAsync(
            connection,
            "DELETE FROM portfolio_game_sessions WHERE portfolio_game_id = @portfolioGameId",
            transaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId));

        var exception = await Assert.ThrowsAsync<PostgresException>(
            () => transaction.CommitAsync().WaitAsync(CommandTimeout));

        Assert.Equal(PostgresErrorCodes.FeatureNotSupported, exception.SqlState);
    }

    [Theory]
    [InlineData(true)]
    [InlineData(false)]
    public async Task RepeatableReadDraftLinkDeleteRacingPublish_ShouldBeRejectedWithoutInvalidPublicCard(
        bool deleteMutatesFirst)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(seedConnection, isPublic: false);
        await using var deleteConnection = await database.OpenConnectionAsync();
        await using var publishConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var deleteTransaction = await deleteConnection.BeginTransactionAsync(IsolationLevel.RepeatableRead);
        await using var publishTransaction = await publishConnection.BeginTransactionAsync();
        var deletePid = await GetBackendPidAsync(deleteConnection, deleteTransaction);
        var publishPid = await GetBackendPidAsync(publishConnection, publishTransaction);

        if (deleteMutatesFirst)
        {
            Assert.Equal(1, await DeletePortfolioGameLinksAsync(
                deleteConnection,
                deleteTransaction,
                "portfolio_game_sessions",
                seed.PortfolioGameId));
            var publishTask = PublishPortfolioGameAsync(
                publishConnection,
                publishTransaction,
                seed.PortfolioGameId);

            await WaitUntilBlockedByAsync(observerConnection, publishPid, deletePid);
            Assert.Equal(
                PostgresErrorCodes.FeatureNotSupported,
                await CommitAndCaptureSqlStateAsync(deleteTransaction).WaitAsync(CommandTimeout));
            Assert.Equal(1, await publishTask.WaitAsync(CommandTimeout));
            Assert.Null(await CommitAndCaptureSqlStateAsync(publishTransaction).WaitAsync(CommandTimeout));
        }
        else
        {
            Assert.Equal(1, await PublishPortfolioGameAsync(
                publishConnection,
                publishTransaction,
                seed.PortfolioGameId));
            var deleteTask = DeletePortfolioGameLinksAsync(
                deleteConnection,
                deleteTransaction,
                "portfolio_game_sessions",
                seed.PortfolioGameId);

            await WaitUntilBlockedByAsync(observerConnection, deletePid, publishPid);
            Assert.Null(await CommitAndCaptureSqlStateAsync(publishTransaction).WaitAsync(CommandTimeout));
            Assert.Equal(1, await deleteTask.WaitAsync(CommandTimeout));
            Assert.Equal(
                PostgresErrorCodes.FeatureNotSupported,
                await CommitAndCaptureSqlStateAsync(deleteTransaction).WaitAsync(CommandTimeout));
        }

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.True(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(1, await ExecuteScalarAsync<long>(
            verificationConnection,
            "SELECT COUNT(*) FROM portfolio_game_sessions WHERE portfolio_game_id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Fact]
    public async Task PublishedCardFutureReschedule_ShouldAutomaticallyUnpublishAndPreserveFirstPublishedAt()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: true);

        await ExecuteNonQueryAsync(
            connection,
            "UPDATE sessions SET scheduled_at = now() + interval '1 day' WHERE id = @sessionId",
            parameters: new NpgsqlParameter("sessionId", seed.SessionIds[0]));

        Assert.False(await ExecuteScalarAsync<bool>(
            connection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(seed.PublishedAt, await ExecuteScalarAsync<DateTime>(
            connection,
            "SELECT published_at FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Fact]
    public async Task PublishedCardPastFuturePastReschedule_ShouldRemainPublicAndPreserveFirstPublishedAt()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: true);
        await using var transaction = await connection.BeginTransactionAsync();

        await ExecuteNonQueryAsync(
            connection,
            "UPDATE sessions SET scheduled_at = now() + interval '1 day' WHERE id = @sessionId",
            transaction,
            new NpgsqlParameter("sessionId", seed.SessionIds[0]));
        await ExecuteNonQueryAsync(
            connection,
            "UPDATE sessions SET scheduled_at = now() - interval '2 days' WHERE id = @sessionId",
            transaction,
            new NpgsqlParameter("sessionId", seed.SessionIds[0]));

        await transaction.CommitAsync().WaitAsync(CommandTimeout);

        Assert.True(await ExecuteScalarAsync<bool>(
            connection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(seed.PublishedAt, await ExecuteScalarAsync<DateTime>(
            connection,
            "SELECT published_at FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
    }

    [Fact]
    public async Task ConcurrentBatchFutureReschedules_ShouldSerializeBeforeSessionRowsWithoutDeadlock()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var firstSeed = await SeedCardAsync(seedConnection, isPublic: true, sessionCount: 2);
        var secondSeed = await SeedCardAsync(seedConnection, isPublic: true, sessionCount: 2);

        await using var firstConnection = await database.OpenConnectionAsync();
        await using var secondConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var firstTransaction = await firstConnection.BeginTransactionAsync();
        await using var secondTransaction = await secondConnection.BeginTransactionAsync();
        var firstPid = await GetBackendPidAsync(firstConnection, firstTransaction);
        var secondPid = await GetBackendPidAsync(secondConnection, secondTransaction);

        await RescheduleSessionsAsync(
            firstConnection,
            firstTransaction,
            firstSeed.SessionIds[0],
            secondSeed.SessionIds[0]);
        var secondRescheduleTask = RescheduleSessionsAsync(
            secondConnection,
            secondTransaction,
            secondSeed.SessionIds[1],
            firstSeed.SessionIds[1]);

        await WaitUntilBlockedByAsync(observerConnection, secondPid, firstPid);
        Assert.Null(await CommitAndCaptureSqlStateAsync(firstTransaction).WaitAsync(CommandTimeout));
        Assert.Equal(2, await secondRescheduleTask.WaitAsync(CommandTimeout));
        Assert.Null(await CommitAndCaptureSqlStateAsync(secondTransaction).WaitAsync(CommandTimeout));

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.Equal(0, await ExecuteScalarAsync<long>(
            verificationConnection,
            """
            SELECT COUNT(*)
            FROM portfolio_games
            WHERE id IN (@firstPortfolioGameId, @secondPortfolioGameId)
              AND is_public = true
            """,
            parameters:
            [
                new NpgsqlParameter("firstPortfolioGameId", firstSeed.PortfolioGameId),
                new NpgsqlParameter("secondPortfolioGameId", secondSeed.PortfolioGameId)
            ]));
    }

    [Fact]
    public async Task PublishingDraftCardWithAnyFutureLinkedSession_ShouldFailCommit()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: false, sessionCount: 2);

        await ExecuteNonQueryAsync(
            connection,
            "UPDATE sessions SET scheduled_at = now() + interval '1 day' WHERE id = @sessionId",
            parameters: new NpgsqlParameter("sessionId", seed.SessionIds[1]));
        await using var transaction = await connection.BeginTransactionAsync();
        await ExecuteNonQueryAsync(
            connection,
            """
            UPDATE portfolio_games
            SET is_public = true,
                published_at = COALESCE(published_at, now()),
                updated_at = now()
            WHERE id = @portfolioGameId
            """,
            transaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId));

        var exception = await Assert.ThrowsAsync<PostgresException>(
            () => transaction.CommitAsync().WaitAsync(CommandTimeout));

        Assert.Equal(PostgresErrorCodes.CheckViolation, exception.SqlState);
    }

    [Fact]
    public async Task ConcurrentPublishAndFutureReschedule_ShouldNotDeadlockOrCommitInvalidPublicCard()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var publishConnection = await database.OpenConnectionAsync();
        await using var rescheduleConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(publishConnection, isPublic: false);
        await using var publishTransaction = await publishConnection.BeginTransactionAsync();
        await using var rescheduleTransaction = await rescheduleConnection.BeginTransactionAsync();
        var publishPid = await GetBackendPidAsync(publishConnection, publishTransaction);
        var reschedulePid = await GetBackendPidAsync(rescheduleConnection, rescheduleTransaction);

        Assert.Equal(1, await PublishPortfolioGameAsync(
            publishConnection,
            publishTransaction,
            seed.PortfolioGameId));
        var rescheduleTask = ExecuteNonQueryAsync(
            rescheduleConnection,
            "UPDATE sessions SET scheduled_at = now() + interval '1 day' WHERE id = @sessionId",
            rescheduleTransaction,
            new NpgsqlParameter("sessionId", seed.SessionIds[0]));

        await WaitUntilBlockedByAsync(observerConnection, reschedulePid, publishPid);
        Assert.Null(await CommitAndCaptureSqlStateAsync(publishTransaction).WaitAsync(CommandTimeout));
        Assert.Equal(1, await rescheduleTask.WaitAsync(CommandTimeout));
        await rescheduleTransaction.CommitAsync().WaitAsync(CommandTimeout);

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.False(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.True(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT scheduled_at >= now() FROM sessions WHERE id = @sessionId",
            parameters: new NpgsqlParameter("sessionId", seed.SessionIds[0])));
    }

    [Fact]
    public async Task ConcurrentNewLinkPublishAndFutureReschedule_ShouldNotCommitInvalidPublicCard()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(seedConnection, isPublic: false);
        var rescheduledSessionId = Guid.NewGuid();
        await ExecuteNonQueryAsync(
            seedConnection,
            """
            INSERT INTO sessions (id, group_id, title, join_link, scheduled_at)
            VALUES (@sessionId, @groupId, 'Completed Session', 'https://example.test/session', now() - interval '1 day');
            """,
            parameters:
            [
                new NpgsqlParameter("sessionId", rescheduledSessionId),
                new NpgsqlParameter("groupId", seed.GroupId)
            ]);

        await using var rescheduleConnection = await database.OpenConnectionAsync();
        await using var publishConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var rescheduleTransaction = await rescheduleConnection.BeginTransactionAsync();
        await using var publishTransaction = await publishConnection.BeginTransactionAsync();
        var reschedulePid = await GetBackendPidAsync(rescheduleConnection, rescheduleTransaction);
        var publishPid = await GetBackendPidAsync(publishConnection, publishTransaction);

        Assert.Equal(1, await RescheduleSessionAsync(
            rescheduleConnection,
            rescheduleTransaction,
            rescheduledSessionId));
        var forceRescheduleTriggerTask = ExecuteNonQueryAsync(
            rescheduleConnection,
            "SET CONSTRAINTS trg_sessions_unpublish_public_portfolio_games_for_future_reschedule IMMEDIATE",
            rescheduleTransaction);
        await forceRescheduleTriggerTask.WaitAsync(CommandTimeout);

        var publishMutationTask = ExecuteNonQueryAsync(
            publishConnection,
            """
            INSERT INTO portfolio_game_sessions (portfolio_game_id, session_id)
            VALUES (@portfolioGameId, @sessionId);

            UPDATE portfolio_games
            SET is_public = true,
                published_at = COALESCE(published_at, now()),
                updated_at = now()
            WHERE id = @portfolioGameId;
            """,
            publishTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("sessionId", rescheduledSessionId));
        await WaitUntilBlockedByAsync(observerConnection, publishPid, reschedulePid);
        await rescheduleTransaction.CommitAsync().WaitAsync(CommandTimeout);
        await publishMutationTask.WaitAsync(CommandTimeout);

        Assert.Equal(
            PostgresErrorCodes.CheckViolation,
            await CommitAndCaptureSqlStateAsync(publishTransaction).WaitAsync(CommandTimeout));

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.False(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.True(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT scheduled_at >= now() FROM sessions WHERE id = @sessionId",
            parameters: new NpgsqlParameter("sessionId", rescheduledSessionId)));
        Assert.Equal(0, await ExecuteScalarAsync<long>(
            verificationConnection,
            """
            SELECT COUNT(*)
            FROM portfolio_game_sessions
            WHERE portfolio_game_id = @portfolioGameId
              AND session_id = @sessionId
            """,
            parameters:
            [
                new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
                new NpgsqlParameter("sessionId", rescheduledSessionId)
            ]));
    }

    [Fact]
    public async Task PortfolioSessionLinkInsert_ShouldAcquirePublicationLockBeforeRows()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(seedConnection, isPublic: false);
        var sessionId = Guid.NewGuid();
        await ExecuteNonQueryAsync(
            seedConnection,
            """
            INSERT INTO sessions (id, group_id, title, join_link, scheduled_at)
            VALUES (@sessionId, @groupId, 'Completed Session', 'https://example.test/session', now() - interval '1 day');
            """,
            parameters:
            [
                new NpgsqlParameter("sessionId", sessionId),
                new NpgsqlParameter("groupId", seed.GroupId)
            ]);

        await using var insertConnection = await database.OpenConnectionAsync();
        await using var gateConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var insertTransaction = await insertConnection.BeginTransactionAsync();
        await using var gateTransaction = await gateConnection.BeginTransactionAsync();
        var insertPid = await GetBackendPidAsync(insertConnection, insertTransaction);
        var gatePid = await GetBackendPidAsync(gateConnection, gateTransaction);
        await AcquirePortfolioValidationLockAsync(gateConnection, gateTransaction);

        var insertTask = ExecuteNonQueryAsync(
            insertConnection,
            """
            INSERT INTO portfolio_game_sessions (portfolio_game_id, session_id)
            VALUES (@portfolioGameId, @sessionId);
            """,
            insertTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("sessionId", sessionId));

        await WaitUntilBlockedByAsync(observerConnection, insertPid, gatePid);
        await gateTransaction.CommitAsync().WaitAsync(CommandTimeout);

        Assert.Equal(1, await insertTask.WaitAsync(CommandTimeout));
        await insertTransaction.RollbackAsync().WaitAsync(CommandTimeout);
    }

    [Fact]
    public async Task FutureReschedule_ShouldAcquirePublicationLockBeforeSessionRows()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(seedConnection, isPublic: true);
        await using var rescheduleConnection = await database.OpenConnectionAsync();
        await using var gateConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var rescheduleTransaction = await rescheduleConnection.BeginTransactionAsync();
        await using var gateTransaction = await gateConnection.BeginTransactionAsync();
        var reschedulePid = await GetBackendPidAsync(rescheduleConnection, rescheduleTransaction);
        var gatePid = await GetBackendPidAsync(gateConnection, gateTransaction);
        await AcquirePortfolioValidationLockAsync(gateConnection, gateTransaction);

        var rescheduleTask = RescheduleSessionAsync(
            rescheduleConnection,
            rescheduleTransaction,
            seed.SessionIds[0]);

        await WaitUntilBlockedByAsync(observerConnection, reschedulePid, gatePid);
        await gateTransaction.CommitAsync().WaitAsync(CommandTimeout);

        Assert.Equal(1, await rescheduleTask.WaitAsync(CommandTimeout));
        await rescheduleTransaction.RollbackAsync().WaitAsync(CommandTimeout);
    }

    [Fact]
    public async Task RepeatableReadStaleSnapshotFutureReschedule_ShouldBeRejectedWithoutInvalidPublicCard()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(seedConnection, isPublic: false);
        var rescheduledSessionId = Guid.NewGuid();
        await ExecuteNonQueryAsync(
            seedConnection,
            """
            INSERT INTO sessions (id, group_id, title, join_link, scheduled_at)
            VALUES (@sessionId, @groupId, 'Completed Session', 'https://example.test/session', now() - interval '1 day');
            """,
            parameters:
            [
                new NpgsqlParameter("sessionId", rescheduledSessionId),
                new NpgsqlParameter("groupId", seed.GroupId)
            ]);
        await using var rescheduleConnection = await database.OpenConnectionAsync();
        await using var publishConnection = await database.OpenConnectionAsync();
        await using var rescheduleTransaction = await rescheduleConnection.BeginTransactionAsync(IsolationLevel.RepeatableRead);

        Assert.Equal(0, await ExecuteScalarAsync<long>(
            rescheduleConnection,
            """
            SELECT COUNT(*)
            FROM portfolio_game_sessions
            WHERE portfolio_game_id = @portfolioGameId
              AND session_id = @sessionId
            """,
            rescheduleTransaction,
            new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
            new NpgsqlParameter("sessionId", rescheduledSessionId)));

        await using (var publishTransaction = await publishConnection.BeginTransactionAsync())
        {
            await ExecuteNonQueryAsync(
                publishConnection,
                """
                INSERT INTO portfolio_game_sessions (portfolio_game_id, session_id)
                VALUES (@portfolioGameId, @sessionId);

                UPDATE portfolio_games
                SET is_public = true,
                    published_at = COALESCE(published_at, now()),
                    updated_at = now()
                WHERE id = @portfolioGameId
                """,
                publishTransaction,
                new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId),
                new NpgsqlParameter("sessionId", rescheduledSessionId));
            await publishTransaction.CommitAsync().WaitAsync(CommandTimeout);
        }

        Assert.Equal(1, await RescheduleSessionAsync(
            rescheduleConnection,
            rescheduleTransaction,
            rescheduledSessionId));

        var exception = await Assert.ThrowsAsync<PostgresException>(
            () => rescheduleTransaction.CommitAsync().WaitAsync(CommandTimeout));

        Assert.Equal(PostgresErrorCodes.FeatureNotSupported, exception.SqlState);

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.True(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.False(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT scheduled_at >= now() FROM sessions WHERE id = @sessionId",
            parameters: new NpgsqlParameter("sessionId", rescheduledSessionId)));
    }

    [Theory]
    [InlineData(true)]
    [InlineData(false)]
    public async Task ConcurrentSessionDeleteAndFutureReschedule_ShouldSerializeMutationGateBeforeRowsWithoutDeadlock(
        bool deleteMutatesFirst)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var seedConnection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(seedConnection, isPublic: true);
        await using var deleteConnection = await database.OpenConnectionAsync();
        await using var rescheduleConnection = await database.OpenConnectionAsync();
        await using var observerConnection = await database.OpenConnectionAsync();
        await using var deleteTransaction = await deleteConnection.BeginTransactionAsync();
        await using var rescheduleTransaction = await rescheduleConnection.BeginTransactionAsync();
        var deletePid = await GetBackendPidAsync(deleteConnection, deleteTransaction);
        var reschedulePid = await GetBackendPidAsync(rescheduleConnection, rescheduleTransaction);

        if (deleteMutatesFirst)
        {
            await AcquirePortfolioValidationLockAsync(deleteConnection, deleteTransaction);
            await LockSessionAsync(deleteConnection, deleteTransaction, seed.SessionIds[0]);
            var rescheduleTask = RescheduleSessionAsync(
                rescheduleConnection,
                rescheduleTransaction,
                seed.SessionIds[0]);

            await WaitUntilBlockedByAsync(observerConnection, reschedulePid, deletePid);
            await UnpublishAndDeleteSessionAsync(
                deleteConnection,
                deleteTransaction,
                seed.PortfolioGameId,
                seed.SessionIds[0]);
            await deleteTransaction.CommitAsync().WaitAsync(CommandTimeout);

            Assert.Equal(0, await rescheduleTask.WaitAsync(CommandTimeout));
            await rescheduleTransaction.CommitAsync().WaitAsync(CommandTimeout);
        }
        else
        {
            Assert.Equal(1, await RescheduleSessionAsync(
                rescheduleConnection,
                rescheduleTransaction,
                seed.SessionIds[0]));
            var deleteTask = LockUnpublishDeleteAndCommitSessionAsync(
                deleteConnection,
                deleteTransaction,
                seed.PortfolioGameId,
                seed.SessionIds[0]);

            await WaitUntilBlockedByAsync(observerConnection, deletePid, reschedulePid);
            await rescheduleTransaction.CommitAsync().WaitAsync(CommandTimeout);
            await deleteTask.WaitAsync(CommandTimeout);
        }

        await using var verificationConnection = await database.OpenConnectionAsync();
        Assert.False(await ExecuteScalarAsync<bool>(
            verificationConnection,
            "SELECT is_public FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", seed.PortfolioGameId)));
        Assert.Equal(0, await ExecuteScalarAsync<long>(
            verificationConnection,
            "SELECT COUNT(*) FROM sessions WHERE id = @sessionId",
            parameters: new NpgsqlParameter("sessionId", seed.SessionIds[0])));
    }

    [Theory]
    [InlineData("portfolio_game_sessions")]
    [InlineData("portfolio_game_masters")]
    public async Task MovingLastRequiredLinkAway_ShouldFailCommitForPublishedCard(string linkTable)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var source = await SeedCardAsync(connection, isPublic: true);
        var destination = await SeedCardAsync(connection, isPublic: false);
        await using var transaction = await connection.BeginTransactionAsync();

        await ExecuteNonQueryAsync(
            connection,
            $"UPDATE {linkTable} SET portfolio_game_id = @destinationId WHERE portfolio_game_id = @sourceId",
            transaction,
            new NpgsqlParameter("destinationId", destination.PortfolioGameId),
            new NpgsqlParameter("sourceId", source.PortfolioGameId));

        var exception = await Assert.ThrowsAsync<PostgresException>(
            () => transaction.CommitAsync().WaitAsync(CommandTimeout));

        Assert.Equal(PostgresErrorCodes.CheckViolation, exception.SqlState);
    }

    [Theory]
    [InlineData("sessions")]
    [InlineData("players")]
    public async Task RequiredParentCascadeDelete_ShouldFailCommitForPublishedCard(string parentTable)
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var seed = await SeedCardAsync(connection, isPublic: true);
        await using var transaction = await connection.BeginTransactionAsync();

        await ExecuteNonQueryAsync(
            connection,
            $"DELETE FROM {parentTable} WHERE id = @parentId",
            transaction,
            new NpgsqlParameter(
                "parentId",
                parentTable == "sessions" ? seed.SessionIds[0] : seed.MasterIds[0]));

        var exception = await Assert.ThrowsAsync<PostgresException>(
            () => transaction.CommitAsync().WaitAsync(CommandTimeout));

        Assert.Equal(PostgresErrorCodes.CheckViolation, exception.SqlState);
    }

    [Fact]
    public async Task ParentCardAndGroupCascadeDeletes_ShouldCommit()
    {
        var database = await fixture.CreateMigratedDatabaseAsync();
        await using var connection = await database.OpenConnectionAsync();
        var cardDeleteSeed = await SeedCardAsync(connection, isPublic: true);

        await ExecuteNonQueryAsync(
            connection,
            "DELETE FROM portfolio_games WHERE id = @portfolioGameId",
            parameters: new NpgsqlParameter("portfolioGameId", cardDeleteSeed.PortfolioGameId));

        var groupDeleteSeed = await SeedCardAsync(connection, isPublic: true);
        await ExecuteNonQueryAsync(
            connection,
            "DELETE FROM game_groups WHERE id = @groupId",
            parameters: new NpgsqlParameter("groupId", groupDeleteSeed.GroupId));

        Assert.Equal(0, await ExecuteScalarAsync<long>(
            connection,
            "SELECT COUNT(*) FROM portfolio_games WHERE id IN (@cardDeleteId, @groupDeleteId)",
            parameters:
            [
                new NpgsqlParameter("cardDeleteId", cardDeleteSeed.PortfolioGameId),
                new NpgsqlParameter("groupDeleteId", groupDeleteSeed.PortfolioGameId)
            ]));
    }

    private static async Task<PortfolioSeed> SeedCardAsync(
        NpgsqlConnection connection,
        bool isPublic,
        int sessionCount = 1,
        int masterCount = 1)
    {
        var groupId = Guid.NewGuid();
        var portfolioGameId = Guid.NewGuid();
        var sessionIds = Enumerable.Range(0, sessionCount).Select(_ => Guid.NewGuid()).ToArray();
        var masterIds = Enumerable.Range(0, masterCount).Select(_ => Guid.NewGuid()).ToArray();
        var publishedAtValue = DateTime.UtcNow.AddDays(-1);
        var publishedAt = new DateTime(publishedAtValue.Ticks / 10 * 10, DateTimeKind.Utc);
        await using var transaction = await connection.BeginTransactionAsync();

        foreach (var masterId in masterIds)
        {
            var legacyId = Interlocked.Increment(ref nextLegacyId);
            await ExecuteNonQueryAsync(
                connection,
                """
                INSERT INTO players (id, telegram_id, display_name, platform, external_user_id)
                VALUES (@playerId, @legacyId, 'Portfolio GM', 'Telegram', @legacyIdText);
                """,
                transaction,
                new NpgsqlParameter("playerId", masterId),
                new NpgsqlParameter("legacyId", legacyId),
                new NpgsqlParameter("legacyIdText", legacyId.ToString()));
        }

        var groupLegacyId = Interlocked.Increment(ref nextLegacyId);
        await ExecuteNonQueryAsync(
            connection,
            """
            INSERT INTO game_groups (id, telegram_chat_id, name, gm_telegram_id, platform, external_group_id)
            VALUES (@groupId, @legacyId, 'Portfolio Club', @legacyId, 'Telegram', @legacyIdText);

            INSERT INTO portfolio_games (
                id,
                group_id,
                public_slug,
                title,
                description,
                cover_storage_key,
                is_public,
                published_at)
            VALUES (
                @portfolioGameId,
                @groupId,
                @publicSlug,
                'Completed Adventure',
                'A completed adventure.',
                'covers/example.webp',
                @isPublic,
                CASE WHEN @isPublic THEN @publishedAt ELSE NULL END);
            """,
            transaction,
            new NpgsqlParameter("legacyId", groupLegacyId),
            new NpgsqlParameter("legacyIdText", groupLegacyId.ToString()),
            new NpgsqlParameter("groupId", groupId),
            new NpgsqlParameter("portfolioGameId", portfolioGameId),
            new NpgsqlParameter("publicSlug", $"portfolio-{portfolioGameId:N}"),
            new NpgsqlParameter("isPublic", isPublic),
            new NpgsqlParameter("publishedAt", publishedAt));

        foreach (var sessionId in sessionIds)
        {
            await ExecuteNonQueryAsync(
                connection,
                """
                INSERT INTO sessions (id, group_id, title, join_link, scheduled_at)
                VALUES (@sessionId, @groupId, 'Completed Session', 'https://example.test/session', now() - interval '1 day');

                INSERT INTO portfolio_game_sessions (portfolio_game_id, session_id)
                VALUES (@portfolioGameId, @sessionId);
                """,
                transaction,
                new NpgsqlParameter("sessionId", sessionId),
                new NpgsqlParameter("groupId", groupId),
                new NpgsqlParameter("portfolioGameId", portfolioGameId));
        }

        foreach (var masterId in masterIds)
        {
            await ExecuteNonQueryAsync(
                connection,
                """
                INSERT INTO portfolio_game_masters (portfolio_game_id, player_id)
                VALUES (@portfolioGameId, @playerId);
                """,
                transaction,
                new NpgsqlParameter("portfolioGameId", portfolioGameId),
                new NpgsqlParameter("playerId", masterId));
        }

        await transaction.CommitAsync().WaitAsync(CommandTimeout);
        return new PortfolioSeed(portfolioGameId, groupId, sessionIds, masterIds, publishedAt);
    }

    private static async Task<string?> CommitAndCaptureSqlStateAsync(NpgsqlTransaction transaction)
    {
        try
        {
            await transaction.CommitAsync().WaitAsync(CommandTimeout);
            return null;
        }
        catch (PostgresException exception)
        {
            return exception.SqlState;
        }
    }

    private static Task<int> AcquirePortfolioValidationLockAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction)
    {
        return ExecuteNonQueryAsync(
            connection,
            "SELECT pg_advisory_xact_lock(20260530, 108)",
            transaction);
    }

    private static Task<int> GetBackendPidAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction)
    {
        return ExecuteScalarAsync<int>(connection, "SELECT pg_backend_pid()", transaction);
    }

    private static Task<int> LockSessionAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        Guid sessionId)
    {
        return ExecuteNonQueryAsync(
            connection,
            "SELECT 1 FROM sessions s WHERE s.id = @sessionId FOR UPDATE OF s",
            transaction,
            new NpgsqlParameter("sessionId", sessionId));
    }

    private static Task<int> PublishPortfolioGameAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        Guid portfolioGameId)
    {
        return ExecuteNonQueryAsync(
            connection,
            """
            UPDATE portfolio_games
            SET is_public = true,
                published_at = COALESCE(published_at, now()),
                updated_at = now()
            WHERE id = @portfolioGameId
            """,
            transaction,
            new NpgsqlParameter("portfolioGameId", portfolioGameId));
    }

    private static Task<int> DeletePortfolioGameLinksAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        string linkTable,
        Guid portfolioGameId)
    {
        return ExecuteNonQueryAsync(
            connection,
            $"DELETE FROM {linkTable} WHERE portfolio_game_id = @portfolioGameId",
            transaction,
            new NpgsqlParameter("portfolioGameId", portfolioGameId));
    }

    private static Task<int> RescheduleSessionAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        Guid sessionId)
    {
        return ExecuteNonQueryAsync(
            connection,
            "UPDATE sessions SET scheduled_at = now() + interval '1 day' WHERE id = @sessionId",
            transaction,
            new NpgsqlParameter("sessionId", sessionId));
    }

    private static Task<int> RescheduleSessionsAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        Guid firstSessionId,
        Guid secondSessionId)
    {
        return ExecuteNonQueryAsync(
            connection,
            """
            UPDATE sessions
            SET scheduled_at = now() + interval '1 day'
            WHERE id = @firstSessionId;

            UPDATE sessions
            SET scheduled_at = now() + interval '1 day'
            WHERE id = @secondSessionId;
            """,
            transaction,
            new NpgsqlParameter("firstSessionId", firstSessionId),
            new NpgsqlParameter("secondSessionId", secondSessionId));
    }

    private static async Task LockUnpublishDeleteAndCommitSessionAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        Guid portfolioGameId,
        Guid sessionId)
    {
        await AcquirePortfolioValidationLockAsync(connection, transaction);
        await LockSessionAsync(connection, transaction, sessionId);
        await UnpublishAndDeleteSessionAsync(connection, transaction, portfolioGameId, sessionId);
        await transaction.CommitAsync().WaitAsync(CommandTimeout);
    }

    private static async Task UnpublishAndDeleteSessionAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        Guid portfolioGameId,
        Guid sessionId)
    {
        await ExecuteNonQueryAsync(
            connection,
            """
            UPDATE portfolio_games
            SET is_public = false,
                updated_at = now()
            WHERE id = @portfolioGameId
            """,
            transaction,
            new NpgsqlParameter("portfolioGameId", portfolioGameId));
        await ExecuteNonQueryAsync(
            connection,
            "DELETE FROM sessions WHERE id = @sessionId",
            transaction,
            new NpgsqlParameter("sessionId", sessionId));
    }

    private static async Task WaitUntilBlockedByAsync(
        NpgsqlConnection observerConnection,
        int blockedPid,
        int blockingPid)
    {
        using var timeout = new CancellationTokenSource(CommandTimeout);
        while (!timeout.IsCancellationRequested)
        {
            if (await ExecuteScalarAsync<bool>(
                    observerConnection,
                    "SELECT @blockingPid = ANY (pg_blocking_pids(@blockedPid))",
                    parameters:
                    [
                        new NpgsqlParameter("blockedPid", blockedPid),
                        new NpgsqlParameter("blockingPid", blockingPid)
                    ]))
            {
                return;
            }

            await Task.Yield();
        }

        throw new TimeoutException(
            $"PostgreSQL backend {blockedPid} was not blocked by backend {blockingPid} within {CommandTimeout}.");
    }

    private static async Task<int> ExecuteNonQueryAsync(
        NpgsqlConnection connection,
        string sql,
        NpgsqlTransaction? transaction = null,
        params NpgsqlParameter[] parameters)
    {
        await using var command = new NpgsqlCommand(sql, connection, transaction);
        command.Parameters.AddRange(parameters);
        return await command.ExecuteNonQueryAsync().WaitAsync(CommandTimeout);
    }

    private static async Task<T> ExecuteScalarAsync<T>(
        NpgsqlConnection connection,
        string sql,
        NpgsqlTransaction? transaction = null,
        params NpgsqlParameter[] parameters)
    {
        await using var command = new NpgsqlCommand(sql, connection, transaction);
        command.Parameters.AddRange(parameters);
        return (T)(await command.ExecuteScalarAsync().WaitAsync(CommandTimeout))!;
    }

    private sealed record PortfolioSeed(
        Guid PortfolioGameId,
        Guid GroupId,
        Guid[] SessionIds,
        Guid[] MasterIds,
        DateTime PublishedAt);
}