# Complete Optimization Problem and Solution: railway

## 1. Problem Context and Goals

### Context  
In the railway management system, the primary challenge is to efficiently allocate managers to railways to minimize the total cost of management while ensuring operational effectiveness. Each manager has a specific cost associated with their level, which directly impacts the total management cost. Additionally, each manager has a maximum capacity for the number of railways they can manage, ensuring that no manager is overburdened. The decision to assign a manager to a railway is binary—either a manager is assigned to a railway or they are not. The goal is to make these assignments in a way that minimizes the total cost while ensuring that every railway is managed by at least one manager and no manager exceeds their capacity.

The business configuration includes two key parameters: the cost associated with a manager's level, which is used to calculate the total cost in the objective function, and the maximum number of railways a manager can manage, which is used to enforce capacity constraints. These parameters are critical in ensuring that the optimization model aligns with real-world operational requirements.

### Goals  
The primary goal of this optimization problem is to minimize the total cost of assigning managers to railways. This cost is calculated based on the cost associated with each manager's level and the number of railways they are assigned to manage. Success is measured by achieving the lowest possible total cost while ensuring that all railways are managed and no manager exceeds their capacity. The optimization process will use the current operational data, including manager costs and capacities, to determine the most cost-effective assignment of managers to railways.

## 2. Constraints    

The optimization problem is subject to two key constraints:

1. **Railway Management Constraint**: Each railway must be managed by at least one manager. This ensures that all railways have the necessary oversight and operational support.

2. **Manager Capacity Constraint**: No manager can be assigned to more railways than their specified capacity. This prevents overburdening managers and ensures that they can effectively manage their assigned railways.

These constraints are designed to ensure that the solution is both operationally feasible and aligned with the business requirements. They are expressed in a way that naturally leads to a linear mathematical formulation, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for manager capacity and manager-railway assignments, modifying the manager table to include cost per level, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE manager (
  manager_id INTEGER,
  cost_per_level INTEGER
);

CREATE TABLE manager_capacity (
  manager_id INTEGER,
  capacity INTEGER
);

