import json,sqlite3,os
from PIL import Image,ImageOps
import numpy as np
DB='/opt/struktur/mas-visual-library/database/visual_library.db';BASE='/opt/struktur/mas-visual-library/originals'
x=json.load(open('/tmp/MAS-VARIANTEN-FORENSIK-REPARATUR-20260904.report.json'))['productive']; pairs=x['safe_pairs']
c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True);c.row_factory=sqlite3.Row
G={r['visual_id']:r['asset_group_id'] for r in c.execute('select visual_id,asset_group_id from visuals')}; R={r['visual_id']:r for r in c.execute('select v.*,a.visual_subject from visuals v join analyses a using(visual_id)')}
def hashes(p):
 im=Image.open(p);w,h=im.size;s=min(w,h);cr=im.crop(((w-s)//2,(h-s)//2,(w+s)//2,(h+s)//2))
 def ph(z):
  a=np.asarray(ImageOps.exif_transpose(z).convert('L').resize((32,32),Image.Resampling.LANCZOS),float);q=np.abs(np.fft.fft2(a))[:8,:8];return q>np.median(q[1:])
 def dh(z):
  a=np.asarray(ImageOps.exif_transpose(z).convert('L').resize((17,16),Image.Resampling.LANCZOS),float);return a[:,1:]>a[:,:-1]
 return [ph(im),ph(cr),dh(im),dh(cr)]
H={}
for z in pairs:
 for v in [z['visual_id_a'],z['visual_id_b']]:
  if v not in H:H[v]=hashes(os.path.join(BASE,R[v]['stored_filename']))
out=[]
for i,z in enumerate(pairs,1):
 a,b=H[z['visual_id_a']],H[z['visual_id_b']]
 ph=1-min(float(np.mean(a[u]!=b[v])) for u in (0,1) for v in (0,1));dh=1-min(float(np.mean(a[u]!=b[v])) for u in (2,3) for v in (2,3))
 q=dict(z);q.update(pair_id=f'VP-{i:02d}',phash_similarity=round(ph,4),dhash_similarity=round(dh,4),group_a=G[z['visual_id_a']],group_b=G[z['visual_id_b']]);out.append(q)
print(json.dumps({'safe_pairs_total':len(out),'pairs':out,'classes':{k:sum(1 for z in out if z.get('group_a')==z.get('group_b') and z.get('group_a')) for k in ['same']},'group_counts':{k:sum(1 for z in out if z.get('classification')==k) for k in ['ALREADY_SAME_GROUP','GROUP_PLUS_UNGROUPED','UNGROUPED_PLUS_UNGROUPED','DIFFERENT_EXISTING_GROUPS']}},ensure_ascii=False,indent=2))