"""
TikTok Shop Intelligence Service - Brazil Focus

Cross-platform viral video + product detection, revenue tracking,
trending products by category, competitor monitoring.
"""

import re
from datetime import datetime, timedelta

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

from backend.models.product import Product
from backend.models.product_snapshot import ProductSnapshot
from backend.models.video import Video


# Brazil TikTok Shop categories with search keywords
BR_CATEGORIES = {
    "moda": {
        "name": "Moda & Acessorios",
        "icon": "shirt",
        "keywords": ["moda feminina", "look barato", "roupas tiktok", "vestido", "conjunto", "blusa"],
        "color": "#ec4899",
    },
    "beleza": {
        "name": "Beleza & Skincare",
        "icon": "sparkles",
        "keywords": ["skincare barato", "maquiagem viral", "rotina beleza", "serum", "protetor solar", "batom"],
        "color": "#f472b6",
    },
    "casa": {
        "name": "Casa & Decoracao",
        "icon": "home",
        "keywords": ["organizador casa", "gadgets cozinha", "decoracao barata", "luminaria", "tapete"],
        "color": "#06b6d4",
    },
    "eletronicos": {
        "name": "Eletronicos & Gadgets",
        "icon": "smartphone",
        "keywords": ["gadgets baratos", "fone bluetooth", "acessorios celular", "smartwatch", "carregador"],
        "color": "#8b5cf6",
    },
    "esportes": {
        "name": "Esportes & Fitness",
        "icon": "dumbbell",
        "keywords": ["legging fitness", "tenis barato", "garrafa termica", "acessorios academia", "yoga"],
        "color": "#10b981",
    },
}


def take_snapshot(db: Session, product_id: str, data: dict) -> ProductSnapshot:
    """Create a snapshot of product metrics."""
    import re as _re
    sold = data.get("sold_count", 0)
    if isinstance(sold, str):
        sold = sold.replace(",", "").replace("+", "").strip()
        nums = _re.findall(r"[\d.]+[KkMm]?", sold)
        if nums:
            val = nums[0]
            if val[-1] in "Kk":
                sold = int(float(val[:-1]) * 1000)
            elif val[-1] in "Mm":
                sold = int(float(val[:-1]) * 1000000)
            else:
                try:
                    sold = int(float(val))
                except ValueError:
                    sold = 0
        else:
            sold = 0

    snapshot = ProductSnapshot(
        product_id=product_id,
        sold_count=int(sold),
        stock=int(data.get("stock", 0) or 0),
        price=str(data.get("price", "")),
        rating=float(data.get("rating", 0) or 0),
        review_count=int(data.get("review_count", 0) or 0),
    )
    db.add(snapshot)
    db.commit()
    db.refresh(snapshot)
    return snapshot


