# 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.  


### Retrieved Values

**Query 1: This is crucial for the objective function, which aims to minimize the total cost of assignments.**

```sql
SELECT cc.captain_id, cc.ship_id, cc.cost_value FROM cost_coefficients cc WHERE cc.assign_flag = TRUE;
```

**Results (CSV format):**
```csv
captain_id,ship_id,cost_value
```

**Query 2: This data is essential for the compatibility constraint, ensuring that captains are only assigned to ships they are qualified to operate.**

```sql
SELECT cr.captain_rank, cr.ship_type, cr.is_compatible FROM compatibility_rules cr WHERE cr.is_compatible = TRUE;
```

**Results (CSV format):**
```csv
captain_rank,ship_type,is_compatible
Junior,Cargo,1
Senior,Passenger,1
```

**Query 3: This data is necessary for the age constraint, ensuring that assignments comply with safety and operational standards based on ship age and captain rank.**

```sql
SELECT ar.captain_rank, ar.ship_age, ar.is_allowed FROM age_rules ar WHERE ar.is_allowed = TRUE;
```

**Results (CSV format):**
```csv
captain_rank,ship_age,is_allowed
Junior,5,1
Senior,15,1
```

**Query 4: This is useful for ensuring that each captain is assigned to at most one ship, as per the captain assignment constraint.**

```sql
SELECT DISTINCT captain_id FROM cost_coefficients;
```

**Results (CSV format):**
```csv
captain_id
1
2
3
```

**Query 5: This is useful for ensuring that each ship is assigned exactly one captain, as per the ship assignment constraint.**

```sql
SELECT DISTINCT ship_id FROM cost_coefficients;
```

**Results (CSV format):**
```csv
ship_id
1
2
3
```

**Query 6: This summary data helps in understanding the scale of the problem and ensuring that the number of captains and ships are balanced for assignment.**

```sql
SELECT COUNT(DISTINCT captain_id) AS total_captains, COUNT(DISTINCT ship_id) AS total_ships FROM cost_coefficients;
```

**Results (CSV format):**
```csv
total_captains,total_ships
3,3
```

**Query 7: This summary data helps in understanding the range of costs, which can be useful for setting bounds or thresholds in the optimization model.**

```sql
SELECT MIN(cost_value) AS min_cost, MAX(cost_value) AS max_cost FROM cost_coefficients;
```

**Results (CSV format):**
```csv
min_cost,max_cost
1000,1500
```

**Query 8: This summary data provides a benchmark for evaluating the overall cost efficiency of assignments.**

```sql
SELECT AVG(cost_value) AS avg_cost FROM cost_coefficients;
```

**Results (CSV format):**
```csv
avg_cost
1233.3333333333333
```

**Query 9: This data helps in understanding the number of feasible assignments before considering other constraints.**

```sql
SELECT COUNT(*) AS compatible_pairs FROM compatibility_rules WHERE is_compatible = TRUE;
```

**Results (CSV format):**
```csv
compatible_pairs
2
```

**Query 10: This data helps in understanding the number of feasible assignments after considering age constraints.**

```sql
SELECT COUNT(*) AS allowed_pairs FROM age_rules WHERE is_allowed = TRUE;
```

**Results (CSV format):**
```csv
allowed_pairs
2
```

