# Complete Optimization Problem and Solution: book_2

## 1. Problem Context and Goals

### Context  
A publishing company is focused on maximizing its revenue by determining the optimal number of issues to publish for each book. The decision-making process involves selecting the number of issues for each book, represented as integer variables. The primary objective is to maximize the total revenue, which is calculated by multiplying the price of each publication by the number of issues published for each book. The company operates under specific constraints: the total number of issues that can be published is capped, and there is a budget limit for publication costs. These constraints ensure that the company's operations remain within feasible production and financial limits. The business configuration includes a maximum number of issues that can be published, which serves as a constraint on total issues, and a total budget available for publication costs, which serves as a constraint on budget. The operational parameters are aligned with these constraints to ensure a linear optimization formulation, avoiding any nonlinear relationships such as variable products or divisions.

### Goals  
The primary goal of the optimization process is to maximize the company's revenue from book publications. This involves determining the optimal number of issues to publish for each book to achieve the highest possible total revenue. The success of this optimization is measured by the total revenue, which is calculated by summing the product of the price of each publication and the number of issues published for each book. The optimization goal is clearly defined in linear terms, focusing on maximizing revenue while adhering to the constraints of total issues and budget.

## 2. Constraints    

The optimization process is subject to two main constraints. First, the total number of issues published across all books must not exceed the maximum number of issues allowed. This constraint ensures that the company's production capacity is not overstretched. Second, the total cost of publishing, calculated by summing the product of the price of each publication and the number of issues published, must not exceed the available budget. This financial constraint ensures that the company operates within its financial means. Both constraints are expressed in linear terms, aligning with the company's operational parameters and business configuration.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding missing constraint bounds and moving scalar parameters to configuration logic. Adjustments ensure all optimization requirements are met and business logic is preserved.

CREATE TABLE book (
  Book_ID INTEGER,
  Issues INTEGER
);

CREATE TABLE publication (
  Publication_ID INTEGER,
  Price FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive overview of the tables and columns used in the optimization process, highlighting their business purposes and roles in the optimization:

- **Book Table**: This table stores information about each book and the number of issues to be published. 
  - **Book_ID**: Serves as a unique identifier for each book, linking it to the decision variable for the number of issues.
  - **Issues**: Represents the number of issues to be published for each book, serving as the decision variable in the optimization process.

- **Publication Table**: This table contains information about each publication and its price.
  - **Publication_ID**: Acts as a unique identifier for each publication, linking it to the price coefficient.
  - **Price**: Indicates the price of each publication, used as a coefficient in the revenue calculation.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical publishing industry standards, ensuring that the number of issues and prices are realistic and align with the given budget and issue constraints.

-- Realistic data for book
INSERT INTO book (Book_ID, Issues) VALUES (1, 15);
INSERT INTO book (Book_ID, Issues) VALUES (2, 25);
INSERT INTO book (Book_ID, Issues) VALUES (3, 10);

-- Realistic data for publication
INSERT INTO publication (Publication_ID, Price) VALUES (1, 18.99);
INSERT INTO publication (Publication_ID, Price) VALUES (2, 22.5);
INSERT INTO publication (Publication_ID, Price) VALUES (3, 14.75);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_1 \) be the number of issues to publish for Book 1.
- Let \( x_2 \) be the number of issues to publish for Book 2.
- Let \( x_3 \) be the number of issues to publish for Book 3.

These decision variables are integers, representing the number of issues to be published for each book.

#### Objective Function
Maximize the total revenue from publishing the books:

\[
\text{Maximize } Z = 18.99x_1 + 22.5x_2 + 14.75x_3
\]

Data Source Verification:
- Coefficients 18.99, 22.5, and 14.75 are from `publication.Price` for Publication_IDs 1, 2, and 3, respectively.

#### Constraints
1. Total number of issues constraint:
   \[
   x_1 + x_2 + x_3 \leq \text{Maximum number of issues allowed}
   \]

   Assume the maximum number of issues allowed is a business configuration parameter, denoted as \( M \).

2. Budget constraint:
   \[
   18.99x_1 + 22.5x_2 + 14.75x_3 \leq \text{Total budget available}
   \]

   Assume the total budget available is a business configuration parameter, denoted as \( B \).

3. Non-negativity and integer constraints:
   \[
   x_1, x_2, x_3 \geq 0 \quad \text{and integers}
   \]

Data Source Verification:
- Coefficients for the budget constraint are from `publication.Price` for Publication_IDs 1, 2, and 3.
- The maximum number of issues allowed and the total budget available are assumed to be provided as business configuration parameters.

