import gradio as gr
import os
from typing import List, Dict, Any
import pandas as pd
from dotenv import load_dotenv
from supabase import create_client, Client

# Load environment variables
load_dotenv()

class SupabaseConnection:
    def __init__(self):
        """Initialize Supabase client using API key"""
        self.url = "https://bnjblzcqaumctpehgoid.supabase.co"
        self.key = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImJuamJsemNxYXVtY3RwZWhnb2lkIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgyOTA0OTQsImV4cCI6MjA3Mzg2NjQ5NH0.L4wPzuBj9dlSKpYxO1eDX-57KcP0mbNfN8stmTB-STM"
        
        try:
            self.supabase: Client = create_client(self.url, self.key)
            self.connection_status = "✅ Supabase connection successful!"
        except Exception as e:
            self.supabase = None
            self.connection_status = f"❌ Supabase connection failed: {str(e)}"
    
    def test_connection(self) -> str:
        """Test the Supabase connection"""
        if not self.supabase:
            return self.connection_status
        
        try:
            # Try to fetch from a system table to test connection
            result = self.supabase.rpc('version').execute()
            return "✅ Supabase connection is working!"
        except Exception as e:
            return f"❌ Connection test failed: {str(e)}"
    
    def get_tables(self) -> List[str]:
        """Get list of tables from the database"""
        if not self.supabase:
            return ["No connection"]
        
        try:
            # Get tables from information schema
            result = self.supabase.rpc('get_tables_list').execute()
            if result.data:
                return result.data
            else:
                # Fallback: try to get some common table names
                # This is a workaround since direct SQL queries might be limited
                return ["users", "posts", "profiles"]  # Common table names
        except Exception as e:
            return [f"Error: {str(e)}"]
    
    def get_table_data(self, table_name: str, limit: int = 100) -> pd.DataFrame:
        """Get data from a specific table"""
        if not self.supabase:
            return pd.DataFrame([{"Error": "No Supabase connection"}])
        
        if not table_name or table_name == "No connection":
            return pd.DataFrame([{"Message": "Please select a valid table"}])
        
        try:
            # Query the table
            result = self.supabase.table(table_name).select("*").limit(limit).execute()
            
            if result.data:
                return pd.DataFrame(result.data)
            else:
                return pd.DataFrame([{"Message": f"No data found in table '{table_name}'"}])
                
        except Exception as e:
            return pd.DataFrame([{"Error": f"Failed to query table '{table_name}': {str(e)}"}])
    
    def create_sample_table(self) -> str:
        """Create a sample table"""
        if not self.supabase:
            return "❌ No Supabase connection"
        
        try:
            # Note: Creating tables requires elevated permissions
            # This might not work with the anon key
            result = self.supabase.rpc('create_sample_table').execute()
            return "✅ Sample table created successfully!"
        except Exception as e:
            return f"❌ Error creating table: {str(e)}. Note: Creating tables requires database admin permissions."
    
    def insert_sample_data(self, name: str, email: str, age: int) -> str:
        """Insert data into sample table"""
        if not self.supabase:
            return "❌ No Supabase connection"
        
        try:
            data = {
                "name": name,
                "email": email,
                "age": age
            }
            
            # Try to insert into a sample_data table
            result = self.supabase.table("sample_data").insert(data).execute()
            
            if result.data:
                return f"✅ Successfully added: {name} ({email})"
            else:
                return "❌ Failed to insert data"
                
        except Exception as e:
            return f"❌ Error inserting data: {str(e)}"
    
    def execute_rpc_function(self, function_name: str, params: Dict[str, Any] = None) -> pd.DataFrame:
        """Execute a remote procedure call (RPC) function"""
        if not self.supabase:
            return pd.DataFrame([{"Error": "No Supabase connection"}])
        
        try:
            if params:
                result = self.supabase.rpc(function_name, params).execute()
            else:
                result = self.supabase.rpc(function_name).execute()
            
            if result.data:
                if isinstance(result.data, list):
                    return pd.DataFrame(result.data)
                else:
                    return pd.DataFrame([{"Result": result.data}])
            else:
                return pd.DataFrame([{"Message": f"RPC function '{function_name}' executed successfully (no data returned)"}])
                
        except Exception as e:
            return pd.DataFrame([{"Error": f"RPC function failed: {str(e)}"}])

# Initialize Supabase connection
try:
    db = SupabaseConnection()
    connection_status = db.connection_status
except Exception as e:
    db = None
    connection_status = f"❌ Failed to initialize Supabase: {str(e)}"

def refresh_tables():
    """Refresh the list of available tables"""
    if db:
        tables = db.get_tables()
        return gr.Dropdown(choices=tables, value=None)
    return gr.Dropdown(choices=["No connection"], value=None)

def view_table_data(table_name: str, limit: int):
    """View data from selected table"""
    if not db:
        return pd.DataFrame([{"Error": "No Supabase connection"}])
    
    return db.get_table_data(table_name, limit)

def create_table():
    """Create sample table"""
    if not db:
        return "❌ No Supabase connection"
    return db.create_sample_table()

def add_sample_data(name: str, email: str, age: int):
    """Add data to sample table"""
    if not db:
        return "❌ No Supabase connection"
    
    if not name or not email:
        return "❌ Name and email are required"
    
    if age < 0 or age > 150:
        return "❌ Please enter a valid age (0-150)"
    
    return db.insert_sample_data(name, email, age)

