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$;


drop function get_departures;
create function get_departures (
	stopsIds varchar,
	mode int,
    dateDepartureBetweenStart timestamptz,
   	dateDepartureBetweenEnd timestamptz,
  	isNegativeMinutesBefore int,
 	dateCanceledDepartureBetweenStart timestamptz,
 	dateCanceledDepartureBetweenEnd timestamptz,
	airconditioninfoenabled int
)
	returns table (
		departure_datetime_real timestamptz,
		arrival_datetime_real timestamptz,
		stop_sequence int2,
		stop_headsign varchar(70),
		arrival_datetime timestamptz,
		departure_datetime timestamptz,
		stop_id varchar(25),
		platform_code varchar(10),
		wheelchair_boarding int2,
		min_stop_sequence int2,
		max_stop_sequence int2,
		trip_id varchar(50),
		trip_headsign varchar(100),
		trip_short_name varchar(30),
		wheelchair_accessible int2,
		route_short_name varchar(50),
		route_type int2,
		route_id varchar(20),
		is_night bpchar(1),
		is_regional bpchar(1),
		is_substitute_transport bpchar(1),
		next_stop_id varchar(30),
		delay_seconds int4,
		real_wheelchair_accessible bool,
		is_canceled bool,
		"trip.gtfs_trip_short_name" varchar(255),
		"trip.gtfs_date" date,
		"trip.internal_route_name" varchar(50),
		"trip.internal_run_number" int4,
		"trip.last_position.last_stop_id" varchar(255),
		"trip.last_position.last_stop_sequence" int4,
		"trip.last_position.last_stop_name" varchar(255),
		"trip.last_position.this_stop_sequence" int4,
		"trip.vehicle_descriptor.is_air_conditioned" bool,
		delay_minutes int4,
		is_delay_available bool
	)
	language SQL
	set search_path from current
