# Complete Optimization Problem and Solution: flight_1

## 1. Problem Context and Goals

### Context  
An airline company is focused on optimizing its flight scheduling to minimize operational costs while ensuring that all flights are adequately covered by available aircraft and certified employees. The decision-making process involves assigning each flight to a specific aircraft and employee, represented by a binary decision variable indicating whether a flight is assigned to a particular aircraft and employee. The operational parameters are aligned with the objective of minimizing the total cost, which includes the cost of operating flights and employee salaries. The business configuration includes a maximum allowable budget for employee salaries to ensure that salary costs do not exceed the budget. The optimization problem is structured to adhere to linear relationships, avoiding any nonlinear scenarios such as variable products or divisions. The constraints are based on the aircraft's distance capabilities and budget limits, ensuring that the optimization remains feasible and realistic.

### Goals  
The primary goal of the optimization is to minimize the total operational cost associated with flight scheduling. This involves reducing the combined cost of operating flights and the salaries of employees assigned to these flights. Success is measured by achieving the lowest possible total cost while adhering to the constraints of aircraft capabilities and budget limits. The optimization goal is clearly defined in linear terms, focusing on minimizing the sum of flight operation costs and employee salaries.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the solution is feasible and aligns with business requirements:

- Each flight must be assigned to exactly one aircraft and one employee. This ensures that all flights are covered without overlap or omission.
- The total distance covered by the assigned aircraft for each flight must not exceed the aircraft's maximum distance capability. This constraint ensures that aircraft are not overburdened beyond their operational limits.
- The total salary cost of employees assigned to flights must not exceed the predefined budget constraint. This ensures that the airline operates within its financial limits.
- The decision variable indicating flight assignments is binary, meaning it can only take values of true or false, ensuring clear and unambiguous assignment decisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating business configuration logic for budget constraints. Adjustments ensure all optimization requirements are met and data is organized according to best practices.

CREATE TABLE flight (
  flno INTEGER,
  price FLOAT,
  distance FLOAT
);

CREATE TABLE aircraft (
  aid INTEGER,
  distance FLOAT
);

CREATE TABLE employee (
  eid INTEGER,
  salary FLOAT
);

