<?php

declare(strict_types=1);

namespace App\Core;

use PDO;

/**
 * Thin database layer. MySQL through PDO in production; SQLite for local
 * development (through PDO when pdo_sqlite is installed, otherwise through
 * the dev-only SqliteCli fallback). The SQLite database is created and
 * seeded automatically on first run.
 */
final class Database
{
    private static ?PDO $pdo = null;
    private static ?SqliteCli $cli = null;

    private static function connect(): void
    {
        if (self::$pdo !== null || self::$cli !== null) {
            return;
        }

        $cfg = App::config('db');

        if ($cfg['driver'] === 'sqlite') {
            $path = $cfg['sqlite_path'];
            if (!is_dir(dirname($path))) {
                mkdir(dirname($path), 0775, true);
            }
            $isNew = !file_exists($path);
            $base = dirname(__DIR__, 3) . '/database';

            if (in_array('sqlite', PDO::getAvailableDrivers(), true)) {
                self::$pdo = new PDO('sqlite:' . $path, null, null, self::options());
                self::$pdo->exec('PRAGMA foreign_keys = ON');
                if ($isNew) {
                    foreach (['schema.sqlite.sql', 'seed.sql'] as $file) {
                        $sql = file_get_contents("$base/$file");
                        if ($sql !== false) {
                            self::$pdo->exec($sql);
                        }
                    }
                }
                return;
            }

            if (SqliteCli::available()) {
                self::$cli = new SqliteCli($path);
                if ($isNew) {
                    self::$cli->bootstrap("$base/schema.sqlite.sql", "$base/seed.sql");
                }
                return;
            }

            throw new \RuntimeException(
                'SQLite driver requested but neither pdo_sqlite nor the sqlite3 CLI is available.',
            );
        }

        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=%s',
            $cfg['host'],
            $cfg['port'],
            $cfg['database'],
            $cfg['charset'],
        );
        self::$pdo = new PDO($dsn, $cfg['username'], $cfg['password'], self::options());
    }

    /** @return array<int, mixed> */
    private static function options(): array
    {
        return [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ];
    }

    /** @param array<string|int, mixed> $params @return array<int, array<string, mixed>> */
    public static function select(string $sql, array $params = []): array
    {
        self::connect();
        if (self::$cli !== null) {
            return self::$cli->select($sql, $params);
        }
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /** @param array<string|int, mixed> $params @return array<string, mixed>|null */
    public static function selectOne(string $sql, array $params = []): ?array
    {
        $rows = self::select($sql, $params);
        return $rows[0] ?? null;
    }

    /** @param array<string|int, mixed> $params */
    public static function execute(string $sql, array $params = []): void
    {
        self::connect();
        if (self::$cli !== null) {
            self::$cli->execute($sql, $params);
            return;
        }
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($params);
    }

    /** @param array<string, mixed> $data */
    public static function insert(string $table, array $data): int
    {
        self::connect();
        $cols = array_keys($data);
        $sql = sprintf(
            'INSERT INTO %s (%s) VALUES (%s)',
            $table,
            implode(', ', $cols),
            implode(', ', array_map(fn($c) => ':' . $c, $cols)),
        );
        self::execute($sql, $data);
        return self::$cli !== null
            ? self::$cli->lastInsertId($table)
            : (int) self::$pdo->lastInsertId();
    }

    /** @param array<string, mixed> $data */
    public static function update(string $table, int $id, array $data): void
    {
        $set = implode(', ', array_map(fn($c) => "$c = :$c", array_keys($data)));
        self::execute("UPDATE $table SET $set WHERE id = :__id", [...$data, '__id' => $id]);
    }

    public static function delete(string $table, int $id): void
    {
        self::execute("DELETE FROM $table WHERE id = ?", [$id]);
    }
}
