"""
Automated Supabase Table Setup
Creates the admin_rules table in Supabase using the Management API.
"""

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

# Load environment variables
load_dotenv()

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_SERVICE_KEY = os.getenv("SUPABASE_SERVICE_KEY")

if not SUPABASE_URL or not SUPABASE_SERVICE_KEY:
    print("❌ Missing Supabase credentials!")
    print("   Please set SUPABASE_URL and SUPABASE_SERVICE_KEY in your .env file")
    sys.exit(1)

def create_table_via_supabase():
    """
    Create table using Supabase client and direct table operations.
    Since Supabase doesn't allow direct SQL execution via REST API,
    we'll create the table structure using the Supabase client.
    """
    try:
        from supabase import create_client
        
        print("🔗 Connecting to Supabase...")
        client = create_client(SUPABASE_URL, SUPABASE_SERVICE_KEY)
        
        # Read SQL 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("📝 SQL Script loaded from supabase_admin_rules_table.sql")
        print("\n" + "=" * 60)
        print("⚠️  IMPORTANT: Supabase Python client cannot execute raw SQL")
        print("=" * 60)
        print("\nYou need to run the SQL manually in Supabase Dashboard:")
        print("\n📋 Steps:")
        print("   1. Open: https://app.supabase.com")
        print("   2. Select your project")
        print("   3. Go to: SQL Editor (left sidebar)")
        print("   4. Click: 'New query'")
        print("   5. Copy the SQL below and paste it:")
        print("\n" + "-" * 60)
        print(sql_content)
        print("-" * 60)
        print("\n   6. Click 'Run' button (or press Ctrl+Enter)")
        print("   7. Wait for success message")
        print("\n✅ After running, the table will be created!")
        
        # Try to verify table exists (after user runs SQL)
        print("\n🔍 Checking if table exists...")
        try:
            result = client.table("admin_rules").select("id").limit(1).execute()
            print("✅ Table 'admin_rules' exists and is accessible!")
            return True
        except Exception as e:
            if "relation" in str(e).lower() or "does not exist" in str(e).lower():
                print("⚠️  Table does not exist yet.")
                print("   Please run the SQL script in Supabase SQL Editor first.")
                return False
            else:
                # Table might be empty, which is fine
                print("✅ Table exists (might be empty)")
                return True
                
    except ImportError:
        print("❌ Supabase client not installed")
        print("   Run: pip install supabase")
        return False
    except Exception as e:
        print(f"❌ Error: {e}")
        return False


def create_table_via_http():
    """
    Alternative: Try to create table via HTTP POST to Supabase REST API.
    This method uses the PostgREST API to create tables.
    Note: This typically requires admin privileges and may not work.
    """
    import httpx
    
    # This approach won't work because Supabase doesn't allow DDL via REST API
    # But we can try to use the pg_net extension if available
    print("⚠️  Direct HTTP table creation is not supported by Supabase REST API")
    print("   Supabase requires SQL execution via the SQL Editor for security reasons")
    return False


if __name__ == "__main__":
    print("=" * 60)
    print("Supabase Admin Rules Table Setup")
    print("=" * 60)
    print()
    
    # Method 1: Try via Supabase client (will show instructions)
    success = create_table_via_supabase()
    
    if not success:
        print("\n" + "=" * 60)
        print("📝 Manual Setup Required")
        print("=" * 60)
        print("\nSince Supabase doesn't allow programmatic SQL execution")
        print("for security reasons, you need to run the SQL manually.")
        print("\nThe SQL script is ready in: supabase_admin_rules_table.sql")
        print("\nAfter running the SQL in Supabase Dashboard:")
        print("  - The table will be created")
        print("  - RulesStore will automatically use Supabase")
        print("  - All rules will be saved to Supabase instead of SQLite")