1from __future__ import annotations23from datetime import UTC, datetime45from fastapi import APIRouter, Depends, Query6from findata_core.db import connection78from findata_api import auth as A9from findata_api.errors import ApiError1011router = APIRouter(prefix="/fundamentals", tags=["fundamentals"])121314def _entity(e: str) -> str:15 e = e.strip()16 if ":" in e:17 return e18 return f"frd:{e.upper()}"192021@router.get("/{entity_id}")22def facts(entity_id: str, as_of: datetime | None = Query(None, description="PIT instant (UTC). Default: now."),23 metrics: str | None = Query(None, description="comma-separated metric names"),24 period_type: str | None = Query(None, pattern="^(q|a)$"),25 include_superseded: bool = False, limit: int = Query(2000, le=20000),26 p: A.Principal = Depends(A.require("read:fundamentals"))):27 """Point-in-time facts: a fact is visible iff available_at <= as_of; among revisions, revision 1 (first observed28 value, proxy for 'as originally filed') is used unless a later revision was *observed* before as_of."""29 ent = _entity(entity_id)30 asof = as_of or datetime.now(UTC)31 if asof.tzinfo is None:32 asof = asof.replace(tzinfo=UTC)33 mlist = [m.strip() for m in metrics.split(",")] if metrics else None34 sql = """35 WITH visible AS (36 SELECT *, row_number() OVER (PARTITION BY metric, fiscal_period_end, fiscal_period_type, source_id37 ORDER BY revision_id DESC) rn38 FROM fundamentals_facts39 WHERE entity_id=%(ent)s AND available_at <= %(asof)s40 AND (revision_id = 1 OR observed_at <= %(asof)s)41 AND (%(pt)s::text IS NULL OR fiscal_period_type=%(pt)s)42 AND (%(ms)s::text[] IS NULL OR metric = ANY(%(ms)s))43 )44 SELECT entity_id, security_id, metric, value, unit, currency, fiscal_period_start, fiscal_period_end, fiscal_period_type,45 fiscal_year, fiscal_period, filing_date, published_at, available_at, observed_at, source_document, source_id,46 revision_id, restatement_flag, superseded_at47 FROM visible WHERE rn = 1 ORDER BY fiscal_period_end DESC, metric LIMIT %(lim)s"""48 with connection() as conn:49 rows = conn.execute(sql, {"ent": ent, "asof": asof, "pt": period_type, "ms": mlist, "lim": limit}).fetchall()50 e = conn.execute("SELECT * FROM entities WHERE entity_id=%s", (ent,)).fetchone()51 hidden = conn.execute("SELECT count(*) FROM fundamentals_facts WHERE entity_id=%s AND available_at > %s", (ent, asof)).fetchone()["count"]52 if not e and not rows:53 raise ApiError(404, "not_found", f"no fundamentals for {ent}; ingest with `findata ingest fundamentals {ent.split(':')[-1]}`")54 return {"data": rows, "entity": e, "meta": {"as_of": asof.isoformat(), "rows": len(rows), "facts_hidden_by_as_of": hidden,55 "pit_policy": "available_at = filing_date + 1 day 00:00 America/New_York; revisions observed after as_of are not shown",56 "license": "frd-private"}}575859@router.get("/{entity_id}/periods")60def periods(entity_id: str, p: A.Principal = Depends(A.require("read:fundamentals"))):61 ent = _entity(entity_id)62 with connection() as conn:63 rows = conn.execute("SELECT fiscal_period_end, fiscal_period_type, fiscal_year, fiscal_period, filing_date, min(available_at) available_at, "64 "count(*) metrics, max(revision_id) max_revision, bool_or(restatement_flag) restated FROM fundamentals_facts WHERE entity_id=%s "65 "GROUP BY 1,2,3,4,5 ORDER BY 1 DESC", (ent,)).fetchall()66 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()67 return {"periods": rows, "earnings": earn}68