"""Extra analyses: mix-adjusted lender loss, franchise brands by normalised name, change-of-ownership
size-adjusted early loss, DSCR price-ceiling tables, lender rate normalization, term-field trap evidence.
Input: raw/7a_enriched.pkl written by 02_analyse.py."""
import os, re, numpy as np, pandas as pd
HERE = os.path.dirname(os.path.abspath(__file__))
RAW = os.path.join(HERE, '..', 'raw'); OUT = os.path.join(HERE, '..', 'tables')
d = pd.read_pickle(os.path.join(RAW, '7a_enriched.pkl'))
def save(df, name):
    df.to_csv(os.path.join(OUT, name), index=False); print(f'wrote {name} rows={len(df)}')

# ---- T08d lender observed vs expected (mix-adjusted for size band x sector x FY), matured std term ----
m = d[d['FY'].between(2010, 2015) & d['std_term'] & d['disbursed']].copy()
m['cell'] = m['size_band'].astype(str) + '|' + m['sector'].fillna('NA') + '|' + m['FY'].astype(str)
m['exp'] = m.groupby('cell')['co'].transform('mean')
g = m.groupby('BankName')
t = pd.DataFrame({'n_disbursed': g.size(), 'observed_co': g['co'].sum(), 'expected_co': g['exp'].sum().round(1), 'median_loan_usd': g['GrossApproval'].median()})
t = t[t['n_disbursed'] >= 500]
t['observed_rate_pct'] = (100 * t['observed_co'] / t['n_disbursed']).round(2)
t['expected_rate_pct'] = (100 * t['expected_co'] / t['n_disbursed']).round(2)
t['obs_to_exp_ratio'] = (t['observed_co'] / t['expected_co']).round(2)
# Poisson-ish 95% band on ratio using sqrt(observed)
t['ratio_ci95_low'] = ((t['observed_co'] - 1.96 * np.sqrt(t['observed_co'])).clip(lower=0) / t['expected_co']).round(2)
t['ratio_ci95_high'] = ((t['observed_co'] + 1.96 * np.sqrt(t['observed_co'])) / t['expected_co']).round(2)
t = t.sort_values('obs_to_exp_ratio').reset_index()
t.insert(0, 'note', 'FY2010-2015 std term; expected = cohort rate for same size band x sector x FY; BankName = current assignee')
save(t, 'T08d_lender_obs_vs_expected_matured_n500.csv')

# ---- T07b franchise by normalised name ----
def norm(s):
    s = str(s).upper()
    s = re.sub(r"[^A-Z0-9 ]", "", s)
    s = re.sub(r"\b(INC|LLC|CORP|CORPORATION|CO|THE|SANDWICH SHOPS?|FRANCHISING|SYSTEMS?|INTERNATIONAL)\b", "", s)
    s = s.replace('CEASARS', 'CAESAR').replace('CAESARS', 'CAESAR').replace('SPORTS CLIPS', 'SPORT CLIPS').replace('DUNKIN DONUTS', 'DUNKIN')
    return re.sub(r"\s+", " ", s).strip()
f = d[d['franchise'] & d['FY'].between(2010, 2015) & d['disbursed']].copy()
f['brand_norm'] = f['FranchiseName'].map(norm)
g = f.groupby('brand_norm')
fb = pd.DataFrame({'n_disbursed': g.size(), 'n_chargeoff': g['co'].sum(), 'n_codes': g['FranchiseCode'].nunique(), 'median_loan_usd': g['GrossApproval'].median(),
                   'dollar_loss_rate_pct': (100 * g['co_amt'].sum() / g['GrossApproval'].sum()).round(2)})
fb = fb[fb['n_disbursed'] >= 100]
fb['chargeoff_rate_pct'] = (100 * fb['n_chargeoff'] / fb['n_disbursed']).round(2)
def wil(k, n, z=1.96):
    p = k / n; den = 1 + z * z / n; c = (p + z * z / (2 * n)) / den; h = z * np.sqrt(p * (1 - p) / n + z * z / (4 * n * n)) / den
    return round(100 * (c - h), 2), round(100 * (c + h), 2)
fb['ci95_low_pct'] = [wil(k, n)[0] for k, n in zip(fb['n_chargeoff'], fb['n_disbursed'])]
fb['ci95_high_pct'] = [wil(k, n)[1] for k, n in zip(fb['n_chargeoff'], fb['n_disbursed'])]
save(fb.sort_values('chargeoff_rate_pct', ascending=False).reset_index(), 'T07b_franchise_brand_normalised_matured_n100.csv')
# franchise vs non-franchise within size band, matured std term
mm = d[d['FY'].between(2010, 2015) & d['std_term'] & d['disbursed']]
x = mm.groupby(['size_band', 'franchise'], observed=True).agg(n=('co', 'size'), chargeoffs=('co', 'sum')).reset_index()
x['chargeoff_rate_pct'] = (100 * x['chargeoffs'] / x['n']).round(2)
save(x, 'T07c_franchise_vs_independent_by_size_matured.csv')

