supabase_service.py

import logging
import os
from typing import Optional
from dotenv import load_dotenv
from supabase import create_client, Client

logging.basicConfig(
    format="%(asctime)s - %(name)s - %(levelname)s - %(message)s",
    level=logging.INFO
)
logger = logging.getLogger("supabase_service")

load_dotenv()

# --- Supabase Setup ---
SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_KEY")
BUCKET_NAME = "csvcharts"

# Initialize Supabase Client
supabase: Optional[Client] = None
if SUPABASE_URL and SUPABASE_KEY:
    try:
        supabase = create_client(SUPABASE_URL, SUPABASE_KEY)
        logger.info("Supabase client initialized successfully.")
    except Exception as e:
        logger.error(f"Failed to initialize Supabase: {e}")
else:
    logger.warning("WARNING: SUPABASE_URL or SUPABASE_KEY not set. Uploads will fail.")
    
    

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 (url, name, chat_id) in the DB.
    """
    if not supabase:
        raise Exception("Supabase client is not initialized. Check .env variables.")

    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()

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

    # 2. Get Public URL
    public_url = supabase.storage.from_(BUCKET_NAME).get_public_url(file_name)
    logger.info(f"Generated Public URL: {public_url}")

    # 3. Save mapping to Database
    try:
        supabase.table("chart_mappings").insert({
            "public_url": public_url,
            "chart_name": file_name,
            "chat_id": chat_id
        }).execute()
    except Exception as e:
        logger.error(f"Failed to save mapping to DB: {e}")
        # Cleanup uploaded file to avoid orphans
        try:
            supabase.storage.from_(BUCKET_NAME).remove([file_name])
        except:
            pass
        raise Exception(f"Failed to save mapping to database: {e}")

    return public_url


def upload_bytes_to_supabase(image_bytes: bytes, file_name: str, chat_id: str) -> str:
    """
    Uploads raw bytes directly to Supabase.
    """
    if not supabase:
        raise Exception("Supabase client not initialized.")

    # 1. Upload bytes directly
    try:
        # Supabase Python SDK accepts bytes directly
        supabase.storage.from_(BUCKET_NAME).upload(
            path=file_name,
            file=image_bytes,
            file_options={"content-type": "image/png"}
        )
    except Exception as e:
        raise Exception(f"Supabase Storage error: {e}")

    # 2. Get Public URL
    public_url = supabase.storage.from_(BUCKET_NAME).get_public_url(file_name)

    # 3. Save mapping to Database
    supabase.table("chart_mappings").insert({
        "public_url": public_url,
        "chart_name": file_name,
        "chat_id": chat_id
    }).execute()

    return public_url