"""Prepare the September 2026 corpus extension from recorded agent judgments.

Read-only database access. Writes collection registers and a review pack; applying
that pack is an explicit separate PatchScholar operation. Never rewrites the
frozen ten-year register. Canonical IDs are specific to this collection database.
"""
from __future__ import annotations
import csv
import hashlib
import json
import sqlite3
from collections import Counter, defaultdict
from pathlib import Path
import yaml

ROOT = Path(__file__).resolve().parents[1]
DATA = ROOT / 'data/batches/2026-09-11-backfill'
OLD = ROOT / 'reviews/2026-09-09-decade.csv'
EXPECTED_SHA = '9719c1184acacd287e3e86488194c239dc0f9180bfbb355f9a956ddd55d5b5ea'
START, END = '2000-01-01', '2016-09-08'


def read_csv(path):
    with path.open() as stream:
        return list(csv.DictReader(stream))


def write_csv(path, rows, fields=None):
    with path.open('w') as stream:
        writer = csv.DictWriter(stream, fieldnames=fields or list(rows[0]), lineterminator='\n')
        writer.writeheader()
        writer.writerows(rows)


def build():
    assert hashlib.sha256(OLD.read_bytes()).hexdigest() == EXPECTED_SHA
    old = read_csv(OLD)
    rows = {int(r['canonical_work_id']): dict(r) for r in old}
    baseline_ids = set(rows)
    reviews = {}
    # First-level files have disjoint ownership and exactly match frozen inputs.
    inputs = sorted(DATA.glob('screen-*.json'))
    for path in inputs:
        key = path.stem.removeprefix('screen-')
        target = ROOT / 'reviews/backfill-2000' / (key + '.csv')
        decisions = read_csv(target)
        expected = [r['id'] for r in json.loads(path.read_text())]
        actual = [int(r['canonical_work_id']) for r in decisions]
        assert actual == expected, (key, 'input/output IDs differ')
        for r in decisions:
            work_id = int(r['canonical_work_id'])
            assert work_id not in reviews, ('overlapping screening inputs', work_id)
            reviews[work_id] = {**r, 'review_register': str(target.relative_to(ROOT))}
    # Follow-up evidence overrides a recorded first-level assessment; originals remain.
    for path in sorted((ROOT / 'reviews/backfill-2000').glob('*audit*.csv')):
        for r in read_csv(path):
            work_id = int(r['canonical_work_id'])
            assert work_id in reviews, ('override without first assessment', work_id)
            reviews[work_id] = {**r, 'review_register': str(path.relative_to(ROOT))}
    for r in reviews.values():
        assert r['decision'] in {'include', 'exclude', 'defer'}
        assert all(r.get(k) for k in ['labels', 'reason', 'evidence'])
        assert r['assessment'] == 'agent' and r['human_confirmed'] == 'false'
    connection = sqlite3.connect(f'file:{ROOT / "state/patchscholar.db"}?mode=ro', uri=True)
    connection.row_factory = sqlite3.Row
    metadata = {r['id']: dict(r) for r in connection.execute('SELECT * FROM canonical_work')}
    new_ids = set(metadata) - baseline_ids
    assert new_ids == set(reviews) - baseline_ids, 'Missing or unexpected new judgments'
    sources = defaultdict(list)
    for r in connection.execute('SELECT * FROM work ORDER BY id'):
        sources[r['canonical_work_id']].append(dict(r))
    fields = list(old[0]) + ['backfill_eligible', 'backfill_date', 'expanded_eligible', 'discovery_batch']
    for work_id in sorted(metadata):
        m, s = metadata[work_id], sources[work_id]
        dates = sorted({w['publication_date'] for w in s if w['publication_date']})
        backdates = [d for d in dates if START <= d <= END]
        expanded_dates = [d for d in dates if START <= d <= '2026-09-09']
        if work_id not in rows:
            rows[work_id] = {
                'canonical_work_id': str(work_id), 'title': m['title'],
                'arxiv_id': m['arxiv_id'] or '', 'doi': m['doi'] or '',
                'publication_type': m['type'] or m['kind'] or '',
                'source_types': '; '.join(dict.fromkeys(w['source']+':'+(w['type'] or w['kind'] or '') for w in s)),
                'source_identifiers': '; '.join(w['source_identifier'] for w in s),
                'window_status': 'backfill' if backdates else 'outside-expanded-window',
                'window_date': min(backdates or dates) if dates else '',
                'source_dates': '; '.join(w['source']+':'+(w['publication_date'] or '') for w in s),
            }
        row = rows[work_id]
        if work_id in reviews:
            row.update(reviews[work_id])
            row['canonical_work_id'] = str(work_id)
            if backdates:
                row['labels'] = ';'.join(sorted(set(row['labels'].split(';')) | {'backfill-2000'}))
        row.update(backfill_eligible=str(bool(backdates)).lower(),
                   backfill_date=min(backdates) if backdates else '',
                   expanded_eligible=str(bool(expanded_dates)).lower(),
                   discovery_batch='backfill-2000' if work_id in new_ids else 'original-snapshot')
    ordered = [rows[k] for k in sorted(rows)]
    write_csv(ROOT / 'reviews/2026-09-11-expanded.csv', ordered, fields)
    backfill = [r for r in ordered if r['backfill_eligible'] == 'true']
    write_csv(ROOT / 'reviews/2026-09-11-backfill.csv', backfill, fields)
    accepted = [r for r in ordered if r['decision'] == 'include']
    grouped = defaultdict(list)
    for r in accepted:
        key = ''.join(ch for ch in r['title'].casefold() if ch.isalnum())
        if key:
            grouped[key].append(r)
    shared = [{'canonical_ids': ';'.join(r['canonical_work_id'] for r in group),
               'title': group[0]['title'], 'status': 'unresolved-title-similarity-not-identity'}
              for group in grouped.values() if len(group) > 1]
    write_csv(ROOT / 'reviews/backfill-2000/shared-title-follow-up.csv', shared)
    pending = [r for r in ordered if r['decision'] == 'defer']
    write_csv(ROOT / 'reviews/backfill-2000/follow-up.csv', pending, fields)
    actions = []
    for work_id in sorted(reviews):
        r = rows[work_id]
        actions.append({'canonical_work_id': work_id, 'decision': r['decision'],
                        'labels': r['labels'].split(';'), 'reason': r['reason'],
                        'note': f"Agent assessment; human_confirmed=false. {r['evidence']} Register: {r['review_register']}. Backfill eligible: {r['backfill_eligible']}"})
    pack = {'version': 1, 'name': 'Andreev 2000 backfill review 2026-09-11',
            'run': {'root': str(ROOT)}, 'steps': [
                {'id': 'candidates', 'action': 'query', 'args': {'subject': 'canonical-works', 'query': {'limit': 20000}}},
                {'id': 'review', 'action': 'review_works', 'args': {'corpus': 'andreev-literature', 'from_step': 'candidates', 'reviews': actions}}]}
    (DATA / 'final-review.yaml').write_text(yaml.safe_dump(pack, sort_keys=False, allow_unicode=True))
    report = {'total': len(rows), 'new_canonical_records': len(new_ids),
              'new_decisions': dict(Counter(rows[k]['decision'] for k in new_ids)),
              'all_decisions': dict(Counter(r['decision'] for r in ordered)),
              'backfill_decisions': dict(Counter(r['decision'] for r in backfill)),
              'accepted_expanded': sum(r['expanded_eligible'] == 'true' for r in accepted),
              'accepted_outside_expanded': sum(r['expanded_eligible'] != 'true' for r in accepted),
              'accepted_backfill_years': dict(sorted(Counter(r['backfill_date'][:4] for r in backfill if r['decision']=='include').items())),
              'old_reconsidered': len(set(reviews) & baseline_ids),
              'old_decisions_changed': sum(rows[k]['decision'] != next(r['decision'] for r in old if int(r['canonical_work_id'])==k) for k in set(reviews) & baseline_ids),
              'shared_title_groups': len(shared), 'shared_title_records': sum(len(r['canonical_ids'].split(';')) for r in shared),
              'baseline_register_sha256': EXPECTED_SHA}
    (ROOT / 'reviews/backfill-2000/validation.json').write_text(json.dumps(report, indent=2)+'\n')
    print(json.dumps(report, indent=2))

if __name__ == '__main__':
    build()