Créa·Ka — annuaire public cross-plateforme des créateurs de contenu québécois (crea-ka.com)
Python 73.6%
HTML 13.2%
TypeScript 6%
JavaScript 4.5%
CSS 1.7%
Dockerfile 0.6%
1# ==============================================================================2# Author: Simon-Pierre Boucher <contact@spboucher.ai>3# File: creaka/seo.py4# Desc: Référencement — SSR léger par-dessus le shell prérendu (frontend/dist)5#6# Principe (pattern lou-ka adapté) : le frontend est un index.html unique généré7# par scripts/build_frontend.py. Ce module sert le MÊME shell pour les routes8# publiques, mais remplace le bloc <!--KA:SEO--> … <!--/KA:SEO--> du <head>9# (title, description, canonical, og:, twitter:, JSON-LD) et injecte le contenu10# essentiel en HTML dans <div id="app"> — les moteurs voient une page complète11# sans exécuter JavaScript ; le SPA, en se montant, remplace ce contenu.12# Fournit aussi robots.txt et les sitemaps chunkés (accueil + 12 000+ fiches).13# ==============================================================================14from __future__ import annotations1516import html17import json18import re19import threading20from datetime import date21from pathlib import Path22from urllib.parse import quote23from xml.sax.saxutils import escape as xml_escape2425from fastapi import APIRouter26from fastapi.responses import HTMLResponse, PlainTextResponse, Response2728from . import db2930router = APIRouter()3132ROOT = Path(__file__).resolve().parent.parent33FRONTEND_DIST = ROOT / "frontend" / "dist"3435BASE_URL = "https://www.crea-ka.com"36SITE_NAME = "Créa-Ka"37SITEMAP_CHUNK = 100003839# étiquettes lisibles + source d'avatar unavatar (aligné sur PLAT du frontend)40PLAT_LABEL = {41 "instagram": "Instagram", "tiktok": "TikTok", "youtube": "YouTube",42 "twitch": "Twitch", "kick": "Kick", "x": "X (Twitter)",43 "facebook": "Facebook", "snapchat": "Snapchat", "substack": "Substack",44 "patreon": "Patreon", "onlyfans": "OnlyFans", "mym": "MYM",45 "fansly": "Fansly", "linkedin": "LinkedIn", "threads": "Threads",46 "spotify": "Spotify", "discord": "Discord", "podcast": "Balado",47 "site-web": "Site web", "autre": "Autre",48}49UNAVATAR = {"instagram": "instagram", "tiktok": "tiktok",50 "youtube": "youtube", "twitch": "twitch", "x": "twitter"}5152# connexion SQLite PAR THREAD (même doctrine que web.py, §18)53_local = threading.local()545556def _db():57 con = getattr(_local, "con", None)58 if con is None:59 con = _local.con = db.connect()60 return con616263def _e(t) -> str:64 return html.escape(str(t or ""), quote=True)656667# --- Gabarit (shell prérendu par scripts/build_frontend.py) -------------------6869_shell_cache: dict = {"mtime": 0.0, "html": ""}707172def _shell() -> str:73 f = FRONTEND_DIST / "index.html"74 mtime = f.stat().st_mtime75 if mtime != _shell_cache["mtime"]:76 _shell_cache["html"] = f.read_text(encoding="utf-8")77 _shell_cache["mtime"] = mtime78 return _shell_cache["html"]798081def _render(*, title: str, description: str, path: str,82 jsonld: list[dict] | None = None, body: str = "",83 og_image: str | None = None, og_type: str = "website",84 status: int = 200, noindex: bool = False) -> HTMLResponse:85 """Shell du build + bloc <head> SEO unique + contenu HTML dans #app."""86 canonical = BASE_URL + path87 img = og_image or (BASE_URL + "/og.png")88 lines = [89 f"<title>{html.escape(title)}</title>",90 f'<meta name="description" content="{_e(description)}">',91 ]92 if noindex:93 lines.append('<meta name="robots" content="noindex">')94 else:95 lines.append(f'<link rel="canonical" href="{_e(canonical)}">')96 lines += [97 f'<meta property="og:site_name" content="{SITE_NAME}">',98 '<meta property="og:locale" content="fr_CA">',99 f'<meta property="og:type" content="{og_type}">',100 f'<meta property="og:title" content="{_e(title)}">',101 f'<meta property="og:description" content="{_e(description)}">',102 f'<meta property="og:url" content="{_e(canonical)}">',103 f'<meta property="og:image" content="{_e(img)}">',104 ]105 if not og_image:106 lines.append('<meta property="og:image:width" content="1200">')107 lines.append('<meta property="og:image:height" content="630">')108 lines += [109 '<meta name="twitter:card" content="summary_large_image">',110 f'<meta name="twitter:title" content="{_e(title)}">',111 f'<meta name="twitter:description" content="{_e(description)}">',112 f'<meta name="twitter:image" content="{_e(img)}">',113 ]114 for obj in (jsonld or []):115 blob = json.dumps(obj, ensure_ascii=False,116 separators=(",", ":")).replace("</", "<\\/")117 lines.append(f'<script type="application/ld+json">{blob}</script>')118 block = "<!--KA:SEO-->\n" + "\n".join(lines) + "\n<!--/KA:SEO-->"119 page = re.sub(r"<!--KA:SEO-->.*?<!--/KA:SEO-->", lambda _m: block,120 _shell(), count=1, flags=re.S)121 if body:122 seo_div = ('<div style="max-width:960px;margin:0 auto;padding:24px;'123 'font-family:system-ui,sans-serif;color:#141814">' + body124 + '<p>Créa-Ka — Un service <a href="https://www.groupe-ka.com">'125 'Groupe KA</a></p></div>')126 page = page.replace('<div id="app"></div>',127 '<div id="app">' + seo_div + "</div>", 1)128 return HTMLResponse(page, status_code=status,129 headers={"Cache-Control": "no-cache"})130131132def _breadcrumb(items: list[tuple[str, str]]) -> dict:133 return {"@context": "https://schema.org", "@type": "BreadcrumbList",134 "itemListElement": [135 {"@type": "ListItem", "position": i + 1, "name": name,136 "item": BASE_URL + path}137 for i, (name, path) in enumerate(items)]}138139140def _fmt_int(n) -> str:141 return f"{int(n):,}".replace(",", " ") if n else "0"142143144# --- Fiches créateur -----------------------------------------------------------145146def _avatar(doc: dict, accounts: list) -> str | None:147 """og:image STABLE : unavatar d'abord (les URL CDN captées — Instagram148 notamment — expirent au bout de quelques semaines), avatar capté en repli."""149 for plat in ("instagram", "tiktok", "youtube", "twitch", "x"):150 for a in accounts:151 if a["platform"] == plat and plat in UNAVATAR:152 return (f"https://unavatar.io/{UNAVATAR[plat]}/"153 f"{quote(a['handle'])}")154 return doc.get("avatar_url") or None155156157def _description(name: str, bio: str, accounts: list) -> str:158 bio = re.sub(r"\s+", " ", bio or "").strip()159 if len(bio) >= 60:160 return bio[:157] + "…" if len(bio) > 160 else bio161 plats = []162 for a in accounts:163 lbl = PLAT_LABEL.get(a["platform"], a["platform"])164 if lbl not in plats:165 plats.append(lbl)166 reach = sum(a["followers"] or 0 for a in accounts)167 parts = [f"Tous les comptes publics de {name} au même endroit"]168 if plats:169 parts[0] += f" ({', '.join(plats[:5])})"170 if reach:171 parts.append(f"{_fmt_int(reach)} abonnés cumulés")172 txt = " — ".join(parts) + ". Annuaire Créa-Ka des créateurs québécois."173 if bio:174 txt = bio + " " + txt175 return txt[:157] + "…" if len(txt) > 160 else txt176177178def _gone(path: str) -> HTMLResponse:179 return _render(title="Fiche retirée | Créa-Ka",180 description="Cette fiche a été retirée de l'annuaire Créa-Ka.",181 path=path, status=410, noindex=True,182 body="<h1>Fiche retirée de l'annuaire</h1>"183 '<p>Ce créateur a demandé son retrait (opt-out) ou sa '184 'fiche n\'est plus publiée. '185 '<a href="/">Voir l\'annuaire des créateurs québécois</a></p>')186187188def _not_found(path: str) -> HTMLResponse:189 return _render(title="Page introuvable | Créa-Ka",190 description="Cette page n'existe pas sur Créa-Ka.",191 path=path, status=404, noindex=True,192 body="<h1>Fiche introuvable</h1>"193 '<p><a href="/">Voir l\'annuaire des créateurs '194 'québécois</a></p>')195196197@router.get("/createur/{cid}", include_in_schema=False)198def creator_page(cid: str) -> HTMLResponse:199 path = f"/createur/{quote(cid)}"200 con = _db()201 row = con.execute("SELECT * FROM creators WHERE id=?", (cid,)).fetchone()202 if row is None:203 return _not_found(path)204 if row["status"] != "active":205 return _gone(path) # retiré (opt-out) ou désactivé → 410206 if row["is_minor"]:207 return _not_found(path) # jamais publié (§15)208 accounts = con.execute(209 "SELECT * FROM accounts WHERE creator_id=? AND needs_review=0 "210 "ORDER BY followers DESC", (cid,)).fetchall()211 doc = json.loads(row["doc"])212 name = row["display_name"]213214 primary = next((a for a in accounts215 if a["platform"] == row["primary_platform"]),216 accounts[0] if accounts else None)217 handle = primary["handle"] if primary else ""218 title = (f"{name} (@{handle}) — profils et liens | Créa-Ka" if handle219 else f"{name} — profils et liens | Créa-Ka")220 description = _description(name, doc.get("bio", ""), accounts)221 avatar = _avatar(doc, accounts)222 canonical = BASE_URL + path223224 same_as = [a["url"] for a in accounts if a["url"]]225 person: dict = {"@type": "Person", "name": name, "url": canonical}226 if handle:227 person["alternateName"] = f"@{handle}"228 if doc.get("bio"):229 person["description"] = re.sub(r"\s+", " ", doc["bio"]).strip()[:500]230 if avatar:231 person["image"] = avatar232 if same_as:233 person["sameAs"] = same_as234 profile: dict = {"@context": "https://schema.org", "@type": "ProfilePage",235 "@id": canonical, "url": canonical,236 "name": title, "inLanguage": "fr-CA",237 "isPartOf": {"@id": BASE_URL + "/#website"},238 "mainEntity": person}239 if row["updated_at"]:240 profile["dateModified"] = row["updated_at"]241 if row["first_seen"]:242 profile["dateCreated"] = row["first_seen"]243 jsonld = [profile,244 _breadcrumb([("Accueil", "/"), (name, path)])]245246 items = []247 for a in accounts:248 lbl = PLAT_LABEL.get(a["platform"], a["platform"])249 extra = (f" — {_fmt_int(a['followers'])} abonnés"250 if a["followers"] else "")251 items.append(f'<li><a href="{_e(a["url"])}" rel="noopener">'252 f"{_e(lbl)} — @{_e(a['handle'])}</a>{extra}</li>")253 bio_html = ""254 if doc.get("bio"):255 clean_bio = re.sub(r"\s+", " ", doc["bio"]).strip()256 bio_html = f"<p>{_e(clean_bio)}</p>"257 body = (f"<h1>{_e(name)}{' (@' + _e(handle) + ')' if handle else ''}</h1>"258 + bio_html259 + (f"<ul>{''.join(items)}</ul>" if items else "")260 + '<p><a href="/">Annuaire des créateurs de contenu québécois</a></p>')261 return _render(title=title, description=description, path=path,262 jsonld=jsonld, body=body, og_image=avatar,263 og_type="profile")264265266# --- Pages statiques du SPA (canonical/title corrects, sinon fallback = accueil)267268_STATIC_PAGES = {269 "/stats": ("Statistiques de l'annuaire | Créa-Ka",270 "Statistiques publiques de Créa-Ka : créateurs québécois "271 "recensés, comptes reliés, répartition par plateforme, niche, "272 "région et taille d'audience.", False),273 "/retrait": ("Retrait de fiche (opt-out) | Créa-Ka",274 "Demander le retrait de votre fiche de l'annuaire Créa-Ka : "275 "masquage immédiat et respecté (Loi 25).", False),276 "/contact": ("Contact | Créa-Ka",277 "Contacter l'équipe de Créa-Ka et du Groupe KA : projets, "278 "partenariats, médias, vie privée et Loi 25.", False),279 "/compte": ("Mon compte | Créa-Ka",280 "Espace compte KA ID sur Créa-Ka.", True),281}282283for _path, (_t, _d, _noidx) in _STATIC_PAGES.items():284 def _mk(p=_path, t=_t, d=_d, noidx=_noidx):285 def handler() -> HTMLResponse:286 return _render(title=t, description=d, path=p, noindex=noidx,287 jsonld=None if noidx else288 [_breadcrumb([("Accueil", "/"), (t.split(" | ")[0], p)])])289 return handler290 router.get(_path, include_in_schema=False)(_mk())291292293# --- Pages programmatiques plateformes : /plateformes + /plateforme/{p} ---------294295# seuil d'inclusion d'une page plateforme (pages trop maigres exclues du sitemap)296MIN_CREATORS_PAGE = 3297298299def _platform_rows() -> list[dict]:300 """[{platform, label, n (créateurs actifs)}] trié par volume décroissant."""301 rows = _db().execute(302 """SELECT a.platform, COUNT(DISTINCT a.creator_id) n303 FROM accounts a JOIN creators c ON c.id=a.creator_id304 WHERE c.status='active' AND c.is_minor=0305 GROUP BY a.platform ORDER BY n DESC""").fetchall()306 return [{"platform": r["platform"],307 "label": PLAT_LABEL.get(r["platform"], r["platform"]),308 "n": r["n"]}309 for r in rows if r["platform"] in PLAT_LABEL]310311312@router.get("/plateformes", include_in_schema=False)313def plateformes_page() -> HTMLResponse:314 plats = _platform_rows()315 total = sum(p["n"] for p in plats)316 title = (f"Créateurs de contenu québécois par plateforme "317 f"({len(plats)} plateformes) | Créa-Ka")318 description = (319 f"L'annuaire Créa-Ka par plateforme : {_fmt_int(total)} présences de "320 f"créateurs québécois sur {len(plats)} plateformes — YouTube, "321 "Instagram, TikTok, Twitch, Facebook, X et plus.")322 body = (f"<h1>Créateurs québécois par plateforme — {len(plats)} "323 "plateformes</h1><ul>"324 + "".join(325 f'<li><a href="/plateforme/{p["platform"]}">Créateurs québécois '326 f'sur {_e(p["label"])}</a> — {_fmt_int(p["n"])} créateurs</li>'327 for p in plats)328 + '</ul><p><a href="/">Annuaire des créateurs de contenu '329 "québécois</a></p>")330 jsonld = [_breadcrumb([("Accueil", "/"), ("Plateformes", "/plateformes")]),331 {"@context": "https://schema.org", "@type": "ItemList",332 "name": "Créateurs de contenu québécois par plateforme",333 "numberOfItems": len(plats),334 "itemListElement": [335 {"@type": "ListItem", "position": i + 1,336 "name": f"Créateurs québécois sur {p['label']}",337 "url": f"{BASE_URL}/plateforme/{p['platform']}"}338 for i, p in enumerate(plats)]}]339 return _render(title=title, description=description, path="/plateformes",340 jsonld=jsonld, body=body)341342343@router.get("/plateforme/{plat}", include_in_schema=False)344def plateforme_page(plat: str) -> HTMLResponse:345 if plat not in PLAT_LABEL:346 return _not_found(f"/plateforme/{plat}")347 label = PLAT_LABEL[plat]348 rows = _db().execute(349 """SELECT c.id, c.display_name, a.handle, a.followers350 FROM accounts a JOIN creators c ON c.id=a.creator_id351 WHERE a.platform=? AND c.status='active' AND c.is_minor=0352 ORDER BY a.followers IS NULL, a.followers DESC LIMIT 100""",353 (plat,)).fetchall()354 n = _db().execute(355 """SELECT COUNT(DISTINCT a.creator_id) n356 FROM accounts a JOIN creators c ON c.id=a.creator_id357 WHERE a.platform=? AND c.status='active' AND c.is_minor=0""",358 (plat,)).fetchone()["n"]359 path = f"/plateforme/{plat}"360 if n == 0:361 return _not_found(path)362363 title = f"Créateurs québécois sur {label} — {_fmt_int(n)} profils | Créa-Ka"364 description = (365 f"{_fmt_int(n)} créateurs de contenu québécois actifs sur {label}, "366 "classés par audience : profils, niches, autres plateformes et "367 "statistiques dans l'annuaire Créa-Ka.")368 lis = []369 for r in rows:370 extra = (f" — {_fmt_int(r['followers'])} abonnés"371 if r["followers"] else "")372 handle = f" (@{_e(r['handle'])})" if r["handle"] else ""373 lis.append(f'<li><a href="/createur/{_e(quote(r["id"]))}">'374 f'{_e(r["display_name"])}</a>{handle}{extra}</li>')375 others = [p for p in _platform_rows() if p["platform"] != plat][:12]376 body = (f"<h1>Créateurs québécois sur {_e(label)} — {_fmt_int(n)} "377 "profils</h1>"378 f"<p>Les créateurs de contenu québécois présents sur {_e(label)}, "379 "classés par taille d'audience. Chaque fiche relie tous les "380 "comptes du créateur sur les autres plateformes.</p>"381 "<h2>Profils</h2><ul>" + "".join(lis) + "</ul>"382 + ("<h2>Autres plateformes</h2><ul>" + "".join(383 f'<li><a href="/plateforme/{p["platform"]}">{_e(p["label"])}</a>'384 f' — {_fmt_int(p["n"])}</li>' for p in others) + "</ul>"385 if others else "")386 + '<p><a href="/plateformes">Toutes les plateformes</a> · '387 '<a href="/">Annuaire des créateurs québécois</a></p>')388 crumbs = [("Accueil", "/"), ("Plateformes", "/plateformes"), (label, path)]389 jsonld = [_breadcrumb(crumbs),390 {"@context": "https://schema.org", "@type": "ItemList",391 "name": f"Créateurs québécois sur {label}", "numberOfItems": n,392 "itemListElement": [393 {"@type": "ListItem", "position": i + 1,394 "name": r["display_name"],395 "url": f"{BASE_URL}/createur/{quote(r['id'])}"}396 for i, r in enumerate(rows[:50])]}]397 return _render(title=title, description=description, path=path,398 jsonld=jsonld, body=body)399400401# --- robots.txt & sitemaps -------------------------------------------------------402403@router.get("/robots.txt", include_in_schema=False)404def robots() -> PlainTextResponse:405 return PlainTextResponse(406 "User-agent: *\n"407 "Disallow: /api/\n"408 "Disallow: /compte\n"409 "Allow: /\n"410 f"\nSitemap: {BASE_URL}/sitemap.xml\n")411412413def _xml(body: str) -> Response:414 return Response('<?xml version="1.0" encoding="UTF-8"?>\n' + body,415 media_type="application/xml",416 headers={"Cache-Control": "max-age=3600"})417418419def _urlset(urls: list[tuple[str, str | None]]) -> Response:420 rows = []421 for loc, lastmod in urls:422 lm = f"<lastmod>{lastmod}</lastmod>" if lastmod else ""423 rows.append(f"<url><loc>{xml_escape(loc)}</loc>{lm}</url>")424 return _xml('<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'425 + "\n".join(rows) + "\n</urlset>")426427428def _active_count() -> int:429 return _db().execute("SELECT COUNT(*) c FROM creators "430 "WHERE status='active' AND is_minor=0").fetchone()["c"]431432433@router.get("/sitemap.xml", include_in_schema=False)434def sitemap_index() -> Response:435 chunks = max(1, -(-_active_count() // SITEMAP_CHUNK))436 names = ["sitemap-pages.xml"] + [f"sitemap-createurs-{i + 1}.xml"437 for i in range(chunks)]438 today = date.today().isoformat()439 rows = [f"<sitemap><loc>{BASE_URL}/{n}</loc><lastmod>{today}</lastmod>"440 "</sitemap>" for n in names]441 return _xml('<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'442 + "\n".join(rows) + "\n</sitemapindex>")443444445@router.get("/sitemap-pages.xml", include_in_schema=False)446def sitemap_pages() -> Response:447 last = _db().execute("SELECT MAX(updated_at) m FROM creators "448 "WHERE status='active'").fetchone()["m"]449 lm = (last or "")[:10] or None450 urls = [(BASE_URL + "/", lm),451 (BASE_URL + "/plateformes", lm),452 (BASE_URL + "/stats", lm),453 (BASE_URL + "/retrait", None),454 (BASE_URL + "/contact", None)]455 urls += [(f"{BASE_URL}/plateforme/{p['platform']}", lm)456 for p in _platform_rows() if p["n"] >= MIN_CREATORS_PAGE]457 return _urlset(urls)458459460@router.get("/sitemap-createurs-{n}.xml", include_in_schema=False)461def sitemap_creators(n: int) -> Response:462 if n < 1:463 return _xml('<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'464 "</urlset>")465 rows = _db().execute(466 "SELECT id, updated_at FROM creators WHERE status='active' AND "467 "is_minor=0 ORDER BY id LIMIT ? OFFSET ?",468 (SITEMAP_CHUNK, (n - 1) * SITEMAP_CHUNK)).fetchall()469 return _urlset([(f"{BASE_URL}/createur/{quote(r['id'])}",470 (r["updated_at"] or "")[:10] or None) for r in rows])471