spb/ora-ka Public

1# -----------------------------------------------------------------------------2# Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# web.py : API FastAPI (JSON) + service du frontend React (frontend/dist)5# -----------------------------------------------------------------------------6from __future__ import annotations78import json9import threading10from pathlib import Path1112from fastapi import BackgroundTasks, FastAPI, HTTPException, Query13from fastapi.middleware.cors import CORSMiddleware14from fastapi.responses import FileResponse15from fastapi.staticfiles import StaticFiles1617from . import db, ingest1819ROOT = Path(__file__).resolve().parent.parent20SOURCES_PATH = ROOT / "data" / "sources.json"21FRONTEND_DIST = ROOT / "frontend" / "dist"2223app = FastAPI(title="Auto-Ka API", version="1.0",24              description="Agrégateur de voitures usagées — province de Québec")25app.add_middleware(CORSMiddleware, allow_origins=["*"],26                   allow_methods=["*"], allow_headers=["*"])2728_sync_lock = threading.Lock()293031def _row_to_dict(row) -> dict:32    d = dict(row)33    d["features"] = json.loads(d.get("features") or "[]")34    d["images"] = json.loads(d.get("images") or "[]")35    d["details"] = json.loads(d.get("details") or "{}")36    return d373839def _apply_filters(sql: str, args: list, *, make=None, model=None, body_type=None,40                   fuel=None, transmission=None, drivetrain=None, region=None,41                   city=None, source=None, year_min=None, year_max=None,42                   price_min=None, price_max=None, km_max=None, q=None) -> str:43    if make:44        sql += " AND make=?"; args.append(make)45    if model:46        sql += " AND model LIKE ?"; args.append(f"{model}%")47    if body_type:48        sql += " AND body_type=?"; args.append(body_type)49    if fuel:50        if fuel == "Hybride":     # inclut l'hybride rechargeable51            sql += " AND fuel IN ('Hybride','Hybride rechargeable')"52        else:53            sql += " AND fuel=?"; args.append(fuel)54    if transmission:55        sql += " AND transmission=?"; args.append(transmission)56    if drivetrain:57        sql += " AND drivetrain=?"; args.append(drivetrain)58    if region:59        sql += " AND region=?"; args.append(region)60    if city:61        sql += " AND city LIKE ?"; args.append(f"%{city}%")62    if source:63        sql += " AND source=?"; args.append(source)64    if year_min is not None:65        sql += " AND year IS NOT NULL AND year>=?"; args.append(year_min)66    if year_max is not None:67        sql += " AND year IS NOT NULL AND year<=?"; args.append(year_max)68    if price_min is not None:69        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)70    if price_max is not None:71        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)72    if km_max is not None:73        sql += " AND mileage_km IS NOT NULL AND mileage_km<=?"; args.append(km_max)74    if q:75        sql += (" AND (title LIKE ? OR make LIKE ? OR model LIKE ?"76                " OR dealer_name LIKE ? OR city LIKE ?)")77        args += [f"%{q}%"] * 578    return sql798081_SORTS = {82    "price_asc": "price IS NULL, price ASC",83    "price_desc": "price IS NULL, price DESC",84    "km_asc": "mileage_km IS NULL, mileage_km ASC",85    "year_desc": "year IS NULL, year DESC",86    "recent": "first_seen DESC",87}888990@app.get("/api/vehicles")91def list_vehicles(92    kind: str = "auto",93    make: str | None = None,94    model: str | None = None,95    body_type: str | None = None,96    fuel: str | None = None,97    transmission: str | None = None,98    drivetrain: str | None = None,99    region: str | None = None,100    city: str | None = None,101    source: str | None = None,102    year_min: int | None = None,103    year_max: int | None = None,104    price_min: float | None = None,105    price_max: float | None = None,106    km_max: float | None = None,107    q: str | None = None,108    sort: str = "price_asc",109    active: int = 1,110    limit: int = Query(60, le=500),111    offset: int = 0,112):113    con = db.connect()114    sql = "SELECT * FROM vehicles WHERE 1=1"115    args: list = []116    if active in (0, 1):117        sql += " AND active=?"; args.append(active)118    if kind and kind != "tous":119        sql += " AND kind=?"; args.append(kind)120    sql = _apply_filters(sql, args, make=make, model=model, body_type=body_type,121                         fuel=fuel, transmission=transmission,122                         drivetrain=drivetrain, region=region, city=city,123                         source=source, year_min=year_min, year_max=year_max,124                         price_min=price_min, price_max=price_max,125                         km_max=km_max, q=q)126    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]127    sql += f" ORDER BY {_SORTS.get(sort, _SORTS['price_asc'])} LIMIT ? OFFSET ?"128    args += [limit, offset]129    rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]130    con.close()131    return {"total": total, "count": len(rows), "vehicles": rows}132133134@app.get("/api/vehicles/{uid}")135def get_vehicle(uid: str):136    con = db.connect()137    row = con.execute("SELECT * FROM vehicles WHERE uid=?", (uid,)).fetchone()138    d = None139    if row is not None:140        d = _row_to_dict(row)141        d["price_history"] = [dict(r) for r in con.execute(142            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 10",143            (uid,)).fetchall()]144        # véhicules similaires : même marque+modèle, autres sources incluses145        if d.get("make") and d.get("model"):146            base_model = d["model"].split()[0]147            d["similar"] = [_row_to_dict(r) for r in con.execute(148                "SELECT * FROM vehicles WHERE active=1 AND make=? AND model LIKE ?"149                " AND uid<>? ORDER BY price IS NULL, price ASC LIMIT 6",150                (d["make"], f"{base_model}%", uid)).fetchall()]151        else:152            d["similar"] = []153    con.close()154    if d is None:155        raise HTTPException(404, "Véhicule introuvable")156    return d157158159@app.get("/api/facets")160def facets(make: str | None = None, kind: str = "auto"):161    """Valeurs distinctes pour construire les filtres du frontend.162163    `make` (optionnel) restreint la liste des modèles à cette marque —164    utilisé par le sélecteur « Modèle » dépendant de « Marque ».165    """166    con = db.connect()167    kf = "" if kind in ("", "tous") else f" AND kind='{'moto' if kind=='moto' else 'scooter' if kind=='scooter' else 'auto'}'"168    model_sql = ("SELECT model, COUNT(*) n FROM vehicles"169                 " WHERE active=1 AND model<>''" + kf)170    model_args: list = []171    if make:172        model_sql += " AND make=?"173        model_args.append(make)174    out = {175        "makes": [dict(r) for r in con.execute(176            "SELECT make, COUNT(*) n FROM vehicles WHERE active=1" + kf + " AND make<>''"177            " GROUP BY make ORDER BY n DESC")],178        "models": [dict(r) for r in con.execute(179            model_sql + " GROUP BY model ORDER BY n DESC LIMIT 80", model_args)],180        "body_types": [r["body_type"] for r in con.execute(181            "SELECT DISTINCT body_type FROM vehicles WHERE active=1" + kf + ""182            " AND body_type<>'' ORDER BY body_type")],183        "fuels": [r["fuel"] for r in con.execute(184            "SELECT DISTINCT fuel FROM vehicles WHERE active=1" + kf + " AND fuel<>''"185            " ORDER BY fuel")],186        "regions": [dict(r) for r in con.execute(187            "SELECT region, COUNT(*) n FROM vehicles WHERE active=1" + kf + " AND region<>''"188            " GROUP BY region ORDER BY n DESC")],189        "sources": [dict(r) for r in con.execute(190            "SELECT source, dealer_name, COUNT(*) n FROM vehicles WHERE active=1" + kf + ""191            " GROUP BY source ORDER BY n DESC")],192        "years": [dict(r) for r in con.execute(193            "SELECT MIN(year) y_min, MAX(year) y_max FROM vehicles"194            " WHERE active=1" + kf + " AND year IS NOT NULL")],195    }196    con.close()197    return out198199200@app.get("/api/sources")201def sources():202    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]203    con = db.connect()204    counts = {r["source"]: r["n"] for r in con.execute(205        "SELECT source, COUNT(*) n FROM vehicles WHERE active=1 GROUP BY source")}206    last = {r["source"]: r["ts"] for r in con.execute(207        "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}208    con.close()209    for s in registry:210        s["active_listings"] = counts.get(s["id"], 0)211        s["last_sync"] = last.get(s["id"])212    return {"sources": registry}213214215@app.get("/api/stats")216def stats():217    con = db.connect()218    row = con.execute(219        """SELECT COUNT(*) total,220                  COUNT(DISTINCT source) sources,221                  COUNT(DISTINCT region) regions,222                  AVG(price) avg_price,223                  AVG(mileage_km) avg_km,224                  AVG(year) avg_year225           FROM vehicles WHERE active=1 AND kind='auto'""").fetchone()226    by_region = [dict(r) for r in con.execute(227        "SELECT region, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles"228        " WHERE active=1 AND kind='auto' AND region<>'' GROUP BY region ORDER BY n DESC")]229    by_make = [dict(r) for r in con.execute(230        "SELECT make, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles"231        " WHERE active=1 AND kind='auto' AND make<>'' GROUP BY make ORDER BY n DESC LIMIT 20")]232    by_body = [dict(r) for r in con.execute(233        "SELECT body_type, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"234        " AND body_type<>'' GROUP BY body_type ORDER BY n DESC")]235    # baisses de prix récentes (signal d'aubaine)236    drops = [dict(r) for r in con.execute(237        """SELECT v.uid, v.title, v.year, v.price, v.images, v.dealer_name, v.city,238                  p.prev_price239           FROM vehicles v JOIN (240             SELECT uid, price prev_price,241                    ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ts DESC) rn242             FROM price_log) p ON p.uid=v.uid AND p.rn=2243           WHERE v.active=1 AND kind='auto' AND v.price IS NOT NULL AND p.prev_price > v.price244           ORDER BY (p.prev_price - v.price) DESC LIMIT 12""")]245    for d in drops:246        d["images"] = json.loads(d.get("images") or "[]")[:1]247    log = [dict(r) for r in con.execute(248        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]249    con.close()250    return {**dict(row), "by_region": by_region, "by_make": by_make,251            "by_body": by_body, "price_drops": drops, "recent_syncs": log}252253254@app.get("/api/stats/detailed")255def stats_detailed():256    """Agrégats complets du marché (source unique : autoka/marketstats.py)."""257    from . import marketstats258    return marketstats.compute()259260261@app.get("/api/stats/rapport.pdf")262def rapport_pdf():263    """Rapport PDF « Le marché de l'occasion » — vue d'ensemble."""264    from fastapi.responses import Response265    from . import pdfgen266    return Response(267        content=pdfgen.rapport_pdf(), media_type="application/pdf",268        headers={"Content-Disposition":269                 'attachment; filename="auto-ka-rapport-marche.pdf"'})270271272@app.post("/api/sync")273def trigger_sync(background: BackgroundTasks, source: str | None = None):274    """Déclenche une synchronisation (équivalent d'un webhook entrant)."""275    def _job():276        with _sync_lock:277            ingest.run([source] if source else None)278    background.add_task(_job)279    return {"status": "démarré", "source": source or "toutes"}280281282# --- Frontend React (build Vite) --------------------------------------------283if FRONTEND_DIST.exists():284    app.mount("/assets", StaticFiles(directory=FRONTEND_DIST / "assets"), name="assets")285286    @app.get("/{full_path:path}")287    def spa(full_path: str):288        target = FRONTEND_DIST / full_path289        if full_path and target.is_file():290            return FileResponse(target)291        # index.html jamais mis en cache : les téléphones reçoivent toujours292        # la dernière version (les bundles /assets sont hachés, eux se cachent)293        return FileResponse(294            FRONTEND_DIST / "index.html",295            headers={"Cache-Control": "no-cache, must-revalidate"})296