--- CODE TO CREATE & MAINTAIN DATABASE STRUCTURE ---

-- Activate PostGIS Extension 
CREATE EXTENTION postgis;

-- Create Schema
CREATE SCHEMA slched; 

-- Make camp and date table for geomoetry
CREATE TABLE slched.camp_date(
poly_ID int PRIMARY KEY,
camp_id int,
cd_tents int,
cd_geog geography(POLYGON),
date date
);

-- Make camps table to hold ID and names
CREATE TABLE slched.camps(
camp_id int PRIMARY KEY,
camp_name VARCHAR(50)
);

-- Make dates table to hold time and date information
CREATE TABLE slched.dates(
date date PRIMARY KEY,
date_time_start time,
date_time_end time,
date_time time
); 

-- Make weather table to hold temp and precipitation information
CREATE TABLE slched.weather(
w_date date PRIMARY KEY,
w_temp_max int,
w_temp_min int,
w_temp_avg float,
w_temp_departure float,
w_hdd int,
w_cdd int,
w_precip float,
w_snow_new float,
w_snow_depth float
); 

-- Update Weather Table
DROP TABLE slched.weather;

CREATE TABLE slched.weather(
w_date date PRIMARY KEY,
w_temp_max int,
w_temp_min int,
w_temp_avg float,
w_temp_departure float,
w_hdd int,
w_cdd int,
w_precip float,
w_snow_new float,
w_snow_depth float
); 


--- CODE TO RESTORE BACKUPS ---

-- Use to restore a plain text backup using psql
psql -U <username> -d <database name> -1 \i D:/Database/file


--- SOME BASIC QUIEREIS TO BGIN ANALYSIS ---

-- Tent and Camp Counts and Areas
SELECT c.date, 
	   count(c.camp_id) as Camps_Documented,
	   sum(c.cd_tents) as Tents_Documetned,
	   sum(c.cd_tents)/count(c.camp_id) as Tents_per_Camp,
	   sum(st_area(c.cd_geog)) as Total_Camp_Area,
	   avg(st_area(c.cd_geog)) as Average_Camp_area,
	   sum(st_area(c.cd_geog))/sum(c.cd_tents) as Average_tent_size
FROM slched.camp_date as c
GROUP BY c.date
ORDER BY c.date asc


-- Each encampments days recorded, average area, and tent count 
SELECT n.camp_name as Camp,
	   count(distinct c.date) as Days_recorded,
	   avg(st_area(c.cd_geog)) as Average_Area, 
	   avg(c.cd_tents) as Average_Tents
FROM slched.camp_date as c
LEFT JOIN slched.camps as n
ON c.camp_id = n.camp_id
GROUP BY n.camp_name
ORDER BY count(distinct c.date) desc 

-- Each encampments days recorded, average area, and tent count 
SELECT n.camp_name as Camp,
	   c.date as Date,
	   st_area(c.cd_geog) as Area, 
	   c.cd_tents as Tents
FROM slched.camp_date as c
LEFT JOIN slched.camps as n
ON c.camp_id = n.camp_id
WHERE n.camp_name = KOA



-- By recording hour how many days documented, how many camps/tents seen, average number of camps/tents seen
SELECT  extract(hour from d.date_time) as time_of_collection, 
		count(distinct(cd.date)) as days_documented,
		count(distinct(cd.camp_id)) as unique_camps_seen,
		count(cd.camp_id) as total_camps_seen,
		count(cd.camp_id)/count(distinct(cd.date)) as average_camps_seen,
		sum(cd.cd_tents) as total_tents_counted,
		sum(cd.cd_tents)/count(distinct(cd.date)) as average_tents_seen
FROM slched.camp_date as cd
LEFT JOIN slched.dates as d
ON cd.date = d.date
GROUP BY extract(hour from d.date_time)
ORDER BY extract(hour from d.date_time) asc

-- By recording length how many days documented, how many camps/tents seen, average number of camps/tents seen
SELECT  extract(hour from d.date_time_total) as time_spent_recording, 
		count(distinct(cd.date)) as days_documented,
		count(distinct(cd.camp_id)) as unique_camps_seen,
		count(cd.camp_id) as total_camps_seen,
		count(cd.camp_id)/count(distinct(cd.date)) as average_camps_seen,
		sum(cd.cd_tents) as total_tents_counted,
		sum(cd.cd_tents)/count(distinct(cd.date)) as average_tents_seen
FROM slched.camp_date as cd
LEFT JOIN slched.dates as d
ON cd.date = d.date
GROUP BY extract(hour from d.date_time_total)
ORDER BY extract(hour from d.date_time_total) asc

