"""
Viral Scanner Service - Find viral TikTok videos with minimum API credits.

Strategy:
1. TikTok Creative Center (FREE) -> get trending hashtags/keywords
2. ScrapeCreators search/top (1 credit) -> get 30 videos per keyword
3. Local analysis (FREE) -> viral score, engagement, filters
4. TikTok Embed (FREE) -> display without download
"""

import time
import logging
from datetime import datetime

from sqlalchemy.orm import Session
from sqlalchemy import desc, or_, and_, func, String

from backend.database.connection import SessionLocal
from backend.models.video import Video
from backend.services.viral_detector import ViralDetector
from backend.services import video_service
from backend.services.api_usage_tracker import log_usage, can_use_credits, get_usage_today

logger = logging.getLogger("viral_social.scanner")
detector = ViralDetector()


def _is_recent(video_data: dict, cutoff: datetime) -> bool:
    """Check if a video was published after cutoff date. Accepts various date formats."""
    pub = video_data.get("published_at") or video_data.get("create_time") or video_data.get("createTime")
    if not pub:
        return True  # If no date info, keep the video
    try:
        if isinstance(pub, (int, float)):
            return datetime.utcfromtimestamp(pub) >= cutoff
        if isinstance(pub, str):
            # Try ISO format first, then common formats
            for fmt in ("%Y-%m-%dT%H:%M:%S", "%Y-%m-%dT%H:%M:%SZ", "%Y-%m-%d %H:%M:%S", "%Y-%m-%d"):
                try:
                    return datetime.strptime(pub[:19], fmt) >= cutoff
                except ValueError:
                    continue
            # Try parsing as timestamp string
            return datetime.utcfromtimestamp(float(pub)) >= cutoff
    except Exception:
        pass
    return True  # If we can't parse, keep the video


def _cache_new_thumbnails(db: Session, limit: int = 100):
    """Cache thumbnails for videos that don't have cached files yet."""
    import io
    from pathlib import Path
    from backend.core.config import settings as app_settings

    cache_dir = Path(app_settings.BASE_DIR) / "data" / "thumbs"
    cache_dir.mkdir(parents=True, exist_ok=True)

    videos = db.query(Video).filter(
        Video.thumbnail_url != None, Video.thumbnail_url != ''
    ).order_by(desc(Video.collected_at)).limit(limit).all()

    try:
        import httpx
        from PIL import Image
        try:
            import pillow_heif
            pillow_heif.register_heif_opener()
        except ImportError:
            pass
    except ImportError:
        return

    cached = 0
    for v in videos:
        f = cache_dir / f"{v.id}.jpg"
        if f.exists() and f.stat().st_size > 500:
            continue
        try:
            r = httpx.get(v.thumbnail_url, timeout=10, follow_redirects=True,
                         headers={"User-Agent": "Mozilla/5.0"})
            if r.status_code != 200 or len(r.content) < 100:
                continue
            img = Image.open(io.BytesIO(r.content)).convert("RGB")
            if img.width > 640:
                ratio = 640 / img.width
                img = img.resize((640, int(img.height * ratio)), Image.LANCZOS)
            for q in [82, 70, 55, 40]:
                buf = io.BytesIO()
                img.save(buf, format="JPEG", quality=q, optimize=True)
                if buf.tell() <= 100_000:
                    break
            buf = io.BytesIO()
            img.save(buf, format="JPEG", quality=q, optimize=True)
            f.write_bytes(buf.getvalue())
            cached += 1
        except Exception:
            continue

    if cached > 0:
        logger.info(f"[Scanner] Cached {cached} new thumbnails")

# In-memory state
_scanner_status = {
    "running": False,
    "last_run": None,
    "last_keywords": [],
    "last_videos_found": 0,
    "last_videos_saved": 0,
    "last_credits_used": 0,
    "total_runs": 0,
    "error": None,
}


def get_scanner_status() -> dict:
    return {**_scanner_status}


