supabase_service.py

import os
from supabase import create_client, Client
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Replace with your Supabase URL and API key
SUPABASE_URL: str = os.getenv("SUPABASE_URL")
SUPABASE_KEY: str = os.getenv("SUPABASE_KEY")

# Initialize the Supabase client
supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)

# Define the bucket name (you can create one in the Supabase Storage section)
BUCKET_NAME = "csvcharts"

async def upload_file_to_supabase(file_path: str, file_name: str, chat_id: str) -> str:
    """
    Uploads an image to Supabase Storage and returns the public URL.
    Saves the mapping between public_url, chart_name, and chat_id in the database.
    """
    if not os.path.exists(file_path):
        raise FileNotFoundError(f"The file {file_path} does not exist.")

    with open(file_path, "rb") as f:
        file_data = f.read()

    try:
        res = supabase.storage.from_(BUCKET_NAME).upload(file_name, file_data)
        print("Upload response:", res)
    except Exception as e:
        raise Exception(f"Failed to upload file: {e}")

    public_url = supabase.storage.from_(BUCKET_NAME).get_public_url(file_name)
    print("Public URL:", public_url)

    # Save the mapping to the database including chat_id
    try:
        supabase.table("chart_mappings").insert({
            "public_url": public_url,
            "chart_name": file_name,
            "chat_id": chat_id
        }).execute()
    except Exception as e:
        # Try to delete the uploaded file if DB insertion fails
        supabase.storage.from_(BUCKET_NAME).remove([file_name])
        raise Exception(f"Failed to save mapping to database: {e}")

    return public_url

async def get_chart_name_by_url(public_url: str) -> str:
    """
    Retrieves the chart_name using the public_url from the database.
    """
    try:
        response = supabase.table("chart_mappings").select("chart_name").eq("public_url", public_url).execute()
        if response.data:
            return response.data[0]["chart_name"]
        else:
            return None
    except Exception as e:
        raise Exception(f"Failed to retrieve chart_name: {e}")