#!/usr/bin/env python3

"""Export all playerbot chat phrases for manual Russian translation review."""

from __future__ import annotations

import csv
import subprocess
from pathlib import Path


ROOT = Path(__file__).resolve().parents[1]
OUTPUT = ROOT / "doc" / "playerbots-phrases-ru-review.tsv"


def load_rows() -> list[tuple[str, str, str, str]]:
    sql = (
        "SELECT id,HEX(name),HEX(text),HEX(COALESCE(text_loc8,'')) "
        "FROM ai_playerbot_texts ORDER BY id"
    )
    command = [
        "docker",
        "compose",
        "exec",
        "-T",
        "ac-database",
        "bash",
        "-lc",
        f'mysql -N -s -uroot -p"$MYSQL_ROOT_PASSWORD" '
        f'acore_playerbots -e "{sql}" 2>/dev/null',
    ]
    output = subprocess.check_output(command, cwd=ROOT, text=True)
    rows: list[tuple[str, str, str, str]] = []

    for line in output.splitlines():
        parts = line.split("\t")
        if len(parts) != 4:
            continue
        row_id, name_hex, source_hex, russian_hex = parts
        rows.append(
            (
                row_id,
                bytes.fromhex(name_hex).decode("utf-8"),
                bytes.fromhex(source_hex).decode("utf-8", errors="replace"),
                bytes.fromhex(russian_hex).decode("utf-8", errors="replace"),
            )
        )

    return rows


def main() -> None:
    rows = load_rows()
    OUTPUT.parent.mkdir(parents=True, exist_ok=True)
    with OUTPUT.open("w", encoding="utf-8", newline="") as output:
        writer = csv.writer(output, delimiter="\t", quoting=csv.QUOTE_MINIMAL)
        writer.writerow(("id", "key", "original_en", "translation_ru"))
        writer.writerows(rows)

    print(f"Wrote {len(rows)} phrases to {OUTPUT}")


if __name__ == "__main__":
    main()