# 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.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for winery operations, considering typical production costs, grape usage, market demand, and profit margins. The values ensure that the optimization problem is meaningful and solvable by balancing constraints and objectives.

-- Realistic data for wine_profit
INSERT INTO wine_profit (Profit) VALUES (10.5);
INSERT INTO wine_profit (Profit) VALUES (15.0);
INSERT INTO wine_profit (Profit) VALUES (20.0);

-- Realistic data for wine_grape_usage
INSERT INTO wine_grape_usage (Grape_usage) VALUES (2.5);
INSERT INTO wine_grape_usage (Grape_usage) VALUES (3.0);
INSERT INTO wine_grape_usage (Grape_usage) VALUES (4.0);

-- Realistic data for wine_production_cost
INSERT INTO wine_production_cost (Production_cost) VALUES (5.0);
INSERT INTO wine_production_cost (Production_cost) VALUES (6.0);
INSERT INTO wine_production_cost (Production_cost) VALUES (7.0);

-- Realistic data for wine_market_demand
INSERT INTO wine_market_demand (Market_demand) VALUES (100);
INSERT INTO wine_market_demand (Market_demand) VALUES (150);
INSERT INTO wine_market_demand (Market_demand) VALUES (200);

-- Realistic data for wine
INSERT INTO wine (Cases) VALUES (50);
INSERT INTO wine (Cases) VALUES (75);
INSERT INTO wine (Cases) VALUES (100);
```