def scan_keywords(keywords: list[str], settings: dict) -> dict:
    """Run a scan cycle with given keywords. Supports TikTok + YouTube.

    Args:
        keywords: List of search keywords
        settings: Dict with scanner settings from AppSettings
            - scanner_platforms: comma-separated platforms (default: "tiktok")
            - scanner_max_results_per_keyword: max results per keyword

    Returns:
        Dict with scan results
    """
    global _scanner_status

    if _scanner_status["running"]:
        return {"error": "Scanner ja esta rodando", "status": "busy"}

    _scanner_status["running"] = True
    _scanner_status["error"] = None

    db = SessionLocal()
    results = {
        "keywords_searched": [],
        "total_videos_found": 0,
        "total_videos_saved": 0,
        "total_credits_used": 0,
        "videos_with_products": 0,
        "scan_time": 0,
        "errors": [],
    }

    try:
        from backend.scrapers.scrapecreators import ScrapeCreatorsAPI
        from backend.models.settings import AppSettings

        # Get API key
        api_key_row = db.query(AppSettings).filter_by(key="scrapecreators_api_key").first()
        if not api_key_row or not api_key_row.value:
            _scanner_status["running"] = False
            return {"error": "ScrapeCreators API key nao configurada. Configure em Configuracoes > Scanner.", "status": "error"}

        sc = ScrapeCreatorsAPI(api_key_row.value)

        daily_limit = int(settings.get("scanner_daily_credit_limit", 100))
        delay = int(settings.get("scanner_api_delay_seconds", 2))
        max_keywords = int(settings.get("scanner_max_keywords_per_cycle", 10))
        min_views = int(settings.get("scanner_min_views_to_save", 10000))
        only_products = settings.get("scanner_only_with_product", "false") == "true"
        results_per_kw = int(settings.get("scanner_max_results_per_keyword",
                            settings.get("scanner_results_per_keyword", 30)))

        # Platforms to scan
        platforms_str = settings.get("scanner_platforms", "tiktok")
        platforms = [p.strip() for p in platforms_str.split(",") if p.strip()]

        start_time = time.time()

        for kw in keywords[:max_keywords]:
            # Check daily limit
            if not can_use_credits(db, daily_limit):
                results["errors"].append(f"Limite diario de {daily_limit} creditos atingido")
                logger.warning(f"[Scanner] Daily limit {daily_limit} reached")
                break

            results["keywords_searched"].append(kw)

            # ── TikTok scan ──
            if "tiktok" in platforms:
                try:
                    # Alternate between search/top and search/keyword
                    # search/keyword returns music data for sound tracking
                    kw_index = keywords.index(kw) if kw in keywords else 0
                    if kw_index % 3 == 0:
                        # Every 3rd keyword: use keyword search (has music data)
                        videos_data = sc.tiktok_search(kw, results_per_kw)
                        endpoint_used = "/v1/tiktok/search/keyword"
                    else:
                        videos_data = sc.tiktok_search_top(kw, results_per_kw)
                        endpoint_used = "/v1/tiktok/search/top"
                    log_usage(db, "scrapecreators", endpoint_used,
                             credits=1, keyword=kw, videos_found=len(videos_data))
                    results["total_credits_used"] += 1

                    if videos_data:
                        # Track sounds from raw video data before filtering
                        try:
                            from backend.services.sound_predictor import track_sounds_from_videos
                            track_sounds_from_videos(db, videos_data)
                        except Exception:
                            pass

                        if min_views > 0:
                            videos_data = [v for v in videos_data if v.get("views", 0) >= min_views]
                        if only_products:
                            videos_data = [v for v in videos_data if v.get("shop_product_url")]
                        product_count = sum(1 for v in videos_data if v.get("shop_product_url"))
                        results["videos_with_products"] += product_count
                        results["total_videos_found"] += len(videos_data)
                        if videos_data:
                            saved = video_service.save_videos(db, videos_data)
                            results["total_videos_saved"] += len(saved)
                        logger.info(f"[Scanner] TikTok '{kw}': {len(videos_data)} found")
                except Exception as e:
                    results["errors"].append(f"TikTok '{kw}': {str(e)}")
                    log_usage(db, "scrapecreators", "/v1/tiktok/search/top",
                             credits=0, keyword=kw, status="error")
                    logger.error(f"[Scanner] TikTok error '{kw}': {e}")

                if delay > 0:
                    time.sleep(delay)

            # ── YouTube scan ──
            if "youtube" in platforms:
                try:
                    yt_result = sc.youtube_search(kw, results_per_kw)
                    # youtube_search returns {"videos": [...], "shorts": [...]}
                    videos_data = (yt_result.get("videos") or []) + (yt_result.get("shorts") or [])
                    log_usage(db, "scrapecreators", "/v1/youtube/search",
                             credits=1, keyword=kw, videos_found=len(videos_data))
                    results["total_credits_used"] += 1

                    if videos_data:
                        if min_views > 0:
                            videos_data = [v for v in videos_data if v.get("views", 0) >= min_views]
                        results["total_videos_found"] += len(videos_data)
                        if videos_data:
                            saved = video_service.save_videos(db, videos_data)
                            results["total_videos_saved"] += len(saved)
                        logger.info(f"[Scanner] YouTube '{kw}': {len(videos_data)} found")
                except Exception as e:
                    results["errors"].append(f"YouTube '{kw}': {str(e)}")
                    log_usage(db, "scrapecreators", "/v1/youtube/search",
                             credits=0, keyword=kw, status="error")
                    logger.error(f"[Scanner] YouTube error '{kw}': {e}")

                if delay > 0:
                    time.sleep(delay)

            # ── Instagram scan ──
            if "instagram" in platforms:
                try:
                    videos_data = sc.instagram_reels_search(kw, results_per_kw)
                    log_usage(db, "scrapecreators", "/v1/instagram/reels/search",
                             credits=1, keyword=kw, videos_found=len(videos_data))
                    results["total_credits_used"] += 1

                    if videos_data:
                        if min_views > 0:
                            videos_data = [v for v in videos_data if v.get("views", 0) >= min_views]
                        results["total_videos_found"] += len(videos_data)
                        if videos_data:
                            saved = video_service.save_videos(db, videos_data)
                            results["total_videos_saved"] += len(saved)
                        logger.info(f"[Scanner] Instagram '{kw}': {len(videos_data)} found")
                except Exception as e:
                    results["errors"].append(f"Instagram '{kw}': {str(e)}")
                    log_usage(db, "scrapecreators", "/v1/instagram/reels/search",
                             credits=0, keyword=kw, status="error")
                    logger.error(f"[Scanner] Instagram error '{kw}': {e}")

                if delay > 0:
                    time.sleep(delay)

        results["scan_time"] = round(time.time() - start_time, 1)
        results["platforms"] = platforms

        # Pre-cache thumbnails for new videos in background
        try:
            _cache_new_thumbnails(db)
        except Exception:
            pass

        # Update status
        _scanner_status.update({
            "last_run": datetime.utcnow().isoformat(),
            "last_keywords": keywords[:max_keywords],
            "last_videos_found": results["total_videos_found"],
            "last_videos_saved": results["total_videos_saved"],
            "last_credits_used": results["total_credits_used"],
            "total_runs": _scanner_status["total_runs"] + 1,
        })

    except Exception as e:
        _scanner_status["error"] = str(e)
        results["errors"].append(str(e))
        logger.error(f"[Scanner] Fatal error: {e}")
    finally:
        _scanner_status["running"] = False
        db.close()

    return results