def execute_rpc_function(function_name: str, params_text: str = ""):
    """Execute RPC function"""
    if not db:
        return pd.DataFrame([{"Error": "No Supabase connection"}])
    
    if not function_name.strip():
        return pd.DataFrame([{"Message": "Please enter a function name"}])
    
    try:
        # Parse parameters if provided
        params = {}
        if params_text.strip():
            # Simple parameter parsing: key=value,key2=value2
            for param in params_text.split(','):
                if '=' in param:
                    key, value = param.split('=', 1)
                    params[key.strip()] = value.strip()
        
        return db.execute_rpc_function(function_name.strip(), params if params else None)
    except Exception as e:
        return pd.DataFrame([{"Error": f"Parameter parsing error: {str(e)}"}])

# Create Gradio interface
with gr.Blocks(title="Supabase Database Interface", theme=gr.themes.Soft()) as app:
    gr.Markdown("# 🗃️ Supabase Database Interface")
    gr.Markdown("Connect and interact with your Supabase database using the API")
    
    # Connection status
    with gr.Row():
        gr.Markdown(f"**Connection Status:** {connection_status}")
    
    with gr.Tabs():
        # Tab 1: View Tables
        with gr.Tab("📊 View Tables"):
            gr.Markdown("### Browse your database tables")
            
            with gr.Row():
                table_dropdown = gr.Dropdown(
                    choices=db.get_tables() if db else ["No connection"],
                    label="Select Table",
                    value=None
                )
                refresh_btn = gr.Button("🔄 Refresh Tables", size="sm")
                
            with gr.Row():
                limit_slider = gr.Slider(
                    minimum=1, maximum=1000, value=100, step=1,
                    label="Number of rows to display"
                )
                
            view_btn = gr.Button("👀 View Table Data", variant="primary")
            table_output = gr.Dataframe(label="Table Data")
            
            refresh_btn.click(refresh_tables, outputs=table_dropdown)
            view_btn.click(
                view_table_data,
                inputs=[table_dropdown, limit_slider],
                outputs=table_output
            )
        
        # Tab 2: Add Data
        with gr.Tab("➕ Add Data"):
            gr.Markdown("### Add data to your tables")
            
            create_table_btn = gr.Button("🏗️ Create Sample Table", variant="secondary")
            create_status = gr.Textbox(label="Table Creation Status", interactive=False)
            
            gr.Markdown("---")
            gr.Markdown("**Add record to sample_data table:**")
            
            with gr.Row():
                name_input = gr.Textbox(label="Name", placeholder="Enter name")
                email_input = gr.Textbox(label="Email", placeholder="Enter email")
                age_input = gr.Number(label="Age", value=25, minimum=0, maximum=150)
            
            add_btn = gr.Button("📝 Add Record", variant="primary")
            add_status = gr.Textbox(label="Add Record Status", interactive=False)
            
            create_table_btn.click(create_table, outputs=create_status)
            add_btn.click(
                add_sample_data,
                inputs=[name_input, email_input, age_input],
                outputs=add_status
            )
        
        # Tab 3: RPC Functions
        with gr.Tab("🔧 RPC Functions"):
            gr.Markdown("### Execute Remote Procedure Call (RPC) functions")
            gr.Markdown("⚠️ **Note:** RPC functions must be created in your Supabase database first.")
            
            function_input = gr.Textbox(
                label="Function Name",
                placeholder="e.g., get_user_count, hello_world",
                lines=1
            )
            
            params_input = gr.Textbox(
                label="Parameters (optional)",
                placeholder="param1=value1,param2=value2",
                lines=2
            )
            
            rpc_btn = gr.Button("▶️ Execute Function", variant="primary")
            rpc_output = gr.Dataframe(label="Function Results")
            
            # Example functions
            gr.Markdown("""
            **Example RPC Functions:**
            - `hello_world` - Simple test function
            - `get_tables_list` - Get list of tables
            - `version` - Get database version
            
            **Note:** These functions need to be created in your Supabase SQL editor first.
            """)
            
            rpc_btn.click(
                execute_rpc_function,
                inputs=[function_input, params_input],
                outputs=rpc_output
            )
        
        # Tab 4: Connection Info
        with gr.Tab("ℹ️ Connection Info"):
            gr.Markdown("### Supabase Connection Details")
            
            if db:
                gr.Markdown(f"""
                **Supabase URL:** `{db.url}`
                **Connection Type:** API Client (using anon key)
                **Status:** {db.connection_status}
                
                **Available Operations:**
                - ✅ Read data from tables
                - ✅ Insert data (if RLS policies allow)
                - ✅ Execute RPC functions
                - ❌ Create/modify tables (requires elevated permissions)
                
                **Note:** The anon key has limited permissions. For full database operations, you may need to use the service role key or enable Row Level Security (RLS) policies.
                """)
            else:
                gr.Markdown("❌ No connection established")
    
    gr.Markdown("---")
    gr.Markdown("💡 **Tips:** This interface uses the Supabase API. Some operations may require specific permissions or RLS policies to be configured in your Supabase project.")

# Launch the app
if __name__ == "__main__":
    app.launch(
        server_name="0.0.0.0",
        server_port=7860,
        share=False
    )