"""
EngageBot - AI-powered Engagement & Community Automation.
Features: auto-reply, spam filter, sentiment analysis, FAQ detection, pain point detection.
"""

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

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

from backend.models.video import Video

logger = logging.getLogger(__name__)


# ── Keyword / pattern constants ───────────────────────────────────

SPAM_KEYWORDS = [
    "compre agora", "clique no link", "ganhe dinheiro", "renda extra",
    "trabalhe de casa", "lucre", "fique rico", "link na bio", "compra",
    "promoção imperdível", "oferta", "desconto exclusivo", "free followers",
    "buy now", "click here", "earn money", "make money", "get rich",
    "dm me", "check my profile", "follow me back", "f4f", "s4s",
    "whatsapp", "telegram group", "join now", "limited offer",
]

HATE_KEYWORDS = [
    "lixo", "porcaria", "idiota", "burro", "imbecil", "otario",
    "nojento", "ridiculo", "palhaço", "merda", "bosta",
    "trash", "garbage", "idiot", "stupid", "moron", "loser", "ugly",
    "hate", "worst", "disgusting", "pathetic",
]

QUESTION_PATTERNS = [
    r"\?$", r"\?[\s!]*$", r"^como\s", r"^qual\s", r"^quanto\s",
    r"^quando\s", r"^onde\s", r"^por\s?que\s", r"^o\s?que\s",
    r"^quem\s", r"^pode\s", r"^será\s", r"^dá\s?pra\s",
    r"^how\s", r"^what\s", r"^why\s", r"^where\s", r"^when\s",
    r"^who\s", r"^can\s", r"^could\s", r"^is\s(it|this|that)\s",
    r"alguem\s?sabe", r"someone\s?know",
]

PAIN_KEYWORDS = [
    "não consigo", "não funciona", "problema", "dificuldade", "travou",
    "erro", "bug", "demora", "lento", "confuso", "complicado",
    "frustrado", "irritado", "cansado de", "falta", "preciso de",
    "gostaria que", "deveria ter", "por que não tem",
    "can't", "doesn't work", "problem", "difficult", "stuck",
    "error", "slow", "confusing", "complicated", "frustrated",
    "annoyed", "tired of", "missing", "need", "wish", "should have",
]

POSITIVE_KEYWORDS = [
    "amei", "incrível", "maravilhoso", "top", "demais", "perfeito",
    "parabéns", "show", "adorei", "sensacional", "genial", "massa",
    "loved", "amazing", "wonderful", "great", "perfect", "awesome",
    "excellent", "brilliant", "fantastic", "incredible", "best",
]

NEGATIVE_KEYWORDS = [
    "odiei", "horrível", "péssimo", "ruim", "decepcionado", "fraco",
    "pior", "terrível", "chato", "sem graça", "meh",
    "hated", "horrible", "terrible", "bad", "worst", "awful",
    "disappointing", "boring", "useless", "waste",
]

DEFAULT_TRIGGERS = {
    "preço": {"action": "send_pricing", "label": "Pergunta sobre preço"},
    "price": {"action": "send_pricing", "label": "Price inquiry"},
    "quanto custa": {"action": "send_pricing", "label": "Pergunta sobre preço"},
    "how much": {"action": "send_pricing", "label": "Price inquiry"},
    "valor": {"action": "send_pricing", "label": "Pergunta sobre preço"},
    "colaboração": {"action": "collab_form", "label": "Proposta de colaboração"},
    "collaboration": {"action": "collab_form", "label": "Collaboration proposal"},
    "parceria": {"action": "collab_form", "label": "Proposta de parceria"},
    "partnership": {"action": "collab_form", "label": "Partnership proposal"},
    "link": {"action": "send_link", "label": "Pedido de link"},
    "onde comprar": {"action": "send_link", "label": "Pedido de link de compra"},
    "where to buy": {"action": "send_link", "label": "Purchase link request"},
    "tutorial": {"action": "send_tutorial", "label": "Pedido de tutorial"},
    "como faz": {"action": "send_tutorial", "label": "Pedido de instrução"},
    "how to": {"action": "send_tutorial", "label": "How-to request"},
    "contato": {"action": "send_contact", "label": "Pedido de contato"},
    "contact": {"action": "send_contact", "label": "Contact request"},
    "email": {"action": "send_contact", "label": "Pedido de contato por email"},
    "reclamação": {"action": "flag_complaint", "label": "Reclamação"},
    "complaint": {"action": "flag_complaint", "label": "Complaint"},
    "problema": {"action": "flag_complaint", "label": "Relato de problema"},
    "bug": {"action": "flag_complaint", "label": "Bug report"},
}

