"""Event Graph — canonical family state store.

Persistent store for extracted events, coordination items, and info.
Single source of truth for the family dashboard and HBM.

Tables:
- events: All extracted events (calendar, coordination, info)
- extractions: Raw extraction results for debugging
"""

import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from typing import Optional

from icarus.config import get_data_dir

---------------------------------------------------------------------------

Schema

---------------------------------------------------------------------------

SCHEMA = """
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source_id TEXT UNIQUE, -- dedup key: hash of source message
source TEXT NOT NULL DEFAULT 'telegram', -- telegram | email
type TEXT NOT NULL CHECK(type IN ('calendar_event', 'coordination', 'info')),

summary TEXT NOT NULL,
dates TEXT,                      -- JSON array ["YYYY-MM-DD", ...]
times TEXT,                      -- JSON array ["HH:MM", ...]
people TEXT,                     -- JSON array of member ids
context TEXT DEFAULT 'other',    -- transport | medical | school | social | care_coverage | other
location TEXT,
action_needed TEXT,

calendar_uid TEXT,               -- Radicale UID if pushed to calendar
confidence REAL DEFAULT 0.0,
needs_confirmation INTEGER DEFAULT 0,

created_at TEXT NOT NULL,
source_text TEXT,                -- raw message text (truncated)
raw_extraction TEXT              -- JSON

);

CREATE TABLE IF NOT EXISTS extractions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source_id TEXT NOT NULL,
tripwire_score REAL,
tripwire_patterns TEXT, -- JSON array
llm_output TEXT,
extraction_result TEXT, -- JSON
duration_ms INTEGER,
created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);
CREATE INDEX IF NOT EXISTS idx_events_dates ON events(dates);
CREATE INDEX IF NOT EXISTS idx_events_created ON events(created_at);
CREATE INDEX IF NOT EXISTS idx_events_source_id ON events(source_id);
"""

---------------------------------------------------------------------------

Database helpers

---------------------------------------------------------------------------

def _ensure_db():
"""Ensure DB path exists and schema is created. Returns connection."""
data_dir = get_data_dir()
data_dir.mkdir(parents=True, exist_ok=True)
db_path = data_dir / "icarus.db"
conn = sqlite3.connect(str(db_path))
conn.row_factory = sqlite3.Row
conn.executescript(SCHEMA)
conn.commit()
return conn

---------------------------------------------------------------------------

Event operations

---------------------------------------------------------------------------

def _make_source_id(source: str, text: str) -> str:
"""Generate a deterministic source_id for dedup."""
import hashlib
return hashlib.sha256(f"{source}:{text}".encode()).hexdigest()[:32]

def insert_event(
source: str,
source_text: str,
event_type: str,
summary: str,
dates: Optional[list[str]] = None,
times: Optional[list[str]] = None,
people: Optional[list[str]] = None,
context: str = "other",
location: Optional[str] = None,
action_needed: Optional[str] = None,
confidence: float = 0.0,
needs_confirmation: bool = False,
raw_extraction: Optional[dict] = None,
source_id: Optional[str] = None,
) -> str:
"""Insert an event into the graph. Returns source_id."""
sid = source_id or _make_source_id(source, source_text)
now = datetime.now(timezone.utc).isoformat()

with _ensure_db() as conn:
    try:
        conn.execute(
            """INSERT INTO events (
                source_id, source, type, summary,
                dates, times, people, context, location, action_needed,
                confidence, needs_confirmation,
                created_at, source_text, raw_extraction
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
            (
                sid, source, event_type, summary,
                json.dumps(dates or []),
                json.dumps(times or []),
                json.dumps(people or []),
                context, location, action_needed,
                confidence, int(needs_confirmation),
                now, source_text[:1000],
                json.dumps(raw_extraction) if raw_extraction else None,
            ),
        )
        conn.commit()
    except sqlite3.IntegrityError:
        pass  # Duplicate source_id — already processed

return sid

def insert_extraction_log(
source_id: str,
tripwire_score: float,
tripwire_patterns: list[str],
llm_output: str,
extraction_result: dict,
duration_ms: int,
):
"""Log a tripwire + extraction event for debugging/analytics."""
now = datetime.now(timezone.utc).isoformat()
with _ensure_db() as conn:
conn.execute(
"""INSERT INTO extractions (
source_id, tripwire_score, tripwire_patterns,
llm_output, extraction_result, duration_ms, created_at
) VALUES (?, ?, ?, ?, ?, ?, ?)""",
(
source_id,
tripwire_score,
json.dumps(tripwire_patterns),
llm_output[:2000],
json.dumps(extraction_result),
duration_ms,
now,
),
)
conn.commit()

---------------------------------------------------------------------------

Queries

---------------------------------------------------------------------------

def get_recent_events(days: int = 7, limit: int = 50) -> list[dict]:
"""Get recent events, ordered by creation time descending."""
with _ensure_db() as conn:
rows = conn.execute(
"""SELECT * FROM events
WHERE created_at >= datetime('now', ? || ' days', 'utc')
ORDER BY created_at DESC LIMIT ?""",
(f"-{days}", limit),
).fetchall()

return [dict(r) for r in rows]

def get_coordination_items() -> list[dict]:
"""Get outstanding coordination items (needs attention)."""
with _ensure_db() as conn:
rows = conn.execute(
"""SELECT * FROM events
WHERE type = 'coordination'
AND action_needed IS NOT NULL
AND calendar_uid IS NULL
ORDER BY created_at DESC
LIMIT 20"""
).fetchall()
return [dict(r) for r in rows]

def get_stats() -> dict:
"""Get aggregate stats for dashboard."""
with _ensure_db() as conn:
total = conn.execute("SELECT COUNT() FROM events").fetchone()[0]
by_type = conn.execute(
"SELECT type, COUNT() as cnt FROM events GROUP BY type"
).fetchall()
recent_24h = conn.execute(
"SELECT COUNT() FROM events WHERE created_at >= datetime('now', '-1 day', 'utc')"
).fetchone()[0]
extractions_24h = conn.execute(
"SELECT COUNT() FROM extractions WHERE created_at >= datetime('now', '-1 day', 'utc')"
).fetchone()[0]

return {
    "total_events": total,
    "by_type": {r["type"]: r["cnt"] for r in by_type},
    "events_24h": recent_24h,
    "extractions_24h": extractions_24h,
}