"""
TikTok Shop Multi-Account Monitor PRO.

Track multiple TikTok Shop accounts: profile, videos, shop products.
Cost per refresh: ~3 ScrapeCreators credits + yt-dlp (free).
Rich analytics: engagement, hook strength, shareability, commerce, cadence, AI reports.
"""

import re
import logging
from datetime import datetime, timedelta

from sqlalchemy.orm import Session

from backend.models.tiktok_shop_account import TikTokShopAccount, TikTokShopAccountSnapshot
from backend.models.product import Product
from backend.models.product_snapshot import ProductSnapshot

logger = logging.getLogger(__name__)


# ─── Username helpers ───────────────────────────────────────────


def normalize_username(input_str: str) -> str:
    """Extract username from '@user', 'https://tiktok.com/@user', etc."""
    s = input_str.strip()
    match = re.search(r"tiktok\.com/@([^/?#\s]+)", s)
    if match:
        return match.group(1).strip()
    s = s.lstrip("@").strip()
    s = re.split(r"[\s/]", s)[0]
    return s


# ─── Account management ────────────────────────────────────────


def add_account(db: Session, input_str: str, category: str = "", notes: str = "") -> dict:
    """Add a TikTok Shop account to monitor."""
    username = normalize_username(input_str)
    if not username:
        return {"status": "error", "message": "Username invalido"}

    existing = db.query(TikTokShopAccount).filter(TikTokShopAccount.username == username).first()
    if existing:
        return {"status": "exists", "account": existing.to_dict()}

    account = TikTokShopAccount(username=username, category=category, notes=notes)
    db.add(account)
    db.commit()
    db.refresh(account)

    try:
        refresh_account(db, account.id)
    except Exception as e:
        logger.warning(f"Initial refresh failed for {username}: {e}")

    return {"status": "added", "account": account.to_dict()}


def add_accounts_bulk(db: Session, input_text: str, category: str = "") -> dict:
    """Parse text with multiple usernames and add each one."""
    raw = re.split(r"[,\n;\s]+", input_text.strip())
    usernames = [normalize_username(r) for r in raw if r.strip()]
    usernames = [u for u in usernames if u]

    added, skipped, errors = [], [], []
    for username in usernames:
        try:
            existing = db.query(TikTokShopAccount).filter(TikTokShopAccount.username == username).first()
            if existing:
                skipped.append(username)
                continue
            account = TikTokShopAccount(username=username, category=category)
            db.add(account)
            db.commit()
            db.refresh(account)
            added.append(username)
            try:
                refresh_account(db, account.id)
            except Exception:
                pass
        except Exception as e:
            db.rollback()
            errors.append({"username": username, "error": str(e)})

    return {"added": added, "skipped": skipped, "errors": errors}


def remove_account(db: Session, account_id: int) -> bool:
    """Remove account and its snapshots."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return False
    db.query(TikTokShopAccountSnapshot).filter(
        TikTokShopAccountSnapshot.account_id == account_id
    ).delete()
    db.delete(account)
    db.commit()
    return True


def list_accounts(db: Session, active_only: bool = True) -> list[dict]:
    """List monitored accounts."""
    q = db.query(TikTokShopAccount)
    if active_only:
        q = q.filter(TikTokShopAccount.is_active == True)  # noqa: E712
    accounts = q.order_by(TikTokShopAccount.total_sold.desc()).all()
    return [a.to_dict() for a in accounts]


def get_account(db: Session, account_id: int) -> dict | None:
    """Get full account details."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    return account.to_dict() if account else None


# ─── Data collection (refresh) ─────────────────────────────────


def _fetch_profile_data(sc, username: str) -> dict:
    """Fetch profile data via ScrapeCreators. Cost: 1 credit."""
    try:
        profile = sc.tiktok_profile(username)
        user = profile.get("user", profile)
        stats = profile.get("stats", user.get("stats", {}))
        return {
            "display_name": user.get("nickname", ""),
            "avatar": user.get("avatarThumb", "") or user.get("avatarMedium", ""),
            "bio": user.get("signature", ""),
            "followers": int(stats.get("followerCount", 0) or user.get("followerCount", 0) or 0),
            "following": int(stats.get("followingCount", 0) or 0),
            "total_likes": int(stats.get("heartCount", 0) or stats.get("heart", 0) or 0),
            "video_count": int(stats.get("videoCount", 0) or 0),
        }
    except Exception as e:
        logger.warning(f"Profile fetch failed for {username}: {e}")
        return {}


def _fetch_video_data(username: str) -> dict:
    """Fetch video data via yt-dlp. FREE."""
    try:
        from backend.scrapers.tiktok import TikTokScraper
        scraper = TikTokScraper()
        videos = scraper.get_user_videos(username, max_results=20)

        if not videos:
            return {}

        views_list = [int(v.get("views", 0) or 0) for v in videos]
        likes_list = [int(v.get("likes", 0) or 0) for v in videos]
        comments_list = [int(v.get("comments", 0) or 0) for v in videos]
        shares_list = [int(v.get("shares", 0) or 0) for v in videos]

        total_views = sum(views_list)
        n = max(1, len(videos))

        sorted_videos = sorted(videos, key=lambda v: int(v.get("views", 0) or 0), reverse=True)
        recent = []
        for v in sorted_videos[:5]:
            recent.append({
                "title": (v.get("title") or v.get("description") or "")[:100],
                "views": int(v.get("views", 0) or 0),
                "likes": int(v.get("likes", 0) or 0),
                "comments": int(v.get("comments", 0) or 0),
                "url": v.get("url") or v.get("video_url") or "",
                "thumbnail": v.get("thumbnail_url") or v.get("thumbnail") or "",
            })

        return {
            "total_views": total_views,
            "avg_views": round(total_views / n, 1),
            "avg_likes": round(sum(likes_list) / n, 1),
            "avg_comments": round(sum(comments_list) / n, 1),
            "avg_shares": round(sum(shares_list) / n, 1),
            "recent_videos": recent,
        }
    except Exception as e:
        logger.warning(f"Video fetch failed for {username}: {e}")
        return {}


def _fetch_rich_video_data(sc, username: str) -> list[dict]:
    """Fetch 30 videos via ScrapeCreators with rich metadata. Cost: 1 credit."""
    try:
        videos = sc.tiktok_user_videos(username, 30, sort_by="popular")
        return videos or []
    except Exception as e:
        logger.warning(f"Rich video fetch failed for {username}: {e}")
        return []


