#!/usr/bin/env python3
import sqlite3,os,re,json,unicodedata,hashlib
from collections import defaultdict,Counter
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'
FIELDS=['visual_subject','secondary_subjects','theme','subtheme','room_type','problem_type','technical_topic','content_type','visual_style','text_present','text_summary']
STOP=set('der die das den dem des ein eine einer einem einen und oder von mit für auf im in an zu als ist sind sich aus bei zur zum wird werden durch über unter sowie auch nicht keine'.split())
def txt(v):
 try:
  x=json.loads(v) if isinstance(v,str) and v[:1] in '[{' else v
  return ' '.join(map(str,x)) if isinstance(x,(list,dict)) else str(x or '')
 except:return str(v or '')
def toks(v):return {x for x in re.findall(r'[a-z0-9]{3,}',unicodedata.normalize('NFKD',txt(v)).lower()) if x not in STOP}
def norm(s):
 s=os.path.splitext(os.path.basename(s or ''))[0].lower();s=re.sub(r'\b(1[ _-]?1|16[ _-]?9|9[ _-]?16|4[ _-]?5)\b','',s);s=re.sub(r'(bearbeitet|viral|gut|hori|vert|breit|hoch|square|portrait|landscape|copy|final|neu|version|v\d+)','',s);return re.sub(r'[^a-z0-9äöüß]+','',s)
def tags(s):return set(re.findall(r'1[-_ ]?1|16[-_ ]?9|9[-_ ]?16|4[-_ ]?5|vert|hori|breit|gut|viral',(s or '').lower()))
def hs(path):
 try:
  im=Image.open(path);w,h=im.size;side=min(w,h);crop=im.crop(((w-side)//2,(h-side)//2,(w+side)//2,(h+side)//2))
  def ph(x):
   a=np.asarray(ImageOps.exif_transpose(x).convert('L').resize((32,32),Image.Resampling.LANCZOS),float);z=np.abs(np.fft.fft2(a))[:8,:8];return z>np.median(z[1:])
  def dh(x):
   a=np.asarray(ImageOps.exif_transpose(x).convert('L').resize((17,16),Image.Resampling.LANCZOS),float);return a[:,1:]>a[:,:-1]
  return [ph(im),ph(crop),dh(im),dh(crop)]
 except:return None
def dist(a,b):return float(np.mean(a!=b))
def main():
 c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True);c.row_factory=sqlite3.Row
 rows=c.execute('select v.*,a.* from visuals v join analyses a using(visual_id)').fetchall();by={r['visual_id']:r for r in rows}
 group={r['visual_id']:r['asset_group_id'] for r in c.execute('select visual_id,asset_group_id from visuals')}
 hashes={r['visual_id']:hs(os.path.join(BASE,r['stored_filename'])) for r in rows}
 safe=[]; pairno=0
 for i,a in enumerate(rows):
  for b in rows[i+1:]:
   if a['sha256']==b['sha256']:continue
   pairno+=1; ha,hb=hashes[a['visual_id']],hashes[b['visual_id']]
   if not ha or not hb:continue
   ph=1-min(dist(ha[i],hb[j]) for i in (0,1) for j in (0,1));dh=1-min(dist(ha[i],hb[j]) for i in (2,3) for j in (2,3)); vs=min(ph,dh)
   A=set().union(*(toks(a[f]) for f in FIELDS));B=set().union(*(toks(b[f]) for f in FIELDS));sem=len(A&B)/(len(A|B) or 1)
   na,nb=norm(a['original_filename']),norm(b['original_filename']);fn=na==nb and len(na)>=4;ft=len(toks(a['original_filename'])&toks(b['original_filename']));path=bool(tags(a['source_relative_path'])&tags(b['source_relative_path'])) or os.path.dirname(a['source_relative_path']).lower()==os.path.dirname(b['source_relative_path']).lower()
   strong=vs>=.84 and ((fn and sem>=.10) or (sem>=.30 and path))
   if strong:
    ga,gb=group[a['visual_id']],group[b['visual_id']]
    cls='ALREADY_SAME_GROUP' if ga and ga==gb else ('GROUP_PLUS_UNGROUPED' if bool(ga) ^ bool(gb) else ('DIFFERENT_EXISTING_GROUPS' if ga and gb else 'UNGROUPED_PLUS_UNGROUPED'))
    score=(.58*vs+.25*sem+.10*(1 if fn else min(ft/3,1))+.07*(1 if path else 0))*100
    safe.append({'pair_id':f'VP-{len(safe)+1:02d}','visual_id_a':a['visual_id'],'visual_id_b':b['visual_id'],'variant_score':round(score,2),'variant_confidence':round(score/100,3),'phash_similarity':round(ph,4),'dhash_similarity':round(dh,4),'semantic_jaccard':round(sem,4),'filename_similarity':fn,'filename_token_overlap':ft,'source_path_signal':path,'relationship':'IMPORTED_VARIANT','group_a':ga,'group_b':gb,'classification':cls,'a_file':a['original_filename'],'b_file':b['original_filename'],'a_path':a['source_relative_path'],'b_path':b['source_relative_path'],'a_subject':a['visual_subject'],'b_subject':b['visual_subject'],'aspect_delta':round(abs((a['aspect_ratio'] or 0)-(b['aspect_ratio'] or 0)),4)})
 counts=Counter(x['classification'] for x in safe)
 # candidates and whether pair endpoints same group
 cands=[dict(r) for r in c.execute('select * from variant_candidates')];cand_info=[]
 for z in cands:
  ga,gb=group.get(z['visual_id_a']),group.get(z['visual_id_b']);cand_info.append({'id':z['variant_candidate_id'],'a':z['visual_id_a'],'b':z['visual_id_b'],'group_a':ga,'group_b':gb,'same_group':bool(ga and ga==gb)})
 out={'safe_pairs_total':len(safe),'counts':dict(counts),'safe_pairs':safe,'candidate_count':len(cands),'candidate_info':cand_info,'group_count':c.execute('select count(*) from asset_groups').fetchone()[0],'grouped_visuals':sum(1 for g in group.values() if g),'ungrouped_visuals':sum(1 for g in group.values() if not g),'singleton_groups':sum(1 for gid,n in c.execute('select asset_group_id,count(*) from visuals where asset_group_id is not null group by asset_group_id') if n<2)}
 print(json.dumps(out,ensure_ascii=False,indent=2))
if __name__=='__main__':main()