"""
Competitor Spy - Track competitors, detect new content, alert on changes.
Full analysis, AI intelligence, comparison, and revenue estimation.
"""

import logging
from collections import Counter
from datetime import datetime, timedelta

from sqlalchemy.orm import Session

from backend.models.competitor import CompetitorProfile, CompetitorSnapshot

logger = logging.getLogger(__name__)


def add_competitor(db: Session, platform: str, username: str, category: str = "", notes: str = "") -> dict:
    existing = db.query(CompetitorProfile).filter(
        CompetitorProfile.platform == platform,
        CompetitorProfile.username == username,
    ).first()
    if existing:
        return {"status": "exists", "competitor": existing.to_dict()}
    comp = CompetitorProfile(
        platform=platform, username=username,
        category=category, notes=notes,
    )
    db.add(comp)
    db.commit()
    db.refresh(comp)
    return {"status": "added", "competitor": comp.to_dict()}


def remove_competitor(db: Session, competitor_id: int) -> bool:
    comp = db.query(CompetitorProfile).filter(CompetitorProfile.id == competitor_id).first()
    if not comp:
        return False
    db.delete(comp)
    db.commit()
    return True


def list_competitors(db: Session, platform: str = "", active_only: bool = True) -> list[dict]:
    q = db.query(CompetitorProfile)
    if platform:
        q = q.filter(CompetitorProfile.platform == platform)
    if active_only:
        q = q.filter(CompetitorProfile.is_active == True)
    return [c.to_dict() for c in q.order_by(CompetitorProfile.followers.desc()).all()]


def refresh_competitor(db: Session, sc, competitor_id: int) -> dict:
    comp = db.query(CompetitorProfile).filter(CompetitorProfile.id == competitor_id).first()
    if not comp:
        return {"error": "Competitor not found"}

    try:
        if comp.platform == "tiktok":
            profile = sc.tiktok_profile(comp.username)
            user = profile.get("user", profile)
            stats = profile.get("stats", user.get("stats", {}))
            comp.display_name = user.get("nickname", comp.display_name)
            comp.avatar = user.get("avatarThumb", comp.avatar)
            comp.followers = int(stats.get("followerCount", 0) or user.get("followerCount", 0) or 0)
            comp.following = int(stats.get("followingCount", 0) or 0)
            comp.total_likes = int(stats.get("heartCount", 0) or stats.get("heart", 0) or 0)
            comp.video_count = int(stats.get("videoCount", 0) or 0)
            comp.bio = user.get("signature", "")
        elif comp.platform == "instagram":
            profile = sc.instagram_user_info(comp.username)
            comp.display_name = profile.get("full_name", comp.display_name)
            comp.avatar = profile.get("profile_pic_url", comp.avatar)
            comp.followers = int(profile.get("follower_count", 0) or 0)
            comp.following = int(profile.get("following_count", 0) or 0)
            comp.video_count = int(profile.get("media_count", 0) or 0)
            comp.bio = profile.get("biography", "")
        elif comp.platform == "youtube":
            info = sc.youtube_channel_info(f"https://youtube.com/@{comp.username}")
            comp.display_name = info.get("title", comp.display_name)
            avatar_raw = info.get("avatar", comp.avatar)
            if isinstance(avatar_raw, dict):
                # Extract URL from nested dict structure
                sources = avatar_raw.get("image", {}).get("sources", []) if isinstance(avatar_raw.get("image"), dict) else []
                avatar_raw = sources[0].get("url", "") if sources else str(avatar_raw)[:500]
            comp.avatar = str(avatar_raw or "")[:500]
            comp.followers = int(info.get("subscriberCount", 0) or 0)
            comp.video_count = int(info.get("videoCount", 0) or 0)
            bio_raw = info.get("description", "")
            comp.bio = (str(bio_raw) if bio_raw else "")[:500]

        comp.last_checked = datetime.utcnow()
        db.commit()

        # Take snapshot
        snap = CompetitorSnapshot(
            competitor_id=comp.id,
            followers=comp.followers,
            total_likes=comp.total_likes,
            video_count=comp.video_count,
        )
        db.add(snap)
        db.commit()

        return comp.to_dict()
    except Exception as e:
        db.rollback()
        logger.warning(f"Error refreshing competitor {comp.username}: {e}")
        return {"error": str(e), "competitor": comp.to_dict()}


