backend/api/storage/create_supabase_table.py

"""
Script to create admin_rules table in Supabase programmatically.
Run this script to set up the table in your Supabase project.
"""

import os
import sys
from pathlib import Path

# Add backend to path
backend_dir = Path(__file__).resolve().parents[2]
sys.path.insert(0, str(backend_dir))

try:
    from supabase import create_client, Client
except ImportError:
    print("❌ Supabase client not installed. Run: pip install supabase")
    sys.exit(1)


def create_admin_rules_table():
    """
    Create the admin_rules table in Supabase with all necessary columns,
    indexes, RLS policies, and triggers.
    """
    supabase_url = os.getenv("SUPABASE_URL")
    supabase_key = os.getenv("SUPABASE_SERVICE_KEY")
    
    if not supabase_url or not supabase_key:
        print("❌ Supabase credentials missing!")
        print("   Set SUPABASE_URL and SUPABASE_SERVICE_KEY in your .env file")
        return False
    
    try:
        client = create_client(supabase_url, supabase_key)
        
        print("🔗 Connecting to Supabase...")
        print(f"   URL: {supabase_url}")
        
        # SQL script to create the table
        sql_script = """
-- Create admin_rules table
CREATE TABLE IF NOT EXISTS admin_rules (
    id BIGSERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    rule TEXT NOT NULL,
    pattern TEXT,
    severity TEXT DEFAULT 'medium' CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    description TEXT,
    enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, rule)
);

-- Create indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_admin_rules_tenant_id ON admin_rules(tenant_id);
CREATE INDEX IF NOT EXISTS idx_admin_rules_enabled ON admin_rules(enabled);
CREATE INDEX IF NOT EXISTS idx_admin_rules_tenant_enabled ON admin_rules(tenant_id, enabled);

-- Enable Row Level Security
ALTER TABLE admin_rules ENABLE ROW LEVEL SECURITY;

-- Drop existing policy if it exists (to avoid conflicts)
DROP POLICY IF EXISTS "Service role can manage all admin rules" ON admin_rules;

-- Create policy to allow service role to access all rows
CREATE POLICY "Service role can manage all admin rules"
    ON admin_rules
    FOR ALL
    USING (true)
    WITH CHECK (true);

-- Create function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Drop existing trigger if it exists
DROP TRIGGER IF EXISTS update_admin_rules_updated_at ON admin_rules;

-- Create trigger to automatically update updated_at
CREATE TRIGGER update_admin_rules_updated_at
    BEFORE UPDATE ON admin_rules
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
"""
        
        print("\n📝 Executing SQL to create admin_rules table...")
        
        # Execute SQL using Supabase REST API
        # Note: Supabase Python client doesn't have direct SQL execution
        # We need to use the REST API or rpc function
        
        # Alternative: Use the REST API to execute SQL
        import httpx
        import json
        
        response = httpx.post(
            f"{supabase_url}/rest/v1/rpc/exec_sql",
            headers={
                "apikey": supabase_key,
                "Authorization": f"Bearer {supabase_key}",
                "Content-Type": "application/json"
            },
            json={"query": sql_script},
            timeout=30
        )
        
        if response.status_code in [200, 201, 204]:
            print("✅ Table created successfully!")
            print("\n📊 Verifying table exists...")
            
            # Try to query the table to verify it exists
            try:
                result = client.table("admin_rules").select("id").limit(1).execute()
                print("✅ Table verified - admin_rules table exists and is accessible")
                return True
            except Exception as e:
                # Table might exist but be empty, which is fine
                if "relation" in str(e).lower() or "does not exist" in str(e).lower():
                    print("⚠️  Table might not have been created. Check Supabase dashboard.")
                    return False
                else:
                    print("✅ Table exists (empty table)")
                    return True
        else:
            # If rpc doesn't work, try direct SQL execution via PostgREST
            # Some Supabase setups allow direct SQL execution
            print("⚠️  RPC method not available, trying alternative method...")
            print("   You may need to run the SQL manually in Supabase SQL Editor")
            print(f"   See: supabase_admin_rules_table.sql")
            return False
            
    except Exception as e:
        print(f"❌ Error creating table: {e}")
        print("\n💡 Alternative: Run the SQL manually in Supabase SQL Editor")
        print("   1. Go to Supabase Dashboard → SQL Editor")
        print("   2. Copy contents of supabase_admin_rules_table.sql")
        print("   3. Paste and run in SQL Editor")
        return False


def verify_table_structure():
    """Verify the table structure by checking columns."""
    supabase_url = os.getenv("SUPABASE_URL")
    supabase_key = os.getenv("SUPABASE_SERVICE_KEY")
    
    if not supabase_url or not supabase_key:
        return False
    
    try:
        client = create_client(supabase_url, supabase_key)
        
        # Try to get table info by querying with limit 0
        result = client.table("admin_rules").select("*").limit(0).execute()
        
        print("\n📋 Table Structure Verified:")
        print("   ✅ admin_rules table exists")
        print("   ✅ Table 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")
            return False
        else:
            print(f"⚠️  Could not verify: {e}")
            return False


if __name__ == "__main__":
    print("=" * 60)
    print("Supabase Admin Rules Table Creator")
    print("=" * 60)
    print()
    
    # Load environment variables
    from dotenv import load_dotenv
    load_dotenv()
    
    # Check if table already exists
    print("🔍 Checking if table already exists...")
    if verify_table_structure():
        print("\n✅ Table already exists! No need to create it.")
        response = input("\nDo you want to recreate it? (y/N): ")
        if response.lower() != 'y':
            print("Exiting...")
            sys.exit(0)
    
    # Create the table
    success = create_admin_rules_table()
    
    if success:
        print("\n" + "=" * 60)
        print("✅ Setup Complete!")
        print("=" * 60)
        print("\nYou can now use the RulesStore with Supabase.")
        print("Rules will be automatically saved to Supabase instead of SQLite.")
    else:
        print("\n" + "=" * 60)
        print("⚠️  Automatic setup failed")
        print("=" * 60)
        print("\nPlease run the SQL manually:")
        print("1. Go to Supabase Dashboard → SQL Editor")
        print("2. Open: supabase_admin_rules_table.sql")
        print("3. Copy and paste the SQL into the editor")
        print("4. Click 'Run' to execute")