def _compute_account_analytics(videos: list[dict], account: TikTokShopAccount) -> dict:
    """Compute ALL derived analytics from rich video data."""
    from backend.services.competitor_spy import (
        analyze_post_cadence, analyze_hashtags, analyze_content_formats
    )
    from backend.services.video_analyzer import (
        calculate_hook_score, calculate_shareability, calculate_monetization_potential
    )

    if not videos:
        return {}

    n = max(1, len(videos))

    # --- Engagement rate ---
    eng_rates = []
    total_shares = 0
    for v in videos:
        views = int(v.get("views", 0) or v.get("playCount", 0) or 0)
        likes = int(v.get("likes", 0) or v.get("diggCount", 0) or 0)
        comments = int(v.get("comments", 0) or v.get("commentCount", 0) or 0)
        shares = int(v.get("shares", 0) or v.get("shareCount", 0) or 0)
        total_shares += shares
        if views > 0:
            eng_rates.append(((likes + comments + shares) / views) * 100)
    avg_engagement_rate = round(sum(eng_rates) / max(1, len(eng_rates)), 2)

    # --- Hook strength ---
    hook_scores = []
    for v in videos:
        title = v.get("title", "") or v.get("description", "") or v.get("desc", "") or ""
        hs = calculate_hook_score(title)
        hook_scores.append(hs.get("score", 0))
    avg_hook_strength = round(sum(hook_scores) / max(1, len(hook_scores)), 1)

    # --- Shareability ---
    share_scores = []
    for v in videos:
        vdata = {
            "views": int(v.get("views", 0) or 0),
            "likes": int(v.get("likes", 0) or 0),
            "comments": int(v.get("comments", 0) or 0),
            "shares": int(v.get("shares", 0) or 0),
            "title": v.get("title", "") or v.get("description", "") or "",
            "hashtags": v.get("hashtags", []),
            "duration": int(v.get("duration", 0) or 0),
        }
        ss = calculate_shareability(vdata)
        share_scores.append(ss.get("score", 0))
    avg_shareability = round(sum(share_scores) / max(1, len(share_scores)), 1)

    # --- Cadence ---
    cadence = analyze_post_cadence(videos)

    # --- Hashtags ---
    hashtags_data = analyze_hashtags(videos)

    # --- Content formats ---
    formats = analyze_content_formats(videos)
    fmt = formats.get("formats", {})
    total_classified = max(1, fmt.get("short", 0) + fmt.get("medium", 0) + fmt.get("long", 0))

    # --- Commerce metrics ---
    commerce_count = 0
    commission_eligible = 0
    for v in videos:
        shop_url = v.get("shop_product_url", "")
        if shop_url:
            commerce_count += 1
        if v.get("is_eligible_for_commission"):
            commission_eligible += 1

    commerce_ratio = round(commerce_count / n * 100, 1)

    # --- Revenue derived ---
    video_count = account.video_count or n
    revenue_per_video = round(account.estimated_revenue / max(1, video_count), 2)
    follower_to_sale = round(account.followers / max(1, account.total_sold), 1) if account.total_sold > 0 else 0

    # --- Top hashtags for storage (top 20) ---
    top_tags = hashtags_data.get("top_hashtags", [])[:20]

    return {
        "avg_engagement_rate": avg_engagement_rate,
        "avg_hook_strength": avg_hook_strength,
        "avg_shareability": avg_shareability,
        "total_shares": total_shares,
        "posts_per_week": cadence.get("posts_per_week", 0),
        "best_posting_days": cadence.get("best_days", []),
        "best_posting_hours": cadence.get("best_hours", []),
        "pct_short_videos": round(fmt.get("short", 0) / total_classified * 100, 1),
        "pct_medium_videos": round(fmt.get("medium", 0) / total_classified * 100, 1),
        "pct_long_videos": round(fmt.get("long", 0) / total_classified * 100, 1),
        "avg_duration": formats.get("avg_duration", 0),
        "commerce_video_count": commerce_count,
        "commerce_ratio": commerce_ratio,
        "commission_eligible_count": commission_eligible,
        "top_hashtags": top_tags,
        "avg_hashtags_per_post": hashtags_data.get("avg_tags_per_post", 0),
        "revenue_per_video": revenue_per_video,
        "follower_to_sale_ratio": follower_to_sale,
    }


def _fetch_shop_data(sc, username: str) -> dict:
    """Fetch shop data via ScrapeCreators. Cost: 1 credit."""
    try:
        store_url = f"https://www.tiktok.com/@{username}/shop"
        result = sc.tiktok_shop_products(store_url)
        shop_info = result.get("shop_info", {})
        products = result.get("products", [])

        if not shop_info.get("shop_name") and not products:
            return {"has_shop": False}

        total_sold = int(shop_info.get("sold_count", 0) or 0)
        revenue = _compute_estimated_revenue(products)

        prices = []
        for p in products:
            price_str = str(p.get("price", ""))
            nums = re.findall(r"[\d.]+", price_str)
            if nums:
                try:
                    prices.append(float(nums[0]))
                except ValueError:
                    pass

        return {
            "has_shop": True,
            "shop_name": shop_info.get("shop_name", ""),
            "shop_logo": shop_info.get("shop_logo", ""),
            "seller_id": shop_info.get("seller_id", ""),
            "shop_rating": float(shop_info.get("shop_rating", 0) or 0),
            "shop_region": shop_info.get("region", ""),
            "total_products": int(shop_info.get("on_sell_product_count", 0) or len(products)),
            "total_sold": total_sold,
            "total_reviews": int(shop_info.get("review_count", 0) or 0),
            "shop_followers": int(shop_info.get("followers_count", 0) or 0),
            "estimated_revenue": revenue,
            "avg_product_price": round(sum(prices) / max(1, len(prices)), 2) if prices else 0,
            "products": products,
        }
    except Exception as e:
        logger.warning(f"Shop fetch failed for {username}: {e}")
        return {}


def _compute_estimated_revenue(products: list[dict]) -> float:
    """Estimate revenue: sum(sold_count * price) per product."""
    total = 0.0
    for p in products:
        sold = int(p.get("sold_count", 0) or 0)
        price_str = str(p.get("price", ""))
        nums = re.findall(r"[\d.]+", price_str)
        if nums:
            try:
                price = float(nums[0])
                total += sold * price
            except ValueError:
                pass
    return round(total, 2)


