<?php
const STARTING_CREDITS = 3;

function getDbConnection(): PDO
{
    static $pdo = null;

    if ($pdo === null) {
        $dbPath = __DIR__ . '/../app.sqlite';
        $pdo = new PDO('sqlite:' . $dbPath);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->exec('PRAGMA foreign_keys = ON');
        $pdo->exec('PRAGMA busy_timeout = 5000');
        // WAL lets readers proceed without blocking a writer's commit (and vice versa),
        // which the default rollback-journal mode doesn't: two concurrent requests each
        // holding open, unfetched-to-completion statement handles could otherwise make a
        // writer's COMMIT fail with "database is locked" even within the busy_timeout.
        $pdo->exec('PRAGMA journal_mode = WAL');
        $pdo->exec('CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            credits INTEGER NOT NULL DEFAULT 0
        )');

        $userColumns = $pdo->query('PRAGMA table_info(users)')->fetchAll(PDO::FETCH_COLUMN, 1);
        if (!in_array('credits', $userColumns, true)) {
            $pdo->exec('ALTER TABLE users ADD COLUMN credits INTEGER NOT NULL DEFAULT 0');
        }
        if (!in_array('email_verified_at', $userColumns, true)) {
            $pdo->exec('ALTER TABLE users ADD COLUMN email_verified_at TEXT');
        }
        if (!in_array('session_version', $userColumns, true)) {
            $pdo->exec('ALTER TABLE users ADD COLUMN session_version INTEGER NOT NULL DEFAULT 0');
        }
    }

    return $pdo;
}

function rowExistsById(PDO $pdo, string $table, $id): bool
{
    $stmt = $pdo->prepare("SELECT 1 FROM {$table} WHERE id = :id");
    $stmt->execute(['id' => $id]);
    return (bool) $stmt->fetch();
}