=== Problem Context ===
# Complete Optimization Problem and Solution: music_4

## 1. Problem Context and Goals

### Context  
A music festival organizer is tasked with curating a lineup of songs that maximizes the total number of weeks these songs have spent on top charts. The organizer must make binary decisions for each song, determining whether it is included in the festival lineup. The primary operational parameter driving this decision is the number of weeks each song has been on top charts, which directly contributes to the festival's overall success metric.  

To ensure a balanced and diverse festival, the organizer must adhere to several business configuration parameters:  
- The festival can include a maximum of 50 songs, ensuring the lineup remains manageable and focused.  
- No single artist can have more than 3 songs in the festival, promoting diversity and preventing overrepresentation of any one artist.  
- The festival must feature at least 10 unique artists, guaranteeing a broad representation of musical talent.  

These constraints are designed to align with realistic operational limitations while maintaining the festival's appeal and manageability. The decision-making process is linear, focusing solely on selecting songs based on their chart performance and ensuring compliance with the established constraints.  

### Goals  
The primary goal of this optimization problem is to maximize the total number of weeks that the selected songs have spent on top charts. This is achieved by summing the weeks on top for each song included in the festival lineup. Success is measured by the cumulative chart performance of the selected songs, ensuring the festival features the most impactful and popular tracks.  

The optimization process is designed to be straightforward and linear, avoiding complex relationships such as variable products or divisions. The focus is on making clear, binary decisions for each song while adhering to the festival's operational constraints.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure the festival lineup is both high-performing and diverse:  
1. **Total Song Capacity**: The total number of songs selected for the festival cannot exceed 50. This ensures the lineup remains manageable and aligns with the festival's capacity.  
2. **Maximum Songs Per Artist**: No single artist can have more than 3 songs included in the festival. This promotes diversity and prevents any one artist from dominating the lineup.  
3. **Minimum Number of Artists**: The festival must feature at least 10 unique artists. This guarantees a broad representation of musical talent and enhances the festival's appeal.  

These constraints are designed to be linear and straightforward, ensuring the optimization problem remains computationally efficient and aligned with the festival's operational goals.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE song_selection (
  x_song BOOLEAN
);

CREATE TABLE volume (
  Weeks_on_Top INTEGER
);
```

### Data Dictionary  
- **song_selection**:  
  - **Business Purpose**: This table contains binary decision variables indicating whether a song is selected for the festival.  
  - **Optimization Role**: Decision variables in the optimization formulation.  
  - **Columns**:  
    - **x_song**: A binary variable (0 or 1) representing whether a song is included in the festival lineup.  

- **volume**:  
  - **Business Purpose**: This table tracks the number of weeks each song has spent on top charts.  
  - **Optimization Role**: Provides coefficients for the objective function.  
  - **Columns**:  
    - **Weeks_on_Top**: The number of weeks a song has been on top charts, used to measure its impact and popularity.  


=== Schema ===
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE song_selection (
  x_song BOOLEAN
);

CREATE TABLE volume (
  Weeks_on_Top INTEGER
);



=== LLM Response ===
```sql
-- Query Description: Retrieve all songs with their corresponding weeks on top charts.
-- This is essential for the objective function, as it provides the data needed to maximize the total number of weeks on top charts.
SELECT s.x_song, v.Weeks_on_Top
FROM song_selection s
JOIN volume v ON s.x_song = v.Weeks_on_Top;

-- Query Description: Count the total number of songs available for selection.
-- This helps in understanding the total pool of songs from which the festival lineup will be curated.
SELECT COUNT(*) AS total_songs
FROM song_selection;

-- Query Description: Retrieve the total number of weeks on top charts for all songs.
-- This provides a baseline for the maximum possible value of the objective function.
SELECT SUM(Weeks_on_Top) AS total_weeks_on_top
FROM volume;

-- Query Description: Retrieve the number of songs per artist.
-- This is crucial for enforcing the constraint that no single artist can have more than 3 songs in the festival.
-- Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.
SELECT artist, COUNT(*) AS songs_per_artist
FROM song_selection
GROUP BY artist;

-- Query Description: Retrieve the total number of unique artists.
-- This is important for ensuring the festival features at least 10 unique artists.
-- Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.
SELECT COUNT(DISTINCT artist) AS unique_artists
FROM song_selection;

-- Query Description: Retrieve the top 50 songs based on weeks on top charts.
-- This helps in identifying the most impactful songs that could be included in the festival lineup, considering the total song capacity constraint.
SELECT s.x_song, v.Weeks_on_Top
FROM song_selection s
JOIN volume v ON s.x_song = v.Weeks_on_Top
ORDER BY v.Weeks_on_Top DESC
LIMIT 50;

-- Query Description: Retrieve the number of songs selected for the festival.
-- This is useful for ensuring the total number of songs does not exceed the festival's capacity of 50.
SELECT COUNT(*) AS selected_songs
FROM song_selection
WHERE x_song = 1;

-- Query Description: Retrieve the number of songs per artist that are selected for the festival.
-- This is important for enforcing the constraint that no single artist can have more than 3 songs in the festival.
-- Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.
SELECT artist, COUNT(*) AS selected_songs_per_artist
FROM song_selection
WHERE x_song = 1
GROUP BY artist;

-- Query Description: Retrieve the number of unique artists whose songs are selected for the festival.
-- This is crucial for ensuring the festival features at least 10 unique artists.
-- Note: This query assumes there is an 'artist' column in the song_selection table, which is not present in the provided schema. If such a column exists, it should be added.
SELECT COUNT(DISTINCT artist) AS selected_unique_artists
FROM song_selection
WHERE x_song = 1;
```

**Note:** The provided schema does not include an 'artist' column in the `song_selection` table, which is essential for enforcing the constraints related to the number of songs per artist and the minimum number of unique artists. If such a column exists or can be added, the queries that involve the 'artist' column will be valid and useful. If not, the schema should be updated to include this information.
