<?php

declare(strict_types=1);

namespace PpPhp\Core\Database;

/**
 * Best-effort, rule-based SQL dialect converter. Rewrites the constructs that
 * appear in real-world schema + seed dumps — data types, identifier quoting,
 * auto-increment/serial/identity, schema prefixes, a few common functions and
 * table options — from a source engine to a target engine.
 *
 * It is deliberately NOT a full SQL parser. Anything it cannot safely rewrite
 * (stored-procedure `$$` bodies, upserts, sequences, extensions, ENUM types on
 * engines that lack them) is left as-is and surfaced as a WARNING so the operator
 * reviews it. The result is always presented as a reviewable diff — never run
 * automatically. Think of it as a very good first draft, not a guarantee.
 *
 * String literals, comments and dollar-quoted blocks are masked before any rule
 * runs, so a keyword inside a string value is never rewritten.
 */
final class SqlDialectConverter
{
    private const FAMILY = [
        'mysql' => 'mysql', 'mariadb' => 'mysql', 'tidb' => 'mysql',
        'pgsql' => 'pgsql', 'cockroachdb' => 'pgsql',
        'sqlsrv' => 'sqlsrv', 'oci' => 'oci', 'sqlite' => 'sqlite',
    ];
    private const NAME = ['mysql' => 'MySQL', 'pgsql' => 'PostgreSQL', 'sqlsrv' => 'SQL Server', 'sqlite' => 'SQLite', 'oci' => 'Oracle'];

    public static function family(string $id): string
    {
        return self::FAMILY[strtolower($id)] ?? strtolower($id);
    }

    public static function name(string $family): string
    {
        return self::NAME[$family] ?? ucfirst($family);
    }

    /**
     * Guess the source dialect family from tell-tale constructs. Returns null when
     * nothing distinctive is found. Conservative: needs the winner to lead.
     */
    public static function detect(string $sql): ?string
    {
        $sig = [
            'pgsql' => ['/\buuid\b/i', '/\bserial\b/i', '/\bbigserial\b/i', '/\btimestamptz\b/i', '/\bjsonb\b/i', '/gen_random_uuid\s*\(/i', '/uuid_generate_v4\s*\(/i', '/\bon\s+conflict\b/i', '/\breturning\b/i', '/\bcreate\s+extension\b/i', '/[^:]::[a-z]/i', '/\$[a-z0-9_]*\$/i', '/\b(public|auth)\.[a-z_]/i', '/\bbytea\b/i'],
            'mysql' => ['/\bauto_increment\b/i', '/\bengine\s*=/i', '/`[^`]+`/', '/\bunsigned\b/i', '/\btinyint\b/i', '/\benum\s*\(/i', '/\bshow\s+tables\b/i', '/\b(longtext|mediumtext)\b/i', '/\bon\s+duplicate\s+key\b/i'],
            'sqlsrv' => ['/\bidentity\s*\(/i', '/\bnvarchar\b/i', '/\bgetdate\s*\(/i', '/\[[a-z_][^\]]*\]/i', '/\btop\s+\d/i', '/\buniqueidentifier\b/i', '/\bdatetime2\b/i'],
            'sqlite' => ['/\bautoincrement\b/i', '/\bpragma\b/i', '/\bwithout\s+rowid\b/i'],
        ];
        $best = null; $bestN = 0;
        foreach ($sig as $fam => $res) {
            $n = 0;
            foreach ($res as $re) { if (preg_match($re, $sql)) { $n++; } }
            if ($n > $bestN) { $bestN = $n; $best = $fam; }
        }
        return $bestN >= 1 ? $best : null;
    }

