# Complete Optimization Problem and Solution: train_station

## 1. Problem Context and Goals

### Context  
The primary objective is to optimize the allocation of trains to various stations to maximize the number of passengers served. This involves making decisions about which trains should be assigned to which stations. Each decision is represented by a binary choice, indicating whether a specific train is assigned to a particular station. The operational goal is to maximize the total number of passengers served, which is calculated by summing the passenger demand for each train-station assignment. The business configuration includes a threshold for platform utilization, which determines when additional resources are needed. This threshold ensures that existing platforms are utilized efficiently before expanding capacity. The calculation of total passengers served is based on the demand for each train at each station and the assignments made, aiming to maximize throughput. The data reflects current operational conditions, focusing on linear decision-making processes. Resource limitations, such as the number of platforms available at each station, are considered to ensure that the optimization remains within feasible bounds. The business configuration parameters, such as platform utilization thresholds, are integral to the decision-making process.

### Goals  
The optimization goal is to maximize the total number of passengers served by strategically assigning trains to stations. The metric for optimization is the total passenger count, which is determined by the demand for each train at each station and the corresponding assignments. Success is measured by the ability to maximize this passenger count while adhering to resource constraints and compatibility requirements. The goal is articulated in straightforward business terms, focusing on maximizing passenger throughput without delving into complex mathematical expressions.

## 2. Constraints    

The constraints are designed to ensure that the optimization problem adheres to practical and operational limits:

- Each station has a limited number of platforms, and the total number of trains assigned to a station cannot exceed this number. This ensures that the station's capacity is not exceeded.
- A train can only be assigned to a station if its service is compatible with that station. This constraint ensures that operational compatibility is maintained.
- Each train can be assigned to at most one station, ensuring that no train is over-allocated and that assignments are exclusive.

These constraints are expressed in business terms that naturally align with linear mathematical formulations, avoiding any complex relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding tables for service compatibility and passenger demand, modifying existing tables for better mapping, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE station (
  Station_ID INTEGER,
  Total_Passengers INTEGER,
  Number_of_Platforms INTEGER
);

CREATE TABLE train_station (
  Train_ID INTEGER,
  Station_ID INTEGER,
  Service_Compatible BOOLEAN
);

CREATE TABLE service_compatibility (
  Train_ID INTEGER,
  Station_ID INTEGER,
  Compatible BOOLEAN
);

CREATE TABLE passenger_demand (
  Train_ID INTEGER,
  Station_ID INTEGER,
  Demand INTEGER
);
```

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

- **Station Table**: Contains information about each station, including the total number of passengers it can serve and the number of platforms available. This data is crucial for setting constraints on platform availability and for calculating the objective of maximizing passenger throughput.
  
- **Train_Station Table**: Records the assignments of trains to stations, indicating whether a train's service is compatible with a station. This table is central to defining decision variables in the optimization problem.
  
- **Service_Compatibility Table**: Details the compatibility of train services with stations, ensuring that only compatible assignments are made. This data enforces service compatibility constraints.
  
- **Passenger_Demand Table**: Captures the demand for each train at each station, which is used to calculate the potential passenger throughput for different assignments. This data is integral to the objective function.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical train station operations, ensuring a balance between demand, platform availability, and service compatibility.

-- Realistic data for station
INSERT INTO station (Station_ID, Total_Passengers, Number_of_Platforms) VALUES (1, 1200, 3);
INSERT INTO station (Station_ID, Total_Passengers, Number_of_Platforms) VALUES (2, 1800, 4);
INSERT INTO station (Station_ID, Total_Passengers, Number_of_Platforms) VALUES (3, 2200, 5);

-- Realistic data for train_station
INSERT INTO train_station (Train_ID, Station_ID, Service_Compatible) VALUES (101, 1, True);
INSERT INTO train_station (Train_ID, Station_ID, Service_Compatible) VALUES (102, 2, True);
INSERT INTO train_station (Train_ID, Station_ID, Service_Compatible) VALUES (103, 3, False);
INSERT INTO train_station (Train_ID, Station_ID, Service_Compatible) VALUES (101, 2, True);
INSERT INTO train_station (Train_ID, Station_ID, Service_Compatible) VALUES (102, 3, True);

-- Realistic data for service_compatibility
INSERT INTO service_compatibility (Train_ID, Station_ID, Compatible) VALUES (101, 1, True);
INSERT INTO service_compatibility (Train_ID, Station_ID, Compatible) VALUES (102, 2, True);
INSERT INTO service_compatibility (Train_ID, Station_ID, Compatible) VALUES (103, 3, False);
INSERT INTO service_compatibility (Train_ID, Station_ID, Compatible) VALUES (101, 2, True);
INSERT INTO service_compatibility (Train_ID, Station_ID, Compatible) VALUES (102, 3, True);

-- Realistic data for passenger_demand
INSERT INTO passenger_demand (Train_ID, Station_ID, Demand) VALUES (101, 1, 250);
INSERT INTO passenger_demand (Train_ID, Station_ID, Demand) VALUES (102, 2, 350);
INSERT INTO passenger_demand (Train_ID, Station_ID, Demand) VALUES (103, 3, 150);
INSERT INTO passenger_demand (Train_ID, Station_ID, Demand) VALUES (101, 2, 300);
INSERT INTO passenger_demand (Train_ID, Station_ID, Demand) VALUES (102, 3, 400);
```