#!/usr/bin/env python3
"""All figures for "Buying a Business with an SBA Loan".
Visual system copied from frontdoor_reference_charts.py: Source Sans 3, 4.6in wide, blue primary,
amber secondary, gray context, direct labels, light gridlines, left-aligned bold title.
Identity never relies on color alone (labels, marker shapes, hatching).
Reads only files under research/. Derived tables (T19, T20, T21, fee schedule) are written first.
Run: python3 tools/charts/make_charts.py   (writes figures/*.png at 300 dpi)"""
import csv, math, pathlib, re, textwrap
import numpy as np
import pandas as pd
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
from matplotlib import font_manager as fm
from matplotlib.ticker import FuncFormatter

ROOT = pathlib.Path(__file__).resolve().parents[2]
R = ROOT / "research"; T = R / "data/tables"; RULES = R / "rules"; SRC = RULES / "src"
OUT = ROOT / "figures"; OUT.mkdir(exist_ok=True)
for f in (ROOT / "tools/fonts").glob("SourceSans3-*.otf"):
    fm.fontManager.addfont(str(f))
BLUE, AMBER, GRAY, INK, MUTED, GRID = "#2a5aa8", "#d39b2a", "#9aa0a8", "#1d1d1f", "#5f6368", "#e3e5e8"
PALE = "#d9dce0"; LBLUE = "#a9bfe3"; DARK = "#3c4043"
plt.rcParams.update({"font.family": "Source Sans 3", "font.size": 8.5, "axes.edgecolor": GRID,
    "axes.labelcolor": MUTED, "xtick.color": MUTED, "ytick.color": MUTED, "axes.spines.top": False,
    "axes.spines.right": False, "axes.grid": True, "grid.color": GRID, "grid.linewidth": 0.6,
    "axes.axisbelow": True, "text.color": INK, "savefig.dpi": 300, "lines.linewidth": 2,
    "lines.solid_capstyle": "round", "hatch.linewidth": 0.6, "text.parse_math": False})
W = 4.6


def fig(h=2.7):
    f, ax = plt.subplots(figsize=(W, h)); return f, ax


def save(f, name, title=None, foot=None, title_ax=None):
    h = f.get_figheight(); top = 1
    if title:  # left-aligned bold title at the figure edge, so long titles never clip
        f.text(0.012, 1 - 0.07 / h, title, ha="left", va="top", fontsize=9.5, fontweight="semibold", color=INK, linespacing=1.15)
        top = 1 - (0.03 + 0.165 * (title.count("\n") + 1)) / h
    bottom = 0
    if foot:
        lines = textwrap.fill(foot, 118)
        n = lines.count("\n") + 1
        f.text(0.012, 0.012, lines, fontsize=6.3, color=MUTED, ha="left", va="bottom", linespacing=1.25)
        bottom = (0.11 * n + 0.06) / f.get_figheight()
    f.tight_layout(rect=(0, bottom, 1, top))
    f.savefig(OUT / name, facecolor="white"); plt.close(f); print("wrote", name)


def money(v):
    return f"${v/1e6:.2f}M" if v >= 1e6 else f"${v/1e3:,.0f}k"


def usd(v):
    return f"${v:,.0f}"


def wilson(k, n, z=1.96):
    if n == 0: return (0, 0)
    p = k / n; d = 1 + z * z / n
    c = (p + z * z / (2 * n)) / d; h = z * math.sqrt(p * (1 - p) / n + z * z / (4 * n * n)) / d
    return max(0, c - h), c + h


# ------------------------------------------------------------------ derived tables
def build_fee_schedule():
    """Upfront fee for loans >12 months, from the FY2025-FY2027 notices in research/rules/src.
    FY2025 has two periods: Notice 5000-858936 (fees25.txt, zero fee on loans of $1M or less) applied from
    1 Oct 2024; Notice 5000-865775 (fees25b.txt, effective 24 Mar 2025) superseded it and restored the tiers
    for delegated loans approved 27 Mar to 30 Sep 2025.
    fees24.pdf in the research files is an SBA 'Page not found' HTML page, so FY2024 cannot be sourced."""
    t24 = (SRC / "fees24.pdf").read_bytes()[:20000].decode("utf-8", "ignore")
    if "Page not found" not in t24:
        raise SystemExit("fees24.pdf now holds real content: add its schedule to build_fee_schedule()")
    def ctrl(fn): return re.search(r"CONTROL NO\.:\s*([\d-]+)", (SRC / fn).read_text()).group(1)
    t25, t26, t27 = [(SRC / f"fees{y}.txt").read_text() for y in (25, 26, 27)]
    t25b = (SRC / "fees25b.txt").read_text()
    # verify the tier wording we rely on is present in each notice
    assert "For loans of $1,000,000 or less: 0.00%." in t25 and "plus 3.75% of the guaranteed portion of the loan over" in t25
    assert "supersedes SBA Information Notice" in t25b and "March 27, 2025" in t25b
    for t in (t25b, t26, t27):
        assert "$150,001 to $700,000: 3% of the guaranteed portion" in t
        assert "$700,001 to $5,000,000: 3.5% of the guaranteed portion" in t
    def fy25(L, g):
        return 0.0 if L <= 1_000_000 else 0.035 * min(g, 1e6) + 0.0375 * max(0, g - 1e6)
    def fy26_27(L, g):
        if L <= 150_000: return 0.02 * g
        if L <= 700_000: return 0.03 * g
        return 0.035 * min(g, 1e6) + 0.0375 * max(0, g - 1e6)
    rows = []
    for fy, per, fn, rule in ((2025, "FY2025 to 26 Mar 2025", "fees25.txt", fy25),
                              (2025, "FY2025 from 27 Mar 2025", "fees25b.txt", fy26_27),
                              (2026, "FY2026", "fees26.txt", fy26_27), (2027, "FY2027", "fees27.txt", fy26_27)):
        for L in (500_000, 1_000_000, 2_000_000, 5_000_000):
            g = 0.75 * L; fee = rule(L, g)
            rows.append(dict(fiscal_year=fy, period=per, loan_amount=L, guaranteed_pct=75, upfront_fee_usd=round(fee),
                             fee_pct_of_loan=round(100 * fee / L, 3), notice_control_no=ctrl(fn)))
    df = pd.DataFrame(rows)
    df.to_csv(RULES / "fee_schedule_FY2024_2027.csv", index=False)
    chk = df[df.fiscal_year == 2027].set_index("loan_amount").upfront_fee_usd.to_dict()
    assert chk == {500000: 11250, 1000000: 26250, 2000000: 53750, 5000000: 138125}, chk
    return df


