# Complete Optimization Problem and Solution: flight_4

## 1. Problem Context and Goals

### Context  
The business problem revolves around optimizing the assignment of airlines to specific routes to minimize total operational costs while ensuring all required destinations are covered and no airline exceeds its capacity. The decision to assign an airline to a route is represented as a binary choice, where each route must be assigned to exactly one airline. The operational cost of assigning an airline to a route is a key factor in this decision, and it is calculated using a formula that includes base operational costs, distance-based costs, fuel costs, and fuel efficiency. Additionally, a threshold for operational efficiency is set to ensure that airlines maintain high performance standards. This threshold is used to evaluate airline performance but does not directly influence the optimization constraints. The problem is designed to avoid nonlinear relationships, ensuring that all calculations and constraints remain linear and straightforward.

### Goals  
The primary goal of this optimization is to minimize the total operational cost associated with assigning airlines to routes. This is achieved by summing the costs of all assignments, where each assignment is weighted by its corresponding operational cost. Success is measured by achieving the lowest possible total cost while ensuring that all routes are covered and no airline is assigned more routes than it can handle. The optimization process relies on precise operational data, including the costs of assignments and the capacities of airlines, to make informed decisions that align with business objectives.

## 2. Constraints    

The optimization problem is subject to two key constraints:  
1. **Route Coverage**: Each route must be assigned to exactly one airline. This ensures that all required destinations are covered without redundancy.  
2. **Airline Capacity**: No airline can be assigned more routes than its specified capacity. This ensures that airlines operate within their operational limits and maintain efficiency.  

These constraints are designed to be linear and straightforward, avoiding any complex relationships or nonlinear calculations. They directly align with the business requirements and ensure that the optimization problem remains solvable and meaningful.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added decision variable table assign_airline_route to address missing optimization requirement. Updated business configuration logic with additional scalar parameters and formulas.

CREATE TABLE cost_airline_route (
  alid INTEGER,
  rid INTEGER,
  cost FLOAT
);

CREATE TABLE capacity_airline (
  alid INTEGER,
  capacity INTEGER
);

CREATE TABLE assign_airline_route (
  alid INTEGER,
  rid INTEGER,
  assign BOOLEAN
);
```

### Data Dictionary  
- **cost_airline_route**: This table contains the operational costs associated with assigning specific airlines to specific routes. Each record includes the airline ID, route ID, and the cost of the assignment. The cost is used as a coefficient in the optimization objective function.  
- **capacity_airline**: This table defines the maximum number of routes each airline can handle. It includes the airline ID and its corresponding capacity, which serves as an upper bound in the capacity constraint.  
- **assign_airline_route**: This table represents the decision variables in the optimization problem. Each record indicates whether a specific airline is assigned to a specific route, using a binary value (true or false).  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards, realistic operational costs, and airline capacities. The data ensures that the optimization problem is meaningful and solvable by maintaining logical relationships between tables and respecting business configuration logic.

-- Realistic data for cost_airline_route
INSERT INTO cost_airline_route (alid, rid, cost) VALUES (1, 1, 1200.0);
INSERT INTO cost_airline_route (alid, rid, cost) VALUES (1, 2, 1800.0);
INSERT INTO cost_airline_route (alid, rid, cost) VALUES (1, 3, 2500.0);

-- Realistic data for capacity_airline
INSERT INTO capacity_airline (alid, capacity) VALUES (1, 8);
INSERT INTO capacity_airline (alid, capacity) VALUES (2, 12);
INSERT INTO capacity_airline (alid, capacity) VALUES (3, 6);

-- Realistic data for assign_airline_route
INSERT INTO assign_airline_route (alid, rid, assign) VALUES (1, 1, False);
INSERT INTO assign_airline_route (alid, rid, assign) VALUES (1, 2, False);
INSERT INTO assign_airline_route (alid, rid, assign) VALUES (1, 3, False);
```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Minimize the total operational cost:
\[
\text{Minimize} \quad \sum_{a \in A} \sum_{r \in R} c_{a,r} \cdot x_{a,r}
\]
where:
- \( c_{a,r} \) is the cost of assigning airline \( a \) to route \( r \).

