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

Breaks the 564-visible main-sidebar set into what looks like *real user conversations* vs
auto-spawned empty shells, prefetch leakage, and tiny stubs. Writes nothing.
"""
from __future__ import annotations

import json
import os
import re
import sqlite3
import sys
from collections import Counter

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")

# _pretty_timestamp(): "May. 4, 2026 9:39am"
_PRETTY_TS_RE = re.compile(
    r"^[A-Z][a-z]{2}\.\s*\d{1,2},\s*\d{4}\s+\d{1,2}:\d{2}(am|pm)$"
)


def _is_pretty_timestamp_title(s: str) -> bool:
    return bool(_PRETTY_TS_RE.match((s or "").strip()))


def _first_user_line(thread_id: str, conn: sqlite3.Connection, limit: int = 8) -> str:
    rows = conn.execute(
        "SELECT role, content FROM messages WHERE thread_id = ? ORDER BY id ASC LIMIT ?",
        (thread_id, limit),
    ).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())
        return text
    return ""


def main() -> int:
    with open(CHATS_DB_JSON, "r", encoding="utf-8") as f:
        meta_all: dict = (json.load(f) or {}).get("conversation_meta") or {}

    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA query_only = ON;")
    msg_counts = {
        str(t): int(n)
        for t, n in conn.execute(
            "SELECT thread_id, COUNT(*) FROM messages GROUP BY thread_id"
        ).fetchall()
    }
    branch_ids = {
        str(r[0]) for r in conn.execute("SELECT branch_thread_id FROM thread_branches").fetchall() if r and r[0]
    }

    bucket_empty_ts = []           # 0 messages, _pretty_timestamp title (auto-spawned shell)
    bucket_empty_other = []        # 0 messages, other title
    bucket_tiny = []               # 1-3 messages
    bucket_normal = []             # 4-9 messages
    bucket_real = []               # 10+ messages
    bucket_topic_query_first = []  # first user line is "TOPIC: …" (curiosity/prefetch leak)

    for tid, meta in meta_all.items():
        if not isinstance(meta, dict):
            continue
        if not thread_id_allowed_in_main_conversation_sidebar(tid):
            continue
        if tid in branch_ids:
            continue
        if bool(meta.get("trashed")):
            continue
        title = (meta.get("title") or "").strip()
        mc = int(msg_counts.get(tid, 0))
        first = _first_user_line(tid, conn) if mc > 0 else ""
        first_low = first.lower()
        if first_low.startswith("topic:") and "query:" in first_low[:120]:
            bucket_topic_query_first.append((tid, mc, title, first))
            continue
        if mc == 0:
            if _is_pretty_timestamp_title(title):
                bucket_empty_ts.append((tid, mc, title, first))
            else:
                bucket_empty_other.append((tid, mc, title, first))
            continue
        if mc <= 3:
            bucket_tiny.append((tid, mc, title, first))
        elif mc <= 9:
            bucket_normal.append((tid, mc, title, first))
        else:
            bucket_real.append((tid, mc, title, first))

    print("Buckets (after current sidebar filter):")
    print(f"  Real (10+ msgs)                : {len(bucket_real)}")
    print(f"  Normal (4-9 msgs)              : {len(bucket_normal)}")
    print(f"  Tiny (1-3 msgs)                : {len(bucket_tiny)}")
    print(f"  Empty + pretty_ts title (shell): {len(bucket_empty_ts)}")
    print(f"  Empty + other title            : {len(bucket_empty_other)}")
    print(f"  TOPIC: … | QUERY: first line   : {len(bucket_topic_query_first)}")
    print()

    def _print(label, sample, n=8):
        print(f"--- {label} (showing {min(n, len(sample))} of {len(sample)}) ---")
        for tid, mc, title, first in sample[:n]:
            tid_short = tid[:30] + ("…" if len(tid) > 30 else "")
            title_short = (title[:42] + "…") if len(title) > 42 else title
            first_short = (first[:80] + "…") if len(first) > 80 else first
            print(f"  msgs={mc:>3}  tid={tid_short:<31}  title={title_short!r}")
            if first_short:
                print(f"      first_user: {first_short!r}")
        print()

    _print("Empty + pretty_ts title (shells, safe to hide)", sorted(bucket_empty_ts, key=lambda r: r[2]))
    _print("Empty + other title", sorted(bucket_empty_other, key=lambda r: r[2]))
    _print("TOPIC: …  QUERY: first line (curiosity/prefetch leak)", sorted(bucket_topic_query_first, key=lambda r: -r[1]))
    _print("Tiny (1-3 msgs)", sorted(bucket_tiny, key=lambda r: -r[1]))
    _print("Normal (4-9 msgs) - sample", sorted(bucket_normal, key=lambda r: -r[1]), n=6)
    _print("Real (10+ msgs) - top by message count", sorted(bucket_real, key=lambda r: -r[1]), n=10)

    print(f"Suggested visible after stricter rules:")
    visible_strict = (
        len(bucket_real)
        + len(bucket_normal)
        + len(bucket_tiny)
        + len(bucket_empty_other)  # keep empties that someone titled manually
    )
    print(f"  Hide pretty_ts shells AND TOPIC|QUERY first-line threads → ~{visible_strict} visible")

    conn.close()
    return 0


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