<?php
namespace Relay\Database;

use Relay\Support\RelayError;

final class Db
{
    private \wpdb $wpdb;
    /** @var string[]|null */
    private ?array $collations = null;

    public function __construct(\wpdb $wpdb)
    {
        $this->wpdb = $wpdb;
    }

    public function wpdb(): \wpdb
    {
        return $this->wpdb;
    }

    public static function ident(string $name): string
    {
        return '`' . str_replace('`', '``', $name) . '`';
    }

    public function query(string $sql): int
    {
        $this->wpdb->last_error = '';
        $result = $this->quietly(fn () => $this->wpdb->query($sql)); // phpcs:ignore WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB.UnescapedDBParameter -- callers pass SQL built with $wpdb->prepare() and Db::ident()
        if ($result === false || $this->wpdb->last_error !== '') { // @phpstan-ignore notIdentical.alwaysFalse (wpdb sets last_error during the query)
            throw $this->error($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL for the error's context; error() escapes the message it builds
        }
        return (int) $result;
    }

    public function results(string $sql, string $output = OBJECT): array
    {
        $this->wpdb->last_error = '';
        $rows = $this->quietly(fn () => $this->wpdb->get_results($sql, $output)); // phpcs:ignore WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB.UnescapedDBParameter -- callers pass SQL built with $wpdb->prepare() and Db::ident()
        if ($this->wpdb->last_error !== '') { // @phpstan-ignore notIdentical.alwaysFalse (wpdb sets last_error during the query)
            throw $this->error($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL for the error's context; error() escapes the message it builds
        }
        return is_array($rows) ? $rows : [];
    }

    public function col(string $sql): array
    {
        $this->wpdb->last_error = '';
        $col = $this->quietly(fn () => $this->wpdb->get_col($sql)); // phpcs:ignore WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB.UnescapedDBParameter -- callers pass SQL built with $wpdb->prepare() and Db::ident()
        if ($this->wpdb->last_error !== '') { // @phpstan-ignore notIdentical.alwaysFalse (wpdb sets last_error during the query)
            throw $this->error($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL for the error's context; error() escapes the message it builds
        }
        return $col;
    }

    /** @return string|null */
    public function var(string $sql)
    {
        $this->wpdb->last_error = '';
        $value = $this->quietly(fn () => $this->wpdb->get_var($sql)); // phpcs:ignore WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB.UnescapedDBParameter -- callers pass SQL built with $wpdb->prepare() and Db::ident()
        if ($this->wpdb->last_error !== '') { // @phpstan-ignore notIdentical.alwaysFalse (wpdb sets last_error during the query)
            throw $this->error($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL for the error's context; error() escapes the message it builds
        }
        return $value;
    }

    public function prefix(): string
    {
        return $this->wpdb->base_prefix;
    }

    /** @return string[] */
    public function tables(): array
    {
        $rows = $this->results($this->wpdb->prepare('SHOW FULL TABLES LIKE %s', $this->wpdb->esc_like($this->prefix()) . '%'), ARRAY_N);
        $tables = [];
        foreach ($rows as $row) {
            if (($row[1] ?? '') === 'BASE TABLE' && !Naming::is_internal($row[0])) {
                $tables[] = $row[0];
            }
        }
        sort($tables);
        return $tables;
    }

    /** @return array<string, array{rows: int, bytes: int}> */
    public function table_status(array $tables): array
    {
        if ($tables === []) {
            return [];
        }
        $in = implode(',', array_map(fn ($t) => $this->wpdb->prepare('%s', $t), $tables));
        $rows = $this->results("SELECT TABLE_NAME AS name, TABLE_ROWS AS row_count, DATA_LENGTH + INDEX_LENGTH AS bytes
            FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$in})");
        $out = [];
        foreach ($rows as $r) {
            $out[$r->name] = ['rows' => (int) $r->row_count, 'bytes' => (int) $r->bytes];
        }
        return $out;
    }

    public function table_exists(string $table): bool
    {
        return $this->var($this->wpdb->prepare('SHOW TABLES LIKE %s', $this->wpdb->esc_like($table))) === $table;
    }

    public function server_version(): string
    {
        return (string) $this->var('SELECT VERSION()');
    }

    /** INSERT one row (column => value; null stays NULL) and return its new auto-increment id; throws on errors. */
    public function insert(string $table, array $row): int
    {
        $values = [];
        foreach ($row as $value) {
            $values[] = $value === null ? 'NULL' : (is_int($value) ? (string) $value : $this->wpdb->prepare('%s', (string) $value));
        }
        $this->query('INSERT INTO ' . self::ident($table) . ' (' . implode(',', array_map([self::class, 'ident'], array_keys($row))) . ') VALUES (' . implode(',', $values) . ')');
        return (int) $this->wpdb->insert_id;
    }

    /**
     * Runs a statement of a database copy or import (bulk inserts, DDL) through $wpdb->query(), with its strings
     * in plain ASCII (ascii()): $wpdb then runs it as MySQL would, without refusing text that doesn't fit its
     * column's character set, which a site's database can hold.
     */
    public function raw(string $sql): void
    {
        $this->query(self::ascii($sql, (string) $this->wpdb->charset));
    }

    /**
     * $sql with each quoted string that isn't plain ASCII written as a hex literal in $charset, the connection's
     * character set (_utf8mb4 0x…), as bytes when they aren't valid in it (_binary 0x…), or with the introducer it
     * already has: the same value for MySQL,
     * in a statement $wpdb->query() doesn't check against the columns' character sets. CREATE and ALTER
     * statements, whose strings MySQL wants quoted (and $wpdb doesn't check), are left as they are.
     */
    public static function ascii(string $sql, string $charset): string
    {
        if (!preg_match('/[\x80-\xff]/', $sql) || preg_match('/^\s*(?:CREATE|ALTER)\s/i', $sql)) {
            return $sql;
        }
        $charset = preg_match('/^[A-Za-z0-9]+$/', $charset) ? $charset : 'utf8mb4';
        $literal = '/`(?:[^`]++|``)*+`|(?<![A-Za-z0-9_$])(?:(_[A-Za-z0-9]+)(\s*))?(\'(?:[^\'\\\\]++|\\\\.|\'\')*+\'|"(?:[^"\\\\]++|\\\\.|"")*+")/s';
        return (string) preg_replace_callback($literal, static function (array $m) use ($charset): string {
            if (!isset($m[3]) || !preg_match('/[\x80-\xff]/', $m[3])) {
                return $m[0]; // an identifier, or a string MySQL reads as ASCII already
            }
            $bytes = self::unquote($m[3]);
            return ($m[1] !== '' ? $m[1] : (self::valid($bytes, $charset) ? '_' . $charset : '_binary')) . ' 0x' . bin2hex($bytes);
        }, $sql);
    }

    /** Whether MySQL takes $bytes as a $charset string (it refuses _utf8mb4 0x… that isn't UTF-8, unlike a quoted string). */
    private static function valid(string $bytes, string $charset): bool
    {
        if (stripos($charset, 'utf8') !== 0) {
            return true;
        }
        return preg_match('//u', $bytes) === 1 && (strtolower($charset) === 'utf8mb4' || !preg_match('/[\x{10000}-\x{10FFFF}]/u', $bytes));
    }

    /** The bytes a quoted MySQL string stands for (backslash escapes, a doubled quote). */
    private static function unquote(string $literal): string
    {
        $quote = $literal[0];
        $escapes = ['0' => "\0", 'b' => "\x08", 'n' => "\n", 'r' => "\r", 't' => "\t", 'Z' => "\x1a", '%' => '\\%', '_' => '\\_'];
        return (string) preg_replace_callback('/\\\\(.)|' . $quote . $quote . '/s', static fn (array $m): string => isset($m[1]) ? ($escapes[$m[1]] ?? $m[1]) : $quote, substr($literal, 1, -1));
    }

    /** Session settings Relay's SQL relies on: backtick identifiers, backslash escapes, literal zero ids, UTC timestamps. */
    public function prepare_session(): void
    {
        $modes = array_filter(explode(',', (string) $this->var('SELECT @@SESSION.sql_mode')));
        $modes = array_values(array_diff($modes, ['ANSI_QUOTES', 'NO_BACKSLASH_ESCAPES', 'ANSI']));
        if (!in_array('NO_AUTO_VALUE_ON_ZERO', $modes, true)) {
            $modes[] = 'NO_AUTO_VALUE_ON_ZERO';
        }
        $this->query($this->wpdb->prepare('SET SESSION sql_mode = %s', implode(',', $modes)));
        $this->query("SET SESSION time_zone = '+00:00'");
    }

    /** @return array<string, string> referencing (unselected) table => referenced (selected) table */
    public function fk_dependents(array $tables): array
    {
        if ($tables === []) {
            return [];
        }
        $in = implode(',', array_map(fn ($t) => $this->wpdb->prepare('%s', $t), $tables));
        $rows = $this->results("SELECT DISTINCT TABLE_NAME AS child, REFERENCED_TABLE_NAME AS parent
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IN ({$in})");
        $out = [];
        foreach ($rows as $r) {
            if (!in_array($r->child, $tables, true)) {
                $out[$r->child] = $r->parent;
            }
        }
        return $out;
    }

    /** CREATE, ALTER, RENAME and DROP are all needed for a swap; find out up front with a throwaway table. */
    public function check_privileges(string $tag): void
    {
        $probe = Naming::import('relay_probe_' . $tag);
        $moved = Naming::import('relay_probe2_' . $tag);
        try {
            $this->raw('CREATE TABLE ' . self::ident($probe) . ' (id int NOT NULL)');
            $this->raw('ALTER TABLE ' . self::ident($probe) . ' ADD COLUMN v int NULL');
            $this->raw('RENAME TABLE ' . self::ident($probe) . ' TO ' . self::ident($moved));
        } finally {
            $this->raw('DROP TABLE IF EXISTS ' . self::ident($probe));
            $this->raw('DROP TABLE IF EXISTS ' . self::ident($moved));
        }
    }

    /** @return string[] lower-case names of the collations this server supports */
    public function collations(): array
    {
        if ($this->collations === null) {
            $this->collations = array_map('strtolower', $this->col('SELECT COLLATION_NAME FROM information_schema.COLLATIONS'));
        }
        return $this->collations;
    }

    /** @return string[] names of the foreign keys defined on $table */
    public function foreign_keys(string $table): array
    {
        return $this->col($this->wpdb->prepare(
            "SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND CONSTRAINT_TYPE = 'FOREIGN KEY'",
            $table
        ));
    }

    /**
     * $wpdb prints errors as HTML when show_errors is on (e.g. WP_DEBUG), which would corrupt REST/JSON
     * output. Relay reports errors itself, so printing is suppressed for the duration of the call.
     *
     * @return mixed
     */
    public function quietly(callable $fn)
    {
        $was_showing = $this->wpdb->hide_errors();
        try {
            return $fn();
        } finally {
            if ($was_showing) {
                $this->wpdb->show_errors();
            }
        }
    }

    private function error(string $sql): RelayError
    {
        return new RelayError(
            'db.query_failed',
            /* translators: %s: database error message */
            esc_html(sprintf(__('Database error: %s', 'siterelay'), $this->wpdb->last_error !== '' ? $this->wpdb->last_error : 'unknown')),
            esc_html__('Check that the database user has CREATE, DROP, ALTER and RENAME privileges.', 'siterelay'),
            false,
            ['sql' => substr($sql, 0, 300)]
        );
    }
}