"""
Database migration system for MbetterClient
"""

import logging
from typing import List, Dict, Any, Optional
from datetime import datetime
from sqlalchemy import text, inspect
from sqlalchemy.exc import SQLAlchemyError

from .models import DatabaseVersion

logger = logging.getLogger(__name__)


def get_current_timestamp_sql(db_manager) -> str:
    """
    Get the SQL expression for current timestamp, works with both SQLite and MySQL.
    
    Args:
        db_manager: Database manager instance
        
    Returns:
        SQL expression string for current timestamp
    """
    dialect = db_manager.engine.dialect.name
    if dialect == 'sqlite':
        return "datetime('now')"
    elif dialect in ('mysql', 'mariadb'):
        return "NOW()"
    else:
        return "CURRENT_TIMESTAMP"


def get_insert_ignore_sql(db_manager) -> str:
    """
    Get the INSERT IGNORE syntax, works with both SQLite and MySQL.
    
    Args:
        db_manager: Database manager instance
        
    Returns:
        SQL INSERT statement prefix
    """
    dialect = db_manager.engine.dialect.name
    if dialect == 'sqlite':
        return "INSERT OR IGNORE"
    elif dialect in ('mysql', 'mariadb'):
        return "INSERT IGNORE"
    else:
        return "INSERT OR IGNORE"


def get_insert_replace_sql(db_manager) -> str:
    """
    Get the INSERT OR REPLACE syntax, works with both SQLite and MySQL.
    
    Args:
        db_manager: Database manager instance
        
    Returns:
        SQL INSERT statement prefix
    """
    dialect = db_manager.engine.dialect.name
    if dialect == 'sqlite':
        return "INSERT OR REPLACE INTO"
    elif dialect in ('mysql', 'mariadb'):
        return "REPLACE INTO"
    else:
        return "INSERT OR REPLACE INTO"


def get_auto_increment_sql(db_manager) -> str:
    """
    Get the AUTO_INCREMENT syntax, works with both SQLite and MySQL.
    
    Args:
        db_manager: Database manager instance
        
    Returns:
        SQL AUTO_INCREMENT expression
    """
    dialect = db_manager.engine.dialect.name
    if dialect == 'sqlite':
        return "AUTOINCREMENT"
    elif dialect in ('mysql', 'mariadb'):
        return "AUTO_INCREMENT"
    else:
        return "AUTOINCREMENT"


def get_primary_key_sql(db_manager, column_name: str = 'id') -> str:
    """
    Get the PRIMARY KEY syntax, works with both SQLite and MySQL.
    
    Args:
        db_manager: Database manager instance
        column_name: Name of the primary key column
        
    Returns:
        SQL PRIMARY KEY expression
    """
    dialect = db_manager.engine.dialect.name
    if dialect == 'sqlite':
        return f"{column_name} INTEGER PRIMARY KEY AUTOINCREMENT"
    elif dialect in ('mysql', 'mariadb'):
        return f"{column_name} INT AUTO_INCREMENT PRIMARY KEY"
    else:
        return f"{column_name} INTEGER PRIMARY KEY AUTOINCREMENT"


def get_table_columns(db_manager, table_name: str) -> List[str]:
    """
    Get list of column names for a table, works with both SQLite and MySQL.
    
    Args:
        db_manager: Database manager instance
        table_name: Name of the table to inspect
        
    Returns:
        List of column names
    """
    try:
        with db_manager.engine.connect() as conn:
            # Check database dialect
            dialect = db_manager.engine.dialect.name
            logger.debug(f"get_table_columns: dialect='{dialect}', table='{table_name}'")
            
            if dialect == 'sqlite':
                # SQLite uses PRAGMA table_info
                logger.debug(f"get_table_columns: Using SQLite PRAGMA for table {table_name}")
                result = conn.execute(text(f"PRAGMA table_info({table_name})"))
                columns = [row[1] for row in result.fetchall()]
            elif dialect in ('mysql', 'mariadb'):
                # MySQL uses DESCRIBE or INFORMATION_SCHEMA
                logger.debug(f"get_table_columns: Using MySQL DESCRIBE for table {table_name}")
                result = conn.execute(text(f"DESCRIBE {table_name}"))
                columns = [row[0] for row in result.fetchall()]
            else:
                # Fallback to SQLAlchemy inspect
                logger.debug(f"get_table_columns: Using SQLAlchemy inspect for table {table_name} (dialect: {dialect})")
                inspector = inspect(db_manager.engine)
                columns = [col['name'] for col in inspector.get_columns(table_name)]
            
            logger.debug(f"get_table_columns: Found columns for {table_name}: {columns}")
            return columns
    except Exception as e:
        logger.error(f"Failed to get columns for table {table_name}: {e}")
        return []


class DatabaseMigration:
    """Base class for database migrations"""
    
    def __init__(self, version: str, description: str):
        self.version = version
        self.description = description
    
    def up(self, db_manager) -> bool:
        """Apply migration"""
        raise NotImplementedError("Migration must implement up() method")
    
    def down(self, db_manager) -> bool:
        """Rollback migration"""
        raise NotImplementedError("Migration must implement down() method")
    
    def __str__(self):
        return f"Migration {self.version}: {self.description}"


class Migration_001_InitialSchema(DatabaseMigration):
    """Initial database schema"""
    
    def __init__(self):
        super().__init__("001", "Initial database schema")
    
    def up(self, db_manager) -> bool:
        """Create initial schema - handled by SQLAlchemy create_all"""
        try:
            # Schema is created by SQLAlchemy Base.metadata.create_all()
            logger.info("Initial schema created by SQLAlchemy")
            return True
        except Exception as e:
            logger.error(f"Failed to create initial schema: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Drop all tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Get all table names - database-agnostic approach
                dialect = db_manager.engine.dialect.name
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name NOT LIKE 'sqlite_%'
                    """))
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT table_name FROM information_schema.tables
                        WHERE table_schema = DATABASE()
                    """))
                else:
                    # Fallback to SQLAlchemy inspect
                    inspector = inspect(db_manager.engine)
                    tables = inspector.get_table_names()
                    result = None
                
                if result:
                    tables = [row[0] for row in result.fetchall()]
                
                # Drop all tables
                for table in tables:
                    conn.execute(text(f"DROP TABLE IF EXISTS {table}"))
                
                conn.commit()
                
            logger.info("All tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop tables: {e}")
            return False


class Migration_002_AddIndexes(DatabaseMigration):
    """Add performance indexes"""
    
    def __init__(self):
        super().__init__("002", "Add performance indexes")
    
    def up(self, db_manager) -> bool:
        """Add indexes for better query performance"""
        try:
            with db_manager.engine.connect() as conn:
                # Additional indexes for better performance
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_log_entries_user_component ON log_entries(user_id, component)",
                    "CREATE INDEX IF NOT EXISTS ix_api_tokens_user_active ON api_tokens(user_id, is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_sessions_user_active ON sessions(user_id, is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_system_metrics_component_name ON system_metrics(component, metric_name)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Performance indexes added")
            return True
        except Exception as e:
            logger.error(f"Failed to add indexes: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove added indexes"""
        try:
            with db_manager.engine.connect() as conn:
                indexes = [
                    "DROP INDEX IF EXISTS ix_log_entries_user_component",
                    "DROP INDEX IF EXISTS ix_api_tokens_user_active",
                    "DROP INDEX IF EXISTS ix_sessions_user_active", 
                    "DROP INDEX IF EXISTS ix_system_metrics_component_name",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Performance indexes removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove indexes: {e}")
            return False


class Migration_003_AddTemplateVersioning(DatabaseMigration):
    """Add template versioning support"""
    
    def __init__(self):
        super().__init__("003", "Add template versioning support")
    
    def up(self, db_manager) -> bool:
        """Add versioning columns to templates"""
        try:
            logger.info(f"Migration_003: Starting - adding template versioning")
            logger.info(f"Migration_003: Database dialect = {db_manager.engine.dialect.name}")
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'templates')
            logger.info(f"Migration_003: Got columns for templates table: {columns}")
            with db_manager.engine.connect() as conn:
                
                if 'parent_template_id' not in columns:
                    conn.execute(text("""
                        ALTER TABLE templates 
                        ADD COLUMN parent_template_id INTEGER 
                        REFERENCES templates(id)
                    """))
                
                if 'version_major' not in columns:
                    conn.execute(text("ALTER TABLE templates ADD COLUMN version_major INTEGER DEFAULT 1"))
                
                if 'version_minor' not in columns:
                    conn.execute(text("ALTER TABLE templates ADD COLUMN version_minor INTEGER DEFAULT 0"))
                
                if 'version_patch' not in columns:
                    conn.execute(text("ALTER TABLE templates ADD COLUMN version_patch INTEGER DEFAULT 0"))
                
                conn.commit()
                
            logger.info("Template versioning columns added")
            return True
        except Exception as e:
            logger.error(f"Failed to add template versioning: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove versioning columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - versioning columns will remain")
        return True


class Migration_004_AddUserPreferences(DatabaseMigration):
    """Add user preferences storage"""
    
    def __init__(self):
        super().__init__("004", "Add user preferences storage")
    
    def up(self, db_manager) -> bool:
        """Create user preferences table"""
        try:
            dialect = db_manager.engine.dialect.name
            with db_manager.engine.connect() as conn:
                # Build table creation SQL based on database dialect
                pk_sql = get_primary_key_sql(db_manager)
                
                if dialect == 'sqlite':
                    # SQLite supports inline REFERENCES
                    create_sql = f"""
                        CREATE TABLE IF NOT EXISTS user_preferences (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            preference_key VARCHAR(100) NOT NULL,
                            preference_value TEXT NOT NULL,
                            value_type VARCHAR(20) DEFAULT 'string',
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            UNIQUE(user_id, preference_key)
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    # MySQL requires separate foreign key constraint
                    create_sql = f"""
                        CREATE TABLE IF NOT EXISTS user_preferences (
                            {pk_sql},
                            user_id INT NOT NULL,
                            preference_key VARCHAR(100) NOT NULL,
                            preference_value TEXT NOT NULL,
                            value_type VARCHAR(20) DEFAULT 'string',
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            UNIQUE(user_id, preference_key),
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                        )
                    """
                else:
                    # Fallback to SQLite syntax
                    create_sql = f"""
                        CREATE TABLE IF NOT EXISTS user_preferences (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            preference_key VARCHAR(100) NOT NULL,
                            preference_value TEXT NOT NULL,
                            value_type VARCHAR(20) DEFAULT 'string',
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            UNIQUE(user_id, preference_key)
                        )
                    """
                
                conn.execute(text(create_sql))
                
                # Add indexes
                conn.execute(text("""
                    CREATE INDEX IF NOT EXISTS ix_user_preferences_user_key
                    ON user_preferences(user_id, preference_key)
                """))
                
                conn.commit()
                
            logger.info("User preferences table created")
            return True
        except Exception as e:
            logger.error(f"Failed to create user preferences table: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Drop user preferences table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS user_preferences"))
                conn.commit()
                
            logger.info("User preferences table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop user preferences table: {e}")
            return False


class Migration_005_CreateDefaultAdminUser(DatabaseMigration):
    """Create default admin user for initial login"""
    
    def __init__(self):
        super().__init__("005", "Create default admin user")
    
    def up(self, db_manager) -> bool:
        """Create default admin user"""
        try:
            # Check if any users exist and create admin if needed
            with db_manager.engine.connect() as conn:
                result = conn.execute(text("SELECT COUNT(*) FROM users"))
                user_count = result.scalar()
                
                if user_count == 0:
                    # No users exist, create default admin
                    import hashlib
                    import secrets
                    
                    username = "admin"
                    email = "admin@mbetterclient.local"
                    password = "admin123"  # User should change this immediately
                    
                    # Use AuthManager's password hashing method (SHA-256 + salt)
                    # This matches what authenticate_user expects
                    salt = secrets.token_hex(16)
                    password_hash = hashlib.sha256((password + salt).encode()).hexdigest()
                    stored_hash = f"{salt}:{password_hash}"
                    
                    # Insert admin user using raw SQL (consistent with other migrations)
                    # Include all NOT NULL columns with proper defaults, including role and email_verified
                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        INSERT INTO users
                        (username, email, password_hash, is_active, is_admin, login_attempts, role, email_verified, created_at, updated_at)
                        VALUES (:username, :email, :password_hash, 1, 1, 0, :role, 1, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'username': username,
                        'email': email,
                        'password_hash': stored_hash,
                        'role': 'admin'
                    })
                    
                    conn.commit()
                    
                    logger.info(f"Default admin user created - Username: {username}, Password: {password}")
                    logger.warning("SECURITY: Please change the default admin password immediately!")
                else:
                    logger.info(f"Users already exist ({user_count}), skipping default admin creation")
                
            return True
        except Exception as e:
            logger.error(f"Failed to create default admin user: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove default admin user if it still exists with default credentials"""
        try:
            with db_manager.engine.connect() as conn:
                # Only remove if username is 'admin' and email is the default
                conn.execute(text("""
                    DELETE FROM users
                    WHERE username = 'admin'
                    AND email = 'admin@mbetterclient.local'
                """))
                
                conn.commit()
                
            logger.info("Default admin user removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default admin user: {e}")
            return False


class Migration_006_AddUserRoles(DatabaseMigration):
    """Add role-based access control to users"""
    
    def __init__(self):
        super().__init__("006", "Add role-based access control (admin, normal, cashier)")
    
    def up(self, db_manager) -> bool:
        """Add role column to users table"""
        try:
            # Check if role column already exists
            columns = get_table_columns(db_manager, 'users')
            with db_manager.engine.connect() as conn:
                
                if 'role' not in columns:
                    # Add role column with default value 'normal'
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN role VARCHAR(20) DEFAULT 'normal' NOT NULL
                    """))
                    
                    # Update existing users: set role based on is_admin field
                    conn.execute(text("""
                        UPDATE users
                        SET role = CASE
                            WHEN is_admin = 1 THEN 'admin'
                            ELSE 'normal'
                        END
                    """))
                    
                    # Add index for role column
                    conn.execute(text("""
                        CREATE INDEX IF NOT EXISTS ix_users_role ON users(role)
                    """))
                    
                    conn.commit()
                    
                    logger.info("Role column added to users table")
                else:
                    logger.info("Role column already exists in users table")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add user roles: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove role column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - role column will remain")
        return True


class Migration_007_CreateDefaultCashierUser(DatabaseMigration):
    """Create default cashier user for immediate testing"""
    
    def __init__(self):
        super().__init__("007", "Create default cashier user")
    
    def up(self, db_manager) -> bool:
        """Create default cashier user"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if cashier user already exists (by username or email)
                result = conn.execute(text("SELECT COUNT(*) FROM users WHERE username = 'cashier' OR email = 'cashier@mbetterclient.local'"))
                cashier_count = result.scalar()

                if cashier_count == 0:
                    # No cashier user exists, create default cashier
                    import hashlib
                    import secrets
                    
                    username = "cashier"
                    email = "cashier@mbetterclient.local"
                    password = "cashier123"  # Correct default password
                    
                    # Use AuthManager's password hashing method (SHA-256 + salt)
                    # This matches what authenticate_user expects
                    salt = secrets.token_hex(16)
                    password_hash = hashlib.sha256((password + salt).encode()).hexdigest()
                    stored_hash = f"{salt}:{password_hash}"
                    
                    # Insert cashier user using raw SQL (consistent with other migrations)
                    # Include all NOT NULL columns with proper defaults, including email_verified
                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        INSERT INTO users
                        (username, email, password_hash, is_active, is_admin, login_attempts, role, email_verified, created_at, updated_at)
                        VALUES (:username, :email, :password_hash, 1, 0, 0, :role, 1, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'username': username,
                        'email': email,
                        'password_hash': stored_hash,
                        'role': 'cashier'
                    })
                    
                    conn.commit()
                    
                    logger.info(f"Default cashier user created - Username: {username}, Password: {password}")
                else:
                    logger.info(f"Cashier user already exists, skipping default cashier creation")
                
            return True
        except Exception as e:
            logger.error(f"Failed to create default cashier user: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove default cashier user if it still exists with default credentials"""
        try:
            with db_manager.engine.connect() as conn:
                # Only remove if username is 'cashier' and email is the default
                conn.execute(text("""
                    DELETE FROM users
                    WHERE username = 'cashier'
                    AND email = 'cashier@mbetterclient.local'
                """))
                
                conn.commit()
                
            logger.info("Default cashier user removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default cashier user: {e}")
            return False


class Migration_008_AddMatchTables(DatabaseMigration):
    """Add matches and match_outcomes tables for fixture data"""
    
    def __init__(self):
        super().__init__("008", "Add matches and match_outcomes tables")
    
    def up(self, db_manager) -> bool:
        """Create matches and match_outcomes tables"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Create matches table
                if dialect == 'sqlite':
                    matches_sql = f"""
                        CREATE TABLE IF NOT EXISTS matches (
                            {pk_sql},
                            match_number INTEGER NOT NULL UNIQUE,
                            fighter1_township VARCHAR(255) NOT NULL,
                            fighter2_township VARCHAR(255) NOT NULL,
                            venue_kampala_township VARCHAR(255) NOT NULL,
                            
                            start_time DATETIME NULL,
                            end_time DATETIME NULL,
                            result VARCHAR(255) NULL,
                            filename VARCHAR(1024) NOT NULL,
                            file_sha1sum VARCHAR(255) NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL UNIQUE,
                            active_status BOOLEAN DEFAULT FALSE,
                            
                            zip_filename VARCHAR(1024) NULL,
                            zip_sha1sum VARCHAR(255) NULL,
                            zip_upload_status VARCHAR(20) DEFAULT 'pending',
                            zip_upload_progress REAL DEFAULT 0.0,
                            
                            created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    matches_sql = f"""
                        CREATE TABLE IF NOT EXISTS matches (
                            {pk_sql},
                            match_number INT NOT NULL UNIQUE,
                            fighter1_township VARCHAR(255) NOT NULL,
                            fighter2_township VARCHAR(255) NOT NULL,
                            venue_kampala_township VARCHAR(255) NOT NULL,
                            
                            start_time DATETIME NULL,
                            end_time DATETIME NULL,
                            result VARCHAR(255) NULL,
                            filename VARCHAR(1024) NOT NULL,
                            file_sha1sum VARCHAR(255) NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL UNIQUE,
                            active_status BOOLEAN DEFAULT FALSE,
                            
                            zip_filename VARCHAR(1024) NULL,
                            zip_sha1sum VARCHAR(255) NULL,
                            zip_upload_status VARCHAR(20) DEFAULT 'pending',
                            zip_upload_progress REAL DEFAULT 0.0,
                            
                            created_by INT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
                        )
                    """
                else:
                    matches_sql = f"""
                        CREATE TABLE IF NOT EXISTS matches (
                            {pk_sql},
                            match_number INTEGER NOT NULL UNIQUE,
                            fighter1_township VARCHAR(255) NOT NULL,
                            fighter2_township VARCHAR(255) NOT NULL,
                            venue_kampala_township VARCHAR(255) NOT NULL,
                            
                            start_time DATETIME NULL,
                            end_time DATETIME NULL,
                            result VARCHAR(255) NULL,
                            filename VARCHAR(1024) NOT NULL,
                            file_sha1sum VARCHAR(255) NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL UNIQUE,
                            active_status BOOLEAN DEFAULT FALSE,
                            
                            zip_filename VARCHAR(1024) NULL,
                            zip_sha1sum VARCHAR(255) NULL,
                            zip_upload_status VARCHAR(20) DEFAULT 'pending',
                            zip_upload_progress REAL DEFAULT 0.0,
                            
                            created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                        )
                    """
                
                conn.execute(text(matches_sql))
                
                # Create match_outcomes table
                if dialect == 'sqlite':
                    outcomes_sql = f"""
                        CREATE TABLE IF NOT EXISTS match_outcomes (
                            {pk_sql},
                            match_id INTEGER NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
                            column_name VARCHAR(255) NOT NULL,
                            float_value REAL NOT NULL,
                            
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            
                            UNIQUE(match_id, column_name)
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    outcomes_sql = f"""
                        CREATE TABLE IF NOT EXISTS match_outcomes (
                            {pk_sql},
                            match_id INT NOT NULL,
                            column_name VARCHAR(255) NOT NULL,
                            float_value REAL NOT NULL,
                            
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            
                            UNIQUE(match_id, column_name),
                            FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
                        )
                    """
                else:
                    outcomes_sql = f"""
                        CREATE TABLE IF NOT EXISTS match_outcomes (
                            {pk_sql},
                            match_id INTEGER NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
                            column_name VARCHAR(255) NOT NULL,
                            float_value REAL NOT NULL,
                            
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            
                            UNIQUE(match_id, column_name)
                        )
                    """
                
                conn.execute(text(outcomes_sql))
                
                # Create indexes for matches table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_matches_match_number ON matches(match_number)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_id ON matches(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_active_status ON matches(active_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_file_sha1sum ON matches(file_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_sha1sum ON matches(zip_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_upload_status ON matches(zip_upload_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_created_by ON matches(created_by)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_composite ON matches(active_status, zip_upload_status, created_at)",
                ]
                
                # Create indexes for match_outcomes table
                indexes.extend([
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_match_id ON match_outcomes(match_id)",
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_column_name ON match_outcomes(column_name)",
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_float_value ON match_outcomes(float_value)",
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_composite ON match_outcomes(match_id, column_name)",
                ])
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Matches and match_outcomes tables created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create match tables: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Drop matches and match_outcomes tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Drop tables in reverse order (child first due to foreign keys)
                conn.execute(text("DROP TABLE IF EXISTS match_outcomes"))
                conn.execute(text("DROP TABLE IF EXISTS matches"))
                
                conn.commit()
                
            logger.info("Matches and match_outcomes tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop match tables: {e}")
            return False


class Migration_009_AddDoneFieldToMatches(DatabaseMigration):
    """Add done flag field to matches table"""
    
    def __init__(self):
        super().__init__("009", "Add done flag field to matches table")
    
    def up(self, db_manager) -> bool:
        """Add done column to matches table"""
        try:
            # Check if done column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:
                
                if 'done' not in columns:
                    # Add done column with default value False
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN done BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    
                    # Add index for done column
                    conn.execute(text("""
                        CREATE INDEX IF NOT EXISTS ix_matches_done ON matches(done)
                    """))
                    
                    conn.commit()
                    
                    logger.info("Done column added to matches table")
                else:
                    logger.info("Done column already exists in matches table")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add done field to matches: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove done column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - done column will remain")
        return True

class Migration_010_AddRunningFieldToMatches(DatabaseMigration):
    """Add running flag field to matches table"""
    
    def __init__(self):
        super().__init__("010", "Add running flag field to matches table")
    
    def up(self, db_manager) -> bool:
        """Add running column to matches table"""
        try:
            # Check if running column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:
                
                if 'running' not in columns:
                    # Add running column with default value False
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN running BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    
                    # Add index for running column
                    conn.execute(text("""
                        CREATE INDEX IF NOT EXISTS ix_matches_running ON matches(running)
                    """))
                    
                    conn.commit()
                    
                    logger.info("Running column added to matches table")
                else:
                    logger.info("Running column already exists in matches table")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add running field to matches: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove running column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - running column will remain")
        return True


class Migration_011_AddFixtureActiveTimeToMatches(DatabaseMigration):
    """Add fixture_active_time field to matches table for server activation timestamp tracking"""
    
    def __init__(self):
        super().__init__("011", "Add fixture_active_time field to matches table")
    
    def up(self, db_manager) -> bool:
        """Add fixture_active_time field to matches table"""
        try:
            # Check if fixture_active_time column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:
                
                if 'fixture_active_time' not in columns:
                    # Add fixture_active_time column (INTEGER for Unix timestamp)
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN fixture_active_time INTEGER NULL
                    """))
                    
                    # Add index for fixture_active_time column
                    conn.execute(text("""
                        CREATE INDEX IF NOT EXISTS ix_matches_fixture_active_time
                        ON matches(fixture_active_time)
                    """))
                    
                    conn.commit()
                    
                    logger.info("Fixture_active_time column added to matches table")
                else:
                    logger.info("Fixture_active_time column already exists in matches table")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add fixture_active_time field to matches: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove fixture_active_time column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - fixture_active_time column will remain")
        return True


class Migration_012_RemoveFixtureIdUniqueConstraint(DatabaseMigration):
    """Remove UNIQUE constraint from matches.fixture_id to allow multiple matches per fixture"""
    
    def __init__(self):
        super().__init__("012", "Remove UNIQUE constraint from matches.fixture_id")
    
    def up(self, db_manager) -> bool:
        """Remove UNIQUE constraint from fixture_id column"""
        try:
            dialect = db_manager.engine.dialect.name
            with db_manager.engine.connect() as conn:
                # Get current column info
                column_names = get_table_columns(db_manager, 'matches')
                logger.info(f"Current matches table columns: {column_names}")

                # SQLite doesn't support ALTER TABLE DROP CONSTRAINT directly
                # We need to recreate the table without the UNIQUE constraint
                
                # For MySQL, disable foreign key checks temporarily
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))
                
                # Create new table with explicit schema (matching current state at migration 012)
                # This includes columns from migrations 008, 009, 010, 011
                pk_sql = get_primary_key_sql(db_manager)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS matches_new (
                        {pk_sql},
                        match_number INTEGER NOT NULL UNIQUE,
                        fighter1_township VARCHAR(255) NOT NULL,
                        fighter2_township VARCHAR(255) NOT NULL,
                        venue_kampala_township VARCHAR(255) NOT NULL,
                        start_time DATETIME NULL,
                        end_time DATETIME NULL,
                        result VARCHAR(255) NULL,
                        filename VARCHAR(1024) NOT NULL,
                        file_sha1sum VARCHAR(255) NOT NULL,
                        fixture_id VARCHAR(255) NOT NULL,
                        active_status BOOLEAN DEFAULT FALSE,
                        zip_filename VARCHAR(1024) NULL,
                        zip_sha1sum VARCHAR(255) NULL,
                        zip_upload_status VARCHAR(20) DEFAULT 'pending',
                        zip_upload_progress REAL DEFAULT 0.0,
                        created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE NOT NULL,
                        fixture_active_time INTEGER NULL
                    )
                """))
                
                # Get the actual columns that exist in both tables
                new_columns = get_table_columns(db_manager, 'matches_new')
                
                # Find common columns
                common_columns = [col for col in column_names if col in new_columns]
                columns_str = ", ".join(common_columns)
                
                logger.info(f"Copying data with common columns: {common_columns}")
                
                # Copy data using only common columns
                conn.execute(text(f"""
                    INSERT INTO matches_new ({columns_str})
                    SELECT {columns_str} FROM matches
                """))
                
                # Drop old table
                conn.execute(text("DROP TABLE matches"))
                
                # Rename new table to original name
                conn.execute(text("ALTER TABLE matches_new RENAME TO matches"))
                
                # Re-enable foreign key checks for MySQL
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))
                
                # Recreate indexes (without fixture_id unique constraint)
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_matches_match_number ON matches(match_number)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_id ON matches(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_active_status ON matches(active_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_file_sha1sum ON matches(file_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_sha1sum ON matches(zip_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_upload_status ON matches(zip_upload_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_created_by ON matches(created_by)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_active_time ON matches(fixture_active_time)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_done ON matches(done)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_running ON matches(running)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_composite ON matches(active_status, zip_upload_status, created_at)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("UNIQUE constraint removed from matches.fixture_id column")
            return True
        except Exception as e:
            logger.error(f"Failed to remove UNIQUE constraint from fixture_id: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Add UNIQUE constraint back to fixture_id column"""
        try:
            dialect = db_manager.engine.dialect.name
            with db_manager.engine.connect() as conn:
                # Check if there are any duplicate fixture_ids that would prevent adding UNIQUE constraint
                result = conn.execute(text("""
                    SELECT fixture_id, COUNT(*) as count
                    FROM matches
                    GROUP BY fixture_id
                    HAVING COUNT(*) > 1
                """))

                duplicates = result.fetchall()
                if duplicates:
                    logger.error(f"Cannot add UNIQUE constraint - duplicate fixture_ids found: {[row[0] for row in duplicates]}")
                    return False

                # For MySQL, disable foreign key checks temporarily
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))

                # Recreate table with UNIQUE constraint on fixture_id
                pk_sql = get_primary_key_sql(db_manager)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS matches_new (
                        {pk_sql},
                        match_number INTEGER NOT NULL UNIQUE,
                        fighter1_township VARCHAR(255) NOT NULL,
                        fighter2_township VARCHAR(255) NOT NULL,
                        venue_kampala_township VARCHAR(255) NOT NULL,

                        start_time DATETIME NULL,
                        end_time DATETIME NULL,
                        result VARCHAR(255) NULL,
                        winning_outcomes TEXT NULL,
                        under_over_result VARCHAR(50) NULL,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE NOT NULL,
                        status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                        fixture_active_time INTEGER NULL,

                        filename VARCHAR(1024) NOT NULL,
                        file_sha1sum VARCHAR(255) NOT NULL,
                        fixture_id VARCHAR(255) NOT NULL UNIQUE,
                        active_status BOOLEAN DEFAULT FALSE,

                        zip_filename VARCHAR(1024) NULL,
                        zip_sha1sum VARCHAR(255) NULL,
                        zip_upload_status VARCHAR(20) DEFAULT 'pending',
                        zip_upload_progress REAL DEFAULT 0.0,
                        zip_validation_status VARCHAR(20) DEFAULT 'pending',

                        created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """))
                
                # Copy data from old table to new table
                conn.execute(text("""
                    INSERT INTO matches_new
                    SELECT * FROM matches
                """))
                
                # Drop old table and rename new table
                conn.execute(text("DROP TABLE matches"))
                conn.execute(text("ALTER TABLE matches_new RENAME TO matches"))
                
                # Re-enable foreign key checks for MySQL
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))
                
                # Recreate indexes
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_matches_match_number ON matches(match_number)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_id ON matches(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_active_status ON matches(active_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_file_sha1sum ON matches(file_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_sha1sum ON matches(zip_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_upload_status ON matches(zip_upload_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_created_by ON matches(created_by)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_active_time ON matches(fixture_active_time)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_done ON matches(done)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_running ON matches(running)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_composite ON matches(active_status, zip_upload_status, created_at)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("UNIQUE constraint added back to matches.fixture_id column")
            return True
        except Exception as e:
            logger.error(f"Failed to add UNIQUE constraint back to fixture_id: {e}")
            return False


class Migration_013_AddStatusFieldToMatches(DatabaseMigration):
    """Add status field to matches table for match status tracking"""

    def __init__(self):
        super().__init__("013", "Add status field to matches table")

    def up(self, db_manager) -> bool:
        """Add status column to matches table"""
        try:
            # Check if status column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:

                if 'status' not in columns:
                    # Add status column with default value 'pending'
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN status VARCHAR(20) DEFAULT 'pending' NOT NULL
                    """))

                    # Add index for status column
                    conn.execute(text("""
                        CREATE INDEX IF NOT EXISTS ix_matches_status ON matches(status)
                    """))

                    conn.commit()

                    logger.info("Status column added to matches table")
                else:
                    logger.info("Status column already exists in matches table")

            return True
        except Exception as e:
            logger.error(f"Failed to add status field to matches: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove status column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - status column will remain")
        return True


