<?php
declare(strict_types=1);

namespace Core;

use PDO;
use PDOException;
use PDOStatement;

/**
 * Thin PDO wrapper. Two named connections: 'main' (v16) and 'seed' (v15, read-only).
 */
final class DB
{
    /** @var array<string, PDO> */
    private static array $connections = [];

    public static function conn(string $name = 'main'): PDO
    {
        if (isset(self::$connections[$name])) {
            return self::$connections[$name];
        }

        if ($name === 'main') {
            $host = env('DB_HOST', 'localhost');
            $db   = env('DB_NAME', 'sitetracker_v16');
            $user = env('DB_USER', 'root');
            $pass = env('DB_PASSWORD', '');
        } elseif ($name === 'seed') {
            $host = env('SEED_DB_HOST', 'localhost');
            $db   = env('SEED_DB_NAME', 'sitetracker_v15');
            $user = env('SEED_DB_USER', 'root');
            $pass = env('SEED_DB_PASSWORD', '');
        } else {
            throw new \InvalidArgumentException("Unknown connection: {$name}");
        }

        $charset = env('DB_CHARSET', 'utf8mb4');
        $dsn = "mysql:host={$host};dbname={$db};charset={$charset}";

        try {
            self::$connections[$name] = new PDO($dsn, $user, $pass, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::ATTR_PERSISTENT         => false,
            ]);
        } catch (PDOException $e) {
            throw new \RuntimeException("DB connection [{$name}] failed: " . $e->getMessage(), 0, $e);
        }

        return self::$connections[$name];
    }

    public static function run(string $sql, array $params = [], string $name = 'main'): PDOStatement
    {
        $stmt = self::conn($name)->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public static function one(string $sql, array $params = [], string $name = 'main'): ?array
    {
        $row = self::run($sql, $params, $name)->fetch();
        return $row === false ? null : $row;
    }

    public static function all(string $sql, array $params = [], string $name = 'main'): array
    {
        return self::run($sql, $params, $name)->fetchAll();
    }

    public static function value(string $sql, array $params = [], string $name = 'main'): mixed
    {
        $stmt = self::run($sql, $params, $name);
        $col = $stmt->fetchColumn();
        return $col === false ? null : $col;
    }

    public static function insert(string $table, array $data, string $name = 'main'): string
    {
        $cols = array_keys($data);
        $place = array_map(fn($c) => ':' . $c, $cols);
        $sql = sprintf(
            'INSERT INTO `%s` (`%s`) VALUES (%s)',
            $table,
            implode('`,`', $cols),
            implode(',', $place)
        );
        self::run($sql, $data, $name);
        return self::conn($name)->lastInsertId();
    }

    public static function tx(callable $fn, string $name = 'main'): mixed
    {
        $pdo = self::conn($name);
        $pdo->beginTransaction();
        try {
            $r = $fn($pdo);
            $pdo->commit();
            return $r;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }
    }
}
