# retoor <retoor@molodetz.nl>

from __future__ import annotations

from devplacepy.config import PRESENCE_TIMEOUT_SECONDS
from devplacepy.database import (
    SOFT_DELETE_TABLES,
    count_deleted,
    db,
    get_platform_analytics,
    get_site_stats,
    get_users_by_uids,
)
from devplacepy.services.manager import service_manager
from devplacepy.services.openai_gateway.analytics import build_analytics
from devplacepy.services.openai_gateway.usage import pricing_from_cfg
from devplacepy.services.presence import online_cutoff_iso as presence_cutoff
from devplacepy.services.statistics.common import (
    bucket_expr,
    metric_card,
    series_payload,
    table_payload,
    window_config,
)
from devplacepy.services.statistics.tracking import RETENTION_DAYS

GATEWAY_LEDGER = "gateway_usage_ledger"


def _table_exists(name: str) -> bool:
    return name in db.tables


def _username(users_map: dict, uid: str) -> str:
    user = users_map.get(uid)
    if user and user.get("username"):
        return user["username"]
    return uid


def _instance_label(inst: dict) -> str:
    name = (inst.get("name") or "").strip()
    if name:
        return name
    slug = (inst.get("slug") or "").strip()
    if slug:
        return slug
    return inst.get("uid", "")


def _has_column(table: str, column: str) -> bool:
    if not _table_exists(table):
        return False
    try:
        return db[table].has_column(column)
    except Exception:
        return False


def _live_clause(table: str, alias: str = "") -> str:
    prefix = f"{alias}" if alias else ""
    if _has_column(table, "deleted_at"):
        return f"{prefix}deleted_at IS NULL"
    return "1=1"


def _count_window(
    table: str,
    start: str,
    end: str,
    extra: str = "",
    *,
    date_column: str = "created_at",
) -> int:
    if not _table_exists(table) or not _has_column(table, date_column):
        return 0
    clause = "1=1"
    if _live_clause(table) != "1=1":
        clause = _live_clause(table)
    if extra:
        clause = f"{clause} AND {extra}"
    rows = list(
        db.query(
            f"SELECT COUNT(*) AS c FROM {table} WHERE {clause} "
            f"AND {date_column} >= :start AND {date_column} < :end",
            start=start,
            end=end,
        )
    )
    return int(rows[0]["c"]) if rows else 0


def _sum_window(
    table: str,
    column: str,
    start: str,
    end: str,
    extra: str = "",
    *,
    date_column: str = "created_at",
) -> float:
    if not _table_exists(table) or not _has_column(table, date_column):
        return 0.0
    clause = extra or "1=1"
    rows = list(
        db.query(
            f"SELECT COALESCE(SUM({column}), 0) AS s FROM {table} "
            f"WHERE {date_column} >= :start AND {date_column} < :end AND " + clause,
            start=start,
            end=end,
        )
    )
    return float(rows[0]["s"]) if rows else 0.0


def _series_table(
    table: str,
    window: dict,
    *,
    extra: str = "",
    value_expr: str = "COUNT(*)",
    date_column: str = "created_at",
) -> dict[str, float]:
    if not _table_exists(table) or not window["start"] or not _has_column(table, date_column):
        return {}
    clause = extra or "1=1"
    live = _live_clause(table)
    if live != "1=1":
        clause = f"{live} AND {clause}"
    expr = bucket_expr(window["granularity"], date_column)
    rows = db.query(
        f"SELECT {expr} AS bucket, {value_expr} AS v FROM {table} "
        f"WHERE {date_column} >= :start AND {date_column} < :end AND {clause} "
        "GROUP BY bucket ORDER BY bucket",
        start=window["start"],
        end=window["end"],
    )
    return {row["bucket"]: float(row["v"]) for row in rows if row.get("bucket")}


def _active_members(start: str, end: str) -> int:
    sources = [
        table
        for table in ("posts", "comments", "gists", "projects")
        if _table_exists(table)
    ]
    if not sources:
        return 0
    union = " UNION ALL ".join(
        f"SELECT user_uid, created_at FROM {table} WHERE deleted_at IS NULL"
        for table in sources
    )
    rows = list(
        db.query(
            f"SELECT COUNT(DISTINCT user_uid) AS c FROM ({union}) "
            "WHERE created_at >= :start AND created_at < :end",
            start=start,
            end=end,
        )
    )
    return int(rows[0]["c"]) if rows else 0


def _online_now() -> int:
    if not _table_exists("users"):
        return 0
    cutoff = presence_cutoff()
    rows = list(
        db.query(
            "SELECT COUNT(*) AS c FROM users WHERE last_seen >= :cutoff",
            cutoff=cutoff,
        )
    )
    return int(rows[0]["c"]) if rows else 0


