<?php
/**
 * SQLiteWrapper.php — Thin wrapper around PHP's SQLite3 extension.
 *
 * Provides parameterized select/insert/update/delete plus a few
 * aggregation helpers (selectGrouped/selectScalar) so callers never need
 * to pull large tables into PHP just to count/group rows — see
 * analytics.php for why that matters (a real production memory-exhaustion
 * fatal came from doing exactly that before selectGrouped/selectScalar
 * existed).
 */
class SQLiteWrapper {
    private $db;

    public function __construct(string $dbLoc) {
        if (!extension_loaded('sqlite3')) {
            die('Fatal error: The PHP "sqlite3" extension is not enabled on this server. '
                . 'Please enable it in your hosting control panel (cPanel: MultiPHP INI Editor / '
                . 'Select PHP Version → Extensions → sqlite3) and reload this page.');
        }

        try {
            $this->db = new SQLite3($dbLoc);
        } catch (Throwable $e) {
            try {
                $this->db = new SQLite3('.db.db');
            } catch (Throwable $e2) {
                die('Fatal error: Unable to open the database file. Check that the "api" directory '
                    . 'is writable by the web server. (' . htmlspecialchars($e2->getMessage()) . ')');
            }
        }
        if (!$this->db) {
            die('Error: Unable to open database.');
        }
        // Enable WAL mode for better concurrent access, and give concurrent
        // writers a retry window instead of failing immediately with
        // "database is locked" (SQLITE_BUSY). Without this, any two requests
        // that touch the DB at nearly the same moment can silently drop a
        // write - e.g. two endpoints polled/submitted within the same
        // instant. 10s comfortably covers this app's write patterns (many
        // devices polling/writing concurrently) without risking a request
        // hanging noticeably.
        $this->db->busyTimeout(10000);
        $this->db->exec('PRAGMA journal_mode=WAL');
        // Bound WAL growth: force a checkpoint whenever the WAL file
        // reaches ~1000 pages (~4MB) instead of only at connection
        // shutdown. Without this, a busy site never naturally checkpoints
        // (every request is a short-lived connection) and the WAL file
        // grows unbounded, which was observed reaching 32MB+ in
        // production — larger WAL files mean longer checkpoint operations
        // and a wider contention window for concurrent writers, directly
        // contributing to SQLITE_BUSY "database is locked" errors.
        $this->db->exec('PRAGMA wal_autocheckpoint=1000');
    }

