Quick Start

using OutWit.Database.Core.Builder;

// Create an in-memory database
using var db = WitDatabase.CreateInMemory();

// Store a value
var key = "greeting"u8.ToArray();
var value = "Hello, WitDatabase!"u8.ToArray();
db.Put(key, value);

// Retrieve the value
var retrieved = db.Get(key);
Console.WriteLine(System.Text.Encoding.UTF8.GetString(retrieved!));
// Output: Hello, WitDatabase!

using OutWit.Database.Core.Builder;

// Create a new database file (or open existing)
using var db = WitDatabase.CreateOrOpen("myapp.witdb");

// Store data
db.Put("user:1:name"u8.ToArray(), "Alice"u8.ToArray());
db.Put("user:1:email"u8.ToArray(), "alice@example.com"u8.ToArray());
db.Put("user:2:name"u8.ToArray(), "Bob"u8.ToArray());
db.Put("user:2:email"u8.ToArray(), "bob@example.com"u8.ToArray());

Console.WriteLine("Data saved to myapp.witdb");

using OutWit.Database.Core.Builder;
using System.Text;

using var db = WitDatabase.CreateOrOpen("demo.witdb");

// Helper to convert strings to bytes
byte[] ToBytes(string s) => Encoding.UTF8.GetBytes(s);
string FromBytes(byte[] b) => Encoding.UTF8.GetString(b);

// ═══════════════════════════════════════════════════════════
// PUT — Store a value
// ═══════════════════════════════════════════════════════════
db.Put(ToBytes("name"), ToBytes("Alice"));
db.Put(ToBytes("age"), ToBytes("30"));
db.Put(ToBytes("city"), ToBytes("New York"));

// ═══════════════════════════════════════════════════════════
// GET — Retrieve a value
// ═══════════════════════════════════════════════════════════
var name = db.Get(ToBytes("name"));
if (name != null)
{
    Console.WriteLine($"Name: {FromBytes(name)}");  // Name: Alice
}

// Check if key exists
var unknown = db.Get(ToBytes("unknown_key"));
Console.WriteLine($"Unknown key exists: {unknown != null}");  // false

// ═══════════════════════════════════════════════════════════
// DELETE — Remove a value
// ═══════════════════════════════════════════════════════════
db.Delete(ToBytes("city"));
var city = db.Get(ToBytes("city"));
Console.WriteLine($"City after delete: {city != null}");  // false

// ═══════════════════════════════════════════════════════════
// UPDATE — Put overwrites existing values
// ═══════════════════════════════════════════════════════════
db.Put(ToBytes("age"), ToBytes("31"));  // Happy birthday!
var newAge = db.Get(ToBytes("age"));
Console.WriteLine($"New age: {FromBytes(newAge!)}");  // 31

// ═══════════════════════════════════════════════════════════
// EXISTS — Check without retrieving
// ═══════════════════════════════════════════════════════════
bool exists = db.Exists(ToBytes("name"));
Console.WriteLine($"Name exists: {exists}");  // true

using var db = WitDatabase.CreateOrOpen("users.witdb");

// Store some users with prefixed keys
db.Put("user:001:name"u8.ToArray(), "Alice"u8.ToArray());
db.Put("user:001:email"u8.ToArray(), "alice@example.com"u8.ToArray());
db.Put("user:002:name"u8.ToArray(), "Bob"u8.ToArray());
db.Put("user:002:email"u8.ToArray(), "bob@example.com"u8.ToArray());
db.Put("user:003:name"u8.ToArray(), "Charlie"u8.ToArray());
db.Put("config:theme"u8.ToArray(), "dark"u8.ToArray());

// Scan all keys starting with "user:"
var startKey = "user:"u8.ToArray();
var endKey = "user:\xff"u8.ToArray();  // \xff ensures we get all "user:*" keys

Console.WriteLine("All user keys:");
foreach (var (key, value) in db.Scan(startKey, endKey))
{
    var keyStr = System.Text.Encoding.UTF8.GetString(key);
    var valueStr = System.Text.Encoding.UTF8.GetString(value);
    Console.WriteLine($"  {keyStr} = {valueStr}");
}

// Output:
//   user:001:email = alice@example.com
//   user:001:name = Alice
//   user:002:email = bob@example.com
//   user:002:name = Bob
//   user:003:name = Charlie

using var db = WitDatabase.CreateOrOpen("async-demo.witdb");

// Async put
await db.PutAsync("key1"u8.ToArray(), "value1"u8.ToArray());

// Async get
var value = await db.GetAsync("key1"u8.ToArray());

// Async delete
await db.DeleteAsync("key1"u8.ToArray());

// Async scan
await foreach (var (key, val) in db.ScanAsync(startKey, endKey))
{
    // Process key-value pair
}

using System.Text.Json;
using OutWit.Database.Core.Builder;

using var db = WitDatabase.CreateOrOpen("objects.witdb");

// Define a class
public record User(int Id, string Name, string Email, DateTime CreatedAt);

// Serialize to JSON bytes
var user = new User(1, "Alice", "alice@example.com", DateTime.UtcNow);
var key = $"user:{user.Id}"u8.ToArray();
var value = JsonSerializer.SerializeToUtf8Bytes(user);

db.Put(key, value);

// Deserialize back
var retrieved = db.Get(key);
var loadedUser = JsonSerializer.Deserialize<User>(retrieved!);
Console.WriteLine($"Loaded: {loadedUser!.Name} ({loadedUser.Email})");

using System.Text;
using System.Text.Json;
using OutWit.Database.Core.Builder;

// ═══════════════════════════════════════════════════════════
// Simple Note Store using WitDatabase Core API
// ═══════════════════════════════════════════════════════════

using var db = WitDatabase.CreateOrOpen("notes.witdb");

// Data model
record Note(string Id, string Title, string Content, DateTime CreatedAt, DateTime? UpdatedAt);

// Helper functions
string NewId() => Guid.NewGuid().ToString("N")[..8];
byte[] NoteKey(string id) => Encoding.UTF8.GetBytes($"note:{id}");
byte[] ToJson(Note note) => JsonSerializer.SerializeToUtf8Bytes(note);
Note FromJson(byte[] data) => JsonSerializer.Deserialize<Note>(data)!;

// ─────────────────────────────────────────────────────────────
// Create some notes
// ─────────────────────────────────────────────────────────────
var note1 = new Note(NewId(), "Shopping List", "Milk, Bread, Eggs", DateTime.UtcNow, null);
var note2 = new Note(NewId(), "Meeting Notes", "Discuss Q3 roadmap", DateTime.UtcNow, null);
var note3 = new Note(NewId(), "Ideas", "Build a note-taking app!", DateTime.UtcNow, null);

db.Put(NoteKey(note1.Id), ToJson(note1));
db.Put(NoteKey(note2.Id), ToJson(note2));
db.Put(NoteKey(note3.Id), ToJson(note3));

Console.WriteLine($"Created 3 notes");

// ─────────────────────────────────────────────────────────────
// List all notes
// ─────────────────────────────────────────────────────────────
Console.WriteLine("\nAll notes:");
var start = "note:"u8.ToArray();
var end = "note:\xff"u8.ToArray();

foreach (var (key, value) in db.Scan(start, end))
{
    var note = FromJson(value);
    Console.WriteLine($"  [{note.Id}] {note.Title}");
}

// ─────────────────────────────────────────────────────────────
// Read a specific note
// ─────────────────────────────────────────────────────────────
var data = db.Get(NoteKey(note1.Id));
if (data != null)
{
    var loaded = FromJson(data);
    Console.WriteLine($"\nNote details:");
    Console.WriteLine($"  Title: {loaded.Title}");
    Console.WriteLine($"  Content: {loaded.Content}");
    Console.WriteLine($"  Created: {loaded.CreatedAt}");
}

// ─────────────────────────────────────────────────────────────
// Update a note
// ─────────────────────────────────────────────────────────────
var updated = note1 with 
{ 
    Content = "Milk, Bread, Eggs, Cheese", 
    UpdatedAt = DateTime.UtcNow 
};
db.Put(NoteKey(updated.Id), ToJson(updated));
Console.WriteLine($"\nUpdated note: {updated.Title}");

// ─────────────────────────────────────────────────────────────
// Delete a note
// ─────────────────────────────────────────────────────────────
db.Delete(NoteKey(note3.Id));
Console.WriteLine($"Deleted note: {note3.Title}");

// ─────────────────────────────────────────────────────────────
// Count remaining notes
// ─────────────────────────────────────────────────────────────
int count = 0;
foreach (var _ in db.Scan(start, end))
    count++;
Console.WriteLine($"\nTotal notes: {count}");

Created 3 notes

All notes:
  [a1b2c3d4] Shopping List
  [e5f6g7h8] Meeting Notes
  [i9j0k1l2] Ideas

Note details:
  Title: Shopping List
  Content: Milk, Bread, Eggs
  Created: 2025-01-20T12:00:00Z

Updated note: Shopping List
Deleted note: Ideas

Total notes: 2

using OutWit.Database.AdoNet;

// Create and open a connection
using var connection = new WitDbConnection("Data Source=myapp.witdb");
connection.Open();

// Now you can execute SQL commands
Console.WriteLine($"Connected! State: {connection.State}");

// File database
"Data Source=myapp.witdb"

// In-memory (for testing)
"Data Source=:memory:"

// With encryption
"Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecret123"

// Read-only mode
"Data Source=myapp.witdb;Mode=ReadOnly"

using OutWit.Database.AdoNet;

using var connection = new WitDbConnection("Data Source=tasks.witdb");
connection.Open();

using var cmd = connection.CreateCommand();

// Create a Tasks table
cmd.CommandText = """
    CREATE TABLE IF NOT EXISTS Tasks (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Title VARCHAR(200) NOT NULL,
        Description TEXT,
        Priority INTEGER DEFAULT 0,
        IsComplete BOOLEAN DEFAULT FALSE,
        DueDate DATETIME,
        CreatedAt DATETIME DEFAULT NOW()
    )
    """;
cmd.ExecuteNonQuery();

Console.WriteLine("Table created successfully!");

-- Primary key with auto-increment
Id INTEGER PRIMARY KEY AUTOINCREMENT

