# Complete Optimization Problem and Solution: sports_competition

## 1. Problem Context and Goals

### Context  
The sports league is focused on optimizing the allocation of players to clubs to maximize the total points scored across all clubs. This decision-making process involves assigning players to clubs in a way that respects the operational constraints of each club, including the maximum and minimum number of players allowed and the total budget available for player salaries. The league uses a binary decision variable to determine whether a player is assigned to a specific club, ensuring that each assignment is clear and definitive. The points scored by each player in a club are used as the primary metric to evaluate the success of the allocation. The league’s operational parameters are designed to align with a linear optimization approach, avoiding complex scenarios that would require nonlinear relationships, such as variable products or divisions. The business configuration includes scalar parameters like the points scored by players in clubs and the binary decision variable for player assignments, ensuring that the optimization problem remains straightforward and mathematically consistent.

### Goals  
The primary goal of the optimization is to maximize the total points scored by all clubs through the optimal assignment of players. This is achieved by considering the points each player is expected to contribute to a club and ensuring that the assignments respect the operational constraints of each club. Success is measured by the total points accumulated across all clubs, which directly reflects the effectiveness of the player allocation strategy. The optimization process focuses on linear relationships, ensuring that the objective and constraints are straightforward and aligned with the league’s operational requirements.

## 2. Constraints  

The optimization problem is subject to several key constraints that ensure the feasibility and fairness of the player allocations:  
1. **Maximum Players per Club**: Each club has a limit on the number of players it can accommodate. The total number of players assigned to a club must not exceed this limit.  
2. **Minimum Players per Club**: Each club must have a minimum number of players to ensure competitive viability. The total number of players assigned to a club must meet or exceed this requirement.  
3. **Budget Limit**: Each club has a total budget available for player salaries. The combined salaries of all players assigned to a club must not exceed this budget.  

These constraints are designed to align with the league’s operational requirements and ensure that the optimization problem remains linear and mathematically consistent.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization requirements (player_points and player_club_assignments) and updating business configuration logic with scalar parameters and formulas.

CREATE TABLE club_constraints (
  club_id INTEGER,
  max_players INTEGER,
  min_players INTEGER,
  budget FLOAT
);

CREATE TABLE player_salaries (
  player_id INTEGER,
  salary FLOAT
);

CREATE TABLE player_points (
  player_id INTEGER,
  club_id INTEGER,
  points INTEGER
);

CREATE TABLE player_club_assignments (
  player_id INTEGER,
  club_id INTEGER,
  assignment INTEGER
);
```

### Data Dictionary  
- **club_constraints**: This table defines the operational constraints for each club, including the maximum and minimum number of players allowed and the total budget available for player salaries. These constraints are used to ensure that the player allocations are feasible and fair.  
  - **club_id**: Unique identifier for the club.  
  - **max_players**: Maximum number of players allowed per club.  
  - **min_players**: Minimum number of players required per club.  
  - **budget**: Total budget available for player salaries per club.  

- **player_salaries**: This table contains the salary information for each player, which is used to ensure that the total salaries of players assigned to a club do not exceed the club’s budget.  
  - **player_id**: Unique identifier for the player.  
  - **salary**: Salary of the player.  

- **player_points**: This table records the points scored by each player in a club, which are used as the primary metric for evaluating the success of the player allocations.  
  - **player_id**: Unique identifier for the player.  
  - **club_id**: Unique identifier for the club.  
  - **points**: Points scored by the player in the club.  

- **player_club_assignments**: This table represents the binary decision variable indicating whether a player is assigned to a club.  
  - **player_id**: Unique identifier for the player.  
  - **club_id**: Unique identifier for the club.  
  - **assignment**: Binary decision variable indicating whether the player is assigned to the club.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic sports league scenarios, considering player performance, club budgets, and typical team sizes. Data was generated to ensure a balanced and solvable optimization problem.

-- Realistic data for club_constraints
INSERT INTO club_constraints (club_id, max_players, min_players, budget) VALUES (1, 25, 18, 1500000);
INSERT INTO club_constraints (club_id, max_players, min_players, budget) VALUES (2, 30, 20, 2000000);
INSERT INTO club_constraints (club_id, max_players, min_players, budget) VALUES (3, 22, 15, 1000000);

-- Realistic data for player_salaries
INSERT INTO player_salaries (player_id, salary) VALUES (1, 75000);
INSERT INTO player_salaries (player_id, salary) VALUES (2, 100000);
INSERT INTO player_salaries (player_id, salary) VALUES (3, 50000);

-- Realistic data for player_points
INSERT INTO player_points (player_id, club_id, points) VALUES (1, 1, 15);
INSERT INTO player_points (player_id, club_id, points) VALUES (2, 2, 20);
INSERT INTO player_points (player_id, club_id, points) VALUES (3, 3, 10);

-- Realistic data for player_club_assignments
INSERT INTO player_club_assignments (player_id, club_id, assignment) VALUES (1, 1, 1);
INSERT INTO player_club_assignments (player_id, club_id, assignment) VALUES (2, 2, 1);
INSERT INTO player_club_assignments (player_id, club_id, assignment) VALUES (3, 3, 1);
```