    /**
     * @return array{sql:string,changes:list<string>,warnings:list<string>,converted:bool,from:string,to:string,fromName:string,toName:string}
     */
    public function convert(string $sql, string $from, string $to): array
    {
        $from = self::family($from);
        $to = self::family($to);
        $result = [
            'sql' => $sql, 'changes' => [], 'warnings' => [], 'converted' => false,
            'from' => $from, 'to' => $to, 'fromName' => self::name($from), 'toName' => self::name($to),
        ];
        if ($from === $to || !isset(self::NAME[$to])) {
            return $result;
        }

        $changes = [];
        $warnings = [];

        // Warnings are detected on the ORIGINAL text (before masking hides bodies).
        $this->collectWarnings($sql, $from, $to, $warnings);

        [$code, $map] = $this->mask($sql, $from);

        $code = $this->convertIdentifierQuoting($code, $from, $to, $changes);
        $code = $this->stripSchemaPrefixes($code, $to, $changes);
        $code = $this->convertAutoIncrement($code, $from, $to, $changes);
        $code = $this->convertDefaults($code, $to, $changes);
        $code = $this->convertTypes($code, $to, $changes);
        $code = $this->convertFunctions($code, $to, $changes);
        $code = $this->convertTableOptions($code, $to, $changes);
        $code = $this->convertIndexes($code, $to, $changes);
        $code = $this->skipUnsupported($code, $to, $changes, $warnings);
        if ($to === 'mysql') {
            $code = $this->fixMysqlTextDefaults($code, $changes);
            $code = $this->fixMysqlAutoIncrementKeys($code, $changes);
            $code = $this->stripInlineForeignKeys($code, $changes, $warnings);
        }

        $out = $this->unmask($code, $map);
        // tidy: collapse the accidental double spaces our deletions can leave
        $out = preg_replace('/[ \t]{2,}/', ' ', $out) ?? $out;
        $out = preg_replace('/\(\s+/', '(', $out) ?? $out;
        $out = preg_replace('/\s+\)/', ')', $out) ?? $out;
        $out = preg_replace('/ +,/', ',', $out) ?? $out;

        $result['sql'] = $out;
        $result['changes'] = array_values(array_unique($changes));
        $result['warnings'] = array_values(array_unique($warnings));
        $result['converted'] = true;
        return $result;
    }

    // ── masking ────────────────────────────────────────────────────────────────
    /** @return array{0:string,1:array<string,string>} */
    private function mask(string $sql, string $from): array
    {
        $map = [];
        $i = 0;
        $store = function (string $s) use (&$map, &$i): string {
            $key = "\x01M" . ($i++) . "\x02";
            $map[$key] = $s;
            return $key;
        };
        // dollar-quoted blocks first (Postgres)
        $sql = preg_replace_callback('/\$([a-z0-9_]*)\$.*?\$\1\$/is', fn($m) => $store($m[0]), $sql) ?? $sql;
        // block + line comments
        $sql = preg_replace_callback('~/\*.*?\*/~s', fn($m) => $store($m[0]), $sql) ?? $sql;
        $sql = preg_replace_callback('/(--|#)[^\n]*/', fn($m) => $store($m[0]), $sql) ?? $sql;
        // single-quoted strings (with '' doubling and backslash escapes)
        $sql = preg_replace_callback("/'(?:[^'\\\\]|\\\\.|'')*'/s", fn($m) => $store($m[0]), $sql) ?? $sql;
        // double-quoted strings are STRINGS only when the source is MySQL; elsewhere
        // they are identifiers we want to re-quote, so leave them unmasked.
        if ($from === 'mysql') {
            $sql = preg_replace_callback('/"(?:[^"\\\\]|\\\\.|"")*"/s', fn($m) => $store($m[0]), $sql) ?? $sql;
        }
        return [$sql, $map];
    }

    private function unmask(string $sql, array $map): string
    {
        return $map ? strtr($sql, $map) : $sql;
    }

    // ── helpers ──────────────────────────────────────────────────────────────
    private function apply(string $code, string $re, string $repl, array &$changes, string $label): string
    {
        $n = 0;
        $out = preg_replace($re, $repl, $code, -1, $n);
        if ($out !== null && $n > 0) {
            $changes[] = $label;
            return $out;
        }
        return $code;
    }

