# Complete Optimization Problem and Solution: music_1

## 1. Problem Context and Goals

### Context  
A music streaming platform is focused on optimizing its storage and bandwidth usage by strategically selecting a subset of songs to store locally on its servers. The platform must decide which songs to store locally, represented by binary decisions for each song. The primary goal is to minimize the total file size of the songs stored locally, ensuring efficient use of storage resources.  

The platform has established several operational parameters to maintain a diverse and high-quality music library. These include:  
- A minimum number of songs that must be stored locally to ensure a substantial library for users.  
- A minimum average rating for the stored songs to maintain high-quality content and enhance user satisfaction.  
- A maximum number of songs per artist to prevent overrepresentation of any single artist and promote diversity.  
- A minimum number of songs per genre to ensure a well-rounded music library across different genres.  

These parameters are defined in the business configuration and serve as the foundation for the optimization constraints. The decision-making process is linear, focusing solely on whether each song is stored locally, without involving complex relationships such as variable products or divisions.  

### Goals  
The optimization goal is to minimize the total file size of the songs stored locally. This is achieved by selecting a subset of songs that meets all operational constraints while keeping the combined file size as small as possible. Success is measured by the efficient use of storage resources, ensuring that the platform maintains a diverse and high-quality library without exceeding storage limitations.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure the platform meets its operational and quality requirements:  
1. **Minimum Total Songs Stored**: The total number of songs stored locally must meet or exceed a specified minimum. This ensures a substantial library for users.  
2. **Minimum Average Rating**: The average rating of the songs stored locally must meet or exceed a specified threshold. This maintains high-quality content and enhances user satisfaction.  
3. **Maximum Songs per Artist**: The number of songs stored locally for any single artist must not exceed a specified limit. This prevents overrepresentation of any artist and promotes diversity.  
4. **Minimum Songs per Genre**: The number of songs stored locally for each genre must meet or exceed a specified minimum. This ensures a diverse music library across different genres.  

These constraints are designed to align with linear mathematical forms, ensuring the optimization problem remains straightforward and computationally efficient.  

## 3. Available Data  

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

CREATE TABLE files (
  file_size INTEGER,
  is_stored_locally BOOLEAN
);

CREATE TABLE decision_variables (
  song_id INTEGER,
  is_stored_locally BOOLEAN
);

CREATE TABLE constraints (
  constraint_type STRING,
  constraint_value INTEGER
);
```

### Data Dictionary  
The data dictionary provides a clear mapping of tables and columns to their business purposes and optimization roles:  
- **files**: Stores metadata about songs, including file size and storage decisions.  
  - `file_size`: Represents the file size of the song in MB. This is used as a coefficient in the objective function to minimize total storage.  
  - `is_stored_locally`: Indicates whether the song is stored locally. This serves as the binary decision variable in the optimization problem.  
- **decision_variables**: Contains binary decision variables for song storage.  
  - `song_id`: Unique identifier for each song, used to index the decision variables.  
  - `is_stored_locally`: Indicates whether the song is stored locally, aligning with the decision variable in the optimization problem.  
- **constraints**: Defines the constraints for the optimization problem.  
  - `constraint_type`: Specifies the type of constraint (e.g., genre, artist).  
  - `constraint_value`: Represents the value of the constraint (e.g., minimum songs per genre), serving as the bound for the constraint in the optimization problem.  


### Retrieved Values

**Query 1: This is essential for the objective function, which aims to minimize the total file size of songs stored locally.**

```sql
SELECT song_id, file_size, is_stored_locally FROM files JOIN decision_variables ON files.song_id = decision_variables.song_id;
```

**Error:** Execution failed on sql 'SELECT song_id, file_size, is_stored_locally FROM files JOIN decision_variables ON files.song_id = decision_variables.song_id;': ambiguous column name: is_stored_locally

**Query 2: This is needed to enforce the constraint that ensures a substantial library for users.**

```sql
SELECT constraint_value AS min_total_songs FROM constraints WHERE constraint_type = 'min_total_songs';
```

**Results (CSV format):**
```csv
min_total_songs
```

**Query 3: This is needed to enforce the constraint that maintains high-quality content.**

```sql
SELECT constraint_value AS min_avg_rating FROM constraints WHERE constraint_type = 'min_avg_rating';
```

**Results (CSV format):**
```csv
min_avg_rating
```

**Query 4: This is needed to enforce the constraint that prevents overrepresentation of any single artist.**

```sql
SELECT constraint_value AS max_songs_per_artist FROM constraints WHERE constraint_type = 'max_songs_per_artist';
```

**Results (CSV format):**
```csv
max_songs_per_artist
```

**Query 5: This is needed to enforce the constraint that ensures a diverse music library across different genres.**

```sql
SELECT constraint_value AS min_songs_per_genre FROM constraints WHERE constraint_type = 'min_songs_per_genre';
```

**Results (CSV format):**
```csv
min_songs_per_genre
```

**Query 6: This helps in verifying the current state against the minimum total songs constraint.**

```sql
SELECT COUNT(*) AS total_songs_stored FROM decision_variables WHERE is_stored_locally = TRUE;
```

**Results (CSV format):**
```csv
total_songs_stored
1
```

**Query 7: This helps in verifying the current state against the minimum average rating constraint.**

```sql
SELECT AVG(rating) AS avg_rating_stored FROM songs JOIN decision_variables ON songs.song_id = decision_variables.song_id WHERE is_stored_locally = TRUE;
```

**Error:** Execution failed on sql 'SELECT AVG(rating) AS avg_rating_stored FROM songs JOIN decision_variables ON songs.song_id = decision_variables.song_id WHERE is_stored_locally = TRUE;': no such table: songs

**Query 8: This helps in verifying the current state against the maximum songs per artist constraint.**

```sql
SELECT artist_id, COUNT(*) AS songs_stored_per_artist FROM songs JOIN decision_variables ON songs.song_id = decision_variables.song_id WHERE is_stored_locally = TRUE GROUP BY artist_id;
```

**Error:** Execution failed on sql 'SELECT artist_id, COUNT(*) AS songs_stored_per_artist FROM songs JOIN decision_variables ON songs.song_id = decision_variables.song_id WHERE is_stored_locally = TRUE GROUP BY artist_id;': no such table: songs

**Query 9: This helps in verifying the current state against the minimum songs per genre constraint.**

```sql
SELECT genre, COUNT(*) AS songs_stored_per_genre FROM songs JOIN decision_variables ON songs.song_id = decision_variables.song_id WHERE is_stored_locally = TRUE GROUP BY genre;
```

**Error:** Execution failed on sql 'SELECT genre, COUNT(*) AS songs_stored_per_genre FROM songs JOIN decision_variables ON songs.song_id = decision_variables.song_id WHERE is_stored_locally = TRUE GROUP BY genre;': no such table: songs

**Query 10: This is useful for ensuring that the selected songs meet the minimum average rating and genre diversity constraints.**

```sql
SELECT song_id, rating, genre FROM songs;
```

**Error:** Execution failed on sql 'SELECT song_id, rating, genre FROM songs;': no such table: songs

**Query 11: This is useful for ensuring that the selected songs do not exceed the maximum songs per artist constraint.**

```sql
SELECT artist_id, song_id FROM songs;
```

**Error:** Execution failed on sql 'SELECT artist_id, song_id FROM songs;': no such table: songs

**Query 12: This is useful for ensuring that the selected songs meet the minimum songs per genre constraint.**

```sql
SELECT genre, song_id FROM songs;
```

**Error:** Execution failed on sql 'SELECT genre, song_id FROM songs;': no such table: songs

