import sqlite3,json,subprocess,glob,os,re
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'
out={}
def cmd(s):
 p=subprocess.run(['/bin/bash','-lc',s],text=True,capture_output=True); return {'rc':p.returncode,'out':p.stdout[-20000:],'err':p.stderr[-5000:]}
out['journal']=cmd("journalctl -u youtube-research-e2e-worker.service --since '2026-08-26 21:50:00' --until '2026-08-26 22:20:00' --no-pager -o short-iso")
out['unit']=cmd("systemctl cat youtube-research-e2e-worker.service")
out['status']=cmd("systemctl status youtube-research-e2e-worker.service --no-pager -l -n 30")
out['processes']=cmd("ps -eo pid,ppid,lstart,etime,stat,cmd --forest | grep -E 'youtube-research|supadata_native|e2e_worker|p28_local_fetcher|python' | grep -v grep")
out['writers_fds']=cmd("command -v lsof >/dev/null && lsof -nP '+D' /opt/struktur/youtube-research 2>/dev/null | grep -E 'knowledge.db|e2e_worker|supadata' | head -100 || true")
out['timers']=cmd("systemctl list-timers --all --no-pager | grep -Ei 'youtube|transcript|graphiti|content|e2e' || true")
out['cron']=cmd("grep -RInE 'knowledge.db|supadata|e2e_worker|youtube' /etc/cron* /var/spool/cron 2>/dev/null | head -100 || true")
try:
 c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
 out['pragma']={k:c.execute('PRAGMA '+k).fetchone()[0] for k in ['journal_mode','busy_timeout','locking_mode','synchronous']}
 out['counts']={'videos':c.execute('select count(*) from videos').fetchone()[0],'done':c.execute("select count(*) from videos where transcript_status='done'").fetchone()[0],'retry':c.execute("select count(*) from videos where transcript_status='retry'").fetchone()[0],'partial':c.execute("select count(*) from videos where transcript_status='partial'").fetchone()[0],'complete':c.execute("select count(*) from e2e_jobs where status='complete'").fetchone()[0],'supadata_retry':c.execute("select count(*) from e2e_jobs where status='supadata_retry'").fetchone()[0],'native_unavailable':c.execute("select count(*) from e2e_jobs where status='supadata_native_unavailable'").fetchone()[0],'blocked':c.execute("select count(*) from e2e_jobs where status='blocked_transcript_fetch'").fetchone()[0]}
 out['lock_artifacts']=[str(p) for p in Path('/opt/struktur/youtube-research').glob('knowledge.db*')]
 c.close()
except Exception as e: out['db_error']=repr(e)
# source snippets for sqlite connection/transactions
for p in ['/opt/struktur/youtube-research/e2e_worker.py','/opt/struktur/youtube-research/supadata_native_batch.py','/opt/struktur/youtube-research/db.py','/opt/struktur/graphiti/youtube_graphiti_queue.py']:
 if os.path.exists(p):
  lines=Path(p).read_text(errors='ignore').splitlines(); out.setdefault('code',{})[p]=[f'{i}:{l}' for i,l in enumerate(lines,1) if any(x in l for x in ['sqlite3.connect','busy_timeout','journal_mode','BEGIN','commit()','rollback()','close()','PRAGMA'])]
# stored R2 report outcomes, no network
for f in sorted(glob.glob('/opt/struktur/youtube-research/reports/p29/r2-batch-*-result.json')):
 try:
  d=json.load(open(f)); out.setdefault('r2_reports',[]).append({'file':os.path.basename(f),'results':[{'job_id':r.get('job_id'),'youtube_id':r.get('youtube_id'),'http':r.get('http'),'result':r.get('result'),'error_class':r.get('error_class')} for r in d.get('results',[]) if r.get('http') in (404,) or r.get('error_class') or r.get('result')!='imported']})
 except Exception as e: out.setdefault('report_errors',[]).append([f,repr(e)])
print(json.dumps(out,ensure_ascii=False,default=str))