"""Load SBA 7(a) FOIA CSVs (as of 2026-06-30) into one parquet with typed columns.
Source: https://data.sba.gov/dataset/7a-504-foia (files FOIA_7a_FY2000_FY2009, FY2010_FY2019, FY2020_Present, asof_260630)."""
import pandas as pd, glob, os
RAW = os.path.join(os.path.dirname(__file__), '..', 'raw')
cols_keep = ["Program","LocationID","BorrName","BorrState","BankName","BankState","GrossApproval","SBAGuaranteedApproval",
 "ApprovalDate","ApprovalFY","FirstDisbursementDate","ProcessingMethod","InitialInterestRate","FixedorVariableInterestInd",
 "TermInMonths","NaicsCode","NaicsDescription","FranchiseCode","FranchiseName","ProjectState","BusinessType","BusinessAge",
 "LoanStatus","PaidInFullDate","ChargeOffDate","GrossChargeOffAmount","RevolverStatus","JobsSupported","CollateralInd","SoldSecMrktInd"]
frames = []
for f in sorted(glob.glob(os.path.join(RAW, 'FOIA_7a_FY*_asof_260630.csv'))):
    df = pd.read_csv(f, usecols=cols_keep, dtype=str, encoding='latin-1', low_memory=False)
    df['src'] = os.path.basename(f)
    print(f, len(df))
    frames.append(df)
d = pd.concat(frames, ignore_index=True)
for c in d.columns:
    if d[c].dtype == object: d[c] = d[c].str.strip()
for c in ["GrossApproval","SBAGuaranteedApproval","InitialInterestRate","TermInMonths","GrossChargeOffAmount","JobsSupported"]:
    d[c] = pd.to_numeric(d[c], errors='coerce')
d['ApprovalFY'] = pd.to_numeric(d['ApprovalFY'], errors='coerce').astype('Int64')
for c in ["ApprovalDate","FirstDisbursementDate","PaidInFullDate","ChargeOffDate"]:
    d[c] = pd.to_datetime(d[c], errors='coerce')
d['LoanStatus'] = d['LoanStatus'].str.replace(' ', '', regex=False)
d.to_pickle(os.path.join(RAW, '7a_all_asof_260630.pkl'))
print('total', len(d))
print(d.groupby('ApprovalFY').size().to_string())
print(d['LoanStatus'].value_counts(dropna=False).to_string())
print(d['BusinessAge'].value_counts(dropna=False).to_string())
print(d['ProcessingMethod'].value_counts(dropna=False).head(25).to_string())
print(d['Program'].value_counts(dropna=False).to_string())