# Complete Optimization Problem and Solution: wine_1

## 1. Problem Context and Goals

### Context  
The winery operates in a competitive market and aims to maximize its profitability by determining the optimal number of cases to produce for each wine type. The decision-making process involves balancing production capacity, available resources, and market demand. The winery has a fixed budget for production, and each wine type requires a specific amount of grapes and incurs a distinct production cost. Additionally, the market demand for each wine type sets an upper limit on the number of cases that can be sold.  

The profit for each wine type is calculated as the difference between the selling price and the production cost per case. The winery’s goal is to allocate its resources efficiently to maximize total profit while adhering to constraints on grape availability, production budget, and market demand.  

Key operational parameters include:  
- **Profit per case for each wine type**: Determined by subtracting the production cost from the selling price.  
- **Total budget available for production**: A fixed amount of $100,000 allocated for production costs.  
- **Grape usage per case**: The amount of grapes required to produce one case of each wine type.  
- **Market demand**: The maximum number of cases that can be sold for each wine type.  

The decision variables are the number of cases to produce for each wine type, which must be whole numbers (integers). The problem is structured to ensure all relationships are linear, avoiding scenarios that would require nonlinear calculations such as variable products or divisions.  

### Goals  
The primary goal of the optimization is to maximize the winery’s total profit. This is achieved by determining the optimal number of cases to produce for each wine type, considering the profit per case and the constraints on resources and market demand. Success is measured by the total profit generated, which is the sum of the profit from each wine type multiplied by the number of cases produced. The optimization ensures that the winery operates within its budget, does not exceed grape availability, and meets market demand limits.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Grape availability**: The total amount of grapes used to produce all wine types must not exceed the total grapes available. This is calculated by summing the grape usage per case multiplied by the number of cases produced for each wine type.  
2. **Production budget**: The total production cost for all wine types must not exceed the available budget of $100,000. This is calculated by summing the production cost per case multiplied by the number of cases produced for each wine type.  
3. **Market demand**: The number of cases produced for each wine type must not exceed the maximum market demand for that wine type.  

These constraints ensure that the winery operates within its resource limitations and market conditions while maximizing profitability.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for profit, grape usage, production costs, and market demand. Configuration logic updates include scalar parameters for budget and formulas for profit calculation.

CREATE TABLE wine_profit (
  Profit FLOAT
);

CREATE TABLE wine_grape_usage (
  Grape_usage FLOAT
);

CREATE TABLE wine_production_cost (
  Production_cost FLOAT
);

CREATE TABLE wine_market_demand (
  Market_demand INTEGER
);

CREATE TABLE wine (
  Cases INTEGER
);
```

### Data Dictionary  
- **wine_profit**: Contains the profit per case for each wine type, used as the coefficient for the optimization objective.  
  - *Profit*: The profit per case for each wine type, calculated as the difference between the selling price and the production cost.  
- **wine_grape_usage**: Specifies the amount of grapes required to produce one case of each wine type, used to enforce the grape availability constraint.  
  - *Grape_usage*: The grape usage per case for each wine type.  
- **wine_production_cost**: Lists the production cost per case for each wine type, used to enforce the budget constraint.  
  - *Production_cost*: The production cost per case for each wine type.  
- **wine_market_demand**: Indicates the maximum market demand for each wine type, used to enforce the market demand constraint.  
  - *Market_demand*: The maximum number of cases that can be sold for each wine type.  
- **wine**: Tracks the number of cases produced for each wine type, serving as the decision variable in the optimization.  
  - *Cases*: The number of cases produced for each wine type, which must be an integer.  


### Retrieved Values

**Query 1: Query Description: Retrieve the profit per case for each wine type. This is crucial for the objective function to maximize total profit.**

```sql
SELECT Profit FROM wine_profit;
```

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

**Query 2: Query Description: Retrieve the grape usage per case for each wine type. This is essential for the grape availability constraint.**

```sql
SELECT Grape_usage FROM wine_grape_usage;
```

**Results (CSV format):**
```csv
Grape_usage
2.5
3.0
4.0
```

**Query 3: Query Description: Retrieve the production cost per case for each wine type. This is necessary for the budget constraint.**

```sql
SELECT Production_cost FROM wine_production_cost;
```

**Results (CSV format):**
```csv
Production_cost
5.0
6.0
7.0
```

**Query 4: Query Description: Retrieve the market demand for each wine type. This is important for the market demand constraint.**

```sql
SELECT Market_demand FROM wine_market_demand;
```

**Results (CSV format):**
```csv
Market_demand
100
150
200
```

**Query 5: Query Description: Retrieve the current number of cases produced for each wine type. This serves as the decision variable in the optimization.**

```sql
SELECT Cases FROM wine;
```

**Results (CSV format):**
```csv
Cases
50
75
100
```

**Query 6: Query Description: Calculate the total grape usage across all wine types. This helps in ensuring the grape availability constraint is not exceeded.**

```sql
SELECT SUM(Grape_usage * Cases) AS Total_Grape_Usage FROM wine_grape_usage JOIN wine ON wine_grape_usage.id = wine.id;
```

**Error:** Execution failed on sql 'SELECT SUM(Grape_usage * Cases) AS Total_Grape_Usage FROM wine_grape_usage JOIN wine ON wine_grape_usage.id = wine.id;': no such column: wine_grape_usage.id

**Query 7: Query Description: Calculate the total production cost across all wine types. This helps in ensuring the budget constraint is not exceeded.**

```sql
SELECT SUM(Production_cost * Cases) AS Total_Production_Cost FROM wine_production_cost JOIN wine ON wine_production_cost.id = wine.id;
```

**Error:** Execution failed on sql 'SELECT SUM(Production_cost * Cases) AS Total_Production_Cost FROM wine_production_cost JOIN wine ON wine_production_cost.id = wine.id;': no such column: wine_production_cost.id

**Query 8: Query Description: Calculate the total profit across all wine types. This is the objective function to be maximized.**

```sql
SELECT SUM(Profit * Cases) AS Total_Profit FROM wine_profit JOIN wine ON wine_profit.id = wine.id;
```

**Error:** Execution failed on sql 'SELECT SUM(Profit * Cases) AS Total_Profit FROM wine_profit JOIN wine ON wine_profit.id = wine.id;': no such column: wine_profit.id

**Query 9: Query Description: Retrieve the maximum market demand for each wine type along with the current number of cases produced. This helps in ensuring the market demand constraint is not exceeded.**

```sql
SELECT wine_market_demand.Market_demand, wine.Cases FROM wine_market_demand JOIN wine ON wine_market_demand.id = wine.id;
```

**Error:** Execution failed on sql 'SELECT wine_market_demand.Market_demand, wine.Cases FROM wine_market_demand JOIN wine ON wine_market_demand.id = wine.id;': no such column: wine_market_demand.id

