spb/fabri-ka Public
Agrégateur de produits québécois — www.fabri-ka.com
HTML 57.9%
Python 18.6%
TypeScript 15.6%
CSS 7.8%
1# -----------------------------------------------------------------------------2# Fabri-Ka — Agrégateur de produits québécois3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# web.py : API FastAPI + service du frontend React (dist/)5# -----------------------------------------------------------------------------6from __future__ import annotations78import json9import sqlite310import threading11from datetime import date12from pathlib import Path1314from fastapi import BackgroundTasks, FastAPI, HTTPException, Query15from fastapi.responses import FileResponse16from fastapi.staticfiles import StaticFiles1718from . import db19from .schema import CATEGORIES, REGIONS2021app = FastAPI(title="Fabri-Ka API", docs_url="/api/docs", openapi_url="/api/openapi.json")2223FRONT_DIST = Path(__file__).resolve().parent.parent / "frontend" / "dist"242526def q(con: sqlite3.Connection, sql: str, args=()) -> list[dict]:27 return [dict(r) for r in con.execute(sql, args)]282930def _product_out(r: dict) -> dict:31 r["images"] = json.loads(r.get("images") or "[]")32 r["tags"] = json.loads(r.get("tags") or "[]")33 return r343536_FTS_SAFE = set("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789àâäéèêëîïôöùûüçÀÂÄÉÈÊËÎÏÔÖÙÛÜÇ- '")373839def fts_query(text: str) -> str:40 words = "".join(c if c in _FTS_SAFE else " " for c in text).split()41 return " ".join(f'"{w}"*' for w in words[:8])424344@app.get("/api/products")45def products(q_text: str | None = Query(None, alias="q"),46 category: str | None = None,47 region: str | None = None,48 store: str | None = None,49 origin: str | None = None,50 price_min: float | None = None,51 price_max: float | None = None,52 available: bool | None = None,53 sort: str = "recent",54 page: int = 1,55 per_page: int = Query(24, le=100)):56 con = db.connect()57 try:58 where, args = ["p.active=1"], []59 joins = "FROM products p JOIN stores s ON s.id = p.store_id"60 if q_text:61 joins += " JOIN products_fts f ON f.uid = p.uid"62 where.append("products_fts MATCH ?")63 args.append(fts_query(q_text))64 if category:65 where.append("p.category=?"); args.append(category)66 if region:67 where.append("s.region=?"); args.append(region)68 if store:69 where.append("p.store_id=?"); args.append(store)70 if origin:71 where.append("s.origin_class IN (%s)" % ",".join("?" * len(origin.split(","))))72 args += origin.split(",")73 if price_min is not None:74 where.append("p.price>=?"); args.append(price_min)75 if price_max is not None:76 where.append("p.price<=?"); args.append(price_max)77 if available is not None:78 where.append("p.available=?"); args.append(int(available))79 wsql = " AND ".join(where)80 order = {"recent": "p.first_seen DESC",81 "price_asc": "p.price IS NULL, p.price ASC",82 "price_desc": "p.price IS NULL, p.price DESC",83 "title": "p.title COLLATE NOCASE"}.get(sort, "p.first_seen DESC")84 total = con.execute(f"SELECT COUNT(*) {joins} WHERE {wsql}", args).fetchone()[0]85 rows = q(con, f"""SELECT p.*, s.name AS store_name, s.region AS store_region,86 s.city AS store_city, s.origin_class87 {joins} WHERE {wsql} ORDER BY {order}88 LIMIT ? OFFSET ?""", args + [per_page, (page - 1) * per_page])89 return {"total": total, "page": page, "per_page": per_page,90 "items": [_product_out(r) for r in rows]}91 finally:92 con.close()939495@app.get("/api/products/{uid}")96def product(uid: str):97 con = db.connect()98 try:99 rows = q(con, """SELECT p.*, s.name AS store_name, s.region AS store_region,100 s.city AS store_city, s.origin_class, s.url AS store_url101 FROM products p JOIN stores s ON s.id=p.store_id WHERE p.uid=?""", (uid,))102 if not rows:103 raise HTTPException(404)104 out = _product_out(rows[0])105 out["related"] = [_product_out(r) for r in q(con, """106 SELECT p.*, s.name AS store_name FROM products p JOIN stores s ON s.id=p.store_id107 WHERE p.store_id=? AND p.uid<>? AND p.active=1 ORDER BY RANDOM() LIMIT 8""",108 (rows[0]["store_id"], uid))]109 return out110 finally:111 con.close()112113114@app.get("/api/stores")115def stores(region: str | None = None, platform: str | None = None,116 origin: str | None = None, q_text: str | None = Query(None, alias="q"),117 with_products: bool = False):118 con = db.connect()119 try:120 where, args = ["1=1"], []121 if region:122 where.append("region=?"); args.append(region)123 if platform:124 where.append("platform=?"); args.append(platform)125 if origin:126 where.append("origin_class=?"); args.append(origin)127 if q_text:128 where.append("(name LIKE ? OR id LIKE ?)"); args += [f"%{q_text}%", f"%{q_text}%"]129 if with_products:130 where.append("product_count>0")131 rows = q(con, f"""SELECT id, name, url, platform, city, region, origin_class,132 origin_confidence, categories, language, product_count,133 last_sync, last_status, logo_url, cover_url,134 description_meta135 FROM stores WHERE {' AND '.join(where)}136 ORDER BY product_count DESC, name""", args)137 for r in rows:138 r["categories"] = json.loads(r.get("categories") or "[]")139 return {"total": len(rows), "items": rows}140 finally:141 con.close()142143144@app.get("/api/stores/{store_id}")145def store_detail(store_id: str):146 con = db.connect()147 try:148 rows = q(con, "SELECT * FROM stores WHERE id=?", (store_id,))149 if not rows:150 raise HTTPException(404)151 s = rows[0]152 for k in ("categories", "socials", "discovery_sources"):153 s[k] = json.loads(s.get(k) or "[]")154 # statistiques produits155 stats = q(con, """SELECT COUNT(*) AS n, MIN(price) AS price_min,156 MAX(price) AS price_max, AVG(price) AS price_avg157 FROM products WHERE store_id=? AND active=1 AND price>0""",158 (store_id,))159 s["product_stats"] = stats[0] if stats else {}160 s["category_breakdown"] = q(con, """SELECT category AS key, COUNT(*) AS n161 FROM products WHERE store_id=? AND active=1 GROUP BY category162 ORDER BY n DESC LIMIT 8""", (store_id,))163 for c in s["category_breakdown"]:164 c["label"] = CATEGORIES.get(c["key"], (c["key"], []))[0]165 # boutiques similaires : même catégorie dominante ou même région, avec produits166 topcat = s["category_breakdown"][0]["key"] if s["category_breakdown"] else None167 s["similar"] = q(con, """168 SELECT DISTINCT st.id, st.name, st.region, st.origin_class, st.logo_url,169 st.product_count170 FROM stores st171 LEFT JOIN products p ON p.store_id=st.id AND p.active=1 AND p.category=?172 WHERE st.id<>? AND st.product_count>0173 AND (p.uid IS NOT NULL OR (st.region<>'' AND st.region=?))174 ORDER BY (st.region=?) DESC, st.product_count DESC LIMIT 8""",175 (topcat, store_id, s.get("region") or "", s.get("region") or ""))176 return s177 finally:178 con.close()179180181@app.get("/api/facets")182def facets():183 con = db.connect()184 try:185 cats = q(con, """SELECT p.category AS key, COUNT(*) AS n FROM products p186 WHERE p.active=1 GROUP BY p.category ORDER BY n DESC""")187 for c in cats:188 c["label"] = CATEGORIES.get(c["key"], (c["key"], []))[0]189 regions = q(con, """SELECT s.region AS key, COUNT(*) AS n FROM products p190 JOIN stores s ON s.id=p.store_id191 WHERE p.active=1 AND s.region<>'' GROUP BY s.region ORDER BY n DESC""")192 origins = q(con, """SELECT s.origin_class AS key, COUNT(*) AS n FROM products p193 JOIN stores s ON s.id=p.store_id WHERE p.active=1194 GROUP BY s.origin_class ORDER BY n DESC""")195 top_stores = q(con, """SELECT id AS key, name, product_count AS n FROM stores196 WHERE product_count>0 ORDER BY n DESC LIMIT 40""")197 return {"categories": cats, "regions": regions, "origins": origins,198 "stores": top_stores, "all_regions": REGIONS}199 finally:200 con.close()201202203_stats_cache: dict = {"ts": 0.0, "data": None}204205206@app.get("/api/stats/extended")207def stats_extended():208 """Statistiques riches pour la page /stats : prix, catégories, croissance.209 Coûteux (médianes) → cache mémoire 10 minutes."""210 import time as _time211 if _stats_cache["data"] is not None and _time.time() - _stats_cache["ts"] < 600:212 return _stats_cache["data"]213 con = db.connect()214 try:215 def median_price(where_sql, args):216 n = con.execute(f"SELECT COUNT(*) FROM products WHERE {where_sql} AND price>0", args).fetchone()[0]217 if not n:218 return None219 row = con.execute(f"""SELECT price FROM products WHERE {where_sql} AND price>0220 ORDER BY price LIMIT 1 OFFSET ?""", args + [n // 2]).fetchone()221 return row[0] if row else None222223 by_category = []224 for r in q(con, """SELECT category AS key, COUNT(*) AS products,225 COUNT(DISTINCT store_id) AS stores,226 MIN(CASE WHEN price>0 THEN price END) AS price_min,227 ROUND(AVG(CASE WHEN price>0 AND price<=500000 THEN price END),2) AS price_avg,228 MAX(CASE WHEN price<=500000 THEN price END) AS price_max229 FROM products WHERE active=1 GROUP BY category230 ORDER BY products DESC"""):231 r["label"] = CATEGORIES.get(r["key"], (r["key"], []))[0]232 r["price_median"] = median_price("active=1 AND category=?", [r["key"]])233 by_category.append(r)234235 global_median = median_price("active=1", [])236 price_buckets = q(con, """SELECT CASE237 WHEN price < 10 THEN '0-10'238 WHEN price < 25 THEN '10-25'239 WHEN price < 50 THEN '25-50'240 WHEN price < 100 THEN '50-100'241 WHEN price < 250 THEN '100-250'242 WHEN price < 1000 THEN '250-1000'243 ELSE '1000+' END AS bucket, COUNT(*) AS n244 FROM products WHERE active=1 AND price>0 GROUP BY bucket""")245 order = ['0-10', '10-25', '25-50', '50-100', '100-250', '250-1000', '1000+']246 price_buckets.sort(key=lambda b: order.index(b["bucket"]))247248 by_origin = q(con, """SELECT s.origin_class AS key, COUNT(DISTINCT s.id) AS stores,249 COUNT(p.uid) AS products250 FROM stores s LEFT JOIN products p251 ON p.store_id=s.id AND p.active=1252 GROUP BY s.origin_class ORDER BY products DESC""")253 by_platform = q(con, """SELECT platform AS key, COUNT(*) AS stores,254 SUM(product_count) AS products255 FROM stores WHERE platform<>'' GROUP BY platform256 ORDER BY products DESC""")257 by_region = q(con, """SELECT s.region AS key, COUNT(DISTINCT s.id) AS stores,258 COUNT(p.uid) AS products,259 ROUND(AVG(CASE WHEN p.price>0 AND p.price<=500000 THEN p.price END),2) AS price_avg260 FROM stores s LEFT JOIN products p261 ON p.store_id=s.id AND p.active=1262 WHERE s.region<>'' GROUP BY s.region ORDER BY products DESC""")263 growth = q(con, """SELECT date(first_seen,'unixepoch') AS day, COUNT(*) AS n264 FROM products WHERE active=1265 GROUP BY day ORDER BY day DESC LIMIT 30""")266 top_stores = q(con, """SELECT id, name, region, origin_class, logo_url,267 product_count AS products268 FROM stores WHERE product_count>0269 ORDER BY product_count DESC LIMIT 15""")270 most_expensive = q(con, """SELECT p.uid, p.title, p.price, s.name AS store_name271 FROM products p JOIN stores s ON s.id=p.store_id272 WHERE p.active=1 AND p.price>0273 ORDER BY p.price DESC LIMIT 8""")274 newest = q(con, """SELECT p.uid, p.title, p.price, p.category, s.name AS store_name275 FROM products p JOIN stores s ON s.id=p.store_id276 WHERE p.active=1 ORDER BY p.first_seen DESC LIMIT 12""")277 for r in newest:278 r["category_label"] = CATEGORIES.get(r["category"], (r["category"], []))[0]279 availability = q(con, """SELECT CASE WHEN available=1 THEN 'en stock'280 WHEN available=0 THEN 'rupture'281 ELSE 'inconnu' END AS key, COUNT(*) AS n282 FROM products WHERE active=1 GROUP BY key""")283 coverage = q(con, """SELECT284 (SELECT COUNT(*) FROM products WHERE active=1 AND images<>'[]' AND images IS NOT NULL) AS with_image,285 (SELECT COUNT(*) FROM products WHERE active=1 AND description<>'') AS with_desc,286 (SELECT COUNT(*) FROM stores WHERE logo_url IS NOT NULL AND logo_url<>'') AS with_logo,287 (SELECT COUNT(*) FROM stores WHERE region<>'') AS with_region288 """)[0]289 totals = q(con, """SELECT290 (SELECT COUNT(*) FROM products WHERE active=1) AS products,291 (SELECT COUNT(*) FROM products WHERE active=1 AND price>0) AS products_priced,292 (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores_live,293 (SELECT COUNT(*) FROM stores) AS stores_registry,294 (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'') AS regions,295 (SELECT ROUND(AVG(price),2) FROM products WHERE active=1 AND price>0 AND price<=500000) AS price_avg296 """)[0]297 totals["price_median"] = global_median298 data = {"totals": totals, "by_category": by_category,299 "price_buckets": price_buckets, "by_origin": by_origin,300 "by_platform": by_platform, "by_region": by_region,301 "growth": growth, "top_stores": top_stores,302 "most_expensive": most_expensive, "newest": newest,303 "availability": availability, "coverage": coverage}304 _stats_cache.update(ts=_time.time(), data=data)305 return data306 finally:307 con.close()308309310@app.get("/api/report.pdf")311def report_pdf():312 """Rapport de marché Fabri-Ka en PDF (logo, KPIs, prix, catégories,313 régions, origine, plateformes, top boutiques)."""314 from fastapi.responses import Response315 from . import report316 stats = stats_extended()317 pdf = report.build_report(stats)318 fname = f"rapport-marche-fabri-ka-{date.today()}.pdf"319 return Response(content=pdf, media_type="application/pdf",320 headers={"Content-Disposition": f'attachment; filename="{fname}"'})321322323@app.get("/api/stats")324def stats():325 con = db.connect()326 try:327 totals = q(con, """SELECT328 (SELECT COUNT(*) FROM products WHERE active=1) AS products,329 (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores_live,330 (SELECT COUNT(*) FROM stores) AS stores_registry,331 (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'' AND product_count>0) AS regions332 """)[0]333 by_region = q(con, """SELECT region, COUNT(*) AS stores, SUM(product_count) AS products334 FROM stores WHERE region<>'' GROUP BY region ORDER BY products DESC""")335 recent = q(con, """SELECT ts, store_id, found, added, updated, removed, status336 FROM sync_log ORDER BY ts DESC LIMIT 30""")337 return {"totals": totals, "by_region": by_region, "sync_log": recent}338 finally:339 con.close()340341342_sync_running = threading.Lock()343344345@app.post("/api/sync")346def trigger_sync(background: BackgroundTasks):347 if _sync_running.locked():348 return {"status": "already-running"}349350 def _job():351 with _sync_running:352 from . import ingest353 ingest.run()354355 background.add_task(_job)356 return {"status": "started"}357358359# ---- frontend ---------------------------------------------------------------360if FRONT_DIST.exists():361 app.mount("/assets", StaticFiles(directory=FRONT_DIST / "assets"), name="assets")362363 @app.get("/{path:path}")364 def spa(path: str):365 target = FRONT_DIST / path366 if path and target.is_file():367 return FileResponse(target)368 return FileResponse(FRONT_DIST / "index.html")369