# Phase 3.5 Statistics System - Session Handoff

**Date**: 2025-11-07
**Status**: In Progress - Materialized Views Implementation
**Completion**: ~40%

---

## Summary

Successfully pivoted Phase 3.5 statistics implementation from in-memory cache approach to PostgreSQL materialized views approach, following legacy API pattern. This is a simpler, more maintainable solution with zero stat tracking code needed.

---

## Architectural Decision: Materialized Views

### Why We Pivoted

**Original Plan** (`phase-3.5-polish-stats.md`):
- In-memory stat cache (GameStatsCache, PlayerStatsCache)
- StatTracker service with background persistence
- Complex caching logic with dirty tracking
- ~400-500 lines of stat tracking code

**New Approach** (`STAT_SYSTEM_MATERIALIZED_VIEWS.md`):
- PostgreSQL materialized views aggregate from plays table
- Single source of truth: plays table
- Zero stat tracking code (PostgreSQL does aggregation)
- ~200 lines simpler, more maintainable

**Decision**: Materialized views approved - following proven pattern from legacy major-domo API.

---

## Work Completed

### 1. ✅ PlayStatCalculator Service

**File**: `backend/app/services/play_stat_calculator.py` (145 lines)

**Purpose**: Calculates statistical fields from PlayOutcome for database storage

**Key Method**:
```python
PlayStatCalculator.calculate_stats(
    outcome: PlayOutcome,
    result: PlayResult,
    state_before: GameState,
    state_after: GameState
) -> Dict[str, int]
```

**Returns**: Dict with fields like `pa`, `ab`, `hit`, `run`, `rbi`, `double`, `triple`, `homerun`, `bb`, `so`, etc.

