// CSharpDB — Embedded Database Engine for .NET
// Zero dependencies. ACID-compliant. Production-grade.

using CSharpDB.Engine;
using CSharpDB.Data;
using System.Threading.Channels;

namespace MyApp.DataLayer;

public sealed class AppDatabase : IAsyncDisposable
{
    private readonly Database _db;
    private readonly Channel<WriteOp> _writes;

    public static async Task<AppDatabase> OpenAsync(string path)
    {
        var db = await Database.OpenAsync(path);
        return new AppDatabase(db);
    }

    private AppDatabase(Database db)
    {
        _db = db;
        _writes = Channel.CreateBounded<WriteOp>(1024);
    }

    public async Task CreateSchemaAsync()
    {
        await using var conn = new CSharpDbConnection("Data Source=myapp.db");
        await conn.OpenAsync();

        await using var cmd = conn.CreateCommand();
        cmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS Products (
                Id       INTEGER PRIMARY KEY,
                Name     TEXT NOT NULL,
                Price    REAL NOT NULL,
                Category TEXT,
                Created  TEXT DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_products_category
                ON Products(Category);

            CREATE TABLE IF NOT EXISTS Orders (
                Id         INTEGER PRIMARY KEY,
                ProductId  INTEGER REFERENCES Products(Id),
                Quantity   INTEGER NOT NULL,
                Total      REAL NOT NULL,
                OrderDate  TEXT DEFAULT CURRENT_TIMESTAMP
            );";
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task<IReadOnlyList<Product>> GetTopProductsAsync(int limit)
    {
        await using var conn = new CSharpDbConnection("Data Source=myapp.db");
        await conn.OpenAsync();

        await using var cmd = conn.CreateCommand();
        cmd.CommandText = @"
            SELECT p.Id, p.Name, p.Price, SUM(o.Quantity) AS TotalSold
            FROM Products p
            JOIN Orders o ON o.ProductId = p.Id
            GROUP BY p.Id, p.Name, p.Price
            ORDER BY TotalSold DESC
            LIMIT @limit";
        cmd.Parameters.Add(new CSharpDbParameter("@limit", limit));

        var results = new List<Product>();
        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            results.Add(new Product(
                reader.GetInt32(0),
                reader.GetString(1),
                reader.GetDouble(2),
                reader.GetInt32(3)
            ));
        }
        return results;
    }

    // Collection API for flexible document storage
    public async Task StoreSessionAsync(UserSession session)
    {
        var sessions = await _db.GetCollectionAsync<UserSession>("sessions");
        await sessions.PutAsync(session.Id, session);
    }

    public async Task<UserSession?> GetSessionAsync(string id)
    {
        var sessions = await _db.GetCollectionAsync<UserSession>("sessions");
        return await sessions.GetAsync(id);
    }

    public async ValueTask DisposeAsync()
    {
        _writes.Writer.Complete();
        await _db.DisposeAsync();
    }
}

public record Product(int Id, string Name, double Price, int TotalSold);
public record UserSession(string Id, string UserId, DateTime Expires);

// Query with the Engine API
await using var result = await db.ExecuteAsync(
    "SELECT Name, Price FROM Products ORDER BY Price DESC LIMIT 5");
while (await result.MoveNextAsync())
    Console.WriteLine($"{result.Current[0].AsText}: ${result.Current[1].AsReal}");

// ─── End of sample ───

// CSharpDB — Embedded Database Engine for .NET
// Zero dependencies. ACID-compliant. Production-grade.

using CSharpDB.Engine;
using CSharpDB.Data;
using System.Threading.Channels;

namespace MyApp.DataLayer;

public sealed class AppDatabase : IAsyncDisposable
{
    private readonly Database _db;
    private readonly Channel<WriteOp> _writes;

    public static async Task<AppDatabase> OpenAsync(string path)
    {
        var db = await Database.OpenAsync(path);
        return new AppDatabase(db);
    }

    private AppDatabase(Database db)
    {
        _db = db;
        _writes = Channel.CreateBounded<WriteOp>(1024);
    }

    public async Task CreateSchemaAsync()
    {
        await using var conn = new CSharpDbConnection("Data Source=myapp.db");
        await conn.OpenAsync();

        await using var cmd = conn.CreateCommand();
        cmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS Products (
                Id       INTEGER PRIMARY KEY,
                Name     TEXT NOT NULL,
                Price    REAL NOT NULL,
                Category TEXT,
                Created  TEXT DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_products_category
                ON Products(Category);

            CREATE TABLE IF NOT EXISTS Orders (
                Id         INTEGER PRIMARY KEY,
                ProductId  INTEGER REFERENCES Products(Id),
                Quantity   INTEGER NOT NULL,
                Total      REAL NOT NULL,
                OrderDate  TEXT DEFAULT CURRENT_TIMESTAMP
            );";
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task<IReadOnlyList<Product>> GetTopProductsAsync(int limit)
    {
        await using var conn = new CSharpDbConnection("Data Source=myapp.db");
        await conn.OpenAsync();

        await using var cmd = conn.CreateCommand();
        cmd.CommandText = @"
            SELECT p.Id, p.Name, p.Price, SUM(o.Quantity) AS TotalSold
            FROM Products p
            JOIN Orders o ON o.ProductId = p.Id
            GROUP BY p.Id, p.Name, p.Price
            ORDER BY TotalSold DESC
            LIMIT @limit";
        cmd.Parameters.Add(new CSharpDbParameter("@limit", limit));

        var results = new List<Product>();
        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            results.Add(new Product(
                reader.GetInt32(0),
                reader.GetString(1),
                reader.GetDouble(2),
                reader.GetInt32(3)
            ));
        }
        return results;
    }
}