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,v.width,v.height,v.aspect_ratio,v.orientation,a.visual_subject,a.theme,a.subtheme,a.text_present,a.text_summary from visuals v join analyses a using(visual_id)'))
print('PATHS'); print([(r['stored_filename'],r['original_filename']) for r in rows[:3]])
def feat(r):
 path=os.path.join(base,r['stored_filename'])
 try:
  im=Image.open(path).convert('RGB').resize((32,32))
  ar=np.asarray(im,dtype=np.float32)/255
  gray=ar.mean(2)
  # normalized low-res visual signature, robust-ish to format/aspect differences
  g=(gray-gray.mean())/(gray.std()+1e-6)
  hist=np.concatenate([np.histogram(ar[:,:,k],bins=8,range=(0,1),density=True)[0] for k in range(3)])
  return g,hist
 except Exception as e: return None
F={r['visual_id']:feat(r) for r in rows}
def sim(a,b):
 ga,ha=F[a]; gb,hb=F[b]
 pix=1-float(np.mean(np.abs(ga-gb)))/4
 hist=1-float(np.mean(np.abs(ha-hb)))/8
 return .7*pix+.3*hist
# candidates constrained by curator semantic overlap OR filename/path signal, then visual score
pairs=[]
for i,a in enumerate(rows):
 for b in rows[i+1:]:
  if a['sha256'] if False else False: pass
  subj=(a['visual_subject'] or '').lower(); subjb=(b['visual_subject'] or '').lower()
  theme=(a['theme'] or '').lower(); themeb=(b['theme'] or '').lower()
  sem=len(set(re.findall(r'[a-zäöüß]{5,}',subj))&set(re.findall(r'[a-zäöüß]{5,}',subjb)))
  fn=len(set(re.findall(r'[a-zäöüß]{3,}',(a['original_filename'] or '').lower()))&set(re.findall(r'[a-zäöüß]{3,}',(b['original_filename'] or '').lower())))
  ps=bool(re.search(r'(1-1|16[-_ ]?9|9[-_ ]?16|4[-_ ]?5|vert|hori)',a['source_relative_path'].lower()+b['source_relative_path'].lower()))
  if sem>=1 or fn>=1 or ps:
   s=sim(a['visual_id'],b['visual_id'])
   if s>=.72: pairs.append((s,a,b,sem,fn))
for s,a,b,sem,fn in sorted(pairs,key=lambda x:-x[0])[:30]:
 print(round(s,3),a['visual_id'],b['visual_id'],'sem',sem,'fn',fn,'|',a['original_filename'],'<>',b['original_filename'],'|',a['source_relative_path'],'<>',b['source_relative_path'])
print('CANDIDATE_COUNT',len(pairs))