"""
management.py — API CRUD pour immobilier.db
============================================
FastAPI standalone exposant les opérations CRUD sur les 4 tables de la base.

Démarrage autonome :
    uvicorn management:app --port 8001 --reload

Inclusion dans api.py (optionnel) :
    from management import router as mgmt_router
    app.include_router(mgmt_router, prefix="/management")

Tables gérées :
    - mutations           DVF Nord-59, transactions géocodées 2021-2025
    - loyers              Observatoire des Loyers 2025, Toulouse
    - prix_evolution      Stats agrégées par commune / type / année
    - prix_evolution_iris Stats agrégées par IRIS / type / année
"""

from __future__ import annotations

from typing import Generator, List, Literal, Optional

import pymysql
import pymysql.cursors
from fastapi import APIRouter, Depends, FastAPI, HTTPException, Query, status
from pydantic import BaseModel, Field

from domain.db import get_db as _get_db_ctx


# ── App ───────────────────────────────────────────────────────────────────────

app = FastAPI(
    title="Immobilier — API de gestion",
    description=(
        "CRUD complet sur toutes les tables de `immobilier.db`.\n\n"
        "| Table | Source | Rechargée au démarrage |\n"
        "|---|---|---|\n"
        "| `mutations` | DVF géocodé 2021-2025, Nord-59 | ⚠️ oui |\n"
        "| `loyers` | Observatoire des Loyers 2025 | ⚠️ oui |\n"
        "| `prix_evolution` | Agrégat commune, `build_prix_evolution.py` | non |\n"
        "| `prix_evolution_iris` | Agrégat IRIS, `build_mutations_iris.py` | non |\n"
        "| `flood_zones` | GEORISQUES WFS TRI, `build_flood_zones.py` | non |\n"
        "| `noise_zones` | OSM Overpass LDEN, `build_noise_zones.py` | non |\n"
        "| `nature_zones` | OSM Overpass parcs/forêts, `build_nature_zones.py` | non |\n"
        "| `poi` | OSM Overpass POI, `build_poi.py` | non |\n"
        "| `iris_pression` | SPS/BPS/NPI IRIS, `build_pression_iris.py` | non |\n\n"
        "> Les tables marquées ⚠️ sont reconstruites depuis CSV à chaque démarrage "
        "de l'API principale — les modifications manuelles seront écrasées."
    ),
    version="1.0.0",
    openapi_tags=[
        {"name": "Info",                    "description": "État de la base et des tables"},
        {"name": "Mutations",               "description": "Transactions DVF Nord-59 (2021-2025)"},
        {"name": "Loyers",                  "description": "Observatoire des Loyers 2025 — Toulouse"},
        {"name": "Prix Évolution Commune",  "description": "Prix médian par commune / type / année"},
        {"name": "Prix Évolution IRIS",     "description": "Prix médian par IRIS / type / année"},
        {"name": "Zones Inondables",        "description": "Polygones TRI GEORISQUES (flood_zones)"},
        {"name": "Zones de Bruit",          "description": "Axes OSM LDEN estimés (noise_zones)"},
        {"name": "Espaces Naturels",        "description": "Parcs / forêts OSM (nature_zones)"},
        {"name": "Points d'Intérêt",        "description": "Mairies, gares, métro, tramway (poi)"},
        {"name": "Pression IRIS",           "description": "Scores SPS / BPS / NPI par IRIS (iris_pression)"},
    ],
)

router = APIRouter()


# ── DB dependency ─────────────────────────────────────────────────────────────

def get_db() -> Generator:
    with _get_db_ctx() as conn:
        yield conn


# ── Helpers ───────────────────────────────────────────────────────────────────

def _get_or_404(conn, table: str, row_id: int) -> dict:
    row = conn.execute(f"SELECT * FROM {table} WHERE id = ?", (row_id,)).fetchone()
    if row is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Enregistrement id={row_id} introuvable dans la table '{table}'",
        )
    return dict(row)


def _table_exists(conn, table: str) -> bool:
    return conn.execute(
        "SELECT 1 FROM information_schema.tables"
        " WHERE table_schema=DATABASE() AND table_name=?",
        (table,),
    ).fetchone() is not None


def _require_iris(conn) -> None:
    if not _table_exists(conn, "prix_evolution_iris"):
        raise HTTPException(
            status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
            detail="Table 'prix_evolution_iris' absente — exécutez d'abord build_mutations_iris.py",
        )


def _do_update(conn, table: str, row_id: int, data: dict) -> dict:
    if not data:
        raise HTTPException(
            status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
            detail="Aucun champ fourni pour la mise à jour",
        )
    set_clause = ", ".join(f"{k} = ?" for k in data)
    try:
        conn.execute(
            f"UPDATE {table} SET {set_clause} WHERE id = ?",
            list(data.values()) + [row_id],
        )
        conn.commit()
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    return dict(conn.execute(f"SELECT * FROM {table} WHERE id = ?", (row_id,)).fetchone())


# ═══════════════════════════════════════════════════════════════════════════════
# Pydantic models
# ═══════════════════════════════════════════════════════════════════════════════

# ── Mutations ─────────────────────────────────────────────────────────────────

class MutationIn(BaseModel):
    id_mutation:         Optional[str]                         = Field(None, description="Identifiant DVF")
    annee:               Optional[int]                         = Field(None, ge=2000, le=2099)
    date_mutation:       Optional[str]                         = Field(None, description="Format YYYY-MM-DD")
    valeur_fonciere:     Optional[float]                       = Field(None, gt=0,    description="Prix de vente en €")
    adresse_numero:      Optional[str]                         = None
    adresse_nom_voie:    Optional[str]                         = None
    code_postal:         Optional[str]                         = None
    code_commune:        Optional[str]                         = Field(None, description="Code INSEE 5 chiffres")
    nom_commune:         Optional[str]                         = None
    code_departement:    Optional[str]                         = None
    type_local:          Optional[Literal["Appartement", "Maison"]] = None
    surface_reelle_bati: Optional[float]                       = Field(None, gt=0,    description="Surface bâtie en m²")
    prix_m2:             Optional[float]                       = Field(None, gt=0,    description="Prix au m² calculé")
    latitude:            Optional[float]                       = Field(None, ge=-90,  le=90)
    longitude:           Optional[float]                       = Field(None, ge=-180, le=180)
    code_iris:           Optional[str]                         = Field(None, description="Code IRIS 9 chiffres")
    nom_iris:            Optional[str]                         = None

    model_config = {"json_schema_extra": {"example": {
        "id_mutation": "2025-123456", "annee": 2025,
        "date_mutation": "2025-03-15", "valeur_fonciere": 185000.0,
        "adresse_nom_voie": "RUE DU GENERAL DE GAULLE",
        "code_postal": "59000", "code_commune": "59350",
        "nom_commune": "Lille", "code_departement": "59",
        "type_local": "Appartement",
        "surface_reelle_bati": 62.0, "prix_m2": 2983.87,
        "latitude": 50.6292, "longitude": 3.0573,
        "code_iris": "590350101", "nom_iris": "Vieux-Lille",
    }}}


