using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using (var seed = new LabDb(options, "A")) { await seed.Database.EnsureCreatedAsync(); seed.Items.Add(new Item { TenantId = "A", Sku = "R1", Stock = 5, PriceCents = 125, Version = 1 }); await seed.SaveChangesAsync(); }
Check(await Reserve(options, "A", "R1", 2, "REF1", CancellationToken.None) == "reserved:250", "reservation accepted"); Console.WriteLine("reserved:250");
Check(await Reserve(options, "A", "R1", 1, "REF1", CancellationToken.None) == "duplicate-ref", "duplicate reference rejects transaction"); Console.WriteLine("duplicate-ref");
Check(await Reserve(options, "A", "R1", 99, "REF2", CancellationToken.None) == "insufficient", "stock rejection"); Console.WriteLine("insufficient");
Check(await Reserve(options, "B", "R1", 1, "REF3", CancellationToken.None) == "missing", "tenant-scoped lookup"); Console.WriteLine("other-tenant:missing");
await using (var verify = new LabDb(options, "A")) { var item = await verify.Items.AsNoTracking().SingleAsync(); Check(item.Stock == 3 && item.Version == 2 && await verify.Reservations.CountAsync() == 1, "fresh atomic result"); Console.WriteLine("stock:3"); Console.WriteLine("reservations:1"); }
static async Task<string> Reserve(DbContextOptions<LabDb> options, string trustedTenant, string sku, int quantity, string reference, CancellationToken token)
{
    if (quantity <= 0 || string.IsNullOrWhiteSpace(reference)) return "invalid";
    await using var db = new LabDb(options, trustedTenant); await using var transaction = await db.Database.BeginTransactionAsync(token);
    var item = await db.Items.SingleOrDefaultAsync(x => x.Sku == sku, token); if (item == null) return "missing"; if (item.Stock < quantity) return "insufficient";
    item.Stock -= quantity; item.Version = checked(item.Version + 1);
    db.Reservations.Add(new Reservation { TenantId = trustedTenant, Reference = reference, ItemId = item.Id, Quantity = quantity, TotalCents = checked((long)item.PriceCents * quantity) });
    try { await db.SaveChangesAsync(token); await transaction.CommitAsync(token); return "reserved:" + checked((long)item.PriceCents * quantity); }
    catch (DbUpdateException ex) when (ex.InnerException is SqliteException { SqliteErrorCode: 19, SqliteExtendedErrorCode: 2067 }) { await transaction.RollbackAsync(CancellationToken.None); return "duplicate-ref"; }
}

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }