create view v_vehiclepositions_trip_position_vehicle_info as 
	select 
		"trip->last_position"."delay" as "delay_seconds",
		"trip"."wheelchair_accessible" as "real_wheelchair_accessible",
		"trip->last_position"."is_canceled" as "is_canceled",	
		"trip"."agency_name_real" as "trip.agency_name_real",
		"trip"."agency_name_scheduled" as "trip.agency_name_scheduled",
		"trip"."cis_line_id" as "trip.cis_line_id",
		"trip"."cis_line_short_name" as "trip.cis_line_short_name",
		"trip"."cis_trip_number" as "trip.cis_trip_number",
		"trip"."gtfs_block_id" as "trip.gtfs_block_id",
		"trip"."gtfs_route_id" as "trip.gtfs_route_id",
		"trip"."gtfs_route_short_name" as "trip.gtfs_route_short_name",
		"trip"."gtfs_route_type" as "trip.gtfs_route_type",
		"trip"."gtfs_trip_headsign" as "trip.gtfs_trip_headsign",
		"trip"."gtfs_trip_short_name" as "trip.gtfs_trip_short_name",
		"trip"."gtfs_trip_id" as "trip.gtfs_trip_id",
		"trip"."gtfs_date" as "trip.gtfs_date",
		"trip"."id" as "trip.id",
		"trip"."is_canceled" as "trip.is_canceled",
		"trip"."last_position_id" as "trip.last_position_id",
		"trip"."origin_route_name" as "trip.origin_route_name",
		"trip"."run_number" as "trip.run_number",
		"trip"."start_asw_stop_id" as "trip.start_asw_stop_id",
		"trip"."start_cis_stop_id" as "trip.start_cis_stop_id",
		"trip"."start_cis_stop_platform_code" as "trip.start_cis_stop_platform_code",
		"trip"."start_time" as "trip.start_time",
		"trip"."start_timestamp" as "trip.start_timestamp",
		"trip"."end_timestamp" as "trip.end_timestamp",
		"trip"."vehicle_registration_number" as "trip.vehicle_registration_number",
		"trip"."vehicle_type_id" as "trip.vehicle_type_id",
		"trip"."wheelchair_accessible" as "trip.wheelchair_accessible",
		"trip"."internal_route_name" as "trip.internal_route_name",
		"trip"."internal_run_number" as "trip.internal_run_number",
		"trip->last_position"."asw_last_stop_id" as "trip.last_position.asw_last_stop_id",
		"trip->last_position"."bearing" as "trip.last_position.bearing",
		"trip->last_position"."cis_last_stop_id" as "trip.last_position.cis_last_stop_id",
		"trip->last_position"."cis_last_stop_sequence" as "trip.last_position.cis_last_stop_sequence",
		"trip->last_position"."delay" as "trip.last_position.delay",
		"trip->last_position"."delay_stop_arrival" as "trip.last_position.delay_stop_arrival",
		"trip->last_position"."delay_stop_departure" as "trip.last_position.delay_stop_departure",
		"trip->last_position"."id" as "trip.last_position.id",
		"trip->last_position"."is_canceled" as "trip.last_position.is_canceled",
		"trip->last_position"."last_stop_arrival_time" as "trip.last_position.last_stop_arrival_time",
		"trip->last_position"."last_stop_departure_time" as "trip.last_position.last_stop_departure_time",
		"trip->last_position"."last_stop_id" as "trip.last_position.last_stop_id",
		"trip->last_position"."last_stop_sequence" as "trip.last_position.last_stop_sequence",
		"trip->last_position"."lat" as "trip.last_position.lat",
		"trip->last_position"."lng" as "trip.last_position.lng",
		"trip->last_position"."next_stop_arrival_time" as "trip.last_position.next_stop_arrival_time",
		"trip->last_position"."next_stop_departure_time" as "trip.last_position.next_stop_departure_time",
		"trip->last_position"."next_stop_id" as "trip.last_position.next_stop_id",
		"trip->last_position"."next_stop_sequence" as "trip.last_position.next_stop_sequence",
		"trip->last_position"."origin_time" as "trip.last_position.origin_time",
		"trip->last_position"."origin_timestamp" as "trip.last_position.origin_timestamp",
		"trip->last_position"."shape_dist_traveled" as "trip.last_position.shape_dist_traveled",
		"trip->last_position"."speed" as "trip.last_position.speed",
		"trip->last_position"."state_position" as "trip.last_position.state_position",
		"trip->last_position"."state_process" as "trip.last_position.state_process",
		"trip->last_position"."this_stop_id" as "trip.last_position.this_stop_id",
		"trip->last_position"."this_stop_sequence" as "trip.last_position.this_stop_sequence",
		"trip->last_position"."tracking" as "trip.last_position.tracking",
		"trip->last_position"."trips_id" as "trip.last_position.trips_id",
		"trip->last_position"."tcp_event" as "trip.last_position.tcp_event",
		"trip->last_position"."last_stop_headsign" as "trip.last_position.last_stop_headsign",
		"trip->last_position"."last_stop_name" as "trip.last_position.last_stop_name",
		"trip->last_position"."valid_to" as "trip.last_position.valid_to",
		"trip->last_position"."scheduled_timestamp" as "trip.last_position.scheduled_timestamp",
		"trip->vehicle_descriptor"."id" as "trip.vehicle_descriptor.id",
		"trip->vehicle_descriptor"."is_air_conditioned" as "trip.vehicle_descriptor.is_air_conditioned"
	from 
		"vehiclepositions_trips" as "trip"
	inner join "vehiclepositions_positions" as "trip->last_position" on
		"trip"."last_position_id" = "trip->last_position"."id"
		and ("trip->last_position"."valid_to" is null or "trip->last_position"."valid_to" >= current_timestamp)
		and "trip->last_position"."state_position" in ('at_stop', 'before_track', 'before_track_delayed', 'canceled', 'off_track', 'on_track')
	left outer join "vehiclepositions_vehicle_descriptors" as "trip->vehicle_descriptor" on
		"trip"."vehicle_registration_number" = "trip->vehicle_descriptor"."registration_number" 
		and trip.gtfs_route_type = "trip->vehicle_descriptor".gtfs_route_type;

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" 
			and (x."trip.gtfs_date" = ropidgtfs_precomputed_departures.date)
	) 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
		);
$$;