"""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')
