# Complete Optimization Problem and Solution: swimming

## 1. Problem Context and Goals

### Context  
The swimming federation is tasked with assigning swimmers to events in a way that maximizes the total performance score across all events. Each swimmer has a performance score for specific events, which is calculated based on their recorded time and the difficulty of the event. The federation has set a minimum performance score threshold of 80, ensuring that only swimmers who meet or exceed this score are considered for event assignments. This threshold aligns with the federation's goal of maintaining high standards and maximizing overall performance.

The assignment process must also respect the capacity constraints of the stadiums hosting the events. Each stadium has a maximum number of swimmers it can accommodate for a given event, and the federation must ensure that the number of swimmers assigned to an event does not exceed this capacity. Additionally, each swimmer can be assigned to at most one event, ensuring fair participation across the competition.

The performance score for each swimmer in an event is calculated using a formula that multiplies the swimmer's recorded time by a difficulty factor specific to the event. This formula ensures that the performance score reflects both the swimmer's speed and the challenge posed by the event. The federation uses these scores to make informed decisions about event assignments, aiming to optimize the overall performance of the team.

### Goals  
The primary goal of the optimization problem is to maximize the total performance score across all events by assigning swimmers to events in the most effective way. This involves selecting the best possible combination of swimmers for each event, considering their individual performance scores and the constraints imposed by stadium capacities and swimmer participation limits. Success is measured by the total performance score achieved, which directly reflects the quality of the assignments made. The federation aims to ensure that every assignment contributes positively to the overall performance, while adhering to the operational constraints.

## 2. Constraints    

The optimization problem must adhere to the following constraints:

1. **Swimmer Assignment Limit**: Each swimmer can be assigned to at most one event. This ensures that no swimmer is overburdened and that participation is fair across the competition.  
2. **Event Capacity Constraint**: The number of swimmers assigned to each event must not exceed the capacity of the stadium hosting the event. This ensures that the physical limitations of the venues are respected and that the events can be conducted smoothly.  

These constraints are designed to ensure that the assignments are both feasible and fair, while still allowing the federation to maximize the total performance score.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for performance scores and event-stadium mappings, modifying the stadium table to better represent capacity constraints, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE swimmer_performance (
  swimmer_id INTEGER,
  event_id INTEGER,
  score FLOAT,
  assigned BOOLEAN
);

CREATE TABLE event_stadium (
  event_id INTEGER,
  stadium_id INTEGER
);

CREATE TABLE stadium (
  stadium_id INTEGER,
  capacity INTEGER,
  event_id INTEGER
);
```

### Data Dictionary  
- **swimmer_performance**:  
  - **swimmer_id**: Unique identifier for a swimmer. Links the swimmer to their performance score in specific events.  
  - **event_id**: Unique identifier for an event. Links the event to the swimmer's performance score.  
  - **score**: Performance score of the swimmer in the event. Used in the objective function to determine the total performance score.  
  - **assigned**: Indicates whether the swimmer is assigned to the event. Represents the decision variable in the optimization problem.  

- **event_stadium**:  
  - **event_id**: Unique identifier for an event. Links the event to the stadium where it will be held.  
  - **stadium_id**: Unique identifier for a stadium. Links the stadium to the event it will host.  

- **stadium**:  
  - **stadium_id**: Unique identifier for a stadium. Links the stadium to its capacity for specific events.  
  - **capacity**: Maximum number of swimmers allowed in the stadium for an event. Used in the capacity constraints.  
  - **event_id**: Unique identifier for an event. Links the stadium to the specific event it will host.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic swimming competition scenarios, considering typical performance scores, stadium capacities, and event-stadium mappings. The data ensures that the optimization problem is meaningful and solvable by providing a mix of performance scores that allow for trade-offs in assignments and capacities that are neither too restrictive nor too lenient.

-- Realistic data for swimmer_performance
INSERT INTO swimmer_performance (swimmer_id, event_id, score, assigned) VALUES (1, 1, 85.5, False);
INSERT INTO swimmer_performance (swimmer_id, event_id, score, assigned) VALUES (2, 1, 78.3, False);
INSERT INTO swimmer_performance (swimmer_id, event_id, score, assigned) VALUES (3, 1, 90.0, False);

-- Realistic data for event_stadium
INSERT INTO event_stadium (event_id, stadium_id) VALUES (1, 1);
INSERT INTO event_stadium (event_id, stadium_id) VALUES (2, 2);
INSERT INTO event_stadium (event_id, stadium_id) VALUES (3, 3);

-- Realistic data for stadium
INSERT INTO stadium (stadium_id, capacity, event_id) VALUES (1, 100, 1);
INSERT INTO stadium (stadium_id, capacity, event_id) VALUES (2, 150, 2);
INSERT INTO stadium (stadium_id, capacity, event_id) VALUES (3, 200, 3);
```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Maximize the total performance score across all events:
\[
\text{Maximize } Z = \sum_{s} \sum_{e} \text{swimmer\_performance.score}_{s,e} \times x_{s,e}
\]