def scan_with_creative_center(settings: dict) -> dict:
    """Use FREE Creative Center to discover trending keywords, then scan them."""
    db = SessionLocal()
    try:
        from backend.scrapers.tiktok_creative import TikTokCreativeCenter

        cc = TikTokCreativeCenter()
        trending = cc.get_trending_hashtags(region="BR", limit=10)

        log_usage(db, "creative_center", "trending_hashtags",
                 credits=0, videos_found=len(trending))

        if not trending:
            return {"error": "Creative Center nao retornou resultados", "keywords": []}

        # Extract keyword names
        keywords = [t.get("name", "") for t in trending if t.get("name")][:5]

        if not keywords:
            return {"error": "Nenhuma keyword extraida", "keywords": []}

        # Now scan with those keywords
        result = scan_keywords(keywords, settings)
        result["source"] = "creative_center"
        result["trending_found"] = len(trending)
        return result

    except Exception as e:
        logger.error(f"[Scanner CC] Error: {e}")
        return {"error": str(e), "keywords": []}
    finally:
        db.close()


def scan_hashtag(hashtag: str, max_results: int = 30) -> dict:
    """Scan videos by TikTok hashtag. Cost: 1 credit."""
    global _scanner_status
    if _scanner_status["running"]:
        return {"error": "Scanner ja esta rodando", "status": "busy"}

    _scanner_status["running"] = True
    db = SessionLocal()

    try:
        from backend.scrapers.scrapecreators import ScrapeCreatorsAPI
        from backend.models.settings import AppSettings
        from backend.services.sound_predictor import track_sounds_from_videos

        api_key_row = db.query(AppSettings).filter_by(key="scrapecreators_api_key").first()
        if not api_key_row or not api_key_row.value:
            _scanner_status["running"] = False
            return {"error": "ScrapeCreators API key nao configurada"}

        sc = ScrapeCreatorsAPI(api_key_row.value)
        hashtag_clean = hashtag.strip().strip("#")
        logger.info(f"[Scanner] Scanning hashtag: #{hashtag_clean}")

        videos_data = sc.tiktok_hashtag_videos(hashtag_clean, count=min(max_results, 30))
        log_usage(db, "scrapecreators", f"/v1/tiktok/search/hashtag", 1, f"hashtag:{hashtag_clean}")

        saved = 0
        if videos_data:
            saved = video_service.save_videos(db, videos_data)
            track_sounds_from_videos(db, videos_data)

        _scanner_status["running"] = False
        _scanner_status["last_run"] = datetime.now().isoformat()

        return {
            "hashtag": hashtag_clean,
            "videos_found": len(videos_data),
            "videos_saved": saved,
            "credits_used": 1,
        }

    except Exception as e:
        logger.error(f"[Scanner Hashtag] Error: {e}")
        return {"error": str(e)}
    finally:
        _scanner_status["running"] = False
        db.close()


