# Complete Optimization Problem and Solution: ship_1

## 1. Problem Context and Goals

### Context  
A shipping company is focused on optimizing the assignment of captains to ships to minimize operational costs while ensuring efficient fleet management. The company must decide which captain to assign to each ship, considering factors such as the captain's rank, the ship's type, and the ship's age. Each ship must be assigned exactly one captain, and each captain can be assigned to at most one ship. Additionally, the assignment must respect compatibility rules between captain ranks and ship types, as well as age restrictions based on the ship's age and the captain's rank.

The cost of assigning a captain to a ship is determined by operational expenses, which vary depending on the captain's rank and the ship's type and age. The company has predefined rules to ensure compatibility between captains and ships, as well as age allowances that dictate whether a ship's age is acceptable for a captain's rank. These rules are critical for maintaining safety and operational efficiency.

The business configuration includes a scalar parameter for the cost of assigning a captain to a ship, which is used in the objective function to minimize total costs. Additionally, business logic formulas are used to determine compatibility between captains and ships and to enforce age restrictions. These formulas ensure that assignments are both cost-effective and compliant with operational standards.

### Goals  
The primary goal of this optimization problem is to minimize the total cost of assigning captains to ships. This cost is calculated based on the operational expenses associated with each possible assignment. Success is measured by achieving the lowest possible total cost while ensuring that all ships are assigned a captain, no captain is over-assigned, and all compatibility and age constraints are satisfied.

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. **Ship Assignment Constraint**: Each ship must be assigned exactly one captain. This ensures that all ships are operational and no ship is left without a captain.  
2. **Captain Assignment Constraint**: Each captain can be assigned to at most one ship. This prevents over-assignment and ensures that captains are not stretched too thin.  
3. **Compatibility Constraint**: A captain can only be assigned to a ship if their rank is compatible with the ship's type. This ensures that captains are qualified to operate the ships they are assigned to.  
4. **Age Constraint**: A captain can only be assigned to a ship if the ship's age is acceptable for their rank. This ensures that safety and operational standards are maintained based on the ship's age and the captain's experience level.  

These constraints are designed to ensure that the assignments are both feasible and compliant with the company's operational requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for cost coefficients, compatibility rules, and age rules. Configuration logic updates include scalar parameters for cost coefficients and formulas for compatibility and age rules.

CREATE TABLE cost_coefficients (
  captain_id INTEGER,
  ship_id INTEGER,
  cost_value INTEGER,
  assign_flag BOOLEAN
);

CREATE TABLE compatibility_rules (
  captain_rank STRING,
  ship_type STRING,
  is_compatible BOOLEAN
);

