# Complete Optimization Problem and Solution: riding_club

## 1. Problem Context and Goals

### Context  
The riding club is focused on enhancing its performance by strategically assigning players to coaches. The primary decision involves determining whether a player should be assigned to a specific coach, represented by binary decisions. The club's operational strategy is to maximize the total points earned from these assignments. This is achieved by leveraging compatibility scores between players and coaches, which serve as a measure of potential success in training outcomes. The club has established a minimum compatibility score that must be met for any player-coach pairing to ensure effective training sessions. This threshold is a critical parameter in the decision-making process. The club operates under resource constraints, such as the maximum number of players each coach can manage, ensuring that assignments are feasible and sustainable. The focus is on making precise, linear decisions that align with these operational parameters, avoiding complex relationships that could complicate the optimization process.

### Goals  
The primary goal of the riding club is to maximize the total points accumulated from player-coach assignments. This involves optimizing the allocation of players to coaches based on their compatibility scores. The success of this optimization is measured by the total points, which are directly influenced by the compatibility scores of the assigned pairs. The club aims to achieve the highest possible total points by ensuring that each assignment contributes positively to the overall score, adhering to the linear optimization framework.

## 2. Constraints    

The riding club's optimization process is governed by several key constraints. Firstly, each coach has a limit on the number of players they can effectively manage, ensuring that no coach is overburdened. This constraint ensures that the sum of assignments for each coach does not exceed their capacity. Additionally, for a player to be assigned to a coach, their compatibility score must meet or exceed the minimum threshold set by the club. This ensures that only pairings with a reasonable chance of success are considered, maintaining the quality of training and maximizing the potential for positive outcomes.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a table for assignment decision variables, updating configuration logic for scalar parameters, and ensuring all constraints are correctly implemented based on OR expert mapping analysis.

CREATE TABLE compatibility_scores (
  Player_ID INTEGER,
  Coach_ID INTEGER,
  compatibility_score FLOAT
);

CREATE TABLE coach_constraints (
  Coach_ID INTEGER,
  max_players INTEGER
);

CREATE TABLE assignments (
  Player_ID INTEGER,
  Coach_ID INTEGER,
  assignment BOOLEAN
);


```

### Data Dictionary  
The riding club's data is organized into several key tables, each serving a distinct purpose in the optimization process. The "compatibility_scores" table captures the compatibility between players and coaches, which is crucial for determining the potential success of assignments. Each entry includes a player ID, a coach ID, and a compatibility score, which acts as a coefficient in the optimization objective. The "coach_constraints" table outlines the capacity limitations for each coach, specifying the maximum number of players they can manage. This information is vital for ensuring that assignments do not exceed these limits. Lastly, the "assignments" table records the binary decision variables, indicating whether a player is assigned to a coach. This table is central to the optimization model, as it directly reflects the decisions being made.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical player-coach dynamics in a riding club, ensuring compatibility scores are realistic and coach capacities are reasonable.

-- Realistic data for compatibility_scores
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (1, 1, 0.85);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (1, 2, 0.65);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (2, 1, 0.75);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (2, 3, 0.6);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (3, 2, 0.9);
INSERT INTO compatibility_scores (Player_ID, Coach_ID, compatibility_score) VALUES (3, 3, 0.7);

-- Realistic data for coach_constraints
INSERT INTO coach_constraints (Coach_ID, max_players) VALUES (1, 5);
INSERT INTO coach_constraints (Coach_ID, max_players) VALUES (2, 4);
INSERT INTO coach_constraints (Coach_ID, max_players) VALUES (3, 6);

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


```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Maximize the total compatibility score from player-coach assignments:

\[
\text{Maximize } \sum_{(i,j) \in \text{compatibility\_scores}} \text{compatibility\_score}_{ij} \times x_{ij}
\]

Data Source Verification:
- Coefficients \(\text{compatibility\_score}_{ij}\) are from the `compatibility_scores` table.

#### Constraints

1. **Coach Capacity Constraints:**
   Each coach can manage a limited number of players:

   \[
   \sum_{i} x_{ij} \leq \text{max\_players}_j \quad \forall j
   \]

   Data Source Verification:
   - \(\text{max\_players}_j\) are from the `coach_constraints` table.