def build_worked_deal_tables():
    # Numbers quoted from Chapter 5, "the worked deal" (illustrative, fixed for the book)
    s = pd.DataFrame([
        ("source", "7(a) loan", 2_961_718), ("source", "Full-standby seller note", 164_540),
        ("source", "Buyer cash", 164_540), ("use", "Purchase price", 3_000_000),
        ("use", "Working capital", 150_000), ("use", "Closing and diligence", 60_000),
        ("use", "FY2027 upfront fee", 80_798)], columns=["side", "item", "usd"])
    assert s[s.side == "source"].usd.sum() == s[s.side == "use"].usd.sum() == 3_290_798
    s["note"] = "Illustrative worked deal (Chapter 5)"
    s.to_csv(T / "T20_worked_deal_stack.csv", index=False)
    y = pd.DataFrame([
        ("Adjusted EBITDA", 580_000), ("Interest, year one", -258_786), ("Principal, year one", -191_427),
        ("Pre-tax cash after debt service", 129_786), ("Taxable income, asset purchase", 99_547),
        ("Taxable income, stock purchase", 266_214)], columns=["item", "usd"])
    y["note"] = "Illustrative worked deal at 9.00% (Chapter 5)"
    y.to_csv(T / "T21_worked_deal_year_one.csv", index=False)
    return s, y


def build_t19():
    d = pd.read_pickle(R / "data/raw/7a_enriched.pkl")
    s = d[(d.coo == True) & d.ApprovalFY.between(2023, 2025) & d.FirstDisbursementDate.notna()].copy()
    s["days"] = (s.FirstDisbursementDate - s.ApprovalDate).dt.days
    n_raw = len(s)
    s = s[(s.days >= 0) & (s.days <= 730)]
    top = s.ProcessingMethod.value_counts().index[:3].tolist()
    s["method"] = s.ProcessingMethod.where(s.ProcessingMethod.isin(top), "Other")
    out = []
    for name, g in [("All", s)] + [(m, s[s.method == m]) for m in top + ["Other"]]:
        q = g.days.quantile([.25, .5, .75, .9])
        out.append(dict(table="summary", group=name, bin_start_days="", n=len(g), p25=q[.25], median=q[.5],
                        p75=q[.75], p90=q[.9]))
    h = np.histogram(s.days, bins=np.arange(0, 740, 10))
    for c, b in zip(h[0], h[1][:-1]):
        out.append(dict(table="histogram_10day", group="All", bin_start_days=int(b), n=int(c)))
    df = pd.DataFrame(out)
    df["note"] = (f"coo==True, ApprovalFY 2023-2025, FirstDisbursementDate not null (n={n_raw}); "
                  f"days<0 or >730 dropped ({n_raw - len(s)})")
    df.to_csv(T / "T19_approval_to_first_disbursement_coo_FY2023_2025.csv", index=False)
    return df


fees = build_fee_schedule()
deal, yr1 = build_worked_deal_tables()
t19 = build_t19()

# ------------------------------------------------------------------ 1.1 change-of-ownership volume
d = pd.read_csv(T / "T10_change_of_ownership_by_fy.csv")
d = d[d.FY.between(2019, 2026)]
f, ax = fig(2.8)
x = np.arange(len(d))
for i, r in enumerate(d.itertuples()):
    part = str(r.partial_year) == "True"
    ax.bar(i, r.coo_usd_bn, 0.66, color="white" if part else BLUE, edgecolor=BLUE, hatch="////" if part else None, lw=0.8)
    ax.text(i, r.coo_usd_bn / 2, f"{r.n_coo:,}\nloans", ha="center", va="center", fontsize=6.6,
            color=BLUE if part else "white", fontweight="semibold",
            bbox=dict(fc="white", ec="none", pad=0.6) if part else None)
    ax.text(i, r.coo_usd_bn + 0.2, f"{r.coo_share_usd_pct:.0f}%", ha="center", va="bottom", fontsize=7.5)
ax.set_xticks(x, [str(fy) if fy < 2026 else "2026\nto 30 Jun" for fy in d.FY])
ax.set_ylim(0, 11); ax.set_ylabel("Approved, US$ billions"); ax.grid(axis="x", visible=False)
ax.text(-0.4, 10.4, "% above bar = share of all 7(a) dollars that year", fontsize=7, color=MUTED)
save(f, "fig01-coo-volume.png", "Loans coded as a change of ownership, FY2019-2026",
     "Source: SBA 7(a) FOIA loan file, data as of 30 June 2026. The change-of-ownership code was introduced in FY2018. "
     "It is a lender-entered business-age category, so it likely undercounts acquisitions. FY2026 is three quarters.")

