from datetime import datetime

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

from backend.database.connection import Base


class Video(Base):
    __tablename__ = "videos"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    platform: Mapped[str] = mapped_column(String(20), index=True)
    external_id: Mapped[str] = mapped_column(String(100), index=True)
    url: Mapped[str] = mapped_column(Text)
    title: Mapped[str] = mapped_column(Text, default="")
    description: Mapped[str] = mapped_column(Text, default="")
    hashtags: Mapped[dict | None] = mapped_column(JSON, default=list)

    creator_id: Mapped[int | None] = mapped_column(
        ForeignKey("creators.id"), nullable=True
    )

    views: Mapped[int] = mapped_column(Integer, default=0)
    likes: Mapped[int] = mapped_column(Integer, default=0)
    comments: Mapped[int] = mapped_column(Integer, default=0)
    shares: Mapped[int] = mapped_column(Integer, default=0)

    duration: Mapped[int] = mapped_column(Integer, default=0)  # seconds
    thumbnail_url: Mapped[str] = mapped_column(Text, default="")

    viral_score: Mapped[float] = mapped_column(Float, default=0.0, index=True)
    engagement_rate: Mapped[float] = mapped_column(Float, default=0.0)

    # Outlier detection fields
    outlier_ratio: Mapped[float] = mapped_column(Float, default=0.0)
    creator_avg_views: Mapped[float] = mapped_column(Float, default=0.0)
    creator_followers: Mapped[int] = mapped_column(Integer, default=0)
    views_per_follower: Mapped[float] = mapped_column(Float, default=0.0)
    is_small_creator: Mapped[bool] = mapped_column(Boolean, default=False)
    replicability_score: Mapped[float] = mapped_column(Float, default=0.0)
    growth_snapshots: Mapped[str | None] = mapped_column(Text, nullable=True)

    published_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    collected_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    shop_product_url: Mapped[str] = mapped_column(Text, default="", index=True)

    # TikTok metadata fields
    region: Mapped[str] = mapped_column(String(5), default="", index=True)  # BR, US, etc.
    is_ad: Mapped[bool] = mapped_column(Boolean, default=False)
    is_paid_partnership: Mapped[bool] = mapped_column(Boolean, default=False)
    is_eligible_for_commission: Mapped[bool] = mapped_column(Boolean, default=False)
    with_shop_entry: Mapped[bool] = mapped_column(Boolean, default=False)
    is_commerce_music: Mapped[bool] = mapped_column(Boolean, default=False)
    created_by_ai: Mapped[bool] = mapped_column(Boolean, default=False)
    audio_language: Mapped[str] = mapped_column(String(10), default="")  # eng-US, pt-BR, etc.

    is_downloaded: Mapped[bool] = mapped_column(Boolean, default=False)
    language: Mapped[str] = mapped_column(String(5), default="", index=True)  # pt, en, es, etc.
    download_path: Mapped[str] = mapped_column(Text, default="")

    creator = relationship("Creator", back_populates="videos")
    collections = relationship(
        "VideoCollection", back_populates="video", cascade="all, delete-orphan"
    )

    def __repr__(self):
        return f"<Video {self.platform}:{self.external_id} score={self.viral_score}>"

    @property
    def views_formatted(self) -> str:
        if self.views >= 1_000_000:
            return f"{self.views / 1_000_000:.1f}M"
        if self.views >= 1_000:
            return f"{self.views / 1_000:.1f}K"
        return str(self.views)

    @property
    def duration_formatted(self) -> str:
        mins, secs = divmod(self.duration, 60)
        if mins >= 60:
            hours, mins = divmod(mins, 60)
            return f"{hours}:{mins:02d}:{secs:02d}"
        return f"{mins}:{secs:02d}"
