core/supabase_sentiment.py

"""Supabase sentiment queries — overview, LLM verification, polarity.

Module-level functions extracted from supabase_client.py.
All functions use get_supabase_client() from the parent module.
"""
import logging
import streamlit as st

logger = logging.getLogger(__name__)


def get_campaign_overview(campaign_id: int) -> dict:
    """Get comprehensive overview stats for a campaign via single RPC call.

    Uses get_campaign_overview_agg() RPC which performs COUNT(*) FILTER
    in a single table scan instead of 6 separate count queries.

    Returns:
        Dict with total_answers, in_house_negative_count, llm_verified_total,
        llm_verified_in_house, llm_pending, llm_confirmed_negative.
        Returns zeroed dict on DB error for graceful degradation.
    """
    from core.supabase_client import get_supabase_client

    _empty = {
        "total_answers": 0,
        "in_house_negative_count": 0,
        "llm_verified_total": 0,
        "llm_verified_in_house": 0,
        "llm_pending": 0,
        "llm_confirmed_negative": 0,
    }
    try:
        client = get_supabase_client()
        result = client.rpc(
            "get_campaign_overview_agg", {"p_campaign_id": campaign_id}
        ).execute()
        data = result.data
        # RPC may return list-wrapped or JSON string depending on PostgREST
        if isinstance(data, list) and data:
            data = data[0]
        if isinstance(data, str):
            import json
            data = json.loads(data)
        return data if (isinstance(data, dict) and data) else _empty
    except Exception:
        return _empty


def get_llm_verification_stats(
    campaign_id: int,
    in_house_only: bool = True,
) -> dict:
    """Get LLM verification statistics via single RPC (avoids VIEW timeout).

    Uses get_campaign_sentiment_stats RPC — one table scan with COUNT FILTER.

    Args:
        campaign_id: Campaign ID
        in_house_only: If True, only count in-house brand negatives (default)

    Returns:
        Dict with total_verified, false_positives, true_negatives counts
    """
    from core.supabase_client import get_supabase_client

    _empty = {"total_verified": 0, "false_positives": 0, "true_negatives": 0}
    try:
        client = get_supabase_client()
        result = client.rpc(
            "get_campaign_sentiment_stats", {"p_campaign_id": campaign_id}
        ).execute()
        stats = result.data or {}
        if isinstance(stats, list) and stats:
            stats = stats[0]
        if isinstance(stats, str):
            import json
            stats = json.loads(stats)
        if not isinstance(stats, dict):
            return _empty

        if in_house_only:
            return {
                "total_verified": stats.get("in_house_llm_verified", 0),
                "false_positives": stats.get("in_house_llm_false_pos", 0),
                "true_negatives": stats.get("in_house_llm_true_neg", 0),
            }
        return {
            "total_verified": stats.get("llm_verified", 0),
            "false_positives": stats.get("llm_false_positive", 0),
            "true_negatives": stats.get("llm_true_negative", 0),
        }
    except Exception:
        return _empty


@st.cache_data(ttl=60)
def get_false_positives(
    campaign_id: int,
    page: int = 1,
    page_size: int = 50,
    in_house_only: bool = True,
) -> tuple[list[dict], int]:
    """Get false positive items via RPC (avoids VIEW timeout).

    Returns:
        Tuple of (items list, total count)
    """
    from core.supabase_client import get_supabase_client

    try:
        client = get_supabase_client()
        result = client.rpc("get_nudge_export_data", {
            "p_campaign_id": campaign_id,
            "p_in_house_only": in_house_only,
            "p_llm_verified_only": True,
            "p_llm_is_negative": False,
            "p_limit": 50000,
        }).execute()
        data = result.data or {}
        rows = data.get("rows", []) if isinstance(data, dict) else []
        total = data.get("total", len(rows)) if isinstance(data, dict) else len(rows)

        # Sort by llm_verified_at DESC (RPC sorts by analyzed_at)
        rows.sort(key=lambda r: r.get("llm_verified_at") or "", reverse=True)

        # Client-side pagination
        offset = (page - 1) * page_size
        return rows[offset:offset + page_size], total
    except Exception as e:
        logger.warning("get_false_positives failed for campaign %s: %s", campaign_id, e)
        return [], 0


