"""Reproduce Bolt's FY2015–FY2026 VA refinance tables.

Install openpyxl, then run: python replicate_va_refinance.py
Writes VA workbooks and aggregate CSVs in ./va_refinance_replication/.
Only public national/state/county aggregate files are accessed.
"""
from collections import Counter
import csv
from hashlib import sha256
from pathlib import Path
from urllib.request import Request, urlopen

from openpyxl import load_workbook

BASE = "https://www.benefits.va.gov/HOMELOANS/documents/"
STATE = {
    **{yr: f"docs/fy{yr}_loans_by_state.xlsx" for yr in range(2015, 2020)},
    2020: "docs/FY_2020_loans_by_state.xlsx",
    2021: "docs/FY2021_loans_by_state.xlsx",
    2022: "docs/fy22-loans-state.xlsm",
    **{yr: f"lender-statistics/fy{yr%100}-states-total.xlsx" for yr in range(2023, 2027)},
}
COUNTY = {yr: f"lender-statistics/fy{yr%100}-counties-total.xlsx" for yr in (2025, 2026)}
OUT = Path("va_refinance_replication")

def sheet_rows(relative):
    url = BASE + relative
    target = OUT / Path(relative).name
    if not target.exists():
        with urlopen(Request(url, headers={"User-Agent": "BoltResearchReplication/1.0"}), timeout=60) as response:
            target.write_bytes(response.read())
    print(target.name, sha256(target.read_bytes()).hexdigest())
    book = load_workbook(target, read_only=True, data_only=True)
    try:
        return list(book.active.values)
    finally:
        book.close()

def write_csv(name, fields, rows):
    with (OUT / name).open("w", encoding="utf-8", newline="") as handle:
        writer = csv.writer(handle)
        writer.writerow(fields)
        writer.writerows(rows)

def main():
    OUT.mkdir(exist_ok=True)
    years, regions, county_values, county_table = {}, {}, {}, []
    for year, relative in STATE.items():
        header, *rows = sheet_rows(relative)
        columns = {str(x).strip(): i for i,x in enumerate(header)}
        p, dollars, streamline, cash, cash_dollars = [
            columns[x] for x in ("Total Purchase Loans", "Total Loan Amount - Purchase",
                                "Total IRRRL Loans", "Total Cash-Out Loans", "Total Loan Amount - Cash-Out")
        ]
        grand, = [r for r in rows if r[0] == "Grand Total"]
        years[year] = [grand[cash], grand[streamline], grand[cash_dollars], grand[p], grand[dollars]]
        regions[year] = {r[0]: (r[cash] or 0,r[streamline] or 0) for r in rows if r[0] and r[0] != "Grand Total"}
    for year, relative in COUNTY.items():
        header, *rows = sheet_rows(relative)
        label, co, ir, pur, tot = [header.index(x) for x in ("County, State","Cash-Out","IRRRL","Purchase","Total")]
        grand, = [r for r in rows if r[label] == "Grand Total"]
        assert [grand[co],grand[ir]] == years[year][:2]
        tally = Counter()
        lookup = {}
        for row in rows:
            if row[label] == "Grand Total":
                continue
            counts = [int(row[j] or 0) for j in (co,ir,pur)]
            assert sum(counts) == row[tot], row
            tally.update(dict(zip(("cash","streamline","purchase"),counts)))
            place = row[label]
            cash, streamline = counts[:2]
            n = cash + streamline
            if place:
                assert place not in lookup
                lookup[place] = (cash, streamline)
            county, state = (str(place).rsplit(", ",1) if place else ("Unassigned",""))
            county_table.append([year,county,state,cash,streamline,n,cash/n if n else ""])
        assert [tally["cash"],tally["streamline"],tally["purchase"]] == [grand[co],grand[ir],grand[pur]]
        county_values[year] = lookup
    write_csv("national.csv", ["fiscal_year","cash_out","irrrl","cash_out_principal_usd","purchase_count","purchase_principal_usd","refinances","cash_out_share","official_state_file"], [
        [yr,*v,v[0]+v[1],v[0]/(v[0]+v[1]),BASE+STATE[yr]] for yr,v in years.items()
    ])
    write_csv("counties.csv",["fiscal_year","county","state","cash_out","irrrl","refinances","cash_out_share"], county_table)
    before,after=county_values[2025],county_values[2026]
    sensitivity=[]
    for threshold in (50,100,250):
        places=[s for s in before.keys() & after.keys() if min(sum(before[s]),sum(after[s])) >= threshold]
        up=sum(after[s][0]>before[s][0] for s in places)
        down=sum(after[s][0]*sum(before[s])<before[s][0]*sum(after[s]) for s in places)
        both=sum(after[s][0]>before[s][0] and after[s][0]*sum(before[s])<before[s][0]*sum(after[s]) for s in places)
        sensitivity.append([threshold,len(places),up,down,both,both/len(places),sum(sum(before[s]) for s in places),sum(sum(after[s]) for s in places)])
    write_csv("sensitivity.csv",["minimum_refis_each_year","matched_counties","count_rose","share_fell","both","fraction_both","refis_fy2025","refis_fy2026"],sensitivity)
    recs=[]
    a,b=regions[2025],regions[2026]
    joint=sorted(s for s in a.keys() & b.keys() if sum(a[s]) and sum(b[s]))
    n_a=sum(sum(a[s]) for s in joint)
    n_b=sum(sum(b[s]) for s in joint)
    for s in joint:
        w_a,w_b=sum(a[s])/n_a,sum(b[s])/n_b
        p_a,p_b=a[s][0]/sum(a[s]),b[s][0]/sum(b[s])
        recs.append([s,*a[s],*b[s],w_a,w_b,p_a,p_b,100*(w_a+w_b)/2*(p_b-p_a),100*(p_a+p_b)/2*(w_b-w_a)])
    write_csv("state-decomposition.csv",["jurisdiction","cash_2025","irrrl_2025","cash_2026","irrrl_2026","weight_2025","weight_2026","share_2025","share_2026","within_pp","mix_pp"],recs)
    within=sum(r[-2] for r in recs)
    mix=sum(r[-1] for r in recs)
    delta=100*(sum(b[s][0] for s in joint)/n_b - sum(a[s][0] for s in joint)/n_a)
    assert abs(within+mix-delta)<1e-9
    print("Matched jurisdiction universe:",n_a,n_b,"Within and mix in percentage points:",within,mix)

if __name__ == "__main__":
    main()
