# Complete Optimization Problem and Solution: film_rank

## 1. Problem Context and Goals

### Context  
A film distribution company is focused on maximizing its total gross revenue by strategically distributing films across various markets. The decision-making process involves determining whether to distribute each film in specific markets, represented by binary decisions. The company uses estimated revenue ranges for each film-market pair to guide these decisions. The primary objective is to maximize the total gross revenue, calculated as the sum of the lower revenue estimates for each film-market pair where the film is distributed. This decision-making process is subject to a maximum budget constraint, which limits the total expenditure on film distribution. Additionally, the company employs a preference score to prioritize film-market pairs, calculated based on market popularity and film rating. This approach ensures that the decision-making process remains linear and straightforward, avoiding complex nonlinear relationships.

### Goals  
The primary goal of the optimization process is to maximize the total gross revenue from film distribution. This involves selecting film-market pairs that contribute the most to revenue while adhering to budget constraints. The success of this optimization is measured by the total gross revenue achieved, which is calculated as the sum of the lower revenue estimates for the selected film-market pairs. The objective is clearly defined in linear terms, focusing on maximizing revenue within the given constraints.

## 2. Constraints    

The optimization process is governed by specific constraints that ensure the decision-making remains within practical and financial limits. The total revenue from the selected film-market pairs must not exceed the predefined budget limit. Additionally, each decision variable, representing whether a film is distributed in a market, is binary, meaning a film can either be distributed in a market or not. These constraints are designed to maintain a linear relationship, ensuring the optimization problem remains solvable using linear methods.

## 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 optimization requirements are met, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE film_market_estimation (
  film_id INTEGER,
  market_id INTEGER,
  Low_Estimate FLOAT,
  High_Estimate FLOAT
);

CREATE TABLE film_market_decision (
  film_id INTEGER,
  market_id INTEGER,
  x BOOLEAN
);
```

### Data Dictionary  
The data used in this optimization process is organized into two main tables, each serving a distinct purpose in the decision-making framework:

- **Film Market Estimation Table**: This table provides estimated revenue data for each film-market pair. It includes:
  - **Film ID**: A unique identifier for each film, used to track and manage film-specific data.
  - **Market ID**: A unique identifier for each market, facilitating market-specific analysis and decisions.
  - **Low Estimate**: The estimated lower bound of revenue for each film-market pair, serving as the objective coefficient in the optimization process.
  - **High Estimate**: The estimated upper bound of revenue, used as a reference for potential revenue but not directly in the optimization objective.

- **Film Market Decision Table**: This table records the decision variables for film distribution, indicating whether a film is distributed in a particular market. It includes:
  - **Film ID**: A unique identifier for each film, aligning with the estimation table for consistency.
  - **Market ID**: A unique identifier for each market, ensuring decisions are market-specific.
  - **Decision Variable (x)**: A binary indicator of whether a film is distributed in a market, forming the core decision variable in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical revenue estimates for film distribution in various markets, ensuring a mix of high and low estimates to reflect market variability. Budget limits were set to allow for strategic decision-making without trivializing the optimization problem.

-- Realistic data for film_market_estimation
INSERT INTO film_market_estimation (film_id, market_id, Low_Estimate, High_Estimate) VALUES (1, 101, 12000.0, 18000.0);
INSERT INTO film_market_estimation (film_id, market_id, Low_Estimate, High_Estimate) VALUES (1, 102, 15000.0, 22000.0);
INSERT INTO film_market_estimation (film_id, market_id, Low_Estimate, High_Estimate) VALUES (2, 101, 8000.0, 12000.0);
INSERT INTO film_market_estimation (film_id, market_id, Low_Estimate, High_Estimate) VALUES (2, 103, 20000.0, 30000.0);
INSERT INTO film_market_estimation (film_id, market_id, Low_Estimate, High_Estimate) VALUES (3, 102, 25000.0, 35000.0);

-- Realistic data for film_market_decision
INSERT INTO film_market_decision (film_id, market_id, x) VALUES (1, 101, True);
INSERT INTO film_market_decision (film_id, market_id, x) VALUES (1, 102, False);
INSERT INTO film_market_decision (film_id, market_id, x) VALUES (2, 101, False);
INSERT INTO film_market_decision (film_id, market_id, x) VALUES (2, 103, True);
INSERT INTO film_market_decision (film_id, market_id, x) VALUES (3, 102, True);
```