class Migration_014_AddExtractionAndGameConfigTables(DatabaseMigration):
    """Add extraction_associations and game_config tables for extraction management"""

    def __init__(self):
        super().__init__("014", "Add extraction_associations and game_config tables")

    def up(self, db_manager) -> bool:
        """Create extraction_associations and game_config tables"""
        try:
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Create extraction_associations table
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS extraction_associations (
                        {pk_sql},
                        outcome_name VARCHAR(255) NOT NULL,
                        extraction_result VARCHAR(50) NOT NULL,
                        is_default BOOLEAN DEFAULT FALSE NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        UNIQUE(outcome_name, extraction_result)
                    )
                """))

                # Create game_config table
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS game_config (
                        {pk_sql},
                        config_key VARCHAR(100) NOT NULL UNIQUE,
                        config_value TEXT NOT NULL,
                        value_type VARCHAR(20) DEFAULT 'string',
                        description VARCHAR(500),
                        is_system BOOLEAN DEFAULT FALSE NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """))

                # Create indexes for extraction_associations table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_outcome_name ON extraction_associations(outcome_name)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_extraction_result ON extraction_associations(extraction_result)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_composite ON extraction_associations(outcome_name, extraction_result)",
                ]

                # Create indexes for game_config table
                indexes.extend([
                    "CREATE INDEX IF NOT EXISTS ix_game_config_key ON game_config(config_key)",
                ])

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                # Insert default extraction associations
                default_associations = [
                    # DKO associations
                    ('DRAW', 'DKO', True),
                    ('X1', 'DKO', True),
                    ('X2', 'DKO', True),
                    ('DKO', 'DKO', True),
                    # DRAW associations
                    ('DRAW', 'DRAW', True),
                    ('X1', 'DRAW', True),
                    ('X2', 'DRAW', True),
                    # KO1 associations
                    ('KO1', 'KO1', True),
                    ('WIN1', 'KO1', True),
                    ('X1', 'KO1', True),
                    ('12', 'KO1', True),
                    # KO2 associations
                    ('KO2', 'KO2', True),
                    ('WIN2', 'KO2', True),
                    ('X2', 'KO2', True),
                    ('12', 'KO2', True),
                    # PTS1 associations
                    ('X1', 'PTS1', True),
                    ('12', 'PTS1', True),
                    ('PTS1', 'PTS1', True),
                    ('WIN1', 'PTS1', True),
                    # PTS2 associations
                    ('X2', 'PTS2', True),
                    ('12', 'PTS2', True),
                    ('PTS2', 'PTS2', True),
                    ('WIN2', 'PTS2', True),
                    # RET1 associations
                    ('WIN2', 'RET1', True),
                    ('X2', 'RET1', True),
                    ('12', 'RET1', True),
                    ('RET1', 'RET1', True),
                    # RET2 associations
                    ('WIN1', 'RET2', True),
                    ('X1', 'RET2', True),
                    ('12', 'RET2', True),
                    ('RET2', 'RET2', True),
                ]

                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                for outcome_name, extraction_result, is_default in default_associations:
                    conn.execute(text(f"""
                        {insert_ignore} INTO extraction_associations
                        (outcome_name, extraction_result, is_default, created_at, updated_at)
                        VALUES (:outcome_name, :extraction_result, :is_default, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'outcome_name': outcome_name,
                        'extraction_result': extraction_result,
                        'is_default': is_default
                    })

                # Insert default game config
                conn.execute(text(f"""
                    {insert_ignore} INTO game_config
                    (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                    VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                """), {
                    'config_key': 'under_over_time_limit',
                    'config_value': '90',
                    'value_type': 'int',
                    'description': 'Time limit in seconds between UNDER and OVER outcomes',
                    'is_system': False
                })

                conn.commit()

            logger.info("Extraction associations and game config tables created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create extraction and game config tables: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop extraction_associations and game_config tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Drop tables in reverse order (if there were foreign keys)
                conn.execute(text("DROP TABLE IF EXISTS game_config"))
                conn.execute(text("DROP TABLE IF EXISTS extraction_associations"))

                conn.commit()

            logger.info("Extraction associations and game config tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop extraction and game config tables: {e}")
            return False


class Migration_015_AddBettingModeTable(DatabaseMigration):
    """Add betting_modes table for user betting preferences"""

    def __init__(self):
        super().__init__("015", "Add betting_modes table for user betting preferences")

    def up(self, db_manager) -> bool:
        """Create betting_modes table"""
        try:
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Create betting_modes table
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS betting_modes (
                        {pk_sql},
                        user_id INTEGER NOT NULL UNIQUE,
                        mode VARCHAR(50) NOT NULL DEFAULT 'all_bets_on_start',
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
                    )
                """))

                # Create indexes for betting_modes table
                conn.execute(text("""
                    CREATE INDEX IF NOT EXISTS ix_betting_modes_user_id ON betting_modes(user_id)
                """))

                conn.commit()

            logger.info("Betting modes table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create betting modes table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop betting_modes table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS betting_modes"))
                conn.commit()

            logger.info("Betting modes table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop betting modes table: {e}")
            return False