    // ── identifier quoting ─────────────────────────────────────────────────────
    private function convertIdentifierQuoting(string $code, string $from, string $to, array &$changes): string
    {
        $wrap = ['mysql' => ['`', '`'], 'pgsql' => ['"', '"'], 'sqlite' => ['"', '"'], 'sqlsrv' => ['[', ']'], 'oci' => ['"', '"']][$to] ?? ['`', '`'];
        $l = $wrap[0]; $r = $wrap[1];
        $label = 'Identifier quoting → ' . self::name($to) . ' style';

        if ($from === 'mysql') {
            $code = $this->apply($code, '/`([^`]+)`/', $l . '$1' . $r, $changes, $label);
        } elseif ($from === 'sqlsrv') {
            $code = $this->apply($code, '/\[([a-z_][a-z0-9_ ]*)\]/i', $l . '$1' . $r, $changes, $label);
            if ($l !== '"') {
                $code = $this->apply($code, '/"([^"]+)"/', $l . '$1' . $r, $changes, $label);
            }
        } elseif (in_array($from, ['pgsql', 'sqlite', 'oci'], true)) {
            if ($l !== '"') {
                $code = $this->apply($code, '/"([^"]+)"/', $l . '$1' . $r, $changes, $label);
            }
        } else {
            // Generic / unknown / beyond-our-8 source: normalise EVERY quoting style
            // to the target so the script is valid whatever engine it came from.
            $code = $this->apply($code, '/`([^`]+)`/', $l . '$1' . $r, $changes, $label);
            $code = $this->apply($code, '/\[([a-z_][a-z0-9_ ]*)\]/i', $l . '$1' . $r, $changes, $label);
            if ($l !== '"') {
                $code = $this->apply($code, '/"([^"]+)"/', $l . '$1' . $r, $changes, $label);
            }
        }
        return $code;
    }

    private function stripSchemaPrefixes(string $code, string $to, array &$changes): string
    {
        // public. / auth. / dbo. schema qualifiers → drop (each engine has one
        // implicit schema = the connected database).
        return $this->apply($code, '/\b(public|auth|dbo)\.(?=[`"\[]?[a-z_])/i', '', $changes, 'Removed schema prefixes (public./auth./dbo.)');
    }

    // ── auto-increment / serial / identity ─────────────────────────────────────
    private function convertAutoIncrement(string $code, string $from, string $to, array &$changes): string
    {
        if ($to === 'mysql') {
            $code = $this->apply($code, '/\bbigserial\b/i', 'BIGINT AUTO_INCREMENT', $changes, 'bigserial → BIGINT AUTO_INCREMENT');
            $code = $this->apply($code, '/\bserial\b/i', 'INT AUTO_INCREMENT', $changes, 'serial → INT AUTO_INCREMENT');
            $code = $this->apply($code, '/\bidentity\s*\(\s*\d+\s*,\s*\d+\s*\)/i', 'AUTO_INCREMENT', $changes, 'IDENTITY(…) → AUTO_INCREMENT');
            $code = $this->apply($code, '/\bautoincrement\b/i', 'AUTO_INCREMENT', $changes, 'AUTOINCREMENT → AUTO_INCREMENT');
            $code = $this->apply($code, '/\bgenerated\s+(always|by\s+default)\s+as\s+identity\b/i', 'AUTO_INCREMENT', $changes, 'GENERATED … AS IDENTITY → AUTO_INCREMENT');
        } elseif ($to === 'pgsql') {
            // Swallow any MySQL display width — SERIAL/BIGSERIAL take no length.
            $code = $this->apply($code, '/\bbigint\s*(?:\(\s*\d+\s*\))?([^,\n]*?)\bauto_increment\b/i', 'BIGSERIAL$1', $changes, 'BIGINT AUTO_INCREMENT → BIGSERIAL');
            $code = $this->apply($code, '/\b(?:int|integer)\s*(?:\(\s*\d+\s*\))?([^,\n]*?)\bauto_increment\b/i', 'SERIAL$1', $changes, 'INT AUTO_INCREMENT → SERIAL');
            $code = $this->apply($code, '/\bidentity\s*\(\s*\d+\s*,\s*\d+\s*\)/i', 'GENERATED BY DEFAULT AS IDENTITY', $changes, 'IDENTITY(…) → GENERATED … AS IDENTITY');
            $code = $this->apply($code, '/\bauto_increment\b/i', '', $changes, 'Removed AUTO_INCREMENT');
            $code = $this->apply($code, '/\bautoincrement\b/i', '', $changes, 'Removed AUTOINCREMENT');
        } elseif ($to === 'sqlsrv') {
            $code = $this->apply($code, '/\b(?:big)?serial\b/i', 'BIGINT IDENTITY(1,1)', $changes, 'serial → IDENTITY(1,1)');
            $code = $this->apply($code, '/\bauto_increment\b/i', 'IDENTITY(1,1)', $changes, 'AUTO_INCREMENT → IDENTITY(1,1)');
            $code = $this->apply($code, '/\bautoincrement\b/i', 'IDENTITY(1,1)', $changes, 'AUTOINCREMENT → IDENTITY(1,1)');
        } elseif ($to === 'sqlite') {
            $code = $this->apply($code, '/\b(?:big)?serial\b/i', 'INTEGER', $changes, 'serial → INTEGER (use INTEGER PRIMARY KEY AUTOINCREMENT)');
            $code = $this->apply($code, '/\bauto_increment\b/i', 'AUTOINCREMENT', $changes, 'AUTO_INCREMENT → AUTOINCREMENT');
            $code = $this->apply($code, '/\bidentity\s*\(\s*\d+\s*,\s*\d+\s*\)/i', 'AUTOINCREMENT', $changes, 'IDENTITY(…) → AUTOINCREMENT');
        }
        return $code;
    }

