In-memory EF Core (UseInMemoryDatabase):
  - Does not enforce constraints (UNIQUE, FK, NOT NULL)
  - Does not test SQL translation
  - Does not test transactions or isolation levels
  - Gives false confidence

Real PostgreSQL via Testcontainers:
  - Runs actual SQL against actual PostgreSQL
  - Catches constraint violations, migration issues, N+1 queries
  - Same database engine as production
  - Starts in ~2s via Docker

<PackageReference Include="Testcontainers"                    Version="3.*" />
<PackageReference Include="Testcontainers.PostgreSql"         Version="3.*" />
<PackageReference Include="Testcontainers.Redis"              Version="3.*" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.Testing"  Version="9.*" />
<PackageReference Include="xunit"                             Version="2.*" />
<PackageReference Include="xunit.runner.visualstudio"         Version="2.*" />
<PackageReference Include="FluentAssertions"                  Version="6.*" />
<PackageReference Include="NSubstitute"                       Version="5.*" />

// Starts ONE PostgreSQL container for the entire test run — shared across all test classes
public sealed class PostgreSqlFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _container = new PostgreSqlBuilder()
        .WithImage("postgres:16-alpine")
        .WithDatabase("testdb")
        .WithUsername("test")
        .WithPassword("test")
        .Build();

    public string ConnectionString { get; private set; } = "";

    public async Task InitializeAsync()
    {
        await _container.StartAsync();
        ConnectionString = _container.GetConnectionString();

        // Run migrations once against the container
        using var scope = CreateServices().CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        await db.Database.MigrateAsync();
    }

    public IServiceProvider CreateServices(Action<IServiceCollection>? configure = null)
    {
        var services = new ServiceCollection();
        services.AddDbContext<AppDbContext>(opts =>
            opts.UseNpgsql(ConnectionString));
        configure?.Invoke(services);
        return services.BuildServiceProvider();
    }

    public async Task DisposeAsync() => await _container.StopAsync();
}

// Register as xUnit collection fixture — shared across all classes in the collection
[CollectionDefinition("Database")]
public class DatabaseCollection : ICollectionFixture<PostgreSqlFixture> { }

// Strategy 1: Transaction rollback — fastest, but doesn't test commit behavior
[Collection("Database")]
public class OrderRepositoryTests(PostgreSqlFixture db) : IAsyncLifetime
{
    private AppDbContext  _context = null!;
    private IDbTransaction _tx    = null!;

    public async Task InitializeAsync()
    {
        var conn = new NpgsqlConnection(db.ConnectionString);
        await conn.OpenAsync();
        _tx = await conn.BeginTransactionAsync();

        var opts = new DbContextOptionsBuilder<AppDbContext>()
            .UseNpgsql(conn)
            .Options;
        _context = new AppDbContext(opts);
    }

    public async Task DisposeAsync()
    {
        await _tx.RollbackAsync();   // undo all test data — next test starts clean
        await _context.DisposeAsync();
    }

    [Fact]
    public async Task GetById_ExistingOrder_ReturnsOrder()
    {
        // Seed
        var order = new Order { CustomerId = 1, Status = "Pending", Total = 100m };
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        // Act
        var repo   = new OrderRepository(_context);
        var result = await repo.GetByIdAsync(order.Id, CancellationToken.None);

        // Assert
        result.Should().NotBeNull();
        result!.Id.Should().Be(order.Id);
    }
}

// Strategy 2: Respawn — truncate all tables between tests (works for HTTP-level tests)
// dotnet add package Respawn

public class DatabaseResetter(string connectionString) : IAsyncLifetime
{
    private Respawner _respawner = null!;

    public async Task InitializeAsync()
    {
        await using var conn = new NpgsqlConnection(connectionString);
        await conn.OpenAsync();
        _respawner = await Respawner.CreateAsync(conn, new RespawnerOptions
        {
            DbAdapter = DbAdapter.Postgres,
            SchemasToInclude = ["public"],
        });
    }

    public async Task ResetAsync()
    {
        await using var conn = new NpgsqlConnection(connectionString);
        await conn.OpenAsync();
        await _respawner.ResetAsync(conn);
    }

