import sqlite3,json,urllib.request
from dotenv import dotenv_values
DB='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True)
q="SELECT e.job_id,e.video_id,e.status,e.attempts,v.transcript_status,v.transcript_source,length(trim(coalesce(v.transcript,''))) transcript_len FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE v.youtube_id IS NOT NULL AND length(trim(coalesce(v.transcript,'')))=0 AND coalesce(v.transcript_status,'')<>'done' AND coalesce(v.transcript_source,'') NOT IN ('supadata_native','windows_local') AND e.status IN ('blocked_transcript_fetch','local_transcript_retry','local_transcript_pending','supadata_retry') ORDER BY CASE e.status WHEN 'blocked_transcript_fetch' THEN 0 WHEN 'local_transcript_retry' THEN 1 WHEN 'local_transcript_pending' THEN 2 ELSE 3 END,e.job_id"
out={'repaired':{'stale_complete':c.execute("SELECT COUNT(*) FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.job_id BETWEEN 129 AND 143 AND e.status='complete' AND v.transcript_status='done' AND length(trim(v.transcript))>0").fetchone()[0],'false_done':c.execute("SELECT COUNT(*) FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.job_id BETWEEN 33 AND 42 AND e.status='supadata_retry' AND v.transcript_status='retry' AND length(trim(coalesce(v.transcript,'')))=0").fetchone()[0],'missing_job_remaining':c.execute("SELECT COUNT(*) FROM videos v WHERE v.transcript_status='retry' AND length(trim(coalesce(v.transcript,'')))=0 AND NOT EXISTS (SELECT 1 FROM e2e_jobs e WHERE e.video_id=v.id)").fetchone()[0]},'candidate_count':c.execute('SELECT COUNT(*) FROM ('+q+')').fetchone()[0],'candidate_status':{r[0]:r[1] for r in c.execute('SELECT e.status,count(*) FROM ('+q+') e GROUP BY e.status')},'candidate_ids':[r[0] for r in c.execute(q)],'queue_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]}
key=dotenv_values('/opt/struktur/youtube-research/.env.supadata').get('SUPADATA_API_KEY'); req=urllib.request.Request('https://api.supadata.ai/v1/me',headers={'x-api-key':key,'Authorization':'Bearer '+key})
with urllib.request.urlopen(req,timeout=30) as r:
 d=json.load(r); out['account']={'http':r.status,'plan':d.get('plan'),'usedCredits':d.get('usedCredits'),'maxCredits':d.get('maxCredits'),'availableCredits_field':d.get('availableCredits'),'derived_remaining':(d.get('maxCredits')-d.get('usedCredits')) if isinstance(d.get('maxCredits'),int) and isinstance(d.get('usedCredits'),int) else None}
print(json.dumps(out,ensure_ascii=False,default=str)); c.close()