<?php
/**
 * PDO database wrapper with:
 * - Connection options tuned for utf8mb4
 * - Automatic retry on transient MySQL errors (deadlock, lock wait, connection lost)
 * - Optional multi-statement transactions via transaction()
 */
class Database
{
    private PDO $pdo;
    private int $maxRetries = 3;
    private int $retryDelayMs = 80;

    /** SQLSTATE / driver codes considered transient and safe to retry */
    private array $retrySqlStates = ['40001', 'HY000'];
    private array $retryDriverCodes = [1213, 1205, 2006, 2013]; // deadlock, lock wait, gone away, lost connection

    public function __construct(array $c)
    {
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=%s',
            $c['host'],
            $c['name'],
            $c['charset'] ?? 'utf8mb4'
        );
        $this->pdo = new PDO($dsn, $c['user'], $c['pass'] ?? '', [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_TIMEOUT => 5,
        ]);
        // Keep session timezone aligned if needed
        try {
            $this->pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
        } catch (Throwable $e) {
            // non-fatal
        }
    }

    public function pdo(): PDO
    {
        return $this->pdo;
    }

    private function isRetryable(Throwable $e): bool
    {
        if (!$e instanceof PDOException) {
            return false;
        }
        $state = (string) ($e->errorInfo[0] ?? $e->getCode());
        $driver = (int) ($e->errorInfo[1] ?? 0);
        if (in_array($state, $this->retrySqlStates, true)) {
            return true;
        }
        if (in_array($driver, $this->retryDriverCodes, true)) {
            return true;
        }
        // Message fallback for some drivers
        $msg = strtolower($e->getMessage());
        return str_contains($msg, 'deadlock')
            || str_contains($msg, 'try restarting transaction')
            || str_contains($msg, 'server has gone away')
            || str_contains($msg, 'lost connection');
    }

    /**
     * Run a callable with retry on transient DB failures.
     * @template T
     * @param callable():T $fn
     * @return mixed
     */
    public function withRetry(callable $fn)
    {
        $attempt = 0;
        beginning:
        $attempt++;
        try {
            return $fn();
        } catch (Throwable $e) {
            if ($attempt < $this->maxRetries && $this->isRetryable($e)) {
                usleep($this->retryDelayMs * 1000 * $attempt); // linear backoff
                goto beginning;
            }
            throw $e;
        }
    }

    public function one(string $sql, array $p = []): ?array
    {
        return $this->withRetry(function () use ($sql, $p) {
            $s = $this->pdo->prepare($sql);
            $s->execute($p);
            $row = $s->fetch();
            return $row === false ? null : $row;
        });
    }

    public function all(string $sql, array $p = []): array
    {
        return $this->withRetry(function () use ($sql, $p) {
            $s = $this->pdo->prepare($sql);
            $s->execute($p);
            $rows = $s->fetchAll();
            return is_array($rows) ? $rows : [];
        });
    }

    public function exec(string $sql, array $p = []): int
    {
        return (int) $this->withRetry(function () use ($sql, $p) {
            $s = $this->pdo->prepare($sql);
            $s->execute($p);
            return $s->rowCount();
        });
    }

    public function lastId(): string
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * Execute work inside a DB transaction with retry on deadlock.
     * Callback receives $this Database instance.
     * @param callable(Database):mixed $fn
     * @return mixed
     */
    public function transaction(callable $fn)
    {
        return $this->withRetry(function () use ($fn) {
            $this->pdo->beginTransaction();
            try {
                $result = $fn($this);
                $this->pdo->commit();
                return $result;
            } catch (Throwable $e) {
                if ($this->pdo->inTransaction()) {
                    $this->pdo->rollBack();
                }
                throw $e;
            }
        });
    }
}
