# Complete Optimization Problem and Solution: coffee_shop

## 1. Problem Context and Goals

### Context  
The coffee shop chain is focused on optimizing the allocation of staff during happy hours across its various locations. The primary decision involves determining the number of staff members to assign to each happy hour event, represented by the variable Num_of_shaff_in_charge[HH_ID], which is an integer. The objective is to maximize the overall customer satisfaction score, which is calculated by summing the product of each shop's satisfaction score and the number of staff allocated to its happy hour events. 

Operational parameters include the total number of staff available for allocation across all shops, which serves as a constraint in the optimization model. Additionally, there is a minimum number of staff required to manage each happy hour event, ensuring that service levels are maintained. These parameters are critical in forming the linear constraints of the optimization problem. The business configuration specifies that the total staff available is 19, reflecting the sum of staff from all shops, and a minimum of 2 staff members is required per happy hour to maintain basic service levels during peak times.

### Goals  
The goal of the optimization is to maximize the overall customer satisfaction score across all coffee shops during happy hours. This is achieved by strategically allocating staff to each event, ensuring that the sum of the product of each shop's satisfaction score and the number of staff allocated is maximized. Success is measured by the increase in the total satisfaction score, aligning with the expected coefficient sources from the shop's satisfaction scores.

## 2. Constraints    

The optimization problem is subject to several linear constraints:

- The total number of staff allocated across all happy hour events must not exceed the total staff available, which is 19.
- Each happy hour event must have at least the minimum required number of staff, which is 2, to ensure adequate service levels.
- The number of staff allocated to each happy hour event at a shop cannot exceed the number of staff available at that shop.

These constraints are designed to ensure that the allocation of staff is both feasible and efficient, adhering to the operational limitations and requirements of the coffee shop chain.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding missing tables and parameters for optimization, modifying existing tables to fill mapping gaps, and moving scalar parameters to configuration logic.

CREATE TABLE shop (
  Shop_ID INTEGER,
  Score FLOAT,
  Num_of_staff INTEGER
);

CREATE TABLE staff_allocation (
  HH_ID INTEGER,
  Num_of_shaff_in_charge INTEGER
);
```

### Data Dictionary  
The database consists of two main tables: 

- **Shop Table**: This table stores information about each coffee shop, including:
  - **Shop_ID**: A unique identifier for each shop, used to map scores and staff numbers.
  - **Score**: The customer satisfaction score for each shop, which serves as a coefficient in the objective function.
  - **Num_of_staff**: The number of staff available at each shop, used as a constraint bound for staff allocation.

- **Staff Allocation Table**: This table tracks the allocation of staff to happy hours, including:
  - **HH_ID**: A unique identifier for each happy hour event, used to index staff allocation.
  - **Num_of_shaff_in_charge**: The number of staff allocated to each happy hour event, serving as a decision variable in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical staffing levels and customer satisfaction scores for a small chain of coffee shops, ensuring that the optimization problem remains solvable and realistic.

-- Realistic data for shop
INSERT INTO shop (Shop_ID, Score, Num_of_staff) VALUES (1, 4.5, 5);
INSERT INTO shop (Shop_ID, Score, Num_of_staff) VALUES (2, 3.8, 8);
INSERT INTO shop (Shop_ID, Score, Num_of_staff) VALUES (3, 4.2, 6);

-- Realistic data for staff_allocation
INSERT INTO staff_allocation (HH_ID, Num_of_shaff_in_charge) VALUES (101, 3);
INSERT INTO staff_allocation (HH_ID, Num_of_shaff_in_charge) VALUES (102, 4);
INSERT INTO staff_allocation (HH_ID, Num_of_shaff_in_charge) VALUES (103, 2);
```