#!/usr/bin/env python3 -u
"""
Aria Chat Logger - SQLite Edition

Lokale SQLite-Variante des Chat Loggers. Speichert Telegram-Nachrichten
in /root/.aria-chat.db statt Supabase. Keine Cloud-Abhängigkeit, DSGVO-konform.

Schema:
  CREATE TABLE IF NOT EXISTS aria_chat_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    direction TEXT NOT NULL,         -- 'in' or 'out'
    chat_id TEXT NOT NULL,
    user_name TEXT,
    message_text TEXT,
    source TEXT DEFAULT 'telegram',
    metadata TEXT
  );
  CREATE INDEX IF NOT EXISTS idx_created_at ON aria_chat_log(created_at);
"""

import os
import re
import time
import json
import hashlib
import sqlite3
import subprocess
import urllib.request
import urllib.error
from datetime import datetime, timezone

# --- Config ---
DB_PATH = os.environ.get("ARIA_DB_PATH", "/root/.aria-chat.db")
TMUX_SESSION = "aria"
POLL_INTERVAL = 30
BUFFER_LINES = 2000
SESSION_BACKUP = "/root/.aria-last-session.txt"
RETENTION_DAYS = 7  # Auto-cleanup nach 7 Tagen

# --- Supabase Sink (optional, parallel zu SQLite) ---
SB_URL = os.environ.get("ARIA_SUPABASE_URL", "").rstrip("/")
SB_KEY = os.environ.get("ARIA_SUPABASE_SERVICE_KEY", "")
SB_TABLE = "aria_chat_log"
SB_ENABLED = bool(SB_URL and SB_KEY)

# Duplikat-Vermeidung
seen_hashes: set[str] = set()
last_backup_size = 0


def init_db():
    """SQLite Schema anlegen falls nicht vorhanden."""
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS aria_chat_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            direction TEXT NOT NULL,
            chat_id TEXT NOT NULL,
            user_name TEXT,
            message_text TEXT,
            source TEXT DEFAULT 'telegram',
            metadata TEXT
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_created_at ON aria_chat_log(created_at)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_direction ON aria_chat_log(direction)")
    conn.commit()
    conn.close()
    os.chmod(DB_PATH, 0o600)


# --- Regex Patterns (identisch zur Supabase-Version) ---

# DEAKTIVIERT (2026-04-11): Dieser Regex matchte jeden Text im tmux-Buffer
# der aussah wie "← telegram · user: text" und schrieb ihn mit hardcoded
# chat_id=$HOME-user in die DB. Dadurch konnten Tool-Outputs (Repo-READMEs,
# Fetch-Ergebnisse, Arias eigene Zitate) fälschlich als echte eingehende
# Nachrichten geloggt werden. Nur noch die XML-Channel-Tags nutzen — die
# sind eindeutig und kommen nur von echten Telegram-Events.
RE_INBOUND = None

RE_INBOUND_XML = re.compile(
    r'<channel\s+source="(?:plugin:telegram:telegram|telegram)"\s+'
    r'chat_id="(?P<chat_id>[^"]{1,50})"\s+'
    r'message_id="(?P<message_id>[^"]{1,50})"\s+'
    r'user="(?P<user>[^"]{1,100})"'
    r'(?:\s+user_id="[^"]{1,50}")?'
    r'\s+ts="(?P<ts>[^"]{1,50})"'
    r'[^>]{0,400}>'
    r'(?P<text>.{0,5000}?)</channel>',
    re.DOTALL
)


def msg_hash(direction: str, chat_id: str, text: str, ts: str = "") -> str:
    raw = f"{direction}|{chat_id}|{text[:300]}|{ts}"
    return hashlib.sha256(raw.encode()).hexdigest()[:16]


def supabase_insert(row: dict) -> None:
    """Best-effort POST in Supabase aria_chat_log. Fehler werden geloggt, nicht geraised."""
    if not SB_ENABLED:
        return
    try:
        meta = row.get("metadata")
        if isinstance(meta, str):
            try:
                meta = json.loads(meta)
            except (ValueError, TypeError):
                meta = {"raw": meta}
        payload = {
            "direction": row["direction"],
            "chat_id": row.get("chat_id"),
            "user_name": row.get("user_name"),
            "message_text": row.get("message_text"),
            "source": row.get("source", "telegram"),
            "metadata": meta,
        }
        data = json.dumps(payload).encode("utf-8")
        req = urllib.request.Request(
            f"{SB_URL}/rest/v1/{SB_TABLE}",
            data=data,
            method="POST",
            headers={
                "apikey": SB_KEY,
                "Authorization": f"Bearer {SB_KEY}",
                "Content-Type": "application/json",
                "Prefer": "return=minimal",
            },
        )
        with urllib.request.urlopen(req, timeout=5) as resp:
            if resp.status >= 300:
                print(f"[WARN] Supabase status {resp.status}")
    except urllib.error.HTTPError as e:
        print(f"[ERROR] Supabase HTTP {e.code}: {e.read()[:200]}")
    except Exception as e:
        print(f"[ERROR] Supabase POST: {e}")


