# Complete Optimization Problem and Solution: device

## 1. Problem Context and Goals

### Context  
The retail company is focused on optimizing the distribution of its devices across various shops to maximize potential sales. The decision-making process involves determining the number of devices to allocate to each shop, represented by integer variables. The primary objective is to maximize the total sales potential, which is calculated by multiplying the estimated sales potential of each device at each shop by the number of devices allocated. The company must consider the maximum capacity of each shop, which serves as a constraint in the optimization model. The potential sales for each device at each shop are determined using a business logic formula that considers base sales and a demand factor. This approach ensures that the decision-making process is linear, avoiding any nonlinear relationships such as variable products or divisions. The operational parameters, including shop capacity and potential sales, are clearly defined to align with the expected coefficient sources in the optimization model.

### Goals  
The primary goal of the optimization process is to maximize the total potential sales across all shops. This involves strategically allocating devices to shops in a way that maximizes the sum of the estimated sales potential for each device, multiplied by the number of devices allocated. Success is measured by the total sales potential achieved, ensuring that the allocation aligns with the expected coefficient sources. The optimization goal is clearly defined in linear terms, focusing on maximizing the total sales potential without involving any complex mathematical expressions.

## 2. Constraints    

The optimization model is subject to several constraints that ensure the feasibility of the solution:

- The total number of devices allocated to each shop must not exceed the shop's maximum capacity. This constraint ensures that the allocation respects the physical limitations of each shop.
- The number of devices allocated to each shop must be a non-negative integer, reflecting the practical requirement that allocations cannot be negative or fractional.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and manageable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization data and updating configuration logic for scalar parameters and formulas.

CREATE TABLE PotentialSales (
  shop_id INTEGER,
  device_id INTEGER,
  potential_sales FLOAT
);

CREATE TABLE ShopCapacity (
  shop_id INTEGER,
  capacity INTEGER
);

