"""Reproduce the national fiscal reconciliation from preserved official originals.

Run beside download-manifest.json. Default is read-only; --write saves the ledger.
Uses the existing research runtime (openpyxl and pypdf), never rewrites originals.
"""
from pathlib import Path
from decimal import Decimal
import hashlib
import json
import re
import sys
import openpyxl
from pypdf import PdfReader

ROOT = Path(__file__).resolve().parent
checks, observations, calculations = [], [], []
manifest = {s['id']: s for s in json.loads((ROOT / 'download-manifest.json').read_text())}
source_ids = ['gfs-commonwealth', 'gfs-states', 'gfs-local', 'gfs-control-nfd', 'gfs-all-government', 'fbo-2025-26']

def check(label, actual, expected, tolerance=0):
    if isinstance(actual, (int, float, Decimal)) and not isinstance(actual, bool):
        passed = abs(Decimal(str(actual)) - Decimal(str(expected))) <= Decimal(str(tolerance))
    else:
        passed = actual == expected
    checks.append({'check': label, 'actual': actual, 'expected': expected, 'tolerance': tolerance, 'passed': passed})
    if not passed:
        raise ValueError(f'{label}: {actual} != {expected}, tolerance {tolerance}')

for sid in source_ids:
    source = manifest[sid]
    content = (ROOT / source['path']).read_bytes()
    check('SHA256 ' + sid, hashlib.sha256(content).hexdigest(), source['sha256'])
    check('bytes ' + sid, len(content), source['bytes'])

def observe(oid, label, value, sid, locator, period, basis, scope):
    check('numeric ' + oid, isinstance(value, (int, float)), True)
    observations.append({'id': oid, 'label': label, 'value': value, 'unit': 'AUD million', 'period': period, 'basis': basis, 'scope': scope, 'source_id': sid, 'source_locator': locator})
    return value

scopes = {'gfs-commonwealth': 'Commonwealth general government', 'gfs-states': 'All state and territory general governments', 'gfs-local': 'All local general governments', 'gfs-control-nfd': 'General government control not further defined (universities)', 'gfs-all-government': 'All Australian general government, consolidated'}
gfs = {}
for sid in source_ids[:-1]:
    book = openpyxl.load_workbook(ROOT / manifest[sid]['path'], read_only=True, data_only=True)
    sheet = book['Table_1']
    check('period ' + sid, sheet['K5'].value, '2024-25')
    check('unit ' + sid, sheet['K6'].value, '$m')
    selected = {}
    for row in sheet.iter_rows(min_row=7, max_col=11):
        label = row[0].value
        if not label or not isinstance(row[10].value, (int, float)):
            continue
        label = ' '.join(label.split())
        key = label.lower()
        check('unique row ' + sid + ' ' + key, key in selected, False)
        oid = f'{sid}:{row[10].coordinate}'
        selected[key] = {'id': oid, 'value': observe(oid, label, row[10].value, sid, f'Table_1!{row[10].coordinate}', '2024-25', 'GFS accrual', scopes[sid])}
    gfs[sid] = selected
    # Components above Total GFS revenue have differing row positions by level.
    total_row = next(row for row in sheet.iter_rows(min_row=7, max_col=11) if ' '.join(str(row[0].value).split()) == 'Total GFS revenue')
    components = [r[10].value for r in sheet.iter_rows(min_row=8, max_row=total_row[0].row-1, max_col=11)]
    check('revenue components ' + sid, sum(components), total_row[10].value, 2)
    book.close()

def gv(sid, label):
    return gfs[sid][label.lower()]['value']

def derive(oid, label, value, formula, input_ids, period, basis):
    calculations.append({'id': oid, 'label': label, 'value': round(value, 10), 'unit': 'percent' if oid.endswith('_percent') else 'AUD million', 'period': period, 'basis': basis, 'formula': formula, 'inputs': input_ids})
    return value

levels = source_ids[:4]
sum_revenue = sum(gv(s, 'Total GFS revenue') for s in levels)
derive('unconsolidated_sum', 'Sum of four published level/control revenue totals', sum_revenue, 'sum(level revenue totals)', [gfs[s]['total gfs revenue']['id'] for s in levels], '2024-25', 'GFS accrual, arithmetic sum before cross-level consolidation')
eliminations = sum_revenue - gv('gfs-all-government', 'Total GFS revenue')
derive('consolidation_difference', 'Difference removed when levels are combined', eliminations, 'unconsolidated_sum - consolidated revenue', ['unconsolidated_sum', gfs['gfs-all-government']['total gfs revenue']['id']], '2024-25', 'GFS accrual, derived difference; includes multiple transaction types')
check('published level revenue sum', sum_revenue, 1242784)
check('consolidation difference', eliminations, 220433)
for sid in levels:
    current = gv(sid, 'Current grants and subsidies')
    capital = gv(sid, 'Capital grants') if 'capital grants' in gfs[sid] else None
    total = gv(sid, 'Total GFS revenue')
    if capital is not None:
        inputs = [gfs[sid][k]['id'] for k in ['current grants and subsidies', 'capital grants', 'total gfs revenue']]
        derive(sid + '_grant_share_percent', 'Displayed current grants/subsidies and capital grants as share of revenue', (current+capital)/total*100, '(current grants and subsidies + capital grants) / total revenue * 100', inputs, '2024-25', 'GFS accrual, same level; not a classification of ultimate funders')
    check('operating balance ' + sid, total-gv(sid, 'Total GFS expenses'), gv(sid, 'GFS Net operating balance'), 1)
    check('net lending/borrowing ' + sid, gv(sid, 'GFS Net operating balance')-gv(sid, 'Total net acquisition of non-financial assets'), gv(sid, 'GFS Net Lending(+)/Borrowing(-)'), 1)