TONE_PROMPTS = {
    "friendly": "Responda de forma amigável, calorosa e acessível. Use emojis de forma natural.",
    "professional": "Responda de forma profissional e educada, mantendo tom corporativo.",
    "humorous": "Responda com humor leve e descontração, sem perder o respeito.",
    "motivational": "Responda de forma motivacional e inspiradora, com energia positiva.",
    "casual": "Responda de forma casual e descontraída, como um amigo falando.",
    "expert": "Responda como um especialista no assunto, com autoridade mas acessibilidade.",
}


# ── 1. Generate Auto-Reply ────────────────────────────────────────

async def generate_auto_reply(
    comment_text: str,
    video_title: str = "",
    creator_tone: str = "friendly",
    language: str = "pt",
) -> dict:
    """Generate AI reply matching creator's tone. Returns reply with alternatives."""
    from backend.services.local_ai import llm_chat

    if not comment_text or not comment_text.strip():
        return {
            "reply": "",
            "tone_match": creator_tone,
            "confidence": 0.0,
            "alternative_replies": [],
        }

    tone_instruction = TONE_PROMPTS.get(creator_tone, TONE_PROMPTS["friendly"])
    lang_label = {"pt": "português brasileiro", "en": "English", "es": "español"}.get(language, "português brasileiro")

    context_line = ""
    if video_title:
        context_line = f"\nO vídeo se chama: \"{video_title}\""

    system_prompt = (
        f"Você é um assistente de criador de conteúdo que responde comentários nas redes sociais.\n"
        f"{tone_instruction}\n"
        f"Responda SEMPRE em {lang_label}.\n"
        f"Seja conciso (máximo 2-3 frases). Nunca invente informações.\n"
        f"Retorne APENAS o JSON, sem texto adicional."
    )

    user_prompt = (
        f"Gere 3 respostas diferentes para este comentário de um seguidor.{context_line}\n\n"
        f"Comentário: \"{comment_text}\"\n\n"
        f"Retorne um JSON neste formato exato:\n"
        f'{{"main_reply": "resposta principal", "alt1": "alternativa 1", "alt2": "alternativa 2"}}'
    )

    try:
        raw = await llm_chat(prompt=user_prompt, system=system_prompt, temperature=0.7)
        raw = raw.strip()

        json_match = re.search(r"\{.*\}", raw, re.DOTALL)
        if json_match:
            data = json.loads(json_match.group())
        else:
            data = {"main_reply": raw, "alt1": "", "alt2": ""}

        main_reply = data.get("main_reply", raw).strip()
        alt1 = data.get("alt1", "").strip()
        alt2 = data.get("alt2", "").strip()

        alternatives = [a for a in [alt1, alt2] if a]

        return {
            "reply": main_reply,
            "tone_match": creator_tone,
            "confidence": 0.85 if main_reply else 0.0,
            "alternative_replies": alternatives,
        }
    except Exception as e:
        logger.error(f"Error generating auto-reply: {e}")
        fallback_replies = {
            "pt": "Obrigado pelo comentário! 🙏",
            "en": "Thanks for your comment! 🙏",
            "es": "¡Gracias por tu comentario! 🙏",
        }
        return {
            "reply": fallback_replies.get(language, fallback_replies["pt"]),
            "tone_match": creator_tone,
            "confidence": 0.3,
            "alternative_replies": [],
        }


# ── 2. Classify Comment ───────────────────────────────────────────