CREATE TABLE flight_assignment (
  flno INTEGER,
  aid INTEGER,
  eid INTEGER,
  binary_decision BOOLEAN
);
```

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

- **Flight Table**: Contains information about each flight, including a unique identifier (flno), the cost of operating the flight (price), and the distance that needs to be covered. This data is crucial for determining the cost coefficients in the optimization objective and setting constraints on aircraft assignments.

- **Aircraft Table**: Stores details about each aircraft, including a unique identifier (aid) and the maximum distance it can cover. This information is used to set constraints ensuring that aircraft are not assigned to flights beyond their capabilities.

- **Employee Table**: Includes data on employees, with a unique identifier (eid) and their salary. Employee salary data is used to calculate the cost component of the optimization objective and to enforce budget constraints.

- **Flight Assignment Table**: Tracks the assignment of flights to aircraft and employees, using a binary decision variable to indicate whether a specific flight is assigned to a particular aircraft and employee. This table is central to the decision-making process in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical airline operational costs, aircraft capabilities, and employee salary ranges to ensure a realistic and solvable optimization problem.

-- Realistic data for flight
INSERT INTO flight (flno, price, distance) VALUES (101, 5000.0, 300.0);
INSERT INTO flight (flno, price, distance) VALUES (102, 7500.0, 450.0);
INSERT INTO flight (flno, price, distance) VALUES (103, 10000.0, 600.0);

-- Realistic data for aircraft
INSERT INTO aircraft (aid, distance) VALUES (1, 500.0);
INSERT INTO aircraft (aid, distance) VALUES (2, 700.0);
INSERT INTO aircraft (aid, distance) VALUES (3, 1000.0);

-- Realistic data for employee
INSERT INTO employee (eid, salary) VALUES (10, 3000.0);
INSERT INTO employee (eid, salary) VALUES (20, 4000.0);
INSERT INTO employee (eid, salary) VALUES (30, 5000.0);

-- Realistic data for flight_assignment
INSERT INTO flight_assignment (flno, aid, eid, binary_decision) VALUES (101, 1, 10, True);
INSERT INTO flight_assignment (flno, aid, eid, binary_decision) VALUES (102, 2, 20, True);
INSERT INTO flight_assignment (flno, aid, eid, binary_decision) VALUES (103, 3, 30, True);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{f,a,e} \): Binary decision variable indicating whether flight \( f \) is assigned to aircraft \( a \) and employee \( e \). \( x_{f,a,e} \in \{0, 1\} \).

#### Objective Function
Minimize the total operational cost, which includes the cost of operating flights and employee salaries:

\[
\text{Minimize } \sum_{f \in F} \sum_{a \in A} \sum_{e \in E} (c_f + s_e) \cdot x_{f,a,e}
\]

Where:
- \( c_f \) is the cost of operating flight \( f \) (from `flight.price`).
- \( s_e \) is the salary of employee \( e \) (from `employee.salary`).

#### Constraints

1. **Flight Assignment Constraint**: Each flight must be assigned to exactly one aircraft and one employee.

\[
\sum_{a \in A} \sum_{e \in E} x_{f,a,e} = 1, \quad \forall f \in F
\]

2. **Aircraft Distance Capability Constraint**: The total distance covered by the assigned aircraft for each flight must not exceed the aircraft's maximum distance capability.

\[
\sum_{f \in F} d_f \cdot x_{f,a,e} \leq D_a, \quad \forall a \in A, \forall e \in E
\]

Where:
- \( d_f \) is the distance of flight \( f \) (from `flight.distance`).
- \( D_a \) is the maximum distance capability of aircraft \( a \) (from `aircraft.distance`).

3. **Employee Salary Budget Constraint**: The total salary cost of employees assigned to flights must not exceed the predefined budget.

\[
\sum_{f \in F} \sum_{a \in A} \sum_{e \in E} s_e \cdot x_{f,a,e} \leq \text{Budget}
\]

Where:
- Budget is a predefined constant representing the maximum allowable budget for employee salaries.

#### Data Source Verification:
- \( c_f \) from `flight.price`
- \( d_f \) from `flight.distance`
- \( D_a \) from `aircraft.distance`
- \( s_e \) from `employee.salary`
- Budget is a business configuration parameter

This formulation ensures that all flights are covered by exactly one aircraft and one employee, respects the aircraft's operational limits, and adheres to the salary budget constraint, all while minimizing the total operational cost.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_flight_schedule():
    """Optimize flight scheduling to minimize operational costs."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("flight_optimization")
    
    # Data from the problem context
    flights = [
        {"flno": 101, "price": 5000.0, "distance": 300.0},
        {"flno": 102, "price": 7500.0, "distance": 450.0},
        {"flno": 103, "price": 10000.0, "distance": 600.0}
    ]
    
    aircrafts = [
        {"aid": 1, "distance": 500.0},
        {"aid": 2, "distance": 700.0},
        {"aid": 3, "distance": 1000.0}
    ]
    
    employees = [
        {"eid": 10, "salary": 3000.0},
        {"eid": 20, "salary": 4000.0},
        {"eid": 30, "salary": 5000.0}
    ]
    
    budget = 12000.0  # Predefined budget for employee salaries
    
    # Validate data lengths
    n_flights = len(flights)
    n_aircrafts = len(aircrafts)
    n_employees = len(employees)
    
    assert n_flights > 0 and n_aircrafts > 0 and n_employees > 0, "Data arrays must not be empty"
    
    # 2. VARIABLES
    x = model.addVars(n_flights, n_aircrafts, n_employees, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(
        gp.quicksum(
            (flights[f]["price"] + employees[e]["salary"]) * x[f, a, e]
            for f in range(n_flights)
            for a in range(n_aircrafts)
            for e in range(n_employees)
        ),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Flight Assignment Constraint
    model.addConstrs(
        (gp.quicksum(x[f, a, e] for a in range(n_aircrafts) for e in range(n_employees)) == 1
         for f in range(n_flights)),
        name="flight_assignment"
    )
    
    # Aircraft Distance Capability Constraint
    model.addConstrs(
        (gp.quicksum(flights[f]["distance"] * x[f, a, e] for f in range(n_flights)) <= aircrafts[a]["distance"]
         for a in range(n_aircrafts) for e in range(n_employees)),
        name="aircraft_distance"
    )
    
    # Employee Salary Budget Constraint
    model.addConstr(
        gp.quicksum(employees[e]["salary"] * x[f, a, e] for f in range(n_flights) for a in range(n_aircrafts) for e in range(n_employees)) <= budget,
        name="salary_budget"
    )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for f in range(n_flights):
            for a in range(n_aircrafts):
                for e in range(n_employees):
                    if x[f, a, e].x > 1e-6:
                        print(f"Flight {flights[f]['flno']} assigned to Aircraft {aircrafts[a]['aid']} and Employee {employees[e]['eid']}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_flight_schedule()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 31500.0
**Execution Time**: 0.28 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 flight_optimization():
    """Flight scheduling optimization using DOCPLEX"""

    # 1. MODEL & DATA SETUP
    mdl = Model(name="flight_optimization")

    # Data from the problem context
    flights = [101, 102, 103]
    aircrafts = [1, 2, 3]
    employees = [10, 20, 30]
    flight_prices = {101: 5000.0, 102: 7500.0, 103: 10000.0}
    flight_distances = {101: 300.0, 102: 450.0, 103: 600.0}
    aircraft_distances = {1: 500.0, 2: 700.0, 3: 1000.0}
    employee_salaries = {10: 3000.0, 20: 4000.0, 30: 5000.0}
    budget = 12000.0  # Example budget constraint

    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(flights) == len(flight_prices) == len(flight_distances), "Flight data length mismatch"
    assert len(aircrafts) == len(aircraft_distances), "Aircraft data length mismatch"
    assert len(employees) == len(employee_salaries), "Employee data length mismatch"

    # Safe indexing
    safe_flights = range(len(flights))
    safe_aircrafts = range(len(aircrafts))
    safe_employees = range(len(employees))

    # 2. VARIABLES
    # Decision variables: x[f, a, e] is 1 if flight f is assigned to aircraft a and employee e
    x = {(f, a, e): mdl.binary_var(name=f"x_{f}_{a}_{e}") for f in flights for a in aircrafts for e in employees}

    # 3. OBJECTIVE FUNCTION
    # Minimize total operational cost
    objective = mdl.sum((flight_prices[f] + employee_salaries[e]) * x[f, a, e] for f in flights for a in aircrafts for e in employees)
    mdl.minimize(objective)

    # 4. CONSTRAINTS

    # Flight Assignment Constraint: Each flight must be assigned to exactly one aircraft and one employee
    for f in flights:
        mdl.add_constraint(mdl.sum(x[f, a, e] for a in aircrafts for e in employees) == 1, ctname=f"assign_flight_{f}")

    # Aircraft Distance Capability Constraint
    for a in aircrafts:
        for e in employees:
            mdl.add_constraint(mdl.sum(flight_distances[f] * x[f, a, e] for f in flights) <= aircraft_distances[a], ctname=f"distance_cap_{a}_{e}")

    # Employee Salary Budget Constraint
    mdl.add_constraint(mdl.sum(employee_salaries[e] * x[f, a, e] for f in flights for a in aircrafts for e in employees) <= budget, ctname="salary_budget")

    # 5. SOLVING & RESULTS
    solution = mdl.solve()

    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for f in flights:
            for a in aircrafts:
                for e in employees:
                    if solution.get_value(x[f, a, e]) > 0.5:  # Check if the binary variable is effectively 1
                        print(f"Flight {f} assigned to Aircraft {a} and Employee {e}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
flight_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 31500.0
**Execution Time**: 1.18 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, though with a slightly longer execution time compared to Gurobipy. It is still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def flight_optimization():
    """Flight scheduling optimization using Pyomo"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    flights = [101, 102, 103]
    aircrafts = [1, 2, 3]
    employees = [10, 20, 30]
    
    flight_prices = {101: 5000.0, 102: 7500.0, 103: 10000.0}
    flight_distances = {101: 300.0, 102: 450.0, 103: 600.0}
    
    aircraft_distances = {1: 500.0, 2: 700.0, 3: 1000.0}
    
    employee_salaries = {10: 3000.0, 20: 4000.0, 30: 5000.0}
    
    budget = 12000.0  # Example budget constraint
    
    # CRITICAL: Validate array lengths before indexing
    assert len(flights) == len(flight_prices) == len(flight_distances), "Flight data length mismatch"
    assert len(aircrafts) == len(aircraft_distances), "Aircraft data length mismatch"
    assert len(employees) == len(employee_salaries), "Employee data length mismatch"
    
    # 3. SETS
    model.F = pyo.Set(initialize=flights)
    model.A = pyo.Set(initialize=aircrafts)
    model.E = pyo.Set(initialize=employees)
    
    # 4. PARAMETERS
    model.price = pyo.Param(model.F, initialize=flight_prices)
    model.distance = pyo.Param(model.F, initialize=flight_distances)
    model.aircraft_distance = pyo.Param(model.A, initialize=aircraft_distances)
    model.salary = pyo.Param(model.E, initialize=employee_salaries)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.F, model.A, model.E, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum((model.price[f] + model.salary[e]) * model.x[f, a, e] for f in model.F for a in model.A for e in model.E)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Flight Assignment Constraint
    def flight_assignment_rule(model, f):
        return sum(model.x[f, a, e] for a in model.A for e in model.E) == 1
    model.flight_assignment_constraint = pyo.Constraint(model.F, rule=flight_assignment_rule)
    
    # Aircraft Distance Capability Constraint
    def aircraft_distance_rule(model, a):
        return sum(model.distance[f] * model.x[f, a, e] for f in model.F for e in model.E) <= model.aircraft_distance[a]
    model.aircraft_distance_constraint = pyo.Constraint(model.A, rule=aircraft_distance_rule)
    
    # Employee Salary Budget Constraint
    def salary_budget_rule(model):
        return sum(model.salary[e] * model.x[f, a, e] for f in model.F for a in model.A for e in model.E) <= budget
    model.salary_budget_constraint = pyo.Constraint(rule=salary_budget_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # 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):.2f}")
        
        # Extract variable values
        print("\nFlight Assignments:")
        for f in model.F:
            for a in model.A:
                for e in model.E:
                    if pyo.value(model.x[f, a, e]) > 0.5:  # Binary variable, check if assigned
                        print(f"Flight {f} assigned to Aircraft {a} and Employee {e}")
        
    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
flight_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 31500.0
**Execution Time**: 3.47 seconds
**Reliability**: medium
**Analysis**: Pyomo achieved an optimal solution but took significantly longer than the other solvers, which may indicate less efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 31500.00 | 0.28s | N/A | N/A |
| Docplex | OPTIMAL | 31500.00 | 1.18s | N/A | N/A |
| Pyomo | OPTIMAL | 31500.00 | 3.47s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 31500.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its high reliability and the fastest execution time, making it suitable for real-time or large-scale applications.

### Business Interpretation
**Overall Strategy**: The optimal solution minimizes the total operational cost effectively, ensuring all flights are covered within budget and operational constraints.
**Objective Value Meaning**: The optimal objective value of 31500.0 represents the minimized total cost of operations, balancing flight costs and employee salaries.
**Resource Allocation Summary**: Resources should be allocated to ensure each flight is covered by one aircraft and one employee, respecting distance and budget constraints.
**Implementation Recommendations**: Implement the solution by assigning flights according to the decision variables, ensuring compliance with operational and budgetary constraints.