"""Anonymous overview metrics for the public landing page. No data rows, no paths, no secrets — only catalog-level
figures (what exists, how much, how fresh). Everything else stays behind authentication (FirstRate license:
raw bars/tables are private)."""
from __future__ import annotations

import threading
import time
from datetime import UTC, datetime
from typing import Any

from fastapi import APIRouter
from findata_core.db import connection

from findata_api.health import cached_evaluate

router = APIRouter(prefix="/public", tags=["public"])
_cache: dict[str, Any] = {"at": 0.0, "value": None}
_lock = threading.Lock()


def _compute() -> dict[str, Any]:
    with connection() as conn:
        totals = conn.execute("SELECT count(*) files, coalesce(sum(row_count),0) rows, coalesce(sum(bytes),0) bytes FROM lake_files WHERE version_removed IS NULL").fetchone()
        fam = conn.execute("""
            SELECT split_part(dataset_id,'.',1)||'.'||split_part(dataset_id,'.',2) AS family, count(DISTINCT dataset_id) datasets,
                   sum(row_count) rows, sum(bytes) bytes
            FROM lake_files WHERE version_removed IS NULL GROUP BY 1 ORDER BY sum(bytes) DESC""").fetchall()
        ds = conn.execute("""
            SELECT d.id, d.name, d.kind, d.asset_class, d.interval, d.adjustment, d.status, d.license_class, d.current_version,
                   v.row_count, v.bytes, v.file_count, v.created_at AS version_created_at
            FROM datasets d LEFT JOIN dataset_versions v ON v.dataset_id=d.id AND v.version=d.current_version ORDER BY d.id""").fetchall()
        cov = conn.execute("""
            SELECT dataset_id, count(DISTINCT symbol) symbols, min(session_date) first_session, max(session_date) last_session, sum(bar_count) bars
            FROM coverage WHERE dataset_id IN ('market_bars.stock.1m.raw','market_bars.etf.1m.raw','market_bars.index.1m.raw') GROUP BY 1""").fetchall()
        opt = conn.execute("SELECT count(DISTINCT symbol_min) underlyings, min(date_min) first_day, max(date_max) last_day, sum(row_count) rows FROM lake_files WHERE dataset_id='options.eod' AND version_removed IS NULL").fetchone()
        fund = conn.execute("SELECT count(DISTINCT entity_id) entities, count(*) facts, min(fiscal_period_end) first_period, max(fiscal_period_end) last_period FROM fundamentals_facts WHERE superseded_at IS NULL").fetchone()
        earn = conn.execute("SELECT count(*) n FROM earnings_events").fetchone()
        inst = conn.execute("SELECT count(*) n, count(*) FILTER (WHERE status='active') active, count(*) FILTER (WHERE status='delisted') delisted FROM instruments").fetchone()
        raw = conn.execute("SELECT count(*) n, coalesce(sum(bytes),0) bytes FROM raw_archives WHERE path IS NOT NULL").fetchone()
        jobs = conn.execute("SELECT status, count(*) n FROM ingest_jobs WHERE created_at > now() - interval '24 hours' GROUP BY 1").fetchall()
        lastjob = conn.execute("SELECT max(finished_at) t FROM ingest_jobs WHERE status='succeeded'").fetchone()
        ck = conn.execute("SELECT value FROM checkpoints WHERE scope='dataset:market_bars.stock.1m.raw' AND key='last_vendor_update:day'").fetchone()
        versions = conn.execute("SELECT count(*) n FROM dataset_versions").fetchone()
        backup = conn.execute("SELECT updated_at, value->>'verified' AS verified FROM settings_kv WHERE key='last_backup'").fetchone()
    h = cached_evaluate()
    lake = h.get("components", {}).get("lake", {}) if isinstance(h, dict) else {}
    return {
        "generated_at": datetime.now(UTC).isoformat(),
        "status": h.get("status"),
        "totals": {"files": int(totals["files"]), "rows": int(totals["rows"]), "bytes": int(totals["bytes"]),
                   "datasets_active": sum(1 for d in ds if d["status"] == "active"), "dataset_versions": int(versions["n"]),
                   "raw_archives": int(raw["n"]), "raw_bytes": int(raw["bytes"])},
        "families": [{"family": f["family"], "datasets": f["datasets"], "rows": int(f["rows"]), "bytes": int(f["bytes"])} for f in fam],
        "datasets": [{k: (v.isoformat() if hasattr(v, "isoformat") else v) for k, v in dict(d).items()} for d in ds],
        "coverage": [{k: (v.isoformat() if hasattr(v, "isoformat") else v) for k, v in dict(c).items()} for c in cov],
        "options": {k: (v.isoformat() if hasattr(v, "isoformat") else v) for k, v in dict(opt).items()},
        "fundamentals": {**{k: (v.isoformat() if hasattr(v, "isoformat") else v) for k, v in dict(fund).items()}, "earnings_events": int(earn["n"])},
        "instruments": dict(inst),
        "freshness": {"vendor_last_update": (ck["value"] or {}).get("vendor_last_update") if ck else None,
                      "last_successful_job": lastjob["t"].isoformat() if lastjob and lastjob["t"] else None,
                      "jobs_24h": {j["status"]: j["n"] for j in jobs}},
        "storage": {"hot_free_gb": lake.get("free_gb"), "cold_free_gb": lake.get("cold_free_gb"), "curated_gb": lake.get("curated_gb"),
                    "raw_gb": lake.get("raw_gb")},
        "backup": {"last": backup["updated_at"].isoformat() if backup else None, "verified": (backup["verified"] == "true") if backup else None},
        "license": "FirstRate Data — private research use; raw bars and tables are not redistributed. Sign in for data access.",
    }


_started = False


def _refresh_loop(period_s: float) -> None:
    while True:
        try:
            v = _compute()
            _cache.update(at=time.monotonic(), value=v)
        except Exception as e:
            _cache.setdefault("error", str(e)[:200])
        time.sleep(period_s)


def start_refresher(period_s: float = 300.0) -> None:
    global _started
    if _started:
        return
    _started = True
    threading.Thread(target=_refresh_loop, args=(period_s,), name="public-overview-refresher", daemon=True).start()


@router.get("/overview")
def overview():
    """Served from a cache refreshed in the background every 5 minutes; the request path never runs the queries."""
    start_refresher()
    v = _cache["value"]
    if v is None:
        return {"generated_at": datetime.now(UTC).isoformat(), "status": "starting", "totals": None, "families": [], "datasets": [],
                "coverage": [], "options": {}, "fundamentals": {}, "instruments": {}, "freshness": {"jobs_24h": {}}, "storage": {},
                "backup": {}, "license": "FirstRate Data — private research use; raw bars and tables are not redistributed. Sign in for data access."}
    return v