import sqlite3, json, datetime, subprocess, ast
from pathlib import Path
import requests
DB='/opt/struktur/youtube-research/knowledge.db'; env=Path('/opt/struktur/youtube-research/.env.supadata')
key=None
for line in env.read_text().splitlines():
    if line.startswith('SUPADATA_API_KEY='): key=line.split('=',1)[1].strip(); break
if not key: raise SystemExit('missing active key')
r=requests.get('https://api.supadata.ai/v1/me',headers={'x-api-key':key},timeout=30)
d=r.json() if r.headers.get('content-type','').startswith('application/json') else {}
c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
q="""SELECT e.job_id,e.video_id,v.youtube_id,e.status,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.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) ORDER BY e.job_id"""
rows=[dict(x) for x in c.execute(q)]
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])
show=subprocess.run(['systemctl','show','youtube-research-e2e-worker.service','-p','ActiveState','-p','SubState','-p','MainPID','-p','NRestarts'],text=True,capture_output=True).stdout
helpout=subprocess.run(['/opt/struktur/youtube-research/venv/bin/python','/opt/struktur/youtube-research/supadata_native_batch.py','--help'],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,'candidate_count':len(rows),'candidates':rows,'queue':queue,'worker':show,'processor_help':helpout},ensure_ascii=False))