from fastapi import APIRouter, Request from fastapi.responses import RedirectResponse from fastapi.templating import Jinja2Templates from pathlib import Path import json from app.db import fetch_all, fetch_one from app.services.briefing import build_live_digest, collect_briefing_data, serialize_stats router = APIRouter(tags=["dashboard"]) BASE_DIR = Path(__file__).resolve().parent.parent.parent templates = Jinja2Templates(directory=str(BASE_DIR / "templates")) def _safe_count(table: str, where: str = "") -> int: try: clause = f" WHERE {where}" if where else "" row = fetch_one(f"SELECT COUNT(*) AS c FROM {table}{clause}") return int(row["c"]) if row else 0 except Exception: return 0 def _safe_sum(table: str, column: str, where: str = "") -> float: try: clause = f" WHERE {where}" if where else "" row = fetch_one(f"SELECT COALESCE(SUM({column}), 0) AS total FROM {table}{clause}") return float(row["total"]) if row else 0.0 except Exception: return 0.0 def _briefing_payload(briefing: dict | None) -> dict: """Live stats + live digest; opgeslagen briefing-tekst alleen als AI-rapport.""" live_stats = serialize_stats(collect_briefing_data()) payload: dict = { "content": None, "stats": live_stats, "live_digest": build_live_digest(live_stats), "created_at": None, } if not briefing: return payload payload["content"] = briefing.get("content") payload["created_at"] = briefing.get("created_at") return payload @router.get("/") async def dashboard(request: Request): kpis = { "deals_count": _safe_count("deals"), "clients_count": _safe_count("clients"), "pending_approvals": _safe_count("agent_events", "status = 'needs_approval'"), "pipeline_value": _safe_sum("deals", "value", "stage NOT IN ('won', 'lost')"), "browser_sessions_24h": 0, "monitor_sites": _safe_count("monitored_sites", "is_active = TRUE"), "supermarkets_count": _safe_count("supermarkets"), "clients_active": _safe_count("clients", "stage = 'active'"), "crm_partnerships": _safe_count("supermarkets", "partnership_status = 'active'"), } briefing = None try: briefing = fetch_one( "SELECT id, content, metadata, created_at FROM daily_briefings ORDER BY created_at DESC LIMIT 1" ) if briefing and briefing.get("created_at"): briefing["created_at"] = briefing["created_at"].isoformat() except Exception: briefing = None agent_feed: list = [] try: agent_feed = fetch_all( """ SELECT id, agent_name, event_type, title, body, status, created_at FROM agent_events ORDER BY created_at DESC LIMIT 25 """ ) for ev in agent_feed: if ev.get("created_at"): ev["created_at"] = ev["created_at"].isoformat() except Exception: agent_feed = [] approvals: list = [] try: approvals = fetch_all( """ SELECT id, agent_name, event_type, title, body, status, created_at FROM agent_events WHERE status = 'needs_approval' ORDER BY created_at ASC LIMIT 25 """ ) for ev in approvals: if ev.get("created_at"): ev["created_at"] = ev["created_at"].isoformat() except Exception: approvals = [] browser_sessions: list = [] try: browser_sessions = fetch_all( """ SELECT id, url, final_url, title, task, status, created_at, LEFT(content_text, 300) AS preview FROM browser_sessions ORDER BY created_at DESC LIMIT 8 """ ) kpis["browser_sessions_24h"] = _safe_count( "browser_sessions", "created_at >= NOW() - INTERVAL '24 hours'" ) for s in browser_sessions: if s.get("created_at"): s["created_at"] = s["created_at"].isoformat() except Exception: browser_sessions = [] monitor_sites: list = [] monitor_changes: list = [] try: monitor_sites = fetch_all( """ SELECT id, name, url, last_title, last_crawled, is_active FROM monitored_sites WHERE is_active = TRUE ORDER BY last_crawled DESC NULLS LAST LIMIT 10 """ ) for s in monitor_sites: if s.get("last_crawled"): s["last_crawled"] = s["last_crawled"].isoformat() monitor_changes = fetch_all( """ SELECT pc.id, pc.changed_at, ms.name, ms.url FROM page_changes pc JOIN monitored_sites ms ON ms.id = pc.site_id WHERE pc.changed_at >= NOW() - INTERVAL '7 days' ORDER BY pc.changed_at DESC LIMIT 10 """ ) for c in monitor_changes: if c.get("changed_at"): c["changed_at"] = c["changed_at"].isoformat() except Exception: pass return templates.TemplateResponse( "dashboard.html", { "request": request, "page_title": "CEO Dashboard", "kpis": kpis, "briefing": briefing, "briefing_payload": _briefing_payload(briefing), "agent_feed": agent_feed, "approvals": approvals, "browser_sessions": browser_sessions, "monitor_sites": monitor_sites, "monitor_changes": monitor_changes, }, ) @router.get("/cto") async def cto_redirect(): return RedirectResponse(url="/ops", status_code=302) @router.get("/cto/dashboard") async def cto_dashboard_legacy(request: Request): kpis = { "pending_approvals": _safe_count("agent_events", "status = 'needs_approval'"), "browser_sessions_24h": 0, } try: kpis["browser_sessions_24h"] = _safe_count( "browser_sessions", "created_at >= NOW() - INTERVAL '24 hours'" ) except Exception: pass agent_feed: list = [] try: agent_feed = fetch_all( """ SELECT id, agent_name, event_type, title, body, status, created_at FROM agent_events ORDER BY created_at DESC LIMIT 25 """ ) for ev in agent_feed: if ev.get("created_at"): ev["created_at"] = ev["created_at"].isoformat() except Exception: agent_feed = [] browser_sessions: list = [] try: browser_sessions = fetch_all( """ SELECT id, url, final_url, title, task, status, created_at FROM browser_sessions ORDER BY created_at DESC LIMIT 8 """ ) for s in browser_sessions: if s.get("created_at"): s["created_at"] = s["created_at"].isoformat() except Exception: browser_sessions = [] return templates.TemplateResponse( "cto-dashboard.html", { "request": request, "page_title": "CTO Dashboard", "kpis": kpis, "agent_feed": agent_feed, "browser_sessions": browser_sessions, }, ) @router.get("/marketing-redirect") async def marketing_redirect(): return RedirectResponse(url="/marketing", status_code=302)