# Complete Optimization Problem and Solution: ship_1

## 1. Problem Context and Goals

### Context  
The business objective is to optimize the assignment of captains to ships in a manner that minimizes the total age of the captains assigned. Each captain can only be assigned to one ship, and each ship must have exactly one captain. The decision-making process involves determining which captain is assigned to which ship, represented by a binary decision variable indicating if a captain is assigned to a specific ship. The operational parameters are aligned with the goal of minimizing the total age of captains assigned to ships, calculated by summing the ages of all assigned captains. The business configuration includes a maximum allowable age for captains, ensuring that only eligible captains are considered for assignment. This maximum age limit is set at 60, ensuring captains are experienced but not beyond typical retirement age. The calculation of the total age of assigned captains is expressed in natural language as the sum of the ages of captains assigned to ships, which forms the basis of the objective function to minimize. The data presented reflects current operational information, focusing on precise decision-making that leads to linear formulations. Resource limitations are expressed through constraints that ensure each captain is assigned to exactly one ship and each ship has exactly one captain, avoiding scenarios that require nonlinear relationships.

### Goals  
The primary goal of this optimization problem is to minimize the total age of captains assigned to ships. The metric to optimize is the total age, calculated as the sum of the ages of all captains assigned to ships. Success is measured by achieving the lowest possible total age, aligning with the expected coefficient sources, which are the ages of the captains. The optimization goal is described in natural language, focusing on minimizing the total age of captains assigned to ships, without using mathematical formulas or symbolic notation.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each captain is assigned to exactly one ship and each ship has exactly one captain. These constraints are expressed in business terms that naturally lead to linear mathematical forms. Specifically, for each captain, the sum of assignments across all ships must equal one, ensuring that each captain is assigned to one ship. Similarly, for each ship, the sum of assignments across all captains must equal one, ensuring that each ship has exactly one captain. These constraints are critical to maintaining the integrity of the assignment process and ensuring that the optimization problem remains linear.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, modifying existing tables to fill mapping gaps, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE Captain (
  id INTEGER,
  age INTEGER,
  class STRING
);

