# Complete Optimization Problem and Solution: employee_hire_evaluation

## 1. Problem Context and Goals

### Context  
A retail chain is focused on optimizing its employee hiring and allocation strategy across multiple shops to minimize total hiring costs while ensuring each shop meets its staffing requirements. The business decisions involve determining the number of full-time and part-time employees to hire for each shop, as well as deciding which employees should receive bonuses based on their performance evaluations.  

The cost of hiring a full-time employee varies by shop, with each shop having a specific cost per full-time employee. Similarly, the cost of hiring a part-time employee also varies by shop. These costs are critical inputs to the optimization problem, as they directly influence the total hiring expenses.  

The business configuration includes the following operational parameters:  
- **Cost per full-time employee for each shop**: This represents the expense associated with hiring a full-time employee at a specific shop.  
- **Cost per part-time employee for each shop**: This represents the expense associated with hiring a part-time employee at a specific shop.  

The optimization problem is designed to ensure that each shop has sufficient staff to meet its operational needs while adhering to limits on the number of full-time and part-time employees that can be hired. The problem avoids scenarios that would require nonlinear relationships, such as variable products or divisions, ensuring a linear formulation.  

### Goals  
The primary goal of this optimization problem is to minimize the total hiring costs across all shops. This is achieved by carefully deciding the number of full-time and part-time employees to hire for each shop, taking into account the specific costs associated with each type of hire.  

Success is measured by the ability to reduce total hiring expenses while ensuring that each shop meets its minimum staffing requirements and does not exceed the maximum allowed number of full-time or part-time employees. The optimization goal is directly aligned with the operational parameters, ensuring a clear and linear relationship between the decisions and the objective.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure that the staffing decisions align with business requirements:  

1. **Minimum Staffing Requirement**: For each shop, the total number of full-time and part-time employees hired must meet or exceed the minimum staffing requirement. This ensures that each shop has sufficient staff to operate effectively.  

2. **Maximum Full-Time Employees**: For each shop, the number of full-time employees hired must not exceed the maximum allowed number of full-time employees. This ensures compliance with business policies regarding full-time staffing levels.  

3. **Maximum Part-Time Employees**: For each shop, the number of part-time employees hired must not exceed the maximum allowed number of part-time employees. This ensures compliance with business policies regarding part-time staffing levels.  

These constraints are designed to naturally lead to linear mathematical forms, avoiding any complexity that would require nonlinear relationships.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include creating a new table for objective coefficients (cost_full_time and cost_part_time) and updating business configuration logic to ensure all optimization requirements are mapped. Existing tables and configuration logic are reviewed for completeness and alignment with OR expert's analysis.

CREATE TABLE shop_staff_requirements (
  shop_id INTEGER,
  min_staff INTEGER,
  max_full_time INTEGER,
  max_part_time INTEGER
);

CREATE TABLE employee_bonus_decisions (
  employee_id INTEGER,
  y BOOLEAN
);

CREATE TABLE employee_hiring_decisions (
  shop_id INTEGER,
  x_full_time INTEGER,
  x_part_time INTEGER
);

