# Complete Optimization Problem and Solution: school_bus

## 1. Problem Context and Goals

### Context  
The school bus operation aims to efficiently allocate full-time drivers to various schools. The primary decision involves determining which drivers are assigned to which schools. Each driver can be assigned to only one school, and the assignment is represented as a binary decision variable. The operational goal is to minimize the mismatch in years of experience between drivers and the schools they are assigned to. This mismatch is quantified and used as a coefficient in the optimization process. 

Each school has a specific requirement for the number of drivers, which must be met. Additionally, only full-time drivers are considered for assignments to ensure that the schools' needs are met consistently. The business configuration includes scalar parameters such as the number of drivers required by each school and whether a driver is full-time, which are crucial for forming the constraints in the optimization model. These parameters are set to reflect typical operational needs and ensure the problem remains challenging yet solvable.

### Goals  
The primary goal of this optimization problem is to minimize the total mismatch in years of experience between the drivers and the schools they are assigned to. This is achieved by carefully selecting driver assignments that reduce the overall experience mismatch. The success of this optimization is measured by how well the assignments minimize the total experience mismatch, aligning with the coefficients derived from the experience mismatch data. The objective is clearly defined in linear terms, focusing on reducing the sum of mismatches across all driver-school assignments.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the solution is feasible and meets the operational requirements:

- Each driver can be assigned to at most one school, and this constraint applies only to full-time drivers. This ensures that drivers are not over-allocated and that only eligible drivers are considered for assignments.
  
- Each school must receive the exact number of drivers it requires. This constraint ensures that the operational needs of each school are met, maintaining the balance between supply and demand.

These constraints are expressed in linear terms, focusing on the allocation of drivers to schools without involving any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a new table for years of experience mismatch and updating existing tables to fill mapping gaps. Business configuration logic is updated to handle scalar parameters and formulas.

CREATE TABLE DriverAssignments (
  DriverID INTEGER,
  SchoolID INTEGER,
  Assigned BOOLEAN
);

CREATE TABLE SchoolRequirements (
  SchoolID INTEGER,
  RequiredDrivers INTEGER
);

CREATE TABLE Drivers (
  DriverID INTEGER,
  IsFullTime BOOLEAN
);

CREATE TABLE ExperienceMismatch (
  DriverID INTEGER,
  SchoolID INTEGER,
  YearsMismatch INTEGER
);
```

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

- **DriverAssignments**: This table tracks which drivers are assigned to which schools. It plays a crucial role in the decision-making process, with each assignment represented as a binary variable indicating whether a driver is assigned to a school.

- **SchoolRequirements**: This table stores the number of drivers required by each school. It is essential for defining the constraints that ensure each school receives the necessary number of drivers.

- **Drivers**: This table contains information about the drivers, specifically whether they are full-time. This information is used to filter eligible drivers for assignments, ensuring that only full-time drivers are considered.

- **ExperienceMismatch**: This table records the years of experience mismatch between drivers and schools. It provides the coefficients used in the objective function to minimize the total experience mismatch.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical school bus operations, ensuring a mix of driver experience levels and school requirements to create a realistic and challenging optimization problem.

-- Realistic data for DriverAssignments
INSERT INTO DriverAssignments (DriverID, SchoolID, Assigned) VALUES (1, 101, False);
INSERT INTO DriverAssignments (DriverID, SchoolID, Assigned) VALUES (2, 102, True);
INSERT INTO DriverAssignments (DriverID, SchoolID, Assigned) VALUES (3, 103, True);

-- Realistic data for SchoolRequirements
INSERT INTO SchoolRequirements (SchoolID, RequiredDrivers) VALUES (101, 3);
INSERT INTO SchoolRequirements (SchoolID, RequiredDrivers) VALUES (102, 4);
INSERT INTO SchoolRequirements (SchoolID, RequiredDrivers) VALUES (103, 5);

-- Realistic data for Drivers
INSERT INTO Drivers (DriverID, IsFullTime) VALUES (1, True);
INSERT INTO Drivers (DriverID, IsFullTime) VALUES (2, True);
INSERT INTO Drivers (DriverID, IsFullTime) VALUES (3, True);

-- Realistic data for ExperienceMismatch
INSERT INTO ExperienceMismatch (DriverID, SchoolID, YearsMismatch) VALUES (1, 101, 2);
INSERT INTO ExperienceMismatch (DriverID, SchoolID, YearsMismatch) VALUES (2, 102, 1);
INSERT INTO ExperienceMismatch (DriverID, SchoolID, YearsMismatch) VALUES (3, 103, 0);
```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Minimize the total experience mismatch across all driver-school assignments:

