from fastapi import APIRouter, HTTPException, Query
from typing import Optional

from app.db import get_db
from app.api.models import CommodityMeta, ExchangeInfo

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


@router.get("/exchanges", response_model=list[ExchangeInfo])
def get_exchanges():
    with get_db() as conn:
        rows = conn.execute(
            """
            SELECT c.exchange_abbr, c.exchange,
                   COUNT(DISTINCT c.id) AS commodity_count
            FROM commodities c
            WHERE EXISTS (SELECT 1 FROM reports r WHERE r.commodity_id = c.id)
               OR EXISTS (SELECT 1 FROM disagg_reports dr WHERE dr.commodity_id = c.id)
            GROUP BY c.exchange_abbr
            ORDER BY commodity_count DESC
            """
        ).fetchall()
    return [ExchangeInfo(**dict(r)) for r in rows]


@router.get("/commodities", response_model=list[CommodityMeta])
def get_commodities(exchange: Optional[str] = Query(None)):
    sql = """
        SELECT c.cftc_code, c.name, c.exchange, c.exchange_abbr, c.contract_unit,
               COALESCE(MIN(r.report_date), MIN(dr.report_date)) AS first_date,
               COALESCE(MAX(r.report_date), MAX(dr.report_date)) AS last_date,
               COUNT(DISTINCT r.report_date) AS week_count,
               CASE WHEN COUNT(DISTINCT dr.report_date) > 0 THEN 1 ELSE 0 END AS has_disagg
        FROM commodities c
        LEFT JOIN reports r ON r.commodity_id = c.id
        LEFT JOIN disagg_reports dr ON dr.commodity_id = c.id
    """
    params = []
    if exchange:
        sql += " WHERE c.exchange_abbr = ?"
        params.append(exchange)
    sql += """
        GROUP BY c.id
        HAVING COUNT(DISTINCT r.report_date) > 0 OR COUNT(DISTINCT dr.report_date) > 0
        ORDER BY c.exchange_abbr, c.name
    """

    with get_db() as conn:
        rows = conn.execute(sql, params).fetchall()
    return [CommodityMeta(**dict(r)) for r in rows]


@router.get("/commodities/{cftc_code}", response_model=CommodityMeta)
def get_commodity(cftc_code: str):
    with get_db() as conn:
        row = conn.execute(
            """
            SELECT c.cftc_code, c.name, c.exchange, c.exchange_abbr, c.contract_unit,
                   COALESCE(MIN(r.report_date), MIN(dr.report_date)) AS first_date,
                   COALESCE(MAX(r.report_date), MAX(dr.report_date)) AS last_date,
                   COUNT(DISTINCT r.report_date) AS week_count,
                   CASE WHEN COUNT(DISTINCT dr.report_date) > 0 THEN 1 ELSE 0 END AS has_disagg
            FROM commodities c
            LEFT JOIN reports r ON r.commodity_id = c.id
            LEFT JOIN disagg_reports dr ON dr.commodity_id = c.id
            WHERE c.cftc_code = ?
            GROUP BY c.id
            """,
            (cftc_code,),
        ).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail=f"Commodity {cftc_code} not found")
    return CommodityMeta(**dict(row))