# =============================================================================
# Job·Ka — Groupe KA
# Auteur  : Simon-Pierre Boucher
# Contact : contact@spboucher.ai
# Fichier : jobka/geocode.py
# Rôle    : Géocodage des lieux de travail — Nominatim + repli Adresses Québec,
#           cache persistant, centroïde de ville quand l'adresse est absente
# Créé    : 2026-08-17   Modifié : 2026-08-17
# =============================================================================
"""Géocodage des offres (pipeline hérité de Lou·Ka).

- cache persistant (table geocode_cache) : un lieu n'est géocodé qu'une seule
  fois, par adresse normalisée — ou par VILLE quand l'offre n'a pas d'adresse
  (cas majoritaire pour l'emploi : le centroïde de ville suffit à la carte).
- politesse : 1 requête/seconde max (règle Nominatim), User-Agent identifiable.
- validation : bounding box provinciale — jamais de coordonnées inventées.
"""
from __future__ import annotations

import re
import time

import requests

from . import db
from .normalize import strip_accents

NOMINATIM_URL = "https://nominatim.openstreetmap.org/search"
# Repli officiel du gouvernement du Québec (Adresses Québec / MERN) : couvre
# les adresses trop récentes pour OpenStreetMap.
AQ_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"
          "Adresse_Geocodage/GeocodeServer/findAddressCandidates")
AQ_BATCH_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"
                "Adresse_Geocodage/GeocodeServer/geocodeAddresses")
AQ_MIN_SCORE = 75
BATCH_SIZE = 200
# ASCII pur : le serveur ArcGIS d'Adresses Québec retourne 500 si l'en-tête
# User-Agent contient des accents (latin-1)
USER_AGENT = "JobKaBot/1.0 (agregateur emplois Quebec; +contact@spboucher.ai)"
REQUEST_DELAY = 1.1          # règle Nominatim : max 1 req/s
RETRY_FAILED_AFTER = 30 * 86400   # re-tenter les échecs après 30 jours

# Garde-fou provincial (lat_min, lat_max, lng_min, lng_max) — même boîte que
# schema.finalize()
_BBOX_QC = (44.50, 63.00, -80.00, -56.00)


def _in_bbox(lat: float, lng: float) -> bool:
    return _BBOX_QC[0] <= lat <= _BBOX_QC[1] and _BBOX_QC[2] <= lng <= _BBOX_QC[3]


def norm_key(address: str, city: str = "") -> str:
    """Clé de cache : adresse normalisée, ou « ville <ville> » sans adresse."""
    s = strip_accents((address or "").lower())
    s = re.sub(r"\b(?:bureau|suite|local|etage|app?t?|#)\.?\s*[\w-]+\b", " ", s)
    s = re.sub(r"[^a-z0-9]+", " ", s)
    s = re.sub(r"\s+", " ", s).strip()
    if s and len(s) >= 6:
        return s
    c = strip_accents((city or "").lower())
    c = re.sub(r"[^a-z0-9]+", " ", c).strip()
    return f"ville {c}" if c else ""


