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

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary decision variable where \( x_{ij} = 1 \) if technician \( i \) is assigned to repair machine \( j \), and \( x_{ij} = 0 \) otherwise.

#### Objective Function
- Minimize the total value points of machines assigned to technicians:
  \[
  \text{Minimize } \sum_{j} \sum_{i} \text{value\_points}_j \times x_{ij}
  \]
  where \(\text{value\_points}_j\) is the value points of machine \( j \) from the `machine` table.

#### Constraints
1. **Each repair must be assigned to exactly one technician:**
   \[
   \sum_{i} x_{ij} = 1 \quad \forall j
   \]
   This ensures that each machine repair is assigned to exactly one technician.

2. **Technician capacity constraints:**
   \[
   \sum_{j} x_{ij} \leq \text{max\_repairs}_i \quad \forall i
   \]
   where \(\text{max\_repairs}_i\) is the maximum number of repairs technician \( i \) can handle from the `constraint_bounds` table.

3. **Binary constraints for decision variables:**
   \[
   x_{ij} \in \{0, 1\} \quad \forall i, j
   \]

Data Source Verification:
- **Objective Function Coefficients:** \(\text{value\_points}_j\) are sourced from `machine.value_points`.
- **Technician Capacity Constraints:** \(\text{max\_repairs}_i\) are sourced from `constraint_bounds.max_repairs`.

This formulation provides a complete and immediately solvable linear optimization model, ensuring that all machine repairs are assigned efficiently while respecting technician capacity constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_machine_repair():
    """Optimize technician assignments to minimize total value points of machine repairs."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("machine_repair")
    
    # Data from the database
    machines = [(1, 15), (2, 25), (3, 10)]  # (machine_id, value_points)
    repair_assignments = [(1, 101, True), (2, 102, True), (3, 103, False), (1, 103, True), (2, 101, False)]
    constraint_bounds = [(1, 3), (2, 2), (3, 1)]  # (technician_id, max_repairs)
    
    # Extracting data for optimization
    machine_ids = [m[0] for m in machines]
    value_points = {m[0]: m[1] for m in machines}
    technician_ids = list(set([r[0] for r in repair_assignments]))
    max_repairs = {c[0]: c[1] for c in constraint_bounds}
    
    # Validate array lengths
    assert len(machine_ids) == len(value_points), "Array length mismatch for machines"
    assert len(technician_ids) == len(max_repairs), "Array length mismatch for technicians"
    
    # 2. VARIABLES
    x = model.addVars(technician_ids, machine_ids, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(value_points[j] * x[i, j] for i in technician_ids for j in machine_ids), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    
    # Each repair must be assigned to exactly one technician
    model.addConstrs((gp.quicksum(x[i, j] for i in technician_ids) == 1 for j in machine_ids), name="assign_once")
    
    # Technician capacity constraints
    model.addConstrs((gp.quicksum(x[i, j] for j in machine_ids) <= max_repairs[i] for i in technician_ids), name="capacity")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in technician_ids:
            for j in machine_ids:
                if x[i, j].x > 1e-6:
                    print(f"Technician {i} assigned to machine {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_machine_repair()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 50.0
**Execution Time**: 1.67 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the lowest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def machine_repair_optimization():
    """Optimize technician assignments to minimize total value points of machine repairs."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="machine_repair")
    
    # Data from the problem context
    machine_ids = [1, 2, 3]
    value_points = [15, 25, 10]
    technician_ids = [1, 2, 3]
    max_repairs = [3, 2, 1]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(machine_ids) == len(value_points), "Machine data length mismatch"
    assert len(technician_ids) == len(max_repairs), "Technician data length mismatch"
    
    # Safe indexing
    safe_machine_range = range(len(machine_ids))
    safe_technician_range = range(len(technician_ids))
    
    # 2. VARIABLES
    # Binary decision variables for technician assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in safe_technician_range for j in safe_machine_range}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total value points of machines assigned to technicians
    objective = mdl.sum(value_points[j] * x[i, j] for i in safe_technician_range for j in safe_machine_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Each repair must be assigned to exactly one technician
    for j in safe_machine_range:
        mdl.add_constraint(mdl.sum(x[i, j] for i in safe_technician_range) == 1, ctname=f"repair_assignment_{j}")
    
    # Technician capacity constraints
    for i in safe_technician_range:
        mdl.add_constraint(mdl.sum(x[i, j] for j in safe_machine_range) <= max_repairs[i], ctname=f"capacity_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_technician_range:
            for j in safe_machine_range:
                if solution.get_value(x[i, j]) > 1e-6:
                    print(f"Technician {technician_ids[i]} assigned to machine {machine_ids[j]}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
machine_repair_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 50.0
**Execution Time**: 6.61 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy, but still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def machine_repair_optimization():
    """Optimize technician assignments to minimize machine repair value points"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data based on the provided database schema and current stored values
    machines = [1, 2, 3]
    technicians = [1, 2, 3]
    value_points = {1: 15, 2: 25, 3: 10}
    max_repairs = {1: 3, 2: 2, 3: 1}
    
    # CRITICAL: Validate data lengths before indexing
    assert len(machines) == len(value_points), "Mismatch in machines and value points data"
    assert len(technicians) == len(max_repairs), "Mismatch in technicians and max repairs data"
    
    # 3. SETS
    model.M = pyo.Set(initialize=machines)  # Machines
    model.T = pyo.Set(initialize=technicians)  # Technicians
    
    # 4. PARAMETERS
    model.value_points = pyo.Param(model.M, initialize=value_points)
    model.max_repairs = pyo.Param(model.T, initialize=max_repairs)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.T, model.M, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.value_points[m] * model.x[t, m] for t in model.T for m in model.M)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Each repair must be assigned to exactly one technician
    def repair_assignment_rule(model, m):
        return sum(model.x[t, m] for t in model.T) == 1
    model.repair_assignment_constraint = pyo.Constraint(model.M, rule=repair_assignment_rule)
    
    # Technician capacity constraints
    def technician_capacity_rule(model, t):
        return sum(model.x[t, m] for m in model.M) <= model.max_repairs[t]
    model.technician_capacity_constraint = pyo.Constraint(model.T, rule=technician_capacity_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)
    
    # 9. RESULT PROCESSING
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nAssignment of technicians to repairs:")
        for t in model.T:
            for m in model.M:
                if pyo.value(model.x[t, m]) > 0.5:  # Binary variable, check if assigned
                    print(f"Technician {t} assigned to machine {m}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Run the optimization
machine_repair_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 50.0
**Execution Time**: 4.33 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, demonstrating reliability similar to the other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 50.00 | 1.67s | N/A | N/A |
| Docplex | OPTIMAL | 50.00 | 6.61s | N/A | N/A |
| Pyomo | OPTIMAL | 50.00 | 4.33s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 50.0

### Final Recommendation
**Recommended Optimal Value**: 50.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its faster execution time while maintaining the same optimal value as other solvers, making it the most efficient choice.

### Business Interpretation
**Overall Strategy**: The optimal assignment of technicians to machines minimizes the total value points, ensuring efficient resource utilization.
**Objective Value Meaning**: The optimal objective value of 50.0 indicates the minimum total value points for machine repairs, reflecting cost-effective operations.
**Resource Allocation Summary**: Technicians are assigned to machines in a way that minimizes total value points while respecting capacity constraints.
**Implementation Recommendations**: Implement the solution by assigning technicians according to the optimal decision variables, ensuring adherence to capacity constraints and minimizing operational costs.