"""
Database migration system for MbetterClient
"""

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

from .models import DatabaseVersion

logger = logging.getLogger(__name__)


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
                result = conn.execute(text("""
                    SELECT name FROM sqlite_master 
                    WHERE type='table' AND name NOT LIKE 'sqlite_%'
                """))
                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:
            with db_manager.engine.connect() as conn:
                # Check if columns already exist
                result = conn.execute(text("PRAGMA table_info(templates)"))
                columns = [row[1] for row in result.fetchall()]
                
                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:
            with db_manager.engine.connect() as conn:
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS user_preferences (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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)
                    )
                """))
                
                # 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
                    conn.execute(text("""
                        INSERT INTO users
                        (username, email, password_hash, is_active, is_admin, login_attempts, role, created_at, updated_at)
                        VALUES (:username, :email, :password_hash, 1, 1, 0, :role, datetime('now'), datetime('now'))
                    """), {
                        '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:
            with db_manager.engine.connect() as conn:
                # Check if role column already exists
                result = conn.execute(text("PRAGMA table_info(users)"))
                columns = [row[1] for row in result.fetchall()]
                
                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
                result = conn.execute(text("SELECT COUNT(*) FROM users WHERE username = 'cashier'"))
                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
                    conn.execute(text("""
                        INSERT INTO users
                        (username, email, password_hash, is_active, is_admin, login_attempts, role, created_at, updated_at)
                        VALUES (:username, :email, :password_hash, 1, 0, 0, :role, datetime('now'), datetime('now'))
                    """), {
                        '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:
            with db_manager.engine.connect() as conn:
                # Create matches table
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS matches (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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
                    )
                """))
                
                # Create match_outcomes table
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS match_outcomes (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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)
                    )
                """))
                
                # 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:
            with db_manager.engine.connect() as conn:
                # Check if done column already exists
                result = conn.execute(text("PRAGMA table_info(matches)"))
                columns = [row[1] for row in result.fetchall()]
                
                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:
            with db_manager.engine.connect() as conn:
                # Check if running column already exists
                result = conn.execute(text("PRAGMA table_info(matches)"))
                columns = [row[1] for row in result.fetchall()]
                
                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:
            with db_manager.engine.connect() as conn:
                # Check if fixture_active_time column already exists
                result = conn.execute(text("PRAGMA table_info(matches)"))
                columns = [row[1] for row in result.fetchall()]
                
                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:
            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 on fixture_id
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS matches_new (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE 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
                    )
                """))
                
                # Step 2: Copy data from old table to new table
                conn.execute(text("""
                    INSERT INTO matches_new
                    SELECT * 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"))
                
                # Step 5: 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:
            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
                
                # Recreate table with UNIQUE constraint on fixture_id
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS matches_new (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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,
                        done BOOLEAN DEFAULT FALSE NOT NULL,
                        running BOOLEAN DEFAULT FALSE 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,
                        
                        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"))
                
                # 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:
            with db_manager.engine.connect() as conn:
                # Check if status column already exists
                result = conn.execute(text("PRAGMA table_info(matches)"))
                columns = [row[1] for row in result.fetchall()]

                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:
            with db_manager.engine.connect() as conn:
                # Create extraction_associations table
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS extraction_associations (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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("""
                    CREATE TABLE IF NOT EXISTS game_config (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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 = [
                    ('WIN1', 'WIN1', True),
                    ('X1', 'WIN1', True),
                    ('K01', 'WIN1', True),
                    ('KO1', 'WIN1', True),
                    ('RET1', 'WIN1', True),
                    ('PTS1', 'WIN1', True),
                    ('DRAW', 'X', True),
                    ('12', 'X', True),
                    ('X1', 'X', True),
                    ('X2', 'X', True),
                    ('DKO', 'X', True),
                    ('WIN2', 'WIN2', True),
                    ('X2', 'WIN2', True),
                    ('K02', 'WIN2', True),
                    ('KO2', 'WIN2', True),
                    ('RET2', 'WIN2', True),
                    ('PTS2', 'WIN2', True),
                ]

                for outcome_name, extraction_result, is_default in default_associations:
                    conn.execute(text("""
                        INSERT OR IGNORE INTO extraction_associations
                        (outcome_name, extraction_result, is_default, created_at, updated_at)
                        VALUES (:outcome_name, :extraction_result, :is_default, datetime('now'), datetime('now'))
                    """), {
                        'outcome_name': outcome_name,
                        'extraction_result': extraction_result,
                        'is_default': is_default
                    })

                # Insert default game config
                conn.execute(text("""
                    INSERT OR 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, datetime('now'), datetime('now'))
                """), {
                    '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:
            with db_manager.engine.connect() as conn:
                # Create betting_modes table
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS betting_modes (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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
                result = conn.execute(text("""
                    SELECT name FROM sqlite_master
                    WHERE type='table' AND name='betting_modes'
                """))
                betting_modes_exists = result.fetchone() is not None

                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
                    conn.execute(text("""
                        INSERT OR REPLACE 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, datetime('now'), datetime('now'))
                    """), {
                        '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
                    conn.execute(text("""
                        INSERT OR 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, datetime('now'), datetime('now'))
                    """), {
                        '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:
            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("""
                    CREATE TABLE IF NOT EXISTS betting_modes (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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
                conn.execute(text("""
                    INSERT INTO betting_modes (user_id, mode, created_at, updated_at)
                    SELECT id, :mode, datetime('now'), datetime('now')
                    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:
            with db_manager.engine.connect() as conn:
                # Create bets table
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS bets (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        uuid VARCHAR(1024) 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,
                        FOREIGN KEY (fixture_id) REFERENCES matches (fixture_id)
                    )
                """))

                # Create bets_details table
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS bets_details (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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)
                    )
                """))

                # 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:
            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("""
                    CREATE TABLE IF NOT EXISTS extraction_associations_new (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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:
            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("""
                    CREATE TABLE IF NOT EXISTS extraction_associations_new (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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:
            with db_manager.engine.connect() as conn:
                # Check if paid column already exists
                result = conn.execute(text("PRAGMA table_info(bets)"))
                columns = [row[1] for row in result.fetchall()]
                
                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:
            with db_manager.engine.connect() as conn:
                # Check current structure of bets_details table
                result = conn.execute(text("PRAGMA table_info(bets_details)"))
                columns = {row[1]: row[2] for row in result.fetchall()}  # column_name: type
                
                # Check if bet_id is still INTEGER (needs to be changed to VARCHAR)
                if 'bet_id' in columns and 'INTEGER' in columns['bet_id'].upper():
                    logger.info("Converting bet_id from INTEGER to VARCHAR for UUID support")
                    
                    # Create new table with correct schema
                    conn.execute(text("""
                        CREATE TABLE IF NOT EXISTS bets_details_new (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            bet_id VARCHAR(1024) 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 (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"))
                
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS bets_details (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        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)
                    )
                """))
                
                # 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': 'false',
                        '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:
                        conn.execute(text("""
                            INSERT INTO configuration
                            (key, value, value_type, description, is_system, created_at, updated_at)
                            VALUES (:key, :value, :value_type, :description, :is_system, datetime('now'), datetime('now'))
                        """), 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

# Registry of all migrations in order
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(),
]


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
        }