# Complete Optimization Problem and Solution: orchestra

## 1. Problem Context and Goals

### Context  
An orchestra management company is tasked with maximizing the total attendance across all performances by optimizing the number of shows each orchestra conducts. The decision revolves around determining the ideal number of performances for each orchestra, ensuring that the total attendance is as high as possible. This decision must consider several operational constraints, including the availability of conductors, the minimum number of performances required for each orchestra to maintain its reputation, and the maximum number of performances each orchestra can feasibly handle. The attendance per performance for each orchestra is a key factor in this decision, as it directly influences the total attendance. The company aims to make these decisions in a way that is both efficient and feasible, ensuring that all constraints are respected while achieving the highest possible attendance.

### Goals  
The primary goal of this optimization problem is to maximize the total attendance across all performances. This is achieved by determining the optimal number of performances for each orchestra, taking into account the attendance per performance for each orchestra. Success is measured by the total attendance figure, which is the sum of the attendance for each performance across all orchestras. The company aims to make decisions that lead to the highest possible total attendance while adhering to all operational constraints.

## 2. Constraints    

The optimization problem must adhere to the following constraints:

1. **Conductor Availability**: The total number of performances across all orchestras must not exceed the total availability of each conductor. This ensures that conductors are not overburdened and can effectively manage their schedules.

2. **Minimum Performances**: Each orchestra must conduct at least a minimum number of performances. This constraint ensures that each orchestra maintains its reputation and meets its contractual obligations.

3. **Maximum Performances**: Each orchestra cannot conduct more than a maximum number of performances. This constraint ensures that orchestras do not overextend themselves and can maintain the quality of their performances.

These constraints are designed to ensure that the optimization problem remains feasible and that all operational limitations are respected.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added attendance table to map attendance data per performance for each orchestra, ensuring the objective function is complete. No tables were deleted or modified as existing tables adequately map to the optimization requirements.

CREATE TABLE attendance (
  Orchestra_ID INTEGER,
  Attendance INTEGER
);

CREATE TABLE conductor_availability (
  Conductor_ID INTEGER,
  Total_Availability INTEGER
);

CREATE TABLE performance_ratings (
  Performance_ID INTEGER,
  Minimum_Rating FLOAT
);

