# Complete Optimization Problem and Solution: world_1

## 1. Problem Context and Goals

### Context  
A global logistics company is focused on optimizing the distribution of goods from a central warehouse to various cities. The primary decision involves determining the quantity of goods to be shipped to each city, where this quantity is a continuous variable. The company aims to minimize the total transportation cost, which is calculated by multiplying the transportation cost per unit for each city by the quantity of goods shipped to that city. The total number of goods available for distribution acts as a constraint, ensuring that the sum of goods shipped does not exceed this total. Additionally, the company must consider each city's demand and the maximum capacity that can be shipped to each city. These operational parameters are crucial for maintaining a linear relationship in the optimization model, avoiding any nonlinear scenarios such as variable products or divisions. The business configuration includes scalar parameters like the total available goods and business logic formulas that calculate the total transportation cost, ensuring consistency and flexibility in parameter tuning.

### Goals  
The primary goal of the optimization is to minimize the total transportation cost associated with distributing goods from the central warehouse to various cities. This involves optimizing the metric of total transportation cost, which is the sum of the transportation cost per unit for each city multiplied by the quantity of goods shipped to that city. Success in this optimization is measured by achieving the lowest possible transportation cost while adhering to the constraints of total available goods, city demands, and city capacities. The goal is articulated in natural language to maintain a clear focus on linear optimization without resorting to mathematical formulas or symbolic notation.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the solution is feasible and aligns with business requirements. These constraints are:

- The total quantity of goods shipped to all cities must not exceed the total number of goods available for distribution. This ensures that the sum of quantities shipped respects the overall supply limit.
- The quantity of goods shipped to each city must not exceed the maximum capacity that can be handled by that city. This constraint ensures that city-specific logistical limits are not breached.
- The quantity of goods shipped to each city must meet or exceed the demand of that city. This ensures that each city's demand is satisfied, maintaining service levels and customer satisfaction.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for decision variables and updated business configuration logic for scalar parameters and formulas based on OR expert mapping analysis.

CREATE TABLE TransportationCosts (
  city_id INTEGER,
  cost_per_unit FLOAT
);

CREATE TABLE CityCapacities (
  city_id INTEGER,
  max_capacity INTEGER
);

CREATE TABLE City (
  city_id INTEGER,
  city_demand INTEGER
);

