# Complete Optimization Problem and Solution: storm_record

## 1. Problem Context and Goals

### Context  
The disaster management agency is tasked with efficiently distributing resources to minimize the financial impact of storms across various regions. The agency's primary decision involves determining the quantity of resources to allocate to each region, with the goal of minimizing the total damage cost. This decision-making process is guided by the need to allocate resources in a way that aligns with the available budget and the severity of storm impacts, as indicated by the number of affected cities and the maximum speed of storms. The agency has a fixed amount of resources available for allocation, which serves as a constraint in their planning. The operational parameters are structured to ensure that the decision-making process is linear, focusing on straightforward allocation without complex interactions between variables. The business configuration includes a total resource availability parameter, which is crucial for ensuring that resource distribution remains within feasible limits.

### Goals  
The primary goal of the optimization process is to minimize the total damage cost incurred by storms. This is achieved by strategically allocating resources to regions in a manner that reduces the overall financial impact. The metric for optimization is the total damage cost, calculated as the sum of the product of the damage cost per storm and the resources allocated to each region. Success in this context is measured by the ability to effectively reduce the total damage cost while adhering to the constraints of resource availability and storm severity. The optimization goal is clearly defined in linear terms, focusing on minimizing the financial impact through efficient resource distribution.

## 2. Constraints    

The resource allocation process is subject to several constraints that ensure feasibility and effectiveness. Firstly, the total amount of resources allocated across all regions must not exceed the total resources available. This constraint ensures that the agency operates within its budgetary limits. Additionally, the resources allocated to each region must not surpass the product of the number of cities affected and the maximum speed of storms in that region. This constraint reflects the practical limitations imposed by the severity of storm impacts, ensuring that resource distribution is aligned with the actual needs of each region. These constraints are articulated in business terms that naturally translate into linear mathematical forms, avoiding complex interactions such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating the business configuration logic to include total available resources as a scalar parameter. Adjustments ensure all optimization requirements are mapped and business logic is preserved.

CREATE TABLE storm (
  Damage_millions_USD FLOAT,
  Max_speed FLOAT
);

CREATE TABLE affected_region (
  Number_city_affected INTEGER
);

