# Complete Optimization Problem and Solution: decoration_competition

## 1. Problem Context and Goals

### Context  
In a college decoration competition, each college sends members to participate in rounds with different themes. The goal is to maximize the overall ranking of a college by strategically assigning members to rounds based on their strengths in specific themes. The decision to assign a member to a round is represented by a binary indicator, which is either true (assigned) or false (not assigned).  

The ranking points a member achieves in a specific round are determined by their performance in that round. These ranking points are used to calculate the total ranking points for the college, which is the metric to be maximized.  

Operational parameters include:  
- **Member Limit**: Each member can participate in a maximum of 2 rounds to ensure balanced participation and prevent overcommitment.  
- **Round Requirement**: Each round requires exactly 2 participants to ensure healthy competition and engagement.  

The business logic for calculating ranking points is straightforward: the ranking points a member achieves in a specific round are directly used in the objective function to maximize the total ranking points.  

### Goals  
The optimization goal is to maximize the total ranking points of the college by assigning members to rounds in a way that leverages their strengths in specific decoration themes. Success is measured by the sum of the ranking points achieved by all assigned members across all rounds.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Member Participation Limit**: Each member can be assigned to no more than 2 rounds. This ensures that no member is overcommitted and participation is balanced across all members.  
2. **Round Participant Requirement**: Each round must have exactly 2 participants. This ensures that every round has the required number of participants to maintain competition and engagement.  

These constraints are designed to ensure fair and balanced participation while achieving the optimization goal.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for ranking points to address the missing optimization requirement. Updated business configuration logic to include scalar parameters and formulas. Ensured all tables meet the 3-row minimum rule.

CREATE TABLE assignment_indicator (
  Member_ID INTEGER,
  Round_ID INTEGER,
  Assignment_Indicator BOOLEAN
);

CREATE TABLE member_limit (
  Member_ID INTEGER,
  Member_Limit INTEGER
);

CREATE TABLE round_requirement (
  Round_ID INTEGER,
  Round_Requirement INTEGER
);

