use Stoolap\Database;

$db = Database::open(':memory:');

$db->exec('
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT
    )
');

// Insert with positional parameters ($1, $2, ...)
$db->execute(
    'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
    [1, 'Alice', '[email protected]']
);

// Insert with named parameters (:key)
$db->execute(
    'INSERT INTO users (id, name, email) VALUES (:id, :name, :email)',
    ['id' => 2, 'name' => 'Bob', 'email' => '[email protected]']
);

// Query rows as associative arrays
$users = $db->query('SELECT * FROM users ORDER BY id');
// [['id' => 1, 'name' => 'Alice', 'email' => '[email protected]'], ...]

// Query single row (LIMIT 1 is auto-injected when not present)
$user = $db->queryOne('SELECT * FROM users WHERE id = $1', [1]);
// ['id' => 1, 'name' => 'Alice', 'email' => '[email protected]']

// Query in raw columnar format (faster, no per-row key creation)
$raw = $db->queryRaw('SELECT id, name FROM users ORDER BY id');
// ['columns' => ['id', 'name'], 'rows' => [[1, 'Alice'], [2, 'Bob']]]

$db->close();

// In-memory
$db = Database::open(':memory:');
$db = Database::open('');
$db = Database::openInMemory();

// File-based (data persists across restarts)
$db = Database::open('./mydata');
$db = Database::open('file:///absolute/path/to/db');

$db->executeBatch(
    'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
    [
        [1, 'Alice', '[email protected]'],
        [2, 'Bob', '[email protected]'],
        [3, 'Charlie', '[email protected]'],
    ]
);
// Returns total affected rows (3)

$db = Database::open('./mydata');

$db->exec('CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)');
$db->execute('INSERT INTO kv VALUES ($1, $2)', ['hello', 'world']);
$db->close();

// Reopen - data is still there
$db2 = Database::open('./mydata');
$row = $db2->queryOne('SELECT * FROM kv WHERE key = $1', ['hello']);
// ['key' => 'hello', 'value' => 'world']
$db2->close();

// Maximum durability: fsync on every write
$db = Database::open('./mydata?sync_mode=full');

// High throughput: no fsync, data durable at checkpoint
$db = Database::open('./mydata?sync_mode=none');

// Custom checkpoint interval with compression
$db = Database::open('./mydata?checkpoint_interval=60&compression=on');

// Multiple options
$db = Database::open(
    './mydata?sync_mode=normal&checkpoint_interval=120&compact_threshold=4'
);

$raw = $db->queryRaw('SELECT id, name, email FROM users ORDER BY id');
// $raw['columns'] => ['id', 'name', 'email']
// $raw['rows']    => [[1, 'Alice', '[email protected]'], [2, 'Bob', '[email protected]']]

$insert = $db->prepare('INSERT INTO users VALUES ($1, $2, $3)');
$insert->execute([1, 'Alice', '[email protected]']);
$insert->execute([2, 'Bob', '[email protected]']);

$lookup = $db->prepare('SELECT * FROM users WHERE id = $1');
$user = $lookup->queryOne([1]);
// ['id' => 1, 'name' => 'Alice', 'email' => '[email protected]']

$tx = $db->begin();
try {
    $tx->execute(
        'INSERT INTO users VALUES ($1, $2, $3)',
        [1, 'Alice', '[email protected]']
    );
    $tx->execute(
        'INSERT INTO users VALUES ($1, $2, $3)',
        [2, 'Bob', '[email protected]']
    );

    // Read within the transaction (sees uncommitted changes)
    $rows = $tx->query('SELECT * FROM users');
    $one = $tx->queryOne('SELECT * FROM users WHERE id = $1', [1]);
    $raw = $tx->queryRaw('SELECT id, name FROM users');

    $tx->commit();
} catch (\Exception $e) {
    $tx->rollback();
    throw $e;
}

$tx = $db->begin();
$tx->executeBatch(
    'INSERT INTO users VALUES ($1, $2, $3)',
    [
        [1, 'Alice', '[email protected]'],
        [2, 'Bob', '[email protected]'],
    ]
);
$tx->commit(); // or $tx->rollback() to undo

// Positional ($1, $2, ...)
$db->query('SELECT * FROM users WHERE id = $1 AND name = $2', [1, 'Alice']);

// Named (:key)
$db->query(
    'SELECT * FROM users WHERE id = :id AND name = :name',
    ['id' => 1, 'name' => 'Alice']
);

use Stoolap\StoolapException;

try {
    $db->execute('INSERT INTO users VALUES ($1, $2)', [1, null]); // NOT NULL violation
} catch (StoolapException $e) {
    echo $e->getMessage();
}