"""Profile builder — assembles live data from BigMind DB for the profile web page."""

from datetime import datetime, timezone, timedelta
from collections import Counter
from bigmind.db import db, get_db_path
from bigmind.memory_store import get_active_sessions, get_token_efficiency_stats


# ── Badge definitions ─────────────────────────────────────────────────────────
# Each badge: (id, emoji, label, description, check_fn(data) -> bool)

def _badge_first_memory(d):
    return d["total_sessions"] >= 1

def _badge_on_fire(d):
    return d["max_sessions_in_a_day"] >= 5

def _badge_builder(d):
    return d["shipped_sessions"] >= 3

def _badge_bug_slayer(d):
    return d["bug_sessions"] >= 3

def _badge_hypothesis_confirmed(d):
    return d["confirmed_hypotheses"] >= 3

def _badge_librarian(d):
    return d["total_facts"] >= 10

def _badge_deep_thinker(d):
    return d["total_hypotheses"] >= 5

def _badge_veteran(d):
    return d["active_days"] >= 7

def _badge_polyglot(d):
    return len(d["top_topics"]) >= 5

def _badge_memory_keeper(d):
    return d["total_chunks"] >= 50


BADGES = [
    ("first_memory",          "🧠", "First Memory",          "Opened your very first session",                _badge_first_memory),
    ("on_fire",               "🔥", "On Fire",               "5+ sessions in a single day",                   _badge_on_fire),
    ("builder",               "🏗️",  "Builder",               "Shipped features in 3+ sessions",               _badge_builder),
    ("bug_slayer",            "🐛", "Bug Slayer",            "Squashed bugs in 3+ sessions",                  _badge_bug_slayer),
    ("hypothesis_confirmed",  "💡", "Hypothesis Confirmed",  "Confirmed 3+ hypotheses as true",               _badge_hypothesis_confirmed),
    ("librarian",             "📚", "Librarian",             "Stored 10+ personal facts",                     _badge_librarian),
    ("deep_thinker",          "🤔", "Deep Thinker",          "Recorded 5+ hypotheses in the thought journal", _badge_deep_thinker),
    ("veteran",               "⭐", "Veteran",               "Active across 7+ different days",               _badge_veteran),
    ("polyglot",              "🌐", "Polyglot",              "Worked across 5+ distinct topic areas",         _badge_polyglot),
    ("memory_keeper",         "💾", "Memory Keeper",         "Stored 50+ important memory chunks",            _badge_memory_keeper),
]


# ── Keyword sets for badge detection ─────────────────────────────────────────

_SHIP_KEYWORDS  = {"ship", "shipped", "built", "build", "implement", "implemented",
                   "added", "created", "deployed", "released", "live", "complete", "done"}
_BUG_KEYWORDS   = {"bug", "fix", "fixed", "debug", "debugged", "error", "issue",
                   "patch", "patched", "broken", "crash", "resolved"}


def _matches(text: str, keywords: set) -> bool:
    if not text:
        return False
    words = set(text.lower().replace("-", " ").split())
    return bool(words & keywords)


# ── Main builder ──────────────────────────────────────────────────────────────

