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