# Complete Optimization Problem and Solution: match_season

## 1. Problem Context and Goals

### Context  
In the context of a sports league, the primary objective is to optimize the selection of players for a season to maximize the team's overall performance. The decision-making process involves selecting players, where each player is represented by a binary decision variable indicating whether they are chosen for the team. The operational parameters are designed to align with a linear objective, focusing on maximizing the total performance score. This score is calculated by summing the performance coefficients of the selected players. The business configuration includes several constraints: the maximum number of players allowed in a team, the maximum allowable sum of draft pick numbers, and the minimum number of players required in specific positions such as defenders, midfielders, and forwards. These constraints are essential to ensure a balanced and competitive team composition. The business logic is structured to maintain linear relationships, avoiding any nonlinear scenarios such as variable products or divisions. The configuration parameters are clearly defined to map to the expected coefficient sources, ensuring consistency and clarity in the optimization process.

### Goals  
The primary goal of this optimization problem is to maximize the total performance score of the team. This involves selecting players in such a way that the sum of their performance coefficients is maximized. The success of this optimization is measured by the alignment of the selected players' performance coefficients with the expected sources. The goal is articulated in natural language to emphasize the linear nature of the optimization objective, focusing on maximizing the total performance score through strategic player selection.

## 2. Constraints    

The optimization problem is subject to several constraints that are directly aligned with linear mathematical formulations. These constraints include:

- The total number of selected players must not exceed the maximum number allowed in a team, ensuring compliance with team size regulations.
- The sum of the draft pick numbers for the selected players must remain within the allowable limit, maintaining competitive balance.
- The team must include at least a minimum number of defenders, ensuring a solid defensive line.
- A minimum number of midfielders is required to provide sufficient midfield presence for control and transition.
- The team must also have a minimum number of forwards to maintain offensive pressure.

Each of these constraints is described in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for performance coefficients and constraint bounds, modifying existing tables to include necessary columns, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE Player (
  player_id INTEGER,
  performance_coefficient FLOAT,
  selected BOOLEAN
);

CREATE TABLE PerformanceCoefficients (
  player_id INTEGER,
  coefficient FLOAT
);

CREATE TABLE ConstraintBounds (
  constraint_name STRING,
  value INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **Player Table**: This table stores information about each player, including their unique identifier, performance score, and selection status. The performance score is used in the objective function to maximize team performance, while the selection status serves as a decision variable in the optimization model.

- **PerformanceCoefficients Table**: This table contains the performance coefficients for each player, linking to the Player table through the player ID. These coefficients are crucial for the objective function, as they determine the contribution of each player to the total performance score.

- **ConstraintBounds Table**: This table holds the constraint bounds for team composition, including the maximum number of players, draft pick limits, and minimum position-specific requirements. These bounds are used in the constraint formulation to ensure the team meets all necessary criteria.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical sports league configurations and realistic player performance metrics to ensure a balanced and competitive team composition.

-- Realistic data for Player
INSERT INTO Player (player_id, performance_coefficient, selected) VALUES (1, 0.88, False);
INSERT INTO Player (player_id, performance_coefficient, selected) VALUES (2, 0.92, True);
INSERT INTO Player (player_id, performance_coefficient, selected) VALUES (3, 0.85, False);

-- Realistic data for PerformanceCoefficients
INSERT INTO PerformanceCoefficients (player_id, coefficient) VALUES (1, 0.88);
INSERT INTO PerformanceCoefficients (player_id, coefficient) VALUES (2, 0.92);
INSERT INTO PerformanceCoefficients (player_id, coefficient) VALUES (3, 0.85);

-- Realistic data for ConstraintBounds
INSERT INTO ConstraintBounds (constraint_name, value) VALUES ('max_players_per_team', 25);
INSERT INTO ConstraintBounds (constraint_name, value) VALUES ('max_draft_pick_sum', 100);
INSERT INTO ConstraintBounds (constraint_name, value) VALUES ('min_defenders', 4);
INSERT INTO ConstraintBounds (constraint_name, value) VALUES ('min_midfielders', 3);
INSERT INTO ConstraintBounds (constraint_name, value) VALUES ('min_forwards', 3);
```