# 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.  


### Retrieved Values

**Query 1: This is crucial for understanding the initial state of employee assignments, which serves as the decision variable in the optimization model.**

```sql
SELECT employee_id, company_id, assignment_status FROM employee_assignment;
```

**Results (CSV format):**
```csv
employee_id,company_id,assignment_status
1,1,1
2,2,0
3,3,1
```

**Query 2: This data is essential for enforcing the company capacity constraint in the optimization model.**

```sql
SELECT company_id, max_employees FROM company_capacity;
```

**Results (CSV format):**
```csv
company_id,max_employees
1,10
2,15
3,20
```

**Query 3: This helps in understanding the current load on each company and ensures that the company capacity constraint is not violated.**

```sql
SELECT company_id, COUNT(employee_id) AS current_employees FROM employee_assignment WHERE assignment_status = TRUE GROUP BY company_id;
```

**Results (CSV format):**
```csv
company_id,current_employees
1,1
3,1
```

**Query 4: This data is important for identifying employees who are available for assignment, which is necessary for the employee assignment constraint.**

```sql
SELECT employee_id FROM employee_assignment WHERE assignment_status = FALSE;
```

**Results (CSV format):**
```csv
employee_id
2
```

**Query 5: This helps in identifying companies that can still accept more employees, which is crucial for the company capacity constraint.**

```sql
SELECT cc.company_id, cc.max_employees, COUNT(ea.employee_id) AS current_employees FROM company_capacity cc LEFT JOIN employee_assignment ea ON cc.company_id = ea.company_id AND ea.assignment_status = TRUE GROUP BY cc.company_id, cc.max_employees HAVING COUNT(ea.employee_id) < cc.max_employees;
```

**Results (CSV format):**
```csv
company_id,max_employees,current_employees
1,10,1
2,15,0
3,20,1
```

**Query 6: This provides an overview of the workforce and capacity, which is useful for understanding the overall feasibility of the optimization problem.**

```sql
SELECT SUM(cc.max_employees) AS total_capacity, COUNT(ea.employee_id) AS total_employees FROM company_capacity cc LEFT JOIN employee_assignment ea ON cc.company_id = ea.company_id AND ea.assignment_status = TRUE;
```

**Results (CSV format):**
```csv
total_capacity,total_employees
45,2
```

**Query 7: This data is useful for understanding the relationship between employees and companies, and for ensuring that the company capacity constraint is not violated.**

```sql
SELECT ea.employee_id, ea.company_id, ea.assignment_status, cc.max_employees FROM employee_assignment ea LEFT JOIN company_capacity cc ON ea.company_id = cc.company_id;
```

**Results (CSV format):**
```csv
employee_id,company_id,assignment_status,max_employees
1,1,1,10
2,2,0,15
3,3,1,20
```

**Query 8: This helps in understanding the current state of each company in terms of employee assignments and capacity.**

```sql
SELECT cc.company_id, cc.max_employees, COUNT(ea.employee_id) AS current_employees FROM company_capacity cc LEFT JOIN employee_assignment ea ON cc.company_id = ea.company_id AND ea.assignment_status = TRUE GROUP BY cc.company_id, cc.max_employees;
```

**Results (CSV format):**
```csv
company_id,max_employees,current_employees
1,10,1
2,15,0
3,20,1
```