# ------------------------------------------------------------------ 3.1 variable rate vs prime
d = pd.read_csv(T / "T02c_monthly_variable_rate_vs_prime.csv")
d["dt"] = pd.to_datetime(d.month + "-15")
d = d.set_index("dt").reindex(pd.date_range(d.dt.min(), d.dt.max(), freq="MS") + pd.Timedelta(days=14))
pr = pd.read_csv(SRC / "dprime.csv"); pr.columns = ["date", "prime"]
p_end = pr.dropna().iloc[-1]
f, ax = fig(2.8)
ax.fill_between(d.index, d.prime, d.median_rate, color=LBLUE, alpha=0.45, lw=0)
ax.plot(d.index, d.median_rate, color=BLUE)
ax.plot(d.index, d.prime, color=GRAY, lw=1.6)
last = d.dropna().iloc[-1]; lx = d.dropna().index[-1]
ax.text(lx + pd.Timedelta(days=60), last.median_rate, f"Median variable\nrate {last.median_rate:.2f}%", va="center", fontsize=7.3, color=BLUE)
ax.text(lx + pd.Timedelta(days=60), last.prime - 1.25, f"Prime {last.prime:.2f}%\n(Jun 2026)", va="center", fontsize=7.3, color=MUTED)
ax.plot(pd.Timestamp(p_end.date), float(p_end.prime), "o", ms=3.5, color=INK)
ax.annotate(f"Prime {float(p_end.prime):.2f}%,\n30 Sep 2026", (pd.Timestamp(p_end.date), float(p_end.prime)),
            xytext=(pd.Timestamp("2022-09-01"), 3.0), fontsize=6.8, color=INK,
            arrowprops=dict(arrowstyle="-", color=MUTED, lw=0.6))
ax.text(pd.Timestamp("2012-12-01"), 4.6, "Spread: median 2.75 points\nin most months", fontsize=7.2, color=BLUE, ha="center", va="center")
ax.annotate("Prime 3.25%", (pd.Timestamp("2012-01-01"), 3.25), xytext=(pd.Timestamp("2014-06-01"), 1.9), fontsize=7, color=MUTED,
            arrowprops=dict(arrowstyle="-", color=MUTED, lw=0.6))
ax.annotate("Prime 8.50%", (pd.Timestamp("2023-09-15"), 8.5), xytext=(pd.Timestamp("2019-06-01"), 10.6), fontsize=7, color=MUTED,
            arrowprops=dict(arrowstyle="-", color=MUTED, lw=0.6))
ax.set_ylim(0, 12); ax.set_ylabel("Interest rate, %")
ax.set_xlim(pd.Timestamp("2009-07-01"), pd.Timestamp("2030-06-01"))
ax.set_xticks([pd.Timestamp(f"{y}-01-01") for y in (2010, 2014, 2018, 2022, 2026)], ["2010", "2014", "2018", "2022", "2026"])
save(f, "fig03-rate-vs-prime.png", "SBA 7(a) variable rates track prime plus a steady spread",
     "Source: SBA 7(a) FOIA loan file, variable-rate loans, monthly medians by approval month, Oct 2009 to Jun 2026 "
     "(no loans recorded Oct 2025). Prime: FRED DPRIME. Shaded band = spread over prime.")

# ------------------------------------------------------------------ 3.2 upfront fee by fiscal year
f, ax = fig(2.9)
loans = [500_000, 1_000_000, 2_000_000, 5_000_000]
yrs = ["FY2025 to 26 Mar 2025", "FY2025 from 27 Mar 2025", "FY2026", "FY2027"]
short = {yrs[0]: "'25a", yrs[1]: "'25b", yrs[2]: "'26", yrs[3]: "'27"}
sty = {yrs[0]: dict(color="white", edgecolor=MUTED, hatch="////"), yrs[1]: dict(color=PALE, edgecolor=GRAY),
       yrs[2]: dict(color=GRAY, edgecolor=GRAY), yrs[3]: dict(color=BLUE, edgecolor=BLUE)}
bw = 0.2; ticks, tl = [], []
for gi, L in enumerate(loans):
    for yi, fy in enumerate(yrs):
        v = float(fees[(fees.period == fy) & (fees.loan_amount == L)].upfront_fee_usd.iloc[0])
        xx = gi + (yi - 1.5) * bw
        ax.bar(xx, v / 1000, bw * 0.92, lw=0.7, **sty[fy])
        lab = "$0" if v == 0 else usd(v)
        ax.text(xx, v / 1000 + 2, lab, ha="center", va="bottom", fontsize=6.2, rotation=90 if v else 0,
                color=INK)
        ticks.append(xx); tl.append(short[fy])
ax.set_xticks(ticks, tl, fontsize=5.6)
for gi, L in enumerate(loans):
    ax.text(gi, -36, f"${L/1e6:g}M loan" if L >= 1e6 else "$500k loan", ha="center", fontsize=7.8, color=INK, fontweight="semibold")
ax.set_ylim(0, 205); ax.set_ylabel("Upfront fee, US$ thousands"); ax.grid(axis="x", visible=False)
ax.text(-0.45, 196, "Bars per loan: FY2025 to 26 Mar (a, hatched), FY2025 from 27 Mar (b, pale), FY2026 (grey), FY2027 (blue)", fontsize=6.3, color=MUTED)
save(f, "fig03-upfront-fee-by-year.png", "The upfront SBA fee on the same loan, FY2025-FY2027",
     "Loans over 12 months, 75% guaranty, no fee relief. Sources: SBA Information Notices 5000-858936 (FY2025 from "
     "1 Oct 2024), 5000-865775 (FY2025, delegated loans approved 27 Mar to 30 Sep 2025), "
     "5000-872051 (FY2026), 5000-881797 (FY2027). Relief: FY2026, 0% for manufacturers (NAICS 31-33) on loans of "
     "$950,000 or less; FY2027, 0% on loans of $700,000 or less to manufacturers, listed food-supply-chain NAICS codes "
     "and rural businesses. FY2024 is not shown: its notice is not in the research files.")

# ------------------------------------------------------------------ 5.1 price ceiling
d = pd.read_csv(T / "T15_dscr_price_ceiling_grid.csv"); d = d[d.term_years == 10]
f, ax = fig(2.8)
spec = {1.15: dict(color=GRAY, ls=(0, (3, 1.5))), 1.25: dict(color=BLUE), 1.5: dict(color=AMBER)}
for dscr, s in spec.items():
    g = d[d.dscr == dscr].sort_values("rate_pct")
    ax.plot(g.rate_pct, g.max_loan_per_100k_cash_available_for_debt_service / 1000, **s)
    ax.text(12.15, g.max_loan_per_100k_cash_available_for_debt_service.iloc[-1] / 1000, f"DSC {dscr:.2f}x",
            va="center", fontsize=7.5, color=s["color"] if dscr != 1.15 else MUTED)
