backfill-progress.ps1

# backfill-progress.ps1 - Monitor backfill processing progress

Write-Host "📊 OpenFront Backfill Progress Report" -ForegroundColor Cyan
Write-Host "=" * 50

# Get lobby processing status
$statusQuery = @"
SELECT 
    status,
    COUNT(*) as count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as percentage
FROM unique_lobbies 
WHERE game_mode = 'Free For All'
GROUP BY status 
ORDER BY count DESC;
"@

Write-Host "`n🎮 FFA Lobby Processing Status:" -ForegroundColor Yellow
docker-compose exec -T postgres psql -U scraper -d openfront -c $statusQuery

# Get processing progress over time
$progressQuery = @"
SELECT 
    DATE(processed_at) as date,
    COUNT(*) as games_processed
FROM game_data 
GROUP BY DATE(processed_at)
ORDER BY date DESC
LIMIT 7;
"@

Write-Host "`n📈 Recent Processing Activity:" -ForegroundColor Yellow
docker-compose exec -T postgres psql -U scraper -d openfront -c $progressQuery

# Get duration statistics
$durationQuery = @"
SELECT 
    COUNT(*) as total_games,
    ROUND(AVG(duration_minutes), 1) as avg_duration_mins,
    MIN(duration_minutes) as min_duration_mins,
    MAX(duration_minutes) as max_duration_mins,
    ROUND(STDDEV(duration_minutes), 1) as stddev_mins
FROM game_data 
WHERE duration_minutes IS NOT NULL;
"@

Write-Host "`n⏱️ Game Duration Statistics:" -ForegroundColor Yellow
docker-compose exec -T postgres psql -U scraper -d openfront -c $durationQuery

# Get top maps by game count
$mapsQuery = @"
SELECT 
    game_map,
    COUNT(*) as games_played,
    ROUND(AVG(duration_minutes), 1) as avg_duration
FROM game_data 
WHERE game_map IS NOT NULL
GROUP BY game_map 
ORDER BY games_played DESC
LIMIT 10;
"@

Write-Host "`n🗺️ Top Maps (by games played):" -ForegroundColor Yellow
docker-compose exec -T postgres psql -U scraper -d openfront -c $mapsQuery

# Current processing queue
$queueQuery = @"
SELECT 
    COUNT(*) FILTER (WHERE status = 'backfill_ready') as backfill_ready,
    COUNT(*) FILTER (WHERE status = 'new') as new_ready,
    COUNT(*) FILTER (WHERE status = 'api_completed') as ready_for_processing,
    COUNT(*) FILTER (WHERE status = 'retrying') as retrying
FROM unique_lobbies 
WHERE game_mode = 'Free For All';
"@

Write-Host "`n🔄 Current Processing Queue:" -ForegroundColor Yellow
docker-compose exec -T postgres psql -U scraper -d openfront -c $queueQuery

Write-Host "`n✅ Report Complete" -ForegroundColor Green