#!/usr/bin/env python3
"""
Wissensdatenbank CLI-Tool fuer den Claudia-Workspace.

SQLite-basierte Datenbank mit Volltextsuche (FTS5) fuer Knowledge Entries,
Research Results und Inbox Items.

Nutzung:
    python scripts/db.py init          -- Datenbank initialisieren
    python scripts/db.py add           -- Neuen Eintrag hinzufuegen
    python scripts/db.py list          -- Eintraege auflisten
    python scripts/db.py search        -- Volltextsuche
    python scripts/db.py get           -- Einzelnen Eintrag anzeigen
    python scripts/db.py update        -- Eintrag aktualisieren
    python scripts/db.py delete        -- Eintrag loeschen
    python scripts/db.py inbox-add     -- Inbox-Item hinzufuegen
    python scripts/db.py inbox-list    -- Inbox-Items auflisten
    python scripts/db.py research-add  -- Research-Result hinzufuegen
    python scripts/db.py research-list -- Research-Results auflisten
"""

import argparse
import json
import os
import sqlite3
import sys
from datetime import datetime
from pathlib import Path

# Datenbank-Pfad relativ zum Workspace-Root
DB_DIR = Path(__file__).resolve().parent.parent
DB_PATH = DB_DIR / "data" / "knowledge.db"


def get_connection() -> sqlite3.Connection:
    """Stellt eine Verbindung zur SQLite-Datenbank her."""
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db():
    """Erstellt alle Tabellen und den FTS5-Index."""
    conn = get_connection()
    cursor = conn.cursor()

    # Haupttabelle: Knowledge Entries
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS knowledge_entries (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            category TEXT DEFAULT '',
            source TEXT DEFAULT '',
            tags TEXT DEFAULT '[]',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)

    # FTS5 Virtual Table fuer Volltextsuche ueber knowledge_entries
    cursor.execute("""
        CREATE VIRTUAL TABLE IF NOT EXISTS knowledge_entries_fts USING fts5(
            title,
            content,
            category,
            tags,
            content=knowledge_entries,
            content_rowid=id
        )
    """)

    # Trigger: FTS-Index bei INSERT aktualisieren
    cursor.execute("""
        CREATE TRIGGER IF NOT EXISTS knowledge_entries_ai AFTER INSERT ON knowledge_entries
        BEGIN
            INSERT INTO knowledge_entries_fts(rowid, title, content, category, tags)
            VALUES (new.id, new.title, new.content, new.category, new.tags);
        END
    """)

    # Trigger: FTS-Index bei DELETE aktualisieren
    cursor.execute("""
        CREATE TRIGGER IF NOT EXISTS knowledge_entries_ad AFTER DELETE ON knowledge_entries
        BEGIN
            INSERT INTO knowledge_entries_fts(knowledge_entries_fts, rowid, title, content, category, tags)
            VALUES ('delete', old.id, old.title, old.content, old.category, old.tags);
        END
    """)

    # Trigger: FTS-Index bei UPDATE aktualisieren
    cursor.execute("""
        CREATE TRIGGER IF NOT EXISTS knowledge_entries_au AFTER UPDATE ON knowledge_entries
        BEGIN
            INSERT INTO knowledge_entries_fts(knowledge_entries_fts, rowid, title, content, category, tags)
            VALUES ('delete', old.id, old.title, old.content, old.category, old.tags);
            INSERT INTO knowledge_entries_fts(rowid, title, content, category, tags)
            VALUES (new.id, new.title, new.content, new.category, new.tags);
        END
    """)

    # Research Results
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS research_results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            topic TEXT NOT NULL,
            summary TEXT NOT NULL,
            sources TEXT DEFAULT '[]',
            agent TEXT DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)

    # Inbox Items
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS inbox_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type TEXT NOT NULL DEFAULT 'note',
            title TEXT NOT NULL,
            content TEXT DEFAULT '',
            status TEXT NOT NULL DEFAULT 'open',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)

    conn.commit()
    conn.close()
    print(f"Datenbank initialisiert: {DB_PATH}")


# ---------------------------------------------------------------------------
# CRUD: Knowledge Entries
# ---------------------------------------------------------------------------

