# Complete Optimization Problem and Solution: decoration_competition

## 1. Problem Context and Goals

### Context  
In a competitive decoration event, colleges send their members to participate in various themed rounds. The primary decision involves determining which members should participate in which rounds. Each member's participation is represented as a binary decision, indicating whether they are involved in a particular round. The objective is to maximize the total rank points accumulated by the college, which is calculated by summing the rank points earned by each participating member across all rounds. The rank points are derived from a formula that converts a member's rank position into rank points, ensuring a linear relationship. Each member is allowed to participate in a maximum of three rounds, providing a constraint that aligns with the operational parameters. This setup ensures that the problem is structured for linear optimization, focusing on maximizing the college's total rank performance.

### Goals  
The optimization goal is to maximize the total rank points earned by the college's members. This involves selecting the optimal set of member participations across rounds to achieve the highest possible sum of rank points. The success of this optimization is measured by the total rank points, which are calculated by multiplying the rank points associated with each member's participation in a round. The aim is to clearly articulate this linear optimization goal in natural language, focusing on maximizing the college's overall performance in the competition.

## 2. Constraints    

The constraints for this optimization problem are straightforward and align with linear mathematical forms. Each member can participate in a limited number of rounds, specifically up to three, ensuring that the total participation for each member does not exceed this limit. Additionally, the participation decision for each member in a round is binary, meaning a member either participates or does not participate in a given round. These constraints are expressed in business terms that naturally lead to linear mathematical formulations, avoiding any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and objective coefficients, modifying existing tables to improve mapping adequacy, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE DecisionVariables (
  round_id INTEGER,
  member_id INTEGER,
  participation BOOLEAN
);