all_sid = 'gfs-all-government'
check('consolidated operating balance', gv(all_sid, 'Total GFS revenue')-gv(all_sid, 'Total GFS expenses'), gv(all_sid, 'GFS Net operating balance'), 1)
check('consolidated net lending/borrowing', gv(all_sid, 'GFS Net operating balance')-gv(all_sid, 'Total net acquisition of non-financial assets'), gv(all_sid, 'GFS Net Lending(+)/Borrowing(-)'), 1)

reader = PdfReader(ROOT / manifest['fbo-2025-26']['path'])
pattern = re.compile(r'^\s*(.*?)\s+(-?[\d,]+)\s+(-?[\d,]+)\s+(-?[\d,]+)\s+(-?[\d,]+)\s*$', re.M)
fbo = {}
for page in [32, 33]:
    text = reader.pages[page-1].extract_text(extraction_mode='layout')
    check(f'FBO table identity page {page}', 'Table 2.3:' in text and 'Outcome' in text, True)
    seen = {}
    for m in pattern.finditer(text):
        label = ' '.join(m[1].split())
        if not re.search('[A-Za-z]', label):
            continue
        occurrence = seen.get(label, 0) + 1
        seen[label] = occurrence
        key = f'{label}#{occurrence}'
        value = int(m[4].replace(',', ''))  # Third numeric column is annual Outcome.
        oid = f'fbo:p{page}:{key}'
        observe(oid, label, value, 'fbo-2025-26', f'Table 2.3, printed p{page-10} / PDF p{page}, annual Outcome column, occurrence {occurrence}', '2025-26', 'cash, annual actual', 'Commonwealth general government')
        fbo[(page, key)] = {'id': oid, 'value': value}

def fv(label, page=32, occurrence=1):
    return fbo[(page, f'{label}#{occurrence}')]['value']

receipts = fv('Total operating receipts')
payments = fv('Total operating payments')
operating = fv('Net cash flows from operating activities')
assets = fv('non-financial assets')
policy = fv('financial assets for policy purposes')
liquidity = fv('financial assets for liquidity purposes')
financing = fv('Net cash flows from financing activities')
check('operating receipt components', sum(fv(x) for x in ['Taxes received', 'Receipts from sales of goods and services', 'Interest receipts', 'Dividends, distributions and income tax equivalents', 'Other receipts']), receipts, 1)
check('operating payment components', sum(fv(x) for x in ['Payments to employees(c)', 'Payments for goods and services', 'Grants and subsidies paid', 'Interest paid', 'Personal benefit payments', 'Other payments(c)']), payments, 1)
check('operating cash balance', receipts+payments, operating)
check('nonfinancial asset flows', fv('Sales of non-financial assets')+fv('Purchases of non-financial assets'), assets)
check('financing receipts', fv('Borrowing')+fv('Other financing'), fv('Total cash receipts from financing activities'))
check('financing payments', fv('Borrowing', occurrence=2)+fv('Other financing', occurrence=2), fv('Total cash payments for financing activities'), 1)
check('net financing', fv('Total cash receipts from financing activities')+fv('Total cash payments for financing activities'), financing, 1)
check('cash movement from activities', operating+assets+policy+liquidity+financing, fv('Net increase/(decrease) in cash held'))
check('GFS cash deficit', operating+assets, fv('GFS cash surplus(+)/deficit(-)(d)', page=33))
check('underlying cash deficit includes lease principal', operating+assets+fv('plus Principal payments of lease liabilities(e)', page=33), fv('Equals underlying cash balance(f)', page=33))
check('headline cash balance includes policy investments', fv('Equals underlying cash balance(f)', page=33)+policy, fv('Equals headline cash balance', page=33), 1)
prior_receipts = {r['label']: r['value'] for r in json.loads((ROOT/'commonwealth-receipts.json').read_text())['records']}
check('Table 1.3 receipts include asset sales', receipts+fv('Sales of non-financial assets'), prior_receipts['Total receipts'])
check('Table 1.3 other receipts include asset sales', fv('Other receipts')+fv('Sales of non-financial assets'), prior_receipts['Other non-taxation receipts'])
derive('net_borrowing_cash', 'Borrowing cash receipts less borrowing cash payments', fv('Borrowing')+fv('Borrowing', occurrence=2), 'borrowing receipts + signed borrowing payments', [fbo[(32, f'Borrowing#{n}')]['id'] for n in [1, 2]], '2025-26', 'cash, Commonwealth general government; differs from GFS accrual net borrowing')

