"""
Create admin_rules table in Supabase programmatically.
This script uses the Supabase Python client to set up the table.
"""

import os
import sys
from pathlib import Path
from dotenv import load_dotenv

load_dotenv()

def create_table_using_supabase_client():
    """
    Create the admin_rules table using Supabase client.
    Since Supabase doesn't allow direct SQL execution via REST API,
    we'll use a workaround or provide clear instructions.
    """
    supabase_url = os.getenv("SUPABASE_URL")
    supabase_key = os.getenv("SUPABASE_SERVICE_KEY")
    
    if not supabase_url or not supabase_key:
        print("❌ Missing Supabase credentials!")
        print("   Set SUPABASE_URL and SUPABASE_SERVICE_KEY in .env file")
        return False
    
    try:
        from supabase import create_client
        import httpx
        
        print("🔗 Connecting to Supabase...")
        client = create_client(supabase_url, supabase_key)
        
        # Read SQL from file
        sql_file = Path(__file__).parent / "supabase_admin_rules_table.sql"
        if not sql_file.exists():
            print(f"❌ SQL file not found: {sql_file}")
            return False
        
        with open(sql_file, "r", encoding="utf-8") as f:
            sql_content = f.read()
        
        print("📝 Attempting to create table via Supabase API...")
        
        # Method 1: Try using Supabase Management API (if available)
        # This requires the project to have pg_net extension enabled
        try:
            # Use the REST API to execute SQL via a custom function
            # First, check if we can use the SQL execution endpoint
            response = httpx.post(
                f"{supabase_url}/rest/v1/rpc/exec_sql",
                headers={
                    "apikey": supabase_key,
                    "Authorization": f"Bearer {supabase_key}",
                    "Content-Type": "application/json",
                    "Prefer": "return=representation"
                },
                json={"query": sql_content},
                timeout=30
            )
            
            if response.status_code in [200, 201, 204]:
                print("✅ Table created successfully via API!")
                return True
            else:
                print(f"⚠️  API method returned: {response.status_code}")
                print(f"   Response: {response.text[:200]}")
        except Exception as e:
            print(f"⚠️  API method failed: {e}")
        
        # Method 2: Try using Supabase Python client's table operations
        # This won't work for DDL, but we can verify if table exists
        print("\n🔍 Checking if table already exists...")
        try:
            result = client.table("admin_rules").select("id").limit(1).execute()
            print("✅ Table 'admin_rules' already exists!")
            return True
        except Exception as e:
            error_str = str(e).lower()
            if "relation" in error_str or "does not exist" in error_str:
                print("⚠️  Table does not exist yet.")
            else:
                print(f"⚠️  Error checking table: {e}")
        
        # Method 3: Since direct SQL execution isn't supported, show instructions
        print("\n" + "=" * 70)
        print("📋 MANUAL SETUP REQUIRED")
        print("=" * 70)
        print("\nSupabase doesn't allow programmatic SQL execution for security.")
        print("Please run the SQL manually in Supabase Dashboard:\n")
        print("1. Go to: https://app.supabase.com")
        print("2. Select your project")
        print("3. Click 'SQL Editor' (left sidebar)")
        print("4. Click 'New query'")
        print("5. Copy the SQL below and paste it:")
        print("\n" + "-" * 70)
        print(sql_content)
        print("-" * 70)
        print("\n6. Click 'Run' button (or press Ctrl+Enter)")
        print("7. Wait for success confirmation")
        print("\n✅ After running, the table will be created automatically!")
        
        return False
        
    except ImportError:
        print("❌ Supabase client not installed")
        print("   Run: pip install supabase")
        return False
    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback
        traceback.print_exc()
        return False


def create_table_via_psql():
    """
    Alternative: Use psql (PostgreSQL client) to execute SQL directly.
    This requires POSTGRESQL_URL to be set.
    """
    postgres_url = os.getenv("POSTGRESQL_URL")
    if not postgres_url:
        print("⚠️  POSTGRESQL_URL not set, skipping psql method")
        return False
    
    sql_file = Path(__file__).parent / "supabase_admin_rules_table.sql"
    if not sql_file.exists():
        return False
    
    try:
        import subprocess
        print("📝 Attempting to create table via psql...")
        
        # Execute SQL using psql
        result = subprocess.run(
            ["psql", postgres_url, "-f", str(sql_file)],
            capture_output=True,
            text=True,
            timeout=30
        )
        
        if result.returncode == 0:
            print("✅ Table created successfully via psql!")
            return True
        else:
            print(f"⚠️  psql failed: {result.stderr}")
            return False
    except FileNotFoundError:
        print("⚠️  psql not found in PATH")
        return False
    except Exception as e:
        print(f"⚠️  psql method failed: {e}")
        return False


if __name__ == "__main__":
    print("=" * 70)
    print("Supabase Admin Rules Table Creator")
    print("=" * 70)
    print()
    
    # Try Method 1: Supabase client
    success = create_table_using_supabase_client()
    
    if not success:
        # Try Method 2: psql (if available)
        print("\n" + "=" * 70)
        print("Trying alternative method: psql")
        print("=" * 70)
        success = create_table_via_psql()
    
    if success:
        print("\n" + "=" * 70)
        print("✅ SUCCESS!")
        print("=" * 70)
        print("\nThe admin_rules table has been created in Supabase.")
        print("RulesStore will now use Supabase instead of SQLite.")
    else:
        print("\n" + "=" * 70)
        print("📝 Manual Setup Required")
        print("=" * 70)
        print("\nPlease run the SQL manually in Supabase SQL Editor.")
        print("The SQL script is ready in: supabase_admin_rules_table.sql")
        print("\nAfter creating the table, RulesStore will automatically use Supabase.")