// Write context: full change tracking, navigation properties, interceptors
public sealed class WriteDbContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(WriteDbContext).Assembly);
    }
}

// Read context: no-tracking by default, optimized for projections
public sealed class ReadDbContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(ReadDbContext).Assembly);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Note: this is supplemental -- primary config is in DI registration
    }
}

// Write context: standard tracking, connection resiliency
builder.Services.AddDbContext<WriteDbContext>(options =>
    options.UseNpgsql(connectionString, npgsql =>
        npgsql.EnableRetryOnFailure(maxRetryCount: 3)));

// Read context: no-tracking, optionally pointed at a read replica
builder.Services.AddDbContext<ReadDbContext>(options =>
    options.UseNpgsql(readReplicaConnectionString ?? connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

// Order is the aggregate root -- it owns OrderItems
public sealed class Order
{
    public int Id { get; private set; }
    public string CustomerId { get; private set; } = default!;
    public OrderStatus Status { get; private set; }
    public DateTimeOffset CreatedAt { get; private set; }

    // Owned collection -- part of the Order aggregate
    private readonly List<OrderItem> _items = [];
    public IReadOnlyList<OrderItem> Items => _items.AsReadOnly();

    public void AddItem(int productId, int quantity, decimal unitPrice)
    {
        if (Status != OrderStatus.Draft)
            throw new InvalidOperationException("Cannot add items to a non-draft order.");

        _items.Add(new OrderItem(productId, quantity, unitPrice));
    }
}

// OrderItem belongs to the Order aggregate -- no independent access
public sealed class OrderItem
{
    public int Id { get; private set; }
    public int ProductId { get; private set; }
    public int Quantity { get; private set; }
    public decimal UnitPrice { get; private set; }

    internal OrderItem(int productId, int quantity, decimal unitPrice)
    {
        ProductId = productId;
        Quantity = quantity;
        UnitPrice = unitPrice;
    }

    private OrderItem() { } // EF Core constructor
}

public sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.HasKey(o => o.Id);

        builder.Property(o => o.CustomerId).IsRequired().HasMaxLength(50);
        builder.Property(o => o.Status).HasConversion<string>();

        // Owned collection navigation -- cascade delete, no independent DbSet
        builder.OwnsMany(o => o.Items, items =>
        {
            items.WithOwner().HasForeignKey("OrderId");
            items.Property(i => i.ProductId).IsRequired();
        });

        // Alternatively, if OrderItem needs its own table with explicit FK:
        // builder.HasMany(o => o.Items)
        //     .WithOne()
        //     .HasForeignKey("OrderId")
        //     .OnDelete(DeleteBehavior.Cascade);
        //
        // builder.Navigation(o => o.Items)
        //     .UsePropertyAccessMode(PropertyAccessMode.Field);
    }
}

public sealed class CreateOrderHandler(WriteDbContext db)
{
    public async Task<int> HandleAsync(
        CreateOrderCommand command,
        CancellationToken ct)
    {
        var order = new Order(command.CustomerId);

        foreach (var item in command.Items)
        {
            order.AddItem(item.ProductId, item.Quantity, item.UnitPrice);
        }

        db.Orders.Add(order);
        await db.SaveChangesAsync(ct);

        return order.Id;
    }
}

public interface IOrderRepository
{
    Task<Order?> GetByIdAsync(int id, CancellationToken ct);
    Task AddAsync(Order order, CancellationToken ct);
    Task SaveChangesAsync(CancellationToken ct);
}

public sealed class OrderRepository(WriteDbContext db) : IOrderRepository
{
    public async Task<Order?> GetByIdAsync(int id, CancellationToken ct)
    {
        return await db.Orders
            .Include(o => o.Items)
            .FirstOrDefaultAsync(o => o.Id == id, ct);
    }

    public async Task AddAsync(Order order, CancellationToken ct)
    {
        await db.Orders.AddAsync(order, ct);
    }

    public Task SaveChangesAsync(CancellationToken ct)
    {
        return db.SaveChangesAsync(ct);
    }
}

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging()  // Development only
           .EnableDetailedErrors());      // Development only

// BAD: N+1 -- each order.Items triggers a query
var orders = await db.Orders.ToListAsync(ct);
foreach (var order in orders)
{
    var total = order.Items.Sum(i => i.Quantity * i.UnitPrice); // Lazy load!
}

// GOOD: Eager load with Include
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync(ct);

// BAD: N+1 -- one query per customer
foreach (var customerId in customerIds)
{
    var orders = await db.Orders
        .Where(o => o.CustomerId == customerId)
        .ToListAsync(ct);
    // ...
}

// GOOD: Single query with Contains
var orders = await db.Orders
    .Where(o => customerIds.Contains(o.CustomerId))
    .ToListAsync(ct);

// BAD: Loads full entity graph, then maps in memory
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToListAsync(ct);
var dtos = orders.Select(o => new OrderDto(...));

// GOOD: Project in the query -- no tracking, no extra data loaded
var dtos = await db.Orders
    .Select(o => new OrderDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
    })
    .ToListAsync(ct);

public async Task<PagedResult<OrderSummary>> GetOrdersAsync(
    string customerId,
    int? afterId,
    int pageSize,
    CancellationToken ct)
{
    const int maxPageSize = 100;
    pageSize = Math.Min(pageSize, maxPageSize);

    var query = db.Orders
        .AsNoTracking()
        .Where(o => o.CustomerId == customerId);

    if (afterId.HasValue)
    {
        query = query.Where(o => o.Id > afterId.Value);
    }

    var items = await query
        .OrderBy(o => o.Id)
        .Take(pageSize + 1)  // Fetch one extra to detect "has next page"
        .Select(o => new OrderSummary
        {
            Id = o.Id,
            Status = o.Status,
            CreatedAt = o.CreatedAt,
            Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
        })
        .ToListAsync(ct);

    var hasNext = items.Count > pageSize;
    if (hasNext)
    {
        items.RemoveAt(items.Count - 1);
    }

    return new PagedResult<OrderSummary>
    {
        Items = items,
        HasNextPage = hasNext,
        NextCursor = hasNext ? items[^1].Id : null
    };
}

var page = await db.Orders
    .AsNoTracking()
    .OrderBy(o => o.CreatedAt)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

// Interceptor approach: enforce max rows at the DbContext level
public sealed class RowLimitInterceptor : IQueryExpressionInterceptor
{
    private const int MaxRows = 1000;

    public Expression QueryCompilationStarting(
        Expression queryExpression,
        QueryExpressionEventData eventData)
    {
        // This is a simplified illustration -- actual implementation requires
        // expression tree analysis to detect existing Take() calls.
        // Consider using a code review rule or analyzer instead.
        return queryExpression;
    }
}

public sealed record OrderSummary
{
    public int Id { get; init; }
    public string CustomerName { get; init; } = default!;
    public int ItemCount { get; init; }
    public decimal Total { get; init; }
    public DateTimeOffset CreatedAt { get; init; }
}

var summaries = await db.Orders
    .Select(o => new OrderSummary
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Quantity * i.UnitPrice),
        CreatedAt = o.CreatedAt
    })
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .ToListAsync(ct);