#!/usr/bin/env python3
"""
Test Supabase client functionality for music player
"""

import os
from supabase import create_client, Client
from dotenv import load_dotenv

load_dotenv()

def test_supabase_client():
    """Test Supabase client functionality"""
    
    print("🔍 Testing Supabase Client for Music Player")
    print("=" * 60)
    
    try:
        # Get Supabase client
        url = os.environ.get("SUPABASE_URL")
        key = os.environ.get("SUPABASE_KEY")
        
        if not url or not key:
            print("❌ SUPABASE_URL and SUPABASE_KEY environment variables are required")
            return False
        
        print(f"✅ Supabase URL: {url}")
        print(f"✅ Supabase Key: {key[:10]}...")
        
        supabase = create_client(url, key)
        print("✅ Supabase client created successfully!")
        
        # Test basic connection
        print("\n🔗 Testing basic connection...")
        response = supabase.table("playlist_songs").select("*").limit(1).execute()
        print("✅ Basic query successful!")
        
        # Test playlists table
        print("\n📋 Testing playlists table...")
        try:
            response = supabase.table("playlists").select("*").execute()
            print(f"✅ Playlists table accessible! Found {len(response.data)} playlists")
        except Exception as e:
            print(f"⚠️  Playlists table not found or accessible: {e}")
        
        # Test playlist_songs table
        print("\n🎵 Testing playlist_songs table...")
        try:
            response = supabase.table("playlist_songs").select("*").execute()
            print(f"✅ Playlist_songs table accessible! Found {len(response.data)} songs")
        except Exception as e:
            print(f"⚠️  Playlist_songs table not found or accessible: {e}")
        
        # Test user_preferences table
        print("\n⚙️  Testing user_preferences table...")
        try:
            response = supabase.table("user_preferences").select("*").execute()
            print(f"✅ User_preferences table accessible! Found {len(response.data)} preferences")
        except Exception as e:
            print(f"⚠️  User_preferences table not found or accessible: {e}")
        
        return True
        
    except Exception as e:
        print(f"❌ Supabase client test failed: {e}")
        return False

def test_playlist_operations():
    """Test playlist CRUD operations"""
    
    print("\n🎵 Testing Playlist Operations")
    print("=" * 60)
    
    try:
        supabase = create_client(os.environ.get("SUPABASE_URL"), os.environ.get("SUPABASE_KEY"))
        
        # Test creating a playlist
        print("📝 Creating test playlist...")
        playlist_data = {"name": "Test Playlist"}
        response = supabase.table("playlists").insert(playlist_data).execute()
        playlist = response.data[0]
        playlist_id = playlist['id']
        print(f"✅ Created playlist: {playlist['name']} (ID: {playlist_id})")
        
        # Test adding songs to playlist
        print("🎵 Adding songs to playlist...")
        songs = ["Test Song 1.mp3", "Test Song 2.mp3"]
        song_data = [{"playlist_id": playlist_id, "song_name": song, "song_order": i} 
                    for i, song in enumerate(songs)]
        response = supabase.table("playlist_songs").insert(song_data).execute()
        print(f"✅ Added {len(response.data)} songs to playlist")
        
        # Test reading playlist songs
        print("📖 Reading playlist songs...")
        response = supabase.table("playlist_songs").select("song_name, song_order").eq("playlist_id", playlist_id).order("song_order").execute()
        songs = response.data
        print(f"✅ Retrieved {len(songs)} songs from playlist")
        for song in songs:
            print(f"   - {song['song_name']} (order: {song['song_order']})")
        
        # Test updating preference
        print("⚙️  Testing preference update...")
        preference_data = {"preference_key": "test_preference", "preference_value": "test_value"}
        response = supabase.table("user_preferences").upsert(preference_data).execute()
        print("✅ Preference updated successfully")
        
        # Clean up test data
        print("🧹 Cleaning up test data...")
        supabase.table("playlists").delete().eq("id", playlist_id).execute()
        supabase.table("user_preferences").delete().eq("preference_key", "test_preference").execute()
        print("✅ Test data cleaned up")
        
        return True
        
    except Exception as e:
        print(f"❌ Playlist operations test failed: {e}")
        return False

def create_tables_if_needed():
    """Create tables if they don't exist"""
    
    print("\n🔧 Creating Tables (if needed)")
    print("=" * 60)
    
    try:
        supabase = create_client(os.environ.get("SUPABASE_URL"), os.environ.get("SUPABASE_KEY"))
        
        # Note: Supabase client doesn't have table creation methods
        # Tables should be created manually in the Supabase dashboard
        # or using the SQL editor
        
        print("📋 Please create the following tables in your Supabase SQL Editor:")
        print()
        print("1. playlists table:")
        print("""
        CREATE TABLE playlists (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );
        """)
        
        print("2. playlist_songs table:")
        print("""
        CREATE TABLE playlist_songs (
            id SERIAL PRIMARY KEY,
            playlist_id INTEGER REFERENCES playlists(id) ON DELETE CASCADE,
            song_name VARCHAR(500) NOT NULL,
            song_order INTEGER DEFAULT 0,
            added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );
        """)
        
        print("3. user_preferences table:")
        print("""
        CREATE TABLE user_preferences (
            id SERIAL PRIMARY KEY,
            preference_key VARCHAR(100) UNIQUE NOT NULL,
            preference_value TEXT,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );
        """)
        
        return True
        
    except Exception as e:
        print(f"❌ Table creation guidance failed: {e}")
        return False

if __name__ == "__main__":
    print("🚀 Supabase Client Test for Music Player")
    print("=" * 60)
    
    # Test basic client functionality
    if test_supabase_client():
        print("\n✅ Basic Supabase client test passed!")
        
        # Test playlist operations
        if test_playlist_operations():
            print("\n✅ Playlist operations test passed!")
        else:
            print("\n⚠️  Playlist operations test failed - tables may not exist")
            create_tables_if_needed()
        
        print("\n🎉 Your Supabase client is working!")
        print("Next steps:")
        print("1. Create the required tables in Supabase SQL Editor")
        print("2. Set SUPABASE_URL and SUPABASE_KEY in Hugging Face Spaces")
        print("3. Deploy your music player!")
        
    else:
        print("\n❌ Supabase client test failed")
        print("Please check your SUPABASE_URL and SUPABASE_KEY environment variables")