<?php
declare(strict_types=1);
namespace Aen;

use PDO;
use RuntimeException;
use Throwable;

/** Native PHP PDO_SQLITE. Never put the database directory under a public web root. */
final class Database
{
    private PDO $pdo;
    public function __construct(string $path = ':memory:', bool $initialize = false, int $busyMs = 2000)
    {
        if (!extension_loaded('pdo_sqlite')) throw new RuntimeException('Enable the pdo_sqlite PHP extension.');
        if ($path !== ':memory:') {
            if ($initialize && file_exists($path)) throw new RuntimeException('Refusing to overwrite an existing database.');
            if (!$initialize && !is_file($path)) throw new RuntimeException('Database not found. Run init first.');
            if (!is_dir(dirname($path))) throw new RuntimeException('Create the private storage directory first.');
        }
        $this->pdo = new PDO('sqlite:' . $path, null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
        if (version_compare($this->version(), '3.37.0', '<')) throw new RuntimeException('SQLite >= 3.37 is required for STRICT tables.');
        $this->pdo->exec('PRAGMA foreign_keys=ON');
        $this->pdo->exec('PRAGMA busy_timeout=' . max(1, min($busyMs, 5000)));
        if ($path !== ':memory:') $this->pdo->exec('PRAGMA journal_mode=WAL');
        if ($initialize || $path === ':memory:') $this->initialize();
    }
    private function initialize(): void
    {
        $root = dirname(__DIR__);
        $schema = file_get_contents($root . '/fixtures/schema.sql');
        $seed = json_decode(file_get_contents($root . '/fixtures/seed.json'), true, 512, JSON_THROW_ON_ERROR);
        $this->pdo->exec($schema);
        $this->transaction(function () use ($seed): void {
            // Table and column names come only from these bundled application fixtures.
            foreach (['contacts', 'orders', 'stock'] as $table) foreach ($seed[$table] as $row) {
                $columns = array_keys($row);
                $this->execute('INSERT INTO ' . $table . '(' . implode(',', $columns) . ') VALUES(' . implode(',', array_fill(0, count($columns), '?')) . ')', array_values($row));
            }
        });
    }
    private function statement(string $sql, array $params = []): \PDOStatement
    {
        $statement = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) $statement->bindValue(is_int($key) ? $key + 1 : $key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        $statement->execute();
        return $statement;
    }
    private function query(string $sql, array $params = []): array
    {
        $statement = $this->statement($sql, $params);
        try { return $statement->fetchAll(); } finally { $statement->closeCursor(); }
    }
    private function execute(string $sql, array $params = []): int
    {
        $statement = $this->statement($sql, $params);
        try { return $statement->rowCount(); } finally { $statement->closeCursor(); }
    }
    private function transaction(callable $fn): mixed
    {
        // BEGIN IMMEDIATE obtains the writer reservation before the read/change sequence.
        $this->pdo->exec('BEGIN IMMEDIATE');
        try { $value = $fn(); $this->pdo->exec('COMMIT'); return $value; }
        catch (Throwable $error) { $this->pdo->exec('ROLLBACK'); throw $error; }
    }
    private static function integer(mixed $value, int $min, int $max): int
    {
        if (!(is_int($value) || (is_string($value) && preg_match('/^\d+$/D', $value)))) throw new RuntimeException('Expected an integer.');
        if ((float)$value < $min || (float)$value > $max) throw new RuntimeException("Expected integer $min..$max.");
        return (int)$value;
    }
    public static function contact(array $raw): array
    {
        $keys = array_keys($raw); sort($keys);
        if ($keys !== ['city', 'email', 'name']) throw new RuntimeException('Contact keys: name, email, city only.');
        foreach ($raw as $key => $value) { if (!is_string($value)) throw new RuntimeException('Contact fields must be strings.'); $raw[$key] = trim($value); }
        $length = preg_match_all('/./us', $raw['name']);
        if ($length === false || $length < 1 || $length > 80) throw new RuntimeException('Name length: 1..80 characters.');
        $raw['email'] = strtolower($raw['email']);
        if (strlen($raw['email']) > 120 || !preg_match("/^[a-z0-9.!#$%&'*+\/=?^_`{|}~-]+@[a-z0-9-]+(?:\.[a-z0-9-]+)+$/iD", $raw['email'])) throw new RuntimeException('Invalid email.');
        if (!in_array($raw['city'], ['Москва', 'Казань', 'Тверь'], true)) throw new RuntimeException('Unknown city.');
        return ['name' => $raw['name'], 'email' => $raw['email'], 'city' => $raw['city']];
    }
    public function version(): string { return $this->pdo->query('SELECT sqlite_version()')->fetchColumn(); }
    public function settings(): array { return ['foreign_keys' => (int)$this->pdo->query('PRAGMA foreign_keys')->fetchColumn(), 'journal_mode' => $this->pdo->query('PRAGMA journal_mode')->fetchColumn(), 'busy_timeout' => (int)$this->pdo->query('PRAGMA busy_timeout')->fetchColumn()]; }
    public function counts(): array { return ['contacts' => (int)$this->pdo->query('SELECT COUNT(*) FROM contacts')->fetchColumn(), 'orders' => (int)$this->pdo->query('SELECT COUNT(*) FROM orders')->fetchColumn(), 'stock' => (int)$this->pdo->query('SELECT quantity FROM stock WHERE id=1')->fetchColumn()]; }
    public function contacts(): array { return $this->query('SELECT id,name,email,city FROM contacts ORDER BY id'); }
    public function add(array $raw): int
    {
        $contact = self::contact($raw);
        return $this->transaction(function () use ($contact): int {
            if ($this->counts()['contacts'] >= 200) throw new RuntimeException('Contact limit: 200.');
            $this->execute('INSERT INTO contacts(name,email,city) VALUES(:name,:email,:city)', $contact);
            return (int)$this->pdo->lastInsertId();
        });
    }
    public function update(int $id, array $raw): void { $contact = self::contact($raw); $contact['id'] = self::integer($id, 1, 1000000); if (!$this->query('SELECT id FROM contacts WHERE id=:id', ['id' => $id])) throw new RuntimeException('Contact does not exist.'); $this->execute('UPDATE contacts SET name=:name,email=:email,city=:city WHERE id=:id', $contact); }
    public function remove(int $id): void { $this->execute('DELETE FROM contacts WHERE id=:id', ['id' => self::integer($id, 1, 1000000)]); }
    private static function status(string $status): string { if (!in_array($status, ['new', 'paid', 'cancelled'], true)) throw new RuntimeException('Unknown status.'); return $status; }
    public function filter(string $status, mixed $minimumRubles): array { return $this->query('SELECT id,contact_id,amount_cents,status FROM orders WHERE status=:status AND amount_cents>=:minimum ORDER BY amount_cents DESC,id', ['status' => self::status($status), 'minimum' => self::integer($minimumRubles, 0, 1000000) * 100]); }
    public function joined(string $mode): array { if (!in_array($mode, ['INNER', 'LEFT'], true)) throw new RuntimeException('Unknown JOIN.'); return $this->query('SELECT c.name,c.city,o.id AS order_id,o.amount_cents,o.status FROM contacts c ' . $mode . ' JOIN orders o ON o.contact_id=c.id ORDER BY c.id,o.id'); }
    public function aggregate(string $status, mixed $minimum): array { return $this->query('SELECT c.city,COUNT(*) AS order_count,SUM(o.amount_cents) AS total_cents,ROUND(AVG(o.amount_cents)) AS average_cents FROM orders o JOIN contacts c ON c.id=o.contact_id WHERE o.status=:status GROUP BY c.city HAVING COUNT(*)>=:minimum ORDER BY total_cents DESC,c.city', ['status' => self::status($status), 'minimum' => self::integer($minimum, 1, 10)]); }
    public function reserve(mixed $quantity, bool $fail = false): void
    {
        $quantity = self::integer($quantity, 1, 100);
        $this->transaction(function () use ($quantity, $fail): void {
            if ($this->counts()['stock'] < $quantity) throw new RuntimeException('Not enough stock.');
            $this->execute('UPDATE stock SET quantity=quantity-:quantity WHERE id=1', ['quantity' => $quantity]);
            if ($fail) throw new RuntimeException('Demo failure after UPDATE: transaction rolled back.');
            $this->execute('INSERT INTO reservations(quantity) VALUES(:quantity)', ['quantity' => $quantity]);
        });
    }
    public function ledger(): array { return $this->query('SELECT id,quantity FROM reservations ORDER BY id'); }
    public function constraint(string $kind): void
    {
        match ($kind) {
            'unique' => $this->execute("INSERT INTO contacts(name,email,city) VALUES('Дубль',:email,'Москва')", ['email' => $this->contacts()[0]['email']]),
            'check' => $this->execute("INSERT INTO orders(contact_id,amount_cents,status) VALUES(1,-100,'new')"),
            'foreign' => $this->execute("INSERT INTO orders(contact_id,amount_cents,status) VALUES(999999,100,'new')"),
            default => throw new RuntimeException('Unknown constraint.'),
        };
    }
    public function index(bool $enabled): void { $this->pdo->exec($enabled ? 'CREATE INDEX IF NOT EXISTS idx_orders_status_amount ON orders(status,amount_cents)' : 'DROP INDEX IF EXISTS idx_orders_status_amount'); }
    public function explain(): array { return $this->query('EXPLAIN QUERY PLAN SELECT id,contact_id,amount_cents,status FROM orders WHERE status=:status AND amount_cents>=:minimum ORDER BY amount_cents DESC,id', ['status' => 'paid', 'minimum' => 1000000]); }
    public function import(string $json): int
    {
        if (strlen($json) > 480000 || preg_match_all('/./us', $json) > 120000) throw new RuntimeException('JSON limit: 120000 characters.');
        $rows = json_decode($json, true, 32, JSON_THROW_ON_ERROR);
        if (!is_array($rows) || !array_is_list($rows) || count($rows) < 1 || count($rows) > 200) throw new RuntimeException('Import: array of 1..200 contacts.');
        $rows = array_map(function ($row): array { if (!is_array($row)) throw new RuntimeException('Contact must be an object.'); return self::contact($row); }, $rows);
        if (count(array_unique(array_column($rows, 'email'))) !== count($rows)) throw new RuntimeException('Duplicate email in import.');
        return $this->transaction(function () use ($rows): int { if ($this->counts()['contacts'] + count($rows) > 200) throw new RuntimeException('Contact limit: 200.'); foreach ($rows as $row) $this->execute('INSERT INTO contacts(name,email,city) VALUES(:name,:email,:city)', $row); return count($rows); });
    }
    public function exportContacts(): array { return $this->query('SELECT name,email,city FROM contacts ORDER BY id'); }
    public function integrity(): array { return ['integrity_check' => $this->pdo->query('PRAGMA integrity_check')->fetchColumn(), 'foreign_key_check' => $this->query('PRAGMA foreign_key_check')]; }
    public function backup(string $destination): void
    {
        // Destination is constructed by the trusted CLI, never from an HTTP parameter.
        if (file_exists($destination) || !is_dir(dirname($destination))) throw new RuntimeException('Backup needs a new filename inside an existing private directory.');
        $this->execute('VACUUM INTO :destination', ['destination' => $destination]);
    }
}