def classify_comment(text: str) -> dict:
    """Classify a single comment using VADER + keyword patterns."""
    from backend.services.local_ai import analyze_sentiment

    if not text or not text.strip():
        return {
            "label": "neutral",
            "confidence": 0.0,
            "is_spam": False,
            "is_hate": False,
            "is_question": False,
            "sentiment_score": 0.0,
        }

    text_lower = text.lower().strip()

    # Sentiment via VADER
    sentiment = analyze_sentiment(text)
    compound = sentiment.get("compound", 0.0)

    # Spam detection
    spam_hits = sum(1 for kw in SPAM_KEYWORDS if kw in text_lower)
    has_excessive_caps = len(re.findall(r"[A-Z]{3,}", text)) > 2
    has_excessive_emoji = len(re.findall(r"[\U0001F300-\U0001FAFF]", text)) > 5
    has_urls = bool(re.search(r"https?://|www\.|bit\.ly|t\.co", text_lower))
    has_repeated_chars = bool(re.search(r"(.)\1{4,}", text_lower))
    spam_score = spam_hits * 0.3 + (0.15 if has_excessive_caps else 0) + (0.1 if has_excessive_emoji else 0) + (0.2 if has_urls else 0) + (0.1 if has_repeated_chars else 0)
    is_spam = spam_score >= 0.3

    # Hate detection
    hate_hits = sum(1 for kw in HATE_KEYWORDS if kw in text_lower)
    is_hate = hate_hits >= 1

    # Question detection
    is_question = any(re.search(p, text_lower) for p in QUESTION_PATTERNS)

    # Positive/negative keyword boost
    pos_hits = sum(1 for kw in POSITIVE_KEYWORDS if kw in text_lower)
    neg_hits = sum(1 for kw in NEGATIVE_KEYWORDS if kw in text_lower)

    # Determine label
    if is_spam:
        label = "spam"
        confidence = min(0.95, 0.5 + spam_score)
    elif is_hate:
        label = "hate"
        confidence = min(0.95, 0.5 + hate_hits * 0.2)
    elif is_question:
        label = "question"
        confidence = 0.8
    elif compound >= 0.05 or pos_hits > 0:
        label = "positive"
        confidence = min(0.95, 0.5 + abs(compound) * 0.4 + pos_hits * 0.1)
    elif compound <= -0.05 or neg_hits > 0:
        label = "negative"
        confidence = min(0.95, 0.5 + abs(compound) * 0.4 + neg_hits * 0.1)
    else:
        label = "neutral"
        confidence = 0.6

    # Feedback detection (suggestions/constructive criticism)
    feedback_patterns = [
        r"deveria", r"poderia", r"sugiro", r"sugestão",
        r"seria (bom|legal|melhor)", r"falta",
        r"should", r"could", r"suggest", r"suggestion",
        r"would be (nice|better|great)", r"missing",
    ]
    is_feedback = any(re.search(p, text_lower) for p in feedback_patterns)
    if is_feedback and not is_spam and not is_hate:
        label = "feedback"
        confidence = max(confidence, 0.7)

    return {
        "label": label,
        "confidence": round(confidence, 2),
        "is_spam": is_spam,
        "is_hate": is_hate,
        "is_question": is_question,
        "sentiment_score": round(compound, 3),
    }


# ── 3. Batch Classify Comments ────────────────────────────────────

def batch_classify_comments(comments: list) -> dict:
    """Batch classify many comments. Returns results and summary."""
    if not comments:
        return {
            "results": [],
            "summary": {
                "positive": 0,
                "negative": 0,
                "neutral": 0,
                "spam": 0,
                "hate": 0,
                "questions": 0,
                "feedback": 0,
                "total": 0,
            },
        }

    results = []
    summary = Counter()

    for comment in comments:
        text = comment if isinstance(comment, str) else comment.get("text", "")
        classification = classify_comment(text)
        results.append({
            "text": text,
            **classification,
        })
        summary[classification["label"]] += 1

    return {
        "results": results,
        "summary": {
            "positive": summary.get("positive", 0),
            "negative": summary.get("negative", 0),
            "neutral": summary.get("neutral", 0),
            "spam": summary.get("spam", 0),
            "hate": summary.get("hate", 0),
            "questions": summary.get("question", 0),
            "feedback": summary.get("feedback", 0),
            "total": len(comments),
        },
    }


# ── 4. Detect FAQ Patterns ────────────────────────────────────────

def detect_faq_patterns(comments: list, min_frequency: int = 3) -> list:
    """Find frequently asked questions from comments. Groups similar questions."""
    if not comments:
        return []

    # Extract only questions
    questions = []
    for c in comments:
        text = c if isinstance(c, str) else c.get("text", "")
        text = text.strip()
        if not text:
            continue
        classification = classify_comment(text)
        if classification["is_question"]:
            questions.append(text)

    if not questions:
        return []

    # Normalize questions for grouping
    def normalize(q):
        q = q.lower().strip()
        q = re.sub(r"[^\w\s]", "", q)
        q = re.sub(r"\s+", " ", q)
        stop_words = {"o", "a", "os", "as", "um", "uma", "de", "do", "da", "no", "na", "que", "e", "para", "com", "por", "the", "a", "an", "is", "it", "to", "of", "and", "in"}
        words = [w for w in q.split() if w not in stop_words and len(w) > 2]
        return " ".join(sorted(words))

    # Group by normalized form
    groups = defaultdict(list)
    for q in questions:
        key = normalize(q)
        if key:
            groups[key].append(q)

    # Filter by minimum frequency and build FAQ list
    faqs = []
    for key, group_questions in sorted(groups.items(), key=lambda x: len(x[1]), reverse=True):
        if len(group_questions) < min_frequency:
            continue

        # Pick the most representative question (longest one usually has more context)
        representative = max(group_questions, key=len)

        # Generate a suggested answer based on the question type
        suggested_answer = _generate_faq_answer_stub(representative)

        faqs.append({
            "question": representative,
            "frequency": len(group_questions),
            "sample_comments": group_questions[:5],
            "suggested_answer": suggested_answer,
        })

    return faqs


