Iteration final - PROBLEM_DESCRIPTION
Sequence: 5
Timestamp: 2025-07-27 23:41:39

Prompt:
You are a business analyst creating structured optimization problem documentation.

DATA SOURCES EXPLANATION:
- FINAL OR ANALYSIS: Final converged optimization problem from alternating process (iteration 1), contains business context and schema mapping evaluation
- DATABASE SCHEMA: Current database structure after iterative adjustments  
- DATA DICTIONARY: Business meanings and optimization roles of tables and columns
- CURRENT STORED VALUES: Realistic business data generated by triple expert (business + data + optimization)
- BUSINESS CONFIGURATION: Scalar parameters and business logic formulas separated from table data

CRITICAL REQUIREMENTS: 
- Ensure problem description naturally leads to LINEAR or MIXED-INTEGER optimization formulation
- Make business context consistent with the intended decision variables and objectives
- Align constraint descriptions with expected mathematical constraints
- Ensure data descriptions map clearly to expected coefficient sources
- Maintain business authenticity while fixing mathematical consistency issues
- Avoid business scenarios that would naturally require nonlinear relationships (variable products, divisions, etc.)

AUTO-EXTRACTED CONTEXT REQUIREMENTS:
- Business decisions match expected decision variables: x[channel_id, program_id] is a binary variable indicating if program_id is broadcasted on channel_id
- Operational parameters align with expected linear objective: maximize total_share = ∑(broadcast_share.Share_in_percent * decision_variables.x[channel_id, program_id])
- Business configuration includes: Maximum share capacity a channel can handle (used for Used as a constraint in optimization model), Indicates if a program is available for broadcasting (used for Used as a constraint in optimization model)
- Use natural language to precisely describe linear mathematical relationships
- NO mathematical formulas, equations, or symbolic notation
- Present data as current operational information
- Focus on precise operational decision-making that leads to linear formulations
- Resource limitations match expected linear constraints
- Avoid scenarios requiring variable products, divisions, or other nonlinear relationships
- Include specific operational parameters that map to expected coefficient sources
- Reference business configuration parameters where appropriate

FINAL OR ANALYSIS:
{
  "database_id": "program_share",
  "iteration": 1,
  "business_context": "A media company aims to optimize the allocation of programs to channels to maximize the total share percentage across all broadcasts, considering constraints on channel capacities and program availability.",
  "optimization_problem_description": "The goal is to maximize the total share percentage of programs broadcasted on various channels. The decision involves selecting which programs to broadcast on which channels, subject to constraints on the maximum share a channel can handle and the availability of programs.",
  "optimization_formulation": {
    "objective": "maximize total_share = \u2211(broadcast_share.Share_in_percent * decision_variables.x[channel_id, program_id])",
    "decision_variables": "x[channel_id, program_id] is a binary variable indicating if program_id is broadcasted on channel_id",
    "constraints": [
      "\u2211(broadcast_share.Share_in_percent * decision_variables.x[channel_id, program_id]) \u2264 business_configuration_logic.channel_capacity_limit for each channel_id",
      "decision_variables.x[channel_id, program_id] \u2264 business_configuration_logic.program_availability for each program_id"
    ]
  },
  "current_optimization_to_schema_mapping": {
    "objective_coefficients": {
      "Share_in_percent[channel_id, program_id]": {
        "currently_mapped_to": "broadcast_share.Share_in_percent",
        "mapping_adequacy": "good",
        "description": "Share percentage of the program on the channel"
      }
    },
    "constraint_bounds": {
      "channel_capacity_limit[channel_id]": {
        "currently_mapped_to": "business_configuration_logic.channel_capacity_limit",
        "mapping_adequacy": "good",
        "description": "Maximum share capacity a channel can handle"
      },
      "program_availability[program_id]": {
        "currently_mapped_to": "business_configuration_logic.program_availability",
        "mapping_adequacy": "good",
        "description": "Indicates if a program is available for broadcasting"
      }
    },
    "decision_variables": {
      "x[channel_id, program_id]": {
        "currently_mapped_to": "decision_variables.x",
        "mapping_adequacy": "good",
        "description": "Binary decision variable indicating if a program is broadcasted",
        "variable_type": "binary"
      }
    }
  },
  "missing_optimization_requirements": [],
  "iteration_status": {
    "complete": true,
    "confidence": "high",
    "next_focus": "Ready for convergence"
  }
}

FINAL 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
);


```

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);


```

