# -----------------------------------------------------------------------------
# House-Ka — Homes-for-sale aggregator (Canada outside Québec, Ontario first)
# Author: Simon-Pierre Boucher — contact@spboucher.ai
# seo.py : server-side HTML rendering for search engines.
#
# The React SPA is untouched: this module pre-fills the initial HTML served by
# the web.py catch-all — unique <title>/meta/canonical/og:, schema.org JSON-LD,
# content and internal links inside <div id="root"> (replaced by React on
# mount). Also generates robots.txt and the sitemaps.
#
# Pages served:
#   /                              enriched home (stats + city/type links)
#   /property/{uid}[/{slug}]       listing page (301 to the canonical slug,
#                                  410 if withdrawn, 404 if unknown)
#   /for-sale/{city}[/{type}]      programmatic city pages (+ type)
#   /type/{type}                   per-property-type page (Canada-wide)
#   /stats /agencies /terms /privacy /account /rates /contact   dedicated meta
#   /robots.txt /sitemap.xml /sitemaps/*.xml
# -----------------------------------------------------------------------------
from __future__ import annotations

import html
import json
import os
import re
import time
import unicodedata
from datetime import datetime, timezone
from pathlib import Path
from urllib.parse import quote

from fastapi.responses import HTMLResponse, PlainTextResponse, RedirectResponse, Response

from . import db

ROOT = Path(__file__).resolve().parent.parent
FRONTEND_DIST = ROOT / "frontend" / "dist"
FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"

BASE_URL = os.environ.get("IMMOKA_BASE_URL", "https://www.house-ka.com").rstrip("/")
SITE_NAME = "House-Ka"

# same visibility rule as /api/listings (DDF dedup + displayable price)
VISIBLE = "active=1 AND dup_hidden=0 AND published=1"
MIN_LISTINGS = 3          # quality floor: no near-empty city/type pages
PAGE_SIZE = 48            # listings per page on programmatic pages

# province name (region column) -> two-letter code for schema.org
_PROVINCE_CODE = {
    "ontario": "ON", "british columbia": "BC", "alberta": "AB",
    "saskatchewan": "SK", "manitoba": "MB", "new brunswick": "NB",
    "nova scotia": "NS", "prince edward island": "PE",
    "newfoundland and labrador": "NL", "yukon": "YT",
    "northwest territories": "NT", "nunavut": "NU", "quebec": "QC",
}

# -----------------------------------------------------------------------------
# Utilities
# -----------------------------------------------------------------------------

def slugify(s: str) -> str:
    """URL slug — SAME algorithm as slugify() in frontend/src/api.ts."""
    s = unicodedata.normalize("NFKD", s or "").encode("ascii", "ignore").decode()
    s = re.sub(r"[^a-z0-9]+", "-", s.lower()).strip("-")
    return s[:80].strip("-")


def listing_slug(row) -> str:
    """Listing slug — SAME logic as listingPath() in frontend/src/api.ts."""
    base = slugify(row["address"] or row["title"] or "")
    city = slugify(row["city"] or "")
    if city and city not in base:
        base = slugify(f"{base} {city}") if base else city
    return base


def _esc(s) -> str:
    return html.escape(str(s or ""), quote=True)


def _fmt_n(n) -> str:
    return f"{int(n):,}"


def _fmt_price(p) -> str:
    return f"${_fmt_n(round(p))}" if p is not None else "Price on request"


def _iso_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")


# small in-memory TTL cache (data changes at watch pace, ~1 h)
_cache: dict[str, tuple[float, object]] = {}


def _cached(key: str, ttl: float, build):
    now = time.time()
    hit = _cache.get(key)
    if hit and now - hit[0] < ttl:
        return hit[1]
    val = build()
    _cache[key] = (now, val)
    return val


# -----------------------------------------------------------------------------
# Slug registries (cities, types) — rebuilt every 15 min
# -----------------------------------------------------------------------------

