<?php
namespace Relay\Import;

use Relay\Database\Db;
use Relay\Database\Naming;
use Relay\Restore\SqlGate;
use Relay\Support\RelayError;

/**
 * Decides what each statement of an uploaded SQL dump may do (spec §4.5) — mysqldump, phpMyAdmin or Relay's
 * own export. Only these run, against this site's import tables (_rli_…), never the live ones:
 *   CREATE TABLE (checked like a backup's; foreign keys are left out), INSERT / REPLACE with plain literal
 *   values only, DROP TABLE, and ALTER TABLE adding keys or indexes, changing columns or AUTO_INCREMENT.
 * Tables are only those of the WordPress site in the dump (its table prefix). Everything else — session
 * settings, locks, transactions, views, triggers, routines, other applications' tables, anything unknown —
 * is skipped and counted, never run. A data statement for a WordPress table that isn't plain is refused.
 */
final class DumpGate
{
    public const SKIP_SESSION = 'session';
    public const SKIP_ROUTINE = 'routine';
    public const SKIP_OTHER_TABLE = 'other_table';
    public const SKIP_FOREIGN_KEY = 'foreign_key';
    public const SKIP_UNKNOWN = 'unknown';

    private const IDENT = '`((?:[^`]|``)+)`';
    private const NUMBER = '[-+]?(?:\d+(?:\.\d*)?|\.\d+)(?:[eE][-+]?\d+)?';

    /** @var array<string, string> source table => this site's table */
    private array $names;
    /** @var string[] */
    private array $collations;

    /** @param array<string, string> $names the dump's WordPress tables => this site's names */
    public function __construct(array $names, array $collations)
    {
        $this->names = $names;
        $this->collations = $collations;
    }

    /**
     * @return array{kind: string, table: ?string, sql: ?string, reason: ?string, dropped_keys: int}
     *   kind create | insert | drop | alter | skip; table is the dump's table name; sql runs against the import table
     */
    public function check(string $sql): array
    {
        $sql = trim($sql);
        if (preg_match('/^(?:SET|LOCK\s+TABLES?|UNLOCK\s+TABLES?|START\s+TRANSACTION|BEGIN|COMMIT|ROLLBACK|USE|CREATE\s+(?:DATABASE|SCHEMA)|SAVEPOINT|RELEASE\s+SAVEPOINT|FLUSH|ANALYZE|OPTIMIZE)\b/i', $sql)) {
            return self::skip(self::SKIP_SESSION);
        }
        if (preg_match('/^(?:CREATE|DROP|ALTER)\s+(?:OR\s+REPLACE\s+)?(?:ALGORITHM\s*=\s*\w+\s+)?(?:DEFINER\s*=\s*\S+\s+)?(?:SQL\s+SECURITY\s+\w+\s+)?(?:VIEW|TRIGGER|PROCEDURE|FUNCTION|EVENT)\b/i', $sql)) {
            return self::skip(self::SKIP_ROUTINE);
        }
        if (preg_match('/^DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?' . self::IDENT . '\s*$/i', $sql, $m)) {
            $t = self::unquote($m[1]);
            return $this->ours($t) ? self::ok('drop', $t, 'DROP TABLE IF EXISTS ' . Db::ident($this->import($t))) : self::skip(self::SKIP_OTHER_TABLE);
        }
        if (preg_match('/^CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?' . self::IDENT . '\s*\(/i', $sql, $m)) {
            return $this->create($sql, $m);
        }
        if (preg_match('/^(INSERT|REPLACE)\s+(?:LOW_PRIORITY\s+|DELAYED\s+|HIGH_PRIORITY\s+)?(IGNORE\s+)?INTO\s+' . self::IDENT . '\s*/i', $sql, $m)) {
            return $this->insert($sql, $m);
        }
        if (preg_match('/^ALTER\s+TABLE\s+' . self::IDENT . '\s+/i', $sql, $m)) {
            return $this->alter($sql, $m);
        }
        return self::skip(self::SKIP_UNKNOWN);
    }

    /** this site's import table for a dump table */
    public function import(string $table): string
    {
        return Naming::import($this->names[$table]);
    }

