"""Parse Delaware County OH 2024 general-election canvass (numbered-key
format) and ingest per-precinct presidential results into PostGIS,
joined to the vtds geometry table by precinct name.

Data source: vote.delawarecountyohio.gov/wp-content/uploads/2024/11/Number_Key_Canvass_409-2.txt
Only the "President and Vice President" contest is ingested (columns 01-16;
we keep totals + Harris (col 02) + Trump (col 06)).
"""
import os, re, sys, subprocess
from pathlib import Path
import psycopg2

TXT = Path(__file__).with_suffix("").parent / "delco_canvass_2024.txt"
if not TXT.exists():
    TXT = Path("/tmp/claude-0/-root/3bd9f7db-15ca-4766-89f7-1f0bf937f24d/scratchpad/delco_canvass_2024.txt")

DB = "postgresql://gisapp:14393eacfb20dfc4ed10d3e6e3f0bef5@127.0.0.1:5432/westerville"

def parse():
    """Walk the canvass, capture president rows (16 columns each)."""
    lines = TXT.read_text(errors="ignore").splitlines()
    rows = {}          # precinct_key -> {name, code, cols[16]}
    in_prez = False
    for ln in lines:
        if "President and Vice President" in ln:
            in_prez = True
            continue
        # A section terminates when we hit the next contest header or a page
        # break for a different race. Blank sentinel line "PAGE" resets.
        if in_prez and re.match(r"^[A-Z][a-zA-Z /\.,]+$", ln.strip()) and "President" not in ln and ln.strip() not in {"REPORT"}:
            # Some other contest heading — stop
            in_prez = False
            continue
        if not in_prez: continue
        # precinct rows start with 5-digit code + space + 3-digit num + space + name
        m = re.match(r"^\s*(\d{5})\s+(\d{3})\s+([A-Za-z0-9\-\. /]+?)\s{2,}(.+)$", ln)
        if not m: continue
        code, num, name, rest = m.groups()
        # rest is a stream of integers (16 of them for president, sometimes ".")
        tokens = re.findall(r"\d+|\.", rest)
        if len(tokens) < 6: continue
        try:
            nums = [0 if t == "." else int(t) for t in tokens[:16]]
        except ValueError:
            continue
        key = name.strip().upper()
        # Sum across duplicate rows (some precincts show twice for provisional
        # + election-day; the canvass file's numbered summary section has one
        # row per precinct so first-seen is fine).
        if key in rows: continue
        rows[key] = {
            "code": code, "num": num, "name": name.strip(),
            "harris": nums[1] if len(nums) > 1 else 0,   # col 02
            "trump":  nums[5] if len(nums) > 5 else 0,   # col 06
            "other":  sum(nums) - (nums[1] if len(nums)>1 else 0) - (nums[5] if len(nums)>5 else 0),
            "total":  sum(nums),
        }
    return rows

def main():
    rows = parse()
    if not rows:
        sys.exit("no precinct rows parsed")
    print(f"parsed {len(rows)} precincts")

    with psycopg2.connect(DB) as conn, conn.cursor() as cur:
        cur.execute("""
        DROP MATERIALIZED VIEW IF EXISTS precinct_2024_prez CASCADE;
        DROP TABLE IF EXISTS precinct_2024_raw CASCADE;
        CREATE TABLE precinct_2024_raw (
          code text primary key,
          num  text,
          name text,
          harris int,
          trump  int,
          other  int,
          total  int
        );
        """)
        conn.commit()
        cur.executemany(
            "INSERT INTO precinct_2024_raw (code, num, name, harris, trump, other, total) "
            "VALUES (%(code)s, %(num)s, %(name)s, %(harris)s, %(trump)s, %(other)s, %(total)s)",
            list(rows.values()),
        )
        conn.commit()

        # Build a joined materialized view with geometry.
        cur.execute("""
        CREATE MATERIALIZED VIEW precinct_2024_prez AS
        SELECT
          v.geoid20                                        AS geoid,
          UPPER(v.name20)                                  AS name,
          COALESCE(r.harris, 0)                            AS harris,
          COALESCE(r.trump,  0)                            AS trump,
          COALESCE(r.other,  0)                            AS other,
          COALESCE(r.total,  0)                            AS total,
          CASE WHEN COALESCE(r.total,0) > 0
               THEN (r.harris - r.trump)::float / r.total
               ELSE NULL END                               AS margin_dr,
          CASE WHEN COALESCE(r.total,0) > 0
               THEN 100.0 * r.harris / r.total
               ELSE NULL END                               AS pct_d,
          CASE WHEN COALESCE(r.total,0) > 0
               THEN 100.0 * r.trump  / r.total
               ELSE NULL END                               AS pct_r,
          v.geometry
        FROM vtds v
        LEFT JOIN precinct_2024_raw r ON UPPER(r.name) = UPPER(v.name20)
        WHERE v.countyfp20 = '041';

        CREATE INDEX precinct_2024_prez_gix ON precinct_2024_prez USING gist (geometry);
        CREATE INDEX precinct_2024_prez_geoid_idx ON precinct_2024_prez (geoid);
        """)
        conn.commit()

        cur.execute("SELECT COUNT(*) matched FROM precinct_2024_prez WHERE total > 0")
        matched = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM precinct_2024_prez")
        total_p = cur.fetchone()[0]
        cur.execute("SELECT SUM(harris) h, SUM(trump) t FROM precinct_2024_prez")
        h, t = cur.fetchone()
        print(f"vtds precincts (Delco): {total_p}, joined with results: {matched}")
        print(f"totals from joined view: Harris={h}  Trump={t}  (canvass truth: Harris=61657 Trump=70448)")

if __name__ == "__main__":
    main()