features/sentiment/data.py

"""감성분석 Feature 데이터 로딩.

All data fetched via direct Supabase (RPC + PostgREST).
Vercel API is NOT used — avoids 10s timeout and HTTP overhead.
"""
import logging
import streamlit as st

from core.api_client import ChainShiftClient
from core.supabase_client import get_campaign_overview, get_supabase_client

logger = logging.getLogger(__name__)


@st.cache_data(ttl=60)
def _get_nudge_data(_sb_id: str, campaign_id: int) -> dict:
    """Fetch nudge stats (RPC) + all candidates (direct PostgREST).

    _sb_id is a cache-buster (not used) since Supabase client isn't hashable.
    """
    try:
        sb = get_supabase_client()

        # 1. Aggregated stats via RPC (single query, ~3ms)
        stats = sb.rpc("get_nudge_stats_agg", {"p_campaign_id": campaign_id}).execute()
        nudge_stats = stats.data or {}

        # 2. All candidates via RPC (inline CTE, avoids VIEW timeout)
        candidates_rpc = sb.rpc("get_nudge_export_data", {
            "p_campaign_id": campaign_id,
            "p_in_house_only": True,
            "p_limit": 500,
        }).execute()
        rpc_data = candidates_rpc.data or {}
        candidates = rpc_data.get("rows", []) if isinstance(rpc_data, dict) else []

        # Merge stats + candidates into nudge_data shape (backward-compatible)
        nudge_data = {
            "total_nudge_candidates": nudge_stats.get("total_nudge_candidates", 0),
            "by_confidence_tier": nudge_stats.get("by_confidence_tier", {}),
            "by_platform": nudge_stats.get("by_platform", {}),
            "by_cej": nudge_stats.get("by_cej", {}),
            "by_bit_quadrant": nudge_stats.get("by_bit_quadrant", {}),
            "llm_verification_stats": nudge_stats.get("llm_verification_stats", {}),
            "candidates": candidates,
        }
        return nudge_data
    except Exception as e:
        logger.warning("Nudge data fetch failed for campaign %s: %s", campaign_id, e)
        return {}


@st.cache_data(ttl=60)
def _get_brand_stats(_sb_id: str, campaign_id: int) -> dict:
    """Fetch brand mention stats via RPC (DB-side aggregation).

    Returns dict compatible with overview.py's brand_data format:
      - total_answers: int
      - in_house_summary: list of brand stat dicts
      - competitor_summary: list of brand stat dicts
    """
    try:
        sb = get_supabase_client()

        # Single RPC returns brand stats + total count (no separate VIEW query)
        result = sb.rpc("get_brand_mention_stats_agg", {"p_campaign_id": campaign_id}).execute()
        data = result.data
        # PostgREST may wrap json return as [dict] or dict
        if isinstance(data, list) and data:
            data = data[0]
        if not isinstance(data, dict):
            return {"total_answers": 0, "in_house_summary": [], "competitor_summary": []}

        brands = data.get("brands") or []
        total_answers = int(data.get("total_unique_answers") or 0)

        in_house_summary = []
        competitor_summary = []

        for b in brands:
            total = b["total_mentions"]
            entry = {
                "brand_name": b["brand_name"],
                "brand_type": b["brand_type"],
                "total_mentions": total,
                "positive_count": b["positive_count"],
                "negative_count": b["negative_count"],
                "neutral_count": b["neutral_count"],
                "positive_rate": round(100.0 * b["positive_count"] / total, 2) if total > 0 else 0.0,
                "negative_rate": round(100.0 * b["negative_count"] / total, 2) if total > 0 else 0.0,
            }
            if b["brand_type"] == "IN_HOUSE":
                in_house_summary.append(entry)
            else:
                competitor_summary.append(entry)

        # Enrich competitor brands with aliases (synonyms) and llm_verified_count
        if competitor_summary:
            synonyms_map: dict[str, list] = {}
            verified_map: dict[str, int] = {}
            try:
                brands_info = sb.table("brands_sync").select(
                    "name, synonyms"
                ).eq("rds_campaign_id", campaign_id).eq(
                    "brand_type", "SECONDARY"
                ).execute()
                synonyms_map = {
                    row["name"]: row.get("synonyms") or []
                    for row in (brands_info.data or [])
                }
            except Exception:
                pass
            try:
                vcounts = sb.rpc("get_competitor_llm_verified_counts", {"p_campaign_id": campaign_id}).execute()
                # PostgREST wraps json return as [result].
                # RPC uses json_agg → array. Actual shape: [[{brand_name, verified_count}, ...]]
                vdata = vcounts.data
                if isinstance(vdata, list) and len(vdata) > 0 and isinstance(vdata[0], list):
                    vdata = vdata[0]  # unwrap PostgREST double-nesting
                elif isinstance(vdata, dict):
                    vdata = [vdata]  # single dict → wrap in list
                elif not isinstance(vdata, list):
                    vdata = []
                for row in vdata:
                    if isinstance(row, dict):
                        verified_map[row.get("brand_name", "")] = row.get("verified_count", 0)
            except Exception:
                pass
            for entry in competitor_summary:
                entry["aliases"] = synonyms_map.get(entry["brand_name"], [])
                entry["llm_verified_count"] = verified_map.get(entry["brand_name"], 0)

        return {
            "total_answers": total_answers,
            "in_house_summary": in_house_summary,
            "competitor_summary": competitor_summary,
        }
    except Exception as e:
        logger.warning("Brand stats fetch failed for campaign %s: %s", campaign_id, e)
        return {}