    // ── data types ─────────────────────────────────────────────────────────────
    private function convertTypes(string $code, string $to, array &$changes): string
    {
        $t = fn(string $re, string $repl, string $label) => null; // (unused placeholder)
        if ($to === 'mysql') {
            $rules = [
                ['/\buuid\b/i', 'CHAR(36)', 'uuid → CHAR(36)'],
                ['/\btimestamptz\b/i', 'TIMESTAMP', 'timestamptz → TIMESTAMP'],
                ['/\btimestamp\s+with(?:out)?\s+time\s+zone\b/i', 'TIMESTAMP', 'timestamp with/without time zone → TIMESTAMP'],
                ['/\bjsonb\b/i', 'JSON', 'jsonb → JSON'],
                ['/\bboolean\b/i', 'TINYINT(1)', 'boolean → TINYINT(1)'],
                ['/\bbytea\b/i', 'BLOB', 'bytea → BLOB'],
                ['/\bdouble\s+precision\b/i', 'DOUBLE', 'double precision → DOUBLE'],
                ['/\breal\b/i', 'FLOAT', 'real → FLOAT'],
                ['/\bcharacter\s+varying\b/i', 'VARCHAR', 'character varying → VARCHAR'],
                ['/\bnvarchar\b/i', 'VARCHAR', 'nvarchar → VARCHAR'],
                ['/\bnchar\b/i', 'CHAR', 'nchar → CHAR'],
                ['/\bntext\b/i', 'TEXT', 'ntext → TEXT'],
                ['/\bdatetime2\b/i', 'DATETIME', 'datetime2 → DATETIME'],
                ['/\bdatetimeoffset\b/i', 'TIMESTAMP', 'datetimeoffset → TIMESTAMP'],
                ['/\buniqueidentifier\b/i', 'CHAR(36)', 'uniqueidentifier → CHAR(36)'],
                ['/\bbit\b/i', 'TINYINT(1)', 'bit → TINYINT(1)'],
                // Postgres `text` is unlimited + indexable + defaultable; MySQL TEXT
                // is none of those, so map it to VARCHAR(255) (widen long columns by
                // hand). `longtext`/`mediumtext`/`tinytext` are left as-is.
                ['/\btext\b/i', 'VARCHAR(255)', 'text → VARCHAR(255) — widen columns that hold long content'],
            ];
        } elseif ($to === 'pgsql') {
            $rules = [
                ['/\btinyint\s*\(\s*1\s*\)([^,]*?)\bdefault\s+1\b/i', 'BOOLEAN$1DEFAULT true', 'tinyint(1) DEFAULT 1 → BOOLEAN DEFAULT true'],
                ['/\btinyint\s*\(\s*1\s*\)([^,]*?)\bdefault\s+0\b/i', 'BOOLEAN$1DEFAULT false', 'tinyint(1) DEFAULT 0 → BOOLEAN DEFAULT false'],
                ['/\btinyint\s*\(\s*1\s*\)/i', 'BOOLEAN', 'tinyint(1) → BOOLEAN'],
                ['/\bmediumint\b(?:\s*\(\s*\d+\s*\))?/i', 'INTEGER', 'mediumint → INTEGER'],
                ['/\b(bigint|integer|int|smallint)\s*\(\s*\d+\s*\)/i', '$1', 'stripped integer display width (Postgres has none)'],
                ['/\btinyint\b/i', 'SMALLINT', 'tinyint → SMALLINT'],
                ['/\bdatetime\b/i', 'TIMESTAMP', 'datetime → TIMESTAMP'],
                ['/\b(long|medium|tiny)text\b/i', 'TEXT', 'long/medium/tinytext → TEXT'],
                ['/\b(long|medium|tiny)blob\b/i', 'BYTEA', 'long/medium/tinyblob → BYTEA'],
                ['/\bblob\b/i', 'BYTEA', 'blob → BYTEA'],
                ['/\bjson\b/i', 'JSONB', 'json → JSONB'],
                ['/\bdouble\b(?!\s+precision)/i', 'DOUBLE PRECISION', 'double → DOUBLE PRECISION'],
                ['/\bunsigned\b/i', '', 'Removed UNSIGNED (unsupported)'],
                ['/\buniqueidentifier\b/i', 'UUID', 'uniqueidentifier → UUID'],
                ['/\bnvarchar\b/i', 'VARCHAR', 'nvarchar → VARCHAR'],
                ['/\bdatetime2\b/i', 'TIMESTAMP', 'datetime2 → TIMESTAMP'],
                ['/\bbit\b/i', 'BOOLEAN', 'bit → BOOLEAN'],
            ];
        } elseif ($to === 'sqlsrv') {
            $rules = [
                ['/\buuid\b/i', 'UNIQUEIDENTIFIER', 'uuid → UNIQUEIDENTIFIER'],
                ['/\b(long|medium|tiny)?text\b/i', 'NVARCHAR(MAX)', 'text → NVARCHAR(MAX)'],
                ['/\btimestamptz\b/i', 'DATETIME2', 'timestamptz → DATETIME2'],
                ['/\btimestamp\s+with(?:out)?\s+time\s+zone\b/i', 'DATETIME2', 'timestamp w/ time zone → DATETIME2'],
                ['/\bdatetime\b/i', 'DATETIME2', 'datetime → DATETIME2'],
                ['/\bboolean\b/i', 'BIT', 'boolean → BIT'],
                ['/\btinyint\s*\(\s*1\s*\)/i', 'BIT', 'tinyint(1) → BIT'],
                ['/\b(jsonb|json)\b/i', 'NVARCHAR(MAX)', 'json/jsonb → NVARCHAR(MAX)'],
                ['/\bbytea\b/i', 'VARBINARY(MAX)', 'bytea → VARBINARY(MAX)'],
                ['/\b(long|medium|tiny)?blob\b/i', 'VARBINARY(MAX)', 'blob → VARBINARY(MAX)'],
                ['/\bdouble\s+precision\b/i', 'FLOAT', 'double precision → FLOAT'],
                ['/\bunsigned\b/i', '', 'Removed UNSIGNED (unsupported)'],
                ['/\bvarchar\b(?!\s*\()/i', 'VARCHAR(MAX)', 'bare varchar → VARCHAR(MAX)'],
            ];
        } else { // sqlite — permissive typing; normalise the exotic ones to affinities
            $rules = [
                ['/\buuid\b/i', 'TEXT', 'uuid → TEXT'],
                ['/\btimestamptz\b/i', 'TEXT', 'timestamptz → TEXT'],
                ['/\btimestamp\s+with(?:out)?\s+time\s+zone\b/i', 'TEXT', 'timestamp w/ time zone → TEXT'],
                ['/\b(jsonb|json)\b/i', 'TEXT', 'json/jsonb → TEXT'],
                ['/\bboolean\b/i', 'INTEGER', 'boolean → INTEGER'],
                ['/\btinyint\s*\(\s*1\s*\)/i', 'INTEGER', 'tinyint(1) → INTEGER'],
                ['/\bbytea\b/i', 'BLOB', 'bytea → BLOB'],
                ['/\bunsigned\b/i', '', 'Removed UNSIGNED'],
                ['/\bnvarchar\b/i', 'TEXT', 'nvarchar → TEXT'],
                ['/\buniqueidentifier\b/i', 'TEXT', 'uniqueidentifier → TEXT'],
            ];
        }
        foreach ($rules as [$re, $repl, $label]) {
            $code = $this->apply($code, $re, $repl, $changes, $label);
        }
        return $code;
    }

