"""
iris_map.py — IRIS neighborhood boundaries + security scores + DVF prices for Nord (59).

Data sources (all pre-computed by build_iris_data.py):
  data/2026/iris_nord.geojson   — 1 345 IRIS polygons from IGN Géoplateforme WFS
  data/2026/iris_prix.json      — avg price/m² per IRIS (DVF 2015-2025, spatial join)
  data/2026/iris_securite.json  — danger score 1-10 per IRIS
                                   (commune base score ± intra-commune price adjustment)

Fallbacks (used when pre-computed files are absent or incomplete):
  prix_evolution_iris (MySQL)           — latest available year per IRIS from DB
  security_nord (MySQL)                 — commune-level danger scores
  data/20*/59.csv                       — commune-level DVF averages
"""

from __future__ import annotations

import asyncio
import glob
import json
import logging
import os

import httpx
import pandas as pd

from domain.core.mysql_db import get_connection, load_security_scores

logger = logging.getLogger("iris_map")

_IGN_WFS    = "https://data.geopf.fr/wfs/ows"
_IRIS_LAYER = "STATISTICALUNITS.IRISGE:iris_ge"
_PAGE_SIZE  = 500

_ROOT          = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
_GEOJSON_CACHE = os.path.join("data", "2026", "iris_nord.geojson")
_PRIX_JSON     = os.path.join("data", "2026", "iris_prix.json")
_SEC_JSON      = os.path.join("data", "2026", "iris_securite.json")

_SCORE_LABELS = {
    1: "Très faible", 2: "Très faible",
    3: "Faible",      4: "Faible",
    5: "Modéré",      6: "Modéré",
    7: "Élevé",       8: "Élevé",
    9: "Très élevé",  10: "Très élevé",
}


def _load_security() -> dict[str, int]:
    """Load per-IRIS danger scores from pre-computed JSON; fall back to MySQL security_nord."""
    if os.path.exists(_SEC_JSON):
        logger.info("Loading IRIS-level security scores from %s", _SEC_JSON)
        with open(_SEC_JSON, encoding="utf-8") as f:
            return {k: int(v) for k, v in json.load(f).items()}

    logger.warning("IRIS security file missing — using MySQL security_nord fallback")
    try:
        return load_security_scores()
    except Exception as exc:
        logger.warning("MySQL security fallback failed: %s — returning empty scores", exc)
        return {}


