# Complete Optimization Problem and Solution: pilot_record

## 1. Problem Context and Goals

### Context  
The pilot assignment problem focuses on optimizing the allocation of pilots to aircraft to minimize operational costs while adhering to critical business rules. The decision involves determining whether a specific pilot is assigned to a specific aircraft, represented as a binary choice. The operational costs are derived from factors such as pilot rank, aircraft fuel efficiency, and experience, which are captured in the cost coefficients. 

Key business rules include:  
1. **Pilot Assignment Limit**: Each pilot can be assigned to at most one aircraft per day to ensure safety and manage workload. This limit is defined by a scalar parameter in the business configuration.  
2. **Aircraft Assignment Requirement**: Each aircraft must be assigned exactly one pilot to ensure operational readiness and safety. This requirement is also defined by a scalar parameter in the business configuration.  
3. **Pilot Qualifications**: Pilots can only be assigned to aircraft they are qualified to fly based on their rank and certification. This is enforced through a qualification indicator in the data.  

The problem is designed to ensure that all relationships and constraints are linear, avoiding scenarios that would require nonlinear relationships such as variable products or divisions. The data sources, including cost coefficients, pilot qualifications, and assignment limits, are structured to support a linear optimization formulation.

### Goals  
The primary goal of this optimization problem is to minimize the total operational cost of assigning pilots to aircraft. This cost is calculated as the sum of the cost coefficients for each pilot-aircraft assignment. Success is measured by achieving the lowest possible total cost while satisfying all business constraints, including pilot assignment limits, aircraft assignment requirements, and pilot qualifications. The optimization process ensures that the solution is both cost-effective and operationally feasible.

## 2. Constraints  

The optimization problem is subject to the following constraints, which are designed to align with linear mathematical forms:  

1. **Pilot Assignment Limit**: For each pilot, the total number of aircraft they are assigned to must not exceed the maximum limit defined in the business configuration. This ensures that no pilot is overburdened.  
2. **Aircraft Assignment Requirement**: For each aircraft, exactly one pilot must be assigned. This ensures that all aircraft are operational and ready for use.  
3. **Pilot Qualifications**: A pilot can only be assigned to an aircraft if they are qualified to fly it, as indicated by the qualification data. This ensures that assignments are safe and compliant with operational standards.  

These constraints are expressed in a way that naturally leads to linear relationships, avoiding any nonlinear complexities.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for pilot assignment limits and aircraft assignment requirements, and updating business configuration logic with scalar parameters for pilot assignment limits and aircraft assignment requirements.

CREATE TABLE cost_coefficients (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  cost_value FLOAT
);

CREATE TABLE pilot_qualifications (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  is_qualified BOOLEAN
);

