import sqlite3,glob,os,json
c=sqlite3.connect('file:/opt/struktur/youtube-research/knowledge.db?mode=ro',uri=True)
out={}
out['db129']=c.execute("SELECT status,transcript_status FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.job_id=129").fetchone()
out['extraction129']=c.execute('SELECT COUNT(*) FROM e2e_extractions WHERE video_id=(SELECT video_id FROM e2e_jobs WHERE job_id=129)').fetchone()[0]
out['queue129']=c.execute('SELECT COUNT(*) FROM graphiti_import_queue WHERE video_id=(SELECT video_id FROM e2e_jobs WHERE job_id=129)').fetchone()[0]
out['queue_dupes']=c.execute('SELECT COUNT(*) FROM (SELECT import_identity FROM graphiti_import_queue GROUP BY import_identity HAVING COUNT(*)>1)').fetchone()[0]
out['false']=c.execute("SELECT status,transcript_status,COUNT(*) FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.job_id BETWEEN 33 AND 42 GROUP BY status,transcript_status").fetchall()
out['missing']=c.execute("SELECT COUNT(*) FROM videos v WHERE v.transcript_status='retry' AND length(trim(coalesce(v.transcript,'')))=0 AND NOT EXISTS (SELECT 1 FROM e2e_jobs e WHERE e.video_id=v.id)").fetchone()[0]
out['obs_files']=glob.glob('/opt/obsidian-vault/YouTube-Research/Automatisch/*')
print(json.dumps(out,default=str))
c.close()