import os
from pathlib import Path
from typing import Optional, Any, Tuple, List, Dict

from dotenv import load_dotenv
from fastapi import FastAPI, HTTPException, Query
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import FileResponse
from fastapi.staticfiles import StaticFiles
import psycopg

load_dotenv(override=True)

def get_conn():
    return psycopg.connect(
        host=os.environ["PGHOST"],
        port=int(os.environ.get("PGPORT", "5432")),
        dbname=os.environ["PGDATABASE"],
        user=os.environ["PGUSER"],
        password=os.environ["PGPASSWORD"],
        sslmode=os.environ.get("PGSSLMODE", "disable"),
    )

def rows(sql: str, params: Tuple[Any, ...] = ()):
    with get_conn() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall()

def one(sql: str, params: Tuple[Any, ...] = ()):
    with get_conn() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        r = cur.fetchone()
        return r[0] if r else None

def qident(name: str) -> str:
    return '"' + str(name).replace('"', '""') + '"'

def qtable(schema: str, table: str) -> str:
    return f"{qident(schema)}.{qident(table)}"

def qcol(name: str) -> str:
    return qident(name)

PAV_SCHEMA = os.environ.get("PAV_SCHEMA", "capteurs_fibre")
PAV_LATEST_VIEW = os.environ.get("PAV_LATEST_VIEW", "v_pav_latest")
PAV_SATURATION_VIEW = os.environ.get("PAV_SATURATION_VIEW", "v_pav_saturation_rate")
PAV_DATA_TABLE = os.environ.get("PAV_DATA_TABLE", "capteurs_données")
PAV_POINTS_TABLE = os.environ.get("PAV_POINTS_TABLE", "capteurs_points")

REF_SCHEMA = os.environ.get("REF_SCHEMA", PAV_SCHEMA)
CADASTRE_TABLE = os.environ.get("CADASTRE_TABLE", "cadastre_pdc")
SITADEL_LOG_TABLE = os.environ.get("SITADEL_LOG_TABLE", "sitadel_logements")

T_PAV_LATEST = qtable(PAV_SCHEMA, PAV_LATEST_VIEW)
T_PAV_SAT = qtable(PAV_SCHEMA, PAV_SATURATION_VIEW)
T_PAV_DATA = qtable(PAV_SCHEMA, PAV_DATA_TABLE)
T_PAV_POINTS = qtable(PAV_SCHEMA, PAV_POINTS_TABLE)
T_CADASTRE = qtable(REF_SCHEMA, CADASTRE_TABLE)
T_SIT_LOG = qtable(REF_SCHEMA, SITADEL_LOG_TABLE)

MV_SITADEL_PAV_400M = qtable(PAV_SCHEMA, "mv_sitadel_pav_400m")
MV_CADASTRE_PAV_400M = qtable(PAV_SCHEMA, "mv_cadastre_pav_400m")
MV_OBS_COUVERTURE_COMMUNES = qtable(PAV_SCHEMA, "mv_obs_couverture_pav_communes")
MV_OBS_PROFIL_PAV_400M = qtable(PAV_SCHEMA, "mv_obs_profil_pav_400m")

CAD_COM = os.environ.get("CAD_COM_COL", "commune")
CAD_SECTION = os.environ.get("CAD_SECTION_COL", "section")
CAD_NUM = os.environ.get("CAD_NUM_COL", "numero")
CAD_GEOM = os.environ.get("CAD_GEOM_COL", "geom")

SIT_INSEE = os.environ.get("SIT_INSEE_COL", "insee_com")
SIT_DATE = os.environ.get("SIT_DATE_COL", "date_reelle_autorisation")
SIT_NB_LOG_COL = os.environ.get("SIT_NB_LOG_COL", "nb_lgt_tot_crees")
SIT_TYPE_DAU = os.environ.get("SIT_TYPE_DAU_COL", "type_dau")
SIT_NUM_DAU = os.environ.get("SIT_NUM_DAU_COL", "num_dau")
SIT_SEC1 = os.environ.get("SIT_SEC1_COL", "sec_cadastre1")
SIT_NUM1 = os.environ.get("SIT_NUM1_COL", "num_cadastre1")
SIT_SEC2 = os.environ.get("SIT_SEC2_COL", "sec_cadastre2")
SIT_NUM2 = os.environ.get("SIT_NUM2_COL", "num_cadastre2")
SIT_SEC3 = os.environ.get("SIT_SEC3_COL", "sec_cadastre3")
SIT_NUM3 = os.environ.get("SIT_NUM3_COL", "num_cadastre3")

FILOSOFI_SCHEMA = os.environ.get("FILOSOFI_SCHEMA", PAV_SCHEMA).strip()
FILOSOFI_TABLE = os.environ.get("FILOSOFI_TABLE", "").strip()
T_FILOSOFI = qtable(FILOSOFI_SCHEMA, FILOSOFI_TABLE) if FILOSOFI_TABLE else None
FILOSOFI_IND_COL = os.environ.get("FILOSOFI_IND_COL", "Ind")
FILOSOFI_GEOM_COL = os.environ.get("FILOSOFI_GEOM_COL", "geom")

ACCESS_BUFFER_M = int(os.environ.get("PAV_ACCESS_BUFFER_M", "400"))
BUILDING_BUFFER_M = int(os.environ.get("PAV_BUILDING_BUFFER_M", "400"))
SITADEL_BUFFER_M = int(os.environ.get("PAV_SITADEL_BUFFER_M", "400"))
SATURATION_THRESHOLD = float(os.environ.get("PAV_SATURATION_THRESHOLD", "95"))
SITADEL_YEAR_FROM = int(os.environ.get("SITADEL_YEAR_FROM", "2023"))
SITADEL_YEAR_TO = int(os.environ.get("SITADEL_YEAR_TO", "2026"))

