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

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic scenarios in a riding club, considering player votes, coach ranks, and the maximum number of players a coach can handle. The data ensures a balanced distribution of players and coaches, enabling a meaningful optimization problem.

-- Realistic data for player
INSERT INTO player (Player_ID, Votes) VALUES (1, 12);
INSERT INTO player (Player_ID, Votes) VALUES (2, 18);
INSERT INTO player (Player_ID, Votes) VALUES (3, 9);

-- Realistic data for coach
INSERT INTO coach (Coach_ID, Rank) VALUES (1, 3);
INSERT INTO coach (Coach_ID, Rank) VALUES (2, 2);
INSERT INTO coach (Coach_ID, Rank) VALUES (3, 1);

-- Realistic data for assignment
INSERT INTO assignment (Player_ID, Coach_ID, Assignment) VALUES (1, 1, False);
INSERT INTO assignment (Player_ID, Coach_ID, Assignment) VALUES (2, 2, True);
INSERT INTO assignment (Player_ID, Coach_ID, Assignment) VALUES (3, 3, False);
```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Maximize the total points earned:
\[
\text{Maximize } \sum_{p \in P} \sum_{c \in C} (\text{player.Votes}_p \times \text{coach.Rank}_c) \times x_{p,c}
\]
Where:
- \( P \) is the set of players,
- \( C \) is the set of coaches,
- \( \text{player.Votes}_p \) is the number of votes for player \( p \),
- \( \text{coach.Rank}_c \) is the rank of coach \( c \).

#### Constraints
1. **Single Assignment Constraint**: Each player must be assigned to exactly one coach.
\[
\sum_{c \in C} x_{p,c} = 1 \quad \forall p \in P
\]

2. **Coach Capacity Constraint**: Each coach can handle a maximum of four players.
\[
\sum_{p \in P} x_{p,c} \leq 4 \quad \forall c \in C
\]

#### Data Source Verification
- **Coefficients in Objective Function**:
  - \( \text{player.Votes}_p \) comes from `player.Votes`.
  - \( \text{coach.Rank}_c \) comes from `coach.Rank`.

- **Constraints**:
  - The constant `1` in the Single Assignment Constraint ensures each player is assigned to exactly one coach.
  - The constant `4` in the Coach Capacity Constraint ensures no coach exceeds their maximum capacity.

### Numerical Example Based on Provided Data
Given the data:
- Players: \( P = \{1, 2, 3\} \)
  - Votes: \( \text{player.Votes}_1 = 12 \), \( \text{player.Votes}_2 = 18 \), \( \text{player.Votes}_3 = 9 \)
- Coaches: \( C = \{1, 2, 3\} \)
  - Ranks: \( \text{coach.Rank}_1 = 3 \), \( \text{coach.Rank}_2 = 2 \), \( \text{coach.Rank}_3 = 1 \)

The objective function becomes:
\[
\text{Maximize } 12 \times 3 \times x_{1,1} + 12 \times 2 \times x_{1,2} + 12 \times 1 \times x_{1,3} + 18 \times 3 \times x_{2,1} + 18 \times 2 \times x_{2,2} + 18 \times 1 \times x_{2,3} + 9 \times 3 \times x_{3,1} + 9 \times 2 \times x_{3,2} + 9 \times 1 \times x_{3,3}
\]

The constraints are:
1. Single Assignment Constraint:
\[
x_{1,1} + x_{1,2} + x_{1,3} = 1 \\
x_{2,1} + x_{2,2} + x_{2,3} = 1 \\
x_{3,1} + x_{3,2} + x_{3,3} = 1
\]

2. Coach Capacity Constraint:
\[
x_{1,1} + x_{2,1} + x_{3,1} \leq 4 \\
x_{1,2} + x_{2,2} + x_{3,2} \leq 4 \\
x_{1,3} + x_{2,3} + x_{3,3} \leq 4
\]

This is a complete, immediately solvable Mixed-Integer Linear Programming (MIP) model.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Riding Club Optimization Problem
"""

import gurobipy as gp
from gurobipy import GRB

def riding_club_optimization():
    """Optimize player-coach assignments to maximize total points."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("riding_club")
    
    # Data from the problem
    players = [1, 2, 3]
    coaches = [1, 2, 3]
    
    player_votes = {1: 12, 2: 18, 3: 9}
    coach_ranks = {1: 3, 2: 2, 3: 1}
    
    # CRITICAL: Validate array lengths before loops
    assert len(players) == len(player_votes), "Player data length mismatch"
    assert len(coaches) == len(coach_ranks), "Coach data length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables: x[p, c] = 1 if player p is assigned to coach c
    x = model.addVars(players, coaches, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total points: sum(player_votes[p] * coach_ranks[c] * x[p, c])
    model.setObjective(
        gp.quicksum(player_votes[p] * coach_ranks[c] * x[p, c] for p in players for c in coaches),
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Single Assignment Constraint: Each player must be assigned to exactly one coach
    for p in players:
        model.addConstr(
            gp.quicksum(x[p, c] for c in coaches) == 1,
            name=f"single_assignment_{p}"
        )
    
    # Coach Capacity Constraint: Each coach can handle a maximum of four players
    for c in coaches:
        model.addConstr(
            gp.quicksum(x[p, c] for p in players) <= 4,
            name=f"coach_capacity_{c}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for p in players:
            for c in coaches:
                if x[p, c].x > 0.5:  # Check if assignment is active
                    print(f"Player {p} is assigned to Coach {c}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    riding_club_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 117.0
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with the lowest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex implementation for the riding club optimization problem
"""

from docplex.mp.model import Model

def riding_club_optimization():
    """Optimize player assignments to coaches to maximize total points"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="riding_club_optimization")
    
    # Player data
    players = [1, 2, 3]
    player_votes = {1: 12, 2: 18, 3: 9}
    
    # Coach data
    coaches = [1, 2, 3]
    coach_ranks = {1: 3, 2: 2, 3: 1}
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(players) == len(player_votes), "Player data length mismatch"
    assert len(coaches) == len(coach_ranks), "Coach data length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables: x[p, c] = 1 if player p is assigned to coach c
    x = {(p, c): mdl.binary_var(name=f"x_{p}_{c}") for p in players for c in coaches}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total points: sum(player_votes[p] * coach_ranks[c] * x[p, c])
    objective = mdl.sum(player_votes[p] * coach_ranks[c] * x[p, c] for p in players for c in coaches)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Single Assignment Constraint: Each player must be assigned to exactly one coach
    for p in players:
        mdl.add_constraint(mdl.sum(x[p, c] for c in coaches) == 1, ctname=f"single_assignment_{p}")
    
    # Coach Capacity Constraint: Each coach can handle a maximum of four players
    for c in coaches:
        mdl.add_constraint(mdl.sum(x[p, c] for p in players) <= 4, ctname=f"coach_capacity_{c}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for p in players:
            for c in coaches:
                if solution.get_value(x[p, c]) > 0.5:
                    print(f"Player {p} assigned to Coach {c}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    riding_club_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 117.0
**Execution Time**: 1.05 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution but took longer compared to Gurobipy.

## 7. Pyomo Implementation

```python
#!/usr/bin/env python3
"""
Pyomo 6.9.2 Implementation for Riding Club Optimization Problem
"""

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

