#!/usr/bin/env python3
"""Convert MySQL 8 dump to MariaDB 10.11 compatible SQL with FK-safe table order."""

import re
import sys
from collections import defaultdict, deque
from pathlib import Path

INPUT = Path(r"c:\Users\LENOVO\Downloads\hse_wtht_mdia.sql")
OUTPUT = Path(r"c:\Users\LENOVO\Downloads\hse_wtht_mdia_mariadb.sql")

FK_RE = re.compile(
    r"FOREIGN KEY\s*\([^)]+\)\s*REFERENCES\s*`([^`]+)`",
    re.IGNORECASE,
)
TABLE_HEADER_RE = re.compile(
    r"--\s*\n-- Table structure for table `([^`]+)`\s*\n--",
)
DROP_RE = re.compile(r"^DROP TABLE IF EXISTS `([^`]+)`;", re.MULTILINE)


def parse_table_blocks(content: str) -> dict[str, str]:
    """Split dump into per-table blocks (from comment header through UNLOCK TABLES)."""
    markers = list(TABLE_HEADER_RE.finditer(content))
    blocks: dict[str, str] = {}

    for i, match in enumerate(markers):
        table_name = match.group(1)
        start = match.start()
        end = markers[i + 1].start() if i + 1 < len(markers) else content.find(
            "/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"
        )
        if end == -1:
            end = len(content)
        blocks[table_name] = content[start:end].rstrip() + "\n"

    return blocks


def extract_dependencies(block: str) -> set[str]:
    return set(FK_RE.findall(block))


def topological_sort(tables: dict[str, str]) -> list[str]:
    deps: dict[str, set[str]] = {}
    for name, block in tables.items():
        refs = extract_dependencies(block)
        deps[name] = {r for r in refs if r in tables and r != name}

    in_degree = {name: 0 for name in tables}
    dependents: dict[str, set[str]] = defaultdict(set)

    for name, refs in deps.items():
        for ref in refs:
            dependents[ref].add(name)
            in_degree[name] += 1

    queue = deque(sorted(name for name, deg in in_degree.items() if deg == 0))
    order: list[str] = []

    while queue:
        node = queue.popleft()
        order.append(node)
        for child in sorted(dependents[node]):
            in_degree[child] -= 1
            if in_degree[child] == 0:
                queue.append(child)

    if len(order) != len(tables):
        remaining = [t for t in tables if t not in order]
        print(
            f"Warning: circular FK dependencies among {remaining}; appending in original order.",
            file=sys.stderr,
        )
        seen = set(order)
        for name in tables:
            if name not in seen:
                order.append(name)

    return order


def mariadb_transform(text: str) -> str:
    text = text.replace("utf8mb4_0900_ai_ci", "utf8mb4_unicode_ci")
    text = re.sub(r"/\*!80016 DEFAULT ENCRYPTION='N' \*/", "", text)
    text = re.sub(r"/\*!50503 SET character_set_client = utf8mb4 \*/;", "", text)
    return text


def build_header() -> str:
    return """CREATE DATABASE IF NOT EXISTS `hse` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `hse`;
-- Converted for MariaDB 10.11.x from MySQL 8.0 dump
-- Collation: utf8mb4_unicode_ci (replaces utf8mb4_0900_ai_ci)
-- Tables and inserts ordered for FOREIGN_KEY_CHECKS=1

SET NAMES utf8mb4;
SET TIME_ZONE='+00:00';
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET SQL_NOTES=0;

"""


def build_footer() -> str:
    return """
SET TIME_ZONE='+00:00';
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
SET SQL_NOTES=1;

-- Dump converted for MariaDB 10.11.x
"""


def main() -> None:
    # latin-1 preserves binary BLOB data in INSERT statements
    content = INPUT.read_text(encoding="latin-1")
    blocks = parse_table_blocks(content)

    if not blocks:
        print("No table blocks found.", file=sys.stderr)
        sys.exit(1)

    order = topological_sort(blocks)

    parts = [build_header()]
    for table in order:
        parts.append(mariadb_transform(blocks[table]))
    parts.append(build_footer())

    OUTPUT.write_text("".join(parts), encoding="latin-1")
    print(f"Wrote {len(order)} tables to {OUTPUT}")
    print("Table order (first 10):", ", ".join(order[:10]), "...")


if __name__ == "__main__":
    main()