#!/usr/bin/env python3
# READY TO DEPLOY — requires base Brain Postgres schema + run schemas/bhi_tables.sql
"""
NSCH — National Survey of Children's Health (HRSA/MCHB).

Source: https://mchb.hrsa.gov/data-research/national-survey-childrens-health
Bulk files by year; we parse state-level indicator tables. Manifest below.
"""
import csv
import io
import logging
import sys
from _common import RateLimitedSession, bulk_insert, job_run

LOG = logging.getLogger("bhi.nsch")

MANIFEST = [
    # (year, url_to_indicator_csv)
    ("2022", "https://mchb.hrsa.gov/sites/default/files/mchb/data-research/nsch/2022/nsch-2022-state-level-indicators.csv"),
]

INDICATORS_OF_INTEREST = {
    "anxiety": "anxiety_pct",
    "depression": "depression_pct",
    "behavioral": "behavioral_pct",
    "mental health treatment": "unmet_mh_treatment_pct",
    "unmet": "unmet_mh_treatment_pct",
}


def test_endpoint():
    s = RateLimitedSession()
    ok = True
    for year, url in MANIFEST:
        r = s.head(url, allow_redirects=True)
        print(f"{year}: {r.status_code}")
        ok = ok and r.status_code in (200, 302)
    return ok


def fetch_rows():
    s = RateLimitedSession(min_interval=0.5)
    out = []
    for year, url in MANIFEST:
        try:
            r = s.get(url)
            r.encoding = "utf-8"
            reader = csv.DictReader(io.StringIO(r.text))
            for row in reader:
                row["_year"] = year
                out.append(row)
        except Exception as e:
            LOG.warning("failed %s: %s", url, e)
    LOG.info("NSCH rows: %d", len(out))
    return out


def write_rows(conn, raw):
    cols = ["geo_type","geo_code","measure","age_bracket","period","value","source"]
    rows = []
    for r in raw:
        indicator = (r.get("Indicator") or "").lower()
        measure = None
        for k, v in INDICATORS_OF_INTEREST.items():
            if k in indicator:
                measure = v
                break
        if not measure:
            continue
        try:
            val = float((r.get("Estimate") or r.get("Value") or "0").replace("%", ""))
        except (TypeError, ValueError):
            continue
        rows.append((
            "state",
            r.get("State"),
            measure,
            "13-17",
            r["_year"],
            val,
            "nsch",
        ))
    bulk_insert(conn, "bhi_demand_indicators", cols, rows)
    return len(rows)


def main():
    with job_run("bhi_nsch") as (conn, _):
        n = write_rows(conn, fetch_rows())
        LOG.info("inserted %d", n)


if __name__ == "__main__":
    if len(sys.argv) > 1 and sys.argv[1] == "test":
        sys.exit(0 if test_endpoint() else 1)
    main()