Data and Migrations

using System.Buffers.Text;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public enum OrderStatus { Pending, Paid, Shipped, Cancelled }

public sealed class Customer
{
    public long Id { get; set; }
    public required string Subject { get; set; }          // the identity provider's sub claim
    public required string Email { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
}

public sealed class Product
{
    public long Id { get; set; }
    public required string Sku { get; set; }
    public required string Name { get; set; }
    public decimal UnitPrice { get; set; }
    public bool IsActive { get; set; } = true;
    public uint Version { get; set; }
}

public sealed class Order
{
    public long Id { get; set; }
    public long CustomerId { get; set; }
    public OrderStatus Status { get; set; }
    public decimal Total { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
    public uint Version { get; set; }
    public List<OrderLine> Lines { get; } = [];
}

public sealed class OrderLine
{
    public long Id { get; set; }
    public long OrderId { get; set; }
    public required string Sku { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }                // copied from Product when the order is placed
}

public sealed class IdempotencyRecord
{
    public long CustomerId { get; set; }
    public required string Key { get; set; }
    public required string RequestHash { get; set; }
    public long OrderId { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
}

public sealed class OutboxMessage
{
    public Guid Id { get; set; }
    public required string Type { get; set; }
    public required string Payload { get; set; }
    public DateTimeOffset OccurredAt { get; set; }
    public DateTimeOffset? PublishedAt { get; set; }
}

public sealed class OrdersDbContext(DbContextOptions<OrdersDbContext> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<IdempotencyRecord> IdempotencyRecords => Set<IdempotencyRecord>();
    public DbSet<OutboxMessage> Outbox => Set<OutboxMessage>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(OrdersDbContext).Assembly);
}

internal sealed class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> customer)
    {
        customer.Property(c => c.Subject).HasMaxLength(64);
        customer.Property(c => c.Email).HasMaxLength(320);
        customer.HasIndex(c => c.Subject).IsUnique();
    }
}

internal sealed class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> product)
    {
        product.Property(p => p.Sku).HasMaxLength(32);
        product.Property(p => p.Name).HasMaxLength(200);
        product.Property(p => p.UnitPrice).HasPrecision(12, 2);
        product.Property(p => p.Version).IsRowVersion();
        product.HasIndex(p => p.Sku).IsUnique();
    }
}

internal sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> order)
    {
        order.Property(o => o.Status).HasConversion<string>().HasMaxLength(16);
        order.Property(o => o.Total).HasPrecision(12, 2);
        order.Property(o => o.Version).IsRowVersion();   // Npgsql maps this to the xmin system column

        order.HasOne<Customer>().WithMany().HasForeignKey(o => o.CustomerId).OnDelete(DeleteBehavior.Restrict);
        order.HasMany(o => o.Lines).WithOne().HasForeignKey(l => l.OrderId).OnDelete(DeleteBehavior.Cascade);

        // Matches ORDER BY CreatedAt DESC, Id DESC for one customer, so each page is an index seek.
        order.HasIndex(o => new { o.CustomerId, o.CreatedAt, o.Id }).IsDescending(false, true, true);
    }
}

internal sealed class OrderLineConfiguration : IEntityTypeConfiguration<OrderLine>
{
    public void Configure(EntityTypeBuilder<OrderLine> line)
    {
        line.Property(l => l.Sku).HasMaxLength(32);
        line.Property(l => l.UnitPrice).HasPrecision(12, 2);
        line.ToTable(t => t.HasCheckConstraint("CK_OrderLine_Quantity", "\"Quantity\" BETWEEN 1 AND 100"));
    }
}

internal sealed class IdempotencyRecordConfiguration : IEntityTypeConfiguration<IdempotencyRecord>
{
    public void Configure(EntityTypeBuilder<IdempotencyRecord> record)
    {
        // The composite key is the unique constraint that makes a concurrent retry fail instead of duplicating.
        record.HasKey(r => new { r.CustomerId, r.Key });
        record.Property(r => r.Key).HasMaxLength(64);
        record.Property(r => r.RequestHash).HasMaxLength(64);
        record.HasIndex(r => r.CreatedAt);                // the nightly purge of keys older than 24 hours
    }
}

internal sealed class OutboxMessageConfiguration : IEntityTypeConfiguration<OutboxMessage>
{
    public void Configure(EntityTypeBuilder<OutboxMessage> message)
    {
        message.Property(m => m.Type).HasMaxLength(100);
        message.Property(m => m.Payload).HasColumnType("jsonb");
        message.HasIndex(m => m.OccurredAt).HasFilter("\"PublishedAt\" IS NULL");
    }
}

public sealed record OrderSummary(long Id, OrderStatus Status, decimal Total, DateTimeOffset CreatedAt);

public sealed record OrderCursor(DateTimeOffset CreatedAt, long Id)
{
    public string Encode() => Base64Url.EncodeToString(Encoding.UTF8.GetBytes($"{CreatedAt.UtcTicks}:{Id}"));

    public static bool TryDecode(string? value, out OrderCursor cursor)
    {
        cursor = new OrderCursor(default, 0);
        if (string.IsNullOrEmpty(value) || value.Length > 64) return false;
        try
        {
            var parts = Encoding.UTF8.GetString(Base64Url.DecodeFromChars(value)).Split(':');
            if (parts.Length != 2 || !long.TryParse(parts[0], out var ticks) || !long.TryParse(parts[1], out var id)) return false;
            cursor = new OrderCursor(new DateTimeOffset(ticks, TimeSpan.Zero), id);
            return true;
        }
        catch (FormatException)
        {
            return false;
        }
    }
}

public static class OrderQueries
{
    public static async Task<(List<OrderSummary> Items, string? Next)> ListForCustomerAsync(
        OrdersDbContext db, long customerId, string? after, int limit, CancellationToken ct)
    {
        var query = db.Orders.AsNoTracking().Where(o => o.CustomerId == customerId);

        if (OrderCursor.TryDecode(after, out var c))
            query = query.Where(o => o.CreatedAt < c.CreatedAt || (o.CreatedAt == c.CreatedAt && o.Id < c.Id));

        var rows = await query
            .OrderByDescending(o => o.CreatedAt).ThenByDescending(o => o.Id)
            .Take(limit + 1)
            .Select(o => new OrderSummary(o.Id, o.Status, o.Total, o.CreatedAt))
            .ToListAsync(ct);

        if (rows.Count <= limit) return (rows, null);

        var last = rows[limit - 1];
        return (rows[..limit], new OrderCursor(last.CreatedAt, last.Id).Encode());
    }
}