# Complete Optimization Problem and Solution: tvshow

## 1. Problem Context and Goals

### Context  
A TV network is tasked with maximizing viewer engagement by strategically allocating airtime to its series and cartoons. The network must decide how many episodes of each show to air, considering the show's viewer ratings, the channel's total available airtime, and the need for content diversity. Each show has a minimum and maximum number of episodes that must be aired to ensure visibility and avoid over-saturation. Additionally, the network must maintain a minimum level of diversity across the aired shows to cater to a broad audience. The total airtime allocated to all shows combined cannot exceed the channel's capacity, which is set at 1200 units. The network aims to balance these factors to achieve the highest possible viewer ratings while adhering to operational constraints.

### Goals  
The primary goal is to maximize the total viewer ratings across all aired episodes. This is achieved by determining the optimal number of episodes to air for each show, weighted by its individual rating. Success is measured by the sum of the ratings of all aired episodes, ensuring that the network's programming appeals to the largest possible audience. The decision-making process is guided by linear relationships, ensuring that the solution is both practical and efficient.

## 2. Constraints    

1. **Total Airtime Constraint**: The combined airtime of all aired episodes must not exceed the channel's total available airtime of 1200 units. This ensures that the network operates within its capacity.  
2. **Episode Bounds Constraint**: Each show must air a minimum of 1 episode and a maximum of 10 episodes. This ensures that no show is overlooked or overexposed.  
3. **Diversity Constraint**: The total diversity score of all aired shows must meet or exceed the minimum required diversity score of 50. This ensures a balanced mix of content that appeals to a wide audience.  

These constraints are designed to align with linear mathematical relationships, ensuring that the optimization problem remains straightforward and solvable using linear methods.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization requirements, modifying existing tables to better map to optimization variables, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE TV_series (
  Rating FLOAT,
  Min_Episodes INTEGER,
  Max_Episodes INTEGER,
  Episodes_Aired INTEGER
);

CREATE TABLE Cartoon (
  Rating FLOAT,
  Min_Episodes INTEGER,
  Max_Episodes INTEGER,
  Episodes_Aired INTEGER
);

CREATE TABLE Show_Diversity (
  Diversity_Score INTEGER
);


```

### Data Dictionary  
- **TV_series**: Stores information about TV series, including their ratings, minimum and maximum episode limits, and the number of episodes aired.  
  - **Rating**: The viewer rating of the TV series, used to determine its contribution to the total viewer engagement.  
  - **Min_Episodes**: The minimum number of episodes that must be aired for the series to maintain visibility.  
  - **Max_Episodes**: The maximum number of episodes that can be aired to avoid over-saturation.  
  - **Episodes_Aired**: The decision variable representing the number of episodes aired for the series.  

- **Cartoon**: Stores information about cartoons, including their ratings, minimum and maximum episode limits, and the number of episodes aired.  
  - **Rating**: The viewer rating of the cartoon, used to determine its contribution to the total viewer engagement.  
  - **Min_Episodes**: The minimum number of episodes that must be aired for the cartoon to maintain visibility.  
  - **Max_Episodes**: The maximum number of episodes that can be aired to avoid over-saturation.  
  - **Episodes_Aired**: The decision variable representing the number of episodes aired for the cartoon.  

- **Show_Diversity**: Stores diversity scores for shows, ensuring a balanced mix of content.  
  - **Diversity_Score**: The diversity score of the show, used to ensure that the total diversity of aired shows meets the minimum requirement.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical TV network operations, considering viewer ratings, airtime constraints, and content diversity requirements. The data ensures a balanced mix of popular and diverse shows while respecting the channel's capacity.

-- Realistic data for TV_series
INSERT INTO TV_series (Rating, Min_Episodes, Max_Episodes, Episodes_Aired) VALUES (4.8, 2, 8, 5);
INSERT INTO TV_series (Rating, Min_Episodes, Max_Episodes, Episodes_Aired) VALUES (4.3, 1, 6, 3);
INSERT INTO TV_series (Rating, Min_Episodes, Max_Episodes, Episodes_Aired) VALUES (4.6, 3, 7, 4);

-- Realistic data for Cartoon
INSERT INTO Cartoon (Rating, Min_Episodes, Max_Episodes, Episodes_Aired) VALUES (4.5, 1, 5, 3);
INSERT INTO Cartoon (Rating, Min_Episodes, Max_Episodes, Episodes_Aired) VALUES (4.1, 2, 4, 2);
INSERT INTO Cartoon (Rating, Min_Episodes, Max_Episodes, Episodes_Aired) VALUES (4.4, 1, 6, 4);

-- Realistic data for Show_Diversity
INSERT INTO Show_Diversity (Diversity_Score) VALUES (15);
INSERT INTO Show_Diversity (Diversity_Score) VALUES (10);
INSERT INTO Show_Diversity (Diversity_Score) VALUES (20);


```