def get_revenue_data(db: Session, product_id: str, days: int = 30) -> dict:
    """Calculate revenue from snapshot deltas."""
    since = datetime.utcnow() - timedelta(days=days)
    snapshots = (
        db.query(ProductSnapshot)
        .filter(
            ProductSnapshot.product_id == product_id,
            ProductSnapshot.captured_at >= since,
        )
        .order_by(ProductSnapshot.captured_at.asc())
        .all()
    )

    if len(snapshots) < 2:
        return {
            "product_id": product_id,
            "snapshots": [s.to_dict() for s in snapshots],
            "daily_sales": [],
            "total_sold": 0,
            "estimated_revenue": 0,
            "estimated_revenue_formatted": "R$ 0,00",
            "avg_daily_sales": 0,
            "velocity": "sem dados",
            "trend": "neutral",
            "days_tracked": 0,
            "current_price": snapshots[0].price if snapshots else "",
            "current_stock": snapshots[0].stock if snapshots else 0,
        }

    first = snapshots[0]
    last = snapshots[-1]
    total_sold = max(0, last.sold_count - first.sold_count)

    # Parse price to float
    price_val = _parse_price(last.price)

    estimated_revenue = total_sold * price_val

    # Days between first and last snapshot
    delta_days = max(1, (last.captured_at - first.captured_at).days)
    avg_daily = total_sold / delta_days if delta_days > 0 else 0

    # Velocity trend (compare last half vs first half)
    mid = len(snapshots) // 2
    if mid > 0 and len(snapshots) > 2:
        first_half_sold = snapshots[mid].sold_count - snapshots[0].sold_count
        second_half_sold = snapshots[-1].sold_count - snapshots[mid].sold_count
        if second_half_sold > first_half_sold * 1.2:
            trend = "accelerating"
            velocity = "acelerando"
        elif second_half_sold < first_half_sold * 0.8:
            trend = "decelerating"
            velocity = "desacelerando"
        else:
            trend = "stable"
            velocity = "estavel"
    else:
        trend = "neutral"
        velocity = "poucos dados"

    # Daily breakdown
    daily_sales = []
    for i in range(1, len(snapshots)):
        delta = snapshots[i].sold_count - snapshots[i - 1].sold_count
        hours = max(1, (snapshots[i].captured_at - snapshots[i - 1].captured_at).total_seconds() / 3600)
        daily_sales.append({
            "date": snapshots[i].captured_at.strftime("%d/%m"),
            "sold": max(0, delta),
            "per_hour": round(max(0, delta) / hours, 1),
            "stock": snapshots[i].stock,
            "price": snapshots[i].price,
        })

    return {
        "product_id": product_id,
        "snapshots": [s.to_dict() for s in snapshots],
        "daily_sales": daily_sales,
        "total_sold": total_sold,
        "estimated_revenue": round(estimated_revenue, 2),
        "estimated_revenue_formatted": _format_currency(estimated_revenue),
        "avg_daily_sales": round(avg_daily, 1),
        "velocity": velocity,
        "trend": trend,
        "days_tracked": delta_days,
        "current_price": last.price,
        "current_stock": last.stock,
    }


def get_top_products_by_velocity(db: Session, limit: int = 20) -> list[dict]:
    """Get products with highest sales velocity (sold delta between snapshots)."""
    since = datetime.utcnow() - timedelta(days=7)

    # Get products that have at least 2 snapshots
    subq = (
        db.query(
            ProductSnapshot.product_id,
            func.min(ProductSnapshot.sold_count).label("min_sold"),
            func.max(ProductSnapshot.sold_count).label("max_sold"),
            func.count(ProductSnapshot.id).label("snap_count"),
        )
        .filter(ProductSnapshot.captured_at >= since)
        .group_by(ProductSnapshot.product_id)
        .having(func.count(ProductSnapshot.id) >= 2)
        .subquery()
    )

    results = (
        db.query(
            Product,
            (subq.c.max_sold - subq.c.min_sold).label("delta_sold"),
        )
        .join(subq, Product.product_id == subq.c.product_id)
        .order_by((subq.c.max_sold - subq.c.min_sold).desc())
        .limit(limit)
        .all()
    )

    return [
        {**p.to_dict(), "delta_sold_7d": delta}
        for p, delta in results
    ]


def detect_shop_videos(videos: list[dict]) -> list[dict]:
    """Filter videos that have shop_product_url (promoting a TikTok Shop product)."""
    shop_videos = []
    for v in videos:
        url = v.get("shop_product_url")
        if url:
            shop_videos.append({
                **v,
                "has_shop_product": True,
                "shop_product_url": url,
            })
    return shop_videos


def cross_platform_detect(sc, keywords: list[str], count: int = 20) -> dict:
    """Search across TikTok for viral videos with shop products.

    Returns videos grouped by: with products and without products.
    """
    all_videos = []
    shop_videos = []
    product_ids_seen = set()

    for kw in keywords[:5]:  # Limit to 5 keywords to save credits
        try:
            # search/top returns shop_product_url
            results = sc.tiktok_search_top(kw, count)
            for v in results:
                all_videos.append(v)
                if v.get("shop_product_url"):
                    shop_videos.append(v)
        except Exception:
            pass

    # Deduplicate
    seen = set()
    unique_all = []
    unique_shop = []

    for v in all_videos:
        eid = v.get("external_id", "")
        if eid and eid not in seen:
            seen.add(eid)
            unique_all.append(v)

    for v in shop_videos:
        eid = v.get("external_id", "")
        if eid and eid not in seen:
            seen.add(eid)
        unique_shop.append(v)

    # Sort by views
    unique_all.sort(key=lambda x: x.get("views", 0), reverse=True)
    unique_shop.sort(key=lambda x: x.get("views", 0), reverse=True)

    return {
        "total_videos": len(unique_all),
        "videos_with_products": len(unique_shop),
        "all_videos": unique_all,
        "shop_videos": unique_shop,
        "keywords_searched": keywords[:5],
    }


