import os
from pathlib import Path
from typing import Optional

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

load_dotenv(override=True)

# -----------------------------
# Connexion (mode simple, inchangé)
# -----------------------------
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=()):
    with get_conn() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall()

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

# -----------------------------
# Tables / colonnes
# -----------------------------
SCHEMA = "compteur_foncier"

T_COMMUNES = f"{SCHEMA}.commune_aula_admin_express_2025"
COL_INSEE = "insee_com"
COL_NOM = "nom_m"
COL_GEOM = "geom"

T_CADASTRE = f"{SCHEMA}.cadastre"
CAD_COM = "commune"
CAD_SECTION = "section"
CAD_NUM = "numero"
CAD_CONT = "contenance"
CAD_GEOM = "geom"

T_FF = f"{SCHEMA}.ff_2023"
FF_COM = "idcom"
FF_SECTION = "ccosec"
FF_NUM = "dnupla"
FF_NBAT = "nbat"          # varchar
FF_YEAR = "jannatmin"     # varchar/int selon import
FF_TLOCDOM = "tlocdomin"

SIT_LOG = f"{SCHEMA}.sitadel_aula_dido_newlog_13_25"
SIT_LOC = f"{SCHEMA}.sitadel_aula_dido_newlocaux_13_25"

# ✅ on passe sur DOC (ouverture chantier)
SIT_DATE_DOC = "date_reelle_doc"
SIT_DATE_AUT = "date_reelle_autorisation"

SIT_INSEE = "insee_com"
SIT_CODE_EPCI = "code_epci"
SIT_NOM_EPCI = "nom_epci"
SIT_NATURE = "nature_projet_declaree"
SIT_DEST = "destination_principale"
SIT_ZONE = "zone_op"
SIT_SUP = "superficie_terrain"
SIT_SEC1, SIT_NUM1 = "sec_cadastre1", "num_cadastre1"
SIT_SEC2, SIT_NUM2 = "sec_cadastre2", "num_cadastre2"
SIT_SEC3, SIT_NUM3 = "sec_cadastre3", "num_cadastre3"

FF_MAX_YEAR = int(os.environ.get("FF_MAX_YEAR", "2023"))
SITADEL_MIN_YEAR = 2023

# -----------------------------
# App
# -----------------------------
app = FastAPI()
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)

# Static
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 le dossier ./static")

# -----------------------------
# Helpers SQL
# -----------------------------
EPCI_CTE = f"""
WITH epci_map AS (
  SELECT DISTINCT
    {SIT_INSEE}::text AS insee_com,
    {SIT_CODE_EPCI}::text AS code_epci,
    {SIT_NOM_EPCI}::text AS nom_epci
  FROM (
    SELECT {SIT_INSEE}, {SIT_CODE_EPCI}, {SIT_NOM_EPCI} FROM {SIT_LOG}
    UNION ALL
    SELECT {SIT_INSEE}, {SIT_CODE_EPCI}, {SIT_NOM_EPCI} FROM {SIT_LOC}
  ) s
  WHERE {SIT_INSEE} IS NOT NULL AND {SIT_CODE_EPCI} IS NOT NULL
)
"""

def geom_fixed(expr: str) -> str:
    # si SRID=0 on force 2154 (comme vos imports), sinon on garde
    return f"CASE WHEN ST_SRID({expr})=0 THEN ST_SetSRID({expr},2154) ELSE {expr} END"

# -----------------------------
# API EPCI
# -----------------------------
@app.get("/api/epci")
def list_epci():
    sql = EPCI_CTE + """
      SELECT code_epci, MAX(nom_epci) AS nom_epci
      FROM epci_map
      GROUP BY code_epci
      ORDER BY MAX(nom_epci)
    """
    return [{"code_epci": c, "nom_epci": n} for c, n in rows(sql)]

@app.get("/api/epci/{code_epci}")
def epci_bbox(code_epci: str):
    sql = EPCI_CTE + f"""
    SELECT ST_XMin(b), ST_YMin(b), ST_XMax(b), ST_YMax(b)
    FROM (
      SELECT ST_Envelope(ST_Transform(u.geom,4326)) AS b
      FROM (
        SELECT
          ST_UnaryUnion(
            ST_Collect(
              ST_Transform({geom_fixed(f"c.{COL_GEOM}")},2154)
            )
          ) AS geom
        FROM {T_COMMUNES} c
        JOIN epci_map m ON m.insee_com = c.{COL_INSEE}::text
        WHERE m.code_epci = %s
      ) u
    ) e
    """
    r = rows(sql, (code_epci,))
    if not r or r[0][0] is None:
        raise HTTPException(404, detail="EPCI introuvable")
    xmin, ymin, xmax, ymax = r[0]
    return {"bbox": [xmin, ymin, xmax, ymax]}

