# Complete Optimization Problem and Solution: museum_visit

## 1. Problem Context and Goals

### Context  
A museum chain is focused on maximizing its total revenue from ticket sales across all its locations. The key decision involves determining the optimal number of tickets to sell at each museum. This decision must consider two critical operational constraints:  
1. **Ticket Sales Limit**: Each museum has a maximum number of tickets it can sell, ensuring that ticket sales do not exceed the museum's capacity.  
2. **Staff Availability**: Each museum has a maximum number of visitors that its staff can effectively manage, ensuring a safe and enjoyable experience for all guests.  

The business configuration includes:  
- **Total_Ticket_Limit**: The maximum number of tickets that can be sold at each museum, which serves as an upper bound for ticket sales.  
- **Staff_Capacity**: The maximum number of visitors that can be handled by the staff at each museum, which also serves as an upper bound for ticket sales based on staff availability.  

The goal is to make decisions that align with these constraints while maximizing revenue. The revenue generated at each museum is directly proportional to the number of tickets sold, ensuring a linear relationship between the decision variables and the objective.  

### Goals  
The primary optimization goal is to maximize the total revenue generated from ticket sales across all museums. This is achieved by determining the optimal number of tickets to sell at each museum, considering the constraints on ticket sales and staff availability. Success is measured by the total revenue, which is calculated as the sum of the revenue generated at each museum. The revenue at each museum is determined by multiplying the number of tickets sold by the revenue per ticket, ensuring a straightforward and linear relationship.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Ticket Sales Limit**: The number of tickets sold at each museum cannot exceed the maximum number of tickets that the museum is allowed to sell. This ensures that ticket sales remain within the museum's capacity.  
2. **Staff Availability**: The number of tickets sold at each museum cannot exceed the maximum number of visitors that the museum's staff can handle. This ensures that the staff can effectively manage the number of visitors, maintaining a safe and enjoyable environment.  

These constraints are designed to ensure that the optimization problem remains linear, with no variable products or divisions involved.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for missing constraints (Total_Ticket_Limit and Staff_Capacity) and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE museum_constraints (
  Museum_ID INTEGER,
  Total_Ticket_Limit INTEGER,
  Staff_Capacity INTEGER
);

