# Complete Optimization Problem and Solution: customer_complaints

## 1. Problem Context and Goals

### Context  
The company operates in a customer service environment where handling complaints efficiently is critical to maintaining customer satisfaction and operational cost-effectiveness. The primary business decision revolves around assigning staff members to customer complaints in a way that minimizes the total cost of handling these complaints. The cost is directly influenced by the time each staff member spends on each complaint and their respective hourly wages. 

Each complaint has specific time requirements, defined by a minimum and maximum time needed for resolution. Staff members also have individual maximum working hours, ensuring that no one is overburdened. The goal is to allocate time spent by staff on complaints in a manner that respects these constraints while minimizing the total cost. This problem naturally lends itself to a linear optimization formulation, as the relationships between time spent, wages, and constraints are all proportional and additive.

### Goals  
The optimization goal is to minimize the total cost of handling customer complaints. This cost is calculated as the sum of the time each staff member spends on each complaint multiplied by their hourly wage. Success is measured by achieving the lowest possible total cost while ensuring that all complaints are fully addressed within the specified time bounds and that no staff member exceeds their maximum working hours. The objective is linear, as it involves summing products of time and wage, both of which are fixed parameters in this context.

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. **Complaint Assignment Constraint**: Each complaint must be fully assigned to one or more staff members, ensuring that the total time spent on each complaint sums to exactly one unit of time. This ensures that all complaints are fully addressed.  
2. **Time Bounds Constraint**: The time spent by any staff member on a specific complaint must fall within the predefined minimum and maximum time limits for that complaint. This ensures that complaints are neither rushed nor unnecessarily prolonged.  
3. **Staff Working Hours Constraint**: The total time spent by each staff member across all complaints must not exceed their maximum working hours. This ensures that no staff member is overworked.  

These constraints are linear, as they involve summing time spent and comparing it to fixed bounds, without any multiplicative or divisive relationships between variables.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a new table for the missing decision variable 'time_spent[staff_id, complaint_id]'. Configuration logic remains unchanged as no additional scalar parameters or formulas were identified.

CREATE TABLE staff_wages (
  staff_id INTEGER,
  hourly_wage FLOAT
);

CREATE TABLE complaint_time_constraints (
  complaint_id INTEGER,
  min_time FLOAT,
  max_time FLOAT
);

CREATE TABLE staff_working_hours (
  staff_id INTEGER,
  max_hours FLOAT
);

