# -----------------------------------------------------------------------------
# Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# web.py : API FastAPI (JSON) + service du frontend React (frontend/dist)
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import threading
from pathlib import Path

from fastapi import BackgroundTasks, FastAPI, HTTPException, Query
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import FileResponse
from fastapi.staticfiles import StaticFiles

from . import db, ingest

ROOT = Path(__file__).resolve().parent.parent
SOURCES_PATH = ROOT / "data" / "sources.json"
FRONTEND_DIST = ROOT / "frontend" / "dist"

app = FastAPI(title="Auto-Ka API", version="1.0",
              description="Agrégateur de voitures usagées — province de Québec")
app.add_middleware(CORSMiddleware, allow_origins=["*"],
                   allow_methods=["*"], allow_headers=["*"])

_sync_lock = threading.Lock()


def _row_to_dict(row) -> dict:
    d = dict(row)
    d["features"] = json.loads(d.get("features") or "[]")
    d["images"] = json.loads(d.get("images") or "[]")
    d["details"] = json.loads(d.get("details") or "{}")
    return d


def _apply_filters(sql: str, args: list, *, make=None, model=None, body_type=None,
                   fuel=None, transmission=None, drivetrain=None, region=None,
                   city=None, source=None, year_min=None, year_max=None,
                   price_min=None, price_max=None, km_max=None, q=None) -> str:
    if make:
        sql += " AND make=?"; args.append(make)
    if model:
        sql += " AND model LIKE ?"; args.append(f"{model}%")
    if body_type:
        sql += " AND body_type=?"; args.append(body_type)
    if fuel:
        if fuel == "Hybride":     # inclut l'hybride rechargeable
            sql += " AND fuel IN ('Hybride','Hybride rechargeable')"
        else:
            sql += " AND fuel=?"; args.append(fuel)
    if transmission:
        sql += " AND transmission=?"; args.append(transmission)
    if drivetrain:
        sql += " AND drivetrain=?"; args.append(drivetrain)
    if region:
        sql += " AND region=?"; args.append(region)
    if city:
        sql += " AND city LIKE ?"; args.append(f"%{city}%")
    if source:
        sql += " AND source=?"; args.append(source)
    if year_min is not None:
        sql += " AND year IS NOT NULL AND year>=?"; args.append(year_min)
    if year_max is not None:
        sql += " AND year IS NOT NULL AND year<=?"; args.append(year_max)
    if price_min is not None:
        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)
    if price_max is not None:
        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)
    if km_max is not None:
        sql += " AND mileage_km IS NOT NULL AND mileage_km<=?"; args.append(km_max)
    if q:
        sql += (" AND (title LIKE ? OR make LIKE ? OR model LIKE ?"
                " OR dealer_name LIKE ? OR city LIKE ?)")
        args += [f"%{q}%"] * 5
    return sql


_SORTS = {
    "price_asc": "price IS NULL, price ASC",
    "price_desc": "price IS NULL, price DESC",
    "km_asc": "mileage_km IS NULL, mileage_km ASC",
    "year_desc": "year IS NULL, year DESC",
    "recent": "first_seen DESC",
}


@app.get("/api/vehicles")
def list_vehicles(
    make: str | None = None,
    model: str | None = None,
    body_type: str | None = None,
    fuel: str | None = None,
    transmission: str | None = None,
    drivetrain: str | None = None,
    region: str | None = None,
    city: str | None = None,
    source: str | None = None,
    year_min: int | None = None,
    year_max: int | None = None,
    price_min: float | None = None,
    price_max: float | None = None,
    km_max: float | None = None,
    q: str | None = None,
    sort: str = "price_asc",
    active: int = 1,
    limit: int = Query(60, le=500),
    offset: int = 0,
):
    con = db.connect()
    sql = "SELECT * FROM vehicles WHERE 1=1"
    args: list = []
    if active in (0, 1):
        sql += " AND active=?"; args.append(active)
    sql = _apply_filters(sql, args, make=make, model=model, body_type=body_type,
                         fuel=fuel, transmission=transmission,
                         drivetrain=drivetrain, region=region, city=city,
                         source=source, year_min=year_min, year_max=year_max,
                         price_min=price_min, price_max=price_max,
                         km_max=km_max, q=q)
    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]
    sql += f" ORDER BY {_SORTS.get(sort, _SORTS['price_asc'])} LIMIT ? OFFSET ?"
    args += [limit, offset]
    rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]
    con.close()
    return {"total": total, "count": len(rows), "vehicles": rows}


@app.get("/api/vehicles/{uid}")
def get_vehicle(uid: str):
    con = db.connect()
    row = con.execute("SELECT * FROM vehicles WHERE uid=?", (uid,)).fetchone()
    d = None
    if row is not None:
        d = _row_to_dict(row)
        d["price_history"] = [dict(r) for r in con.execute(
            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 10",
            (uid,)).fetchall()]
        # véhicules similaires : même marque+modèle, autres sources incluses
        if d.get("make") and d.get("model"):
            base_model = d["model"].split()[0]
            d["similar"] = [_row_to_dict(r) for r in con.execute(
                "SELECT * FROM vehicles WHERE active=1 AND make=? AND model LIKE ?"
                " AND uid<>? ORDER BY price IS NULL, price ASC LIMIT 6",
                (d["make"], f"{base_model}%", uid)).fetchall()]
        else:
            d["similar"] = []
    con.close()
    if d is None:
        raise HTTPException(404, "Véhicule introuvable")
    return d


