using Microsoft.Data.Sqlite;
public sealed class Store {
    private readonly string connectionString;
    public Store(IConfiguration configuration) {
        string path = configuration["Production:DatabasePath"] ?? Path.Combine(Path.GetTempPath(), "sharprune-production-" + Guid.NewGuid().ToString("N") + ".db");
        connectionString = new SqliteConnectionStringBuilder { DataSource = path, DefaultTimeout = 1, Pooling = false, ForeignKeys = true }.ToString();
    }
    public SqliteConnection Open() { var connection = new SqliteConnection(connectionString); connection.Open(); return connection; }
    public static SqliteCommand Command(SqliteConnection c, string sql, SqliteTransaction? tx = null, params (string Key, object? Value)[] values) {
        var command = c.CreateCommand(); command.CommandText = sql; command.Transaction = tx;
        foreach (var value in values) command.Parameters.AddWithValue(value.Key, value.Value ?? DBNull.Value);
        return command;
    }
    public static int Execute(SqliteConnection c, string sql, SqliteTransaction? tx = null, params (string Key, object? Value)[] values) { using var command = Command(c, sql, tx, values); return command.ExecuteNonQuery(); }
    public static long Number(SqliteConnection c, string sql, SqliteTransaction? tx = null, params (string Key, object? Value)[] values) { using var command = Command(c, sql, tx, values); return Convert.ToInt64(command.ExecuteScalar(), System.Globalization.CultureInfo.InvariantCulture); }
    public void Initialize() {
        using var c = Open(); Execute(c, "PRAGMA journal_mode=WAL");
        Execute(c, """
CREATE TABLE IF NOT EXISTS sql_values(id INTEGER PRIMARY KEY, amount INTEGER CHECK(amount > 0));
CREATE TABLE IF NOT EXISTS sql_required(amount INTEGER NOT NULL CHECK(amount > 0));
INSERT OR IGNORE INTO sql_values VALUES(1,NULL),(2,NULL),(3,1),(4,2),(5,3);
CREATE TABLE IF NOT EXISTS products(id INTEGER PRIMARY KEY,name TEXT NOT NULL,category TEXT NOT NULL,price INTEGER NOT NULL CHECK(price>=0),active INTEGER NOT NULL CHECK(active IN(0,1)));
INSERT OR IGNORE INTO products VALUES(1,'Rune','books',30,1),(2,'rune','books',10,1),(3,'Sword','books',20,0),(4,'Shield','gear',20,1),(5,'RUNE','gear',15,0);
CREATE INDEX IF NOT EXISTS ix_products_category_price_id ON products(category,price,id);
CREATE INDEX IF NOT EXISTS ix_products_active_price ON products(price) WHERE active=1;
CREATE INDEX IF NOT EXISTS ix_products_lower_name ON products(lower(name));
CREATE TABLE IF NOT EXISTS counters(id INTEGER PRIMARY KEY,value INTEGER NOT NULL);
INSERT OR IGNORE INTO counters VALUES(1,10);
CREATE TABLE IF NOT EXISTS orders(order_id TEXT PRIMARY KEY,quantity INTEGER NOT NULL CHECK(quantity BETWEEN 1 AND 10));
CREATE TABLE IF NOT EXISTS outbox(id INTEGER PRIMARY KEY AUTOINCREMENT,event_key TEXT UNIQUE NOT NULL,aggregate_id TEXT NOT NULL,sequence INTEGER NOT NULL,payload TEXT NOT NULL,status TEXT NOT NULL DEFAULT 'pending',owner TEXT,lease_until INTEGER,attempts INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS inbox(message_id TEXT PRIMARY KEY,payload TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS effects(id INTEGER PRIMARY KEY,total INTEGER NOT NULL);
INSERT OR IGNORE INTO effects VALUES(1,0);
CREATE TABLE IF NOT EXISTS aggregate_state(aggregate_id TEXT PRIMARY KEY,last_sequence INTEGER NOT NULL,total INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS ordered_inbox(message_id TEXT PRIMARY KEY,payload TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS deadletters(message_id TEXT PRIMARY KEY,reason TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS documents(id INTEGER PRIMARY KEY,body TEXT NOT NULL,version INTEGER NOT NULL);
INSERT OR IGNORE INTO documents VALUES(1,'rune',1);
CREATE TABLE IF NOT EXISTS catalog_cache(id INTEGER PRIMARY KEY,name TEXT NOT NULL,version INTEGER NOT NULL);
INSERT OR IGNORE INTO catalog_cache VALUES(1,'rune',1);
CREATE TABLE IF NOT EXISTS contract_effect(id INTEGER PRIMARY KEY,value INTEGER NOT NULL);
INSERT OR IGNORE INTO contract_effect VALUES(1,0);
CREATE TABLE IF NOT EXISTS pipeline_requests(request_id TEXT PRIMARY KEY,payload TEXT NOT NULL,result_version INTEGER NOT NULL);
""");
    }
    public object Counts() { using var c = Open(); return new { orders = Number(c, "SELECT COUNT(*) FROM orders"), outbox = Number(c, "SELECT COUNT(*) FROM outbox"), inbox = Number(c, "SELECT COUNT(*) FROM inbox"), effects = Number(c, "SELECT total FROM effects WHERE id=1") }; }
}