"""
Unit tests for BoxScoreService with mocked database queries.

Tests the BoxScoreService class that queries materialized views for box scores,
using mocked SQLAlchemy session and results to avoid database dependencies.
"""
import pytest
from unittest.mock import AsyncMock, patch, MagicMock
from uuid import uuid4, UUID
from app.services.box_score_service import BoxScoreService


@pytest.fixture
def box_score_service():
    """Create BoxScoreService instance for testing"""
    return BoxScoreService()


@pytest.fixture
def mock_game_stats():
    """Sample game stats data from materialized view"""
    game_id = uuid4()
    return {
        'game_id': str(game_id),
        'home_runs': 5,
        'away_runs': 3,
        'home_hits': 8,
        'away_hits': 6,
        'home_errors': 1,
        'away_errors': 2,
        'home_linescore': [0, 1, 0, 3, 0, 0, 1, 0, 0],
        'away_linescore': [1, 0, 0, 2, 0, 0, 0, 0, 0]
    }


@pytest.fixture
def mock_batting_stats():
    """Sample batting stats for multiple players"""
    return [
        {
            'lineup_id': 1,
            'game_id': str(uuid4()),
            'player_card_id': 101,
            'pa': 4,
            'ab': 3,
            'run': 2,
            'hit': 2,
            'rbi': 1,
            'double': 1,
            'triple': 0,
            'hr': 0,
            'bb': 1,
            'so': 0,
            'hbp': 0,
            'sac': 0,
            'sb': 1,
            'cs': 0,
            'gidp': 0
        },
        {
            'lineup_id': 2,
            'game_id': str(uuid4()),
            'player_card_id': 102,
            'pa': 4,
            'ab': 4,
            'run': 1,
            'hit': 1,
            'rbi': 2,
            'double': 0,
            'triple': 0,
            'hr': 1,
            'bb': 0,
            'so': 2,
            'hbp': 0,
            'sac': 0,
            'sb': 0,
            'cs': 0,
            'gidp': 0
        }
    ]


@pytest.fixture
def mock_pitching_stats():
    """Sample pitching stats for multiple pitchers"""
    return [
        {
            'lineup_id': 10,
            'game_id': str(uuid4()),
            'player_card_id': 201,
            'batters_faced': 25,
            'hit_allowed': 6,
            'run_allowed': 3,
            'erun': 3,
            'bb': 2,
            'so': 5,
            'hbp': 0,
            'hr_allowed': 1,
            'wp': 0,
            'ip': 6.0
        },
        {
            'lineup_id': 11,
            'game_id': str(uuid4()),
            'player_card_id': 202,
            'batters_faced': 12,
            'hit_allowed': 2,
            'run_allowed': 0,
            'erun': 0,
            'bb': 1,
            'so': 3,
            'hbp': 0,
            'hr_allowed': 0,
            'wp': 0,
            'ip': 3.0
        }
    ]


