alter table vehiclepositions_stop_times_history
add COLUMN stop_name varchar(255),
add COLUMN lat numeric,
add COLUMN lng numeric;


CREATE OR REPLACE VIEW v_vehiclepositions_past_stop_times
AS SELECT sub.trips_id AS rt_trip_id,
    sub.last_stop_sequence AS stop_sequence,
    sub.last_stop_id AS stop_id,
    sub.last_stop_arrival_time AS stop_arrival,
    sub.last_stop_departure_time AS stop_departure,
    max(sub.delay_stop_arrival) AS stop_arr_delay,
    min(sub.delay_stop_departure) AS stop_dep_delay,
     sub.last_stop_name as stop_name,
            sub.lat as lat,
            sub.lng as lng
   FROM ( SELECT rt_position.trips_id,
            rt_position.last_stop_sequence,
            rt_position.last_stop_id,
            rt_position.last_stop_arrival_time,
            rt_position.last_stop_departure_time,
            rt_position.delay_stop_arrival,
            rt_position.delay_stop_departure,
            rt_position.last_stop_name,
            rs.stop_lat as lat,
            rs.stop_lon as lng,
            row_number() OVER seq AS rn
           FROM vehiclepositions_positions rt_position
           JOIN ropidgtfs_stops rs ON rs.stop_id =  rt_position.last_stop_id
          WHERE (rt_position.delay_stop_arrival IS NOT NULL OR rt_position.delay_stop_departure IS NOT NULL) AND (rt_position.state_position::text = ANY (ARRAY['on_track'::character varying::text, 'at_stop'::character varying::text, 'after_track'::character varying::text]))
          WINDOW seq AS (PARTITION BY rt_position.trips_id, rt_position.last_stop_sequence, rt_position.state_position ORDER BY rt_position.id)) sub
  WHERE sub.rn = 1
  GROUP BY sub.trips_id, sub.last_stop_arrival_time, sub.last_stop_departure_time, sub.last_stop_sequence, sub.last_stop_id, sub.last_stop_name, sub.lat, sub.lng
  ORDER BY sub.trips_id, sub.last_stop_sequence;

CREATE OR REPLACE PROCEDURE vehiclepositions_data_retention(inout numberOfRows int, in dataRetentionMinutes int)
LANGUAGE plpgsql
AS $procedure$
	declare
		idsForDelete varchar(255)[];
	begin
        select array_agg(t.id) from vehiclepositions_trips t
        left join vehiclepositions_positions p on
            p.id = t.last_position_id
        where
            p.valid_to < (NOW() - (dataRetentionMinutes || ' minutes')::interval)
            or (t.gtfs_trip_id is null and p.valid_to is null and p.updated_at < NOW() - (dataRetentionMinutes || ' minutes')::interval) -- entries with valid to is null
        into idsForDelete;

        INSERT INTO vehiclepositions_trips_history
        select * from vehiclepositions_trips where id = ANY(idsForDelete)
        on conflict do nothing;

        INSERT INTO vehiclepositions_positions_history
        select * from vehiclepositions_positions where trips_id = ANY(idsForDelete)
        on conflict do nothing;

        insert into vehiclepositions_stop_times_history (
            rt_trip_id,
            gtfs_date,
            gtfs_trip_id,
            gtfs_direction_id,
            gtfs_route_short_name,
            gtfs_route_type,
            run_number,
            vehicle_registration_number,
            gtfs_stop_id,
            gtfs_stop_sequence,
            current_stop_arrival,
            current_stop_departure,
            current_stop_arr_delay,
            current_stop_dep_delay,
            created_at,
            updated_at,
            origin_route_name,
            stop_name,
            lat,
            lng
        )
        select
            rt_trip.id,
            rt_trip.gtfs_date,
            rt_trip.gtfs_trip_id,
            rt_trip.gtfs_direction_id,
            rt_trip.gtfs_route_short_name,
            rt_trip.gtfs_route_type,
            rt_trip.run_number,
            rt_trip.vehicle_registration_number,
            stop_time.stop_id,
            stop_time.stop_sequence,
            stop_time.stop_arrival,
            stop_time.stop_departure,
            stop_time.stop_arr_delay,
            stop_time.stop_dep_delay,
            now(),
            now(),
            rt_trip.origin_route_name,
            stop_time.stop_name,
            stop_time.lat,
            stop_time.lng
        from
            vehiclepositions_trips rt_trip
        join
            v_vehiclepositions_past_stop_times stop_time on stop_time.rt_trip_id  = rt_trip.id
        where
            rt_trip.id = ANY(idsForDelete)
        on conflict do nothing;


        delete from vehiclepositions_positions where trips_id = ANY(idsForDelete);
        delete from vehiclepositions_trips where id = ANY(idsForDelete);
        delete from vehiclepositions_cis_stops where rt_trip_id = ANY(idsForDelete);

        select array_length(idsForDelete,1) into numberOfRows;
	end;
$procedure$;