@app.get("/api/epci/{code_epci}/geojson")
def epci_geojson(code_epci: str):
    sql = EPCI_CTE + f"""
    SELECT jsonb_build_object(
      'type','FeatureCollection',
      'features', jsonb_build_array(
        jsonb_build_object(
          'type','Feature',
          'geometry', ST_AsGeoJSON(ST_Transform(u.geom,4326))::jsonb,
          'properties', jsonb_build_object(
            'code_epci', %s,
            'nom_epci', u.nom_epci
          )
        )
      )
    )
    FROM (
      SELECT
        MAX(m.nom_epci) AS nom_epci,
        ST_UnaryUnion(
          ST_Collect(ST_Transform({geom_fixed(f"c.{COL_GEOM}")},2154))
        ) AS geom
      FROM {T_COMMUNES} c
      JOIN epci_map m ON m.insee_com = c.{COL_INSEE}::text
      WHERE m.code_epci = %s
    ) u
    """
    gj = one(sql, (code_epci, code_epci))
    if not gj:
        raise HTTPException(404, detail="EPCI introuvable")
    return gj

# -----------------------------
# API Communes (filtrables par EPCI)
# -----------------------------
@app.get("/api/communes")
def list_communes(code_epci: Optional[str] = Query(default=None)):
    if code_epci:
        sql = EPCI_CTE + f"""
          SELECT c.{COL_INSEE}::text AS insee_com, c.{COL_NOM}::text AS nom
          FROM {T_COMMUNES} c
          JOIN epci_map m ON m.insee_com = c.{COL_INSEE}::text
          WHERE m.code_epci = %s
          ORDER BY c.{COL_NOM}
        """
        data = rows(sql, (code_epci,))
    else:
        sql = f"""
          SELECT {COL_INSEE}::text AS insee_com, {COL_NOM}::text AS nom
          FROM {T_COMMUNES}
          ORDER BY {COL_NOM}
        """
        data = rows(sql)
    return [{"insee_com": i, "nom": n} for i, n in data]

@app.get("/api/communes/geojson")
def communes_geojson(code_epci: Optional[str] = Query(default=None)):
    if code_epci:
        sql = EPCI_CTE + f"""
        SELECT jsonb_build_object(
          'type','FeatureCollection',
          'features', COALESCE(jsonb_agg(
            jsonb_build_object(
              'type','Feature',
              'geometry', ST_AsGeoJSON(ST_Transform({geom_fixed(f"c.{COL_GEOM}")},4326))::jsonb,
              'properties', jsonb_build_object(
                'insee_com', c.{COL_INSEE}::text,
                'nom', c.{COL_NOM}::text
              )
            )
          ),'[]'::jsonb)
        )
        FROM {T_COMMUNES} c
        JOIN epci_map m ON m.insee_com = c.{COL_INSEE}::text
        WHERE m.code_epci = %s
        """
        return one(sql, (code_epci,))
    else:
        sql = f"""
        SELECT jsonb_build_object(
          'type','FeatureCollection',
          'features', COALESCE(jsonb_agg(
            jsonb_build_object(
              'type','Feature',
              'geometry', ST_AsGeoJSON(ST_Transform({geom_fixed(COL_GEOM)},4326))::jsonb,
              'properties', jsonb_build_object(
                'insee_com', {COL_INSEE}::text,
                'nom', {COL_NOM}::text
              )
            )
          ),'[]'::jsonb)
        )
        FROM {T_COMMUNES}
        """
        return one(sql)

@app.get("/api/communes/{insee}")
def commune_bbox(insee: str):
    sql = f"""
      SELECT ST_XMin(b), ST_YMin(b), ST_XMax(b), ST_YMax(b)
      FROM (
        SELECT ST_Envelope(ST_Transform({geom_fixed(COL_GEOM)},4326)) b
        FROM {T_COMMUNES}
        WHERE {COL_INSEE} = %s
      ) t
    """
    r = rows(sql, (insee,))
    if not r:
        raise HTTPException(404, detail="Commune introuvable")
    xmin, ymin, xmax, ymax = r[0]
    return {"bbox": [xmin, ymin, xmax, ymax]}