CREATE TABLE ObjectiveCoefficients (
  round_id INTEGER,
  member_id INTEGER,
  rank_points FLOAT
);
```

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

- **DecisionVariables Table**: This table tracks the participation of members in various rounds. Each entry indicates whether a member is participating in a specific round, serving as a binary decision variable in the optimization process.
  - **round_id**: Represents the identifier for each round, used as an index for decision variables.
  - **member_id**: Represents the identifier for each member, used as an index for decision variables.
  - **participation**: A binary indicator showing if a member is participating in a round, crucial for decision-making in the optimization.

- **ObjectiveCoefficients Table**: This table stores the rank points associated with each member's participation in a round, which are used as coefficients in the optimization objective.
  - **round_id**: Represents the identifier for each round, used as an index for objective coefficients.
  - **member_id**: Represents the identifier for each member, used as an index for objective coefficients.
  - **rank_points**: The rank points earned by a member in a round, serving as a coefficient in the objective function to maximize total rank points.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical competition structures, ensuring a variety of participation and rank points to reflect realistic scenarios.

-- Realistic data for DecisionVariables
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (1, 101, True);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (2, 102, True);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (3, 103, False);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (1, 102, True);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (2, 101, False);

-- Realistic data for ObjectiveCoefficients
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (1, 101, 25.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (2, 102, 30.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (3, 103, 0.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (1, 102, 20.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (2, 101, 0.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_{i,j} \) be a binary decision variable where:
- \( x_{i,j} = 1 \) if member \( j \) participates in round \( i \)
- \( x_{i,j} = 0 \) otherwise

#### Objective Function
Maximize the total rank points earned by the college's members:
\[
\text{Maximize } Z = \sum_{i,j} \text{rank\_points}_{i,j} \times x_{i,j}
\]
where \(\text{rank\_points}_{i,j}\) is the rank points earned by member \( j \) in round \( i \).

Data Source Verification:
- \(\text{rank\_points}_{i,j}\) comes from `ObjectiveCoefficients.rank_points` for each combination of `round_id` and `member_id`.

#### Constraints
1. Each member can participate in a maximum of three rounds:
   \[
   \sum_{i} x_{i,j} \leq 3 \quad \forall j
   \]

2. Participation decision is binary:
   \[
   x_{i,j} \in \{0, 1\} \quad \forall i, j
   \]

Data Source Verification:
- The constraint on the number of rounds a member can participate in is derived from the business rule that each member can participate in up to three rounds.
- The binary nature of \( x_{i,j} \) is inherent in the problem description and aligns with `DecisionVariables.participation`.

This formulation provides a complete linear optimization model that can be solved using standard linear or mixed-integer programming solvers. The objective function and constraints are directly derived from the provided data and business rules, ensuring that the model is both accurate and actionable.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def decoration_competition_optimization():
    """Optimize member participation in decoration competition rounds to maximize rank points."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("decoration_competition")
    
    # Sample data based on the provided schema and realistic data
    rounds = [1, 2, 3]
    members = [101, 102, 103]
    
    # Objective coefficients (rank points) from ObjectiveCoefficients table
    rank_points = {
        (1, 101): 25.0,
        (2, 102): 30.0,
        (3, 103): 0.0,
        (1, 102): 20.0,
        (2, 101): 0.0
    }
    
    # Validate data lengths
    assert all(isinstance(rank_points[key], float) for key in rank_points), "Rank points data type mismatch"
    
    # 2. VARIABLES
    # Binary decision variables for participation
    x = model.addVars(rounds, members, vtype=GRB.BINARY, name="participation")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total rank points
    model.setObjective(gp.quicksum(rank_points.get((i, j), 0) * x[i, j] for i in rounds for j in members), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Constraint: Each member can participate in a maximum of three rounds
    model.addConstrs((gp.quicksum(x[i, j] for i in rounds) <= 3 for j in members), name="max_rounds_per_member")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in rounds:
            for j in members:
                if x[i, j].x > 1e-6:
                    print(f"Member {j} participates in round {i}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
decoration_competition_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 75.0
**Execution Time**: 0.53 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 decoration_competition_optimization():
    """Optimization for decoration competition to maximize rank points"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="decoration_competition")
    
    # Data from the problem context
    rounds = [1, 2, 3]
    members = [101, 102, 103]
    
    # Rank points data
    rank_points_data = {
        (1, 101): 25.0,
        (2, 102): 30.0,
        (3, 103): 0.0,
        (1, 102): 20.0,
        (2, 101): 0.0
    }
    
    # Validate data consistency
    assert all(isinstance(rank_points_data[key], float) for key in rank_points_data), "Rank points data must be float"
    
    # Safe range for indexing
    safe_rounds = range(len(rounds))
    safe_members = range(len(members))
    
    # 2. VARIABLES
    # Binary decision variables for participation
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in rounds for j in members}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total rank points
    objective = mdl.sum(rank_points_data.get((i, j), 0.0) * x[i, j] for i in rounds for j in members)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Each member can participate in a maximum of three rounds
    for j in members:
        mdl.add_constraint(mdl.sum(x[i, j] for i in rounds) <= 3, ctname=f"max_rounds_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in rounds:
            for j in members:
                value = solution.get_value(x[i, j])
                if value > 1e-6:
                    print(f"Member {j} participates in round {i}: {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
decoration_competition_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 75.0
**Execution Time**: 2.09 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy, but still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def decoration_competition_optimization():
    """Optimization for decoration competition"""

    # 1. MODEL CREATION
    model = pyo.ConcreteModel()

    # 2. DATA SETUP
    # Example data based on the provided schema and realistic data
    rounds = [1, 2, 3]
    members = [101, 102, 103]
    
    # Rank points data from ObjectiveCoefficients table
    rank_points_data = {
        (1, 101): 25.0,
        (2, 102): 30.0,
        (3, 103): 0.0,
        (1, 102): 20.0,
        (2, 101): 0.0
    }
    
    # 3. SETS
    model.Rounds = pyo.Set(initialize=rounds)
    model.Members = pyo.Set(initialize=members)

    # 4. PARAMETERS
    model.rank_points = pyo.Param(model.Rounds, model.Members, initialize=rank_points_data, default=0.0)

    # 5. VARIABLES
    model.x = pyo.Var(model.Rounds, model.Members, within=pyo.Binary)

    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.rank_points[i, j] * model.x[i, j] for i in model.Rounds for j in model.Members)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)

    # 7. CONSTRAINTS
    # Constraint: Each member can participate in a maximum of three rounds
    def participation_limit_rule(model, j):
        return sum(model.x[i, j] for i in model.Rounds) <= 3
    model.participation_limit = pyo.Constraint(model.Members, rule=participation_limit_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("\nParticipation decisions:")
        for i in model.Rounds:
            for j in model.Members:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0.5:  # Only print participation decisions
                    print(f"Member {j} participates in Round {i}")
        
    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
decoration_competition_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 75.0
**Execution Time**: 1.29 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with a moderate execution time, demonstrating reliability and consistency with other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 75.00 | 0.53s | N/A | N/A |
| Docplex | OPTIMAL | 75.00 | 2.09s | N/A | N/A |
| Pyomo | OPTIMAL | 75.00 | 1.29s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 75.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its fastest execution time and high reliability, making it suitable for scenarios requiring quick decision-making.

### Business Interpretation
**Overall Strategy**: The optimal allocation of members to rounds maximizes the total rank points, ensuring the best performance for the college.
**Objective Value Meaning**: The optimal objective value of 75.0 indicates the maximum rank points achievable under the given constraints.
**Resource Allocation Summary**: Members should be strategically allocated to rounds to maximize rank points, adhering to the constraint of maximum three participations per member.
**Implementation Recommendations**: Implement the solution by scheduling members according to the optimal decision variables, ensuring compliance with participation constraints.