"""Load wv_home_parcels.json + wv_voters.json into PostGIS.

Both source files carry only centroids (lat/lng), so we materialize POINT
geometry in EPSG:4326 and a projected copy in EPSG:3857 for tile serving.
"""
from __future__ import annotations

import json
import os
from pathlib import Path

import geopandas as gpd
import pandas as pd
from shapely.geometry import Point
from sqlalchemy import create_engine, text

DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com")
ENV_FILE = DATA_DIR / ".env.gis"


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


def load_parcels(engine) -> None:
    raw = json.loads((DATA_DIR / "wv_home_parcels.json").read_text())
    df = pd.DataFrame(raw["parcels"])
    df = df.dropna(subset=["lat", "lng"]).reset_index(drop=True)
    df["parcel_pk"] = range(1, len(df) + 1)
    gdf = gpd.GeoDataFrame(
        df,
        geometry=[Point(xy) for xy in zip(df["lng"], df["lat"])],
        crs="EPSG:4326",
    )
    gdf.to_postgis("parcels", engine, if_exists="replace", index=False)
    with engine.begin() as conn:
        conn.execute(text("CREATE INDEX parcels_geom_idx ON parcels USING GIST (geometry);"))
        conn.execute(text("ALTER TABLE parcels ADD PRIMARY KEY (parcel_pk);"))
        conn.execute(text("CREATE INDEX parcels_id_idx ON parcels (id);"))
        conn.execute(text('CREATE INDEX parcels_addr_idx ON parcels (LOWER("adr"));'))
    print(f"[parcels] loaded {len(gdf)} rows")


def load_voters(engine) -> None:
    raw = json.loads((DATA_DIR / "wv_voters.json").read_text())
    df = pd.DataFrame(raw["voters"])
    df = df.dropna(subset=["lat", "lng"])
    df["voter_id"] = range(1, len(df) + 1)
    gdf = gpd.GeoDataFrame(
        df,
        geometry=[Point(xy) for xy in zip(df["lng"], df["lat"])],
        crs="EPSG:4326",
    )
    gdf.to_postgis("voters", engine, if_exists="replace", index=False)
    with engine.begin() as conn:
        conn.execute(text("CREATE INDEX voters_geom_idx ON voters USING GIST (geometry);"))
        conn.execute(text("ALTER TABLE voters ADD PRIMARY KEY (voter_id);"))
        conn.execute(text('CREATE INDEX voters_addr_idx ON voters (LOWER("a"));'))
    print(f"[voters] loaded {len(gdf)} rows")


def main() -> None:
    password = _read_gispass()
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{password}@127.0.0.1:5432/westerville"
    )
    load_parcels(engine)
    load_voters(engine)
    with engine.begin() as conn:
        counts = conn.execute(text(
            "SELECT (SELECT count(*) FROM parcels), (SELECT count(*) FROM voters);"
        )).one()
        print(f"[verify] parcels={counts[0]}  voters={counts[1]}")


if __name__ == "__main__":
    main()