# supabase_client.py

import json
import time
from supabase import create_client, Client
from functools import wraps

from config import (
    SUPABASE_URL, SUPABASE_KEY, SUPABASE_SERVICE_ROLE_KEY,
    SUPABASE_URL_1, SUPABASE_KEY_1
)

# --- Primary Global Client (Write/Storage Operations) ---
supabase: Client = None

def get_supabase_client() -> Client | None:
    """Singleton accessor for the Primary Supabase client."""
    global supabase
    if supabase is None:
        init_supabase_client()
    return supabase

def init_supabase_client():
    global supabase
    if not all([SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY]):
        print("[WARNING] Primary Supabase credentials missing or incomplete. Disabling cloud storage.")
        supabase = None
        return
        
    # Validate URL format before attempting to create client
    if not str(SUPABASE_URL).startswith("https://"):
        print("[ERROR] Primary Supabase URL is invalid (must start with https://). Disabling cloud storage.")
        supabase = None
        return

    try:
        supabase = create_client(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY)
    except Exception as e:
        print(f"[ERROR] Failed to connect to Primary Supabase: {e}")
        supabase = None

# --- Secondary Global Client (Read-Only DB2 Context) ---
supabase2: Client = None
_cached_races_db_fields = None

def get_supabase2_client() -> Client | None:
    """Singleton accessor for Database2 (Read-Only)."""
    global supabase2
    if supabase2 is None:
        if not all([SUPABASE_URL_1, SUPABASE_KEY_1]):
            print("[WARNING] Database2 credentials missing (SUPABASE_URL_1 / KEY_1). Context enrichment disabled.")
            return None
            
        if not str(SUPABASE_URL_1).startswith("https://"):
            print("[ERROR] Database2 URL is invalid. Context enrichment disabled.")
            return None

        try:
            supabase2 = create_client(SUPABASE_URL_1, SUPABASE_KEY_1)
            print("  - [SUCCESS] Database2 (Context) client initialized.")
        except Exception as e:
            print(f"[ERROR] Failed to connect to Database2: {e}")
            supabase2 = None
    return supabase2

def fetch_races_db_fields() -> list:
    """
    Fetches the races_db_fields table exactly once per runtime from Database2
    to enrich LLM extraction context without modifying output schemas.
    """
    global _cached_races_db_fields
    if _cached_races_db_fields is not None:
        return _cached_races_db_fields

    client2 = get_supabase2_client()
    if not client2:
        return[]

    try:
        # Strictly queries ONLY the races_db_fields table
        response = client2.table('races_db_fields').select('*').execute()
        _cached_races_db_fields = response.data if response.data else []
        print(f"  - [SUCCESS] Fetched {_cached_races_db_fields.__len__()} field context definitions from Database2.")
        return _cached_races_db_fields
    except Exception as e:
        print(f"[ERROR] Failed to fetch races_db_fields from Database2: {e}")
        return[]

# --- Decorator for resilience (Primary Client) ---
def supabase_operation(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        client = get_supabase_client()
        if not client:
            # Silently return if no client is configured to avoid spamming logs
            if "delete" in func.__name__: return False, "Supabase disabled."
            elif "list" in func.__name__: return[]
            return None
        
        for attempt in range(3):
            try:
                return func(*args, **kwargs)
            except Exception as e:
                if "Bucket not found" in str(e) or "Object not found" in str(e):
                    return None
                print(f"[WARNING] Supabase operation '{func.__name__}' failed on attempt {attempt + 1}: {e}")
                if attempt < 2:
                    time.sleep(2 ** attempt)
        return None 
    return wrapper

# --- Primary Client Functions ---

@supabase_operation
def get_knowledge_cache(event_key: str) -> dict | None:
    client = get_supabase_client()
    response = client.table('knowledge_cache').select('data').eq('event_key', event_key).limit(1).execute()
    if response.data:
        data = response.data[0].get('data')
        if isinstance(data, str):
            try: return json.loads(data)
            except json.JSONDecodeError: return None
        return data
    return None

@supabase_operation
def set_knowledge_cache(event_key: str, data: dict):
    client = get_supabase_client()
    client.table('knowledge_cache').upsert({'event_key': event_key, 'data': data}).execute()
    print(f"  - [SUCCESS] Supabase: Knowledge cache saved for '{event_key}'.")

@supabase_operation
def download_from_storage(file_path: str) -> str | None:
    client = get_supabase_client()
    response = client.storage.from_('crawl_cache').download(file_path)
    return response.decode('utf-8') if response else None

@supabase_operation
def upload_to_storage(file_path: str, content: str):
    client = get_supabase_client()
    try:
        client.storage.from_('crawl_cache').upload(file_path, content.encode('utf-8'), {"upsert": True})
    except Exception as e:
        if "Bucket not found" not in str(e):
             print(f"  - [WARNING] Supabase upload failed: {e}")