# Complete Optimization Problem and Solution: employee_hire_evaluation

## 1. Problem Context and Goals

### Context  
A retail company is focused on optimizing the allocation of its employees to various shops to maximize the bonuses awarded to employees. The decision-making process involves determining which employees should be assigned to which shops. Each employee can only be assigned to one shop, and each shop must have a minimum number of employees to operate effectively. The bonuses for employees are predetermined based on past evaluations. The company's operational parameters include ensuring that each shop meets its minimum staffing requirements, which is a critical constraint in the optimization model. The business configuration specifies that each shop requires a minimum of three employees, which is a realistic requirement for maintaining adequate service levels in small to medium-sized retail environments.

### Goals  
The primary goal of this optimization problem is to maximize the total bonuses awarded to employees by strategically assigning them to shops. The metric for optimization is the total sum of bonuses, which is calculated by considering the bonus associated with each employee and their assignment to a shop. Success in this optimization is measured by the total bonus amount, which is directly linked to the employee assignments. The objective is to achieve the highest possible total bonus while adhering to the staffing constraints of each shop.

## 2. Constraints    

The optimization problem is subject to the following constraints:

- Each employee must be assigned to exactly one shop. This ensures that no employee is left unassigned and that each employee's assignment is unique.
- Each shop must have at least the minimum required number of employees. This constraint ensures that the operational needs of each shop are met, maintaining service quality and operational efficiency.

These constraints are designed to align with the linear mathematical formulation of the problem, ensuring that the decision variables and operational parameters are appropriately represented.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema adjustments and configuration logic updates were made to address the OR expert's mapping analysis, ensuring all necessary data for constraints are available and decision variables are properly mapped.

CREATE TABLE evaluation (
  Employee_ID INTEGER,
  Bonus FLOAT
);

CREATE TABLE hiring (
  Employee_ID INTEGER,
  Shop_ID INTEGER,
  binary_assignment BOOLEAN
);