class Migration_016_ConvertBettingModeToGlobal(DatabaseMigration):
    """Convert betting mode from user-specific to global system setting"""

    def __init__(self):
        super().__init__("016", "Convert betting mode from user-specific to global system setting")

    def up(self, db_manager) -> bool:
        """Migrate user betting modes to global game config and remove betting_modes table"""
        try:
            with db_manager.engine.connect() as conn:
                # Step 1: Check if betting_modes table exists
                dialect = db_manager.engine.dialect.name
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='betting_modes'
                    """))
                    betting_modes_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'betting_modes'
                    """))
                    betting_modes_exists = result.scalar() > 0
                else:
                    # Fallback to SQLAlchemy inspect
                    inspector = inspect(db_manager.engine)
                    betting_modes_exists = 'betting_modes' in inspector.get_table_names()

                if betting_modes_exists:
                    # Step 2: Get existing betting mode configurations
                    result = conn.execute(text("""
                        SELECT bm.mode, u.role, COUNT(*) as count
                        FROM betting_modes bm
                        JOIN users u ON bm.user_id = u.id
                        GROUP BY bm.mode, u.role
                        ORDER BY
                            CASE WHEN u.role = 'admin' THEN 1 ELSE 2 END,
                            count DESC
                    """))
                    existing_modes = result.fetchall()
                    
                    # Determine global setting: prefer admin user's choice, then most common
                    global_mode = 'all_bets_on_start'  # Default
                    if existing_modes:
                        global_mode = existing_modes[0][0]  # First result (admin preference or most common)
                        logger.info(f"Found existing betting modes, setting global mode to: {global_mode}")
                    else:
                        logger.info("No existing betting mode configurations found, using default: all_bets_on_start")

                    # Step 3: Add global betting mode to game_config
                    insert_replace = get_insert_replace_sql(db_manager)
                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        {insert_replace} game_config
                        (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                        VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'config_key': 'betting_mode',
                        'config_value': global_mode,
                        'value_type': 'string',
                        'description': 'Global betting mode: all_bets_on_start or one_bet_at_a_time',
                        'is_system': True
                    })

                    # Step 4: Drop the betting_modes table
                    conn.execute(text("DROP TABLE betting_modes"))
                    logger.info("Removed user-specific betting_modes table")
                else:
                    # Table doesn't exist, just add the global config
                    insert_replace = get_insert_replace_sql(db_manager)
                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        {insert_replace} game_config
                        (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                        VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'config_key': 'betting_mode',
                        'config_value': 'all_bets_on_start',
                        'value_type': 'string',
                        'description': 'Global betting mode: all_bets_on_start or one_bet_at_a_time',
                        'is_system': True
                    })
                    logger.info("Added default global betting mode configuration")

                conn.commit()

            logger.info("Successfully converted betting mode to global system setting")
            return True
        except Exception as e:
            logger.error(f"Failed to convert betting mode to global: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Restore user-specific betting mode table"""
        try:
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Step 1: Get current global betting mode
                result = conn.execute(text("""
                    SELECT config_value FROM game_config WHERE config_key = 'betting_mode'
                """))
                global_mode_row = result.fetchone()
                global_mode = global_mode_row[0] if global_mode_row else 'all_bets_on_start'

                # Step 2: Recreate betting_modes table
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS betting_modes (
                        {pk_sql},
                        user_id INTEGER NOT NULL UNIQUE,
                        mode VARCHAR(50) NOT NULL DEFAULT 'all_bets_on_start',
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
                    )
                """))

                # Step 3: Create index
                conn.execute(text("""
                    CREATE INDEX IF NOT EXISTS ix_betting_modes_user_id ON betting_modes(user_id)
                """))

                # Step 4: Set all existing users to use the global mode
                timestamp_sql = get_current_timestamp_sql(db_manager)
                conn.execute(text(f"""
                    INSERT INTO betting_modes (user_id, mode, created_at, updated_at)
                    SELECT id, :mode, {timestamp_sql}, {timestamp_sql}
                    FROM users
                """), {'mode': global_mode})

                # Step 5: Remove global betting mode config
                conn.execute(text("DELETE FROM game_config WHERE config_key = 'betting_mode'"))

                conn.commit()

            logger.info("Restored user-specific betting mode table")
            return True
        except Exception as e:
            logger.error(f"Failed to restore user-specific betting mode: {e}")
            return False


class Migration_017_AddBettingTables(DatabaseMigration):
    """Add bets and bets_details tables for betting system"""

    def __init__(self):
        super().__init__("017", "Add bets and bets_details tables for betting system")

    def up(self, db_manager) -> bool:
        """Create bets and bets_details tables"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Create bets table
                if dialect == 'sqlite':
                    bets_sql = f"""
                        CREATE TABLE IF NOT EXISTS bets (
                            {pk_sql},
                            uuid VARCHAR(255) NOT NULL UNIQUE,
                            bet_datetime DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    bets_sql = f"""
                        CREATE TABLE IF NOT EXISTS bets (
                            {pk_sql},
                            uuid VARCHAR(255) NOT NULL UNIQUE,
                            bet_datetime DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                else:
                    bets_sql = f"""
                        CREATE TABLE IF NOT EXISTS bets (
                            {pk_sql},
                            uuid VARCHAR(255) NOT NULL UNIQUE,
                            bet_datetime DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                
                conn.execute(text(bets_sql))
                
                # Create bets_details table
                if dialect == 'sqlite':
                    details_sql = f"""
                        CREATE TABLE IF NOT EXISTS bets_details (
                            {pk_sql},
                            bet_id INTEGER NOT NULL,
                            match_id INTEGER NOT NULL,
                            outcome VARCHAR(255) NOT NULL,
                            amount REAL NOT NULL,
                            result VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            FOREIGN KEY (bet_id) REFERENCES bets (id) ON DELETE CASCADE,
                            FOREIGN KEY (match_id) REFERENCES matches (id) ON DELETE CASCADE
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    details_sql = f"""
                        CREATE TABLE IF NOT EXISTS bets_details (
                            {pk_sql},
                            bet_id INT NOT NULL,
                            match_id INT NOT NULL,
                            outcome VARCHAR(255) NOT NULL,
                            amount REAL NOT NULL,
                            result VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            FOREIGN KEY (bet_id) REFERENCES bets (id) ON DELETE CASCADE,
                            FOREIGN KEY (match_id) REFERENCES matches (id) ON DELETE CASCADE
                        )
                    """
                else:
                    details_sql = f"""
                        CREATE TABLE IF NOT EXISTS bets_details (
                            {pk_sql},
                            bet_id INTEGER NOT NULL,
                            match_id INTEGER NOT NULL,
                            outcome VARCHAR(255) NOT NULL,
                            amount REAL NOT NULL,
                            result VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            FOREIGN KEY (bet_id) REFERENCES bets (id) ON DELETE CASCADE,
                            FOREIGN KEY (match_id) REFERENCES matches (id) ON DELETE CASCADE
                        )
                    """
                
                conn.execute(text(details_sql))

                # Create indexes for bets table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_bets_uuid ON bets(uuid)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_fixture_id ON bets(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_created_at ON bets(created_at)",
                ]

                # Create indexes for bets_details table
                indexes.extend([
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_bet_id ON bets_details(bet_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_match_id ON bets_details(match_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_outcome ON bets_details(outcome)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_result ON bets_details(result)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_composite ON bets_details(bet_id, match_id)",
                ])

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Bets and bets_details tables created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create betting tables: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop bets and bets_details tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Drop tables in reverse order (child first due to foreign keys)
                conn.execute(text("DROP TABLE IF EXISTS bets_details"))
                conn.execute(text("DROP TABLE IF EXISTS bets"))

                conn.commit()

            logger.info("Bets and bets_details tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop betting tables: {e}")
            return False


class Migration_018_RemoveExtractionAssociationUniqueConstraint(DatabaseMigration):
    """Remove unique constraint from extraction_associations to allow multiple result associations per outcome"""
    
    def __init__(self):
        super().__init__("018", "Remove unique constraint from extraction_associations to allow multiple result associations per outcome")
    
    def up(self, db_manager) -> bool:
        """Remove unique constraint from extraction_associations table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # SQLite doesn't support ALTER TABLE DROP CONSTRAINT directly
                # We need to recreate the table without the unique constraint
                
                # Step 1: Create new table without UNIQUE constraint
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS extraction_associations_new (
                        {pk_sql},
                        outcome_name VARCHAR(255) NOT NULL,
                        extraction_result VARCHAR(50) NOT NULL,
                        is_default BOOLEAN DEFAULT FALSE NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """))
                
                # Step 2: Copy data from old table to new table
                conn.execute(text("""
                    INSERT INTO extraction_associations_new
                    (id, outcome_name, extraction_result, is_default, created_at, updated_at)
                    SELECT id, outcome_name, extraction_result, is_default, created_at, updated_at
                    FROM extraction_associations
                """))
                
                # Step 3: Drop old table
                conn.execute(text("DROP TABLE extraction_associations"))
                
                # Step 4: Rename new table to original name
                conn.execute(text("ALTER TABLE extraction_associations_new RENAME TO extraction_associations"))
                
                # Step 5: Recreate indexes (without unique constraint)
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_outcome_name ON extraction_associations(outcome_name)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_extraction_result ON extraction_associations(extraction_result)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_composite ON extraction_associations(outcome_name, extraction_result)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Unique constraint removed from extraction_associations table - outcomes can now have multiple result associations")
            return True
        except Exception as e:
            logger.error(f"Failed to remove unique constraint from extraction_associations: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Add unique constraint back to extraction_associations table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if there are any duplicate (outcome_name, extraction_result) combinations
                result = conn.execute(text("""
                    SELECT outcome_name, extraction_result, COUNT(*) as count
                    FROM extraction_associations
                    GROUP BY outcome_name, extraction_result
                    HAVING COUNT(*) > 1
                """))
                
                duplicates = result.fetchall()
                if duplicates:
                    logger.error(f"Cannot add unique constraint - duplicate combinations found: {[(row[0], row[1]) for row in duplicates]}")
                    return False
                
                # Recreate table with unique constraint
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS extraction_associations_new (
                        {pk_sql},
                        outcome_name VARCHAR(255) NOT NULL,
                        extraction_result VARCHAR(50) NOT NULL,
                        is_default BOOLEAN DEFAULT FALSE NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        UNIQUE(outcome_name, extraction_result)
                    )
                """))
                
                # Copy data from old table to new table
                conn.execute(text("""
                    INSERT INTO extraction_associations_new
                    (id, outcome_name, extraction_result, is_default, created_at, updated_at)
                    SELECT id, outcome_name, extraction_result, is_default, created_at, updated_at
                    FROM extraction_associations
                """))
                
                # Drop old table and rename new table
                conn.execute(text("DROP TABLE extraction_associations"))
                conn.execute(text("ALTER TABLE extraction_associations_new RENAME TO extraction_associations"))
                
                # Recreate indexes (with unique constraint)
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_outcome_name ON extraction_associations(outcome_name)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_extraction_result ON extraction_associations(extraction_result)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_associations_composite ON extraction_associations(outcome_name, extraction_result)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Unique constraint added back to extraction_associations table")
            return True
        except Exception as e:
            logger.error(f"Failed to add unique constraint back to extraction_associations: {e}")
            return False


class Migration_019_AddPaidFieldToBets(DatabaseMigration):
    """Add paid boolean field to bets table for payment tracking"""
    
    def __init__(self):
        super().__init__("019", "Add paid boolean field to bets table")
    
    def up(self, db_manager) -> bool:
        """Add paid column to bets table"""
        try:
            # Check if paid column already exists
            columns = get_table_columns(db_manager, 'bets')
            with db_manager.engine.connect() as conn:
                
                if 'paid' not in columns:
                    # Add paid column with default value False
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN paid BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    
                    conn.commit()
                    
                    logger.info("Paid column added to bets table")
                else:
                    logger.info("Paid column already exists in bets table")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add paid field to bets: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove paid column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - paid column will remain")
        return True


class Migration_020_FixBetDetailsForeignKey(DatabaseMigration):
    """Fix bets_details table to use bet UUID instead of bet ID"""
    
    def __init__(self):
        super().__init__("020", "Fix bets_details table foreign key to use bet UUID")
    
    def up(self, db_manager) -> bool:
        """Update bet_id column to use UUID instead of integer ID"""
        try:
            # Check current structure of bets_details table
            columns_dict = {}
            with db_manager.engine.connect() as conn:
                dialect = db_manager.engine.dialect.name
                if dialect == 'sqlite':
                    result = conn.execute(text("PRAGMA table_info(bets_details)"))
                    columns_dict = {row[1]: row[2] for row in result.fetchall()}
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("DESCRIBE bets_details"))
                    columns_dict = {row[0]: row[1] for row in result.fetchall()}
                else:
                    # Fallback to SQLAlchemy inspect
                    inspector = inspect(db_manager.engine)
                    for col in inspector.get_columns('bets_details'):
                        columns_dict[col['name']] = str(col['type'])
                
                # Check if bet_id is still INTEGER (needs to be changed to VARCHAR)
                if 'bet_id' in columns_dict and 'INTEGER' in columns_dict['bet_id'].upper():
                    logger.info("Converting bet_id from INTEGER to VARCHAR for UUID support")
                    
                    # Create new table with correct schema
                    pk_sql = get_primary_key_sql(db_manager)
                    conn.execute(text(f"""
                        CREATE TABLE IF NOT EXISTS bets_details_new (
                            {pk_sql},
                            bet_id VARCHAR(255) NOT NULL,
                            match_id INT NOT NULL,
                            outcome VARCHAR(255) NOT NULL,
                            amount REAL NOT NULL,
                            result VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            FOREIGN KEY (match_id) REFERENCES matches (id)
                        )
                    """))
                    
                    # Copy existing data (convert bet IDs to UUIDs if any data exists)
                    result = conn.execute(text("SELECT COUNT(*) FROM bets_details"))
                    detail_count = result.scalar()
                    
                    if detail_count > 0:
                        # Map old bet IDs to UUIDs
                        conn.execute(text("""
                            INSERT INTO bets_details_new
                            (id, bet_id, match_id, outcome, amount, result, created_at, updated_at)
                            SELECT bd.id, b.uuid, bd.match_id, bd.outcome, bd.amount, bd.result, bd.created_at, bd.updated_at
                            FROM bets_details bd
                            JOIN bets b ON bd.bet_id = b.id
                        """))
                    
                    # Drop old table and rename new one
                    conn.execute(text("DROP TABLE bets_details"))
                    conn.execute(text("ALTER TABLE bets_details_new RENAME TO bets_details"))
                    
                    # Recreate indexes
                    indexes = [
                        "CREATE INDEX IF NOT EXISTS ix_bets_details_bet_id ON bets_details(bet_id)",
                        "CREATE INDEX IF NOT EXISTS ix_bets_details_match_id ON bets_details(match_id)",
                        "CREATE INDEX IF NOT EXISTS ix_bets_details_outcome ON bets_details(outcome)",
                        "CREATE INDEX IF NOT EXISTS ix_bets_details_result ON bets_details(result)",
                        "CREATE INDEX IF NOT EXISTS ix_bets_details_composite ON bets_details(bet_id, match_id)",
                    ]
                    
                    for index_sql in indexes:
                        conn.execute(text(index_sql))
                    
                    logger.info("bets_details table updated to use UUID foreign key")
                else:
                    logger.info("bets_details table already uses correct UUID foreign key")
                
                conn.commit()
                
            return True
        except Exception as e:
            logger.error(f"Failed to fix bets_details foreign key: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Rollback to integer foreign key - only if no data would be lost"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if there's any betting data that would be lost
                result = conn.execute(text("SELECT COUNT(*) FROM bets_details"))
                detail_count = result.scalar()
                
                if detail_count > 0:
                    logger.error("Cannot rollback bets_details foreign key - would lose existing betting data")
                    return False
                
                # Safe to recreate with integer foreign key
                conn.execute(text("DROP TABLE bets_details"))
                
                pk_sql = get_primary_key_sql(db_manager)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS bets_details (
                        {pk_sql},
                        bet_id INT NOT NULL,
                        match_id INT NOT NULL,
                        outcome VARCHAR(255) NOT NULL,
                        amount REAL NOT NULL,
                        result VARCHAR(20) DEFAULT 'pending' NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        FOREIGN KEY (bet_id) REFERENCES bets (id) ON DELETE CASCADE,
                        FOREIGN KEY (match_id) REFERENCES matches (id)
                    )
                """))
                
                # Recreate indexes
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_bet_id ON bets_details(bet_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_match_id ON bets_details(match_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_outcome ON bets_details(outcome)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_result ON bets_details(result)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_details_composite ON bets_details(bet_id, match_id)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("bets_details table rolled back to integer foreign key")
            return True
        except Exception as e:
            logger.error(f"Failed to rollback bets_details foreign key: {e}")
            return False

class Migration_021_AddBarcodeConfiguration(DatabaseMigration):
    """Add barcode configuration settings for betting system"""

    def __init__(self):
        super().__init__("021", "Add barcode configuration settings for betting system")

    def up(self, db_manager) -> bool:
        """Add barcode configuration to configuration table"""
        try:
            with db_manager.engine.connect() as conn:
                # Add barcode configuration settings
                barcode_configs = [
                    {
                        'key': 'barcode.enabled',
                        'value': 'true',
                        'value_type': 'bool',
                        'description': 'Enable barcode generation for betting tickets',
                        'is_system': False
                    },
                    {
                        'key': 'barcode.standard',
                        'value': 'code128',
                        'value_type': 'string',
                        'description': 'Barcode standard: none, code128, code39, ean13, ean8, upca, upce, codabar, itf',
                        'is_system': False
                    },
                    {
                        'key': 'barcode.width',
                        'value': '300',
                        'value_type': 'int',
                        'description': 'Barcode image width in pixels',
                        'is_system': False
                    },
                    {
                        'key': 'barcode.height',
                        'value': '100',
                        'value_type': 'int',
                        'description': 'Barcode image height in pixels',
                        'is_system': False
                    },
                    {
                        'key': 'barcode.show_on_thermal',
                        'value': 'true',
                        'value_type': 'bool',
                        'description': 'Show barcode on thermal printed receipts',
                        'is_system': False
                    },
                    {
                        'key': 'barcode.show_on_verification',
                        'value': 'true',
                        'value_type': 'bool',
                        'description': 'Show barcode scanner option on verification pages',
                        'is_system': False
                    }
                ]

                for config in barcode_configs:
                    # Check if configuration already exists
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM configuration WHERE `key` = :key
                    """), {'key': config['key']})

                    exists = result.scalar() > 0

                    if not exists:
                        timestamp_sql = get_current_timestamp_sql(db_manager)
                        conn.execute(text(f"""
                            INSERT INTO configuration
                            (`key`, value, value_type, description, is_system, created_at, updated_at)
                            VALUES (:key, :value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                        """), config)
                        logger.info(f"Added barcode configuration: {config['key']} = {config['value']}")
                    else:
                        logger.info(f"Barcode configuration already exists: {config['key']}")

                conn.commit()

            logger.info("Barcode configuration settings added successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add barcode configuration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove barcode configuration settings"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove all barcode-related configurations
                conn.execute(text("""
                    DELETE FROM configuration
                    WHERE key LIKE 'barcode.%'
                """))

                conn.commit()

            logger.info("Barcode configuration settings removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove barcode configuration: {e}")
            return False


class Migration_022_AddQRCodeConfiguration(DatabaseMigration):
    """Add QR code configuration settings for betting system"""

    def __init__(self):
        super().__init__("022", "Add QR code configuration settings for betting system")

    def up(self, db_manager) -> bool:
        """Add QR code configuration to configuration table"""
        try:
            with db_manager.engine.connect() as conn:
                # Add QR code configuration settings
                qrcode_configs = [
                    {
                        'key': 'qrcode.enabled',
                        'value': 'false',
                        'value_type': 'bool',
                        'description': 'Enable QR code generation for betting tickets',
                        'is_system': False
                    },
                    {
                        'key': 'qrcode.size',
                        'value': '200',
                        'value_type': 'int',
                        'description': 'QR code image size in pixels (square)',
                        'is_system': False
                    },
                    {
                        'key': 'qrcode.error_correction',
                        'value': 'M',
                        'value_type': 'string',
                        'description': 'QR code error correction level: L, M, Q, H',
                        'is_system': False
                    },
                    {
                        'key': 'qrcode.show_on_thermal',
                        'value': 'true',
                        'value_type': 'bool',
                        'description': 'Show QR code on thermal printed receipts',
                        'is_system': False
                    },
                    {
                        'key': 'qrcode.show_on_verification',
                        'value': 'true',
                        'value_type': 'bool',
                        'description': 'Show QR code on bet verification pages',
                        'is_system': False
                    }
                ]

                for config in qrcode_configs:
                    # Check if configuration already exists
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM configuration WHERE `key` = :key
                    """), {'key': config['key']})

                    exists = result.scalar() > 0

                    if not exists:
                        timestamp_sql = get_current_timestamp_sql(db_manager)
                        conn.execute(text(f"""
                            INSERT INTO configuration
                            (`key`, value, value_type, description, is_system, created_at, updated_at)
                            VALUES (:key, :value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                        """), config)
                        logger.info(f"Added QR code configuration: {config['key']} = {config['value']}")
                    else:
                        logger.info(f"QR code configuration already exists: {config['key']}")

                conn.commit()

            logger.info("QR code configuration settings added successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add QR code configuration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove QR code configuration settings"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove all QR code-related configurations
                conn.execute(text("""
                    DELETE FROM configuration
                    WHERE key LIKE 'qrcode.%'
                """))

                conn.commit()

            logger.info("QR code configuration settings removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove QR code configuration: {e}")
            return False


class Migration_023_AddAvailableBetsTable(DatabaseMigration):
    """Add available_bets table for managing betting options in extraction page"""

    def __init__(self):
        super().__init__("023", "Add available_bets table for managing betting options")

    def up(self, db_manager) -> bool:
        """Create available_bets table with default betting options"""
        try:
            with db_manager.engine.connect() as conn:
                # Create available_bets table
                pk_sql = get_primary_key_sql(db_manager)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS available_bets (
                        {pk_sql},
                        bet_name VARCHAR(50) NOT NULL UNIQUE,
                        description VARCHAR(255),
                        is_active BOOLEAN DEFAULT TRUE NOT NULL,
                        sort_order INT DEFAULT 0,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """))

                # Create indexes for available_bets table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_available_bets_bet_name ON available_bets(bet_name)",
                    "CREATE INDEX IF NOT EXISTS ix_available_bets_is_active ON available_bets(is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_available_bets_sort_order ON available_bets(sort_order)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                # Insert default betting options
                default_bets = [
                    ('DRAW', 'Draw outcome', 1),
                    ('DKO', 'Double Knockout', 2),
                    ('X1', 'Draw No Bet - Fighter 1', 3),
                    ('X2', 'Draw No Bet - Fighter 2', 4),
                    ('12', '1X2 betting (1, X, 2)', 5),
                    ('WIN1', 'Fighter 1 wins', 6),
                    ('WIN2', 'Fighter 2 wins', 7),
                    ('RET1', 'Fighter 1 retires', 8),
                    ('RET2', 'Fighter 2 retires', 9),
                    ('PTS1', 'Fighter 1 wins by points', 10),
                    ('PTS2', 'Fighter 2 wins by points', 11),
                    ('OVER', 'Over time limit', 12),
                    ('UNDER', 'Under time limit', 13),
                    ('KO1', 'Fighter 1 wins by KO', 14),
                    ('KO2', 'Fighter 2 wins by KO', 15),
                ]

                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                for bet_name, description, sort_order in default_bets:
                    conn.execute(text(f"""
                        {insert_ignore} INTO available_bets
                        (bet_name, description, is_active, sort_order, created_at, updated_at)
                        VALUES (:bet_name, :description, 1, :sort_order, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'bet_name': bet_name,
                        'description': description,
                        'sort_order': sort_order
                    })

                conn.commit()

            logger.info("Available bets table created with default betting options")
            return True
        except Exception as e:
            logger.error(f"Failed to create available_bets table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop available_bets table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS available_bets"))
                conn.commit()

            logger.info("Available bets table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop available_bets table: {e}")
            return False


class Migration_024_AddResultOptionsTable(DatabaseMigration):
    """Add result_options table for managing result options in extraction page"""

    def __init__(self):
        super().__init__("024", "Add result_options table for managing result options")

    def up(self, db_manager) -> bool:
        """Create result_options table with default result options"""
        try:
            with db_manager.engine.connect() as conn:
                # Create result_options table
                pk_sql = get_primary_key_sql(db_manager)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS result_options (
                        {pk_sql},
                        result_name VARCHAR(50) NOT NULL UNIQUE,
                        description VARCHAR(255),
                        is_active BOOLEAN DEFAULT TRUE NOT NULL,
                        sort_order INT DEFAULT 0,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """))

                # Create indexes for result_options table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_result_options_result_name ON result_options(result_name)",
                    "CREATE INDEX IF NOT EXISTS ix_result_options_is_active ON result_options(is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_result_options_sort_order ON result_options(sort_order)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                # Insert default result options as specified by user
                default_results = [
                    ('DRAW', 'Draw result', 1),
                    ('DKO', 'Double Knockout result', 2),
                    ('RET1', 'Fighter 1 retires result', 3),
                    ('RET2', 'Fighter 2 retires result', 4),
                    ('PTS1', 'Fighter 1 wins by points result', 5),
                    ('PTS2', 'Fighter 2 wins by points result', 6),
                    ('KO1', 'Fighter 1 wins by KO result', 7),
                    ('KO2', 'Fighter 2 wins by KO result', 8),
                ]

                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                for result_name, description, sort_order in default_results:
                    conn.execute(text(f"""
                        {insert_ignore} INTO result_options
                        (result_name, description, is_active, sort_order, created_at, updated_at)
                        VALUES (:result_name, :description, 1, :sort_order, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'result_name': result_name,
                        'description': description,
                        'sort_order': sort_order
                    })

                conn.commit()

            logger.info("Result options table created with default result options")
            return True
        except Exception as e:
            logger.error(f"Failed to create result_options table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop result_options table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS result_options"))
                conn.commit()

            logger.info("Result options table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop result_options table: {e}")
            return False


class Migration_025_AddResultOptionModel(DatabaseMigration):
    """Add ResultOptionModel to models.py for result options management"""

    def __init__(self):
        super().__init__("025", "Add ResultOptionModel to models.py for result options management")

    def up(self, db_manager) -> bool:
        """This migration just ensures the ResultOptionModel is available - no database changes needed"""
        try:
            logger.info("ResultOptionModel migration - no database changes required")
            return True
        except Exception as e:
            logger.error(f"Failed to apply ResultOptionModel migration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """No database changes to rollback"""
        logger.info("ResultOptionModel migration rollback - no database changes")
        return True


class Migration_026_AddExtractionStatsTable(DatabaseMigration):
    """Add extraction_stats table for collecting match betting statistics"""

    def __init__(self):
        super().__init__("026", "Add extraction_stats table for collecting match betting statistics")

    def up(self, db_manager) -> bool:
        """Create extraction_stats table"""
        try:
            with db_manager.engine.connect() as conn:
                # Create extraction_stats table
                pk_sql = get_primary_key_sql(db_manager)
                dialect = db_manager.engine.dialect.name
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text(f"""
                        CREATE TABLE IF NOT EXISTS extraction_stats (
                            {pk_sql},
                            match_id INT NOT NULL,
                            fixture_id VARCHAR(255) NOT NULL,
                            match_datetime DATETIME NOT NULL,

                            total_bets INT DEFAULT 0 NOT NULL,
                            total_amount_collected REAL DEFAULT 0.0 NOT NULL,
                            total_redistributed REAL DEFAULT 0.0 NOT NULL,

                            actual_result VARCHAR(50) NOT NULL,
                            result_breakdown TEXT NOT NULL,

                            under_bets INT DEFAULT 0 NOT NULL,
                            under_amount REAL DEFAULT 0.0 NOT NULL,
                            over_bets INT DEFAULT 0 NOT NULL,
                            over_amount REAL DEFAULT 0.0 NOT NULL,

                            extraction_result VARCHAR(50),
                            cap_applied BOOLEAN DEFAULT FALSE NOT NULL,
                            cap_percentage REAL,

                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            FOREIGN KEY (match_id) REFERENCES matches(id)
                        )
                    """))
                else:
                    conn.execute(text(f"""
                        CREATE TABLE IF NOT EXISTS extraction_stats (
                            {pk_sql},
                            match_id INTEGER NOT NULL REFERENCES matches(id),
                            fixture_id VARCHAR(255) NOT NULL,
                            match_datetime DATETIME NOT NULL,

                            total_bets INTEGER DEFAULT 0 NOT NULL,
                            total_amount_collected REAL DEFAULT 0.0 NOT NULL,
                            total_redistributed REAL DEFAULT 0.0 NOT NULL,

                            actual_result VARCHAR(50) NOT NULL,
                            result_breakdown TEXT NOT NULL,

                            under_bets INTEGER DEFAULT 0 NOT NULL,
                            under_amount REAL DEFAULT 0.0 NOT NULL,
                            over_bets INTEGER DEFAULT 0 NOT NULL,
                            over_amount REAL DEFAULT 0.0 NOT NULL,

                            extraction_result VARCHAR(50),
                            cap_applied BOOLEAN DEFAULT FALSE NOT NULL,
                            cap_percentage REAL,

                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """))

                # Create indexes for extraction_stats table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_extraction_stats_match_id ON extraction_stats(match_id)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_stats_fixture_id ON extraction_stats(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_stats_match_datetime ON extraction_stats(match_datetime)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_stats_actual_result ON extraction_stats(actual_result)",
                    "CREATE INDEX IF NOT EXISTS ix_extraction_stats_composite ON extraction_stats(fixture_id, match_id)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Extraction stats table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create extraction_stats table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop extraction_stats table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS extraction_stats"))
                conn.commit()

            logger.info("Extraction stats table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop extraction_stats table: {e}")
            return False


class Migration_027_AddDefaultIntroTemplatesConfig(DatabaseMigration):
    """Add default intro templates configuration for Qt player"""

    def __init__(self):
        super().__init__("027", "Add default intro templates configuration for Qt player")

    def up(self, db_manager) -> bool:
        """Add default intro templates configuration to game_config table"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if intro_templates_config already exists
                result = conn.execute(text("""
                    SELECT COUNT(*) FROM game_config WHERE config_key = 'intro_templates_config'
                """))

                exists = result.scalar() > 0

                if not exists:
                    # Create default intro templates configuration
                    default_config = {
                        "templates": [
                            {
                                "name": "fixtures",
                                "show_time": "00:15"
                            },
                            {
                                "name": "match",
                                "show_time": "00:15"
                            }
                        ],
                        "default_show_time": "00:15",
                        "rotating_time": "00:15"
                    }

                    import json
                    config_json = json.dumps(default_config)

                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        INSERT INTO game_config
                        (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                        VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'config_key': 'intro_templates_config',
                        'config_value': config_json,
                        'value_type': 'string',
                        'description': 'Default intro templates configuration for Qt player START_INTRO messages',
                        'is_system': True
                    })

                    logger.info("Added default intro templates configuration")
                else:
                    logger.info("Intro templates configuration already exists")

                conn.commit()

            logger.info("Default intro templates configuration migration completed successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add default intro templates configuration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove default intro templates configuration"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove the intro templates configuration
                conn.execute(text("""
                    DELETE FROM game_config WHERE config_key = 'intro_templates_config'
                """))

                conn.commit()

            logger.info("Default intro templates configuration removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default intro templates configuration: {e}")
            return False


class Migration_028_AddFixtureRefreshIntervalConfig(DatabaseMigration):
    """Add fixture refresh interval configuration for web dashboard"""

    def __init__(self):
        super().__init__("028", "Add fixture refresh interval configuration for web dashboard")

    def up(self, db_manager) -> bool:
        """Add fixture refresh interval configuration to game_config table"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if fixture_refresh_interval already exists
                result = conn.execute(text("""
                    SELECT COUNT(*) FROM game_config WHERE config_key = 'fixture_refresh_interval'
                """))

                exists = result.scalar() > 0

                if not exists:
                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        INSERT INTO game_config
                        (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                        VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'config_key': 'fixture_refresh_interval',
                        'config_value': '15',
                        'value_type': 'int',
                        'description': 'Auto-refresh interval in seconds for fixture pages in web dashboard',
                        'is_system': False
                    })

                    logger.info("Added fixture refresh interval configuration (15 seconds)")
                else:
                    logger.info("Fixture refresh interval configuration already exists")

                conn.commit()

            logger.info("Fixture refresh interval configuration migration completed successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add fixture refresh interval configuration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove fixture refresh interval configuration"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove the fixture refresh interval configuration
                conn.execute(text("""
                    DELETE FROM game_config WHERE config_key = 'fixture_refresh_interval'
                """))

                conn.commit()

            logger.info("Fixture refresh interval configuration removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove fixture refresh interval configuration: {e}")
            return False


class Migration_029_ChangeMatchNumberToUniqueWithinFixture(DatabaseMigration):
    """Change match_number from globally unique to unique within fixture"""

    def __init__(self):
        super().__init__("029", "Change match_number from globally unique to unique within fixture")

    def up(self, db_manager) -> bool:
        """Change match_number constraint from global uniqueness to unique within fixture"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # SQLite doesn't support ALTER TABLE DROP CONSTRAINT directly
                # We need to recreate the table with the new constraint
                
                # For MySQL, disable foreign key checks temporarily
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))

                # Step 1: Create new table with correct constraint
                # Note: zip_validation_status is added in Migration 030
                # Note: winning_outcomes and under_over_result are added in Migration 031
                # Note: accumulated_shortfall and cap_percent are added in Migration 041
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS matches_new (
                        {pk_sql},
                        match_number INTEGER NOT NULL,
                        fighter1_township VARCHAR(255) NOT NULL,
                        fighter2_township VARCHAR(255) NOT NULL,
                        venue_kampala_township VARCHAR(255) NOT NULL,

                        start_time DATETIME NULL,
                        end_time DATETIME NULL,
                        result VARCHAR(255) NULL,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE NOT NULL,
                        status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                        fixture_active_time INTEGER NULL,

                        filename VARCHAR(1024) NOT NULL,
                        file_sha1sum VARCHAR(255) NOT NULL,
                        fixture_id VARCHAR(255) NOT NULL,
                        active_status BOOLEAN DEFAULT FALSE,

                        zip_filename VARCHAR(1024) NULL,
                        zip_sha1sum VARCHAR(255) NULL,
                        zip_upload_status VARCHAR(20) DEFAULT 'pending',
                        zip_upload_progress REAL DEFAULT 0.0,

                        created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

                        UNIQUE(fixture_id, match_number)
                    )
                """))

                # Step 2: Copy data from old table to new table using explicit column names
                # Exclude zip_validation_status (added in Migration 030)
                # Exclude winning_outcomes and under_over_result (added in Migration 031)
                # Exclude accumulated_shortfall and cap_percent (added in Migration 041)
                conn.execute(text("""
                    INSERT INTO matches_new
                    (id, match_number, fighter1_township, fighter2_township, venue_kampala_township,
                     start_time, end_time, result,
                     done, running, status, fixture_active_time,
                     filename, file_sha1sum, fixture_id, active_status,
                     zip_filename, zip_sha1sum, zip_upload_status, zip_upload_progress,
                     created_by, created_at, updated_at)
                    SELECT id, match_number, fighter1_township, fighter2_township, venue_kampala_township,
                           start_time, end_time, result,
                           done, running, status, fixture_active_time,
                           filename, file_sha1sum, fixture_id, active_status,
                           zip_filename, zip_sha1sum, zip_upload_status, zip_upload_progress,
                           created_by, created_at, updated_at
                    FROM matches
                """))

                # Step 3: Drop old table
                conn.execute(text("DROP TABLE matches"))

                # Step 4: Rename new table to original name
                conn.execute(text("ALTER TABLE matches_new RENAME TO matches"))
                
                # Re-enable foreign key checks for MySQL
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))

                # Step 5: Recreate indexes (without the old global unique constraint)
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_matches_match_number ON matches(match_number)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_id ON matches(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_active_status ON matches(active_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_file_sha1sum ON matches(file_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_sha1sum ON matches(zip_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_upload_status ON matches(zip_upload_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_created_by ON matches(created_by)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_active_time ON matches(fixture_active_time)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_done ON matches(done)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_running ON matches(running)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_status ON matches(status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_composite ON matches(active_status, zip_upload_status, created_at)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Changed match_number constraint from globally unique to unique within fixture")
            return True
        except Exception as e:
            logger.error(f"Failed to change match_number constraint: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Revert match_number constraint back to globally unique"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Check if there are any duplicate match_numbers within the same fixture
                # that would prevent adding back the global unique constraint
                result = conn.execute(text("""
                    SELECT fixture_id, match_number, COUNT(*) as count
                    FROM matches
                    GROUP BY fixture_id, match_number
                    HAVING COUNT(*) > 1
                """))

                duplicates = result.fetchall()
                if duplicates:
                    logger.error(f"Cannot revert to global unique constraint - duplicate match_numbers within fixtures found: {[(row[0], row[1]) for row in duplicates]}")
                    return False

                # For MySQL, disable foreign key checks temporarily
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))

                # Recreate table with global unique constraint on match_number
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS matches_new (
                        {pk_sql},
                        match_number INTEGER NOT NULL UNIQUE,
                        fighter1_township VARCHAR(255) NOT NULL,
                        fighter2_township VARCHAR(255) NOT NULL,
                        venue_kampala_township VARCHAR(255) NOT NULL,

                        start_time DATETIME NULL,
                        end_time DATETIME NULL,
                        result VARCHAR(255) NULL,
                        winning_outcomes TEXT NULL,
                        under_over_result VARCHAR(50) NULL,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE NOT NULL,
                        status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                        fixture_active_time INTEGER NULL,

                        filename VARCHAR(1024) NOT NULL,
                        file_sha1sum VARCHAR(255) NOT NULL,
                        fixture_id VARCHAR(255) NOT NULL,
                        active_status BOOLEAN DEFAULT FALSE,

                        zip_filename VARCHAR(1024) NULL,
                        zip_sha1sum VARCHAR(255) NULL,
                        zip_upload_status VARCHAR(20) DEFAULT 'pending',
                        zip_upload_progress REAL DEFAULT 0.0,
                        zip_validation_status VARCHAR(20) DEFAULT 'pending',

                        created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                    )
                """))

                # Copy data from old table to new table
                conn.execute(text("""
                    INSERT INTO matches_new
                    SELECT * FROM matches
                """))

                # Drop old table and rename new table
                conn.execute(text("DROP TABLE matches"))
                conn.execute(text("ALTER TABLE matches_new RENAME TO matches"))
                
                # Re-enable foreign key checks for MySQL
                if dialect in ('mysql', 'mariadb'):
                    conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))

                # Recreate indexes
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_matches_match_number ON matches(match_number)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_id ON matches(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_active_status ON matches(active_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_file_sha1sum ON matches(file_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_sha1sum ON matches(zip_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_zip_upload_status ON matches(zip_upload_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_created_by ON matches(created_by)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_fixture_active_time ON matches(fixture_active_time)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_done ON matches(done)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_running ON matches(running)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_status ON matches(status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_composite ON matches(active_status, zip_upload_status, created_at)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Reverted match_number constraint back to globally unique")
            return True
        except Exception as e:
            logger.error(f"Failed to revert match_number constraint: {e}")
            return False


class Migration_030_AddZipValidationStatus(DatabaseMigration):
    """Add zip_validation_status field to matches table"""

    def __init__(self):
        super().__init__("030", "Add zip_validation_status field to matches table")

    def up(self, db_manager) -> bool:
        """Add zip_validation_status column to matches table"""
        try:
            # Check if zip_validation_status column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:

                if 'zip_validation_status' not in columns:
                    # Add zip_validation_status column with default value 'pending'
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN zip_validation_status VARCHAR(20) DEFAULT 'pending'
                    """))

                    # Add index for zip_validation_status column
                    conn.execute(text("""
                        CREATE INDEX IF NOT EXISTS ix_matches_zip_validation_status
                        ON matches(zip_validation_status)
                    """))

                    conn.commit()

                    logger.info("zip_validation_status column added to matches table")
                else:
                    logger.info("zip_validation_status column already exists in matches table")

            return True
        except Exception as e:
            logger.error(f"Failed to add zip_validation_status field to matches: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove zip_validation_status column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - zip_validation_status column will remain")
        return True


class Migration_031_AddWinningOutcomesFields(DatabaseMigration):
    """Add winning_outcomes and under_over_result fields to matches table"""

    def __init__(self):
        super().__init__("031", "Add winning_outcomes and under_over_result fields to matches table")

    def up(self, db_manager) -> bool:
        """Add winning_outcomes and under_over_result columns to matches table"""
        try:
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:

                if 'winning_outcomes' not in columns:
                    # Add winning_outcomes column (JSON array)
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN winning_outcomes TEXT
                    """))

                    logger.info("winning_outcomes column added to matches table")
                else:
                    logger.info("winning_outcomes column already exists in matches table")

                if 'under_over_result' not in columns:
                    # Add under_over_result column (string)
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN under_over_result VARCHAR(50)
                    """))

                    logger.info("under_over_result column added to matches table")
                else:
                    logger.info("under_over_result column already exists in matches table")

                conn.commit()

            return True
        except Exception as e:
            logger.error(f"Failed to add winning outcomes fields to matches: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove winning_outcomes and under_over_result columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - winning_outcomes and under_over_result columns will remain")
        return True
class Migration_032_FixExtractionAssociationDefaults(DatabaseMigration):
    """Fix incorrect defaults in extraction_associations table for KO1 and KO2"""

    def __init__(self):
        super().__init__("032", "Fix incorrect defaults in extraction_associations table for KO1 and KO2")

    def up(self, db_manager) -> bool:
        """Update extraction associations to correct KO1 and KO2 defaults"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove incorrect associations
                # Remove WIN1 and X1 from KO1
                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'WIN1' AND extraction_result = 'KO1' AND is_default = 1
                """))

                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'X1' AND extraction_result = 'KO1' AND is_default = 1
                """))

                # Remove WIN2 and X2 from KO2
                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'WIN2' AND extraction_result = 'KO2' AND is_default = 1
                """))

                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'X2' AND extraction_result = 'KO2' AND is_default = 1
                """))

                # Add correct associations
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                # Add WIN2 and X2 to KO1
                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('WIN2', 'KO1', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('X2', 'KO1', 1, {timestamp_sql}, {timestamp_sql})
                """))

                # Add WIN1 and X1 to KO2
                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('WIN1', 'KO2', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('X1', 'KO2', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.commit()

            logger.info("Fixed extraction association defaults for KO1 and KO2")
            return True
        except Exception as e:
            logger.error(f"Failed to fix extraction association defaults: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Revert the extraction association changes"""
        try:
            insert_ignore = get_insert_ignore_sql(db_manager)
            timestamp_sql = get_current_timestamp_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Remove the corrected associations
                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'WIN2' AND extraction_result = 'KO1' AND is_default = 1
                """))

                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'X2' AND extraction_result = 'KO1' AND is_default = 1
                """))

                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'WIN1' AND extraction_result = 'KO2' AND is_default = 1
                """))

                conn.execute(text("""
                    DELETE FROM extraction_associations
                    WHERE outcome_name = 'X1' AND extraction_result = 'KO2' AND is_default = 1
                """))

                # Restore original incorrect associations
                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('WIN1', 'KO1', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('X1', 'KO1', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('WIN2', 'KO2', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.execute(text(f"""
                    {insert_ignore} INTO extraction_associations
                    (outcome_name, extraction_result, is_default, created_at, updated_at)
                    VALUES ('X2', 'KO2', 1, {timestamp_sql}, {timestamp_sql})
                """))

                conn.commit()

            logger.info("Reverted extraction association defaults for KO1 and KO2")
            return True
        except Exception as e:
            logger.error(f"Failed to revert extraction association defaults: {e}")
            return False


class Migration_033_AddBarcodeFieldsToBets(DatabaseMigration):
    """Add barcode_standard and barcode_data fields to bets table for barcode verification"""

    def __init__(self):
        super().__init__("033", "Add barcode_standard and barcode_data fields to bets table")

    def up(self, db_manager) -> bool:
        """Add barcode fields to bets table"""
        try:
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'bets')
            with db_manager.engine.connect() as conn:

                if 'barcode_standard' not in columns:
                    # Add barcode_standard column
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN barcode_standard VARCHAR(50)
                    """))

                    logger.info("barcode_standard column added to bets table")
                else:
                    logger.info("barcode_standard column already exists in bets table")

                if 'barcode_data' not in columns:
                    # Add barcode_data column
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN barcode_data VARCHAR(255)
                    """))

                    logger.info("barcode_data column added to bets table")
                else:
                    logger.info("barcode_data column already exists in bets table")

                # Add indexes for barcode fields
                conn.execute(text("""
                    CREATE INDEX IF NOT EXISTS ix_bets_barcode_data ON bets(barcode_data)
                """))

                conn.execute(text("""
                    CREATE INDEX IF NOT EXISTS ix_bets_barcode_standard ON bets(barcode_standard)
                """))

                # Add unique constraint for barcode data per standard
                try:
                    conn.execute(text("""
                        CREATE UNIQUE INDEX IF NOT EXISTS uq_bets_barcode ON bets(barcode_data, barcode_standard)
                    """))
                except Exception as e:
                    logger.warning(f"Could not create unique constraint on barcode fields: {e}")

                conn.commit()

            logger.info("Barcode fields added to bets table successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add barcode fields to bets table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove barcode fields - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - barcode fields will remain")
        return True


class Migration_034_AddDefaultLicenseText(DatabaseMigration):
    """Add default license text configuration for overlay templates"""

    def __init__(self):
        super().__init__("034", "Add default license text configuration for overlay templates")

    def up(self, db_manager) -> bool:
        """Add default license text to configuration table"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if license text configuration already exists
                result = conn.execute(text("""
                    SELECT COUNT(*) FROM configuration WHERE `key` = 'license_text'
                """))

                exists = result.scalar() > 0

                if not exists:
                    default_license_text = 'LICENSED BY THE NATIONAL LOTTERIES AND GAMING REGULATORY BOARD - LICENSE NUMBER NLGBR-GB-82-006. Gambling can be addictive and psychologically harmful not allowed for minors'

                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    conn.execute(text(f"""
                        INSERT INTO configuration
                        (`key`, value, value_type, description, is_system, created_at, updated_at)
                        VALUES (:key, :value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'key': 'license_text',
                        'value': default_license_text,
                        'value_type': 'string',
                        'description': 'License text displayed on overlay templates',
                        'is_system': False
                    })

                    logger.info("Added default license text configuration")
                else:
                    logger.info("License text configuration already exists")

                conn.commit()

            logger.info("Default license text configuration migration completed successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add default license text configuration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove default license text configuration"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove the license text configuration
                conn.execute(text("""
                    DELETE FROM configuration WHERE `key` = 'license_text'
                """))

                conn.commit()

            logger.info("Default license text configuration removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default license text configuration: {e}")
            return False


class Migration_035_AddDailyRedistributionShortfallTable(DatabaseMigration):
    """Add daily_redistribution_shortfall table for tracking CAP shortfalls across matches"""

    def __init__(self):
        super().__init__("035", "Add daily_redistribution_shortfall table for tracking CAP shortfalls")

    def up(self, db_manager) -> bool:
        """Create daily_redistribution_shortfall table"""
        try:
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Create daily_redistribution_shortfall table
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS daily_redistribution_shortfall (
                        {pk_sql},
                        date DATE NOT NULL UNIQUE,
                        accumulated_shortfall REAL DEFAULT 0.0 NOT NULL,
                        total_payin REAL DEFAULT 0.0 NOT NULL,
                        total_redistributed REAL DEFAULT 0.0 NOT NULL,
                        cap_percentage REAL DEFAULT 70.0 NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                    )
                """))

                # Create indexes for daily_redistribution_shortfall table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_daily_redistribution_shortfall_date ON daily_redistribution_shortfall(date)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Daily redistribution shortfall table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create daily_redistribution_shortfall table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop daily_redistribution_shortfall table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS daily_redistribution_shortfall"))
                conn.commit()

            logger.info("Daily redistribution shortfall table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop daily_redistribution_shortfall table: {e}")
            return False


