from __future__ import annotations

from datetime import UTC, datetime

from fastapi import APIRouter, Depends, Query
from findata_core.db import connection

from findata_api import auth as A
from findata_api.errors import ApiError

router = APIRouter(prefix="/fundamentals", tags=["fundamentals"])


def _entity(e: str) -> str:
    e = e.strip()
    if ":" in e:
        return e
    return f"frd:{e.upper()}"


@router.get("/{entity_id}")
def facts(entity_id: str, as_of: datetime | None = Query(None, description="PIT instant (UTC). Default: now."),
          metrics: str | None = Query(None, description="comma-separated metric names"),
          period_type: str | None = Query(None, pattern="^(q|a)$"),
          include_superseded: bool = False, limit: int = Query(2000, le=20000),
          p: A.Principal = Depends(A.require("read:fundamentals"))):
    """Point-in-time facts: a fact is visible iff available_at <= as_of; among revisions, revision 1 (first observed
    value, proxy for 'as originally filed') is used unless a later revision was *observed* before as_of."""
    ent = _entity(entity_id)
    asof = as_of or datetime.now(UTC)
    if asof.tzinfo is None:
        asof = asof.replace(tzinfo=UTC)
    mlist = [m.strip() for m in metrics.split(",")] if metrics else None
    sql = """
    WITH visible AS (
      SELECT *, row_number() OVER (PARTITION BY metric, fiscal_period_end, fiscal_period_type, source_id
                                   ORDER BY revision_id DESC) rn
      FROM fundamentals_facts
      WHERE entity_id=%(ent)s AND available_at <= %(asof)s
        AND (revision_id = 1 OR observed_at <= %(asof)s)
        AND (%(pt)s::text IS NULL OR fiscal_period_type=%(pt)s)
        AND (%(ms)s::text[] IS NULL OR metric = ANY(%(ms)s))
    )
    SELECT entity_id, security_id, metric, value, unit, currency, fiscal_period_start, fiscal_period_end, fiscal_period_type,
           fiscal_year, fiscal_period, filing_date, published_at, available_at, observed_at, source_document, source_id,
           revision_id, restatement_flag, superseded_at
    FROM visible WHERE rn = 1 ORDER BY fiscal_period_end DESC, metric LIMIT %(lim)s"""
    with connection() as conn:
        rows = conn.execute(sql, {"ent": ent, "asof": asof, "pt": period_type, "ms": mlist, "lim": limit}).fetchall()
        e = conn.execute("SELECT * FROM entities WHERE entity_id=%s", (ent,)).fetchone()
        hidden = conn.execute("SELECT count(*) FROM fundamentals_facts WHERE entity_id=%s AND available_at > %s", (ent, asof)).fetchone()["count"]
    if not e and not rows:
        raise ApiError(404, "not_found", f"no fundamentals for {ent}; ingest with `findata ingest fundamentals {ent.split(':')[-1]}`")
    return {"data": rows, "entity": e, "meta": {"as_of": asof.isoformat(), "rows": len(rows), "facts_hidden_by_as_of": hidden,
            "pit_policy": "available_at = filing_date + 1 day 00:00 America/New_York; revisions observed after as_of are not shown",
            "license": "frd-private"}}


@router.get("/{entity_id}/periods")
def periods(entity_id: str, p: A.Principal = Depends(A.require("read:fundamentals"))):
    ent = _entity(entity_id)
    with connection() as conn:
        rows = conn.execute("SELECT fiscal_period_end, fiscal_period_type, fiscal_year, fiscal_period, filing_date, min(available_at) available_at, "
                            "count(*) metrics, max(revision_id) max_revision, bool_or(restatement_flag) restated FROM fundamentals_facts WHERE entity_id=%s "
                            "GROUP BY 1,2,3,4,5 ORDER BY 1 DESC", (ent,)).fetchall()
        earn = conn.execute("SELECT event_date, eps_estimate, eps_reported, release_time FROM earnings_events WHERE entity_id=%s ORDER BY event_date DESC LIMIT 60", (ent,)).fetchall()
    return {"periods": rows, "earnings": earn}