# Complete Optimization Problem and Solution: manufacturer

## 1. Problem Context and Goals

### Context  
A furniture manufacturer is tasked with determining the optimal number of units to produce for each furniture type to maximize profit. The decision involves balancing production costs, market rates, and resource limitations. The manufacturer operates under two key constraints: the total production capacity across all factories is limited to 1,000 units, and the total budget available for production is capped at $50,000.  

The profit for each furniture type is calculated as the difference between its market rate and its production cost. The manufacturer aims to allocate production quantities in a way that maximizes total profit while staying within the factory capacity and budget limits. This problem is formulated as a linear optimization model, ensuring that all relationships and constraints are linear and directly tied to operational parameters.  

### Goals  
The primary goal is to maximize the total profit from furniture production. This is achieved by optimizing the number of units produced for each furniture type, considering the difference between the market rate and the production cost for each item. Success is measured by the total profit generated, which is directly influenced by the production quantities and the operational parameters of market rates, production costs, and resource constraints.  

## 2. Constraints  

The manufacturer must adhere to the following constraints:  
1. **Total Production Capacity**: The sum of units produced across all furniture types must not exceed the total factory capacity of 1,000 units. This ensures that production does not surpass the manufacturer's operational capabilities.  
2. **Budget Limit**: The total cost of production, calculated as the sum of the production cost multiplied by the number of units produced for each furniture type, must not exceed the available budget of $50,000. This ensures that production remains financially feasible.  

These constraints are linear and directly tied to the manufacturer's operational limitations, ensuring a realistic and feasible optimization problem.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for objective coefficients (Market_Rate and Price_in_Dollar) and updating business configuration logic to include scalar parameters and formulas for missing optimization requirements.

CREATE TABLE production_plan (
  Furniture_ID INTEGER,
  Quantity_Produced INTEGER
);

CREATE TABLE furniture_market_rates (
  Furniture_ID INTEGER,
  Market_Rate FLOAT
);

CREATE TABLE furniture_production_costs (
  Furniture_ID INTEGER,
  Price_in_Dollar FLOAT
);
```

### Data Dictionary  
- **production_plan**:  
  - **Furniture_ID**: Unique identifier for each furniture type.  
  - **Quantity_Produced**: Number of units to produce for each furniture type. This is the decision variable in the optimization model.  

- **furniture_market_rates**:  
  - **Furniture_ID**: Unique identifier for each furniture type.  
  - **Market_Rate**: Market rate for each furniture type. This is used as an objective coefficient in the optimization model.  

- **furniture_production_costs**:  
  - **Furniture_ID**: Unique identifier for each furniture type.  
  - **Price_in_Dollar**: Production cost for each furniture type. This is used as an objective coefficient in the optimization model.  


### Retrieved Values

**Query 1: This is essential for calculating the profit per unit for each furniture type, which is the objective function coefficient.**

```sql
SELECT fmr.Furniture_ID, fmr.Market_Rate, fpc.Price_in_Dollar FROM furniture_market_rates fmr JOIN furniture_production_costs fpc ON fmr.Furniture_ID = fpc.Furniture_ID;
```

**Results (CSV format):**
```csv
Furniture_ID,Market_Rate,Price_in_Dollar
1,120.0,60.0
2,180.0,90.0
3,250.0,130.0
```

**Query 2: This is the decision variable in the optimization model, representing the number of units to produce for each furniture type.**

```sql
SELECT Furniture_ID, Quantity_Produced FROM production_plan;
```

**Results (CSV format):**
```csv
Furniture_ID,Quantity_Produced
1,0
2,0
3,0
```

**Query 3: This is needed to ensure the budget constraint is not exceeded.**

```sql
SELECT SUM(pp.Quantity_Produced * fpc.Price_in_Dollar) AS Total_Production_Cost FROM production_plan pp JOIN furniture_production_costs fpc ON pp.Furniture_ID = fpc.Furniture_ID;
```

**Results (CSV format):**
```csv
Total_Production_Cost
0.0
```

**Query 4: This is needed to ensure the production capacity constraint is not exceeded.**

```sql
SELECT SUM(Quantity_Produced) AS Total_Units_Produced FROM production_plan;
```

**Results (CSV format):**
```csv
Total_Units_Produced
0
```

**Query 5: This is crucial for the objective function, as it represents the profit generated per unit for each furniture type.**

```sql
SELECT fmr.Furniture_ID, (fmr.Market_Rate - fpc.Price_in_Dollar) AS Profit_Per_Unit FROM furniture_market_rates fmr JOIN furniture_production_costs fpc ON fmr.Furniture_ID = fpc.Furniture_ID;
```

**Results (CSV format):**
```csv
Furniture_ID,Profit_Per_Unit
1,60.0
2,90.0
3,120.0
```

**Query 6: This is the primary goal of the optimization problem, representing the total profit generated from the current production quantities.**

```sql
SELECT SUM(pp.Quantity_Produced * (fmr.Market_Rate - fpc.Price_in_Dollar)) AS Total_Profit FROM production_plan pp JOIN furniture_market_rates fmr ON pp.Furniture_ID = fmr.Furniture_ID JOIN furniture_production_costs fpc ON pp.Furniture_ID = fpc.Furniture_ID;
```

**Results (CSV format):**
```csv
Total_Profit
0.0
```

**Query 7: This is useful for understanding the range of market rates and production costs across different furniture types.**

```sql
SELECT fmr.Furniture_ID, fmr.Market_Rate, fpc.Price_in_Dollar FROM furniture_market_rates fmr JOIN furniture_production_costs fpc ON fmr.Furniture_ID = fpc.Furniture_ID;
```

**Results (CSV format):**
```csv
Furniture_ID,Market_Rate,Price_in_Dollar
1,120.0,60.0
2,180.0,90.0
3,250.0,130.0
```

**Query 8: This helps in understanding the variability in market rates and production costs, which can influence the optimization strategy.**

```sql
SELECT MAX(fmr.Market_Rate) AS Max_Market_Rate, MIN(fmr.Market_Rate) AS Min_Market_Rate, MAX(fpc.Price_in_Dollar) AS Max_Production_Cost, MIN(fpc.Price_in_Dollar) AS Min_Production_Cost FROM furniture_market_rates fmr JOIN furniture_production_costs fpc ON fmr.Furniture_ID = fpc.Furniture_ID;
```

**Results (CSV format):**
```csv
Max_Market_Rate,Min_Market_Rate,Max_Production_Cost,Min_Production_Cost
250.0,120.0,130.0,60.0
```

