# Complete Optimization Problem and Solution: shop_membership

## 1. Problem Context and Goals

### Context  
A retail chain is focused on optimizing the allocation of its membership marketing budget across various branches. The primary decision involves determining the budget allocation for each branch, represented by the continuous variable BudgetAllocation.amount[branch_id]. The goal is to maximize the total purchases made by members, which is directly influenced by the historical purchase data of each branch. The operational parameters include a total marketing budget that serves as a constraint, ensuring that the sum of all branch allocations does not exceed this budget. Additionally, each branch must receive a minimum budget allocation to ensure effective marketing efforts. This setup naturally aligns with a linear optimization formulation, where the objective is to maximize the total purchases by strategically distributing the available budget. The business configuration includes a total marketing budget of 45,000 and a minimum budget allocation of 10,000 for each branch, ensuring that all branches have sufficient resources to drive member purchases.

### Goals  
The primary goal of this optimization problem is to maximize the total purchases made by members across all branches. This is achieved by optimally allocating the fixed marketing budget based on historical purchase data and membership levels. The success of this optimization is measured by the increase in total purchases, which is directly linked to the budget allocation for each branch. The linear optimization goal is to maximize the total purchases by effectively utilizing the available budget, ensuring that each branch receives at least the minimum required allocation.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the feasibility and effectiveness of the budget allocation:

- The total budget allocated across all branches must not exceed the total marketing budget of 45,000. This constraint ensures that the allocation remains within the available financial resources.
- Each branch must receive a minimum budget allocation of 10,000. This constraint guarantees that every branch has enough funding to implement meaningful marketing strategies and drive member purchases.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and solvable using standard linear programming techniques.

## 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 BudgetAllocation (
  branch_id INTEGER,
  amount FLOAT
);

