"""Build wv_home_lean.json: one row per parcel with aggregated voter stats.

Strategy: assign each voter to the nearest parcel centroid via a KNN spatial
join (both source layers are points — parcel polygons are not in the export).
We cap the join distance at 40 meters, which covers the typical parcel
diameter in this Delaware Co dataset and keeps voters at addresses without a
matching parcel from being roped in.
"""
from __future__ import annotations

import json
from collections import Counter, defaultdict
from pathlib import Path

import pandas as pd
from sqlalchemy import create_engine, text

DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com")
OUT_FILE = DATA_DIR / "wv_home_lean.json"
MAX_M = 40  # meters — nearest-parcel cap


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")


ASSIGN_SQL = """
WITH nearest AS (
    SELECT
        v.voter_id,
        v.p    AS party,
        v.pl   AS lean,
        v.gv   AS gv,
        v.fn, v.ln, v.a AS addr, v.yr, v.reg, v.pct,
        p.parcel_pk,
        p.id   AS parcel_id,
        p.adr  AS parcel_adr,
        ST_Distance(v.geometry::geography, p.geometry::geography) AS dist_m
    FROM voters v
    CROSS JOIN LATERAL (
        SELECT parcel_pk, id, adr, geometry
        FROM parcels
        ORDER BY parcels.geometry <-> v.geometry
        LIMIT 1
    ) p
)
SELECT * FROM nearest WHERE dist_m <= :max_m;
"""

# All FEC records in the target ZIPs, keyed by (upper(ln), upper(fn))
FEC_SQL = """
SELECT
    upper(contributor_last_name)  AS ln,
    upper(contributor_first_name) AS fn,
    cycle, contribution_date, amount,
    committee_name, committee_type
FROM fec_contributions
WHERE contributor_zip = ANY(:zips)
  AND contributor_last_name  IS NOT NULL
  AND contributor_first_name IS NOT NULL
ORDER BY contribution_date DESC;
"""
FEC_ZIPS = [
    "43003", "43011", "43013", "43015", "43016", "43017", "43021",
    "43031", "43035", "43040", "43054", "43061", "43064", "43065",
    "43066", "43074", "43081", "43082", "43235", "43240", "43334",
    "43342", "43344", "43356",
]  # every Delaware County ZIP


def dominant_lean(counter: Counter[str]) -> str:
    if not counter:
        return ""
    order = ["R", "D", "U", "L", "M", "N", ""]
    return max(counter.items(), key=lambda kv: (kv[1], -order.index(kv[0]) if kv[0] in order else -99))[0]


def dominant_party(counter: Counter[str]) -> str:
    """Same as dominant_lean but promotes to 'S' (split R/D) when both parties
    are present within ~2:1 of each other. Matches the parcels_full paint spec
    and Delaware-map.html's client-side rule."""
    if not counter:
        return ""
    r, d = counter.get("R", 0), counter.get("D", 0)
    if r > 0 and d > 0 and min(r, d) * 2 >= max(r, d):
        return "S"
    return dominant_lean(counter)