@st.cache_data(ttl=60)
def get_true_negatives(
    campaign_id: int,
    page: int = 1,
    page_size: int = 50,
    in_house_only: bool = True,
) -> tuple[list[dict], int]:
    """Get true negative items via RPC (avoids VIEW timeout).

    Returns:
        Tuple of (items list, total count)
    """
    from core.supabase_client import get_supabase_client

    try:
        client = get_supabase_client()
        result = client.rpc("get_nudge_export_data", {
            "p_campaign_id": campaign_id,
            "p_in_house_only": in_house_only,
            "p_llm_verified_only": True,
            "p_llm_is_negative": True,
            "p_limit": 50000,
        }).execute()
        data = result.data or {}
        rows = data.get("rows", []) if isinstance(data, dict) else []
        total = data.get("total", len(rows)) if isinstance(data, dict) else len(rows)

        # Sort by llm_verified_at DESC (RPC sorts by analyzed_at)
        rows.sort(key=lambda r: r.get("llm_verified_at") or "", reverse=True)

        # Client-side pagination
        offset = (page - 1) * page_size
        return rows[offset:offset + page_size], total
    except Exception as e:
        logger.warning("get_true_negatives failed for campaign %s: %s", campaign_id, e)
        return [], 0


def get_llm_verified_for_export(
    campaign_id: int,
    is_negative: bool,
    in_house_only: bool = True,
    include_full_answers: bool = False,
) -> list[dict]:
    """Get all LLM verified items for export via single RPC (avoids VIEW timeout).

    Returns:
        List of items with optional full answer text
    """
    from core.supabase_client import get_supabase_client

    try:
        client = get_supabase_client()
        result = client.rpc("get_nudge_export_data", {
            "p_campaign_id": campaign_id,
            "p_in_house_only": in_house_only,
            "p_llm_verified_only": True,
            "p_llm_is_negative": is_negative,
            "p_limit": 50000,
        }).execute()
        data = result.data or {}
        items = data.get("rows", []) if isinstance(data, dict) else []

        # Fetch full answers from Athena if requested
        if include_full_answers and items:
            from .athena_client import fetch_full_answers_batch
            answer_ids = [item["answer_id"] for item in items if item.get("answer_id")]
            if answer_ids:
                full_answers = fetch_full_answers_batch(answer_ids)
                for item in items:
                    aid = item.get("answer_id")
                    if aid and aid in full_answers:
                        item["answer_full"] = full_answers[aid]
                    else:
                        item["answer_full"] = item.get("answer_preview", "")

        return items
    except Exception as e:
        logger.warning("get_llm_verified_for_export failed for campaign %s: %s", campaign_id, e)
        return []


