1from __future__ import annotations23from fastapi import APIRouter, Depends, Query4from findata_core.db import connection56from findata_api import auth as A78router = APIRouter(prefix="/instruments", tags=["instruments"])91011@router.get("")12def search(query: str = Query("", max_length=64), asset_class: str | None = None, status: str | None = None,13 limit: int = Query(50, le=500), cursor: str | None = None, p: A.Principal = Depends(A.require("read:market"))):14 q = query.strip()15 sql = "SELECT instrument_id, symbol, name, asset_class, status, first_date, last_date, currency FROM instruments WHERE true"16 params: list = []17 if q:18 sql += " AND (upper(symbol) LIKE %s OR lower(name) LIKE %s)"19 params += [q.upper() + "%", "%" + q.lower() + "%"]20 if asset_class:21 sql += " AND asset_class=%s"22 params.append(asset_class)23 if status:24 sql += " AND status=%s"25 params.append(status)26 if cursor:27 sql += " AND symbol > %s"28 params.append(cursor)29 sql += " ORDER BY (upper(symbol) = %s) DESC, symbol LIMIT %s"30 params += [q.upper(), limit + 1]31 with connection() as conn:32 rows = conn.execute(sql, params).fetchall()33 total = conn.execute("SELECT count(*) n, count(*) FILTER (WHERE status='active') active FROM instruments").fetchone()34 nxt = rows[limit]["symbol"] if len(rows) > limit else None35 return {"data": rows[:limit], "meta": {"total_instruments": total["n"], "active": total["active"]}, "next_cursor": nxt if not q else None}363738@router.get("/{symbol}")39def detail(symbol: str, p: A.Principal = Depends(A.require("read:market"))):40 sym = symbol.upper()41 with connection() as conn:42 inst = conn.execute("SELECT * FROM instruments WHERE symbol=%s ORDER BY asset_class LIMIT 1", (sym,)).fetchone()43 aliases = conn.execute("SELECT a.* FROM instrument_aliases a JOIN instruments i ON i.instrument_id=a.instrument_id WHERE a.symbol=%s OR i.symbol=%s", (sym, sym)).fetchall()44 actions = conn.execute("SELECT action_type, ex_date, value FROM corporate_actions WHERE instrument_id LIKE %s ORDER BY ex_date DESC LIMIT 200", (f"firstrate:%:{sym}",)).fetchall()45 cov = conn.execute("SELECT dataset_id, count(*) sessions, min(session_date) d0, max(session_date) d1, sum(bar_count) bars FROM coverage WHERE symbol=%s GROUP BY dataset_id", (sym,)).fetchall()46 fund = conn.execute("SELECT count(*) facts, max(fiscal_period_end) last_period, max(filing_date) last_filing FROM fundamentals_facts WHERE entity_id=%s AND superseded_at IS NULL", (f"frd:{sym}",)).fetchone()47 return {"instrument": inst, "aliases": aliases, "corporate_actions": actions, "coverage": cov, "fundamentals": fund}48