as $$
	select
		departure_boards_detailed."computed.departure_datetime_real",
		departure_boards_detailed."computed.arrival_datetime_real",
		departure_boards_detailed."ropidgtfs_precomputed_departures.stop_sequence",
		departure_boards_detailed."ropidgtfs_precomputed_departures.stop_headsign",
		departure_boards_detailed."ropidgtfs_precomputed_departures.arrival_datetime",
		departure_boards_detailed."ropidgtfs_precomputed_departures.departure_datetime",
		departure_boards_detailed."ropidgtfs_precomputed_departures.stop_id",
		departure_boards_detailed."ropidgtfs_precomputed_departures.platform_code",
		departure_boards_detailed."ropidgtfs_precomputed_departures.wheelchair_boarding",
		departure_boards_detailed."ropidgtfs_precomputed_departures.min_stop_sequence",
		departure_boards_detailed."ropidgtfs_precomputed_departures.max_stop_sequence",
		departure_boards_detailed."ropidgtfs_precomputed_departures.trip_id",
		departure_boards_detailed."ropidgtfs_precomputed_departures.trip_headsign",
		departure_boards_detailed."ropidgtfs_precomputed_departures.trip_short_name",
		departure_boards_detailed."ropidgtfs_precomputed_departures.wheelchair_accessible",
		departure_boards_detailed."ropidgtfs_precomputed_departures.route_short_name",
		departure_boards_detailed."ropidgtfs_precomputed_departures.route_type",
		departure_boards_detailed."ropidgtfs_precomputed_departures.route_id",
		departure_boards_detailed."ropidgtfs_precomputed_departures.is_night",
		departure_boards_detailed."ropidgtfs_precomputed_departures.is_regional",
		departure_boards_detailed."ropidgtfs_precomputed_departures.is_substitute_transport",
		departure_boards_detailed."ropidgtfs_precomputed_departures.next_stop_id",
		departure_boards_detailed."delay_seconds",
		departure_boards_detailed."real_wheelchair_accessible",
		departure_boards_detailed."is_canceled",
		departure_boards_detailed."trip.gtfs_trip_short_name",
		departure_boards_detailed."trip.gtfs_date",
		departure_boards_detailed."trip.internal_route_name",
		departure_boards_detailed."trip.internal_run_number",
		departure_boards_detailed."trip.last_position.last_stop_id",
		departure_boards_detailed."trip.last_position.last_stop_sequence",
		departure_boards_detailed."trip.last_position.last_stop_name",
		departure_boards_detailed."trip.last_position.this_stop_sequence",
		case when airconditioninfoenabled = 1 then departure_boards_detailed."trip.vehicle_descriptor.is_air_conditioned" else NULL end,
		TRUNC(departure_boards_detailed."trip.last_position.delay"::DECIMAL / 60,	0)::int as "delay_minutes",
		case when departure_boards_detailed."trip.last_position.delay" is null then false else true end as "is_delay_available"
	from (
		select
			("departure_datetime"
				+ MAKE_INTERVAL(secs => (case when "trip.last_position.delay" is null then 0 else "trip.last_position.delay" end))
				- case when (MAKE_INTERVAL(secs => (case when "trip.last_position.delay" is null then 0 else "trip.last_position.delay" end)) > MAKE_INTERVAL())
					then least (
						MAKE_INTERVAL(secs => (case when "trip.last_position.delay" is null then 0 else "trip.last_position.delay" end)),
						("departure_datetime" - "arrival_datetime")::interval
					)
					else MAKE_INTERVAL()
					end
			) "computed.departure_datetime_real",
			"arrival_datetime" + MAKE_INTERVAL(secs => (
				CASE WHEN "trip.last_position.delay" IS NULL THEN 0 ELSE "trip.last_position.delay" end
			))  "computed.arrival_datetime_real",
			"ropidgtfs_precomputed_departures"."stop_sequence" as "ropidgtfs_precomputed_departures.stop_sequence",
			"ropidgtfs_precomputed_departures"."stop_headsign" as "ropidgtfs_precomputed_departures.stop_headsign",
			"ropidgtfs_precomputed_departures"."pickup_type" as "ropidgtfs_precomputed_departures.pickup_type",
			"ropidgtfs_precomputed_departures"."drop_off_type" as "ropidgtfs_precomputed_departures.drop_off_type",
			"ropidgtfs_precomputed_departures"."arrival_time" as "ropidgtfs_precomputed_departures.arrival_time",
			"ropidgtfs_precomputed_departures"."arrival_datetime" as "ropidgtfs_precomputed_departures.arrival_datetime",
			"ropidgtfs_precomputed_departures"."departure_time" as "ropidgtfs_precomputed_departures.departure_time",
			"ropidgtfs_precomputed_departures"."departure_datetime" as "ropidgtfs_precomputed_departures.departure_datetime",
			"ropidgtfs_precomputed_departures"."stop_id" as "ropidgtfs_precomputed_departures.stop_id",
			"ropidgtfs_precomputed_departures"."stop_name" as "ropidgtfs_precomputed_departures.stop_name",
			"ropidgtfs_precomputed_departures"."platform_code" as "ropidgtfs_precomputed_departures.platform_code",
			"ropidgtfs_precomputed_departures"."wheelchair_boarding" as "ropidgtfs_precomputed_departures.wheelchair_boarding",
			"ropidgtfs_precomputed_departures"."min_stop_sequence" as "ropidgtfs_precomputed_departures.min_stop_sequence",
			"ropidgtfs_precomputed_departures"."max_stop_sequence" as "ropidgtfs_precomputed_departures.max_stop_sequence",
			"ropidgtfs_precomputed_departures"."trip_id" as "ropidgtfs_precomputed_departures.trip_id",
			"ropidgtfs_precomputed_departures"."trip_headsign" as "ropidgtfs_precomputed_departures.trip_headsign",
			"ropidgtfs_precomputed_departures"."trip_short_name" as "ropidgtfs_precomputed_departures.trip_short_name",
			"ropidgtfs_precomputed_departures"."wheelchair_accessible" as "ropidgtfs_precomputed_departures.wheelchair_accessible",
			"ropidgtfs_precomputed_departures"."service_id" as "ropidgtfs_precomputed_departures.service_id",
			"ropidgtfs_precomputed_departures"."date" as "ropidgtfs_precomputed_departures.date",
			"ropidgtfs_precomputed_departures"."route_short_name" as "ropidgtfs_precomputed_departures.route_short_name",
			"ropidgtfs_precomputed_departures"."route_type" as "ropidgtfs_precomputed_departures.route_type",
			"ropidgtfs_precomputed_departures"."route_id" as "ropidgtfs_precomputed_departures.route_id",
			"ropidgtfs_precomputed_departures"."is_night" as "ropidgtfs_precomputed_departures.is_night",
			"ropidgtfs_precomputed_departures"."is_regional" as "ropidgtfs_precomputed_departures.is_regional",
			"ropidgtfs_precomputed_departures"."is_substitute_transport" as "ropidgtfs_precomputed_departures.is_substitute_transport",
			"ropidgtfs_precomputed_departures"."next_stop_sequence" as "ropidgtfs_precomputed_departures.next_stop_sequence",
			"ropidgtfs_precomputed_departures"."next_stop_id" as "ropidgtfs_precomputed_departures.next_stop_id",
			"ropidgtfs_precomputed_departures"."last_stop_sequence" as "ropidgtfs_precomputed_departures.last_stop_sequence",
			"ropidgtfs_precomputed_departures"."last_stop_id" as "ropidgtfs_precomputed_departures.last_stop_id",
			x.*
		from
			"ropidgtfs_precomputed_departures" as "ropidgtfs_precomputed_departures"
		left outer join v_vehiclepositions_trip_position_vehicle_info as x on
			"ropidgtfs_precomputed_departures"."trip_id" = x."trip.gtfs_trip_id"
	) departure_boards_detailed
	where
		departure_boards_detailed."ropidgtfs_precomputed_departures.stop_id" = ANY(STRING_TO_ARRAY(stopsIds,','))
		and (
			(
				(
					(mode = 1 and departure_boards_detailed."computed.arrival_datetime_real" between dateDepartureBetweenStart and dateDepartureBetweenEnd)
					or (mode != 1 and departure_boards_detailed."computed.departure_datetime_real" between dateDepartureBetweenStart and dateDepartureBetweenEnd)
				)
				and
				(
					0 = isNegativeMinutesBefore
					or ( -- pro záporné minutes before
						departure_boards_detailed."ropidgtfs_precomputed_departures.stop_sequence" >= departure_boards_detailed."trip.last_position.last_stop_sequence"
						or departure_boards_detailed."trip.last_position.last_stop_sequence" IS null
					)
				)

			)
			or (
				departure_boards_detailed."trip.is_canceled" = true
				and
				(
					(mode = 1 and departure_boards_detailed."computed.arrival_datetime_real" between dateCanceledDepartureBetweenStart and dateCanceledDepartureBetweenEnd)
					or (mode != 1 and departure_boards_detailed."computed.departure_datetime_real" between dateCanceledDepartureBetweenStart and dateCanceledDepartureBetweenEnd)
				)
			)
		) and
		(
			(mode = 1 and (departure_boards_detailed."ropidgtfs_precomputed_departures.pickup_type" != '1' and departure_boards_detailed."ropidgtfs_precomputed_departures.stop_sequence" != departure_boards_detailed."ropidgtfs_precomputed_departures.max_stop_sequence"))  -- mode default/departures
			or (mode=2 and (departure_boards_detailed."ropidgtfs_precomputed_departures.drop_off_type" != '1' and departure_boards_detailed."ropidgtfs_precomputed_departures.stop_sequence" != departure_boards_detailed."ropidgtfs_precomputed_departures.min_stop_sequence")) -- mode arrivals
			or (mode=3 and (departure_boards_detailed."ropidgtfs_precomputed_departures.pickup_type" != '1')) -- mode mixed
		);
