alter table vehiclepositions_stop_times_history add column origin_route_name varchar(20);

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 (gtfs_trip_id is null and p.created_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
        )
        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
        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);

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