app = FastAPI(title="PAV décisionnel")

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)

BASE_DIR = Path(__file__).resolve().parent
STATIC_DIR = BASE_DIR / "static"
if STATIC_DIR.exists():
    app.mount("/static", StaticFiles(directory=str(STATIC_DIR)), name="static")

@app.get("/")
def root():
    index_path = STATIC_DIR / "index.html"
    if index_path.exists():
        return FileResponse(index_path)
    raise HTTPException(404, detail="index.html introuvable dans ./static")

def pav_where(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    alias: Optional[str] = None,
) -> Tuple[str, List[str]]:
    prefix = f"{alias}." if alias else ""
    where = [f"{prefix}geom_2154 IS NOT NULL"]
    params: List[str] = []
    if code_epci:
        where.append(f"{prefix}epci_code = %s")
        params.append(code_epci)
    if insee:
        where.append(f"{prefix}code_insee = %s")
        params.append(insee)
    if deviceidentifier:
        where.append(f"{prefix}deviceidentifier = %s")
        params.append(deviceidentifier)
    return " AND ".join(where), params

def device_filter(deviceidentifier: Optional[str] = None, deviceIdentifier: Optional[str] = None) -> Optional[str]:
    value = (deviceIdentifier or deviceidentifier or "").strip()
    return value or None

def geom_fixed_2154(expr: str) -> str:
    return (
        f"CASE WHEN ST_SRID({expr})=2154 THEN {expr} "
        f"WHEN ST_SRID({expr})=0 THEN ST_SetSRID({expr},2154) "
        f"ELSE ST_Transform({expr},2154) END"
    )

def safe_indicator(label: str, fn):
    try:
        return fn(), None
    except Exception as exc:
        return None, f"{label}: {type(exc).__name__}: {exc}"

def obs_commune_filter(code_epci: Optional[str] = None, insee: Optional[str] = None) -> Tuple[str, List[str]]:
    where = ["1=1"]
    params: List[str] = []
    if insee:
        where.append("m.code_insee = %s")
        params.append(insee)
    if code_epci:
        where.append(f"""
          m.code_insee IN (
            SELECT DISTINCT code_insee
            FROM {T_PAV_LATEST}
            WHERE epci_code = %s
          )
        """)
        params.append(code_epci)
    return " AND ".join(where), params

@app.get("/api/health")
def health():
    return {
        "ok": True,
        "pav_latest_view": T_PAV_LATEST,
        "pav_saturation_view": T_PAV_SAT,
        "ref_schema": REF_SCHEMA,
        "cadastre_table": T_CADASTRE,
        "sitadel_log_table": T_SIT_LOG,
        "mv_sitadel_pav_400m": MV_SITADEL_PAV_400M,
        "mv_cadastre_pav_400m": MV_CADASTRE_PAV_400M,
        "mv_obs_couverture_communes": MV_OBS_COUVERTURE_COMMUNES,
        "mv_obs_profil_pav_400m": MV_OBS_PROFIL_PAV_400M,
        "filosofi_configured": bool(T_FILOSOFI),
        "filosofi_table": T_FILOSOFI,
        "filosofi_ind_col": FILOSOFI_IND_COL,
        "filosofi_geom_col": FILOSOFI_GEOM_COL,
        "buffers": {
            "access_buffer_m": ACCESS_BUFFER_M,
            "building_buffer_m": BUILDING_BUFFER_M,
            "sitadel_buffer_m": SITADEL_BUFFER_M,
        },
    }

@app.get("/api/debug/columns")
def debug_columns(schema: str = Query(...), table: str = Query(...)):
    sql = """
      SELECT column_name, data_type
      FROM information_schema.columns
      WHERE table_schema = %s AND table_name = %s
      ORDER BY ordinal_position
    """
    return [{"column": c, "type": t} for c, t in rows(sql, (schema, table))]

@app.get("/api/tags")
def list_tags(limit: int = Query(100, ge=1, le=500)):
    sql = f"""
      SELECT tagreference::text, count(*)::bigint
      FROM {T_PAV_DATA}
      GROUP BY tagreference
      ORDER BY tagreference
      LIMIT %s
    """
    return [{"tagreference": t, "n": int(n)} for t, n in rows(sql, (limit,))]

@app.get("/api/epci")
def list_epci():
    sql = f"""
      SELECT epci_code, COALESCE(max(epci_nom), max(epci_code)) AS epci_nom
      FROM {T_PAV_LATEST}
      WHERE epci_code IS NOT NULL
      GROUP BY epci_code
      ORDER BY COALESCE(max(epci_nom), max(epci_code))
    """
    return [{"code_epci": c, "nom_epci": n} for c, n in rows(sql)]

@app.get("/api/communes")
def list_communes(code_epci: Optional[str] = Query(default=None)):
    if code_epci:
        sql = f"""
          SELECT code_insee, COALESCE(max(nom_maj), max(nom_offici), max(code_insee)) AS nom
          FROM {T_PAV_LATEST}
          WHERE code_insee IS NOT NULL AND epci_code = %s
          GROUP BY code_insee
          ORDER BY COALESCE(max(nom_maj), max(nom_offici), max(code_insee))
        """
        data = rows(sql, (code_epci,))
    else:
        sql = f"""
          SELECT code_insee, COALESCE(max(nom_maj), max(nom_offici), max(code_insee)) AS nom
          FROM {T_PAV_LATEST}
          WHERE code_insee IS NOT NULL
          GROUP BY code_insee
          ORDER BY COALESCE(max(nom_maj), max(nom_offici), max(code_insee))
        """
        data = rows(sql)
    return [{"insee_com": i, "nom": n} for i, n in data]

