"""Chapter 10 extras: charge-off severity and the lender-reported collateral flag.
Input: raw/7a_enriched.pkl. Output: tables/Tch10_severity_and_collateral.csv.
Charge-off severity = GrossChargeOffAmount / GrossApproval for charged-off loans
(the FOIA dictionary defines GrossChargeOffAmount as "Total loan balance charged off
(includes guaranteed and non-guaranteed portion of loan)").
"""
import os, pandas as pd
HERE = os.path.dirname(os.path.abspath(__file__))
d = pd.read_pickle(os.path.join(HERE, '..', 'raw', '7a_enriched.pkl'))
rows = []
def add(label, g):
    g = g[g['disbursed']]; c = g[g['co']]; sev = c['co_amt'] / c['GrossApproval']
    rows.append(dict(cohort=label, n_disbursed=len(g), n_chargeoff=len(c),
        chargeoff_rate_pct=round(100*len(c)/len(g), 2),
        dollar_loss_rate_pct=round(100*c['co_amt'].sum()/g['GrossApproval'].sum(), 2),
        severity_median_pct=round(100*sev.median(), 1), severity_p25_pct=round(100*sev.quantile(.25), 1),
        severity_p75_pct=round(100*sev.quantile(.75), 1),
        severity_aggregate_pct=round(100*c['co_amt'].sum()/c['GrossApproval'].sum(), 1),
        median_approval_all=g['GrossApproval'].median(), median_approval_chargedoff=c['GrossApproval'].median(),
        collateral_flag_Y_pct=round(100*g['CollateralInd'].eq('Y').mean(), 1)))
m = d[d['FY'].between(2010, 2015) & d['std_term']]
add('FY2010-2015 std term', m)
add('FY2015 std term', m[m['FY'] == 2015])
for k, g in m.groupby('CollateralInd'): add(f'FY2010-2015 std term, collateral flag {k}', g)
add('Change of ownership FY2023-2025 all', d[d['coo'] & d['FY'].between(2023, 2025)])
out = pd.DataFrame(rows)
out.to_csv(os.path.join(HERE, '..', 'tables', 'Tch10_severity_and_collateral.csv'), index=False)
print(out.to_string())