#!/bin/sh
# Evidence extraction for PR #22761: read-only queries of a stopped server's isolated statev2.sqlite.
# usage: extract.sh <statev2.sqlite> <transcript.json>
set -eu
db="$1"
transcript="$2"
node -e 'const t=require(require("path").resolve(process.argv[1]));for(const [k,r] of Object.entries(t.rows))console.log(k+" "+r.threadId)' "$transcript" |
while read -r row thread; do
  echo "=== $row thread $thread"
  echo "--- orchestration_events (provider-turn.updated; orchestration_v2_events is empty on this schema)"
  sqlite3 -readonly "$db" "select sequence, json_extract(payload_json,'\$.status'), coalesce(json_extract(payload_json,'\$.tokenUsage'),'<absent>'), coalesce(json_extract(payload_json,'\$.turnTokenUsage'),'<absent>') from orchestration_events where event_type='provider-turn.updated' and aggregate_kind='thread' and stream_id='$thread' order by sequence"
  echo "--- orchestration_v2_projection_provider_turns"
  sqlite3 -readonly "$db" "select ordinal, status, coalesce(json_extract(payload_json,'\$.tokenUsage'),'<absent>'), coalesce(json_extract(payload_json,'\$.turnTokenUsage'),'<absent>') from orchestration_v2_projection_provider_turns where thread_id='$thread' order by ordinal"
done