Python 67%
TypeScript 18.2%
CSS 14.4%
1# -----------------------------------------------------------------------------2# House-Ka — Homes-for-sale aggregator (Canada outside Québec, Ontario first)3# Author: Simon-Pierre Boucher — contact@spboucher.ai4# seo.py : server-side HTML rendering for search engines.5#6# The React SPA is untouched: this module pre-fills the initial HTML served by7# the web.py catch-all — unique <title>/meta/canonical/og:, schema.org JSON-LD,8# content and internal links inside <div id="root"> (replaced by React on9# mount). Also generates robots.txt and the sitemaps.10#11# Pages served:12# / enriched home (stats + city/type links)13# /property/{uid}[/{slug}] listing page (301 to the canonical slug,14# 410 if withdrawn, 404 if unknown)15# /for-sale/{city}[/{type}] programmatic city pages (+ type)16# /type/{type} per-property-type page (Canada-wide)17# /stats /agencies /terms /privacy /account /rates /contact dedicated meta18# /robots.txt /sitemap.xml /sitemaps/*.xml19# -----------------------------------------------------------------------------20from __future__ import annotations2122import html23import json24import os25import re26import time27import unicodedata28from datetime import datetime, timezone29from pathlib import Path30from urllib.parse import quote3132from fastapi.responses import HTMLResponse, PlainTextResponse, RedirectResponse, Response3334from . import db3536ROOT = Path(__file__).resolve().parent.parent37FRONTEND_DIST = ROOT / "frontend" / "dist"38FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"3940BASE_URL = os.environ.get("IMMOKA_BASE_URL", "https://www.house-ka.com").rstrip("/")41SITE_NAME = "House-Ka"4243# same visibility rule as /api/listings (DDF dedup + displayable price)44VISIBLE = "active=1 AND dup_hidden=0 AND published=1"45MIN_LISTINGS = 3 # quality floor: no near-empty city/type pages46PAGE_SIZE = 48 # listings per page on programmatic pages4748# province name (region column) -> two-letter code for schema.org49_PROVINCE_CODE = {50 "ontario": "ON", "british columbia": "BC", "alberta": "AB",51 "saskatchewan": "SK", "manitoba": "MB", "new brunswick": "NB",52 "nova scotia": "NS", "prince edward island": "PE",53 "newfoundland and labrador": "NL", "yukon": "YT",54 "northwest territories": "NT", "nunavut": "NU", "quebec": "QC",55}5657# -----------------------------------------------------------------------------58# Utilities59# -----------------------------------------------------------------------------6061def slugify(s: str) -> str:62 """URL slug — SAME algorithm as slugify() in frontend/src/api.ts."""63 s = unicodedata.normalize("NFKD", s or "").encode("ascii", "ignore").decode()64 s = re.sub(r"[^a-z0-9]+", "-", s.lower()).strip("-")65 return s[:80].strip("-")666768def listing_slug(row) -> str:69 """Listing slug — SAME logic as listingPath() in frontend/src/api.ts."""70 base = slugify(row["address"] or row["title"] or "")71 city = slugify(row["city"] or "")72 if city and city not in base:73 base = slugify(f"{base} {city}") if base else city74 return base757677def _esc(s) -> str:78 return html.escape(str(s or ""), quote=True)798081def _fmt_n(n) -> str:82 return f"{int(n):,}"838485def _fmt_price(p) -> str:86 return f"${_fmt_n(round(p))}" if p is not None else "Price on request"878889def _iso_date(ts) -> str:90 try:91 return datetime.fromtimestamp(float(ts), tz=timezone.utc).strftime("%Y-%m-%d")92 except (TypeError, ValueError):93 return datetime.now(tz=timezone.utc).strftime("%Y-%m-%d")949596# small in-memory TTL cache (data changes at watch pace, ~1 h)97_cache: dict[str, tuple[float, object]] = {}9899100def _cached(key: str, ttl: float, build):101 now = time.time()102 hit = _cache.get(key)103 if hit and now - hit[0] < ttl:104 return hit[1]105 val = build()106 _cache[key] = (now, val)107 return val108109110# -----------------------------------------------------------------------------111# Slug registries (cities, types) — rebuilt every 15 min112# -----------------------------------------------------------------------------113114def _build_registry() -> dict:115 con = db.connect()116 cities: dict[str, dict] = {}117 for r in con.execute(118 f"SELECT city, COUNT(*) n FROM listings WHERE {VISIBLE}"119 " AND city<>'' GROUP BY city"):120 slug = slugify(r["city"])121 if len(slug) < 2:122 continue123 e = cities.setdefault(slug, {"label": r["city"], "n": 0, "values": [], "best": 0})124 e["n"] += r["n"]125 e["values"].append(r["city"])126 if r["n"] > e["best"]:127 e["best"] = r["n"]; e["label"] = r["city"]128 types: dict[str, dict] = {}129 for r in con.execute(130 f"SELECT property_type, COUNT(*) n FROM listings WHERE {VISIBLE}"131 " AND property_type<>'' GROUP BY property_type"):132 slug = slugify(r["property_type"])133 if len(slug) < 2:134 continue135 e = types.setdefault(slug, {"label": r["property_type"], "n": 0, "values": [], "best": 0})136 e["n"] += r["n"]137 e["values"].append(r["property_type"])138 if r["n"] > e["best"]:139 e["best"] = r["n"]; e["label"] = r["property_type"]140 city_types: dict[tuple[str, str], int] = {}141 for r in con.execute(142 f"SELECT city, property_type, COUNT(*) n FROM listings WHERE {VISIBLE}"143 " AND city<>'' AND property_type<>'' GROUP BY city, property_type"):144 cs, ts_ = slugify(r["city"]), slugify(r["property_type"])145 if len(cs) < 2 or len(ts_) < 2:146 continue147 city_types[(cs, ts_)] = city_types.get((cs, ts_), 0) + r["n"]148 con.close()149 return {"cities": cities, "types": types, "city_types": city_types}150151152def registry() -> dict:153 return _cached("registry", 900, _build_registry)154155156# -----------------------------------------------------------------------------157# Template: dist/index.html, stripped of its static <title>/description158# -----------------------------------------------------------------------------159160_tpl_cache: tuple[float, str] | None = None161162163def _template() -> str:164 global _tpl_cache165 path = FRONTEND_DIR / "index.html"166 mtime = path.stat().st_mtime167 if _tpl_cache and _tpl_cache[0] == mtime:168 return _tpl_cache[1]169 tpl = path.read_text(encoding="utf-8")170 tpl = re.sub(r"<title>.*?</title>\s*", "", tpl, flags=re.S)171 tpl = re.sub(r'<meta name="description"[^>]*>\s*', "", tpl)172 tpl = re.sub(r'<meta (?:property="og:|name="twitter:)[^>]*>\s*', "", tpl)173 _tpl_cache = (mtime, tpl)174 return tpl175176177def _page(title: str, description: str, canonical: str, body: str,178 jsonld: list[dict] | None = None, og_image: str | None = None,179 og_type: str = "website", noindex: bool = False,180 status: int = 200) -> HTMLResponse:181 head = [182 f"<title>{_esc(title)}</title>",183 f'<meta name="description" content="{_esc(description)}" />',184 f'<link rel="canonical" href="{_esc(canonical)}" />',185 f'<meta property="og:site_name" content="{SITE_NAME}" />',186 '<meta property="og:locale" content="en_CA" />',187 f'<meta property="og:type" content="{og_type}" />',188 f'<meta property="og:title" content="{_esc(title)}" />',189 f'<meta property="og:description" content="{_esc(description)}" />',190 f'<meta property="og:url" content="{_esc(canonical)}" />',191 ]192 if og_image:193 head.append(f'<meta property="og:image" content="{_esc(og_image)}" />')194 else:195 og_image = BASE_URL + "/og.png"196 head.append(f'<meta property="og:image" content="{_esc(og_image)}" />')197 head.append('<meta property="og:image:width" content="1200" />')198 head.append('<meta property="og:image:height" content="630" />')199 head.append('<meta name="twitter:card" content="summary_large_image" />')200 head.append(f'<meta name="twitter:image" content="{_esc(og_image)}" />')201 if noindex:202 head.append('<meta name="robots" content="noindex" />')203 for obj in (jsonld or []):204 head.append('<script type="application/ld+json">'205 + json.dumps(obj, ensure_ascii=False) + "</script>")206 tpl = _template()207 out = tpl.replace("</head>", " " + "\n ".join(head) + "\n </head>", 1)208 out = out.replace('<div id="root"></div>',209 f'<div id="root"><div class="container seo-ssr">{body}</div></div>', 1)210 return HTMLResponse(out, status_code=status)211212213# -----------------------------------------------------------------------------214# Reusable HTML blocks215# -----------------------------------------------------------------------------216217def fiche_href(uid: str, slug: str = "") -> str:218 path = f"/property/{quote(uid, safe='')}"219 return path + (f"/{slug}" if slug else "")220221222def _item_li(r) -> str:223 href = fiche_href(r["uid"], listing_slug(r))224 bits = [b for b in [225 r["property_type"],226 f"{r['bedrooms']} bed" if r["bedrooms"] is not None else "",227 f"{r['bathrooms']} bath" if r["bathrooms"] is not None else "",228 f"{_fmt_n(round(r['area_sqft']))} sq ft" if r["area_sqft"] else "",229 ] if b]230 name = r["address"] or r["title"] or "Property"231 loc = ", ".join(x for x in [r["sector"], r["city"]] if x)232 return (f'<li><a href="{href}"><strong>{_esc(name)}</strong></a> — '233 f'{_esc(_fmt_price(r["price"]))}'234 + (f' · {_esc(" · ".join(bits))}' if bits else "")235 + (f' · {_esc(loc)}' if loc else "") + "</li>")236237238def _agg_stats(con, cities: list[str] | None = None,239 types_values: list[str] | None = None) -> dict:240 where = VISIBLE241 args: list = []242 if cities:243 where += f" AND city IN ({','.join('?' * len(cities))})"244 args += cities245 if types_values:246 where += f" AND property_type IN ({','.join('?' * len(types_values))})"247 args += types_values248 row = con.execute(249 f"SELECT COUNT(*) n, AVG(price) avg_p, MIN(price) min_p, MAX(price) max_p"250 f" FROM listings WHERE {where}", args).fetchone()251 med = None252 if row["n"]:253 med_row = con.execute(254 f"SELECT price FROM listings WHERE {where}"255 f" ORDER BY price LIMIT 1 OFFSET ?", args + [row["n"] // 2]).fetchone()256 med = med_row["price"] if med_row else None257 return {"n": row["n"], "avg": row["avg_p"], "med": med,258 "min": row["min_p"], "max": row["max_p"], "where": where, "args": args}259260261def _pagination_html(base_path: str, page: int, pages: int) -> str:262 if pages <= 1:263 return ""264 out = ['<nav class="seo-pages" aria-label="Pagination">']265 if page > 1:266 prev = base_path if page == 2 else f"{base_path}?page={page - 1}"267 out.append(f'<a rel="prev" href="{prev}">← Previous page</a> ')268 out.append(f"<span>Page {page} of {pages}</span>")269 if page < pages:270 out.append(f' <a rel="next" href="{base_path}?page={page + 1}">Next page →</a>')271 out.append("</nav>")272 return "".join(out)273274275def _breadcrumb_ld(crumbs: list[tuple[str, str]]) -> dict:276 return {277 "@context": "https://schema.org",278 "@type": "BreadcrumbList",279 "itemListElement": [280 {"@type": "ListItem", "position": i + 1, "name": name,281 "item": BASE_URL + path}282 for i, (name, path) in enumerate(crumbs)283 ],284 }285286287# -----------------------------------------------------------------------------288# Home289# -----------------------------------------------------------------------------290291def _home_data() -> dict:292 def build():293 con = db.connect()294 row = con.execute(295 f"SELECT COUNT(*) total, COUNT(DISTINCT city) cities,"296 f" COUNT(DISTINCT source) sources, AVG(price) avg_p"297 f" FROM listings WHERE {VISIBLE}").fetchone()298 recent = [dict(r) for r in con.execute(299 f"SELECT uid, address, title, city, sector, property_type, price,"300 f" bedrooms, bathrooms, area_sqft FROM listings WHERE {VISIBLE}"301 f" ORDER BY first_seen DESC LIMIT 12")]302 con.close()303 return {**dict(row), "recent": recent}304 return _cached("home", 900, build)305306307def render_home() -> HTMLResponse:308 d = _home_data()309 reg = registry()310 total = _fmt_n(d["total"])311 title = f"House-Ka — {total} homes for sale across Canada · A Groupe KA service"312 desc = (f"{total} homes for sale in {_fmt_n(d['cities'])} Canadian cities and towns, "313 f"aggregated from {d['sources']} brokerage sources on the CREA DDF feed — "314 f"continuously updated. Average asking price: {_fmt_price(d['avg_p'])}.")315 top_cities = sorted(reg["cities"].items(), key=lambda kv: -kv[1]["n"])[:60]316 types = sorted(reg["types"].items(), key=lambda kv: -kv[1]["n"])317 body = [318 f"<h1>Homes for sale across Canada — {total} listings from real-estate brokerages</h1>",319 f"<p>House-Ka continuously aggregates homes for sale publicly listed by Canadian "320 f"real-estate brokerages and teams (CREA DDF feed) — {d['sources']} sources, "321 f"{_fmt_n(d['cities'])} cities, average asking price {_esc(_fmt_price(d['avg_p']))}. "322 f"Every listing links back to the brokerage's original page. Coverage starts with "323 f"Ontario and grows across the rest of Canada. Looking for Québec? "324 f'See our sister site <a href="https://www.immo-ka.com" rel="noopener">Immo-Ka</a>.</p>',325 "<h2>Homes for sale by city</h2>",326 "<ul>" + "".join(327 f'<li><a href="/for-sale/{s}">Homes for sale in {_esc(e["label"])}</a>'328 f" ({_fmt_n(e['n'])})</li>"329 for s, e in top_cities if e["n"] >= MIN_LISTINGS) + "</ul>",330 "<h2>By property type</h2>",331 "<ul>" + "".join(332 f'<li><a href="/type/{s}">{_esc(e["label"])} for sale in Canada</a>'333 f" ({_fmt_n(e['n'])})</li>"334 for s, e in types if e["n"] >= MIN_LISTINGS) + "</ul>",335 "<h2>Latest listings</h2>",336 "<ul>" + "".join(_item_li(r) for r in d["recent"]) + "</ul>",337 '<p><a href="/stats">Market statistics</a> · '338 '<a href="/agencies">Covered brokerages</a> · '339 '<a href="/rates">Mortgage rates</a></p>',340 ]341 jsonld = [{342 "@context": "https://schema.org",343 "@type": "WebSite",344 "name": SITE_NAME,345 "url": BASE_URL + "/",346 "inLanguage": "en-CA",347 "description": desc,348 "potentialAction": {349 "@type": "SearchAction",350 "target": {"@type": "EntryPoint",351 "urlTemplate": BASE_URL + "/?q={search_term_string}"},352 "query-input": "required name=search_term_string",353 },354 }, {355 "@context": "https://schema.org",356 "@type": "Organization",357 "name": "Groupe-Ka",358 "url": BASE_URL + "/",359 "email": "contact@groupe-ka.com",360 }]361 return _page(title, desc, BASE_URL + "/", "".join(body), jsonld)362363364# -----------------------------------------------------------------------------365# Programmatic pages: city, city+type, type366# -----------------------------------------------------------------------------367368def _render_category(city_slug: str | None, type_slug: str | None,369 page: int) -> HTMLResponse:370 reg = registry()371 city = reg["cities"].get(city_slug) if city_slug else None372 ptype = reg["types"].get(type_slug) if type_slug else None373 if (city_slug and not city) or (type_slug and not ptype):374 return render_404()375 if city_slug and type_slug and reg["city_types"].get((city_slug, type_slug), 0) < 1:376 return render_404()377378 con = db.connect()379 st = _agg_stats(con, city["values"] if city else None,380 ptype["values"] if ptype else None)381 if st["n"] < 1:382 con.close()383 return render_404()384385 pages = max(1, -(-st["n"] // PAGE_SIZE))386 if page < 1 or page > pages:387 con.close()388 return render_404()389 rows = con.execute(390 f"SELECT uid, address, title, city, sector, property_type, price,"391 f" bedrooms, bathrooms, area_sqft FROM listings WHERE {st['where']}"392 f" ORDER BY price IS NULL, price ASC LIMIT ? OFFSET ?",393 st["args"] + [PAGE_SIZE, (page - 1) * PAGE_SIZE]).fetchall()394395 # internal linking396 links = []397 if city:398 tlinks = []399 for (cs, ts_), n in sorted(reg["city_types"].items(), key=lambda kv: -kv[1]):400 if cs == city_slug and n >= 1 and ts_ in reg["types"] and ts_ != type_slug:401 lbl = reg["types"][ts_]["label"]402 tlinks.append(f'<li><a href="/for-sale/{cs}/{ts_}">'403 f"{_esc(lbl)} for sale in {_esc(city['label'])}</a> ({_fmt_n(n)})</li>")404 if tlinks:405 links.append("<h2>Other property types in "406 + _esc(city["label"]) + "</h2><ul>" + "".join(tlinks[:20]) + "</ul>")407 if type_slug:408 links.append(f'<p><a href="/for-sale/{city_slug}">All homes for sale '409 f"in {_esc(city['label'])}</a> · "410 f'<a href="/type/{type_slug}">{_esc(ptype["label"])} for sale in Canada</a></p>')411 top = sorted(reg["cities"].items(), key=lambda kv: -kv[1]["n"])[:30]412 links.append("<h2>Other cities</h2><ul>" + "".join(413 f'<li><a href="/for-sale/{s}{"/" + type_slug if type_slug and (s, type_slug) in reg["city_types"] else ""}">'414 f'Homes for sale in {_esc(e["label"])}</a> ({_fmt_n(e["n"])})</li>'415 for s, e in top if s != city_slug and e["n"] >= MIN_LISTINGS) + "</ul>")416 con.close()417418 if city and ptype:419 base_path = f"/for-sale/{city_slug}/{type_slug}"420 h1 = f"{ptype['label']} for sale in {city['label']}"421 what = f"{ptype['label'].lower()} listings in {city['label']}"422 elif city:423 base_path = f"/for-sale/{city_slug}"424 h1 = f"Homes for sale in {city['label']}"425 what = f"homes for sale in {city['label']}"426 else:427 base_path = f"/type/{type_slug}"428 h1 = f"{ptype['label']} for sale in Canada"429 what = f"{ptype['label'].lower()} listings across Canada"430431 canonical = BASE_URL + base_path + (f"?page={page}" if page > 1 else "")432 title = f"{h1} — {_fmt_n(st['n'])} listings" + (f" (page {page})" if page > 1 else "") + " | House-Ka"433 desc = (f"{_fmt_n(st['n'])} {what}: median price {_fmt_price(st['med'])}, "434 f"average price {_fmt_price(st['avg'])}. Listings from Canadian brokerages "435 f"on the CREA DDF feed, continuously updated.")436 stats_p = (f"<p><strong>{_fmt_n(st['n'])}</strong> listings · median price "437 f"<strong>{_esc(_fmt_price(st['med']))}</strong> · average price "438 f"<strong>{_esc(_fmt_price(st['avg']))}</strong> · from "439 f"{_esc(_fmt_price(st['min']))} to {_esc(_fmt_price(st['max']))}.</p>")440 crumbs = [("Home", "/")]441 if city:442 crumbs.append((f"For sale in {city['label']}", f"/for-sale/{city_slug}"))443 if ptype:444 crumbs.append((f"{ptype['label']}", base_path))445 else:446 crumbs.append((h1, base_path))447 body = ('<nav aria-label="Breadcrumb">'448 + " › ".join(f'<a href="{p}">{_esc(n)}</a>' for n, p in crumbs)449 + f"</nav><h1>{_esc(h1)}</h1>" + stats_p450 + "<ul>" + "".join(_item_li(r) for r in rows) + "</ul>"451 + _pagination_html(base_path, page, pages)452 + "".join(links))453 return _page(title, desc, canonical, body, [_breadcrumb_ld(crumbs)])454455456# -----------------------------------------------------------------------------457# Listing page458# -----------------------------------------------------------------------------459460_TYPE_SCHEMA = {461 "house": "SingleFamilyResidence", "condo": "Apartment",462 "cottage": "House", "semi-detached": "House", "townhouse": "House",463 "duplex": "Residence", "triplex": "Residence",464 "multi-family": "Residence", "mobile-home": "House",465}466467468def render_listing(uid: str, slug: str | None) -> Response:469 con = db.connect()470 row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()471 con.close()472 if row is None:473 return render_404()474475 city_slug = slugify(row["city"] or "")476 city_known = city_slug in registry()["cities"]477 city_href = f"/for-sale/{city_slug}" if city_known else "/"478479 if not row["active"]:480 # withdrawn / sold → 410 Gone, with escape hatches481 name = row["address"] or row["title"] or "Property"482 body = (f"<h1>This property is no longer for sale</h1>"483 f"<p>The listing “{_esc(name)}” ({_esc(row['city'] or 'Canada')}) has been "484 f"withdrawn or sold.</p><ul>"485 + (f'<li><a href="{city_href}">Homes for sale in '486 f"{_esc(row['city'])}</a></li>" if city_known else "")487 + '<li><a href="/">All homes for sale across Canada</a></li></ul>')488 return _page(f"Listing withdrawn — {name} | {SITE_NAME}",489 "This listing has been withdrawn or sold.",490 BASE_URL + fiche_href(uid), body, noindex=True, status=410)491492 expected = listing_slug(row)493 if expected and slug != expected:494 return RedirectResponse(BASE_URL + fiche_href(uid, expected), status_code=301)495496 d = dict(row)497 images = json.loads(d.get("images") or "[]")498 features = json.loads(d.get("features") or "[]")499 name = d["address"] or d["title"] or "Property for sale"500 loc = ", ".join(x for x in [d["sector"], d["city"]] if x) or "Canada"501 canonical = BASE_URL + fiche_href(uid, expected)502 ptype = d["property_type"] or "Property"503504 specs = [(lbl, val) for lbl, val in [505 ("Type", ptype),506 ("Price", _fmt_price(d["price"]) if d["price"] is not None else d["price_label"]),507 ("Bedrooms", d["bedrooms"]),508 ("Bathrooms", d["bathrooms"]),509 ("Half baths", d["powder_rooms"]),510 ("Living area", f"{_fmt_n(round(d['area_sqft']))} sq ft" if d["area_sqft"] else None),511 ("Lot", f"{_fmt_n(round(d['lot_sqft']))} sq ft" if d["lot_sqft"] else None),512 ("Year built", d["year_built"]),513 ("City", d["city"]),514 ("Neighbourhood", d["sector"]),515 ("MLS® number", d["mls"]),516 ("Agent", d["broker_name"]),517 ("Brokerage", d["agency"]),518 ] if val not in (None, "", 0)]519 descr = (d["description"] or "").strip()520 if len(descr) > 1500:521 descr = descr[:1500].rsplit(" ", 1)[0] + "…"522523 type_slug = slugify(ptype)524 crumbs = [("Home", "/")]525 if city_known:526 crumbs.append((f"For sale in {d['city']}", city_href))527 if (city_slug, type_slug) in registry()["city_types"]:528 crumbs.append((ptype, f"/for-sale/{city_slug}/{type_slug}"))529 crumbs.append((name, fiche_href(uid, expected)))530531 body = [532 '<nav aria-label="Breadcrumb">'533 + " › ".join(f'<a href="{p}">{_esc(n)}</a>' for n, p in crumbs[:-1])534 + f" › {_esc(name)}</nav>",535 f"<h1>{_esc(name)}</h1>",536 f"<p><strong>{_esc(ptype)} for sale in {_esc(loc)}</strong> — "537 f"{_esc(_fmt_price(d['price']) if d['price'] is not None else (d['price_label'] or 'Price on request'))}</p>",538 ]539 if images:540 body.append("".join(541 f'<img src="{_esc(u)}" alt="{_esc(name)} — photo {i + 1}" loading="lazy" />'542 for i, u in enumerate(images[:3])))543 body.append("<h2>Key facts</h2><ul>" + "".join(544 f"<li><strong>{_esc(l)}:</strong> {_esc(v)}</li>" for l, v in specs) + "</ul>")545 if descr:546 body.append(f"<h2>Description</h2><p>{_esc(descr)}</p>")547 if features:548 body.append("<h2>Details</h2><ul>" + "".join(549 f"<li>{_esc(f)}</li>" for f in features[:20]) + "</ul>")550 if d["url"]:551 body.append(f'<p><a href="{_esc(d["url"])}" rel="noopener">'552 f"See the original listing at {_esc(d['broker_name'] or d['agency'] or 'the brokerage')}</a></p>")553 if city_known:554 body.append(f'<p><a href="{city_href}">Homes for sale in {_esc(d["city"])}</a>'555 + (f' · <a href="/for-sale/{city_slug}/{type_slug}">{_esc(ptype)} for sale in '556 f'{_esc(d["city"])}</a>'557 if (city_slug, type_slug) in registry()["city_types"] else "") + "</p>")558559 about_type = _TYPE_SCHEMA.get(type_slug, "Residence")560 region_code = _PROVINCE_CODE.get((d["region"] or "").strip().lower(), "ON")561 about: dict = {562 "@type": about_type,563 "name": name,564 "address": {"@type": "PostalAddress",565 "streetAddress": d["address"] or None,566 "addressLocality": d["city"] or None,567 "addressRegion": region_code, "addressCountry": "CA"},568 }569 if d["lat"] is not None and d["lng"] is not None:570 about["geo"] = {"@type": "GeoCoordinates",571 "latitude": d["lat"], "longitude": d["lng"]}572 if d["bedrooms"] is not None:573 about["numberOfBedrooms"] = d["bedrooms"]574 if d["bathrooms"] is not None:575 about["numberOfBathroomsTotal"] = d["bathrooms"]576 if d["area_sqft"]:577 about["floorSize"] = {"@type": "QuantitativeValue",578 "value": round(d["area_sqft"]), "unitCode": "FTK"}579 if d["year_built"]:580 about["yearBuilt"] = d["year_built"]581 about = {k: v for k, v in about.items() if v is not None}582 about["address"] = {k: v for k, v in about["address"].items() if v is not None}583 jsonld: list[dict] = [{584 "@context": "https://schema.org",585 "@type": "RealEstateListing",586 "name": name,587 "url": canonical,588 "inLanguage": "en-CA",589 "datePosted": _iso_date(d.get("first_seen")),590 "image": images[:6] or None,591 "about": about,592 }, _breadcrumb_ld(crumbs)]593 if d["price"] is not None:594 jsonld[0]["offers"] = {"@type": "Offer", "price": round(d["price"], 2),595 "priceCurrency": "CAD",596 "availability": "https://schema.org/InStock"}597 jsonld[0] = {k: v for k, v in jsonld[0].items() if v is not None}598599 title = f"{name} — {ptype} for sale, {d['city'] or 'Canada'} | {_fmt_price(d['price']) if d['price'] is not None else 'Price on request'}"600 meta_desc = (f"{ptype} for sale in {loc}"601 + (f", {d['bedrooms']} bedrooms" if d["bedrooms"] else "")602 + (f", {_fmt_n(round(d['area_sqft']))} sq ft" if d["area_sqft"] else "")603 + f" — {_fmt_price(d['price']) if d['price'] is not None else 'price on request'}. "604 + (descr[:120] + "…" if len(descr) > 120 else descr))605 return _page(title, meta_desc, canonical, "".join(body), jsonld,606 og_image=images[0] if images else None, og_type="article")607608609# -----------------------------------------------------------------------------610# Static SPA pages (dedicated meta) and 404611# -----------------------------------------------------------------------------612613_STATIC_META = {614 "/stats": ("Canadian housing market statistics",615 "Average prices, listing volumes by source and data quality — "616 "continuous statistics from House-Ka.",617 False),618 "/agencies": ("Covered brokerages",619 "All the Canadian real-estate brokerages and teams aggregated by "620 "House-Ka through the CREA DDF feed.",621 False),622 "/rates": (623 "Mortgage rates in Canada — live comparator",624 "Compare mortgage rates actually published by RBC, TD, BMO, CIBC, "625 "Scotiabank, NBC, Desjardins, Tangerine, EQ and more — fixed and variable, "626 "with official source, freshness and history. Continuously collected by House-Ka.",627 False),628 "/terms": ("Terms of use",629 "Terms of use of the House-Ka platform (Groupe-Ka).", False),630 "/privacy": ("Privacy policy",631 "House-Ka privacy policy (Groupe-Ka) — PIPEDA.", False),632 "/account": ("My account", "Your Groupe KA account on House-Ka.", True),633 "/contact": ("Contact — Groupe KA",634 "Write to Groupe KA: contact@groupe-ka.com (projects and data), "635 "info@groupe-ka.com (media), admin@groupe-ka.com (legal and privacy). "636 "House-Ka is a Groupe KA service — https://www.groupe-ka.com.",637 False),638}639640641def render_static(path: str) -> HTMLResponse:642 t, desc, noindex = _STATIC_META[path]643 body = f"<h1>{_esc(t)}</h1><p>{_esc(desc)}</p>"644 return _page(f"{t} | {SITE_NAME}", desc, BASE_URL + path, body, noindex=noindex)645646647def render_404() -> HTMLResponse:648 body = ('<h1>Page not found</h1><p>The requested link does not exist.</p>'649 '<p><a href="/">All homes for sale across Canada</a></p>')650 return _page(f"Page not found | {SITE_NAME}", "Page not found.",651 BASE_URL + "/", body, noindex=True, status=404)652653654# -----------------------------------------------------------------------------655# robots.txt and sitemaps656# -----------------------------------------------------------------------------657658FICHES_PER_SITEMAP = 40000659660661def robots_txt() -> PlainTextResponse:662 return PlainTextResponse(663 "User-agent: *\n"664 "Allow: /\n"665 "Disallow: /api/\n"666 "Disallow: /account\n"667 f"\nSitemap: {BASE_URL}/sitemap.xml\n")668669670def _xml(urls: list[str]) -> Response:671 body = ('<?xml version="1.0" encoding="UTF-8"?>\n'672 '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'673 + "\n".join(urls) + "\n</urlset>")674 return Response(body, media_type="application/xml")675676677def _url_el(loc: str, lastmod: str | None = None) -> str:678 lm = f"<lastmod>{lastmod}</lastmod>" if lastmod else ""679 return f" <url><loc>{html.escape(loc)}</loc>{lm}</url>"680681682def sitemap_index() -> Response:683 def build():684 con = db.connect()685 n = con.execute(f"SELECT COUNT(*) c FROM listings WHERE {VISIBLE}").fetchone()["c"]686 last = con.execute(687 f"SELECT MAX(updated_at) m FROM listings WHERE {VISIBLE}").fetchone()["m"]688 con.close()689 parts = -(-n // FICHES_PER_SITEMAP) or 1690 lm = _iso_date(last)691 maps = [f"{BASE_URL}/sitemaps/listings-{i + 1}.xml" for i in range(parts)]692 maps += [f"{BASE_URL}/sitemaps/cities.xml",693 f"{BASE_URL}/sitemaps/cities-types.xml",694 f"{BASE_URL}/sitemaps/types.xml",695 f"{BASE_URL}/sitemaps/pages.xml"]696 body = ('<?xml version="1.0" encoding="UTF-8"?>\n'697 '<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'698 + "\n".join(f" <sitemap><loc>{html.escape(m)}</loc>"699 f"<lastmod>{lm}</lastmod></sitemap>" for m in maps)700 + "\n</sitemapindex>")701 return body702 return Response(_cached("sm:index", 3600, build), media_type="application/xml")703704705def sitemap_file(name: str) -> Response:706 m = re.fullmatch(r"listings-(\d+)\.xml", name)707 if m:708 part = int(m.group(1))709710 def build():711 con = db.connect()712 rows = con.execute(713 f"SELECT uid, address, title, city, updated_at FROM listings"714 f" WHERE {VISIBLE} ORDER BY uid LIMIT ? OFFSET ?",715 (FICHES_PER_SITEMAP, (part - 1) * FICHES_PER_SITEMAP)).fetchall()716 con.close()717 if not rows:718 return None719 return [_url_el(BASE_URL + fiche_href(r["uid"], listing_slug(r)),720 _iso_date(r["updated_at"])) for r in rows]721 urls = _cached(f"sm:listings:{part}", 3600, build)722 if urls is None:723 return Response("Sitemap not found", status_code=404)724 return _xml(urls)725726 if name == "cities.xml":727 def build():728 reg = registry()729 lm = _city_lastmod()730 return [_url_el(f"{BASE_URL}/for-sale/{s}", lm.get(s))731 for s, e in sorted(reg["cities"].items())732 if e["n"] >= MIN_LISTINGS]733 return _xml(_cached("sm:cities", 3600, build))734735 if name == "cities-types.xml":736 def build():737 reg = registry()738 lm = _city_lastmod()739 return [_url_el(f"{BASE_URL}/for-sale/{cs}/{ts_}", lm.get(cs))740 for (cs, ts_), n in sorted(reg["city_types"].items())741 if n >= MIN_LISTINGS and cs in reg["cities"] and ts_ in reg["types"]742 and reg["cities"][cs]["n"] >= MIN_LISTINGS]743 return _xml(_cached("sm:cities-types", 3600, build))744745 if name == "types.xml":746 def build():747 reg = registry()748 return [_url_el(f"{BASE_URL}/type/{s}")749 for s, e in sorted(reg["types"].items()) if e["n"] >= MIN_LISTINGS]750 return _xml(_cached("sm:types", 3600, build))751752 if name == "pages.xml":753 return _xml([_url_el(f"{BASE_URL}{p}")754 for p in ["/", "/stats", "/agencies", "/rates",755 "/terms", "/privacy"]])756757 return Response("Sitemap not found", status_code=404)758759760def _city_lastmod() -> dict[str, str]:761 def build():762 con = db.connect()763 out: dict[str, str] = {}764 for r in con.execute(765 f"SELECT city, MAX(updated_at) m FROM listings WHERE {VISIBLE}"766 " AND city<>'' GROUP BY city"):767 s = slugify(r["city"])768 if s:769 prev = out.get(s)770 cur = _iso_date(r["m"])771 out[s] = max(prev, cur) if prev else cur772 con.close()773 return out774 return _cached("sm:citylastmod", 3600, build)775776777# -----------------------------------------------------------------------------778# Slug resolution for the frontend (client-side /for-sale pages)779# -----------------------------------------------------------------------------780781def resolve_slugs(ville: str | None, ptype: str | None) -> dict | None:782 reg = registry()783 out: dict = {}784 if ville:785 e = reg["cities"].get(ville)786 if not e:787 return None788 out["city"] = e["label"]789 out["city_n"] = e["n"]790 if ptype:791 e = reg["types"].get(ptype)792 if not e:793 return None794 out["property_type"] = e["label"]795 out["type_n"] = e["n"]796 return out797798799# -----------------------------------------------------------------------------800# Routing: called by the web.py catch-all801# -----------------------------------------------------------------------------802803def render_for_path(path: str, query: dict) -> Response | None:804 """SEO HTML for `path` (e.g. “/for-sale/ottawa”), or None → raw index."""805 path = path.rstrip("/") or "/"806 try:807 page = max(1, int(query.get("page", "1")))808 except ValueError:809 page = 1810811 if path == "/":812 return render_home()813 if path in _STATIC_META:814 return render_static(path)815816 parts = [p for p in path.split("/") if p]817 if parts[0] == "property" and len(parts) in (2, 3):818 return render_listing(parts[1], parts[2] if len(parts) == 3 else None)819 if parts[0] == "for-sale" and len(parts) in (2, 3):820 return _render_category(parts[1], parts[2] if len(parts) == 3 else None, page)821 if parts[0] == "type" and len(parts) == 2:822 return _render_category(None, parts[1], page)823 return render_404()824