def get_competitor_videos(sc, platform: str, username: str, count: int = 20) -> list[dict]:
    try:
        if platform == "tiktok":
            return sc.tiktok_user_videos(username, count, sort_by="popular")
        elif platform == "instagram":
            return sc.instagram_user_reels(username, count)
        elif platform == "youtube":
            result = sc.youtube_channel_videos(f"https://youtube.com/@{username}", count)
            return result.get("videos", []) + result.get("shorts", [])
    except Exception:
        return []


def get_competitor_growth(db: Session, competitor_id: int, days: int = 30) -> dict:
    from datetime import timedelta
    since = datetime.utcnow() - timedelta(days=days)
    snapshots = (
        db.query(CompetitorSnapshot)
        .filter(CompetitorSnapshot.competitor_id == competitor_id, CompetitorSnapshot.captured_at >= since)
        .order_by(CompetitorSnapshot.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
    like_growth = last.total_likes - first.total_likes
    video_growth = last.video_count - first.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),
            "likes": like_growth,
            "likes_per_day": round(like_growth / days_delta, 1),
            "videos": video_growth,
            "days_tracked": days_delta,
        },
    }


# ─── Content Analysis (4 functions) ────────────────────────────


def analyze_post_cadence(videos: list[dict]) -> dict:
    """Analyze posting cadence from competitor videos."""
    if not videos:
        return {"posts_per_week": 0, "best_days": [], "best_hours": [], "cadence_heatmap": {}}

    dates = []
    day_counter = Counter()
    hour_counter = Counter()
    for v in videos:
        ts = v.get("createTime") or v.get("timestamp") or v.get("published_at")
        if not ts:
            continue
        try:
            if isinstance(ts, (int, float)):
                dt = datetime.utcfromtimestamp(ts)
            else:
                dt = datetime.fromisoformat(str(ts).replace("Z", "+00:00").replace("+00:00", ""))
            dates.append(dt)
            day_counter[dt.strftime("%A")] += 1
            hour_counter[dt.hour] += 1
        except Exception:
            continue

    if len(dates) < 2:
        return {"posts_per_week": len(dates), "best_days": [], "best_hours": [], "cadence_heatmap": {}}

    dates.sort()
    span_days = max(1, (dates[-1] - dates[0]).days)
    posts_per_week = round(len(dates) / span_days * 7, 1)

    # Build heatmap: day -> hour -> count
    heatmap = {}
    for dt in dates:
        day = dt.strftime("%A")
        hour = dt.hour
        heatmap.setdefault(day, {})
        heatmap[day][hour] = heatmap[day].get(hour, 0) + 1

    return {
        "posts_per_week": posts_per_week,
        "total_posts": len(dates),
        "span_days": span_days,
        "best_days": [d for d, _ in day_counter.most_common(3)],
        "best_hours": [h for h, _ in hour_counter.most_common(3)],
        "day_distribution": dict(day_counter),
        "hour_distribution": {str(k): v for k, v in sorted(hour_counter.items())},
        "cadence_heatmap": heatmap,
    }


def analyze_hashtags(videos: list[dict]) -> dict:
    """Analyze hashtag strategy from competitor videos."""
    all_tags = []
    tag_performance = {}

    for v in videos:
        tags = v.get("hashtags") or []
        if isinstance(tags, str):
            tags = [t.strip("#").strip() for t in tags.split() if t.startswith("#")]
        desc = v.get("desc") or v.get("description") or v.get("title") or ""
        desc_tags = [w.strip("#").lower() for w in desc.split() if w.startswith("#")]
        tags = list(set([t.lower() for t in tags] + desc_tags))

        views = int(v.get("playCount") or v.get("views") or v.get("play_count") or 0)
        likes = int(v.get("diggCount") or v.get("likes") or v.get("like_count") or 0)

        for tag in tags:
            if not tag:
                continue
            all_tags.append(tag)
            if tag not in tag_performance:
                tag_performance[tag] = {"count": 0, "total_views": 0, "total_likes": 0}
            tag_performance[tag]["count"] += 1
            tag_performance[tag]["total_views"] += views
            tag_performance[tag]["total_likes"] += likes

    # Average performance per tag
    for tag, data in tag_performance.items():
        c = max(1, data["count"])
        data["avg_views"] = round(data["total_views"] / c)
        data["avg_likes"] = round(data["total_likes"] / c)

    tag_counts = Counter(all_tags)
    top_tags = [{"tag": t, "count": c, **tag_performance.get(t, {})} for t, c in tag_counts.most_common(30)]

    return {
        "total_unique_tags": len(set(all_tags)),
        "avg_tags_per_post": round(len(all_tags) / max(1, len(videos)), 1),
        "top_hashtags": top_tags,
        "cloud": [{"text": t, "value": c} for t, c in tag_counts.most_common(50)],
    }


