text: /** Some tuning/preprocessing. These will have to be wrapped either
  * in a db function (so we can call it from datasync.py)
  * or alternatively save as plaintext files and let datasync.py
  * read them (it) and then execute in a transaction after data sync
  * has taken place.
  */
  
  
  -- drop previous
  drop view if exists gtfs.loctable_v2;
  drop table if exists gtfs.calcshapes;
  drop table if exists gtfs.calcstopnodes;
  drop table if exists gtfs.calctrips;
  
  -- and create anew
  
  /** table: gtfs.calcshapes
  *
  * A table for storing full trip shapes (linestrings) and
  * collected nodes, which we'll use afterwards for "snapping"
  * stops onto trip shapes.
  */
  
  
  create table gtfs.calcshapes as
  select
      shape_id, st_makeline(array_agg(shape)) as shape,
      st_collect(shape) as nodes
  from (
      select
          s.shape_id, st_setsrid(st_makepoint(s.shape_pt_lon, s.shape_pt_lat), 4326) as shape
      from
          gtfs.shapes s
      order by s.shape_id, s.shape_pt_sequence) n
  group by shape_id
  order by shape_id;
  
  alter table gtfs.calcshapes
      add constraint pk__calcshapes primary key (shape_id);
  create index sidx__calcshapes
      on gtfs.calcshapes using gist (shape);
  create index sidx__calcshapes_nodes
      on gtfs.calcshapes using gist (shape);
  alter table gtfs.calcshapes owner to postgres;
  
  
  /** table: gtfs:calcstopnodes
  *
  * Stores closest nodes on trip shapes to respective stops.
  */
  
  
  create table gtfs.calcstopnodes as
  select
      shape_id, st_multi(st_collect(stop_node)) as stop_nodes,
      trip_id, array_agg(stop_seq) as stop_seq
  from (
      select
          s.shape_id,
          st_closestpoint(s.nodes, st_setsrid(
              st_point(stops.stop_lon, stops.stop_lat), 4326)) as stop_node,
          t.trip_id, st.stop_sequence as stop_seq
      from
          gtfs.calcshapes s,
          gtfs.stop_times st,
          gtfs.trips t,
          gtfs.stops stops
      where
          st.stop_id = stops.stop_id and
          st.trip_id = t.trip_id and
          s.shape_id = t.shape_id
      order by s.shape_id, t.trip_id, st.stop_sequence) m
  group by shape_id, trip_id;
  
  alter table gtfs.calcstopnodes
      add constraint pk__calcstopnodes primary key (trip_id);
  create index sidx__calcstopnodes
      on gtfs.calcstopnodes using gist (stop_nodes);
  alter table gtfs.calcstopnodes owner to postgres;
  
  
  /** table: gtfs.calctrips
  *
  * Break trip shapes up into shorter linestrings based on stops (i.e
  * "trip shape closest nodes to stops"). Call these "trip legs".
  * A trip leg starts at gtfs.stop_times.departure_time and ends at
  * gtfs.stop_times.arrival_time.
  */
  
  
  create table gtfs.calctrips as
  with
      splits as (
          select
              cs.shape_id, sn.trip_id,
              sn.stop_seq,
              (st_dump(split_line_multipoint(cs.shape, sn.stop_nodes))).*
          from
              gtfs.calcshapes cs,
              gtfs.calcstopnodes sn
          where
              cs.shape_id = sn.shape_id
      ),
      inbetween as (
          select
              splits.shape_id, splits.trip_id,
              splits.stop_seq[splits.path[1]] as from_stop,
              splits.stop_seq[splits.path[1]+1] as to_stop,
              splits.geom as shape
          from splits
      ),
      triptimes as (
          select
              trip_id, min(departure_time) as trip_start,
              max(arrival_time) as trip_fin
          from gtfs.stop_times
          group by trip_id
      )
  select
      inbetween.shape_id, inbetween.trip_id, inbetween.shape,
      triptimes.trip_start, triptimes.trip_fin,
      inbetween.from_stop as from_stop_seq,
      from_stops.stop_id as from_stop_id,
      from_stops.departure_time as from_stop_time,
      inbetween.to_stop as to_stop_seq,
      to_stops.stop_id as to_stop_id,
      to_stops.arrival_time as to_stop_time
  from
      inbetween,
      gtfs.stop_times from_stops,
      gtfs.stop_times to_stops,
      triptimes
  where
      inbetween.trip_id = from_stops.trip_id and
      inbetween.trip_id = to_stops.trip_id and
      inbetween.from_stop = from_stops.stop_sequence and
      inbetween.to_stop = to_stops.stop_sequence and
      triptimes.trip_id = inbetween.trip_id
  order by inbetween.trip_id, inbetween.from_stop;
  
  --alter table gtfs.calctrips add constraint pk__calcstopnodes primary key (trip_id);
  create index sidx__calctrips
      on gtfs.calctrips using gist (shape);
  alter table gtfs.calctrips owner to postgres;
  
  
  /** view: gtfs.loctable_v2
  *
  * Estimated locations of currently running public transport. This
  * is the view that will be queried for current location of public transit
  * vehicles.
  */
  
  
  create or replace view gtfs.loctable_v2 as
  with
      curtime as (
          select
              clock_timestamp()::date AS cd,
              to_char(clock_timestamp(), 'hh24:mi:ss'::text) AS ct,
              date_part('dow'::text, clock_timestamp()) + 1 AS d,
              lpad((to_char(clock_timestamp(), 'hh24')::int + 24)::varchar,2,'0')||':'||to_char(clock_timestamp(), 'mi:ss') as plushours
      ),
      cal as (
          select
              c.service_id
          from
              gtfs.calendar c,
              curtime
          where
              curtime.cd >= c.start_date and
              curtime.cd <= c.end_date and
              (array[
                  c.monday,
                  c.tuesday,
                  c.wednesday,
                  c.thursday,
                  c.friday,
                  c.saturday,
                  c.sunday])[curtime.d] = true
          ),
          startstop as (
              select
                  calctrips.trip_id, calctrips.from_stop_time as leg_start,
                  calctrips.to_stop_time as leg_fin, calctrips.from_stop_id,
                  calctrips.to_stop_id, calctrips.from_stop_seq,
                  calctrips.to_stop_seq, calctrips.shape,
                  calctrips.trip_start, calctrips.trip_fin
              from
                  gtfs.calctrips, curtime
              where
                  (
                      curtime.ct >= calctrips.from_stop_time::text and
                      curtime.ct <= calctrips.to_stop_time::text
                  ) or (
                      curtime.plushours >= calctrips.from_stop_time::text and
                      curtime.plushours <= calctrips.to_stop_time::text
                  )
          ),
          trip as (
              select
                  startstop.trip_id, trips.shape_id,
                  startstop.trip_start, startstop.trip_fin,
                  startstop.leg_start, startstop.leg_fin,
                  curtime.ct as cur, trips.trip_headsign,
                  trips.trip_long_name, routes.route_short_name,
                  routes.route_long_name, routes.route_color,
                  startstop.from_stop_id, startstop.to_stop_id,
                  startstop.from_stop_seq, startstop.to_stop_seq,
                  startstop.shape
              from
                  cal, curtime, startstop, gtfs.trips trips, gtfs.routes routes
              where
                  trips.trip_id = startstop.trip_id and
                  trips.service_id = cal.service_id and
                  trips.route_id::text = routes.route_id::text
          )
  select
      trip.trip_id, trip.shape_id, trip.trip_start, trip.trip_fin,
      trip.trip_headsign, trip.trip_long_name, trip.route_short_name,
      trip.route_long_name,
      tostop.stop_id as next_stop_id,
      tostop.stop_name as next_stop,
      trip.leg_fin as next_stop_time,
      fromstop.stop_id as prev_stop_id,
      fromstop.stop_name as prev_stop,
      trip.leg_start as prev_stop_time,
      curtime.ct as current_time,
      '#'::text || trip.route_color::text as route_color,
      /* @tkardi 09.11.2021 st_flipcoordinates to quickly get API geojson
         coors order correct. FIXME: should be a django version gdal version thing.
      */
      st_flipcoordinates(
          st_lineinterpolatepoint(
              trip.shape,
              gtfs.get_time_fraction(
                  trip.leg_start,
                  trip.leg_fin,
                  gtfs.get_current_impeded_time(
                      trip.leg_start,
                      trip.leg_fin,
                      trip.cur::character varying
                  )
              )
          )
      ) as pos
  from curtime, trip
      left join gtfs.stops tostop on trip.to_stop_id = tostop.stop_id
      left join gtfs.stops fromstop on trip.from_stop_id = fromstop.stop_id;
  
  alter table gtfs.loctable_v2 owner to postgres;
  