spb/forma-ka Public
Python 65.1%
TypeScript 17.9%
CSS 16.4%
HTML 0.5%
1# -----------------------------------------------------------------------------2# Forma-Ka — Agrégateur de formations (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="Forma-Ka API", version="1.0",24 description="Agrégateur de formations — province de Québec")25app.add_middleware(CORSMiddleware, allow_origins=["*"],26 allow_methods=["*"], allow_headers=["*"])2728_sync_lock = threading.Lock()2930_JSON_COLS = ("sessions", "objectives", "program", "tags", "details", "images")313233def _row_to_dict(row) -> dict:34 d = dict(row)35 for col in _JSON_COLS:36 default = "{}" if col == "details" else "[]"37 d[col] = json.loads(d.get(col) or default)38 if d.get("is_free") is not None:39 d["is_free"] = bool(d["is_free"])40 return d414243def _apply_filters(sql: str, args: list,44 training_type: str | None, category: str | None,45 mode: str | None, city: str | None, language: str | None,46 level: str | None, source: str | None,47 free: int | None, price_max: float | None,48 credential: str | None, starts_after: str | None,49 q: str | None) -> str:50 if training_type:51 sql += " AND training_type=?"; args.append(training_type)52 if category:53 sql += " AND category LIKE ?"; args.append(f"%{category}%")54 if mode:55 sql += " AND mode=?"; args.append(mode)56 if city:57 sql += " AND city LIKE ?"; args.append(f"%{city}%")58 if language:59 sql += " AND language=?"; args.append(language)60 if level:61 sql += " AND level=?"; args.append(level)62 if source:63 sql += " AND source=?"; args.append(source)64 if free == 1:65 sql += " AND is_free=1"66 elif free == 0:67 sql += " AND is_free=0"68 if price_max is not None:69 sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)70 if credential:71 sql += " AND credential LIKE ?"; args.append(f"%{credential}%")72 if starts_after:73 sql += " AND start_date IS NOT NULL AND start_date>=?"74 args.append(starts_after)75 if q:76 sql += (" AND (title LIKE ? OR description LIKE ? OR category LIKE ?"77 " OR code LIKE ? OR tags LIKE ?)")78 args += [f"%{q}%"] * 579 return sql808182@app.get("/api/formations")83def list_formations(84 training_type: str | None = None, # Cours universitaire, Séminaire…85 category: str | None = None, # domaine (LIKE)86 mode: str | None = None, # en ligne | présentiel | hybride | asynchrone87 city: str | None = None,88 language: str | None = None, # fr | en | fr/en89 level: str | None = None, # débutant | intermédiaire | avancé90 source: str | None = None,91 free: int | None = None, # 1 = gratuites seulement92 price_max: float | None = None,93 credential: str | None = None, # attestation, UEC, certificat…94 starts_after: str | None = None, # ISO : prochaine séance à partir de95 q: str | None = None,96 active: int = 1,97 sort: str = "recent", # recent | price | title | start98 limit: int = Query(500, le=2000),99 offset: int = 0,100):101 con = db.connect()102 sql = "SELECT * FROM formations WHERE 1=1"103 args: list = []104 if active in (0, 1):105 sql += " AND active=?"; args.append(active)106 sql = _apply_filters(sql, args, training_type, category, mode, city,107 language, level, source, free, price_max,108 credential, starts_after, q)109 total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]110 order = {111 "price": " ORDER BY price IS NULL, price ASC",112 "title": " ORDER BY title COLLATE NOCASE ASC",113 "start": " ORDER BY start_date IS NULL, start_date ASC",114 "recent": " ORDER BY first_seen DESC",115 }.get(sort, " ORDER BY first_seen DESC")116 sql += order + " LIMIT ? OFFSET ?"117 args += [limit, offset]118 rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]119 con.close()120 return {"total": total, "count": len(rows), "formations": rows}121122123@app.get("/api/formations/{uid:path}")124def get_formation(uid: str):125 con = db.connect()126 row = con.execute("SELECT * FROM formations WHERE uid=?", (uid,)).fetchone()127 d = None128 if row is not None:129 d = _row_to_dict(row)130 d["price_history"] = [dict(r) for r in con.execute(131 "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 6",132 (uid,)).fetchall()]133 # formations similaires : même source ou même catégorie134 d["similar"] = [135 {"uid": r["uid"], "title": r["title"], "training_type": r["training_type"],136 "source": r["source"], "price": r["price"], "mode": r["mode"],137 "duration": r["duration"]}138 for r in con.execute(139 "SELECT uid, title, training_type, source, price, mode, duration"140 " FROM formations WHERE active=1 AND uid<>? AND"141 " (category=? OR source=?) ORDER BY RANDOM() LIMIT 6",142 (uid, d.get("category") or "-", d["source"])).fetchall()]143 con.close()144 if d is None:145 raise HTTPException(404, "Formation introuvable")146 return d147148149@app.get("/api/facets")150def facets(training_type: str | None = None):151 """Valeurs distinctes pour construire les filtres du frontend."""152 con = db.connect()153 cat_sql = "SELECT category, COUNT(*) n FROM formations WHERE active=1 AND category<>''"154 cat_args: list = []155 if training_type:156 cat_sql += " AND training_type=?"157 cat_args.append(training_type)158 out = {159 "types": [dict(r) for r in con.execute(160 "SELECT training_type t, COUNT(*) n FROM formations"161 " WHERE active=1 AND training_type<>'' GROUP BY training_type ORDER BY n DESC")],162 "categories": [dict(r) for r in con.execute(163 cat_sql + " GROUP BY category ORDER BY n DESC LIMIT 60", cat_args)],164 "modes": [r["mode"] for r in con.execute(165 "SELECT DISTINCT mode FROM formations WHERE active=1 AND mode<>'' ORDER BY mode")],166 "cities": [r["city"] for r in con.execute(167 "SELECT DISTINCT city FROM formations WHERE active=1 AND city<>'' ORDER BY city")],168 "languages": [r["language"] for r in con.execute(169 "SELECT DISTINCT language FROM formations WHERE active=1 AND language<>'' ORDER BY language")],170 "levels": [r["level"] for r in con.execute(171 "SELECT DISTINCT level FROM formations WHERE active=1 AND level<>'' ORDER BY level")],172 "sources": [dict(r) for r in con.execute(173 "SELECT source, COUNT(*) n FROM formations WHERE active=1"174 " GROUP BY source ORDER BY n DESC")],175 }176 con.close()177 return out178179180@app.get("/api/sources")181def sources():182 registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]183 con = db.connect()184 counts = {r["source"]: r["n"] for r in con.execute(185 "SELECT source, COUNT(*) n FROM formations WHERE active=1 GROUP BY source")}186 last = {r["source"]: r["ts"] for r in con.execute(187 "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}188 con.close()189 for s in registry:190 s["active_formations"] = counts.get(s["id"], 0)191 s["last_sync"] = last.get(s["id"])192 return {"sources": registry}193194195@app.get("/api/stats")196def stats():197 con = db.connect()198 row = con.execute(199 """SELECT COUNT(*) total,200 SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) gratuites,201 SUM(CASE WHEN mode='en ligne' OR mode='asynchrone'202 THEN 1 ELSE 0 END) en_ligne,203 SUM(CASE WHEN training_type='Cours universitaire'204 THEN 1 ELSE 0 END) universitaires,205 COUNT(DISTINCT source) sources,206 AVG(price) avg_price,207 AVG(duration_hours) avg_hours208 FROM formations WHERE active=1""").fetchone()209 par_type = [dict(r) for r in con.execute(210 "SELECT training_type t, COUNT(*) n FROM formations WHERE active=1"211 " GROUP BY training_type ORDER BY n DESC")]212 log = [dict(r) for r in con.execute(213 "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]214 con.close()215 return {**dict(row), "par_type": par_type, "recent_syncs": log}216217218@app.post("/api/sync")219def trigger_sync(background: BackgroundTasks, source: str | None = None):220 """Déclenche une synchronisation (équivalent d'un webhook entrant)."""221 def _job():222 with _sync_lock:223 ingest.run([source] if source else None)224 background.add_task(_job)225 return {"status": "démarré", "source": source or "toutes"}226227228# --- Frontend React (build Vite) --------------------------------------------229if FRONTEND_DIST.exists():230 app.mount("/assets", StaticFiles(directory=FRONTEND_DIST / "assets"), name="assets")231232 @app.get("/{full_path:path}")233 def spa(full_path: str):234 target = FRONTEND_DIST / full_path235 if full_path and target.is_file():236 return FileResponse(target)237 return FileResponse(FRONTEND_DIST / "index.html")238