def get_sentiment_data_for_export(
    campaign_id: int,
    polarity: str | None = None,
    llm_status: str | None = None,
    in_house_only: bool = True,
    include_full_answers: bool = False,
    include_evidence: bool = False,
) -> list[dict]:
    """Export sentiment data via RPC (avoids VIEW timeout on large campaigns).

    Uses get_nudge_export_data RPC with inline CTE — WHERE campaign_id
    is applied before DISTINCT ON, enabling index scan instead of full
    VIEW materialization.

    Args:
        campaign_id: Campaign ID
        polarity: Polarity filter ('negative', 'positive', 'neutral', None=all)
        llm_status: LLM status filter ('verified', 'false_positive', 'true_negative', 'unverified', None=all)
        in_house_only: In-house brand filter
        include_full_answers: Include full answer text from Athena
        include_evidence: Include LLM evidence fields

    Returns:
        Filtered data list
    """
    from core.supabase_client import get_supabase_client

    try:
        client = get_supabase_client()

        # Map llm_status to RPC parameters
        llm_verified_only = llm_status in ("verified", "false_positive", "true_negative")
        llm_is_negative = None
        if llm_status == "true_negative":
            llm_is_negative = True
        elif llm_status == "false_positive":
            llm_is_negative = False

        params = {
            "p_campaign_id": campaign_id,
            "p_in_house_only": in_house_only,
            "p_llm_verified_only": llm_verified_only,
            "p_llm_is_negative": llm_is_negative,
            "p_limit": 50000,
        }

        result = client.rpc("get_nudge_export_data", params).execute()
        data = result.data or {}
        if isinstance(data, dict):
            items = data.get("rows", []) or []
        else:
            items = data if isinstance(data, list) else []

        # Apply polarity filter (kept client-side for simplicity)
        if polarity:
            items = [item for item in items if item.get("overall_polarity") == polarity]

        # Apply unverified filter (RPC only supports verified=True)
        if llm_status == "unverified":
            items = [item for item in items if not item.get("llm_verified")]

        if include_full_answers and items:
            from .athena_client import fetch_full_answers_batch
            answer_ids = [item["answer_id"] for item in items if item.get("answer_id")]
            if answer_ids:
                full_answers = fetch_full_answers_batch(answer_ids)
                for item in items:
                    aid = item.get("answer_id")
                    if aid and aid in full_answers:
                        item["answer_full"] = full_answers[aid]
                    else:
                        item["answer_full"] = item.get("answer_preview", "")

        return items
    except Exception as e:
        logger.warning("get_sentiment_data_for_export failed for campaign %s: %s", campaign_id, e)
        return []


@st.cache_data(ttl=60)
def get_answers_by_polarity(
    campaign_id: int,
    polarity: str,
    page: int = 1,
    page_size: int = 50,
    in_house_only: bool = False,
) -> tuple[list[dict], int]:
    """Get answers filtered by polarity via RPC (avoids VIEW timeout).

    Returns:
        Tuple of (items list, total count)
    """
    from core.supabase_client import get_supabase_client

    try:
        client = get_supabase_client()
        offset = (page - 1) * page_size

        params: dict = {
            "p_campaign_id": campaign_id,
            "p_in_house_only": False,
            "p_polarity": polarity,
            "p_offset": offset,
            "p_limit": page_size,
        }
        if in_house_only:
            params["p_has_in_house_brands"] = True

        result = client.rpc("get_nudge_export_data", params).execute()
        data = result.data or {}
        rows = data.get("rows", []) if isinstance(data, dict) else []
        total = data.get("total", len(rows)) if isinstance(data, dict) else len(rows)

        return rows, total
    except Exception as e:
        logger.warning("get_answers_by_polarity failed for campaign %s: %s", campaign_id, e)
        return [], 0


def get_polarity_stats(campaign_id: int, in_house_only: bool = False) -> dict:
    """Get polarity distribution via single RPC (avoids VIEW timeout).

    Uses get_campaign_sentiment_stats RPC — one table scan with COUNT FILTER.

    Returns:
        Dict with positive, neutral, negative counts
    """
    from core.supabase_client import get_supabase_client

    _empty = {"positive": 0, "neutral": 0, "negative": 0}
    try:
        client = get_supabase_client()
        result = client.rpc(
            "get_campaign_sentiment_stats", {"p_campaign_id": campaign_id}
        ).execute()
        stats = result.data or {}
        if isinstance(stats, list) and stats:
            stats = stats[0]
        if isinstance(stats, str):
            import json
            stats = json.loads(stats)
        if not isinstance(stats, dict):
            return _empty

        if in_house_only:
            return {
                "positive": stats.get("in_house_positive", 0),
                "neutral": stats.get("in_house_neutral", 0),
                "negative": stats.get("in_house_negative_polarity", 0),
            }
        return {
            "positive": stats.get("positive", 0),
            "neutral": stats.get("neutral", 0),
            "negative": stats.get("negative", 0),
        }
    except Exception:
        return _empty