    /**
     * Runs a write operation (INSERT/UPDATE/DELETE), retrying with a short
     * backoff if SQLite reports the database as busy/locked. busyTimeout()
     * already makes SQLite itself wait and retry internally, but under
     * heavy concurrent write load (many devices polling/writing at once)
     * that single internal wait can still be exhausted — this adds a
     * bounded number of retries at the PHP level as defense in depth,
     * rather than surfacing a failed write to the caller on the first
     * SQLITE_BUSY.
     *
     * @param callable(): mixed $operation
     */
    private function withBusyRetry(callable $operation, string $context) {
        $maxAttempts = 3;
        $delayMicroseconds = 100000; // 100ms, doubled each retry
        for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
            $result = $operation();
            if ($result !== false) {
                return $result;
            }
            $errorCode = $this->db->lastErrorCode();
            $isBusyOrLocked = in_array($errorCode, [5 /* SQLITE_BUSY */, 6 /* SQLITE_LOCKED */], true);
            if (!$isBusyOrLocked || $attempt === $maxAttempts) {
                if ($isBusyOrLocked) {
                    error_log("DB $context: still locked after $maxAttempts attempts: " . $this->db->lastErrorMsg());
                }
                return $result;
            }
            usleep($delayMicroseconds);
            $delayMicroseconds *= 2;
        }
        return false;
    }

    public function select(string $tableName, string $columns = '*', string $where = '', string $orderBy = '', array $placeholders = [], int $limit = 0): array {
        $query = "SELECT $columns FROM $tableName";
        if (!empty($where)) {
            $query .= " WHERE $where";
        }
        if (!empty($orderBy)) {
            $query .= " ORDER BY $orderBy";
        }
        if ($limit > 0) {
            $query .= " LIMIT " . $limit;
        }

        $stmt = $this->db->prepare($query);
        if ($stmt === false) {
            error_log("DB select prepare failed: " . $this->db->lastErrorMsg() . " query=$query");
            return [];
        }
        foreach ($placeholders as $key => $value) {
            $stmt->bindValue($key, $value);
        }

        $result = $stmt->execute();
        $data = [];
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            $data[] = $row;
        }
        return $data;
    }

    /**
     * Runs a GROUP BY aggregation query and returns the grouped rows
     * directly from SQLite, WITHOUT ever pulling the underlying ungrouped
     * rows into PHP. Use this instead of select()-then-aggregate-in-a-
     * foreach-loop for any table that can grow large (e.g. request_log) —
     * pulling tens/hundreds of thousands of raw rows into a PHP array to
     * count them client-side is what caused a real production "Allowed
     * memory size exhausted" fatal on the analytics page.
     *
     * @param string $selectExpr Column/aggregate expressions, e.g. "endpoint, COUNT(*) as hits"
     * @param string $groupBy Column(s) to group by, e.g. "endpoint"
     */
    public function selectGrouped(
        string $tableName,
        string $selectExpr,
        string $groupBy,
        string $where = '',
        string $orderBy = '',
        array $placeholders = [],
        int $limit = 0
    ): array {
        $query = "SELECT $selectExpr FROM $tableName";
        if (!empty($where)) {
            $query .= " WHERE $where";
        }
        $query .= " GROUP BY $groupBy";
        if (!empty($orderBy)) {
            $query .= " ORDER BY $orderBy";
        }
        if ($limit > 0) {
            $query .= " LIMIT " . $limit;
        }

        $stmt = $this->db->prepare($query);
        if ($stmt === false) {
            error_log("DB selectGrouped prepare failed: " . $this->db->lastErrorMsg() . " query=$query");
            return [];
        }
        foreach ($placeholders as $key => $value) {
            $stmt->bindValue($key, $value);
        }

        $result = $stmt->execute();
        $data = [];
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            $data[] = $row;
        }
        return $data;
    }

    /**
     * Runs a single-row aggregation query (e.g. COUNT(*), MIN(col)) and
     * returns just that row, without a GROUP BY. Use for scalar
     * aggregates over a potentially large table instead of select()-ing
     * every row just to read one computed value.
     *
     * @param string $selectExpr e.g. "COUNT(*) as total, MIN(created_at) as oldest"
     */
    public function selectScalar(string $tableName, string $selectExpr, string $where = '', array $placeholders = []): array {
        $query = "SELECT $selectExpr FROM $tableName";
        if (!empty($where)) {
            $query .= " WHERE $where";
        }
        $stmt = $this->db->prepare($query);
        if ($stmt === false) {
            error_log("DB selectScalar prepare failed: " . $this->db->lastErrorMsg() . " query=$query");
            return [];
        }
        foreach ($placeholders as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $result = $stmt->execute();
        $row = $result->fetchArray(SQLITE3_ASSOC);
        return $row !== false ? $row : [];
    }

    public function insert(string $tableName, array $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        $query = "INSERT INTO $tableName ($columns) VALUES ($placeholders)";

        $stmt = $this->db->prepare($query);
        if ($stmt === false) {
            error_log("DB insert prepare failed: " . $this->db->lastErrorMsg() . " query=$query");
            return false;
        }
        foreach ($data as $key => $value) {
            $stmt->bindValue(':' . $key, $value);
        }
        return $this->withBusyRetry(fn() => $stmt->execute(), "insert into $tableName");
    }

    public function update(string $tableName, array $data, string $where = '', array $placeholders = []) {
        $setValues = [];
        foreach ($data as $column => $value) {
            $setValues[] = "$column = :set_$column";
        }
        $query = "UPDATE $tableName SET " . implode(', ', $setValues);
        if (!empty($where)) {
            $query .= " WHERE $where";
        }

        $stmt = $this->db->prepare($query);
        if ($stmt === false) {
            error_log("DB prepare failed: " . $this->db->lastErrorMsg() . " query=$query");
            return false;
        }
        foreach ($data as $key => $value) {
            $stmt->bindValue(':set_' . $key, $value);
        }
        foreach ($placeholders as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $result = $this->withBusyRetry(fn() => $stmt->execute(), "update $tableName");
        if ($result === false) {
            error_log("DB update failed: " . $this->db->lastErrorMsg() . " query=$query");
        }
        return $result;
    }

    public function delete(string $tableName, string $where = '', array $placeholders = []) {
        $query = "DELETE FROM $tableName";
        if (!empty($where)) {
            $query .= " WHERE $where";
        }

        $stmt = $this->db->prepare($query);
        if ($stmt === false) {
            error_log("DB delete prepare failed: " . $this->db->lastErrorMsg() . " query=$query");
            return false;
        }
        foreach ($placeholders as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        return $this->withBusyRetry(fn() => $stmt->execute(), "delete from $tableName");
    }

    public function insertIfEmpty(string $tableName, array $data) {
        if ($this->isEmptyTable($tableName)) {
            return $this->insert($tableName, $data);
        }
        return false;
    }

    private function isEmptyTable(string $tableName): bool {
        $result = $this->db->query("SELECT COUNT(*) as count FROM $tableName");
        $row = $result->fetchArray(SQLITE3_ASSOC);
        return ((int) $row['count'] === 0);
    }

    public function count(string $tableName, string $where = '', array $placeholders = []): int {
        $query = "SELECT COUNT(*) as count FROM $tableName";
        if (!empty($where)) {
            $query .= " WHERE $where";
        }
        $stmt = $this->db->prepare($query);
        foreach ($placeholders as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $result = $stmt->execute();
        $row = $result->fetchArray(SQLITE3_ASSOC);
        return (int) $row['count'];
    }

    public function getLastInsertId(): int {
        return $this->db->lastInsertRowID();
    }

    public function close(): void {
        $this->db->close();
    }
}

/**
 * Creates all tables from $tables (see includes/table.php) if they don't
 * exist, and adds any missing columns to tables that already exist (a
 * lightweight migration mechanism — never drops or renames columns).
 */
function createTables(array $tables, string $dbLoc): void {
    if (!extension_loaded('sqlite3')) {
        die('Fatal error: The PHP "sqlite3" extension is not enabled on this server. '
            . 'Please enable it in your hosting control panel (cPanel: MultiPHP INI Editor / '
            . 'Select PHP Version → Extensions → sqlite3) and reload this page.');
    }

    try {
        $sqliteDb = new SQLite3($dbLoc);
    } catch (Throwable $e) {
        try {
            $sqliteDb = new SQLite3('.db.db');
        } catch (Throwable $e2) {
            die('Fatal error: Unable to open the database file. Check that the "api" directory '
                . 'is writable by the web server. (' . htmlspecialchars($e2->getMessage()) . ')');
        }
    }

    if (!$sqliteDb) {
        die('Error connecting to the database');
    }

    $sqliteDb->busyTimeout(5000);

    foreach ($tables as $tableName => $columns) {
        $sql = "CREATE TABLE IF NOT EXISTS $tableName (";
        $cols = [];
        foreach ($columns as $columnName => $columnType) {
            $cols[] = "$columnName $columnType";
        }
        $sql .= implode(', ', $cols) . ');';
        $sqliteDb->exec($sql);

        // Add missing columns to existing tables
        $existingCols = [];
        $pragmaResult = $sqliteDb->query("PRAGMA table_info($tableName)");
        if ($pragmaResult) {
            while ($row = $pragmaResult->fetchArray(SQLITE3_ASSOC)) {
                $existingCols[] = $row['name'];
            }
        }
        foreach ($columns as $columnName => $columnType) {
            if (!in_array($columnName, $existingCols)) {
                // Strip NOT NULL from ALTER TABLE ADD COLUMN (SQLite limitation)
                $alterType = str_replace(' NOT NULL', '', $columnType);
                @$sqliteDb->exec("ALTER TABLE $tableName ADD COLUMN $columnName $alterType");
            }
        }
    }
    $sqliteDb->close();
}
