utils.py

import os
import pandas as pd
from supabase import create_client, Client
from typing import Dict
from dotenv import load_dotenv
load_dotenv()

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_ANON_KEY")

def get_supabase_client() -> Client:
    if not SUPABASE_URL or not SUPABASE_KEY:
        raise ValueError("Missing SUPABASE_URL or SUPABASE_ANON_KEY in env")
    return create_client(SUPABASE_URL, SUPABASE_KEY)

def save_model_responses_row(supabase: Client, prompt_id: str, model_map_values: Dict[str, str]):
    """
    model_map_values: mapping of db column -> model textual response
    """
    row = {"prompt_id": prompt_id}
    row.update(model_map_values)
    supabase.table("model_responses").insert(row).execute()

def save_parsed_scores(supabase: Client, prompt_id: str, model_name: str, scores: Dict[str, float], evaluation_text: str, winner: str = None, margin: str = None, judge_model: str = "x-ai/grok-4-fast"):
    import json
    payload = {
        "prompt_id": prompt_id,
        "model_name": model_name,
        "scores": json.dumps(scores),
        "evaluation_text": evaluation_text,
        "judge_model": judge_model,
        "winner": winner,
        "margin": margin
    }
    supabase.table("llm_evaluations").insert(payload).execute()

def append_scores_to_csv(csv_path: str, prompt_id: str, prompt_text: str, model_name: str, scores: Dict[str, float]):
    df_new = pd.DataFrame([{
        "prompt_id": prompt_id,
        "prompt_text": prompt_text,
        "model_name": model_name,
        **scores
    }])
    if os.path.exists(csv_path):
        df = pd.read_csv(csv_path)
        df = pd.concat([df, df_new], ignore_index=True)
    else:
        df = df_new
    df.to_csv(csv_path, index=False)
    return csv_path

def compute_rankings_from_csv(csv_path: str):
    df = pd.read_csv(csv_path)
    # select numeric metric columns (exclude prompt_id, prompt_text, model_name)
    metric_cols = [c for c in df.columns if c not in ("prompt_id", "prompt_text", "model_name")]
    # compute average per model across all prompts and metrics
    # melt -> groupby
    df_melt = df.melt(id_vars=["model_name"], value_vars=metric_cols, var_name="metric", value_name="score")
    # drop NaNs
    df_melt = df_melt.dropna(subset=["score"])
    avg_per_model = df_melt.groupby("model_name")["score"].mean().reset_index().sort_values("score", ascending=False)
    return avg_per_model