"""Live counters and aggregate statistics — always computed from the database, never hardcoded."""
from __future__ import annotations

from typing import Any

from sqlalchemy.ext.asyncio import AsyncConnection

from aiatlas.db import execute, fetch_all, fetch_one, jsonb, transaction
from aiatlas.sdk.archive import archive_size


async def live_counts(conn: AsyncConnection) -> dict[str, Any]:
    by_type = await fetch_all(conn, "select entity_type, count(*) as n from entities where merged_into is null group by 1")
    counts = {r["entity_type"]: int(r["n"]) for r in by_type}
    infra = await fetch_one(conn, """select (select count(*) from sources where enabled) as sources,
                                            (select count(*) from connectors) as connectors,
                                            (select count(*) from connectors where enabled) as connectors_enabled,
                                            (select count(*) from documents) as documents,
                                            (select count(*) from snapshots) as snapshots,
                                            (select count(*) from claims) as claims,
                                            (select count(*) from claims where status = 'current') as claims_current,
                                            (select count(*) from relations where valid_to is null) as relations,
                                            (select count(*) from change_events) as change_events,
                                            (select count(*) from change_events where observed_at > now() - interval '24 hours') as change_events_24h,
                                            (select count(*) from change_events where observed_at > now() - interval '7 days') as change_events_7d,
                                            (select count(*) from benchmark_results where valid_to is null) as benchmark_results,
                                            (select count(*) from prices where valid_to is null) as prices_current,
                                            (select count(*) from prices) as prices_total,
                                            (select count(*) from jobs where status = 'queued') as jobs_queued,
                                            (select count(*) from jobs where status = 'dead') as jobs_dead,
                                            (select count(*) from review_queue where status = 'pending') as review_pending,
                                            (select count(*) from llm_jobs) as llm_jobs,
                                            (select coalesce(sum(input_tokens),0) + coalesce(sum(output_tokens),0) from llm_jobs) as llm_tokens,
                                            (select max(observed_at) from snapshots) as last_snapshot_at,
                                            (select max(observed_at) from change_events) as last_event_at,
                                            (select min(first_seen_at) from entities) as first_entity_at""")
    return {"entities": counts, "entities_total": sum(counts.values()), **{k: (int(v) if isinstance(v, int) else v) for k, v in (infra or {}).items()}}


async def compute_stats() -> dict[str, Any]:
    async with transaction() as conn:
        counts = await live_counts(conn)
        counts["archive"] = archive_size()
        await execute(conn, "insert into stats_snapshots (counts) values (cast(:c as jsonb))", c=jsonb(counts))
    return counts


async def history(conn: AsyncConnection, days: int = 90) -> list[dict[str, Any]]:
    return await fetch_all(conn, """select distinct on (date_trunc('day', computed_at)) date_trunc('day', computed_at) as day, counts
                                    from stats_snapshots where computed_at > now() - make_interval(days => :d) order by 1 desc, computed_at desc""", d=days)


__all__ = ["compute_stats", "history", "live_counts"]