# Complete Optimization Problem and Solution: program_share

## 1. Problem Context and Goals

### Context  
A media company is focused on optimizing the allocation of its programs across various broadcasting channels. The primary decision involves determining which programs should be broadcasted on which channels. This decision is represented by a binary variable, where each combination of channel and program is either selected for broadcasting or not. The objective is to maximize the total share percentage of all broadcasts, which is calculated by summing the share percentages of the selected programs on their respective channels.

The operational parameters include constraints on the maximum share capacity that each channel can handle, ensuring that no channel exceeds its operational limits. Additionally, the availability of programs for broadcasting is considered, with all programs assumed to be available for scheduling. These constraints are crucial for maintaining a linear optimization model, where each decision variable directly influences the objective without involving nonlinear relationships.

The business configuration includes:
- A maximum share capacity for each channel, which serves as a constraint to ensure channels do not exceed their operational limits.
- An indicator of program availability, ensuring that only available programs are considered for broadcasting.

### Goals  
The primary goal of this optimization problem is to maximize the total share percentage of programs broadcasted across all channels. This involves selecting the optimal combination of programs and channels to achieve the highest possible total share. The success of this optimization is measured by the total share percentage, which is calculated by summing the share percentages of the selected programs on their respective channels. The objective is clearly defined in linear terms, focusing on maximizing the sum of the share percentages associated with the decision variables.

## 2. Constraints    

The optimization problem is subject to the following constraints:

- Each channel has a maximum share capacity that it can handle. The total share percentage of programs broadcasted on a channel must not exceed this capacity. This ensures that the channel operates within its limits and maintains quality broadcasting standards.

- Only available programs can be considered for broadcasting. This constraint ensures that the decision variables only include programs that are ready and available for scheduling, aligning with the operational realities of the media company.

These constraints are expressed in linear terms, ensuring that the optimization problem remains within the realm of linear programming.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables, modifying existing tables to ensure all necessary data for constraints are available, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE broadcast_share (
  channel_id INTEGER,
  program_id INTEGER,
  Share_in_percent FLOAT
);

CREATE TABLE decision_variables (
  channel_id INTEGER,
  program_id INTEGER,
  x BOOLEAN
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **broadcast_share**: This table stores the share percentages of programs on various channels. It plays a critical role in the optimization model by providing the coefficients for the objective function. Each entry links a channel and a program with a specific share percentage, which is used to calculate the total share.

  - **channel_id**: Represents the unique identifier for each channel. It is used to link with decision variables and ensure that the share percentages are correctly attributed to the respective channels.
  
  - **program_id**: Represents the unique identifier for each program. It is used to link with decision variables, ensuring that the share percentages are correctly attributed to the respective programs.
  
  - **Share_in_percent**: Indicates the share percentage of a program on a channel. This value serves as a coefficient in the objective function, directly influencing the total share calculation.

- **decision_variables**: This table indicates whether a program is broadcasted on a channel. It contains the binary decision variables that form the core of the optimization model.

  - **channel_id**: Represents the unique identifier for each channel, forming part of the decision variable index.
  
  - **program_id**: Represents the unique identifier for each program, forming part of the decision variable index.
  
  - **x**: A binary variable indicating whether a program is broadcasted on a channel. It is the primary decision variable in the optimization model, determining the selection of programs for broadcasting.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical media company operations, ensuring that channel capacities and program availabilities align with realistic broadcasting scenarios.

-- Realistic data for broadcast_share
INSERT INTO broadcast_share (channel_id, program_id, Share_in_percent) VALUES (1, 101, 12.5);
INSERT INTO broadcast_share (channel_id, program_id, Share_in_percent) VALUES (1, 102, 18.0);
INSERT INTO broadcast_share (channel_id, program_id, Share_in_percent) VALUES (2, 103, 15.0);
INSERT INTO broadcast_share (channel_id, program_id, Share_in_percent) VALUES (2, 101, 10.0);
INSERT INTO broadcast_share (channel_id, program_id, Share_in_percent) VALUES (3, 102, 20.0);

-- Realistic data for decision_variables
INSERT INTO decision_variables (channel_id, program_id, x) VALUES (1, 101, 1);
INSERT INTO decision_variables (channel_id, program_id, x) VALUES (1, 102, 1);
INSERT INTO decision_variables (channel_id, program_id, x) VALUES (2, 103, 1);
INSERT INTO decision_variables (channel_id, program_id, x) VALUES (2, 101, 0);
INSERT INTO decision_variables (channel_id, program_id, x) VALUES (3, 102, 1);
```