CREATE TABLE shop_requirements (
  Shop_ID INTEGER,
  Min_Employees INTEGER
);
```

### Data Dictionary  
- **Evaluation Table**: This table captures the evaluation data for each employee, including the bonus amount. The bonus serves as a key factor in the optimization objective, representing the potential reward for assigning an employee to a shop.
  - **Employee_ID**: A unique identifier for each employee, used to index the bonus values in the optimization model.
  - **Bonus**: The monetary bonus awarded to each employee, which acts as a coefficient in the objective function to maximize total bonuses.

- **Hiring Table**: This table tracks the assignment of employees to shops. The binary assignment indicates whether an employee is assigned to a specific shop.
  - **Employee_ID**: A unique identifier for each employee, used to track their assignment status.
  - **Shop_ID**: A unique identifier for each shop, used to determine which shop an employee is assigned to.
  - **Binary Assignment**: A boolean value indicating if an employee is assigned to a shop, serving as the decision variable in the optimization model.

- **Shop Requirements Table**: This table outlines the staffing requirements for each shop, specifying the minimum number of employees needed.
  - **Shop_ID**: A unique identifier for each shop, used to link staffing requirements to specific shops.
  - **Min_Employees**: The minimum number of employees required for each shop, serving as a constraint bound 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 retail staffing needs and bonus structures, ensuring each shop has a realistic number of employees and bonuses reflect common retail compensation practices.

-- Realistic data for evaluation
INSERT INTO evaluation (Employee_ID, Bonus) VALUES (1, 500.0);
INSERT INTO evaluation (Employee_ID, Bonus) VALUES (2, 750.0);
INSERT INTO evaluation (Employee_ID, Bonus) VALUES (3, 1000.0);

-- Realistic data for hiring
INSERT INTO hiring (Employee_ID, Shop_ID, binary_assignment) VALUES (1, 101, True);
INSERT INTO hiring (Employee_ID, Shop_ID, binary_assignment) VALUES (2, 102, True);
INSERT INTO hiring (Employee_ID, Shop_ID, binary_assignment) VALUES (3, 103, True);

-- Realistic data for shop_requirements
INSERT INTO shop_requirements (Shop_ID, Min_Employees) VALUES (101, 3);
INSERT INTO shop_requirements (Shop_ID, Min_Employees) VALUES (102, 4);
INSERT INTO shop_requirements (Shop_ID, Min_Employees) VALUES (103, 5);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary decision variable where \( x_{ij} = 1 \) if employee \( i \) is assigned to shop \( j \), and \( x_{ij} = 0 \) otherwise.

#### Objective Function
Maximize the total bonuses awarded to employees:
\[
\text{Maximize } \sum_{i} \sum_{j} \text{Bonus}_i \times x_{ij}
\]
where \(\text{Bonus}_i\) is the bonus for employee \( i \) from the `evaluation` table.

#### Constraints
1. **Each employee is assigned to exactly one shop:**
   \[
   \sum_{j} x_{ij} = 1 \quad \forall i
   \]
   This ensures that each employee is assigned to exactly one shop.

2. **Each shop meets its minimum staffing requirement:**
   \[
   \sum_{i} x_{ij} \geq \text{Min\_Employees}_j \quad \forall j
   \]
   where \(\text{Min\_Employees}_j\) is the minimum number of employees required for shop \( j \) from the `shop_requirements` table.

3. **Binary assignment constraint:**
   \[
   x_{ij} \in \{0, 1\} \quad \forall i, j
   \]
   This ensures that the decision variable \( x_{ij} \) is binary, indicating whether an employee is assigned to a shop or not.

Data Source Verification:
- **Bonuses (\(\text{Bonus}_i\))**: Retrieved from `evaluation.Bonus`.
- **Minimum Employees (\(\text{Min\_Employees}_j\))**: Retrieved from `shop_requirements.Min_Employees`.

This formulation provides a complete linear model that can be solved using linear programming techniques to determine the optimal assignment of employees to shops, maximizing the total bonuses while satisfying all constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation - Retry Attempt 4

import gurobipy as gp
from gurobipy import GRB

def employee_hire_evaluation():
    # 1. MODEL & DATA SETUP
    model = gp.Model("employee_hire_evaluation")
    
    # Data from the problem context
    employee_ids = [1, 2, 3]
    shop_ids = [101, 102, 103]
    bonuses = {1: 500.0, 2: 750.0, 3: 1000.0}
    min_employees = {101: 3, 102: 4, 103: 5}
    
    # Validate data lengths
    assert len(employee_ids) == len(bonuses), "Mismatch in employee data length"
    assert len(shop_ids) == len(min_employees), "Mismatch in shop data length"
    
    # 2. VARIABLES
    # Decision variables: x[i, j] = 1 if employee i is assigned to shop j
    x = model.addVars(employee_ids, shop_ids, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total bonuses
    model.setObjective(gp.quicksum(bonuses[i] * x[i, j] for i in employee_ids for j in shop_ids), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    # Each employee is assigned to exactly one shop
    model.addConstrs((gp.quicksum(x[i, j] for j in shop_ids) == 1 for i in employee_ids), name="employee_assignment")
    
    # Each shop meets its minimum staffing requirement
    model.addConstrs((gp.quicksum(x[i, j] for i in employee_ids) >= min_employees[j] for j in shop_ids), name="shop_staffing")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in employee_ids:
            for j in shop_ids:
                if x[i, j].x > 0.5:  # Binary variable, so check if it's 1
                    print(f"Employee {i} assigned to Shop {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    else:
        print("Optimization ended with status", model.status)

# Run the optimization
employee_hire_evaluation()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Gurobipy consistently reports the problem as infeasible, indicating a potential issue with the constraints or data.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation - Retry Attempt 4

from docplex.mp.model import Model

def employee_hire_evaluation():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="employee_hire_evaluation")
    
    # Data from the database
    employee_ids = [1, 2, 3]
    bonuses = [500.0, 750.0, 1000.0]
    shop_ids = [101, 102, 103]
    min_employees = [3, 4, 5]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(employee_ids) == len(bonuses), "Employee and bonus array length mismatch"
    assert len(shop_ids) == len(min_employees), "Shop and min_employees array length mismatch"
    
    # Safe indexing
    safe_employee_range = range(len(employee_ids))
    safe_shop_range = range(len(shop_ids))
    
    # 2. VARIABLES
    # Binary decision variables for employee-shop assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in safe_employee_range for j in safe_shop_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total bonuses
    objective = mdl.sum(bonuses[i] * x[i, j] for i in safe_employee_range for j in safe_shop_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Each employee is assigned to exactly one shop
    for i in safe_employee_range:
        mdl.add_constraint(mdl.sum(x[i, j] for j in safe_shop_range) == 1, ctname=f"assign_employee_{i}")
    
    # Each shop meets its minimum staffing requirement
    for j in safe_shop_range:
        mdl.add_constraint(mdl.sum(x[i, j] for i in safe_employee_range) >= min_employees[j], ctname=f"min_staff_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_employee_range:
            for j in safe_shop_range:
                if solution.get_value(x[i, j]) > 0.5:  # Check if the employee is assigned to the shop
                    print(f"Employee {employee_ids[i]} assigned to Shop {shop_ids[j]}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Execute the function
employee_hire_evaluation()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: DOCplex also reports infeasibility, suggesting that the problem formulation or data constraints are not satisfiable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation - Retry Attempt 4

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

def employee_hire_evaluation():
    """Optimize employee assignments to maximize bonuses."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Employee and shop data
    employees = [1, 2, 3]
    shops = [101, 102, 103]
    bonuses = {1: 500.0, 2: 750.0, 3: 1000.0}
    min_employees = {101: 3, 102: 4, 103: 5}
    
    # CRITICAL: Validate data lengths
    assert len(bonuses) == len(employees), "Mismatch in bonuses and employees"
    assert len(min_employees) == len(shops), "Mismatch in min_employees and shops"
    
    # 3. SETS
    model.I = pyo.Set(initialize=employees)
    model.J = pyo.Set(initialize=shops)
    
    # 4. PARAMETERS
    model.bonus = pyo.Param(model.I, initialize=bonuses)
    model.min_employees = pyo.Param(model.J, initialize=min_employees)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, model.J, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.bonus[i] * 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
    
    # Each employee is assigned to exactly one shop
    def employee_assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.J) == 1
    model.employee_assignment = pyo.Constraint(model.I, rule=employee_assignment_rule)
    
    # Each shop meets its minimum staffing requirement
    def shop_staffing_rule(model, j):
        return sum(model.x[i, j] for i in model.I) >= model.min_employees[j]
    model.shop_staffing = pyo.Constraint(model.J, rule=shop_staffing_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)
    
    # 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("\nEmployee assignments:")
        for i in model.I:
            for j in model.J:
                if pyo.value(model.x[i, j]) > 0.5:  # Binary variable, check if assigned
                    print(f"Employee {i} assigned to Shop {j}")
        
    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
employee_hire_evaluation()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Pyomo's infeasibility result aligns with the other solvers, reinforcing the likelihood of an issue with the problem setup.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | INFEASIBLE | N/A | 0.25s | N/A | 4 |
| Docplex | INFEASIBLE | N/A | 2.15s | N/A | 4 |
| Pyomo | INFEASIBLE | N/A | 1.18s | N/A | 4 |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Solver Retry Summary**: gurobipy: 4 attempts, docplex: 4 attempts, pyomo: 4 attempts

### Final Recommendation
**Confidence Level**: HIGH
**Preferred Solver(s)**: multiple
**Reasoning**: All solvers consistently report infeasibility, suggesting a fundamental issue with the problem constraints or data.

### Business Interpretation
**Overall Strategy**: The current problem setup cannot be solved as it stands, indicating that the staffing requirements or employee bonuses need to be re-evaluated.
**Objective Value Meaning**: The objective value cannot be interpreted as the problem is infeasible.
**Resource Allocation Summary**: Current resource allocation requirements cannot be met; reassessment of staffing needs and employee bonuses is necessary.
**Implementation Recommendations**: Review and potentially relax the minimum staffing requirements for shops. Verify the accuracy of the bonus data and ensure all constraints are logically sound.