#### Constraints
1. **Swimmer Assignment Limit**: Each swimmer can be assigned to at most one event:
\[
\sum_{e} x_{s,e} \leq 1 \quad \forall s
\]
2. **Event Capacity Constraint**: The number of swimmers assigned to each event must not exceed the capacity of the stadium hosting the event:
\[
\sum_{s} x_{s,e} \leq \text{stadium.capacity}_e \quad \forall e
\]
3. **Performance Score Threshold**: Only swimmers with a performance score of at least 80 can be assigned to an event:
\[
x_{s,e} = 0 \quad \forall s, e \text{ where } \text{swimmer\_performance.score}_{s,e} < 80
\]

#### Data Source Verification
- **swimmer\_performance.score**: From `swimmer_performance.score` table.
- **stadium.capacity**: From `stadium.capacity` table.

### Complete Numerical Model
Using the provided data:

#### Decision Variables
- \( x_{1,1}, x_{2,1}, x_{3,1} \) (for event 1)
- \( x_{1,2}, x_{2,2}, x_{3,2} \) (for event 2)
- \( x_{1,3}, x_{2,3}, x_{3,3} \) (for event 3)

#### Objective Function
\[
\text{Maximize } Z = 85.5x_{1,1} + 78.3x_{2,1} + 90.0x_{3,1}
\]

#### Constraints
1. **Swimmer Assignment Limit**:
\[
x_{1,1} + x_{1,2} + x_{1,3} \leq 1
\]
\[
x_{2,1} + x_{2,2} + x_{2,3} \leq 1
\]
\[
x_{3,1} + x_{3,2} + x_{3,3} \leq 1
\]
2. **Event Capacity Constraint**:
\[
x_{1,1} + x_{2,1} + x_{3,1} \leq 100
\]
\[
x_{1,2} + x_{2,2} + x_{3,2} \leq 150
\]
\[
x_{1,3} + x_{2,3} + x_{3,3} \leq 200
\]
3. **Performance Score Threshold**:
\[
x_{2,1} = 0 \quad \text{(since 78.3 < 80)}
\]

This formulation is a complete, immediately solvable linear programming model.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Swimming Event Assignment Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def swimming_event_assignment():
    """Optimize swimmer assignments to events to maximize total performance score."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("swimming_event_assignment")
    
    # Data from the problem
    swimmers = [1, 2, 3]
    events = [1, 2, 3]
    
    # Performance scores (swimmer_id, event_id): score
    performance_scores = {
        (1, 1): 85.5,
        (2, 1): 78.3,
        (3, 1): 90.0,
        (1, 2): 0.0,  # Placeholder, not used in this example
        (2, 2): 0.0,  # Placeholder, not used in this example
        (3, 2): 0.0,  # Placeholder, not used in this example
        (1, 3): 0.0,  # Placeholder, not used in this example
        (2, 3): 0.0,  # Placeholder, not used in this example
        (3, 3): 0.0   # Placeholder, not used in this example
    }
    
    # Stadium capacities (event_id): capacity
    stadium_capacities = {
        1: 100,
        2: 150,
        3: 200
    }
    
    # CRITICAL: Validate array lengths before loops
    assert len(swimmers) == 3, "Swimmers array length mismatch"
    assert len(events) == 3, "Events array length mismatch"
    assert len(performance_scores) == 9, "Performance scores array length mismatch"
    assert len(stadium_capacities) == 3, "Stadium capacities array length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x[s, e] = 1 if swimmer s is assigned to event e, 0 otherwise
    x = model.addVars(swimmers, events, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total performance score
    model.setObjective(
        gp.quicksum(performance_scores[s, e] * x[s, e] for s in swimmers for e in events),
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Swimmer Assignment Limit
    for s in swimmers:
        model.addConstr(
            gp.quicksum(x[s, e] for e in events) <= 1,
            name=f"swimmer_assignment_limit_{s}"
        )
    
    # Constraint 2: Event Capacity Constraint
    for e in events:
        model.addConstr(
            gp.quicksum(x[s, e] for s in swimmers) <= stadium_capacities[e],
            name=f"event_capacity_constraint_{e}"
        )
    
    # Constraint 3: Performance Score Threshold
    for s in swimmers:
        for e in events:
            if performance_scores[s, e] < 80:
                model.addConstr(
                    x[s, e] == 0,
                    name=f"performance_threshold_{s}_{e}"
                )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for s in swimmers:
            for e in events:
                if x[s, e].x > 0.5:
                    print(f"Swimmer {s} assigned to Event {e} with score {performance_scores[s, e]}")
    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__":
    swimming_event_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 175.5
**Execution Time**: 0.39 seconds
**Reliability**: high
**Analysis**: Gurobipy successfully found the optimal solution with a total performance score of 175.5. The execution time was the fastest among the solvers.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex implementation for swimming event assignment optimization
"""

