"""Read-only append-aware integrity for a concurrent, separately sealed pilot.

Only the ten explicitly authorized new keys may append to the gallery. Baseline
raw current records and the entire old event prefix must remain byte-identical.
No append becomes a model-training record and no old revision may be changed.
"""
from __future__ import annotations

from contextlib import closing
import json
from pathlib import Path
import re
import sqlite3

from .mask_model_training_data import PROJECT, digest, jsonl, read, require, sha
from .pose_review_geometry import canonical

SUPPLEMENT = PROJECT/'outputs/pallet_mask_under50_requests_v1'
SUPPLEMENT_SEAL = '87d868e53d396acf37a161019b71144fbff906c62091e7d4b9d68458f9083e5a'


def authorized_supplement_keys():
    require(digest(SUPPLEMENT/'verification.json') == SUPPLEMENT_SEAL,
            'Concurrent supplement independent seal changed')
    seal = read(SUPPLEMENT/'verification.json')
    require(seal['state'] == 'passed' and digest(SUPPLEMENT/'manifest.json') == seal['manifest_sha256'],
            'Concurrent supplement manifest binding changed')
    for name in ('requests', 'proposals'):
        require(digest(SUPPLEMENT/(name+'.jsonl')) == seal[name+'_sha256'],
                'Concurrent supplement target allowlist changed')
    # Only identities are extracted; draft polygons and pose payloads are never
    # exported or incorporated into the frozen training dataset.
    requests = jsonl(SUPPLEMENT/'requests.jsonl')
    proposals = jsonl(SUPPLEMENT/'proposals.jsonl')
    keys = {r['target_key'] for r in requests}
    require(len(keys) == len(requests) == len(proposals) == 10
            and keys == {p['target_key'] for p in proposals}, 'Unexpected authorized append scope')
    require(sorted(r['class_id'] for r in requests) == [11, 11, 16, 16, 17, 17, 19, 19, 20, 20],
            'Authorized new-key class scope differs')
    return keys


def database_rows(path, frozen=False):
    path = Path(path).resolve()
    uri = path.as_uri()+'?mode=ro'+('&immutable=1' if frozen else '')
    with closing(sqlite3.connect(uri, uri=True)) as db:
        db.execute('PRAGMA query_only=ON')
        db.execute('BEGIN')
        schema = list(db.execute('SELECT type,name,tbl_name,sql FROM sqlite_master ORDER BY type,name'))
        tables, columns = {}, {}
        for row in schema:
            if row[0] != 'table':
                continue
            name = row[1]
            require(re.fullmatch('[A-Za-z0-9_]+', name) is not None, 'Unsafe SQLite identifier')
            cursor = db.execute('SELECT * FROM "'+name+'"')
            columns[name] = [c[0] for c in cursor.description]
            tables[name] = cursor.fetchall()
    logical = dict(schema_sha256=sha(schema), tables={
        name: sha(sorted(canonical(list(row)).decode() for row in values))
        for name, values in tables.items()})
    return dict(schema=schema, columns=columns, tables=tables, logical_fingerprint=logical)


def compare_gallery_extension(baseline, current, allowed_keys, trained_keys=()):
    """Pure raw-row comparison, independently testable with synthetic histories."""
    require(current['schema'] == baseline['schema'] and current['columns'] == baseline['columns'],
            'Gallery schema changed during model training')
    old_tables, new_tables = baseline['tables'], current['tables']
    require(old_tables.keys() == new_tables.keys(), 'Gallery tables changed')
    for name in old_tables:
        if name not in ('reviews', 'events', 'sqlite_sequence'):
            require(sorted(old_tables[name]) == sorted(new_tables[name]),
                    'Baseline gallery metadata/table changed: '+name)
    columns = baseline['columns']['reviews']
    key_index = columns.index('target_key')
    old_current = {row[key_index]: row for row in old_tables['reviews']}
    new_current = {row[key_index]: row for row in new_tables['reviews']}
    require(len(old_current) == len(old_tables['reviews']) and len(new_current) == len(new_tables['reviews']),
            'Duplicate gallery current key')
    for key, row in old_current.items():
        require(new_current.get(key) == row,
                ('TRAINED source/revision changed: ' if key in trained_keys else 'Baseline current raw payload changed: ')+key)
    new_keys = set(new_current)-set(old_current)
    require(new_keys <= set(allowed_keys), 'Unapproved new gallery key outside frozen experiment')
    event_columns = baseline['columns']['events']
    id_index, event_key_index = event_columns.index('id'), event_columns.index('target_key')
    old_events = sorted(old_tables['events'], key=lambda row: row[id_index])
    new_events = sorted(new_tables['events'], key=lambda row: row[id_index])
    require(new_events[:len(old_events)] == old_events, 'Baseline raw event prefix changed')
    delta = new_events[len(old_events):]
    old_event_keys = {row[event_key_index] for row in old_events}
    old_max = max((row[id_index] for row in old_events), default=0)
    require(all(row[id_index] > old_max and row[event_key_index] in allowed_keys
                and row[event_key_index] not in old_current and row[event_key_index] not in old_event_keys
                for row in delta), 'Event append touches old history or an unauthorized key')
    require(len({row[id_index] for row in new_events}) == len(new_events), 'Duplicate event IDs')
    latest, revisions = {}, {}
    revision_index = event_columns.index('revision')
    for event in delta:
        key, revision = event[event_key_index], event[revision_index]
        value = json.loads(event[event_columns.index('payload')])
        require(value.get('target_key') == key and type(value.get('revision')) is int
                and value['revision'] == revision, 'New raw event relational/payload identity differs')
        require(key not in revisions or revision > revisions[key], 'Nonmonotonic new-key event revisions')
        revisions[key], latest[key] = revision, event
    require(set(latest) == new_keys, 'New current/event key coverage differs')
    for key in new_keys:
        row, event = new_current[key], latest[key]
        for field in ('revision', 'payload'):
            require(row[columns.index(field)] == event[event_columns.index(field)],
                    'New current is not exact latest raw event: '+key)
    if 'sqlite_sequence' in old_tables:
        old_sequence, new_sequence = dict(old_tables['sqlite_sequence']), dict(new_tables['sqlite_sequence'])
        require(old_sequence.keys() == new_sequence.keys(), 'Unexpected SQLite sequence table change')
        for name, value in old_sequence.items():
            expected = max(row[id_index] for row in new_events) if name == 'events' else value
            require(new_sequence[name] == expected, 'SQLite sequence does not match append-only events')
    return dict(state='baseline_preserved_authorized_new_key_appends_only',
        baseline_current_records=len(old_current), baseline_raw_event_prefix=len(old_events),
        appended_events=len(delta), appended_distinct_keys=len(new_keys),
        appended_keys=sorted(new_keys), allowed_key_count=len(allowed_keys),
        baseline_current_payloads_raw_byte_identical=True, baseline_event_prefix_raw_byte_identical=True,
        schema_and_metadata_identical=True, frozen_training_dataset_appended=False,
        baseline_events_sha256=sha(old_events), observed_events_sha256=sha(new_events),
        external_supplement_seal_sha256=SUPPLEMENT_SEAL,
        new_human_saves_not_used_as_training_labels=True)


def verify_gallery_extension(snapshot, live, trained_keys=()):
    baseline, current = database_rows(snapshot, True), database_rows(live)
    result = compare_gallery_extension(baseline, current, authorized_supplement_keys(), trained_keys)
    result['observed_logical_fingerprint'] = current['logical_fingerprint']
    return result