def refresh_account(db: Session, account_id: int) -> dict:
    """Full refresh of an account: profile + videos + shop + rich analytics."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    username = account.username
    errors = []
    rich_videos = []

    # 1. Try ScrapeCreators for profile + shop
    sc = _get_sc()
    if sc:
        profile_data = _fetch_profile_data(sc, username)
        if profile_data:
            account.display_name = profile_data.get("display_name") or account.display_name
            account.avatar = profile_data.get("avatar") or account.avatar
            account.bio = profile_data.get("bio") or account.bio
            account.followers = profile_data.get("followers", account.followers)
            account.following = profile_data.get("following", account.following)
            account.total_likes = profile_data.get("total_likes", account.total_likes)
            account.video_count = profile_data.get("video_count", account.video_count)
        else:
            errors.append("profile_fetch_failed")

        shop_data = _fetch_shop_data(sc, username)
        if shop_data:
            account.has_shop = shop_data.get("has_shop", False)
            if account.has_shop:
                account.shop_name = shop_data.get("shop_name") or account.shop_name
                account.shop_logo = shop_data.get("shop_logo") or account.shop_logo
                account.seller_id = shop_data.get("seller_id") or account.seller_id
                account.shop_rating = shop_data.get("shop_rating", account.shop_rating)
                account.shop_region = shop_data.get("shop_region") or account.shop_region
                account.total_products = shop_data.get("total_products", account.total_products)
                account.total_sold = shop_data.get("total_sold", account.total_sold)
                account.total_reviews = shop_data.get("total_reviews", account.total_reviews)
                account.shop_followers = shop_data.get("shop_followers", account.shop_followers)
                account.estimated_revenue = shop_data.get("estimated_revenue", account.estimated_revenue)
                account.avg_product_price = shop_data.get("avg_product_price", account.avg_product_price)
                _save_shop_products(db, account, shop_data.get("products", []))
        else:
            errors.append("shop_fetch_failed")

        # 2.5. Rich video data via ScrapeCreators (1 extra credit)
        rich_videos = _fetch_rich_video_data(sc, username)
        if not rich_videos:
            errors.append("rich_video_fetch_failed")
    else:
        errors.append("scrapecreators_not_configured")

    # 2. yt-dlp for videos (FREE) - basic video aggregates
    video_data = _fetch_video_data(username)
    if video_data:
        account.total_views = video_data.get("total_views", account.total_views)
        account.avg_views = video_data.get("avg_views", account.avg_views)
        account.avg_likes = video_data.get("avg_likes", account.avg_likes)
        account.avg_comments = video_data.get("avg_comments", account.avg_comments)
        account.avg_shares = video_data.get("avg_shares", account.avg_shares)
        account.recent_videos = video_data.get("recent_videos", account.recent_videos)
    else:
        errors.append("video_fetch_failed")

    # 3. Compute rich analytics from ScrapeCreators video data
    if rich_videos:
        analytics = _compute_account_analytics(rich_videos, account)
        if analytics:
            account.avg_engagement_rate = analytics.get("avg_engagement_rate", 0)
            account.avg_hook_strength = analytics.get("avg_hook_strength", 0)
            account.avg_shareability = analytics.get("avg_shareability", 0)
            account.total_shares = analytics.get("total_shares", 0)
            account.posts_per_week = analytics.get("posts_per_week", 0)
            account.best_posting_days = analytics.get("best_posting_days", [])
            account.best_posting_hours = analytics.get("best_posting_hours", [])
            account.pct_short_videos = analytics.get("pct_short_videos", 0)
            account.pct_medium_videos = analytics.get("pct_medium_videos", 0)
            account.pct_long_videos = analytics.get("pct_long_videos", 0)
            account.avg_duration = analytics.get("avg_duration", 0)
            account.commerce_video_count = analytics.get("commerce_video_count", 0)
            account.commerce_ratio = analytics.get("commerce_ratio", 0)
            account.commission_eligible_count = analytics.get("commission_eligible_count", 0)
            account.top_hashtags = analytics.get("top_hashtags", [])
            account.avg_hashtags_per_post = analytics.get("avg_hashtags_per_post", 0)
            account.revenue_per_video = analytics.get("revenue_per_video", 0)
            account.follower_to_sale_ratio = analytics.get("follower_to_sale_ratio", 0)

        # Store full videos (compact for JSON storage)
        account.full_videos = _compact_videos(rich_videos)

    # Update status
    account.last_checked = datetime.utcnow()
    account.last_error = "; ".join(errors) if errors else ""
    db.commit()

    # Take snapshot (with new fields)
    snap = TikTokShopAccountSnapshot(
        account_id=account.id,
        followers=account.followers,
        total_likes=account.total_likes,
        video_count=account.video_count,
        total_views=account.total_views,
        avg_views=account.avg_views,
        total_products=account.total_products,
        total_sold=account.total_sold,
        total_reviews=account.total_reviews,
        estimated_revenue=account.estimated_revenue,
        avg_engagement_rate=account.avg_engagement_rate,
        commerce_video_count=account.commerce_video_count,
        total_shares=account.total_shares,
        avg_hook_strength=account.avg_hook_strength,
        avg_likes=account.avg_likes,
        avg_shares=account.avg_shares,
        shop_followers=account.shop_followers,
        avg_product_price=account.avg_product_price,
        posts_per_week=account.posts_per_week,
    )
    db.add(snap)
    db.commit()

    return account.to_dict()


def _compact_videos(videos: list[dict]) -> list[dict]:
    """Compact video data for JSON storage (keep essential fields only)."""
    compact = []
    for v in videos:
        compact.append({
            "id": v.get("external_id", ""),
            "title": (v.get("title", "") or v.get("description", ""))[:200],
            "url": v.get("url", ""),
            "thumbnail": v.get("thumbnail_url", "") or v.get("cover", ""),
            "views": int(v.get("views", 0) or 0),
            "likes": int(v.get("likes", 0) or 0),
            "comments": int(v.get("comments", 0) or 0),
            "shares": int(v.get("shares", 0) or 0),
            "duration": int(v.get("duration", 0) or 0),
            "hashtags": (v.get("hashtags", []) or [])[:10],
            "shop_product_url": v.get("shop_product_url", ""),
            "is_ad": v.get("is_ad", False),
            "is_paid_partnership": v.get("is_paid_partnership", False),
            "is_eligible_for_commission": v.get("is_eligible_for_commission", False),
            "with_shop_entry": v.get("with_shop_entry", False),
            "is_commerce_music": v.get("is_commerce_music", False),
            "created_by_ai": v.get("created_by_ai", False),
            "region": v.get("region", ""),
            "audio_language": v.get("audio_language", ""),
            "published_at": v.get("published_at").isoformat() if isinstance(v.get("published_at"), datetime) else str(v.get("published_at", "")),
            "music": v.get("music"),
            "transcript": v.get("transcript", ""),
        })
    return compact


def _save_shop_products(db: Session, account: TikTokShopAccount, products: list[dict]):
    """Save/update Product records and take ProductSnapshot for each."""
    for p in products:
        pid = str(p.get("product_id", ""))
        if not pid:
            continue

        existing = db.query(Product).filter(Product.product_id == pid).first()
        if existing:
            existing.sold_count = int(p.get("sold_count", 0) or 0)
            existing.rating = float(p.get("rating", 0) or 0)
            existing.review_count = int(p.get("review_count", 0) or 0)
            existing.price = str(p.get("price", ""))
            existing.title = p.get("title") or existing.title
            existing.image = p.get("image") or existing.image
            existing.seller_name = account.username
            existing.shop_name = account.shop_name
            existing.updated_at = datetime.utcnow()
        else:
            prod = Product(
                platform="tiktok_shop",
                product_id=pid,
                title=p.get("title", ""),
                image=p.get("image", ""),
                url=p.get("url", ""),
                price=str(p.get("price", "")),
                original_price=str(p.get("original_price", "")),
                discount=str(p.get("discount", "")),
                sold_count=int(p.get("sold_count", 0) or 0),
                rating=float(p.get("rating", 0) or 0),
                review_count=int(p.get("review_count", 0) or 0),
                seller_name=account.username,
                shop_name=account.shop_name,
            )
            db.add(prod)

        snap = ProductSnapshot(
            product_id=pid,
            sold_count=int(p.get("sold_count", 0) or 0),
            price=str(p.get("price", "")),
            rating=float(p.get("rating", 0) or 0),
            review_count=int(p.get("review_count", 0) or 0),
        )
        db.add(snap)

    try:
        db.commit()
    except Exception:
        db.rollback()


def refresh_all_accounts(db: Session) -> dict:
    """Refresh all active accounts."""
    accounts = db.query(TikTokShopAccount).filter(TikTokShopAccount.is_active == True).all()  # noqa: E712
    results = {"success": 0, "errors": 0, "total": len(accounts)}

    for account in accounts:
        try:
            result = refresh_account(db, account.id)
            if "error" in result:
                results["errors"] += 1
            else:
                results["success"] += 1
        except Exception as e:
            db.rollback()
            results["errors"] += 1
            logger.warning(f"Error refreshing {account.username}: {e}")

    return results


# ─── Dashboard & growth ────────────────────────────────────────


def get_dashboard_summary(db: Session) -> dict:
    """Aggregated summary across all accounts (8 metrics)."""
    accounts = db.query(TikTokShopAccount).filter(TikTokShopAccount.is_active == True).all()  # noqa: E712

    n = max(1, len(accounts))
    total_followers = sum(a.followers for a in accounts)
    total_sold = sum(a.total_sold for a in accounts)
    total_revenue = sum(a.estimated_revenue for a in accounts)
    total_views = sum(a.total_views for a in accounts)
    total_products = sum(a.total_products for a in accounts)

    eng_rates = [a.avg_engagement_rate for a in accounts if a.avg_engagement_rate > 0]
    avg_engagement = round(sum(eng_rates) / max(1, len(eng_rates)), 2) if eng_rates else 0

    commerce_ratios = [a.commerce_ratio for a in accounts if a.commerce_ratio > 0]
    avg_commerce_ratio = round(sum(commerce_ratios) / max(1, len(commerce_ratios)), 1) if commerce_ratios else 0

    avg_revenue_per_account = round(total_revenue / n, 2)

    return {
        "total_accounts": len(accounts),
        "total_followers": total_followers,
        "total_sold": total_sold,
        "total_revenue": round(total_revenue, 2),
        "total_views": total_views,
        "total_products": total_products,
        "avg_engagement": avg_engagement,
        "avg_commerce_ratio": avg_commerce_ratio,
        "avg_revenue_per_account": avg_revenue_per_account,
        "accounts": [a.to_dict() for a in accounts],
    }


def get_account_growth(db: Session, account_id: int, days: int = 30) -> dict:
    """Get growth data (snapshots + deltas) for charts."""
    since = datetime.utcnow() - timedelta(days=days)
    snapshots = (
        db.query(TikTokShopAccountSnapshot)
        .filter(
            TikTokShopAccountSnapshot.account_id == account_id,
            TikTokShopAccountSnapshot.captured_at >= since,
        )
        .order_by(TikTokShopAccountSnapshot.captured_at.asc())
        .all()
    )

    if len(snapshots) < 2:
        return {"snapshots": [s.to_dict() for s in snapshots], "growth": {}}

    first, last = snapshots[0], snapshots[-1]
    days_delta = max(1, (last.captured_at - first.captured_at).days)

    follower_growth = last.followers - first.followers
    sold_growth = last.total_sold - first.total_sold
    revenue_growth = last.estimated_revenue - first.estimated_revenue
    views_growth = last.total_views - first.total_views
    eng_growth = round(last.avg_engagement_rate - first.avg_engagement_rate, 2)
    hook_growth = round(last.avg_hook_strength - first.avg_hook_strength, 1)
    commerce_growth = last.commerce_video_count - first.commerce_video_count

    return {
        "snapshots": [s.to_dict() for s in snapshots],
        "growth": {
            "followers": follower_growth,
            "followers_per_day": round(follower_growth / days_delta, 1),
            "followers_percent": round(follower_growth / max(1, first.followers) * 100, 2),
            "sold": sold_growth,
            "sold_per_day": round(sold_growth / days_delta, 1),
            "revenue": round(revenue_growth, 2),
            "revenue_per_day": round(revenue_growth / days_delta, 2),
            "views": views_growth,
            "views_per_day": round(views_growth / days_delta, 1),
            "engagement_delta": eng_growth,
            "hook_strength_delta": hook_growth,
            "commerce_videos_delta": commerce_growth,
            "days_tracked": days_delta,
        },
    }


def get_account_products(db: Session, account_id: int, sort_by: str = "sold_count", limit: int = 50) -> list[dict]:
    """Get products linked to an account."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return []

    q = db.query(Product).filter(
        Product.seller_name == account.username,
        Product.platform == "tiktok_shop",
    )

    if sort_by == "rating":
        q = q.order_by(Product.rating.desc())
    elif sort_by == "price":
        q = q.order_by(Product.price.desc())
    elif sort_by == "review_count":
        q = q.order_by(Product.review_count.desc())
    else:
        q = q.order_by(Product.sold_count.desc())

    products = q.limit(limit).all()
    return [p.to_dict() for p in products]