CREATE TABLE pilot_assignments (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  assignment BOOLEAN
);
```

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

- **cost_coefficients**:  
  - **pilot_id**: Unique identifier for the pilot, used to link the cost coefficient to a specific pilot.  
  - **aircraft_id**: Unique identifier for the aircraft, used to link the cost coefficient to a specific aircraft.  
  - **cost_value**: The calculated cost of assigning a pilot to an aircraft, used in the objective function to minimize total costs.  

- **pilot_qualifications**:  
  - **pilot_id**: Unique identifier for the pilot, used to link the qualification to a specific pilot.  
  - **aircraft_id**: Unique identifier for the aircraft, used to link the qualification to a specific aircraft.  
  - **is_qualified**: Indicates whether the pilot is qualified to fly the aircraft, used in the qualification constraint.  

- **pilot_assignments**:  
  - **pilot_id**: Unique identifier for the pilot, used to link the assignment to a specific pilot.  
  - **aircraft_id**: Unique identifier for the aircraft, used to link the assignment to a specific aircraft.  
  - **assignment**: Indicates whether the pilot is assigned to the aircraft, used as the decision variable in the optimization problem.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic operational scenarios, considering pilot ranks, aircraft types, and cost factors such as fuel efficiency and pilot experience.

-- Realistic data for cost_coefficients
INSERT INTO cost_coefficients (pilot_id, aircraft_id, cost_value) VALUES (1, 101, 2.55);
INSERT INTO cost_coefficients (pilot_id, aircraft_id, cost_value) VALUES (2, 102, 3.4);
INSERT INTO cost_coefficients (pilot_id, aircraft_id, cost_value) VALUES (3, 103, 4.25);

-- Realistic data for pilot_qualifications
INSERT INTO pilot_qualifications (pilot_id, aircraft_id, is_qualified) VALUES (1, 101, True);
INSERT INTO pilot_qualifications (pilot_id, aircraft_id, is_qualified) VALUES (2, 102, True);
INSERT INTO pilot_qualifications (pilot_id, aircraft_id, is_qualified) VALUES (3, 103, False);

-- Realistic data for pilot_assignments
INSERT INTO pilot_assignments (pilot_id, aircraft_id, assignment) VALUES (1, 101, True);
INSERT INTO pilot_assignments (pilot_id, aircraft_id, assignment) VALUES (2, 102, True);
INSERT INTO pilot_assignments (pilot_id, aircraft_id, assignment) VALUES (3, 103, False);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_{p,a} \) be a binary decision variable where:
- \( x_{p,a} = 1 \) if pilot \( p \) is assigned to aircraft \( a \),
- \( x_{p,a} = 0 \) otherwise.

#### Objective Function
Minimize the total operational cost:
\[
\text{Minimize} \quad \sum_{p \in P} \sum_{a \in A} c_{p,a} \cdot x_{p,a}
\]
where \( c_{p,a} \) is the cost of assigning pilot \( p \) to aircraft \( a \).

#### Constraints
1. **Pilot Assignment Limit**: Each pilot can be assigned to at most one aircraft:
\[
\sum_{a \in A} x_{p,a} \leq 1 \quad \forall p \in P
\]

2. **Aircraft Assignment Requirement**: Each aircraft must be assigned exactly one pilot:
\[
\sum_{p \in P} x_{p,a} = 1 \quad \forall a \in A
\]

3. **Pilot Qualifications**: A pilot can only be assigned to an aircraft if they are qualified:
\[
x_{p,a} \leq q_{p,a} \quad \forall p \in P, \forall a \in A
\]
where \( q_{p,a} \) is a binary indicator of whether pilot \( p \) is qualified to fly aircraft \( a \).

#### Data Source Verification
- **Objective Function Coefficients**: \( c_{p,a} \) comes from `cost_coefficients.cost_value`.
- **Pilot Qualification Indicators**: \( q_{p,a} \) comes from `pilot_qualifications.is_qualified`.
- **Pilot Assignment Limit**: The right-hand side value of 1 is a scalar parameter from the business configuration.
- **Aircraft Assignment Requirement**: The right-hand side value of 1 is a scalar parameter from the business configuration.

### Numerical Example Based on Provided Data
Given the data:
- **Pilots**: \( P = \{1, 2, 3\} \)
- **Aircraft**: \( A = \{101, 102, 103\} \)
- **Cost Coefficients**:
  - \( c_{1,101} = 2.55 \)
  - \( c_{2,102} = 3.4 \)
  - \( c_{3,103} = 4.25 \)
- **Pilot Qualifications**:
  - \( q_{1,101} = 1 \)
  - \( q_{2,102} = 1 \)
  - \( q_{3,103} = 0 \)

The mathematical model becomes:

#### Objective Function
\[
\text{Minimize} \quad 2.55x_{1,101} + 3.4x_{2,102} + 4.25x_{3,103}
\]

#### Constraints
1. **Pilot Assignment Limit**:
\[
x_{1,101} + x_{1,102} + x_{1,103} \leq 1 \\
x_{2,101} + x_{2,102} + x_{2,103} \leq 1 \\
x_{3,101} + x_{3,102} + x_{3,103} \leq 1
\]

2. **Aircraft Assignment Requirement**:
\[
x_{1,101} + x_{2,101} + x_{3,101} = 1 \\
x_{1,102} + x_{2,102} + x_{3,102} = 1 \\
x_{1,103} + x_{2,103} + x_{3,103} = 1
\]

3. **Pilot Qualifications**:
\[
x_{1,101} \leq 1 \\
x_{2,102} \leq 1 \\
x_{3,103} \leq 0
\]

This is a complete, immediately solvable linear programming (LP) model.

## 5. Gurobipy Implementation

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

import gurobipy as gp
from gurobipy import GRB

def pilot_assignment_optimization():
    # 1. MODEL & DATA SETUP
    model = gp.Model("pilot_assignment")

    # Define sets
    pilots = [1, 2, 3]
    aircraft = [101, 102, 103]

    # Cost coefficients
    cost_coefficients = {
        (1, 101): 2.55,
        (2, 102): 3.4,
        (3, 103): 4.25
    }

    # Pilot qualifications
    pilot_qualifications = {
        (1, 101): 1,
        (2, 102): 1,
        (3, 103): 0
    }

    # Validate data consistency
    assert all((p, a) in cost_coefficients for p in pilots for a in aircraft), "Missing cost coefficients"
    assert all((p, a) in pilot_qualifications for p in pilots for a in aircraft), "Missing pilot qualifications"

    # 2. VARIABLES
    x = model.addVars(pilots, aircraft, vtype=GRB.BINARY, name="x")

    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(cost_coefficients[p, a] * x[p, a] for p in pilots for a in aircraft), GRB.MINIMIZE)

    # 4. CONSTRAINTS

    # Pilot Assignment Limit
    for p in pilots:
        model.addConstr(gp.quicksum(x[p, a] for a in aircraft) <= 1, name=f"pilot_limit_{p}")

    # Aircraft Assignment Requirement
    for a in aircraft:
        model.addConstr(gp.quicksum(x[p, a] for p in pilots) == 1, name=f"aircraft_requirement_{a}")

    # Pilot Qualifications
    for p in pilots:
        for a in aircraft:
            model.addConstr(x[p, a] <= pilot_qualifications[p, a], name=f"qualification_{p}_{a}")

    # 5. SOLVING & RESULTS
    model.optimize()

    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for p in pilots:
            for a in aircraft:
                if x[p, a].x > 1e-6:
                    print(f"Pilot {p} assigned to Aircraft {a}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")

    return model

# Execute the optimization
pilot_assignment_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmplpl739mc.py", line 70, in <module>
    pilot_assignment_optimization()
  File "/tmp/tmplpl739mc.py", line 29, in pilot_assignment_optimization
    assert all((p, a) in cost_coefficients for p in pilots for a in aircraft), "Missing cost coefficients"
AssertionError: Missing cost coefficients

**Analysis**: Gurobipy encountered an error due to missing cost coefficients. This indicates a data preparation issue rather than a solver limitation.

## 6. DOCplex Implementation

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

from docplex.mp.model import Model

def pilot_assignment_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="pilot_assignment_optimization")
    
    # Data from the problem
    pilots = [1, 2, 3]
    aircraft = [101, 102, 103]
    
    cost_coefficients = {
        (1, 101): 2.55,
        (2, 102): 3.4,
        (3, 103): 4.25
    }
    
    pilot_qualifications = {
        (1, 101): 1,
        (2, 102): 1,
        (3, 103): 0
    }
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert all((p, a) in cost_coefficients for p in pilots for a in aircraft), "Missing cost coefficients"
    assert all((p, a) in pilot_qualifications for p in pilots for a in aircraft), "Missing pilot qualifications"
    
    # 2. VARIABLES
    x = {(p, a): mdl.binary_var(name=f"x_{p}_{a}") for p in pilots for a in aircraft}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(cost_coefficients[(p, a)] * x[(p, a)] for p in pilots for a in aircraft)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Pilot Assignment Limit: Each pilot can be assigned to at most one aircraft
    for p in pilots:
        mdl.add_constraint(mdl.sum(x[(p, a)] for a in aircraft) <= 1, ctname=f"pilot_limit_{p}")
    
    # Aircraft Assignment Requirement: Each aircraft must be assigned exactly one pilot
    for a in aircraft:
        mdl.add_constraint(mdl.sum(x[(p, a)] for p in pilots) == 1, ctname=f"aircraft_req_{a}")
    
    # Pilot Qualifications: A pilot can only be assigned to an aircraft if they are qualified
    for p in pilots:
        for a in aircraft:
            mdl.add_constraint(x[(p, a)] <= pilot_qualifications[(p, a)], ctname=f"qualification_{p}_{a}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for p in pilots:
            for a in aircraft:
                if solution.get_value(x[(p, a)]) > 0:
                    print(f"Pilot {p} is assigned to Aircraft {a}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
pilot_assignment_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpv686vlmq.py", line 67, in <module>
    pilot_assignment_optimization()
  File "/tmp/tmpv686vlmq.py", line 26, in pilot_assignment_optimization
    assert all((p, a) in cost_coefficients for p in pilots for a in aircraft), "Missing cost coefficients"
AssertionError: Missing cost coefficients

**Analysis**: DOCplex also failed due to missing cost coefficients, highlighting a consistent data issue across solvers.

## 7. Pyomo Implementation

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

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

def pilot_assignment_optimization():
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    pilots = [1, 2, 3]
    aircraft = [101, 102, 103]
    
    cost_coefficients = {
        (1, 101): 2.55,
        (2, 102): 3.4,
        (3, 103): 4.25
    }
    
    pilot_qualifications = {
        (1, 101): 1,
        (2, 102): 1,
        (3, 103): 0
    }
    
    # CRITICAL: Validate array lengths before indexing
    assert all((p, a) in cost_coefficients for p in pilots for a in aircraft), "Missing cost coefficients"
    assert all((p, a) in pilot_qualifications for p in pilots for a in aircraft), "Missing pilot qualifications"
    
    # 3. SETS
    model.P = pyo.Set(initialize=pilots)
    model.A = pyo.Set(initialize=aircraft)
    
    # 4. PARAMETERS
    model.cost = pyo.Param(model.P, model.A, initialize=cost_coefficients)
    model.qualification = pyo.Param(model.P, model.A, initialize=pilot_qualifications)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.P, model.A, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost[p, a] * model.x[p, a] for p in model.P for a in model.A)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    # Pilot Assignment Limit
    def pilot_limit_rule(model, p):
        return sum(model.x[p, a] for a in model.A) <= 1
    model.pilot_limit = pyo.Constraint(model.P, rule=pilot_limit_rule)
    
    # Aircraft Assignment Requirement
    def aircraft_requirement_rule(model, a):
        return sum(model.x[p, a] for p in model.P) == 1
    model.aircraft_requirement = pyo.Constraint(model.A, rule=aircraft_requirement_rule)
    
    # Pilot Qualifications
    def qualification_rule(model, p, a):
        return model.x[p, a] <= model.qualification[p, a]
    model.qualification_constraint = pyo.Constraint(model.P, model.A, rule=qualification_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)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nVariable values:")
        for p in model.P:
            for a in model.A:
                if pyo.value(model.x[p, a]) > 0:
                    print(f"x[{p}, {a}] = {pyo.value(model.x[p, a])}")
        
    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
if __name__ == "__main__":
    pilot_assignment_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpfwtr6kcq.py", line 96, in <module>
    pilot_assignment_optimization()
  File "/tmp/tmpfwtr6kcq.py", line 27, in pilot_assignment_optimization
    assert all((p, a) in cost_coefficients for p in pilots for a in aircraft), "Missing cost coefficients"
AssertionError: Missing cost coefficients

**Analysis**: Pyomo faced the same error as the other solvers, confirming the problem lies in the input data rather than the solver.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | ERROR | N/A | 0.16s | N/A | 4 |
| Docplex | ERROR | N/A | 1.14s | N/A | 4 |
| Pyomo | ERROR | N/A | 0.86s | N/A | 4 |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Inconsistent Solvers**: gurobipy, docplex, pyomo
**Potential Issues**:
- Missing cost coefficients in the input data
**Solver Retry Summary**: gurobipy: 4 attempts, docplex: 4 attempts, pyomo: 4 attempts

### Final Recommendation
**Confidence Level**: LOW
**Preferred Solver(s)**: none
**Reasoning**: All solvers failed due to missing cost coefficients, indicating a critical data preparation issue that must be resolved before optimization can proceed.

### Business Interpretation
**Overall Strategy**: The optimization cannot proceed due to incomplete data, leading to potential inefficiencies in pilot assignments.
**Objective Value Meaning**: The total operational cost cannot be minimized due to incomplete data, leading to potential financial inefficiencies.
**Resource Allocation Summary**: Pilot assignments cannot be optimized until all cost coefficients are provided.
**Implementation Recommendations**: 1. Verify and complete the cost coefficients data. 2. Re-run the optimization with all necessary data. 3. Ensure data integrity for future optimizations.