# Complete Optimization Problem and Solution: local_govt_in_alabama

## 1. Problem Context and Goals

### Context  
The local government in Alabama is organizing a series of community events and aims to optimize the allocation of participants to these events. The primary objective is to maximize the overall satisfaction score derived from participants attending specific events. Each participant has a predefined satisfaction score for attending each event, which reflects their preference or interest level. The decision to assign a participant to an event is represented by a binary choice, indicating whether a participant is assigned to a particular event or not. 

Operationally, the goal is to maximize the total satisfaction score by making these assignments while adhering to the capacity constraints of each event. The capacity constraint is defined by the maximum number of participants that can be accommodated at each event, ensuring that no event exceeds its designated capacity. This constraint is a critical parameter in the optimization model, reflecting the logistical and resource limitations of event management. The business configuration includes these capacity limits, which are scalar parameters determined by typical event sizes and logistical considerations.

### Goals  
The optimization goal is to maximize the total satisfaction score across all events. This involves strategically assigning participants to events in a manner that maximizes the sum of their satisfaction scores. The success of this optimization is measured by the total satisfaction score achieved, which is directly influenced by the satisfaction scores of participants for their assigned events. The objective is to ensure that the assignments lead to the highest possible aggregate satisfaction, reflecting the preferences and interests of the participants while respecting the capacity constraints of each event.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure feasibility and adherence to business rules:

- Each event has a maximum capacity, which limits the number of participants that can be assigned to it. This constraint ensures that the total number of participants assigned to any event does not exceed its capacity limit.
- The assignment of participants to events is binary, meaning a participant can either be assigned to an event or not. This constraint ensures that each participant-event assignment is a clear yes or no decision, reflecting the binary nature of the decision variables.

These constraints are designed to align with the linear mathematical formulation of the problem, ensuring that the optimization process respects the logistical and operational limitations of event management.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for satisfaction scores and event capacities, modifying existing tables to improve mapping adequacy, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE SatisfactionScores (
  participant_id INTEGER,
  event_id INTEGER,
  satisfaction_score FLOAT
);

CREATE TABLE EventCapacities (
  event_id INTEGER,
  capacity_limit INTEGER
);

CREATE TABLE Participants_in_Events (
  participant_id INTEGER,
  event_id INTEGER,
  participant_event_assignment BOOLEAN
);
```

### Data Dictionary  
The data used in this optimization problem is structured to support the decision-making process:

- **SatisfactionScores**: This table captures the satisfaction scores for each participant-event pair. It serves as the source of coefficients for the objective function, representing the level of satisfaction a participant derives from attending a specific event.
  - **participant_id**: A unique identifier for each participant, used to index satisfaction scores.
  - **event_id**: A unique identifier for each event, used to index satisfaction scores.
  - **satisfaction_score**: The satisfaction score of a participant attending an event, serving as a coefficient in the objective function.

- **EventCapacities**: This table stores the capacity limits for each event, which are used as bounds in the optimization model.
  - **event_id**: A unique identifier for each event, used to index capacity constraints.
  - **capacity_limit**: The maximum number of participants allowed for the event, serving as a constraint bound.

- **Participants_in_Events**: This table tracks the assignments of participants to events, representing the decision variables in the optimization model.
  - **participant_id**: A unique identifier for each participant, used to index decision variables.
  - **event_id**: A unique identifier for each event, used to index decision variables.
  - **participant_event_assignment**: A binary indicator of whether a participant is assigned to an event, representing the decision variable.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical event sizes and participant preferences in community events, ensuring a diverse range of satisfaction scores and capacity limits to reflect realistic scenarios.

-- Realistic data for SatisfactionScores
INSERT INTO SatisfactionScores (participant_id, event_id, satisfaction_score) VALUES (1, 101, 0.7);
INSERT INTO SatisfactionScores (participant_id, event_id, satisfaction_score) VALUES (1, 102, 0.9);
INSERT INTO SatisfactionScores (participant_id, event_id, satisfaction_score) VALUES (2, 101, 0.6);
INSERT INTO SatisfactionScores (participant_id, event_id, satisfaction_score) VALUES (2, 103, 0.8);
INSERT INTO SatisfactionScores (participant_id, event_id, satisfaction_score) VALUES (3, 102, 0.5);

-- Realistic data for EventCapacities
INSERT INTO EventCapacities (event_id, capacity_limit) VALUES (101, 50);
INSERT INTO EventCapacities (event_id, capacity_limit) VALUES (102, 100);
INSERT INTO EventCapacities (event_id, capacity_limit) VALUES (103, 150);

-- Realistic data for Participants_in_Events
INSERT INTO Participants_in_Events (participant_id, event_id, participant_event_assignment) VALUES (1, 101, True);
INSERT INTO Participants_in_Events (participant_id, event_id, participant_event_assignment) VALUES (1, 102, False);
INSERT INTO Participants_in_Events (participant_id, event_id, participant_event_assignment) VALUES (2, 101, True);
INSERT INTO Participants_in_Events (participant_id, event_id, participant_event_assignment) VALUES (2, 103, True);
INSERT INTO Participants_in_Events (participant_id, event_id, participant_event_assignment) VALUES (3, 102, False);
```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Maximize the total satisfaction score:
\[
\text{Maximize } \sum_{(i,j) \in \text{SatisfactionScores}} \text{satisfaction\_score}_{i,j} \times x_{i,j}
\]
Where \(\text{satisfaction\_score}_{i,j}\) is the satisfaction score of participant \( i \) attending event \( j \).

