import sqlite3,json,datetime,subprocess,requests
from pathlib import Path
key=next(x.split('=',1)[1].strip() for x in Path('/opt/struktur/youtube-research/.env.supadata').read_text().splitlines() if x.startswith('SUPADATA_API_KEY='))
r=requests.get('https://api.supadata.ai/v1/me',headers={'x-api-key':key},timeout=30); d=r.json()
c=sqlite3.connect('file:/opt/struktur/youtube-research/knowledge.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])
ids=[198,245,253,263]; special=[dict(x) for x in c.execute("select e.job_id,e.youtube_id,e.status,e.error_code,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")]
open_empty=c.execute("select count(*) from videos where transcript_status='retry' and length(trim(coalesce(transcript,'')))=0").fetchone()[0]
normal=c.execute("select count(*) from e2e_jobs e join videos v on v.id=e.video_id where e.status='supadata_retry' and v.transcript_status='retry' and length(trim(coalesce(v.transcript,'')))=0 and v.youtube_id is not null and e.job_id not in (198,245,253,263)").fetchone()[0]
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
journal=subprocess.run(['journalctl','-u','youtube-research-e2e-worker.service','--since','2026-08-27 13:00:00','--no-pager'],text=True,capture_output=True).stdout
lock_hits=[l for l in journal.splitlines() if any(t in l.lower() for t in ['database is locked','sqlite_busy','traceback'])]
try:
 dr=requests.get('http://127.0.0.1:8000/v1/dashboard',timeout=20); dashboard={'http':dr.status_code,'content_type':dr.headers.get('content-type'),'json_keys':list(dr.json().keys()) if dr.headers.get('content-type','').startswith('application/json') and isinstance(dr.json(),dict) else None}
except Exception as ex: dashboard={'error_class':type(ex).__name__}
ps=subprocess.run(['ps','-ef'],text=True,capture_output=True).stdout
active_batch=[l for l in ps.splitlines() if 'supadata_native_batch.py' in l and 'grep' not in l]
print(json.dumps({'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat(),'account_http':r.status_code,'account':{k:d.get(k) for k in ['plan','usedCredits','maxCredits','availableCredits']},'computed_available':d.get('maxCredits')-d.get('usedCredits') if isinstance(d.get('maxCredits'),int) and isinstance(d.get('usedCredits'),int) else None,'videos':videos,'e2e':e2e,'queue':queue,'open_empty_transcripts':open_empty,'normal_candidates_excluding_all_404_and_403':normal,'special_jobs':special,'worker':show,'lock_hits':lock_hits,'dashboard':dashboard,'active_batch_processes':active_batch},ensure_ascii=False))