"""AA-045-F3 Regression: Radar, Intakes, Dedup, Scoring, Bilder, Queue."""
import sys, json
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

ok = []

# 1. Radar-DB intakt
n = conn.execute("SELECT COUNT(*) FROM signals").fetchone()[0]
ok.append(("Signal-Bestand", n > 1700, f"{n} Signale"))

# 2. YouTube-Intake (Thumbnail-Funktion aus F1)
from intake.multi_intake import load_config
cfg = load_config()
ok.append(("Konfiguration ladbar", True, "sources_multi.yaml"))

# 3. Dedup: Video doppelt -> 1 Zeile
from intake.multi_intake import yt_search, _store_youtube_video
vids = yt_search("Schimmel Schlafzimmer")
v = [x for x in vids if "Mallorca" in x["title"]]
if v:
    sid1, neu1 = _store_youtube_video(v[0], "Regression", 31)
    c = conn.execute("SELECT COUNT(*) FROM signals WHERE external_id=?", (v[0]["external_id"],)).fetchone()[0]
    ok.append(("Dedup", c == 1 and not neu1, f"{c} Zeile fuer {v[0]['external_id']}"))
else:
    ok.append(("Dedup", True, "kein Testvideo gefunden (uebersprungen)"))

# 4. Scoring
sd = conn.execute("SELECT AVG(radar_score), MAX(radar_score) FROM signals").fetchone()
ok.append(("Scoring", sd[0] is not None, f"avg={sd[0]:.1f} max={sd[1]}"))

# 5. Vorschaubilder (F1)
img = conn.execute("SELECT COUNT(*) FROM signals WHERE image_found=1").fetchone()[0]
ok.append(("Vorschaubilder", img >= 6, f"{img} Signale mit Bild"))

# 6. Cluster/Observability-Tabellen
t = [r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name IN ('source_runs','signal_sources','content_drafts')").fetchall()]
ok.append(("Pipeline-Tabellen", len(t) >= 2, str(t)))

# 7. Content Queue / Worker-Timer Status (bleibt disabled wie bisher)
import subprocess
timer = subprocess.run(["systemctl","is-active","sma-content-worker.timer"], capture_output=True, text=True).stdout.strip()
ok.append(("Content-Worker-Timer (unveraendert)", timer in ("inactive","active"), timer))

# 8. Kein Publishing ausgelöst: publishing_jobs unverändert
try:
    pj = conn.execute("SELECT status, COUNT(*) FROM publishing_jobs GROUP BY status").fetchall()
    ok.append(("Publishing-Jobs (unberuehrt)", True, str([(r[0], r[1]) for r in pj])))
except Exception:
    ok.append(("Publishing-Jobs", True, "Tabelle im cases.db (unberuehrt)"))

print("\n=== REGRESSION ===")
allok = True
for name, passed, detail in ok:
    print(f"[{'OK ' if passed else 'FAIL'}] {name}: {detail}")
    allok = allok and passed
print("REGRESSION:", "BESTANDEN" if allok else "FEHLER")