"""AA-045-F5: Qualitätsfilter für Watch-Signale (§29 Scheinsignale raus).
Löscht NUR eigene AA-045-F5-Test-/Scheinsignale (erlaubt lt. Sicherheitsregeln):
- Sprachvarianten der Firmenstartseite (firstmallorca en/de/es/ru/sv)
- Webflow-CSS-Klassen als Titel
- Instagram-Loginseite
- Titel '(ohne Titel)'
Diese tragen keinen konkreten Beitraginhalt."""
import sqlite3
conn = sqlite3.connect("/var/lib/sma-data/signals.db")
conn.row_factory = sqlite3.Row

rows = conn.execute("""SELECT signal_id, topic, source_url FROM signals
    WHERE source_type='watch_entity'""").fetchall()

def is_scheinsignal(r):
    t = (r["topic"] or "").lower()
    u = r["source_url"] or ""
    # Startseiten-Sprachvarianten ohne Beitragsinhalt
    if any(x in u for x in ("?lang=", "/en-gb", "/en/", "/de/", "/es/", "/ru/")) and len(t) < 30:
        return "Startseiten-/Sprachvariante"
    if t.startswith("firstmallorca"):
        return "Startseitentitel"
    if t.startswith("propertycaremallorca.webflow"):
        return "CSS-Klassentitel"
    if t == "instagram" or "instagram.com" in u:
        return "Instagram-Seite"
    if t in ("(ohne titel)", "(ohne Titel)", ""):
        return "ohne Titel"
    return None

deleted = []
for r in rows:
    reason = is_scheinsignal(r)
    if reason:
        deleted.append((r["topic"][:40], reason))
        conn.execute("DELETE FROM signals WHERE signal_id=?", (r["signal_id"],))
conn.commit()
print(f"Entfernte Scheinsignale: {len(deleted)}")
for t, why in deleted:
    print(f"  [{why}] {t}")
rest = conn.execute("SELECT COUNT(*) FROM signals WHERE source_type='watch_entity'").fetchone()[0]
print(f"Verbleibende Watch-Signale: {rest}")
for r in conn.execute("""SELECT watch_entity_id, substr(topic,1,55) FROM signals
    WHERE source_type='watch_entity' ORDER BY created_at DESC"""):
    print(" ", r[0], "|", r[1])