import sqlite3,json,os,glob,hashlib,datetime
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
ids=list(range(129,144)); rows=[]
for r in c.execute("SELECT e.job_id,e.video_id,e.youtube_id,e.status e2e_status,e.attempts,e.error_code,e.error_message,e.updated_at,v.title,v.transcript_status,v.transcript,v.transcript_hash,v.transcript_source,v.transcript_updated_at FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.job_id IN (%s) ORDER BY e.job_id"%(','.join('?'*len(ids))),ids):
 d=dict(r); d['transcript_len']=len(d.get('transcript') or ''); d['segments']=c.execute('SELECT COUNT(*) FROM transcript_segments WHERE video_id=?',(d['video_id'],)).fetchone()[0]; d['segment_chars']=c.execute('SELECT COALESCE(SUM(length(text)),0) FROM transcript_segments WHERE video_id=?',(d['video_id'],)).fetchone()[0]
 # search likely Obsidian references by youtube id, bounded filenames/content
 hits=[]
 for root,dirs,files in os.walk('/opt/obsidian-vault/YouTube-Research'):
  dirs[:]=[z for z in dirs if z not in {'.git','.obsidian'}]
  for fn in files:
   if fn.endswith('.md'):
    p=os.path.join(root,fn)
    try:
     if d['youtube_id'] in Path(p).read_text(errors='ignore'): hits.append(p)
    except OSError: pass
 d['obsidian_hits']=hits[:10]; d['obsidian_hit_count']=len(hits)
 rows.append(d)
# CE paths/schema and id hits, read-only
ce=[]
for p in ['/opt/struktur/content-extraction/data/content_extraction.db','/opt/struktur/content-extraction/content_extraction.db']:
 if os.path.exists(p):
  x=sqlite3.connect('file:'+p+'?mode=ro',uri=True); x.row_factory=sqlite3.Row
  tables=[r[0] for r in x.execute("SELECT name FROM sqlite_master WHERE type='table'")]
  ce.append({'path':p,'tables':tables})
  for d in rows:
   for t in tables:
    try:
     cols=[r[1] for r in x.execute('PRAGMA table_info('+t+')')]
     if 'source_video_id' in cols: d.setdefault('ce_hits',[]).append({'table':t,'count':x.execute('SELECT COUNT(*) FROM '+t+' WHERE source_video_id=?',(d['youtube_id'],)).fetchone()[0]})
    except Exception: pass
  x.close()
out={'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat(),'scope':'R1 snapshot + stale downstream audit','blocked_25':[dict(r) for r in c.execute("SELECT e.job_id,e.video_id,e.youtube_id,e.status e2e_status,e.attempts,e.error_code,e.error_message,e.updated_at,v.transcript_status,length(v.transcript) transcript_len,v.transcript_hash,v.transcript_source FROM e2e_jobs e JOIN videos v ON v.id=e.video_id WHERE e.status='blocked_transcript_fetch' ORDER BY e.job_id")], 'retry_81':[dict(r) for r in c.execute("SELECT id video_id,youtube_id,transcript_status,length(transcript) transcript_len,transcript_hash,transcript_source,updated_at FROM videos WHERE transcript_status='retry' ORDER BY id")], 'stale_audit':rows,'ce':ce}
print(json.dumps(out,ensure_ascii=False,default=str))
c.close()