"""
Shop Analytics Service - Advanced intelligence for ShopIntel module.

Features:
- Viral Product Detection (explosive growth products)
- Product Lifecycle Analysis (launch/growth/maturity/decline)
- Price & Stock Change Alerts
- Affiliate Network Map (creator ↔ product relationships)
- Commission Calculator & Optimizer
- Category Trend Heatmap
- Product Opportunity Score
"""

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

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

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

logger = logging.getLogger(__name__)


# ── Viral Product Detection ──────────────────────────────────

def detect_viral_products(db: Session, days: int = 7, min_velocity: float = 20,
                          limit: int = 20) -> list[dict]:
    """
    Identify products with explosive growth (>20% daily sales velocity).
    Uses snapshot deltas to detect acceleration.
    """
    since = datetime.utcnow() - timedelta(days=days)

    # Get products with multiple snapshots
    subq = (
        db.query(
            ProductSnapshot.product_id,
            func.min(ProductSnapshot.sold_count).label("first_sold"),
            func.max(ProductSnapshot.sold_count).label("last_sold"),
            func.min(ProductSnapshot.captured_at).label("first_at"),
            func.max(ProductSnapshot.captured_at).label("last_at"),
            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.first_sold,
            subq.c.last_sold,
            subq.c.first_at,
            subq.c.last_at,
            subq.c.snap_count,
        )
        .join(subq, Product.product_id == subq.c.product_id)
        .all()
    )

    viral_products = []
    for product, first_sold, last_sold, first_at, last_at, snap_count in results:
        first_sold = int(first_sold or 0)
        last_sold = int(last_sold or 0)
        snap_count = int(snap_count or 0)

        delta_sold = last_sold - first_sold
        if delta_sold <= 0:
            continue

        hours = max(1, (last_at - first_at).total_seconds() / 3600)
        daily_velocity = (delta_sold / hours) * 24

        # Calculate growth percentage
        growth_pct = (delta_sold / max(first_sold, 1)) * 100

        if daily_velocity < min_velocity and growth_pct < 50:
            continue

        # Get related video count
        video_count = db.query(func.count(Video.id)).filter(
            or_(
                Video.shop_product_url.ilike(f"%{product.product_id}%"),
                Video.title.ilike(f"%{product.title[:20]}%") if product.title else False,
            )
        ).scalar() or 0

        viral_products.append({
            **product.to_dict(),
            "delta_sold": delta_sold,
            "daily_velocity": round(daily_velocity, 1),
            "growth_percent": round(growth_pct, 1),
            "snap_count": snap_count,
            "hours_tracked": round(hours, 1),
            "video_count": video_count,
            "viral_label": "EXPLOSIVO" if growth_pct > 200 else "VIRAL" if growth_pct > 100 else "CRESCENDO RAPIDO",
        })

    viral_products.sort(key=lambda x: x["daily_velocity"], reverse=True)
    return viral_products[:limit]


# ── Product Lifecycle Analysis ───────────────────────────────

