app/api/analytics_export.py

"""Author RAG — Analytics CSV/JSON Export API.

Gives authors a way to download their raw conversation data
for use in external analytics tools (Excel, Notion, Google Sheets).

Routes:
  GET /api/admin/{slug}/analytics/export/conversations  → CSV of all sessions
  GET /api/admin/{slug}/analytics/export/events         → CSV of raw analytics events
  GET /api/admin/{slug}/analytics/summary               → JSON summary dashboard data
"""

import csv
import io
from datetime import datetime, timedelta, timezone

from fastapi import APIRouter, Depends, Query, HTTPException
from fastapi.responses import StreamingResponse
from sqlalchemy import select, func, desc
from sqlalchemy.ext.asyncio import AsyncSession

from app.dependencies import get_current_author, get_db
from app.models.analytics import AnalyticsEvent
from app.models.chat_session import ChatSession, ChatMessage

router = APIRouter()


@router.get("/{slug}/analytics/summary")
async def analytics_summary(
    slug: str,
    days: int = Query(30, ge=1, le=365),
    db: AsyncSession = Depends(get_db),
    author=Depends(get_current_author),
):
    """JSON analytics summary — powers the admin dashboard charts."""
    if author.id != slug:
        raise HTTPException(403, "Forbidden")

    since = datetime.now(timezone.utc) - timedelta(days=days)

    # Total sessions
    sessions_result = await db.execute(
        select(func.count(ChatSession.session_id))
        .where(ChatSession.author_id == slug, ChatSession.created_at >= since)
    )
    total_sessions = sessions_result.scalar() or 0

    # Total messages
    messages_result = await db.execute(
        select(func.count(ChatMessage.id))
        .join(ChatSession, ChatMessage.session_id == ChatSession.session_id)
        .where(ChatSession.author_id == slug, ChatSession.created_at >= since)
    )
    total_messages = messages_result.scalar() or 0

    # Intent distribution
    intent_result = await db.execute(
        select(AnalyticsEvent.event_type, func.count(AnalyticsEvent.id))
        .where(
            AnalyticsEvent.author_id == slug,
            AnalyticsEvent.created_at >= since,
            AnalyticsEvent.event_type.like("intent_%"),
        )
        .group_by(AnalyticsEvent.event_type)
    )
    intent_counts = {row[0].replace("intent_", ""): row[1] for row in intent_result}

    # Daily sessions for sparkline chart (last 14 days)
    daily_result = await db.execute(
        select(
            func.date(ChatSession.created_at).label("day"),
            func.count(ChatSession.session_id).label("count"),
        )
        .where(
            ChatSession.author_id == slug,
            ChatSession.created_at >= datetime.now(timezone.utc) - timedelta(days=14),
        )
        .group_by("day")
        .order_by("day")
    )
    daily_sessions = [{"date": str(r.day), "sessions": r.count} for r in daily_result]

    # Analytics events
    events_result = await db.execute(
        select(AnalyticsEvent)
        .where(AnalyticsEvent.author_id == slug, AnalyticsEvent.created_at >= since)
        .order_by(desc(AnalyticsEvent.created_at))
        .limit(5)
    )
    recent_events = [
        {"type": e.event_type, "at": e.created_at.isoformat() if e.created_at else None}
        for e in events_result.scalars()
    ]

    return {
        "period_days": days,
        "total_sessions": total_sessions,
        "total_messages": total_messages,
        "avg_messages_per_session": round(total_messages / total_sessions, 1) if total_sessions else 0,
        "intent_distribution": intent_counts,
        "daily_sessions": daily_sessions,
        "recent_events": recent_events,
    }


@router.get("/{slug}/analytics/export/conversations")
async def export_conversations_csv(
    slug: str,
    days: int = Query(90, ge=1, le=365),
    db: AsyncSession = Depends(get_db),
    author=Depends(get_current_author),
):
    """Stream conversation history as a CSV file download."""
    if author.id != slug:
        raise HTTPException(403, "Forbidden")

    since = datetime.now(timezone.utc) - timedelta(days=days)

    sessions_result = await db.execute(
        select(ChatSession)
        .where(ChatSession.author_id == slug, ChatSession.created_at >= since)
        .order_by(desc(ChatSession.created_at))
    )
    sessions = sessions_result.scalars().all()

    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(["session_id", "started_at", "role", "message", "tokens_used", "intent"])

    for session in sessions:
        msgs_result = await db.execute(
            select(ChatMessage)
            .where(ChatMessage.session_id == session.session_id)
            .order_by(ChatMessage.created_at)
        )
        for msg in msgs_result.scalars():
            writer.writerow([
                session.session_id,
                session.created_at.isoformat() if session.created_at else "",
                msg.role,
                msg.content[:500],  # Truncate very long messages
                getattr(msg, "tokens_used", ""),
                getattr(msg, "intent", ""),
            ])

    output.seek(0)
    filename = f"conversations_{slug}_{datetime.now().strftime('%Y%m%d')}.csv"
    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": f'attachment; filename="{filename}"'},
    )


@router.get("/{slug}/analytics/export/events")
async def export_events_csv(
    slug: str,
    days: int = Query(90, ge=1, le=365),
    db: AsyncSession = Depends(get_db),
    author=Depends(get_current_author),
):
    """Stream raw analytics events as CSV."""
    if author.id != slug:
        raise HTTPException(403, "Forbidden")

    since = datetime.now(timezone.utc) - timedelta(days=days)
    result = await db.execute(
        select(AnalyticsEvent)
        .where(AnalyticsEvent.author_id == slug, AnalyticsEvent.created_at >= since)
        .order_by(desc(AnalyticsEvent.created_at))
    )
    events = result.scalars().all()

    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(["event_type", "session_id", "visitor_id", "book_id", "country", "created_at", "metadata"])

    for e in events:
        writer.writerow([
            e.event_type,
            getattr(e, "session_id", ""),
            getattr(e, "visitor_id", ""),
            getattr(e, "book_id", ""),
            getattr(e, "country", ""),
            e.created_at.isoformat() if e.created_at else "",
            getattr(e, "metadata_json", ""),
        ])

    output.seek(0)
    filename = f"events_{slug}_{datetime.now().strftime('%Y%m%d')}.csv"
    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": f'attachment; filename="{filename}"'},
    )