# Complete Optimization Problem and Solution: flight_2

## 1. Problem Context and Goals

### Context  
An airline company is focused on optimizing its flight operations to minimize costs while ensuring efficient use of airport resources. The company operates flights on specific routes, each with an associated cost of operation. The primary decision involves determining the number of flights to operate on each route, which must be an integer value. The cost of operating a flight on a specific route is a key operational parameter, directly influencing the total operating cost. Additionally, each airport has a maximum capacity for handling flights, which limits the number of flights that can depart from or arrive at that airport. The business logic for calculating the total operating cost is straightforward: it is the sum of the cost of operating each flight across all routes. This scenario naturally aligns with a linear optimization formulation, as the relationships between costs, flight counts, and airport capacities are proportional and additive, without any nonlinear interactions such as variable products or divisions.

### Goals  
The primary goal of this optimization problem is to minimize the total cost of operating all flights. This is achieved by determining the optimal number of flights to operate on each route, ensuring that the sum of the costs for all flights is as low as possible. Success is measured by achieving the lowest possible total operating cost while adhering to the constraints of airport capacities and ensuring that each route is covered by at least one flight. The metric to optimize is the total cost, which is calculated by multiplying the cost of operating a flight on each route by the number of flights on that route and summing these values across all routes.

## 2. Constraints    

The optimization problem is subject to the following constraints:  
1. **Route Coverage**: Each route must be covered by at least one flight. This ensures that all routes are operational and no route is left unserved.  
2. **Airport Departure Capacity**: The total number of flights departing from any given airport must not exceed the airport's capacity. This ensures that the airport's resources are not overburdened.  
3. **Airport Arrival Capacity**: The total number of flights arriving at any given airport must not exceed the airport's capacity. This ensures that the airport's resources are not overburdened from incoming flights.  

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and tractable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for cost_per_flight and airport_capacity, modifying the flights table to better map decision variables, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE cost_per_flight (
  route STRING,
  cost INTEGER
);

CREATE TABLE airport_capacity (
  airport STRING,
  capacity INTEGER
);

