import sqlite3,json,glob,os,urllib.request,subprocess,datetime,hashlib
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'; BASE='/opt/struktur/youtube-research/reports/p29'; c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
batch=[]
for f in sorted(glob.glob(BASE+'/r2-batch-*-result.json')):
 d=json.load(open(f)); cls={}
 for r in d.get('results',[]): cls[r.get('result','unknown')]=cls.get(r.get('result','unknown'),0)+1
 batch.append({'file':os.path.basename(f),'before':d.get('before'),'after':d.get('after'),'candidate_count':d.get('candidate_count'),'classes':cls,'requests':len(d.get('results',[]))})
q={s:c.execute('SELECT COUNT(*) FROM graphiti_import_queue WHERE graphiti_status=?',(s,)).fetchone()[0] for s in ['done','processing','retry_wait','failed_permanent','queued']}; q['total']=c.execute('SELECT COUNT(*) FROM graphiti_import_queue').fetchone()[0]
out={'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat(),'videos_total':c.execute('SELECT COUNT(*) FROM videos').fetchone()[0],'video_status':{s:c.execute('SELECT COUNT(*) FROM videos WHERE transcript_status=?',(s,)).fetchone()[0] for s in ['done','retry','partial','none']},'e2e_status':{r['status']:r['n'] for r in c.execute('SELECT status,COUNT(*) n FROM e2e_jobs GROUP BY status')},'transcript_source':{str(r['transcript_source']):r['n'] for r in c.execute('SELECT transcript_source,COUNT(*) n FROM videos GROUP BY transcript_source')},'remaining_candidates':c.execute("SELECT COUNT(*) 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')").fetchone()[0],'remaining_404_jobs':[dict(r) for r in c.execute("SELECT e.job_id,e.youtube_id,e.status,e.error_code,e.error_message,v.transcript_status FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.error_code='http_404'")],'queue':q,'queue_dupe_import':c.execute('SELECT COUNT(*) FROM (SELECT import_identity FROM graphiti_import_queue GROUP BY import_identity HAVING COUNT(*)>1)').fetchone()[0],'queue_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],'batch':batch}
key=__import__('dotenv').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'),'derived_remaining':d.get('maxCredits')-d.get('usedCredits')}
print(json.dumps(out,ensure_ascii=False,default=str))
c.close()