---------- Creating the database ----------
--Log into the generic postgres database and create a new database titled "invasion"
CREATE DATABASE invasion;
--Log out of postgres and log into your invasion database
---------- Importing the data ----------
--For this project we will create 5 tables. When creating tables you must specify the table name, followed by a list of all the fields (columns) in your table. When specifying the fields, you must also state the data type (ie; text, numeric, boolean, timestamp, etc...)
--Once a table is created, we will populate the table with data from the appropriate CSV file.
CREATE TABLE trt (tank int,treatment text); --This creates a table titled "trt" with two columns: tank(integer) and treatment(text)

\COPY trt FROM '/media/sf_C_DRIVE/Admin_Rowan/Mir_GuestLecture/Updated_Code_7_2014/Treatment_Assignments.csv' WITH CSV HEADER; --This statement populates the table titled "trt" with data from the CSV file titled "Treatment_Assignments.csv". Note you have to specify the folder path where the Treatment_Assignments.csv file exits.

CREATE TABLE asur (tank int,species text,surv numeric);
\COPY asur FROM '/media/sf_C_DRIVE/Admin_Rowan/Mir_GuestLecture/Updated_Code_7_2014/Amphib_Survival.csv' WITH CSV HEADER;
CREATE TABLE amass (tank int,species text,mass numeric);
\COPY amass FROM '/media/sf_C_DRIVE/Admin_Rowan/Mir_GuestLecture/Updated_Code_7_2014/Amphib_Mass.csv' WITH CSV HEADER;
CREATE TABLE snailzoo (tank int,taxa text,density numeric);
\COPY snailzoo FROM '/media/sf_C_DRIVE/Admin_Rowan/Mir_GuestLecture/Updated_Code_7_2014/Snails_and_Zooplankton.csv' WITH CSV HEADER;
CREATE TABLE phyto (tank int,run int, fluor numeric);
\COPY phyto FROM '/media/sf_C_DRIVE/Admin_Rowan/Mir_GuestLecture/Updated_Code_7_2014/Phytoplankton.csv' WITH CSV HEADER;
--Now lets view what is in the data base
---\d shows you what is in the database
---\dt shows you all the tables in the database
---\dv shows you all the views (saved queries) in the database
\d
--Now lets look at the details of our amphibian survival (which we called asur) table:
\d asur
---------- Views (Saved Queries) to Answer Questions ----------