def compare_accounts(db: Session, account_ids: list[int]) -> dict:
    """Compare multiple accounts side by side with chart data (including new metrics)."""
    accounts = db.query(TikTokShopAccount).filter(TikTokShopAccount.id.in_(account_ids)).all()
    if not accounts:
        return {"accounts": [], "chart_data": {}}

    since = datetime.utcnow() - timedelta(days=30)
    chart_data = {}

    for acc in accounts:
        snapshots = (
            db.query(TikTokShopAccountSnapshot)
            .filter(
                TikTokShopAccountSnapshot.account_id == acc.id,
                TikTokShopAccountSnapshot.captured_at >= since,
            )
            .order_by(TikTokShopAccountSnapshot.captured_at.asc())
            .all()
        )
        chart_data[acc.username] = {
            "labels": [s.captured_at.strftime("%d/%m") for s in snapshots],
            "followers": [s.followers for s in snapshots],
            "sold": [s.total_sold for s in snapshots],
            "revenue": [s.estimated_revenue for s in snapshots],
            "views": [s.total_views for s in snapshots],
            "engagement": [s.avg_engagement_rate for s in snapshots],
            "hook_strength": [s.avg_hook_strength for s in snapshots],
            "commerce_videos": [s.commerce_video_count for s in snapshots],
        }

    return {
        "accounts": [a.to_dict() for a in accounts],
        "chart_data": chart_data,
    }


