"""OK ITHAKI local translation dictionary (R10F8).

Persistent user/community corrections used before and after Argos.  The data is
stored outside release folders on production so upgrades never delete it.
"""
from __future__ import annotations

import csv
import os
import re
import sqlite3
import threading
import time
import unicodedata
from pathlib import Path
from typing import Any


def _data_dir() -> Path:
    configured = os.getenv("ITHAKI_TRANSLATION_DATA_DIR", "").strip()
    if configured:
        return Path(configured).expanduser().resolve()
    if Path("/opt/ithaki").exists():
        return Path("/opt/ithaki/shared/translation")
    return Path(__file__).resolve().parent / "data" / "translation"


DICT_DIR = _data_dir()
DB_PATH = DICT_DIR / "local_dictionary_v249526r10f8.sqlite3"
UNKNOWN_CSV = DICT_DIR / "unknown_translation_terms.csv"
_LOCK = threading.RLock()


def norm_lang(code: str) -> str:
    code = str(code or "auto").strip().lower().replace("_", "-")
    aliases = {
        "gr": "el", "greek": "el", "ell": "el", "el-gr": "el",
        "eng": "en", "english": "en", "en-gb": "en", "en-us": "en",
        "ger": "de", "deu": "de", "german": "de",
        "fre": "fr", "fra": "fr", "french": "fr",
        "ita": "it", "italian": "it", "spa": "es", "spanish": "es",
        "por": "pt", "portuguese": "pt", "dut": "nl", "nld": "nl",
        "nor": "no", "swe": "sv", "dan": "da", "fin": "fi",
        "rum": "ro", "ron": "ro", "ukr": "uk", "tur": "tr",
    }
    return aliases.get(code, code.split("-")[0])


def norm_key_loose(value: str) -> str:
    value = unicodedata.normalize("NFKD", str(value or "")).casefold()
    value = "".join(c for c in value if not unicodedata.combining(c))
    value = re.sub(r"[^\w\s'-]+", " ", value, flags=re.UNICODE)
    return re.sub(r"\s+", " ", value).strip()


def _connect() -> sqlite3.Connection:
    DICT_DIR.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(str(DB_PATH), timeout=20)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA synchronous=NORMAL")
    con.executescript(
        """
        CREATE TABLE IF NOT EXISTS translations(
          src_lang TEXT NOT NULL,
          dst_lang TEXT NOT NULL,
          source_key TEXT NOT NULL,
          source TEXT NOT NULL,
          target TEXT NOT NULL,
          kind TEXT NOT NULL DEFAULT 'word',
          use_count INTEGER NOT NULL DEFAULT 0,
          created_at REAL NOT NULL,
          updated_at REAL NOT NULL,
          PRIMARY KEY(src_lang,dst_lang,source_key)
        );
        CREATE INDEX IF NOT EXISTS idx_translation_pair
          ON translations(src_lang,dst_lang,updated_at DESC);
        """
    )
    return con


def _ensure_unknown_csv() -> None:
    DICT_DIR.mkdir(parents=True, exist_ok=True)
    if not UNKNOWN_CSV.exists():
        UNKNOWN_CSV.write_text(
            "created_at,src_lang,dst_lang,term,suggested_translation,kind,context,notes\n",
            encoding="utf-8-sig",
        )


def add_custom_translation(source: str, target: str, src_lang: str = "el", dst_lang: str = "en", kind: str = "word") -> dict[str, Any]:
    source, target = str(source or "").strip(), str(target or "").strip()
    src_lang, dst_lang = norm_lang(src_lang), norm_lang(dst_lang)
    kind = "phrase" if str(kind).lower() == "phrase" or " " in source else "word"
    if not source or not target:
        return {"ok": False, "note": "source_and_target_required"}
    if src_lang == dst_lang:
        return {"ok": False, "note": "different_languages_required"}
    key, ts = norm_key_loose(source), time.time()
    if not key:
        return {"ok": False, "note": "invalid_source"}
    with _LOCK, _connect() as con:
        con.execute(
            """INSERT INTO translations(src_lang,dst_lang,source_key,source,target,kind,use_count,created_at,updated_at)
               VALUES(?,?,?,?,?,?,0,?,?)
               ON CONFLICT(src_lang,dst_lang,source_key) DO UPDATE SET
                 source=excluded.source,target=excluded.target,kind=excluded.kind,updated_at=excluded.updated_at""",
            (src_lang, dst_lang, key, source, target, kind, ts, ts),
        )
        con.commit()
    return {"ok": True, "source": source, "target": target, "src_lang": src_lang, "dst_lang": dst_lang, "kind": kind, "provider": "ithaki-lexicon-1-r10f8"}


def _exact(text: str, src: str, dst: str) -> str | None:
    key = norm_key_loose(text)
    if not key:
        return None
    with _LOCK, _connect() as con:
        row = con.execute(
            "SELECT target FROM translations WHERE src_lang=? AND dst_lang=? AND source_key=?",
            (src, dst, key),
        ).fetchone()
        if row:
            con.execute(
                "UPDATE translations SET use_count=use_count+1 WHERE src_lang=? AND dst_lang=? AND source_key=?",
                (src, dst, key),
            )
            con.commit()
            return str(row["target"] or "").strip() or None
    return None


