spb/immo-ka

1# -----------------------------------------------------------------------------2# Immo-Ka — Agrégateur de propriétés à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# commerces.py : grands commerces à proximité — API Mapbox Search Box5#6#   Pour chaque grande bannière (Costco, Metro, IGA, Walmart…), on interroge7#   l'API Search Box de Mapbox (jeton PUBLIC pk.… lu dans8#   frontend/src/kamaps/config.ts — source de vérité du projet) avec la9#   position de l'annonce en `proximity`, et on retient le point de vente le10#   plus proche. Cache par cellule d'environ 1 km (data/commerces.db,11#   TTL 30 jours) : les fiches d'un même secteur ne recoûtent rien.12# -----------------------------------------------------------------------------13from __future__ import annotations1415import json16import math17import re18import sqlite319import time20import urllib.parse21import urllib.request22from concurrent.futures import ThreadPoolExecutor23from pathlib import Path2425ROOT = Path(__file__).resolve().parent.parent26DB_PATH = ROOT / "data" / "commerces.db"27UA = "ImmoKaBot/1.0 (+https://www.immo-ka.com; contact@spboucher.ai)"28TTL = 30 * 8640029API = "https://api.mapbox.com/search/searchbox/v1/forward"3031# id, libellé, requête Mapbox, mot-clé de validation (le nom du POI doit le32# contenir, sans accents ni casse — écarte « Station Métro », « Super Qualité »…)33BRANDS = [34    ("costco", "Costco", "Costco Wholesale", "costco"),35    ("walmart", "Walmart", "Walmart Supercentre", "walmart"),36    ("metro", "Metro", "Metro", "metro"),37    ("iga", "IGA", "IGA", "iga"),38    ("maxi", "Maxi", "Maxi", "maxi"),39    ("superc", "Super C", "Super C", "super c"),40    ("provigo", "Provigo", "Provigo", "provigo"),41    ("canadiantire", "Canadian Tire", "Canadian Tire", "canadian tire"),42    ("dollarama", "Dollarama", "Dollarama", "dollarama"),43    ("saq", "SAQ", "SAQ", "saq"),44    ("pharmaprix", "Pharmaprix", "Pharmaprix", "pharmaprix"),45    ("jeancoutu", "Jean Coutu", "Jean Coutu pharmacie", "jean coutu"),46    ("homedepot", "Home Depot", "Home Depot", "home depot"),47    ("rona", "RONA", "RONA", "rona"),48]4950_BAN = ("station", "stationnement")515253def _norm(s: str) -> str:54    import unicodedata55    s = unicodedata.normalize("NFD", s or "")56    return "".join(c for c in s if unicodedata.category(c) != "Mn").lower()5758_token_cache: list[str] = []596061def _token() -> str:62    if not _token_cache:63        cfg = (ROOT / "frontend" / "src" / "kamaps" / "config.ts").read_text()64        m = re.search(r'"(pk\.[A-Za-z0-9._-]+)"', cfg)65        if not m:66            raise RuntimeError("jeton Mapbox introuvable (kamaps/config.ts)")67        _token_cache.append(m.group(1))68    return _token_cache[0]697071def _connect() -> sqlite3.Connection:72    DB_PATH.parent.mkdir(parents=True, exist_ok=True)73    con = sqlite3.connect(DB_PATH, timeout=15)74    con.row_factory = sqlite3.Row75    con.execute("""CREATE TABLE IF NOT EXISTS commerces_cache (76        cellule TEXT, brand TEXT, nom TEXT, adresse TEXT,77        lat REAL, lng REAL, fetched_at REAL,78        PRIMARY KEY (cellule, brand))""")79    return con808182def _dist_m(lat1, lng1, lat2, lng2) -> float:83    dlat = math.radians(lat2 - lat1)84    dlng = math.radians(lng2 - lng1)85    a = (math.sin(dlat / 2) ** 2 + math.cos(math.radians(lat1))86         * math.cos(math.radians(lat2)) * math.sin(dlng / 2) ** 2)87    return 6371000 * 2 * math.asin(math.sqrt(a))888990def _fetch_brand(brand_q: str, lat: float, lng: float,91                 match: str = "") -> dict | None:92    params = urllib.parse.urlencode({93        "q": brand_q, "proximity": f"{lng},{lat}", "limit": 5,94        "types": "poi", "language": "fr", "country": "CA",95        "access_token": _token()})96    req = urllib.request.Request(f"{API}?{params}",97                                 headers={"User-Agent": UA})98    try:99        with urllib.request.urlopen(req, timeout=12) as r:100            feats = json.load(r).get("features") or []101    except Exception:102        return None103    for f in feats:104        p = f.get("properties") or {}105        nom = _norm(p.get("name") or "")106        if match and match not in nom:107            continue108        if any(b in nom for b in _BAN):109            continue110        lng2, lat2 = f["geometry"]["coordinates"][:2]111        return {"nom": p.get("name") or brand_q,112                "adresse": p.get("full_address")113                           or p.get("place_formatted") or "",114                "lat": lat2, "lng": lng2}115    return None116117118OVERPASS = ["https://overpass.kumi.systems/api/interpreter",119            "https://overpass-api.de/api/interpreter"]120121122def _fetch_transit(lat: float, lng: float) -> list[tuple[str, dict]]:123    """Station de métro et arrêt de bus les plus proches (OpenStreetMap)."""124    q = f"""[out:json][timeout:20];125(126  node["railway"="station"]["station"="subway"](around:3000,{lat},{lng});127  node["highway"="bus_stop"](around:1000,{lat},{lng});128);129out body;"""130    data = None131    for url in OVERPASS:132        try:133            req = urllib.request.Request(134                url, data=urllib.parse.urlencode({"data": q}).encode(),135                headers={"User-Agent": UA})136            with urllib.request.urlopen(req, timeout=25) as r:137                data = json.load(r)138            break139        except Exception:140            continue141    if not data:142        return []143    best: dict[str, tuple[float, dict]] = {}144    for el in data.get("elements", []):145        tags = el.get("tags") or {}146        kind = ("metro_station" if tags.get("railway") == "station"147                else "arret_bus")148        d = _dist_m(lat, lng, el["lat"], el["lon"])149        if kind not in best or d < best[kind][0]:150            best[kind] = (d, {"nom": tags.get("name")151                              or ("Station de métro" if kind == "metro_station"152                                  else "Arrêt de bus"),153                              "adresse": "", "lat": el["lat"],154                              "lng": el["lon"]})155    return [(k, v[1]) for k, v in best.items()]156157158TRANSIT = [("metro_station", "Station de métro"),159           ("rem_station", "Station REM"),160           ("arret_bus", "Arrêt de bus"),161           ("gare_train", "Gare de train")]162163_DB_GENRE = {"metro": "metro_station", "rem": "rem_station",164             "bus": "arret_bus", "train": "gare_train"}165166167def _transit_from_db(lat: float, lng: float) -> list[tuple[str, dict]]:168    """Arrêts/stations depuis data/transit.db (extrait OpenStreetMap169    pré-calculé : ~37 000 arrêts de bus, métro, REM, gares du Québec)."""170    db = ROOT / "data" / "transit.db"171    if not db.exists():172        return []173    con = sqlite3.connect(f"file:{db}?mode=ro", uri=True)174    con.row_factory = sqlite3.Row175    out = []176    for genre, rayon in (("metro", 6000), ("rem", 6000), ("bus", 1500),177                         ("train", 8000)):178        d = rayon / 111320.0179        rows = con.execute(180            "SELECT nom, lat, lng FROM arrets WHERE genre=? AND lat BETWEEN "181            "? AND ? AND lng BETWEEN ? AND ?",182            (genre, lat - d, lat + d, lng - d, lng + d)).fetchall()183        best = None184        for r in rows:185            dd = _dist_m(lat, lng, r["lat"], r["lng"])186            if dd <= rayon and (best is None or dd < best[0]):187                best = (dd, r)188        if best:189            out.append((_DB_GENRE[genre],190                        {"nom": best[1]["nom"] or "", "adresse": "",191                         "lat": best[1]["lat"], "lng": best[1]["lng"]}))192    con.close()193    return out194195_POI_CAT = {"metro": "metro_station", "bus": "arret_bus"}196197198def _transit_from_poi(lat: float, lng: float) -> list[tuple[str, dict]]:199    """Métro/bus depuis le cache POI du projet (louka.db, déjà calculé par200    immeuble) — la fiche interroge avec les mêmes coordonnées que poi.py."""201    db_main = ROOT / "data" / next(202        (n for n in ("louka.db", "immoka.db", "immo.db")203         if (ROOT / "data" / n).exists()), "louka.db")204    if not db_main.exists():205        return []206    try:207        con = sqlite3.connect(f"file:{db_main}?mode=ro", uri=True)208        con.row_factory = sqlite3.Row209        d = 300 / 111320.0210        row = con.execute(211            "SELECT pois, lat, lng FROM poi_cache WHERE lat BETWEEN ? AND ? "212            "AND lng BETWEEN ? AND ? ORDER BY (lat-?)*(lat-?)+(lng-?)*(lng-?) "213            "LIMIT 1", (lat - d, lat + d, lng - d, lng + d,214                        lat, lat, lng, lng)).fetchone()215        con.close()216    except sqlite3.Error:217        return []218    if row is None:219        return []220    out = []221    for e in json.loads(row["pois"] or "[]"):222        k = _POI_CAT.get(e.get("cat"))223        if k:224            out.append((k, {"nom": e.get("name") or "", "adresse": "",225                            "lat": lat, "lng": lng,226                            "_dist": e.get("dist_m")}))227    return out228229230def nearby(lat: float, lng: float) -> dict:231    """Grand commerce le plus proche par bannière (cache ~1 km, TTL 30 j)."""232    cell = f"{round(lat, 2)},{round(lng, 2)}"233    con = _connect()234    now = time.time()235    cached = {r["brand"]: r for r in con.execute(236        "SELECT * FROM commerces_cache WHERE cellule=? AND fetched_at>?",237        (cell, now - TTL))}238    manquants = [(bid, q, m) for bid, _, q, m in BRANDS239                 if bid not in cached]240    transit_manquant = any(k not in cached for k, _ in TRANSIT)241    if manquants or transit_manquant:242        res: list[tuple[str, dict | None]] = []243        if manquants:244            with ThreadPoolExecutor(max_workers=6) as ex:245                res = list(ex.map(246                    lambda b: (b[0], _fetch_brand(b[1], lat, lng, b[2])),247                    manquants))248        if transit_manquant:249            tr = (_transit_from_db(lat, lng)250                  or _transit_from_poi(lat, lng) or _fetch_transit(lat, lng))251            res.extend(tr)252        with con:253            for bid, hit in res:254                if hit is None:255                    continue256                con.execute(257                    "INSERT OR REPLACE INTO commerces_cache VALUES "258                    "(?,?,?,?,?,?,?)",259                    (cell, bid, hit["nom"],260                     hit.get("adresse") or (str(hit["_dist"])261                                            if hit.get("_dist") is not None262                                            else ""),263                     hit["lat"], hit["lng"], now))264        cached = {r["brand"]: r for r in con.execute(265            "SELECT * FROM commerces_cache WHERE cellule=? AND fetched_at>?",266            (cell, now - TTL))}267    con.close()268269    items = []270    transit = []271    for bid, label in TRANSIT:272        r = cached.get(bid)273        if r is not None:274            # distance : celle du cache POI si disponible (adresse numérique)275            d = (float(r["adresse"]) if (r["adresse"] or "").replace(276                 ".", "").isdigit() else _dist_m(lat, lng, r["lat"], r["lng"]))277            if d <= 5000:278                transit.append({"id": bid, "commerce": label,279                                "nom": r["nom"], "adresse": "",280                                "dist_m": round(d),281                                "lat": r["lat"], "lng": r["lng"]})282    for bid, label, _q, _m in BRANDS:283        r = cached.get(bid)284        if r is None:285            continue286        d = _dist_m(lat, lng, r["lat"], r["lng"])287        if d > 40000:                 # au-delà de 40 km : non pertinent288            continue289        items.append({"id": bid, "commerce": label, "nom": r["nom"],290                      "adresse": r["adresse"], "dist_m": round(d),291                      "lat": r["lat"], "lng": r["lng"]})292    items.sort(key=lambda x: x["dist_m"])293    transit.sort(key=lambda x: x["dist_m"])294    return {"n": len(items), "commerces": items, "transit": transit}295