@app.get("/api/pav/devices")
def list_devices(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    q: Optional[str] = None,
    limit: int = Query(500, ge=1, le=2000),
):
    where, params = pav_where(code_epci, insee)
    if q:
        where += " AND deviceidentifier ILIKE %s"
        params.append(f"%{q.strip()}%")
    sql = f"""
      SELECT
        deviceidentifier,
        COALESCE(max(address), '') AS address,
        COALESCE(max(nom_maj), max(nom_offici), max(code_insee), '') AS commune
      FROM {T_PAV_LATEST}
      WHERE {where}
      GROUP BY deviceidentifier
      ORDER BY deviceidentifier
      LIMIT %s
    """
    return [{"deviceidentifier": d, "address": a, "commune": c} for d, a, c in rows(sql, tuple(params + [limit]))]

@app.get("/api/bbox")
def bbox(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    deviceIdentifier: Optional[str] = None,
):
    device = device_filter(deviceidentifier, deviceIdentifier)
    where, params = pav_where(code_epci, insee, device)
    sql = f"""
      SELECT ST_XMin(b), ST_YMin(b), ST_XMax(b), ST_YMax(b)
      FROM (
        SELECT ST_Envelope(ST_Transform(ST_Collect(geom_2154),4326)) AS b
        FROM {T_PAV_LATEST}
        WHERE {where}
      ) x
    """
    r = rows(sql, tuple(params))
    if not r or r[0][0] is None:
        raise HTTPException(404, detail="Aucun PAV pour ce filtre")
    return {"bbox": [float(v) for v in r[0]]}

@app.get("/api/pav/points")
def pav_points(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    deviceIdentifier: Optional[str] = None,
):
    device = device_filter(deviceidentifier, deviceIdentifier)
    where, params = pav_where(code_epci, insee, device, alias="p")
    sql = f"""
      SELECT jsonb_build_object(
        'type','FeatureCollection',
        'features', COALESCE(jsonb_agg(
          jsonb_build_object(
            'type','Feature',
            'geometry', ST_AsGeoJSON(ST_Transform(p.geom_2154,4326))::jsonb,
            'properties', jsonb_build_object(
              'deviceidentifier', p.deviceidentifier,
              'code_insee', p.code_insee,
              'commune', COALESCE(p.nom_maj, p.nom_offici),
              'epci_code', p.epci_code,
              'epci_nom', p.epci_nom,
              'address', p.address,
              'percent_of_bin_used', p.percent_of_bin_used,
              'volume_bin', p.volume_bin,
              'volume_libre', p.volume_libre,
              'temperature', p.temperature,
              'last_date_raw', p.last_date_raw,
              'last_timestamp', p.last_timestamp,
              'jours_observes', s.jours_observes,
              'jours_satures', s.jours_satures,
              'taux_jours_saturation', s.taux_jours_saturation
            )
          )
        ), '[]'::jsonb)
      )
      FROM {T_PAV_LATEST} p
      LEFT JOIN {T_PAV_SAT} s ON s.deviceidentifier = p.deviceidentifier
      WHERE {where}
    """
    return one(sql, tuple(params))

def population_400_direct(where: str, params: List[str]) -> Optional[float]:
    if not T_FILOSOFI:
        return None
    ind_col = qident(FILOSOFI_IND_COL)
    geom_col = qident(FILOSOFI_GEOM_COL)
    geom_expr = geom_fixed_2154(f"f.{geom_col}")
    sql = f"""
      WITH pav AS (
        SELECT ST_UnaryUnion(ST_Collect(ST_Buffer(geom_2154, %s))) AS geom
        FROM {T_PAV_LATEST}
        WHERE {where}
      ), inter AS (
        SELECT
          CASE
            WHEN ST_Area({geom_expr}) > 0
            THEN (replace(NULLIF(f.{ind_col}::text,''), ',', '.')::numeric)
              * ST_Area(ST_Intersection({geom_expr}, pav.geom))
              / ST_Area({geom_expr})
            ELSE 0
          END AS pop_part
        FROM {T_FILOSOFI} f
        CROSS JOIN pav
        WHERE pav.geom IS NOT NULL
          AND ST_Intersects({geom_expr}, pav.geom)
      )
      SELECT COALESCE(sum(pop_part),0)::numeric FROM inter
    """
    return float(one(sql, tuple([ACCESS_BUFFER_M] + params)) or 0)

def population_400_from_mv(code_epci: Optional[str], insee: Optional[str]) -> Optional[float]:
    where, params = obs_commune_filter(code_epci=code_epci, insee=insee)
    sql = f"""
      SELECT COALESCE(SUM(m.population_couverte_400m), 0)::numeric
      FROM {MV_OBS_COUVERTURE_COMMUNES} m
      WHERE {where}
    """
    return float(one(sql, tuple(params)) or 0)

def population_400(code_epci: Optional[str], insee: Optional[str], device: Optional[str], where: str, params: List[str]) -> Optional[float]:
    if device:
        return population_400_direct(where, params)
    return population_400_from_mv(code_epci, insee)