def get_monitored_products(db: Session) -> list[dict]:
    """Get all products that are being monitored (have snapshots)."""
    product_ids = (
        db.query(ProductSnapshot.product_id)
        .distinct()
        .all()
    )
    pids = [p[0] for p in product_ids]
    if not pids:
        return []

    products = db.query(Product).filter(Product.product_id.in_(pids)).all()
    result = []
    for p in products:
        latest = (
            db.query(ProductSnapshot)
            .filter(ProductSnapshot.product_id == p.product_id)
            .order_by(ProductSnapshot.captured_at.desc())
            .first()
        )
        prev = (
            db.query(ProductSnapshot)
            .filter(
                ProductSnapshot.product_id == p.product_id,
                ProductSnapshot.id != latest.id if latest else True,
            )
            .order_by(ProductSnapshot.captured_at.desc())
            .first()
        )
        delta = 0
        if latest and prev:
            delta = latest.sold_count - prev.sold_count

        d = p.to_dict()
        d["last_snapshot"] = latest.to_dict() if latest else None
        d["delta_sold"] = delta
        d["is_monitored"] = True
        result.append(d)

    result.sort(key=lambda x: x.get("delta_sold", 0), reverse=True)
    return result


def snapshot_all_monitored(db: Session, sc) -> int:
    """Take snapshots of all monitored products. Returns count of snapshots taken."""
    products = get_monitored_products(db)
    count = 0
    for p in products:
        url = p.get("url", "")
        if not url:
            continue
        try:
            details = sc.tiktok_shop_product(url, get_related_videos=False)
            take_snapshot(db, p["product_id"], {
                "sold_count": details.get("sold_count", 0),
                "stock": details.get("stock", 0),
                "price": details.get("price", ""),
                "rating": details.get("rating", 0),
                "review_count": details.get("review_count", 0),
            })
            count += 1
        except Exception:
            pass
    return count


def _parse_price(price_str: str) -> float:
    """Parse a price string like 'R$49.90', '$12.99', or '$2.23 - 4.93' into float."""
    if not price_str:
        return 0.0
    # Handle range prices: take the first (minimum) price
    price_str = str(price_str).split("-")[0].strip()
    cleaned = re.sub(r"[^\d.,]", "", price_str)
    # Handle Brazilian format (R$ 49,90) vs US format ($49.90)
    if "," in cleaned and "." in cleaned:
        # R$ 1.234,56 -> 1234.56
        cleaned = cleaned.replace(".", "").replace(",", ".")
    elif "," in cleaned:
        cleaned = cleaned.replace(",", ".")
    try:
        return float(cleaned)
    except ValueError:
        return 0.0


def save_product_to_db(db: Session, data: dict, url: str = "", category: str = ""):
    """Save or update a product in the database."""
    pid = str(data.get("product_id", ""))
    if not pid:
        return None
    existing = db.query(Product).filter(Product.product_id == pid).first()
    if existing:
        existing.title = data.get("title", existing.title)
        existing.sold_count = data.get("sold_count", existing.sold_count)
        existing.rating = data.get("rating", existing.rating)
        existing.review_count = data.get("review_count", existing.review_count)
        existing.stock = data.get("stock", existing.stock)
        existing.related_videos = data.get("related_videos", existing.related_videos)
        existing.related_video_count = data.get("related_video_count", existing.related_video_count)
        if category and not existing.category:
            existing.category = category
        db.commit()
        return existing
    product = Product(
        product_id=pid,
        title=data.get("title", ""),
        description=data.get("description", ""),
        image=data.get("image", ""),
        url=data.get("url", url),
        price=str(data.get("price", "")),
        original_price=str(data.get("original_price", "")),
        discount=str(data.get("discount", "")),
        sold_count=data.get("sold_count", 0),
        rating=data.get("rating", 0),
        review_count=data.get("review_count", 0),
        stock=data.get("stock", 0),
        seller_name=data.get("seller_name", ""),
        shop_name=data.get("shop", {}).get("name", "") if isinstance(data.get("shop"), dict) else "",
        category=category,
        related_video_count=data.get("related_video_count", 0),
        related_videos=data.get("related_videos", []),
    )
    db.add(product)
    db.commit()
    return product


