<?php

namespace App\Support;

use RuntimeException;

final class LegacySqlDumpReader
{
    /**
     * Stream INSERT rows from a phpMyAdmin/MySQL dump without executing SQL.
     *
     * @param callable(string, array<string, mixed>): void $onRow
     */
    public static function read(string $path, callable $onRow): void
    {
        $handle = fopen($path, 'rb');
        if ($handle === false) {
            throw new RuntimeException("Unable to open SQL dump: {$path}");
        }

        $statement = '';
        $capturing = false;

        try {
            while (($line = fgets($handle)) !== false) {
                if (!$capturing) {
                    if (!str_starts_with(ltrim($line), 'INSERT INTO `')) {
                        continue;
                    }
                    $capturing = true;
                    $statement = '';
                }

                $statement .= $line;
                if (str_ends_with(rtrim($line), ';')) {
                    self::parseInsert($statement, $onRow);
                    $statement = '';
                    $capturing = false;
                }
            }

            if ($capturing) {
                throw new RuntimeException('The SQL dump ended inside an INSERT statement.');
            }
        } finally {
            fclose($handle);
        }
    }

    /** @param callable(string, array<string, mixed>): void $onRow */
    private static function parseInsert(string $statement, callable $onRow): void
    {
        if (!preg_match('/^\s*INSERT INTO `([^`]+)`\s*\((.*?)\)\s*VALUES\s*(.*);\s*$/s', $statement, $matches)) {
            throw new RuntimeException('Unsupported INSERT statement encountered in legacy dump.');
        }

        $table = $matches[1];
        preg_match_all('/`([^`]+)`/', $matches[2], $columnMatches);
        $columns = $columnMatches[1];

        foreach (self::parseTuples($matches[3]) as $values) {
            if (count($values) !== count($columns)) {
                throw new RuntimeException("Column/value mismatch while parsing {$table}.");
            }
            $onRow($table, array_combine($columns, $values));
        }
    }

    /** @return \Generator<int, array<int, mixed>> */
    private static function parseTuples(string $input): \Generator
    {
        $length = strlen($input);
        $position = 0;

        while ($position < $length) {
            self::skipWhitespaceAndCommas($input, $position, $length);
            if ($position >= $length) {
                break;
            }
            if ($input[$position] !== '(') {
                throw new RuntimeException("Expected tuple at byte {$position}.");
            }
            $position++;
            $values = [];

            while (true) {
                self::skipWhitespace($input, $position, $length);
                $values[] = self::parseValue($input, $position, $length);
                self::skipWhitespace($input, $position, $length);

                if ($position >= $length) {
                    throw new RuntimeException('Unterminated tuple in legacy dump.');
                }
                if ($input[$position] === ',') {
                    $position++;
                    continue;
                }
                if ($input[$position] === ')') {
                    $position++;
                    break;
                }
                throw new RuntimeException("Unexpected tuple delimiter at byte {$position}.");
            }

            yield $values;
        }
    }

    private static function parseValue(string $input, int &$position, int $length): mixed
    {
        if ($position < $length && $input[$position] === "'") {
            return self::parseQuotedString($input, $position, $length);
        }

        $start = $position;
        while ($position < $length && $input[$position] !== ',' && $input[$position] !== ')') {
            $position++;
        }
        $raw = trim(substr($input, $start, $position - $start));

        if (strcasecmp($raw, 'NULL') === 0) {
            return null;
        }
        if (preg_match('/^-?\d+$/', $raw)) {
            return (int) $raw;
        }
        if (is_numeric($raw)) {
            return (float) $raw;
        }

        return $raw;
    }

    private static function parseQuotedString(string $input, int &$position, int $length): string
    {
        $position++;
        $value = '';
        $escapes = [
            '0' => "\0", 'n' => "\n", 'r' => "\r", 't' => "\t",
            'b' => "\x08", 'Z' => "\x1a", "'" => "'", '"' => '"', '\\' => '\\',
        ];

        while ($position < $length) {
            $char = $input[$position++];
            if ($char === '\\') {
                if ($position >= $length) {
                    throw new RuntimeException('Dangling escape in SQL string.');
                }
                $escaped = $input[$position++];
                $value .= $escapes[$escaped] ?? $escaped;
                continue;
            }
            if ($char === "'") {
                if ($position < $length && $input[$position] === "'") {
                    $value .= "'";
                    $position++;
                    continue;
                }
                return $value;
            }
            $value .= $char;
        }

        throw new RuntimeException('Unterminated SQL string.');
    }

    private static function skipWhitespace(string $input, int &$position, int $length): void
    {
        while ($position < $length && ctype_space($input[$position])) {
            $position++;
        }
    }

    private static function skipWhitespaceAndCommas(string $input, int &$position, int $length): void
    {
        while ($position < $length && (ctype_space($input[$position]) || $input[$position] === ',')) {
            $position++;
        }
    }
}