CREATE TABLE resource_allocation (
  resources_allocated FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive overview of the tables and columns used in the optimization process, highlighting their business purposes and roles in the optimization model:

- **Storm Table**: This table captures data related to storms impacting various regions. It includes:
  - **Damage Cost (in millions USD)**: Represents the financial impact of each storm, serving as a coefficient in the objective function to minimize total damage cost.
  - **Maximum Speed**: Indicates the maximum speed of storms affecting each region, used as a constraint bound for resource allocation.

- **Affected Region Table**: This table contains information about regions impacted by storms. It includes:
  - **Number of Cities Affected**: Reflects the number of cities impacted in each region, serving as a constraint bound for resource allocation.

- **Resource Allocation Table**: This table records the allocation of resources to each region. It includes:
  - **Resources Allocated**: Represents the amount of resources distributed to each region, acting as a decision variable in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on historical storm data and typical resource allocation scenarios in disaster management. The approach ensures that the data reflects realistic storm impacts and resource constraints.

-- Realistic data for storm
INSERT INTO storm (Damage_millions_USD, Max_speed) VALUES (12.0, 110.0);
INSERT INTO storm (Damage_millions_USD, Max_speed) VALUES (25.0, 140.0);
INSERT INTO storm (Damage_millions_USD, Max_speed) VALUES (18.0, 130.0);

-- Realistic data for affected_region
INSERT INTO affected_region (Number_city_affected) VALUES (4);
INSERT INTO affected_region (Number_city_affected) VALUES (6);
INSERT INTO affected_region (Number_city_affected) VALUES (5);

-- Realistic data for resource_allocation
INSERT INTO resource_allocation (resources_allocated) VALUES (150.0);
INSERT INTO resource_allocation (resources_allocated) VALUES (250.0);
INSERT INTO resource_allocation (resources_allocated) VALUES (200.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_i \) be the amount of resources allocated to region \( i \).
  - \( x_i \) is a continuous decision variable representing the resources allocated to each region.

#### Objective Function
Minimize the total damage cost:
\[ \text{Minimize } Z = 12.0x_1 + 25.0x_2 + 18.0x_3 \]

#### Constraints
1. Total resource availability constraint:
   \[ x_1 + x_2 + x_3 \leq 600.0 \]
   - This constraint ensures that the total resources allocated do not exceed the available resources (sum of resources allocated from the `resource_allocation` table).

2. Resource allocation constraints based on storm severity:
   - For region 1:
     \[ x_1 \leq 4 \times 110.0 = 440.0 \]
   - For region 2:
     \[ x_2 \leq 6 \times 140.0 = 840.0 \]
   - For region 3:
     \[ x_3 \leq 5 \times 130.0 = 650.0 \]
   - These constraints ensure that the resources allocated to each region do not exceed the product of the number of cities affected and the maximum speed of storms in that region.

3. Non-negativity constraints:
   \[ x_1, x_2, x_3 \geq 0 \]
   - Resources allocated must be non-negative.

Data Source Verification:
- Coefficients for the objective function (12.0, 25.0, 18.0) are from the `storm.Damage_millions_USD` column.
- The total resource availability constraint (600.0) is derived from the sum of values in the `resource_allocation.resources_allocated` column.
- Constraints for resource allocation based on storm severity use values from `affected_region.Number_city_affected` and `storm.Max_speed` columns.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def storm_record_optimization():
    """Optimize resource allocation to minimize storm damage costs."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("storm_record")
    
    # Data from the problem context
    damage_costs = [12.0, 25.0, 18.0]
    max_speeds = [110.0, 140.0, 130.0]
    num_cities_affected = [4, 6, 5]
    total_resources_available = 600.0
    
    # CRITICAL: Validate array lengths before loops
    n_regions = len(damage_costs)
    assert len(max_speeds) == len(num_cities_affected) == n_regions, "Array length mismatch"
    
    # 2. VARIABLES
    # Variable dictionaries for resource allocation
    x = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{i}", lb=0) 
         for i in range(n_regions)}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total damage cost
    model.setObjective(gp.quicksum(damage_costs[i] * x[i] for i in range(n_regions)), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total resource availability constraint
    model.addConstr(gp.quicksum(x[i] for i in range(n_regions)) <= total_resources_available, name="total_resource_limit")
    
    # Resource allocation constraints based on storm severity
    for i in range(n_regions):
        model.addConstr(x[i] <= num_cities_affected[i] * max_speeds[i], name=f"severity_limit_{i}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_regions):
            if x[i].x > 1e-6:
                print(f"x[{i}] = {x[i].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
storm_record_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 0.78 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution with zero damage cost, indicating no resources need to be allocated to minimize costs. The execution time was very efficient.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def storm_resource_optimization():
    """Optimize resource allocation to minimize storm damage costs."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="storm_record")
    
    # Data from the problem context
    damage_costs = [12.0, 25.0, 18.0]
    max_speeds = [110.0, 140.0, 130.0]
    cities_affected = [4, 6, 5]
    total_resources_available = 600.0
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(damage_costs) == len(max_speeds) == len(cities_affected), "Array length mismatch"
    safe_range = range(min(len(damage_costs), len(max_speeds), len(cities_affected)))  # Safe indexing
    
    # 2. VARIABLES
    x = {i: mdl.continuous_var(name=f"x_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(damage_costs[i] * x[i] for i in safe_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total resource availability constraint
    total_usage = mdl.sum(x[i] for i in safe_range)
    mdl.add_constraint(total_usage <= total_resources_available, ctname="total_resource_limit")
    
    # Resource allocation constraints based on storm severity
    for i in safe_range:
        max_allocation = cities_affected[i] * max_speeds[i]
        mdl.add_constraint(x[i] <= max_allocation, ctname=f"severity_limit_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(x[i])
            if value > 1e-6:
                print(f"x[{i}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
storm_resource_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 10.12 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution with zero damage cost. The execution time was longer compared to Gurobipy, but the results are consistent.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def storm_record_optimization():
    """Optimization for resource allocation to minimize storm damage cost"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    n_regions = 3
    damage_costs = [12.0, 25.0, 18.0]
    max_speeds = [110.0, 140.0, 130.0]
    cities_affected = [4, 6, 5]
    total_resources = 600.0
    
    # CRITICAL: Validate array lengths before indexing
    assert len(damage_costs) == len(max_speeds) == len(cities_affected) == n_regions, "Array length mismatch"
    
    # 3. SETS
    model.I = pyo.RangeSet(1, n_regions)  # 1-based indexing
    
    # 4. PARAMETERS
    model.damage_cost = pyo.Param(model.I, initialize={i+1: damage_costs[i] for i in range(n_regions)})
    model.max_speed = pyo.Param(model.I, initialize={i+1: max_speeds[i] for i in range(n_regions)})
    model.cities_affected = pyo.Param(model.I, initialize={i+1: cities_affected[i] for i in range(n_regions)})
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.damage_cost[i] * model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Total resource availability constraint
    def total_resource_constraint_rule(model):
        return sum(model.x[i] for i in model.I) <= total_resources
    model.total_resource_constraint = pyo.Constraint(rule=total_resource_constraint_rule)
    
    # Resource allocation constraints based on storm severity
    def resource_allocation_rule(model, i):
        return model.x[i] <= model.cities_affected[i] * model.max_speed[i]
    model.resource_allocation_constraint = pyo.Constraint(model.I, rule=resource_allocation_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective):.3f}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.I:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{i}] = {x_val:.3f}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Run the optimization
storm_record_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 6.64 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with zero damage cost. The execution time was between Gurobipy and DOCplex, and the results are consistent with the other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 0.00 | 0.78s | N/A | N/A |
| Docplex | OPTIMAL | 0.00 | 10.12s | N/A | N/A |
| Pyomo | OPTIMAL | 0.00 | 6.64s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 0.0

### Final Recommendation
**Recommended Optimal Value**: 0.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided the optimal solution with the fastest execution time, making it the most efficient choice for this problem.

### Optimal Decision Variables
- **x_1** = 0.000
  - *Business Meaning*: Amount of resources allocated to region 2, which is optimal at 0.0
- **x_2** = 0.000
  - *Business Meaning*: Amount of resources allocated to region 3, which is optimal at 0.0
- **x_3** = 0.000
  - *Business Meaning*: Resource allocation for x_3

### Business Interpretation
**Overall Strategy**: The optimal solution suggests that no resources need to be allocated to any region to minimize damage costs, indicating that the current situation does not require intervention.
**Objective Value Meaning**: The optimal objective value of 0.0 indicates that no damage costs are incurred, suggesting no immediate need for resource allocation.
**Resource Allocation Summary**: No resources need to be allocated to any region under the current conditions to achieve the minimum damage cost.
**Implementation Recommendations**: Monitor the situation for any changes in storm severity or resource availability that might necessitate a re-evaluation of resource allocation.