def _generate_faq_answer_stub(question: str) -> str:
    """Generate a basic suggested answer template for a FAQ."""
    q_lower = question.lower()

    if any(w in q_lower for w in ["preço", "price", "quanto custa", "how much", "valor"]):
        return "Confira nossos preços e planos no link da bio! Temos opções para todos os bolsos."
    elif any(w in q_lower for w in ["como", "how to", "tutorial", "como faz"]):
        return "Ótima pergunta! Vou fazer um vídeo tutorial detalhado sobre isso. Fique ligado!"
    elif any(w in q_lower for w in ["onde", "where", "comprar", "buy"]):
        return "Você encontra tudo no link da bio! Se tiver dúvidas, me chama no DM."
    elif any(w in q_lower for w in ["quando", "when", "data", "date"]):
        return "Em breve! Ative as notificações para não perder nenhuma novidade."
    elif any(w in q_lower for w in ["qual", "which", "melhor", "best"]):
        return "Depende da sua necessidade! Me conta mais no DM que te ajudo a escolher."
    else:
        return "Boa pergunta! Vou abordar isso em um próximo conteúdo. Obrigado pelo feedback!"


# ── 5. Detect Pain Points ─────────────────────────────────────────

def detect_pain_points(comments: list) -> list:
    """Find frustrations/problems expressed in comments."""
    from backend.services.local_ai import analyze_sentiment

    if not comments:
        return []

    pain_mentions = defaultdict(lambda: {"count": 0, "sentiments": [], "samples": []})

    for c in comments:
        text = c if isinstance(c, str) else c.get("text", "")
        text_lower = text.lower().strip()
        if not text_lower:
            continue

        sentiment = analyze_sentiment(text)
        compound = sentiment.get("compound", 0.0)

        # Check for pain point keywords
        matched_pains = []
        for kw in PAIN_KEYWORDS:
            if kw in text_lower:
                matched_pains.append(kw)

        # Also detect negative sentiment comments as potential pain points
        if compound <= -0.3 and not matched_pains:
            matched_pains.append("sentimento_negativo")

        for pain in matched_pains:
            pain_mentions[pain]["count"] += 1
            pain_mentions[pain]["sentiments"].append(compound)
            if len(pain_mentions[pain]["samples"]) < 5:
                pain_mentions[pain]["samples"].append(text)

    # Build results sorted by frequency
    results = []
    for pain_key, data in sorted(pain_mentions.items(), key=lambda x: x[1]["count"], reverse=True):
        if data["count"] < 2:
            continue

        avg_sentiment = sum(data["sentiments"]) / len(data["sentiments"]) if data["sentiments"] else 0.0

        content_idea = _generate_content_idea_for_pain(pain_key, data["samples"])

        results.append({
            "pain_point": pain_key,
            "frequency": data["count"],
            "sentiment_avg": round(avg_sentiment, 3),
            "sample_comments": data["samples"],
            "content_idea": content_idea,
        })

    return results


def _generate_content_idea_for_pain(pain_key: str, samples: list) -> str:
    """Generate a content idea to address a detected pain point."""
    pain_to_idea = {
        "não consigo": "Tutorial passo-a-passo mostrando como resolver esse problema",
        "não funciona": "Vídeo de troubleshooting: 5 razões pelas quais não está funcionando",
        "problema": "Como resolver os problemas mais comuns que meus seguidores enfrentam",
        "dificuldade": "Guia completo para iniciantes: simplificando o que parece difícil",
        "travou": "Quick fix: o que fazer quando trava? Soluções em 60 segundos",
        "erro": "Os erros mais comuns e como evitá-los - Tutorial rápido",
        "bug": "Workarounds para os bugs mais reportados pela comunidade",
        "demora": "Dicas para acelerar o processo - Do lento ao instantâneo",
        "lento": "Performance: como deixar tudo mais rápido em 5 passos",
        "confuso": "Explicação simplificada: entenda de uma vez por todas",
        "complicado": "Descomplicando: guia visual passo a passo",
        "frustrado": "Eu entendo sua frustração - Aqui está a solução definitiva",
        "falta": "Nova feature/conteúdo que vocês pediram! Entregando o que faltava",
        "preciso de": "Vocês pediram, eu entreguei! Conteúdo sob demanda",
        "can't": "Step-by-step tutorial: How to solve this common issue",
        "doesn't work": "Troubleshooting guide: 5 reasons why it doesn't work",
        "problem": "Solving the most common problems my followers face",
        "difficult": "Beginner's guide: making the difficult simple",
        "stuck": "Quick fix: what to do when you're stuck",
        "error": "Most common errors and how to fix them",
        "slow": "Speed tips: from slow to instant in 5 steps",
        "confusing": "Simplified explanation: understand it once and for all",
        "frustrated": "I hear you - here's the definitive solution",
        "missing": "You asked, I delivered! New content you've been waiting for",
        "need": "Delivering what my community needs most",
        "sentimento_negativo": "Respondendo às preocupações da comunidade - Vamos melhorar juntos",
    }
    return pain_to_idea.get(pain_key, f"Conteúdo abordando: {pain_key} - Transformando feedback em valor")


