db.py

import streamlit as st
from supabase import create_client, Client, ClientOptions
from datetime import datetime, timezone
import os

def get_supabase_client() -> Client:
    # Try using environment variables or st.secrets
    url = os.environ.get("SUPABASE_URL")
    key = os.environ.get("SUPABASE_KEY")
    
    if not url or not key:
        try:
            if hasattr(st, "secrets") and st.secrets:
                url = url or st.secrets.get("SUPABASE_URL")
                key = key or st.secrets.get("SUPABASE_KEY")
        except Exception:
            pass
            
    if not url or not key:
        # Try loading from secrets.toml directly
        import toml
        try:
            secrets_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), ".streamlit", "secrets.toml")
            if os.path.exists(secrets_path):
                secrets = toml.load(secrets_path)
                url = url or secrets.get("SUPABASE_URL")
                key = key or secrets.get("SUPABASE_KEY")
        except Exception:
            pass
            
    if not url or not key:
        raise ValueError("SUPABASE_URL and SUPABASE_KEY must be set in environment variables or secrets")
        
    try:
        if "supabase_client" not in st.session_state:
            st.session_state.supabase_client = create_client(url, key)
        return st.session_state.supabase_client
    except Exception:
        # Fallback to creating a new client if session_state is unavailable
        return create_client(url, key)

def get_current_user_id(client=None):
    if client is None:
        client = get_supabase_client()
    if hasattr(client, "current_user_id"):
        return client.current_user_id
    try:
        session = client.auth.get_session()
        if session and session.user:
            return session.user.id
    except Exception:
        pass
    return None