CREATE TABLE age_rules (
  captain_rank STRING,
  ship_age INTEGER,
  is_allowed BOOLEAN
);
```

### Data Dictionary  
- **cost_coefficients**:  
  - **Business Purpose**: Stores the cost of assigning a captain to a ship based on their rank, the ship's type, and the ship's age.  
  - **Optimization Role**: Provides the coefficients for the objective function, which aims to minimize total assignment costs.  
  - **Columns**:  
    - `captain_id`: Unique identifier for a captain.  
    - `ship_id`: Unique identifier for a ship.  
    - `cost_value`: The cost of assigning the captain to the ship.  
    - `assign_flag`: Indicates whether the captain is assigned to the ship (used as a binary decision variable).  

- **compatibility_rules**:  
  - **Business Purpose**: Defines whether a captain's rank is compatible with a ship's type.  
  - **Optimization Role**: Used in constraints to ensure that assignments are compatible.  
  - **Columns**:  
    - `captain_rank`: The rank of the captain (e.g., Junior, Senior).  
    - `ship_type`: The type of the ship (e.g., Cargo, Passenger).  
    - `is_compatible`: Indicates whether the captain's rank is compatible with the ship's type.  

- **age_rules**:  
  - **Business Purpose**: Determines whether a ship's age is acceptable for a captain's rank.  
  - **Optimization Role**: Used in constraints to enforce age-related restrictions.  
  - **Columns**:  
    - `captain_rank`: The rank of the captain.  
    - `ship_age`: The age of the ship.  
    - `is_allowed`: Indicates whether the ship's age is acceptable for the captain's rank.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical shipping industry standards, considering captain ranks, ship types, and ages. Costs were set to reflect operational expenses, compatibility rules were based on industry norms, and age allowances were aligned with safety regulations.

-- Realistic data for cost_coefficients
INSERT INTO cost_coefficients (captain_id, ship_id, cost_value, assign_flag) VALUES (1, 1, 1200, False);
INSERT INTO cost_coefficients (captain_id, ship_id, cost_value, assign_flag) VALUES (2, 2, 1500, False);
INSERT INTO cost_coefficients (captain_id, ship_id, cost_value, assign_flag) VALUES (3, 3, 1000, False);

-- Realistic data for compatibility_rules
INSERT INTO compatibility_rules (captain_rank, ship_type, is_compatible) VALUES ('Junior', 'Cargo', True);
INSERT INTO compatibility_rules (captain_rank, ship_type, is_compatible) VALUES ('Senior', 'Passenger', True);
INSERT INTO compatibility_rules (captain_rank, ship_type, is_compatible) VALUES ('Junior', 'Passenger', False);

-- Realistic data for age_rules
INSERT INTO age_rules (captain_rank, ship_age, is_allowed) VALUES ('Junior', 5, True);
INSERT INTO age_rules (captain_rank, ship_age, is_allowed) VALUES ('Senior', 15, True);
INSERT INTO age_rules (captain_rank, ship_age, is_allowed) VALUES ('Junior', 15, False);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary decision variable where:
  - \( x_{ij} = 1 \) if captain \( i \) is assigned to ship \( j \),
  - \( x_{ij} = 0 \) otherwise.

#### Objective Function
Minimize the total cost of assigning captains to ships:
\[
\text{Minimize} \quad \sum_{i} \sum_{j} c_{ij} \cdot x_{ij}
\]
where \( c_{ij} \) is the cost of assigning captain \( i \) to ship \( j \).

#### Constraints
1. **Ship Assignment Constraint**: Each ship must be assigned exactly one captain:
\[
\sum_{i} x_{ij} = 1 \quad \forall j
\]

2. **Captain Assignment Constraint**: Each captain can be assigned to at most one ship:
\[
\sum_{j} x_{ij} \leq 1 \quad \forall i
\]

3. **Compatibility Constraint**: A captain can only be assigned to a ship if their rank is compatible with the ship's type:
\[
x_{ij} \leq \text{is\_compatible}_{ij} \quad \forall i, j
\]
where \( \text{is\_compatible}_{ij} \) is 1 if captain \( i \)'s rank is compatible with ship \( j \)'s type, and 0 otherwise.

4. **Age Constraint**: A captain can only be assigned to a ship if the ship's age is acceptable for their rank:
\[
x_{ij} \leq \text{is\_allowed}_{ij} \quad \forall i, j
\]
where \( \text{is\_allowed}_{ij} \) is 1 if ship \( j \)'s age is acceptable for captain \( i \)'s rank, and 0 otherwise.

#### Data Source Verification
- **Objective Function Coefficients**: \( c_{ij} \) comes from `cost_coefficients.cost_value`.
- **Compatibility Constraint Coefficients**: \( \text{is\_compatible}_{ij} \) comes from `compatibility_rules.is_compatible`.
- **Age Constraint Coefficients**: \( \text{is\_allowed}_{ij} \) comes from `age_rules.is_allowed`.

This formulation provides a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

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

import gurobipy as gp
from gurobipy import GRB

def optimize_ship_captain_assignment():
    # 1. MODEL & DATA SETUP
    model = gp.Model("ship_captain_assignment")
    
    # Data from the database
    captains = [1, 2, 3]
    ships = [1, 2, 3]
    
    cost_coefficients = {
        (1, 1): 1200,
        (2, 2): 1500,
        (3, 3): 1000
    }
    
    compatibility_rules = {
        (1, 1): True,
        (2, 2): True,
        (3, 3): False
    }
    
    age_rules = {
        (1, 1): True,
        (2, 2): True,
        (3, 3): False
    }
    
    # CRITICAL: Validate array lengths before loops
    assert len(captains) == len(ships), "Array length mismatch"
    
    # 2. VARIABLES
    x = model.addVars(captains, ships, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(cost_coefficients[(i, j)] * x[i, j] for i in captains for j in ships), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    
    # Ship Assignment Constraint: Each ship must be assigned exactly one captain
    for j in ships:
        model.addConstr(gp.quicksum(x[i, j] for i in captains) == 1, name=f"ship_assignment_{j}")
    
    # Captain Assignment Constraint: Each captain can be assigned to at most one ship
    for i in captains:
        model.addConstr(gp.quicksum(x[i, j] for j in ships) <= 1, name=f"captain_assignment_{i}")
    
    # Compatibility Constraint: A captain can only be assigned to a ship if their rank is compatible with the ship's type
    for i in captains:
        for j in ships:
            model.addConstr(x[i, j] <= compatibility_rules[(i, j)], name=f"compatibility_{i}_{j}")
    
    # Age Constraint: A captain can only be assigned to a ship if the ship's age is acceptable for their rank
    for i in captains:
        for j in ships:
            model.addConstr(x[i, j] <= age_rules[(i, j)], name=f"age_{i}_{j}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in captains:
            for j in ships:
                if x[i, j].x > 1e-6:
                    print(f"Captain {i} assigned to Ship {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
optimize_ship_captain_assignment()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpgxo1y52y.py", line 78, in <module>
    optimize_ship_captain_assignment()
  File "/tmp/tmpgxo1y52y.py", line 39, in optimize_ship_captain_assignment
    model.setObjective(gp.quicksum(cost_coefficients[(i, j)] * x[i, j] for i in captains for j in ships), GRB.MINIMIZE)
  File "src/gurobipy/_helpers.pyx", line 41, in gurobipy._helpers.quicksum
  File "/tmp/tmpgxo1y52y.py", line 39, in <genexpr>
    model.setObjective(gp.quicksum(cost_coefficients[(i, j)] * x[i, j] for i in captains for j in ships), GRB.MINIMIZE)
KeyError: (1, 2)

**Analysis**: Gurobipy encountered a KeyError for the tuple (1, 2) in the cost_coefficients dictionary. This indicates missing data or an incorrect mapping in the input data.

## 6. DOCplex Implementation

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

from docplex.mp.model import Model

def optimize_ship_captain_assignment():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="ship_captain_assignment")
    
    # Data from the problem
    captains = [1, 2, 3]
    ships = [1, 2, 3]
    
    # Cost coefficients
    cost_coefficients = {
        (1, 1): 1200,
        (2, 2): 1500,
        (3, 3): 1000
    }
    
    # Compatibility rules
    compatibility_rules = {
        (1, 1): True,
        (2, 2): True,
        (3, 3): True,
        (1, 2): False,
        (1, 3): False,
        (2, 1): False,
        (2, 3): False,
        (3, 1): False,
        (3, 2): False
    }
    
    # Age rules
    age_rules = {
        (1, 1): True,
        (2, 2): True,
        (3, 3): True,
        (1, 2): False,
        (1, 3): False,
        (2, 1): False,
        (2, 3): False,
        (3, 1): False,
        (3, 2): False
    }
    
    # 2. VARIABLES
    x = mdl.binary_var_dict(keys=[(i, j) for i in captains for j in ships], name="x")
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(cost_coefficients[(i, j)] * x[(i, j)] for i in captains for j in ships)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Ship Assignment Constraint: Each ship must be assigned exactly one captain
    for j in ships:
        mdl.add_constraint(mdl.sum(x[(i, j)] for i in captains) == 1, ctname=f"ship_assignment_{j}")
    
    # Captain Assignment Constraint: Each captain can be assigned to at most one ship
    for i in captains:
        mdl.add_constraint(mdl.sum(x[(i, j)] for j in ships) <= 1, ctname=f"captain_assignment_{i}")
    
    # Compatibility Constraint: A captain can only be assigned to a ship if their rank is compatible with the ship's type
    for i in captains:
        for j in ships:
            mdl.add_constraint(x[(i, j)] <= compatibility_rules[(i, j)], ctname=f"compatibility_{i}_{j}")
    
    # Age Constraint: A captain can only be assigned to a ship if the ship's age is acceptable for their rank
    for i in captains:
        for j in ships:
            mdl.add_constraint(x[(i, j)] <= age_rules[(i, j)], ctname=f"age_{i}_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in captains:
            for j in ships:
                if solution.get_value(x[(i, j)]) > 0.5:
                    print(f"Captain {i} assigned to Ship {j}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
optimize_ship_captain_assignment()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpmt210o4g.py", line 89, in <module>
    optimize_ship_captain_assignment()
  File "/tmp/tmpmt210o4g.py", line 50, in optimize_ship_captain_assignment
    objective = mdl.sum(cost_coefficients[(i, j)] * x[(i, j)] for i in captains for j in ships)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/model.py", line 3342, in sum
    return self._aggregator.sum(args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/aggregator.py", line 198, in sum
    sum_res = self._sum_with_iter(sum_args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/aggregator.py", line 221, in _sum_with_iter
    for item in args:
  File "/tmp/tmpmt210o4g.py", line 50, in <genexpr>
    objective = mdl.sum(cost_coefficients[(i, j)] * x[(i, j)] for i in captains for j in ships)
KeyError: (1, 2)

**Analysis**: DOCplex also encountered a KeyError for the tuple (1, 2) in the cost_coefficients dictionary. This suggests a consistent issue with the input data across solvers.

## 7. Pyomo Implementation

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

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

def optimize_ship_captain_assignment():
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()

    # 2. DATA SETUP
    captains = [1, 2, 3]
    ships = [1, 2, 3]

    # Cost coefficients
    cost_coefficients = {
        (1, 1): 1200,
        (2, 2): 1500,
        (3, 3): 1000
    }

    # Compatibility rules
    compatibility_rules = {
        (1, 1): True,
        (2, 2): True,
        (3, 3): True
    }

    # Age rules
    age_rules = {
        (1, 1): True,
        (2, 2): True,
        (3, 3): True
    }

    # 3. SETS
    model.Captains = pyo.Set(initialize=captains)
    model.Ships = pyo.Set(initialize=ships)

    # 4. PARAMETERS
    model.cost_coefficients = pyo.Param(model.Captains, model.Ships, initialize=cost_coefficients)
    model.compatibility_rules = pyo.Param(model.Captains, model.Ships, initialize=compatibility_rules)
    model.age_rules = pyo.Param(model.Captains, model.Ships, initialize=age_rules)

    # 5. VARIABLES
    model.x = pyo.Var(model.Captains, model.Ships, within=pyo.Binary)

    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost_coefficients[i, j] * model.x[i, j] for i in model.Captains for j in model.Ships)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)

    # 7. CONSTRAINTS

    # Ship Assignment Constraint
    def ship_assignment_rule(model, j):
        return sum(model.x[i, j] for i in model.Captains) == 1
    model.ship_assignment = pyo.Constraint(model.Ships, rule=ship_assignment_rule)

    # Captain Assignment Constraint
    def captain_assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.Ships) <= 1
    model.captain_assignment = pyo.Constraint(model.Captains, rule=captain_assignment_rule)

    # Compatibility Constraint
    def compatibility_rule(model, i, j):
        return model.x[i, j] <= model.compatibility_rules[i, j]
    model.compatibility = pyo.Constraint(model.Captains, model.Ships, rule=compatibility_rule)

    # Age Constraint
    def age_rule(model, i, j):
        return model.x[i, j] <= model.age_rules[i, j]
    model.age = pyo.Constraint(model.Captains, model.Ships, rule=age_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 i in model.Captains:
            for j in model.Ships:
                if pyo.value(model.x[i, j]) > 0:
                    print(f"Captain {i} assigned to Ship {j}")
        
    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
optimize_ship_captain_assignment()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpfnnwk27y.py", line 107, in <module>
    optimize_ship_captain_assignment()
  File "/tmp/tmpfnnwk27y.py", line 50, in optimize_ship_captain_assignment
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
  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/objective.py", line 336, in construct
    ans = self._setitem_when_not_present(index, rule(block, index))
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/initializer.py", line 485, in __call__
    return self._fcn(parent)
  File "/tmp/tmpfnnwk27y.py", line 49, in obj_rule
    return sum(model.cost_coefficients[i, j] * model.x[i, j] for i in model.Captains for j in model.Ships)
  File "/tmp/tmpfnnwk27y.py", line 49, in <genexpr>
    return sum(model.cost_coefficients[i, j] * model.x[i, j] for i in model.Captains for j in model.Ships)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 1012, in __getitem__
    return super().__getitem__(args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 662, in __getitem__
    return self._getitem_when_not_present(index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 627, in _getitem_when_not_present
    raise ValueError(
ValueError: Error retrieving immutable Param value (cost_coefficients[(1, 2)]):
	The Param value is undefined and no default value is specified.

**Analysis**: Pyomo failed due to an undefined value for the parameter cost_coefficients[(1, 2)]. This further confirms the issue lies in the input data.

## 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.18s | N/A | 4 |
| Docplex | ERROR | N/A | 1.19s | N/A | 4 |
| Pyomo | ERROR | N/A | 0.89s | N/A | 4 |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Inconsistent Solvers**: gurobipy, docplex, pyomo
**Potential Issues**:
- Missing or incomplete cost_coefficients data
- Incorrect mapping of captains to ships in the input data
- Undefined or missing values in the compatibility or age constraint 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 KeyError, indicating a fundamental issue with the input data. No solver can be recommended until the data issue is resolved.

### Business Interpretation
**Overall Strategy**: The optimization process cannot proceed due to missing or incorrect data. This prevents any meaningful assignment of captains to ships.
**Objective Value Meaning**: The total cost of assigning captains to ships cannot be calculated due to missing data.
**Resource Allocation Summary**: No resource allocation can be determined until the input data is corrected.
**Implementation Recommendations**: ['Verify and complete the cost_coefficients data for all captain-ship pairs.', 'Ensure compatibility and age constraint data are correctly defined and mapped.', 'Re-run the optimization process after addressing the data issues.']