# ── 6. Generate FAQ Content ───────────────────────────────────────

async def generate_faq_content(faqs: list) -> dict:
    """Turn FAQs into content ideas using AI."""
    from backend.services.local_ai import llm_chat

    if not faqs:
        return {"content_ideas": []}

    # Build the FAQ summary for the AI
    faq_summary_parts = []
    for i, faq in enumerate(faqs[:10], 1):
        faq_summary_parts.append(
            f"{i}. \"{faq.get('question', '')}\" (perguntada {faq.get('frequency', 0)} vezes)"
        )
    faq_text = "\n".join(faq_summary_parts)

    system_prompt = (
        "Você é um estrategista de conteúdo para redes sociais.\n"
        "Sua tarefa é transformar perguntas frequentes da audiência em ideias de conteúdo viral.\n"
        "Retorne APENAS o JSON, sem texto adicional."
    )

    user_prompt = (
        f"Aqui estão as perguntas mais frequentes da minha audiência:\n\n"
        f"{faq_text}\n\n"
        f"Para cada pergunta, crie uma ideia de conteúdo. Retorne um JSON neste formato:\n"
        f'{{"ideas": ['
        f'{{"title": "titulo do video", "hook": "frase de abertura chamativa", "format": "tipo (tutorial/storytime/lista/etc)", "estimated_engagement": "alto/medio/baixo"}}'
        f"]}}"
    )

    try:
        raw = await llm_chat(prompt=user_prompt, system=system_prompt, max_tokens=2048, temperature=0.7)
        raw = raw.strip()

        json_match = re.search(r"\{.*\}", raw, re.DOTALL)
        if json_match:
            data = json.loads(json_match.group())
            ideas = data.get("ideas", [])
        else:
            ideas = []

        # Validate and normalize
        content_ideas = []
        for idea in ideas:
            content_ideas.append({
                "title": idea.get("title", "Sem título"),
                "hook": idea.get("hook", ""),
                "format": idea.get("format", "tutorial"),
                "estimated_engagement": idea.get("estimated_engagement", "medio"),
            })

        return {"content_ideas": content_ideas}

    except Exception as e:
        logger.error(f"Error generating FAQ content: {e}")
        # Fallback: generate basic content ideas from FAQs
        fallback_ideas = []
        for faq in faqs[:5]:
            question = faq.get("question", "")
            fallback_ideas.append({
                "title": f"Respondendo: {question[:60]}",
                "hook": f"Vocês perguntaram {faq.get('frequency', 0)} vezes sobre isso...",
                "format": "tutorial",
                "estimated_engagement": "medio",
            })
        return {"content_ideas": fallback_ideas}


# ── 7. Calculate Community Health ─────────────────────────────────