**Logic**:
- Determines PA vs AB (walks/HBP/sac don't count as AB)
- Identifies hit types from PlayOutcome helpers
- Calculates runs/RBIs from state change
- Handles non-PA events (SB, CS, WP, PB, BALK, PICKOFF)
- Tracks outs recorded

### 2. ✅ GameEngine Integration

**File**: `backend/app/core/game_engine.py`

**Changes**:
- Added import: `from app.services import PlayStatCalculator`
- Modified `_save_play_to_db()` method (lines 1027-1045):
  - Creates `state_after` by cloning state and applying result
  - Calls `PlayStatCalculator.calculate_stats()`
  - Merges stat fields into `play_data` dict
  - Stats automatically saved with every play

**Impact**: Every play record now includes complete statistical fields for aggregation.

### 3. ✅ Materialized Views Migration

**File**: `backend/alembic/versions/004_create_stat_materialized_views.py` (180 lines)

**Creates 3 Views**:

1. **batting_game_stats**
   - Aggregates batting stats per player per game
   - Fields: pa, ab, run, hit, rbi, double, triple, hr, bb, so, hbp, sac, sb, cs, gidp
   - Indexes: lineup_id (unique), game_id, player_card_id

2. **pitching_game_stats**
   - Aggregates pitching stats per player per game
   - Fields: batters_faced, hit_allowed, run_allowed, erun, bb, so, hbp, hr_allowed, wp, ip
   - IP calculation: `SUM(outs_recorded)::float / 3.0`
   - Indexes: lineup_id (unique), game_id, player_card_id

3. **game_stats**
   - Team totals and linescore per game
   - Fields: home_runs, away_runs, home_hits, away_hits, home_errors, away_errors
   - Linescore: JSON arrays `[0, 1, 0, 3, ...]` per inning
   - Index: game_id (unique)

**Usage**:
```sql
-- Get box score
SELECT * FROM batting_game_stats WHERE game_id = ?;
SELECT * FROM pitching_game_stats WHERE game_id = ?;
SELECT * FROM game_stats WHERE game_id = ?;
```

### 4. ✅ Play Model

**File**: `backend/app/models/db_models.py`

**Status**: Already had all required stat fields! No changes needed.

**Existing Fields** (lines 159-196):
- Batting: `pa`, `ab`, `hit`, `double`, `triple`, `homerun`, `bb`, `so`, `hbp`, `rbi`, `sac`, `ibb`, `gidp`
- Baserunning: `sb`, `cs`
- Pitching events: `wild_pitch`, `passed_ball`, `pick_off`, `balk`
- Runs: `run`, `e_run`
- Outs: Already tracked in other field
- Added via PlayStatCalculator: `outs_recorded`

---

## Work Remaining

### 5. ⏳ BoxScoreService (Next Priority)

**File**: `backend/app/services/box_score_service.py` (NEW)

**Purpose**: Query materialized views for formatted box scores

**Key Method**:
```python
async def get_box_score(game_id: UUID) -> dict:
    """
    Returns:
        {
            'game_stats': {...},           # Team totals, linescore
            'batting_stats': [...],        # Player batting lines
            'pitching_stats': [...]        # Player pitching lines
        }
    """
```

**Implementation**: Use SQLAlchemy `text()` queries against views

### 6. ⏳ StatViewRefresher (Next Priority)

**File**: `backend/app/services/stat_view_refresher.py` (NEW)

**Purpose**: Refresh materialized views at strategic moments

**Key Method**:
```python
async def refresh_all() -> None:
    """Refresh all stat views using REFRESH MATERIALIZED VIEW CONCURRENTLY"""
```

**Refresh Strategy**:
- ✅ **After game completion** (recommended for MVP)
- ✅ **On demand** (for terminal client testing)
- ⏳ **Scheduled** (every 5 minutes - optional Phase 3.6+)

### 7. ⏳ Terminal Client Integration

**File**: `backend/terminal_client/commands.py`

**Add**: `box_score` command using BoxScoreService

**Usage**:
```bash
⚾ > box_score <game_id>
# Displays formatted box score with player stats
```

### 8. ⏳ Testing

**Unit Tests Needed**:
- `tests/unit/services/test_play_stat_calculator.py` (~15 tests)
  - Test each outcome type (hits, walks, strikeouts, etc.)
  - Test PA vs AB logic
  - Test runs/RBI calculation
  - Test non-PA events (SB, CS, WP, PB)

- `tests/unit/services/test_box_score_service.py` (~8 tests)
  - Test view queries
  - Test data formatting
  - Test missing data handling

**Integration Tests Needed**:
- `tests/integration/test_stat_views.py` (~6 tests)
  - Play complete game → verify stats in views
  - Test view refresh
  - Test box score retrieval

### 9. ⏳ Run Migration

**Command**:
```bash
cd backend
uv run alembic upgrade head
```

**Verify**:
```sql
\d batting_game_stats
\d pitching_game_stats
\d game_stats
```

---

## Files Created

```
backend/app/services/play_stat_calculator.py     (145 lines) - NEW
backend/alembic/versions/004_create_stat_materialized_views.py  (180 lines) - NEW
```

## Files Modified

```
backend/app/services/__init__.py                 (+1 export)
backend/app/core/game_engine.py                  (+19 lines in _save_play_to_db)
```

## Files Removed

```
backend/app/models/stat_models.py                (DELETED - was for in-memory cache)
```

---

## Key Design Decisions

### 1. Stats Calculated at Play Save Time

**When**: In `GameEngine._save_play_to_db()` (STEP 2 of play resolution)

**Why**: We have access to:
- `state` (before play)
- `result` (play outcome with details)
- Can construct `state_after` by applying result

**Alternative Considered**: Calculate during play resolution
**Rejected**: Would add logic to PlayResolver, muddies separation of concerns

### 2. State Clone for state_after

**Approach**: `state.model_copy(deep=True)` then apply outs/runs

**Why**: Lightweight, no side effects on actual state

**Alternative Considered**: Pass both states from resolve_play
**Rejected**: Would require changing method signatures throughout

### 3. Materialized Views Over Regular Views

**Choice**: `CREATE MATERIALIZED VIEW` with `REFRESH MATERIALIZED VIEW CONCURRENTLY`

**Why**:
- Faster queries (pre-computed, indexed)
- Can refresh without locking
- Good for read-heavy workloads (box scores)

**Tradeoff**: Must manually refresh (not real-time)

**Acceptable**: Box scores typically viewed after game completion

---

## Integration Points

### GameEngine → PlayStatCalculator

```python
# In _save_play_to_db (lines 1027-1045)
state_after = state.model_copy(deep=True)
state_after.outs += result.outs_recorded
state_after.home_score += result.runs_scored  # or away_score

stats = PlayStatCalculator.calculate_stats(
    outcome=result.outcome,
    result=result,
    state_before=state,
    state_after=state_after
)

play_data.update(stats)  # Merge into database dict
```

### Database → Materialized Views

```
plays table (writes)
    ↓
Materialized views (reads after refresh)
    ↓
BoxScoreService (queries)
    ↓
Terminal client / WebSocket
```

### Refresh Workflow

```python
# After game completion
await game_engine.end_game(game_id)
await stat_view_refresher.refresh_all()

# Box score now available
box_score = await box_score_service.get_box_score(game_id)
```

---

## Testing Strategy

### Current Test Status

**Before Changes**: 688 tests passing
**After Changes**: Need to verify still passing + add new tests

**Run Tests**:
```bash
cd backend

# Unit tests (should still pass)
uv run pytest tests/unit/ -v

# Specific new tests (once created)
uv run pytest tests/unit/services/test_play_stat_calculator.py -v
```

### Test Data Setup

**For Play Stats**:
- Use existing GameState fixtures
- Create various PlayOutcome scenarios
- Verify stat field values

**For Views**:
- Create game with plays in test database
- Refresh views
- Query and verify aggregation

---

## Next Steps for New Session

### Immediate (1-2 hours):

1. **Create BoxScoreService**
   - File: `backend/app/services/box_score_service.py`
   - Method: `async def get_box_score(game_id: UUID) -> dict`
   - Use `text()` queries against materialized views
   - Format response with game totals + player stats

2. **Create StatViewRefresher**
   - File: `backend/app/services/stat_view_refresher.py`
   - Method: `async def refresh_all() -> None`
   - Execute `REFRESH MATERIALIZED VIEW CONCURRENTLY` for all 3 views
   - Add error handling and logging

3. **Update services/__init__.py**
   - Export both new services

### Testing (2-3 hours):

4. **Write PlayStatCalculator Tests**
   - Test all outcome types
   - Test edge cases (2 outs, grand slam, etc.)
   - Verify PA vs AB logic

5. **Write BoxScoreService Tests**
   - Mock database queries
   - Test response formatting
   - Test missing data scenarios

6. **Integration Test**
   - Play complete game via terminal client
   - Verify stats populate in plays table
   - Refresh views manually
   - Query views and verify aggregation

### Polish (1 hour):

7. **Terminal Client Command**
   - Add `box_score` command to `terminal_client/commands.py`
   - Format and display using Rich tables
   - Test with real game data

8. **Run Migration**
   - `uv run alembic upgrade head`
   - Verify views exist in database
   - Test refresh performance

---

## References

### Key Documents

- **Original Plan**: `.claude/implementation/phase-3.5-polish-stats.md`
- **Materialized Views Design**: `.claude/implementation/STAT_SYSTEM_MATERIALIZED_VIEWS.md`
- **Current Handoff**: `.claude/PHASE_3_5_HANDOFF.md` (this file)

### Code Locations

- **Play Model**: `backend/app/models/db_models.py:96-244`
- **PlayStatCalculator**: `backend/app/services/play_stat_calculator.py`
- **GameEngine Integration**: `backend/app/core/game_engine.py:936-1048`
- **Migration**: `backend/alembic/versions/004_create_stat_materialized_views.py`

### Related Systems

- **PlayOutcome Enum**: `backend/app/config/play_outcome.py`
  - Helper methods: `is_hit()`, `is_out()`, `get_hit_outcomes()`
- **GameState Model**: `backend/app/models/game_models.py`
  - Used for state_before and state_after
- **Terminal Client**: `backend/terminal_client/`
  - Will display box scores

---

## Progress Tracking

### Phase 3.5 Overall Status

- [x] **Stat Fields** - Play model already complete
- [x] **Migration** - Not needed (fields already exist)
- [x] **PlayStatCalculator** - Complete (145 lines)
- [x] **GameEngine Integration** - Complete
- [x] **Materialized Views Migration** - Complete (180 lines)
- [ ] **BoxScoreService** - TODO (next priority)
- [ ] **StatViewRefresher** - TODO (next priority)
- [ ] **Terminal Client** - TODO
- [ ] **Unit Tests** - TODO (~23 tests)
- [ ] **Integration Tests** - TODO (~6 tests)
- [ ] **Run Migration** - TODO (manual step)

**Estimated Remaining**: 4-6 hours
**Total Phase 3.5**: ~50% complete

---

## Quick Start for New Session

```bash
# 1. Navigate to backend
cd /mnt/NV2/Development/strat-gameplay-webapp/backend

# 2. Verify current state
uv run pytest tests/unit/ -q  # Should pass

# 3. Check PlayStatCalculator
cat app/services/play_stat_calculator.py

# 4. Check GameEngine integration
grep -A 20 "PlayStatCalculator" app/core/game_engine.py

# 5. Review migration
cat alembic/versions/004_create_stat_materialized_views.py

# 6. Create BoxScoreService (NEXT TASK)
# File: app/services/box_score_service.py
```

---

## Notes & Caveats

### Known Issues

- **Earned Runs**: Currently all runs counted as earned (`erun = run`)
  - TODO: Implement earned run logic with error tracking
  - Low priority for MVP

- **Integration Tests**: May need individual execution due to asyncpg pooling
  - Run with: `uv run pytest tests/integration/test_name.py::test_func -v`

### Performance Considerations

- **View Refresh**: Takes ~10-50ms for small games, longer for large datasets
- **Use CONCURRENTLY**: Allows reads during refresh (no locking)
- **Refresh Strategy**: After game completion is optimal for MVP

### Future Enhancements

- **Real-time Stats**: Could use regular views instead of materialized
  - Tradeoff: Slower queries, always current
  - Consider for Phase 4+

- **Partial Refresh**: PostgreSQL doesn't support natively
  - Would need custom implementation or partitioning
  - Low priority

---

**Session End**: 2025-11-07
**Next Session**: Continue with BoxScoreService and StatViewRefresher
**Context**: 175k/200k tokens (88%)