"""Reproduce selected public financial data from the preserved source snapshots.

Usage: python3 extract.py [--write]
Default is read-only: verify checksums, identities, selected controls, and saved output.
Dependencies match the repository research environment: openpyxl, lxml, pypdf.
"""
from pathlib import Path
from collections import Counter
import hashlib, json, re, sys
import openpyxl
from lxml import html
from pypdf import PdfReader

ROOT = Path(__file__).resolve().parent
checks = []
def check(name, condition, detail=None):
    checks.append({'name': name, 'passed': bool(condition), 'detail': detail})
    if not condition: raise ValueError(f'{name}: {detail}')
def close(name, value, expected, tolerance=0):
    check(name, abs(value-expected)<=tolerance, {'actual':value,'expected':expected,'tolerance':tolerance})
def write_json(name, value):
    text=json.dumps(value,indent=2,ensure_ascii=False,allow_nan=False)+'\n'
    if '--write' in sys.argv: (ROOT/name).write_text(text)
    else: check('reproduce '+name,(ROOT/name).read_text()==text)
def table_by_caption(source, caption):
    tree=html.fromstring((ROOT/'raw'/f'{source}.html').read_bytes())
    tables=[t for t in tree.xpath('//table') if ' '.join(''.join(t.xpath('./caption//text()')).split())==caption]
    check('unique table '+caption,len(tables)==1)
    return tables[0]
def records(table):
    return [[' '.join(c.text_content().split()) for c in r.xpath('./th|./td')] for r in table.xpath('.//tr')]
def number(value):
    if value in ('na','np','..','-','', '#VALUE!'): return None
    return float(value.replace(',',''))

manifest=json.loads((ROOT/'download-manifest.json').read_text())
for item in manifest:
    if item['status']=='downloaded':
        body=(ROOT/item['path']).read_bytes()
        check('SHA256 '+item['id'],hashlib.sha256(body).hexdigest()==item['sha256'])

# Preserve all income years and blank fields in the ATO workbook. Rank only 2024-25.
w=openpyxl.load_workbook(ROOT/'raw/ato-entities-2024-25.xlsx',read_only=True,data_only=True)
s=w['Income tax details']
check('ATO headings',[c.value for c in next(s.iter_rows())]==['Name','ABN','Total income $','Taxable income $','Tax payable $','Income year'])
entities=[]
for i,row in enumerate(s.iter_rows(min_row=2,values_only=True),2):
    name,abn,income,taxable,payable,year=row
    if name is None: continue
    check(f'ATO numeric fields row {i}',all(v is None or isinstance(v,(int,float)) for v in (income,taxable,payable)))
    entities.append({'name':name,'abn':None if abn is None else str(abn),'total_income_aud':income,'taxable_income_aud':taxable,'tax_payable_aud':payable,'income_year':year,'source_id':'ato-entities-2024-25','source_locator':f'Income tax details!A{i}:F{i}'})
current=[e for e in entities if e['income_year']=='2024-25']
close('2024-25 corporate entity count',len(current),4299)
identities=[(e['name'],e['abn'],e['income_year']) for e in entities]
check('unique ATO entity identities',len(identities)==len(set(identities)))
total_payable=sum(e['tax_payable_aud'] for e in current if e['tax_payable_aud'] is not None)
close('ATO payable rounded to 87.5 billion',total_payable/1e9,87.5,.05)
close('ATO total income rounded to 3342.2 billion',sum(e['total_income_aud'] for e in current)/1e9,3342.2,.05)
prrt=[]
for i,(name,abn,payable) in enumerate(w['PRRT details'].iter_rows(min_row=2,values_only=True),2):
    if name is not None: prrt.append({'name':name,'abn':None if abn is None else str(abn),'prrt_payable_aud':payable,'income_year':'2024-25','source_id':'ato-entities-2024-25','source_locator':f'PRRT details!A{i}:C{i}'})