CREATE TABLE CaptainShipAssignment (
  captain_id INTEGER,
  ship_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE ConstraintBounds (
  captain_assignment INTEGER,
  ship_assignment INTEGER
);
```

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

- **Captain Table**: This table stores information about captains, including their unique identifier, age, and class. The age of the captain serves as a coefficient in the objective function, contributing to the calculation of the total age of assigned captains.

- **CaptainShipAssignment Table**: This table represents the assignments of captains to ships. It includes the ID of the assigned captain and the ID of the ship to which a captain is assigned. The assignment column is a binary decision variable indicating whether a captain is assigned to a ship.

- **ConstraintBounds Table**: This table stores the constraint bounds for assignments, ensuring that each captain is assigned to exactly one ship and each ship has exactly one captain. The captain_assignment and ship_assignment columns ensure compliance with these constraints.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were chosen to reflect a realistic distribution of captain ages and ship assignments, ensuring that the optimization problem remains solvable while respecting business constraints.

-- Realistic data for Captain
INSERT INTO Captain (id, age, class) VALUES (1, 45, 'A');
INSERT INTO Captain (id, age, class) VALUES (2, 50, 'B');
INSERT INTO Captain (id, age, class) VALUES (3, 55, 'C');

-- Realistic data for CaptainShipAssignment
INSERT INTO CaptainShipAssignment (captain_id, ship_id, assignment) VALUES (1, 101, True);
INSERT INTO CaptainShipAssignment (captain_id, ship_id, assignment) VALUES (2, 102, True);
INSERT INTO CaptainShipAssignment (captain_id, ship_id, assignment) VALUES (3, 103, True);

-- Realistic data for ConstraintBounds
INSERT INTO ConstraintBounds (captain_assignment, ship_assignment) VALUES (1, 1);
```

## 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 \), and \( x_{ij} = 0 \) otherwise.

#### Objective Function
- Minimize the total age of captains assigned to ships:
  \[
  \text{Minimize } \sum_{i} \sum_{j} \text{age}_i \times x_{ij}
  \]
  where \(\text{age}_i\) is the age of captain \( i \).

#### Constraints
1. Each captain is assigned to exactly one ship:
   \[
   \sum_{j} x_{ij} = 1 \quad \forall i
   \]

2. Each ship has exactly one captain:
   \[
   \sum_{i} x_{ij} = 1 \quad \forall j
   \]

3. Only eligible captains (age ≤ 60) can be assigned:
   \[
   x_{ij} = 0 \quad \text{if } \text{age}_i > 60
   \]

Data Source Verification:
- The coefficient \(\text{age}_i\) in the objective function comes from the `Captain.age` column.
- The constraints ensuring each captain is assigned to exactly one ship and each ship has exactly one captain are derived from the `ConstraintBounds` table, specifically the `captain_assignment` and `ship_assignment` columns, both set to 1.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_captain_assignment():
    """Optimize the assignment of captains to ships to minimize total age."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("ship_1")
    
    # Data: Captains and their ages
    captains = [1, 2, 3]
    ages = {1: 45, 2: 50, 3: 55}
    
    # Data: Ships
    ships = [101, 102, 103]
    
    # Validate data lengths
    assert len(captains) == len(ages), "Mismatch in captains and ages data"
    
    # 2. VARIABLES
    # Decision variables: x[i, j] = 1 if captain i is assigned to ship j
    x = model.addVars(captains, ships, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total age of captains assigned to ships
    model.setObjective(gp.quicksum(ages[i] * x[i, j] for i in captains for j in ships), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    # Each captain is assigned to exactly one ship
    model.addConstrs((gp.quicksum(x[i, j] for j in ships) == 1 for i in captains), name="captain_assignment")
    
    # Each ship has exactly one captain
    model.addConstrs((gp.quicksum(x[i, j] for i in captains) == 1 for j in ships), name="ship_assignment")
    
    # Only eligible captains (age ≤ 60) can be assigned
    model.addConstrs((x[i, j] == 0 for i in captains for j in ships if ages[i] > 60), name="age_limit")
    
    # 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} is assigned to Ship {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_captain_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 150.0
**Execution Time**: 0.75 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the shortest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_captain_assignment():
    """Optimize the assignment of captains to ships to minimize total age."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="captain_assignment")
    
    # Data: Captains and their ages
    captains = [1, 2, 3]
    ages = [45, 50, 55]
    ships = [101, 102, 103]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(captains) == len(ages), "Array length mismatch for captains and ages"
    assert len(captains) == len(ships), "Array length mismatch for captains and ships"
    safe_range = range(len(captains))  # Safe indexing
    
    # 2. VARIABLES
    # Binary decision variables for captain-ship assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in safe_range for j in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total age of captains assigned to ships
    objective = mdl.sum(ages[i] * x[i, j] for i in safe_range for j in safe_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Each captain is assigned to exactly one ship
    for i in safe_range:
        mdl.add_constraint(mdl.sum(x[i, j] for j in safe_range) == 1, ctname=f"captain_{i}_assignment")
    
    # Each ship has exactly one captain
    for j in safe_range:
        mdl.add_constraint(mdl.sum(x[i, j] for i in safe_range) == 1, ctname=f"ship_{j}_assignment")
    
    # Only eligible captains (age ≤ 60) can be assigned
    for i in safe_range:
        if ages[i] > 60:
            for j in safe_range:
                mdl.add_constraint(x[i, j] == 0, ctname=f"eligibility_{i}_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            for j in safe_range:
                value = solution.get_value(x[i, j])
                if value > 1e-6:
                    print(f"Captain {captains[i]} assigned to Ship {ships[j]}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_captain_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 150.0
**Execution Time**: 7.18 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy, but still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def ship_optimization():
    """Optimize captain assignments to ships to minimize total age"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    captains = [1, 2, 3]
    ships = [101, 102, 103]
    ages = {1: 45, 2: 50, 3: 55}
    
    # CRITICAL: Validate array lengths before indexing
    assert len(captains) == len(ages), "Array length mismatch for captains and ages"
    
    # 3. SETS
    model.C = pyo.Set(initialize=captains)
    model.S = pyo.Set(initialize=ships)
    
    # 4. PARAMETERS
    model.age = pyo.Param(model.C, initialize=ages)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.C, model.S, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.age[i] * model.x[i, j] for i in model.C for j in model.S)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    # Each captain is assigned to exactly one ship
    def captain_assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.S) == 1
    model.captain_assignment = pyo.Constraint(model.C, rule=captain_assignment_rule)
    
    # Each ship has exactly one captain
    def ship_assignment_rule(model, j):
        return sum(model.x[i, j] for i in model.C) == 1
    model.ship_assignment = pyo.Constraint(model.S, rule=ship_assignment_rule)
    
    # Only eligible captains (age ≤ 60) can be assigned
    def eligibility_rule(model, i, j):
        if model.age[i] > 60:
            return model.x[i, j] == 0
        else:
            return pyo.Constraint.Skip
    model.eligibility = pyo.Constraint(model.C, model.S, rule=eligibility_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)
    
    # 9. RESULT PROCESSING
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nAssignment of captains to ships:")
        for i in model.C:
            for j in model.S:
                if pyo.value(model.x[i, j]) > 0.5:  # Binary variable, check if assigned
                    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

# Run the optimization
ship_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 150.0
**Execution Time**: 3.60 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, demonstrating reliability and consistency with other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 150.00 | 0.75s | N/A | N/A |
| Docplex | OPTIMAL | 150.00 | 7.18s | N/A | N/A |
| Pyomo | OPTIMAL | 150.00 | 3.60s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 150.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its efficient execution time and consistent results, making it suitable for large-scale or time-sensitive applications.

### Business Interpretation
**Overall Strategy**: The optimal assignment minimizes the total age of captains assigned to ships, ensuring compliance with age eligibility constraints.
**Objective Value Meaning**: The optimal objective value of 150.0 represents the minimized total age of captains assigned to ships, ensuring the youngest possible team within eligibility constraints.
**Resource Allocation Summary**: Captains are allocated to ships in a manner that minimizes the total age, ensuring compliance with eligibility criteria and optimal resource utilization.
**Implementation Recommendations**: Implement the solution by assigning captains to ships as per the optimal decision variables, ensuring all captains are eligible and the total age is minimized.