tests/test_username_submissions.py

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_username_submissions():
    """Test username-based submission system"""
    st.title("🧪 Username-Based Submission Test")
    
    supabase = init_supabase()
    if not supabase:
        st.error("Failed to initialize Supabase client")
        return
    
    try:
        # Test 1: Check if student_submissions table exists with username column
        st.subheader("🔍 Testing Database Structure")
        
        try:
            response = supabase.table('student_submissions').select('*').limit(1).execute()
            st.success("✅ student_submissions table exists and is accessible")
            
            # Check if it has username column
            if response.data:
                sample_data = response.data[0]
                if 'username' in sample_data:
                    st.success("✅ Table has username column")
                else:
                    st.error("❌ Table does not have username column")
            else:
                st.info("ℹ️ Table is empty, but accessible")
                
        except Exception as e:
            st.error(f"❌ student_submissions table error: {str(e)}")
            return
        
        # Test 2: Get all users
        st.subheader("👥 Available Users")
        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({
                        '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)
                
                # Use the first user for testing
                test_user = users_response.data[0]
                st.info(f"Using {test_user.get('username')} for testing")
                
            else:
                st.error("No users found in database")
                return
        except Exception as e:
            st.error(f"❌ Error fetching users: {str(e)}")
            return
        
        # Test 3: Try to insert a test submission with username
        st.subheader("📤 Testing Username-Based Submission")
        
        test_submission_data = {
            "questions": [
                {
                    "id": "test_q1",
                    "type": "textarea",
                    "question": "Test question",
                    "student_answer": "Test answer",
                    "max_length": 500
                }
            ]
        }
        
        test_data = {
            'username': test_user.get('username'),
            'week_number': 1,
            'submission_type': 'username_test',
            'submission_data': test_submission_data,
            'score': 5,
            'max_score': 5
        }
        
        st.write("**Attempting to insert test submission:**")
        st.json(test_data)
        
        try:
            insert_response = supabase.table('student_submissions').insert(test_data).execute()
            st.write(f"**Insert Response:** {insert_response}")
            st.write(f"**Response Data:** {insert_response.data}")
            st.write(f"**Response Count:** {insert_response.count}")
            
            if insert_response.data:
                st.success("✅ Test submission inserted successfully!")
            else:
                st.error("❌ No data returned from insert")
                
        except Exception as e:
            st.error(f"❌ Insert failed: {str(e)}")
        
        # Test 4: Try to retrieve the test submission
        st.subheader("📥 Testing Submission Retrieval")
        
        try:
            retrieve_response = supabase.table('student_submissions').select('*').eq('username', test_user.get('username')).eq('week_number', 1).eq('submission_type', 'username_test').execute()
            
            st.write(f"**Retrieve Response:** {retrieve_response}")
            st.write(f"**Retrieve Data:** {retrieve_response.data}")
            st.write(f"**Retrieve Count:** {retrieve_response.count}")
            
            if retrieve_response.data:
                st.success("✅ Test submission retrieved successfully!")
                st.json(retrieve_response.data[0])
            else:
                st.warning("⚠️ No submission found (insert may have failed)")
                
        except Exception as e:
            st.error(f"❌ Retrieve failed: {str(e)}")
        
        # Test 5: Test multiple insertions
        st.subheader("🔄 Testing Multiple Insertions")
        
        # Insert a second submission
        second_submission_data = {
            "questions": [
                {
                    "id": "test_q2",
                    "type": "textarea",
                    "question": "Second test question",
                    "student_answer": "Second test answer",
                    "max_length": 500
                }
            ]
        }
        
        second_data = {
            'username': test_user.get('username'),
            'week_number': 1,
            'submission_type': 'username_test',
            'submission_data': second_submission_data,
            'score': 8,
            'max_score': 10
        }
        
        st.write("**Attempting to insert second test submission:**")
        st.json(second_data)
        
        try:
            second_response = supabase.table('student_submissions').insert(second_data).execute()
            st.write(f"**Second Insert Response:** {second_response}")
            st.write(f"**Second Insert Data:** {second_response.data}")
            st.write(f"**Second Insert Count:** {second_response.count}")
            
            if second_response.data:
                st.success("✅ Second test submission inserted successfully!")
            else:
                st.error("❌ No data returned from second insert")
                
        except Exception as e:
            st.error(f"❌ Second insert failed: {str(e)}")
        
        # Test 6: Show all submissions for this user
        st.subheader("📊 All Submissions for Test User")
        
        try:
            all_user_submissions = supabase.table('student_submissions').select('*').eq('username', test_user.get('username')).execute()
            
            if all_user_submissions.data:
                st.success(f"Found {len(all_user_submissions.data)} submissions for {test_user.get('username')}:")
                
                submission_data = []
                for submission in all_user_submissions.data:
                    submission_data.append({
                        'Week': submission.get('week_number'),
                        'Type': submission.get('submission_type'),
                        'Score': f"{submission.get('score')}/{submission.get('max_score')}" if submission.get('score') else 'Not graded',
                        'Submitted': submission.get('submitted_at')
                    })
                
                st.dataframe(submission_data, use_container_width=True)
            else:
                st.info("No submissions found for this user")
                
        except Exception as e:
            st.error(f"❌ Error fetching user submissions: {str(e)}")
        
        # Test 7: Clean up test data
        st.subheader("🧹 Cleanup")
        
        if st.button("Clean up test submissions"):
            try:
                delete_response = supabase.table('student_submissions').delete().eq('submission_type', 'username_test').execute()
                st.success(f"✅ Cleaned up {len(delete_response.data) if delete_response.data else 0} test submissions")
            except Exception as e:
                st.error(f"❌ Cleanup failed: {str(e)}")
        
    except Exception as e:
        st.error(f"❌ Test failed: {str(e)}")

if __name__ == "__main__":
    test_username_submissions()