CREATE TABLE Allocation (
  shop_id INTEGER,
  device_id INTEGER,
  quantity INTEGER
);
```

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

- **PotentialSales Table**: This table estimates the sales potential for each device at each shop. It plays a crucial role in determining the coefficients for the objective function in the optimization model.
  - **shop_id**: Represents the unique identifier for each shop.
  - **device_id**: Represents the unique identifier for each device.
  - **potential_sales**: Indicates the estimated sales potential for a specific device at a specific shop.

- **ShopCapacity Table**: This table defines the maximum number of devices each shop can hold, serving as a constraint in the optimization model.
  - **shop_id**: Represents the unique identifier for each shop.
  - **capacity**: Indicates the maximum capacity of the shop in terms of device allocation.

- **Allocation Table**: This table records the number of devices allocated to each shop, representing the decision variables in the optimization model.
  - **shop_id**: Represents the unique identifier for each shop.
  - **device_id**: Represents the unique identifier for each device.
  - **quantity**: Indicates the number of devices allocated to a specific shop.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical retail scenarios where device allocation is influenced by shop capacity and potential sales. The values ensure a balance between demand and supply, reflecting realistic business operations.

-- Realistic data for PotentialSales
INSERT INTO PotentialSales (shop_id, device_id, potential_sales) VALUES (1, 101, 600.0);
INSERT INTO PotentialSales (shop_id, device_id, potential_sales) VALUES (2, 102, 850.0);
INSERT INTO PotentialSales (shop_id, device_id, potential_sales) VALUES (3, 103, 950.0);

-- Realistic data for ShopCapacity
INSERT INTO ShopCapacity (shop_id, capacity) VALUES (1, 120);
INSERT INTO ShopCapacity (shop_id, capacity) VALUES (2, 180);
INSERT INTO ShopCapacity (shop_id, capacity) VALUES (3, 220);

-- Realistic data for Allocation
INSERT INTO Allocation (shop_id, device_id, quantity) VALUES (1, 101, 30);
INSERT INTO Allocation (shop_id, device_id, quantity) VALUES (2, 102, 50);
INSERT INTO Allocation (shop_id, device_id, quantity) VALUES (3, 103, 70);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be the number of devices allocated to shop \( i \) for device \( j \).
  - \( x_{ij} \) is a non-negative integer.

#### Objective Function
Maximize the total potential sales across all shops:
\[ \text{Maximize } Z = \sum_{i,j} \text{potential\_sales}_{ij} \times x_{ij} \]

Where:
- \(\text{potential\_sales}_{ij}\) is the estimated sales potential for device \( j \) at shop \( i \).

#### Constraints
1. Shop Capacity Constraints:
   - For each shop \( i \), the total number of devices allocated must not exceed the shop's capacity:
   \[ \sum_{j} x_{ij} \leq \text{capacity}_i \]
   - Where \(\text{capacity}_i\) is the maximum capacity of shop \( i \).

2. Non-negativity and Integer Constraints:
   - \( x_{ij} \geq 0 \) and \( x_{ij} \) is an integer for all \( i, j \).

Data Source Verification:
- \(\text{potential\_sales}_{ij}\) comes from the `PotentialSales` table, specifically `PotentialSales.potential_sales`.
- \(\text{capacity}_i\) comes from the `ShopCapacity` table, specifically `ShopCapacity.capacity`.

### Numerical Coefficients from Provided Data
Using the provided data, the model can be instantiated as follows:

Objective Function:
\[ \text{Maximize } Z = 600x_{1,101} + 850x_{2,102} + 950x_{3,103} \]

Constraints:
1. For shop 1:
   \[ x_{1,101} \leq 120 \]

2. For shop 2:
   \[ x_{2,102} \leq 180 \]

3. For shop 3:
   \[ x_{3,103} \leq 220 \]

4. Non-negativity and Integer Constraints:
   - \( x_{1,101}, x_{2,102}, x_{3,103} \geq 0 \) and are integers.

This formulation is ready to be solved using a linear or mixed-integer programming solver, ensuring that the allocation of devices maximizes the total potential sales while respecting shop capacities.

## 5. Gurobipy Implementation

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

import gurobipy as gp
from gurobipy import GRB

def optimize_device_allocation():
    # 1. MODEL & DATA SETUP
    model = gp.Model("device_allocation")
    
    # Data from the problem statement
    shop_ids = [1, 2, 3]
    device_ids = [101, 102, 103]
    
    # Potential sales data
    potential_sales = {
        (1, 101): 600.0,
        (2, 102): 850.0,
        (3, 103): 950.0
    }
    
    # Shop capacity data
    shop_capacity = {
        1: 120,
        2: 180,
        3: 220
    }
    
    # Validate data lengths
    assert len(potential_sales) == len(shop_ids) == len(device_ids), "Data length mismatch"
    
    # 2. VARIABLES
    # Decision variables: number of devices allocated to each shop
    x = model.addVars(shop_ids, device_ids, vtype=GRB.INTEGER, name="x", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total potential sales
    model.setObjective(
        gp.quicksum(potential_sales[i, j] * x[i, j] for i in shop_ids for j in device_ids if (i, j) in potential_sales),
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS
    # Shop capacity constraints
    for i in shop_ids:
        model.addConstr(
            gp.quicksum(x[i, j] for j in device_ids if (i, j) in potential_sales) <= shop_capacity[i],
            name=f"capacity_{i}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in shop_ids:
            for j in device_ids:
                if (i, j) in potential_sales and x[i, j].x > 1e-6:
                    print(f"x[{i},{j}] = {x[i, j].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")

# Run the optimization
optimize_device_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 434000.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Retry Attempt**: 3
**Analysis**: Gurobipy successfully found an optimal solution with the expected objective value. The execution time was efficient, and no errors were encountered.

## 6. DOCplex Implementation

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

from docplex.mp.model import Model

def device_allocation_optimization():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="device_allocation")
    
    # Data from the problem statement
    shop_ids = [1, 2, 3]
    device_ids = [101, 102, 103]
    potential_sales = {
        (1, 101): 600.0,
        (2, 102): 850.0,
        (3, 103): 950.0
    }
    shop_capacity = {
        1: 120,
        2: 180,
        3: 220
    }
    
    # Validate data lengths
    assert len(potential_sales) == len(shop_ids) == len(device_ids), "Data length mismatch"
    
    # 2. VARIABLES
    x = {(i, j): mdl.integer_var(name=f"x_{i}_{j}", lb=0) for i in shop_ids for j in device_ids}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(potential_sales[i, j] * x[i, j] for i, j in potential_sales)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Shop capacity constraints
    for i in shop_ids:
        mdl.add_constraint(mdl.sum(x[i, j] for j in device_ids if (i, j) in potential_sales) <= shop_capacity[i], ctname=f"capacity_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i, j in potential_sales:
            value = solution.get_value(x[i, j])
            if value > 1e-6:
                print(f"x[{i},{j}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

# Execute the optimization
device_allocation_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 434000.0
**Execution Time**: 2.33 seconds
**Reliability**: high
**Retry Attempt**: 3
**Analysis**: DOCplex also found an optimal solution matching Gurobipy's results. The execution time was longer, but the solution is consistent and reliable.

## 7. Pyomo Implementation

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

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

def device_allocation_optimization():
    """Optimize device allocation to maximize sales potential across shops."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Define the data based on the provided database schema and current stored values
    shops = [1, 2, 3]
    devices = [101, 102, 103]
    
    potential_sales_data = {
        (1, 101): 600.0,
        (2, 102): 850.0,
        (3, 103): 950.0
    }
    
    shop_capacity_data = {
        1: 120,
        2: 180,
        3: 220
    }
    
    # 3. SETS
    model.Shops = pyo.Set(initialize=shops)
    model.Devices = pyo.Set(initialize=devices)
    
    # 4. PARAMETERS
    model.potential_sales = pyo.Param(model.Shops * model.Devices, initialize=potential_sales_data, within=pyo.NonNegativeReals)
    model.capacity = pyo.Param(model.Shops, initialize=shop_capacity_data, within=pyo.NonNegativeIntegers)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.Shops, model.Devices, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.potential_sales[i, j] * model.x[i, j] for i in model.Shops for j in model.Devices)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    def capacity_constraint_rule(model, i):
        return sum(model.x[i, j] for j in model.Devices) <= model.capacity[i]
    model.capacity_constraint = pyo.Constraint(model.Shops, rule=capacity_constraint_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("\nDevice allocation:")
        for i in model.Shops:
            for j in model.Devices:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0:
                    print(f"Shop {i}, Device {j}: {x_val} units")
        
    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
device_allocation_optimization()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpd7pw90ic.py", line 84, in <module>
    device_allocation_optimization()
  File "/tmp/tmpd7pw90ic.py", line 43, in device_allocation_optimization
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 571, in __setattr__
    self.add_component(name, val)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 1101, in add_component
    val.construct(data)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/objective.py", line 336, in construct
    ans = self._setitem_when_not_present(index, rule(block, index))
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/initializer.py", line 485, in __call__
    return self._fcn(parent)
  File "/tmp/tmpd7pw90ic.py", line 42, in obj_rule
    return sum(model.potential_sales[i, j] * model.x[i, j] for i in model.Shops for j in model.Devices)
  File "/tmp/tmpd7pw90ic.py", line 42, in <genexpr>
    return sum(model.potential_sales[i, j] * model.x[i, j] for i in model.Shops for j in model.Devices)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 1012, in __getitem__
    return super().__getitem__(args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/indexed_component.py", line 662, in __getitem__
    return self._getitem_when_not_present(index)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/param.py", line 627, in _getitem_when_not_present
    raise ValueError(
ValueError: Error retrieving immutable Param value (potential_sales[(1, 102)]):
	The Param value is undefined and no default value is specified.

**Analysis**: Pyomo encountered an error due to an undefined parameter value, which prevented it from finding a solution. This suggests a potential issue with data input or model setup.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 434000.00 | 0.18s | N/A | 3 |
| Docplex | OPTIMAL | 434000.00 | 2.33s | N/A | 3 |
| Pyomo | ERROR | N/A | 0.83s | N/A | 3 |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex
**Majority Vote Optimal Value**: 434000.0
**Solver Retry Summary**: gurobipy: 3 attempts, docplex: 3 attempts, pyomo: 3 attempts

### Final Recommendation
**Recommended Optimal Value**: 434000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided a reliable and efficient solution with the shortest execution time and no errors.

### Optimal Decision Variables
- **x_1,101** = 120.000
  - *Business Meaning*: Resource allocation for x_1,101
- **x_2,102** = 180.000
  - *Business Meaning*: Resource allocation for x_2,102
- **x_3,103** = 220.000
  - *Business Meaning*: Resource allocation for x_3,103

### Business Interpretation
**Overall Strategy**: The optimal allocation of devices maximizes potential sales while respecting shop capacities.
**Objective Value Meaning**: The optimal objective value of 434000.0 represents the maximum potential sales achievable with the given device allocations.
**Resource Allocation Summary**: All available shop capacities are fully utilized to achieve maximum sales potential.
**Implementation Recommendations**: Ensure accurate data input for all parameters, particularly when using Pyomo. Implement the recommended device allocations to maximize sales.