# Complete Optimization Problem and Solution: machine_repair

## 1. Problem Context and Goals

### Context  
The company is focused on optimizing the assignment of technicians to machine repairs. The primary decision involves determining whether a technician is assigned to a specific repair task. This decision is represented by binary variables, where each variable indicates if a technician is assigned to a repair. The operational objective is to minimize the total value points associated with the machines being repaired. This involves assigning technicians in a way that reduces the overall value points, which are indicative of the machine's importance or urgency. The business configuration includes a critical parameter: the maximum number of repairs a technician can handle. This parameter is used as a constraint in the optimization model to ensure that no technician is overburdened. The data reflects current operational realities, focusing on precise decision-making that aligns with linear optimization principles. Resource limitations are expressed through linear constraints, ensuring that each repair is assigned to exactly one technician and that technicians do not exceed their repair capacity. The business configuration, including scalar parameters and logic, is integral to maintaining consistency and feasibility in the optimization model.

### Goals  
The primary goal of the optimization is to minimize the total value points of machines assigned to technicians for repair. This involves strategically assigning technicians to repairs in a manner that reduces the cumulative value points, thereby optimizing resource allocation and operational efficiency. Success is measured by the reduction in total value points, which directly correlates with the effectiveness of the repair assignments. The optimization goal is articulated in natural language, focusing on minimizing the sum of value points associated with the repair assignments, without resorting to mathematical notation.

## 2. Constraints    

The optimization model is subject to several constraints that ensure practical and efficient technician assignments. Each technician can only be assigned to a limited number of repairs, as dictated by the maximum repair capacity parameter. This constraint ensures that technicians are not overloaded and can perform their tasks effectively. Additionally, each repair must be assigned to exactly one technician, ensuring that all repair tasks are covered without duplication or omission. These constraints are expressed in business terms, naturally leading to linear mathematical forms that align with the operational requirements and resource limitations.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding a new table for constraint bounds, modifying existing tables for better mapping, and updating configuration logic for missing parameters.

CREATE TABLE machine (
  machine_id INTEGER,
  value_points INTEGER
);

CREATE TABLE repair_assignment (
  technician_id INTEGER,
  repair_id INTEGER,
  is_assigned BOOLEAN
);

CREATE TABLE constraint_bounds (
  technician_id INTEGER,
  max_repairs INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and roles in the optimization process:

- **Machine Table**: This table stores information about the machines that require repairs. Each machine is identified by a unique ID and has associated value points, which serve as coefficients in the optimization objective. The value points reflect the importance or urgency of repairing the machine.

- **Repair Assignment Table**: This table tracks the assignment of technicians to repair tasks. It includes identifiers for both technicians and repair tasks, along with a binary indicator showing whether a technician is assigned to a specific repair. This table plays a crucial role in defining the decision variables for the optimization model.

- **Constraint Bounds Table**: This table contains information about the maximum number of repairs each technician can handle. It ensures that the optimization model respects the capacity constraints of each technician, preventing over-assignment and maintaining operational feasibility.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical machine repair scenarios, technician workloads, and the need to balance repair assignments with technician capacity.

-- Realistic data for machine
INSERT INTO machine (machine_id, value_points) VALUES (1, 15);
INSERT INTO machine (machine_id, value_points) VALUES (2, 25);
INSERT INTO machine (machine_id, value_points) VALUES (3, 10);

-- Realistic data for repair_assignment
INSERT INTO repair_assignment (technician_id, repair_id, is_assigned) VALUES (1, 101, True);
INSERT INTO repair_assignment (technician_id, repair_id, is_assigned) VALUES (2, 102, True);
INSERT INTO repair_assignment (technician_id, repair_id, is_assigned) VALUES (3, 103, False);
INSERT INTO repair_assignment (technician_id, repair_id, is_assigned) VALUES (1, 103, True);
INSERT INTO repair_assignment (technician_id, repair_id, is_assigned) VALUES (2, 101, False);

-- Realistic data for constraint_bounds
INSERT INTO constraint_bounds (technician_id, max_repairs) VALUES (1, 3);
INSERT INTO constraint_bounds (technician_id, max_repairs) VALUES (2, 2);
INSERT INTO constraint_bounds (technician_id, max_repairs) VALUES (3, 1);
```