strat-gameplay-webapp/.claude/plans/009-integration-test-fix.md

# Plan 009: Fix Integration Test Infrastructure

**Priority**: MEDIUM
**Effort**: 2-3 days
**Status**: NOT STARTED
**Risk Level**: LOW - Testing infrastructure

---

## Problem Statement

Integration tests have infrastructure issues with AsyncPG:
- Connection conflicts with concurrent tests
- Fixture scope mismatches (module vs function)
- ~49 errors, 28 failures in integration test suite

Current workaround: Run tests individually or serially.

## Impact

- **CI/CD**: Can't run full test suite in pipeline
- **Confidence**: Integration behavior not verified automatically
- **Speed**: Serial execution is slow

## Root Causes

### 1. AsyncPG Connection Conflicts
```
asyncpg.exceptions.InterfaceError: cannot perform operation:
another operation is in progress
```

AsyncPG doesn't support concurrent operations on a single connection.

### 2. Fixture Scope Mismatches
```python
@pytest.fixture(scope="module")       # Module scope
async def setup_database(event_loop):  # Depends on function-scoped event_loop
```

Different scopes cause `ScopeMismatch` errors.

### 3. Shared Database State
Tests don't properly isolate database state, causing cascading failures.

## Files to Modify

| File | Changes |
|------|---------|
| `backend/tests/conftest.py` | Fix fixture scopes |
| `backend/tests/integration/conftest.py` | Add test isolation |
| `backend/pyproject.toml` | Update pytest-asyncio config |

## Implementation Steps

### Step 1: Update pytest-asyncio Configuration (30 min)

Update `backend/pyproject.toml`:

```toml
[tool.pytest.ini_options]
asyncio_mode = "auto"
asyncio_default_fixture_loop_scope = "function"  # Important!
```

This ensures each test function gets its own event loop.

### Step 2: Create Test Database Utilities (1 hour)

Create `backend/tests/utils/db_utils.py`:

```python
"""Database utilities for integration tests."""

import asyncio
from contextlib import asynccontextmanager
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.pool import NullPool

from app.models.db_models import Base
from app.config import settings


def get_test_database_url() -> str:
    """Get test database URL (separate from dev/prod)."""
    # Use a different database for tests
    base_url = settings.database_url
    if "strat_gameplay" in base_url:
        return base_url.replace("strat_gameplay", "strat_gameplay_test")
    return base_url + "_test"


def create_test_engine():
    """Create engine with NullPool (no connection pooling)."""
    return create_async_engine(
        get_test_database_url(),
        poolclass=NullPool,  # Each connection is created fresh
        echo=False,
    )


@asynccontextmanager
async def test_session():
    """Create isolated test session."""
    engine = create_test_engine()

    async with engine.begin() as conn:
        # Create tables
        await conn.run_sync(Base.metadata.create_all)

    AsyncTestSession = async_sessionmaker(
        engine,
        class_=AsyncSession,
        expire_on_commit=False,
    )

    async with AsyncTestSession() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()

    # Clean up
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)

    await engine.dispose()


async def reset_database(session: AsyncSession):
    """Reset database to clean state."""
    # Delete all data in reverse dependency order
    for table in reversed(Base.metadata.sorted_tables):
        await session.execute(table.delete())
    await session.commit()
```

### Step 3: Update Integration Test Fixtures (2 hours)

Update `backend/tests/integration/conftest.py`:

```python
"""Integration test fixtures."""

import pytest
import pytest_asyncio
from uuid import uuid4

from tests.utils.db_utils import create_test_engine, test_session, reset_database
from app.models.db_models import Base
from app.database.operations import DatabaseOperations


@pytest_asyncio.fixture(scope="function")
async def test_db():
    """
    Create fresh database for each test.
    Uses NullPool to avoid connection conflicts.
    """
    engine = create_test_engine()

    # Create tables
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    yield engine

    # Drop tables
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)

    await engine.dispose()


@pytest_asyncio.fixture(scope="function")
async def db_session(test_db):
    """
    Provide isolated database session for each test.
    Automatically rolls back after test.
    """
    from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker

    AsyncTestSession = async_sessionmaker(
        test_db,
        class_=AsyncSession,
        expire_on_commit=False,
    )

    async with AsyncTestSession() as session:
        yield session
        # Rollback any uncommitted changes
        await session.rollback()


@pytest_asyncio.fixture(scope="function")
async def db_ops(db_session, monkeypatch):
    """
    Provide DatabaseOperations with test session.
    """
    from app.database import session as db_module

    # Monkeypatch to use test session
    original_session = db_module.AsyncSessionLocal

    # Create factory that returns our test session
    async def get_test_session():
        return db_session

    monkeypatch.setattr(db_module, 'AsyncSessionLocal', get_test_session)

    yield DatabaseOperations()

    # Restore
    monkeypatch.setattr(db_module, 'AsyncSessionLocal', original_session)


@pytest_asyncio.fixture(scope="function")
async def sample_game(db_ops):
    """Create sample game for testing."""
    game_id = uuid4()
    await db_ops.create_game({
        "id": game_id,
        "league_id": "sba",
        "home_team_id": 1,
        "away_team_id": 2,
        "status": "in_progress"
    })
    return game_id


@pytest_asyncio.fixture(scope="function")
async def sample_lineups(db_ops, sample_game):
    """Create sample lineups for testing."""
    lineups = []
    for team_id in [1, 2]:
        for i in range(9):
            lineup = await db_ops.create_lineup({
                "game_id": sample_game,
                "team_id": team_id,
                "card_id": 100 + (team_id * 10) + i,
                "position": ["P", "C", "1B", "2B", "3B", "SS", "LF", "CF", "RF"][i],
                "batting_order": i + 1 if i > 0 else None,
                "is_active": True
            })
            lineups.append(lineup)
    return lineups
```

