#!/usr/bin/env python3
"""Read-only sidebar diagnostic.

Walks ``~/.mugi/chats_db.json`` (sidebar meta) and ``~/.mugi/chats.db`` (encrypted message
bodies) and prints what would *currently* show in the main Conversations sidebar after the
new ``utils.conversation_sidebar`` filter.

Writes nothing. Decrypts via the same Keychain key the app uses. Run with the same Python
environment Mugi uses, e.g.::

    ~/.pyenv/versions/ai_env_312/bin/python scripts/diagnose_sidebar_threads.py
"""
from __future__ import annotations

import json
import os
import sqlite3
import sys
from collections import Counter
from typing import Any

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from chat_storage import (  # noqa: E402
    DB_PATH,
    _content_to_str,
    _decrypt,
    _is_displayable_message,
)
from utils.conversation_sidebar import thread_id_allowed_in_main_conversation_sidebar  # noqa: E402

CHATS_DB_JSON = os.path.expanduser("~/.mugi/chats_db.json")


def _prefix_bucket(tid: str) -> str:
    t = tid.lower()
    for p in (
        "mesh_peer_",
        "mesh_",
        "mesh-",
        "subagent_",
        "btw_",
        "group_",
        "group-",
        "peer_",
        "peer-",
        "heartbeat_",
        "background_heartbeat",
        "drive_discovery_",
        "drive_action",
        "curiosity_",
        "consolidation_",
        "skill_eval_",
        "cron_",
        "cron-",
        "__voice_",
    ):
        if t.startswith(p):
            return p
    return "uuid_or_other"


def _first_displayable_user_line(thread_id: str, conn: sqlite3.Connection, max_chars: int = 90) -> str:
    rows = conn.execute(
        "SELECT role, content FROM messages WHERE thread_id = ? ORDER BY id ASC LIMIT 12",
        (thread_id,),
    ).fetchall()
    for role, blob in rows:
        try:
            raw = _decrypt(blob) if blob else ""
        except Exception:
            continue
        if isinstance(raw, str) and raw.strip().startswith("["):
            try:
                content = json.loads(raw)
            except Exception:
                content = raw
        else:
            content = raw
        msg = {"role": role, "content": content}
        if not _is_displayable_message(msg):
            continue
        if (msg.get("role") or "").strip() != "user":
            continue
        text = " ".join(_content_to_str(msg["content"]).split())
        if not text:
            continue
        if len(text) > max_chars:
            text = text[: max_chars - 1] + "…"
        return text
    return ""


def main() -> int:
    if not os.path.isfile(CHATS_DB_JSON):
        print(f"chats_db.json not found at {CHATS_DB_JSON}")
        return 1
    if not os.path.isfile(DB_PATH):
        print(f"chats.db not found at {DB_PATH}")
        return 1

    with open(CHATS_DB_JSON, "r", encoding="utf-8") as f:
        data: dict[str, Any] = json.load(f)
    meta_all: dict[str, dict] = data.get("conversation_meta") or {}
    if not isinstance(meta_all, dict):
        meta_all = {}

    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA query_only = ON;")
    try:
        msg_counts_rows = conn.execute(
            """
            SELECT thread_id, COUNT(*) AS n
            FROM messages
            WHERE LENGTH(COALESCE(thread_id, '')) > 0
            GROUP BY thread_id
            """
        ).fetchall()
        branch_rows = conn.execute("SELECT branch_thread_id FROM thread_branches").fetchall()
        branch_ids = {str(r[0]).strip() for r in branch_rows if r and r[0]}

        msg_counts = {str(tid): int(n) for tid, n in msg_counts_rows}
        union_ids = set(meta_all.keys()) | set(msg_counts.keys())

        prefix_counter: Counter[str] = Counter()
        prefix_visible: Counter[str] = Counter()
        prefix_hidden: Counter[str] = Counter()
        rows: list[tuple[str, str, str, int, str, str, bool, bool, str]] = []

        for tid in sorted(union_ids):
            bucket = _prefix_bucket(tid)
            prefix_counter[bucket] += 1
            in_meta = tid in meta_all
            mc = int(msg_counts.get(tid, 0))
            meta = meta_all.get(tid) or {}
            title = (meta.get("title") or "") if isinstance(meta, dict) else ""
            archived = bool(meta.get("archived")) if isinstance(meta, dict) else False
            trashed = bool(meta.get("trashed")) if isinstance(meta, dict) else False
            is_branch = tid in branch_ids
            allowed = thread_id_allowed_in_main_conversation_sidebar(tid)
            visible_in_main = (
                in_meta
                and allowed
                and not is_branch
                and not trashed
                and not archived
            )
            if visible_in_main:
                prefix_visible[bucket] += 1
            else:
                prefix_hidden[bucket] += 1
            first_user = _first_displayable_user_line(tid, conn) if mc > 0 else ""
            rows.append(
                (
                    bucket,
                    tid,
                    title,
                    mc,
                    "branch" if is_branch else "",
                    "trashed" if trashed else ("archived" if archived else ""),
                    allowed,
                    visible_in_main,
                    first_user,
                )
            )

        print("=" * 88)
        print("Mugi sidebar diagnostic — read-only")
        print("=" * 88)
        print(f"chats_db.json       : {CHATS_DB_JSON}")
        print(f"chats.db            : {DB_PATH}")
        print(f"meta entries        : {len(meta_all)}")
        print(f"sqlite distinct tids: {len(msg_counts)}")
        print(f"branches            : {len(branch_ids)}")
        print()
        print("Per ID-prefix bucket:")
        print(f"  {'bucket':<22} {'total':>6} {'visible_main':>14} {'hidden':>8}")
        for bucket, total in prefix_counter.most_common():
            print(
                f"  {bucket:<22} {total:>6} {prefix_visible[bucket]:>14} "
                f"{prefix_hidden[bucket]:>8}"
            )
        print()
        all_visible = [r for r in rows if r[7]]
        all_hidden = [r for r in rows if not r[7] and r[3] > 0]
        print(f"Threads VISIBLE in main sidebar after new filter: {len(all_visible)}")
        print(f"Threads with messages but HIDDEN by new filter   : {len(all_hidden)}")
        print()

        def _print_sample(label: str, sample: list, limit: int = 12) -> None:
            print(f"--- {label} (sample of {min(limit, len(sample))} of {len(sample)}) ---")
            sample_sorted = sorted(sample, key=lambda r: (-r[3], r[1]))
            for bucket, tid, title, mc, branch, archtrash, allowed, vis, first in sample_sorted[:limit]:
                tid_short = tid[:34] + ("…" if len(tid) > 34 else "")
                title_short = (title[:42] + "…") if len(title) > 42 else title
                first_short = (first[:60] + "…") if len(first) > 60 else first
                print(f"  [{bucket:<14}] tid={tid_short:<35} msgs={mc:>4} title={title_short!r}")
                if first_short:
                    print(f"      first user line: {first_short!r}")

        _print_sample("VISIBLE in main sidebar", all_visible, 15)
        print()
        _print_sample("HIDDEN by new filter (had messages)", all_hidden, 15)
        print()
        print("If counts above look wrong (mesh threads still visible / real chats hidden), share")
        print("a few of the offending thread IDs back so the filter prefix list can be adjusted.")
    finally:
        conn.close()
    return 0


if __name__ == "__main__":
    sys.exit(main())