def _build_registry() -> dict:
    con = db.connect()
    cities: dict[str, dict] = {}
    for r in con.execute(
            f"SELECT city, COUNT(*) n FROM listings WHERE {VISIBLE}"
            " AND city<>'' GROUP BY city"):
        slug = slugify(r["city"])
        if len(slug) < 2:
            continue
        e = cities.setdefault(slug, {"label": r["city"], "n": 0, "values": [], "best": 0})
        e["n"] += r["n"]
        e["values"].append(r["city"])
        if r["n"] > e["best"]:
            e["best"] = r["n"]; e["label"] = r["city"]
    types: dict[str, dict] = {}
    for r in con.execute(
            f"SELECT property_type, COUNT(*) n FROM listings WHERE {VISIBLE}"
            " AND property_type<>'' GROUP BY property_type"):
        slug = slugify(r["property_type"])
        if len(slug) < 2:
            continue
        e = types.setdefault(slug, {"label": r["property_type"], "n": 0, "values": [], "best": 0})
        e["n"] += r["n"]
        e["values"].append(r["property_type"])
        if r["n"] > e["best"]:
            e["best"] = r["n"]; e["label"] = r["property_type"]
    city_types: dict[tuple[str, str], int] = {}
    for r in con.execute(
            f"SELECT city, property_type, COUNT(*) n FROM listings WHERE {VISIBLE}"
            " AND city<>'' AND property_type<>'' GROUP BY city, property_type"):
        cs, ts_ = slugify(r["city"]), slugify(r["property_type"])
        if len(cs) < 2 or len(ts_) < 2:
            continue
        city_types[(cs, ts_)] = city_types.get((cs, ts_), 0) + r["n"]
    con.close()
    return {"cities": cities, "types": types, "city_types": city_types}


def registry() -> dict:
    return _cached("registry", 900, _build_registry)


# -----------------------------------------------------------------------------
# Template: dist/index.html, stripped of its static <title>/description
# -----------------------------------------------------------------------------

_tpl_cache: tuple[float, str] | None = None


def _template() -> str:
    global _tpl_cache
    path = FRONTEND_DIR / "index.html"
    mtime = path.stat().st_mtime
    if _tpl_cache and _tpl_cache[0] == mtime:
        return _tpl_cache[1]
    tpl = path.read_text(encoding="utf-8")
    tpl = re.sub(r"<title>.*?</title>\s*", "", tpl, flags=re.S)
    tpl = re.sub(r'<meta name="description"[^>]*>\s*', "", tpl)
    tpl = re.sub(r'<meta (?:property="og:|name="twitter:)[^>]*>\s*', "", tpl)
    _tpl_cache = (mtime, tpl)
    return tpl


def _page(title: str, description: str, canonical: str, body: str,
          jsonld: list[dict] | None = None, og_image: str | None = None,
          og_type: str = "website", noindex: bool = False,
          status: int = 200) -> HTMLResponse:
    head = [
        f"<title>{_esc(title)}</title>",
        f'<meta name="description" content="{_esc(description)}" />',
        f'<link rel="canonical" href="{_esc(canonical)}" />',
        f'<meta property="og:site_name" content="{SITE_NAME}" />',
        '<meta property="og:locale" content="en_CA" />',
        f'<meta property="og:type" content="{og_type}" />',
        f'<meta property="og:title" content="{_esc(title)}" />',
        f'<meta property="og:description" content="{_esc(description)}" />',
        f'<meta property="og:url" content="{_esc(canonical)}" />',
    ]
    if og_image:
        head.append(f'<meta property="og:image" content="{_esc(og_image)}" />')
    else:
        og_image = BASE_URL + "/og.png"
        head.append(f'<meta property="og:image" content="{_esc(og_image)}" />')
        head.append('<meta property="og:image:width" content="1200" />')
        head.append('<meta property="og:image:height" content="630" />')
    head.append('<meta name="twitter:card" content="summary_large_image" />')
    head.append(f'<meta name="twitter:image" content="{_esc(og_image)}" />')
    if noindex:
        head.append('<meta name="robots" content="noindex" />')
    for obj in (jsonld or []):
        head.append('<script type="application/ld+json">'
                    + json.dumps(obj, ensure_ascii=False) + "</script>")
    tpl = _template()
    out = tpl.replace("</head>", "    " + "\n    ".join(head) + "\n  </head>", 1)
    out = out.replace('<div id="root"></div>',
                      f'<div id="root"><div class="container seo-ssr">{body}</div></div>', 1)
    return HTMLResponse(out, status_code=status)


# -----------------------------------------------------------------------------
# Reusable HTML blocks
# -----------------------------------------------------------------------------

def fiche_href(uid: str, slug: str = "") -> str:
    path = f"/property/{quote(uid, safe='')}"
    return path + (f"/{slug}" if slug else "")


def _item_li(r) -> str:
    href = fiche_href(r["uid"], listing_slug(r))
    bits = [b for b in [
        r["property_type"],
        f"{r['bedrooms']} bed" if r["bedrooms"] is not None else "",
        f"{r['bathrooms']} bath" if r["bathrooms"] is not None else "",
        f"{_fmt_n(round(r['area_sqft']))} sq ft" if r["area_sqft"] else "",
    ] if b]
    name = r["address"] or r["title"] or "Property"
    loc = ", ".join(x for x in [r["sector"], r["city"]] if x)
    return (f'<li><a href="{href}"><strong>{_esc(name)}</strong></a> — '
            f'{_esc(_fmt_price(r["price"]))}'
            + (f' · {_esc(" · ".join(bits))}' if bits else "")
            + (f' · {_esc(loc)}' if loc else "") + "</li>")


