spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (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 = "LouKaBot/1.0 (+https://www.lou-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",120            "https://maps.mail.ru/osm/tools/overpass/api/interpreter",121            "https://overpass.openstreetmap.fr/api/interpreter"]122123124def _fetch_transit(lat: float, lng: float) -> list[tuple[str, dict]]:125    """Station de métro et arrêt de bus les plus proches (OpenStreetMap)."""126    q = f"""[out:json][timeout:20];127(128  node["railway"="station"]["station"="subway"](around:3000,{lat},{lng});129  node["highway"="bus_stop"](around:1000,{lat},{lng});130);131out body;"""132    data = None133    for url in OVERPASS:134        try:135            req = urllib.request.Request(136                url, data=urllib.parse.urlencode({"data": q}).encode(),137                headers={"User-Agent": UA})138            with urllib.request.urlopen(req, timeout=25) as r:139                data = json.load(r)140            break141        except Exception:142            continue143    if not data:144        return []145    best: dict[str, tuple[float, dict]] = {}146    for el in data.get("elements", []):147        tags = el.get("tags") or {}148        kind = ("metro_station" if tags.get("railway") == "station"149                else "arret_bus")150        d = _dist_m(lat, lng, el["lat"], el["lon"])151        if kind not in best or d < best[kind][0]:152            best[kind] = (d, {"nom": tags.get("name")153                              or ("Station de métro" if kind == "metro_station"154                                  else "Arrêt de bus"),155                              "adresse": "", "lat": el["lat"],156                              "lng": el["lon"]})157    return [(k, v[1]) for k, v in best.items()]158159160TRANSIT = [("metro_station", "Station de métro"),161           ("rem_station", "Station REM"),162           ("arret_bus", "Arrêt de bus"),163           ("gare_train", "Gare de train")]164165_DB_GENRE = {"metro": "metro_station", "rem": "rem_station",166             "bus": "arret_bus", "train": "gare_train"}167168169def _transit_from_db(lat: float, lng: float) -> list[tuple[str, dict]]:170    """Arrêts/stations depuis data/transit.db (extrait OpenStreetMap171    pré-calculé : ~37 000 arrêts de bus, métro, REM, gares du Québec)."""172    db = ROOT / "data" / "transit.db"173    if not db.exists():174        return []175    con = sqlite3.connect(f"file:{db}?mode=ro", uri=True)176    con.row_factory = sqlite3.Row177    out = []178    for genre, rayon in (("metro", 6000), ("rem", 6000), ("bus", 1500),179                         ("train", 8000)):180        d = rayon / 111320.0181        rows = con.execute(182            "SELECT nom, lat, lng FROM arrets WHERE genre=? AND lat BETWEEN "183            "? AND ? AND lng BETWEEN ? AND ?",184            (genre, lat - d, lat + d, lng - d, lng + d)).fetchall()185        best = None186        for r in rows:187            dd = _dist_m(lat, lng, r["lat"], r["lng"])188            if dd <= rayon and (best is None or dd < best[0]):189                best = (dd, r)190        if best:191            out.append((_DB_GENRE[genre],192                        {"nom": best[1]["nom"] or "", "adresse": "",193                         "lat": best[1]["lat"], "lng": best[1]["lng"]}))194    con.close()195    return out196197_POI_CAT = {"metro": "metro_station", "bus": "arret_bus"}198199200def _transit_from_poi(lat: float, lng: float) -> list[tuple[str, dict]]:201    """Métro/bus depuis le cache POI du projet (louka.db, déjà calculé par202    immeuble) — la fiche interroge avec les mêmes coordonnées que poi.py."""203    db_main = ROOT / "data" / next(204        (n for n in ("louka.db", "immoka.db", "immo.db")205         if (ROOT / "data" / n).exists()), "louka.db")206    if not db_main.exists():207        return []208    try:209        con = sqlite3.connect(f"file:{db_main}?mode=ro", uri=True)210        con.row_factory = sqlite3.Row211        d = 300 / 111320.0212        row = con.execute(213            "SELECT pois, lat, lng FROM poi_cache WHERE lat BETWEEN ? AND ? "214            "AND lng BETWEEN ? AND ? ORDER BY (lat-?)*(lat-?)+(lng-?)*(lng-?) "215            "LIMIT 1", (lat - d, lat + d, lng - d, lng + d,216                        lat, lat, lng, lng)).fetchone()217        con.close()218    except sqlite3.Error:219        return []220    if row is None:221        return []222    out = []223    for e in json.loads(row["pois"] or "[]"):224        k = _POI_CAT.get(e.get("cat"))225        if k:226            out.append((k, {"nom": e.get("name") or "", "adresse": "",227                            "lat": lat, "lng": lng,228                            "_dist": e.get("dist_m")}))229    return out230231232def nearby(lat: float, lng: float) -> dict:233    """Grand commerce le plus proche par bannière (cache ~1 km, TTL 30 j)."""234    cell = f"{round(lat, 2)},{round(lng, 2)}"235    con = _connect()236    now = time.time()237    cached = {r["brand"]: r for r in con.execute(238        "SELECT * FROM commerces_cache WHERE cellule=? AND fetched_at>?",239        (cell, now - TTL))}240    manquants = [(bid, q, m) for bid, _, q, m in BRANDS241                 if bid not in cached]242    transit_manquant = any(k not in cached for k, _ in TRANSIT)243    if manquants or transit_manquant:244        res: list[tuple[str, dict | None]] = []245        if manquants:246            with ThreadPoolExecutor(max_workers=6) as ex:247                res = list(ex.map(248                    lambda b: (b[0], _fetch_brand(b[1], lat, lng, b[2])),249                    manquants))250        if transit_manquant:251            tr = (_transit_from_db(lat, lng)252                  or _transit_from_poi(lat, lng) or _fetch_transit(lat, lng))253            res.extend(tr)254        with con:255            for bid, hit in res:256                if hit is None:257                    continue258                con.execute(259                    "INSERT OR REPLACE INTO commerces_cache VALUES "260                    "(?,?,?,?,?,?,?)",261                    (cell, bid, hit["nom"],262                     hit.get("adresse") or (str(hit["_dist"])263                                            if hit.get("_dist") is not None264                                            else ""),265                     hit["lat"], hit["lng"], now))266        cached = {r["brand"]: r for r in con.execute(267            "SELECT * FROM commerces_cache WHERE cellule=? AND fetched_at>?",268            (cell, now - TTL))}269    con.close()270271    items = []272    transit = []273    for bid, label in TRANSIT:274        r = cached.get(bid)275        if r is not None:276            # distance : celle du cache POI si disponible (adresse numérique)277            d = (float(r["adresse"]) if (r["adresse"] or "").replace(278                 ".", "").isdigit() else _dist_m(lat, lng, r["lat"], r["lng"]))279            if d <= 5000:280                transit.append({"id": bid, "commerce": label,281                                "nom": r["nom"], "adresse": "",282                                "dist_m": round(d),283                                "lat": r["lat"], "lng": r["lng"]})284    for bid, label, _q, _m in BRANDS:285        r = cached.get(bid)286        if r is None:287            continue288        d = _dist_m(lat, lng, r["lat"], r["lng"])289        if d > 40000:                 # au-delà de 40 km : non pertinent290            continue291        items.append({"id": bid, "commerce": label, "nom": r["nom"],292                      "adresse": r["adresse"], "dist_m": round(d),293                      "lat": r["lat"], "lng": r["lng"]})294    items.sort(key=lambda x: x["dist_m"])295    transit.sort(key=lambda x: x["dist_m"])296    return {"n": len(items), "commerces": items, "transit": transit}297