Real Databases with Testcontainers

using System.Net;
using System.Net.Http.Json;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.EntityFrameworkCore;
using Npgsql;
using Testcontainers.PostgreSql;
using Xunit;

// One PostgreSQL container and one test host for every class in the "Postgres" collection.
public sealed class PostgresApiFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _postgres = new PostgreSqlBuilder("postgres:17-alpine")
        .WithDatabase("orders")
        .WithUsername("orders_app")
        .WithPassword("test-only-password")
        .Build();

    public WebApplicationFactory<Program> Factory { get; private set; } = null!;

    public async ValueTask InitializeAsync()
    {
        await _postgres.StartAsync(TestContext.Current.CancellationToken);

        Factory = new WebApplicationFactory<Program>().WithWebHostBuilder(web =>
        {
            web.UseEnvironment("Testing");
            web.UseSetting("ConnectionStrings:Orders", _postgres.GetConnectionString());
        });

        using var scope = Factory.Services.CreateScope();
        await scope.ServiceProvider.GetRequiredService<OrdersDb>().Database.MigrateAsync(TestContext.Current.CancellationToken);
    }

    public async Task ResetAsync()
    {
        await using var connection = new NpgsqlConnection(_postgres.GetConnectionString());
        await connection.OpenAsync();
        await using var truncate = new NpgsqlCommand("""
            DO $$
            DECLARE tables text;
            BEGIN
              SELECT string_agg(format('%I.%I', schemaname, tablename), ', ') INTO tables
              FROM pg_tables
              WHERE schemaname = 'public' AND tablename <> '__EFMigrationsHistory';
              IF tables IS NOT NULL THEN
                EXECUTE 'TRUNCATE ' || tables || ' RESTART IDENTITY CASCADE';
              END IF;
            END $$;
            """, connection);
        await truncate.ExecuteNonQueryAsync();
    }

    public async ValueTask DisposeAsync()
    {
        await Factory.DisposeAsync();
        await _postgres.DisposeAsync();
    }
}

[CollectionDefinition("Postgres")]
public sealed class PostgresCollection : ICollectionFixture<PostgresApiFixture>;

[Collection("Postgres")]
public sealed class OrderPersistenceTests(PostgresApiFixture fixture) : IAsyncLifetime
{
    public async ValueTask InitializeAsync() => await fixture.ResetAsync();
    public ValueTask DisposeAsync() => ValueTask.CompletedTask;

    [Fact]
    public async Task Duplicate_order_number_returns_409_not_500()
    {
        using var client = fixture.Factory.CreateClient();
        var body = new { orderNumber = "SO-2026-00042", customerId = "CUST-17", total = 129.90m };

        using var first = await client.PostAsJsonAsync("/orders", body, TestContext.Current.CancellationToken);
        using var second = await client.PostAsJsonAsync("/orders", body, TestContext.Current.CancellationToken);

        Assert.Equal(HttpStatusCode.Created, first.StatusCode);
        Assert.Equal(HttpStatusCode.Conflict, second.StatusCode);   // the unique index, enforced by PostgreSQL
    }

    [Fact]
    public async Task Customer_search_ignores_case_like_production()
    {
        using var client = fixture.Factory.CreateClient();
        await client.PostAsJsonAsync("/orders",
            new { orderNumber = "SO-2026-00043", customerId = "CUST-17", total = 34.50m }, TestContext.Current.CancellationToken);

        var found = await client.GetFromJsonAsync<OrderSummary[]>("/orders?customerId=cust-17", TestContext.Current.CancellationToken);

        Assert.Single(found!);
    }

    private sealed record OrderSummary(string OrderNumber, decimal Total);
}

// Stand-ins for the API project's types.
public sealed class OrdersDb(DbContextOptions<OrdersDb> options) : DbContext(options);

// Stand-in so this file compiles on its own. Delete it in a real test project: in .NET 10 the API's
// generated Program is already public, and a Program declared here would shadow it (CS0436).
public partial class Program;