def parcelles_cadastre_400(where_p: str, params_p: List[str]) -> Optional[int]:
    sql = f"""
      SELECT COUNT(DISTINCT m.parcelle_id)::bigint
      FROM {MV_CADASTRE_PAV_400M} m
      JOIN {T_PAV_LATEST} p
        ON p.deviceidentifier = m.deviceidentifier
      WHERE {where_p}
    """
    return int(one(sql, tuple(params_p)) or 0)

def logements_sitadel_400(where_p: str, params_p: List[str], year_from: int, year_to: int) -> Optional[int]:
    sql = f"""
      WITH dossiers AS (
        SELECT
          m.dossier_id,
          MAX(m.nb_logements) AS nb_logements
        FROM {MV_SITADEL_PAV_400M} m
        JOIN {T_PAV_LATEST} p
          ON p.deviceidentifier = m.deviceidentifier
        WHERE {where_p}
          AND m.annee BETWEEN %s AND %s
        GROUP BY m.dossier_id
      )
      SELECT COALESCE(SUM(nb_logements), 0)::bigint
      FROM dossiers
    """
    return int(one(sql, tuple(params_p + [year_from, year_to])) or 0)

@app.get("/api/pav/stats")
def pav_stats(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    deviceIdentifier: Optional[str] = None,
    year_from: int = Query(default=SITADEL_YEAR_FROM, ge=2000, le=2100),
    year_to: int = Query(default=SITADEL_YEAR_TO, ge=2000, le=2100),
):
    device = device_filter(deviceidentifier, deviceIdentifier)
    where, params = pav_where(code_epci, insee, device)

    sql_base = f"""
      SELECT
        count(DISTINCT deviceidentifier)::bigint AS nb_pav,
        COALESCE(sum(volume_bin),0)::numeric AS volume_total,
        COALESCE(sum(volume_libre),0)::numeric AS volume_libre_total,
        COALESCE(avg(percent_of_bin_used),0)::numeric AS taux_remplissage_moyen,
        CASE WHEN COALESCE(sum(volume_bin),0) > 0
          THEN 100.0 * sum(volume_bin * percent_of_bin_used / 100.0) / sum(volume_bin)
          ELSE 0 END AS taux_remplissage_pondere
      FROM {T_PAV_LATEST}
      WHERE {where}
    """
    nb_pav, volume_total, volume_libre_total, fill_avg, fill_weighted = rows(sql_base, tuple(params))[0]

    where_p, params_p = pav_where(code_epci, insee, device, alias="p")

    sql_sat = f"""
      SELECT COALESCE(avg(s.taux_jours_saturation),0)::numeric
      FROM {T_PAV_LATEST} p
      JOIN {T_PAV_SAT} s ON s.deviceidentifier = p.deviceidentifier
      WHERE {where_p}
    """
    taux_sat_moyen = float(one(sql_sat, tuple(params_p)) or 0)

    sql_communes = f"""
      SELECT
        code_insee,
        COALESCE(max(nom_maj), max(nom_offici), max(code_insee)) AS commune,
        count(DISTINCT deviceidentifier)::bigint AS nb_pav,
        COALESCE(sum(volume_bin),0)::numeric AS volume_total,
        COALESCE(sum(volume_libre),0)::numeric AS volume_libre_total,
        COALESCE(avg(percent_of_bin_used),0)::numeric AS taux_remplissage_moyen
      FROM {T_PAV_LATEST}
      WHERE {where}
      GROUP BY code_insee
      ORDER BY commune
    """
    volume_communes = [
        {
            "code_insee": r[0],
            "commune": r[1],
            "nb_pav": int(r[2] or 0),
            "volume_total": float(r[3] or 0),
            "volume_libre_total": float(r[4] or 0),
            "taux_remplissage_moyen": float(r[5] or 0),
        }
        for r in rows(sql_communes, tuple(params))
    ]

    warnings: List[str] = []
    pop400, err = safe_indicator("population_400m", lambda: population_400(code_epci, insee, device, where, params))
    if err:
        warnings.append(err)

    cad400, err = safe_indicator("parcelles_cadastre_400m", lambda: parcelles_cadastre_400(where_p, params_p))
    if err:
        warnings.append(err)

    sit400, err = safe_indicator("logements_sitadel_400m", lambda: logements_sitadel_400(where_p, params_p, year_from, year_to))
    if err:
        warnings.append(err)

    volume_par_hab_400 = None
    if pop400 and pop400 > 0:
        volume_par_hab_400 = (float(volume_total or 0) / pop400) * 1000

    return {
        "filters": {
            "code_epci": code_epci,
            "insee": insee,
            "deviceidentifier": device,
            "year_from": year_from,
            "year_to": year_to,
        },
        "parameters": {
            "access_buffer_m": ACCESS_BUFFER_M,
            "building_buffer_m": BUILDING_BUFFER_M,
            "sitadel_buffer_m": SITADEL_BUFFER_M,
            "saturation_threshold": SATURATION_THRESHOLD,
        },
        "units": {
            "population_400m": "hab.",
            "volume_total": "m³",
            "volume_libre_total": "m³",
            "volume_par_habitant_400m": "L/hab.",
            "taux_remplissage_moyen": "%",
            "taux_remplissage_pondere": "%",
            "taux_jours_saturation_moyen": "%",
            "parcelles_cadastre_400m": "parcelles",
            "logements_sitadel_400m": "logements autorisés",
            "nb_pav": "PAV",
        },
        "kpi": {
            "nb_pav": int(nb_pav or 0),
            "population_400m": pop400,
            "volume_total": float(volume_total or 0),
            "volume_libre_total": float(volume_libre_total or 0),
            "volume_par_habitant_400m": volume_par_hab_400,
            "taux_remplissage_moyen": float(fill_avg or 0),
            "taux_remplissage_pondere": float(fill_weighted or 0),
            "taux_jours_saturation_moyen": taux_sat_moyen,
            "parcelles_cadastre_400m": cad400,
            "batiments_50m": cad400,
            "logements_sitadel_300m": sit400,
            "logements_sitadel_400m": sit400,
        },
        "volume_communes": volume_communes,
        "warnings": warnings,
    }

