import sqlite3,json,glob,os,datetime,hashlib,subprocess
DB='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
out={'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat()}
out['videos']={s:c.execute('select count(*) from videos where transcript_status=?',(s,)).fetchone()[0] for s in ['done','retry','partial','none']}; out['videos']['total']=c.execute('select count(*) from videos').fetchone()[0]
out['e2e']={r['status']:r['n'] for r in c.execute('select status,count(*) n from e2e_jobs group by status')}
out['queue']={s:c.execute('select count(*) from graphiti_import_queue where graphiti_status=?',(s,)).fetchone()[0] for s in ['done','processing','retry_wait','failed_permanent','queued']}; out['queue']['total']=c.execute('select count(*) from graphiti_import_queue').fetchone()[0]
out['queue']['dupe_import']=c.execute('select count(*) from (select import_identity from graphiti_import_queue group by import_identity having count(*)>1)').fetchone()[0]; out['queue']['dupe_record']=c.execute('select count(*) from (select reconciliation_record_id from graphiti_import_queue group by reconciliation_record_id having count(*)>1)').fetchone()[0]; out['queue']['release_nonzero']=c.execute('select count(*) from graphiti_import_queue where coalesce(graphiti_release,0)<>0').fetchone()[0]
out['remaining_candidates']=c.execute("select count(*) from e2e_jobs e join videos v on v.id=e.video_id where v.youtube_id is not null and length(trim(coalesce(v.transcript,'')))=0 and coalesce(v.transcript_status,'')<>'done' and coalesce(v.transcript_source,'') not in ('supadata_native','windows_local') and e.status in ('blocked_transcript_fetch','local_transcript_retry','local_transcript_pending','supadata_retry')").fetchone()[0]
out['404_jobs']=[dict(r) for r in c.execute("select e.job_id,e.youtube_id,e.status,e.error_code,e.error_message,v.transcript_status,length(trim(coalesce(v.transcript,''))) transcript_len from e2e_jobs e join videos v on v.id=e.video_id where e.job_id in (198,245)")]
out['pragmas']={k:c.execute('pragma '+k).fetchone()[0] for k in ['journal_mode','busy_timeout','locking_mode','synchronous']}
out['process']=subprocess.run(['ps','-o','pid=,ppid=,etime=,stat=,cmd=','-p','1958979'],text=True,capture_output=True).stdout.strip()
out['worker_source_sha256']=hashlib.sha256(open('/opt/struktur/youtube-research/e2e_worker.py','rb').read()).hexdigest()
out['service_show']=subprocess.run(['systemctl','show','youtube-research-e2e-worker.service','-p','ActiveState','-p','SubState','-p','MainPID','-p','NRestarts','-p','ExecMainCode','-p','ExecMainStatus'],text=True,capture_output=True).stdout.strip()
print(json.dumps(out,ensure_ascii=False,default=str)); c.close()