def analyze_product_lifecycle(db: Session, product_id: str) -> dict:
    """
    Classify product lifecycle stage based on snapshot trends.
    Stages: launch, growth, maturity, decline.
    """
    snapshots = (
        db.query(ProductSnapshot)
        .filter(ProductSnapshot.product_id == product_id)
        .order_by(ProductSnapshot.captured_at.asc())
        .all()
    )

    if len(snapshots) < 2:
        return {
            "product_id": product_id,
            "stage": "unknown",
            "label": "Dados insuficientes",
            "description": "Precisa de pelo menos 2 snapshots para analise.",
            "snapshots": len(snapshots),
            "metrics": {},
        }

    # Split into periods
    total = len(snapshots)
    third = max(1, total // 3)

    early = snapshots[:third]
    mid = snapshots[third:third*2]
    late = snapshots[third*2:]

    def period_velocity(snaps):
        if len(snaps) < 2:
            return 0
        delta = snaps[-1].sold_count - snaps[0].sold_count
        hours = max(1, (snaps[-1].captured_at - snaps[0].captured_at).total_seconds() / 3600)
        return (delta / hours) * 24

    early_vel = period_velocity(early)
    mid_vel = period_velocity(mid) if mid else early_vel
    late_vel = period_velocity(late) if late else mid_vel

    total_sold = snapshots[-1].sold_count - snapshots[0].sold_count
    days_tracked = max(1, (snapshots[-1].captured_at - snapshots[0].captured_at).days)

    # Rating trend
    early_rating = early[-1].rating if early else 0
    late_rating = late[-1].rating if late else early_rating

    # Determine stage
    if days_tracked <= 3 or total_sold < 50:
        stage = "launch"
        label = "Lancamento"
        description = "Produto novo no mercado. Ainda pouco validado."
    elif late_vel > mid_vel * 1.2 and late_vel > early_vel:
        stage = "growth"
        label = "Crescimento"
        description = "Vendas acelerando! Momento ideal para entrar como afiliado."
    elif late_vel >= mid_vel * 0.7 and mid_vel >= early_vel * 0.5:
        stage = "maturity"
        label = "Maturidade"
        description = "Vendas estaveis. Produto consolidado mas pode saturar."
    else:
        stage = "decline"
        label = "Declinio"
        description = "Vendas desacelerando. Considere trocar de produto."

    return {
        "product_id": product_id,
        "stage": stage,
        "label": label,
        "description": description,
        "snapshots": total,
        "days_tracked": days_tracked,
        "metrics": {
            "total_sold": total_sold,
            "early_velocity": round(early_vel, 1),
            "mid_velocity": round(mid_vel, 1),
            "late_velocity": round(late_vel, 1),
            "rating_trend": "improving" if late_rating > early_rating else "declining" if late_rating < early_rating else "stable",
            "current_rating": late_rating,
        },
    }


# ── Price & Stock Change Alerts ──────────────────────────────

def get_price_stock_alerts(db: Session, hours: int = 48) -> list[dict]:
    """
    Detect significant price or stock changes from recent snapshots.
    """
    since = datetime.utcnow() - timedelta(hours=hours)

    # Get products with recent snapshots
    product_ids = (
        db.query(ProductSnapshot.product_id)
        .filter(ProductSnapshot.captured_at >= since)
        .distinct()
        .all()
    )

    alerts = []
    for (pid,) in product_ids:
        snaps = (
            db.query(ProductSnapshot)
            .filter(ProductSnapshot.product_id == pid)
            .order_by(ProductSnapshot.captured_at.desc())
            .limit(5)
            .all()
        )

        if len(snaps) < 2:
            continue

        latest = snaps[0]
        prev = snaps[1]

        product = db.query(Product).filter(Product.product_id == pid).first()
        product_name = product.title[:50] if product else pid

        # Price change
        try:
            latest_price = float(str(latest.price or "0").replace("R$", "").replace(",", ".").strip() or 0)
            prev_price = float(str(prev.price or "0").replace("R$", "").replace(",", ".").strip() or 0)
        except (ValueError, TypeError):
            latest_price = 0
            prev_price = 0

        if prev_price > 0 and latest_price > 0:
            price_change = ((latest_price - prev_price) / prev_price) * 100
            if abs(price_change) >= 5:
                alerts.append({
                    "type": "price_change",
                    "product_id": pid,
                    "product_name": product_name,
                    "icon": "trending-down" if price_change < 0 else "trending-up",
                    "priority": "high" if abs(price_change) >= 20 else "medium",
                    "title": f"Preco {'caiu' if price_change < 0 else 'subiu'} {abs(price_change):.0f}%",
                    "detail": f"{product_name}: R${prev_price:.2f} → R${latest_price:.2f}",
                    "change_percent": round(price_change, 1),
                    "timestamp": latest.captured_at.isoformat(),
                })

        # Stock change (running low or restocked)
        if prev.stock > 0 and latest.stock > 0:
            stock_change = latest.stock - prev.stock
            stock_pct = (stock_change / prev.stock) * 100

            if latest.stock < 10:
                alerts.append({
                    "type": "low_stock",
                    "product_id": pid,
                    "product_name": product_name,
                    "icon": "alert-triangle",
                    "priority": "urgent",
                    "title": f"Estoque baixo: {latest.stock} unidades",
                    "detail": f"{product_name} - pode esgotar em breve",
                    "stock": latest.stock,
                    "timestamp": latest.captured_at.isoformat(),
                })
            elif stock_pct > 50:
                alerts.append({
                    "type": "restocked",
                    "product_id": pid,
                    "product_name": product_name,
                    "icon": "package-plus",
                    "priority": "low",
                    "title": f"Reabastecido (+{stock_change} unidades)",
                    "detail": f"{product_name}: {prev.stock} → {latest.stock}",
                    "timestamp": latest.captured_at.isoformat(),
                })

        # Sales spike
        sold_delta = latest.sold_count - prev.sold_count
        if sold_delta > 0:
            hours_between = max(1, (latest.captured_at - prev.captured_at).total_seconds() / 3600)
            hourly_rate = sold_delta / hours_between
            if hourly_rate > 10:
                alerts.append({
                    "type": "sales_spike",
                    "product_id": pid,
                    "product_name": product_name,
                    "icon": "zap",
                    "priority": "high",
                    "title": f"Pico de vendas: {sold_delta} em {hours_between:.0f}h",
                    "detail": f"{product_name} ({hourly_rate:.0f}/hora)",
                    "sold_delta": sold_delta,
                    "timestamp": latest.captured_at.isoformat(),
                })

    # Sort by priority
    priority_order = {"urgent": 0, "high": 1, "medium": 2, "low": 3}
    alerts.sort(key=lambda a: priority_order.get(a["priority"], 99))
    return alerts


# ── Affiliate Network Map ────────────────────────────────────

def get_affiliate_network(db: Session, days: int = 30, limit: int = 50) -> dict:
    """
    Map which creators are promoting which products (via shop_product_url).
    Returns creator-product relationships with performance metrics.
    """
    since = datetime.utcnow() - timedelta(days=days)

    videos = (
        db.query(
            Video.creator_id,
            Video.shop_product_url,
            Video.views,
            Video.likes,
            Video.viral_score,
        )
        .filter(
            Video.collected_at >= since,
            Video.shop_product_url != "",
            Video.shop_product_url.isnot(None),
            Video.creator_id.isnot(None),
        )
        .all()
    )

    # Group by creator
    creator_products = defaultdict(lambda: {
        "products": defaultdict(lambda: {"videos": 0, "total_views": 0, "total_likes": 0, "scores": []}),
        "total_videos": 0,
        "total_views": 0,
    })

    for cid, shop_url, views, likes, score in videos:
        cp = creator_products[cid]
        cp["total_videos"] += 1
        cp["total_views"] += views or 0
        pp = cp["products"][shop_url or "unknown"]
        pp["videos"] += 1
        pp["total_views"] += views or 0
        pp["total_likes"] += likes or 0
        if score:
            pp["scores"].append(score)

    # Get creator details
    creator_ids = list(creator_products.keys())[:limit]
    creators = {c.id: c for c in db.query(Creator).filter(Creator.id.in_(creator_ids)).all()}

    network = []
    for cid in creator_ids:
        c = creators.get(cid)
        if not c:
            continue
        cp = creator_products[cid]

        products_list = []
        for url, pd in cp["products"].items():
            avg_score = sum(pd["scores"]) / len(pd["scores"]) if pd["scores"] else 0
            products_list.append({
                "url": url,
                "videos": pd["videos"],
                "total_views": pd["total_views"],
                "total_likes": pd["total_likes"],
                "avg_score": round(avg_score, 1),
            })
        products_list.sort(key=lambda x: x["total_views"], reverse=True)

        network.append({
            "creator_id": cid,
            "username": c.username or "",
            "platform": c.platform or "",
            "followers": c.followers or 0,
            "size_tier": c.size_tier or "",
            "avatar_url": c.avatar_url or "",
            "total_shop_videos": cp["total_videos"],
            "total_views": cp["total_views"],
            "products_promoted": len(cp["products"]),
            "top_products": products_list[:5],
        })

    network.sort(key=lambda x: x["total_views"], reverse=True)

    # Top products promoted (global)
    product_totals = defaultdict(lambda: {"creators": set(), "videos": 0, "views": 0})
    for cid, shop_url, views, likes, score in videos:
        pt = product_totals[shop_url or "unknown"]
        pt["creators"].add(cid)
        pt["videos"] += 1
        pt["views"] += views or 0

    top_products = []
    for url, pd in product_totals.items():
        top_products.append({
            "url": url,
            "creator_count": len(pd["creators"]),
            "video_count": pd["videos"],
            "total_views": pd["views"],
        })
    top_products.sort(key=lambda x: x["total_views"], reverse=True)

    return {
        "creators": network[:limit],
        "top_products": top_products[:20],
        "total_creators": len(creator_products),
        "total_links": sum(cp["total_videos"] for cp in creator_products.values()),
    }


# ── Commission Calculator ────────────────────────────────────

COMMISSION_TIERS = {
    "moda": {"min": 5, "max": 15, "avg": 10},
    "beleza": {"min": 8, "max": 20, "avg": 12},
    "casa": {"min": 5, "max": 15, "avg": 8},
    "eletronicos": {"min": 3, "max": 10, "avg": 5},
    "esportes": {"min": 5, "max": 15, "avg": 8},
    "saude": {"min": 10, "max": 25, "avg": 15},
    "outros": {"min": 3, "max": 15, "avg": 8},
}


def calculate_commission(price: float, category: str = "outros",
                         daily_sales: float = 10,
                         commission_pct: float = 0) -> dict:
    """
    Estimate affiliate commission for a product.
    """
    tier = COMMISSION_TIERS.get(category, COMMISSION_TIERS["outros"])
    if commission_pct <= 0:
        commission_pct = tier["avg"]

    per_sale = price * (commission_pct / 100)
    daily_revenue = per_sale * daily_sales
    monthly_revenue = daily_revenue * 30

    return {
        "price": price,
        "category": category,
        "commission_percent": commission_pct,
        "commission_range": f"{tier['min']}% - {tier['max']}%",
        "per_sale": round(per_sale, 2),
        "daily_sales": daily_sales,
        "daily_revenue": round(daily_revenue, 2),
        "monthly_revenue": round(monthly_revenue, 2),
        "monthly_formatted": _format_currency(monthly_revenue),
        "tier": "ouro" if monthly_revenue >= 5000 else "prata" if monthly_revenue >= 1000 else "bronze",
    }


def get_top_commission_products(db: Session, days: int = 7, limit: int = 20) -> list[dict]:
    """
    Find products with highest estimated commission opportunity.
    Score = price × sales velocity × commission rate.
    """
    since = datetime.utcnow() - timedelta(days=days)

    # Get products with velocity data
    subq = (
        db.query(
            ProductSnapshot.product_id,
            func.min(ProductSnapshot.sold_count).label("first_sold"),
            func.max(ProductSnapshot.sold_count).label("last_sold"),
            func.min(ProductSnapshot.captured_at).label("first_at"),
            func.max(ProductSnapshot.captured_at).label("last_at"),
        )
        .filter(ProductSnapshot.captured_at >= since)
        .group_by(ProductSnapshot.product_id)
        .having(func.count(ProductSnapshot.id) >= 2)
        .subquery()
    )

    results = (
        db.query(
            Product,
            subq.c.first_sold,
            subq.c.last_sold,
            subq.c.first_at,
            subq.c.last_at,
        )
        .join(subq, Product.product_id == subq.c.product_id)
        .all()
    )

    opportunities = []
    for product, first_sold, last_sold, first_at, last_at in results:
        first_sold = int(first_sold or 0)
        last_sold = int(last_sold or 0)

        delta = last_sold - first_sold
        if delta <= 0:
            continue

        hours = max(1, (last_at - first_at).total_seconds() / 3600)
        daily_sales = (delta / hours) * 24

        price = _parse_price(product.price)
        if price <= 0:
            continue

        cat = product.category or "outros"
        tier = COMMISSION_TIERS.get(cat, COMMISSION_TIERS["outros"])
        commission_pct = tier["avg"]
        per_sale = price * (commission_pct / 100)
        monthly_est = per_sale * daily_sales * 30

        opportunities.append({
            **product.to_dict(),
            "daily_sales": round(daily_sales, 1),
            "commission_percent": commission_pct,
            "per_sale": round(per_sale, 2),
            "monthly_estimate": round(monthly_est, 2),
            "monthly_formatted": _format_currency(monthly_est),
            "opportunity_score": round(monthly_est / 100, 1),
        })

    opportunities.sort(key=lambda x: x["monthly_estimate"], reverse=True)
    return opportunities[:limit]


# ── Category Trend Heatmap ───────────────────────────────────

def get_category_heatmap(db: Session, days: int = 7) -> dict:
    """
    Generate heatmap data showing which product categories are hottest.
    Based on video volume, engagement, and shop product presence.
    """
    since = datetime.utcnow() - timedelta(days=days)
    prev_since = since - timedelta(days=days)

    from backend.services.shop_intelligence import CATEGORY_KEYWORDS, _NICHO_NAMES

    categories = {}
    for cat_key, keywords in CATEGORY_KEYWORDS.items():
        conditions = []
        for kw in keywords:
            conditions.append(Video.title.ilike(f"%{kw}%"))
            conditions.append(Video.description.ilike(f"%{kw}%"))
            conditions.append(Video.hashtags.cast(String).ilike(f"%{kw}%"))

        # Current period
        current = db.query(
            func.count(Video.id),
            func.avg(Video.viral_score),
            func.sum(Video.views),
            func.count(func.distinct(Video.creator_id)),
        ).filter(
            Video.collected_at >= since,
            or_(*conditions),
        ).first()

        # Previous period
        prev_count = db.query(func.count(Video.id)).filter(
            Video.collected_at >= prev_since,
            Video.collected_at < since,
            or_(*conditions),
        ).scalar() or 0

        # Shop videos
        shop_count = db.query(func.count(Video.id)).filter(
            Video.collected_at >= since,
            or_(*conditions),
            Video.shop_product_url != "",
            Video.shop_product_url.isnot(None),
        ).scalar() or 0

        count = current[0] or 0
        growth = round(((count - prev_count) / max(prev_count, 1)) * 100, 1) if prev_count > 0 else (100.0 if count > 0 else 0)

        # Heat score: combo of volume, growth, and viral score
        avg_score = float(current[1] or 0)
        heat = round((count * 0.3 + growth * 0.3 + avg_score * 0.4), 1)

        categories[cat_key] = {
            "name": _NICHO_NAMES.get(cat_key, cat_key),
            "video_count": count,
            "avg_score": round(avg_score, 1),
            "total_views": int(current[2] or 0),
            "creator_count": int(current[3] or 0),
            "shop_count": shop_count,
            "shop_rate": round((shop_count / max(count, 1)) * 100, 1),
            "growth_percent": growth,
            "heat_score": heat,
            "status": "fire" if growth > 100 else "hot" if growth > 30 else "warm" if growth > 0 else "cold",
        }

    # Sort by heat score
    sorted_cats = dict(sorted(categories.items(), key=lambda x: x[1]["heat_score"], reverse=True))
    return sorted_cats


# ── Product Opportunity Score ────────────────────────────────

def score_product_opportunity(db: Session, product_id: str) -> dict:
    """
    Calculate an opportunity score for a product considering multiple factors:
    - Sales velocity
    - Competition (how many creators promote it)
    - Price point (commission potential)
    - Lifecycle stage
    - Viral video count
    """
    product = db.query(Product).filter(Product.product_id == product_id).first()
    if not product:
        return {"error": "Produto nao encontrado"}

    lifecycle = analyze_product_lifecycle(db, product_id)
    price = _parse_price(product.price)

    # Sales velocity from snapshots
    since = datetime.utcnow() - timedelta(days=7)
    snaps = (
        db.query(ProductSnapshot)
        .filter(ProductSnapshot.product_id == product_id, ProductSnapshot.captured_at >= since)
        .order_by(ProductSnapshot.captured_at.asc())
        .all()
    )

    daily_velocity = 0
    if len(snaps) >= 2:
        delta = snaps[-1].sold_count - snaps[0].sold_count
        hours = max(1, (snaps[-1].captured_at - snaps[0].captured_at).total_seconds() / 3600)
        daily_velocity = (delta / hours) * 24

    # Competition: count creators promoting this product
    creator_count = db.query(func.count(func.distinct(Video.creator_id))).filter(
        Video.shop_product_url.ilike(f"%{product_id}%"),
    ).scalar() or 0

    # Video count
    video_count = db.query(func.count(Video.id)).filter(
        Video.shop_product_url.ilike(f"%{product_id}%"),
    ).scalar() or 0

    # Scoring components (0-100 each)
    velocity_score = min(100, daily_velocity * 2)
    price_score = min(100, price * 2) if price < 50 else 80  # Sweet spot is affordable
    lifecycle_score = {"growth": 100, "launch": 70, "maturity": 50, "decline": 20, "unknown": 30}
    stage_score = lifecycle_score.get(lifecycle["stage"], 30)
    competition_score = 100 if creator_count < 5 else 70 if creator_count < 20 else 40
    video_score = min(100, video_count * 10)

    total = round(
        velocity_score * 0.30 +
        price_score * 0.15 +
        stage_score * 0.25 +
        competition_score * 0.15 +
        video_score * 0.15
    , 1)

    cat = product.category or "outros"
    tier = COMMISSION_TIERS.get(cat, COMMISSION_TIERS["outros"])
    est_monthly = price * (tier["avg"] / 100) * daily_velocity * 30

    return {
        **product.to_dict(),
        "opportunity_score": total,
        "label": "Excelente" if total >= 75 else "Bom" if total >= 50 else "Medio" if total >= 30 else "Baixo",
        "lifecycle": lifecycle,
        "daily_velocity": round(daily_velocity, 1),
        "creator_count": creator_count,
        "video_count": video_count,
        "price_value": price,
        "est_monthly_commission": round(est_monthly, 2),
        "est_monthly_formatted": _format_currency(est_monthly),
        "breakdown": {
            "velocity": round(velocity_score, 1),
            "price_appeal": round(price_score, 1),
            "lifecycle_stage": round(stage_score, 1),
            "low_competition": round(competition_score, 1),
            "viral_reach": round(video_score, 1),
        },
    }


# ── Helpers ──────────────────────────────────────────────────

import re

def _parse_price(price_str) -> float:
    if not price_str:
        return 0.0
    price_str = str(price_str).split("-")[0].strip()
    cleaned = re.sub(r"[^\d.,]", "", price_str)
    if "," in cleaned and "." in cleaned:
        cleaned = cleaned.replace(".", "").replace(",", ".")
    elif "," in cleaned:
        cleaned = cleaned.replace(",", ".")
    try:
        return float(cleaned)
    except ValueError:
        return 0.0


def _format_currency(value: float) -> str:
    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}"


