import sqlite3, json, urllib.request, datetime, re, os, hashlib
DB='/opt/struktur/youtube-research/knowledge.db'; VAULT='/opt/obsidian-vault/YouTube-Research'; OUT='/opt/struktur/reports/AA-045-preflight.json'
c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
rows=[dict(r) for r in c.execute("select * from graphiti_import_queue where graphiti_status in ('queued','retry_wait') order by id")]
vids=[r for r in rows if r.get('video_id') and re.fullmatch(r'[A-Za-z0-9_-]{11}',r['video_id'])]
non=[r for r in rows if r not in vids]
counts={}
for r in vids: counts[r['video_id']]=counts.get(r['video_id'],0)+1
try:
 data=json.load(urllib.request.urlopen('http://127.0.0.1:8644/episodes/inventory',timeout=120)); eps=data.get('episodes',[])
except Exception as e: eps=[]; err=type(e).__name__+': '+str(e)
else: err=None
out=[]
for r in vids:
 hits=[]
 for e in eps:
  s=json.dumps(e,ensure_ascii=False)
  if r.get('import_identity') and r['import_identity'] in s: hits.append(e.get('uuid'))
 p=r.get('obsidian_path') or ''; exists=os.path.isfile(p)
 if err: cls='D'
 elif len(set(hits))==0: cls='B'
 elif len(set(hits))==1: cls='A'
 else: cls='C'
 out.append({'queue_id':r['id'],'video_id':r['video_id'],'status':r['graphiti_status'],'import_identity':r.get('import_identity'),'transcript_status':None,'transcript_present':None,'obsidian_path':p,'obsidian_exists':exists,'queue_row_count_for_id':counts[r['video_id']],'episode_matches':sorted(set(hits)),'classification':cls})
# fill transcript data separately
for x in out:
 v=c.execute('select transcript_status,length(transcript) n from videos where youtube_id=?',(x['video_id'],)).fetchone(); x['transcript_status']=v['transcript_status'] if v else None; x['transcript_len']=v['n'] if v else 0; x['transcript_present']=bool(v and v['n'])
res={'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat(),'source_db':DB,'queue_filter':"queued/retry_wait",'inventory_endpoint':'GET http://127.0.0.1:8644/episodes/inventory','inventory_error':err,'open_queue_rows':len(rows),'youtube_rows':len(vids),'non_youtube_rows':len(non),'unique_youtube_ids':len(counts),'duplicate_ids':sum(v>1 for v in counts.values()),'class_counts':{k:sum(x['classification']==k for x in out) for k in 'ABCD'},'rows':out,'non_youtube':[{'queue_id':r['id'],'source_type':r['source_type'],'video_id':r['video_id'],'obsidian_path':r['obsidian_path'],'status':r['graphiti_status']} for r in non]}
open(OUT,'w').write(json.dumps(res,ensure_ascii=False,indent=2)+'\n'); print(json.dumps({'path':OUT,'sha256':hashlib.sha256(open(OUT,'rb').read()).hexdigest(),'A':res['class_counts']['A'],'B':res['class_counts']['B'],'C':res['class_counts']['C'],'D':res['class_counts']['D'],'non_youtube':len(non)}))