# Season Batting Stats Trigger Migration Plan

## Overview
Convert the existing `season_batting_stats_view` materialized view to a trigger-based table for incremental updates when games are completed.

## Current State
- Materialized view defined in `season_batting_stats_view.sql`
- Requires full refresh for all data updates
- Used by `/api/v3/views/season-batting` endpoint in `app/routers_v3/views.py`

## Target State
- Regular table with same structure and data
- Trigger automatically updates only affected players when games complete
- Same query performance, much faster updates (~30 players vs entire dataset)

## Pre-Migration Checklist

### 1. Backup Current Data
```sql
-- Export current materialized view data
CREATE TABLE season_batting_stats_backup AS 
SELECT * FROM season_batting_stats_view;
```

### 2. Verify Dependencies
- [x] Confirm `app/routers_v3/views.py` uses `SeasonBattingStatsView` model
- [x] Check if any other code references the materialized view
- [ ] Verify `stratgame` table has `is_complete` column or similar completion flag

### 3. Environment Setup
- [ ] Test database connection in Adminer
- [ ] Verify PostgreSQL version supports required trigger syntax
- [ ] Ensure adequate storage space for table conversion

## Migration Steps

### Step 1: Database Schema Migration
**Location:** Run in Adminer with "Stop on error" ✅ checked

```sql
-- Execute the full conversion SQL from season_batting_stats_view.sql
-- This includes:
-- 1. DROP MATERIALIZED VIEW season_batting_stats_view CASCADE
-- 2. CREATE TABLE season_batting_stats_view AS (original query)
-- 3. ADD PRIMARY KEY CONSTRAINT (player_id, season)
-- 4. CREATE indexes for performance
-- 5. CREATE trigger function update_season_batting_stats_for_game()
-- 6. CREATE trigger on stratgame completion
```

### Step 2: Verify Migration Success
```sql
-- Check table structure matches original
\d season_batting_stats_view

-- Verify data integrity
SELECT COUNT(*) FROM season_batting_stats_view;
SELECT COUNT(*) FROM season_batting_stats_backup;

-- Test sample queries
SELECT * FROM season_batting_stats_view WHERE season = 12 LIMIT 5;
```

### Step 3: Test Trigger Functionality
```sql
-- Test trigger by marking a game complete (if safe to do so)
-- OR create a test scenario with sample data
```

### Step 4: Application Testing
- [ ] Restart FastAPI application
- [ ] Test `/api/v3/views/season-batting?season=12` endpoint
- [ ] Verify query performance is maintained
- [ ] Confirm data consistency with previous results

## Game Completion Integration

### Required: Game Completion Flag
The trigger depends on a game completion mechanism. Verify one of these exists:

**Option A: Existing column**
```sql
-- Check if stratgame has completion tracking
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'stratgame' 
  AND column_name IN ('is_complete', 'completed', 'status', 'final');
```

**Option B: Alternative completion logic**
If no completion flag exists, the trigger can be modified to fire on:
- Game result updates
- Final play insertion
- Manual API call

### Integration Points
- `app/routers_v3/games.py` - Game completion endpoints
- `app/routers_v3/stratplay.py` - Play insertion logic
- Discord bot game processing workflows

## Rollback Plan

### If Migration Fails
```sql
-- Restore original materialized view
CREATE MATERIALIZED VIEW season_batting_stats_view AS
-- (original query from season_batting_stats_view.sql)

-- Restore original indexes
CREATE INDEX idx_season_batting_stats_season ON season_batting_stats_view (season);
CREATE INDEX idx_season_batting_stats_team ON season_batting_stats_view (season, player_team_id);
CREATE INDEX idx_season_batting_stats_player ON season_batting_stats_view (player_id);

-- Clean up failed table if needed
DROP TABLE IF EXISTS season_batting_stats_view;
```

### If Performance Issues Arise
- Monitor query execution plans
- Add additional indexes if needed
- Consider reverting to materialized view if trigger overhead is too high

## Success Criteria

### Functional Requirements
- [x] All existing queries return same results
- [ ] Trigger updates only affected players when games complete
- [ ] Query performance matches or exceeds materialized view
- [ ] FastAPI endpoints work without modification

### Performance Targets
- **Query time**: ≤ current materialized view performance
- **Update time**: < 5 seconds for typical game (30 players)
- **Storage overhead**: Minimal (same data structure)

### Monitoring
- Log trigger execution times
- Monitor database performance during game completions
- Track any query plan changes

## Post-Migration Tasks

### 1. Cleanup
```sql
-- Remove backup table after successful migration (1+ weeks)
DROP TABLE season_batting_stats_backup;
```

### 2. Documentation Updates
- Update `database/CLAUDE.md` to document new trigger system
- Document game completion integration requirements
- Update API documentation if needed

### 3. Consider Similar Views
Evaluate other materialized views for similar conversion:
- Season pitching stats
- Season fielding stats
- Career statistics views

## Risk Assessment

### High Risk
- **Data loss**: Mitigated by backup table creation
- **Query performance degradation**: Mitigated by same index structure
- **Trigger bugs**: Mitigated by comprehensive testing

### Medium Risk
- **Application downtime**: Plan migration during low-usage period
- **Complex rollback**: Keep original SQL and backup data

### Low Risk
- **Storage increase**: Minimal, same data structure
- **Maintenance overhead**: Triggers are largely self-managing

## Timeline

### Preparation Phase (1-2 days)
- [ ] Complete pre-migration checklist
- [ ] Set up backup procedures
- [ ] Identify game completion mechanism

### Migration Phase (2-4 hours)
- [ ] Execute database migration
- [ ] Verify data integrity
- [ ] Test application functionality

### Validation Phase (1 week)
- [ ] Monitor trigger performance
- [ ] Validate game completion updates
- [ ] Clean up backup data

**Total Estimated Time**: 3-4 days including testing and monitoring