def calculate_community_health(comments: list, engagement_data: dict = None) -> dict:
    """Score community health 0-100 based on multiple factors."""
    if not comments:
        return {
            "score": 0,
            "label": "Sem dados",
            "factors": {
                "sentiment": 0,
                "spam_rate": 0,
                "diversity": 0,
                "engagement": 0,
                "question_rate": 0,
            },
            "recommendations": ["Colete mais comentários para ter uma análise completa."],
        }

    # Classify all comments
    classified = batch_classify_comments(comments)
    summary = classified["summary"]
    total = summary["total"]

    if total == 0:
        return {
            "score": 0,
            "label": "Sem dados",
            "factors": {"sentiment": 0, "spam_rate": 0, "diversity": 0, "engagement": 0, "question_rate": 0},
            "recommendations": ["Colete mais comentários para ter uma análise completa."],
        }

    # Factor 1: Sentiment (0-100) - How positive is the community?
    positive_ratio = summary["positive"] / total
    negative_ratio = summary["negative"] / total
    sentiment_score = max(0, min(100, int((positive_ratio * 100) + ((1 - negative_ratio) * 50)) / 1.5))

    # Factor 2: Spam rate (0-100 inverse) - Lower spam = higher score
    spam_ratio = summary["spam"] / total
    spam_score = max(0, int((1 - spam_ratio) * 100))

    # Factor 3: Diversity (0-100) - Variety of comment types
    label_counts = [
        summary["positive"], summary["negative"], summary["neutral"],
        summary["questions"], summary["feedback"],
    ]
    non_zero_labels = sum(1 for c in label_counts if c > 0)
    diversity_score = min(100, int(non_zero_labels / 5 * 100))

    # Factor 4: Engagement quality (0-100)
    engagement_score = 50  # Default
    if engagement_data:
        avg_comments = engagement_data.get("avg_comments_per_video", 0)
        avg_likes = engagement_data.get("avg_likes_per_video", 0)
        if avg_comments > 100:
            engagement_score = 90
        elif avg_comments > 50:
            engagement_score = 75
        elif avg_comments > 20:
            engagement_score = 60
        elif avg_comments > 5:
            engagement_score = 45
        else:
            engagement_score = 30

        if avg_likes > 1000:
            engagement_score = min(100, engagement_score + 10)

    # Factor 5: Question rate (0-100) - Engaged audience asks questions
    question_ratio = summary["questions"] / total
    question_score = min(100, int(question_ratio * 300))  # 33% questions = 100

    # Weighted final score
    weights = {
        "sentiment": 0.30,
        "spam_rate": 0.25,
        "diversity": 0.15,
        "engagement": 0.15,
        "question_rate": 0.15,
    }

    final_score = int(
        sentiment_score * weights["sentiment"]
        + spam_score * weights["spam_rate"]
        + diversity_score * weights["diversity"]
        + engagement_score * weights["engagement"]
        + question_score * weights["question_rate"]
    )
    final_score = max(0, min(100, final_score))

    # Label
    if final_score >= 80:
        label = "Excelente"
    elif final_score >= 60:
        label = "Saudável"
    elif final_score >= 40:
        label = "Regular"
    elif final_score >= 20:
        label = "Preocupante"
    else:
        label = "Crítico"

    # Recommendations
    recommendations = []
    if sentiment_score < 50:
        recommendations.append("Sentimento da comunidade está baixo. Considere criar conteúdo mais positivo e engajante.")
    if spam_score < 60:
        recommendations.append("Alta taxa de spam detectada. Considere usar filtros automáticos de comentários.")
    if diversity_score < 40:
        recommendations.append("Pouca diversidade nos tipos de interação. Incentive perguntas e feedback com CTAs.")
    if question_score < 30:
        recommendations.append("Poucas perguntas da audiência. Use CTAs como 'O que vocês acham?' para estimular interação.")
    if summary["hate"] > 0:
        hate_pct = (summary["hate"] / total) * 100
        recommendations.append(f"{hate_pct:.0f}% dos comentários contêm conteúdo ofensivo. Modere ativamente.")
    if not recommendations:
        recommendations.append("Sua comunidade está saudável! Continue com o bom trabalho.")

    return {
        "score": final_score,
        "label": label,
        "factors": {
            "sentiment": sentiment_score,
            "spam_rate": spam_score,
            "diversity": diversity_score,
            "engagement": engagement_score,
            "question_rate": question_score,
        },
        "recommendations": recommendations,
    }


# ── 8. Generate Welcome DM ───────────────────────────────────────

async def generate_welcome_dm(
    creator_name: str,
    new_follower: str = "",
    niche: str = "",
) -> dict:
    """Generate personalized welcome DM for new followers."""
    from backend.services.local_ai import llm_chat

    follower_part = f" chamado(a) {new_follower}" if new_follower else ""
    niche_part = f" no nicho de {niche}" if niche else ""

    system_prompt = (
        "Você é um criador de conteúdo carismático nas redes sociais.\n"
        "Gere mensagens de boas-vindas curtas, calorosas e autênticas.\n"
        "A mensagem deve ter um CTA (call to action) sutil no final.\n"
        "Máximo 3-4 frases. Retorne APENAS o JSON, sem texto adicional."
    )

    user_prompt = (
        f"Gere uma mensagem de boas-vindas de {creator_name}{niche_part} para um novo seguidor{follower_part}.\n\n"
        f"Retorne um JSON neste formato:\n"
        f'{{"message": "mensagem de boas-vindas", "tone": "tom usado", "has_cta": true}}'
    )

    try:
        raw = await llm_chat(prompt=user_prompt, system=system_prompt, temperature=0.8)
        raw = raw.strip()

        json_match = re.search(r"\{.*\}", raw, re.DOTALL)
        if json_match:
            data = json.loads(json_match.group())
        else:
            data = {}

        return {
            "message": data.get("message", f"Oi{follower_part}! Bem-vindo(a) à comunidade! 🎉 Fico feliz que esteja aqui. Me conta nos comentários o que você mais quer ver por aqui!"),
            "tone": data.get("tone", "friendly"),
            "has_cta": data.get("has_cta", True),
        }
    except Exception as e:
        logger.error(f"Error generating welcome DM: {e}")
        return {
            "message": f"Oi{follower_part}! 👋 Seja muito bem-vindo(a) à nossa comunidade! Fico feliz demais que você está aqui. Me conta o que te trouxe e o que mais quer aprender! 🚀",
            "tone": "friendly",
            "has_cta": True,
        }