# -----------------------------
# Cadastre (lignes)
# -----------------------------
@app.get("/api/communes/{insee}/layers/cadastre")
def cadastre_layer(insee: str):
    sql = f"""
    SELECT jsonb_build_object(
      'type','FeatureCollection',
      'features', COALESCE(jsonb_agg(
        jsonb_build_object(
          'type','Feature',
          'geometry', ST_AsGeoJSON(ST_Transform({geom_fixed(CAD_GEOM)},4326))::jsonb,
          'properties', jsonb_build_object(
            'section', upper({CAD_SECTION}),
            'numero', lpad({CAD_NUM}::text,4,'0'),
            'contenance_m2', COALESCE(NULLIF({CAD_CONT}::text,'')::numeric, NULL)
          )
        )
      ),'[]'::jsonb)
    )
    FROM {T_CADASTRE}
    WHERE {CAD_COM} = %s
    """
    return one(sql, (insee,))

# -----------------------------
# ZAN (indicateurs)
# -----------------------------
@app.get("/api/communes/{insee}/zan")
def zan_gauge(insee: str, year: int = Query(..., ge=2000, le=2100)):
    # 1) surface totale commune (ha)
    sql_total = f"""
      SELECT ST_Area(ST_Transform({geom_fixed(COL_GEOM)},2154)) / 10000.0
      FROM {T_COMMUNES}
      WHERE {COL_INSEE} = %s
    """
    total_ha = float(one(sql_total, (insee,)) or 0)

    # 2) FF : consommation (jusqu’à FF_MAX_YEAR)
    year_ff = min(int(year), FF_MAX_YEAR)

    sql_ff = f"""
    WITH cad AS (
      SELECT
        upper({CAD_SECTION})::text AS section,
        lpad({CAD_NUM}::text,4,'0') AS numero,
        COALESCE(
          NULLIF({CAD_CONT}::text,'')::numeric,
          ST_Area(ST_Transform({geom_fixed(CAD_GEOM)},2154))
        ) AS contenance_m2
      FROM {T_CADASTRE}
      WHERE {CAD_COM} = %s
    ),
    ff AS (
      SELECT
        upper({FF_SECTION})::text AS section,
        lpad({FF_NUM}::text,4,'0') AS numero,
        CASE WHEN {FF_NBAT} ~ '^[0-9]+$' THEN {FF_NBAT}::int ELSE 0 END AS nbat_int,
        CASE WHEN {FF_YEAR} ~ '^[0-9]+$' THEN {FF_YEAR}::int ELSE NULL END AS annee_min,
        COALESCE(NULLIF(upper(trim({FF_TLOCDOM})),''),'AUCUN LOCAL') AS usage_dom
      FROM {T_FF}
      WHERE {FF_COM} = %s
    ),
    j AS (
      SELECT
        cad.section, cad.numero, cad.contenance_m2,
        ff.nbat_int, ff.annee_min, ff.usage_dom
      FROM cad
      LEFT JOIN ff ON ff.section=cad.section AND ff.numero=cad.numero
    )
    SELECT
      COUNT(*)::bigint AS nb_parcelles_total,
      COUNT(*) FILTER (WHERE nbat_int>0 AND (annee_min IS NULL OR annee_min<=%s))::bigint AS nb_parcelles_baties,
      COALESCE(SUM(contenance_m2) FILTER (WHERE nbat_int>0 AND (annee_min IS NULL OR annee_min<=%s)),0)::numeric AS m2_consomme_ff
    FROM j
    """
    nb_total, nb_baties, m2_ff = rows(sql_ff, (insee, insee, year_ff, year_ff))[0]
    consomme_ff_ha = float(m2_ff or 0) / 10000.0

    # 3) SIT@DEL : complément récent (DOC) si année > FF_MAX_YEAR
    sitadel_ha = 0.0
    nb_sitadel = 0

    if int(year) > FF_MAX_YEAR:
        y1 = SITADEL_MIN_YEAR
        y2 = int(year)

        sql_sit = f"""
        WITH sit AS (
          SELECT
            'newlog'::text AS source,
            type_dau::text AS type_dau,
            num_dau::text AS num_dau,
            {SIT_DATE_DOC}::date AS date_doc,
            COALESCE(NULLIF({SIT_SUP}::text,'')::numeric,0) AS sup_terrain_m2
          FROM {SIT_LOG}
          WHERE {SIT_INSEE} = %s

          UNION ALL

          SELECT
            'newlocaux'::text AS source,
            type_dau::text AS type_dau,
            num_dau::text AS num_dau,
            {SIT_DATE_DOC}::date AS date_doc,
            COALESCE(NULLIF({SIT_SUP}::text,'')::numeric,0) AS sup_terrain_m2
          FROM {SIT_LOC}
          WHERE {SIT_INSEE} = %s
        ),
        x AS (
          SELECT DISTINCT
            (source||':'||type_dau||':'||num_dau) AS pid,
            sup_terrain_m2
          FROM sit
          WHERE date_doc IS NOT NULL
            AND EXTRACT(YEAR FROM date_doc) BETWEEN %s AND %s
        )
        SELECT
          COALESCE(SUM(sup_terrain_m2),0)::numeric AS m2_sitadel,
          COUNT(*)::bigint AS nb_projets
        FROM x
        """
        m2_sit, nb_sit = rows(sql_sit, (insee, insee, y1, y2))[0]
        sitadel_ha = float(m2_sit or 0) / 10000.0
        nb_sitadel = int(nb_sit or 0)

    consomme_ha = consomme_ff_ha + sitadel_ha
    pct = (consomme_ha / total_ha * 100.0) if total_ha > 0 else 0.0

    return {
        "year": int(year),
        "year_ff": int(year_ff),
        "total_ha": total_ha,
        "consomme_ff_ha": consomme_ff_ha,
        "sitadel_ha": sitadel_ha,
        "consomme_ha": consomme_ha,
        "pct": pct,
        "nb_parcelles_total": int(nb_total or 0),
        "nb_parcelles_baties": int(nb_baties or 0),
        "nb_sitadel": nb_sitadel,
    }

