create unique index ropidgtfs_run_numbers_unique_idx on ropidgtfs_run_numbers using btree (trip_id, service_id);
create unique index ropidgtfs_run_numbers_unique_idx_actual on ropidgtfs_run_numbers_actual using btree (trip_id, service_id);

create index ropidgtfs_precomputed_departures_idx on ropidgtfs_precomputed_departures using btree (trip_id, service_id);

-- Drop columns in the resulting table of the get_departures function
-- - origin_route_name

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.start_timestamp" timestamptz,
		"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.cis_stop_platform_code" varchar(15),
		"trip.vehicle_descriptor.is_air_conditioned" bool,
		delay_minutes int4,
		is_delay_available bool,
		run_number int4
	)
	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.start_timestamp",
		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",
        departure_boards_detailed.cis_stop_platform_code as "trip.cis_stop_platform_code",
		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",
		departure_boards_detailed."run.run_number"
	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.*,
            cis_stop.cis_stop_platform_code as "cis_stop_platform_code",
			run.run_number as "run.run_number"
		from
			"ropidgtfs_precomputed_departures" as "ropidgtfs_precomputed_departures"
		left join "ropidgtfs_run_numbers" as run on
			"ropidgtfs_precomputed_departures"."trip_id" = run.trip_id
			and "ropidgtfs_precomputed_departures"."service_id" = run.service_id
		left outer join v_vehiclepositions_trip_position_vehicle_info as x on
			"ropidgtfs_precomputed_departures"."trip_id" = x."trip.gtfs_trip_id"
        left join vehiclepositions_cis_stops as cis_stop on
            cis_stop.rt_trip_id = x."trip.id"
            and cis_stop.cis_stop_group_id = "ropidgtfs_precomputed_departures"."cis_stop_group_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
		);
$$;
