db_conversations.py

from supabase import create_client, Client
import os

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

supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)


def save_conversation(
    thread_id: str,
    user_type: str,
    question: str,
    answer: str,
    flagged: bool = False,
    is_lead: bool = False,
    is_escalated: bool = False,
    escalation_status: str = "open",
    assigned_to: str = None,
):
    """Save one conversation for a user session (with lead/escalation info)."""
    data = {
        "thread_id": thread_id,
        "user_type": user_type,
        "question": question,
        "answer": answer,
        "flagged": flagged,
        "is_lead": is_lead,
        "is_escalated": is_escalated,
        "escalation_status": escalation_status,
        "assigned_to": assigned_to,
    }
    result = supabase.table("conversations").insert(data).execute()
    # Return the inserted row ID
    return result.data[0]["id"] if result.data else None


def get_conversations(thread_id: str):
    """Fetch all conversations for a specific thread (user)."""
    result = supabase.table("conversations").select("*").eq("thread_id", thread_id).execute()
    return result.data


def update_flag(
    thread_id: str,
    conversation_id: str,
    flagged: bool = True,
    is_escalated: bool = True,
    escalation_status: str = "pending",
    assigned_to: str | None = None,
):
    """Update a conversation row to mark it flagged/escalated."""
    update_data = {
        "flagged": flagged,
        "is_escalated": is_escalated,
        "escalation_status": escalation_status,
        "assigned_to": assigned_to,
    }
    result = (
        supabase.table("conversations")
        .update(update_data)
        .eq("id", conversation_id)   # unique row id
        .eq("thread_id", thread_id)  # safety check
        .execute()
    )
    return result.data