class Migration_037_RenameDailyRedistributionShortfallTable(DatabaseMigration):
    """Rename daily_redistribution_shortfall table to persistent_redistribution_adjustment"""

    def __init__(self):
        super().__init__("037", "Rename daily_redistribution_shortfall table to persistent_redistribution_adjustment")

    def up(self, db_manager) -> bool:
        """Rename the table"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if the old table exists
                dialect = db_manager.engine.dialect.name
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='daily_redistribution_shortfall'
                    """))
                    old_table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'daily_redistribution_shortfall'
                    """))
                    old_table_exists = result.scalar() > 0
                else:
                    # Fallback to SQLAlchemy inspect
                    inspector = inspect(db_manager.engine)
                    old_table_exists = 'daily_redistribution_shortfall' in inspector.get_table_names()

                if old_table_exists:
                    # Rename the table
                    conn.execute(text("""
                        ALTER TABLE daily_redistribution_shortfall
                        RENAME TO persistent_redistribution_adjustment
                    """))

                    # Note: SQLite doesn't support ALTER INDEX RENAME, so we'll leave the old index name
                    # The index will be recreated with the new name when the table is recreated in future migrations

                    conn.commit()
                    logger.info("Renamed daily_redistribution_shortfall table to persistent_redistribution_adjustment")
                else:
                    logger.info("daily_redistribution_shortfall table does not exist, nothing to rename")

            return True
        except Exception as e:
            logger.error(f"Failed to rename daily_redistribution_shortfall table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Rename back to the old table name"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if the new table exists
                dialect = db_manager.engine.dialect.name
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='persistent_redistribution_adjustment'
                    """))
                    new_table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'persistent_redistribution_adjustment'
                    """))
                    new_table_exists = result.scalar() > 0
                else:
                    # Fallback to SQLAlchemy inspect
                    inspector = inspect(db_manager.engine)
                    new_table_exists = 'persistent_redistribution_adjustment' in inspector.get_table_names()

                if new_table_exists:
                    # Rename back to the old name
                    conn.execute(text("""
                        ALTER TABLE persistent_redistribution_adjustment
                        RENAME TO daily_redistribution_shortfall
                    """))

                    # Note: SQLite doesn't support ALTER INDEX RENAME, so we'll leave the index name as-is

                    conn.commit()
                    logger.info("Renamed persistent_redistribution_adjustment table back to daily_redistribution_shortfall")
                else:
                    logger.info("persistent_redistribution_adjustment table does not exist, nothing to rename back")

            return True
        except Exception as e:
            logger.error(f"Failed to rename back to daily_redistribution_shortfall table: {e}")
            return False


class Migration_038_AddWin1Win2Associations(DatabaseMigration):
    """Add WIN1 and WIN2 result options and extraction associations for X1, X2, and 12 outcomes"""

    def __init__(self):
        super().__init__("038", "Add WIN1 and WIN2 result options and extraction associations for X1, X2, and 12 outcomes")

    def up(self, db_manager) -> bool:
        """Add WIN1 and WIN2 result options and associations if they don't exist"""
        try:
            with db_manager.engine.connect() as conn:
                # Add WIN1 and WIN2 to result_options table
                result_options = [
                    ('WIN1', 'Fighter 1 wins result', 9),
                    ('WIN2', 'Fighter 2 wins result', 10)
                ]

                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                for result_name, description, sort_order in result_options:
                    conn.execute(text(f"""
                        {insert_ignore} INTO result_options
                        (result_name, description, is_active, sort_order, created_at, updated_at)
                        VALUES (:result_name, :description, 1, :sort_order, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'result_name': result_name,
                        'description': description,
                        'sort_order': sort_order
                    })

                # Add WIN1 associations: X1 and 12 -> WIN1, and WIN1 -> WIN1
                associations = [
                    ('X1', 'WIN1'),
                    ('12', 'WIN1'),
                    ('WIN1', 'WIN1'),
                    ('X2', 'WIN2'),
                    ('12', 'WIN2'),
                    ('WIN2', 'WIN2')
                ]

                for outcome_name, extraction_result in associations:
                    conn.execute(text(f"""
                        {insert_ignore} INTO extraction_associations
                        (outcome_name, extraction_result, is_default, created_at, updated_at)
                        VALUES (:outcome_name, :extraction_result, 1, {timestamp_sql}, {timestamp_sql})
                    """), {
                        'outcome_name': outcome_name,
                        'extraction_result': extraction_result
                    })

                conn.commit()
                logger.info("Added WIN1 and WIN2 result options and extraction associations")

            return True
        except Exception as e:
            logger.error(f"Failed to add WIN1 and WIN2 result options and associations: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove the WIN1 and WIN2 result options and associations added by this migration"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove WIN1 and WIN2 from result_options
                conn.execute(text("""
                    DELETE FROM result_options
                    WHERE result_name IN ('WIN1', 'WIN2')
                """))

                # Remove the specific associations added by this migration
                associations = [
                    ('X1', 'WIN1'),
                    ('12', 'WIN1'),
                    ('WIN1', 'WIN1'),
                    ('X2', 'WIN2'),
                    ('12', 'WIN2'),
                    ('WIN2', 'WIN2')
                ]

                for outcome_name, extraction_result in associations:
                    conn.execute(text("""
                        DELETE FROM extraction_associations
                        WHERE outcome_name = :outcome_name
                        AND extraction_result = :extraction_result
                        AND is_default = 1
                    """), {
                        'outcome_name': outcome_name,
                        'extraction_result': extraction_result
                    })

                conn.commit()
                logger.info("Removed WIN1 and WIN2 result options and extraction associations")

            return True
        except Exception as e:
            logger.error(f"Failed to remove WIN1 and WIN2 result options and associations: {e}")
            return False


class Migration_039_AddMatchNumberToBetDetails(DatabaseMigration):
    """Add match_number column to bets_details table for storing match numbers directly"""

    def __init__(self):
        super().__init__("039", "Add match_number column to bets_details table")

    def up(self, db_manager) -> bool:
        """Add match_number column to bets_details table"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if match_number column already exists
                columns = get_table_columns(db_manager, 'bets_details')

                if 'match_number' not in columns:
                    # Add match_number column
                    conn.execute(text("""
                        ALTER TABLE bets_details
                        ADD COLUMN match_number INTEGER
                    """))

                    # Populate existing records with match numbers from matches table
                    conn.execute(text("""
                        UPDATE bets_details
                        SET match_number = (
                            SELECT m.match_number
                            FROM matches m
                            WHERE m.id = bets_details.match_id
                        )
                        WHERE match_number IS NULL
                    """))

                    conn.commit()

                    logger.info("match_number column added to bets_details table")
                else:
                    logger.info("match_number column already exists in bets_details table")

            return True
        except Exception as e:
            logger.error(f"Failed to add match_number column to bets_details: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove match_number column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - match_number column will remain")
        return True


class Migration_040_AddAccumulatedShortfallToMatches(DatabaseMigration):
    """Add accumulated_shortfall field to matches table for storing historical shortfall values"""

    def __init__(self):
        super().__init__("040", "Add accumulated_shortfall field to matches table")

    def up(self, db_manager) -> bool:
        """Add accumulated_shortfall column to matches table"""
        try:
            # Check if accumulated_shortfall column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:

                if 'accumulated_shortfall' not in columns:
                    # Add accumulated_shortfall column with default value 0.0
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN accumulated_shortfall REAL DEFAULT 0.0 NOT NULL
                    """))

                    conn.commit()

                    logger.info("accumulated_shortfall column added to matches table")
                else:
                    logger.info("accumulated_shortfall column already exists in matches table")

            return True
        except Exception as e:
            logger.error(f"Failed to add accumulated_shortfall field to matches: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove accumulated_shortfall column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - accumulated_shortfall column will remain")
        return True


class Migration_041_AddCapPercentToMatches(DatabaseMigration):
    """Add cap_percent field to matches table for storing CAP percentage at match completion"""

    def __init__(self):
        super().__init__("041", "Add cap_percent field to matches table")

    def up(self, db_manager) -> bool:
        """Add cap_percent column to matches table"""
        try:
            # Check if cap_percent column already exists
            columns = get_table_columns(db_manager, 'matches')
            with db_manager.engine.connect() as conn:

                if 'cap_percent' not in columns:
                    # Add cap_percent column with default value 70.0
                    conn.execute(text("""
                        ALTER TABLE matches
                        ADD COLUMN cap_percent REAL DEFAULT 70.0 NOT NULL
                    """))

                    conn.commit()

                    logger.info("cap_percent column added to matches table")
                else:
                    logger.info("cap_percent column already exists in matches table")

            return True
        except Exception as e:
            logger.error(f"Failed to add cap_percent field to matches: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove cap_percent column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - cap_percent column will remain")
        return True


class Migration_036_AddMatchTemplatesTables(DatabaseMigration):
    """Add matches_templates and match_outcomes_templates tables for storing match templates"""

    def __init__(self):
        super().__init__("036", "Add matches_templates and match_outcomes_templates tables for storing match templates")

    def up(self, db_manager) -> bool:
        """Create matches_templates and match_outcomes_templates tables"""
        try:
            pk_sql = get_primary_key_sql(db_manager)
            with db_manager.engine.connect() as conn:
                # Create matches_templates table (identical structure to matches table)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS matches_templates (
                        {pk_sql},
                        match_number INTEGER NOT NULL,
                        fighter1_township VARCHAR(255) NOT NULL,
                        fighter2_township VARCHAR(255) NOT NULL,
                        venue_kampala_township VARCHAR(255) NOT NULL,

                        start_time DATETIME NULL,
                        end_time DATETIME NULL,
                        result VARCHAR(255) NULL,
                        winning_outcomes TEXT NULL,
                        under_over_result VARCHAR(50) NULL,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE NOT NULL,
                        status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                        fixture_active_time INTEGER NULL,

                        filename VARCHAR(1024) NOT NULL,
                        file_sha1sum VARCHAR(255) NOT NULL,
                        fixture_id VARCHAR(255) NOT NULL,
                        active_status BOOLEAN DEFAULT FALSE,

                        zip_filename VARCHAR(1024) NULL,
                        zip_sha1sum VARCHAR(255) NULL,
                        zip_upload_status VARCHAR(20) DEFAULT 'pending',
                        zip_upload_progress REAL DEFAULT 0.0,
                        zip_validation_status VARCHAR(20) DEFAULT 'pending',

                        created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

                        UNIQUE(fixture_id, match_number)
                    )
                """))

                # Create match_outcomes_templates table (identical structure to match_outcomes table)
                conn.execute(text(f"""
                    CREATE TABLE IF NOT EXISTS match_outcomes_templates (
                        {pk_sql},
                        match_id INTEGER NOT NULL REFERENCES matches_templates(id) ON DELETE CASCADE,
                        column_name VARCHAR(255) NOT NULL,
                        float_value REAL NOT NULL,

                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

                        UNIQUE(match_id, column_name)
                    )
                """))

                # Create indexes for matches_templates table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_match_number ON matches_templates(match_number)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_fixture_id ON matches_templates(fixture_id)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_active_status ON matches_templates(active_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_file_sha1sum ON matches_templates(file_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_zip_sha1sum ON matches_templates(zip_sha1sum)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_zip_upload_status ON matches_templates(zip_upload_status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_created_by ON matches_templates(created_by)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_fixture_active_time ON matches_templates(fixture_active_time)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_done ON matches_templates(done)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_running ON matches_templates(running)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_status ON matches_templates(status)",
                    "CREATE INDEX IF NOT EXISTS ix_matches_templates_composite ON matches_templates(active_status, zip_upload_status, created_at)",
                ]

                # Create indexes for match_outcomes_templates table
                indexes.extend([
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_templates_match_id ON match_outcomes_templates(match_id)",
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_templates_column_name ON match_outcomes_templates(column_name)",
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_templates_float_value ON match_outcomes_templates(float_value)",
                    "CREATE INDEX IF NOT EXISTS ix_match_outcomes_templates_composite ON match_outcomes_templates(match_id, column_name)",
                ])

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Matches templates and match outcomes templates tables created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create match templates tables: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop matches_templates and match_outcomes_templates tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Drop tables in reverse order (child first due to foreign keys)
                conn.execute(text("DROP TABLE IF EXISTS match_outcomes_templates"))
                conn.execute(text("DROP TABLE IF EXISTS matches_templates"))

                conn.commit()

            logger.info("Matches templates and match outcomes templates tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop match templates tables: {e}")
            return False


