# Complete Optimization Problem and Solution: manufactory_1

## 1. Problem Context and Goals

### Context  
A manufacturing company is focused on optimizing its product portfolio to maximize total revenue while adhering to operational constraints. The company must determine the number of units to produce for each product, ensuring that production costs do not exceed the available budget, that demand for each product is fully met, and that production does not exceed capacity limits.  

The company has a predefined budget for production, which serves as an upper limit for the total production costs. The production cost for each product is calculated by multiplying the cost per unit by the number of units produced. The company also has specific demand requirements for each product, which must be fulfilled, and production capacity limits that cannot be exceeded.  

The operational parameters include the price of each product, the cost per unit for production, the demand for each product, and the production capacity for each product. These parameters are used to guide the decision-making process, ensuring that the optimization aligns with the company’s financial and operational goals.  

### Goals  
The primary goal of this optimization is to maximize the total revenue generated from the production of all products. Revenue is calculated by multiplying the price of each product by the number of units produced. Success is measured by achieving the highest possible revenue while staying within the constraints of the production budget, meeting all product demand, and not exceeding production capacity.  

## 2. Constraints  

The optimization must adhere to the following constraints:  
1. **Total Production Cost Constraint**: The total cost of producing all products must not exceed the available budget. The total production cost is calculated by summing the cost per unit multiplied by the number of units produced for each product.  
2. **Demand Fulfillment Constraint**: The number of units produced for each product must be at least equal to the demand for that product. This ensures that all customer orders are fulfilled.  
3. **Production Capacity Constraint**: The number of units produced for each product must not exceed the production capacity for that product. This ensures that the company does not overextend its manufacturing capabilities.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for production costs, demand, and capacity, and updating business configuration logic with budget and formulas. Missing mappings for budget, demand, and capacity are addressed.

CREATE TABLE Products (
  Price FLOAT
);

CREATE TABLE ProductionCosts (
  CostValue FLOAT
);

CREATE TABLE Demand (
  DemandValue INTEGER
);

CREATE TABLE Capacity (
  CapacityValue INTEGER
);

CREATE TABLE ProductionPlan (
  UnitsProduced FLOAT
);
```

### Data Dictionary  
- **Products**: Contains the price of each product, which is used to calculate revenue in the optimization objective.  
  - **Price**: The price of the product, serving as a coefficient in the revenue calculation.  
- **ProductionCosts**: Contains the cost per unit for producing each product, used to calculate total production costs.  
  - **CostValue**: The cost per unit for production, serving as a coefficient in the total production cost constraint.  
- **Demand**: Contains the demand for each product, which sets the minimum number of units that must be produced.  
  - **DemandValue**: The demand for the product, serving as the lower bound in the demand fulfillment constraint.  
- **Capacity**: Contains the production capacity for each product, which sets the maximum number of units that can be produced.  
  - **CapacityValue**: The production capacity for the product, serving as the upper bound in the production capacity constraint.  
- **ProductionPlan**: Contains the number of units produced for each product, which are the decision variables in the optimization model.  
  - **UnitsProduced**: The number of units produced for the product, representing the decision variable in the optimization model.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as revenue is calculated by multiplying the price by the number of units produced.**

```sql
SELECT Price FROM Products;
```

**Results (CSV format):**
```csv
Price
10.5
15.0
20.0
```

**Query 2: Query Description: Retrieve the cost per unit for each product, which is necessary for calculating the total cost.**

```sql
SELECT Cost FROM Products;
```

**Error:** Execution failed on sql 'SELECT Cost FROM Products;': no such column: Cost

**Query 3: Query Description: Retrieve the production capacity for each product, which is necessary for ensuring that the production does not exceed the capacity.**

```sql
SELECT Capacity FROM Products;
```

**Error:** Execution failed on sql 'SELECT Capacity FROM Products;': no such column: Capacity

**Query 4: Query Description: Retrieve the demand for each product, which is necessary for ensuring that the production meets the demand.**

```sql
SELECT Demand FROM Products;
```

**Error:** Execution failed on sql 'SELECT Demand FROM Products;': no such column: Demand

**Query 5: Query Description: Retrieve the total cost, which is calculated by multiplying the cost per unit by the number of units produced.**

```sql
SELECT SUM(Cost * UnitsProduced) AS TotalCost FROM Products;
```

**Error:** Execution failed on sql 'SELECT SUM(Cost * UnitsProduced) AS TotalCost FROM Products;': no such column: Cost

**Query 6: Query Description: Retrieve the total revenue, which is calculated by multiplying the price by the number of units produced.**

```sql
SELECT SUM(Price * UnitsProduced) AS TotalRevenue FROM Products;
```

**Error:** Execution failed on sql 'SELECT SUM(Price * UnitsProduced) AS TotalRevenue FROM Products;': no such column: UnitsProduced

**Query 7: Query Description: Retrieve the total profit, which is calculated by subtracting the total cost from the total revenue.**

```sql
SELECT SUM((Price - Cost) * UnitsProduced) AS TotalProfit FROM Products;
```

**Error:** Execution failed on sql 'SELECT SUM((Price - Cost) * UnitsProduced) AS TotalProfit FROM Products;': no such column: Cost

**Query 8: Query Description: Retrieve the list of products where the production exceeds the demand.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced > Demand;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced > Demand;': no such column: ProductID

**Query 9: Query Description: Retrieve the list of products where the production is below the demand.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced < Demand;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced < Demand;': no such column: ProductID

**Query 10: Query Description: Retrieve the list of products where the production equals the demand.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced = Demand;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced = Demand;': no such column: ProductID

**Query 11: Query Description: Retrieve the list of products where the production exceeds the capacity.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced > Capacity;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced > Capacity;': no such column: ProductID

**Query 12: Query Description: Retrieve the list of products where the production is below the capacity.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced < Capacity;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced < Capacity;': no such column: ProductID

**Query 13: Query Description: Retrieve the list of products where the production equals the capacity.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced = Capacity;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced = Capacity;': no such column: ProductID

**Query 14: Query Description: Retrieve the list of products where the production is below the capacity and the demand is not met.**

```sql
SELECT ProductID FROM Products WHERE UnitsProduced < Capacity AND UnitsProduced < Demand;
```

**Error:** Execution failed on sql 'SELECT ProductID FROM Products WHERE UnitsProduced < Capacity AND UnitsProduced < Demand;': no such column: ProductID

**Query 15: Query Description: Retrieve the total number of trains a station can handle**

```sql
SELECT COUNT(DISTINCT train_id) FROM;
```

**Error:** Execution failed on sql 'SELECT COUNT(DISTINCT train_id) FROM;': near ";": syntax error

