spb/food-ka Public

1# -----------------------------------------------------------------------------2# Food-Ka — Agrégateur de produits d'épicerie (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 FileResponse, Response15from 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="Food-Ka API", version="1.0",24              description="Agrégateur de produits d'épicerie — province de Québec")25app.add_middleware(CORSMiddleware, allow_origins=["*"],26                   allow_methods=["*"], allow_headers=["*"])2728_sync_lock = threading.Lock()2930# tris supportés -> clause SQL (liste blanche, jamais d'injection)31_SORTS = {32    "price_asc": "price IS NULL, price ASC",33    "price_desc": "price IS NULL, price DESC",34    "unit_price": "unit_price IS NULL, unit_price ASC",35    "discount": "(CASE WHEN regular_price IS NOT NULL AND price IS NOT NULL"36                " THEN (regular_price - price) / regular_price ELSE 0 END) DESC",37    "name": "name COLLATE NOCASE ASC",38    "recent": "first_seen DESC",39}404142def _row_to_dict(row) -> dict:43    d = dict(row)44    d["keywords"] = json.loads(d.get("keywords") or "[]")45    d["images"] = json.loads(d.get("images") or "[]")46    d["details"] = json.loads(d.get("details") or "{}")47    d["on_sale"] = bool(d.get("on_sale"))48    if d.get("in_stock") is not None:49        d["in_stock"] = bool(d["in_stock"])50    return d515253@app.get("/api/products")54def list_products(55    category: str | None = None,56    source: str | None = None,57    brand: str | None = None,58    price_max: float | None = None,59    price_min: float | None = None,60    on_sale: int | None = None,          # 1 = en solde seulement61    in_stock: int | None = None,         # 1 / 062    q: str | None = None,63    sort: str = "price_asc",64    active: int = 1,65    limit: int = Query(60, le=500),66    offset: int = 0,67):68    con = db.connect()69    sql = "SELECT * FROM products WHERE 1=1"70    args: list = []71    if active in (0, 1):72        sql += " AND active=?"; args.append(active)73    if category:74        sql += " AND category=?"; args.append(category)75    if source:76        sql += " AND source=?"; args.append(source)77    if brand:78        sql += " AND brand LIKE ?"; args.append(f"%{brand}%")79    if price_max is not None:80        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)81    if price_min is not None:82        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)83    if on_sale == 1:84        sql += " AND on_sale=1"85    if in_stock in (0, 1):86        sql += " AND in_stock=?"; args.append(in_stock)87    if q:88        sql += " AND (name LIKE ? OR brand LIKE ? OR category_raw LIKE ?)"89        args += [f"%{q}%"] * 390    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]91    sql += f" ORDER BY {_SORTS.get(sort, _SORTS['price_asc'])} LIMIT ? OFFSET ?"92    args += [limit, offset]93    rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]94    con.close()95    return {"total": total, "count": len(rows), "products": rows}969798@app.get("/api/products/{uid}")99def get_product(uid: str):100    con = db.connect()101    row = con.execute("SELECT * FROM products WHERE uid=?", (uid,)).fetchone()102    d = None103    if row is not None:104        d = _row_to_dict(row)105        # historique de prix (suivi des soldes)106        d["price_history"] = [dict(r) for r in con.execute(107            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 12",108            (uid,)).fetchall()]109        # le même produit ailleurs : comparaison inter-bannières par nom110        if d["name"]:111            d["compare"] = [_row_to_dict(r) for r in con.execute(112                """SELECT * FROM products WHERE active=1 AND uid<>? AND name LIKE ?113                   ORDER BY price IS NULL, price ASC LIMIT 8""",114                (uid, f"%{d['name'].split('(')[0].strip()[:40]}%")).fetchall()]115        else:116            d["compare"] = []117    con.close()118    if d is None:119        raise HTTPException(404, "Produit introuvable")120    return d121122123@app.get("/api/facets")124def facets(category: str | None = None):125    """Valeurs distinctes pour construire les filtres du frontend."""126    con = db.connect()127    brand_sql = ("SELECT brand, COUNT(*) n FROM products"128                 " WHERE active=1 AND brand<>''")129    brand_args: list = []130    if category:131        brand_sql += " AND category=?"132        brand_args.append(category)133    out = {134        "categories": [dict(r) for r in con.execute(135            "SELECT category, COUNT(*) n FROM products WHERE active=1 AND category<>''"136            " GROUP BY category ORDER BY n DESC")],137        "brands": [dict(r) for r in con.execute(138            brand_sql + " GROUP BY brand ORDER BY n DESC LIMIT 60", brand_args)],139        "sources": [dict(r) for r in con.execute(140            "SELECT source, COUNT(*) n FROM products WHERE active=1"141            " GROUP BY source ORDER BY n DESC")],142        "on_sale": con.execute(143            "SELECT COUNT(*) c FROM products WHERE active=1 AND on_sale=1"144        ).fetchone()["c"],145    }146    con.close()147    return out148149150@app.get("/api/sources")151def sources():152    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]153    con = db.connect()154    counts = {r["source"]: r["n"] for r in con.execute(155        "SELECT source, COUNT(*) n FROM products WHERE active=1 GROUP BY source")}156    last = {r["source"]: r["ts"] for r in con.execute(157        "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}158    con.close()159    for s in registry:160        s["active_products"] = counts.get(s["id"], 0)161        s["last_sync"] = last.get(s["id"])162    return {"sources": registry}163164165@app.get("/api/stats")166def stats():167    con = db.connect()168    row = con.execute(169        """SELECT COUNT(*) total,170                  SUM(on_sale) on_sale,171                  COUNT(DISTINCT source) sources,172                  COUNT(DISTINCT category) categories,173                  AVG(price) avg_price174           FROM products WHERE active=1""").fetchone()175    by_source = [dict(r) for r in con.execute(176        """SELECT source, COUNT(*) n, SUM(on_sale) sales, AVG(price) avg_price177           FROM products WHERE active=1 GROUP BY source ORDER BY n DESC""")]178    by_category = [dict(r) for r in con.execute(179        """SELECT category, COUNT(*) n, AVG(price) avg_price180           FROM products WHERE active=1 AND category<>''181           GROUP BY category ORDER BY n DESC""")]182    # meilleures aubaines du moment (rabais relatif le plus fort)183    deals = [_row_to_dict(r) for r in con.execute(184        """SELECT * FROM products185           WHERE active=1 AND regular_price IS NOT NULL AND price IS NOT NULL186           ORDER BY (regular_price - price) / regular_price DESC LIMIT 24""")]187    log = [dict(r) for r in con.execute(188        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]189    con.close()190    return {**dict(row), "by_source": by_source, "by_category": by_category,191            "deals": deals, "recent_syncs": log}192193194@app.get("/api/stats/detailed")195def stats_detailed():196    """Agrégats du marché (source unique : foodka/marketstats.py)."""197    from . import marketstats198    return marketstats.compute()199200201@app.get("/api/stats/rapport.pdf")202def rapport_pdf():203    """Rapport PDF du marché — mêmes chiffres que la page Statistiques."""204    from . import pdfgen205    return Response(206        content=pdfgen.rapport_pdf(),207        media_type="application/pdf",208        headers={"Content-Disposition":209                 'attachment; filename="foodka-rapport-marche.pdf"'})210211212@app.post("/api/sync")213def trigger_sync(background: BackgroundTasks, source: str | None = None):214    """Déclenche une synchronisation (équivalent d'un webhook entrant)."""215    def _job():216        with _sync_lock:217            ingest.run([source] if source else None)218    background.add_task(_job)219    return {"status": "démarré", "source": source or "toutes"}220221222# --- Frontend React (build Vite) --------------------------------------------223if FRONTEND_DIST.exists():224    app.mount("/assets", StaticFiles(directory=FRONTEND_DIST / "assets"), name="assets")225226    @app.get("/{full_path:path}")227    def spa(full_path: str):228        target = FRONTEND_DIST / full_path229        if full_path and target.is_file():230            return FileResponse(target)231        return FileResponse(FRONTEND_DIST / "index.html")232