\[
\text{Minimize} \quad \sum_{i,j} \text{YearsMismatch}_{ij} \times x_{ij}
\]

Where:
- \(\text{YearsMismatch}_{ij}\) is the mismatch in years of experience between driver \( i \) and school \( j \), sourced from the `ExperienceMismatch` table.

#### Constraints
1. **Each driver can be assigned to at most one school:**

\[
\sum_{j} x_{ij} \leq 1 \quad \forall i \text{ such that } \text{IsFullTime}_i = \text{True}
\]

Where:
- \(\text{IsFullTime}_i\) is a boolean indicating if driver \( i \) is full-time, sourced from the `Drivers` table.

2. **Each school must receive the exact number of drivers it requires:**

\[
\sum_{i} x_{ij} = \text{RequiredDrivers}_j \quad \forall j
\]

Where:
- \(\text{RequiredDrivers}_j\) is the number of drivers required by school \( j \), sourced from the `SchoolRequirements` table.

#### Data Source Verification:
- \(\text{YearsMismatch}_{ij}\) is sourced from `ExperienceMismatch.YearsMismatch`.
- \(\text{IsFullTime}_i\) is sourced from `Drivers.IsFullTime`.
- \(\text{RequiredDrivers}_j\) is sourced from `SchoolRequirements.RequiredDrivers`.

This formulation ensures that the assignment of drivers to schools minimizes the total experience mismatch while satisfying all operational constraints. The model is linear and can be solved using standard linear programming or mixed-integer programming solvers.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation - Retry Attempt 4

import gurobipy as gp
from gurobipy import GRB