def add_entry(title: str, content: str, category: str = "", source: str = "", tags: list = None):
    """Fuegt einen neuen Knowledge Entry hinzu."""
    tags_json = json.dumps(tags or [], ensure_ascii=False)
    conn = get_connection()
    cursor = conn.cursor()
    now = datetime.utcnow().isoformat(sep=" ", timespec="seconds")
    cursor.execute(
        """INSERT INTO knowledge_entries (title, content, category, source, tags, created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?)""",
        (title, content, category, source, tags_json, now, now),
    )
    conn.commit()
    entry_id = cursor.lastrowid
    conn.close()
    print(f"Knowledge Entry #{entry_id} erstellt: {title}")
    return entry_id


def list_entries(category: str = None, limit: int = 20):
    """Listet Knowledge Entries auf, optional gefiltert nach Kategorie."""
    conn = get_connection()
    if category:
        rows = conn.execute(
            "SELECT id, title, category, tags, created_at FROM knowledge_entries WHERE category = ? ORDER BY created_at DESC LIMIT ?",
            (category, limit),
        ).fetchall()
    else:
        rows = conn.execute(
            "SELECT id, title, category, tags, created_at FROM knowledge_entries ORDER BY created_at DESC LIMIT ?",
            (limit,),
        ).fetchall()
    conn.close()

    if not rows:
        print("Keine Eintraege gefunden.")
        return

    print(f"{'ID':>4}  {'Titel':<40} {'Kategorie':<15} {'Erstellt':<20}")
    print("-" * 85)
    for r in rows:
        print(f"{r['id']:>4}  {r['title'][:40]:<40} {r['category'][:15]:<15} {r['created_at']:<20}")


def get_entry(entry_id: int):
    """Zeigt einen einzelnen Knowledge Entry an."""
    conn = get_connection()
    row = conn.execute("SELECT * FROM knowledge_entries WHERE id = ?", (entry_id,)).fetchone()
    conn.close()

    if not row:
        print(f"Eintrag #{entry_id} nicht gefunden.")
        return

    print(f"ID:        {row['id']}")
    print(f"Titel:     {row['title']}")
    print(f"Kategorie: {row['category']}")
    print(f"Quelle:    {row['source']}")
    print(f"Tags:      {row['tags']}")
    print(f"Erstellt:  {row['created_at']}")
    print(f"Updated:   {row['updated_at']}")
    print(f"\n{row['content']}")


def update_entry(entry_id: int, title: str = None, content: str = None, category: str = None, source: str = None, tags: list = None):
    """Aktualisiert einen bestehenden Knowledge Entry."""
    conn = get_connection()
    row = conn.execute("SELECT * FROM knowledge_entries WHERE id = ?", (entry_id,)).fetchone()
    if not row:
        print(f"Eintrag #{entry_id} nicht gefunden.")
        conn.close()
        return

    new_title = title if title is not None else row["title"]
    new_content = content if content is not None else row["content"]
    new_category = category if category is not None else row["category"]
    new_source = source if source is not None else row["source"]
    new_tags = json.dumps(tags, ensure_ascii=False) if tags is not None else row["tags"]
    now = datetime.utcnow().isoformat(sep=" ", timespec="seconds")

    conn.execute(
        """UPDATE knowledge_entries
           SET title=?, content=?, category=?, source=?, tags=?, updated_at=?
           WHERE id=?""",
        (new_title, new_content, new_category, new_source, new_tags, now, entry_id),
    )
    conn.commit()
    conn.close()
    print(f"Knowledge Entry #{entry_id} aktualisiert.")


def delete_entry(entry_id: int):
    """Loescht einen Knowledge Entry."""
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM knowledge_entries WHERE id = ?", (entry_id,))
    if cursor.rowcount == 0:
        print(f"Eintrag #{entry_id} nicht gefunden.")
    else:
        print(f"Knowledge Entry #{entry_id} geloescht.")
    conn.commit()
    conn.close()