close('PRRT entities',len(prrt),21)
close('PRRT payable rounded millions',sum(e['prrt_payable_aud'] for e in prrt)/1e6,1872.8,.05)
top=sorted(current,key=lambda e:e['tax_payable_aud'] or 0,reverse=True)[:25]
write_json('corporate-entities.json',{'notes':'Tax return figures, not audited cash payments. Null retains the publisher blank for zero or negative amounts, not an inferred exact zero. Names represent reporting entities, not necessarily entire economic groups. Includes late prior-year returns, identified by income_year.','records':entities,'prrt_records':prrt})
write_json('corporate-summary.json',{'income_year':'2024-25','current_entities':len(current),'all_rows':len(entities),'rows_by_year':dict(sorted(Counter(e['income_year'] for e in entities).items())),'tax_payable_aud':total_payable,'blank_payable_count':sum(e['tax_payable_aud'] is None for e in current),'top_25_payable_share_percent':sum(e['tax_payable_aud'] for e in top)/total_payable*100,'top_25':top,'prrt_payable_aud':sum(e['prrt_payable_aud'] for e in prrt)} )

# ABS tax classifications include subtotals and consolidation adjustments; do not sum every row.
w=openpyxl.load_workbook(ROOT/'raw/tax-tables.xlsx',read_only=True,data_only=True)
tax=[]
for s in list(w)[1:]:
    check('ABS period '+s.title,s['K5'].value=='2024-25' and s['K6'].value=='$m')
    for row in s:
        if len(row)>=11 and isinstance(row[10].value,(int,float)) and isinstance(row[0].value,str):
            label=row[0].value.strip()
            tax.append({'jurisdiction':s['A4'].value,'label':label,'value':row[10].value,'unit':'AUD million','period':'2024-25','basis':'GFS accrual','row_role':'subtotal_or_total' if label.lower().startswith('total') else ('consolidation_information' if label.startswith('Taxes received') else 'category'),'source_id':'tax-tables','source_locator':f'{s.title}!{row[10].coordinate}'})
close('Commonwealth GFS taxes',w['Table_1']['K45'].value,675173)
write_json('tax-observations.json',{'note':'Rows include nested totals; never add all rows. Published state and local tables have their own consolidation boundaries. These are statistical classes, not a complete statute register.','records':tax})

w=openpyxl.load_workbook(ROOT/'raw/gfs-all-government.xlsx',read_only=True,data_only=True)
for sheet in ['Table_1','Table_4']:
    check('GFS period '+sheet,w[sheet]['K5'].value=='2024-25' and w[sheet]['K6'].value=='$m')
gfs=[]
for row in list(w['Table_1'].iter_rows())[7:15]:
    if isinstance(row[10].value,(int,float)):gfs.append({'label':row[0].value,'value':row[10].value,'unit':'AUD million','period':'2024-25','scope':'All Australian general government consolidated','basis':'GFS accrual','source_id':'gfs-all-government','source_locator':f'Table_1!{row[10].coordinate}'})
close('GFS revenue components',sum(r['value'] for r in gfs[:-1]),gfs[-1]['value'],4)
expenses=[]
for row in w['Table_4'].iter_rows(min_row=7):
    if len(row)>=11 and isinstance(row[10].value,(int,float)) and row[0].value:
        expenses.append({'label':row[0].value.strip(),'value':row[10].value,'unit':'AUD million','period':'2024-25','scope':'All Australian general government consolidated','basis':'GFS accrual expenses','source_id':'gfs-all-government','source_locator':f'Table_4!{row[10].coordinate}'})
close('GFS purpose expense reconciliation',sum(r['value'] for r in expenses[:-1]),expenses[-1]['value'],4)
write_json('government-observations.json',{'revenue':gfs,'expenses':expenses})

