#!/usr/bin/env python3
"""
Reproduces every figure published in the CSA scoreboard hero + footnote.
=======================================================================
Source: HCD APR Table A2, snapshot downloaded 2026-08-01 from
  https://data.ca.gov/dataset/81b0841f-2802-403e-b48e-2ef4b751f77c/resource/
  fe505d9b-8c36-42ba-ba30-08bc4f34e022/download/tablea2.csv
(The live dataset updates weekly; published figures refer to the archived
snapshot in ./archive/. Verify sha256 against the archive README.)

Published claims reproduced here (completions / CO stage, 2021-2025):
  - "For every 5 expensive homes ... 1 they can afford"  -> 5.00 to 1
  - 422,087 moderate-and-above vs 84,382 very-low/low completions
  - without dedup: 460,136 vs 89,150 (5.16 to 1) — robustness bound
  - moderate-income: 35,635 of 40,327 not deed-restricted
  - 79,042 of the 84,382 affordable completions were rentals (93.7%)
  - fewer than 1 in 380 completions was a deed-restricted lower-income
    home for ownership (computed: 1,306 units = 1 in 388, and a
    project-level audit shows the numerator contains miscoded rentals,
    so the true figure is rarer — hence "fewer than")

Method notes:
  - Income bands: HCD reports acutely-low (0-15% AMI), extremely-low
    (15-30%), very-low (30-50%), low (50-80%) as SEPARATE ADDITIVE
    columns (per the Table A2 data dictionary). "Very-low and low"
    totals here sum all four bands, DR + non-DR — the generous reading.
  - Dedup: jurisdictions re-report the same project in successive annual
    filings until completion. We collapse to the earliest APR year per
    (jurisdiction, tracking ID, APN, CO issue date, unit category) and
    attribute each record to its certificate-of-occupancy year.
  - Ownership stat: owner-tenure rows only, deed-restricted VLI+LI
    columns only, ADUs excluded (not family-purchasable product).
"""

from pathlib import Path

import pandas as pd

HERE = Path(__file__).parent
CSV = HERE / "tablea2.csv"

CO_ALL = ["CO_ACUTELY_LOW_INCOME_DR", "CO_ACUTELY_LOW_INCOME_NDR",
          "CO_EXTREMELY_LOW_INCOME_DR", "CO_EXTREMELY_LOW_INCOME_NDR",
          "CO_VLOW_INCOME_DR", "CO_VLOW_INCOME_NDR",
          "CO_LOW_INCOME_DR", "CO_LOW_INCOME_NDR",
          "CO_MOD_INCOME_DR", "CO_MOD_INCOME_NDR", "CO_ABOVE_MOD_INCOME"]
CO_AFFORD = CO_ALL[:8]                      # VLI+LI, DR and non-DR
CO_AFFORD_DR = ["CO_ACUTELY_LOW_INCOME_DR", "CO_EXTREMELY_LOW_INCOME_DR",
                "CO_VLOW_INCOME_DR", "CO_LOW_INCOME_DR"]


def norm(s):
    return s.astype(str).str.strip().str.upper()


