# Complete Optimization Problem and Solution: shop_membership

## 1. Problem Context and Goals

### Context  
A retail chain is focused on optimizing the allocation of membership benefits across its branches to maximize customer satisfaction. Customer satisfaction is measured by the total pounds spent by members at each branch. The business must make decisions on how to allocate resources to each branch to achieve this goal while adhering to two key operational limitations:  
1. **Total Membership Benefits Budget**: The retail chain has a fixed total budget allocated for membership benefits across all branches. This budget cannot be exceeded when distributing benefits.  
2. **Branch Capacity Limits**: Each branch has a maximum number of members it can handle, which is determined by its operational capacity. The total pounds spent by members at a branch must not exceed this capacity.  

The business configuration includes:  
- A total budget of 500,000 units allocated for membership benefits across all branches.  
- A formula to calculate the total capacity across all branches, which is the sum of the maximum number of members each branch can handle.  

The decision variables in this problem are the total pounds spent by members at each branch, which are continuous values. The goal is to maximize the sum of these values across all branches while respecting the budget and capacity constraints.  

### Goals  
The primary goal of this optimization problem is to maximize the total pounds spent by members across all branches. This metric directly reflects customer satisfaction and is aligned with the retail chain’s objective of enhancing member engagement. Success is measured by achieving the highest possible sum of total pounds spent while staying within the allocated budget and respecting each branch’s capacity limits.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Total Membership Benefits Budget**: The sum of the membership amounts allocated to all branches must not exceed the total budget of 500,000 units. This ensures that the retail chain operates within its financial limits.  
2. **Branch Capacity Limits**: The sum of the total pounds spent by members at all branches must not exceed the total capacity across all branches. This ensures that no branch is overloaded beyond its operational capabilities.  

These constraints are designed to ensure that the optimization problem remains linear, avoiding any scenarios that would require nonlinear relationships 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 branch capacity, modifying the purchase table to better map constraints, and adding configuration logic for budget and capacity limits.

CREATE TABLE purchase (
  Total_pounds FLOAT,
  branch_id INTEGER
);

CREATE TABLE branch (
  membership_amount FLOAT
);

CREATE TABLE branch_capacity (
  max_members INTEGER
);
```

### Data Dictionary  
- **purchase Table**:  
  - **Total_pounds**: Represents the total pounds spent by members at a specific branch. This is the key decision variable and objective coefficient in the optimization problem.  
  - **branch_id**: Identifies the branch where the purchase was made. This is used to map constraints to specific branches.  

- **branch Table**:  
  - **membership_amount**: Represents the maximum membership amount allocated to a branch. This is used as a constraint bound to ensure the total budget is not exceeded.  

- **branch_capacity Table**:  
  - **max_members**: Represents the maximum number of members a branch can handle. This is used as a constraint bound to ensure branch capacities are respected.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic retail chain operations, considering average customer spending, branch capacities, and budget allocations.

-- Realistic data for purchase
INSERT INTO purchase (Total_pounds, branch_id) VALUES (150.75, 1);
INSERT INTO purchase (Total_pounds, branch_id) VALUES (200.5, 2);
INSERT INTO purchase (Total_pounds, branch_id) VALUES (300.0, 3);

-- Realistic data for branch
INSERT INTO branch (membership_amount) VALUES (5000.0);
INSERT INTO branch (membership_amount) VALUES (7500.0);
INSERT INTO branch (membership_amount) VALUES (10000.0);

-- Realistic data for branch_capacity
INSERT INTO branch_capacity (max_members) VALUES (100);
INSERT INTO branch_capacity (max_members) VALUES (150);
INSERT INTO branch_capacity (max_members) VALUES (200);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_i \) represent the total pounds spent by members at branch \( i \), where \( i = 1, 2, 3 \).  
  \( x_i \) is a continuous variable.

#### Objective Function
Maximize the total pounds spent by members across all branches:  
\[
\text{Maximize } Z = x_1 + x_2 + x_3
\]

#### Constraints
1. **Total Membership Benefits Budget**:  
   The sum of the membership amounts allocated to all branches must not exceed the total budget of 500,000 units.  
   \[
   5000.0 + 7500.0 + 10000.0 \leq 500000
   \]  
   This constraint is already satisfied by the given data and does not involve decision variables.  

2. **Branch Capacity Limits**:  
   The total pounds spent by members at each branch must not exceed the branch's capacity.  
   \[
   x_1 \leq 100, \quad x_2 \leq 150, \quad x_3 \leq 200
   \]  

3. **Non-Negativity Constraints**:  
   The total pounds spent by members at each branch must be non-negative.  
   \[
   x_1 \geq 0, \quad x_2 \geq 0, \quad x_3 \geq 0
   \]  

#### Data Source Verification
- **Objective Function Coefficients**:  
  The coefficients for \( x_1, x_2, x_3 \) are all 1, as the goal is to maximize the sum of total pounds spent.  

- **Branch Capacity Constraints**:  
  The right-hand side values (100, 150, 200) are from the `branch_capacity.max_members` table.  

- **Total Membership Benefits Budget**:  
  The membership amounts (5000.0, 7500.0, 10000.0) are from the `branch.membership_amount` table.  

This is a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Shop Membership Optimization Problem
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_shop_membership():
    """Optimize the allocation of membership benefits across branches to maximize customer satisfaction."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("shop_membership")
    
    # Data from the problem
    branches = [1, 2, 3]
    membership_amounts = {1: 5000.0, 2: 7500.0, 3: 10000.0}
    branch_capacities = {1: 100, 2: 150, 3: 200}
    total_budget = 500000
    
    # CRITICAL: Validate array lengths before loops
    assert len(branches) == len(membership_amounts) == len(branch_capacities), "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variables: total pounds spent by members at each branch
    x = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{i}", lb=0) for i in branches}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total pounds spent by members across all branches
    model.setObjective(gp.quicksum(x[i] for i in branches), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Total Membership Benefits Budget
    # The sum of the membership amounts allocated to all branches must not exceed the total budget
    model.addConstr(gp.quicksum(membership_amounts[i] for i in branches) <= total_budget, name="total_budget")
    
    # Constraint 2: Branch Capacity Limits
    # The total pounds spent by members at each branch must not exceed the branch's capacity
    for i in branches:
        model.addConstr(x[i] <= branch_capacities[i], name=f"capacity_{i}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in branches:
            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

# Execute the optimization
if __name__ == "__main__":
    optimize_shop_membership()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 450.0
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy achieved the optimal solution efficiently with the fastest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCPLEX implementation for shop_membership optimization problem
"""

from docplex.mp.model import Model

def optimize_shop_membership():
    """Optimize the allocation of membership benefits across branches"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="shop_membership")
    
    # Data from the problem
    total_budget = 500000
    branch_ids = [1, 2, 3]
    membership_amounts = [5000.0, 7500.0, 10000.0]
    branch_capacities = [100, 150, 200]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(branch_ids) == len(membership_amounts) == len(branch_capacities), "Array length mismatch"
    safe_range = range(min(len(branch_ids), len(membership_amounts), len(branch_capacities)))  # Safe indexing
    
    # 2. VARIABLES
    # Decision variables: total pounds spent by members at each branch
    x = {i: mdl.continuous_var(name=f"x_{branch_ids[i]}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total pounds spent by members across all branches
    objective = mdl.sum(x[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Total Membership Benefits Budget
    # The sum of the membership amounts allocated to all branches must not exceed the total budget
    total_membership = mdl.sum(membership_amounts[i] for i in safe_range)
    mdl.add_constraint(total_membership <= total_budget, ctname="total_budget")
    
    # Constraint 2: Branch Capacity Limits
    # The total pounds spent by members at each branch must not exceed the branch's capacity
    for i in safe_range:
        mdl.add_constraint(x[i] <= branch_capacities[i], ctname=f"capacity_{branch_ids[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])
            print(f"x[{branch_ids[i]}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    optimize_shop_membership()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 450.0
**Execution Time**: 1.23 seconds
**Reliability**: high
**Analysis**: DOCplex also achieved the optimal solution but with a longer execution time compared to Gurobipy.

## 7. Pyomo Implementation

```python
#!/usr/bin/env python3
"""
Pyomo 6.9.2 Implementation for Shop Membership Optimization Problem
"""

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