CREATE TABLE time_spent (
  staff_id INTEGER,
  complaint_id INTEGER,
  time_spent FLOAT
);
```

### Data Dictionary  
- **staff_wages**:  
  - **Purpose**: Stores the hourly wage of each staff member.  
  - **Optimization Role**: Provides the coefficients for the objective function, representing the cost per hour for each staff member.  
  - **Columns**:  
    - `staff_id`: Unique identifier for each staff member.  
    - `hourly_wage`: Hourly wage of the staff member, used to calculate the total cost.  

- **complaint_time_constraints**:  
  - **Purpose**: Defines the minimum and maximum time required to handle each complaint.  
  - **Optimization Role**: Provides the bounds for the time spent on each complaint, ensuring resolutions are neither rushed nor prolonged.  
  - **Columns**:  
    - `complaint_id`: Unique identifier for each complaint.  
    - `min_time`: Minimum time required to handle the complaint.  
    - `max_time`: Maximum time allowed to handle the complaint.  

- **staff_working_hours**:  
  - **Purpose**: Specifies the maximum working hours for each staff member.  
  - **Optimization Role**: Provides the upper bound for the total time each staff member can spend on all complaints.  
  - **Columns**:  
    - `staff_id`: Unique identifier for each staff member.  
    - `max_hours`: Maximum working hours for the staff member.  

- **time_spent**:  
  - **Purpose**: Tracks the time spent by each staff member on each complaint.  
  - **Optimization Role**: Represents the decision variable in the optimization model, determining how time is allocated.  
  - **Columns**:  
    - `staff_id`: Unique identifier for each staff member.  
    - `complaint_id`: Unique identifier for each complaint.  
    - `time_spent`: Time spent by the staff member on the complaint.  


### Retrieved Values

**Query 1: This data is crucial as it provides the coefficients for the objective function, representing the cost per hour for each staff member.**

```sql
SELECT staff_id, hourly_wage FROM staff_wages;
```

**Results (CSV format):**
```csv
staff_id,hourly_wage
1,25.0
2,30.0
3,35.0
```

**Query 2: This data ensures that the time spent on each complaint falls within the predefined limits, preventing rushed or prolonged resolutions.**

```sql
SELECT complaint_id, min_time, max_time FROM complaint_time_constraints;
```

**Results (CSV format):**
```csv
complaint_id,min_time,max_time
1,1.0,3.0
2,1.5,4.0
3,2.0,5.0
```

**Query 3: This data ensures that no staff member exceeds their maximum working hours, preventing overwork.**

```sql
SELECT staff_id, max_hours FROM staff_working_hours;
```

**Results (CSV format):**
```csv
staff_id,max_hours
1,40.0
2,45.0
3,50.0
```

**Query 4: This data is essential for determining how time is allocated across staff and complaints.**

```sql
SELECT staff_id, complaint_id, time_spent FROM time_spent;
```

**Results (CSV format):**
```csv
staff_id,complaint_id,time_spent
1,1,1.5
2,2,2.0
3,3,2.5
```

**Query 5: This aggregated data helps ensure that the total time spent by each staff member does not exceed their maximum working hours.**

```sql
SELECT staff_id, SUM(time_spent) AS total_time_spent FROM time_spent GROUP BY staff_id;
```

**Results (CSV format):**
```csv
staff_id,total_time_spent
1,1.5
2,2.0
3,2.5
```

**Query 6: This aggregated data ensures that each complaint is fully addressed by summing the time spent by all staff members on that complaint.**

```sql
SELECT complaint_id, SUM(time_spent) AS total_time_spent FROM time_spent GROUP BY complaint_id;
```

**Results (CSV format):**
```csv
complaint_id,total_time_spent
1,1.5
2,2.0
3,2.5
```

**Query 7: This joined data provides a comprehensive view of each staff member's cost per hour and their capacity, aiding in the optimization process.**

```sql
SELECT sw.staff_id, sw.hourly_wage, swh.max_hours FROM staff_wages sw JOIN staff_working_hours swh ON sw.staff_id = swh.staff_id;
```

**Results (CSV format):**
```csv
staff_id,hourly_wage,max_hours
1,25.0,40.0
2,30.0,45.0
3,35.0,50.0
```

**Query 8: This joined data helps ensure that the time spent on each complaint falls within the predefined minimum and maximum limits.**

```sql
SELECT ctc.complaint_id, ctc.min_time, ctc.max_time, ts.time_spent FROM complaint_time_constraints ctc JOIN time_spent ts ON ctc.complaint_id = ts.complaint_id;
```

**Results (CSV format):**
```csv
complaint_id,min_time,max_time,time_spent
1,1.0,3.0,1.5
2,1.5,4.0,2.0
3,2.0,5.0,2.5
```

**Query 9: This joined data provides a complete picture of the optimization problem, including the objective function coefficients and all constraints.**

```sql
SELECT sw.staff_id, sw.hourly_wage, swh.max_hours, ctc.complaint_id, ctc.min_time, ctc.max_time, ts.time_spent FROM staff_wages sw JOIN staff_working_hours swh ON sw.staff_id = swh.staff_id JOIN time_spent ts ON sw.staff_id = ts.staff_id JOIN complaint_time_constraints ctc ON ts.complaint_id = ctc.complaint_id;
```

**Results (CSV format):**
```csv
staff_id,hourly_wage,max_hours,complaint_id,min_time,max_time,time_spent
1,25.0,40.0,1,1.0,3.0,1.5
2,30.0,45.0,2,1.5,4.0,2.0
3,35.0,50.0,3,2.0,5.0,2.5
```

