1from __future__ import annotations23from datetime import date, timedelta45from fastapi import APIRouter, Depends, Query6from findata_core.db import connection78from findata_api import auth as A910router = APIRouter(prefix="/coverage", tags=["coverage"])111213@router.get("")14def coverage(dataset_id: str = "market_bars.stock.1m.raw", symbol: str | None = None, start: date | None = None, end: date | None = None,15 limit: int = Query(500, le=5000), p: A.Principal = Depends(A.require("read:market"))):16 with connection() as conn:17 rows = conn.execute("SELECT symbol, session_date, bar_count, expected_bars, first_ts, last_ts, flags FROM coverage WHERE dataset_id=%s "18 "AND (%s::text IS NULL OR symbol=%s) AND (%s::date IS NULL OR session_date>=%s) AND (%s::date IS NULL OR session_date<=%s) "19 "ORDER BY session_date DESC, symbol LIMIT %s", (dataset_id, symbol, symbol, start, start, end, end, limit)).fetchall()20 return {"data": rows, "meta": {"dataset_id": dataset_id}}212223@router.get("/heatmap")24def heatmap(dataset_id: str = "market_bars.stock.1m.raw", start: date | None = None, end: date | None = None,25 p: A.Principal = Depends(A.require("read:market"))):26 """Per session: symbols with data, total bars, mean regular-hours completeness (bars / expected, capped at 1)."""27 end = end or date.today()28 start = start or end - timedelta(days=120)29 with connection() as conn:30 rows = conn.execute(31 "SELECT session_date, count(*) symbols, sum(bar_count) bars, "32 "avg(LEAST(1.0, bar_count::float / NULLIF(expected_bars,0))) FILTER (WHERE expected_bars > 0) completeness, "33 "count(*) FILTER (WHERE 'non_trading_day' = ANY(flags)) non_trading_rows "34 "FROM coverage WHERE dataset_id=%s AND session_date BETWEEN %s AND %s GROUP BY 1 ORDER BY 1", (dataset_id, start, end)).fetchall()35 syms = conn.execute("SELECT symbol, count(*) sessions, sum(bar_count) bars, avg(LEAST(1.0, bar_count::float / NULLIF(expected_bars,0))) FILTER (WHERE expected_bars>0) completeness "36 "FROM coverage WHERE dataset_id=%s AND session_date BETWEEN %s AND %s GROUP BY 1 ORDER BY bars DESC LIMIT 50", (dataset_id, start, end)).fetchall()37 return {"sessions": rows, "top_symbols": syms, "meta": {"dataset_id": dataset_id, "start": str(start), "end": str(end)}}383940@router.get("/symbols")41def symbols(dataset_id: str = "market_bars.stock.1m.raw", min_sessions: int = 1, limit: int = Query(200, le=5000), cursor: str | None = None,42 p: A.Principal = Depends(A.require("read:market"))):43 with connection() as conn:44 rows = conn.execute("SELECT symbol, count(*) sessions, min(session_date) d0, max(session_date) d1, sum(bar_count) bars FROM coverage WHERE dataset_id=%s "45 "AND (%s::text IS NULL OR symbol > %s) GROUP BY symbol HAVING count(*) >= %s ORDER BY symbol LIMIT %s",46 (dataset_id, cursor, cursor, min_sessions, limit + 1)).fetchall()47 nxt = rows[limit]["symbol"] if len(rows) > limit else None48 return {"data": rows[:limit], "next_cursor": nxt}49