1|1|ANGAMALY PRIVATE BUS STATION|06:35 am|06:35 am|2023-12-19 13:47:46.288595|2023-12-19 13:47:46.288595
2|1|ATHANI|06:53 am|06:53 am|2023-12-19 13:47:46.326042|2023-12-19 13:47:46.326042
3|1|MANJALY|07:15 am|07:15 am|2023-12-19 13:47:46.328846|2023-12-19 13:47:46.328846
4|1|NORTH PARAVUR|07:25 am|07:25 am|2023-12-19 13:47:46.331207|2023-12-19 13:47:46.331207
5|2|NORTH PARAVUR|07:30 am|07:30 am|2023-12-19 13:47:46.336711|2023-12-19 13:47:46.336711
6|2|MANJALY|07:40 am|07:40 am|2023-12-19 13:47:46.339104|2023-12-19 13:47:46.339104
7|2|ATHANI|08:02 am|08:02 am|2023-12-19 13:47:46.341712|2023-12-19 13:47:46.341712
8|2|ANGAMALY PRIVATE BUS STATION|08:20 am|08:20 am|2023-12-19 13:47:46.344398|2023-12-19 13:47:46.344398
9|3|ANGAMALY PRIVATE BUS STATION|08:38 am|08:38 am|2023-12-19 13:47:46.350449|2023-12-19 13:47:46.350449
10|3|ATHANI|08:56 am|08:56 am|2023-12-19 13:47:46.353683|2023-12-19 13:47:46.353683
11|3|MANJALY|09:18 am|09:18 am|2023-12-19 13:47:46.357239|2023-12-19 13:47:46.357239
12|3|NORTH PARAVUR|09:28 am|09:28 am|2023-12-19 13:47:46.359593|2023-12-19 13:47:46.359593
13|4|KOTHAD CHURCH|07:01 am|07:01 am|2023-12-19 13:47:46.382469|2023-12-19 13:47:46.382469
14|4|KOTHAD FERRY|07:05 am|07:05 am|2023-12-19 13:47:46.385671|2023-12-19 13:47:46.385671
15|4|SOUTH CHITTOOR|07:10 am|07:10 am|2023-12-19 13:47:46.388818|2023-12-19 13:47:46.388818
16|4|ERNAKULAM SOUTH|07:40 am|07:40 am|2023-12-19 13:47:46.391433|2023-12-19 13:47:46.391433
17|4|KUNDANOOR|07:58 am|07:58 am|2023-12-19 13:47:46.393678|2023-12-19 13:47:46.393678
18|5|KUNDANOOR|08:26 am|08:26 am|2023-12-19 13:47:46.399398|2023-12-19 13:47:46.399398
19|5|ERNAKULAM SOUTH|08:44 am|08:44 am|2023-12-19 13:47:46.401729|2023-12-19 13:47:46.401729
20|5|SOUTH CHITTOOR|09:14 am|09:14 am|2023-12-19 13:47:46.404313|2023-12-19 13:47:46.404313
21|5|KOTHAD FERRY|09:19 am|09:19 am|2023-12-19 13:47:46.406821|2023-12-19 13:47:46.406821
22|5|KOTHAD CHURCH|09:23 am|09:23 am|2023-12-19 13:47:46.409210|2023-12-19 13:47:46.409210
23|6|KOTHAD CHURCH|09:57 am|09:57 am|2023-12-19 13:47:46.414600|2023-12-19 13:47:46.414600
24|6|KOTHAD FERRY|10:01 am|10:01 am|2023-12-19 13:47:46.417928|2023-12-19 13:47:46.417928
25|6|SOUTH CHITTOOR|10:06 am|10:06 am|2023-12-19 13:47:46.420240|2023-12-19 13:47:46.420240
26|6|ERNAKULAM SOUTH|10:36 am|10:36 am|2023-12-19 13:47:46.422972|2023-12-19 13:47:46.422972
27|6|THEVARA JN|10:42 am|10:42 am|2023-12-19 13:47:46.425651|2023-12-19 13:47:46.425651
28|7|NORTH PARAVUR|05:30 am|05:30 am|2023-12-19 13:47:46.449067|2023-12-19 13:47:46.449067
29|7|CHERAI|05:42 am|05:42 am|2023-12-19 13:47:46.451667|2023-12-19 13:47:46.451667
30|7|MUNAMBAM|05:59 am|05:59 am|2023-12-19 13:47:46.453905|2023-12-19 13:47:46.453905
31|8|MUNAMBAM|06:08 am|06:08 am|2023-12-19 13:47:46.459139|2023-12-19 13:47:46.459139
32|8|CHERAI|06:25 am|06:25 am|2023-12-19 13:47:46.461334|2023-12-19 13:47:46.461334
33|8|NORTH PARAVUR|06:37 am|06:42 am|2023-12-19 13:47:46.463757|2023-12-19 13:47:46.463757
34|8|K THURUTHU|07:07 am|07:07 am|2023-12-19 13:47:46.466172|2023-12-19 13:47:46.466172
35|8|PANAYIKULAM JN. ALUVA|07:22 am|07:22 am|2023-12-19 13:47:46.468390|2023-12-19 13:47:46.468390

