"""
Integration tests for materialized stat views.

Tests complete workflow: plays → views → box score retrieval.

IMPORTANT: These tests require materialized views to exist in database.
Run migration first: `uv run alembic upgrade head`

Run individually due to known asyncpg connection issues:
    uv run pytest tests/integration/test_stat_views.py::TestStatViewsIntegration::test_batting_stats_aggregation -v
"""
import pytest
from uuid import uuid4
from sqlalchemy import text
from app.services import box_score_service, stat_view_refresher
from app.database.operations import DatabaseOperations
from app.database.session import AsyncSessionLocal
from app.config.result_charts import PlayOutcome


@pytest.fixture
async def db_ops():
    """Create DatabaseOperations instance"""
    return DatabaseOperations()


@pytest.mark.integration
class TestStatViewsIntegration:
    """Integration tests for materialized stat views"""

    async def test_batting_stats_aggregation(self, db_ops):
        """Test batting stats aggregate correctly from plays"""
        # Create test game
        game_id = uuid4()
        await db_ops.create_game(
            game_id=game_id,
            league_id="sba",
            home_team_id=1,
            away_team_id=2,
            game_mode="friendly",
            visibility="public"
        )

        # Create lineup entries
        batter_lineup_id = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=101,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=True
        )

        pitcher_lineup_id = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=201,
            team_id=2,
            position="P",
            batting_order=1,
            is_starter=True
        )

        # Create plays with various outcomes
        play_data_list = [
            # Single (pa=1, ab=1, hit=1)
            {
                'game_id': game_id,
                'play_num': 1,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'SINGLE_1',
                'pa': 1,
                'ab': 1,
                'hit': 1,
                'run': 0,
                'rbi': 0,
                'double': 0,
                'triple': 0,
                'hr': 0,
                'bb': 0,
                'so': 0,
                'hbp': 0,
                'sac': 0,
                'sb': 0,
                'cs': 0,
                'gidp': 0,
                'outs_recorded': 0
            },
            # Strikeout (pa=1, ab=1, so=1)
            {
                'game_id': game_id,
                'play_num': 2,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'STRIKEOUT',
                'pa': 1,
                'ab': 1,
                'hit': 0,
                'run': 0,
                'rbi': 0,
                'double': 0,
                'triple': 0,
                'hr': 0,
                'bb': 0,
                'so': 1,
                'hbp': 0,
                'sac': 0,
                'sb': 0,
                'cs': 0,
                'gidp': 0,
                'outs_recorded': 1
            },
            # Walk (pa=1, ab=0, bb=1)
            {
                'game_id': game_id,
                'play_num': 3,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'WALK',
                'pa': 1,
                'ab': 0,
                'hit': 0,
                'run': 0,
                'rbi': 0,
                'double': 0,
                'triple': 0,
                'hr': 0,
                'bb': 1,
                'so': 0,
                'hbp': 0,
                'sac': 0,
                'sb': 0,
                'cs': 0,
                'gidp': 0,
                'outs_recorded': 0
            },
            # Home run (pa=1, ab=1, hit=1, hr=1, run=1, rbi=1)
            {
                'game_id': game_id,
                'play_num': 4,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'HOMERUN',
                'pa': 1,
                'ab': 1,
                'hit': 1,
                'run': 1,
                'rbi': 1,
                'double': 0,
                'triple': 0,
                'hr': 1,
                'bb': 0,
                'so': 0,
                'hbp': 0,
                'sac': 0,
                'sb': 0,
                'cs': 0,
                'gidp': 0,
                'outs_recorded': 0
            }
        ]

        # Save all plays
        for play_data in play_data_list:
            await db_ops.save_play(play_data)

        # Refresh materialized views
        await stat_view_refresher.refresh_all()

        # Query batting_game_stats view directly
        async with AsyncSessionLocal() as session:
            query = text("""
                SELECT pa, ab, hit, hr, bb, so, run, rbi
                FROM batting_game_stats
                WHERE game_id = :game_id AND lineup_id = :lineup_id
            """)
            result = await session.execute(query, {
                'game_id': str(game_id),
                'lineup_id': batter_lineup_id
            })
            row = result.fetchone()

        # Verify aggregated stats
        assert row is not None, "No batting stats found in view"
        assert row[0] == 4, f"Expected 4 PA, got {row[0]}"  # pa
        assert row[1] == 3, f"Expected 3 AB, got {row[1]}"  # ab (walk doesn't count)
        assert row[2] == 2, f"Expected 2 hits, got {row[2]}"  # hit
        assert row[3] == 1, f"Expected 1 HR, got {row[3]}"  # hr
        assert row[4] == 1, f"Expected 1 BB, got {row[4]}"  # bb
        assert row[5] == 1, f"Expected 1 SO, got {row[5]}"  # so
        assert row[6] == 1, f"Expected 1 run, got {row[6]}"  # run
        assert row[7] == 1, f"Expected 1 RBI, got {row[7]}"  # rbi

    async def test_pitching_stats_aggregation(self, db_ops):
        """Test pitching stats aggregate correctly"""
        # Create test game
        game_id = uuid4()
        await db_ops.create_game(
            game_id=game_id,
            league_id="sba",
            home_team_id=1,
            away_team_id=2,
            game_mode="friendly",
            visibility="public"
        )

        # Create lineup entries
        batter_lineup_id = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=101,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=True
        )

        pitcher_lineup_id = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=201,
            team_id=2,
            position="P",
            batting_order=1,
            is_starter=True
        )

        # Create plays that affect pitcher stats
        play_data_list = [
            # Hit allowed (batters_faced=1, hit_allowed=1)
            {
                'game_id': game_id,
                'play_num': 1,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'SINGLE_1',
                'pa': 1,
                'ab': 1,
                'hit': 1,
                'run': 0,
                'rbi': 0,
                'bb': 0,
                'so': 0,
                'hr': 0,
                'outs_recorded': 0
            },
            # Strikeout (batters_faced=1, so=1, outs_recorded=1)
            {
                'game_id': game_id,
                'play_num': 2,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'STRIKEOUT',
                'pa': 1,
                'ab': 1,
                'hit': 0,
                'run': 0,
                'rbi': 0,
                'bb': 0,
                'so': 1,
                'hr': 0,
                'outs_recorded': 1
            },
            # Walk allowed (batters_faced=1, bb=1)
            {
                'game_id': game_id,
                'play_num': 3,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'WALK',
                'pa': 1,
                'ab': 0,
                'hit': 0,
                'run': 0,
                'rbi': 0,
                'bb': 1,
                'so': 0,
                'hr': 0,
                'outs_recorded': 0
            },
            # Home run allowed (batters_faced=1, hit_allowed=1, hr_allowed=1, run_allowed=1)
            {
                'game_id': game_id,
                'play_num': 4,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter_lineup_id,
                'pitcher_lineup_id': pitcher_lineup_id,
                'play_result': 'HOMERUN',
                'pa': 1,
                'ab': 1,
                'hit': 1,
                'run': 1,
                'rbi': 1,
                'bb': 0,
                'so': 0,
                'hr': 1,
                'outs_recorded': 0
            }
        ]

        # Save all plays
        for play_data in play_data_list:
            await db_ops.save_play(play_data)

        # Refresh views
        await stat_view_refresher.refresh_all()

        # Query pitching_game_stats view directly
        async with AsyncSessionLocal() as session:
            query = text("""
                SELECT batters_faced, hit_allowed, run_allowed, bb, so, hr_allowed, ip
                FROM pitching_game_stats
                WHERE game_id = :game_id AND lineup_id = :lineup_id
            """)
            result = await session.execute(query, {
                'game_id': str(game_id),
                'lineup_id': pitcher_lineup_id
            })
            row = result.fetchone()

        # Verify aggregated pitching stats
        assert row is not None, "No pitching stats found in view"
        assert row[0] == 4, f"Expected 4 batters faced, got {row[0]}"
        assert row[1] == 2, f"Expected 2 hits allowed, got {row[1]}"
        assert row[2] == 1, f"Expected 1 run allowed, got {row[2]}"
        assert row[3] == 1, f"Expected 1 BB, got {row[3]}"
        assert row[4] == 1, f"Expected 1 SO, got {row[4]}"
        assert row[5] == 1, f"Expected 1 HR allowed, got {row[5]}"
        assert float(row[6]) == pytest.approx(0.333333, rel=1e-5), f"Expected 0.33 IP (1 out), got {row[6]}"

    async def test_game_stats_totals_and_linescore(self, db_ops):
        """Test game totals and linescore aggregation"""
        # Create test game
        game_id = uuid4()
        await db_ops.create_game(
            game_id=game_id,
            league_id="sba",
            home_team_id=1,
            away_team_id=2,
            game_mode="friendly",
            visibility="public"
        )

        # Create lineup entries
        home_batter = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=101,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=True
        )

        away_batter = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=102,
            team_id=2,
            position="CF",
            batting_order=1,
            is_starter=True
        )

        pitcher_id = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=201,
            team_id=2,
            position="P",
            batting_order=1,
            is_starter=True
        )

        # Create plays in different innings with runs
        plays = [
            # Top 1st - away scores 2 runs
            {'inning': 1, 'half': 'top', 'batter': away_batter, 'run': 1, 'hit': 1},
            {'inning': 1, 'half': 'top', 'batter': away_batter, 'run': 1, 'hit': 1},
            # Bot 1st - home scores 1 run
            {'inning': 1, 'half': 'bot', 'batter': home_batter, 'run': 1, 'hit': 1},
            # Top 2nd - away scores 1 run
            {'inning': 2, 'half': 'top', 'batter': away_batter, 'run': 1, 'hit': 1},
            # Bot 2nd - home scores 3 runs
            {'inning': 2, 'half': 'bot', 'batter': home_batter, 'run': 1, 'hit': 1},
            {'inning': 2, 'half': 'bot', 'batter': home_batter, 'run': 1, 'hit': 1},
            {'inning': 2, 'half': 'bot', 'batter': home_batter, 'run': 1, 'hit': 1}
        ]

        play_num = 1
        for play in plays:
            await db_ops.save_play({
                'game_id': game_id,
                'play_num': play_num,
                'inning': play['inning'],
                'half': play['half'],
                'batter_lineup_id': play['batter'],
                'pitcher_lineup_id': pitcher_id,
                'play_result': 'SINGLE_1',
                'pa': 1,
                'ab': 1,
                'hit': play['hit'],
                'run': play['run'],
                'rbi': play['run'],
                'outs_recorded': 0
            })
            play_num += 1

        # Update game scores
        await db_ops.update_game_state(
            game_id=game_id,
            home_score=4,
            away_score=3
        )

        # Refresh views
        await stat_view_refresher.refresh_all()

        # Query game_stats view
        async with AsyncSessionLocal() as session:
            query = text("""
                SELECT home_runs, away_runs, home_linescore, away_linescore
                FROM game_stats
                WHERE game_id = :game_id
            """)
            result = await session.execute(query, {'game_id': str(game_id)})
            row = result.fetchone()

        # Verify team totals
        assert row is not None, "No game stats found in view"
        assert row[0] == 4, f"Expected 4 home runs, got {row[0]}"
        assert row[1] == 3, f"Expected 3 away runs, got {row[1]}"

        # Verify linescore (JSON arrays)
        # Note: Linescore aggregation depends on actual implementation
        # This is a basic check that the arrays exist
        assert row[2] is not None, "Home linescore should not be None"
        assert row[3] is not None, "Away linescore should not be None"

    async def test_view_refresh_concurrency(self, db_ops):
        """Test REFRESH CONCURRENTLY allows reads during refresh"""
        # Create a simple game with data
        game_id = uuid4()
        await db_ops.create_game(
            game_id=game_id,
            league_id="sba",
            home_team_id=1,
            away_team_id=2,
            game_mode="friendly",
            visibility="public"
        )

        batter = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=101,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=True
        )

        pitcher = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=201,
            team_id=2,
            position="P",
            batting_order=1,
            is_starter=True
        )

        await db_ops.save_play({
            'game_id': game_id,
            'play_num': 1,
            'inning': 1,
            'half': 'top',
            'batter_lineup_id': batter,
            'pitcher_lineup_id': pitcher,
            'play_result': 'SINGLE_1',
            'pa': 1,
            'ab': 1,
            'hit': 1,
            'outs_recorded': 0
        })

        # Refresh views (this should not block)
        await stat_view_refresher.refresh_all()

        # Query view immediately after refresh (should work)
        async with AsyncSessionLocal() as session:
            query = text("""
                SELECT COUNT(*) FROM batting_game_stats WHERE game_id = :game_id
            """)
            result = await session.execute(query, {'game_id': str(game_id)})
            count = result.scalar()

        # Verify we could read during/after refresh
        assert count >= 0, "Should be able to read from view"

    async def test_box_score_retrieval_workflow(self, db_ops):
        """Test complete box score retrieval workflow"""
        # Create test game with plays
        game_id = uuid4()
        await db_ops.create_game(
            game_id=game_id,
            league_id="sba",
            home_team_id=1,
            away_team_id=2,
            game_mode="friendly",
            visibility="public"
        )

        batter = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=101,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=True
        )

        pitcher = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=201,
            team_id=2,
            position="P",
            batting_order=1,
            is_starter=True
        )

        # Add several plays
        for i in range(1, 6):
            await db_ops.save_play({
                'game_id': game_id,
                'play_num': i,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': batter,
                'pitcher_lineup_id': pitcher,
                'play_result': 'SINGLE_1',
                'pa': 1,
                'ab': 1,
                'hit': 1,
                'outs_recorded': 0
            })

        # Refresh views
        await stat_view_refresher.refresh_all()

        # Get box score using service
        box_score = await box_score_service.get_box_score(game_id)

        # Verify box score structure
        assert box_score is not None, "Box score should not be None"
        assert 'game_stats' in box_score
        assert 'batting_stats' in box_score
        assert 'pitching_stats' in box_score

        # Verify we got data
        assert len(box_score['batting_stats']) > 0, "Should have batting stats"
        assert len(box_score['pitching_stats']) > 0, "Should have pitching stats"

        # Verify batting stats match expected
        batting = box_score['batting_stats'][0]
        assert batting['pa'] == 5, f"Expected 5 PA, got {batting['pa']}"
        assert batting['hit'] == 5, f"Expected 5 hits, got {batting['hit']}"

    async def test_stats_with_substitutions(self, db_ops):
        """Test stats track correctly across substitutions"""
        # Create test game
        game_id = uuid4()
        await db_ops.create_game(
            game_id=game_id,
            league_id="sba",
            home_team_id=1,
            away_team_id=2,
            game_mode="friendly",
            visibility="public"
        )

        # Create starter
        starter = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=101,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=True
        )

        pitcher = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=201,
            team_id=2,
            position="P",
            batting_order=1,
            is_starter=True
        )

        # Starter gets 2 hits
        for i in range(1, 3):
            await db_ops.save_play({
                'game_id': game_id,
                'play_num': i,
                'inning': 1,
                'half': 'top',
                'batter_lineup_id': starter,
                'pitcher_lineup_id': pitcher,
                'play_result': 'SINGLE_1',
                'pa': 1,
                'ab': 1,
                'hit': 1,
                'outs_recorded': 0
            })

        # Create substitute
        substitute = await db_ops.add_sba_lineup_player(
            game_id=game_id,
            player_id=102,
            team_id=1,
            position="RF",
            batting_order=1,
            is_starter=False
        )

        # Substitute gets 1 hit
        await db_ops.save_play({
            'game_id': game_id,
            'play_num': 3,
            'inning': 2,
            'half': 'top',
            'batter_lineup_id': substitute,
            'pitcher_lineup_id': pitcher,
            'play_result': 'SINGLE_1',
            'pa': 1,
            'ab': 1,
            'hit': 1,
            'outs_recorded': 0
        })

        # Refresh views
        await stat_view_refresher.refresh_all()

        # Query batting stats for both players
        async with AsyncSessionLocal() as session:
            query = text("""
                SELECT lineup_id, pa, hit
                FROM batting_game_stats
                WHERE game_id = :game_id
                ORDER BY lineup_id
            """)
            result = await session.execute(query, {'game_id': str(game_id)})
            rows = result.fetchall()

        # Verify both players have separate stats
        assert len(rows) == 2, f"Expected stats for 2 players, got {len(rows)}"

        # Verify starter stats
        starter_row = rows[0]
        assert starter_row[0] == starter, "First row should be starter"
        assert starter_row[1] == 2, f"Starter should have 2 PA, got {starter_row[1]}"
        assert starter_row[2] == 2, f"Starter should have 2 hits, got {starter_row[2]}"

        # Verify substitute stats
        sub_row = rows[1]
        assert sub_row[0] == substitute, "Second row should be substitute"
        assert sub_row[1] == 1, f"Substitute should have 1 PA, got {sub_row[1]}"
        assert sub_row[2] == 1, f"Substitute should have 1 hit, got {sub_row[2]}"
