# Complete Optimization Problem and Solution: school_player

## 1. Problem Context and Goals

### Context  
The optimization problem focuses on allocating players to schools in a way that maximizes the overall performance of school sports teams. Each school has specific enrollment limits, and each team must adhere to position constraints to ensure balanced and effective team compositions. The decision to assign a player to a school is binary, meaning a player is either assigned to a school or not.  

The performance weight for each school and position is calculated based on the school's historical performance and the importance of the position. This weight is used to evaluate the contribution of assigning a player to a school. The business configuration includes:  
- A minimum team size of 11 players, ensuring that each school has enough players to form a complete team.  
- Minimum and maximum player requirements for each position, ensuring that teams have adequate coverage and flexibility. For example, a team must have at least 2 players and no more than 5 players in the "Forward" position.  

The performance weight is derived by multiplying the school's historical performance score by the position weight, ensuring that the objective function reflects both the school's capabilities and the strategic importance of each position.  

### Goals  
The primary goal of this optimization is to maximize the overall performance of all school teams. This is achieved by assigning players to schools in a way that aligns with the schools' historical performance and the players' positions. Success is measured by the total performance weight across all assignments, ensuring that the best possible team compositions are achieved while adhering to enrollment limits and position constraints.  

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. **School Enrollment Limits**: The total number of players assigned to a school cannot exceed the school's enrollment capacity. For example, if a school can enroll up to 25 players, the sum of all player assignments to that school must be less than or equal to 25.  
2. **Position Constraints**: Each school must have a minimum and maximum number of players in each position. For instance, a school must have at least 2 players and no more than 5 players in the "Forward" position.  
3. **Minimum Team Size**: Each school must have at least 11 players assigned to form a complete team.  

These constraints ensure that the team compositions are feasible, balanced, and aligned with operational requirements.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for performance weights, position constraints, and team size requirements. Configuration logic updates include scalar parameters for minimum team size and position constraints, and formulas for performance weight calculations.

CREATE TABLE performance_weight (
  school_id INTEGER,
  position STRING,
  weight FLOAT
);

CREATE TABLE position_constraints (
  position STRING,
  min_players INTEGER,
  max_players INTEGER
);

CREATE TABLE school (
  school_id INTEGER,
  enrollment INTEGER,
  historical_performance FLOAT
);

