# Complete Optimization Problem and Solution: theme_gallery

## 1. Problem Context and Goals

### Context  
The theme gallery operates with the goal of maximizing its annual revenue from exhibitions. To achieve this, the gallery must strategically allocate artists to exhibitions based on their popularity and the ticket prices of the exhibitions. Each artist has a limited availability, and each exhibition requires a minimum number of artists to ensure its success. The gallery’s decision-making process involves determining the number of exhibitions each artist should participate in during the year. This decision is influenced by two key factors: the ticket price of each exhibition and the expected attendance for each artist in each exhibition. The ticket price for each exhibition is a fixed value, and the expected attendance is based on historical data and artist popularity. The gallery must ensure that no artist is overbooked and that each exhibition meets its minimum artist requirement. These operational parameters are designed to align with a linear optimization framework, ensuring that the relationships between variables are straightforward and avoid any nonlinear complexities such as variable products or divisions.

### Goals  
The primary goal of the optimization is to maximize the gallery’s total annual revenue from exhibitions. This revenue is calculated by multiplying the ticket price of each exhibition by the expected attendance for each artist in that exhibition and then summing these values across all artists and exhibitions. Success is measured by the total revenue generated, which directly depends on the strategic allocation of artists to exhibitions. The optimization process ensures that this allocation respects the constraints of artist availability and exhibition requirements, leading to a feasible and profitable solution.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Artist Availability Constraint**: Each artist can participate in a limited number of exhibitions per year. The total number of exhibitions an artist is assigned to must not exceed their maximum allowed number of exhibitions.  
2. **Exhibition Requirement Constraint**: Each exhibition must have a minimum number of artists participating to ensure its success. The total number of artists assigned to an exhibition must meet or exceed this minimum requirement.  
3. **Non-Negativity Constraint**: The number of exhibitions an artist participates in must be a non-negative integer, ensuring that the solution is realistic and implementable.  

These constraints are designed to reflect real-world operational limitations and are expressed in a way that naturally leads to a linear mathematical formulation.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for missing optimization requirements (Ticket_Price_j and Attendance_ij). Business configuration logic updated with scalar parameters and formulas for optimization constraints and calculations.

CREATE TABLE artist_exhibition_limits (
  Artist_ID INTEGER,
  Max_Exhibitions INTEGER
);

CREATE TABLE exhibition_artist_requirements (
  Exhibition_ID INTEGER,
  Min_Artists INTEGER
);

CREATE TABLE artist_exhibition_assignments (
  Artist_ID INTEGER,
  Exhibition_ID INTEGER,
  Num_Exhibitions INTEGER
);

CREATE TABLE exhibition_ticket_prices (
  Exhibition_ID INTEGER,
  Ticket_Price INTEGER
);