    /**
     * Column DEFAULT clauses need per-engine care — a function default valid in one
     * engine is a syntax error in another (e.g. MySQL rejects DEFAULT UUID()).
     */
    private function convertDefaults(string $code, string $to, array &$changes): string
    {
        if ($to === 'mysql') {
            $code = $this->apply($code, '/\bdefault\s+(?:gen_random_uuid|uuid_generate_v4|newid|uuid)\s*\(\s*\)\s*/i', '', $changes, 'Removed a UUID()/NEWID() column default (MySQL can’t default to it)');
            $code = $this->apply($code, '/\bdefault\s+(?:now|getdate|sysdate)\s*\(\s*\)/i', 'DEFAULT CURRENT_TIMESTAMP', $changes, 'DEFAULT now()/GETDATE() → DEFAULT CURRENT_TIMESTAMP');
            $code = $this->apply($code, '/\bdefault\s+true\b/i', 'DEFAULT 1', $changes, 'DEFAULT TRUE → DEFAULT 1');
            $code = $this->apply($code, '/\bdefault\s+false\b/i', 'DEFAULT 0', $changes, 'DEFAULT FALSE → DEFAULT 0');
        } elseif ($to === 'pgsql') {
            $code = $this->apply($code, '/\bdefault\s+newid\s*\(\s*\)/i', 'DEFAULT gen_random_uuid()', $changes, 'DEFAULT NEWID() → gen_random_uuid()');
            $code = $this->apply($code, '/\bdefault\s+getdate\s*\(\s*\)/i', 'DEFAULT now()', $changes, 'DEFAULT GETDATE() → now()');
        } elseif ($to === 'sqlsrv') {
            $code = $this->apply($code, '/\bdefault\s+(?:gen_random_uuid|uuid_generate_v4|uuid)\s*\(\s*\)/i', 'DEFAULT NEWID()', $changes, 'DEFAULT uuid fn → NEWID()');
            $code = $this->apply($code, '/\bdefault\s+(?:now|current_timestamp)\s*(?:\(\s*\))?/i', 'DEFAULT GETDATE()', $changes, 'DEFAULT now()/CURRENT_TIMESTAMP → GETDATE()');
        } elseif ($to === 'sqlite') {
            $code = $this->apply($code, '/\bdefault\s+(?:gen_random_uuid|uuid_generate_v4|newid|uuid)\s*\(\s*\)\s*/i', '', $changes, 'Removed a UUID() column default (unsupported in SQLite)');
            $code = $this->apply($code, '/\bdefault\s+(?:now|getdate)\s*\(\s*\)/i', 'DEFAULT CURRENT_TIMESTAMP', $changes, 'DEFAULT now()/GETDATE() → CURRENT_TIMESTAMP');
        }
        return $code;
    }