class MutationOut(MutationIn):
    id: int


# ── Loyers ────────────────────────────────────────────────────────────────────

class LoyerIn(BaseModel):
    annee:                Optional[int]                             = Field(None, ge=2000, le=2099)
    agglomeration:        Optional[str]                             = None
    zone:                 Optional[str]                             = Field(None, description='"Ville centre" | "Périphérie" | null')
    type_habitat:         Optional[Literal["Appartement", "Maison"]] = None
    epoque_construction:  Optional[str]                             = Field(None, description='Ex. "1. Avant 1946"')
    anciennete_locataire: Optional[str]                             = Field(None, description='Ex. "1. Moins de 1 an"')
    nombre_pieces:        Optional[str]                             = Field(None, description='Ex. "Appart 2P", "Maison 4P+"')
    loyer_m2_moyen:       Optional[float]                           = Field(None, gt=0)
    loyer_m2_median:      Optional[float]                           = Field(None, gt=0)
    loyer_m2_q1:          Optional[float]                           = Field(None, gt=0)
    loyer_m2_q3:          Optional[float]                           = Field(None, gt=0)
    loyer_mensuel_moyen:  Optional[float]                           = Field(None, gt=0)
    loyer_mensuel_median: Optional[float]                           = Field(None, gt=0)
    loyer_mensuel_q1:     Optional[float]                           = Field(None, gt=0)
    loyer_mensuel_q3:     Optional[float]                           = Field(None, gt=0)
    surface_moyenne:      Optional[float]                           = Field(None, gt=0)
    nombre_observations:  Optional[int]                             = Field(None, ge=0)
    nombre_logements:     Optional[int]                             = Field(None, ge=0)

    model_config = {"json_schema_extra": {"example": {
        "annee": 2025, "agglomeration": "Agglomération de Toulouse",
        "zone": "Ville centre", "type_habitat": "Appartement",
        "nombre_pieces": "Appart 2P",
        "loyer_m2_moyen": 13.5, "loyer_m2_median": 13.2,
        "loyer_m2_q1": 11.8, "loyer_m2_q3": 15.1,
        "loyer_mensuel_moyen": 730.0, "loyer_mensuel_median": 710.0,
        "surface_moyenne": 54.0, "nombre_observations": 312,
    }}}


class LoyerOut(LoyerIn):
    id: int


# ── Prix évolution (commune) ──────────────────────────────────────────────────

class PrixEvolutionIn(BaseModel):
    annee:            int                                  = Field(..., ge=2000, le=2099)
    code_postal:      str                                  = Field(...)
    nom_commune:      str                                  = Field(...)
    type_local:       Literal["Appartement", "Maison"]     = Field(...)
    nb_transactions:  int                                  = Field(..., ge=1)
    prix_m2_moyen:    float                                = Field(..., gt=0, description="€/m² moyen")
    prix_m2_median:   float                                = Field(..., gt=0, description="€/m² médian")
    evolution_m2_pct: Optional[float]                      = Field(None, description="Évolution % vs année précédente (null si 1ère année)")
    prix_moyen:       float                                = Field(..., gt=0, description="Prix total moyen en €")
    prix_median:      float                                = Field(..., gt=0, description="Prix total médian en €")
    surface_moyenne:  float                                = Field(..., gt=0, description="Surface moyenne en m²")

    model_config = {"json_schema_extra": {"example": {
        "annee": 2024, "code_postal": "59000", "nom_commune": "Lille",
        "type_local": "Appartement", "nb_transactions": 145,
        "prix_m2_moyen": 3120.5, "prix_m2_median": 3050.0,
        "evolution_m2_pct": 2.3,
        "prix_moyen": 185000.0, "prix_median": 178000.0, "surface_moyenne": 58.5,
    }}}


class PrixEvolutionPatch(BaseModel):
    annee:            Optional[int]                                = Field(None, ge=2000, le=2099)
    code_postal:      Optional[str]                                = None
    nom_commune:      Optional[str]                                = None
    type_local:       Optional[Literal["Appartement", "Maison"]]   = None
    nb_transactions:  Optional[int]                                = Field(None, ge=1)
    prix_m2_moyen:    Optional[float]                              = Field(None, gt=0)
    prix_m2_median:   Optional[float]                              = Field(None, gt=0)
    evolution_m2_pct: Optional[float]                              = None
    prix_moyen:       Optional[float]                              = Field(None, gt=0)
    prix_median:      Optional[float]                              = Field(None, gt=0)
    surface_moyenne:  Optional[float]                              = Field(None, gt=0)


class PrixEvolutionOut(PrixEvolutionIn):
    id: int


# ── Prix évolution IRIS ───────────────────────────────────────────────────────

class PrixEvolutionIrisIn(BaseModel):
    code_iris:        str                                  = Field(..., description="Code IRIS 9 chiffres")
    nom_iris:         Optional[str]                        = None
    nom_commune:      Optional[str]                        = None
    type_local:       Literal["Appartement", "Maison"]     = Field(...)
    annee:            int                                  = Field(..., ge=2000, le=2099)
    nb_transactions:  int                                  = Field(..., ge=1)
    prix_m2_median:   float                                = Field(..., gt=0, description="€/m² médian")
    evolution_m2_pct: Optional[float]                      = Field(None, description="Évolution % vs année précédente")

    model_config = {"json_schema_extra": {"example": {
        "code_iris": "590350101", "nom_iris": "Vieux-Lille",
        "nom_commune": "Lille", "type_local": "Appartement",
        "annee": 2024, "nb_transactions": 23,
        "prix_m2_median": 3850.0, "evolution_m2_pct": 1.8,
    }}}


class PrixEvolutionIrisPatch(BaseModel):
    code_iris:        Optional[str]                                = None
    nom_iris:         Optional[str]                                = None
    nom_commune:      Optional[str]                                = None
    type_local:       Optional[Literal["Appartement", "Maison"]]   = None
    annee:            Optional[int]                                = Field(None, ge=2000, le=2099)
    nb_transactions:  Optional[int]                                = Field(None, ge=1)
    prix_m2_median:   Optional[float]                              = Field(None, gt=0)
    evolution_m2_pct: Optional[float]                              = None


class PrixEvolutionIrisOut(PrixEvolutionIrisIn):
    id: int


# ═══════════════════════════════════════════════════════════════════════════════
# Routes
# ═══════════════════════════════════════════════════════════════════════════════