def school_bus_optimization():
    """Optimize school bus driver assignments to minimize experience mismatch."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("school_bus")
    
    # Data from the database schema
    drivers = [1, 2, 3]
    schools = [101, 102, 103]
    
    # Experience mismatch data
    years_mismatch = {
        (1, 101): 2,
        (2, 102): 1,
        (3, 103): 0
    }
    
    # Full-time status of drivers
    is_full_time = {
        1: True,
        2: True,
        3: True
    }
    
    # School requirements
    required_drivers = {
        101: 3,
        102: 4,
        103: 5
    }
    
    # Validate data lengths
    assert len(drivers) == len(is_full_time), "Driver data length mismatch"
    assert len(schools) == len(required_drivers), "School data length mismatch"
    
    # 2. VARIABLES
    x = model.addVars(drivers, schools, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(
        gp.quicksum(years_mismatch.get((i, j), 0) * x[i, j] for i in drivers for j in schools),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Each driver can be assigned to at most one school
    for i in drivers:
        if is_full_time[i]:
            model.addConstr(gp.quicksum(x[i, j] for j in schools) <= 1, name=f"driver_{i}_assignment")
    
    # Each school must receive the exact number of drivers it requires
    for j in schools:
        model.addConstr(gp.quicksum(x[i, j] for i in drivers) == required_drivers[j], name=f"school_{j}_requirement")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in drivers:
            for j in schools:
                if x[i, j].x > 0.5:  # Binary variable threshold
                    print(f"Driver {i} assigned to School {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    else:
        print("Optimization ended with status", model.status)

# Run the optimization
school_bus_optimization()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Gurobipy consistently reports the problem as infeasible, indicating a potential issue with the model constraints or data.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation - Retry Attempt 4

from docplex.mp.model import Model

def school_bus_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="school_bus")
    
    # Data from the problem context
    drivers = [1, 2, 3]
    schools = [101, 102, 103]
    is_full_time = {1: True, 2: True, 3: True}
    required_drivers = {101: 3, 102: 4, 103: 5}
    years_mismatch = {
        (1, 101): 2, (1, 102): 0, (1, 103): 0,
        (2, 101): 0, (2, 102): 1, (2, 103): 0,
        (3, 101): 0, (3, 102): 0, (3, 103): 0
    }
    
    # Validate data lengths
    assert len(drivers) == len(is_full_time)
    assert len(schools) == len(required_drivers)
    
    # 2. VARIABLES
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in drivers for j in schools}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(years_mismatch[i, j] * x[i, j] for i in drivers for j in schools)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    # Each driver can be assigned to at most one school
    for i in drivers:
        if is_full_time[i]:
            mdl.add_constraint(mdl.sum(x[i, j] for j in schools) <= 1, ctname=f"driver_{i}_assignment")
    
    # Each school must receive the exact number of drivers it requires
    for j in schools:
        mdl.add_constraint(mdl.sum(x[i, j] for i in drivers) == required_drivers[j], ctname=f"school_{j}_requirement")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in drivers:
            for j in schools:
                if solution.get_value(x[i, j]) > 0.5:  # Binary variable threshold
                    print(f"Driver {i} assigned to School {j}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
school_bus_optimization()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: DOCplex also reports infeasibility, suggesting that the issue is likely with the model formulation or input data rather than the solver.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation - Retry Attempt 4

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

def school_bus_optimization():
    """Optimize school bus driver assignments to minimize experience mismatch."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data based on the problem context
    drivers = [1, 2, 3]
    schools = [101, 102, 103]
    is_full_time = {1: True, 2: True, 3: True}
    required_drivers = {101: 3, 102: 4, 103: 5}
    years_mismatch = {
        (1, 101): 2, (1, 102): 3, (1, 103): 4,
        (2, 101): 5, (2, 102): 1, (2, 103): 2,
        (3, 101): 3, (3, 102): 4, (3, 103): 0
    }
    
    # CRITICAL: Validate data lengths
    assert len(drivers) == len(is_full_time), "Driver data length mismatch"
    assert len(schools) == len(required_drivers), "School data length mismatch"
    
    # 3. SETS
    model.D = pyo.Set(initialize=drivers)
    model.S = pyo.Set(initialize=schools)
    
    # 4. PARAMETERS
    model.is_full_time = pyo.Param(model.D, initialize=is_full_time)
    model.required_drivers = pyo.Param(model.S, initialize=required_drivers)
    model.years_mismatch = pyo.Param(model.D * model.S, initialize=years_mismatch)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.D, model.S, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.years_mismatch[i, j] * model.x[i, j] for i in model.D for j in model.S)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Each driver can be assigned to at most one school
    def driver_assignment_rule(model, i):
        if model.is_full_time[i]:
            return sum(model.x[i, j] for j in model.S) <= 1
        else:
            return pyo.Constraint.Skip
    model.driver_assignment_constraint = pyo.Constraint(model.D, rule=driver_assignment_rule)
    
    # Each school must receive the exact number of drivers it requires
    def school_requirement_rule(model, j):
        return sum(model.x[i, j] for i in model.D) == model.required_drivers[j]
    model.school_requirement_constraint = pyo.Constraint(model.S, rule=school_requirement_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("\nDriver assignments:")
        for i in model.D:
            for j in model.S:
                if pyo.value(model.x[i, j]) > 0.5:  # Binary variable check
                    print(f"Driver {i} assigned to School {j}")
        
    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

# Execute the optimization
school_bus_optimization()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Pyomo's infeasibility result aligns with the other solvers, reinforcing the likelihood of a model or data issue.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | INFEASIBLE | N/A | 0.55s | N/A | 4 |
| Docplex | INFEASIBLE | N/A | 4.88s | N/A | 4 |
| Pyomo | INFEASIBLE | N/A | 3.62s | N/A | 4 |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Solver Retry Summary**: gurobipy: 4 attempts, docplex: 4 attempts, pyomo: 4 attempts

### Final Recommendation
**Confidence Level**: LOW
**Preferred Solver(s)**: multiple
**Reasoning**: All solvers consistently report infeasibility, suggesting the issue is not solver-specific but related to the model or data.

### Business Interpretation
**Overall Strategy**: The current model cannot provide a feasible solution, indicating a need to revisit the data and constraints.
**Objective Value Meaning**: The objective value represents the total experience mismatch, which should be minimized for optimal driver-school assignments.
**Resource Allocation Summary**: Currently, no feasible allocation of drivers to schools meets all constraints.
**Implementation Recommendations**: Review and verify the input data for accuracy, particularly the ExperienceMismatch, Drivers, and SchoolRequirements tables. Ensure that the constraints accurately reflect the business requirements and consider relaxing constraints if necessary.