CREATE TABLE artist_exhibition_attendance (
  Artist_ID INTEGER,
  Exhibition_ID INTEGER,
  Attendance INTEGER
);
```

### Data Dictionary  
- **artist_exhibition_limits**: Tracks the maximum number of exhibitions each artist can participate in.  
  - *Artist_ID*: Unique identifier for each artist.  
  - *Max_Exhibitions*: Maximum number of exhibitions the artist can participate in.  

- **exhibition_artist_requirements**: Specifies the minimum number of artists required for each exhibition.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Min_Artists*: Minimum number of artists required for the exhibition.  

- **artist_exhibition_assignments**: Records the number of exhibitions each artist participates in per year.  
  - *Artist_ID*: Unique identifier for each artist.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Num_Exhibitions*: Number of exhibitions the artist participates in.  

- **exhibition_ticket_prices**: Stores the ticket price for each exhibition.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Ticket_Price*: Ticket price for the exhibition.  

- **artist_exhibition_attendance**: Captures the expected attendance for each artist in each exhibition.  
  - *Artist_ID*: Unique identifier for each artist.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Attendance*: Expected attendance for the artist in the exhibition.  

### 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 ticket prices, artist popularity, and exhibition capacity. Data was generated to ensure a balanced optimization problem with feasible constraints.

-- Realistic data for artist_exhibition_limits
INSERT INTO artist_exhibition_limits (Artist_ID, Max_Exhibitions) VALUES (1, 5);
INSERT INTO artist_exhibition_limits (Artist_ID, Max_Exhibitions) VALUES (2, 6);
INSERT INTO artist_exhibition_limits (Artist_ID, Max_Exhibitions) VALUES (3, 4);

-- Realistic data for exhibition_artist_requirements
INSERT INTO exhibition_artist_requirements (Exhibition_ID, Min_Artists) VALUES (1, 3);
INSERT INTO exhibition_artist_requirements (Exhibition_ID, Min_Artists) VALUES (2, 4);
INSERT INTO exhibition_artist_requirements (Exhibition_ID, Min_Artists) VALUES (3, 3);

-- Realistic data for artist_exhibition_assignments
INSERT INTO artist_exhibition_assignments (Artist_ID, Exhibition_ID, Num_Exhibitions) VALUES (1, 1, 1);
INSERT INTO artist_exhibition_assignments (Artist_ID, Exhibition_ID, Num_Exhibitions) VALUES (2, 2, 2);
INSERT INTO artist_exhibition_assignments (Artist_ID, Exhibition_ID, Num_Exhibitions) VALUES (3, 3, 1);

-- Realistic data for exhibition_ticket_prices
INSERT INTO exhibition_ticket_prices (Exhibition_ID, Ticket_Price) VALUES (1, 50);
INSERT INTO exhibition_ticket_prices (Exhibition_ID, Ticket_Price) VALUES (2, 70);
INSERT INTO exhibition_ticket_prices (Exhibition_ID, Ticket_Price) VALUES (3, 60);

-- Realistic data for artist_exhibition_attendance
INSERT INTO artist_exhibition_attendance (Artist_ID, Exhibition_ID, Attendance) VALUES (1, 1, 100);
INSERT INTO artist_exhibition_attendance (Artist_ID, Exhibition_ID, Attendance) VALUES (2, 2, 200);
INSERT INTO artist_exhibition_attendance (Artist_ID, Exhibition_ID, Attendance) VALUES (3, 3, 150);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_{ij} \) be the number of exhibitions artist \( i \) participates in at exhibition \( j \).  
- \( x_{ij} \geq 0 \) and integer for all \( i, j \).

#### Objective Function
Maximize the total annual revenue:  
\[
\text{Maximize } Z = \sum_{i} \sum_{j} (\text{Ticket\_Price}_j \times \text{Attendance}_{ij} \times x_{ij})
\]  
Where:  
- \( \text{Ticket\_Price}_j \) is the ticket price for exhibition \( j \) (from `exhibition_ticket_prices.Ticket_Price`).  
- \( \text{Attendance}_{ij} \) is the expected attendance for artist \( i \) in exhibition \( j \) (from `artist_exhibition_attendance.Attendance`).  

#### Constraints
1. **Artist Availability Constraint**:  
   \[
   \sum_{j} x_{ij} \leq \text{Max\_Exhibitions}_i \quad \forall i
   \]  
   Where \( \text{Max\_Exhibitions}_i \) is the maximum number of exhibitions artist \( i \) can participate in (from `artist_exhibition_limits.Max_Exhibitions`).  

2. **Exhibition Requirement Constraint**:  
   \[
   \sum_{i} x_{ij} \geq \text{Min\_Artists}_j \quad \forall j
   \]  
   Where \( \text{Min\_Artists}_j \) is the minimum number of artists required for exhibition \( j \) (from `exhibition_artist_requirements.Min_Artists`).  

3. **Non-Negativity Constraint**:  
   \[
   x_{ij} \geq 0 \quad \forall i, j
   \]  

#### Data Source Verification
- \( \text{Ticket\_Price}_j \): `exhibition_ticket_prices.Ticket_Price`  
- \( \text{Attendance}_{ij} \): `artist_exhibition_attendance.Attendance`  
- \( \text{Max\_Exhibitions}_i \): `artist_exhibition_limits.Max_Exhibitions`  
- \( \text{Min\_Artists}_j \): `exhibition_artist_requirements.Min_Artists`  

This is a complete, immediately solvable Mixed-Integer Linear Programming (MILP) model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy Implementation for Theme Gallery Optimization Problem
"""

import gurobipy as gp
from gurobipy import GRB

def theme_gallery_optimization():
    # 1. MODEL & DATA SETUP
    model = gp.Model("theme_gallery_optimization")
    
    # Data from the provided SQL schema
    artist_ids = [1, 2, 3]
    exhibition_ids = [1, 2, 3]
    
    # Artist exhibition limits
    max_exhibitions = {1: 5, 2: 6, 3: 4}
    
    # Exhibition artist requirements
    min_artists = {1: 3, 2: 4, 3: 3}
    
    # Ticket prices
    ticket_prices = {1: 50, 2: 70, 3: 60}
    
    # Attendance data
    attendance = {
        (1, 1): 100,
        (2, 2): 200,
        (3, 3): 150
    }
    
    # CRITICAL: Validate array lengths before loops
    assert len(artist_ids) == len(max_exhibitions), "Artist data length mismatch"
    assert len(exhibition_ids) == len(min_artists) == len(ticket_prices), "Exhibition data length mismatch"
    
    # 2. VARIABLES
    x = model.addVars(artist_ids, exhibition_ids, vtype=GRB.INTEGER, name="x", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    revenue = gp.quicksum(ticket_prices[j] * attendance[(i, j)] * x[i, j] 
                          for i in artist_ids for j in exhibition_ids if (i, j) in attendance)
    model.setObjective(revenue, GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Artist Availability Constraint
    for i in artist_ids:
        model.addConstr(gp.quicksum(x[i, j] for j in exhibition_ids) <= max_exhibitions[i], 
                        name=f"artist_availability_{i}")
    
    # Exhibition Requirement Constraint
    for j in exhibition_ids:
        model.addConstr(gp.quicksum(x[i, j] for i in artist_ids) >= min_artists[j], 
                        name=f"exhibition_requirement_{j}")
    
    # Non-Negativity Constraint is already handled by lb=0 in addVars
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in artist_ids:
            for j in exhibition_ids:
                if x[i, j].x > 1e-6:
                    print(f"Artist {i} participates in Exhibition {j}: {x[i, j].x:.0f} times")
    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__":
    theme_gallery_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 145000.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy successfully found an optimal solution with a high level of reliability and the fastest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex implementation for theme gallery optimization problem
"""

from docplex.mp.model import Model

def theme_gallery_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="theme_gallery_optimization")
    
    # Data from the provided SQL inserts
    artist_ids = [1, 2, 3]
    exhibition_ids = [1, 2, 3]
    
    # Artist availability constraints
    max_exhibitions = {1: 5, 2: 6, 3: 4}
    
    # Exhibition requirements
    min_artists = {1: 3, 2: 4, 3: 3}
    
    # Ticket prices
    ticket_prices = {1: 50, 2: 70, 3: 60}
    
    # Attendance data
    attendance = {
        (1, 1): 100,
        (2, 2): 200,
        (3, 3): 150
    }
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(artist_ids) == len(max_exhibitions), "Artist data length mismatch"
    assert len(exhibition_ids) == len(min_artists) == len(ticket_prices), "Exhibition data length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x_ij = number of exhibitions artist i participates in at exhibition j
    x = {(i, j): mdl.integer_var(name=f"x_{i}_{j}", lb=0) for i in artist_ids for j in exhibition_ids}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total annual revenue
    revenue = mdl.sum(ticket_prices[j] * attendance[(i, j)] * x[(i, j)] for i in artist_ids for j in exhibition_ids if (i, j) in attendance)
    mdl.maximize(revenue)
    
    # 4. CONSTRAINTS
    
    # Artist Availability Constraint
    for i in artist_ids:
        total_exhibitions = mdl.sum(x[(i, j)] for j in exhibition_ids if (i, j) in x)
        mdl.add_constraint(total_exhibitions <= max_exhibitions[i], ctname=f"artist_availability_{i}")
    
    # Exhibition Requirement Constraint
    for j in exhibition_ids:
        total_artists = mdl.sum(x[(i, j)] for i in artist_ids if (i, j) in x)
        mdl.add_constraint(total_artists >= min_artists[j], ctname=f"exhibition_requirement_{j}")
    
    # Non-Negativity Constraint is already handled by the variable definition (lb=0)
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in artist_ids:
            for j in exhibition_ids:
                if (i, j) in x:
                    value = solution.get_value(x[(i, j)])
                    if value > 1e-6:
                        print(f"x[{i},{j}] = {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 145000.0
**Execution Time**: 0.95 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution with the same value as Gurobipy, but with a longer execution time.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def optimize_theme_gallery():
    """Optimize the theme gallery's annual revenue from exhibitions."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Artist data
    artist_ids = [1, 2, 3]
    max_exhibitions = {1: 5, 2: 6, 3: 4}
    
    # Exhibition data
    exhibition_ids = [1, 2, 3]
    min_artists = {1: 3, 2: 4, 3: 3}
    ticket_prices = {1: 50, 2: 70, 3: 60}
    
    # Attendance data
    attendance = {
        (1, 1): 100,
        (2, 2): 200,
        (3, 3): 150
    }
    
    # CRITICAL: Validate array lengths before indexing
    assert len(artist_ids) == len(max_exhibitions), "Artist data length mismatch"
    assert len(exhibition_ids) == len(min_artists) == len(ticket_prices), "Exhibition data length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.I = pyo.Set(initialize=artist_ids)  # Set of artists
    model.J = pyo.Set(initialize=exhibition_ids)  # Set of exhibitions
    
    # 4. PARAMETERS (data containers)
    model.max_exhibitions = pyo.Param(model.I, initialize=max_exhibitions)
    model.min_artists = pyo.Param(model.J, initialize=min_artists)
    model.ticket_prices = pyo.Param(model.J, initialize=ticket_prices)
    model.attendance = pyo.Param(model.I, model.J, initialize=attendance)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, model.J, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.ticket_prices[j] * model.attendance[i, j] * model.x[i, j] for i in model.I for j in model.J)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    # Artist Availability Constraint
    def artist_availability_rule(model, i):
        return sum(model.x[i, j] for j in model.J) <= model.max_exhibitions[i]
    model.artist_availability = pyo.Constraint(model.I, rule=artist_availability_rule)
    
    # Exhibition Requirement Constraint
    def exhibition_requirement_rule(model, j):
        return sum(model.x[i, j] for i in model.I) >= model.min_artists[j]
    model.exhibition_requirement = pyo.Constraint(model.J, rule=exhibition_requirement_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(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.I:
            for j in model.J:
                x_val = pyo.value(model.x[i, j])
                if x_val > 1e-6:  # Only print non-zero values
                    print(f"x[{i}, {j}] = {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
if __name__ == "__main__":
    optimize_theme_gallery()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpf92mxzp8.py", line 96, in <module>
    optimize_theme_gallery()
  File "/tmp/tmpf92mxzp8.py", line 49, in optimize_theme_gallery
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 571, in __setattr__
    self.add_component(name, val)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 1101, in add_component
    val.construct(data)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/objective.py", line 336, in construct
    ans = self._setitem_when_not_present(index, rule(block, index))
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/initializer.py", line 485, in __call__
    return self._fcn(parent)
  File "/tmp/tmpf92mxzp8.py", line 48, in obj_rule
    return sum(model.ticket_prices[j] * model.attendance[i, j] * model.x[i, j] for i in model.I for j in model.J)
  File "/tmp/tmpf92mxzp8.py", line 48, in <genexpr>
    return sum(model.ticket_prices[j] * model.attendance[i, j] * model.x[i, j] for i in model.I for j in model.J)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 1012, in __getitem__
    return super().__getitem__(args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 662, in __getitem__
    return self._getitem_when_not_present(index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 627, in _getitem_when_not_present
    raise ValueError(
ValueError: Error retrieving immutable Param value (attendance[(1, 2)]):
	The Param value is undefined and no default value is specified.

**Analysis**: Pyomo encountered an error due to an undefined parameter value, indicating a potential issue with data handling or model setup.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 145000.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 145000.00 | 0.95s | N/A | N/A |
| Pyomo | ERROR | N/A | 0.75s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 145000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its optimal solution, high reliability, and fastest execution time. DOCplex also provides a valid solution but is slower. Pyomo is not recommended due to its error.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests a total annual revenue of $145,000, which is achievable under the given constraints.
**Objective Value Meaning**: The optimal objective value of $145,000 represents the maximum annual revenue achievable by optimally allocating artists to exhibitions.
**Resource Allocation Summary**: Artists should be allocated to exhibitions based on the optimal solution to maximize revenue while respecting their availability and exhibition requirements.
**Implementation Recommendations**: Implement the allocation plan derived from the optimal solution. Ensure data accuracy and handle any undefined parameters to avoid errors in future optimizations.