# ─── NEW: Content Analysis ────────────────────────────────────


def get_account_content_analysis(db: Session, account_id: int) -> dict:
    """Full content analysis: cadence, hashtags, formats, top content."""
    from backend.services.competitor_spy import (
        analyze_post_cadence, analyze_hashtags, analyze_content_formats, get_top_content
    )

    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    videos = account.full_videos or []
    if not videos:
        return {
            "cadence": {"posts_per_week": 0, "best_days": [], "best_hours": [], "cadence_heatmap": {}},
            "hashtags": {"total_unique_tags": 0, "top_hashtags": [], "cloud": []},
            "formats": {"formats": {}, "avg_duration": 0},
            "top_content": [],
        }

    cadence = analyze_post_cadence(videos)
    hashtags = analyze_hashtags(videos)
    formats = analyze_content_formats(videos)
    top_content = get_top_content(videos, 10)

    return {
        "cadence": cadence,
        "hashtags": hashtags,
        "formats": formats,
        "top_content": top_content,
    }


# ─── NEW: AI Report ───────────────────────────────────────────


def get_account_ai_report(db: Session, account_id: int, force: bool = False) -> dict:
    """Generate AI strategic report (cached 24h, uses local Ollama)."""
    import asyncio

    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    # Check cache (24h)
    if not force and account.ai_report and account.ai_report_generated_at:
        if (datetime.utcnow() - account.ai_report_generated_at).total_seconds() < 86400:
            return account.ai_report

    videos = account.full_videos or []

    comp_data = {
        "username": account.username,
        "display_name": account.display_name,
        "platform": "tiktok",
        "followers": account.followers,
        "video_count": account.video_count,
        "total_likes": account.total_likes,
        "bio": account.bio,
    }

    try:
        from backend.services.competitor_spy import generate_ai_report
        loop = asyncio.new_event_loop()
        report = loop.run_until_complete(generate_ai_report(comp_data, videos))
        loop.close()
    except Exception as e:
        logger.warning(f"AI report generation failed: {e}")
        report = {"report": "Erro ao gerar relatorio. Verifique se o Ollama esta rodando.", "metrics": {}}

    # Cache
    account.ai_report = report
    account.ai_report_generated_at = datetime.utcnow()
    db.commit()

    return report


# ─── NEW: Revenue Detail ──────────────────────────────────────


def get_account_revenue_detail(db: Session, account_id: int) -> dict:
    """Detailed revenue: ad revenue + shop revenue + combined."""
    from backend.services.competitor_spy import estimate_revenue

    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    videos = account.full_videos or []
    comp_data = {
        "username": account.username,
        "followers": account.followers,
        "total_likes": account.total_likes,
        "platform": "tiktok",
    }

    ad_revenue = estimate_revenue(comp_data, videos)

    shop_revenue = account.estimated_revenue
    revenue_per_video = account.revenue_per_video

    return {
        "shop_revenue": shop_revenue,
        "revenue_per_video": revenue_per_video,
        "ad_revenue": ad_revenue.get("ad_revenue", {}),
        "sponsorship_per_post": ad_revenue.get("sponsorship_per_post", {}),
        "estimated_sponsored_monthly": ad_revenue.get("estimated_sponsored_monthly", {}),
        "monthly_views": ad_revenue.get("estimated_monthly_views", 0),
        "posts_per_month": ad_revenue.get("posts_per_month", 0),
        "tier": ad_revenue.get("tier", "nano"),
        "combined_monthly": {
            "low": round(shop_revenue + ad_revenue.get("ad_revenue", {}).get("low", 0), 2),
            "mid": round(shop_revenue + ad_revenue.get("ad_revenue", {}).get("mid", 0), 2),
            "high": round(shop_revenue + ad_revenue.get("ad_revenue", {}).get("high", 0), 2),
        },
    }


# ─── NEW: Viral Videos ────────────────────────────────────────


def get_account_viral_videos(db: Session, account_id: int) -> dict:
    """All videos ranked by composite viral score."""
    from backend.services.video_analyzer import (
        calculate_hook_score, calculate_shareability, calculate_monetization_potential
    )

    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    videos = account.full_videos or []
    if not videos:
        return {"videos": [], "total": 0}

    scored = []
    for v in videos:
        views = int(v.get("views", 0) or 0)
        likes = int(v.get("likes", 0) or 0)
        comments = int(v.get("comments", 0) or 0)
        shares = int(v.get("shares", 0) or 0)
        eng_rate = round(((likes + comments + shares) / max(1, views)) * 100, 2)

        title = v.get("title", "") or ""
        hook = calculate_hook_score(title)
        shareability = calculate_shareability({
            "views": views, "likes": likes, "comments": comments, "shares": shares,
            "title": title, "hashtags": v.get("hashtags", []), "duration": v.get("duration", 0),
        })
        monetization = calculate_monetization_potential({
            "views": views, "platform": "tiktok",
            "shop_product_url": v.get("shop_product_url", ""),
            "hashtags": v.get("hashtags", []), "title": title,
            "engagement_rate": eng_rate,
        })

        hook_score = hook.get("score", 0)
        share_score = shareability.get("score", 0)
        monet_score = monetization.get("score", 0)
        viral_score = round(hook_score * 0.25 + share_score * 0.25 + eng_rate * 5 + monet_score * 0.25, 1)

        scored.append({
            **v,
            "engagement_rate": eng_rate,
            "hook_score": hook_score,
            "hook_type": hook.get("hook_type", ""),
            "shareability_score": share_score,
            "shareability_label": shareability.get("label", ""),
            "monetization_score": monet_score,
            "monetization_label": monetization.get("label", ""),
            "viral_score": viral_score,
            "has_commerce": monetization.get("has_commerce", False),
        })

    scored.sort(key=lambda x: x["viral_score"], reverse=True)
    return {"videos": scored, "total": len(scored)}


# ─── NEW: Product Trends ──────────────────────────────────────


