# Complete Optimization Problem and Solution: flight_1

## 1. Problem Context and Goals

### Context  
An airline is tasked with optimizing its flight operations to minimize costs while ensuring operational efficiency. The airline must decide which flights to operate and which employees to assign to those flights, considering aircraft availability and employee certifications. Each flight has an associated operational cost, and each employee has a fixed salary. The goal is to minimize the total cost, which includes the sum of flight operational costs and employee salaries.  

The airline operates flights between specific origin and destination pairs, with a maximum number of flights allowed for each pair based on aircraft capacity. Additionally, every flight that is operated must be staffed with certified employees. The operational cost of each flight is determined by factors such as fuel, maintenance, and other expenses, while employee salaries are based on industry standards for pilots and cabin crew.  

The problem is formulated as a linear optimization model to ensure computational efficiency and scalability. The decision variables include whether to operate a specific flight and whether to assign a specific employee to a flight. The objective is to minimize the total cost, which is a linear combination of flight operational costs and employee salaries.  

### Goals  
The primary goal of this optimization problem is to minimize the total operational cost for the airline. This cost includes the sum of the operational costs for all flights that are operated and the salaries of all employees assigned to those flights. Success is measured by achieving the lowest possible total cost while ensuring that all operational constraints, such as aircraft capacity and employee staffing requirements, are satisfied.  

## 2. Constraints  

1. **Flight Capacity Constraint**: The number of flights operated between any origin and destination pair must not exceed the maximum number of flights allowed for that pair. This ensures that aircraft are not overutilized and that operational limits are respected.  

2. **Employee Assignment Constraint**: Every flight that is operated must be staffed with at least one certified employee. This ensures that all flights have the necessary personnel to operate safely and efficiently.  

These constraints are designed to ensure that the airline's operations remain within feasible limits while minimizing costs.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for flight costs and employee salaries to address missing optimization requirements. Business configuration logic updated to include scalar parameters for flight costs and employee salaries.

CREATE TABLE aircraft_capacity (
  origin STRING,
  destination STRING,
  max_flights INTEGER
);

CREATE TABLE employee_assignment (
  eid INTEGER,
  flno INTEGER
);

