# Complete Optimization Problem and Solution: battle_death

## 1. Problem Context and Goals

### Context  
In the context of naval operations, the strategic allocation of ships to battles is crucial to minimizing casualties. The decision-making process involves determining whether a specific ship should be assigned to a particular battle. This decision is represented by a binary choice, where each ship can either be assigned to a battle or not. The primary objective is to minimize the total number of deaths resulting from these assignments. This is achieved by considering the number of deaths each ship could potentially cause in a battle and making assignments that minimize this total.

Operational parameters include a maximum limit on the number of ships available for assignment, ensuring that the fleet's resources are not overextended. The business logic involves calculating the total deaths caused by ship assignments, which serves as the objective function to minimize. This calculation is straightforward and linear, focusing on the sum of potential deaths weighted by the assignment decisions.

The data used in this process reflects current operational scenarios, ensuring that decisions are based on realistic and historical naval battle data. The constraints are linear, focusing on resource limitations and ensuring that each ship is assigned to at most one battle, while each battle receives the required number of ships. The business configuration includes both scalar parameters, such as the maximum number of ships available, and business logic formulas that guide the decision-making process.

### Goals  
The primary goal of this optimization problem is to minimize the total number of deaths resulting from ship assignments to battles. This is achieved by strategically assigning ships in a way that reduces the potential casualties. The metric used to measure success is the total number of deaths, calculated as the sum of deaths caused by each ship in each battle, weighted by the assignment decisions. The optimization goal is clearly defined and aligns with the linear nature of the problem, focusing on minimizing this total in a straightforward manner.

## 2. Constraints    

The constraints in this optimization problem are designed to ensure that the ship assignments are both feasible and optimal:

- Each ship can be assigned to at most one battle. This constraint ensures that no ship is overcommitted, reflecting the operational reality that a ship cannot be in two places at once.
- Each battle must receive at least the minimum required number of ships. This ensures that the battles are adequately supported, aligning with strategic requirements.
- The total number of ships assigned cannot exceed the maximum number available. This constraint reflects the resource limitations of the fleet, ensuring that assignments do not exceed the available resources.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Incorporated missing data for deaths caused by ships in battles, adjusted schema to include objective coefficients, and updated business configuration logic for scalar parameters and formulas.