# ── Info ─────────────────────────────────────────────────────────────────────

@router.get(
    "/",
    tags=["Info"],
    summary="Résumé des tables disponibles",
    response_description="Nombre de lignes et disponibilité de chaque table",
)
def db_info(db = Depends(get_db)):
    tables = [
        "mutations", "loyers", "prix_evolution", "prix_evolution_iris",
        "flood_zones", "noise_zones", "nature_zones", "poi", "iris_pression",
    ]
    result = {}
    for t in tables:
        if _table_exists(db, t):
            count = db.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
            result[t] = {"disponible": True, "nb_lignes": count}
        else:
            result[t] = {"disponible": False, "nb_lignes": 0}
    return result


# ── Mutations ─────────────────────────────────────────────────────────────────

@router.get(
    "/mutations",
    response_model=List[MutationOut],
    tags=["Mutations"],
    summary="Lister les mutations",
)
def list_mutations(
    skip:        int            = Query(0,    ge=0,                                       description="Offset de pagination"),
    limit:       int            = Query(50,   ge=1, le=500,                               description="Nombre max de résultats"),
    annee:       Optional[int]  = Query(None,                                             description="Filtrer par année"),
    nom_commune: Optional[str]  = Query(None,                                             description="Commune (partiel, insensible à la casse)"),
    type_local:  Optional[Literal["Appartement", "Maison"]] = Query(None),
    code_iris:   Optional[str]  = Query(None,                                             description="Code IRIS exact"),
    code_postal: Optional[str]  = Query(None),
    db = Depends(get_db),
):
    conditions, params = [], []
    if annee:
        conditions.append("annee = ?");        params.append(annee)
    if nom_commune:
        conditions.append("nom_commune LIKE ?"); params.append(f"%{nom_commune}%")
    if type_local:
        conditions.append("type_local = ?");   params.append(type_local)
    if code_iris:
        conditions.append("code_iris = ?");    params.append(code_iris)
    if code_postal:
        conditions.append("code_postal = ?");  params.append(code_postal)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM mutations {where} ORDER BY id LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/mutations/{mutation_id}",
    response_model=MutationOut,
    tags=["Mutations"],
    summary="Obtenir une mutation par ID",
)
def get_mutation(mutation_id: int, db = Depends(get_db)):
    return _get_or_404(db, "mutations", mutation_id)