# ── Review Sentiment Analysis ───────────────────────────────

def analyze_product_reviews(db: Session, product_id: str) -> dict:
    """
    Analyze sentiment of product reviews using VADER.
    Returns aggregate sentiment + per-review breakdown.
    """
    from backend.services.local_ai import analyze_sentiment, analyze_sentiment_batch

    product = db.query(Product).filter(Product.product_id == product_id).first()
    if not product:
        return {"error": "Produto nao encontrado"}

    # Collect review-like texts from related videos (comments about the product)
    related_texts = []

    # Use product title fragments to find relevant video descriptions/titles
    videos = db.query(Video).filter(
        or_(
            Video.shop_product_url.ilike(f"%{product_id}%"),
            Video.title.ilike(f"%{product.title[:25]}%") if product.title and len(product.title) > 5 else False,
        )
    ).limit(50).all()

    for v in videos:
        if v.title:
            related_texts.append(v.title)
        if v.description and len(v.description) > 20:
            related_texts.append(v.description[:300])

    # Also analyze the product's own description
    if product.description and len(product.description) > 10:
        related_texts.append(product.description[:500])

    if not related_texts:
        return {
            "product_id": product_id,
            "product_name": product.title[:60] if product.title else product_id,
            "sentiment": {"label": "sem dados", "score": 0},
            "review_count": product.review_count or 0,
            "rating": product.rating or 0,
            "related_videos": 0,
            "analysis": {"average": 0, "distribution": {"positivo": 0, "negativo": 0, "neutro": 0}, "total": 0},
        }

    batch_result = analyze_sentiment_batch(related_texts)

    # Determine overall sentiment
    dist = batch_result["distribution"]
    if dist["positivo"] > dist["negativo"] * 2:
        overall = "muito positivo"
    elif dist["positivo"] > dist["negativo"]:
        overall = "positivo"
    elif dist["negativo"] > dist["positivo"] * 2:
        overall = "muito negativo"
    elif dist["negativo"] > dist["positivo"]:
        overall = "negativo"
    else:
        overall = "neutro"

    return {
        "product_id": product_id,
        "product_name": product.title[:60] if product.title else product_id,
        "sentiment": {"label": overall, "score": batch_result["average"]},
        "review_count": product.review_count or 0,
        "rating": product.rating or 0,
        "related_videos": len(videos),
        "analysis": batch_result,
        "recommendation": _sentiment_recommendation(overall, product.rating),
    }


