# 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.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as it determines the performance metric to maximize.**

```sql
SELECT player_id, position_id, historical_win_rate FROM player_position;
```

**Results (CSV format):**
```csv
player_id,position_id,historical_win_rate
1,1,0.75
2,2,0.8
3,3,0.85
```

**Query 2: This data is essential for enforcing the team size constraints during optimization.**

```sql
SELECT team_id, min_players, max_players FROM team_constraints;
```

**Results (CSV format):**
```csv
team_id,min_players,max_players
1,5,10
2,6,11
3,7,12
```

**Query 3: This data is necessary to ensure that the total number of assigned players does not exceed the league's capacity.**

```sql
SELECT league_id, capacity FROM league_capacity;
```

**Results (CSV format):**
```csv
league_id,capacity
1,100
```

**Query 4: This data is important for identifying available players for assignment during optimization.**

```sql
SELECT player_id, position_id FROM player_position WHERE player_assigned = FALSE;
```

**Results (CSV format):**
```csv
player_id,position_id
1,1
2,2
3,3
```

**Query 5: This data helps in enforcing the league capacity constraint by tracking the current usage.**

```sql
SELECT COUNT(*) AS total_assigned_players FROM player_position WHERE player_assigned = TRUE;
```

**Results (CSV format):**
```csv
total_assigned_players
0
```

**Query 6: This data is useful for ensuring that each team adheres to its minimum and maximum player constraints.**

```sql
SELECT team_id, COUNT(*) AS assigned_players FROM player_position WHERE player_assigned = TRUE GROUP BY team_id;
```

**Error:** Execution failed on sql 'SELECT team_id, COUNT(*) AS assigned_players FROM player_position WHERE player_assigned = TRUE GROUP BY team_id;': no such column: team_id

**Query 7: This data can be used to prioritize the assignment of high-performing players to maximize total wins.**

```sql
SELECT player_id, position_id, historical_win_rate FROM player_position ORDER BY historical_win_rate DESC;
```

**Results (CSV format):**
```csv
player_id,position_id,historical_win_rate
3,3,0.85
2,2,0.8
1,1,0.75
```

**Query 8: This data can provide insights into the overall performance of players in different positions, aiding in strategic assignment decisions.**

```sql
SELECT position_id, AVG(historical_win_rate) AS avg_win_rate FROM player_position GROUP BY position_id;
```

**Results (CSV format):**
```csv
position_id,avg_win_rate
1,0.75
2,0.8
3,0.85
```

**Query 9: This data helps in understanding the pool of players available for optimization.**

```sql
SELECT COUNT(*) AS total_available_players FROM player_position WHERE player_assigned = FALSE;
```

**Results (CSV format):**
```csv
total_available_players
3
```

**Query 10: This data is useful for ensuring that the total number of players does not exceed the league capacity.**

```sql
SELECT COUNT(*) AS total_players FROM player_position;
```

**Results (CSV format):**
```csv
total_players
3
```