@app.get("/api/observatoire/couverture-communes")
def observatoire_couverture_communes(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    deviceIdentifier: Optional[str] = None,
):
    device = device_filter(deviceidentifier, deviceIdentifier)

    where = ["1=1"]
    params: List[str] = []

    if code_epci:
        where.append("m.epci_code = %s")
        params.append(code_epci)

    if insee:
        where.append("m.code_insee = %s")
        params.append(insee)

    # La vue matérialisée est communale. Si un PAV est sélectionné,
    # on affiche les indicateurs de sa commune, tout en gardant les KPI PAV
    # de la carte calculés séparément dans /api/pav/stats.
    if device:
        where.append(f"""
          m.code_insee IN (
            SELECT code_insee
            FROM {T_PAV_LATEST}
            WHERE deviceidentifier = %s
          )
        """)
        params.append(device)

    sql = f"""
      SELECT
        m.code_insee,
        m.commune,
        m.nb_pav,
        m.population_totale,
        m.population_couverte_400m,
        m.population_non_couverte_400m,
        m.taux_couverture_population_400m,
        m.capacite_l_hab_couvert,
        m.menages_collectifs_couverts_400m,
        m.logements_sociaux_couverts_400m,
        m.menages_pauvres_couverts_400m,
        m.population_0_17_couverte_400m,
        m.population_65p_couverte_400m,
        m.volume_total_m3,
        m.volume_libre_m3,
        m.taux_remplissage_moyen
      FROM {MV_OBS_COUVERTURE_COMMUNES} m
      WHERE {" AND ".join(where)}
      ORDER BY m.commune
    """
    data = rows(sql, tuple(params))
    return [
        {
            "code_insee": r[0],
            "commune": r[1],
            "nb_pav": int(r[2] or 0),
            "population_totale": float(r[3] or 0),
            "population_couverte_400m": float(r[4] or 0),
            "population_non_couverte_400m": float(r[5] or 0),
            "taux_couverture_population_400m": float(r[6] or 0),
            "capacite_l_hab_couvert": float(r[7] or 0),
            "menages_collectifs_couverts_400m": float(r[8] or 0),
            "logements_sociaux_couverts_400m": float(r[9] or 0),
            "menages_pauvres_couverts_400m": float(r[10] or 0),
            "population_0_17_couverte_400m": float(r[11] or 0),
            "population_65p_couverte_400m": float(r[12] or 0),
            "volume_total_m3": float(r[13] or 0),
            "volume_libre_m3": float(r[14] or 0),
            "taux_remplissage_moyen": float(r[15] or 0),
        }
        for r in data
    ]

