# ----------------------------------------------------------------------------- # Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec) # Auteur : Simon-Pierre Boucher — contact@spboucher.ai # seo.py : rendu serveur des pages publiques — le HTML complet (meta uniques, # JSON-LD schema.org, contenu dans #root) est servi dès la première # requête, puis React monte par-dessus et reprend la main. # Inclut : pages programmatiques (marque, modèle, région, ville, # carrosserie), sitemap.xml dynamique, robots.txt, 410 pour les # véhicules retirés, 404 réels. # ----------------------------------------------------------------------------- from __future__ import annotations import html as html_lib import json import math import re import time import unicodedata from datetime import datetime, timezone from pathlib import Path from urllib.parse import quote from fastapi import APIRouter, Response from fastapi.responses import HTMLResponse, PlainTextResponse, RedirectResponse from . import db BASE_URL = "https://www.auto-ka.com" ROOT = Path(__file__).resolve().parent.parent INDEX_PATH = ROOT / "frontend" / "dist" / "index.html" SOURCES_PATH = ROOT / "data" / "sources.json" SITEMAP_CHUNK = 10_000 # URLs par sous-sitemap véhicules LIST_SIZE = 24 # fiches listées en HTML sur les pages de recherche MAPS_TTL = 300 # secondes de cache des dictionnaires slug -> valeur router = APIRouter() esc = html_lib.escape # --- utilitaires -------------------------------------------------------------- def slugify(text: str, max_len: int = 70) -> str: """Slug URL — DOIT rester synchrone avec slugify() de frontend/src/api.ts.""" t = unicodedata.normalize("NFKD", text or "") t = "".join(c for c in t if not unicodedata.combining(c)) t = re.sub(r"[^a-zA-Z0-9]+", "-", t).strip("-").lower() t = t[:max_len].rstrip("-") return t or "x" def fmt_price(p) -> str: if p is None: return "Prix sur demande" return f"{int(round(p)):,}".replace(",", " ") + " $" def fmt_km(km) -> str: if km is None: return "" return f"{int(round(km)):,}".replace(",", " ") + " km" def _date(ts) -> str: try: return datetime.fromtimestamp(float(ts), tz=timezone.utc).strftime("%Y-%m-%d") except (TypeError, ValueError): return datetime.now(tz=timezone.utc).strftime("%Y-%m-%d") _cache: dict[str, tuple[float, object]] = {} def _cached(key: str, ttl: float, fn): now = time.time() hit = _cache.get(key) if hit is not None and now - hit[0] < ttl: return hit[1] val = fn() _cache[key] = (now, val) return val # --- dictionnaires slug -> valeur (base des pages programmatiques) ------------- # Seules les carrosseries « propres » ont droit à une page dédiée : les valeurs # brutes des concessionnaires contiennent du bruit (« 4 P0RTES », « 2dr Car »…) BODY_WHITELIST = {"VUS", "Berline", "Camionnette", "Fourgonnette", "Cabriolet", "Coupé", "Hayon", "Familiale", "Compacte", "Sport"} def _put(dct: dict, slug: str, info: dict) -> None: """Insère en fusionnant les variantes de casse (RAV4 / Rav4 → même slug).""" prev = dct.get(slug) if prev is None: dct[slug] = info return keep = info if info["n"] >= prev["n"] else prev other = prev if keep is info else info keep = dict(keep) keep["n"] += other["n"] keep["lm"] = max(keep["lm"] or 0, other["lm"] or 0) dct[slug] = keep def _load_maps() -> dict: con = db.connect() m: dict = {"make": {}, "model": {}, "region": {}, "city": {}, "body": {}} for r in con.execute( "SELECT kind, make, COUNT(*) n, MAX(updated_at) lm FROM vehicles" " WHERE active=1 AND make<>'' GROUP BY kind, make"): _put(m["make"].setdefault(r["kind"], {}), slugify(r["make"]), {"value": r["make"], "n": r["n"], "lm": r["lm"]}) for r in con.execute( "SELECT make, model, COUNT(*) n, MAX(updated_at) lm FROM vehicles" " WHERE active=1 AND kind='auto' AND make<>'' AND model<>''" " GROUP BY make, model"): _put(m["model"].setdefault(slugify(r["make"]), {}), slugify(r["model"]), {"make": r["make"], "value": r["model"], "n": r["n"], "lm": r["lm"]}) for r in con.execute( "SELECT region, COUNT(*) n, MAX(updated_at) lm FROM vehicles" " WHERE active=1 AND kind='auto' AND region<>'' GROUP BY region"): m["region"][slugify(r["region"])] = {"value": r["region"], "n": r["n"], "lm": r["lm"]} for r in con.execute( "SELECT city, MAX(region) region, COUNT(*) n, MAX(updated_at) lm" " FROM vehicles WHERE active=1 AND kind='auto' AND city<>'' GROUP BY city"): m["city"][slugify(r["city"])] = { "value": r["city"], "region": r["region"], "n": r["n"], "lm": r["lm"]} for r in con.execute( "SELECT body_type, COUNT(*) n, MAX(updated_at) lm FROM vehicles" " WHERE active=1 AND kind='auto' AND body_type<>'' GROUP BY body_type"): if r["body_type"] in BODY_WHITELIST: m["body"][slugify(r["body_type"])] = { "value": r["body_type"], "n": r["n"], "lm": r["lm"]} con.close() return m def _maps() -> dict: return _cached("maps", MAPS_TTL, _load_maps) # --- gabarit HTML (frontend/dist/index.html) ----------------------------------- def _shell() -> str: """index.html du build Vite, relu quand le fichier change (cache mtime).""" mtime = INDEX_PATH.stat().st_mtime hit = _cache.get("shell") if hit is not None and hit[0] == mtime: return hit[1] # type: ignore[return-value] text = INDEX_PATH.read_text(encoding="utf-8") _cache["shell"] = (mtime, text) return text def render_page(*, path: str, title: str, description: str, body: str = "", robots: str | None = None, og_image: str | None = None, jsonld: list[dict] | None = None, seo_ctx: dict | None = None, og_type: str = "website", status: int = 200) -> HTMLResponse: """Injecte meta + contenu dans le index.html du build (React reprend ensuite).""" shell = _shell() canonical = BASE_URL + path shell = re.sub(r".*?", f"{esc(title)}", shell, count=1, flags=re.S) shell = re.sub(r'', f'', shell, count=1) # og:image / twitter : retirer les balises statiques du gabarit (dist/index.html) # pour éviter les doublons — elles sont réinjectées dynamiquement ci-dessous. shell = re.sub(r'\s*', "", shell) extra: list[str] = [f''] if robots: extra.append(f'') extra += [ f'', f'', f'', f'', f'', f'', '', '', ] img_url = og_image or (BASE_URL + "/og.png") extra.append(f'') if not og_image: extra.append('') extra.append('') extra.append('') extra.append(f'') for ld in (jsonld or []): blob = json.dumps(ld, ensure_ascii=False).replace("{blob}') if seo_ctx is not None: blob = json.dumps(seo_ctx, ensure_ascii=False).replace("window.__SEO_CTX__={blob}") shell = shell.replace("", " " + "\n ".join(extra) + "\n ", 1) shell = shell.replace('
', f'
{body}
', 1) return HTMLResponse(shell, status_code=status, headers={"Cache-Control": "no-cache, must-revalidate"}) # --- fragments HTML ------------------------------------------------------------- def _veh_slug(v: dict) -> str: base = " ".join(str(x) for x in [v.get("year"), v.get("make"), v.get("model")] if x) or (v.get("title") or "vehicule") return slugify(base) def _veh_path(v: dict) -> str: return f"/vehicule/{quote(v['uid'], safe='')}/{_veh_slug(v)}" def _first_image(v: dict) -> str | None: imgs = v.get("images") if isinstance(imgs, str): imgs = json.loads(imgs or "[]") return imgs[0] if imgs else None def _card(v: dict) -> str: img = _first_image(v) photo = (f'{esc(v.get(' if img else '') year = f'{v["year"]}' if v.get("year") else "" chip_vals = [c for c in (fmt_km(v.get("mileage_km")), v.get("transmission"), v.get("fuel"), v.get("body_type")) if c][:4] chips = "".join(f'{esc(c)}' for c in chip_vals) return ( f'' f'
{photo}{year}
' f'

{esc(v.get("title") or "")}

' f'
{fmt_price(v.get("price"))}
' f'
{chips}
' f'
{esc(v.get("dealer_name") or v.get("source") or "")}' f'{esc(v.get("city") or "")}
' f"
") def _crumbs_html(crumbs: list[tuple[str, str | None]]) -> str: """crumbs = [(label, path|None)] — le dernier élément n'est pas un lien.""" items = [] for label, path in crumbs: if path: items.append(f'{esc(label)}') else: items.append(f"{esc(label)}") return ('") def _links_block(title: str, links: list[tuple[str, str]]) -> str: if not links: return "" inner = "".join(f'{esc(t)}' for t, p in links) return (f'

{esc(title)}

' f'
{inner}
') def _listing_body(*, h1: str, kicker: str, intro: str, st: dict, vehicles: list[dict], sections: list[tuple[str, list[tuple[str, str]]]], crumbs: list[tuple[str, str | None]] | None = None) -> str: parts = ['
'] if crumbs: parts.append(_crumbs_html(crumbs)) stats_html = "" if st.get("n"): cells = [f'
{st["n"]:,} en vente
'.replace(",", " ")] if st.get("avg_p"): cells.append(f'
prix moyen {fmt_price(st["avg_p"])}
') if st.get("med_p"): cells.append(f'
prix médian {fmt_price(st["med_p"])}
') if st.get("ymin") and st.get("ymax"): cells.append(f'
années {st["ymin"]}–{st["ymax"]}
') stats_html = f'
{"".join(cells)}
' parts.append( f'
{esc(kicker)}' f"

{esc(h1)}

" f'

{esc(intro)}

{stats_html}
') if vehicles: shown = min(len(vehicles), LIST_SIZE) label = f'{st["n"]:,}'.replace(",", " ") if st.get("n") else str(shown) parts.append(f'

{label}' " véhicules à vendre

") parts.append('
' + "".join(_card(v) for v in vehicles[:LIST_SIZE]) + "
") for title, links in sections: parts.append(_links_block(title, links)) parts.append("
") return "".join(parts) # --- requêtes ------------------------------------------------------------------ def _stats(con, where: str, args: list) -> dict: row = con.execute( f"SELECT COUNT(*) n, ROUND(AVG(price)) avg_p, MIN(year) ymin, MAX(year) ymax" f" FROM vehicles WHERE active=1 AND dup_of IS NULL AND {where}", args).fetchone() st = dict(row) n_priced = con.execute( f"SELECT COUNT(*) c FROM vehicles WHERE active=1 AND dup_of IS NULL AND price IS NOT NULL" f" AND {where}", args).fetchone()["c"] st["med_p"] = None if n_priced: st["med_p"] = con.execute( f"SELECT price FROM vehicles WHERE active=1 AND dup_of IS NULL AND price IS NOT NULL AND {where}" f" ORDER BY price LIMIT 1 OFFSET ?", args + [(n_priced - 1) // 2], ).fetchone()["price"] return st def _fetch(con, where: str, args: list, limit: int = LIST_SIZE) -> list[dict]: rows = con.execute( f"SELECT * FROM vehicles WHERE active=1 AND dup_of IS NULL AND {where}" f" ORDER BY price IS NULL, price ASC LIMIT ?", args + [limit]).fetchall() return [dict(r) for r in rows] # --- JSON-LD -------------------------------------------------------------------- def _ld_itemlist(name: str, n: int, vehicles: list[dict]) -> dict: return { "@context": "https://schema.org", "@type": "ItemList", "name": name, "numberOfItems": n, "itemListElement": [ {"@type": "ListItem", "position": i + 1, "url": BASE_URL + _veh_path(v)} for i, v in enumerate(vehicles[:LIST_SIZE])], } def _ld_crumbs(crumbs: list[tuple[str, str | None]]) -> dict: return { "@context": "https://schema.org", "@type": "BreadcrumbList", "itemListElement": [ {"@type": "ListItem", "position": i + 1, "name": label, **({"item": BASE_URL + path} if path else {})} for i, (label, path) in enumerate(crumbs)], } def _ld_vehicle(v: dict, canonical: str) -> dict: vtype = {"auto": "Car", "moto": "Motorcycle", "scooter": "Motorcycle"}.get( v.get("kind") or "auto", "Vehicle") imgs = v.get("images") if isinstance(imgs, str): imgs = json.loads(imgs or "[]") ld: dict = { "@context": "https://schema.org", "@type": vtype, "name": v.get("title") or "", "url": canonical, "itemCondition": "https://schema.org/UsedCondition", } if v.get("make"): ld["brand"] = {"@type": "Brand", "name": v["make"]} if v.get("model"): ld["model"] = v["model"] if v.get("year"): ld["vehicleModelDate"] = str(v["year"]) if v.get("mileage_km") is not None: ld["mileageFromOdometer"] = {"@type": "QuantitativeValue", "value": int(v["mileage_km"]), "unitCode": "KMT"} if v.get("transmission"): ld["vehicleTransmission"] = v["transmission"] if v.get("fuel"): ld["fuelType"] = v["fuel"] if v.get("drivetrain"): ld["driveWheelConfiguration"] = v["drivetrain"] if v.get("body_type"): ld["bodyType"] = v["body_type"] if v.get("exterior_color"): ld["color"] = v["exterior_color"] if v.get("doors"): ld["numberOfDoors"] = v["doors"] if v.get("seats"): ld["seatingCapacity"] = v["seats"] if v.get("vin"): ld["vehicleIdentificationNumber"] = v["vin"] if v.get("description"): ld["description"] = (v["description"] or "")[:400] if imgs: ld["image"] = imgs[:5] if v.get("price") is not None: ld["offers"] = { "@type": "Offer", "price": int(round(v["price"])), "priceCurrency": "CAD", "availability": "https://schema.org/InStock", "itemCondition": "https://schema.org/UsedCondition", "url": canonical, "seller": {"@type": "AutoDealer", "name": v.get("dealer_name") or v.get("source") or "", "address": {"@type": "PostalAddress", "addressLocality": v.get("city") or "", "addressRegion": "QC", "addressCountry": "CA"}}, } return ld # --- blocs de liens partagés ----------------------------------------------------- def _links_makes(kind: str = "auto", limit: int = 30, skip: str | None = None) -> list[tuple[str, str]]: prefix = {"auto": "/usagees/", "moto": "/motos/", "scooter": "/scooters/"}[kind] makes = _maps()["make"].get(kind, {}) out = [] for slug, info in sorted(makes.items(), key=lambda kv: -kv[1]["n"])[: limit + 1]: if skip and info["value"] == skip: continue out.append((f'{info["value"]} ({info["n"]})', prefix + slug)) return out[:limit] def _links_regions(skip: str | None = None) -> list[tuple[str, str]]: return [(f'{i["value"]} ({i["n"]})', "/region/" + s) for s, i in sorted(_maps()["region"].items(), key=lambda kv: -kv[1]["n"]) if i["value"] != skip] def _links_cities(region: str | None = None, limit: int = 30, skip: str | None = None) -> list[tuple[str, str]]: out = [] for s, i in sorted(_maps()["city"].items(), key=lambda kv: -kv[1]["n"]): if region and i["region"] != region: continue if skip and i["value"] == skip: continue out.append((f'{i["value"]} ({i["n"]})', "/ville/" + s)) return out[:limit] def _links_bodies(skip: str | None = None) -> list[tuple[str, str]]: return [(f'{i["value"]} ({i["n"]})', "/carrosserie/" + s) for s, i in sorted(_maps()["body"].items(), key=lambda kv: -kv[1]["n"]) if i["value"] != skip] def _links_models(make_slug: str, limit: int = 40, skip: str | None = None) -> list[tuple[str, str]]: models = _maps()["model"].get(make_slug, {}) out = [] for s, i in sorted(models.items(), key=lambda kv: -kv[1]["n"]): if skip and i["value"] == skip: continue out.append((f'{i["value"]} ({i["n"]})', f"/usagees/{make_slug}/{s}")) return out[:limit] # --- page 404 --------------------------------------------------------------------- def render_404(path: str = "/introuvable") -> HTMLResponse: body = ('
🧭
' "

Page introuvable

Le lien demandé n'existe pas ou plus.

" '

← Toutes les voitures usagées

' "
") return render_page(path=path, title="Page introuvable | Auto·Ka", description="Cette page n'existe pas. Retrouvez toutes les " "voitures usagées à vendre au Québec sur Auto-Ka.", body=body, robots="noindex, follow", status=404) # --- routes : accueils --------------------------------------------------------------- _KIND_HOME = { "auto": { "path": "/", "who": "voitures usagées", "title": "Voitures usagées à vendre au Québec — {n} véhicules | Auto·Ka · Un service Groupe KA", "h1": "Toutes les voitures usagées à vendre au Québec. Un seul endroit.", "intro": ("Auto-Ka visite les sites des concessionnaires et marchands " "d'occasion de toutes les régions du Québec, normalise chaque " "annonce et détecte les nouveautés, les ventes et les baisses " "de prix — automatiquement."), }, "moto": { "path": "/motos", "who": "motos usagées", "title": "Motos usagées à vendre au Québec — {n} motos | Auto·Ka", "h1": "Toutes les motos usagées à vendre au Québec. Un seul endroit.", "intro": ("Les inventaires des concessionnaires moto du Québec — " "Harley-Davidson, Honda, Yamaha, Kawasaki, BMW et plus — " "agrégés à la source et tenus à jour automatiquement."), }, "scooter": { "path": "/scooters", "who": "scooters usagés", "title": "Scooters usagés à vendre au Québec — {n} scooters | Auto·Ka", "h1": "Tous les scooters usagés à vendre au Québec. Un seul endroit.", "intro": ("Les scooters usagés des concessionnaires du Québec — Vespa, " "Honda, Yamaha, Kymco et plus — agrégés à la source et tenus " "à jour automatiquement."), }, } def _home(kind: str) -> HTMLResponse: cfg = _KIND_HOME[kind] con = db.connect() st = _stats(con, "kind=?", [kind]) n_sources = con.execute( "SELECT COUNT(DISTINCT source) c FROM vehicles WHERE active=1 AND kind=?", [kind]).fetchone()["c"] vehicles = _fetch(con, "kind=?", [kind]) con.close() n_txt = f'{st["n"]:,}'.replace(",", " ") title = cfg["title"].format(n=n_txt) description = (f"{n_txt} {cfg['who']} à vendre chez {n_sources} concessionnaires " f"du Québec, agrégées directement depuis leurs sites." + (f" Prix moyen {fmt_price(st['avg_p'])}." if st.get("avg_p") else "") + " Mise à jour continue.") sections: list[tuple[str, list[tuple[str, str]]]] = [ ("Par marque", _links_makes(kind)), ] if kind == "auto": sections += [ ("Par région", _links_regions()), ("Par carrosserie", _links_bodies()), ("Par ville", _links_cities()), ("Aussi sur Auto-Ka", [("Motos usagées", "/motos"), ("Scooters usagés", "/scooters"), ("Statistiques du marché", "/stats"), ("Concessionnaires sources", "/sources")]), ] body = _listing_body(h1=cfg["h1"], kicker="Agrégateur indépendant — direct des concessionnaires", intro=cfg["intro"], st=st, vehicles=vehicles, sections=sections) jsonld = [_ld_itemlist(title, st["n"], vehicles)] if kind == "auto": jsonld.insert(0, { "@context": "https://schema.org", "@type": "WebSite", "name": "Auto·Ka", "url": BASE_URL + "/", "potentialAction": {"@type": "SearchAction", "target": f"{BASE_URL}/?q={{search_term_string}}", "query-input": "required name=search_term_string"}, }) return render_page(path=cfg["path"], title=title, description=description, body=body, jsonld=jsonld) @router.get("/", response_model=None, include_in_schema=False) def home_auto(): return _home("auto") @router.get("/motos", response_model=None, include_in_schema=False) def home_moto(): return _home("moto") @router.get("/scooters", response_model=None, include_in_schema=False) def home_scooter(): return _home("scooter") # --- routes : pages programmatiques ---------------------------------------------- def _listing_page(*, path: str, kind: str, where: str, args: list, h1: str, title_fmt: str, desc_intro: str, filters: dict, sections: list[tuple[str, list[tuple[str, str]]]], crumbs: list[tuple[str, str | None]]) -> HTMLResponse: con = db.connect() st = _stats(con, where, args) vehicles = _fetch(con, where, args) con.close() if not st["n"]: return render_404(path) n_txt = f'{st["n"]:,}'.replace(",", " ") title = title_fmt.format(n=n_txt) description = (f"{desc_intro} {n_txt} en inventaire" + (f", prix moyen {fmt_price(st['avg_p'])}" if st.get("avg_p") else "") + (f", médian {fmt_price(st['med_p'])}" if st.get("med_p") else "") + ". Annonces agrégées directement des sites des concessionnaires," " mises à jour en continu.") intro = (f"{n_txt} annonces vérifiées chez les concessionnaires du Québec." + (f" Prix moyen {fmt_price(st['avg_p'])}, médian {fmt_price(st['med_p'])}." if st.get("avg_p") and st.get("med_p") else "") + " Chaque fiche renvoie vers l'annonce originale du commerçant.") seo_ctx = {"path": path, "kind": kind, "h1": h1, "intro": intro, "filters": filters} body = _listing_body(h1=h1, kicker="Voitures usagées — direct des concessionnaires" if kind == "auto" else "Direct des concessionnaires", intro=intro, st=st, vehicles=vehicles, sections=sections, crumbs=crumbs) return render_page(path=path, title=title, description=description, body=body, jsonld=[_ld_crumbs(crumbs), _ld_itemlist(h1, st["n"], vehicles)], seo_ctx=seo_ctx) @router.get("/usagees/{make_slug}", response_model=None, include_in_schema=False) def page_make(make_slug: str): info = _maps()["make"].get("auto", {}).get(make_slug) if not info: return render_404(f"/usagees/{make_slug}") make = info["value"] return _listing_page( path=f"/usagees/{make_slug}", kind="auto", where="kind='auto' AND make=? COLLATE NOCASE", args=[make], h1=f"{make} usagées à vendre au Québec", title_fmt=f"{make} usagées à vendre au Québec — {{n}} en inventaire | Auto·Ka", desc_intro=f"Toutes les {make} usagées à vendre chez les concessionnaires du Québec.", filters={"make": make}, sections=[(f"Modèles {make}", _links_models(make_slug)), ("Autres marques", _links_makes("auto", 20, skip=make)), ("Par région", _links_regions())], crumbs=[("Accueil", "/"), (f"{make} usagées", None)]) @router.get("/usagees/{make_slug}/{model_slug}", response_model=None, include_in_schema=False) def page_model(make_slug: str, model_slug: str): info = _maps()["model"].get(make_slug, {}).get(model_slug) if not info: return render_404(f"/usagees/{make_slug}/{model_slug}") make, model = info["make"], info["value"] return _listing_page( path=f"/usagees/{make_slug}/{model_slug}", kind="auto", where="kind='auto' AND make=? COLLATE NOCASE AND model LIKE ?", args=[make, f"{model}%"], h1=f"{make} {model} usagés à vendre au Québec", title_fmt=f"{make} {model} usagés à vendre au Québec — {{n}} en inventaire | Auto·Ka", desc_intro=f"Tous les {make} {model} usagés à vendre chez les concessionnaires du Québec.", filters={"make": make, "model": model}, sections=[(f"Autres modèles {make}", _links_models(make_slug, skip=model)), ("Autres marques", _links_makes("auto", 15, skip=make))], crumbs=[("Accueil", "/"), (f"{make} usagées", f"/usagees/{make_slug}"), (f"{make} {model}", None)]) @router.get("/motos/{make_slug}", response_model=None, include_in_schema=False) def page_moto_make(make_slug: str): info = _maps()["make"].get("moto", {}).get(make_slug) if not info: return render_404(f"/motos/{make_slug}") make = info["value"] return _listing_page( path=f"/motos/{make_slug}", kind="moto", where="kind='moto' AND make=? COLLATE NOCASE", args=[make], h1=f"Motos {make} usagées à vendre au Québec", title_fmt=f"Motos {make} usagées à vendre au Québec — {{n}} en inventaire | Auto·Ka", desc_intro=f"Toutes les motos {make} usagées chez les concessionnaires du Québec.", filters={"make": make}, sections=[("Autres marques de motos", _links_makes("moto", 20, skip=make)), ("Aussi sur Auto-Ka", [("Voitures usagées", "/"), ("Scooters usagés", "/scooters")])], crumbs=[("Accueil", "/"), ("Motos", "/motos"), (make, None)]) @router.get("/scooters/{make_slug}", response_model=None, include_in_schema=False) def page_scooter_make(make_slug: str): info = _maps()["make"].get("scooter", {}).get(make_slug) if not info: return render_404(f"/scooters/{make_slug}") make = info["value"] return _listing_page( path=f"/scooters/{make_slug}", kind="scooter", where="kind='scooter' AND make=? COLLATE NOCASE", args=[make], h1=f"Scooters {make} usagés à vendre au Québec", title_fmt=f"Scooters {make} usagés à vendre au Québec — {{n}} en inventaire | Auto·Ka", desc_intro=f"Tous les scooters {make} usagés chez les concessionnaires du Québec.", filters={"make": make}, sections=[("Autres marques de scooters", _links_makes("scooter", 20, skip=make)), ("Aussi sur Auto-Ka", [("Voitures usagées", "/"), ("Motos usagées", "/motos")])], crumbs=[("Accueil", "/"), ("Scooters", "/scooters"), (make, None)]) @router.get("/region/{slug}", response_model=None, include_in_schema=False) def page_region(slug: str): info = _maps()["region"].get(slug) if not info: return render_404(f"/region/{slug}") region = info["value"] return _listing_page( path=f"/region/{slug}", kind="auto", where="kind='auto' AND region=?", args=[region], h1=f"Voitures usagées à vendre — {region}", title_fmt=f"Voitures usagées à vendre — {region} ({{n}}) | Auto·Ka", desc_intro=f"Toutes les voitures usagées à vendre dans la région {region}.", filters={"region": region}, sections=[(f"Villes — {region}", _links_cities(region=region)), ("Autres régions", _links_regions(skip=region)), ("Marques populaires", _links_makes("auto", 15))], crumbs=[("Accueil", "/"), (region, None)]) @router.get("/ville/{slug}", response_model=None, include_in_schema=False) def page_city(slug: str): info = _maps()["city"].get(slug) if not info: return render_404(f"/ville/{slug}") city, region = info["value"], info["region"] region_slug = slugify(region) if region else None crumbs: list[tuple[str, str | None]] = [("Accueil", "/")] if region: crumbs.append((region, f"/region/{region_slug}")) crumbs.append((city, None)) return _listing_page( path=f"/ville/{slug}", kind="auto", where="kind='auto' AND city=?", args=[city], h1=f"Voitures usagées à vendre à {city}", title_fmt=f"Voitures usagées à vendre à {city} ({{n}}) | Auto·Ka", desc_intro=f"Toutes les voitures usagées à vendre chez les concessionnaires de {city}.", filters={"city": city}, sections=([(f"Autres villes — {region}", _links_cities(region=region, skip=city))] if region else []) + [("Marques populaires", _links_makes("auto", 15)), ("Par région", _links_regions())], crumbs=crumbs) @router.get("/carrosserie/{slug}", response_model=None, include_in_schema=False) def page_body(slug: str): info = _maps()["body"].get(slug) if not info: return render_404(f"/carrosserie/{slug}") body_type = info["value"] return _listing_page( path=f"/carrosserie/{slug}", kind="auto", where="kind='auto' AND body_type=?", args=[body_type], h1=f"{body_type} usagés à vendre au Québec", title_fmt=f"{body_type} usagés à vendre au Québec ({{n}}) | Auto·Ka", desc_intro=f"Tous les {body_type} usagés à vendre chez les concessionnaires du Québec.", filters={"body_type": body_type}, sections=[("Autres carrosseries", _links_bodies(skip=body_type)), ("Marques populaires", _links_makes("auto", 15))], crumbs=[("Accueil", "/"), (body_type, None)]) # --- routes : fiches véhicule ------------------------------------------------------ def _get_vehicle(uid: str) -> dict | None: con = db.connect() row = con.execute("SELECT * FROM vehicles WHERE uid=?", (uid,)).fetchone() v = dict(row) if row is not None else None if v is not None: v["images"] = json.loads(v.get("images") or "[]") v["features"] = json.loads(v.get("features") or "[]") if v.get("make") and v.get("model"): v["similar"] = [dict(r) for r in con.execute( "SELECT * FROM vehicles WHERE active=1 AND make=? AND model LIKE ?" " AND uid<>? ORDER BY price IS NULL, price ASC LIMIT 6", (v["make"], f'{v["model"].split()[0]}%', uid)).fetchall()] else: v["similar"] = [] con.close() return v def _fiche_crumbs(v: dict) -> list[tuple[str, str | None]]: crumbs: list[tuple[str, str | None]] = [("Accueil", "/")] if v.get("kind") == "moto": crumbs = [("Accueil", "/"), ("Motos", "/motos")] if v.get("make"): crumbs.append((v["make"], f'/motos/{slugify(v["make"])}')) elif v.get("kind") == "scooter": crumbs = [("Accueil", "/"), ("Scooters", "/scooters")] if v.get("make"): crumbs.append((v["make"], f'/scooters/{slugify(v["make"])}')) else: if v.get("make"): crumbs.append((f'{v["make"]} usagées', f'/usagees/{slugify(v["make"])}')) if v.get("model"): crumbs.append((f'{v["make"]} {v["model"]}', f'/usagees/{slugify(v["make"])}/{slugify(v["model"])}')) crumbs.append((v.get("title") or "Fiche", None)) return crumbs def _fiche_parent_path(v: dict) -> str: """Page catégorie la plus précise pour un véhicule (cible des liens 410).""" crumbs = _fiche_crumbs(v) for label, path in reversed(crumbs): if path: return path return "/" def _render_410(v: dict, path: str) -> HTMLResponse: parent = _fiche_parent_path(v) links = [f'' f'Voir les véhicules similaires →'] if v.get("make"): links.append(f'' f'Toutes les {esc(v["make"])} usagées') body = ('
🚗
' f'

{esc(v.get("title") or "Véhicule")} — vendu ou retiré

' "

Cette annonce n'est plus en ligne chez le concessionnaire. " "L'inventaire évolue chaque jour — voici où continuer :

" f'

{" ".join(links)}

') return render_page(path=path, title=f'{v.get("title") or "Véhicule"} — vendu ou retiré | Auto·Ka', description="Cette annonce a été retirée. Consultez les véhicules " "similaires à vendre au Québec sur Auto-Ka.", body=body, robots="noindex, follow", status=410) def _spec_rows(v: dict) -> list[tuple[str, str]]: rows = [ ("Année", str(v["year"]) if v.get("year") else ""), ("Kilométrage", fmt_km(v.get("mileage_km"))), ("Transmission", v.get("transmission") or ""), ("Carburant", v.get("fuel") or ""), ("Motricité", v.get("drivetrain") or ""), ("Carrosserie", v.get("body_type") or ""), ("Moteur", v.get("engine") or ""), ("Couleur ext.", v.get("exterior_color") or ""), ("Couleur int.", v.get("interior_color") or ""), ("Portes", str(v["doors"]) if v.get("doors") else ""), ("Places", str(v["seats"]) if v.get("seats") else ""), ("NIV (VIN)", v.get("vin") or ""), ("No de stock", v.get("stock_number") or ""), ] return [(k, val) for k, val in rows if val] @router.get("/vehicule/{uid}", response_model=None, include_in_schema=False) def fiche_redirect(uid: str): v = _get_vehicle(uid) if v is None: return render_404(f"/vehicule/{quote(uid, safe='')}") return RedirectResponse(_veh_path(v), status_code=301) @router.get("/vehicule/{uid}/{slug}", response_model=None, include_in_schema=False) def fiche(uid: str, slug: str): v = _get_vehicle(uid) if v is None: return render_404(f"/vehicule/{quote(uid, safe='')}/{slug}") canonical_path = _veh_path(v) if slug != _veh_slug(v): return RedirectResponse(canonical_path, status_code=301) if not v.get("active"): return _render_410(v, canonical_path) crumbs = _fiche_crumbs(v) where = " · ".join(x for x in [v.get("city"), v.get("region")] if x) dealer = v.get("dealer_name") or v.get("source") or "" img = _first_image(v) main_img = (f'{esc(v.get(' if img else '
🚗
') thumbs = "".join( f'{esc(v.get(' for i, u in enumerate(v["images"][1:8])) spec_html = "".join(f"{esc(k)}{esc(val)}" for k, val in _spec_rows(v)) feats = "".join(f'{esc(f)}' for f in v["features"][:50]) desc = "" if v.get("description"): desc = (f'

Description du concessionnaire

' f'
{esc(v["description"])}
') similar = "" if v["similar"]: similar = (f'

{esc(v.get("make") or "")} ' f'{esc(v.get("model") or "")} similaires au Québec

' + "".join(_card(s) for s in v["similar"]) + "
") body = ( '
' + _crumbs_html(crumbs) + '
' f'{esc(dealer)}{" — " + esc(where) if where else ""}' f'

{esc(v.get("title") or "")}

' f'
{fmt_price(v.get("price"))}
' '
' f'
{main_img}
' + (f'
{thumbs}
' if thumbs else "") + "
" + desc + "
" '

Caractéristiques

' f'{spec_html}
' + (f'' f"Voir chez {esc(dealer or 'le concessionnaire')} →" if v.get("url") else "") + '
annonce originale — prix et disponibilité confirmés à la source
' + (f'

Équipements ({len(v["features"])})

' f'
{feats}
' if feats else "") + "
" + similar + "
") bits = [str(v["year"]) if v.get("year") else None, fmt_km(v.get("mileage_km")) or None, v.get("transmission") or None, v.get("fuel") or None] description = (f'{v.get("title") or ""} à vendre : {", ".join(b for b in bits if b)}. ' f'{fmt_price(v.get("price"))} chez {dealer}' + (f" à {v['city']}" if v.get("city") else "") + (f" ({v['region']})" if v.get("region") else "") + ". Fiche complète, historique de prix et annonce originale sur Auto-Ka.") title = (f'{v.get("title") or "Véhicule"} — {fmt_price(v.get("price"))}' + (f' | {v["city"]}' if v.get("city") else "") + " | Auto·Ka") return render_page(path=canonical_path, title=title, description=description, body=body, og_image=img, og_type="product", jsonld=[_ld_crumbs(crumbs), _ld_vehicle(v, BASE_URL + canonical_path)]) # --- routes : stats, sources, profil ------------------------------------------------- @router.get("/stats", response_model=None, include_in_schema=False) def page_stats(): con = db.connect() st = _stats(con, "kind='auto'", []) by_region = [dict(r) for r in con.execute( "SELECT region, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles" " WHERE active=1 AND kind='auto' AND region<>'' GROUP BY region ORDER BY n DESC")] by_make = [dict(r) for r in con.execute( "SELECT make, COUNT(*) n, ROUND(AVG(price)) avg_price FROM vehicles" " WHERE active=1 AND kind='auto' AND make<>'' GROUP BY make ORDER BY n DESC LIMIT 20")] con.close() n_txt = f'{st["n"]:,}'.replace(",", " ") def _table(rows, key, href): trs = "".join( f'{esc(r[key])}' f'{r["n"]}{fmt_price(r["avg_price"])}' for r in rows) return ('' f"{trs}
véhiculesprix moyen
") body = ( '
' 'Statistiques en direct' "

Le marché des voitures usagées au Québec, en chiffres

" f'

{n_txt} voitures usagées en vente en ce moment. ' f'Prix moyen {fmt_price(st["avg_p"])}, médian {fmt_price(st["med_p"])}.

' '

Par région

' + _table(by_region, "region", lambda r: "/region/" + slugify(r["region"])) + '

Marques les plus offertes

' + _table(by_make, "make", lambda r: "/usagees/" + slugify(r["make"])) + "
") return render_page( path="/stats", title="Statistiques du marché des voitures usagées au Québec | Auto·Ka", description=f"Le marché de l'occasion au Québec en direct : {n_txt} véhicules, " f"prix moyen {fmt_price(st['avg_p'])}, répartition par région, marque et " "carrosserie. Données agrégées des concessionnaires.", body=body) @router.get("/sources", response_model=None, include_in_schema=False) def page_sources(): registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"] con = db.connect() counts = {r["source"]: r["n"] for r in con.execute( "SELECT source, COUNT(*) n FROM vehicles WHERE active=1 GROUP BY source")} con.close() items = "".join( f'
  • {esc(s["name"])}' + (f' — {esc(s["city"])}' if s.get("city") else "") + f' ({counts.get(s["id"], 0)})
    • ' for s in registry) body = ( '
    ' 'Transparence des sources' f"

    {len(registry)} concessionnaires connectés à Auto-Ka

    " '

    Les annonces proviennent directement des sites publics des ' "concessionnaires — pas des plateformes d'annonces — et sont rafraîchies " "automatiquement. Chaque fiche renvoie vers l'annonce originale.

    " f'
    ') return render_page( path="/sources", title=f"{len(registry)} concessionnaires sources | Auto·Ka", description=f"Auto-Ka agrège les inventaires de {len(registry)} concessionnaires " "du Québec, directement depuis leurs sites. Liste complète des sources et " "nombre d'annonces actives.", body=body) @router.get("/profil", response_model=None, include_in_schema=False) def page_profil(): return render_page(path="/profil", title="Mon profil | Auto·Ka", description="Votre profil Auto-Ka.", robots="noindex, nofollow") @router.get("/contact", response_model=None, include_in_schema=False) def page_contact(): """Page contact — coordonnées officielles du Groupe KA (rendu React).""" return render_page( path="/contact", title="Contact — Auto-Ka, un service Groupe KA", description="Joindre le Groupe KA : contact@groupe-ka.com (projets, " "partenariats et données), info@groupe-ka.com (médias) et " "admin@groupe-ka.com (légal, vie privée et Loi 25). Auto-Ka est un " "service Groupe KA.") # --- robots.txt et sitemaps ----------------------------------------------------------- @router.get("/robots.txt", response_model=None, include_in_schema=False) def robots_txt(): return PlainTextResponse( "User-agent: *\n" "Allow: /\n" "Disallow: /api/\n" "Disallow: /profil\n" "\n" f"Sitemap: {BASE_URL}/sitemap.xml\n") def _xml(content: str) -> Response: return Response(content=content, media_type="application/xml", headers={"Cache-Control": "no-cache, must-revalidate"}) @router.get("/sitemap.xml", response_model=None, include_in_schema=False) def sitemap_index(): con = db.connect() row = con.execute("SELECT COUNT(*) n, MAX(updated_at) lm FROM vehicles" " WHERE active=1").fetchone() con.close() lastmod = _date(row["lm"]) chunks = max(1, math.ceil(row["n"] / SITEMAP_CHUNK)) entries = [f"{BASE_URL}/sitemaps/pages.xml"] + [ f"{BASE_URL}/sitemaps/vehicules-{i}.xml" for i in range(1, chunks + 1)] body = "".join(f"{esc(u)}{lastmod}" for u in entries) return _xml('' '' + body + "") @router.get("/sitemaps/pages.xml", response_model=None, include_in_schema=False) def sitemap_pages(): m = _maps() today = datetime.now(tz=timezone.utc).strftime("%Y-%m-%d") urls: list[tuple[str, str]] = [("/", today), ("/motos", today), ("/scooters", today), ("/stats", today), ("/sources", today), ("/contact", today)] for kind, prefix in (("auto", "/usagees/"), ("moto", "/motos/"), ("scooter", "/scooters/")): for slug, info in m["make"].get(kind, {}).items(): urls.append((prefix + slug, _date(info["lm"]))) for make_slug, models in m["model"].items(): for slug, info in models.items(): if info["n"] >= 2: urls.append((f"/usagees/{make_slug}/{slug}", _date(info["lm"]))) for slug, info in m["region"].items(): urls.append((f"/region/{slug}", _date(info["lm"]))) for slug, info in m["city"].items(): if info["n"] >= 2: urls.append((f"/ville/{slug}", _date(info["lm"]))) for slug, info in m["body"].items(): urls.append((f"/carrosserie/{slug}", _date(info["lm"]))) body = "".join(f"{esc(BASE_URL + p)}{lm}" for p, lm in urls) return _xml('' '' + body + "") @router.get("/sitemaps/vehicules-{num}.xml", response_model=None, include_in_schema=False) def sitemap_vehicles(num: int): if num < 1: return render_404(f"/sitemaps/vehicules-{num}.xml") con = db.connect() rows = con.execute( "SELECT uid, year, make, model, trim, title, updated_at FROM vehicles" " WHERE active=1 ORDER BY uid LIMIT ? OFFSET ?", (SITEMAP_CHUNK, (num - 1) * SITEMAP_CHUNK)).fetchall() con.close() if not rows: return render_404(f"/sitemaps/vehicules-{num}.xml") body = "".join( f"{esc(BASE_URL + _veh_path(dict(r)))}" f"{_date(r['updated_at'])}" for r in rows) return _xml('' '' + body + "")