# -----------------------------
# ZAN (couche FF consommée)
# -----------------------------
@app.get("/api/communes/{insee}/layers/zan")
def zan_layer(insee: str, year: int = Query(..., ge=2000, le=2100)):
    year_ff = min(int(year), FF_MAX_YEAR)

    sql = f"""
    WITH cad AS (
      SELECT
        upper({CAD_SECTION})::text AS section,
        lpad({CAD_NUM}::text,4,'0') AS numero,
        COALESCE(
          NULLIF({CAD_CONT}::text,'')::numeric,
          ST_Area(ST_Transform({geom_fixed(CAD_GEOM)},2154))
        ) AS contenance_m2,
        {geom_fixed(CAD_GEOM)} AS geom
      FROM {T_CADASTRE}
      WHERE {CAD_COM} = %s
    ),
    ff AS (
      SELECT
        upper({FF_SECTION})::text AS section,
        lpad({FF_NUM}::text,4,'0') AS numero,
        CASE WHEN {FF_NBAT} ~ '^[0-9]+$' THEN {FF_NBAT}::int ELSE 0 END AS nbat_int,
        CASE WHEN {FF_YEAR} ~ '^[0-9]+$' THEN {FF_YEAR}::int ELSE NULL END AS annee_min,
        COALESCE(NULLIF(upper(trim({FF_TLOCDOM})),''),'AUCUN LOCAL') AS usage_dom
      FROM {T_FF}
      WHERE {FF_COM} = %s
    ),
    j AS (
      SELECT
        cad.section, cad.numero, cad.contenance_m2, cad.geom,
        ff.nbat_int, ff.annee_min, ff.usage_dom
      FROM cad
      LEFT JOIN ff ON ff.section=cad.section AND ff.numero=cad.numero
    )
    SELECT jsonb_build_object(
      'type','FeatureCollection',
      'features', COALESCE(jsonb_agg(
        jsonb_build_object(
          'type','Feature',
          'geometry', ST_AsGeoJSON(ST_Transform(geom,4326))::jsonb,
          'properties', jsonb_build_object(
            'section', section,
            'numero', numero,
            'contenance_m2', contenance_m2,
            'annee_min', annee_min,
            'usage_dom', usage_dom
          )
        )
      ),'[]'::jsonb)
    )
    FROM j
    WHERE nbat_int > 0
      AND (annee_min IS NULL OR annee_min <= %s)
    """
    return one(sql, (insee, insee, year_ff))