    public Task DisposeAsync() => Task.CompletedTask;
}

// Custom WebApplicationFactory — replace prod services with test services
public class TestWebAppFactory(PostgreSqlFixture db)
    : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly DatabaseResetter _resetter = new(db.ConnectionString);

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureServices(services =>
        {
            // Replace the production DbContext with the test container's connection
            services.RemoveAll<DbContextOptions<AppDbContext>>();
            services.AddDbContext<AppDbContext>(opts =>
                opts.UseNpgsql(db.ConnectionString));

            // Replace external dependencies with test doubles
            services.RemoveAll<IPaymentGateway>();
            services.AddSingleton<IPaymentGateway>(Substitute.For<IPaymentGateway>());

            services.RemoveAll<IEmailService>();
            services.AddSingleton<IEmailService>(Substitute.For<IEmailService>());
        });

        builder.UseEnvironment("Testing");
    }

    public async Task InitializeAsync()
    {
        await _resetter.InitializeAsync();
    }

    public new async Task DisposeAsync()
    {
        await _resetter.DisposeAsync();
        await base.DisposeAsync();
    }

    public async Task ResetDatabaseAsync() => await _resetter.ResetAsync();
}

[Collection("Database")]
public class OrderApiTests(PostgreSqlFixture db) : IAsyncLifetime
{
    private TestWebAppFactory _factory = null!;
    private HttpClient        _client  = null!;

    public async Task InitializeAsync()
    {
        _factory = new TestWebAppFactory(db);
        _client  = _factory.CreateClient();
        await _factory.InitializeAsync();
    }

    public async Task DisposeAsync()
    {
        await _factory.ResetDatabaseAsync();   // clean state after each test
        _client.Dispose();
        await _factory.DisposeAsync();
    }

    [Fact]
    public async Task PlaceOrder_ValidRequest_Returns201WithOrderId()
    {
        // Arrange
        var request = new
        {
            customerId = 1,
            items = new[] { new { productId = 1, quantity = 2, unitPrice = 9.99 } },
        };

        // Act
        var response = await _client.PostAsJsonAsync("/api/orders", request);

        // Assert
        response.StatusCode.Should().Be(HttpStatusCode.Created);

        var body = await response.Content.ReadFromJsonAsync<PlaceOrderResponse>();
        body!.OrderId.Should().BeGreaterThan(0);

        // Verify database state directly
        using var scope = _factory.Services.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        var order = await db.Orders.FindAsync(body.OrderId);
        order!.Status.Should().Be("Pending");
    }

    [Fact]
    public async Task PlaceOrder_EmptyItems_Returns422WithValidationErrors()
    {
        var request = new { customerId = 1, items = Array.Empty<object>() };

        var response = await _client.PostAsJsonAsync("/api/orders", request);

        response.StatusCode.Should().Be(HttpStatusCode.UnprocessableEntity);
        var problem = await response.Content.ReadFromJsonAsync<ValidationProblemDetails>();
        problem!.Errors.Should().ContainKey("items");
    }
}

// Builder pattern for test data — readable, maintainable
public class OrderBuilder
{
    private int     _customerId = 1;
    private string  _status     = "Pending";
    private decimal _total      = 100m;

    public OrderBuilder WithCustomer(int id)     { _customerId = id; return this; }
    public OrderBuilder WithStatus(string s)     { _status = s; return this; }
    public OrderBuilder WithTotal(decimal t)     { _total = t; return this; }

    public Order Build() => new()
    {
        CustomerId = _customerId,
        Status     = _status,
        Total      = _total,
    };
}

// In test:
var order = new OrderBuilder()
    .WithCustomer(42)
    .WithStatus("Paid")
    .WithTotal(250m)
    .Build();
context.Orders.Add(order);
await context.SaveChangesAsync();

// xUnit runs test classes in parallel by default
// Tests in the same collection run sequentially (share the fixture)

// For database tests: use transaction rollback per test → parallel-safe
// Each test has its own transaction — no interference

// xunit.runner.json — tune parallelism
{
  "parallelizeAssembly": true,
  "parallelizeTestCollections": true,
  "maxParallelThreads": 4
}