#!/usr/bin/env python3
"""
Lodestar Research — SBA 7(a) Trades Acquisition Analysis
=========================================================
Input:  SBA 7(a) FOIA loan-level CSV (FY2020–Present file).
        Source: data.sba.gov, dataset "7(a) & 504 FOIA" (updated quarterly).
        Download in a browser and pass the path:  python3 analyze_trades.py foia-7a-*.csv
Output: ./out/  — every stats table for the flagship report, as CSV + a summary.md

Acquisition marker: BusinessAge == "Change of Ownership" (per SBA data dictionary).
"""
import sys, os
import pandas as pd

# ---------------------------------------------------------------- taxonomy
TRADES = {
    "238220": "Plumbing / HVAC contractors",
    "238210": "Electrical contractors",
    "238160": "Roofing contractors",
    "238320": "Painting contractors",
    "238910": "Site prep / excavation",
    "238990": "Other specialty trades",
    "238350": "Finish carpentry",
    "238140": "Masonry",
    "238330": "Flooring",
    "561730": "Landscaping services",
    "561710": "Pest control",
    "561720": "Janitorial / cleaning",
    "811111": "Auto repair (general)",
    "811412": "Appliance repair",
    "484110": "Local freight trucking",
}
NJ_FOCUS = "NJ"

USECOLS = [
    "AsOfDate","Program","BorrName","BorrState","BankName","BankState",
    "GrossApproval","SBAGuaranteedApproval","ApprovalDate","ApprovalFiscalYear",
    "InitialInterestRate","FixedOrVariableInterestInd","TermInMonths",
    "NaicsCode","NaicsDescription","FranchiseName","ProjectState",
    "BusinessType","BusinessAge","LoanStatus","ChargeOffDate","GrossChargeOffAmount",
    "JobsSupported",
]

def load(path):
    df = pd.read_csv(path, usecols=lambda c: c in USECOLS, dtype=str, low_memory=False)
    for col in ["GrossApproval","SBAGuaranteedApproval","InitialInterestRate",
                "TermInMonths","GrossChargeOffAmount","JobsSupported","ApprovalFiscalYear"]:
        if col in df: df[col] = pd.to_numeric(df[col], errors="coerce")
    df["naics6"] = df["NaicsCode"].astype(str).str[:6]
    df["trade"] = df["naics6"].map(TRADES)
    df["is_acq"] = df["BusinessAge"].str.contains("Change of Ownership", case=False, na=False)
    return df

def money(x): return f"${x:,.0f}"

def run(path):
    os.makedirs("out", exist_ok=True)
    df = load(path)
    t = df[df["trade"].notna()].copy()          # all trades loans
    a = t[t["is_acq"]].copy()                    # trades acquisitions
    lines = ["# Lodestar Research — SBA 7(a) Trades Acquisition Analysis (auto-generated)\n"]

    # 1. Headline: acquisition volume in the trades, by FY
    g = a.groupby("ApprovalFiscalYear").agg(loans=("GrossApproval","size"),
        dollars=("GrossApproval","sum"), avg=("GrossApproval","mean"),
        median=("GrossApproval","median"))
    g.to_csv("out/1_acquisitions_by_fy.csv")
    lines.append("## 1. Trades acquisition lending by fiscal year\n" + g.to_string() + "\n")

    # 2. By trade: volume, avg deal size, median (the money table)
    g = a.groupby("trade").agg(loans=("GrossApproval","size"),
        dollars=("GrossApproval","sum"), avg=("GrossApproval","mean"),
        median=("GrossApproval","median")).sort_values("loans", ascending=False)
    g.to_csv("out/2_by_trade.csv")
    lines.append("## 2. Acquisition loans by trade\n" + g.to_string() + "\n")

    # 3. Who lends: top lenders funding trades acquisitions
    g = a.groupby("BankName").agg(loans=("GrossApproval","size"),
        dollars=("GrossApproval","sum")).sort_values("loans", ascending=False).head(25)
    g.to_csv("out/3_top_lenders.csv")
    lines.append("## 3. Top 25 lenders for trades acquisitions\n" + g.to_string() + "\n")

    # 4. Pricing: rates & terms by FY (fixed vs variable)
    g = a.groupby(["ApprovalFiscalYear","FixedOrVariableInterestInd"]).agg(
        loans=("InitialInterestRate","size"), avg_rate=("InitialInterestRate","mean"),
        avg_term_mo=("TermInMonths","mean"))
    g.to_csv("out/4_pricing.csv")
    lines.append("## 4. Rates and terms\n" + g.to_string() + "\n")

    # 5. Deal size distribution (bands)
    bands = pd.cut(a["GrossApproval"], [0,150e3,350e3,750e3,1.5e6,3e6,5e6,99e9],
        labels=["<150K","150-350K","350-750K","750K-1.5M","1.5-3M","3-5M","5M+"])
    g = bands.value_counts().sort_index()
    g.to_csv("out/5_size_bands.csv")
    lines.append("## 5. Deal size distribution\n" + g.to_string() + "\n")

    # 6. Geography: acquisitions by state + NJ focus cut
    g = a.groupby("ProjectState").agg(loans=("GrossApproval","size"),
        dollars=("GrossApproval","sum")).sort_values("loans", ascending=False)
    g.to_csv("out/6_by_state.csv")
    nj = a[a["ProjectState"]==NJ_FOCUS]
    lines.append(f"## 6. Geography\nTop states saved to CSV. NJ: {len(nj)} acquisition loans, "
                 f"{money(nj['GrossApproval'].sum())}, avg {money(nj['GrossApproval'].mean())}\n")
    njg = nj.groupby("trade").agg(loans=("GrossApproval","size"),
        avg=("GrossApproval","mean")).sort_values("loans", ascending=False)
    njg.to_csv("out/6b_nj_by_trade.csv")

    # 7. Risk: charge-off rate, acquisitions vs all trades loans (FY2020-22 cohorts age enough)
    old = t[t["ApprovalFiscalYear"] <= 2022]
    def co_rate(x): return x["ChargeOffDate"].notna().mean()*100
    lines.append("## 7. Charge-off rates (FY2020-22 cohorts)\n"
        f"Trades acquisitions: {co_rate(old[old.is_acq]):.2f}%  |  "
        f"All trades 7(a): {co_rate(old):.2f}%\n")
    g = old[old.is_acq].groupby("trade").apply(co_rate).sort_values(ascending=False)
    g.to_csv("out/7_chargeoff_by_trade.csv")

    # 8. Acquisition share: what % of all trades 7(a) lending is change-of-ownership
    g = t.groupby("ApprovalFiscalYear").apply(lambda x: x["is_acq"].mean()*100)
    g.to_csv("out/8_acq_share_by_fy.csv")
    lines.append("## 8. Acquisition share of all trades 7(a) lending by FY\n" + g.to_string() + "\n")

    open("out/summary.md","w").write("\n".join(lines))
    print(f"Done. {len(t):,} trades loans, {len(a):,} acquisitions. See ./out/")

if __name__ == "__main__":
    run(sys.argv[1] if len(sys.argv)>1 else "foia-7a-fy2020-present.csv")