-- -----------------------------------------------------------------------------
-- Quantitative validation queries against the original SALVIAS database
-- -------------------------------------------------------------------------

-- ------------------
-- Set variables specific to this source
-- Currently configured for SALVIAS
-- ------------------

SET search_path TO "SALVIAS";

-- ------------------
-- 1. Count of projects
-- ------------------
CREATE OR REPLACE VIEW _plots_01_count_of_projects AS
SELECT COUNT(*) AS projects
FROM projects
LIMIT 10
;

-- ------------------
-- 2. List of project names
-- ------------------
CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS
SELECT project_name AS projectname
FROM projects
LIMIT 10
;

-- ------------------
-- 3. Count of all plots in this source
-- ------------------
CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS
SELECT COUNT(DISTINCT "SiteCode") AS plots
FROM projects p JOIN "PlotMetadata" pm
ON p.project_id=pm.project_id
LIMIT 10
;

-- ------------------
-- 4. Count of plots in each project in this source
-- ------------------
CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
SELECT p.project_name, COUNT(*) AS plots
FROM projects p JOIN "PlotMetadata" pm
ON p.project_id=pm.project_id
GROUP BY p.project_name
LIMIT 10
;

-- ------------------
-- 5.List of plot codes by project
-- ------------------
CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS
SELECT p.project_name, pm."SiteCode"
FROM projects p JOIN "PlotMetadata" pm
ON p.project_id=pm.project_id
LIMIT 10
;

-- ------------------
-- 6. List of plots with stem measurements
-- ------------------
CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
SELECT DISTINCT p.project_name, pm."SiteCode"
FROM projects p JOIN "PlotMetadata" pm
JOIN "PlotObservations" po JOIN stems s
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND po."PlotObsID"=s.plotobs_id
GROUP BY p.project_name, "SiteCode"
ORDER BY p.project_name, "SiteCode"
LIMIT 10
;

-- ------------------
-- 7.List of plots with counts of individuals per species
-- ------------------
CREATE OR REPLACE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS
SELECT DISTINCT p.project_name, pm."SiteCode"
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND po."NoInd">1
GROUP BY p.project_name, "SiteCode"
ORDER BY p.project_name, "SiteCode"
LIMIT 10
;

-- ------------------
-- 8.List of plots which use percent cover
-- ------------------
CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
SELECT DISTINCT p.project_name, pm."SiteCode"
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND cover_percent IS NOT NULL
GROUP BY p.project_name, "SiteCode"
ORDER BY p.project_name, "SiteCode"
LIMIT 10
;

-- ------------------
-- 9.List of plots which use line-intercept
-- ------------------
CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
SELECT DISTINCT p.project_name, pm."SiteCode"
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND intercept_cm IS NOT NULL
GROUP BY p.project_name, "SiteCode"
ORDER BY p.project_name, "SiteCode"
LIMIT 10
;

-- ------------------
-- 10. Count of individuals per plot in each project
--
-- Method: Sum on NoInd (number of individuals) column.
-- Note 1: `individuals` should be NULL for plots which use percent cover
-- or line-intercept methods.
-- Note 2: Do not count records in PlotObservations table, as this will give
-- incorrect total for plots such as Gentry plot, which count individuals
-- per species per subplot
-- ------------------
CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
SELECT p.project_name, "SiteCode" AS plotcode,
SUM("NoInd") AS individuals
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
GROUP BY p.project_name, "SiteCode"
ORDER BY p.project_name, "SiteCode"
LIMIT 10
;

-- ------------------
-- 11. Count of stems per plot in each project
--
-- Method: count records in stems table
-- ------------------
CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
SELECT p.project_name, "SiteCode" AS plotcode,
COUNT(DISTINCT stem_id) AS stems
FROM projects p JOIN "PlotMetadata" pm
JOIN "PlotObservations" po JOIN stems s
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND po."PlotObsID"=s.plotobs_id
GROUP BY p.project_name, "SiteCode"
ORDER BY p.project_name, "SiteCode"
LIMIT 10
;