def main():
    df = pd.read_csv(CSV, low_memory=False)
    for c in CO_ALL:
        df[c] = pd.to_numeric(df[c], errors="coerce").fillna(0)
    df["co_units"] = df[CO_ALL].sum(axis=1)
    d = df[df["co_units"] > 0].copy()

    # --- Robustness bound: NO dedup, filtered on APR reporting year -----
    raw = d[(d["YEAR"] >= 2021) & (d["YEAR"] <= 2025)]
    raw_afford = raw[CO_AFFORD].sum().sum()
    raw_exp = raw[["CO_MOD_INCOME_DR", "CO_MOD_INCOME_NDR",
                   "CO_ABOVE_MOD_INCOME"]].sum().sum()
    print("WITHOUT dedup (APR reporting year 2021-2025):")
    print(f"  expensive (mod+above): {raw_exp:>10,.0f}")
    print(f"  affordable (VLI+LI):   {raw_afford:>10,.0f}")
    print(f"  ratio:                 {raw_exp/raw_afford:.2f} to 1")

    # --- Published basis: deduped, attributed to CO issue year ----------
    d["key"] = (norm(d["JURIS_NAME"]) + "|" + norm(d["JURS_TRACKING_ID"]) + "|"
                + norm(d["APN"]) + "|" + d["CO_ISSUE_DT1"].astype(str) + "|"
                + norm(d["UNIT_CAT"]))
    first_year = d.groupby("key")["YEAR"].transform("min")
    dd = d[d["YEAR"] == first_year].copy()
    co_year = pd.to_datetime(dd["CO_ISSUE_DT1"], errors="coerce").dt.year
    dd["ayear"] = co_year.fillna(dd["YEAR"]).astype(int)
    f = dd[(dd["ayear"] >= 2021) & (dd["ayear"] <= 2025)].copy()

    afford = f[CO_AFFORD].sum().sum()
    mod = f[["CO_MOD_INCOME_DR", "CO_MOD_INCOME_NDR"]].sum().sum()
    mod_ndr = f["CO_MOD_INCOME_NDR"].sum()
    amod = f["CO_ABOVE_MOD_INCOME"].sum()
    expensive = mod + amod
    total = f["co_units"].sum()

    print("\nWITH dedup (CO issue year 2021-2025) — PUBLISHED BASIS:")
    print(f"  total completions:     {total:>10,.0f}")
    print(f"  expensive (mod+above): {expensive:>10,.0f}")
    print(f"  affordable (VLI+LI):   {afford:>10,.0f}")
    print(f"  ratio:                 {expensive/afford:.2f} to 1")
    print(f"  moderate not deed-restricted: {mod_ndr:,.0f} of {mod:,.0f}")

    # --- Tenure split of the affordable units ---------------------------
    f["is_owner"] = norm(f["TENURE"]).str.startswith("O")
    f["is_adu"] = f["UNIT_CAT"].astype(str).str.contains("ADU", case=False,
                                                         na=False)
    f["afford_units"] = f[CO_AFFORD].sum(axis=1)
    f["afford_dr"] = f[CO_AFFORD_DR].sum(axis=1)
    rentals = f.loc[~f.is_owner, "afford_units"].sum()
    print(f"\n  affordable completions that were rentals: {rentals:,.0f} "
          f"({rentals/afford:.1%})")

    own_dr = f.loc[f.is_owner & ~f.is_adu, "afford_dr"].sum()
    print(f"  owner-tenure, deed-restricted VLI/LI, excl. ADUs: {own_dr:,.0f}")
    print(f"  => 1 in {total/own_dr:,.0f} of all completions "
          f"(published as 'fewer than 1 in 380'; the numerator is inflated "
          f"by rentals miscoded as Owner — e.g. Villa de Vida (Poway) and "
          f"Las Haciendas (Temecula) are verified rental projects)")

    # --- Moderate-income sensitivity (pre-empts the 'you excluded
    # moderate' critique; both variants are published) -------------------
    own_dr_mod = f.loc[f.is_owner & ~f.is_adu, "CO_MOD_INCOME_DR"].sum()
    own_dr_all = own_dr + own_dr_mod
    max_generous = amod / (afford + mod)   # ALL moderate counted affordable
    print(f"\n  moderate-income sensitivity:")
    print(f"  owner + DR MODERATE excl. ADUs:  {own_dr_mod:,.0f}")
    print(f"  owner + DR VLI/LI/Mod excl ADUs: {own_dr_all:,.0f} "
          f"=> 1 in {total/own_dr_all:,.0f} "
          f"(published as 'still fewer than 1 in 225')")
    print(f"  ratio if ALL moderate (incl. non-deed-restricted) counted "
          f"affordable: {max_generous:.2f} to 1 (published as 3.1 to 1 "
          f"outer bound in 'Where we drew the line')")


if __name__ == "__main__":
    main()
