<?php
// SPDX-License-Identifier: GPL-3.0-or-later
declare(strict_types=1);

namespace Aiwend\Db;

use PDO;
use PDOException;

/**
 * PDO-backed connection. Used for tests (SQLite) and available for MySQL
 * environments that are not running inside WordPress.
 */
final class PdoConnection implements Connection
{
    private int $depth = 0;

    public function __construct(private PDO $pdo, private string $prefix = 'wp_')
    {
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        if ($this->driver() === 'sqlite') {
            $this->pdo->exec('PRAGMA foreign_keys = ON');
        }
    }

    public static function sqliteMemory(string $prefix = 'wp_'): self
    {
        return new self(new PDO('sqlite::memory:'), $prefix);
    }

    public function select(string $sql, array $params = []): array
    {
        $stmt = $this->run($this->expand($sql), $params);
        return $stmt->fetchAll();
    }

    public function selectOne(string $sql, array $params = []): ?array
    {
        $rows = $this->select($sql, $params);
        return $rows[0] ?? null;
    }

    public function execute(string $sql, array $params = []): int
    {
        return $this->run($this->expand($sql), $params)->rowCount();
    }

    public function insert(string $table, array $row): int
    {
        $cols = array_keys($row);
        foreach ($cols as $col) {
            if (!preg_match('/^[a-z_][a-z0-9_]*$/', $col)) {
                throw new \InvalidArgumentException('Invalid column name.');
            }
        }
        $sql = sprintf(
            'INSERT INTO %s (%s) VALUES (%s)',
            $this->table($table),
            implode(', ', $cols),
            implode(', ', array_fill(0, count($cols), '?'))
        );
        $this->run($sql, $row);
        return (int) $this->pdo->lastInsertId();
    }

    public function insertIfAbsent(string $table, array $row, array $keyColumns): bool
    {
        $cols = array_keys($row);
        foreach (array_merge($cols, $keyColumns) as $col) {
            if (!preg_match('/^[a-z_][a-z0-9_]*$/', $col)) {
                throw new \InvalidArgumentException('Invalid column name.');
            }
        }
        if ($keyColumns === [] || array_diff($keyColumns, $cols) !== []) {
            throw new \InvalidArgumentException('The key columns must be part of the row.');
        }
        // SQLite names the conflict target, so any other unique violation still raises. MySQL/MariaDB can only say "on a duplicate key":
        // the no-op update changes nothing, and its affected-row count is 0 (a new row counts 1) with the default client flags.
        $conflict = $this->driver() === 'sqlite'
            ? ' ON CONFLICT (' . implode(', ', $keyColumns) . ') DO NOTHING'
            : ' ON DUPLICATE KEY UPDATE ' . $keyColumns[0] . ' = ' . $keyColumns[0];
        $sql = sprintf('INSERT INTO %s (%s) VALUES (%s)%s', $this->table($table), implode(', ', $cols), implode(', ', array_fill(0, count($cols), '?')), $conflict);
        // Phase 87: same bounded retry as WpdbConnection for a deadlock victim outside a transaction (MySQL/MariaDB error 1213 = SQLSTATE 40001).
        for ($attempt = 1; ; $attempt++) {
            try {
                return $this->run($sql, $row)->rowCount() === 1;
            } catch (PDOException $e) {
                $deadlock = (string) $e->getCode() === '40001' || stripos($e->getMessage(), 'Deadlock found') !== false;
                if ($this->depth > 0 || $attempt >= 3 || !$deadlock) {
                    throw $e;
                }
                usleep(random_int(20_000, 80_000));
            }
        }
    }

    public function transaction(callable $fn): mixed
    {
        // Nested calls join the outer transaction.
        if ($this->depth > 0) {
            $this->depth++;
            try {
                return $fn();
            } finally {
                $this->depth--;
            }
        }
        $this->pdo->beginTransaction();
        $this->depth = 1;
        try {
            $result = $fn();
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        } finally {
            $this->depth = 0;
        }
    }

    public function table(string $name): string
    {
        if (!preg_match('/^[a-z_][a-z0-9_]*$/', $name)) {
            throw new \InvalidArgumentException('Invalid table name.');
        }
        return $this->prefix . 'aiwend_' . $name;
    }

    public function driver(): string
    {
        return (string) $this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
    }

    public function isUniqueViolation(\Throwable $e): bool
    {
        if (!$e instanceof PDOException) {
            return false;
        }
        $code = (string) $e->getCode();
        // SQLSTATE 23000 = integrity constraint; narrow to unique/duplicate messages.
        return ($code === '23000' || $code === '23505')
            && (bool) preg_match('/unique|duplicate/i', $e->getMessage());
    }

    private function run(string $sql, array $params): \PDOStatement
    {
        $stmt = $this->pdo->prepare($sql);
        foreach (array_values($params) as $i => $v) {
            $type = match (true) {
                $v === null => PDO::PARAM_NULL,
                is_int($v), is_bool($v) => PDO::PARAM_INT,
                default => PDO::PARAM_STR,
            };
            $stmt->bindValue($i + 1, is_bool($v) ? (int) $v : $v, $type);
        }
        $stmt->execute();
        return $stmt;
    }

    private function expand(string $sql): string
    {
        return (string) preg_replace_callback(
            '/\{([a-z_][a-z0-9_]*)\}/',
            fn(array $m): string => $this->table($m[1]),
            $sql
        );
    }
}