def _visit_totals(start: str, end: str) -> dict:
    if not _table_exists("visit_stats_hourly"):
        return {"views": 0, "member_views": 0, "guest_views": 0, "unique_visitors": 0}
    rows = list(
        db.query(
            "SELECT COALESCE(SUM(views), 0) AS views, "
            "COALESCE(SUM(member_views), 0) AS member_views, "
            "COALESCE(SUM(guest_views), 0) AS guest_views "
            "FROM visit_stats_hourly WHERE bucket_start >= :start AND bucket_start < :end",
            start=start,
            end=end,
        )
    )
    totals = rows[0] if rows else {}
    unique_rows = list(
        db.query(
            "SELECT COUNT(DISTINCT visitor_hash) AS c FROM visit_unique_slots "
            "WHERE bucket_start >= :start AND bucket_start < :end",
            start=start,
            end=end,
        )
    )
    return {
        "views": int(totals.get("views") or 0),
        "member_views": int(totals.get("member_views") or 0),
        "guest_views": int(totals.get("guest_views") or 0),
        "unique_visitors": int(unique_rows[0]["c"]) if unique_rows else 0,
    }


def _visit_series(window: dict, field: str) -> dict[str, float]:
    if not _table_exists("visit_stats_hourly") or not window["start"]:
        return {}
    if field == "unique_visitors":
        expr = bucket_expr(window["granularity"], "bucket_start")
        rows = db.query(
            f"SELECT {expr} AS bucket, COUNT(DISTINCT visitor_hash) AS v "
            "FROM visit_unique_slots "
            "WHERE bucket_start >= :start AND bucket_start < :end "
            "GROUP BY bucket ORDER BY bucket",
            start=window["start"],
            end=window["end"],
        )
        return {row["bucket"]: float(row["v"]) for row in rows if row.get("bucket")}
    rows = db.query(
        f"SELECT {bucket_expr(window['granularity'], 'bucket_start')} AS bucket, "
        f"COALESCE(SUM({field}), 0) AS v FROM visit_stats_hourly "
        "WHERE bucket_start >= :start AND bucket_start < :end "
        "GROUP BY bucket ORDER BY bucket",
        start=window["start"],
        end=window["end"],
    )
    return {row["bucket"]: float(row["v"]) for row in rows if row.get("bucket")}