# ── 9. Detect Keyword Triggers ────────────────────────────────────

def detect_keyword_triggers(text: str, triggers: dict = None) -> list:
    """Detect keywords that should trigger auto-actions."""
    if not text or not text.strip():
        return []

    text_lower = text.lower().strip()
    trigger_map = triggers or DEFAULT_TRIGGERS

    detected = []
    seen_actions = set()

    for keyword, config in trigger_map.items():
        if keyword.lower() in text_lower:
            action = config.get("action", "notify") if isinstance(config, dict) else config
            label = config.get("label", keyword) if isinstance(config, dict) else keyword

            # Avoid duplicate actions
            if action in seen_actions:
                continue
            seen_actions.add(action)

            # Calculate confidence based on exact match vs partial
            words = text_lower.split()
            exact_match = keyword.lower() in words or keyword.lower() in text_lower
            confidence = 0.9 if exact_match else 0.6

            detected.append({
                "keyword": keyword,
                "action": action,
                "label": label,
                "confidence": confidence,
            })

    # Sort by confidence descending
    detected.sort(key=lambda x: x["confidence"], reverse=True)
    return detected


# ── 10. Comment to Content Pipeline ───────────────────────────────

async def comment_to_content_pipeline(
    db: Session,
    days: int = 7,
    limit: int = 50,
) -> dict:
    """
    Full pipeline: get recent video data -> simulate comments from descriptions
    -> find patterns -> generate content ideas.
    """
    cutoff = datetime.utcnow() - timedelta(days=days)

    # Get recent videos with engagement data
    videos = (
        db.query(Video)
        .filter(Video.collected_at >= cutoff)
        .order_by(desc(Video.comments))
        .limit(limit)
        .all()
    )

    if not videos:
        return {
            "total_analyzed": 0,
            "faqs": [],
            "pain_points": [],
            "content_ideas": [],
        }

    # Use video descriptions and titles as proxy for comment analysis
    # (since actual comments are not stored in the video model)
    comment_proxies = []
    for v in videos:
        if v.description:
            comment_proxies.append(v.description)
        if v.title:
            comment_proxies.append(v.title)

    # Detect FAQ patterns
    faqs = detect_faq_patterns(comment_proxies, min_frequency=2)

    # Detect pain points
    pain_points = detect_pain_points(comment_proxies)

    # Generate content ideas from FAQs
    content_result = {"content_ideas": []}
    if faqs:
        content_result = await generate_faq_content(faqs)

    # Add content ideas from pain points
    for pp in pain_points[:5]:
        content_result["content_ideas"].append({
            "title": pp["content_idea"],
            "hook": f"Vocês mencionaram '{pp['pain_point']}' {pp['frequency']} vezes...",
            "format": "tutorial",
            "estimated_engagement": "alto" if pp["frequency"] > 5 else "medio",
        })

    return {
        "total_analyzed": len(comment_proxies),
        "faqs": faqs,
        "pain_points": pain_points,
        "content_ideas": content_result.get("content_ideas", []),
    }


# ── 11. Engagement Analytics ──────────────────────────────────────

