spb/fabri-ka

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 threading11import time12from datetime import date13from pathlib import Path1415from fastapi import BackgroundTasks, Body, FastAPI, HTTPException, Query, Request16from fastapi.middleware.gzip import GZipMiddleware17from fastapi.responses import FileResponse, RedirectResponse18from fastapi.staticfiles import StaticFiles1920from . import auth, db, hubfav, inscriptions, kaid, seo21from .schema import CATEGORIES, REGIONS2223app = FastAPI(title="Fabri-Ka API", docs_url="/api/docs", openapi_url="/api/openapi.json")24app.add_middleware(GZipMiddleware, minimum_size=1000)2526# Connexion « KA ID » (SSO Groupe KA) — AVANT le catch-all SPA plus bas27app.include_router(auth.router)28# Favoris « Mon univers Ka » (magasin central du hub Groupe KA, zéro stockage local)29app.include_router(hubfav.router)3031# personnalisation KA ID v2 (fabrika/kaid.py, canonique ka-ui.git/kaid)32kaid.init("fabri-ka")33app.include_router(kaid.build_router(auth.current_user))343536def _kaid_features(d: dict) -> dict:37    """Caractéristiques d'un produit pour le profil de préférences KA ID."""38    return {k: v for k, v in {39        "category": d.get("category"), "store": d.get("store_name"),40        "region": d.get("store_region"), "origin": d.get("origin_class"),41        "price": d.get("price"),42    }.items() if v not in (None, "")}43# Auto-inscription des artisans (« Inscrire mon atelier ») — quarantaine44app.include_router(inscriptions.router)4546FRONT_DIST = Path(__file__).resolve().parent.parent / "frontend" / "dist"474849def q(con: sqlite3.Connection, sql: str, args=()) -> list[dict]:50    return [dict(r) for r in con.execute(sql, args)]515253def _product_out(r: dict) -> dict:54    r["images"] = json.loads(r.get("images") or "[]")55    r["tags"] = json.loads(r.get("tags") or "[]")56    if "details" in r:57        r["details"] = json.loads(r["details"]) if r["details"] else None58    return r596061_FTS_SAFE = set("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789àâäéèêëîïôöùûüçÀÂÄÉÈÊËÎÏÔÖÙÛÜÇ- '")626364def fts_query(text: str) -> str:65    words = "".join(c if c in _FTS_SAFE else " " for c in text).split()66    return " ".join(f'"{w}"*' for w in words[:8])676869@app.get("/api/products")70def products(request: Request,71             q_text: str | None = Query(None, alias="q"),72             category: str | None = None,73             region: str | None = None,74             store: str | None = None,75             origin: str | None = None,76             price_min: float | None = None,77             price_max: float | None = None,78             available: bool | None = None,79             on_sale: bool | None = None,80             kind: str | None = None,81             sort: str = "recent",82             page: int = 1,83             per_page: int = Query(24, le=100)):84    con = db.connect()85    try:86        # listing_status : cartes-cadeaux/ateliers/abonnements/billets exclus87        # et aberrations en quarantaine ne sont jamais publiés88        where, args = ["p.active=1", "p.listing_status='published'"], []89        joins = "FROM products p JOIN stores s ON s.id = p.store_id"90        if kind == "fabricant":91            # « fabricants seulement » : écarte revendeurs et hors-mission92            # (les collectifs d'artisans restent : produits faits au Québec)93            where.append("COALESCE(s.store_kind,'') NOT IN ('revendeur','hors_mission')")94        if q_text:95            joins += " JOIN products_fts f ON f.uid = p.uid"96            where.append("products_fts MATCH ?")97            args.append(fts_query(q_text))98        if category:99            where.append("p.category=?"); args.append(category)100        if region:101            where.append("s.region=?"); args.append(region)102        if store:103            where.append("p.store_id=?"); args.append(store)104        if origin:105            where.append("s.origin_class IN (%s)" % ",".join("?" * len(origin.split(","))))106            args += origin.split(",")107        if price_min is not None:108            where.append("p.price>=?"); args.append(price_min)109        if price_max is not None:110            where.append("p.price<=?"); args.append(price_max)111        if available is not None:112            where.append("p.available=?"); args.append(int(available))113        if on_sale:114            # compare > price*10 (rabais >90 %) = donnée marchande aberrante115            # (prix barré saisi en cents chez certains Shopify) — exclue.116            where.append("p.compare_at_price IS NOT NULL AND p.price IS NOT NULL"117                         " AND p.compare_at_price > p.price"118                         " AND p.compare_at_price <= p.price * 10")119        wsql = " AND ".join(where)120        order = {"recent": "p.first_seen DESC",121                 "price_asc": "p.price IS NULL, p.price ASC",122                 "price_desc": "p.price IS NULL, p.price DESC",123                 "discount": ("(p.compare_at_price IS NULL OR p.price IS NULL"124                              " OR p.compare_at_price <= p.price"125                              " OR p.compare_at_price > p.price * 10),"126                              " (1.0 - p.price / p.compare_at_price) DESC"),127                 "title": "p.title COLLATE NOCASE"}.get(sort, "p.first_seen DESC")128        total = con.execute(f"SELECT COUNT(*) {joins} WHERE {wsql}", args).fetchone()[0]129        rows = q(con, f"""SELECT p.*, s.name AS store_name, s.region AS store_region,130                                 s.city AS store_city, s.origin_class, s.store_kind131                          {joins} WHERE {wsql} ORDER BY {order}132                          LIMIT ? OFFSET ?""", args + [per_page, (page - 1) * per_page])133        items = [_product_out(r) for r in rows]134        # personnalisation KA ID : journal + reclassement (tri par défaut135        # « recent » seulement ; intention de session > long terme)136        personalized = False137        user = auth.current_user(request)138        if user:139            filters = {k: v for k, v in {140                "category": category, "region": region, "store": store,141                "origin": origin, "price_min": price_min,142                "price_max": price_max, "on_sale": on_sale,143                "q": q_text}.items() if v not in (None, "", False)}144            if filters and page == 1:145                kaid.track(user, "search", query=q_text, filters=filters)146            if sort == "recent":147                active = {k for k in ("category", "region", "store", "origin")148                          if filters.get(k)}149                if price_min is not None or price_max is not None:150                    active.add("price")151                items, personalized = kaid.rerank(152                    items, user, features_of=_kaid_features, active_dims=active)153        return {"total": total, "page": page, "per_page": per_page,154                "items": items, "personalized": personalized}155    finally:156        con.close()157158159@app.get("/api/products/{uid}")160def product(uid: str, request: Request):161    con = db.connect()162    try:163        rows = q(con, """SELECT p.*, s.name AS store_name, s.region AS store_region,164                                s.city AS store_city, s.origin_class, s.store_kind,165                                s.url AS store_url166                         FROM products p JOIN stores s ON s.id=p.store_id WHERE p.uid=?""", (uid,))167        if not rows:168            raise HTTPException(404)169        out = _product_out(rows[0])170        out["related"] = [_product_out(r) for r in q(con, """171            SELECT p.*, s.name AS store_name FROM products p JOIN stores s ON s.id=p.store_id172            WHERE p.store_id=? AND p.uid<>? AND p.active=1 AND p.listing_status='published'173            ORDER BY RANDOM() LIMIT 8""",174            (rows[0]["store_id"], uid))]175        kaid.track(auth.current_user(request), "detail_view",176                   entity_type="product", entity_id=uid,177                   features=_kaid_features(out))178        return out179    finally:180        con.close()181182183@app.get("/api/stores")184def stores(region: str | None = None, platform: str | None = None,185           origin: str | None = None, q_text: str | None = Query(None, alias="q"),186           with_products: bool = False, include_closed: bool = False):187    con = db.connect()188    try:189        # les boutiques « présumées fermées » (échecs consécutifs) sont190        # dépubliées par défaut, réintégrables (include_closed=1)191        where, args = (["1=1"] if include_closed192                       else ["COALESCE(presumed_closed,0)=0"]), []193        if region:194            where.append("region=?"); args.append(region)195        if platform:196            where.append("platform=?"); args.append(platform)197        if origin:198            where.append("origin_class=?"); args.append(origin)199        if q_text:200            where.append("(name LIKE ? OR id LIKE ?)"); args += [f"%{q_text}%", f"%{q_text}%"]201        if with_products:202            where.append("product_count>0")203        rows = q(con, f"""SELECT id, name, url, platform, city, region, origin_class,204                                 origin_confidence, categories, language, product_count,205                                 last_sync, last_status, logo_url, cover_url,206                                 description_meta, store_kind, email, phone, lat, lng,207                                 shipping_info, certifications, presumed_closed208                          FROM stores WHERE {' AND '.join(where)}209                          ORDER BY product_count DESC, name""", args)210        for r in rows:211            r["categories"] = json.loads(r.get("categories") or "[]")212            r["certifications"] = json.loads(r.get("certifications") or "[]")213        return {"total": len(rows), "items": rows}214    finally:215        con.close()216217218@app.get("/api/stores/{store_id}")219def store_detail(store_id: str):220    con = db.connect()221    try:222        rows = q(con, "SELECT * FROM stores WHERE id=?", (store_id,))223        if not rows:224            raise HTTPException(404)225        s = rows[0]226        for k in ("categories", "socials", "discovery_sources", "certifications"):227            s[k] = json.loads(s.get(k) or "[]")228        # statistiques produits229        stats = q(con, """SELECT COUNT(*) AS n, MIN(price) AS price_min,230                                 MAX(price) AS price_max, AVG(price) AS price_avg231                          FROM products WHERE store_id=? AND active=1 AND listing_status='published' AND price>0""",232                  (store_id,))233        s["product_stats"] = stats[0] if stats else {}234        s["category_breakdown"] = q(con, """SELECT category AS key, COUNT(*) AS n235            FROM products WHERE store_id=? AND active=1 AND listing_status='published' GROUP BY category236            ORDER BY n DESC LIMIT 8""", (store_id,))237        for c in s["category_breakdown"]:238            c["label"] = CATEGORIES.get(c["key"], (c["key"], []))[0]239        # boutiques similaires : même catégorie dominante ou même région, avec produits240        topcat = s["category_breakdown"][0]["key"] if s["category_breakdown"] else None241        # Boutiques similaires en 2 temps INDEXÉS (l'ancien LEFT JOIN DISTINCT242        # prenait plusieurs minutes sur les grosses catégories — constaté le243        # 2026-08-19 après le passage à 347 k produits publiés) :244        # 1) boutiques ayant des produits publiés dans la catégorie dominante245        #    (idx_products_cat), 2) complément par région.246        region = s.get("region") or ""247        similar, seen_ids = [], {store_id}248        if topcat:249            for r in q(con, """250                SELECT st.id, st.name, st.region, st.origin_class, st.logo_url,251                       st.product_count252                FROM stores st JOIN (253                    SELECT store_id FROM products254                    WHERE category=? AND active=1 AND listing_status='published'255                    GROUP BY store_id) pc ON pc.store_id = st.id256                WHERE st.id<>? AND st.product_count>0257                ORDER BY (st.region=?) DESC, st.product_count DESC LIMIT 8""",258                    (topcat, store_id, region)):259                if r["id"] not in seen_ids:260                    seen_ids.add(r["id"])261                    similar.append(r)262        if len(similar) < 8 and region:263            for r in q(con, """264                SELECT id, name, region, origin_class, logo_url, product_count265                FROM stores WHERE id<>? AND product_count>0 AND region=?266                ORDER BY product_count DESC LIMIT 8""", (store_id, region)):267                if r["id"] not in seen_ids:268                    seen_ids.add(r["id"])269                    similar.append(r)270                if len(similar) >= 8:271                    break272        s["similar"] = similar[:8]273        return s274    finally:275        con.close()276277278@app.get("/api/facets")279def facets():280    con = db.connect()281    try:282        cats = q(con, """SELECT p.category AS key, COUNT(*) AS n FROM products p283                         WHERE p.active=1 AND p.listing_status='published'284                         GROUP BY p.category ORDER BY n DESC""")285        for c in cats:286            c["label"] = CATEGORIES.get(c["key"], (c["key"], []))[0]287        regions = q(con, """SELECT s.region AS key, COUNT(*) AS n FROM products p288                            JOIN stores s ON s.id=p.store_id289                            WHERE p.active=1 AND p.listing_status='published' AND s.region<>''290                            GROUP BY s.region ORDER BY n DESC""")291        origins = q(con, """SELECT s.origin_class AS key, COUNT(*) AS n FROM products p292                            JOIN stores s ON s.id=p.store_id293                            WHERE p.active=1 AND p.listing_status='published'294                            GROUP BY s.origin_class ORDER BY n DESC""")295        top_stores = q(con, """SELECT id AS key, name, product_count AS n FROM stores296                               WHERE product_count>0 ORDER BY n DESC LIMIT 40""")297        return {"categories": cats, "regions": regions, "origins": origins,298                "stores": top_stores, "all_regions": REGIONS,299                # taxonomie complète (formulaire « Inscrire mon atelier »)300                "all_categories": [{"key": k, "label": v[0]}301                                   for k, v in CATEGORIES.items()]}302    finally:303        con.close()304305306_stats_cache: dict = {"ts": 0.0, "data": None}307308309@app.get("/api/stats/extended")310def stats_extended():311    """Statistiques riches pour la page /stats : prix, catégories, croissance.312    Coûteux (médianes) → cache mémoire 10 minutes."""313    import time as _time314    if _stats_cache["data"] is not None and _time.time() - _stats_cache["ts"] < 600:315        return _stats_cache["data"]316    con = db.connect()317    try:318        def median_price(where_sql, args):319            n = con.execute(f"SELECT COUNT(*) FROM products WHERE {where_sql} AND price>0", args).fetchone()[0]320            if not n:321                return None322            row = con.execute(f"""SELECT price FROM products WHERE {where_sql} AND price>0323                                  ORDER BY price LIMIT 1 OFFSET ?""", args + [n // 2]).fetchone()324            return row[0] if row else None325326        by_category = []327        for r in q(con, """SELECT category AS key, COUNT(*) AS products,328                                  COUNT(DISTINCT store_id) AS stores,329                                  MIN(CASE WHEN price>0 THEN price END) AS price_min,330                                  ROUND(AVG(CASE WHEN price>0 AND price<=500000 THEN price END),2) AS price_avg,331                                  MAX(CASE WHEN price<=500000 THEN price END) AS price_max332                           FROM products WHERE active=1 AND listing_status='published' GROUP BY category333                           ORDER BY products DESC"""):334            r["label"] = CATEGORIES.get(r["key"], (r["key"], []))[0]335            r["price_median"] = median_price("active=1 AND listing_status='published' AND category=?", [r["key"]])336            by_category.append(r)337338        global_median = median_price("active=1 AND listing_status='published'", [])339        price_buckets = q(con, """SELECT CASE340                WHEN price < 10 THEN '0-10'341                WHEN price < 25 THEN '10-25'342                WHEN price < 50 THEN '25-50'343                WHEN price < 100 THEN '50-100'344                WHEN price < 250 THEN '100-250'345                WHEN price < 1000 THEN '250-1000'346                ELSE '1000+' END AS bucket, COUNT(*) AS n347            FROM products WHERE active=1 AND listing_status='published' AND price>0 GROUP BY bucket""")348        order = ['0-10', '10-25', '25-50', '50-100', '100-250', '250-1000', '1000+']349        price_buckets.sort(key=lambda b: order.index(b["bucket"]))350351        by_origin = q(con, """SELECT s.origin_class AS key, COUNT(DISTINCT s.id) AS stores,352                                     COUNT(p.uid) AS products353                              FROM stores s LEFT JOIN products p354                                   ON p.store_id=s.id AND p.active=1 AND p.listing_status='published'355                              GROUP BY s.origin_class ORDER BY products DESC""")356        by_platform = q(con, """SELECT platform AS key, COUNT(*) AS stores,357                                       SUM(product_count) AS products358                                FROM stores WHERE platform<>'' GROUP BY platform359                                ORDER BY products DESC""")360        by_region = q(con, """SELECT s.region AS key, COUNT(DISTINCT s.id) AS stores,361                                     COUNT(p.uid) AS products,362                                     ROUND(AVG(CASE WHEN p.price>0 AND p.price<=500000 THEN p.price END),2) AS price_avg363                              FROM stores s LEFT JOIN products p364                                   ON p.store_id=s.id AND p.active=1 AND p.listing_status='published'365                              WHERE s.region<>'' GROUP BY s.region ORDER BY products DESC""")366        growth = q(con, """SELECT date(first_seen,'unixepoch') AS day, COUNT(*) AS n367                           FROM products WHERE active=1 AND listing_status='published'368                           GROUP BY day ORDER BY day DESC LIMIT 30""")369        top_stores = q(con, """SELECT id, name, region, origin_class, logo_url,370                                      product_count AS products371                               FROM stores WHERE product_count>0372                               ORDER BY product_count DESC LIMIT 15""")373        most_expensive = q(con, """SELECT p.uid, p.title, p.price, s.name AS store_name374                                   FROM products p JOIN stores s ON s.id=p.store_id375                                   WHERE p.active=1 AND p.listing_status='published' AND p.price>0376                                   ORDER BY p.price DESC LIMIT 8""")377        newest = q(con, """SELECT p.uid, p.title, p.price, p.category, s.name AS store_name378                           FROM products p JOIN stores s ON s.id=p.store_id379                           WHERE p.active=1 AND p.listing_status='published' ORDER BY p.first_seen DESC LIMIT 12""")380        for r in newest:381            r["category_label"] = CATEGORIES.get(r["category"], (r["category"], []))[0]382        availability = q(con, """SELECT CASE WHEN available=1 THEN 'en stock'383                                             WHEN available=0 THEN 'rupture'384                                             ELSE 'inconnu' END AS key, COUNT(*) AS n385                                 FROM products WHERE active=1 AND listing_status='published' GROUP BY key""")386        coverage = q(con, """SELECT387            (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND images<>'[]' AND images IS NOT NULL) AS with_image,388            (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND description<>'') AS with_desc,389            (SELECT COUNT(*) FROM stores WHERE logo_url IS NOT NULL AND logo_url<>'') AS with_logo,390            (SELECT COUNT(*) FROM stores WHERE region<>'') AS with_region391        """)[0]392        totals = q(con, """SELECT393            (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published') AS products,394            (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published' AND price>0) AS products_priced,395            (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores_live,396            (SELECT COUNT(*) FROM stores) AS stores_registry,397            (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'') AS regions,398            (SELECT ROUND(AVG(price),2) FROM products WHERE active=1 AND listing_status='published' AND price>0 AND price<=500000) AS price_avg399        """)[0]400        totals["price_median"] = global_median401        data = {"totals": totals, "by_category": by_category,402                "price_buckets": price_buckets, "by_origin": by_origin,403                "by_platform": by_platform, "by_region": by_region,404                "growth": growth, "top_stores": top_stores,405                "most_expensive": most_expensive, "newest": newest,406                "availability": availability, "coverage": coverage}407        _stats_cache.update(ts=_time.time(), data=data)408        return data409    finally:410        con.close()411412413@app.get("/api/report.pdf")414def report_pdf():415    """Rapport de marché Fabri-Ka en PDF (logo, KPIs, prix, catégories,416    régions, origine, plateformes, top boutiques)."""417    from fastapi.responses import Response418    from . import report419    stats = stats_extended()420    pdf = report.build_report(stats)421    fname = f"rapport-marche-fabri-ka-{date.today()}.pdf"422    return Response(content=pdf, media_type="application/pdf",423                    headers={"Content-Disposition": f'attachment; filename="{fname}"'})424425426# ---- module Stats commun Groupe KA (contrat ka-stats SPEC.md) ---------------427@app.get("/api/stats/dashboard")428def stats_dashboard(period: str = "30j",429                    from_: str | None = Query(None, alias="from"),430                    to: str | None = None):431    """Tableau de bord analytique (KPI, séries, répartitions, geo, heatmap,432    tableaux, records) — données réelles, cache 5 min par période."""433    from . import statsdash434    try:435        return statsdash.dashboard(period, from_, to)436    except ValueError:437        raise HTTPException(400, "Dates invalides (format attendu YYYY-MM-DD)")438439440@app.get("/api/stats/report")441def stats_report(period: str = "30j",442                 from_: str | None = Query(None, alias="from"),443                 to: str | None = None,444                 mode: str = "complet"):445    """Rapport statistique PDF estampillé Groupe-KA (gabarit kapdf/fpdf2)."""446    from fastapi.responses import Response447448    from . import kapdf, statsdash449    try:450        dash = statsdash.dashboard(period, from_, to)451    except ValueError:452        raise HTTPException(400, "Dates invalides (format attendu YYYY-MM-DD)")453    site = {"wordmark": "Fabri·Ka", "accent": "#c4532e",454            "domain": "www.fabri-ka.com",455            "tagline": "Tous les produits québécois. Un seul endroit."}456    if mode not in kapdf.REPORT_MODES:457        mode = "complet"458    pdf = kapdf.GroupeKAReport(459        site=site, dashboard=dash, mode=mode).build()460    fname = kapdf.filename("fabri-ka", period, mode)461    return Response(content=pdf, media_type="application/pdf",462                    headers={"Content-Disposition": f'attachment; filename="{fname}"'})463464465466@app.get("/api/stats/catalog")467def stats_catalog(period: str = "30j",468                  from_: str | None = Query(None, alias="from"),469                  to: str | None = None):470    """v3 — blocs composables pour le constructeur de rapports personnalisés."""471    from . import kapdf, statsdash472    try:473        dash = statsdash.dashboard(period, from_, to)474    except ValueError:475        raise HTTPException(400, "Dates invalides (format attendu YYYY-MM-DD)")476    return {"updated": dash.get("updated"), "period": dash.get("period"),477            "blocks": kapdf.catalog(dash)}478479480@app.post("/api/stats/report/custom")481def stats_report_custom(spec: dict = Body(...)):482    """v3 — rapport PDF personnalisé : {"title", "period", "from", "to",483    "blocks": [{"key": "series:…", "render": "bar"}, …]} (SPEC.md §3bis)."""484    from fastapi.responses import Response485486    from . import kapdf, statsdash487    period = str(spec.get("period") or "30j")488    try:489        dash = statsdash.dashboard(period, str(spec.get("from") or "") or None,490                                   str(spec.get("to") or "") or None)491    except ValueError:492        raise HTTPException(400, "Dates invalides (format attendu YYYY-MM-DD)")493    site = {"wordmark": "Fabri·Ka", "accent": "#c4532e",494            "domain": "www.fabri-ka.com",495            "tagline": "Tous les produits québécois. Un seul endroit."}496    known = {b["key"] for b in kapdf.catalog(dash)}497    blocks = [b for b in (spec.get("blocks") or [])498              if isinstance(b, dict) and b.get("key") in known][:40]499    if not blocks:500        raise HTTPException(400, "Aucun bloc valide dans la composition")501    pdf = kapdf.GroupeKAReport(502        site=site, dashboard=dash, mode=kapdf.CUSTOM_MODE,503        spec={"title": str(spec.get("title") or "")[:80], "blocks": blocks},504    ).build()505    fname = kapdf.filename("fabri-ka", period, kapdf.CUSTOM_MODE)506    return Response(content=pdf, media_type="application/pdf",507                    headers={"Content-Disposition": f'attachment; filename="{fname}"'})508509@app.get("/api/stats")510def stats(syncs_since_h: float | None = None):511    """Stats globales. `syncs_since_h` (heures) : fenêtre du sync_log exposé —512    sans le paramètre, comportement historique (30 dernières entrées) ; avec,513    toutes les entrées depuis now-syncs_since_h (LIMIT 4000), et au moins les514    20 dernières si la fenêtre en contient moins (supervision multi-boutiques :515    fabri-ka fait tourner ~1 700 boutiques par cycle, 30 entrées ne couvrent516    qu'une poignée de magasins)."""517    con = db.connect()518    try:519        totals = q(con, """SELECT520            (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published') AS products,521            (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores_live,522            (SELECT COUNT(*) FROM stores) AS stores_registry,523            (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'' AND product_count>0) AS regions524        """)[0]525        by_region = q(con, """SELECT region, COUNT(*) AS stores, SUM(product_count) AS products526                              FROM stores WHERE region<>'' GROUP BY region ORDER BY products DESC""")527        # Les stores enabled=0 (faux positifs de découverte, boutiques fermées…)528        # sont exclus du flux : leurs derniers syncs à 0 restent sinon figés dans529        # la fenêtre et la supervision api-ka les compte comme « broken » à vie.530        active = "store_id NOT IN (SELECT id FROM stores WHERE enabled = 0)"531        if syncs_since_h is not None:532            cutoff = time.time() - syncs_since_h * 3600533            recent = q(con, f"""SELECT ts, store_id, found, added, updated, removed, status534                                FROM sync_log WHERE ts >= ? AND {active}535                                ORDER BY ts DESC LIMIT 4000""",536                       (cutoff,))537            if len(recent) < 20:538                recent = q(con, f"""SELECT ts, store_id, found, added, updated, removed, status539                                    FROM sync_log WHERE {active} ORDER BY ts DESC LIMIT 20""")540        else:541            recent = q(con, f"""SELECT ts, store_id, found, added, updated, removed, status542                                FROM sync_log WHERE {active} ORDER BY ts DESC LIMIT 30""")543        # Stores volontairement désactivés (faux positifs de découverte, boutiques544        # fermées…) mais présents dans le sync_log : sans ce signal, la supervision545        # api-ka les fige en « broken » car ils n'émettent plus jamais de syncs.546        disabled = q(con, """SELECT DISTINCT st.id FROM stores st547                             JOIN sync_log sl ON sl.store_id = st.id548                             WHERE st.enabled = 0 ORDER BY st.id""")549        return {"totals": totals, "by_region": by_region, "sync_log": recent,550                "disabled_stores": [r["id"] for r in disabled]}551    finally:552        con.close()553554555_sync_running = threading.Lock()556557558@app.post("/api/sync")559def trigger_sync(background: BackgroundTasks):560    if _sync_running.locked():561        return {"status": "already-running"}562563    def _job():564        with _sync_running:565            from . import ingest566            ingest.run()567568    background.add_task(_job)569    return {"status": "started"}570571572# ---- SEO : robots + sitemaps -------------------------------------------------573@app.get("/robots.txt")574def robots():575    return seo.robots_txt()576577578@app.get("/sitemap.xml")579def sitemap_index():580    return seo.sitemap_index()581582583@app.get("/sitemap-{name}.xml")584def sitemap(name: str):585    return seo.sitemap(name)586587588# ---- frontend ---------------------------------------------------------------589class HashedStaticFiles(StaticFiles):590    """Assets Vite hashés -> cache navigateur agressif."""591592    def file_response(self, *args, **kwargs):593        resp = super().file_response(*args, **kwargs)594        resp.headers["Cache-Control"] = "public, max-age=31536000, immutable"595        return resp596597598if FRONT_DIST.exists():599    app.mount("/assets", HashedStaticFiles(directory=FRONT_DIST / "assets"), name="assets")600601    @app.get("/doc", include_in_schema=False)602    def doc_redirect():603        return RedirectResponse("/doc/", status_code=301)604605    # /doc : documentation statique (index.html + captures + PDF) — montée606    # explicitement pour que le catch-all SPA ne l'intercepte pas.607    if (FRONT_DIST / "doc").is_dir():608        app.mount("/doc", StaticFiles(directory=FRONT_DIST / "doc", html=True), name="doc")609610    @app.get("/{path:path}")611    def spa(path: str, request: Request):612        target = FRONT_DIST / path613        if path and target.is_file():614            resp = FileResponse(target)615            resp.headers["Cache-Control"] = "public, max-age=86400"616            return resp617        # Rendu serveur SEO : HTML complet (meta + contenu) par type de page,618        # avec les bons statuts (200 / 301 slug canonique / 404 / 410).619        return seo.render_route(path, request.query_params)620