"""
Social Media Agent v2 -- Flask App
Port: 8652
Analysiert virale Social-Media-Inhalte und transformiert sie
fuer Mallorca AirServices.
"""
import os
import sys
import json
import threading
import time
import stat
from collections import defaultdict, deque
from pathlib import Path
from radar_database import RADAR_DB_PATH
from datetime import datetime, timezone
from flask import Flask, abort, render_template, request, jsonify, redirect, send_file, url_for, send_from_directory
BASE_DIR = Path(__file__).parent
sys.path.insert(0, str(BASE_DIR))
APP_HOST = os.getenv("SMA_HOST", "127.0.0.1")
APP_PORT = int(os.getenv("SMA_PORT", "8652"))
from db import init_db, save_post, get_posts, get_post
from services.analyzer import analyze
from services.scraper import fetch_text
from cases_db import (
init_cases_db, get_cases, get_case, create_case,
update_case_status, update_case_field,
add_media, delete_media, link_signal_to_case,
unlink_signal, get_signals_for_case,
get_ops_stats, add_audit, get_audit,
create_content_item, get_content_items, update_content_status,
get_content_item, update_content_item, get_pipeline_board,
link_media_to_content, unlink_media_from_content,
get_publishing_jobs, create_publishing_job, update_publishing_job_status,
get_publishing_job, mark_publishing_job_published,
get_stale_signals, get_top_priority_signals, get_recent_cases,
get_content_in_progress,
)
# Radar-DB fuer Signal-Verknuepfung
RADAR_DB = RADAR_DB_PATH
CASES_DB = Path(os.environ.get(
"SMA_CASES_DATABASE_PATH",
"/opt/struktur/social-media-agent/cases.db",
)).expanduser().resolve()
UPLOAD_DIR = BASE_DIR / "uploads"
UPLOAD_DIR.mkdir(exist_ok=True)
IMG_CACHE_DIR = BASE_DIR / "static" / "img_cache"
IMG_CACHE_DIR.mkdir(parents=True, exist_ok=True)
ALLOWED_EXT = {".jpg", ".jpeg", ".png", ".gif", ".webp", ".mp4", ".mov"}
app = Flask(__name__, template_folder="templates", static_folder="static")
app.secret_key = os.getenv("SECRET_KEY", "sma-mallorca-2026")
app.config["MAX_CONTENT_LENGTH"] = 50 * 1024 * 1024 # 50 MB fuer Media-Uploads
MEDIA_DIR = BASE_DIR / "static" / "media" / "cases"
MEDIA_DIR.mkdir(parents=True, exist_ok=True)
# ---------------------------------------------------------------------------
# Hilfsfunktionen
# ---------------------------------------------------------------------------
def get_radar_signals(limit=12, days=3, sort="relevance",
status_filter=None, competition_only=False, pinned_only=False):
"""Holt Radar-Signale mit Filter- und Sortieroptionen."""
if not RADAR_DB.exists():
return []
try:
import sqlite3
from datetime import timedelta
conn = sqlite3.connect(str(RADAR_DB))
conn.row_factory = sqlite3.Row
now = datetime.now(timezone.utc)
cutoff = (now - timedelta(days=max(days, 1))).isoformat() if days else "1970-01-01"
if status_filter and status_filter != "alle":
st_clause = "AND status = ?"
st_params = [status_filter]
elif pinned_only:
st_clause = "AND pinned = 1"
st_params = []
else:
st_clause = "AND status NOT IN ('ignoriert','veroeffentlicht')"
st_params = []
comp_clause = "AND competition_signal = 1" if competition_only else ""
sort_map = {
"op_priority": "pinned DESC, operational_priority DESC, radar_score DESC",
"relevance": "pinned DESC, radar_score DESC",
"newest": "pinned DESC, created_at DESC",
"virality": "pinned DESC, virality_level DESC, radar_score DESC",
"emotionality": "pinned DESC, emotionality_level DESC, radar_score DESC",
"linkedin": "pinned DESC, platform_linkedin DESC, radar_score DESC",
"facebook": "pinned DESC, platform_facebook DESC, radar_score DESC",
"tiktok": "pinned DESC, platform_tiktok DESC, radar_score DESC",
}
order_by = sort_map.get(sort, "pinned DESC, operational_priority DESC, radar_score DESC")
sql = (
"SELECT signal_id, signal_category, radar_score, topic, extracted_hook,"
" expires_at, source_name, source_url, timestamp, language,"
" short_summary, emotional_direction, urgency_level,"
" mallorca_relevance, risk_relevance, suggested_case_types,"
" status, score_breakdown, virality_level, emotionality_level,"
" comment_potential, content_potential,"
" COALESCE(source_count,1) as source_count, topic_de, image_url,"
" image_source, COALESCE(image_found,0) as image_found, final_article_url,"
" COALESCE(platform_linkedin,1) as platform_linkedin,"
" COALESCE(platform_facebook,1) as platform_facebook,"
" COALESCE(platform_instagram,1) as platform_instagram,"
" COALESCE(platform_tiktok,1) as platform_tiktok,"
" COALESCE(topic_class,'sekundaer') as topic_class,"
" COALESCE(competition_signal,0) as competition_signal,"
" COALESCE(pinned,0) as pinned,"
" COALESCE(operational_priority,0) as operational_priority,"
" COALESCE(recommended_action,'') as recommended_action,"
" COALESCE(action_platform,'') as action_platform,"
" COALESCE(director_score,0) as director_score,"
" COALESCE(director_action,'') as director_action,"
" COALESCE(director_reason,'') as director_reason,"
" COALESCE(director_blocks,'') as director_blocks,"
" COALESCE(director_dims,'') as director_dims,"
" COALESCE(publishing_value,0) as publishing_value,"
" COALESCE(relevance_score,0) as relevance_score,"
" COALESCE(briefing_winkel,'') as briefing_winkel,"
" COALESCE(briefing_nutzen,'') as briefing_nutzen,"
" COALESCE(briefing_zielgruppe,'') as briefing_zielgruppe,"
" COALESCE(briefing_post_typ,'') as briefing_post_typ,"
" COALESCE(briefing_fb_fit,'') as briefing_fb_fit,"
" COALESCE(briefing_fb_reason,'') as briefing_fb_reason,"
" COALESCE(briefing_li_fit,'') as briefing_li_fit,"
" COALESCE(briefing_li_reason,'') as briefing_li_reason,"
" COALESCE(briefing_material,'') as briefing_material,"
" COALESCE(briefing_risiko,'') as briefing_risiko,"
" COALESCE(briefing_freigabe,'') as briefing_freigabe,"
" created_at"
" FROM signals"
" WHERE radar_score >= 31"
" AND (mallorca_relevance > 0 OR director_action IN ('pruefen','veroeffentlichen'))"
" AND created_at > ?"
f" {st_clause} {comp_clause}"
f" ORDER BY {order_by} LIMIT ?"
)
params = [cutoff] + st_params + [limit]
rows = conn.execute(sql, params).fetchall()
result = []
for r in rows:
d = dict(r)
try:
d["score_breakdown"] = json.loads(d.get("score_breakdown") or "{}")
except Exception:
d["score_breakdown"] = {}
d["source_count"] = int(d.get("source_count") or 1)
if d["source_count"] > 1:
try:
src_rows = conn.execute(
"SELECT source_name, source_url FROM signal_sources WHERE signal_id = ? LIMIT 5",
(d["signal_id"],)
).fetchall()
d["extra_sources"] = [dict(r) for r in src_rows]
except Exception:
d["extra_sources"] = []
else:
d["extra_sources"] = []
result.append(d)
conn.close()
# Dedup: keep highest-score signal per (category, topic-prefix)
# SQL already orders by score DESC, so first occurrence wins
seen = {}
deduped = []
for d in result:
key = (
d.get("signal_category", ""),
(d.get("topic") or d.get("extracted_hook") or "")[:55].strip().lower()
)
if key not in seen:
seen[key] = True
deduped.append(d)
return deduped
except Exception:
return []
def get_radar_score_stats():
"""Score-Statistiken aus der Radar-DB."""
if not RADAR_DB.exists():
return {}
try:
import sqlite3
conn = sqlite3.connect(str(RADAR_DB))
rows = conn.execute(
"SELECT radar_score FROM signals WHERE status NOT IN ('ignoriert', 'veroeffentlicht')"
).fetchall()
conn.close()
if not rows:
return {}
scores = sorted(r[0] for r in rows)
n = len(scores)
total = sum(scores)
median = scores[n // 2] if n % 2 else (scores[n // 2 - 1] + scores[n // 2]) / 2
buckets = {"irrelevant": 0, "beobachten": 0, "interessant": 0, "operativ": 0, "prioritaet": 0}
for s in scores:
if s <= 30: buckets["irrelevant"] += 1
elif s <= 50: buckets["beobachten"] += 1
elif s <= 69: buckets["interessant"] += 1
elif s <= 84: buckets["operativ"] += 1
else: buckets["prioritaet"] += 1
clustered = max(buckets.values()) / n if n > 0 else 0
return {
"count": n,
"min": round(scores[0], 1),
"max": round(scores[-1], 1),
"avg": round(total / n, 1),
"median": round(median, 1),
"buckets": buckets,
"warning": clustered > 0.5,
}
except Exception:
return {}
def match_case(problem_type: str) -> dict:
"""Sucht passenden Fall in der Cases-DB."""
if not CASES_DB.exists():
return {"status": "no_db"}
try:
import sqlite3
conn = sqlite3.connect(str(CASES_DB))
conn.row_factory = sqlite3.Row
row = conn.execute("""
SELECT case_id, problem_type, short_description, priority
FROM cases
WHERE problem_type LIKE ?
AND release_ok = 1
ORDER BY priority ASC LIMIT 1
""", (f"%{problem_type}%",)).fetchone()
conn.close()
if row:
return {"status": "found", "case": dict(row)}
return {"status": "missing"}
except Exception:
return {"status": "error"}
def extract_content(req) -> tuple:
"""
Extrahiert Content aus dem Request.
Gibt (content, input_type, source_url, file_path) zurueck.
"""
input_type = req.form.get("input_type", "text")
source_url = ""
file_path = ""
content = ""
if input_type == "text":
content = req.form.get("post_text", "").strip()
elif input_type == "url":
url = req.form.get("post_url", "").strip()
source_url = url
fetched, err = fetch_text(url)
if err:
return "", input_type, source_url, file_path
content = fetched
elif input_type == "file":
f = req.files.get("post_file")
if f and f.filename:
ext = Path(f.filename).suffix.lower()
if ext in ALLOWED_EXT:
fname = f"upload_{datetime.now().strftime('%Y%m%d_%H%M%S')}{ext}"
fpath = UPLOAD_DIR / fname
f.save(str(fpath))
file_path = str(fpath)
content = req.form.get("post_text", "").strip()
if not content:
content = f"[Bild hochgeladen: {fname}]"
return content, input_type, source_url, file_path
# ---------------------------------------------------------------------------
# Routen
# ---------------------------------------------------------------------------
@app.route("/")
def index():
return redirect("/dashboard")
@app.route("/cockpit")
def cockpit():
days = int(request.args.get("days", 3))
sort = request.args.get("sort", "op_priority")
status_filter = request.args.get("status", None)
tab = request.args.get("tab", "radar")
signals = get_radar_signals(limit=20, days=days, sort=sort,
status_filter=status_filter)
competition = get_radar_signals(limit=20, days=days, sort="relevance",
competition_only=True)
stats = get_radar_score_stats()
posts = get_posts(limit=20)
post_stats = {
"total": len(get_posts(limit=1000)),
"high_score": len([p for p in get_posts(limit=1000) if p["overall_score"] >= 70]),
"missing_cases": len([p for p in get_posts(limit=1000) if p["case_match_status"] == "missing"]),
}
# H-013: Version aus zentraler Quelldatei für die Cockpit-Anzeige
try:
cockpit_version = (BASE_DIR.parent / 'VERSION').read_text().strip()
except Exception:
cockpit_version = "unknown"
return render_template("cockpit.html",
signals=signals,
competition=competition,
stats=post_stats,
score_stats=stats,
posts=posts,
days=days,
sort=sort,
status_filter=status_filter,
tab=tab,
version=cockpit_version)
@app.route("/analyze", methods=["GET", "POST"])
def analyze_post():
if request.method == "GET":
signals = get_radar_signals(limit=5)
return render_template("analyze.html", signals=signals)
content, input_type, source_url, file_path = extract_content(request)
if not content:
return render_template("analyze.html",
signals=get_radar_signals(),
error="Kein Inhalt gefunden.")
result = analyze(content)
case_match = match_case(result.get("problem_type", ""))
post_id = save_post({
"input_type": input_type,
"source_url": source_url,
"raw_text": content[:2000],
"file_path": file_path,
"hook": result.get("hook", ""),
"emotional_trigger": result.get("emotional_trigger", ""),
"problem_type": result.get("problem_type", ""),
"urgency_pattern": result.get("urgency_pattern", ""),
"virality_mechanic": result.get("virality_mechanic", ""),
"cta_type": result.get("cta_type", ""),
"target_audience": result.get("target_audience", ""),
"content_structure": result.get("content_structure", ""),
"language_patterns": result.get("language_patterns", ""),
"why_it_works": result.get("why_it_works", ""),
"mallorca_version": result.get("mallorca_version", ""),
"hook_variants": json.dumps(result.get("hook_variants", [])),
"reel_idea": result.get("reel_idea", ""),
"caption_draft": result.get("caption_draft", ""),
"suggested_cta": result.get("suggested_cta", ""),
"matched_case_id": case_match.get("case", {}).get("case_id", ""),
"case_match_status": case_match.get("status", "unknown"),
"radar_signal_ids": request.form.get("linked_signal_id", ""),
"overall_score": result.get("overall_score", 0),
"virality_score": result.get("virality_score", 0),
"mallorca_fit": result.get("mallorca_fit", 0),
})
return redirect(url_for("result", post_id=post_id))
@app.route("/result/<post_id>")
def result(post_id):
post = get_post(post_id)
if not post:
return redirect(url_for("dashboard"))
try:
post["hook_variants"] = json.loads(post.get("hook_variants") or "[]")
except Exception:
post["hook_variants"] = []
return render_template("result.html", post=post)
# ---------------------------------------------------------------------------
# API
# ---------------------------------------------------------------------------
@app.route("/api/signals")
def api_signals():
days = int(request.args.get("days", 3))
sort = request.args.get("sort", "op_priority")
status_filter = request.args.get("status", None)
limit = int(request.args.get("limit", 20))
competition = request.args.get("competition", "0") == "1"
return jsonify(get_radar_signals(limit=limit, days=days, sort=sort,
status_filter=status_filter,
competition_only=competition))
@app.route("/api/signal/<signal_id>/status", methods=["POST"])
def api_signal_status(signal_id):
"""Setzt den Status eines Signals."""
if not RADAR_DB.exists():
return jsonify({"error": "Radar DB nicht gefunden"}), 404
data = request.get_json(silent=True) or {}
new_status = data.get("status", "")
valid = {"neu", "analysiert", "fall_gefunden", "fall_fehlt",
"content_vorbereitet", "beitrag_in_arbeit", "entwurf_fertig",
"veroeffentlicht", "ignoriert", "beobachten", "spaeter"}
if new_status not in valid:
return jsonify({"error": f"Ungueltiger Status: {new_status}"}), 400
try:
import sqlite3
conn = sqlite3.connect(str(RADAR_DB))
conn.execute("UPDATE signals SET status = ? WHERE signal_id = ?", (new_status, signal_id))
conn.commit()
conn.close()
return jsonify({"ok": True, "status": new_status})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/signal/<signal_id>/pin", methods=["POST"])
def api_signal_pin(signal_id):
"""Toggled Pinned-Status eines Signals."""
if not RADAR_DB.exists():
return jsonify({"error": "Radar DB nicht gefunden"}), 404
try:
import sqlite3
conn = sqlite3.connect(str(RADAR_DB))
row = conn.execute("SELECT COALESCE(pinned,0) FROM signals WHERE signal_id = ?",
(signal_id,)).fetchone()
if not row:
conn.close()
return jsonify({"error": "Signal nicht gefunden"}), 404
new_pinned = 0 if row[0] else 1
conn.execute("UPDATE signals SET pinned = ? WHERE signal_id = ?",
(new_pinned, signal_id))
conn.commit()
conn.close()
return jsonify({"ok": True, "pinned": bool(new_pinned)})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/director-run", methods=["POST"])
def api_director_run():
"""Fuehrt Content-Director-Bewertung auf allen Signalen aus."""
import sys
sys.path.insert(0, "/opt/struktur/social-media-radar")
from content_director import run as director_run
from content_briefing import run as briefing_run
director_run(limit=1000, min_radar_score=25.0)
briefing_run(limit=1000, action_filter="pruefen")
briefing_run(limit=500, action_filter="beobachten")
return jsonify({"ok": True, "message": "Director + Briefing-Run abgeschlossen"})
@app.route("/api/feed-stats")
def api_feed_stats():
"""Feed-Qualitaets-Statistiken fuer Ops-Center."""
import sys
sys.path.insert(0, "/opt/struktur/social-media-radar")
from editorial_rules import get_feed_stats
return jsonify(get_feed_stats())
@app.route("/api/score-stats")
def api_score_stats():
return jsonify(get_radar_score_stats())
@app.route("/api/posts")
def api_posts():
return jsonify(get_posts(limit=50))
@app.route("/export/<post_id>")
def export(post_id):
post = get_post(post_id)
if not post:
return "Nicht gefunden", 404
try:
post["hook_variants"] = json.loads(post.get("hook_variants") or "[]")
except Exception:
post["hook_variants"] = []
return render_template("export.html", post=post)
@app.route("/api/img")
def image_proxy():
"""
Bild-Proxy mit lokalem Cache.
Laedt das Bild einmalig herunter, speichert es in static/img_cache/,
liefert es danach direkt aus -- kein Hotlinking.
"""
import hashlib, urllib.request as ureq
url = request.args.get("u", "").strip()
if not url or not url.startswith("http"):
return "", 404
url_hash = hashlib.md5(url.encode()).hexdigest()[:16]
# Cache-Treffer: zurueckgeben ohne erneuten Download
for ext in [".jpg", ".jpeg", ".png", ".webp", ".gif"]:
cached = IMG_CACHE_DIR / f"{url_hash}{ext}"
if cached.exists():
return send_from_directory(IMG_CACHE_DIR, cached.name,
max_age=86400 * 7)
# Download
try:
req = ureq.Request(url, headers={
"User-Agent": "Mozilla/5.0 (compatible; MallorcaRadar/1.0)",
"Referer": url,
})
with ureq.urlopen(req, timeout=8) as resp:
ctype = resp.headers.get("Content-Type", "image/jpeg")
data = resp.read(600_000) # max 600 KB
ext = ".jpg"
if "png" in ctype: ext = ".png"
elif "webp" in ctype: ext = ".webp"
elif "gif" in ctype: ext = ".gif"
cached = IMG_CACHE_DIR / f"{url_hash}{ext}"
cached.write_bytes(data)
return send_from_directory(IMG_CACHE_DIR, cached.name,
max_age=86400 * 7)
except Exception:
return "", 404
# ---------------------------------------------------------------------------
# Dashboard
# ---------------------------------------------------------------------------
@app.route("/dashboard")
def dashboard():
import sqlite3
from datetime import timedelta
ops = get_ops_stats()
# Fälle ohne Medien
from cases_db import _conn, CASES_DB
con = _conn()
ops["cases_no_media"] = con.execute(
"SELECT COUNT(*) FROM cases c WHERE c.status != 'archiviert'"
" AND (SELECT COUNT(*) FROM case_media m WHERE m.case_id=c.case_id) = 0"
).fetchone()[0]
# Heute veroeffentlicht
ops["content_veroeffentlicht_today"] = con.execute(
"SELECT COUNT(*) FROM content_items WHERE status='veroeffentlicht'"
" AND date(published_at) = date('now')"
).fetchone()[0]
con.close()
# Stale Signals
stale = get_stale_signals(hours=48, limit=10)
ops["stale_signals"] = len(stale)
# Priority Signals (score >= 85)
if RADAR_DB.exists():
rcon = sqlite3.connect(str(RADAR_DB))
ops["priority_signals"] = rcon.execute(
"SELECT COUNT(*) FROM signals WHERE radar_score >= 85"
" AND status NOT IN ('ignoriert','veroeffentlicht')"
).fetchone()[0]
rcon.close()
else:
ops["priority_signals"] = 0
# Read version
version_path = BASE_DIR.parent / 'VERSION'
try:
version = version_path.read_text().strip()
except Exception:
version = "unknown"
return render_template("dashboard.html",
ops=ops,
stats=ops,
top_signals=get_top_priority_signals(limit=6),
recent_cases=get_recent_cases(limit=5),
content_wip=get_content_in_progress(limit=8),
stale_signals=stale,
version=version,
)
# ---------------------------------------------------------------------------
# Pipeline
# ---------------------------------------------------------------------------
@app.route("/pipeline")
def pipeline():
board = get_pipeline_board()
cases = get_cases(limit=100)
return render_template("pipeline.html", board=board, cases=cases)
@app.route("/pipeline/<content_id>")
def pipeline_detail(content_id):
item = get_content_item(content_id)
if not item:
return redirect("/pipeline")
return render_template("pipeline_detail.html", item=item)
# ---------------------------------------------------------------------------
# Publishing
# ---------------------------------------------------------------------------
@app.route("/publishing")
def publishing():
status_filter = request.args.get("status", None)
jobs = get_publishing_jobs(status_filter=status_filter, limit=200)
all_j = get_publishing_jobs(limit=1000)
from collections import Counter
cnt = Counter(j["status"] for j in all_j)
stats = {
"pending": cnt.get("pending", 0),
"sent": cnt.get("sent", 0),
"error": cnt.get("error", 0),
"cancelled": cnt.get("cancelled", 0),
"total": len(all_j),
}
return render_template("publishing.html", jobs=jobs, stats=stats,
current_status=status_filter or "alle")
# ---------------------------------------------------------------------------
# Content API
# ---------------------------------------------------------------------------
@app.route("/api/content/new", methods=["POST"])
def api_content_new():
title = request.form.get("title", "").strip()
platform = request.form.get("platform", "facebook").strip()
content_type = request.form.get("content_type", "post").strip()
case_id = request.form.get("case_id", "").strip()
signal_id = request.form.get("signal_id", "").strip()
hook_text = request.form.get("hook_text", "").strip()
if not title:
return jsonify({"error": "Titel fehlt"}), 400
content_id = create_content_item(
title=title, platform=platform, content_type=content_type,
signal_id=signal_id, case_id=case_id, hook_text=hook_text,
)
return jsonify({"content_id": content_id})
@app.route("/api/content/<content_id>/save", methods=["POST"])
def api_content_save(content_id):
data = request.get_json(silent=True) or {}
try:
update_content_item(content_id, data)
return jsonify({"ok": True})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/content/<content_id>/status", methods=["POST"])
def api_content_status(content_id):
data = request.get_json(silent=True) or {}
new_status = data.get("status", "")
try:
update_content_status(content_id, new_status)
return jsonify({"ok": True, "status": new_status})
except ValueError as e:
return jsonify({"error": str(e)}), 400
@app.route("/api/content/<content_id>/link-media", methods=["POST"])
def api_content_link_media(content_id):
data = request.get_json(silent=True) or {}
media_id = data.get("media_id")
if not media_id:
return jsonify({"error": "media_id fehlt"}), 400
link_media_to_content(content_id, int(media_id))
return jsonify({"ok": True})
@app.route("/api/content/<content_id>/unlink-media", methods=["POST"])
def api_content_unlink_media(content_id):
data = request.get_json(silent=True) or {}
media_id = data.get("media_id")
if not media_id:
return jsonify({"error": "media_id fehlt"}), 400
unlink_media_from_content(content_id, int(media_id))
return jsonify({"ok": True})
@app.route("/api/content/<content_id>/publish-job", methods=["POST"])
def api_content_publish_job(content_id):
item = get_content_item(content_id)
if not item:
return jsonify({"error": "Content nicht gefunden"}), 404
data = request.get_json(silent=True) or {}
scheduled_at = data.get("scheduled_at", "")
create_publishing_job(
content_id=content_id,
platform=item["platform"],
scheduled_at=scheduled_at,
platform_account=item.get("platform_account", ""),
)
return jsonify({"ok": True})
@app.route("/publishing/<int:job_id>")
def publishing_detail(job_id):
job = get_publishing_job(job_id)
if not job:
return redirect("/publishing")
# Signal-Daten aus Radar-DB
signal_info = None
if job.get("signal_id") and RADAR_DB.exists():
import sqlite3
try:
rc = sqlite3.connect(str(RADAR_DB))
rc.row_factory = sqlite3.Row
row = rc.execute(
"SELECT title, topic_de, radar_score, operational_priority, status, platform"
" FROM signals WHERE signal_id=?",
(job["signal_id"],)
).fetchone()
if row:
signal_info = dict(row)
rc.close()
except Exception:
pass
return render_template("publishing_detail.html",
job=job, signal_info=signal_info)
@app.route("/api/publishing/<int:job_id>/mark-published", methods=["POST"])
def api_publishing_mark_published(job_id):
data = request.get_json(silent=True) or {}
external_url = data.get("external_url", "").strip()
try:
mark_publishing_job_published(job_id, external_url)
return jsonify({"ok": True})
except ValueError as e:
return jsonify({"error": str(e)}), 400
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/publishing/<int:job_id>/set-ready", methods=["POST"])
def api_publishing_set_ready(job_id):
"""Setzt Content-Status auf bereit_zum_posten."""
job = get_publishing_job(job_id)
if not job or not job.get("content_id"):
return jsonify({"error": "Job nicht gefunden"}), 404
try:
update_content_status(job["content_id"], "bereit_zum_posten")
return jsonify({"ok": True})
except ValueError as e:
return jsonify({"error": str(e)}), 400
@app.route("/api/publishing/pending")
def api_publishing_pending():
"""n8n-Endpunkt: gibt ausstehende Jobs als JSON zurück (inkl. Content-Text + Media-URLs)."""
jobs = get_publishing_jobs(status_filter="pending", limit=50)
base_url = request.host_url.rstrip("/")
enriched = []
for job in jobs:
item = get_content_item(job["content_id"]) if job.get("content_id") else None
if item:
job["hook_text"] = item.get("hook_text", "")
job["body_text"] = item.get("body_text", "")
job["cta_text"] = item.get("cta_text", "")
job["notes"] = item.get("notes", "")
job["media_urls"] = [
base_url + "/static/media/cases/" + m["case_id"] + "/" + m["filename"]
for m in item.get("media", [])
if m.get("filename") and m.get("case_id")
]
else:
job["hook_text"] = ""
job["body_text"] = ""
job["cta_text"] = ""
job["notes"] = ""
job["media_urls"] = []
enriched.append(job)
return jsonify(enriched)
# ---------------------------------------------------------------------------
# Fälle-Routen
# ---------------------------------------------------------------------------
@app.route("/cases")
def cases_list():
status_filter = request.args.get("status", None)
extra_filter = request.args.get("filter", None)
if extra_filter == "no_media":
status_filter = "no_media"
cases = get_cases(status_filter=status_filter)
stats = get_ops_stats()
return render_template("cases.html", cases=cases, stats=stats,
current_status=status_filter or "alle")
@app.route("/cases/new", methods=["POST"])
def cases_new():
title = request.form.get("title", "").strip()
case_type = request.form.get("case_type", "sonstiges").strip()
location_area = request.form.get("location_area", "").strip()
problem_desc = request.form.get("problem_desc", "").strip()
if not title:
stats = get_ops_stats()
return render_template("cases.html", cases=get_cases(), stats=stats,
error="Titel ist erforderlich.", current_status="alle")
case_id = create_case(title, case_type, location_area, problem_desc)
return redirect(f"/cases/{case_id}")
@app.route("/cases/<case_id>")
def case_detail(case_id):
case = get_case(case_id)
if not case:
return redirect("/cases")
linked_signals = get_signals_for_case(case_id)
return render_template("case_detail.html", case=case, linked_signals=linked_signals)
@app.route("/cases/<case_id>/edit", methods=["POST"])
def case_edit(case_id):
for field in ["title", "location_area", "problem_desc"]:
val = request.form.get(field, "").strip()
if val:
update_case_field(case_id, field, val)
return redirect(f"/cases/{case_id}")
@app.route("/cases/<case_id>/media", methods=["POST"])
def case_media_upload(case_id):
import hashlib
case = get_case(case_id)
if not case:
return "Fall nicht gefunden", 404
f = request.files.get("media_file")
if not f or not f.filename:
return "Keine Datei", 400
ext = Path(f.filename).suffix.lower()
if ext not in {".jpg", ".jpeg", ".png", ".webp", ".gif", ".mp4", ".mov"}:
return "Dateityp nicht erlaubt", 400
ts = datetime.now().strftime("%Y%m%d_%H%M%S")
h = hashlib.md5(f.filename.encode()).hexdigest()[:6]
fname = f"{ts}_{h}{ext}"
dest_dir = MEDIA_DIR / case_id
dest_dir.mkdir(parents=True, exist_ok=True)
dest = dest_dir / fname
f.save(str(dest))
file_type = "video" if ext in {".mp4", ".mov"} else "foto"
caption = request.form.get("caption", "").strip()
stored = str(dest)
add_media(case_id, fname, file_type, stored, caption)
return "", 200
@app.route("/cases/<case_id>/media/<int:media_id>/delete", methods=["POST"])
def case_media_delete(case_id, media_id):
delete_media(media_id, case_id)
return "", 200
# ---------------------------------------------------------------------------
# Cases API
# ---------------------------------------------------------------------------
@app.route("/api/cases/<case_id>/status", methods=["POST"])
def api_case_status(case_id):
data = request.get_json(silent=True) or {}
new_status = data.get("status", "")
try:
update_case_status(case_id, new_status)
return jsonify({"ok": True, "status": new_status})
except ValueError as e:
return jsonify({"error": str(e)}), 400
@app.route("/api/signal/<signal_id>/link-case", methods=["POST"])
def api_link_case(signal_id):
data = request.get_json(silent=True) or {}
case_id = data.get("case_id", "").strip()
note = data.get("note", "").strip()
if not case_id:
return jsonify({"error": "case_id fehlt"}), 400
try:
link_signal_to_case(signal_id, case_id, note)
# Signal-Status in Radar-DB auf fall_gefunden setzen
if RADAR_DB.exists():
import sqlite3
conn = sqlite3.connect(str(RADAR_DB))
conn.execute("UPDATE signals SET status=? WHERE signal_id=?",
("fall_gefunden", signal_id))
conn.commit()
conn.close()
return jsonify({"ok": True})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/cases/<case_id>/unlink-signal", methods=["POST"])
def api_unlink_signal(case_id):
data = request.get_json(silent=True) or {}
signal_id = data.get("signal_id", "").strip()
if not signal_id:
return jsonify({"error": "signal_id fehlt"}), 400
unlink_signal(signal_id, case_id)
return jsonify({"ok": True})
@app.route("/api/cases")
def api_cases():
status = request.args.get("status", None)
return jsonify(get_cases(status_filter=status, limit=100))
# ---------------------------------------------------------------------------
# Content Queue -- Radar-generierte Post-Entwuerfe
# ---------------------------------------------------------------------------
import sqlite3 as _sqlite3
def _signals_conn():
conn = _sqlite3.connect(str(RADAR_DB))
conn.row_factory = _sqlite3.Row
return conn
@app.route("/content-queue")
def content_queue_page():
"""Standalone Content-Queue Seite (inline HTML)."""
return _content_queue_html()
@app.route("/api/content-drafts")
def api_content_drafts():
status = request.args.get("status", "draft")
limit = min(int(request.args.get("limit", 50)), 200)
conn = _signals_conn()
if status:
rows = conn.execute(
"SELECT * FROM content_drafts WHERE status=? "
"ORDER BY radar_score DESC, created_at DESC LIMIT ?",
(status, limit)
).fetchall()
else:
rows = conn.execute(
"SELECT * FROM content_drafts "
"ORDER BY radar_score DESC, created_at DESC LIMIT ?",
(limit,)
).fetchall()
counts = {}
for s in ["draft", "approved", "rejected", "published"]:
row = conn.execute(
"SELECT COUNT(*) FROM content_drafts WHERE status=?", (s,)
).fetchone()
counts[s] = row[0] if row else 0
conn.close()
return jsonify({"drafts": [dict(r) for r in rows], "total": len(rows), "counts": counts})
@app.route("/api/content-draft/<draft_id>/approve", methods=["POST"])
def api_draft_approve(draft_id):
conn = _signals_conn()
conn.execute(
"UPDATE content_drafts SET status='approved', reviewed_at=? WHERE draft_id=?",
(datetime.now(timezone.utc).isoformat(), draft_id)
)
conn.commit()
conn.close()
return jsonify({"ok": True})
@app.route("/api/content-draft/<draft_id>/reject", methods=["POST"])
def api_draft_reject(draft_id):
conn = _signals_conn()
conn.execute(
"UPDATE content_drafts SET status='rejected', reviewed_at=? WHERE draft_id=?",
(datetime.now(timezone.utc).isoformat(), draft_id)
)
conn.commit()
conn.close()
return jsonify({"ok": True})
@app.route("/api/content-draft/<draft_id>/published", methods=["POST"])
def api_draft_published(draft_id):
conn = _signals_conn()
conn.execute(
"UPDATE content_drafts SET status='published', published_at=? WHERE draft_id=?",
(datetime.now(timezone.utc).isoformat(), draft_id)
)
conn.commit()
conn.close()
return jsonify({"ok": True})
@app.route("/api/content-draft/<draft_id>/update", methods=["POST"])
def api_draft_update(draft_id):
data = request.get_json(force=True)
post_text = (data.get("post_text") or "").strip()
if not post_text:
return jsonify({"error": "post_text required"}), 400
conn = _signals_conn()
conn.execute(
"UPDATE content_drafts SET post_text=? WHERE draft_id=?",
(post_text, draft_id)
)
conn.commit()
conn.close()
return jsonify({"ok": True})
def _content_queue_html():
return """<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Content Queue</title>
<style>
*{margin:0;padding:0;box-sizing:border-box}
body{font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;
background:linear-gradient(135deg,#667eea 0%,#764ba2 100%);min-height:100vh;padding:20px}
.container{max-width:900px;margin:0 auto}
.header{background:white;border-radius:12px;padding:24px 32px;margin-bottom:16px;
box-shadow:0 4px 20px rgba(0,0,0,.15)}
.header h1{font-size:22px;color:#333}
.header p{color:#666;font-size:13px;margin-top:4px}
.nav-link{display:inline-block;margin-top:10px;color:#667eea;text-decoration:none;font-size:13px}
.stats{display:flex;gap:10px;margin-bottom:14px;flex-wrap:wrap}
.stat{background:rgba(255,255,255,.9);border-radius:8px;padding:8px 14px;font-size:12px;font-weight:600}
.stat span{display:block;font-size:18px}
select{padding:8px 12px;border-radius:8px;border:1px solid #ddd;font-size:13px;margin-bottom:14px}
.card{background:white;border-radius:12px;padding:18px;margin-bottom:10px;
box-shadow:0 2px 12px rgba(0,0,0,.1)}
.meta{font-size:11px;color:#999;display:flex;gap:10px;flex-wrap:wrap;margin-bottom:6px}
.topic{font-size:13px;color:#777;font-style:italic;margin-bottom:8px}
.hook{font-size:13px;color:#667eea;font-weight:600;margin-bottom:6px}
.post-text{font-size:14px;color:#222;line-height:1.5;padding:10px;background:#f9fafb;
border-radius:6px;border-left:3px solid #667eea;margin-bottom:8px;min-height:50px}
.post-text[contenteditable]{border-left-color:#10b981;outline:none}
.cc{font-size:11px;color:#888;margin-bottom:8px}
.actions{display:flex;gap:6px;flex-wrap:wrap}
.btn{padding:7px 14px;border-radius:7px;border:none;cursor:pointer;font-size:12px;font-weight:600;transition:opacity .2s}
.btn:hover{opacity:.85}
.btn-ok{background:#10b981;color:white}
.btn-no{background:#ef4444;color:white}
.btn-edit{background:#667eea;color:white}
.btn-save{background:#8b5cf6;color:white;display:none}
.btn-pub{background:#f59e0b;color:white}
.btn-copy{background:#e5e7eb;color:#333}
.badge{display:inline-block;padding:2px 7px;border-radius:10px;font-size:10px;font-weight:700}
.b-draft{background:#e5e7eb;color:#555}
.b-approved{background:#d1fae5;color:#065f46}
.b-rejected{background:#fee2e2;color:#991b1b}
.b-published{background:#ddd6fe;color:#4c1d95}
.empty{text-align:center;padding:40px;color:white;font-size:15px}
.notice{background:#fef3c7;border:1px solid #f59e0b;border-radius:8px;padding:12px;
margin-bottom:14px;font-size:13px;color:#92400e}
</style>
</head>
<body>
<div class="container">
<div class="header">
<h1>Content Queue</h1>
<p>Radar-generierte Post-Entwuerfe fuer Mallorca AirServices</p>
<a href="/cockpit" class="nav-link">← Zurueck zum Cockpit</a>
</div>
<div class="stats" id="stats"></div>
<select id="filter" onchange="load()">
<option value="draft">Entwuerfe</option>
<option value="approved">Freigegeben</option>
<option value="rejected">Abgelehnt</option>
<option value="published">Veroeffentlicht</option>
<option value="">Alle</option>
</select>
<div id="list"><div class="empty">Laden...</div></div>
</div>
<script>
async function load(){
const s=document.getElementById('filter').value;
const r=await fetch('/api/content-drafts?status='+encodeURIComponent(s)+'&limit=50');
const d=await r.json();
const counts=d.counts||{};
document.getElementById('stats').innerHTML=[
['Entwuerfe',counts.draft||0,'#667eea'],
['Freigegeben',counts.approved||0,'#10b981'],
['Veroeffentlicht',counts.published||0,'#8b5cf6'],
['Abgelehnt',counts.rejected||0,'#ef4444']
].map(([l,n,c])=>`<div class="stat"><span style="color:${c}">${n}</span>${l}</div>`).join('');
const el=document.getElementById('list');
if(!d.drafts||!d.drafts.length){
el.innerHTML=`<div class="empty"><p>Keine Eintraege.</p><p style="margin-top:6px;font-size:12px">python3 radar_content_generator.py ausfuehren um Entwuerfe zu erstellen.<br>Dazu benoetigt einen gueltigen API-Key in settings.json (contentGeneratorKey).</p></div>`;
return;
}
el.innerHTML=d.drafts.map(x=>{
const cl=x.post_text?x.post_text.length:0;
const cc=cl>200?'⚠ zu lang':cl<80?'⚠ zu kurz':'✓';
const bc='b-'+x.status;
const dt=x.created_at?new Date(x.created_at).toLocaleString('de-DE'):'-';
return `<div class="card" id="c-${x.draft_id}">
<div class="meta"><span>${x.radar_score||0} pts</span><span>${x.signal_cat||'-'}</span><span>${x.ai_model||'-'}</span><span>${dt}</span><span class="badge ${bc}">${x.status}</span></div>
<div class="topic">Signal: ${esc(x.signal_topic||'')}</div>
${x.hook?`<div class="hook">Hook: ${esc(x.hook)}</div>`:''}
<div class="post-text" id="t-${x.draft_id}">${esc(x.post_text||'')}</div>
<div class="cc" id="cc-${x.draft_id}">${cl} Zeichen ${cc}</div>
<div class="actions">
${x.status==='draft'?`<button class="btn btn-ok" onclick="act('${x.draft_id}','approve')">✓ Freigeben</button><button class="btn btn-no" onclick="act('${x.draft_id}','reject')">✗ Ablehnen</button>`:''}
${x.status==='approved'?`<button class="btn btn-pub" onclick="act('${x.draft_id}','published')">📤 Veroeffentlicht</button>`:''}
<button class="btn btn-edit" onclick="edit('${x.draft_id}')">✎ Bearbeiten</button>
<button class="btn btn-save" id="sv-${x.draft_id}" onclick="save('${x.draft_id}')">💾 Speichern</button>
<button class="btn btn-copy" onclick="copy('${x.draft_id}')">📋 Kopieren</button>
</div>
</div>`}).join('');
}
function esc(t){return(t||'').replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>')}
async function act(id,action){await fetch('/api/content-draft/'+id+'/'+action,{method:'POST'});load()}
function edit(id){const e=document.getElementById('t-'+id);e.contentEditable='true';e.focus();document.getElementById('sv-'+id).style.display='inline-block'}
async function save(id){const e=document.getElementById('t-'+id);const t=e.innerText.trim();await fetch('/api/content-draft/'+id+'/update',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({post_text:t})});e.contentEditable='false';document.getElementById('sv-'+id).style.display='none';const cc=document.getElementById('cc-'+id);cc.textContent=t.length+' Zeichen '+(t.length>200?'zu lang':t.length<80?'zu kurz':'OK')}
function copy(id){const e=document.getElementById('t-'+id);navigator.clipboard.writeText(e.innerText.trim()).catch(()=>{const r=document.createRange();r.selectNode(e);window.getSelection().removeAllRanges();window.getSelection().addRange(r);document.execCommand('copy');window.getSelection().removeAllRanges()})}
load();
</script>
</body>
</html>"""
# ---------------------------------------------------------------------------
# Start
# ---------------------------------------------------------------------------
# ── SMA Posting Package ───────────────────────────────────────────────────
from posting_package import SIGNAL_ID_PATTERN as _PACKAGE_ID_PATTERN
from posting_package import generate_package as _generate_package
_PACKAGE_RENDER_SLOTS = threading.BoundedSemaphore(1)
_PACKAGE_RATE_LOCK = threading.Lock()
_PACKAGE_REQUESTS = defaultdict(deque)
_PACKAGE_RATE_WINDOW_SECONDS = 60.0
_PACKAGE_RATE_LIMIT = 6
_PACKAGE_MAX_CLIENTS = 2048
def _allow_render_request(client: str) -> bool:
now = time.monotonic()
with _PACKAGE_RATE_LOCK:
for known_client, bucket in list(_PACKAGE_REQUESTS.items()):
while bucket and bucket[0] <= now - _PACKAGE_RATE_WINDOW_SECONDS:
bucket.popleft()
if not bucket:
del _PACKAGE_REQUESTS[known_client]
if client not in _PACKAGE_REQUESTS and len(_PACKAGE_REQUESTS) >= _PACKAGE_MAX_CLIENTS:
return False
requests_for_client = _PACKAGE_REQUESTS[client]
while requests_for_client and requests_for_client[0] <= now - _PACKAGE_RATE_WINDOW_SECONDS:
requests_for_client.popleft()
if len(requests_for_client) >= _PACKAGE_RATE_LIMIT:
return False
requests_for_client.append(now)
return True
PACKAGES_DIR = Path(os.environ.get(
"SMA_PACKAGES_DIR", str(BASE_DIR / "generated_packages")
)).resolve()
_PACKAGE_DOWNLOAD_FILES = {
"facebook.txt", "linkedin.txt", "visual_square.png", "visual_45.png", "metadata.json"
}
@app.route("/api/package/<signal_id>", methods=["POST"])
def api_generate_package(signal_id):
client = request.remote_addr or "unknown"
if not _allow_render_request(client):
return jsonify({"success": False, "reason": "Zu viele Paketanfragen — bitte kurz warten."}), 429
if not _PACKAGE_RENDER_SLOTS.acquire(blocking=False):
return jsonify({"success": False, "reason": "Eine Paketerzeugung läuft bereits."}), 429
try:
result = _generate_package(signal_id)
return jsonify(result)
finally:
_PACKAGE_RENDER_SLOTS.release()
@app.route("/packages/<sid_short>/<path:filename>")
def serve_package_file(sid_short, filename):
if not _PACKAGE_ID_PATTERN.fullmatch(sid_short):
abort(404)
if filename not in _PACKAGE_DOWNLOAD_FILES:
abort(404)
pkg_dir = (PACKAGES_DIR / sid_short).resolve(strict=False)
if pkg_dir.parent != PACKAGES_DIR:
abort(404)
path = pkg_dir / filename
try:
descriptor = os.open(path, os.O_RDONLY | getattr(os, "O_NOFOLLOW", 0))
info = os.fstat(descriptor)
if not stat.S_ISREG(info.st_mode):
os.close(descriptor)
abort(404)
except OSError:
abort(404)
file_object = os.fdopen(descriptor, "rb")
response = send_file(file_object, download_name=filename, conditional=False, etag=False)
response.call_on_close(file_object.close)
return response
# ── Ende SMA Posting Package ──────────────────────────────────────────────
# ── SMA Caption Engine ────────────────────────────────────────────────────
from caption_render import render_by_id as _render_caption_by_id
@app.route("/api/caption/<signal_id>")
def api_generate_caption(signal_id):
result = _render_caption_by_id(signal_id)
return jsonify({
"success": result["success"],
"facebook": result.get("facebook"),
"linkedin": result.get("linkedin"),
"reason": result.get("reason"),
"char_count": result.get("char_count", {}),
})
# ── Ende SMA Caption Engine ───────────────────────────────────────────────
# ── SMA Visual Engine ─────────────────────────────────────────────────────
import sys as _sys
_sys.path.insert(0, os.environ.get("SMA_AGENT_DIR", str(BASE_DIR)))
_sys.path.insert(0, os.environ.get("SMA_RADAR_DIR", "/opt/struktur/social-media-radar"))
from visual_render import render_by_id as _render_by_id
from flask import send_from_directory as _send_from_dir
from pathlib import Path as _Path
VISUALS_DIR = _Path(os.environ.get(
"SMA_VISUALS_DIR", str(BASE_DIR / "generated_visuals")
)).resolve()
@app.route("/api/visual/<signal_id>")
def api_generate_visual(signal_id):
fmt = request.args.get("fmt", "1x1")
client = request.remote_addr or "unknown"
if not _allow_render_request(client):
return jsonify({"success": False, "path": None, "reason": "Zu viele Bildanfragen — bitte kurz warten."}), 429
if not _PACKAGE_RENDER_SLOTS.acquire(blocking=False):
return jsonify({"success": False, "path": None, "reason": "Eine Bilderzeugung läuft bereits."}), 429
try:
result = _render_by_id(signal_id, fmt)
finally:
_PACKAGE_RENDER_SLOTS.release()
if result["success"]:
fname = _Path(result["path"]).name
return jsonify({
"success": True,
"path": f"/visuals/{fname}",
"reason": result["reason"],
})
return jsonify({"success": False, "path": None, "reason": result["reason"]})
@app.route("/visuals/<path:filename>")
def serve_visual(filename):
return _send_from_dir(str(VISUALS_DIR), filename)
# ── Ende SMA Visual Engine ────────────────────────────────────────────────
# -- SMA Publish Workflow ---------------------------------------------------
import uuid as _uuid
from datetime import datetime as _dt, timezone as _tz, timedelta as _td
RADAR_DB_PW = RADAR_DB_PATH
_PW_VALID = {"geprueft", "paket_generiert", "bereit_zum_posten", "gepostet", "verworfen"}
def _pw_conn():
import sqlite3 as _sq3
c = _sq3.connect(str(RADAR_DB_PW))
c.row_factory = _sq3.Row
return c
def _topic_norm(t):
import re as _re
t = (t or "").lower().strip()
t = _re.sub(r"[^\w\s]", " ", t)
t = _re.sub(r"\s+", " ", t).strip()
return t[:60]
@app.route("/api/signal/<signal_id>/publish-status", methods=["POST"])
def api_publish_status(signal_id):
data = request.get_json(silent=True) or {}
new_ps = (data.get("publish_status") or "").strip().lower()
if new_ps not in _PW_VALID:
return jsonify({"error": "Ungultiger publish_status: " + new_ps}), 400
now = _dt.now(_tz.utc).isoformat()
try:
conn = _pw_conn()
conn.execute(
"UPDATE signals SET publish_status=?, publish_status_at=? WHERE signal_id=?",
(new_ps, now, signal_id)
)
conn.commit()
conn.close()
return jsonify({"ok": True, "publish_status": new_ps})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/signal/<signal_id>/mark-posted", methods=["POST"])
def api_mark_posted(signal_id):
data = request.get_json(silent=True) or {}
now = _dt.now(_tz.utc).isoformat()
log_id = str(_uuid.uuid4())
try:
conn = _pw_conn()
sig = conn.execute(
"SELECT topic, topic_de FROM signals WHERE signal_id=?", (signal_id,)
).fetchone()
sig = dict(sig) if sig else {}
topic_raw = sig.get("topic_de") or sig.get("topic") or ""
conn.execute(
"UPDATE signals SET publish_status='gepostet', publish_status_at=? WHERE signal_id=?",
(now, signal_id)
)
conn.execute(
"INSERT INTO posting_log"
" (log_id, signal_id, posted_at, platform, caption_used, visual_path, topic_normalize, operator_note)"
" VALUES (?,?,?,?,?,?,?,?)",
(log_id, signal_id, now,
data.get("platform", ""),
data.get("caption_used", ""),
data.get("visual_path", ""),
_topic_norm(topic_raw),
data.get("operator_note", ""))
)
conn.commit()
conn.close()
return jsonify({"ok": True, "log_id": log_id, "posted_at": now})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/publish/queue")
def api_publish_queue():
try:
conn = _pw_conn()
rows = conn.execute(
"SELECT signal_id, topic, topic_de, signal_category, radar_score,"
" source_name, publish_status, publish_status_at,"
" briefing_freigabe, briefing_fb_fit, briefing_li_fit,"
" cached_image_path, image_url, image_found,"
" platform_facebook, platform_linkedin, expires_at"
" FROM signals WHERE publish_status='bereit_zum_posten'"
" ORDER BY publish_status_at DESC"
).fetchall()
conn.close()
out = []
for r in rows:
d = dict(r)
if d.get("cached_image_path"):
d["img"] = "/static/img_cache/" + d["cached_image_path"]
elif d.get("image_found") and d.get("image_url"):
from urllib.parse import quote as _q
d["img"] = "/api/img?u=" + _q(d["image_url"])
else:
d["img"] = ""
out.append(d)
return jsonify({"ok": True, "items": out, "count": len(out)})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/publish/archive")
def api_publish_archive():
limit = int(request.args.get("limit", 50))
try:
conn = _pw_conn()
rows = conn.execute(
"SELECT pl.log_id, pl.signal_id, pl.posted_at, pl.platform,"
" pl.caption_used, pl.visual_path, pl.topic_normalize, pl.operator_note,"
" s.topic, s.topic_de, s.signal_category, s.radar_score, s.source_name,"
" s.cached_image_path, s.image_url, s.image_found"
" FROM posting_log pl"
" LEFT JOIN signals s ON s.signal_id = pl.signal_id"
" ORDER BY pl.posted_at DESC LIMIT ?",
(limit,)
).fetchall()
conn.close()
out = []
for r in rows:
d = dict(r)
if d.get("cached_image_path"):
d["img"] = "/static/img_cache/" + d["cached_image_path"]
elif d.get("image_found") and d.get("image_url"):
from urllib.parse import quote as _q
d["img"] = "/api/img?u=" + _q(d["image_url"])
else:
d["img"] = ""
out.append(d)
return jsonify({"ok": True, "items": out, "count": len(out)})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/signal/<signal_id>/dup-check")
def api_dup_check(signal_id):
try:
conn = _pw_conn()
sig = conn.execute(
"SELECT topic, topic_de FROM signals WHERE signal_id=?", (signal_id,)
).fetchone()
if not sig:
conn.close()
return jsonify({"ok": True, "dup": False, "reason": "Signal nicht gefunden"})
sig = dict(sig)
topic_raw = sig.get("topic_de") or sig.get("topic") or ""
tn = _topic_norm(topic_raw)
if not tn:
conn.close()
return jsonify({"ok": True, "dup": False, "reason": "Kein Topic"})
cutoff = (_dt.now(_tz.utc) - _td(hours=72)).isoformat()
match = conn.execute(
"SELECT pl.signal_id, pl.posted_at, pl.platform"
" FROM posting_log pl"
" WHERE pl.topic_normalize=? AND pl.posted_at>=?"
" ORDER BY pl.posted_at DESC LIMIT 1",
(tn, cutoff)
).fetchone()
conn.close()
if match:
m = dict(match)
pa = m["posted_at"].replace("Z", "+00:00")
hours_ago = round(
(_dt.now(_tz.utc) - _dt.fromisoformat(pa)).total_seconds() / 3600, 1
)
return jsonify({
"ok": True, "dup": True,
"matched_signal_id": m["signal_id"],
"posted_at": m["posted_at"],
"platform": m["platform"],
"hours_ago": hours_ago,
})
return jsonify({"ok": True, "dup": False})
except Exception as e:
return jsonify({"error": str(e)}), 500
# -- SMA Pilot Monitoring ---------------------------------------------------
@app.route("/api/monitoring/event", methods=["POST"])
def api_monitoring_event():
"""Leichtgewichtiges Event-Log aus Cockpit-JS (fire-and-forget)."""
try:
data = request.get_json(silent=True) or {}
event_type = data.get("event_type", "unknown")
signal_id = data.get("signal_id")
session_id = data.get("session_id")
extra = data.get("extra")
import json as _json
extra_str = _json.dumps(extra) if extra and not isinstance(extra, str) else extra
with _pw_conn() as conn:
conn.execute(
"INSERT INTO operator_events (event_type, signal_id, session_id, extra) VALUES (?,?,?,?)",
(event_type, signal_id, session_id, extra_str)
)
return jsonify({"ok": True})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/monitoring/stats")
def api_monitoring_stats():
"""KPI-Aggregat fuer Pilot-Monitoring."""
try:
days = int(request.args.get("days", 30))
conn = _pw_conn()
cur = conn.cursor()
# -- Posting-Metriken ------------------------------------------------
cur.execute("""
SELECT
COUNT(*) FILTER (WHERE publish_status = 'paket_generiert') AS pakete,
COUNT(*) FILTER (WHERE publish_status IN ('bereit_zum_posten','gepostet')) AS freigegeben,
COUNT(*) FILTER (WHERE publish_status = 'gepostet') AS gepostet,
COUNT(*) FILTER (WHERE publish_status = 'verworfen') AS verworfen
FROM signals
WHERE datetime(created_at) >= datetime('now', '-' || ? || ' days')
""", (days,))
pm = dict(cur.fetchone())
cur.execute("""
SELECT signal_category, COUNT(*) as cnt
FROM signals
WHERE publish_status = 'gepostet'
AND datetime(created_at) >= datetime('now', '-' || ? || ' days')
GROUP BY signal_category ORDER BY cnt DESC
""", (days,))
pm["kategorien_gepostet"] = [dict(r) for r in cur.fetchall()]
# durchschnittliche Bearbeitungszeit (paket_generiert -> gepostet)
# approximiert via publish_status_at fuer paket_generiert Signale
cur.execute("""
SELECT AVG((julianday(publish_status_at) - julianday(created_at)) * 24) AS avg_h
FROM signals
WHERE publish_status IN ('gepostet','bereit_zum_posten')
AND publish_status_at IS NOT NULL
AND datetime(created_at) >= datetime('now', '-' || ? || ' days')
""", (days,))
row = cur.fetchone()
pm["avg_bearbeitungszeit_h"] = round(row[0], 1) if row and row[0] else None
# -- Operator-Metriken -----------------------------------------------
cur.execute("""
SELECT event_type, COUNT(*) as cnt
FROM operator_events
WHERE datetime(ts) >= datetime('now', '-' || ? || ' days')
GROUP BY event_type ORDER BY cnt DESC
""", (days,))
events = {r["event_type"]: r["cnt"] for r in cur.fetchall()}
cur.execute("""
SELECT COUNT(DISTINCT signal_id) as abgebrochen
FROM operator_events
WHERE event_type = 'pkg_started'
AND datetime(ts) >= datetime('now', '-' || ? || ' days')
AND signal_id NOT IN (
SELECT DISTINCT signal_id FROM operator_events
WHERE event_type = 'pkg_completed'
AND datetime(ts) >= datetime('now', '-' || ? || ' days')
)
""", (days, days))
abgebrochen = cur.fetchone()["abgebrochen"]
cur.execute("""
SELECT signal_category, COUNT(*) as cnt
FROM signals
WHERE publish_status = 'verworfen'
AND datetime(created_at) >= datetime('now', '-' || ? || ' days')
GROUP BY signal_category ORDER BY cnt DESC LIMIT 10
""", (days,))
verworf_cats = [dict(r) for r in cur.fetchall()]
cur.execute("""
SELECT briefing_freigabe, COUNT(*) as cnt
FROM signals
WHERE publish_status = 'verworfen'
AND datetime(created_at) >= datetime('now', '-' || ? || ' days')
GROUP BY briefing_freigabe ORDER BY cnt DESC
""", (days,))
block_gruende = [dict(r) for r in cur.fetchall()]
om = {
"events": events,
"pkg_abbruchrate": abgebrochen,
"verworfene_kategorien": verworf_cats,
"blockierungsgruende": block_gruende,
}
# -- Qualitaets-Metriken ---------------------------------------------
cur.execute("""
SELECT pl.signal_id, pl.caption_used, s.radar_score, s.signal_category,
pl.platform, pl.posted_at, pl.operator_note
FROM posting_log pl
JOIN signals s ON s.signal_id = pl.signal_id
WHERE datetime(pl.posted_at) >= datetime('now', '-' || ? || ' days')
ORDER BY s.radar_score DESC
""", (days,))
postings = [dict(r) for r in cur.fetchall()]
qm = {
"postings_gesamt": len(postings),
"beste_captions": postings[:3],
"schlechteste_captions": postings[-3:] if len(postings) > 3 else [],
"mit_operator_note": [p for p in postings if p.get("operator_note")],
}
# -- Plattform-Metriken ----------------------------------------------
cur.execute("""
SELECT platform,
SUM(reach) as reach, SUM(likes) as likes, SUM(comments) as comments,
SUM(reactions) as reactions, SUM(clicks) as clicks, SUM(shares) as shares,
COUNT(*) as eintraege
FROM platform_metrics
WHERE datetime(created_at) >= datetime('now', '-' || ? || ' days')
GROUP BY platform
""", (days,))
plm = [dict(r) for r in cur.fetchall()]
conn.close()
return jsonify({
"days": days,
"posting_metriken": pm,
"operator_metriken": om,
"qualitaets_metriken": qm,
"plattform_metriken": plm,
})
except Exception as e:
import traceback
return jsonify({"error": str(e), "trace": traceback.format_exc()}), 500
@app.route("/api/platform-metrics/<signal_id>", methods=["POST"])
def api_platform_metrics_save(signal_id):
"""Manuelle Plattform-Metriken speichern (nach dem echten Posten)."""
try:
data = request.get_json(silent=True) or {}
platform = data.get("platform", "facebook")
posted_date = data.get("posted_date")
reach = int(data.get("reach", 0))
likes = int(data.get("likes", 0))
comments = int(data.get("comments", 0))
reactions = int(data.get("reactions", 0))
clicks = int(data.get("clicks", 0))
shares = int(data.get("shares", 0))
notes = data.get("notes", "")
with _pw_conn() as conn:
# Upsert: wenn Eintrag fuer signal+platform existiert, updaten
existing = conn.execute(
"SELECT id FROM platform_metrics WHERE signal_id=? AND platform=?",
(signal_id, platform)
).fetchone()
if existing:
conn.execute("""
UPDATE platform_metrics
SET reach=?, likes=?, comments=?, reactions=?, clicks=?, shares=?,
notes=?, posted_date=?,
updated_at=strftime('%Y-%m-%dT%H:%M:%SZ','now')
WHERE signal_id=? AND platform=?
""", (reach, likes, comments, reactions, clicks, shares,
notes, posted_date, signal_id, platform))
else:
conn.execute("""
INSERT INTO platform_metrics
(signal_id, platform, posted_date, reach, likes, comments,
reactions, clicks, shares, notes)
VALUES (?,?,?,?,?,?,?,?,?,?)
""", (signal_id, platform, posted_date, reach, likes, comments,
reactions, clicks, shares, notes))
return jsonify({"ok": True})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/api/platform-metrics/<signal_id>")
def api_platform_metrics_get(signal_id):
try:
conn = _pw_conn()
rows = conn.execute(
"SELECT * FROM platform_metrics WHERE signal_id=? ORDER BY platform",
(signal_id,)
).fetchall()
conn.close()
return jsonify({"ok": True, "metrics": [dict(r) for r in rows]})
except Exception as e:
return jsonify({"error": str(e)}), 500
@app.route("/monitoring")
def monitoring_dashboard():
return render_template("monitoring.html")
# -- Ende SMA Publish Workflow ----------------------------------------------
if __name__ == "__main__":
init_db()
init_cases_db()
print(f"[SMA] Social Media Agent startet auf {APP_HOST}:{APP_PORT}")
app.run(host=APP_HOST, port=APP_PORT, debug=False)