"""Lokaler Daten-Layer: spiegelt ElevenLabs-Gespräche in SQLite + Vokabel-Tracking.

Das ist das detaillierte Tracking (#3): jedes Gespräch, jeder Turn, jede gelernte
Vokabel wird gespeichert und vom Eltern-Dashboard (#2/#6) gelesen.
"""
from __future__ import annotations

import re
import sqlite3
import threading
import time
import unicodedata
from pathlib import Path

from . import config, convai, learning

DB = config.DATA_DIR / "plappi.sqlite"
_score_lock = threading.Lock()

# Serbisch->Deutsch Kinder-Vokabular (für Tracking; erweiterbar)
VOCAB_SR_DE = {
    "pas": "Hund", "mačka": "Katze", "ptica": "Vogel", "riba": "Fisch", "konj": "Pferd",
    "krava": "Kuh", "zec": "Hase", "medved": "Bär", "lav": "Löwe", "slon": "Elefant",
    "zdravo": "Hallo", "ćao": "Tschüss", "hvala": "Danke", "molim": "Bitte", "da": "Ja", "ne": "Nein",
    "mama": "Mama", "tata": "Papa", "baka": "Oma", "deka": "Opa", "beba": "Baby",
    "crveno": "rot", "plavo": "blau", "žuto": "gelb", "zeleno": "grün", "crno": "schwarz", "belo": "weiß",
    "jedan": "eins", "dva": "zwei", "tri": "drei", "četiri": "vier", "pet": "fünf",
    "voda": "Wasser", "hleb": "Brot", "mleko": "Milch", "jabuka": "Apfel", "sunce": "Sonne",
    "mesec": "Mond", "cvet": "Blume", "kuća": "Haus", "auto": "Auto", "lopta": "Ball",
}


def _norm(s: str) -> str:
    s = unicodedata.normalize("NFKD", s.lower())
    return "".join(c for c in s if not unicodedata.combining(c))


# normalisierte Lookup-Map (macka -> mačka)
_NORM_MAP = {_norm(k): k for k in VOCAB_SR_DE}


def _conn():
    c = sqlite3.connect(DB)
    c.row_factory = sqlite3.Row
    return c


def init():
    c = _conn()
    c.executescript("""
    CREATE TABLE IF NOT EXISTS conversations(
        id TEXT PRIMARY KEY, started_at INT, duration_secs INT, num_turns INT, synced_at INT);
    CREATE TABLE IF NOT EXISTS turns(
        conv_id TEXT, idx INT, role TEXT, text TEXT, PRIMARY KEY(conv_id, idx));
    CREATE TABLE IF NOT EXISTS vocab(
        word_sr TEXT PRIMARY KEY, word_de TEXT, times_taught INT, times_practiced INT,
        first_seen INT, last_seen INT);
    """)
    c.commit(); c.close()


def _find_words(text: str) -> list[str]:
    n = _norm(text)
    found = []
    for nk, sr in _NORM_MAP.items():
        if re.search(rf"\b{re.escape(nk)}\b", n):
            found.append(sr)
    return found


def sync() -> dict:
    """Holt neue/aktualisierte Gespräche von ElevenLabs, speichert Turns, baut Vokabeln neu."""
    init()
    if not config.ELEVENLABS_AGENT_ID:
        return {"ok": False, "error": "kein ELEVENLABS_AGENT_ID"}
    c = _conn()
    convs = convai.list_conversations()
    pulled = 0
    for meta in convs:
        cid = meta.get("conversation_id") or meta.get("id")
        if not cid:
            continue
        try:
            full = convai.get_conversation(cid)
        except Exception:
            continue
        turns = convai.transcript_turns(full)
        m = full.get("metadata", {}) if isinstance(full, dict) else {}
        started = (meta.get("start_time_unix_secs") or m.get("start_time_unix_secs") or 0)
        dur = (meta.get("call_duration_secs") or m.get("call_duration_secs") or 0)
        c.execute("INSERT OR REPLACE INTO conversations VALUES(?,?,?,?,?)",
                  (cid, started, dur, len(turns), int(time.time())))
        c.execute("DELETE FROM turns WHERE conv_id=?", (cid,))
        for i, t in enumerate(turns):
            c.execute("INSERT OR REPLACE INTO turns VALUES(?,?,?,?)", (cid, i, t["role"], t["text"]))
        pulled += 1
    c.commit()
    _rebuild_vocab(c)
    c.commit(); c.close()
    # Mastery-Bewertung (LLM, ~100s/Gespräch) im HINTERGRUND — blockiert das Dashboard nicht.
    threading.Thread(target=_background_score, daemon=True).start()
    return {"ok": True, "conversations": pulled}


