# Complete Optimization Problem and Solution: company_employee

## 1. Problem Context and Goals

### Context  
The company is focused on optimizing its workforce allocation to maximize the overall employee experience while ensuring efficient resource utilization. The decision involves assigning employees to different companies based on their years of experience and the financial performance of the companies. Each employee can be assigned to only one company, and each company has a maximum capacity for the number of employees it can hire.  

The business configuration includes two key weights:  
- **Weight for years of experience (0.7)**: This reflects the importance of employee experience in the overall employee experience score.  
- **Weight for company profits (0.3)**: This represents the contribution of a company's financial performance to the employee experience score.  

Additionally, each company has a predefined maximum number of employees it can hire, ensuring a balanced distribution of workforce across companies.  

### Goals  
The primary goal is to maximize the total employee experience score, which is calculated as a weighted combination of the employees' years of experience and the financial performance of the companies they are assigned to. Success is measured by achieving the highest possible score while adhering to the constraints of employee assignments and company hiring capacities.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Employee Assignment Constraint**: Each employee must be assigned to exactly one company. This ensures that all employees are allocated without duplication.  
2. **Company Capacity Constraint**: The total number of employees assigned to each company cannot exceed its predefined maximum capacity. This ensures that companies do not exceed their hiring limits.  

These constraints ensure that the workforce allocation is both feasible and aligned with operational capabilities.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for employee assignments and company capacities, and updating business configuration logic to include weights and maximum employee capacities.

CREATE TABLE employee_assignment (
  employee_id INTEGER,
  company_id INTEGER,
  assignment_status BOOLEAN
);

CREATE TABLE company_capacity (
  company_id INTEGER,
  max_employees INTEGER
);
```

### Data Dictionary  
- **employee_assignment**:  
  - **employee_id**: Unique identifier for each employee. Used to track individual employees in the optimization process.  
  - **company_id**: Unique identifier for each company. Used to track the companies employees are assigned to.  
  - **assignment_status**: Indicates whether an employee is assigned to a specific company. This is the binary decision variable in the optimization model.  

- **company_capacity**:  
  - **company_id**: Unique identifier for each company. Used to track the companies in the optimization process.  
  - **max_employees**: The maximum number of employees each company can hire. This serves as the 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 workforce allocation scenarios, considering employee experience levels and company financial performance. The weights for the objective function were adjusted to reflect the importance of experience and profits in the decision-making process. Maximum employee capacities were set based on realistic company sizes and hiring capabilities.

-- Realistic data for employee_assignment
INSERT INTO employee_assignment (employee_id, company_id, assignment_status) VALUES (1, 1, True);
INSERT INTO employee_assignment (employee_id, company_id, assignment_status) VALUES (2, 2, False);
INSERT INTO employee_assignment (employee_id, company_id, assignment_status) VALUES (3, 3, True);

-- Realistic data for company_capacity
INSERT INTO company_capacity (company_id, max_employees) VALUES (1, 10);
INSERT INTO company_capacity (company_id, max_employees) VALUES (2, 15);
INSERT INTO company_capacity (company_id, max_employees) VALUES (3, 20);
```