"""Fetch Delaware County parcel POLYGONS from ArcGIS REST and load into PostGIS.

Layer: DelawareCountyData/MapServer/0 (name=Parcel, esriGeometryPolygon)
Source SRID: 102722 (Ohio State Plane N ftUS). We request outSR=4326.
Layer publishes maxRecordCount=1000, so we paginate.
"""
from __future__ import annotations

import concurrent.futures as cf
import json
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")
LAYER = "https://maps.delco-gis.org/arcgiswebadaptor/rest/services/DelawareCountyData/MapServer/0"
PAGE = 1000
FIELDS = "PARCEL_NO,OWNER1,ADDR1,CLASS,SUB_NAME,MKT_TOTAL,LAND_MKT,IMPR_MKT,TXB_TOTAL,TAX_ANNUAL,YR_BUILT,ACREAGE"


def _read_gispass() -> str:
    for line in (DATA_DIR / ".env.gis").read_text().splitlines():
        if line.startswith("GISPASS="):
            return line.split("=", 1)[1].strip()
    raise SystemExit("GISPASS missing")


def _fetch_page(offset: int) -> list[dict]:
    r = requests.get(
        f"{LAYER}/query",
        params={
            "where": "1=1",
            "outFields": "PARCEL_NO,OWNER1,ADDR1,CLASS",
            "returnGeometry": "true",
            "outSR": "4326",
            "f": "geojson",
            "resultOffset": offset,
            "resultRecordCount": PAGE,
            "geometryPrecision": 7,
        },
        timeout=60,
    )
    r.raise_for_status()
    return r.json().get("features", [])


def main() -> None:
    count = requests.get(
        f"{LAYER}/query", params={"where": "1=1", "returnCountOnly": "true", "f": "json"}, timeout=30
    ).json()["count"]
    print(f"[arcgis] {count} parcels to fetch in pages of {PAGE}")

    offsets = list(range(0, count, PAGE))
    all_features: list[dict] = []
    with cf.ThreadPoolExecutor(max_workers=8) as ex:
        for i, batch in enumerate(ex.map(_fetch_page, offsets)):
            all_features.extend(batch)
            if (i + 1) % 10 == 0 or i + 1 == len(offsets):
                print(f"  page {i+1}/{len(offsets)} — total features so far: {len(all_features)}")

    print(f"[fetch] got {len(all_features)} features")

    rows = []
    bad = 0
    for f in all_features:
        try:
            geom = shape(f["geometry"])
            if not geom.is_valid:
                geom = geom.buffer(0)
        except Exception:
            bad += 1
            continue
        props = f.get("properties", {})
        rows.append({
            "parcel_no": (props.get("PARCEL_NO") or "").strip(),
            "owner": (props.get("OWNER1") or "").strip(),
            "addr": (props.get("ADDR1") or "").strip(),
            "class": (props.get("CLASS") or "").strip(),
            "geometry": geom,
        })
    if bad:
        print(f"[warn] {bad} features had invalid geometry (dropped)")

    gdf = gpd.GeoDataFrame(rows, crs="EPSG:4326")
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_read_gispass()}@127.0.0.1:5432/westerville"
    )
    gdf.to_postgis("parcel_geoms", engine, if_exists="replace", index=False)
    with engine.begin() as conn:
        conn.execute(text("CREATE INDEX parcel_geoms_geom_idx ON parcel_geoms USING GIST (geometry);"))
        conn.execute(text("CREATE INDEX parcel_geoms_pn_idx  ON parcel_geoms (parcel_no);"))
        (n_rows,) = conn.execute(text("SELECT count(*) FROM parcel_geoms")).one()
    print(f"[postgis] parcel_geoms loaded: {n_rows} rows")


if __name__ == "__main__":
    main()