#!/usr/bin/env python3
"""Rigor checks for the APR ownership x affordability stat before publication."""

import pandas as pd
from pathlib import Path

HERE = Path(__file__).parent
INC = ['BP_ACUTELY_LOW_INCOME_DR', 'BP_ACUTELY_LOW_INCOME_NDR',
       'BP_EXTREMELY_LOW_INCOME_DR', 'BP_EXTREMELY_LOW_INCOME_NDR',
       'BP_VLOW_INCOME_DR', 'BP_VLOW_INCOME_NDR',
       'BP_LOW_INCOME_DR', 'BP_LOW_INCOME_NDR',
       'BP_MOD_INCOME_DR', 'BP_MOD_INCOME_NDR', 'BP_ABOVE_MOD_INCOME']

df = pd.read_csv(HERE / 'tablea2.csv', low_memory=False)
for c in INC:
    df[c] = pd.to_numeric(df[c], errors='coerce').fillna(0)
df['bp_units'] = df[INC].sum(axis=1)
df = df[df['bp_units'] > 0].copy()

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

# Three candidate dedup keys, weakest to strongest
df['key_nojur'] = (norm(df['JURS_TRACKING_ID']) + '|' + norm(df['APN']) + '|'
                   + df['BP_ISSUE_DT1'].astype(str) + '|' + norm(df['UNIT_CAT']))
df['key_jur'] = norm(df['JURIS_NAME']) + '|' + df['key_nojur']
# id-quality flags
df['blank_apn'] = norm(df['APN']).isin(['NAN', '', ' ', 'N/A', 'NA', '0'])
df['blank_track'] = norm(df['JURS_TRACKING_ID']).isin(['NAN', '', ' ', '" "', 'N/A', 'NA', '0'])

print("=" * 70)
print("CHECK 1: dedup-key integrity")
print("=" * 70)
print(f"Rows with blank/placeholder APN:          {df['blank_apn'].sum():>8,}")
print(f"Rows with blank/placeholder tracking ID:  {df['blank_track'].sum():>8,}")
print(f"Rows with BOTH blank:                     {(df['blank_apn'] & df['blank_track']).sum():>8,}")

# How much does adding jurisdiction to the key change the dedup?
d_nojur = df.drop_duplicates(subset='key_nojur')
d_jur = df.drop_duplicates(subset='key_jur')
print(f"\nDeduped units, key WITHOUT jurisdiction:  {d_nojur['bp_units'].sum():>12,.0f}")
print(f"Deduped units, key WITH jurisdiction:     {d_jur['bp_units'].sum():>12,.0f}")
print(f"Difference (units falsely merged across jurisdictions by the old key): "
      f"{d_jur['bp_units'].sum() - d_nojur['bp_units'].sum():,.0f}")

# Within-year same-key multiplicity: are we collapsing genuinely distinct
# units reported as separate same-day rows?
g = df.groupby(['key_jur', 'YEAR']).agg(n_rows=('bp_units', 'size'),
                                        units=('bp_units', 'sum'))
within = g[g.n_rows > 1]
print(f"\nSame-key groups appearing >1x WITHIN a single APR year: {len(within):,}")
print(f"Units in those groups: {within.units.sum():,.0f} "
      f"(units that WOULD be dropped by within-year collapse: "
      f"{(within.units - within.units / within.n_rows).sum():,.0f} if rows are equal-sized)")
# The safer dedup: collapse across years only (keep the earliest year's rows
# intact, including within-year multiplicity)
first_year = df.groupby('key_jur')['YEAR'].transform('min')
d_crossyear = df[df['YEAR'] == first_year]
print(f"\nDedup strategy comparison (statewide units, all years):")
print(f"  A. No dedup:                       {df['bp_units'].sum():>12,.0f}")
print(f"  B. Cross-year only (keep within):  {d_crossyear['bp_units'].sum():>12,.0f}")
print(f"  C. Full row-collapse (with jur):   {d_jur['bp_units'].sum():>12,.0f}")
print(f"  D. Full row-collapse (no jur, as published): {d_nojur['bp_units'].sum():>12,.0f}")