    private function convertFunctions(string $code, string $to, array &$changes): string
    {
        if ($to === 'mysql') {
            $code = $this->apply($code, '/\b(?:gen_random_uuid|uuid_generate_v4)\s*\(\s*\)/i', 'UUID()', $changes, 'gen_random_uuid()/uuid_generate_v4() → UUID()');
            $code = $this->apply($code, '/\bgetdate\s*\(\s*\)/i', 'NOW()', $changes, 'GETDATE() → NOW()');
        } elseif ($to === 'pgsql') {
            $code = $this->apply($code, '/\bUUID\s*\(\s*\)/i', 'gen_random_uuid()', $changes, 'UUID() → gen_random_uuid()');
            $code = $this->apply($code, '/\bgetdate\s*\(\s*\)/i', 'now()', $changes, 'GETDATE() → now()');
        } elseif ($to === 'sqlsrv') {
            $code = $this->apply($code, '/\b(?:now|gen_random_uuid|uuid_generate_v4)\s*\(\s*\)/i', 'GETDATE()', $changes, 'now()/uuid fn → GETDATE()/NEWID()');
        }
        return $code;
    }

    private function convertTableOptions(string $code, string $to, array &$changes): string
    {
        if ($to !== 'mysql') {
            $code = $this->apply($code, '/\bengine\s*=\s*\w+/i', '', $changes, 'Removed ENGINE=… (MySQL-only)');
            $code = $this->apply($code, '/\bdefault\s+charset\s*=\s*\w+/i', '', $changes, 'Removed DEFAULT CHARSET=… (MySQL-only)');
            $code = $this->apply($code, '/\bcollate\s*=?\s*[a-z0-9_]+/i', '', $changes, 'Removed COLLATE=… (MySQL-only)');
        }
        return $code;
    }