CREATE TABLE ship_battle_assignment (
  ship_id INTEGER,
  battle_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE battle_requirements (
  battle_id INTEGER,
  min_ships INTEGER
);

CREATE TABLE deaths_by_ship_battle (
  ship_id INTEGER,
  battle_id INTEGER,
  deaths INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **Ship Battle Assignment Table**: This table tracks which ships are assigned to which battles. It plays a critical role in decision-making, with each assignment represented as a binary decision variable.
  - **Ship ID**: Identifies each ship, used to track assignments.
  - **Battle ID**: Identifies each battle, used to track assignments.
  - **Assignment**: Indicates whether a ship is assigned to a battle, serving as the binary decision variable.

- **Battle Requirements Table**: This table stores the minimum number of ships required for each battle, serving as a constraint bound in the optimization process.
  - **Battle ID**: Identifies each battle, linking to requirement checks.
  - **Minimum Ships**: Specifies the minimum number of ships needed for a battle, acting as a constraint bound.

- **Deaths by Ship Battle Table**: This table records the number of deaths caused by each ship in each battle, serving as the objective coefficients in the optimization problem.
  - **Ship ID**: Identifies each ship, used in death calculations.
  - **Battle ID**: Identifies each battle, used in death calculations.
  - **Deaths**: Represents the number of deaths caused by a ship in a battle, acting as a coefficient in the objective function.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical naval battle scenarios, ensuring that ship assignments and battle requirements are realistic and align with historical data on ship deployments and battle outcomes.

-- Realistic data for ship_battle_assignment
INSERT INTO ship_battle_assignment (ship_id, battle_id, assignment) VALUES (1, 101, True);
INSERT INTO ship_battle_assignment (ship_id, battle_id, assignment) VALUES (2, 102, True);
INSERT INTO ship_battle_assignment (ship_id, battle_id, assignment) VALUES (3, 103, False);

-- Realistic data for battle_requirements
INSERT INTO battle_requirements (battle_id, min_ships) VALUES (101, 3);
INSERT INTO battle_requirements (battle_id, min_ships) VALUES (102, 5);
INSERT INTO battle_requirements (battle_id, min_ships) VALUES (103, 2);

-- Realistic data for deaths_by_ship_battle
INSERT INTO deaths_by_ship_battle (ship_id, battle_id, deaths) VALUES (1, 101, 15);
INSERT INTO deaths_by_ship_battle (ship_id, battle_id, deaths) VALUES (2, 102, 25);
INSERT INTO deaths_by_ship_battle (ship_id, battle_id, deaths) VALUES (3, 103, 5);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary decision variable where \( x_{ij} = 1 \) if ship \( i \) is assigned to battle \( j \), and \( x_{ij} = 0 \) otherwise.

#### Objective Function
Minimize the total number of deaths:
\[
\text{Minimize } \sum_{i,j} \text{deaths}_{ij} \times x_{ij}
\]
where \(\text{deaths}_{ij}\) is the number of deaths caused by ship \( i \) in battle \( j \), sourced from the `deaths_by_ship_battle` table.

#### Constraints
1. **Each ship can be assigned to at most one battle:**
   \[
   \sum_{j} x_{ij} \leq 1 \quad \forall i
   \]

2. **Each battle must receive at least the minimum required number of ships:**
   \[
   \sum_{i} x_{ij} \geq \text{min\_ships}_j \quad \forall j
   \]
   where \(\text{min\_ships}_j\) is the minimum number of ships required for battle \( j \), sourced from the `battle_requirements` table.

3. **The total number of ships assigned cannot exceed the maximum number available:**
   \[
   \sum_{i,j} x_{ij} \leq \text{max\_ships}
   \]
   where \(\text{max\_ships}\) is a scalar parameter representing the maximum number of ships available for assignment.

#### Data Source Verification:
- \(\text{deaths}_{ij}\) from `deaths_by_ship_battle.deaths`
- \(\text{min\_ships}_j\) from `battle_requirements.min_ships`
- \(\text{max\_ships}\) is a business configuration parameter not explicitly provided in the data but assumed to be known from operational constraints.

This formulation provides a complete and solvable linear model for minimizing casualties by optimally assigning ships to battles under the given constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation - Retry Attempt 4

import gurobipy as gp
from gurobipy import GRB

def naval_optimization():
    # 1. MODEL & DATA SETUP
    model = gp.Model("battle_death_minimization")
    
    # Example data setup (replace with actual data retrieval logic)
    ships = [1, 2, 3]
    battles = [101, 102, 103]
    deaths_data = {
        (1, 101): 15,
        (2, 102): 25,
        (3, 103): 5
    }
    min_ships_required = {
        101: 3,
        102: 5,
        103: 2
    }
    max_ships = 5  # Example maximum number of ships available

    # CRITICAL: Validate array lengths before loops
    assert len(deaths_data) == len(ships) * len(battles), "Array length mismatch"

    # 2. VARIABLES
    # Binary decision variables for ship assignments
    x = model.addVars(ships, battles, vtype=GRB.BINARY, name="x")

    # 3. OBJECTIVE FUNCTION
    # Minimize the total number of deaths
    model.setObjective(gp.quicksum(deaths_data[i, j] * x[i, j] for i in ships for j in battles), GRB.MINIMIZE)

    # 4. CONSTRAINTS
    # Each ship can be assigned to at most one battle
    model.addConstrs((gp.quicksum(x[i, j] for j in battles) <= 1 for i in ships), name="ship_assignment")

    # Each battle must receive at least the minimum required number of ships
    model.addConstrs((gp.quicksum(x[i, j] for i in ships) >= min_ships_required[j] for j in battles), name="battle_requirement")

    # The total number of ships assigned cannot exceed the maximum number available
    model.addConstr(gp.quicksum(x[i, j] for i in ships for j in battles) <= max_ships, name="max_ships")

    # 5. SOLVING & RESULTS
    model.optimize()

    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in ships:
            for j in battles:
                if x[i, j].x > 1e-6:
                    print(f"Ship {i} assigned to Battle {j}: {x[i, j].x:.0f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")

    return model

# Run the optimization
naval_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpth5ku8br.py", line 63, in <module>
    naval_optimization()
  File "/tmp/tmpth5ku8br.py", line 26, in naval_optimization
    assert len(deaths_data) == len(ships) * len(battles), "Array length mismatch"
AssertionError: Array length mismatch

**Analysis**: Gurobipy encountered an 'Array length mismatch' error, indicating a potential issue with the input data dimensions or data integrity.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation - Retry Attempt 4

from docplex.mp.model import Model

def naval_optimization():
    """Optimizes ship assignments to minimize battle deaths."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="battle_death_minimization")
    
    # Data from the problem context
    ships = [1, 2, 3]
    battles = [101, 102, 103]
    deaths_data = {
        (1, 101): 15,
        (2, 102): 25,
        (3, 103): 5
    }
    min_ships_required = {
        101: 3,
        102: 5,
        103: 2
    }
    max_ships = 5  # Assumed maximum number of ships available
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(deaths_data) == len(ships), "Array length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables for ship assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in ships for j in battles}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total deaths
    objective = mdl.sum(deaths_data.get((i, j), 0) * x[i, j] for i in ships for j in battles)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Each ship can be assigned to at most one battle
    for i in ships:
        mdl.add_constraint(mdl.sum(x[i, j] for j in battles) <= 1, ctname=f"ship_{i}_assignment")
    
    # Each battle must receive at least the minimum required number of ships
    for j in battles:
        mdl.add_constraint(mdl.sum(x[i, j] for i in ships) >= min_ships_required[j], ctname=f"battle_{j}_min_ships")
    
    # The total number of ships assigned cannot exceed the maximum number available
    mdl.add_constraint(mdl.sum(x[i, j] for i in ships for j in battles) <= max_ships, ctname="max_ships_limit")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in ships:
            for j in battles:
                if solution.get_value(x[i, j]) > 0.5:  # Binary variable threshold
                    print(f"Ship {i} assigned to Battle {j}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

# Execute the optimization
naval_optimization()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: DOCplex reported the problem as infeasible, suggesting that the constraints may be too restrictive or conflicting.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation - Retry Attempt 4

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

def naval_optimization():
    """Optimize ship assignments to minimize battle deaths."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data based on the problem description
    ships = [1, 2, 3]
    battles = [101, 102, 103]
    deaths_data = {
        (1, 101): 15,
        (2, 102): 25,
        (3, 103): 5
    }
    min_ships_data = {
        101: 3,
        102: 5,
        103: 2
    }
    max_ships = 5  # Example maximum number of ships available
    
    # CRITICAL: Validate array lengths before indexing
    assert len(deaths_data) == len(ships) * len(battles), "Array length mismatch"
    
    # 3. SETS
    model.Ships = pyo.Set(initialize=ships)
    model.Battles = pyo.Set(initialize=battles)
    
    # 4. PARAMETERS
    model.deaths = pyo.Param(model.Ships, model.Battles, initialize=deaths_data, default=0)
    model.min_ships = pyo.Param(model.Battles, initialize=min_ships_data)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.Ships, model.Battles, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.deaths[i, j] * model.x[i, j] for i in model.Ships for j in model.Battles)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Each ship can be assigned to at most one battle
    def ship_assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.Battles) <= 1
    model.ship_assignment_constraint = pyo.Constraint(model.Ships, rule=ship_assignment_rule)
    
    # Each battle must receive at least the minimum required number of ships
    def battle_requirement_rule(model, j):
        return sum(model.x[i, j] for i in model.Ships) >= model.min_ships[j]
    model.battle_requirement_constraint = pyo.Constraint(model.Battles, rule=battle_requirement_rule)
    
    # The total number of ships assigned cannot exceed the maximum number available
    def max_ships_rule(model):
        return sum(model.x[i, j] for i in model.Ships for j in model.Battles) <= max_ships
    model.max_ships_constraint = pyo.Constraint(rule=max_ships_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)
    
    # 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)}")
        
        # Extract variable values
        print("\nAssignment values:")
        for i in model.Ships:
            for j in model.Battles:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0.5:  # Only print assignments
                    print(f"Ship {i} assigned to Battle {j}")
        
    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