def analyze_content_formats(videos: list[dict]) -> dict:
    """Analyze content format distribution."""
    formats = {"short": 0, "medium": 0, "long": 0, "unknown": 0}
    total_duration = 0
    count = 0

    for v in videos:
        dur = int(v.get("duration") or v.get("video", {}).get("duration", 0) or 0)
        if dur > 0:
            total_duration += dur
            count += 1
            if dur <= 15:
                formats["short"] += 1
            elif dur <= 60:
                formats["medium"] += 1
            else:
                formats["long"] += 1
        else:
            formats["unknown"] += 1

    return {
        "formats": formats,
        "avg_duration": round(total_duration / max(1, count), 1),
        "total_analyzed": len(videos),
    }


def get_top_content(videos: list[dict], top_n: int = 10) -> list[dict]:
    """Get top performing content sorted by views."""
    scored = []
    for v in videos:
        views = int(v.get("playCount") or v.get("views") or v.get("play_count") or 0)
        likes = int(v.get("diggCount") or v.get("likes") or v.get("like_count") or 0)
        comments = int(v.get("commentCount") or v.get("comments") or v.get("comment_count") or 0)
        shares = int(v.get("shareCount") or v.get("shares") or v.get("share_count") or 0)
        eng_rate = round(((likes + comments + shares) / max(1, views)) * 100, 2)

        scored.append({
            "title": v.get("desc") or v.get("description") or v.get("title") or "",
            "views": views,
            "likes": likes,
            "comments": comments,
            "shares": shares,
            "engagement_rate": eng_rate,
            "duration": int(v.get("duration") or v.get("video", {}).get("duration", 0) or 0),
            "url": v.get("url") or v.get("video_url") or "",
            "thumbnail": v.get("cover") or v.get("thumbnail_url") or v.get("video", {}).get("cover", ""),
            "create_time": v.get("createTime") or v.get("timestamp") or v.get("published_at"),
        })

    scored.sort(key=lambda x: x["views"], reverse=True)
    return scored[:top_n]


# ─── AI Intelligence (3 functions) ─────────────────────────────


async def generate_ai_report(comp_data: dict, videos: list[dict]) -> dict:
    """Generate comprehensive AI report about a competitor."""
    from backend.services.local_ai import llm_chat

    top = get_top_content(videos, 5)
    cadence = analyze_post_cadence(videos)
    hashtags = analyze_hashtags(videos)
    formats = analyze_content_formats(videos)

    top_desc = "\n".join([f"- {t['title'][:80]} ({t['views']} views, {t['engagement_rate']}% eng)" for t in top])
    tag_str = ", ".join([t["tag"] for t in hashtags["top_hashtags"][:15]])

    prompt = f"""Analise este concorrente de redes sociais e gere um relatorio estrategico em portugues:

PERFIL: {comp_data.get('display_name', comp_data.get('username', ''))} (@{comp_data.get('username', '')})
Plataforma: {comp_data.get('platform', '')}
Seguidores: {comp_data.get('followers', 0):,}
Videos: {comp_data.get('video_count', 0)}
Likes totais: {comp_data.get('total_likes', 0):,}
Bio: {comp_data.get('bio', '')[:200]}

CADENCIA: {cadence['posts_per_week']} posts/semana, melhores dias: {', '.join(cadence['best_days'][:3])}
FORMATOS: curto={formats['formats']['short']}, medio={formats['formats']['medium']}, longo={formats['formats']['long']}
DURACAO MEDIA: {formats['avg_duration']}s
HASHTAGS TOP: {tag_str}

TOP VIDEOS:
{top_desc}

Gere um relatorio com:
1. RESUMO (2-3 frases sobre a estrategia do concorrente)
2. PONTOS FORTES (3-4 pontos)
3. PONTOS FRACOS (2-3 oportunidades que voce pode explorar)
4. ESTRATEGIA DE CONTEUDO (o que funciona para ele)
5. RECOMENDACOES (3-4 acoes concretas para superar este concorrente)

Responda em formato estruturado, direto e acionavel."""

    report_text = await llm_chat(prompt, system="Voce e um analista de marketing digital especializado em redes sociais.", max_tokens=1500)

    return {
        "competitor": comp_data.get("username", ""),
        "report": report_text or "Erro ao gerar relatorio. Verifique se o Ollama esta rodando.",
        "metrics": {
            "cadence": cadence,
            "hashtags_summary": {"total": hashtags["total_unique_tags"], "avg_per_post": hashtags["avg_tags_per_post"]},
            "formats": formats["formats"],
            "top_content_count": len(top),
        },
        "generated_at": datetime.utcnow().isoformat(),
    }


