# -----------------------------------------------------------------------------
# Immo-Ka — Agrégateur de propriétés à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# geocode.py : géocodage des adresses via Nominatim (OpenStreetMap)
#   - cache persistant (table geocode_cache) : une adresse n'est géocodée
#     qu'une seule fois, et par immeuble (adresse normalisée), pas par annonce
#   - politesse : 1 requête/seconde max, User-Agent identifiable
#   - validation : les coordonnées doivent tomber dans la région attendue
#     (bounding box par ville), sinon flag geocode_failed — jamais de
#     coordonnées bidon
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import sqlite3

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 rues trop récentes pour OpenStreetMap (développements neufs de Lévis…)
AQ_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"
          "Adresse_Geocodage/GeocodeServer/findAddressCandidates")
AQ_MIN_SCORE = 75
# 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 = "ImmoKaBot/1.0 (agregateur logements Quebec; +contact@spboucher.ai)"
REQUEST_DELAY = 1.1          # règle Nominatim : max 1 req/s
REQUEST_DELAY_AQ = 0.12      # Adresses Québec (MERN) : pas de limite stricte
RETRY_FAILED_AFTER = 30 * 86400   # re-tenter les échecs après 30 jours

# Bounding boxes (lat_min, lat_max, lng_min, lng_max)
_BBOX_QUEBEC = (46.55, 47.10, -71.75, -70.85)     # Québec, Lévis, St-Augustin
# garde-fou provincial : couvre Gatineau, l'Abitibi, le Saguenay, la Côte-Nord
# et la Gaspésie (expansion provinciale) — même boîte que schema.finalize()
_BBOX_SUD_QC = (44.50, 63.00, -80.00, -56.00)

_VILLES_QUEBEC = {"quebec", "levis", "saint-augustin-de-desmaures",
                  "l'ancienne-lorette", "ancienne-lorette"}


def _bbox_for(city: str) -> tuple[float, float, float, float]:
    key = strip_accents((city or "").strip().lower())
    return _BBOX_QUEBEC if key in _VILLES_QUEBEC else _BBOX_SUD_QC


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


def norm_key(address: str) -> str:
    """Clé de cache : adresse normalisée (casse, accents, espaces, n° d'app.)."""
    s = strip_accents((address or "").lower())
    s = re.sub(r"\b(?:app?t?|appartement|unite|suite|#)\.?\s*[\w-]+\b", " ", s)
    s = re.sub(r"[^a-z0-9]+", " ", s)
    return re.sub(r"\s+", " ", s).strip()


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.strip()
        # « 101-2905 Rue X » = unité 101, civique 2905 -> garder le civique
        s = re.sub(r"^(\d+)-(\d+)\s", r"\2 ", s)
        # « 6275 et 6375 boulevard X » -> premier civique
        s = re.sub(r"^(\d+)\s+et\s+\d+\s", r"\1 ", s)
        # « Montréal - Laval » / « Montréal - Île-des-Soeurs » -> garder le vrai lieu
        s = re.sub(r"montr[ée]al\s*-\s*", "", s, flags=re.I)
        # « bureau 105 » / « suite 3 » / « local B » : suffixes de bureau
        s = re.sub(r",?\s*(?:bureau|suite|local|app\.?|apt\.?)\s*[\w-]+\b", "", s, flags=re.I)
        # « Vanier (Québec) » -> « Vanier, Québec »
        s = re.sub(r"\s*\((qu[ée]bec)\)", r", \1", s, flags=re.I)
        # abréviations cardinales : « Rue Salaberry O » -> « Ouest »
        s = re.sub(r"\bO\.?(?=,|\s*$)", "Ouest", s)
        s = re.sub(r"\bE\.?(?=,|\s*$)", "Est", s)
        return s

    def _build_query(self, address: str, city: str) -> str:
        q = self._clean(address)
        key = strip_accents(q.lower())
        if city and strip_accents(city.lower()) not in key:
            q += f", {city}"
        if "quebec" not in strip_accents(q.lower()) and "qc" not in q.lower():
            q += ", Québec"
        return q + ", Canada"

    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).

        Couvre les rues trop récentes pour OSM. Seuil de score AQ_MIN_SCORE
        pour éviter les correspondances approximatives sur une autre rue.
        """
        premier = self._clean(address).split(",")[0].strip()
        ville = (city or "Québec").strip()
        wait = REQUEST_DELAY_AQ - (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.

        1) structurée civique+rue (évite l'ambiguïté « Québec » ville/province)
        2) recherche libre complète
        3) structurée rue seule -> centroïde de rue (repli acceptable pour la
           carte quand le numéro civique est absent d'OpenStreetMap)
        """
        premier = self._clean(address).split(",")[0].strip()
        ville = (city or "").strip()
        if not ville:
            k = strip_accents(address.lower())
            ville = "Lévis" if "levis" in k else "Québec"
        commun = {"city": ville, "state": "Québec", "country": "Canada"}

        tries: list[dict] = []
        m = re.match(r"^(\d+)[,\s]+(.{4,})$", premier)
        if m:
            tries.append({"street": f"{m.group(1)} {m.group(2)}", **commun})
        tries.append({"q": self._build_query(address, city)})
        if m:
            tries.append({"street": m.group(2), **commun})
        return tries

    def resolve(self, address: str, city: str) -> tuple[float, float] | None:
        """Adresse -> (lat, lng), via cache puis Nominatim. None si introuvable."""
        key = norm_key(address)
        if not key or len(key) < 6:
            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

        bbox = _bbox_for(city)
        coords = None
        # Adresses Québec (MERN) d'abord : rapide, autoritatif au Québec, pas de
        # limite stricte. Nominatim (1 req/s) seulement en repli.
        provider = "adresses_quebec"
        c = self._query_adresses_quebec(address, city)
        if c is not None and _in_bbox(*c, bbox):
            coords = c
        if coords is None:
            provider = "nominatim"
            for params in self._attempts(address, city):
                c = self._query_nominatim(params)
                if c is not None and _in_bbox(*c, bbox):
                    coords = c
                    break
        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 annonces actives sans coordonnées, par adresse unique.

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

    # regrouper par immeuble (adresse normalisée)
    groupes: dict[str, list] = {}
    for r in rows:
        groupes.setdefault(norm_key(r["address"]), []).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
        try:
            coords = geo.resolve(members[0]["address"], members[0]["city"])
            if coords:
                for r in members:
                    con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",
                                (coords[0], coords[1], r["uid"]))
                done += len(members)
            else:
                # introuvable/hors zone : flag ; jamais de coordonnées bidon
                for r in members:
                    con.execute("UPDATE listings SET geocode_failed=1 WHERE uid=?",
                                (r["uid"],))
                failed += len(members)
            con.commit()
        except sqlite3.OperationalError:
            # verrou transitoire (watcher/refresh_dedup) : on saute cette adresse,
            # elle sera reprise au prochain passage — jamais crasher tout le run.
            try:
                con.rollback()
            except sqlite3.Error:
                pass
            time.sleep(1.0)
            continue

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