# Execute the optimization
naval_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmp3zx6t6c7.py", line 98, in <module>
    naval_optimization()
  File "/tmp/tmp3zx6t6c7.py", line 29, in naval_optimization
    assert len(deaths_data) == len(ships) * len(battles), "Array length mismatch"
AssertionError: Array length mismatch

**Analysis**: Pyomo also encountered an 'Array length mismatch' error, similar to Gurobipy, indicating a consistent issue with the input data.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | ERROR | N/A | 0.77s | N/A | 4 |
| Docplex | INFEASIBLE | N/A | 8.08s | N/A | 4 |
| Pyomo | ERROR | N/A | 4.36s | N/A | 4 |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Inconsistent Solvers**: gurobipy, docplex, pyomo
**Potential Issues**:
- Data integrity issues leading to array length mismatches
- Overly restrictive or conflicting constraints causing infeasibility
- Incorrect or incomplete data input
**Solver Retry Summary**: gurobipy: 4 attempts, docplex: 4 attempts, pyomo: 4 attempts

### Final Recommendation
**Confidence Level**: LOW
**Preferred Solver(s)**: multiple
**Reasoning**: Given the errors and infeasibility, no single solver can be recommended. The focus should be on resolving data and model issues.

### Business Interpretation
**Overall Strategy**: The current model and data inputs are not yielding a feasible or error-free solution, indicating a need for data verification and model adjustment.
**Objective Value Meaning**: The optimal objective value represents the minimized total number of deaths across all battles.
**Resource Allocation Summary**: Currently, no feasible allocation of ships to battles can be determined due to data and model issues.
**Implementation Recommendations**: Verify and correct the input data dimensions and integrity. Re-evaluate the constraints to ensure they are not overly restrictive or conflicting. Once resolved, re-run the optimization using multiple solvers to ensure consistency.