-- Not null
Title VARCHAR(200) NOT NULL

-- Default value
Priority INTEGER DEFAULT 0
CreatedAt DATETIME DEFAULT NOW()

-- Unique
Email VARCHAR(255) UNIQUE

-- Check constraint
CHECK (Priority >= 0 AND Priority <= 5)

-- Foreign key
FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE

using var cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO Tasks (Title, Priority) VALUES ('Buy groceries', 1)";
int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Inserted {rowsAffected} row(s)");

using var cmd = connection.CreateCommand();
cmd.CommandText = """
    INSERT INTO Tasks (Title, Description, Priority, DueDate) 
    VALUES (@title, @description, @priority, @dueDate)
    """;

cmd.Parameters.Add(new WitDbParameter("@title", "Finish report"));
cmd.Parameters.Add(new WitDbParameter("@description", "Q4 financial summary"));
cmd.Parameters.Add(new WitDbParameter("@priority", 2));
cmd.Parameters.Add(new WitDbParameter("@dueDate", DateTime.Now.AddDays(7)));

cmd.ExecuteNonQuery();

// Method 1: Use RETURNING clause (recommended)
cmd.CommandText = """
    INSERT INTO Tasks (Title, Priority) 
    VALUES (@title, @priority)
    RETURNING Id
    """;
cmd.Parameters.Add(new WitDbParameter("@title", "New task"));
cmd.Parameters.Add(new WitDbParameter("@priority", 1));

var newId = cmd.ExecuteScalar();
Console.WriteLine($"Inserted task with Id: {newId}");

// Method 2: Use LAST_INSERT_ROWID()
cmd.CommandText = "INSERT INTO Tasks (Title) VALUES ('Another task')";
cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT LAST_INSERT_ROWID()";
var lastId = cmd.ExecuteScalar();
Console.WriteLine($"Last inserted Id: {lastId}");

using var transaction = connection.BeginTransaction();

using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = "INSERT INTO Tasks (Title, Priority) VALUES (@title, @priority)";

var titleParam = new WitDbParameter("@title", "");
var priorityParam = new WitDbParameter("@priority", 0);
cmd.Parameters.Add(titleParam);
cmd.Parameters.Add(priorityParam);

// Prepare the command for repeated execution
cmd.Prepare();

// Insert 1000 tasks
for (int i = 1; i <= 1000; i++)
{
    titleParam.Value = $"Task #{i}";
    priorityParam.Value = i % 5;
    cmd.ExecuteNonQuery();
}

transaction.Commit();
Console.WriteLine("Inserted 1000 tasks");

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT Id, Title, Priority, IsComplete FROM Tasks ORDER BY Priority DESC";

using var reader = cmd.ExecuteReader();

Console.WriteLine("Tasks:");
Console.WriteLine("─".PadRight(60, '─'));

while (reader.Read())
{
    var id = reader.GetInt32(0);           // By index
    var title = reader["Title"];            // By name
    var priority = reader.GetInt32(2);
    var isComplete = reader.GetBoolean(3);
    
    var status = isComplete ? "✓" : "○";
    Console.WriteLine($"  {status} [{priority}] {title} (Id: {id})");
}

cmd.CommandText = "SELECT COUNT(*) FROM Tasks WHERE IsComplete = FALSE";
var incompleteCount = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine($"Incomplete tasks: {incompleteCount}");

cmd.CommandText = "SELECT MAX(Priority) FROM Tasks";
var maxPriority = cmd.ExecuteScalar();
Console.WriteLine($"Highest priority: {maxPriority}");

cmd.CommandText = """
    SELECT Id, Title, DueDate 
    FROM Tasks 
    WHERE Priority >= @minPriority 
      AND IsComplete = FALSE
      AND DueDate <= @deadline
    ORDER BY DueDate
    """;
cmd.Parameters.Clear();
cmd.Parameters.Add(new WitDbParameter("@minPriority", 2));
cmd.Parameters.Add(new WitDbParameter("@deadline", DateTime.Now.AddDays(7)));

using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"  {reader["Title"]} - Due: {reader["DueDate"]}");
}

while (reader.Read())
{
    var title = reader.GetString(reader.GetOrdinal("Title"));
    
    // Check for NULL before reading
    var descriptionOrdinal = reader.GetOrdinal("Description");
    var description = reader.IsDBNull(descriptionOrdinal) 
        ? "(no description)" 
        : reader.GetString(descriptionOrdinal);
    
    Console.WriteLine($"  {title}: {description}");
}

public record Task(
    int Id, 
    string Title, 
    string? Description, 
    int Priority, 
    bool IsComplete,
    DateTime? DueDate,
    DateTime CreatedAt
);

public static List<Task> GetAllTasks(WitDbConnection connection)
{
    var tasks = new List<Task>();
    
    using var cmd = connection.CreateCommand();
    cmd.CommandText = "SELECT * FROM Tasks ORDER BY CreatedAt DESC";
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        tasks.Add(new Task(
            Id: reader.GetInt32(reader.GetOrdinal("Id")),
            Title: reader.GetString(reader.GetOrdinal("Title")),
            Description: reader.IsDBNull(reader.GetOrdinal("Description")) 
                ? null 
                : reader.GetString(reader.GetOrdinal("Description")),
            Priority: reader.GetInt32(reader.GetOrdinal("Priority")),
            IsComplete: reader.GetBoolean(reader.GetOrdinal("IsComplete")),
            DueDate: reader.IsDBNull(reader.GetOrdinal("DueDate")) 
                ? null 
                : reader.GetDateTime(reader.GetOrdinal("DueDate")),
            CreatedAt: reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
        ));
    }
    
    return tasks;
}

// Usage
var allTasks = GetAllTasks(connection);
foreach (var task in allTasks)
{
    Console.WriteLine($"  [{task.Priority}] {task.Title}");
}

using var cmd = connection.CreateCommand();
cmd.CommandText = """
    UPDATE Tasks 
    SET IsComplete = TRUE 
    WHERE Id = @id
    """;
cmd.Parameters.Add(new WitDbParameter("@id", 5));

int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Updated {rowsAffected} row(s)");

cmd.CommandText = """
    UPDATE Tasks 
    SET Priority = Priority + 1 
    WHERE Id = @id
    RETURNING Id, Title, Priority
    """;
cmd.Parameters.Clear();
cmd.Parameters.Add(new WitDbParameter("@id", 3));

using var reader = cmd.ExecuteReader();
if (reader.Read())
{
    Console.WriteLine($"Updated: {reader["Title"]} - New priority: {reader["Priority"]}");
}

// Mark all overdue tasks as high priority
cmd.CommandText = """
    UPDATE Tasks 
    SET Priority = 5 
    WHERE DueDate < @now AND IsComplete = FALSE
    """;
cmd.Parameters.Clear();
cmd.Parameters.Add(new WitDbParameter("@now", DateTime.Now));

int updated = cmd.ExecuteNonQuery();
Console.WriteLine($"Marked {updated} overdue task(s) as high priority");

using var cmd = connection.CreateCommand();
cmd.CommandText = "DELETE FROM Tasks WHERE Id = @id";
cmd.Parameters.Add(new WitDbParameter("@id", 10));

int deleted = cmd.ExecuteNonQuery();
Console.WriteLine($"Deleted {deleted} row(s)");

// Delete all completed tasks older than 30 days
cmd.CommandText = """
    DELETE FROM Tasks 
    WHERE IsComplete = TRUE 
      AND CreatedAt < @cutoffDate
    """;
cmd.Parameters.Clear();
cmd.Parameters.Add(new WitDbParameter("@cutoffDate", DateTime.Now.AddDays(-30)));

int cleaned = cmd.ExecuteNonQuery();
Console.WriteLine($"Cleaned up {cleaned} old completed task(s)");

cmd.CommandText = """
    DELETE FROM Tasks 
    WHERE Id = @id
    RETURNING Id, Title
    """;
cmd.Parameters.Clear();
cmd.Parameters.Add(new WitDbParameter("@id", 7));

using var reader = cmd.ExecuteReader();
if (reader.Read())
{
    Console.WriteLine($"Deleted task: {reader["Title"]}");
}
else
{
    Console.WriteLine("Task not found");
}

cmd.CommandText = "UPDATE Tasks SET Priority = 3 WHERE Priority > 3";
cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT CHANGES()";
var changes = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine($"Rows affected: {changes}");

using OutWit.Database.AdoNet;

// ═══════════════════════════════════════════════════════════════════
// Task Manager - Complete ADO.NET Example
// ═══════════════════════════════════════════════════════════════════

using var connection = new WitDbConnection("Data Source=taskmanager.witdb");
connection.Open();

// ───────────────────────────────────────────────────────────────────
// 1. Create the schema
// ───────────────────────────────────────────────────────────────────
ExecuteNonQuery(connection, """
    CREATE TABLE IF NOT EXISTS Tasks (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Title VARCHAR(200) NOT NULL,
        Description TEXT,
        Priority INTEGER DEFAULT 0 CHECK (Priority >= 0 AND Priority <= 5),
        IsComplete BOOLEAN DEFAULT FALSE,
        DueDate DATETIME,
        CreatedAt DATETIME DEFAULT NOW()
    )
    """);
Console.WriteLine("✓ Schema ready\n");

// ───────────────────────────────────────────────────────────────────
// 2. Insert sample tasks
// ───────────────────────────────────────────────────────────────────
var tasks = new[]
{
    ("Write documentation", "Complete the Quick Start guide", 3, DateTime.Now.AddDays(2)),
    ("Review pull requests", null as string, 2, DateTime.Now.AddDays(1)),
    ("Fix bug #1234", "NullReferenceException in OrderService", 5, DateTime.Now),
    ("Update dependencies", "Upgrade to .NET 10", 1, DateTime.Now.AddDays(14)),
    ("Team meeting", "Weekly sync", 2, DateTime.Now.AddDays(3)),
};

foreach (var (title, description, priority, dueDate) in tasks)
{
    InsertTask(connection, title, description, priority, dueDate);
}
Console.WriteLine($"✓ Inserted {tasks.Length} tasks\n");

