#!/usr/bin/env python3
"""Aria Insight-Register (KAR-588) — wasserdichtes, append-only Verzeichnis aller
Funde (Videos, Tools, Plugins, Patterns, Repos, Skills, Papers).

Prinzip: NICHTS wird je geloescht — nur Status geaendert. Jeder Fund bekommt einen
Eintrag mit future_relevance-Topic-Ankern, damit „jetzt irrelevant, spaeter relevant"
nie verloren geht. Der Harvest-Loop (aria-insight-harvest.py) scannt das GANZE
Register periodisch und re-surfaced passende Items gegen den aktuellen Kontext.

Aufruf:
  python3 aria-insight-register.py ingest          # scannt Brain + wishlist, upsert
  python3 aria-insight-register.py stats           # Coverage + Status-Verteilung
  python3 aria-insight-register.py list --status later --limit 20
"""
from __future__ import annotations
import argparse
import re
import sqlite3
import sys
from datetime import datetime, timezone
from pathlib import Path

BRAIN = Path("/root/aria/brain")
VIDEOS = BRAIN / "00-Inbox" / "Videos"
WISSEN = BRAIN / "02-Wissen"
WISHLIST = WISSEN / "integration-wishlist.md"
DB = Path("/root/aria/state/insight-register.db")

NOW = lambda: datetime.now(timezone.utc).isoformat()

SCHEMA = """
CREATE TABLE IF NOT EXISTS insights (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source_path TEXT UNIQUE NOT NULL,
  kind TEXT NOT NULL,
  title TEXT NOT NULL,
  source_url TEXT,
  found_date TEXT,
  category TEXT,
  status TEXT NOT NULL DEFAULT 'new',
  future_relevance TEXT,
  trigger_note TEXT,
  kar_id TEXT,
  last_reviewed TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_status ON insights(status);
CREATE INDEX IF NOT EXISTS idx_kind ON insights(kind);
-- audit trail: every status change is logged, never lost
CREATE TABLE IF NOT EXISTS insight_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  insight_id INTEGER NOT NULL,
  ts TEXT NOT NULL,
  event TEXT NOT NULL,
  detail TEXT
);
"""

VALID_STATUS = {"new", "later", "active", "implemented", "rejected"}


def db() -> sqlite3.Connection:
    DB.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB)
    conn.executescript(SCHEMA)
    return conn


def parse_frontmatter(text: str) -> dict:
    if not text.startswith("---"):
        return {}
    end = text.find("\n---", 4)
    if end < 0:
        return {}
    out = {}
    for line in text[4:end].splitlines():
        m = re.match(r"^(\w[\w_]*):\s*(.*)$", line)
        if m:
            out[m.group(1)] = m.group(2).strip().strip("\"'")
    return out


STOP = {"der", "die", "das", "und", "fuer", "mit", "von", "the", "and", "for", "with",
        "aria", "kais", "video", "note", "2026", "claude"}


def derive_relevance(fm: dict, title: str) -> str:
    """future_relevance aus Frontmatter, sonst heuristisch aus tags + Titel-Keywords."""
    if fm.get("future_relevance"):
        return fm["future_relevance"].strip("[]")
    anchors = []
    tags = fm.get("tags", "").strip("[]")
    if tags:
        anchors += [t.strip() for t in tags.split(",") if t.strip()]
    # title keywords (kebab, >3 chars, not stopword)
    for w in re.findall(r"[A-Za-z][A-Za-z0-9-]{3,}", title.lower()):
        if w not in STOP and w not in anchors and len(anchors) < 8:
            anchors.append(w)
    uniq = list(dict.fromkeys(a.lower() for a in anchors))
    return ",".join(uniq[:8])


def upsert(conn, source_path: str, kind: str, title: str, **kw):
    """Append-only upsert: insert new, or update mutable fields only (never wipe)."""
    cur = conn.execute("SELECT id, status FROM insights WHERE source_path=?", (source_path,))
    row = cur.fetchone()
    now = NOW()
    if row is None:
        conn.execute(
            """INSERT INTO insights(source_path,kind,title,source_url,found_date,category,
               status,future_relevance,trigger_note,created_at,updated_at)
               VALUES(?,?,?,?,?,?,?,?,?,?,?)""",
            (source_path, kind, title, kw.get("source_url"), kw.get("found_date"),
             kw.get("category"), kw.get("status", "new"), kw.get("future_relevance"),
             kw.get("trigger_note"), now, now))
        iid = conn.execute("SELECT id FROM insights WHERE source_path=?", (source_path,)).fetchone()[0]
        conn.execute("INSERT INTO insight_log(insight_id,ts,event,detail) VALUES(?,?,?,?)",
                     (iid, now, "ingested", f"kind={kind}"))
        return "new"
    else:
        # refresh enrichment fields only (title/relevance can improve), keep status + history
        conn.execute(
            """UPDATE insights SET title=?, source_url=COALESCE(?,source_url),
               future_relevance=COALESCE(?,future_relevance), category=COALESCE(?,category),
               updated_at=? WHERE source_path=?""",
            (title, kw.get("source_url"), kw.get("future_relevance"), kw.get("category"),
             now, source_path))
        return "updated"


