#!/usr/bin/env python3
"""Offline verification of fresh Material metadata against sealed source evidence."""
import hashlib
import json
from pathlib import Path

root = Path(__file__).parent
sealed = root.parents[2] / 'work_store/apifox/2026-09-30-dm8-interface-acceptance/parallel-store-category-crud-20261011'
def read(path):
    return json.loads(path.read_text())
def sha(path):
    return hashlib.sha256(path.read_bytes()).hexdigest()
def rows_sha(rows):
    return hashlib.sha256(json.dumps(rows, ensure_ascii=False, separators=(',', ':')).encode()).hexdigest()

summary = {'passed': True, 'DML': 0, 'DDL': 0, 'HTTP': 0, 'lanes': {}, 'sealed': {}}
for directory, expected in [('private-evidence', 'ec4620a8d38a53c912a7a7cbaa8abbfdd5ca004bccd7719d7b554e0896409a9e'), ('scratch-private-evidence', 'a92ffb05a4d0d6890a5f8ed09431e0f6b63779cc6cfe97420d99c24dae5940d3')]:
    base = sealed / directory
    manifest = base / 'manifest.json'
    assert sha(manifest) == expected
    entries = read(manifest)['files']
    for item in entries:
        file = base / item['path']
        assert file.stat().st_size == item['bytes'] and sha(file) == item['sha256']
    summary['sealed'][directory] = {'files': len(entries), 'manifest_sha256': expected, 'unchanged': True}

for lane, filename, natural_count in [('B', 'B-fresh-v2.json', 31), ('C', 'C-fresh-v3.json', 0)]:
    file = root / 'private-evidence' / filename
    fresh = read(file)
    old = read(sealed / 'private-evidence' / lane / 'final.json')['source']['material_category']
    assert fresh['rows'] == old
    # C-fresh-v3 used DDL as metadata key; it contains a SELECT-only GET_DDL
    # result, not an executed-DDL counter. Keep its original bytes unchanged.
    assert fresh['DML'] == 0 and fresh['triggers'] == []
    assert fresh['DDL'] == 0 if lane == 'B' else isinstance(fresh['DDL'], list)
    assert all(entry['sql'].startswith('SELECT ') for entry in fresh['journal'])
    groups = {'read9': [], 'crud': [], 'natural': []}
    for row in fresh['rows']:
        group = 'read9' if row['name'].startswith('CDXC9733C88C1-') else 'crud' if row['name'].startswith('CDXCCFF927311-') else 'natural'
        groups[group].append(row)
    assert [len(groups[k]) for k in ('read9', 'crud', 'natural')] == [2, 3, natural_count]
    assert all(row['is_delete'] in (1, '1') for row in groups['crud'])
    assert len(fresh['columns']) == 12
    assert all(column['is_nullable'] == 'NO' if lane == 'B' else column['nullable'] == 'N' for column in fresh['columns'])
    column = next(c for c in fresh['columns'] if c['column_name'].lower() == 'update_time')
    if lane == 'B':
        assert column['datetime_precision'] in (0, '0') and column['extra'].lower() == 'on update current_timestamp'
    else:
        assert column['data_scale'] == 6 and column['data_default'] == 'CURRENT_TIMESTAMP'
    for fraction, expected in [('123456', '01'), ('499999', '01'), ('500000', '02'), ('999999', '02')]:
        assert fresh['fraction_' + fraction][0]['t'] == '2001-01-01 01:01:' + expected
    summary['lanes'][lane] = {'file_sha256': sha(file), 'rows_sha256': rows_sha(fresh['rows']), 'rows': len(fresh['rows']), 'read9': 2, 'crud_softdeleted': 3, 'natural': natural_count, 'source_matches_sealed_final': True, 'successful_SELECT': len(fresh['journal']), 'fsp': 0 if lane == 'B' else 6, 'triggers': 0}
summary['candidate_C_old_update_time_mapping_hypothesis'] = {'2': '2001-02-03 04:05:06', '3': '2001-02-03 04:05:07', '4': '2026-10-11 02:21:27', '5': '2026-10-11 02:21:28', '6': '2026-10-11 02:21:28'}
print(json.dumps(summary, ensure_ascii=False, indent=2))