backend/app/dashboard/router.py

"""
Dashboard router — aggregated data for the main dashboard page.
Returns balances, recent transactions, spending breakdown, and AI briefing.
"""
from typing import Optional
from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import func, desc
from datetime import datetime, timedelta

from app.database.database import get_db
from app.database.models import User, Account, Transaction, AnalyticsSnapshot
from app.middleware.cache import cache
from app.ai.fraud import get_user_fraud_alerts
from collections import defaultdict

router = APIRouter(prefix="/api/dashboard", tags=["Dashboard"])

def _resolve_user(db: Session, user_id: Optional[str]) -> str:
    if user_id:
        return user_id
    user = db.query(User).first()
    if not user:
        from fastapi import HTTPException
        raise HTTPException(status_code=404, detail="No users found. Seed the database first.")
    return user.id

@router.get("/overview")
def get_dashboard_overview(user_id: Optional[str] = None, db: Session = Depends(get_db)):
    """
    Returns all data needed for the main dashboard in a single request:
    - account balances
    - monthly income/expense totals
    - recent transactions (last 10)
    - spending by category (current month)
    - financial health score
    - AI daily briefing (cached 1h)
    - fraud alert count
    """
    uid = _resolve_user(db, user_id)
    cache_key = f"dashboard:overview:{uid}"
    cached = cache.get(cache_key)
    if cached:
        return cached

    # ── Accounts & balances ──────────────────────────────────────────────────
    accounts = db.query(Account).filter(Account.user_id == uid).all()
    total_balance = sum(a.balance for a in accounts)
    account_list = [
        {"id": a.id, "type": a.type, "balance": a.balance, "currency": a.currency}
        for a in accounts
    ]

    # ── Current month date range ─────────────────────────────────────────────
    now = datetime.utcnow()
    month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)

    # ── Transactions this month (lightweight) ───────────────────────────────
    account_ids = [a.id for a in accounts]
    monthly_raw = (
        db.query(Transaction.type, Transaction.amount, Transaction.category)
        .filter(
            Transaction.account_id.in_(account_ids),
            Transaction.timestamp >= month_start,
        )
        .all()
    )

    monthly_income = sum(amt for t_type, amt, _ in monthly_raw if t_type == "credit")
    monthly_expenses = sum(abs(amt) for t_type, amt, _ in monthly_raw if t_type == "debit")
    savings_rate = round((monthly_income - monthly_expenses) / monthly_income * 100, 1) if monthly_income > 0 else 0.0

    # ── Spending by category ─────────────────────────────────────────────────
    category_totals: dict = {}
    for t_type, amt, cat in monthly_raw:
        if t_type == "debit" and cat:
            category_totals[cat] = category_totals.get(cat, 0) + abs(amt)

    spending_by_category = [
        {"name": cat, "value": round(total, 2)}
        for cat, total in sorted(category_totals.items(), key=lambda x: -x[1])
    ]

    # ── Recent transactions (last 10) ────────────────────────────────────────
    recent_txns = (
        db.query(Transaction)
        .filter(Transaction.account_id.in_(account_ids))
        .order_by(desc(Transaction.timestamp))
        .limit(10)
        .all()
    )
    recent_list = [
        {
            "id": t.id,
            "merchant": t.merchant or "Unknown",
            "category": t.category or "Other",
            "amount": t.amount if t.type == "credit" else -abs(t.amount),
            "type": t.type,
            "timestamp": t.timestamp.isoformat() if t.timestamp else None,
        }
        for t in recent_txns
    ]

    # ── 6-month cash flow trend (lightweight column-only query) ─────────────
    six_months_ago = now - timedelta(days=180)
    raw_6m = (
        db.query(
            Transaction.type,
            Transaction.amount,
            Transaction.timestamp,
        )
        .filter(
            Transaction.account_id.in_(account_ids),
            Transaction.timestamp >= six_months_ago,
        )
        .all()
    )

    # Group by month label in Python
    month_buckets: dict = defaultdict(lambda: {"income": 0.0, "expenses": 0.0})
    for t_type, t_amount, t_ts in raw_6m:
        if t_ts:
            label = t_ts.strftime("%b")
            if t_type == "credit":
                month_buckets[label]["income"] += t_amount
            else:
                month_buckets[label]["expenses"] += abs(t_amount)

    # Build ordered list for last 6 months
    cash_flow = []
    for i in range(5, -1, -1):
        m_date = (now.replace(day=1) - timedelta(days=i * 30))
        label = m_date.strftime("%b")
        inc = round(month_buckets[label]["income"], 2)
        exp = round(month_buckets[label]["expenses"], 2)
        cash_flow.append({
            "month": label,
            "income": inc,
            "expenses": exp,
            "savings": round(max(inc - exp, 0), 2),
        })

    # ── Financial health score (from cache only — never block on AI) ────────────
    score_data = {}
    health_score = 0.0
    try:
        score_cache_key = f"ai:coaching:score:{uid}"
        score_data = cache.get(score_cache_key) or {}
        health_score = score_data.get("overall_score", 0.0)
    except Exception:
        pass

    # ── Fraud alerts (cached separately) ────────────────────────────────────
    fraud_count = 0
    try:
        fraud_cache_key = f"dashboard:fraud:{uid}"
        cached_fraud = cache.get(fraud_cache_key)
        if cached_fraud is not None:
            fraud_count = cached_fraud
        else:
            fraud_data = get_user_fraud_alerts(db, uid)
            fraud_count = len(fraud_data.get("alerts", []))
            cache.set(fraud_cache_key, fraud_count, ttl=300)  # 5-min cache
    except Exception:
        pass

    # ── AI briefing (from cache only — never block on AI) ────────────────────
    briefing_key = f"ai:coaching:briefing:{uid}"
    briefing = cache.get(briefing_key) or {
        "summary": "Run /api/ai/coaching/briefing to generate your AI daily briefing.",
        "briefing": None,
    }

    result = {
        "total_balance": round(total_balance, 2),
        "accounts": account_list,
        "monthly_income": round(monthly_income, 2),
        "monthly_expenses": round(monthly_expenses, 2),
        "savings_rate": savings_rate,
        "spending_by_category": spending_by_category,
        "recent_transactions": recent_list,
        "cash_flow": cash_flow,
        "health_score": round(health_score, 1),
        "fraud_alert_count": fraud_count,
        "ai_briefing": briefing,
    }

    cache.set(cache_key, result, ttl=120)  # 2-minute cache
    return result