"""Fetch ACS 5-year (2019-2023) demographic vars for all Ohio tracts.

Loads into `acs_tract` joinable to `census_tracts` on geoid. No API key needed
for the volume we pull.
"""
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")
BASE = "https://api.census.gov/data/2023/acs/acs5"
OH = "39"

# (variable, output column, description)
VARS = [
    ("B01003_001E", "pop_total",       "Total population"),
    ("B01002_001E", "age_median",      "Median age"),
    ("B19013_001E", "hh_income_median","Median household income"),
    ("B19301_001E", "per_capita_inc",  "Per-capita income"),
    ("B25077_001E", "home_value_med",  "Median home value"),
    ("B25064_001E", "rent_gross_med",  "Median gross rent"),
    ("B25003_001E", "hh_units_occ",    "Occupied housing units"),
    ("B25003_002E", "hh_owner",        "Owner-occupied units"),
    ("B25003_003E", "hh_renter",       "Renter-occupied units"),
    ("B02001_001E", "race_total",      "Race total"),
    ("B02001_002E", "race_white",      "White alone"),
    ("B02001_003E", "race_black",      "Black alone"),
    ("B02001_004E", "race_ainative",   "American Indian alone"),
    ("B02001_005E", "race_asian",      "Asian alone"),
    ("B03003_003E", "eth_hispanic",    "Hispanic or Latino"),
    ("B15003_001E", "edu_total_25p",   "Educ base (25+)"),
    ("B15003_022E", "edu_bachelors",   "Bachelor's"),
    ("B15003_023E", "edu_masters",     "Master's"),
    ("B15003_024E", "edu_prof",        "Professional degree"),
    ("B15003_025E", "edu_doctorate",   "Doctorate"),
    ("B08303_001E", "commute_total",   "Commute base"),
    ("B23025_005E", "unemployed",      "Unemployed"),
    ("B23025_002E", "labor_force",     "Labor force"),
    ("B17001_002E", "below_poverty",   "Below poverty"),
    ("B17001_001E", "poverty_total",   "Poverty base"),
]


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


def _read_gispass() -> str:
    return _env("GISPASS")


def main() -> None:
    key = _env("CENSUS_KEY")
    # API allows up to 50 vars per request; we're at 25 — one call is fine
    var_list = ",".join(v[0] for v in VARS)
    url = f"{BASE}?get=NAME,{var_list}&for=tract:*&in=state:{OH}&key={key}"
    print(f"[api] {url[:120]}...")
    r = requests.get(url, timeout=60)
    r.raise_for_status()
    rows = r.json()
    header, data = rows[0], rows[1:]
    df = pd.DataFrame(data, columns=header)
    print(f"[api] {len(df)} tracts")

    # normalize
    df["geoid"] = df["state"] + df["county"] + df["tract"]
    df = df.rename(columns={"NAME": "name"})
    for census_var, out_col, _ in VARS:
        df[out_col] = pd.to_numeric(df[census_var], errors="coerce")
        df = df.drop(columns=[census_var])
    # derived %
    df["pct_owner"]     = (df["hh_owner"] / df["hh_units_occ"] * 100).round(2)
    df["pct_bachplus"]  = ((df["edu_bachelors"] + df["edu_masters"] + df["edu_prof"] + df["edu_doctorate"]) / df["edu_total_25p"] * 100).round(2)
    df["pct_hispanic"]  = (df["eth_hispanic"]  / df["race_total"] * 100).round(2)
    df["pct_white"]     = (df["race_white"]    / df["race_total"] * 100).round(2)
    df["pct_black"]     = (df["race_black"]    / df["race_total"] * 100).round(2)
    df["pct_asian"]     = (df["race_asian"]    / df["race_total"] * 100).round(2)
    df["pct_poverty"]   = (df["below_poverty"] / df["poverty_total"] * 100).round(2)
    df["pct_unemployed"] = (df["unemployed"]   / df["labor_force"]   * 100).round(2)

    # drop the raw state/county/tract cols
    df = df.drop(columns=["state", "county", "tract"])
    cols = ["geoid", "name"] + [c for c in df.columns if c not in ("geoid", "name")]
    df = df[cols]

    engine = create_engine(
        f"postgresql+psycopg2://gisapp:{_read_gispass()}@127.0.0.1:5432/westerville"
    )
    df.to_sql("acs_tract", engine, if_exists="replace", index=False)
    with engine.begin() as conn:
        conn.execute(text("ALTER TABLE acs_tract ADD PRIMARY KEY (geoid);"))
        (n,) = conn.execute(text("SELECT count(*) FROM acs_tract")).one()
    print(f"[postgis] acs_tract: {n} rows, {len(df.columns)} cols")


if __name__ == "__main__":
    main()