"""
Games thread component for managing game-related operations
"""

import time
import logging
import threading
from datetime import datetime, timedelta
from typing import Optional, Dict, Any, List

from .thread_manager import ThreadedComponent
from .message_bus import MessageBus, Message, MessageType, MessageBuilder
from ..database.manager import DatabaseManager
from ..database.models import MatchModel, MatchStatus, BetDetailModel, MatchOutcomeModel, GameConfigModel, ExtractionAssociationModel

logger = logging.getLogger(__name__)


class GamesThread(ThreadedComponent):
    """Games thread for handling game operations and monitoring"""

    def __init__(self, name: str, message_bus: MessageBus, db_manager: DatabaseManager):
        super().__init__(name, message_bus)
        self.db_manager = db_manager
        self.current_fixture_id: Optional[str] = None
        self.game_active = False
        self._shutdown_event = threading.Event()
        self.message_queue = None

    def _cleanup_stale_ingame_matches(self):
        """Clean up any stale 'ingame' matches from previous crashed sessions and old 'bet' fixtures"""
        try:
            session = self.db_manager.get_session()
            try:
                # Get today's date
                today = datetime.now().date()

                # PART 1: Clean up stale 'ingame' matches from today (existing logic)
                stale_matches = session.query(MatchModel).filter(
                    MatchModel.start_time.isnot(None),
                    MatchModel.start_time >= datetime.combine(today, datetime.min.time()),
                    MatchModel.start_time < datetime.combine(today, datetime.max.time()),
                    MatchModel.status == 'ingame',
                    MatchModel.active_status == True
                ).all()

                if stale_matches:
                    logger.info(f"Found {len(stale_matches)} stale ingame matches - cleaning up")
                    for match in stale_matches:
                        logger.info(f"Cleaning up stale match {match.match_number}: {match.fighter1_township} vs {match.fighter2_township}")
                        match.status = 'pending'
                        match.active_status = False
                    session.commit()
                    logger.info(f"Cleaned up {len(stale_matches)} stale ingame matches")
                else:
                    logger.info("No stale ingame matches found")

                # PART 2: Clean up ALL old 'bet' fixtures (new logic)
                old_bet_matches = session.query(MatchModel).filter(
                    MatchModel.status == 'bet',
                    MatchModel.active_status == True,
                    # Exclude today's matches to avoid interfering with active games
                    ~MatchModel.start_time.between(
                        datetime.combine(today, datetime.min.time()),
                        datetime.combine(today, datetime.max.time())
                    )
                ).all()

                if old_bet_matches:
                    logger.info(f"Found {len(old_bet_matches)} old 'bet' matches - cancelling them")

                    for match in old_bet_matches:
                        logger.info(f"Cancelling old bet match {match.match_number}: {match.fighter1_township} vs {match.fighter2_township}")
                        match.status = 'cancelled'

                        # Cancel/refund associated bets
                        self._cancel_match_bets(match.id, session)

                    session.commit()
                    logger.info(f"Cancelled {len(old_bet_matches)} old bet matches")
                else:
                    logger.info("No old bet matches found to cancel")

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to cleanup stale matches: {e}")

    def _cancel_match_bets(self, match_id: int, session):
        """Cancel all pending bets for a match"""
        try:
            # Update all pending bets for this match to 'cancelled'
            cancelled_count = session.query(BetDetailModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.result == 'pending'
            ).update({'result': 'cancelled'})

            if cancelled_count > 0:
                logger.info(f"Cancelled {cancelled_count} pending bets for match {match_id}")

        except Exception as e:
            logger.error(f"Failed to cancel bets for match {match_id}: {e}")

    def initialize(self) -> bool:
        """Initialize the games thread"""
        try:
            logger.info("Initializing GamesThread...")

            # Clean up any stale 'ingame' matches from previous crashed sessions
            self._cleanup_stale_ingame_matches()

            # Register with message bus first
            self.message_queue = self.message_bus.register_component(self.name)

            # Subscribe to relevant messages
            self.message_bus.subscribe(self.name, MessageType.START_GAME, self._handle_start_game)
            self.message_bus.subscribe(self.name, MessageType.SCHEDULE_GAMES, self._handle_schedule_games)
            self.message_bus.subscribe(self.name, MessageType.SYSTEM_SHUTDOWN, self._handle_shutdown_message)
            self.message_bus.subscribe(self.name, MessageType.MATCH_START, self._handle_match_start)
            self.message_bus.subscribe(self.name, MessageType.PLAY_VIDEO_MATCH_DONE, self._handle_play_video_match_done)
            self.message_bus.subscribe(self.name, MessageType.PLAY_VIDEO_RESULT_DONE, self._handle_play_video_result_done)
            self.message_bus.subscribe(self.name, MessageType.MATCH_DONE, self._handle_match_done)
            self.message_bus.subscribe(self.name, MessageType.GAME_STATUS, self._handle_game_status_request)
            self.message_bus.subscribe(self.name, MessageType.SYSTEM_STATUS, self._handle_system_status)

            # Send ready status
            ready_message = MessageBuilder.system_status(
                sender=self.name,
                status="ready",
                details={
                    "component": "games_thread",
                    "capabilities": ["game_monitoring", "fixture_tracking"]
                }
            )
            self.message_bus.publish(ready_message)

            logger.info("GamesThread initialized successfully")
            return True

        except Exception as e:
            logger.error(f"Failed to initialize GamesThread: {e}")
            return False

    def run(self):
        """Main run loop for the games thread"""
        logger.info("GamesThread started")

        try:
            while self.running and not self._shutdown_event.is_set():
                try:
                    # Process any pending messages with shorter timeout for responsive shutdown
                    message = self.message_bus.get_message(self.name, timeout=0.05)
                    if message:
                        self._process_message(message)

                    # If a game is active, perform game-related operations
                    if self.game_active and self.current_fixture_id:
                        self._monitor_game_state()

                    # Update heartbeat
                    self.heartbeat()

                    # Check shutdown event more frequently
                    if self._shutdown_event.wait(0.05):
                        break

                except Exception as e:
                    logger.error(f"GamesThread run loop error: {e}")
                    # Shorter sleep on error to be more responsive to shutdown
                    if not self._shutdown_event.wait(0.5):
                        continue
                    break

        except Exception as e:
            logger.error(f"GamesThread run failed: {e}")
        finally:
            self._cleanup()
            logger.info("GamesThread ended")

    def shutdown(self):
        """Shutdown the games thread"""
        logger.info("GamesThread shutdown requested")
        self._shutdown_event.set()
        self.game_active = False

    def _handle_start_game(self, message: Message):
        """Handle START_GAME message with comprehensive logic"""
        try:
            logger.info(f"Processing START_GAME message from {message.sender}")

            fixture_id = message.data.get("fixture_id")

            if fixture_id:
                # If fixture_id is provided, check if it's in terminal state
                if self._is_fixture_all_terminal(fixture_id):
                    logger.info(f"Fixture {fixture_id} is in terminal state - discarding START_GAME message")
                    self._send_response(message, "discarded", f"Fixture {fixture_id} is already completed")
                    return

                # Fixture is not terminal, activate it (ZIP validation happens asynchronously)
                logger.info(f"Activating provided fixture: {fixture_id}")
                self._activate_fixture(fixture_id, message)
                # Start ZIP validation asynchronously in background
                self._start_async_zip_validation(fixture_id)
                return

            # No fixture_id provided - check today's fixtures
            if self._has_today_fixtures_all_terminal():
                logger.info("All today's fixtures are in terminal states - discarding START_GAME message")
                self._send_response(message, "discarded", "All today's fixtures are already completed")
                return

            # Step 2: Handle matches currently in "ingame" status
            ingame_handled = self._handle_ingame_matches(message)
            if ingame_handled:
                # Message was handled (either discarded or processed) - return
                return

            # Step 3: Check if there are active fixtures with today's date
            active_fixture = self._find_active_today_fixture()
            if active_fixture:
                logger.info(f"Found active fixture for today: {active_fixture}")
                self._activate_fixture(active_fixture, message)
                # Start ZIP validation asynchronously in background
                self._start_async_zip_validation(active_fixture)
                return

            # Step 4: No active fixtures found - initialize new fixture
            logger.info("No active fixtures found - initializing new fixture")
            new_fixture_id = self._initialize_new_fixture()
            if new_fixture_id:
                self._activate_fixture(new_fixture_id, message)
                # Start ZIP validation asynchronously in background
                self._start_async_zip_validation(new_fixture_id)
            else:
                logger.warning("Could not initialize new fixture")
                self._send_response(message, "error", "Could not initialize new fixture")

        except Exception as e:
            logger.error(f"Failed to handle START_GAME message: {e}")
            self._send_response(message, "error", str(e))

    def _handle_schedule_games(self, message: Message):
        """Handle SCHEDULE_GAMES message - change status of pending matches to scheduled"""
        try:
            fixture_id = message.data.get("fixture_id")

            if not fixture_id:
                # If no fixture_id provided, find the last fixture with pending matches
                fixture_id = self._find_last_fixture_with_pending_matches()

            if fixture_id:
                logger.info(f"Scheduling games for fixture: {fixture_id}")

                # Update status of all pending matches in the fixture to scheduled
                updated_count = self._schedule_fixture_matches(fixture_id)

                if updated_count > 0:
                    logger.info(f"Successfully scheduled {updated_count} matches for fixture {fixture_id}")

                    # Send success response
                    response = Message(
                        type=MessageType.GAME_STATUS,
                        sender=self.name,
                        recipient=message.sender,
                        data={
                            "status": "scheduled",
                            "fixture_id": fixture_id,
                            "matches_scheduled": updated_count,
                            "timestamp": time.time()
                        },
                        correlation_id=message.correlation_id
                    )
                    self.message_bus.publish(response)
                else:
                    logger.warning(f"No pending matches found to schedule for fixture {fixture_id}")

                    # Send response indicating no matches were scheduled
                    response = Message(
                        type=MessageType.GAME_STATUS,
                        sender=self.name,
                        recipient=message.sender,
                        data={
                            "status": "no_matches",
                            "fixture_id": fixture_id,
                            "message": "No pending matches found to schedule",
                            "timestamp": time.time()
                        },
                        correlation_id=message.correlation_id
                    )
                    self.message_bus.publish(response)
            else:
                logger.warning("No fixture with pending matches found")

                # Send error response
                error_response = Message(
                    type=MessageType.GAME_STATUS,
                    sender=self.name,
                    recipient=message.sender,
                    data={
                        "status": "error",
                        "error": "No fixture with pending matches found",
                        "timestamp": time.time()
                    },
                    correlation_id=message.correlation_id
                )
                self.message_bus.publish(error_response)

        except Exception as e:
            logger.error(f"Failed to handle SCHEDULE_GAMES message: {e}")

            # Send error response
            error_response = Message(
                type=MessageType.GAME_STATUS,
                sender=self.name,
                recipient=message.sender,
                data={
                    "status": "error",
                    "error": str(e),
                    "timestamp": time.time()
                },
                correlation_id=message.correlation_id
            )
            self.message_bus.publish(error_response)

    def _handle_shutdown_message(self, message: Message):
        """Handle shutdown message"""
        logger.info(f"Shutdown message received from {message.sender}")
        self._shutdown_event.set()
        self.game_active = False

    def _handle_match_start(self, message: Message):
        """Handle MATCH_START message and determine match result based on betting"""
        try:
            fixture_id = message.data.get("fixture_id")
            match_id = message.data.get("match_id")

            logger.info(f"Processing MATCH_START for fixture {fixture_id}, match {match_id}")

            # Calculate result using betting CAP logic
            result = self._calculate_match_result(fixture_id, match_id)

            # Send PLAY_VIDEO_MATCH with calculated result
            self._send_play_video_match(fixture_id, match_id, result)

            # Set match status to 'ingame' before performing extraction
            self._set_match_status(match_id, 'ingame')

            # Perform result extraction immediately after sending PLAY_VIDEO_MATCH
            logger.info(f"🔍 [EXTRACTION DEBUG] Starting immediate extraction after PLAY_VIDEO_MATCH for fixture {fixture_id}, match {match_id}")
            extracted_result = self._perform_result_extraction(fixture_id, match_id)
            logger.info(f"✅ [EXTRACTION DEBUG] Extraction completed immediately: {extracted_result}")

        except Exception as e:
            logger.error(f"Failed to handle MATCH_START message: {e}")
            # Fallback to random selection if betting calculation fails
            try:
                fixture_id = message.data.get("fixture_id")
                match_id = message.data.get("match_id")
                result = self._weighted_random_selection(1.0, 1.0)  # Equal weights
                self._send_play_video_match(fixture_id, match_id, result)
            except Exception as fallback_e:
                logger.error(f"Fallback betting calculation also failed: {fallback_e}")

    def _handle_game_status_request(self, message: Message):
        """Handle GAME_STATUS requests from Qt player"""
        try:
            logger.info(f"Received GAME_STATUS request from {message.sender}")

            # Determine current game status
            game_status = self._determine_game_status()

            # If status is "already_active" but game is not active, activate the fixture
            # But NOT if we're waiting for downloads
            if game_status == "already_active" and not self.game_active:
                logger.info("Status is 'already_active' but game is not active - activating fixture")
                active_fixture = self._find_active_today_fixture()
                if active_fixture:
                    # Create a dummy message for activation
                    dummy_message = Message(
                        type=MessageType.START_GAME,
                        sender=message.sender,
                        recipient=self.name,
                        data={"timestamp": time.time()},
                        correlation_id=message.correlation_id
                    )
                    self._activate_fixture(active_fixture, dummy_message)
                    # Update status after activation
                    game_status = "started"
                else:
                    logger.warning("Could not find active fixture to activate")
            elif game_status == "waiting_for_downloads":
                logger.info("Game status is 'waiting_for_downloads' - not activating fixture until all ZIP files are available")

            # Send GAME_STATUS response back to the requester
            response = Message(
                type=MessageType.GAME_STATUS,
                sender=self.name,
                recipient=message.sender,
                data={
                    "status": game_status,
                    "fixture_id": self.current_fixture_id,
                    "game_active": self.game_active,
                    "timestamp": time.time()
                },
                correlation_id=message.correlation_id
            )
            # Broadcast the response instead of sending to specific recipient
            self.message_bus.publish(response, broadcast=True)
            logger.info(f"Broadcast game status response: {game_status}")

        except Exception as e:
            logger.error(f"Failed to handle game status request: {e}")

    def _process_message(self, message: Message):
        """Process incoming messages"""
        try:
            logger.debug(f"GamesThread processing message: {message}")

            # Handle messages directly since broadcast messages don't trigger subscription handlers
            if message.type == MessageType.START_GAME:
                self._handle_start_game(message)
            elif message.type == MessageType.SCHEDULE_GAMES:
                self._handle_schedule_games(message)
            elif message.type == MessageType.SYSTEM_SHUTDOWN:
                self._handle_shutdown_message(message)
            elif message.type == MessageType.SYSTEM_STATUS:
                self._handle_system_status(message)
            elif message.type == MessageType.GAME_UPDATE:
                self._handle_game_update(message)
            elif message.type == MessageType.PLAY_VIDEO_MATCH_DONE:
                self._handle_play_video_match_done(message)
            elif message.type == MessageType.PLAY_VIDEO_RESULT_DONE:
                self._handle_play_video_result_done(message)
            elif message.type == MessageType.MATCH_DONE:
                self._handle_match_done(message)
            elif message.type == MessageType.MATCH_START:
                self._handle_match_start(message)
            elif message.type == MessageType.GAME_STATUS:
                self._handle_game_status_request(message)

        except Exception as e:
            logger.error(f"Failed to process message: {e}")

    def _handle_game_update(self, message: Message):
        """Handle game update messages"""
        try:
            update_data = message.data
            logger.debug(f"Game update received: {update_data}")

            # Process game update data as needed
            # This could include updating match states, processing outcomes, etc.

        except Exception as e:
            logger.error(f"Failed to handle game update: {e}")

    def _find_last_fixture_with_pending_matches(self) -> Optional[str]:
        """Find the last fixture that has pending matches"""
        try:
            session = self.db_manager.get_session()
            try:
                # Query for matches with PENDING status
                pending_matches = session.query(MatchModel).filter(
                    MatchModel.status == 'pending',
                    MatchModel.active_status == True
                ).order_by(MatchModel.fixture_active_time.desc()).all()

                if pending_matches:
                    # Get the fixture_id from the most recent pending match
                    latest_match = pending_matches[0]
                    fixture_id = latest_match.fixture_id
                    logger.info(f"Found fixture with pending matches: {fixture_id} ({len(pending_matches)} matches)")
                    return fixture_id
                else:
                    logger.info("No pending matches found")
                    return None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to find last fixture with pending matches: {e}")
            return None

    def _schedule_fixture_matches(self, fixture_id: str) -> int:
        """Update status of all pending matches in a fixture to scheduled"""
        try:
            session = self.db_manager.get_session()
            try:
                # Query for pending matches in the specified fixture
                pending_matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.status == 'pending',
                    MatchModel.active_status == True
                ).all()

                updated_count = 0

                for match in pending_matches:
                    # Change status from PENDING to SCHEDULED
                    match.status = 'scheduled'
                    logger.debug(f"Scheduling match #{match.match_number}: {match.fighter1_township} vs {match.fighter2_township} - status changed to {match.status}")
                    updated_count += 1

                # Commit the changes
                session.commit()
                logger.info(f"Scheduled {updated_count} matches for fixture {fixture_id}")

                return updated_count

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to schedule matches for fixture {fixture_id}: {e}")
            return 0

    def _monitor_game_state(self):
        """Monitor the current game state"""
        try:
            if not self.current_fixture_id:
                return

            # Check if there are still pending or scheduled matches for this fixture
            session = self.db_manager.get_session()
            try:
                active_count = session.query(MatchModel).filter(
                    MatchModel.fixture_id == self.current_fixture_id,
                    MatchModel.status.in_(['pending', 'scheduled', 'bet', 'ingame']),
                    MatchModel.active_status == True
                ).count()

                if active_count == 0:
                    logger.info(f"All matches completed for fixture {self.current_fixture_id} - creating new matches from old completed ones")

                    # Instead of stopping the game, create 5 new matches from old completed matches
                    old_matches = self._select_random_completed_matches_with_fallback(5, self.current_fixture_id, session)
                    if old_matches:
                        self._create_matches_from_old_matches(self.current_fixture_id, old_matches, session)
                        logger.info(f"Created 5 new matches in fixture {self.current_fixture_id} from old completed matches")
                    else:
                        logger.warning("No old completed matches found - cannot create new matches")
                        # If no old matches available, stop the game
                        self.game_active = False
                        completed_message = Message(
                            type=MessageType.GAME_STATUS,
                            sender=self.name,
                            data={
                                "status": "completed_no_old_matches",
                                "fixture_id": self.current_fixture_id,
                                "timestamp": time.time()
                            }
                        )
                        self.message_bus.publish(completed_message)
                        self.current_fixture_id = None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to monitor game state: {e}")

    def _send_response(self, original_message: Message, status: str, message: str = None):
        """Send response message back to the sender"""
        try:
            response_data = {
                "status": status,
                "timestamp": time.time()
            }

            if message:
                response_data["message"] = message

            response = Message(
                type=MessageType.GAME_STATUS,
                sender=self.name,
                recipient=original_message.sender,
                data=response_data,
                correlation_id=original_message.correlation_id
            )
            self.message_bus.publish(response)

        except Exception as e:
            logger.error(f"Failed to send response: {e}")

    def _is_fixture_all_terminal(self, fixture_id: str) -> bool:
        """Check if all matches in a fixture are in terminal states (done, cancelled, failed, paused)"""
        try:
            session = self.db_manager.get_session()
            try:
                # Get all matches for this fixture
                matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.active_status == True
                ).all()

                if not matches:
                    return False  # No matches means not terminal

                # Check if all matches are in terminal states
                terminal_states = ['done', 'cancelled', 'failed', 'paused']
                return all(match.status in terminal_states for match in matches)

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to check if fixture {fixture_id} is terminal: {e}")
            return False

    def _has_today_fixtures_all_terminal(self) -> bool:
        """Check if all fixtures with today's matches are in terminal states"""
        try:
            session = self.db_manager.get_session()
            try:
                # Get today's date
                today = datetime.now().date()

                # Find all fixtures that have matches with today's start_time
                fixtures_with_today_matches = session.query(MatchModel.fixture_id).filter(
                    MatchModel.start_time.isnot(None),
                    MatchModel.start_time >= datetime.combine(today, datetime.min.time()),
                    MatchModel.start_time < datetime.combine(today, datetime.max.time())
                ).distinct().all()

                if not fixtures_with_today_matches:
                    return False  # No today's fixtures

                # Check each fixture
                for fixture_row in fixtures_with_today_matches:
                    fixture_id = fixture_row.fixture_id
                    if not self._is_fixture_all_terminal(fixture_id):
                        return False  # Found a non-terminal fixture

                return True  # All fixtures are terminal

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to check today's fixtures terminal status: {e}")
            return False

    def _handle_ingame_matches(self, message: Message) -> bool:
        """Handle matches currently in 'ingame' status. Returns True if message was handled."""
        try:
            session = self.db_manager.get_session()
            try:
                # Get today's date
                today = datetime.now().date()

                # Find fixtures with ingame matches today
                ingame_matches = session.query(MatchModel).filter(
                    MatchModel.start_time.isnot(None),
                    MatchModel.start_time >= datetime.combine(today, datetime.min.time()),
                    MatchModel.start_time < datetime.combine(today, datetime.max.time()),
                    MatchModel.status == 'ingame',
                    MatchModel.active_status == True
                ).all()

                if not ingame_matches:
                    return False  # No ingame matches, continue processing

                # Get unique fixture IDs
                fixture_ids = list(set(match.fixture_id for match in ingame_matches))

                for fixture_id in fixture_ids:
                    # Check if timer is running for this fixture
                    # This is a simplified check - in real implementation you'd check the match_timer component
                    timer_running = self._is_timer_running_for_fixture(fixture_id)

                    if not timer_running:
                        # Timer not running, change status to pending and set active_status to False
                        logger.info(f"Timer not running for fixture {fixture_id}, changing ingame matches to pending and setting active_status to False")
                        self._change_fixture_matches_status(fixture_id, 'ingame', 'pending', active_status_to_set=False)

                        # Check if this was the only non-terminal fixture
                        if self._is_only_non_terminal_fixture(fixture_id):
                            logger.info("This was the only non-terminal fixture - discarding START_GAME message")
                            self._send_response(message, "discarded", "Timer not running and no other active fixtures")
                            return True
                    else:
                        # Timer is running, check for other pending/bet/scheduled matches
                        other_active_matches = session.query(MatchModel).filter(
                            MatchModel.fixture_id == fixture_id,
                            MatchModel.status.in_(['pending', 'bet', 'scheduled']),
                            MatchModel.active_status == True
                        ).all()

                        if other_active_matches:
                            # Change first pending/bet/scheduled match to bet status
                            first_match = other_active_matches[0]
                            if first_match.status != 'bet':
                                logger.info(f"Changing match {first_match.match_number} status to bet")
                                first_match.status = 'bet'
                                session.commit()

                        # Timer is running, discard the message
                        logger.info(f"Timer running for fixture {fixture_id} - discarding START_GAME message")
                        self._send_response(message, "discarded", "Timer already running for active fixture")
                        return True

                return False  # Continue processing

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to handle ingame matches: {e}")
            return False

    def _is_timer_running_for_fixture(self, fixture_id: str) -> bool:
        """Check if timer is running for a specific fixture"""
        # This is a simplified implementation
        # In a real implementation, you'd check the match_timer component status
        return self.current_fixture_id == fixture_id and self.game_active

    def _change_fixture_matches_status(self, fixture_id: str, from_status: str, to_status: str, active_status_to_set: Optional[bool] = None):
        """Change status of matches in a fixture from one status to another"""
        try:
            session = self.db_manager.get_session()
            try:
                matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.status == from_status,
                    MatchModel.active_status == True
                ).all()

                for match in matches:
                    logger.info(f"Changing match {match.match_number} status from {from_status} to {to_status}")
                    match.status = to_status
                    if active_status_to_set is not None:
                        match.active_status = active_status_to_set
                        logger.info(f"Setting active_status to {active_status_to_set} for match {match.match_number}")

                session.commit()

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to change match statuses: {e}")

    def _is_only_non_terminal_fixture(self, fixture_id: str) -> bool:
        """Check if this is the only non-terminal fixture"""
        try:
            session = self.db_manager.get_session()
            try:
                # Get today's date
                today = datetime.now().date()

                # Find all fixtures with today's matches
                all_fixtures = session.query(MatchModel.fixture_id).filter(
                    MatchModel.start_time.isnot(None),
                    MatchModel.start_time >= datetime.combine(today, datetime.min.time()),
                    MatchModel.start_time < datetime.combine(today, datetime.max.time())
                ).distinct().all()

                # Check each fixture except the current one
                terminal_states = ['done', 'cancelled', 'failed', 'paused']
                non_terminal_count = 0

                for fixture_row in all_fixtures:
                    fid = fixture_row.fixture_id
                    if fid == fixture_id:
                        continue

                    # Check if this fixture has non-terminal matches
                    non_terminal_matches = session.query(MatchModel).filter(
                        MatchModel.fixture_id == fid,
                        MatchModel.status.notin_(terminal_states),
                        MatchModel.active_status == True
                    ).all()

                    if non_terminal_matches:
                        non_terminal_count += 1

                return non_terminal_count == 0

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to check if only non-terminal fixture: {e}")
            return False

    def _start_async_zip_validation(self, fixture_id: str):
        """Start asynchronous ZIP validation for a fixture without blocking"""
        try:
            logger.info(f"Starting asynchronous ZIP validation for fixture {fixture_id}")

            # Start validation in a background thread
            validation_thread = threading.Thread(
                target=self._validate_fixture_zips_async,
                args=(fixture_id,),
                daemon=True
            )
            validation_thread.start()

        except Exception as e:
            logger.error(f"Failed to start async ZIP validation for fixture {fixture_id}: {e}")

    def _validate_fixture_zips_async(self, fixture_id: str):
        """Validate ZIP files for a fixture asynchronously"""
        try:
            logger.info(f"Async ZIP validation started for fixture {fixture_id}")

            session = self.db_manager.get_session()
            try:
                # Get all active matches for this fixture that have ZIP files
                matches_with_zips = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.active_status == True,
                    MatchModel.zip_filename.isnot(None)
                ).all()

                if not matches_with_zips:
                    logger.debug(f"Fixture {fixture_id} has no matches requiring ZIP files")
                    return

                logger.info(f"Validating {len(matches_with_zips)} ZIP files for fixture {fixture_id}")

                # Reset any stale 'validating' statuses (older than 5 minutes)
                stale_threshold = datetime.utcnow() - timedelta(minutes=5)
                stale_count = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.zip_validation_status == 'validating',
                    MatchModel.updated_at < stale_threshold
                ).update({'zip_validation_status': 'pending'})
                if stale_count > 0:
                    logger.info(f"Reset {stale_count} stale 'validating' statuses to 'pending'")
                    session.commit()

                for match in matches_with_zips:
                    # Check if already validated
                    if match.zip_validation_status == 'valid':
                        logger.debug(f"Match {match.match_number} ZIP already validated, skipping")
                        continue
                    elif match.zip_validation_status == 'validating':
                        logger.debug(f"Match {match.match_number} ZIP validation in progress, skipping")
                        continue

                    # Start validation for this match
                    self._validate_single_zip_async(match.id, session)

            finally:
                session.close()

            logger.info(f"Async ZIP validation completed for fixture {fixture_id}")

        except Exception as e:
            logger.error(f"Async ZIP validation failed for fixture {fixture_id}: {e}")

    def _validate_single_zip_async(self, match_id: int, session):
        """Validate a single ZIP file asynchronously"""
        try:
            match = session.query(MatchModel).filter(MatchModel.id == match_id).first()
            if not match:
                logger.warning(f"Match {match_id} not found for ZIP validation")
                return

            # Update status to validating
            match.zip_validation_status = 'validating'
            session.commit()

            # Start validation in separate thread
            validation_thread = threading.Thread(
                target=self._perform_zip_validation,
                args=(match_id,),
                daemon=True
            )
            validation_thread.start()

        except Exception as e:
            logger.error(f"Failed to start ZIP validation for match {match_id}: {e}")

    def _perform_zip_validation(self, match_id: int):
        """Perform actual ZIP validation"""
        try:
            session = self.db_manager.get_session()
            try:
                match = session.query(MatchModel).filter(MatchModel.id == match_id).first()
                if not match:
                    logger.warning(f"Match {match_id} not found during ZIP validation")
                    return

                zip_filename = match.zip_filename
                if not zip_filename:
                    logger.warning(f"Match {match_id} has no ZIP filename")
                    return

                from ..config.settings import get_user_data_dir
                from pathlib import Path
                import zipfile

                user_data_dir = get_user_data_dir()
                zip_path = user_data_dir / "zip_files" / zip_filename

                logger.info(f"Validating ZIP file: {zip_path}")

                # Check if file exists
                if not zip_path.exists():
                    logger.error(f"ZIP file missing: {zip_path}")
                    match.zip_validation_status = 'invalid'
                    session.commit()
                    return

                # Check file size
                if zip_path.stat().st_size == 0:
                    logger.error(f"ZIP file empty: {zip_path}")
                    match.zip_validation_status = 'invalid'
                    session.commit()
                    return

                # Try to open and validate ZIP structure
                try:
                    with zipfile.ZipFile(str(zip_path), 'r') as zip_ref:
                        # Check for required video files (WIN1.mp4, WIN2.mp4, etc.)
                        file_list = zip_ref.namelist()
                        required_videos = ['WIN1.mp4', 'WIN2.mp4', 'DRAW.mp4']  # Basic requirements
                        found_videos = [f for f in file_list if f.endswith('.mp4')]

                        if not found_videos:
                            logger.error(f"ZIP file contains no MP4 files: {zip_path}")
                            match.zip_validation_status = 'invalid'
                            session.commit()
                            return

                        logger.info(f"ZIP file valid - contains {len(found_videos)} video files: {zip_path}")

                except zipfile.BadZipFile as e:
                    logger.error(f"Invalid ZIP file: {zip_path} - {e}")
                    match.zip_validation_status = 'invalid'
                    session.commit()
                    return
                except Exception as e:
                    logger.error(f"Error validating ZIP file: {zip_path} - {e}")
                    match.zip_validation_status = 'invalid'
                    session.commit()
                    return

                # Validation successful
                match.zip_validation_status = 'valid'
                session.commit()
                logger.info(f"ZIP validation successful for match {match_id}: {zip_filename}")

            finally:
                session.close()

        except Exception as e:
            logger.error(f"ZIP validation failed for match {match_id}: {e}")
            try:
                session = self.db_manager.get_session()
                match = session.query(MatchModel).filter(MatchModel.id == match_id).first()
                if match:
                    match.zip_validation_status = 'invalid'
                    session.commit()
                session.close()
            except Exception as update_e:
                logger.error(f"Failed to update validation status after error: {update_e}")

    def _find_active_today_fixture(self) -> Optional[str]:
        """Find an active fixture with today's date"""
        try:
            session = self.db_manager.get_session()
            try:
                # Get today's date
                today = datetime.now().date()

                # Find fixtures with today's matches that are not in terminal states
                terminal_states = ['done', 'cancelled', 'failed', 'paused']

                active_matches = session.query(MatchModel).filter(
                    MatchModel.start_time.isnot(None),
                    MatchModel.start_time >= datetime.combine(today, datetime.min.time()),
                    MatchModel.start_time < datetime.combine(today, datetime.max.time()),
                    MatchModel.status.notin_(terminal_states),
                    MatchModel.active_status == True
                ).order_by(MatchModel.start_time.asc()).all()

                if active_matches:
                    return active_matches[0].fixture_id

                return None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to find active today fixture: {e}")
            return None

    def _initialize_new_fixture(self) -> Optional[str]:
        """Initialize a new fixture by finding the first one with no start_time set, or create one from old matches"""
        try:
            session = self.db_manager.get_session()
            try:
                # First, try to find the first fixture with no start_time set
                fixtures_no_start_time = session.query(MatchModel.fixture_id).filter(
                    MatchModel.start_time.is_(None),
                    MatchModel.active_status == True
                ).distinct().order_by(MatchModel.created_at.asc()).all()

                if fixtures_no_start_time:
                    fixture_id = fixtures_no_start_time[0].fixture_id
                    logger.info(f"Initializing existing fixture with no start_time: {fixture_id}")

                    # Set start_time to now for all matches in this fixture
                    now = datetime.utcnow()
                    matches = session.query(MatchModel).filter(
                        MatchModel.fixture_id == fixture_id,
                        MatchModel.active_status == True
                    ).all()

                    for match in matches:
                        match.start_time = now
                        match.status = 'scheduled'
                        logger.debug(f"Set start_time for match {match.match_number}")

                    session.commit()
                    return fixture_id

                # No fixtures with no start_time found - create a new fixture from old completed matches
                logger.info("No fixtures with no start_time found - creating new fixture from old completed matches")
                old_matches = self._select_random_completed_matches_with_fallback(5, None, session)
                if old_matches:
                    fixture_id = self._create_new_fixture_from_old_matches(old_matches, session)
                    if fixture_id:
                        logger.info(f"Created new fixture {fixture_id} from old completed matches")
                        return fixture_id
                    else:
                        logger.warning("Failed to create new fixture from old matches")
                        return None
                else:
                    logger.warning("No old completed matches found - cannot create new fixture")
                    return None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to initialize new fixture: {e}")
            return None

    def _activate_fixture(self, fixture_id: str, message: Message):
        """Activate a fixture and start the game"""
        try:
            logger.info(f"🎯 ACTIVATING FIXTURE: {fixture_id}")

            # Check if fixture is already active to prevent double activation
            if self.current_fixture_id == fixture_id and self.game_active:
                logger.warning(f"Fixture {fixture_id} is already active - ignoring duplicate activation")
                self._send_response(message, "already_active", f"Fixture {fixture_id} already active")
                return

            # Set current fixture
            self.current_fixture_id = fixture_id
            self.game_active = True

            # Step 1 & 2: Change match statuses in a single transaction
            logger.info(f"🔄 Starting match status changes for fixture {fixture_id}")
            self._schedule_and_apply_betting_logic(fixture_id)

            # Send game started confirmation
            logger.info(f"✅ Fixture {fixture_id} activated successfully")
            self._send_response(message, "started", f"Fixture {fixture_id} activated")

            # Start match timer
            logger.info(f"⏰ Starting match timer for fixture {fixture_id}")
            self._start_match_timer(fixture_id)

            # Dispatch START_INTRO message
            logger.info(f"🎬 Dispatching START_INTRO message for fixture {fixture_id}")
            self._dispatch_start_intro(fixture_id)

            # Broadcast GAME_STARTED message to notify all components that game has started with this fixture
            game_started_message = MessageBuilder.game_started(
                sender=self.name,
                fixture_id=fixture_id
            )
            self.message_bus.publish(game_started_message, broadcast=True)
            logger.info(f"🎯 Broadcast GAME_STARTED message for fixture {fixture_id}")

            # Refresh dashboard statuses
            self._refresh_dashboard_statuses()

        except Exception as e:
            logger.error(f"❌ Failed to activate fixture {fixture_id}: {e}")
            import traceback
            logger.error(f"Stack trace: {traceback.format_exc()}")
            self._send_response(message, "error", f"Failed to activate fixture: {str(e)}")

    def _schedule_and_apply_betting_logic(self, fixture_id: str):
        """Change match statuses in a single transaction: first to 'scheduled', then apply betting logic"""
        try:
            logger.info(f"🔄 Starting match status update for fixture {fixture_id}")
            
            # Get betting mode configuration from database (default to 'all_bets_on_start')
            betting_mode = self._get_betting_mode_config()
            logger.info(f"📋 Using betting mode: {betting_mode}")
            
            session = self.db_manager.get_session()
            try:
                # First, let's see what matches exist for this fixture
                all_matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.active_status == True
                ).all()
                
                if not all_matches:
                    logger.warning(f"⚠️ No matches found for fixture {fixture_id}")
                    return
                
                logger.info(f"📊 Found {len(all_matches)} total matches in fixture {fixture_id}")
                for match in all_matches:
                    logger.info(f"  Match {match.match_number}: {match.fighter1_township} vs {match.fighter2_township} - Status: {match.status}")

                # Step 1: Change ALL matches in the fixture to 'scheduled' status first
                terminal_states = ['done', 'cancelled', 'failed', 'paused']
                matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.status.notin_(terminal_states),
                    MatchModel.active_status == True
                ).all()

                logger.info(f"📋 Found {len(matches)} non-terminal matches to process")

                scheduled_count = 0
                for match in matches:
                    if match.status != 'scheduled':
                        logger.info(f"🔄 Changing match {match.match_number} status from '{match.status}' to 'scheduled'")
                        match.status = 'scheduled'
                        scheduled_count += 1
                    else:
                        logger.info(f"✅ Match {match.match_number} already scheduled")

                # Flush to make sure scheduled status is available for next step
                if scheduled_count > 0:
                    session.flush()
                    logger.info(f"✅ Scheduled {scheduled_count} matches in fixture {fixture_id}")
                else:
                    logger.info("📋 No matches needed scheduling")

                # Step 2: Apply betting logic based on configuration
                if betting_mode == 'all_bets_on_start':
                    logger.info("🎰 Applying 'all_bets_on_start' logic")
                    # Change ALL scheduled matches to 'bet' status
                    scheduled_matches = session.query(MatchModel).filter(
                        MatchModel.fixture_id == fixture_id,
                        MatchModel.status == 'scheduled',
                        MatchModel.active_status == True
                    ).all()

                    logger.info(f"📋 Found {len(scheduled_matches)} scheduled matches to change to 'bet'")

                    bet_count = 0
                    for match in scheduled_matches:
                        logger.info(f"🎰 Changing match {match.match_number} status to 'bet' (all bets on start)")
                        match.status = 'bet'
                        bet_count += 1

                    if bet_count > 0:
                        logger.info(f"✅ Changed {bet_count} matches to 'bet' status (all bets on start mode)")
                    else:
                        logger.warning("⚠️ No scheduled matches found to change to 'bet' status")

                else:  # 'one_bet_at_a_time'
                    logger.info("🎯 Applying 'one_bet_at_a_time' logic")
                    # Change only the FIRST scheduled match to 'bet' status
                    first_match = session.query(MatchModel).filter(
                        MatchModel.fixture_id == fixture_id,
                        MatchModel.status == 'scheduled',
                        MatchModel.active_status == True
                    ).order_by(MatchModel.match_number.asc()).first()

                    if first_match:
                        logger.info(f"🎯 Changing first match {first_match.match_number} status to 'bet' (one bet at a time)")
                        first_match.status = 'bet'
                    else:
                        logger.warning("⚠️ No scheduled match found to change to 'bet' status")

                # Commit all changes in a single transaction
                logger.info("💾 Committing database changes...")
                session.commit()
                logger.info(f"✅ Successfully updated match statuses for fixture {fixture_id} with betting mode: {betting_mode}")

                # Send notification to web dashboard about fixture status update
                try:
                    from .message_bus import Message, MessageType
                    status_update_message = Message(
                        type=MessageType.CUSTOM,
                        sender=self.name,
                        recipient="web_dashboard",
                        data={
                            "fixture_status_update": {
                                "fixture_id": fixture_id,
                                "betting_mode": betting_mode,
                                "timestamp": time.time()
                            }
                        }
                    )
                    self.message_bus.publish(status_update_message)
                    logger.info(f"📢 Broadcast fixture status update notification for {fixture_id}")
                except Exception as msg_e:
                    logger.warning(f"Failed to send fixture status update notification: {msg_e}")

                # Verify the changes were applied
                final_matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.active_status == True
                ).all()
                
                logger.info(f"🔍 Final match statuses for fixture {fixture_id}:")
                for match in final_matches:
                    logger.info(f"  Match {match.match_number}: {match.status}")

            finally:
                session.close()

        except Exception as e:
            logger.error(f"❌ Failed to schedule and apply betting logic: {e}")
            import traceback
            logger.error(f"Stack trace: {traceback.format_exc()}")
            # Try to rollback in case of error
            try:
                if 'session' in locals():
                    session.rollback()
            except Exception as rollback_e:
                logger.error(f"Failed to rollback: {rollback_e}")

    def _get_betting_mode_config(self) -> str:
        """Get global betting mode configuration from game config (default: 'all_bets_on_start')"""
        try:
            session = self.db_manager.get_session()
            try:
                from ..database.models import GameConfigModel
                
                # Get global betting mode configuration from game_config table
                betting_mode_config = session.query(GameConfigModel).filter_by(
                    config_key='betting_mode'
                ).first()
                
                if betting_mode_config:
                    return betting_mode_config.get_typed_value()
                else:
                    # Default to 'all_bets_on_start' if no configuration found
                    logger.debug("No betting mode configuration found, using default: 'all_bets_on_start'")
                    return 'all_bets_on_start'

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to get betting mode config: {e}")
            # Default fallback
            return 'all_bets_on_start'

    def _refresh_dashboard_statuses(self):
        """Refresh dashboard statuses by sending update messages"""
        try:
            # Send refresh message to web dashboard
            refresh_message = Message(
                type=MessageType.GAME_STATUS,
                sender=self.name,
                data={
                    "action": "refresh",
                    "timestamp": time.time()
                }
            )
            self.message_bus.publish(refresh_message, broadcast=True)

        except Exception as e:
            logger.error(f"Failed to refresh dashboard statuses: {e}")

    def _start_match_timer(self, fixture_id: str):
        """Start the match timer for the fixture"""
        try:
            # Send message to match timer component
            timer_message = Message(
                type=MessageType.START_GAME,
                sender=self.name,
                recipient="match_timer",
                data={
                    "fixture_id": fixture_id,
                    "action": "start_timer",
                    "timestamp": time.time()
                }
            )
            self.message_bus.publish(timer_message)

            logger.info(f"Started match timer for fixture {fixture_id}")

        except Exception as e:
            logger.error(f"Failed to start match timer: {e}")

    def _dispatch_start_intro(self, fixture_id: str):
        """Dispatch START_INTRO message to trigger intro content"""
        try:
            from .message_bus import MessageBuilder

            # Check if there are at least 5 matches remaining in the fixture
            logger.info(f"🎬 Checking minimum match count for fixture {fixture_id} before playing INTRO.mp4")
            remaining_matches = self._count_remaining_matches_in_fixture(fixture_id)
            logger.info(f"🎬 Fixture {fixture_id} has {remaining_matches} remaining matches")

            if remaining_matches < 5:
                logger.info(f"🚨 Only {remaining_matches} matches remaining (minimum 5 required) - creating new matches")
                self._ensure_minimum_matches_in_fixture(fixture_id, 5 - remaining_matches)
                # Recount after adding matches
                remaining_matches = self._count_remaining_matches_in_fixture(fixture_id)
                logger.info(f"✅ After adding matches, fixture {fixture_id} now has {remaining_matches} remaining matches")

            # Find the first match that was set to 'bet' status
            first_bet_match_id = self._get_first_bet_match_id(fixture_id)

            # Unzip the ZIP file of the first match if it exists
            self._unzip_match_zip_file(first_bet_match_id)

            # Create and send START_INTRO message
            start_intro_message = MessageBuilder.start_intro(
                sender=self.name,
                fixture_id=fixture_id,
                match_id=first_bet_match_id
            )
            self.message_bus.publish(start_intro_message, broadcast=True)

            logger.info(f"🎬 START_INTRO message dispatched for fixture {fixture_id}, match {first_bet_match_id}")

        except Exception as e:
            logger.error(f"❌ Failed to dispatch START_INTRO message: {e}")

    def _get_first_bet_match_id(self, fixture_id: str) -> Optional[int]:
        """Get the ID of the first match set to 'bet' status in the fixture"""
        try:
            session = self.db_manager.get_session()
            try:
                from ..database.models import MatchModel

                # Find the first match with 'bet' status in this fixture
                first_bet_match = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.status == 'bet',
                    MatchModel.active_status == True
                ).order_by(MatchModel.match_number.asc()).first()

                if first_bet_match:
                    return first_bet_match.id
                else:
                    logger.warning(f"No match with 'bet' status found in fixture {fixture_id}")
                    return None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to get first bet match ID for fixture {fixture_id}: {e}")
            return None

    def _unzip_match_zip_file(self, match_id: int):
        """Unzip the ZIP file associated with a match to a temporary directory"""
        try:
            import zipfile
            import tempfile
            import os
            from pathlib import Path

            logger.info(f"DEBUG: Starting ZIP extraction for match {match_id}")

            # CLEANUP: Delete all previous unzipped match directories before proceeding
            self._cleanup_previous_match_extractions()

            session = self.db_manager.get_session()
            try:
                # Get the match from database
                match = session.query(MatchModel).filter_by(id=match_id).first()

                if not match:
                    logger.warning(f"DEBUG: Match {match_id} not found in database, skipping ZIP extraction")
                    return

                logger.info(f"DEBUG: Found match {match_id}, zip_filename: {match.zip_filename}")

                if not match.zip_filename:
                    logger.info(f"DEBUG: Match {match_id} has no associated ZIP file, skipping extraction")
                    return

                # Determine ZIP file location (ZIP files are stored in the zip_files directory)
                from ..config.settings import get_user_data_dir
                user_data_dir = get_user_data_dir()
                zip_file_path = user_data_dir / "zip_files" / match.zip_filename

                logger.info(f"DEBUG: Looking for ZIP file at: {zip_file_path}")
                logger.info(f"DEBUG: ZIP file exists: {zip_file_path.exists()}")

                if not zip_file_path.exists():
                    logger.warning(f"DEBUG: ZIP file not found: {zip_file_path}")
                    return

                logger.info(f"DEBUG: ZIP file size: {zip_file_path.stat().st_size} bytes")

                # Create temporary directory for extraction
                temp_dir = Path(tempfile.mkdtemp(prefix=f"match_{match_id}_"))
                logger.info(f"DEBUG: Created temp directory: {temp_dir}")

                # Extract the ZIP file
                logger.info(f"DEBUG: Starting ZIP extraction...")
                with zipfile.ZipFile(str(zip_file_path), 'r') as zip_ref:
                    file_list = zip_ref.namelist()
                    logger.info(f"DEBUG: ZIP contains {len(file_list)} files: {file_list}")
                    zip_ref.extractall(str(temp_dir))

                # Log extraction results
                extracted_files = list(temp_dir.rglob("*"))
                logger.info(f"DEBUG: Successfully extracted {len(extracted_files)} files from {match.zip_filename}")
                for extracted_file in extracted_files:
                    if extracted_file.is_file():
                        logger.info(f"DEBUG: Extracted file: {extracted_file} (size: {extracted_file.stat().st_size} bytes)")

                # Store the temporary directory path for potential cleanup
                # In a real implementation, you might want to track this for cleanup
                match.temp_extract_path = str(temp_dir)

                # Update match in database with temp path (optional)
                session.commit()

                logger.info(f"DEBUG: ZIP extraction completed for match {match_id}")

            finally:
                session.close()

        except Exception as e:
            logger.error(f"DEBUG: Failed to unzip ZIP file for match {match_id}: {e}")
            import traceback
            logger.error(f"DEBUG: Full traceback: {traceback.format_exc()}")

    def _calculate_match_result(self, fixture_id: str, match_id: int) -> str:
        """Calculate match result based on betting patterns and CAP logic"""
        try:
            logger.info(f"🔍 [DEBUG] Starting match result calculation for fixture {fixture_id}, match {match_id}")

            session = self.db_manager.get_session()
            try:
                # Step 2.1: Retrieve UNDER/OVER Coefficients
                logger.info(f"📊 [DEBUG] Step 2.1: Retrieving UNDER/OVER coefficients for fixture {fixture_id}")
                under_coeff, over_coeff = self._get_fixture_coefficients(fixture_id, session)
                logger.info(f"📊 [DEBUG] Retrieved coefficients - UNDER: {under_coeff}, OVER: {over_coeff}")

                # Step 2.2: Fallback Check
                if under_coeff is None or over_coeff is None:
                    logger.warning(f"⚠️ [DEBUG] Step 2.2: No coefficients found for fixture {fixture_id}, falling back to random selection")
                    fallback_result = self._weighted_random_selection(1.0, 1.0)
                    logger.info(f"🎲 [DEBUG] Fallback random result: {fallback_result}")
                    return fallback_result

                # Step 2.3: Calculate UNDER Payout
                logger.info(f"💰 [DEBUG] Step 2.3: Calculating UNDER payout (match_id={match_id}, coeff={under_coeff})")
                under_payout = self._calculate_payout(match_id, 'UNDER', under_coeff, session)
                logger.info(f"💰 [DEBUG] UNDER payout calculated: {under_payout:.2f}")

                # Step 2.4: Calculate OVER Payout
                logger.info(f"💰 [DEBUG] Step 2.4: Calculating OVER payout (match_id={match_id}, coeff={over_coeff})")
                over_payout = self._calculate_payout(match_id, 'OVER', over_coeff, session)
                logger.info(f"💰 [DEBUG] OVER payout calculated: {over_payout:.2f}")

                # Step 2.5: Calculate Total Payin
                logger.info(f"💵 [DEBUG] Step 2.5: Calculating total payin for match {match_id}")
                total_payin = self._calculate_total_payin(match_id, session)
                logger.info(f"💵 [DEBUG] Total payin calculated: {total_payin:.2f}")

                # Step 2.6: Get Redistribution CAP
                logger.info(f"🎯 [DEBUG] Step 2.6: Retrieving redistribution CAP percentage")
                cap_percentage = self._get_redistribution_cap()
                logger.info(f"🎯 [DEBUG] CAP percentage: {cap_percentage}%")

                # Step 2.7: Calculate CAP Threshold
                logger.info(f"📏 [DEBUG] Step 2.7: Calculating CAP threshold")
                max_payout = max(under_payout, over_payout)
                cap_threshold = total_payin * (cap_percentage / 100.0)
                logger.info(f"📏 [DEBUG] Max payout: {max_payout:.2f}, CAP threshold: {cap_threshold:.2f}")

                # Step 2.8: CAP Logic Decision
                logger.info(f"🧠 [DEBUG] Step 2.8: Evaluating CAP logic")
                logger.info(f"📊 [DEBUG] Summary - UNDER payout: {under_payout:.2f}, OVER payout: {over_payout:.2f}, total_payin: {total_payin:.2f}, CAP: {cap_percentage}%, threshold: {cap_threshold:.2f}")

                if max_payout > cap_threshold:
                    # CAP exceeded - select outcome with lower payout to minimize losses
                    logger.info(f"🚨 [DEBUG] CAP exceeded! Max payout ({max_payout:.2f}) > threshold ({cap_threshold:.2f})")
                    if under_payout <= over_payout:
                        result = 'UNDER'
                        logger.info(f"✅ [DEBUG] Selecting UNDER (lower payout: {under_payout:.2f} vs {over_payout:.2f})")
                    else:
                        result = 'OVER'
                        logger.info(f"✅ [DEBUG] Selecting OVER (lower payout: {over_payout:.2f} vs {under_payout:.2f})")
                else:
                    # CAP not exceeded - use weighted random selection
                    logger.info(f"✅ [DEBUG] CAP not exceeded. Max payout ({max_payout:.2f}) <= threshold ({cap_threshold:.2f})")
                    logger.info(f"🎲 [DEBUG] Step 2.9: Performing weighted random selection")
                    result = self._weighted_random_selection(under_coeff, over_coeff)
                    logger.info(f"🎯 [DEBUG] Weighted random result: {result}")

                logger.info(f"🏁 [DEBUG] Match result calculation completed: {result}")
                return result

            finally:
                session.close()

        except Exception as e:
            logger.error(f"❌ [DEBUG] Failed to calculate match result: {e}")
            # Fallback to random
            logger.info(f"🔄 [DEBUG] Using fallback random selection")
            fallback_result = self._weighted_random_selection(1.0, 1.0)
            logger.info(f"🎲 [DEBUG] Fallback result: {fallback_result}")
            return fallback_result

    def _calculate_payout(self, match_id: int, outcome: str, coefficient: float, session) -> float:
        """Calculate payout for an outcome"""
        try:
            logger.info(f"💰 [DEBUG] Calculating payout for {outcome} (match_id={match_id}, coefficient={coefficient})")

            # Get total bets for this outcome on this match
            logger.info(f"📊 [DEBUG] Querying bets for outcome '{outcome}' on match {match_id}")
            total_bet_amount = session.query(
                BetDetailModel.amount
            ).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == outcome,
                BetDetailModel.result == 'pending'
            ).all()

            bet_count = len(total_bet_amount) if total_bet_amount else 0
            logger.info(f"📊 [DEBUG] Found {bet_count} pending bets for {outcome}")

            total_amount = sum(bet.amount for bet in total_bet_amount) if total_bet_amount else 0.0
            logger.info(f"💵 [DEBUG] Total bet amount for {outcome}: {total_amount:.2f}")

            payout = total_amount * coefficient
            logger.info(f"💰 [DEBUG] Calculated payout for {outcome}: {total_amount:.2f} × {coefficient} = {payout:.2f}")

            return payout

        except Exception as e:
            logger.error(f"❌ [DEBUG] Failed to calculate payout for {outcome}: {e}")
            return 0.0

    def _calculate_total_payin(self, match_id: int, session) -> float:
        """Calculate total payin (sum of all UNDER + OVER bets)"""
        try:
            logger.info(f"💵 [DEBUG] Calculating total payin for match {match_id}")

            # Query UNDER bets
            logger.info(f"📊 [DEBUG] Querying UNDER bets for match {match_id}")
            total_under = session.query(
                BetDetailModel.amount
            ).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == 'UNDER',
                BetDetailModel.result == 'pending'
            ).all()

            under_count = len(total_under) if total_under else 0
            under_amount = sum(bet.amount for bet in total_under) if total_under else 0.0
            logger.info(f"💵 [DEBUG] UNDER bets: {under_count} bets, total amount: {under_amount:.2f}")

            # Query OVER bets
            logger.info(f"📊 [DEBUG] Querying OVER bets for match {match_id}")
            total_over = session.query(
                BetDetailModel.amount
            ).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == 'OVER',
                BetDetailModel.result == 'pending'
            ).all()

            over_count = len(total_over) if total_over else 0
            over_amount = sum(bet.amount for bet in total_over) if total_over else 0.0
            logger.info(f"💵 [DEBUG] OVER bets: {over_count} bets, total amount: {over_amount:.2f}")

            total_payin = under_amount + over_amount
            logger.info(f"💵 [DEBUG] Total payin calculated: {under_amount:.2f} + {over_amount:.2f} = {total_payin:.2f}")

            return total_payin

        except Exception as e:
            logger.error(f"❌ [DEBUG] Failed to calculate total payin: {e}")
            return 0.0

    def _get_fixture_coefficients(self, fixture_id: str, session) -> tuple:
        """Get UNDER/OVER coefficients from fixture/match outcomes"""
        try:
            # Get one active match from the fixture to get UNDER/OVER coefficients
            match = session.query(MatchModel).filter(
                MatchModel.fixture_id == fixture_id,
                MatchModel.active_status == True
            ).first()

            if not match:
                logger.warning(f"No active matches found for fixture {fixture_id}")
                return None, None

            # Get UNDER/OVER coefficients from match outcomes
            under_coeff = None
            over_coeff = None

            for outcome in match.outcomes:
                if outcome.column_name == 'UNDER':
                    under_coeff = outcome.float_value
                elif outcome.column_name == 'OVER':
                    over_coeff = outcome.float_value

            return under_coeff, over_coeff

        except Exception as e:
            logger.error(f"Failed to get fixture coefficients: {e}")
            return None, None

    def _get_redistribution_cap(self) -> float:
        """Get redistribution CAP percentage from configuration"""
        try:
            session = self.db_manager.get_session()
            try:
                # Get global CAP configuration
                cap_config = session.query(GameConfigModel).filter_by(
                    config_key='redistribution_cap'
                ).first()

                if cap_config:
                    cap_value = cap_config.get_typed_value()
                    if isinstance(cap_value, (int, float)) and 10 <= cap_value <= 100:
                        return float(cap_value)
                    else:
                        logger.warning(f"Invalid CAP value: {cap_value}, using default 70%")
                else:
                    logger.debug("No CAP configuration found, using default 70%")

                return 70.0  # Default CAP

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to get redistribution CAP: {e}")
            return 70.0

    def _weighted_random_selection(self, under_coeff: float, over_coeff: float) -> str:
        """Weighted random selection based on inverse coefficients"""
        try:
            import random

            logger.info(f"🎲 [DEBUG] Weighted random selection - UNDER coeff: {under_coeff}, OVER coeff: {over_coeff}")

            # Higher coefficients get lower probability (inverse weighting)
            logger.info(f"⚖️ [DEBUG] Calculating inverse weights (higher coeff = lower probability)")
            under_weight = 1.0 / under_coeff if under_coeff > 0 else 1.0
            over_weight = 1.0 / over_coeff if over_coeff > 0 else 1.0
            logger.info(f"⚖️ [DEBUG] Weights calculated - UNDER: {under_weight:.4f}, OVER: {over_weight:.4f}")

            total_weight = under_weight + over_weight
            logger.info(f"📊 [DEBUG] Total weight: {total_weight:.4f}")

            if total_weight == 0:
                # Fallback to equal weights
                logger.warning(f"⚠️ [DEBUG] Total weight is zero, falling back to equal weights")
                under_weight = over_weight = 1.0
                total_weight = 2.0
                logger.info(f"⚖️ [DEBUG] Equal weights applied - UNDER: {under_weight}, OVER: {over_weight}")

            # Generate random number
            rand = random.uniform(0, total_weight)
            logger.info(f"🎯 [DEBUG] Generated random number: {rand:.4f} (range: 0-{total_weight:.4f})")

            # Determine result
            if rand < under_weight:
                logger.info(f"🎯 [DEBUG] Random {rand:.4f} < UNDER weight {under_weight:.4f} → selecting UNDER")
                return 'UNDER'
            else:
                logger.info(f"🎯 [DEBUG] Random {rand:.4f} >= UNDER weight {under_weight:.4f} → selecting OVER")
                return 'OVER'

        except Exception as e:
            logger.error(f"❌ [DEBUG] Failed to perform weighted random selection: {e}")
            # Fallback to 50/50
            logger.info(f"🔄 [DEBUG] Using 50/50 fallback")
            import random
            fallback_result = 'UNDER' if random.random() < 0.5 else 'OVER'
            logger.info(f"🎲 [DEBUG] 50/50 fallback result: {fallback_result}")
            return fallback_result

    def _send_play_video_match(self, fixture_id: str, match_id: int, result: str):
        """Send PLAY_VIDEO_MATCH message with calculated result"""
        try:
            # Get video filename based on result
            video_filename = self._get_match_video_filename(match_id, result)

            # Unzip the match ZIP file before sending PLAY_VIDEO_MATCH
            logger.info(f"Unzipping ZIP file for match {match_id} before sending PLAY_VIDEO_MATCH")
            self._unzip_match_zip_file(match_id)

            # Send PLAY_VIDEO_MATCH message with result
            play_message = MessageBuilder.play_video_match(
                sender=self.name,
                fixture_id=fixture_id,
                match_id=match_id,
                video_filename=video_filename,
                result=result
            )

            self.message_bus.publish(play_message)
            logger.info(f"Sent PLAY_VIDEO_MATCH for fixture {fixture_id}, match {match_id}, result {result}, video {video_filename}")

        except Exception as e:
            logger.error(f"Failed to send PLAY_VIDEO_MATCH: {e}")

    def _get_match_video_filename(self, match_id: int, result: str) -> str:
        """Get appropriate video filename based on result"""
        try:
            # For now, use result.mp4 (UNDER.mp4 or OVER.mp4)
            # In future, this could be more sophisticated based on match data
            return f"{result}.mp4"

        except Exception as e:
            logger.error(f"Failed to get match video filename: {e}")
            return f"{result}.mp4"  # Fallback

    def _handle_play_video_match_done(self, message: Message):
        """Handle PLAY_VIDEO_MATCH_DONE message and query database for result"""
        try:
            fixture_id = message.data.get("fixture_id")
            match_id = message.data.get("match_id")

            logger.info(f"Processing PLAY_VIDEO_MATCH_DONE for fixture {fixture_id}, match {match_id}")

            # DEBUG: Log the full message data
            logger.info(f"DEBUG PLAY_VIDEO_MATCH_DONE: message.data = {message.data}")

            # Set match status to 'ingame'
            self._set_match_status(match_id, 'ingame')

            # Query database for the previously extracted result
            extracted_result = self._query_extracted_result(match_id)

            logger.info(f"DEBUG PLAY_VIDEO_MATCH_DONE: extracted_result = '{extracted_result}'")

            if extracted_result:
                logger.info(f"Found extracted result for match {match_id}: {extracted_result}")
                # Send PLAY_VIDEO_RESULTS message (note: RESULTS plural as per user request)
                self._send_play_video_results(fixture_id, match_id, extracted_result)
            else:
                logger.error(f"No extracted result found for match {match_id}")
                # Fallback to random selection
                fallback_result = self._fallback_result_selection()
                logger.info(f"Using fallback result for match {match_id}: {fallback_result}")
                self._send_play_video_results(fixture_id, match_id, fallback_result)

        except Exception as e:
            logger.error(f"Failed to handle PLAY_VIDEO_MATCH_DONE message: {e}")
            import traceback
            logger.error(f"DEBUG PLAY_VIDEO_MATCH_DONE: Exception traceback: {traceback.format_exc()}")

    def _handle_play_video_result_done(self, message: Message):
        """Handle PLAY_VIDEO_RESULTS_DONE message - result video finished, send MATCH_DONE and START_INTRO"""
        try:
            fixture_id = message.data.get("fixture_id")
            match_id = message.data.get("match_id")
            result = message.data.get("result")

            logger.info(f"Processing PLAY_VIDEO_RESULTS_DONE for fixture {fixture_id}, match {match_id}, result {result}")

            # DEBUG: Log the full message data
            logger.info(f"DEBUG PLAY_VIDEO_RESULTS_DONE: message.data = {message.data}")

            # Update match status to 'done' and save result
            self._set_match_status_and_result(match_id, 'done', result)

            # Send MATCH_DONE message with result
            self._send_match_done(fixture_id, match_id, result)

            # Send START_INTRO message to start the next match cycle
            self._dispatch_start_intro(fixture_id)

        except Exception as e:
            logger.error(f"Failed to handle PLAY_VIDEO_RESULTS_DONE message: {e}")

    def _handle_match_done(self, message: Message):
        """Handle MATCH_DONE message"""
        try:
            fixture_id = message.data.get("fixture_id")
            match_id = message.data.get("match_id")
            result = message.data.get("result")

            logger.info(f"Processing MATCH_DONE for fixture {fixture_id}, match {match_id}, result {result}")

            # DEBUG: Log the message data in detail
            logger.info(f"DEBUG MATCH_DONE: message.data = {message.data}")

            # DEBUG: Check current match state before update
            session = self.db_manager.get_session()
            try:
                match = session.query(MatchModel).filter_by(id=match_id).first()
                if match:
                    logger.info(f"DEBUG MATCH_DONE: Before update - match {match_id} status='{match.status}', result='{match.result}'")
                else:
                    logger.error(f"DEBUG MATCH_DONE: Match {match_id} not found in database!")
            finally:
                session.close()

            # Update match status to 'done' and save result
            self._set_match_status_and_result(match_id, 'done', result)

            # DEBUG: Check match state after update
            session = self.db_manager.get_session()
            try:
                match = session.query(MatchModel).filter_by(id=match_id).first()
                if match:
                    logger.info(f"DEBUG MATCH_DONE: After update - match {match_id} status='{match.status}', result='{match.result}'")
                else:
                    logger.error(f"DEBUG MATCH_DONE: Match {match_id} still not found after update!")
            finally:
                session.close()

            # Wait 2 seconds then send NEXT_MATCH
            import time
            time.sleep(2)

            # Send NEXT_MATCH message
            self._send_next_match(fixture_id, match_id)

        except Exception as e:
            logger.error(f"Failed to handle MATCH_DONE message: {e}")

    def _handle_system_status(self, message: Message):
        """Handle SYSTEM_STATUS messages, particularly fixture update completion and status requests"""
        try:
            logger.debug(f"GamesThread handling SYSTEM_STATUS message from {message.sender}: {message.data}")
            status = message.data.get("status")
            details = message.data.get("details", {})

            if status == "fixture_update_completed":
                synchronized_matches = message.data.get("synchronized_matches", 0)
                downloaded_zips = message.data.get("downloaded_zips", 0)

                logger.info(f"Fixture update completed: {synchronized_matches} matches synchronized, {downloaded_zips} ZIPs downloaded")

                # Check if we should start a game now that fixtures are available
                if synchronized_matches > 0 and not self.game_active:
                    logger.info("New fixtures available and no game is active - attempting to start game")
                    # Send START_GAME message to ourselves to trigger game start
                    start_game_message = Message(
                        type=MessageType.START_GAME,
                        sender=self.name,
                        recipient=self.name,
                        data={
                            "timestamp": time.time()
                        }
                    )
                    self.message_bus.publish(start_game_message)

            elif status == "status_request":
                # Handle status requests from Qt player
                request_type = details.get("request_type")
                logger.debug(f"Status request type: {request_type}, details: {details}")
                if request_type == "game_status":
                    logger.info(f"Received game status request from {message.sender}")

                    # Determine current game status
                    game_status = self._determine_game_status()
                    logger.debug(f"Determined game status: {game_status}")

                    # Send GAME_STATUS response back to the requester (broadcast to ensure delivery)
                    response = Message(
                        type=MessageType.GAME_STATUS,
                        sender=self.name,
                        recipient=message.sender,
                        data={
                            "status": game_status,
                            "fixture_id": self.current_fixture_id,
                            "game_active": self.game_active,
                            "timestamp": time.time()
                        },
                        correlation_id=message.correlation_id
                    )
                    logger.debug(f"About to publish GAME_STATUS response: {response.data}")
                    # Broadcast the response to ensure it reaches the Qt player
                    self.message_bus.publish(response, broadcast=True)
                    logger.info(f"Sent game status response to {message.sender}: {game_status}")
                else:
                    logger.debug(f"Ignoring status_request with unknown request_type: {request_type}")
            else:
                logger.debug(f"Ignoring SYSTEM_STATUS message with unknown status: {status}")

        except Exception as e:
            logger.error(f"Failed to handle system status message: {e}")
            import traceback
            logger.error(f"Full traceback: {traceback.format_exc()}")

    def _set_match_status(self, match_id: int, status: str):
        """Set match status in database"""
        try:
            session = self.db_manager.get_session()
            try:
                match = session.query(MatchModel).filter_by(id=match_id).first()
                if match:
                    match.status = status
                    session.commit()
                    logger.info(f"Updated match {match_id} status to {status}")
                else:
                    logger.error(f"Match {match_id} not found")
            finally:
                session.close()
        except Exception as e:
            logger.error(f"Failed to set match status: {e}")

    def _set_match_status_and_result(self, match_id: int, status: str, result: str):
        """Set match status and result in database"""
        try:
            logger.info(f"DEBUG _set_match_status_and_result: Called with match_id={match_id}, status='{status}', result='{result}'")

            session = self.db_manager.get_session()
            try:
                match = session.query(MatchModel).filter_by(id=match_id).first()
                if match:
                    logger.info(f"DEBUG _set_match_status_and_result: Found match {match_id}, current status='{match.status}', current result='{match.result}'")
                    match.status = status
                    match.result = result
                    # Set end_time when match is completed
                    if status == 'done':
                        match.end_time = datetime.utcnow()
                        logger.info(f"DEBUG _set_match_status_and_result: Set end_time for match {match_id}")
                    session.commit()
                    logger.info(f"Updated match {match_id} status to {status} and result to {result}")

                    # DEBUG: Verify the update
                    session.refresh(match)
                    logger.info(f"DEBUG _set_match_status_and_result: After commit - match.status='{match.status}', match.result='{match.result}'")
                else:
                    logger.error(f"Match {match_id} not found")
            finally:
                session.close()
        except Exception as e:
            logger.error(f"Failed to set match status and result: {e}")
            import traceback
            logger.error(f"DEBUG _set_match_status_and_result: Exception traceback: {traceback.format_exc()}")

    def _perform_result_extraction(self, fixture_id: str, match_id: int) -> str:
        """Perform complex result extraction logic"""
        try:
            logger.info(f"🔍 [EXTRACTION DEBUG] Starting result extraction for fixture {fixture_id}, match {match_id}")

            session = self.db_manager.get_session()
            try:
                # DEBUG: Check if match exists and its current state
                match = session.query(MatchModel).filter_by(id=match_id).first()
                if match:
                    logger.info(f"🔍 [EXTRACTION DEBUG] Match {match_id} found - status='{match.status}', result='{match.result}', fixture_id='{match.fixture_id}'")
                else:
                    logger.error(f"🔍 [EXTRACTION DEBUG] Match {match_id} NOT FOUND in database!")
                    return self._fallback_result_selection()

                # Step 1: Get match outcomes to determine available result options
                logger.info(f"📊 [EXTRACTION DEBUG] Step 1: Retrieving match outcomes for match {match_id}")
                match_outcomes = session.query(MatchOutcomeModel).filter(
                    MatchOutcomeModel.match_id == match_id
                ).all()

                available_outcome_names = [outcome.column_name for outcome in match_outcomes]
                logger.info(f"📊 [EXTRACTION DEBUG] Found {len(match_outcomes)} match outcomes: {available_outcome_names}")

                # DEBUG: Log detailed outcome information
                for outcome in match_outcomes:
                    logger.info(f"📊 [EXTRACTION DEBUG] Outcome: {outcome.column_name} = {outcome.float_value}")

                # Step 2: Get result options that correspond to match outcomes (excluding UNDER/OVER)
                logger.info(f"🎯 [EXTRACTION DEBUG] Step 2: Filtering result options to match fixture outcomes")
                from ..database.models import ResultOptionModel, AvailableBetModel, ExtractionAssociationModel

                # Only include result options that have corresponding outcomes in this match
                result_options = session.query(ResultOptionModel).filter(
                    ResultOptionModel.is_active == True,
                    ResultOptionModel.result_name.in_(available_outcome_names),
                    ~ResultOptionModel.result_name.in_(['UNDER', 'OVER'])  # Exclude UNDER/OVER as handled separately
                ).all()

                logger.info(f"🎯 [EXTRACTION DEBUG] Filtered to {len(result_options)} result options: {[opt.result_name for opt in result_options]}")

                payouts = {}
                total_bet_amount = 0.0

                # Step 3: Calculate payouts for each result option
                logger.info(f"💰 [EXTRACTION DEBUG] Step 3: Calculating payouts for {len(result_options)} result options")
                for result_option in result_options:
                    result_name = result_option.result_name
                    logger.info(f"💰 [EXTRACTION DEBUG] Processing result option: {result_name}")

                    # Get associated available bets for this result
                    associations = session.query(ExtractionAssociationModel).filter(
                        ExtractionAssociationModel.extraction_result == result_name
                    ).all()

                    logger.info(f"💰 [EXTRACTION DEBUG] Found {len(associations)} associations for {result_name}")
                    payout = 0.0

                    for association in associations:
                        outcome_name = association.outcome_name
                        logger.info(f"💰 [EXTRACTION DEBUG] Processing association: {outcome_name} -> {result_name}")

                        # Get coefficient for this outcome from match outcomes
                        match_outcome = session.query(MatchOutcomeModel).filter(
                            MatchOutcomeModel.match_id == match_id,
                            MatchOutcomeModel.column_name == outcome_name
                        ).first()

                        if match_outcome:
                            coefficient = match_outcome.float_value
                            logger.info(f"💰 [EXTRACTION DEBUG] Found coefficient for {outcome_name}: {coefficient}")

                            # Get total bets for this outcome on this match
                            bet_amount = session.query(BetDetailModel.amount).filter(
                                BetDetailModel.match_id == match_id,
                                BetDetailModel.outcome == outcome_name,
                                BetDetailModel.result == 'pending'
                            ).all()

                            total_outcome_amount = sum(bet.amount for bet in bet_amount) if bet_amount else 0.0
                            logger.info(f"💰 [EXTRACTION DEBUG] Total bet amount for {outcome_name}: {total_outcome_amount:.2f}")
                            payout += total_outcome_amount * coefficient
                            logger.info(f"💰 [EXTRACTION DEBUG] Added payout for {outcome_name}: {total_outcome_amount:.2f} × {coefficient} = {total_outcome_amount * coefficient:.2f}")
                        else:
                            logger.warning(f"💰 [EXTRACTION DEBUG] No match outcome found for {outcome_name}")

                    payouts[result_name] = payout
                    logger.info(f"💰 [EXTRACTION DEBUG] Total payout for {result_name}: {payout:.2f}")

                # Step 4: Calculate total bet amount (excluding UNDER/OVER)
                logger.info(f"💵 [EXTRACTION DEBUG] Step 4: Calculating total bet amount for match {match_id}")
                all_bets = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.result == 'pending',
                    ~BetDetailModel.outcome.in_(['UNDER', 'OVER'])
                ).all()

                total_bet_amount = sum(bet.amount for bet in all_bets) if all_bets else 0.0
                logger.info(f"💵 [EXTRACTION DEBUG] Total bet amount calculated: {total_bet_amount:.2f} from {len(all_bets)} bets")

                # Step 5: Get redistribution CAP
                logger.info(f"🎯 [EXTRACTION DEBUG] Step 5: Retrieving redistribution CAP")
                cap_percentage = self._get_redistribution_cap()
                cap_threshold = total_bet_amount * (cap_percentage / 100.0)
                logger.info(f"🎯 [EXTRACTION DEBUG] CAP percentage: {cap_percentage}%, threshold: {cap_threshold:.2f}")

                logger.info(f"📊 [EXTRACTION DEBUG] Extraction summary - {len(payouts)} results, total_bet_amount={total_bet_amount:.2f}, CAP={cap_percentage}%, threshold={cap_threshold:.2f}")
                logger.info(f"📊 [EXTRACTION DEBUG] Payouts: {payouts}")

                # Step 6: Filter payouts below CAP threshold
                logger.info(f"🎯 [EXTRACTION DEBUG] Step 6: Filtering payouts below CAP threshold")
                eligible_payouts = {k: v for k, v in payouts.items() if v <= cap_threshold}
                logger.info(f"🎯 [EXTRACTION DEBUG] Eligible payouts (≤ {cap_threshold:.2f}): {eligible_payouts}")

                if not eligible_payouts:
                    # No payouts below CAP, select the lowest payout
                    lowest_payout_result = min(payouts, key=payouts.get)
                    eligible_payouts = {lowest_payout_result: payouts[lowest_payout_result]}
                    logger.info(f"🚨 [EXTRACTION DEBUG] No payouts below CAP, selecting lowest payout: {lowest_payout_result} ({payouts[lowest_payout_result]:.2f})")

                # Step 7: Perform weighted random selection
                logger.info(f"🎲 [EXTRACTION DEBUG] Step 7: Performing weighted random selection from {len(eligible_payouts)} eligible results")
                selected_result = self._weighted_result_selection(eligible_payouts, session, match_id)
                logger.info(f"🎯 [EXTRACTION DEBUG] Selected result: {selected_result}")

                # Step 7.1: Log winning outcomes from results associations configurations
                winning_outcomes = session.query(ExtractionAssociationModel.outcome_name).filter(
                    ExtractionAssociationModel.extraction_result == selected_result
                ).distinct().all()
                winning_outcome_names = [outcome.outcome_name for outcome in winning_outcomes]
                logger.info(f"🏆 [EXTRACTION DEBUG] Winning outcomes for result '{selected_result}': {winning_outcome_names}")

                # Step 8: Update bet results
                logger.info(f"💾 [EXTRACTION DEBUG] Step 8: Updating bet results for match {match_id}")
                self._update_bet_results(match_id, selected_result, session)

                # Step 9: Collect statistics
                logger.info(f"📈 [EXTRACTION DEBUG] Step 9: Collecting match statistics")
                self._collect_match_statistics(match_id, fixture_id, selected_result, session)

                logger.info(f"✅ [EXTRACTION DEBUG] Result extraction completed successfully: selected {selected_result}")

                # DEBUG: Final check - ensure result is not None
                if selected_result is None:
                    logger.error(f"❌ [EXTRACTION DEBUG] CRITICAL: selected_result is None! This should not happen.")
                    return self._fallback_result_selection()

                return selected_result

            finally:
                session.close()

        except Exception as e:
            logger.error(f"❌ [EXTRACTION DEBUG] Failed to perform result extraction: {e}")
            import traceback
            logger.error(f"❌ [EXTRACTION DEBUG] Full traceback: {traceback.format_exc()}")
            # Fallback to random selection
            logger.info(f"🔄 [EXTRACTION DEBUG] Using fallback random selection")
            fallback_result = self._fallback_result_selection()
            logger.info(f"🔄 [EXTRACTION DEBUG] Fallback result: {fallback_result}")
            return fallback_result

    def _weighted_result_selection(self, eligible_payouts: Dict[str, float], session, match_id: int) -> str:
        """Perform weighted random selection based on inverse coefficients"""
        try:
            import random

            weights = {}
            total_weight = 0.0

            for result_name in eligible_payouts.keys():
                # Get coefficient for this result (inverse weighting - higher coefficient = lower probability)
                # For simplicity, we'll use a default coefficient or calculate based on payout
                # In a real implementation, you'd have result-specific coefficients
                coefficient = 1.0 / (eligible_payouts[result_name] + 1.0)  # Avoid division by zero
                weights[result_name] = coefficient
                total_weight += coefficient

            if total_weight == 0:
                # Fallback to equal weights
                weight_value = 1.0 / len(eligible_payouts)
                weights = {k: weight_value for k in eligible_payouts.keys()}
                total_weight = sum(weights.values())

            # Generate random selection
            rand = random.uniform(0, total_weight)
            cumulative = 0.0

            for result_name, weight in weights.items():
                cumulative += weight
                if rand <= cumulative:
                    return result_name

            # Fallback
            return list(eligible_payouts.keys())[0]

        except Exception as e:
            logger.error(f"Failed to perform weighted result selection: {e}")
            return list(eligible_payouts.keys())[0]

    def _get_outcome_coefficient(self, match_id: int, outcome: str, session) -> float:
        """Get coefficient for a specific outcome from match outcomes"""
        try:
            from ..database.models import MatchOutcomeModel

            # For UNDER/OVER outcomes, get from fixture coefficients
            if outcome in ['UNDER', 'OVER']:
                fixture_id = session.query(MatchModel.fixture_id).filter(MatchModel.id == match_id).first()
                if fixture_id:
                    return self._get_fixture_coefficients(fixture_id[0], session)[0 if outcome == 'UNDER' else 1] or 1.0
                return 1.0

            # For other outcomes, get from match outcomes
            match_outcome = session.query(MatchOutcomeModel).filter(
                MatchOutcomeModel.match_id == match_id,
                MatchOutcomeModel.column_name == outcome
            ).first()

            return match_outcome.float_value if match_outcome else 1.0

        except Exception as e:
            logger.error(f"Failed to get coefficient for outcome {outcome}: {e}")
            return 1.0

    def _update_bet_results(self, match_id: int, selected_result: str, session):
        """Update bet results for UNDER/OVER and selected result with win amount calculation"""
        try:
            logger.info(f"DEBUG _update_bet_results: Starting for match {match_id}, selected_result='{selected_result}'")

            # Get coefficient for the selected result
            win_coefficient = self._get_outcome_coefficient(match_id, selected_result, session)
            logger.info(f"DEBUG _update_bet_results: win_coefficient = {win_coefficient}")

            # Update UNDER/OVER bets - they win if they match the UNDER/OVER outcome
            under_over_outcome = 'UNDER' if selected_result == 'UNDER' else 'OVER' if selected_result == 'OVER' else None
            logger.info(f"DEBUG _update_bet_results: under_over_outcome = '{under_over_outcome}'")

            # DEBUG: Log the current match result before updating
            match = session.query(MatchModel).filter_by(id=match_id).first()
            if match:
                logger.info(f"DEBUG _update_bet_results: Current match.result before formatting = '{match.result}'")

            if under_over_outcome:
                # UNDER/OVER bet wins
                under_over_bets = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.outcome == under_over_outcome,
                    BetDetailModel.result == 'pending'
                ).all()

                logger.info(f"DEBUG _update_bet_results: Found {len(under_over_bets)} winning {under_over_outcome} bets")

                for bet in under_over_bets:
                    win_amount = bet.amount * win_coefficient
                    bet.set_result('win', win_amount)
                    logger.info(f"DEBUG _update_bet_results: Set bet {bet.id} to win with amount {win_amount}")

                # Other UNDER/OVER bet loses
                other_under_over = 'OVER' if under_over_outcome == 'UNDER' else 'UNDER'
                losing_count = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.outcome == other_under_over,
                    BetDetailModel.result == 'pending'
                ).update({'result': 'lost'})
                logger.info(f"DEBUG _update_bet_results: Set {losing_count} {other_under_over} bets to lost")
            else:
                # No UNDER/OVER result selected, all UNDER/OVER bets lose
                losing_count = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.outcome.in_(['UNDER', 'OVER']),
                    BetDetailModel.result == 'pending'
                ).update({'result': 'lost'})
                logger.info(f"DEBUG _update_bet_results: Set {losing_count} UNDER/OVER bets to lost (no UNDER/OVER result)")

            # Update bets for the selected result to 'win' (if not UNDER/OVER)
            if selected_result not in ['UNDER', 'OVER']:
                winning_bets = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.outcome == selected_result,
                    BetDetailModel.result == 'pending'
                ).all()

                logger.info(f"DEBUG _update_bet_results: Found {len(winning_bets)} winning {selected_result} bets")

                for bet in winning_bets:
                    win_amount = bet.amount * win_coefficient
                    bet.set_result('win', win_amount)
                    logger.info(f"DEBUG _update_bet_results: Set bet {bet.id} to win with amount {win_amount}")

            # Update all other bets to 'lost'
            losing_count = session.query(BetDetailModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.result == 'pending',
                ~BetDetailModel.outcome.in_([selected_result, 'UNDER', 'OVER'])
            ).update({'result': 'lost'})
            logger.info(f"DEBUG _update_bet_results: Set {losing_count} other bets to lost")

            # Update the match result in the matches table with winning outcomes in parentheses
            match = session.query(MatchModel).filter_by(id=match_id).first()
            if match:
                logger.info(f"DEBUG _update_bet_results: Before update - match.result = '{match.result}'")

                # Get winning outcomes for the selected result
                winning_outcomes = session.query(ExtractionAssociationModel.outcome_name).filter(
                    ExtractionAssociationModel.extraction_result == selected_result
                ).distinct().all()
                winning_outcome_names = [outcome.outcome_name for outcome in winning_outcomes]
                logger.info(f"DEBUG _update_bet_results: Found {len(winning_outcomes)} winning outcomes for '{selected_result}': {winning_outcome_names}")

                # Include UNDER/OVER if applicable
                under_over_result = None
                if under_over_outcome:
                    under_over_result = under_over_outcome
                    logger.info(f"DEBUG _update_bet_results: UNDER/OVER result detected: '{under_over_result}'")

                # Format result to include winning outcomes and UNDER/OVER
                result_parts = []
                if selected_result not in ['UNDER', 'OVER']:
                    result_parts.append(selected_result)
                    logger.info(f"DEBUG _update_bet_results: Added main result '{selected_result}' to result_parts")
                if under_over_result:
                    result_parts.append(under_over_result)
                    logger.info(f"DEBUG _update_bet_results: Added UNDER/OVER result '{under_over_result}' to result_parts")
                if winning_outcome_names:
                    # Add winning outcomes that are not already included
                    additional_outcomes = [outcome for outcome in winning_outcome_names if outcome not in result_parts]
                    if additional_outcomes:
                        result_parts.extend(additional_outcomes)
                        logger.info(f"DEBUG _update_bet_results: Added additional outcomes {additional_outcomes} to result_parts")

                # Join with " + " separator
                formatted_result = " + ".join(result_parts) if result_parts else selected_result
                logger.info(f"DEBUG _update_bet_results: Final result_parts = {result_parts}, formatted_result = '{formatted_result}'")
                match.result = formatted_result
                logger.info(f"Updated match {match_id} result to {formatted_result}")
            else:
                logger.error(f"DEBUG _update_bet_results: Match {match_id} not found for result update!")

            session.commit()
            logger.info(f"Updated bet results for match {match_id}: winner={selected_result}, coefficient={win_coefficient}")

        except Exception as e:
            logger.error(f"Failed to update bet results: {e}")
            import traceback
            logger.error(f"DEBUG _update_bet_results: Exception traceback: {traceback.format_exc()}")
            session.rollback()

    def _collect_match_statistics(self, match_id: int, fixture_id: str, selected_result: str, session):
        """Collect and store statistics for match completion"""
        try:
            from ..database.models import ExtractionStatsModel, BetDetailModel, MatchModel
            import json

            # Get match information
            match = session.query(MatchModel).filter_by(id=match_id).first()
            if not match:
                logger.warning(f"Match {match_id} not found for statistics collection")
                return

            # Calculate statistics
            total_bets = session.query(BetDetailModel).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                MatchModel.active_status == True
            ).count()

            total_amount_collected = session.query(
                BetDetailModel.amount
            ).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                MatchModel.active_status == True
            ).all()
            total_amount_collected = sum(bet.amount for bet in total_amount_collected) if total_amount_collected else 0.0

            # Calculate redistribution amount (sum of all win_amounts)
            total_redistributed = session.query(
                BetDetailModel.win_amount
            ).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.result == 'win',
                MatchModel.active_status == True
            ).all()
            total_redistributed = sum(bet.win_amount for bet in total_redistributed) if total_redistributed else 0.0

            # Get UNDER/OVER specific statistics
            under_bets = session.query(BetDetailModel).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == 'UNDER',
                MatchModel.active_status == True
            ).count()

            under_amount = session.query(
                BetDetailModel.amount
            ).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == 'UNDER',
                MatchModel.active_status == True
            ).all()
            under_amount = sum(bet.amount for bet in under_amount) if under_amount else 0.0

            over_bets = session.query(BetDetailModel).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == 'OVER',
                MatchModel.active_status == True
            ).count()

            over_amount = session.query(
                BetDetailModel.amount
            ).join(MatchModel).filter(
                BetDetailModel.match_id == match_id,
                BetDetailModel.outcome == 'OVER',
                MatchModel.active_status == True
            ).all()
            over_amount = sum(bet.amount for bet in over_amount) if over_amount else 0.0

            # Check if CAP was applied
            cap_percentage = self._get_redistribution_cap()
            cap_applied = False
            cap_threshold = total_amount_collected * (cap_percentage / 100.0)

            # Get extraction result (the actual result selected)
            extraction_result = selected_result if selected_result not in ['UNDER', 'OVER'] else None

            # Create result breakdown (simplified for now)
            result_breakdown = {
                'selected_result': selected_result,
                'extraction_result': extraction_result,
                'under_over_result': selected_result if selected_result in ['UNDER', 'OVER'] else None,
                'total_payin': total_amount_collected,
                'total_payout': total_redistributed,
                'profit': total_amount_collected - total_redistributed
            }

            # Create or update extraction stats record
            stats_record = ExtractionStatsModel(
                match_id=match_id,
                fixture_id=fixture_id,
                match_datetime=match.start_time or datetime.utcnow(),
                total_bets=total_bets,
                total_amount_collected=total_amount_collected,
                total_redistributed=total_redistributed,
                actual_result=selected_result,
                result_breakdown=json.dumps(result_breakdown),
                under_bets=under_bets,
                under_amount=under_amount,
                over_bets=over_bets,
                over_amount=over_amount,
                extraction_result=extraction_result,
                cap_applied=cap_applied,
                cap_percentage=cap_percentage if cap_applied else None
            )

            session.add(stats_record)
            session.commit()

            logger.info(f"Collected statistics for match {match_id}: {total_bets} bets, collected={total_amount_collected:.2f}, redistributed={total_redistributed:.2f}")

        except Exception as e:
            logger.error(f"Failed to collect match statistics: {e}")
            session.rollback()

    def _fallback_result_selection(self) -> str:
        """Fallback result selection when extraction fails"""
        try:
            session = self.db_manager.get_session()
            try:
                from ..database.models import ResultOptionModel

                # Get first active result option (excluding UNDER/OVER)
                result_option = session.query(ResultOptionModel).filter(
                    ResultOptionModel.is_active == True,
                    ~ResultOptionModel.result_name.in_(['UNDER', 'OVER'])
                ).first()

                if result_option:
                    return result_option.result_name
                else:
                    return "WIN1"  # Ultimate fallback

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Fallback result selection failed: {e}")
            return "WIN1"

    def _query_extracted_result(self, match_id: int) -> Optional[str]:
        """Query database for previously extracted result"""
        try:
            logger.info(f"DEBUG _query_extracted_result: Querying for match {match_id}")

            session = self.db_manager.get_session()
            try:
                # Query for the most recent extraction stats for this match
                from ..database.models import ExtractionStatsModel
                extraction_stats = session.query(ExtractionStatsModel).filter(
                    ExtractionStatsModel.match_id == match_id
                ).order_by(ExtractionStatsModel.created_at.desc()).first()

                logger.info(f"DEBUG _query_extracted_result: Query result - extraction_stats exists: {extraction_stats is not None}")

                if extraction_stats:
                    logger.info(f"DEBUG _query_extracted_result: extraction_stats.actual_result = '{extraction_stats.actual_result}'")
                    logger.info(f"DEBUG _query_extracted_result: extraction_stats.extraction_result = '{extraction_stats.extraction_result}'")
                    logger.info(f"DEBUG _query_extracted_result: extraction_stats.created_at = {extraction_stats.created_at}")

                if extraction_stats and extraction_stats.actual_result:
                    logger.info(f"Found extracted result for match {match_id}: {extraction_stats.actual_result}")
                    return extraction_stats.actual_result
                else:
                    logger.warning(f"No extraction stats found for match {match_id}")
                    return None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to query extracted result for match {match_id}: {e}")
            import traceback
            logger.error(f"DEBUG _query_extracted_result: Exception traceback: {traceback.format_exc()}")
            return None

    def _determine_under_over_result(self, match_id: int, main_result: str) -> Optional[str]:
        """Determine the under/over result for display purposes"""
        try:
            session = self.db_manager.get_session()
            try:
                # If the main result is already UNDER or OVER, return it
                if main_result in ['UNDER', 'OVER']:
                    return main_result

                # Check if there are winning UNDER or OVER bets for this match
                from ..database.models import BetDetailModel
                winning_under_over = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.result == 'win',
                    BetDetailModel.outcome.in_(['UNDER', 'OVER'])
                ).first()

                if winning_under_over:
                    logger.info(f"Found winning {winning_under_over.outcome} bet for match {match_id}")
                    return winning_under_over.outcome

                # If no winning UNDER/OVER bets, check if there were any UNDER/OVER bets at all
                # and determine based on some logic (e.g., random or based on main result characteristics)
                under_bets = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.outcome == 'UNDER'
                ).count()

                over_bets = session.query(BetDetailModel).filter(
                    BetDetailModel.match_id == match_id,
                    BetDetailModel.outcome == 'OVER'
                ).count()

                # Simple logic: if there were UNDER bets, show UNDER; if OVER bets, show OVER; otherwise random
                if under_bets > 0 and over_bets == 0:
                    return 'UNDER'
                elif over_bets > 0 and under_bets == 0:
                    return 'OVER'
                elif under_bets > 0 and over_bets > 0:
                    # Both types of bets exist, use simple random selection
                    import random
                    return 'UNDER' if random.random() < 0.5 else 'OVER'
                else:
                    # No UNDER/OVER bets, don't show under/over result
                    return None

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to determine under/over result for match {match_id}: {e}")
            return None

    def _send_play_video_results(self, fixture_id: str, match_id: int, result: str):
        """Send PLAY_VIDEO_RESULTS message (plural as per user request)"""
        try:
            logger.info(f"DEBUG _send_play_video_results: Sending PLAY_VIDEO_RESULTS with fixture_id={fixture_id}, match_id={match_id}, result='{result}'")

            # Determine under/over result separately from main result
            under_over_result = self._determine_under_over_result(match_id, result)
            logger.info(f"DEBUG _send_play_video_results: under_over_result = '{under_over_result}'")

            play_results_message = MessageBuilder.play_video_result(
                sender=self.name,
                fixture_id=fixture_id,
                match_id=match_id,
                result=result,
                under_over_result=under_over_result
            )

            # DEBUG: Log the message content
            logger.info(f"DEBUG _send_play_video_results: Message data: {play_results_message.data}")

            self.message_bus.publish(play_results_message)
            logger.info(f"Sent PLAY_VIDEO_RESULTS for fixture {fixture_id}, match {match_id}, result {result}, under_over {under_over_result}")

        except Exception as e:
            logger.error(f"Failed to send PLAY_VIDEO_RESULTS: {e}")
            import traceback
            logger.error(f"DEBUG _send_play_video_results: Exception traceback: {traceback.format_exc()}")

    def _send_play_video_result(self, fixture_id: str, match_id: int, result: str):
        """Send PLAY_VIDEO_RESULT message"""
        try:
            play_result_message = MessageBuilder.play_video_result(
                sender=self.name,
                fixture_id=fixture_id,
                match_id=match_id,
                result=result
            )
            self.message_bus.publish(play_result_message)
            logger.info(f"Sent PLAY_VIDEO_RESULT for fixture {fixture_id}, match {match_id}, result {result}")

        except Exception as e:
            logger.error(f"Failed to send PLAY_VIDEO_RESULT: {e}")

    def _send_match_done(self, fixture_id: str, match_id: int, result: str = None):
        """Send MATCH_DONE message"""
        try:
            logger.info(f"DEBUG _send_match_done: Sending MATCH_DONE with fixture_id={fixture_id}, match_id={match_id}, result='{result}'")

            match_done_message = MessageBuilder.match_done(
                sender=self.name,
                fixture_id=fixture_id,
                match_id=match_id,
                result=result
            )

            # DEBUG: Log the message content
            logger.info(f"DEBUG _send_match_done: Message data: {match_done_message.data}")

            self.message_bus.publish(match_done_message)
            logger.info(f"Sent MATCH_DONE for fixture {fixture_id}, match {match_id}, result {result}")

        except Exception as e:
            logger.error(f"Failed to send MATCH_DONE: {e}")
            import traceback
            logger.error(f"DEBUG _send_match_done: Exception traceback: {traceback.format_exc()}")

    def _send_next_match(self, fixture_id: str, match_id: int):
        """Send NEXT_MATCH message"""
        try:
            next_match_message = MessageBuilder.next_match(
                sender=self.name,
                fixture_id=fixture_id,
                match_id=match_id
            )
            self.message_bus.publish(next_match_message)
            logger.info(f"Sent NEXT_MATCH for fixture {fixture_id}, match {match_id}")

        except Exception as e:
            logger.error(f"Failed to send NEXT_MATCH: {e}")

    def _select_random_completed_matches(self, count: int, session) -> List[MatchModel]:
        """Select random completed matches from the database (including cancelled and failed)"""
        try:
            # Get all completed matches (status = 'done', 'cancelled', or 'failed')
            # Exclude matches from fixtures that contain "_recycle_" in the fixture name
            completed_matches = session.query(MatchModel).filter(
                MatchModel.status.in_(['done', 'end', 'cancelled', 'failed']),
                MatchModel.active_status == True,
                ~MatchModel.fixture_id.like('%_recycle_%')
            ).all()

            if len(completed_matches) < count:
                logger.warning(f"Only {len(completed_matches)} completed matches found, requested {count}")
                return completed_matches

            # Select random matches
            import random
            selected_matches = random.sample(completed_matches, count)
            logger.info(f"Selected {len(selected_matches)} random completed matches")
            return selected_matches

        except Exception as e:
            logger.error(f"Failed to select random completed matches: {e}")
            return []

    def _create_matches_from_old_matches(self, fixture_id: str, old_matches: List[MatchModel], session):
        """Create new matches in the fixture by copying from old completed matches"""
        try:
            now = datetime.utcnow()

            # Determine the status for new matches based on system state
            new_match_status = self._determine_new_match_status(fixture_id, session)

            # Find the maximum match_number in the fixture and increment from there
            max_match_number = session.query(MatchModel.match_number).filter(
                MatchModel.fixture_id == fixture_id
            ).order_by(MatchModel.match_number.desc()).first()

            match_number = (max_match_number[0] + 1) if max_match_number else 1

            for old_match in old_matches:
                # Create a new match based on the old one
                new_match = MatchModel(
                    match_number=match_number,
                    fighter1_township=old_match.fighter1_township,
                    fighter2_township=old_match.fighter2_township,
                    venue_kampala_township=old_match.venue_kampala_township,
                    start_time=now,
                    status=new_match_status,
                    fixture_id=fixture_id,
                    filename=old_match.filename,
                    file_sha1sum=old_match.file_sha1sum,
                    active_status=True,
                    zip_filename=old_match.zip_filename,
                    zip_sha1sum=old_match.zip_sha1sum,
                    zip_upload_status='completed',  # Assume ZIP is already available
                    zip_validation_status='valid',  # ZIP already validated from old match
                    fixture_active_time=int(now.timestamp()),
                    result=None,  # Reset result for new match
                    end_time=None,  # Reset end time for new match
                    done=False,  # Reset done flag for new match
                    running=False  # Reset running flag for new match
                )

                session.add(new_match)
                session.flush()  # Get the ID

                # Copy match outcomes
                for outcome in old_match.outcomes:
                    new_outcome = MatchOutcomeModel(
                        match_id=new_match.id,
                        column_name=outcome.column_name,
                        float_value=outcome.float_value
                    )
                    session.add(new_outcome)

                logger.debug(f"Created new match #{match_number} from old match #{old_match.match_number} with status {new_match_status}")
                match_number += 1

            session.commit()
            logger.info(f"Created {len(old_matches)} new matches in fixture {fixture_id} with status {new_match_status}")

        except Exception as e:
            logger.error(f"Failed to create matches from old matches: {e}")
            session.rollback()
            raise

    def _create_new_fixture_from_old_matches(self, old_matches: List[MatchModel], session) -> Optional[str]:
        """Create a new fixture with matches copied from old completed matches"""
        try:
            # Generate a unique fixture ID
            import uuid
            fixture_id = f"recycle_{uuid.uuid4().hex[:8]}"
            now = datetime.utcnow()

            # For a new fixture, start match_number from 1
            match_number = 1
            for old_match in old_matches:
                # Create a new match based on the old one
                new_match = MatchModel(
                    match_number=match_number,
                    fighter1_township=old_match.fighter1_township,
                    fighter2_township=old_match.fighter2_township,
                    venue_kampala_township=old_match.venue_kampala_township,
                    start_time=now,
                    status='scheduled',
                    fixture_id=fixture_id,
                    filename=old_match.filename,
                    file_sha1sum=old_match.file_sha1sum,
                    active_status=True,
                    zip_filename=old_match.zip_filename,
                    zip_sha1sum=old_match.zip_sha1sum,
                    zip_upload_status='completed',  # Assume ZIP is already available
                    zip_validation_status='valid',  # ZIP already validated from old match
                    fixture_active_time=int(now.timestamp()),
                    result=None,  # Reset result for new match
                    end_time=None,  # Reset end time for new match
                    done=False,  # Reset done flag for new match
                    running=False  # Reset running flag for new match
                )

                session.add(new_match)
                session.flush()  # Get the ID

                # Copy match outcomes
                for outcome in old_match.outcomes:
                    new_outcome = MatchOutcomeModel(
                        match_id=new_match.id,
                        column_name=outcome.column_name,
                        float_value=outcome.float_value
                    )
                    session.add(new_outcome)

                logger.debug(f"Created match #{match_number} in new fixture {fixture_id} from old match #{old_match.match_number}")
                match_number += 1

            session.commit()
            logger.info(f"Created new fixture {fixture_id} with {len(old_matches)} matches from old completed matches")
            return fixture_id

        except Exception as e:
            logger.error(f"Failed to create new fixture from old matches: {e}")
            session.rollback()
            return None

    def _determine_game_status(self) -> str:
        """Determine the current game status for status requests"""
        try:
            # If a game is currently active, return "started"
            if self.game_active and self.current_fixture_id:
                return "started"

            # Check if there are any active fixtures (matches in non-terminal states)
            session = self.db_manager.get_session()
            try:
                # Get today's date
                today = datetime.now().date()

                # Check for active matches today
                active_matches = session.query(MatchModel).filter(
                    MatchModel.start_time.isnot(None),
                    MatchModel.start_time >= datetime.combine(today, datetime.min.time()),
                    MatchModel.start_time < datetime.combine(today, datetime.max.time()),
                    MatchModel.status.notin_(['done', 'cancelled', 'failed', 'paused']),
                    MatchModel.active_status == True
                ).all()

                if active_matches:
                    # Active matches found - return "already_active" since validation happens asynchronously
                    logger.debug("Active matches found - game can be activated")
                    return "already_active"

                # Check if all today's fixtures are in terminal states
                if self._has_today_fixtures_all_terminal():
                    return "completed_no_old_matches"

                # Check if there are any fixtures at all (even if not today)
                any_fixtures = session.query(MatchModel).filter(
                    MatchModel.active_status == True
                ).count()

                if any_fixtures > 0:
                    return "ready"  # Fixtures exist but no active game

                # No fixtures at all
                return "shutdown"

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to determine game status: {e}")
            return "ready"  # Default fallback

    def _count_remaining_matches_in_fixture(self, fixture_id: str) -> int:
        """Count remaining matches in fixture that can still be played"""
        try:
            session = self.db_manager.get_session()
            try:
                # Count matches that are not in terminal states (done, cancelled, failed, paused)
                remaining_count = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.status.notin_(['done', 'cancelled', 'failed', 'paused']),
                    MatchModel.active_status == True
                ).count()

                logger.debug(f"Fixture {fixture_id} has {remaining_count} remaining matches")
                return remaining_count

            finally:
                session.close()

        except Exception as e:
            logger.error(f"Failed to count remaining matches in fixture {fixture_id}: {e}")
            return 0

    def _ensure_minimum_matches_in_fixture(self, fixture_id: str, minimum_required: int):
        """Ensure fixture has at least minimum_required matches by creating new ones from old completed matches"""
        try:
            logger.info(f"🔄 Ensuring fixture {fixture_id} has at least {minimum_required} matches")

            session = self.db_manager.get_session()
            try:
                # Get the last played match ID to exclude it from selection
                last_played_match_id = self._get_last_played_match_id(fixture_id, session)
                logger.info(f"🎯 Last played match ID: {last_played_match_id}")

                # Select random completed matches using progressive fallback (excludes last 3 matches)
                old_matches = self._select_random_completed_matches_with_fallback(
                    minimum_required, fixture_id, session
                )

                if old_matches:
                    logger.info(f"📋 Selected {len(old_matches)} old matches to create new ones")
                    self._create_matches_from_old_matches(fixture_id, old_matches, session)
                    logger.info(f"✅ Created {len(old_matches)} new matches in fixture {fixture_id}")
                else:
                    logger.warning(f"⚠️ No suitable old matches found to create new ones for fixture {fixture_id}")

            finally:
                session.close()

        except Exception as e:
            logger.error(f"❌ Failed to ensure minimum matches in fixture {fixture_id}: {e}")

    def _get_last_played_match_id(self, fixture_id: str, session) -> Optional[int]:
        """Get the ID of the last match that was played in this fixture"""
        try:
            # Find the most recently completed match in this fixture
            last_match = session.query(MatchModel).filter(
                MatchModel.fixture_id == fixture_id,
                MatchModel.status.in_(['done', 'end', 'cancelled', 'failed']),
                MatchModel.active_status == True
            ).order_by(MatchModel.updated_at.desc()).first()

            if last_match:
                logger.debug(f"Last played match in fixture {fixture_id}: #{last_match.match_number} (ID: {last_match.id})")
                return last_match.id
            else:
                logger.debug(f"No completed matches found in fixture {fixture_id}")
                return None

        except Exception as e:
            logger.error(f"Failed to get last played match ID for fixture {fixture_id}: {e}")
            return None

    def _select_random_completed_matches_excluding_last(self, count: int, exclude_match_id: Optional[int], session) -> List[MatchModel]:
        """Select random completed matches from the database, excluding matches with same fighters as the last played match"""
        try:
            # Build query for completed matches
            # Exclude matches from fixtures that contain "_recycle_" in the fixture name
            query = session.query(MatchModel).filter(
                MatchModel.status.in_(['done', 'end', 'cancelled', 'failed']),
                MatchModel.active_status == True,
                ~MatchModel.fixture_id.like('%_recycle_%')
            )

            # Exclude matches with same fighters as the last played match
            if exclude_match_id:
                last_match = session.query(MatchModel).filter(MatchModel.id == exclude_match_id).first()
                if last_match:
                    # Exclude matches with same fighter combinations (both directions)
                    query = query.filter(
                        ~((MatchModel.fighter1_township == last_match.fighter1_township) &
                          (MatchModel.fighter2_township == last_match.fighter2_township)) &
                        ~((MatchModel.fighter1_township == last_match.fighter2_township) &
                          (MatchModel.fighter2_township == last_match.fighter1_township))
                    )
                    logger.info(f"Excluding matches with fighters: {last_match.fighter1_township} vs {last_match.fighter2_township}")

            completed_matches = query.all()

            if len(completed_matches) < count:
                logger.warning(f"Only {len(completed_matches)} completed matches available (excluding same fighters), requested {count}")
                return completed_matches

            # Select random matches
            import random
            selected_matches = random.sample(completed_matches, count)
            logger.info(f"Selected {len(selected_matches)} random completed matches (excluding same fighters as last match)")
            return selected_matches

        except Exception as e:
            logger.error(f"Failed to select random completed matches excluding same fighters: {e}")
            return []

    def _select_random_completed_matches_with_fallback(self, count: int, fixture_id: Optional[str], session, max_attempts: int = 5) -> List[MatchModel]:
        """Select random matches with progressive fallback - try up to 5 times with relaxed criteria"""
        import random

        for attempt in range(max_attempts):
            try:
                if attempt == 0:
                    # Attempt 1: Exclude last 3 matches (fighters + venue)
                    exclusion_count = 3
                    fighters_only = False
                    logger.info(f"🎯 Attempt {attempt + 1}: Excluding last {exclusion_count} matches (fighters + venue)")
                elif attempt == 1:
                    # Attempt 2: Exclude last 2 matches (fighters + venue)
                    exclusion_count = 2
                    fighters_only = False
                    logger.info(f"🎯 Attempt {attempt + 1}: Excluding last {exclusion_count} matches (fighters + venue)")
                elif attempt == 2:
                    # Attempt 3: Exclude last 1 match (fighters + venue)
                    exclusion_count = 1
                    fighters_only = False
                    logger.info(f"🎯 Attempt {attempt + 1}: Excluding last {exclusion_count} match (fighters + venue)")
                elif attempt == 3:
                    # Attempt 4: Exclude last 1 match (fighters only, ignore venue)
                    exclusion_count = 1
                    fighters_only = True
                    logger.info(f"🎯 Attempt {attempt + 1}: Excluding last {exclusion_count} match (fighters only)")
                else:
                    # Attempt 5: No exclusions
                    exclusion_count = 0
                    fighters_only = False
                    logger.info(f"🎯 Attempt {attempt + 1}: No exclusions (final fallback)")

                # Get available matches with current exclusion criteria
                available_matches = self._get_available_matches_excluding_recent(
                    fixture_id, exclusion_count, fighters_only, session
                )

                if len(available_matches) >= count:
                    selected = random.sample(available_matches, count)
                    logger.info(f"✅ Success on attempt {attempt + 1}: selected {len(selected)} matches from {len(available_matches)} available")
                    return selected
                else:
                    logger.warning(f"⚠️ Attempt {attempt + 1} failed: only {len(available_matches)} matches available, need {count}")
                    continue

            except Exception as e:
                logger.error(f"❌ Attempt {attempt + 1} failed with error: {e}")
                continue

        # Final fallback: return whatever matches are available
        logger.warning(f"🚨 All {max_attempts} attempts failed - returning all available matches")
        all_matches = session.query(MatchModel).filter(
            MatchModel.status.in_(['done', 'end', 'cancelled', 'failed']),
            MatchModel.active_status == True,
            ~MatchModel.fixture_id.like('%_recycle_%')
        ).all()

        result = all_matches[:count] if len(all_matches) >= count else all_matches
        logger.info(f"🔄 Final fallback: returning {len(result)} matches from {len(all_matches)} total available")
        return result

    def _get_available_matches_excluding_recent(self, fixture_id: Optional[str], exclude_last_n: int, fighters_only: bool, session) -> List[MatchModel]:
        """Get available matches excluding the last N recent matches in the fixture"""
        try:
            # If no fixture_id provided (creating new fixture), don't exclude any recent matches
            if fixture_id is None:
                recent_matches = []
            else:
                # Get the last N matches in the fixture (by match_number, regardless of completion status)
                recent_matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.active_status == True
                ).order_by(MatchModel.match_number.desc()).limit(exclude_last_n).all()

            logger.debug(f"Found {len(recent_matches)} recent matches to exclude: {[f'#{m.match_number}: {m.fighter1_township} vs {m.fighter2_township}' for m in recent_matches]}")

            # Build exclusion filters
            exclusion_filters = []
            for recent_match in recent_matches:
                if fighters_only:
                    # Exclude matches with same fighters only (both directions)
                    exclusion_filters.append(
                        ~((MatchModel.fighter1_township == recent_match.fighter1_township) &
                          (MatchModel.fighter2_township == recent_match.fighter2_township)) &
                        ~((MatchModel.fighter1_township == recent_match.fighter2_township) &
                          (MatchModel.fighter2_township == recent_match.fighter1_township))
                    )
                else:
                    # Exclude matches with same fighters AND venue
                    exclusion_filters.append(
                        ~((MatchModel.fighter1_township == recent_match.fighter1_township) &
                          (MatchModel.fighter2_township == recent_match.fighter2_township) &
                          (MatchModel.venue_kampala_township == recent_match.venue_kampala_township))
                    )

            # Query available matches with exclusions
            query = session.query(MatchModel).filter(
                MatchModel.status.in_(['done', 'end', 'cancelled', 'failed']),
                MatchModel.active_status == True,
                ~MatchModel.fixture_id.like('%_recycle_%'),
                *exclusion_filters
            )

            available_matches = query.all()
            logger.debug(f"Found {len(available_matches)} matches available after exclusions")
            return available_matches

        except Exception as e:
            logger.error(f"Failed to get available matches excluding recent: {e}")
            return []

    def _determine_new_match_status(self, fixture_id: str, session) -> str:
        """Determine the status for new matches based on system state"""
        try:
            # Check if system is ingame (has any match with status 'ingame')
            ingame_match = session.query(MatchModel).filter(
                MatchModel.status == 'ingame',
                MatchModel.active_status == True
            ).first()

            if ingame_match:
                # System is ingame, check if last 4 matches in the fixture are in 'bet' status
                last_4_matches = session.query(MatchModel).filter(
                    MatchModel.fixture_id == fixture_id,
                    MatchModel.active_status == True
                ).order_by(MatchModel.match_number.desc()).limit(4).all()

                if len(last_4_matches) >= 4:
                    # Check if all last 4 matches are in 'bet' status
                    if all(match.status == 'bet' for match in last_4_matches):
                        logger.info(f"System is ingame and last 4 matches in fixture {fixture_id} are in bet status - new matches will be in bet status")
                        return 'bet'

            # Default status
            logger.info(f"New matches will be in scheduled status (system not ingame or last 4 matches not all in bet status)")
            return 'scheduled'

        except Exception as e:
            logger.error(f"Failed to determine new match status: {e}")
            return 'scheduled'  # Default fallback


    def _cleanup_previous_match_extractions(self):
        """Clean up all previous unzipped match directories from temporary location"""
        try:
            import tempfile
            from pathlib import Path
            import shutil

            temp_base = Path(tempfile.gettempdir())
            logger.info(f"DEBUG: Cleaning up previous match extractions in: {temp_base}")

            # Find all directories matching the pattern match_*_*
            match_dirs = list(temp_base.glob("match_*_*"))
            logger.info(f"DEBUG: Found {len(match_dirs)} match extraction directories to clean up")

            cleaned_count = 0
            for match_dir in match_dirs:
                try:
                    if match_dir.is_dir():
                        logger.debug(f"DEBUG: Removing match extraction directory: {match_dir}")
                        shutil.rmtree(match_dir)
                        cleaned_count += 1
                except Exception as dir_error:
                    logger.warning(f"DEBUG: Failed to remove directory {match_dir}: {dir_error}")

            if cleaned_count > 0:
                logger.info(f"DEBUG: Successfully cleaned up {cleaned_count} previous match extraction directories")
            else:
                logger.debug("DEBUG: No previous match extraction directories found to clean up")

        except Exception as e:
            logger.error(f"DEBUG: Failed to cleanup previous match extractions: {e}")

    def _cleanup(self):
        """Perform cleanup operations"""
        try:
            logger.info("GamesThread performing cleanup...")

            # Reset state
            self.game_active = False
            self.current_fixture_id = None

            # Send final status
            final_status = MessageBuilder.system_status(
                sender=self.name,
                status="shutdown",
                details={
                    "component": "games_thread",
                    "cleanup_completed": True
                }
            )
            self.message_bus.publish(final_status)

            logger.info("GamesThread cleanup completed")

        except Exception as e:
            logger.error(f"GamesThread cleanup error: {e}")