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
statuses={s:c.execute('select count(*) from videos where transcript_status=?',(s,)).fetchone()[0] for s in ['done','retry','partial','none']}
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']}
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])
rows=[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 between 247 and 256 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
journal=subprocess.run(['journalctl','-u','youtube-research-e2e-worker.service','--since','2026-08-27 13:00:40','--no-pager'],text=True,capture_output=True).stdout
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':statuses,'e2e':e2e,'queue':queue,'batch_jobs':rows,'worker':show,'lock_hits':[l for l in journal.splitlines() if 'locked' in l.lower() or 'traceback' in l.lower()],'journal_tail':journal[-4000:]},ensure_ascii=False))