gst_text = reader.pages[73].extract_text(extraction_mode='layout')
gst = {}
for table, next_table in [('3.6', '3.7'), ('3.7', '3.8')]:
    block = gst_text.split('Table '+table+':')[1].split('Table '+next_table+':')[0]
    for m in re.finditer(r'^\s*([A-Za-z][^\n]*?)\s{2,}(-?[\d,]+)\s*$', block, re.M):
        label, value = ' '.join(m[1].split()), int(m[2].replace(',', ''))
        key = f'{table}:{label}'
        gst[key] = observe('gst:'+key, label, value, 'fbo-2025-26', f'Table {table}, printed p64 / PDF p74, Total column', '2025-26', 'GST reconciliation; entitlement subject to ministerial determination', 'Commonwealth to states and territories')
check('GST accrual to cash', gst['3.6:GST revenue']-gst['3.6:less Change in GST receivables'], gst['3.6:GST receipts'], 1)
check('GST cash to entitlement', gst['3.6:GST receipts']-gst['3.6:less Non-GIC penalties collected']-gst['3.6:less Net GST collected by Commonwealth agencies but not yet remitted to the ATO']+gst['3.6:plus GST pool boost'], gst["3.6:States' GST entitlement(a)"])
check('GST entitlement to advances', gst["3.7:States' GST entitlement(a)"]-gst['3.7:less Advances of GST made throughout 2025-26'], gst['3.7:equals Balancing adjustment'], 1)

transfers = {}
jurisdictions = ['NSW', 'VIC', 'QLD', 'WA', 'SA', 'TAS', 'ACT', 'NT', 'Total']
decimal_token = r'(?:\d[\d,]*\.\d|-)'
row_pattern = re.compile(r'^\s*(.*?)\s+(' + decimal_token + r'(?:\s+' + decimal_token + r'){8})\s*$', re.M)
for page, table in [(95, '3.22'), (96, '3.23')]:
    # Plain extraction preserves rotated tables without relying on text orientation.
    text = reader.pages[page-1].extract_text()
    check('transfer header ' + table, 'NSW VIC QLD WA SA TAS ACT NT Total' in text, True)
    seen = {}
    for m in row_pattern.finditer(text):
        label = ' '.join(m[1].split())
        if not re.search('[A-Za-z]', label):
            continue
        occurrence = seen.get(label, 0)+1
        seen[label] = occurrence
        values = [0.0 if s == '-' else float(s.replace(',', '')) for s in m[2].split()]
        key = f'{table}:{label}#{occurrence}'
        transfers[key] = dict(zip(jurisdictions, values))
        for jurisdiction, value in zip(jurisdictions, values):
            observe(f'transfer:{key}:{jurisdiction}', label, value, 'fbo-2025-26', f'Table {table}, printed p{page-10} / PDF p{page}, {jurisdiction} column, row occurrence {occurrence}', '2025-26', 'expense basis; GST entitlement subject to ministerial determination; displayed dash means nil', 'Australian Government payments under federal financial relations')
        check('jurisdiction sum ' + key, sum(values[:-1]), values[-1], 0.3)

for j in jurisdictions:
    gross = transfers['3.23:Total payments to the states#1'][j]
    through = transfers["3.23:less payments 'through' the states#1"][j]
    local_assistance = transfers['3.23:governments#1'][j]
    local_direct = transfers['3.23:governments#2'][j]
    own = transfers['3.23:for own-purpose expenses#1'][j]
    check('own-purpose transfer bridge ' + j, gross-through-local_assistance-local_direct, own, 0.2)
    gst_value = transfers['3.22:GST entitlement(a)#1'][j]
    hfe = transfers['3.22:HFE transition payments#1'][j]
    other = transfers['3.22:Total other general revenue assistance#1'][j]
    check('general assistance components ' + j, gst_value+hfe+other, transfers['3.22:Total#1'][j], 0.2)

ledger = {'schema_version': 'fiscal-reconciliation-v1', 'evidence_as_at': '2026-10-04', 'note': 'Selected observations include components, subtotals and overlapping per-jurisdiction totals. Do not sum all records. Preserve different years and accounting bases. Small published rounding differences are retained.', 'sources': [{k: manifest[sid][k] for k in ['id', 'url', 'path', 'sha256', 'bytes']} for sid in source_ids], 'observations': observations, 'calculations': calculations, 'validation': {'status': 'passed', 'scope': 'Original hashes, source cells and PDF annual Outcome columns, accounting identities, transfer and GST reconciliations. Does not audit government accounts or establish ultimate tax incidence.', 'checks': checks}}
output = json.dumps(ledger, indent=2, ensure_ascii=False, allow_nan=False)+'\n'
destination = ROOT/'fiscal-reconciliation.json'
if '--write' in sys.argv:
    destination.write_text(output)
else:
    if destination.read_text() != output:
        raise ValueError('Saved fiscal ledger does not reproduce')
print(json.dumps({'status': 'passed', 'observations': len(observations), 'calculations': len(calculations), 'source_and_accounting_checks': len(checks), 'sources': len(source_ids)}, indent=2))