    private function convertIndexes(string $code, string $to, array &$changes): string
    {
        // CREATE INDEX IF NOT EXISTS is Postgres/SQLite only.
        if ($to === 'mysql' || $to === 'sqlsrv') {
            $code = $this->apply($code, '/\bcreate\s+((?:unique\s+)?index)\s+if\s+not\s+exists\b/i', 'CREATE $1', $changes, 'CREATE INDEX IF NOT EXISTS → CREATE INDEX (unsupported)');
        }
        // CREATE TABLE IF NOT EXISTS is not valid in SQL Server.
        if ($to === 'sqlsrv') {
            $code = $this->apply($code, '/\bcreate\s+table\s+if\s+not\s+exists\b/i', 'CREATE TABLE', $changes, 'CREATE TABLE IF NOT EXISTS → CREATE TABLE (SQL Server has no IF NOT EXISTS)');
        }
        return $code;
    }

    /** Comment out statements that are meaningless / invalid on the target engine. */
    private function skipUnsupported(string $code, string $to, array &$changes, array &$warnings): string
    {
        if ($to === 'pgsql') {
            return $code;
        }
        $name = self::name($to);
        $hit = false;
        $comment = function (array $m) use (&$hit, $name): string {
            $hit = true;
            return '-- [skipped for ' . $name . '] ' . preg_replace('/\s+/', ' ', trim($m[0]));
        };
        // Postgres extensions
        $before = $code;
        $code = preg_replace_callback('/\bcreate\s+extension\b[^;]*;/i', $comment, $code) ?? $code;
        if ($code !== $before) {
            $warnings[] = 'A CREATE EXTENSION statement was commented out — extensions are PostgreSQL-only.';
        }
        // GIN / GiST indexes (e.g. pg_trgm trigram search)
        $before = $code;
        $code = preg_replace_callback('/\bcreate\s+(?:unique\s+)?index\b[^;]*\busing\s+(?:gin|gist)\b[^;]*;/i', $comment, $code) ?? $code;
        if ($code !== $before) {
            $warnings[] = 'A GIN/GiST index (e.g. trigram search) was commented out — that index type is PostgreSQL-only; use a FULLTEXT index in ' . $name . ' instead.';
        }
        if ($hit) {
            $changes[] = 'Commented out PostgreSQL-only statements (extensions / GIN indexes)';
        }
        return $code;
    }

    /** MySQL forbids DEFAULT on TEXT/BLOB/JSON columns — reconcile that. */
    private function fixMysqlTextDefaults(string $code, array &$changes): string
    {
        // A TEXT column that carries a default is almost always a short value
        // (role, status…) → promote to VARCHAR(255) so the default is legal.
        $code = $this->apply(
            $code,
            '/([`"\[]?[A-Za-z_]\w*[`"\]]?\s+)(?:long|medium|tiny)?text\b([^,()]*?)\bdefault\b/i',
            '$1VARCHAR(255)$2DEFAULT',
            $changes,
            'TEXT column with a DEFAULT → VARCHAR(255) (MySQL forbids TEXT/JSON/BLOB defaults)'
        );
        // JSON/BLOB defaults are rare — drop them.
        $code = $this->apply(
            $code,
            '/(\b(?:json|(?:long|medium|tiny)?blob)\b[^,()]*?)\bdefault\s+(?:\x01M\d+\x02|[^,\s]+)\s*/i',
            '$1',
            $changes,
            'Removed DEFAULT on a JSON/BLOB column (MySQL forbids it)'
        );
        return $code;
    }