async def gap_analysis(my_data: dict, competitor_data: dict, my_videos: list[dict], comp_videos: list[dict]) -> dict:
    """AI-powered gap analysis between you and a competitor."""
    from backend.services.local_ai import llm_chat

    my_tags = set(t["tag"] for t in analyze_hashtags(my_videos)["top_hashtags"][:20])
    comp_tags = set(t["tag"] for t in analyze_hashtags(comp_videos)["top_hashtags"][:20])
    missing_tags = comp_tags - my_tags
    my_cadence = analyze_post_cadence(my_videos)
    comp_cadence = analyze_post_cadence(comp_videos)

    prompt = f"""Faca uma analise de gap (lacunas) entre dois perfis de redes sociais em portugues:

MEU PERFIL:
- Seguidores: {my_data.get('followers', 0):,}
- Videos: {my_data.get('video_count', 0)}
- Posts/semana: {my_cadence.get('posts_per_week', 0)}

CONCORRENTE:
- Username: @{competitor_data.get('username', '')}
- Seguidores: {competitor_data.get('followers', 0):,}
- Videos: {competitor_data.get('video_count', 0)}
- Posts/semana: {comp_cadence.get('posts_per_week', 0)}

HASHTAGS QUE O CONCORRENTE USA E EU NAO: {', '.join(list(missing_tags)[:15])}

Identifique:
1. GAPS DE CONTEUDO - Que tipo de conteudo o concorrente faz que eu nao faco
2. GAPS DE FREQUENCIA - Diferenca na cadencia de publicacao
3. GAPS DE HASHTAGS - Tags que deveria usar
4. OPORTUNIDADES - O que posso fazer melhor que o concorrente
5. PLANO DE ACAO - 3 acoes imediatas para fechar os gaps"""

    analysis_text = await llm_chat(prompt, system="Voce e um consultor de growth hacking para redes sociais.", max_tokens=1200)

    return {
        "analysis": analysis_text or "Erro ao gerar analise. Verifique o Ollama.",
        "missing_hashtags": list(missing_tags)[:20],
        "cadence_gap": {
            "my_posts_week": my_cadence.get("posts_per_week", 0),
            "competitor_posts_week": comp_cadence.get("posts_per_week", 0),
        },
        "follower_gap": competitor_data.get("followers", 0) - my_data.get("followers", 0),
        "generated_at": datetime.utcnow().isoformat(),
    }


async def reverse_engineer_viral(videos: list[dict], competitor_name: str = "") -> dict:
    """Reverse-engineer what makes competitor's videos go viral."""
    from backend.services.local_ai import llm_chat

    top = get_top_content(videos, 5)
    if not top:
        return {"patterns": [], "hooks": [], "analysis": "Sem videos para analisar."}

    videos_desc = "\n".join([
        f"VIDEO {i+1}: \"{t['title'][:100]}\" | Views: {t['views']:,} | Eng: {t['engagement_rate']}% | Dur: {t['duration']}s"
        for i, t in enumerate(top)
    ])

    prompt = f"""Analise os videos virais deste criador ({competitor_name}) e faca engenharia reversa:

{videos_desc}

Identifique em portugues:
1. PADROES VIRAIS - O que os videos de sucesso tem em comum
2. HOOKS USADOS - Que ganchos iniciais funcionam (primeiros 3 segundos)
3. ESTRUTURA - Como os videos sao estruturados
4. ELEMENTOS EMOCIONAIS - Que emocoes sao ativadas
5. FORMULA REPLICAVEL - Um template que eu posso usar

Seja especifico e acionavel."""

    analysis = await llm_chat(prompt, system="Voce e um especialista em viralidade de videos curtos.", max_tokens=1200)

    avg_views_top = round(sum(t["views"] for t in top) / len(top)) if top else 0
    avg_eng_top = round(sum(t["engagement_rate"] for t in top) / len(top), 2) if top else 0

    return {
        "competitor": competitor_name,
        "analysis": analysis or "Erro ao gerar analise.",
        "top_videos_analyzed": len(top),
        "avg_views_top": avg_views_top,
        "avg_engagement_top": avg_eng_top,
        "viral_threshold": avg_views_top,
        "generated_at": datetime.utcnow().isoformat(),
    }