class Migration_042_AddOAuthAndEmailSettings(DatabaseMigration):
    """Add OAuth and email settings configuration to game_config table and add email verification columns to users table"""

    def __init__(self):
        super().__init__("042", "Add OAuth and email settings configuration and email verification columns")

    def up(self, db_manager) -> bool:
        """Add OAuth and email settings to game_config table and add email verification columns to users table"""
        try:
            # First, add email verification columns to users table
            columns = get_table_columns(db_manager, 'users')
            with db_manager.engine.connect() as conn:
                
                if 'email_verified' not in columns:
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN email_verified BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    logger.info("email_verified column added to users table")
                
                if 'email_verified_at' not in columns:
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN email_verified_at DATETIME NULL
                    """))
                    logger.info("email_verified_at column added to users table")
                
                # OAuth settings (stored as JSON in game_config)
                oauth_settings = {
                    'google': {
                        'enabled': True,
                        'client_id': '',
                        'client_secret': ''
                    },
                    'facebook': {
                        'enabled': True,
                        'client_id': '',
                        'client_secret': ''
                    },
                    'discord': {
                        'enabled': True,
                        'client_id': '',
                        'client_secret': ''
                    }
                }
                
                import json
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                conn.execute(text(f"""
                    {insert_ignore} INTO game_config
                    (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                    VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                """), {
                    'config_key': 'oauth_settings',
                    'config_value': json.dumps(oauth_settings),
                    'value_type': 'json',
                    'description': 'OAuth provider settings (Google, Facebook, Discord)',
                    'is_system': False
                })
                
                # Email settings (stored as JSON in game_config)
                email_settings = {
                    'enabled': False,
                    'smtp_host': '',
                    'smtp_port': 587,
                    'smtp_username': '',
                    'smtp_password': '',
                    'smtp_from_email': '',
                    'smtp_from_name': 'MbetterClient',
                    'smtp_use_tls': 'tls',
                    'app_domain': ''
                }
                
                conn.execute(text(f"""
                    {insert_ignore} INTO game_config
                    (config_key, config_value, value_type, description, is_system, created_at, updated_at)
                    VALUES (:config_key, :config_value, :value_type, :description, :is_system, {timestamp_sql}, {timestamp_sql})
                """), {
                    'config_key': 'email_settings',
                    'config_value': json.dumps(email_settings),
                    'value_type': 'json',
                    'description': 'Email/SMTP settings for user verification and password reset',
                    'is_system': False
                })
                
                conn.commit()
                
            logger.info("OAuth and email settings configuration added successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add OAuth and email settings: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove OAuth and email settings from game_config table and email verification columns from users table"""
        try:
            with db_manager.engine.connect() as conn:
                # Remove OAuth and email settings
                conn.execute(text("DELETE FROM game_config WHERE config_key = 'oauth_settings'"))
                conn.execute(text("DELETE FROM game_config WHERE config_key = 'email_settings'"))
                
                # Note: SQLite doesn't support DROP COLUMN, so email_verified and email_verified_at columns will remain
                logger.warning("SQLite doesn't support DROP COLUMN - email_verified and email_verified_at columns will remain")
                
                conn.commit()
                
            logger.info("OAuth and email settings configuration removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove OAuth and email settings: {e}")
            return False


class Migration_043_AddBetOriginTracking(DatabaseMigration):
    """Add bet origin tracking fields to bets table for tracking which bet was inserted by which user/API key"""

    def __init__(self):
        super().__init__("043", "Add bet origin tracking fields to bets table")

    def up(self, db_manager) -> bool:
        """Add bet origin tracking columns to bets table"""
        try:
            dialect = db_manager.engine.dialect.name
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'bets')
            with db_manager.engine.connect() as conn:
                
                if 'created_by_user_id' not in columns:
                    # Add created_by_user_id column
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE bets
                            ADD COLUMN created_by_user_id INT NULL
                        """))
                        # Add foreign key constraint for MySQL
                        conn.execute(text("""
                            ALTER TABLE bets
                            ADD CONSTRAINT fk_bets_created_by_user
                            FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
                        """))
                    else:
                        conn.execute(text("""
                            ALTER TABLE bets
                            ADD COLUMN created_by_user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL
                        """))
                    logger.info("created_by_user_id column added to bets table")
                else:
                    logger.info("created_by_user_id column already exists in bets table")

                if 'created_by_api_key_id' not in columns:
                    # Add created_by_api_key_id column
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE bets
                            ADD COLUMN created_by_api_key_id INT NULL
                        """))
                        # Add foreign key constraint for MySQL
                        conn.execute(text("""
                            ALTER TABLE bets
                            ADD CONSTRAINT fk_bets_created_by_api_key
                            FOREIGN KEY (created_by_api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL
                        """))
                    else:
                        conn.execute(text("""
                            ALTER TABLE bets
                            ADD COLUMN created_by_api_key_id INTEGER NULL REFERENCES api_keys(id) ON DELETE SET NULL
                        """))
                    logger.info("created_by_api_key_id column added to bets table")
                else:
                    logger.info("created_by_api_key_id column already exists in bets table")

                if 'created_by_role' not in columns:
                    # Add created_by_role column
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN created_by_role VARCHAR(50) NULL
                    """))
                    logger.info("created_by_role column added to bets table")
                else:
                    logger.info("created_by_role column already exists in bets table")

                if 'created_via' not in columns:
                    # Add created_via column
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN created_via VARCHAR(50) NULL
                    """))
                    logger.info("created_via column added to bets table")
                else:
                    logger.info("created_via column already exists in bets table")

                if 'client_ip' not in columns:
                    # Add client_ip column
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN client_ip VARCHAR(45) NULL
                    """))
                    logger.info("client_ip column added to bets table")
                else:
                    logger.info("client_ip column already exists in bets table")

                if 'user_agent' not in columns:
                    # Add user_agent column
                    conn.execute(text("""
                        ALTER TABLE bets
                        ADD COLUMN user_agent TEXT NULL
                    """))
                    logger.info("user_agent column added to bets table")
                else:
                    logger.info("user_agent column already exists in bets table")

                # Add indexes for efficient origin tracking queries
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_bets_created_by_user_id ON bets(created_by_user_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_created_by_api_key_id ON bets(created_by_api_key_id)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_created_by_role ON bets(created_by_role)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_created_via ON bets(created_via)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_client_ip ON bets(client_ip)",
                    "CREATE INDEX IF NOT EXISTS ix_bets_origin_composite ON bets(created_by_user_id, created_by_api_key_id, created_via)",
                ]

                for index_sql in indexes:
                    conn.execute(text(index_sql))

                conn.commit()

            logger.info("Bet origin tracking fields added to bets table successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add bet origin tracking fields to bets table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove bet origin tracking columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - bet origin tracking columns will remain")
        return True


class Migration_044_CreateOAuthProvidersTable(DatabaseMigration):
    """Create oauth_providers table for storing OAuth provider configurations"""

    def __init__(self):
        super().__init__("044", "Create oauth_providers table for OAuth provider configurations")

    def up(self, db_manager) -> bool:
        """Create oauth_providers table with default providers"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Create oauth_providers table
                if dialect == 'sqlite':
                    providers_sql = f"""
                        CREATE TABLE IF NOT EXISTS oauth_providers (
                            {pk_sql},
                            provider_name VARCHAR(50) NOT NULL UNIQUE,
                            display_name VARCHAR(100) NOT NULL,
                            provider_type VARCHAR(50) NOT NULL DEFAULT 'oauth2',
                            client_id VARCHAR(255) NOT NULL,
                            client_secret VARCHAR(255) NOT NULL,
                            authorization_url VARCHAR(500) NOT NULL,
                            token_url VARCHAR(500) NOT NULL,
                            user_info_url VARCHAR(500) NOT NULL,
                            scopes TEXT NOT NULL,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    providers_sql = f"""
                        CREATE TABLE IF NOT EXISTS oauth_providers (
                            {pk_sql},
                            provider_name VARCHAR(50) NOT NULL UNIQUE,
                            display_name VARCHAR(100) NOT NULL,
                            provider_type VARCHAR(50) NOT NULL DEFAULT 'oauth2',
                            client_id VARCHAR(255) NOT NULL,
                            client_secret VARCHAR(255) NOT NULL,
                            authorization_url VARCHAR(500) NOT NULL,
                            token_url VARCHAR(500) NOT NULL,
                            user_info_url VARCHAR(500) NOT NULL,
                            scopes TEXT NOT NULL,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                else:
                    providers_sql = f"""
                        CREATE TABLE IF NOT EXISTS oauth_providers (
                            {pk_sql},
                            provider_name VARCHAR(50) NOT NULL UNIQUE,
                            display_name VARCHAR(100) NOT NULL,
                            provider_type VARCHAR(50) NOT NULL DEFAULT 'oauth2',
                            client_id VARCHAR(255) NOT NULL,
                            client_secret VARCHAR(255) NOT NULL,
                            authorization_url VARCHAR(500) NOT NULL,
                            token_url VARCHAR(500) NOT NULL,
                            user_info_url VARCHAR(500) NOT NULL,
                            scopes TEXT NOT NULL,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                
                conn.execute(text(providers_sql))
                
                # Create indexes for oauth_providers table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_oauth_providers_provider_name ON oauth_providers(provider_name)",
                    "CREATE INDEX IF NOT EXISTS ix_oauth_providers_is_active ON oauth_providers(is_active)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                # Insert default OAuth providers (Google, Facebook, Discord, GitHub)
                # Note: client_id and client_secret are empty placeholders - must be configured in admin dashboard
                default_providers = [
                    {
                        'provider_name': 'google',
                        'display_name': 'Google',
                        'provider_type': 'oauth2',
                        'client_id': '',
                        'client_secret': '',
                        'authorization_url': 'https://accounts.google.com/o/oauth2/v2/auth',
                        'token_url': 'https://oauth2.googleapis.com/token',
                        'user_info_url': 'https://www.googleapis.com/oauth2/v2/userinfo',
                        'scopes': 'openid email profile'
                    },
                    {
                        'provider_name': 'facebook',
                        'display_name': 'Facebook',
                        'provider_type': 'oauth2',
                        'client_id': '',
                        'client_secret': '',
                        'authorization_url': 'https://www.facebook.com/v18.0/dialog/oauth',
                        'token_url': 'https://graph.facebook.com/v18.0/oauth/access_token',
                        'user_info_url': 'https://graph.facebook.com/v18.0/me?fields=id,name,email',
                        'scopes': 'email public_profile'
                    },
                    {
                        'provider_name': 'discord',
                        'display_name': 'Discord',
                        'provider_type': 'oauth2',
                        'client_id': '',
                        'client_secret': '',
                        'authorization_url': 'https://discord.com/api/oauth2/authorize',
                        'token_url': 'https://discord.com/api/oauth2/token',
                        'user_info_url': 'https://discord.com/api/users/@me',
                        'scopes': 'identify email'
                    },
                    {
                        'provider_name': 'github',
                        'display_name': 'GitHub',
                        'provider_type': 'oauth2',
                        'client_id': '',
                        'client_secret': '',
                        'authorization_url': 'https://github.com/login/oauth/authorize',
                        'token_url': 'https://github.com/login/oauth/access_token',
                        'user_info_url': 'https://api.github.com/user',
                        'scopes': 'user:email'
                    }
                ]
                
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                for provider in default_providers:
                    conn.execute(text(f"""
                        {insert_ignore} INTO oauth_providers
                        (provider_name, display_name, provider_type, client_id, client_secret,
                         authorization_url, token_url, user_info_url, scopes, is_active, created_at, updated_at)
                        VALUES (:provider_name, :display_name, :provider_type, :client_id, :client_secret,
                                :authorization_url, :token_url, :user_info_url, :scopes, 1, {timestamp_sql}, {timestamp_sql})
                    """), provider)
                
                conn.commit()
                
            logger.info("OAuth providers table created with default providers (Google, Facebook, Discord, GitHub)")
            return True
        except Exception as e:
            logger.error(f"Failed to create oauth_providers table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop oauth_providers table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS oauth_providers"))
                conn.commit()
                
            logger.info("OAuth providers table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop oauth_providers table: {e}")
            return False


class Migration_045_CreateUserOAuthAccountsTable(DatabaseMigration):
    """Create user_oauth_accounts table for linking users to OAuth provider accounts"""

    def __init__(self):
        super().__init__("045", "Create user_oauth_accounts table for linking users to OAuth provider accounts")

    def up(self, db_manager) -> bool:
        """Create user_oauth_accounts table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Create user_oauth_accounts table
                if dialect == 'sqlite':
                    accounts_sql = f"""
                        CREATE TABLE IF NOT EXISTS user_oauth_accounts (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            provider_id INTEGER NOT NULL REFERENCES oauth_providers(id) ON DELETE CASCADE,
                            provider_user_id VARCHAR(255) NOT NULL,
                            provider_email VARCHAR(255),
                            provider_name VARCHAR(255),
                            access_token TEXT NOT NULL,
                            refresh_token TEXT,
                            token_expires_at DATETIME,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            last_used_at DATETIME,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            UNIQUE(user_id, provider_id, provider_user_id)
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    accounts_sql = f"""
                        CREATE TABLE IF NOT EXISTS user_oauth_accounts (
                            {pk_sql},
                            user_id INT NOT NULL,
                            provider_id INT NOT NULL,
                            provider_user_id VARCHAR(255) NOT NULL,
                            provider_email VARCHAR(255),
                            provider_name VARCHAR(255),
                            access_token TEXT NOT NULL,
                            refresh_token TEXT,
                            token_expires_at DATETIME,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            last_used_at DATETIME,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            UNIQUE(user_id, provider_id, provider_user_id),
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                            FOREIGN KEY (provider_id) REFERENCES oauth_providers(id) ON DELETE CASCADE
                        )
                    """
                else:
                    accounts_sql = f"""
                        CREATE TABLE IF NOT EXISTS user_oauth_accounts (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            provider_id INTEGER NOT NULL REFERENCES oauth_providers(id) ON DELETE CASCADE,
                            provider_user_id VARCHAR(255) NOT NULL,
                            provider_email VARCHAR(255),
                            provider_name VARCHAR(255),
                            access_token TEXT NOT NULL,
                            refresh_token TEXT,
                            token_expires_at DATETIME,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            last_used_at DATETIME,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            UNIQUE(user_id, provider_id, provider_user_id)
                        )
                    """
                
                conn.execute(text(accounts_sql))
                
                # Create indexes for user_oauth_accounts table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_user_oauth_accounts_user_id ON user_oauth_accounts(user_id)",
                    "CREATE INDEX IF NOT EXISTS ix_user_oauth_accounts_provider_id ON user_oauth_accounts(provider_id)",
                    "CREATE INDEX IF NOT EXISTS ix_user_oauth_accounts_provider_user_id ON user_oauth_accounts(provider_user_id)",
                    "CREATE INDEX IF NOT EXISTS ix_user_oauth_accounts_is_active ON user_oauth_accounts(is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_user_oauth_accounts_composite ON user_oauth_accounts(user_id, provider_id)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("User OAuth accounts table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create user_oauth_accounts table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop user_oauth_accounts table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS user_oauth_accounts"))
                conn.commit()
                
            logger.info("User OAuth accounts table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop user_oauth_accounts table: {e}")
            return False


class Migration_046_CreateCurrenciesTable(DatabaseMigration):
    """Create currencies table with support for fiat and cryptocurrencies"""

    def __init__(self):
        super().__init__("046", "Create currencies table with support for fiat and cryptocurrencies")

    def up(self, db_manager) -> bool:
        """Create currencies table with default currencies"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if currencies table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='currencies'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'currencies'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'currencies' in inspector.get_table_names()
                
                # If table exists, check if it has the required columns
                if table_exists:
                    existing_columns = get_table_columns(db_manager, 'currencies')
                    logger.info(f"Currencies table already exists with columns: {existing_columns}")
                    
                    # Add missing columns if needed
                    if 'is_crypto' not in existing_columns:
                        logger.info("Adding missing is_crypto column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN is_crypto BOOLEAN DEFAULT FALSE NOT NULL
                        """))
                    
                    if 'currency_type' not in existing_columns:
                        logger.info("Adding missing currency_type column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN currency_type VARCHAR(20) NOT NULL DEFAULT 'fiat'
                        """))
                    
                    if 'decimal_places' not in existing_columns:
                        logger.info("Adding missing decimal_places column to currencies table")
                        if dialect in ('mysql', 'mariadb'):
                            conn.execute(text("""
                                ALTER TABLE currencies
                                ADD COLUMN decimal_places INT DEFAULT 2 NOT NULL
                            """))
                        else:
                            conn.execute(text("""
                                ALTER TABLE currencies
                                ADD COLUMN decimal_places INTEGER DEFAULT 2 NOT NULL
                            """))
                    
                    if 'min_deposit' not in existing_columns:
                        logger.info("Adding missing min_deposit column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN min_deposit REAL DEFAULT 0.0 NOT NULL
                        """))
                    
                    if 'max_deposit' not in existing_columns:
                        logger.info("Adding missing max_deposit column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN max_deposit REAL DEFAULT 1000000.0 NOT NULL
                        """))
                    
                    if 'min_withdrawal' not in existing_columns:
                        logger.info("Adding missing min_withdrawal column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN min_withdrawal REAL DEFAULT 0.0 NOT NULL
                        """))
                    
                    if 'max_withdrawal' not in existing_columns:
                        logger.info("Adding missing max_withdrawal column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN max_withdrawal REAL DEFAULT 1000000.0 NOT NULL
                        """))
                    
                    if 'networks' not in existing_columns:
                        logger.info("Adding missing networks column to currencies table")
                        conn.execute(text("""
                            ALTER TABLE currencies
                            ADD COLUMN networks TEXT
                        """))
                else:
                    # Create currencies table from scratch
                    if dialect == 'sqlite':
                        currencies_sql = f"""
                            CREATE TABLE IF NOT EXISTS currencies (
                                {pk_sql},
                                code VARCHAR(10) NOT NULL UNIQUE,
                                name VARCHAR(100) NOT NULL,
                                symbol VARCHAR(10) NOT NULL,
                                currency_type VARCHAR(20) NOT NULL DEFAULT 'fiat',
                                is_crypto BOOLEAN DEFAULT FALSE NOT NULL,
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                decimal_places INT DEFAULT 2 NOT NULL,
                                min_deposit REAL DEFAULT 0.0 NOT NULL,
                                max_deposit REAL DEFAULT 1000000.0 NOT NULL,
                                min_withdrawal REAL DEFAULT 0.0 NOT NULL,
                                max_withdrawal REAL DEFAULT 1000000.0 NOT NULL,
                                networks TEXT,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        currencies_sql = f"""
                            CREATE TABLE IF NOT EXISTS currencies (
                                {pk_sql},
                                code VARCHAR(10) NOT NULL UNIQUE,
                                name VARCHAR(100) NOT NULL,
                                symbol VARCHAR(10) NOT NULL,
                                currency_type VARCHAR(20) NOT NULL DEFAULT 'fiat',
                                is_crypto BOOLEAN DEFAULT FALSE NOT NULL,
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                decimal_places INT DEFAULT 2 NOT NULL,
                                min_deposit REAL DEFAULT 0.0 NOT NULL,
                                max_deposit REAL DEFAULT 1000000.0 NOT NULL,
                                min_withdrawal REAL DEFAULT 0.0 NOT NULL,
                                max_withdrawal REAL DEFAULT 1000000.0 NOT NULL,
                                networks TEXT,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    else:
                        currencies_sql = f"""
                            CREATE TABLE IF NOT EXISTS currencies (
                                {pk_sql},
                                code VARCHAR(10) NOT NULL UNIQUE,
                                name VARCHAR(100) NOT NULL,
                                symbol VARCHAR(10) NOT NULL,
                                currency_type VARCHAR(20) NOT NULL DEFAULT 'fiat',
                                is_crypto BOOLEAN DEFAULT FALSE NOT NULL,
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                decimal_places INTEGER DEFAULT 2 NOT NULL,
                                min_deposit REAL DEFAULT 0.0 NOT NULL,
                                max_deposit REAL DEFAULT 1000000.0 NOT NULL,
                                min_withdrawal REAL DEFAULT 0.0 NOT NULL,
                                max_withdrawal REAL DEFAULT 1000000.0 NOT NULL,
                                networks TEXT,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(currencies_sql))
                
                # Create indexes for currencies table (only if columns exist)
                # Check if is_crypto column exists before creating index on it
                current_columns = get_table_columns(db_manager, 'currencies')
                
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_currencies_code ON currencies(code)",
                    "CREATE INDEX IF NOT EXISTS ix_currencies_currency_type ON currencies(currency_type)",
                    "CREATE INDEX IF NOT EXISTS ix_currencies_is_active ON currencies(is_active)",
                ]
                
                # Only add is_crypto index if the column exists
                if 'is_crypto' in current_columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_currencies_is_crypto ON currencies(is_crypto)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_e:
                        logger.warning(f"Could not create index (may already exist): {idx_e}")
                
                # Insert default fiat currencies
                import json
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                
                fiat_currencies = [
                    ('USD', 'US Dollar', '$', 'fiat', False, 2, 10.0, 1000000.0, 10.0, 1000000.0, None),
                    ('EUR', 'Euro', '€', 'fiat', False, 2, 10.0, 1000000.0, 10.0, 1000000.0, None),
                    ('GBP', 'British Pound', '£', 'fiat', False, 2, 10.0, 1000000.0, 10.0, 1000000.0, None),
                    ('UGX', 'Ugandan Shilling', 'USh', 'fiat', False, 0, 1000.0, 100000000.0, 1000.0, 100000000.0, None),
                    ('KES', 'Kenyan Shilling', 'KSh', 'fiat', False, 2, 100.0, 100000000.0, 100.0, 100000000.0, None),
                    ('TZS', 'Tanzanian Shilling', 'TSh', 'fiat', False, 2, 100.0, 100000000.0, 100.0, 100000000.0, None),
                    ('RWF', 'Rwandan Franc', 'FRw', 'fiat', False, 0, 100.0, 100000000.0, 100.0, 100000000.0, None),
                    ('ZAR', 'South African Rand', 'R', 'fiat', False, 2, 10.0, 1000000.0, 10.0, 1000000.0, None),
                ]
                
                for currency in fiat_currencies:
                    conn.execute(text(f"""
                        {insert_ignore} INTO currencies
                        (code, name, symbol, currency_type, is_crypto, is_active, decimal_places,
                         min_deposit, max_deposit, min_withdrawal, max_withdrawal, networks, created_at, updated_at)
                        VALUES (:code, :name, :symbol, :currency_type, :is_crypto, 1, :decimal_places,
                                :min_deposit, :max_deposit, :min_withdrawal, :max_withdrawal, :networks,
                                {timestamp_sql}, {timestamp_sql})
                    """), {
                        'code': currency[0],
                        'name': currency[1],
                        'symbol': currency[2],
                        'currency_type': currency[3],
                        'is_crypto': currency[4],
                        'decimal_places': currency[5],
                        'min_deposit': currency[6],
                        'max_deposit': currency[7],
                        'min_withdrawal': currency[8],
                        'max_withdrawal': currency[9],
                        'networks': currency[10]
                    })
                
                # Insert default cryptocurrencies with networks
                crypto_currencies = [
                    ('BTC', 'Bitcoin', '₿', 'crypto', True, 8, 0.0001, 100.0, 0.0001, 100.0,
                     json.dumps(['BTC', 'ERC20'])),
                    ('ETH', 'Ethereum', 'Ξ', 'crypto', True, 18, 0.001, 1000.0, 0.001, 1000.0,
                     json.dumps(['ETH', 'ERC20'])),
                    ('USDT', 'Tether', '₮', 'crypto', True, 6, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['TRC20', 'ERC20', 'BSC', 'SOL', 'POLYGON', 'AVAXC', 'ARBITRUM', 'OPTIMISM'])),
                    ('USDC', 'USD Coin', '$', 'crypto', True, 6, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['ERC20', 'SOL', 'POLYGON', 'AVAXC', 'ARBITRUM', 'OPTIMISM'])),
                    ('BNB', 'Binance Coin', 'BNB', 'crypto', True, 18, 0.001, 10000.0, 0.001, 10000.0,
                     json.dumps(['BSC', 'ERC20'])),
                    ('SOL', 'Solana', 'SOL', 'crypto', True, 9, 0.01, 10000.0, 0.01, 10000.0,
                     json.dumps(['SOL'])),
                    ('DOGE', 'Dogecoin', 'Ð', 'crypto', True, 8, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['DOGE'])),
                    ('LTC', 'Litecoin', 'Ł', 'crypto', True, 8, 0.01, 10000.0, 0.01, 10000.0,
                     json.dumps(['LTC'])),
                    ('XRP', 'Ripple', 'XRP', 'crypto', True, 6, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['XRP'])),
                    ('ADA', 'Cardano', '₳', 'crypto', True, 6, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['ADA'])),
                    ('TRX', 'TRON', 'TRX', 'crypto', True, 6, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['TRC20'])),
                    ('MATIC', 'Polygon', 'MATIC', 'crypto', True, 18, 1.0, 1000000.0, 1.0, 1000000.0,
                     json.dumps(['POLYGON', 'ERC20'])),
                    ('AVAX', 'Avalanche', 'AVAX', 'crypto', True, 18, 0.01, 10000.0, 0.01, 10000.0,
                     json.dumps(['AVAXC', 'ERC20'])),
                    ('DOT', 'Polkadot', 'DOT', 'crypto', True, 10, 0.1, 100000.0, 0.1, 100000.0,
                     json.dumps(['DOT'])),
                    ('SHIB', 'Shiba Inu', 'SHIB', 'crypto', True, 18, 1000.0, 1000000000000.0, 1000.0, 1000000000000.0,
                     json.dumps(['ERC20', 'BSC'])),
                ]
                
                for currency in crypto_currencies:
                    conn.execute(text(f"""
                        {insert_ignore} INTO currencies
                        (code, name, symbol, currency_type, is_crypto, is_active, decimal_places,
                         min_deposit, max_deposit, min_withdrawal, max_withdrawal, networks, created_at, updated_at)
                        VALUES (:code, :name, :symbol, :currency_type, :is_crypto, 1, :decimal_places,
                                :min_deposit, :max_deposit, :min_withdrawal, :max_withdrawal, :networks,
                                {timestamp_sql}, {timestamp_sql})
                    """), {
                        'code': currency[0],
                        'name': currency[1],
                        'symbol': currency[2],
                        'currency_type': currency[3],
                        'is_crypto': currency[4],
                        'decimal_places': currency[5],
                        'min_deposit': currency[6],
                        'max_deposit': currency[7],
                        'min_withdrawal': currency[8],
                        'max_withdrawal': currency[9],
                        'networks': currency[10]
                    })
                
                conn.commit()
                
            logger.info("Currencies table created with default fiat and cryptocurrencies")
            return True
        except Exception as e:
            logger.error(f"Failed to create currencies table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop currencies table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS currencies"))
                conn.commit()
                
            logger.info("Currencies table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop currencies table: {e}")
            return False


class Migration_047_CreateExchangeRateHistoryTable(DatabaseMigration):
    """Create exchange_rate_history table for storing historical exchange rates"""

    def __init__(self):
        super().__init__("047", "Create exchange_rate_history table for storing historical exchange rates")

    def up(self, db_manager) -> bool:
        """Create exchange_rate_history table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='exchange_rate_history'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'exchange_rate_history'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'exchange_rate_history' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("exchange_rate_history table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'exchange_rate_history')
                    logger.info(f"Existing columns in exchange_rate_history: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'from_currency': 'VARCHAR(10) NOT NULL',
                        'to_currency': 'VARCHAR(10) NOT NULL',
                        'rate': 'REAL NOT NULL',
                        'provider': 'VARCHAR(50) NOT NULL',
                        'recorded_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to exchange_rate_history table")
                            conn.execute(text(f"ALTER TABLE exchange_rate_history ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        rates_sql = f"""
                            CREATE TABLE exchange_rate_history (
                                {pk_sql},
                                from_currency VARCHAR(10) NOT NULL,
                                to_currency VARCHAR(10) NOT NULL,
                                rate REAL NOT NULL,
                                provider VARCHAR(50) NOT NULL,
                                recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        rates_sql = f"""
                            CREATE TABLE exchange_rate_history (
                                {pk_sql},
                                from_currency VARCHAR(10) NOT NULL,
                                to_currency VARCHAR(10) NOT NULL,
                                rate REAL NOT NULL,
                                provider VARCHAR(50) NOT NULL,
                                recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    else:
                        rates_sql = f"""
                            CREATE TABLE exchange_rate_history (
                                {pk_sql},
                                from_currency VARCHAR(10) NOT NULL,
                                to_currency VARCHAR(10) NOT NULL,
                                rate REAL NOT NULL,
                                provider VARCHAR(50) NOT NULL,
                                recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(rates_sql))
                
                # Create indexes for exchange_rate_history table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'exchange_rate_history')
                indexes = []
                
                if 'from_currency' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_history_from_currency ON exchange_rate_history(from_currency)")
                if 'to_currency' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_history_to_currency ON exchange_rate_history(to_currency)")
                if 'provider' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_history_provider ON exchange_rate_history(provider)")
                if 'recorded_at' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_history_recorded_at ON exchange_rate_history(recorded_at)")
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_history_composite ON exchange_rate_history(from_currency, to_currency, recorded_at)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                conn.commit()
                
            logger.info("Exchange rate history table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create exchange_rate_history table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop exchange_rate_history table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS exchange_rate_history"))
                conn.commit()
                
            logger.info("Exchange rate history table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop exchange_rate_history table: {e}")
            return False


class Migration_048_CreateExchangeRateProvidersTable(DatabaseMigration):
    """Create exchange_rate_providers table for storing exchange rate API provider configurations"""

    def __init__(self):
        super().__init__("048", "Create exchange_rate_providers table for exchange rate API provider configurations")

    def up(self, db_manager) -> bool:
        """Create exchange_rate_providers table with default providers"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='exchange_rate_providers'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'exchange_rate_providers'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'exchange_rate_providers' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("exchange_rate_providers table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'exchange_rate_providers')
                    logger.info(f"Existing columns in exchange_rate_providers: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'provider_name': 'VARCHAR(50) NOT NULL UNIQUE',
                        'display_name': 'VARCHAR(100) NOT NULL',
                        'api_url': 'VARCHAR(500) NOT NULL',
                        'api_key': 'VARCHAR(255)',
                        'is_active': 'BOOLEAN DEFAULT TRUE NOT NULL',
                        'priority': 'INT DEFAULT 0 NOT NULL',
                        'rate_limit_per_hour': 'INT DEFAULT 100 NOT NULL',
                        'last_successful_fetch': 'DATETIME',
                        'last_failed_fetch': 'DATETIME',
                        'failure_count': 'INT DEFAULT 0 NOT NULL',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to exchange_rate_providers table")
                            conn.execute(text(f"ALTER TABLE exchange_rate_providers ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        providers_sql = f"""
                            CREATE TABLE exchange_rate_providers (
                                {pk_sql},
                                provider_name VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                api_url VARCHAR(500) NOT NULL,
                                api_key VARCHAR(255),
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                priority INT DEFAULT 0 NOT NULL,
                                rate_limit_per_hour INT DEFAULT 100 NOT NULL,
                                last_successful_fetch DATETIME,
                                last_failed_fetch DATETIME,
                                failure_count INT DEFAULT 0 NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        providers_sql = f"""
                            CREATE TABLE exchange_rate_providers (
                                {pk_sql},
                                provider_name VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                api_url VARCHAR(500) NOT NULL,
                                api_key VARCHAR(255),
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                priority INT DEFAULT 0 NOT NULL,
                                rate_limit_per_hour INT DEFAULT 100 NOT NULL,
                                last_successful_fetch DATETIME,
                                last_failed_fetch DATETIME,
                                failure_count INT DEFAULT 0 NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    else:
                        providers_sql = f"""
                            CREATE TABLE exchange_rate_providers (
                                {pk_sql},
                                provider_name VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                api_url VARCHAR(500) NOT NULL,
                                api_key VARCHAR(255),
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                priority INTEGER DEFAULT 0 NOT NULL,
                                rate_limit_per_hour INTEGER DEFAULT 100 NOT NULL,
                                last_successful_fetch DATETIME,
                                last_failed_fetch DATETIME,
                                failure_count INTEGER DEFAULT 0 NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(providers_sql))
                
                # Create indexes for exchange_rate_providers table
                columns = get_table_columns(db_manager, 'exchange_rate_providers')
                indexes = []
                
                if 'provider_name' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_providers_provider_name ON exchange_rate_providers(provider_name)")
                if 'is_active' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_providers_is_active ON exchange_rate_providers(is_active)")
                if 'priority' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_exchange_rate_providers_priority ON exchange_rate_providers(priority)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                # Insert default exchange rate providers (only if they don't exist)
                # Note: api_key is empty placeholder - must be configured in admin dashboard
                default_providers = [
                    {
                        'provider_name': 'fixer',
                        'display_name': 'Fixer.io',
                        'api_url': 'http://data.fixer.io/api/latest',
                        'api_key': '',
                        'priority': 1,
                        'rate_limit_per_hour': 100
                    },
                    {
                        'provider_name': 'exchangerate_api',
                        'display_name': 'ExchangeRate-API',
                        'api_url': 'https://v6.exchangerate-api.com/v6/latest',
                        'api_key': '',
                        'priority': 2,
                        'rate_limit_per_hour': 1500
                    },
                    {
                        'provider_name': 'openexchangerates',
                        'display_name': 'Open Exchange Rates',
                        'api_url': 'https://openexchangerates.org/api/latest.json',
                        'api_key': '',
                        'priority': 3,
                        'rate_limit_per_hour': 1000
                    },
                    {
                        'provider_name': 'coingecko',
                        'display_name': 'CoinGecko',
                        'api_url': 'https://api.coingecko.com/api/v3/simple/price',
                        'api_key': '',
                        'priority': 4,
                        'rate_limit_per_hour': 50
                    },
                    {
                        'provider_name': 'coinapi',
                        'display_name': 'CoinAPI',
                        'api_url': 'https://rest.coinapi.io/v1/exchangerate',
                        'api_key': '',
                        'priority': 5,
                        'rate_limit_per_hour': 100
                    }
                ]
                
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                
                # Only insert if we have all required columns
                required_cols = ['provider_name', 'display_name', 'api_url', 'api_key', 'is_active', 'priority', 'rate_limit_per_hour', 'failure_count', 'created_at', 'updated_at']
                if all(col in columns for col in required_cols):
                    for provider in default_providers:
                        conn.execute(text(f"""
                            {insert_ignore} INTO exchange_rate_providers
                            (provider_name, display_name, api_url, api_key, is_active, priority, rate_limit_per_hour,
                             failure_count, created_at, updated_at)
                            VALUES (:provider_name, :display_name, :api_url, :api_key, 1, :priority, :rate_limit_per_hour,
                                    0, {timestamp_sql}, {timestamp_sql})
                        """), provider)
                else:
                    logger.warning(f"Skipping default provider insertion - missing required columns. Have: {columns}, Need: {required_cols}")
                
                conn.commit()
                
            logger.info("Exchange rate providers table created with default providers (Fixer, ExchangeRate-API, Open Exchange Rates, CoinGecko, CoinAPI)")
            return True
        except Exception as e:
            logger.error(f"Failed to create exchange_rate_providers table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop exchange_rate_providers table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS exchange_rate_providers"))
                conn.commit()
                
            logger.info("Exchange rate providers table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop exchange_rate_providers table: {e}")
            return False

class Migration_049_CreateBrokerSubscriptionsTable(DatabaseMigration):
    """Create broker_subscriptions table for managing broker subscriptions"""

    def __init__(self):
        super().__init__("049", "Create broker_subscriptions table for managing broker subscriptions")

    def up(self, db_manager) -> bool:
        """Create broker_subscriptions table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='broker_subscriptions'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'broker_subscriptions'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'broker_subscriptions' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("broker_subscriptions table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'broker_subscriptions')
                    logger.info(f"Existing columns in broker_subscriptions: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'user_id': 'INTEGER NOT NULL',
                        'plan_id': 'INTEGER NULL',
                        'status': "VARCHAR(20) DEFAULT 'active' NOT NULL",
                        'start_date': 'DATETIME NOT NULL',
                        'end_date': 'DATETIME',
                        'auto_renew': 'BOOLEAN DEFAULT TRUE NOT NULL',
                        'currency': "VARCHAR(10) NOT NULL DEFAULT 'USD'",
                        'monthly_fee': 'REAL DEFAULT 0.0 NOT NULL',
                        'billing_cycle': "VARCHAR(20) DEFAULT 'monthly' NOT NULL",
                        'next_billing_date': 'DATETIME',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to broker_subscriptions table")
                            conn.execute(text(f"ALTER TABLE broker_subscriptions ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        subscriptions_sql = f"""
                            CREATE TABLE broker_subscriptions (
                                {pk_sql},
                                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                plan_id INTEGER NULL REFERENCES subscription_plans(id) ON DELETE SET NULL,
                                status VARCHAR(20) DEFAULT 'active' NOT NULL,
                                start_date DATETIME NOT NULL,
                                end_date DATETIME,
                                auto_renew BOOLEAN DEFAULT TRUE NOT NULL,
                                currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                monthly_fee REAL DEFAULT 0.0 NOT NULL,
                                billing_cycle VARCHAR(20) DEFAULT 'monthly' NOT NULL,
                                next_billing_date DATETIME,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                UNIQUE(user_id)
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        subscriptions_sql = f"""
                            CREATE TABLE broker_subscriptions (
                                {pk_sql},
                                user_id INT NOT NULL,
                                plan_id INT NULL,
                                status VARCHAR(20) DEFAULT 'active' NOT NULL,
                                start_date DATETIME NOT NULL,
                                end_date DATETIME,
                                auto_renew BOOLEAN DEFAULT TRUE NOT NULL,
                                currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                monthly_fee REAL DEFAULT 0.0 NOT NULL,
                                billing_cycle VARCHAR(20) DEFAULT 'monthly' NOT NULL,
                                next_billing_date DATETIME,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                UNIQUE(user_id),
                                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                                FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL
                            )
                        """
                    else:
                        subscriptions_sql = f"""
                            CREATE TABLE broker_subscriptions (
                                {pk_sql},
                                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                plan_id INTEGER NULL REFERENCES subscription_plans(id) ON DELETE SET NULL,
                                status VARCHAR(20) DEFAULT 'active' NOT NULL,
                                start_date DATETIME NOT NULL,
                                end_date DATETIME,
                                auto_renew BOOLEAN DEFAULT TRUE NOT NULL,
                                currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                monthly_fee REAL DEFAULT 0.0 NOT NULL,
                                billing_cycle VARCHAR(20) DEFAULT 'monthly' NOT NULL,
                                next_billing_date DATETIME,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                UNIQUE(user_id)
                            )
                        """
                    
                    conn.execute(text(subscriptions_sql))
                
                # Create indexes for broker_subscriptions table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'broker_subscriptions')
                indexes = []
                
                if 'user_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_subscriptions_user_id ON broker_subscriptions(user_id)")
                if 'plan_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_subscriptions_plan_id ON broker_subscriptions(plan_id)")
                if 'status' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_subscriptions_status ON broker_subscriptions(status)")
                if 'currency' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_subscriptions_currency ON broker_subscriptions(currency)")
                if 'next_billing_date' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_subscriptions_next_billing_date ON broker_subscriptions(next_billing_date)")
                if 'user_id' in columns and 'status' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_subscriptions_composite ON broker_subscriptions(user_id, status)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                conn.commit()
                
            logger.info("Broker subscriptions table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create broker_subscriptions table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop broker_subscriptions table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS broker_subscriptions"))
                conn.commit()
                
            logger.info("Broker subscriptions table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop broker_subscriptions table: {e}")
            return False



class Migration_050_CreateSubscriptionPlansTable(DatabaseMigration):
    """Create subscription_plans table for managing subscription plan configurations"""

    def __init__(self):
        super().__init__("050", "Create subscription_plans table for managing subscription plan configurations")

    def up(self, db_manager) -> bool:
        """Create subscription_plans table with default plans"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='subscription_plans'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'subscription_plans'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'subscription_plans' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("subscription_plans table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'subscription_plans')
                    logger.info(f"Existing columns in subscription_plans: {columns}")
                    
                    # Define expected columns with their definitions
                    # Note: UNIQUE constraint cannot be added via ALTER TABLE in SQLite
                    expected_columns = {
                        'plan_type': 'VARCHAR(50) NOT NULL',
                        'display_name': 'VARCHAR(100) NOT NULL',
                        'description': 'TEXT',
                        'transaction_fee': 'REAL DEFAULT 0.0 NOT NULL',
                        'transaction_fee_currency': "VARCHAR(10) NOT NULL DEFAULT 'USD'",
                        'revenue_share_percentage': 'REAL DEFAULT 0.0 NOT NULL',
                        'monthly_fee': 'REAL DEFAULT 0.0 NOT NULL',
                        'monthly_fee_currency': "VARCHAR(10) NOT NULL DEFAULT 'USD'",
                        'max_api_keys': 'INTEGER DEFAULT 1 NOT NULL',
                        'allow_custom_gateway': 'BOOLEAN DEFAULT FALSE NOT NULL',
                        'allow_dedicated_session': 'BOOLEAN DEFAULT FALSE NOT NULL',
                        'support_level': "VARCHAR(20) NOT NULL DEFAULT 'email'",
                        'is_active': 'BOOLEAN DEFAULT TRUE NOT NULL',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to subscription_plans table")
                            conn.execute(text(f"ALTER TABLE subscription_plans ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        plans_sql = f"""
                            CREATE TABLE subscription_plans (
                                {pk_sql},
                                plan_type VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                description TEXT,
                                transaction_fee REAL DEFAULT 0.0 NOT NULL,
                                transaction_fee_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                revenue_share_percentage REAL DEFAULT 0.0 NOT NULL,
                                monthly_fee REAL DEFAULT 0.0 NOT NULL,
                                monthly_fee_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                max_api_keys INTEGER DEFAULT 1 NOT NULL,
                                allow_custom_gateway BOOLEAN DEFAULT FALSE NOT NULL,
                                allow_dedicated_session BOOLEAN DEFAULT FALSE NOT NULL,
                                support_level VARCHAR(20) NOT NULL DEFAULT 'email',
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        plans_sql = f"""
                            CREATE TABLE subscription_plans (
                                {pk_sql},
                                plan_type VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                description TEXT,
                                transaction_fee REAL DEFAULT 0.0 NOT NULL,
                                transaction_fee_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                revenue_share_percentage REAL DEFAULT 0.0 NOT NULL,
                                monthly_fee REAL DEFAULT 0.0 NOT NULL,
                                monthly_fee_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                max_api_keys INT DEFAULT 1 NOT NULL,
                                allow_custom_gateway BOOLEAN DEFAULT FALSE NOT NULL,
                                allow_dedicated_session BOOLEAN DEFAULT FALSE NOT NULL,
                                support_level VARCHAR(20) NOT NULL DEFAULT 'email',
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    else:
                        plans_sql = f"""
                            CREATE TABLE subscription_plans (
                                {pk_sql},
                                plan_type VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                description TEXT,
                                transaction_fee REAL DEFAULT 0.0 NOT NULL,
                                transaction_fee_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                revenue_share_percentage REAL DEFAULT 0.0 NOT NULL,
                                monthly_fee REAL DEFAULT 0.0 NOT NULL,
                                monthly_fee_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                max_api_keys INTEGER DEFAULT 1 NOT NULL,
                                allow_custom_gateway BOOLEAN DEFAULT FALSE NOT NULL,
                                allow_dedicated_session BOOLEAN DEFAULT FALSE NOT NULL,
                                support_level VARCHAR(20) NOT NULL DEFAULT 'email',
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(plans_sql))
                
                # Create indexes for subscription_plans table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'subscription_plans')
                indexes = []
                
                if 'plan_type' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_subscription_plans_plan_type ON subscription_plans(plan_type)")
                if 'is_active' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_subscription_plans_is_active ON subscription_plans(is_active)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                # Insert default subscription plans (only if we have all required columns)
                import json
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                
                # Check if we have all required columns for insertion
                required_cols = ['plan_type', 'display_name', 'description', 'transaction_fee',
                                'transaction_fee_currency', 'revenue_share_percentage', 'monthly_fee',
                                'monthly_fee_currency', 'max_api_keys', 'allow_custom_gateway',
                                'allow_dedicated_session', 'support_level', 'is_active',
                                'created_at', 'updated_at']
                
                if all(col in columns for col in required_cols):
                    default_plans = [
                        {
                            'plan_type': 'free',
                            'display_name': 'Free Plan',
                            'description': 'Basic access for individual users with limited features',
                            'transaction_fee': 0.05,
                            'transaction_fee_currency': 'USD',
                            'revenue_share_percentage': 10.0,
                            'monthly_fee': 0.0,
                            'monthly_fee_currency': 'USD',
                            'max_api_keys': 1,
                            'allow_custom_gateway': False,
                            'allow_dedicated_session': False,
                            'support_level': 'email',
                            'is_active': True
                        },
                        {
                            'plan_type': 'basic',
                            'display_name': 'Basic Plan',
                            'description': 'Standard plan for small businesses and individual brokers',
                            'transaction_fee': 0.03,
                            'transaction_fee_currency': 'USD',
                            'revenue_share_percentage': 5.0,
                            'monthly_fee': 29.99,
                            'monthly_fee_currency': 'USD',
                            'max_api_keys': 3,
                            'allow_custom_gateway': False,
                            'allow_dedicated_session': False,
                            'support_level': 'email_chat',
                            'is_active': True
                        },
                        {
                            'plan_type': 'professional',
                            'display_name': 'Professional Plan',
                            'description': 'Advanced plan for growing businesses with higher volume',
                            'transaction_fee': 0.01,
                            'transaction_fee_currency': 'USD',
                            'revenue_share_percentage': 0.0,
                            'monthly_fee': 99.99,
                            'monthly_fee_currency': 'USD',
                            'max_api_keys': 10,
                            'allow_custom_gateway': True,
                            'allow_dedicated_session': False,
                            'support_level': 'priority',
                            'is_active': True
                        },
                        {
                            'plan_type': 'enterprise',
                            'display_name': 'Enterprise Plan',
                            'description': 'Custom plan for large organizations with dedicated support',
                            'transaction_fee': 0.0,
                            'transaction_fee_currency': 'USD',
                            'revenue_share_percentage': 0.0,
                            'monthly_fee': 0.0,
                            'monthly_fee_currency': 'USD',
                            'max_api_keys': 999,
                            'allow_custom_gateway': True,
                            'allow_dedicated_session': True,
                            'support_level': 'dedicated',
                            'is_active': True
                        }
                    ]
                    
                    for plan in default_plans:
                        conn.execute(text(f"""
                            {insert_ignore} INTO subscription_plans
                            (plan_type, display_name, description, transaction_fee, transaction_fee_currency,
                             revenue_share_percentage, monthly_fee, monthly_fee_currency, max_api_keys,
                             allow_custom_gateway, allow_dedicated_session, support_level, is_active,
                             created_at, updated_at)
                            VALUES (:plan_type, :display_name, :description, :transaction_fee, :transaction_fee_currency,
                                    :revenue_share_percentage, :monthly_fee, :monthly_fee_currency, :max_api_keys,
                                    :allow_custom_gateway, :allow_dedicated_session, :support_level, :is_active,
                                    {timestamp_sql}, {timestamp_sql})
                        """), plan)
                else:
                    logger.warning(f"Skipping default plan insertion - missing required columns. Have: {columns}")
                
                conn.commit()
                
            logger.info("Subscription plans table created with default plans (FREE, BASIC, PROFESSIONAL, ENTERPRISE)")
            return True
        except Exception as e:
            logger.error(f"Failed to create subscription_plans table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop subscription_plans table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS subscription_plans"))
                conn.commit()
                
            logger.info("Subscription plans table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop subscription_plans table: {e}")
            return False

class Migration_051_CreateBrokerTransactionsTable(DatabaseMigration):
    """Create broker_transactions table for tracking all broker financial transactions"""

    def __init__(self):
        super().__init__("051", "Create broker_transactions table for tracking financial transactions")

    def up(self, db_manager) -> bool:
        """Create broker_transactions table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='broker_transactions'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'broker_transactions'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'broker_transactions' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("broker_transactions table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'broker_transactions')
                    logger.info(f"Existing columns in broker_transactions: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'user_id': 'INTEGER NOT NULL',
                        'subscription_id': 'INTEGER NULL',
                        'transaction_type': 'VARCHAR(50) NOT NULL',
                        'amount': 'REAL NOT NULL',
                        'currency': "VARCHAR(10) NOT NULL DEFAULT 'USD'",
                        'status': "VARCHAR(20) DEFAULT 'pending' NOT NULL",
                        'payment_method': 'VARCHAR(50)',
                        'payment_gateway': 'VARCHAR(50)',
                        'gateway_transaction_id': 'VARCHAR(255)',
                        'gateway_response': 'TEXT',
                        'invoice_id': 'INTEGER NULL',
                        'description': 'TEXT',
                        'metadata': 'TEXT',
                        'processed_at': 'DATETIME',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to broker_transactions table")
                            conn.execute(text(f"ALTER TABLE broker_transactions ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        transactions_sql = f"""
                            CREATE TABLE broker_transactions (
                                {pk_sql},
                                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                                transaction_type VARCHAR(50) NOT NULL,
                                amount REAL NOT NULL,
                                currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                                payment_method VARCHAR(50),
                                payment_gateway VARCHAR(50),
                                gateway_transaction_id VARCHAR(255),
                                gateway_response TEXT,
                                invoice_id INTEGER NULL REFERENCES broker_invoices(id) ON DELETE SET NULL,
                                description TEXT,
                                metadata TEXT,
                                processed_at DATETIME,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        transactions_sql = f"""
                            CREATE TABLE broker_transactions (
                                {pk_sql},
                                user_id INT NOT NULL,
                                subscription_id INT NULL,
                                transaction_type VARCHAR(50) NOT NULL,
                                amount REAL NOT NULL,
                                currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                                payment_method VARCHAR(50),
                                payment_gateway VARCHAR(50),
                                gateway_transaction_id VARCHAR(255),
                                gateway_response TEXT,
                                invoice_id INT NULL,
                                description TEXT,
                                metadata TEXT,
                                processed_at DATETIME,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                                FOREIGN KEY (subscription_id) REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                                FOREIGN KEY (invoice_id) REFERENCES broker_invoices(id) ON DELETE SET NULL
                            )
                        """
                    else:
                        transactions_sql = f"""
                            CREATE TABLE broker_transactions (
                                {pk_sql},
                                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                                transaction_type VARCHAR(50) NOT NULL,
                                amount REAL NOT NULL,
                                currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                                status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                                payment_method VARCHAR(50),
                                payment_gateway VARCHAR(50),
                                gateway_transaction_id VARCHAR(255),
                                gateway_response TEXT,
                                invoice_id INTEGER NULL REFERENCES broker_invoices(id) ON DELETE SET NULL,
                                description TEXT,
                                metadata TEXT,
                                processed_at DATETIME,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(transactions_sql))
                
                # Create indexes for broker_transactions table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'broker_transactions')
                indexes = []
                
                if 'user_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_user_id ON broker_transactions(user_id)")
                if 'subscription_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_subscription_id ON broker_transactions(subscription_id)")
                if 'transaction_type' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_transaction_type ON broker_transactions(transaction_type)")
                if 'status' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_status ON broker_transactions(status)")
                if 'currency' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_currency ON broker_transactions(currency)")
                if 'payment_gateway' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_payment_gateway ON broker_transactions(payment_gateway)")
                if 'gateway_transaction_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_gateway_transaction_id ON broker_transactions(gateway_transaction_id)")
                if 'invoice_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_invoice_id ON broker_transactions(invoice_id)")
                if 'created_at' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_created_at ON broker_transactions(created_at)")
                if 'user_id' in columns and 'status' in columns and 'created_at' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_transactions_composite ON broker_transactions(user_id, status, created_at)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                conn.commit()
                
            logger.info("Broker transactions table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create broker_transactions table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop broker_transactions table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS broker_transactions"))
                conn.commit()
                
            logger.info("Broker transactions table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop broker_transactions table: {e}")
            return False

class Migration_052_CreateBrokerUsageStatsTable(DatabaseMigration):
    """Create broker_usage_stats table for tracking broker usage statistics"""

    def __init__(self):
        super().__init__("052", "Create broker_usage_stats table for tracking usage statistics")

    def up(self, db_manager) -> bool:
        """Create broker_usage_stats table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='broker_usage_stats'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'broker_usage_stats'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'broker_usage_stats' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("broker_usage_stats table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'broker_usage_stats')
                    logger.info(f"Existing columns in broker_usage_stats: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'user_id': 'INTEGER NOT NULL',
                        'subscription_id': 'INTEGER NULL',
                        'stat_date': 'DATE NOT NULL',
                        'stat_period': "VARCHAR(20) NOT NULL DEFAULT 'daily'",
                        'bets_count': 'INTEGER DEFAULT 0 NOT NULL',
                        'api_calls_count': 'INTEGER DEFAULT 0 NOT NULL',
                        'webhook_calls_count': 'INTEGER DEFAULT 0 NOT NULL',
                        'total_bet_amount': 'REAL DEFAULT 0.0 NOT NULL',
                        'total_bet_amount_usd': 'REAL DEFAULT 0.0 NOT NULL',
                        'unique_matches_count': 'INTEGER DEFAULT 0 NOT NULL',
                        'unique_fixtures_count': 'INTEGER DEFAULT 0 NOT NULL',
                        'peak_concurrent_users': 'INTEGER DEFAULT 0 NOT NULL',
                        'storage_used_bytes': 'INTEGER DEFAULT 0 NOT NULL',
                        'bandwidth_used_bytes': 'INTEGER DEFAULT 0 NOT NULL',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to broker_usage_stats table")
                            conn.execute(text(f"ALTER TABLE broker_usage_stats ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        stats_sql = f"""
                            CREATE TABLE broker_usage_stats (
                                {pk_sql},
                                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                                stat_date DATE NOT NULL,
                                stat_period VARCHAR(20) NOT NULL DEFAULT 'daily',
                                bets_count INTEGER DEFAULT 0 NOT NULL,
                                api_calls_count INTEGER DEFAULT 0 NOT NULL,
                                webhook_calls_count INTEGER DEFAULT 0 NOT NULL,
                                total_bet_amount REAL DEFAULT 0.0 NOT NULL,
                                total_bet_amount_usd REAL DEFAULT 0.0 NOT NULL,
                                unique_matches_count INTEGER DEFAULT 0 NOT NULL,
                                unique_fixtures_count INTEGER DEFAULT 0 NOT NULL,
                                peak_concurrent_users INTEGER DEFAULT 0 NOT NULL,
                                storage_used_bytes INTEGER DEFAULT 0 NOT NULL,
                                bandwidth_used_bytes INTEGER DEFAULT 0 NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                UNIQUE(user_id, stat_date, stat_period)
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        stats_sql = f"""
                            CREATE TABLE broker_usage_stats (
                                {pk_sql},
                                user_id INT NOT NULL,
                                subscription_id INT NULL,
                                stat_date DATE NOT NULL,
                                stat_period VARCHAR(20) NOT NULL DEFAULT 'daily',
                                bets_count INT DEFAULT 0 NOT NULL,
                                api_calls_count INT DEFAULT 0 NOT NULL,
                                webhook_calls_count INT DEFAULT 0 NOT NULL,
                                total_bet_amount REAL DEFAULT 0.0 NOT NULL,
                                total_bet_amount_usd REAL DEFAULT 0.0 NOT NULL,
                                unique_matches_count INT DEFAULT 0 NOT NULL,
                                unique_fixtures_count INT DEFAULT 0 NOT NULL,
                                peak_concurrent_users INT DEFAULT 0 NOT NULL,
                                storage_used_bytes BIGINT DEFAULT 0 NOT NULL,
                                bandwidth_used_bytes BIGINT DEFAULT 0 NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                UNIQUE(user_id, stat_date, stat_period),
                                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                                FOREIGN KEY (subscription_id) REFERENCES broker_subscriptions(id) ON DELETE SET NULL
                            )
                        """
                    else:
                        stats_sql = f"""
                            CREATE TABLE broker_usage_stats (
                                {pk_sql},
                                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                                stat_date DATE NOT NULL,
                                stat_period VARCHAR(20) NOT NULL DEFAULT 'daily',
                                bets_count INTEGER DEFAULT 0 NOT NULL,
                                api_calls_count INTEGER DEFAULT 0 NOT NULL,
                                webhook_calls_count INTEGER DEFAULT 0 NOT NULL,
                                total_bet_amount REAL DEFAULT 0.0 NOT NULL,
                                total_bet_amount_usd REAL DEFAULT 0.0 NOT NULL,
                                unique_matches_count INTEGER DEFAULT 0 NOT NULL,
                                unique_fixtures_count INTEGER DEFAULT 0 NOT NULL,
                                peak_concurrent_users INTEGER DEFAULT 0 NOT NULL,
                                storage_used_bytes INTEGER DEFAULT 0 NOT NULL,
                                bandwidth_used_bytes INTEGER DEFAULT 0 NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                UNIQUE(user_id, stat_date, stat_period)
                            )
                        """
                    
                    conn.execute(text(stats_sql))
                
                # Create indexes for broker_usage_stats table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'broker_usage_stats')
                indexes = []
                
                if 'user_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_usage_stats_user_id ON broker_usage_stats(user_id)")
                if 'subscription_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_usage_stats_subscription_id ON broker_usage_stats(subscription_id)")
                if 'stat_date' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_usage_stats_stat_date ON broker_usage_stats(stat_date)")
                if 'stat_period' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_usage_stats_stat_period ON broker_usage_stats(stat_period)")
                if 'user_id' in columns and 'stat_date' in columns and 'stat_period' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_broker_usage_stats_composite ON broker_usage_stats(user_id, stat_date, stat_period)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                conn.commit()
                
            logger.info("Broker usage stats table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create broker_usage_stats table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop broker_usage_stats table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS broker_usage_stats"))
                conn.commit()
                
            logger.info("Broker usage stats table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop broker_usage_stats table: {e}")
            return False




class Migration_053_CreateTimezonesTable(DatabaseMigration):
    """Create timezones table for storing timezone information"""

    def __init__(self):
        super().__init__("053", "Create timezones table for storing timezone information")

    def up(self, db_manager) -> bool:
        """Create timezones table with default timezones"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='timezones'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'timezones'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'timezones' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("timezones table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'timezones')
                    logger.info(f"Existing columns in timezones: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'timezone_id': 'VARCHAR(50) NOT NULL',
                        'timezone_name': 'VARCHAR(100) NOT NULL',
                        'utc_offset': 'VARCHAR(10) NOT NULL',
                        'country_code': 'VARCHAR(5) NOT NULL',
                        'country_name': 'VARCHAR(100) NOT NULL',
                        'region': 'VARCHAR(100)',
                        'is_active': 'BOOLEAN DEFAULT TRUE NOT NULL',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to timezones table")
                            conn.execute(text(f"ALTER TABLE timezones ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        timezones_sql = f"""
                            CREATE TABLE timezones (
                                {pk_sql},
                                timezone_id VARCHAR(50) NOT NULL UNIQUE,
                                timezone_name VARCHAR(100) NOT NULL,
                                utc_offset VARCHAR(10) NOT NULL,
                                country_code VARCHAR(5) NOT NULL,
                                country_name VARCHAR(100) NOT NULL,
                                region VARCHAR(100),
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        timezones_sql = f"""
                            CREATE TABLE timezones (
                                {pk_sql},
                                timezone_id VARCHAR(50) NOT NULL UNIQUE,
                                timezone_name VARCHAR(100) NOT NULL,
                                utc_offset VARCHAR(10) NOT NULL,
                                country_code VARCHAR(5) NOT NULL,
                                country_name VARCHAR(100) NOT NULL,
                                region VARCHAR(100),
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    else:
                        timezones_sql = f"""
                            CREATE TABLE timezones (
                                {pk_sql},
                                timezone_id VARCHAR(50) NOT NULL UNIQUE,
                                timezone_name VARCHAR(100) NOT NULL,
                                utc_offset VARCHAR(10) NOT NULL,
                                country_code VARCHAR(5) NOT NULL,
                                country_name VARCHAR(100) NOT NULL,
                                region VARCHAR(100),
                                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(timezones_sql))
                
                # Create indexes for timezones table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'timezones')
                indexes = []
                
                if 'timezone_id' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_timezones_timezone_id ON timezones(timezone_id)")
                if 'country_code' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_timezones_country_code ON timezones(country_code)")
                if 'is_active' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_timezones_is_active ON timezones(is_active)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                # Insert default timezones (only if we have all required columns)
                required_cols = ['timezone_id', 'timezone_name', 'utc_offset', 'country_code', 'country_name', 'region', 'is_active', 'created_at', 'updated_at']
                
                if all(col in columns for col in required_cols):
                    insert_ignore = get_insert_ignore_sql(db_manager)
                    timestamp_sql = get_current_timestamp_sql(db_manager)
                    
                    default_timezones = [
                        # African timezones
                        ('Africa/Kampala', 'East Africa Time', '+03:00', 'UG', 'Uganda', 'Africa'),
                        ('Africa/Nairobi', 'East Africa Time', '+03:00', 'KE', 'Kenya', 'Africa'),
                        ('Africa/Dar_es_Salaam', 'East Africa Time', '+03:00', 'TZ', 'Tanzania', 'Africa'),
                        ('Africa/Kigali', 'Central Africa Time', '+02:00', 'RW', 'Rwanda', 'Africa'),
                        ('Africa/Johannesburg', 'South Africa Standard Time', '+02:00', 'ZA', 'South Africa', 'Africa'),
                        ('Africa/Cairo', 'Eastern European Time', '+02:00', 'EG', 'Egypt', 'Africa'),
                        ('Africa/Lagos', 'West Africa Time', '+01:00', 'NG', 'Nigeria', 'Africa'),
                        ('Africa/Algiers', 'Central European Time', '+01:00', 'DZ', 'Algeria', 'Africa'),
                        ('Africa/Casablanca', 'Western European Time', '+00:00', 'MA', 'Morocco', 'Africa'),
                        # Major global timezones
                        ('UTC', 'Coordinated Universal Time', '+00:00', 'XX', 'Universal', 'Global'),
                        ('America/New_York', 'Eastern Time', '-05:00', 'US', 'United States', 'America'),
                        ('America/Chicago', 'Central Time', '-06:00', 'US', 'United States', 'America'),
                        ('America/Denver', 'Mountain Time', '-07:00', 'US', 'United States', 'America'),
                        ('America/Los_Angeles', 'Pacific Time', '-08:00', 'US', 'United States', 'America'),
                        ('Europe/London', 'Greenwich Mean Time', '+00:00', 'GB', 'United Kingdom', 'Europe'),
                        ('Europe/Paris', 'Central European Time', '+01:00', 'FR', 'France', 'Europe'),
                        ('Europe/Berlin', 'Central European Time', '+01:00', 'DE', 'Germany', 'Europe'),
                        ('Asia/Dubai', 'Gulf Standard Time', '+04:00', 'AE', 'United Arab Emirates', 'Asia'),
                        ('Asia/Tokyo', 'Japan Standard Time', '+09:00', 'JP', 'Japan', 'Asia'),
                        ('Asia/Shanghai', 'China Standard Time', '+08:00', 'CN', 'China', 'Asia'),
                        ('Asia/Singapore', 'Singapore Time', '+08:00', 'SG', 'Singapore', 'Asia'),
                        ('Australia/Sydney', 'Australian Eastern Time', '+10:00', 'AU', 'Australia', 'Oceania'),
                    ]
                    
                    for tz in default_timezones:
                        conn.execute(text(f"""
                            {insert_ignore} INTO timezones
                            (timezone_id, timezone_name, utc_offset, country_code, country_name, region, is_active, created_at, updated_at)
                            VALUES (:timezone_id, :timezone_name, :utc_offset, :country_code, :country_name, :region, 1, {timestamp_sql}, {timestamp_sql})
                        """), {
                            'timezone_id': tz[0],
                            'timezone_name': tz[1],
                            'utc_offset': tz[2],
                            'country_code': tz[3],
                            'country_name': tz[4],
                            'region': tz[5]
                        })
                else:
                    logger.warning(f"Skipping default timezone insertion - missing required columns. Have: {columns}")
                
                conn.commit()
                
            logger.info("Timezones table created with default timezones")
            return True
        except Exception as e:
            logger.error(f"Failed to create timezones table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop timezones table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS timezones"))
                conn.commit()
                
            logger.info("Timezones table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop timezones table: {e}")
            return False


class Migration_054_CreatePaymentGatewaysTable(DatabaseMigration):
    """Create payment_gateways table for storing payment gateway configurations"""

    def __init__(self):
        super().__init__("054", "Create payment_gateways table for payment gateway configurations")

    def up(self, db_manager) -> bool:
        """Create payment_gateways table with default gateways"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if table already exists
                if dialect == 'sqlite':
                    result = conn.execute(text("""
                        SELECT name FROM sqlite_master
                        WHERE type='table' AND name='payment_gateways'
                    """))
                    table_exists = result.fetchone() is not None
                elif dialect in ('mysql', 'mariadb'):
                    result = conn.execute(text("""
                        SELECT COUNT(*) FROM information_schema.tables
                        WHERE table_schema = DATABASE() AND table_name = 'payment_gateways'
                    """))
                    table_exists = result.scalar() > 0
                else:
                    inspector = inspect(db_manager.engine)
                    table_exists = 'payment_gateways' in inspector.get_table_names()
                
                if table_exists:
                    # Table exists - check and add missing columns
                    logger.info("payment_gateways table already exists, checking for missing columns")
                    columns = get_table_columns(db_manager, 'payment_gateways')
                    logger.info(f"Existing columns in payment_gateways: {columns}")
                    
                    # Define expected columns with their definitions
                    expected_columns = {
                        'gateway_name': 'VARCHAR(50) NOT NULL',
                        'display_name': 'VARCHAR(100) NOT NULL',
                        'gateway_type': "VARCHAR(50) NOT NULL DEFAULT 'payment'",
                        'provider': 'VARCHAR(50) NOT NULL',
                        'api_key': 'VARCHAR(255)',
                        'api_secret': 'VARCHAR(255)',
                        'merchant_id': 'VARCHAR(255)',
                        'webhook_secret': 'VARCHAR(255)',
                        'sandbox_mode': 'BOOLEAN DEFAULT TRUE NOT NULL',
                        'is_active': 'BOOLEAN DEFAULT FALSE NOT NULL',
                        'supported_currencies': 'TEXT',
                        'supported_methods': 'TEXT',
                        'min_amount': 'REAL DEFAULT 0.0 NOT NULL',
                        'max_amount': 'REAL DEFAULT 1000000.0 NOT NULL',
                        'fee_percentage': 'REAL DEFAULT 0.0 NOT NULL',
                        'fixed_fee': 'REAL DEFAULT 0.0 NOT NULL',
                        'config_json': 'TEXT',
                        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL',
                    }
                    
                    # Add missing columns
                    for col_name, col_def in expected_columns.items():
                        if col_name not in columns:
                            logger.info(f"Adding missing column {col_name} to payment_gateways table")
                            conn.execute(text(f"ALTER TABLE payment_gateways ADD COLUMN {col_name} {col_def}"))
                else:
                    # Table doesn't exist - create it
                    if dialect == 'sqlite':
                        gateways_sql = f"""
                            CREATE TABLE payment_gateways (
                                {pk_sql},
                                gateway_name VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                gateway_type VARCHAR(50) NOT NULL DEFAULT 'payment',
                                provider VARCHAR(50) NOT NULL,
                                api_key VARCHAR(255),
                                api_secret VARCHAR(255),
                                merchant_id VARCHAR(255),
                                webhook_secret VARCHAR(255),
                                sandbox_mode BOOLEAN DEFAULT TRUE NOT NULL,
                                is_active BOOLEAN DEFAULT FALSE NOT NULL,
                                supported_currencies TEXT,
                                supported_methods TEXT,
                                min_amount REAL DEFAULT 0.0 NOT NULL,
                                max_amount REAL DEFAULT 1000000.0 NOT NULL,
                                fee_percentage REAL DEFAULT 0.0 NOT NULL,
                                fixed_fee REAL DEFAULT 0.0 NOT NULL,
                                config_json TEXT,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    elif dialect in ('mysql', 'mariadb'):
                        gateways_sql = f"""
                            CREATE TABLE payment_gateways (
                                {pk_sql},
                                gateway_name VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                gateway_type VARCHAR(50) NOT NULL DEFAULT 'payment',
                                provider VARCHAR(50) NOT NULL,
                                api_key VARCHAR(255),
                                api_secret VARCHAR(255),
                                merchant_id VARCHAR(255),
                                webhook_secret VARCHAR(255),
                                sandbox_mode BOOLEAN DEFAULT TRUE NOT NULL,
                                is_active BOOLEAN DEFAULT FALSE NOT NULL,
                                supported_currencies TEXT,
                                supported_methods TEXT,
                                min_amount REAL DEFAULT 0.0 NOT NULL,
                                max_amount REAL DEFAULT 1000000.0 NOT NULL,
                                fee_percentage REAL DEFAULT 0.0 NOT NULL,
                                fixed_fee REAL DEFAULT 0.0 NOT NULL,
                                config_json TEXT,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    else:
                        gateways_sql = f"""
                            CREATE TABLE payment_gateways (
                                {pk_sql},
                                gateway_name VARCHAR(50) NOT NULL UNIQUE,
                                display_name VARCHAR(100) NOT NULL,
                                gateway_type VARCHAR(50) NOT NULL DEFAULT 'payment',
                                provider VARCHAR(50) NOT NULL,
                                api_key VARCHAR(255),
                                api_secret VARCHAR(255),
                                merchant_id VARCHAR(255),
                                webhook_secret VARCHAR(255),
                                sandbox_mode BOOLEAN DEFAULT TRUE NOT NULL,
                                is_active BOOLEAN DEFAULT FALSE NOT NULL,
                                supported_currencies TEXT,
                                supported_methods TEXT,
                                min_amount REAL DEFAULT 0.0 NOT NULL,
                                max_amount REAL DEFAULT 1000000.0 NOT NULL,
                                fee_percentage REAL DEFAULT 0.0 NOT NULL,
                                fixed_fee REAL DEFAULT 0.0 NOT NULL,
                                config_json TEXT,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )
                        """
                    
                    conn.execute(text(gateways_sql))
                
                # Create indexes for payment_gateways table
                # Only create indexes for columns that exist
                columns = get_table_columns(db_manager, 'payment_gateways')
                indexes = []
                
                if 'gateway_name' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_payment_gateways_gateway_name ON payment_gateways(gateway_name)")
                if 'gateway_type' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_payment_gateways_gateway_type ON payment_gateways(gateway_type)")
                if 'provider' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_payment_gateways_provider ON payment_gateways(provider)")
                if 'is_active' in columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_payment_gateways_is_active ON payment_gateways(is_active)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_err:
                        logger.warning(f"Could not create index (may already exist): {idx_err}")
                
                # Insert default payment gateways
                import json
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                
                default_gateways = [
                    {
                        'gateway_name': 'stripe',
                        'display_name': 'Stripe',
                        'gateway_type': 'payment',
                        'provider': 'stripe',
                        'sandbox_mode': True,
                        'is_active': False,
                        'supported_currencies': json.dumps(['USD', 'EUR', 'GBP', 'UGX', 'KES', 'TZS', 'RWF', 'ZAR']),
                        'supported_methods': json.dumps(['card', 'bank_transfer']),
                        'min_amount': 1.0,
                        'max_amount': 1000000.0,
                        'fee_percentage': 2.9,
                        'fixed_fee': 0.30
                    },
                    {
                        'gateway_name': 'paypal',
                        'display_name': 'PayPal',
                        'gateway_type': 'payment',
                        'provider': 'paypal',
                        'sandbox_mode': True,
                        'is_active': False,
                        'supported_currencies': json.dumps(['USD', 'EUR', 'GBP', 'UGX', 'KES', 'TZS', 'RWF', 'ZAR']),
                        'supported_methods': json.dumps(['paypal', 'card']),
                        'min_amount': 1.0,
                        'max_amount': 1000000.0,
                        'fee_percentage': 3.4,
                        'fixed_fee': 0.30
                    },
                    {
                        'gateway_name': 'flutterwave',
                        'display_name': 'Flutterwave',
                        'gateway_type': 'payment',
                        'provider': 'flutterwave',
                        'sandbox_mode': True,
                        'is_active': False,
                        'supported_currencies': json.dumps(['USD', 'EUR', 'GBP', 'UGX', 'KES', 'TZS', 'RWF', 'ZAR', 'NGN', 'GHS']),
                        'supported_methods': json.dumps(['card', 'bank_transfer', 'mobile_money', 'ussd']),
                        'min_amount': 1.0,
                        'max_amount': 1000000.0,
                        'fee_percentage': 1.4,
                        'fixed_fee': 0.0
                    },
                    {
                        'gateway_name': 'paystack',
                        'display_name': 'Paystack',
                        'gateway_type': 'payment',
                        'provider': 'paystack',
                        'sandbox_mode': True,
                        'is_active': False,
                        'supported_currencies': json.dumps(['USD', 'EUR', 'GBP', 'UGX', 'KES', 'TZS', 'RWF', 'ZAR', 'NGN', 'GHS']),
                        'supported_methods': json.dumps(['card', 'bank_transfer']),
                        'min_amount': 1.0,
                        'max_amount': 1000000.0,
                        'fee_percentage': 1.5,
                        'fixed_fee': 0.0
                    },
                    {
                        'gateway_name': 'coinbase_commerce',
                        'display_name': 'Coinbase Commerce',
                        'gateway_type': 'crypto',
                        'provider': 'coinbase',
                        'sandbox_mode': True,
                        'is_active': False,
                        'supported_currencies': json.dumps(['BTC', 'ETH', 'USDT', 'USDC', 'BNB', 'SOL', 'DOGE', 'LTC', 'XRP', 'ADA', 'TRX', 'MATIC', 'AVAX', 'DOT', 'SHIB']),
                        'supported_methods': json.dumps(['crypto']),
                        'min_amount': 0.0001,
                        'max_amount': 1000000.0,
                        'fee_percentage': 1.0,
                        'fixed_fee': 0.0
                    },
                    {
                        'gateway_name': 'bitpay',
                        'display_name': 'BitPay',
                        'gateway_type': 'crypto',
                        'provider': 'bitpay',
                        'sandbox_mode': True,
                        'is_active': False,
                        'supported_currencies': json.dumps(['BTC', 'ETH', 'USDT', 'USDC', 'BNB', 'SOL', 'DOGE', 'LTC', 'XRP', 'ADA', 'TRX', 'MATIC', 'AVAX', 'DOT', 'SHIB']),
                        'supported_methods': json.dumps(['crypto']),
                        'min_amount': 0.0001,
                        'max_amount': 1000000.0,
                        'fee_percentage': 1.0,
                        'fixed_fee': 0.0
                    },
                ]
                
                for gateway in default_gateways:
                    conn.execute(text(f"""
                        {insert_ignore} INTO payment_gateways
                        (gateway_name, display_name, gateway_type, provider, api_key, api_secret, merchant_id, webhook_secret,
                         sandbox_mode, is_active, supported_currencies, supported_methods, min_amount, max_amount,
                         fee_percentage, fixed_fee, config_json, created_at, updated_at)
                        VALUES (:gateway_name, :display_name, :gateway_type, :provider, '', '', '', '',
                                :sandbox_mode, :is_active, :supported_currencies, :supported_methods, :min_amount, :max_amount,
                                :fee_percentage, :fixed_fee, NULL, {timestamp_sql}, {timestamp_sql})
                    """), gateway)
                
                conn.commit()
                
            logger.info("Payment gateways table created with default gateways (Stripe, PayPal, Flutterwave, Paystack, Coinbase Commerce, BitPay)")
            return True
        except Exception as e:
            logger.error(f"Failed to create payment_gateways table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop payment_gateways table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS payment_gateways"))
                conn.commit()
                
            logger.info("Payment gateways table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop payment_gateways table: {e}")
            return False


# Registry of all migrations in order

class Migration_057_AddBrokerFieldsToApiKeys(DatabaseMigration):
    """Add broker-related fields to api_keys table for broker billing system"""

    def __init__(self):
        super().__init__("057", "Add broker-related fields to api_keys table")

    def up(self, db_manager) -> bool:
        """Add broker-related columns to api_keys table"""
        try:
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'api_keys')
            with db_manager.engine.connect() as conn:
                
                if 'subscription_id' not in columns:
                    # Add subscription_id column (FK to broker_subscriptions)
                    dialect = db_manager.engine.dialect.name
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE api_keys
                            ADD COLUMN subscription_id INT NULL
                        """))
                        # Add foreign key constraint for MySQL
                        try:
                            conn.execute(text("""
                                ALTER TABLE api_keys
                                ADD CONSTRAINT fk_api_keys_subscription
                                FOREIGN KEY (subscription_id) REFERENCES broker_subscriptions(id) ON DELETE SET NULL
                            """))
                        except Exception as e:
                            logger.warning(f"Could not add foreign key constraint for subscription_id: {e}")
                    else:
                        conn.execute(text("""
                            ALTER TABLE api_keys
                            ADD COLUMN subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL
                        """))
                    logger.info("subscription_id column added to api_keys table")
                
                if 'rate_limit' not in columns:
                    # Add rate_limit column (requests per minute)
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN rate_limit INTEGER DEFAULT 60 NOT NULL
                    """))
                    logger.info("rate_limit column added to api_keys table")
                
                if 'daily_limit' not in columns:
                    # Add daily_limit column (requests per day)
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN daily_limit INTEGER DEFAULT 1000 NOT NULL
                    """))
                    logger.info("daily_limit column added to api_keys table")
                
                if 'monthly_limit' not in columns:
                    # Add monthly_limit column (requests per month)
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN monthly_limit INTEGER DEFAULT 30000 NOT NULL
                    """))
                    logger.info("monthly_limit column added to api_keys table")
                
                if 'requests_today' not in columns:
                    # Add requests_today column
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN requests_today INTEGER DEFAULT 0 NOT NULL
                    """))
                    logger.info("requests_today column added to api_keys table")
                
                if 'requests_this_month' not in columns:
                    # Add requests_this_month column
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN requests_this_month INTEGER DEFAULT 0 NOT NULL
                    """))
                    logger.info("requests_this_month column added to api_keys table")
                
                if 'last_reset_daily' not in columns:
                    # Add last_reset_daily column
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN last_reset_daily DATE NULL
                    """))
                    logger.info("last_reset_daily column added to api_keys table")
                
                if 'last_reset_monthly' not in columns:
                    # Add last_reset_monthly column
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN last_reset_monthly DATE NULL
                    """))
                    logger.info("last_reset_monthly column added to api_keys table")
                
                if 'allowed_endpoints' not in columns:
                    # Add allowed_endpoints column (JSON array of allowed API endpoints)
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN allowed_endpoints TEXT NULL
                    """))
                    logger.info("allowed_endpoints column added to api_keys table")
                
                if 'allowed_ips' not in columns:
                    # Add allowed_ips column (JSON array of allowed IP addresses)
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN allowed_ips TEXT NULL
                    """))
                    logger.info("allowed_ips column added to api_keys table")
                
                if 'webhook_url' not in columns:
                    # Add webhook_url column
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN webhook_url VARCHAR(500) NULL
                    """))
                    logger.info("webhook_url column added to api_keys table")
                
                if 'webhook_secret' not in columns:
                    # Add webhook_secret column
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN webhook_secret VARCHAR(255) NULL
                    """))
                    logger.info("webhook_secret column added to api_keys table")
                
                if 'metadata' not in columns:
                    # Add metadata column (JSON for additional data)
                    conn.execute(text("""
                        ALTER TABLE api_keys
                        ADD COLUMN metadata TEXT NULL
                    """))
                    logger.info("metadata column added to api_keys table")
                
                # Add indexes for broker-related fields
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_subscription_id ON api_keys(subscription_id)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_rate_limit ON api_keys(rate_limit)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_daily_limit ON api_keys(daily_limit)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_requests_today ON api_keys(requests_today)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_requests_this_month ON api_keys(requests_this_month)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_last_reset_daily ON api_keys(last_reset_daily)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_last_reset_monthly ON api_keys(last_reset_monthly)",
                    "CREATE INDEX IF NOT EXISTS ix_api_keys_composite ON api_keys(user_id, subscription_id, is_active)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Broker-related fields added to api_keys table successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add broker fields to api_keys table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove broker-related columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - broker fields will remain")
        return True


class Migration_059_InsertDefaultSubscriptionPlans(DatabaseMigration):
    """Insert default subscription plans (Free, Basic, Professional, Enterprise)"""

    def __init__(self):
        super().__init__("059", "Insert default subscription plans")

    def up(self, db_manager) -> bool:
        """Insert default subscription plans if they don't exist"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if table exists and has required columns
                columns = get_table_columns(db_manager, 'subscription_plans')
                required_cols = ['plan_type', 'display_name', 'description', 'transaction_fee',
                                'transaction_fee_currency', 'revenue_share_percentage', 'monthly_fee',
                                'monthly_fee_currency', 'max_api_keys', 'allow_custom_gateway',
                                'allow_dedicated_session', 'support_level', 'is_active']
                
                if not all(col in columns for col in required_cols):
                    logger.warning(f"Skipping default plan insertion - missing required columns. Have: {columns}")
                    return True
                
                # Check if default plans already exist (by plan_type)
                result = conn.execute(text("""
                    SELECT plan_type FROM subscription_plans
                    WHERE plan_type IN ('free', 'basic', 'professional', 'enterprise')
                """))
                existing_plan_types = [row[0] for row in result.fetchall()]
                
                if len(existing_plan_types) == 4:
                    logger.info(f"All default subscription plans already exist: {existing_plan_types}, skipping insertion")
                    return True
                
                logger.info(f"Existing default plans: {existing_plan_types}, will insert missing plans")
                
                # Insert default plans
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                
                default_plans = [
                    {
                        'plan_type': 'free',
                        'display_name': 'Free Plan',
                        'description': 'Basic access for individual users with limited features',
                        'transaction_fee': 0.05,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 10.0,
                        'monthly_fee': 0.0,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 1,
                        'allow_custom_gateway': False,
                        'allow_dedicated_session': False,
                        'support_level': 'email',
                        'is_active': True
                    },
                    {
                        'plan_type': 'basic',
                        'display_name': 'Basic Plan',
                        'description': 'Standard plan for small businesses and individual brokers',
                        'transaction_fee': 0.03,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 5.0,
                        'monthly_fee': 29.99,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 3,
                        'allow_custom_gateway': False,
                        'allow_dedicated_session': False,
                        'support_level': 'email_chat',
                        'is_active': True
                    },
                    {
                        'plan_type': 'professional',
                        'display_name': 'Professional Plan',
                        'description': 'Advanced plan for growing businesses with higher volume',
                        'transaction_fee': 0.01,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 0.0,
                        'monthly_fee': 99.99,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 10,
                        'allow_custom_gateway': True,
                        'allow_dedicated_session': False,
                        'support_level': 'priority',
                        'is_active': True
                    },
                    {
                        'plan_type': 'enterprise',
                        'display_name': 'Enterprise Plan',
                        'description': 'Custom plan for large organizations with dedicated support',
                        'transaction_fee': 0.0,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 0.0,
                        'monthly_fee': 0.0,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 999,
                        'allow_custom_gateway': True,
                        'allow_dedicated_session': True,
                        'support_level': 'dedicated',
                        'is_active': True
                    }
                ]
                
                for plan in default_plans:
                    conn.execute(text(f"""
                        {insert_ignore} INTO subscription_plans
                        (plan_type, display_name, description, transaction_fee, transaction_fee_currency,
                         revenue_share_percentage, monthly_fee, monthly_fee_currency, max_api_keys,
                         allow_custom_gateway, allow_dedicated_session, support_level, is_active,
                         created_at, updated_at)
                        VALUES (:plan_type, :display_name, :description, :transaction_fee, :transaction_fee_currency,
                                :revenue_share_percentage, :monthly_fee, :monthly_fee_currency, :max_api_keys,
                                :allow_custom_gateway, :allow_dedicated_session, :support_level, :is_active,
                                {timestamp_sql}, {timestamp_sql})
                    """), plan)
                
                conn.commit()
                
            logger.info("Default subscription plans inserted (Free, Basic, Professional, Enterprise)")
            return True
        except Exception as e:
            logger.error(f"Failed to insert default subscription plans: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove default subscription plans"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("""
                    DELETE FROM subscription_plans
                    WHERE plan_type IN ('free', 'basic', 'professional', 'enterprise')
                """))
                conn.commit()
                
            logger.info("Default subscription plans removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default subscription plans: {e}")
            return False


