# Complete Optimization Problem and Solution: pilot_record

## 1. Problem Context and Goals

### Context  
The business objective is to optimize the assignment of pilots to aircraft in a manner that minimizes the total number of flights. This involves making decisions about the number of flights assigned to each pilot for specific aircraft, represented as integer values. The operational parameters are designed to align with a linear objective, focusing on minimizing the total flights across all assignments. Each pilot is required to fly a minimum number of flights annually, ensuring they maintain proficiency and meet industry standards. Additionally, each aircraft has a maximum flight capacity to balance operational efficiency and maintenance needs. These parameters are critical in forming the constraints of the optimization model, ensuring that the problem remains linear and avoids nonlinear relationships. The business configuration includes scalar parameters such as the minimum number of flights each pilot must fly and the maximum number of flights an aircraft can be assigned, which are used as lower and upper bound constraints, respectively.

### Goals  
The primary goal of this optimization problem is to minimize the total number of flights assigned to pilots. This is achieved by strategically assigning flights to pilots and aircraft while adhering to the constraints of minimum and maximum flight requirements. The success of this optimization is measured by the reduction in total flights, aligning with the expected coefficient sources. The goal is articulated in natural language to emphasize the linear nature of the optimization objective, focusing on minimizing the total flights assigned.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each pilot meets their minimum flight requirement and that no aircraft exceeds its maximum flight capacity. Specifically, the constraints are:

- Each pilot must be assigned a number of flights that meets or exceeds their minimum required flights. This ensures that pilots maintain their proficiency and comply with industry standards.
- Each aircraft must not be assigned more flights than its maximum capacity, ensuring operational efficiency and adherence to maintenance schedules.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE flights_assigned (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  number_of_flights INTEGER
);

CREATE TABLE pilot_constraints (
  pilot_id INTEGER,
  min_flights INTEGER
);