def _sentiment_recommendation(sentiment: str, rating: float) -> str:
    if sentiment in ("muito positivo", "positivo") and rating >= 4.5:
        return "Produto com excelente recepcao! Otimo para promover como afiliado."
    elif sentiment == "positivo" and rating >= 4.0:
        return "Boa recepcao do publico. Produto confiavel para promover."
    elif sentiment == "neutro":
        return "Recepcao neutra. Avalie se o produto tem diferencial suficiente."
    elif sentiment in ("negativo", "muito negativo"):
        return "Atencao: sentimento negativo detectado. Avalie reviews antes de promover."
    return "Avalie os dados de sentimento para tomar sua decisao."


# ── Store Revenue Estimator ─────────────────────────────────

def estimate_store_revenue(db: Session, shop_name: str = "", days: int = 30,
                           limit: int = 20) -> dict:
    """
    Estimate store/seller revenue based on product snapshots and prices.
    """
    since = datetime.utcnow() - timedelta(days=days)

    query = db.query(Product)
    if shop_name:
        query = query.filter(
            or_(
                Product.shop_name.ilike(f"%{shop_name}%"),
                Product.seller_name.ilike(f"%{shop_name}%"),
            )
        )

    products = query.all()
    if not products:
        return {"error": "Nenhuma loja encontrada", "stores": []}

    # Group products by store
    stores = defaultdict(lambda: {
        "products": [], "total_revenue": 0, "total_sold": 0,
        "avg_rating": [], "categories": Counter(),
    })

    for p in products:
        store_key = p.shop_name or p.seller_name or "Desconhecido"
        price = _parse_price(p.price)

        # Get velocity from snapshots
        snaps = (
            db.query(ProductSnapshot)
            .filter(ProductSnapshot.product_id == p.product_id,
                    ProductSnapshot.captured_at >= since)
            .order_by(ProductSnapshot.captured_at.asc())
            .all()
        )

        daily_velocity = 0
        if len(snaps) >= 2:
            delta = (snaps[-1].sold_count or 0) - (snaps[0].sold_count or 0)
            hours = max(1, (snaps[-1].captured_at - snaps[0].captured_at).total_seconds() / 3600)
            daily_velocity = max(0, (delta / hours) * 24)

        est_monthly = price * daily_velocity * 30

        s = stores[store_key]
        s["products"].append({
            "product_id": p.product_id,
            "title": p.title[:50] if p.title else "",
            "price": price,
            "daily_velocity": round(daily_velocity, 1),
            "monthly_revenue": round(est_monthly, 2),
        })
        s["total_revenue"] += est_monthly
        s["total_sold"] += p.sold_count or 0
        if p.rating:
            s["avg_rating"].append(p.rating)
        if p.category:
            s["categories"][p.category] += 1

    # Build result
    store_list = []
    for name, data in stores.items():
        avg_r = sum(data["avg_rating"]) / len(data["avg_rating"]) if data["avg_rating"] else 0
        top_cat = data["categories"].most_common(1)[0][0] if data["categories"] else "N/A"
        store_list.append({
            "shop_name": name,
            "product_count": len(data["products"]),
            "total_sold": data["total_sold"],
            "estimated_monthly_revenue": round(data["total_revenue"], 2),
            "revenue_formatted": _format_currency(data["total_revenue"]),
            "avg_rating": round(avg_r, 1),
            "top_category": top_cat,
            "top_products": sorted(data["products"], key=lambda x: x["monthly_revenue"], reverse=True)[:5],
            "tier": "mega" if data["total_revenue"] >= 100000 else "grande" if data["total_revenue"] >= 30000 else "medio" if data["total_revenue"] >= 5000 else "pequeno",
        })

    store_list.sort(key=lambda x: x["estimated_monthly_revenue"], reverse=True)

    return {
        "stores": store_list[:limit],
        "total_stores": len(store_list),
        "total_estimated_revenue": round(sum(s["estimated_monthly_revenue"] for s in store_list), 2),
    }


