""" admin_stats.py — Admin「裝置」分頁 + 對話篩選(P2-A, docs/11 §8.7; FR-TELE-04/10/12). No device_profiles / inference_log tables: everything is computed from telemetry_events (latest device_caps per device, chat_turn, model_download). Volumes are small (one row per app start / chat turn), so plain Python over the rows is fine. """ from __future__ import annotations import json import math from collections import Counter from datetime import datetime, timedelta, timezone from sqlalchemy import func, select from app import db from app.db import cases, devices, messages, telemetry_events TTFT_BUCKETS = (("<5s", 5_000), ("5–10s", 10_000), ("10–20s", 20_000), ("20–40s", 40_000), ("≥40s", None)) def _str(v) -> str | None: return v[:120] if isinstance(v, str) and v else None LOCAL_KINDS = ("full", "lite", "manual", "none") def _props(raw) -> dict: """Telemetry comes from any registered device: keep only the fields we read, with the types we expect (a list reason, an Infinity TTFT or a string tier must not 500 the page — review 2026-10-20 #3).""" try: p = json.loads(raw or "{}") except ValueError: return {} if not isinstance(p, dict): return {} static = p.get("static") if isinstance(p.get("static"), dict) else {} ttft, mem, tier = p.get("ttft_ms"), static.get("total_mem_gb"), p.get("tier") num = lambda v: isinstance(v, (int, float)) and not isinstance(v, bool) and math.isfinite(v) # noqa: E731 return { "route": p.get("route") if p.get("route") in ("LOCAL", "API") else None, "ttft_ms": int(ttft) if num(ttft) and 0 <= ttft < 3_600_000 else None, "tier": tier if isinstance(tier, int) and not isinstance(tier, bool) and 0 <= tier <= 3 else None, "downgrade_reason": _str(p.get("downgrade_reason")), "app_version": _str(p.get("app_version")), "action": _str(p.get("action")), "role": _str(p.get("role")), "model": _str(static.get("model")), "total_mem_gb": float(mem) if num(mem) else None, # P5 (FR-LITE-12): which on-device mode — device_caps carries the phone's, chat_turn the turn's "local_kind": p.get("local_kind") if p.get("local_kind") in LOCAL_KINDS else None, "lite_guard_tripped": isinstance(p.get("lite_guard"), dict) and p["lite_guard"].get("tripped") is True, "last_resort": p.get("last_resort") is True, } def _events(conn, names: tuple[str, ...], since: str | None = None) -> list[dict]: stmt = (select(telemetry_events.c.device_id, telemetry_events.c.event, telemetry_events.c.props, telemetry_events.c.received_at) .where(telemetry_events.c.event.in_(names)).order_by(telemetry_events.c.id)) if since: stmt = stmt.where(telemetry_events.c.received_at >= since) return [{"device_id": r.device_id, "event": r.event, "props": _props(r.props), "at": r.received_at} for r in conn.execute(stmt)] def _since(days: int, now: datetime | None) -> str: now = (now or datetime.now(timezone.utc)).replace(tzinfo=None) return (now - timedelta(days=days)).isoformat() def _pct(sorted_vals: list[int], q: float) -> int | None: if not sorted_vals: return None return sorted_vals[min(len(sorted_vals) - 1, int(q * len(sorted_vals)))] def _latest_caps(conn) -> dict[str, dict]: """device_id → props of its newest device_caps (all time: a device's spec does not expire).""" latest: dict[str, dict] = {} for e in _events(conn, ("device_caps",)): if e["device_id"]: latest[e["device_id"]] = e["props"] | {"_at": e["at"]} return latest def _tier_key(caps: dict | None) -> str: t = (caps or {}).get("tier") return str(t) if isinstance(t, int) else "?" def device_summary(days: int = 30, now: datetime | None = None) -> dict: """D-25 five aggregates over the last `days` (tier distribution covers devices seen in the window).""" since = _since(days, now) with db.read_connection() as conn: caps = _latest_caps(conn) turns = _events(conn, ("chat_turn",), since) downloads = _events(conn, ("model_download",), since) recent_caps = _events(conn, ("device_caps",), since) active = {e["device_id"] for e in recent_caps + turns if e["device_id"]} tiers = Counter(_tier_key(caps.get(d)) for d in active) routes = Counter(e["props"].get("route") for e in turns if e["props"].get("route") in ("LOCAL", "API")) ttft = sorted(e["props"]["ttft_ms"] for e in turns if e["props"]["route"] == "LOCAL" and e["props"]["ttft_ms"] is not None) buckets = {name: 0 for name, _ in TTFT_BUCKETS} for v in ttft: buckets[next(name for name, hi in TTFT_BUCKETS if hi is None or v < hi)] += 1 by_tier: dict[str, list[int]] = {} for e in turns: p = e["props"] if p["route"] == "LOCAL" and p["ttft_ms"] is not None: by_tier.setdefault(_tier_key(caps.get(e["device_id"])), []).append(p["ttft_ms"]) reasons = Counter(e["props"]["downgrade_reason"] for e in turns + recent_caps if e["props"].get("downgrade_reason")) gen = [e for e in downloads if (e["props"]["role"] or "generator") == "generator"] started = {e["device_id"] for e in gen if e["props"].get("action") == "start"} done = {e["device_id"] for e in gen if e["props"].get("action") == "done"} & started n_routes = routes["LOCAL"] + routes["API"] kinds = Counter((caps.get(d) or {}).get("local_kind") or "?" for d in active) local_turns = [e["props"] for e in turns if e["props"].get("route") == "LOCAL"] return { "days": days, "devices": len(active), "tiers": dict(sorted(tiers.items())), "routes": {"LOCAL": routes["LOCAL"], "API": routes["API"]}, "local_ratio": round(routes["LOCAL"] / n_routes, 3) if n_routes else None, # P5 (FR-LITE-12): devices by on-device mode; LOCAL turns by mode; LITE number-guard trips; handbook last layer "local_kinds": dict(sorted(kinds.items())), "local_turns_by_kind": dict(sorted(Counter(p.get("local_kind") or "full" for p in local_turns).items())), "lite_guard_trips": sum(1 for p in local_turns if p.get("lite_guard_tripped")), "last_resort": sum(1 for p in local_turns if p.get("last_resort")), "ttft": {"n": len(ttft), "p50": _pct(ttft, 0.5), "p90": _pct(ttft, 0.9), "buckets": buckets}, "ttft_by_tier": {t: {"n": len(v), "p50": _pct(sorted(v), 0.5)} for t, v in sorted(by_tier.items())}, "downgrade_top": [{"reason": r, "n": n} for r, n in reasons.most_common(5)], "download": {"started": len(started), "done": len(done), "rate": round(len(done) / len(started), 3) if started else None}, } def device_list(q: str = "", tier: str = "") -> list[dict]: """One row per device that ever sent telemetry or registered (FR-TELE-10).""" with db.read_connection() as conn: caps = _latest_caps(conn) turns = _events(conn, ("chat_turn",)) last_tel = dict(conn.execute(select(telemetry_events.c.device_id, func.max(telemetry_events.c.received_at)) .group_by(telemetry_events.c.device_id)).all()) registered = dict(conn.execute(select(devices.c.device_id, devices.c.last_seen)).all()) n_cases = dict(conn.execute(select(cases.c.device_id, func.count()).where(cases.c.hidden == 0) .group_by(cases.c.device_id)).all()) per: dict[str, dict] = {} for e in turns: d = per.setdefault(e["device_id"], {"turns": 0, "local": 0, "down": None, "down_at": None}) d["turns"] += 1 d["local"] += e["props"].get("route") == "LOCAL" if e["props"].get("downgrade_reason"): d["down"], d["down_at"] = e["props"]["downgrade_reason"], e["at"] from app.account import masked_accounts acc = masked_accounts() rows = [] for did in set(caps) | set(registered) | set(last_tel): if not did: continue c = caps.get(did, {}) t = per.get(did, {"turns": 0, "local": 0, "down": None, "down_at": None}) down, down_at = t["down"], t["down_at"] if c.get("downgrade_reason") and (down_at is None or c["_at"] > down_at): down, down_at = c["downgrade_reason"], c["_at"] rows.append({ "device_id": did, "short": did[:8], "model": c.get("model"), "total_mem_gb": c.get("total_mem_gb"), "tier": c.get("tier"), "app_version": c.get("app_version"), "last_seen": max(filter(None, (last_tel.get(did), registered.get(did))), default=None), "cases": n_cases.get(did, 0), "turns": t["turns"], "local_ratio": round(t["local"] / t["turns"], 3) if t["turns"] else None, "last_downgrade": down, "last_downgrade_at": down_at, "account": acc.get(did), # {email_masked, devices} | None (FR-ACC-04: never the full email) }) ql = q.strip().lower() if ql: rows = [r for r in rows if r["device_id"].lower().startswith(ql) or ql in (r["model"] or "").lower()] if tier != "": rows = [r for r in rows if str(r["tier"]) == tier] return sorted(rows, key=lambda r: r["last_seen"] or "", reverse=True) def conv_meta() -> dict[tuple, dict]: """(session_id, conv_id) → {origin of the first row, sorted assistant routes} for the sessions filter (FR-TELE-12).""" meta: dict[tuple, dict] = {} with db.read_connection() as conn: for r in conn.execute(select(messages.c.session_id, messages.c.conv_id, messages.c.origin, messages.c.role, messages.c.route).order_by(messages.c.ts)): m = meta.setdefault((r.session_id, r.conv_id), {"origin": r.origin or "web", "routes": set()}) if r.role == "assistant": m["routes"].add(r.route or "API") return {k: {"origin": v["origin"], "routes": sorted(v["routes"])} for k, v in meta.items()} # ── P3-A: case page, last 7 days, 👎 handling (docs/11 §8.7; TEL-05/07/08) ───────────────────────────────────── TPE = timezone(timedelta(hours=8)) def _json_field(v): try: return json.loads(v) if isinstance(v, str) and v else v except ValueError: return v def case_detail(case_id: str) -> dict | None: """Everything about one App case for the admin (TEL-05). Exact GPS never leaves the DB (TEL-01).""" from app.db import feedback, images with db.read_connection() as conn: row = conn.execute(select(cases).where(cases.c.case_id == case_id)).mappings().first() if row is None: return None imgs = conn.execute(select(*[c for c in images.c if c.name not in ("lat", "lon")]) .where(images.c.case_id == case_id).order_by(images.c.ts)).mappings().all() msgs = conn.execute(select(messages).where(messages.c.case_id == case_id).order_by(messages.c.ts)).mappings().all() fbs = conn.execute(select(feedback).where(feedback.c.case_id == case_id)).mappings().all() c = dict(row) for k in ("questionnaire_answers", "rapid_test"): c[k] = _json_field(c.get(k)) dev = next((d for d in device_list() if d["device_id"] == c["device_id"]), None) return {"case": c, "images": [dict(i) for i in imgs], "messages": [dict(m) for m in msgs], "feedback": [dict(f) for f in fbs], "device": dev} def daily_stats(days: int = 7, now: datetime | None = None) -> dict: """Per Taipei day, oldest first, missing days as 0 (TEL-07) + the unreviewed 👎 count (TEL-08).""" from app.db import feedback, images now = now or datetime.now(timezone.utc) today = now.astimezone(TPE).replace(hour=0, minute=0, second=0, microsecond=0) start = today - timedelta(days=days - 1) utc = lambda d: d.astimezone(timezone.utc).replace(tzinfo=None).isoformat() # noqa: E731 def bucket(conn, col, *where) -> Counter: out: Counter = Counter() for (ts,) in conn.execute(select(col).where(col >= utc(start), *where)): try: t = datetime.fromisoformat(str(ts).replace("Z", "+00:00")) except ValueError: continue t = t if t.tzinfo else t.replace(tzinfo=timezone.utc) out[t.astimezone(TPE).date().isoformat()] += 1 return out with db.read_connection() as conn: n_cases = bucket(conn, cases.c.created_at, cases.c.hidden == 0) n_msgs = bucket(conn, messages.c.ts) n_imgs = bucket(conn, images.c.ts) n_down = bucket(conn, feedback.c.ts, feedback.c.rating == "down") unreviewed = conn.scalar(select(func.count()).select_from(feedback).where( feedback.c.rating == "down", feedback.c.reviewed_at.is_(None))) or 0 rows = [] for i in range(days): d = (start + timedelta(days=i)).date().isoformat() rows.append({"day": d, "cases": n_cases[d], "messages": n_msgs[d], "images": n_imgs[d], "down": n_down[d]}) return {"days": rows, "unreviewed_down": unreviewed} def feedback_rows() -> list[dict]: """Legacy feedback rows + the rated answer and the question before it (TEL-08).""" from app.db import feedback with db.read_connection() as conn: fbs = [dict(r) for r in conn.execute(select(feedback).order_by(feedback.c.ts.desc())).mappings()] ids = [f["msg_id"] for f in fbs if f.get("msg_id")] answers = {r.msg_id: r for r in conn.execute( select(messages.c.msg_id, messages.c.conv_id, messages.c.content, messages.c.ts).where(messages.c.msg_id.in_(ids)))} out = [] for f in fbs: a = answers.get(f.get("msg_id")) q = None if a is not None: q = conn.execute(select(messages.c.content).where( messages.c.conv_id == a.conv_id, messages.c.role == "user", messages.c.ts <= a.ts) .order_by(messages.c.ts.desc()).limit(1)).scalar() out.append(f | {"answer": a.content[:300] if a is not None else None, "question": q[:200] if q else None}) return out def review_feedback(fb_id: str, note: str | None, unreview: bool = False) -> bool: from app.db import feedback, now_iso from sqlalchemy import update vals = {"reviewed_at": None, "review_note": None} if unreview else {"reviewed_at": now_iso(), "review_note": (note or "")[:500]} with db.engine().begin() as conn: return conn.execute(update(feedback).where(feedback.c.fb_id == fb_id).values(**vals)).rowcount == 1