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

## 1. Problem Context and Goals

### Context  
The film festival aims to maximize audience engagement by selecting a subset of artworks to nominate for each festival. The decision to nominate an artwork is binary, meaning each artwork can either be nominated or not for a specific festival. The engagement score for each artwork at a festival reflects its potential to attract and engage the audience. The festival must also adhere to two key operational constraints:  
1. **Maximum Nominations per Festival**: Each festival has a predefined limit on the number of artworks that can be nominated.  
2. **Minimum Diversity Requirement**: The nominated artworks must collectively meet a minimum diversity score, ensuring a balanced representation of different artwork types.  

The business configuration includes a scalar parameter, the minimum required diversity score, which is set to 0.75. This ensures that the selection of artworks maintains a moderate level of diversity while maximizing audience engagement.  

### Goals  
The primary goal of this optimization problem is to maximize the total audience engagement across all festivals. This is achieved by selecting the optimal set of artworks to nominate, considering their engagement scores and adhering to the operational constraints. Success is measured by the sum of engagement scores for the nominated artworks, ensuring that the festival’s audience engagement is as high as possible while respecting the nomination limits and diversity requirements.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Maximum Nominations per Festival**: For each festival, the total number of nominated artworks cannot exceed the predefined maximum number of nominations allowed for that festival.  
2. **Minimum Diversity Requirement**: For each festival, the combined diversity score of the nominated artworks must meet or exceed the minimum required diversity score of 0.75. This ensures a balanced selection of artwork types.  

These constraints are designed to ensure that the festival’s operational limits and diversity goals are met while maximizing audience engagement.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding a minimum diversity score constraint to the business configuration logic and updating the data dictionary to reflect this addition. No new tables were created or deleted as the existing schema adequately supports the optimization requirements.

CREATE TABLE engagement_scores (
  Artwork_ID INTEGER,
  Festival_ID INTEGER,
  score FLOAT
);

CREATE TABLE festival_nominations (
  Festival_ID INTEGER,
  max_nominations INTEGER
);

CREATE TABLE artwork_types (
  Type STRING,
  diversity_score FLOAT
);

CREATE TABLE nomination_decisions (
  Artwork_ID INTEGER,
  Festival_ID INTEGER,
  decision BOOLEAN
);
```

### Data Dictionary  
- **engagement_scores**: Contains the engagement scores for artworks at specific festivals.  
  - *Artwork_ID*: Unique identifier for the artwork.  
  - *Festival_ID*: Unique identifier for the festival.  
  - *score*: Engagement score for the artwork at the festival, used as the coefficient in the optimization objective.  

- **festival_nominations**: Defines the maximum number of nominations allowed per festival.  
  - *Festival_ID*: Unique identifier for the festival.  
  - *max_nominations*: Maximum number of nominations allowed for the festival, used as a constraint bound.  

- **artwork_types**: Lists the types of artworks and their associated diversity scores.  
  - *Type*: Type of artwork (e.g., Film, Short, Documentary).  
  - *diversity_score*: Diversity score for the artwork type, used in the diversity constraint.  

- **nomination_decisions**: Represents the binary decision variable indicating whether an artwork is nominated at a festival.  
  - *Artwork_ID*: Unique identifier for the artwork.  
  - *Festival_ID*: Unique identifier for the festival.  
  - *decision*: Binary decision (True/False) indicating whether the artwork is nominated.  


=== Schema ===
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding a minimum diversity score constraint to the business configuration logic and updating the data dictionary to reflect this addition. No new tables were created or deleted as the existing schema adequately supports the optimization requirements.

CREATE TABLE engagement_scores (
  Artwork_ID INTEGER,
  Festival_ID INTEGER,
  score FLOAT
);

CREATE TABLE festival_nominations (
  Festival_ID INTEGER,
  max_nominations INTEGER
);

CREATE TABLE artwork_types (
  Type STRING,
  diversity_score FLOAT
);

CREATE TABLE nomination_decisions (
  Artwork_ID INTEGER,
  Festival_ID INTEGER,
  decision BOOLEAN
);



=== LLM Response ===
```sql
-- Query Description: Retrieve engagement scores for all artworks at each festival. 
-- This data is crucial for the objective function, as it represents the coefficients to maximize.
SELECT Artwork_ID, Festival_ID, score 
FROM engagement_scores;

