Testing With EF Core — In-Memory vs Real Database

Strategy 1: In-memory database (EF Core's UseInMemoryDatabase)
  Use when: testing non-DB logic that happens to touch EF models
  Avoid when: testing actual DB behavior (queries, constraints, migrations)
  Speed: very fast (no container startup)
  Accuracy: low — many SQL Server behaviors differ

Strategy 2: SQLite in-memory
  Use when: testing relational behavior without SQL Server syntax
  Avoid when: you use SQL Server-specific features
  Speed: fast
  Accuracy: medium — relational but not SQL Server-compatible

Strategy 3: Testcontainers SQL Server
  Use when: testing queries, relationships, migrations, constraints
  Avoid when: you need pure unit tests (no need for real DB)
  Speed: slower (15s startup)
  Accuracy: exact — same engine as production

// Helper to create a fresh DbContext for each test
public static AppDbContext CreateInMemoryContext(string? dbName = null)
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseInMemoryDatabase(dbName ?? Guid.NewGuid().ToString())
        .Options;
    return new AppDbContext(options, new NoOpPublisher());
}

public static async Task<AppDbContext> CreateSqlServerContextAsync(
    string connectionString)
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(connectionString)
        .Options;
    var ctx = new AppDbContext(options, new NoOpPublisher());
    await ctx.Database.MigrateAsync();
    return ctx;
}

// Test that EF Core configurations are correct
public class PatientEntityConfigurationTests : IAsyncLifetime
{
    private readonly MsSqlContainer _sql = new MsSqlBuilder().Build();
    private AppDbContext _context = null!;

    public async Task InitializeAsync()
    {
        await _sql.StartAsync();
        _context = await CreateSqlServerContextAsync(_sql.GetConnectionString());
    }

    [Fact]
    public async Task Patient_MRN_should_have_unique_constraint()
    {
        // Arrange
        var p1 = Patient.Create("John Smith", new DateOnly(1985, 3, 15), "MRN-DUP").Value;
        var p2 = Patient.Create("Jane Doe",   new DateOnly(1990, 7, 22), "MRN-DUP").Value;

        _context.Patients.Add(p1);
        await _context.SaveChangesAsync();

        _context.Patients.Add(p2);

        // Act & Assert — unique constraint violation
        await _context.Invoking(ctx => ctx.SaveChangesAsync())
            .Should().ThrowAsync<DbUpdateException>()
            .WithMessage("*unique*");
    }

    [Fact]
    public async Task Patient_MRN_column_should_have_max_length_20()
    {
        // Check column metadata via model inspection
        var entityType = _context.Model.FindEntityType(typeof(Patient))!;
        var mrnProp    = entityType.FindProperty(nameof(Patient.MRN))!;

        mrnProp.GetMaxLength().Should().Be(20);
        mrnProp.IsNullable().Should().BeFalse();
    }

    public async Task DisposeAsync()
    {
        await _context.DisposeAsync();
        await _sql.DisposeAsync();
    }
}

// Verify soft-delete filter is applied
[Fact]
public async Task Deleted_patients_should_not_appear_in_normal_queries()
{
    // Arrange — create and soft-delete a patient
    var patient = Patient.Create("Test Patient", new DateOnly(1985, 3, 15), "MRN-DEL").Value;
    _context.Patients.Add(patient);
    await _context.SaveChangesAsync();

    patient.SoftDelete();
    await _context.SaveChangesAsync();

    // Act — normal query (should apply HasQueryFilter)
    var patients = await _context.Patients.ToListAsync();

    // Assert — deleted patient not visible
    patients.Should().NotContain(p => p.MRN == "MRN-DEL");

    // But visible with IgnoreQueryFilters
    var allPatients = await _context.Patients
        .IgnoreQueryFilters()
        .ToListAsync();
    allPatients.Should().Contain(p => p.MRN == "MRN-DEL");
}

// Verify all migrations apply cleanly
[Fact]
public async Task All_pending_migrations_should_apply_successfully()
{
    using var container = await new MsSqlBuilder().Build().StartAsync();
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(container.GetConnectionString())
        .Options;

    await using var ctx = new AppDbContext(options, new NoOpPublisher());

    // Should not throw
    await ctx.Invoking(c => c.Database.MigrateAsync())
        .Should().NotThrowAsync();

    // No pending migrations remain
    var pending = await ctx.Database.GetPendingMigrationsAsync();
    pending.Should().BeEmpty("all migrations should be applied");
}

// Verify migration rollback (down() method)
[Fact]
public async Task Last_migration_should_roll_back_successfully()
{
    using var container = await new MsSqlBuilder().Build().StartAsync();
    // Apply all
    // Then roll back last migration
    // Then apply again — should not error
}

// For query logic testing (not DB-specific behavior), in-memory is fine
[Fact]
public async Task GetActivePatients_should_exclude_inactive()
{
    await using var ctx = CreateInMemoryContext();

    ctx.Patients.AddRange(
        Patient.Create("Active 1", new DateOnly(1985, 3, 15), "MRN-A1").Value,
        Patient.Create("Active 2", new DateOnly(1990, 7, 22), "MRN-A2").Value
    );

    var inactive = Patient.Create("Inactive", new DateOnly(1970, 1, 1), "MRN-I1").Value;
    inactive.Deactivate();
    ctx.Patients.Add(inactive);

    await ctx.SaveChangesAsync();

    // Act
    var repo    = new PatientRepository(ctx);
    var results = await repo.GetActiveAsync(CancellationToken.None);

    // Assert
    results.Should().HaveCount(2);
    results.Should().AllSatisfy(p => p.IsActive.Should().BeTrue());
}

// Verify value object serialization round-trips correctly
[Fact]
public async Task Dosage_value_object_should_persist_and_retrieve_correctly()
{
    // Arrange
    var dosage  = Dosage.Create(5.5m, "mg").Value;
    var patient = Patient.Create("Test", new DateOnly(1985, 3, 15), "MRN-001").Value;
    var rx      = Prescription.Create(patient.Id, "Warfarin", dosage).Value;

    _context.Prescriptions.Add(rx);
    await _context.SaveChangesAsync();
    _context.ChangeTracker.Clear();

    // Act — load fresh
    var retrieved = await _context.Prescriptions
        .FirstOrDefaultAsync(r => r.Id == rx.Id);

    // Assert value object round-trip
    retrieved!.Dosage.Amount.Should().Be(5.5m);
    retrieved.Dosage.Unit.Should().Be("mg");
}