# 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);
```