"""Verify date-grain results against read-only original rows and Decimal.""" import hashlib import json import sqlite3 from collections import defaultdict from datetime import datetime from decimal import Decimal from pathlib import Path LAB = Path(__file__).resolve().parents[1] EVIDENCE = LAB / 'evidence/chapter-11' HASH = 'bdf635be69850bd3be09c9a2dbeef7ddfb80036bd3ef3381383cd03b61e4a61a' ZERO = Decimal('0') TOLERANCE = Decimal('1E-12') def load(path): return json.loads(Path(path).read_text(encoding='utf-8-sig')) def near(actual, expected): assert abs(Decimal(str(actual)) - expected) < TOLERANCE, (actual, expected) def main(): manifest = load(EVIDENCE / 'query-manifest.json') cases = {c['name']: c for c in manifest['queries']} assert len(cases) == 5 model_record = load(EVIDENCE / 'model-validation.json') hashes = model_record['modelSha256'] assert len(hashes) == 6 previous = load(LAB / 'evidence/chapter-10/acceptance.json')['modelSha256'] for name, digest in hashes.items(): assert hashlib.sha256((LAB / name).read_bytes()).hexdigest() == digest assert all(hashes[name] == digest for name, digest in previous.items()) validation = load(model_record['validationResponse']) assert validation['success'] and validation['data']['validFiles'] == 6 assert validation['data']['invalidFiles'] == 0 fields = load(model_record['describeResponse'])['data']['data']['fields'] assert fields['invoiceDate$id']['type'] == 'DATETIME' assert fields['invoiceDate$yearMonth']['type'] == 'STRING' for f in ['invoiceDate$year', 'invoiceDate$month']: assert fields[f]['type'] == 'INTEGER' original = LAB.parent / '2026-10-13/open-data/chinook-v1.4.5/Chinook_Sqlite.sqlite' copy = LAB / 'data/Chinook_Sqlite.sqlite' for p in [original, copy]: assert hashlib.sha256(p.read_bytes()).hexdigest() == HASH with sqlite3.connect(original.as_uri() + '?mode=ro&immutable=1', uri=True) as db: rows = list(db.execute('SELECT InvoiceId, InvoiceDate, Total FROM Invoice')) def result(name): c = cases[name] assert load(c['validation'])['success'] r = load(c['response']) assert r['success'] assert not r['data'].get('hasNext') and not r['data'].get('pagination', {}).get('hasMore') return r['data']['items'] def grouped(start, end, by_year=False): totals = defaultdict(lambda: ZERO) counts = defaultdict(int) for _, date, amount in rows: if start <= date < end: key = int(date[:4]) if by_year else date[:7] totals[key] += Decimal(str(amount)) counts[key] += 1 return totals, counts def check(name, start, end, by_year=False): totals, counts = grouped(start, end, by_year) actual = result(name) key = 'invoiceDate$year' if by_year else 'invoiceDate$yearMonth' assert [r[key] for r in actual] == sorted(totals) for r in actual: near(r['totalAmount'], totals[r[key]]) assert r['billCount'] == counts[r[key]] return totals, counts monthly, counts = check('01-monthly-totals', '2012-01-01', '2013-01-01') assert len(monthly) == 12 and sum(counts.values()) == 83 assert sum(monthly.values(), ZERO) == Decimal('477.53') baseline7 = load(LAB / 'evidence/chapter-07/baseline.json') for r in baseline7['monthly2012']: assert monthly[r['month']] == Decimal(r['amount']) assert counts[r['month']] == r['invoiceCount'] check('02-october-november', '2012-10-01', '2012-12-01') check('03-cross-year-months', '2011-11-01', '2012-03-01') annual, _ = check('04-annual-totals', '2011-01-01', '2013-01-01', by_year=True) assert annual[2012] == sum(monthly.values(), ZERO) check('05-empty-months', '1990-01-01', '1991-01-01') assert result('05-empty-months') == [] comparison = load(EVIDENCE / 'comparison.json') prior, current = monthly['2012-10'], monthly['2012-11'] diff = current - prior ratio = diff / prior assert comparison['sourceResponse'] == cases['02-october-november']['response'] assert Decimal(comparison['priorAmount']) == prior assert Decimal(comparison['currentAmount']) == current assert Decimal(comparison['difference']) == diff == Decimal('-5.00') near(comparison['changeRatio'], ratio) assert Decimal(comparison['changePercent']) == (ratio * 100).quantize(Decimal('0.01')) # Preserve the distinction between validation success and execution failure. failures = [] for probe in ['probe-yoy', 'probe-mom', 'probe-monthly-lag']: vp = next(EVIDENCE.glob(f'*-{probe}-validate.json')) ep = next(EVIDENCE.glob(f'*-{probe}-execute.json')) assert load(vp)['success'] is True err = load(ep) assert err['success'] is False and err['error']['phase'] == 'query.execute' failures.append(dict(probe=probe, validation=str(vp), response=str(ep), error=err['error']['code'])) baseline = dict(monthly=[dict(month=k, amount=str(monthly[k]), bills=counts[k]) for k in sorted(monthly)], annual={str(k): str(v) for k, v in annual.items()}, priorAmount=str(prior), currentAmount=str(current), difference=str(diff), changeRatio=str(ratio), calculation='Read-only original Invoice rows, Python date-string grouping and Decimal.') (EVIDENCE / 'baseline.json').write_text(json.dumps(baseline, ensure_ascii=False, indent=2), encoding='utf-8') acceptance = dict(checkedAt=datetime.now().astimezone().isoformat(), success=True, runtimeUrl='http://127.0.0.1:18067', namespace='chinook', dataSource='chinook-sqlite', model='ChinookInvoiceTimeQueryModel', securityMode='none-dev-test-only', passingQueryCases=5, validModelFiles=6, modelSha256=hashes, newModelFiles=['models/model/ChinookInvoiceTimeModel.tm', 'models/query/ChinookInvoiceTimeQueryModel.qm'], checks=['Monthly amounts and counts match original rows and chapter7 baseline', 'Cross-year YYYY-MM keys remain separate and ordered', 'Annual totals match monthly sums', 'Empty grouped range is an empty list, no invented zero months', 'October-November change computed by Decimal from CLI results matches original-row baseline', 'Original data and previous four model files unchanged'], failedWindowProbes=failures, timeWindowExecutionVerified=False, windowCalculatedFieldsExecutionVerified=False, numericTolerance=str(TOLERANCE), cleanSessionPromptVerified=False, textSqlBenchmarkExecuted=False, productionPermissionsVerified=False, browserFapVerified=False, executionMode='Current Codex session authored two models and invoked public CLI; comparison arithmetic used Python Decimal.') (EVIDENCE / 'acceptance.json').write_text(json.dumps(acceptance, ensure_ascii=False, indent=2), encoding='utf-8') print(json.dumps({k:v for k,v in acceptance.items() if k not in ['modelSha256', 'failedWindowProbes']}, ensure_ascii=False, indent=2)) if __name__ == '__main__': main()