@router.post(
    "/mutations",
    response_model=MutationOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Mutations"],
    summary="Créer une mutation",
)
def create_mutation(body: MutationIn, db = Depends(get_db)):
    data = body.model_dump()
    cols         = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    cur = db.execute(
        f"INSERT INTO mutations ({cols}) VALUES ({placeholders})",
        list(data.values()),
    )
    db.commit()
    return dict(db.execute("SELECT * FROM mutations WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/mutations/{mutation_id}",
    response_model=MutationOut,
    tags=["Mutations"],
    summary="Mettre à jour une mutation (champs fournis uniquement)",
)
def update_mutation(
    mutation_id: int, body: MutationIn, db = Depends(get_db)
):
    _get_or_404(db, "mutations", mutation_id)
    return _do_update(db, "mutations", mutation_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/mutations/{mutation_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Mutations"],
    summary="Supprimer une mutation",
)
def delete_mutation(mutation_id: int, db = Depends(get_db)):
    _get_or_404(db, "mutations", mutation_id)
    db.execute("DELETE FROM mutations WHERE id = ?", (mutation_id,))
    db.commit()


# ── Loyers ────────────────────────────────────────────────────────────────────

@router.get(
    "/loyers",
    response_model=List[LoyerOut],
    tags=["Loyers"],
    summary="Lister les loyers",
)
def list_loyers(
    skip:         int            = Query(0,   ge=0),
    limit:        int            = Query(50,  ge=1, le=500),
    agglomeration: Optional[str] = Query(None, description="Agglomération (partiel)"),
    zone:         Optional[str]  = Query(None, description='"Ville centre" | "Périphérie"'),
    type_habitat: Optional[Literal["Appartement", "Maison"]] = Query(None),
    nombre_pieces: Optional[str] = Query(None, description='Ex. "Appart 2P"'),
    db = Depends(get_db),
):
    conditions, params = [], []
    if agglomeration:
        conditions.append("agglomeration LIKE ?"); params.append(f"%{agglomeration}%")
    if zone:
        conditions.append("zone = ?");             params.append(zone)
    if type_habitat:
        conditions.append("type_habitat = ?");     params.append(type_habitat)
    if nombre_pieces:
        conditions.append("nombre_pieces = ?");    params.append(nombre_pieces)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM loyers {where} ORDER BY id LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/loyers/{loyer_id}",
    response_model=LoyerOut,
    tags=["Loyers"],
    summary="Obtenir un loyer par ID",
)
def get_loyer(loyer_id: int, db = Depends(get_db)):
    return _get_or_404(db, "loyers", loyer_id)


@router.post(
    "/loyers",
    response_model=LoyerOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Loyers"],
    summary="Créer un loyer",
)
def create_loyer(body: LoyerIn, db = Depends(get_db)):
    data = body.model_dump()
    cols         = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    cur = db.execute(
        f"INSERT INTO loyers ({cols}) VALUES ({placeholders})",
        list(data.values()),
    )
    db.commit()
    return dict(db.execute("SELECT * FROM loyers WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/loyers/{loyer_id}",
    response_model=LoyerOut,
    tags=["Loyers"],
    summary="Mettre à jour un loyer (champs fournis uniquement)",
)
def update_loyer(loyer_id: int, body: LoyerIn, db = Depends(get_db)):
    _get_or_404(db, "loyers", loyer_id)
    return _do_update(db, "loyers", loyer_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/loyers/{loyer_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Loyers"],
    summary="Supprimer un loyer",
)
def delete_loyer(loyer_id: int, db = Depends(get_db)):
    _get_or_404(db, "loyers", loyer_id)
    db.execute("DELETE FROM loyers WHERE id = ?", (loyer_id,))
    db.commit()


# ── Prix évolution (commune) ──────────────────────────────────────────────────

@router.get(
    "/prix-evolution",
    response_model=List[PrixEvolutionOut],
    tags=["Prix Évolution Commune"],
    summary="Lister les statistiques de prix par commune",
)
def list_prix_evolution(
    skip:        int            = Query(0,   ge=0),
    limit:       int            = Query(50,  ge=1, le=500),
    annee:       Optional[int]  = Query(None),
    nom_commune: Optional[str]  = Query(None, description="Commune (partiel)"),
    code_postal: Optional[str]  = Query(None),
    type_local:  Optional[Literal["Appartement", "Maison"]] = Query(None),
    db = Depends(get_db),
):
    conditions, params = [], []
    if annee:
        conditions.append("annee = ?");          params.append(annee)
    if nom_commune:
        conditions.append("nom_commune LIKE ?"); params.append(f"%{nom_commune}%")
    if code_postal:
        conditions.append("code_postal = ?");    params.append(code_postal)
    if type_local:
        conditions.append("type_local = ?");     params.append(type_local)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM prix_evolution {where} ORDER BY nom_commune, type_local, annee LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/prix-evolution/{record_id}",
    response_model=PrixEvolutionOut,
    tags=["Prix Évolution Commune"],
    summary="Obtenir une statistique par ID",
)
def get_prix_evolution(record_id: int, db = Depends(get_db)):
    return _get_or_404(db, "prix_evolution", record_id)


@router.post(
    "/prix-evolution",
    response_model=PrixEvolutionOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Prix Évolution Commune"],
    summary="Créer une statistique de prix",
)
def create_prix_evolution(body: PrixEvolutionIn, db = Depends(get_db)):
    data = body.model_dump()
    cols         = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    try:
        cur = db.execute(
            f"INSERT INTO prix_evolution ({cols}) VALUES ({placeholders})",
            list(data.values()),
        )
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    db.commit()
    return dict(db.execute("SELECT * FROM prix_evolution WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/prix-evolution/{record_id}",
    response_model=PrixEvolutionOut,
    tags=["Prix Évolution Commune"],
    summary="Mettre à jour une statistique de prix (champs fournis uniquement)",
)
def update_prix_evolution(
    record_id: int, body: PrixEvolutionPatch, db = Depends(get_db)
):
    _get_or_404(db, "prix_evolution", record_id)
    return _do_update(db, "prix_evolution", record_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/prix-evolution/{record_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Prix Évolution Commune"],
    summary="Supprimer une statistique de prix",
)
def delete_prix_evolution(record_id: int, db = Depends(get_db)):
    _get_or_404(db, "prix_evolution", record_id)
    db.execute("DELETE FROM prix_evolution WHERE id = ?", (record_id,))
    db.commit()


# ── Prix évolution IRIS ───────────────────────────────────────────────────────

@router.get(
    "/prix-evolution-iris",
    response_model=List[PrixEvolutionIrisOut],
    tags=["Prix Évolution IRIS"],
    summary="Lister les statistiques de prix par IRIS",
)
def list_prix_evolution_iris(
    skip:        int            = Query(0,   ge=0),
    limit:       int            = Query(50,  ge=1, le=500),
    annee:       Optional[int]  = Query(None),
    code_iris:   Optional[str]  = Query(None),
    nom_commune: Optional[str]  = Query(None, description="Commune (partiel)"),
    type_local:  Optional[Literal["Appartement", "Maison"]] = Query(None),
    db = Depends(get_db),
):
    _require_iris(db)
    conditions, params = [], []
    if annee:
        conditions.append("annee = ?");          params.append(annee)
    if code_iris:
        conditions.append("code_iris = ?");      params.append(code_iris)
    if nom_commune:
        conditions.append("nom_commune LIKE ?"); params.append(f"%{nom_commune}%")
    if type_local:
        conditions.append("type_local = ?");     params.append(type_local)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM prix_evolution_iris {where} "
        f"ORDER BY nom_commune, type_local, annee LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/prix-evolution-iris/{record_id}",
    response_model=PrixEvolutionIrisOut,
    tags=["Prix Évolution IRIS"],
    summary="Obtenir une statistique IRIS par ID",
)
def get_prix_evolution_iris(record_id: int, db = Depends(get_db)):
    _require_iris(db)
    return _get_or_404(db, "prix_evolution_iris", record_id)


@router.post(
    "/prix-evolution-iris",
    response_model=PrixEvolutionIrisOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Prix Évolution IRIS"],
    summary="Créer une statistique IRIS",
)
def create_prix_evolution_iris(
    body: PrixEvolutionIrisIn, db = Depends(get_db)
):
    _require_iris(db)
    data = body.model_dump()
    cols         = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    cur = db.execute(
        f"INSERT INTO prix_evolution_iris ({cols}) VALUES ({placeholders})",
        list(data.values()),
    )
    db.commit()
    return dict(db.execute("SELECT * FROM prix_evolution_iris WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/prix-evolution-iris/{record_id}",
    response_model=PrixEvolutionIrisOut,
    tags=["Prix Évolution IRIS"],
    summary="Mettre à jour une statistique IRIS (champs fournis uniquement)",
)
def update_prix_evolution_iris(
    record_id: int, body: PrixEvolutionIrisPatch, db = Depends(get_db)
):
    _require_iris(db)
    _get_or_404(db, "prix_evolution_iris", record_id)
    return _do_update(db, "prix_evolution_iris", record_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/prix-evolution-iris/{record_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Prix Évolution IRIS"],
    summary="Supprimer une statistique IRIS",
)
def delete_prix_evolution_iris(record_id: int, db = Depends(get_db)):
    _require_iris(db)
    _get_or_404(db, "prix_evolution_iris", record_id)
    db.execute("DELETE FROM prix_evolution_iris WHERE id = ?", (record_id,))
    db.commit()


# ── Helpers (tables optionnelles + PK texte) ──────────────────────────────────

def _require_table(conn, table: str) -> None:
    if not _table_exists(conn, table):
        raise HTTPException(
            status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
            detail=f"Table '{table}' absente — exécutez le script build correspondant",
        )


def _get_or_404_str(conn, table: str, pk_col: str, pk_val: str) -> dict:
    row = conn.execute(f"SELECT * FROM {table} WHERE {pk_col} = ?", (pk_val,)).fetchone()
    if row is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Enregistrement {pk_col}={pk_val!r} introuvable dans '{table}'",
        )
    return dict(row)


def _do_update_str(conn, table: str, pk_col: str, pk_val: str, data: dict) -> dict:
    if not data:
        raise HTTPException(
            status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
            detail="Aucun champ fourni pour la mise à jour",
        )
    set_clause = ", ".join(f"{k} = ?" for k in data)
    try:
        conn.execute(
            f"UPDATE {table} SET {set_clause} WHERE {pk_col} = ?",
            list(data.values()) + [pk_val],
        )
        conn.commit()
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    return dict(conn.execute(f"SELECT * FROM {table} WHERE {pk_col} = ?", (pk_val,)).fetchone())


# ═══════════════════════════════════════════════════════════════════════════════
# Pydantic models — Zones inondables
# ═══════════════════════════════════════════════════════════════════════════════

class FloodZoneIn(BaseModel):
    scenario:     Literal["frequent", "moyen", "rare"]  = Field(..., description="Scénario TRI")
    geom_type:    Literal["Polygon", "MultiPolygon"]     = Field(...)
    coordinates:  str   = Field(..., description="Coordonnées GeoJSON sérialisées en JSON")
    coords_hash:  str   = Field(..., description="SHA256 des coordonnées (déduplication)")
    bbox_min_lng: float = Field(..., ge=-180, le=180)
    bbox_min_lat: float = Field(..., ge=-90,  le=90)
    bbox_max_lng: float = Field(..., ge=-180, le=180)
    bbox_max_lat: float = Field(..., ge=-90,  le=90)
    fetched_at:   Optional[str] = Field(None, description="Horodatage ISO 8601")

    model_config = {"json_schema_extra": {"example": {
        "scenario": "moyen", "geom_type": "Polygon",
        "coordinates": "[[[3.05, 50.62], [3.06, 50.62], [3.06, 50.63], [3.05, 50.62]]]",
        "coords_hash": "abc123def456",
        "bbox_min_lng": 3.05, "bbox_min_lat": 50.62,
        "bbox_max_lng": 3.06, "bbox_max_lat": 50.63,
        "fetched_at": "2026-01-01T00:00:00",
    }}}


class FloodZonePatch(BaseModel):
    scenario:     Optional[Literal["frequent", "moyen", "rare"]] = None
    geom_type:    Optional[Literal["Polygon", "MultiPolygon"]]   = None
    coordinates:  Optional[str]   = None
    coords_hash:  Optional[str]   = None
    bbox_min_lng: Optional[float] = Field(None, ge=-180, le=180)
    bbox_min_lat: Optional[float] = Field(None, ge=-90,  le=90)
    bbox_max_lng: Optional[float] = Field(None, ge=-180, le=180)
    bbox_max_lat: Optional[float] = Field(None, ge=-90,  le=90)
    fetched_at:   Optional[str]   = None


class FloodZoneOut(FloodZoneIn):
    id: int


# ═══════════════════════════════════════════════════════════════════════════════
# Pydantic models — Zones de bruit
# ═══════════════════════════════════════════════════════════════════════════════

class NoiseZoneIn(BaseModel):
    source_type:  Literal["routier", "ferroviaire"] = Field(...)
    lden_min:     int   = Field(..., ge=40, le=90,  description="Niveau LDEN minimum (dB)")
    geom_type:    str   = Field(...,                description='"Polygon" | "MultiPolygon" | "LineString"')
    coordinates:  str   = Field(...,                description="Coordonnées GeoJSON sérialisées en JSON")
    coords_hash:  str   = Field(...,                description="SHA256 des coordonnées (déduplication)")
    bbox_min_lng: float = Field(..., ge=-180, le=180)
    bbox_min_lat: float = Field(..., ge=-90,  le=90)
    bbox_max_lng: float = Field(..., ge=-180, le=180)
    bbox_max_lat: float = Field(..., ge=-90,  le=90)
    fetched_at:   Optional[str] = None

    model_config = {"json_schema_extra": {"example": {
        "source_type": "routier", "lden_min": 65,
        "geom_type": "LineString",
        "coordinates": "[[[3.05, 50.62], [3.07, 50.63]]]",
        "coords_hash": "def456abc123",
        "bbox_min_lng": 3.05, "bbox_min_lat": 50.62,
        "bbox_max_lng": 3.07, "bbox_max_lat": 50.63,
    }}}


class NoiseZonePatch(BaseModel):
    source_type:  Optional[Literal["routier", "ferroviaire"]] = None
    lden_min:     Optional[int]   = Field(None, ge=40, le=90)
    geom_type:    Optional[str]   = None
    coordinates:  Optional[str]   = None
    coords_hash:  Optional[str]   = None
    bbox_min_lng: Optional[float] = Field(None, ge=-180, le=180)
    bbox_min_lat: Optional[float] = Field(None, ge=-90,  le=90)
    bbox_max_lng: Optional[float] = Field(None, ge=-180, le=180)
    bbox_max_lat: Optional[float] = Field(None, ge=-90,  le=90)
    fetched_at:   Optional[str]   = None


class NoiseZoneOut(NoiseZoneIn):
    id: int


# ═══════════════════════════════════════════════════════════════════════════════
# Pydantic models — Points d'intérêt
# ═══════════════════════════════════════════════════════════════════════════════

class POIIn(BaseModel):
    osm_id:     int   = Field(...,               description="Identifiant OpenStreetMap")
    type:       Literal["mairie", "gare", "metro", "tram"] = Field(...)
    name:       str   = Field(..., min_length=1, max_length=300)
    lat:        float = Field(..., ge=-90,  le=90)
    lon:        float = Field(..., ge=-180, le=180)
    fetched_at: Optional[str] = None

    model_config = {"json_schema_extra": {"example": {
        "osm_id": 123456789, "type": "gare",
        "name": "Gare de Lille-Flandres",
        "lat": 50.6366, "lon": 3.0700,
    }}}


class POIPatch(BaseModel):
    osm_id:     Optional[int]   = None
    type:       Optional[Literal["mairie", "gare", "metro", "tram"]] = None
    name:       Optional[str]   = Field(None, min_length=1, max_length=300)
    lat:        Optional[float] = Field(None, ge=-90,  le=90)
    lon:        Optional[float] = Field(None, ge=-180, le=180)
    fetched_at: Optional[str]   = None


class POIOut(POIIn):
    id: int


# ═══════════════════════════════════════════════════════════════════════════════
# Pydantic models — Espaces naturels
# ═══════════════════════════════════════════════════════════════════════════════

class NatureZoneIn(BaseModel):
    osm_id:      int  = Field(...,               description="Identifiant OpenStreetMap")
    osm_type:    str  = Field(...,               description='"way" | "relation"')
    category:    str  = Field(...,               description='"foret" | "parc" | "natural" | …')
    name:        Optional[str]  = None
    geom_type:   Literal["Polygon", "MultiPolygon"] = Field(...)
    coordinates: str  = Field(...,               description="Coordonnées GeoJSON sérialisées en JSON")
    bbox_min_lng: float = Field(..., ge=-180, le=180)
    bbox_min_lat: float = Field(..., ge=-90,  le=90)
    bbox_max_lng: float = Field(..., ge=-180, le=180)
    bbox_max_lat: float = Field(..., ge=-90,  le=90)
    fetched_at:   Optional[str] = None

    model_config = {"json_schema_extra": {"example": {
        "osm_id": 987654321, "osm_type": "relation", "category": "parc",
        "name": "Parc de la Citadelle",
        "geom_type": "Polygon",
        "coordinates": "[[[3.04, 50.63], [3.05, 50.63], [3.05, 50.64], [3.04, 50.63]]]",
        "bbox_min_lng": 3.04, "bbox_min_lat": 50.63,
        "bbox_max_lng": 3.05, "bbox_max_lat": 50.64,
    }}}


class NatureZonePatch(BaseModel):
    osm_id:      Optional[int]   = None
    osm_type:    Optional[str]   = None
    category:    Optional[str]   = None
    name:        Optional[str]   = None
    geom_type:   Optional[Literal["Polygon", "MultiPolygon"]] = None
    coordinates: Optional[str]   = None
    bbox_min_lng: Optional[float] = Field(None, ge=-180, le=180)
    bbox_min_lat: Optional[float] = Field(None, ge=-90,  le=90)
    bbox_max_lng: Optional[float] = Field(None, ge=-180, le=180)
    bbox_max_lat: Optional[float] = Field(None, ge=-90,  le=90)
    fetched_at:   Optional[str]   = None


class NatureZoneOut(NatureZoneIn):
    id: int


# ═══════════════════════════════════════════════════════════════════════════════
# Pydantic models — Pression IRIS
# ═══════════════════════════════════════════════════════════════════════════════

class IrisPressionIn(BaseModel):
    code_iris:             str            = Field(..., description="Code IRIS 9 chiffres")
    # SPS raw indicators
    senior_ratio:          Optional[float] = Field(None, ge=0, le=1)
    senior_ownership:      Optional[float] = Field(None, ge=0, le=1)
    vacancy_rate:          Optional[float] = Field(None, ge=0, le=1)
    pop_decline:           Optional[float] = Field(None, ge=0, le=1)
    poverty_rate:          Optional[float] = Field(None, ge=0, le=1)
    # BPS raw indicators
    income_level:          Optional[float] = Field(None, ge=0)
    prime_age_ratio:       Optional[float] = Field(None, ge=0, le=1)
    security_score:        Optional[float] = Field(None, ge=0, le=10)
    employment_rate:       Optional[float] = Field(None, ge=0, le=1)
    diploma_rate:          Optional[float] = Field(None, ge=0, le=1)
    greenery_pct:          Optional[float] = Field(None, ge=0, le=1)
    pop_growth:            Optional[float] = None
    amenity_score:         Optional[float] = Field(None, ge=0)
    transaction_rate:      Optional[float] = Field(None, ge=0)
    # SPS normalised
    senior_ratio_norm:     Optional[float] = Field(None, ge=0, le=1)
    senior_ownership_norm: Optional[float] = Field(None, ge=0, le=1)
    vacancy_rate_norm:     Optional[float] = Field(None, ge=0, le=1)
    pop_decline_norm:      Optional[float] = Field(None, ge=0, le=1)
    poverty_rate_norm:     Optional[float] = Field(None, ge=0, le=1)
    # BPS normalised
    income_level_norm:     Optional[float] = Field(None, ge=0, le=1)
    prime_age_ratio_norm:  Optional[float] = Field(None, ge=0, le=1)
    security_score_norm:   Optional[float] = Field(None, ge=0, le=1)
    employment_rate_norm:  Optional[float] = Field(None, ge=0, le=1)
    diploma_rate_norm:     Optional[float] = Field(None, ge=0, le=1)
    greenery_pct_norm:     Optional[float] = Field(None, ge=0, le=1)
    pop_growth_norm:       Optional[float] = Field(None, ge=0, le=1)
    amenity_score_norm:    Optional[float] = Field(None, ge=0, le=1)
    transaction_rate_norm: Optional[float] = Field(None, ge=0, le=1)
    # Composite scores
    sps:                   Optional[float] = Field(None, ge=0, le=1,  description="Selling Pressure Score [0,1]")
    bps:                   Optional[float] = Field(None, ge=0, le=1,  description="Buying Pressure Score [0,1]")
    npi:                   Optional[float] = Field(None, ge=-1, le=1, description="Net Pressure Index [-1,1]")
    built_at:              Optional[str]   = Field(None, description="Horodatage de construction ISO 8601")

    model_config = {"json_schema_extra": {"example": {
        "code_iris": "590350101",
        "senior_ratio": 0.18, "vacancy_rate": 0.06, "poverty_rate": 0.12,
        "income_level": 22000.0, "employment_rate": 0.87, "security_score": 4.2,
        "greenery_pct": 0.08,
        "sps": 0.31, "bps": 0.62, "npi": 0.31,
    }}}


class IrisPressionPatch(BaseModel):
    senior_ratio:          Optional[float] = Field(None, ge=0, le=1)
    senior_ownership:      Optional[float] = Field(None, ge=0, le=1)
    vacancy_rate:          Optional[float] = Field(None, ge=0, le=1)
    pop_decline:           Optional[float] = Field(None, ge=0, le=1)
    poverty_rate:          Optional[float] = Field(None, ge=0, le=1)
    income_level:          Optional[float] = Field(None, ge=0)
    prime_age_ratio:       Optional[float] = Field(None, ge=0, le=1)
    security_score:        Optional[float] = Field(None, ge=0, le=10)
    employment_rate:       Optional[float] = Field(None, ge=0, le=1)
    diploma_rate:          Optional[float] = Field(None, ge=0, le=1)
    greenery_pct:          Optional[float] = Field(None, ge=0, le=1)
    pop_growth:            Optional[float] = None
    amenity_score:         Optional[float] = Field(None, ge=0)
    transaction_rate:      Optional[float] = Field(None, ge=0)
    senior_ratio_norm:     Optional[float] = Field(None, ge=0, le=1)
    senior_ownership_norm: Optional[float] = Field(None, ge=0, le=1)
    vacancy_rate_norm:     Optional[float] = Field(None, ge=0, le=1)
    pop_decline_norm:      Optional[float] = Field(None, ge=0, le=1)
    poverty_rate_norm:     Optional[float] = Field(None, ge=0, le=1)
    income_level_norm:     Optional[float] = Field(None, ge=0, le=1)
    prime_age_ratio_norm:  Optional[float] = Field(None, ge=0, le=1)
    security_score_norm:   Optional[float] = Field(None, ge=0, le=1)
    employment_rate_norm:  Optional[float] = Field(None, ge=0, le=1)
    diploma_rate_norm:     Optional[float] = Field(None, ge=0, le=1)
    greenery_pct_norm:     Optional[float] = Field(None, ge=0, le=1)
    pop_growth_norm:       Optional[float] = Field(None, ge=0, le=1)
    amenity_score_norm:    Optional[float] = Field(None, ge=0, le=1)
    transaction_rate_norm: Optional[float] = Field(None, ge=0, le=1)
    sps:                   Optional[float] = Field(None, ge=0, le=1)
    bps:                   Optional[float] = Field(None, ge=0, le=1)
    npi:                   Optional[float] = Field(None, ge=-1, le=1)
    built_at:              Optional[str]   = None


class IrisPressionOut(IrisPressionIn):
    pass  # code_iris already in IrisPressionIn


# ═══════════════════════════════════════════════════════════════════════════════
# Routes — Zones inondables
# ═══════════════════════════════════════════════════════════════════════════════

@router.get(
    "/flood-zones",
    response_model=List[FloodZoneOut],
    tags=["Zones Inondables"],
    summary="Lister les zones inondables",
)
def list_flood_zones(
    skip:     int = Query(0,   ge=0),
    limit:    int = Query(50,  ge=1, le=500),
    scenario: Optional[Literal["frequent", "moyen", "rare"]] = Query(None),
    db = Depends(get_db),
):
    _require_table(db, "flood_zones")
    conditions, params = [], []
    if scenario:
        conditions.append("scenario = ?"); params.append(scenario)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM flood_zones {where} ORDER BY id LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/flood-zones/{zone_id}",
    response_model=FloodZoneOut,
    tags=["Zones Inondables"],
    summary="Obtenir une zone inondable par ID",
)
def get_flood_zone(zone_id: int, db = Depends(get_db)):
    _require_table(db, "flood_zones")
    return _get_or_404(db, "flood_zones", zone_id)


@router.post(
    "/flood-zones",
    response_model=FloodZoneOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Zones Inondables"],
    summary="Créer une zone inondable",
)
def create_flood_zone(body: FloodZoneIn, db = Depends(get_db)):
    _require_table(db, "flood_zones")
    data = body.model_dump()
    cols = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    try:
        cur = db.execute(
            f"INSERT INTO flood_zones ({cols}) VALUES ({placeholders})",
            list(data.values()),
        )
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    db.commit()
    return dict(db.execute("SELECT * FROM flood_zones WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/flood-zones/{zone_id}",
    response_model=FloodZoneOut,
    tags=["Zones Inondables"],
    summary="Mettre à jour une zone inondable (champs fournis uniquement)",
)
def update_flood_zone(
    zone_id: int, body: FloodZonePatch, db = Depends(get_db)
):
    _require_table(db, "flood_zones")
    _get_or_404(db, "flood_zones", zone_id)
    return _do_update(db, "flood_zones", zone_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/flood-zones/{zone_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Zones Inondables"],
    summary="Supprimer une zone inondable",
)
def delete_flood_zone(zone_id: int, db = Depends(get_db)):
    _require_table(db, "flood_zones")
    _get_or_404(db, "flood_zones", zone_id)
    db.execute("DELETE FROM flood_zones WHERE id = ?", (zone_id,))
    db.commit()


# ═══════════════════════════════════════════════════════════════════════════════
# Routes — Zones de bruit
# ═══════════════════════════════════════════════════════════════════════════════

@router.get(
    "/noise-zones",
    response_model=List[NoiseZoneOut],
    tags=["Zones de Bruit"],
    summary="Lister les zones de bruit",
)
def list_noise_zones(
    skip:        int = Query(0,  ge=0),
    limit:       int = Query(50, ge=1, le=500),
    source_type: Optional[Literal["routier", "ferroviaire"]] = Query(None),
    lden_min:    Optional[int]  = Query(None, ge=40, le=90, description="Filtre niveau LDEN minimum ≥ valeur"),
    db = Depends(get_db),
):
    _require_table(db, "noise_zones")
    conditions, params = [], []
    if source_type:
        conditions.append("source_type = ?"); params.append(source_type)
    if lden_min is not None:
        conditions.append("lden_min >= ?");   params.append(lden_min)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM noise_zones {where} ORDER BY id LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/noise-zones/{zone_id}",
    response_model=NoiseZoneOut,
    tags=["Zones de Bruit"],
    summary="Obtenir une zone de bruit par ID",
)
def get_noise_zone(zone_id: int, db = Depends(get_db)):
    _require_table(db, "noise_zones")
    return _get_or_404(db, "noise_zones", zone_id)


@router.post(
    "/noise-zones",
    response_model=NoiseZoneOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Zones de Bruit"],
    summary="Créer une zone de bruit",
)
def create_noise_zone(body: NoiseZoneIn, db = Depends(get_db)):
    _require_table(db, "noise_zones")
    data = body.model_dump()
    cols = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    try:
        cur = db.execute(
            f"INSERT INTO noise_zones ({cols}) VALUES ({placeholders})",
            list(data.values()),
        )
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    db.commit()
    return dict(db.execute("SELECT * FROM noise_zones WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/noise-zones/{zone_id}",
    response_model=NoiseZoneOut,
    tags=["Zones de Bruit"],
    summary="Mettre à jour une zone de bruit (champs fournis uniquement)",
)
def update_noise_zone(
    zone_id: int, body: NoiseZonePatch, db = Depends(get_db)
):
    _require_table(db, "noise_zones")
    _get_or_404(db, "noise_zones", zone_id)
    return _do_update(db, "noise_zones", zone_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/noise-zones/{zone_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Zones de Bruit"],
    summary="Supprimer une zone de bruit",
)
def delete_noise_zone(zone_id: int, db = Depends(get_db)):
    _require_table(db, "noise_zones")
    _get_or_404(db, "noise_zones", zone_id)
    db.execute("DELETE FROM noise_zones WHERE id = ?", (zone_id,))
    db.commit()


# ═══════════════════════════════════════════════════════════════════════════════
# Routes — Points d'intérêt
# ═══════════════════════════════════════════════════════════════════════════════

@router.get(
    "/poi",
    response_model=List[POIOut],
    tags=["Points d'Intérêt"],
    summary="Lister les points d'intérêt",
)
def list_poi(
    skip:     int = Query(0,  ge=0),
    limit:    int = Query(50, ge=1, le=500),
    type:     Optional[Literal["mairie", "gare", "metro", "tram"]] = Query(None),
    name:     Optional[str] = Query(None, description="Nom partiel, insensible à la casse"),
    db = Depends(get_db),
):
    _require_table(db, "poi")
    conditions, params = [], []
    if type:
        conditions.append("type = ?");       params.append(type)
    if name:
        conditions.append("name LIKE ?");    params.append(f"%{name}%")
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM poi {where} ORDER BY type, name LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/poi/{poi_id}",
    response_model=POIOut,
    tags=["Points d'Intérêt"],
    summary="Obtenir un point d'intérêt par ID",
)
def get_poi(poi_id: int, db = Depends(get_db)):
    _require_table(db, "poi")
    return _get_or_404(db, "poi", poi_id)


@router.post(
    "/poi",
    response_model=POIOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Points d'Intérêt"],
    summary="Créer un point d'intérêt",
)
def create_poi(body: POIIn, db = Depends(get_db)):
    _require_table(db, "poi")
    data = body.model_dump()
    cols = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    try:
        cur = db.execute(
            f"INSERT INTO poi ({cols}) VALUES ({placeholders})",
            list(data.values()),
        )
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    db.commit()
    return dict(db.execute("SELECT * FROM poi WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/poi/{poi_id}",
    response_model=POIOut,
    tags=["Points d'Intérêt"],
    summary="Mettre à jour un point d'intérêt (champs fournis uniquement)",
)
def update_poi(poi_id: int, body: POIPatch, db = Depends(get_db)):
    _require_table(db, "poi")
    _get_or_404(db, "poi", poi_id)
    return _do_update(db, "poi", poi_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/poi/{poi_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Points d'Intérêt"],
    summary="Supprimer un point d'intérêt",
)
def delete_poi(poi_id: int, db = Depends(get_db)):
    _require_table(db, "poi")
    _get_or_404(db, "poi", poi_id)
    db.execute("DELETE FROM poi WHERE id = ?", (poi_id,))
    db.commit()


# ═══════════════════════════════════════════════════════════════════════════════
# Routes — Espaces naturels
# ═══════════════════════════════════════════════════════════════════════════════

@router.get(
    "/nature-zones",
    response_model=List[NatureZoneOut],
    tags=["Espaces Naturels"],
    summary="Lister les espaces naturels",
)
def list_nature_zones(
    skip:     int = Query(0,  ge=0),
    limit:    int = Query(50, ge=1, le=500),
    category: Optional[str] = Query(None, description="Catégorie : foret, parc, natural…"),
    name:     Optional[str] = Query(None, description="Nom partiel, insensible à la casse"),
    db = Depends(get_db),
):
    _require_table(db, "nature_zones")
    conditions, params = [], []
    if category:
        conditions.append("category = ?");   params.append(category)
    if name:
        conditions.append("name LIKE ?");    params.append(f"%{name}%")
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM nature_zones {where} ORDER BY id LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/nature-zones/{zone_id}",
    response_model=NatureZoneOut,
    tags=["Espaces Naturels"],
    summary="Obtenir un espace naturel par ID",
)
def get_nature_zone(zone_id: int, db = Depends(get_db)):
    _require_table(db, "nature_zones")
    return _get_or_404(db, "nature_zones", zone_id)


@router.post(
    "/nature-zones",
    response_model=NatureZoneOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Espaces Naturels"],
    summary="Créer un espace naturel",
)
def create_nature_zone(body: NatureZoneIn, db = Depends(get_db)):
    _require_table(db, "nature_zones")
    data = body.model_dump()
    cols = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    try:
        cur = db.execute(
            f"INSERT INTO nature_zones ({cols}) VALUES ({placeholders})",
            list(data.values()),
        )
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    db.commit()
    return dict(db.execute("SELECT * FROM nature_zones WHERE id = ?", (cur.lastrowid,)).fetchone())


@router.put(
    "/nature-zones/{zone_id}",
    response_model=NatureZoneOut,
    tags=["Espaces Naturels"],
    summary="Mettre à jour un espace naturel (champs fournis uniquement)",
)
def update_nature_zone(
    zone_id: int, body: NatureZonePatch, db = Depends(get_db)
):
    _require_table(db, "nature_zones")
    _get_or_404(db, "nature_zones", zone_id)
    return _do_update(db, "nature_zones", zone_id, body.model_dump(exclude_unset=True))


@router.delete(
    "/nature-zones/{zone_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Espaces Naturels"],
    summary="Supprimer un espace naturel",
)
def delete_nature_zone(zone_id: int, db = Depends(get_db)):
    _require_table(db, "nature_zones")
    _get_or_404(db, "nature_zones", zone_id)
    db.execute("DELETE FROM nature_zones WHERE id = ?", (zone_id,))
    db.commit()


# ═══════════════════════════════════════════════════════════════════════════════
# Routes — Pression IRIS
# ═══════════════════════════════════════════════════════════════════════════════

@router.get(
    "/iris-pressure",
    response_model=List[IrisPressionOut],
    tags=["Pression IRIS"],
    summary="Lister les scores de pression IRIS",
)
def list_iris_pression(
    skip:      int = Query(0,  ge=0),
    limit:     int = Query(50, ge=1, le=500),
    code_iris: Optional[str] = Query(None, description="Code IRIS exact (9 chiffres)"),
    db = Depends(get_db),
):
    _require_table(db, "iris_pression")
    conditions, params = [], []
    if code_iris:
        conditions.append("code_iris = ?"); params.append(code_iris)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows = db.execute(
        f"SELECT * FROM iris_pression {where} ORDER BY code_iris LIMIT ? OFFSET ?",
        params + [limit, skip],
    ).fetchall()
    return [dict(r) for r in rows]


@router.get(
    "/iris-pressure/{code_iris}",
    response_model=IrisPressionOut,
    tags=["Pression IRIS"],
    summary="Obtenir les scores de pression d'un IRIS",
)
def get_iris_pression(code_iris: str, db = Depends(get_db)):
    _require_table(db, "iris_pression")
    return _get_or_404_str(db, "iris_pression", "code_iris", code_iris)


@router.post(
    "/iris-pressure",
    response_model=IrisPressionOut,
    status_code=status.HTTP_201_CREATED,
    tags=["Pression IRIS"],
    summary="Créer un enregistrement de pression IRIS",
)
def create_iris_pression(body: IrisPressionIn, db = Depends(get_db)):
    _require_table(db, "iris_pression")
    data = body.model_dump()
    cols = ", ".join(data.keys())
    placeholders = ", ".join("?" for _ in data)
    try:
        db.execute(
            f"INSERT INTO iris_pression ({cols}) VALUES ({placeholders})",
            list(data.values()),
        )
    except pymysql.IntegrityError as exc:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=str(exc))
    db.commit()
    return _get_or_404_str(db, "iris_pression", "code_iris", body.code_iris)


@router.put(
    "/iris-pressure/{code_iris}",
    response_model=IrisPressionOut,
    tags=["Pression IRIS"],
    summary="Mettre à jour les scores d'un IRIS (champs fournis uniquement)",
)
def update_iris_pression(
    code_iris: str, body: IrisPressionPatch, db = Depends(get_db)
):
    _require_table(db, "iris_pression")
    _get_or_404_str(db, "iris_pression", "code_iris", code_iris)
    return _do_update_str(db, "iris_pression", "code_iris", code_iris, body.model_dump(exclude_unset=True))


@router.delete(
    "/iris-pressure/{code_iris}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Pression IRIS"],
    summary="Supprimer les scores d'un IRIS",
)
def delete_iris_pression(code_iris: str, db = Depends(get_db)):
    _require_table(db, "iris_pression")
    _get_or_404_str(db, "iris_pression", "code_iris", code_iris)
    db.execute("DELETE FROM iris_pression WHERE code_iris = ?", (code_iris,))
    db.commit()


# ── Mount router ──────────────────────────────────────────────────────────────

app.include_router(router)