<?php

namespace App\Services;

use Generator;
use RuntimeException;

/** Read account data from mysqldump SQL; never execute SQL from the backup. */
class AzerothCoreAccountDumpReader
{
    /** @return Generator<int, array{table: string, row: array<string, string|null>}> */
    public function rows(string $path, string $sourceDatabase = 'acore_auth'): Generator
    {
        if (! is_file($path) || ! is_readable($path)) {
            throw new RuntimeException('The auth backup file is not readable.');
        }
        $stream = fopen($path, 'rb');
        if ($stream === false) {
            throw new RuntimeException('Could not open the auth backup.');
        }
        $compressed = fread($stream, 2) === "\x1f\x8b";
        if ($compressed) {
            fclose($stream);
            $stream = fopen('compress.zlib://'.$path, 'rb');
        } else {
            rewind($stream);
        }
        if ($stream === false) {
            throw new RuntimeException('Could not open the auth backup.');
        }

        $database = null;
        $schemas = [];
        $pending = '';
        $kind = $table = null;
        try {
            while (($line = fgets($stream)) !== false) {
                if ($pending === '') {
                    if (preg_match('/^\s*USE\s+`([^`]+)`\s*;/i', $line, $match)) {
                        $database = $match[1];

                        continue;
                    }
                    if (! preg_match('/^\s*(CREATE TABLE(?: IF NOT EXISTS)?|INSERT(?: IGNORE)? INTO)\s+(?:`([^`]+)`\.)?`(account|account_access)`(?=\s|\()/i', $line, $match)) {
                        continue;
                    }
                    $statementDatabase = $match[2] !== '' ? $match[2] : $database;
                    if ($statementDatabase !== null && $statementDatabase !== $sourceDatabase) {
                        continue;
                    }
                    $kind = str_starts_with(strtoupper($match[1]), 'CREATE') ? 'create' : 'insert';
                    $table = strtolower($match[3]);
                }
                $pending .= $line;
                if (! preg_match('/;\s*$/D', $line)) {
                    continue;
                }
                if ($kind === 'create') {
                    preg_match_all('/(?:\(\s*|,\s*)`([A-Za-z0-9_]+)`\s+[A-Za-z]/', $pending, $columns);
                    $schemas[$table] = $columns[1];
                } else {
                    foreach ($this->insertRows($pending, $schemas[$table] ?? []) as $row) {
                        yield ['table' => $table, 'row' => $row];
                    }
                }
                $pending = '';
            }
            if ($pending !== '') {
                throw new RuntimeException('The auth backup has a truncated account SQL statement.');
            }
        } finally {
            fclose($stream);
        }
    }

    /** @return Generator<int, array<string, string|null>> */
    private function insertRows(string $sql, array $schema): Generator
    {
        if (! preg_match('/^\s*INSERT(?: IGNORE)? INTO\s+(?:`[^`]+`\.)?`(?:account|account_access)`\s*(?:\(([^)]*)\))?\s*VALUES\s*/i', $sql, $header)) {
            throw new RuntimeException('Unsupported account INSERT syntax in the auth backup.');
        }
        $columns = $schema;
        if (isset($header[1]) && trim($header[1]) !== '') {
            $columns = array_map(fn (string $value): string => trim($value, " \t\r\n`"), explode(',', $header[1]));
        }
        if ($columns === [] || count($columns) !== count(array_unique($columns))) {
            throw new RuntimeException('The auth backup must include account table definitions or explicit INSERT column names.');
        }
        $offset = strlen($header[0]);
        $length = strlen($sql);
        while ($offset < $length) {
            $this->skipWhitespace($sql, $offset);
            if (($sql[$offset] ?? '') === ';') {
                $offset++;
                $this->skipWhitespace($sql, $offset);
                if ($offset !== $length) {
                    throw new RuntimeException('Unexpected SQL after the account INSERT.');
                }

                return;
            }
            if (($sql[$offset] ?? '') !== '(') {
                throw new RuntimeException('Malformed account tuple in the auth backup.');
            }
            $offset++;
            $values = [];
            while (true) {
                $values[] = $this->value($sql, $offset);
                $this->skipWhitespace($sql, $offset);
                $delimiter = $sql[$offset++] ?? '';
                if ($delimiter === ')') {
                    break;
                }
                if ($delimiter !== ',') {
                    throw new RuntimeException('Malformed account value in the auth backup.');
                }
            }
            if (count($values) !== count($columns)) {
                throw new RuntimeException('Account column count differs from the auth backup schema.');
            }
            yield array_combine($columns, $values);
            $this->skipWhitespace($sql, $offset);
            if (($sql[$offset] ?? '') === ',') {
                $offset++;
            } elseif (($sql[$offset] ?? '') !== ';') {
                throw new RuntimeException('Malformed account INSERT ending in the auth backup.');
            }
        }
        throw new RuntimeException('Unterminated account INSERT in the auth backup.');
    }

    private function value(string $sql, int &$offset): ?string
    {
        $this->skipWhitespace($sql, $offset);
        if (preg_match('/\G(?:_binary|_utf8mb4|_utf8)\s*/i', $sql, $prefix, 0, $offset)) {
            $offset += strlen($prefix[0]);
        }
        $hexQuoted = strtolower($sql[$offset] ?? '') === 'x' && ($sql[$offset + 1] ?? '') === "'";
        if ($hexQuoted) {
            $offset++;
        }
        $quote = $sql[$offset] ?? '';
        if ($quote === "'" || $quote === '"') {
            $offset++;
            $result = '';
            while ($offset < strlen($sql)) {
                $character = $sql[$offset++];
                if ($character === $quote) {
                    if (($sql[$offset] ?? '') === $quote) {
                        $result .= $quote;
                        $offset++;

                        continue;
                    }
                    if ($hexQuoted) {
                        if (strlen($result) % 2 !== 0 || ($result !== '' && ! ctype_xdigit($result))) {
                            throw new RuntimeException('Invalid quoted binary literal in the auth backup.');
                        }

                        return hex2bin($result);
                    }

                    return $result;
                }
                if ($character === '\\') {
                    $escaped = $sql[$offset++] ?? '';
                    $result .= match ($escaped) {
                        '0' => "\0", 'n' => "\n", 'r' => "\r", 't' => "\t", 'b' => "\x08", 'Z' => "\x1a",
                        '%', '_' => '\\'.$escaped,
                        default => $escaped,
                    };
                } else {
                    $result .= $character;
                }
            }
            throw new RuntimeException('Unterminated account string in the auth backup.');
        }
        $start = $offset;
        while ($offset < strlen($sql) && $sql[$offset] !== ',' && $sql[$offset] !== ')') {
            $offset++;
        }
        $token = trim(substr($sql, $start, $offset - $start));
        if (strcasecmp($token, 'NULL') === 0) {
            return null;
        }
        if (preg_match('/^0x([0-9a-f]+)$/Di', $token, $match) && strlen($match[1]) % 2 === 0) {
            return hex2bin($match[1]);
        }
        if (preg_match('/^-?\d+(?:\.\d+)?$/D', $token)) {
            return $token;
        }
        throw new RuntimeException('Unsupported account value literal in the auth backup.');
    }

    private function skipWhitespace(string $sql, int &$offset): void
    {
        while ($offset < strlen($sql) && ctype_space($sql[$offset])) {
            $offset++;
        }
    }
}