CREATE TABLE flight_operation (
  flno INTEGER
);
```

### Data Dictionary  
- **aircraft_capacity**:  
  - **Business Purpose**: Specifies the maximum number of flights allowed between origin and destination pairs based on aircraft availability.  
  - **Columns**:  
    - **origin**: The airport code for the origin of the flight.  
    - **destination**: The airport code for the destination of the flight.  
    - **max_flights**: The maximum number of flights allowed between the origin and destination.  

- **employee_assignment**:  
  - **Business Purpose**: Tracks which employees are assigned to which flights.  
  - **Columns**:  
    - **eid**: The unique identifier for an employee.  
    - **flno**: The flight number to which the employee is assigned.  

- **flight_operation**:  
  - **Business Purpose**: Tracks which flights are operated.  
  - **Columns**:  
    - **flno**: The unique identifier for a flight.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for flight costs and employee salaries, ensuring they align with typical airline operations. Flight numbers and employee IDs were generated sequentially to maintain consistency.

-- Realistic data for aircraft_capacity
INSERT INTO aircraft_capacity (origin, destination, max_flights) VALUES ('JFK', 'LAX', 8);
INSERT INTO aircraft_capacity (origin, destination, max_flights) VALUES ('LAX', 'JFK', 8);
INSERT INTO aircraft_capacity (origin, destination, max_flights) VALUES ('ORD', 'DFW', 5);

-- Realistic data for employee_assignment
INSERT INTO employee_assignment (eid, flno) VALUES (1, 101);
INSERT INTO employee_assignment (eid, flno) VALUES (2, 102);
INSERT INTO employee_assignment (eid, flno) VALUES (3, 103);

-- Realistic data for flight_operation
INSERT INTO flight_operation (flno) VALUES (101);
INSERT INTO flight_operation (flno) VALUES (102);
INSERT INTO flight_operation (flno) VALUES (103);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_f \): Binary decision variable indicating whether flight \( f \) is operated (\( x_f = 1 \)) or not (\( x_f = 0 \)).  
  - **Domain**: \( x_f \in \{0, 1\} \) for all flights \( f \).  
  - **Data Source**: `flight_operation.flno`.  

- \( y_{e,f} \): Binary decision variable indicating whether employee \( e \) is assigned to flight \( f \) (\( y_{e,f} = 1 \)) or not (\( y_{e,f} = 0 \)).  
  - **Domain**: \( y_{e,f} \in \{0, 1\} \) for all employees \( e \) and flights \( f \).  
  - **Data Source**: `employee_assignment.eid` and `employee_assignment.flno`.  

#### Objective Function
Minimize the total cost, which includes the sum of flight operational costs and employee salaries:  
\[
\text{Minimize } Z = \sum_{f} c_f \cdot x_f + \sum_{e} s_e \cdot \sum_{f} y_{e,f}
\]  
- \( c_f \): Operational cost of flight \( f \).  
  - **Data Source**: Business configuration parameter for flight costs.  
- \( s_e \): Salary of employee \( e \).  
  - **Data Source**: Business configuration parameter for employee salaries.  

#### Constraints
1. **Flight Capacity Constraint**: The number of flights operated between any origin \( o \) and destination \( d \) must not exceed the maximum number of flights allowed for that pair:  
\[
\sum_{f \in F_{o,d}} x_f \leq \text{max\_flights}_{o,d} \quad \forall (o, d)
\]  
- \( F_{o,d} \): Set of flights between origin \( o \) and destination \( d \).  
- \( \text{max\_flights}_{o,d} \): Maximum number of flights allowed between \( o \) and \( d \).  
  - **Data Source**: `aircraft_capacity.max_flights`.  

2. **Employee Assignment Constraint**: Every flight that is operated must be staffed with at least one certified employee:  
\[
\sum_{e} y_{e,f} \geq x_f \quad \forall f
\]  
- Ensures that if \( x_f = 1 \) (flight \( f \) is operated), at least one employee is assigned to it.  

#### Data Source Verification
- \( c_f \): Business configuration parameter for flight costs.  
- \( s_e \): Business configuration parameter for employee salaries.  
- \( \text{max\_flights}_{o,d} \): `aircraft_capacity.max_flights`.  
- \( x_f \): `flight_operation.flno`.  
- \( y_{e,f} \): `employee_assignment.eid` and `employee_assignment.flno`.  

This formulation provides a complete, immediately solvable linear optimization model with all numerical coefficients explicitly mapped to their respective data sources.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Flight Optimization Problem
"""

import gurobipy as gp
from gurobipy import GRB

def flight_optimization():
    """Optimize flight operations to minimize costs while ensuring operational efficiency."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("flight_optimization")
    
    # Example data (replace with actual data from database)
    flights = [101, 102, 103]
    employees = [1, 2, 3]
    origins = ['JFK', 'LAX', 'ORD']
    destinations = ['LAX', 'JFK', 'DFW']
    max_flights = {'JFK-LAX': 8, 'LAX-JFK': 8, 'ORD-DFW': 5}
    flight_costs = {101: 5000, 102: 6000, 103: 5500}
    employee_salaries = {1: 3000, 2: 3500, 3: 3200}
    
    # CRITICAL: Validate array lengths before loops
    assert len(flights) == len(flight_costs), "Flight data length mismatch"
    assert len(employees) == len(employee_salaries), "Employee data length mismatch"
    
    # 2. VARIABLES
    # Decision variables
    x = {f: model.addVar(vtype=GRB.BINARY, name=f"x_{f}") for f in flights}
    y = {(e, f): model.addVar(vtype=GRB.BINARY, name=f"y_{e}_{f}") for e in employees for f in flights}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total cost: sum of flight operational costs and employee salaries
    model.setObjective(
        gp.quicksum(flight_costs[f] * x[f] for f in flights) +
        gp.quicksum(employee_salaries[e] * y[e, f] for e in employees for f in flights),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Flight Capacity Constraint
    for o in origins:
        for d in destinations:
            key = f"{o}-{d}"
            if key in max_flights:
                model.addConstr(
                    gp.quicksum(x[f] for f in flights if f in flight_costs) <= max_flights[key],
                    name=f"capacity_{o}_{d}"
                )
    
    # Employee Assignment Constraint
    for f in flights:
        model.addConstr(
            gp.quicksum(y[e, f] for e in employees) >= x[f],
            name=f"assignment_{f}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for f in flights:
            if x[f].x > 1e-6:
                print(f"Flight {f} is operated.")
        for e in employees:
            for f in flights:
                if y[e, f].x > 1e-6:
                    print(f"Employee {e} is assigned to flight {f}.")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
if __name__ == "__main__":
    flight_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution with a total cost of 0.0 in the shortest execution time (0.18 seconds). This indicates high efficiency and reliability.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCPLEX implementation for airline flight optimization problem
"""

from docplex.mp.model import Model

def optimize_flight_operations():
    """Optimize flight operations to minimize total cost"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="flight_optimization")
    
    # Sample data (replace with actual data loading)
    flight_costs = {101: 5000, 102: 6000, 103: 5500}  # Operational cost per flight
    employee_salaries = {1: 3000, 2: 3500, 3: 3200}  # Salary per employee
    
    # Flight capacity data
    aircraft_capacity = {
        ('JFK', 'LAX'): 8,
        ('LAX', 'JFK'): 8,
        ('ORD', 'DFW'): 5
    }
    
    # Employee assignment data
    employee_assignment = [
        (1, 101),
        (2, 102),
        (3, 103)
    ]
    
    # Extract unique flights and employees
    flights = list(flight_costs.keys())
    employees = list(employee_salaries.keys())
    
    # Validate array lengths
    assert len(flight_costs) == len(flights), "Flight cost data mismatch"
    assert len(employee_salaries) == len(employees), "Employee salary data mismatch"
    
    # 2. VARIABLES
    # Decision variables
    x = {f: mdl.binary_var(name=f"x_{f}") for f in flights}  # Flight operation
    y = {(e, f): mdl.binary_var(name=f"y_{e}_{f}") for e in employees for f in flights}  # Employee assignment
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total cost: flight operational costs + employee salaries
    total_flight_cost = mdl.sum(flight_costs[f] * x[f] for f in flights)
    total_employee_cost = mdl.sum(employee_salaries[e] * y[(e, f)] for e in employees for f in flights)
    mdl.minimize(total_flight_cost + total_employee_cost)
    
    # 4. CONSTRAINTS
    
    # Flight Capacity Constraint
    # For each origin-destination pair, sum of flights <= max_flights
    # Assuming flights are grouped by origin-destination (simplified for example)
    for (origin, dest), max_flights in aircraft_capacity.items():
        relevant_flights = [f for f in flights if (origin, dest) in aircraft_capacity]  # Simplified
        mdl.add_constraint(mdl.sum(x[f] for f in relevant_flights) <= max_flights, ctname=f"capacity_{origin}_{dest}")
    
    # Employee Assignment Constraint
    # Each operated flight must have at least one assigned employee
    for f in flights:
        mdl.add_constraint(mdl.sum(y[(e, f)] for e in employees) >= x[f], ctname=f"staffing_{f}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for f in flights:
            if solution.get_value(x[f]) > 0.5:
                print(f"Flight {f} is operated")
                assigned_employees = [e for e in employees if solution.get_value(y[(e, f)]) > 0.5]
                print(f"Assigned employees: {assigned_employees}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
if __name__ == "__main__":
    optimize_flight_operations()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 1.17 seconds
**Reliability**: medium
**Analysis**: DOCplex also found an optimal solution with a total cost of 0.0, but it took significantly longer (1.17 seconds) compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def flight_optimization():
    """Optimize flight operations to minimize costs while ensuring operational efficiency."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Flight operational costs (example data)
    flight_costs = {101: 5000, 102: 4500, 103: 4800}
    
    # Employee salaries (example data)
    employee_salaries = {1: 3000, 2: 3200, 3: 3100}
    
    # Aircraft capacity data (example data)
    aircraft_capacity = {('JFK', 'LAX'): 8, ('LAX', 'JFK'): 8, ('ORD', 'DFW'): 5}
    
    # Flight origin-destination pairs (example data)
    flight_od_pairs = {101: ('JFK', 'LAX'), 102: ('LAX', 'JFK'), 103: ('ORD', 'DFW')}
    
    # Employee assignments (example data)
    employee_assignments = [(1, 101), (2, 102), (3, 103)]
    
    # Validate array lengths before indexing
    assert len(flight_costs) == len(flight_od_pairs), "Flight data length mismatch"
    assert len(employee_salaries) == len(set(eid for eid, _ in employee_assignments)), "Employee data length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.F = pyo.Set(initialize=flight_costs.keys())  # Set of flights
    model.E = pyo.Set(initialize=employee_salaries.keys())  # Set of employees
    model.OD = pyo.Set(initialize=aircraft_capacity.keys())  # Set of origin-destination pairs
    
    # 4. PARAMETERS (data containers)
    model.cost_f = pyo.Param(model.F, initialize=flight_costs)  # Flight operational costs
    model.salary_e = pyo.Param(model.E, initialize=employee_salaries)  # Employee salaries
    model.max_flights_od = pyo.Param(model.OD, initialize=aircraft_capacity)  # Maximum flights per OD pair
    model.flight_od = pyo.Param(model.F, initialize=flight_od_pairs)  # Flight origin-destination pairs
    
    # 5. VARIABLES
    # Binary decision variable for operating flights
    model.x_f = pyo.Var(model.F, within=pyo.Binary)
    
    # Binary decision variable for assigning employees to flights
    model.y_ef = pyo.Var(model.E, model.F, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost_f[f] * model.x_f[f] for f in model.F) + \
               sum(model.salary_e[e] * sum(model.y_ef[e, f] for f in model.F) for e in model.E)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Flight Capacity Constraint
    def flight_capacity_rule(model, o, d):
        return sum(model.x_f[f] for f in model.F if model.flight_od[f] == (o, d)) <= model.max_flights_od[o, d]
    model.flight_capacity_constraint = pyo.Constraint(model.OD, rule=flight_capacity_rule)
    
    # Employee Assignment Constraint
    def employee_assignment_rule(model, f):
        return sum(model.y_ef[e, f] for e in model.E) >= model.x_f[f]
    model.employee_assignment_constraint = pyo.Constraint(model.F, rule=employee_assignment_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    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("\nFlight operations:")
        for f in model.F:
            if pyo.value(model.x_f[f]) > 0.5:  # Only print operated flights
                print(f"Flight {f} is operated.")
        
        print("\nEmployee assignments:")
        for e in model.E:
            for f in model.F:
                if pyo.value(model.y_ef[e, f]) > 0.5:  # Only print assigned employees
                    print(f"Employee {e} is assigned to flight {f}.")
        
    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
if __name__ == "__main__":
    flight_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 1.15 seconds
**Reliability**: medium
**Analysis**: Pyomo found an optimal solution with a total cost of 0.0, but its execution time (1.15 seconds) was similar to DOCplex, making it less efficient than Gurobipy.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 0.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 0.00 | 1.17s | N/A | N/A |
| Pyomo | OPTIMAL | 0.00 | 1.15s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 0.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its significantly faster execution time while achieving the same optimal result as the other solvers.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests that the total cost of operating flights and assigning employees is minimized to 0.0, which may indicate that no flights are being operated or that the costs are fully offset by some other factor.
**Objective Value Meaning**: A total cost of 0.0 implies that no flights are being operated or that the operational costs and employee salaries are fully offset, possibly due to external factors or constraints.
**Resource Allocation Summary**: No flights are recommended to be operated, and no employees are assigned to flights, resulting in zero costs.
**Implementation Recommendations**: Review the constraints and data sources to ensure the model accurately reflects the business scenario. If the result is unexpected, investigate potential issues in the input data or constraints.