## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{ij} \): Number of units of product \( i \) assigned to store \( j \). These are integer variables representing the decision of how many units of each product to allocate to each store.

#### Objective Function
Maximize the total sales value across all stores and products:
\[ \text{Maximize } \sum_{i,j} \text{sales\_value}_i \times x_{ij} \]

Where:
- \(\text{sales\_value}_i\) is the sales value of product \( i \) from the `ProductSalesValue` table.

#### Constraints
1. **Product Availability Constraint**: The total number of units assigned to each product across all stores must not exceed the available units for that product.
   \[
   \sum_{j} x_{ij} \leq \text{availability}_i, \quad \forall i
   \]
   - \(\text{availability}_i\) is the availability of product \( i \) from the `ProductAvailability` table.

2. **Store Capacity Constraint**: The total number of units assigned to all products in a store must not exceed the store's capacity.
   \[
   \sum_{i} x_{ij} \leq \text{store\_capacity}_j, \quad \forall j
   \]
   - \(\text{store\_capacity}_j\) is the capacity of store \( j \) from the `Store` table.

3. **Non-negativity Constraint**: The number of units assigned must be non-negative.
   \[
   x_{ij} \geq 0, \quad \forall i, j
   \]

Data Source Verification:
- \(\text{sales\_value}_i\) is sourced from `ProductSalesValue.sales_value`.
- \(\text{availability}_i\) is sourced from `ProductAvailability.availability`.
- \(\text{store\_capacity}_j\) is sourced from `Store.store_capacity`.

This formulation provides a complete linear model that can be solved using linear programming techniques to determine the optimal distribution of products to maximize sales while respecting product availability and store capacity constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_store_product_distribution():
    """Optimize product distribution to maximize sales potential across stores."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("store_product")
    
    # Data from the database schema
    product_sales_value = {1: 12.5, 2: 25.0, 3: 18.0}
    product_availability = {1: 60, 2: 80, 3: 70}
    store_capacity = {1: 120, 2: 180, 3: 150}
    
    products = list(product_sales_value.keys())
    stores = list(store_capacity.keys())
    
    # CRITICAL: Validate array lengths before loops
    assert len(product_sales_value) == len(product_availability) == len(products), "Product array length mismatch"
    assert len(store_capacity) == len(stores), "Store array length mismatch"
    
    # 2. VARIABLES
    # Decision variables: units assigned to each product-store pair
    units_assigned = model.addVars(products, stores, vtype=GRB.INTEGER, name="units_assigned", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total sales value
    model.setObjective(
        gp.quicksum(product_sales_value[i] * units_assigned[i, j] for i in products for j in stores),
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Product Availability Constraint
    model.addConstrs(
        (gp.quicksum(units_assigned[i, j] for j in stores) <= product_availability[i] for i in products),
        name="product_availability"
    )
    
    # Store Capacity Constraint
    model.addConstrs(
        (gp.quicksum(units_assigned[i, j] for i in products) <= store_capacity[j] for j in stores),
        name="store_capacity"
    )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in products:
            for j in stores:
                if units_assigned[i, j].x > 1e-6:
                    print(f"units_assigned[{i},{j}] = {units_assigned[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
optimize_store_product_distribution()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 4010.0
**Execution Time**: 0.16 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the shortest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_store_product_distribution():
    """Optimize product distribution to maximize sales potential across stores."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="store_product")
    
    # Data from the database schema
    product_sales_value = {1: 12.5, 2: 25.0, 3: 18.0}
    product_availability = {1: 60, 2: 80, 3: 70}
    store_capacity = {1: 120, 2: 180, 3: 150}
    
    # Extract keys for safe indexing
    products = list(product_sales_value.keys())
    stores = list(store_capacity.keys())
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(product_sales_value) == len(product_availability) == len(products), "Product array length mismatch"
    assert len(store_capacity) == len(stores), "Store array length mismatch"
    
    # 2. VARIABLES
    # Decision variables: units assigned to each product in each store
    units_assigned = {(i, j): mdl.integer_var(name=f"x_{i}_{j}", lb=0) for i in products for j in stores}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total sales value
    objective = mdl.sum(product_sales_value[i] * units_assigned[i, j] for i in products for j in stores)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Product Availability Constraint
    for i in products:
        mdl.add_constraint(mdl.sum(units_assigned[i, j] for j in stores) <= product_availability[i], ctname=f"availability_{i}")
    
    # Store Capacity Constraint
    for j in stores:
        mdl.add_constraint(mdl.sum(units_assigned[i, j] for i in products) <= store_capacity[j], ctname=f"capacity_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in products:
            for j in stores:
                value = solution.get_value(units_assigned[i, j])
                if value > 1e-6:
                    print(f"Units assigned to product {i} in store {j}: {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_store_product_distribution()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 4010.0
**Execution Time**: 1.41 seconds
**Reliability**: high
**Analysis**: DOCplex also found an 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 store_product_optimization():
    """Optimize product distribution across stores to maximize sales potential."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Data from the database schema
    product_ids = [1, 2, 3]
    store_ids = [1, 2, 3]
    
    sales_values = {1: 12.5, 2: 25.0, 3: 18.0}
    availabilities = {1: 60, 2: 80, 3: 70}
    store_capacities = {1: 120, 2: 180, 3: 150}
    
    # 3. SETS
    model.I = pyo.Set(initialize=product_ids)
    model.J = pyo.Set(initialize=store_ids)
    
    # 4. PARAMETERS
    model.sales_value = pyo.Param(model.I, initialize=sales_values)
    model.availability = pyo.Param(model.I, initialize=availabilities)
    model.store_capacity = pyo.Param(model.J, initialize=store_capacities)
    
    # 5. VARIABLES
    model.units_assigned = pyo.Var(model.I, model.J, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.sales_value[i] * model.units_assigned[i, j] for i in model.I for j in model.J)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Product Availability Constraint
    def product_availability_rule(model, i):
        return sum(model.units_assigned[i, j] for j in model.J) <= model.availability[i]
    model.product_availability_constraint = pyo.Constraint(model.I, rule=product_availability_rule)
    
    # Store Capacity Constraint
    def store_capacity_rule(model, j):
        return sum(model.units_assigned[i, j] for i in model.I) <= model.store_capacity[j]
    model.store_capacity_constraint = pyo.Constraint(model.J, rule=store_capacity_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)}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.I:
            for j in model.J:
                units_val = pyo.value(model.units_assigned[i, j])
                if units_val > 1e-6:  # Only print non-zero values
                    print(f"units_assigned[{i}, {j}] = {units_val:.0f}")
        
    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
store_product_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 4010.0
**Execution Time**: 1.76 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with the longest execution time, but 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 | 4010.00 | 0.16s | N/A | N/A |
| Docplex | OPTIMAL | 4010.00 | 1.41s | N/A | N/A |
| Pyomo | OPTIMAL | 4010.00 | 1.76s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 4010.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its faster execution time while maintaining the same optimal value as other solvers, indicating efficiency and reliability.

### Business Interpretation
**Overall Strategy**: The optimal allocation of products to stores maximizes sales value, ensuring efficient use of available resources and store capacities.
**Objective Value Meaning**: The optimal objective value of 4010.0 represents the maximum achievable sales value given the constraints of product availability and store capacity.
**Resource Allocation Summary**: Resources should be allocated to maximize sales value while respecting product availability and store capacity constraints.
**Implementation Recommendations**: Implement the solution by adjusting inventory distribution according to the optimal allocation determined by the solver, ensuring that store capacities and product availabilities are not exceeded.