# ── Affiliate Matcher (Video x Product) ─────────────────────

def match_affiliates(db: Session, days: int = 14, limit: int = 30) -> list[dict]:
    """
    Cross-reference viral videos with products to suggest affiliate opportunities.
    Matches videos by content (keywords/hashtags) to product categories.
    """
    since = datetime.utcnow() - timedelta(days=days)

    # Get viral videos without shop products (untapped opportunities)
    videos = (
        db.query(Video)
        .filter(
            Video.collected_at >= since,
            Video.viral_score >= 40,
            or_(Video.shop_product_url == "", Video.shop_product_url.is_(None)),
        )
        .order_by(desc(Video.viral_score))
        .limit(100)
        .all()
    )

    # Get top products with good velocity
    products = (
        db.query(Product)
        .filter(Product.sold_count >= 50)
        .order_by(desc(Product.sold_count))
        .limit(200)
        .all()
    )

    if not videos or not products:
        return []

    matches = []
    for v in videos:
        text = ((v.title or "") + " " + (v.description or "") + " " + str(v.hashtags or "")).lower()

        best_match = None
        best_score = 0

        for p in products:
            match_score = 0
            p_title = (p.title or "").lower()
            p_cat = (p.category or "").lower()

            # Title word overlap
            p_words = set(w for w in p_title.split() if len(w) > 3)
            for word in p_words:
                if word in text:
                    match_score += 10

            # Category match
            if p_cat and p_cat in text:
                match_score += 15

            # Same niche indicators
            niche_maps = {
                "beleza": ["makeup", "skincare", "maquiagem", "pele", "beauty"],
                "moda": ["outfit", "look", "roupa", "fashion", "style"],
                "fitness": ["treino", "gym", "workout", "academia", "suplemento"],
                "tech": ["gadget", "tech", "smartphone", "setup", "acessorio"],
                "casa": ["decoracao", "casa", "home", "cozinha", "organizacao"],
            }
            for niche, keywords in niche_maps.items():
                video_has = any(k in text for k in keywords)
                product_has = any(k in p_title or k in p_cat for k in keywords)
                if video_has and product_has:
                    match_score += 20

            if match_score > best_score:
                best_score = match_score
                best_match = p

        if best_match and best_score >= 10:
            price = _parse_price(best_match.price)
            tier = COMMISSION_TIERS.get(best_match.category or "outros", COMMISSION_TIERS["outros"])
            est_commission = price * (tier["avg"] / 100)

            matches.append({
                "video": {
                    "id": v.id,
                    "title": v.title[:60] if v.title else "",
                    "platform": v.platform,
                    "views": v.views,
                    "viral_score": v.viral_score,
                    "url": v.url,
                },
                "product": {
                    **best_match.to_dict(),
                },
                "match_score": best_score,
                "estimated_commission": round(est_commission, 2),
                "suggestion": f"Este video viral pode promover '{best_match.title[:40]}' (est. R${est_commission:.2f}/venda)",
            })

    matches.sort(key=lambda x: x["match_score"], reverse=True)
    return matches[:limit]


# ── Product Suggestion by Niche (IA) ────────────────────────

