Agrégateur de produits québécois — www.fabri-ka.com
Python 38.4%
HTML 30.3%
TypeScript 17.2%
CSS 11%
JavaScript 3.2%
1# -----------------------------------------------------------------------------2# Fabri-Ka — Agrégateur de produits québécois3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# seo.py : rendu serveur pour l'indexation — chaque route livre son contenu5# essentiel en HTML (title/meta/canonical/og/JSON-LD + corps de page),6# robots.txt, sitemaps, redirections 301 et statuts 404/410.7# React ré-hydrate par-dessus (createRoot remplace #root au montage).8# -----------------------------------------------------------------------------9from __future__ import annotations1011import html12import json13import os14import re15import sqlite316import threading17import time18import unicodedata19from datetime import date, datetime, timezone20from pathlib import Path2122from fastapi.responses import HTMLResponse, PlainTextResponse, RedirectResponse, Response2324from . import db25from .schema import CATEGORIES, REGIONS2627BASE = os.environ.get("FABRIKA_BASE_URL", "https://www.fabri-ka.com").rstrip("/")28SITE = "Fabri-Ka"29DEFAULT_TITLE = ("Fabri-Ka — Tous les produits québécois. Un seul endroit."30 " · Un service Groupe KA")31FRONT_DIST = Path(__file__).resolve().parent.parent / "frontend" / "dist"32PER_PAGE = 2433SITEMAP_CHUNK = 40_0003435ORIGIN_LABELS = {"A": "Fabriqué au Québec", "B": "Conçu au Québec",36 "C": "Détaillant québécois", "D": "Mixte", "E": "À vérifier"}373839# ---- slugs -------------------------------------------------------------------4041def slugify(text: str) -> str:42 text = unicodedata.normalize("NFKD", text or "")43 text = "".join(c for c in text if not unicodedata.combining(c))44 return re.sub(r"[^a-z0-9]+", "-", text.lower()).strip("-")454647CAT_SLUG = {key: slugify(key) for key in CATEGORIES} # cafe_the -> cafe-the48SLUG_CAT = {v: k for k, v in CAT_SLUG.items()}49REGION_SLUG = {r: slugify(r) for r in REGIONS} # Montérégie -> monteregie50SLUG_REGION = {v: k for k, v in REGION_SLUG.items()}515253def cat_label(key: str) -> str:54 return CATEGORIES.get(key, (key or "Autres", []))[0]555657def product_path(uid: str, title: str) -> str:58 slug = slugify(title or "")[:70].rstrip("-")59 return f"/produits/{slug}-{uid}" if slug else f"/produits/{uid}"606162# ---- helpers -----------------------------------------------------------------6364def esc(s) -> str:65 return html.escape(str(s or ""), quote=True)666768def fmt_int(n) -> str:69 return f"{int(n or 0):,}".replace(",", " ")707172def fmt_price(v) -> str:73 if v is None:74 return ""75 return f"{v:,.2f}".replace(",", " ").replace(".", ",") + " $"767778def clean_text(s: str, limit: int = 160) -> str:79 s = re.sub(r"<[^>]+>", " ", s or "")80 s = re.sub(r"\s+", " ", s).strip()81 if len(s) > limit:82 s = s[: limit - 1].rsplit(" ", 1)[0] + "…"83 return s848586def iso_date(epoch) -> str:87 try:88 return datetime.fromtimestamp(float(epoch), tz=timezone.utc).strftime("%Y-%m-%d")89 except (TypeError, ValueError):90 return date.today().isoformat()919293_cache: dict[str, tuple[float, object]] = {}94_cache_lock = threading.Lock()959697def cached(key: str, ttl: float, fn):98 now = time.time()99 with _cache_lock:100 hit = _cache.get(key)101 if hit and now - hit[0] < ttl:102 return hit[1]103 val = fn()104 with _cache_lock:105 _cache[key] = (time.time(), val)106 return val107108109def q(con: sqlite3.Connection, sql: str, args=()) -> list[dict]:110 return [dict(r) for r in con.execute(sql, args)]111112113# ---- gabarit HTML ------------------------------------------------------------114115_tpl = {"mtime": 0.0, "text": ""}116117_FALLBACK_TPL = """<!doctype html><html lang="fr"><head><meta charset="UTF-8">118<meta name="viewport" content="width=device-width, initial-scale=1.0">119<meta name="description" content=""><title>Fabri-Ka</title></head>120<body><div id="root"></div></body></html>"""121122123def template() -> str:124 p = FRONT_DIST / "index.html"125 try:126 m = p.stat().st_mtime127 except OSError:128 return _FALLBACK_TPL129 if m != _tpl["mtime"]:130 _tpl.update(mtime=m, text=p.read_text(encoding="utf-8"))131 return _tpl["text"]132133134def render(*, title: str, description: str, canonical: str | None = None,135 body: str = "", jsonld: list[dict] | None = None,136 og: dict | None = None, robots: str | None = None,137 status: int = 200) -> HTMLResponse:138 tpl = template()139 tpl = re.sub(r"<title>[^<]*</title>", f"<title>{esc(title)}</title>", tpl, count=1)140 tpl = re.sub(r'<meta[^>]*name="description"[^>]*>',141 f'<meta name="description" content="{esc(description)}" />', tpl, count=1)142143 extra: list[str] = []144 if canonical:145 url = BASE + canonical146 extra.append(f'<link rel="canonical" href="{esc(url)}" />')147 extra.append(f'<link rel="alternate" hreflang="fr-CA" href="{esc(url)}" />')148 extra.append(f'<link rel="alternate" hreflang="x-default" href="{esc(url)}" />')149 if robots:150 extra.append(f'<meta name="robots" content="{esc(robots)}" />')151 og_all = {"og:site_name": SITE, "og:locale": "fr_CA", "og:type": "website",152 "og:title": title, "og:description": description}153 if canonical:154 og_all["og:url"] = BASE + canonical155 custom = og or {}156 if not custom.get("og:image"):157 og_all["og:image"] = f"{BASE}/og.png"158 og_all["og:image:width"] = "1200"159 og_all["og:image:height"] = "630"160 og_all.update(custom)161 # les balises og/twitter statiques du gabarit sont remplacées par celles-ci162 tpl = re.sub(r'\s*<meta (?:property="og:|name="twitter:)[^>]*/?>', "", tpl)163 for k, v in og_all.items():164 if v:165 extra.append(f'<meta property="{esc(k)}" content="{esc(v)}" />')166 extra.append('<meta name="twitter:card" content="summary_large_image" />')167 extra.append(f'<meta name="twitter:image" content="{esc(og_all["og:image"])}" />')168 for obj in jsonld or []:169 payload = json.dumps(obj, ensure_ascii=False).replace("</", "<\\/")170 extra.append(f'<script type="application/ld+json">{payload}</script>')171172 tpl = tpl.replace("</head>", "\n".join(extra) + "\n</head>", 1)173 tpl = tpl.replace('<div id="root"></div>', f'<div id="root">{body}</div>', 1)174 return HTMLResponse(tpl, status_code=status)175176177def breadcrumb_ld(items: list[tuple[str, str]]) -> dict:178 return {"@context": "https://schema.org", "@type": "BreadcrumbList",179 "itemListElement": [180 {"@type": "ListItem", "position": i + 1, "name": name,181 "item": BASE + path}182 for i, (name, path) in enumerate(items)]}183184185# ---- fragments HTML ----------------------------------------------------------186187def _img(src: str, alt: str, eager: bool = False) -> str:188 return (f'<img src="{esc(src)}" alt="{esc(alt)}" width="400" height="400" '189 f'loading="{"eager" if eager else "lazy"}" decoding="async" />')190191192def product_li(p: dict) -> str:193 href = product_path(p["uid"], p.get("title") or "")194 img = ""195 images = p.get("images")196 if isinstance(images, str):197 images = json.loads(images or "[]")198 if images:199 img = f'<span class="seo-card-media">{_img(images[0], p.get("title") or "")}</span>'200 price = fmt_price(p.get("price")) or "Prix en boutique"201 store = " · ".join(x for x in [p.get("store_name"), p.get("store_region")] if x)202 return (f'<li class="seo-card">{img}'203 f'<a href="{esc(href)}">{esc(p.get("title"))}</a>'204 f'<span class="seo-card-price">{esc(price)}</span>'205 f'<span class="seo-card-store">{esc(store)}</span></li>')206207208def product_grid(products: list[dict]) -> str:209 return '<ul class="seo-grid">' + "".join(product_li(p) for p in products) + "</ul>"210211212def link_list(links: list[tuple[str, str, str]]) -> str:213 """[(href, label, suffix)] -> nav en liste."""214 lis = "".join(f'<li><a href="{esc(h)}">{esc(t)}</a>'215 + (f' <small>{esc(s)}</small>' if s else "") + "</li>"216 for h, t, s in links)217 return f'<ul class="seo-links">{lis}</ul>'218219220def pagination_html(path: str, page: int, total: int, per_page: int = PER_PAGE) -> str:221 pages = max(1, (total + per_page - 1) // per_page)222 if pages <= 1:223 return ""224 parts = ['<nav class="seo-pagination" aria-label="Pagination">']225 if page > 1:226 prev = path if page == 2 else f"{path}?page={page - 1}"227 parts.append(f'<a rel="prev" href="{esc(prev)}">← Page précédente</a>')228 parts.append(f"<span>Page {fmt_int(page)} de {fmt_int(pages)}</span>")229 if page < pages:230 parts.append(f'<a rel="next" href="{esc(path)}?page={page + 1}">Page suivante →</a>')231 parts.append("</nav>")232 return "".join(parts)233234235# ---- requêtes ----------------------------------------------------------------236237def list_products(con, category: str | None, region: str | None,238 page: int, per_page: int = PER_PAGE) -> tuple[int, list[dict]]:239 where, args = ["p.active=1 AND p.listing_status='published'"], []240 if category:241 where.append("p.category=?"); args.append(category)242 if region:243 where.append("s.region=?"); args.append(region)244 wsql = " AND ".join(where)245 total = con.execute(f"""SELECT COUNT(*) FROM products p246 JOIN stores s ON s.id=p.store_id WHERE {wsql}""", args).fetchone()[0]247 rows = q(con, f"""SELECT p.uid, p.title, p.price, p.images,248 s.name AS store_name, s.region AS store_region249 FROM products p JOIN stores s ON s.id=p.store_id250 WHERE {wsql} ORDER BY p.first_seen DESC LIMIT ? OFFSET ?""",251 args + [per_page, (page - 1) * per_page])252 return total, rows253254255def listing_stats(category: str | None, region: str | None) -> dict:256 def _run():257 con = db.connect()258 try:259 where, args = ["p.active=1 AND p.listing_status='published'", "p.price>0", "p.price<=500000"], []260 if category:261 where.append("p.category=?"); args.append(category)262 if region:263 where.append("s.region=?"); args.append(region)264 wsql = " AND ".join(where)265 base = f"FROM products p JOIN stores s ON s.id=p.store_id WHERE {wsql}"266 row = con.execute(f"""SELECT COUNT(*), MIN(p.price), MAX(p.price),267 ROUND(AVG(p.price),2) {base}""", args).fetchone()268 n, pmin, pmax, pavg = row269 median = None270 if n:271 r = con.execute(f"SELECT p.price {base} ORDER BY p.price LIMIT 1 OFFSET ?",272 args + [n // 2]).fetchone()273 median = r[0] if r else None274 return {"priced": n, "min": pmin, "max": pmax, "avg": pavg, "median": median}275 finally:276 con.close()277 return cached(f"lstats:{category}:{region}", 600, _run)278279280def combo_counts() -> list[dict]:281 """(catégorie, région) -> nb produits + lastmod. Sert au maillage et aux sitemaps."""282 def _run():283 con = db.connect()284 try:285 return q(con, """SELECT p.category AS cat, s.region AS region,286 COUNT(*) AS n, MAX(p.last_seen) AS m287 FROM products p JOIN stores s ON s.id=p.store_id288 WHERE p.active=1 AND p.listing_status='published' GROUP BY p.category, s.region""")289 finally:290 con.close()291 return cached("combos", 3600, _run)292293294def stats_line(total: int, st: dict) -> str:295 bits = [f"<b>{fmt_int(total)}</b> produits"]296 if st.get("avg") is not None:297 bits.append(f"prix moyen <b>{fmt_price(st['avg'])}</b>")298 if st.get("median") is not None:299 bits.append(f"prix médian <b>{fmt_price(st['median'])}</b>")300 if st.get("min") is not None and st.get("max") is not None:301 bits.append(f"de {fmt_price(st['min'])} à {fmt_price(st['max'])}")302 return f'<p class="seo-stats">{" · ".join(bits)}.</p>'303304305# ---- pages -------------------------------------------------------------------306307def page_home() -> HTMLResponse:308 def _data():309 con = db.connect()310 try:311 totals = q(con, """SELECT312 (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published') AS products,313 (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores,314 (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'' AND product_count>0) AS regions315 """)[0]316 cats = q(con, """SELECT category AS key, COUNT(*) AS n FROM products317 WHERE active=1 AND listing_status='published' GROUP BY category ORDER BY n DESC""")318 regions = q(con, """SELECT s.region AS key, COUNT(*) AS n FROM products p319 JOIN stores s ON s.id=p.store_id320 WHERE p.active=1 AND p.listing_status='published' AND s.region<>'' GROUP BY s.region ORDER BY n DESC""")321 top_stores = q(con, """SELECT id, name, product_count AS n FROM stores322 WHERE product_count>0 ORDER BY n DESC LIMIT 20""")323 newest = q(con, """SELECT p.uid, p.title, p.price, p.images,324 s.name AS store_name, s.region AS store_region325 FROM products p JOIN stores s ON s.id=p.store_id326 WHERE p.active=1 AND p.listing_status='published' ORDER BY p.first_seen DESC LIMIT 12""")327 return totals, cats, regions, top_stores, newest328 finally:329 con.close()330331 totals, cats, regions, top_stores, newest = cached("home", 600, _data)332 desc = (f"Fabri-Ka agrège {fmt_int(totals['products'])} produits de "333 f"{fmt_int(totals['stores'])} boutiques en ligne québécoises, dans les "334 f"{totals['regions']} régions du Québec. Produits fabriqués, conçus ou "335 f"vendus au Québec, tous au même endroit.")336 body = f"""337<div class="seo-page">338<h1>Tous les produits québécois. Un seul endroit.</h1>339<p>{esc(desc)}</p>340<p><a href="/produits">Voir les {fmt_int(totals["products"])} produits</a> ·341 <a href="/boutiques">{fmt_int(totals["stores"])} boutiques</a> ·342 <a href="/stats">Statistiques</a> · <a href="/a-propos">À propos</a> ·343 <a href="/contact">Contact</a> ·344 <a href="https://www.groupe-ka.com">Un service Groupe KA</a></p>345<h2>Parcourir par catégorie</h2>346{link_list([(f"/categorie/{CAT_SLUG.get(c['key'], slugify(c['key'] or 'autre'))}",347 cat_label(c["key"]), f"{fmt_int(c['n'])} produits")348 for c in cats if c["key"] in CAT_SLUG])}349<h2>Parcourir par région</h2>350{link_list([(f"/region/{REGION_SLUG[r['key']]}", r["key"], f"{fmt_int(r['n'])} produits")351 for r in regions if r["key"] in REGION_SLUG])}352<h2>Boutiques populaires</h2>353{link_list([(f"/boutiques/{s['id']}", s["name"], f"{fmt_int(s['n'])} produits")354 for s in top_stores])}355<h2>Nouveaux produits</h2>356{product_grid(newest)}357</div>"""358 jsonld = [359 {"@context": "https://schema.org", "@type": "WebSite", "url": BASE + "/",360 "name": SITE, "inLanguage": "fr-CA",361 "potentialAction": {"@type": "SearchAction",362 "target": {"@type": "EntryPoint",363 "urlTemplate": BASE + "/produits?q={search_term_string}"},364 "query-input": "required name=search_term_string"}},365 {"@context": "https://schema.org", "@type": "Organization",366 "name": SITE, "url": BASE + "/", "email": "contact@spboucher.ai"},367 ]368 return render(title=DEFAULT_TITLE, description=desc, canonical="/", body=body, jsonld=jsonld)369370371def page_listing(category: str | None, region: str | None, params) -> Response:372 try:373 page = max(1, int(params.get("page", "1")))374 except ValueError:375 page = 1376377 if category:378 path = f"/categorie/{CAT_SLUG[category]}"379 if region:380 path += f"/region/{REGION_SLUG[region]}"381 elif region:382 path = f"/region/{REGION_SLUG[region]}"383 else:384 path = "/produits"385386 con = db.connect()387 try:388 total, rows = list_products(con, category, region, page)389 finally:390 con.close()391 pages = max(1, (total + PER_PAGE - 1) // PER_PAGE)392 if path != "/produits" and total == 0:393 return page_404()394 if page > pages:395 return page_404()396397 label = cat_label(category) if category else None398 if category and region:399 h1 = f"{label} — {region}"400 title = f"{label} — {region} | {SITE}"401 desc = (f"{fmt_int(total)} produits « {label} » offerts par des boutiques de la région "402 f"{region}. Comparez les produits québécois sur Fabri-Ka.")403 elif category:404 h1 = f"{label} du Québec"405 title = f"{label} du Québec — {fmt_int(total)} produits | {SITE}"406 desc = (f"{fmt_int(total)} produits « {label} » de boutiques en ligne québécoises, "407 f"réunis sur Fabri-Ka. Prix, disponibilité et boutiques d'origine.")408 elif region:409 h1 = f"Produits québécois — {region}"410 title = f"Produits de la région {region} — {fmt_int(total)} produits | {SITE}"411 desc = (f"{fmt_int(total)} produits offerts par les boutiques en ligne de la région "412 f"{region}, réunis sur Fabri-Ka.")413 else:414 h1 = "Produits"415 title = f"Tous les produits québécois — {fmt_int(total)} produits | {SITE}"416 desc = (f"Parcourez {fmt_int(total)} produits de boutiques en ligne québécoises : "417 f"alimentation, mode, maison, art et plus. Filtrez par catégorie, région et prix.")418419 st = listing_stats(category, region)420 combos = combo_counts()421422 nav_sections: list[str] = []423 if category and region:424 sib_r = [(f"/categorie/{CAT_SLUG[category]}/region/{REGION_SLUG[c['region']]}",425 c["region"], f"{fmt_int(c['n'])} produits")426 for c in combos if c["cat"] == category and c["region"] != region427 and c["region"] in REGION_SLUG and c["n"] > 0]428 sib_c = [(f"/categorie/{CAT_SLUG[c['cat']]}/region/{REGION_SLUG[region]}",429 cat_label(c["cat"]), f"{fmt_int(c['n'])} produits")430 for c in combos if c["region"] == region and c["cat"] != category431 and c["cat"] in CAT_SLUG and c["n"] > 0]432 if sib_r:433 nav_sections.append(f"<h2>{esc(label)} dans les autres régions</h2>" + link_list(sorted(sib_r, key=lambda x: x[1])))434 if sib_c:435 nav_sections.append(f"<h2>Autres catégories — {esc(region)}</h2>" + link_list(sib_c[:20]))436 elif category:437 by_region: dict[str, int] = {}438 for c in combos:439 if c["cat"] == category and c["region"] in REGION_SLUG:440 by_region[c["region"]] = by_region.get(c["region"], 0) + c["n"]441 links = [(f"/categorie/{CAT_SLUG[category]}/region/{REGION_SLUG[r]}", r,442 f"{fmt_int(n)} produits") for r, n in sorted(by_region.items(), key=lambda x: -x[1]) if n > 0]443 if links:444 nav_sections.append("<h2>Par région</h2>" + link_list(links))445 others = [(f"/categorie/{s}", cat_label(k), "") for k, s in CAT_SLUG.items() if k != category]446 nav_sections.append("<h2>Autres catégories</h2>" + link_list(others))447 elif region:448 by_cat: dict[str, int] = {}449 for c in combos:450 if c["region"] == region and c["cat"] in CAT_SLUG:451 by_cat[c["cat"]] = by_cat.get(c["cat"], 0) + c["n"]452 links = [(f"/categorie/{CAT_SLUG[k]}/region/{REGION_SLUG[region]}", cat_label(k),453 f"{fmt_int(n)} produits") for k, n in sorted(by_cat.items(), key=lambda x: -x[1]) if n > 0]454 if links:455 nav_sections.append(f"<h2>Catégories — {esc(region)}</h2>" + link_list(links))456 others = [(f"/region/{s}", r, "") for r, s in REGION_SLUG.items() if r != region]457 nav_sections.append("<h2>Autres régions</h2>" + link_list(others))458 else:459 nav_sections.append("<h2>Par catégorie</h2>" +460 link_list([(f"/categorie/{s}", cat_label(k), "") for k, s in CAT_SLUG.items()]))461 nav_sections.append("<h2>Par région</h2>" +462 link_list([(f"/region/{s}", r, "") for r, s in REGION_SLUG.items()]))463464 crumbs = [("Accueil", "/"), ("Produits", "/produits")]465 if category:466 crumbs.append((label, f"/categorie/{CAT_SLUG[category]}"))467 if region:468 crumbs.append((region, path))469470 body = f"""471<div class="seo-page">472<nav class="breadcrumb">{' / '.join(f'<a href="{esc(p)}">{esc(n)}</a>' for n, p in crumbs)}</nav>473<h1>{esc(h1)}</h1>474{stats_line(total, st)}475{product_grid(rows)}476{pagination_html(path, page, total)}477{"".join(nav_sections)}478</div>"""479 canonical = path if page == 1 else f"{path}?page={page}"480 jsonld = [breadcrumb_ld(crumbs),481 {"@context": "https://schema.org", "@type": "CollectionPage",482 "name": h1, "url": BASE + canonical, "inLanguage": "fr-CA",483 "mainEntity": {"@type": "ItemList", "numberOfItems": total,484 "itemListElement": [485 {"@type": "ListItem", "position": i + 1,486 "url": BASE + product_path(r["uid"], r["title"] or "")}487 for i, r in enumerate(rows)]}}]488 if page > 1:489 title = f"{h1} — page {page} | {SITE}"490 return render(title=title, description=desc, canonical=canonical, body=body, jsonld=jsonld)491492493def page_product(slug: str | None, uid: str, params) -> Response:494 con = db.connect()495 try:496 rows = q(con, """SELECT p.*, s.name AS store_name, s.region AS store_region,497 s.city AS store_city, s.origin_class, s.url AS store_url498 FROM products p JOIN stores s ON s.id=p.store_id WHERE p.uid=?""", (uid,))499 if not rows:500 return page_404()501 p = rows[0]502 canonical = product_path(uid, p["title"] or "")503504 if not p["active"]:505 # 410 Gone — produit retiré : jamais de soft-404506 cat = p.get("category")507 back = f"/categorie/{CAT_SLUG[cat]}" if cat in CAT_SLUG else "/produits"508 back_label = cat_label(cat) if cat in CAT_SLUG else "tous les produits"509 body = f"""510<div class="seo-page">511<h1>Ce produit n'est plus offert</h1>512<p>« {esc(p["title"])} » a été retiré du catalogue de {esc(p["store_name"])}.</p>513<p><a href="{esc(back)}">Voir les produits similaires : {esc(back_label)}</a> ·514 <a href="/produits">Tous les produits</a></p>515</div>"""516 return render(title=f"Produit retiré — {SITE}",517 description="Ce produit n'est plus offert. Découvrez des produits québécois similaires.",518 body=body, robots="noindex", status=410)519520 # URL canonique : /produits/<slug>-<uid> — 301 depuis toute variante521 expected_slug = slugify(p["title"] or "")[:70].rstrip("-") or None522 if slug != expected_slug:523 return RedirectResponse(canonical, status_code=301)524525 related = q(con, """SELECT p.uid, p.title, p.price, p.images, s.name AS store_name,526 s.region AS store_region527 FROM products p JOIN stores s ON s.id=p.store_id528 WHERE p.store_id=? AND p.uid<>? AND p.active=1 AND p.listing_status='published'529 ORDER BY p.first_seen DESC LIMIT 8""", (p["store_id"], uid))530 finally:531 con.close()532533 images = json.loads(p.get("images") or "[]")534 title_txt = p["title"] or "Produit"535 price_txt = fmt_price(p.get("price"))536 store_loc = ", ".join(x for x in [p.get("store_city"), p.get("store_region")] if x) or "Québec"537 desc_meta = clean_text(p.get("description") or "", 150)538 meta_desc = " — ".join(x for x in [539 title_txt if not desc_meta else desc_meta,540 f"{price_txt}" if price_txt else "",541 f"offert par {p['store_name']} ({store_loc})",542 ] if x)543 meta_desc = clean_text(meta_desc, 160)544545 cat = p.get("category")546 crumbs = [("Accueil", "/"), ("Produits", "/produits")]547 if cat in CAT_SLUG:548 crumbs.append((cat_label(cat), f"/categorie/{CAT_SLUG[cat]}"))549 crumbs.append((title_txt, canonical))550551 gallery = "".join(552 f'<figure>{_img(src, title_txt if i == 0 else "", eager=(i == 0))}</figure>'553 for i, src in enumerate(images[:6]))554 meta_rows = "".join(555 f"<div><dt>{esc(k)}</dt><dd>{esc(v)}</dd></div>"556 for k, v in [("Marque", p.get("vendor")),557 ("Catégorie", cat_label(cat) if cat else None),558 ("Type", p.get("product_type")),559 ("Origine", ORIGIN_LABELS.get(p.get("origin_class") or "")),560 ("Région", p.get("store_region"))] if v)561 avail_txt = ("En stock" if p.get("available") == 1562 else "Non disponible" if p.get("available") == 0 else "")563 body = f"""564<div class="seo-page">565<nav class="breadcrumb">{' / '.join(f'<a href="{esc(pt)}">{esc(n)}</a>' for n, pt in crumbs[:-1])}</nav>566<article>567<h1>{esc(title_txt)}</h1>568<p class="seo-stats"><b>{esc(price_txt or "Prix affiché en boutique")}</b>{(" · " + esc(avail_txt)) if avail_txt else ""}</p>569<div class="seo-gallery">{gallery}</div>570<p>{esc(clean_text(p.get("description") or "", 1200))}</p>571<dl class="seo-meta">{meta_rows}</dl>572<p><a href="{esc(p.get("url"))}" rel="nofollow noopener">Voir chez {esc(p["store_name"])} ↗</a> ·573 <a href="/boutiques/{esc(p["store_id"])}">Tous les produits de {esc(p["store_name"])}</a>574 ({esc(store_loc)})</p>575</article>576<h2>Produits similaires</h2>577{product_grid(related)}578</div>"""579580 availability = {1: "https://schema.org/InStock", 0: "https://schema.org/OutOfStock"}.get(581 p.get("available"))582 ld_product: dict = {583 "@context": "https://schema.org", "@type": "Product",584 "name": title_txt, "sku": uid, "url": BASE + canonical,585 "description": clean_text(p.get("description") or "", 500) or title_txt,586 }587 if images:588 ld_product["image"] = images[:6]589 if p.get("vendor"):590 ld_product["brand"] = {"@type": "Brand", "name": p["vendor"]}591 if p.get("price") is not None:592 offer = {"@type": "Offer", "url": BASE + canonical,593 "price": f"{p['price']:.2f}", "priceCurrency": p.get("currency") or "CAD",594 "seller": {"@type": "Organization", "name": p["store_name"]},595 "areaServed": {"@type": "AdministrativeArea", "name": "Québec, Canada"}}596 if availability:597 offer["availability"] = availability598 ld_product["offers"] = offer599 og = {"og:type": "product"}600 if images:601 og["og:image"] = images[0]602603 return render(title=f"{clean_text(title_txt, 70)} — {p['store_name']} | {SITE}",604 description=meta_desc, canonical=canonical, body=body,605 jsonld=[ld_product, breadcrumb_ld(crumbs)], og=og)606607608def page_stores_index() -> HTMLResponse:609 def _data():610 con = db.connect()611 try:612 return q(con, """SELECT id, name, region, city, product_count AS n613 FROM stores WHERE product_count>0614 ORDER BY region, product_count DESC""")615 finally:616 con.close()617618 stores = cached("stores_index", 600, _data)619 total = len(stores)620 by_region: dict[str, list[dict]] = {}621 for s in stores:622 by_region.setdefault(s["region"] or "Ailleurs au Québec", []).append(s)623 sections = "".join(624 f"<h2>{esc(region)} <small>({fmt_int(len(items))} boutiques)</small></h2>"625 + link_list([(f"/boutiques/{s['id']}", s["name"], f"{fmt_int(s['n'])} produits")626 for s in items])627 for region, items in sorted(by_region.items(), key=lambda kv: -len(kv[1])))628 desc = (f"Annuaire de {fmt_int(total)} boutiques en ligne québécoises agrégées par "629 f"Fabri-Ka, classées par région : alimentation, mode, maison, art et plus.")630 body = f"""631<div class="seo-page">632<h1>Boutiques québécoises en ligne</h1>633<p class="seo-stats"><b>{fmt_int(total)}</b> boutiques avec produits, classées par région.</p>634{sections}635</div>"""636 return render(title=f"Boutiques québécoises — annuaire de {fmt_int(total)} boutiques en ligne | {SITE}",637 description=desc, canonical="/boutiques", body=body,638 jsonld=[breadcrumb_ld([("Accueil", "/"), ("Boutiques", "/boutiques")])])639640641def page_store(store_id: str) -> Response:642 con = db.connect()643 try:644 rows = q(con, "SELECT * FROM stores WHERE id=?", (store_id,))645 if not rows:646 return page_404()647 s = rows[0]648 stats = q(con, """SELECT COUNT(*) AS n, MIN(price) AS pmin, MAX(price) AS pmax,649 ROUND(AVG(price),2) AS pavg650 FROM products WHERE store_id=? AND active=1 AND listing_status='published' AND price>0""",651 (store_id,))[0]652 cats = q(con, """SELECT category AS key, COUNT(*) AS n FROM products653 WHERE store_id=? AND active=1 AND listing_status='published' GROUP BY category654 ORDER BY n DESC LIMIT 8""", (store_id,))655 prods = q(con, """SELECT p.uid, p.title, p.price, p.images, s.name AS store_name,656 s.region AS store_region657 FROM products p JOIN stores s ON s.id=p.store_id658 WHERE p.store_id=? AND p.active=1 AND p.listing_status='published'659 ORDER BY p.first_seen DESC LIMIT 24""", (store_id,))660 finally:661 con.close()662663 loc = ", ".join(x for x in [s.get("city"), s.get("region")] if x) or "Québec"664 n = s.get("product_count") or 0665 origin = ORIGIN_LABELS.get(s.get("origin_class") or "", "")666 desc = clean_text(s.get("description_meta") or "", 100)667 meta_desc = clean_text(668 f"{s['name']} ({loc}) — {fmt_int(n)} produits sur Fabri-Ka. "669 + (desc + " " if desc else "")670 + (f"{origin}. " if origin else "")671 + "Prix, catalogue et boutiques similaires.", 160)672 cat_links = link_list([(f"/categorie/{CAT_SLUG[c['key']]}", cat_label(c["key"]),673 f"{fmt_int(c['n'])} produits")674 for c in cats if c["key"] in CAT_SLUG])675 region_link = (f' · <a href="/region/{REGION_SLUG[s["region"]]}">Produits de la région '676 f'{esc(s["region"])}</a>') if s.get("region") in REGION_SLUG else ""677 body = f"""678<div class="seo-page">679<nav class="breadcrumb"><a href="/">Accueil</a> / <a href="/boutiques">Boutiques</a></nav>680<h1>{esc(s["name"])}</h1>681<p class="seo-stats"><b>{fmt_int(n)}</b> produits · {esc(loc)}{" · " + esc(origin) if origin else ""}682{f" · prix moyen <b>{fmt_price(stats['pavg'])}</b>" if stats.get("pavg") else ""}</p>683<p><a href="{esc(s.get("url"))}" rel="nofollow noopener">Site officiel ↗</a>{region_link}</p>684<h2>Catégories</h2>685{cat_links}686<h2>Produits récents</h2>687{product_grid(prods)}688</div>"""689 ld: dict = {"@context": "https://schema.org", "@type": "OnlineStore",690 "name": s["name"], "url": s.get("url"),691 "address": {"@type": "PostalAddress", "addressRegion": "QC",692 "addressCountry": "CA"}}693 if s.get("city"):694 ld["address"]["addressLocality"] = s["city"]695 if s.get("logo_url"):696 ld["logo"] = s["logo_url"]697 crumbs = [("Accueil", "/"), ("Boutiques", "/boutiques"), (s["name"], f"/boutiques/{store_id}")]698 return render(title=f"{s['name']} — {fmt_int(n)} produits québécois | {SITE}",699 description=meta_desc, canonical=f"/boutiques/{store_id}", body=body,700 jsonld=[ld, breadcrumb_ld(crumbs)],701 og={"og:image": s.get("cover_url") or s.get("logo_url") or ""})702703704def page_stats() -> HTMLResponse:705 def _data():706 con = db.connect()707 try:708 return q(con, """SELECT709 (SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published') AS products,710 (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores711 """)[0]712 finally:713 con.close()714 t = cached("stats_head", 600, _data)715 desc = (f"Statistiques du marché des produits québécois en ligne : "716 f"{fmt_int(t['products'])} produits, {fmt_int(t['stores'])} boutiques — "717 f"prix par catégorie, répartition régionale, croissance du catalogue.")718 body = f"""719<div class="seo-page">720<h1>Statistiques — produits québécois en ligne</h1>721<p>{esc(desc)}</p>722<p><a href="/api/report.pdf">Télécharger le rapport de marché (PDF)</a> ·723 <a href="/produits">Parcourir les produits</a></p>724</div>"""725 return render(title=f"Statistiques du marché québécois en ligne | {SITE}",726 description=desc, canonical="/stats", body=body)727728729def page_about() -> HTMLResponse:730 desc = ("Fabri-Ka est un agrégateur indépendant qui réunit les catalogues publics des "731 "boutiques en ligne québécoises : produits fabriqués, conçus ou vendus au Québec.")732 body = f"""733<div class="seo-page">734<h1>À propos de Fabri-Ka</h1>735<p>{esc(desc)}</p>736<p>Les prix et disponibilités appartiennent aux boutiques sources. Chaque fiche renvoie737vers la boutique d'origine pour l'achat. Contact : contact@spboucher.ai</p>738<p><a href="/produits">Produits</a> · <a href="/boutiques">Boutiques</a> · <a href="/stats">Statistiques</a></p>739</div>"""740 return render(title=f"À propos | {SITE}", description=desc, canonical="/a-propos", body=body)741742743def page_inscrire() -> HTMLResponse:744 desc = ("Artisans et fabricants québécois : inscrivez votre atelier sur Fabri-Ka. "745 "Chaque fiche est validée manuellement avant publication.")746 body = f"""747<div class="seo-page">748<h1>Inscrire mon atelier</h1>749<p>{esc(desc)}</p>750<p>Remplissez le formulaire (nom, métier, description, coordonnées, site web) —751votre demande part en file de validation ; rien n'est publié sans vérification752de la fabrication québécoise et des coordonnées.</p>753<p><a href="/produits">Produits</a> · <a href="/boutiques">Boutiques</a></p>754</div>"""755 return render(title=f"Inscrire mon atelier | {SITE}", description=desc,756 canonical="/inscrire", body=body)757758759def _ecosystem() -> dict:760 """ka-ui/ecosystem.json vendoré côté frontend — source unique des761 coordonnées et des sites du Groupe KA (partagée avec la page React)."""762 p = Path(__file__).resolve().parent.parent / "frontend" / "src" / "ka" / "ecosystem.json"763 try:764 return json.loads(p.read_text(encoding="utf-8"))765 except OSError:766 return {"org": {}, "hub": {"url": "https://www.groupe-ka.com"},767 "contacts": [], "sites": []}768769770def page_contact() -> HTMLResponse:771 eco = _ecosystem()772 hub = (eco.get("hub") or {}).get("url", "https://www.groupe-ka.com")773 contacts = "".join(774 f'<li><a href="mailto:{esc(c["email"])}">{esc(c["email"])}</a>'775 f' — {esc(c["role"])}</li>'776 for c in eco.get("contacts", []))777 sites = "".join(778 f'<li><a href="https://{esc(st["domain"])}">{esc(st["wordmark"])}</a>'779 f' — {esc(st["tagline"])}</li>'780 for st in eco.get("sites", []))781 disclaimer = (eco.get("org") or {}).get("disclaimer", "")782 desc = ("Joindre le Groupe KA — Fabri-Ka est un service Groupe KA : "783 "projets et partenariats, médias, légal et vie privée (Loi 25).")784 body = f"""785<div class="seo-page">786<h1>Contact</h1>787<p>Fabri-Ka est un service du <a href="{esc(hub)}">Groupe KA</a>. Les coordonnées788ci-dessous sont communes à toutes les plateformes de l'écosystème.</p>789<ul>{contacts}</ul>790<p>{esc(disclaimer)}</p>791<h2>Les sites de l'écosystème</h2>792<ul>{sites}</ul>793</div>"""794 return render(title=f"Contact | {SITE} — Un service Groupe KA",795 description=desc, canonical="/contact", body=body)796797798def page_404() -> HTMLResponse:799 body = """800<div class="seo-page">801<h1>Page introuvable</h1>802<p>Cette page n'existe pas ou n'existe plus.</p>803<p><a href="/">Accueil</a> · <a href="/produits">Tous les produits</a> ·804 <a href="/boutiques">Boutiques</a></p>805</div>"""806 return render(title=f"Page introuvable — {SITE}",807 description="Cette page n'existe pas. Découvrez les produits québécois sur Fabri-Ka.",808 body=body, robots="noindex", status=404)809810811# ---- robots + sitemaps -------------------------------------------------------812813def robots_txt() -> PlainTextResponse:814 return PlainTextResponse(815 "User-agent: *\n"816 "Allow: /\n"817 "Disallow: /api/\n"818 "Disallow: /profil\n"819 "\n"820 f"Sitemap: {BASE}/sitemap.xml\n")821822823def _urlset(urls: list[tuple[str, str | None]]) -> str:824 ents = "".join(825 "<url><loc>" + esc(BASE + path) + "</loc>"826 + (f"<lastmod>{lastmod}</lastmod>" if lastmod else "") + "</url>"827 for path, lastmod in urls)828 return ('<?xml version="1.0" encoding="UTF-8"?>'829 '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">' + ents + "</urlset>")830831832def _product_chunks() -> int:833 def _run():834 con = db.connect()835 try:836 n = con.execute("SELECT COUNT(*) FROM products WHERE active=1 AND listing_status='published'").fetchone()[0]837 finally:838 con.close()839 return max(1, -(-n // SITEMAP_CHUNK))840 return cached("chunks", 3600, _run)841842843def sitemap_index() -> Response:844 def _run():845 today = date.today().isoformat()846 names = ["pages", "boutiques"] + [f"produits-{i}" for i in range(1, _product_chunks() + 1)]847 ents = "".join(f"<sitemap><loc>{esc(BASE)}/sitemap-{n}.xml</loc>"848 f"<lastmod>{today}</lastmod></sitemap>" for n in names)849 return ('<?xml version="1.0" encoding="UTF-8"?>'850 '<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'851 + ents + "</sitemapindex>")852 return Response(cached("sm:index", 3600, _run), media_type="application/xml")853854855def sitemap(name: str) -> Response:856 def _pages():857 combos = combo_counts()858 today = date.today().isoformat()859 urls: list[tuple[str, str | None]] = [860 ("/", today), ("/produits", today), ("/boutiques", today),861 ("/stats", today), ("/a-propos", None), ("/contact", None),862 ("/inscrire", None)]863 cat_last: dict[str, float] = {}864 reg_last: dict[str, float] = {}865 for c in combos:866 if c["cat"] in CAT_SLUG:867 cat_last[c["cat"]] = max(cat_last.get(c["cat"], 0), c["m"] or 0)868 if c["region"] in REGION_SLUG:869 reg_last[c["region"]] = max(reg_last.get(c["region"], 0), c["m"] or 0)870 urls += [(f"/categorie/{CAT_SLUG[k]}", iso_date(m)) for k, m in cat_last.items()]871 urls += [(f"/region/{REGION_SLUG[r]}", iso_date(m)) for r, m in reg_last.items()]872 urls += [(f"/categorie/{CAT_SLUG[c['cat']]}/region/{REGION_SLUG[c['region']]}",873 iso_date(c["m"]))874 for c in combos875 if c["cat"] in CAT_SLUG and c["region"] in REGION_SLUG and c["n"] > 0]876 return _urlset(urls)877878 def _stores():879 con = db.connect()880 try:881 rows = q(con, """SELECT id, last_sync FROM stores WHERE product_count>0882 ORDER BY id""")883 finally:884 con.close()885 return _urlset([(f"/boutiques/{r['id']}",886 iso_date(r["last_sync"]) if r["last_sync"] else None) for r in rows])887888 def _products(idx: int):889 def _run():890 con = db.connect()891 try:892 rows = q(con, """SELECT uid, title, last_seen FROM products WHERE active=1 AND listing_status='published'893 ORDER BY uid LIMIT ? OFFSET ?""",894 (SITEMAP_CHUNK, (idx - 1) * SITEMAP_CHUNK))895 finally:896 con.close()897 return _urlset([(product_path(r["uid"], r["title"] or ""), iso_date(r["last_seen"]))898 for r in rows])899 return _run900901 if name == "pages":902 xml = cached("sm:pages", 3600, _pages)903 elif name == "boutiques":904 xml = cached("sm:boutiques", 3600, _stores)905 else:906 m = re.fullmatch(r"produits-(\d+)", name)907 if not m or not (1 <= int(m.group(1)) <= _product_chunks()):908 return PlainTextResponse("Not found", status_code=404)909 idx = int(m.group(1))910 xml = cached(f"sm:produits:{idx}", 3600, _products(idx))911 return Response(xml, media_type="application/xml")912913914# ---- dispatch ----------------------------------------------------------------915916# uid = sha1[:20] (schema.Product.uid) — toujours 20 hex, le slug est optionnel917_PROD_RE = re.compile(r"^produits/(?:(?P<slug>.+)-)?(?P<uid>[0-9a-f]{20})$")918919920def render_route(path: str, params) -> Response:921 path = path.strip("/")922 if path == "":923 return page_home()924 if path == "produits":925 return page_listing(None, None, params)926 m = _PROD_RE.match(path)927 if m:928 return page_product(m.group("slug"), m.group("uid"), params)929 if path.startswith("produits/"):930 return page_404()931 if path == "boutiques":932 return page_stores_index()933 if path.startswith("boutiques/"):934 return page_store(path.split("/", 1)[1])935 if path == "stats":936 return page_stats()937 if path == "a-propos":938 return page_about()939 if path == "contact":940 return page_contact()941 if path == "inscrire":942 return page_inscrire()943 if path == "profil":944 return render(title=f"Mon profil — {SITE}",945 description="Profil du membre Groupe KA.",946 body='<div class="seo-page"><h1>Mon profil</h1></div>',947 robots="noindex")948 parts = path.split("/")949 if parts[0] == "categorie" and len(parts) >= 2:950 cat = SLUG_CAT.get(parts[1])951 if not cat:952 return page_404()953 if len(parts) == 2:954 return page_listing(cat, None, params)955 if len(parts) == 4 and parts[2] == "region":956 region = SLUG_REGION.get(parts[3])957 if region:958 return page_listing(cat, region, params)959 return page_404()960 if parts[0] == "region" and len(parts) == 2:961 region = SLUG_REGION.get(parts[1])962 if region:963 return page_listing(None, region, params)964 return page_404()965 return page_404()966