CREATE TABLE player_assignment (
  player_id INTEGER,
  school_id INTEGER,
  assigned BOOLEAN
);
```

### Data Dictionary  
- **performance_weight**: Stores the performance weights for schools based on positions.  
  - `school_id`: Unique identifier for the school.  
  - `position`: Player position (e.g., Forward, Midfielder, Defender).  
  - `weight`: Performance weight for the school in the specified position, used in the objective function.  
- **position_constraints**: Stores the minimum and maximum number of players allowed in each position.  
  - `position`: Player position.  
  - `min_players`: Minimum number of players required in the position, used in position constraints.  
  - `max_players`: Maximum number of players allowed in the position, used in position constraints.  
- **school**: Stores school information, including enrollment capacity and historical performance.  
  - `school_id`: Unique identifier for the school.  
  - `enrollment`: Maximum number of players a school can enroll, used in enrollment constraints.  
  - `historical_performance`: Historical performance score of the school, used in performance weight calculation.  
- **player_assignment**: Stores the assignment of players to schools.  
  - `player_id`: Unique identifier for the player.  
  - `school_id`: Unique identifier for the school.  
  - `assigned`: Indicates if the player is assigned to the school, used as the decision variable.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic school sports team sizes, historical performance data, and typical position constraints in team sports. The data ensures that the optimization problem is meaningful and solvable by aligning with real-world scenarios.

-- Realistic data for performance_weight
INSERT INTO performance_weight (school_id, position, weight) VALUES (1, 'Forward', 0.9);
INSERT INTO performance_weight (school_id, position, weight) VALUES (1, 'Midfielder', 0.8);
INSERT INTO performance_weight (school_id, position, weight) VALUES (1, 'Defender', 0.7);
INSERT INTO performance_weight (school_id, position, weight) VALUES (2, 'Forward', 0.8);
INSERT INTO performance_weight (school_id, position, weight) VALUES (2, 'Midfielder', 0.9);
INSERT INTO performance_weight (school_id, position, weight) VALUES (2, 'Defender', 0.8);
INSERT INTO performance_weight (school_id, position, weight) VALUES (3, 'Forward', 0.7);
INSERT INTO performance_weight (school_id, position, weight) VALUES (3, 'Midfielder', 0.8);
INSERT INTO performance_weight (school_id, position, weight) VALUES (3, 'Defender', 0.9);

-- Realistic data for position_constraints
INSERT INTO position_constraints (position, min_players, max_players) VALUES ('Forward', 2, 4);
INSERT INTO position_constraints (position, min_players, max_players) VALUES ('Midfielder', 3, 5);
INSERT INTO position_constraints (position, min_players, max_players) VALUES ('Defender', 3, 5);

-- Realistic data for school
INSERT INTO school (school_id, enrollment, historical_performance) VALUES (1, 25, 0.9);
INSERT INTO school (school_id, enrollment, historical_performance) VALUES (2, 20, 0.8);
INSERT INTO school (school_id, enrollment, historical_performance) VALUES (3, 30, 0.7);

-- Realistic data for player_assignment
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (1, 1, False);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (2, 2, True);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (3, 3, False);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (4, 1, True);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (5, 2, False);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (6, 3, True);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (7, 1, False);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (8, 2, True);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (9, 3, False);
INSERT INTO player_assignment (player_id, school_id, assigned) VALUES (10, 1, True);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{p,s} \) be a binary decision variable where:
  - \( x_{p,s} = 1 \) if player \( p \) is assigned to school \( s \),
  - \( x_{p,s} = 0 \) otherwise.

#### Objective Function
Maximize the total performance weight across all player assignments:
\[
\text{Maximize } \sum_{p} \sum_{s} \text{performance\_weight.weight}_{s, \text{position}(p)} \times x_{p,s}
\]
where \( \text{position}(p) \) is the position of player \( p \).

#### Constraints
1. **School Enrollment Limits**:
   \[
   \sum_{p} x_{p,s} \leq \text{school.enrollment}_s \quad \forall s
   \]
2. **Position Constraints**:
   - Minimum players per position:
     \[
     \sum_{p \in \text{position}(p) = pos} x_{p,s} \geq \text{position\_constraints.min\_players}_{pos} \quad \forall s, pos
     \]
   - Maximum players per position:
     \[
     \sum_{p \in \text{position}(p) = pos} x_{p,s} \leq \text{position\_constraints.max\_players}_{pos} \quad \forall s, pos
     \]
3. **Minimum Team Size**:
   \[
   \sum_{p} x_{p,s} \geq 11 \quad \forall s
   \]
4. **Binary Decision Variables**:
   \[
   x_{p,s} \in \{0, 1\} \quad \forall p, s
   \]

#### Data Source Verification
- **performance\_weight.weight**: From `performance_weight.weight` table.
- **school.enrollment**: From `school.enrollment` table.
- **position\_constraints.min\_players**: From `position_constraints.min_players` table.
- **position\_constraints.max\_players**: From `position_constraints.max_players` table.
- **Minimum Team Size**: Business configuration parameter set to 11.

This formulation provides a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation - Retry Attempt 4

import gurobipy as gp
from gurobipy import GRB

def optimize_player_assignment():
    # 1. MODEL & DATA SETUP
    model = gp.Model("school_player_assignment")
    
    # Data from the provided schema
    schools = [1, 2, 3]
    positions = ['Forward', 'Midfielder', 'Defender']
    players = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    
    # Performance weights
    performance_weight = {
        (1, 'Forward'): 0.9,
        (1, 'Midfielder'): 0.8,
        (1, 'Defender'): 0.7,
        (2, 'Forward'): 0.8,
        (2, 'Midfielder'): 0.9,
        (2, 'Defender'): 0.8,
        (3, 'Forward'): 0.7,
        (3, 'Midfielder'): 0.8,
        (3, 'Defender'): 0.9
    }
    
    # Position constraints
    position_constraints = {
        'Forward': {'min_players': 2, 'max_players': 4},
        'Midfielder': {'min_players': 3, 'max_players': 5},
        'Defender': {'min_players': 3, 'max_players': 5}
    }
    
    # School enrollment and historical performance
    school_data = {
        1: {'enrollment': 25, 'historical_performance': 0.9},
        2: {'enrollment': 20, 'historical_performance': 0.8},
        3: {'enrollment': 30, 'historical_performance': 0.7}
    }
    
    # Player positions (assuming each player has a fixed position)
    player_positions = {
        1: 'Forward',
        2: 'Midfielder',
        3: 'Defender',
        4: 'Forward',
        5: 'Midfielder',
        6: 'Defender',
        7: 'Forward',
        8: 'Midfielder',
        9: 'Defender',
        10: 'Forward'
    }
    
    # 2. VARIABLES
    x = model.addVars(players, schools, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(
        gp.quicksum(performance_weight[(s, player_positions[p])] * x[p, s] 
                    for p in players for s in schools),
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # School Enrollment Limits
    for s in schools:
        model.addConstr(
            gp.quicksum(x[p, s] for p in players) <= school_data[s]['enrollment'],
            name=f"enrollment_limit_{s}"
        )
    
    # Position Constraints
    for s in schools:
        for pos in positions:
            players_in_pos = [p for p in players if player_positions[p] == pos]
            model.addConstr(
                gp.quicksum(x[p, s] for p in players_in_pos) >= position_constraints[pos]['min_players'],
                name=f"min_players_{s}_{pos}"
            )
            model.addConstr(
                gp.quicksum(x[p, s] for p in players_in_pos) <= position_constraints[pos]['max_players'],
                name=f"max_players_{s}_{pos}"
            )
    
    # Minimum Team Size
    for s in schools:
        model.addConstr(
            gp.quicksum(x[p, s] for p in players) >= 11,
            name=f"min_team_size_{s}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for p in players:
            for s in schools:
                if x[p, s].x > 0.5:
                    print(f"Player {p} assigned to School {s}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
optimize_player_assignment()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Gurobipy identified the problem as infeasible quickly, indicating a high reliability in detecting infeasibility.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation - Retry Attempt 4

from docplex.mp.model import Model

def school_player_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="school_player_optimization")
    
    # Data from the database schema
    performance_weight = {
        (1, 'Forward'): 0.9,
        (1, 'Midfielder'): 0.8,
        (1, 'Defender'): 0.7,
        (2, 'Forward'): 0.8,
        (2, 'Midfielder'): 0.9,
        (2, 'Defender'): 0.8,
        (3, 'Forward'): 0.7,
        (3, 'Midfielder'): 0.8,
        (3, 'Defender'): 0.9
    }
    
    position_constraints = {
        'Forward': {'min_players': 2, 'max_players': 4},
        'Midfielder': {'min_players': 3, 'max_players': 5},
        'Defender': {'min_players': 3, 'max_players': 5}
    }
    
    school_data = {
        1: {'enrollment': 25, 'historical_performance': 0.9},
        2: {'enrollment': 20, 'historical_performance': 0.8},
        3: {'enrollment': 30, 'historical_performance': 0.7}
    }
    
    player_assignment = [
        {'player_id': 1, 'school_id': 1, 'position': 'Forward'},
        {'player_id': 2, 'school_id': 2, 'position': 'Midfielder'},
        {'player_id': 3, 'school_id': 3, 'position': 'Defender'},
        {'player_id': 4, 'school_id': 1, 'position': 'Midfielder'},
        {'player_id': 5, 'school_id': 2, 'position': 'Forward'},
        {'player_id': 6, 'school_id': 3, 'position': 'Midfielder'},
        {'player_id': 7, 'school_id': 1, 'position': 'Defender'},
        {'player_id': 8, 'school_id': 2, 'position': 'Midfielder'},
        {'player_id': 9, 'school_id': 3, 'position': 'Forward'},
        {'player_id': 10, 'school_id': 1, 'position': 'Forward'}
    ]
    
    # 2. VARIABLES
    # Binary decision variable: x[p, s] = 1 if player p is assigned to school s
    x = {(p['player_id'], s): mdl.binary_var(name=f"x_{p['player_id']}_{s}") 
         for p in player_assignment for s in school_data.keys()}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total performance weight across all player assignments
    objective = mdl.sum(performance_weight[s, p['position']] * x[p['player_id'], s] 
                        for p in player_assignment for s in school_data.keys())
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # School Enrollment Limits
    for s in school_data.keys():
        mdl.add_constraint(mdl.sum(x[p['player_id'], s] for p in player_assignment) <= school_data[s]['enrollment'], 
                           ctname=f"enrollment_limit_{s}")
    
    # Position Constraints
    for s in school_data.keys():
        for pos, constraints in position_constraints.items():
            players_in_position = [p['player_id'] for p in player_assignment if p['position'] == pos]
            mdl.add_constraint(mdl.sum(x[p, s] for p in players_in_position) >= constraints['min_players'], 
                               ctname=f"min_players_{s}_{pos}")
            mdl.add_constraint(mdl.sum(x[p, s] for p in players_in_position) <= constraints['max_players'], 
                               ctname=f"max_players_{s}_{pos}")
    
    # Minimum Team Size
    for s in school_data.keys():
        mdl.add_constraint(mdl.sum(x[p['player_id'], s] for p in player_assignment) >= 11, 
                           ctname=f"min_team_size_{s}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for p in player_assignment:
            for s in school_data.keys():
                if solution.get_value(x[p['player_id'], s]) > 0.5:
                    print(f"Player {p['player_id']} assigned to School {s}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
school_player_optimization()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: DOCplex also confirmed the problem as infeasible, with a slightly longer execution time but consistent results.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation - Retry Attempt 4

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def school_player_optimization():
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Define sets and parameters based on the provided data
    schools = [1, 2, 3]
    positions = ['Forward', 'Midfielder', 'Defender']
    players = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    
    # Performance weights
    performance_weight = {
        (1, 'Forward'): 0.9,
        (1, 'Midfielder'): 0.8,
        (1, 'Defender'): 0.7,
        (2, 'Forward'): 0.8,
        (2, 'Midfielder'): 0.9,
        (2, 'Defender'): 0.8,
        (3, 'Forward'): 0.7,
        (3, 'Midfielder'): 0.8,
        (3, 'Defender'): 0.9
    }
    
    # Position constraints
    position_constraints = {
        'Forward': {'min_players': 2, 'max_players': 4},
        'Midfielder': {'min_players': 3, 'max_players': 5},
        'Defender': {'min_players': 3, 'max_players': 5}
    }
    
    # School enrollment limits
    school_enrollment = {
        1: 25,
        2: 20,
        3: 30
    }
    
    # Player positions (assuming each player has a fixed position)
    player_position = {
        1: 'Forward',
        2: 'Midfielder',
        3: 'Defender',
        4: 'Forward',
        5: 'Midfielder',
        6: 'Defender',
        7: 'Forward',
        8: 'Midfielder',
        9: 'Defender',
        10: 'Forward'
    }
    
    # 3. SETS
    model.S = pyo.Set(initialize=schools)
    model.P = pyo.Set(initialize=players)
    model.Pos = pyo.Set(initialize=positions)
    
    # 4. PARAMETERS
    model.performance_weight = pyo.Param(model.S, model.Pos, initialize=performance_weight)
    model.position_constraints_min = pyo.Param(model.Pos, initialize={pos: position_constraints[pos]['min_players'] for pos in positions})
    model.position_constraints_max = pyo.Param(model.Pos, initialize={pos: position_constraints[pos]['max_players'] for pos in positions})
    model.school_enrollment = pyo.Param(model.S, initialize=school_enrollment)
    model.player_position = pyo.Param(model.P, initialize=player_position)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.P, model.S, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.performance_weight[s, model.player_position[p]] * model.x[p, s] for p in model.P for s in model.S)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    # School Enrollment Limits
    def enrollment_rule(model, s):
        return sum(model.x[p, s] for p in model.P) <= model.school_enrollment[s]
    model.enrollment_constraint = pyo.Constraint(model.S, rule=enrollment_rule)
    
    # Position Constraints
    def position_min_rule(model, s, pos):
        return sum(model.x[p, s] for p in model.P if model.player_position[p] == pos) >= model.position_constraints_min[pos]
    model.position_min_constraint = pyo.Constraint(model.S, model.Pos, rule=position_min_rule)
    
    def position_max_rule(model, s, pos):
        return sum(model.x[p, s] for p in model.P if model.player_position[p] == pos) <= model.position_constraints_max[pos]
    model.position_max_constraint = pyo.Constraint(model.S, model.Pos, rule=position_max_rule)
    
    # Minimum Team Size
    def team_size_rule(model, s):
        return sum(model.x[p, s] for p in model.P) >= 11
    model.team_size_constraint = pyo.Constraint(model.S, rule=team_size_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nPlayer assignments:")
        for p in model.P:
            for s in model.S:
                if pyo.value(model.x[p, s]) > 0.5:
                    print(f"Player {p} assigned to School {s}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Execute the optimization
school_player_optimization()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Pyomo took significantly longer to determine infeasibility, suggesting lower efficiency compared to Gurobipy and DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | INFEASIBLE | N/A | 0.24s | N/A | 4 |
| Docplex | INFEASIBLE | N/A | 1.14s | N/A | 4 |
| Pyomo | INFEASIBLE | N/A | 4.70s | N/A | 4 |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Solver Retry Summary**: gurobipy: 4 attempts, docplex: 4 attempts, pyomo: 4 attempts

### Final Recommendation
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided the fastest and most reliable confirmation of infeasibility, making it the preferred solver for this analysis.

### Business Interpretation
**Overall Strategy**: The problem is infeasible, meaning no valid assignment of players to schools satisfies all constraints. This could indicate a need to revise constraints or input data.
**Objective Value Meaning**: The objective value represents the total performance weight of player assignments. Since the problem is infeasible, no valid assignment exists.
**Resource Allocation Summary**: No feasible allocation of players to schools is possible under the current constraints.
**Implementation Recommendations**: ['Review and relax constraints (e.g., enrollment limits, position constraints, or minimum team size).', 'Verify the accuracy and consistency of input data (e.g., performance weights, school enrollments).', 'Consider increasing the number of available players or adjusting position requirements to make the problem feasible.']