CREATE TABLE manager_railway_assignment (
  manager_id INTEGER,
  railway_id INTEGER,
  assignment_status BOOLEAN
);
```

### Data Dictionary  
- **manager**: This table stores information about managers, including their unique identifier and the cost associated with their level. The cost per level is used in the objective function to calculate the total cost of management.
  - **manager_id**: A unique identifier for each manager.
  - **cost_per_level**: The cost associated with a manager's level, used to calculate the total cost in the objective function.

- **manager_capacity**: This table stores the maximum number of railways each manager can manage. This information is used to enforce the capacity constraint in the optimization model.
  - **manager_id**: A unique identifier for each manager.
  - **capacity**: The maximum number of railways a manager can manage, used in the capacity constraint.

- **manager_railway_assignment**: This table stores the binary decision variables indicating whether a manager is assigned to a railway. These variables are used in the optimization model to determine the optimal assignment of managers to railways.
  - **manager_id**: A unique identifier for each manager.
  - **railway_id**: A unique identifier for each railway.
  - **assignment_status**: A binary variable indicating whether a manager is assigned to a railway (true) or not (false).

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical railway management scenarios, ensuring realistic costs, capacities, and assignments that align with business logic and optimization requirements.

-- Realistic data for manager
INSERT INTO manager (manager_id, cost_per_level) VALUES (1, 120);
INSERT INTO manager (manager_id, cost_per_level) VALUES (2, 150);
INSERT INTO manager (manager_id, cost_per_level) VALUES (3, 200);

-- Realistic data for manager_capacity
INSERT INTO manager_capacity (manager_id, capacity) VALUES (1, 4);
INSERT INTO manager_capacity (manager_id, capacity) VALUES (2, 6);
INSERT INTO manager_capacity (manager_id, capacity) VALUES (3, 8);

-- Realistic data for manager_railway_assignment
INSERT INTO manager_railway_assignment (manager_id, railway_id, assignment_status) VALUES (1, 1, True);
INSERT INTO manager_railway_assignment (manager_id, railway_id, assignment_status) VALUES (2, 2, True);
INSERT INTO manager_railway_assignment (manager_id, railway_id, assignment_status) VALUES (3, 3, True);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{m,r} \) be a binary decision variable where:
  - \( x_{m,r} = 1 \) if manager \( m \) is assigned to railway \( r \),
  - \( x_{m,r} = 0 \) otherwise.

#### Objective Function
Minimize the total cost of assigning managers to railways:
\[
\text{Minimize} \quad \sum_{m} \sum_{r} c_m \cdot x_{m,r}
\]
where \( c_m \) is the cost per level of manager \( m \).

#### Constraints
1. **Railway Management Constraint**: Each railway must be managed by at least one manager:
\[
\sum_{m} x_{m,r} \geq 1 \quad \forall r
\]
2. **Manager Capacity Constraint**: No manager can be assigned to more railways than their specified capacity:
\[
\sum_{r} x_{m,r} \leq k_m \quad \forall m
\]
where \( k_m \) is the capacity of manager \( m \).

#### Data Source Verification
- **Objective Function Coefficient \( c_m \)**: Comes from `manager.cost_per_level`.
- **Manager Capacity \( k_m \)**: Comes from `manager_capacity.capacity`.
- **Decision Variable \( x_{m,r} \)**: Represents the assignment status in `manager_railway_assignment.assignment_status`.

This formulation provides a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy Implementation for Railway Management Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def railway_optimization():
    """Optimize manager assignments to railways to minimize total cost."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("railway_management")
    
    # Data from the database
    managers = [1, 2, 3]
    railways = [1, 2, 3]
    
    # Manager costs and capacities
    cost_per_level = {1: 120, 2: 150, 3: 200}
    manager_capacity = {1: 4, 2: 6, 3: 8}
    
    # CRITICAL: Validate array lengths before loops
    assert len(managers) == len(cost_per_level) == len(manager_capacity), "Array length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables: x[m, r] = 1 if manager m is assigned to railway r
    x = model.addVars(managers, railways, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of assigning managers to railways
    model.setObjective(gp.quicksum(cost_per_level[m] * x[m, r] for m in managers for r in railways), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    
    # Railway Management Constraint: Each railway must be managed by at least one manager
    for r in railways:
        model.addConstr(gp.quicksum(x[m, r] for m in managers) >= 1, name=f"railway_{r}_managed")
    
    # Manager Capacity Constraint: No manager can be assigned to more railways than their capacity
    for m in managers:
        model.addConstr(gp.quicksum(x[m, r] for r in railways) <= manager_capacity[m], name=f"manager_{m}_capacity")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for m in managers:
            for r in railways:
                if x[m, r].x > 0.5:
                    print(f"Manager {m} is assigned to Railway {r}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    railway_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 360.0
**Execution Time**: 0.22 seconds
**Reliability**: high
**Analysis**: Gurobipy achieved the optimal solution with the lowest execution time, indicating high efficiency and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def railway_optimization():
    """Optimize manager to railway assignments to minimize total cost."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="railway_management")
    
    # Data from the database
    managers = [1, 2, 3]
    railways = [1, 2, 3]
    
    # Manager costs and capacities
    manager_costs = {1: 120, 2: 150, 3: 200}
    manager_capacities = {1: 4, 2: 6, 3: 8}
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(managers) == len(manager_costs) == len(manager_capacities), "Array length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables: x[m][r] = 1 if manager m is assigned to railway r
    x = {(m, r): mdl.binary_var(name=f"x_{m}_{r}") for m in managers for r in railways}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of assigning managers to railways
    objective = mdl.sum(manager_costs[m] * x[(m, r)] for m in managers for r in railways)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Railway Management Constraint: Each railway must be managed by at least one manager
    for r in railways:
        mdl.add_constraint(mdl.sum(x[(m, r)] for m in managers) >= 1, ctname=f"railway_{r}_managed")
    
    # Manager Capacity Constraint: No manager can be assigned to more railways than their capacity
    for m in managers:
        mdl.add_constraint(mdl.sum(x[(m, r)] for r in railways) <= manager_capacities[m], ctname=f"manager_{m}_capacity")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for m in managers:
            for r in railways:
                if solution.get_value(x[(m, r)]) > 0:
                    print(f"Manager {m} is assigned to Railway {r}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
railway_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 360.0
**Execution Time**: 1.23 seconds
**Reliability**: high
**Analysis**: DOCplex also achieved the optimal solution but with a significantly higher execution time compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def railway_optimization():
    """Optimization model for assigning managers to railways."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Managers data
    managers = [1, 2, 3]
    cost_per_level = {1: 120, 2: 150, 3: 200}
    capacities = {1: 4, 2: 6, 3: 8}
    
    # Railways data
    railways = [1, 2, 3]
    
    # CRITICAL: Validate array lengths before indexing
    assert len(managers) == len(cost_per_level) == len(capacities), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.M = pyo.Set(initialize=managers)  # Set of managers
    model.R = pyo.Set(initialize=railways)  # Set of railways
    
    # 4. PARAMETERS (data containers)
    model.cost = pyo.Param(model.M, initialize=cost_per_level)
    model.capacity = pyo.Param(model.M, initialize=capacities)
    
    # 5. VARIABLES
    # Binary decision variables: x[m, r] = 1 if manager m is assigned to railway r
    model.x = pyo.Var(model.M, model.R, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    # Minimize the total cost of assigning managers to railways
    def obj_rule(model):
        return sum(model.cost[m] * model.x[m, r] for m in model.M for r in model.R)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Railway Management Constraint: Each railway must be managed by at least one manager
    def railway_management_rule(model, r):
        return sum(model.x[m, r] for m in model.M) >= 1
    model.railway_management_constraint = pyo.Constraint(model.R, rule=railway_management_rule)
    
    # Manager Capacity Constraint: No manager can be assigned to more railways than their capacity
    def manager_capacity_rule(model, m):
        return sum(model.x[m, r] for r in model.R) <= model.capacity[m]
    model.manager_capacity_constraint = pyo.Constraint(model.M, rule=manager_capacity_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 m in model.M:
            for r in model.R:
                if pyo.value(model.x[m, r]) > 0.5:  # Only print assignments
                    print(f"Manager {m} is assigned to Railway {r}")
        
    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
if __name__ == "__main__":
    railway_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 360.0
**Execution Time**: 0.87 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with an execution time between Gurobipy and DOCplex, indicating moderate efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 360.00 | 0.22s | N/A | N/A |
| Docplex | OPTIMAL | 360.00 | 1.23s | N/A | N/A |
| Pyomo | OPTIMAL | 360.00 | 0.87s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 360.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its optimal solution and the fastest execution time, indicating superior efficiency and reliability.

### Business Interpretation
**Overall Strategy**: The optimal total cost of assigning managers to railways is $360, ensuring all railways are managed without exceeding any manager's capacity.
**Objective Value Meaning**: The optimal objective value of $360 represents the minimum total cost of assigning managers to railways while satisfying all constraints.
**Resource Allocation Summary**: Managers should be assigned to railways in a way that minimizes total cost, ensuring each railway is managed and no manager exceeds their capacity.
**Implementation Recommendations**: Implement the assignment plan derived from the optimal solution, ensuring all railways are managed and manager capacities are respected. Monitor the execution to ensure adherence to the plan.