# Complete Optimization Problem and Solution: museum_visit

## 1. Problem Context and Goals

### Context  
The museum network aims to optimize the allocation of its staff across various museums to enhance visitor satisfaction while controlling operational costs. The decision involves determining the number of staff members to allocate to each museum, where the number of staff is an integer. The operational parameters are designed to align with a linear objective: maximizing total visitor satisfaction, which is directly proportional to the number of staff allocated, while minimizing the total operational cost, which is also a linear function of the staff allocation.

The business configuration includes several key parameters:
- The total number of staff available for allocation across all museums, which serves as a constraint in the optimization model.
- The minimum number of staff required for each museum to ensure effective operation, also used as a constraint.
- The maximum staff capacity for each museum to prevent overstaffing and ensure operational efficiency.

These parameters are crucial for maintaining a balance between resource allocation and operational efficiency. The current operational information reflects these constraints and objectives, ensuring that the decision-making process is precise and leads to a linear formulation. The resource limitations are clearly defined to match the expected linear constraints, avoiding any scenarios that would require nonlinear relationships. The specific operational parameters are mapped to the expected coefficient sources, ensuring consistency and clarity in the optimization process.

### Goals  
The primary goal of this optimization problem is to maximize the total visitor satisfaction across all museums. This is achieved by strategically allocating staff to each museum, where the satisfaction is assumed to increase linearly with the number of staff members. The optimization metric is to maximize the total visitor satisfaction, which is calculated as the sum of the satisfaction coefficients multiplied by the staff allocated to each museum, minus the sum of the cost coefficients multiplied by the staff allocated. Success in this optimization is measured by the alignment with the expected coefficient sources, ensuring that the linear optimization goal is clearly defined and achievable.

## 2. Constraints    

The optimization problem is subject to several constraints that are directly aligned with linear mathematical forms:
- The total number of staff allocated across all museums must not exceed the total available staff. This ensures that the allocation does not surpass the staffing resources available.
- Each museum must have a number of staff allocated that is at least the minimum required for effective operation and does not exceed the maximum capacity. This constraint ensures that each museum operates efficiently without overstaffing.

These constraints are described in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains within the realm of linear programming.

## 3. Available Data  

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

CREATE TABLE museum (
  Num_of_Staff INTEGER,
  satisfaction_coefficient FLOAT,
  cost_coefficient FLOAT
);

CREATE TABLE ObjectiveCoefficients (
  museum_id INTEGER,
  satisfaction_coefficient FLOAT,
  cost_coefficient FLOAT
);

CREATE TABLE ConstraintBounds (
  museum_id INTEGER,
  minimum_staff_required INTEGER,
  maximum_staff_capacity INTEGER
);
```

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

- **Museum Table**: This table stores information about each museum, including the number of staff allocated and the coefficients related to visitor satisfaction and operational cost. The number of staff allocated is a decision variable, while the satisfaction and cost coefficients are used in the objective function.

- **ObjectiveCoefficients Table**: This table contains the coefficients used in the optimization objectives. Each entry links a museum to its respective satisfaction and cost coefficients, which are crucial for calculating the optimization goal.

- **ConstraintBounds Table**: This table defines the bounds for staff allocation constraints. It specifies the minimum and maximum number of staff required for each museum, ensuring that the allocation adheres to operational requirements.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical staffing needs and operational costs for museums of varying sizes, ensuring a balance between visitor satisfaction and cost efficiency.

-- Realistic data for museum
INSERT INTO museum (Num_of_Staff, satisfaction_coefficient, cost_coefficient) VALUES (8, 1.3, 0.9);
INSERT INTO museum (Num_of_Staff, satisfaction_coefficient, cost_coefficient) VALUES (12, 1.6, 1.1);
INSERT INTO museum (Num_of_Staff, satisfaction_coefficient, cost_coefficient) VALUES (6, 1.2, 0.8);

-- Realistic data for ObjectiveCoefficients
INSERT INTO ObjectiveCoefficients (museum_id, satisfaction_coefficient, cost_coefficient) VALUES (1, 1.3, 0.9);
INSERT INTO ObjectiveCoefficients (museum_id, satisfaction_coefficient, cost_coefficient) VALUES (2, 1.6, 1.1);
INSERT INTO ObjectiveCoefficients (museum_id, satisfaction_coefficient, cost_coefficient) VALUES (3, 1.2, 0.8);

-- Realistic data for ConstraintBounds
INSERT INTO ConstraintBounds (museum_id, minimum_staff_required, maximum_staff_capacity) VALUES (1, 5, 15);
INSERT INTO ConstraintBounds (museum_id, minimum_staff_required, maximum_staff_capacity) VALUES (2, 7, 20);
INSERT INTO ConstraintBounds (museum_id, minimum_staff_required, maximum_staff_capacity) VALUES (3, 4, 10);
```

## 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.