@app.get("/api/observatoire/indicateurs")
def observatoire_indicateurs(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    deviceIdentifier: Optional[str] = None,
):
    device = device_filter(deviceidentifier, deviceIdentifier)

    communes = observatoire_couverture_communes(
        code_epci=code_epci,
        insee=insee,
        deviceidentifier=device,
    )

    population_totale = sum(x["population_totale"] for x in communes)
    population_couverte = sum(x["population_couverte_400m"] for x in communes)
    population_non_couverte = sum(x["population_non_couverte_400m"] for x in communes)
    nb_pav = sum(x["nb_pav"] for x in communes)
    volume_total = sum(x["volume_total_m3"] for x in communes)
    volume_libre = sum(x["volume_libre_m3"] for x in communes)
    volume_occupe = max(volume_total - volume_libre, 0)

    menages_collectifs = sum(x["menages_collectifs_couverts_400m"] for x in communes)
    logements_sociaux = sum(x["logements_sociaux_couverts_400m"] for x in communes)
    menages_pauvres = sum(x["menages_pauvres_couverts_400m"] for x in communes)
    pop_0_17 = sum(x["population_0_17_couverte_400m"] for x in communes)
    pop_65p = sum(x["population_65p_couverte_400m"] for x in communes)
    pop_18_64 = max(population_couverte - pop_0_17 - pop_65p, 0)

    taux_couverture = population_couverte / population_totale * 100 if population_totale > 0 else 0
    taux_non_couverture = 100 - taux_couverture if population_totale > 0 else 0
    capacite_l_hab = volume_total / population_couverte * 1000 if population_couverte > 0 else 0
    taux_remplissage = (
        sum(x["taux_remplissage_moyen"] * x["nb_pav"] for x in communes) / nb_pav
        if nb_pav > 0 else 0
    )

    # Ces deux indicateurs sont bien filtrés sur EPCI / commune / PAV,
    # car les vues matérialisées conservent le deviceidentifier.
    where_p, params_p = pav_where(code_epci=code_epci, insee=insee, deviceidentifier=device, alias="p")

    logements_sitadel = 0.0
    try:
        sql_sit = f"""
          SELECT COALESCE(SUM(nb_logements), 0)::numeric
          FROM (
            SELECT m.dossier_id, MAX(m.nb_logements) AS nb_logements
            FROM {MV_SITADEL_PAV_400M} m
            JOIN {T_PAV_LATEST} p
              ON p.deviceidentifier = m.deviceidentifier
            WHERE {where_p}
            GROUP BY m.dossier_id
          ) x
        """
        logements_sitadel = float(one(sql_sit, tuple(params_p)) or 0)
    except Exception:
        logements_sitadel = 0.0

    parcelles_cadastre = 0
    try:
        sql_cad = f"""
          SELECT COUNT(DISTINCT m.parcelle_id)::bigint
          FROM {MV_CADASTRE_PAV_400M} m
          JOIN {T_PAV_LATEST} p
            ON p.deviceidentifier = m.deviceidentifier
          WHERE {where_p}
        """
        parcelles_cadastre = int(one(sql_cad, tuple(params_p)) or 0)
    except Exception:
        parcelles_cadastre = 0

    # Hypothèse simple pour le PoC : 2,2 personnes par logement autorisé.
    population_future_estimee = logements_sitadel * 2.2
    capacite_future_l_hab = (
        volume_total / (population_couverte + population_future_estimee) * 1000
        if (population_couverte + population_future_estimee) > 0 else 0
    )
    perte_capacite_l_hab = max(capacite_l_hab - capacite_future_l_hab, 0)

    part_collectif = menages_collectifs / population_couverte * 100 if population_couverte > 0 else 0
    part_logements_sociaux = logements_sociaux / population_couverte * 100 if population_couverte > 0 else 0
    part_menages_pauvres = menages_pauvres / population_couverte * 100 if population_couverte > 0 else 0
    part_0_17 = pop_0_17 / population_couverte * 100 if population_couverte > 0 else 0
    part_65p = pop_65p / population_couverte * 100 if population_couverte > 0 else 0

    score_non_couverture = max(0, min(100, taux_non_couverture))
    score_saturation = max(0, min(100, taux_remplissage))
    score_capacite = max(0, min(100, (150 - capacite_l_hab) / 150 * 100))
    score_logements = max(0, min(100, logements_sitadel / max(nb_pav, 1) * 10))
    score_collectif = max(0, min(100, part_collectif))

    indice_besoin = round(
        0.30 * score_non_couverture
        + 0.25 * score_saturation
        + 0.20 * score_capacite
        + 0.15 * score_logements
        + 0.10 * score_collectif,
        1
    )

    labels_communes = [x["commune"] for x in communes]

    kpi = {
        "nb_pav": nb_pav,
        "population_totale": population_totale,
        "population_couverte_400m": population_couverte,
        "population_non_couverte_400m": population_non_couverte,
        "taux_couverture_population_400m": taux_couverture,
        "taux_non_couverture_population_400m": taux_non_couverture,
        "volume_total_m3": volume_total,
        "volume_libre_m3": volume_libre,
        "volume_occupe_m3": volume_occupe,
        "capacite_l_hab_couvert": capacite_l_hab,
        "taux_remplissage_moyen": taux_remplissage,
        "menages_collectifs_couverts_400m": menages_collectifs,
        "logements_sociaux_couverts_400m": logements_sociaux,
        "menages_pauvres_couverts_400m": menages_pauvres,
        "population_0_17_couverte_400m": pop_0_17,
        "population_65p_couverte_400m": pop_65p,
        "population_18_64_couverte_400m": pop_18_64,
        "logements_sitadel_400m": logements_sitadel,
        "parcelles_cadastre_400m": parcelles_cadastre,
        "population_future_estimee": population_future_estimee,
        "capacite_future_l_hab": capacite_future_l_hab,
        "perte_capacite_l_hab": perte_capacite_l_hab,
        "indice_besoin_prospectif": indice_besoin,
        "part_menages_collectifs_couverts": part_collectif,
        "part_logements_sociaux_couverts": part_logements_sociaux,
        "part_menages_pauvres_couverts": part_menages_pauvres,
        "part_population_0_17_couverte": part_0_17,
        "part_population_65p_couverte": part_65p,

        # Alias conservés pour l'interface.
        "population_totale_territoire": population_totale,
        "taux_couverture_population": taux_couverture,
        "capacite_actuelle_l_hab": capacite_l_hab,
        "menages_collectifs_400m": menages_collectifs,
        "logements_sociaux_400m": logements_sociaux,
        "menages_pauvres_400m": menages_pauvres,
        "population_0_17_400m": pop_0_17,
        "population_65p_400m": pop_65p,
        "logements_autorises_sitadel_400m": logements_sitadel,
    }

    charts = {
        "couverture_communes": {
            "labels": labels_communes,
            "covered": [x["population_couverte_400m"] for x in communes],
            "uncovered": [x["population_non_couverte_400m"] for x in communes],
            "capacity": [x["capacite_l_hab_couvert"] for x in communes],
            "coverage": [x["taux_couverture_population_400m"] for x in communes],
        },
        "couverture": {
            "labels": ["Population couverte", "Population non couverte"],
            "values": [population_couverte, population_non_couverte],
        },
        "volume": {
            "labels": ["Volume occupé", "Volume libre"],
            "values": [volume_occupe, volume_libre],
        },
        "capacite": {
            "labels": ["Actuelle", "Projetée"],
            "values": [capacite_l_hab, capacite_future_l_hab],
        },
        "profil_menages": {
            "labels": ["Ménages collectifs", "Logements sociaux", "Ménages pauvres"],
            "values": [menages_collectifs, logements_sociaux, menages_pauvres],
        },
        "ages": {
            "labels": ["0-17 ans", "18-64 ans", "65 ans et +"],
            "values": [pop_0_17, pop_18_64, pop_65p],
        },
        "radar": {
            "labels": ["Non-couverture", "Saturation", "Faible capacité", "Logements futurs", "Collectif"],
            "values": [score_non_couverture, score_saturation, score_capacite, score_logements, score_collectif],
        },
        "prospective": {
            "capacite_actuelle_l_hab": capacite_l_hab,
            "capacite_future_l_hab": capacite_future_l_hab,
            "logements_sitadel_400m": logements_sitadel,
            "population_future_estimee": population_future_estimee,
            "indice_besoin_prospectif": indice_besoin,
        },
        "profil_residentiel": {
            "menages_collectifs": menages_collectifs,
            "logements_sociaux": logements_sociaux,
            "menages_pauvres": menages_pauvres,
        },
        "age": {
            "population_0_17": pop_0_17,
            "population_18_64": pop_18_64,
            "population_65p": pop_65p,
        },
    }

    return {
        "filters": {
            "code_epci": code_epci,
            "insee": insee,
            "deviceidentifier": device,
        },
        "kpi": kpi,
        "communes": communes,
        "coverage_communes": communes,
        "charts": charts,
        "warnings": [],
    }


