# Complete Optimization Problem and Solution: program_share

## 1. Problem Context and Goals

### Context  
A media company is focused on maximizing its total viewership share across multiple channels by strategically scheduling programs. The company must decide which programs to broadcast on which channels and at what times of day. This decision is guided by two key operational metrics: the share of viewership each program attracts on a specific channel and the overall rating of the channel itself. The goal is to ensure that the scheduling decisions are made in a way that maximizes the combined impact of these metrics.

The company operates under specific business rules:  
1. Each program can be broadcast at most once across all channels and time slots.  
2. Each channel has a maximum number of time slots available for broadcasting, currently set to 5.  
3. Each channel must broadcast a minimum number of local programs, currently set to 2, to comply with regulatory requirements and cater to local audiences.  

These rules ensure that the scheduling process remains balanced and adheres to both operational and regulatory constraints. The decision variables are binary, indicating whether a specific program is scheduled on a particular channel at a given time slot. The objective is to maximize the total viewership share, calculated as the sum of the products of program shares and channel ratings for all scheduled programs.

### Goals  
The primary goal of this optimization problem is to maximize the total viewership share across all channels. This is achieved by selecting the best combination of programs, channels, and time slots, weighted by the program's share of viewership on the channel and the channel's overall rating. Success is measured by the total viewership share generated from the scheduled programs, ensuring that the company's content reaches the largest possible audience while adhering to operational and regulatory constraints.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Program Uniqueness**: Each program can be broadcast at most once across all channels and time slots. This ensures that no program is over-scheduled, maintaining variety in the programming lineup.  
2. **Channel Time Slot Limit**: Each channel can only use a limited number of time slots for broadcasting, currently capped at 5. This ensures that no channel is overburdened with too many programs, maintaining a balanced schedule.  
3. **Local Program Requirement**: Each channel must broadcast a minimum number of local programs, currently set to 2. This ensures compliance with regulatory requirements and caters to the preferences of local audiences.  

These constraints are designed to ensure that the scheduling decisions are both operationally feasible and aligned with the company's strategic and regulatory goals.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding tables for Max_Time_Slots and Min_Local_Programs to address missing optimization requirements. Configuration logic updates include scalar parameters for Max_Time_Slots and Min_Local_Programs.

CREATE TABLE time_slots (
  time_slot_id INTEGER,
  time_of_day STRING
);

CREATE TABLE program_origins (
  program_id INTEGER,
  origin STRING
);

CREATE TABLE broadcast_decisions (
  channel_id INTEGER,
  program_id INTEGER,
  time_slot_id INTEGER,
  x BOOLEAN
);

CREATE TABLE program_shares (
  channel_id INTEGER,
  program_id INTEGER,
  share_in_percent INTEGER
);

CREATE TABLE channel_ratings (
  channel_id INTEGER,
  rating_in_percent INTEGER
);
```

### Data Dictionary  
- **time_slots**: Contains available time slots for broadcasting programs.  
  - `time_slot_id`: Unique identifier for a time slot.  
  - `time_of_day`: Time of day for broadcasting (e.g., Morning, Afternoon, Evening).  

- **program_origins**: Indicates the origin of programs (e.g., local, international).  
  - `program_id`: Unique identifier for a program.  
  - `origin`: Origin of the program (e.g., Local, International).  

- **broadcast_decisions**: Represents binary decisions indicating whether a program is broadcast on a channel at a specific time.  
  - `channel_id`: Unique identifier for a channel.  
  - `program_id`: Unique identifier for a program.  
  - `time_slot_id`: Unique identifier for a time slot.  
  - `x`: Binary decision variable (0 or 1).  

- **program_shares**: Contains the share of viewership for a program on a channel.  
  - `channel_id`: Unique identifier for a channel.  
  - `program_id`: Unique identifier for a program.  
  - `share_in_percent`: Share of viewership for the program on the channel.  

- **channel_ratings**: Contains the rating of a channel.  
  - `channel_id`: Unique identifier for a channel.  
  - `rating_in_percent`: Rating of the channel.  


### Retrieved Values

**Query 1: The result will be used to calculate the total viewership share for each program-channel combination.**

```sql
SELECT ps.channel_id, ps.program_id, ps.share_in_percent, cr.rating_in_percent, (ps.share_in_percent * cr.rating_in_percent) AS viewership_impact FROM program_shares ps JOIN channel_ratings cr ON ps.channel_id = cr.channel_id;
```

**Results (CSV format):**
```csv
channel_id,program_id,share_in_percent,rating_in_percent,viewership_impact
1,1,60,75,4500
2,2,70,85,5950
3,3,50,80,4000
```

**Query 2: The result will be used to ensure that each channel broadcasts the minimum number of local programs.**

```sql
SELECT program_id, origin FROM program_origins;
```

**Results (CSV format):**
```csv
program_id,origin
1,Local
2,International
3,Local
```

**Query 3: The result will be used to ensure that each program is broadcast at most once across all channels and time slots.**

```sql
SELECT channel_id, program_id, time_slot_id, x FROM broadcast_decisions;
```

**Results (CSV format):**
```csv
channel_id,program_id,time_slot_id,x
1,1,1,1
2,2,3,1
3,3,2,1
```

**Query 4: The result will be used to schedule programs within the available time slots.**

```sql
SELECT time_slot_id, time_of_day FROM time_slots;
```

**Results (CSV format):**
```csv
time_slot_id,time_of_day
1,Morning
2,Afternoon
3,Evening
```

**Query 5: The result will be used to ensure that no channel exceeds the maximum number of time slots (currently capped at 5).**

```sql
SELECT channel_id, COUNT(program_id) AS scheduled_programs FROM broadcast_decisions WHERE x = 1 GROUP BY channel_id;
```

**Results (CSV format):**
```csv
channel_id,scheduled_programs
1,1
2,1
3,1
```

**Query 6: The result will be used to ensure that each channel broadcasts the minimum number of local programs (currently set to 2).**

```sql
SELECT bd.channel_id, COUNT(bd.program_id) AS local_programs_scheduled FROM broadcast_decisions bd JOIN program_origins po ON bd.program_id = po.program_id WHERE bd.x = 1 AND po.origin = 'Local' GROUP BY bd.channel_id;
```

**Results (CSV format):**
```csv
channel_id,local_programs_scheduled
1,1
3,1
```

**Query 7: The result will be used to evaluate the current schedule and identify areas for improvement.**

```sql
SELECT bd.channel_id, SUM(ps.share_in_percent * cr.rating_in_percent) AS total_viewership_share FROM broadcast_decisions bd JOIN program_shares ps ON bd.channel_id = ps.channel_id AND bd.program_id = ps.program_id JOIN channel_ratings cr ON bd.channel_id = cr.channel_id WHERE bd.x = 1 GROUP BY bd.channel_id;
```

**Results (CSV format):**
```csv
channel_id,total_viewership_share
1,4500
2,5950
3,4000
```

**Query 8: The result will be used to consider additional programs for scheduling to maximize viewership share.**

```sql
SELECT p.program_id FROM program_origins p LEFT JOIN broadcast_decisions bd ON p.program_id = bd.program_id WHERE bd.x IS NULL OR bd.x = 0;
```

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