def search_entries(query: str, limit: int = 20):
    """Volltextsuche ueber Knowledge Entries via FTS5."""
    conn = get_connection()
    rows = conn.execute(
        """SELECT ke.id, ke.title, ke.category, ke.created_at,
                  snippet(knowledge_entries_fts, 1, '>>>', '<<<', '...', 32) AS snippet
           FROM knowledge_entries_fts fts
           JOIN knowledge_entries ke ON ke.id = fts.rowid
           WHERE knowledge_entries_fts MATCH ?
           ORDER BY rank
           LIMIT ?""",
        (query, limit),
    ).fetchall()
    conn.close()

    if not rows:
        print(f"Keine Treffer fuer: {query}")
        return

    print(f"Suchergebnisse fuer '{query}':\n")
    for r in rows:
        print(f"  #{r['id']} {r['title']} [{r['category']}]")
        print(f"    ...{r['snippet']}...")
        print()


# ---------------------------------------------------------------------------
# Inbox Items
# ---------------------------------------------------------------------------

def inbox_add(title: str, content: str = "", item_type: str = "note"):
    """Fuegt ein Inbox-Item hinzu."""
    conn = get_connection()
    cursor = conn.cursor()
    now = datetime.utcnow().isoformat(sep=" ", timespec="seconds")
    cursor.execute(
        "INSERT INTO inbox_items (type, title, content, status, created_at) VALUES (?, ?, ?, 'open', ?)",
        (item_type, title, content, now),
    )
    conn.commit()
    item_id = cursor.lastrowid
    conn.close()
    print(f"Inbox-Item #{item_id} erstellt: {title}")
    return item_id


def inbox_list(status: str = None, limit: int = 20):
    """Listet Inbox-Items auf."""
    conn = get_connection()
    if status:
        rows = conn.execute(
            "SELECT id, type, title, status, created_at FROM inbox_items WHERE status = ? ORDER BY created_at DESC LIMIT ?",
            (status, limit),
        ).fetchall()
    else:
        rows = conn.execute(
            "SELECT id, type, title, status, created_at FROM inbox_items ORDER BY created_at DESC LIMIT ?",
            (limit,),
        ).fetchall()
    conn.close()

    if not rows:
        print("Keine Inbox-Items gefunden.")
        return

    print(f"{'ID':>4}  {'Typ':<10} {'Titel':<35} {'Status':<10} {'Erstellt':<20}")
    print("-" * 85)
    for r in rows:
        print(f"{r['id']:>4}  {r['type'][:10]:<10} {r['title'][:35]:<35} {r['status'][:10]:<10} {r['created_at']:<20}")


# ---------------------------------------------------------------------------
# Research Results
# ---------------------------------------------------------------------------

def research_add(topic: str, summary: str, sources: list = None, agent: str = ""):
    """Fuegt ein Research-Result hinzu."""
    sources_json = json.dumps(sources or [], ensure_ascii=False)
    conn = get_connection()
    cursor = conn.cursor()
    now = datetime.utcnow().isoformat(sep=" ", timespec="seconds")
    cursor.execute(
        "INSERT INTO research_results (topic, summary, sources, agent, created_at) VALUES (?, ?, ?, ?, ?)",
        (topic, summary, sources_json, agent, now),
    )
    conn.commit()
    result_id = cursor.lastrowid
    conn.close()
    print(f"Research-Result #{result_id} erstellt: {topic}")
    return result_id


def research_list(limit: int = 20):
    """Listet Research-Results auf."""
    conn = get_connection()
    rows = conn.execute(
        "SELECT id, topic, agent, created_at FROM research_results ORDER BY created_at DESC LIMIT ?",
        (limit,),
    ).fetchall()
    conn.close()

    if not rows:
        print("Keine Research-Results gefunden.")
        return

    print(f"{'ID':>4}  {'Thema':<40} {'Agent':<15} {'Erstellt':<20}")
    print("-" * 85)
    for r in rows:
        print(f"{r['id']:>4}  {r['topic'][:40]:<40} {r['agent'][:15]:<15} {r['created_at']:<20}")


# ---------------------------------------------------------------------------
# CLI Interface
# ---------------------------------------------------------------------------

