#!/usr/bin/env python3
import sqlite3,os,re,json,hashlib,time,sys,unicodedata
from collections import defaultdict
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):
 if v is None:return ''
 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)
 except:return str(v)
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 hashes(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 sim(x,y):
 if not x or not y:return 0
 return 1-min(float(np.mean(a!=b)) for a in x[:2] for b in y[:2])+0.0 if False else 1-min([float(np.mean(x[i]!=y[j])) for i in (0,1) for j in (0,1)]+[float(np.mean(x[i]!=y[j])) for i in (2,3) for j in (2,3)])
def main():
 apply='--apply' in sys.argv
 c=sqlite3.connect(DB);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}; hs={r['visual_id']:hashes(os.path.join(BASE,r['stored_filename'])) for r in rows}
 # Existing groups are retained as validated baseline; this prevents a repaired run from silently dropping prior relations.
 seed=[]
 for gid, in c.execute('select asset_group_id from asset_groups'):
  m=[r['visual_id'] for r in c.execute('select visual_id from visuals where asset_group_id=?',(gid,))]
  if len(m)>1: seed += list(zip(m,m[1:]))
 safe=[]; cand=[]
 for i,a in enumerate(rows):
  for b in rows[i+1:]:
   if a['sha256']==b['sha256']:continue
   vs=sim(hs[a['visual_id']],hs[b['visual_id']]); 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))
   candidate=vs>=.78 and ((fn and sem>=.12) or (sem>=.24 and (path or ft>=1)))
   z={'visual_id_a':a['visual_id'],'visual_id_b':b['visual_id'],'variant_score':round((.58*vs+.25*sem+.10*(1 if fn else min(ft/3,1))+.07*(1 if path else 0))*100,2),'variant_confidence':round(.58*vs+.25*sem+.10*(1 if fn else min(ft/3,1))+.07*(1 if path else 0),3),'visual_similarity':round(vs,4),'semantic_jaccard':round(sem,4),'evidence':{'filename':fn,'filename_token_overlap':ft,'related_folder':path,'aspect_delta':round(abs((a['aspect_ratio'] or 0)-(b['aspect_ratio'] or 0)),4),'sha256_used_for_decision':False}}
   if strong:safe.append(z)
   elif candidate:cand.append(z)
 edges=seed
 adj=defaultdict(set)
 # Existing groups are the validated ground-truth seed; newly detected pairs remain auditable until reviewed rather than merging groups transitively on weak evidence.
 for a,b in edges:adj[a].add(b);adj[b].add(a)
 comps=[];seen=set()
 for v in sorted(adj):
  if v in seen:continue
  st=[v];seen.add(v);cc=[]
  while st:
   x=st.pop();cc.append(x)
   for y in adj[x]:
    if y not in seen:seen.add(y);st.append(y)
  if len(cc)>1:comps.append(sorted(cc))
 group_of={v:i for i,cc in enumerate(comps) for v in cc}
 cand=[z for z in cand if group_of.get(z['visual_id_a'])!=group_of.get(z['visual_id_b'])]
 out={'dry_run':not apply,'images':len(rows),'analysis_rows':c.execute('select count(*) from analyses').fetchone()[0],'pairs_examined':len(rows)*(len(rows)-1)//2,'safe_new_pairs':len(safe),'candidate_pairs':len(cand),'proposed_groups':len(comps),'grouped_visuals':sum(map(len,comps)),'unique_assets':len(rows)-sum(len(x)-1 for x in comps),'groups':comps,'safe_pairs':safe,'candidates':sorted(cand,key=lambda z:-z['variant_score'])}
 if apply:
  now=time.time(); c.execute('update visuals set asset_group_id=NULL');c.execute('delete from asset_groups');c.execute('delete from variant_candidates')
  for cc in comps:
   gid='AG-'+hashlib.sha256('|'.join(cc).encode()).hexdigest()[:16].upper(); c.execute('insert into asset_groups values(?,?,?,?)',(gid,'IMPORTED_VARIANT',None,now));c.executemany('update visuals set asset_group_id=? where visual_id=?',[(gid,x) for x in cc])
  for z in cand:c.execute('insert into variant_candidates(visual_id_a,visual_id_b,status,evidence,created_at) values(?,?,?,?,?)',(z['visual_id_a'],z['visual_id_b'],'VARIANT_CANDIDATE',json.dumps({'variant_score':z['variant_score'],'variant_confidence':z['variant_confidence'],'visual_similarity':z['visual_similarity'],'semantic_jaccard':z['semantic_jaccard'],'evidence':z['evidence']},ensure_ascii=False),now))
  c.commit();out['dry_run']=False;out['integrity']=c.execute('pragma integrity_check').fetchone()[0];out['asset_groups_after']=c.execute('select count(*) from asset_groups').fetchone()[0];out['candidates_after']=c.execute('select count(*) from variant_candidates').fetchone()[0]
 print(json.dumps(out,ensure_ascii=False,indent=2))
if __name__=='__main__':main()