CREATE TABLE aircraft_constraints (
  aircraft_id INTEGER,
  max_flights INTEGER
);
```

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

- **Flights Assigned Table**: This table tracks the assignment of flights to pilots and aircraft. It plays a crucial role in the optimization model as it contains the decision variables, which are the number of flights assigned to each pilot for a specific aircraft.
  - **Pilot ID**: Serves as a unique identifier for each pilot, linking them to their flight assignments.
  - **Aircraft ID**: Serves as a unique identifier for each aircraft, linking them to their flight assignments.
  - **Number of Flights**: Represents the number of flights assigned to a pilot for a specific aircraft, serving as the decision variable in the optimization model.

- **Pilot Constraints Table**: This table stores the minimum flight requirements for each pilot, serving as a lower bound constraint in the optimization model.
  - **Pilot ID**: Links to the pilot in the constraints, ensuring each pilot meets their minimum flight requirement.
  - **Minimum Flights**: Represents the minimum number of flights required for each pilot, ensuring compliance with industry standards.

- **Aircraft Constraints Table**: This table stores the maximum flight limits for each aircraft, serving as an upper bound constraint in the optimization model.
  - **Aircraft ID**: Links to the aircraft in the constraints, ensuring each aircraft does not exceed its maximum flight capacity.
  - **Maximum Flights**: Represents the maximum number of flights allowed for each aircraft, balancing operational efficiency and maintenance requirements.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical industry standards for pilot flight assignments and aircraft utilization, ensuring a balance between pilot workload and aircraft capacity.

-- Realistic data for flights_assigned
INSERT INTO flights_assigned (pilot_id, aircraft_id, number_of_flights) VALUES (1, 101, 12);
INSERT INTO flights_assigned (pilot_id, aircraft_id, number_of_flights) VALUES (2, 102, 18);
INSERT INTO flights_assigned (pilot_id, aircraft_id, number_of_flights) VALUES (3, 103, 15);

-- Realistic data for pilot_constraints
INSERT INTO pilot_constraints (pilot_id, min_flights) VALUES (1, 10);
INSERT INTO pilot_constraints (pilot_id, min_flights) VALUES (2, 15);
INSERT INTO pilot_constraints (pilot_id, min_flights) VALUES (3, 12);

-- Realistic data for aircraft_constraints
INSERT INTO aircraft_constraints (aircraft_id, max_flights) VALUES (101, 120);
INSERT INTO aircraft_constraints (aircraft_id, max_flights) VALUES (102, 100);
INSERT INTO aircraft_constraints (aircraft_id, max_flights) VALUES (103, 110);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be the number of flights assigned to pilot \( i \) for aircraft \( j \).
  - \( x_{ij} \) is an integer decision variable representing the number of flights.

#### Objective Function
- Minimize the total number of flights assigned:
  \[
  \text{Minimize } \sum_{i,j} x_{ij}
  \]

#### Constraints
1. **Pilot Minimum Flight Requirement:**
   - Each pilot must be assigned at least their minimum required flights:
   \[
   \sum_{j} x_{ij} \geq \text{min\_flights}_i \quad \forall i
   \]
   - Data Source Verification: \( \text{min\_flights}_i \) from `pilot_constraints.min_flights`

2. **Aircraft Maximum Flight Capacity:**
   - Each aircraft must not exceed its maximum flight capacity:
   \[
   \sum_{i} x_{ij} \leq \text{max\_flights}_j \quad \forall j
   \]
   - Data Source Verification: \( \text{max\_flights}_j \) from `aircraft_constraints.max_flights`

#### Data Source Verification
- Decision Variables \( x_{ij} \) are derived from `flights_assigned.number_of_flights`.
- Coefficients for constraints are sourced from:
  - `pilot_constraints.min_flights` for minimum flight requirements.
  - `aircraft_constraints.max_flights` for maximum flight capacities.

This linear model is immediately solvable using standard linear programming solvers, ensuring that all pilots meet their minimum flight requirements while no aircraft exceeds its maximum capacity, thereby minimizing the total number of flights assigned.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_pilot_assignment():
    """Optimize the assignment of pilots to aircraft to minimize total flights."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("pilot_record")
    
    # Data from the database
    pilots = [1, 2, 3]
    aircrafts = [101, 102, 103]
    
    # Minimum flights required for each pilot
    min_flights = {1: 10, 2: 15, 3: 12}
    
    # Maximum flights allowed for each aircraft
    max_flights = {101: 120, 102: 100, 103: 110}
    
    # Validate data lengths
    assert len(min_flights) == len(pilots), "Mismatch in pilots and min_flights data"
    assert len(max_flights) == len(aircrafts), "Mismatch in aircrafts and max_flights data"
    
    # 2. VARIABLES
    # Decision variables: number of flights assigned to each pilot for each aircraft
    x = model.addVars(pilots, aircrafts, vtype=GRB.INTEGER, name="x", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total number of flights assigned
    model.setObjective(gp.quicksum(x[i, j] for i in pilots for j in aircrafts), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    # Pilot minimum flight requirement
    model.addConstrs((gp.quicksum(x[i, j] for j in aircrafts) >= min_flights[i] for i in pilots), name="pilot_min_flights")
    
    # Aircraft maximum flight capacity
    model.addConstrs((gp.quicksum(x[i, j] for i in pilots) <= max_flights[j] for j in aircrafts), name="aircraft_max_flights")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in pilots:
            for j in aircrafts:
                if x[i, j].x > 1e-6:
                    print(f"x[{i},{j}] = {x[i, j].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_pilot_assignment()
```

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

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_pilot_assignments():
    """Optimize the assignment of pilots to aircraft to minimize total flights."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="pilot_record")
    
    # Data from the database schema
    pilots = [1, 2, 3]
    aircrafts = [101, 102, 103]
    
    # Minimum flights required for each pilot
    min_flights = {1: 10, 2: 15, 3: 12}
    
    # Maximum flights allowed for each aircraft
    max_flights = {101: 120, 102: 100, 103: 110}
    
    # Validate array lengths to prevent IndexError
    assert len(pilots) == len(min_flights), "Mismatch in pilots and min_flights"
    assert len(aircrafts) == len(max_flights), "Mismatch in aircrafts and max_flights"
    
    # Safe indexing range
    safe_pilot_range = range(len(pilots))
    safe_aircraft_range = range(len(aircrafts))
    
    # 2. VARIABLES
    # Decision variables: number of flights assigned to each pilot for each aircraft
    x = {(i, j): mdl.integer_var(name=f"x_{i}_{j}", lb=0) for i in pilots for j in aircrafts}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total number of flights assigned
    objective = mdl.sum(x[i, j] for i in pilots for j in aircrafts)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Pilot minimum flight requirement
    for i in pilots:
        mdl.add_constraint(mdl.sum(x[i, j] for j in aircrafts) >= min_flights[i], ctname=f"min_flights_{i}")
    
    # Aircraft maximum flight capacity
    for j in aircrafts:
        mdl.add_constraint(mdl.sum(x[i, j] for i in pilots) <= max_flights[j], ctname=f"max_flights_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in pilots:
            for j in aircrafts:
                value = solution.get_value(x[i, j])
                if value > 1e-6:
                    print(f"x[{i},{j}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
optimize_pilot_assignments()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 37.0
**Execution Time**: 10.02 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 pilot_record_optimization():
    """Optimize pilot flight assignments to minimize total flights"""

    # 1. MODEL CREATION
    model = pyo.ConcreteModel()

    # 2. DATA SETUP
    pilots = [1, 2, 3]
    aircrafts = [101, 102, 103]
    min_flights = {1: 10, 2: 15, 3: 12}
    max_flights = {101: 120, 102: 100, 103: 110}

    # CRITICAL: Validate data lengths before indexing
    assert len(min_flights) == len(pilots), "Mismatch in pilots and min_flights data"
    assert len(max_flights) == len(aircrafts), "Mismatch in aircrafts and max_flights data"

    # 3. SETS
    model.P = pyo.Set(initialize=pilots)
    model.A = pyo.Set(initialize=aircrafts)

    # 4. PARAMETERS
    model.min_flights = pyo.Param(model.P, initialize=min_flights)
    model.max_flights = pyo.Param(model.A, initialize=max_flights)

    # 5. VARIABLES
    model.x = pyo.Var(model.P, model.A, within=pyo.NonNegativeIntegers)

    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.x[i, j] for i in model.P for j in model.A)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)

    # 7. CONSTRAINTS

    # Pilot Minimum Flight Requirement
    def pilot_min_flights_rule(model, i):
        return sum(model.x[i, j] for j in model.A) >= model.min_flights[i]
    model.pilot_min_flights_constraint = pyo.Constraint(model.P, rule=pilot_min_flights_rule)

    # Aircraft Maximum Flight Capacity
    def aircraft_max_flights_rule(model, j):
        return sum(model.x[i, j] for i in model.P) <= model.max_flights[j]
    model.aircraft_max_flights_constraint = pyo.Constraint(model.A, rule=aircraft_max_flights_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("\nFlight assignments:")
        for i in model.P:
            for j in model.A:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0:  # Only print non-zero values
                    print(f"Flights assigned to pilot {i} for aircraft {j}: {x_val}")

    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
pilot_record_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 37.0
**Execution Time**: 8.30 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 | 37.00 | 2.55s | N/A | N/A |
| Docplex | OPTIMAL | 37.00 | 10.02s | N/A | N/A |
| Pyomo | OPTIMAL | 37.00 | 8.30s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 37.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its faster execution time, which is beneficial for operational efficiency in real-time decision-making scenarios.

### Business Interpretation
**Overall Strategy**: The optimal assignment of flights minimizes the total number of flights while meeting all pilot and aircraft constraints.
**Objective Value Meaning**: The optimal objective value of 37 indicates the minimum total number of flights assigned across all pilots and aircraft, ensuring efficient resource utilization.
**Resource Allocation Summary**: Resources should be allocated such that each pilot meets their minimum flight requirements without exceeding aircraft capacities.
**Implementation Recommendations**: Implement the solution by assigning flights according to the optimal decision variables, ensuring compliance with all constraints and monitoring for any changes in pilot or aircraft availability.