# -----------------------------
# SIT@DEL (points) - basé sur DOC + couleur côté front
# -----------------------------
@app.get("/api/communes/{insee}/layers/sitadel")
def sitadel_layer(insee: str, year: int = Query(..., ge=2000, le=2100)):
    y1 = SITADEL_MIN_YEAR
    y2 = int(year)

    sql = f"""
    WITH cad AS (
      SELECT
        upper({CAD_SECTION})::text AS section,
        lpad({CAD_NUM}::text,4,'0') AS numero,
        {geom_fixed(CAD_GEOM)} AS geom
      FROM {T_CADASTRE}
      WHERE {CAD_COM} = %s
    ),
    sit AS (
      SELECT
        'newlog'::text AS source,
        type_dau::text AS type_dau,
        num_dau::text AS num_dau,
        etat_dau::text AS etat_dau,
        {SIT_DATE_DOC}::date AS date_doc,                 -- ✅ DOC ouverture chantier
        {SIT_DATE_AUT}::date AS date_aut,
        {SIT_NATURE}::text AS nature_code,
        {SIT_DEST}::text AS destination,
        {SIT_ZONE}::text AS zone_op,
        COALESCE(NULLIF({SIT_SUP}::text,'')::numeric,0) AS superficie_terrain_m2,
        p.section,
        p.numero
      FROM {SIT_LOG} s
      CROSS JOIN LATERAL (
        VALUES
          (upper(s.{SIT_SEC1})::text, lpad(s.{SIT_NUM1}::text,4,'0')::text),
          (upper(s.{SIT_SEC2})::text, lpad(s.{SIT_NUM2}::text,4,'0')::text),
          (upper(s.{SIT_SEC3})::text, lpad(s.{SIT_NUM3}::text,4,'0')::text)
      ) AS p(section, numero)
      WHERE s.{SIT_INSEE} = %s AND p.section IS NOT NULL AND p.numero IS NOT NULL

      UNION ALL

      SELECT
        'newlocaux'::text AS source,
        type_dau::text AS type_dau,
        num_dau::text AS num_dau,
        etat_dau::text AS etat_dau,
        {SIT_DATE_DOC}::date AS date_doc,                 -- ✅ DOC ouverture chantier
        {SIT_DATE_AUT}::date AS date_aut,
        {SIT_NATURE}::text AS nature_code,
        {SIT_DEST}::text AS destination,
        {SIT_ZONE}::text AS zone_op,
        COALESCE(NULLIF({SIT_SUP}::text,'')::numeric,0) AS superficie_terrain_m2,
        p.section,
        p.numero
      FROM {SIT_LOC} s
      CROSS JOIN LATERAL (
        VALUES
          (upper(s.{SIT_SEC1})::text, lpad(s.{SIT_NUM1}::text,4,'0')::text),
          (upper(s.{SIT_SEC2})::text, lpad(s.{SIT_NUM2}::text,4,'0')::text),
          (upper(s.{SIT_SEC3})::text, lpad(s.{SIT_NUM3}::text,4,'0')::text)
      ) AS p(section, numero)
      WHERE s.{SIT_INSEE} = %s AND p.section IS NOT NULL AND p.numero IS NOT NULL
    ),
    j AS (
      SELECT
        s.*,
        ST_PointOnSurface(c.geom) AS geom_pt
      FROM sit s
      JOIN cad c ON c.section=s.section AND c.numero=s.numero
      WHERE s.date_doc IS NOT NULL
        AND EXTRACT(YEAR FROM s.date_doc) BETWEEN %s AND %s
    )
    SELECT jsonb_build_object(
      'type','FeatureCollection',
      'features', COALESCE(jsonb_agg(
        jsonb_build_object(
          'type','Feature',
          'geometry', ST_AsGeoJSON(ST_Transform(geom_pt,4326))::jsonb,
          'properties', jsonb_build_object(
            'source', source,
            'type_dau', type_dau,
            'num_dau', num_dau,
            'etat_dau', etat_dau,
            'date_doc', to_char(date_doc,'YYYY-MM-DD'),
            'nature_code', nature_code,
            'destination', destination,
            'zone_op', zone_op,
            'superficie_terrain_m2', superficie_terrain_m2,
            'section', section,
            'numero', numero
          )
        )
      ),'[]'::jsonb)
    )
    FROM j
    """
    return one(sql, (insee, insee, insee, y1, y2))