<?php

declare(strict_types=1);

namespace App\Core;

use RuntimeException;

/**
 * DEVELOPMENT-ONLY fallback driver.
 *
 * Used automatically when the sqlite driver is configured but the pdo_sqlite
 * PHP extension is not installed (common on minimal macOS PHP builds): queries
 * are executed through the system `sqlite3` CLI instead. Production always
 * runs MySQL through PDO — this class is never used there.
 */
final class SqliteCli
{
    public function __construct(private readonly string $path)
    {
    }

    public static function available(): bool
    {
        exec('command -v sqlite3', $out, $code);
        return $code === 0;
    }

    public function bootstrap(string $schemaFile, string $seedFile): void
    {
        foreach ([$schemaFile, $seedFile] as $file) {
            if (!file_exists($file)) {
                throw new RuntimeException("SQLite bootstrap file missing: $file");
            }
            $cmd = sprintf('sqlite3 %s < %s 2>&1', escapeshellarg($this->path), escapeshellarg($file));
            exec($cmd, $out, $code);
            if ($code !== 0) {
                throw new RuntimeException('SQLite bootstrap failed: ' . implode("\n", $out));
            }
        }
    }

    /** @param array<string|int, mixed> $params @return array<int, array<string, mixed>> */
    public function select(string $sql, array $params = []): array
    {
        $out = $this->run($this->bind($sql, $params), true);
        return $out === '' ? [] : (json_decode($out, true) ?: []);
    }

    /** @param array<string|int, mixed> $params */
    public function execute(string $sql, array $params = []): void
    {
        $this->run($this->bind($sql, $params), false);
    }

    public function lastInsertId(string $table): int
    {
        $rows = $this->select("SELECT seq FROM sqlite_sequence WHERE name = " . $this->quote($table));
        return (int) ($rows[0]['seq'] ?? 0);
    }

    // -- internals -----------------------------------------------------------

    private function run(string $sql, bool $json): string
    {
        $cmd = sprintf(
            'sqlite3 %s %s %s 2>&1',
            $json ? '-json' : '',
            escapeshellarg($this->path),
            escapeshellarg($sql),
        );
        exec($cmd, $out, $code);
        $result = implode("\n", $out);
        if ($code !== 0) {
            throw new RuntimeException('SQLite error: ' . $result . ' — in: ' . $sql);
        }
        return $result;
    }

    /**
     * Interpolates bound parameters as safely-quoted literals. Dev-only:
     * the PDO code path with true prepared statements is used in production.
     *
     * @param array<string|int, mixed> $params
     */
    private function bind(string $sql, array $params): string
    {
        $named = [];
        $positional = [];
        foreach ($params as $key => $value) {
            if (is_string($key)) {
                $named[ltrim($key, ':')] = $value;
            } else {
                $positional[] = $value;
            }
        }

        // One pass over the original SQL only: inserted literals are never
        // re-scanned, so a '?' or ':word' INSIDE a bound value (image URLs,
        // question text, …) can never be mistaken for another placeholder.
        $i = 0;
        return preg_replace_callback(
            '/\?|:([A-Za-z_][A-Za-z0-9_]*)/',
            function (array $m) use (&$i, $named, $positional) {
                if ($m[0] === '?') {
                    return $this->literal($positional[$i++] ?? null);
                }
                return array_key_exists($m[1], $named)
                    ? $this->literal($named[$m[1]])
                    : $m[0]; // not a bound name (e.g. a time literal) — leave it
            },
            $sql,
        );
    }

    private function literal(mixed $value): string
    {
        if ($value === null) {
            return 'NULL';
        }
        if (is_bool($value)) {
            return $value ? '1' : '0';
        }
        if (is_int($value) || is_float($value)) {
            return (string) $value;
        }
        return $this->quote((string) $value);
    }

    private function quote(string $value): string
    {
        return "'" . str_replace("'", "''", $value) . "'";
    }
}