def auto_scan_shop_by_niche(db: Session, sc, category_key: str, max_products: int = 3) -> dict:
    """Scan a single niche: search products + fetch details with related videos.

    Credit cost: 2 keywords × 1 credit + up to max_products × 1 credit = ~5 credits.
    """
    from backend.services.api_usage_tracker import log_usage, can_use_credits

    cat = BR_CATEGORIES.get(category_key)
    if not cat:
        return {"error": f"Categoria '{category_key}' nao encontrada"}

    daily_limit = 1000  # Will be overridden by caller
    result = {
        "category": category_key,
        "category_name": cat["name"],
        "keywords_searched": [],
        "products_found": 0,
        "products_saved": 0,
        "videos_linked": 0,
        "credits_used": 0,
        "errors": [],
    }

    import random
    keywords = list(cat["keywords"])
    random.shuffle(keywords)
    search_keywords = keywords[:2]  # Only 2 keywords per cycle to save credits

    all_products = []
    seen_pids = set()

    for kw in search_keywords:
        if not can_use_credits(db, daily_limit):
            result["errors"].append("Limite diario de creditos atingido")
            break

        try:
            products = sc.tiktok_shop_search(kw, 30)
            log_usage(db, "scrapecreators", "/v1/tiktok/shop/search",
                     credits=1, keyword=kw, videos_found=len(products))
            result["credits_used"] += 1
            result["keywords_searched"].append(kw)

            for p in products:
                pid = p.get("product_id", "")
                if pid and pid not in seen_pids:
                    seen_pids.add(pid)
                    all_products.append(p)
        except Exception as e:
            result["errors"].append(f"Search '{kw}': {str(e)}")
            log_usage(db, "scrapecreators", "/v1/tiktok/shop/search",
                     credits=0, keyword=kw, status="error")

    # Sort by sold_count to get the most popular
    all_products.sort(key=lambda x: int(x.get("sold_count", 0) or 0), reverse=True)
    result["products_found"] = len(all_products)

    # Fetch details + related videos for top products only
    for p in all_products[:max_products]:
        if not can_use_credits(db, daily_limit):
            break

        product_url = p.get("url", "")
        if not product_url:
            continue

        try:
            details = sc.tiktok_shop_product(product_url, get_related_videos=True)
            log_usage(db, "scrapecreators", "/v1/tiktok/product",
                     credits=1, keyword=category_key,
                     videos_found=details.get("related_video_count", 0))
            result["credits_used"] += 1

            pid = details.get("product_id", "")
            if pid:
                save_product_to_db(db, details, product_url, category=category_key)
                take_snapshot(db, pid, details)
                result["products_saved"] += 1
                result["videos_linked"] += details.get("related_video_count", 0)
        except Exception as e:
            result["errors"].append(f"Product detail: {str(e)}")

    # Also save remaining products (without full details) for browsing
    min_sold = 100
    for p in all_products[max_products:]:
        sold = int(p.get("sold_count", 0) or 0)
        if sold >= min_sold:
            save_product_to_db(db, p, p.get("url", ""), category=category_key)

    return result


# Rotation state for niche cycling
_niche_rotation_index = 0


def get_next_niche() -> str:
    """Get next niche in rotation order."""
    global _niche_rotation_index
    niches = list(BR_CATEGORIES.keys())
    niche = niches[_niche_rotation_index % len(niches)]
    _niche_rotation_index += 1
    return niche