class Migration_060_EnsureDefaultSubscriptionPlans(DatabaseMigration):
    """Ensure default subscription plans exist (re-run safe)"""

    def __init__(self):
        super().__init__("060", "Ensure default subscription plans exist")

    def up(self, db_manager) -> bool:
        """Ensure default subscription plans exist (idempotent)"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if table exists and has required columns
                columns = get_table_columns(db_manager, 'subscription_plans')
                required_cols = ['plan_type', 'display_name', 'description', 'transaction_fee',
                                'transaction_fee_currency', 'revenue_share_percentage', 'monthly_fee',
                                'monthly_fee_currency', 'max_api_keys', 'allow_custom_gateway',
                                'allow_dedicated_session', 'support_level', 'is_active']
                
                if not all(col in columns for col in required_cols):
                    logger.warning(f"Skipping default plan insertion - missing required columns. Have: {columns}")
                    return True
                
                # Check which default plans already exist
                result = conn.execute(text("""
                    SELECT plan_type FROM subscription_plans
                    WHERE plan_type IN ('free', 'basic', 'professional', 'enterprise')
                """))
                existing_plan_types = [row[0] for row in result.fetchall()]
                
                logger.info(f"Existing default plans: {existing_plan_types}")
                
                # Define default plans
                default_plans = [
                    {
                        'plan_type': 'free',
                        'display_name': 'Free Plan',
                        'description': 'Basic access for individual users with limited features',
                        'transaction_fee': 0.05,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 10.0,
                        'monthly_fee': 0.0,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 1,
                        'allow_custom_gateway': False,
                        'allow_dedicated_session': False,
                        'support_level': 'email',
                        'is_active': True
                    },
                    {
                        'plan_type': 'basic',
                        'display_name': 'Basic Plan',
                        'description': 'Standard plan for small businesses and individual brokers',
                        'transaction_fee': 0.03,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 5.0,
                        'monthly_fee': 29.99,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 3,
                        'allow_custom_gateway': False,
                        'allow_dedicated_session': False,
                        'support_level': 'email_chat',
                        'is_active': True
                    },
                    {
                        'plan_type': 'professional',
                        'display_name': 'Professional Plan',
                        'description': 'Advanced plan for growing businesses with higher volume',
                        'transaction_fee': 0.01,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 0.0,
                        'monthly_fee': 99.99,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 10,
                        'allow_custom_gateway': True,
                        'allow_dedicated_session': False,
                        'support_level': 'priority',
                        'is_active': True
                    },
                    {
                        'plan_type': 'enterprise',
                        'display_name': 'Enterprise Plan',
                        'description': 'Custom plan for large organizations with dedicated support',
                        'transaction_fee': 0.0,
                        'transaction_fee_currency': 'USD',
                        'revenue_share_percentage': 0.0,
                        'monthly_fee': 0.0,
                        'monthly_fee_currency': 'USD',
                        'max_api_keys': 999,
                        'allow_custom_gateway': True,
                        'allow_dedicated_session': True,
                        'support_level': 'dedicated',
                        'is_active': True
                    }
                ]
                
                # Insert only missing plans
                insert_ignore = get_insert_ignore_sql(db_manager)
                timestamp_sql = get_current_timestamp_sql(db_manager)
                
                inserted_count = 0
                for plan in default_plans:
                    if plan['plan_type'] not in existing_plan_types:
                        conn.execute(text(f"""
                            {insert_ignore} INTO subscription_plans
                            (plan_type, display_name, description, transaction_fee, transaction_fee_currency,
                             revenue_share_percentage, monthly_fee, monthly_fee_currency, max_api_keys,
                             allow_custom_gateway, allow_dedicated_session, support_level, is_active,
                             created_at, updated_at)
                            VALUES (:plan_type, :display_name, :description, :transaction_fee, :transaction_fee_currency,
                                    :revenue_share_percentage, :monthly_fee, :monthly_fee_currency, :max_api_keys,
                                    :allow_custom_gateway, :allow_dedicated_session, :support_level, :is_active,
                                    {timestamp_sql}, {timestamp_sql})
                        """), plan)
                        inserted_count += 1
                        logger.info(f"Inserted default plan: {plan['plan_type']}")
                
                conn.commit()
                
            logger.info(f"Ensured default subscription plans exist (inserted {inserted_count} new plans)")
            return True
        except Exception as e:
            logger.error(f"Failed to ensure default subscription plans: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove default subscription plans"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("""
                    DELETE FROM subscription_plans
                    WHERE plan_type IN ('free', 'basic', 'professional', 'enterprise')
                """))
                conn.commit()
                
            logger.info("Default subscription plans removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default subscription plans: {e}")
            return False


class Migration_061_RemoveFakeSubscriptionPlans(DatabaseMigration):
    """Remove fake/invalid subscription plans"""

    def __init__(self):
        super().__init__("061", "Remove fake/invalid subscription plans")

    def up(self, db_manager) -> bool:
        """Remove fake/invalid subscription plans (plans not in default set)"""
        try:
            with db_manager.engine.connect() as conn:
                # Delete plans that are not in the default set
                # This removes any fake plans that were created manually
                result = conn.execute(text("""
                    DELETE FROM subscription_plans
                    WHERE plan_type NOT IN ('free', 'basic', 'professional', 'enterprise')
                """))
                deleted_count = result.rowcount
                
                conn.commit()
                
            logger.info(f"Removed {deleted_count} fake/invalid subscription plans")
            return True
        except Exception as e:
            logger.error(f"Failed to remove fake subscription plans: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Cannot restore deleted fake plans"""
        logger.warning("Cannot restore deleted fake plans")
        return True


