73 lines
2.4 KiB
Python
73 lines
2.4 KiB
Python
|
from pathlib import Path
|
||
|
|
|
||
|
|
from fastapi import APIRouter, Request
|
||
|
|
from fastapi.templating import Jinja2Templates
|
||
|
|
|
||
|
|
from app.db import fetch_all, fetch_one
|
||
|
|
from app.services.marketing import evaluate_agent_rules
|
||
|
|
|
||
|
|
router = APIRouter(prefix="/marketing", tags=["marketing"])
|
||
|
|
|
||
|
|
BASE_DIR = Path(__file__).resolve().parent.parent.parent
|
||
|
|
templates = Jinja2Templates(directory=str(BASE_DIR / "templates"))
|
||
|
|
|
||
|
|
|
||
|
|
def _iso_rows(rows: list) -> list:
|
||
|
|
for row in rows:
|
||
|
|
for key, val in list(row.items()):
|
||
|
|
if hasattr(val, "isoformat"):
|
||
|
|
row[key] = val.isoformat()
|
||
|
|
return rows
|
||
|
|
|
||
|
|
|
||
|
|
@router.get("")
|
||
|
|
async def marketing_page(request: Request):
|
||
|
|
evaluate_agent_rules()
|
||
|
|
posts, mentions, accounts, rules, logs = [], [], [], [], []
|
||
|
|
analytics = {"mention_count": 0, "avg_sentiment": 0}
|
||
|
|
try:
|
||
|
|
posts = _iso_rows(fetch_all(
|
||
|
|
"""SELECT sp.*, sa.platform, sa.username FROM scheduled_posts sp
|
||
|
|
JOIN social_accounts sa ON sp.account_id = sa.id
|
||
|
|
ORDER BY sp.scheduled_time DESC LIMIT 50"""
|
||
|
|
))
|
||
|
|
except Exception:
|
||
|
|
posts = []
|
||
|
|
try:
|
||
|
|
mentions = _iso_rows(fetch_all(
|
||
|
|
"SELECT * FROM social_mentions ORDER BY created_at DESC LIMIT 50"
|
||
|
|
))
|
||
|
|
row = fetch_one(
|
||
|
|
"SELECT COUNT(*) AS cnt, COALESCE(AVG(sentiment_score),0) AS avg FROM social_mentions"
|
||
|
|
)
|
||
|
|
if row:
|
||
|
|
analytics["mention_count"] = int(row["cnt"])
|
||
|
|
analytics["avg_sentiment"] = float(row["avg"])
|
||
|
|
except Exception:
|
||
|
|
mentions = []
|
||
|
|
try:
|
||
|
|
accounts = _iso_rows(fetch_all("SELECT * FROM social_accounts ORDER BY platform"))
|
||
|
|
except Exception:
|
||
|
|
accounts = []
|
||
|
|
try:
|
||
|
|
rules = _iso_rows(fetch_all("SELECT * FROM agent_rules ORDER BY id"))
|
||
|
|
logs = _iso_rows(fetch_all(
|
||
|
|
"""SELECT al.*, ar.name AS rule_name FROM agent_logs al
|
||
|
|
LEFT JOIN agent_rules ar ON al.rule_id = ar.id ORDER BY al.created_at DESC LIMIT 30"""
|
||
|
|
))
|
||
|
|
except Exception:
|
||
|
|
pass
|
||
|
|
return templates.TemplateResponse(
|
||
|
|
"marketing.html",
|
||
|
|
{
|
||
|
|
"request": request,
|
||
|
|
"page_title": "Marketing",
|
||
|
|
"scheduled_posts": posts,
|
||
|
|
"social_mentions": mentions,
|
||
|
|
"accounts": accounts,
|
||
|
|
"agent_rules": rules,
|
||
|
|
"agent_logs": logs,
|
||
|
|
"analytics": analytics,
|
||
|
|
},
|
||
|
|
)
|