AQ_BATCH_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"
                "Adresse_Geocodage/GeocodeServer/geocodeAddresses")
BATCH_SIZE = 200


def run_batch(limit: int | None = None) -> dict:
    """Géocodage EN LOT via Adresses Québec (`geocodeAddresses`, jusqu'à 1000
    adresses/requête) — ~35 requêtes pour tout le parc au lieu de dizaines de
    milliers. Beaucoup plus rapide que le mode 1-par-1 (et que Nominatim)."""
    con = db.connect()
    geo = Geocoder(con)     # pour réutiliser _clean / bbox
    rows = con.execute(
        """SELECT uid, address, city FROM listings
           WHERE active=1 AND lat IS NULL AND address<>'' AND geocode_failed=0
             AND dup_hidden=0 ORDER BY address""").fetchall()
    # 1 entrée par immeuble (adresse normalisée) ; on saute les échecs en cache
    uniq: dict[str, dict] = {}
    for r in rows:
        k = norm_key(r["address"])
        if not k or len(k) < 6:
            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"]:      # déjà résolu : appliquer direct
            for uid in u["members"]:
                con.execute("UPDATE listings 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:
            # POST obligatoire : le param `addresses` (JSON de N records) est trop
            # long pour une URL GET dès quelques dizaines d'adresses.
            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"[immo-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, _bbox_for(u["city"])):
                        coords = cand
                except (KeyError, ValueError, TypeError):
                    coords = None
            try:
                if coords:
                    for uid in u["members"]:
                        con.execute("UPDATE listings 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:
                    for uid in u["members"]:
                        con.execute("UPDATE listings SET geocode_failed=1 WHERE uid=?", (uid,))
                    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()))
                    failed += len(u["members"])
                con.commit()
            except sqlite3.OperationalError:
                try: con.rollback()
                except sqlite3.Error: pass
                time.sleep(1.0)
        print(f"[immo-ka] geocode-batch {i+len(chunk)}/{len(pending)} "
              f"(résolues {done}, échecs {failed})")
    con.close()
    stats = {"geocoded": done, "failed": failed, "batches": (len(pending)+BATCH_SIZE-1)//BATCH_SIZE}
    print(f"[immo-ka] geocode-batch {stats}")
    return stats