#### Constraints
1. **Route Coverage**: Each route must be assigned to exactly one airline:
\[
\sum_{a \in A} x_{a,r} = 1 \quad \forall r \in R
\]
2. **Airline Capacity**: No airline can be assigned more routes than its capacity:
\[
\sum_{r \in R} x_{a,r} \leq C_a \quad \forall a \in A
\]
where:
- \( C_a \) is the capacity of airline \( a \).

#### Data Source Verification
- \( c_{a,r} \): `cost_airline_route.cost`
- \( C_a \): `capacity_airline.capacity`

### Complete Numerical Model
Using the provided data:

#### Decision Variables
\( x_{1,1}, x_{1,2}, x_{1,3} \) (binary variables for airline 1 and routes 1, 2, 3)

#### Objective Function
\[
\text{Minimize} \quad 1200 \cdot x_{1,1} + 1800 \cdot x_{1,2} + 2500 \cdot x_{1,3}
\]

#### Constraints
1. **Route Coverage**:
\[
x_{1,1} + x_{1,2} + x_{1,3} = 1
\]
2. **Airline Capacity**:
\[
x_{1,1} + x_{1,2} + x_{1,3} \leq 8
\]

This is a complete, immediately solvable linear programming model.

## 5. Gurobipy Implementation

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

import gurobipy as gp
from gurobipy import GRB