2. **Player Assignment Constraints:**
   Each player can be assigned to at most one coach:

   \[
   \sum_{j} x_{ij} \leq 1 \quad \forall i
   \]

3. **Compatibility Threshold Constraints:**
   A player-coach assignment is only valid if the compatibility score meets or exceeds a minimum threshold (assume threshold is \( \text{min\_compatibility\_score} \)):

   \[
   x_{ij} = 0 \quad \text{if } \text{compatibility\_score}_{ij} < \text{min\_compatibility\_score}
   \]

   Data Source Verification:
   - \(\text{compatibility\_score}_{ij}\) are from the `compatibility_scores` table.
   - \(\text{min\_compatibility\_score}\) is a business configuration parameter.

#### Additional Notes
- The binary nature of \( x_{ij} \) ensures that the assignments are either made or not, aligning with the business requirement for binary decisions.
- The constraints ensure that no coach is overburdened and that each player is assigned to at most one coach, maintaining operational feasibility and quality of training.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_riding_club():
    """Optimize player-coach assignments for the riding club"""

    # 1. MODEL & DATA SETUP
    model = gp.Model("riding_club")

    # Data from the database
    compatibility_scores = [
        (1, 1, 0.85),
        (1, 2, 0.65),
        (2, 1, 0.75),
        (2, 3, 0.6),
        (3, 2, 0.9),
        (3, 3, 0.7)
    ]
    
    coach_constraints = {
        1: 5,
        2: 4,
        3: 6
    }
    
    min_compatibility_score = 0.7

    # Extract unique players and coaches
    players = set(player for player, _, _ in compatibility_scores)
    coaches = set(coach for _, coach, _ in compatibility_scores)

    # CRITICAL: Validate data consistency
    assert all(isinstance(score, float) for _, _, score in compatibility_scores), "Invalid score type"

    # 2. VARIABLES
    # Decision variables: x[i, j] = 1 if player i is assigned to coach j
    x = model.addVars(players, coaches, vtype=GRB.BINARY, name="x")

    # 3. OBJECTIVE FUNCTION
    # Maximize total compatibility score
    model.setObjective(
        gp.quicksum(score * x[i, j] for i, j, score in compatibility_scores),
        GRB.MAXIMIZE
    )

    # 4. CONSTRAINTS

    # Coach capacity constraints
    model.addConstrs(
        (gp.quicksum(x[i, j] for i in players) <= coach_constraints[j] for j in coaches),
        name="coach_capacity"
    )

    # Player assignment constraints
    model.addConstrs(
        (gp.quicksum(x[i, j] for j in coaches) <= 1 for i in players),
        name="player_assignment"
    )

    # Compatibility threshold constraints
    model.addConstrs(
        (x[i, j] == 0 for i, j, score in compatibility_scores if score < min_compatibility_score),
        name="compatibility_threshold"
    )

    # 5. SOLVING & RESULTS
    model.optimize()

    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i, j in x.keys():
            if x[i, j].x > 1e-6:
                print(f"Player {i} assigned to Coach {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")

    return model

