"""Read the live Supabase state and print a one-page health report.

Usage:
    python scripts/check_state.py

What it checks:
    * each per-type challenges table — row count per team
    * 1v1 tables — room / match / submission counts
    * certificates — issued count
    * user_completions — total + breakdown by category
"""

import os
import sys
from pathlib import Path

try:
    from dotenv import load_dotenv
except ImportError:
    print("Missing python-dotenv.")
    sys.exit(1)

import httpx


ROOT = Path(__file__).resolve().parent.parent
ENV_PATH = ROOT / ".env"


def _load_env() -> None:
    if ENV_PATH.exists():
        load_dotenv(ENV_PATH)


def _headers() -> dict:
    key = os.environ.get("SUPABASE_ANON_KEY", "")
    return {
        "apikey": key,
        "Authorization": f"Bearer {key}",
        "Content-Type": "application/json",
    }


def _count(client: httpx.Client, table: str, team: str | None = None) -> int:
    base = os.environ.get("SUPABASE_URL", "").rstrip("/")
    q = f"select=id"
    if team:
        q += f"&team_role=eq.{team}"
    r = client.get(f"{base}/rest/v1/{table}?{q}", headers=_headers())
    if r.status_code != 200:
        return -1
    return len(r.json())


def _count_per_team(client: httpx.Client, table: str) -> dict[str, int]:
    return {"red": _count(client, table, "red"), "blue": _count(client, table, "blue")}


def main() -> int:
    _load_env()
    base = os.environ.get("SUPABASE_URL", "").rstrip("/")
    if not base:
        sys.exit("SUPABASE_URL is not set.")

    print(f"Supabase: {base}\n")

    with httpx.Client(timeout=15) as client:
        print("=== Challenge pools ===")
        for table in [
            "encryption_challenges",
            "code_fixing_challenges",
            "log_analysis_challenges",
            "vulnerability_hunter_challenges",
        ]:
            counts = _count_per_team(client, table)
            print(f"  {table:40s} red={counts['red']:4d}  blue={counts['blue']:4d}")

        # Migration 011 consistency check: every row's `module` must
        # be the canonical challenge type for its table. The DB view
        # `v_challenge_type_consistency` returns one row per table
        # with `bad_rows = 0` when the contract is honoured.
        print("\n=== Migration 011 — challenge type consistency ===")
        try:
            r = client.get(
                f"{base}/rest/v1/v_challenge_type_consistency?select=table_name,bad_rows",
                headers={k: v for k, v in _headers().items() if k != "Content-Type"},
            )
            if r.status_code == 200:
                total_bad = 0
                for row in r.json():
                    bad = row.get("bad_rows", 0)
                    total_bad += bad
                    marker = "OK  " if bad == 0 else "FAIL"
                    print(f"  [{marker}] {row['table_name']:35s} bad_rows={bad}")
                if total_bad:
                    print(
                        "\n  >>> run CyberArena/db/schema/011_challenge_type_normalization.sql"
                    )
            else:
                print(f"  (view not accessible: HTTP {r.status_code})")
        except Exception as e:
            print(f"  (skip: {e})")

        print("\n=== 1v1 mode ===")
        for table in ["onevone_rooms", "onevone_matches", "onevone_submissions", "onevone_players"]:
            print(f"  {table:40s} total={_count(client, table):4d}")

        print("\n=== Certificates ===")
        print(f"  {'certificates':40s} total={_count(client, 'certificates'):4d}")

        print("\n=== User completions ===")
        print(f"  {'user_completions':40s} total={_count(client, 'user_completions'):4d}")
        # Per-category breakdown
        for cat in ["crypto", "code-fixing", "log-analysis", "vulnerability-hunter"]:
            r = client.get(
                f"{base}/rest/v1/user_completions?category=eq.{cat}&select=id",
                headers=_headers(),
            )
            if r.status_code == 200:
                print(f"    {cat:38s} {len(r.json()):4d}")

    return 0


if __name__ == "__main__":
    sys.exit(main())