This linear mathematical model is immediately solvable given specific values for the maximum number of issues allowed (\( M \)) and the total budget available (\( B \)).

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_book_publication():
    """Optimize the number of issues to publish for each book to maximize revenue."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("book_publication")
    
    # Prices from the publication table
    prices = [18.99, 22.5, 14.75]
    
    # Maximum number of issues allowed and total budget available
    M = 50  # Example value for maximum number of issues
    B = 1000  # Example value for total budget
    
    # Number of books
    n_books = len(prices)
    
    # CRITICAL: Validate array lengths before loops
    assert len(prices) == n_books, "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variables for the number of issues to publish for each book
    issues = model.addVars(n_books, vtype=GRB.INTEGER, name="issues", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total revenue
    model.setObjective(gp.quicksum(prices[i] * issues[i] for i in range(n_books)), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total number of issues constraint
    model.addConstr(gp.quicksum(issues[i] for i in range(n_books)) <= M, name="total_issues_limit")
    
    # Budget constraint
    model.addConstr(gp.quicksum(prices[i] * issues[i] for i in range(n_books)) <= B, name="budget_limit")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_books):
            if issues[i].x > 1e-6:
                print(f"issues[{i}] = {issues[i].x:.0f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_book_publication()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 999.9799999999999
**Execution Time**: 0.21 seconds
**Reliability**: high
**Analysis**: Gurobipy provided an optimal solution with a high degree of precision and the fastest execution time, indicating a reliable result.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_book_publication():
    """Optimize the number of issues to publish for each book to maximize revenue."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="book_publication")
    
    # Prices from the publication table
    prices = [18.99, 22.5, 14.75]
    
    # Maximum number of issues allowed and total budget available
    max_issues = 50  # Example value for maximum number of issues
    total_budget = 1000  # Example value for total budget
    
    # CRITICAL: Validate array lengths to prevent IndexError
    n_books = len(prices)
    safe_range = range(n_books)  # Safe indexing
    
    # 2. VARIABLES
    # Integer variables for the number of issues to publish for each book
    issues = {i: mdl.integer_var(name=f"x_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total revenue
    revenue = mdl.sum(prices[i] * issues[i] for i in safe_range)
    mdl.maximize(revenue)
    
    # 4. CONSTRAINTS
    # Total number of issues constraint
    total_issues = mdl.sum(issues[i] for i in safe_range)
    mdl.add_constraint(total_issues <= max_issues, ctname="total_issues_limit")
    
    # Budget constraint
    total_cost = mdl.sum(prices[i] * issues[i] for i in safe_range)
    mdl.add_constraint(total_cost <= total_budget, ctname="budget_limit")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(issues[i])
            if value > 1e-6:
                print(f"x[{i}] = {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_book_publication()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 999.9799999999999
**Execution Time**: 8.39 seconds
**Reliability**: high
**Analysis**: DOCplex also reached an optimal solution matching Gurobipy's result, albeit with a longer execution time, suggesting reliability in the solution.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def book_optimization():
    """Optimization for maximizing revenue from book publications"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    n_books = 3
    prices = [18.99, 22.5, 14.75]
    max_issues = 50  # Example maximum number of issues allowed
    total_budget = 1000  # Example total budget available
    
    # CRITICAL: Validate array lengths before indexing
    assert len(prices) == n_books, "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.B = pyo.RangeSet(1, n_books)  # 1-based indexing for books
    
    # 4. PARAMETERS (data containers)
    model.price = pyo.Param(model.B, initialize={i+1: prices[i] for i in range(n_books)})
    
    # 5. VARIABLES
    # Integer variables for number of issues
    model.x = pyo.Var(model.B, within=pyo.NonNegativeIntegers)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.price[i] * model.x[i] for i in model.B)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total number of issues constraint
    def total_issues_rule(model):
        return sum(model.x[i] for i in model.B) <= max_issues
    model.total_issues_constraint = pyo.Constraint(rule=total_issues_rule)
    
    # Budget constraint
    def budget_rule(model):
        return sum(model.price[i] * model.x[i] for i in model.B) <= total_budget
    model.budget_constraint = pyo.Constraint(rule=budget_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    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 i in model.B:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{i}] = {x_val:.0f}")
        
    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
book_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 993.49
**Execution Time**: 3.64 seconds
**Reliability**: medium
**Analysis**: Pyomo reached an optimal solution but with a slightly lower objective value, indicating potential differences in solver precision or configuration.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 999.98 | 0.21s | N/A | N/A |
| Docplex | OPTIMAL | 999.98 | 8.39s | N/A | N/A |
| Pyomo | OPTIMAL | 993.49 | 3.64s | N/A | N/A |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Consistent Solvers**: gurobipy, docplex
**Inconsistent Solvers**: pyomo
**Potential Issues**:
- Differences in solver algorithms or precision settings
- Potential rounding or numerical stability issues in Pyomo
**Majority Vote Optimal Value**: 999.9799999999999

### Final Recommendation
**Recommended Optimal Value**: 999.9799999999999
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided the optimal solution with the highest precision and fastest execution time, making it the most reliable choice.

### Business Interpretation
**Overall Strategy**: The optimal solution maximizes revenue within the given constraints, ensuring the best financial outcome for the publishing strategy.
**Objective Value Meaning**: The optimal objective value represents the maximum achievable revenue from publishing the selected number of issues for each book.
**Resource Allocation Summary**: Allocate resources to publish the optimal number of issues for each book as determined by the solver to maximize revenue.
**Implementation Recommendations**: Use Gurobipy to determine the exact number of issues to publish for each book, ensuring alignment with budget and issue constraints.