# Complete Optimization Problem and Solution: ship_mission

## 1. Problem Context and Goals

### Context  
A shipping company operates a fleet of ships and needs to assign them to various missions efficiently. Each mission has specific requirements, including minimum tonnage, minimum speed, and a required nationality for the ship. The company aims to minimize the total operational cost of the fleet, which is influenced by the tonnage and speed of the ships assigned to missions. The cost is calculated as a weighted sum of the ship's tonnage and speed, where the weights are predefined cost coefficients. The company must ensure that each mission is assigned to exactly one ship and that each ship is assigned to at most one mission. Additionally, the assigned ship must meet the mission's tonnage, speed, and nationality requirements. The decision to assign a ship to a mission is represented by a binary indicator, which is the core decision variable in this problem.

### Goals  
The primary goal of this optimization problem is to minimize the total operational cost of the fleet. This cost is determined by the sum of the costs associated with the tonnage and speed of the ships assigned to missions. The cost coefficients for tonnage and speed are predefined and reflect the company's operational priorities. Success is measured by achieving the lowest possible total cost while ensuring that all mission requirements are met and that the assignment constraints are satisfied.

## 2. Constraints  

1. **Mission Assignment Constraint**: Each mission must be assigned to exactly one ship. This ensures that all missions are fulfilled without overlap or omission.  
2. **Ship Assignment Constraint**: Each ship can be assigned to at most one mission. This prevents overcommitment of ships and ensures efficient resource utilization.  
3. **Tonnage Requirement Constraint**: The tonnage of the ship assigned to a mission must meet or exceed the minimum tonnage required by the mission. This ensures that the ship has the necessary capacity to handle the mission.  
4. **Speed Requirement Constraint**: The speed of the ship assigned to a mission must meet or exceed the minimum speed required by the mission. This ensures that the ship can complete the mission within the required timeframe.  
5. **Nationality Requirement Constraint**: The nationality of the ship assigned to a mission must match the required nationality specified by the mission. This ensures compliance with regulatory or contractual obligations.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for ship-to-mission assignments to address the missing decision variable x_ij. Updated the data dictionary and business configuration logic to reflect these changes.

CREATE TABLE missions (
  mission_id INTEGER,
  min_tonnage INTEGER,
  min_speed INTEGER,
  required_nationality STRING
);

CREATE TABLE ships (
  ship_id INTEGER,
  tonnage INTEGER,
  speed_knots INTEGER,
  nationality STRING
);

CREATE TABLE ship_mission_assignments (
  ship_id INTEGER,
  mission_id INTEGER,
  is_assigned BOOLEAN
);
```

### Data Dictionary  
- **Missions**: Contains details of each mission, including the minimum tonnage, minimum speed, and required nationality.  
  - `mission_id`: Unique identifier for each mission.  
  - `min_tonnage`: Minimum tonnage required for the mission.  
  - `min_speed`: Minimum speed required for the mission.  
  - `required_nationality`: Required nationality for the mission.  

- **Ships**: Contains details of each ship, including tonnage, speed, and nationality.  
  - `ship_id`: Unique identifier for each ship.  
  - `tonnage`: Tonnage of the ship.  
  - `speed_knots`: Speed of the ship in knots.  
  - `nationality`: Nationality of the ship.  

- **Ship Mission Assignments**: Tracks the assignment of ships to missions.  
  - `ship_id`: Unique identifier for each ship.  
  - `mission_id`: Unique identifier for each mission.  
  - `is_assigned`: Indicates whether the ship is assigned to the mission.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic shipping industry standards, ensuring that ships meet mission requirements and that the optimization problem remains solvable. Parameters were set to reflect typical operational costs and constraints.

-- Realistic data for missions
INSERT INTO missions (mission_id, min_tonnage, min_speed, required_nationality) VALUES (1, 5000, 20, 'USA');
INSERT INTO missions (mission_id, min_tonnage, min_speed, required_nationality) VALUES (2, 6000, 25, 'UK');
INSERT INTO missions (mission_id, min_tonnage, min_speed, required_nationality) VALUES (3, 7000, 30, 'Canada');

-- Realistic data for ships
INSERT INTO ships (ship_id, tonnage, speed_knots, nationality) VALUES (1, 5500, 22, 'USA');
INSERT INTO ships (ship_id, tonnage, speed_knots, nationality) VALUES (2, 6500, 27, 'UK');
INSERT INTO ships (ship_id, tonnage, speed_knots, nationality) VALUES (3, 7500, 32, 'Canada');

-- Realistic data for ship_mission_assignments
INSERT INTO ship_mission_assignments (ship_id, mission_id, is_assigned) VALUES (1, 1, True);
INSERT INTO ship_mission_assignments (ship_id, mission_id, is_assigned) VALUES (2, 2, True);
INSERT INTO ship_mission_assignments (ship_id, mission_id, is_assigned) VALUES (3, 3, True);
```