CREATE TABLE rank_in_round (
  Member_ID INTEGER,
  Round_ID INTEGER,
  Rank_in_Round INTEGER
);
```

### Data Dictionary  
- **assignment_indicator**: Tracks which members are assigned to which rounds.  
  - **Member_ID**: Unique identifier for a member.  
  - **Round_ID**: Unique identifier for a round.  
  - **Assignment_Indicator**: Indicates if the member is assigned to the round (true or false).  

- **member_limit**: Stores the maximum number of rounds each member can participate in.  
  - **Member_ID**: Unique identifier for a member.  
  - **Member_Limit**: The maximum number of rounds the member can participate in.  

- **round_requirement**: Stores the number of participants required for each round.  
  - **Round_ID**: Unique identifier for a round.  
  - **Round_Requirement**: The number of participants required for the round.  

- **rank_in_round**: Stores the ranking points a member achieves in a specific round.  
  - **Member_ID**: Unique identifier for a member.  
  - **Round_ID**: Unique identifier for a round.  
  - **Rank_in_Round**: The ranking points a member achieves in a specific round.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic scenarios in a college decoration competition, considering the strengths of members in specific themes and the constraints on participation.

-- Realistic data for assignment_indicator
INSERT INTO assignment_indicator (Member_ID, Round_ID, Assignment_Indicator) VALUES (1, 1, True);
INSERT INTO assignment_indicator (Member_ID, Round_ID, Assignment_Indicator) VALUES (2, 2, True);
INSERT INTO assignment_indicator (Member_ID, Round_ID, Assignment_Indicator) VALUES (3, 3, True);

-- Realistic data for member_limit
INSERT INTO member_limit (Member_ID, Member_Limit) VALUES (1, 2);
INSERT INTO member_limit (Member_ID, Member_Limit) VALUES (2, 2);
INSERT INTO member_limit (Member_ID, Member_Limit) VALUES (3, 2);

-- Realistic data for round_requirement
INSERT INTO round_requirement (Round_ID, Round_Requirement) VALUES (1, 2);
INSERT INTO round_requirement (Round_ID, Round_Requirement) VALUES (2, 2);
INSERT INTO round_requirement (Round_ID, Round_Requirement) VALUES (3, 2);

-- Realistic data for rank_in_round
INSERT INTO rank_in_round (Member_ID, Round_ID, Rank_in_Round) VALUES (1, 1, 15);
INSERT INTO rank_in_round (Member_ID, Round_ID, Rank_in_Round) VALUES (2, 2, 20);
INSERT INTO rank_in_round (Member_ID, Round_ID, Rank_in_Round) VALUES (3, 3, 25);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{m,r} \) be a binary decision variable where:
  - \( x_{m,r} = 1 \) if member \( m \) is assigned to round \( r \),
  - \( x_{m,r} = 0 \) otherwise.

#### Objective Function
Maximize the total ranking points:
\[
\text{Maximize } Z = \sum_{m} \sum_{r} \text{Rank\_in\_Round}_{m,r} \times x_{m,r}
\]
Where:
- \( \text{Rank\_in\_Round}_{m,r} \) is the ranking points member \( m \) achieves in round \( r \).

#### Constraints
1. **Member Participation Limit**: Each member \( m \) can participate in at most 2 rounds:
\[
\sum_{r} x_{m,r} \leq 2 \quad \forall m
\]
2. **Round Participant Requirement**: Each round \( r \) must have exactly 2 participants:
\[
\sum_{m} x_{m,r} = 2 \quad \forall r
\]
3. **Binary Decision Variables**: Ensure \( x_{m,r} \) is binary:
\[
x_{m,r} \in \{0, 1\} \quad \forall m, r
\]

#### Data Source Verification
- **Objective Function Coefficients**: \( \text{Rank\_in\_Round}_{m,r} \) comes from the `rank_in_round.Rank_in_Round` column.
- **Member Participation Limit**: The constant 2 comes from the `member_limit.Member_Limit` column.
- **Round Participant Requirement**: The constant 2 comes from the `round_requirement.Round_Requirement` column.

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 1

import gurobipy as gp
from gurobipy import GRB

def decoration_competition_optimization():
    # 1. MODEL & DATA SETUP
    model = gp.Model("decoration_competition")
    
    # Data from the database
    members = [1, 2, 3]
    rounds = [1, 2, 3]
    
    # Ranking points for each member in each round
    rank_in_round = {
        (1, 1): 15,
        (2, 2): 20,
        (3, 3): 25
    }
    
    # Member participation limit
    member_limit = {m: 2 for m in members}
    
    # Round participant requirement
    round_requirement = {r: 2 for r in rounds}
    
    # CRITICAL: Validate array lengths before loops
    assert len(members) == len(member_limit), "Member data length mismatch"
    assert len(rounds) == len(round_requirement), "Round data length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables: x[m, r] = 1 if member m is assigned to round r, 0 otherwise
    x = model.addVars(members, rounds, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total ranking points
    model.setObjective(gp.quicksum(rank_in_round[m, r] * x[m, r] for m in members for r in rounds if (m, r) in rank_in_round), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Member Participation Limit: Each member can participate in at most 2 rounds
    for m in members:
        model.addConstr(gp.quicksum(x[m, r] for r in rounds) <= member_limit[m], name=f"member_limit_{m}")
    
    # Round Participant Requirement: Each round must have exactly 2 participants
    for r in rounds:
        model.addConstr(gp.quicksum(x[m, r] for m in members) == round_requirement[r], name=f"round_requirement_{r}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for m in members:
            for r in rounds:
                if x[m, r].x > 0.5:
                    print(f"Member {m} assigned to Round {r}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
decoration_competition_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 60.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Retry Attempt**: 1
**Analysis**: Gurobipy successfully found an optimal solution with a total ranking score of 60.0 in a very short execution time (0.176 seconds). This indicates high reliability and efficiency.

## 6. DOCplex Implementation

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

from docplex.mp.model import Model

def decoration_competition_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="decoration_competition")
    
    # Data from the database
    members = [1, 2, 3]
    rounds = [1, 2, 3]
    
    # Ranking points for each member in each round
    rank_in_round = {
        (1, 1): 15,
        (2, 2): 20,
        (3, 3): 25
    }
    
    # Member participation limit
    member_limit = {m: 2 for m in members}
    
    # Round participant requirement
    round_requirement = {r: 2 for r in rounds}
    
    # 2. VARIABLES
    # Binary decision variable: x[m, r] = 1 if member m is assigned to round r, 0 otherwise
    x = mdl.binary_var_dict(keys=[(m, r) for m in members for r in rounds], name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total ranking points
    objective = mdl.sum(rank_in_round[m, r] * x[m, r] for m in members for r in rounds)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Member Participation Limit: Each member can participate in at most 2 rounds
    for m in members:
        mdl.add_constraint(mdl.sum(x[m, r] for r in rounds) <= member_limit[m], ctname=f"member_limit_{m}")
    
    # Round Participant Requirement: Each round must have exactly 2 participants
    for r in rounds:
        mdl.add_constraint(mdl.sum(x[m, r] for m in members) == round_requirement[r], ctname=f"round_requirement_{r}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for m in members:
            for r in rounds:
                if solution.get_value(x[m, r]) > 0.5:
                    print(f"Member {m} assigned to Round {r}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
decoration_competition_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpup2r6nzm.py", line 61, in <module>
    decoration_competition_optimization()
  File "/tmp/tmpup2r6nzm.py", line 32, in decoration_competition_optimization
    objective = mdl.sum(rank_in_round[m, r] * x[m, r] for m in members for r in rounds)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/model.py", line 3342, in sum
    return self._aggregator.sum(args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/aggregator.py", line 198, in sum
    sum_res = self._sum_with_iter(sum_args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/aggregator.py", line 221, in _sum_with_iter
    for item in args:
  File "/tmp/tmpup2r6nzm.py", line 32, in <genexpr>
    objective = mdl.sum(rank_in_round[m, r] * x[m, r] for m in members for r in rounds)
KeyError: (1, 2)

**Analysis**: DOCplex encountered a KeyError during execution, likely due to missing or incorrect data in the `rank_in_round` dictionary. This suggests a potential issue with data preparation or model setup.

## 7. Pyomo Implementation

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

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

def decoration_competition_optimization():
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    members = [1, 2, 3]
    rounds = [1, 2, 3]
    
    # Ranking points data
    rank_in_round_data = {
        (1, 1): 15,
        (2, 2): 20,
        (3, 3): 25
    }
    
    # Validate data consistency
    assert len(members) == 3, "Members data length mismatch"
    assert len(rounds) == 3, "Rounds data length mismatch"
    assert len(rank_in_round_data) == 3, "Rank_in_Round data length mismatch"
    
    # 3. SETS
    model.Members = pyo.Set(initialize=members)
    model.Rounds = pyo.Set(initialize=rounds)
    
    # 4. PARAMETERS
    model.Rank_in_Round = pyo.Param(model.Members, model.Rounds, initialize=rank_in_round_data, default=0)
    
    # 5. VARIABLES
    model.Assignment = pyo.Var(model.Members, model.Rounds, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.Rank_in_Round[m, r] * model.Assignment[m, r] for m in model.Members for r in model.Rounds)
    model.Objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Member Participation Limit
    def member_limit_rule(model, m):
        return sum(model.Assignment[m, r] for r in model.Rounds) <= 2
    model.MemberLimit = pyo.Constraint(model.Members, rule=member_limit_rule)
    
    # Round Participant Requirement
    def round_requirement_rule(model, r):
        return sum(model.Assignment[m, r] for m in model.Members) == 2
    model.RoundRequirement = pyo.Constraint(model.Rounds, rule=round_requirement_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
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print(f"Optimal value: {pyo.value(model.Objective)}")
        
        # Extract assignment values
        print("\nAssignment values:")
        for m in model.Members:
            for r in model.Rounds:
                if pyo.value(model.Assignment[m, r]) > 0.5:
                    print(f"Member {m} assigned to Round {r}")
                    
    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__":
    decoration_competition_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 60.0
**Execution Time**: 0.86 seconds
**Reliability**: medium
**Retry Attempt**: 1
**Analysis**: Pyomo also found an optimal solution with a total ranking score of 60.0, but it took significantly longer (0.865 seconds) compared to Gurobipy. While reliable, it is less efficient.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 60.00 | 0.18s | N/A | 1 |
| Docplex | ERROR | N/A | 1.41s | N/A | 1 |
| Pyomo | OPTIMAL | 60.00 | 0.86s | N/A | 1 |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, pyomo
**Majority Vote Optimal Value**: 60.0
**Solver Retry Summary**: gurobipy: 1 attempts, docplex: 1 attempts, pyomo: 1 attempts

### Final Recommendation
**Recommended Optimal Value**: 60.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its high reliability, optimal solution, and significantly faster execution time compared to Pyomo. DOCplex is excluded due to its execution error.

### Business Interpretation
**Overall Strategy**: The optimal solution maximizes the total ranking points to 60.0, ensuring efficient allocation of members to rounds while adhering to participation limits.
**Objective Value Meaning**: The optimal objective value of 60.0 represents the maximum achievable total ranking points across all rounds, ensuring the best possible performance.
**Resource Allocation Summary**: Members should be assigned to rounds such that no member participates in more than 2 rounds, and each round has exactly 2 participants.
**Implementation Recommendations**: 1. Verify and clean the data to avoid errors like those encountered in DOCplex. 2. Use Gurobipy for solving the optimization problem due to its efficiency and reliability. 3. Implement the solution by assigning members to rounds as per the optimal decision variables.