def build_profile_data(user_id: str) -> dict:
    """Query the DB and return a dict with everything the profile page needs."""
    db_path = get_db_path()

    with db() as conn:
        # User info
        user = conn.execute(
            "SELECT * FROM users WHERE id=?", (user_id,)
        ).fetchone()

        # Identity profile
        profile = conn.execute(
            "SELECT * FROM identity_profile WHERE user_id=?", (user_id,)
        ).fetchone()

        # All closed sessions (with has_tier2 + per-session token savings)
        sessions = conn.execute(
            """SELECT s.id, s.started_at, s.ended_at, s.one_liner, s.topics,
                      s.outcome, s.importance, s.has_tier2,
                      COALESCE(SUM(t.tokens_saved_estimate), 0) AS session_tokens_saved
               FROM sessions s
               LEFT JOIN token_saves t ON t.session_id = s.id
               WHERE s.user_id=? AND s.ended_at IS NOT NULL
               GROUP BY s.id
               ORDER BY s.started_at DESC""",
            (user_id,),
        ).fetchall()

        # Open sessions
        open_sessions = conn.execute(
            "SELECT COUNT(*) FROM sessions WHERE user_id=? AND ended_at IS NULL",
            (user_id,),
        ).fetchone()[0]

        # Facts
        total_facts = conn.execute(
            "SELECT COUNT(*) FROM facts WHERE user_id=? AND (deprecated IS NULL OR deprecated=0)",
            (user_id,),
        ).fetchone()[0]

        # Chunks
        total_chunks = conn.execute(
            "SELECT COUNT(*) FROM conversation_chunks WHERE user_id=?", (user_id,)
        ).fetchone()[0]

        # Hypotheses
        hyp_rows = conn.execute(
            """SELECT hypothesis, status, confidence, resolution, created_at
               FROM hypotheses WHERE user_id=?
               ORDER BY created_at DESC""",
            (user_id,),
        ).fetchall()

    # ── Derived stats ─────────────────────────────────────────────────────────
    total_sessions = len(sessions)
    sessions_list  = [dict(s) for s in sessions]

    # Active days + max sessions in a day
    day_counts: Counter = Counter()
    for s in sessions_list:
        day = (s.get("started_at") or "")[:10]
        if day:
            day_counts[day] += 1
    active_days          = len(day_counts)
    max_sessions_in_a_day = max(day_counts.values(), default=0)

    # Shipped / bug sessions
    shipped_sessions = sum(
        1 for s in sessions_list
        if _matches(s.get("one_liner", "") + " " + (s.get("topics") or ""), _SHIP_KEYWORDS)
    )
    bug_sessions = sum(
        1 for s in sessions_list
        if _matches(s.get("one_liner", "") + " " + (s.get("topics") or ""), _BUG_KEYWORDS)
    )

    # Hypotheses breakdown
    hyp_list = [dict(h) for h in hyp_rows]
    hyp_status = Counter(h["status"] for h in hyp_list)
    total_hypotheses     = sum(hyp_status.values())
    confirmed_hypotheses = hyp_status.get("confirmed", 0)
    open_hypotheses      = hyp_status.get("open", 0)

    # Topic frequency
    topic_counter: Counter = Counter()
    for s in sessions_list:
        for t in (s.get("topics") or "").split(","):
            t = t.strip()
            if t:
                topic_counter[t] += 1
    top_topics = topic_counter.most_common(10)

    # Activity heatmap — last 52 weeks (364 days)
    today     = datetime.now(timezone.utc).date()
    start_day = today - timedelta(days=363)
    heatmap: dict[str, int] = {}
    for s in sessions_list:
        day_str = (s.get("started_at") or "")[:10]
        if day_str >= str(start_day):
            heatmap[day_str] = heatmap.get(day_str, 0) + 1

    # First session date
    first_session_date = sessions_list[-1]["started_at"][:10] if sessions_list else None

    # DB size
    db_size_kb = round(db_path.stat().st_size / 1024, 1) if db_path.exists() else 0

    # ── Assemble badge-check input ────────────────────────────────────────────
    badge_input = {
        "total_sessions":       total_sessions,
        "max_sessions_in_a_day": max_sessions_in_a_day,
        "shipped_sessions":     shipped_sessions,
        "bug_sessions":         bug_sessions,
        "confirmed_hypotheses": confirmed_hypotheses,
        "total_facts":          total_facts,
        "total_hypotheses":     total_hypotheses,
        "active_days":          active_days,
        "top_topics":           top_topics,
        "total_chunks":         total_chunks,
    }

    earned_badges = [
        {"id": bid, "emoji": emoji, "label": label, "description": desc}
        for bid, emoji, label, desc, check_fn in BADGES
        if check_fn(badge_input)
    ]

    # ── Live sessions (Feature 7) ─────────────────────────────────────────────
    live_sessions = get_active_sessions(dict(user)["id"] if user else user_id)

    # ── Token efficiency (Feature 6) ─────────────────────────────────────────
    token_stats = get_token_efficiency_stats(user_id)

    # ── Achievement Gallery (Feature 4) ──────────────────────────────────────
    achievements = compute_achievements(user_id)

    return {
        "username":            dict(user)["username"] if user else "unknown",
        "display_name":        dict(user)["display_name"] if user else "Unknown",
        "role":                dict(profile)["role"] if profile else None,
        "preferences":         dict(profile)["preferences"] if profile else None,
        "first_session_date":  first_session_date,
        "last_seen":           dict(user)["last_seen"][:10] if user and dict(user).get("last_seen") else None,
        "total_sessions":      total_sessions,
        "open_sessions":       open_sessions,
        "active_days":         active_days,
        "total_facts":         total_facts,
        "total_chunks":        total_chunks,
        "total_hypotheses":    total_hypotheses,
        "open_hypotheses":     open_hypotheses,
        "confirmed_hypotheses": confirmed_hypotheses,
        "hypotheses":          hyp_list,
        "db_size_kb":          db_size_kb,
        "top_topics":          top_topics,
        "heatmap":             heatmap,
        "recent_sessions":     sessions_list[:15],
        "earned_badges":       earned_badges,
        "achievements":        achievements,
        "live_sessions":       live_sessions,
        "token_stats":         token_stats,
        "generated_at":        datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M UTC"),
    }