def get_viral_videos_with_products(db: Session, min_views: int = 10000,
                                    limit: int = 100, offset: int = 0,
                                    region_filter: str = "all",
                                    min_viral_score: float = 0,
                                    min_duration: int = 0,
                                    max_duration: int = 0,
                                    description_contains: str = "",
                                    category: str = "",
                                    style: str = "",
                                    sort_by: str = "views") -> tuple[list[dict], int]:
    """Get viral videos from DB that have shop indicators.
    Includes videos with shop_product_url OR is_commerce_music.
    Default: show ALL regions to maximize results."""
    from sqlalchemy import desc, String

    # Category and style keyword maps (same as viral_scanner)
    CATEGORY_KEYWORDS = {
        "ai_construcao": ["ai house", "ai construction", "ai mansion", "ai building", "ai home", "ai villa", "sora house", "sora construction", "runway house", "casa ia", "construção ia", "ai generated house", "ai dream house", "ai mega mansion", "ai futuristic house", "created_by_ai"],
        "construcao": ["construção", "construction", "building", "obra", "fundação", "acabamento", "house build"],
        "arquitetura": ["arquitetura", "architecture", "modern house", "casa moderna", "mansão", "mansion", "villa", "luxury home"],
        "decoracao": ["decoração", "decor", "home decor", "room makeover", "quarto", "furniture"],
        "diy_reforma": ["diy", "reforma", "renovation", "before after", "antes depois", "makeover", "transformação"],
        "lifestyle": ["lifestyle", "rotina", "routine", "vlog", "day in my life"],
        "tech": ["tech", "tecnologia", "gadget", "smartphone", "setup", "unboxing tech"],
        "receitas": ["receita", "recipe", "cooking", "food", "comida"],
        "fitness": ["fitness", "treino", "workout", "gym", "academia"],
        "moda": ["moda", "fashion", "outfit", "look", "roupa", "style"],
        "beleza": ["beleza", "beauty", "skincare", "maquiagem", "makeup"],
        "humor": ["humor", "funny", "comedy", "meme", "prank"],
        "educacao": ["educação", "education", "tutorial", "como fazer", "how to", "dica"],
    }
    STYLE_KEYWORDS = {
        "ai_generated": ["ai generated", "ai render", "ai art", "sora", "runway", "midjourney", "kling", "pika", "created_by_ai", "ai video", "feito com ia", "gerado por ia"],
        "timelapse": ["timelapse", "time lapse", "time-lapse", "fast forward", "acelerado", "speed build"],
        "tutorial": ["tutorial", "passo a passo", "step by step", "como fazer", "how to"],
        "before_after": ["before after", "antes depois", "antes e depois", "transformação", "makeover"],
        "review": ["review", "resenha", "testei", "vale a pena", "worth it"],
        "unboxing": ["unboxing", "abrindo", "chegou", "haul", "comprei"],
        "satisfying": ["satisfying", "satisfatório", "oddly satisfying", "asmr"],
    }

    query = db.query(Video).filter(
        Video.platform == "tiktok",
        or_(
            and_(Video.shop_product_url != "", Video.shop_product_url.isnot(None)),
            Video.is_commerce_music == True,  # noqa: E712
        ),
    )

    # Filter by region/language
    if region_filter == "br":
        query = query.filter(
            or_(
                Video.region == "BR",
                Video.language == "pt",
                Video.audio_language.like("pt%"),
            )
        )
    elif region_filter and region_filter != "all":
        query = query.filter(Video.region == region_filter.upper())

    if min_views > 0:
        query = query.filter(Video.views >= min_views)

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

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

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

    if category and category in CATEGORY_KEYWORDS:
        conditions = []
        for kw in CATEGORY_KEYWORDS[category]:
            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))

    if style and style in STYLE_KEYWORDS:
        conditions = []
        for kw in STYLE_KEYWORDS[style]:
            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))

    total = query.count()

    sort_map = {
        "views": desc(Video.views),
        "viral_score": desc(Video.viral_score),
        "recent": desc(Video.collected_at),
        "engagement": desc(Video.engagement_rate),
        "likes": desc(Video.likes),
    }
    order = sort_map.get(sort_by, desc(Video.views))
    videos = query.order_by(order).offset(offset).limit(limit).all()

    result = []
    for v in videos:
        result.append({
            "id": v.id,
            "external_id": v.external_id,
            "url": v.url,
            "title": v.title,
            "description": v.description[:200] if v.description else "",
            "views": v.views,
            "views_formatted": v.views_formatted,
            "likes": v.likes,
            "comments": v.comments,
            "shares": v.shares,
            "viral_score": v.viral_score,
            "engagement_rate": v.engagement_rate,
            "thumbnail_url": f"/api/thumbnail/{v.id}",
            "creator_username": v.creator.username if v.creator else "",
            "creator_followers": v.creator_followers,
            "shop_product_url": v.shop_product_url,
            "region": v.region or "",
            "language": v.language or "",
            "audio_language": v.audio_language or "",
            "is_ad": v.is_ad,
            "is_eligible_for_commission": v.is_eligible_for_commission,
            "with_shop_entry": v.with_shop_entry,
            "published_at": v.published_at.isoformat() if v.published_at else None,
            "collected_at": v.collected_at.isoformat() if v.collected_at else None,
        })

    return result, total


