#!/usr/bin/env python3

"""Build ruRU locale overlays from reviewed French-to-Russian CSV rows."""

from __future__ import annotations

import csv
import re
from pathlib import Path


ROOT = Path(__file__).resolve().parents[1]
SOURCE_CSV = ROOT / "tools" / "ChatGPT WoW localisation source.csv"
OUT_DIR = ROOT / "sql" / "sylvania" / "locales"
INVALID = {"", "Загрузка..."}

BROADCAST_ROW = re.compile(
    r"^\((\d+),'frFR','((?:''|[^'])*)','((?:''|[^'])*)',(\d+)\)(?:,|;)$"
)
TMP_BROADCAST_ROW = re.compile(r"^\((\d+), '((?:''|[^'])*)'\)(?:,|;)$")
CREATURE_ROW = re.compile(
    r"^\((\d+),(\d+),(\d+),'frFR','((?:''|[^'])*)'\)(?:,|;)$"
)


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


def translations() -> dict[str, str]:
    result: dict[str, str] = {}
    with SOURCE_CSV.open(encoding="utf-8-sig", newline="") as source:
        for row in csv.DictReader(source):
            russian = row["russian_text"].strip()
            if row["kind"] == "literal" and russian not in INVALID:
                result[row["french_text"]] = russian
    return result


def build_hotfixes(translated: dict[str, str]) -> int:
    rows: dict[int, tuple[str, str, int]] = {}
    dynamic_rows: dict[int, str] = {}
    source = ROOT / "sql/sylvania/broadcast_text_frFR_client.sql"
    for line in source.read_text(encoding="utf-8").splitlines():
        match = BROADCAST_ROW.match(line)
        if not match:
            continue
        entry, male, female, build = match.groups()
        male_ru = translated.get(male, "") if male else ""
        female_ru = translated.get(female, "") if female else ""
        if male_ru or female_ru:
            rows[int(entry)] = (male_ru, female_ru, int(build))

    source = ROOT / "sql/sylvania/mardum_fr_broadcast.sql"
    for line in source.read_text(encoding="utf-8").splitlines():
        match = TMP_BROADCAST_ROW.match(line)
        if not match or match.group(2) not in translated:
            continue
        entry = int(match.group(1))
        dynamic_rows[entry] = translated[match.group(2)]

    output = OUT_DIR / "ruRU_from_french_hotfixes.sql"
    with output.open("w", encoding="utf-8", newline="\n") as sql:
        sql.write("-- Generated from ChatGPT WoW localisation source.csv.\n")
        sql.write("-- Apply to dc_hotfixes. Existing frFR rows are not modified.\n\n")
        sql.write("SET NAMES utf8mb4;\n\n")
        sql.write("INSERT INTO `broadcast_text_locale` "
                  "(`ID`,`locale`,`Text_lang`,`Text1_lang`,`VerifiedBuild`) VALUES\n")
        values = []
        for entry, (male, female, build) in sorted(rows.items()):
            values.append(
                f"({entry},'ruRU',{sql_quote(male)},{sql_quote(female)},{build})"
            )
        sql.write(",\n".join(values))
        sql.write("\nON DUPLICATE KEY UPDATE\n")
        sql.write(" `Text_lang`=IF(VALUES(`Text_lang`)<>'',VALUES(`Text_lang`),`Text_lang`),\n")
        sql.write(" `Text1_lang`=IF(VALUES(`Text1_lang`)<>'',VALUES(`Text1_lang`),`Text1_lang`),\n")
        sql.write(" `VerifiedBuild`=VALUES(`VerifiedBuild`);\n")
        sql.write("\nDROP TEMPORARY TABLE IF EXISTS `tmp_ruru_broadcast`;\n")
        sql.write("CREATE TEMPORARY TABLE `tmp_ruru_broadcast` "
                  "(`id` INT PRIMARY KEY, `text_ru` TEXT) CHARACTER SET utf8mb4;\n")
        sql.write("INSERT INTO `tmp_ruru_broadcast` (`id`,`text_ru`) VALUES\n")
        sql.write(",\n".join(
            f"({entry},{sql_quote(text)})" for entry, text in sorted(dynamic_rows.items())
        ))
        sql.write(";\n\nINSERT INTO `broadcast_text_locale` "
                  "(`ID`,`locale`,`Text_lang`,`Text1_lang`,`VerifiedBuild`)\n")
        sql.write("SELECT r.`id`,'ruRU',\n"
                  " CASE WHEN b.`Text` IS NOT NULL AND b.`Text`<>'' THEN r.`text_ru` ELSE '' END,\n"
                  " CASE WHEN b.`Text1` IS NOT NULL AND b.`Text1`<>'' THEN r.`text_ru` ELSE '' END,0\n"
                  "FROM `tmp_ruru_broadcast` r JOIN `broadcast_text` b ON b.`ID`=r.`id`\n"
                  "ON DUPLICATE KEY UPDATE\n"
                  " `Text_lang`=IF(VALUES(`Text_lang`)<>'',VALUES(`Text_lang`),`Text_lang`),\n"
                  " `Text1_lang`=IF(VALUES(`Text1_lang`)<>'',VALUES(`Text1_lang`),`Text1_lang`),\n"
                  " `VerifiedBuild`=VALUES(`VerifiedBuild`);\n"
                  "DROP TEMPORARY TABLE `tmp_ruru_broadcast`;\n")
    return len(rows) + len(dynamic_rows)


