core/supabase_research.py

"""Supabase research queries — topic clusters, cross-model analysis.

Module-level functions extracted from supabase_client.py.
All functions use get_supabase_client() from the parent module.
"""


def get_topic_clusters(campaign_id: int, source: str | None = None) -> list[dict]:
    """Fetch topic clusters with scores for a campaign, optionally filtered by source.

    Returns:
        List of cluster dicts sorted by opportunity_score DESC.
    """
    from core.supabase_client import get_supabase_client

    client = get_supabase_client()
    query = (
        client.table("topic_clusters")
        .select(
            "id, cluster_label, fanout_count, unique_questions, "
            "attention_score, citation_density, opportunity_score, "
            "sample_fanouts, top_sources, source"
        )
        .eq("campaign_id", campaign_id)
    )
    if source:
        query = query.eq("source", source)
    result = query.order("opportunity_score", desc=True).execute()
    return result.data or []


def get_topic_map_snapshot(campaign_id: int, source: str | None = None) -> dict | None:
    """Fetch latest UMAP 2D coordinates for visualization.

    Returns:
        Dict with coordinates and algorithm_params, or None.
    """
    from core.supabase_client import get_supabase_client

    client = get_supabase_client()
    result = (
        client.table("topic_map_snapshots")
        .select("coordinates, algorithm_params")
        .eq("campaign_id", campaign_id)
        .order("created_at", desc=True)
        .execute()
    )
    if not result.data:
        return None
    if source:
        for snap in result.data:
            params = snap.get("algorithm_params") or {}
            if params.get("source") == source:
                return snap
        return None
    return result.data[0]


def get_cross_model_analysis(
    campaign_chatgpt: int,
    campaign_gemini: int,
) -> dict | None:
    """Fetch cross-model analysis summary (NMI, match count).

    Returns:
        Dict with nmi_score, total_matched_topics, algorithm_params, or None.
    """
    from core.supabase_client import get_supabase_client

    client = get_supabase_client()
    result = (
        client.table("cross_model_analysis")
        .select("nmi_score, total_matched_topics, algorithm_params, created_at")
        .eq("campaign_chatgpt", campaign_chatgpt)
        .eq("campaign_gemini", campaign_gemini)
        .limit(1)
        .execute()
    )
    return result.data[0] if result.data else None


def get_gap_scores(
    campaign_chatgpt: int,
    campaign_gemini: int,
) -> list[dict]:
    """Fetch cross-model topic matches with GapScore and quadrant.

    Returns:
        List of match dicts with cluster labels, sorted by gap_score DESC.
    """
    from core.supabase_client import get_supabase_client

    client = get_supabase_client()
    result = (
        client.table("cross_model_topic_matches")
        .select(
            "id, chatgpt_cluster_id, gemini_cluster_id, "
            "match_score, label_similarity, centroid_similarity, "
            "demand_percentile, supply_percentile, gap_score, quadrant"
        )
        .eq("campaign_chatgpt", campaign_chatgpt)
        .eq("campaign_gemini", campaign_gemini)
        .not_.is_("gap_score", "null")
        .order("gap_score", desc=True)
        .execute()
    )
    matches = result.data or []

    # Enrich with cluster labels (batch query instead of N+1)
    if matches:
        chatgpt_ids = [m["chatgpt_cluster_id"] for m in matches]
        gemini_ids = [m["gemini_cluster_id"] for m in matches]
        all_ids = list(set(chatgpt_ids + gemini_ids))

        label_map = {}
        label_result = (
            client.table("topic_clusters")
            .select("id, cluster_label")
            .in_("id", all_ids)
            .execute()
        )
        for row in (label_result.data or []):
            label_map[row["id"]] = row.get("cluster_label", "")

        for m in matches:
            m["chatgpt_label"] = label_map.get(m["chatgpt_cluster_id"], "")
            m["gemini_label"] = label_map.get(m["gemini_cluster_id"], "")

    return matches


def find_cross_model_pair(campaign_id: int) -> dict | None:
    """Find cross-model analysis pair containing this campaign_id.

    Checks both chatgpt and gemini sides so the sidebar only needs one ID.

    Returns:
        Dict with campaign_chatgpt, campaign_gemini, nmi_score,
        total_matched_topics, or None if no pair exists.
    """
    from core.supabase_client import get_supabase_client

    client = get_supabase_client()
    resp = (
        client.table("cross_model_analysis")
        .select("campaign_chatgpt, campaign_gemini, nmi_score, total_matched_topics")
        .or_(f"campaign_chatgpt.eq.{campaign_id},campaign_gemini.eq.{campaign_id}")
        .limit(1)
        .execute()
    )
    if resp.data:
        return resp.data[0]
    return None