"""/diff?a=&b=&scope= — what changed between two dates: new / gone entities, property, price and benchmark changes."""
from __future__ import annotations

from datetime import UTC, datetime
from datetime import time as dtime
from typing import Any

from fastapi import APIRouter, Query, Request

from aiatlas.api.common import (
    ENTITY_COLS,
    ENTITY_FROM,
    EVENT_COLS,
    EVENT_FROM,
    ApiError,
    cached,
    change_event,
    entity_summary,
    parse_date,
)
from aiatlas.db import connection, fetch_all, fetch_one, fetch_val

router = APIRouter(prefix="/api/v1/diff", tags=["diff"])
GONE_TYPES = ("RETIREMENT_ANNOUNCED", "DEPRECATION_ANNOUNCED", "STATUS_CHANGED", "ENTITY_MERGED", "MODEL_RETIRED", "MODEL_DEPRECATED")


async def _scope(conn: Any, scope: str) -> tuple[str, str, dict[str, Any], dict[str, Any]]:
    """Returns (entity where-fragment on alias e, event where-fragment on alias e, params, scope description)."""
    s = (scope or "all").strip()
    if s == "all":
        return "true", "true", {}, {"kind": "all"}
    if s == "models":
        return "e.entity_type = 'model'", "e.entity_type = 'model'", {}, {"kind": "models"}
    if s.startswith("org:"):
        key = s[4:]
        org = await fetch_one(conn, "select id, slug, canonical_name from entities where slug = :k or id = :k limit 1", k=key)
        if not org:
            raise ApiError(404, f"organization {key!r} not found")
        frag = "(e.organization_id = :org or e.id = :org)"
        return frag, frag, {"org": org["id"]}, {"kind": "org", "organization": {"id": org["id"], "slug": org["slug"], "name": org["canonical_name"]}}
    if s.startswith("family:"):
        frag = "e.attributes->>'family' ilike :family"
        return frag, frag, {"family": s[7:]}, {"kind": "family", "family": s[7:]}
    raise ApiError(400, "scope must be all | models | org:<slug> | family:<name>")


@router.get("")
@cached(300)
async def diff(request: Request, a: str = Query(..., description="YYYY-MM-DD"), b: str = Query(..., description="YYYY-MM-DD"), scope: str = "all",
               limit: int = Query(200, ge=1, le=1000)) -> dict[str, Any]:
    da, db = parse_date(a, "a"), parse_date(b, "b")
    assert da is not None and db is not None
    if da > db:
        da, db = db, da
    start, end = datetime.combine(da, dtime.max, UTC), datetime.combine(db, dtime.max, UTC)
    async with connection() as conn:
        ent_frag, ev_frag, params, scope_desc = await _scope(conn, scope)
        new_entities = await fetch_all(conn, f"select {ENTITY_COLS} from {ENTITY_FROM} where e.merged_into is null and {ent_frag} and e.first_seen_at > :s and e.first_seen_at <= :e "
                                             f"order by e.first_seen_at desc limit :lim", s=start, e=end, lim=limit, **params)
        gone = await fetch_all(conn, f"""select distinct on (e.id) {ENTITY_COLS} from change_events ev join entities e on e.id = ev.entity_id left join entities eo on eo.id = e.organization_id
                                         where {ev_frag} and ev.observed_at > :s and ev.observed_at <= :e and (ev.event_type = any(cast(:gt as text[])) or e.merged_into is not null)
                                         and (e.status in ('retired','deprecated','merged') or ev.event_type in ('RETIREMENT_ANNOUNCED','DEPRECATION_ANNOUNCED'))
                                         order by e.id limit :lim""", s=start, e=end, gt=list(GONE_TYPES), lim=limit, **params)

        async def events(cond: str) -> list[dict[str, Any]]:
            rows = await fetch_all(conn, f"select {EVENT_COLS} from {EVENT_FROM} where {ev_frag} and ev.observed_at > :s and ev.observed_at <= :e and {cond} "
                                         f"order by ev.importance desc, ev.observed_at desc limit :lim", s=start, e=end, lim=limit, **params)
            return [change_event(r) for r in rows]

        prop_changes = await events("ev.property is not null and ev.category not in ('price','benchmark') and ev.event_type <> 'DOCUMENT_CHANGED'")
        price_changes = await events("ev.category = 'price'")
        bench_changes = await events("ev.category = 'benchmark'")
        counts = await fetch_one(conn, f"""select (select count(*) from entities e where e.merged_into is null and {ent_frag} and e.first_seen_at > :s and e.first_seen_at <= :e) as new_entities,
                                           (select count(*) from change_events ev left join entities e on e.id = ev.entity_id where {ev_frag} and ev.observed_at > :s and ev.observed_at <= :e and ev.event_type <> 'DOCUMENT_CHANGED') as events,
                                           (select count(*) from change_events ev left join entities e on e.id = ev.entity_id where {ev_frag} and ev.observed_at > :s and ev.observed_at <= :e and ev.category = 'price') as price_changes,
                                           (select count(*) from change_events ev left join entities e on e.id = ev.entity_id where {ev_frag} and ev.observed_at > :s and ev.observed_at <= :e and ev.category = 'benchmark') as benchmark_changes,
                                           (select count(*) from prices p join entities e on e.id = p.model_id where {ent_frag} and p.valid_from > :s and p.valid_from <= :e) as price_rows_opened,
                                           (select count(*) from prices p join entities e on e.id = p.model_id where {ent_frag} and p.valid_to > :s and p.valid_to <= :e) as price_rows_closed,
                                           (select count(*) from claims c join entities e on e.id = c.entity_id where {ent_frag} and c.valid_to > :s and c.valid_to <= :e) as claims_superseded""",
                                 s=start, e=end, **params)
        entities_at_a = await fetch_val(conn, f"select count(*) from entities e where e.merged_into is null and {ent_frag} and e.first_seen_at <= :s", s=start, **params)
        entities_at_b = await fetch_val(conn, f"select count(*) from entities e where e.merged_into is null and {ent_frag} and e.first_seen_at <= :e", e=end, **params)
    return {"a": da.isoformat(), "b": db.isoformat(), "scope": scope_desc, "new_entities": [entity_summary(r) for r in new_entities], "gone_entities": [entity_summary(r) for r in gone],
            "property_changes": prop_changes, "price_changes": price_changes, "benchmark_changes": bench_changes,
            "counts": {**{k: int(v or 0) for k, v in (counts or {}).items()}, "gone_entities": len(gone), "entities_at_a": int(entities_at_a or 0), "entities_at_b": int(entities_at_b or 0)}}