#!/usr/bin/env python3
"""
Database setup script for Booknap
Run this script to create the necessary database tables
"""

import os
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

def setup_database():
    """Set up the database schema"""
    
    # Database configuration - update with your actual password
    DATABASE_URL = os.environ.get(
        "DATABASE_URL",
        "postgresql://postgres:YOUR_ACTUAL_PASSWORD@db.ckrqyjfdifjbsuuofegd.supabase.co:5432/postgres"
    )
    
    print("Connecting to database...")
    print(f"URL: {DATABASE_URL.replace('YOUR_ACTUAL_PASSWORD', '***')}")
    
    try:
        # Create engine
        engine = create_engine(DATABASE_URL, pool_pre_ping=True)
        
        # Test connection
        with engine.connect() as conn:
            print("✅ Database connection successful!")
            
            # Create users table
            create_users_table = """
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                full_name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
            """
            
            # Create index
            create_index = """
            CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
            """
            
            # Execute schema creation
            print("Creating users table...")
            conn.execute(text(create_users_table))
            
            print("Creating email index...")
            conn.execute(text(create_index))
            
            # Commit changes
            conn.commit()
            
            print("✅ Database schema created successfully!")
            
            # Verify table exists
            result = conn.execute(text("SELECT table_name FROM information_schema.tables WHERE table_name = 'users'"))
            if result.fetchone():
                print("✅ Users table verified!")
                
                # Show table structure
                result = conn.execute(text("\\d users"))
                print("\nTable structure:")
                # Note: \d command won't work in Python, let's use a different approach
                result = conn.execute(text("SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'users' ORDER BY ordinal_position"))
                columns = result.fetchall()
                for col in columns:
                    print(f"  {col[0]}: {col[1]} ({'NULL' if col[2] == 'YES' else 'NOT NULL'})")
                
            else:
                print("❌ Users table not found!")
                
    except SQLAlchemyError as e:
        print(f"❌ Database error: {e}")
        print("\nPlease check:")
        print("1. Your database password is correct")
        print("2. The database is accessible from your network")
        print("3. Update the DATABASE_URL in this script or set the environment variable")
        
    except Exception as e:
        print(f"❌ Unexpected error: {e}")

if __name__ == "__main__":
    print("🚀 Booknap Database Setup")
    print("=" * 40)
    
    # Check if DATABASE_URL is set
    if not os.environ.get("DATABASE_URL"):
        print("⚠️  DATABASE_URL environment variable not set")
        print("Please set it with your actual database password:")
        print("export DATABASE_URL='postgresql://postgres:YOUR_PASSWORD@db.ckrqyjfdifjbsuuofegd.supabase.co:5432/postgres'")
        print()
    
    setup_database()
    
    print("\n" + "=" * 40)
    print("Setup complete!")