Feature: Proper season/competition management

[adrunkhuman]

Current State

Weeks run back-to-back forever. Standings are calculated across ALL fixtures ever created. There is no concept of "season" or "competition" boundaries.

Problem

• No way to organize fixtures into logical groups (seasons/tournaments)
• Standings accumulate forever (Week 47 of a random season includes Week 3 from 2 years ago)
• Cannot view "this season only" standings
• Cannot browse historical seasons
• Cannot have separate competitions (e.g., "World Cup 2024" vs "Premier League 2024/25")

Proposed Solution: Seasons System

Core Concepts

• Season: A named competition period with start/end dates
• Fixture: Belongs to exactly one season
• Week Number: Resets to 1 for each new season
• Standings: Filterable by season (default = current season)

Database Changes

-- New table
CREATE TABLE seasons (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,              -- "Premier League 2024/25"
    start_date DATETIME,
    end_date DATETIME,
    status TEXT DEFAULT active,    -- active, ended, archived
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Migration: Add season_id to fixtures
ALTER TABLE fixtures ADD COLUMN season_id INTEGER;
-- Migration: Create default season for existing fixtures

Key Features

1. Admin Commands

• /admin season create <name> - Start new season, auto-close previous
• /admin season end - Close current season (must close open fixture first?)
• /admin season list - View all seasons
• /admin season switch <id> - Switch active season (for special events?)

2. User Commands

• /standings - Show current season standings (default)
• /standings alltime - All-time standings
• /standings season <name> - Specific season
• /seasons - List available seasons with dates

3. Week Numbering

• Reset to 1 for each season
• Display: "Premier League 2024/25 - Week 3" instead of just "Week 47"

4. Migration Strategy
On startup:

1. Check if seasons table exists
2. If not: create seasons table + season_id column
3. Create "Legacy Season" for all existing fixtures
4. Set as active season

Open Questions

1. Auto-close on season end?

   • Should ending a season require all fixtures to be closed?
   • Or auto-close the current open fixture?

2. Overlapping seasons?

   • Can you have multiple active seasons (e.g., league + cup running simultaneously)?
   • Or strictly one active season at a time?

3. Standings display

   • Default: current season only?
   • Include "last week" from current season?
   • All-time as secondary option?

4. Historical browsing

   • Show week-by-week for past seasons?
   • Just final standings?

5. Scoring rules per season?

   • Related to Feature: Configurable scoring system #50 - different seasons might have different scoring

UI Examples

Current:

Week 47 Fixtures

Desired:

Premier League 2024/25 - Week 3
[3/38 weeks completed]

Standings command:

/standings
→ Shows current season by default

/standings season:"World Cup 2022"
→ Shows that specific season

/standings alltime
→ All-time leaderboard

Priority

Medium-High - Current forever-accumulating standings get confusing after many weeks.

Related

• Feature: Configurable scoring system #50 (Configurable scoring) - Scoring rules might be per-season