def auto_link_videos_to_products(db: Session, sc, max_credits: int = 10) -> dict:
    """Scan videos with shop_product_url, fetch product details, save to products table.

    This automatically populates the Shop with products found in viral videos.
    Cost: 1 credit per unique product URL.
    """
    from backend.services.api_usage_tracker import log_usage, can_use_credits

    result = {
        "videos_with_products": 0,
        "new_products_saved": 0,
        "products_updated": 0,
        "credits_used": 0,
        "errors": [],
    }

    # Get all videos with shop_product_url that we haven't processed yet
    videos = db.query(Video).filter(
        Video.shop_product_url != "",
        Video.shop_product_url.isnot(None),
    ).all()

    result["videos_with_products"] = len(videos)

    # Group by unique product URLs
    product_urls = {}
    for v in videos:
        url = v.shop_product_url.strip()
        if url and url not in product_urls:
            product_urls[url] = {
                "url": url,
                "video_count": 0,
                "top_video_views": 0,
            }
        if url in product_urls:
            product_urls[url]["video_count"] += 1
            product_urls[url]["top_video_views"] = max(
                product_urls[url]["top_video_views"], v.views or 0
            )

    # Sort by most viral videos first
    sorted_urls = sorted(product_urls.values(),
                         key=lambda x: x["top_video_views"], reverse=True)

    # Check which products already exist in DB
    existing_pids = set()
    for p in db.query(Product.url).all():
        if p.url:
            existing_pids.add(p.url.strip())

    credits_used = 0
    for item in sorted_urls:
        if credits_used >= max_credits:
            break
        if not can_use_credits(db, 1000):
            result["errors"].append("Limite diario de creditos atingido")
            break

        url = item["url"]

        # Skip if product already in DB (by URL match)
        if url in existing_pids:
            result["products_updated"] += 1
            continue

        try:
            details = sc.tiktok_shop_product(url, get_related_videos=True)
            log_usage(db, "scrapecreators", "/v1/tiktok/product",
                     credits=1, keyword="auto_link", videos_found=item["video_count"])
            credits_used += 1

            pid = details.get("product_id", "")
            if pid:
                save_product_to_db(db, details, url, category="viral_auto")
                take_snapshot(db, pid, details)
                result["new_products_saved"] += 1
                existing_pids.add(url)
        except Exception as e:
            result["errors"].append(f"Product fetch error: {str(e)[:100]}")

    result["credits_used"] = credits_used
    return result