    private function create(string $sql, array $m): array
    {
        $t = self::unquote($m[1]);
        if (!$this->ours($t)) {
            return self::skip(self::SKIP_OTHER_TABLE);
        }
        $rest = substr($sql, strlen($m[0]));
        $dropped = 0;
        $rest = (string) preg_replace_callback(
            '/,\s*(?:CONSTRAINT\s+(?:`(?:[^`]|``)+`\s+)?)?FOREIGN\s+KEY\s*(?:`(?:[^`]|``)+`\s*)?\([^)]*\)\s*REFERENCES\s+`(?:[^`]|``)+`\s*\([^)]*\)(?:\s+ON\s+(?:DELETE|UPDATE)\s+(?:RESTRICT|CASCADE|SET\s+NULL|NO\s+ACTION|SET\s+DEFAULT))*/i',
            static function () use (&$dropped): string {
                $dropped++;
                return '';
            },
            $rest
        );
        $body = SqlGate::table_body($rest, $this->collations);
        if ($body === null) {
            throw self::refused($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL; refused() shortens and escapes it in the message
        }
        return self::ok('create', $t, 'CREATE TABLE ' . Db::ident($this->import($t)) . ' (' . $body, $dropped);
    }

    private function insert(string $sql, array $m): array
    {
        $t = self::unquote($m[3]);
        if (!$this->ours($t)) {
            return self::skip(self::SKIP_OTHER_TABLE);
        }
        $p = strlen($m[0]);
        $columns = '';
        if (preg_match('/\G\(\s*`(?:[^`]|``)+`(?:\s*,\s*`(?:[^`]|``)+`)*\s*\)\s*/', $sql, $c, 0, $p)) {
            $columns = trim($c[0]) . ' ';
            $p += strlen($c[0]);
        }
        if (!preg_match('/\GVALUES?\s*/i', $sql, $v, 0, $p)) {
            throw self::refused($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL; refused() shortens and escapes it in the message
        }
        $p += strlen($v[0]);
        $values_at = $p;
        if (!self::tuples($sql, $p)) {
            throw self::refused($sql); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- raw SQL; refused() shortens and escapes it in the message
        }
        $verb = strtoupper($m[1]) . ($m[2] !== '' ? ' IGNORE' : '');
        return self::ok('insert', $t, $verb . ' INTO ' . Db::ident($this->import($t)) . ' ' . $columns . 'VALUES ' . substr($sql, $values_at));
    }

    /** Tuples of plain literals from $p to the end: (v, v), (v, v) */
    private static function tuples(string $s, int $p): bool
    {
        $n = strlen($s);
        while (true) {
            if (!preg_match('/\G\s*\(\s*/', $s, $m, 0, $p)) {
                return false;
            }
            $p += strlen($m[0]);
            while (true) {
                $p = self::value($s, $p);
                if ($p < 0) {
                    return false;
                }
                if (!preg_match('/\G\s*([,)])\s*/', $s, $m, 0, $p)) {
                    return false;
                }
                $p += strlen($m[0]);
                if ($m[1] === ')') {
                    break;
                }
            }
            if ($p >= $n) {
                return true;
            }
            if ($s[$p] !== ',') {
                return false;
            }
            $p++;
        }
    }

    /**
     * Position after one literal at $p — quoted string (with an optional _charset introducer), number, NULL,
     * TRUE/FALSE, hex or bit literal, or the two forms Relay's own export writes for binary and point columns,
     * UNHEX('…') and ST_GeomFromText('POINT(x y)') — or -1.
     */
    private static function value(string $s, int $p): int
    {
        // Hex and bit literals before numbers: "0x1F" must not be read as the number 0.
        $patterns = [
            "(?:_[A-Za-z0-9]+\\s*)?'(?:[^'\\\\]++|\\\\.|'')*+'",
            '(?:_[A-Za-z0-9]+\\s*)?"(?:[^"\\\\]++|\\\\.|"")*+"',
            "(?:_[A-Za-z0-9]+\\s*)?(?:0x[0-9A-Fa-f]+|[xX]'[0-9A-Fa-f]*'|[bB]'[01]*'|0b[01]+)",
            "UNHEX\\(\\s*'[0-9A-Fa-f]*'\\s*\\)",
            "ST_GeomFromText\\(\\s*'POINT\\(\\s*" . self::NUMBER . '\\s+' . self::NUMBER . "\\s*\\)'\\s*\\)",
            self::NUMBER,
            'NULL\b|TRUE\b|FALSE\b',
        ];
        foreach ($patterns as $pattern) {
            if (preg_match('/\G(?:' . $pattern . ')/si', $s, $m, 0, $p)) {
                return $p + strlen($m[0]);
            }
        }
        return -1;
    }

    /**
     * ALTER TABLE: keys and indexes, column changes and AUTO_INCREMENT are kept (phpMyAdmin adds keys this way
     * after the data); DISABLE/ENABLE KEYS and foreign keys are dropped. Anything else skips the statement.
     */
    private function alter(string $sql, array $m): array
    {
        $t = self::unquote($m[1]);
        if (!$this->ours($t)) {
            return self::skip(self::SKIP_OTHER_TABLE);
        }
        $kept = [];
        $dropped = 0;
        foreach (self::clauses(substr($sql, strlen($m[0]))) as $clause) {
            $bare = (string) preg_replace(["/'(?:[^'\\\\]++|\\\\.)*+'/s", '/`(?:[^`]++|``)*+`/'], ["''", '``'], $clause);
            if (preg_match('/^(?:DISABLE|ENABLE)\s+KEYS$/i', $clause)) {
                continue;
            }
            if (preg_match('/^ADD\s+(?:CONSTRAINT\s+(?:`(?:[^`]|``)+`\s+)?)?FOREIGN\s+KEY\b/i', $clause)) {
                $dropped++;
                continue;
            }
            $allowed = preg_match('/^(?:ADD\s+(?:CONSTRAINT\s+(?:`(?:[^`]|``)+`\s+)?)?(?:PRIMARY\s+KEY|UNIQUE(?:\s+(?:KEY|INDEX))?|KEY|INDEX|FULLTEXT(?:\s+(?:KEY|INDEX))?|SPATIAL(?:\s+(?:KEY|INDEX))?)|MODIFY(?:\s+COLUMN)?|CHANGE(?:\s+COLUMN)?|AUTO_INCREMENT\s*=?\s*\d+$)/i', $clause);
            if (!$allowed || preg_match('/\b(SELECT|REFERENCES|DATA\s+DIRECTORY|INDEX\s+DIRECTORY|RENAME|TABLESPACE)\b|;/i', $bare)) {
                return self::skip(self::SKIP_UNKNOWN);
            }
            $kept[] = $clause;
        }
        if ($kept === []) {
            return ['dropped_keys' => $dropped] + self::skip($dropped > 0 ? self::SKIP_FOREIGN_KEY : self::SKIP_SESSION);
        }
        return self::ok('alter', $t, 'ALTER TABLE ' . Db::ident($this->import($t)) . ' ' . implode(', ', $kept), $dropped);
    }

    /** @return string[] top-level comma-separated parts (commas inside parentheses or quotes don't split) */
    private static function clauses(string $s): array
    {
        $out = [];
        $depth = 0;
        $start = 0;
        $n = strlen($s);
        for ($i = 0; $i < $n; $i++) {
            $c = $s[$i];
            if ($c === "'" || $c === '"' || $c === '`') {
                $end = $c === '`' ? strpos($s, '`', $i + 1) : self::string_end($s, $i, $c);
                if ($end === false) {
                    break;
                }
                $i = $end;
            } elseif ($c === '(') {
                $depth++;
            } elseif ($c === ')') {
                $depth--;
            } elseif ($c === ',' && $depth === 0) {
                $out[] = trim(substr($s, $start, $i - $start));
                $start = $i + 1;
            }
        }
        $out[] = trim(substr($s, $start));
        return array_values(array_filter($out, static fn ($c) => $c !== ''));
    }

    /** @return int|false position of the closing quote */
    private static function string_end(string $s, int $i, string $q)
    {
        $n = strlen($s);
        for ($j = $i + 1; $j < $n; $j++) {
            if ($s[$j] === '\\') {
                $j++;
            } elseif ($s[$j] === $q) {
                if (($s[$j + 1] ?? '') === $q) {
                    $j++;
                    continue;
                }
                return $j;
            }
        }
        return false;
    }

    private function ours(string $table): bool
    {
        return isset($this->names[$table]);
    }

    private static function unquote(string $quoted): string
    {
        return str_replace('``', '`', $quoted);
    }

    private static function ok(string $kind, string $table, string $sql, int $dropped = 0): array
    {
        return ['kind' => $kind, 'table' => $table, 'sql' => $sql, 'reason' => null, 'dropped_keys' => $dropped];
    }

    private static function skip(string $reason): array
    {
        return ['kind' => 'skip', 'table' => null, 'sql' => null, 'reason' => $reason, 'dropped_keys' => 0];
    }

    private static function refused(string $sql): RelayError
    {
        return new RelayError(
            'import.statement_rejected',
            /* translators: %s: start of the refused SQL statement */
            esc_html(sprintf(__('The SQL file contains a statement SiteRelay will not run: %s', 'siterelay'), substr($sql, 0, 120))),
            esc_html__('SiteRelay imports plain table definitions and rows. Export the database again with mysqldump, phpMyAdmin or SiteRelay.', 'siterelay')
        );
    }
}