def get_active_streak(client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    res = client.table('user_streaks').select('*').eq('user_id', user_id).limit(1).execute()
    if res.data:
        return res.data[0]
    else:
        new_streak = {
            "user_id": user_id,
            "current_streak": 0,
            "longest_streak": 0,
            "last_active_date": None
        }
        res2 = client.table('user_streaks').insert(new_streak).execute()
        return res2.data[0] if res2.data else None

def update_streak(streak_data, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    return client.table('user_streaks').update({
        "current_streak": streak_data["current_streak"],
        "longest_streak": streak_data["longest_streak"],
        "last_active_date": streak_data["last_active_date"],
        "updated_at": datetime.now(timezone.utc).isoformat()
    }).eq('user_id', user_id).execute()

def get_due_reviews(client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return []
    now_iso = datetime.now(timezone.utc).isoformat()
    res = client.table('user_reviews').select('*, problems(*)').eq('user_id', user_id).lte('next_review', now_iso).order('next_review').execute()
    return res.data

def update_review(review_id, update_data, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    return client.table('user_reviews').update(update_data).eq('id', review_id).eq('user_id', user_id).execute()

def log_trace(message: str):
    import datetime
    import os
    try:
        log_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "sync.log")
        with open(log_path, "a", encoding="utf-8") as f:
            f.write(f"[{datetime.datetime.now().isoformat()}] {message}\n")
    except Exception:
        pass

def insert_problem(problem_data, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        log_trace("db: insert_problem failed - no user_id")
        return None
    problem_data["user_id"] = user_id
    log_trace(f"db: Inserting problem '{problem_data['name']}' with code length {len(problem_data.get('reference_code', '') or '')}")
    res = client.table('problems').insert(problem_data).execute()
    log_trace(f"db: Insert completed, returned ID: {res.data[0]['id'] if res.data else 'None'}")
    return res

def update_problem(problem_id, update_data, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        log_trace("db: update_problem failed - no user_id")
        return None
    log_trace(f"db: Updating problem {problem_id} with keys {list(update_data.keys())} and code length {len(update_data.get('reference_code', '') or '')}")
    res = client.table('problems').update(update_data).eq('id', problem_id).eq('user_id', user_id).execute()
    log_trace(f"db: Update completed, returned data: {res.data}")
    return res

def insert_review(review_data, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    review_data["user_id"] = user_id
    return client.table('user_reviews').insert(review_data).execute()

def get_problem_by_doc_id(doc_id, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    res = client.table('problems').select('*').eq('user_id', user_id).eq('google_doc_id', doc_id).execute()
    return res.data[0] if res.data else None

def get_problem_by_name(name, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    res = client.table('problems').select('*').eq('user_id', user_id).eq('name', name).execute()
    return res.data[0] if res.data else None

def insert_journey_progress(problem_number: int, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    return client.table('user_journey_progress').upsert({
        "user_id": user_id,
        "problem_number": problem_number
    }, on_conflict="user_id,problem_number").execute()

def delete_journey_progress(problem_number: int, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    return client.table('user_journey_progress').delete().eq('user_id', user_id).eq('problem_number', problem_number).execute()

def toggle_journey_progress(problem_number: int, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    res = client.table('user_journey_progress').select('id').eq('user_id', user_id).eq('problem_number', problem_number).execute()
    if res.data:
        return delete_journey_progress(problem_number, client)
    else:
        return insert_journey_progress(problem_number, client)

def get_user_journey_progress(client=None) -> set:
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return set()
    res = client.table('user_journey_progress').select('problem_number').eq('user_id', user_id).execute()
    return {row['problem_number'] for row in res.data} if res.data else set()

def get_problem_titles_by_number(client=None) -> dict:
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    res = client.table('problems').select('problem_number, name, user_id').execute()
    mapping = {}
    if res.data:
        # Sort so that current user's titles override others
        rows = sorted(res.data, key=lambda r: 1 if r.get('user_id') == user_id else 0)
        for row in rows:
            num = row.get('problem_number')
            if num is not None:
                base_name = row['name'].split(" - ")[0].strip()
                mapping[num] = base_name
    return mapping

def get_problems_by_number(problem_number: int, client=None) -> list:
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    
    # Fetch all versions of this problem number
    res = client.table('problems').select('*').eq('problem_number', problem_number).execute()
    problems = res.data if res.data else []
    
    if not problems:
        return []
        
    # Prioritize current user's problem
    if user_id:
        user_probs = [p for p in problems if p.get('user_id') == user_id]
        if user_probs:
            return user_probs
            
    # Fallback to returning all public versions
    return problems

def get_reviews_by_problem_id(problem_id: str, client=None) -> list:
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return []
    res = client.table('user_reviews').select('*, problems(*)').eq('user_id', user_id).eq('problem_id', problem_id).execute()
    return res.data if res.data else []

def get_user_score(client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    res = client.table('user_scores').select('*').eq('user_id', user_id).limit(1).execute()
    if res.data:
        return res.data[0]
    else:
        new_score = {
            "user_id": user_id,
            "total_score": 0,
            "python_challenges_completed": 0,
            "sql_challenges_completed": 0
        }
        try:
            res2 = client.table('user_scores').insert(new_score).execute()
            return res2.data[0] if res2.data else None
        except Exception:
            return None

def increment_user_score(points: int, challenge_type: str, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    
    current_score = get_user_score(client)
    if not current_score:
        return None
        
    update_data = {
        "total_score": current_score["total_score"] + points,
        "updated_at": datetime.now(timezone.utc).isoformat()
    }
    
    if challenge_type == "python":
        update_data["python_challenges_completed"] = current_score["python_challenges_completed"] + 1
    elif challenge_type == "mysql":
        update_data["sql_challenges_completed"] = current_score["sql_challenges_completed"] + 1
        
    return client.table('user_scores').update(update_data).eq('user_id', user_id).execute()

def add_challenge_history(challenge_type: str, challenge_title: str, points_awarded: int, client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return None
    history_data = {
        "user_id": user_id,
        "challenge_type": challenge_type,
        "challenge_title": challenge_title,
        "points_awarded": points_awarded
    }
    return client.table('challenge_history').insert(history_data).execute()

def get_challenge_history(client=None):
    if client is None:
        client = get_supabase_client()
    user_id = get_current_user_id(client)
    if not user_id:
        return []
    res = client.table('challenge_history').select('*').eq('user_id', user_id).order('completed_at', desc=True).execute()
    return res.data if res.data else []