async def suggest_products_by_niche(db: Session, niche: str = "",
                                     limit: int = 10) -> dict:
    """
    Use AI + data analysis to suggest ideal products for a niche.
    Combines sales data with AI recommendations.
    """
    from backend.services.local_ai import llm_chat

    # Get top products by sales velocity
    since = datetime.utcnow() - timedelta(days=14)
    products = (
        db.query(Product)
        .filter(Product.sold_count >= 20)
        .order_by(desc(Product.sold_count))
        .limit(100)
        .all()
    )

    if niche:
        # Filter products related to niche
        niche_keywords = {
            "beleza": ["makeup", "skincare", "maquiagem", "pele", "beauty", "creme", "serum"],
            "moda": ["roupa", "vestido", "camiseta", "tenis", "sapato", "bolsa", "fashion"],
            "fitness": ["suplemento", "whey", "treino", "academia", "yoga", "esporte"],
            "tech": ["gadget", "fone", "carregador", "cabo", "led", "smart", "acessorio"],
            "casa": ["decoracao", "organizador", "cozinha", "banheiro", "lampada", "tapete"],
            "saude": ["vitamina", "colageno", "natural", "organico", "saude", "bem estar"],
        }
        keywords = niche_keywords.get(niche, [niche])
        products = [p for p in products if any(
            k in (p.title or "").lower() or k in (p.category or "").lower()
            for k in keywords
        )]

    if not products:
        return {"niche": niche, "products": [], "ai_analysis": "Sem produtos encontrados para este nicho."}

    # Build product summary for AI
    product_summary = "\n".join([
        f"- {p.title[:50]} | R${p.price} | {p.sold_count} vendidos | Rating: {p.rating}"
        for p in products[:20]
    ])

    # AI analysis
    system = "Voce e um especialista em e-commerce e marketing de afiliados no Brasil. Responda em portugues."
    prompt = f"""Analise estes produtos do nicho '{niche or "geral"}' e recomende os 5 melhores para promover como afiliado.

Produtos disponíveis:
{product_summary}

Responda com:
1. Top 5 produtos recomendados e por que
2. Estrategia de conteudo para cada um
3. Estimativa de potencial de ganho

Seja pratico e direto."""

    ai_analysis = await llm_chat(prompt, system=system, temperature=0.3, max_tokens=1500)

    # Data-driven recommendations
    scored = []
    for p in products[:30]:
        price = _parse_price(p.price)
        tier = COMMISSION_TIERS.get(p.category or "outros", COMMISSION_TIERS["outros"])
        est_monthly = price * (tier["avg"] / 100) * max(1, p.sold_count / 30)
        scored.append({
            **p.to_dict(),
            "estimated_monthly_commission": round(est_monthly, 2),
            "commission_formatted": _format_currency(est_monthly),
            "commission_percent": tier["avg"],
        })

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

    return {
        "niche": niche or "geral",
        "products": scored[:limit],
        "ai_analysis": ai_analysis or "Analise IA indisponivel no momento.",
        "total_found": len(products),
    }


# ── Commission Optimizer ─────────────────────────────────────

def optimize_commission_mix(db: Session, budget_hours: int = 20,
                            days: int = 7) -> dict:
    """
    Optimize product mix to maximize total commission.
    Given limited time (budget_hours of content creation per month),
    recommends the best products to promote.
    """
    since = datetime.utcnow() - timedelta(days=days)

    subq = (
        db.query(
            ProductSnapshot.product_id,
            func.min(ProductSnapshot.sold_count).label("first_sold"),
            func.max(ProductSnapshot.sold_count).label("last_sold"),
            func.min(ProductSnapshot.captured_at).label("first_at"),
            func.max(ProductSnapshot.captured_at).label("last_at"),
        )
        .filter(ProductSnapshot.captured_at >= since)
        .group_by(ProductSnapshot.product_id)
        .having(func.count(ProductSnapshot.id) >= 2)
        .subquery()
    )

    results = (
        db.query(
            Product,
            subq.c.first_sold,
            subq.c.last_sold,
            subq.c.first_at,
            subq.c.last_at,
        )
        .join(subq, Product.product_id == subq.c.product_id)
        .all()
    )

    candidates = []
    for product, first_sold, last_sold, first_at, last_at in results:
        first_sold = int(first_sold or 0)
        last_sold = int(last_sold or 0)
        delta = last_sold - first_sold
        if delta <= 0:
            continue

        hours = max(1, (last_at - first_at).total_seconds() / 3600)
        daily_sales = (delta / hours) * 24
        price = _parse_price(product.price)
        if price <= 0:
            continue

        cat = product.category or "outros"
        tier = COMMISSION_TIERS.get(cat, COMMISSION_TIERS["outros"])
        per_sale = price * (tier["avg"] / 100)
        monthly_est = per_sale * daily_sales * 30

        # Effort estimate (hours to create content for this product)
        effort = 2 if price < 30 else 3 if price < 100 else 4  # hours per video

        # ROI = monthly commission / effort hours
        roi = monthly_est / effort

        candidates.append({
            **product.to_dict(),
            "daily_sales": round(daily_sales, 1),
            "commission_per_sale": round(per_sale, 2),
            "monthly_estimate": round(monthly_est, 2),
            "monthly_formatted": _format_currency(monthly_est),
            "effort_hours": effort,
            "roi_per_hour": round(roi, 2),
            "roi_formatted": _format_currency(roi),
        })

    # Sort by ROI per hour
    candidates.sort(key=lambda x: x["roi_per_hour"], reverse=True)

    # Greedy knapsack: fill budget_hours with highest ROI
    selected = []
    hours_used = 0
    total_monthly = 0
    for c in candidates:
        if hours_used + c["effort_hours"] <= budget_hours:
            selected.append(c)
            hours_used += c["effort_hours"]
            total_monthly += c["monthly_estimate"]

    return {
        "budget_hours": budget_hours,
        "hours_used": hours_used,
        "selected_products": selected,
        "total_monthly_estimate": round(total_monthly, 2),
        "total_formatted": _format_currency(total_monthly),
        "avg_roi_per_hour": round(total_monthly / max(hours_used, 1), 2),
        "total_candidates": len(candidates),
    }


# ── Seasonal Product Predictor ───────────────────────────────

SEASONAL_EVENTS = {
    1: ["ano novo", "verao", "volta as aulas", "liquidacao"],
    2: ["carnaval", "verao"],
    3: ["dia da mulher", "outono"],
    4: ["pascoa"],
    5: ["dia das maes"],
    6: ["dia dos namorados", "inverno", "festa junina"],
    7: ["inverno", "festa julina", "ferias"],
    8: ["dia dos pais"],
    9: ["primavera", "dia do cliente"],
    10: ["dia das criancas", "halloween"],
    11: ["black friday", "esquenta natal"],
    12: ["natal", "reveillon", "presentes", "verao"],
}