@app.get("/api/facets")
def facets(make: str | None = None):
    """Valeurs distinctes pour construire les filtres du frontend.

    `make` (optionnel) restreint la liste des modèles à cette marque —
    utilisé par le sélecteur « Modèle » dépendant de « Marque ».
    """
    con = db.connect()
    model_sql = ("SELECT model, COUNT(*) n FROM vehicles"
                 " WHERE active=1 AND model<>''")
    model_args: list = []
    if make:
        model_sql += " AND make=?"
        model_args.append(make)
    out = {
        "makes": [dict(r) for r in con.execute(
            "SELECT make, COUNT(*) n FROM vehicles WHERE active=1 AND make<>''"
            " GROUP BY make ORDER BY n DESC")],
        "models": [dict(r) for r in con.execute(
            model_sql + " GROUP BY model ORDER BY n DESC LIMIT 80", model_args)],
        "body_types": [r["body_type"] for r in con.execute(
            "SELECT DISTINCT body_type FROM vehicles WHERE active=1"
            " AND body_type<>'' ORDER BY body_type")],
        "fuels": [r["fuel"] for r in con.execute(
            "SELECT DISTINCT fuel FROM vehicles WHERE active=1 AND fuel<>''"
            " ORDER BY fuel")],
        "regions": [dict(r) for r in con.execute(
            "SELECT region, COUNT(*) n FROM vehicles WHERE active=1 AND region<>''"
            " GROUP BY region ORDER BY n DESC")],
        "sources": [dict(r) for r in con.execute(
            "SELECT source, dealer_name, COUNT(*) n FROM vehicles WHERE active=1"
            " GROUP BY source ORDER BY n DESC")],
        "years": [dict(r) for r in con.execute(
            "SELECT MIN(year) y_min, MAX(year) y_max FROM vehicles"
            " WHERE active=1 AND year IS NOT NULL")],
    }
    con.close()
    return out


@app.get("/api/sources")
def sources():
    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]
    con = db.connect()
    counts = {r["source"]: r["n"] for r in con.execute(
        "SELECT source, COUNT(*) n FROM vehicles WHERE active=1 GROUP BY source")}
    last = {r["source"]: r["ts"] for r in con.execute(
        "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}
    con.close()
    for s in registry:
        s["active_listings"] = counts.get(s["id"], 0)
        s["last_sync"] = last.get(s["id"])
    return {"sources": registry}


@app.get("/api/stats")
def stats():
    con = db.connect()
    row = con.execute(
        """SELECT COUNT(*) total,
                  COUNT(DISTINCT source) sources,
                  COUNT(DISTINCT region) regions,
                  AVG(price) avg_price,
                  AVG(mileage_km) avg_km,
                  AVG(year) avg_year
           FROM vehicles WHERE active=1""").fetchone()
    by_region = [dict(r) for r in con.execute(
        "SELECT region, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles"
        " WHERE active=1 AND region<>'' GROUP BY region ORDER BY n DESC")]
    by_make = [dict(r) for r in con.execute(
        "SELECT make, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles"
        " WHERE active=1 AND make<>'' GROUP BY make ORDER BY n DESC LIMIT 20")]
    by_body = [dict(r) for r in con.execute(
        "SELECT body_type, COUNT(*) n FROM vehicles WHERE active=1"
        " AND body_type<>'' GROUP BY body_type ORDER BY n DESC")]
    # baisses de prix récentes (signal d'aubaine)
    drops = [dict(r) for r in con.execute(
        """SELECT v.uid, v.title, v.year, v.price, v.images, v.dealer_name, v.city,
                  p.prev_price
           FROM vehicles v JOIN (
             SELECT uid, price prev_price,
                    ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ts DESC) rn
             FROM price_log) p ON p.uid=v.uid AND p.rn=2
           WHERE v.active=1 AND v.price IS NOT NULL AND p.prev_price > v.price
           ORDER BY (p.prev_price - v.price) DESC LIMIT 12""")]
    for d in drops:
        d["images"] = json.loads(d.get("images") or "[]")[:1]
    log = [dict(r) for r in con.execute(
        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]
    con.close()
    return {**dict(row), "by_region": by_region, "by_make": by_make,
            "by_body": by_body, "price_drops": drops, "recent_syncs": log}


@app.get("/api/stats/detailed")
def stats_detailed():
    """Agrégats complets du marché (source unique : autoka/marketstats.py)."""
    from . import marketstats
    return marketstats.compute()


@app.get("/api/stats/rapport.pdf")
def rapport_pdf():
    """Rapport PDF « Le marché de l'occasion » — vue d'ensemble."""
    from fastapi.responses import Response
    from . import pdfgen
    return Response(
        content=pdfgen.rapport_pdf(), media_type="application/pdf",
        headers={"Content-Disposition":
                 'attachment; filename="auto-ka-rapport-marche.pdf"'})


@app.post("/api/sync")
def trigger_sync(background: BackgroundTasks, source: str | None = None):
    """Déclenche une synchronisation (équivalent d'un webhook entrant)."""
    def _job():
        with _sync_lock:
            ingest.run([source] if source else None)
    background.add_task(_job)
    return {"status": "démarré", "source": source or "toutes"}


# --- Frontend React (build Vite) --------------------------------------------
if FRONTEND_DIST.exists():
    app.mount("/assets", StaticFiles(directory=FRONTEND_DIST / "assets"), name="assets")

    @app.get("/{full_path:path}")
    def spa(full_path: str):
        target = FRONTEND_DIST / full_path
        if full_path and target.is_file():
            return FileResponse(target)
        return FileResponse(FRONTEND_DIST / "index.html")