#!/usr/bin/env python3
from __future__ import annotations

import re
import sqlite3
import tempfile
from pathlib import Path

ROOT = Path(__file__).resolve().parents[2]
APP = ROOT / 'app'
MIGRATIONS = sorted((APP / 'server/migrations').glob('*.sql'))
SOURCE = (APP / 'server/lib/DataManagement.php').read_text(encoding='utf-8')

match = re.search(r'private const CONTENT_TABLES = \[(.*?)\];', SOURCE, re.S)
assert match, 'DataManagement CONTENT_TABLES was not found.'
content_tables = re.findall(r"'([^']+)'", match.group(1))
assert len(content_tables) == len(set(content_tables)), 'CONTENT_TABLES contains duplicates.'

required_export_datasets = {
    'conversations', 'messages', 'message_attachments', 'projects', 'project_files',
    'automation_definitions', 'automation_templates', 'workflows', 'workflow_stages',
    'workflow_snapshots', 'workflow_workers', 'workflow_events', 'workflow_checkpoints',
    'workflow_interventions', 'tool_calls', 'tool_approvals', 'generations',
    'cost_events', 'cost_reservations', 'prompts', 'offline_drafts', 'sync_conflicts',
    'sync_tombstones', 'sync_mutations',
}
export_datasets = set(re.findall(r"^\s*'([^']+)'\s*=>\s*\$db->all", SOURCE, re.M))
assert required_export_datasets <= export_datasets, sorted(required_export_datasets - export_datasets)
assert required_export_datasets <= set(content_tables), sorted(required_export_datasets - set(content_tables))