def build_parser() -> argparse.ArgumentParser:
    parser = argparse.ArgumentParser(
        description="Wissensdatenbank CLI fuer den Claudia-Workspace",
        formatter_class=argparse.RawDescriptionHelpFormatter,
    )
    subparsers = parser.add_subparsers(dest="command", help="Verfuegbare Befehle")

    # init
    subparsers.add_parser("init", help="Datenbank initialisieren")

    # add
    p_add = subparsers.add_parser("add", help="Knowledge Entry hinzufuegen")
    p_add.add_argument("--title", "-t", required=True, help="Titel des Eintrags")
    p_add.add_argument("--content", "-c", required=True, help="Inhalt")
    p_add.add_argument("--category", default="", help="Kategorie")
    p_add.add_argument("--source", default="", help="Quelle")
    p_add.add_argument("--tags", nargs="*", default=[], help="Tags (Leerzeichen-getrennt)")

    # list
    p_list = subparsers.add_parser("list", help="Knowledge Entries auflisten")
    p_list.add_argument("--category", default=None, help="Nach Kategorie filtern")
    p_list.add_argument("--limit", type=int, default=20, help="Max. Anzahl (default: 20)")

    # search
    p_search = subparsers.add_parser("search", help="Volltextsuche")
    p_search.add_argument("query", help="Suchbegriff")
    p_search.add_argument("--limit", type=int, default=20, help="Max. Anzahl (default: 20)")

    # get
    p_get = subparsers.add_parser("get", help="Einzelnen Eintrag anzeigen")
    p_get.add_argument("id", type=int, help="ID des Eintrags")

    # update
    p_update = subparsers.add_parser("update", help="Eintrag aktualisieren")
    p_update.add_argument("id", type=int, help="ID des Eintrags")
    p_update.add_argument("--title", "-t", default=None, help="Neuer Titel")
    p_update.add_argument("--content", "-c", default=None, help="Neuer Inhalt")
    p_update.add_argument("--category", default=None, help="Neue Kategorie")
    p_update.add_argument("--source", default=None, help="Neue Quelle")
    p_update.add_argument("--tags", nargs="*", default=None, help="Neue Tags")

    # delete
    p_delete = subparsers.add_parser("delete", help="Eintrag loeschen")
    p_delete.add_argument("id", type=int, help="ID des Eintrags")

    # inbox-add
    p_inbox_add = subparsers.add_parser("inbox-add", help="Inbox-Item hinzufuegen")
    p_inbox_add.add_argument("--title", "-t", required=True, help="Titel")
    p_inbox_add.add_argument("--content", "-c", default="", help="Inhalt")
    p_inbox_add.add_argument("--type", dest="item_type", default="note", help="Typ (note, task, idea, ...)")

    # inbox-list
    p_inbox_list = subparsers.add_parser("inbox-list", help="Inbox-Items auflisten")
    p_inbox_list.add_argument("--status", default=None, help="Nach Status filtern (open, done, ...)")
    p_inbox_list.add_argument("--limit", type=int, default=20, help="Max. Anzahl")

    # research-add
    p_research_add = subparsers.add_parser("research-add", help="Research-Result hinzufuegen")
    p_research_add.add_argument("--topic", "-t", required=True, help="Thema")
    p_research_add.add_argument("--summary", "-s", required=True, help="Zusammenfassung")
    p_research_add.add_argument("--sources", nargs="*", default=[], help="Quellen")
    p_research_add.add_argument("--agent", default="", help="Agent-Name")

    # research-list
    p_research_list = subparsers.add_parser("research-list", help="Research-Results auflisten")
    p_research_list.add_argument("--limit", type=int, default=20, help="Max. Anzahl")

    return parser


def main():
    parser = build_parser()
    args = parser.parse_args()

    if not args.command:
        parser.print_help()
        sys.exit(1)

    if args.command == "init":
        init_db()
    elif args.command == "add":
        add_entry(args.title, args.content, args.category, args.source, args.tags)
    elif args.command == "list":
        list_entries(args.category, args.limit)
    elif args.command == "search":
        search_entries(args.query, args.limit)
    elif args.command == "get":
        get_entry(args.id)
    elif args.command == "update":
        update_entry(args.id, args.title, args.content, args.category, args.source, args.tags)
    elif args.command == "delete":
        delete_entry(args.id)
    elif args.command == "inbox-add":
        inbox_add(args.title, args.content, args.item_type)
    elif args.command == "inbox-list":
        inbox_list(args.status, args.limit)
    elif args.command == "research-add":
        research_add(args.topic, args.summary, args.sources, args.agent)
    elif args.command == "research-list":
        research_list(args.limit)


if __name__ == "__main__":
    main()