# Complete Optimization Problem and Solution: film_rank

## 1. Problem Context and Goals

### Context  
A film studio is focused on maximizing its total gross revenue by strategically allocating its films to different markets. The studio must decide the proportion of each film to allocate to each market based on average revenue estimates. The average revenue for each film in each market is calculated as the midpoint between the low and high revenue estimates, ensuring a balanced and realistic projection. The studio has set a business rule that the total allocation for any single film across all markets cannot exceed 100%, ensuring feasible and practical distribution. This decision-making process is designed to be linear, avoiding complex relationships such as variable products or divisions, and relies on straightforward proportional allocations and revenue estimates.

### Goals  
The primary goal of the optimization is to maximize the studio's total gross revenue. This is achieved by determining the optimal proportion of each film to allocate to each market, weighted by the average revenue estimate for that film-market pair. Success is measured by the total revenue generated from these allocations, ensuring that the studio's resources are used efficiently and effectively. The optimization process is designed to be linear, focusing on proportional allocations and revenue estimates without introducing nonlinear complexities.

## 2. Constraints    

The optimization problem is subject to two key constraints. First, the total allocation for each film across all markets must not exceed 100%. This ensures that the studio does not overcommit its films to multiple markets beyond their availability. Second, the allocation proportion for each film in each market must be non-negative, ensuring that the allocations are realistic and feasible. These constraints are designed to be linear, avoiding any nonlinear relationships and ensuring that the problem remains straightforward and solvable using linear optimization techniques.

## 3. Available Data  

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

CREATE TABLE film_market_allocation (
  allocation_proportion FLOAT
);

CREATE TABLE film_market_revenue_estimate (
  low_estimate FLOAT,
  high_estimate FLOAT,
  average_revenue FLOAT
);
```

### Data Dictionary  
- **film_market_allocation**: This table tracks the proportion of each film allocated to each market.  
  - **allocation_proportion**: Represents the proportion of a film allocated to a specific market. This is a continuous value ranging from 0 to 1, where 0 means no allocation and 1 means full allocation. It serves as the decision variable in the optimization problem.  

- **film_market_revenue_estimate**: This table contains revenue estimates for each film in each market.  
  - **low_estimate**: The minimum expected revenue for a film in a specific market.  
  - **high_estimate**: The maximum expected revenue for a film in a specific market.  
  - **average_revenue**: The midpoint between the low and high estimates, representing the average expected revenue. This value is used as the coefficient in the optimization objective to maximize total revenue.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for film revenue estimates and allocation proportions, ensuring they are realistic and align with the business context of maximizing gross revenue.

-- Realistic data for film_market_allocation
INSERT INTO film_market_allocation (allocation_proportion) VALUES (0.3);
INSERT INTO film_market_allocation (allocation_proportion) VALUES (0.5);
INSERT INTO film_market_allocation (allocation_proportion) VALUES (0.2);

-- Realistic data for film_market_revenue_estimate
INSERT INTO film_market_revenue_estimate (low_estimate, high_estimate, average_revenue) VALUES (200000.0, 600000.0, 400000.0);
INSERT INTO film_market_revenue_estimate (low_estimate, high_estimate, average_revenue) VALUES (300000.0, 800000.0, 550000.0);
INSERT INTO film_market_revenue_estimate (low_estimate, high_estimate, average_revenue) VALUES (100000.0, 400000.0, 250000.0);
INSERT INTO film_market_revenue_estimate (low_estimate, high_estimate, average_revenue) VALUES (250000.0, 700000.0, 475000.0);
INSERT INTO film_market_revenue_estimate (low_estimate, high_estimate, average_revenue) VALUES (150000.0, 500000.0, 325000.0);
```