"""Operations commands: `catlas stats`, `catlas status`, `catlas backup`."""
from __future__ import annotations

import json
import os
import subprocess
import time
from datetime import UTC, datetime
from pathlib import Path

import typer
from rich.table import Table

from companyatlas.config import settings


def register(app: typer.Typer) -> None:
    app.command()(stats)
    app.command()(status)
    app.command()(backup)


def stats() -> None:
    """Dataset counters (companies, sensors, observations, changes, events, archive)."""
    from companyatlas.archive import store_stats
    from companyatlas.cli import out, run_async
    from companyatlas.db import connection, fetch_one

    async def go():  # type: ignore[no-untyped-def]
        async with connection() as conn:
            return await fetch_one(conn, """
                select (select count(*) from companies) as companies,
                       (select count(*) from companies where onboarding_status = 'active') as companies_active,
                       (select count(*) from sensors) as sensors,
                       (select count(*) from sensors where status = 'active') as sensors_active,
                       (select count(*) from observations) as observations,
                       (select count(*) from snapshots) as snapshots,
                       (select count(*) from changes) as changes,
                       (select count(*) from changes where kind in ('meaningful','major','critical')) as meaningful_changes,
                       (select count(*) from events where status = 'active') as events,
                       (select count(*) from jobs where status = 'open') as jobs_open,
                       (select count(*) from observations where fetched_at > now() - interval '24 hours') as observations_24h,
                       (select count(*) from events where detected_at > now() - interval '24 hours' and status = 'active') as events_24h,
                       (select value from settings_kv where key = 'dataset_started_at') as dataset_started_at
            """)

    row = run_async(go()) or {}
    row["archive"] = store_stats()
    started = row.get("dataset_started_at")
    if started:
        try:
            dt = datetime.fromisoformat(str(started).strip('"'))
            row["dataset_age_days"] = (datetime.now(UTC) - dt).days
        except ValueError:
            pass
    out.print(json.dumps(row, default=str, indent=1))


def status() -> None:
    """Scheduler heartbeat, queue depth, failures by class (last 24 h), periodic task health."""
    from companyatlas.cli import out, run_async
    from companyatlas.db import connection, fetch_all, fetch_one

    async def go():  # type: ignore[no-untyped-def]
        async with connection() as conn:
            hb = await fetch_one(conn, "select value, updated_at from settings_kv where key = 'scheduler:heartbeat'")
            due = await fetch_one(conn, """select count(*) filter (where next_run_at <= now()) as due, count(*) as total,
                                                 count(*) filter (where status = 'failing') as failing, count(*) filter (where status = 'stale') as stale,
                                                 count(*) filter (where status = 'blocked') as blocked, count(*) filter (where status = 'retired') as retired
                                          from sensors where status <> 'retired' or status = 'retired'""")
            queue = await fetch_all(conn, "select kind, status, count(*) as n from queue_jobs group by 1, 2 order by 1, 2")
            fails = await fetch_all(conn, "select failure_class, count(*) as n from failures where at > now() - interval '24 hours' group by 1 order by 2 desc")
            onboarding = await fetch_all(conn, "select onboarding_status, count(*) as n from companies group by 1 order by 2 desc")
            return hb, due, queue, fails, onboarding

    hb, due, queue, fails, onboarding = run_async(go())
    if hb:
        age = (datetime.now(UTC) - hb["updated_at"]).total_seconds() if hb.get("updated_at") else None
        out.print(f"[bold]scheduler[/] heartbeat {int(age)}s ago" if age is not None else "[bold]scheduler[/] heartbeat unknown")
        val = hb["value"] if isinstance(hb["value"], dict) else json.loads(hb["value"])
        out.print(f"  worker={val.get('worker')} inflight={val.get('inflight')} due={val.get('due')} tick_ms={val.get('tick_ms')}")
        for t in val.get("tasks") or []:
            out.print(f"  task {t.get('name'):<22} runs={t.get('runs')} failures={t.get('failures')} err={t.get('last_error') or '-'}")
    else:
        out.print("[yellow]scheduler[/] no heartbeat yet")
    out.print(f"[bold]sensors[/] {due}")
    t = Table(title="onboarding")
    t.add_column("status")
    t.add_column("n", justify="right")
    for r in onboarding:
        t.add_row(str(r["onboarding_status"]), str(r["n"]))
    out.print(t)
    t = Table(title="queue")
    for c in ("kind", "status", "n"):
        t.add_column(c)
    for r in queue:
        t.add_row(r["kind"], r["status"], str(r["n"]))
    out.print(t)
    t = Table(title="failures 24h")
    t.add_column("class")
    t.add_column("n", justify="right")
    for r in fails:
        t.add_row(str(r["failure_class"]), str(r["n"]))
    out.print(t)


def backup(keep: int = typer.Option(14, help="number of dumps to keep")) -> None:
    """pg_dump (custom format, compressed) into CA_DATA_DIR/backups; prunes old dumps. Copy off-node with scripts/backup-offnode.sh."""
    from companyatlas.cli import out

    settings.ensure_dirs()
    ts = time.strftime("%Y%m%d-%H%M%S")
    target = settings.backups_dir / f"companyatlas-{ts}.dump"
    env = dict(os.environ)
    cmd = ["pg_dump", "--format=custom", "--compress=zstd:6", "--no-owner", "--dbname", settings.sync_database_url, "--file", str(target)]
    try:
        subprocess.run(cmd, check=True, env=env, timeout=3600)
    except FileNotFoundError:
        cmd[0] = "/opt/homebrew/opt/postgresql@17/bin/pg_dump"
        subprocess.run(cmd, check=True, env=env, timeout=3600)
    dumps = sorted(Path(settings.backups_dir).glob("companyatlas-*.dump"))
    for old in dumps[:-keep]:
        old.unlink(missing_ok=True)
    out.print(f"[green]backup ok[/] {target} ({target.stat().st_size // 1024} kB), kept {min(len(dumps), keep)}")


try:  # register the nightly backup as a periodic task when the scheduler imports this module
    from companyatlas.services.periodic import periodic

    @periodic("backup-nightly", cron=settings.backup_cron)
    async def _backup_task() -> None:
        import asyncio

        await asyncio.to_thread(backup, 14)
except Exception:  # noqa: BLE001, S110 — optional: scheduler not present in this process
    pass