// ───────────────────────────────────────────────────────────────────
// 3. List all tasks
// ───────────────────────────────────────────────────────────────────
Console.WriteLine("All Tasks:");
Console.WriteLine("─".PadRight(70, '─'));
PrintTasks(connection, "SELECT * FROM Tasks ORDER BY Priority DESC, DueDate");

// ───────────────────────────────────────────────────────────────────
// 4. Find high-priority tasks
// ───────────────────────────────────────────────────────────────────
Console.WriteLine("\nHigh Priority Tasks (≥3):");
Console.WriteLine("─".PadRight(70, '─'));
PrintTasks(connection, "SELECT * FROM Tasks WHERE Priority >= 3 ORDER BY Priority DESC");

// ───────────────────────────────────────────────────────────────────
// 5. Complete a task
// ───────────────────────────────────────────────────────────────────
var completedTitle = CompleteTask(connection, 3); // Complete task #3
Console.WriteLine($"\n✓ Completed: \"{completedTitle}\"\n");

// ───────────────────────────────────────────────────────────────────
// 6. Show statistics
// ───────────────────────────────────────────────────────────────────
ShowStatistics(connection);

// ───────────────────────────────────────────────────────────────────
// 7. Delete completed tasks
// ───────────────────────────────────────────────────────────────────
int deleted = DeleteCompletedTasks(connection);
Console.WriteLine($"\n✓ Deleted {deleted} completed task(s)");

// ───────────────────────────────────────────────────────────────────
// 8. Final task list
// ───────────────────────────────────────────────────────────────────
Console.WriteLine("\nRemaining Tasks:");
Console.WriteLine("─".PadRight(70, '─'));
PrintTasks(connection, "SELECT * FROM Tasks ORDER BY DueDate");

// ═══════════════════════════════════════════════════════════════════
// Helper Methods
// ═══════════════════════════════════════════════════════════════════

static void ExecuteNonQuery(WitDbConnection conn, string sql)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

static int InsertTask(WitDbConnection conn, string title, string? description, int priority, DateTime dueDate)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = """
        INSERT INTO Tasks (Title, Description, Priority, DueDate) 
        VALUES (@title, @desc, @priority, @due)
        RETURNING Id
        """;
    cmd.Parameters.Add(new WitDbParameter("@title", title));
    cmd.Parameters.Add(new WitDbParameter("@desc", description ?? (object)DBNull.Value));
    cmd.Parameters.Add(new WitDbParameter("@priority", priority));
    cmd.Parameters.Add(new WitDbParameter("@due", dueDate));
    
    return Convert.ToInt32(cmd.ExecuteScalar());
}

static void PrintTasks(WitDbConnection conn, string sql)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        var id = reader.GetInt32(reader.GetOrdinal("Id"));
        var title = reader.GetString(reader.GetOrdinal("Title"));
        var priority = reader.GetInt32(reader.GetOrdinal("Priority"));
        var isComplete = reader.GetBoolean(reader.GetOrdinal("IsComplete"));
        var dueDate = reader.GetDateTime(reader.GetOrdinal("DueDate"));
        
        var status = isComplete ? "✓" : "○";
        var priorityStars = new string('★', priority) + new string('☆', 5 - priority);
        var dueDays = (dueDate.Date - DateTime.Today).Days;
        var dueText = dueDays switch
        {
            < 0 => $"OVERDUE by {-dueDays}d",
            0 => "TODAY",
            1 => "Tomorrow",
            _ => $"in {dueDays}d"
        };
        
        Console.WriteLine($"  {status} #{id,-3} [{priorityStars}] {title,-30} ({dueText})");
    }
}

static string CompleteTask(WitDbConnection conn, int taskId)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = """
        UPDATE Tasks 
        SET IsComplete = TRUE 
        WHERE Id = @id
        RETURNING Title
        """;
    cmd.Parameters.Add(new WitDbParameter("@id", taskId));
    
    return cmd.ExecuteScalar()?.ToString() ?? "(not found)";
}

static void ShowStatistics(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    
    Console.WriteLine("\nStatistics:");
    Console.WriteLine("─".PadRight(30, '─'));
    
    cmd.CommandText = "SELECT COUNT(*) FROM Tasks";
    Console.WriteLine($"  Total tasks:      {cmd.ExecuteScalar()}");
    
    cmd.CommandText = "SELECT COUNT(*) FROM Tasks WHERE IsComplete = TRUE";
    Console.WriteLine($"  Completed:        {cmd.ExecuteScalar()}");
    
    cmd.CommandText = "SELECT COUNT(*) FROM Tasks WHERE IsComplete = FALSE";
    Console.WriteLine($"  Pending:          {cmd.ExecuteScalar()}");
    
    cmd.CommandText = "SELECT COUNT(*) FROM Tasks WHERE DueDate < @now AND IsComplete = FALSE";
    cmd.Parameters.Add(new WitDbParameter("@now", DateTime.Now));
    Console.WriteLine($"  Overdue:          {cmd.ExecuteScalar()}");
    
    cmd.Parameters.Clear();
    cmd.CommandText = "SELECT AVG(Priority) FROM Tasks WHERE IsComplete = FALSE";
    var avgPriority = cmd.ExecuteScalar();
    Console.WriteLine($"  Avg priority:     {avgPriority:F1}");
}

static int DeleteCompletedTasks(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "DELETE FROM Tasks WHERE IsComplete = TRUE";
    return cmd.ExecuteNonQuery();
}

✓ Schema ready

✓ Inserted 5 tasks

All Tasks:
──────────────────────────────────────────────────────────────────────
  ○ #3  [★★★★★] Fix bug #1234                     (TODAY)
  ○ #1  [★★★☆☆] Write documentation               (in 2d)
  ○ #2  [★★☆☆☆] Review pull requests              (Tomorrow)
  ○ #5  [★★☆☆☆] Team meeting                      (in 3d)
  ○ #4  [★☆☆☆☆] Update dependencies               (in 14d)

High Priority Tasks (≥3):
──────────────────────────────────────────────────────────────────────
  ○ #3  [★★★★★] Fix bug #1234                     (TODAY)
  ○ #1  [★★★☆☆] Write documentation               (in 2d)

✓ Completed: "Fix bug #1234"

Statistics:
──────────────────────────────────
  Total tasks:      5
  Completed:        1
  Pending:          4
  Overdue:          0
  Avg priority:     2.0

✓ Deleted 1 completed task(s)

Remaining Tasks:
──────────────────────────────────────────────────────────────────────
  ○ #2  [★★☆☆☆] Review pull requests              (Tomorrow)
  ○ #1  [★★★☆☆] Write documentation               (in 2d)
  ○ #5  [★★☆☆☆] Team meeting                      (in 3d)
  ○ #4  [★☆☆☆☆] Update dependencies               (in 14d)

using var connection = new WitDbConnection("Data Source=app.witdb");
await connection.OpenAsync();

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Tasks";

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    // Process row
}

// Or for non-query
cmd.CommandText = "UPDATE Tasks SET Priority = 5 WHERE Id = 1";
await cmd.ExecuteNonQueryAsync();

// Or for scalar
cmd.CommandText = "SELECT COUNT(*) FROM Tasks";
var count = await cmd.ExecuteScalarAsync();

// A simple Todo item
public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string? Description { get; set; }
    public bool IsComplete { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? CompletedAt { get; set; }
}

using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;

public class AppDbContext : DbContext
{
    // DbSet properties represent tables
    public DbSet<Todo> Todos => Set<Todo>();

    // Configure the database connection
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseWitDb("Data Source=todos.witdb");
    }
}

// In Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=todos.witdb"));

// In your DbContext - remove OnConfiguring, use constructor
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options) { }

    public DbSet<Todo> Todos => Set<Todo>();
}

using var context = new AppDbContext();

// Create database and tables if they don't exist
context.Database.EnsureCreated();

Console.WriteLine("Database ready!");

using var context = new AppDbContext();
context.Database.EnsureCreated();

// Add a single entity
var todo = new Todo
{
    Title = "Learn WitDatabase",
    Description = "Complete the Quick Start guide"
};

context.Todos.Add(todo);
context.SaveChanges();

Console.WriteLine($"Created todo with Id: {todo.Id}");

var todos = new[]
{
    new Todo { Title = "Buy groceries" },
    new Todo { Title = "Call mom" },
    new Todo { Title = "Finish report", Description = "Q4 summary" }
};

context.Todos.AddRange(todos);
context.SaveChanges();

Console.WriteLine($"Created {todos.Length} todos");

var allTodos = context.Todos.ToList();

foreach (var todo in allTodos)
{
    Console.WriteLine($"  [{(todo.IsComplete ? "x" : " ")}] {todo.Title}");
}

var todo = context.Todos.Find(1);

if (todo != null)
{
    Console.WriteLine($"Found: {todo.Title}");
}

// Incomplete todos
var incomplete = context.Todos
    .Where(t => !t.IsComplete)
    .ToList();

// Single item (throws if not found or multiple)
var specific = context.Todos
    .Single(t => t.Title == "Learn WitDatabase");

// First matching (or null)
var first = context.Todos
    .FirstOrDefault(t => t.Title.Contains("report"));

// Check existence
bool hasIncomplete = context.Todos.Any(t => !t.IsComplete);

// Count
int incompleteCount = context.Todos.Count(t => !t.IsComplete);

// Find the entity
var todo = context.Todos.Find(1);

if (todo != null)
{
    // Modify properties
    todo.IsComplete = true;
    todo.CompletedAt = DateTime.UtcNow;
    
    // Save changes
    context.SaveChanges();
    
    Console.WriteLine($"Completed: {todo.Title}");
}

// Mark all old incomplete todos as complete
var oldTodos = context.Todos
    .Where(t => !t.IsComplete && t.CreatedAt < DateTime.UtcNow.AddDays(-30))
    .ToList();

foreach (var todo in oldTodos)
{
    todo.IsComplete = true;
    todo.CompletedAt = DateTime.UtcNow;
}

context.SaveChanges();
Console.WriteLine($"Auto-completed {oldTodos.Count} old todos");

// Find and remove
var todo = context.Todos.Find(5);

if (todo != null)
{
    context.Todos.Remove(todo);
    context.SaveChanges();
    
    Console.WriteLine($"Deleted: {todo.Title}");
}

// Remove all completed todos
var completed = context.Todos
    .Where(t => t.IsComplete)
    .ToList();

