# Complete Optimization Problem and Solution: movie_1

## 1. Problem Context and Goals

### Context  
A movie streaming platform is focused on enhancing viewer satisfaction by strategically recommending movies based on user ratings. The platform must decide how many times each movie should be recommended to each reviewer to maximize overall satisfaction. The satisfaction is measured by the sum of star ratings associated with the recommendations. 

The platform operates under specific operational constraints:  
1. Each movie has a maximum number of times it can be recommended across all reviewers.  
2. Each reviewer has a maximum number of recommendations they can receive in total.  
3. Recommendations must be non-negative and integer-valued.  

The star ratings provided by reviewers for movies serve as the key metric for determining the value of each recommendation. The platform’s goal is to allocate recommendations in a way that maximizes the total satisfaction while respecting these operational limits.  

### Goals  
The primary goal of this optimization problem is to maximize the total satisfaction derived from movie recommendations. This is achieved by strategically assigning the number of times each movie is recommended to each reviewer, weighted by the star ratings provided by the reviewers. Success is measured by the total sum of star ratings across all recommendations, ensuring that the platform’s operational constraints are fully respected.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Movie Recommendation Limits**: The total number of times a movie is recommended across all reviewers must not exceed the maximum allowed for that movie.  
2. **Reviewer Recommendation Limits**: The total number of recommendations received by a reviewer must not exceed the maximum allowed for that reviewer.  
3. **Non-Negative Recommendations**: The number of times a movie is recommended to a reviewer must be a non-negative integer.  

These constraints ensure that the platform’s operational limits are respected while maximizing viewer satisfaction.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for star ratings to address missing objective coefficients, updated business configuration logic, and ensured all mappings are complete.

CREATE TABLE MovieRecommendationLimits (
  movie_id INTEGER,
  max_recommendations INTEGER
);

CREATE TABLE ReviewerRecommendationLimits (
  reviewer_id INTEGER,
  max_recommendations INTEGER
);

CREATE TABLE RecommendationAssignments (
  reviewer_id INTEGER,
  movie_id INTEGER,
  recommendation_count INTEGER
);

CREATE TABLE MovieRatings (
  reviewer_id INTEGER,
  movie_id INTEGER,
  star_rating INTEGER
);
```

### Data Dictionary  
- **MovieRecommendationLimits**:  
  - **Purpose**: Defines the maximum number of recommendations allowed for each movie.  
  - **Columns**:  
    - `movie_id`: Unique identifier for the movie.  
    - `max_recommendations`: Maximum number of recommendations allowed for the movie.  

- **ReviewerRecommendationLimits**:  
  - **Purpose**: Defines the maximum number of recommendations allowed for each reviewer.  
  - **Columns**:  
    - `reviewer_id`: Unique identifier for the reviewer.  
    - `max_recommendations`: Maximum number of recommendations allowed for the reviewer.  

- **RecommendationAssignments**:  
  - **Purpose**: Tracks the number of times each movie is recommended to each reviewer.  
  - **Columns**:  
    - `reviewer_id`: Unique identifier for the reviewer receiving the recommendation.  
    - `movie_id`: Unique identifier for the movie being recommended.  
    - `recommendation_count`: Number of times the movie is recommended to the reviewer.  

- **MovieRatings**:  
  - **Purpose**: Stores the star ratings given by reviewers to movies.  
  - **Columns**:  
    - `reviewer_id`: Unique identifier for the reviewer providing the rating.  
    - `movie_id`: Unique identifier for the movie being rated.  
    - `star_rating`: Star rating given by the reviewer to the movie.  


### Retrieved Values

**Query 1: This data is crucial as it represents the satisfaction value (star rating) associated with each recommendation.**

```sql
SELECT reviewer_id, movie_id, star_rating FROM MovieRatings;
```

**Results (CSV format):**
```csv
reviewer_id,movie_id,star_rating
1,1,5
2,2,4
3,3,3
```

**Query 2: This data is essential for the constraint that limits the total number of recommendations per movie.**

```sql
SELECT movie_id, max_recommendations FROM MovieRecommendationLimits;
```

**Results (CSV format):**
```csv
movie_id,max_recommendations
1,10
2,7
3,5
```

**Query 3: This data is essential for the constraint that limits the total number of recommendations per reviewer.**

```sql
SELECT reviewer_id, max_recommendations FROM ReviewerRecommendationLimits;
```

**Results (CSV format):**
```csv
reviewer_id,max_recommendations
1,5
2,4
3,3
```

**Query 4: This data represents the current state of recommendations and is necessary for checking constraints and optimizing the allocation.**

```sql
SELECT reviewer_id, movie_id, recommendation_count FROM RecommendationAssignments;
```

**Results (CSV format):**
```csv
reviewer_id,movie_id,recommendation_count
1,1,2
2,2,1
3,3,1
```

**Query 5: This aggregated data helps in ensuring that the movie recommendation limits are not exceeded.**

```sql
SELECT movie_id, SUM(recommendation_count) AS total_recommendations FROM RecommendationAssignments GROUP BY movie_id;
```

**Results (CSV format):**
```csv
movie_id,total_recommendations
1,2
2,1
3,1
```

**Query 6: This aggregated data helps in ensuring that the reviewer recommendation limits are not exceeded.**

```sql
SELECT reviewer_id, SUM(recommendation_count) AS total_recommendations FROM RecommendationAssignments GROUP BY reviewer_id;
```

**Results (CSV format):**
```csv
reviewer_id,total_recommendations
1,2
2,1
3,1
```

**Query 7: This joined data helps in understanding the potential satisfaction value and the constraints for each movie.**

```sql
SELECT mr.movie_id, mr.star_rating, mrl.max_recommendations FROM MovieRatings mr JOIN MovieRecommendationLimits mrl ON mr.movie_id = mrl.movie_id;
```

**Results (CSV format):**
```csv
movie_id,star_rating,max_recommendations
1,5,10
2,4,7
3,3,5
```

**Query 8: This joined data helps in understanding the potential satisfaction value and the constraints for each reviewer.**

```sql
SELECT mr.reviewer_id, mr.star_rating, rrl.max_recommendations FROM MovieRatings mr JOIN ReviewerRecommendationLimits rrl ON mr.reviewer_id = rrl.reviewer_id;
```

**Results (CSV format):**
```csv
reviewer_id,star_rating,max_recommendations
1,5,5
2,4,4
3,3,3
```

**Query 9: This comprehensive data is useful for evaluating the current state and potential adjustments to maximize satisfaction.**

```sql
SELECT mr.reviewer_id, mr.movie_id, mr.star_rating, ra.recommendation_count, mrl.max_recommendations AS movie_max, rrl.max_recommendations AS reviewer_max FROM MovieRatings mr LEFT JOIN RecommendationAssignments ra ON mr.reviewer_id = ra.reviewer_id AND mr.movie_id = ra.movie_id JOIN MovieRecommendationLimits mrl ON mr.movie_id = mrl.movie_id JOIN ReviewerRecommendationLimits rrl ON mr.reviewer_id = rrl.reviewer_id;
```

**Results (CSV format):**
```csv
reviewer_id,movie_id,star_rating,recommendation_count,movie_max,reviewer_max
1,1,5,2,10,5
2,2,4,1,7,4
3,3,3,1,5,3
```