CREATE TABLE employee_costs (
  shop_id INTEGER,
  cost_full_time INTEGER,
  cost_part_time INTEGER
);
```

### Data Dictionary  
The following tables and columns are critical to the optimization problem, with their business purposes and optimization roles clearly defined:  

- **shop_staff_requirements**:  
  - **shop_id**: Unique identifier for each shop.  
  - **min_staff**: Minimum number of employees required for the shop to operate effectively.  
  - **max_full_time**: Maximum number of full-time employees allowed for the shop.  
  - **max_part_time**: Maximum number of part-time employees allowed for the shop.  

- **employee_bonus_decisions**:  
  - **employee_id**: Unique identifier for each employee.  
  - **y**: Binary decision indicating whether a bonus is awarded to the employee.  

- **employee_hiring_decisions**:  
  - **shop_id**: Unique identifier for each shop.  
  - **x_full_time**: Number of full-time employees to hire for the shop.  
  - **x_part_time**: Number of part-time employees to hire for the shop.  

- **employee_costs**:  
  - **shop_id**: Unique identifier for each shop.  
  - **cost_full_time**: Cost per full-time employee for the shop.  
  - **cost_part_time**: Cost per part-time employee for the shop.  

### Current Stored Values  
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for retail staffing and costs, ensuring realistic and meaningful data for optimization.

-- Realistic data for shop_staff_requirements
INSERT INTO shop_staff_requirements (shop_id, min_staff, max_full_time, max_part_time) VALUES (1, 5, 10, 8);
INSERT INTO shop_staff_requirements (shop_id, min_staff, max_full_time, max_part_time) VALUES (2, 6, 12, 10);
INSERT INTO shop_staff_requirements (shop_id, min_staff, max_full_time, max_part_time) VALUES (3, 7, 15, 12);

-- Realistic data for employee_bonus_decisions
INSERT INTO employee_bonus_decisions (employee_id, y) VALUES (101, True);
INSERT INTO employee_bonus_decisions (employee_id, y) VALUES (102, False);
INSERT INTO employee_bonus_decisions (employee_id, y) VALUES (103, True);

-- Realistic data for employee_hiring_decisions
INSERT INTO employee_hiring_decisions (shop_id, x_full_time, x_part_time) VALUES (1, 2, 3);
INSERT INTO employee_hiring_decisions (shop_id, x_full_time, x_part_time) VALUES (2, 3, 4);
INSERT INTO employee_hiring_decisions (shop_id, x_full_time, x_part_time) VALUES (3, 4, 5);

-- Realistic data for employee_costs
INSERT INTO employee_costs (shop_id, cost_full_time, cost_part_time) VALUES (1, 1500, 800);
INSERT INTO employee_costs (shop_id, cost_full_time, cost_part_time) VALUES (2, 1600, 850);
INSERT INTO employee_costs (shop_id, cost_full_time, cost_part_time) VALUES (3, 1700, 900);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{i}^{ft} \): Number of full-time employees to hire for shop \( i \) (integer)  
- \( x_{i}^{pt} \): Number of part-time employees to hire for shop \( i \) (integer)  

#### Objective Function
Minimize the total hiring cost:  
\[
\text{Minimize } \sum_{i} \left( \text{cost\_full\_time}_i \times x_{i}^{ft} + \text{cost\_part\_time}_i \times x_{i}^{pt} \right)
\]  
Where:  
- \( \text{cost\_full\_time}_i \): Cost per full-time employee for shop \( i \) (from `employee_costs.cost_full_time`)  
- \( \text{cost\_part\_time}_i \): Cost per part-time employee for shop \( i \) (from `employee_costs.cost_part_time`)  

#### Constraints
1. **Minimum Staffing Requirement**:  
   For each shop \( i \):  
   \[
   x_{i}^{ft} + x_{i}^{pt} \geq \text{min\_staff}_i
   \]  
   Where \( \text{min\_staff}_i \) is the minimum staffing requirement for shop \( i \) (from `shop_staff_requirements.min_staff`).  

2. **Maximum Full-Time Employees**:  
   For each shop \( i \):  
   \[
   x_{i}^{ft} \leq \text{max\_full\_time}_i
   \]  
   Where \( \text{max\_full\_time}_i \) is the maximum allowed full-time employees for shop \( i \) (from `shop_staff_requirements.max_full_time`).  

3. **Maximum Part-Time Employees**:  
   For each shop \( i \):  
   \[
   x_{i}^{pt} \leq \text{max\_part\_time}_i
   \]  
   Where \( \text{max\_part\_time}_i \) is the maximum allowed part-time employees for shop \( i \) (from `shop_staff_requirements.max_part_time`).  

#### Data Source Verification
- \( \text{cost\_full\_time}_i \): `employee_costs.cost_full_time`  
- \( \text{cost\_part\_time}_i \): `employee_costs.cost_part_time`  
- \( \text{min\_staff}_i \): `shop_staff_requirements.min_staff`  
- \( \text{max\_full\_time}_i \): `shop_staff_requirements.max_full_time`  
- \( \text{max\_part\_time}_i \): `shop_staff_requirements.max_part_time`  

#### Complete Numerical Model
For the provided data, the model is:  

**Objective Function**:  
\[
\text{Minimize } 1500x_{1}^{ft} + 800x_{1}^{pt} + 1600x_{2}^{ft} + 850x_{2}^{pt} + 1700x_{3}^{ft} + 900x_{3}^{pt}
\]  

**Constraints**:  
1. **Minimum Staffing Requirement**:  
   \[
   x_{1}^{ft} + x_{1}^{pt} \geq 5  
   x_{2}^{ft} + x_{2}^{pt} \geq 6  
   x_{3}^{ft} + x_{3}^{pt} \geq 7  
   \]  
2. **Maximum Full-Time Employees**:  
   \[
   x_{1}^{ft} \leq 10  
   x_{2}^{ft} \leq 12  
   x_{3}^{ft} \leq 15  
   \]  
3. **Maximum Part-Time Employees**:  
   \[
   x_{1}^{pt} \leq 8  
   x_{2}^{pt} \leq 10  
   x_{3}^{pt} \leq 12  
   \]  

This is a complete, immediately solvable Mixed-Integer Linear Programming (MIP) model.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Employee Hiring Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def employee_hire_optimization():
    """Optimize employee hiring to minimize total hiring costs."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("employee_hire_evaluation")
    
    # Data from the problem
    shops = [1, 2, 3]
    cost_full_time = {1: 1500, 2: 1600, 3: 1700}
    cost_part_time = {1: 800, 2: 850, 3: 900}
    min_staff = {1: 5, 2: 6, 3: 7}
    max_full_time = {1: 10, 2: 12, 3: 15}
    max_part_time = {1: 8, 2: 10, 3: 12}
    
    # CRITICAL: Validate array lengths before loops
    assert len(shops) == len(cost_full_time) == len(cost_part_time) == len(min_staff) == len(max_full_time) == len(max_part_time), "Array length mismatch"
    
    # 2. VARIABLES
    x_full_time = {i: model.addVar(vtype=GRB.INTEGER, name=f"x_full_time_{i}", lb=0) for i in shops}
    x_part_time = {i: model.addVar(vtype=GRB.INTEGER, name=f"x_part_time_{i}", lb=0) for i in shops}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(
        gp.quicksum(cost_full_time[i] * x_full_time[i] + cost_part_time[i] * x_part_time[i] for i in shops),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    # Minimum Staffing Requirement
    for i in shops:
        model.addConstr(x_full_time[i] + x_part_time[i] >= min_staff[i], name=f"min_staff_{i}")
    
    # Maximum Full-Time Employees
    for i in shops:
        model.addConstr(x_full_time[i] <= max_full_time[i], name=f"max_full_time_{i}")
    
    # Maximum Part-Time Employees
    for i in shops:
        model.addConstr(x_part_time[i] <= max_part_time[i], name=f"max_part_time_{i}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in shops:
            print(f"Shop {i}: Full-Time Employees = {x_full_time[i].x}, Part-Time Employees = {x_part_time[i].x}")
    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__":
    employee_hire_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 15400.0
**Execution Time**: 0.14 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with the lowest execution time among the solvers.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex implementation for employee hiring optimization problem
"""

from docplex.mp.model import Model

def employee_hire_optimization():
    """Optimize employee hiring to minimize total costs"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="employee_hire_optimization")
    
    # Data from the problem statement
    shop_ids = [1, 2, 3]
    cost_full_time = {1: 1500, 2: 1600, 3: 1700}
    cost_part_time = {1: 800, 2: 850, 3: 900}
    min_staff = {1: 5, 2: 6, 3: 7}
    max_full_time = {1: 10, 2: 12, 3: 15}
    max_part_time = {1: 8, 2: 10, 3: 12}
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(shop_ids) == len(cost_full_time) == len(cost_part_time) == len(min_staff) == len(max_full_time) == len(max_part_time), "Array length mismatch"
    safe_range = range(min(len(shop_ids), len(cost_full_time), len(cost_part_time), len(min_staff), len(max_full_time), len(max_part_time)))  # Safe indexing
    
    # 2. VARIABLES
    x_full_time = {i: mdl.integer_var(name=f"x_full_time_{i}", lb=0) for i in safe_range}
    x_part_time = {i: mdl.integer_var(name=f"x_part_time_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(cost_full_time[shop_ids[i]] * x_full_time[i] + cost_part_time[shop_ids[i]] * x_part_time[i] for i in safe_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    # Pattern A: Minimum Staffing Requirement
    for i in safe_range:
        mdl.add_constraint(x_full_time[i] + x_part_time[i] >= min_staff[shop_ids[i]], ctname=f"min_staff_{shop_ids[i]}")
    
    # Pattern B: Maximum Full-Time Employees
    for i in safe_range:
        mdl.add_constraint(x_full_time[i] <= max_full_time[shop_ids[i]], ctname=f"max_full_time_{shop_ids[i]}")
    
    # Pattern C: Maximum Part-Time Employees
    for i in safe_range:
        mdl.add_constraint(x_part_time[i] <= max_part_time[shop_ids[i]], ctname=f"max_part_time_{shop_ids[i]}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            print(f"Shop {shop_ids[i]}: Full-time = {solution.get_value(x_full_time[i])}, Part-time = {solution.get_value(x_part_time[i])}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

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

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def employee_hire_optimization():
    """Optimize employee hiring and allocation strategy across multiple shops."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Define shop IDs and their respective data
    shop_ids = [1, 2, 3]
    
    # Cost per full-time and part-time employee for each shop
    cost_full_time = {1: 1500, 2: 1600, 3: 1700}
    cost_part_time = {1: 800, 2: 850, 3: 900}
    
    # Minimum staffing requirements for each shop
    min_staff = {1: 5, 2: 6, 3: 7}
    
    # Maximum allowed full-time and part-time employees for each shop
    max_full_time = {1: 10, 2: 12, 3: 15}
    max_part_time = {1: 8, 2: 10, 3: 12}
    
    # CRITICAL: Validate array lengths before indexing
    assert len(shop_ids) == len(cost_full_time) == len(cost_part_time) == len(min_staff) == len(max_full_time) == len(max_part_time), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.I = pyo.Set(initialize=shop_ids)  # Set of shop IDs
    
    # 4. PARAMETERS (data containers)
    model.cost_full_time = pyo.Param(model.I, initialize=cost_full_time)
    model.cost_part_time = pyo.Param(model.I, initialize=cost_part_time)
    model.min_staff = pyo.Param(model.I, initialize=min_staff)
    model.max_full_time = pyo.Param(model.I, initialize=max_full_time)
    model.max_part_time = pyo.Param(model.I, initialize=max_part_time)
    
    # 5. VARIABLES
    # Number of full-time and part-time employees to hire for each shop
    model.x_full_time = pyo.Var(model.I, within=pyo.NonNegativeIntegers)
    model.x_part_time = pyo.Var(model.I, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    # Minimize the total hiring cost
    def obj_rule(model):
        return sum(model.cost_full_time[i] * model.x_full_time[i] + model.cost_part_time[i] * model.x_part_time[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Minimum Staffing Requirement
    def min_staff_rule(model, i):
        return model.x_full_time[i] + model.x_part_time[i] >= model.min_staff[i]
    model.min_staff_constraint = pyo.Constraint(model.I, rule=min_staff_rule)
    
    # Maximum Full-Time Employees
    def max_full_time_rule(model, i):
        return model.x_full_time[i] <= model.max_full_time[i]
    model.max_full_time_constraint = pyo.Constraint(model.I, rule=max_full_time_rule)
    
    # Maximum Part-Time Employees
    def max_part_time_rule(model, i):
        return model.x_part_time[i] <= model.max_part_time[i]
    model.max_part_time_constraint = pyo.Constraint(model.I, rule=max_part_time_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("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.I:
            x_ft_val = pyo.value(model.x_full_time[i])
            x_pt_val = pyo.value(model.x_part_time[i])
            print(f"Shop {i}: Full-Time = {int(x_ft_val)}, Part-Time = {int(x_pt_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__":
    employee_hire_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 15400.0
**Execution Time**: 0.84 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with an execution time between Gurobipy and DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 15400.00 | 0.14s | N/A | N/A |
| Docplex | OPTIMAL | 15400.00 | 1.08s | N/A | N/A |
| Pyomo | OPTIMAL | 15400.00 | 0.84s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 15400.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its faster execution time while still providing the same optimal solution as the other solvers.

### Business Interpretation
**Overall Strategy**: The optimal hiring plan minimizes the total cost while meeting all staffing requirements and constraints.
**Objective Value Meaning**: The total cost of hiring employees across all shops is minimized to $15,400.
**Resource Allocation Summary**: Allocate the minimum required staff for each shop while adhering to the maximum limits for full-time and part-time employees.
**Implementation Recommendations**: Proceed with hiring the calculated number of full-time and part-time employees for each shop to achieve the optimal cost.