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 TikTokShopAccount(Base):
    """Track TikTok Shop accounts for multi-account monitoring."""
    __tablename__ = "tiktok_shop_accounts"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    username: Mapped[str] = mapped_column(String(200), unique=True, index=True)
    display_name: Mapped[str] = mapped_column(String(200), default="")
    avatar: Mapped[str] = mapped_column(Text, default="")
    bio: Mapped[str] = mapped_column(Text, default="")

    # Profile metrics
    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)

    # Shop info
    has_shop: Mapped[bool] = mapped_column(Boolean, default=False)
    shop_name: Mapped[str] = mapped_column(String(200), default="")
    shop_logo: Mapped[str] = mapped_column(Text, default="")
    seller_id: Mapped[str] = mapped_column(String(100), default="")
    shop_rating: Mapped[float] = mapped_column(Float, default=0.0)
    shop_region: Mapped[str] = mapped_column(String(20), default="")

    # Shop metrics
    total_products: Mapped[int] = mapped_column(Integer, default=0)
    total_sold: Mapped[int] = mapped_column(Integer, default=0)
    total_reviews: Mapped[int] = mapped_column(Integer, default=0)
    shop_followers: Mapped[int] = mapped_column(Integer, default=0)

    # Video aggregates (from yt-dlp)
    total_views: Mapped[int] = mapped_column(Integer, default=0)
    avg_views: Mapped[float] = mapped_column(Float, default=0.0)
    avg_likes: Mapped[float] = mapped_column(Float, default=0.0)
    avg_comments: Mapped[float] = mapped_column(Float, default=0.0)
    avg_shares: Mapped[float] = mapped_column(Float, default=0.0)
    recent_videos: Mapped[dict | None] = mapped_column(JSON, default=list)

    # Revenue
    estimated_revenue: Mapped[float] = mapped_column(Float, default=0.0)
    avg_product_price: Mapped[float] = mapped_column(Float, default=0.0)

    # Analytics (computed from rich video data)
    avg_engagement_rate: Mapped[float] = mapped_column(Float, default=0.0)
    avg_hook_strength: Mapped[float] = mapped_column(Float, default=0.0)
    avg_shareability: Mapped[float] = mapped_column(Float, default=0.0)
    total_shares: Mapped[int] = mapped_column(Integer, default=0)

    # Cadence
    posts_per_week: Mapped[float] = mapped_column(Float, default=0.0)
    best_posting_days: Mapped[dict | None] = mapped_column(JSON, default=list)
    best_posting_hours: Mapped[dict | None] = mapped_column(JSON, default=list)

    # Format mix
    pct_short_videos: Mapped[float] = mapped_column(Float, default=0.0)
    pct_medium_videos: Mapped[float] = mapped_column(Float, default=0.0)
    pct_long_videos: Mapped[float] = mapped_column(Float, default=0.0)
    avg_duration: Mapped[float] = mapped_column(Float, default=0.0)

    # Commerce
    commerce_video_count: Mapped[int] = mapped_column(Integer, default=0)
    commerce_ratio: Mapped[float] = mapped_column(Float, default=0.0)
    commission_eligible_count: Mapped[int] = mapped_column(Integer, default=0)

    # Hashtags
    top_hashtags: Mapped[dict | None] = mapped_column(JSON, default=list)
    avg_hashtags_per_post: Mapped[float] = mapped_column(Float, default=0.0)

    # Full video data (30 videos with rich metadata)
    full_videos: Mapped[dict | None] = mapped_column(JSON, default=list)

    # Derived revenue
    revenue_per_video: Mapped[float] = mapped_column(Float, default=0.0)
    follower_to_sale_ratio: Mapped[float] = mapped_column(Float, default=0.0)

    # AI report cache
    ai_report: Mapped[dict | None] = mapped_column(JSON, default=None, nullable=True)
    ai_report_generated_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    # Insights cache (AI-generated actionable insights)
    insights_cache: Mapped[dict | None] = mapped_column(JSON, default=None, nullable=True)
    insights_generated_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    # Video ideas cache (AI-generated content ideas)
    video_ideas_cache: Mapped[dict | None] = mapped_column(JSON, default=None, nullable=True)
    video_ideas_generated_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    # Product-video mapping cache
    product_video_map: Mapped[dict | None] = mapped_column(JSON, default=None, nullable=True)

    # Status
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    notes: Mapped[str] = mapped_column(Text, default="")
    category: Mapped[str] = mapped_column(String(100), default="")
    last_checked: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    last_error: Mapped[str] = mapped_column(Text, default="")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "username": self.username,
            "display_name": self.display_name,
            "avatar": self.avatar,
            "bio": self.bio,
            "followers": self.followers,
            "following": self.following,
            "total_likes": self.total_likes,
            "video_count": self.video_count,
            "has_shop": self.has_shop,
            "shop_name": self.shop_name,
            "shop_logo": self.shop_logo,
            "seller_id": self.seller_id,
            "shop_rating": self.shop_rating,
            "shop_region": self.shop_region,
            "total_products": self.total_products,
            "total_sold": self.total_sold,
            "total_reviews": self.total_reviews,
            "shop_followers": self.shop_followers,
            "total_views": self.total_views,
            "avg_views": self.avg_views,
            "avg_likes": self.avg_likes,
            "avg_comments": self.avg_comments,
            "avg_shares": self.avg_shares,
            "recent_videos": self.recent_videos or [],
            "estimated_revenue": self.estimated_revenue,
            "avg_product_price": self.avg_product_price,
            "avg_engagement_rate": self.avg_engagement_rate,
            "avg_hook_strength": self.avg_hook_strength,
            "avg_shareability": self.avg_shareability,
            "total_shares": self.total_shares,
            "posts_per_week": self.posts_per_week,
            "best_posting_days": self.best_posting_days or [],
            "best_posting_hours": self.best_posting_hours or [],
            "pct_short_videos": self.pct_short_videos,
            "pct_medium_videos": self.pct_medium_videos,
            "pct_long_videos": self.pct_long_videos,
            "avg_duration": self.avg_duration,
            "commerce_video_count": self.commerce_video_count,
            "commerce_ratio": self.commerce_ratio,
            "commission_eligible_count": self.commission_eligible_count,
            "top_hashtags": self.top_hashtags or [],
            "avg_hashtags_per_post": self.avg_hashtags_per_post,
            "revenue_per_video": self.revenue_per_video,
            "follower_to_sale_ratio": self.follower_to_sale_ratio,
            "ai_report_generated_at": self.ai_report_generated_at.isoformat() if self.ai_report_generated_at else None,
            "insights_generated_at": self.insights_generated_at.isoformat() if self.insights_generated_at else None,
            "video_ideas_generated_at": self.video_ideas_generated_at.isoformat() if self.video_ideas_generated_at else None,
            "has_product_video_map": bool(self.product_video_map),
            "is_active": self.is_active,
            "notes": self.notes,
            "category": self.category,
            "last_checked": self.last_checked.isoformat() if self.last_checked else None,
            "last_error": self.last_error,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class TikTokShopAccountSnapshot(Base):
    """Track TikTok Shop account metrics over time."""
    __tablename__ = "tiktok_shop_account_snapshots"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    account_id: Mapped[int] = mapped_column(Integer, index=True)
    captured_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, 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)
    total_views: Mapped[int] = mapped_column(Integer, default=0)
    avg_views: Mapped[float] = mapped_column(Float, default=0.0)
    total_products: Mapped[int] = mapped_column(Integer, default=0)
    total_sold: Mapped[int] = mapped_column(Integer, default=0)
    total_reviews: Mapped[int] = mapped_column(Integer, default=0)
    estimated_revenue: Mapped[float] = mapped_column(Float, default=0.0)

    # New analytics fields
    avg_engagement_rate: Mapped[float] = mapped_column(Float, default=0.0)
    commerce_video_count: Mapped[int] = mapped_column(Integer, default=0)
    total_shares: Mapped[int] = mapped_column(Integer, default=0)
    avg_hook_strength: Mapped[float] = mapped_column(Float, default=0.0)
    avg_likes: Mapped[float] = mapped_column(Float, default=0.0)
    avg_shares: Mapped[float] = mapped_column(Float, default=0.0)
    shop_followers: Mapped[int] = mapped_column(Integer, default=0)
    avg_product_price: Mapped[float] = mapped_column(Float, default=0.0)
    posts_per_week: Mapped[float] = mapped_column(Float, default=0.0)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "account_id": self.account_id,
            "captured_at": self.captured_at.isoformat() if self.captured_at else None,
            "followers": self.followers,
            "total_likes": self.total_likes,
            "video_count": self.video_count,
            "total_views": self.total_views,
            "avg_views": self.avg_views,
            "total_products": self.total_products,
            "total_sold": self.total_sold,
            "total_reviews": self.total_reviews,
            "estimated_revenue": self.estimated_revenue,
            "avg_engagement_rate": self.avg_engagement_rate,
            "commerce_video_count": self.commerce_video_count,
            "total_shares": self.total_shares,
            "avg_hook_strength": self.avg_hook_strength,
            "avg_likes": self.avg_likes,
            "avg_shares": self.avg_shares,
            "shop_followers": self.shop_followers,
            "avg_product_price": self.avg_product_price,
            "posts_per_week": self.posts_per_week,
        }
