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

### Current Stored Values  
```sql
-- Iteration 5 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic storm scenarios, typical resource allocation costs, and historical data on storm damage and fatalities. The goal was to ensure that the optimization problem is meaningful and solvable while respecting business constraints.

-- Realistic data for resource_allocation
INSERT INTO resource_allocation (storm_id, region_id, is_allocated) VALUES (1, 101, True);
INSERT INTO resource_allocation (storm_id, region_id, is_allocated) VALUES (1, 102, False);
INSERT INTO resource_allocation (storm_id, region_id, is_allocated) VALUES (2, 103, True);

-- Realistic data for storm_details
INSERT INTO storm_details (storm_id, storm_speed, Damage_millions_USD, Number_Deaths) VALUES (1, 110, 15.5, 55);
INSERT INTO storm_details (storm_id, storm_speed, Damage_millions_USD, Number_Deaths) VALUES (2, 90, 10.0, 40);
INSERT INTO storm_details (storm_id, storm_speed, Damage_millions_USD, Number_Deaths) VALUES (3, 130, 20.0, 70);

-- Realistic data for allocation_costs
INSERT INTO allocation_costs (storm_id, region_id, cost_per_allocation) VALUES (1, 101, 5000);
INSERT INTO allocation_costs (storm_id, region_id, cost_per_allocation) VALUES (1, 102, 4000);
INSERT INTO allocation_costs (storm_id, region_id, cost_per_allocation) VALUES (2, 103, 6000);
```