## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_i \) be the number of staff allocated to museum \( i \), where \( i \) corresponds to the museum IDs. These are integer decision variables.

#### Objective Function
Maximize the total visitor satisfaction minus the total operational cost:

\[
\text{Maximize } Z = \sum_{i=1}^{3} (satisfaction\_coefficient_i \times x_i) - \sum_{i=1}^{3} (cost\_coefficient_i \times x_i)
\]

Substituting the coefficients from the data:

\[
Z = (1.3 \times x_1 + 1.6 \times x_2 + 1.2 \times x_3) - (0.9 \times x_1 + 1.1 \times x_2 + 0.8 \times x_3)
\]

Simplifying:

\[
Z = (0.4 \times x_1 + 0.5 \times x_2 + 0.4 \times x_3)
\]

#### Constraints
1. Total staff constraint:
   \[
   x_1 + x_2 + x_3 \leq \text{Total available staff}
   \]

   Assuming the total available staff is a parameter, let's denote it as \( S \).

2. Minimum and maximum staff constraints for each museum:
   \[
   5 \leq x_1 \leq 15
   \]
   \[
   7 \leq x_2 \leq 20
   \]
   \[
   4 \leq x_3 \leq 10
   \]

Data Source Verification:
- The coefficients for the objective function are sourced from the `ObjectiveCoefficients` table: `satisfaction_coefficient` and `cost_coefficient`.
- The constraints for minimum and maximum staff are sourced from the `ConstraintBounds` table: `minimum_staff_required` and `maximum_staff_capacity`.
- The total available staff \( S \) is assumed to be a parameter provided by the business configuration or another data source not explicitly listed in the provided data.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_museum_staff_allocation(total_available_staff):
    """Optimize staff allocation across museums to maximize visitor satisfaction."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("museum_visit")
    
    # Data from the database
    satisfaction_coefficients = [1.3, 1.6, 1.2]
    cost_coefficients = [0.9, 1.1, 0.8]
    minimum_staff_required = [5, 7, 4]
    maximum_staff_capacity = [15, 20, 10]
    
    n_museums = len(satisfaction_coefficients)
    
    # CRITICAL: Validate array lengths before loops
    assert len(cost_coefficients) == len(minimum_staff_required) == len(maximum_staff_capacity) == n_museums, "Array length mismatch"
    
    # 2. VARIABLES
    # Variable dictionaries for staff allocation
    staff_allocation = {i: model.addVar(vtype=GRB.INTEGER, name=f"x_{i}", lb=minimum_staff_required[i], ub=maximum_staff_capacity[i]) 
                        for i in range(n_museums)}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total visitor satisfaction minus total operational cost
    model.setObjective(gp.quicksum((satisfaction_coefficients[i] - cost_coefficients[i]) * staff_allocation[i] for i in range(n_museums)), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total staff constraint
    model.addConstr(gp.quicksum(staff_allocation[i] for i in range(n_museums)) <= total_available_staff, name="total_staff_limit")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_museums):
            print(f"Staff allocated to museum {i+1}: {staff_allocation[i].x:.0f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Example usage
total_available_staff = 30  # Example total available staff
optimize_museum_staff_allocation(total_available_staff)
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 14.0
**Execution Time**: 1.71 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 optimize_museum_staff_allocation(total_available_staff):
    """Optimize staff allocation across museums to maximize visitor satisfaction."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="museum_visit")
    
    # Data from the database
    satisfaction_coefficients = [1.3, 1.6, 1.2]
    cost_coefficients = [0.9, 1.1, 0.8]
    minimum_staff_required = [5, 7, 4]
    maximum_staff_capacity = [15, 20, 10]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(satisfaction_coefficients) == len(cost_coefficients) == len(minimum_staff_required) == len(maximum_staff_capacity) == 3, "Array length mismatch"
    safe_range = range(3)  # Safe indexing for all arrays
    
    # 2. VARIABLES
    # Integer variables for staff allocation
    x = {i: mdl.integer_var(name=f"x_{i}", lb=minimum_staff_required[i], ub=maximum_staff_capacity[i]) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total visitor satisfaction minus total operational cost
    objective = mdl.sum((satisfaction_coefficients[i] - cost_coefficients[i]) * x[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Total staff constraint
    total_staff_usage = mdl.sum(x[i] for i in safe_range)
    mdl.add_constraint(total_staff_usage <= total_available_staff, ctname="total_staff_limit")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(x[i])
            print(f"x[{i}] = {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Example usage
total_available_staff = 30  # Example total available staff
optimize_museum_staff_allocation(total_available_staff)
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 14.0
**Execution Time**: 8.70 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 museum_visit_optimization():
    """Optimize staff allocation across museums to maximize visitor satisfaction."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Assuming total available staff is provided as a parameter
    total_available_staff = 30
    
    # Coefficients and bounds from the database
    satisfaction_coefficients = [1.3, 1.6, 1.2]
    cost_coefficients = [0.9, 1.1, 0.8]
    minimum_staff_required = [5, 7, 4]
    maximum_staff_capacity = [15, 20, 10]
    
    # Validate array lengths
    assert len(satisfaction_coefficients) == len(cost_coefficients) == len(minimum_staff_required) == len(maximum_staff_capacity) == 3, "Array length mismatch"
    
    # 3. SETS
    model.M = pyo.RangeSet(1, 3)  # 3 museums
    
    # 4. PARAMETERS
    model.satisfaction_coefficient = pyo.Param(model.M, initialize={i+1: satisfaction_coefficients[i] for i in range(3)})
    model.cost_coefficient = pyo.Param(model.M, initialize={i+1: cost_coefficients[i] for i in range(3)})
    model.minimum_staff = pyo.Param(model.M, initialize={i+1: minimum_staff_required[i] for i in range(3)})
    model.maximum_staff = pyo.Param(model.M, initialize={i+1: maximum_staff_capacity[i] for i in range(3)})
    
    # 5. VARIABLES
    model.x = pyo.Var(model.M, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum((model.satisfaction_coefficient[i] - model.cost_coefficient[i]) * model.x[i] for i in model.M)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    # Total staff constraint
    def total_staff_rule(model):
        return sum(model.x[i] for i in model.M) <= total_available_staff
    model.total_staff_constraint = pyo.Constraint(rule=total_staff_rule)
    
    # Minimum and maximum staff constraints
    def staff_bounds_rule(model, i):
        return (model.minimum_staff[i], model.x[i], model.maximum_staff[i])
    model.staff_bounds_constraint = pyo.Constraint(model.M, rule=staff_bounds_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):.3f}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.M:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{i}] = {x_val}")
        
    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
museum_visit_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 14.0
**Execution Time**: 5.02 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 | 14.00 | 1.71s | N/A | N/A |
| Docplex | OPTIMAL | 14.00 | 8.70s | N/A | N/A |
| Pyomo | OPTIMAL | 14.00 | 5.02s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 14.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 real-time decision-making scenarios.

### Business Interpretation
**Overall Strategy**: The optimal allocation of staff maximizes visitor satisfaction while minimizing operational costs.
**Objective Value Meaning**: The optimal objective value of 14.0 represents the net benefit of staff allocation in terms of visitor satisfaction minus operational costs.
**Resource Allocation Summary**: Allocate staff efficiently across museums to achieve maximum satisfaction with minimal cost.
**Implementation Recommendations**: Ensure staff allocation adheres to the constraints and monitor visitor satisfaction to validate the model's effectiveness.