def get_shop_reports(db: Session, days: int = 30) -> dict:
    """Generate cross-referenced shop reports from local DB data (BR only)."""
    from backend.models.creator import Creator
    from backend.models.collection import VideoCollection  # noqa: F401
    from sqlalchemy import case, desc

    cutoff = datetime.utcnow() - timedelta(days=days)
    br_filter = or_(Video.region == 'BR', Video.language == 'pt')
    base_filter = [Video.platform == 'tiktok', br_filter, Video.published_at >= cutoff]

    # ── KPIs ──
    kpi_row = db.query(
        func.count(Video.id).label('total_videos'),
        func.count(func.distinct(Video.creator_id)).label('total_creators'),
        func.count(case((and_(Video.shop_product_url != '', Video.shop_product_url.isnot(None)), 1))).label('videos_with_product'),
        func.coalesce(func.sum(Video.views), 0).label('total_views'),
        func.coalesce(func.sum(Video.likes), 0).label('total_likes'),
        func.coalesce(func.avg(Video.engagement_rate), 0).label('avg_engagement'),
        func.coalesce(func.avg(Video.viral_score), 0).label('avg_viral_score'),
    ).filter(*base_filter).first()

    total_vids = kpi_row.total_videos or 0
    vids_product = kpi_row.videos_with_product or 0
    kpis = {
        "total_videos": total_vids,
        "total_creators": kpi_row.total_creators or 0,
        "videos_with_product": vids_product,
        "shop_rate": round(vids_product / total_vids * 100, 1) if total_vids > 0 else 0,
        "total_views": int(kpi_row.total_views),
        "total_views_formatted": _format_number(kpi_row.total_views),
        "total_likes": int(kpi_row.total_likes),
        "avg_engagement": round(float(kpi_row.avg_engagement), 2),
        "avg_viral_score": round(float(kpi_row.avg_viral_score), 1),
    }

    # ── Creator x Produto (top creators promoting products) ──
    creator_shop_rows = db.query(
        Creator.id, Creator.username, Creator.display_name, Creator.avatar_url,
        Creator.followers, Creator.size_tier,
        func.count(Video.id).label('shop_videos'),
        func.coalesce(func.sum(Video.views), 0).label('total_views'),
        func.coalesce(func.avg(Video.viral_score), 0).label('avg_viral'),
        func.coalesce(func.avg(Video.engagement_rate), 0).label('avg_eng'),
    ).join(Video, Video.creator_id == Creator.id).filter(
        *base_filter,
        Video.shop_product_url != '',
        Video.shop_product_url.isnot(None),
    ).group_by(Creator.id).order_by(
        func.count(Video.id).desc()
    ).limit(20).all()

    creator_x_produto = [{
        "username": r.username or "",
        "display_name": r.display_name or r.username or "",
        "avatar": r.avatar_url or "",
        "followers": r.followers or 0,
        "followers_formatted": _format_number(r.followers),
        "size_tier": r.size_tier or "",
        "shop_videos": int(r.shop_videos),
        "total_views": int(r.total_views),
        "total_views_formatted": _format_number(r.total_views),
        "avg_viral": round(float(r.avg_viral), 1),
        "avg_eng": round(float(r.avg_eng), 2),
    } for r in creator_shop_rows]

    # ── Nicho Performance (classify BR videos by category) ──
    videos_for_nicho = db.query(
        Video.id, Video.title, Video.description, Video.hashtags,
        Video.views, Video.viral_score, Video.engagement_rate,
        Video.shop_product_url, Video.likes,
    ).filter(*base_filter).all()

    nicho_stats = {}
    for cat_key, cat_kws in CATEGORY_KEYWORDS.items():
        nicho_stats[cat_key] = {
            "category": cat_key,
            "category_name": _NICHO_NAMES.get(cat_key, cat_key),
            "video_count": 0,
            "shop_count": 0,
            "total_views": 0,
            "total_likes": 0,
            "viral_scores": [],
            "eng_rates": [],
        }

    for v in videos_for_nicho:
        text = f"{v.title or ''} {v.description or ''}".lower()
        tags = str(v.hashtags or "").lower()
        search_text = f"{text} {tags}"
        has_shop = bool(v.shop_product_url and v.shop_product_url.strip())

        for cat_key, cat_kws in CATEGORY_KEYWORDS.items():
            if any(kw in search_text for kw in cat_kws):
                s = nicho_stats[cat_key]
                s["video_count"] += 1
                if has_shop:
                    s["shop_count"] += 1
                s["total_views"] += (v.views or 0)
                s["total_likes"] += (v.likes or 0)
                if v.viral_score:
                    s["viral_scores"].append(v.viral_score)
                if v.engagement_rate:
                    s["eng_rates"].append(v.engagement_rate)

    nicho_performance = []
    for s in nicho_stats.values():
        if s["video_count"] > 0:
            nicho_performance.append({
                "category": s["category"],
                "category_name": s["category_name"],
                "video_count": s["video_count"],
                "shop_count": s["shop_count"],
                "shop_rate": round(s["shop_count"] / s["video_count"] * 100, 1),
                "total_views": s["total_views"],
                "total_views_formatted": _format_number(s["total_views"]),
                "avg_viral": round(sum(s["viral_scores"]) / len(s["viral_scores"]), 1) if s["viral_scores"] else 0,
                "avg_eng": round(sum(s["eng_rates"]) / len(s["eng_rates"]), 2) if s["eng_rates"] else 0,
            })
    nicho_performance.sort(key=lambda x: x["total_views"], reverse=True)

    # ── Growth Insights (fastest growing creators) ──
    growth_rows = db.query(
        Creator.id, Creator.username, Creator.display_name, Creator.avatar_url,
        Creator.followers,
        func.count(Video.id).label('video_count'),
        func.coalesce(func.sum(Video.views), 0).label('total_views'),
        func.coalesce(func.avg(Video.viral_score), 0).label('avg_viral'),
        func.count(case((and_(Video.shop_product_url != '', Video.shop_product_url.isnot(None)), 1))).label('shop_count'),
        func.min(Video.published_at).label('first_video'),
    ).join(Video, Video.creator_id == Creator.id).filter(
        *base_filter,
    ).group_by(Creator.id).having(
        func.count(Video.id) >= 2
    ).order_by(desc('total_views')).limit(15).all()

    growth_insights = []
    now = datetime.utcnow()
    for r in growth_rows:
        days_active = max(1, (now - r.first_video).days) if r.first_video else days
        velocity = int(r.total_views) / days_active
        growth_insights.append({
            "username": r.username or "",
            "display_name": r.display_name or r.username or "",
            "avatar": r.avatar_url or "",
            "followers": r.followers or 0,
            "followers_formatted": _format_number(r.followers),
            "video_count": int(r.video_count),
            "total_views": int(r.total_views),
            "total_views_formatted": _format_number(r.total_views),
            "avg_viral": round(float(r.avg_viral), 1),
            "shop_count": int(r.shop_count),
            "velocity": round(velocity),
            "velocity_formatted": _format_number(velocity),
            "days_active": days_active,
        })
    growth_insights.sort(key=lambda x: x["velocity"], reverse=True)

    return {
        "kpis": kpis,
        "creator_x_produto": creator_x_produto,
        "nicho_performance": nicho_performance,
        "growth_insights": growth_insights,
        "days": days,
    }


