GmRelayBot/tests/GmRelay.Bot.Tests/Web/PortfolioMigrationPostgresFixture.cs

using Npgsql;
using Testcontainers.PostgreSql;

namespace GmRelay.Bot.Tests.Web;

[CollectionDefinition(Name)]
public sealed class PortfolioMigrationPostgresCollection : ICollectionFixture<PortfolioMigrationPostgresFixture>
{
    public const string Name = "Portfolio migration PostgreSQL";
}

public sealed class PortfolioMigrationPostgresFixture : IAsyncLifetime
{
    private static readonly TimeSpan ContainerTimeout = TimeSpan.FromMinutes(2);
    private readonly PostgreSqlContainer container = new PostgreSqlBuilder("postgres:17-alpine").Build();

    public Task InitializeAsync()
    {
        return container.StartAsync().WaitAsync(ContainerTimeout);
    }

    public Task DisposeAsync()
    {
        return container.DisposeAsync().AsTask().WaitAsync(ContainerTimeout);
    }

    public async Task<MigratedPortfolioDatabase> CreateMigratedDatabaseAsync()
    {
        var databaseName = $"portfolio_{Guid.NewGuid():N}";

        await using (var adminConnection = new NpgsqlConnection(container.GetConnectionString()))
        {
            await adminConnection.OpenAsync().WaitAsync(ContainerTimeout);
            await using var createDatabase = new NpgsqlCommand($"CREATE DATABASE \"{databaseName}\"", adminConnection);
            await createDatabase.ExecuteNonQueryAsync().WaitAsync(ContainerTimeout);
        }

        var connectionString = new NpgsqlConnectionStringBuilder(container.GetConnectionString())
        {
            Database = databaseName,
            Timeout = 10,
            CommandTimeout = 10
        }.ConnectionString;

        var migrations = GetMigrationPaths();
        await using var connection = new NpgsqlConnection(connectionString);
        await connection.OpenAsync().WaitAsync(ContainerTimeout);

        foreach (var migration in migrations)
        {
            await using var command = new NpgsqlCommand(await File.ReadAllTextAsync(migration), connection)
            {
                CommandTimeout = 30
            };
            await command.ExecuteNonQueryAsync().WaitAsync(ContainerTimeout);
        }

        return new MigratedPortfolioDatabase(connectionString, migrations.Count);
    }

    private static IReadOnlyList<string> GetMigrationPaths()
    {
        var directory = new DirectoryInfo(AppContext.BaseDirectory);
        while (directory is not null)
        {
            var migrationsDirectory = Path.Combine(directory.FullName, "src", "GmRelay.Bot", "Migrations");
            if (Directory.Exists(migrationsDirectory))
            {
                return Directory.GetFiles(migrationsDirectory, "V*.sql")
                    .Where(path => string.CompareOrdinal(Path.GetFileName(path), "V030__") < 0)
                    .OrderBy(path => Path.GetFileName(path), StringComparer.Ordinal)
                    .ToArray();
            }

            directory = directory.Parent;
        }

        throw new DirectoryNotFoundException("Could not locate the bot migrations directory.");
    }
}

public sealed record MigratedPortfolioDatabase(string ConnectionString, int AppliedMigrationCount)
{
    public async Task<NpgsqlConnection> OpenConnectionAsync()
    {
        var connection = new NpgsqlConnection(ConnectionString);
        await connection.OpenAsync().WaitAsync(TimeSpan.FromSeconds(10));
        return connection;
    }
}