class Migration_056_AddBrokerFieldsToUsers(DatabaseMigration):
    """Add broker-related fields to users table for broker billing system"""

    def __init__(self):
        super().__init__("056", "Add broker-related fields to users table")

    def up(self, db_manager) -> bool:
        """Add broker-related columns to users table"""
        try:
            dialect = db_manager.engine.dialect.name
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'users')
            with db_manager.engine.connect() as conn:
                
                if 'broker_id' not in columns:
                    # Add broker_id column (unique identifier for brokers)
                    # Note: SQLite doesn't support UNIQUE in ALTER TABLE ADD COLUMN
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN broker_id VARCHAR(50) NULL UNIQUE
                        """))
                    else:
                        # SQLite - add without UNIQUE, create index separately
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN broker_id VARCHAR(50) NULL
                        """))
                        # Create unique index for broker_id
                        try:
                            conn.execute(text("""
                                CREATE UNIQUE INDEX IF NOT EXISTS ix_users_broker_id_unique ON users(broker_id)
                            """))
                        except Exception as idx_err:
                            logger.warning(f"Could not create unique index on broker_id: {idx_err}")
                    logger.info("broker_id column added to users table")
                
                if 'company_name' not in columns:
                    # Add company_name column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN company_name VARCHAR(255) NULL
                    """))
                    logger.info("company_name column added to users table")
                
                if 'company_address' not in columns:
                    # Add company_address column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN company_address TEXT NULL
                    """))
                    logger.info("company_address column added to users table")
                
                if 'company_phone' not in columns:
                    # Add company_phone column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN company_phone VARCHAR(50) NULL
                    """))
                    logger.info("company_phone column added to users table")
                
                if 'company_email' not in columns:
                    # Add company_email column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN company_email VARCHAR(255) NULL
                    """))
                    logger.info("company_email column added to users table")
                
                if 'preferred_currency_id' not in columns:
                    # Add preferred_currency_id column (FK to currencies table)
                    dialect = db_manager.engine.dialect.name
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN preferred_currency_id INT NULL
                        """))
                        # Add foreign key constraint for MySQL
                        try:
                            conn.execute(text("""
                                ALTER TABLE users
                                ADD CONSTRAINT fk_users_preferred_currency
                                FOREIGN KEY (preferred_currency_id) REFERENCES currencies(id) ON DELETE SET NULL
                            """))
                        except Exception as e:
                            logger.warning(f"Could not add foreign key constraint for preferred_currency_id: {e}")
                    else:
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN preferred_currency_id INTEGER NULL REFERENCES currencies(id) ON DELETE SET NULL
                        """))
                    logger.info("preferred_currency_id column added to users table")
                
                if 'timezone_id' not in columns:
                    # Add timezone_id column (FK to timezones table)
                    dialect = db_manager.engine.dialect.name
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN timezone_id INT NULL
                        """))
                        # Add foreign key constraint for MySQL
                        try:
                            conn.execute(text("""
                                ALTER TABLE users
                                ADD CONSTRAINT fk_users_timezone
                                FOREIGN KEY (timezone_id) REFERENCES timezones(id) ON DELETE SET NULL
                            """))
                        except Exception as e:
                            logger.warning(f"Could not add foreign key constraint for timezone_id: {e}")
                    else:
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN timezone_id INTEGER NULL REFERENCES timezones(id) ON DELETE SET NULL
                        """))
                    logger.info("timezone_id column added to users table")
                
                if 'billing_email' not in columns:
                    # Add billing_email column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN billing_email VARCHAR(255) NULL
                    """))
                    logger.info("billing_email column added to users table")
                
                if 'billing_address' not in columns:
                    # Add billing_address column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN billing_address TEXT NULL
                    """))
                    logger.info("billing_address column added to users table")
                
                if 'tax_id' not in columns:
                    # Add tax_id column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN tax_id VARCHAR(100) NULL
                    """))
                    logger.info("tax_id column added to users table")
                
                if 'payment_method' not in columns:
                    # Add payment_method column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN payment_method VARCHAR(50) NULL
                    """))
                    logger.info("payment_method column added to users table")
                
                if 'is_broker_approved' not in columns:
                    # Add is_broker_approved column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN is_broker_approved BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    logger.info("is_broker_approved column added to users table")
                
                if 'approved_at' not in columns:
                    # Add approved_at column
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN approved_at DATETIME NULL
                    """))
                    logger.info("approved_at column added to users table")
                
                if 'approved_by' not in columns:
                    # Add approved_by column (FK to users table)
                    dialect = db_manager.engine.dialect.name
                    if dialect in ('mysql', 'mariadb'):
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN approved_by INT NULL
                        """))
                        # Add foreign key constraint for MySQL
                        try:
                            conn.execute(text("""
                                ALTER TABLE users
                                ADD CONSTRAINT fk_users_approved_by
                                FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
                            """))
                        except Exception as e:
                            logger.warning(f"Could not add foreign key constraint for approved_by: {e}")
                    else:
                        conn.execute(text("""
                            ALTER TABLE users
                            ADD COLUMN approved_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL
                        """))
                    logger.info("approved_by column added to users table")
                
                # Add indexes for broker-related fields
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_users_broker_id ON users(broker_id)",
                    "CREATE INDEX IF NOT EXISTS ix_users_company_name ON users(company_name)",
                    "CREATE INDEX IF NOT EXISTS ix_users_preferred_currency_id ON users(preferred_currency_id)",
                    "CREATE INDEX IF NOT EXISTS ix_users_timezone_id ON users(timezone_id)",
                    "CREATE INDEX IF NOT EXISTS ix_users_is_broker_approved ON users(is_broker_approved)",
                    "CREATE INDEX IF NOT EXISTS ix_users_approved_by ON users(approved_by)",
                    "CREATE INDEX IF NOT EXISTS ix_users_broker_composite ON users(role, is_broker_approved, broker_id)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Broker-related fields added to users table successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add broker fields to users table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove broker-related columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - broker fields will remain")
        return True


class Migration_055_CreateBrokerInvoicesTable(DatabaseMigration):
    """Create broker_invoices table for managing broker billing invoices"""

    def __init__(self):
        super().__init__("055", "Create broker_invoices table for managing billing invoices")

    def up(self, db_manager) -> bool:
        """Create broker_invoices table"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Create broker_invoices table
                if dialect == 'sqlite':
                    invoices_sql = f"""
                        CREATE TABLE IF NOT EXISTS broker_invoices (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                            invoice_number VARCHAR(50) NOT NULL UNIQUE,
                            invoice_date DATE NOT NULL,
                            due_date DATE NOT NULL,
                            status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            subtotal REAL DEFAULT 0.0 NOT NULL,
                            tax_amount REAL DEFAULT 0.0 NOT NULL,
                            discount_amount REAL DEFAULT 0.0 NOT NULL,
                            total_amount REAL DEFAULT 0.0 NOT NULL,
                            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                            billing_period_start DATE,
                            billing_period_end DATE,
                            description TEXT,
                            notes TEXT,
                            paid_at DATETIME,
                            payment_method VARCHAR(50),
                            payment_gateway VARCHAR(50),
                            gateway_transaction_id VARCHAR(255),
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    invoices_sql = f"""
                        CREATE TABLE IF NOT EXISTS broker_invoices (
                            {pk_sql},
                            user_id INT NOT NULL,
                            subscription_id INT NULL,
                            invoice_number VARCHAR(50) NOT NULL UNIQUE,
                            invoice_date DATE NOT NULL,
                            due_date DATE NOT NULL,
                            status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            subtotal REAL DEFAULT 0.0 NOT NULL,
                            tax_amount REAL DEFAULT 0.0 NOT NULL,
                            discount_amount REAL DEFAULT 0.0 NOT NULL,
                            total_amount REAL DEFAULT 0.0 NOT NULL,
                            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                            billing_period_start DATE,
                            billing_period_end DATE,
                            description TEXT,
                            notes TEXT,
                            paid_at DATETIME,
                            payment_method VARCHAR(50),
                            payment_gateway VARCHAR(50),
                            gateway_transaction_id VARCHAR(255),
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                            FOREIGN KEY (subscription_id) REFERENCES broker_subscriptions(id) ON DELETE SET NULL
                        )
                    """
                else:
                    invoices_sql = f"""
                        CREATE TABLE IF NOT EXISTS broker_invoices (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            subscription_id INTEGER NULL REFERENCES broker_subscriptions(id) ON DELETE SET NULL,
                            invoice_number VARCHAR(50) NOT NULL UNIQUE,
                            invoice_date DATE NOT NULL,
                            due_date DATE NOT NULL,
                            status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            subtotal REAL DEFAULT 0.0 NOT NULL,
                            tax_amount REAL DEFAULT 0.0 NOT NULL,
                            discount_amount REAL DEFAULT 0.0 NOT NULL,
                            total_amount REAL DEFAULT 0.0 NOT NULL,
                            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                            billing_period_start DATE,
                            billing_period_end DATE,
                            description TEXT,
                            notes TEXT,
                            paid_at DATETIME,
                            payment_method VARCHAR(50),
                            payment_gateway VARCHAR(50),
                            gateway_transaction_id VARCHAR(255),
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
                        )
                    """
                
                conn.execute(text(invoices_sql))
                
                # Create indexes for broker_invoices table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_user_id ON broker_invoices(user_id)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_subscription_id ON broker_invoices(subscription_id)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_invoice_number ON broker_invoices(invoice_number)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_invoice_date ON broker_invoices(invoice_date)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_due_date ON broker_invoices(due_date)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_status ON broker_invoices(status)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_currency ON broker_invoices(currency)",
                    "CREATE INDEX IF NOT EXISTS ix_broker_invoices_composite ON broker_invoices(user_id, status, invoice_date)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Broker invoices table created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create broker_invoices table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop broker_invoices table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS broker_invoices"))
                conn.commit()
                
            logger.info("Broker invoices table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop broker_invoices table: {e}")
            return False


