# Complete Optimization Problem and Solution: bike_1

## 1. Problem Context and Goals

### Context  
In the urban bike-sharing system, the primary business decision involves determining the optimal allocation of bikes across various stations to ensure efficient operation. The decision variables are the number of bikes in shortage and excess at each station. The operational parameters are designed to align with the objective of minimizing the total penalty costs associated with bike shortages and excesses. The business configuration includes a penalty cost for each bike shortage at a station, which is used to minimize shortage costs, and a penalty cost for each excess bike at a station, which is used to minimize excess costs. Additionally, the total number of bikes available in the system serves as a constraint bound in the optimization model. The relationships between these elements are linear, ensuring that the optimization problem can be solved using linear programming techniques. The data presented reflects current operational information, focusing on precise decision-making that leads to linear formulations. Resource limitations are expressed through linear constraints, avoiding scenarios that require nonlinear relationships. Specific operational parameters are mapped to expected coefficient sources, with business configuration parameters referenced appropriately.

### Goals  
The goal of the optimization is to minimize the total penalty costs associated with bike shortages and excesses across all stations. This is achieved by optimizing the metric that sums the product of the shortage penalty and the number of bikes in shortage, and the product of the excess penalty and the number of bikes in excess. Success is measured by the reduction in these penalty costs, aligning with the expected coefficient sources. The optimization goal is described in natural language, focusing on minimizing the total penalty costs without using mathematical formulas or symbolic notation.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that the allocation of bikes adheres to the system's operational limits. The total number of bikes allocated as shortages and excesses across all stations must equal the total number of bikes available in the system. Each station must have a non-negative number of bikes in shortage and excess, ensuring that no station reports a negative value for these variables. Additionally, the sum of the number of bikes in shortage and the expected demand at each station must not exceed the station's dock capacity. These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for penalty costs and expected demand, modifying existing tables to include missing data, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE station (
  station_id INTEGER,
  dock_count INTEGER,
  shortage FLOAT,
  excess FLOAT
);