def ingest_videos(conn) -> tuple[int, int]:
    new = upd = 0
    if not VIDEOS.exists():
        return 0, 0
    for p in sorted(VIDEOS.glob("*.md")):
        if "pending-synthesis" in p.name:
            continue
        text = p.read_text(errors="replace")
        fm = parse_frontmatter(text)
        title = fm.get("title") or p.stem
        rel = derive_relevance(fm, title)
        # map AKP prioritaet to status: P0/P1 -> later (actionable), P2/P3 -> later, Brain-Entry -> later
        r = upsert(conn, str(p.relative_to(BRAIN)), "video", title[:200],
                   source_url=fm.get("source"), found_date=fm.get("date"),
                   category=fm.get("klassifikation") or fm.get("thema", "")[:60],
                   status="later", future_relevance=rel,
                   trigger_note=fm.get("umsetzungsidee") or fm.get("nutzen", "")[:200])
        new += r == "new"; upd += r == "updated"
    return new, upd


def ingest_wissen(conn) -> tuple[int, int]:
    new = upd = 0
    for p in sorted(WISSEN.glob("*.md")):
        text = p.read_text(errors="replace")
        fm = parse_frontmatter(text)
        typ = fm.get("type", "")
        if typ not in ("audit", "research", "reference"):
            continue
        title = fm.get("title") or p.stem
        rel = derive_relevance(fm, title)
        r = upsert(conn, str(p.relative_to(BRAIN)), "knowledge", title[:200],
                   source_url=fm.get("source"), found_date=fm.get("date"),
                   category=typ, status="later", future_relevance=rel,
                   trigger_note=fm.get("nutzen", "")[:200])
        new += r == "new"; upd += r == "updated"
    return new, upd


def ingest_wishlist(conn) -> tuple[int, int]:
    new = upd = 0
    if not WISHLIST.exists():
        return 0, 0
    text = WISHLIST.read_text(errors="replace")
    # entries like: ### <Name> [<status>]
    for m in re.finditer(r"^###\s+(.+?)\s*\[([^\]]+)\]\s*$", text, re.M):
        name, status_raw = m.group(1).strip(), m.group(2).strip().lower()
        if name.startswith("---") or name.startswith("<"):
            continue
        status = ("implemented" if "integrat" in status_raw or "adopt" in status_raw
                  else "rejected" if "reject" in status_raw
                  else "active" if "evaluat" in status_raw else "later")
        sp = f"wishlist:{name}"
        r = upsert(conn, sp, "tool", name[:200],
                   category="integration-wishlist", status=status,
                   future_relevance=derive_relevance({}, name),
                   trigger_note=f"wishlist-status: {status_raw}")
        new += r == "new"; upd += r == "updated"
    return new, upd


def cmd_ingest(args):
    conn = db()
    vn, vu = ingest_videos(conn)
    kn, ku = ingest_wissen(conn)
    wn, wu = ingest_wishlist(conn)
    conn.commit()
    total = conn.execute("SELECT COUNT(*) FROM insights").fetchone()[0]
    print(f"Ingest done. videos +{vn}/~{vu}  knowledge +{kn}/~{ku}  wishlist +{wn}/~{wu}")
    print(f"Register total: {total} insights (append-only, 0 deleted)")
    conn.close()


def cmd_stats(args):
    conn = db()
    total = conn.execute("SELECT COUNT(*) FROM insights").fetchone()[0]
    print(f"=== Insight-Register Stats ===")
    print(f"Total: {total}")
    for kind, c in conn.execute("SELECT kind, COUNT(*) FROM insights GROUP BY kind ORDER BY 2 DESC"):
        print(f"  kind {kind}: {c}")
    print("Status:")
    for st, c in conn.execute("SELECT status, COUNT(*) FROM insights GROUP BY status ORDER BY 2 DESC"):
        print(f"  {st}: {c}")
    notag = conn.execute("SELECT COUNT(*) FROM insights WHERE future_relevance IS NULL OR future_relevance=''").fetchone()[0]
    print(f"ohne future_relevance-Tag: {notag} ({100*notag//max(total,1)}%)")
    impl = conn.execute("SELECT COUNT(*) FROM insights WHERE status='implemented'").fetchone()[0]
    print(f"Umsetzungs-Quote: {impl}/{total} = {100*impl//max(total,1)}%")
    conn.close()


def cmd_list(args):
    conn = db()
    q = "SELECT source_path,kind,status,title FROM insights"
    params = []
    if args.status:
        q += " WHERE status=?"; params.append(args.status)
    q += " ORDER BY updated_at DESC LIMIT ?"; params.append(args.limit)
    for sp, kind, st, title in conn.execute(q, params):
        print(f"[{st:11}] {kind:9} {title[:60]}  ::  {sp}")
    conn.close()


def main():
    ap = argparse.ArgumentParser()
    sub = ap.add_subparsers(dest="cmd", required=True)
    sub.add_parser("ingest")
    sub.add_parser("stats")
    pl = sub.add_parser("list")
    pl.add_argument("--status", choices=sorted(VALID_STATUS))
    pl.add_argument("--limit", type=int, default=30)
    args = ap.parse_args()
    {"ingest": cmd_ingest, "stats": cmd_stats, "list": cmd_list}[args.cmd](args)


if __name__ == "__main__":
    main()