import sqlite3,json,os,re
K='/opt/struktur/youtube-research/knowledge.db'; c=sqlite3.connect('file:'+K+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
allf=[]
for t in [r[0] for r in c.execute("select name from sqlite_master where type='table' and name not like 'sqlite_%'")]:
 try:
  for r in c.execute('pragma foreign_key_list('+t+')'):
   if r['table']=='videos': allf.append({'table':t,'id':r['id'],'from':r['from'],'to':r['to'],'on_update':r['on_update'],'on_delete':r['on_delete']})
 except: pass
print('ALL_FKS_TO_VIDEOS',json.dumps(allf,ensure_ascii=False))
# exact child sets and counts
ids=[]
for r in c.execute('select distinct video_id from chapters where video_id not in (select id from videos) union select distinct video_id from video_links where video_id not in (select id from videos)'): ids.append(r[0])
ch={r[0]:r[1] for r in c.execute('select video_id,count(*) from chapters where video_id not in (select id from videos) group by video_id')}
li={r[0]:r[1] for r in c.execute('select video_id,count(*) from video_links where video_id not in (select id from videos) group by video_id')}
print('DETAIL',json.dumps({'ids':ids,'chapters_ids':sorted(ch),'links_ids':sorted(li),'overlap':sorted(set(ch)&set(li)),'counts':{str(i):{'chapters':ch.get(i,0),'video_links':li.get(i,0),'total':ch.get(i,0)+li.get(i,0)} for i in ids}},ensure_ascii=False))
c.close()
# targeted writer SQL search
root='/opt/struktur/youtube-research'; terms=re.compile(r'(?i)(insert\s+(or\s+ignore\s+|or\s+replace\s+)?into\s+videos|replace\s+into\s+videos|delete\s+from\s+videos|update\s+videos\s+set)')
for base,ds,fs in os.walk(root):
 ds[:]=[d for d in ds if d not in ['.git','__pycache__','.venv','venv','e2e-test']]
 for f in fs:
  if f.endswith('.py'):
   p=os.path.join(base,f)
   try: lines=open(p,encoding='utf-8',errors='ignore').read().splitlines()
   except: continue
   for i,line in enumerate(lines,1):
    if terms.search(line): print('MUT',p,i,line.strip()[:500])