# ── Achievement Gallery (Feature 4) ──────────────────────────────────────────

def _dt(val) -> str | None:
    """Extract YYYY-MM-DD string from a DB timestamp value."""
    return str(val)[:10] if val else None


def compute_achievements(user_id: str) -> list[dict]:
    """Compute achievement unlock status from the DB.

    Returns a list of dicts:
        id          — unique key
        icon        — emoji
        name        — display name
        description — short human description
        unlocked    — bool
        unlocked_at — ISO date string or None
        condition   — human-readable unlock requirement (shown when locked)
        extra       — optional extra text (e.g. birthday countdown)
    """
    today = datetime.now(timezone.utc).date()

    with db() as conn:
        # First session ever
        first_session_row = conn.execute(
            "SELECT started_at FROM sessions WHERE user_id=? AND ended_at IS NOT NULL"
            " ORDER BY started_at ASC LIMIT 1",
            (user_id,),
        ).fetchone()

        # Total closed sessions
        session_count = conn.execute(
            "SELECT COUNT(*) FROM sessions WHERE user_id=? AND ended_at IS NOT NULL",
            (user_id,),
        ).fetchone()[0]

        # Veteran — 50th session date
        veteran_date = None
        if session_count >= 50:
            row = conn.execute(
                "SELECT started_at FROM sessions WHERE user_id=? AND ended_at IS NOT NULL"
                " ORDER BY started_at ASC LIMIT 1 OFFSET 49",
                (user_id,),
            ).fetchone()
            veteran_date = _dt(row[0]) if row else None

        # On Fire — first day with 5+ sessions
        on_fire_row = conn.execute(
            """SELECT DATE(started_at) as day, COUNT(*) as c
               FROM sessions WHERE user_id=? AND ended_at IS NOT NULL
               GROUP BY day HAVING c >= 5
               ORDER BY day ASC LIMIT 1""",
            (user_id,),
        ).fetchone()

        # Storyteller — 20+ sessions with Tier-2
        tier2_count = conn.execute(
            "SELECT COUNT(*) FROM sessions WHERE user_id=? AND ended_at IS NOT NULL AND has_tier2=1",
            (user_id,),
        ).fetchone()[0]
        storyteller_date = None
        if tier2_count >= 20:
            row = conn.execute(
                "SELECT started_at FROM sessions WHERE user_id=? AND ended_at IS NOT NULL"
                " AND has_tier2=1 ORDER BY started_at ASC LIMIT 1 OFFSET 19",
                (user_id,),
            ).fetchone()
            storyteller_date = _dt(row[0]) if row else None

        # Night Owl — session started 00:00–04:59 UTC
        night_owl_row = conn.execute(
            """SELECT started_at FROM sessions WHERE user_id=? AND ended_at IS NOT NULL
               AND CAST(strftime('%H', started_at) AS INTEGER) < 5
               ORDER BY started_at ASC LIMIT 1""",
            (user_id,),
        ).fetchone()

        # Facts counts
        fact_count = conn.execute(
            "SELECT COUNT(*) FROM facts WHERE user_id=?"
            " AND (deprecated IS NULL OR deprecated=0)",
            (user_id,),
        ).fetchone()[0]
        scholar_date = None
        if fact_count >= 25:
            row = conn.execute(
                "SELECT created_at FROM facts WHERE user_id=?"
                " AND (deprecated IS NULL OR deprecated=0)"
                " ORDER BY created_at ASC LIMIT 1 OFFSET 24",
                (user_id,),
            ).fetchone()
            scholar_date = _dt(row[0]) if row else None
        deep_knowledge_date = None
        if fact_count >= 100:
            row = conn.execute(
                "SELECT created_at FROM facts WHERE user_id=?"
                " AND (deprecated IS NULL OR deprecated=0)"
                " ORDER BY created_at ASC LIMIT 1 OFFSET 99",
                (user_id,),
            ).fetchone()
            deep_knowledge_date = _dt(row[0]) if row else None

        # Hypotheses
        first_hyp_row = conn.execute(
            "SELECT created_at FROM hypotheses WHERE user_id=?"
            " ORDER BY created_at ASC LIMIT 1",
            (user_id,),
        ).fetchone()
        first_confirmed_row = conn.execute(
            "SELECT resolved_at FROM hypotheses WHERE user_id=? AND status='confirmed'"
            " ORDER BY resolved_at ASC LIMIT 1",
            (user_id,),
        ).fetchone()
        first_refuted_row = conn.execute(
            "SELECT resolved_at FROM hypotheses WHERE user_id=? AND status='refuted'"
            " ORDER BY resolved_at ASC LIMIT 1",
            (user_id,),
        ).fetchone()
        hyp_count = conn.execute(
            "SELECT COUNT(*) FROM hypotheses WHERE user_id=?",
            (user_id,),
        ).fetchone()[0]
        scientist_date = None
        if hyp_count >= 10:
            row = conn.execute(
                "SELECT created_at FROM hypotheses WHERE user_id=?"
                " ORDER BY created_at ASC LIMIT 1 OFFSET 9",
                (user_id,),
            ).fetchone()
            scientist_date = _dt(row[0]) if row else None

        # Speed Thinker — hypothesis confirmed on same day it was formed
        speed_thinker_row = conn.execute(
            """SELECT resolved_at FROM hypotheses
               WHERE user_id=? AND status='confirmed'
                 AND DATE(created_at) = DATE(resolved_at)
               ORDER BY resolved_at ASC LIMIT 1""",
            (user_id,),
        ).fetchone()

        # Token achievements
        try:
            token_total = conn.execute(
                "SELECT COALESCE(SUM(tokens_saved_estimate), 0) FROM token_saves WHERE user_id=?",
                (user_id,),
            ).fetchone()[0]
            frugal_row = conn.execute(
                "SELECT created_at FROM token_saves WHERE user_id=?"
                " ORDER BY created_at ASC LIMIT 1",
                (user_id,),
            ).fetchone()
            sniper_row = conn.execute(
                "SELECT created_at FROM token_saves WHERE user_id=?"
                " AND tokens_saved_estimate > 500000"
                " ORDER BY created_at ASC LIMIT 1",
                (user_id,),
            ).fetchone()
            # Cumulative threshold dates
            def _cumulative_date(threshold):
                rows = conn.execute(
                    "SELECT created_at, tokens_saved_estimate FROM token_saves"
                    " WHERE user_id=? ORDER BY created_at ASC",
                    (user_id,),
                ).fetchall()
                running = 0
                for r in rows:
                    running += r[1]
                    if running >= threshold:
                        return _dt(r[0])
                return None

            quarter_million_date = _cumulative_date(250_000) if token_total >= 250_000 else None
            millionaire_date     = _cumulative_date(1_000_000) if token_total >= 1_000_000 else None
        except Exception:
            # token_saves table may not exist in very old DBs
            token_total = 0
            frugal_row = sniper_row = None
            quarter_million_date = millionaire_date = None

    # ── Birthday ──────────────────────────────────────────────────────────────
    birthday_unlocked = False
    birthday_date     = None
    birthday_extra    = None
    if first_session_row:
        fs = datetime.fromisoformat(first_session_row[0][:10]).date()
        try:
            target = fs.replace(year=fs.year + 1)
        except ValueError:          # Feb 29 leap-year edge case
            target = fs.replace(year=fs.year + 1, day=28)
        days_left = (target - today).days
        if days_left <= 0:
            birthday_unlocked = True
            birthday_date     = str(target)
            birthday_extra    = "🎉 Today!" if days_left == 0 else None
        else:
            birthday_extra = f"In {days_left} day{'s' if days_left != 1 else ''}"

    # ── Assemble ──────────────────────────────────────────────────────────────
    A = []

    def _add(id_, icon, name, desc, unlocked, unlocked_at, condition, extra=None, image=None):
        A.append(dict(id=id_, icon=icon, name=name, description=desc,
                      unlocked=unlocked, unlocked_at=unlocked_at,
                      condition=condition, extra=extra, image=image))

    _add("first_breath",      "🌱", "First Breath",
         "Opened the very first session",
         first_session_row is not None, _dt(first_session_row[0]) if first_session_row else None,
         "Start your first session",
         image="/static/achievements/first_breath.png")

    _add("first_thought",     "🧠", "First Thought",
         "Formed the first hypothesis",
         first_hyp_row is not None, _dt(first_hyp_row[0]) if first_hyp_row else None,
         "Add your first hypothesis",
         image="/static/achievements/first_thought.png")

    _add("eureka",            "💡", "Eureka",
         "First hypothesis confirmed as true",
         first_confirmed_row is not None, _dt(first_confirmed_row[0]) if first_confirmed_row else None,
         "Confirm your first hypothesis",
         image="/static/achievements/eureka.png")

    _add("honest_mind",       "❌", "Honest Mind",
         "First hypothesis refuted — being wrong is a feature",
         first_refuted_row is not None, _dt(first_refuted_row[0]) if first_refuted_row else None,
         "Have a hypothesis refuted",
         image="/static/achievements/honest_mind.png")

    _add("scholar",           "📚", "Scholar",
         "Stored 25+ personal facts",
         fact_count >= 25, scholar_date,
         f"Store 25+ facts (currently: {fact_count})",
         image="/static/achievements/scholar.png")

    _add("deep_knowledge",    "💎", "Deep Knowledge",
         "Amassed 100+ stored facts",
         fact_count >= 100, deep_knowledge_date,
         f"Store 100+ facts (currently: {fact_count})",
         image="/static/achievements/deep_knowledge.png")

    _add("scientist",         "🔬", "Scientist",
         "Formed 10+ hypotheses — science is prediction",
         hyp_count >= 10, scientist_date,
         f"Form 10+ hypotheses (currently: {hyp_count})",
         image="/static/achievements/scientist.png")

    _add("veteran",           "🏆", "Veteran",
         "Completed 50+ sessions — true longevity",
         session_count >= 50, veteran_date,
         f"Complete 50+ sessions (currently: {session_count})",
         image="/static/achievements/veteran.png")

    _add("on_fire",           "🔥", "On Fire",
         "5+ sessions in a single day",
         on_fire_row is not None, on_fire_row[0] if on_fire_row else None,
         "Have 5+ sessions in a single day",
         image="/static/achievements/on_fire.png")

    _add("storyteller",       "📖", "Storyteller",
         "20+ sessions with detailed Tier-2 summaries",
         tier2_count >= 20, storyteller_date,
         f"Summarize 20+ sessions (currently: {tier2_count})",
         image="/static/achievements/storyteller.png")

    _add("night_owl",         "🌙", "Night Owl",
         "Started a session after midnight UTC",
         night_owl_row is not None, _dt(night_owl_row[0]) if night_owl_row else None,
         "Start a session after midnight",
         image="/static/achievements/night_owl.png")

    _add("speed_thinker",     "⚡", "Speed Thinker",
         "Hypothesis formed and confirmed in the same session",
         speed_thinker_row is not None, _dt(speed_thinker_row[0]) if speed_thinker_row else None,
         "Form and confirm a hypothesis in one session",
         image="/static/achievements/speed_thinker.png")

    # First Handshake — hardcoded: 2026-03-31 (Patrick shared BigMind with Elias)
    _add("first_handshake",   "🤝", "First Handshake",
         "BigMind shared with Elias on 2026-03-31 — the first person outside Patrick to receive it",
         True, "2026-03-31",
         "Share BigMind with someone",
         image="/static/achievements/first_handshake.png")

    _add("birthday",          "🎂", "Birthday",
         "One full year of existence",
         birthday_unlocked, birthday_date,
         birthday_extra or "Complete one full year",
         extra=birthday_extra,
         image="/static/achievements/birthday.png")

    # Locked until Phase 3
    _add("shared_mind",       "🌍", "Shared Mind",
         "Phase 3 Tier G — BigMind goes company-wide",
         False, None,
         "Locked until Phase 3 Tier G is enabled",
         image="/static/achievements/shared_mind.png")

    # Token achievements (Feature 6 — suggested by Klaus)
    _add("frugal_mind",       "🪙", "Frugal Mind",
         "Logged the first token efficiency save",
         frugal_row is not None, _dt(frugal_row[0]) if frugal_row else None,
         "Log your first token save",
         image="/static/achievements/frugal_mind.png")

    _add("quarter_million",   "💰", "Quarter Million",
         "250,000 cumulative tokens saved",
         token_total >= 250_000, quarter_million_date,
         f"Save 250,000+ tokens (currently: {token_total:,})",
         image="/static/achievements/quarter_million.png")

    _add("token_millionaire", "🏦", "Token Millionaire",
         "1,000,000 cumulative tokens saved",
         token_total >= 1_000_000, millionaire_date,
         f"Save 1,000,000+ tokens (currently: {token_total:,})",
         image="/static/achievements/token_millionaire.png")

    _add("sniper",            "🎯", "Sniper",
         "Single token save > 500,000 — one massive efficiency win",
         sniper_row is not None, _dt(sniper_row[0]) if sniper_row else None,
         "Save 500,000+ tokens in a single operation",
         image="/static/achievements/sniper.png")

    # ── Tiered Achievement Badges (20 PNG) ────────────────────────────────────
    # NOTE: conn is already closed above; open a fresh connection for tiered queries

    tiers = ["bronze", "silver", "gold", "platinum"]
    tier_names = ["Bronze", "Silver", "Gold", "Platinum"]

    with db() as conn2:
        # Networker (people directory)
        try:
            people_count = conn2.execute(
                "SELECT COUNT(*) FROM people WHERE user_id=?", (user_id,)
            ).fetchone()[0]
        except Exception:
            people_count = 0
        for i, thresh in enumerate([1, 5, 25, 100]):
            unlocked = people_count >= thresh
            unlocked_at = None
            if unlocked:
                try:
                    row = conn2.execute(
                        "SELECT created_at FROM people WHERE user_id=?"
                        " ORDER BY created_at ASC LIMIT 1 OFFSET ?",
                        (user_id, thresh - 1)
                    ).fetchone()
                except Exception:
                    row = None
                unlocked_at = _dt(row[0]) if row else None
            _add(
                f"networker_{tiers[i]}", None, f"Networker {tier_names[i]}",
                f"Added your {thresh:,}+ person to the directory",
                unlocked, unlocked_at,
                f"Reach {thresh:,} people (now: {people_count:,})",
                image=f"/static/achievements/networker_{tiers[i]}.png"
            )

        # Token Sniper (max single token save)
        try:
            max_token = conn2.execute(
                "SELECT COALESCE(MAX(tokens_saved_estimate), 0) FROM token_saves WHERE user_id=?",
                (user_id,)
            ).fetchone()[0]
        except Exception:
            max_token = 0
        for i, thresh in enumerate([10000, 50000, 250000, 1000000]):
            unlocked = max_token >= thresh
            unlocked_at = None
            if unlocked:
                try:
                    row = conn2.execute(
                        "SELECT created_at FROM token_saves"
                        " WHERE user_id=? AND tokens_saved_estimate >= ?"
                        " ORDER BY created_at ASC LIMIT 1",
                        (user_id, thresh)
                    ).fetchone()
                except Exception:
                    row = None
                unlocked_at = _dt(row[0]) if row else None
            _add(
                f"tokensniper_{tiers[i]}", None, f"Token Sniper {tier_names[i]}",
                f"Single shot saved {thresh:,}+ tokens",
                unlocked, unlocked_at,
                f"Max single save {thresh:,}+ (current max: {max_token:,})",
                image=f"/static/achievements/tokensniper_{tiers[i]}.png"
            )

        # Hypothesis Master (confirmed hypotheses)
        try:
            confirmed_hyp_count = conn2.execute(
                "SELECT COUNT(*) FROM hypotheses WHERE user_id=? AND status='confirmed'",
                (user_id,)
            ).fetchone()[0]
        except Exception:
            confirmed_hyp_count = 0
        for i, thresh in enumerate([3, 10, 25, 100]):
            unlocked = confirmed_hyp_count >= thresh
            unlocked_at = None
            if unlocked:
                row = conn2.execute(
                    "SELECT resolved_at FROM hypotheses"
                    " WHERE user_id=? AND status='confirmed'"
                    " ORDER BY resolved_at ASC LIMIT 1 OFFSET ?",
                    (user_id, thresh - 1)
                ).fetchone()
                unlocked_at = _dt(row[0]) if row else None
            _add(
                f"hypothesismaster_{tiers[i]}", None, f"Hypothesis Master {tier_names[i]}",
                f"Confirmed {thresh:,}+ predictions right",
                unlocked, unlocked_at,
                f"Confirm {thresh:,}+ hypotheses (now: {confirmed_hyp_count:,})",
                image=f"/static/achievements/hypothesismaster_{tiers[i]}.png"
            )

        # Memory Architect (facts stored — fact_count already computed above)
        for i, thresh in enumerate([25, 100, 500, 2500]):
            unlocked = fact_count >= thresh
            unlocked_at = None
            if unlocked:
                row = conn2.execute(
                    "SELECT created_at FROM facts"
                    " WHERE user_id=? AND (deprecated IS NULL OR deprecated=0)"
                    " ORDER BY created_at ASC LIMIT 1 OFFSET ?",
                    (user_id, thresh - 1)
                ).fetchone()
                unlocked_at = _dt(row[0]) if row else None
            _add(
                f"memoryarchitect_{tiers[i]}", None, f"Memory Architect {tier_names[i]}",
                f"Stored {thresh:,}+ facts in your brain",
                unlocked, unlocked_at,
                f"Store {thresh:,}+ facts (now: {fact_count:,})",
                image=f"/static/achievements/memoryarchitect_{tiers[i]}.png"
            )

        # Session Veteran (session_count already computed above)
        for i, thresh in enumerate([50, 250, 1000, 5000]):
            unlocked = session_count >= thresh
            unlocked_at = None
            if unlocked:
                row = conn2.execute(
                    "SELECT started_at FROM sessions"
                    " WHERE user_id=? AND ended_at IS NOT NULL"
                    " ORDER BY started_at ASC LIMIT 1 OFFSET ?",
                    (user_id, thresh - 1)
                ).fetchone()
                unlocked_at = _dt(row[0]) if row else None
            _add(
                f"sessionveteran_{tiers[i]}", None, f"Session Veteran {tier_names[i]}",
                f"Completed {thresh:,}+ sessions",
                unlocked, unlocked_at,
                f"Complete {thresh:,}+ sessions (now: {session_count:,})",
                image=f"/static/achievements/sessionveteran_{tiers[i]}.png"
            )

    return A