@st.cache_data(ttl=60)
def _get_feedback_stats(_sb_id: str, campaign_id: int) -> dict:
    """Fetch feedback stats via RPC (DB-side aggregation)."""
    try:
        sb = get_supabase_client()
        result = sb.rpc("get_feedback_stats_agg", {"p_campaign_id": campaign_id}).execute()

        data = result.data
        # PostgREST may wrap json return as [dict] or dict
        if isinstance(data, list) and data:
            data = data[0]
        if not isinstance(data, dict):
            return {}

        correct_count = data.get("correct_count", 0)
        wrong_count = data.get("wrong_count", 0)
        evaluated = correct_count + wrong_count
        data["accuracy_rate"] = round(correct_count / evaluated * 100, 1) if evaluated > 0 else 0.0

        return data
    except Exception as e:
        logger.warning("Feedback stats fetch failed for campaign %s: %s", campaign_id, e)
        return {}


@st.cache_data(ttl=120)
def _get_keyword_summary(_sb_id: str, campaign_id: int):
    """Get keyword summary via direct Supabase RPC (bypasses Vercel 10s timeout)."""
    try:
        sb = get_supabase_client()

        # DB-side aggregation (covering indexes: <0.3s for 660K+ rows)
        summary = sb.rpc("get_keyword_summary_agg", {"p_campaign_id": campaign_id}).execute()
        tags = sb.rpc("get_keyword_llm_tags_agg", {"p_campaign_id": campaign_id}).limit(10000).execute()

        # Build tag lookup
        tag_map: dict[str, dict[str, int]] = {}
        for tr in (tags.data or []):
            kw = tr["keyword"]
            if kw not in tag_map:
                tag_map[kw] = {}
            tag_map[kw][tr["tag"]] = int(tr["tag_count"])

        total_sentences = 0
        keywords = []
        for row in sorted(summary.data or [], key=lambda r: r["keyword"]):
            kw = row["keyword"]
            total = int(row["total_sentences"])
            total_sentences += total
            brand_count = int(row["brand_mentioned_count"])

            kw_tags = tag_map.get(kw, {})
            top_tags = dict(sorted(kw_tags.items(), key=lambda x: -x[1])[:20])

            item = {
                "keyword": kw,
                "total_sentences": total,
                "keyword_sentiment": {
                    "positive": int(row["kw_positive"]),
                    "neutral": int(row["kw_neutral"]),
                    "negative": int(row["kw_negative"]),
                },
                "brand_mentioned_count": brand_count,
                "brand_sentiment": {
                    "positive": int(row["brand_positive"]),
                    "neutral": int(row["brand_neutral"]),
                    "negative": int(row["brand_negative"]),
                } if brand_count > 0 else None,
                "llm_reason_tags": top_tags,
            }
            keywords.append(item)

        return {
            "campaign_id": campaign_id,
            "total_keywords": len(keywords),
            "total_sentences": total_sentences,
            "keywords": keywords,
        }
    except Exception as e:
        logger.warning("Keyword summary RPC failed for campaign %s: %s", campaign_id, e)
        st.warning(f"키워드 RPC 실패: {e}")
        return None


