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

## 1. Problem Context and Goals

### Context  
A music label is tasked with selecting a subset of singers to promote in order to maximize the total sales of their songs. The label must make these decisions while adhering to two key operational constraints: the total budget available for promotions and the maximum number of singers that can be effectively promoted. 

The decision to promote a singer is represented as a binary choice: either the singer is selected for promotion or they are not. The total sales of songs associated with the selected singers will be used to measure the success of the promotion strategy. The label has a fixed budget of 500,000 units, which limits the total net worth of the singers that can be promoted. Additionally, the label can promote no more than 3 singers at a time to ensure effective resource allocation and promotional impact.

### Goals  
The primary goal of this optimization problem is to maximize the total sales of songs by selecting the optimal set of singers for promotion. Success is measured by the cumulative sales of the songs associated with the selected singers. The label aims to achieve this goal while staying within the constraints of the available budget and the maximum number of singers that can be promoted.

## 2. Constraints  

1. **Budget Constraint**: The total net worth of the singers selected for promotion must not exceed the available budget of 500,000 units. This ensures that the label does not overspend on promotional activities.  
2. **Promotional Capacity Constraint**: The label can promote a maximum of 3 singers at a time. This ensures that the promotional efforts are focused and manageable, avoiding resource overextension.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating the song table to include sales data. Configuration logic updates include adding scalar parameters for Total_Budget and Max_Singers.

CREATE TABLE singer_selection (
  singer_id INTEGER,
  is_selected BOOLEAN
);

CREATE TABLE song (
  song_id INTEGER,
  singer_id INTEGER,
  Sales INTEGER
);
```

### Data Dictionary  
- **singer_selection**:  
  - **Business Purpose**: Represents the selection of singers for promotion.  
  - **Optimization Role**: Decision variables indicating whether a singer is selected.  
  - **Columns**:  
    - **singer_id**: Unique identifier for each singer. Links to the singer being considered for selection.  
    - **is_selected**: Binary indicator of whether the singer is selected for promotion.  

- **song**:  
  - **Business Purpose**: Represents the songs and their sales data.  
  - **Optimization Role**: Provides the sales data used to measure the success of the promotion strategy.  
  - **Columns**:  
    - **song_id**: Unique identifier for each song.  
    - **singer_id**: Identifier for the singer of the song. Links the song to the singer.  
    - **Sales**: Sales of the song. Used as the objective coefficient in the optimization problem.  


=== Schema ===
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating the song table to include sales data. Configuration logic updates include adding scalar parameters for Total_Budget and Max_Singers.

CREATE TABLE singer_selection (
  singer_id INTEGER,
  is_selected BOOLEAN
);

CREATE TABLE song (
  song_id INTEGER,
  singer_id INTEGER,
  Sales INTEGER
);



=== LLM Response ===
```sql
-- Query Description: Retrieve the total sales per singer, which is crucial for the objective function (maximizing total sales)
-- This query aggregates sales data by singer_id to determine the total sales each singer contributes.
SELECT 
    singer_id, 
    SUM(Sales) AS total_sales
FROM 
    song
GROUP BY 
    singer_id;

-- Query Description: Retrieve the current selection status of singers, which is essential for decision variables
-- This query fetches the current selection status of each singer, indicating whether they are already selected for promotion.
SELECT 
    singer_id, 
    is_selected
FROM 
    singer_selection;

-- Query Description: Retrieve the total sales for selected singers, which helps in evaluating the current promotion strategy
-- This query calculates the total sales for singers who are currently selected for promotion.
SELECT 
    ss.singer_id, 
    SUM(s.Sales) AS total_sales
FROM 
    singer_selection ss
JOIN 
    song s ON ss.singer_id = s.singer_id
WHERE 
    ss.is_selected = TRUE
GROUP BY 
    ss.singer_id;

-- Query Description: Retrieve the total number of singers currently selected for promotion, which is important for the promotional capacity constraint
-- This query counts the number of singers currently selected to ensure it does not exceed the maximum allowed (3).
SELECT 
    COUNT(*) AS total_selected_singers
FROM 
    singer_selection
WHERE 
    is_selected = TRUE;

-- Query Description: Retrieve the total net worth of selected singers, which is crucial for the budget constraint
-- This query calculates the total net worth of singers currently selected for promotion to ensure it does not exceed the budget (500,000 units).
-- Note: Assuming net worth is stored in a hypothetical 'singer' table, which is not provided in the schema.
SELECT 
    SUM(net_worth) AS total_net_worth
FROM 
    singer
WHERE 
    singer_id IN (SELECT singer_id FROM singer_selection WHERE is_selected = TRUE);

-- Query Description: Retrieve the top 3 singers with the highest total sales, which could be a starting point for the optimization
-- This query identifies the top 3 singers with the highest total sales, which could be considered for promotion.
SELECT 
    singer_id, 
    SUM(Sales) AS total_sales
FROM 
    song
GROUP BY 
    singer_id
ORDER BY 
    total_sales DESC
LIMIT 3;

-- Query Description: Retrieve the total sales for all singers, which provides a comprehensive view of potential sales
-- This query calculates the total sales for all singers, providing a baseline for comparison.
SELECT 
    singer_id, 
    SUM(Sales) AS total_sales
FROM 
    song
GROUP BY 
    singer_id;

-- Query Description: Retrieve the total sales for singers not currently selected, which helps in identifying potential candidates for promotion
-- This query calculates the total sales for singers who are not currently selected, helping to identify potential candidates for promotion.
SELECT 
    s.singer_id, 
    SUM(s.Sales) AS total_sales
FROM 
    song s
LEFT JOIN 
    singer_selection ss ON s.singer_id = ss.singer_id AND ss.is_selected = TRUE
WHERE 
    ss.singer_id IS NULL
GROUP BY 
    s.singer_id;
```

These queries are designed to retrieve the necessary data for the optimization problem, including decision variables, objective function coefficients, and constraint parameters. They also provide aggregated and summary data to help in making informed decisions about which singers to promote.
