from __future__ import annotations

from datetime import datetime, timedelta

import pandas as pd
from fastapi import APIRouter, HTTPException, Query
from sqlalchemy import text

from ..db import get_engine
from ..services.schema_discovery import discover_schema
from ..services.timeseries import normalize_timeseries


router = APIRouter()


@router.get("/overview")
def overview():
    """
    返回 BI 顶部卡片核心指标（尽量从现有表自动推断）。
    """
    engine = get_engine()
    schema = discover_schema(engine)
    if not schema.sales_table:
        raise HTTPException(status_code=422, detail="未发现可用销量/订单明细表（需要至少包含 product_id + 时间 + 数量/金额）")

    q = text(schema.overview_sql)
    with engine.connect() as conn:
        row = conn.execute(q).mappings().first()
    return {"schema": schema.model_dump(), "metrics": dict(row) if row else {}}


@router.get("/sales/timeseries")
def sales_timeseries(
    product_id: str = Query(..., min_length=1),
    days: int = Query(30, ge=1, le=365),
):
    engine = get_engine()
    schema = discover_schema(engine)
    if not schema.sales_table:
        raise HTTPException(status_code=422, detail="未发现可用销量/订单明细表")

    since = datetime.utcnow() - timedelta(days=days)
    q = text(schema.timeseries_sql)
    with engine.connect() as conn:
        df = pd.read_sql(
            q,
            conn,
            params={"product_id": product_id, "since": since},
        )
    if df.empty:
        return {"product_id": product_id, "points": []}

    points = normalize_timeseries(df, ts_col="ds", value_cols=["units", "gmv"])
    return {"product_id": product_id, "points": points}