def shop_membership_optimization():
    """Optimize the allocation of membership benefits across branches to maximize customer satisfaction."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Branch data
    branches = [1, 2, 3]
    membership_amounts = [5000.0, 7500.0, 10000.0]
    branch_capacities = [100, 150, 200]
    
    # Validate array lengths before indexing
    assert len(branches) == len(membership_amounts) == len(branch_capacities), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.I = pyo.Set(initialize=branches)  # Set of branches
    
    # 4. PARAMETERS (data containers)
    model.membership_amount = pyo.Param(model.I, initialize={i: membership_amounts[idx] for idx, i in enumerate(branches)})
    model.branch_capacity = pyo.Param(model.I, initialize={i: branch_capacities[idx] for idx, i in enumerate(branches)})
    
    # 5. VARIABLES
    # Continuous variables for total pounds spent by members at each branch
    model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    # Maximize the total pounds spent by members across all branches
    def obj_rule(model):
        return sum(model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Branch Capacity Limits: The total pounds spent by members at each branch must not exceed the branch's capacity
    def capacity_rule(model, i):
        return model.x[i] <= model.branch_capacity[i]
    model.capacity_constraint = pyo.Constraint(model.I, rule=capacity_rule)
    
    # Non-Negativity Constraints: The total pounds spent by members at each branch must be non-negative
    def non_negativity_rule(model, i):
        return model.x[i] >= 0
    model.non_negativity_constraint = pyo.Constraint(model.I, rule=non_negativity_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    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:
            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

# Execute the optimization
if __name__ == "__main__":
    shop_membership_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 450.0
**Execution Time**: 1.00 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution but with an execution time between Gurobipy and DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 450.00 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 450.00 | 1.23s | N/A | N/A |
| Pyomo | OPTIMAL | 450.00 | 1.00s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 450.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its fastest execution time while achieving the same optimal solution as the other solvers.

### Optimal Decision Variables
- **x_1** = 100.000
  - *Business Meaning*: Total pounds spent by members at branch 1, optimally at its capacity of 100.
- **x_2** = 150.000
  - *Business Meaning*: Total pounds spent by members at branch 2, optimally at its capacity of 150.
- **x_3** = 200.000
  - *Business Meaning*: Total pounds spent by members at branch 3, optimally at its capacity of 200.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests maximizing member spending by fully utilizing the capacity of each branch.
**Objective Value Meaning**: The total maximum pounds spent by members across all branches is 450, indicating full utilization of branch capacities.
**Resource Allocation Summary**: Allocate resources to ensure each branch operates at its maximum capacity to maximize member spending.
**Implementation Recommendations**: Ensure branch capacities are maintained and monitor member spending to sustain optimal performance.