# Run the optimization
optimize_riding_club()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 2.5
**Execution Time**: 2.44 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the shortest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def riding_club_optimization():
    """Riding Club Optimization using DOCPLEX"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="riding_club")
    
    # Data from the problem statement
    compatibility_scores = [
        (1, 1, 0.85),
        (1, 2, 0.65),
        (2, 1, 0.75),
        (2, 3, 0.6),
        (3, 2, 0.9),
        (3, 3, 0.7)
    ]
    
    coach_constraints = {
        1: 5,
        2: 4,
        3: 6
    }
    
    min_compatibility_score = 0.7
    
    # Extract unique player and coach IDs
    player_ids = set(cs[0] for cs in compatibility_scores)
    coach_ids = set(cs[1] for cs in compatibility_scores)
    
    # 2. VARIABLES
    # Binary decision variables for player-coach assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in player_ids for j in coach_ids}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total compatibility score from player-coach assignments
    objective = mdl.sum(cs[2] * x[cs[0], cs[1]] for cs in compatibility_scores)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Coach Capacity Constraints
    for j in coach_ids:
        mdl.add_constraint(mdl.sum(x[i, j] for i in player_ids if (i, j) in x) <= coach_constraints[j], ctname=f"coach_capacity_{j}")
    
    # Player Assignment Constraints
    for i in player_ids:
        mdl.add_constraint(mdl.sum(x[i, j] for j in coach_ids if (i, j) in x) <= 1, ctname=f"player_assignment_{i}")
    
    # Compatibility Threshold Constraints
    for cs in compatibility_scores:
        i, j, score = cs
        if score < min_compatibility_score:
            mdl.add_constraint(x[i, j] == 0, ctname=f"compatibility_threshold_{i}_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for (i, j) in x:
            value = solution.get_value(x[i, j])
            if value > 1e-6:
                print(f"x[{i},{j}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
riding_club_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 2.5
**Execution Time**: 10.12 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, but with a longer execution time compared to Gurobipy, suggesting it is reliable but less efficient.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def riding_club_optimization():
    """Optimization model for the riding club player-coach assignment problem."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Compatibility scores and coach constraints data
    compatibility_scores = {
        (1, 1): 0.85,
        (1, 2): 0.65,
        (2, 1): 0.75,
        (2, 3): 0.6,
        (3, 2): 0.9,
        (3, 3): 0.7
    }
    coach_constraints = {
        1: 5,
        2: 4,
        3: 6
    }
    min_compatibility_score = 0.6  # Example threshold
    
    # 3. SETS
    model.Players = pyo.Set(initialize=set(i for i, j in compatibility_scores))
    model.Coaches = pyo.Set(initialize=set(j for i, j in compatibility_scores))
    
    # 4. PARAMETERS
    model.compatibility_score = pyo.Param(model.Players, model.Coaches, initialize=compatibility_scores, default=0)
    model.max_players = pyo.Param(model.Coaches, initialize=coach_constraints)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.Players, model.Coaches, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.compatibility_score[i, j] * model.x[i, j] for i in model.Players for j in model.Coaches)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Coach capacity constraints
    def coach_capacity_rule(model, j):
        return sum(model.x[i, j] for i in model.Players) <= model.max_players[j]
    model.coach_capacity_constraint = pyo.Constraint(model.Coaches, rule=coach_capacity_rule)
    
    # Player assignment constraints
    def player_assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.Coaches) <= 1
    model.player_assignment_constraint = pyo.Constraint(model.Players, rule=player_assignment_rule)
    
    # Compatibility threshold constraints
    def compatibility_threshold_rule(model, i, j):
        if model.compatibility_score[i, j] < min_compatibility_score:
            return model.x[i, j] == 0
        return pyo.Constraint.Skip
    model.compatibility_threshold_constraint = pyo.Constraint(model.Players, model.Coaches, rule=compatibility_threshold_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("\nAssignment decisions:")
        for i in model.Players:
            for j in model.Coaches:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0.5:  # Only print assignments that are made
                    print(f"Player {i} assigned to Coach {j}")
        
    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

# Run the optimization
riding_club_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 2.5
**Execution Time**: 4.56 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, demonstrating reliability and reasonable efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 2.50 | 2.44s | N/A | N/A |
| Docplex | OPTIMAL | 2.50 | 10.12s | N/A | N/A |
| Pyomo | OPTIMAL | 2.50 | 4.56s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 2.5
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its high reliability and the shortest execution time, making it the most efficient choice.

### Business Interpretation
**Overall Strategy**: The optimal assignment maximizes compatibility scores, ensuring the best player-coach pairings.
**Objective Value Meaning**: The optimal objective value of 2.5 represents the maximum achievable compatibility score for the player-coach assignments.
**Resource Allocation Summary**: Players should be assigned to coaches based on the optimal solution to maximize compatibility and training effectiveness.
**Implementation Recommendations**: Implement the solution using Gurobipy for efficient execution, and ensure all assignments meet the compatibility threshold.