using Microsoft.Data.Sqlite;
public static class SqlLabs {
    public static void Map(WebApplication app) {
        app.MapGet("/sql/nulls", (Store store) => { using var c = store.Open(); return new { equalsNull = Store.Number(c, "SELECT COUNT(*) FROM sql_values WHERE amount = NULL"), isNull = Store.Number(c, "SELECT COUNT(*) FROM sql_values WHERE amount IS NULL"), all = Store.Number(c, "SELECT COUNT(*) FROM sql_values"), known = Store.Number(c, "SELECT COUNT(amount) FROM sql_values"), sum = Store.Number(c, "SELECT SUM(amount) FROM sql_values") }; });
        app.MapPost("/lab/sql/null-check", (Store store) => {
            using var c = store.Open(); using var tx = c.BeginTransaction(); Store.Execute(c,"INSERT INTO sql_values(id,amount) VALUES(6,NULL)",tx);
            bool rejected = false; try { Store.Execute(c,"INSERT INTO sql_required(amount) VALUES(NULL)",tx); } catch(SqliteException ex) when(ex.SqliteErrorCode==19) { rejected=true; }
            tx.Rollback(); return new { checkAllowedNull = true, notNullRejected = rejected };
        });
        app.MapGet("/sql/plans", (Store store) => {
            using var c = store.Open();
            var rows = new List<int>(); using(var command=Store.Command(c,"SELECT id,price FROM products WHERE category=$category ORDER BY price,id",null,("$category","books"))) using(var read=command.ExecuteReader()) while(read.Read()) rows.Add(read.GetInt32(0));
            return new { ids=rows, composite=Plan(c,"SELECT id,price FROM products WHERE category='books' ORDER BY price,id"), expression=Plan(c,"SELECT id FROM products WHERE lower(name)='rune'"), collated=Plan(c,"SELECT id FROM products WHERE name='rune' COLLATE NOCASE"), partial=Plan(c,"SELECT id,price FROM products WHERE active=1 ORDER BY price") };
        });
        app.MapPost("/lab/sql/snapshot", (Store store) => {
            using var first=store.Open(); Store.Execute(first,"UPDATE counters SET value=10 WHERE id=1"); using var reader=first.BeginTransaction(deferred:true);
            long before=Store.Number(first,"SELECT value FROM counters WHERE id=1",reader);
            using(var second=store.Open()) Store.Execute(second,"UPDATE counters SET value=20 WHERE id=1");
            long during=Store.Number(first,"SELECT value FROM counters WHERE id=1",reader); reader.Commit();
            using var fresh=store.Open(); return new { before,during,after=Store.Number(fresh,"SELECT value FROM counters WHERE id=1") };
        });
        app.MapPost("/lab/sql/retry", (Store store) => {
            using var first=store.Open(); Store.Execute(first,"UPDATE counters SET value=10 WHERE id=1"); using var stale=first.BeginTransaction(deferred:true);
            long seen=Store.Number(first,"SELECT value FROM counters WHERE id=1",stale);
            using(var second=store.Open()) Store.Execute(second,"UPDATE counters SET value=20 WHERE id=1");
            int error=0,extended=0; try { Store.Execute(first,"UPDATE counters SET value=$value WHERE id=1",stale,("$value",seen+1)); } catch(SqliteException ex) when(ex.SqliteErrorCode==5 || ex.SqliteErrorCode==6) { error=ex.SqliteErrorCode; extended=ex.SqliteExtendedErrorCode; }
            stale.Rollback(); if(error==0) return Results.Problem(statusCode:500,title:"Fixture did not encounter the expected stale-writer boundary");
            using var fresh=store.Open(); using var tx=fresh.BeginTransaction(); long current=Store.Number(fresh,"SELECT value FROM counters WHERE id=1",tx); Store.Execute(fresh,"UPDATE counters SET value=$value WHERE id=1",tx,("$value",current+1)); tx.Commit();
            return Results.Ok(new { attempts=2,error,extended,value=current+1 });
        });
    }
    private static List<string> Plan(SqliteConnection c,string sql) { var rows=new List<string>(); using var command=Store.Command(c,"EXPLAIN QUERY PLAN "+sql); using var read=command.ExecuteReader(); while(read.Read()) rows.Add(read.GetString(3)); return rows; }
}