# Complete Optimization Problem and Solution: coffee_shop

## 1. Problem Context and Goals

### Context  
A coffee shop chain is focused on optimizing staff allocation during happy hours across its various shops. The goal is to maximize customer satisfaction by ensuring that members, who are valued based on their membership level, spend more during these peak hours. The chain must allocate staff efficiently to meet customer demand while staying within operational constraints. 

The key decision involves determining the number of staff to assign to each shop during happy hours. This decision must consider the total budget for staff across all shops, which is capped at 150. Additionally, each shop has specific staffing limits: a minimum of 2 staff members is required to maintain basic operations, and a maximum of 15 staff members is allowed to handle peak customer traffic. The chain also enforces a maximum spending limit of $50 per member to control costs while encouraging customer spending.

The optimization problem is designed to maximize the total amount spent by members during happy hours, weighted by their membership level. This ensures that higher-tier members, who contribute more to revenue, are prioritized. The problem is structured to ensure that all constraints are linear, avoiding any nonlinear relationships such as variable products or divisions.

### Goals  
The primary goal of this optimization is to maximize the total weighted spending by members during happy hours. This is achieved by assigning the optimal number of staff to each shop, ensuring that higher-tier members are adequately served. Success is measured by the total amount spent by members, adjusted for their membership level, while adhering to the total staff budget and individual shop staffing limits. The optimization ensures that the chain operates efficiently, balancing customer satisfaction with operational costs.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Total Staff Budget Constraint**: The total number of staff assigned across all shops must not exceed the total budget of 150. This ensures that the chain operates within its financial limits.  
2. **Shop Staffing Limits**: Each shop must have a minimum of 2 staff members to maintain basic operations and a maximum of 15 staff members to handle peak customer traffic. This ensures that each shop is adequately staffed without exceeding its capacity.  
3. **Maximum Spending Limit per Member**: The spending per member during happy hours must not exceed $50. This constraint helps control costs while encouraging customer spending.  

These constraints are designed to ensure that the optimization problem remains linear, avoiding any nonlinear relationships that could complicate the solution.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing constraint bounds, modifying existing tables to refine decision variable mapping, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE happy_hour_member (
  Total_amount FLOAT
);

CREATE TABLE member (
  Level_of_membership INTEGER
);

CREATE TABLE happy_hour (
  Num_of_staff_in_charge INTEGER
);

CREATE TABLE shop_staff_limits (
  Max_Staff_Per_Shop INTEGER,
  Min_Staff_Per_Shop INTEGER
);
```

### Data Dictionary  
- **happy_hour_member**:  
  - **Business Purpose**: Records the amount spent by each member during happy hours.  
  - **Optimization Role**: Provides coefficients for the objective function.  
  - **Columns**:  
    - **Total_amount**: The amount spent by a member during a happy hour.  

- **member**:  
  - **Business Purpose**: Stores membership information for each member.  
  - **Optimization Role**: Provides coefficients for the objective function.  
  - **Columns**:  
    - **Level_of_membership**: The membership level of a member, used to weight their spending in the objective function.  

- **happy_hour**:  
  - **Business Purpose**: Records the number of staff assigned to each shop during happy hours.  
  - **Optimization Role**: Represents the decision variable in the optimization model.  
  - **Columns**:  
    - **Num_of_staff_in_charge**: The number of staff assigned to a shop during a happy hour.  

- **shop_staff_limits**:  
  - **Business Purpose**: Stores the maximum and minimum number of staff allowed per shop.  
  - **Optimization Role**: Provides constraint bounds for the optimization model.  
  - **Columns**:  
    - **Max_Staff_Per_Shop**: The maximum number of staff allowed per shop.  
    - **Min_Staff_Per_Shop**: The minimum number of staff required per shop.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as it helps in weighting the spending by membership level.**

```sql
SELECT hhm.Total_amount, m.Level_of_membership FROM happy_hour_member hhm JOIN member m ON hhm.member_id = m.member_id;
```

**Error:** Execution failed on sql 'SELECT hhm.Total_amount, m.Level_of_membership FROM happy_hour_member hhm JOIN member m ON hhm.member_id = m.member_id;': no such column: hhm.member_id

**Query 2: This data represents the decision variable in the optimization model, which needs to be optimized.**

```sql
SELECT Num_of_staff_in_charge FROM happy_hour;
```

**Results (CSV format):**
```csv
Num_of_staff_in_charge
3
5
7
```

**Query 3: This data provides the constraint bounds for the optimization model, ensuring that staffing levels are within acceptable limits.**

```sql
SELECT Max_Staff_Per_Shop, Min_Staff_Per_Shop FROM shop_staff_limits;
```

**Results (CSV format):**
```csv
Max_Staff_Per_Shop,Min_Staff_Per_Shop
10,2
12,3
15,4
```

**Query 4: This data is important for the total staff budget constraint, ensuring that the total number of staff does not exceed the budget of 150.**

```sql
SELECT SUM(Num_of_staff_in_charge) AS Total_Staff_Assigned FROM happy_hour;
```

**Results (CSV format):**
```csv
Total_Staff_Assigned
15
```

**Query 5: This data is essential for the objective function, as it directly relates to the goal of maximizing weighted spending.**

```sql
SELECT SUM(hhm.Total_amount * m.Level_of_membership) AS Weighted_Total_Spending FROM happy_hour_member hhm JOIN member m ON hhm.member_id = m.member_id;
```

**Error:** Execution failed on sql 'SELECT SUM(hhm.Total_amount * m.Level_of_membership) AS Weighted_Total_Spending FROM happy_hour_member hhm JOIN member m ON hhm.member_id = m.member_id;': no such column: hhm.member_id

**Query 6: This data helps in understanding the spending behavior and ensuring that the maximum spending limit per member ($50) is not exceeded.**

```sql
SELECT AVG(Total_amount) AS Average_Spending_Per_Member FROM happy_hour_member;
```

**Results (CSV format):**
```csv
Average_Spending_Per_Member
23.75
```

**Query 7: This data helps in verifying that the staffing levels for each shop are within the allowed limits.**

```sql
SELECT hh.Num_of_staff_in_charge, ssl.Max_Staff_Per_Shop, ssl.Min_Staff_Per_Shop FROM happy_hour hh JOIN shop_staff_limits ssl ON hh.shop_id = ssl.shop_id;
```

**Error:** Execution failed on sql 'SELECT hh.Num_of_staff_in_charge, ssl.Max_Staff_Per_Shop, ssl.Min_Staff_Per_Shop FROM happy_hour hh JOIN shop_staff_limits ssl ON hh.shop_id = ssl.shop_id;': no such column: hh.shop_id

**Query 8: This data helps in understanding the contribution of each shop to the overall objective function.**

```sql
SELECT hh.shop_id, SUM(hhm.Total_amount * m.Level_of_membership) AS Weighted_Spending_Per_Shop FROM happy_hour_member hhm JOIN member m ON hhm.member_id = m.member_id JOIN happy_hour hh ON hhm.happy_hour_id = hh.happy_hour_id GROUP BY hh.shop_id;
```

**Error:** Execution failed on sql 'SELECT hh.shop_id, SUM(hhm.Total_amount * m.Level_of_membership) AS Weighted_Spending_Per_Shop FROM happy_hour_member hhm JOIN member m ON hhm.member_id = m.member_id JOIN happy_hour hh ON hhm.happy_hour_id = hh.happy_hour_id GROUP BY hh.shop_id;': no such column: hh.shop_id