def build_overview(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    compare_start = window["compare_start"] or start
    site = get_site_stats()
    platform = get_platform_analytics(top_n)
    current_active = _active_members(start, end) if window["start"] else platform["active_30d"]
    previous_active = (
        _active_members(compare_start, start) if compare and window["start"] else 0
    )
    signups = _count_window("users", start, end) if window["start"] else site["total_members"]
    prev_signups = _count_window("users", compare_start, start) if compare and window["start"] else 0
    posts = _count_window("posts", start, end) if window["start"] else platform["totals"]["posts"]
    prev_posts = _count_window("posts", compare_start, start) if compare and window["start"] else 0
    ai_spend = 0.0
    prev_ai_spend = 0.0
    if _table_exists(GATEWAY_LEDGER):
        ai_spend = _sum_window(GATEWAY_LEDGER, "cost_usd", start, end)
        if compare and window["start"]:
            prev_ai_spend = _sum_window(GATEWAY_LEDGER, "cost_usd", compare_start, start)
    deleted = sum(count_deleted(table) for table in SOFT_DELETE_TABLES if _table_exists(table))
    visits = _visit_totals(start, end) if window["start"] else {"views": 0, "unique_visitors": 0}
    cards = [
        metric_card(
            "members",
            "Total members",
            site["total_members"],
            format_kind="int",
        ),
        metric_card(
            "signups",
            "New signups",
            signups,
            current=signups,
            previous=prev_signups if compare else None,
        ),
        metric_card(
            "active",
            "Active members",
            current_active,
            current=current_active,
            previous=previous_active if compare else None,
        ),
        metric_card(
            "online",
            "Online now",
            _online_now(),
            format_kind="int",
        ),
        metric_card(
            "posts",
            "Posts created",
            posts,
            current=posts,
            previous=prev_posts if compare else None,
        ),
        metric_card(
            "views",
            "Page views",
            visits["views"],
            format_kind="int",
        ),
        metric_card(
            "ai_spend",
            "AI spend",
            round(ai_spend, 4),
            current=ai_spend,
            previous=prev_ai_spend if compare else None,
            format_kind="money",
        ),
        metric_card("trash", "In trash", deleted, format_kind="int"),
    ]
    series = []
    if window["start"]:
        series.append(
            series_payload(
                "signups",
                "Signups",
                _series_table("users", window),
                window,
            )
        )
        series.append(
            series_payload(
                "posts",
                "Posts",
                _series_table("posts", window),
                window,
            )
        )
        series.append(
            series_payload(
                "comments",
                "Comments",
                _series_table("comments", window),
                window,
            )
        )
        series.append(
            series_payload(
                "views",
                "Page views",
                _visit_series(window, "views"),
                window,
            )
        )
    highlights = _highlights()
    tables = [
        table_payload(
            "top_authors",
            "Top authors",
            ["Username", "Stars"],
            [[author["username"], author["stars"]] for author in platform["top_authors"]],
        )
    ]
    return {
        "cards": cards,
        "series": series,
        "tables": tables,
        "highlights": highlights,
        "notes": {
            "active": platform["active_definition"],
            "presence_timeout": PRESENCE_TIMEOUT_SECONDS,
        },
    }


def build_visitors(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    compare_start = window["compare_start"] or start
    current = _visit_totals(start, end)
    previous = _visit_totals(compare_start, start) if compare and window["start"] else {}
    cards = [
        metric_card(
            "views",
            "Page views",
            current["views"],
            current=current["views"],
            previous=previous.get("views") if compare else None,
        ),
        metric_card(
            "unique",
            "Unique visitors",
            current["unique_visitors"],
            current=current["unique_visitors"],
            previous=previous.get("unique_visitors") if compare else None,
        ),
        metric_card("member_views", "Member views", current["member_views"], format_kind="int"),
        metric_card("guest_views", "Guest views", current["guest_views"], format_kind="int"),
        metric_card("online", "Online now", _online_now(), format_kind="int"),
    ]
    series = []
    if window["start"]:
        for key, label, field in (
            ("views", "Page views", "views"),
            ("unique", "Unique visitors", "unique_visitors"),
            ("member_views", "Member views", "member_views"),
            ("guest_views", "Guest views", "guest_views"),
        ):
            series.append(series_payload(key, label, _visit_series(window, field), window))
    tables = []
    if _table_exists("visit_stats_hourly") and window["start"]:
        page_rows = db.query(
            "SELECT page_group, SUM(views) AS views FROM visit_stats_hourly "
            "WHERE bucket_start >= :start AND bucket_start < :end "
            "GROUP BY page_group ORDER BY views DESC LIMIT :limit",
            start=start,
            end=end,
            limit=top_n,
        )
        tables.append(
            table_payload(
                "pages",
                "Top page groups",
                ["Page group", "Views"],
                [[row["page_group"], int(row["views"] or 0)] for row in page_rows],
            )
        )
        ref_rows = db.query(
            "SELECT referrer_group, SUM(views) AS views FROM visit_stats_hourly "
            "WHERE bucket_start >= :start AND bucket_start < :end "
            "GROUP BY referrer_group ORDER BY views DESC LIMIT :limit",
            start=start,
            end=end,
            limit=top_n,
        )
        tables.append(
            table_payload(
                "referrers",
                "Top referrers",
                ["Referrer", "Views"],
                [[row["referrer_group"], int(row["views"] or 0)] for row in ref_rows],
            )
        )
    return {
        "cards": cards,
        "series": series,
        "tables": tables,
        "notes": {"retention_days": RETENTION_DAYS},
    }


def build_members(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    compare_start = window["compare_start"] or start
    signups = _count_window("users", start, end) if window["start"] else db["users"].count()
    prev_signups = _count_window("users", compare_start, start) if compare and window["start"] else 0
    disabled = db["users"].count(is_active=0) if _table_exists("users") else 0
    avg_level = 0.0
    avg_xp = 0.0
    if _table_exists("users"):
        row = list(db.query("SELECT AVG(level) AS l, AVG(xp) AS x FROM users"))
        if row:
            avg_level = round(float(row[0]["l"] or 0), 1)
            avg_xp = round(float(row[0]["x"] or 0), 0)
    cards = [
        metric_card(
            "signups",
            "New signups",
            signups,
            current=signups,
            previous=prev_signups if compare else None,
        ),
        metric_card("total", "Total members", db["users"].count() if _table_exists("users") else 0),
        metric_card("disabled", "Disabled accounts", disabled),
        metric_card("avg_level", "Avg level", avg_level, format_kind="decimal"),
        metric_card("avg_xp", "Avg XP", avg_xp, format_kind="int"),
    ]
    series = []
    if window["start"]:
        series.append(series_payload("signups", "Signups", _series_table("users", window), window))
    tables = []
    if _table_exists("users") and window["start"]:
        active_rows = db.query(
            "SELECT u.username, COUNT(*) AS c FROM ("
            "SELECT user_uid, created_at FROM posts WHERE deleted_at IS NULL "
            "UNION ALL SELECT user_uid, created_at FROM comments WHERE deleted_at IS NULL "
            "UNION ALL SELECT user_uid, created_at FROM gists WHERE deleted_at IS NULL "
            "UNION ALL SELECT user_uid, created_at FROM projects WHERE deleted_at IS NULL"
            ") a JOIN users u ON u.uid = a.user_uid "
            "WHERE a.created_at >= :start AND a.created_at < :end "
            "GROUP BY u.username ORDER BY c DESC LIMIT :limit",
            start=start,
            end=end,
            limit=top_n,
        )
        tables.append(
            table_payload(
                "active",
                "Most active members",
                ["Username", "Actions"],
                [[row["username"], int(row["c"] or 0)] for row in active_rows],
            )
        )
        newest = list(
            db["users"].find(order_by=["-created_at"], _limit=top_n)
        )
        tables.append(
            table_payload(
                "newest",
                "Newest members",
                ["Username", "Joined"],
                [[row.get("username", ""), (row.get("created_at") or "")[:10]] for row in newest],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_content(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    compare_start = window["compare_start"] or start
    kinds = (
        ("posts", "Posts", "created_at"),
        ("comments", "Comments", "created_at"),
        ("gists", "Gists", "created_at"),
        ("projects", "Projects", "created_at"),
        ("news", "News", "synced_at"),
    )
    cards = []
    series = []
    for kind, label, date_column in kinds:
        count = _count_window(kind, start, end, date_column=date_column) if window["start"] else (
            db[kind].count(deleted_at=None) if _table_exists(kind) else 0
        )
        prev = (
            _count_window(kind, compare_start, start, date_column=date_column)
            if compare and window["start"]
            else 0
        )
        cards.append(
            metric_card(
                kind,
                label,
                count,
                current=count,
                previous=prev if compare else None,
            )
        )
        if window["start"]:
            series.append(
                series_payload(
                    kind,
                    label,
                    _series_table(kind, window, date_column=date_column),
                    window,
                )
            )
    tables = []
    if _table_exists("posts") and window["start"]:
        post_live = _live_clause("posts")
        topic_rows = db.query(
            f"SELECT topic, COUNT(*) AS c FROM posts "
            f"WHERE {post_live} AND created_at >= :start AND created_at < :end "
            "GROUP BY topic ORDER BY c DESC LIMIT :limit",
            start=start,
            end=end,
            limit=top_n,
        )
        tables.append(
            table_payload(
                "topics",
                "Top topics",
                ["Topic", "Posts"],
                [[row["topic"] or "(none)", int(row["c"] or 0)] for row in topic_rows],
            )
        )
    if _table_exists("gists"):
        gist_live = _live_clause("gists")
        window_clause = ""
        params: dict = {}
        if window["start"]:
            window_clause = " AND created_at >= :start AND created_at < :end"
            params = {"start": start, "end": end}
        lang_rows = db.query(
            f"SELECT language, COUNT(*) AS c FROM gists WHERE {gist_live}"
            f"{window_clause} GROUP BY language ORDER BY c DESC, language ASC",
            **params,
        )
        if lang_rows:
            tables.append(
                table_payload(
                    "languages",
                    "Gist languages",
                    ["Language", "Gists"],
                    [
                        [row["language"] or "(none)", int(row["c"] or 0)]
                        for row in lang_rows
                    ],
                )
            )
    return {"cards": cards, "series": series, "tables": tables}


def build_engagement(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    compare_start = window["compare_start"] or start
    kinds = (
        ("votes", "Votes"),
        ("reactions", "Reactions"),
        ("bookmarks", "Bookmarks"),
        ("follows", "Follows"),
    )
    cards = []
    series = []
    for key, label in kinds:
        count = _count_window(key, start, end) if window["start"] else (
            db[key].count(deleted_at=None) if _table_exists(key) else 0
        )
        prev = _count_window(key, compare_start, start) if compare and window["start"] else 0
        cards.append(
            metric_card(key, label, count, current=count, previous=prev if compare else None)
        )
        if window["start"]:
            series.append(series_payload(key, label, _series_table(key, window), window))
    if _table_exists("poll_votes") and window["start"]:
        poll_count = _count_window("poll_votes", start, end)
        cards.append(metric_card("poll_votes", "Poll votes", poll_count))
        series.append(
            series_payload("poll_votes", "Poll votes", _series_table("poll_votes", window), window)
        )
    tables = []
    if _table_exists("reactions"):
        reaction_live = _live_clause("reactions")
        emoji_rows = db.query(
            f"SELECT emoji, COUNT(*) AS c FROM reactions WHERE {reaction_live} "
            + ("AND created_at >= :start AND created_at < :end " if window["start"] else "")
            + "GROUP BY emoji ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "emoji",
                "Top reaction emojis",
                ["Emoji", "Count"],
                [[row["emoji"], int(row["c"] or 0)] for row in emoji_rows],
            )
        )
    if _table_exists("votes") and window["start"]:
        vote_live = _live_clause("votes")
        vote_rows = list(
            db.query(
                "SELECT SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) AS up, "
                "SUM(CASE WHEN value < 0 THEN 1 ELSE 0 END) AS down "
                f"FROM votes WHERE {vote_live} "
                "AND created_at >= :start AND created_at < :end",
                start=start,
                end=end,
            )
        )
        if vote_rows:
            tables.append(
                table_payload(
                    "vote_ratio",
                    "Vote ratio",
                    ["Upvotes", "Downvotes"],
                    [[int(vote_rows[0]["up"] or 0), int(vote_rows[0]["down"] or 0)]],
                )
            )
    return {"cards": cards, "series": series, "tables": tables}


def build_social(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    compare_start = window["compare_start"] or start
    messages = _count_window("messages", start, end) if window["start"] else (
        db["messages"].count(deleted_at=None) if _table_exists("messages") else 0
    )
    prev_messages = _count_window("messages", compare_start, start) if compare and window["start"] else 0
    notifications = _count_window("notifications", start, end) if window["start"] else (
        db["notifications"].count(deleted_at=None) if _table_exists("notifications") else 0
    )
    follows = _count_window("follows", start, end) if window["start"] else (
        db["follows"].count(deleted_at=None) if _table_exists("follows") else 0
    )
    push_count = db["push_registration"].count() if _table_exists("push_registration") else 0
    telegram_count = db["telegram_links"].count() if _table_exists("telegram_links") else 0
    cards = [
        metric_card(
            "messages",
            "Messages sent",
            messages,
            current=messages,
            previous=prev_messages if compare else None,
        ),
        metric_card("notifications", "Notifications", notifications),
        metric_card("follows", "New follows", follows),
        metric_card("push", "Push subscriptions", push_count),
        metric_card("telegram", "Telegram links", telegram_count),
    ]
    series = []
    if window["start"]:
        series.append(series_payload("messages", "Messages", _series_table("messages", window), window))
        series.append(
            series_payload("notifications", "Notifications", _series_table("notifications", window), window)
        )
        series.append(series_payload("follows", "Follows", _series_table("follows", window), window))
    tables = []
    if _table_exists("notifications"):
        notif_live = _live_clause("notifications")
        type_rows = db.query(
            f"SELECT type, COUNT(*) AS c FROM notifications WHERE {notif_live} "
            + ("AND created_at >= :start AND created_at < :end " if window["start"] else "")
            + "GROUP BY type ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "notification_types",
                "Notification types",
                ["Type", "Count"],
                [[row["type"], int(row["c"] or 0)] for row in type_rows],
            )
        )
    if _table_exists("follows"):
        follow_live = _live_clause("follows", "f.")
        followed_rows = db.query(
            "SELECT u.username, COUNT(*) AS c FROM follows f "
            "JOIN users u ON u.uid = f.following_uid "
            f"WHERE {follow_live} "
            + ("AND f.created_at >= :start AND f.created_at < :end " if window["start"] else "")
            + "GROUP BY u.username ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "most_followed",
                "Most followed",
                ["Username", "New followers"],
                [[row["username"], int(row["c"] or 0)] for row in followed_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_ai(hours: int, compare: bool, top_n: int) -> dict:
    bounded = hours if hours > 0 else 168
    service = service_manager.get_service("openai")
    pricing = pricing_from_cfg(service.get_config()) if service is not None else None
    payload = build_analytics(bounded, top_n=top_n, pricing=pricing)
    cards = [
        metric_card("requests", "Requests", payload.get("requests", 0)),
        metric_card(
            "cost",
            "Cost USD",
            payload.get("cost", {}).get("total_usd", 0),
            format_kind="money",
        ),
        metric_card(
            "tokens",
            "Total tokens",
            payload.get("tokens", {}).get("total", 0),
        ),
        metric_card(
            "errors",
            "Failed requests",
            payload.get("errors", {}).get("failed", 0),
        ),
        metric_card(
            "latency_p95",
            "Latency p95 ms",
            payload.get("latency", {}).get("upstream", {}).get("p95", 0),
            format_kind="decimal",
        ),
    ]
    series = []
    hourly = payload.get("hourly") or []
    if hourly:
        points = {row["hour"]: float(row.get("requests") or 0) for row in hourly if row.get("hour")}
        window = window_config(bounded)
        series.append(series_payload("requests", "Requests", points, window))
        cost_points = {row["hour"]: float(row.get("cost_usd") or 0) for row in hourly if row.get("hour")}
        series.append(series_payload("cost", "Cost USD", cost_points, window))
    tables = []
    models = (payload.get("cost") or {}).get("per_model") or []
    if models:
        tables.append(
            table_payload(
                "models",
                "Top models by cost",
                ["Model", "Requests", "Cost"],
                [
                    [row.get("key", ""), row.get("requests", 0), round(row.get("cost_usd", 0), 4)]
                    for row in models[:top_n]
                ],
            )
        )
    return {
        "cards": cards,
        "series": series,
        "tables": tables,
        "notes": {"full_page": "/admin/ai-usage"},
    }


def build_devii(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    turns = _count_window("devii_turns", start, end, "1=1") if window["start"] else (
        db["devii_turns"].count() if _table_exists("devii_turns") else 0
    )
    cost = _sum_window("devii_usage_ledger", "cost_usd", start, end) if _table_exists("devii_usage_ledger") else 0.0
    tool_calls = 0
    if _table_exists("devii_turns") and window["start"]:
        row = list(
            db.query(
                "SELECT COALESCE(SUM(tool_calls), 0) AS s FROM devii_turns "
                "WHERE started_at >= :start AND started_at < :end",
                start=start,
                end=end,
            )
        )
        tool_calls = int(row[0]["s"]) if row else 0
    errors = 0
    if _table_exists("devii_turns") and window["start"]:
        errors = _count_window("devii_turns", start, end, "error IS NOT NULL AND error != ''")
    cards = [
        metric_card("turns", "Turns", turns),
        metric_card("cost", "Cost USD", round(cost, 4), format_kind="money"),
        metric_card("tool_calls", "Tool calls", tool_calls),
        metric_card("errors", "Errors", errors),
    ]
    series = []
    if _table_exists("devii_turns") and window["start"]:
        expr = bucket_expr(window["granularity"], "started_at")
        rows = db.query(
            f"SELECT {expr} AS bucket, COUNT(*) AS v FROM devii_turns "
            "WHERE started_at >= :start AND started_at < :end GROUP BY bucket",
            start=start,
            end=end,
        )
        points = {row["bucket"]: float(row["v"]) for row in rows if row.get("bucket")}
        series.append(series_payload("turns", "Turns", points, window))
    tables = []
    if _table_exists("devii_turns"):
        owner_rows = db.query(
            "SELECT owner_kind, COUNT(*) AS c FROM devii_turns "
            + ("WHERE started_at >= :start AND started_at < :end " if window["start"] else "")
            + "GROUP BY owner_kind ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "owners",
                "Turns by owner kind",
                ["Owner", "Turns"],
                [[row["owner_kind"], int(row["c"] or 0)] for row in owner_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_services(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    completed = 0
    failed = 0
    if _table_exists("jobs"):
        if window["start"]:
            completed = _count_window("jobs", start, end, "status = 'completed'")
            failed = _count_window("jobs", start, end, "status = 'failed'")
        else:
            completed = db["jobs"].count(status="completed")
            failed = db["jobs"].count(status="failed")
    avg_duration = 0.0
    if _table_exists("jobs") and window["start"]:
        row = list(
            db.query(
                "SELECT AVG(duration_ms) AS a FROM jobs "
                "WHERE status = 'completed' AND created_at >= :start AND created_at < :end",
                start=start,
                end=end,
            )
        )
        if row and row[0]["a"]:
            avg_duration = round(float(row[0]["a"]), 0)
    running_services = 0
    if _table_exists("service_state"):
        running_services = db["service_state"].count(status="running")
    cards = [
        metric_card("completed", "Jobs completed", completed),
        metric_card("failed", "Jobs failed", failed),
        metric_card("avg_duration", "Avg duration ms", avg_duration, format_kind="decimal"),
        metric_card("running_services", "Running services", running_services),
    ]
    series = []
    if _table_exists("jobs") and window["start"]:
        series.append(
            series_payload("completed", "Completed jobs", _series_table("jobs", window, extra="status = 'completed'"), window)
        )
        series.append(
            series_payload("failed", "Failed jobs", _series_table("jobs", window, extra="status = 'failed'"), window)
        )
    tables = []
    if _table_exists("jobs"):
        kind_rows = db.query(
            "SELECT kind, status, COUNT(*) AS c FROM jobs "
            + ("WHERE created_at >= :start AND created_at < :end " if window["start"] else "")
            + "GROUP BY kind, status ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "job_kinds",
                "Jobs by kind",
                ["Kind", "Status", "Count"],
                [[row["kind"], row["status"], int(row["c"] or 0)] for row in kind_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_containers(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    running = db["instances"].count(status="running") if _table_exists("instances") else 0
    restarts = 0
    if _table_exists("instances"):
        row = list(db.query("SELECT COALESCE(SUM(restart_count), 0) AS s FROM instances"))
        restarts = int(row[0]["s"]) if row else 0
    events = _count_window("instance_events", start, end) if _table_exists("instance_events") and window["start"] else 0
    cards = [
        metric_card("running", "Running instances", running),
        metric_card("restarts", "Total restarts", restarts),
        metric_card("events", "Events in window", events),
    ]
    series = []
    if _table_exists("instance_events") and window["start"]:
        series.append(
            series_payload("events", "Instance events", _series_table("instance_events", window), window)
        )
    tables = []
    if _table_exists("instances"):
        top_restart = list(
            db["instances"].find(order_by=["-restart_count"], _limit=top_n)
        )
        tables.append(
            table_payload(
                "restarts",
                "Most restarted instances",
                ["Instance", "Restarts", "Status"],
                [
                    [
                        _instance_label(row),
                        int(row.get("restart_count") or 0),
                        row.get("status", ""),
                    ]
                    for row in top_restart
                ],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_game(hours: int, compare: bool, top_n: int) -> dict:
    farms = db["game_farms"].count() if _table_exists("game_farms") else 0
    steals = 0
    harvests = 0
    coins = 0
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    if _table_exists("game_steals"):
        if window["start"]:
            row = list(
                db.query(
                    "SELECT COUNT(*) AS c FROM game_steals "
                    "WHERE stolen_at >= :start AND stolen_at < :end",
                    start=start,
                    end=end,
                )
            )
            steals = int(row[0]["c"]) if row else 0
        else:
            steals = db["game_steals"].count()
    if _table_exists("game_farms"):
        row = list(db.query("SELECT COALESCE(SUM(total_harvests), 0) AS h, COALESCE(SUM(coins), 0) AS c FROM game_farms"))
        if row:
            harvests = int(row[0]["h"] or 0)
            coins = int(row[0]["c"] or 0)
    cards = [
        metric_card("farms", "Active farms", farms),
        metric_card("steals", "Steals in window", steals),
        metric_card("harvests", "Total harvests", harvests),
        metric_card("coins", "Coins in circulation", coins),
    ]
    series = []
    if _table_exists("game_steals") and window["start"]:
        series.append(
            series_payload(
                "steals",
                "Steals",
                _series_table("game_steals", window, extra="1=1", date_column="stolen_at"),
                window,
            )
        )
    tables = []
    if _table_exists("game_farms"):
        leaders = list(db["game_farms"].find(order_by=["-coins"], _limit=top_n))
        users_map = get_users_by_uids([row.get("user_uid") for row in leaders if row.get("user_uid")])
        tables.append(
            table_payload(
                "richest",
                "Richest farms",
                ["Username", "Coins", "Level"],
                [
                    [
                        _username(users_map, row.get("user_uid", "")),
                        int(row.get("coins") or 0),
                        int(row.get("level") or 0),
                    ]
                    for row in leaders
                ],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_awards(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    given = _count_window("awards", start, end) if _table_exists("awards") and window["start"] else (
        db["awards"].count(deleted_at=None) if _table_exists("awards") else 0
    )
    badges = _count_window("badges", start, end) if _table_exists("badges") and window["start"] else (
        db["badges"].count() if _table_exists("badges") else 0
    )
    cards = [
        metric_card("awards", "Awards given", given),
        metric_card("badges", "Badges earned", badges),
    ]
    series = []
    if window["start"]:
        if _table_exists("awards"):
            series.append(series_payload("awards", "Awards", _series_table("awards", window), window))
        if _table_exists("badges"):
            series.append(series_payload("badges", "Badges", _series_table("badges", window), window))
    tables = []
    if _table_exists("badges"):
        badge_rows = db.query(
            "SELECT badge_name, COUNT(*) AS c FROM badges "
            + ("WHERE created_at >= :start AND created_at < :end " if window["start"] else "")
            + "GROUP BY badge_name ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "badge_names",
                "Badges by name",
                ["Badge", "Count"],
                [[row["badge_name"], int(row["c"] or 0)] for row in badge_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_moderation(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    deleted = sum(count_deleted(table) for table in SOFT_DELETE_TABLES if _table_exists(table))
    blocks = db["user_relations"].count(kind="block") if _table_exists("user_relations") else 0
    denials = 0
    if _table_exists("audit_log") and window["start"]:
        denials = _count_window("audit_log", start, end, "result = 'denied'")
    cards = [
        metric_card("trash", "Items in trash", deleted),
        metric_card("blocks", "Blocks", blocks),
        metric_card("denials", "Audit denials", denials),
    ]
    series = []
    if _table_exists("audit_log") and window["start"]:
        series.append(
            series_payload(
                "denials",
                "Audit denials",
                _series_table("audit_log", window, extra="result = 'denied'"),
                window,
            )
        )
    tables = []
    trash_rows = []
    for table in SOFT_DELETE_TABLES:
        if not _table_exists(table):
            continue
        count = count_deleted(table)
        if count:
            trash_rows.append([table, count])
    trash_rows.sort(key=lambda row: row[1], reverse=True)
    if trash_rows:
        tables.append(
            table_payload(
                "trash_tables",
                "Trash by table",
                ["Table", "Count"],
                trash_rows[:top_n],
            )
        )
    if _table_exists("audit_log") and window["start"]:
        cat_rows = db.query(
            "SELECT category, COUNT(*) AS c FROM audit_log "
            "WHERE created_at >= :start AND created_at < :end "
            "GROUP BY category ORDER BY c DESC LIMIT :limit",
            start=start,
            end=end,
            limit=top_n,
        )
        tables.append(
            table_payload(
                "audit_categories",
                "Audit by category",
                ["Category", "Events"],
                [[row["category"], int(row["c"] or 0)] for row in cat_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_tools(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    deepsearch = _count_window("deepsearch_sessions", start, end) if _table_exists("deepsearch_sessions") and window["start"] else (
        db["deepsearch_sessions"].count(deleted_at=None) if _table_exists("deepsearch_sessions") else 0
    )
    isslop = 0
    if _table_exists("isslop_analyses"):
        isslop = _count_window("isslop_analyses", start, end) if window["start"] else db["isslop_analyses"].count(deleted_at=None)
    seo = 0
    if _table_exists("seo_metadata"):
        seo = _count_window("seo_metadata", start, end, "1=1") if window["start"] else db["seo_metadata"].count()
    issues = _count_window("issue_tickets", start, end) if _table_exists("issue_tickets") and window["start"] else (
        db["issue_tickets"].count() if _table_exists("issue_tickets") else 0
    )
    cards = [
        metric_card("deepsearch", "DeepSearch sessions", deepsearch),
        metric_card("isslop", "IsSlop analyses", isslop),
        metric_card("seo", "SEO metadata jobs", seo),
        metric_card("issues", "Issue tickets", issues),
    ]
    series = []
    if window["start"]:
        if _table_exists("deepsearch_sessions"):
            series.append(
                series_payload("deepsearch", "DeepSearch", _series_table("deepsearch_sessions", window), window)
            )
        if _table_exists("isslop_analyses"):
            series.append(
                series_payload("isslop", "IsSlop", _series_table("isslop_analyses", window), window)
            )
    tables = []
    if _table_exists("isslop_analyses"):
        isslop_live = _live_clause("isslop_analyses")
        grade_rows = db.query(
            f"SELECT grade, COUNT(*) AS c FROM isslop_analyses WHERE {isslop_live} "
            + ("AND created_at >= :start AND created_at < :end " if window["start"] else "")
            + "GROUP BY grade ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "isslop_grades",
                "IsSlop grades",
                ["Grade", "Count"],
                [[row["grade"], int(row["c"] or 0)] for row in grade_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def build_storage(hours: int, compare: bool, top_n: int) -> dict:
    window = window_config(hours)
    end = window["end"]
    start = window["start"] or "0000-01-01T00:00:00+00:00"
    uploads = _count_window("attachments", start, end) if _table_exists("attachments") and window["start"] else (
        db["attachments"].count(deleted_at=None) if _table_exists("attachments") else 0
    )
    bytes_total = 0
    if _table_exists("attachments"):
        attach_live = _live_clause("attachments")
        row = list(db.query(f"SELECT COALESCE(SUM(file_size), 0) AS s FROM attachments WHERE {attach_live}"))
        if row:
            bytes_total = int(row[0]["s"] or 0)
    project_files = db["project_files"].count(deleted_at=None) if _table_exists("project_files") else 0
    backup_bytes = 0
    if _table_exists("backups"):
        row = list(db.query("SELECT COALESCE(SUM(size_bytes), 0) AS s FROM backups WHERE status = 'completed'"))
        if row:
            backup_bytes = int(row[0]["s"] or 0)
    cards = [
        metric_card("uploads", "Uploads in window", uploads),
        metric_card("attachment_bytes", "Attachment bytes", bytes_total),
        metric_card("project_files", "Project files", project_files),
        metric_card("backup_bytes", "Backup bytes", backup_bytes),
    ]
    series = []
    if _table_exists("attachments") and window["start"]:
        series.append(series_payload("uploads", "Uploads", _series_table("attachments", window), window))
        attach_live = _live_clause("attachments")
        size_rows = db.query(
            f"SELECT {bucket_expr(window['granularity'])} AS bucket, "
            "COALESCE(SUM(file_size), 0) AS v FROM attachments "
            f"WHERE {attach_live} AND created_at >= :start AND created_at < :end "
            "GROUP BY bucket",
            start=start,
            end=end,
        )
        size_points = {row["bucket"]: float(row["v"]) for row in size_rows if row.get("bucket")}
        series.append(series_payload("bytes", "Bytes uploaded", size_points, window))
    tables = []
    if _table_exists("attachments"):
        attach_live = _live_clause("attachments")
        mime_rows = db.query(
            f"SELECT mime_type, COUNT(*) AS c FROM attachments WHERE {attach_live} "
            + ("AND created_at >= :start AND created_at < :end " if window["start"] else "")
            + "GROUP BY mime_type ORDER BY c DESC LIMIT :limit",
            **({"start": start, "end": end} if window["start"] else {}),
            limit=top_n,
        )
        tables.append(
            table_payload(
                "mime",
                "Upload MIME types",
                ["MIME", "Count"],
                [[row["mime_type"] or "(unknown)", int(row["c"] or 0)] for row in mime_rows],
            )
        )
    return {"cards": cards, "series": series, "tables": tables}


def _highlights() -> list[dict]:
    items: list[dict] = []
    if _table_exists("reactions"):
        reaction_live = _live_clause("reactions")
        row = list(
            db.query(
                f"SELECT emoji, COUNT(*) AS c FROM reactions WHERE {reaction_live} "
                "GROUP BY emoji ORDER BY c DESC LIMIT 1"
            )
        )
        if row:
            items.append({"label": "Top reaction emoji", "value": row[0]["emoji"]})
    if _table_exists("posts"):
        post_live = _live_clause("posts")
        row = list(
            db.query(
                f"SELECT strftime('%H', created_at) AS hour, COUNT(*) AS c FROM posts "
                f"WHERE {post_live} GROUP BY hour ORDER BY c DESC LIMIT 1"
            )
        )
        if row:
            items.append({"label": "Peak posting hour (UTC)", "value": f"{row[0]['hour']}:00"})
    if _table_exists("instances"):
        row = list(
            db.query(
                "SELECT name, slug, uid, restart_count FROM instances "
                "ORDER BY restart_count DESC LIMIT 1"
            )
        )
        if row and int(row[0]["restart_count"] or 0) > 0:
            label = _instance_label(row[0])
            items.append(
                {
                    "label": "Most restarted container",
                    "value": f"{label} ({row[0]['restart_count']} restarts)",
                }
            )
    if _table_exists("game_steals"):
        row = list(db.query("SELECT COUNT(*) AS c FROM game_steals"))
        if row:
            items.append({"label": "Total farm steals all-time", "value": int(row[0]["c"] or 0)})
    if _table_exists("user_customizations"):
        count = db["user_customizations"].count()
        if count:
            items.append({"label": "Users with custom CSS/JS", "value": count})
    return items