context.Todos.RemoveRange(completed);
context.SaveChanges();

Console.WriteLine($"Removed {completed.Count} completed todos");

var results = context.Todos
    .Where(t => !t.IsComplete)                    // WHERE IsComplete = FALSE
    .Where(t => t.Title.Contains("report"))       // AND Title LIKE '%report%'
    .OrderByDescending(t => t.CreatedAt)          // ORDER BY CreatedAt DESC
    .ThenBy(t => t.Title)                         // THEN BY Title
    .ToList();

// Anonymous type projection
var titles = context.Todos
    .Where(t => !t.IsComplete)
    .Select(t => new { t.Id, t.Title })
    .ToList();

foreach (var item in titles)
{
    Console.WriteLine($"  #{item.Id}: {item.Title}");
}

int page = 2;
int pageSize = 10;

var pagedTodos = context.Todos
    .OrderBy(t => t.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToList();

// Count
int total = context.Todos.Count();
int incomplete = context.Todos.Count(t => !t.IsComplete);

// Any / All
bool hasAny = context.Todos.Any();
bool allComplete = context.Todos.All(t => t.IsComplete);

// Min / Max
var oldest = context.Todos.Min(t => t.CreatedAt);
var newest = context.Todos.Max(t => t.CreatedAt);

var stats = context.Todos
    .GroupBy(t => t.IsComplete)
    .Select(g => new 
    { 
        IsComplete = g.Key, 
        Count = g.Count() 
    })
    .ToList();

foreach (var stat in stats)
{
    var label = stat.IsComplete ? "Complete" : "Incomplete";
    Console.WriteLine($"  {label}: {stat.Count}");
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    
    // Navigation property - one user has many todos
    public List<Todo> Todos { get; set; } = new();
}

public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public bool IsComplete { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    
    // Foreign key
    public int UserId { get; set; }
    
    // Navigation property - each todo belongs to one user
    public User User { get; set; } = null!;
}

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Todo> Todos => Set<Todo>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseWitDb("Data Source=app.witdb");
}

using var context = new AppDbContext();
context.Database.EnsureCreated();

// Create a user with todos
var user = new User
{
    Name = "Alice",
    Email = "alice@example.com",
    Todos = new List<Todo>
    {
        new() { Title = "Buy milk" },
        new() { Title = "Call dentist" },
        new() { Title = "Finish project" }
    }
};

context.Users.Add(user);
context.SaveChanges();

Console.WriteLine($"Created user {user.Name} with {user.Todos.Count} todos");

// Load user with their todos
var userWithTodos = context.Users
    .Include(u => u.Todos)
    .FirstOrDefault(u => u.Name == "Alice");

if (userWithTodos != null)
{
    Console.WriteLine($"User: {userWithTodos.Name}");
    foreach (var todo in userWithTodos.Todos)
    {
        Console.WriteLine($"  - {todo.Title}");
    }
}

// Find todos for a specific user
var aliceTodos = context.Todos
    .Include(t => t.User)
    .Where(t => t.User.Name == "Alice")
    .ToList();

// Or using the navigation property
var alice = context.Users
    .Include(u => u.Todos)
    .First(u => u.Name == "Alice");

foreach (var todo in alice.Todos.Where(t => !t.IsComplete))
{
    Console.WriteLine($"  {todo.Title}");
}

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Product
{
    public int Id { get; set; }
    
    [Required]
    [MaxLength(100)]
    public string Name { get; set; } = string.Empty;
    
    [MaxLength(500)]
    public string? Description { get; set; }
    
    [Column(TypeName = "decimal(18,2)")]
    public decimal Price { get; set; }
    
    [Required]
    public string SKU { get; set; } = string.Empty;
}

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseWitDb("Data Source=shop.witdb");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            // Primary key
            entity.HasKey(e => e.Id);
            
            // Required field with max length
            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(100);
            
            // Optional field
            entity.Property(e => e.Description)
                .HasMaxLength(500);
            
            // Decimal precision
            entity.Property(e => e.Price)
                .HasColumnType("decimal(18,2)");
            
            // Unique index
            entity.HasIndex(e => e.SKU)
                .IsUnique();
            
            // Composite index
            entity.HasIndex(e => new { e.Name, e.Price });
        });
    }
}

using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;

// ═══════════════════════════════════════════════════════════════════
// Todo Application - Complete EF Core Example
// ═══════════════════════════════════════════════════════════════════

// Run the application
await RunTodoApp();

async Task RunTodoApp()
{
    await using var context = new TodoDbContext();
    await context.Database.EnsureCreatedAsync();
    
    Console.WriteLine("╔══════════════════════════════════════════════════════════════╗");
    Console.WriteLine("║              Todo Application with EF Core                   ║");
    Console.WriteLine("╚══════════════════════════════════════════════════════════════╝\n");

    // ─────────────────────────────────────────────────────────────────
    // 1. Create users
    // ─────────────────────────────────────────────────────────────────
    if (!await context.Users.AnyAsync())
    {
        var users = new[]
        {
            new User { Name = "Alice", Email = "alice@example.com" },
            new User { Name = "Bob", Email = "bob@example.com" }
        };
        
        context.Users.AddRange(users);
        await context.SaveChangesAsync();
        Console.WriteLine($"✓ Created {users.Length} users\n");
    }

    // ─────────────────────────────────────────────────────────────────
    // 2. Create todos for Alice
    // ─────────────────────────────────────────────────────────────────
    var alice = await context.Users.FirstAsync(u => u.Name == "Alice");
    
    if (!await context.Todos.AnyAsync(t => t.UserId == alice.Id))
    {
        var aliceTodos = new[]
        {
            new Todo { Title = "Review PR #123", Priority = Priority.High, UserId = alice.Id },
            new Todo { Title = "Write documentation", Priority = Priority.Medium, UserId = alice.Id },
            new Todo { Title = "Update dependencies", Priority = Priority.Low, UserId = alice.Id },
            new Todo { Title = "Fix login bug", Priority = Priority.Critical, UserId = alice.Id },
            new Todo { Title = "Team meeting", Priority = Priority.Medium, UserId = alice.Id },
        };
        
        context.Todos.AddRange(aliceTodos);
        await context.SaveChangesAsync();
        Console.WriteLine($"✓ Created {aliceTodos.Length} todos for Alice\n");
    }

    // ─────────────────────────────────────────────────────────────────
    // 3. List all users with todo counts
    // ─────────────────────────────────────────────────────────────────
    Console.WriteLine("Users:");
    Console.WriteLine("─".PadRight(50, '─'));
    
    var usersWithCounts = await context.Users
        .Select(u => new 
        { 
            u.Name, 
            u.Email, 
            TodoCount = u.Todos.Count,
            IncompleteCount = u.Todos.Count(t => !t.IsComplete)
        })
        .ToListAsync();

    foreach (var user in usersWithCounts)
    {
        Console.WriteLine($"  {user.Name} ({user.Email})");
        Console.WriteLine($"    Todos: {user.TodoCount} total, {user.IncompleteCount} incomplete\n");
    }

    // ─────────────────────────────────────────────────────────────────
    // 4. List Alice's todos by priority
    // ─────────────────────────────────────────────────────────────────
    Console.WriteLine("Alice's Todos (by priority):");
    Console.WriteLine("─".PadRight(50, '─'));
    
    var aliceTodosList = await context.Todos
        .Where(t => t.User.Name == "Alice")
        .OrderByDescending(t => t.Priority)
        .ToListAsync();

    foreach (var todo in aliceTodosList)
    {
        var status = todo.IsComplete ? "✓" : "○";
        var priority = todo.Priority.ToString().PadRight(8);
        Console.WriteLine($"  {status} [{priority}] {todo.Title}");
    }

    // ─────────────────────────────────────────────────────────────────
    // 5. Complete the critical bug
    // ─────────────────────────────────────────────────────────────────
    var criticalBug = await context.Todos
        .FirstOrDefaultAsync(t => t.Title.Contains("bug") && t.Priority == Priority.Critical);

    if (criticalBug != null && !criticalBug.IsComplete)
    {
        criticalBug.IsComplete = true;
        criticalBug.CompletedAt = DateTime.UtcNow;
        await context.SaveChangesAsync();
        
        Console.WriteLine($"\n✓ Completed: {criticalBug.Title}");
    }

    // ─────────────────────────────────────────────────────────────────
    // 6. Show statistics
    // ─────────────────────────────────────────────────────────────────
    Console.WriteLine("\nStatistics:");
    Console.WriteLine("─".PadRight(50, '─'));

    var stats = await context.Todos
        .GroupBy(t => t.Priority)
        .Select(g => new { Priority = g.Key, Count = g.Count(), Completed = g.Count(t => t.IsComplete) })
        .OrderByDescending(s => s.Priority)
        .ToListAsync();

    foreach (var stat in stats)
    {
        var pct = stat.Count > 0 ? (stat.Completed * 100 / stat.Count) : 0;
        Console.WriteLine($"  {stat.Priority,-10}: {stat.Completed}/{stat.Count} complete ({pct}%)");
    }

    // ─────────────────────────────────────────────────────────────────
    // 7. Find overdue or high-priority incomplete todos
    // ─────────────────────────────────────────────────────────────────
    Console.WriteLine("\n⚠ Action Required:");
    Console.WriteLine("─".PadRight(50, '─'));

    var actionRequired = await context.Todos
        .Include(t => t.User)
        .Where(t => !t.IsComplete && t.Priority >= Priority.High)
        .OrderByDescending(t => t.Priority)
        .ToListAsync();

    if (actionRequired.Any())
    {
        foreach (var todo in actionRequired)
        {
            Console.WriteLine($"  [{todo.Priority}] {todo.Title} (assigned to {todo.User.Name})");
        }
    }
    else
    {
        Console.WriteLine("  No high-priority items pending!");
    }

    Console.WriteLine("\n✓ Done!");
}

// ═══════════════════════════════════════════════════════════════════
// Entity Classes
// ═══════════════════════════════════════════════════════════════════

public enum Priority
{
    Low = 0,
    Medium = 1,
    High = 2,
    Critical = 3
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    
    public List<Todo> Todos { get; set; } = new();
}

