=== Problem Context ===
# 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.  


=== Schema ===
-- 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
);



=== LLM Response ===
```sql
-- Query Description: Retrieve all missions with their requirements to understand the constraints for ship assignments.
-- This is crucial for ensuring that the ships assigned to missions meet the minimum tonnage, speed, and nationality requirements.
SELECT mission_id, min_tonnage, min_speed, required_nationality
FROM missions;

-- Query Description: Retrieve all ships with their tonnage, speed, and nationality to evaluate their suitability for missions.
-- This data is essential for determining which ships can be assigned to which missions based on the mission requirements.
SELECT ship_id, tonnage, speed_knots, nationality
FROM ships;

-- Query Description: Retrieve all current ship-to-mission assignments to understand the current state of assignments.
-- This helps in ensuring that the constraints of each mission being assigned to exactly one ship and each ship being assigned to at most one mission are satisfied.
SELECT ship_id, mission_id, is_assigned
FROM ship_mission_assignments
WHERE is_assigned = TRUE;

-- Query Description: Retrieve a list of ships that meet the tonnage, speed, and nationality requirements for each mission.
-- This query is useful for identifying potential ship candidates for each mission, which is a key step in the optimization process.
SELECT m.mission_id, s.ship_id
FROM missions m
JOIN ships s ON s.tonnage >= m.min_tonnage
            AND s.speed_knots >= m.min_speed
            AND s.nationality = m.required_nationality;

-- Query Description: Retrieve the total number of missions and ships to understand the scale of the problem.
-- This helps in assessing the complexity of the optimization problem and planning the solution approach.
SELECT COUNT(*) AS total_missions FROM missions;
SELECT COUNT(*) AS total_ships FROM ships;

-- Query Description: Retrieve the total operational cost for each ship based on its tonnage and speed.
-- This is important for calculating the objective function, which aims to minimize the total operational cost.
-- Assuming cost coefficients for tonnage and speed are 0.5 and 0.3 respectively (these values should be adjusted based on actual coefficients).
SELECT ship_id, (0.5 * tonnage) + (0.3 * speed_knots) AS operational_cost
FROM ships;

-- Query Description: Retrieve the total operational cost for all current assignments.
-- This helps in evaluating the current total cost and identifying areas for optimization.
SELECT sma.ship_id, sma.mission_id, (0.5 * s.tonnage) + (0.3 * s.speed_knots) AS operational_cost
FROM ship_mission_assignments sma
JOIN ships s ON sma.ship_id = s.ship_id
WHERE sma.is_assigned = TRUE;

-- Query Description: Retrieve missions that are not yet assigned to any ship.
-- This is important for ensuring that all missions are fulfilled and identifying which missions still need to be assigned.
SELECT m.mission_id
FROM missions m
LEFT JOIN ship_mission_assignments sma ON m.mission_id = sma.mission_id
WHERE sma.is_assigned IS NULL OR sma.is_assigned = FALSE;

-- Query Description: Retrieve ships that are not yet assigned to any mission.
-- This helps in identifying available ships that can be assigned to missions, ensuring efficient resource utilization.
SELECT s.ship_id
FROM ships s
LEFT JOIN ship_mission_assignments sma ON s.ship_id = sma.ship_id
WHERE sma.is_assigned IS NULL OR sma.is_assigned = FALSE;

-- Query Description: Retrieve the total operational cost for all possible ship-to-mission assignments.
-- This is useful for evaluating the cost of all potential assignments and identifying the optimal combination.
SELECT m.mission_id, s.ship_id, (0.5 * s.tonnage) + (0.3 * s.speed_knots) AS operational_cost
FROM missions m
JOIN ships s ON s.tonnage >= m.min_tonnage
            AND s.speed_knots >= m.min_speed
            AND s.nationality = m.required_nationality;
```

These queries are designed to retrieve the necessary data for solving the optimization problem, including mission requirements, ship capabilities, current assignments, and operational costs. They help in understanding the constraints, evaluating potential assignments, and calculating the objective function to minimize the total operational cost.