def get_product_trends(db: Session, account_id: int, days: int = 30) -> dict:
    """Product performance trends over time using snapshots."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    since = datetime.utcnow() - timedelta(days=days)
    products = db.query(Product).filter(
        Product.seller_name == account.username,
        Product.platform == "tiktok_shop",
    ).order_by(Product.sold_count.desc()).all()

    product_trends = []
    for p in products:
        snaps = db.query(ProductSnapshot).filter(
            ProductSnapshot.product_id == p.product_id,
            ProductSnapshot.captured_at >= since,
        ).order_by(ProductSnapshot.captured_at.asc()).all()

        sold_history = [{"date": s.captured_at.isoformat(), "sold": s.sold_count} for s in snaps]
        sold_growth = (snaps[-1].sold_count - snaps[0].sold_count) if len(snaps) >= 2 else 0

        product_trends.append({
            **p.to_dict(),
            "sold_history": sold_history,
            "sold_growth": sold_growth,
            "snapshot_count": len(snaps),
        })

    return {"products": product_trends, "days": days}


# ─── NEW: Commerce Analysis ───────────────────────────────────


def get_account_commerce_analysis(db: Session, account_id: int) -> dict:
    """Analyze commerce vs organic video performance."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    videos = account.full_videos or []
    if not videos:
        return {"commerce": {}, "organic": {}, "comparison": {}}

    commerce_videos = []
    organic_videos = []

    for v in videos:
        if v.get("shop_product_url") or v.get("is_eligible_for_commission") or v.get("with_shop_entry"):
            commerce_videos.append(v)
        else:
            organic_videos.append(v)

    def _aggregate(vids):
        if not vids:
            return {"count": 0, "avg_views": 0, "avg_likes": 0, "avg_shares": 0, "avg_comments": 0, "avg_engagement": 0}
        n = len(vids)
        total_views = sum(int(v.get("views", 0) or 0) for v in vids)
        total_likes = sum(int(v.get("likes", 0) or 0) for v in vids)
        total_shares = sum(int(v.get("shares", 0) or 0) for v in vids)
        total_comments = sum(int(v.get("comments", 0) or 0) for v in vids)
        avg_views = round(total_views / n)
        eng_rates = []
        for v in vids:
            views = int(v.get("views", 0) or 0)
            if views > 0:
                eng_rates.append(((int(v.get("likes", 0) or 0) + int(v.get("comments", 0) or 0) + int(v.get("shares", 0) or 0)) / views) * 100)
        avg_eng = round(sum(eng_rates) / max(1, len(eng_rates)), 2) if eng_rates else 0
        return {
            "count": n,
            "avg_views": avg_views,
            "avg_likes": round(total_likes / n),
            "avg_shares": round(total_shares / n),
            "avg_comments": round(total_comments / n),
            "avg_engagement": avg_eng,
        }

    commerce = _aggregate(commerce_videos)
    organic = _aggregate(organic_videos)

    comparison = {}
    if commerce["count"] > 0 and organic["count"] > 0:
        comparison = {
            "views_diff_pct": round(((commerce["avg_views"] - organic["avg_views"]) / max(1, organic["avg_views"])) * 100, 1),
            "engagement_diff_pct": round(commerce["avg_engagement"] - organic["avg_engagement"], 2),
            "commerce_performs_better": commerce["avg_views"] > organic["avg_views"],
        }

    return {
        "commerce": commerce,
        "organic": organic,
        "comparison": comparison,
        "commerce_ratio": account.commerce_ratio,
        "commission_eligible": account.commission_eligible_count,
        "total_videos": len(videos),
    }


# ─── NEW: Transcripts ──────────────────────────────────────────


def get_account_transcripts(db: Session, account_id: int, max_videos: int = 10) -> dict:
    """Get/generate transcripts for top videos using Whisper (FREE, local).

    Transcripts are stored inside full_videos JSON to avoid extra API calls.
    Only transcribes videos that don't have a transcript yet.
    """
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    videos = account.full_videos or []
    if not videos:
        return {"videos": [], "total": 0, "transcribed": 0}

    # Sort by views (transcribe most popular first)
    sorted_vids = sorted(videos, key=lambda v: int(v.get("views", 0) or 0), reverse=True)
    target_vids = sorted_vids[:max_videos]

    transcribed_count = 0
    updated = False
    results = []

    for v in target_vids:
        url = v.get("url", "")
        existing_transcript = v.get("transcript", "")

        if existing_transcript:
            results.append({
                "id": v.get("id", ""),
                "title": v.get("title", "")[:100],
                "url": url,
                "views": v.get("views", 0),
                "transcript": existing_transcript,
                "status": "cached",
            })
            transcribed_count += 1
            continue

        if not url:
            results.append({
                "id": v.get("id", ""),
                "title": v.get("title", "")[:100],
                "url": "",
                "views": v.get("views", 0),
                "transcript": "",
                "status": "no_url",
            })
            continue

        # Transcribe with Whisper (FREE)
        try:
            from backend.services.whisper_transcriber import transcribe_url
            result = transcribe_url(url, language="pt")
            if result.get("success"):
                transcript_text = result.get("transcript_text", "")
                # Store back in video data
                v["transcript"] = transcript_text
                updated = True
                transcribed_count += 1
                results.append({
                    "id": v.get("id", ""),
                    "title": v.get("title", "")[:100],
                    "url": url,
                    "views": v.get("views", 0),
                    "transcript": transcript_text,
                    "segments": result.get("transcript", []),
                    "language": result.get("language", ""),
                    "duration": result.get("duration", 0),
                    "status": "transcribed",
                })
            else:
                results.append({
                    "id": v.get("id", ""),
                    "title": v.get("title", "")[:100],
                    "url": url,
                    "views": v.get("views", 0),
                    "transcript": "",
                    "status": "error",
                    "error": result.get("error", ""),
                })
        except Exception as e:
            logger.warning(f"Transcription failed for {url}: {e}")
            results.append({
                "id": v.get("id", ""),
                "title": v.get("title", "")[:100],
                "url": url,
                "views": v.get("views", 0),
                "transcript": "",
                "status": "error",
                "error": str(e)[:100],
            })

    # Save updated videos with transcripts
    if updated:
        account.full_videos = videos
        db.commit()

    return {"videos": results, "total": len(target_vids), "transcribed": transcribed_count}


# ─── NEW: Product-Video Map ───────────────────────────────────


