# Complete Optimization Problem and Solution: storm_record

## 1. Problem Context and Goals

### Context  
A disaster response organization is tasked with minimizing the total damage and loss of life caused by storms by optimally allocating resources to the most affected regions. The organization must make decisions on whether to allocate resources to specific regions impacted by specific storms, represented by the decision variable `is_allocated[storm_id, region_id]`. The operational parameters guiding this decision include the total budget available for resource allocation, the maximum speed of storms, and the maximum number of cities that can be affected.  

The total budget for resource allocation is set at 1,500,000 USD, ensuring that resources are prioritized for the most critical regions. The maximum speed of storms is capped at 120 km/h, reflecting the speed at which resources can be effectively deployed. Additionally, the organization has set a limit of 5 cities that can be affected by storms, ensuring that resources are not overextended.  

The organization aims to minimize the weighted sum of damage in millions of USD and the number of deaths caused by storms. The weights `w1` and `w2` are used to balance the importance of minimizing damage versus minimizing loss of life. The damage and death data for each storm are sourced from the `storm_details` table, while the costs associated with resource allocation are sourced from the `allocation_costs` table.  

### Goals  
The primary goal of the optimization is to minimize the combined impact of storm damage and loss of life, weighted by their respective importance. This is achieved by minimizing the sum of the weighted damage in millions of USD and the weighted number of deaths across all storms. Success is measured by the ability to allocate resources efficiently within the constraints of the total budget, maximum storm speed, and maximum number of affected cities.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Budget Constraint**: The total cost of resource allocations across all storms and regions must not exceed the total budget of 1,500,000 USD. This ensures that resource allocation decisions remain financially feasible.  
2. **Speed Constraint**: The speed of each storm must not exceed the maximum speed of 120 km/h. This ensures that resources are only allocated to storms that can be effectively managed given deployment speed limitations.  
3. **Cities Affected Constraint**: The total number of cities affected by storms must not exceed the maximum limit of 5. This ensures that resources are focused on the most critical regions and not spread too thin.  

These constraints are designed to ensure that resource allocation decisions are both operationally feasible and aligned with the organization’s strategic priorities.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 5 Database Schema
-- Objective: Added missing scalar parameters to business configuration logic and ensured all optimization requirements are mapped appropriately.

CREATE TABLE resource_allocation (
  storm_id INTEGER,
  region_id INTEGER,
  is_allocated BOOLEAN
);

CREATE TABLE storm_details (
  storm_id INTEGER,
  storm_speed INTEGER,
  Damage_millions_USD FLOAT,
  Number_Deaths INTEGER
);