@st.cache_data(ttl=60)
def _get_competitor_mentions(
    _sb_id: str,
    campaign_id: int,
    polarity: str | None = None,
    competitor_llm_verified: bool | None = None,
    competitor_llm_is_negative: bool | None = None,
    brand_name: str | None = None,
    platform: str | None = None,
    page: int = 1,
    page_size: int = 50,
) -> dict:
    """Fetch competitor brand mentions via RPC (server-side filtering + pagination).

    All filters and pagination are handled by `get_nudge_export_data` RPC.
    _sb_id is a cache-buster (not used) since Supabase client isn't hashable.
    """
    try:
        sb = get_supabase_client()

        params: dict = {
            "p_campaign_id": campaign_id,
            "p_in_house_only": False,
            "p_has_mentioned_brands": True,
            "p_offset": (page - 1) * page_size,
            "p_limit": page_size,
        }
        if polarity:
            params["p_polarity"] = polarity
        if brand_name:
            params["p_brand_name"] = brand_name
        if platform:
            params["p_platform"] = platform
        if competitor_llm_verified is not None:
            params["p_competitor_llm_verified"] = competitor_llm_verified
        if competitor_llm_is_negative is not None:
            params["p_competitor_llm_is_negative"] = competitor_llm_is_negative

        result = sb.rpc("get_nudge_export_data", params).execute()
        data = result.data or {}
        if not isinstance(data, dict):
            data = {}

        return {
            "recent_mentions": data.get("rows", []),
            "total_answers": data.get("total", 0),
        }
    except Exception as e:
        logger.warning("Competitor mentions fetch failed for campaign %s: %s", campaign_id, e)
        return {"recent_mentions": [], "total_answers": 0}


def load_sentiment_data(api_key: str, campaign_id: int, access_token: str = "") -> dict | None:
    """감성분석에 필요한 모든 데이터 로딩.

    All data fetched via direct Supabase (no Vercel API dependency).

    Returns:
        dict with all sentiment data, or None on failure.
    """
    try:
        # Cache buster for Supabase client (not hashable by st.cache_data)
        sb_id = "sb"

        # Direct Supabase: nudge stats RPC + candidates PostgREST
        nudge_data = _get_nudge_data(sb_id, campaign_id)

        # Direct Supabase: brand aggregation RPC
        brand_data = _get_brand_stats(sb_id, campaign_id)

        # Direct Supabase: feedback stats PostgREST
        feedback_stats = _get_feedback_stats(sb_id, campaign_id)

        try:
            campaign_overview = get_campaign_overview(campaign_id)
        except Exception:
            campaign_overview = {}

        # Extract metrics
        total_nudge = nudge_data.get("total_nudge_candidates", 0)
        tier_stats = {k: v for k, v in nudge_data.get("by_confidence_tier", {}).items() if k}
        platform_stats = {k: v for k, v in nudge_data.get("by_platform", {}).items() if k}
        cej_stats = {k: v for k, v in nudge_data.get("by_cej", {}).items() if k}
        bit_stats = {k: v for k, v in nudge_data.get("by_bit_quadrant", {}).items() if k}
        candidates = nudge_data.get("candidates", [])
        llm_stats = nudge_data.get("llm_verification_stats", {})

        risk_score = ChainShiftClient.calculate_risk_score(tier_stats)
        domain_counts = ChainShiftClient.aggregate_citation_domains(candidates)

        # Keyword data (optional, doesn't fail if unavailable)
        keyword_data = _get_keyword_summary(sb_id, campaign_id) or {}

        # Overview derived values
        overview_llm_done = campaign_overview.get("llm_verified_in_house", 0)
        overview_llm_confirmed = campaign_overview.get("llm_confirmed_negative", 0)

        return {
            "api_key": api_key,
            "access_token": access_token,
            "campaign_id": campaign_id,
            "nudge_data": nudge_data,
            "brand_data": brand_data,
            "feedback_stats": feedback_stats,
            "campaign_overview": campaign_overview,
            "candidates": candidates,
            "total_nudge": total_nudge,
            "tier_stats": tier_stats,
            "platform_stats": platform_stats,
            "cej_stats": cej_stats,
            "bit_stats": bit_stats,
            "domain_counts": domain_counts,
            "risk_score": risk_score,
            "high_count": tier_stats.get("HIGH", 0),
            "medium_count": tier_stats.get("MEDIUM", 0),
            "low_count": tier_stats.get("LOW", 0),
            "overview_total_answers": campaign_overview.get("total_answers", 0),
            "overview_nudge_candidates": campaign_overview.get("in_house_negative_count", 0),
            "overview_llm_verified": overview_llm_done,
            "overview_llm_pending": campaign_overview.get("llm_pending", 0),
            "overview_false_positive_rate": (overview_llm_done - overview_llm_confirmed) / overview_llm_done if overview_llm_done > 0 else 0,
            "keyword_data": keyword_data,
            "llm_verification_stats": llm_stats,
        }
    except Exception:
        return None