import sqlite3, json, sys, os, collections, importlib.util
DB='/var/lib/sma-data/signals.db'
con=sqlite3.connect('file:'+DB+'?mode=ro', uri=True)
con.row_factory=sqlite3.Row
# load current module exactly from production path
sys.path.insert(0,'/opt/struktur/social-media-radar')
from mas_relevance import assess_mas_relevance

def rows(sql, args=()): return [dict(r) for r in con.execute(sql,args)]
def dist(col, where=''):
    return rows(f"SELECT COALESCE(CAST({col} AS TEXT),'∅') value, COUNT(*) n FROM signals {where} GROUP BY {col} ORDER BY n DESC, value")
def nonempty_dist(col):
    return rows(f"SELECT COALESCE(NULLIF(TRIM(CAST({col} AS TEXT)),''),'∅') value, COUNT(*) n FROM signals GROUP BY {col} ORDER BY n DESC, value")
report={}
report['db']={'path':DB,'size':os.path.getsize(DB),'integrity':con.execute('pragma integrity_check').fetchone()[0],'count':con.execute('select count(*) from signals').fetchone()[0]}
report['schema']=rows("select name,type,\"notnull\",dflt_value,pk from pragma_table_info('signals')")
# requested distributions
for c in ['mas_relevant','content_blocked','mas_anchor','mas_relevance_reason','content_block_reason','source_type','source_platform','source_name','watch_entity_id','watch_sector','watch_priority','signal_category','topic_class','status','processing_state','publish_status','director_action','recommended_action','action_platform','platform_linkedin','platform_facebook','platform_instagram','platform_tiktok']:
 report['dist_'+c]=nonempty_dist(c)
for c in ['timestamp','created_at','observed_at','published_at','last_seen_at','ad_start_at']:
 report['null_'+c]=rows(f"SELECT CASE WHEN {c} IS NULL OR TRIM({c})='' THEN 'empty' ELSE 'nonempty' END value,COUNT(*) n FROM signals GROUP BY 1 ORDER BY 1")
 report['range_'+c]=rows(f"SELECT MIN({c}) min,MAX({c}) max FROM signals WHERE {c} IS NOT NULL AND TRIM({c})!=''")
# all downstream table counts and states
alltables=rows("select name from sqlite_master where type='table' and name not like 'sqlite_%' order by name")
report['table_counts']=[]
for x in alltables:
 t=x['name'];
 try:
  n=con.execute('select count(*) from "'+t.replace('"','""')+'"').fetchone()[0]
  report['table_counts'].append({'table':t,'count':n})
 except: pass
# source_runs latest and states
report['source_runs_latest']=rows('select source_name,run_at,last_success,last_error,items_seen,items_new,items_duplicate,items_rejected,platform,retrieval_mode from source_runs order by run_at desc limit 25')
# re-evaluate every signal using only topic+summary fields, preserving current function behavior
cats=collections.Counter(); diffs=collections.Counter(); examples=collections.defaultdict(list); evalrows=[]
for r in con.execute('select signal_id,mas_relevant,content_blocked,mas_anchor,mas_relevance_reason,content_block_reason,topic,short_summary,source_name,source_url,source_platform,source_type,watch_entity_id,watch_sector from signals'):
 d=dict(r); fields={'topic':d.get('topic') or '', 'short_summary':d.get('short_summary') or ''}
 a=assess_mas_relevance('',fields=fields)
 # operational groups: technical junk = missing/empty usable content; false brand = explicit Agent Solutions provenance; boundary = anchor but low-confidence/blocked/legacy conflicting state
 corpus=(fields['topic']+' '+fields['short_summary']).strip()
 false_brand=any('agent solutions' in str(d.get(k) or '').casefold() for k in ('source_name','source_url','watch_entity_id','watch_sector'))
 if false_brand: cat='FALSCHE MARKE'
 elif not corpus: cat='TECHNISCHER MÜLL'
 elif a.relevant and len(a.anchor_matches)==1: cat='GRENZFALL'
 elif a.relevant: cat='RELEVANT'
 else: cat='IRRELEVANT'
 cats[cat]+=1
 old=bool(d['mas_relevant'])
 new=bool(a.relevant) and not false_brand
 if old != new: diffs[(('RELEVANT' if old else 'IRRELEVANT'),('RELEVANT' if new else 'IRRELEVANT'))]+=1
 key=(cat, 'changed' if old!=new else 'same')
 if len(examples[key])<8: examples[key].append({'id':d['signal_id'],'topic':d['topic'],'summary':d['short_summary'],'old':int(old),'new':int(new),'anchors':a.anchor_matches,'reason':a.reason,'source':d['source_name'],'brand_fields':{k:d.get(k) for k in ('source_name','source_url','watch_entity_id','watch_sector')}})
 evalrows.append({'id':d['signal_id'],'old':int(old),'new':int(new),'blocked_new':int(a.content_blocked),'anchors':a.anchor_matches,'category':cat})
report['reeval_counts']=dict(cats)
report['reeval_differences']={f'{a}->{b}':n for (a,b),n in diffs.items()}
report['reeval_examples']={f'{k[0]}|{k[1]}':v for k,v in examples.items()}
report['current_vs_new']=rows('select mas_relevant,content_blocked,count(*) n from signals group by mas_relevant,content_blocked order by mas_relevant,content_blocked')
report['current_anchor_inconsistency']=rows("select signal_id,mas_relevant,mas_anchor,mas_relevance_reason,topic,short_summary from signals where trim(coalesce(mas_anchor,''))!='' and mas_relevant=0")
report['downstream_key_states']={}
for t,col in [('signals','processing_state'),('signals','publish_status'),('signals','status'),('signals','director_action'),('signals','recommended_action'),('posting_log','platform'),('operator_events','event_type'),('source_runs','last_error')]:
 try: report['downstream_key_states'][t+'.'+col]=rows(f"select coalesce(nullif(trim(cast({col} as text)),''),'∅') value,count(*) n from {t} group by {col} order by n desc,value")
 except Exception as e: report['downstream_key_states'][t+'.'+col]=str(e)
report['join_brandish_counts']=rows("select CASE WHEN lower(coalesce(source_name,'')||' '||coalesce(source_url,'')||' '||coalesce(watch_entity_id,'')||' '||coalesce(watch_sector,'')) like '%agent solutions%' THEN 'Agent Solutions mention' ELSE 'other' END brand, count(*) n from signals group by 1")
print(json.dumps(report,ensure_ascii=False,indent=2,default=str))