# Read the 2025-26 outcome column, not the neighbouring forecast or variance column.
p=PdfReader(ROOT/'raw/fbo-2025-26.pdf').pages[15].extract_text()
check('FBO table identity','Table 1.3:' in p and '2025-26' in p)
fiscal=[]
for line in p.splitlines():
    m=re.match(r'^\s*(.*?)\s+(-?[\d,]+)\s+(-?[\d,]+)\s+(-?[\d,]+)\s*$',line)
    if m and re.search('[a-zA-Z]',m[1]):
        fiscal.append({'label':m[1].strip(),'value':int(m[3].replace(',','')),'unit':'AUD million','period':'2025-26','scope':'Commonwealth general government','basis':'cash receipts actual','source_id':'fbo-2025-26','source_locator':'PDF page 16, printed page 6, Table 1.3, Outcome column'})
f={r['label']:r['value'] for r in fiscal}
close('FBO tax plus non-tax',f['Taxation receipts']+f['Non-taxation receipts'],f['Total receipts'])
close('FBO non-tax subcategories',sum(f[k] for k in ['Sales of goods and services','Interest received','Dividends and distributions','Other non-taxation receipts']),f['Non-taxation receipts'],2.5)
close('FBO net personal tax',f['Gross income tax withholding']+f['Gross other individuals and trusts']-f['less: Refunds'],f['Total individuals and other withholding tax'],2)
write_json('commonwealth-receipts.json',{'note':'Includes component rows, subtotals and memorandum totals. Do not sum all rows. Independent rounding is retained.','records':fiscal,'derived_non_tax_share_percent':f['Non-taxation receipts']/f['Total receipts']*100})

international=[]
captions=['Level of foreign investment in Australia, Top 20 countries','Foreign investment in Australia, Direct investment transactions, Top 10 countries','Foreign investment in Australia, Portfolio investment transactions, Top 10 countries','Foreign investment in Australia, income debits, Top 10 countries','Australian investment abroad, income credits, Top 10 countries']
for caption in captions:
    rows=records(table_by_caption('iip-2025',caption))
    check('IIP latest column '+caption,any(len(row)==6 and row[0]=='Country' and row[-1].startswith('2025') for row in rows))
    for cells in rows:
        if len(cells)==6 and cells[0]!='Country':
            international.append({'table':caption,'country':cells[0],'value':number(cells[-1]),'source_value':cells[-1],'unit':'AUD million','period':'2025','basis':'year-end stock' if caption.startswith('Level') else 'annual transactions or income accrual','source_id':'iip-2025','source_locator':caption+', 2025 column'})
write_json('international-observations.json',{'note':'Country of immediate counterparty does not establish ultimate beneficial ownership or foreign-government ownership. Headline stocks are not annual funding. Income includes reinvested earnings. These top-country tables are partial distributions, with a published whole-economy total.','records':international})

close('2026-27 personal tax illustration',26800*.15+55000*.30,20520)
close('2026-27 personal rate base at 135000',26800*.15+90000*.30,31020)
close('2026-27 personal rate base at 190000',26800*.15+90000*.30+55000*.37,51370)
close('GST illustration credit reconciliation',10+(20-10),20)
close('Transgrid published ownership shares',sum([22.505,19.99,15.01,12.505,10,9.995,9.995]),100,.000001)
close('Ausgrid published ownership shares',sum([49.6,8.4,25.2,16.8]),100,.000001)
close('DFAT calendar 2025 trade surplus',665210-658308,6902)
close('ABS June quarter 2026 current account reconciliation',-5104-21863-253,-27220)

validation={'status':'passed','scope':'Snapshot hashes, selected extraction identities, corporate population and aggregate controls, ABS classification extraction, FBO column and rounding checks. Does not independently audit publishers or validate every source publication.','checks':checks}
if '--write' in sys.argv: (ROOT/'validation.json').write_text(json.dumps(validation,indent=2)+'\n')
print(json.dumps({'status':'passed','checks':len(checks),'corporate_rows':len(entities),'current_entities':len(current),'tax_rows':len(tax),'international_rows':len(international),'non_tax_share_percent':f['Non-taxation receipts']/f['Total receipts']*100,'top25_share_percent':sum(e['tax_payable_aud'] for e in top)/total_payable*100},indent=2))