def setup_mock_session(
    mock_session_class,
    game_stats_data=None,
    batting_stats_data=None,
    pitching_stats_data=None,
    raise_error=None
):
    """
    Helper to setup AsyncSessionLocal mock with proper async context manager.

    Args:
        mock_session_class: The mocked AsyncSessionLocal class
        game_stats_data: Dict for game_stats query result (or None for not found)
        batting_stats_data: List of dicts for batting_stats (or empty list)
        pitching_stats_data: List of dicts for pitching_stats (or empty list)
        raise_error: Exception to raise from execute(), or None

    Returns:
        mock_session: The mock session instance for additional assertions
    """
    mock_session = MagicMock()

    if raise_error:
        # Setup session that raises exception
        mock_session.execute = AsyncMock(side_effect=raise_error)
    else:
        # Track which query is being executed (based on call count)
        call_count = [0]

        async def mock_execute(query, params=None):
            """Mock execute that returns different results based on call order"""
            call_count[0] += 1

            mock_result = MagicMock()

            # First call is game_stats (fetchone)
            if call_count[0] == 1:
                if game_stats_data is None:
                    mock_result.fetchone.return_value = None
                else:
                    # Convert dict to row tuple
                    row = MagicMock()
                    row.__getitem__ = lambda self, idx: list(game_stats_data.values())[idx]
                    mock_result.fetchone.return_value = row

            # Second call is batting_stats (fetchall)
            elif call_count[0] == 2:
                rows = []
                for stat in (batting_stats_data or []):
                    row = MagicMock()
                    row.__getitem__ = lambda self, idx, s=stat: list(s.values())[idx]
                    rows.append(row)
                mock_result.fetchall.return_value = rows

            # Third call is pitching_stats (fetchall)
            elif call_count[0] == 3:
                rows = []
                for stat in (pitching_stats_data or []):
                    row = MagicMock()
                    row.__getitem__ = lambda self, idx, s=stat: list(s.values())[idx]
                    rows.append(row)
                mock_result.fetchall.return_value = rows

            return mock_result

        mock_session.execute = mock_execute

    # Setup async context manager protocol
    mock_session_instance = MagicMock()
    mock_session_instance.__aenter__ = AsyncMock(return_value=mock_session)
    mock_session_instance.__aexit__ = AsyncMock(return_value=None)

    mock_session_class.return_value = mock_session_instance

    return mock_session


class TestBoxScoreServiceSuccess:
    """Tests for successful box score retrieval"""

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_complete_data(
        self,
        mock_session_class,
        box_score_service,
        mock_game_stats,
        mock_batting_stats,
        mock_pitching_stats
    ):
        """Test get_box_score returns complete data for a game"""
        # Setup mock session
        setup_mock_session(
            mock_session_class,
            game_stats_data=mock_game_stats,
            batting_stats_data=mock_batting_stats,
            pitching_stats_data=mock_pitching_stats
        )

        # Execute
        game_id = UUID(mock_game_stats['game_id'])
        result = await box_score_service.get_box_score(game_id)

        # Verify structure
        assert result is not None
        assert 'game_stats' in result
        assert 'batting_stats' in result
        assert 'pitching_stats' in result

        # Verify game stats
        assert result['game_stats']['home_runs'] == 5
        assert result['game_stats']['away_runs'] == 3
        assert result['game_stats']['home_hits'] == 8
        assert len(result['game_stats']['home_linescore']) == 9

        # Verify batting stats
        assert len(result['batting_stats']) == 2
        assert result['batting_stats'][0]['player_card_id'] == 101
        assert result['batting_stats'][1]['player_card_id'] == 102

        # Verify pitching stats
        assert len(result['pitching_stats']) == 2
        assert result['pitching_stats'][0]['ip'] == 6.0
        assert result['pitching_stats'][1]['ip'] == 3.0

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_empty_batting_stats(
        self,
        mock_session_class,
        box_score_service,
        mock_game_stats
    ):
        """Test get_box_score with no batting stats (early game)"""
        # Setup mock with empty batting stats
        setup_mock_session(
            mock_session_class,
            game_stats_data=mock_game_stats,
            batting_stats_data=[],
            pitching_stats_data=[]
        )

        # Execute
        game_id = UUID(mock_game_stats['game_id'])
        result = await box_score_service.get_box_score(game_id)

        # Verify
        assert result is not None
        assert result['batting_stats'] == []
        assert result['pitching_stats'] == []
        assert result['game_stats']['home_runs'] == 5

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_null_values_handled(
        self,
        mock_session_class,
        box_score_service
    ):
        """Test get_box_score handles NULL values correctly"""
        # Game stats with NULL values
        game_stats_with_nulls = {
            'game_id': str(uuid4()),
            'home_runs': None,  # NULL in DB
            'away_runs': 0,
            'home_hits': None,
            'away_hits': 0,
            'home_errors': None,
            'away_errors': 0,
            'home_linescore': None,
            'away_linescore': None
        }

        # Setup mock session
        setup_mock_session(
            mock_session_class,
            game_stats_data=game_stats_with_nulls,
            batting_stats_data=[],
            pitching_stats_data=[]
        )

        # Execute
        game_id = UUID(game_stats_with_nulls['game_id'])
        result = await box_score_service.get_box_score(game_id)

        # Verify NULL handling (should convert to 0 or [])
        assert result is not None
        assert result['game_stats']['home_runs'] == 0
        assert result['game_stats']['home_hits'] == 0
        assert result['game_stats']['home_errors'] == 0
        assert result['game_stats']['home_linescore'] == []
        assert result['game_stats']['away_linescore'] == []