class Migration_058_FixCurrenciesTableForMySQL(DatabaseMigration):
    """Fix currencies table for MySQL - add missing columns and indexes"""

    def __init__(self):
        super().__init__("058", "Fix currencies table for MySQL - add missing columns")

    def up(self, db_manager) -> bool:
        """Add missing columns to currencies table if they don't exist"""
        try:
            dialect = db_manager.engine.dialect.name
            
            # Only needed for MySQL/MariaDB
            if dialect not in ('mysql', 'mariadb'):
                logger.info("Migration 058 skipped - not needed for SQLite")
                return True
            
            with db_manager.engine.connect() as conn:
                # Check if currencies table exists
                result = conn.execute(text("""
                    SELECT COUNT(*) FROM information_schema.tables
                    WHERE table_schema = DATABASE() AND table_name = 'currencies'
                """))
                table_exists = result.scalar() > 0
                
                if not table_exists:
                    logger.info("Currencies table doesn't exist - nothing to fix")
                    return True
                
                # Get existing columns
                existing_columns = get_table_columns(db_manager, 'currencies')
                logger.info(f"Currencies table exists with columns: {existing_columns}")
                
                # Add missing columns
                columns_to_add = []
                
                if 'is_crypto' not in existing_columns:
                    columns_to_add.append(('is_crypto', 'BOOLEAN DEFAULT FALSE NOT NULL'))
                
                if 'currency_type' not in existing_columns:
                    columns_to_add.append(('currency_type', "VARCHAR(20) NOT NULL DEFAULT 'fiat'"))
                
                if 'decimal_places' not in existing_columns:
                    columns_to_add.append(('decimal_places', 'INT DEFAULT 2 NOT NULL'))
                
                if 'min_deposit' not in existing_columns:
                    columns_to_add.append(('min_deposit', 'REAL DEFAULT 0.0 NOT NULL'))
                
                if 'max_deposit' not in existing_columns:
                    columns_to_add.append(('max_deposit', 'REAL DEFAULT 1000000.0 NOT NULL'))
                
                if 'min_withdrawal' not in existing_columns:
                    columns_to_add.append(('min_withdrawal', 'REAL DEFAULT 0.0 NOT NULL'))
                
                if 'max_withdrawal' not in existing_columns:
                    columns_to_add.append(('max_withdrawal', 'REAL DEFAULT 1000000.0 NOT NULL'))
                
                if 'networks' not in existing_columns:
                    columns_to_add.append(('networks', 'TEXT'))
                
                # Add each column
                for col_name, col_def in columns_to_add:
                    logger.info(f"Adding missing column {col_name} to currencies table")
                    conn.execute(text(f"""
                        ALTER TABLE currencies
                        ADD COLUMN {col_name} {col_def}
                    """))
                
                # Commit column changes before creating indexes
                if columns_to_add:
                    conn.commit()
                
                # Create indexes for columns that should exist now
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_currencies_code ON currencies(code)",
                    "CREATE INDEX IF NOT EXISTS ix_currencies_currency_type ON currencies(currency_type)",
                    "CREATE INDEX IF NOT EXISTS ix_currencies_is_active ON currencies(is_active)",
                ]
                
                # Refresh column list after adding
                updated_columns = get_table_columns(db_manager, 'currencies')
                
                if 'is_crypto' in updated_columns:
                    indexes.append("CREATE INDEX IF NOT EXISTS ix_currencies_is_crypto ON currencies(is_crypto)")
                
                for index_sql in indexes:
                    try:
                        conn.execute(text(index_sql))
                    except Exception as idx_e:
                        logger.warning(f"Could not create index (may already exist): {idx_e}")
                
                conn.commit()
                
            logger.info("Currencies table fixed for MySQL")
            return True
        except Exception as e:
            logger.error(f"Failed to fix currencies table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Cannot easily remove columns in MySQL"""
        logger.warning("Cannot remove columns in MySQL - columns will remain")
        return True


class Migration_062_AddRealNameAndAvatarToUsers(DatabaseMigration):
    """Add real_name and avatar_url fields to users table for OAuth profile data"""

    def __init__(self):
        super().__init__("062", "Add real_name and avatar_url fields to users table")

    def up(self, db_manager) -> bool:
        """Add real_name and avatar_url columns to users table"""
        try:
            # Check if columns already exist
            columns = get_table_columns(db_manager, 'users')
            with db_manager.engine.connect() as conn:
                
                if 'real_name' not in columns:
                    # Add real_name column (user's actual name from OAuth)
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN real_name VARCHAR(255) NULL
                    """))
                    logger.info("real_name column added to users table")
                
                if 'avatar_url' not in columns:
                    # Add avatar_url column (profile picture URL from OAuth/Gravatar)
                    conn.execute(text("""
                        ALTER TABLE users
                        ADD COLUMN avatar_url VARCHAR(500) NULL
                    """))
                    logger.info("avatar_url column added to users table")
                
                # Add indexes
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_users_real_name ON users(real_name)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Real name and avatar fields added to users table successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add real_name and avatar_url fields to users table: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - fields will remain")
        return True


class Migration_063_AddSubscriptionPlanFields(DatabaseMigration):
    """Add new fields to subscription_plans table for enhanced features"""

    def __init__(self):
        super().__init__("063", "Add api_requests_per_month, is_featured, contact_for_pricing to subscription_plans")

    def up(self, db_manager) -> bool:
        """Add new columns to subscription_plans table"""
        try:
            columns = get_table_columns(db_manager, 'subscription_plans')
            with db_manager.engine.connect() as conn:
                
                if 'api_requests_per_month' not in columns:
                    conn.execute(text("""
                        ALTER TABLE subscription_plans
                        ADD COLUMN api_requests_per_month INTEGER DEFAULT 0 NOT NULL
                    """))
                    logger.info("api_requests_per_month column added to subscription_plans table")
                
                if 'is_featured' not in columns:
                    conn.execute(text("""
                        ALTER TABLE subscription_plans
                        ADD COLUMN is_featured BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    logger.info("is_featured column added to subscription_plans table")
                
                if 'contact_for_pricing' not in columns:
                    conn.execute(text("""
                        ALTER TABLE subscription_plans
                        ADD COLUMN contact_for_pricing BOOLEAN DEFAULT FALSE NOT NULL
                    """))
                    logger.info("contact_for_pricing column added to subscription_plans table")
                
                # Mark professional plan as featured by default
                conn.execute(text("""
                    UPDATE subscription_plans
                    SET is_featured = TRUE
                    WHERE plan_type = 'professional'
                """))
                
                conn.commit()
                
            logger.info("Subscription plan fields added successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to add subscription plan fields: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - fields will remain")
        return True


class Migration_064_AddIsPrimaryToCurrencies(DatabaseMigration):
    """Add is_primary column to currencies table"""

    def __init__(self):
        super().__init__("064", "Add is_primary column to currencies table")

    def up(self, db_manager) -> bool:
        """Add is_primary column to currencies table"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if column already exists
                columns = get_table_columns(db_manager, 'currencies')
                
                if 'is_primary' not in columns:
                    logger.info("Adding is_primary column to currencies table")
                    conn.execute(text("""
                        ALTER TABLE currencies ADD COLUMN is_primary BOOLEAN DEFAULT 0 NOT NULL
                    """))
                    conn.commit()
                    logger.info("is_primary column added to currencies table")
                else:
                    logger.info("is_primary column already exists in currencies table")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add is_primary column: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove column - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - field will remain")
        return True


class Migration_065_AddDefaultStreamingConfig(DatabaseMigration):
    """Add default streaming configuration for HLS video streaming"""

    def __init__(self):
        super().__init__("065", "Add default streaming configuration for HLS video streaming")

    def up(self, db_manager) -> bool:
        """Add default streaming configuration"""
        try:
            insert_ignore = get_insert_ignore_sql(db_manager)
            timestamp_sql = get_current_timestamp_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Check if streaming section exists
                result = conn.execute(text("""
                    SELECT COUNT(*) FROM configuration WHERE `key` LIKE 'streaming.%'
                """))
                existing_count = result.scalar()
                
                if existing_count == 0:
                    # Add default streaming configuration values
                    streaming_defaults = [
                        ('streaming.enabled', 'true', 'boolean', 'Enable HLS video streaming'),
                        ('streaming.port', '5884', 'integer', 'Port for HLS streaming server'),
                        ('streaming.framerate', '25', 'integer', 'Video framerate (FPS)'),
                        ('streaming.segment_duration', '2', 'integer', 'HLS segment duration in seconds'),
                        ('streaming.video_bitrate_high', '10000', 'integer', 'High quality video bitrate in kbps (1080p). Medium=2/3, Low=1/3 of this value'),
                        ('streaming.video_hostname', '', 'string', 'Optional separate hostname for video streaming'),
                        ('streaming.video_port', '', 'string', 'Optional separate port for video streaming'),
                        ('streaming.video_use_ssl', '', 'string', 'Use SSL for video streaming (true/false/empty for same as web)'),
                        ('streaming.cors_enabled', 'true', 'boolean', 'Enable CORS for HLS streaming'),
                        ('streaming.cors_origins', '*', 'string', 'Allowed CORS origins for HLS streaming'),
                    ]
                    
                    for key, value, value_type, description in streaming_defaults:
                        conn.execute(text(f"""
                            {insert_ignore} INTO configuration
                            (`key`, value, value_type, description, is_system, created_at, updated_at)
                            VALUES (:key, :value, :value_type, :description, 0, {timestamp_sql}, {timestamp_sql})
                        """), {
                            'key': key,
                            'value': value,
                            'value_type': value_type,
                            'description': description
                        })
                    
                    conn.commit()
                    logger.info("Default streaming configuration added")
                else:
                    logger.info("Streaming configuration already exists, skipping defaults")
                
            return True
        except Exception as e:
            logger.error(f"Failed to add default streaming configuration: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Remove default streaming configuration"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("""
                    DELETE FROM configuration WHERE `key` LIKE 'streaming.%'
                """))
                conn.commit()
            
            logger.info("Default streaming configuration removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default streaming configuration: {e}")
            return False


class Migration_066_AddWalletTables(DatabaseMigration):
    """Add wallets and wallet_transactions tables for broker and player wallet functionality"""

    def __init__(self):
        super().__init__("066", "Add wallets and wallet_transactions tables for broker and player wallet functionality")

    def up(self, db_manager) -> bool:
        """Create wallets and wallet_transactions tables"""
        try:
            dialect = db_manager.engine.dialect.name
            pk_sql = get_primary_key_sql(db_manager)
            
            with db_manager.engine.connect() as conn:
                # Create wallets table
                if dialect == 'sqlite':
                    wallets_sql = f"""
                        CREATE TABLE IF NOT EXISTS wallets (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id),
                            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                            balance REAL DEFAULT 0.0 NOT NULL,
                            pending_balance REAL DEFAULT 0.0 NOT NULL,
                            total_deposited REAL DEFAULT 0.0 NOT NULL,
                            total_withdrawn REAL DEFAULT 0.0 NOT NULL,
                            total_wagered REAL DEFAULT 0.0 NOT NULL,
                            total_winnings REAL DEFAULT 0.0 NOT NULL,
                            is_active BOOLEAN DEFAULT 1 NOT NULL,
                            is_primary BOOLEAN DEFAULT 1 NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            UNIQUE(user_id, currency)
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    wallets_sql = f"""
                        CREATE TABLE IF NOT EXISTS wallets (
                            {pk_sql},
                            user_id INT NOT NULL,
                            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                            balance DOUBLE DEFAULT 0.0 NOT NULL,
                            pending_balance DOUBLE DEFAULT 0.0 NOT NULL,
                            total_deposited DOUBLE DEFAULT 0.0 NOT NULL,
                            total_withdrawn DOUBLE DEFAULT 0.0 NOT NULL,
                            total_wagered DOUBLE DEFAULT 0.0 NOT NULL,
                            total_winnings DOUBLE DEFAULT 0.0 NOT NULL,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            is_primary BOOLEAN DEFAULT TRUE NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            UNIQUE(user_id, currency),
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                        )
                    """
                else:
                    wallets_sql = f"""
                        CREATE TABLE IF NOT EXISTS wallets (
                            {pk_sql},
                            user_id INTEGER NOT NULL REFERENCES users(id),
                            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
                            balance REAL DEFAULT 0.0 NOT NULL,
                            pending_balance REAL DEFAULT 0.0 NOT NULL,
                            total_deposited REAL DEFAULT 0.0 NOT NULL,
                            total_withdrawn REAL DEFAULT 0.0 NOT NULL,
                            total_wagered REAL DEFAULT 0.0 NOT NULL,
                            total_winnings REAL DEFAULT 0.0 NOT NULL,
                            is_active BOOLEAN DEFAULT TRUE NOT NULL,
                            is_primary BOOLEAN DEFAULT TRUE NOT NULL,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            UNIQUE(user_id, currency)
                        )
                    """
                
                conn.execute(text(wallets_sql))
                
                # Create wallet_transactions table
                if dialect == 'sqlite':
                    transactions_sql = f"""
                        CREATE TABLE IF NOT EXISTS wallet_transactions (
                            {pk_sql},
                            wallet_id INTEGER NOT NULL REFERENCES wallets(id) ON DELETE CASCADE,
                            transaction_type VARCHAR(50) NOT NULL,
                            amount REAL NOT NULL,
                            balance_before REAL NOT NULL,
                            balance_after REAL NOT NULL,
                            status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            description TEXT,
                            reference_id VARCHAR(255),
                            reference_type VARCHAR(50),
                            payment_gateway VARCHAR(100),
                            payment_gateway_transaction_id VARCHAR(255),
                            payment_details TEXT,
                            processed_by INTEGER REFERENCES users(id),
                            notes TEXT,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                        )
                    """
                elif dialect in ('mysql', 'mariadb'):
                    transactions_sql = f"""
                        CREATE TABLE IF NOT EXISTS wallet_transactions (
                            {pk_sql},
                            wallet_id INT NOT NULL,
                            transaction_type VARCHAR(50) NOT NULL,
                            amount DOUBLE NOT NULL,
                            balance_before DOUBLE NOT NULL,
                            balance_after DOUBLE NOT NULL,
                            status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            description TEXT,
                            reference_id VARCHAR(255),
                            reference_type VARCHAR(50),
                            payment_gateway VARCHAR(100),
                            payment_gateway_transaction_id VARCHAR(255),
                            payment_details JSON,
                            processed_by INT,
                            notes TEXT,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
                            FOREIGN KEY (processed_by) REFERENCES users(id)
                        )
                    """
                else:
                    transactions_sql = f"""
                        CREATE TABLE IF NOT EXISTS wallet_transactions (
                            {pk_sql},
                            wallet_id INTEGER NOT NULL REFERENCES wallets(id) ON DELETE CASCADE,
                            transaction_type VARCHAR(50) NOT NULL,
                            amount REAL NOT NULL,
                            balance_before REAL NOT NULL,
                            balance_after REAL NOT NULL,
                            status VARCHAR(20) DEFAULT 'pending' NOT NULL,
                            description TEXT,
                            reference_id VARCHAR(255),
                            reference_type VARCHAR(50),
                            payment_gateway VARCHAR(100),
                            payment_gateway_transaction_id VARCHAR(255),
                            payment_details TEXT,
                            processed_by INTEGER REFERENCES users(id),
                            notes TEXT,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                        )
                    """
                
                conn.execute(text(transactions_sql))
                
                # Create indexes for wallets table
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_wallets_user_id ON wallets(user_id)",
                    "CREATE INDEX IF NOT EXISTS ix_wallets_currency ON wallets(currency)",
                    "CREATE INDEX IF NOT EXISTS ix_wallets_is_active ON wallets(is_active)",
                ]
                
                # Create indexes for wallet_transactions table
                indexes.extend([
                    "CREATE INDEX IF NOT EXISTS ix_wallet_transactions_wallet_id ON wallet_transactions(wallet_id)",
                    "CREATE INDEX IF NOT EXISTS ix_wallet_transactions_transaction_type ON wallet_transactions(transaction_type)",
                    "CREATE INDEX IF NOT EXISTS ix_wallet_transactions_status ON wallet_transactions(status)",
                    "CREATE INDEX IF NOT EXISTS ix_wallet_transactions_created_at ON wallet_transactions(created_at)",
                    "CREATE INDEX IF NOT EXISTS ix_wallet_transactions_reference_id ON wallet_transactions(reference_id)",
                    "CREATE INDEX IF NOT EXISTS ix_wallet_transactions_reference_type ON wallet_transactions(reference_type)",
                ])
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Wallets and wallet_transactions tables created successfully")
            return True
        except Exception as e:
            logger.error(f"Failed to create wallet tables: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Drop wallets and wallet_transactions tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Drop tables in reverse order (child first due to foreign keys)
                conn.execute(text("DROP TABLE IF EXISTS wallet_transactions"))
                conn.execute(text("DROP TABLE IF EXISTS wallets"))
                
                conn.commit()
                
            logger.info("Wallets and wallet_transactions tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop wallet tables: {e}")
            return False


class Migration_067_FixMatchResults(DatabaseMigration):
    """Fix match results that have UNDER/OVER in result field instead of actual fight winner"""

    def __init__(self):
        super().__init__("067", "Fix match results that have UNDER/OVER in result field")

    def up(self, db_manager) -> bool:
        """Fix matches where result contains UNDER or OVER instead of actual fight winner
        
        The winning_outcomes column is stored as a double-encoded JSON string:
        - Outer layer: JSON string (e.g., '"[\"KO2\", \"WIN1\"]"')
        - Inner layer: JSON array of outcome names
        
        We need to:
        1. Extract the first element from the inner JSON array
        2. Set it as the result (the actual fight winner)
        3. The under_over_result should already be set to UNDER/OVER
        """
        try:
            dialect = db_manager.engine.dialect.name
            with db_manager.engine.connect() as conn:
                fixed_count = 0
                
                if dialect in ('mysql', 'mariadb'):
                    # MySQL/MariaDB: Use JSON functions to extract the first element
                    # winning_outcomes is stored as: "[\"KO2\", \"WIN1\"]" (JSON string of JSON array)
                    # JSON_UNQUOTE removes the outer quotes, then JSON_EXTRACT gets the first element
                    result = conn.execute(text("""
                        UPDATE matches
                        SET result = JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(winning_outcomes), '$[0]'))
                        WHERE under_over_result IN ('UNDER', 'OVER')
                          AND winning_outcomes IS NOT NULL
                          AND JSON_VALID(winning_outcomes)
                    """))
                    fixed_count = result.rowcount
                else:
                    # SQLite: Use Python to parse the JSON
                    import json
                    result = conn.execute(text("""
                        SELECT id, result, winning_outcomes, under_over_result
                        FROM matches
                        WHERE under_over_result IN ('UNDER', 'OVER')
                        AND winning_outcomes IS NOT NULL
                    """))
                    
                    matches_to_fix = result.fetchall()
                    
                    for match in matches_to_fix:
                        match_id = match[0]
                        winning_outcomes_json = match[2]
                        
                        try:
                            # Parse the double-encoded JSON
                            winning_outcomes = json.loads(winning_outcomes_json)
                            if isinstance(winning_outcomes, list) and len(winning_outcomes) > 0:
                                new_result = winning_outcomes[0]
                                if new_result not in ('UNDER', 'OVER'):
                                    conn.execute(text("""
                                        UPDATE matches
                                        SET result = :new_result
                                        WHERE id = :match_id
                                    """), {
                                        'new_result': new_result,
                                        'match_id': match_id
                                    })
                                    fixed_count += 1
                        except (json.JSONDecodeError, TypeError):
                            pass
                
                conn.commit()
                
            logger.info(f"Fixed {fixed_count} matches with incorrect result values")
            return True
        except Exception as e:
            logger.error(f"Failed to fix match results: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Cannot reliably rollback this migration as we don't store the original incorrect values"""
        logger.warning("Cannot rollback Migration_067_FixMatchResults - original incorrect values not preserved")
        return True


class Migration_068_ResetRedistributionBalance(DatabaseMigration):
    """Reset redistribution balance to zero - one-time migration for bug fix"""

    def __init__(self):
        super().__init__("068", "Reset redistribution balance to zero")

    def up(self, db_manager) -> bool:
        """Reset the accumulated_shortfall in persistent_redistribution_adjustments table to 0
        
        This is a one-time migration to reset the redistribution balance after fixing
        the bug where over-distribution values were incorrectly added instead of subtracted.
        """
        try:
            from datetime import date
            with db_manager.engine.connect() as conn:
                global_date = date(1970, 1, 1)
                
                # Check if the global record exists
                result = conn.execute(text("""
                    SELECT id, accumulated_shortfall 
                    FROM persistent_redistribution_adjustments 
                    WHERE date = :global_date
                """), {'global_date': global_date})
                
                record = result.fetchone()
                if record:
                    old_value = record[1]
                    # Reset accumulated_shortfall to 0
                    conn.execute(text("""
                        UPDATE persistent_redistribution_adjustments
                        SET accumulated_shortfall = 0.0
                        WHERE date = :global_date
                    """), {'global_date': global_date})
                    conn.commit()
                    logger.info(f"Reset redistribution balance from {old_value} to 0.0")
                else:
                    logger.info("No global redistribution record found - nothing to reset")
                
            return True
        except Exception as e:
            logger.error(f"Failed to reset redistribution balance: {e}")
            return False

    def down(self, db_manager) -> bool:
        """Cannot rollback this migration as we don't store the original balance value"""
        logger.warning("Cannot rollback Migration_068_ResetRedistributionBalance - original balance value not preserved")
        return True


class Migration_069_RepairExtractionStatsFinancials(DatabaseMigration):
    """Repair historical extraction stats financial fields from authoritative bet details"""

    def __init__(self):
        super().__init__("069", "Repair extraction stats financial fields")

    def _snapshot_financial_fields(self, stat_row):
        return {
            'total_bets': getattr(stat_row, 'total_bets', 0),
            'total_amount_collected': getattr(stat_row, 'total_amount_collected', 0.0),
            'total_redistributed': getattr(stat_row, 'total_redistributed', 0.0),
            'under_bets': getattr(stat_row, 'under_bets', 0),
            'under_amount': getattr(stat_row, 'under_amount', 0.0),
            'over_bets': getattr(stat_row, 'over_bets', 0),
            'over_amount': getattr(stat_row, 'over_amount', 0.0),
            'result_breakdown': getattr(stat_row, 'result_breakdown', None),
        }

    def _build_delta_log(self, match_id, before_values, after_values):
        changes = []
        for field_name, before_value in before_values.items():
            after_value = after_values[field_name]
            if before_value != after_value:
                changes.append(f"{field_name}: {before_value} -> {after_value}")

        if not changes:
            return None

        return f"Repaired extraction stats row for match_id={match_id}: " + ", ".join(changes)

    def _rebuild_result_breakdown(self, stat_row, active_details):
        breakdown = {}

        for detail in active_details:
            outcome = getattr(detail, 'outcome', None)
            if not outcome:
                continue

            if outcome not in breakdown:
                breakdown[outcome] = {'bets': 0, 'amount': 0.0}

            breakdown[outcome]['bets'] += 1
            breakdown[outcome]['amount'] += float(getattr(detail, 'amount', 0) or 0)

        stat_row.result_breakdown = breakdown

    def _recompute_stat_row(self, stat_row, detail_rows):
        active_details = [detail for detail in detail_rows if getattr(detail, 'result', None) != 'cancelled']

        stat_row.total_bets = len(active_details)
        stat_row.total_amount_collected = sum(float(getattr(detail, 'amount', 0) or 0) for detail in active_details)
        stat_row.total_redistributed = sum(
            float(getattr(detail, 'win_amount', 0) or 0)
            for detail in active_details
            if getattr(detail, 'result', None) == 'win'
        )
        stat_row.under_bets = sum(1 for detail in active_details if getattr(detail, 'outcome', None) == 'UNDER')
        stat_row.under_amount = sum(
            float(getattr(detail, 'amount', 0) or 0)
            for detail in active_details
            if getattr(detail, 'outcome', None) == 'UNDER'
        )
        stat_row.over_bets = sum(1 for detail in active_details if getattr(detail, 'outcome', None) == 'OVER')
        stat_row.over_amount = sum(
            float(getattr(detail, 'amount', 0) or 0)
            for detail in active_details
            if getattr(detail, 'outcome', None) == 'OVER'
        )
        self._rebuild_result_breakdown(stat_row, active_details)
        return stat_row

    def up(self, db_manager) -> bool:
        session = None
        try:
            from .models import ExtractionStatsModel, BetDetailModel

            session = db_manager.get_session()
            stats_rows = session.query(ExtractionStatsModel).all()
            modified_rows = 0
            match_counts = {}

            for stat_row in stats_rows:
                match_counts[stat_row.match_id] = match_counts.get(stat_row.match_id, 0) + 1

            for match_id, row_count in match_counts.items():
                if row_count > 1:
                    logger.warning(f"Duplicate extraction stats rows detected for match_id {match_id}: {row_count} rows")

            for stat_row in stats_rows:
                detail_rows = session.query(BetDetailModel).filter_by(match_id=stat_row.match_id).all()
                before_values = self._snapshot_financial_fields(stat_row)
                self._recompute_stat_row(stat_row, detail_rows)
                after_values = self._snapshot_financial_fields(stat_row)
                delta_log = self._build_delta_log(stat_row.match_id, before_values, after_values)
                if delta_log:
                    modified_rows += 1
                    logger.info(delta_log)

            session.commit()
            logger.info(f"Modified {modified_rows} extraction stats rows out of {len(stats_rows)} processed")
            return True
        except Exception as e:
            logger.error(f"Failed to repair extraction stats financial fields: {e}")
            if session:
                session.rollback()
            return False
        finally:
            if session:
                session.close()

    def down(self, db_manager) -> bool:
        """Cannot rollback this migration because prior incorrect values are not preserved"""
        logger.warning("Cannot rollback Migration_069_RepairExtractionStatsFinancials - original financial values not preserved")
        return True


MIGRATIONS: List[DatabaseMigration] = [
    Migration_001_InitialSchema(),
    Migration_002_AddIndexes(),
    Migration_003_AddTemplateVersioning(),
    Migration_004_AddUserPreferences(),
    Migration_005_CreateDefaultAdminUser(),
    Migration_006_AddUserRoles(),
    Migration_007_CreateDefaultCashierUser(),
    Migration_008_AddMatchTables(),
    Migration_009_AddDoneFieldToMatches(),
    Migration_010_AddRunningFieldToMatches(),
    Migration_011_AddFixtureActiveTimeToMatches(),
    Migration_012_RemoveFixtureIdUniqueConstraint(),
    Migration_013_AddStatusFieldToMatches(),
    Migration_014_AddExtractionAndGameConfigTables(),
    Migration_015_AddBettingModeTable(),
    Migration_016_ConvertBettingModeToGlobal(),
    Migration_017_AddBettingTables(),
    Migration_018_RemoveExtractionAssociationUniqueConstraint(),
    Migration_019_AddPaidFieldToBets(),
    Migration_020_FixBetDetailsForeignKey(),
    Migration_021_AddBarcodeConfiguration(),
    Migration_022_AddQRCodeConfiguration(),
    Migration_023_AddAvailableBetsTable(),
    Migration_024_AddResultOptionsTable(),
    Migration_025_AddResultOptionModel(),
    Migration_026_AddExtractionStatsTable(),
    Migration_027_AddDefaultIntroTemplatesConfig(),
    Migration_028_AddFixtureRefreshIntervalConfig(),
    Migration_029_ChangeMatchNumberToUniqueWithinFixture(),
    Migration_030_AddZipValidationStatus(),
    Migration_031_AddWinningOutcomesFields(),
    Migration_032_FixExtractionAssociationDefaults(),
    Migration_033_AddBarcodeFieldsToBets(),
    Migration_034_AddDefaultLicenseText(),
    Migration_035_AddDailyRedistributionShortfallTable(),
    Migration_036_AddMatchTemplatesTables(),
    Migration_037_RenameDailyRedistributionShortfallTable(),
    Migration_038_AddWin1Win2Associations(),
    Migration_039_AddMatchNumberToBetDetails(),
    Migration_040_AddAccumulatedShortfallToMatches(),
    Migration_041_AddCapPercentToMatches(),
    Migration_042_AddOAuthAndEmailSettings(),
    Migration_043_AddBetOriginTracking(),
    Migration_044_CreateOAuthProvidersTable(),
    Migration_045_CreateUserOAuthAccountsTable(),
    Migration_046_CreateCurrenciesTable(),
    Migration_047_CreateExchangeRateHistoryTable(),
    Migration_048_CreateExchangeRateProvidersTable(),
    Migration_049_CreateBrokerSubscriptionsTable(),
    Migration_050_CreateSubscriptionPlansTable(),
    Migration_051_CreateBrokerTransactionsTable(),
    Migration_052_CreateBrokerUsageStatsTable(),
    Migration_053_CreateTimezonesTable(),
    Migration_054_CreatePaymentGatewaysTable(),
    Migration_055_CreateBrokerInvoicesTable(),
    Migration_056_AddBrokerFieldsToUsers(),
    Migration_057_AddBrokerFieldsToApiKeys(),
    Migration_058_FixCurrenciesTableForMySQL(),
    Migration_059_InsertDefaultSubscriptionPlans(),
    Migration_060_EnsureDefaultSubscriptionPlans(),
    Migration_061_RemoveFakeSubscriptionPlans(),
    Migration_062_AddRealNameAndAvatarToUsers(),
    Migration_063_AddSubscriptionPlanFields(),
    Migration_064_AddIsPrimaryToCurrencies(),
    Migration_065_AddDefaultStreamingConfig(),
    Migration_066_AddWalletTables(),
    Migration_067_FixMatchResults(),
    Migration_068_ResetRedistributionBalance(),
    Migration_069_RepairExtractionStatsFinancials(),
]


def get_applied_migrations(db_manager) -> List[str]:
    """Get list of applied migration versions"""
    session = None
    try:
        session = db_manager.get_session()
        
        versions = session.query(DatabaseVersion.version).all()
        return [v[0] for v in versions]
        
    except Exception as e:
        logger.error(f"Failed to get applied migrations: {e}")
        return []
    finally:
        if session:
            session.close()


def mark_migration_applied(db_manager, migration: DatabaseMigration) -> bool:
    """Mark migration as applied"""
    session = None
    try:
        session = db_manager.get_session()
        
        # Check if already applied
        existing = session.query(DatabaseVersion).filter_by(version=migration.version).first()
        
        if not existing:
            db_version = DatabaseVersion(
                version=migration.version,
                description=migration.description,
                applied_at=datetime.utcnow()
            )
            
            session.add(db_version)
            session.commit()
            
            logger.info(f"Migration {migration.version} marked as applied")
        
        return True
        
    except Exception as e:
        logger.error(f"Failed to mark migration as applied: {e}")
        if session:
            session.rollback()
        return False
    finally:
        if session:
            session.close()


def unmark_migration_applied(db_manager, version: str) -> bool:
    """Remove migration from applied list"""
    session = None
    try:
        session = db_manager.get_session()
        
        migration = session.query(DatabaseVersion).filter_by(version=version).first()
        
        if migration:
            session.delete(migration)
            session.commit()
            
            logger.info(f"Migration {version} unmarked as applied")
        
        return True
        
    except Exception as e:
        logger.error(f"Failed to unmark migration: {e}")
        if session:
            session.rollback()
        return False
    finally:
        if session:
            session.close()


def run_migrations(db_manager) -> bool:
    """Run all pending migrations"""
    try:
        applied_versions = get_applied_migrations(db_manager)
        
        logger.info(f"Applied migrations: {applied_versions}")
        
        success = True
        applied_count = 0
        
        for migration in MIGRATIONS:
            if migration.version not in applied_versions:
                logger.info(f"Applying migration: {migration}")
                
                if migration.up(db_manager):
                    if mark_migration_applied(db_manager, migration):
                        applied_count += 1
                        logger.info(f"Migration {migration.version} applied successfully")
                    else:
                        logger.error(f"Failed to mark migration {migration.version} as applied")
                        success = False
                        break
                else:
                    logger.error(f"Migration {migration.version} failed")
                    success = False
                    break
            else:
                logger.debug(f"Migration {migration.version} already applied")
        
        if success:
            if applied_count > 0:
                logger.info(f"Successfully applied {applied_count} migrations")
            else:
                logger.info("All migrations up to date")
        else:
            logger.error("Migration process failed")
        
        return success
        
    except Exception as e:
        logger.error(f"Migration process failed: {e}")
        return False


def rollback_migration(db_manager, version: str) -> bool:
    """Rollback a specific migration"""
    try:
        # Find the migration
        migration = None
        for m in MIGRATIONS:
            if m.version == version:
                migration = m
                break
        
        if not migration:
            logger.error(f"Migration {version} not found")
            return False
        
        # Check if it's applied
        applied_versions = get_applied_migrations(db_manager)
        
        if version not in applied_versions:
            logger.error(f"Migration {version} is not applied")
            return False
        
        logger.info(f"Rolling back migration: {migration}")
        
        if migration.down(db_manager):
            if unmark_migration_applied(db_manager, version):
                logger.info(f"Migration {version} rolled back successfully")
                return True
            else:
                logger.error(f"Failed to unmark migration {version}")
                return False
        else:
            logger.error(f"Migration {version} rollback failed")
            return False
        
    except Exception as e:
        logger.error(f"Migration rollback failed: {e}")
        return False


def get_migration_status(db_manager) -> Dict[str, Any]:
    """Get current migration status"""
    try:
        applied_versions = get_applied_migrations(db_manager)
        
        status = {
            'total_migrations': len(MIGRATIONS),
            'applied_migrations': len(applied_versions),
            'pending_migrations': len(MIGRATIONS) - len(applied_versions),
            'applied_versions': applied_versions,
            'pending_versions': [],
            'migrations': []
        }
        
        for migration in MIGRATIONS:
            is_applied = migration.version in applied_versions
            
            migration_info = {
                'version': migration.version,
                'description': migration.description,
                'applied': is_applied,
                'applied_at': None
            }
            
            if is_applied:
                # Get applied date
                session = db_manager.get_session()
                try:
                    db_version = session.query(DatabaseVersion).filter_by(
                        version=migration.version
                    ).first()
                    if db_version:
                        migration_info['applied_at'] = db_version.applied_at.isoformat()
                finally:
                    session.close()
            else:
                status['pending_versions'].append(migration.version)
            
            status['migrations'].append(migration_info)
        
        return status
        
    except Exception as e:
        logger.error(f"Failed to get migration status: {e}")
        return {
            'error': str(e),
            'total_migrations': 0,
            'applied_migrations': 0,
            'pending_migrations': 0
        }
