#!/usr/bin/env python3
"""Evidence extraction only: SQLite mode=ro/query_only; no runtime commands.
All output is scoped to the supplied fresh parent and descendants. Reasoning,
attachments, environment, provider credentials and unrelated threads excluded.
"""
import sqlite3,json,pathlib,subprocess,datetime,re
ROOT='/home/ubuntu/wt/issue-24297'; OUT=pathlib.Path('/tmp/issue-24297')
PARENT='4b04e5b5-fce6-486c-9fc7-64770820c0b2'; START='2026-10-24T10:30:33.890Z'
c=sqlite3.connect('file:'+ROOT+'/.t3/userdata/statev2.sqlite?mode=ro',uri=True)
c.execute('PRAGMA query_only=ON'); c.execute('BEGIN')
def rows(t): return [json.loads(r[0]) for r in c.execute('select payload_json from orchestration_v2_projection_'+t)]
subs=rows('subagents'); threads={PARENT}
for _ in range(4): threads|={p['childThreadId'] for p in subs if p.get('threadId') in threads and p.get('childThreadId')}
keys=set('id threadId appThreadId runId ordinal role text prompt result status startedAt completedAt createdAt updatedAt requestedAt createdBy creationSource senderThreadId delegatedCompletion completionDelivery completionWake childThreadId parentNodeId origin model modelSelection providerInstanceId pendingBackgroundTasks restartCancelledBackgroundWork workStartedAt type toolName input output source outcome summary userMessageId notification restartContinuationOfRunId driver'.split())
def clean(p):
 d={k:v for k,v in p.items() if k in keys}
 s=json.dumps(d)
 s=re.sub(r'/tmp/claude-[^\s"\\]+','[provider-task-output-path]',s)
 return json.loads(s)
def scope(p): return p.get('threadId',p.get('appThreadId')) in threads
messages=sorted([clean(p) for p in rows('messages') if scope(p)],key=lambda p:p['createdAt'])
tasks=[clean(p) for p in subs if scope(p)]; task=next(p for p in tasks if p['threadId']==PARENT); child=task['childThreadId']
tools=[clean(p) for p in rows('turn_items') if scope(p) and p.get('type') in {'command_execution','dynamic_tool','notification'}]
events=[]
for seq,typ,at,cmd,cause,raw in c.execute('select sequence,event_type,occurred_at,command_id,causation_event_id,payload_json from orchestration_events where occurred_at>=? order by sequence',(START,)):
 p=json.loads(raw)
 if not scope(p) or typ not in {'run.updated','subagent.updated','message.updated','message.created','provider-thread.updated','turn-item.updated'}: continue
 if typ=='turn-item.updated' and p.get('type') not in {'command_execution','dynamic_tool','notification'}:continue
 events.append(dict(sequence=seq,eventType=typ,at=at,commandId=cmd,causationEventId=cause,payload=clean(p)))
deliveries=[p for p in messages if p['threadId']==PARENT and p.get('creationSource')=='server' and task['id'] in p.get('text','')]
finals=[p for p in messages if p['threadId']==child and p.get('role')=='assistant' and p.get('text')=='FINAL_FINALCHECK_24297']
wakes=[p for p in tools if p['threadId']==child and p.get('source',{}).get('kind')=='background_command']
term=[e for e in events if e['eventType']=='subagent.updated' and e['payload'].get('id')==task['id'] and e['payload'].get('status')=='completed']
parent_tools=[p for p in tools if p['threadId']==PARENT and p['type'] in {'command_execution','dynamic_tool'}]
forbidden=[p for p in tools if any(x in str(p.get('toolName','')) for x in ['task_status','thread_send','task_wait'])]
checks={'exactlyOneFinalMessage':len(finals)==1,'exactlyOneAutomaticParentDelivery':len(deliveries)==1,'deliveryHasFinal':len(deliveries)==1 and deliveries[0]['text'].endswith('FINAL_FINALCHECK_24297'),'taskResultIsFinal':task.get('result')=='FINAL_FINALCHECK_24297','completionAfterBackgroundWake':bool(wakes) and task['completedAt']>wakes[0]['startedAt'],'completionAfterFinalMessage':bool(finals) and task['completedAt']>finals[0]['createdAt'],'noPrematureTerminalEvent':bool(term) and all(e['at']>finals[0]['createdAt'] for e in term),'noPollingOrManualSends':not forbidden}
report={'capturedAt':datetime.datetime.now(datetime.timezone.utc).isoformat(),'head':subprocess.check_output(['git','-C',ROOT,'rev-parse','HEAD'],text=True).strip(),'parentThread':PARENT,'matrixRows':'0/0 (matrix [] supplied; no matrix coverage claimed)','verdict':'PASS' if all(checks.values()) else 'FAIL','checks':checks,'counts':{'automaticParentDeliveries':len(deliveries),'exactFinalMessages':len(finals)},'tasks':tasks,'messages':messages,'runs':[clean(p) for p in rows('runs') if scope(p)],'tools':tools,'events':events}
(OUT/'final-runtime.txt').write_text(json.dumps(report,indent=2)+'\n')
print(json.dumps({k:report[k] for k in ['capturedAt','head','verdict','checks','counts']},indent=2))
print('Parent tools:',[(p.get('toolName'),p.get('input')) for p in parent_tools])
print('Timeline:')
for p in messages: print(p['createdAt'], 'PARENT' if p['threadId']==PARENT else ('CHILD' if p['threadId']==child else 'REVIEWER'),p.get('text'))
print('Task terminal events:',[(e['sequence'],e['at'],e['payload'].get('result')) for e in term])