create table if not exists route_stops (
  table_period_id             int           not null,
  route_id                    text          not null,
  direction_id                smallint      not null,
  stop_id                     text          not null,
  stop_sequence               smallint      not null
);

create index if not exists rs_route_id_idx on route_stops(table_period_id, route_id);

do $$
declare
  rec record;
  routes cursor for
    select
      table_period_id,
      route_id,
      route_short_name,
      direction_id
    from routes
      order by table_period_id, direction_id, route_id;
begin
  for route in routes
    loop
      select * into rec from route_stops where table_period_id = route.table_period_id and route_id = route.route_id limit 1;
      continue when found;

      select * into rec from trips where table_period_id = route.table_period_id limit 1;
      continue when not found;

      insert into route_stops
        select
          t.table_period_id,
          t.route_id,
          t.direction_id,
          s.stop_id,
          s.stop_sequence
        from
          stop_times s
        inner join
          (select * from trips where table_period_id = route.table_period_id and route_id = route.route_id limit 1) t
            on
              t.table_period_id = s.table_period_id
                and
              t.trip_id = s.trip_id
        order by s.stop_sequence;
    end loop;
end$$;