-- Number of times a camp has moved, and the Min/Max/AVG distance of those moves
WITH camp_dist AS(

		WITH name_date AS( -- joins names to dates/geometry
		SELECT cd.*, c.camp_name
		FROM slched.camp_date as cd
		LEFT JOIN slched.camps as c
		ON cd.camp_id = c.camp_id
		)
			SELECT -- performs a loop comparing distance between camps
				cd1.date,
				cd1.camp_id,
				cd1.camp_name,
				st_distance(st_centroid(cd1.cd_geog), st_centroid(cd2.cd_geog)) as dist,
				cd2.camp_name as closest_camp,
				cd2.date
			FROM name_date as cd1
			CROSS JOIN LATERAL(
				SELECT cd2.camp_name, cd2.cd_geog, cd2.date
				FROM name_date as cd2
				WHERE cd1.camp_name = cd2.camp_name  -- compares camps with the same name
					and (cd1.date + interval '3 Day') = cd2.date  -- where the move happened 3 days (one recording) after another
					and st_distance(st_centroid(cd1.cd_geog), st_centroid(cd2.cd_geog)) != 0 -- don't include values of 0 (or no movement)
				ORDER BY cd2.cd_geog <-> cd1.cd_geog
			) as cd2
		)

SELECT 
	camp_name as name,
	count(distinct dist) as times_moved,
	min(dist) as min_movement,
	max(dist) as max_movement,
	avg(dist) as avg_movement
FROM camp_dist
GROUP BY camp_name
ORDER BY count(distinct dist) desc;

-- Camps Nearest Neighbor (and their distance apart) ordered by date 
WITH camp_dist AS(

	WITH name_date AS( -- joins names to dates/geometry
		SELECT cd.*, c.camp_name
		FROM slched.camp_date as cd
		LEFT JOIN slched.camps as c
		ON cd.camp_id = c.camp_id
		)
		
			SELECT -- performs a loop comparing distance between camps
				cd1.date,
				cd1.camp_id,
				cd1.camp_name,
				st_distance(st_centroid(cd1.cd_geog), st_centroid(cd2.cd_geog))  as dist,
				cd2.camp_name as closest_camp
			FROM name_date as cd1
			CROSS JOIN LATERAL(
				SELECT cd2.camp_name, cd2.cd_geog
				FROM name_date as cd2
				WHERE cd1.camp_name != cd2.camp_name -- compares camps with different names
					and cd1.date = cd2.date -- which were documented on the same date
				ORDER BY cd2.cd_geog <-> cd1.cd_geog
				LIMIT 1
			) as cd2
		)

SELECT 
	date,
	camp_name as name,
	dist,
	closest_camp
FROM camp_dist
ORDER BY date asc, dist desc

-- Min/Max/Avg Distance Between Camps per date 
WITH camp_dist AS(

	WITH name_date AS( -- joins names to dates/geometry
		SELECT cd.*, c.camp_name
		FROM slched.camp_date as cd
		LEFT JOIN slched.camps as c
		ON cd.camp_id = c.camp_id
		)
		
			SELECT -- performs a loop comparing distance between camps
				cd1.date,
				cd1.camp_id,
				cd1.camp_name,
				st_distance(st_centroid(cd1.cd_geog), st_centroid(cd2.cd_geog))  as dist,
				cd2.camp_name as closest_camp
			FROM name_date as cd1
			CROSS JOIN LATERAL(
				SELECT cd2.camp_name, cd2.cd_geog
				FROM name_date as cd2
				WHERE cd1.camp_name != cd2.camp_name -- compares camps with different names
					and cd1.date = cd2.date -- which were documented on the same date
				ORDER BY cd2.cd_geog <-> cd1.cd_geog
				LIMIT 1
			) as cd2
		)

SELECT 
	date,
	count(distinct camp_name) as camp_count,
	min(dist) as min_dist_apart,
	max(dist) as max_dist_apart,
	avg(dist) as avg_dist_apart
FROM camp_dist
GROUP BY date
ORDER BY date asc


-- Tent and Camp Counts and Areas along with all Weather Data
WITH camp_info AS(

	SELECT 	c.date, 
			count(c.camp_id) as Camps_Documented,
			sum(c.cd_tents) as Tents_Documented,
			sum(c.cd_tents)/count(c.camp_id) as Tents_per_Camp,
			sum(st_area(c.cd_geog)) as Total_Camp_Area,
			avg(st_area(c.cd_geog)) as Average_Camp_area,
			sum(st_area(c.cd_geog))/sum(c.cd_tents) as Average_tent_size
	FROM slched.camp_date as c
	GROUP BY c.date
	ORDER BY c.date asc
		)
SELECT 
	w.*,
	ci.*
FROM camp_info as ci
RIGHT JOIN slched.weather as w
ON w.w_date = ci.date