def get_product_video_map(db: Session, account_id: int) -> dict:
    """Map which products appear in which videos and vice-versa."""
    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    videos = account.full_videos or []
    products = db.query(Product).filter(
        Product.seller_name == account.username,
        Product.platform == "tiktok_shop",
    ).all()

    if not videos:
        return {"product_videos": [], "video_products": [], "products_without_videos": [], "summary": {}}

    product_dict = {p.product_id: p.to_dict() for p in products}
    product_titles = {p.product_id: (p.title or "").lower() for p in products}

    # Track which videos promote products
    video_products = []
    product_video_counts = {}

    for v in videos:
        vid_products = []
        shop_url = v.get("shop_product_url", "")
        title_lower = (v.get("title", "") or "").lower()
        hashtags_lower = [h.lower() for h in (v.get("hashtags", []) or [])]
        transcript_lower = (v.get("transcript", "") or "").lower()

        # Check commerce signals
        is_commerce = bool(shop_url) or v.get("is_eligible_for_commission") or v.get("with_shop_entry")

        # Try to match products by title keywords in video
        for pid, ptitle in product_titles.items():
            if not ptitle or len(ptitle) < 3:
                continue
            # Check if product title words appear in video title, hashtags, or transcript
            title_words = [w for w in ptitle.split() if len(w) > 3]
            match_score = 0
            for word in title_words:
                if word in title_lower:
                    match_score += 2
                if word in transcript_lower:
                    match_score += 1
                if any(word in h for h in hashtags_lower):
                    match_score += 1
            if match_score >= 2:
                vid_products.append({"product_id": pid, "match_score": match_score, **product_dict.get(pid, {})})
                product_video_counts[pid] = product_video_counts.get(pid, 0) + 1

        # If commerce video but no product matched, still flag it
        if is_commerce and not vid_products:
            vid_products.append({"product_id": "unknown", "match_score": 0, "title": "Produto detectado (nao identificado)"})

        video_products.append({
            "video_id": v.get("id", ""),
            "video_title": v.get("title", "")[:100],
            "video_url": v.get("url", ""),
            "views": v.get("views", 0),
            "likes": v.get("likes", 0),
            "is_commerce": is_commerce,
            "products": vid_products,
            "product_count": len(vid_products),
        })

    # Products ranked by video appearances
    product_videos = []
    for pid, count in sorted(product_video_counts.items(), key=lambda x: x[1], reverse=True):
        pdata = product_dict.get(pid, {})
        associated_vids = [vp for vp in video_products if any(p.get("product_id") == pid for p in vp["products"])]
        total_views = sum(vp["views"] for vp in associated_vids)
        product_videos.append({
            **pdata,
            "video_count": count,
            "total_video_views": total_views,
            "avg_video_views": round(total_views / max(1, count)),
        })

    # Products with zero video appearances
    products_without = [product_dict[pid] for pid in product_dict if pid not in product_video_counts]

    # Commerce videos sorted by views
    commerce_vids = sorted([vp for vp in video_products if vp["is_commerce"]], key=lambda x: x["views"], reverse=True)

    # Cache
    account.product_video_map = {
        "product_video_count": len(product_videos),
        "commerce_video_count": len(commerce_vids),
        "products_without_videos": len(products_without),
    }
    db.commit()

    return {
        "product_videos": product_videos,
        "video_products": video_products,
        "commerce_videos": commerce_vids,
        "products_without_videos": products_without,
        "summary": {
            "total_products": len(products),
            "products_in_videos": len(product_videos),
            "products_without_videos": len(products_without),
            "total_commerce_videos": len(commerce_vids),
            "total_videos": len(videos),
            "commerce_ratio": round(len(commerce_vids) / max(1, len(videos)) * 100, 1),
        },
    }


# ─── NEW: AI Insights ─────────────────────────────────────────


def get_account_insights(db: Session, account_id: int, force: bool = False) -> dict:
    """Generate actionable insights based on all account data (cached 24h)."""
    import asyncio

    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    # Cache check (24h)
    if not force and account.insights_cache and account.insights_generated_at:
        if (datetime.utcnow() - account.insights_generated_at).total_seconds() < 86400:
            return account.insights_cache

    videos = account.full_videos or []

    # Gather all data for the prompt
    top_vids = sorted(videos, key=lambda v: int(v.get("views", 0) or 0), reverse=True)[:5]
    worst_vids = sorted(videos, key=lambda v: int(v.get("views", 0) or 0))[:3]

    commerce_vids = [v for v in videos if v.get("shop_product_url") or v.get("is_eligible_for_commission")]
    organic_vids = [v for v in videos if not (v.get("shop_product_url") or v.get("is_eligible_for_commission"))]

    # Extract transcripts for context
    transcripts_sample = ""
    for v in top_vids[:3]:
        t = v.get("transcript", "")
        if t:
            transcripts_sample += f"\n--- Video ({v.get('views', 0)} views): {t[:300]}\n"

    avg_eng_commerce = 0
    avg_eng_organic = 0
    if commerce_vids:
        eng_c = [((int(v.get("likes", 0) or 0) + int(v.get("comments", 0) or 0) + int(v.get("shares", 0) or 0)) / max(1, int(v.get("views", 0) or 0))) * 100 for v in commerce_vids]
        avg_eng_commerce = round(sum(eng_c) / len(eng_c), 2)
    if organic_vids:
        eng_o = [((int(v.get("likes", 0) or 0) + int(v.get("comments", 0) or 0) + int(v.get("shares", 0) or 0)) / max(1, int(v.get("views", 0) or 0))) * 100 for v in organic_vids]
        avg_eng_organic = round(sum(eng_o) / len(eng_o), 2)

    top_desc = "\n".join([f"- {v.get('title', '')[:80]} ({v.get('views', 0)} views, dur: {v.get('duration', 0)}s)" for v in top_vids])
    worst_desc = "\n".join([f"- {v.get('title', '')[:80]} ({v.get('views', 0)} views)" for v in worst_vids])

    prompt = f"""Analise estes dados de uma conta TikTok Shop e gere insights acionaveis em portugues:

CONTA: @{account.username} ({account.display_name})
Followers: {account.followers:,} | Videos: {account.video_count}
Vendas: {account.total_sold:,} | Revenue: ${account.estimated_revenue:,.2f}
Engagement medio: {account.avg_engagement_rate:.1f}% | Hook Strength: {account.avg_hook_strength:.0f}/100
Commerce Ratio: {account.commerce_ratio:.1f}% | Posts/semana: {account.posts_per_week:.1f}
Formato: {account.pct_short_videos:.0f}% curtos, {account.pct_medium_videos:.0f}% medios, {account.pct_long_videos:.0f}% longos
Duracao media: {account.avg_duration:.0f}s

TOP VIDEOS (mais views):
{top_desc}

PIORES VIDEOS (menos views):
{worst_desc}

COMMERCE vs ORGANICO:
- Commerce: {len(commerce_vids)} videos, eng medio: {avg_eng_commerce:.1f}%
- Organico: {len(organic_vids)} videos, eng medio: {avg_eng_organic:.1f}%

{"TRANSCRICOES DOS TOP VIDEOS:" + transcripts_sample if transcripts_sample else ""}

Gere:
1. PADROES DE SUCESSO (3-4 padroes que os videos top tem em comum)
2. PROBLEMAS IDENTIFICADOS (2-3 problemas nos videos que nao performam)
3. OPORTUNIDADES (3-4 oportunidades concretas baseadas nos dados)
4. ENGAGEMENT INSIGHTS (o que gera mais engajamento nesta conta)
5. RECOMENDACOES DE CONTEUDO (tipos especificos de video que deveria fazer mais)
6. TIMING (quando e quanto postar baseado nos dados)

Seja especifico, use os numeros, e de recomendacoes que possam ser aplicadas HOJE."""

    try:
        from backend.services.local_ai import llm_chat
        loop = asyncio.new_event_loop()
        insights_text = loop.run_until_complete(llm_chat(
            prompt,
            system="Voce e um analista de dados especializado em TikTok Shop e e-commerce social. Responda em portugues, direto e acionavel.",
            max_tokens=2000,
        ))
        loop.close()
    except Exception as e:
        logger.warning(f"Insights generation failed: {e}")
        insights_text = "Erro ao gerar insights. Verifique se o Ollama esta rodando."

    result = {
        "insights": insights_text,
        "data_summary": {
            "total_videos": len(videos),
            "commerce_videos": len(commerce_vids),
            "organic_videos": len(organic_vids),
            "avg_engagement_commerce": avg_eng_commerce,
            "avg_engagement_organic": avg_eng_organic,
            "top_video_views": top_vids[0].get("views", 0) if top_vids else 0,
            "worst_video_views": worst_vids[0].get("views", 0) if worst_vids else 0,
            "has_transcripts": any(v.get("transcript") for v in videos),
        },
        "generated_at": datetime.utcnow().isoformat(),
    }

    # Cache
    account.insights_cache = result
    account.insights_generated_at = datetime.utcnow()
    db.commit()

    return result