g = d[d.dscr == 1.25].set_index("rate_pct").max_loan_per_100k_cash_available_for_debt_service
for r_, off in ((6.0, (0.15, 18)), (10.0, (0.25, -45))):
    ax.plot(r_, g[r_] / 1000, "o", ms=4, color=BLUE)
    ax.text(r_ + off[0], g[r_] / 1000 + off[1], f"${g[r_]:,.0f}", fontsize=7.3, color=BLUE)
ax.axvline(10, color=INK, ls=":", lw=1)
ax.text(9.9, 665, "Variable-rate cap, loans over\n$350k, prime 7.00%: 10.00%", ha="right", fontsize=6.8, color=INK)
ax.set_xlim(5.8, 13.3); ax.set_ylim(350, 700); ax.set_xticks(range(6, 13))
ax.xaxis.set_major_formatter(FuncFormatter(lambda v, _: f"{v:.0f}%"))
ax.yaxis.set_major_formatter(FuncFormatter(lambda v, _: f"${v:.0f}k"))
ax.set_xlabel("Interest rate"); ax.set_ylabel("Maximum loan per $100,000")
save(f, "fig05-price-ceiling.png", "How much each $100,000 of cash flow can borrow, 10-year term",
     "Maximum loan whose annual payments (monthly amortization, 10 years) equal cash available for debt service "
     "divided by the DSC ratio. Y-axis starts at $350k. Source: author's price-ceiling model (Appendix A).")

# ------------------------------------------------------------------ 7.1 capital stack
f, ax = fig(3.4)
src = deal[deal.side == "source"]; use = deal[deal.side == "use"]
total = 3_290_798
cs = {"7(a) loan": BLUE, "Full-standby seller note": AMBER, "Buyer cash": DARK,
      "Purchase price": GRAY, "Working capital": LBLUE, "Closing and diligence": PALE, "FY2027 upfront fee": AMBER}
def stack(xpos, df, side):
    b = 0; mids = []
    for r in df.itertuples():
        ax.bar(xpos, r.usd / 1e6, 0.5, bottom=b / 1e6, color=cs[r.item], edgecolor="white", lw=0.6,
               hatch="////" if r.item == "FY2027 upfront fee" else None)
        mids.append((r.item, r.usd, (b + r.usd / 2) / 1e6)); b += r.usd
    return mids
ms = stack(0, src, "L"); mu = stack(1, use, "R")
# labels with leaders; spread the thin top segments apart
def place(mids, xbar, side, ys):
    for (item, v, y), ty in zip(mids, ys):
        xt = xbar - 0.32 if side == "L" else xbar + 0.32
        ax.annotate(f"{item}\n{usd(v)}", (xbar + (-0.25 if side == "L" else 0.25), y), xytext=(xt, ty),
                    ha="right" if side == "L" else "left", va="center", fontsize=6.9,
                    arrowprops=dict(arrowstyle="-", color=MUTED, lw=0.5, shrinkA=1, shrinkB=0, relpos=(1, 0.5) if side == "L" else (0, 0.5)))
place(ms, 0, "L", [1.48, 2.85, 3.4])
place(mu, 1, "R", [1.5, 2.55, 2.95, 3.4])
ax.text(0, total / 1e6 + 0.07, usd(total), ha="center", fontsize=7.3, fontweight="semibold")
ax.text(1, total / 1e6 + 0.07, usd(total), ha="center", fontsize=7.3, fontweight="semibold")
ax.set_xticks([0, 1], ["Sources", "Uses"]); ax.tick_params(axis="x", labelsize=8.5, labelcolor=INK)
ax.set_xlim(-1.35, 2.35); ax.set_ylim(0, 3.75); ax.set_ylabel("US$ millions"); ax.grid(axis="x", visible=False)
ax.text(-1.3, 0.25, f"Buyer's own cash:\n{usd(164_540)} =\n{164_540/total*100:.1f}% of total\nproject cost",
        fontsize=7.3, color=INK, fontweight="semibold")
ax.text(2.3, 0.12, "ILLUSTRATIVE", ha="right", fontsize=6.8, color=MUTED, fontweight="semibold")
save(f, "fig07-capital-stack.png", "The worked deal: sources and uses",
     "Illustrative worked deal (Chapter 5). Equity injection 10% of total project cost ($329,080): half full-standby "
     "seller note, half buyer cash. Upfront fee at FY2027 rates, financed in the loan.")

# ------------------------------------------------------------------ 11.1 charge-off by vintage
d = pd.read_csv(T / "T04_chargeoff_by_vintage.csv"); d["FY"] = d.FY.astype(int)
d = d[d.FY.between(2000, 2025)]
mat = d[d.FY <= 2015]; imm = d[d.FY >= 2016]
f, ax = fig(3.0)
ax.bar(mat.FY, mat.std_term_chargeoff_rate_pct, 0.72, color=BLUE)
ax.bar(imm.FY, imm.std_term_chargeoff_rate_pct, 0.72, color=PALE, edgecolor=GRAY, hatch="////", lw=0.5)
ax.plot(mat.FY, mat.std_term_dollar_loss_pct, color=AMBER, marker="o", ms=2.8, lw=1.6)
for r in imm.itertuples():
    ax.text(r.FY, r.std_term_chargeoff_rate_pct + 0.8, f"{r.still_active_exempt_pct:.0f}", ha="center", fontsize=6.2, color=MUTED)
for fy in (2006, 2007, 2008, 2013):
    v = float(mat[mat.FY == fy].std_term_chargeoff_rate_pct.iloc[0])
    ax.text(fy, v + 0.8, f"{v:.0f}%", ha="center", fontsize=7, color=BLUE, fontweight="semibold")
