Migrate last_season to PlayerSeason table

[finleysg]

Summary

Replace Player.last_season integer column with a register_playerseason table holding full membership history (one row per player per season).

Phase 1: Add New Model + Schema

1.1 Django model

• File: backend/register/models.py (after PlayerHandicap)
• Add PlayerSeason model: player FK + season int, unique constraint (player, season)
• Keep last_season on Player for now (removed in Phase 5)

1.2 Django migration

• Run makemigrations register to create the new table

1.3 Django admin

• File: backend/register/admin.py
• Add PlayerSeasonInline (TabularInline) to PlayerAdmin.inlines

1.4 Django serializer

• File: backend/register/serializers.py
• Replace last_season with computed is_returning_member (SerializerMethodField) in SimplePlayerSerializer and PlayerSerializer
• Logic: PlayerSeason.objects.filter(player=obj, season=current_season()-1).exists()

1.5 Drizzle schema

• File: apps/api/src/database/schema/registration.schema.ts
• Add playerSeason table: id, playerId FK, season int, unique constraint

1.6 Domain type

• File: packages/domain/src/types/register/player.ts
• Remove lastSeason?: number | null
• Add isReturningMember?: boolean

Phase 2: SQL Backfill Script

2.1 Create backfill script

• File: backend/sql/BackfillPlayerSeasons.sql (new)

INSERT INTO register_playerseason (player_id, season)
SELECT DISTINCT rs.player_id, e.season
FROM register_registrationslot rs
JOIN events_event e ON rs.event_id = e.id
WHERE e.event_type = 'R'
  AND rs.status = 'R'
  AND rs.player_id IS NOT NULL
ON DUPLICATE KEY UPDATE season = VALUES(season);

Phase 3: API Layer Updates (NestJS)

3.1 Repository methods

• File: apps/api/src/registration/repositories/registration.repository.ts
• Add: createPlayerSeason(playerId, season), deletePlayerSeason(playerId, season), isReturningMember(playerId, season) (checks row exists for season-1)

3.2 Update updateMembershipStatus

• File: apps/api/src/registration/services/admin-registration.service.ts
• Replace player.lastSeason = season with repository.createPlayerSeason(player.id, season)
• Still set player.isMember = 1

3.3 Update player drop-registration

• File: apps/api/src/registration/services/player.service.ts
• When dropping season registration: call repository.deletePlayerSeason(playerId, season) and unset isMember

3.4 Update mail service

• File: apps/api/src/mail/mail.service.ts
• Replace player.lastSeason === currentYear - 1 with precomputed isReturningMember boolean passed from the calling service

3.5 Update mapper

• File: apps/api/src/registration/mappers.ts
• Remove lastSeason mapping, add isReturningMember (computed from PlayerSeason or passed through)

3.6 Stripe webhook

• No direct change needed — already calls updateMembershipStatus (updated in 3.2)

Phase 4: Frontend Updates

4.1 Public Player model

• File: apps/public/src/models/player.ts
• Remove last_season from zod schemas
• Source isReturningMember from API field is_returning_member instead of computing from last_season

4.2 Registration context

• File: apps/public/src/context/registration-context-provider.tsx
• Replace last_season: currentSeason - 1 with is_returning_member: true in optimistic cache update

4.3 Report utils

• File: apps/public/src/components/reports/report-utils.ts
• Replace obj.last_season == currentSeason - 1 with obj.is_returning_member (requires SP update below)

4.4 Season event detail + club event model

• No changes needed — already read player.isReturningMember property

Phase 5: Django Backend Updates + Cleanup

5.1 Update derive_notification_type

• File: backend/payments/utils.py
• Replace player.last_season == (season - 1) with PlayerSeason.objects.filter(player=player, season=season-1).exists()

5.2 Update Django tests

• File: backend/payments/tests/test_utils.py
• Replace mock last_season attributes with PlayerSeason fixtures

5.3 Update SQL stored procedures

• File: backend/sql/PostRestoreUpdates.sql
• GetFriends, SearchPlayers: remove p.last_season from SELECT
• MembershipReport: replace rp.last_season with LEFT JOIN to register_playerseason

5.4 Domain payment function

• File: packages/domain/src/functions/payment.ts
• Update deriveNotificationType — callers pass isReturningMember: boolean instead of playerLastSeason: number | null

5.5 Remove last_season from Player

• backend/register/models.py — delete last_season field
• backend/register/admin.py — remove from fields and list_filter
• apps/api/src/database/schema/registration.schema.ts — remove lastSeason column

5.6 Final Django migration

• Run makemigrations register to drop last_season column

5.7 Update NestJS tests

• All test files with mock lastSeason on player objects:
  • apps/api/src/registration/__tests__/admin-registration.service.test.ts
  • apps/api/src/registration/__tests__/player.service.test.ts
  • apps/api/src/registration/__tests__/registration.service.test.ts
  • apps/api/src/registration/__tests__/payments.service.test.ts
  • apps/api/src/mail/__tests__/mail.service.test.ts
  • apps/api/src/stripe/__tests__/stripe-webhook.service.test.ts

Verification

1. Run Django migrations: uv run python manage.py migrate
2. Run backfill script against dev database
3. Run Django tests: uv run python manage.py test
4. Run API tests: pnpm --filter api test
5. docker compose up -d --build and verify:
   • Season registration flow creates PlayerSeason row
   • Drop registration removes PlayerSeason row
   • Returning member fees display correctly
   • Membership report shows correct returning/new status