import sqlite3,json,datetime,subprocess,requests
DB='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
videos={s:c.execute('select count(*) from videos where transcript_status=?',(s,)).fetchone()[0] for s in ['done','retry','partial','none']}; videos['total']=c.execute('select count(*) from videos').fetchone()[0]
e2e={x['status']:x['n'] for x in c.execute('select status,count(*) n from e2e_jobs group by status')}
queue={s:c.execute('select count(*) from graphiti_import_queue where graphiti_status=?',(s,)).fetchone()[0] for s in ['done','queued','retry_wait','processing','failed_permanent']}; queue.update(total=c.execute('select count(*) from graphiti_import_queue').fetchone()[0],dupe_import=c.execute('select count(*) from (select import_identity from graphiti_import_queue group by import_identity having count(*)>1)').fetchone()[0],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],release_nonzero=c.execute('select count(*) from graphiti_import_queue where coalesce(graphiti_release,0)<>0').fetchone()[0])
special=[dict(x) for x in c.execute("select e.job_id,e.video_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,253,263) order by e.job_id")]
show=subprocess.run(['systemctl','show','youtube-research-e2e-worker.service','-p','ActiveState','-p','SubState','-p','MainPID','-p','NRestarts','-p','ExecMainStatus'],text=True,capture_output=True).stdout
j=subprocess.run(['journalctl','-u','youtube-research-e2e-worker.service','--since','2026-08-27 13:00:00','--no-pager'],text=True,capture_output=True).stdout
locks=[l for l in j.splitlines() if any(x in l.lower() for x in ['database is locked','sqlite_busy','traceback'])]
try:
 r=requests.get('http://127.0.0.1:8000/v1/dashboard',timeout=20); dash={'http':r.status_code,'content_type':r.headers.get('content-type')}
except Exception as ex: dash={'error_class':type(ex).__name__}
print(json.dumps({'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat(),'videos':videos,'e2e':e2e,'queue':queue,'special_jobs':special,'worker':show,'lock_hits':locks,'dashboard':dash},ensure_ascii=False))