"""``roam stats`` — aggregate metrics over the index.

single overview number for "where am I". Counts files by
language, file role, and symbol kind; reports recent commit activity.
Useful as the first thing an agent runs after ``roam init`` to size
the codebase.

Output formats: text (default), ``--json``. SARIF is deliberately NOT
emitted because stats outputs are invocation-scoped index-rollup
summaries (file counts by language / role, symbol counts by kind,
recent commit activity) — not per-location code violations. The
output describes index size and shape, which is metadata about the
indexed workspace rather than a defect at a source coordinate. See
``cmd_graph_stats`` for the parallel topology-summary disclosure
pattern (W1224) + action.yml _SUPPORTED_SARIF allowlist +
W1175-RESEARCH propagation plan + W1224-audit memo.
"""

from __future__ import annotations

import sqlite3
import time

import click

from roam.capability import roam_capability
from roam.commands.resolve import ensure_index
from roam.db.connection import open_db
from roam.output.formatter import json_envelope, to_json


def _is_missing_git_commits_schema(exc: sqlite3.OperationalError) -> bool:
    """Return True for old/partial indexes without git commit metadata."""
    message = str(exc).lower()
    return ("no such table" in message and "git_commits" in message) or (
        "no such column" in message and "timestamp" in message
    )


def _count_git_commits(conn: sqlite3.Connection, sql: str, params: tuple[object, ...] = ()) -> int:
    try:
        row = conn.execute(sql, params).fetchone()
    except sqlite3.OperationalError as exc:
        if _is_missing_git_commits_schema(exc):
            return 0
        raise
    return int(row[0] if row is not None else 0)


@roam_capability(
    name="stats",
    category="getting-started",
    summary="Aggregate metrics: language, role, kind counts + recent activity",
    maturity="stable",
    mcp_expose=True,
    mcp_preset=("core",),
    side_effect=False,
    task_required=False,
    destructive=False,
    stale_sensitive=False,
    ai_safe=True,
    requires_index=True,
)
@click.command()
@click.option(
    "--days",
    type=int,
    default=30,
    show_default=True,
    help="Window for the recent-commit activity counter.",
)
@click.pass_context
def stats(ctx, days: int) -> None:
    """Aggregate metrics: language, role, kind counts + recent activity."""
    json_mode = ctx.obj.get("json") if ctx.obj else False
    ensure_index()

    with open_db(readonly=True) as conn:
        file_total = conn.execute("SELECT COUNT(*) FROM files").fetchone()[0]
        symbol_total = conn.execute("SELECT COUNT(*) FROM symbols").fetchone()[0]
        commits_total = _count_git_commits(conn, "SELECT COUNT(*) FROM git_commits")
        cutoff_ts = int(time.time()) - days * 86400
        commits_recent = _count_git_commits(
            conn,
            "SELECT COUNT(*) FROM git_commits WHERE timestamp >= ?",
            (cutoff_ts,),
        )

        languages = dict(
            conn.execute(
                "SELECT COALESCE(language, 'unknown') AS lang, COUNT(*) AS n FROM files GROUP BY lang ORDER BY n DESC"
            ).fetchall()
        )
        roles = dict(
            conn.execute(
                "SELECT COALESCE(file_role, 'unknown') AS role, COUNT(*) AS n FROM files GROUP BY role ORDER BY n DESC"
            ).fetchall()
        )
        kinds = dict(conn.execute("SELECT kind, COUNT(*) AS n FROM symbols GROUP BY kind ORDER BY n DESC").fetchall())

        loc_row = conn.execute("SELECT COALESCE(SUM(line_count), 0) AS lines FROM files").fetchone()
        line_total = int(loc_row["lines"] or 0)

    verdict = (
        f"{file_total} file(s) · {symbol_total} symbol(s) · {line_total:,} line(s) · "
        f"{commits_recent} commit(s) in the last {days} day(s)"
    )

    if json_mode:
        click.echo(
            to_json(
                json_envelope(
                    "stats",
                    summary={
                        "verdict": verdict,
                        "file_total": file_total,
                        "symbol_total": symbol_total,
                        "line_total": line_total,
                        "commits_total": commits_total,
                        "commits_recent": commits_recent,
                        "days": days,
                    },
                    by_language=languages,
                    by_role=roles,
                    by_kind=kinds,
                )
            )
        )
        return

    click.echo(f"VERDICT: {verdict}")
    click.echo()
    click.echo(f"Files          : {file_total:,}")
    click.echo(f"Symbols        : {symbol_total:,}")
    click.echo(f"Lines of code  : {line_total:,}")
    click.echo(f"Total commits  : {commits_total:,}")
    click.echo(f"Recent commits : {commits_recent} in {days} days")
    click.echo()
    click.echo("By language    " + " " * 7 + "Files")
    for lang, n in list(languages.items())[:10]:
        click.echo(f"  {str(lang):<20}  {n:>5}")
    click.echo()
    click.echo("By file role   " + " " * 7 + "Files")
    for role, n in roles.items():
        click.echo(f"  {role:<20}  {n:>5}")
    click.echo()
    click.echo("By symbol kind " + " " * 7 + "Symbols")
    for kind, n in list(kinds.items())[:10]:
        click.echo(f"  {kind:<20}  {n:>7}")