def riding_club_optimization():
    """Optimization model for maximizing total points in a riding club."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Players data
    players = {1: 12, 2: 18, 3: 9}  # Player_ID: Votes
    # Coaches data
    coaches = {1: 3, 2: 2, 3: 1}    # Coach_ID: Rank
    
    # CRITICAL: Validate array lengths before indexing
    assert len(players) > 0 and len(coaches) > 0, "Players or coaches data is empty"
    
    # 3. SETS (Pyomo way to define indices)
    model.P = pyo.Set(initialize=players.keys())  # Set of players
    model.C = pyo.Set(initialize=coaches.keys())  # Set of coaches
    
    # 4. PARAMETERS (data containers)
    model.player_votes = pyo.Param(model.P, initialize=players)
    model.coach_rank = pyo.Param(model.C, initialize=coaches)
    
    # 5. VARIABLES
    # Binary decision variable: x[p, c] = 1 if player p is assigned to coach c
    model.x = pyo.Var(model.P, model.C, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.player_votes[p] * model.coach_rank[c] * model.x[p, c] for p in model.P for c in model.C)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Single Assignment Constraint: Each player must be assigned to exactly one coach
    def single_assignment_rule(model, p):
        return sum(model.x[p, c] for c in model.C) == 1
    model.single_assignment = pyo.Constraint(model.P, rule=single_assignment_rule)
    
    # Coach Capacity Constraint: Each coach can handle a maximum of four players
    def coach_capacity_rule(model, c):
        return sum(model.x[p, c] for p in model.P) <= 4
    model.coach_capacity = pyo.Constraint(model.C, rule=coach_capacity_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    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(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nAssignment details:")
        for p in model.P:
            for c in model.C:
                if pyo.value(model.x[p, c]) > 0.5:  # Only print assignments
                    print(f"Player {p} is assigned to Coach {c}")
        
    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
if __name__ == "__main__":
    riding_club_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 117.0
**Execution Time**: 1.02 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with a similar execution time to DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 117.00 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 117.00 | 1.05s | N/A | N/A |
| Pyomo | OPTIMAL | 117.00 | 1.02s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 117.0

### Final Recommendation
**Recommended Optimal Value**: 117.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its significantly faster execution time while still providing the optimal solution.

### Business Interpretation
**Overall Strategy**: The optimal assignment of players to coaches maximizes the total points earned based on player votes and coach ranks.
**Objective Value Meaning**: The optimal objective value of 117 represents the maximum total points achievable given the constraints.
**Resource Allocation Summary**: Players should be assigned to coaches in a way that maximizes the total points, ensuring no coach exceeds their capacity of four players.
**Implementation Recommendations**: Implement the assignment as determined by the solver, ensuring each player is assigned to exactly one coach and no coach is overloaded.