Promotion & Relegation: Auto-Calculating the Bottom Three, Manually Entering the Promoted Three

# routers/tables.py (refactored) def compute_league_table(db: Session, season_id: int) -> list[dict]: rows = db.execute(LEAGUE_TABLE_QUERY, {"season_id": season_id}).mappings().all() return [dict(row) for row in rows] @router.get("/seasons/{season_id}/table", response_model=list[schemas.LeagueTableRow]) def get_league_table(season_id: int, db: Session = Depends(get_db)): season = db.get(models.Season, season_id) if not season: raise HTTPException(status_code=404, detail="Season not found") return compute_league_table(db, season_id)

# routers/rollover.py from fastapi import APIRouter, Depends, HTTPException from sqlalchemy.orm import Session from sqlalchemy.exc import IntegrityError from database import get_db from routers.tables import compute_league_table import models, schemas router = APIRouter(prefix="/api", tags=["rollover"]) REQUIRED_TEAMS_PER_SEASON = 20 RELEGATION_COUNT = 3 @router.get("/seasons/{season_id}/relegation-preview", response_model=list[schemas.TeamResponse]) def preview_relegation(season_id: int, db: Session = Depends(get_db)): season = db.get(models.Season, season_id) if not season: raise HTTPException(status_code=404, detail="Season not found") table = compute_league_table(db, season_id) if len(table) != REQUIRED_TEAMS_PER_SEASON: raise HTTPException( status_code=400, detail=f"Season has {len(table)} teams, expected {REQUIRED_TEAMS_PER_SEASON}", ) bottom_three_ids = [row["team_id"] for row in table[-RELEGATION_COUNT:]] return ( db.query(models.Team) .filter(models.Team.id.in_(bottom_three_ids)) .all() )

# schemas.py (additions) from pydantic import field_validator class PromotedTeam(BaseModel): team_name: str short_name: Optional[str] = None class RolloverRequest(BaseModel): promoted_teams: list[PromotedTeam] @field_validator("promoted_teams") @classmethod def exactly_three(cls, value): if len(value) != 3: raise ValueError("Exactly 3 promoted teams are required") return value

# routers/rollover.py (additions) @router.post("/seasons/{new_season_id}/rollover-from/{old_season_id}", response_model=list[schemas.TeamResponse]) def rollover_season( new_season_id: int, old_season_id: int, payload: schemas.RolloverRequest, db: Session = Depends(get_db) ): old_season = db.get(models.Season, old_season_id) new_season = db.get(models.Season, new_season_id) if not old_season or not new_season: raise HTTPException(status_code=404, detail="Season not found") existing_new_season_teams = ( db.query(models.SeasonTeam).filter(models.SeasonTeam.season_id == new_season_id).count() ) if existing_new_season_teams > 0: raise HTTPException( status_code=400, detail="New season already has teams; rollover expects a freshly created, empty season", ) old_table = compute_league_table(db, old_season_id) if len(old_table) != REQUIRED_TEAMS_PER_SEASON: raise HTTPException( status_code=400, detail=f"Old season has {len(old_table)} teams, expected {REQUIRED_TEAMS_PER_SEASON}", ) # old_table is already correctly ordered by compute_league_table's own real sort survivors = old_table[:-RELEGATION_COUNT] for row in survivors: db.add(models.SeasonTeam(season_id=new_season_id, team_id=row["team_id"])) for promoted in payload.promoted_teams: team = db.query(models.Team).filter(models.Team.name == promoted.team_name).first() if not team: team = models.Team(name=promoted.team_name, short_name=promoted.short_name or promoted.team_name) db.add(team) db.flush() db.add(models.SeasonTeam(season_id=new_season_id, team_id=team.id)) try: db.commit() except IntegrityError: db.rollback() raise HTTPException( status_code=409, detail="This rollover could not be completed cleanly — check whether it already ran", ) return ( db.query(models.Team) .join(models.SeasonTeam, models.SeasonTeam.team_id == models.Team.id) .filter(models.SeasonTeam.season_id == new_season_id) .order_by(models.Team.name) .all() )

// static/rollover.js async function previewRelegation(seasonId) { const res = await fetch(`/api/seasons/${seasonId}/relegation-preview`); const teams = await res.json(); alert('Relegated: ' + teams.map(t => t.short_name).join(', ')); } async function rolloverSeason(newSeasonId, oldSeasonId, promotedTeams) { const res = await fetch(`/api/seasons/${newSeasonId}/rollover-from/${oldSeasonId}`, { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ promoted_teams: promotedTeams }), }); if (!res.ok) { const error = await res.json(); alert(error.detail); return; } const roster = await res.json(); alert(`New season roster: ${roster.length} teams`); }