backend/tests/simple_sqlite_setup.py

"""
Simple SQLite Fallback Database Setup

This script initializes the SQLite fallback database with all required tables.
"""

import asyncio
import logging
import sys
from pathlib import Path

# Add the backend directory to Python path
backend_dir = Path(__file__).parent
sys.path.insert(0, str(backend_dir))

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from sqlalchemy import text

# Setup logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)


async def setup_sqlite_fallback():
    """Setup SQLite fallback database."""
    print("🚀 Setting up SQLite Fallback Database")
    print("=" * 50)
    
    try:
        # Import configuration
        from core.config import settings
        
        print(f"SQLite Fallback Enabled: {settings.ENABLE_SQLITE_FALLBACK}")
        print(f"SQLite Database Path: {settings.SQLITE_DATABASE_PATH}")
        
        if not settings.ENABLE_SQLITE_FALLBACK:
            print("❌ SQLite fallback is disabled in configuration")
            return False
        
        # Create SQLite engine
        db_path = Path(settings.SQLITE_DATABASE_PATH)
        db_path.parent.mkdir(parents=True, exist_ok=True)
        
        sqlite_url = f"sqlite+aiosqlite:///{settings.SQLITE_DATABASE_PATH}"
        
        engine = create_async_engine(
            sqlite_url,
            echo=True,  # Show SQL statements for debugging
            connect_args={
                "check_same_thread": False,
            }
        )
        
        print(f"✅ SQLite engine created: {sqlite_url}")
        
        # Create session factory
        SessionLocal = async_sessionmaker(
            engine,
            class_=AsyncSession,
            expire_on_commit=False
        )
        
        # Test connection
        async with SessionLocal() as session:
            await session.execute(text("SELECT 1"))
            print("✅ SQLite connection test passed")
        
        # Create tables manually
        print("\n📝 Creating tables...")
        
        async with SessionLocal() as session:
            # Users table
            await session.execute(text("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    email VARCHAR(255) UNIQUE NOT NULL,
                    password_hash VARCHAR(255) NOT NULL,
                    full_name VARCHAR(255),
                    company VARCHAR(255),
                    is_active BOOLEAN DEFAULT TRUE NOT NULL,
                    is_verified BOOLEAN DEFAULT FALSE NOT NULL,
                    is_superuser BOOLEAN DEFAULT FALSE NOT NULL,
                    verified_at TIMESTAMP,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    last_login_at TIMESTAMP
                )
            """))
            print("   ✅ Users table created")
            
            # Roles table
            await session.execute(text("""
                CREATE TABLE IF NOT EXISTS roles (
                    id INTEGER PRIMARY KEY,
                    name VARCHAR(255) UNIQUE NOT NULL,
                    slug VARCHAR(255) UNIQUE NOT NULL,
                    description TEXT,
                    level INTEGER DEFAULT 0 NOT NULL,
                    is_active BOOLEAN DEFAULT TRUE NOT NULL,
                    is_system BOOLEAN DEFAULT FALSE NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
                )
            """))
            print("   ✅ Roles table created")
            
            # Permissions table
            await session.execute(text("""
                CREATE TABLE IF NOT EXISTS permissions (
                    id INTEGER PRIMARY KEY,
                    name VARCHAR(255) UNIQUE NOT NULL,
                    slug VARCHAR(255) UNIQUE NOT NULL,
                    description TEXT,
                    resource VARCHAR(255) NOT NULL,
                    action VARCHAR(255) NOT NULL,
                    is_active BOOLEAN DEFAULT TRUE NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
                )
            """))
            print("   ✅ Permissions table created")
            
            # User roles junction table
            await session.execute(text("""
                CREATE TABLE IF NOT EXISTS user_roles (
                    user_id INTEGER NOT NULL,
                    role_id INTEGER NOT NULL,
                    PRIMARY KEY (user_id, role_id),
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
                )
            """))
            print("   ✅ User roles junction table created")
            
            # API Keys table
            await session.execute(text("""
                CREATE TABLE IF NOT EXISTS api_keys (
                    id INTEGER PRIMARY KEY,
                    key_name VARCHAR(255) NOT NULL,
                    key_prefix VARCHAR(255) NOT NULL,
                    user_id INTEGER NOT NULL,
                    is_active BOOLEAN DEFAULT TRUE NOT NULL,
                    expires_at TIMESTAMP,
                    last_used_at TIMESTAMP,
                    usage_count INTEGER DEFAULT 0 NOT NULL,
                    rate_limit_per_minute INTEGER DEFAULT 60 NOT NULL,
                    permissions TEXT,
                    scope VARCHAR(255) DEFAULT 'read' NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            """))
            print("   ✅ API Keys table created")
            
            # Evaluations table
            await session.execute(text("""
                CREATE TABLE IF NOT EXISTS evaluations (
                    id INTEGER PRIMARY KEY,
                    user_id INTEGER NOT NULL,
                    job_id VARCHAR(255) UNIQUE NOT NULL,
                    status VARCHAR(50) DEFAULT 'pending' NOT NULL,
                    model_config TEXT NOT NULL,
                    pipeline_config TEXT NOT NULL,
                    result_json TEXT,
                    error_message TEXT,
                    experiment_run_id VARCHAR(255),
                    total_attacks INTEGER DEFAULT 0 NOT NULL,
                    successful_attacks INTEGER DEFAULT 0 NOT NULL,
                    success_rate VARCHAR(50) DEFAULT '0%' NOT NULL,
                    execution_time_ms INTEGER,
                    progress TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                    started_at TIMESTAMP,
                    completed_at TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            """))
            print("   ✅ Evaluations table created")
            
            # Create indexes for better performance
            await session.execute(text("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"))
            await session.execute(text("CREATE INDEX IF NOT EXISTS idx_evaluations_user_id ON evaluations(user_id)"))
            await session.execute(text("CREATE INDEX IF NOT EXISTS idx_evaluations_job_id ON evaluations(job_id)"))
            await session.execute(text("CREATE INDEX IF NOT EXISTS idx_evaluations_status ON evaluations(status)"))
            await session.execute(text("CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id)"))
            await session.execute(text("CREATE INDEX IF NOT EXISTS idx_api_keys_key_prefix ON api_keys(key_prefix)"))
            
            print("   ✅ Indexes created")
            
            # Enable foreign keys and optimizations
            await session.execute(text("PRAGMA foreign_keys = ON"))
            await session.execute(text("PRAGMA journal_mode = WAL"))
            await session.execute(text("PRAGMA synchronous = NORMAL"))
            await session.execute(text("PRAGMA cache_size = 10000"))
            await session.execute(text("PRAGMA temp_store = memory"))
            
            await session.commit()
            print("   ✅ SQLite optimizations enabled")
        
        # Verify tables were created
        async with SessionLocal() as session:
            result = await session.execute(text("""
                SELECT name FROM sqlite_master 
                WHERE type='table' 
                AND name NOT LIKE 'sqlite_%'
                ORDER BY name
            """))
            
            tables = result.fetchall()
            table_names = [table[0] for table in tables]
            
            print(f"\n📋 Created {len(table_names)} tables:")
            for table_name in table_names:
                print(f"   - {table_name}")
        
        # Close engine
        await engine.dispose()
        
        print(f"\n🎉 SQLite fallback database setup completed successfully!")
        print(f"   Database file: {db_path.absolute()}")
        print(f"   File size: {db_path.stat().st_size} bytes" if db_path.exists() else "")
        
        return True
        
    except Exception as e:
        print(f"❌ Setup failed: {e}")
        logger.error(f"Setup failed: {e}")
        return False


if __name__ == "__main__":
    success = asyncio.run(setup_sqlite_fallback())
    exit(0 if success else 1)