def _replace_known_phrases(text: str, src: str, dst: str) -> tuple[str, int]:
    """Apply longer learned phrases, then exact learned words."""
    with _LOCK, _connect() as con:
        rows = con.execute(
            "SELECT source,target,kind FROM translations WHERE src_lang=? AND dst_lang=? ORDER BY length(source) DESC LIMIT 5000",
            (src, dst),
        ).fetchall()
    out, changed = text, 0
    for row in rows:
        source, target = str(row["source"] or "").strip(), str(row["target"] or "").strip()
        if not source or not target:
            continue
        if str(row["kind"]) == "phrase" or " " in source:
            pattern = re.compile(r"(?<!\w)" + re.escape(source) + r"(?!\w)", re.IGNORECASE | re.UNICODE)
            out2, n = pattern.subn(target, out)
            if n:
                out, changed = out2, changed + n
    def repl(match: re.Match[str]) -> str:
        nonlocal changed
        token = match.group(0)
        hit = _exact(token, src, dst)
        if not hit:
            return token
        changed += 1
        if token[:1].isupper() and hit:
            return hit[:1].upper() + hit[1:]
        return hit
    out = re.sub(r"[^\W\d_][\w'’-]*", repl, out, flags=re.UNICODE)
    return out, changed


def translate_with_local_db(text: str, src_lang: str = "auto", dst_lang: str = "en") -> dict[str, Any]:
    text = str(text or "")
    src, dst = norm_lang(src_lang), norm_lang(dst_lang)
    if not text.strip() or src == dst:
        return {"ok": True, "translated": text, "provider": "ithaki-lexicon-same", "source_lang": src, "target_lang": dst}
    direct = _exact(text, src, dst)
    if direct:
        return {"ok": True, "translated": direct, "provider": "ithaki-lexicon-exact-r10f8", "source_lang": src, "target_lang": dst}
    out, changed = _replace_known_phrases(text, src, dst)
    return {
        "ok": True,
        "translated": out,
        "provider": "ithaki-lexicon-partial-r10f8" if changed else "ithaki-lexicon-no-change-r10f8",
        "source_lang": src,
        "target_lang": dst,
        "changed_terms": changed,
    }


def log_unknown_terms(text: str, src_lang: str, dst_lang: str, suggested_translation: str = "", context: str = "", notes: str = "") -> dict[str, Any]:
    _ensure_unknown_csv()
    text = str(text or "").strip()
    src, dst = norm_lang(src_lang), norm_lang(dst_lang)
    if not text or src == dst:
        return {"ok": True, "added": 0, "path": str(UNKNOWN_CSV)}
    # Whole phrases are useful for manual correction; also record unchanged lexical tokens.
    terms: list[tuple[str, str]] = []
    suggested = str(suggested_translation or "").strip()
    if not suggested or norm_key_loose(suggested) == norm_key_loose(text):
        terms.append((text[:800], "phrase" if " " in text else "word"))
    else:
        translated_keys = set(re.findall(r"[^\W\d_][\w'’-]*", norm_key_loose(suggested), flags=re.UNICODE))
        for token in re.findall(r"[^\W\d_][\w'’-]*", text, flags=re.UNICODE):
            k = norm_key_loose(token)
            if len(k) > 1 and k in translated_keys:
                terms.append((token[:180], "word"))
    # De-duplicate against recent CSV rows and learned dictionary.
    existing: set[tuple[str, str, str]] = set()
    try:
        with UNKNOWN_CSV.open("r", encoding="utf-8-sig", errors="replace", newline="") as fh:
            for row in list(csv.DictReader(fh))[-5000:]:
                existing.add((norm_lang(row.get("src_lang", "")), norm_lang(row.get("dst_lang", "")), norm_key_loose(row.get("term", ""))))
    except Exception:
        pass
    added = 0
    with _LOCK, UNKNOWN_CSV.open("a", encoding="utf-8-sig", newline="") as fh:
        writer = csv.writer(fh)
        for term, kind in terms:
            key = (src, dst, norm_key_loose(term))
            if not key[2] or key in existing or _exact(term, src, dst):
                continue
            writer.writerow([int(time.time()), src, dst, term, suggested[:800], kind, str(context or "")[:1000], str(notes or "")[:500]])
            existing.add(key)
            added += 1
    return {"ok": True, "added": added, "path": str(UNKNOWN_CSV)}


def dictionary_status() -> dict[str, Any]:
    _ensure_unknown_csv()
    with _LOCK, _connect() as con:
        total = int(con.execute("SELECT COUNT(*) c FROM translations").fetchone()["c"])
        pairs = [dict(r) for r in con.execute("SELECT src_lang,dst_lang,COUNT(*) entries FROM translations GROUP BY src_lang,dst_lang ORDER BY entries DESC").fetchall()]
    try:
        unknown_rows = max(0, sum(1 for _ in UNKNOWN_CSV.open("r", encoding="utf-8-sig", errors="replace")) - 1)
    except Exception:
        unknown_rows = 0
    return {"ok": True, "version": "V249526R10F8", "directory": str(DICT_DIR), "database": str(DB_PATH), "unknown_file": str(UNKNOWN_CSV), "entries": total, "unknown_rows": unknown_rows, "pairs": pairs}