# ---- Use strategy B (cross-year, jurisdiction-aware) as the conservative
# base for everything below, and compare C for sensitivity. ----
def analyze(frame, label):
    f = frame.copy()
    bp_year = pd.to_datetime(f['BP_ISSUE_DT1'], errors='coerce').dt.year
    f['ayear'] = bp_year.fillna(f['YEAR']).astype(int)
    f = f[(f['ayear'] >= 2018) & (f['ayear'] <= 2025)]
    f['is_owner'] = f['TENURE'].astype(str).str.strip().str.upper().str.startswith('O')
    f['is_adu'] = f['UNIT_CAT'].astype(str).str.contains('ADU', case=False, na=False)
    f['vli_li_dr'] = (f['BP_ACUTELY_LOW_INCOME_DR'] + f['BP_EXTREMELY_LOW_INCOME_DR']
                      + f['BP_VLOW_INCOME_DR'] + f['BP_LOW_INCOME_DR'])
    f['mod_dr'] = f['BP_MOD_INCOME_DR']
    total = f['bp_units'].sum()
    oa = f.loc[f.is_owner & ~f.is_adu, 'vli_li_dr'].sum()
    oa_mod = f.loc[f.is_owner & ~f.is_adu, 'mod_dr'].sum()
    ra = f.loc[~f.is_owner, 'vli_li_dr'].sum()
    renter_total = f.loc[~f.is_owner, 'bp_units'].sum()
    owner_total = f.loc[f.is_owner, 'bp_units'].sum()
    print(f"\n[{label}] 2018-2025")
    print(f"  Total units:                          {total:>12,.0f}")
    print(f"  Owner + DR VLI/LI (excl ADU):         {oa:>12,.0f}   1 in {total/oa:,.0f} of all units")
    print(f"      as share of owner units:          {oa/owner_total:.3%}  (1 in {owner_total/oa:,.0f})")
    print(f"  Owner + DR MODERATE (excl ADU):       {oa_mod:>12,.0f}")
    print(f"  Owner + DR VLI/LI/Mod (excl ADU):     {oa+oa_mod:>12,.0f}   1 in {total/(oa+oa_mod):,.0f} of all units")
    print(f"  Renter + DR VLI/LI (any):             {ra:>12,.0f}   = {ra/renter_total:.2%} of renter units")
    return f

fB = analyze(d_crossyear, 'Strategy B: cross-year dedup, jurisdiction-aware')
fC = analyze(d_jur, 'Strategy C: full collapse, jurisdiction-aware')
fA = analyze(df, 'Strategy A: no dedup (upper bound denominator)')

print("\n" + "=" * 70)
print("CHECK 2: statewide totals by year under strategy B (vs HCD dashboard)")
print("=" * 70)
print(fB.groupby('ayear')['bp_units'].sum().to_string(float_format=lambda x: f"{x:,.0f}"))

print("\n" + "=" * 70)
print("CHECK 3: tenure plausibility crosstab (strategy B, units)")
print("=" * 70)
ct = fB.pivot_table(index='UNIT_CAT', columns='TENURE', values='bp_units',
                    aggfunc='sum', fill_value=0)
ct['pct_owner'] = ct.get('Owner', 0) / ct.sum(axis=1)
print(ct.to_string(float_format=lambda x: f"{x:,.2f}"))

print("\n" + "=" * 70)
print("CHECK 4: numerator audit — top jurisdictions & projects (strategy B)")
print("=" * 70)
num = fB[fB.is_owner & ~fB.is_adu & (fB.vli_li_dr > 0)].copy()
print(f"Rows contributing to numerator: {len(num):,}; units: {num.vli_li_dr.sum():,.0f}")
print("\nTop 15 jurisdictions by numerator units:")
print(num.groupby('JURIS_NAME')['vli_li_dr'].sum().sort_values(ascending=False)
      .head(15).to_string(float_format=lambda x: f"{x:,.0f}"))
print("\nTop 20 largest single rows (project-level, for public-record spot checks):")
top = num.nlargest(20, 'vli_li_dr')[['JURIS_NAME', 'CNTY_NAME', 'ayear',
                                     'PROJECT_NAME', 'STREET_ADDRESS', 'UNIT_CAT',
                                     'vli_li_dr', 'bp_units', 'DR_TYPE', 'TERM_AFF_DR']]
with pd.option_context('display.width', 250, 'display.max_colwidth', 40):
    print(top.to_string())

print("\nNumerator by unit category:")
print(num.groupby('UNIT_CAT')['vli_li_dr'].sum().to_string(float_format=lambda x: f"{x:,.0f}"))
print("\nNumerator by DR_TYPE:")
print(num.groupby(num['DR_TYPE'].astype(str))['vli_li_dr'].sum()
      .sort_values(ascending=False).to_string(float_format=lambda x: f"{x:,.0f}"))

# Suspicious: numerator rows where DR units exceed plausible row size or
# tenure conflicts with category
print("\nNumerator rows with vli_li_dr > 50 in a single row (verify these):")
big = num[num.vli_li_dr > 50][['JURIS_NAME', 'ayear', 'PROJECT_NAME', 'UNIT_CAT',
                               'TENURE', 'vli_li_dr', 'bp_units']]
with pd.option_context('display.width', 220, 'display.max_colwidth', 40):
    print(big.to_string() if len(big) else "  (none)")

print("\n" + "=" * 70)
print("CHECK 5: high-growth counties, strategy B")
print("=" * 70)
HG = {"LOS ANGELES", "ORANGE", "SAN DIEGO", "ALAMEDA", "CONTRA COSTA", "MARIN",
      "NAPA", "SAN FRANCISCO", "SAN MATEO", "SANTA CLARA", "SOLANO", "SONOMA"}
hgB = fB[norm(fB['CNTY_NAME']).isin(HG)]
total = hgB['bp_units'].sum()
oa = hgB.loc[hgB.is_owner & ~hgB.is_adu, 'vli_li_dr'].sum()
print(f"HG total: {total:,.0f}; owner+DR VLI/LI excl ADU: {oa:,.0f}; 1 in {total/oa:,.0f}")
