--Using CTE to remove rows where nothing changed
--WITH livedata as (
--SELECT  _id,
--		is_operational,
--		queue_time,
--		queue_status_message,
--		is_open,
--		open_time,
--		close_time,
--		MIN(time_stamp) as time_stamp
--FROM knoebels."LZ_attractions_io" as livedata
--GROUP BY _id, is_operational, queue_time, queue_status_message, is_open, open_time, close_time
--)
SELECT 	livedata.time_stamp
		,livedata._id
	 	,poi.name
	 	,prices.price
		,is_operational
	 	,queue_time
	 	,queue_status_message
	 	,is_open
	 	,open_time
	 	,close_time
	 	,hand_stamp_included
	 	,bargain_night_included
	 	,capacity
	 	,duration
	 	,summary
	 	,keywords
	 	,default_image
	 	,location
		,location[0] as lattitude
		,location[1] as longitude
	 	,featured
	 	,wayfinding_enabled
	 	,visible_on_map
	 	,category
	 	,ceil(minimum_height_requirement*39.37) AS minimum_height_requirement
	 	,ceil(minimum_unaccompanied_height_requirement*39.37) AS minimum_unaccompanied_height_requirement
	 	,ceil(maximum_height_requirement*39.37) AS maximum_height_requirement
	 	,minimum_age_requirement
	 	,minimum_unaccompanied_age_requirement
	 	,maximum_age_requirement
	 	,restriction_summary
FROM knoebels."LZ_attractions_io" AS livedata
--FROM livedata
--This POI data comes from a massive JSON file hosted by attractions.io the app downloads it on first launch. Not sure how often it's updated or only as-needed
JOIN knoebels."LZ_attractions_io_poi" AS poi ON livedata._id = poi._id
-- This is my own "master" file for ride data, for data points not served by the attractions.io API. Handstamp inclusions/exclusions, capacity, duration, etc.
JOIN knoebels.ride_master rm ON livedata._id = rm.attractions_dot_io_id
-- fetch most recent price data, scraped from webpage, this can differ from the POI data used by the app
LEFT JOIN (SELECT row_number() OVER (PARTITION BY ride ORDER BY time_stamp DESC) AS row_number, * FROM knoebels.ride_data_in) prices ON prices.ride = rm.ride and prices.row_number = 1
--where name = 'Sklooosh'
--ORDER BY name, time_stamp