def flight_assignment_optimization():
    # 1. MODEL & DATA SETUP
    model = gp.Model("flight_assignment")
    
    # Data from the problem
    airlines = [1, 2, 3]
    routes = [1, 2, 3]
    
    # Cost data
    cost_data = {
        (1, 1): 1200.0,
        (1, 2): 1800.0,
        (1, 3): 2500.0
    }
    
    # Capacity data
    capacity_data = {
        1: 8,
        2: 12,
        3: 6
    }
    
    # Validate array lengths
    assert len(cost_data) == len(airlines) * len(routes), "Cost data length mismatch"
    assert len(capacity_data) == len(airlines), "Capacity data length mismatch"
    
    # 2. VARIABLES
    x = model.addVars(airlines, routes, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(cost_data[a, r] * x[a, r] for a in airlines for r in routes), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    # Route Coverage: Each route must be assigned to exactly one airline
    for r in routes:
        model.addConstr(gp.quicksum(x[a, r] for a in airlines) == 1, name=f"route_coverage_{r}")
    
    # Airline Capacity: No airline can be assigned more routes than its capacity
    for a in airlines:
        model.addConstr(gp.quicksum(x[a, r] for r in routes) <= capacity_data[a], name=f"capacity_{a}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for a in airlines:
            for r in routes:
                if x[a, r].x > 1e-6:
                    print(f"Airline {a} assigned to route {r}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
flight_assignment_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmph7hf41op.py", line 64, in <module>
    flight_assignment_optimization()
  File "/tmp/tmph7hf41op.py", line 29, in flight_assignment_optimization
    assert len(cost_data) == len(airlines) * len(routes), "Cost data length mismatch"
AssertionError: Cost data length mismatch

**Analysis**: Gurobipy encountered an error due to a mismatch in the length of cost data. This suggests an issue with the input data structure or preparation.

## 6. DOCplex Implementation

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

from docplex.mp.model import Model

def flight_assignment_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="flight_assignment_optimization")
    
    # Data from the problem
    airlines = [1, 2, 3]
    routes = [1, 2, 3]
    
    # Cost data
    cost_data = [
        (1, 1, 1200.0),
        (1, 2, 1800.0),
        (1, 3, 2500.0)
    ]
    
    # Capacity data
    capacity_data = [
        (1, 8),
        (2, 12),
        (3, 6)
    ]
    
    # Validate array lengths
    assert len(cost_data) == len(airlines) * len(routes), "Cost data length mismatch"
    assert len(capacity_data) == len(airlines), "Capacity data length mismatch"
    
    # Create dictionaries for cost and capacity
    cost = {(a, r): c for a, r, c in cost_data}
    capacity = {a: c for a, c in capacity_data}
    
    # 2. VARIABLES
    x = mdl.binary_var_dict(keys=[(a, r) for a in airlines for r in routes], name="x")
    
    # 3. OBJECTIVE FUNCTION
    total_cost = mdl.sum(cost[(a, r)] * x[(a, r)] for a in airlines for r in routes)
    mdl.minimize(total_cost)
    
    # 4. CONSTRAINTS
    
    # Route Coverage: Each route must be assigned to exactly one airline
    for r in routes:
        mdl.add_constraint(mdl.sum(x[(a, r)] for a in airlines) == 1, ctname=f"route_coverage_{r}")
    
    # Airline Capacity: No airline can be assigned more routes than its capacity
    for a in airlines:
        mdl.add_constraint(mdl.sum(x[(a, r)] for r in routes) <= capacity[a], ctname=f"capacity_{a}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for a in airlines:
            for r in routes:
                if solution.get_value(x[(a, r)]) > 0:
                    print(f"Airline {a} assigned to route {r}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
flight_assignment_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpkdl4nhoy.py", line 68, in <module>
    flight_assignment_optimization()
  File "/tmp/tmpkdl4nhoy.py", line 28, in flight_assignment_optimization
    assert len(cost_data) == len(airlines) * len(routes), "Cost data length mismatch"
AssertionError: Cost data length mismatch

**Analysis**: DOCplex also failed with the same error, indicating a systemic issue with the cost data provided to the solver.

## 7. Pyomo Implementation

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

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

def optimize_flight_assignment():
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Define airlines and routes
    airlines = [1, 2, 3]
    routes = [1, 2, 3]
    
    # Cost data from cost_airline_route table
    cost_data = {
        (1, 1): 1200.0,
        (1, 2): 1800.0,
        (1, 3): 2500.0
    }
    
    # Capacity data from capacity_airline table
    capacity_data = {
        1: 8,
        2: 12,
        3: 6
    }
    
    # Validate array lengths
    assert len(cost_data) == len(airlines) * len(routes), "Cost data length mismatch"
    assert len(capacity_data) == len(airlines), "Capacity data length mismatch"
    
    # 3. SETS
    model.A = pyo.Set(initialize=airlines)
    model.R = pyo.Set(initialize=routes)
    
    # 4. PARAMETERS
    model.cost = pyo.Param(model.A, model.R, initialize=cost_data)
    model.capacity = pyo.Param(model.A, initialize=capacity_data)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.A, model.R, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost[a, r] * model.x[a, r] for a in model.A for r in model.R)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    # Route Coverage: Each route must be assigned to exactly one airline
    def route_coverage_rule(model, r):
        return sum(model.x[a, r] for a in model.A) == 1
    model.route_coverage = pyo.Constraint(model.R, rule=route_coverage_rule)
    
    # Airline Capacity: No airline can be assigned more routes than its capacity
    def airline_capacity_rule(model, a):
        return sum(model.x[a, r] for r in model.R) <= model.capacity[a]
    model.airline_capacity = pyo.Constraint(model.A, rule=airline_capacity_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 a in model.A:
            for r in model.R:
                x_val = pyo.value(model.x[a, r])
                if x_val > 1e-6:  # Only print non-zero values
                    print(f"x[{a}, {r}] = {int(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

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

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpopca6jcm.py", line 95, in <module>
    optimize_flight_assignment()
  File "/tmp/tmpopca6jcm.py", line 30, in optimize_flight_assignment
    assert len(cost_data) == len(airlines) * len(routes), "Cost data length mismatch"
AssertionError: Cost data length mismatch

**Analysis**: Pyomo experienced the same error as the other solvers, reinforcing the conclusion that the problem lies in the data preparation stage.

## 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.20s | N/A | 4 |
| Docplex | ERROR | N/A | 1.14s | N/A | 4 |
| Pyomo | ERROR | N/A | 0.70s | N/A | 4 |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Inconsistent Solvers**: gurobipy, docplex, pyomo
**Potential Issues**:
- Cost data length mismatch
- Incorrect data preparation
- Missing or incomplete 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 the same data mismatch issue. The problem must be addressed at the data preparation level before any solver can be effectively used.

### Business Interpretation
**Overall Strategy**: The optimization process cannot proceed due to data issues, preventing any meaningful business decision.
**Objective Value Meaning**: No optimal objective value is available due to the failure in the optimization process.
**Resource Allocation Summary**: Resource allocation cannot be determined until the data issues are resolved.
**Implementation Recommendations**: 1. Verify and correct the cost data to ensure it matches the expected dimensions. 2. Re-run the optimization with the corrected data. 3. Validate the results with at least two different solvers to ensure consistency.