from this table this is used 
# # app/controllers/api/v1/schedules_controller.rb
# class Api::V1::SchedulesController < ApplicationController
#   def index
#     departure_station = params[:departure]
#     destination_station = params[:destination]

#     if departure_station.present? && destination_station.present?
#       schedules = Schedule.joins(:stations)
#                           .where(stations: { name: departure_station })
#                           .where(Schedule.arel_table[:id].in(
#                             Schedule.joins(:stations)
#                             .where(stations: { name: destination_station})
#                             .where(‘stations.arrival_time > stations_departures.arrival_time’)
#                             .where(‘stations.schedule_id = stations_departures.schedule_id’)
#                             .select(:id)
#                           ))
#                           .select(‘schedules.*, stations.arrival_time AS departure_arrival_time’)
#                           .from(‘schedules, stations as stations_departures’)
#                           .where(‘schedules.id = stations_departures.schedule_id’)
#                           .where(‘stations_departures.name’: departure_station)
#                           .distinct
#                           .order(‘departure_arrival_time’)
      
#       render json: schedules.map { |schedule|
#         {
#           trip: schedule.trip,
#           stations: schedule.stations.order(:arrival_time).map { |station|
#             {
#               station: station.name,
#               arrivalTime: station.arrival_time.strftime(‘%I:%M %p’),
#               departureTime: station.departure_time.strftime(‘%I:%M %p’)
#             }
#           }.reject { |s| s[:arrivalTime] < schedule.departure_arrival_time }  # Filters out stations occurring before the departure station
#         }
#       }, status: :ok
#     else
#       render json: { error: ‘Departure and destination stations are required.’ }, status: :unprocessable_entity
#     end
#   end
# end

# app/controllers/api/v1/schedules_controller.rb
class Api::V1::SchedulesController < ApplicationController
  def index
    departure_station = params[:departure]
    destination_station = params[:destination]

    if departure_station.present? && destination_station.present?
      schedules = Schedule.joins(stations: { schedule: { route: :bus_schedule } })
                          .where(stations: { name: departure_station })
                          .where(id: Schedule.joins(stations: { schedule: { route: :bus_schedule } })
                                              .where(stations: { name: destination_station })
                                              .pluck(:id))
                          .group(:trip)
                          .order(:trip)

      render json: schedules.map { |schedule|
        {
          trip: schedule.trip,
          stations: schedule.stations.map { |station|
            {
              station: station.name,
              arrivalTime: station.arrival_time,
              departureTime: station.departure_time
            }
          }
        }
      }, status: :ok
    else
      render json: { error: 'Departure and destination stations are required.' }, status: :unprocessable_entity
    end
  end
end