import sqlite3,json,datetime,subprocess
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
cols=[r['name'] for r in c.execute('pragma table_info(videos)')]; wanted=['id','youtube_id','title','channel','channel_title','published_at','url','transcript_status','transcript','transcript_source','updated_at']
sel=[x for x in wanted if x in cols]
q='select '+','.join(sel)+" from videos where youtube_id in ('P_gY0uz2xOo','9LBSIqAIkis','sKfhEQnAT0c','u1hmak0GoDU') order by id"
vs=[]
for r in c.execute(q):
 d=dict(r); d['transcript_len']=len((d.get('transcript') or '').strip()); d.pop('transcript',None); vs.append(d)
ecols=[r['name'] for r in c.execute('pragma table_info(e2e_jobs)')]; esel=[x for x in ['job_id','video_id','youtube_id','status','step','attempts','error_code','error_message','created_at','updated_at','completed_at','lease_until'] if x in ecols]
es=[dict(r) for r in c.execute('select '+','.join(esel)+' from e2e_jobs where job_id in (198,245,253,263) order by job_id')]
# recursive report extraction, only relevant job records and safe fields
root=Path('/opt/struktur/youtube-research/reports/p29'); hits=[]
def walk(x,path=''):
 if isinstance(x,dict):
  if x.get('job_id') in [198,245,253,263]:
   safe={k:v for k,v in x.items() if k.lower() not in ['api_key','key','authorization','headers'] and 'token' not in k.lower()}
   if 'response_headers' in safe: safe['response_headers']='[REDACTED/omitted]'
   hits.append({'path':path,'record':safe})
  for k,v in x.items(): walk(v,path+'/'+str(k))
 elif isinstance(x,list):
  for i,v in enumerate(x): walk(v,path+'/'+str(i))
for p in sorted(root.glob('r2-*.json'))+sorted(root.glob('r5-*.json')):
 try: walk(json.loads(p.read_text()),str(p))
 except Exception: pass
# files with relevant names and hashes
files=[{'path':str(p),'size':p.stat().st_size} for p in sorted(root.glob('r2-*.json'))+sorted(root.glob('r5-*.json'))]
print(json.dumps({'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat(),'video_columns':cols,'videos':vs,'e2e_columns':ecols,'e2e_jobs':es,'report_files':files,'report_hits':hits},ensure_ascii=False))