from pathlib import Path
p=Path('/opt/struktur/social-media-agent/app.py'); s=p.read_text()
old='''        rows = conn.execute(
            "SELECT * FROM content_drafts WHERE status=? "
            "ORDER BY radar_score DESC, created_at DESC LIMIT ?",
            (status, limit)
        ).fetchall()
'''
new='''        rows = conn.execute(
            "SELECT d.* FROM content_drafts d JOIN signals s ON s.signal_id=d.signal_id "
            "WHERE d.status=? AND s.mas_relevant=1 AND COALESCE(s.content_blocked,0)=0 "
            "ORDER BY d.radar_score DESC, d.created_at DESC LIMIT ?",
            (status, limit)
        ).fetchall()
'''
assert old in s; s=s.replace(old,new)
old='''            "SELECT * FROM content_drafts "
            "ORDER BY radar_score DESC, created_at DESC LIMIT ?",
            (limit,)
'''
new='''            "SELECT d.* FROM content_drafts d JOIN signals s ON s.signal_id=d.signal_id "
            "WHERE s.mas_relevant=1 AND COALESCE(s.content_blocked,0)=0 "
            "ORDER BY d.radar_score DESC, d.created_at DESC LIMIT ?",
            (limit,),
'''
assert old in s; s=s.replace(old,new)
old='''            " FROM signals WHERE publish_status='bereit_zum_posten'"
'''
new='''            " FROM signals WHERE publish_status='bereit_zum_posten'"
            " AND mas_relevant=1 AND COALESCE(content_blocked,0)=0"
'''
assert old in s; s=s.replace(old,new)
old='''        sig = conn.execute(
            "SELECT topic, topic_de FROM signals WHERE signal_id=?", (signal_id,)
        ).fetchone()
'''
new='''        sig = conn.execute(
            "SELECT * FROM signals WHERE signal_id=? AND mas_relevant=1 AND COALESCE(content_blocked,0)=0", (signal_id,)
        ).fetchone()
        if not sig:
            conn.close()
            return jsonify({"error": "Signal ist nicht für MAS-Content zugelassen"}), 403
'''
# replace only first occurrence in mark-posted; there are multiple exact occurrences; use position after def
pos=s.index('def api_mark_posted'); tail=s[pos:]; assert old in tail; tail=tail.replace(old,new,1); s=s[:pos]+tail
p.write_text(s)