database/setup_database.py

"""Database setup script for Supabase.

Run this script to set up the database schema in your Supabase instance.
"""

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

# Add parent directory to path
sys.path.insert(0, str(Path(__file__).parent.parent))

load_dotenv()


def setup_database():
    """Set up database schema in Supabase."""
    supabase_url = os.getenv("SUPABASE_URL")
    supabase_key = os.getenv("SUPABASE_KEY")

    if not supabase_url or not supabase_key:
        print("❌ Error: SUPABASE_URL and SUPABASE_KEY must be set in .env file")
        return False

    try:
        print("📊 Connecting to Supabase...")
        client = create_client(supabase_url, supabase_key)

        # Read schema file
        schema_path = Path(__file__).parent / "schema.sql"
        with open(schema_path, 'r') as f:
            schema_sql = f.read()

        print("🔧 Executing schema SQL...")
        # Note: Supabase Python client doesn't support raw SQL execution
        # You need to execute this in the Supabase SQL Editor instead
        print("\n" + "="*80)
        print("⚠️  IMPORTANT: Copy the SQL below and execute it in your Supabase SQL Editor")
        print("="*80 + "\n")
        print(schema_sql)
        print("\n" + "="*80)
        print("📝 Instructions:")
        print("1. Go to your Supabase dashboard")
        print("2. Navigate to SQL Editor")
        print("3. Create a new query")
        print("4. Copy and paste the SQL above")
        print("5. Click 'Run' to execute")
        print("="*80 + "\n")

        # Verify connection
        result = client.table('users').select("count", count='exact').execute()
        print(f"✅ Database connection verified!")

        return True

    except Exception as e:
        print(f"❌ Error setting up database: {e}")
        return False


if __name__ == "__main__":
    success = setup_database()
    sys.exit(0 if success else 1)