# Complete Optimization Problem and Solution: news_report

## 1. Problem Context and Goals

### Context  
The news agency is focused on optimizing the allocation of its journalists to various events. The primary decision involves determining whether a journalist should cover a specific event, represented by binary decisions for each journalist-event pair. The agency aims to maximize the total attendance at events covered by journalists, which is a linear objective. The operational parameters include the maximum number of events a journalist can cover and the total number of journalists available. These parameters are crucial for setting the constraints in the optimization model. The business configuration specifies that each journalist can cover up to a certain number of events, ensuring they are not overburdened, and reflects the total number of journalists available for assignments. This setup ensures that the problem remains linear, avoiding any nonlinear relationships such as variable products or divisions.

### Goals  
The primary goal of the optimization is to maximize the total attendance at events covered by journalists. This involves maximizing the sum of the expected attendance at each event that a journalist covers. The success of this optimization is measured by the total attendance achieved, aligning with the expected coefficients derived from event attendance data. The objective is clearly defined in linear terms, focusing on maximizing the sum of attendance figures for events covered by journalists.

## 2. Constraints    

The constraints for this optimization problem are straightforward and linear. Each journalist can cover a limited number of events, ensuring they are not overworked. This is represented by a constraint that limits the sum of events assigned to each journalist to the maximum number of events they can cover. Additionally, the total number of journalists available imposes a constraint on the number of journalists that can be assigned to each event. These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring the problem remains within the realm of linear optimization.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing constraint bounds and modifying existing tables to better map decision variables. Configuration logic updates include moving scalar parameters to JSON for better management.

CREATE TABLE event (
  Event_ID INTEGER,
  Event_Attendance INTEGER
);

