from __future__ import annotations

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

from findata_api import auth as A

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


@router.get("")
def search(query: str = Query("", max_length=64), asset_class: str | None = None, status: str | None = None,
           limit: int = Query(50, le=500), cursor: str | None = None, p: A.Principal = Depends(A.require("read:market"))):
    q = query.strip()
    sql = "SELECT instrument_id, symbol, name, asset_class, status, first_date, last_date, currency FROM instruments WHERE true"
    params: list = []
    if q:
        sql += " AND (upper(symbol) LIKE %s OR lower(name) LIKE %s)"
        params += [q.upper() + "%", "%" + q.lower() + "%"]
    if asset_class:
        sql += " AND asset_class=%s"
        params.append(asset_class)
    if status:
        sql += " AND status=%s"
        params.append(status)
    if cursor:
        sql += " AND symbol > %s"
        params.append(cursor)
    sql += " ORDER BY (upper(symbol) = %s) DESC, symbol LIMIT %s"
    params += [q.upper(), limit + 1]
    with connection() as conn:
        rows = conn.execute(sql, params).fetchall()
        total = conn.execute("SELECT count(*) n, count(*) FILTER (WHERE status='active') active FROM instruments").fetchone()
    nxt = rows[limit]["symbol"] if len(rows) > limit else None
    return {"data": rows[:limit], "meta": {"total_instruments": total["n"], "active": total["active"]}, "next_cursor": nxt if not q else None}


@router.get("/{symbol}")
def detail(symbol: str, p: A.Principal = Depends(A.require("read:market"))):
    sym = symbol.upper()
    with connection() as conn:
        inst = conn.execute("SELECT * FROM instruments WHERE symbol=%s ORDER BY asset_class LIMIT 1", (sym,)).fetchone()
        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()
        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()
        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()
        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()
    return {"instrument": inst, "aliases": aliases, "corporate_actions": actions, "coverage": cov, "fundamentals": fund}