import sys, sqlite3
sys.path.insert(0,'/opt/struktur/social-media-radar')
from mas_relevance import assess_mas_relevance
DB='/var/lib/sma-data/signals.db'
con=sqlite3.connect(DB); con.row_factory=sqlite3.Row
before=con.execute("select count(*) from signals").fetchone()[0]
rows=con.execute('select * from signals order by signal_id').fetchall()
counts={'RELEVANT':0,'GRENZFALL':0,'IRRELEVANT':0,'TECHNISCHER MÜLL':0,'FALSCHE MARKE':0}
for r in rows:
    result=assess_mas_relevance('',fields={'topic':r['topic'],'short_summary':r['short_summary']})
    safety_existing = r['content_blocked'] == 1
    # Ein einzelner Anchor bleibt Review/Grenzfall; mindestens zwei eindeutige fachliche Treffer sind automatisch relevant.
    if safety_existing or result.content_blocked:
        group='TECHNISCHER MÜLL'; relevant=0
        reason = r['content_block_reason'] or result.block_reason or 'safety block preserved'
        blocked=1
    elif not result.relevant:
        group='IRRELEVANT'; relevant=0; reason=result.reason or 'Kein fachlicher MAS-Anker'; blocked=0
    elif len(result.anchor_matches) == 1:
        group='GRENZFALL'; relevant=0; reason='Grenzfall: nur ein fachlicher Anchor; manuelle Prüfung erforderlich'; blocked=0
    else:
        group='RELEVANT'; relevant=1; reason=result.reason; blocked=0
    counts[group]+=1
    anchor=', '.join(result.anchor_matches[:6])
    con.execute('''update signals set mas_relevant=?, mas_anchor=?, mas_relevance_reason=?, content_blocked=?, content_block_reason=? where signal_id=?''',
                (relevant, anchor, reason, blocked, reason if blocked else ('' if relevant else reason), r['signal_id']))
con.commit()
print('before',before,'after',con.execute('select count(*) from signals').fetchone()[0])
print(counts)
print('mas',con.execute('select mas_relevant,count(*) from signals group by mas_relevant').fetchall())
print('blocked',con.execute('select content_blocked,count(*) from signals group by content_blocked').fetchall())
print('integrity',con.execute('pragma integrity_check').fetchone()[0]); print('fk',con.execute('pragma foreign_key_check').fetchall())
con.close()