@app.get("/api/observatoire/profil-pression")
def observatoire_profil_pression(
    code_epci: Optional[str] = None,
    insee: Optional[str] = None,
    deviceidentifier: Optional[str] = None,
    deviceIdentifier: Optional[str] = None,
):
    """
    Analyse par PAV : remplissage moyen historique + profil social/habitat Filosofi à 400 m
    + logements autorisés SIT@DEL à 400 m. Utilise mv_obs_profil_pav_400m.
    """
    device = device_filter(deviceidentifier, deviceIdentifier)

    where = ["1=1"]
    params: List[str] = []

    if code_epci:
        where.append("m.epci_code = %s")
        params.append(code_epci)

    if insee:
        where.append("m.code_insee = %s")
        params.append(insee)

    if device:
        where.append("m.deviceidentifier = %s")
        params.append(device)

    sql = f"""
      SELECT
        m.deviceidentifier,
        m.code_insee,
        m.commune,
        m.address,
        m.volume_total_m3,
        m.volume_libre_m3,
        m.remplissage_instantane,
        m.remplissage_moyen_observe,
        m.remplissage_median,
        m.remplissage_max,
        m.ecart_type_remplissage,
        m.jours_observes,
        m.jours_tension_80,
        m.jours_saturation_95,
        m.taux_jours_tension_80,
        m.taux_jours_saturation_95,
        m.typologie_usage,
        m.population_400m,
        m.menages_400m,
        m.menages_collectifs_400m,
        m.menages_maisons_400m,
        m.logements_sociaux_400m,
        m.menages_pauvres_400m,
        m.population_0_17_400m,
        m.population_65p_400m,
        m.nb_dossiers_sitadel_400m,
        m.logements_sitadel_400m,
        m.capacite_l_hab,
        m.population_future_estimee,
        m.capacite_future_l_hab,
        m.part_menages_collectifs,
        m.part_menages_maisons,
        m.part_logements_sociaux,
        m.part_menages_pauvres,
        m.part_population_0_17,
        m.part_population_65p,
        m.est_pav_sous_pression,
        m.typologie_habitat,
        m.profil_social,
        m.score_besoin_adaptation
      FROM {MV_OBS_PROFIL_PAV_400M} m
      WHERE {" AND ".join(where)}
      ORDER BY m.score_besoin_adaptation DESC NULLS LAST,
               m.remplissage_moyen_observe DESC NULLS LAST,
               m.deviceidentifier
    """

    data = rows(sql, tuple(params))
    pav = []
    for r in data:
        typologie_usage = r[16] or "Non qualifié"
        typologie_habitat = r[37] or "Non qualifié"
        profil_social = r[38] or "Non qualifié"
        pav.append({
            "deviceidentifier": r[0],
            "code_insee": r[1],
            "commune": r[2],
            "address": r[3],
            "volume_total_m3": float(r[4] or 0),
            "volume_bin": float(r[4] or 0),
            "volume_libre_m3": float(r[5] or 0),
            "volume_libre": float(r[5] or 0),
            "remplissage_instantane": float(r[6] or 0),
            "remplissage_moyen_observe": float(r[7] or 0),
            "remplissage_moyen": float(r[7] or 0),
            "remplissage_median": float(r[8] or 0),
            "remplissage_max": float(r[9] or 0),
            "ecart_type_remplissage": float(r[10] or 0),
            "jours_observes": int(r[11] or 0),
            "jours_tension_80": int(r[12] or 0),
            "jours_saturation_95": int(r[13] or 0),
            "taux_jours_tension_80": float(r[14] or 0),
            "taux_jours_saturation_95": float(r[15] or 0),
            "typologie_usage": typologie_usage,
            "population_400m": float(r[17] or 0),
            "menages_400m": float(r[18] or 0),
            "menages_collectifs_400m": float(r[19] or 0),
            "menages_maisons_400m": float(r[20] or 0),
            "logements_sociaux_400m": float(r[21] or 0),
            "menages_pauvres_400m": float(r[22] or 0),
            "population_0_17_400m": float(r[23] or 0),
            "population_65p_400m": float(r[24] or 0),
            "nb_dossiers_sitadel_400m": int(r[25] or 0),
            "logements_sitadel_400m": float(r[26] or 0),
            "capacite_l_hab": float(r[27] or 0),
            "population_future_estimee": float(r[28] or 0),
            "capacite_future_l_hab": float(r[29] or 0),
            "part_menages_collectifs": float(r[30] or 0),
            "part_menages_maisons": float(r[31] or 0),
            "part_logements_sociaux": float(r[32] or 0),
            "part_menages_pauvres": float(r[33] or 0),
            "part_population_0_17": float(r[34] or 0),
            "part_population_65p": float(r[35] or 0),
            "est_pav_sous_pression": bool(r[36]),
            "typologie_habitat": typologie_habitat,
            "profil_social": profil_social,
            "typologie_pav": f"{typologie_usage} / {typologie_habitat}",
            "score_besoin_adaptation": float(r[39] or 0),
        })

    n = len(pav)
    if n == 0:
        return {
            "filters": {"code_epci": code_epci, "insee": insee, "deviceidentifier": device},
            "kpi": {},
            "typologies": [],
            "pav_prioritaires": [],
            "charts": {},
            "pav": [],
            "warnings": [],
        }

    pav_sous_pression = [p for p in pav if p["est_pav_sous_pression"]]
    base_profil = pav_sous_pression if pav_sous_pression else pav

    def avg(items, key):
        return sum(float(x.get(key) or 0) for x in items) / len(items) if items else 0

    def count_by(items, key):
        out: Dict[str, int] = {}
        for x in items:
            label = str(x.get(key) or "Non qualifié")
            out[label] = out.get(label, 0) + 1
        return out

    typ_usage = count_by(pav, "typologie_usage")
    typ_habitat_pressure = count_by(base_profil, "typologie_habitat")
    prof_social_pressure = count_by(base_profil, "profil_social")

    nb_pav_sous_pression = len(pav_sous_pression)
    nb_pav_tension_future = sum(1 for p in pav if p["logements_sitadel_400m"] > 0 and p["capacite_future_l_hab"] < p["capacite_l_hab"])

    pression_profile = [
        avg(base_profil, "part_menages_collectifs"),
        avg(base_profil, "part_menages_maisons"),
        avg(base_profil, "part_logements_sociaux"),
        avg(base_profil, "part_menages_pauvres"),
        avg(base_profil, "part_population_0_17"),
        avg(base_profil, "part_population_65p"),
    ]
    ensemble_profile = [
        avg(pav, "part_menages_collectifs"),
        avg(pav, "part_menages_maisons"),
        avg(pav, "part_logements_sociaux"),
        avg(pav, "part_menages_pauvres"),
        avg(pav, "part_population_0_17"),
        avg(pav, "part_population_65p"),
    ]

    pav_prioritaires = sorted(
        pav,
        key=lambda x: (x["score_besoin_adaptation"], x["remplissage_moyen_observe"]),
        reverse=True,
    )[:12]

    kpi = {
        "nb_pav": n,
        "remplissage_moyen_observe": avg(pav, "remplissage_moyen_observe"),
        "taux_jours_tension_80_moyen": avg(pav, "taux_jours_tension_80"),
        "taux_jours_saturation_95_moyen": avg(pav, "taux_jours_saturation_95"),
        "nb_pav_sous_pression": nb_pav_sous_pression,
        "nb_pav_tension_future": nb_pav_tension_future,
        "score_besoin_moyen": avg(pav, "score_besoin_adaptation"),
        "population_moyenne_400m": avg(pav, "population_400m"),
        "capacite_moyenne_l_hab": avg(pav, "capacite_l_hab"),
        "part_menages_collectifs_pression": pression_profile[0],
        "part_menages_maisons_pression": pression_profile[1],
        "part_logements_sociaux_pression": pression_profile[2],
        "part_menages_pauvres_pression": pression_profile[3],
        "part_population_0_17_pression": pression_profile[4],
        "part_population_65p_pression": pression_profile[5],
    }

    charts = {
        "typologies": {
            "labels": list(typ_usage.keys()),
            "values": list(typ_usage.values()),
        },
        "typologie_habitat_pression": {
            "labels": list(typ_habitat_pressure.keys()),
            "values": list(typ_habitat_pressure.values()),
        },
        "profil_social_pression": {
            "labels": list(prof_social_pressure.keys()),
            "values": list(prof_social_pressure.values()),
        },
        "scatter_population_remplissage": [
            {
                "x": p["population_400m"],
                "y": p["remplissage_moyen_observe"],
                "r": max(4, min(18, (p["volume_total_m3"] or 1) * 1.8)),
                "deviceidentifier": p["deviceidentifier"],
                "commune": p["commune"],
                "typologie_habitat": p["typologie_habitat"],
                "profil_social": p["profil_social"],
            }
            for p in pav
        ],
        "profil_pression_vs_ensemble": {
            "labels": ["Collectif", "Maison", "Log. sociaux", "Mén. pauvres", "0-17 ans", "65 ans +"],
            "pression": pression_profile,
            "ensemble": ensemble_profile,
        },
        "pression_by_habitat": {
            "labels": list(count_by(pav, "typologie_habitat").keys()),
            "values": [
                avg([p for p in pav if p["typologie_habitat"] == label], "remplissage_moyen_observe")
                for label in count_by(pav, "typologie_habitat").keys()
            ],
        },
    }

    return {
        "filters": {"code_epci": code_epci, "insee": insee, "deviceidentifier": device},
        "kpi": kpi,
        "typologies": [{"label": k, "value": v} for k, v in typ_usage.items()],
        "pav_prioritaires": pav_prioritaires,
        "charts": charts,
        "pav": pav,
        "warnings": [],
    }