# Complete Optimization Problem and Solution: manufacturer

## 1. Problem Context and Goals

### Context  
The furniture manufacturer is focused on maximizing its profit by strategically determining the optimal number of furniture items to produce and sell. This decision-making process involves evaluating the market rates and production costs associated with each furniture item. The manufacturer operates under specific capacity constraints, which include the total production capacity determined by the number of factories and the total sales capacity dictated by the number of shops. These constraints are crucial in ensuring that the production and sales activities remain within feasible limits. The business decisions revolve around selecting the quantity of each furniture item to produce and sell, where each quantity is an integer value. The operational parameters are aligned with the objective of maximizing total profit, calculated as the difference between market rates and production costs, multiplied by the quantity of items produced and sold. The business configuration includes scalar parameters such as total production capacity and total sales capacity, which serve as bounds in the optimization model. The focus is on precise operational decision-making that leads to a linear optimization formulation, avoiding any scenarios that would require nonlinear relationships.

### Goals  
The primary goal of the optimization is to maximize the total profit for the furniture manufacturer. This involves determining the optimal quantities of furniture items to produce and sell, with the aim of maximizing the difference between the market rates and production costs for each item. The success of this optimization is measured by the total profit achieved, which is calculated by summing the product of the profit margin (market rate minus production cost) and the quantity of each item. The optimization goal is clearly defined in linear terms, ensuring that the decision-making process is straightforward and aligned with the expected linear mathematical formulation.

## 2. Constraints    

The optimization process is subject to two key constraints that ensure the feasibility of the production and sales activities. The first constraint is the total production capacity, which limits the sum of the quantities of all furniture items to not exceed the maximum output capacity of the manufacturing facilities. This capacity is determined by the number of factories available. The second constraint is the total sales capacity, which restricts the sum of the quantities of all furniture items to not surpass the maximum sales capacity across all retail outlets. This capacity is based on the number of shops. Both constraints are expressed in linear terms, directly aligning with the business requirements and ensuring that the optimization remains within realistic operational limits.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables, updating existing tables to include missing mappings, and moving scalar parameters to configuration logic. This addresses the OR expert's mapping gaps and ensures all optimization requirements are met.

CREATE TABLE furniture (
  market_rate FLOAT,
  price_in_dollar FLOAT,
  quantity INTEGER
);

CREATE TABLE decision_variables (
  quantity INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of the tables and columns to their respective business purposes and optimization roles. The "furniture" table stores critical information about each furniture item, including its market rate and production cost. These values are essential for calculating the profit margin for each item. The "quantity" column in this table represents the number of units of each furniture item to produce and sell, serving as a decision variable in the optimization process. The "decision_variables" table is specifically designed to store the decision variables for optimization, with the "quantity" column indicating the number of units to produce and sell for each item. This structure ensures a clear connection to the expected linear mathematical formulation, facilitating effective decision-making.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical market rates and production costs for furniture items, ensuring a balance between profitability and capacity constraints.

-- Realistic data for furniture
INSERT INTO furniture (market_rate, price_in_dollar, quantity) VALUES (120.0, 60.0, 15);
INSERT INTO furniture (market_rate, price_in_dollar, quantity) VALUES (180.0, 90.0, 10);
INSERT INTO furniture (market_rate, price_in_dollar, quantity) VALUES (140.0, 70.0, 20);

-- Realistic data for decision_variables
INSERT INTO decision_variables (quantity) VALUES (15);
INSERT INTO decision_variables (quantity) VALUES (10);
INSERT INTO decision_variables (quantity) VALUES (20);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_1, x_2, x_3 \) be the decision variables representing the quantity of each furniture item to produce and sell. These variables are integers.

#### Objective Function
Maximize the total profit, which is the sum of the profit margins (market rate minus production cost) multiplied by the quantity of each item produced and sold.

\[
\text{Maximize } Z = (120 - 60)x_1 + (180 - 90)x_2 + (140 - 70)x_3
\]

Simplified:

\[
\text{Maximize } Z = 60x_1 + 90x_2 + 70x_3
\]

Data Source Verification:
- Coefficients for \( x_1, x_2, x_3 \) are derived from the profit margins calculated as \((\text{furniture.market_rate} - \text{furniture.price_in_dollar})\).

#### Constraints
1. **Total Production Capacity Constraint:**
   The sum of the quantities of all furniture items must not exceed the total production capacity. Assume the total production capacity is a constant \( P \).

   \[
   x_1 + x_2 + x_3 \leq P
   \]

2. **Total Sales Capacity Constraint:**
   The sum of the quantities of all furniture items must not exceed the total sales capacity. Assume the total sales capacity is a constant \( S \).

   \[
   x_1 + x_2 + x_3 \leq S
   \]

Data Source Verification:
- The coefficients for the constraints are derived from the decision variables \( \text{decision_variables.quantity} \).
- Constants \( P \) and \( S \) are assumed to be provided as part of the business configuration parameters.