public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string? Description { get; set; }
    public Priority Priority { get; set; } = Priority.Medium;
    public bool IsComplete { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? CompletedAt { get; set; }
    
    public int UserId { get; set; }
    public User User { get; set; } = null!;
}

// ═══════════════════════════════════════════════════════════════════
// DbContext
// ═══════════════════════════════════════════════════════════════════

public class TodoDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Todo> Todos => Set<Todo>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseWitDb("Data Source=todo-app.witdb");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // User configuration
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasIndex(e => e.Email).IsUnique();
            entity.Property(e => e.Name).HasMaxLength(100);
            entity.Property(e => e.Email).HasMaxLength(255);
        });

        // Todo configuration
        modelBuilder.Entity<Todo>(entity =>
        {
            entity.Property(e => e.Title).HasMaxLength(200);
            entity.Property(e => e.Description).HasMaxLength(1000);
            
            // Relationship
            entity.HasOne(t => t.User)
                .WithMany(u => u.Todos)
                .HasForeignKey(t => t.UserId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }
}

╔══════════════════════════════════════════════════════════════╗
║              Todo Application with EF Core                   ║
╚══════════════════════════════════════════════════════════════╝

✓ Created 2 users

✓ Created 5 todos for Alice

Users:
──────────────────────────────────────────────────
  Alice (alice@example.com)
    Todos: 5 total, 5 incomplete

  Bob (bob@example.com)
    Todos: 0 total, 0 incomplete

Alice's Todos (by priority):
──────────────────────────────────────────────────
  ○ [Critical] Fix login bug
  ○ [High    ] Review PR #123
  ○ [Medium  ] Write documentation
  ○ [Medium  ] Team meeting
  ○ [Low     ] Update dependencies

✓ Completed: Fix login bug

Statistics:
──────────────────────────────────────────────────
  Critical  : 1/1 complete (100%)
  High      : 0/1 complete (0%)
  Medium    : 0/2 complete (0%)
  Low       : 0/1 complete (0%)

⚠ Action Required:
──────────────────────────────────────────────────
  [High] Review PR #123 (assigned to Alice)

✓ Done!

// Prefer async methods
var todos = await context.Todos.ToListAsync();
var todo = await context.Todos.FindAsync(id);
var count = await context.Todos.CountAsync();
var exists = await context.Todos.AnyAsync(t => t.Id == id);
var first = await context.Todos.FirstOrDefaultAsync(t => t.Title == "Test");

await context.SaveChangesAsync();
await context.Database.EnsureCreatedAsync();

await foreach (var todo in context.Todos.AsAsyncEnumerable())
{
    // Process one at a time, doesn't load all into memory
    Console.WriteLine(todo.Title);
}

using OutWit.Database.AdoNet;

// Just add Encryption and Password parameters
var connectionString = "Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecretPassword123!";

using var connection = new WitDbConnection(connectionString);
connection.Open();

// Use exactly like an unencrypted database
using var cmd = connection.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Secrets (Id INTEGER PRIMARY KEY, Data TEXT)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO Secrets (Data) VALUES (@data)";
cmd.Parameters.Add(new WitDbParameter("@data", "This is encrypted!"));
cmd.ExecuteNonQuery();

Console.WriteLine("Data saved with AES-256 encryption!");

using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;

public class SecureDbContext : DbContext
{
    public DbSet<Secret> Secrets => Set<Secret>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseWitDb("Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecretPassword123!");
    }
}

public class Secret
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;
}

// Usage
using var context = new SecureDbContext();
context.Database.EnsureCreated();

context.Secrets.Add(new Secret { Name = "API_KEY", Value = "sk-1234567890" });
context.SaveChangesAsync();

using OutWit.Database.Core.Builder;

// Using builder pattern
using var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithBTree()
    .WithEncryption("MySecretPassword123!")
    .Build();

db.Put("secret-key"u8.ToArray(), "secret-value"u8.ToArray());

// Connection string
"Data Source=app.witdb;Encryption=aes-gcm;Password=secret"

// Builder
.WithEncryption("password")  // AES-GCM is default

// Requires: dotnet add package OutWit.Database.Core.BouncyCastle

// Connection string
"Data Source=app.witdb;Encryption=chacha20;Password=secret"

// Builder
using OutWit.Database.Core.BouncyCastle;

var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithBTree()
    .WithBouncyCastleEncryption("password")
    .Build();

// ❌ Bad - too short, predictable
"password"
"123456"
"myapp"

// ✅ Good - long, mixed characters
"Hk9$mP2!xL7@nQ4&"
"correct-horse-battery-staple"
"MyApp-2024-SecureDB-#1"

public class SecureDocumentService
{
    public void CreateDocument(string filePath, string userPassword)
    {
        // Validate password strength
        if (userPassword.Length < 8)
            throw new ArgumentException("Password must be at least 8 characters");
        
        using var db = new WitDatabaseBuilder()
            .WithFilePath(filePath)
            .WithBTree()
            .WithEncryption(userPassword)
            .Build();
        
        // Initialize document structure...
    }
    
    public WitDatabase OpenDocument(string filePath, string userPassword)
    {
        try
        {
            var db = new WitDatabaseBuilder()
                .WithFilePath(filePath)
                .WithBTree()
                .WithEncryption(userPassword)
                .Build();
            
            // Verify we can read (will fail with wrong password)
            db.Get("_meta"u8.ToArray());
            
            return db;
        }
        catch (CryptographicException)
        {
            throw new UnauthorizedAccessException("Invalid password");
        }
    }
}

// ❌ Bad - hardcoded password
var conn = new WitDbConnection("Data Source=app.witdb;Encryption=aes-gcm;Password=hardcoded123");

// ✅ Good - from environment variable
var password = Environment.GetEnvironmentVariable("MYAPP_DB_PASSWORD")
    ?? throw new InvalidOperationException("Database password not configured");
var conn = new WitDbConnection($"Data Source=app.witdb;Encryption=aes-gcm;Password={password}");

// ✅ Good - from configuration
var password = Configuration["Database:Password"];
var conn = new WitDbConnection($"Data Source=app.witdb;Encryption=aes-gcm;Password={password}");

// ✅ Good - from secure storage (Windows DPAPI, macOS Keychain, etc.)
var password = SecureStorage.GetPassword("myapp-db");

// Each user gets a unique encryption key derived from username + password
var connectionString = $"Data Source=user_{userId}.witdb;Encryption=aes-gcm;User={username};Password={password}";

using var connection = new WitDbConnection(connectionString);
connection.Open();

// Builder API equivalent
var db = new WitDatabaseBuilder()
    .WithFilePath($"user_{userId}.witdb")
    .WithBTree()
    .WithEncryption(username, password)  // User + password
    .Build();

// Standard - 100,000 PBKDF2 iterations (secure but slow in WASM)
"Data Source=app.witdb;Encryption=aes-gcm;Password=secret"

// Fast - 10,000 iterations (faster, still secure for most use cases)
"Data Source=app.witdb;Encryption=aes-gcm;Password=secret;Fast Encryption=true"

// Builder API
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithEncryptionFast("password")  // Optimized for WASM
    .Build();

// Generate a 256-bit key (32 bytes)
var key = new byte[32];
RandomNumberGenerator.Fill(key);

// Store this key securely (e.g., Azure Key Vault, AWS KMS)
Console.WriteLine($"Key (base64): {Convert.ToBase64String(key)}");

// Use the key
var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithBTree()
    .WithAesEncryption(key)
    .Build();

using System.Security.Cryptography;
using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;

// ═══════════════════════════════════════════════════════════════════
// Simple Password Manager with Encrypted Storage
// ═══════════════════════════════════════════════════════════════════

Console.WriteLine("╔══════════════════════════════════════════════════════════════╗");
Console.WriteLine("║              Encrypted Password Manager                      ║");
Console.WriteLine("╚══════════════════════════════════════════════════════════════╝\n");

// Get master password from user
Console.Write("Enter master password: ");
var masterPassword = Console.ReadLine() ?? throw new InvalidOperationException("Password required");

// Validate password strength
if (masterPassword.Length < 8)
{
    Console.WriteLine("Error: Password must be at least 8 characters");
    return;
}

try
{
    await using var context = new PasswordVaultContext(masterPassword);
    await context.Database.EnsureCreatedAsync();
    
    Console.WriteLine("✓ Vault unlocked successfully!\n");

    // Add some passwords
    if (!await context.Credentials.AnyAsync())
    {
        var credentials = new[]
        {
            new Credential 
            { 
                Site = "github.com", 
                Username = "myuser", 
                Password = GeneratePassword(16),
                Notes = "Main development account"
            },
            new Credential 
            { 
                Site = "aws.amazon.com", 
                Username = "admin@company.com", 
                Password = GeneratePassword(20),
                Notes = "Production AWS account"
            },
            new Credential 
            { 
                Site = "database.example.com", 
                Username = "dbadmin", 
                Password = GeneratePassword(24),
                Notes = "PostgreSQL admin"
            }
        };
        
        context.Credentials.AddRange(credentials);
        await context.SaveChangesAsync();
        Console.WriteLine($"✓ Added {credentials.Length} sample credentials\n");
    }

    // List all credentials
    Console.WriteLine("Stored Credentials:");
    Console.WriteLine("─".PadRight(70, '─'));
    
    var allCredentials = await context.Credentials
        .OrderBy(c => c.Site)
        .ToListAsync();

    foreach (var cred in allCredentials)
    {
        Console.WriteLine($"  Site:     {cred.Site}");
        Console.WriteLine($"  Username: {cred.Username}");
        Console.WriteLine($"  Password: {MaskPassword(cred.Password)}");
        if (!string.IsNullOrEmpty(cred.Notes))
            Console.WriteLine($"  Notes:    {cred.Notes}");
        Console.WriteLine();
    }

    // Show a specific password
    Console.Write("Show password for site (or press Enter to skip): ");
    var searchSite = Console.ReadLine();
    
    if (!string.IsNullOrWhiteSpace(searchSite))
    {
        var found = await context.Credentials
            .FirstOrDefaultAsync(c => c.Site.Contains(searchSite, StringComparison.OrdinalIgnoreCase));
        
        if (found != null)
        {
            Console.WriteLine($"\n✓ Password for {found.Site}: {found.Password}");
        }
        else
        {
            Console.WriteLine($"\n✗ No credential found for '{searchSite}'");
        }
    }

    Console.WriteLine("\n✓ Vault locked. Data remains encrypted on disk.");
}
catch (Exception ex) when (ex.Message.Contains("decrypt") || ex.Message.Contains("authentication"))
{
    Console.WriteLine("\n✗ Error: Invalid master password or corrupted vault.");
}

