app/analytics/models.py

from __future__ import annotations

from datetime import datetime, timezone
from uuid import uuid4

from sqlalchemy import (
    JSON,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.social.models import SocialBase


def utcnow() -> datetime:
    return datetime.now(timezone.utc)


def new_id() -> str:
    return str(uuid4())


class AnalyticsSyncRun(SocialBase):
    __tablename__ = "analytics_sync_runs"
    __table_args__ = (
        UniqueConstraint(
            "workspace_id", "idempotency_key", name="uq_analytics_sync_workspace_idempotency"
        ),
        Index("ix_analytics_sync_workspace_status", "workspace_id", "status"),
        Index("ix_analytics_sync_due", "status", "next_attempt_at"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_id)
    workspace_id: Mapped[str] = mapped_column(String(120), nullable=False)
    project_id: Mapped[str | None] = mapped_column(String(36))
    provider: Mapped[str | None] = mapped_column(String(32))
    date_from: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    date_to: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    timezone: Mapped[str] = mapped_column(String(100), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="queued")
    idempotency_key: Mapped[str] = mapped_column(String(255), nullable=False)
    requested_by: Mapped[str | None] = mapped_column(String(120))
    attempt_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    next_attempt_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    error_code: Mapped[str | None] = mapped_column(String(100))
    error_message: Mapped[str | None] = mapped_column(Text)
    metrics_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=utcnow
    )
    started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=utcnow, onupdate=utcnow
    )


class AnalyticsMetricSnapshot(SocialBase):
    __tablename__ = "analytics_metric_snapshots"
    __table_args__ = (
        UniqueConstraint(
            "workspace_id",
            "provider",
            "external_object_id",
            "metric_name",
            "bucket_start",
            name="uq_analytics_metric_snapshot",
        ),
        Index("ix_analytics_metric_snapshots_workspace_bucket", "workspace_id", "bucket_start"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_id)
    workspace_id: Mapped[str] = mapped_column(String(120), nullable=False)
    project_id: Mapped[str | None] = mapped_column(String(36))
    social_post_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("social_posts.id", ondelete="CASCADE")
    )
    social_account_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("social_accounts.id", ondelete="CASCADE")
    )
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    external_object_id: Mapped[str] = mapped_column(String(255), nullable=False)
    metric_name: Mapped[str] = mapped_column(String(64), nullable=False)
    metric_value: Mapped[float] = mapped_column(Float, nullable=False)
    bucket_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    dimensions: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False, default=dict)
    source: Mapped[str] = mapped_column(String(64), nullable=False, default="provider")
    collected_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=utcnow
    )
    provider_updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class AnalyticsPostMetric(SocialBase):
    __tablename__ = "analytics_post_metrics"
    __table_args__ = (
        UniqueConstraint(
            "workspace_id",
            "social_post_target_id",
            "metric_date",
            name="uq_analytics_post_metric_bucket",
        ),
        Index("ix_analytics_post_metrics_workspace_date", "workspace_id", "metric_date"),
        Index(
            "ix_analytics_post_metrics_project_date", "workspace_id", "project_id", "metric_date"
        ),
        Index("ix_analytics_post_metrics_provider_date", "workspace_id", "provider", "metric_date"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_id)
    workspace_id: Mapped[str] = mapped_column(String(120), nullable=False)
    project_id: Mapped[str | None] = mapped_column(String(36))
    social_post_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("social_posts.id", ondelete="CASCADE"), nullable=False
    )
    social_post_target_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("social_post_targets.id", ondelete="CASCADE"), nullable=False
    )
    social_account_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("social_accounts.id", ondelete="CASCADE"), nullable=False
    )
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    external_post_id: Mapped[str | None] = mapped_column(String(255))
    metric_date: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    views: Mapped[int | None] = mapped_column()
    impressions: Mapped[int | None] = mapped_column()
    likes: Mapped[int | None] = mapped_column()
    comments: Mapped[int | None] = mapped_column()
    shares: Mapped[int | None] = mapped_column()
    engagement_rate: Mapped[float | None] = mapped_column(Float)
    dimensions: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False, default=dict)
    source: Mapped[str] = mapped_column(String(64), nullable=False, default="provider")
    collected_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=utcnow
    )
    provider_updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class AnalyticsPlatformMetric(SocialBase):
    __tablename__ = "analytics_platform_metrics"
    __table_args__ = (
        UniqueConstraint(
            "workspace_id",
            "provider",
            "social_account_id",
            "metric_date",
            name="uq_analytics_platform_metric_bucket",
        ),
        Index("ix_analytics_platform_metrics_workspace_date", "workspace_id", "metric_date"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_id)
    workspace_id: Mapped[str] = mapped_column(String(120), nullable=False)
    project_id: Mapped[str | None] = mapped_column(String(36))
    social_account_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("social_accounts.id", ondelete="CASCADE"), nullable=False
    )
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    metric_date: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    posts_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    views: Mapped[int | None] = mapped_column()
    impressions: Mapped[int | None] = mapped_column()
    likes: Mapped[int | None] = mapped_column()
    comments: Mapped[int | None] = mapped_column()
    shares: Mapped[int | None] = mapped_column()
    engagement_rate: Mapped[float | None] = mapped_column(Float)
    dimensions: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False, default=dict)
    source: Mapped[str] = mapped_column(String(64), nullable=False, default="provider")
    collected_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=utcnow
    )