#!/usr/bin/env python3
"""Read only synthetic runs: extract-sessions.py BASE_DB HEAD_DB NATIVE_DB OUTPUT."""
import json
import sqlite3
import sys
from pathlib import Path

def readonly(path):
    return sqlite3.connect('file:' + str(Path(path).resolve()) + '?mode=ro', uri=True)

native = readonly(sys.argv[3])
result = {}
for side, path in zip(('base', 'head'), sys.argv[1:3]):
    db = readonly(path)
    rows = db.execute('select payload_json from orchestration_v2_projection_provider_threads '
                      'order by updated_at').fetchall()
    # These inputs must be the isolated evidence databases, never an installed T3 database.
    selected = [rows[0], rows[-1]]
    cases = []
    for label, (raw,) in zip(('selected researcher', 'unset agent'), selected):
        payload = json.loads(raw)
        session = payload['nativeThreadRef']['nativeId']
        messages = []
        for kind, seq, raw_message in native.execute(
                'select type,seq,data from session_message where session_id=? order by seq', (session,)):
            message = json.loads(raw_message)
            if kind in ('user', 'assistant', 'agent-switched'):
                messages.append({'type': kind, 'seq': seq, **{
                    key: message[key] for key in ('text', 'agent', 'model', 'content') if key in message
                }})
        cases.append({'case': label, 'messages': messages})
    result[side] = cases
Path(sys.argv[4]).write_text(json.dumps(result, indent=2))
