#!/usr/bin/env python3
"""
Deduplicated pipeline numbers for the /scoreboard page, 2021-2025.
For each stage (entitled, permitted, built) computes on the same deduped
basis as scoreboard_footnote_numbers.py:
  - VLI+LI (acutely+extremely+vlow+low, DR and non-DR)
  - moderate (total and non-DR), above-moderate, ratio, total units
  - tenure split of the VLI+LI units (renter share)
  - owner-tenure deed-restricted VLI/LI excl. ADUs -> "1 in N"
Snapshot: tablea2.csv downloaded 2026-08-01 (sha256 in archive README).
"""

from pathlib import Path
import pandas as pd

HERE = Path(__file__).parent

STAGES = {
    "ENTITLED": ("", "ENT_APPROVE_DT1"),
    "PERMITTED": ("BP_", "BP_ISSUE_DT1"),
    "BUILT": ("CO_", "CO_ISSUE_DT1"),
}
BANDS = ["ACUTELY_LOW_INCOME", "EXTREMELY_LOW_INCOME", "VLOW_INCOME",
         "LOW_INCOME", "MOD_INCOME"]


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


def main():
    raw = pd.read_csv(HERE / "tablea2.csv", low_memory=False)
    for prefix, datecol in STAGES.values():
        cols = ([f"{prefix}{b}_DR" for b in BANDS]
                + [f"{prefix}{b}_NDR" for b in BANDS]
                + [f"{prefix}ABOVE_MOD_INCOME"])
        for c in cols:
            raw[c] = pd.to_numeric(raw[c], errors="coerce").fillna(0)

    for stage, (prefix, datecol) in STAGES.items():
        afford_cols = [f"{prefix}{b}_{k}" for b in BANDS[:4]
                       for k in ("DR", "NDR")]
        afford_dr_cols = [f"{prefix}{b}_DR" for b in BANDS[:4]]
        mod_cols = [f"{prefix}MOD_INCOME_DR", f"{prefix}MOD_INCOME_NDR"]
        amod_col = f"{prefix}ABOVE_MOD_INCOME"
        all_cols = afford_cols + mod_cols + [amod_col]

        d = raw.copy()
        d["units"] = d[all_cols].sum(axis=1)
        d = d[d["units"] > 0]
        d = d.assign(key=(norm(d["JURIS_NAME"]) + "|"
                          + norm(d["JURS_TRACKING_ID"]) + "|" + norm(d["APN"])
                          + "|" + d[datecol].astype(str) + "|"
                          + norm(d["UNIT_CAT"])))
        first = d.groupby("key")["YEAR"].transform("min")
        d = d[d["YEAR"] == first].copy()
        stage_year = pd.to_datetime(d[datecol], errors="coerce").dt.year
        d["ayear"] = stage_year.fillna(d["YEAR"]).astype(int)
        f = d[(d["ayear"] >= 2021) & (d["ayear"] <= 2025)].copy()

        afford = f[afford_cols].sum().sum()
        mod = f[mod_cols].sum().sum()
        mod_ndr = f[f"{prefix}MOD_INCOME_NDR"].sum()
        amod = f[amod_col].sum()
        total = f["units"].sum()
        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["aff_u"] = f[afford_cols].sum(axis=1)
        f["aff_dr"] = f[afford_dr_cols].sum(axis=1)
        renter_aff = f.loc[~f.is_owner, "aff_u"].sum()
        own_dr = f.loc[f.is_owner & ~f.is_adu, "aff_dr"].sum()
        mod_dr = mod - mod_ndr
        strict_ratio = (mod + amod - mod_dr) / (afford + mod_dr)

        print(f"=== {stage} (deduped, {datecol[:-1].lower()} year 2021-2025) ===")
        print(f"  total units:              {total:>10,.0f}")
        print(f"  VLI+LI (DR+NDR):          {afford:>10,.0f}")
        print(f"  moderate (total / NDR):   {mod:>10,.0f} / {mod_ndr:,.0f}"
              f"  ({mod_ndr/mod:.0%} not deed-restricted)")
        print(f"  above-moderate:           {amod:>10,.0f}")
        print(f"  ratio (mod+above):VLI+LI  {(mod+amod)/afford:>10.2f} to 1")
        print(f"  stricter (DR-mod moved to affordable side): "
              f"{strict_ratio:.2f} to 1")
        print(f"  VLI+LI that are rentals:  {renter_aff:>10,.0f}"
              f"  ({renter_aff/afford:.1%})")
        print(f"  owner + DR VLI/LI excl ADU: {own_dr:>8,.0f}"
              f"   = 1 in {total/own_dr:,.0f} of all units")
        print()


if __name__ == "__main__":
    main()