# ---- T12b change of ownership vs existing, within size band, 36m horizon, FY2018-2022 pooled ----
e = d[d['FY'].between(2018, 2022) & d['std_term'] & d['disbursed'] & d['BusinessAge'].isin(['Change of Ownership', 'Existing or more than 2 years old', 'Startup, Loan Funds will Open Business', 'New Business or 2 years or less'])].copy()
e['co36'] = e['co'] & (e['months_to_co'] <= 36)
x = e.groupby(['size_band', 'BusinessAge'], observed=True).agg(n=('co36', 'size'), chargeoffs_36m=('co36', 'sum')).reset_index()
x['chargeoff_36m_pct'] = (100 * x['chargeoffs_36m'] / x['n']).round(2)
x['ci95_low'] = [wil(k, n)[0] for k, n in zip(x['chargeoffs_36m'], x['n'])]; x['ci95_high'] = [wil(k, n)[1] for k, n in zip(x['chargeoffs_36m'], x['n'])]
save(x, 'T12b_coo_vs_others_36m_by_size_FY2018_2022.csv')
# by vintage excluding the CARES-subsidy years (FY2018-2019 only) and the 2022 vintage separately
x2 = e.groupby(['FY', 'BusinessAge']).agg(n=('co36', 'size'), chargeoffs_36m=('co36', 'sum'), median_usd=('GrossApproval', 'median')).reset_index()
x2['chargeoff_36m_pct'] = (100 * x2['chargeoffs_36m'] / x2['n']).round(2)
save(x2, 'T12c_coo_vs_others_36m_by_vintage.csv')
# CoO early loss by sector FY2018-2022, 36m, n>=200
c = e[e['BusinessAge'] == 'Change of Ownership']
x3 = c.groupby('sector').agg(n=('co36', 'size'), chargeoffs_36m=('co36', 'sum')).reset_index()
x3 = x3[x3['n'] >= 200]; x3['chargeoff_36m_pct'] = (100 * x3['chargeoffs_36m'] / x3['n']).round(2)
x3['ci95_low'] = [wil(k, n)[0] for k, n in zip(x3['chargeoffs_36m'], x3['n'])]; x3['ci95_high'] = [wil(k, n)[1] for k, n in zip(x3['chargeoffs_36m'], x3['n'])]
save(x3.sort_values('chargeoff_36m_pct', ascending=False), 'T12d_coo_36m_chargeoff_by_sector_FY2018_2022.csv')

# ---- T15 DSCR price ceilings ----
def pv(pmt_m, r, n):
    i = r / 12; return pmt_m * (1 - (1 + i) ** -n) / i
rows = []
for dscr in (1.15, 1.25, 1.5):
    for years in (10, 25):
        for rate in (6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0):
            loan = pv(100_000 / dscr / 12, rate / 100, years * 12)
            rows.append({'dscr': dscr, 'term_years': years, 'rate_pct': rate, 'max_loan_per_100k_cash_available_for_debt_service': round(loan),
                         'implied_price_at_10pct_equity_no_seller_note': round(loan / 0.9), 'annual_debt_service_per_1m_loan': round(1_000_000 / pv(1, rate / 100, years * 12) * 12)})
save(pd.DataFrame(rows), 'T15_dscr_price_ceiling_grid.csv')

# ---- T16 lender rate normalization (timing confound) ----
pr = pd.read_csv(os.path.join(RAW, 'prime_DPRIME.csv'))
rows = []
coo = d[d['coo'] & (d['FY'] >= 2019)]
for bank, lab in [('PNC Bank, National Association', 'PNC'), ('Live Oak Banking Company', 'Live Oak'), ('First Internet Bank of Indiana', 'First Internet')]:
    x = coo[coo['BankName'] == bank]
    sp = x['spread'].median(); rate = 7.00 + sp
    loan = pv(580_000 / 1.25 / 12, rate / 100, 120)
    rows.append({'case': f'{lab}: median CoO spread over prime ({sp:.2f}) + prime 7.00 (FRED DPRIME 2026-09-30), n={len(x)}, fixed share {100*(x["FixedorVariableInterestInd"]=="F").mean():.0f}%, median approval {x["ApprovalDate"].median().date()}',
                 'rate_pct': round(rate, 2), 'max_debt_recomputed': round(loan), 'max_price_recomputed_10pct_equity': round(loan / 0.9), 'basis': 'spread-normalised to one prime date'})
save(pd.DataFrame(rows), 'T16_lender_rate_normalization.csv')

# ---- T17 term field trap evidence ----
r = d[d['FY'].between(2010, 2015) & ~d['revolver']]
rows = []
for st, g in r.groupby('LoanStatus'):
    t = g['TermInMonths']
    rows.append({'status': st, 'n': len(g), 'share_term_in_standard_set_pct': round(100 * t.isin([60, 84, 120, 180, 240, 300]).mean(), 1), 'median_term': t.median(),
                 'top_terms': '; '.join(f'{int(k)}:{v}' for k, v in t.value_counts().head(5).items())})
save(pd.DataFrame(rows), 'T17_term_field_trap_by_status_FY2010_2015.csv')

# ---- T18 CoO share of loans by size band (all FY2023-2025) ----
w = d[d['FY'].between(2023, 2025)]
x = w.groupby('size_band', observed=True).agg(n=('coo', 'size'), n_coo=('coo', 'sum')).reset_index()
x['coo_share_pct'] = (100 * x['n_coo'] / x['n']).round(1)
save(x, 'T18_coo_share_by_size_FY2023_2025.csv')
print('done')