# Complete Optimization Problem and Solution: train_station

## 1. Problem Context and Goals

### Context  
The train station optimization problem focuses on efficiently assigning trains to stations to minimize passenger waiting times while adhering to operational constraints. The primary decision involves determining whether a specific train should be assigned to a particular station, represented as a binary choice. The operational parameters include the waiting time of passengers at each station for a specific train and the number of passengers waiting for that train. These parameters are used to calculate the total waiting time, which is the metric to be minimized.  

The business configuration includes a maximum allowed waiting time for passengers, set at 15 minutes, which serves as a constraint to ensure passenger satisfaction and operational efficiency. Additionally, the passenger waiting time is calculated based on the difference between the train's arrival and departure times at a station. This calculation is a linear relationship and is used to determine the coefficients in the optimization objective.  

The problem is designed to avoid nonlinear relationships, such as variable products or divisions, ensuring that the formulation remains linear. The data sources, including passenger waiting times, passenger counts, and station platform capacities, are mapped directly to the coefficients and constraints in the optimization problem.  

### Goals  
The primary goal of this optimization problem is to minimize the total passenger waiting time across all stations. This is achieved by optimally assigning trains to stations, considering the number of passengers and their waiting times. Success is measured by reducing the overall waiting time while ensuring that no station exceeds its platform capacity and that the maximum waiting time constraint is not violated. The optimization aligns with the business objective of improving passenger satisfaction and operational efficiency through linear decision-making.  

## 2. Constraints  

The optimization problem is subject to two key constraints:  

1. **Platform Capacity Constraint**: For each station, the total number of trains assigned must not exceed the number of available platforms. This ensures that stations do not become overcrowded and can handle the assigned trains efficiently.  

2. **Maximum Waiting Time Constraint**: For each train assigned to a station, the waiting time of passengers must not exceed the maximum allowed waiting time of 15 minutes. This constraint ensures that passenger satisfaction is maintained by preventing excessive delays.  

Both constraints are expressed in linear terms, avoiding any nonlinear relationships such as variable products or divisions. They are directly derived from the operational limitations and business configuration parameters.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a table for train assignments, updating existing tables to include train and station IDs, and moving passenger waiting time formula to configuration logic. Configuration logic updates include scalar parameters for maximum waiting time and formulas for passenger waiting time calculations.

CREATE TABLE passenger_waiting_time (
  Train_ID INTEGER,
  Station_ID INTEGER,
  Waiting_Time INTEGER
);

CREATE TABLE number_of_passengers (
  Train_ID INTEGER,
  Station_ID INTEGER,
  Passenger_Count INTEGER
);

CREATE TABLE station (
  Number_of_Platforms INTEGER
);