CREATE TABLE visit (
  Museum_ID INTEGER,
  Num_of_Ticket INTEGER,
  Total_spent FLOAT
);
```

### Data Dictionary  
- **museum_constraints**: This table contains the maximum ticket limits and staff capacity for each museum.  
  - **Museum_ID**: A unique identifier for each museum.  
  - **Total_Ticket_Limit**: The maximum number of tickets that can be sold at each museum.  
  - **Staff_Capacity**: The maximum number of visitors that can be handled by the staff at each museum.  

- **visit**: This table contains the revenue generated from ticket sales at each museum.  
  - **Museum_ID**: A unique identifier for each museum.  
  - **Num_of_Ticket**: The number of tickets sold at each museum.  
  - **Total_spent**: The revenue generated from ticket sales at each museum.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical museum capacities, staff-to-visitor ratios, and ticket pricing strategies to ensure realistic and meaningful optimization.

-- Realistic data for museum_constraints
INSERT INTO museum_constraints (Museum_ID, Total_Ticket_Limit, Staff_Capacity) VALUES (1, 1200, 600);
INSERT INTO museum_constraints (Museum_ID, Total_Ticket_Limit, Staff_Capacity) VALUES (2, 1500, 750);
INSERT INTO museum_constraints (Museum_ID, Total_Ticket_Limit, Staff_Capacity) VALUES (3, 1000, 500);

-- Realistic data for visit
INSERT INTO visit (Museum_ID, Num_of_Ticket, Total_spent) VALUES (1, 500, 2500.0);
INSERT INTO visit (Museum_ID, Num_of_Ticket, Total_spent) VALUES (2, 700, 3500.0);
INSERT INTO visit (Museum_ID, Num_of_Ticket, Total_spent) VALUES (3, 300, 1500.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_i \) be the number of tickets sold at museum \( i \), where \( i \in \{1, 2, 3\} \).

#### Objective Function
Maximize the total revenue generated from ticket sales across all museums:
\[
\text{Maximize } \sum_{i=1}^{3} \text{Total\_spent}_i \times x_i
\]
where \( \text{Total\_spent}_i \) is the revenue per ticket at museum \( i \).

#### Constraints
1. **Ticket Sales Limit**: The number of tickets sold at each museum cannot exceed the maximum number of tickets that the museum is allowed to sell:
\[
x_i \leq \text{Total\_Ticket\_Limit}_i \quad \forall i \in \{1, 2, 3\}
\]
2. **Staff Availability**: The number of tickets sold at each museum cannot exceed the maximum number of visitors that the museum's staff can handle:
\[
x_i \leq \text{Staff\_Capacity}_i \quad \forall i \in \{1, 2, 3\}
\]

#### Data Source Verification
- **Total\_spent_i**: This coefficient comes from the `visit.Total_spent` column, which represents the revenue per ticket at each museum.
- **Total\_Ticket\_Limit_i**: This coefficient comes from the `museum_constraints.Total_Ticket_Limit` column, which represents the maximum number of tickets that can be sold at each museum.
- **Staff\_Capacity_i**: This coefficient comes from the `museum_constraints.Staff_Capacity` column, which represents the maximum number of visitors that can be handled by the staff at each museum.

### Numerical Example
Using the provided data:
- For museum 1:
  - \( \text{Total\_spent}_1 = 2500.0 / 500 = 5.0 \)
  - \( \text{Total\_Ticket\_Limit}_1 = 1200 \)
  - \( \text{Staff\_Capacity}_1 = 600 \)
- For museum 2:
  - \( \text{Total\_spent}_2 = 3500.0 / 700 = 5.0 \)
  - \( \text{Total\_Ticket\_Limit}_2 = 1500 \)
  - \( \text{Staff\_Capacity}_2 = 750 \)
- For museum 3:
  - \( \text{Total\_spent}_3 = 1500.0 / 300 = 5.0 \)
  - \( \text{Total\_Ticket\_Limit}_3 = 1000 \)
  - \( \text{Staff\_Capacity}_3 = 500 \)

The objective function becomes:
\[
\text{Maximize } 5.0 \times x_1 + 5.0 \times x_2 + 5.0 \times x_3
\]

The constraints are:
\[
x_1 \leq 1200, \quad x_1 \leq 600
\]
\[
x_2 \leq 1500, \quad x_2 \leq 750
\]
\[
x_3 \leq 1000, \quad x_3 \leq 500
\]

This is 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 12.0.2 Implementation for Museum Ticket Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def museum_ticket_optimization():
    """Optimize ticket sales across museums to maximize revenue."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("museum_ticket_optimization")
    
    # Data from the problem
    museums = [1, 2, 3]
    total_ticket_limit = {1: 1200, 2: 1500, 3: 1000}
    staff_capacity = {1: 600, 2: 750, 3: 500}
    revenue_per_ticket = {1: 5.0, 2: 5.0, 3: 5.0}  # Derived from Total_spent / Num_of_Ticket
    
    # CRITICAL: Validate array lengths before loops
    assert len(museums) == len(total_ticket_limit) == len(staff_capacity) == len(revenue_per_ticket), "Array length mismatch"
    
    # 2. VARIABLES
    x = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{i}", lb=0) for i in museums}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(revenue_per_ticket[i] * x[i] for i in museums), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    # Ticket Sales Limit
    for i in museums:
        model.addConstr(x[i] <= total_ticket_limit[i], name=f"ticket_limit_{i}")
    
    # Staff Availability
    for i in museums:
        model.addConstr(x[i] <= staff_capacity[i], name=f"staff_capacity_{i}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in museums:
            if x[i].x > 1e-6:
                print(f"Tickets sold at museum {i}: {x[i].x:.0f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
museum_ticket_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 9250.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 museum ticket sales optimization
"""

from docplex.mp.model import Model

def museum_optimization():
    """Optimize museum ticket sales to maximize revenue"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="museum_visit")
    
    # Data from the problem
    museum_ids = [1, 2, 3]
    total_ticket_limits = [1200, 1500, 1000]
    staff_capacities = [600, 750, 500]
    total_spent = [2500.0, 3500.0, 1500.0]
    num_of_tickets = [500, 700, 300]
    
    # Calculate revenue per ticket
    revenue_per_ticket = [total_spent[i] / num_of_tickets[i] for i in range(len(museum_ids))]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(museum_ids) == len(total_ticket_limits) == len(staff_capacities) == len(revenue_per_ticket), "Array length mismatch"
    safe_range = range(min(len(museum_ids), len(total_ticket_limits), len(staff_capacities), len(revenue_per_ticket)))  # Safe indexing
    
    # 2. VARIABLES
    x = {i: mdl.continuous_var(name=f"x_{museum_ids[i]}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(revenue_per_ticket[i] * x[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Ticket Sales Limit
    for i in safe_range:
        mdl.add_constraint(x[i] <= total_ticket_limits[i], ctname=f"ticket_limit_{museum_ids[i]}")
    
    # Staff Availability
    for i in safe_range:
        mdl.add_constraint(x[i] <= staff_capacities[i], ctname=f"staff_capacity_{museum_ids[i]}")
    
    # 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"Tickets sold at museum {museum_ids[i]}: {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

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

## 7. Pyomo Implementation

```python
#!/usr/bin/env python3
"""
Pyomo 6.9.2 Implementation for Museum Revenue Optimization
"""

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

