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

namespace Aiwend\Db;

/**
 * WordPress `$wpdb` adapter. Translates `?` placeholders to `$wpdb->prepare()`
 * formats based on each bound value's PHP type.
 *
 * Status: not yet exercised against a real WordPress runtime (see project-state).
 */
final class WpdbConnection implements Connection
{
    private int $depth = 0;

    public function __construct(private \wpdb $wpdb)
    {
    }

    public function select(string $sql, array $params = []): array
    {
        $this->wpdb->last_error = '';
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- prepared by WpdbConnection::prepare() with placeholders; table names are validated identifiers
        $rows = $this->wpdb->get_results($this->prepare($sql, $params), ARRAY_A);
        $this->throwOnError();
        return is_array($rows) ? $rows : [];
    }

    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
    {
        $this->wpdb->last_error = '';
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- prepared by WpdbConnection::prepare() with placeholders; table names are validated identifiers
        $result = $this->wpdb->query($this->prepare($sql, $params));
        $this->throwOnError();
        return (int) $result;
    }

    public function insert(string $table, array $row): int
    {
        $this->wpdb->last_error = '';
        $ok = $this->wpdb->insert($this->table($table), $row);
        $this->throwOnError();
        if ($ok === false) {
            throw new DatabaseException('Insert failed.');
        }
        return (int) $this->wpdb->insert_id;
    }

    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.');
        }
        // A no-op ON DUPLICATE KEY update raises no error (so $wpdb prints nothing for an expected race) and reports 0 affected rows when
        // the key was taken (mysqli default flags); a new row reports 1.
        $sql = sprintf(
            'INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s = %s',
            $this->table($table), implode(', ', $cols), implode(', ', array_fill(0, count($cols), '?')), $keyColumns[0], $keyColumns[0]
        );
        // Phase 87: on MySQL 8.4 several PHP processes inserting the same unique key can pick this single statement as the deadlock
        // victim (error 1213). Outside a transaction the server rolled back only this statement, so running it again is exact and cannot
        // duplicate anything; inside a transaction the whole transaction was rolled back and transaction() re-runs it instead.
        for ($attempt = 1; ; $attempt++) {
            try {
                return $this->execute($sql, array_values($row)) === 1;
            } catch (DatabaseException $e) {
                if ($this->depth > 0 || $attempt >= self::DEADLOCK_ATTEMPTS || !self::isDeadlock($e)) {
                    throw $e;
                }
                usleep(random_int(20_000, 80_000));
            }
        }
    }

    public function transaction(callable $fn): mixed
    {
        if ($this->depth > 0) {
            $this->depth++;
            try {
                return $fn();
            } finally {
                $this->depth--;
            }
        }
        // Phase 86: InnoDB may pick this transaction as the victim of a deadlock between two concurrent requests (error 1213; the
        // server has already rolled it back). The work inside is database-only, so it is simply run again, a bounded number of times.
        for ($attempt = 1; ; $attempt++) {
            $this->wpdb->query('START TRANSACTION');
            $this->depth = 1;
            try {
                $result = $fn();
                $this->wpdb->query('COMMIT');
                return $result;
            } catch (\Throwable $e) {
                $this->wpdb->query('ROLLBACK');
                if ($attempt < self::DEADLOCK_ATTEMPTS && self::isDeadlock($e)) {
                    usleep(random_int(20_000, 80_000));
                    continue;
                }
                throw $e;
            } finally {
                $this->depth = 0;
            }
        }
    }

    private const DEADLOCK_ATTEMPTS = 3;

    private static function isDeadlock(\Throwable $e): bool
    {
        return $e instanceof DatabaseException && stripos($e->getMessage(), 'Deadlock found') !== false;
    }

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

    public function driver(): string
    {
        return 'mysql';
    }

    public function isUniqueViolation(\Throwable $e): bool
    {
        return $e instanceof DatabaseException && stripos($e->getMessage(), 'Duplicate entry') !== false;
    }

    /**
     * wpdb::query() checks every raw statement that contains non-ASCII text against ONE character set per table, and treats a table that mixes
     * ascii and utf8mb4 columns (several Aiwend tables do, on purpose: binary ASCII identifiers beside free text) as ASCII, so Persian, emoji or any
     * other non-ASCII value in a raw INSERT, UPDATE or WHERE on such a table was refused ("contains invalid data"). wpdb skips that check for a
     * query that is pure ASCII, so each non-ASCII string value is sent as a hex literal in the utf8mb4 character set: the same value, no quoting
     * or injection surface, and wpdb's check is never reached. The UTF-8 validity the check provided is kept here (invalid bytes are refused).
     */
    private function prepare(string $sql, array $params): string
    {
        $sql = (string) preg_replace_callback(
            '/\{([a-z_][a-z0-9_]*)\}/',
            fn(array $m): string => $this->table($m[1]),
            $sql
        );
        if ($params === []) {
            return $sql;
        }
        // Escape literal % before converting placeholders.
        $sql = str_replace('%', '%%', $sql);
        $values = array_values($params);
        $bound = [];
        $i = 0;
        $sql = (string) preg_replace_callback('/\?/', function () use (&$i, &$bound, $values): string {
            $v = $values[$i++] ?? null;
            if ($v === null) {
                return 'NULL'; // $wpdb->prepare() cannot bind NULL.
            }
            if (is_string($v) && preg_match('/[^\x00-\x7F]/', $v) === 1) {
                if (preg_match('//u', $v) !== 1) { // valid UTF-8 only (no overlong forms, surrogates or values above U+10FFFF); fails closed
                    throw new DatabaseException('A value is not valid UTF-8 and was not written.');
                }
                return '_utf8mb4 0x' . bin2hex($v);
            }
            $bound[] = is_bool($v) ? (int) $v : $v;
            return is_int($v) || is_bool($v) ? '%d' : (is_float($v) ? '%f' : '%s');
        }, $sql);
        if ($bound === []) {
            return str_replace('%%', '%', $sql);
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- prepared by WpdbConnection::prepare() with placeholders; table names are validated identifiers
        return (string) $this->wpdb->prepare($sql, ...$bound);
    }

    private function throwOnError(): void
    {
        if ($this->wpdb->last_error !== '') {
            // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- exception messages go to logs and callers, never echoed; public errors are fixed codes
            throw new DatabaseException($this->wpdb->last_error);
        }
    }
}