-- EXAMPLE DATA - for testing purposes

WITH init_values AS (SELECT DATE_TRUNC('minute', NOW())::TIME AS start_time, DATE_TRUNC('minute', NOW()) AS start_timestamp)
INSERT INTO vehiclepositions_positions (created_at, delay, delay_stop_arrival, delay_stop_departure, next_stop_id, shape_dist_traveled, is_canceled, lat, lng, origin_time, origin_timestamp, is_tracked, trips_id, create_batch_id, created_by, update_batch_id, updated_at, updated_by, id, bearing, cis_last_stop_id, cis_last_stop_sequence, last_stop_id, last_stop_sequence, next_stop_sequence, speed, last_stop_arrival_time, last_stop_departure_time, next_stop_arrival_time, next_stop_departure_time, state_process, state_position, this_stop_id)
VALUES (NOW(), 273, 252, NULL, 'U1942Z13', 22.7, NULL, 50.18338, 14.65627, (SELECT start_time + INTERVAL '0 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '0 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 16, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 57, NULL, 61, 'U967Z3P', 25.9, NULL, 50.03694, 14.56214,    (SELECT start_time + INTERVAL '11 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '11 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 256, 245, NULL, 'U78Z73', 36.5, NULL, 50.12662, 14.47038,   (SELECT start_time + INTERVAL '22 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '22 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 148, NULL, 163, 'U9871Z1', 38.7, NULL, 50.35566, 14.3611,   (SELECT start_time + INTERVAL '23 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '23 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 18, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 127, NULL, 83, 'U2817Z1', 39.2, NULL, 50.02875, 15.19213,   (SELECT start_time + INTERVAL '30 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '30 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 236, NULL, 225, 'U2349Z1', 41.2, NULL, 50.16256, 14.75678,  (SELECT start_time + INTERVAL '35 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '35 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 14, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 478, NULL, 497, 'U188Z1P', 43.3, NULL, 50.11107, 14.591,    (SELECT start_time + INTERVAL '40 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '40 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 6, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 298, NULL, 300, 'U461Z1P', 49.9, NULL, 50.03539, 14.59305,  (SELECT start_time + INTERVAL '47 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '47 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 7, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), -21, NULL, 22, 'U2286Z1', 51.1, NULL, 50.24147, 14.31026,   (SELECT start_time + INTERVAL '48 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '48 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 752, 745, NULL, 'U2019Z1', 51.3, NULL, 49.96941, 14.80906,  (SELECT start_time + INTERVAL '50 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '50 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 13, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 31, NULL, 60, 'U337Z4', 52.3, NULL, 50.02615, 14.39524,     (SELECT start_time + INTERVAL '60 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '60 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 15, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 344, NULL, 310, 'U4507Z2', 56.1, NULL, 49.85952, 14.50574,  (SELECT start_time + INTERVAL '61 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '61 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 11, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 401, NULL, 423, 'U9162Z1', 56.9, NULL, 50.33257, 14.52904,  (SELECT start_time + INTERVAL '65 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '65 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 19, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 385, NULL, 390, 'U336Z2', 59.4, NULL, 50.02967, 14.60214,   (SELECT start_time + INTERVAL '70 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '70 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 121, NULL, 110, 'U1952Z1', 61.6, NULL, 49.83629, 14.33417,  (SELECT start_time + INTERVAL '78 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '78 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 17, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 267, NULL, 271, 'U4413Z2', 61.7, NULL, 50.17277, 14.1221,   (SELECT start_time + INTERVAL '100 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '100 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 8, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 385, NULL, 383, 'U1644Z2', 64.2, NULL, 50.03932, 14.24612,  (SELECT start_time + INTERVAL '120 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '120 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 566, NULL, 576, 'U1796Z1', 65.5, NULL, 50.35788, 14.47421,  (SELECT start_time + INTERVAL '125 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '125 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 12, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL),
(NOW(), 163, NULL, 163, 'U55Z2P', 68.9, NULL, 50.13257, 14.47052,   (SELECT start_time + INTERVAL '130 seconds' FROM init_values), (SELECT start_timestamp + INTERVAL '130 seconds' FROM init_values), true, '2019-05-26T09:13:00Z_100115_115_95', NULL, NULL, NULL, NOW(), NULL, 10, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'on_track', NULL);

WITH init_values AS (SELECT DATE_TRUNC('minute', NOW())::TIME AS start_time, DATE_TRUNC('minute', NOW()) AS start_timestamp)
INSERT INTO vehiclepositions_trips (cis_line_id, cis_trip_number, run_number, cis_line_short_name, created_at, gtfs_route_id, gtfs_route_short_name, gtfs_trip_id, id, updated_at, start_cis_stop_id, start_cis_stop_platform_code, start_time, start_timestamp, vehicle_type_id, wheelchair_accessible, create_batch_id, created_by, update_batch_id, updated_by, agency_name_scheduled, origin_route_name, agency_name_real, vehicle_registration_number, gtfs_trip_headsign, gtfs_route_type, last_position_id, gtfs_trip_short_name)
VALUES (100115, 95, 39, '115', '2019-05-26 08:43:16.991+00', 'L115', '115', '115_107_180501', '2019-05-26T09:13:00Z_100115_115_95', '2019-05-26 09:56:37.275+00', 56714, 'D', (SELECT start_time - INTERVAL '10 minutes' FROM init_values), (SELECT start_timestamp - INTERVAL '10 minutes' FROM init_values), 3, true, NULL, NULL, NULL, NULL, 'ARRIVA City', 433, 'ARRIVA City', 8581, NULL, 1, 10, '115_short');

WITH init_values AS (SELECT DATE_TRUNC('minute', NOW()) AS start_timestamp)
INSERT INTO vehiclepositions_runs (id, route_id,run_number,line_short_name,registration_number,msg_start_timestamp,msg_last_timestamp) VALUES
('95_52_8509_2021-06-10T16:00:00+02:00', '95', '52', '95', '8509', (SELECT start_timestamp - INTERVAL '4 hours' FROM init_values), (SELECT start_timestamp - INTERVAL '5 minutes' FROM init_values)),
('96_51_5890_2021-06-10T17:00:00+02:00', '96', '51', '96', '5890', (SELECT start_timestamp - INTERVAL '2 hours' FROM init_values), (SELECT start_timestamp - INTERVAL '10 minutes' FROM init_values)),
('22_5_1234_2021-06-10T18:00:00+02:00', '22', '5', '22', '1234', (SELECT start_timestamp - INTERVAL '49 hours' FROM init_values), (SELECT start_timestamp - INTERVAL '49 hours' FROM init_values));


--- NOT PUBLIC TRIP

WITH init_values AS (SELECT DATE_TRUNC('minute', NOW())::TIME AS start_time, DATE_TRUNC('minute', NOW()) AS start_timestamp)
INSERT INTO "vehiclepositions_trips"("cis_line_id","cis_trip_number","run_number","cis_line_short_name","created_at","gtfs_route_id","gtfs_route_short_name","gtfs_trip_id","id","updated_at","start_cis_stop_id","start_cis_stop_platform_code","start_time","start_timestamp","vehicle_type_id","wheelchair_accessible","create_batch_id","created_by","update_batch_id","updated_by","agency_name_scheduled","origin_route_name","agency_name_real","vehicle_registration_number","gtfs_trip_headsign","start_asw_stop_id","gtfs_route_type","gtfs_block_id","last_position_id","is_canceled","end_timestamp","gtfs_trip_short_name")
VALUES
(NULL,NULL,1,NULL,(SELECT start_timestamp FROM init_values),NULL,NULL,NULL,E'NOT_PUBLIC_67_1_2021-06-28T15:05:00+02:00_2210',(SELECT start_timestamp FROM init_values),NULL,NULL,NULL,NULL,2,FALSE,NULL,NULL,NULL,NULL,E'DP PRAHA',E'67',E'DP PRAHA',2210,NULL,NULL,0,NULL,22,NULL,NULL,NULL);

WITH init_values AS (SELECT DATE_TRUNC('minute', NOW())::TIME AS start_time, DATE_TRUNC('minute', NOW()) AS start_timestamp)
INSERT INTO "vehiclepositions_positions"("created_at","delay","delay_stop_arrival","delay_stop_departure","next_stop_id","shape_dist_traveled","is_canceled","lat","lng","origin_time","origin_timestamp","is_tracked","trips_id","create_batch_id","created_by","update_batch_id","updated_at","updated_by","id","bearing","cis_last_stop_id","cis_last_stop_sequence","last_stop_id","last_stop_sequence","next_stop_sequence","speed","last_stop_arrival_time","last_stop_departure_time","next_stop_arrival_time","next_stop_departure_time","asw_last_stop_id","state_process","state_position","this_stop_id","this_stop_sequence","tcp_event","last_stop_headsign","last_stop_name","next_stop_name","this_stop_name")
VALUES (E'2021-03-17 10:00:00.06+01',NULL,NULL,NULL,NULL,NULL,NULL,50.08885,14.42999,(SELECT start_time FROM init_values),(SELECT start_timestamp FROM init_values),true,E'NOT_PUBLIC_67_1_2021-06-28T15:05:00+02:00_2210',NULL,NULL,NULL,E'2021-03-17 10:00:00.06+01',NULL,22,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,E'10190002',E'processed',E'not_public',E'04800002',NULL,E'P',NULL,NULL,NULL,NULL);

--- ^^^


--- RUNS & RUNS MESSAGES
INSERT INTO vehiclepositions_runs (id,route_id,run_number,line_short_name,registration_number,msg_start_timestamp,create_batch_id,created_at,created_by,update_batch_id,updated_at,updated_by,msg_last_timestamp,wheelchair_accessible) VALUES
	 ('8_10_9445_2022-12-06T06:20:58+01:00','8','10','8','9445','2022-12-06 06:20:58.000',NULL,'2022-12-06 06:20:58.390',NULL,NULL,'2022-12-06 23:46:09.886',NULL,NOW(),true);
INSERT INTO vehiclepositions_runs_messages (runs_id,lat,lng,actual_stop_asw_id,actual_stop_timestamp_real,actual_stop_timestamp_scheduled,last_stop_asw_id,packet_number,msg_timestamp,events,create_batch_id,created_at,created_by,update_batch_id,updated_at,updated_by) VALUES
	 ('8_10_9445_2022-12-06T06:20:58+01:00',50.10197,14.50258,'01340001','2022-12-06T14:15:42.000Z','2022-12-06T14:16:00.000Z','01260003','2257698',NOW() - interval '23 MINUTES','P',NULL,NULL,NULL,NULL,'2022-12-06 15:15:46.117',NULL),
	 ('8_10_9445_2022-12-06T06:20:58+01:00',50.09716,14.40401,'01630001','2022-12-06T11:17:36.000Z','2022-12-06T11:16:00.000Z','01440002','2210020',NOW() - interval '40 MINUTES','P',NULL,'2022-12-06 12:17:42.005',NULL,NULL,'2022-12-06 12:17:42.005',NULL),
	 ('8_10_9445_2022-12-06T06:20:58+01:00',50.09811,14.41059,'06920002','2022-12-06T14:48:54.000Z','2022-12-06T14:43:00.000Z','01260003','2268563',NOW() - interval '230 MINUTES','O',NULL,'2022-12-06 15:48:58.003',NULL,NULL,'2022-12-06 15:48:58.003',NULL),
	 ('8_10_9445_2022-12-06T06:20:58+01:00',50.09654,14.43221,'04390001','2022-12-06T17:23:23.000Z','2022-12-06T17:17:00.000Z','01440002','2321771',NOW() - interval '60 MINUTES','P',NULL,'2022-12-06 18:23:27.102',NULL,NULL,'2022-12-06 18:23:27.102',NULL);
--- ^^^
