#!/usr/bin/env python3
"""
Discover existing tables in your Supabase database
"""

from supabase import create_client, Client
import pandas as pd

def discover_database_structure():
    """Discover what tables and data exist in the Supabase database"""
    
    url = "https://bnjblzcqaumctpehgoid.supabase.co"
    key = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM"
    
    print("🔍 Discovering your Supabase Database Structure")
    print("=" * 60)
    
    try:
        # Create Supabase client
        supabase: Client = create_client(url, key)
        print("✅ Connected to Supabase!")
        
        # Common table names to check
        common_tables = [
            'users', 'profiles', 'posts', 'products', 'orders', 'customers',
            'employees', 'tasks', 'projects', 'categories', 'items', 'data',
            'records', 'entries', 'logs', 'events', 'notifications', 'settings',
            'comments', 'reviews', 'messages', 'files', 'uploads', 'content',
            'articles', 'pages', 'menu', 'navigation', 'contacts', 'addresses'
        ]
        
        existing_tables = []
        
        print("\n🔍 Checking for existing tables...")
        
        for table_name in common_tables:
            try:
                # Try to query the table with a limit of 1 to see if it exists
                result = supabase.table(table_name).select("*").limit(1).execute()
                
                # If we get here without an exception, the table exists
                existing_tables.append(table_name)
                
                # Get some info about the table
                data_count = len(result.data) if result.data else 0
                print(f"  ✅ Found table: '{table_name}' (sample data: {data_count} rows)")
                
                # Show column names if data exists
                if result.data and len(result.data) > 0:
                    columns = list(result.data[0].keys())
                    print(f"     Columns: {', '.join(columns[:10])}{'...' if len(columns) > 10 else ''}")
                
            except Exception as e:
                # Table doesn't exist or we don't have access
                if "PGRST205" in str(e):  # Table not found error
                    continue
                else:
                    print(f"  ⚠️ Error accessing '{table_name}': {str(e)}")
        
        if existing_tables:
            print(f"\n🎉 Found {len(existing_tables)} accessible tables:")
            for table in existing_tables:
                print(f"  - {table}")
                
            print("\n📊 Getting sample data from first table...")
            first_table = existing_tables[0]
            try:
                sample_result = supabase.table(first_table).select("*").limit(5).execute()
                if sample_result.data:
                    df = pd.DataFrame(sample_result.data)
                    print(f"\nSample data from '{first_table}':")
                    print(df.to_string(index=False))
                else:
                    print(f"Table '{first_table}' exists but has no data")
                    
            except Exception as e:
                print(f"Error getting sample data: {str(e)}")
        else:
            print("\n⚠️ No common tables found or accessible with current permissions.")
            print("Your database might have custom table names or require different permissions.")
            
            # Try to get any available tables through RPC if available
            print("\n🔍 Attempting alternative discovery methods...")
            try:
                # Try some common RPC functions that might list tables
                for rpc_name in ['get_tables', 'list_tables', 'show_tables']:
                    try:
                        rpc_result = supabase.rpc(rpc_name).execute()
                        print(f"✅ RPC function '{rpc_name}' available: {rpc_result.data}")
                        break
                    except:
                        continue
            except Exception as e:
                print("No table discovery RPC functions found.")
        
        return existing_tables
        
    except Exception as e:
        print(f"❌ Error discovering database: {str(e)}")
        return []

if __name__ == "__main__":
    tables = discover_database_structure()
    
    if tables:
        print(f"\n🚀 Ready to use Gradio interface with your {len(tables)} tables!")
        print("Run: python app_supabase.py")
    else:
        print("\n💡 Tips:")
        print("1. Check your Supabase dashboard to see table names")
        print("2. Ensure Row Level Security (RLS) policies allow read access")
        print("3. Use the Gradio interface to try custom table names")