"""Fetch TIGER/Line boundaries for Ohio and load into PostGIS.

- tracts (2024)
- block groups (2024)
- places / cities (2024)
- county subdivisions (2024)
- VTDs = 'voting districts' ~ precincts (2020 vintage — TIGER only publishes VTDs
  with the decennial PL 94-171 redistricting series)
- counties (2024; national file, filtered to OH)
"""
from __future__ import annotations

import io
import zipfile
from pathlib import Path

import geopandas as gpd
import requests
from sqlalchemy import create_engine, text

DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com")
CACHE = DATA_DIR / "gis_scripts" / ".cache"
CACHE.mkdir(exist_ok=True)

OH = "39"

LAYERS = [
    # table_name, url, national_filter
    ("census_tracts", f"https://www2.census.gov/geo/tiger/TIGER2024/TRACT/tl_2024_{OH}_tract.zip", None),
    ("census_bg",     f"https://www2.census.gov/geo/tiger/TIGER2024/BG/tl_2024_{OH}_bg.zip",       None),
    ("places",        f"https://www2.census.gov/geo/tiger/TIGER2024/PLACE/tl_2024_{OH}_place.zip", None),
    ("county_subdiv", f"https://www2.census.gov/geo/tiger/TIGER2024/COUSUB/tl_2024_{OH}_cousub.zip", None),
    ("counties",      "https://www2.census.gov/geo/tiger/TIGER2024/COUNTY/tl_2024_us_county.zip",  ("STATEFP", OH)),
    ("vtds",          f"https://www2.census.gov/geo/tiger/TIGER2020PL/STATE/{OH}_OHIO/{OH}/tl_2020_{OH}_vtd20.zip", None),
    ("school_districts", f"https://www2.census.gov/geo/tiger/TIGER2024/UNSD/tl_2024_{OH}_unsd.zip", None),
    ("cong_districts",   f"https://www2.census.gov/geo/tiger/TIGER2024/CD/tl_2024_{OH}_cd119.zip", None),
    ("state_house",      f"https://www2.census.gov/geo/tiger/TIGER2024/SLDL/tl_2024_{OH}_sldl.zip", None),
    ("state_senate",     f"https://www2.census.gov/geo/tiger/TIGER2024/SLDU/tl_2024_{OH}_sldu.zip", None),
]


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 _download(url: str) -> Path:
    name = url.rsplit("/", 1)[-1]
    dest = CACHE / name
    if dest.exists() and dest.stat().st_size > 100_000:
        return dest
    print(f"[fetch] {url}")
    r = requests.get(url, timeout=240)
    r.raise_for_status()
    dest.write_bytes(r.content)
    print(f"  {len(r.content)/1e6:.1f} MB")
    return dest


def _load(engine, table: str, url: str, national_filter):
    zpath = _download(url)
    out = CACHE / f"{table}_shp"
    out.mkdir(exist_ok=True)
    with zipfile.ZipFile(zpath) as z:
        z.extractall(out)
    shp = next(out.rglob("*.shp"))
    gdf = gpd.read_file(shp)
    if national_filter:
        col, val = national_filter
        gdf = gdf[gdf[col] == val].copy()
    if gdf.crs and gdf.crs.to_epsg() != 4326:
        gdf = gdf.to_crs("EPSG:4326")
    gdf.columns = [c.lower() for c in gdf.columns]
    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")


def main() -> None:
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_read_gispass()}@127.0.0.1:5432/westerville"
    )
    for table, url, flt in LAYERS:
        try:
            _load(engine, table, url, flt)
        except Exception as e:
            print(f"[fail] {table}: {e!r}")


if __name__ == "__main__":
    main()