# Complete Optimization Problem and Solution: match_season

## 1. Problem Context and Goals

### Context  
A sports league is focused on optimizing the allocation of players to teams to enhance the overall performance of the league. The performance is measured by the total wins of all teams, which is influenced by the players' historical performance in specific positions. The league must decide whether to assign each player to a particular position on a team, ensuring that the assignments maximize the total wins across all teams.  

The league operates under specific operational parameters:  
- Each player can be assigned to at most one position on one team.  
- Each team must have a minimum number of players to ensure competitiveness and a maximum number to maintain balance.  
- The total number of players assigned across all teams cannot exceed the league's capacity.  

The historical win rate of each player in a specific position is calculated as the ratio of the number of wins they have achieved in that position to the total number of games they have played in that position. This historical win rate serves as the key metric for evaluating player performance and is used to guide the allocation decisions.  

The league has established the following business configuration parameters:  
- **Minimum number of players per team**: 5 players, ensuring each team has enough members to compete effectively.  
- **Maximum number of players per team**: 10 players, preventing overcrowding and maintaining team balance.  
- **League capacity**: 100 players, allowing for a reasonable number of teams and players to operate efficiently.  

### Goals  
The primary goal of this optimization problem is to maximize the total wins across all teams by strategically assigning players to positions based on their historical performance. Success is measured by the total wins achieved, which is directly influenced by the historical win rates of the players in their assigned positions. The optimization process ensures that the assignments adhere to the league's operational constraints, including team size limits and overall league capacity.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Player Assignment Constraint**: Each player can be assigned to at most one position on one team. This ensures that no player is overcommitted or double-counted in the allocation process.  
2. **Team Size Constraints**: Each team must have at least the minimum number of players required to compete effectively and no more than the maximum number allowed to maintain balance. These constraints ensure that teams are neither understaffed nor overcrowded.  
3. **League Capacity Constraint**: The total number of players assigned across all teams cannot exceed the league's capacity. This ensures that the league operates within its resource limits and maintains efficiency.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for player positions, team constraints, and league capacity. Configuration logic updates include scalar parameters for team size constraints and league capacity, and a formula for calculating historical win rates by position.

CREATE TABLE player_position (
  player_id INTEGER,
  position_id INTEGER,
  historical_win_rate FLOAT,
  player_assigned BOOLEAN
);

CREATE TABLE team_constraints (
  team_id INTEGER,
  min_players INTEGER,
  max_players INTEGER
);

CREATE TABLE league_capacity (
  league_id INTEGER,
  capacity INTEGER
);
```

### Data Dictionary  
- **player_position**:  
  - **player_id**: Unique identifier for a player.  
  - **position_id**: Unique identifier for a position.  
  - **historical_win_rate**: The historical win rate of the player in the specified position, calculated as the ratio of wins to total games played in that position. This metric is used to evaluate player performance and guide allocation decisions.  
  - **player_assigned**: Indicates whether the player is assigned to the specified position on a team. This is the key decision variable in the optimization process.  

- **team_constraints**:  
  - **team_id**: Unique identifier for a team.  
  - **min_players**: The minimum number of players required for the team to ensure competitiveness.  
  - **max_players**: The maximum number of players allowed for the team to maintain balance.  

- **league_capacity**:  
  - **league_id**: Unique identifier for the league.  
  - **capacity**: The total number of players the league can accommodate, ensuring efficient operation.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical sports league configurations, ensuring realistic team sizes, player capacities, and historical performance metrics. The data was designed to align with the optimization problem's constraints and objective.

-- Realistic data for player_position
INSERT INTO player_position (player_id, position_id, historical_win_rate, player_assigned) VALUES (1, 1, 0.75, False);
INSERT INTO player_position (player_id, position_id, historical_win_rate, player_assigned) VALUES (2, 2, 0.8, False);
INSERT INTO player_position (player_id, position_id, historical_win_rate, player_assigned) VALUES (3, 3, 0.85, False);

-- Realistic data for team_constraints
INSERT INTO team_constraints (team_id, min_players, max_players) VALUES (1, 5, 10);
INSERT INTO team_constraints (team_id, min_players, max_players) VALUES (2, 6, 11);
INSERT INTO team_constraints (team_id, min_players, max_players) VALUES (3, 7, 12);

-- Realistic data for league_capacity
INSERT INTO league_capacity (league_id, capacity) VALUES (1, 100);
```