from docplex.mp.model import Model

def swimming_event_assignment():
    """Optimize swimmer assignments to events to maximize total performance score"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="swimming_event_assignment")
    
    # Data from the problem description
    swimmer_performance = [
        {'swimmer_id': 1, 'event_id': 1, 'score': 85.5},
        {'swimmer_id': 2, 'event_id': 1, 'score': 78.3},
        {'swimmer_id': 3, 'event_id': 1, 'score': 90.0}
    ]
    
    event_stadium = [
        {'event_id': 1, 'stadium_id': 1},
        {'event_id': 2, 'stadium_id': 2},
        {'event_id': 3, 'stadium_id': 3}
    ]
    
    stadium_capacity = [
        {'stadium_id': 1, 'capacity': 100, 'event_id': 1},
        {'stadium_id': 2, 'capacity': 150, 'event_id': 2},
        {'stadium_id': 3, 'capacity': 200, 'event_id': 3}
    ]
    
    # Create dictionaries for easy access
    swimmer_event_score = {(sp['swimmer_id'], sp['event_id']): sp['score'] for sp in swimmer_performance}
    event_stadium_map = {es['event_id']: es['stadium_id'] for es in event_stadium}
    stadium_event_capacity = {se['event_id']: se['capacity'] for se in stadium_capacity}
    
    # Get unique swimmers and events
    swimmers = list(set(sp['swimmer_id'] for sp in swimmer_performance))
    events = list(set(es['event_id'] for es in event_stadium))
    
    # 2. VARIABLES
    x = {(s, e): mdl.binary_var(name=f"x_{s}_{e}") for s in swimmers for e in events}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(swimmer_event_score[(s, e)] * x[(s, e)] for s in swimmers for e in events if (s, e) in swimmer_event_score)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Swimmer Assignment Limit: Each swimmer can be assigned to at most one event
    for s in swimmers:
        mdl.add_constraint(mdl.sum(x[(s, e)] for e in events if (s, e) in swimmer_event_score) <= 1, ctname=f"swimmer_limit_{s}")
    
    # Event Capacity Constraint: Number of swimmers assigned to each event must not exceed the capacity
    for e in events:
        mdl.add_constraint(mdl.sum(x[(s, e)] for s in swimmers if (s, e) in swimmer_event_score) <= stadium_event_capacity[e], ctname=f"event_capacity_{e}")
    
    # Performance Score Threshold: Only swimmers with a score of at least 80 can be assigned
    for s in swimmers:
        for e in events:
            if (s, e) in swimmer_event_score and swimmer_event_score[(s, e)] < 80:
                mdl.add_constraint(x[(s, e)] == 0, ctname=f"score_threshold_{s}_{e}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for s in swimmers:
            for e in events:
                if (s, e) in x and solution.get_value(x[(s, e)]) > 0.5:
                    print(f"Swimmer {s} assigned to Event {e} with score {swimmer_event_score[(s, e)]}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

if __name__ == "__main__":
    swimming_event_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 175.5
**Execution Time**: 1.10 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution with the same total performance score of 175.5, but it took longer to execute compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def swimming_optimization():
    """Optimization model for assigning swimmers to events to maximize total performance score."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Swimmer performance data
    swimmer_performance_data = {
        (1, 1): 85.5,
        (2, 1): 78.3,
        (3, 1): 90.0
    }
    
    # Stadium capacity data
    stadium_capacity_data = {
        1: 100,
        2: 150,
        3: 200
    }
    
    # Validate data consistency
    assert len(swimmer_performance_data) > 0, "Swimmer performance data is empty"
    assert len(stadium_capacity_data) > 0, "Stadium capacity data is empty"
    
    # 3. SETS
    # Swimmers and events
    swimmers = set(s for (s, e) in swimmer_performance_data.keys())
    events = set(e for (s, e) in swimmer_performance_data.keys())
    
    model.swimmers = pyo.Set(initialize=swimmers)
    model.events = pyo.Set(initialize=events)
    
    # 4. PARAMETERS
    # Performance scores
    model.performance_score = pyo.Param(model.swimmers, model.events, initialize=swimmer_performance_data)
    
    # Stadium capacities
    model.stadium_capacity = pyo.Param(model.events, initialize=stadium_capacity_data)
    
    # 5. VARIABLES
    # Binary decision variable: 1 if swimmer s is assigned to event e, 0 otherwise
    model.x = pyo.Var(model.swimmers, model.events, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    # Maximize total performance score
    def obj_rule(model):
        return sum(model.performance_score[s, e] * model.x[s, e] for s in model.swimmers for e in model.events)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    # Swimmer Assignment Limit: Each swimmer can be assigned to at most one event
    def swimmer_assignment_rule(model, s):
        return sum(model.x[s, e] for e in model.events) <= 1
    model.swimmer_assignment = pyo.Constraint(model.swimmers, rule=swimmer_assignment_rule)
    
    # Event Capacity Constraint: Number of swimmers assigned to each event must not exceed stadium capacity
    def event_capacity_rule(model, e):
        return sum(model.x[s, e] for s in model.swimmers) <= model.stadium_capacity[e]
    model.event_capacity = pyo.Constraint(model.events, rule=event_capacity_rule)
    
    # Performance Score Threshold: Only swimmers with a performance score of at least 80 can be assigned
    def performance_threshold_rule(model, s, e):
        if model.performance_score[s, e] < 80:
            return model.x[s, e] == 0
        else:
            return pyo.Constraint.Skip
    model.performance_threshold = pyo.Constraint(model.swimmers, model.events, rule=performance_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("\nVariable values:")
        for s in model.swimmers:
            for e in model.events:
                if pyo.value(model.x[s, e]) > 0:
                    print(f"Swimmer {s} assigned to Event {e} with score {model.performance_score[s, e]}")
        
    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
if __name__ == "__main__":
    swimming_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmph9lsw_g8.py", line 109, in <module>
    swimming_optimization()
  File "/tmp/tmph9lsw_g8.py", line 44, in swimming_optimization
    model.stadium_capacity = pyo.Param(model.events, initialize=stadium_capacity_data)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 571, in __setattr__
    self.add_component(name, val)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 1101, in add_component
    val.construct(data)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 850, in construct
    self._construct_from_rule_using_setitem()
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 772, in _construct_from_rule_using_setitem
    self[index] = rule(block, index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 692, in __setitem__
    index = self._validate_index(index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 870, in _validate_index
    raise KeyError(
KeyError: "Index '2' is not valid for indexed component 'stadium_capacity'"

**Analysis**: Pyomo encountered an error during execution due to an invalid index in the stadium capacity data. This suggests a potential issue with data handling or model setup in Pyomo.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 175.50 | 0.39s | N/A | N/A |
| Docplex | OPTIMAL | 175.50 | 1.10s | N/A | N/A |
| Pyomo | ERROR | N/A | 0.85s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 175.5
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its faster execution time and consistent optimal results. DOCplex also found the same solution but was slower. Pyomo is not recommended due to execution errors.

### Optimal Decision Variables
- **x_1_1** = 1.000
  - *Business Meaning*: Swimmer 1 is assigned to Event 1, contributing a performance score of 85.5.
- **x_3_1** = 1.000
  - *Business Meaning*: Swimmer 3 is assigned to Event 1, contributing a performance score of 90.0.
- **x_1_2** = 0.000
  - *Business Meaning*: Resource allocation for x_1_2
- **x_1_3** = 0.000
  - *Business Meaning*: Resource allocation for x_1_3
- **x_2_1** = 0.000
  - *Business Meaning*: Swimmer 2 is not assigned to any event due to a performance score below the threshold.
- **x_2_2** = 0.000
  - *Business Meaning*: Resource allocation for x_2_2
- **x_2_3** = 0.000
  - *Business Meaning*: Resource allocation for x_2_3
- **x_3_2** = 0.000
  - *Business Meaning*: Resource allocation for x_3_2
- **x_3_3** = 0.000
  - *Business Meaning*: Resource allocation for x_3_3

### Business Interpretation
**Overall Strategy**: The optimal solution assigns Swimmer 1 and Swimmer 3 to Event 1, maximizing the total performance score while adhering to constraints.
**Objective Value Meaning**: The total performance score of 175.5 represents the maximum achievable score given the constraints.
**Resource Allocation Summary**: Swimmers 1 and 3 should be allocated to Event 1, while Swimmer 2 should not be assigned to any event.
**Implementation Recommendations**: Ensure Swimmer 1 and Swimmer 3 are registered for Event 1. Verify that Swimmer 2 meets the performance threshold before future assignments.