def scan_sound(sound_url: str, max_results: int = 30) -> dict:
    """Scan videos by TikTok sound/music URL. Cost: 1 credit."""
    global _scanner_status
    if _scanner_status["running"]:
        return {"error": "Scanner ja esta rodando", "status": "busy"}

    _scanner_status["running"] = True
    db = SessionLocal()

    try:
        from backend.scrapers.scrapecreators import ScrapeCreatorsAPI
        from backend.models.settings import AppSettings
        from backend.services.sound_predictor import track_sounds_from_videos

        api_key_row = db.query(AppSettings).filter_by(key="scrapecreators_api_key").first()
        if not api_key_row or not api_key_row.value:
            _scanner_status["running"] = False
            return {"error": "ScrapeCreators API key nao configurada"}

        sc = ScrapeCreatorsAPI(api_key_row.value)
        logger.info(f"[Scanner] Scanning sound: {sound_url}")

        videos_data = sc.tiktok_song_videos(sound_url, count=min(max_results, 30))
        log_usage(db, "scrapecreators", f"/v1/tiktok/song/videos", 1, f"sound")

        saved = 0
        if videos_data:
            saved = video_service.save_videos(db, videos_data)
            track_sounds_from_videos(db, videos_data)

        _scanner_status["running"] = False
        _scanner_status["last_run"] = datetime.now().isoformat()

        return {
            "sound_url": sound_url,
            "videos_found": len(videos_data),
            "videos_saved": saved,
            "credits_used": 1,
        }

    except Exception as e:
        logger.error(f"[Scanner Sound] Error: {e}")
        return {"error": str(e)}
    finally:
        _scanner_status["running"] = False
        db.close()


