alter table ropidgtfs_precomputed_trip_schedule add column direction_id smallint;

alter table vehiclepositions_trips add column gtfs_direction_id smallint;
alter table vehiclepositions_trips_history add column gtfs_direction_id smallint;


create table vehiclepositions_stop_times_history (
    rt_trip_id varchar(100) not null,
    gtfs_date date,
    gtfs_trip_id varchar(50),
    gtfs_direction_id smallint,
    gtfs_route_short_name varchar(20),
    gtfs_route_type int,
    run_number int,
    vehicle_registration_number int,
    gtfs_stop_sequence smallint not null,
    gtfs_stop_id varchar(20),
    current_stop_arrival timestamp with time zone,
    current_stop_departure timestamp with time zone,
    current_stop_arr_delay int,
    current_stop_dep_delay int,

    -- audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by varchar(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by varchar(150),

    constraint vehiclepositions_stop_times_history_pkey primary key (rt_trip_id, gtfs_stop_sequence)
);


create view v_vehiclepositions_past_stop_times as
select
    trips_id as rt_trip_id,
    last_stop_sequence as stop_sequence,
    last_stop_id as stop_id,
    last_stop_arrival_time as stop_arrival,
    last_stop_departure_time as stop_departure,
    min(delay_stop_arrival) as stop_arr_delay,
    max(delay_stop_departure) as stop_dep_delay
from (
    select
    	trips_id,
        last_stop_sequence,
        last_stop_id,
        last_stop_arrival_time,
        last_stop_departure_time,
        delay_stop_arrival,
        delay_stop_departure,
        row_number() over seq as rn
    from
        vehiclepositions_positions rt_position
    where
        (delay_stop_arrival is not null or delay_stop_departure is not null)
        and state_position in ('on_track', 'at_stop', 'after_track')
    window
        seq as (partition by trips_id, last_stop_sequence, state_position order by id)
) as sub
where
    rn = 1
group by
    trips_id,
    last_stop_arrival_time,
    last_stop_departure_time,
    last_stop_sequence,
    last_stop_id
order by
    trips_id,
    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 (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
        )
        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()
        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$;
