import json, sqlite3, sys
from pathlib import Path
connection = sqlite3.connect(Path(sys.argv[1]).resolve().as_uri() + '?mode=ro', uri=True)
connection.row_factory = sqlite3.Row
sessions = {r['provider_thread_id']: r['first_run_ordinal'] for r in connection.execute('select provider_thread_id, first_run_ordinal from orchestration_v2_projection_provider_threads')}
result = []
for row in connection.execute('select ordinal,status,provider_thread_id,payload_json from orchestration_v2_projection_runs order by ordinal'):
    run = json.loads(row['payload_json'])
    messages = [dict(m) for m in connection.execute("select role,json_extract(payload_json,'$.text') as text from orchestration_v2_projection_messages where run_id=? order by created_at", (run['id'],))]
    result.append({'run': row['ordinal'], 'status': row['status'], 'sessionStartedAtRun': sessions.get(row['provider_thread_id']), 'freshContext': run.get('freshContext', False), 'messages': messages})
print(json.dumps(result, indent=2))