CREATE TABLE flights (
  route STRING,
  flight_count INTEGER
);
```

### Data Dictionary  
- **cost_per_flight**: This table contains the cost of operating a flight on each specific route. The `route` column identifies the flight route, and the `cost` column specifies the cost of operating a flight on that route. This data is used as coefficients in the objective function to calculate the total operating cost.  
- **airport_capacity**: This table defines the maximum number of flights that each airport can handle. The `airport` column identifies the airport, and the `capacity` column specifies the maximum number of flights. This data is used as bounds in the constraints to ensure that airport capacities are not exceeded.  
- **flights**: This table represents the decision variables in the optimization problem. The `route` column identifies the flight route, and the `flight_count` column specifies the number of flights to operate on that route. This data is used to determine the optimal number of flights for each route.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards, realistic flight costs, airport capacities, and flight frequencies. The data ensures that the optimization problem is meaningful and solvable by providing a balance between cost, capacity, and flight coverage.

-- Realistic data for cost_per_flight
INSERT INTO cost_per_flight (route, cost) VALUES ('NYC-LAX', 4500);
INSERT INTO cost_per_flight (route, cost) VALUES ('CHI-MIA', 3500);
INSERT INTO cost_per_flight (route, cost) VALUES ('SEA-DEN', 3000);

-- Realistic data for airport_capacity
INSERT INTO airport_capacity (airport, capacity) VALUES ('JFK', 150);
INSERT INTO airport_capacity (airport, capacity) VALUES ('ORD', 120);
INSERT INTO airport_capacity (airport, capacity) VALUES ('SEA', 80);

-- Realistic data for flights
INSERT INTO flights (route, flight_count) VALUES ('NYC-LAX', 5);
INSERT INTO flights (route, flight_count) VALUES ('CHI-MIA', 3);
INSERT INTO flights (route, flight_count) VALUES ('SEA-DEN', 2);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_r \) be the number of flights to operate on route \( r \), where \( r \) is a route in the set of routes \( R \).  
  \( x_r \) is a non-negative integer variable (MIP).

#### Objective Function
Minimize the total cost of operating all flights:  
\[
\text{Minimize} \quad \sum_{r \in R} c_r \cdot x_r
\]  
where \( c_r \) is the cost of operating a flight on route \( r \).

#### Constraints
1. **Route Coverage**: Each route must be covered by at least one flight:  
\[
x_r \geq 1 \quad \forall r \in R
\]  
2. **Airport Departure Capacity**: The total number of flights departing from any airport \( a \) must not exceed its capacity:  
\[
\sum_{r \in R_a^{\text{dep}}} x_r \leq C_a^{\text{dep}} \quad \forall a \in A
\]  
where \( R_a^{\text{dep}} \) is the set of routes departing from airport \( a \), and \( C_a^{\text{dep}} \) is the departure capacity of airport \( a \).  
3. **Airport Arrival Capacity**: The total number of flights arriving at any airport \( a \) must not exceed its capacity:  
\[
\sum_{r \in R_a^{\text{arr}}} x_r \leq C_a^{\text{arr}} \quad \forall a \in A
\]  
where \( R_a^{\text{arr}} \) is the set of routes arriving at airport \( a \), and \( C_a^{\text{arr}} \) is the arrival capacity of airport \( a \).

#### Data Source Verification
- \( c_r \): `cost_per_flight.cost`  
- \( C_a^{\text{dep}} \) and \( C_a^{\text{arr}} \): `airport_capacity.capacity`  
- \( R \): Set of routes from `flights.route`  
- \( R_a^{\text{dep}} \) and \( R_a^{\text{arr}} \): Derived from the routes and airports in the problem context.

#### Numerical Example
Using the provided data:  
- Routes \( R = \{\text{NYC-LAX}, \text{CHI-MIA}, \text{SEA-DEN}\} \)  
- Costs \( c_r = \{4500, 3500, 3000\} \)  
- Departure and arrival capacities \( C_a = \{150, 120, 80\} \) for airports \( A = \{\text{JFK}, \text{ORD}, \text{SEA}\} \)  

The complete model is:  
\[
\text{Minimize} \quad 4500x_1 + 3500x_2 + 3000x_3
\]  
Subject to:  
\[
x_1 \geq 1, \quad x_2 \geq 1, \quad x_3 \geq 1
\]  
\[
x_1 \leq 150, \quad x_2 \leq 120, \quad x_3 \leq 80
\]  
where \( x_1, x_2, x_3 \) are non-negative integers representing the number of flights on routes NYC-LAX, CHI-MIA, and SEA-DEN, respectively.

## 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 adhering to airport capacities."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("flight_optimization")
    
    # Data from the problem context
    routes = ['NYC-LAX', 'CHI-MIA', 'SEA-DEN']
    costs = {'NYC-LAX': 4500, 'CHI-MIA': 3500, 'SEA-DEN': 3000}
    airport_capacities = {'JFK': 150, 'ORD': 120, 'SEA': 80}
    
    # CRITICAL: Validate array lengths before loops
    assert len(routes) == len(costs), "Route and cost array length mismatch"
    
    # 2. VARIABLES
    # Decision variables: number of flights on each route
    x = {r: model.addVar(vtype=GRB.INTEGER, name=f"x_{r}", lb=1) for r in routes}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of operating all flights
    model.setObjective(gp.quicksum(costs[r] * x[r] for r in routes), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    
    # Route Coverage: Each route must be covered by at least one flight
    for r in routes:
        model.addConstr(x[r] >= 1, name=f"route_coverage_{r}")
    
    # Airport Departure Capacity: Total flights departing from any airport must not exceed its capacity
    # Assuming NYC-LAX departs from JFK, CHI-MIA from ORD, SEA-DEN from SEA
    model.addConstr(x['NYC-LAX'] <= airport_capacities['JFK'], name="departure_capacity_JFK")
    model.addConstr(x['CHI-MIA'] <= airport_capacities['ORD'], name="departure_capacity_ORD")
    model.addConstr(x['SEA-DEN'] <= airport_capacities['SEA'], name="departure_capacity_SEA")
    
    # Airport Arrival Capacity: Total flights arriving at any airport must not exceed its capacity
    # Assuming NYC-LAX arrives at LAX, CHI-MIA at MIA, SEA-DEN at DEN
    # Since LAX, MIA, and DEN capacities are not provided, we assume they are sufficiently large
    # If capacities were provided, constraints would be added similarly to departure capacities
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for r in routes:
            print(f"Flights on {r}: {x[r].x}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

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

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 11000.0
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy successfully found the optimal solution with the minimum total cost of 11000.0. The decision variables indicate that one flight should be operated on each route, which satisfies all constraints.

## 6. DOCplex Implementation

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

from docplex.mp.model import Model

def flight_optimization():
    """Optimize flight operations to minimize costs while respecting airport capacities."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="flight_optimization")
    
    # Define routes, costs, and capacities
    routes = ['NYC-LAX', 'CHI-MIA', 'SEA-DEN']
    costs = [4500, 3500, 3000]
    departure_capacities = [150, 120, 80]
    arrival_capacities = [150, 120, 80]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(routes) == len(costs) == len(departure_capacities) == len(arrival_capacities), "Array length mismatch"
    safe_range = range(min(len(routes), len(costs), len(departure_capacities), len(arrival_capacities)))  # Safe indexing
    
    # 2. VARIABLES
    # Decision variables: number of flights on each route
    x = {i: mdl.integer_var(name=f"x_{routes[i]}", lb=1) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total cost of operating all flights
    objective = mdl.sum(costs[i] * x[i] for i in safe_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Route Coverage: Each route must be covered by at least one flight
    for i in safe_range:
        mdl.add_constraint(x[i] >= 1, ctname=f"route_coverage_{routes[i]}")
    
    # Airport Departure Capacity: Total flights departing from any airport must not exceed its capacity
    for i in safe_range:
        mdl.add_constraint(x[i] <= departure_capacities[i], ctname=f"departure_capacity_{routes[i]}")
    
    # Airport Arrival Capacity: Total flights arriving at any airport must not exceed its capacity
    for i in safe_range:
        mdl.add_constraint(x[i] <= arrival_capacities[i], ctname=f"arrival_capacity_{routes[i]}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(x[i])
            print(f"Flights on route {routes[i]}: {value}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 11000.0
**Execution Time**: 1.20 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution with the same total cost of 11000.0. The decision variables align with those from Gurobipy, confirming the solution's validity.

## 7. Pyomo Implementation

```python
#!/usr/bin/env python3
"""
Pyomo 6.9.2 Implementation for Flight Optimization Problem
"""

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

