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

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{i,j} \): Binary decision variable indicating whether film \( i \) is distributed in market \( j \). \( x_{i,j} = 1 \) if the film is distributed, and \( x_{i,j} = 0 \) otherwise.

#### Objective Function
Maximize the total gross revenue from film distribution:
\[ \text{Maximize } \sum_{(i,j) \in \text{film\_market\_estimation}} \text{Low\_Estimate}_{i,j} \times x_{i,j} \]

Data Source Verification:
- Coefficients for the objective function (\(\text{Low\_Estimate}_{i,j}\)) are sourced from `film_market_estimation.Low_Estimate`.

#### Constraints
1. **Budget Constraint**: The total expenditure on film distribution must not exceed the predefined budget limit. Assume the budget limit is \( B \).
   \[ \sum_{(i,j) \in \text{film\_market\_estimation}} \text{Low\_Estimate}_{i,j} \times x_{i,j} \leq B \]

2. **Binary Decision Variables**: Each decision variable must be binary.
   \[ x_{i,j} \in \{0, 1\} \quad \forall (i,j) \in \text{film\_market\_estimation} \]

Data Source Verification:
- The budget limit \( B \) is a business configuration parameter, not directly provided in the data tables.
- The binary nature of \( x_{i,j} \) is defined in `film_market_decision.x`.

This formulation provides a linear optimization model that can be solved using linear programming techniques, ensuring that the decision-making process is both efficient and effective in maximizing the total gross revenue within the given constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def film_rank_optimization():
    """Optimize film distribution to maximize total gross revenue."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("film_rank")
    
    # Data from the database
    film_market_data = [
        (1, 101, 12000.0),
        (1, 102, 15000.0),
        (2, 101, 8000.0),
        (2, 103, 20000.0),
        (3, 102, 25000.0)
    ]
    
    # Budget limit
    B = 50000.0
    
    # Validate data length
    assert all(len(record) == 3 for record in film_market_data), "Data length mismatch"
    
    # 2. VARIABLES
    # Create a dictionary of binary decision variables
    x = {(film_id, market_id): model.addVar(vtype=GRB.BINARY, name=f"x_{film_id}_{market_id}")
         for film_id, market_id, _ in film_market_data}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total gross revenue
    model.setObjective(gp.quicksum(low_estimate * x[(film_id, market_id)]
                                   for film_id, market_id, low_estimate in film_market_data), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    # Budget constraint
    model.addConstr(gp.quicksum(low_estimate * x[(film_id, market_id)]
                                for film_id, market_id, low_estimate in film_market_data) <= B, name="budget_limit")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for film_id, market_id, _ in film_market_data:
            if x[(film_id, market_id)].x > 1e-6:
                print(f"Film {film_id} distributed in Market {market_id}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
film_rank_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 48000.0
**Execution Time**: 0.41 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with a short execution time, indicating efficient performance.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def film_rank_optimization():
    """Film distribution optimization using DOCPLEX"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="film_rank")
    
    # Data from the problem context
    film_market_data = [
        (1, 101, 12000.0),
        (1, 102, 15000.0),
        (2, 101, 8000.0),
        (2, 103, 20000.0),
        (3, 102, 25000.0)
    ]
    
    # Budget limit
    B = 50000.0  # Example budget limit
    
    # Extracting data into separate lists for safety
    film_ids = [data[0] for data in film_market_data]
    market_ids = [data[1] for data in film_market_data]
    low_estimates = [data[2] for data in film_market_data]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(film_ids) == len(market_ids) == len(low_estimates), "Array length mismatch"
    safe_range = range(len(film_market_data))  # Safe indexing
    
    # 2. VARIABLES
    # Binary decision variables for film distribution
    x = {(film_ids[i], market_ids[i]): mdl.binary_var(name=f"x_{film_ids[i]}_{market_ids[i]}") for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total gross revenue
    objective = mdl.sum(low_estimates[i] * x[(film_ids[i], market_ids[i])] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Budget constraint
    total_revenue = mdl.sum(low_estimates[i] * x[(film_ids[i], market_ids[i])] for i in safe_range)
    mdl.add_constraint(total_revenue <= B, ctname="budget_limit")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            film_id = film_ids[i]
            market_id = market_ids[i]
            value = solution.get_value(x[(film_id, market_id)])
            if value > 1e-6:
                print(f"x[{film_id}, {market_id}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
film_rank_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 48000.0
**Execution Time**: 7.19 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, but with a longer execution time compared to Gurobipy, which may suggest less efficiency in this instance.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def film_rank_optimization():
    """Film distribution optimization using Pyomo"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data based on the provided problem context
    film_market_data = [
        (1, 101, 12000.0),
        (1, 102, 15000.0),
        (2, 101, 8000.0),
        (2, 103, 20000.0),
        (3, 102, 25000.0)
    ]
    
    # Budget limit
    B = 50000.0  # Example budget limit
    
    # Extracting unique film-market pairs
    films = set(film_id for film_id, _, _ in film_market_data)
    markets = set(market_id for _, market_id, _ in film_market_data)
    
    # 3. SETS
    model.F = pyo.Set(initialize=films)
    model.M = pyo.Set(initialize=markets)
    model.FM = pyo.Set(initialize=[(f, m) for f, m, _ in film_market_data])
    
    # 4. PARAMETERS
    low_estimate_dict = {(f, m): low for f, m, low in film_market_data}
    model.Low_Estimate = pyo.Param(model.FM, initialize=low_estimate_dict)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.FM, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.Low_Estimate[f, m] * model.x[f, m] for f, m in model.FM)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    def budget_constraint_rule(model):
        return sum(model.Low_Estimate[f, m] * model.x[f, m] for f, m in model.FM) <= B
    model.budget_constraint = pyo.Constraint(rule=budget_constraint_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nVariable values:")
        for f, m in model.FM:
            x_val = pyo.value(model.x[f, m])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{f},{m}] = {int(x_val)}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Run the optimization
film_rank_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 48000.0
**Execution Time**: 4.01 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, showing reliable performance.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 48000.00 | 0.41s | N/A | N/A |
| Docplex | OPTIMAL | 48000.00 | 7.19s | N/A | N/A |
| Pyomo | OPTIMAL | 48000.00 | 4.01s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 48000.0

### Final Recommendation
**Recommended Optimal Value**: 48000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its efficient execution time and high reliability, making it suitable for quick decision-making processes.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests the maximum achievable gross revenue from film distribution within the budget constraint.
**Objective Value Meaning**: The optimal objective value of 48000.0 represents the maximum gross revenue achievable under the given constraints.
**Resource Allocation Summary**: Resources should be allocated to distribute films in markets that contribute to achieving the optimal revenue.
**Implementation Recommendations**: Implement the distribution strategy as per the decision variables to maximize revenue, ensuring adherence to the budget constraint.