DATA DICTIONARY:
{
  "tables": {
    "broadcast_share": {
      "business_purpose": "Stores share percentages of programs on channels",
      "optimization_role": "objective_coefficients",
      "columns": {
        "channel_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the channel",
          "optimization_purpose": "Used to link with decision variables",
          "sample_values": "1, 2, 3"
        },
        "program_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the program",
          "optimization_purpose": "Used to link with decision variables",
          "sample_values": "101, 102, 103"
        },
        "Share_in_percent": {
          "data_type": "FLOAT",
          "business_meaning": "Share percentage of the program on the channel",
          "optimization_purpose": "Coefficient in the objective function",
          "sample_values": "10.5, 20.0, 15.0"
        }
      }
    },
    "decision_variables": {
      "business_purpose": "Indicates if a program is broadcasted on a channel",
      "optimization_role": "decision_variables",
      "columns": {
        "channel_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the channel",
          "optimization_purpose": "Part of the decision variable index",
          "sample_values": "1, 2, 3"
        },
        "program_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the program",
          "optimization_purpose": "Part of the decision variable index",
          "sample_values": "101, 102, 103"
        },
        "x": {
          "data_type": "BOOLEAN",
          "business_meaning": "Binary decision variable indicating if a program is broadcasted",
          "optimization_purpose": "Decision variable in the optimization model",
          "sample_values": "0, 1"
        }
      }
    }
  }
}


BUSINESS CONFIGURATION:

BUSINESS CONFIGURATION:
{
  "channel_capacity_limit": {
    "data_type": "INTEGER",
    "business_meaning": "Maximum share capacity a channel can handle",
    "optimization_role": "Used as a constraint in optimization model",
    "configuration_type": "scalar_parameter",
    "value": 50,
    "business_justification": "A realistic capacity limit for channels, ensuring they do not exceed their operational limits."
  },
  "program_availability": {
    "data_type": "BOOLEAN",
    "business_meaning": "Indicates if a program is available for broadcasting",
    "optimization_role": "Used as a constraint in optimization model",
    "configuration_type": "scalar_parameter",
    "value": 1,
    "business_justification": "All programs are assumed to be available for broadcasting, reflecting a typical scenario where programs are ready for scheduling."
  }
}

Business Configuration Design: 
Our system separates business logic design from value determination:
- Configuration Logic (business_configuration_logic.json): Templates designed by data engineers with sample_value for scalars and actual formulas for business logic
- Configuration Values (business_configuration.json): Realistic values determined by domain experts for scalar parameters only
- Design Rationale: Ensures business logic consistency while allowing flexible parameter tuning


TASK: Create structured markdown documentation for SECTIONS 1-3 ONLY (Problem Description).

EXACT MARKDOWN STRUCTURE TO FOLLOW:

# Complete Optimization Problem and Solution: program_share

## 1. Problem Context and Goals

### Context  
[Regenerate business context that naturally aligns with LINEAR optimization formulation. Ensure:]
- Business decisions match expected decision variables: x[channel_id, program_id] is a binary variable indicating if program_id is broadcasted on channel_id
- Operational parameters align with expected linear objective: maximize total_share = ∑(broadcast_share.Share_in_percent * decision_variables.x[channel_id, program_id])
- Business configuration includes: Maximum share capacity a channel can handle (used for Used as a constraint in optimization model), Indicates if a program is available for broadcasting (used for Used as a constraint in optimization model)
- Use natural language to precisely describe linear mathematical relationships
- NO mathematical formulas, equations, or symbolic notation
- Present data as current operational information
- Focus on precise operational decision-making that leads to linear formulations
- Resource limitations match expected linear constraints
- Avoid scenarios requiring variable products, divisions, or other nonlinear relationships
- Include specific operational parameters that map to expected coefficient sources
- Reference business configuration parameters where appropriate
- CRITICAL: Include ALL business configuration information (scalar parameters AND business logic formulas) in natural business language

### Goals  
[Regenerate goals that clearly lead to LINEAR mathematical objective:]
- Optimization goal: maximize
- Metric to optimize: maximize total_share = ∑(broadcast_share.Share_in_percent * decision_variables.x[channel_id, program_id])
- Success measurement aligned with expected coefficient sources
- Use natural language to precisely describe linear optimization goal
- NO mathematical formulas, equations, or symbolic notation

## 2. Constraints    

[Regenerate constraints that directly match expected LINEAR mathematical constraints:]
- Expected constraint: ['∑(broadcast_share.Share_in_percent * decision_variables.x[channel_id, program_id]) ≤ business_configuration_logic.channel_capacity_limit for each channel_id', 'decision_variables.x[channel_id, program_id] ≤ business_configuration_logic.program_availability for each program_id'] (Form: Standard constraint form based on business requirements)

[Each constraint should be described in business terms that naturally lead to LINEAR mathematical forms (no variable products or divisions)]

## 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  
[Create comprehensive business-oriented data dictionary mapping tables and columns to their business purposes and optimization roles - NOT technical database terms. Base this on the data_dictionary provided but rewrite in business language that clearly connects to the expected linear mathematical formulation]

### 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);


```

STOP HERE - Do not include section 4 (Mathematical Optimization Formulation).

Response:
# 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);
```