    /**
     * MySQL never creates a foreign key from a column-level (inline) REFERENCES —
     * older versions silently ignore it, newer ones reject a mismatched one. Strip
     * inline references so the schema is valid everywhere; table-level FOREIGN KEY
     * constraints are preserved.
     */
    private function stripInlineForeignKeys(string $code, array &$changes, array &$warnings): string
    {
        // Protect valid table-level FK constraints from the inline stripper.
        $protected = [];
        $code = preg_replace_callback(
            '/\bforeign\s+key\s*\([^)]*\)\s*references\s+[`"\[]?\w+[`"\]]?(?:\s*\([^)]*\))?(?:\s+on\s+(?:delete|update)\s+(?:cascade|restrict|no\s+action|set\s+null|set\s+default))*/i',
            function (array $m) use (&$protected): string {
                $k = "\x01F" . count($protected) . "\x02";
                $protected[$k] = $m[0];
                return $k;
            },
            $code
        ) ?? $code;

        $n = 0;
        $code = preg_replace(
            '/\s*\breferences\s+[`"\[]?\w+[`"\]]?(?:\s*\([^)]*\))?(?:\s+on\s+(?:delete|update)\s+(?:cascade|restrict|no\s+action|set\s+null|set\s+default))*/i',
            '',
            $code,
            -1,
            $n
        ) ?? $code;
        if ($n > 0) {
            $changes[] = 'Removed inline foreign-key references (MySQL doesn’t create FKs from them)';
            $warnings[] = 'Inline foreign-key references were removed — MySQL only enforces table-level FOREIGN KEY constraints. Re-add them (or use Tables → Link Tables) if you need referential integrity.';
        }
        if ($protected) {
            $code = strtr($code, $protected);
        }
        return $code;
    }

    /** MySQL needs every AUTO_INCREMENT column to be a key; a converted non-PK serial isn't. */
    private function fixMysqlAutoIncrementKeys(string $code, array &$changes): string
    {
        return $this->apply(
            $code,
            '/\bAUTO_INCREMENT\b(?![^,)]*\bprimary\s+key\b)/i',
            'AUTO_INCREMENT UNIQUE',
            $changes,
            'Non-PK AUTO_INCREMENT column → added UNIQUE (MySQL requires it to be a key)'
        );
    }

    /** Detect things we cannot safely convert and warn (never silently drop logic). */
    private function collectWarnings(string $sql, string $from, string $to, array &$warnings): void
    {
        $checks = [
            '/\$[a-z0-9_]*\$/i' => 'Contains a dollar-quoted body ($$…$$) — stored functions/DO blocks are NOT converted; port them by hand.',
            '/\bon\s+conflict\b/i' => 'Uses PostgreSQL ON CONFLICT (upsert) — rewrite as the target engine’s upsert (e.g. MySQL ON DUPLICATE KEY UPDATE).',
            '/\bon\s+duplicate\s+key\b/i' => 'Uses MySQL ON DUPLICATE KEY UPDATE — rewrite as the target engine’s upsert (e.g. PostgreSQL ON CONFLICT).',
            '/\breturning\b/i' => 'Uses RETURNING — not supported on every engine; review those statements.',
            '/\bcreate\s+extension\b/i' => 'CREATE EXTENSION is PostgreSQL-only and was left as-is — remove it or provide the target equivalent.',
            '/\bcreate\s+type\b/i' => 'CREATE TYPE (custom/enum type) is not portable — was left as-is; define columns inline instead.',
            '/\bcreate\s+sequence\b|\bnextval\s*\(/i' => 'Sequences (CREATE SEQUENCE / nextval) are not portable — use the target’s auto-increment instead.',
            '/\benum\s*\(/i' => 'ENUM(…) exists in MySQL but not Postgres/SQL Server/SQLite — the target may need a CHECK constraint or a lookup table.',
            '/\blimit\b\s+\d+/i' => ($to === 'sqlsrv') ? 'LIMIT is not valid in SQL Server — use TOP n or OFFSET … FETCH.' : '',
        ];
        foreach ($checks as $re => $msg) {
            if ($msg !== '' && preg_match($re, $sql)) {
                $warnings[] = $msg;
            }
        }
        if (in_array($to, ['mysql', 'sqlite'], true) && preg_match('/\bdefault\s+(?:gen_random_uuid|uuid_generate_v4|newid)\s*\(/i', $sql)) {
            $warnings[] = 'A column defaulted to a generated UUID — ' . self::name($to) . ' can’t auto-generate that, so the default was removed. Generate the id in your app (or via a trigger).';
        }
        if ($to === 'mysql' && preg_match('/\btext\b/i', $sql)) {
            $warnings[] = '“text” columns became VARCHAR(255) so MySQL can index and default them — widen any column that stores long content (notes, descriptions) to TEXT or a larger VARCHAR.';
        }
    }
}