class Geocoder:
    def __init__(self, con) -> None:
        self.con = con
        self.session = requests.Session()
        self.session.headers["User-Agent"] = USER_AGENT
        self._last = 0.0

    @staticmethod
    def _clean(address: str) -> str:
        """Nettoyages qui aident Nominatim sur les adresses québécoises."""
        s = (address or "").strip()
        s = re.sub(r"^(\d+)-(\d+)\s", r"\2 ", s)          # unité-civique
        s = re.sub(r",?\s*(?:bureau|suite|local|app\.?|apt\.?|etage|étage)\s*[\w-]+\b",
                   "", s, flags=re.I)
        s = re.sub(r"\s*\((qu[ée]bec)\)", r", \1", s, flags=re.I)
        return s

    def _query_nominatim(self, params: dict) -> tuple[float, float] | None:
        """Une requête Nominatim, throttlée."""
        wait = REQUEST_DELAY - (time.time() - self._last)
        if wait > 0:
            time.sleep(wait)
        try:
            resp = self.session.get(NOMINATIM_URL, params={
                "format": "jsonv2", "limit": 1, "countrycodes": "ca", **params,
            }, timeout=20)
            self._last = time.time()
            resp.raise_for_status()
            hits = resp.json()
        except Exception:
            self._last = time.time()
            return None
        if not hits:
            return None
        try:
            return float(hits[0]["lat"]), float(hits[0]["lon"])
        except (KeyError, ValueError):
            return None

    def _query_adresses_quebec(self, address: str, city: str) -> tuple[float, float] | None:
        """Repli : géocodeur officiel Adresses Québec (MERN, ArcGIS)."""
        premier = self._clean(address).split(",")[0].strip()
        ville = (city or "Québec").strip()
        wait = REQUEST_DELAY - (time.time() - self._last)
        if wait > 0:
            time.sleep(wait)
        try:
            resp = self.session.get(AQ_URL, params={
                "SingleLine": f"{premier}, {ville}",
                "f": "json", "outSR": 4326, "maxLocations": 1,
            }, timeout=20)
            self._last = time.time()
            resp.raise_for_status()
            cands = resp.json().get("candidates") or []
        except Exception:
            self._last = time.time()
            return None
        if not cands or cands[0].get("score", 0) < AQ_MIN_SCORE:
            return None
        loc = cands[0].get("location") or {}
        try:
            return float(loc["y"]), float(loc["x"])
        except (KeyError, ValueError):
            return None

    def _attempts(self, address: str, city: str) -> list[dict]:
        """Stratégies de requête, de la plus précise à la moins précise."""
        ville = (city or "Québec").strip()
        commun = {"city": ville, "state": "Québec", "country": "Canada"}
        tries: list[dict] = []
        premier = self._clean(address).split(",")[0].strip() if address else ""
        if premier:
            m = re.match(r"^(\d+)[,\s]+(.{4,})$", premier)
            if m:
                tries.append({"street": f"{m.group(1)} {m.group(2)}", **commun})
            tries.append({"q": f"{premier}, {ville}, Québec, Canada"})
            if m:
                tries.append({"street": m.group(2), **commun})
        else:
            # sans adresse : centroïde de ville (suffisant pour la carte)
            tries.append(commun)
        return tries

    def resolve(self, address: str, city: str) -> tuple[float, float] | None:
        """Lieu -> (lat, lng), via cache puis Nominatim/AQ. None si introuvable."""
        key = norm_key(address, city)
        if not key:
            return None
        row = self.con.execute(
            "SELECT lat, lng, failed, ts FROM geocode_cache WHERE address=?",
            (key,)).fetchone()
        if row is not None:
            if not row["failed"]:
                return (row["lat"], row["lng"])
            if time.time() - (row["ts"] or 0) < RETRY_FAILED_AFTER:
                return None   # échec récent : ne pas marteler l'API

        coords = None
        provider = "nominatim"
        for params in self._attempts(address, city):
            c = self._query_nominatim(params)
            if c is not None and _in_bbox(*c):
                coords = c
                break
        if coords is None and address:
            c = self._query_adresses_quebec(address, city)
            if c is not None and _in_bbox(*c):
                coords = c
                provider = "adresses_quebec"
        ok = coords is not None
        self.con.execute(
            "INSERT INTO geocode_cache (address, lat, lng, provider, failed, ts)"
            " VALUES (?,?,?,?,?,?)"
            " ON CONFLICT(address) DO UPDATE SET lat=excluded.lat,"
            " lng=excluded.lng, provider=excluded.provider,"
            " failed=excluded.failed, ts=excluded.ts",
            (key, coords[0] if ok else None, coords[1] if ok else None,
             provider, 0 if ok else 1, time.time()))
        self.con.commit()
        return coords if ok else None


def run(limit: int | None = None) -> dict:
    """Géocode les offres actives sans coordonnées, par lieu unique.

    `limit` borne le nombre de NOUVELLES requêtes réseau (les hits de cache
    sont gratuits et toujours appliqués).
    """
    con = db.connect()
    geo = Geocoder(con)
    rows = con.execute(
        """SELECT uid, address, city FROM jobs
           WHERE active=1 AND lat IS NULL AND geocode_failed=0
             AND (address<>'' OR city<>'') ORDER BY city, address""").fetchall()

    # regrouper par lieu (adresse normalisée, ou ville)
    groupes: dict[str, list] = {}
    for r in rows:
        groupes.setdefault(norm_key(r["address"], r["city"]), []).append(r)

    done = failed = requests_made = 0
    for key, members in groupes.items():
        if not key:
            continue
        cached = con.execute(
            "SELECT failed FROM geocode_cache WHERE address=?", (key,)).fetchone()
        if cached is None:
            if limit is not None and requests_made >= limit:
                continue
            requests_made += 1
        coords = geo.resolve(members[0]["address"], members[0]["city"])
        if coords:
            for r in members:
                con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",
                            (coords[0], coords[1], r["uid"]))
            done += len(members)
        else:
            # introuvable/hors zone : flag pour révision, jamais de coordonnées bidon
            for r in members:
                con.execute("UPDATE jobs SET geocode_failed=1 WHERE uid=?",
                            (r["uid"],))
            failed += len(members)
        con.commit()

    con.close()
    stats = {"geocoded": done, "failed": failed,
             "unique_places": len(groupes), "api_requests": requests_made}
    print(f"[job-ka] geocode {stats}")
    return stats


