"""Reproduce the FSI FY2025 IRS / 2023 Census CPA-office comparison.

Python 3 standard library only. Download the two public source files linked on
the article, then run:
python reproduce-cpa-office-study.py 25db-1-03-nr.xlsx cbp23st.zip output.csv

Counts are returns and employer establishments, not clients, firms or capacity.
"""
import csv, hashlib, io, sys, zipfile
import xml.etree.ElementTree as ET
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path

FIPS = dict(zip(
    '01 02 04 05 06 08 09 10 11 12 13 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 44 45 46 47 48 49 50 51 53 54 55 56'.split(),
    'Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|District of Columbia|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming'.split('|')))
NS = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}

def read_irs(path):
    with zipfile.ZipFile(path) as archive:
        shared = [''.join(n.itertext()) for n in ET.fromstring(archive.read('xl/sharedStrings.xml')).findall('s:si', NS)]
        sheet = ET.fromstring(archive.read('xl/worksheets/sheet1.xml'))
        data = {}
        for row in sheet.findall('.//s:row', NS):
            cells = {}
            for cell in row.findall('s:c', NS):
                value = cell.find('s:v', NS)
                if value is None: continue
                column = ''.join(c for c in cell.attrib['r'] if c.isalpha())
                cells[column] = shared[int(value.text)] if cell.attrib.get('t') == 's' else value.text
            if cells.get('A') in FIPS.values():
                data[cells['A']] = (int(cells['D']), int(cells['E']))
        if len(data) != 51: raise ValueError('Expected 51 IRS state/DC rows; source layout may have changed')
        return data

def calculate(irs_path, cbp_path):
    irs = read_irs(irs_path)
    with zipfile.ZipFile(cbp_path) as archive:
        reader = csv.DictReader(io.TextIOWrapper(archive.open('cbp23st.txt'), encoding='utf-8-sig'))
        cbp = {FIPS[r['fipstate']]: r for r in reader if r['fipstate'] in FIPS and r['naics'] == '541211' and r['lfo'] == '-'}
    if len(cbp) != 51: raise ValueError('Expected 51 Census employer-establishment rows')
    rows = []
    for state, (scorp, partnership) in irs.items():
        offices = int(cbp[state]['est'])
        ratio = Decimal(scorp + partnership) / offices
        rows.append(dict(state=state, s_corporation_returns=scorp, partnership_returns=partnership,
                         cpa_employer_offices=offices, pass_through_returns=scorp + partnership,
                         returns_per_office_unrounded=str(ratio),
                         returns_per_office_rounded=int(ratio.quantize(Decimal('1'), rounding=ROUND_HALF_UP)),
                         s_corporation_returns_per_office_rounded=int((Decimal(scorp) / offices).quantize(Decimal('1'), rounding=ROUND_HALF_UP)),
                         irs_fiscal_year=2025, census_employer_office_year=2023))
    rows.sort(key=lambda r: Decimal(r['returns_per_office_unrounded']), reverse=True)
    rows = [dict(rank=i + 1, **r) for i, r in enumerate(rows)]
    totals = dict(s_corporation_returns=sum(r['s_corporation_returns'] for r in rows),
                  partnership_returns=sum(r['partnership_returns'] for r in rows),
                  cpa_employer_offices=sum(r['cpa_employer_offices'] for r in rows),
                  offices_under_five=sum(int(r['n<5']) for r in cbp.values()),
                  offices_under_ten=sum(int(r['n<5']) + int(r['n5_9']) for r in cbp.values()))
    return rows, totals

if __name__ == '__main__':
    if len(sys.argv) != 4: raise SystemExit(__doc__)
    rows, totals = calculate(sys.argv[1], sys.argv[2])
    with open(sys.argv[3], 'w', newline='', encoding='utf-8') as stream:
        writer = csv.DictWriter(stream, fieldnames=list(rows[0])); writer.writeheader(); writer.writerows(rows)
    print('51 state/DC rows written. Totals:', totals)
    for filename in sys.argv[1:3]: print(Path(filename).name, 'SHA256', hashlib.sha256(Path(filename).read_bytes()).hexdigest())