def _background_score():
    """Bewertet noch nicht ausgewertete Gespräche (begrenzt) und aktualisiert danach
    den Agent-Prompt mit den neuen Fokus-Wörtern. Läuft asynchron, mit Lock gegen Parallelläufe."""
    if not _score_lock.acquire(blocking=False):
        return
    try:
        learning.init()
        c = _conn()
        rows = c.execute("""SELECT conv.id AS id, conv.started_at AS started_at
                            FROM conversations conv
                            LEFT JOIN scored_convs s ON s.conv_id = conv.id
                            WHERE s.conv_id IS NULL
                            ORDER BY conv.started_at ASC LIMIT 8""").fetchall()
        todo = [(r["id"], r["started_at"]) for r in rows]
        c.close()
        newly = 0
        for cid, started in todo:
            c2 = _conn()
            turns = [{"role": t["role"], "text": t["text"]}
                     for t in c2.execute("SELECT role, text FROM turns WHERE conv_id=? ORDER BY idx", (cid,)).fetchall()]
            c2.close()
            try:
                res = learning.apply_conversation(cid, started or 0, turns)
                if res.get("ok") and not res.get("skipped"):
                    newly += 1
            except Exception as e:  # noqa: BLE001
                print(f"[score] {cid} fehlgeschlagen: {e}", flush=True)
        if newly:
            try:
                from . import profile
                profile.push_to_agent(profile.get_profile())
                print(f"[score] {newly} Gespräch(e) bewertet, Agent-Prompt aktualisiert.", flush=True)
            except Exception as e:  # noqa: BLE001
                print(f"[score] Prompt-Aktualisierung fehlgeschlagen: {e}", flush=True)
    finally:
        _score_lock.release()


def _rebuild_vocab(c):
    c.execute("DELETE FROM vocab")
    agg = {}  # sr -> [taught, practiced, first, last]
    rows = c.execute("""SELECT t.role, t.text, conv.started_at
                        FROM turns t JOIN conversations conv ON conv.id=t.conv_id
                        ORDER BY conv.started_at, t.idx""").fetchall()
    for r in rows:
        ts = r["started_at"] or 0
        for sr in _find_words(r["text"]):
            a = agg.setdefault(sr, [0, 0, ts or None, ts or None])
            if r["role"] == "plappi":
                a[0] += 1
            else:
                a[1] += 1
            if ts:
                a[2] = min(a[2] or ts, ts)
                a[3] = max(a[3] or ts, ts)
    for sr, a in agg.items():
        c.execute("INSERT OR REPLACE INTO vocab VALUES(?,?,?,?,?,?)",
                  (sr, VOCAB_SR_DE.get(sr, ""), a[0], a[1], a[2], a[3]))


def dashboard_data() -> dict:
    init()
    c = _conn()
    convs = c.execute("SELECT * FROM conversations ORDER BY started_at DESC").fetchall()
    vocab = c.execute("SELECT * FROM vocab ORDER BY times_practiced DESC, times_taught DESC").fetchall()
    total_secs = sum(r["duration_secs"] or 0 for r in convs)
    out_convs = []
    for cv in convs[:30]:
        turns = c.execute("SELECT role, text FROM turns WHERE conv_id=? ORDER BY idx", (cv["id"],)).fetchall()
        out_convs.append({
            "id": cv["id"], "started_at": cv["started_at"], "duration_secs": cv["duration_secs"],
            "num_turns": cv["num_turns"], "turns": [{"role": t["role"], "text": t["text"]} for t in turns],
        })
    c.close()
    try:
        mastery = learning.mastery_summary()
    except Exception as e:  # noqa: BLE001
        print(f"[dashboard] mastery_summary failed: {e}", flush=True)
        mastery = {"level": 0, "cefr": "pre-A1", "counts": {}, "words": []}
    return {
        "stats": {
            "conversations": len(convs),
            "minutes": round(total_secs / 60, 1),
            "words_taught": sum(1 for v in vocab if v["times_taught"] > 0),
            "words_practiced": sum(1 for v in vocab if v["times_practiced"] > 0),
        },
        "mastery": mastery,
        "vocab": [{"sr": v["word_sr"], "de": v["word_de"], "taught": v["times_taught"],
                   "practiced": v["times_practiced"]} for v in vocab],
        "conversations": out_convs,
    }