import streamlit as st
from supabase import create_client, Client
import os

def init_supabase():
    """Initialize Supabase client"""
    try:
        url = st.secrets.get("SUPABASE_URL")
        key = st.secrets.get("SUPABASE_KEY")
    except:
        url = os.getenv("SUPABASE_URL")
        key = os.getenv("SUPABASE_KEY")
    
    if not url or not key:
        st.error("Supabase credentials not found.")
        return None
    
    return create_client(url, key)

def test_database_connection():
    """Test database connection and user data"""
    st.title("🔍 Database Connection Test")
    
    supabase = init_supabase()
    if not supabase:
        st.error("Failed to initialize Supabase client")
        return
    
    try:
        # Test 1: Get all users
        st.subheader("📊 All Users in Database")
        response = supabase.table('users').select('*').execute()
        
        if response.data:
            st.success(f"Found {len(response.data)} users:")
            for user in response.data:
                st.write(f"**{user.get('username')}** - {user.get('full_name')} ({user.get('role')})")
                st.write(f"  Email: {user.get('email')}")
                st.write(f"  Password: '{user.get('password')}'")
                st.write("---")
        else:
            st.warning("No users found in database")
        
        # Test 2: Test function call
        st.subheader("🔧 Testing get_user_by_username Function")
        test_username = "instructor"
        
        try:
            response = supabase.rpc('get_user_by_username', {'username_param': test_username}).execute()
            if response.data:
                st.success(f"Function call successful for '{test_username}':")
                st.write(response.data[0])
            else:
                st.warning(f"Function returned no data for '{test_username}'")
        except Exception as e:
            st.error(f"Function call failed: {str(e)}")
        
        # Test 3: Direct table query
        st.subheader("📋 Direct Table Query")
        try:
            response = supabase.table('users').select('*').eq('username', test_username).execute()
            if response.data:
                st.success(f"Direct query successful for '{test_username}':")
                st.write(response.data[0])
            else:
                st.warning(f"Direct query returned no data for '{test_username}'")
        except Exception as e:
            st.error(f"Direct query failed: {str(e)}")
        
        # Test 4: Test password verification
        st.subheader("🔐 Password Verification Test")
        test_password = "password123"
        
        # Try function first
        try:
            response = supabase.rpc('get_user_by_username', {'username_param': test_username}).execute()
            if response.data:
                user = response.data[0]
                stored_password = user.get('password')
                st.write(f"Username: {test_username}")
                st.write(f"Stored password: '{stored_password}'")
                st.write(f"Test password: '{test_password}'")
                st.write(f"Password match: {stored_password == test_password}")
            else:
                st.warning("No user data to test password")
        except Exception as e:
            st.error(f"Password test failed: {str(e)}")
        
    except Exception as e:
        st.error(f"Database test failed: {str(e)}")

if __name__ == "__main__":
    test_database_connection()