# Complete Optimization Problem and Solution: performance_attendance

## 1. Problem Context and Goals

### Context  
A music ensemble is focused on optimizing the number of pieces each member performs across various performances to maximize overall attendance. The ensemble must make decisions about how many pieces each member should perform in each performance, ensuring that these decisions align with the goal of attracting the largest possible audience. The attendance for each performance is a key factor in this optimization, as it directly influences the overall success of the ensemble's performances.  

The ensemble operates under specific constraints: each member has a maximum number of pieces they can perform, and each performance has a maximum number of pieces that can be included. Additionally, every member must perform at least one piece to ensure fair participation. The attendance for each performance is a known value and is used to weight the importance of each performance in the optimization process.  

The business configuration includes a scalar parameter for attendance, which represents the average attendance across performances. This parameter simplifies the objective function by providing a consistent measure of attendance impact.  

### Goals  
The primary goal of this optimization is to maximize the total attendance across all performances by strategically assigning the number of pieces each member performs. Success is measured by the overall attendance achieved, which is directly influenced by the number of pieces performed by each member in each performance. The optimization ensures that the ensemble's performances are as attractive as possible to the audience while respecting the operational constraints of the members and performances.  

## 2. Constraints  

The optimization must adhere to the following constraints:  
1. **Maximum Pieces per Member**: Each member cannot perform more than a specified maximum number of pieces across all performances. This ensures that no member is overburdened.  
2. **Maximum Pieces per Performance**: Each performance cannot include more than a specified maximum number of pieces. This ensures that performances remain manageable and focused.  
3. **Minimum Pieces per Member**: Each member must perform at least one piece across all performances. This ensures fair participation and engagement from all members.  

These constraints are designed to maintain operational feasibility while achieving the optimization goal.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added attendance_data table to capture attendance per performance, updated business configuration logic with attendance-related scalar parameters, and ensured all mappings align with OR expert requirements.

CREATE TABLE member_constraints (
  member_id INTEGER,
  max_pieces INTEGER
);

CREATE TABLE performance_constraints (
  performance_id INTEGER,
  max_pieces INTEGER
);

CREATE TABLE member_attendance (
  member_id INTEGER,
  performance_id INTEGER,
  num_of_pieces INTEGER
);