def get_scanner_videos(
    db: Session,
    min_views: int = 0,
    max_views: int = 0,
    min_likes: int = 0,
    min_comments: int = 0,
    min_shares: int = 0,
    min_engagement: float = 0,
    max_engagement: float = 0,
    min_viral_score: float = 0,
    max_viral_score: float = 0,
    viral_level: str = "",
    min_outlier: float = 0,
    min_replicability: float = 0,
    min_duration: int = 0,
    max_duration: int = 0,
    has_product: str = "",
    hashtag_contains: str = "",
    description_contains: str = "",
    creator_tier: str = "",
    min_views_per_follower: float = 0,
    small_creator_viral: bool = False,
    published_within_hours: int = 0,
    min_velocity: float = 0,
    sort_by: str = "viral_score",
    limit: int = 48,
    offset: int = 0,
    platform: str = "tiktok",
    language: str = "",
    category: str = "",
    style: str = "",
) -> tuple[list[dict], int]:
    """Get videos with filters applied. Returns dicts with embed info."""
    from datetime import timedelta

    # Category keywords for filtering by content niche
    CATEGORY_KEYWORDS = {
        "ai_construcao": ["ai house", "ai construction", "ai mansion", "ai building", "ai home", "ai villa", "ai architecture", "ai render", "sora house", "sora construction", "runway house", "casa ia", "construção ia", "mansão ia", "ai generated house", "ai dream house", "ai mega mansion", "ai futuristic house", "ai impossible", "created_by_ai"],
        "construcao": ["construção", "construction", "building", "obra", "fundação", "alicerce", "acabamento", "cimento", "concreto", "tijolo", "estrutura", "house build", "home build"],
        "arquitetura": ["arquitetura", "architecture", "architect", "projeto", "planta", "design interior", "interior design", "modern house", "casa moderna", "mansão", "mansion", "villa", "luxury home"],
        "decoracao": ["decoração", "decor", "decoracao", "home decor", "room makeover", "quarto", "sala", "cozinha", "banheiro", "furniture", "moveis"],
        "diy_reforma": ["diy", "reforma", "renovation", "before after", "antes depois", "makeover", "transformação", "transformation", "restore", "restauração"],
        "lifestyle": ["lifestyle", "rotina", "routine", "dia a dia", "daily", "vlog", "day in my life"],
        "tech": ["tech", "tecnologia", "gadget", "smartphone", "setup", "unboxing tech", "iphone", "samsung"],
        "receitas": ["receita", "recipe", "cooking", "cozinhando", "food", "comida", "bolo", "cake"],
        "fitness": ["fitness", "treino", "workout", "gym", "academia", "exercicio", "muscle", "shape"],
        "moda": ["moda", "fashion", "outfit", "look", "roupa", "style", "estilo", "trend"],
        "beleza": ["beleza", "beauty", "skincare", "maquiagem", "makeup", "hair", "cabelo", "skin"],
        "humor": ["humor", "funny", "comedy", "comedia", "meme", "prank", "challenge"],
        "educacao": ["educação", "education", "tutorial", "como fazer", "how to", "dica", "tip", "hack", "aprenda"],
        "financas": ["financas", "finanças", "finance", "investimento", "investment", "dinheiro", "money", "renda", "trading", "crypto", "bitcoin", "economia"],
        "pets": ["pet", "pets", "cachorro", "dog", "gato", "cat", "animal", "puppy", "kitten", "vet", "adote"],
        "viagem": ["viagem", "travel", "trip", "turismo", "tourism", "destino", "destination", "hotel", "mochilao", "backpack"],
        "automotivo": ["carro", "car", "auto", "moto", "motorcycle", "tuning", "mecanica", "engine", "drift", "supercar", "automotive"],
        "jardinagem": ["jardim", "garden", "planta", "plant", "horta", "flores", "flower", "paisagismo", "landscape", "gardening"],
        "games": ["game", "gaming", "gamer", "gameplay", "playstation", "xbox", "nintendo", "esports", "streamer", "jogos"],
        "musica": ["musica", "music", "song", "guitar", "piano", "singing", "cover", "beat", "producer", "dj", "remix"],
        "arte": ["arte", "art", "drawing", "desenho", "pintura", "painting", "sculpture", "artista", "creative", "illustration"],
        "espiritualidade": ["espiritualidade", "spirituality", "meditacao", "meditation", "yoga", "mindfulness", "energia", "chakra", "cristal", "oracao"],
        "maternidade": ["maternidade", "maternity", "bebe", "baby", "gestante", "pregnant", "mae", "mom", "motherhood", "parenting", "crianca"],
        "empreendedorismo": ["empreendedorismo", "entrepreneurship", "negocio", "business", "startup", "empreender", "renda extra", "side hustle", "marca", "branding"],
        "sustentabilidade": ["sustentabilidade", "sustainability", "eco", "reciclagem", "recycle", "zero waste", "organic", "verde", "green", "meio ambiente"],
        "ciencia": ["ciencia", "science", "experimento", "experiment", "quimica", "fisica", "biology", "nasa", "space", "espaco", "lab"],
        "fotografia": ["fotografia", "photography", "photo", "camera", "lightroom", "retrato", "portrait", "landscape photo", "lens", "dslr"],
        "livros": ["livro", "book", "leitura", "reading", "resenha literaria", "bookstagram", "booktok", "biblioteca", "autor", "writer"],
        "relacionamentos": ["relacionamento", "relationship", "namoro", "dating", "casal", "couple", "amor", "love", "casamento", "wedding"],
        "saude_mental": ["saude mental", "mental health", "ansiedade", "anxiety", "depressao", "depression", "terapia", "therapy", "autoestima", "burnout", "psicologia"],
    }

    # Style keywords for filtering by video format
    STYLE_KEYWORDS = {
        "ai_generated": ["ai generated", "ai render", "ai art", "made with ai", "feito com ia", "sora", "runway", "midjourney", "kling", "pika", "created_by_ai", "ai video", "artificial intelligence", "inteligencia artificial", "gerado por ia"],
        "timelapse": ["timelapse", "time lapse", "time-lapse", "fast forward", "acelerado", "speed build", "speedbuild", "fast motion"],
        "tutorial": ["tutorial", "passo a passo", "step by step", "como fazer", "how to", "aprenda", "learn"],
        "before_after": ["before after", "antes depois", "antes e depois", "before and after", "transformação", "transformation", "makeover"],
        "review": ["review", "resenha", "analise", "testei", "vale a pena", "worth it", "honest review"],
        "unboxing": ["unboxing", "abrindo", "chegou", "haul", "compras", "comprei"],
        "vlog": ["vlog", "daily vlog", "rotina", "dia a dia", "day in my life", "come with me"],
        "satisfying": ["satisfying", "satisfatório", "oddly satisfying", "asmr", "cleaning", "organize"],
        "behind_scenes": ["behind the scenes", "bastidores", "making of", "backstage", "processo", "process"],
        "storytelling": ["storytelling", "história", "story", "storytime", "historia real", "true story"],
        "pov": ["pov", "point of view", "primeira pessoa", "first person"],
    }

    query = db.query(Video)

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

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

    # Category filter - search in title, description, hashtags
    if category and category in CATEGORY_KEYWORDS:
        kws = CATEGORY_KEYWORDS[category]
        conditions = []
        for kw in kws:
            conditions.append(Video.title.ilike(f"%{kw}%"))
            conditions.append(Video.description.ilike(f"%{kw}%"))
            conditions.append(Video.hashtags.cast(String).ilike(f"%{kw}%"))
        query = query.filter(or_(*conditions))

    # Style filter - search in title, description, hashtags
    if style and style in STYLE_KEYWORDS:
        kws = STYLE_KEYWORDS[style]
        conditions = []
        for kw in kws:
            conditions.append(Video.title.ilike(f"%{kw}%"))
            conditions.append(Video.description.ilike(f"%{kw}%"))
            conditions.append(Video.hashtags.cast(String).ilike(f"%{kw}%"))
        query = query.filter(or_(*conditions))

    # Views
    if min_views > 0:
        query = query.filter(Video.views >= min_views)
    if max_views > 0:
        query = query.filter(Video.views <= max_views)

    # Likes
    if min_likes > 0:
        query = query.filter(Video.likes >= min_likes)

    # Comments
    if min_comments > 0:
        query = query.filter(Video.comments >= min_comments)

    # Shares
    if min_shares > 0:
        query = query.filter(Video.shares >= min_shares)

    # Engagement Rate
    if min_engagement > 0:
        query = query.filter(Video.engagement_rate >= min_engagement)
    if max_engagement > 0:
        query = query.filter(Video.engagement_rate <= max_engagement)

    # Viral Score
    if min_viral_score > 0:
        query = query.filter(Video.viral_score >= min_viral_score)
    if max_viral_score > 0:
        query = query.filter(Video.viral_score <= max_viral_score)

    # Viral Level
    if viral_level:
        levels = {
            "mega_viral": (90, 100),
            "viral": (75, 89.9),
            "trending": (60, 74.9),
            "em_alta": (40, 59.9),
            "crescendo": (20, 39.9),
            "normal": (0, 19.9),
        }
        if viral_level in levels:
            low, high = levels[viral_level]
            query = query.filter(Video.viral_score >= low, Video.viral_score <= high)

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

    # Replicability
    if min_replicability > 0:
        query = query.filter(Video.replicability_score >= min_replicability)

    # Duration
    if min_duration > 0:
        query = query.filter(Video.duration >= min_duration)
    if max_duration > 0:
        query = query.filter(Video.duration <= max_duration)

    # Has Product
    if has_product == "yes":
        query = query.filter(
            or_(
                Video.description.like("%shop_product_url%"),
                Video.url.like("%/shop/%"),
            )
        )

    # Hashtag contains
    if hashtag_contains:
        query = query.filter(Video.hashtags.cast(String).ilike(f"%{hashtag_contains}%"))

    # Description contains
    if description_contains:
        query = query.filter(
            or_(
                Video.title.ilike(f"%{description_contains}%"),
                Video.description.ilike(f"%{description_contains}%"),
            )
        )

    # Creator tier
    if creator_tier:
        tiers = {
            "nano": (1, 10_000),
            "micro": (10_000, 100_000),
            "mid": (100_000, 1_000_000),
            "macro": (1_000_000, 10_000_000),
            "mega": (10_000_000, 999_999_999),
        }
        if creator_tier in tiers:
            low, high = tiers[creator_tier]
            query = query.filter(Video.creator_followers >= low, Video.creator_followers < high)

    # Views per follower
    if min_views_per_follower > 0:
        query = query.filter(Video.views_per_follower >= min_views_per_follower)

    # Small creator viral
    if small_creator_viral:
        query = query.filter(
            Video.is_small_creator == True,
            Video.outlier_ratio >= 5,
        )

    # Published within hours
    if published_within_hours > 0:
        since = datetime.utcnow() - timedelta(hours=published_within_hours)
        query = query.filter(Video.published_at >= since)

    # Count total
    total = query.count()

    # Sort
    sort_map = {
        "viral_score": desc(Video.viral_score),
        "views": desc(Video.views),
        "likes": desc(Video.likes),
        "comments": desc(Video.comments),
        "shares": desc(Video.shares),
        "engagement": desc(Video.engagement_rate),
        "outlier": desc(Video.outlier_ratio),
        "replicability": desc(Video.replicability_score),
        "recent": desc(Video.collected_at),
        "collected": desc(Video.collected_at),
        "views_per_follower": desc(Video.views_per_follower),
    }
    order = sort_map.get(sort_by, desc(Video.viral_score))
    videos = query.order_by(order).offset(offset).limit(limit).all()

    # Convert to dicts with embed info + advanced metrics
    from backend.services.video_analyzer import (
        calculate_hook_score, calculate_shareability,
        calculate_engagement_velocity, predict_completion_rate,
        calculate_monetization_potential,
    )

    result = []
    for v in videos:
        d = {
            "id": v.id,
            "platform": v.platform,
            "external_id": v.external_id,
            "url": v.url,
            "embed_id": v.external_id,
            "title": v.title,
            "description": v.description,
            "hashtags": v.hashtags or [],
            "views": v.views,
            "views_formatted": v.views_formatted,
            "likes": v.likes,
            "comments": v.comments,
            "shares": v.shares,
            "duration": v.duration,
            "duration_formatted": v.duration_formatted,
            "thumbnail_url": f"/api/thumbnail/{v.id}",
            "viral_score": v.viral_score,
            "engagement_rate": v.engagement_rate,
            "outlier_ratio": v.outlier_ratio,
            "replicability_score": v.replicability_score,
            "creator_followers": v.creator_followers,
            "views_per_follower": v.views_per_follower,
            "is_small_creator": v.is_small_creator,
            "shop_product_url": v.shop_product_url or "",
            "region": v.region or "",
            "is_ad": v.is_ad,
            "is_paid_partnership": v.is_paid_partnership,
            "is_eligible_for_commission": v.is_eligible_for_commission,
            "with_shop_entry": v.with_shop_entry,
            "is_commerce_music": v.is_commerce_music,
            "created_by_ai": v.created_by_ai,
            "audio_language": v.audio_language or "",
            "published_at": v.published_at.isoformat() if v.published_at else None,
            "collected_at": v.collected_at.isoformat() if v.collected_at else None,
            "viral_label": detector.get_viral_label(v.viral_score),
            "viral_color": detector.get_viral_color(v.viral_score),
            "outlier_label": detector.get_outlier_label(v.outlier_ratio),
            "size_tier": detector.get_size_tier(v.creator_followers),
            "creator": {"username": v.creator.username if v.creator else None} if v.creator else None,
        }

        # Advanced metrics (calculated on the fly, lightweight)
        vdata = {
            "title": v.title or "", "description": v.description or "",
            "views": v.views or 0, "likes": v.likes or 0,
            "comments": v.comments or 0, "shares": v.shares or 0,
            "duration": v.duration or 0, "hashtags": v.hashtags or [],
            "published_at": v.published_at, "platform": v.platform,
            "engagement_rate": v.engagement_rate or 0,
            "shop_product_url": v.shop_product_url or "",
            "outlier_ratio": v.outlier_ratio or 0,
            "creator_followers": v.creator_followers or 0,
            "is_small_creator": v.is_small_creator,
        }
        hook = calculate_hook_score(v.title or "", v.description or "")
        velocity = calculate_engagement_velocity(vdata)
        shareability = calculate_shareability(vdata)

        d["hook_score"] = hook["score"]
        d["hook_type"] = hook["hook_type"]
        d["velocity_score"] = velocity["velocity_score"]
        d["velocity_label"] = velocity["label"]
        d["shareability_score"] = shareability["score"]
        d["shareability_label"] = shareability["label"]

        result.append(d)

    return result, total