def build_world(translated: dict[str, str]) -> int:
    rows: dict[tuple[int, int, int], str] = {}
    source = ROOT / "sql/sylvania/mardum_fr_creature_text.sql"
    for line in source.read_text(encoding="utf-8").splitlines():
        match = CREATURE_ROW.match(line)
        if not match or match.group(4) not in translated:
            continue
        creature, group, entry, french = match.groups()
        rows[(int(creature), int(group), int(entry))] = translated[french]

    output = OUT_DIR / "ruRU_from_french_world.sql"
    with output.open("w", encoding="utf-8", newline="\n") as sql:
        sql.write("-- Generated from ChatGPT WoW localisation source.csv.\n")
        sql.write("-- Apply to dc_world. Existing frFR rows are not modified.\n\n")
        sql.write("SET NAMES utf8mb4;\n\n")
        sql.write("INSERT INTO `creature_text_locale` "
                  "(`CreatureID`,`GroupID`,`ID`,`Locale`,`Text`) VALUES\n")
        values = []
        for (creature, group, entry), text in sorted(rows.items()):
            values.append(
                f"({creature},{group},{entry},'ruRU',{sql_quote(text)})"
            )
        sql.write(",\n".join(values))
        sql.write("\nON DUPLICATE KEY UPDATE `Text`=VALUES(`Text`);\n\n")

        # These two source rows are assembled dynamically in the French module.
        replacements = [
            (97058, 1, 0, [("begins to cast", "начинает произносить"),
                            ("[Carrion Storm]", translated.get("[Tempête de charogne]", "[Буря падали]"))]),
            (97059, 2, 0, [("Nearby", "Ближайшие"),
                            ("[Spider Eggs]", translated.get("[Œufs d’araignée]", "[Паучьи яйца]")),
                            ("will hatch soon if not destroyed!.", "скоро вылупятся, если их не уничтожить!")]),
        ]
        for creature, group, entry, pairs in replacements:
            expression = "ct.`Text`"
            for old, new in pairs:
                expression = f"REPLACE({expression}, {sql_quote(old)}, {sql_quote(new)})"
            sql.write(
                "INSERT INTO `creature_text_locale` (`CreatureID`,`GroupID`,`ID`,`Locale`,`Text`)\n"
                f"SELECT ct.`CreatureID`,ct.`GroupID`,ct.`ID`,'ruRU',{expression}\n"
                f"FROM `creature_text` ct WHERE ct.`CreatureID`={creature} "
                f"AND ct.`GroupID`={group} AND ct.`ID`={entry}\n"
                "ON DUPLICATE KEY UPDATE `Text`=VALUES(`Text`);\n\n"
            )

        gossip_fr = (
            "Whitemoon, je vais vous emprunter une de vos chauves-souris gangrenées. "
            "Nous allons chercher ce pour quoi nous sommes venus."
        )
        if gossip_fr in translated:
            sql.write(
                "INSERT INTO `gossip_menu_option_locale` "
                "(`MenuId`,`OptionIndex`,`Locale`,`OptionText`,`BoxText`) VALUES\n"
                f"(18776,0,'ruRU',{sql_quote(translated[gossip_fr])},'')\n"
                "ON DUPLICATE KEY UPDATE `OptionText`=VALUES(`OptionText`);\n"
            )
    return len(rows) + 3


def main() -> None:
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    translated = translations()
    hotfixes = build_hotfixes(translated)
    world = build_world(translated)
    print(f"hotfixes rows: {hotfixes}")
    print(f"world rows: {world}")


if __name__ == "__main__":
    main()