CREATE TABLE QuantityShipped (
  city_id INTEGER,
  quantity FLOAT
);
```

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

- **TransportationCosts**: This table stores the cost of transporting one unit of goods to each city. It plays a critical role in the objective function as it provides the coefficients needed to calculate the total transportation cost.
  - **city_id**: A unique identifier for each city, linking transportation costs to specific cities.
  - **cost_per_unit**: Represents the cost to transport one unit of goods to the city, serving as a coefficient in the objective function.

- **CityCapacities**: This table contains the maximum capacity of goods that can be shipped to each city, serving as a constraint bound in the optimization model.
  - **city_id**: A unique identifier for each city, linking capacities to specific cities.
  - **max_capacity**: The maximum number of goods that can be shipped to the city, used as a constraint in the optimization.

- **City**: This table holds information about each city, including the demand for goods, which acts as a constraint bound in the optimization.
  - **city_id**: A unique identifier for each city, serving as the primary key for city data.
  - **city_demand**: The demand for goods in the city, used as a constraint in the optimization.

- **QuantityShipped**: This table records the quantity of goods shipped to each city, representing the decision variables in the optimization model.
  - **city_id**: A unique identifier for each city, linking quantities to specific cities.
  - **quantity**: The number of goods shipped to the city, serving as a decision variable in the optimization.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical logistics scenarios, ensuring that transportation costs, city demands, and capacities are realistic and align with the total available goods.

-- Realistic data for TransportationCosts
INSERT INTO TransportationCosts (city_id, cost_per_unit) VALUES (1, 5.0);
INSERT INTO TransportationCosts (city_id, cost_per_unit) VALUES (2, 10.0);
INSERT INTO TransportationCosts (city_id, cost_per_unit) VALUES (3, 15.0);

-- Realistic data for CityCapacities
INSERT INTO CityCapacities (city_id, max_capacity) VALUES (1, 100);
INSERT INTO CityCapacities (city_id, max_capacity) VALUES (2, 200);
INSERT INTO CityCapacities (city_id, max_capacity) VALUES (3, 300);

-- Realistic data for City
INSERT INTO City (city_id, city_demand) VALUES (1, 50);
INSERT INTO City (city_id, city_demand) VALUES (2, 150);
INSERT INTO City (city_id, city_demand) VALUES (3, 250);

-- Realistic data for QuantityShipped
INSERT INTO QuantityShipped (city_id, quantity) VALUES (1, 60.0);
INSERT INTO QuantityShipped (city_id, quantity) VALUES (2, 150.0);
INSERT INTO QuantityShipped (city_id, quantity) VALUES (3, 290.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_1 \), \( x_2 \), and \( x_3 \) represent the quantity of goods shipped to cities 1, 2, and 3, respectively. These are continuous decision variables.

#### Objective Function
Minimize the total transportation cost:
\[ \text{Minimize } Z = 5.0x_1 + 10.0x_2 + 15.0x_3 \]

Data Source Verification:
- Coefficients 5.0, 10.0, and 15.0 are from `TransportationCosts.cost_per_unit` for cities 1, 2, and 3, respectively.

#### Constraints
1. Total goods shipped must not exceed the total available goods:
   \[ x_1 + x_2 + x_3 \leq 500 \]
   - Total available goods is assumed to be 500 based on the sum of maximum capacities.

2. Quantity shipped to each city must not exceed the city's maximum capacity:
   \[ x_1 \leq 100 \]
   \[ x_2 \leq 200 \]
   \[ x_3 \leq 300 \]

   Data Source Verification:
   - Capacities 100, 200, and 300 are from `CityCapacities.max_capacity` for cities 1, 2, and 3, respectively.

3. Quantity shipped to each city must meet or exceed the city's demand:
   \[ x_1 \geq 50 \]
   \[ x_2 \geq 150 \]
   \[ x_3 \geq 250 \]

   Data Source Verification:
   - Demands 50, 150, and 250 are from `City.city_demand` for cities 1, 2, and 3, respectively.

This linear programming model is now complete and ready to be solved using appropriate optimization software.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_transportation():
    """Optimize transportation costs for a logistics company"""

    # 1. MODEL & DATA SETUP
    model = gp.Model("transportation_optimization")

    # Data from the problem statement
    city_ids = [1, 2, 3]
    costs = [5.0, 10.0, 15.0]
    demands = [50, 150, 250]
    capacities = [100, 200, 300]
    total_available_goods = 500

    # CRITICAL: Validate array lengths before loops
    assert len(city_ids) == len(costs) == len(demands) == len(capacities), "Array length mismatch"

    # 2. VARIABLES
    # Variable dictionaries for quantities shipped to each city
    quantity_shipped = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{i}", lb=0) for i in city_ids}

    # 3. OBJECTIVE FUNCTION
    # Minimize the total transportation cost
    model.setObjective(gp.quicksum(costs[i-1] * quantity_shipped[i] for i in city_ids), GRB.MINIMIZE)

    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS

    # Total goods shipped must not exceed the total available goods
    model.addConstr(gp.quicksum(quantity_shipped[i] for i in city_ids) <= total_available_goods, name="total_goods_limit")

    # Quantity shipped to each city must not exceed the city's maximum capacity
    for i in city_ids:
        model.addConstr(quantity_shipped[i] <= capacities[i-1], name=f"capacity_limit_{i}")

    # Quantity shipped to each city must meet or exceed the city's demand
    for i in city_ids:
        model.addConstr(quantity_shipped[i] >= demands[i-1], name=f"demand_satisfaction_{i}")

    # 5. SOLVING & RESULTS
    model.optimize()

    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in city_ids:
            if quantity_shipped[i].x > 1e-6:
                print(f"Quantity shipped to city {i}: {quantity_shipped[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
optimize_transportation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 5500.0
**Execution Time**: 2.22 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with a minimal execution time, indicating high efficiency and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_transportation():
    """Optimize transportation costs for a logistics company"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="transportation_optimization")
    
    # Data from the problem context
    costs = [5.0, 10.0, 15.0]
    demands = [50, 150, 250]
    capacities = [100, 200, 300]
    total_available_goods = 500
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(costs) == len(demands) == len(capacities), "Array length mismatch"
    safe_range = range(min(len(costs), len(demands), len(capacities)))  # 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(costs[i] * x[i] for i in safe_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    # Total goods shipped must not exceed the total available goods
    total_shipped = mdl.sum(x[i] for i in safe_range)
    mdl.add_constraint(total_shipped <= total_available_goods, ctname="total_goods_limit")
    
    # Quantity shipped to each city must not exceed the city's maximum capacity
    for i in safe_range:
        mdl.add_constraint(x[i] <= capacities[i], ctname=f"capacity_limit_{i}")
    
    # Quantity shipped to each city must meet or exceed the city's demand
    for i in safe_range:
        mdl.add_constraint(x[i] >= demands[i], ctname=f"demand_satisfaction_{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
optimize_transportation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 5500.0
**Execution Time**: 9.23 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution, albeit with a longer execution time compared to Gurobipy, but still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def logistics_optimization():
    """Logistics optimization using Pyomo"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    n_cities = 3
    costs = [5.0, 10.0, 15.0]
    demands = [50, 150, 250]
    capacities = [100, 200, 300]
    total_available_goods = 500
    
    # CRITICAL: Validate array lengths before indexing
    assert len(costs) == len(demands) == len(capacities) == n_cities, "Array length mismatch"
    
    # 3. SETS
    model.C = pyo.RangeSet(1, n_cities)  # 1-based indexing for cities
    
    # 4. PARAMETERS
    model.cost = pyo.Param(model.C, initialize={i+1: costs[i] for i in range(n_cities)})
    model.demand = pyo.Param(model.C, initialize={i+1: demands[i] for i in range(n_cities)})
    model.capacity = pyo.Param(model.C, initialize={i+1: capacities[i] for i in range(n_cities)})
    
    # 5. VARIABLES
    model.x = pyo.Var(model.C, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost[i] * model.x[i] for i in model.C)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Total goods shipped must not exceed the total available goods
    def total_goods_rule(model):
        return sum(model.x[i] for i in model.C) <= total_available_goods
    model.total_goods_constraint = pyo.Constraint(rule=total_goods_rule)
    
    # Quantity shipped to each city must not exceed the city's maximum capacity
    def capacity_rule(model, i):
        return model.x[i] <= model.capacity[i]
    model.capacity_constraint = pyo.Constraint(model.C, rule=capacity_rule)
    
    # Quantity shipped to each city must meet or exceed the city's demand
    def demand_rule(model, i):
        return model.x[i] >= model.demand[i]
    model.demand_constraint = pyo.Constraint(model.C, rule=demand_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Solve the model
    results = solver.solve(model, tee=True)
    
    # 9. RESULT PROCESSING
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective):.2f}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.C:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{i}] = {x_val:.2f}")
        
    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
logistics_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 5500.0
**Execution Time**: 8.13 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with a moderate execution time, confirming its reliability.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 5500.00 | 2.22s | N/A | N/A |
| Docplex | OPTIMAL | 5500.00 | 9.23s | N/A | N/A |
| Pyomo | OPTIMAL | 5500.00 | 8.13s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 5500.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its fastest execution time and high reliability, making it suitable for time-sensitive applications.

### Optimal Decision Variables
- **x_1** = 100.000
  - *Business Meaning*: Quantity of goods shipped to city 2, optimized at 150 units
- **x_2** = 150.000
  - *Business Meaning*: Quantity of goods shipped to city 3, optimized at 250 units
- **x_3** = 250.000
  - *Business Meaning*: Resource allocation for x_3

### Business Interpretation
**Overall Strategy**: The optimal transportation cost is minimized at $5500, meeting all city demands and capacity constraints.
**Objective Value Meaning**: The optimal objective value of $5500 represents the minimum transportation cost to meet all city demands within capacity limits.
**Resource Allocation Summary**: Allocate 100 units to city 1, 150 units to city 2, and 250 units to city 3 to achieve cost efficiency.
**Implementation Recommendations**: Implement the shipping plan as per the recommended decision variables to ensure cost-effective distribution while satisfying all constraints.