"""Fetch FEMA NFHL flood zones for Delaware Co area via the FEMA ArcGIS REST.

Full statewide is huge; we filter server-side to a bbox around Whitetail and
pull only high-value zones (100-year AE/A, 500-year X shaded, floodways).
"""
from __future__ import annotations

import concurrent.futures as cf
import time
from pathlib import Path

import geopandas as gpd
import requests
from shapely.geometry import shape
from sqlalchemy import create_engine, text

DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com")
BASE = "https://hazards.fema.gov/arcgis/rest/services/public/NFHL/MapServer"
LAYER = 28   # Flood Hazard Zones
PAGE = 1000
# generous bbox around Whitetail — ~35 mi radius
BBOX = "-83.5,39.85,-82.55,40.42"


def _env(k):
    for line in (DATA_DIR / ".env.gis").read_text().splitlines():
        if line.startswith(f"{k}="):
            return line.split("=", 1)[1].strip()
    raise SystemExit(f"{k} missing")


def _fetch_page(offset: int):
    for attempt in range(4):
        try:
            r = requests.get(
                f"{BASE}/{LAYER}/query",
                params={
                    "where": "1=1",
                    "geometry": BBOX,
                    "geometryType": "esriGeometryEnvelope",
                    "spatialRel": "esriSpatialRelIntersects",
                    "outFields": "FLD_ZONE,ZONE_SUBTY,SFHA_TF,STATIC_BFE",
                    "returnGeometry": "true",
                    "outSR": "4326",
                    "inSR": "4326",
                    "f": "geojson",
                    "resultOffset": offset,
                    "resultRecordCount": PAGE,
                    "geometryPrecision": 6,
                },
                timeout=180,
            )
            r.raise_for_status()
            return r.json().get("features", [])
        except (requests.ConnectionError, requests.Timeout) as e:
            wait = 2 * (attempt + 1)
            print(f"  offset {offset} attempt {attempt+1} failed: {e.__class__.__name__}, sleeping {wait}s")
            time.sleep(wait)
    print(f"  offset {offset} — giving up")
    return []


def main():
    # count first
    r = requests.get(f"{BASE}/{LAYER}/query", params={
        "where": "1=1", "geometry": BBOX, "geometryType": "esriGeometryEnvelope",
        "spatialRel": "esriSpatialRelIntersects", "returnCountOnly": "true",
        "f": "json", "inSR": "4326",
    }, timeout=60)
    count = r.json().get("count", 0)
    print(f"[fema] {count} flood zones in bbox {BBOX}")
    if count == 0:
        return
    offsets = list(range(0, count, PAGE))
    features = []
    # FEMA server drops connections when hit in parallel; keep to 2 threads
    with cf.ThreadPoolExecutor(max_workers=2) as ex:
        for i, batch in enumerate(ex.map(_fetch_page, offsets)):
            features.extend(batch)
            if (i + 1) % 5 == 0 or i + 1 == len(offsets):
                print(f"  {i+1}/{len(offsets)} pages → {len(features)} features")

    rows = []
    for f in features:
        try:
            geom = shape(f["geometry"])
            if not geom.is_valid: geom = geom.buffer(0)
        except Exception: continue
        p = f.get("properties") or {}
        rows.append({
            "fld_zone":   p.get("FLD_ZONE"),
            "zone_subty": p.get("ZONE_SUBTY"),
            "sfha_tf":    p.get("SFHA_TF"),
            "static_bfe": p.get("STATIC_BFE"),
            "geometry":   geom,
        })
    gdf = gpd.GeoDataFrame(rows, geometry="geometry", crs="EPSG:4326")
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_env('GISPASS')}@127.0.0.1:5432/westerville"
    )
    gdf.to_postgis("fema_flood", engine, if_exists="replace", index=False)
    with engine.begin() as conn:
        conn.execute(text("CREATE INDEX fema_flood_geom_idx ON fema_flood USING GIST (geometry);"))
        (n,) = conn.execute(text("SELECT count(*) FROM fema_flood")).one()
    print(f"[postgis] fema_flood: {n} rows")


if __name__ == "__main__":
    main()