-- ------------------
-- 12. Count of verbatim taxa per plot in each project
--
-- Note: Assumes identical concatenation of taxonomic fields
--       to form verbatim taxon name in vegBIEN
-- ------------------
CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
SELECT project_name, plotcode, COUNT(taxon) AS taxa
FROM
(
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
))) AS taxon
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
) AS a
GROUP BY project_name, plotcode
ORDER BY project_name, plotcode
LIMIT 10
;

-- ------------------
-- 13. List of distinct verbatim taxa in each plot in each project
--
-- Note: Assumes identical concatenation of taxonomic fields
--       to form verbatim taxon name in vegBIEN.
-- ------------------
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
))) AS taxon
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
ORDER BY p.project_name, "SiteCode", taxon
LIMIT 10
;

-- ------------------
-- 14. Count of individuals per (verbatim) taxon per plot in each project
--
-- Note: Assumes identical concatenation of taxonomic fields
--       to form verbatim taxon name in vegBIEN
-- ------------------
CREATE OR REPLACE VIEW _plots_14_count_of_individuals_per_verbatim_taxon_per_plot_in_each_project AS
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
))) AS taxon,
SUM("NoInd") AS individuals
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
GROUP BY p.project_name, "SiteCode", taxon
ORDER BY p.project_name, "SiteCode", taxon
LIMIT 10
;

-- ------------------
-- 15. Percent cover of each (verbatim) taxon in each plot in each project
--      where percent cover is recorded
--
-- Note 1: Assumes identical concatenation of taxonomic fields
--       to form verbatim taxon name in vegbien
-- Note 2: Grouping mean cover for entire plot allows inclusion of plots with
--       subplots (separate cover measure for each species in each subplot), 
--       plots with strata (separate cover measures for each species in each
--       stratum), and plots without subplots or strata (one cover measure
--       per species per plot) in same query.
-- Note 3: currently, there are no SALVIAS percent cover plots in BIEN
-- ------------------
CREATE OR REPLACE VIEW _plots_15_percent_cover_of_each_verbatim_taxon_in_each_plot_in_each_project AS
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
))) AS taxon,
AVG(cover_percent) AS mean_cover
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND cover_percent IS NOT NULL
GROUP BY p.project_name, "SiteCode", taxon
ORDER BY p.project_name, "SiteCode", taxon
LIMIT 10
;

-- ------------------
-- 16. Intercept values for each (verbatim) taxon in each plot in each project
--      where line-intercept values are recorded
--
-- Note 1: Assumes identical concatenation of taxonomic fields
--       to form verbatim taxon name in vegbien
-- Note 2: Grouping mean cover for entire plot combines plots with
--       subplots (separate cover measure for each species in each subplot) with
--       plots with strata (separate cover measures for each species in each
--       stratum) with plots without subplots or strata (one cover measure
--       per species per plot.
-- ------------------
CREATE OR REPLACE VIEW _plots_16_intercept_values_for_each_verbatim_taxon_in_each_plot_in_each_project AS
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
))) AS taxon,
intercept_cm
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND intercept_cm IS NOT NULL
ORDER BY p.project_name, "SiteCode", taxon
LIMIT 10
;

-- ------------------
-- 17. Count of subplots per plot, for each project
-- ------------------
CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
SELECT project_name, pm."SiteCode" AS plotcode, COUNT(DISTINCT "Line") as subplots
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
GROUP BY project_name, pm."SiteCode"
ORDER BY project_name, pm."SiteCode"
LIMIT 10
;

-- ------------------
-- 18. List of subplots codes for each plot for each project
--
-- Note: includes plots with and without subplots
-- ------------------
CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
SELECT DISTINCT project_name, pm."SiteCode" AS plotcode, "Line" as subplot
FROM projects p JOIN "PlotMetadata" pm JOIN "PlotObservations" po
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
AND pm."SiteCode" IS NOT NULL
ORDER BY project_name, pm."SiteCode", subplot
LIMIT 10
;