class TestBoxScoreServiceNotFound:
    """Tests for game not found scenarios"""

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_game_not_found(
        self,
        mock_session_class,
        box_score_service
    ):
        """Test get_box_score returns None when game not found"""
        # Setup mock with no game stats (None from fetchone)
        setup_mock_session(
            mock_session_class,
            game_stats_data=None
        )

        # Execute
        game_id = uuid4()
        result = await box_score_service.get_box_score(game_id)

        # Verify
        assert result is None

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_invalid_game_id(
        self,
        mock_session_class,
        box_score_service
    ):
        """Test get_box_score with non-existent game ID"""
        # Setup mock with no results
        setup_mock_session(
            mock_session_class,
            game_stats_data=None
        )

        # Execute
        fake_game_id = uuid4()
        result = await box_score_service.get_box_score(fake_game_id)

        # Verify
        assert result is None


class TestBoxScoreServiceErrorHandling:
    """Tests for database error handling"""

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_database_error(
        self,
        mock_session_class,
        box_score_service
    ):
        """Test get_box_score handles database errors correctly"""
        # Setup mock to raise database exception
        setup_mock_session(
            mock_session_class,
            raise_error=Exception("Database connection failed")
        )

        # Execute and verify exception is raised
        with pytest.raises(Exception) as exc_info:
            await box_score_service.get_box_score(uuid4())

        assert "Database connection failed" in str(exc_info.value)

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_get_box_score_query_timeout(
        self,
        mock_session_class,
        box_score_service
    ):
        """Test get_box_score handles query timeout"""
        # Setup mock to raise timeout exception
        setup_mock_session(
            mock_session_class,
            raise_error=TimeoutError("Query timeout")
        )

        # Execute and verify exception is raised
        with pytest.raises(TimeoutError):
            await box_score_service.get_box_score(uuid4())


class TestBoxScoreServicePrivateMethods:
    """Tests for private helper methods"""

    @pytest.mark.asyncio
    async def test_get_game_stats_query_format(self, box_score_service):
        """Test _get_game_stats constructs correct query"""
        # Create a mock session
        mock_session = MagicMock()

        # Mock result
        mock_result = MagicMock()
        mock_result.fetchone.return_value = None

        mock_session.execute = AsyncMock(return_value=mock_result)

        # Execute
        game_id = uuid4()
        await box_score_service._get_game_stats(mock_session, game_id)

        # Verify execute was called
        mock_session.execute.assert_called_once()

        # Verify query contains expected table name
        call_args = mock_session.execute.call_args
        query_text = str(call_args[0][0])
        assert 'game_stats' in query_text.lower()
        assert 'game_id' in query_text.lower()

    @pytest.mark.asyncio
    async def test_get_batting_stats_query_format(self, box_score_service):
        """Test _get_batting_stats constructs correct query"""
        # Create a mock session
        mock_session = MagicMock()

        # Mock result
        mock_result = MagicMock()
        mock_result.fetchall.return_value = []

        mock_session.execute = AsyncMock(return_value=mock_result)

        # Execute
        game_id = uuid4()
        await box_score_service._get_batting_stats(mock_session, game_id)

        # Verify execute was called
        mock_session.execute.assert_called_once()

        # Verify query contains expected table and fields
        call_args = mock_session.execute.call_args
        query_text = str(call_args[0][0])
        assert 'batting_game_stats' in query_text.lower()
        assert 'lineup_id' in query_text.lower()
        assert 'player_card_id' in query_text.lower()

    @pytest.mark.asyncio
    async def test_get_pitching_stats_query_format(self, box_score_service):
        """Test _get_pitching_stats constructs correct query"""
        # Create a mock session
        mock_session = MagicMock()

        # Mock result
        mock_result = MagicMock()
        mock_result.fetchall.return_value = []

        mock_session.execute = AsyncMock(return_value=mock_result)

        # Execute
        game_id = uuid4()
        await box_score_service._get_pitching_stats(mock_session, game_id)

        # Verify execute was called
        mock_session.execute.assert_called_once()

        # Verify query contains expected table and fields
        call_args = mock_session.execute.call_args
        query_text = str(call_args[0][0])
        assert 'pitching_game_stats' in query_text.lower()
        assert 'batters_faced' in query_text.lower()
        assert 'ip' in query_text.lower()


