CREATE TABLE IF NOT EXISTS muni_data (
	id varchar(255),
	route varchar(255),
	direction varchar(255),
	heading integer,
	speed integer,
	report_time timestamp,
	lat float,
	lon float
);

CREATE TABLE IF NOT EXISTS segments (
	segment_id serial,
	route_id int,
	start_lat float,
	start_lon float,
	end_lat float,
	end_lon float,
	length float,
	avg_speed float
);

CREATE TABLE IF NOT EXISTS routes (
	route_id serial,
	route_name varchar(25),
	direction varchar(25)
);


--------------------
-- Raw GTFS data
--------------------
CREATE TABLE IF NOT EXISTS raw_routes (
	route_id int,
	agency_id varchar(25),
	route_name varchar(255),
	route_long_name varchar(255),
	route_desc varchar(25),
	route_type varchar(25),
	route_url varchar(25),
	route_color varchar(25),
	route_text_color varchar(25)
);

CREATE TABLE IF NOT EXISTS raw_stop_times (
	trip_id int,
	arrival_time time,
	departure_time time,
	stop_id int,
	stop_sequence int,
	stop_headsign varchar(25),
	pickup_type varchar(25),
	drop_off_type varchar(25),
	shape_dist_traveled varchar(25)
);

CREATE TABLE IF NOT EXISTS raw_stops (
	stop_id int,
	stop_name varchar(80),
	stop_desc varchar(255),
	stop_lat float,
	stop_lon float,
	zone_id varchar(10),
	stop_url varchar(255)
);

CREATE TABLE IF NOT EXISTS raw_trips (
	route_id int,
	service_id int,
	trip_id int,
	trip_headsign varchar(50),
	direction_id int,
	block_id int,
	shape_id int
);

CREATE TABLE IF NOT EXISTS raw_shapes (
	route varchar(50),
	shape_id int,
	shape_pt_lon float,
	shape_pt_lat float,
	shape_pt_sequence int,
	shape_dist_traveled float
);

COPY raw_routes FROM '/home/yian/Documents/code/joyofmuni/google_transit/routes.txt' (FORMAT CSV, DELIMITER ',', HEADER);
COPY raw_stop_times FROM '/home/yian/Documents/code/joyofmuni/google_transit/stop_times.txt' (FORMAT CSV, DELIMITER ',', HEADER);
COPY raw_stops FROM '/home/yian/Documents/code/joyofmuni/google_transit/stops.txt' (FORMAT CSV, DELIMITER ',', HEADER);
COPY raw_trips FROM '/home/yian/Documents/code/joyofmuni/google_transit/trips.txt' (FORMAT CSV, DELIMITER ',', HEADER);

select rid, max(num_stops) from (
	select raw_routes.route_id as rid,
		raw_trips.trip_id as tid,
		count(raw_stop_times.stop_sequence) as num_stops
	from raw_routes, raw_trips, raw_stop_times\
	where raw_routes.route_id = raw_trips.route_id and raw_stop_times.trip_id = raw_trips.trip_id
	group by rid, tid
) as A group by rid;

select raw_routes.route_id as rid,
		raw_trips.trip_id as tid
	from raw_routes, raw_trips, raw_stop_times
	where raw_routes.route_id = raw_trips.route_id and raw_routes.route_id = '1002' and raw_stop_times.trip_id = raw_trips.trip_id
	group by rid, tid
	having count(raw_stop_times.stop_sequence) = 39;

create table route_info as (select ST.trip_id, ST.stop_id, ST.stop_sequence, S.stop_name, S.stop_lat, S.stop_lon from raw_stop_times ST inner join raw_stops S on ( ST.stop_id = S.stop_id ) where trip_id = 6439016);
insert into route_info (select 1002 as route_id, ST.stop_id, ST.stop_sequence, S.stop_name, S.stop_lat, S.stop_lon, 0.0 as distance from raw_stop_times ST inner join raw_stops S on ( ST.stop_id = S.stop_id ) where trip_id = 6439016);
----------------------
-- Useful queries
----------------------

select route, direction, avg(speed) as speed, count(*), max(report_time) 
from muni_data where route in ('J', 'KT', 'L', 'M', 'N') group by route, direction order by speed;

select avg(speed) as speed, route, EXTRACT (HOUR FROM report_time) as thour
from muni_data where route in ('N') group by route, thour order by thour;




select * from muni_data, segments 
where lat >= start_lat and lat <= end_lat and lon >= start_lon and lon <= end_lon and segment_id=12353;

select route, avg(speed), muni_data.direction, start_lat, end_lat, start_lon, end_lon, EXTRACT (HOUR FROM report_time) as thour from muni_data, segments 
where lat >= start_lat and lat <= end_lat and lon >= start_lon and lon <= end_lon and segment_id=12365 
group by route, muni_data.direction, start_lat, end_lat, start_lon, end_lon, thour order by thour;

select round(cast(lat as numeric), 3) as lat, round(cast(lon as numeric), 3) as lon, route, avg(speed) as speed, count(*)
from muni_data
where route='%s' and direction='inbound' group by round(cast(lat as numeric), 3), round(cast(lon as numeric), 3), route, direction;

select start_lat, start_lon, segment_id from segments where route_id='N' order by segment_id;
