# Complete Optimization Problem and Solution: coffee_shop

## 1. Problem Context and Goals

### Context  
The coffee shop chain is focused on optimizing the allocation of staff during happy hours across its various locations. The primary decision involves determining the number of staff members to assign to each happy hour event, represented by the variable Num_of_shaff_in_charge[HH_ID], which is an integer. The objective is to maximize the overall customer satisfaction score, which is calculated by summing the product of each shop's satisfaction score and the number of staff allocated to its happy hour events. 

Operational parameters include the total number of staff available for allocation across all shops, which serves as a constraint in the optimization model. Additionally, there is a minimum number of staff required to manage each happy hour event, ensuring that service levels are maintained. These parameters are critical in forming the linear constraints of the optimization problem. The business configuration specifies that the total staff available is 19, reflecting the sum of staff from all shops, and a minimum of 2 staff members is required per happy hour to maintain basic service levels during peak times.

### Goals  
The goal of the optimization is to maximize the overall customer satisfaction score across all coffee shops during happy hours. This is achieved by strategically allocating staff to each event, ensuring that the sum of the product of each shop's satisfaction score and the number of staff allocated is maximized. Success is measured by the increase in the total satisfaction score, aligning with the expected coefficient sources from the shop's satisfaction scores.

## 2. Constraints    

The optimization problem is subject to several linear constraints:

- The total number of staff allocated across all happy hour events must not exceed the total staff available, which is 19.
- Each happy hour event must have at least the minimum required number of staff, which is 2, to ensure adequate service levels.
- The number of staff allocated to each happy hour event at a shop cannot exceed the number of staff available at that shop.

These constraints are designed to ensure that the allocation of staff is both feasible and efficient, adhering to the operational limitations and requirements of the coffee shop chain.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding missing tables and parameters for optimization, modifying existing tables to fill mapping gaps, and moving scalar parameters to configuration logic.

CREATE TABLE shop (
  Shop_ID INTEGER,
  Score FLOAT,
  Num_of_staff INTEGER
);