#### Constraints
1. **Capacity Constraints**: Ensure that the number of participants assigned to each event does not exceed its capacity.
   \[
   \sum_{i} x_{i,j} \leq \text{capacity\_limit}_j \quad \forall j \in \text{EventCapacities}
   \]
   Where \(\text{capacity\_limit}_j\) is the maximum number of participants allowed for event \( j \).

2. **Binary Assignment Constraints**: Each participant can be assigned to an event or not, represented by binary variables.
   \[
   x_{i,j} \in \{0, 1\} \quad \forall (i,j) \in \text{SatisfactionScores}
   \]

Data Source Verification:
- The coefficients \(\text{satisfaction\_score}_{i,j}\) are sourced from the `SatisfactionScores` table, specifically from the `satisfaction_score` column.
- The capacity limits \(\text{capacity\_limit}_j\) are sourced from the `EventCapacities` table, specifically from the `capacity_limit` column.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_event_assignments():
    """Optimize participant assignments to events to maximize satisfaction scores."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("local_govt_in_alabama")
    
    # Sample data based on the provided schema and data
    satisfaction_scores = {
        (1, 101): 0.7,
        (1, 102): 0.9,
        (2, 101): 0.6,
        (2, 103): 0.8,
        (3, 102): 0.5
    }
    
    event_capacities = {
        101: 50,
        102: 100,
        103: 150
    }
    
    participants = {1, 2, 3}
    events = {101, 102, 103}
    
    # CRITICAL: Validate array lengths before loops
    assert all(isinstance(satisfaction_scores[(i, j)], float) for i in participants for j in events if (i, j) in satisfaction_scores), "Satisfaction scores must be floats"
    assert all(isinstance(event_capacities[j], int) for j in events), "Event capacities must be integers"
    
    # 2. VARIABLES
    # Decision variables: x[i, j] = 1 if participant i is assigned to event j, 0 otherwise
    x = model.addVars(participants, events, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total satisfaction score
    model.setObjective(gp.quicksum(satisfaction_scores[i, j] * x[i, j] for i, j in satisfaction_scores), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Capacity constraints: Ensure no event exceeds its capacity
    model.addConstrs((gp.quicksum(x[i, j] for i in participants) <= event_capacities[j] for j in events), name="capacity_limit")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in participants:
            for j in events:
                if x[i, j].x > 1e-6:
                    print(f"Participant {i} assigned to Event {j} with satisfaction score {satisfaction_scores[i, 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_event_assignments()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3.5
**Execution Time**: 0.96 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the shortest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_event_participation():
    """Optimize participant assignments to events to maximize satisfaction scores."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="local_govt_in_alabama")
    
    # Data from the database (mocked for this example)
    satisfaction_scores = [
        (1, 101, 0.7),
        (1, 102, 0.9),
        (2, 101, 0.6),
        (2, 103, 0.8),
        (3, 102, 0.5)
    ]
    
    event_capacities = {
        101: 50,
        102: 100,
        103: 150
    }
    
    # Extract unique participants and events
    participants = set(participant_id for participant_id, _, _ in satisfaction_scores)
    events = set(event_id for _, event_id, _ in satisfaction_scores)
    
    # CRITICAL: Validate data consistency
    assert all(event_id in event_capacities for _, event_id, _ in satisfaction_scores), "Event ID mismatch"
    
    # 2. VARIABLES
    # Binary decision variables for participant-event assignments
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i, j, _ in satisfaction_scores}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total satisfaction score
    objective = mdl.sum(score * x[i, j] for i, j, score in satisfaction_scores)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Capacity constraints for each event
    for j in events:
        mdl.add_constraint(mdl.sum(x[i, j] for i, _, _ in satisfaction_scores if j == _) <= event_capacities[j], ctname=f"capacity_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i, j, _ in satisfaction_scores:
            if solution.get_value(x[i, j]) > 0.5:  # Binary variable threshold
                print(f"Participant {i} assigned to Event {j}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
optimize_event_participation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3.5
**Execution Time**: 8.65 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, but with a longer execution time compared to Gurobipy, suggesting it is reliable but less efficient.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def local_govt_in_alabama_optimization():
    """Optimize participant assignments to maximize satisfaction scores."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Sample data based on the provided database schema and current stored values
    participants = [1, 2, 3]
    events = [101, 102, 103]
    
    satisfaction_scores = {
        (1, 101): 0.7,
        (1, 102): 0.9,
        (2, 101): 0.6,
        (2, 103): 0.8,
        (3, 102): 0.5
    }
    
    capacity_limits = {
        101: 50,
        102: 100,
        103: 150
    }
    
    # 3. SETS
    model.Participants = pyo.Set(initialize=participants)
    model.Events = pyo.Set(initialize=events)
    
    # 4. PARAMETERS
    model.satisfaction_score = pyo.Param(model.Participants, model.Events, initialize=satisfaction_scores, default=0)
    model.capacity_limit = pyo.Param(model.Events, initialize=capacity_limits)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.Participants, model.Events, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.satisfaction_score[i, j] * model.x[i, j] for i in model.Participants for j in model.Events)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Capacity constraints
    def capacity_rule(model, j):
        return sum(model.x[i, j] for i in model.Participants) <= model.capacity_limit[j]
    model.capacity_constraint = pyo.Constraint(model.Events, rule=capacity_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("\nAssignment of participants to events:")
        for i in model.Participants:
            for j in model.Events:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0.5:  # Only print assignments
                    print(f"Participant {i} assigned to 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
local_govt_in_alabama_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 3.5
**Execution Time**: 4.91 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, indicating a balance between reliability and efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 3.50 | 0.96s | N/A | N/A |
| Docplex | OPTIMAL | 3.50 | 8.65s | N/A | N/A |
| Pyomo | OPTIMAL | 3.50 | 4.91s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 3.5
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its high reliability and the shortest execution time, making it the most efficient choice for this problem.

### Business Interpretation
**Overall Strategy**: The optimal satisfaction score of 3.5 indicates the best possible allocation of participants to events, maximizing overall satisfaction.
**Objective Value Meaning**: The optimal objective value of 3.5 represents the maximum achievable satisfaction score by optimally assigning participants to events.
**Resource Allocation Summary**: Participants should be allocated to events in a manner that maximizes the total satisfaction score while respecting event capacity constraints.
**Implementation Recommendations**: Implement the solution by assigning participants to events as per the optimal decision variables, ensuring adherence to capacity constraints.