CREATE TABLE Purchase (
  branch_id INTEGER,
  Total_pounds FLOAT
);
```

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

- **BudgetAllocation Table**: This table stores the budget allocation for each branch. It plays a critical role in the optimization process as it represents the decision variables.
  - **branch_id**: An integer identifier for each branch, used as an index for budget allocation.
  - **amount**: A float representing the allocated budget amount for the branch, serving as the decision variable for budget allocation.

- **Purchase Table**: This table contains historical purchase data, which is used to determine the effectiveness of budget allocations.
  - **branch_id**: An integer identifier for each branch, used as an index for purchase coefficients.
  - **Total_pounds**: A float representing the total historical purchases in pounds for each branch, serving as the coefficient for budget allocation in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical marketing budgets and purchase data for retail chains, ensuring a balance between budget allocation and historical purchase performance.

-- Realistic data for BudgetAllocation
INSERT INTO BudgetAllocation (branch_id, amount) VALUES (1, 12000);
INSERT INTO BudgetAllocation (branch_id, amount) VALUES (2, 18000);
INSERT INTO BudgetAllocation (branch_id, amount) VALUES (3, 15000);

-- Realistic data for Purchase
INSERT INTO Purchase (branch_id, Total_pounds) VALUES (1, 60000);
INSERT INTO Purchase (branch_id, Total_pounds) VALUES (2, 90000);
INSERT INTO Purchase (branch_id, Total_pounds) VALUES (3, 80000);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_i \) be the budget allocation for branch \( i \).
- \( x_i \) is a continuous variable representing the amount of budget allocated to branch \( i \).

#### Objective Function
Maximize the total purchases made by members across all branches. The objective function is:

\[
\text{Maximize } Z = 60000x_1 + 90000x_2 + 80000x_3
\]

where:
- 60000, 90000, and 80000 are the historical purchase data (Total_pounds) for branches 1, 2, and 3, respectively, from the `Purchase` table.

#### Constraints
1. Total budget constraint:
   \[
   x_1 + x_2 + x_3 \leq 45000
   \]
   - The total budget allocated across all branches must not exceed the total marketing budget of 45,000.

2. Minimum budget allocation for each branch:
   \[
   x_1 \geq 10000
   \]
   \[
   x_2 \geq 10000
   \]
   \[
   x_3 \geq 10000
   \]
   - Each branch must receive a minimum budget allocation of 10,000.

Data Source Verification:
- Coefficients for the objective function (60000, 90000, 80000) are from `Purchase.Total_pounds`.
- The total budget constraint (45000) is from the business configuration parameter.
- The minimum budget allocation (10000) is from the business configuration parameter.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_shop_membership():
    """Optimize budget allocation for maximizing member purchases across branches"""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("shop_membership")
    
    # Data from the problem context
    branches = [1, 2, 3]
    total_budget = 45000
    min_budget = 10000
    purchase_coefficients = {1: 60000, 2: 90000, 3: 80000}
    
    # CRITICAL: Validate array lengths before loops
    assert len(branches) == len(purchase_coefficients), "Array length mismatch"
    
    # 2. VARIABLES
    # Variable dictionaries for budget allocation
    budget_allocation = {branch: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{branch}", lb=0) 
                         for branch in branches}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total purchases
    model.setObjective(gp.quicksum(purchase_coefficients[branch] * budget_allocation[branch] 
                                   for branch in branches), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total budget constraint
    model.addConstr(gp.quicksum(budget_allocation[branch] for branch in branches) <= total_budget, 
                    name="total_budget")
    
    # Minimum budget allocation for each branch
    for branch in branches:
        model.addConstr(budget_allocation[branch] >= min_budget, name=f"min_budget_{branch}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for branch in branches:
            if budget_allocation[branch].x > 1e-6:
                print(f"Budget allocation for branch {branch}: {budget_allocation[branch].x:.2f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_shop_membership()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3650000000.0
**Execution Time**: 0.42 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with a reliable status and consistent results.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_shop_membership():
    """Optimize budget allocation for maximizing member purchases across branches."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="shop_membership")
    
    # Historical purchase data (Total_pounds) for each branch
    purchase_data = [60000, 90000, 80000]
    
    # Number of branches
    n_branches = len(purchase_data)
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(purchase_data) == n_branches, "Array length mismatch"
    safe_range = range(n_branches)  # Safe indexing
    
    # 2. VARIABLES
    # Budget allocation for each branch
    budget_allocation = {i: mdl.continuous_var(name=f"budget_{i}", lb=10000) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total purchases
    objective = mdl.sum(purchase_data[i] * budget_allocation[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Total budget constraint
    total_budget = mdl.sum(budget_allocation[i] for i in safe_range)
    mdl.add_constraint(total_budget <= 45000, ctname="total_budget_constraint")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(budget_allocation[i])
            print(f"Budget allocation for branch {i+1}: {value:.2f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_shop_membership()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3650000000.0
**Execution Time**: 6.68 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def optimize_shop_membership():
    """Optimize budget allocation for maximizing member purchases"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    branches = [1, 2, 3]
    total_purchases = {1: 60000, 2: 90000, 3: 80000}
    total_budget = 45000
    min_budget = 10000
    
    # CRITICAL: Validate data lengths before indexing
    assert len(branches) == len(total_purchases), "Data length mismatch"
    
    # 3. SETS
    model.B = pyo.Set(initialize=branches)
    
    # 4. PARAMETERS
    model.purchase = pyo.Param(model.B, initialize=total_purchases)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.B, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.purchase[b] * model.x[b] for b in model.B)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Total budget constraint
    def total_budget_rule(model):
        return sum(model.x[b] for b in model.B) <= total_budget
    model.total_budget_constraint = pyo.Constraint(rule=total_budget_rule)
    
    # Minimum budget allocation for each branch
    def min_budget_rule(model, b):
        return model.x[b] >= min_budget
    model.min_budget_constraint = pyo.Constraint(model.B, rule=min_budget_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("\nBudget Allocation:")
        for b in model.B:
            x_val = pyo.value(model.x[b])
            print(f"Branch {b}: {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
optimize_shop_membership()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3650000000.0
**Execution Time**: 2.00 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, consistent with other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 3650000000.00 | 0.42s | N/A | N/A |
| Docplex | OPTIMAL | 3650000000.00 | 6.68s | N/A | N/A |
| Pyomo | OPTIMAL | 3650000000.00 | 2.00s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 3650000000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided the optimal solution with the fastest execution time, making it the most efficient choice.

### Optimal Decision Variables
- **x_1** = 10000.000
  - *Business Meaning*: Budget allocated to branch 2, which receives the highest allocation due to its potential for maximizing purchases.
- **x_2** = 25000.000
  - *Business Meaning*: Budget allocated to branch 3, also set at the minimum required level.
- **x_3** = 10000.000
  - *Business Meaning*: Resource allocation for x_3

### Business Interpretation
**Overall Strategy**: The optimal allocation maximizes purchases by focusing more budget on branch 2, which has the highest historical purchase data.
**Objective Value Meaning**: The optimal objective value represents the maximum potential purchases across all branches given the budget constraints.
**Resource Allocation Summary**: Allocate 10,000 to branch 1, 25,000 to branch 2, and 10,000 to branch 3 to maximize purchases.
**Implementation Recommendations**: Implement the budget allocation as per the recommended decision variables to achieve the optimal purchase levels. Monitor branch performance to ensure alignment with historical data trends.