from __future__ import annotations

from datetime import date, timedelta

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

from findata_api import auth as A

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


@router.get("")
def coverage(dataset_id: str = "market_bars.stock.1m.raw", symbol: str | None = None, start: date | None = None, end: date | None = None,
             limit: int = Query(500, le=5000), p: A.Principal = Depends(A.require("read:market"))):
    with connection() as conn:
        rows = conn.execute("SELECT symbol, session_date, bar_count, expected_bars, first_ts, last_ts, flags FROM coverage WHERE dataset_id=%s "
                            "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) "
                            "ORDER BY session_date DESC, symbol LIMIT %s", (dataset_id, symbol, symbol, start, start, end, end, limit)).fetchall()
    return {"data": rows, "meta": {"dataset_id": dataset_id}}


@router.get("/heatmap")
def heatmap(dataset_id: str = "market_bars.stock.1m.raw", start: date | None = None, end: date | None = None,
            p: A.Principal = Depends(A.require("read:market"))):
    """Per session: symbols with data, total bars, mean regular-hours completeness (bars / expected, capped at 1)."""
    end = end or date.today()
    start = start or end - timedelta(days=120)
    with connection() as conn:
        rows = conn.execute(
            "SELECT session_date, count(*) symbols, sum(bar_count) bars, "
            "avg(LEAST(1.0, bar_count::float / NULLIF(expected_bars,0))) FILTER (WHERE expected_bars > 0) completeness, "
            "count(*) FILTER (WHERE 'non_trading_day' = ANY(flags)) non_trading_rows "
            "FROM coverage WHERE dataset_id=%s AND session_date BETWEEN %s AND %s GROUP BY 1 ORDER BY 1", (dataset_id, start, end)).fetchall()
        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 "
                            "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()
    return {"sessions": rows, "top_symbols": syms, "meta": {"dataset_id": dataset_id, "start": str(start), "end": str(end)}}


@router.get("/symbols")
def symbols(dataset_id: str = "market_bars.stock.1m.raw", min_sessions: int = 1, limit: int = Query(200, le=5000), cursor: str | None = None,
            p: A.Principal = Depends(A.require("read:market"))):
    with connection() as conn:
        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 "
                            "AND (%s::text IS NULL OR symbol > %s) GROUP BY symbol HAVING count(*) >= %s ORDER BY symbol LIMIT %s",
                            (dataset_id, cursor, cursor, min_sessions, limit + 1)).fetchall()
    nxt = rows[limit]["symbol"] if len(rows) > limit else None
    return {"data": rows[:limit], "next_cursor": nxt}