### Step 4: Fix Specific Test Files (4 hours)

Update each integration test file to use function-scoped fixtures:

**Example: `tests/integration/database/test_operations.py`**

```python
"""Integration tests for DatabaseOperations."""

import pytest
from uuid import uuid4

class TestDatabaseOperations:
    """Tests for database CRUD operations."""

    @pytest.mark.asyncio
    async def test_create_game(self, db_ops):
        """Test game creation."""
        game_id = uuid4()
        result = await db_ops.create_game({
            "id": game_id,
            "league_id": "sba",
            "home_team_id": 1,
            "away_team_id": 2,
        })

        assert result is not None
        assert result["id"] == game_id

    @pytest.mark.asyncio
    async def test_get_game(self, db_ops, sample_game):
        """Test game retrieval."""
        game = await db_ops.get_game(sample_game)

        assert game is not None
        assert game["id"] == sample_game

    @pytest.mark.asyncio
    async def test_create_play(self, db_ops, sample_game, sample_lineups):
        """Test play creation."""
        play_id = await db_ops.save_play({
            "game_id": sample_game,
            "play_number": 1,
            "inning": 1,
            "half": "top",
            "outs_before": 0,
            "outs_after": 1,
            "batter_id": sample_lineups[0].id,
            "pitcher_id": sample_lineups[9].id,
            "outcome": "STRIKEOUT",
        })

        assert play_id is not None

    @pytest.mark.asyncio
    async def test_get_plays(self, db_ops, sample_game, sample_lineups):
        """Test play retrieval."""
        # Create a play first
        await db_ops.save_play({
            "game_id": sample_game,
            "play_number": 1,
            # ... play data
        })

        plays = await db_ops.get_plays(sample_game)

        assert len(plays) == 1
```

### Step 5: Add Test Isolation Markers (1 hour)

Update `backend/pyproject.toml`:

```toml
[tool.pytest.ini_options]
markers = [
    "integration: marks tests as integration tests (require database)",
    "slow: marks tests as slow (>1s)",
    "serial: marks tests that must run serially",
]
```

Update tests that need serial execution:

```python
@pytest.mark.serial
@pytest.mark.integration
class TestGameStateRecovery:
    """Tests that must run serially due to shared state."""
    pass
```

### Step 6: Create pytest Plugin for Serial Tests (1 hour)

Create `backend/tests/plugins/serial_runner.py`:

```python
"""Plugin for running serial tests in order."""

import pytest


def pytest_configure(config):
    """Register serial marker."""
    config.addinivalue_line(
        "markers", "serial: mark test to run serially"
    )


@pytest.hookimpl(tryfirst=True)
def pytest_collection_modifyitems(config, items):
    """Ensure serial tests run after parallel tests."""
    serial_tests = []
    parallel_tests = []

    for item in items:
        if item.get_closest_marker("serial"):
            serial_tests.append(item)
        else:
            parallel_tests.append(item)

    # Run parallel tests first, then serial
    items[:] = parallel_tests + serial_tests
```

### Step 7: Update CI Configuration (30 min)

Update `.github/workflows/test.yml`:

```yaml
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:14
        env:
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
          POSTGRES_DB: strat_gameplay_test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5          
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.13'

      - name: Install dependencies
        run: |
          cd backend
          pip install -e ".[dev]"          

      - name: Run unit tests
        run: |
          cd backend
          pytest tests/unit/ -v --tb=short          

      - name: Run integration tests
        env:
          DATABASE_URL: postgresql+asyncpg://test:test@localhost:5432/strat_gameplay_test
        run: |
          cd backend
          pytest tests/integration/ -v --tb=short -x          
```

### Step 8: Verify Test Suite (1 hour)

```bash
cd /mnt/NV2/Development/strat-gameplay-webapp/backend

# Run unit tests (should all pass)
pytest tests/unit/ -v

# Run integration tests (should now pass)
pytest tests/integration/ -v

# Run full suite
pytest tests/ -v --tb=short

# Check for any remaining failures
pytest tests/ -v --tb=long -x  # Stop on first failure
```

## Verification Checklist

- [ ] All unit tests pass (739/739)
- [ ] Integration tests pass without connection errors
- [ ] No fixture scope mismatch errors
- [ ] Tests can run in parallel with pytest-xdist
- [ ] CI pipeline passes
- [ ] Test execution time < 60 seconds

## Expected Outcome

| Metric | Before | After |
|--------|--------|-------|
| Unit tests | 739/739 ✅ | 739/739 ✅ |
| Integration tests | ~49 errors | 0 errors |
| Total time | N/A (can't run) | < 60s |
| CI status | ❌ Failing | ✅ Passing |

## Rollback Plan

If issues persist:
1. Keep serial execution as workaround
2. Skip problematic tests with `@pytest.mark.skip`
3. Run integration tests nightly instead of per-commit

## Dependencies

- None (can be implemented independently)

## Notes

- Consider using testcontainers for fully isolated DB
- May want to add pytest-xdist for parallel execution
- Future: Add database seeding scripts for complex scenarios