---------- Amphibian Survival ----------
--For each species, count the average number of amphibians that survived in each tank and
--calculate the survival percentage: 100*(avg(#_survived)/starting_population)
CREATE OR REPLACE VIEW amphib_survQRY AS SELECT trt.treatment AS treatment, avg(CASE WHEN asur.species = 'PSRE' THEN asur.surv END)/15*100 AS psre_surv, avg(CASE WHEN asur.species = 'BUBO' THEN asur.surv END)/15*100 AS bubo_surv, avg(CASE WHEN asur.species = 'TATO' THEN asur.surv END)/10*100 AS tato_surv FROM asur JOIN trt ON trt.tank=asur.tank GROUP BY treatment;

--to view the amphibian survival query:
SELECT * FROM amphib_survQRY;

--amphibian survival standard error query:

CREATE OR REPLACE VIEW amphib_surv_SE_QRY AS SELECT trt.treatment AS treatment, stddev(CASE WHEN asur.species = 'PSRE' THEN asur.surv END)/15/sqrt(5)*100 AS psre_surv_se, stddev(CASE WHEN asur.species = 'BUBO' THEN asur.surv END)/15/sqrt(5)*100 AS bubo_surv_se, stddev(CASE WHEN asur.species = 'TATO' THEN asur.surv END)/10/sqrt(5)*100 AS tato_surv_se FROM asur JOIN trt ON trt.tank=asur.tank GROUP BY treatment;

---------- Amphibian Mass ----------

--step 1 query: calculating mean mass for each tank and each species

CREATE OR REPLACE VIEW amphib_mass_step1qry AS SELECT amass.tank AS tank, trt.treatment AS treatment, avg(CASE WHEN amass.species = 'PSRE' THEN amass.mass END) AS psre_mass, avg(CASE WHEN amass.species = 'BUBO' THEN amass.mass END) AS bubo_mass, avg(CASE WHEN amass.species = 'TATO' THEN amass.mass END) AS tato_mass FROM amass JOIN trt ON trt.tank = amass.tank GROUP BY trt.treatment, amass.tank;

--final query: mean and standard error of amphibian mass for each treatment and each species
CREATE OR REPLACE VIEW amphib_mass_final_qry AS SELECT amphib_mass_step1qry.treatment, Avg(amphib_mass_step1qry.psre_mass) AS MEAN_PSRE, Avg(amphib_mass_step1qry.bubo_mass) AS MEAN_BUBO, Avg(amphib_mass_step1qry.tato_mass) AS MEAN_TATO, STDDEV(amphib_mass_step1qry.psre_mass)/(Sqrt(COUNT(amphib_mass_step1qry.psre_mass))) AS SE_PSRE, STDDEV(amphib_mass_step1qry.bubo_mass)/(Sqrt(COUNT(amphib_mass_step1qry.bubo_mass))) AS SE_BUBO,STDDEV(amphib_mass_step1qry.tato_mass)/(Sqrt(COUNT(amphib_mass_step1qry.tato_mass))) AS SE_TATO FROM amphib_mass_step1qry GROUP BY amphib_mass_step1qry.treatment;


---------- Snail and Zooplankton Abundance ----------
--Mean Query
CREATE OR REPLACE VIEW snail_zoo_dens_mean_QRY AS SELECT trt.treatment AS treatment, avg(CASE WHEN snailzoo.taxa = 'Snails' THEN snailzoo.density END) AS snail_density, sum(CASE WHEN snailzoo. taxa = 'Daphnia' OR snailzoo.taxa = 'Copepods' THEN snailzoo.density END)/5./6.9 AS zooplankton_density FROM snailzoo JOIN trt ON trt.tank=snailzoo.tank GROUP BY treatment;

--Standard Error Queries
CREATE OR REPLACE VIEW snail_dens_se_QRY AS SELECT trt.treatment AS treatment, stddev(CASE WHEN
snailzoo.taxa = 'Snails' THEN snailzoo.density END)/sqrt(count(CASE WHEN snailzoo.taxa = 'Snails' THEN snailzoo.density END)) AS snail_se FROM snailzoo JOIN trt ON trt.tank=snailzoo.tank GROUP BY treatment;

CREATE OR REPLACE VIEW zoo_dens_se_QRYstep1 AS SELECT snailzoo.tank AS tank, sum(CASE WHEN taxa = 'Daphnia' OR snailzoo.taxa = 'Copepods' THEN snailzoo.density END) AS zoo_sum FROM snailzoo GROUP BY tank;

CREATE OR REPLACE VIEW zoo_dens_se_QRY_final AS SELECT trt.treatment AS treatment, (stddev(zoo_dens_se_QRYstep1.zoo_sum)/sqrt(count(zoo_dens_se_QRYstep1.zoo_sum)))/6.9 AS zooplankton_se FROM zoo_dens_se_QRYstep1 JOIN trt ON trt.tank=zoo_dens_se_QRYstep1.tank GROUP BY treatment;

---------- Phytoplankton Fluorescence ----------

--step 1 query: calculating mean relative fluorescence for each tank and each species
CREATE OR REPLACE VIEW Phyto_Dens_QRY_step1 AS SELECT trt.treatment AS treatment, phyto.tank AS tank, Avg(phyto.fluor) AS phyto_fluor FROM phyto INNER JOIN trt ON phyto.tank = trt.tank GROUP BY treatment, trt.tank,phyto.tank;

--final query: mean and standard error of relative phytoplankton fluorescence for each treatment and each species
CREATE OR REPLACE VIEW Phyto_Dens_QRY AS SELECT Phyto_Dens_QRY_step1.treatment AS treatment, Avg(Phyto_Dens_QRY_step1.phyto_fluor) AS Mean_Phyto, STDDEV(Phyto_Dens_QRY_step1.phyto_fluor)/Sqrt(Count(Phyto_Dens_QRY_step1.phyto_fluor)) AS SE_Phyto FROM Phyto_Dens_QRY_step1 GROUP BY treatment;