from datetime import datetime

from sqlalchemy import String, Integer, Float, DateTime, Text, JSON, Boolean
from sqlalchemy.orm import Mapped, mapped_column

from backend.database.connection import Base


class CompetitorProfile(Base):
    """Track competitor profiles across platforms."""
    __tablename__ = "competitor_profiles"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    platform: Mapped[str] = mapped_column(String(20), index=True)
    username: Mapped[str] = mapped_column(String(200), index=True)
    display_name: Mapped[str] = mapped_column(String(200), default="")
    avatar: Mapped[str] = mapped_column(Text, default="")
    followers: Mapped[int] = mapped_column(Integer, default=0)
    following: Mapped[int] = mapped_column(Integer, default=0)
    total_likes: Mapped[int] = mapped_column(Integer, default=0)
    video_count: Mapped[int] = mapped_column(Integer, default=0)
    bio: Mapped[str] = mapped_column(Text, default="")
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    last_checked: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    last_video_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    notes: Mapped[str] = mapped_column(Text, default="")
    category: Mapped[str] = mapped_column(String(100), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "platform": self.platform,
            "username": self.username,
            "display_name": self.display_name,
            "avatar": self.avatar,
            "followers": self.followers,
            "following": self.following,
            "total_likes": self.total_likes,
            "video_count": self.video_count,
            "bio": self.bio,
            "is_active": self.is_active,
            "last_checked": self.last_checked.isoformat() if self.last_checked else None,
            "last_video_at": self.last_video_at.isoformat() if self.last_video_at else None,
            "notes": self.notes,
            "category": self.category,
        }


class CompetitorSnapshot(Base):
    """Track competitor metrics over time."""
    __tablename__ = "competitor_snapshots"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    competitor_id: Mapped[int] = mapped_column(Integer, index=True)
    followers: Mapped[int] = mapped_column(Integer, default=0)
    total_likes: Mapped[int] = mapped_column(Integer, default=0)
    video_count: Mapped[int] = mapped_column(Integer, default=0)
    captured_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "competitor_id": self.competitor_id,
            "followers": self.followers,
            "total_likes": self.total_likes,
            "video_count": self.video_count,
            "captured_at": self.captured_at.isoformat() if self.captured_at else None,
        }


class SoundTracker(Base):
    """Track TikTok sounds/audio growth for prediction."""
    __tablename__ = "sound_tracker"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    sound_id: Mapped[str] = mapped_column(String(100), index=True)
    title: Mapped[str] = mapped_column(Text, default="")
    author: Mapped[str] = mapped_column(String(200), default="")
    url: Mapped[str] = mapped_column(Text, default="")
    video_count: Mapped[int] = mapped_column(Integer, default=0)
    prev_video_count: Mapped[int] = mapped_column(Integer, default=0)
    growth_rate: Mapped[float] = mapped_column(Float, default=0.0)
    phase: Mapped[str] = mapped_column(String(20), default="emerging")  # emerging, growing, peak, declining
    first_seen: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    last_checked: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "sound_id": self.sound_id,
            "title": self.title,
            "author": self.author,
            "url": self.url,
            "video_count": self.video_count,
            "prev_video_count": self.prev_video_count,
            "growth_rate": self.growth_rate,
            "phase": self.phase,
            "platform": "tiktok",
            "first_seen": self.first_seen.isoformat() if self.first_seen else None,
            "last_checked": self.last_checked.isoformat() if self.last_checked else None,
            "tracked_at": self.first_seen.isoformat() if self.first_seen else None,
        }


class TrendLifecycle(Base):
    """Track trend phases: emerging, growing, peak, declining."""
    __tablename__ = "trend_lifecycle"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    keyword: Mapped[str] = mapped_column(String(200), index=True)
    platform: Mapped[str] = mapped_column(String(20), default="tiktok")
    phase: Mapped[str] = mapped_column(String(20), default="emerging")
    video_count: Mapped[int] = mapped_column(Integer, default=0)
    avg_views: Mapped[int] = mapped_column(Integer, default=0)
    avg_engagement: Mapped[float] = mapped_column(Float, default=0.0)
    top_creators: Mapped[dict | None] = mapped_column(JSON, default=list)
    first_seen: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    last_checked: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    history: Mapped[dict | None] = mapped_column(JSON, default=list)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "keyword": self.keyword,
            "platform": self.platform,
            "phase": self.phase,
            "video_count": self.video_count,
            "avg_views": self.avg_views,
            "avg_engagement": self.avg_engagement,
            "top_creators": self.top_creators or [],
            "first_seen": self.first_seen.isoformat() if self.first_seen else None,
            "last_checked": self.last_checked.isoformat() if self.last_checked else None,
            "history": self.history or [],
        }