CREATE TABLE journalist_event_assignment (
  journalist_ID INTEGER,
  Event_ID INTEGER
);
```

### Data Dictionary  
The data dictionary provides a business-oriented view of the tables and columns, mapping them to their purposes and roles in the optimization process. 

- **Event Table**: This table stores information about each event, including the expected number of attendees. The Event_ID serves as a unique identifier for each event, while the Event_Attendance column provides the attendance figures used as coefficients in the optimization objective.

- **Journalist Event Assignment Table**: This table tracks which journalists are assigned to which events. The journalist_ID and Event_ID columns serve as indices for the decision variables, indicating the assignment of journalists to events.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were chosen to reflect a realistic scenario where journalists cover events with varying attendance, ensuring constraints are met and the optimization problem is solvable.

-- Realistic data for event
INSERT INTO event (Event_ID, Event_Attendance) VALUES (1, 150);
INSERT INTO event (Event_ID, Event_Attendance) VALUES (2, 250);
INSERT INTO event (Event_ID, Event_Attendance) VALUES (3, 100);

-- Realistic data for journalist_event_assignment
INSERT INTO journalist_event_assignment (journalist_ID, Event_ID) VALUES (1, 1);
INSERT INTO journalist_event_assignment (journalist_ID, Event_ID) VALUES (1, 2);
INSERT INTO journalist_event_assignment (journalist_ID, Event_ID) VALUES (2, 2);
INSERT INTO journalist_event_assignment (journalist_ID, Event_ID) VALUES (2, 3);
INSERT INTO journalist_event_assignment (journalist_ID, Event_ID) VALUES (3, 1);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary decision variable where \( x_{ij} = 1 \) if journalist \( i \) is assigned to cover event \( j \), and \( x_{ij} = 0 \) otherwise.

#### Objective Function
Maximize the total attendance at events covered by journalists:
\[
\text{Maximize } Z = 150x_{11} + 250x_{12} + 100x_{13} + 250x_{22} + 100x_{23} + 150x_{31}
\]

#### Constraints
1. Each journalist can cover a limited number of events. Assuming each journalist can cover up to 2 events:
   \[
   x_{11} + x_{12} \leq 2 \quad \text{(Journalist 1)}
   \]
   \[
   x_{22} + x_{23} \leq 2 \quad \text{(Journalist 2)}
   \]
   \[
   x_{31} \leq 2 \quad \text{(Journalist 3)}
   \]

2. Each event can be covered by a limited number of journalists. Assuming each event can be covered by up to 2 journalists:
   \[
   x_{11} + x_{31} \leq 2 \quad \text{(Event 1)}
   \]
   \[
   x_{12} + x_{22} \leq 2 \quad \text{(Event 2)}
   \]
   \[
   x_{13} + x_{23} \leq 2 \quad \text{(Event 3)}
   \]

3. Binary constraints for decision variables:
   \[
   x_{ij} \in \{0, 1\} \quad \forall i, j
   \]

Data Source Verification:
- Coefficients for the objective function (attendance figures) are sourced from the `event.Event_Attendance` column.
- Constraints on the number of events a journalist can cover are based on business configuration parameters.
- Constraints on the number of journalists per event are based on business configuration parameters.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_journalist_event_assignment():
    """Optimize journalist assignments to maximize event attendance"""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("news_report")
    
    # Data: Event attendance and journalist-event pairs
    event_attendance = {1: 150, 2: 250, 3: 100}
    journalist_event_pairs = [(1, 1), (1, 2), (2, 2), (2, 3), (3, 1)]
    
    # Validate data lengths
    assert len(event_attendance) == 3, "Event attendance data length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables for journalist-event assignments
    x = model.addVars(journalist_event_pairs, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total attendance at events covered by journalists
    model.setObjective(gp.quicksum(event_attendance[j] * x[i, j] for i, j in journalist_event_pairs), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Constraint: Each journalist can cover up to 2 events
    model.addConstrs((gp.quicksum(x[i, j] for j in event_attendance.keys() if (i, j) in journalist_event_pairs) <= 2 for i in range(1, 4)), name="journalist_event_limit")
    
    # Constraint: Each event can be covered by up to 2 journalists
    model.addConstrs((gp.quicksum(x[i, j] for i in range(1, 4) if (i, j) in journalist_event_pairs) <= 2 for j in event_attendance.keys()), name="event_journalist_limit")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i, j in journalist_event_pairs:
            if x[i, j].x > 1e-6:
                print(f"Journalist {i} covers Event {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_journalist_event_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 900.0
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution with an objective value of 900.0. The execution time was the fastest among the solvers, indicating efficient performance.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_journalist_event_assignment():
    """Optimize the assignment of journalists to events to maximize attendance."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="news_report")
    
    # Data: Event attendance and journalist-event assignments
    event_attendance = [150, 250, 100]  # Attendance for events 1, 2, 3
    journalist_event_pairs = [(1, 1), (1, 2), (2, 2), (2, 3), (3, 1)]
    
    # Validate array lengths to prevent IndexError
    assert len(event_attendance) == 3, "Event attendance array length mismatch"
    
    # Safe range for indexing
    safe_range = range(len(journalist_event_pairs))
    
    # 2. VARIABLES
    # Binary decision variables for journalist-event assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i, j in journalist_event_pairs}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total attendance at events covered by journalists
    objective = mdl.sum(event_attendance[j-1] * x[i, j] for i, j in journalist_event_pairs)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Each journalist can cover up to 2 events
    mdl.add_constraint(x[1, 1] + x[1, 2] <= 2, ctname="journalist_1_limit")
    mdl.add_constraint(x[2, 2] + x[2, 3] <= 2, ctname="journalist_2_limit")
    mdl.add_constraint(x[3, 1] <= 2, ctname="journalist_3_limit")
    
    # Each event can be covered by up to 2 journalists
    mdl.add_constraint(x[1, 1] + x[3, 1] <= 2, ctname="event_1_limit")
    mdl.add_constraint(x[1, 2] + x[2, 2] <= 2, ctname="event_2_limit")
    mdl.add_constraint(x[2, 3] <= 2, ctname="event_3_limit")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i, j in journalist_event_pairs:
            value = solution.get_value(x[i, j])
            if value > 1e-6:
                print(f"x[{i},{j}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_journalist_event_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 900.0
**Execution Time**: 2.16 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution with an objective value of 900.0. The execution time was longer than Gurobipy but still reasonable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def news_report_optimization():
    """Optimize journalist assignments to maximize event attendance"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Event attendance data
    event_attendance = {1: 150, 2: 250, 3: 100}
    # Journalists and events
    journalists = [1, 2, 3]
    events = [1, 2, 3]
    
    # CRITICAL: Validate data lengths before indexing
    assert len(event_attendance) == len(events), "Event data length mismatch"
    
    # 3. SETS
    model.Journalists = pyo.Set(initialize=journalists)
    model.Events = pyo.Set(initialize=events)
    
    # 4. PARAMETERS
    model.EventAttendance = pyo.Param(model.Events, initialize=event_attendance)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.Journalists, model.Events, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.EventAttendance[j] * model.x[i, j] for i in model.Journalists for j in model.Events)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Each journalist can cover up to 2 events
    def journalist_constraint_rule(model, i):
        return sum(model.x[i, j] for j in model.Events) <= 2
    model.journalist_constraint = pyo.Constraint(model.Journalists, rule=journalist_constraint_rule)
    
    # Each event can be covered by up to 2 journalists
    def event_constraint_rule(model, j):
        return sum(model.x[i, j] for i in model.Journalists) <= 2
    model.event_constraint = pyo.Constraint(model.Events, rule=event_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)
    
    # 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("\nAssignment of journalists to events:")
        for i in model.Journalists:
            for j in model.Events:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0.5:  # Only print assignments
                    print(f"Journalist {i} covers Event {j}")
        
    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
news_report_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 1000.0
**Execution Time**: 2.01 seconds
**Reliability**: medium
**Analysis**: Pyomo reported an optimal solution with an objective value of 1000.0, which is inconsistent with the other solvers. This discrepancy suggests potential issues with the model setup or solver configuration.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 900.00 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 900.00 | 2.16s | N/A | N/A |
| Pyomo | OPTIMAL | 1000.00 | 2.01s | N/A | N/A |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Consistent Solvers**: gurobipy, docplex
**Inconsistent Solvers**: pyomo
**Potential Issues**:
- Pyomo might have a different interpretation of constraints or objective function.
- There could be a bug or misconfiguration in the Pyomo model setup.
- Numerical precision or solver-specific settings might have affected Pyomo's results.
**Majority Vote Optimal Value**: 900.0

### Final Recommendation
**Recommended Optimal Value**: 900.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided a consistent and optimal solution with the fastest execution time, indicating reliability and efficiency.

### Business Interpretation
**Overall Strategy**: The optimal solution suggests that the maximum achievable attendance is 900, given the constraints on journalist and event assignments.
**Objective Value Meaning**: The optimal objective value of 900 represents the maximum total attendance at events covered by journalists, optimizing resource allocation.
**Resource Allocation Summary**: Journalists should be assigned to events in a way that maximizes total attendance while respecting the constraints on the number of events each journalist can cover and the number of journalists per event.
**Implementation Recommendations**: Ensure accurate data input and model configuration. Use Gurobipy for solving similar optimization problems due to its reliability and efficiency.