"""SQLite-Schicht. Aller Pipeline-Zustand lebt HIER (Resume-Prinzip: jeder Schritt
ist idempotent, weil er nur offene Status-Werte weiterverarbeitet).

Synchronen Zugriff bewusst: DB-Arbeit ist gegen LLM-Latenz vernachlässigbar.
`on_change` (von main.py gesetzt) meldet Statuswechsel ans Live-Board."""

import json
import re
import sqlite3
import threading
from pathlib import Path

from config import DB_PATH

on_change = None  # callable(tabelle: str, row: dict) | None — nie fatal

_lock = threading.RLock()
_con: sqlite3.Connection | None = None


def connect(path: Path | str = DB_PATH) -> sqlite3.Connection:
    global _con
    with _lock:
        if _con is not None:
            return _con
        Path(path).parent.mkdir(parents=True, exist_ok=True)
        _con = sqlite3.connect(path, check_same_thread=False)
        _con.row_factory = sqlite3.Row
        _con.execute("PRAGMA journal_mode=WAL")
        _con.execute("PRAGMA foreign_keys=ON")
        _init_schema(_con)
        return _con


def reset_for_tests(path: str = ":memory:") -> None:
    """Frische DB (Tests): bestehende Verbindung verwerfen, neue öffnen."""
    global _con
    with _lock:
        if _con is not None:
            _con.close()
        _con = None
    connect(path)


