import sqlite3, re, importlib.util
from collections import defaultdict
p='/opt/struktur/mas-visual-library/database/visual_library.db'
c=sqlite3.connect(p); c.row_factory=sqlite3.Row
rows=list(c.execute('select v.visual_id,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)'))
def norm(s): return re.sub(r'[^a-z0-9äöüß]+',' ',(s or '').lower()).strip()
g=defaultdict(list)
for r in rows: g[(norm(r['visual_subject']),norm(r['theme']))].append(r)
print('FAMILIES_BY_SUBJECT_THEME')
n=0
for k,rs in sorted(g.items(),key=lambda x:-len(x[1])):
 if len(rs)>1:
  print('FAMILY',len(rs),k)
  for r in rs[:6]: print(' ',r['visual_id'],r['orientation'],round(r['aspect_ratio'],3),r['source_relative_path'],'|',r['original_filename'])
  n+=1
  if n>=10: break
print('FOLDER_COUNTS')
for r in c.execute("select case when instr(source_relative_path,'/')>0 then substr(source_relative_path,1,instr(source_relative_path,'/')-1) else source_relative_path end folder,count(*) n from visuals group by folder"):
 print(dict(r))
print('VARIANT_PATH_COUNTS')
for pat in ['1-1','16-9','9_16','4-5','vert','hori']:
 print(pat,sum(pat in r['source_relative_path'].lower() for r in rows))
print('MODULES')
for m in ['PIL','cv2','numpy','imagehash']:
 try: print(m,bool(importlib.util.find_spec(m)))
 except Exception as e: print(m,'ERR',e)