$$;

drop view v_vehiclepositions_stop_time_delay_prediction;
create view v_vehiclepositions_stop_time_delay_prediction as
with recursive stop_times as (
    select
    	rst.trip_id,
        vt.provider_source_type,
    	vp.delay,
    	coalesce(vp.last_stop_sequence, 1) as initial_stop_sequence,
        vp.state_position,
    	rst.stop_sequence,
    	rst.stop_id,
    	rst.computed_dwell_time_seconds,
    	case when vp.last_stop_sequence is null
            then vp.delay
            else vp.delay_stop_arrival
        end as arrival_delay_seconds,
    	case when vp.last_stop_sequence is null
            then predict_delay_seconds(vp.delay, rst.computed_dwell_time_seconds, vt.provider_source_type)
            when vp.state_position = 'at_stop'
                then case when vt.provider_source_type = '1' then greatest(0, vp.delay) else vp.delay end
            else vp.delay_stop_departure
        end as departure_delay_seconds
    from ropidgtfs_stop_times rst
    inner join vehiclepositions_trips vt on vt.gtfs_trip_id = rst.trip_id
    inner join vehiclepositions_positions vp on vp.id = vt.last_position_id
        and (vp.valid_to is null or vp.valid_to >= now())
    where
        vt.gtfs_trip_id is not null
        and rst.stop_sequence = coalesce(vp.last_stop_sequence, 1)
union all
    select
    	rst.trip_id,
        previous_row.provider_source_type,
    	previous_row.delay,
    	previous_row.initial_stop_sequence,
        previous_row.state_position,
    	rst.stop_sequence,
    	rst.stop_id,
    	rst.computed_dwell_time_seconds,
    	case when rst.stop_sequence - previous_row.initial_stop_sequence = 1 and previous_row.state_position != 'at_stop'
            then previous_row.delay
            else previous_row.departure_delay_seconds
        end as arrival_delay_seconds,
        predict_delay_seconds(
            case when rst.stop_sequence - previous_row.initial_stop_sequence = 1 and previous_row.state_position != 'at_stop' then previous_row.delay else previous_row.departure_delay_seconds end,
            rst.computed_dwell_time_seconds,
            previous_row.provider_source_type
        ) as departure_delay_seconds
    from stop_times previous_row
    inner join ropidgtfs_stop_times rst on rst.trip_id = previous_row.trip_id and rst.stop_sequence = previous_row.stop_sequence + 1
)
select trip_id, stop_sequence, stop_id, arrival_delay_seconds, departure_delay_seconds
from stop_times
order by trip_id, stop_sequence;

comment on view v_vehiclepositions_stop_time_delay_prediction is '
This view contains the predicted delay of a vehicle at each stop.
';


CREATE OR REPLACE PROCEDURE ropidgtfs_preparetmptables()
 LANGUAGE plpgsql
 SET search_path from CURRENT
AS $procedure$
	begin
		CALL ropidgtfs_cleanTmpTables();
		CALL ropidgtfs_cleanOldTables();
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_agency');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_calendar');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_calendar_dates');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_shapes');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_stop_times');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_stops');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_routes');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_route_sub_agencies');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_trips');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_cis_stops');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_cis_stop_groups');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_run_numbers');
		CALL ropidgtfs_prepareTmpTable('ropidgtfs_ois');
		alter table ropidgtfs_stop_times_tmp drop computed_dwell_time_seconds;
		alter table ropidgtfs_stop_times_tmp add computed_dwell_time_seconds int2 NOT NULL GENERATED ALWAYS AS (calculate_dwell_time_seconds(arrival_time, departure_time)) stored;
	end
$procedure$
;


alter table ropidgtfs_stops drop column computed_cis_stop_id;
alter table ropidgtfs_precomputed_departures drop column cis_stop_group_id;
drop table vehiclepositions_cis_stops;