_SCHEMA = """
CREATE TABLE IF NOT EXISTS topics(
  name TEXT PRIMARY KEY, titel TEXT NOT NULL, art TEXT NOT NULL CHECK(art IN ('thema','uni')),
  status TEXT NOT NULL DEFAULT 'neu', provider TEXT NOT NULL DEFAULT '',
  auto TEXT NOT NULL DEFAULT '{}', erstellt TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS runs(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'running',
  gestartet TEXT DEFAULT (datetime('now')), beendet TEXT, ebene TEXT DEFAULT '',
  git_hash TEXT DEFAULT '', git_dirty INTEGER DEFAULT 0, budget_tokens INTEGER DEFAULT 0, grund TEXT DEFAULT '');
CREATE TABLE IF NOT EXISTS events(
  id INTEGER PRIMARY KEY, run_id INTEGER NOT NULL, ts TEXT DEFAULT (datetime('now')),
  ebene TEXT NOT NULL, stage TEXT NOT NULL, item TEXT DEFAULT '', template TEXT DEFAULT '',
  template_hash TEXT DEFAULT '', provider TEXT DEFAULT '', model TEXT DEFAULT '', role TEXT DEFAULT '',
  status TEXT NOT NULL, dur_ms INTEGER DEFAULT 0, wait_ms INTEGER DEFAULT 0,
  tok_in INTEGER DEFAULT 0, tok_out INTEGER DEFAULT 0, tok_cache_read INTEGER DEFAULT 0,
  tok_cache_write INTEGER DEFAULT 0, meta TEXT DEFAULT '{}');
CREATE INDEX IF NOT EXISTS idx_events_run ON events(run_id);
CREATE TABLE IF NOT EXISTS quellen(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, art TEXT NOT NULL CHECK(art IN ('web','datei')),
  titel TEXT NOT NULL, url TEXT DEFAULT '', snapshot TEXT NOT NULL, hash TEXT DEFAULT '',
  runde INTEGER DEFAULT 0, status TEXT NOT NULL DEFAULT 'neu',
  rolle TEXT NOT NULL DEFAULT 'stoff', atome_stand TEXT NOT NULL DEFAULT '');
CREATE TABLE IF NOT EXISTS soll(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, punkt TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'kandidat', belege TEXT NOT NULL DEFAULT '[]',
  intro TEXT NOT NULL DEFAULT '', geprueft TEXT NOT NULL DEFAULT '[]',
  thema_id INTEGER);
CREATE TABLE IF NOT EXISTS themen(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, titel TEXT NOT NULL,
  ord INTEGER DEFAULT 0, art TEXT NOT NULL DEFAULT 'judge');
CREATE TABLE IF NOT EXISTS atome(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, titel TEXT NOT NULL, typ TEXT NOT NULL DEFAULT '',
  definition TEXT NOT NULL DEFAULT '', level TEXT NOT NULL DEFAULT 'M',
  status TEXT NOT NULL DEFAULT 'neu', soll_id INTEGER, ziel_id INTEGER, baustein_id INTEGER,
  ord INTEGER DEFAULT 0, merged_into INTEGER);
CREATE INDEX IF NOT EXISTS idx_atome_topic ON atome(topic);
CREATE TABLE IF NOT EXISTS anker(
  id INTEGER PRIMARY KEY, atom_id INTEGER NOT NULL, quelle_id INTEGER NOT NULL,
  start INTEGER NOT NULL, ende INTEGER NOT NULL, zitat TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS idx_anker_atom ON anker(atom_id);
CREATE TABLE IF NOT EXISTS kanten(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, von_atom INTEGER NOT NULL, zu_atom INTEGER NOT NULL,
  art TEXT NOT NULL CHECK(art IN ('braucht','verwandt')), status TEXT NOT NULL DEFAULT 'aktiv',
  UNIQUE(von_atom, zu_atom, art));
CREATE TABLE IF NOT EXISTS artefakte(
  id INTEGER PRIMARY KEY, atom_id INTEGER NOT NULL,
  typ TEXT NOT NULL CHECK(typ IN ('flashcard','beispiel','aussage')),
  inhalt TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'kandidat');
CREATE INDEX IF NOT EXISTS idx_artefakte_atom ON artefakte(atom_id);
CREATE TABLE IF NOT EXISTS lernziele(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, text TEXT NOT NULL, soll_id INTEGER,
  titel TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'neu');
CREATE TABLE IF NOT EXISTS bausteine(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, ziel_id INTEGER NOT NULL, titel TEXT NOT NULL,
  ord INTEGER DEFAULT 0, status TEXT NOT NULL DEFAULT 'neu', kapitel_id INTEGER,
  level TEXT NOT NULL DEFAULT 'M', ordnung TEXT NOT NULL DEFAULT 'judge',
  thema_id INTEGER);
CREATE TABLE IF NOT EXISTS kapitel(
  id INTEGER PRIMARY KEY, topic TEXT NOT NULL, titel TEXT NOT NULL,
  intro TEXT NOT NULL DEFAULT '', ord INTEGER DEFAULT 0,
  art TEXT NOT NULL DEFAULT 'judge', level TEXT NOT NULL DEFAULT 'M');
CREATE TABLE IF NOT EXISTS sections(
  baustein_id INTEGER PRIMARY KEY, stage TEXT NOT NULL DEFAULT 'writer',
  text_kompakt TEXT DEFAULT '', text_lang TEXT DEFAULT '', befunde TEXT DEFAULT '[]',
  qa_hash TEXT DEFAULT '', fix_versuche INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS leitner(
  artefakt_id INTEGER PRIMARY KEY, box INTEGER NOT NULL DEFAULT 1,
  faellig TEXT DEFAULT (date('now')), historie TEXT DEFAULT '[]');
CREATE TABLE IF NOT EXISTS befunde(
  id INTEGER PRIMARY KEY, run_id INTEGER NOT NULL, ebene TEXT NOT NULL, art TEXT NOT NULL,
  item TEXT DEFAULT '', detail TEXT DEFAULT '', status TEXT NOT NULL DEFAULT 'offen');
CREATE INDEX IF NOT EXISTS idx_befunde_run ON befunde(run_id, ebene);
CREATE TABLE IF NOT EXISTS lernstand(
  topic TEXT NOT NULL, baustein_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'aktiv', xp INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY(topic, baustein_id));
CREATE TABLE IF NOT EXISTS auftraege(
  id INTEGER PRIMARY KEY, baustein_id INTEGER NOT NULL,
  art TEXT NOT NULL CHECK(art IN ('falsch','luecke','stil')),
  detail TEXT NOT NULL, quelle TEXT NOT NULL DEFAULT 'pruefer',
  status TEXT NOT NULL DEFAULT 'offen'
    CHECK(status IN ('offen','behoben','kein_mangel','eskaliert')),
  runden INTEGER NOT NULL DEFAULT 0,
  geklaert INTEGER NOT NULL DEFAULT 0);
CREATE INDEX IF NOT EXISTS idx_auftraege ON auftraege(baustein_id, status);
"""