ax.text(2020.5, 17.5, "Still maturing: not loss rates.\nNumber = % of loans\nstill active", ha="center", fontsize=6.8, color=MUTED)
ax.text(2016.2, 33, "Bars: loans charged off", fontsize=7.2, color=BLUE)
ax.text(2016.2, 30.2, "Line: dollars lost, % of approved", fontsize=7.2, color="#9a6c12")
ax.set_xlim(1999.3, 2025.7); ax.set_ylim(0, 37); ax.set_xticks([2000, 2005, 2010, 2015, 2020, 2025])
ax.set_ylabel("Share of loans, %"); ax.grid(axis="x", visible=False)
save(f, "fig11-chargeoff-by-vintage.png", "Share of standard-term 7(a) loans eventually charged off,\nby year approved",
     "Source: SBA 7(a) FOIA loan file as of 30 June 2026; standard-term (non-revolving, full-term) loans that were disbursed. "
     "FY2016-2025 cohorts are right-censored: many loans have not yet had time to fail.")

# ------------------------------------------------------------------ 11.2 charge-off timing
d = pd.read_csv(T / "T11_chargeoff_timing_curve.csv")
f, ax = fig(2.7)
a = d[d.cohort == "FY2010-2015 std term"]; b = d[d.cohort == "FY2005-2008 std term"]
ax.plot(b.years_since_approval, b.cum_share_of_all_chargeoffs_pct, color=GRAY, ls=(0, (3, 1.5)))
ax.plot(a.years_since_approval, a.cum_share_of_all_chargeoffs_pct, color=BLUE)
for yv in (3, 6):
    v = float(a[a.years_since_approval == yv].cum_share_of_all_chargeoffs_pct.iloc[0])
    ax.plot(yv, v, "o", ms=4, color=BLUE)
    ax.text(yv + 0.3 if yv == 3 else yv - 0.25, v - 4 if yv == 3 else v + 4, f"{v:.1f}% by year {yv}", ha="left" if yv == 3 else "right", fontsize=7.3, color=BLUE)
ax.text(15.3, 99.7, "FY2010-2015", va="bottom", fontsize=7.3, color=BLUE)
ax.text(15.3, 93, "FY2005-2008", va="center", fontsize=7.3, color=MUTED)
ax.set_xlim(0.5, 18); ax.set_ylim(0, 108); ax.set_xticks([1, 3, 5, 7, 9, 11, 13, 15])
ax.set_xlabel("Years since approval"); ax.set_ylabel("Share of eventual charge-offs, %")
save(f, "fig11-chargeoff-timing.png", "When charge-offs happen",
     "Cumulative share of each cohort's charge-offs to date that had occurred by each year after approval; "
     "standard-term loans. Source: SBA 7(a) FOIA loan file as of 30 June 2026.")

# ------------------------------------------------------------------ 11.3 CoO vs existing, 36m
d = pd.read_csv(T / "T12b_coo_vs_others_36m_by_size_FY2018_2022.csv")
bands = ["<=150k", "150k-350k", "350k-500k", "500k-1M", "1M-2M", "2M-3.5M", "3.5M-5M"]
blab = {"<=150k": "$150k or less", "150k-350k": "$150k-350k", "350k-500k": "$350k-500k", "500k-1M": "$500k-1M",
        "1M-2M": "$1M-2M", "2M-3.5M": "$2M-3.5M", "3.5M-5M": "$3.5M-5M"}
cats = [("Change of Ownership", "Change of ownership", BLUE, "o"),
        ("Existing or more than 2 years old", "Existing business", GRAY, "s"),
        ("New Business or 2 years or less", "New (2 years or less)", AMBER, "^"),
        ("Startup, Loan Funds will Open Business", "Start-up", DARK, "D")]
f, ax = fig(3.9)
for bi, band in enumerate(bands):
    yb = len(bands) - 1 - bi
    for ci, (key, lab, col, mk) in enumerate(cats):
        r = d[(d.size_band == band) & (d.BusinessAge == key)].iloc[0]
        yy = yb + 0.27 - ci * 0.18
        ax.plot([max(0, r.ci95_low), r.ci95_high], [yy, yy], color=col, lw=1, alpha=0.9)
        ax.plot(r.chargeoff_36m_pct, yy, mk, ms=4, color=col, mec="white", mew=0.4)