CREATE TABLE allocation_costs (
  storm_id INTEGER,
  region_id INTEGER,
  cost_per_allocation INTEGER
);
```

### Data Dictionary  
- **resource_allocation**:  
  - **storm_id**: Unique identifier for the storm. Used to link resource allocation decisions to specific storms.  
  - **region_id**: Unique identifier for the region. Used to link resource allocation decisions to specific regions.  
  - **is_allocated**: Binary indicator (True/False) of whether resources are allocated to a region for a specific storm. This is the primary decision variable in the optimization model.  

- **storm_details**:  
  - **storm_id**: Unique identifier for the storm. Used to link storm details to resource allocation decisions.  
  - **storm_speed**: Speed of the storm in km/h. Used to enforce the speed constraint.  
  - **Damage_millions_USD**: Damage caused by the storm in millions of USD. Used in the objective function to measure the impact of storms.  
  - **Number_Deaths**: Number of deaths caused by the storm. Used in the objective function to measure the impact of storms.  

- **allocation_costs**:  
  - **storm_id**: Unique identifier for the storm. Used to link allocation costs to specific storms.  
  - **region_id**: Unique identifier for the region. Used to link allocation costs to specific regions.  
  - **cost_per_allocation**: Cost to allocate resources to a region for a specific storm. Used in the budget constraint calculation.  


### Retrieved Values

**Query 1: Query Description: Retrieve storm details including speed, damage, and deaths for all storms that meet the speed constraint (<= 120 km/h). This is crucial for the objective function and speed constraint.**

```sql
SELECT storm_id, storm_speed, Damage_millions_USD, Number_Deaths FROM storm_details WHERE storm_speed <= 120;
```

**Results (CSV format):**
```csv
storm_id,storm_speed,Damage_millions_USD,Number_Deaths
1,110,15.5,55
2,90,10.0,40
```

**Query 2: Query Description: Retrieve the cost of resource allocation for each storm and region. This is essential for the budget constraint.**

```sql
SELECT storm_id, region_id, cost_per_allocation FROM allocation_costs;
```

**Results (CSV format):**
```csv
storm_id,region_id,cost_per_allocation
1,101,5000
1,102,4000
2,103,6000
```

**Query 3: Query Description: Retrieve the current resource allocation decisions (is_allocated) for each storm and region. This is the primary decision variable in the optimization model.**

```sql
SELECT storm_id, region_id, is_allocated FROM resource_allocation;
```

**Results (CSV format):**
```csv
storm_id,region_id,is_allocated
1,101,1
1,102,0
2,103,1
```

**Query 4: Query Description: Calculate the total cost of all current resource allocations. This helps in ensuring the budget constraint is not exceeded.**

```sql
SELECT SUM(cost_per_allocation) AS total_cost FROM allocation_costs JOIN resource_allocation ON allocation_costs.storm_id = resource_allocation.storm_id AND allocation_costs.region_id = resource_allocation.region_id WHERE is_allocated = TRUE;
```

**Results (CSV format):**
```csv
total_cost
11000
```

**Query 5: Query Description: Retrieve the total number of cities affected by storms. This is important for the cities affected constraint.**

```sql
SELECT COUNT(DISTINCT region_id) AS total_cities_affected FROM resource_allocation WHERE is_allocated = TRUE;
```

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

**Query 6: Query Description: Retrieve the weighted sum of damage and deaths for each storm. This is crucial for the objective function.**

```sql
SELECT storm_id, (w1 * Damage_millions_USD) + (w2 * Number_Deaths) AS weighted_impact FROM storm_details WHERE storm_speed <= 120;
```

**Error:** Execution failed on sql 'SELECT storm_id, (w1 * Damage_millions_USD) + (w2 * Number_Deaths) AS weighted_impact FROM storm_details WHERE storm_speed <= 120;': no such column: w1

**Query 7: Query Description: Retrieve the total weighted impact across all storms. This is the value to be minimized in the objective function.**

```sql
SELECT SUM((w1 * Damage_millions_USD) + (w2 * Number_Deaths)) AS total_weighted_impact FROM storm_details WHERE storm_speed <= 120;
```

**Error:** Execution failed on sql 'SELECT SUM((w1 * Damage_millions_USD) + (w2 * Number_Deaths)) AS total_weighted_impact FROM storm_details WHERE storm_speed <= 120;': no such column: w1

**Query 8: Query Description: Retrieve the list of storms and regions where resources can be allocated, along with their costs. This helps in making informed allocation decisions.**

```sql
SELECT storm_details.storm_id, allocation_costs.region_id, storm_details.storm_speed, storm_details.Damage_millions_USD, storm_details.Number_Deaths, allocation_costs.cost_per_allocation FROM storm_details JOIN allocation_costs ON storm_details.storm_id = allocation_costs.storm_id WHERE storm_details.storm_speed <= 120;
```

**Results (CSV format):**
```csv
storm_id,region_id,storm_speed,Damage_millions_USD,Number_Deaths,cost_per_allocation
1,101,110,15.5,55,5000
1,102,110,15.5,55,4000
2,103,90,10.0,40,6000
```

**Query 9: Query Description: Retrieve the list of storms and regions where resources are currently allocated. This helps in understanding the current state of resource allocation.**

```sql
SELECT resource_allocation.storm_id, resource_allocation.region_id, storm_details.storm_speed, storm_details.Damage_millions_USD, storm_details.Number_Deaths, allocation_costs.cost_per_allocation FROM resource_allocation JOIN storm_details ON resource_allocation.storm_id = storm_details.storm_id JOIN allocation_costs ON resource_allocation.storm_id = allocation_costs.storm_id AND resource_allocation.region_id = allocation_costs.region_id WHERE is_allocated = TRUE;
```

**Results (CSV format):**
```csv
storm_id,region_id,storm_speed,Damage_millions_USD,Number_Deaths,cost_per_allocation
1,101,110,15.5,55,5000
2,103,90,10.0,40,6000
```