CTX_SQL = """
SELECT
    p.parcel_pk,
    t.geoid          AS tract_geoid,
    t.name           AS tract_name,
    a.hh_income_median,
    a.home_value_med,
    a.pct_bachplus,
    a.pct_owner,
    a.pop_total      AS tract_pop,
    vtd.name20       AS vtd_name,
    vtd.namelsad20   AS vtd_label,
    sd.name          AS school_district,
    cnty.name        AS county_name,
    twn.name         AS township_name,
    muni.name        AS muni_name,
    cd.namelsad      AS cong_dist,
    sh.namelsad      AS state_house,
    ss.namelsad      AS state_senate,
    sch.name         AS nearest_school,
    sch.locale       AS nearest_school_locale,
    ROUND(sch.dist_m::numeric)::int AS nearest_school_m,
    flood.fld_zone   AS flood_zone,
    flood.sfha_tf    AS flood_sfha,
    cdc.obesity      AS cdc_obesity,
    cdc.csmoking     AS cdc_smoking,
    cdc.bphigh       AS cdc_bp_high,
    cdc.access2      AS cdc_no_access
FROM parcels p
LEFT JOIN LATERAL (
    SELECT geoid, name FROM census_tracts
    WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) t ON TRUE
LEFT JOIN acs_tract a ON a.geoid = t.geoid
LEFT JOIN LATERAL (
    SELECT name20, namelsad20 FROM vtds
    WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) vtd ON TRUE
LEFT JOIN LATERAL (
    SELECT name FROM school_districts
    WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) sd ON TRUE
LEFT JOIN LATERAL (
    SELECT name FROM counties WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) cnty ON TRUE
LEFT JOIN LATERAL (
    SELECT twp_name AS name FROM delco_townships WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) twn ON TRUE
LEFT JOIN LATERAL (
    SELECT mun_name AS name FROM delco_municipalities WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) muni ON TRUE
LEFT JOIN LATERAL (
    SELECT namelsad FROM cong_districts WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) cd ON TRUE
LEFT JOIN LATERAL (
    SELECT namelsad FROM state_house WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) sh ON TRUE
LEFT JOIN LATERAL (
    SELECT namelsad FROM state_senate WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) ss ON TRUE
LEFT JOIN LATERAL (
    SELECT name, locale,
           ST_Distance(geometry::geography, p.geometry::geography) AS dist_m
    FROM schools
    ORDER BY geometry <-> p.geometry
    LIMIT 1
) sch ON TRUE
LEFT JOIN LATERAL (
    SELECT fld_zone, sfha_tf FROM fema_flood
    WHERE ST_Contains(geometry, p.geometry) LIMIT 1
) flood ON TRUE
LEFT JOIN cdc_places cdc ON cdc.geoid = t.geoid;
"""

# Bounding box for overlay layers + Leaflet-fetch JSONs — roughly a 5 mi box
# around CENTER. Constrained so wv_home_lean.json / wv_voters.json stay small
# enough for Leaflet fetch-all rendering; county-wide viz lives on MapLibre
# tiles (parcels_full, voters).
BBOX_LAT_R = 0.07
BBOX_LNG_R = 0.09
CENTER_LAT = 40.14917
CENTER_LNG = -82.899


def _bbox_wkt() -> str:
    return (
        f"POLYGON(("
        f"{CENTER_LNG - BBOX_LNG_R} {CENTER_LAT - BBOX_LAT_R}, "
        f"{CENTER_LNG + BBOX_LNG_R} {CENTER_LAT - BBOX_LAT_R}, "
        f"{CENTER_LNG + BBOX_LNG_R} {CENTER_LAT + BBOX_LAT_R}, "
        f"{CENTER_LNG - BBOX_LNG_R} {CENTER_LAT + BBOX_LAT_R}, "
        f"{CENTER_LNG - BBOX_LNG_R} {CENTER_LAT - BBOX_LAT_R}))"
    )


def _has_table(engine, name: str) -> bool:
    with engine.begin() as conn:
        (n,) = conn.execute(text(
            "SELECT count(*) FROM information_schema.tables "
            "WHERE table_schema='public' AND table_name=:n"
        ), {"n": name}).one()
    return n > 0


def _emit_overlay_geojson(engine, sql: str, params: dict, out_path: Path, label: str) -> int:
    with engine.begin() as conn:
        rows = conn.execute(text(sql), params).mappings().all()
    features = []
    _skip = {"geometry_json", "geometry", "shape_length", "shape_area", "objectid"}
    for r in rows:
        geom = r.get("geometry_json")
        if not geom:
            continue
        props = {k: v for k, v in r.items() if k not in _skip}
        for k, v in list(props.items()):
            if hasattr(v, "quantize"):    # Decimal
                props[k] = float(v)
        features.append({"type": "Feature", "properties": props, "geometry": json.loads(geom)})
    payload = {"type": "FeatureCollection", "features": features}
    out_path.write_text(json.dumps(payload))
    print(f"[overlay:{label}] {len(features)} → {out_path.name}")
    return len(features)


