app/models/analytics.py

"""Author RAG Chatbot SaaS — Analytics Models."""

import datetime

from sqlalchemy import Boolean, Date, Float, ForeignKey, Integer, String, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, TimestampMixin, generate_uuid


class AnalyticsEvent(Base):
    """Raw analytics event per chat turn — aggregated into AnalyticsDaily."""

    __tablename__ = "analytics_events"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=generate_uuid)
    session_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("chat_sessions.id", ondelete="CASCADE"), nullable=False, index=True
    )
    author_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True
    )
    book_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("books.id", ondelete="SET NULL"), nullable=True, index=True
    )
    timestamp: Mapped[datetime.datetime] = mapped_column(nullable=False, index=True)
    turn_number: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    intent: Mapped[str | None] = mapped_column(String(50), nullable=True)
    intent_confidence: Mapped[float | None] = mapped_column(Float, nullable=True)
    faithfulness_score: Mapped[float | None] = mapped_column(Float, nullable=True)
    hallucination_detected: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    boundary_triggered: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    prompt_tokens: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    completion_tokens: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    response_ms: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    upsell_strategy: Mapped[str | None] = mapped_column(String(50), nullable=True)
    link_shown: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    link_clicked: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    visitor_fingerprint: Mapped[str] = mapped_column(String(64), nullable=False, default="")


class AnalyticsDaily(Base):
    """Pre-aggregated daily analytics — populated by Celery task hourly."""

    __tablename__ = "analytics_daily"
    # DESIGN-2 fix: add the unique constraint required by the ON CONFLICT upsert
    # in aggregator.py. Without this, create_all creates the table without the
    # constraint and the upsert silently fails, creating duplicate rows.
    __table_args__ = (
        UniqueConstraint("author_id", "date", name="uq_analytics_daily_author_date"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=generate_uuid)
    author_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True
    )
    date: Mapped[datetime.date] = mapped_column(Date, nullable=False, index=True)

    total_chats: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    unique_visitors: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    total_tokens_used: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    total_link_clicks: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    avg_session_turns: Mapped[float] = mapped_column(Float, default=0.0, nullable=False)
    avg_faithfulness_score: Mapped[float] = mapped_column(Float, default=0.0, nullable=False)