#!/usr/bin/env python3
"""Export synthetic search projections and their persisted event histories, read-only.
Usage: python3 export-evidence.py SIDE DATABASE
"""
import json,sqlite3,sys,pathlib
side,db=sys.argv[1:3]
c=sqlite3.connect('file:'+str(pathlib.Path(db).resolve())+'?mode=ro',uri=True)
records=[]
for title,run,item,node in c.execute('''select t.title,r.payload_json,i.payload_json,n.payload_json
from orchestration_v2_projection_turn_items i
join orchestration_v2_projection_threads t using(thread_id)
join orchestration_v2_projection_runs r using(run_id)
join orchestration_v2_projection_nodes n on n.node_id=i.node_id
where i.type='web_search' and t.title like 'Evidence - %'
order by r.requested_at,i.ordinal'''):
 i,n,r=map(json.loads,(item,node,run))
 history={}
 for kind,ident in [('turn-item.updated',i['id']),('node.updated',n['id'])]:
  history[kind]=[json.loads(p) for p, in c.execute('select payload_json from orchestration_events where event_type=? and json_extract(payload_json,\'$.id\')=? order by sequence',(kind,ident))]
 controls='completed control query' in i['patterns']
 records.append(dict(case=title.removeprefix('Evidence - '),runOrdinal=r['ordinal'],runStatus=r['status'],runCompletedAt=r['completedAt'],search='control' if controls else 'unfinished',item=i,node=n,history=history,**({'controlItemUnchanged':history['turn-item.updated'][-1]==i and [x['status'] for x in history['turn-item.updated']]==['running','completed'],'controlNodeUnchanged':history['node.updated'][-1]==n and [x['status'] for x in history['node.updated']]==['running','completed']} if controls else {})))
print(json.dumps(dict(side=side,records=records),indent=2))