"""``roam index-stats`` — index size, row counts, fragmentation.

surfaces the size of the on-disk index plus a SQLite
fragmentation heuristic (free_pages / page_count). When fragmentation
exceeds ~25%, suggest ``VACUUM`` (or ``roam reset`` for a full
rebuild). Useful when an index has grown after a long-lived dev
session and the user wonders if it's bloated.

Output formats: text (default), ``--json``. SARIF is deliberately NOT
emitted because index-stats outputs are invocation-scoped index-
artifact metadata (DB file size, row counts per table, SQLite
fragmentation percentage) — not per-location code violations in
user source. The output describes roam's own index artifact, which
has no source coordinates to populate SARIF ``locations[]``. See
``cmd_index`` / ``cmd_index_bundle`` for the parallel setup/state
disclosure pattern (W1224) + action.yml _SUPPORTED_SARIF allowlist +
W1175-RESEARCH propagation plan + W1224-audit memo.
"""

from __future__ import annotations

import os
import sqlite3

import click

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


def _humanize(bytes_: int) -> str:
    units = ["B", "KB", "MB", "GB"]
    f = float(bytes_)
    for u in units:
        if f < 1024 or u == units[-1]:
            return f"{f:.1f} {u}"
        f /= 1024
    return f"{f:.1f} {units[-1]}"


@roam_capability(
    name="index-stats",
    category="getting-started",
    summary="Report .roam index size, row counts, and fragmentation",
    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(name="index-stats")
@click.pass_context
def index_stats(ctx) -> None:
    """Report .roam index size, row counts, and fragmentation."""
    json_mode = ctx.obj.get("json") if ctx.obj else False
    ensure_index()
    db_path = get_db_path()
    try:
        size_bytes = os.path.getsize(db_path)
    except OSError:
        size_bytes = 0

    table_counts: dict[str, int] = {}
    with open_db(readonly=True) as conn:
        for table in (
            "files",
            "symbols",
            "edges",
            "git_commits",
            "git_file_changes",
            "graph_metrics",
            "symbol_metrics",
            "clusters",
        ):
            try:
                row = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()
                table_counts[table] = row[0] if row else 0
            except sqlite3.OperationalError as exc:
                if "no such table" not in str(exc).lower():
                    raise
                table_counts[table] = 0
        page_size = conn.execute("PRAGMA page_size").fetchone()[0]
        page_count = conn.execute("PRAGMA page_count").fetchone()[0]
        free_pages = conn.execute("PRAGMA freelist_count").fetchone()[0]

    frag_pct = (free_pages / page_count * 100.0) if page_count else 0.0
    threshold_mb = float(os.environ.get("ROAM_INDEX_SIZE_WARN_MB", "200"))
    size_mb = size_bytes / (1024 * 1024)

    bloat = frag_pct >= 25.0
    oversized = size_mb >= threshold_mb
    if bloat and oversized:
        verdict = f"{_humanize(size_bytes)} index, {frag_pct:.1f}% fragmented — run `roam reset` to rebuild compactly."
    elif bloat:
        verdict = f"{_humanize(size_bytes)} index, {frag_pct:.1f}% fragmented — VACUUM recommended."
    elif oversized:
        verdict = (
            f"{_humanize(size_bytes)} index exceeds {int(threshold_mb)} MB threshold; "
            "consider exclude patterns or `roam clean`."
        )
    else:
        verdict = f"OK — {_humanize(size_bytes)} index, {frag_pct:.1f}% fragmentation"

    if json_mode:
        click.echo(
            to_json(
                json_envelope(
                    "index-stats",
                    summary={
                        "verdict": verdict,
                        "size_bytes": size_bytes,
                        "fragmentation_pct": round(frag_pct, 2),
                        "size_warn_mb": threshold_mb,
                    },
                    db_path=str(db_path),
                    table_counts=table_counts,
                    page_size=page_size,
                    page_count=page_count,
                    free_pages=free_pages,
                )
            )
        )
        return

    click.echo(f"VERDICT: {verdict}")
    click.echo()
    click.echo(f"DB path:        {db_path}")
    click.echo(f"Size:           {_humanize(size_bytes)}")
    click.echo(f"Pages:          {page_count} ({free_pages} free, {frag_pct:.1f}%)")
    click.echo(f"Page size:      {page_size} bytes")
    click.echo()
    click.echo("Table          Rows")
    click.echo("-------------  ----------")
    for tbl, n in table_counts.items():
        click.echo(f"{tbl:<13}  {n:>10}")
