import sqlite3, os, re, numpy as np
from PIL import Image
p='/opt/struktur/mas-visual-library/database/visual_library.db'; base='/opt/struktur/mas-visual-library/originals'; c=sqlite3.connect(p); c.row_factory=sqlite3.Row
rows=list(c.execute('select v.visual_id,v.stored_filename,v.original_filename,v.source_relative_path,a.visual_subject,a.theme,a.subtheme from visuals v join analyses a using(visual_id)'))
def tokens(s): return set(re.findall(r'[a-zäöüß]{3,}',(s or '').lower()))
def feat(r):
 im=Image.open(os.path.join(base,r['stored_filename'])).convert('L').resize((32,32)); x=np.asarray(im,dtype=np.float32); x=(x-x.mean())/(x.std()+1e-6); return x
F={r['visual_id']:feat(r) for r in rows}
def vis(a,b):
 x,y=F[a],F[b]; return float((np.sum(x*y)/(np.linalg.norm(x)*np.linalg.norm(y))+1)/2)
def pathsig(s): return set(re.findall(r'1-1|16-9|9_16|4-5|vert|hori',s.lower()))
pairs=[]
for i,a in enumerate(rows):
 ta=tokens(a['visual_subject'])|tokens(a['theme'])|tokens(a['subtheme'])
 for b in rows[i+1:]:
  tb=tokens(b['visual_subject'])|tokens(b['theme'])|tokens(b['subtheme']); sem=len(ta&tb)
  fn=tokens(os.path.splitext(a['original_filename'])[0])&tokens(os.path.splitext(b['original_filename'])[0]); path=pathsig(a['source_relative_path'])|pathsig(b['source_relative_path'])
  s=vis(a['visual_id'],b['visual_id'])
  # evidence candidates, not claims: semantic overlap plus visual, or same stem/path-family plus visual
  if s>=.88 and (sem>=2 or fn or path): pairs.append((s,sem,len(fn),a,b))
for s,sem,fn,a,b in sorted(pairs,key=lambda z:-z[0])[:60]: print(round(s,3), 'sem',sem,'fn',fn,a['visual_id'],b['visual_id'],'|',a['original_filename'],'<>',b['original_filename'],'|',a['source_relative_path'],'<>',b['source_relative_path'])
print('CANDIDATES',len(pairs))