def museum_revenue_optimization():
    """Optimize museum ticket sales to maximize revenue."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    museums = [1, 2, 3]
    total_ticket_limit = {1: 1200, 2: 1500, 3: 1000}
    staff_capacity = {1: 600, 2: 750, 3: 500}
    revenue_per_ticket = {1: 5.0, 2: 5.0, 3: 5.0}
    
    # CRITICAL: Validate array lengths before indexing
    assert len(museums) == len(total_ticket_limit) == len(staff_capacity) == len(revenue_per_ticket), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.I = pyo.Set(initialize=museums)
    
    # 4. PARAMETERS (data containers)
    model.total_ticket_limit = pyo.Param(model.I, initialize=total_ticket_limit)
    model.staff_capacity = pyo.Param(model.I, initialize=staff_capacity)
    model.revenue_per_ticket = pyo.Param(model.I, initialize=revenue_per_ticket)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.revenue_per_ticket[i] * model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    def ticket_limit_rule(model, i):
        return model.x[i] <= model.total_ticket_limit[i]
    model.ticket_limit_constraint = pyo.Constraint(model.I, rule=ticket_limit_rule)
    
    def staff_capacity_rule(model, i):
        return model.x[i] <= model.staff_capacity[i]
    model.staff_capacity_constraint = pyo.Constraint(model.I, rule=staff_capacity_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(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nOptimal ticket sales:")
        for i in model.I:
            x_val = pyo.value(model.x[i])
            print(f"Museum {i}: {x_val:.0f} tickets")
        
    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__":
    museum_revenue_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 9250.0
**Execution Time**: 0.98 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with a moderate execution time.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 9250.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 9250.00 | 1.14s | N/A | N/A |
| Pyomo | OPTIMAL | 9250.00 | 0.98s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 9250.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its fastest execution time while consistently finding the optimal solution. DOCplex and Pyomo are also reliable but slower.

### Optimal Decision Variables
- **x_1** = 600.000
  - *Business Meaning*: Number of tickets sold at museum 1, optimally set to 600 due to staff capacity constraints.
- **x_2** = 750.000
  - *Business Meaning*: Number of tickets sold at museum 2, optimally set to 750 due to staff capacity constraints.
- **x_3** = 500.000
  - *Business Meaning*: Number of tickets sold at museum 3, optimally set to 500 due to staff capacity constraints.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests selling 600 tickets at museum 1, 750 tickets at museum 2, and 500 tickets at museum 3 to maximize total revenue. This allocation respects both ticket sales limits and staff capacity constraints.
**Objective Value Meaning**: The optimal total revenue of $9,250 is achieved by maximizing ticket sales within the constraints of staff capacity and ticket limits.
**Resource Allocation Summary**: Allocate staff and ticket sales to ensure museum 1 sells 600 tickets, museum 2 sells 750 tickets, and museum 3 sells 500 tickets.
**Implementation Recommendations**: 1. Adjust ticket sales to match the recommended values. 2. Ensure staff are adequately allocated to handle the visitor load. 3. Monitor ticket sales to stay within the optimal limits.