with tempfile.TemporaryDirectory(prefix='glmchat-privacy-reset-') as temporary:
    db = sqlite3.connect(Path(temporary) / 'privacy.db')
    db.execute('PRAGMA foreign_keys=ON')
    for migration in MIGRATIONS:
        db.executescript(migration.read_text(encoding='utf-8'))

    p = '00000000-0000-4000-8000-000000000001'
    c = '00000000-0000-4000-8000-000000000002'
    m = '00000000-0000-4000-8000-000000000003'
    w = '00000000-0000-4000-8000-000000000004'
    d = '00000000-0000-4000-8000-000000000005'
    db.execute("INSERT INTO users(id,password_hash) VALUES(1,'retained')")
    db.execute("INSERT INTO projects(id,name,root_path,source_type) VALUES(?,?,?,?)", (p, 'Project', f'{temporary}/{p}', 'pasted'))
    db.execute("INSERT INTO project_files(id,project_id,relative_path,size_bytes,sha256) VALUES(?,?,?,?,?)", ('f1', p, 'src/app.js', 1, 'a' * 64))
    db.execute("INSERT INTO conversations(id,title,project_id) VALUES(?,?,?)", (c, 'Conversation', p))
    db.execute("INSERT INTO messages(id,conversation_id,role,content,sequence_no) VALUES(?,?,?,?,?)", (m, c, 'user', 'hello', 1))
    db.execute("INSERT INTO message_attachments(id,message_id,conversation_id,project_id,filename,media_type,size_bytes,sha256) VALUES(?,?,?,?,?,?,?,?)", ('a1', m, c, p, 'image.png', 'image/png', 1, 'b' * 64))
    db.execute("INSERT INTO automation_definitions(id,kind,name,definition_json) VALUES(?,?,?,?)", (d, 'agent', 'Agent', '{}'))
    db.execute("INSERT INTO automation_templates(id,kind,name,definition_json) VALUES(?,?,?,?)", ('t1', 'agent', 'Template', '{}'))
    db.execute("INSERT INTO workflows(id,conversation_id,project_id,status,definition_id) VALUES(?,?,?,?,?)", (w, c, p, 'pending', d))
    db.execute("INSERT INTO workflow_stages(id,workflow_id,stage_key,ordinal,status) VALUES(?,?,?,?,?)", ('s1', w, 'stage', 1, 'pending'))
    db.execute("INSERT INTO workflow_snapshots(id,workflow_id,definition_id,schema_version,snapshot_json,snapshot_sha256) VALUES(?,?,?,?,?,?)", ('sn1', w, d, 1, '{}', 'c' * 64))
    db.execute("INSERT INTO workflow_workers(id,workflow_id,agent_key,name,role) VALUES(?,?,?,?,?)", ('ww1', w, 'agent', 'Worker', 'worker'))
    db.execute("INSERT INTO workflow_events(workflow_id,worker_id,stage_key,event_type) VALUES(?,?,?,?)", (w, 'ww1', 'stage', 'started'))
    db.execute("INSERT INTO workflow_checkpoints(id,workflow_id,stage_key,status) VALUES(?,?,?,?)", ('cp1', w, 'stage', 'saved'))
    db.execute("INSERT INTO tool_calls(id,workflow_id,worker_id,stage_key,tool_name,risk_level,status,arguments_json,arguments_sha256) VALUES(?,?,?,?,?,?,?,?,?)", ('tc1', w, 'ww1', 'stage', 'project_read_file', 'read', 'pending', '{}', 'd' * 64))
    db.execute("INSERT INTO tool_approvals(id,workflow_id,tool_call_id,risk_level,scope_json,scope_sha256,expires_at) VALUES(?,?,?,?,?,?,?)", ('ta1', w, 'tc1', 'read', '{}', 'e' * 64, '2099-01-01T00:00:00Z'))
    db.execute("INSERT INTO workflow_interventions(id,workflow_id,action) VALUES(?,?,?)", ('wi1', w, 'pause'))
    db.execute("INSERT INTO generations(id,workflow_id,status) VALUES(?,?,?)", ('g1', w, 'pending'))
    db.execute("INSERT INTO cost_reservations(request_id,workflow_id,reserved_cost) VALUES(?,?,?)", ('r1', w, 1.0))
    db.execute("INSERT INTO cost_events(request_id,workflow_id,model) VALUES(?,?,?)", ('ce1', w, 'model'))
    db.execute("INSERT INTO offline_drafts(id,conversation_id,content) VALUES(?,?,?)", ('od1', c, 'draft'))
    db.execute("INSERT INTO sync_mutations(mutation_id,request_hash,entity_type,entity_id,result_json) VALUES(?,?,?,?,?)", ('sm1', 'f' * 64, 'conversation', c, '{}'))
    db.execute("INSERT INTO sync_conflicts(id,mutation_id,entity_type,entity_id,base_revision,server_revision,local_json,server_json) VALUES(?,?,?,?,?,?,?,?)", ('sc1', 'sm2', 'conversation', c, 1, 2, '{}', '{}'))
    db.execute("INSERT INTO sync_tombstones(entity_type,entity_id,revision) VALUES(?,?,?)", ('conversation', c, 2))
    db.execute("INSERT INTO voice_cache(model,voice_id,voice_name,payload_json,refreshed_at) VALUES(?,?,?,?,?)", ('tts', 'voice', 'Voice', '{}', 1))
    db.commit()

    db.execute('BEGIN IMMEDIATE')
    for table in content_tables:
        db.execute(f'DELETE FROM {table}')
    db.commit()

    for table in content_tables:
        assert db.execute(f'SELECT COUNT(*) FROM {table}').fetchone()[0] == 0, table
    assert db.execute('SELECT COUNT(*) FROM users').fetchone()[0] == 1
    assert db.execute("SELECT COUNT(*) FROM settings WHERE key='app_version'").fetchone()[0] == 1
    assert db.execute('PRAGMA foreign_key_check').fetchall() == []
    assert db.execute('PRAGMA integrity_check').fetchone()[0] == 'ok'
    db.close()

print(f'Privacy/reset contracts passed: {len(required_export_datasets)} export datasets and {len(content_tables)} content tables.')