# Complete Optimization Problem and Solution: riding_club

## 1. Problem Context and Goals

### Context  
The riding club operates with the goal of maximizing the total points earned across all clubs in a season. This is achieved by optimally assigning players to coaches based on two key factors: the votes received by each player and the rank of each coach. The assignment of players to coaches is a binary decision, meaning each player is either assigned to a specific coach or not. 

The club has established a business configuration that limits the number of players each coach can handle to a maximum of four. This ensures that coaches can provide adequate attention and support to their assigned players. The votes received by players and the ranks of coaches are used as coefficients in the optimization model to determine the total points earned. 

The optimization problem is designed to be linear, ensuring computational efficiency and scalability. This means that all relationships and constraints are expressed in a straightforward, additive manner without any complex interactions such as variable products or divisions.

### Goals  
The primary goal of the optimization is to maximize the total points earned by all clubs. This is achieved by strategically assigning players to coaches in a way that leverages the coach's rank and the player's votes. The success of the optimization is measured by the total points calculated, which is directly influenced by the combination of player votes and coach ranks for each assignment. 

The optimization process ensures that each player is assigned to exactly one coach and that no coach exceeds their maximum capacity of four players. The goal is to find the optimal assignment that maximizes the overall points while adhering to these constraints.

## 2. Constraints    

The optimization problem is subject to two main constraints:

1. **Single Assignment Constraint**: Each player must be assigned to exactly one coach. This ensures that no player is left without a coach or assigned to multiple coaches simultaneously.

2. **Coach Capacity Constraint**: Each coach can handle a maximum of four players. This ensures that coaches are not overwhelmed and can provide adequate attention to each player assigned to them.

These constraints are designed to be linear, meaning they are expressed in a straightforward, additive manner without any complex interactions. This ensures that the optimization problem remains computationally efficient and scalable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization requirements and updating business configuration logic to handle scalar parameters and formulas.

CREATE TABLE player (
  Player_ID INTEGER,
  Votes INTEGER
);

CREATE TABLE coach (
  Coach_ID INTEGER,
  Rank INTEGER
);

CREATE TABLE assignment (
  Player_ID INTEGER,
  Coach_ID INTEGER,
  Assignment BOOLEAN
);
```

### Data Dictionary  
- **Player Table**: Represents the players in the riding club. Each player has a unique identifier (`Player_ID`) and a number of votes (`Votes`) they have received. The votes are used as coefficients in the objective function to calculate the total points.
  
- **Coach Table**: Represents the coaches in the riding club. Each coach has a unique identifier (`Coach_ID`) and a rank (`Rank`). The rank is used as a coefficient in the objective function to calculate the total points.
  
- **Assignment Table**: Represents the assignment of players to coaches. Each record indicates whether a specific player (`Player_ID`) is assigned to a specific coach (`Coach_ID`). The assignment is a binary decision variable in the optimization model.


### Retrieved Values

**Query 1: This data is essential for calculating the total points based on player votes.**

```sql
SELECT Player_ID, Votes FROM player;
```

**Results (CSV format):**
```csv
Player_ID,Votes
1,12
2,18
3,9
```

**Query 2: This data is essential for calculating the total points based on coach ranks.**

```sql
SELECT Coach_ID, Rank FROM coach;
```

**Results (CSV format):**
```csv
Coach_ID,Rank
1,3
2,2
3,1
```

**Query 3: This data is crucial for understanding the current state of assignments and ensuring constraints are met.**

```sql
SELECT Player_ID, Coach_ID, Assignment FROM assignment WHERE Assignment = TRUE;
```

**Results (CSV format):**
```csv
Player_ID,Coach_ID,Assignment
2,2,1
```

**Query 4: This data is important for enforcing the coach capacity constraint (max 4 players per coach).**

```sql
SELECT Coach_ID, COUNT(Player_ID) AS Player_Count FROM assignment WHERE Assignment = TRUE GROUP BY Coach_ID;
```

**Results (CSV format):**
```csv
Coach_ID,Player_Count
2,1
```

**Query 5: This data is useful for identifying potential new assignments that could improve the total points.**

```sql
SELECT p.Player_ID, c.Coach_ID FROM player p CROSS JOIN coach c LEFT JOIN assignment a ON p.Player_ID = a.Player_ID AND c.Coach_ID = a.Coach_ID WHERE a.Assignment IS NULL OR a.Assignment = FALSE;
```

**Results (CSV format):**
```csv
Player_ID,Coach_ID
1,1
1,2
1,3
2,1
2,3
3,1
3,2
3,3
```

**Query 6: This data helps in evaluating the impact of assigning a player to a coach on the total points.**

```sql
SELECT p.Player_ID, c.Coach_ID, (p.Votes * c.Rank) AS Potential_Points FROM player p CROSS JOIN coach c LEFT JOIN assignment a ON p.Player_ID = a.Player_ID AND c.Coach_ID = a.Coach_ID WHERE a.Assignment IS NULL OR a.Assignment = FALSE;
```

**Results (CSV format):**
```csv
Player_ID,Coach_ID,Potential_Points
1,1,36
1,2,24
1,3,12
2,1,54
2,3,18
3,1,27
3,2,18
3,3,9
```

**Query 7: This data provides the baseline total points that the optimization aims to improve.**

```sql
SELECT SUM(p.Votes * c.Rank) AS Total_Points FROM assignment a JOIN player p ON a.Player_ID = p.Player_ID JOIN coach c ON a.Coach_ID = c.Coach_ID WHERE a.Assignment = TRUE;
```

**Results (CSV format):**
```csv
Total_Points
36
```

**Query 8: This data is important for ensuring the single assignment constraint is met.**

```sql
SELECT p.Player_ID FROM player p LEFT JOIN assignment a ON p.Player_ID = a.Player_ID WHERE a.Assignment IS NULL OR a.Assignment = FALSE;
```

**Results (CSV format):**
```csv
Player_ID
1
3
```

**Query 9: This data is useful for identifying coaches who can still take on more players.**

```sql
SELECT Coach_ID, COUNT(Player_ID) AS Player_Count FROM assignment WHERE Assignment = TRUE GROUP BY Coach_ID ) AS assigned_counts ON c.Coach_ID = assigned_counts.Coach_ID WHERE assigned_counts.Player_Count IS NULL OR assigned_counts.Player_Count < 4;
```

**Error:** Execution failed on sql 'SELECT Coach_ID, COUNT(Player_ID) AS Player_Count FROM assignment WHERE Assignment = TRUE GROUP BY Coach_ID ) AS assigned_counts ON c.Coach_ID = assigned_counts.Coach_ID WHERE assigned_counts.Player_Count IS NULL OR assigned_counts.Player_Count < 4;': near ")": syntax error

