"""Fetch CDC PLACES health measures at the census-tract level for Ohio.

Uses the Socrata Open Data API. Loads all measures for OH tracts into
`cdc_places` keyed by tract geoid.
"""
from __future__ import annotations

from pathlib import Path
import pandas as pd
import requests
from sqlalchemy import create_engine, text

DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com")
# 2024 release; census-tract level
URL = "https://data.cdc.gov/resource/cwsq-ngmh.json"
OH_FIPS = "39"
# a subset of key measures to keep the row width sane
MEASURES = [
    "BPHIGH", "OBESITY", "DIABETES", "CSMOKING", "MENTHLTH",
    "ACCESS2", "COREM", "COLON_SCREEN", "LPA", "SLEEP",
]


def _env(k):
    for line in (DATA_DIR / ".env.gis").read_text().splitlines():
        if line.startswith(f"{k}="):
            return line.split("=", 1)[1].strip()
    raise SystemExit(f"{k} missing")


def main():
    print("[cdc] querying PLACES tract data for OH")
    all_rows = []
    off = 0
    while True:
        r = requests.get(
            URL, params={
                "stateabbr": "OH",
                "$limit": 50000, "$offset": off,
                "$select": "locationid,measureid,data_value,totalpopulation",
            }, timeout=60)
        r.raise_for_status()
        batch = r.json()
        if not batch: break
        all_rows.extend(batch)
        off += len(batch)
        if len(batch) < 50000: break
    print(f"[cdc] {len(all_rows)} measure rows")
    df = pd.DataFrame(all_rows)
    df["data_value"] = pd.to_numeric(df["data_value"], errors="coerce")
    df["totalpopulation"] = pd.to_numeric(df["totalpopulation"], errors="coerce")
    # pivot: one row per tract
    pop = df.groupby("locationid")["totalpopulation"].first().reset_index()
    piv = df.pivot_table(index="locationid", columns="measureid",
                          values="data_value", aggfunc="first").reset_index()
    piv = piv.merge(pop, on="locationid", how="left")
    piv = piv.rename(columns={"locationid": "geoid"})
    piv.columns = [c.lower() for c in piv.columns]
    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_env('GISPASS')}@127.0.0.1:5432/westerville"
    )
    piv.to_sql("cdc_places", engine, if_exists="replace", index=False)
    with engine.begin() as conn:
        conn.execute(text("ALTER TABLE cdc_places ADD PRIMARY KEY (geoid);"))
        (n,) = conn.execute(text("SELECT count(*) FROM cdc_places")).one()
    print(f"[postgis] cdc_places: {n} tracts, {len(piv.columns)-1} measures")


if __name__ == "__main__":
    main()