import sqlite3,sys,json
sys.path.insert(0,'/opt/struktur/social-media-radar')
from mas_relevance import assess_mas_relevance
p='/var/lib/sma-data/signals.db'
con=sqlite3.connect(p); con.row_factory=sqlite3.Row
rows=con.execute('select * from signals').fetchall(); assert len(rows)==2230
changed=0; groups={'RELEVANT':0,'IRRELEVANT':0,'GRENZFALL':0,'TECHNISCHER MÜLL':0,'FALSCHE MARKE':0}
for r in rows:
    text=' '.join(str(r[k] or '') for k in ('topic','short_summary'))
    m=assess_mas_relevance(text,fields={'topic':r['topic'],'short_summary':r['short_summary']})
    g='TECHNISCHER MÜLL' if m.content_blocked else ('RELEVANT' if m.relevant else 'IRRELEVANT')
    groups[g]+=1
    con.execute('update signals set mas_relevant=?,mas_anchor=?,mas_relevance_reason=?,content_blocked=?,content_block_reason=? where signal_id=?',(int(m.relevant),','.join(m.anchor_matches),m.reason,int(m.content_blocked),m.block_reason,r['signal_id']))
    changed+=1
con.commit()
print('UPDATED',changed); print('GROUPS',json.dumps(groups,ensure_ascii=False))
print('COUNTS',[(r[0],r[1]) for r in con.execute('select mas_relevant,count(*) from signals group by mas_relevant')])
print('NULLS',con.execute('select count(*) from signals where mas_relevant is null').fetchone()[0])
print('INTEGRITY',con.execute('pragma integrity_check').fetchone()[0]); print('FOREIGN',con.execute('pragma foreign_key_check').fetchall())
con.close()