CREATE TABLE attendance_data (
  performance_id INTEGER,
  attendance INTEGER
);
```

### Data Dictionary  
- **member_constraints**:  
  - **member_id**: Unique identifier for each member.  
  - **max_pieces**: Maximum number of pieces the member can perform across all performances.  

- **performance_constraints**:  
  - **performance_id**: Unique identifier for each performance.  
  - **max_pieces**: Maximum number of pieces allowed in the performance.  

- **member_attendance**:  
  - **member_id**: Unique identifier for each member.  
  - **performance_id**: Unique identifier for each performance.  
  - **num_of_pieces**: Number of pieces the member performs in the performance.  

- **attendance_data**:  
  - **performance_id**: Unique identifier for each performance.  
  - **attendance**: Attendance for the performance, used to weight the importance of the performance in the optimization.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic scenarios for a music ensemble, considering typical attendance numbers, member capabilities, and performance constraints. The data ensures a balanced distribution of pieces among members and performances, while respecting the optimization constraints.

-- Realistic data for member_constraints
INSERT INTO member_constraints (member_id, max_pieces) VALUES (1, 5);
INSERT INTO member_constraints (member_id, max_pieces) VALUES (2, 4);
INSERT INTO member_constraints (member_id, max_pieces) VALUES (3, 3);

-- Realistic data for performance_constraints
INSERT INTO performance_constraints (performance_id, max_pieces) VALUES (1, 10);
INSERT INTO performance_constraints (performance_id, max_pieces) VALUES (2, 8);
INSERT INTO performance_constraints (performance_id, max_pieces) VALUES (3, 6);

-- Realistic data for member_attendance
INSERT INTO member_attendance (member_id, performance_id, num_of_pieces) VALUES (1, 1, 3);
INSERT INTO member_attendance (member_id, performance_id, num_of_pieces) VALUES (2, 1, 2);
INSERT INTO member_attendance (member_id, performance_id, num_of_pieces) VALUES (3, 1, 1);

-- Realistic data for attendance_data
INSERT INTO attendance_data (performance_id, attendance) VALUES (1, 200);
INSERT INTO attendance_data (performance_id, attendance) VALUES (2, 150);
INSERT INTO attendance_data (performance_id, attendance) VALUES (3, 100);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_{m,p} \) be the number of pieces performed by member \( m \) in performance \( p \), where:
- \( m \in \{1, 2, 3\} \) (members)
- \( p \in \{1, 2, 3\} \) (performances)

#### Objective Function
Maximize the total weighted attendance:
\[
\text{Maximize } Z = \sum_{m=1}^{3} \sum_{p=1}^{3} \text{attendance}_p \cdot x_{m,p}
\]
where \( \text{attendance}_p \) is the attendance for performance \( p \).

#### Constraints
1. **Maximum Pieces per Member**:
\[
\sum_{p=1}^{3} x_{m,p} \leq \text{max\_pieces}_m \quad \forall m \in \{1, 2, 3\}
\]
where \( \text{max\_pieces}_m \) is the maximum number of pieces member \( m \) can perform.

2. **Maximum Pieces per Performance**:
\[
\sum_{m=1}^{3} x_{m,p} \leq \text{max\_pieces}_p \quad \forall p \in \{1, 2, 3\}
\]
where \( \text{max\_pieces}_p \) is the maximum number of pieces allowed in performance \( p \).

3. **Minimum Pieces per Member**:
\[
\sum_{p=1}^{3} x_{m,p} \geq 1 \quad \forall m \in \{1, 2, 3\}
\]

4. **Non-Negativity**:
\[
x_{m,p} \geq 0 \quad \forall m \in \{1, 2, 3\}, \forall p \in \{1, 2, 3\}
\]

#### Data Source Verification
- \( \text{attendance}_p \): From `attendance_data.attendance`
- \( \text{max\_pieces}_m \): From `member_constraints.max_pieces`
- \( \text{max\_pieces}_p \): From `performance_constraints.max_pieces`

#### Numerical Coefficients from Provided Data
- \( \text{attendance}_1 = 200 \), \( \text{attendance}_2 = 150 \), \( \text{attendance}_3 = 100 \)
- \( \text{max\_pieces}_1 = 5 \), \( \text{max\_pieces}_2 = 4 \), \( \text{max\_pieces}_3 = 3 \)
- \( \text{max\_pieces}_1 = 10 \), \( \text{max\_pieces}_2 = 8 \), \( \text{max\_pieces}_3 = 6 \)

This formulation provides a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy Implementation for Performance Attendance Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_performance_attendance():
    """Optimize the number of pieces each member performs to maximize attendance."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("performance_attendance")
    
    # Data from the problem
    members = [1, 2, 3]
    performances = [1, 2, 3]
    
    # Attendance data
    attendance = {1: 200, 2: 150, 3: 100}
    
    # Member constraints
    max_pieces_member = {1: 5, 2: 4, 3: 3}
    
    # Performance constraints
    max_pieces_performance = {1: 10, 2: 8, 3: 6}
    
    # Validate array lengths
    assert len(members) == len(max_pieces_member), "Member data length mismatch"
    assert len(performances) == len(attendance) == len(max_pieces_performance), "Performance data length mismatch"
    
    # 2. VARIABLES
    x = {(m, p): model.addVar(vtype=GRB.INTEGER, name=f"x_{m}_{p}", lb=0) 
         for m in members for p in performances}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(
        gp.quicksum(attendance[p] * x[m, p] for m in members for p in performances), 
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Maximum Pieces per Member
    for m in members:
        model.addConstr(
            gp.quicksum(x[m, p] for p in performances) <= max_pieces_member[m], 
            name=f"max_pieces_member_{m}"
        )
    
    # Maximum Pieces per Performance
    for p in performances:
        model.addConstr(
            gp.quicksum(x[m, p] for m in members) <= max_pieces_performance[p], 
            name=f"max_pieces_performance_{p}"
        )
    
    # Minimum Pieces per Member
    for m in members:
        model.addConstr(
            gp.quicksum(x[m, p] for p in performances) >= 1, 
            name=f"min_pieces_member_{m}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for m in members:
            for p in performances:
                if x[m, p].x > 1e-6:
                    print(f"Member {m} performs {x[m, p].x:.0f} pieces in performance {p}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
if __name__ == "__main__":
    optimize_performance_attendance()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 2300.0
**Execution Time**: 0.18 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 music ensemble performance attendance optimization
"""

from docplex.mp.model import Model

def optimize_performance_attendance():
    """Optimize the number of pieces each member performs to maximize attendance"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="performance_attendance")
    
    # Data from the provided SQL inserts
    members = [1, 2, 3]
    performances = [1, 2, 3]
    
    member_max_pieces = {1: 5, 2: 4, 3: 3}
    performance_max_pieces = {1: 10, 2: 8, 3: 6}
    attendance = {1: 200, 2: 150, 3: 100}
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(members) == len(member_max_pieces) == 3, "Member data length mismatch"
    assert len(performances) == len(performance_max_pieces) == len(attendance) == 3, "Performance data length mismatch"
    
    # 2. VARIABLES
    x = {(m, p): mdl.continuous_var(name=f"x_{m}_{p}", lb=0) for m in members for p in performances}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(attendance[p] * x[(m, p)] for m in members for p in performances)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Maximum Pieces per Member
    for m in members:
        mdl.add_constraint(mdl.sum(x[(m, p)] for p in performances) <= member_max_pieces[m], ctname=f"max_pieces_member_{m}")
    
    # Maximum Pieces per Performance
    for p in performances:
        mdl.add_constraint(mdl.sum(x[(m, p)] for m in members) <= performance_max_pieces[p], ctname=f"max_pieces_performance_{p}")
    
    # Minimum Pieces per Member
    for m in members:
        mdl.add_constraint(mdl.sum(x[(m, p)] for p in performances) >= 1, ctname=f"min_pieces_member_{m}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for m in members:
            for p in performances:
                value = solution.get_value(x[(m, p)])
                if value > 1e-6:
                    print(f"x[{m},{p}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

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

## 7. Pyomo Implementation

```python
#!/usr/bin/env python3
"""
Pyomo 6.9.2 Implementation for Music Ensemble Performance Attendance Optimization
"""

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