PROPAGATE_SQL = """
-- Refresh n_voters / n_r / n_d / n_u / n_super / dom_party / dom_lean on ALL
-- parcels county-wide from the current voters table via KNN within MAX_M meters.
UPDATE parcels p SET
    n_voters  = COALESCE(s.n_voters, 0),
    n_r       = COALESCE(s.n_r, 0),
    n_d       = COALESCE(s.n_d, 0),
    n_u       = COALESCE(s.n_u, 0),
    n_super   = COALESCE(s.n_super, 0),
    dom_party = COALESCE(s.dom_party, ''),
    dom_lean  = COALESCE(s.dom_lean, '')
FROM (
    SELECT parcel_pk,
        COUNT(*)                                                        AS n_voters,
        COUNT(*) FILTER (WHERE party = 'R')                             AS n_r,
        COUNT(*) FILTER (WHERE party = 'D')                             AS n_d,
        COUNT(*) FILTER (WHERE party = 'U')                             AS n_u,
        COUNT(*) FILTER (WHERE gv >= 3)                                 AS n_super,
        -- 'S' when a parcel has both R and D voters within ~2:1 of each other
        -- (e.g. 1R+1D, 2R+1D, 2R+2D, 3R+2D). Above 2:1 the majority party wins.
        CASE
          WHEN COUNT(*) FILTER (WHERE party = 'R') > 0
           AND COUNT(*) FILTER (WHERE party = 'D') > 0
           AND LEAST(COUNT(*) FILTER (WHERE party = 'R'),
                     COUNT(*) FILTER (WHERE party = 'D')) * 2
             >= GREATEST(COUNT(*) FILTER (WHERE party = 'R'),
                         COUNT(*) FILTER (WHERE party = 'D'))
          THEN 'S'
          ELSE (MODE() WITHIN GROUP (ORDER BY party) FILTER (WHERE party <> ''))
        END                                                             AS dom_party,
        (MODE() WITHIN GROUP (ORDER BY lean)  FILTER (WHERE lean  <> '')) AS dom_lean
    FROM (
        SELECT
            p.parcel_pk,
            v.p AS party,
            v.pl AS lean,
            v.gv,
            ST_Distance(v.geometry::geography, p.geometry::geography) AS d
        FROM voters v
        CROSS JOIN LATERAL (
            SELECT parcel_pk, geometry FROM parcels
            ORDER BY parcels.geometry <-> v.geometry
            LIMIT 1
        ) p
    ) j
    WHERE d <= :max_m
    GROUP BY parcel_pk
) s
WHERE p.parcel_pk = s.parcel_pk;

"""

REFRESH_SQL = "REFRESH MATERIALIZED VIEW parcels_full;"