CREATE TABLE train_assignment (
  Train_ID INTEGER,
  Station_ID INTEGER,
  Assignment BOOLEAN
);
```

### Data Dictionary  
- **Passenger Waiting Time**:  
  - **Business Purpose**: Represents the waiting time in minutes for passengers at a specific station for a particular train.  
  - **Optimization Role**: Used as a coefficient in the objective function to calculate the total waiting time.  

- **Number of Passengers**:  
  - **Business Purpose**: Indicates the number of passengers waiting for a specific train at a station.  
  - **Optimization Role**: Used as a weight in the objective function to prioritize trains with higher passenger counts.  

- **Station**:  
  - **Business Purpose**: Contains information about the station, including the maximum number of platforms available.  
  - **Optimization Role**: Provides the constraint bound for the platform capacity constraint.  

- **Train Assignment**:  
  - **Business Purpose**: Represents the binary decision of whether a train is assigned to a station.  
  - **Optimization Role**: Serves as the decision variable in the optimization problem.  


### Retrieved Values

**Query 1: Query Description: Retrieve the waiting time and number of passengers for each train at each station. This data is crucial for calculating the total waiting time, which is the objective function to be minimized.**

```sql
SELECT pwt.Train_ID, pwt.Station_ID, pwt.Waiting_Time, np.Passenger_Count FROM passenger_waiting_time pwt JOIN number_of_passengers np ON pwt.Train_ID = np.Train_ID AND pwt.Station_ID = np.Station_ID;
```

**Results (CSV format):**
```csv
Train_ID,Station_ID,Waiting_Time,Passenger_Count
1,1,5,150
2,2,10,100
3,3,15,50
```

**Query 2: Query Description: Retrieve the number of platforms available at each station. This data is essential for the platform capacity constraint, ensuring that the number of assigned trains does not exceed the station's capacity.**

```sql
SELECT Station_ID, Number_of_Platforms FROM station;
```

**Error:** Execution failed on sql 'SELECT Station_ID, Number_of_Platforms FROM station;': no such column: Station_ID

**Query 3: Query Description: Retrieve the current train assignments to understand the existing state of train allocations. This data helps in evaluating the current assignments and making informed decisions for optimization.**

```sql
SELECT Train_ID, Station_ID, Assignment FROM train_assignment;
```

**Results (CSV format):**
```csv
Train_ID,Station_ID,Assignment
1,1,1
2,2,1
3,3,0
```

**Query 4: Query Description: Retrieve the total number of passengers waiting for each train across all stations. This aggregated data helps prioritize trains with higher passenger counts in the optimization process.**

```sql
SELECT Train_ID, SUM(Passenger_Count) AS Total_Passengers FROM number_of_passengers GROUP BY Train_ID;
```

**Results (CSV format):**
```csv
Train_ID,Total_Passengers
1,150
2,100
3,50
```

**Query 5: Query Description: Retrieve the total waiting time for each train across all stations. This aggregated data helps in understanding the overall waiting time impact of each train, which is critical for the objective function.**

```sql
SELECT Train_ID, SUM(Waiting_Time) AS Total_Waiting_Time FROM passenger_waiting_time GROUP BY Train_ID;
```

**Results (CSV format):**
```csv
Train_ID,Total_Waiting_Time
1,5
2,10
3,15
```

**Query 6: Query Description: Retrieve the waiting time and number of passengers for trains where the waiting time exceeds the maximum allowed waiting time (15 minutes). This data is crucial for identifying and addressing violations of the maximum waiting time constraint.**

```sql
SELECT pwt.Train_ID, pwt.Station_ID, pwt.Waiting_Time, np.Passenger_Count FROM passenger_waiting_time pwt JOIN number_of_passengers np ON pwt.Train_ID = np.Train_ID AND pwt.Station_ID = np.Station_ID WHERE pwt.Waiting_Time > 15;
```

**Results (CSV format):**
```csv
Train_ID,Station_ID,Waiting_Time,Passenger_Count
```

**Query 7: Query Description: Retrieve the number of trains currently assigned to each station. This data helps in evaluating the current platform usage and ensuring that the platform capacity constraint is not violated.**

```sql
SELECT Station_ID, COUNT(Train_ID) AS Assigned_Trains FROM train_assignment WHERE Assignment = TRUE GROUP BY Station_ID;
```

**Results (CSV format):**
```csv
Station_ID,Assigned_Trains
1,1
2,1
```

**Query 8: Query Description: Retrieve the total waiting time for each station across all trains. This aggregated data helps in understanding the waiting time impact at each station, which is important for the objective function.**

```sql
SELECT Station_ID, SUM(Waiting_Time) AS Total_Waiting_Time FROM passenger_waiting_time GROUP BY Station_ID;
```

**Results (CSV format):**
```csv
Station_ID,Total_Waiting_Time
1,5
2,10
3,15
```

**Query 9: Query Description: Retrieve the total number of passengers waiting at each station across all trains. This aggregated data helps in understanding the passenger load at each station, which is important for the objective function.**

```sql
SELECT Station_ID, SUM(Passenger_Count) AS Total_Passengers FROM number_of_passengers GROUP BY Station_ID;
```

**Results (CSV format):**
```csv
Station_ID,Total_Passengers
1,150
2,100
3,50
```

