import sqlite3,json,datetime,subprocess,hashlib,os
from pathlib import Path
import requests
DB='/opt/struktur/youtube-research/knowledge.db'; root=Path('/opt/struktur/youtube-research')
key=next(x.split('=',1)[1].strip() for x in (root/'.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:'+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['total']=c.execute('select count(*) from graphiti_import_queue').fetchone()[0]
queue.update(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])
constraints=[dict(x) for x in c.execute("select type,name,sql from sqlite_master where type in ('table','index') and (name like '%e2e%' or name like '%queue%')")]
retry_jobs=[dict(x) for x in c.execute("select e.job_id,e.video_id,e.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' order by e.job_id")]
show=subprocess.run(['systemctl','show','youtube-research-e2e-worker.service','-p','ActiveState','-p','SubState','-p','MainPID','-p','NRestarts','-p','ExecStart'],text=True,capture_output=True).stdout
p=subprocess.run(['ps','-ef'],text=True,capture_output=True).stdout
active=[l for l in p.splitlines() if any(x in l for x in ['supadata_native_batch.py','local_transcript','p28']) and 'grep' not in l]
files={str(root/x):hashlib.sha256((root/x).read_bytes()).hexdigest() for x in ['e2e_worker.py','supadata_native_batch.py','db.py']}
mode=(root/'.env.supadata').stat().st_mode&0o777
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,'retry_jobs':retry_jobs,'queue':queue,'constraints':constraints,'worker':show,'active_related_processes':active,'file_hashes':files,'env_mode':oct(mode),'p28_unit':subprocess.run(['systemctl','is-active','youtube-research-p28-fetcher.service'],text=True,capture_output=True).stdout.strip()},ensure_ascii=False))