def get_engagement_analytics(db: Session, days: int = 7) -> dict:
    """Analyze engagement patterns from videos."""
    cutoff = datetime.utcnow() - timedelta(days=days)

    videos = (
        db.query(Video)
        .filter(Video.collected_at >= cutoff)
        .all()
    )

    if not videos:
        return {
            "total_comments": 0,
            "total_likes": 0,
            "total_shares": 0,
            "total_views": 0,
            "total_videos": 0,
            "avg_per_video": {
                "comments": 0,
                "likes": 0,
                "shares": 0,
                "views": 0,
            },
            "sentiment_breakdown": {
                "positivo": 0,
                "negativo": 0,
                "neutro": 0,
            },
            "peak_hours": [],
            "top_videos_by_engagement": [],
            "platform_breakdown": {},
            "engagement_rate_avg": 0.0,
        }

    total_comments = sum(v.comments or 0 for v in videos)
    total_likes = sum(v.likes or 0 for v in videos)
    total_shares = sum(v.shares or 0 for v in videos)
    total_views = sum(v.views or 0 for v in videos)
    num_videos = len(videos)

    # Average per video
    avg_comments = total_comments / num_videos if num_videos else 0
    avg_likes = total_likes / num_videos if num_videos else 0
    avg_shares = total_shares / num_videos if num_videos else 0
    avg_views = total_views / num_videos if num_videos else 0

    # Sentiment from titles/descriptions
    from backend.services.local_ai import analyze_sentiment_batch

    texts = [v.title or "" for v in videos if v.title]
    sentiment_data = analyze_sentiment_batch(texts)
    sentiment_breakdown = sentiment_data.get("distribution", {"positivo": 0, "negativo": 0, "neutro": 0})

    # Peak hours from collected_at
    hour_counts = Counter()
    for v in videos:
        if v.published_at:
            hour_counts[v.published_at.hour] += 1
        elif v.collected_at:
            hour_counts[v.collected_at.hour] += 1

    peak_hours = [
        {"hour": h, "count": c}
        for h, c in sorted(hour_counts.items(), key=lambda x: x[1], reverse=True)[:6]
    ]

    # Top videos by engagement
    top_videos = sorted(
        videos,
        key=lambda v: (v.likes or 0) + (v.comments or 0) + (v.shares or 0),
        reverse=True,
    )[:10]

    top_videos_data = [
        {
            "id": v.id,
            "title": v.title[:80] if v.title else "Sem título",
            "platform": v.platform,
            "views": v.views or 0,
            "likes": v.likes or 0,
            "comments": v.comments or 0,
            "shares": v.shares or 0,
            "engagement_rate": round(v.engagement_rate or 0, 2),
            "viral_score": round(v.viral_score or 0, 1),
        }
        for v in top_videos
    ]

    # Platform breakdown
    platform_stats = defaultdict(lambda: {"videos": 0, "views": 0, "likes": 0, "comments": 0, "shares": 0})
    for v in videos:
        p = v.platform or "unknown"
        platform_stats[p]["videos"] += 1
        platform_stats[p]["views"] += v.views or 0
        platform_stats[p]["likes"] += v.likes or 0
        platform_stats[p]["comments"] += v.comments or 0
        platform_stats[p]["shares"] += v.shares or 0

    # Average engagement rate
    engagement_rates = [v.engagement_rate for v in videos if v.engagement_rate and v.engagement_rate > 0]
    avg_engagement_rate = sum(engagement_rates) / len(engagement_rates) if engagement_rates else 0.0

    return {
        "total_comments": total_comments,
        "total_likes": total_likes,
        "total_shares": total_shares,
        "total_views": total_views,
        "total_videos": num_videos,
        "avg_per_video": {
            "comments": round(avg_comments, 1),
            "likes": round(avg_likes, 1),
            "shares": round(avg_shares, 1),
            "views": round(avg_views, 1),
        },
        "sentiment_breakdown": sentiment_breakdown,
        "peak_hours": peak_hours,
        "top_videos_by_engagement": top_videos_data,
        "platform_breakdown": dict(platform_stats),
        "engagement_rate_avg": round(avg_engagement_rate, 4),
    }


# ── 12. Generate Comment Responses Batch ──────────────────────────

async def generate_comment_responses_batch(
    comments: list,
    tone: str = "friendly",
) -> list:
    """Generate AI responses for multiple comments at once."""
    if not comments:
        return []

    results = []

    # Classify all comments first for priority assignment
    classifications = batch_classify_comments(comments)

    for i, comment_data in enumerate(comments):
        text = comment_data if isinstance(comment_data, str) else comment_data.get("text", "")

        if not text.strip():
            results.append({
                "comment": text,
                "reply": "",
                "priority": "low",
                "classification": "empty",
            })
            continue

        # Determine priority based on classification
        classification = classifications["results"][i] if i < len(classifications["results"]) else {}
        label = classification.get("label", "neutral")

        if label in ("question", "feedback"):
            priority = "high"
        elif label == "negative":
            priority = "high"
        elif label == "positive":
            priority = "medium"
        elif label in ("spam", "hate"):
            priority = "low"
        else:
            priority = "medium"

        # Skip spam and hate - no reply needed
        if label in ("spam", "hate"):
            results.append({
                "comment": text,
                "reply": "[Filtrado - Spam/Ofensivo]",
                "priority": "low",
                "classification": label,
            })
            continue

        # Generate reply
        reply_data = await generate_auto_reply(
            comment_text=text,
            creator_tone=tone,
        )

        results.append({
            "comment": text,
            "reply": reply_data.get("reply", ""),
            "priority": priority,
            "classification": label,
            "alternatives": reply_data.get("alternative_replies", []),
        })

    # Sort by priority (high first)
    priority_order = {"high": 0, "medium": 1, "low": 2}
    results.sort(key=lambda x: priority_order.get(x.get("priority", "low"), 2))

    return results