def optimize_performance_attendance():
    """Optimize the number of pieces each member performs to maximize attendance."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    members = [1, 2, 3]
    performances = [1, 2, 3]
    
    # Member constraints
    max_pieces_member = {1: 5, 2: 4, 3: 3}
    
    # Performance constraints
    max_pieces_performance = {1: 10, 2: 8, 3: 6}
    
    # Attendance data
    attendance = {1: 200, 2: 150, 3: 100}
    
    # CRITICAL: Validate array lengths before indexing
    assert len(members) == len(max_pieces_member), "Member data length mismatch"
    assert len(performances) == len(max_pieces_performance) == len(attendance), "Performance data length mismatch"
    
    # 3. SETS
    model.M = pyo.Set(initialize=members)  # Members
    model.P = pyo.Set(initialize=performances)  # Performances
    
    # 4. PARAMETERS
    model.max_pieces_member = pyo.Param(model.M, initialize=max_pieces_member)
    model.max_pieces_performance = pyo.Param(model.P, initialize=max_pieces_performance)
    model.attendance = pyo.Param(model.P, initialize=attendance)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.M, model.P, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.attendance[p] * model.x[m, p] for m in model.M for p in model.P)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Maximum Pieces per Member
    def max_pieces_member_rule(model, m):
        return sum(model.x[m, p] for p in model.P) <= model.max_pieces_member[m]
    model.max_pieces_member_constraint = pyo.Constraint(model.M, rule=max_pieces_member_rule)
    
    # Maximum Pieces per Performance
    def max_pieces_performance_rule(model, p):
        return sum(model.x[m, p] for m in model.M) <= model.max_pieces_performance[p]
    model.max_pieces_performance_constraint = pyo.Constraint(model.P, rule=max_pieces_performance_rule)
    
    # Minimum Pieces per Member
    def min_pieces_member_rule(model, m):
        return sum(model.x[m, p] for p in model.P) >= 1
    model.min_pieces_member_constraint = pyo.Constraint(model.M, rule=min_pieces_member_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 m in model.M:
            for p in model.P:
                x_val = pyo.value(model.x[m, p])
                if x_val > 1e-6:  # Only print non-zero values
                    print(f"x[{m}, {p}] = {x_val:.0f}")
        
    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__":
    optimize_performance_attendance()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 2300.0
**Execution Time**: 0.89 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with an execution time between Gurobipy and DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 2300.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 2300.00 | 1.30s | N/A | N/A |
| Pyomo | OPTIMAL | 2300.00 | 0.89s | N/A | N/A |

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

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

### Business Interpretation
**Overall Strategy**: The optimal total weighted attendance is 2300, indicating the best possible allocation of performances to maximize audience engagement.
**Objective Value Meaning**: The optimal value of 2300 represents the maximum possible total weighted attendance across all performances.
**Resource Allocation Summary**: Resources should be allocated to ensure each member performs at least one piece, while not exceeding the maximum pieces per member and per performance.
**Implementation Recommendations**: Schedule performances according to the optimal solution to maximize attendance. Ensure all constraints are adhered to during implementation.