// ═══════════════════════════════════════════════════════════════════
// Helper Methods
// ═══════════════════════════════════════════════════════════════════

static string GeneratePassword(int length)
{
    const string chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*";
    var password = new char[length];
    var random = RandomNumberGenerator.Create();
    var bytes = new byte[length];
    random.GetBytes(bytes);
    
    for (int i = 0; i < length; i++)
        password[i] = chars[bytes[i] % chars.Length];
    
    return new string(password);
}

static string MaskPassword(string password)
{
    if (password.Length <= 4)
        return new string('*', password.Length);
    return password[..2] + new string('*', password.Length - 4) + password[^2..];
}

// ═══════════════════════════════════════════════════════════════════
// Entity Classes
// ═══════════════════════════════════════════════════════════════════

public class Credential
{
    public int Id { get; set; }
    public string Site { get; set; } = string.Empty;
    public string Username { get; set; } = string.Empty;
    public string Password { get; set; } = string.Empty;
    public string? Notes { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? ModifiedAt { get; set; }
}

// ═══════════════════════════════════════════════════════════════════
// DbContext with Encryption
// ═══════════════════════════════════════════════════════════════════

public class PasswordVaultContext : DbContext
{
    private readonly string _masterPassword;

    public PasswordVaultContext(string masterPassword)
    {
        _masterPassword = masterPassword;
    }

    public DbSet<Credential> Credentials => Set<Credential>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        // AES-256-GCM encryption with master password
        options.UseWitDb($"Data Source=vault.witdb;Encryption=aes-gcm;Password={_masterPassword}");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Credential>(entity =>
        {
            entity.HasIndex(e => e.Site);
            entity.Property(e => e.Site).HasMaxLength(255);
            entity.Property(e => e.Username).HasMaxLength(255);
            entity.Property(e => e.Password).HasMaxLength(500);
            entity.Property(e => e.Notes).HasMaxLength(2000);
        });
    }
}

╔══════════════════════════════════════════════════════════════╗
║              Encrypted Password Manager                      ║
╚══════════════════════════════════════════════════════════════╝

Enter master password: MySecureVault123!
✓ Vault unlocked successfully!

✓ Added 3 sample credentials

Stored Credentials:
──────────────────────────────────────────────────────────────────────
  Site:     aws.amazon.com
  Username: admin@company.com
  Password: Kx**************9#
  Notes:    Production AWS account

  Site:     database.example.com
  Username: dbadmin
  Password: 7Q**********************&m
  Notes:    PostgreSQL admin

  Site:     github.com
  Username: myuser
  Password: Np************@3
  Notes:    Main development account

Show password for site (or press Enter to skip): github
✓ Password for github.com: NpK$8mL2xQ#4nJ@3

✓ Vault locked. Data remains encrypted on disk.

// Create encrypted database
var db1 = new WitDatabaseBuilder()
    .WithFilePath("encrypted.witdb")
    .WithBTree()
    .WithEncryption("correct-password")
    .Build();

db1.Put("key"u8.ToArray(), "secret value"u8.ToArray());
db1.Dispose();

// Try to open without password - should fail
try
{
    var db2 = new WitDatabaseBuilder()
        .WithFilePath("encrypted.witdb")
        .WithBTree()
        // No encryption!
        .Build();
    
    var value = db2.Get("key"u8.ToArray());
    Console.WriteLine("ERROR: Database opened without password!");
}
catch (Exception ex)
{
    Console.WriteLine($"✓ Correctly failed: {ex.Message}");
}

// Try with wrong password - should fail
try
{
    var db3 = new WitDatabaseBuilder()
        .WithFilePath("encrypted.witdb")
        .WithBTree()
        .WithEncryption("wrong-password")
        .Build();
    
    var value = db3.Get("key"u8.ToArray());
    Console.WriteLine("ERROR: Database opened with wrong password!");
}
catch (Exception ex)
{
    Console.WriteLine($"✓ Correctly failed: {ex.Message}");
}

// ❌ Without transaction - DANGEROUS!
account1.Balance -= 100;  // Debit
SaveChanges();
// What if the app crashes here?
account2.Balance += 100;  // Credit
SaveChanges();

// ✅ With transaction - SAFE
BeginTransaction();
account1.Balance -= 100;  // Debit
account2.Balance += 100;  // Credit
Commit();  // Both happen, or neither happens

using OutWit.Database.AdoNet;

using var connection = new WitDbConnection("Data Source=bank.witdb");
connection.Open();

// Create accounts table
using var setupCmd = connection.CreateCommand();
setupCmd.CommandText = """
    CREATE TABLE IF NOT EXISTS Accounts (
        Id INTEGER PRIMARY KEY,
        Name VARCHAR(100) NOT NULL,
        Balance DECIMAL(18,2) NOT NULL DEFAULT 0
    )
    """;
setupCmd.ExecuteNonQuery();

// Begin transaction
using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;  // Associate command with transaction
    
    // Debit from account 1
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
    cmd.Parameters.Add(new WitDbParameter("@amount", 100.00m));
    cmd.Parameters.Add(new WitDbParameter("@id", 1));
    cmd.ExecuteNonQuery();
    
    // Credit to account 2
    cmd.Parameters.Clear();
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
    cmd.Parameters.Add(new WitDbParameter("@amount", 100.00m));
    cmd.Parameters.Add(new WitDbParameter("@id", 2));
    cmd.ExecuteNonQuery();
    
    // All succeeded - commit
    transaction.Commit();
    Console.WriteLine("Transfer complete!");
}
catch (Exception ex)
{
    // Something failed - rollback all changes
    transaction.Rollback();
    Console.WriteLine($"Transfer failed: {ex.Message}");
}

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Check source account has sufficient funds
    cmd.CommandText = "SELECT Balance FROM Accounts WHERE Id = @id";
    cmd.Parameters.Add(new WitDbParameter("@id", sourceAccountId));
    var balance = Convert.ToDecimal(cmd.ExecuteScalar());
    
    if (balance < transferAmount)
    {
        throw new InvalidOperationException("Insufficient funds");
    }
    
    // Perform transfer
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new WitDbParameter("@amount", transferAmount));
    cmd.Parameters.Add(new WitDbParameter("@id", sourceAccountId));
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new WitDbParameter("@amount", transferAmount));
    cmd.Parameters.Add(new WitDbParameter("@id", targetAccountId));
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

using var connection = new WitDbConnection("Data Source=bulk.witdb");
connection.Open();

// Without transaction: ~1000 disk writes
// With transaction: 1 disk write at commit

using var transaction = connection.BeginTransaction();

using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = "INSERT INTO Items (Name, Value) VALUES (@name, @value)";

var nameParam = new WitDbParameter("@name", "");
var valueParam = new WitDbParameter("@value", 0);
cmd.Parameters.Add(nameParam);
cmd.Parameters.Add(valueParam);
cmd.Prepare();

var stopwatch = System.Diagnostics.Stopwatch.StartNew();

for (int i = 0; i < 10000; i++)
{
    nameParam.Value = $"Item {i}";
    valueParam.Value = i * 10;
    cmd.ExecuteNonQuery();
}

transaction.Commit();
stopwatch.Stop();

Console.WriteLine($"Inserted 10,000 rows in {stopwatch.ElapsedMilliseconds}ms");
// Typically 50-100ms with transaction vs 5-10 seconds without

using var context = new AppDbContext();

// All changes are in a single implicit transaction
context.Accounts.Add(new Account { Name = "Alice", Balance = 1000 });
context.Accounts.Add(new Account { Name = "Bob", Balance = 500 });

// Both inserts succeed or both fail
await context.SaveChangesAsync();

using var context = new BankDbContext();

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Find accounts
    var source = await context.Accounts.FindAsync(sourceId);
    var target = await context.Accounts.FindAsync(targetId);
    
    if (source == null || target == null)
        throw new InvalidOperationException("Account not found");
    
    if (source.Balance < amount)
        throw new InvalidOperationException("Insufficient funds");
    
    // Perform transfer
    source.Balance -= amount;
    target.Balance += amount;
    
    // Log the transaction
    context.TransactionLogs.Add(new TransactionLog
    {
        FromAccountId = sourceId,
        ToAccountId = targetId,
        Amount = amount,
        Timestamp = DateTime.UtcNow
    });
    
    await context.SaveChangesAsync();
    
    // Commit both the transfer and the log
    await transaction.CommitAsync();
    
    Console.WriteLine($"Transferred {amount:C} from {source.Name} to {target.Name}");
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

var strategy = context.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    using var transaction = await context.Database.BeginTransactionAsync();
    
    try
    {
        // Your operations here
        source.Balance -= amount;
        target.Balance += amount;
        
        await context.SaveChangesAsync();
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
});

using OutWit.Database.Core.Builder;

using var db = WitDatabase.CreateOrOpen("app.witdb");

using var tx = db.BeginTransaction();