# Tabellen, deren Änderungen das Live-Board interessieren.
_LIVE_TABELLEN = {"topics", "runs", "quellen", "soll", "atome", "artefakte",
                  "lernziele", "bausteine", "kapitel", "sections", "befunde", "themen"}


def _init_schema(con: sqlite3.Connection) -> None:
    con.executescript(_SCHEMA)
    # Migration für Bestands-DBs: CREATE IF NOT EXISTS ergänzt keine neuen Spalten.
    for zusatz in ("ALTER TABLE soll ADD COLUMN intro TEXT NOT NULL DEFAULT ''",
                   "ALTER TABLE lernziele ADD COLUMN titel TEXT NOT NULL DEFAULT ''",
                   "ALTER TABLE quellen ADD COLUMN rolle TEXT NOT NULL DEFAULT 'stoff'",
                   "ALTER TABLE sections ADD COLUMN qa_hash TEXT DEFAULT ''",
                   "ALTER TABLE topics ADD COLUMN auto TEXT NOT NULL DEFAULT '{}'",
                   "ALTER TABLE topics ADD COLUMN resets TEXT NOT NULL DEFAULT '{}'",
                   "ALTER TABLE bausteine ADD COLUMN kapitel_id INTEGER",
                   "ALTER TABLE kapitel ADD COLUMN art TEXT NOT NULL DEFAULT 'judge'",
                   "ALTER TABLE bausteine ADD COLUMN level TEXT NOT NULL DEFAULT 'M'",
                   "ALTER TABLE kapitel ADD COLUMN level TEXT NOT NULL DEFAULT 'M'",
                   "ALTER TABLE soll ADD COLUMN geprueft TEXT NOT NULL DEFAULT '[]'",
                   "ALTER TABLE quellen ADD COLUMN atome_stand TEXT NOT NULL DEFAULT ''",
                   "ALTER TABLE sections ADD COLUMN fix_versuche INTEGER NOT NULL DEFAULT 0",
                   "ALTER TABLE bausteine ADD COLUMN ordnung TEXT NOT NULL DEFAULT 'judge'",
                   # Themen-Schicht: Soll-Punkte tragen die Struktur (Refactoring Stufe 1)
                   "ALTER TABLE soll ADD COLUMN thema_id INTEGER",
                   "ALTER TABLE bausteine ADD COLUMN thema_id INTEGER",
                   # Klärungs-Stichentscheid: eskalierte Aufträge einmalig klären
                   "ALTER TABLE auftraege ADD COLUMN geklaert INTEGER NOT NULL DEFAULT 0",
                   # braucht-Maschinerie entfernt: Ordnung kommt vom Judge,
                   # nicht mehr vom (zu dünnen) Abhängigkeits-Graph
                   "ALTER TABLE atome DROP COLUMN braucht"):
        try:
            con.execute(zusatz)
        except sqlite3.OperationalError:
            pass  # Spalte existiert schon (bzw. ist schon weg)
    con.execute("DELETE FROM kanten WHERE art='braucht'")  # tote Daten, kein Leser mehr
    # Status-Marker entfernter Ebenen (diagramme/fehlersuche) zurückrollen — sonst
    # kennt _ORDNUNG den Status nicht und der Resume fiele fälschlich auf korpus zurück.
    con.execute("UPDATE topics SET status='struktur_fertig'"
                " WHERE status IN ('diagramme_fertig', 'fehlersuche_fertig')")
    # Befunde entfernter Ebenen schließen — sie würden sonst ewig als „offen" im Board stehen
    con.execute("UPDATE befunde SET status='veraltet'"
                " WHERE status='offen' AND ebene IN ('diagramme', 'fehlersuche')")
    # CHECK-Migration: Bestands-DBs kennen typ='aussage' nicht (SQLite kann CHECKs
    # nicht ändern) → Tabelle einmalig neu aufbauen. Erkennung über sqlite_master.
    alt = con.execute("SELECT sql FROM sqlite_master WHERE type='table'"
                      " AND name='artefakte'").fetchone()
    if alt and "'aussage'" not in alt[0]:
        con.executescript(
            "ALTER TABLE artefakte RENAME TO artefakte_alt;"
            "CREATE TABLE artefakte("
            "  id INTEGER PRIMARY KEY, atom_id INTEGER NOT NULL,"
            "  typ TEXT NOT NULL CHECK(typ IN ('flashcard','beispiel','aussage')),"
            "  inhalt TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'kandidat');"
            "INSERT INTO artefakte SELECT * FROM artefakte_alt;"
            "DROP TABLE artefakte_alt;"
            "CREATE INDEX IF NOT EXISTS idx_artefakte_atom ON artefakte(atom_id);")
    # Auftrags-Migration: Freitext-JSON in sections.befunde → auftraege-Zeilen.
    # Nur KRITISCH-Arten überleben — det-/Stil-Aufträge werden ohnehin jede Runde
    # frisch berechnet, und die Prüfer-Paraphrasen waren genau die Krankheit.
    if any(sp[1] == "befunde" for sp in con.execute("PRAGMA table_info(sections)")):
        for row in con.execute("SELECT baustein_id, befunde FROM sections"
                               " WHERE befunde NOT IN ('', '[]')").fetchall():
            try:
                alte = json.loads(row[1])
            except ValueError:
                alte = []
            for a in alte:
                p = alt_auftrag(a)
                if p and not con.execute(
                        "SELECT 1 FROM auftraege WHERE baustein_id=? AND detail=?",
                        (row[0], p[1])).fetchone():
                    con.execute("INSERT INTO auftraege(baustein_id, art, detail, quelle)"
                                " VALUES(?,?,?,?)", (row[0], *p))
        con.execute("ALTER TABLE sections DROP COLUMN befunde")
    con.commit()