def _format_number(n) -> str:
    try:
        n = float(n or 0)
        if n >= 1_000_000:
            return f"{n/1_000_000:.1f}M"
        if n >= 1_000:
            return f"{n/1_000:.1f}K"
        return str(int(n))
    except (ValueError, TypeError):
        return "0"


# Nicho display names
_NICHO_NAMES = {
    "ai_construcao": "IA Construcao",
    "construcao": "Construcao",
    "arquitetura": "Arquitetura",
    "decoracao": "Decoracao",
    "diy_reforma": "DIY/Reforma",
    "lifestyle": "Lifestyle",
    "tech": "Tech",
    "receitas": "Receitas",
    "fitness": "Fitness",
    "moda": "Moda",
    "beleza": "Beleza",
    "humor": "Humor",
    "educacao": "Educacao",
}

# Category keywords for nicho classification
CATEGORY_KEYWORDS = {
    "ai_construcao": ["ai house", "ai construction", "ai mansion", "ai building", "sora house", "casa ia", "construção ia"],
    "construcao": ["construção", "construction", "building", "obra", "fundação", "house build"],
    "arquitetura": ["arquitetura", "architecture", "modern house", "casa moderna", "mansão", "mansion"],
    "decoracao": ["decoração", "decor", "home decor", "room makeover", "quarto"],
    "diy_reforma": ["diy", "reforma", "renovation", "antes depois", "makeover"],
    "lifestyle": ["lifestyle", "rotina", "routine", "vlog", "day in my life"],
    "tech": ["tech", "tecnologia", "gadget", "smartphone", "setup"],
    "receitas": ["receita", "recipe", "cooking", "food", "comida"],
    "fitness": ["fitness", "treino", "workout", "gym", "academia", "legging"],
    "moda": ["moda", "fashion", "outfit", "look", "roupa", "vestido", "conjunto"],
    "beleza": ["beleza", "beauty", "skincare", "maquiagem", "makeup", "perfume"],
    "humor": ["humor", "funny", "comedy", "meme", "prank"],
    "educacao": ["educação", "education", "tutorial", "como fazer", "how to"],
}


def _format_currency(value: float) -> str:
    """Format value as Brazilian Real."""
    if value >= 1_000_000:
        return f"R$ {value / 1_000_000:.1f}M"
    if value >= 1_000:
        return f"R$ {value / 1_000:.1f}K"
    return f"R$ {value:.2f}"