# ─── Comparison (3 functions) ──────────────────────────────────


def compare_competitors(db: Session, competitor_ids: list[int]) -> dict:
    """Compare multiple competitors side by side."""
    comps = db.query(CompetitorProfile).filter(CompetitorProfile.id.in_(competitor_ids)).all()
    if not comps:
        return {"competitors": [], "ranking": {}}

    data = []
    for c in comps:
        growth = get_competitor_growth(db, c.id, 30)
        g = growth.get("growth", {})
        data.append({
            **c.to_dict(),
            "growth_followers": g.get("followers", 0),
            "growth_percent": g.get("followers_percent", 0),
            "growth_likes": g.get("likes", 0),
            "posts_tracked_days": g.get("days_tracked", 0),
        })

    # Rankings
    ranking = {
        "by_followers": sorted(data, key=lambda x: x["followers"], reverse=True),
        "by_growth": sorted(data, key=lambda x: x["growth_percent"], reverse=True),
        "by_likes": sorted(data, key=lambda x: x["total_likes"], reverse=True),
        "by_videos": sorted(data, key=lambda x: x["video_count"], reverse=True),
    }

    return {"competitors": data, "ranking": ranking, "total": len(data)}


def benchmark_competitors(db: Session, competitor_ids: list[int]) -> dict:
    """Generate benchmark metrics across competitors."""
    comps = db.query(CompetitorProfile).filter(CompetitorProfile.id.in_(competitor_ids)).all()
    if not comps:
        return {}

    followers_list = [c.followers for c in comps]
    likes_list = [c.total_likes for c in comps]
    videos_list = [c.video_count for c in comps]

    def _stats(values):
        if not values:
            return {"min": 0, "max": 0, "avg": 0, "median": 0}
        s = sorted(values)
        return {
            "min": s[0],
            "max": s[-1],
            "avg": round(sum(s) / len(s)),
            "median": s[len(s) // 2],
        }

    return {
        "sample_size": len(comps),
        "followers": _stats(followers_list),
        "likes": _stats(likes_list),
        "videos": _stats(videos_list),
        "engagement_avg": round(
            sum(c.total_likes / max(1, c.followers) for c in comps) / max(1, len(comps)) * 100, 2
        ),
        "platforms": dict(Counter(c.platform for c in comps)),
    }


def check_viral_alerts(db: Session, sc, threshold_multiplier: float = 3.0) -> list[dict]:
    """Check for new viral videos from tracked competitors."""
    alerts = []
    comps = db.query(CompetitorProfile).filter(CompetitorProfile.is_active == True).all()

    for comp in comps:
        try:
            videos = get_competitor_videos(sc, comp.platform, comp.username, 10)
            if not videos:
                continue

            # Calculate average views
            views_list = [int(v.get("playCount") or v.get("views") or v.get("play_count") or 0) for v in videos]
            if not views_list:
                continue
            avg_views = sum(views_list) / len(views_list)

            for v in videos:
                v_views = int(v.get("playCount") or v.get("views") or v.get("play_count") or 0)
                if v_views > avg_views * threshold_multiplier and v_views > 10000:
                    alerts.append({
                        "competitor": comp.to_dict(),
                        "video": {
                            "title": v.get("desc") or v.get("description") or v.get("title") or "",
                            "views": v_views,
                            "avg_views": round(avg_views),
                            "multiplier": round(v_views / max(1, avg_views), 1),
                            "url": v.get("url") or v.get("video_url") or "",
                        },
                        "alert_type": "viral_video",
                    })
        except Exception as e:
            logger.warning(f"Error checking viral alerts for {comp.username}: {e}")

    return alerts


# ─── Revenue Estimation (2 functions) ──────────────────────────


def estimate_revenue(comp_data: dict, videos: list[dict]) -> dict:
    """Estimate competitor revenue based on public metrics."""
    followers = comp_data.get("followers", 0)
    total_likes = comp_data.get("total_likes", 0)
    platform = comp_data.get("platform", "tiktok")

    # Calculate average views from videos
    views_list = [int(v.get("playCount") or v.get("views") or v.get("play_count") or 0) for v in videos]
    avg_views = round(sum(views_list) / max(1, len(views_list))) if views_list else 0

    cadence = analyze_post_cadence(videos)
    posts_per_month = cadence.get("posts_per_week", 0) * 4.3

    # CPM estimates by platform (USD)
    cpm_ranges = {
        "tiktok": {"low": 0.02, "mid": 0.05, "high": 0.10},
        "youtube": {"low": 1.00, "mid": 3.00, "high": 8.00},
        "instagram": {"low": 0.01, "mid": 0.03, "high": 0.08},
    }
    cpm = cpm_ranges.get(platform, cpm_ranges["tiktok"])

    # Sponsorship rate estimates (per post)
    if followers >= 1_000_000:
        sponsor_range = {"low": 5000, "high": 50000}
    elif followers >= 100_000:
        sponsor_range = {"low": 1000, "high": 10000}
    elif followers >= 10_000:
        sponsor_range = {"low": 200, "high": 2000}
    else:
        sponsor_range = {"low": 50, "high": 500}

    monthly_views = avg_views * posts_per_month

    return {
        "estimated_monthly_views": round(monthly_views),
        "ad_revenue": {
            "low": round(monthly_views / 1000 * cpm["low"], 2),
            "mid": round(monthly_views / 1000 * cpm["mid"], 2),
            "high": round(monthly_views / 1000 * cpm["high"], 2),
            "currency": "USD",
        },
        "sponsorship_per_post": sponsor_range,
        "estimated_sponsored_monthly": {
            "low": round(sponsor_range["low"] * min(4, posts_per_month * 0.3)),
            "high": round(sponsor_range["high"] * min(8, posts_per_month * 0.5)),
        },
        "avg_views_per_video": avg_views,
        "posts_per_month": round(posts_per_month, 1),
        "platform": platform,
        "tier": "mega" if followers >= 1_000_000 else "macro" if followers >= 100_000 else "micro" if followers >= 10_000 else "nano",
    }


def compare_engagement(db: Session, competitor_ids: list[int], sc=None) -> list[dict]:
    """Compare engagement metrics across competitors."""
    comps = db.query(CompetitorProfile).filter(CompetitorProfile.id.in_(competitor_ids)).all()
    results = []

    for comp in comps:
        eng_rate = round((comp.total_likes / max(1, comp.followers)) * 100, 2)
        likes_per_video = round(comp.total_likes / max(1, comp.video_count))

        videos = []
        if sc:
            try:
                videos = get_competitor_videos(sc, comp.platform, comp.username, 20)
            except Exception:
                pass

        # Video-level engagement
        video_eng_rates = []
        for v in videos:
            v_views = int(v.get("playCount") or v.get("views") or v.get("play_count") or 0)
            v_likes = int(v.get("diggCount") or v.get("likes") or v.get("like_count") or 0)
            v_comments = int(v.get("commentCount") or v.get("comments") or v.get("comment_count") or 0)
            v_shares = int(v.get("shareCount") or v.get("shares") or v.get("share_count") or 0)
            if v_views > 0:
                video_eng_rates.append(round(((v_likes + v_comments + v_shares) / v_views) * 100, 2))

        avg_video_eng = round(sum(video_eng_rates) / max(1, len(video_eng_rates)), 2) if video_eng_rates else 0

        results.append({
            **comp.to_dict(),
            "engagement_rate": eng_rate,
            "likes_per_video": likes_per_video,
            "avg_video_engagement": avg_video_eng,
            "videos_analyzed": len(videos),
        })

    results.sort(key=lambda x: x["engagement_rate"], reverse=True)
    return results


def refresh_all_competitors(db: Session, sc) -> dict:
    """Refresh all active competitors."""
    comps = db.query(CompetitorProfile).filter(CompetitorProfile.is_active == True).all()
    results = {"success": 0, "errors": 0, "total": len(comps), "details": []}

    for comp in comps:
        try:
            result = refresh_competitor(db, sc, comp.id)
            if "error" in result:
                results["errors"] += 1
                results["details"].append({"username": comp.username, "error": result["error"]})
            else:
                results["success"] += 1
        except Exception as e:
            db.rollback()
            results["errors"] += 1
            results["details"].append({"username": comp.username, "error": str(e)})
            logger.warning(f"Error refreshing {comp.username}: {e}")

    return results