_ALT_AUFTRAG = re.compile(r"^KRITISCH \((falsch|luecke|fachlich_falsch)\): (.+)$", re.S)


def alt_auftrag(text) -> tuple[str, str, str] | None:
    """Alt-Format aus sections.befunde → (art, detail, quelle) oder None (verwerfen).
    Auch der Transfer-Import alter Exporte nutzt dies."""
    m = _ALT_AUFTRAG.match(str(text))
    if not m:
        return None
    art, detail = m.group(1), m.group(2).strip()
    return ("falsch" if art == "fachlich_falsch" else art, detail,
            "qa" if art == "fachlich_falsch" else "pruefer")


def _notify(tabelle: str, row: dict) -> None:
    if on_change is not None and tabelle in _LIVE_TABELLEN:
        try:
            on_change(tabelle, row)
        except Exception:
            pass


def _rowdict(row: sqlite3.Row | None) -> dict | None:
    return dict(row) if row is not None else None


def query(sql: str, params: tuple = ()) -> list[dict]:
    con = connect()
    with _lock:
        return [dict(r) for r in con.execute(sql, params).fetchall()]


def one(sql: str, params: tuple = ()) -> dict | None:
    con = connect()
    with _lock:
        return _rowdict(con.execute(sql, params).fetchone())


def execute(sql: str, params: tuple = ()) -> int:
    """→ lastrowid. Kein Notify — für Ledger/Interna."""
    con = connect()
    with _lock:
        cur = con.execute(sql, params)
        con.commit()
        return cur.lastrowid


def insert(tabelle: str, **felder) -> int:
    keys = ", ".join(felder)
    marks = ", ".join("?" * len(felder))
    rid = execute(f"INSERT INTO {tabelle}({keys}) VALUES({marks})", tuple(felder.values()))
    _notify(tabelle, {"id": rid, **felder})
    return rid


def update(tabelle: str, id_feld: str, id_wert, **felder) -> None:
    sets = ", ".join(f"{k}=?" for k in felder)
    execute(f"UPDATE {tabelle} SET {sets} WHERE {id_feld}=?", (*felder.values(), id_wert))
    _notify(tabelle, {id_feld: id_wert, **felder})


def now() -> str:
    """UTC-Zeitstempel im SQLite-datetime('now')-Format (wie runs.gestartet).
    Für Timestamp-Spalten — der Literal-String „jetzt" landete sonst als Müll
    in runs.beendet und machte jede Dauer-Query unbrauchbar."""
    import time
    return time.strftime("%Y-%m-%d %H:%M:%S", time.gmtime())


def j(obj) -> str:
    return json.dumps(obj, ensure_ascii=False)


def uj(text: str | None, default=None):
    if not text:
        return default if default is not None else []
    try:
        return json.loads(text)
    except ValueError:
        return default if default is not None else []