#!/usr/bin/env python3

"""Build the static mod-playerbots ruRU SQL update from a reviewed TSV file."""

from __future__ import annotations

import csv
import re
from collections import Counter
from pathlib import Path


ROOT = Path(__file__).resolve().parents[1]
INPUT = ROOT / "playerbots-phrases-ru-reviewed.tsv"
OUTPUT = (
    ROOT
    / "modules/mod-playerbots/data/sql/playerbots/updates/"
    / "2026_07_30_00_ai_playerbot_russian_texts.sql"
)
EXPECTED_FIELDS = ("id", "key", "original_en", "translation_ru")
PLACEHOLDER_RE = re.compile(r"%[A-Za-z_][A-Za-z0-9_]*|<[^>]+>")


def sql_quote(value: str) -> str:
    return "'" + value.replace("\\", "\\\\").replace("'", "\\'") + "'"


def load_rows() -> list[dict[str, str]]:
    with INPUT.open(encoding="utf-8-sig", newline="") as input_file:
        reader = csv.DictReader(input_file, delimiter="\t")
        if tuple(reader.fieldnames or ()) != EXPECTED_FIELDS:
            raise RuntimeError(
                f"Unexpected TSV columns: {reader.fieldnames}; "
                f"expected {EXPECTED_FIELDS}"
            )
        rows = list(reader)

    if not rows:
        raise RuntimeError(f"No translations found in {INPUT}")

    seen_ids: set[int] = set()
    for line_number, row in enumerate(rows, start=2):
        try:
            row_id = int(row["id"])
        except ValueError as error:
            raise RuntimeError(
                f"{INPUT}:{line_number}: invalid id {row['id']!r}"
            ) from error

        if row_id in seen_ids:
            raise RuntimeError(f"{INPUT}:{line_number}: duplicate id {row_id}")
        seen_ids.add(row_id)

        if not row["key"]:
            raise RuntimeError(f"{INPUT}:{line_number}: empty key")
        if not row["translation_ru"].strip():
            raise RuntimeError(f"{INPUT}:{line_number}: empty translation")

        source_placeholders = Counter(PLACEHOLDER_RE.findall(row["original_en"]))
        target_placeholders = Counter(PLACEHOLDER_RE.findall(row["translation_ru"]))
        if source_placeholders != target_placeholders:
            raise RuntimeError(
                f"{INPUT}:{line_number}: placeholder mismatch: "
                f"{source_placeholders} != {target_placeholders}"
            )

    return rows


def write_sql(rows: list[dict[str, str]]) -> None:
    lines = [
        "-- MoonWell static Russian localization for mod-playerbots.",
        f"-- Generated from {INPUT.name}; edit the TSV and rebuild, not this file.",
        "-- Placeholder names and counts are validated; their order may differ in Russian.",
        "SET NAMES utf8mb4;",
        "",
        "START TRANSACTION;",
    ]
    for row in rows:
        lines.append(
            "UPDATE `ai_playerbot_texts` "
            f"SET `text_loc8`={sql_quote(row['translation_ru'])} "
            f"WHERE `id`={int(row['id'])} AND `name`={sql_quote(row['key'])};"
        )
    lines.extend(["COMMIT;", ""])
    OUTPUT.write_text("\n".join(lines), encoding="utf-8")
    print(f"Wrote {len(rows)} reviewed translations to {OUTPUT}")


def main() -> None:
    write_sql(load_rows())


if __name__ == "__main__":
    main()