CREATE TABLE performance_limits (
  Orchestra_ID INTEGER,
  Maximum_Performances INTEGER,
  Minimum_Performances INTEGER,
  Number_of_Performances INTEGER
);
```

### Data Dictionary  
- **attendance**: This table contains data on the attendance per performance for each orchestra. The `Orchestra_ID` column uniquely identifies each orchestra, while the `Attendance` column records the number of attendees per performance. This data is crucial for determining the total attendance, which is the primary metric to optimize.

- **conductor_availability**: This table records the total availability of each conductor. The `Conductor_ID` column uniquely identifies each conductor, and the `Total_Availability` column indicates the total number of hours each conductor is available. This data is used to ensure that the total number of performances does not exceed the conductors' availability.

- **performance_ratings**: This table contains the minimum required rating for each performance. The `Performance_ID` column uniquely identifies each performance, and the `Minimum_Rating` column specifies the minimum rating required for each performance. This data ensures that the quality of performances is maintained.

- **performance_limits**: This table defines the maximum and minimum number of performances allowed for each orchestra. The `Orchestra_ID` column uniquely identifies each orchestra, while the `Maximum_Performances` and `Minimum_Performances` columns specify the upper and lower limits on the number of performances. The `Number_of_Performances` column is used as a decision variable in the optimization problem, representing the number of performances each orchestra should conduct.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic business scenarios, considering typical orchestra sizes, conductor availability, and performance constraints. Attendance figures were derived from average concert hall capacities, and performance limits were set to ensure feasibility while maximizing attendance.

-- Realistic data for attendance
INSERT INTO attendance (Orchestra_ID, Attendance) VALUES (1, 500);
INSERT INTO attendance (Orchestra_ID, Attendance) VALUES (2, 600);
INSERT INTO attendance (Orchestra_ID, Attendance) VALUES (3, 450);

-- Realistic data for conductor_availability
INSERT INTO conductor_availability (Conductor_ID, Total_Availability) VALUES (1, 100);
INSERT INTO conductor_availability (Conductor_ID, Total_Availability) VALUES (2, 120);
INSERT INTO conductor_availability (Conductor_ID, Total_Availability) VALUES (3, 80);

-- Realistic data for performance_ratings
INSERT INTO performance_ratings (Performance_ID, Minimum_Rating) VALUES (1, 5.0);
INSERT INTO performance_ratings (Performance_ID, Minimum_Rating) VALUES (2, 6.0);
INSERT INTO performance_ratings (Performance_ID, Minimum_Rating) VALUES (3, 4.5);

-- Realistic data for performance_limits
INSERT INTO performance_limits (Orchestra_ID, Maximum_Performances, Minimum_Performances, Number_of_Performances) VALUES (1, 10, 1, 5);
INSERT INTO performance_limits (Orchestra_ID, Maximum_Performances, Minimum_Performances, Number_of_Performances) VALUES (2, 12, 2, 6);
INSERT INTO performance_limits (Orchestra_ID, Maximum_Performances, Minimum_Performances, Number_of_Performances) VALUES (3, 8, 1, 4);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_i \) be the number of performances conducted by orchestra \( i \), where \( i \in \{1, 2, 3\} \).

#### Objective Function
Maximize the total attendance across all performances:
\[
\text{Maximize } Z = 500x_1 + 600x_2 + 450x_3
\]
**Data Source Verification**: Coefficients 500, 600, and 450 are from `attendance.Attendance` for orchestras 1, 2, and 3, respectively.

#### Constraints
1. **Conductor Availability**: The total number of performances must not exceed the total availability of each conductor. Assuming each performance requires one conductor and all conductors are interchangeable:
\[
x_1 + x_2 + x_3 \leq 100 + 120 + 80
\]
\[
x_1 + x_2 + x_3 \leq 300
\]
**Data Source Verification**: The total availability 300 is the sum of `conductor_availability.Total_Availability` for conductors 1, 2, and 3.

2. **Minimum Performances**: Each orchestra must conduct at least a minimum number of performances:
\[
x_1 \geq 1
\]
\[
x_2 \geq 2
\]
\[
x_3 \geq 1
\]
**Data Source Verification**: Minimum performances 1, 2, and 1 are from `performance_limits.Minimum_Performances` for orchestras 1, 2, and 3, respectively.

3. **Maximum Performances**: Each orchestra cannot conduct more than a maximum number of performances:
\[
x_1 \leq 10
\]
\[
x_2 \leq 12
\]
\[
x_3 \leq 8
\]
**Data Source Verification**: Maximum performances 10, 12, and 8 are from `performance_limits.Maximum_Performances` for orchestras 1, 2, and 3, respectively.

#### Summary of the Linear Programming Model
\[
\text{Maximize } Z = 500x_1 + 600x_2 + 450x_3
\]
Subject to:
\[
x_1 + x_2 + x_3 \leq 300
\]
\[
x_1 \geq 1, \quad x_2 \geq 2, \quad x_3 \geq 1
\]
\[
x_1 \leq 10, \quad x_2 \leq 12, \quad x_3 \leq 8
\]
\[
x_1, x_2, x_3 \geq 0 \quad \text{(Non-negativity)}
\]

This is a complete, immediately solvable linear programming model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Orchestra Optimization Problem
"""

import gurobipy as gp
from gurobipy import GRB

def orchestra_optimization():
    """Optimize the number of performances for each orchestra to maximize total attendance."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("orchestra_optimization")
    
    # Data from the problem
    orchestras = [1, 2, 3]
    attendance = {1: 500, 2: 600, 3: 450}
    min_performances = {1: 1, 2: 2, 3: 1}
    max_performances = {1: 10, 2: 12, 3: 8}
    total_conductor_availability = 300  # Sum of conductor availability
    
    # CRITICAL: Validate array lengths before loops
    assert len(orchestras) == len(attendance) == len(min_performances) == len(max_performances), "Array length mismatch"
    
    # 2. VARIABLES
    x = {i: model.addVar(vtype=GRB.INTEGER, name=f"x_{i}", lb=min_performances[i], ub=max_performances[i]) 
         for i in orchestras}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(attendance[i] * x[i] for i in orchestras), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Conductor Availability Constraint
    model.addConstr(gp.quicksum(x[i] for i in orchestras) <= total_conductor_availability, name="conductor_availability")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in orchestras:
            print(f"x[{i}] = {x[i].x}")
    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__":
    orchestra_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 15800.0
**Execution Time**: 0.17 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with the lowest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex 2.29.245 Implementation for Orchestra Optimization Problem
"""

from docplex.mp.model import Model

def orchestra_optimization():
    """Optimize the number of performances for each orchestra to maximize total attendance."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="orchestra_optimization")
    
    # Data from the problem
    orchestras = [1, 2, 3]
    attendance = {1: 500, 2: 600, 3: 450}
    min_performances = {1: 1, 2: 2, 3: 1}
    max_performances = {1: 10, 2: 12, 3: 8}
    total_conductor_availability = 300  # Sum of conductor availability
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(orchestras) == len(attendance) == len(min_performances) == len(max_performances), "Array length mismatch"
    safe_range = range(min(len(orchestras), len(attendance), len(min_performances), len(max_performances)))  # Safe indexing
    
    # 2. VARIABLES
    # Decision variables: number of performances for each orchestra
    x = {i: mdl.integer_var(name=f"x_{i}", lb=min_performances[i], ub=max_performances[i]) for i in orchestras}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total attendance
    objective = mdl.sum(attendance[i] * x[i] for i in orchestras)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Total number of performances must not exceed conductor availability
    total_performances = mdl.sum(x[i] for i in orchestras)
    mdl.add_constraint(total_performances <= total_conductor_availability, ctname="conductor_availability")
    
    # Constraint 2: Each orchestra must conduct at least the minimum number of performances
    for i in orchestras:
        mdl.add_constraint(x[i] >= min_performances[i], ctname=f"min_performances_{i}")
    
    # Constraint 3: Each orchestra cannot conduct more than the maximum number of performances
    for i in orchestras:
        mdl.add_constraint(x[i] <= max_performances[i], ctname=f"max_performances_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in orchestras:
            value = solution.get_value(x[i])
            print(f"x[{i}] = {value}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

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

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def orchestra_optimization():
    """Optimize the number of performances for each orchestra to maximize total attendance."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    orchestras = [1, 2, 3]
    attendance = {1: 500, 2: 600, 3: 450}
    min_performances = {1: 1, 2: 2, 3: 1}
    max_performances = {1: 10, 2: 12, 3: 8}
    total_conductor_availability = 300
    
    # CRITICAL: Validate array lengths before indexing
    assert len(attendance) == len(min_performances) == len(max_performances) == len(orchestras), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.I = pyo.Set(initialize=orchestras)
    
    # 4. PARAMETERS (data containers)
    model.attendance = pyo.Param(model.I, initialize=attendance)
    model.min_performances = pyo.Param(model.I, initialize=min_performances)
    model.max_performances = pyo.Param(model.I, initialize=max_performances)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.attendance[i] * model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Conductor Availability Constraint
    def conductor_availability_rule(model):
        return sum(model.x[i] for i in model.I) <= total_conductor_availability
    model.conductor_availability_constraint = pyo.Constraint(rule=conductor_availability_rule)
    
    # Minimum Performances Constraint
    def min_performances_rule(model, i):
        return model.x[i] >= model.min_performances[i]
    model.min_performances_constraint = pyo.Constraint(model.I, rule=min_performances_rule)
    
    # Maximum Performances Constraint
    def max_performances_rule(model, i):
        return model.x[i] <= model.max_performances[i]
    model.max_performances_constraint = pyo.Constraint(model.I, rule=max_performances_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    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 i in model.I:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{i}] = {x_val:.3f}")
        
    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__":
    orchestra_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 15800.0
**Execution Time**: 0.93 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution but had the longest execution time among the three solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 15800.00 | 0.17s | N/A | N/A |
| Docplex | OPTIMAL | 15800.00 | 1.03s | N/A | N/A |
| Pyomo | OPTIMAL | 15800.00 | 0.93s | N/A | N/A |

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

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

### Optimal Decision Variables
- **x_1** = 10.000
  - *Business Meaning*: Number of performances by Orchestra 1, optimally set to 10 to maximize attendance.
- **x_2** = 12.000
  - *Business Meaning*: Number of performances by Orchestra 2, optimally set to 12 to maximize attendance.
- **x_3** = 8.000
  - *Business Meaning*: Number of performances by Orchestra 3, optimally set to 8 to maximize attendance.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests maximizing the total attendance by conducting the maximum allowed performances for each orchestra, resulting in a total attendance of 15,800.
**Objective Value Meaning**: The optimal total attendance of 15,800 is achieved by conducting the maximum number of performances allowed for each orchestra.
**Resource Allocation Summary**: Allocate conductors to support 10 performances by Orchestra 1, 12 by Orchestra 2, and 8 by Orchestra 3, utilizing the total conductor availability of 300.
**Implementation Recommendations**: Ensure that the conductors are scheduled efficiently to meet the performance requirements. Monitor attendance to validate the model's assumptions and adjust future performance limits if necessary.