try
{
    tx.Put("key1"u8.ToArray(), "value1"u8.ToArray());
    tx.Put("key2"u8.ToArray(), "value2"u8.ToArray());
    tx.Delete("old-key"u8.ToArray());
    
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

await using var tx = await db.BeginTransactionAsync();

try
{
    await tx.PutAsync(key1, value1);
    await tx.PutAsync(key2, value2);
    
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Step 1: Create order
    cmd.CommandText = "INSERT INTO Orders (CustomerId, Total) VALUES (@custId, @total) RETURNING Id";
    cmd.Parameters.Add(new WitDbParameter("@custId", customerId));
    cmd.Parameters.Add(new WitDbParameter("@total", 0));
    var orderId = Convert.ToInt32(cmd.ExecuteScalar());
    
    Console.WriteLine($"Created order {orderId}");
    
    // Create savepoint after order creation
    transaction.Save("after_order");
    
    decimal orderTotal = 0;
    
    foreach (var item in cartItems)
    {
        // Create savepoint before each item
        transaction.Save("before_item");
        
        try
        {
            // Check inventory
            cmd.CommandText = "SELECT Stock FROM Products WHERE Id = @productId";
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new WitDbParameter("@productId", item.ProductId));
            var stock = Convert.ToInt32(cmd.ExecuteScalar());
            
            if (stock < item.Quantity)
            {
                Console.WriteLine($"  Skipping {item.ProductName}: insufficient stock");
                transaction.Rollback("before_item");  // Rollback just this item
                continue;
            }
            
            // Add order item
            cmd.CommandText = """
                INSERT INTO OrderItems (OrderId, ProductId, Quantity, Price) 
                VALUES (@orderId, @productId, @qty, @price)
                """;
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new WitDbParameter("@orderId", orderId));
            cmd.Parameters.Add(new WitDbParameter("@productId", item.ProductId));
            cmd.Parameters.Add(new WitDbParameter("@qty", item.Quantity));
            cmd.Parameters.Add(new WitDbParameter("@price", item.Price));
            cmd.ExecuteNonQuery();
            
            // Update inventory
            cmd.CommandText = "UPDATE Products SET Stock = Stock - @qty WHERE Id = @productId";
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new WitDbParameter("@qty", item.Quantity));
            cmd.Parameters.Add(new WitDbParameter("@productId", item.ProductId));
            cmd.ExecuteNonQuery();
            
            orderTotal += item.Price * item.Quantity;
            Console.WriteLine($"  Added {item.ProductName} x{item.Quantity}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"  Error with {item.ProductName}: {ex.Message}");
            transaction.Rollback("before_item");
        }
    }
    
    if (orderTotal == 0)
    {
        // No items added - rollback entire order
        transaction.Rollback("after_order");
        Console.WriteLine("Order cancelled - no items available");
    }
    else
    {
        // Update order total
        cmd.CommandText = "UPDATE Orders SET Total = @total WHERE Id = @orderId";
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@total", orderTotal));
        cmd.Parameters.Add(new WitDbParameter("@orderId", orderId));
        cmd.ExecuteNonQuery();
        
        transaction.Commit();
        Console.WriteLine($"Order {orderId} completed: {orderTotal:C}");
    }
}
catch
{
    transaction.Rollback();
    throw;
}

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Create order
    var order = new Order { CustomerId = customerId };
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    
    // Savepoint after order creation
    await transaction.CreateSavepointAsync("after_order");
    
    foreach (var item in cartItems)
    {
        await transaction.CreateSavepointAsync("before_item");
        
        try
        {
            var product = await context.Products.FindAsync(item.ProductId);
            
            if (product == null || product.Stock < item.Quantity)
            {
                await transaction.RollbackToSavepointAsync("before_item");
                continue;
            }
            
            order.Items.Add(new OrderItem
            {
                ProductId = item.ProductId,
                Quantity = item.Quantity,
                Price = item.Price
            });
            
            product.Stock -= item.Quantity;
            await context.SaveChangesAsync();
        }
        catch
        {
            await transaction.RollbackToSavepointAsync("before_item");
        }
    }
    
    if (!order.Items.Any())
    {
        await transaction.RollbackToSavepointAsync("after_order");
        throw new InvalidOperationException("No items could be added");
    }
    
    order.Total = order.Items.Sum(i => i.Price * i.Quantity);
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

using System.Data;

// Specify isolation level when beginning transaction
using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;

// All reads see a consistent snapshot of data
cmd.CommandText = "SELECT * FROM Accounts";
// ... reads see data as of transaction start

transaction.Commit();

using var transaction = await context.Database
    .BeginTransactionAsync(IsolationLevel.Snapshot);

// All queries in this transaction see consistent snapshot
var accounts = await context.Accounts.ToListAsync();

await transaction.CommitAsync();

// Connection string
"Data Source=app.witdb;Isolation Level=Snapshot"

// Builder
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .WithDefaultIsolationLevel(IsolationLevel.Snapshot)
    .Build();

Time →

Writer Transaction:                  Reader Transaction:
─────────────────────────────────   ─────────────────────────────────
BEGIN                               
UPDATE Account SET Balance=900      BEGIN
                                    SELECT Balance FROM Account
                                    → Returns 1000 (old version)
COMMIT                              
                                    SELECT Balance FROM Account  
                                    → Still returns 1000 (snapshot)
                                    COMMIT
                                    
                                    BEGIN (new transaction)
                                    SELECT Balance FROM Account
                                    → Returns 900 (committed value)

// Connection string (enabled by default)
"Data Source=app.witdb;MVCC=true"

// Builder
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .WithMvcc()
    .Build();

using System.Data;
using OutWit.Database.AdoNet;

// ═══════════════════════════════════════════════════════════════════
// Bank Transfer System - Transaction Example
// ═══════════════════════════════════════════════════════════════════

using var connection = new WitDbConnection("Data Source=bank.witdb");
connection.Open();

// Setup database
InitializeDatabase(connection);

Console.WriteLine("╔══════════════════════════════════════════════════════════════╗");
Console.WriteLine("║              Bank Transfer System                            ║");
Console.WriteLine("╚══════════════════════════════════════════════════════════════╝\n");

// Show initial balances
ShowBalances(connection);

// Perform some transfers
Console.WriteLine("\nPerforming transfers:");
Console.WriteLine("─".PadRight(50, '─'));

Transfer(connection, "Alice", "Bob", 200);
Transfer(connection, "Bob", "Charlie", 50);
Transfer(connection, "Charlie", "Alice", 75);

// This should fail - insufficient funds
Transfer(connection, "Alice", "Bob", 10000);

// Show final balances
Console.WriteLine("\nFinal balances:");
ShowBalances(connection);

// Show transaction history
ShowHistory(connection);

// ═══════════════════════════════════════════════════════════════════
// Helper Methods
// ═══════════════════════════════════════════════════════════════════

static void InitializeDatabase(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    
    cmd.CommandText = """
        CREATE TABLE IF NOT EXISTS Accounts (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Name VARCHAR(100) NOT NULL UNIQUE,
            Balance DECIMAL(18,2) NOT NULL DEFAULT 0 CHECK (Balance >= 0)
        )
        """;
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = """
        CREATE TABLE IF NOT EXISTS Transactions (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            FromAccount VARCHAR(100),
            ToAccount VARCHAR(100),
            Amount DECIMAL(18,2) NOT NULL,
            Status VARCHAR(20) NOT NULL,
            Timestamp DATETIME DEFAULT NOW(),
            ErrorMessage TEXT
        )
        """;
    cmd.ExecuteNonQuery();
    
    // Seed accounts if empty
    cmd.CommandText = "SELECT COUNT(*) FROM Accounts";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
    {
        cmd.CommandText = """
            INSERT INTO Accounts (Name, Balance) VALUES 
            ('Alice', 1000.00),
            ('Bob', 500.00),
            ('Charlie', 250.00)
            """;
        cmd.ExecuteNonQuery();
    }
}

static void Transfer(WitDbConnection conn, string fromName, string toName, decimal amount)
{
    using var transaction = conn.BeginTransaction(IsolationLevel.Serializable);
    
    try
    {
        using var cmd = conn.CreateCommand();
        cmd.Transaction = transaction;
        
        // Get source account
        cmd.CommandText = "SELECT Id, Balance FROM Accounts WHERE Name = @name";
        cmd.Parameters.Add(new WitDbParameter("@name", fromName));
        
        int fromId;
        decimal fromBalance;
        
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
                throw new InvalidOperationException($"Account '{fromName}' not found");
            fromId = reader.GetInt32(0);
            fromBalance = reader.GetDecimal(1);
        }
        
        // Get target account
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@name", toName));
        
        int toId;
        
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
                throw new InvalidOperationException($"Account '{toName}' not found");
            toId = reader.GetInt32(0);
        }
        
        // Check sufficient funds
        if (fromBalance < amount)
        {
            throw new InvalidOperationException(
                $"Insufficient funds: {fromName} has {fromBalance:C}, needs {amount:C}");
        }
        
        // Debit source
        cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@amount", amount));
        cmd.Parameters.Add(new WitDbParameter("@id", fromId));
        cmd.ExecuteNonQuery();
        
        // Credit target
        cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new WitDbParameter("@amount", amount));
        cmd.Parameters.Add(new WitDbParameter("@id", toId));
        cmd.ExecuteNonQuery();
        
        // Log successful transaction
        LogTransaction(cmd, fromName, toName, amount, "SUCCESS", null);
        
        transaction.Commit();
        Console.WriteLine($"  ✓ {fromName} → {toName}: {amount:C}");
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        
        // Log failed transaction (in a new transaction)
        using var logCmd = conn.CreateCommand();
        LogTransaction(logCmd, fromName, toName, amount, "FAILED", ex.Message);
        
        Console.WriteLine($"  ✗ {fromName} → {toName}: {amount:C} - {ex.Message}");
    }
}

static void LogTransaction(WitDbCommand cmd, string from, string to, decimal amount, string status, string? error)
{
    cmd.CommandText = """
        INSERT INTO Transactions (FromAccount, ToAccount, Amount, Status, ErrorMessage)
        VALUES (@from, @to, @amount, @status, @error)
        """;
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new WitDbParameter("@from", from));
    cmd.Parameters.Add(new WitDbParameter("@to", to));
    cmd.Parameters.Add(new WitDbParameter("@amount", amount));
    cmd.Parameters.Add(new WitDbParameter("@status", status));
    cmd.Parameters.Add(new WitDbParameter("@error", error ?? (object)DBNull.Value));
    cmd.ExecuteNonQuery();
}

static void ShowBalances(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT Name, Balance FROM Accounts ORDER BY Name";
    
    Console.WriteLine("Account Balances:");
    Console.WriteLine("─".PadRight(30, '─'));
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"  {reader["Name"],-15} {reader.GetDecimal(1),12:C}");
    }
}

