from __future__ import annotations

import math
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.forecast import forecast_next_n
from ..services.schema_discovery import discover_schema
from ..services.trend_engine import compute_trend_scores


router = APIRouter()


@router.get("/potential-winners")
def potential_winners(days: int = Query(14, ge=3, le=60), limit: int = Query(50, ge=1, le=200)):
    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.trend_candidates_sql)
    with engine.connect() as conn:
        df = pd.read_sql(q, conn, params={"since": since, "limit": limit * 5})
    if df.empty:
        return {"items": []}

    scored = compute_trend_scores(df)
    scored = scored.sort_values("potential_score", ascending=False).head(limit)
    return {"items": scored.to_dict(orient="records")}


@router.get("/forecast")
def forecast(
    product_id: str = Query(..., min_length=1),
    days: int = Query(30, ge=7, le=180),
    horizon: int = Query(14, ge=1, le=60),
):
    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, "forecast": []}

    df = df.sort_values("ds")
    y = df["units"].astype(float).fillna(0.0).values
    yhat = forecast_next_n(y, n=horizon)
    start = pd.to_datetime(df["ds"]).max()
    out = []
    for i, v in enumerate(yhat, start=1):
        out.append({"ds": (start + pd.Timedelta(days=i)).to_pydatetime().isoformat(), "units_hat": float(max(0.0, v))})
    return {"product_id": product_id, "forecast": out}