CREATE TABLE expected_demand (
  station_id INTEGER,
  expected_demand INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles. The "station" table stores information about each bike station, with columns for the station ID, dock count, shortage, and excess. The station ID uniquely identifies each station and links to optimization processes. The dock count represents the maximum number of bikes that can be docked at the station, serving as a constraint bound for bike allocation. The shortage and excess columns represent the decision variables for the number of bikes short or in excess at each station. The "expected_demand" table stores the expected demand for bikes at each station, with columns for the station ID and expected demand. The expected demand is used to forecast demand in the optimization process, linking demand data to stations.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical urban bike-sharing systems, considering average station sizes, expected demand patterns, and penalty costs that reflect operational priorities.

-- Realistic data for station
INSERT INTO station (station_id, dock_count, shortage, excess) VALUES (1, 15, 0, 2);
INSERT INTO station (station_id, dock_count, shortage, excess) VALUES (2, 20, 1, 0);
INSERT INTO station (station_id, dock_count, shortage, excess) VALUES (3, 10, 0, 1);

-- Realistic data for expected_demand
INSERT INTO expected_demand (station_id, expected_demand) VALUES (1, 12);
INSERT INTO expected_demand (station_id, expected_demand) VALUES (2, 18);
INSERT INTO expected_demand (station_id, expected_demand) VALUES (3, 8);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( s_i \): Number of bikes in shortage at station \( i \).
- \( e_i \): Number of bikes in excess at station \( i \).

#### Objective Function
Minimize the total penalty costs associated with bike shortages and excesses across all stations:

\[
\text{Minimize } Z = \sum_{i} \left( \text{shortage\_penalty}_i \times s_i + \text{excess\_penalty}_i \times e_i \right)
\]

Where:
- \(\text{shortage\_penalty}_i\) and \(\text{excess\_penalty}_i\) are the penalty costs per bike in shortage and excess at station \( i \), respectively. These values are assumed to be provided as part of the business configuration parameters.

#### Constraints
1. **Total Bikes Constraint**: The total number of bikes in shortage and excess must equal the total number of bikes available in the system.
   \[
   \sum_{i} (s_i + e_i) = \text{Total\_Bikes}
   \]

2. **Non-negativity Constraints**: The number of bikes in shortage and excess at each station must be non-negative.
   \[
   s_i \geq 0, \quad e_i \geq 0 \quad \forall i
   \]

3. **Dock Capacity Constraints**: The sum of the number of bikes in shortage and the expected demand at each station must not exceed the station's dock capacity.
   \[
   s_i + \text{expected\_demand}_i \leq \text{dock\_count}_i \quad \forall i
   \]

Data Source Verification:
- \(\text{dock\_count}_i\) is sourced from `station.dock_count`.
- \(\text{expected\_demand}_i\) is sourced from `expected_demand.expected_demand`.
- \(\text{Total\_Bikes}\) is a parameter representing the total number of bikes available in the system, assumed to be provided as part of the business configuration.
- \(\text{shortage\_penalty}_i\) and \(\text{excess\_penalty}_i\) are assumed to be provided as part of the business configuration parameters.

This linear model can be solved using standard linear programming techniques to determine the optimal allocation of bikes across stations, minimizing the total penalty costs associated with shortages and excesses.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def bike_optimization():
    """Optimize bike allocation across stations to minimize penalty costs."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("bike_optimization")
    
    # Data: Penalty costs, dock counts, expected demands, and total bikes
    shortage_penalty = [5, 4, 6]  # Example penalty costs for shortages
    excess_penalty = [3, 2, 4]    # Example penalty costs for excesses
    dock_counts = [15, 20, 10]
    expected_demands = [12, 18, 8]
    total_bikes = 3  # Total number of bikes in shortage and excess
    
    # Validate array lengths
    n_stations = len(dock_counts)
    assert len(shortage_penalty) == len(excess_penalty) == len(expected_demands) == n_stations, "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variables for shortages and excesses at each station
    shortages = model.addVars(n_stations, vtype=GRB.CONTINUOUS, name="shortage", lb=0)
    excesses = model.addVars(n_stations, vtype=GRB.CONTINUOUS, name="excess", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total penalty costs
    model.setObjective(
        gp.quicksum(shortage_penalty[i] * shortages[i] + excess_penalty[i] * excesses[i] for i in range(n_stations)),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    # Total bikes constraint
    model.addConstr(
        gp.quicksum(shortages[i] + excesses[i] for i in range(n_stations)) == total_bikes,
        name="total_bikes"
    )
    
    # Dock capacity constraints
    model.addConstrs(
        (shortages[i] + expected_demands[i] <= dock_counts[i] for i in range(n_stations)),
        name="dock_capacity"
    )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_stations):
            print(f"Station {i+1}: Shortage = {shortages[i].x:.3f}, Excess = {excesses[i].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
bike_optimization()
```

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

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def bike_optimization():
    """Optimization for bike-sharing system to minimize penalty costs"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="bike_optimization")
    
    # Data: Penalty costs, dock counts, expected demands, and total bikes
    shortage_penalty = [5, 4, 6]  # Example penalty costs for shortages
    excess_penalty = [3, 2, 4]    # Example penalty costs for excesses
    dock_counts = [15, 20, 10]
    expected_demands = [12, 18, 8]
    total_bikes = 3  # Total number of bikes in shortage and excess
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(shortage_penalty) == len(excess_penalty) == len(dock_counts) == len(expected_demands), "Array length mismatch"
    safe_range = range(min(len(shortage_penalty), len(excess_penalty), len(dock_counts), len(expected_demands)))  # Safe indexing
    
    # 2. VARIABLES
    s = {i: mdl.continuous_var(name=f"s_{i}", lb=0) for i in safe_range}
    e = {i: mdl.continuous_var(name=f"e_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(shortage_penalty[i] * s[i] + excess_penalty[i] * e[i] for i in safe_range)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total Bikes Constraint
    total_bikes_constraint = mdl.sum(s[i] + e[i] for i in safe_range)
    mdl.add_constraint(total_bikes_constraint == total_bikes, ctname="total_bikes_constraint")
    
    # Dock Capacity Constraints
    for i in safe_range:
        mdl.add_constraint(s[i] + expected_demands[i] <= dock_counts[i], ctname=f"dock_capacity_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            shortage_value = solution.get_value(s[i])
            excess_value = solution.get_value(e[i])
            print(f"Station {i}: Shortage = {shortage_value:.3f}, Excess = {excess_value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
bike_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 6.0
**Execution Time**: 2.90 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy, but still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def bike_optimization():
    """Bike-sharing system optimization using Pyomo"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    station_ids = [1, 2, 3]
    dock_counts = [15, 20, 10]
    expected_demands = [12, 18, 8]
    shortage_penalties = [5, 4, 6]  # Example penalties for shortages
    excess_penalties = [3, 2, 4]    # Example penalties for excesses
    total_bikes = 3  # Total number of bikes available in the system
    
    # CRITICAL: Validate array lengths before indexing
    assert len(station_ids) == len(dock_counts) == len(expected_demands) == len(shortage_penalties) == len(excess_penalties), "Array length mismatch"
    
    # 3. SETS
    model.I = pyo.Set(initialize=station_ids)
    
    # 4. PARAMETERS
    model.dock_count = pyo.Param(model.I, initialize={station_ids[i]: dock_counts[i] for i in range(len(station_ids))})
    model.expected_demand = pyo.Param(model.I, initialize={station_ids[i]: expected_demands[i] for i in range(len(station_ids))})
    model.shortage_penalty = pyo.Param(model.I, initialize={station_ids[i]: shortage_penalties[i] for i in range(len(station_ids))})
    model.excess_penalty = pyo.Param(model.I, initialize={station_ids[i]: excess_penalties[i] for i in range(len(station_ids))})
    
    # 5. VARIABLES
    model.s = pyo.Var(model.I, within=pyo.NonNegativeReals)
    model.e = pyo.Var(model.I, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.shortage_penalty[i] * model.s[i] + model.excess_penalty[i] * model.e[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Total Bikes Constraint
    def total_bikes_rule(model):
        return sum(model.s[i] + model.e[i] for i in model.I) == total_bikes
    model.total_bikes_constraint = pyo.Constraint(rule=total_bikes_rule)
    
    # Dock Capacity Constraints
    def dock_capacity_rule(model, i):
        return model.s[i] + model.expected_demand[i] <= model.dock_count[i]
    model.dock_capacity_constraint = pyo.Constraint(model.I, rule=dock_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("\nVariable values:")
        for i in model.I:
            s_val = pyo.value(model.s[i])
            e_val = pyo.value(model.e[i])
            print(f"s[{i}] = {s_val:.3f}, e[{i}] = {e_val:.3f}")
        
    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
bike_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 6.0
**Execution Time**: 1.99 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, demonstrating reliability similar to the other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 6.00 | 0.24s | N/A | N/A |
| Docplex | OPTIMAL | 6.00 | 2.90s | N/A | N/A |
| Pyomo | OPTIMAL | 6.00 | 1.99s | N/A | N/A |

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

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

### Business Interpretation
**Overall Strategy**: The optimal solution minimizes penalty costs effectively, ensuring efficient bike distribution across stations.
**Objective Value Meaning**: The optimal objective value of 6.0 represents the minimized total penalty cost for bike shortages and excesses.
**Resource Allocation Summary**: Bikes should be allocated to minimize shortages and excesses, adhering to dock capacities and expected demands.
**Implementation Recommendations**: Implement the solution by adjusting bike allocations according to the solver's recommendations, ensuring compliance with constraints.