-- Query Description: Retrieve the maximum number of nominations allowed per festival. 
-- This data is essential for the constraint on the number of nominations per festival.
SELECT Festival_ID, max_nominations 
FROM festival_nominations;

-- Query Description: Retrieve diversity scores for each artwork type. 
-- This data is necessary for calculating the diversity constraint.
SELECT Type, diversity_score 
FROM artwork_types;

-- Query Description: Retrieve the current nomination decisions for artworks at festivals. 
-- This data represents the decision variables in the optimization problem.
SELECT Artwork_ID, Festival_ID, decision 
FROM nomination_decisions;

-- Query Description: Retrieve aggregated engagement scores per festival. 
-- This summary data helps in understanding the total potential engagement per festival.
SELECT Festival_ID, SUM(score) AS total_engagement 
FROM engagement_scores 
GROUP BY Festival_ID;

-- Query Description: Retrieve the count of nominated artworks per festival. 
-- This data is useful for ensuring the nomination limit constraint is not exceeded.
SELECT Festival_ID, COUNT(Artwork_ID) AS num_nominations 
FROM nomination_decisions 
WHERE decision = TRUE 
GROUP BY Festival_ID;

-- Query Description: Retrieve the average diversity score of nominated artworks per festival. 
-- This data helps in verifying the diversity constraint.
SELECT nd.Festival_ID, AVG(at.diversity_score) AS avg_diversity_score 
FROM nomination_decisions nd 
JOIN artwork_types at ON nd.Artwork_ID = at.Type 
WHERE nd.decision = TRUE 
GROUP BY nd.Festival_ID;

-- Query Description: Retrieve artworks with their engagement scores and diversity scores. 
-- This combined data is useful for evaluating both engagement and diversity simultaneously.
SELECT es.Artwork_ID, es.Festival_ID, es.score, at.diversity_score 
FROM engagement_scores es 
JOIN artwork_types at ON es.Artwork_ID = at.Type;

-- Query Description: Retrieve festivals with their maximum nominations and current nominations. 
-- This data helps in monitoring the nomination limit constraint.
SELECT fn.Festival_ID, fn.max_nominations, COUNT(nd.Artwork_ID) AS current_nominations 
FROM festival_nominations fn 
LEFT JOIN nomination_decisions nd ON fn.Festival_ID = nd.Festival_ID AND nd.decision = TRUE 
GROUP BY fn.Festival_ID, fn.max_nominations;

-- Query Description: Retrieve festivals where the current nominations exceed the maximum allowed. 
-- This data is critical for identifying constraint violations.
SELECT fn.Festival_ID, fn.max_nominations, COUNT(nd.Artwork_ID) AS current_nominations 
FROM festival_nominations fn 
JOIN nomination_decisions nd ON fn.Festival_ID = nd.Festival_ID AND nd.decision = TRUE 
GROUP BY fn.Festival_ID, fn.max_nominations 
HAVING COUNT(nd.Artwork_ID) > fn.max_nominations;

-- Query Description: Retrieve festivals where the average diversity score of nominated artworks is below the minimum required. 
-- This data helps in identifying diversity constraint violations.
SELECT nd.Festival_ID, AVG(at.diversity_score) AS avg_diversity_score 
FROM nomination_decisions nd 
JOIN artwork_types at ON nd.Artwork_ID = at.Type 
WHERE nd.decision = TRUE 
GROUP BY nd.Festival_ID 
HAVING AVG(at.diversity_score) < 0.75;
```

These queries are designed to retrieve the necessary data for the optimization problem, including the decision variables, objective function coefficients, and constraint parameters. They also provide aggregated and summary data to help monitor and enforce the constraints effectively.
