import logging
from supabase import create_client, Client
from datetime import datetime

# Set up logging to a file
logging.basicConfig(filename='supabase_debug.log', level=logging.INFO, format='%(asctime)s %(levelname)s: %(message)s')

SUPABASE_URL = "https://padvkwgpejxjxkxyqbrm.supabase.co"
SUPABASE_KEY = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InBhZHZrd2dwZWp4anhreHlxYnJtIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTI1MDI2NjYsImV4cCI6MjA2ODA3ODY2Nn0.qpMqwRFhurv3YX4S_3G5EMk5IJ8vuAGvz3y2CZ6VxnQ"

supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)

def save_playlist_to_supabase(name, created_date=None):
    if created_date is None:
        created_date = datetime.utcnow().isoformat()
    data = {
        "name": name,
        "created_date": created_date
    }
    logging.info(f"Attempting to insert playlist: {data}")
    try:
        result = supabase.table("playlists").insert(data).execute()
        logging.info(f"Supabase insert response: {result}")
        if hasattr(result, 'data'):
            logging.info(f"Inserted data: {result.data}")
        if hasattr(result, 'error') and result.error:
            logging.error(f"Supabase error: {result.error}")
        return result
    except Exception as e:
        logging.error(f"Exception during Supabase insert: {e}")
        raise

def save_action_to_supabase(playlist_id, action_type, x, y, key, timestamp, playlist_name=None):
    data = {
        "playlist_id": playlist_id,
        "action_type": action_type,
        "x": x,
        "y": y,
        "key": key,
        "timestamp": timestamp
    }
    if playlist_name is not None:
        data["playlist_name"] = playlist_name
    logging.info(f"Attempting to insert action: {data}")
    try:
        result = supabase.table("actions").insert(data).execute()
        logging.info(f"Supabase insert response: {result}")
        if hasattr(result, 'data'):
            logging.info(f"Inserted data: {result.data}")
        if hasattr(result, 'error') and result.error:
            logging.error(f"Supabase error: {result.error}")
        return result
    except Exception as e:
        logging.error(f"Exception during Supabase action insert: {e}")
        raise 

def get_playlist_names_from_supabase():
    """Fetch all playlist names from Supabase, ordered by created_date descending."""
    try:
        result = supabase.table("playlists").select("name").order("created_date", desc=True).execute()
        if hasattr(result, 'data') and result.data:
            return [row['name'] for row in result.data if 'name' in row]
        return []
    except Exception as e:
        logging.error(f"Exception during Supabase fetch playlists: {e}")
        return [] 

def playlist_name_exists_in_supabase(name):
    """Return True if a playlist with the given name exists in Supabase, else False."""
    try:
        result = supabase.table("playlists").select("id").eq("name", name).limit(1).execute()
        if hasattr(result, 'data') and result.data:
            return True
        return False
    except Exception as e:
        logging.error(f"Exception during Supabase playlist name check: {e}")
        return False 

def save_screenshot_analysis_to_supabase(playlist_name, screenshot_path, openai_result):
    """Save screenshot analysis result to Supabase."""
    data = {
        "playlist_name": playlist_name,
        "screenshot_path": screenshot_path,
        "openai_result": openai_result,
    }
    try:
        result = supabase.table("screenshot_analysis").insert(data).execute()
        return result
    except Exception as e:
        logging.error(f"Exception during Supabase screenshot analysis insert: {e}")
        return None 

def get_next_auto_extractor_job():
    """Fetch the first unprocessed auto_extractor job from Supabase."""
    try:
        result = supabase.table("auto_extractor").select("*").order("created_at").limit(10).execute()
        logging.info(f"DEBUG: Raw Supabase result: {result}")
        # print("DEBUG: Raw Supabase result:", result)
        if hasattr(result, 'data') and result.data:
            for row in result.data:
                logging.info(f"DEBUG: Row: {row}, processed type: {type(row.get('processed'))}")
                # print(f"DEBUG: Row: {row}, processed type: {type(row.get('processed'))}")
                if row.get('processed') == 0:
                    return row
        return None
    except Exception as e:
        logging.error(f"Exception during Supabase auto_extractor fetch: {e}")
        # print(f"Exception during Supabase auto_extractor fetch: {e}")
        return None 

def mark_auto_extractor_job_processed(job_id):
    """Mark the given auto_extractor job as processed in Supabase."""
    try:
        result = supabase.table("auto_extractor").update({"processed": 1}).eq("id", job_id).execute()
        return result
    except Exception as e:
        logging.error(f"Exception during Supabase auto_extractor update: {e}")
        return None