import sqlite3,time,json,traceback
from youtube_transcript_api import YouTubeTranscriptApi
from youtube_transcript_api._errors import IpBlocked,RequestBlocked,NoTranscriptFound,TranscriptsDisabled,VideoUnavailable,YouTubeRequestFailed
DB='/opt/struktur/youtube-research/knowledge.db'
c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); rows=c.execute("SELECT youtube_id,attempts,updated_at,error_message,status FROM e2e_jobs WHERE status='blocked_transcript_fetch' ORDER BY updated_at DESC LIMIT 5").fetchall(); c.close()
def cls(ex):
 if isinstance(ex,IpBlocked): return 'IP_BLOCKED'
 if isinstance(ex,RequestBlocked): return 'REQUEST_BLOCKED'
 if isinstance(ex,TranscriptsDisabled): return 'TRANSCRIPT_NOT_AVAILABLE'
 if isinstance(ex,NoTranscriptFound): return 'TRANSCRIPT_NOT_AVAILABLE'
 if isinstance(ex,VideoUnavailable): return 'VIDEO_UNAVAILABLE'
 if isinstance(ex,YouTubeRequestFailed): return 'ANDERER_FEHLER'
 return 'ANDERER_FEHLER'
api=YouTubeTranscriptApi(); out=[]
for yid,attempts,updated,error,status in rows:
 result={'video_id':yid,'previous_status':status,'attempts':attempts,'last_attempt':updated,'result':None,'exceptions':[]}
 ok=False
 for langs in (['de'],['en'],None):
  try:
   r=api.fetch(yid,**({'languages':langs} if langs else {})); result['result']='ERFOLG'; result['segments']=len(r); ok=True; break
  except Exception as e:
   result['exceptions'].append({'language':langs,'class':type(e).__name__,'classification':cls(e),'message':str(e)[:300]})
  time.sleep(1)
 if not ok:
  result['result']=result['exceptions'][-1]['classification'] if result['exceptions'] else 'ANDERER_FEHLER'
 out.append(result); print(json.dumps(result,ensure_ascii=False),flush=True); time.sleep(2)