def _agg_stats(con, cities: list[str] | None = None,
               types_values: list[str] | None = None) -> dict:
    where = VISIBLE
    args: list = []
    if cities:
        where += f" AND city IN ({','.join('?' * len(cities))})"
        args += cities
    if types_values:
        where += f" AND property_type IN ({','.join('?' * len(types_values))})"
        args += types_values
    row = con.execute(
        f"SELECT COUNT(*) n, AVG(price) avg_p, MIN(price) min_p, MAX(price) max_p"
        f" FROM listings WHERE {where}", args).fetchone()
    med = None
    if row["n"]:
        med_row = con.execute(
            f"SELECT price FROM listings WHERE {where}"
            f" ORDER BY price LIMIT 1 OFFSET ?", args + [row["n"] // 2]).fetchone()
        med = med_row["price"] if med_row else None
    return {"n": row["n"], "avg": row["avg_p"], "med": med,
            "min": row["min_p"], "max": row["max_p"], "where": where, "args": args}


def _pagination_html(base_path: str, page: int, pages: int) -> str:
    if pages <= 1:
        return ""
    out = ['<nav class="seo-pages" aria-label="Pagination">']
    if page > 1:
        prev = base_path if page == 2 else f"{base_path}?page={page - 1}"
        out.append(f'<a rel="prev" href="{prev}">← Previous page</a> ')
    out.append(f"<span>Page {page} of {pages}</span>")
    if page < pages:
        out.append(f' <a rel="next" href="{base_path}?page={page + 1}">Next page →</a>')
    out.append("</nav>")
    return "".join(out)


def _breadcrumb_ld(crumbs: list[tuple[str, str]]) -> dict:
    return {
        "@context": "https://schema.org",
        "@type": "BreadcrumbList",
        "itemListElement": [
            {"@type": "ListItem", "position": i + 1, "name": name,
             "item": BASE_URL + path}
            for i, (name, path) in enumerate(crumbs)
        ],
    }


# -----------------------------------------------------------------------------
# Home
# -----------------------------------------------------------------------------

def _home_data() -> dict:
    def build():
        con = db.connect()
        row = con.execute(
            f"SELECT COUNT(*) total, COUNT(DISTINCT city) cities,"
            f" COUNT(DISTINCT source) sources, AVG(price) avg_p"
            f" FROM listings WHERE {VISIBLE}").fetchone()
        recent = [dict(r) for r in con.execute(
            f"SELECT uid, address, title, city, sector, property_type, price,"
            f" bedrooms, bathrooms, area_sqft FROM listings WHERE {VISIBLE}"
            f" ORDER BY first_seen DESC LIMIT 12")]
        con.close()
        return {**dict(row), "recent": recent}
    return _cached("home", 900, build)


def render_home() -> HTMLResponse:
    d = _home_data()
    reg = registry()
    total = _fmt_n(d["total"])
    title = f"House-Ka — {total} homes for sale across Canada · A Groupe KA service"
    desc = (f"{total} homes for sale in {_fmt_n(d['cities'])} Canadian cities and towns, "
            f"aggregated from {d['sources']} brokerage sources on the CREA DDF feed — "
            f"continuously updated. Average asking price: {_fmt_price(d['avg_p'])}.")
    top_cities = sorted(reg["cities"].items(), key=lambda kv: -kv[1]["n"])[:60]
    types = sorted(reg["types"].items(), key=lambda kv: -kv[1]["n"])
    body = [
        f"<h1>Homes for sale across Canada — {total} listings from real-estate brokerages</h1>",
        f"<p>House-Ka continuously aggregates homes for sale publicly listed by Canadian "
        f"real-estate brokerages and teams (CREA DDF feed) — {d['sources']} sources, "
        f"{_fmt_n(d['cities'])} cities, average asking price {_esc(_fmt_price(d['avg_p']))}. "
        f"Every listing links back to the brokerage's original page. Coverage starts with "
        f"Ontario and grows across the rest of Canada. Looking for Québec? "
        f'See our sister site <a href="https://www.immo-ka.com" rel="noopener">Immo-Ka</a>.</p>',
        "<h2>Homes for sale by city</h2>",
        "<ul>" + "".join(
            f'<li><a href="/for-sale/{s}">Homes for sale in {_esc(e["label"])}</a>'
            f" ({_fmt_n(e['n'])})</li>"
            for s, e in top_cities if e["n"] >= MIN_LISTINGS) + "</ul>",
        "<h2>By property type</h2>",
        "<ul>" + "".join(
            f'<li><a href="/type/{s}">{_esc(e["label"])} for sale in Canada</a>'
            f" ({_fmt_n(e['n'])})</li>"
            for s, e in types if e["n"] >= MIN_LISTINGS) + "</ul>",
        "<h2>Latest listings</h2>",
        "<ul>" + "".join(_item_li(r) for r in d["recent"]) + "</ul>",
        '<p><a href="/stats">Market statistics</a> · '
        '<a href="/agencies">Covered brokerages</a> · '
        '<a href="/rates">Mortgage rates</a></p>',
    ]
    jsonld = [{
        "@context": "https://schema.org",
        "@type": "WebSite",
        "name": SITE_NAME,
        "url": BASE_URL + "/",
        "inLanguage": "en-CA",
        "description": desc,
        "potentialAction": {
            "@type": "SearchAction",
            "target": {"@type": "EntryPoint",
                       "urlTemplate": BASE_URL + "/?q={search_term_string}"},
            "query-input": "required name=search_term_string",
        },
    }, {
        "@context": "https://schema.org",
        "@type": "Organization",
        "name": "Groupe-Ka",
        "url": BASE_URL + "/",
        "email": "contact@groupe-ka.com",
    }]
    return _page(title, desc, BASE_URL + "/", "".join(body), jsonld)


# -----------------------------------------------------------------------------
# Programmatic pages: city, city+type, type
# -----------------------------------------------------------------------------

def _render_category(city_slug: str | None, type_slug: str | None,
                     page: int) -> HTMLResponse:
    reg = registry()
    city = reg["cities"].get(city_slug) if city_slug else None
    ptype = reg["types"].get(type_slug) if type_slug else None
    if (city_slug and not city) or (type_slug and not ptype):
        return render_404()
    if city_slug and type_slug and reg["city_types"].get((city_slug, type_slug), 0) < 1:
        return render_404()

    con = db.connect()
    st = _agg_stats(con, city["values"] if city else None,
                    ptype["values"] if ptype else None)
    if st["n"] < 1:
        con.close()
        return render_404()

    pages = max(1, -(-st["n"] // PAGE_SIZE))
    if page < 1 or page > pages:
        con.close()
        return render_404()
    rows = con.execute(
        f"SELECT uid, address, title, city, sector, property_type, price,"
        f" bedrooms, bathrooms, area_sqft FROM listings WHERE {st['where']}"
        f" ORDER BY price IS NULL, price ASC LIMIT ? OFFSET ?",
        st["args"] + [PAGE_SIZE, (page - 1) * PAGE_SIZE]).fetchall()

    # internal linking
    links = []
    if city:
        tlinks = []
        for (cs, ts_), n in sorted(reg["city_types"].items(), key=lambda kv: -kv[1]):
            if cs == city_slug and n >= 1 and ts_ in reg["types"] and ts_ != type_slug:
                lbl = reg["types"][ts_]["label"]
                tlinks.append(f'<li><a href="/for-sale/{cs}/{ts_}">'
                              f"{_esc(lbl)} for sale in {_esc(city['label'])}</a> ({_fmt_n(n)})</li>")
        if tlinks:
            links.append("<h2>Other property types in "
                         + _esc(city["label"]) + "</h2><ul>" + "".join(tlinks[:20]) + "</ul>")
        if type_slug:
            links.append(f'<p><a href="/for-sale/{city_slug}">All homes for sale '
                         f"in {_esc(city['label'])}</a> · "
                         f'<a href="/type/{type_slug}">{_esc(ptype["label"])} for sale in Canada</a></p>')
    top = sorted(reg["cities"].items(), key=lambda kv: -kv[1]["n"])[:30]
    links.append("<h2>Other cities</h2><ul>" + "".join(
        f'<li><a href="/for-sale/{s}{"/" + type_slug if type_slug and (s, type_slug) in reg["city_types"] else ""}">'
        f'Homes for sale in {_esc(e["label"])}</a> ({_fmt_n(e["n"])})</li>'
        for s, e in top if s != city_slug and e["n"] >= MIN_LISTINGS) + "</ul>")
    con.close()

    if city and ptype:
        base_path = f"/for-sale/{city_slug}/{type_slug}"
        h1 = f"{ptype['label']} for sale in {city['label']}"
        what = f"{ptype['label'].lower()} listings in {city['label']}"
    elif city:
        base_path = f"/for-sale/{city_slug}"
        h1 = f"Homes for sale in {city['label']}"
        what = f"homes for sale in {city['label']}"
    else:
        base_path = f"/type/{type_slug}"
        h1 = f"{ptype['label']} for sale in Canada"
        what = f"{ptype['label'].lower()} listings across Canada"

    canonical = BASE_URL + base_path + (f"?page={page}" if page > 1 else "")
    title = f"{h1} — {_fmt_n(st['n'])} listings" + (f" (page {page})" if page > 1 else "") + " | House-Ka"
    desc = (f"{_fmt_n(st['n'])} {what}: median price {_fmt_price(st['med'])}, "
            f"average price {_fmt_price(st['avg'])}. Listings from Canadian brokerages "
            f"on the CREA DDF feed, continuously updated.")
    stats_p = (f"<p><strong>{_fmt_n(st['n'])}</strong> listings · median price "
               f"<strong>{_esc(_fmt_price(st['med']))}</strong> · average price "
               f"<strong>{_esc(_fmt_price(st['avg']))}</strong> · from "
               f"{_esc(_fmt_price(st['min']))} to {_esc(_fmt_price(st['max']))}.</p>")
    crumbs = [("Home", "/")]
    if city:
        crumbs.append((f"For sale in {city['label']}", f"/for-sale/{city_slug}"))
        if ptype:
            crumbs.append((f"{ptype['label']}", base_path))
    else:
        crumbs.append((h1, base_path))
    body = ('<nav aria-label="Breadcrumb">'
            + " › ".join(f'<a href="{p}">{_esc(n)}</a>' for n, p in crumbs)
            + f"</nav><h1>{_esc(h1)}</h1>" + stats_p
            + "<ul>" + "".join(_item_li(r) for r in rows) + "</ul>"
            + _pagination_html(base_path, page, pages)
            + "".join(links))
    return _page(title, desc, canonical, body, [_breadcrumb_ld(crumbs)])


# -----------------------------------------------------------------------------
# Listing page
# -----------------------------------------------------------------------------

_TYPE_SCHEMA = {
    "house": "SingleFamilyResidence", "condo": "Apartment",
    "cottage": "House", "semi-detached": "House", "townhouse": "House",
    "duplex": "Residence", "triplex": "Residence",
    "multi-family": "Residence", "mobile-home": "House",
}


def render_listing(uid: str, slug: str | None) -> Response:
    con = db.connect()
    row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()
    con.close()
    if row is None:
        return render_404()

    city_slug = slugify(row["city"] or "")
    city_known = city_slug in registry()["cities"]
    city_href = f"/for-sale/{city_slug}" if city_known else "/"

    if not row["active"]:
        # withdrawn / sold → 410 Gone, with escape hatches
        name = row["address"] or row["title"] or "Property"
        body = (f"<h1>This property is no longer for sale</h1>"
                f"<p>The listing “{_esc(name)}” ({_esc(row['city'] or 'Canada')}) has been "
                f"withdrawn or sold.</p><ul>"
                + (f'<li><a href="{city_href}">Homes for sale in '
                   f"{_esc(row['city'])}</a></li>" if city_known else "")
                + '<li><a href="/">All homes for sale across Canada</a></li></ul>')
        return _page(f"Listing withdrawn — {name} | {SITE_NAME}",
                     "This listing has been withdrawn or sold.",
                     BASE_URL + fiche_href(uid), body, noindex=True, status=410)

    expected = listing_slug(row)
    if expected and slug != expected:
        return RedirectResponse(BASE_URL + fiche_href(uid, expected), status_code=301)

    d = dict(row)
    images = json.loads(d.get("images") or "[]")
    features = json.loads(d.get("features") or "[]")
    name = d["address"] or d["title"] or "Property for sale"
    loc = ", ".join(x for x in [d["sector"], d["city"]] if x) or "Canada"
    canonical = BASE_URL + fiche_href(uid, expected)
    ptype = d["property_type"] or "Property"

    specs = [(lbl, val) for lbl, val in [
        ("Type", ptype),
        ("Price", _fmt_price(d["price"]) if d["price"] is not None else d["price_label"]),
        ("Bedrooms", d["bedrooms"]),
        ("Bathrooms", d["bathrooms"]),
        ("Half baths", d["powder_rooms"]),
        ("Living area", f"{_fmt_n(round(d['area_sqft']))} sq ft" if d["area_sqft"] else None),
        ("Lot", f"{_fmt_n(round(d['lot_sqft']))} sq ft" if d["lot_sqft"] else None),
        ("Year built", d["year_built"]),
        ("City", d["city"]),
        ("Neighbourhood", d["sector"]),
        ("MLS® number", d["mls"]),
        ("Agent", d["broker_name"]),
        ("Brokerage", d["agency"]),
    ] if val not in (None, "", 0)]
    descr = (d["description"] or "").strip()
    if len(descr) > 1500:
        descr = descr[:1500].rsplit(" ", 1)[0] + "…"

    type_slug = slugify(ptype)
    crumbs = [("Home", "/")]
    if city_known:
        crumbs.append((f"For sale in {d['city']}", city_href))
        if (city_slug, type_slug) in registry()["city_types"]:
            crumbs.append((ptype, f"/for-sale/{city_slug}/{type_slug}"))
    crumbs.append((name, fiche_href(uid, expected)))

    body = [
        '<nav aria-label="Breadcrumb">'
        + " › ".join(f'<a href="{p}">{_esc(n)}</a>' for n, p in crumbs[:-1])
        + f" › {_esc(name)}</nav>",
        f"<h1>{_esc(name)}</h1>",
        f"<p><strong>{_esc(ptype)} for sale in {_esc(loc)}</strong> — "
        f"{_esc(_fmt_price(d['price']) if d['price'] is not None else (d['price_label'] or 'Price on request'))}</p>",
    ]
    if images:
        body.append("".join(
            f'<img src="{_esc(u)}" alt="{_esc(name)} — photo {i + 1}" loading="lazy" />'
            for i, u in enumerate(images[:3])))
    body.append("<h2>Key facts</h2><ul>" + "".join(
        f"<li><strong>{_esc(l)}:</strong> {_esc(v)}</li>" for l, v in specs) + "</ul>")
    if descr:
        body.append(f"<h2>Description</h2><p>{_esc(descr)}</p>")
    if features:
        body.append("<h2>Details</h2><ul>" + "".join(
            f"<li>{_esc(f)}</li>" for f in features[:20]) + "</ul>")
    if d["url"]:
        body.append(f'<p><a href="{_esc(d["url"])}" rel="noopener">'
                    f"See the original listing at {_esc(d['broker_name'] or d['agency'] or 'the brokerage')}</a></p>")
    if city_known:
        body.append(f'<p><a href="{city_href}">Homes for sale in {_esc(d["city"])}</a>'
                    + (f' · <a href="/for-sale/{city_slug}/{type_slug}">{_esc(ptype)} for sale in '
                       f'{_esc(d["city"])}</a>'
                       if (city_slug, type_slug) in registry()["city_types"] else "") + "</p>")

    about_type = _TYPE_SCHEMA.get(type_slug, "Residence")
    region_code = _PROVINCE_CODE.get((d["region"] or "").strip().lower(), "ON")
    about: dict = {
        "@type": about_type,
        "name": name,
        "address": {"@type": "PostalAddress",
                    "streetAddress": d["address"] or None,
                    "addressLocality": d["city"] or None,
                    "addressRegion": region_code, "addressCountry": "CA"},
    }
    if d["lat"] is not None and d["lng"] is not None:
        about["geo"] = {"@type": "GeoCoordinates",
                        "latitude": d["lat"], "longitude": d["lng"]}
    if d["bedrooms"] is not None:
        about["numberOfBedrooms"] = d["bedrooms"]
    if d["bathrooms"] is not None:
        about["numberOfBathroomsTotal"] = d["bathrooms"]
    if d["area_sqft"]:
        about["floorSize"] = {"@type": "QuantitativeValue",
                              "value": round(d["area_sqft"]), "unitCode": "FTK"}
    if d["year_built"]:
        about["yearBuilt"] = d["year_built"]
    about = {k: v for k, v in about.items() if v is not None}
    about["address"] = {k: v for k, v in about["address"].items() if v is not None}
    jsonld: list[dict] = [{
        "@context": "https://schema.org",
        "@type": "RealEstateListing",
        "name": name,
        "url": canonical,
        "inLanguage": "en-CA",
        "datePosted": _iso_date(d.get("first_seen")),
        "image": images[:6] or None,
        "about": about,
    }, _breadcrumb_ld(crumbs)]
    if d["price"] is not None:
        jsonld[0]["offers"] = {"@type": "Offer", "price": round(d["price"], 2),
                               "priceCurrency": "CAD",
                               "availability": "https://schema.org/InStock"}
    jsonld[0] = {k: v for k, v in jsonld[0].items() if v is not None}

    title = f"{name} — {ptype} for sale, {d['city'] or 'Canada'} | {_fmt_price(d['price']) if d['price'] is not None else 'Price on request'}"
    meta_desc = (f"{ptype} for sale in {loc}"
                 + (f", {d['bedrooms']} bedrooms" if d["bedrooms"] else "")
                 + (f", {_fmt_n(round(d['area_sqft']))} sq ft" if d["area_sqft"] else "")
                 + f" — {_fmt_price(d['price']) if d['price'] is not None else 'price on request'}. "
                 + (descr[:120] + "…" if len(descr) > 120 else descr))
    return _page(title, meta_desc, canonical, "".join(body), jsonld,
                 og_image=images[0] if images else None, og_type="article")


# -----------------------------------------------------------------------------
# Static SPA pages (dedicated meta) and 404
# -----------------------------------------------------------------------------

_STATIC_META = {
    "/stats": ("Canadian housing market statistics",
               "Average prices, listing volumes by source and data quality — "
               "continuous statistics from House-Ka.",
               False),
    "/agencies": ("Covered brokerages",
                  "All the Canadian real-estate brokerages and teams aggregated by "
                  "House-Ka through the CREA DDF feed.",
                  False),
    "/rates": (
        "Mortgage rates in Canada — live comparator",
        "Compare mortgage rates actually published by RBC, TD, BMO, CIBC, "
        "Scotiabank, NBC, Desjardins, Tangerine, EQ and more — fixed and variable, "
        "with official source, freshness and history. Continuously collected by House-Ka.",
        False),
    "/terms": ("Terms of use",
               "Terms of use of the House-Ka platform (Groupe-Ka).", False),
    "/privacy": ("Privacy policy",
                 "House-Ka privacy policy (Groupe-Ka) — PIPEDA.", False),
    "/account": ("My account", "Your Groupe KA account on House-Ka.", True),
    "/contact": ("Contact — Groupe KA",
                 "Write to Groupe KA: contact@groupe-ka.com (projects and data), "
                 "info@groupe-ka.com (media), admin@groupe-ka.com (legal and privacy). "
                 "House-Ka is a Groupe KA service — https://www.groupe-ka.com.",
                 False),
}


def render_static(path: str) -> HTMLResponse:
    t, desc, noindex = _STATIC_META[path]
    body = f"<h1>{_esc(t)}</h1><p>{_esc(desc)}</p>"
    return _page(f"{t} | {SITE_NAME}", desc, BASE_URL + path, body, noindex=noindex)


def render_404() -> HTMLResponse:
    body = ('<h1>Page not found</h1><p>The requested link does not exist.</p>'
            '<p><a href="/">All homes for sale across Canada</a></p>')
    return _page(f"Page not found | {SITE_NAME}", "Page not found.",
                 BASE_URL + "/", body, noindex=True, status=404)


# -----------------------------------------------------------------------------
# robots.txt and sitemaps
# -----------------------------------------------------------------------------

FICHES_PER_SITEMAP = 40000


def robots_txt() -> PlainTextResponse:
    return PlainTextResponse(
        "User-agent: *\n"
        "Allow: /\n"
        "Disallow: /api/\n"
        "Disallow: /account\n"
        f"\nSitemap: {BASE_URL}/sitemap.xml\n")


def _xml(urls: list[str]) -> Response:
    body = ('<?xml version="1.0" encoding="UTF-8"?>\n'
            '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'
            + "\n".join(urls) + "\n</urlset>")
    return Response(body, media_type="application/xml")


def _url_el(loc: str, lastmod: str | None = None) -> str:
    lm = f"<lastmod>{lastmod}</lastmod>" if lastmod else ""
    return f"  <url><loc>{html.escape(loc)}</loc>{lm}</url>"


def sitemap_index() -> Response:
    def build():
        con = db.connect()
        n = con.execute(f"SELECT COUNT(*) c FROM listings WHERE {VISIBLE}").fetchone()["c"]
        last = con.execute(
            f"SELECT MAX(updated_at) m FROM listings WHERE {VISIBLE}").fetchone()["m"]
        con.close()
        parts = -(-n // FICHES_PER_SITEMAP) or 1
        lm = _iso_date(last)
        maps = [f"{BASE_URL}/sitemaps/listings-{i + 1}.xml" for i in range(parts)]
        maps += [f"{BASE_URL}/sitemaps/cities.xml",
                 f"{BASE_URL}/sitemaps/cities-types.xml",
                 f"{BASE_URL}/sitemaps/types.xml",
                 f"{BASE_URL}/sitemaps/pages.xml"]
        body = ('<?xml version="1.0" encoding="UTF-8"?>\n'
                '<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'
                + "\n".join(f"  <sitemap><loc>{html.escape(m)}</loc>"
                            f"<lastmod>{lm}</lastmod></sitemap>" for m in maps)
                + "\n</sitemapindex>")
        return body
    return Response(_cached("sm:index", 3600, build), media_type="application/xml")


def sitemap_file(name: str) -> Response:
    m = re.fullmatch(r"listings-(\d+)\.xml", name)
    if m:
        part = int(m.group(1))

        def build():
            con = db.connect()
            rows = con.execute(
                f"SELECT uid, address, title, city, updated_at FROM listings"
                f" WHERE {VISIBLE} ORDER BY uid LIMIT ? OFFSET ?",
                (FICHES_PER_SITEMAP, (part - 1) * FICHES_PER_SITEMAP)).fetchall()
            con.close()
            if not rows:
                return None
            return [_url_el(BASE_URL + fiche_href(r["uid"], listing_slug(r)),
                            _iso_date(r["updated_at"])) for r in rows]
        urls = _cached(f"sm:listings:{part}", 3600, build)
        if urls is None:
            return Response("Sitemap not found", status_code=404)
        return _xml(urls)

    if name == "cities.xml":
        def build():
            reg = registry()
            lm = _city_lastmod()
            return [_url_el(f"{BASE_URL}/for-sale/{s}", lm.get(s))
                    for s, e in sorted(reg["cities"].items())
                    if e["n"] >= MIN_LISTINGS]
        return _xml(_cached("sm:cities", 3600, build))

    if name == "cities-types.xml":
        def build():
            reg = registry()
            lm = _city_lastmod()
            return [_url_el(f"{BASE_URL}/for-sale/{cs}/{ts_}", lm.get(cs))
                    for (cs, ts_), n in sorted(reg["city_types"].items())
                    if n >= MIN_LISTINGS and cs in reg["cities"] and ts_ in reg["types"]
                    and reg["cities"][cs]["n"] >= MIN_LISTINGS]
        return _xml(_cached("sm:cities-types", 3600, build))

    if name == "types.xml":
        def build():
            reg = registry()
            return [_url_el(f"{BASE_URL}/type/{s}")
                    for s, e in sorted(reg["types"].items()) if e["n"] >= MIN_LISTINGS]
        return _xml(_cached("sm:types", 3600, build))

    if name == "pages.xml":
        return _xml([_url_el(f"{BASE_URL}{p}")
                     for p in ["/", "/stats", "/agencies", "/rates",
                               "/terms", "/privacy"]])

    return Response("Sitemap not found", status_code=404)


def _city_lastmod() -> dict[str, str]:
    def build():
        con = db.connect()
        out: dict[str, str] = {}
        for r in con.execute(
                f"SELECT city, MAX(updated_at) m FROM listings WHERE {VISIBLE}"
                " AND city<>'' GROUP BY city"):
            s = slugify(r["city"])
            if s:
                prev = out.get(s)
                cur = _iso_date(r["m"])
                out[s] = max(prev, cur) if prev else cur
        con.close()
        return out
    return _cached("sm:citylastmod", 3600, build)


# -----------------------------------------------------------------------------
# Slug resolution for the frontend (client-side /for-sale pages)
# -----------------------------------------------------------------------------

def resolve_slugs(ville: str | None, ptype: str | None) -> dict | None:
    reg = registry()
    out: dict = {}
    if ville:
        e = reg["cities"].get(ville)
        if not e:
            return None
        out["city"] = e["label"]
        out["city_n"] = e["n"]
    if ptype:
        e = reg["types"].get(ptype)
        if not e:
            return None
        out["property_type"] = e["label"]
        out["type_n"] = e["n"]
    return out


# -----------------------------------------------------------------------------
# Routing: called by the web.py catch-all
# -----------------------------------------------------------------------------

def render_for_path(path: str, query: dict) -> Response | None:
    """SEO HTML for `path` (e.g. “/for-sale/ottawa”), or None → raw index."""
    path = path.rstrip("/") or "/"
    try:
        page = max(1, int(query.get("page", "1")))
    except ValueError:
        page = 1

    if path == "/":
        return render_home()
    if path in _STATIC_META:
        return render_static(path)

    parts = [p for p in path.split("/") if p]
    if parts[0] == "property" and len(parts) in (2, 3):
        return render_listing(parts[1], parts[2] if len(parts) == 3 else None)
    if parts[0] == "for-sale" and len(parts) in (2, 3):
        return _render_category(parts[1], parts[2] if len(parts) == 3 else None, page)
    if parts[0] == "type" and len(parts) == 2:
        return _render_category(None, parts[1], page)
    return render_404()