"""Validate this supplied fixture independently of work/reconcile.py. Run from the workspace root: python output/validate.py Reads inputs and submitted CSVs; writes output/review-checks.md only. Not a general-purpose accounting system; unsupported source conflicts fail loudly. """ import csv import hashlib import json from collections import Counter, defaultdict from datetime import datetime from decimal import Decimal from pathlib import Path checks = [] def check(condition, description): if not condition: raise AssertionError(description) checks.append(description) def read(path): with open(path, newline='', encoding='utf-8') as f: reader = csv.DictReader(f) fields = reader.fieldnames rows = list(reader) assert fields and len(fields) == len(set(fields)) assert all(None not in r and all(v is not None for v in r.values()) for r in rows) return rows def norm_date(s): for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%b %d %Y'): try: return datetime.strptime(s, fmt).strftime('%Y-%m-%d') except ValueError: pass raise ValueError('Unresolved input date: ' + s) def norm_money(s): return '' if not s else format(Decimal(s.replace('CAD', '').replace('$', '').strip()), '.2f') def october(s): return '2026-10-01' <= s <= '2026-10-31' input_paths = [Path('inputs') / f for f in ('sales.csv', 'refunds.csv', 'ads.csv', 'policy.md')] before = {p: hashlib.sha256(p.read_bytes()).hexdigest() for p in input_paths} raw = {k: read('inputs/' + k + '.csv') for k in ('sales', 'refunds', 'ads')} clean = {k: read('output/cleaned-' + k + '.csv') for k in raw} ex = read('output/exceptions.csv') expected = {} retained = {} # Independently derive canonical records using business IDs, not the prior script's row lists. for kind, idcol in [('sales', 'order_id'), ('refunds', 'refund_id'), ('ads', 'ad_id')]: grouped = defaultdict(list) for r in raw[kind]: grouped[r[idcol]].append(r) for ident, group in grouped.items(): unique = [] seen = {} for r in group: business = tuple((k, v) for k, v in r.items() if k != 'row_id') if business in seen: expected[(kind, r['row_id'])] = 'duplicate' retained[(kind, r['row_id'])] = seen[business]['row_id'] else: seen[business] = r unique.append(r) if len(unique) > 1: assert kind == 'sales', 'No conflict-resolution rule supplied for conflicting refund/ad records' winner = max(unique, key=lambda r: datetime.fromisoformat(r['updated_at'])) assert sum(r['updated_at'] == winner['updated_at'] for r in unique) == 1 for r in unique: if r is not winner: expected[(kind, r['row_id'])] = 'superseded' retained[(kind, r['row_id'])] = winner['row_id'] else: winner = unique[0] retained[(kind, ident)] = winner paid_orders = {} for r in raw['sales']: key = ('sales', r['row_id']) if key in expected: continue if r['status'] != 'paid': expected[key] = r['status'] + '_order' elif not october(norm_date(r['date'])): expected[key] = 'outside_period' else: paid_orders[r['order_id']] = r for r in raw['refunds']: key = ('refunds', r['row_id']) if key in expected: continue if r['status'] != 'settled': expected[key] = 'pending_refund' elif not october(norm_date(r['settled_date'])): expected[key] = 'outside_period' elif r['order_id'] not in paid_orders: expected[key] = 'unmatched_order' elif not r['amount_cad']: expected[key] = 'missing_amount' for r in raw['ads']: key = ('ads', r['row_id']) if key not in expected and not october(norm_date(r['date'])): expected[key] = 'outside_period' for kind in raw: check(Counter(r['row_id'] for r in raw[kind]) == Counter(r['row_id'] for r in clean[kind]), kind + ': exact source row-ID multiplicities preserved') byid = {r['row_id']: r for r in clean[kind]} check(len(byid) == len(clean[kind]), kind + ': output row IDs unique') for src in raw[kind]: dst = byid[src['row_id']] for field, value in src.items(): if field in ('date', 'settled_date'): value = norm_date(value) elif field in ('gross_cad', 'amount_cad', 'spend_cad'): value = norm_money(value) elif field == 'product': value = value.strip().title() assert value in ('Workshop', 'Course') elif field == 'channel': value = value.strip().lower() assert dst[field] == value, (kind, src['row_id'], field) key = (kind, src['row_id']) reason = expected.get(key, '') assert dst['included_in_october'] == ('no' if reason else 'yes') assert dst['exclusion_reason'] == reason assert dst['retained_row_id'] == retained.get(key, '') if kind == 'refunds': order = paid_orders.get(src['order_id']) assert dst['linked_sales_row_id'] == (order['row_id'] if order else '') assert dst['product'] == (order['product'].strip().title() if order else '') check(True, kind + ': every original field and derived inclusion, reason, retained-row and linkage value verified against raw evidence') exc_by_key = {(r['source_file'].split('/')[-1].split('.')[0], r['row_id']): r for r in ex} check(len(exc_by_key) == len(ex) == 13 and set(exc_by_key) == set(expected), 'Exactly one exception per independently derived exclusion (13); no extra exceptions') for key, reason in expected.items(): e = exc_by_key[key] assert e['reason_code'] == reason and e['explanation'] assert e['retained_row_id'] == retained.get(key, '') source = next(r for r in raw[key[0]] if r['row_id'] == key[1]) idcol = {'sales': 'order_id', 'refunds': 'refund_id', 'ads': 'ad_id'}[key[0]] assert e['record_id'] == source[idcol] assert 'inputs/policy.md' in e['policy_reference'] check(True, 'Exception business IDs, reasons, retained-row references and policy citations verified') rows_for = lambda kind, product: [r for r in clean[kind] if r['included_in_october'] == 'yes' and (product == 'Combined' or r['product'] == product)] total = lambda rows, field: sum((Decimal(r[field]) for r in rows), Decimal(0)) results = {} for product in ('Workshop', 'Course', 'Combined'): s, r, a = [rows_for(k, product) for k in ('sales', 'refunds', 'ads')] gross, refund, spend = total(s, 'gross_cad'), total(r, 'amount_cad'), total(a, 'spend_cad') net = gross - refund results[product] = (len(s), gross, refund, net, spend, net-spend, net/len(s)) expected_totals = {'Workshop': (5,1920,240,1680,300,1380,336), 'Course': (4,900,60,840,150,690,210), 'Combined': (9,2820,300,2520,450,2070,280)} check(results == expected_totals, 'All seven metrics verified for Workshop, Course and combined using Decimal arithmetic') summary = Path('output/summary.md').read_text(encoding='utf-8') labels = ('Paid order count', 'Gross sales', 'Applied refunds (deduction)', 'Net revenue', 'Advertising spend', 'Contribution after advertising', 'Net revenue per paid order') for index, label in enumerate(labels): line = next(line for line in summary.splitlines() if line.startswith('| ' + label + ' |')) cells = [cell.strip().replace('*', '').replace(',', '') for cell in line.strip('|').split('|')][1:] assert [Decimal(c) for c in cells] == [Decimal(results[p][index]) for p in ('Workshop', 'Course', 'Combined')] check(True, 'All 21 numerical results in summary.md match the independently derived CSV totals') for kind, field, counts, amounts in [('sales','gross_cad',(15,9,6),(4140,1320,2820)),('refunds','amount_cad',(7,2,5),(960,660,300)),('ads','spend_cad',(7,5,2),(630,180,450))]: rows = clean[kind] yes = [r for r in rows if r['included_in_october'] == 'yes'] no = [r for r in rows if r['included_in_october'] == 'no'] assert (len(rows), len(yes), len(no)) == counts known = lambda rs: total([r for r in rs if r[field] != ''], field) assert (known(rows), known(no), known(yes)) == amounts check(True, 'Row-count and known-amount control totals verified; R06 remains the only missing amount') check([(r['row_id'],r['amount_cad']) for r in clean['refunds'] if not r['amount_cad']] == [('R06','')], 'Unknown R06 amount preserved as blank, not zero') known_refunds = defaultdict(Decimal) for r in clean['refunds']: if r['status'] == 'settled' and r['amount_cad'] and r['linked_sales_row_id'] and r['exclusion_reason'] != 'duplicate': known_refunds[r['order_id']] += Decimal(r['amount_cad']) check(all(value <= Decimal(norm_money(paid_orders[oid]['gross_cad'])) for oid, value in known_refunds.items()), 'Known linked unique settled refunds, including November R07, do not exceed order gross; R05 and R06 cannot be fully cap-checked') manifest = json.loads(Path('output/original-submission/sha256.json').read_text()) check(all(hashlib.sha256((Path('output/original-submission')/name).read_bytes()).hexdigest() == h for name,h in manifest.items()), 'Five archived original deliverables match the preservation SHA-256 manifest') check(all((Path('output')/name).read_bytes() == (Path('output/original-submission')/name).read_bytes() for name in manifest if name.endswith('.csv')), 'Four revised CSVs are byte-identical to the original submission; no numerical correction needed') check(all(hashlib.sha256(p.read_bytes()).hexdigest() == h for p,h in before.items()), 'Four input files unchanged across this review validation run (SHA-256)') report = '# Submission review — executed checks\n\nRun: `python output/validate.py` from workspace root. Python standard library only.\n\n' report += '\n'.join('- PASS: ' + c + '.' for c in checks) report += '\n\n## Scope and limitations\n\nThe review derives duplicate/superseded records and eligibility from raw CSVs independently of the original hard-coded exclusion lists. All input fields, output linkage fields, exception keys and summary results are checked. CSV syntax was parsed, not tested in a spreadsheet application. Human-readable explanations and recommendations were reviewed against the supplied policy and row evidence. No external verification was performed. R06 remains unknown and R05 remains unmatched; known-value cap tests cannot clear either exception. Hash checks establish unchanged files during this review, not any earlier unobserved period.\n' Path('output/review-checks.md').write_text(report, encoding='utf-8') print('\n'.join('PASS: ' + c for c in checks)) print('Wrote output/review-checks.md')