spb/resto-ka

1# ==============================================================================2# Author: Simon-Pierre Boucher <contact@spboucher.ai>3# File:   restoka/permits.py4# Desc:   Enrichissement RACJ — permis d'alcool en vigueur (Régie des alcools,5#         des courses et des jeux, Données Québec, licence CC-BY 4.0).6#         Télécharge le registre CSV des permis de détaillant d'alcool7#         (bar, restaurant pour vendre/servir…), regroupe par établissement et8#         croise de façon CONSERVATRICE avec les restaurants (mêmes règles que9#         les inspections MAPAQ : nom exact + lieu, ou postal + civique + nom10#         similaire — jamais fusionner deux établissements). Résultat dans11#         details.permis_alcool (catégories de permis, capacité, titulaire).12#         Gratuit, sans clé API. Cadence hebdomadaire (guard 6 jours).13# ==============================================================================14from __future__ import annotations1516import csv17import io18import json19import re20import sys21import time22from datetime import date2324import requests2526from .connectors.base import USER_AGENT27from .inspections import (_CIVIC_RE, _NUMBERED_CO_RE, _name_similar,28                          core_name, norm_name)29from .regions import strip_accents3031SOURCE_ID = "racj"32REFRESH_DAYS = 6                     # au plus une fois par cycle hebdo33CSV_URL = ("https://www.donneesquebec.ca/recherche/dataset/"34           "d817c9f7-76c7-44af-882d-0d673056ef86/resource/"35           "6b69360c-af8d-4c57-b5bf-1c38d5461de3/download/"36           "racj-alcool-detaillant.csv")373839def _download() -> list[dict]:40    resp = requests.get(CSV_URL, headers={"User-Agent": USER_AGENT}, timeout=120)41    resp.raise_for_status()42    text = None43    for enc in ("utf-8-sig", "latin-1"):44        try:45            text = resp.content.decode(enc)46            break47        except UnicodeDecodeError:48            continue49    if text is None:50        raise RuntimeError("encodage CSV RACJ inconnu")51    return list(csv.DictReader(io.StringIO(text)))525354def group_establishments(records: list[dict]) -> list[dict]:55    """Regroupe les lignes CSV (une par local/terrasse/permis) par56    établissement (NoEtablissement) : catégories de permis, capacité totale,57    nom, adresse, ville, code postal, titulaire."""58    by_no: dict[str, dict] = {}59    for rec in records:60        no = (rec.get("NoEtablissement") or "").strip()61        if not no:62            continue63        e = by_no.setdefault(no, {64            "no": no,65            "nom": (rec.get("RaisonSociale") or "").strip(),66            "titulaire": (rec.get("Titulaire") or "").strip(),67            "adresse": (rec.get("Adresse") or "").strip(),68            "ville": (rec.get("Ville") or "").strip(),69            "postal": (rec.get("CodePostal") or "").replace(" ", "").upper(),70            "categories": set(),71            "capacite": 0,72            "permis": set(),73        })74        cat = (rec.get("Categorie") or "").strip()75        if cat:76            e["categories"].add(cat)77        e["permis"].add((rec.get("NoPermis") or "").strip())78        try:79            e["capacite"] += int(rec.get("Capacite") or 0)80        except ValueError:81            pass82    return list(by_no.values())838485def match(con, establishments: list[dict]) -> dict:86    """Croisement CONSERVATEUR permis <-> restaurants (mêmes règles que87    inspections.match) :8889    Règle A « nom+lieu » : nom commercial normalisé IDENTIQUE ET (code postal90      identique OU même ville + même numéro civique).91    Règle B « adresse+nom » : code postal identique ET numéro civique92      identique ET similarité de nom (hors tokens d'adresse/ville).93    """94    restos = con.execute(95        "SELECT uid, name, chain, city, postal_code, address FROM restaurants"96        " WHERE active=1 AND dup_of IS NULL").fetchall()97    by_name: dict[str, list] = {}98    by_postal: dict[str, list] = {}99    for r in restos:100        info = {101            "uid": r["uid"],102            "nname": norm_name(r["name"]),103            "nchain": norm_name(r["chain"] or ""),104            "ncity": strip_accents((r["city"] or "").lower()).strip(),105            "postal": (r["postal_code"] or "").replace(" ", "").upper(),106        }107        mm = _CIVIC_RE.match(r["address"] or "")108        info["civic"] = mm.group(1) if mm else ""109        if info["nname"]:110            by_name.setdefault(info["nname"], []).append(info)111            core = core_name(info["nname"])112            if core and core != info["nname"]:113                by_name.setdefault(core, []).append(info)114        if info["postal"]:115            by_postal.setdefault(info["postal"], []).append(info)116117    today = date.today().isoformat()118    matched = 0119    seen_uids: set[str] = set()120    for est in establishments:121        names = []122        if est["nom"]:123            names.append(norm_name(est["nom"]))124        tit = est["titulaire"]125        if tit and not _NUMBERED_CO_RE.match(strip_accents(tit.lower())):126            names.append(norm_name(tit))127        names = [n for n in names if n]128        names += [c for c in (core_name(n) for n in names)129                  if c and c not in names]130        if not names:131            continue132        ncity = strip_accents(est["ville"].lower()).strip()133        civic_m = _CIVIC_RE.match(est["adresse"])134        civic = civic_m.group(1) if civic_m else ""135        postal = est["postal"]136137        hit, how = None, ""138        # Règle A : nom exact + code postal ou ville+civique139        for n in names:140            for info in by_name.get(n, []):141                same_place = ((postal and info["postal"] == postal)142                              or (ncity and info["ncity"]143                                  and (ncity == info["ncity"]144                                       or ncity in info["ncity"]145                                       or info["ncity"] in ncity)146                                  and civic and info["civic"] == civic))147                if same_place:148                    hit, how = info["uid"], "nom+lieu"149                    break150            if hit:151                break152        # Règle B : code postal + civique + similarité de nom153        if hit is None and postal and civic:154            addr_tokens = set(re.sub(r"[^a-z0-9]+", " ", strip_accents(155                est["adresse"].lower())).split()) | {ncity}156            for info in by_postal.get(postal, []):157                if info["civic"] != civic:158                    continue159                if any(_name_similar(n, info["nname"], addr_tokens)160                       or (info["nchain"]161                           and _name_similar(n, info["nchain"], addr_tokens))162                       for n in names):163                    hit, how = info["uid"], "adresse+nom"164                    break165        if hit is None or hit in seen_uids:166            continue                  # jamais deux établissements sur 1 fiche167        seen_uids.add(hit)168        from . import db as _db169        _db.merge_details(con, hit, {"permis_alcool": {170            "categories": sorted(est["categories"]),171            "nb_permis": len(est["permis"]),172            "capacite": est["capacite"] or None,173            "titulaire": est["titulaire"],174            "matched_by": how,175            "source": "RACJ / Données Québec (CC-BY 4.0)",176            "maj": today,177        }})178        matched += 1179    con.commit()180    total = con.execute(181        "SELECT COUNT(*) c FROM restaurants WHERE active=1 AND dup_of IS NULL"182        " AND details LIKE '%\"permis_alcool\"%'").fetchone()["c"]183    return {"matched": matched, "with_permit": total}184185186def sync(con=None, force: bool = False) -> dict | None:187    """Télécharge le registre RACJ, regroupe et croise. Cadence hebdo."""188    from . import db189    own = con is None190    if own:191        con = db.connect()192    try:193        last = con.execute(194            "SELECT MAX(ts) ts FROM sync_log WHERE source=? AND ok=1",195            (SOURCE_ID,)).fetchone()["ts"]196        if not force and last and time.time() - last < REFRESH_DAYS * 86400:197            return None                         # déjà à jour cette semaine198        records = _download()199        establishments = group_establishments(records)200        m = match(con, establishments)201        stats = {"rows": len(records), "etablissements": len(establishments),202                 **m}203        con.execute(204            "INSERT INTO sync_log (source, ts, found, added, updated, removed,"205            " ok, message, stats) VALUES (?,?,?,?,0,0,1,?,?)",206            (SOURCE_ID, time.time(), len(establishments), m["matched"],207             f"permis d'alcool croisés : {m['with_permit']} resto(s)",208             json.dumps(stats, ensure_ascii=False)))209        con.commit()210        print(f"[resto-ka] racj: {stats}")211        return stats212    except Exception as exc:213        db.log_failure(con, SOURCE_ID, str(exc))214        print(f"[resto-ka] racj: erreur non bloquante: {exc}", file=sys.stderr)215        return {"error": str(exc)}216    finally:217        if own:218            con.close()219