SEASONAL_PRODUCTS = {
    "carnaval": ["fantasia", "glitter", "acessorio", "maquiagem", "protetor solar"],
    "dia das maes": ["presente", "joias", "perfume", "bolsa", "skincare", "flor"],
    "dia dos namorados": ["presente", "perfume", "joias", "chocolate", "lingerie"],
    "dia dos pais": ["relogio", "carteira", "perfume", "tech", "ferramenta"],
    "dia das criancas": ["brinquedo", "jogo", "roupa infantil", "boneca", "educativo"],
    "black friday": ["eletronico", "smartphone", "tv", "fone", "desconto"],
    "natal": ["presente", "decoracao natal", "brinquedo", "roupa", "kit"],
    "verao": ["praia", "biquini", "protetor solar", "oculos", "chinelo", "piscina"],
    "inverno": ["casaco", "cobertor", "aquecedor", "chocolate quente", "moletom"],
    "volta as aulas": ["mochila", "caderno", "estojo", "caneta", "uniforme"],
}


def predict_seasonal_products(db: Session, months_ahead: int = 2) -> dict:
    """
    Predict which products will be in demand based on upcoming seasonal events.
    """
    now = datetime.utcnow()
    predictions = []

    for offset in range(months_ahead + 1):
        month = (now.month + offset - 1) % 12 + 1
        month_name = ["Jan", "Fev", "Mar", "Abr", "Mai", "Jun",
                       "Jul", "Ago", "Set", "Out", "Nov", "Dez"][month - 1]
        events = SEASONAL_EVENTS.get(month, [])

        for event in events:
            # Find matching products in DB
            keywords = SEASONAL_PRODUCTS.get(event, [event])
            conditions = []
            for kw in keywords:
                conditions.append(Product.title.ilike(f"%{kw}%"))
                conditions.append(Product.category.ilike(f"%{kw}%"))

            matching = (
                db.query(Product)
                .filter(or_(*conditions))
                .order_by(desc(Product.sold_count))
                .limit(5)
                .all()
            )

            predictions.append({
                "month": month,
                "month_name": month_name,
                "event": event.title(),
                "offset_months": offset,
                "is_current": offset == 0,
                "keywords": keywords,
                "matching_products": [p.to_dict() for p in matching],
                "product_count": len(matching),
                "tip": f"Prepare conteudo para '{event}' com {max(offset, 1)} mes de antecedencia" if offset > 0
                       else f"'{event.title()}' esta acontecendo AGORA! Aproveite a demanda.",
            })

    # Sort: current events first, then by offset
    predictions.sort(key=lambda x: (x["offset_months"], -x["product_count"]))

    return {
        "predictions": predictions,
        "current_month": now.month,
        "months_analyzed": months_ahead + 1,
    }


# ── Viral Product Predictor (AI) ──────────────────────────────