CREATE TABLE staff_allocation (
  HH_ID INTEGER,
  Num_of_shaff_in_charge INTEGER
);
```

### Data Dictionary  
The database consists of two main tables: 

- **Shop Table**: This table stores information about each coffee shop, including:
  - **Shop_ID**: A unique identifier for each shop, used to map scores and staff numbers.
  - **Score**: The customer satisfaction score for each shop, which serves as a coefficient in the objective function.
  - **Num_of_staff**: The number of staff available at each shop, used as a constraint bound for staff allocation.

- **Staff Allocation Table**: This table tracks the allocation of staff to happy hours, including:
  - **HH_ID**: A unique identifier for each happy hour event, used to index staff allocation.
  - **Num_of_shaff_in_charge**: The number of staff allocated to each happy hour event, serving as a decision variable in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical staffing levels and customer satisfaction scores for a small chain of coffee shops, ensuring that the optimization problem remains solvable and realistic.

-- Realistic data for shop
INSERT INTO shop (Shop_ID, Score, Num_of_staff) VALUES (1, 4.5, 5);
INSERT INTO shop (Shop_ID, Score, Num_of_staff) VALUES (2, 3.8, 8);
INSERT INTO shop (Shop_ID, Score, Num_of_staff) VALUES (3, 4.2, 6);

-- Realistic data for staff_allocation
INSERT INTO staff_allocation (HH_ID, Num_of_shaff_in_charge) VALUES (101, 3);
INSERT INTO staff_allocation (HH_ID, Num_of_shaff_in_charge) VALUES (102, 4);
INSERT INTO staff_allocation (HH_ID, Num_of_shaff_in_charge) VALUES (103, 2);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{i} \): Number of staff allocated to happy hour event \( i \). This is an integer variable.
  - \( i \) corresponds to the happy hour event IDs (101, 102, 103).

#### Objective Function
Maximize the overall customer satisfaction score:

\[
\text{Maximize } Z = 4.5 \times x_{101} + 3.8 \times x_{102} + 4.2 \times x_{103}
\]

Data Source Verification:
- Coefficients (4.5, 3.8, 4.2) are derived from `shop.Score` for each corresponding shop.

#### Constraints
1. Total staff allocation constraint:
   \[
   x_{101} + x_{102} + x_{103} \leq 19
   \]
   Data Source Verification:
   - Total staff available is a business configuration parameter.

2. Minimum staff requirement for each happy hour event:
   \[
   x_{101} \geq 2
   \]
   \[
   x_{102} \geq 2
   \]
   \[
   x_{103} \geq 2
   \]
   Data Source Verification:
   - Minimum staff requirement per event is a business configuration parameter.

3. Maximum staff available at each shop:
   \[
   x_{101} \leq 5
   \]
   \[
   x_{102} \leq 8
   \]
   \[
   x_{103} \leq 6
   \]
   Data Source Verification:
   - Maximum staff per shop is derived from `shop.Num_of_staff` for each corresponding shop.

This linear programming model is designed to maximize customer satisfaction by optimally allocating staff to happy hour events while adhering to staffing constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_staff_allocation():
    """Optimize staff allocation for coffee shop happy hours"""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("coffee_shop")
    
    # Data from the problem context
    hh_ids = [101, 102, 103]
    scores = [4.5, 3.8, 4.2]
    max_staff = [5, 8, 6]
    total_staff_available = 19
    min_staff_per_event = 2
    
    # CRITICAL: Validate array lengths before loops
    assert len(hh_ids) == len(scores) == len(max_staff), "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variables for staff allocation
    x = {i: model.addVar(vtype=GRB.INTEGER, name=f"x_{hh_ids[i]}", lb=0) 
         for i in range(len(hh_ids))}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the overall customer satisfaction score
    model.setObjective(gp.quicksum(scores[i] * x[i] for i in range(len(hh_ids))), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total staff allocation constraint
    model.addConstr(gp.quicksum(x[i] for i in range(len(hh_ids))) <= total_staff_available, name="total_staff_limit")
    
    # Minimum staff requirement for each happy hour event
    for i in range(len(hh_ids)):
        model.addConstr(x[i] >= min_staff_per_event, name=f"min_staff_{hh_ids[i]}")
    
    # Maximum staff available at each shop
    for i in range(len(hh_ids)):
        model.addConstr(x[i] <= max_staff[i], name=f"max_staff_{hh_ids[i]}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(len(hh_ids)):
            if x[i].x > 1e-6:
                print(f"x[{hh_ids[i]}] = {x[i].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_staff_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 78.1
**Execution Time**: 3.20 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution efficiently with the shortest execution time among the solvers.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_staff_allocation():
    """Optimize staff allocation for coffee shop happy hours"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="coffee_shop")
    
    # Data from the problem context
    shop_ids = [1, 2, 3]
    hh_ids = [101, 102, 103]
    scores = [4.5, 3.8, 4.2]
    max_staff = [5, 8, 6]
    total_staff_available = 19
    min_staff_per_hh = 2
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(shop_ids) == len(hh_ids) == len(scores) == len(max_staff), "Array length mismatch"
    safe_range = range(len(shop_ids))  # Safe indexing
    
    # 2. VARIABLES
    # Integer variables for staff allocation
    x = {i: mdl.integer_var(name=f"x_{hh_ids[i]}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the overall customer satisfaction score
    objective = mdl.sum(scores[i] * x[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total staff allocation constraint
    total_allocation = mdl.sum(x[i] for i in safe_range)
    mdl.add_constraint(total_allocation <= total_staff_available, ctname="total_staff_limit")
    
    # Minimum staff requirement for each happy hour event
    for i in safe_range:
        mdl.add_constraint(x[i] >= min_staff_per_hh, ctname=f"min_staff_{hh_ids[i]}")
    
    # Maximum staff available at each shop
    for i in safe_range:
        mdl.add_constraint(x[i] <= max_staff[i], ctname=f"max_staff_{hh_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"x[{hh_ids[i]}] = {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_staff_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 78.1
**Execution Time**: 8.70 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def coffee_shop_optimization():
    """Optimize staff allocation for coffee shop happy hours"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Happy hour event IDs
    hh_ids = [101, 102, 103]
    # Satisfaction scores for each shop
    scores = {101: 4.5, 102: 3.8, 103: 4.2}
    # Maximum staff available at each shop
    max_staff = {101: 5, 102: 8, 103: 6}
    # Total staff available across all shops
    total_staff_available = 19
    # Minimum staff required per happy hour
    min_staff_required = 2
    
    # CRITICAL: Validate data lengths before indexing
    assert len(hh_ids) == len(scores) == len(max_staff), "Data length mismatch"
    
    # 3. SETS
    model.HH = pyo.Set(initialize=hh_ids)
    
    # 4. PARAMETERS
    model.score = pyo.Param(model.HH, initialize=scores)
    model.max_staff = pyo.Param(model.HH, initialize=max_staff)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.HH, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.score[i] * model.x[i] for i in model.HH)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Total staff allocation constraint
    def total_staff_rule(model):
        return sum(model.x[i] for i in model.HH) <= total_staff_available
    model.total_staff_constraint = pyo.Constraint(rule=total_staff_rule)
    
    # Minimum staff requirement for each happy hour event
    def min_staff_rule(model, i):
        return model.x[i] >= min_staff_required
    model.min_staff_constraint = pyo.Constraint(model.HH, rule=min_staff_rule)
    
    # Maximum staff available at each shop
    def max_staff_rule(model, i):
        return model.x[i] <= model.max_staff[i]
    model.max_staff_constraint = pyo.Constraint(model.HH, rule=max_staff_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Solve the model
    results = solver.solve(model, tee=True)
    
    # 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("\nStaff allocation:")
        for i in model.HH:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"Staff for HH_ID {i} = {int(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
coffee_shop_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 78.1
**Execution Time**: 5.27 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with a moderate execution time, consistent with the other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 78.10 | 3.20s | N/A | N/A |
| Docplex | OPTIMAL | 78.10 | 8.70s | N/A | N/A |
| Pyomo | OPTIMAL | 78.10 | 5.27s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 78.1
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided the optimal solution with the shortest execution time, indicating efficiency and reliability.

### Optimal Decision Variables
- **x_101** = 5.000
  - *Business Meaning*: Resource allocation for x_101
- **x_102** = 8.000
  - *Business Meaning*: Resource allocation for x_102
- **x_103** = 6.000
  - *Business Meaning*: Resource allocation for x_103

### Business Interpretation
**Overall Strategy**: The optimal allocation of staff maximizes customer satisfaction while adhering to all constraints.
**Objective Value Meaning**: An optimal customer satisfaction score of 78.1 indicates the best possible allocation of staff resources to maximize satisfaction.
**Resource Allocation Summary**: Staff should be allocated as follows: 5 to event 101, 8 to event 102, and 6 to event 103, utilizing all available staff efficiently.
**Implementation Recommendations**: Implement the staff allocation plan as per the recommended decision variables to achieve optimal customer satisfaction.