"""Fetch boundary/reference layers from Delaware Co ArcGIS REST into PostGIS.

Each layer's numeric id, target table name, and human label are listed below.
Filter geometry is the same as the parcels — the whole county (no bbox), because
these tables are tiny in count.
"""
from __future__ import annotations

import concurrent.futures as cf
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://maps.delco-gis.org/arcgiswebadaptor/rest/services/DelawareCountyData/MapServer"
PAGE = 1000

LAYERS = [
    # (layer_id, table_name)
    (5,  "delco_zipcodes"),
    (6,  "delco_townships"),
    (9,  "delco_subdivisions"),
    (14, "delco_tax_districts"),
    (15, "delco_municipalities"),
]


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_layer(layer_id: int, fields: list[str]) -> list[dict]:
    outFields = "*"   # always fetch all — arcgis rejects unknown field names silently
    # feature count
    r = requests.get(f"{BASE}/{layer_id}/query",
                     params={"where": "1=1", "returnCountOnly": "true", "f": "json"},
                     timeout=30)
    count = r.json().get("count", 0)
    print(f"[arcgis:{layer_id}] {count} features")
    features = []
    offsets = list(range(0, count, PAGE))

    def one(offset: int):
        return requests.get(
            f"{BASE}/{layer_id}/query",
            params={
                "where": "1=1",
                "outFields": outFields,
                "returnGeometry": "true",
                "outSR": "4326",
                "f": "geojson",
                "resultOffset": offset,
                "resultRecordCount": PAGE,
                "geometryPrecision": 7,
            },
            timeout=90,
        ).json().get("features", [])

    with cf.ThreadPoolExecutor(max_workers=4) as ex:
        for i, batch in enumerate(ex.map(one, offsets)):
            features.extend(batch)
            if (i + 1) % 10 == 0 or i + 1 == len(offsets):
                print(f"  {i+1}/{len(offsets)} pages → {len(features)} features")
    return features


def _to_gdf(features: list[dict]) -> gpd.GeoDataFrame:
    rows = []
    for f in features:
        try:
            geom = shape(f["geometry"])
            if not geom.is_valid: geom = geom.buffer(0)
        except Exception:
            continue
        props = f.get("properties") or {}
        rows.append({**{k.lower(): v for k, v in props.items()}, "geometry": geom})
    if not rows:
        return gpd.GeoDataFrame(columns=["geometry"], geometry="geometry", crs="EPSG:4326")
    return gpd.GeoDataFrame(rows, geometry="geometry", crs="EPSG:4326")


def main():
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_env('GISPASS')}@127.0.0.1:5432/westerville"
    )
    for lid, table in LAYERS:
        features = _fetch_layer(lid, [])
        gdf = _to_gdf(features)
        gdf.to_postgis(table, engine, if_exists="replace", index=False)
        with engine.begin() as conn:
            conn.execute(text(f"CREATE INDEX {table}_geom_idx ON {table} USING GIST (geometry);"))
            (n,) = conn.execute(text(f"SELECT count(*) FROM {table}")).one()
        print(f"[postgis] {table}: {n} rows\n")


if __name__ == "__main__":
    main()