static void ShowHistory(WitDbConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = """
        SELECT FromAccount, ToAccount, Amount, Status, Timestamp 
        FROM Transactions 
        ORDER BY Timestamp DESC 
        LIMIT 10
        """;
    
    Console.WriteLine("\nRecent Transactions:");
    Console.WriteLine("─".PadRight(70, '─'));
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        var status = reader["Status"].ToString() == "SUCCESS" ? "✓" : "✗";
        var timestamp = reader.GetDateTime(4).ToString("HH:mm:ss");
        Console.WriteLine($"  {status} [{timestamp}] {reader["FromAccount"]} → {reader["ToAccount"]}: {reader.GetDecimal(2):C}");
    }
}

╔══════════════════════════════════════════════════════════════╗
║              Bank Transfer System                            ║
╚══════════════════════════════════════════════════════════════╝

Account Balances:
──────────────────────────────
  Alice              $1,000.00
  Bob                  $500.00
  Charlie              $250.00

Performing transfers:
──────────────────────────────────────────────────
  ✓ Alice → Bob: $200.00
  ✓ Bob → Charlie: $50.00
  ✓ Charlie → Alice: $75.00
  ✗ Alice → Bob: $10,000.00 - Insufficient funds: Alice has $875.00, needs $10,000.00

Final balances:
Account Balances:
──────────────────────────────
  Alice                $875.00
  Bob                  $650.00
  Charlie              $225.00

Recent Transactions:
──────────────────────────────────────────────────────────────────────
  ✗ [14:32:01] Alice → Bob: $10,000.00
  ✓ [14:32:01] Charlie → Alice: $75.00
  ✓ [14:32:01] Bob → Charlie: $50.00
  ✓ [14:32:01] Alice → Bob: $200.00

// Simple file database
"Data Source=app.witdb"

// In-memory (testing)
"Data Source=:memory:"

// With encryption
"Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecret123!"

// ChaCha20 for Blazor WASM
"Data Source=app.witdb;Encryption=chacha20;Password=secret"

// Read-only mode
"Data Source=app.witdb;Mode=ReadOnly"

// With all options
"Data Source=app.witdb;Store=btree;Encryption=aes-gcm;Password=secret;MVCC=true;Isolation Level=Snapshot"

using OutWit.Database.Core.Builder;

// Create / Open
using var db = WitDatabase.CreateInMemory();
using var db = WitDatabase.Create("app.witdb");
using var db = WitDatabase.Open("app.witdb");
using var db = WitDatabase.CreateOrOpen("app.witdb");
using var db = WitDatabase.CreateOrOpen("app.witdb", "password");

// Builder pattern
using var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()                    // or .WithLsmTree()
    .WithEncryption("password")     // or .WithBouncyCastleEncryption()
    .WithTransactions()
    .WithMvcc()
    .Build();

// Basic operations
db.Put(key, value);                 // Store
var value = db.Get(key);            // Retrieve (null if not found)
db.Delete(key);                     // Remove
bool exists = db.Exists(key);       // Check existence

// Async versions
await db.PutAsync(key, value);
var value = await db.GetAsync(key);
await db.DeleteAsync(key);

// Scanning
foreach (var (k, v) in db.Scan(startKey, endKey))
{
    // Process key-value pairs in order
}

// Transactions
using var tx = db.BeginTransaction();
tx.Put(key1, value1);
tx.Put(key2, value2);
tx.Commit();  // or tx.Rollback();

using OutWit.Database.AdoNet;

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();
// ... use connection
// Automatically closed and disposed

using var cmd = connection.CreateCommand();

// Non-query (CREATE, INSERT, UPDATE, DELETE)
cmd.CommandText = "CREATE TABLE Users (Id INTEGER PRIMARY KEY, Name TEXT)";
int rowsAffected = cmd.ExecuteNonQuery();

// Scalar (single value)
cmd.CommandText = "SELECT COUNT(*) FROM Users";
var count = cmd.ExecuteScalar();

// Reader (multiple rows)
cmd.CommandText = "SELECT * FROM Users";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    var id = reader.GetInt32(0);
    var name = reader["Name"].ToString();
}

cmd.CommandText = "INSERT INTO Users (Name, Email) VALUES (@name, @email)";
cmd.Parameters.Add(new WitDbParameter("@name", "Alice"));
cmd.Parameters.Add(new WitDbParameter("@email", "alice@example.com"));
cmd.ExecuteNonQuery();

// Clear for reuse
cmd.Parameters.Clear();

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2";
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

await connection.OpenAsync();

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Users";

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    // Process row
}

using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseWitDb("Data Source=app.witdb");
}

// With DI (ASP.NET Core)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=app.witdb"));

using var context = new AppDbContext();
context.Database.EnsureCreated();

// Create
context.Users.Add(new User { Name = "Alice" });
await context.SaveChangesAsync();

// Read
var users = await context.Users.ToListAsync();
var user = await context.Users.FindAsync(1);
var alice = await context.Users.FirstOrDefaultAsync(u => u.Name == "Alice");

// Update
user.Name = "Alice Smith";
await context.SaveChangesAsync();

// Delete
context.Users.Remove(user);
await context.SaveChangesAsync();

// Filter
var active = context.Users.Where(u => u.IsActive);

// Sort
var sorted = context.Users.OrderBy(u => u.Name);
var sortedDesc = context.Users.OrderByDescending(u => u.CreatedAt);

// Select specific columns
var names = context.Users.Select(u => new { u.Id, u.Name });

// Pagination
var page = context.Users
    .OrderBy(u => u.Id)
    .Skip(20)
    .Take(10);

// Aggregates
var count = await context.Users.CountAsync();
var any = await context.Users.AnyAsync(u => u.IsAdmin);
var max = await context.Users.MaxAsync(u => u.Age);

// Include related data
var usersWithPosts = context.Users
    .Include(u => u.Posts)
    .ToList();

// Group
var byCountry = context.Users
    .GroupBy(u => u.Country)
    .Select(g => new { Country = g.Key, Count = g.Count() });

// Implicit (single SaveChanges)
context.Users.Add(user1);
context.Users.Add(user2);
await context.SaveChangesAsync();  // All or nothing

// Explicit
using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // Multiple operations
    await context.SaveChangesAsync();
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

-- Create table
CREATE TABLE Users (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Age INTEGER DEFAULT 0,
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT NOW()
);

-- Create index
CREATE INDEX idx_users_email ON Users(Email);
CREATE UNIQUE INDEX idx_users_name ON Users(Name);

-- Drop
DROP TABLE IF EXISTS Users;
DROP INDEX IF EXISTS idx_users_email;

-- Alter (limited support)
ALTER TABLE Users ADD COLUMN Phone VARCHAR(20);

-- Insert
INSERT INTO Users (Name, Email) VALUES ('Alice', 'alice@example.com');
INSERT INTO Users (Name, Email) VALUES ('Bob', 'bob@example.com') RETURNING Id;

-- Select
SELECT * FROM Users;
SELECT Id, Name FROM Users WHERE IsActive = TRUE ORDER BY Name LIMIT 10;
SELECT COUNT(*), Country FROM Users GROUP BY Country HAVING COUNT(*) > 5;

-- Update
UPDATE Users SET Name = 'Alice Smith' WHERE Id = 1;
UPDATE Users SET IsActive = FALSE WHERE LastLogin < '2024-01-01' RETURNING Id, Name;

-- Delete
DELETE FROM Users WHERE Id = 1;
DELETE FROM Users WHERE IsActive = FALSE RETURNING Id;

-- Upsert
INSERT INTO Users (Id, Name) VALUES (1, 'Alice')
ON CONFLICT(Id) DO UPDATE SET Name = excluded.Name;

-- Inner join
SELECT u.Name, p.Title 
FROM Users u 
INNER JOIN Posts p ON u.Id = p.UserId;

-- Left join
SELECT u.Name, COUNT(p.Id) as PostCount
FROM Users u
LEFT JOIN Posts p ON u.Id = p.UserId
GROUP BY u.Id;

-- String
LENGTH(str), UPPER(str), LOWER(str), TRIM(str)
SUBSTR(str, start, length), REPLACE(str, old, new)
CONCAT(str1, str2), INSTR(str, substr)

-- Numeric
ABS(n), ROUND(n, decimals), CEIL(n), FLOOR(n)
MIN(n), MAX(n), SUM(n), AVG(n), COUNT(*)

-- Date/Time
NOW(), DATE('now'), TIME('now')
STRFTIME('%Y-%m-%d', datetime_col)

-- Null handling
COALESCE(val1, val2, ...), IFNULL(val, default), NULLIF(val1, val2)

-- Conditional
CASE WHEN condition THEN result ELSE default END
IIF(condition, true_val, false_val)

try
{
    using var connection = new WitDbConnection(connectionString);
    connection.Open();
    // ... operations
}
catch (WitDbException ex) when (ex.Message.Contains("locked"))
{
    // Database is locked by another process
    Console.WriteLine("Database is busy, try again later");
}
catch (WitDbException ex) when (ex.Message.Contains("decrypt"))
{
    // Wrong password
    Console.WriteLine("Invalid password");
}
catch (WitDbException ex)
{
    // Other database errors
    Console.WriteLine($"Database error: {ex.Message}");
}

public class UserServiceTests
{
    private AppDbContext CreateTestContext()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDb("Data Source=:memory:")
            .Options;
        
        var context = new AppDbContext(options);
        context.Database.EnsureCreated();
        return context;
    }

    [Fact]
    public async Task CreateUser_ShouldAssignId()
    {
        // Arrange
        using var context = CreateTestContext();
        var service = new UserService(context);
        
        // Act
        var user = await service.CreateUserAsync("Alice", "alice@test.com");
        
        // Assert
        Assert.True(user.Id > 0);
        Assert.Equal("Alice", user.Name);
    }
}

public interface IRepository<T> where T : class
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(T entity);
}

public class Repository<T> : IRepository<T> where T : class
{
    private readonly AppDbContext _context;
    private readonly DbSet<T> _dbSet;

    public Repository(AppDbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public async Task<T?> GetByIdAsync(int id) 
        => await _dbSet.FindAsync(id);

    public async Task<IEnumerable<T>> GetAllAsync() 
        => await _dbSet.ToListAsync();

    public async Task AddAsync(T entity)
    {
        await _dbSet.AddAsync(entity);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateAsync(T entity)
    {
        _dbSet.Update(entity);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteAsync(T entity)
    {
        _dbSet.Remove(entity);
        await _context.SaveChangesAsync();
    }
}