"""AA-045-F4 Tests E-K (Links, Qualität) - DB-/HTML-Belege sammeln."""
import sys, json, urllib.request
sys.path.insert(0, "/opt/struktur/social-media-radar")
import sqlite3
conn = sqlite3.connect("/var/lib/sma-data/signals.db")
conn.row_factory = sqlite3.Row
results = []

# Test E: Einzelquelle-Link = echte Artikel-URL
r = conn.execute("""SELECT signal_id, topic, COALESCE(NULLIF(final_article_url,''), source_url) AS link
    FROM signals WHERE created_at>'2026-08-16' AND source_url LIKE 'http%'
      AND source_name NOT LIKE '%Google News%' LIMIT 1""").fetchone()
results.append(("E Einzelquelle", bool(r["link"]), f"{r['topic'][:40]} -> {r['link'][:70]}"))

# Test F: Mehrquellen - extra_sources mit URLs
r = conn.execute("""
    SELECT s.signal_id, s.topic, COUNT(es.id) AS n, SUM(CASE WHEN es.source_url LIKE 'http%' THEN 1 ELSE 0 END) AS n_url
    FROM signals s JOIN signal_sources es ON es.signal_id = s.signal_id
    WHERE s.source_count > 1 GROUP BY s.signal_id HAVING n >= 2 LIMIT 1""").fetchone()
results.append(("F Mehrquellen", r and r["n_url"] >= 1, f"{r['n']} Quellen, {r['n_url']} mit URL" if r else "keine"))

# Test G: GNews-Zielauflösung
n_final = conn.execute("""SELECT COUNT(*) FROM signals
    WHERE source_name LIKE '%Google News%' AND COALESCE(final_article_url,'') != ''""").fetchone()[0]
n_gnews = conn.execute("SELECT COUNT(*) FROM signals WHERE source_name LIKE '%Google News%'").fetchone()[0]
results.append(("G GNews-Ziel", n_gnews > 0, f"{n_final}/{n_gnews} aufgeloest (Rest: JS-Consent-Wall)"))

# Test H: echter Schimmeltreffer korrekt
cat = conn.execute("SELECT signal_category FROM signals WHERE signal_id LIKE '9a15eedd%'").fetchone()[0]
results.append(("H Schimmel korrekt", cat == "schimmel_risiko", cat))

# Test I: Fleischskandal nicht mehr schimmel_risiko
cat = conn.execute("SELECT signal_category FROM signals WHERE signal_id='4522b3d6-88be-4bfb-ada1-70e942a0c724'").fetchone()[0]
results.append(("I Fleischskandal umklassifiziert", cat == "allgemein", cat))

# Test J: FB-Gruppe - Kategorie bleibt (inhaltliche Evidenz), aber Director beobachten
row = conn.execute("SELECT signal_category, director_action FROM signals WHERE signal_id LIKE '919a871d%'").fetchone()
results.append(("J FB-Gruppe", row["director_action"] == "beobachten", f"cat={row['signal_category']} action={row['director_action']}"))

# Test K: Occupas als ferienimmobilie (Randthema), nicht feuchte/schimmel
cat = conn.execute("SELECT signal_category FROM signals WHERE signal_id LIKE '9b0a4244%'").fetchone()[0]
results.append(("K Occupas Randthema", cat == "ferienimmobilie", cat))

print("=== TESTS E-K ===")
allok = True
for name, ok, detail in results:
    print(f"[{'OK ' if ok else 'FAIL'}] {name}: {detail}")
    allok = allok and ok
print("GESAMT:", "BESTANDEN" if allok else "PRUEFEN")