"""
Database optimization script.
Adds indexes for common queries to improve performance.
"""
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent))

from shared.database import engine, Base
from sqlalchemy import text, inspect


def create_indexes():
    """Create database indexes for performance."""
    print("Creating database indexes...")
    
    with engine.connect() as conn:
        # Users table indexes
        indexes = [
            # User lookups by email (login)
            "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)",
            
            # User lookups by username
            "CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)",
            
            # Admin queries
            "CREATE INDEX IF NOT EXISTS idx_users_is_admin ON users(is_admin)",
            
            # Active users filter
            "CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active)",
            
            # Tier-based queries
            "CREATE INDEX IF NOT EXISTS idx_users_tier ON users(tier)",
            
            # Projects table indexes
            # User's projects lookup
            "CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects(user_id)",
            
            # Status filtering
            "CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status)",
            
            # Recent projects (ordered by created_at)
            "CREATE INDEX IF NOT EXISTS idx_projects_created_at ON projects(created_at DESC)",
            
            # Composite index for user's recent projects
            "CREATE INDEX IF NOT EXISTS idx_projects_user_created ON projects(user_id, created_at DESC)",
            
            # Composite index for user's projects by status
            "CREATE INDEX IF NOT EXISTS idx_projects_user_status ON projects(user_id, status)",
            
            # API Keys table indexes
            # User's API keys
            "CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id)",
            
            # API key lookup
            "CREATE INDEX IF NOT EXISTS idx_api_keys_key ON api_keys(key)",
            
            # Active keys filter
            "CREATE INDEX IF NOT EXISTS idx_api_keys_is_active ON api_keys(is_active)",
            
            # Composite for user's active keys
            "CREATE INDEX IF NOT EXISTS idx_api_keys_user_active ON api_keys(user_id, is_active)",
        ]
        
        for idx_sql in indexes:
            try:
                conn.execute(text(idx_sql))
                index_name = idx_sql.split("idx_")[1].split(" ")[0]
                print(f"✓ Created index: idx_{index_name}")
            except Exception as e:
                print(f"✗ Error creating index: {e}")
        
        conn.commit()
    
    print("\nIndexes created successfully!")


def analyze_database():
    """Analyze database for optimization opportunities."""
    print("\n" + "="*50)
    print("Database Analysis")
    print("="*50)
    
    inspector = inspect(engine)
    
    for table_name in inspector.get_table_names():
        print(f"\nTable: {table_name}")
        
        # Show indexes
        indexes = inspector.get_indexes(table_name)
        if indexes:
            print("  Indexes:")
            for idx in indexes:
                columns = ", ".join(idx['column_names'])
                unique = " (UNIQUE)" if idx.get('unique') else ""
                print(f"    - {idx['name']}: {columns}{unique}")
        else:
            print("  No indexes (except primary key)")
        
        # Show columns
        columns = inspector.get_columns(table_name)
        print(f"  Columns: {len(columns)}")


def vacuum_database():
    """Vacuum database to reclaim space and update statistics."""
    print("\nVacuuming database...")
    
    # Note: VACUUM doesn't work in transaction for SQLite
    # This is more relevant for PostgreSQL
    with engine.connect() as conn:
        try:
            conn.execute(text("VACUUM"))
            print("✓ Database vacuumed")
        except Exception as e:
            print(f"Note: Vacuum not needed or not supported: {e}")
    
    with engine.connect() as conn:
        try:
            conn.execute(text("ANALYZE"))
            print("✓ Database statistics updated")
        except Exception as e:
            print(f"Note: Analyze not needed: {e}")


if __name__ == "__main__":
    print("Database Optimization Tool")
    print("="*50)
    
    # Create indexes
    create_indexes()
    
    # Analyze current state
    analyze_database()
    
    # Vacuum and analyze
    vacuum_database()
    
    print("\n" + "="*50)
    print("Optimization complete!")
    print("="*50)