def db_insert(row: dict) -> bool:
    """INSERT in SQLite + parallel in Supabase (wenn ENABLED)."""
    try:
        conn = sqlite3.connect(DB_PATH, timeout=10)
        conn.execute("""
            INSERT INTO aria_chat_log (direction, chat_id, user_name, message_text, source, metadata)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (
            row["direction"],
            row["chat_id"],
            row.get("user_name"),
            row.get("message_text"),
            row.get("source", "telegram"),
            row.get("metadata"),
        ))
        conn.commit()
        conn.close()
        supabase_insert(row)
        return True
    except sqlite3.Error as e:
        print(f"[ERROR] SQLite INSERT: {e}")
        return False


def db_cleanup():
    """Alte Einträge löschen (>7 Tage)."""
    try:
        conn = sqlite3.connect(DB_PATH, timeout=10)
        cur = conn.execute(
            f"DELETE FROM aria_chat_log WHERE created_at < datetime('now', '-{RETENTION_DAYS} days')"
        )
        deleted = cur.rowcount
        conn.commit()
        conn.execute("VACUUM")
        conn.close()
        if deleted > 0:
            print(f"[CLEANUP] {deleted} alte Einträge gelöscht")
    except sqlite3.Error as e:
        print(f"[ERROR] SQLite Cleanup: {e}")


def capture_tmux() -> str:
    try:
        result = subprocess.run(
            ["tmux", "capture-pane", "-t", TMUX_SESSION, "-p", "-S", f"-{BUFFER_LINES}"],
            capture_output=True, text=True, timeout=5
        )
        return result.stdout
    except (subprocess.TimeoutExpired, FileNotFoundError, subprocess.SubprocessError):
        return ""


def read_session_backup() -> str:
    global last_backup_size
    try:
        size = os.path.getsize(SESSION_BACKUP)
        if size == last_backup_size:
            return ""
        last_backup_size = size
        with open(SESSION_BACKUP, "r", errors="replace") as f:
            return f.read()
    except (FileNotFoundError, OSError):
        return ""


def parse_inbound(text: str) -> int:
    logged = 0

    # Alte RE_INBOUND (← telegram · user: text) entfernt — siehe Kommentar
    # oben bei der Regex-Definition. Nur noch XML-Tags werden als echte
    # eingehende Nachrichten betrachtet.

    for m in RE_INBOUND_XML.finditer(text):
        chat_id = m.group("chat_id")
        user = m.group("user")
        ts = m.group("ts")
        msg_text = m.group("text").strip()

        if not msg_text or len(msg_text) < 1:
            continue

        h = msg_hash("in", chat_id, msg_text, ts)
        if h in seen_hashes:
            continue
        seen_hashes.add(h)

        if db_insert({
            "direction": "in",
            "chat_id": chat_id,
            "user_name": user,
            "message_text": msg_text,
            "source": "telegram",
            "metadata": json.dumps({"ts_telegram": ts}),
        }):
            logged += 1
            preview = msg_text[:80].replace('\n', ' ')
            print(f"[IN/XML] {user}: {preview}")

    return logged


def parse_outbound(text: str) -> int:
    logged = 0
    blocks = re.split(r'(?=mcp__plugin_telegram_telegram__reply|"reply")', text)

    for block in blocks:
        chat_match = re.search(r'chat_id["\s:]+(\d+)', block)
        text_match = re.search(r'"text":\s*"((?:[^"\\]|\\.)*)"', block)

        if not chat_match or not text_match:
            text_match = re.search(r'text["\s:]+([^\n]{10,})', block)
            if not chat_match or not text_match:
                continue

        chat_id = chat_match.group(1)
        msg_text = text_match.group(1)
        msg_text = msg_text.replace('\\n', '\n').replace('\\t', '\t').replace('\\"', '"')

        if len(msg_text) < 2:
            continue

        h = msg_hash("out", chat_id, msg_text)
        if h in seen_hashes:
            continue
        seen_hashes.add(h)

        if db_insert({
            "direction": "out",
            "chat_id": chat_id,
            "user_name": "aria",
            "message_text": msg_text,
            "source": "telegram",
        }):
            logged += 1
            preview = msg_text[:80].replace('\n', ' ')
            print(f"[OUT] aria: {preview}")

    return logged


def cleanup_seen(max_size: int = 5000):
    if len(seen_hashes) > max_size:
        to_remove = list(seen_hashes)[:max_size // 2]
        for h in to_remove:
            seen_hashes.discard(h)
        print(f"[CLEANUP] seen_hashes: {max_size} -> {len(seen_hashes)}")


def main():
    print(f"[START] Aria Chat Logger (SQLite)")
    print(f"  DB: {DB_PATH}")
    print(f"  Poll-Intervall: {POLL_INTERVAL}s")
    print(f"  Retention: {RETENTION_DAYS} Tage")
    print(f"  Supabase Sink: {'ON' if SB_ENABLED else 'OFF'}{' (' + SB_URL + ')' if SB_ENABLED else ''}")

    init_db()

    cycle = 0
    while True:
        try:
            total = 0
            buffer = capture_tmux()
            if buffer:
                total += parse_inbound(buffer)
                total += parse_outbound(buffer)

            backup = read_session_backup()
            if backup:
                total += parse_inbound(backup)
                total += parse_outbound(backup)

            if total > 0:
                print(f"[LOG] {total} neue Nachricht(en) gespeichert")

            cycle += 1
            if cycle % 50 == 0:
                cleanup_seen()
            # Cleanup einmal pro Stunde (~120 Zyklen bei 30s)
            if cycle % 120 == 0:
                db_cleanup()

        except Exception as e:
            print(f"[ERROR] {e}")

        time.sleep(POLL_INTERVAL)


if __name__ == "__main__":
    main()