import sqlite3,json,datetime,os
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect(DB); c.row_factory=sqlite3.Row
out={'generated_at':datetime.datetime.now(datetime.timezone.utc).isoformat(),'db':{'size':os.path.getsize(DB),'integrity':c.execute('pragma integrity_check').fetchone()[0],'fk':len(c.execute('pragma foreign_key_check').fetchall()),'fk_mode':c.execute('pragma foreign_keys').fetchone()[0]},'queue_counts':[dict(x) for x in c.execute('select graphiti_status,count(*) n from graphiti_import_queue group by graphiti_status')],'targets':[dict(x) for x in c.execute('select id,source_type,video_id,obsidian_path,graphiti_status,graphiti_attempts,attempt_count,next_attempt_at,lock_owner,lease_expires_at,last_error_class,last_http_status,graphiti_episode_id,source_version_id,import_identity,remote_outcome,updated_at,completed_at from graphiti_import_queue where id in (1092,1268,347)')],'open':[dict(x) for x in c.execute("select id,source_type,video_id,obsidian_path,graphiti_status,next_attempt_at,attempt_count,graphiti_attempts,lock_owner,lease_expires_at,last_error_class,last_http_status,graphiti_episode_id,source_version_id,updated_at from graphiti_import_queue where graphiti_status!='done' order by id")],'parents':[]}
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')]
for i in ids:
 out['parents'].append({'parent':i,'chapters':c.execute('select count(*) from chapters where video_id=?',(i,)).fetchone()[0],'video_links':c.execute('select count(*) from video_links where video_id=?',(i,)).fetchone()[0],'registry':c.execute('select count(*) from source_processing_registry where source_id=? or source_external_id=?',(str(i),str(i))).fetchone()[0],'raw':c.execute('select count(*) from transcripts_raw where video_ref=?',(str(i),)).fetchone()[0],'segments':c.execute('select count(*) from transcript_segments where video_id=?',(i,)).fetchone()[0],'jobs':c.execute('select count(*) from e2e_jobs where video_id=?',(i,)).fetchone()[0],'queue':c.execute('select count(*) from graphiti_import_queue where video_id=?',(str(i),)).fetchone()[0]})
c.close(); Path('/tmp/phase1_final_data.json').write_text(json.dumps(out,ensure_ascii=False,indent=2,default=str)); print(json.dumps({'generated_at':out['generated_at'],'db':out['db'],'queue_counts':out['queue_counts'],'open':len(out['open']),'parents':len(out['parents'])},ensure_ascii=False))