def flight_optimization():
    """Optimize flight operations to minimize costs while ensuring efficient use of airport resources."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    routes = ['NYC-LAX', 'CHI-MIA', 'SEA-DEN']
    costs = {'NYC-LAX': 4500, 'CHI-MIA': 3500, 'SEA-DEN': 3000}
    airport_capacities = {'JFK': 150, 'ORD': 120, 'SEA': 80}
    
    # CRITICAL: Validate array lengths before indexing
    assert len(routes) == len(costs), "Route and cost array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.R = pyo.Set(initialize=routes)  # Set of routes
    
    # 4. PARAMETERS (data containers)
    model.cost = pyo.Param(model.R, initialize=costs)
    model.departure_capacity = pyo.Param(initialize=airport_capacities)
    model.arrival_capacity = pyo.Param(initialize=airport_capacities)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.R, within=pyo.NonNegativeIntegers)  # Number of flights per route
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost[r] * model.x[r] for r in model.R)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Route Coverage: Each route must be covered by at least one flight
    def route_coverage_rule(model, r):
        return model.x[r] >= 1
    model.route_coverage = pyo.Constraint(model.R, rule=route_coverage_rule)
    
    # Airport Departure Capacity: Total flights departing from any airport must not exceed its capacity
    def departure_capacity_rule(model, a):
        return sum(model.x[r] for r in model.R if a in r) <= model.departure_capacity[a]
    model.departure_capacity_constraint = pyo.Constraint(airport_capacities.keys(), rule=departure_capacity_rule)
    
    # Airport Arrival Capacity: Total flights arriving at any airport must not exceed its capacity
    def arrival_capacity_rule(model, a):
        return sum(model.x[r] for r in model.R if a in r) <= model.arrival_capacity[a]
    model.arrival_capacity_constraint = pyo.Constraint(airport_capacities.keys(), rule=arrival_capacity_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("\nVariable values:")
        for r in model.R:
            x_val = pyo.value(model.x[r])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{r}] = {x_val:.3f}")
        
    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__":
    flight_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpd_the2fc.py", line 90, in <module>
    flight_optimization()
  File "/tmp/tmpd_the2fc.py", line 28, in flight_optimization
    model.departure_capacity = pyo.Param(initialize=airport_capacities)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 571, in __setattr__
    self.add_component(name, val)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 1101, in add_component
    val.construct(data)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 850, in construct
    self._construct_from_rule_using_setitem()
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 772, in _construct_from_rule_using_setitem
    self[index] = rule(block, index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 692, in __setitem__
    index = self._validate_index(index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 863, in _validate_index
    raise KeyError(
KeyError: "Cannot treat the scalar component 'departure_capacity' as an indexed component"

**Analysis**: Pyomo encountered an error during execution, specifically a KeyError related to the initialization of the 'departure_capacity' parameter. This suggests a potential issue with the model setup or data handling in Pyomo.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 11000.00 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 11000.00 | 1.20s | N/A | N/A |
| Pyomo | ERROR | N/A | 0.80s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 11000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its faster execution time and high reliability. DOCplex also provides a valid solution but is slower. Pyomo is not recommended due to the encountered error.

### Optimal Decision Variables
- **x_1** = 1.000
  - *Business Meaning*: Number of flights on route NYC-LAX. Optimal value is 1, meaning one flight should be operated.
- **x_2** = 1.000
  - *Business Meaning*: Number of flights on route CHI-MIA. Optimal value is 1, meaning one flight should be operated.
- **x_3** = 1.000
  - *Business Meaning*: Number of flights on route SEA-DEN. Optimal value is 1, meaning one flight should be operated.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests operating one flight on each route (NYC-LAX, CHI-MIA, SEA-DEN) to minimize the total cost while ensuring all routes are covered and airport capacities are not exceeded.
**Objective Value Meaning**: The optimal total cost of 11000.0 represents the minimum expenditure required to operate flights on all routes while adhering to airport capacity constraints.
**Resource Allocation Summary**: Allocate resources to operate one flight on each of the three routes (NYC-LAX, CHI-MIA, SEA-DEN). Ensure that airport departure and arrival capacities are not exceeded.
**Implementation Recommendations**: 1. Schedule one flight for each route. 2. Monitor airport capacities to avoid overloading. 3. Verify cost data and constraints periodically to ensure continued optimality.