import sqlite3, os, json, datetime
p='/opt/struktur/youtube-research/knowledge.db'
c=sqlite3.connect('file:'+p+'?mode=ro', uri=True)
out={'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat()}
for k,q in {
'videos':'select count(*) from videos',
'unique_youtube_ids':'select count(distinct youtube_id) from videos where youtube_id is not null and youtube_id != ""',
'registry_rows':'select count(*) from source_processing_registry',
'queue_rows':'select count(*) from graphiti_import_queue',
'queue_unique_ids':'select count(distinct video_id) from graphiti_import_queue where video_id is not null and video_id != ""',
}.items(): out[k]=c.execute(q).fetchone()[0]
out['transcript_status']=c.execute('select coalesce(transcript_status,"<NULL>"),count(*) from videos group by transcript_status order by 1').fetchall()
out['registry_status']=c.execute('select coalesce(graphiti_status,"<NULL>"),count(*) from source_processing_registry group by graphiti_status order by 1').fetchall()
out['queue_status']=c.execute('select coalesce(graphiti_status,"<NULL>"),count(*) from graphiti_import_queue group by graphiti_status order by 1').fetchall()
for table,col in [('source_processing_registry','obsidian_artifact_path'),('graphiti_import_queue','obsidian_path')]:
 try: out[table+'_paths']=c.execute('select '+col+',count(*) from '+table+' where '+col+' is not null group by '+col+' order by count(*) desc limit 10').fetchall()
 except Exception as e: out[table+'_paths_error']=str(e)
print(json.dumps(out,ensure_ascii=False))
c.close()