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 get_user_id(username):
    """Get user ID from database by username"""
    supabase = init_supabase()
    if not supabase:
        return None
    
    try:
        response = supabase.rpc('get_user_by_username', {'username_param': username}).execute()
        if response.data:
            return response.data[0]['id']
        
        # Fallback: query the table directly
        response = supabase.table('users').select('id').eq('username', username).execute()
        if response.data:
            return response.data[0]['id']
        
        return None
    except Exception as e:
        st.error(f"Database error: {str(e)}")
        return None

def debug_user_id():
    """Debug user ID issues"""
    st.title("🔍 User ID Debug")
    
    supabase = init_supabase()
    if not supabase:
        st.error("Failed to initialize Supabase client")
        return
    
    try:
        # Test 1: Show all users in database
        st.subheader("📊 All Users in Database")
        try:
            users_response = supabase.table('users').select('*').execute()
            if users_response.data:
                st.success(f"Found {len(users_response.data)} users:")
                
                # Create a table to display users
                user_data = []
                for user in users_response.data:
                    user_data.append({
                        'ID': user.get('id'),
                        'Username': user.get('username'),
                        'Full Name': user.get('full_name'),
                        'Role': user.get('role'),
                        'Email': user.get('email')
                    })
                
                st.dataframe(user_data, use_container_width=True)
                
            else:
                st.error("No users found in database")
                return
        except Exception as e:
            st.error(f"❌ Error fetching users: {str(e)}")
            return
        
        # Test 2: Test get_user_id function for each user
        st.subheader("🔧 Testing get_user_id Function")
        
        for user in users_response.data:
            username = user.get('username')
            actual_id = user.get('id')
            
            st.write(f"**Testing username:** {username}")
            st.write(f"**Actual ID in database:** {actual_id}")
            
            # Test the function
            function_id = get_user_id(username)
            st.write(f"**Function returned ID:** {function_id}")
            
            if function_id == actual_id:
                st.success("✅ Function returns correct ID")
            else:
                st.error("❌ Function returns wrong ID")
            
            st.write("---")
        
        # Test 3: Test with the problematic ID
        st.subheader("🚨 Testing Problematic ID")
        problematic_id = "6ab26f7f-8c00-40bf-b925-a2f59a0c09ef"
        
        st.write(f"**Problematic ID:** {problematic_id}")
        
        # Check if this ID exists in users table
        try:
            check_response = supabase.table('users').select('*').eq('id', problematic_id).execute()
            if check_response.data:
                st.success("✅ This ID exists in users table")
                st.write(f"**User:** {check_response.data[0]}")
            else:
                st.error("❌ This ID does NOT exist in users table")
        except Exception as e:
            st.error(f"❌ Error checking ID: {str(e)}")
        
        # Test 4: Try to find which user this ID belongs to (if any)
        st.subheader("🔍 Finding User for Problematic ID")
        
        # Check if this ID exists in any table
        try:
            # Check student_submissions table
            submissions_response = supabase.table('student_submissions').select('*').eq('student_id', problematic_id).execute()
            if submissions_response.data:
                st.warning(f"⚠️ Found {len(submissions_response.data)} submissions with this student_id")
                for submission in submissions_response.data:
                    st.write(f"  - Week {submission.get('week_number')}, Type: {submission.get('submission_type')}")
            else:
                st.info("ℹ️ No submissions found with this student_id")
        except Exception as e:
            st.error(f"❌ Error checking submissions: {str(e)}")
        
        # Test 5: Test submission with valid user
        st.subheader("📤 Test Submission with Valid User")
        
        if users_response.data:
            # Use the first user for testing
            test_user = users_response.data[0]
            st.write(f"**Using user:** {test_user.get('username')} (ID: {test_user.get('id')})")
            
            test_data = {
                'student_id': test_user.get('id'),
                'week_number': 1,
                'submission_type': 'debug_test',
                'submission_data': {
                    "questions": [{
                        "id": "debug_q1",
                        "type": "textarea",
                        "question": "Debug test question",
                        "student_answer": "Debug test answer",
                        "max_length": 500
                    }]
                },
                'score': 5,
                'max_score': 5
            }
            
            st.write("**Test submission data:**")
            st.json(test_data)
            
            try:
                insert_response = supabase.table('student_submissions').insert(test_data).execute()
                if insert_response.data:
                    st.success("✅ Test submission successful!")
                else:
                    st.error("❌ Test submission failed - no data returned")
            except Exception as e:
                st.error(f"❌ Test submission failed: {str(e)}")
        
        # Test 6: Clean up
        st.subheader("🧹 Cleanup")
        
        if st.button("Clean up debug test submissions"):
            try:
                delete_response = supabase.table('student_submissions').delete().eq('submission_type', 'debug_test').execute()
                st.success(f"✅ Cleaned up {len(delete_response.data) if delete_response.data else 0} debug test submissions")
            except Exception as e:
                st.error(f"❌ Cleanup failed: {str(e)}")
        
    except Exception as e:
        st.error(f"❌ Debug failed: {str(e)}")

if __name__ == "__main__":
    debug_user_id()