def _load_prix_from_db() -> dict[str, int]:
    """
    Query prix_evolution_iris for the latest available prix_m2_median per IRIS (Nord 59).
    Returns {code_iris_9digit: prix_m2} using the most recent year with data.
    Used to fill gaps left by iris_prix.json (IRIS with too few transactions for the
    spatial join, or when the static file is absent).
    """
    try:
        conn = get_connection()
    except RuntimeError as exc:
        logger.warning("MySQL unavailable for price fallback: %s", exc)
        return {}
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT p.code_iris, p.prix_m2_median
                FROM prix_evolution_iris p
                INNER JOIN (
                    SELECT code_iris, MAX(annee) AS last_annee
                    FROM prix_evolution_iris
                    WHERE code_iris LIKE '59%'
                    GROUP BY code_iris
                ) latest
                  ON p.code_iris = latest.code_iris
                 AND p.annee     = latest.last_annee
                WHERE p.code_iris LIKE '59%'
                """
            )
            rows = cur.fetchall()
    except Exception as exc:
        logger.warning("Could not load DB prices: %s", exc)
        return {}
    finally:
        conn.close()

    result: dict[str, int] = {}
    for row in rows:
        existing = result.get(row["code_iris"])
        new_val  = int(round(row["prix_m2_median"]))
        if existing is None:
            result[row["code_iris"]] = new_val
        else:
            result[row["code_iris"]] = (existing + new_val) // 2  # avg Maison+Appartement
    logger.info("DB prix fallback loaded: %d IRIS from prix_evolution_iris", len(result))
    return result


def _load_prix() -> dict[str, int]:
    """
    Load per-IRIS avg price/m² with a three-level fallback:
      1. iris_prix.json   — pre-computed spatial join (all DVF years), IRIS-level
      2. prix_evolution_iris (DB) — latest available year per IRIS, fills JSON gaps
      3. commune-level CSV averages — last resort when DB also lacks data
    """
    # Level 1 — pre-computed JSON (IRIS-level, most accurate)
    json_prices: dict[str, int] = {}
    if os.path.exists(_PRIX_JSON):
        logger.info("Loading IRIS-level prices from %s", _PRIX_JSON)
        with open(_PRIX_JSON, encoding="utf-8") as f:
            json_prices = {k: int(v) for k, v in json.load(f).items()}
        logger.info("  iris_prix.json: %d IRIS", len(json_prices))

    # Level 2 — DB latest price (fills IRIS missing from static JSON)
    db_prices = _load_prix_from_db()
    gaps_filled = 0
    result = dict(json_prices)
    for code, prix in db_prices.items():
        if code not in result:
            result[code] = prix
            gaps_filled += 1
    if gaps_filled:
        logger.info("  DB fallback filled %d additional IRIS", gaps_filled)

    if result:
        return result

    # Level 3 — commune-level CSV (last resort when no JSON and no DB)
    logger.warning("No IRIS price data available — falling back to commune CSV averages")
    pattern = os.path.join("data", "20*", "59.csv")
    files = sorted(glob.glob(pattern))
    if not files:
        logger.warning("No Nord DVF CSV files found at %s", pattern)
        return {}

    cols_needed = ["code_commune", "valeur_fonciere", "surface_reelle_bati", "type_local"]
    chunks = []
    for fpath in files:
        try:
            df = pd.read_csv(
                fpath,
                usecols=cols_needed,
                dtype={"code_commune": str},
                low_memory=False,
            )
            chunks.append(df)
        except Exception as exc:
            logger.warning("Could not read %s: %s", fpath, exc)

    if not chunks:
        return {}

    data = pd.concat(chunks, ignore_index=True)
    data = data[
        data["type_local"].isin(["Appartement", "Maison"])
        & (data["surface_reelle_bati"] > 9)
        & (data["valeur_fonciere"].between(10_000, 5_000_000))
    ].copy()
    data["prix_m2"] = data["valeur_fonciere"] / data["surface_reelle_bati"]
    data = data[data["prix_m2"].between(500, 12_000)]
    data["code_commune"] = data["code_commune"].astype(str).str.zfill(5)

    commune_result = (
        data.groupby("code_commune")["prix_m2"]
        .mean()
        .round(0)
        .astype(int)
        .to_dict()
    )
    logger.info("DVF commune prices loaded: %d communes", len(commune_result))
    return commune_result


async def _fetch_page(client: httpx.AsyncClient, start: int) -> list[dict]:
    r = await client.get(_IGN_WFS, params={
        "SERVICE": "WFS", "VERSION": "2.0.0", "REQUEST": "GetFeature",
        "TYPENAMES": _IRIS_LAYER,
        "COUNT": str(_PAGE_SIZE), "STARTINDEX": str(start),
        "CQL_FILTER": "code_insee LIKE '59%'",
        "outputFormat": "application/json",
    })
    r.raise_for_status()
    return r.json().get("features", [])


async def _fetch_iris_features() -> list[dict]:
    """Fetch IRIS polygons from local cache or IGN WFS."""
    if os.path.exists(_GEOJSON_CACHE):
        logger.info("Loading IRIS geometries from cache %s", _GEOJSON_CACHE)
        with open(_GEOJSON_CACHE, encoding="utf-8") as f:
            return json.load(f).get("features", [])

    logger.info("Fetching IRIS geometries from IGN WFS…")
    async with httpx.AsyncClient(timeout=90.0) as client:
        r0 = await client.get(_IGN_WFS, params={
            "SERVICE": "WFS", "VERSION": "2.0.0", "REQUEST": "GetFeature",
            "TYPENAMES": _IRIS_LAYER,
            "COUNT": str(_PAGE_SIZE), "STARTINDEX": "0",
            "CQL_FILTER": "code_insee LIKE '59%'",
            "outputFormat": "application/json",
        })
        r0.raise_for_status()
        page0    = r0.json()
        total    = int(page0.get("numberMatched", 0))
        features = list(page0.get("features", []))
        logger.info("IRIS Nord: %d total — fetching remaining pages…", total)

        starts = range(_PAGE_SIZE, total, _PAGE_SIZE)
        if starts:
            pages = await asyncio.gather(*[_fetch_page(client, s) for s in starts])
            for page in pages:
                features.extend(page)

    logger.info("Fetched %d IRIS features", len(features))
    return features


def _is_iris_keyed(scores: dict, prices: dict) -> bool:
    """True when the dicts are keyed by 9-digit IRIS codes (not 5-digit commune codes)."""
    sample_key = next(iter(scores), "")
    return len(sample_key) == 9


async def build_iris_geojson() -> dict:
    """
    Return an enriched GeoJSON FeatureCollection for all Nord IRIS polygons.

    Properties per feature:
      code_iris, code_insee, nom_commune, nom_iris, type_iris,
      danger_score, danger_label, prix_m2
    """
    scores   = _load_security()
    prix_map = _load_prix()
    iris_keyed = _is_iris_keyed(scores, prix_map)

    features = await _fetch_iris_features()
    logger.info("Building enriched GeoJSON (%d features, IRIS-keyed=%s)…",
                len(features), iris_keyed)

    enriched = []
    for feat in features:
        p          = feat.get("properties", {})
        code_iris  = str(p.get("code_iris", "")).zfill(9)
        code_insee = code_iris[:5]
        nom_commune = str(p.get("nom_commune", ""))
        nom_iris    = str(p.get("nom_iris", ""))

        if iris_keyed:
            score = scores.get(code_iris) or scores.get(code_insee, 3)
            prix  = prix_map.get(code_iris) or prix_map.get(code_insee)
        else:
            score = scores.get(code_insee, 3)
            prix  = prix_map.get(code_insee)

        feat["properties"] = {
            "code_iris":    code_iris,
            "code_insee":   code_insee,
            "nom_commune":  nom_commune,
            "nom_iris":     nom_iris,
            "type_iris":    p.get("type_iris", ""),
            "danger_score": score,
            "danger_label": _SCORE_LABELS.get(score, "—"),
            "prix_m2":      prix,
        }
        enriched.append(feat)

    return {"type": "FeatureCollection", "features": enriched}