ax.set_yticks(range(len(bands)), [blab[b] for b in bands[::-1]])
for yb in range(len(bands)): ax.axhspan(yb - 0.5, yb + 0.5, color="#f4f5f6" if yb % 2 else "white", zorder=0)
ax.grid(axis="y", visible=False); ax.set_ylim(-0.5, len(bands) + 0.35)
ax.set_xlim(-0.08, 5); ax.set_xlabel("Charged off within 36 months, % (lines: 95% intervals)")
for k, (key, lab, col, mk) in enumerate(cats):
    lx_, ly_ = (0.1 if k % 2 == 0 else 2.3), len(bands) + 0.15 - 0.33 * (k // 2)
    ax.plot(lx_, ly_, mk, ms=4, color=col); ax.text(lx_ + 0.12, ly_, lab, va="center", fontsize=7)
save(f, "fig11-coo-vs-existing.png", "Charge-offs within 36 months, FY2018-2022 loans,\nby business type",
     "Business type is the lender-entered BusinessAge code; loan size is the gross approval. CARES Act payment relief "
     "(2020-2021) suppressed early losses for these cohorts, so 36-month rates are low across the board. "
     "Source: SBA 7(a) FOIA loan file as of 30 June 2026.")

# ------------------------------------------------------------------ 12.1 charge-off by sector
d = pd.read_csv(T / "T06_chargeoff_by_sector_matured.csv")
a = d[(d.cohort == "FY2010-2015 all") & (d.n_disbursed >= 1000)].sort_values("chargeoff_rate_pct")
b = d[d.cohort == "FY2005-2008 std term"].set_index("sector")
short = {"Accommodation and food services": "Accommodation, food", "Arts, entertainment, recreation": "Arts, recreation",
         "Other services (except public administration)": "Other services", "Administrative, support, waste management": "Admin., support",
         "Professional, scientific, technical services": "Professional services", "Agriculture, forestry, fishing": "Agriculture",
         "Health care and social assistance": "Health care", "Real estate, rental and leasing": "Real estate, rental",
         "Transportation and warehousing": "Transportation", "Finance and insurance": "Finance, insurance",
         "Educational services": "Education"}
f, (a1, a2) = plt.subplots(1, 2, figsize=(W, 4.0), sharey=True, gridspec_kw=dict(width_ratios=[1, 1.15]))
yy = np.arange(len(a))
a1.hlines(yy, a.ci95_low_pct, a.ci95_high_pct, color=BLUE, lw=1.1)
a1.plot(a.chargeoff_rate_pct, yy, "o", ms=4, color=BLUE)
for y_, v, hi in zip(yy, a.chargeoff_rate_pct, a.ci95_high_pct): a1.text(hi + 0.3, y_, f"{v:.1f}", va="center", fontsize=6.4, color=BLUE)
a1.set_yticks(yy, [short.get(s, s) for s in a.sector], fontsize=7.2)
a1.set_xlim(0, 11); a1.set_xticks([0, 5, 10]); a1.grid(axis="y", visible=False)
a1.set_xlabel("% charged off", fontsize=7.5)
a1.text(0, len(a) - 0.1, "FY2010-2015, all loans", fontsize=7.3, fontweight="semibold", color=INK, va="bottom")
bv = b.loc[a.sector]
a2.hlines(yy, bv.ci95_low_pct, bv.ci95_high_pct, color=GRAY, lw=1.1)
a2.plot(bv.chargeoff_rate_pct, yy, "s", ms=3.8, color=DARK)
for y_, v, hi in zip(yy, bv.chargeoff_rate_pct, bv.ci95_high_pct): a2.text(max(hi, v + 1) + 0.8, y_, f"{v:.1f}", va="center", fontsize=6.4, color=DARK)
a2.set_xlim(0, 42); a2.set_xticks([0, 10, 20, 30, 40]); a2.grid(axis="y", visible=False)
a2.set_xlabel("% charged off", fontsize=7.5)
a2.text(0, len(a) - 0.1, "FY2005-2008, standard-term only", fontsize=7.3, fontweight="semibold", color=INK, va="bottom")
a1.set_ylim(-0.6, len(a) + 0.5)
save(f, "fig12-chargeoff-by-sector.png", "Share of loans charged off by industry",
     "Different cohort definitions and x-axis scales: left, all FY2010-2015 loans disbursed (sectors with 1,000+ loans; "
     "lines are 95% intervals); right, FY2005-2008 standard-term loans in the same sectors. Both cohorts have matured. "
     "Source: SBA 7(a) FOIA loan file as of 30 June 2026.", title_ax=a1)

# ------------------------------------------------------------------ 13.1 franchise vs independent
d = pd.read_csv(T / "T07c_franchise_vs_independent_by_size_matured.csv")
f, ax = fig(3.4)
for bi, band in enumerate(bands):
    yb = len(bands) - 1 - bi
    for fr, col, mk, off in ((True, AMBER, "^", 0.14), (False, BLUE, "o", -0.14)):
        r = d[(d.size_band == band) & (d.franchise.astype(str) == str(fr))].iloc[0]
        lo, hi = wilson(r.chargeoffs, r.n)
        ax.plot([100 * lo, 100 * hi], [yb + off] * 2, color=col, lw=1.1)
        ax.plot(r.chargeoff_rate_pct, yb + off, mk, ms=4.3, color=col, mec="white", mew=0.4)
        ax.text(100 * hi + 0.25, yb + off, f"{r.chargeoff_rate_pct:.1f}%", va="center", fontsize=6.5, color=col if not fr else "#9a6c12")
for yb in range(len(bands)): ax.axhspan(yb - 0.5, yb + 0.5, color="#f4f5f6" if yb % 2 else "white", zorder=0)
ax.set_yticks(range(len(bands)), [blab[b] for b in bands[::-1]])
ax.grid(axis="y", visible=False); ax.set_xlim(0, 15); ax.set_ylim(-0.5, len(bands) - 0.5 + 0.6)
ax.plot(0.3, len(bands) - 0.1, "^", ms=4.3, color=AMBER); ax.text(0.6, len(bands) - 0.1, "Franchise", va="center", fontsize=7.2)
ax.plot(3.0, len(bands) - 0.1, "o", ms=4.3, color=BLUE); ax.text(3.3, len(bands) - 0.1, "Independent", va="center", fontsize=7.2)
ax.set_xlabel("Loans charged off, % (lines: Wilson 95% intervals)")
save(f, "fig13-franchise-vs-independent.png", "Franchise and independent loans: charge-off rates by loan size",
     "FY2010-2015 standard-term loans; franchise = loan carries a franchise code. "
     "Source: SBA 7(a) FOIA loan file as of 30 June 2026; intervals computed by the author.")

# ------------------------------------------------------------------ 14.1 top CoO lenders
d = pd.read_csv(T / "T08b_top_lenders_FY2023_2025.csv")
d = d[d.population == "Change of Ownership flag FY2023-2025"].sort_values("approved_usd_m", ascending=False).head(10)
nm = lambda s: (s.replace(", National Association", "").replace(" National Association", "").replace("The Huntington National Bank", "Huntington National Bank")
                 .replace(" Banking Company", "").replace(" Corporation", "").replace(" of Indiana", "").replace(", A Division of", "").replace(", Inc.", "").replace(", LLC", ""))
f, ax = fig(3.2)
dd = d.iloc[::-1]
ax.barh([nm(s) for s in dd.BankName], dd.approved_usd_m / 1000, 0.62, color=[BLUE if "Live Oak" in s else GRAY for s in dd.BankName])
for i, r in enumerate(dd.itertuples()):
    inside = r.approved_usd_m > 2000
    ax.text(r.approved_usd_m / 1000 + (-0.05 if inside else 0.04), i, f"${r.approved_usd_m/1000:.2f}bn | {r.n_loans:,} loans | median {money(r.median_loan_usd)}",
            va="center", ha="right" if inside else "left", fontsize=6.6, color="white" if inside else INK, fontweight="semibold" if inside else "normal")
ax.set_xlim(0, 4.6); ax.set_xticks([0, 1, 2, 3]); ax.set_xlabel("Approved, US$ billions"); ax.grid(axis="y", visible=False)
save(f, "fig14-coo-top-lenders.png", "The largest change-of-ownership lenders, FY2023-2025",
     "Loans with the change-of-ownership code approved FY2023-2025, top 10 by dollars. Names are the current assignee in "
     "the SBA file, which may differ from the originating lender. Source: SBA 7(a) FOIA loan file as of 30 June 2026.")

# ------------------------------------------------------------------ 14.2 lender obs vs expected
d = pd.read_csv(T / "T08d_lender_obs_vs_expected_matured_n500.csv").sort_values("obs_to_exp_ratio", ascending=False)
f, ax = fig(6.6)
yy = np.arange(len(d))
for y_, r in zip(yy, d.itertuples()):
    col = BLUE if r.ratio_ci95_high < 1 else (AMBER if r.ratio_ci95_low > 1 else GRAY)
    mk = "o" if r.ratio_ci95_high < 1 else ("^" if r.ratio_ci95_low > 1 else "s")
    ax.plot([r.ratio_ci95_low, r.ratio_ci95_high], [y_, y_], color=col, lw=1)
    ax.plot(r.obs_to_exp_ratio, y_, mk, ms=3.3, color=col)
labels = [textwrap.shorten(nm(s), 40, placeholder="...") + f" ({n:,})" for s, n in zip(d.BankName, d.n_disbursed)]
ax.set_yticks(yy, labels, fontsize=5.9)
ax.axvline(1, color=INK, lw=0.9)
ax.text(1.03, len(d) - 0.4, "1.0 = as expected", fontsize=6.8, color=INK, va="bottom")
ax.text(1.06, len(d) + 0.9, "● below 1   ■ includes 1   ▲ above 1", fontsize=6.8, color=MUTED, va="bottom")
ax.set_ylim(-0.8, len(d) + 2.3); ax.set_xlim(0, 2.7); ax.grid(axis="y", visible=False)
ax.set_xlabel("Observed ÷ expected charge-offs (lines: 95% intervals)")
save(f, "fig14-lender-obs-vs-expected.png", "Lender charge-offs against what their loan mix predicts, FY2010-2015",
     "Standard-term loans; lenders with 500+ disbursed loans (count in brackets). Expected = cohort charge-off rate for "
     "the same size band, sector and fiscal year; no adjustment for borrower credit, collateral or region. Names are the "
     "current assignee. Source: SBA 7(a) FOIA loan file as of 30 June 2026.")

# ------------------------------------------------------------------ 14.3 lender rate audit
d = pd.read_csv(T / "T16_lender_rate_normalization.csv")
def pick(prefix, basis):
    return d[d.case.str.startswith(prefix) & (d.basis == basis)].iloc[0]
lenders = ["PNC", "Live Oak", "First Internet"]
# "Naive" bars: each lender's raw median initial rate on loans coded Change of Ownership,
# FY2019 to 30 June 2026, from the loan record (recomputed from 7a_enriched.pkl:
# PNC 6.18%, Live Oak 8.00%, First Internet 10.00%), run through the same price-ceiling model.
from types import SimpleNamespace as _NS
def _maxprice(rate):
    i = rate / 1200; pay = 12 * i / (1 - (1 + i) ** -120)
    return round(580000 / 1.25 / pay / 0.9)
st = [_NS(rate_pct=r, max_total_project_cost_10pct_equity_before_wc_closing_fees=_maxprice(r)) for r in (6.18, 8.00, 10.00)]
no = [pick(l, "spread-normalised to one prime date") for l in lenders]
f, ax = fig(3.0)
yy = np.arange(len(lenders))[::-1]
for y_, s_, n_ in zip(yy, st, no):
    ax.barh(y_ + 0.19, s_.max_total_project_cost_10pct_equity_before_wc_closing_fees / 1e6, 0.36, color="white", edgecolor=GRAY, hatch="////", lw=0.7)
    ax.barh(y_ - 0.19, n_.max_total_project_cost_10pct_equity_before_wc_closing_fees / 1e6, 0.36, color=BLUE)
    ax.text(s_.max_total_project_cost_10pct_equity_before_wc_closing_fees / 1e6 + 0.05, y_ + 0.19, f"{usd(s_.max_total_project_cost_10pct_equity_before_wc_closing_fees)} at {s_.rate_pct:.2f}% (raw median)",
            va="center", fontsize=6.6, color=MUTED)
    ax.text(n_.max_total_project_cost_10pct_equity_before_wc_closing_fees / 1e6 + 0.05, y_ - 0.19, f"{usd(n_.max_total_project_cost_10pct_equity_before_wc_closing_fees)} at {n_.rate_pct:.2f}% (normalized)",
            va="center", fontsize=6.6, color=BLUE)
ax.set_yticks(yy, lenders, fontsize=8)
gs = st[0].max_total_project_cost_10pct_equity_before_wc_closing_fees - st[2].max_total_project_cost_10pct_equity_before_wc_closing_fees
gn = no[0].max_total_project_cost_10pct_equity_before_wc_closing_fees - no[2].max_total_project_cost_10pct_equity_before_wc_closing_fees
ax.text(0.05, -0.95, f"Gap, PNC vs First Internet: raw medians {usd(gs)}; normalized {usd(gn)}", fontsize=7.3, color=INK, fontweight="semibold")
ax.set_xlim(0, 6.3); ax.set_ylim(-1.15, 2.6); ax.set_xticks([0, 1, 2, 3, 4])
ax.set_xlabel("Maximum total project cost at 10% equity (before working capital,\nclosing costs and fees), US$ millions"); ax.grid(axis="y", visible=False)
save(f, "fig14-lender-rate-audit.png", "Same business, same cash flow: maximum project cost by lender,\nbefore and after normalizing for the year",
     "Raw (hatched) = each lender's median initial rate on change-of-ownership loans, FY2019 to 30 June 2026, from loans "
     "approved in different years (median approval PNC Jul 2020, Live Oak Nov 2022, First Internet Mar 2025). Normalized (blue) = prime 7.00% "
     "(FRED, 30 Sep 2026) plus each lender's median change-of-ownership spread. Price-ceiling model: 1.25 coverage, 10 years, "
     "10% equity, no fees. Source: author's analysis of the SBA 7(a) FOIA loan file as of 30 June 2026.")

# ------------------------------------------------------------------ 15.1 approval to funding
summ = t19[t19.table == "summary"].set_index("group"); hist = t19[t19.table == "histogram_10day"]
f, ax = fig(3.0)
hb = hist.bin_start_days.astype(int); hn = hist.n.astype(int)
ax.bar(hb + 5, hn, 9.2, color=GRAY)
allr = summ.loc["All"]
for q, lab, col in ((allr["median"], "Median", BLUE), (allr["p75"], "75th percentile", AMBER)):
    ax.axvline(q, color=col, lw=1.4, ls="-" if lab == "Median" else (0, (3, 1.5)), ymax=1 if lab == "Median" else 0.62)
    ax.text(q + 4, hn.max() * (1.08 if lab == "Median" else 0.86), f"{lab}\n{q:.0f} days", fontsize=7,
            color=col if lab == "Median" else "#9a6c12", va="center")
ax.set_xlim(0, 300); ax.set_ylim(0, hn.max() * 1.22); ax.set_ylabel("Loans per 10-day bin"); ax.set_xlabel("Days from approval to first disbursement")
ax.grid(axis="x", visible=False)
beyond = hn[hb >= 300].sum() / hn.sum() * 100
ax.text(298, hn.max() * 0.12, f"{beyond:.1f}% of loans take 300+ days (not shown)", ha="right", fontsize=6.6, color=MUTED)
mlab = {"Preferred Lenders Program": "Preferred Lenders", "SBA Express Program": "SBA Express", "7a General": "7(a) General (SBA-approved)", "Other": "Other"}
tx = "Median days by processing method\n" + "\n".join(
    f"{mlab.get(g, g)}: {summ.loc[g, 'median']:.0f}  (n={int(summ.loc[g, 'n']):,})" for g in summ.index if g != "All")
ax.text(0.97, 0.62, tx, transform=ax.transAxes, ha="right", va="top", fontsize=6.5, color=INK,
        bbox=dict(fc="white", ec=GRID, lw=0.6, pad=3))
save(f, "fig15-approval-to-funding.png", "Days from SBA approval to first disbursement,\nchange-of-ownership loans, FY2023-2025",
     f"n = {int(allr['n']):,} loans with a first-disbursement date; gaps under 0 or over 730 days dropped. "
     "Time from application to approval is not in the data. Source: SBA 7(a) FOIA loan file as of 30 June 2026.")

# ------------------------------------------------------------------ 16.1 year one
v = yr1.set_index("item").usd
f, (a1, a2) = plt.subplots(1, 2, figsize=(W, 3.1), gridspec_kw=dict(width_ratios=[2.1, 1]))
e, i_, p_, c_ = v["Adjusted EBITDA"], -v["Interest, year one"], -v["Principal, year one"], v["Pre-tax cash after debt service"]
a1.bar(0, e / 1e3, 0.6, color=BLUE)
a1.bar(1, i_ / 1e3, 0.6, bottom=(e - i_) / 1e3, color=AMBER)
a1.bar(2, p_ / 1e3, 0.6, bottom=(e - i_ - p_) / 1e3, color="white", edgecolor=AMBER, hatch="////", lw=0.8)
a1.bar(3, c_ / 1e3, 0.6, color=DARK)
for x_, (y_, lab) in enumerate([(e, usd(e)), (e, "-" + usd(i_)), (e - i_, "-" + usd(p_)), (c_, usd(c_))]):
    a1.text(x_, y_ / 1e3 + 12, lab, ha="center", fontsize=6.8)
for x_ in range(3):
    top = [e, e - i_, e - i_ - p_][x_]
    a1.plot([x_ + 0.3, x_ + 0.7], [top / 1e3] * 2, color=MUTED, lw=0.6)
a1.annotate("", xy=(2.45, (e - i_ - p_) / 1e3), xytext=(2.45, e / 1e3), arrowprops=dict(arrowstyle="<->", color=INK, lw=0.7))
a1.text(2.55, (e - (i_ + p_) / 2) / 1e3 + 40, f"Debt service\n{(i_ + p_) / e * 100:.0f}% of\nEBITDA", fontsize=6.8, va="center")
a1.set_xticks(range(4), ["Adjusted\nEBITDA", "Interest", "Principal", "Pre-tax cash\nafter debt\nservice"], fontsize=6.8)
a1.set_ylim(0, 680); a1.set_ylabel("US$ thousands"); a1.grid(axis="x", visible=False)
a2.bar([0, 1], [v["Taxable income, asset purchase"] / 1e3, v["Taxable income, stock purchase"] / 1e3], 0.6,
       color=[GRAY, BLUE])
for x_, k in enumerate(["Taxable income, asset purchase", "Taxable income, stock purchase"]):
    a2.text(x_, v[k] / 1e3 + 12, usd(v[k]), ha="center", fontsize=6.8)
a2.set_xticks([0, 1], ["Asset\npurchase", "Stock\npurchase"], fontsize=6.8)
a2.set_ylim(0, 680); a2.set_yticklabels([]); a2.grid(axis="x", visible=False)
a2.text(-0.45, 655, "Taxable income,\nsame cash", fontsize=7.3, color=INK, va="top", fontweight="semibold")
a1.text(3.4, 640, "ILLUSTRATIVE", ha="right", fontsize=6.6, color=MUTED, fontweight="semibold")
save(f, "fig16-year-one-cash.png", "The worked deal, year one",
     "Illustrative worked deal at 9.00%, 10-year amortization. Asset purchase: $2.5M goodwill amortized over 15 years "
     "(IRC 197) plus $55,000 depreciation. Stock purchase: no step-up. Ignores state taxes; the buyer's $155,000 "
     "salary is already deducted in adjusted EBITDA.", title_ax=a1)