import sqlite3,json,glob,os
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect(DB); c.row_factory=sqlite3.Row
ids=[r[0] for r in c.execute('select distinct video_id from chapters where video_id not in (select id from videos) union select distinct video_id from video_links where video_id not in (select id from videos) order by video_id')]
rows=[]
for i in ids:
 ch=c.execute('select count(*) from chapters where video_id=?',(i,)).fetchone()[0]; lk=c.execute('select count(*) from video_links where video_id=?',(i,)).fetchone()[0]
 evidence={}
 for name,sql,args in [
  ('registry',"select count(*) from source_processing_registry where source_id=? or source_external_id=?",(str(i),str(i))),
  ('raw',"select count(*) from transcripts_raw where video_ref=?",(str(i),)),
  ('jobs',"select count(*) from e2e_jobs where video_id=?",(i,)),
  ('queue',"select count(*) from graphiti_import_queue where video_id=?",(str(i),)),
  ('segments',"select count(*) from transcript_segments where video_id=?",(i,))]: evidence[name]=c.execute(sql,args).fetchone()[0]
 rows.append({'missing_video_parent':i,'chapters':ch,'video_links':lk,'current_auxiliary_evidence':evidence,'youtube_id_reconstructed':False,'parent_safely_restorable':False})
out={'generated_at_utc':__import__('datetime').datetime.now(__import__('datetime').timezone.utc).isoformat(),'missing_parent_matrix':rows,'totals':{'parents':len(rows),'chapters':sum(x['chapters'] for x in rows),'video_links':sum(x['video_links'] for x in rows)},'queue_counts':[dict(x) for x in c.execute('select graphiti_status,count(*) n from graphiti_import_queue group by graphiti_status')],'open_queue':[dict(x) for x in c.execute("select id,source_type,video_id,obsidian_path,graphiti_status,next_attempt_at,lock_owner,lease_expires_at,attempt_count,graphiti_attempts,last_error_class,last_http_status,graphiti_episode_id,updated_at,completed_at,remote_outcome from graphiti_import_queue where graphiti_status!='done' order by id")],'fk_count':len(c.execute('pragma foreign_key_check').fetchall()),'integrity':c.execute('pragma integrity_check').fetchone()[0],'foreign_keys':c.execute('pragma foreign_keys').fetchone()[0]}
c.close(); Path('/tmp/phase1_post.json').write_text(json.dumps(out,ensure_ascii=False,indent=2,default=str)); print(json.dumps({'generated_at_utc':out['generated_at_utc'],'totals':out['totals'],'queue_counts':out['queue_counts'],'open_count':len(out['open_queue']),'fk_count':out['fk_count'],'integrity':out['integrity'],'foreign_keys':out['foreign_keys']},ensure_ascii=False,indent=2))