def run_batch(limit: int | None = None) -> dict:
    """Géocodage EN LOT : les offres AVEC adresse passent par Adresses Québec
    (geocodeAddresses, lots de 200) ; celles avec seulement une ville passent
    par le mode 1-par-1 (centroïdes de ville — peu nombreuses et vite en cache)."""
    import json as _json

    con = db.connect()
    geo = Geocoder(con)
    rows = con.execute(
        """SELECT uid, address, city FROM jobs
           WHERE active=1 AND lat IS NULL AND geocode_failed=0
             AND address IS NOT NULL AND address<>'' ORDER BY address""").fetchall()
    uniq: dict[str, dict] = {}
    for r in rows:
        k = norm_key(r["address"], r["city"])
        if not k or k.startswith("ville "):
            continue
        u = uniq.setdefault(k, {"address": r["address"], "city": r["city"],
                                "members": []})
        u["members"].append(r["uid"])
    pending = []
    for k, u in uniq.items():
        c = con.execute("SELECT lat,lng,failed FROM geocode_cache WHERE address=?",
                        (k,)).fetchone()
        if c is not None and not c["failed"]:
            for uid in u["members"]:
                con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",
                            (c["lat"], c["lng"], uid))
            continue
        if c is not None and c["failed"]:
            continue
        pending.append((k, u))
    con.commit()
    if limit is not None:
        pending = pending[:limit]

    session = requests.Session()
    session.headers["User-Agent"] = USER_AGENT
    done = failed = 0
    for i in range(0, len(pending), BATCH_SIZE):
        chunk = pending[i:i + BATCH_SIZE]
        records = {"records": [
            {"attributes": {"OBJECTID": j,
                            "SingleLine": f"{geo._clean(u['address']).split(',')[0].strip()}, "
                                          f"{(u['city'] or 'Québec').strip()}"}}
            for j, (_k, u) in enumerate(chunk)]}
        try:
            resp = session.post(AQ_BATCH_URL, data={
                "addresses": _json.dumps(records, ensure_ascii=False),
                "f": "json", "outSR": 4326}, timeout=90)
            locs = resp.json().get("locations", [])
        except Exception as e:
            print(f"[job-ka] geocode-batch lot {i//BATCH_SIZE} ERREUR: {str(e)[:80]}")
            time.sleep(1.0)
            continue
        by_id = {l["attributes"].get("ResultID"): l for l in locs}
        for j, (k, u) in enumerate(chunk):
            loc = by_id.get(j)
            coords = None
            if loc and loc["attributes"].get("Score", 0) >= AQ_MIN_SCORE:
                lc = loc.get("location") or {}
                try:
                    cand = (float(lc["y"]), float(lc["x"]))
                    if _in_bbox(*cand):
                        coords = cand
                except (KeyError, ValueError, TypeError):
                    coords = None
            if coords:
                for uid in u["members"]:
                    con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",
                                (coords[0], coords[1], uid))
                con.execute(
                    "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"
                    " VALUES (?,?,?,?,0,?) ON CONFLICT(address) DO UPDATE SET"
                    " lat=excluded.lat, lng=excluded.lng, provider=excluded.provider,"
                    " failed=0, ts=excluded.ts",
                    (k, coords[0], coords[1], "adresses_quebec_batch", time.time()))
                done += len(u["members"])
            else:
                # l'adresse a échoué : retomber sur le centroïde de ville plutôt
                # que d'abandonner (l'offre reste cartographiable)
                cv = geo.resolve("", u["city"])
                if cv:
                    for uid in u["members"]:
                        con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",
                                    (cv[0], cv[1], uid))
                    done += len(u["members"])
                else:
                    for uid in u["members"]:
                        con.execute("UPDATE jobs SET geocode_failed=1 WHERE uid=?",
                                    (uid,))
                    failed += len(u["members"])
                con.execute(
                    "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"
                    " VALUES (?,?,?,?,1,?) ON CONFLICT(address) DO UPDATE SET"
                    " failed=1, ts=excluded.ts",
                    (k, None, None, "adresses_quebec_batch", time.time()))
            con.commit()
        print(f"[job-ka] geocode-batch {i+len(chunk)}/{len(pending)} "
              f"(résolues {done}, échecs {failed})")

    con.close()
    # offres sans adresse : centroïdes de ville en mode 1-par-1 (cache partagé)
    stats_villes = run()
    stats = {"geocoded": done + stats_villes["geocoded"],
             "failed": failed + stats_villes["failed"],
             "batches": (len(pending) + BATCH_SIZE - 1) // BATCH_SIZE}
    print(f"[job-ka] geocode-batch {stats}")
    return stats