def main() -> None:
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_read_gispass()}@127.0.0.1:5432/westerville"
    )

    # ---- county-wide propagation ---------------------------------------
    print(f"[propagate] running county-wide voter->parcel KNN (<= {MAX_M} m)…")
    with engine.begin() as conn:
        conn.execute(text(PROPAGATE_SQL), {"max_m": MAX_M})
        (n_with,) = conn.execute(text(
            "SELECT COUNT(*) FROM parcels WHERE n_voters > 0"
        )).one()
    print(f"[propagate] {n_with} parcels now have >= 1 voter within {MAX_M} m")

    # ---- refresh parcels_full mat view so MapLibre tiles see new attrs --
    print("[refresh] REFRESH MATERIALIZED VIEW parcels_full …")
    with engine.begin() as conn:
        # CONCURRENTLY requires a unique index — try it first, fall back if
        # not (initial refreshes after DDL need a plain REFRESH).
        try:
            conn.execute(text(REFRESH_SQL))
        except Exception:
            conn.execute(text("REFRESH MATERIALIZED VIEW parcels_full;"))
    print("[refresh] parcels_full ready")

    with engine.begin() as conn:
        rows = conn.execute(text(ASSIGN_SQL), {"max_m": MAX_M}).mappings().all()
        # Constrain the JSON emit to the ~10 mi Westerville box — the file
        # drives Leaflet interactivity near HOME; county-wide viz comes from
        # MapLibre `parcels_full` tiles.
        parcels = conn.execute(text(
            "SELECT parcel_pk, id, adr, own, lat, lng, mkt, sub "
            "FROM parcels "
            "WHERE geometry && ST_MakeEnvelope(:xmin,:ymin,:xmax,:ymax,4326)"
        ), {
            "xmin": CENTER_LNG - BBOX_LNG_R, "xmax": CENTER_LNG + BBOX_LNG_R,
            "ymin": CENTER_LAT - BBOX_LAT_R, "ymax": CENTER_LAT + BBOX_LAT_R,
        }).mappings().all()
        ctx_rows = conn.execute(text(CTX_SQL)).mappings().all()
        fec_rows = conn.execute(text(FEC_SQL), {"zips": FEC_ZIPS}).mappings().all()

    # index enrichment by parcel_pk
    ctx_by_pk: dict[int, dict] = {r["parcel_pk"]: dict(r) for r in ctx_rows}

    print(f"[join] {len(rows)} voter->parcel assignments (<= {MAX_M} m)")
    print(f"[fec]  {len(fec_rows)} FEC records in zip {FEC_ZIPS}")

    # index FEC by (last, first) — many rows per key (multiple donations from same person)
    fec_by_name: dict[tuple[str, str], list[dict]] = defaultdict(list)
    for f in fec_rows:
        fec_by_name[(f["ln"], f["fn"])].append(dict(f))

    def _voter_fec_summary(v_last: str, v_first: str) -> dict | None:
        key = ((v_last or "").upper().strip(), (v_first or "").upper().strip())
        if not key[0] or not key[1]:
            return None
        matches = fec_by_name.get(key, [])
        if not matches:
            return None
        total_r = sum(m["amount"] or 0 for m in matches
                      if (m.get("committee_type") or "") in ("R","W","X","Y","Z"))  # R-affiliated committee types
        cmts = Counter(m["committee_name"] for m in matches if m["committee_name"])
        return {
            "n": len(matches),
            "total": round(float(sum(float(m["amount"] or 0) for m in matches)), 2),
            "first_date": str(min((m["contribution_date"] for m in matches if m["contribution_date"]), default="")),
            "last_date":  str(max((m["contribution_date"] for m in matches if m["contribution_date"]), default="")),
            "top_committees": [c for c, _ in cmts.most_common(3)],
            "recent": [
                {"date": str(m["contribution_date"]) if m["contribution_date"] else None,
                 "amount": float(m["amount"] or 0),
                 "committee": m["committee_name"],
                 "cycle": m["cycle"]}
                for m in sorted(matches, key=lambda x: x["contribution_date"] or "", reverse=True)[:10]
            ],
        }

    by_parcel: dict[int, list[dict]] = defaultdict(list)
    for r in rows:
        rec = dict(r)
        rec["fec"] = _voter_fec_summary(rec.get("ln"), rec.get("fn"))
        by_parcel[r["parcel_pk"]].append(rec)

    out = []
    tot_r = tot_d = tot_u = tot_super = 0
    for p in parcels:
        vs = by_parcel.get(p["parcel_pk"], [])
        party = Counter(v["party"] or "" for v in vs)
        lean = Counter(v["lean"] or "" for v in vs)
        supervoters = sum(1 for v in vs if (v.get("gv") or 0) >= 3)
        ctx = ctx_by_pk.get(p["parcel_pk"], {})
        rec = {
            "parcel_pk": p["parcel_pk"],
            "id": p["id"],
            "adr": p["adr"],
            "own": p["own"],
            "lat": p["lat"],
            "lng": p["lng"],
            "mkt": p["mkt"],
            "sub": p["sub"],
            "ctx": {
                "tract_geoid":    ctx.get("tract_geoid"),
                "tract_income":   float(ctx["hh_income_median"]) if ctx.get("hh_income_median") is not None else None,
                "tract_home_val": float(ctx["home_value_med"])   if ctx.get("home_value_med")   is not None else None,
                "tract_pct_bach": float(ctx["pct_bachplus"])     if ctx.get("pct_bachplus")     is not None else None,
                "tract_pct_own":  float(ctx["pct_owner"])        if ctx.get("pct_owner")        is not None else None,
                "tract_pop":      int(ctx["tract_pop"])          if ctx.get("tract_pop")        is not None else None,
                "precinct":       ctx.get("vtd_name") or ctx.get("vtd_label"),
                "school_district": ctx.get("school_district"),
                "county":         ctx.get("county_name"),
                "township":       ctx.get("township_name"),
                "muni":           ctx.get("muni_name"),
                "cong_dist":      ctx.get("cong_dist"),
                "state_house":    ctx.get("state_house"),
                "state_senate":   ctx.get("state_senate"),
                "nearest_school": ctx.get("nearest_school"),
                "nearest_school_m": int(ctx["nearest_school_m"]) if ctx.get("nearest_school_m") is not None else None,
                "flood_zone":     ctx.get("flood_zone"),
                "flood_sfha":     ctx.get("flood_sfha"),
                "cdc_obesity":    float(ctx["cdc_obesity"])   if ctx.get("cdc_obesity")   is not None else None,
                "cdc_smoking":    float(ctx["cdc_smoking"])   if ctx.get("cdc_smoking")   is not None else None,
                "cdc_bp_high":    float(ctx["cdc_bp_high"])   if ctx.get("cdc_bp_high")   is not None else None,
                "cdc_no_access":  float(ctx["cdc_no_access"]) if ctx.get("cdc_no_access") is not None else None,
            },
            "n": len(vs),
            "n_R": party.get("R", 0),
            "n_D": party.get("D", 0),
            "n_U": party.get("U", 0),
            "n_L": party.get("L", 0),
            "n_supervoters": supervoters,
            "dominant_party": dominant_party(party) if vs else "",
            "dominant_lean": dominant_lean(lean) if vs else "",
            "voters": [
                {
                    "fn": v["fn"], "ln": v["ln"],
                    "p": v["party"], "pl": v["lean"],
                    "gv": v["gv"], "yr": v["yr"], "reg": v["reg"],
                    "pct": v["pct"],
                    "fec": v.get("fec"),
                }
                for v in vs
            ],
            "fec_total": round(float(sum(float((v.get("fec") or {}).get("total", 0)) for v in vs)), 2),
            "fec_n_donors": sum(1 for v in vs if v.get("fec")),
        }
        tot_r += rec["n_R"]
        tot_d += rec["n_D"]
        tot_u += rec["n_U"]
        tot_super += rec["n_supervoters"]
        out.append(rec)

    payload = {
        "generated_at": pd.Timestamp.utcnow().isoformat(),
        "n_parcels": len(out),
        "n_parcels_with_voters": sum(1 for r in out if r["n"] > 0),
        "totals": {
            "n_R": tot_r, "n_D": tot_d, "n_U": tot_u,
            "n_supervoters": tot_super,
        },
        "join": {"strategy": "nearest_parcel_centroid", "max_m": MAX_M},
        "parcels": out,
    }
    OUT_FILE.write_text(json.dumps(payload, default=str))
    print(f"[out] {OUT_FILE} — {len(out)} parcels, "
          f"{payload['n_parcels_with_voters']} with voters, "
          f"R={tot_r} D={tot_d} U={tot_u} super={tot_super}")

    # ----- overlay GeoJSONs for the map's toggle chips -----
    bbox = _bbox_wkt()

    # ----- wv_home_parcels.json (Leaflet base parcel dots, county-wide) -----
    # No bbox — the base Leaflet dot layer should cover the whole county so
    # the default view isn't a Westerville-only rectangle. Include the party
    # summary fields (n_voters, n_r, n_d, dom_party, …) so Political color
    # mode works everywhere; the per-voter details in wv_home_lean.json stay
    # 5 mi around HOME for payload sanity.
    with engine.begin() as conn:
        parcels_all = conn.execute(text(
            "SELECT id, own, adr, cls, mkt, lan, imp, txb, tax, yr, ac, "
            "       bd, fb, hb, sub, lat, lng, "
            "       n_voters, n_r, n_d, n_u, n_super, dom_party, dom_lean "
            "FROM parcels "
            "WHERE lat IS NOT NULL AND lng IS NOT NULL"
        )).mappings().all()
    parcels_payload = {
        "generated_at": pd.Timestamp.utcnow().isoformat(),
        "center": {"lat": CENTER_LAT, "lng": CENTER_LNG},
        "county": "Delaware",
        "scope": "county-wide",
        "parcels": [dict(r) for r in parcels_all],
    }
    (DATA_DIR / "wv_home_parcels.json").write_text(
        json.dumps(parcels_payload, default=str)
    )
    print(f"[out] wv_home_parcels.json — {len(parcels_all)} parcels county-wide")

    # ----- wv_voters.json (Leaflet voter dots, county-wide) -----
    with engine.begin() as conn:
        voters_all = conn.execute(text(
            "SELECT fn, ln, p, pl, gv, gh, ph, lat, lng, a, yr, reg, pct "
            "FROM voters "
            "WHERE lat IS NOT NULL AND lng IS NOT NULL"
        )).mappings().all()
    party_counter: Counter[str] = Counter()
    lean_counter:  Counter[str] = Counter()
    hh: set[str] = set()
    for v in voters_all:
        party_counter[v["p"] or ""] += 1
        lean_counter[v["pl"] or ""] += 1
        hh.add((v["a"] or "").upper().strip())
    voters_payload = {
        "generated_at": pd.Timestamp.utcnow().isoformat(),
        "center": {"lat": CENTER_LAT, "lng": CENTER_LNG},
        "county": "Delaware",
        "scope": "county-wide",
        "totals": {
            "individuals": len(voters_all),
            "households":  len(hh),
            "by_party":    dict(party_counter),
            "by_lean":     dict(lean_counter),
        },
        "voters": [dict(v) for v in voters_all],
    }
    (DATA_DIR / "wv_voters.json").write_text(json.dumps(voters_payload, default=str))
    print(f"[out] wv_voters.json — {len(voters_all)} voters ({len(hh)} households) county-wide")
    _emit_overlay_geojson(engine,
        """SELECT ncessch, name, street, city, nmcnty, locale,
                  ST_AsGeoJSON(geometry) AS geometry_json
           FROM schools
           WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
        {"bbox": bbox}, DATA_DIR / "wv_schools.json", "schools")

    _emit_overlay_geojson(engine,
        """SELECT t.geoid, t.name,
                  a.hh_income_median, a.home_value_med, a.pct_bachplus,
                  a.pct_owner, a.pop_total,
                  ST_AsGeoJSON(t.geometry) AS geometry_json
           FROM census_tracts t
           LEFT JOIN acs_tract a ON a.geoid = t.geoid
           WHERE ST_Intersects(t.geometry, ST_GeomFromText(:bbox, 4326))""",
        {"bbox": bbox}, DATA_DIR / "wv_tracts.json", "tracts")

    _emit_overlay_geojson(engine,
        """SELECT name20, namelsad20, geoid20, countyfp20,
                  ST_AsGeoJSON(geometry) AS geometry_json
           FROM vtds
           WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
        {"bbox": bbox}, DATA_DIR / "wv_vtds.json", "vtds")

    _emit_overlay_geojson(engine,
        """SELECT geoid, name, lograde, higrade,
                  ST_AsGeoJSON(geometry) AS geometry_json
           FROM school_districts
           WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
        {"bbox": bbox}, DATA_DIR / "wv_districts.json", "districts")

    # A: real filled parcel polygons for the ring — join parcels ↔ parcel_geoms by id.
    # Constrained to the 10 mi Westerville box; the county-wide polygon view is
    # served by the MapLibre `parcels_full` tile source.
    _emit_overlay_geojson(engine,
        """SELECT DISTINCT ON (p.id) p.id,
                  ST_AsGeoJSON(pg.geometry) AS geometry_json
           FROM parcels p
           JOIN parcel_geoms pg ON pg.parcel_no = p.id
           WHERE pg.geometry && ST_GeomFromText(:bbox, 4326)
           ORDER BY p.id, pg.parcel_no""",
        {"bbox": bbox}, DATA_DIR / "wv_parcel_polygons.json", "parcel_polygons")

    # H: counties in the overlay area
    _emit_overlay_geojson(engine,
        """SELECT geoid, name, namelsad,
                  ST_AsGeoJSON(geometry) AS geometry_json
           FROM counties
           WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
        {"bbox": bbox}, DATA_DIR / "wv_counties.json", "counties")

    # I: cities/villages ("places")
    _emit_overlay_geojson(engine,
        """SELECT geoid, name, namelsad,
                  ST_AsGeoJSON(geometry) AS geometry_json
           FROM places
           WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
        {"bbox": bbox}, DATA_DIR / "wv_places.json", "places")

    # J1: congressional districts (OH) — table populated by fetch_congressional_districts.py
    if _has_table(engine, "cong_districts"):
        _emit_overlay_geojson(engine,
            """SELECT geoid, namelsad, cd119fp,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM cong_districts
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_cong.json", "cong_districts")

    # J2: state house (SLDL) and state senate (SLDU)
    if _has_table(engine, "state_house"):
        _emit_overlay_geojson(engine,
            """SELECT geoid, namelsad, sldlst,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM state_house
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_sldl.json", "state_house")
    if _has_table(engine, "state_senate"):
        _emit_overlay_geojson(engine,
            """SELECT geoid, namelsad, sldust,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM state_senate
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_sldu.json", "state_senate")

    # B: townships (Delaware Co)
    if _has_table(engine, "delco_townships"):
        _emit_overlay_geojson(engine,
            """SELECT * , ST_AsGeoJSON(geometry) AS geometry_json
               FROM delco_townships
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_townships.json", "townships")

    # C: subdivisions
    if _has_table(engine, "delco_subdivisions"):
        _emit_overlay_geojson(engine,
            """SELECT * , ST_AsGeoJSON(geometry) AS geometry_json
               FROM delco_subdivisions
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_subdivisions.json", "subdivisions")

    # D: tax districts (join to auditor "descript" via tax_index → parcels.txb where available)
    if _has_table(engine, "delco_tax_districts"):
        _emit_overlay_geojson(engine,
            """SELECT taxdist, NULLIF(TRIM(descript), '') AS descript, tax_index,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM delco_tax_districts
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_tax_districts.json", "tax_districts")

    # E: municipalities (city limits)
    if _has_table(engine, "delco_municipalities"):
        _emit_overlay_geojson(engine,
            """SELECT * , ST_AsGeoJSON(geometry) AS geometry_json
               FROM delco_municipalities
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_municipalities.json", "municipalities")

    # F: ZIP codes
    if _has_table(engine, "delco_zipcodes"):
        _emit_overlay_geojson(engine,
            """SELECT * , ST_AsGeoJSON(geometry) AS geometry_json
               FROM delco_zipcodes
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_zipcodes.json", "zipcodes")

    # G: building outlines
    if _has_table(engine, "delco_buildings"):
        _emit_overlay_geojson(engine,
            """SELECT ST_AsGeoJSON(geometry) AS geometry_json
               FROM delco_buildings
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_buildings.json", "buildings")

    # K: FEMA flood zones
    if _has_table(engine, "fema_flood"):
        _emit_overlay_geojson(engine,
            """SELECT fld_zone, sfha_tf, static_bfe,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM fema_flood
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))
                 AND fld_zone IS NOT NULL AND fld_zone <> 'X'""",
            {"bbox": bbox}, DATA_DIR / "wv_flood.json", "flood_zones")

    # O: OSM points of interest
    if _has_table(engine, "osm_pois"):
        _emit_overlay_geojson(engine,
            """SELECT osm_id, name, category, tags,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM osm_pois
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_pois.json", "osm_pois")

    # OH SoS business filings — matched to parcels by normalized address
    if _has_table(engine, "oh_business_points"):
        _emit_overlay_geojson(engine,
            """SELECT doc_num, business_name, entity_type, trans_desc,
                      effective_date, addr1, city, zip, parcel_addr,
                      ST_AsGeoJSON(geometry) AS geometry_json
               FROM oh_business_points
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))""",
            {"bbox": bbox}, DATA_DIR / "wv_businesses.json", "businesses")

    # Roads (OSM), excluding service (which is mostly driveways/parking)
    if _has_table(engine, "osm_roads"):
        _emit_overlay_geojson(engine,
            """SELECT osm_id, name, road_class, highway,
                      ST_AsGeoJSON(ST_SimplifyPreserveTopology(geometry, 0.00002)) AS geometry_json
               FROM osm_roads
               WHERE ST_Intersects(geometry, ST_GeomFromText(:bbox, 4326))
                 AND road_class <> 'service'
               ORDER BY CASE road_class
                   WHEN 'motorway' THEN 1 WHEN 'trunk' THEN 2 WHEN 'primary' THEN 3
                   WHEN 'secondary' THEN 4 WHEN 'tertiary' THEN 5 WHEN 'residential' THEN 6
                   ELSE 7 END""",
            {"bbox": bbox}, DATA_DIR / "wv_roads.json", "osm_roads")


if __name__ == "__main__":
    main()