# ─── NEW: Video Ideas ─────────────────────────────────────────


def get_account_video_ideas(db: Session, account_id: int, force: bool = False) -> dict:
    """Generate video ideas based on top-performing content patterns (cached 24h)."""
    import asyncio

    account = db.query(TikTokShopAccount).filter(TikTokShopAccount.id == account_id).first()
    if not account:
        return {"error": "Account not found"}

    # Cache check (24h)
    if not force and account.video_ideas_cache and account.video_ideas_generated_at:
        if (datetime.utcnow() - account.video_ideas_generated_at).total_seconds() < 86400:
            return account.video_ideas_cache

    videos = account.full_videos or []
    top_vids = sorted(videos, key=lambda v: int(v.get("views", 0) or 0), reverse=True)[:10]

    # Get products for product-focused ideas
    products = db.query(Product).filter(
        Product.seller_name == account.username,
        Product.platform == "tiktok_shop",
    ).order_by(Product.sold_count.desc()).limit(5).all()

    product_list = "\n".join([f"- {p.title} (${p.price}, {p.sold_count} vendidos)" for p in products]) if products else "Nenhum produto cadastrado"

    top_desc = "\n".join([f"- {v.get('title', '')[:100]} ({v.get('views', 0)} views, {v.get('duration', 0)}s, hashtags: {', '.join((v.get('hashtags', []) or [])[:5])})" for v in top_vids])

    # Extract transcripts for tone analysis
    sample_transcripts = ""
    for v in top_vids[:3]:
        t = v.get("transcript", "")
        if t:
            sample_transcripts += f"\nVideo ({v.get('views', 0)} views): {t[:200]}\n"

    hashtags_str = ", ".join([h.get("tag", "") for h in (account.top_hashtags or [])[:10]])

    prompt = f"""Baseado nos dados desta conta TikTok Shop, gere 10 ideias de novos videos em portugues:

CONTA: @{account.username}
Nicho: {account.category or 'nao definido'}
Followers: {account.followers:,}
Engagement medio: {account.avg_engagement_rate:.1f}%
Formato preferido: {'curtos (<15s)' if account.pct_short_videos > 50 else 'medios (15-60s)' if account.pct_medium_videos > 50 else 'longos (>60s)'}
Duracao media: {account.avg_duration:.0f}s

TOP VIDEOS QUE JA DERAM CERTO:
{top_desc}

HASHTAGS MAIS USADAS: {hashtags_str}

PRODUTOS DA LOJA:
{product_list}

{"TOM DE VOZ (transcricoes):" + sample_transcripts if sample_transcripts else ""}

Gere EXATAMENTE 10 ideias de video, cada uma com:
1. TITULO do video (como apareceria no TikTok)
2. FORMATO (tutorial, review, antes/depois, POV, storytelling, lista, trending, unboxing, comparacao, hack)
3. HOOK (primeiros 3 segundos - texto exato do que falar)
4. DESCRICAO (1 frase do que seria o video)
5. DURACAO sugerida em segundos
6. PRODUTO RELACIONADO (se aplicavel, qual produto da loja promover)
7. HASHTAGS (5 sugeridas)

Misture ideias de conteudo commerce (vendendo produto) e organico (engajamento puro).
Use padroes dos videos que ja deram certo.
Adapte ao tom de voz da conta."""

    try:
        from backend.services.local_ai import llm_chat
        loop = asyncio.new_event_loop()
        ideas_text = loop.run_until_complete(llm_chat(
            prompt,
            system="Voce e um estrategista de conteudo especializado em TikTok Shop viral. Gere ideias criativas, acionaveis e que misturem entretenimento com vendas.",
            max_tokens=3000,
        ))
        loop.close()
    except Exception as e:
        logger.warning(f"Video ideas generation failed: {e}")
        ideas_text = "Erro ao gerar ideias. Verifique se o Ollama esta rodando."

    result = {
        "ideas": ideas_text,
        "based_on": {
            "top_videos_analyzed": len(top_vids),
            "products_available": len(products),
            "top_hashtags": hashtags_str,
            "preferred_format": "curto" if account.pct_short_videos > 50 else "medio" if account.pct_medium_videos > 50 else "longo",
            "avg_duration": account.avg_duration,
        },
        "generated_at": datetime.utcnow().isoformat(),
    }

    # Cache
    account.video_ideas_cache = result
    account.video_ideas_generated_at = datetime.utcnow()
    db.commit()

    return result


# ─── Helper ─────────────────────────────────────────────────────


def _get_sc():
    """Get ScrapeCreators API instance if configured."""
    from backend.core.config import settings
    if settings.SCRAPECREATORS_API_KEY:
        from backend.scrapers.scrapecreators import ScrapeCreatorsAPI
        return ScrapeCreatorsAPI(settings.SCRAPECREATORS_API_KEY)
    return None