This linear mathematical model is immediately solvable, given the values for \( P \) and \( S \), and adheres to the constraints and objectives outlined in the business problem description.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_furniture_production():
    """Optimize furniture production to maximize profit"""

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

    # Data: Profit margins and capacities
    profit_margins = [60, 90, 70]  # Derived from market_rate - price_in_dollar
    total_production_capacity = 40  # Example capacity
    total_sales_capacity = 35  # Example capacity

    # Number of furniture items
    n_items = len(profit_margins)

    # CRITICAL: Validate array lengths before loops
    assert len(profit_margins) == n_items, "Array length mismatch"

    # 2. VARIABLES
    # Decision variables for quantities of each furniture item
    quantities = model.addVars(n_items, vtype=GRB.INTEGER, name="quantity", lb=0)

    # 3. OBJECTIVE FUNCTION
    # Maximize total profit
    model.setObjective(gp.quicksum(profit_margins[i] * quantities[i] for i in range(n_items)), GRB.MAXIMIZE)

    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS

    # Total production capacity constraint
    model.addConstr(gp.quicksum(quantities[i] for i in range(n_items)) <= total_production_capacity, name="production_capacity")

    # Total sales capacity constraint
    model.addConstr(gp.quicksum(quantities[i] for i in range(n_items)) <= total_sales_capacity, name="sales_capacity")

    # 5. SOLVING & RESULTS
    model.optimize()

    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_items):
            if quantities[i].x > 1e-6:
                print(f"quantity[{i}] = {quantities[i].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_furniture_production()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3150.0
**Execution Time**: 0.55 seconds
**Reliability**: high
**Analysis**: Gurobipy provided an optimal solution quickly with a consistent result compared to Pyomo.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def furniture_optimization():
    """Optimize furniture production and sales for maximum profit."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="furniture_optimization")
    
    # Data: Market rates, production costs, and initial quantities
    market_rates = [120.0, 180.0, 140.0]
    production_costs = [60.0, 90.0, 70.0]
    initial_quantities = [15, 10, 20]
    
    # Constants for capacity constraints
    total_production_capacity = 50  # Example value for P
    total_sales_capacity = 50       # Example value for S
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(market_rates) == len(production_costs) == len(initial_quantities), "Array length mismatch"
    safe_range = range(min(len(market_rates), len(production_costs), len(initial_quantities)))  # Safe indexing
    
    # 2. VARIABLES
    # Integer variables for quantities of each furniture item
    quantities = {i: mdl.integer_var(name=f"x_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total profit
    profit_margins = [market_rates[i] - production_costs[i] for i in safe_range]
    objective = mdl.sum(profit_margins[i] * quantities[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Total production capacity constraint
    total_production = mdl.sum(quantities[i] for i in safe_range)
    mdl.add_constraint(total_production <= total_production_capacity, ctname="production_capacity")
    
    # Total sales capacity constraint
    total_sales = mdl.sum(quantities[i] for i in safe_range)
    mdl.add_constraint(total_sales <= total_sales_capacity, ctname="sales_capacity")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(quantities[i])
            if value > 1e-6:
                print(f"x[{i}] = {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
furniture_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 4500.0
**Execution Time**: 2.11 seconds
**Reliability**: medium
**Analysis**: DOCplex provided a different optimal value, which suggests a potential issue with the model setup or solver interpretation.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def furniture_optimization():
    """Optimize furniture production and sales for maximum profit"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Given data for the problem
    market_rates = [120.0, 180.0, 140.0]
    production_costs = [60.0, 90.0, 70.0]
    n_items = len(market_rates)
    
    # Constants for constraints
    P = 40  # Total production capacity
    S = 35  # Total sales capacity
    
    # CRITICAL: Validate array lengths before indexing
    assert len(market_rates) == len(production_costs) == n_items, "Array length mismatch"
    
    # 3. SETS
    model.I = pyo.RangeSet(1, n_items)  # 1-based indexing
    
    # 4. PARAMETERS
    profit_margins = {i+1: market_rates[i] - production_costs[i] for i in range(n_items)}
    model.profit_margin = pyo.Param(model.I, initialize=profit_margins)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.profit_margin[i] * model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    def production_capacity_rule(model):
        return sum(model.x[i] for i in model.I) <= P
    model.production_capacity_constraint = pyo.Constraint(rule=production_capacity_rule)
    
    def sales_capacity_rule(model):
        return sum(model.x[i] for i in model.I) <= S
    model.sales_capacity_constraint = pyo.Constraint(rule=sales_capacity_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)}")
        
        # 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}")
        
    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
furniture_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3150.0
**Execution Time**: 2.22 seconds
**Reliability**: high
**Analysis**: Pyomo's result is consistent with Gurobipy, indicating reliability in the solution.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 3150.00 | 0.55s | N/A | N/A |
| Docplex | OPTIMAL | 4500.00 | 2.11s | N/A | N/A |
| Pyomo | OPTIMAL | 3150.00 | 2.22s | N/A | N/A |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Consistent Solvers**: gurobipy, pyomo
**Inconsistent Solvers**: docplex
**Potential Issues**:
- DOCplex may have a different interpretation of constraints or objective function.
- Possible data input error or misconfiguration in DOCplex.
**Majority Vote Optimal Value**: 3150.0

### Final Recommendation
**Recommended Optimal Value**: 3150.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy/pyomo
**Reasoning**: Both Gurobipy and Pyomo provided consistent results, indicating a reliable solution. DOCplex's deviation suggests a potential issue.

### Business Interpretation
**Overall Strategy**: The optimal production and sales strategy should aim for a profit of $3150, as supported by two consistent solvers.
**Objective Value Meaning**: The optimal objective value of $3150 represents the maximum achievable profit given the constraints.
**Resource Allocation Summary**: Allocate production and sales resources to achieve a total profit of $3150, focusing on the quantities determined by the consistent solvers.
**Implementation Recommendations**: Verify the model setup in DOCplex, ensure data consistency across solvers, and proceed with the production plan based on Gurobipy and Pyomo results.