spb/auto-ka Public
Python 82.3%
TypeScript 12%
CSS 5.4%
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 make: str | None = None,93 model: str | None = None,94 body_type: str | None = None,95 fuel: str | None = None,96 transmission: str | None = None,97 drivetrain: str | None = None,98 region: str | None = None,99 city: str | None = None,100 source: str | None = None,101 year_min: int | None = None,102 year_max: int | None = None,103 price_min: float | None = None,104 price_max: float | None = None,105 km_max: float | None = None,106 q: str | None = None,107 sort: str = "price_asc",108 active: int = 1,109 limit: int = Query(60, le=500),110 offset: int = 0,111):112 con = db.connect()113 sql = "SELECT * FROM vehicles WHERE 1=1"114 args: list = []115 if active in (0, 1):116 sql += " AND active=?"; args.append(active)117 sql = _apply_filters(sql, args, make=make, model=model, body_type=body_type,118 fuel=fuel, transmission=transmission,119 drivetrain=drivetrain, region=region, city=city,120 source=source, year_min=year_min, year_max=year_max,121 price_min=price_min, price_max=price_max,122 km_max=km_max, q=q)123 total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]124 sql += f" ORDER BY {_SORTS.get(sort, _SORTS['price_asc'])} LIMIT ? OFFSET ?"125 args += [limit, offset]126 rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]127 con.close()128 return {"total": total, "count": len(rows), "vehicles": rows}129130131@app.get("/api/vehicles/{uid}")132def get_vehicle(uid: str):133 con = db.connect()134 row = con.execute("SELECT * FROM vehicles WHERE uid=?", (uid,)).fetchone()135 d = None136 if row is not None:137 d = _row_to_dict(row)138 d["price_history"] = [dict(r) for r in con.execute(139 "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 10",140 (uid,)).fetchall()]141 # véhicules similaires : même marque+modèle, autres sources incluses142 if d.get("make") and d.get("model"):143 base_model = d["model"].split()[0]144 d["similar"] = [_row_to_dict(r) for r in con.execute(145 "SELECT * FROM vehicles WHERE active=1 AND make=? AND model LIKE ?"146 " AND uid<>? ORDER BY price IS NULL, price ASC LIMIT 6",147 (d["make"], f"{base_model}%", uid)).fetchall()]148 else:149 d["similar"] = []150 con.close()151 if d is None:152 raise HTTPException(404, "Véhicule introuvable")153 return d154155156@app.get("/api/facets")157def facets(make: str | None = None):158 """Valeurs distinctes pour construire les filtres du frontend.159160 `make` (optionnel) restreint la liste des modèles à cette marque —161 utilisé par le sélecteur « Modèle » dépendant de « Marque ».162 """163 con = db.connect()164 model_sql = ("SELECT model, COUNT(*) n FROM vehicles"165 " WHERE active=1 AND model<>''")166 model_args: list = []167 if make:168 model_sql += " AND make=?"169 model_args.append(make)170 out = {171 "makes": [dict(r) for r in con.execute(172 "SELECT make, COUNT(*) n FROM vehicles WHERE active=1 AND make<>''"173 " GROUP BY make ORDER BY n DESC")],174 "models": [dict(r) for r in con.execute(175 model_sql + " GROUP BY model ORDER BY n DESC LIMIT 80", model_args)],176 "body_types": [r["body_type"] for r in con.execute(177 "SELECT DISTINCT body_type FROM vehicles WHERE active=1"178 " AND body_type<>'' ORDER BY body_type")],179 "fuels": [r["fuel"] for r in con.execute(180 "SELECT DISTINCT fuel FROM vehicles WHERE active=1 AND fuel<>''"181 " ORDER BY fuel")],182 "regions": [dict(r) for r in con.execute(183 "SELECT region, COUNT(*) n FROM vehicles WHERE active=1 AND region<>''"184 " GROUP BY region ORDER BY n DESC")],185 "sources": [dict(r) for r in con.execute(186 "SELECT source, dealer_name, COUNT(*) n FROM vehicles WHERE active=1"187 " GROUP BY source ORDER BY n DESC")],188 "years": [dict(r) for r in con.execute(189 "SELECT MIN(year) y_min, MAX(year) y_max FROM vehicles"190 " WHERE active=1 AND year IS NOT NULL")],191 }192 con.close()193 return out194195196@app.get("/api/sources")197def sources():198 registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]199 con = db.connect()200 counts = {r["source"]: r["n"] for r in con.execute(201 "SELECT source, COUNT(*) n FROM vehicles WHERE active=1 GROUP BY source")}202 last = {r["source"]: r["ts"] for r in con.execute(203 "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}204 con.close()205 for s in registry:206 s["active_listings"] = counts.get(s["id"], 0)207 s["last_sync"] = last.get(s["id"])208 return {"sources": registry}209210211@app.get("/api/stats")212def stats():213 con = db.connect()214 row = con.execute(215 """SELECT COUNT(*) total,216 COUNT(DISTINCT source) sources,217 COUNT(DISTINCT region) regions,218 AVG(price) avg_price,219 AVG(mileage_km) avg_km,220 AVG(year) avg_year221 FROM vehicles WHERE active=1""").fetchone()222 by_region = [dict(r) for r in con.execute(223 "SELECT region, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles"224 " WHERE active=1 AND region<>'' GROUP BY region ORDER BY n DESC")]225 by_make = [dict(r) for r in con.execute(226 "SELECT make, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles"227 " WHERE active=1 AND make<>'' GROUP BY make ORDER BY n DESC LIMIT 20")]228 by_body = [dict(r) for r in con.execute(229 "SELECT body_type, COUNT(*) n FROM vehicles WHERE active=1"230 " AND body_type<>'' GROUP BY body_type ORDER BY n DESC")]231 # baisses de prix récentes (signal d'aubaine)232 drops = [dict(r) for r in con.execute(233 """SELECT v.uid, v.title, v.year, v.price, v.images, v.dealer_name, v.city,234 p.prev_price235 FROM vehicles v JOIN (236 SELECT uid, price prev_price,237 ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ts DESC) rn238 FROM price_log) p ON p.uid=v.uid AND p.rn=2239 WHERE v.active=1 AND v.price IS NOT NULL AND p.prev_price > v.price240 ORDER BY (p.prev_price - v.price) DESC LIMIT 12""")]241 for d in drops:242 d["images"] = json.loads(d.get("images") or "[]")[:1]243 log = [dict(r) for r in con.execute(244 "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]245 con.close()246 return {**dict(row), "by_region": by_region, "by_make": by_make,247 "by_body": by_body, "price_drops": drops, "recent_syncs": log}248249250@app.post("/api/sync")251def trigger_sync(background: BackgroundTasks, source: str | None = None):252 """Déclenche une synchronisation (équivalent d'un webhook entrant)."""253 def _job():254 with _sync_lock:255 ingest.run([source] if source else None)256 background.add_task(_job)257 return {"status": "démarré", "source": source or "toutes"}258259260# --- Frontend React (build Vite) --------------------------------------------261if FRONTEND_DIST.exists():262 app.mount("/assets", StaticFiles(directory=FRONTEND_DIST / "assets"), name="assets")263264 @app.get("/{full_path:path}")265 def spa(full_path: str):266 target = FRONTEND_DIST / full_path267 if full_path and target.is_file():268 return FileResponse(target)269 return FileResponse(FRONTEND_DIST / "index.html")270