from datetime import datetime

from sqlalchemy.orm import Session
from sqlalchemy import desc, or_

from backend.models.video import Video
from backend.models.creator import Creator
from backend.services.viral_detector import ViralDetector
from backend.services.creator_baseline import CreatorBaselineService
from backend.services.language_detector import detect_language


detector = ViralDetector()
baseline_service = CreatorBaselineService()


def save_videos(db: Session, videos_data: list[dict]) -> list[Video]:
    """Save scraped videos to database, updating existing ones."""
    saved = []

    for data in videos_data:
        existing = (
            db.query(Video)
            .filter_by(platform=data["platform"], external_id=data["external_id"])
            .first()
        )

        # Find or create creator
        creator = None
        creator_username = data.get("creator_username", "")
        if creator_username:
            creator = (
                db.query(Creator)
                .filter_by(platform=data["platform"], username=creator_username)
                .first()
            )
            if not creator:
                followers = data.get("creator_followers", 0) or 0
                creator = Creator(
                    platform=data["platform"],
                    username=creator_username,
                    display_name=data.get("creator_display_name", creator_username),
                    profile_url=data.get("creator_profile_url", ""),
                    followers=followers,
                    size_tier=ViralDetector.get_size_tier(followers),
                )
                db.add(creator)
                db.flush()
            elif data.get("creator_followers") and data["creator_followers"] > 0:
                # Update followers if we got new data
                creator.followers = data["creator_followers"]
                creator.size_tier = ViralDetector.get_size_tier(data["creator_followers"])

        # Enrich with creator baseline data for scoring
        enriched = baseline_service.enrich_video_data(db, data)

        # Calculate viral score with outlier detection
        score_data = detector.calculate_score(enriched)

        # Calculate replicability
        replicability = detector.calculate_replicability(enriched, score_data)

        if existing:
            existing.views = data.get("views", existing.views)
            existing.likes = data.get("likes", existing.likes)
            existing.comments = data.get("comments", existing.comments)
            existing.shares = data.get("shares", existing.shares)
            existing.viral_score = score_data["viral_score"]
            existing.engagement_rate = score_data["engagement_rate"]
            existing.outlier_ratio = score_data["outlier_ratio"]
            existing.creator_avg_views = score_data.get("creator_avg_views", 0)
            existing.creator_followers = score_data.get("creator_followers", 0)
            existing.views_per_follower = score_data.get("views_per_follower", 0)
            existing.is_small_creator = score_data.get("is_small_creator", False)
            existing.replicability_score = replicability
            existing.collected_at = datetime.utcnow()
            # Update shop_product_url if available
            shop_url = data.get("shop_product_url", "")
            if shop_url and not existing.shop_product_url:
                existing.shop_product_url = shop_url
            # Update metadata fields
            if data.get("region") and not existing.region:
                existing.region = data["region"]
            if data.get("is_ad"):
                existing.is_ad = True
            if data.get("is_paid_partnership"):
                existing.is_paid_partnership = True
            if data.get("is_eligible_for_commission"):
                existing.is_eligible_for_commission = True
            if data.get("with_shop_entry"):
                existing.with_shop_entry = True
            if data.get("is_commerce_music"):
                existing.is_commerce_music = True
            if data.get("created_by_ai"):
                existing.created_by_ai = True
            if data.get("audio_language") and not existing.audio_language:
                existing.audio_language = data["audio_language"]
            if not existing.language:
                lang_text = f"{data.get('title', '')} {data.get('description', '')}"
                existing.language = detect_language(lang_text)
            saved.append(existing)
        else:
            # Detect language from title + description
            lang_text = f"{data.get('title', '')} {data.get('description', '')}"
            detected_lang = detect_language(lang_text)

            video = Video(
                platform=data["platform"],
                external_id=data["external_id"],
                url=data.get("url", ""),
                title=data.get("title", ""),
                description=data.get("description", ""),
                hashtags=data.get("hashtags", []),
                creator_id=creator.id if creator else None,
                views=data.get("views", 0),
                likes=data.get("likes", 0),
                comments=data.get("comments", 0),
                shares=data.get("shares", 0),
                duration=data.get("duration", 0),
                thumbnail_url=data.get("thumbnail_url", ""),
                viral_score=score_data["viral_score"],
                engagement_rate=score_data["engagement_rate"],
                outlier_ratio=score_data["outlier_ratio"],
                creator_avg_views=score_data.get("creator_avg_views", 0),
                creator_followers=score_data.get("creator_followers", 0),
                views_per_follower=score_data.get("views_per_follower", 0),
                is_small_creator=score_data.get("is_small_creator", False),
                replicability_score=replicability,
                published_at=data.get("published_at"),
                language=detected_lang,
                shop_product_url=data.get("shop_product_url", ""),
                region=data.get("region", ""),
                is_ad=bool(data.get("is_ad", False)),
                is_paid_partnership=bool(data.get("is_paid_partnership", False)),
                is_eligible_for_commission=bool(data.get("is_eligible_for_commission", False)),
                with_shop_entry=bool(data.get("with_shop_entry", False)),
                is_commerce_music=bool(data.get("is_commerce_music", False)),
                created_by_ai=bool(data.get("created_by_ai", False)),
                audio_language=data.get("audio_language", ""),
            )
            db.add(video)
            saved.append(video)

    db.commit()
    return saved


