app/services/export_service.py

"""Author RAG — Export Service.

Business logic for admin CSV exports: sessions, analytics, conversations.
"""

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

from fastapi.responses import StreamingResponse
from sqlalchemy import Integer, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.analytics import AnalyticsEvent
from app.models.chat_session import ChatMessage, ChatSession

EXPORT_ROW_LIMIT = 50000


class ExportService:
    """Orchestrates CSV data exports for the admin panel."""

    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def export_sessions(self, author_id: str, days: int) -> StreamingResponse:
        """Export chat sessions as CSV."""
        since = datetime.now(timezone.utc) - timedelta(days=days)
        result = await self._db.execute(
            select(ChatSession)
            .where(ChatSession.author_id == author_id, ChatSession.created_at >= since)
            .order_by(ChatSession.created_at.desc())
            .limit(EXPORT_ROW_LIMIT)
        )
        sessions = result.scalars().all()

        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow([
            "session_id", "visitor_fp", "visitor_name", "country", "city",
            "device", "browser", "os", "turn_count", "link_clicked", "rating",
            "blocked", "created_at",
        ])
        for s in sessions:
            writer.writerow([
                s.id, s.visitor_fingerprint[:8], s.visitor_name or "",
                s.country_name or "", s.city or "", s.device_type or "",
                s.browser or "", s.os or "", s.turn_count, s.link_clicked,
                s.rating or "", s.blocked,
                s.created_at.isoformat() if s.created_at else "",
            ])

        output.seek(0)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename=sessions_{days}d.csv"},
        )

    async def export_analytics(self, author_id: str, days: int) -> StreamingResponse:
        """Export daily analytics aggregates as CSV."""
        since = datetime.now(timezone.utc) - timedelta(days=days)
        result = await self._db.execute(
            select(
                func.date(AnalyticsEvent.timestamp).label("date"),
                func.count().label("events"),
                func.sum(AnalyticsEvent.prompt_tokens + AnalyticsEvent.completion_tokens).label("tokens"),
                func.avg(AnalyticsEvent.response_ms).label("avg_latency"),
                func.sum(func.cast(AnalyticsEvent.link_clicked, Integer)).label("clicks"),
            )
            .where(AnalyticsEvent.author_id == author_id, AnalyticsEvent.timestamp >= since)
            .group_by(func.date(AnalyticsEvent.timestamp))
            .order_by(func.date(AnalyticsEvent.timestamp))
        )
        rows = result.all()

        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow(["date", "events", "total_tokens", "avg_latency_ms", "link_clicks"])
        for r in rows:
            writer.writerow([
                str(r.date), r.events, r.tokens or 0,
                round(r.avg_latency or 0, 1), r.clicks or 0,
            ])

        output.seek(0)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename=analytics_{days}d.csv"},
        )

    async def export_conversations(
        self,
        author_id: str,
        days: int,
        session_id: str | None = None,
    ) -> StreamingResponse:
        """Export full conversation transcripts as CSV."""
        since = datetime.now(timezone.utc) - timedelta(days=days)
        query = (
            select(ChatMessage)
            .join(ChatSession, ChatMessage.session_id == ChatSession.id)
            .where(ChatSession.author_id == author_id)
            .order_by(ChatMessage.created_at.asc())
            .limit(EXPORT_ROW_LIMIT)
        )
        if session_id:
            query = query.where(ChatSession.id == session_id)
        else:
            query = query.where(ChatSession.created_at >= since)

        result = await self._db.execute(query)
        messages = result.scalars().all()

        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow([
            "session_id", "role", "content", "intent", "confidence",
            "faithfulness", "hallucination", "tokens", "latency_ms", "timestamp",
        ])
        for m in messages:
            writer.writerow([
                m.session_id, m.role, m.content[:500], m.intent or "",
                m.intent_confidence or "", m.faithfulness_score or "",
                m.hallucination_detected or "",
                (m.prompt_tokens or 0) + (m.completion_tokens or 0),
                m.response_ms or "",
                m.created_at.isoformat() if m.created_at else "",
            ])

        output.seek(0)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": "attachment; filename=conversations.csv"},
        )