# Complete Optimization Problem and Solution: restaurants

## 1. Problem Context and Goals

### Context  
A restaurant chain is strategically planning the placement of new restaurants across various cities to maximize overall customer satisfaction. Customer satisfaction is measured by the restaurant's rating, which serves as a key indicator of success. The decision to place a new restaurant in a specific city is represented by a binary choice: either to place the restaurant or not. The goal is to ensure that the total customer satisfaction across all new placements is maximized while adhering to several operational constraints.  

The business has established specific configuration parameters to guide this decision-making process:  
- **Maximum number of new restaurants allowed per city**: To prevent over-saturation, no more than 2 new restaurants can be placed in any single city.  
- **Minimum number of new restaurants required per region**: To maintain a balanced distribution, at least 1 new restaurant must be placed in each geographical region.  
- **Total budget constraint for the number of new restaurants**: The total number of new restaurants placed across all cities cannot exceed 5, ensuring financial feasibility.  

These parameters ensure that the optimization problem remains linear and avoids scenarios requiring complex nonlinear relationships, such as variable products or divisions.  

### Goals  
The primary goal of this optimization problem is to maximize the total customer satisfaction derived from the placement of new restaurants. This is achieved by selecting the optimal set of cities for new restaurant placements, where each placement contributes to the overall satisfaction based on the restaurant's rating. Success is measured by the sum of the ratings of all selected restaurants, ensuring that the highest possible customer satisfaction is achieved within the defined constraints.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure that the solution aligns with the business's operational and financial limitations:  
1. **Total budget constraint**: The total number of new restaurants placed across all cities must not exceed the budget of 5. This ensures that the financial resources are not overextended.  
2. **Regional distribution constraint**: Each geographical region must have at least 1 new restaurant placed. This ensures a fair and balanced distribution of new restaurants across all regions.  
3. **City-level placement constraint**: No more than 2 new restaurants can be placed in any single city. This prevents over-saturation and ensures a diverse placement strategy.  

These constraints are designed to naturally lead to linear mathematical forms, avoiding any complexity that would require nonlinear relationships.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, moving scalar parameters to business configuration logic, and updating the data dictionary to reflect optimization mappings.

CREATE TABLE RESTAURANT (
  RATING FLOAT,
  REGION STRING
);

CREATE TABLE DECISION_VARIABLES (
  X_i BOOLEAN
);
```

### Data Dictionary  
- **RESTAURANT Table**:  
  - **RATING**: Represents the customer satisfaction rating of the restaurant. This value is used as the coefficient in the objective function to maximize total satisfaction.  
  - **REGION**: Indicates the geographical region where the restaurant is located. This supports the regional distribution constraint.  

- **DECISION_VARIABLES Table**:  
  - **X_i**: A binary decision variable that indicates whether to place a new restaurant in city i. This is the key variable in the optimization model.  


### Retrieved Values

**Query 1: This is important for the regional distribution constraint, ensuring at least one new restaurant is placed in each region.**

```sql
SELECT REGION, COUNT(*) AS NUM_RESTAURANTS FROM RESTAURANT GROUP BY REGION;
```

**Results (CSV format):**
```csv
REGION,NUM_RESTAURANTS
East,1
North,2
South,1
West,1
```

**Query 2: This helps in understanding the potential customer satisfaction impact of placing a new restaurant in each region.**

```sql
SELECT REGION, AVG(RATING) AS AVG_RATING FROM RESTAURANT GROUP BY REGION;
```

**Results (CSV format):**
```csv
REGION,AVG_RATING
East,4.2
North,4.3
South,3.8
West,3.9
```

**Query 3: This is useful for identifying the cities with the highest potential customer satisfaction impact.**

```sql
SELECT REGION, MAX(RATING) AS MAX_RATING FROM RESTAURANT GROUP BY REGION;
```

**Results (CSV format):**
```csv
REGION,MAX_RATING
East,4.2
North,4.5
South,3.8
West,3.9
```

**Query 4: This data is crucial for the objective function, as it provides the coefficients (ratings) to maximize total customer satisfaction.**

```sql
SELECT RATING, REGION FROM RESTAURANT;
```

**Results (CSV format):**
```csv
RATING,REGION
4.5,North
3.8,South
4.2,East
3.9,West
4.1,North
```

**Query 5: This is important for the total budget constraint, ensuring the total number of new restaurants does not exceed the budget.**

```sql
SELECT COUNT(*) AS TOTAL_NEW_RESTAURANTS FROM DECISION_VARIABLES WHERE X_i = TRUE;
```

**Results (CSV format):**
```csv
TOTAL_NEW_RESTAURANTS
2
```

**Query 6: This helps in enforcing the regional distribution constraint, ensuring at least one new restaurant is placed in each region.**

```sql
SELECT REGION, COUNT(*) AS NEW_RESTAURANTS_PER_REGION FROM DECISION_VARIABLES JOIN RESTAURANT ON DECISION_VARIABLES.X_i = TRUE GROUP BY REGION;
```

**Results (CSV format):**
```csv
REGION,NEW_RESTAURANTS_PER_REGION
East,2
North,4
South,2
West,2
```

**Query 7: This is important for the city-level placement constraint, ensuring no more than 2 new restaurants are placed in any single city.**

```sql
SELECT CITY, COUNT(*) AS NEW_RESTAURANTS_PER_CITY FROM DECISION_VARIABLES JOIN RESTAURANT ON DECISION_VARIABLES.X_i = TRUE GROUP BY CITY;
```

**Error:** Execution failed on sql 'SELECT CITY, COUNT(*) AS NEW_RESTAURANTS_PER_CITY FROM DECISION_VARIABLES JOIN RESTAURANT ON DECISION_VARIABLES.X_i = TRUE GROUP BY CITY;': no such column: CITY

**Query 8: This data is useful for making informed decisions on where to place new restaurants based on regional and city-level data.**

```sql
SELECT CITY, REGION, RATING FROM RESTAURANT;
```

**Error:** Execution failed on sql 'SELECT CITY, REGION, RATING FROM RESTAURANT;': no such column: CITY

**Query 9: This helps in understanding the current saturation level of restaurants in each city, which is important for the city-level placement constraint.**

```sql
SELECT CITY, COUNT(*) AS TOTAL_RESTAURANTS FROM RESTAURANT GROUP BY CITY;
```

**Error:** Execution failed on sql 'SELECT CITY, COUNT(*) AS TOTAL_RESTAURANTS FROM RESTAURANT GROUP BY CITY;': no such column: CITY

**Query 10: This is crucial for enforcing the regional distribution constraint, ensuring at least one new restaurant is placed in each region.**

```sql
SELECT REGION, COUNT(*) AS NEW_RESTAURANTS_PER_REGION FROM DECISION_VARIABLES JOIN RESTAURANT ON DECISION_VARIABLES.X_i = TRUE GROUP BY REGION ) AS REGION_COUNTS WHERE NEW_RESTAURANTS_PER_REGION < 1;
```

**Error:** Execution failed on sql 'SELECT REGION, COUNT(*) AS NEW_RESTAURANTS_PER_REGION FROM DECISION_VARIABLES JOIN RESTAURANT ON DECISION_VARIABLES.X_i = TRUE GROUP BY REGION ) AS REGION_COUNTS WHERE NEW_RESTAURANTS_PER_REGION < 1;': near ")": syntax error