def get_videos(
    db: Session,
    platform: str = "",
    search: str = "",
    sort_by: str = "viral_score",
    min_score: float = 0,
    limit: int = 50,
    offset: int = 0,
    creator_tier: str = "",
    min_outlier: float = 0,
    language: str = "",
) -> tuple[list[Video], int]:
    """Get videos with filters, sorting and pagination."""
    query = db.query(Video)

    if language:
        query = query.filter(Video.language == language)

    if platform:
        query = query.filter(Video.platform == platform)

    if search:
        query = query.filter(
            or_(
                Video.title.ilike(f"%{search}%"),
                Video.description.ilike(f"%{search}%"),
            )
        )

    if min_score > 0:
        query = query.filter(Video.viral_score >= min_score)

    if min_outlier > 0:
        query = query.filter(Video.outlier_ratio >= min_outlier)

    if creator_tier:
        if creator_tier == "small":
            query = query.filter(Video.is_small_creator == True)  # noqa: E712
        elif creator_tier == "nano":
            query = query.filter(Video.creator_followers < 10_000, Video.creator_followers > 0)
        elif creator_tier == "micro":
            query = query.filter(Video.creator_followers >= 10_000, Video.creator_followers < 100_000)

    total = query.count()

    sort_columns = {
        "viral_score": desc(Video.viral_score),
        "outlier": desc(Video.outlier_ratio),
        "replicability": desc(Video.replicability_score),
        "views": desc(Video.views),
        "likes": desc(Video.likes),
        "engagement": desc(Video.engagement_rate),
        "recent": desc(Video.published_at),
        "collected": desc(Video.collected_at),
    }
    order = sort_columns.get(sort_by, desc(Video.viral_score))
    query = query.order_by(order)

    videos = query.offset(offset).limit(limit).all()
    return videos, total


def get_video_by_id(db: Session, video_id: int) -> Video | None:
    return db.query(Video).filter(Video.id == video_id).first()


def get_opportunities(
    db: Session,
    platform: str = "",
    limit: int = 30,
) -> list[Video]:
    """Get videos with high replicability score - opportunities to replicate."""
    query = db.query(Video).filter(
        Video.replicability_score >= 50,
        Video.outlier_ratio >= 3,
    )
    if platform:
        query = query.filter(Video.platform == platform)
    return query.order_by(desc(Video.replicability_score)).limit(limit).all()


def get_explosions(db: Session, min_outlier: float = 10, limit: int = 20) -> list[Video]:
    """Get videos that are outlier explosions (views >> creator average)."""
    return (
        db.query(Video)
        .filter(Video.outlier_ratio >= min_outlier)
        .order_by(desc(Video.outlier_ratio))
        .limit(limit)
        .all()
    )


def get_stats(db: Session) -> dict:
    """Get dashboard statistics - TikTok only (matches Viral Scanner count)."""
    # Base query: only TikTok videos (matches Viral Scanner)
    base = db.query(Video).filter(Video.platform == "tiktok")

    total_videos = base.count()
    total_creators = db.query(Creator).count()

    platform_counts = {}
    for platform in ["youtube", "tiktok", "instagram"]:
        count = db.query(Video).filter(Video.platform == platform).count()
        platform_counts[platform] = count

    viral_count = base.filter(Video.viral_score >= 60).count()
    downloaded = base.filter(Video.is_downloaded == True).count()  # noqa: E712

    from sqlalchemy import func
    if total_videos > 0:
        avg_score = round(
            db.query(func.avg(Video.viral_score))
            .filter(Video.platform == "tiktok")
            .scalar() or 0, 1
        )
    else:
        avg_score = 0

    # New stats - TikTok only
    explosion_count = base.filter(Video.outlier_ratio >= 10).count()
    small_creator_virals = (
        db.query(Video)
        .filter(Video.platform == "tiktok")
        .filter(Video.is_small_creator == True, Video.viral_score >= 60)  # noqa: E712
    ).count()
    opportunities = (
        db.query(Video)
        .filter(Video.platform == "tiktok")
        .filter(Video.replicability_score >= 60)
    ).count()

    # Shop videos count
    shop_videos = (
        db.query(Video)
        .filter(Video.platform == "tiktok")
        .filter(Video.shop_product_url != None, Video.shop_product_url != "")  # noqa: E711
    ).count()

    return {
        "total_videos": total_videos,
        "total_creators": total_creators,
        "platform_counts": platform_counts,
        "viral_count": viral_count,
        "downloaded_count": downloaded,
        "avg_viral_score": round(float(avg_score), 1),
        "explosion_count": explosion_count,
        "small_creator_virals": small_creator_virals,
        "opportunities": opportunities,
        "shop_videos": shop_videos,
    }