async def predict_viral_products(db: Session, days: int = 14, limit: int = 10) -> dict:
    """
    Predict which products are about to go viral using acceleration scoring + AI analysis.
    Score = velocity_acceleration(30%) + video_growth(25%) + sentiment(20%) + niche_momentum(15%) + price(10%)
    """
    from backend.services.local_ai import llm_chat, analyze_sentiment_batch

    since = datetime.utcnow() - timedelta(days=days)
    half = datetime.utcnow() - timedelta(days=days // 2)

    products = db.query(Product).filter(Product.sold_count >= 5).all()
    if not products:
        return {"predictions": [], "total": 0}

    scored = []
    for p in products:
        price = _parse_price(p.price)

        # Velocity acceleration: compare first half vs second half sales
        snaps_old = db.query(ProductSnapshot).filter(
            ProductSnapshot.product_id == p.id,
            ProductSnapshot.captured_at >= since,
            ProductSnapshot.captured_at < half,
        ).all()
        snaps_new = db.query(ProductSnapshot).filter(
            ProductSnapshot.product_id == p.id,
            ProductSnapshot.captured_at >= half,
        ).all()

        old_sales = sum(s.sold_count or 0 for s in snaps_old) / max(len(snaps_old), 1)
        new_sales = sum(s.sold_count or 0 for s in snaps_new) / max(len(snaps_new), 1)
        acceleration = ((new_sales - old_sales) / max(old_sales, 1)) * 100

        # Video growth: count related videos
        video_count = db.query(func.count(Video.id)).filter(
            Video.collected_at >= since,
            or_(
                Video.title.ilike(f"%{p.title[:30]}%"),
                Video.description.ilike(f"%{p.title[:30]}%"),
            ),
        ).scalar() or 0

        # Price sweetspot (10-150 BRL = optimal for impulse buy)
        price_score = 100 if 10 <= price <= 150 else max(0, 100 - abs(price - 80))

        # Combined score
        total = (
            min(acceleration, 100) * 0.30 +
            min(video_count * 10, 100) * 0.25 +
            50 * 0.20 +  # sentiment placeholder
            min(acceleration * 0.5, 100) * 0.15 +
            min(price_score, 100) * 0.10
        )

        if total > 20:
            scored.append({
                **p.to_dict(),
                "viral_prediction_score": round(total, 1),
                "acceleration": round(acceleration, 1),
                "video_mentions": video_count,
                "price_score": round(price_score, 1),
                "price_formatted": _format_currency(price),
            })

    scored.sort(key=lambda x: x["viral_prediction_score"], reverse=True)
    top = scored[:limit]

    # AI Commentary on top products
    ai_commentary = ""
    if top:
        summary = "\n".join([
            f"- {p['title'][:50]} | Score: {p['viral_prediction_score']} | Aceleracao: {p['acceleration']}% | Videos: {p['video_mentions']}"
            for p in top[:5]
        ])
        system = "Voce e um analista de produtos virais no TikTok Shop. Responda em portugues, maximo 200 palavras."
        prompt = f"Estes produtos estao acelerando. Analise o potencial viral e de recomendacoes:\n{summary}"
        ai_commentary = await llm_chat(prompt, system=system, temperature=0.3, max_tokens=600)

    return {
        "predictions": top,
        "total": len(scored),
        "days_analyzed": days,
        "ai_commentary": ai_commentary or "Analise IA indisponivel.",
    }


# ── Product Video Template Generator (AI) ─────────────────────

async def generate_product_video_template(db: Session, product_id: int,
                                           style: str = "review") -> dict:
    """
    Generate a structured video script template for a product.
    Styles: review, unboxing, comparacao, tutorial, storytelling
    """
    from backend.services.local_ai import llm_chat

    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        return {"success": False, "error": "Produto nao encontrado"}

    style_prompts = {
        "review": "Crie um script de REVIEW honesto e engajante",
        "unboxing": "Crie um script de UNBOXING empolgante com reacao genuina",
        "comparacao": "Crie um script COMPARANDO este produto com alternativas",
        "tutorial": "Crie um script TUTORIAL mostrando como usar o produto",
        "storytelling": "Crie um script STORYTELLING com narrativa emocional sobre o produto",
    }

    style_prompt = style_prompts.get(style, style_prompts["review"])

    system = """Voce e um roteirista de videos virais para TikTok/Reels. Retorne APENAS um JSON valido (sem markdown, sem ```).
Formato exato:
{"hook": "primeiros 3 segundos", "intro": "introducao 5-10s", "demo": "demonstracao do produto 15-20s", "prova_social": "prova social ou resultado", "cta": "call to action final", "hashtags": ["#hash1", "#hash2"], "duracao_ideal": "30-60s", "dicas_gravacao": ["dica1", "dica2"], "musica_sugerida": "tipo de musica"}"""

    prompt = f"""{style_prompt} para:

Produto: {product.title}
Preco: R${product.price}
Categoria: {product.category or 'geral'}
Vendidos: {product.sold_count or 0}
Rating: {product.rating or 'N/A'}

Gere um roteiro viral otimizado para TikTok/Reels."""

    response = await llm_chat(prompt, system=system, temperature=0.4, max_tokens=1200)
    if not response:
        return {"success": False, "error": "LLM indisponivel"}

    import json as json_mod
    import re as re_mod
    try:
        json_match = re_mod.search(r"\{.*\}", response, re_mod.DOTALL)
        if json_match:
            data = json_mod.loads(json_match.group())
            data["success"] = True
            data["product"] = product.to_dict()
            data["style"] = style
            return data
    except (json_mod.JSONDecodeError, AttributeError):
        pass

    return {"success": True, "raw_script": response, "product": product.to_dict(), "style": style}


# ── Sales Script Generator (AI) ──────────────────────────────

async def generate_sales_script(db: Session, product_id: int,
                                 audience: str = "geral",
                                 tone: str = "conversacional") -> dict:
    """
    Generate a conversion-optimized sales script with objection handling,
    urgency triggers, and price anchoring.
    """
    from backend.services.local_ai import llm_chat

    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        return {"success": False, "error": "Produto nao encontrado"}

    system = """Voce e um copywriter expert em vendas para redes sociais. Retorne APENAS um JSON valido (sem markdown).
Formato exato:
{"headline": "titulo persuasivo", "hook": "abertura que prende", "pain_points": ["dor 1", "dor 2"], "solution": "como o produto resolve", "benefits": ["beneficio 1", "beneficio 2", "beneficio 3"], "objection_handling": [{"objection": "objecao", "response": "resposta"}], "urgency": "gatilho de urgencia", "price_anchoring": "ancoragem de preco", "cta": "chamada para acao", "closing": "fechamento emocional"}"""

    prompt = f"""Crie um script de vendas para:

Produto: {product.title}
Preco: R${product.price}
Categoria: {product.category or 'geral'}
Vendidos: {product.sold_count or 0} unidades
Rating: {product.rating or 'N/A'}

Publico-alvo: {audience}
Tom: {tone}

Gere um script persuasivo completo."""

    response = await llm_chat(prompt, system=system, temperature=0.3, max_tokens=1500)
    if not response:
        return {"success": False, "error": "LLM indisponivel"}

    import json as json_mod
    import re as re_mod
    try:
        json_match = re_mod.search(r"\{.*\}", response, re_mod.DOTALL)
        if json_match:
            data = json_mod.loads(json_match.group())
            data["success"] = True
            data["product"] = product.to_dict()
            data["audience"] = audience
            data["tone"] = tone
            return data
    except (json_mod.JSONDecodeError, AttributeError):
        pass

    return {"success": True, "raw_script": response, "product": product.to_dict(), "audience": audience, "tone": tone}


# ── Live Commerce Planner (AI) ──────────────────────────────

async def plan_live_commerce(db: Session, product_ids: list = None,
                              duration_minutes: int = 60) -> dict:
    """
    Plan a live commerce session with schedule, talking points,
    and engagement triggers using AI.
    """
    from backend.services.local_ai import llm_chat

    products = []
    if product_ids:
        products = db.query(Product).filter(Product.id.in_(product_ids)).all()
    else:
        # Auto-select top products
        products = (
            db.query(Product)
            .filter(Product.sold_count >= 10)
            .order_by(desc(Product.sold_count))
            .limit(5)
            .all()
        )

    if not products:
        return {"success": False, "error": "Nenhum produto encontrado"}

    product_list = "\n".join([
        f"- {p.title[:60]} | R${p.price} | {p.sold_count} vendidos | Rating: {p.rating or 'N/A'}"
        for p in products
    ])

    system = """Voce e um estrategista de live commerce no TikTok/Shopee. Retorne APENAS um JSON valido (sem markdown).
Formato exato:
{"title": "titulo da live", "schedule": [{"minute": 0, "duration": 5, "activity": "abertura", "script": "o que falar", "product": "produto ou null"}], "engagement_triggers": [{"minute": 10, "action": "sorteio/enquete/desconto flash"}], "talking_points": ["ponto 1", "ponto 2"], "setup_checklist": ["item 1", "item 2"], "estimated_revenue": "R$X-Y", "tips": ["dica 1", "dica 2"]}"""

    prompt = f"""Planeje uma LIVE COMMERCE de {duration_minutes} minutos com estes produtos:

{product_list}

Crie um plano completo com cronograma minuto-a-minuto, gatilhos de engajamento e scripts."""

    response = await llm_chat(prompt, system=system, temperature=0.4, max_tokens=2000)
    if not response:
        return {"success": False, "error": "LLM indisponivel"}

    import json as json_mod
    import re as re_mod
    try:
        json_match = re_mod.search(r"\{.*\}", response, re_mod.DOTALL)
        if json_match:
            data = json_mod.loads(json_match.group())
            data["success"] = True
            data["products"] = [p.to_dict() for p in products]
            data["duration_minutes"] = duration_minutes
            return data
    except (json_mod.JSONDecodeError, AttributeError):
        pass

    return {"success": True, "raw_plan": response, "products": [p.to_dict() for p in products], "duration_minutes": duration_minutes}