class TestBoxScoreServiceDataFormatting:
    """Tests for data formatting and conversion"""

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_ip_float_conversion(
        self,
        mock_session_class,
        box_score_service,
        mock_game_stats
    ):
        """Test innings pitched is converted to float correctly"""
        # Pitching stats with various IP values
        pitching_with_fractional_ip = [
            {
                'lineup_id': 10,
                'game_id': str(uuid4()),
                'player_card_id': 201,
                'batters_faced': 20,
                'hit_allowed': 4,
                'run_allowed': 2,
                'erun': 2,
                'bb': 1,
                'so': 4,
                'hbp': 0,
                'hr_allowed': 0,
                'wp': 0,
                'ip': 5.333333333  # 5.1 innings (16 outs / 3)
            }
        ]

        # Setup mock
        setup_mock_session(
            mock_session_class,
            game_stats_data=mock_game_stats,
            batting_stats_data=[],
            pitching_stats_data=pitching_with_fractional_ip
        )

        # Execute
        game_id = UUID(mock_game_stats['game_id'])
        result = await box_score_service.get_box_score(game_id)

        # Verify IP is float
        assert isinstance(result['pitching_stats'][0]['ip'], float)
        assert result['pitching_stats'][0]['ip'] == pytest.approx(5.333333333, rel=1e-6)

    @pytest.mark.asyncio
    @patch('app.services.box_score_service.AsyncSessionLocal')
    async def test_all_stat_fields_present(
        self,
        mock_session_class,
        box_score_service,
        mock_game_stats,
        mock_batting_stats,
        mock_pitching_stats
    ):
        """Test all expected fields are present in result"""
        # Setup mock
        setup_mock_session(
            mock_session_class,
            game_stats_data=mock_game_stats,
            batting_stats_data=mock_batting_stats,
            pitching_stats_data=mock_pitching_stats
        )

        # Execute
        game_id = UUID(mock_game_stats['game_id'])
        result = await box_score_service.get_box_score(game_id)

        # Verify game stats fields
        game_stats_fields = [
            'game_id', 'home_runs', 'away_runs', 'home_hits', 'away_hits',
            'home_errors', 'away_errors', 'home_linescore', 'away_linescore'
        ]
        for field in game_stats_fields:
            assert field in result['game_stats'], f"Missing field: {field}"

        # Verify batting stats fields
        batting_fields = [
            'lineup_id', 'game_id', 'player_card_id', 'pa', 'ab', 'run', 'hit',
            'rbi', 'double', 'triple', 'hr', 'bb', 'so', 'hbp', 'sac', 'sb', 'cs', 'gidp'
        ]
        for field in batting_fields:
            assert field in result['batting_stats'][0], f"Missing batting field: {field}"

        # Verify pitching stats fields
        pitching_fields = [
            'lineup_id', 'game_id', 'player_card_id', 'batters_faced', 'hit_allowed',
            'run_allowed', 'erun', 'bb', 'so', 'hbp', 'hr_allowed', 'wp', 'ip'
        ]
        for field in pitching_fields:
            assert field in result['pitching_stats'][0], f"Missing pitching field: {field}"
