=== Problem Context ===
# Complete Optimization Problem and Solution: battle_death

## 1. Problem Context and Goals

### Context  
In military operations, the allocation of ships to battles is a critical decision that directly impacts the outcome of engagements. The goal is to minimize the total number of casualties (killed and injured) across all battles by optimally deploying ships, considering their tonnage and the constraints of each battle. Each ship can only be deployed to one battle, and the total tonnage of ships deployed to a battle must not exceed the maximum tonnage allowed for that battle. The decision to deploy a ship to a specific battle is represented as a binary choice: a ship is either deployed or not. The total casualties in a battle are calculated as the sum of killed and injured personnel, which serves as the primary metric for optimization. The maximum tonnage allowed per battle is a key operational parameter that ensures realistic and feasible deployment scenarios.

### Goals  
The primary goal of this optimization problem is to minimize the total number of casualties across all battles. This is achieved by strategically deciding which ships to deploy to each battle, ensuring that the total tonnage of deployed ships does not exceed the maximum allowed for each battle. Success is measured by the reduction in the sum of killed and injured personnel across all battles, aligning with the operational objective of minimizing human losses.

## 2. Constraints    

1. **Tonnage Constraint**: For each battle, the total tonnage of all ships deployed to that battle must not exceed the maximum tonnage allowed for that battle. This ensures that the deployment of ships is within the operational capacity of each battle.

2. **Single Deployment Constraint**: Each ship can only be deployed to one battle. This ensures that ships are not overcommitted and are available for their assigned engagements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for ship tonnage, battle constraints, and ship-to-battle deployment decisions. Configuration logic updates include scalar parameters for maximum tonnage and formulas for casualty calculations.

CREATE TABLE ship_tonnage (
  ship_id INTEGER,
  tonnage INTEGER
);

CREATE TABLE battle_constraints (
  battle_id INTEGER,
  max_tonnage INTEGER
);

CREATE TABLE ship_deployment (
  ship_id INTEGER,
  battle_id INTEGER,
  deployed BOOLEAN
);

CREATE TABLE death (
  battle_id INTEGER,
  ship_id INTEGER,
  killed INTEGER,
  injured INTEGER
);


```

### Data Dictionary  
- **ship_tonnage**: Contains the tonnage of each ship available for deployment.  
  - *ship_id*: Unique identifier for each ship.  
  - *tonnage*: Tonnage of the ship, used in the tonnage constraint.  

- **battle_constraints**: Specifies the maximum tonnage allowed per battle.  
  - *battle_id*: Unique identifier for each battle.  
  - *max_tonnage*: Maximum tonnage allowed in the battle, used as a constraint bound in the optimization model.  

- **ship_deployment**: Represents the binary decision variable indicating if a ship is deployed to a battle.  
  - *ship_id*: Unique identifier for each ship.  
  - *battle_id*: Unique identifier for each battle.  
  - *deployed*: Indicates if the ship is deployed to the battle, used as a decision variable in the optimization model.  

- **death**: Records the casualties in each battle.  
  - *battle_id*: Unique identifier for each battle.  
  - *ship_id*: Unique identifier for each ship.  
  - *killed*: Number of killed in the battle, used as an objective coefficient in the optimization model.  
  - *injured*: Number of injured in the battle, used as an objective coefficient in the optimization model.  


=== Schema ===
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for ship tonnage, battle constraints, and ship-to-battle deployment decisions. Configuration logic updates include scalar parameters for maximum tonnage and formulas for casualty calculations.

CREATE TABLE ship_tonnage (
  ship_id INTEGER,
  tonnage INTEGER
);

CREATE TABLE battle_constraints (
  battle_id INTEGER,
  max_tonnage INTEGER
);

CREATE TABLE ship_deployment (
  ship_id INTEGER,
  battle_id INTEGER,
  deployed BOOLEAN
);

CREATE TABLE death (
  battle_id INTEGER,
  ship_id INTEGER,
  killed INTEGER,
  injured INTEGER
);



=== LLM Response ===
```sql
-- Query Description: Retrieve the tonnage of each ship, which is essential for the tonnage constraint in the optimization model.
-- This data is needed to ensure that the total tonnage of ships deployed to a battle does not exceed the maximum allowed tonnage.
SELECT ship_id, tonnage
FROM ship_tonnage;

-- Query Description: Retrieve the maximum tonnage allowed for each battle, which is a key constraint parameter.
-- This data is used to ensure that the total tonnage of ships deployed to a battle does not exceed the maximum allowed tonnage.
SELECT battle_id, max_tonnage
FROM battle_constraints;

-- Query Description: Retrieve the current deployment decisions for ships to battles, which are the decision variables in the optimization model.
-- This data is needed to determine which ships are deployed to which battles and to ensure that each ship is only deployed to one battle.
SELECT ship_id, battle_id, deployed
FROM ship_deployment;

-- Query Description: Retrieve the number of killed and injured personnel for each ship in each battle, which are the objective function coefficients.
-- This data is used to calculate the total casualties (killed + injured) that need to be minimized across all battles.
SELECT battle_id, ship_id, killed, injured
FROM death;

-- Query Description: Retrieve the total casualties (killed + injured) for each battle, which is the primary metric for optimization.
-- This aggregated data helps in understanding the overall impact of ship deployments on casualties.
SELECT battle_id, SUM(killed + injured) AS total_casualties
FROM death
GROUP BY battle_id;

-- Query Description: Retrieve the total tonnage of ships currently deployed to each battle, which is useful for verifying the tonnage constraint.
-- This aggregated data helps in ensuring that the total tonnage of deployed ships does not exceed the maximum allowed tonnage for each battle.
SELECT b.battle_id, SUM(s.tonnage) AS total_deployed_tonnage
FROM ship_deployment sd
JOIN ship_tonnage s ON sd.ship_id = s.ship_id
WHERE sd.deployed = TRUE
GROUP BY b.battle_id;

-- Query Description: Retrieve the list of ships that are not yet deployed to any battle, which can be considered for deployment.
-- This data is useful for identifying available ships that can be deployed to battles to minimize casualties.
SELECT s.ship_id, s.tonnage
FROM ship_tonnage s
LEFT JOIN ship_deployment sd ON s.ship_id = sd.ship_id AND sd.deployed = TRUE
WHERE sd.ship_id IS NULL;

-- Query Description: Retrieve the total tonnage of all ships, which can be used to assess the overall capacity available for deployment.
-- This summary data helps in understanding the total tonnage available for allocation across all battles.
SELECT SUM(tonnage) AS total_tonnage
FROM ship_tonnage;

-- Query Description: Retrieve the total maximum tonnage allowed across all battles, which can be used to assess the overall capacity required.
-- This summary data helps in understanding the total tonnage capacity needed across all battles.
SELECT SUM(max_tonnage) AS total_max_tonnage
FROM battle_constraints;

-- Query Description: Retrieve the total casualties (killed + injured) for each ship across all battles, which can be used to prioritize ships for deployment.
-- This aggregated data helps in identifying ships that contribute the most to casualties and may need to be deployed strategically.
SELECT ship_id, SUM(killed + injured) AS total_casualties
FROM death
GROUP BY ship_id;
```

These queries are designed to retrieve the most relevant data for the optimization problem, including decision variables, objective function coefficients, constraint parameters, and aggregated summary statistics. The data retrieved will be essential for formulating and solving the optimization model to minimize total casualties across all battles.
