#!/usr/bin/env python3
"""Show why row counts and integrity_check are incomplete restore checks.

Only synthetic, closed temporary databases are changed. No live DB is opened.
"""
import argparse
import hashlib
import json
import platform
import shutil
import sqlite3
import tempfile
from datetime import datetime, timezone
from pathlib import Path


def inspect(path):
    connection = sqlite3.connect(path.as_uri() + '?mode=ro', uri=True)
    try:
        rows = connection.execute('SELECT id,title,user_id FROM notes ORDER BY id').fetchall()
        return {
            'integrity_check': connection.execute('PRAGMA integrity_check').fetchall(),
            'foreign_key_check': connection.execute('PRAGMA foreign_key_check').fetchall(),
            'count_and_max_id': connection.execute('SELECT count(*),max(id) FROM notes').fetchone(),
            'content_sha256': hashlib.sha256(json.dumps(rows, ensure_ascii=False).encode()).hexdigest(),
            'first_note': rows[0],
        }
    finally:
        connection.close()


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--output', required=True)
    output = Path(parser.parse_args().output)
    if output.exists():
        raise SystemExit('Existing evidence will not be overwritten.')
    with tempfile.TemporaryDirectory(prefix='punchblog-restore-checks-') as directory:
        root = Path(directory)
        original = root / 'reference.db'
        connection = sqlite3.connect(original)
        try:
            connection.execute('PRAGMA foreign_keys=ON')
            connection.executescript('''
                CREATE TABLE users(id INTEGER PRIMARY KEY);
                CREATE TABLE notes(id INTEGER PRIMARY KEY,title TEXT NOT NULL,
                                   user_id INTEGER NOT NULL REFERENCES users(id));
                INSERT INTO users VALUES(1);
            ''')
            connection.executemany('INSERT INTO notes VALUES(?,?,1)',
                                   [(n, f'synthetic-note-{n}') for n in range(1, 17)])
            connection.commit()
            backup = root / 'backup.db'
            destination = sqlite3.connect(backup)
            try:
                connection.backup(destination)
            finally:
                destination.close()
        finally:
            connection.close()
        observations = {'reference': inspect(original)}
        for name in ('restored', 'changed_title', 'orphan_reference'):
            candidate = root / (name + '.db')
            shutil.copy2(backup, candidate)  # Both backup connections are closed.
            if name != 'restored':
                con = sqlite3.connect(candidate)
                try:
                    # A deliberately bad historical state, not a repair procedure.
                    con.execute('PRAGMA foreign_keys=OFF')
                    if name == 'changed_title':
                        con.execute("UPDATE notes SET title='changed-title' WHERE id=1")
                    else:
                        con.execute('UPDATE notes SET user_id=999 WHERE id=1')
                    con.commit()
                finally:
                    con.close()
            observations[name] = inspect(candidate)
        expected = observations['reference']
        assert observations['restored'] == expected
        assert all(item['integrity_check'] == [('ok',)] for item in observations.values())
        assert all(item['count_and_max_id'] == (16, 16) for item in observations.values())
        assert observations['changed_title']['content_sha256'] != expected['content_sha256']
        assert observations['changed_title']['foreign_key_check'] == []
        assert observations['orphan_reference']['foreign_key_check'] == [('notes', 1, 'users', 0)]
    rows = []
    for key, label in [('restored', '정상 복구본'),
                       ('changed_title', '제목만 바꾼 복구본'),
                       ('orphan_reference', '없는 사용자 999를 참조')]:
        item = observations[key]
        rows.append([label, '16 / 16', 'ok', len(item['foreign_key_check']),
                     '일치' if item['content_sha256'] == expected['content_sha256'] else '불일치'])
    record = {
        'observed_at': datetime.now(timezone.utc).isoformat(),
        'environment': {'os': platform.system(), 'python': platform.python_version(), 'sqlite': sqlite3.sqlite_version},
        'method': '합성 사용자 1명·메모 16개를 backup API로 보관. 연결 종료 후 별도 복구본 3개 중 하나의 제목, 다른 하나의 사용자 참조만 변경. 구조·행 수·관계·전체 메모 내용을 대조.',
        'limits': '변경은 백업 API 실패나 디스크 손상을 재현하지 않는다. 검사의 사각지대를 보이기 위한 의도된 데이터 수정이다. 동시 쓰기·대용량·외부 첨부파일·복구 시간은 미검증.',
        'observations': observations,
        'tables': {'checks': {'headers': ['대상', '행 수 / 마지막 ID', 'integrity_check', '외래 키 위반 수', '기준 내용 대조'], 'rows': rows}},
    }
    output.parent.mkdir(parents=True, exist_ok=True)
    with output.open('x') as stream:
        json.dump(record, stream, ensure_ascii=False, indent=2)
        stream.write('\n')
    print(json.dumps({'output': str(output), 'tables': record['tables']}, ensure_ascii=False))


if __name__ == '__main__':
    main()