#!/usr/bin/env python3
"""Zero-loss memory vault for the WA developer-finder session.

Parses the session log on disk. Watch mode stays silent after the first check.
"""

from __future__ import annotations

import argparse
import fcntl
import hashlib
import json
import sqlite3
import time
from datetime import datetime, timezone
from pathlib import Path

TOPIC_SLUG = "wa-developer-finder"
ROOT = Path("/Users/hattr/Documents/wa-land-developers")
DB_PATH = ROOT / f"{TOPIC_SLUG}_memory.sqlite"
LINK_PATH = ROOT / "conversation_memory.sqlite"
LOCK_PATH = ROOT / f".{TOPIC_SLUG}_memory.lock"
SESSION_ID = "01a152de-2df1-79e2-b049-65698d6576ed"
LOG_PATH = (
    Path.home()
    / ".grok/sessions/%2FUsers%2Fhattr"
    / SESSION_ID
    / "chat_history.jsonl"
)
POLL_SECONDS = 3


def utc_now() -> str:
    return datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


def connect(path: Path) -> sqlite3.Connection:
    conn = sqlite3.connect(path, timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")
    conn.execute("PRAGMA busy_timeout=10000;")
    conn.execute("PRAGMA foreign_keys=ON;")
    conn.execute("PRAGMA recursive_triggers=ON;")
    return conn


SCHEMA = """
CREATE TABLE IF NOT EXISTS metadata(
    id INTEGER PRIMARY KEY CHECK(id=1),
    archive_title TEXT NOT NULL,
    session_id TEXT NOT NULL,
    export_timestamp TEXT NOT NULL,
    summary TEXT NOT NULL,
    workspace_path TEXT
) STRICT;

CREATE TABLE IF NOT EXISTS timeline(
    sequence INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp_utc TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('user','assistant','system')),
    kind TEXT NOT NULL CHECK(kind IN ('prompt','thinking','answer','tool_call','tool_result')),
    text TEXT NOT NULL,
    payload_json TEXT CHECK(payload_json IS NULL OR json_valid(payload_json)),
    dedup_key TEXT UNIQUE
) STRICT;

CREATE TABLE IF NOT EXISTS current_state(
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
) STRICT;

CREATE TABLE IF NOT EXISTS decisions(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp_utc TEXT NOT NULL,
    category TEXT NOT NULL,
    decision TEXT NOT NULL,
    rationale TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'settled',
    UNIQUE(category, decision)
) STRICT;

CREATE TABLE IF NOT EXISTS artifacts(
    artifact_id TEXT PRIMARY KEY,
    file_path TEXT NOT NULL,
    description TEXT NOT NULL,
    sha256 TEXT NOT NULL,
    content_text TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
) STRICT;

CREATE TABLE IF NOT EXISTS connection_guidance(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    topic TEXT NOT NULL UNIQUE,
    notes TEXT NOT NULL,
    sql_or_python_snippet TEXT NOT NULL
) STRICT;

CREATE VIRTUAL TABLE IF NOT EXISTS transcript_search USING fts5(
    role, kind, text,
    content='timeline',
    content_rowid='sequence'
);

CREATE TRIGGER IF NOT EXISTS timeline_ai AFTER INSERT ON timeline BEGIN
    INSERT INTO transcript_search(rowid, role, kind, text)
    VALUES (new.sequence, new.role, new.kind, new.text);
END;

CREATE TRIGGER IF NOT EXISTS timeline_ad AFTER DELETE ON timeline BEGIN
    INSERT INTO transcript_search(transcript_search, rowid, role, kind, text)
    VALUES ('delete', old.sequence, old.role, old.kind, old.text);
END;

CREATE TRIGGER IF NOT EXISTS timeline_au AFTER UPDATE ON timeline BEGIN
    INSERT INTO transcript_search(transcript_search, rowid, role, kind, text)
    VALUES ('delete', old.sequence, old.role, old.kind, old.text);
    INSERT INTO transcript_search(rowid, role, kind, text)
    VALUES (new.sequence, new.role, new.kind, new.text);
END;

CREATE VIEW IF NOT EXISTS conversation AS
SELECT sequence, timestamp_utc, role, kind, text, payload_json
FROM timeline
WHERE kind IN ('prompt', 'answer')
ORDER BY sequence;

CREATE VIEW IF NOT EXISTS thinking_stream AS
SELECT sequence, timestamp_utc, role, kind, text, payload_json
FROM timeline
WHERE kind = 'thinking'
ORDER BY sequence;

CREATE VIEW IF NOT EXISTS tool_activity AS
SELECT sequence, timestamp_utc, role, kind, text, payload_json
FROM timeline
WHERE kind IN ('tool_call', 'tool_result')
ORDER BY sequence;
"""


def ensure_link() -> None:
    if LINK_PATH.is_symlink() and LINK_PATH.resolve() == DB_PATH.resolve():
        return
    if LINK_PATH.exists() or LINK_PATH.is_symlink():
        LINK_PATH.unlink()
    LINK_PATH.symlink_to(DB_PATH.name)


def text_of(content) -> str:
    if content is None:
        return ""
    if isinstance(content, str):
        return content
    if isinstance(content, list):
        parts = []
        for item in content:
            if isinstance(item, dict) and item.get("text"):
                parts.append(item["text"])
            elif isinstance(item, str):
                parts.append(item)
        return "\n".join(parts)
    return json.dumps(content, ensure_ascii=False)


def reasoning_text(obj: dict) -> str:
    summary = obj.get("summary")
    if isinstance(summary, list):
        parts = [
            item["text"]
            for item in summary
            if isinstance(item, dict) and item.get("text")
        ]
        if parts:
            return "\n".join(parts)
    if isinstance(summary, str) and summary:
        return summary
    return ""


def iter_events(path: Path):
    with path.open(encoding="utf-8") as handle:
        for index, line in enumerate(handle):
            line = line.strip()
            if not line:
                continue
            obj = json.loads(line)
            record_type = obj.get("type")
            if record_type == "system":
                continue
            stamp = utc_now()
            if record_type == "user":
                body = text_of(obj.get("content"))
                yield (
                    f"chat:{index}:prompt",
                    stamp,
                    "user",
                    "prompt",
                    body if body else "[empty prompt]",
                    None,
                )
            elif record_type == "reasoning":
                body = reasoning_text(obj) or "[encrypted reasoning only]"
                payload = json.dumps(
                    {
                        "id": obj.get("id"),
                        "status": obj.get("status"),
                        "encrypted_content": obj.get("encrypted_content"),
                    },
                    ensure_ascii=False,
                )
                yield (
                    f"chat:{index}:thinking",
                    stamp,
                    "assistant",
                    "thinking",
                    body,
                    payload,
                )
            elif record_type == "assistant":
                content = obj.get("content") or ""
                if isinstance(content, str) and content.strip():
                    yield (
                        f"chat:{index}:answer",
                        stamp,
                        "assistant",
                        "answer",
                        content,
                        json.dumps({"model_id": obj.get("model_id")}, ensure_ascii=False),
                    )
                for call in obj.get("tool_calls") or []:
                    name = call.get("name") or ""
                    args = call.get("arguments")
                    if not isinstance(args, str):
                        args = json.dumps(args, ensure_ascii=False)
                    call_id = call.get("id") or str(index)
                    yield (
                        f"chat:{index}:tool:{call_id}",
                        stamp,
                        "assistant",
                        "tool_call",
                        f"{name}\n{args}",
                        json.dumps(
                            {"id": call_id, "name": name, "arguments": args},
                            ensure_ascii=False,
                        ),
                    )
            elif record_type == "tool_result":
                call_id = obj.get("tool_call_id") or str(index)
                body = text_of(obj.get("content")) or "[empty tool result]"
                yield (
                    f"chat:{index}:tool_result:{call_id}",
                    stamp,
                    "assistant",
                    "tool_result",
                    body,
                    json.dumps({"tool_call_id": call_id}, ensure_ascii=False),
                )
            elif record_type == "backend_tool_call":
                info = obj.get("kind") or {}
                yield (
                    f"chat:{index}:backend:{info.get('id') or index}",
                    stamp,
                    "assistant",
                    "tool_call",
                    json.dumps(info, ensure_ascii=False),
                    json.dumps(info, ensure_ascii=False),
                )


def sync_timeline(conn: sqlite3.Connection, path: Path) -> tuple[int, int]:
    existing = {
        row["dedup_key"]: (row["text"], row["payload_json"])
        for row in conn.execute("SELECT dedup_key, text, payload_json FROM timeline")
    }
    inserted = 0
    updated = 0
    for key, stamp, role, kind, text, payload in iter_events(path):
        previous = existing.get(key)
        if previous is None:
            conn.execute(
                """
                INSERT INTO timeline (timestamp_utc, role, kind, text, payload_json, dedup_key)
                VALUES (?, ?, ?, ?, ?, ?)
                """,
                (stamp, role, kind, text, payload, key),
            )
            inserted += 1
        elif previous != (text, payload):
            conn.execute(
                """
                UPDATE timeline
                SET text = ?, payload_json = ?
                WHERE dedup_key = ?
                """,
                (text, payload, key),
            )
            updated += 1
    return inserted, updated


def seed_static(conn: sqlite3.Connection) -> None:
    now = utc_now()
    conn.execute(
        """
        INSERT INTO metadata (id, archive_title, session_id, export_timestamp, summary, workspace_path)
        VALUES (1, ?, ?, ?, ?, ?)
        ON CONFLICT(id) DO NOTHING
        """,
        (
            "WA developer finder and Zenith microgrid watch",
            SESSION_ID,
            now,
            "Verbatim timeline of the WA land-developer session, excluding the system prompt.",
            str(ROOT),
        ),
    )
    state = {
        "active_goals": "Ask Landgate for a sample of sales evidence plus tenure, with buyer names and pending sales, covering greenfields land on Western Power and Horizon Power.",
        "settled_facts": "The developer database and fit ranking are in this folder. Buyer names require sales evidence and tenure together. Pending sales comes with the sales licence. Perth Metro alone misses Peel and later greenfields.",
        "current_blockers": "No Landgate licence yet. The hackathon user is not a company and cannot buy the feed. Rezoning text is public but buried in planning PDFs.",
        "resume_guidance": "Open wa-developer-finder_memory.sqlite with the five PRAGMAs. Search transcript_search. Do not publish owner names.",
    }
    for key, value in state.items():
        conn.execute(
            """
            INSERT INTO current_state (key, value, updated_at_utc)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO NOTHING
            """,
            (key, value, now),
        )
    for category, decision, rationale in (
        (
            "data-scope",
            "Request greenfields coverage on both Western Power and Horizon Power, not Perth Metro only.",
            "New housing land sits further out than Perth and Mandurah.",
        ),
        (
            "licence",
            "Do not apply as a Landgate reseller.",
            "The demo does not sell or publish owner names.",
        ),
        (
            "memory",
            "Keep the system prompt out of the vault.",
            "The archive stores prompts, thinking, answers, and tool traffic only.",
        ),
    ):
        conn.execute(
            """
            INSERT INTO decisions (timestamp_utc, category, decision, rationale)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(category, decision) DO NOTHING
            """,
            (now, category, decision, rationale),
        )
    conn.execute(
        """
        INSERT INTO connection_guidance (topic, notes, sql_or_python_snippet)
        VALUES (?, ?, ?)
        ON CONFLICT(topic) DO NOTHING
        """,
        (
            "search",
            "transcript_search stays aligned with timeline through triggers.",
            "SELECT sequence, role, kind FROM transcript_search WHERE transcript_search MATCH 'Landgate';",
        ),
    )


def sync_once(conn: sqlite3.Connection) -> tuple[int, int]:
    before = conn.total_changes
    conn.executescript(SCHEMA)
    seed_static(conn)
    ensure_link()
    inserted, updated = sync_timeline(conn, LOG_PATH)
    wrote = inserted or updated or conn.total_changes != before
    if wrote:
        conn.commit()
        conn.execute("PRAGMA wal_checkpoint(PASSIVE);")
    return inserted, updated


def verify(conn: sqlite3.Connection) -> None:
    counts = {
        name: conn.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]
        for name in ("timeline", "conversation", "thinking_stream", "tool_activity")
    }
    hits = conn.execute(
        "SELECT COUNT(*) FROM transcript_search WHERE transcript_search MATCH 'Landgate'"
    ).fetchone()[0]
    sequences = [row[0] for row in conn.execute("SELECT sequence FROM timeline ORDER BY sequence")]
    contiguous = sequences == list(range(1, len(sequences) + 1))
    print(
        json.dumps(
            {
                "db": str(DB_PATH),
                "link": str(LINK_PATH),
                "counts": counts,
                "fts_landgate": hits,
                "contiguous": contiguous,
            }
        )
    )


def watch(conn: sqlite3.Connection) -> None:
    while True:
        time.sleep(POLL_SECONDS)
        try:
            sync_once(conn)
        except Exception:
            continue


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--watch", action="store_true")
    args = parser.parse_args()
    ROOT.mkdir(parents=True, exist_ok=True)
    lock_handle = LOCK_PATH.open("a+")
    if args.watch:
        try:
            fcntl.flock(lock_handle.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
        except BlockingIOError:
            return
    conn = connect(DB_PATH)
    try:
        sync_once(conn)
        if args.watch:
            verify(conn)
            watch(conn)
        else:
            verify(conn)
    finally:
        conn.close()


if __name__ == "__main__":
    main()