-- -----------------------------------------------------------------------------

-- Quantitative validation queries on the BIEN database

-- 

-- Applies to SALVIAS, or any datasource with all of the following attributes:

-- - Plots

-- - Comprised of multiple datasets organized as "projects"

-- - Maximum 2-level nesting (subplots within plots)

-- - One or more plots contain individual observations with stem measurements

-- - One or more plots contain aggregate observations of counts of individuals within species

-- - One or more plots contain aggregate observations of cover by species

-- 

-- Requires list of projects from source db manager, in three groups: (1) individual observation

--   plot, (2) aggregate individual count plots, (3) aggregate percent cover plots

-- -------------------------------------------------------------------------

-- ------------------

-- Set variables specific to this source

-- Currently configured for SALVIAS

-- ------------------

SET search_path TO public_validations, public;

-- ------------------

-- 1. Count of projects

-- Check: identical count in source db

-- ------------------

CREATE OR REPLACE VIEW _plots_01_count_of_projects AS

SELECT COUNT(*) AS projects

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=current_schema; 

-- ------------------

-- 2. List of project names

-- Check: join to source db returns same number of rows

-- ------------------

CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS

SELECT p.projectname

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=current_schema; 

-- ------------------

-- 3. Count of all plots in this source

-- Check: identical count in source db

-- ------------------

CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS

SELECT COUNT(*) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=current_schema;

-- ------------------

-- 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.projectname, COUNT(DISTINCT l.location_id) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=current_schema

GROUP BY p.projectname;

-- ------------------

-- 5.List of plot codes by project

-- Check: join to source db by all columns returns same number of rows

-- ------------------

CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS

SELECT p.projectname, l.authorlocationcode AS "plotCode"

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=current_schema;

-- ------------------

-- 6. List of plots with stem measurements

-- ------------------

CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS

SELECT authorlocationcode

FROM top_plot

WHERE source_id = source_by_shortname(current_schema)

AND EXISTS(

	SELECT NULL

	FROM location

	JOIN locationevent USING (location_id)

	JOIN taxonoccurrence USING (locationevent_id)

	JOIN aggregateoccurrence USING (taxonoccurrence_id)

	JOIN plantobservation USING (aggregateoccurrence_id)

	JOIN stemobservation USING (plantobservation_id)

	WHERE location.top_plot = top_plot.location_id

	LIMIT 1

)

ORDER BY authorlocationcode

;

-- ------------------

-- 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

-- ------------------

-- 8.List of plots which use percent cover

-- ------------------

CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS

-- ------------------

-- 9.List of plots which use line-intercept

-- ------------------

CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS

-- ------------------

-- 10. Count of individuals per plot in each project

--

-- Method: count taxonObservations

-- Note 1: `individuals` should be NULL for plots which use percent cover

-- or line-intercept methods.

-- Note 2: Does this method requires that plots have 2-level nesting of

--       subplots within plots?

-- ------------------

CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS

select p.projectname, l.authorlocationcode as plotcode, sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=current_schema

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

-- ------------------

-- 11. Count of stems per plot in each project

-- 

-- Method: count records in stemobservation table

-- ------------------

CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS

select p.projectname, l.authorlocationcode as plotcode, count(distinct stemobservation_id) as stems

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join plantobservation po on po.aggregateoccurrence_id=ao.aggregateoccurrence_id

join stemobservation so on so.plantobservation_id=po.plantobservation_id

where s.shortname=current_schema 

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

-- ------------------

-- 12. Count of verbatim taxa per plot in each project

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

-- NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY

-- ------------------

CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')))

as taxa

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=current_schema and td.isoriginal='t'

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

-- ------------------

-- 13. List of distinct verbatim taxa in each plot in each project

-- 

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

-- ------------------

CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=current_schema and td.isoriginal='t'

order by p.projectname, l.authorlocationcode, taxon;

-- ------------------

-- 14. Count of individuals per (verbatim) taxon per plot in each project 

-- 

-- Note: Must do equivalent concatenation of taxonomic field in source db.

-- ------------------

CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project AS

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=current_schema and td.isoriginal='t'

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

-- ------------------

-- 15. Percent cover of each (verbatim) taxon in each plot in each project

-- Applies to: aggregate-cover plots only

-- Method: sums percent cover in aggregateoccurrence (as recorded in coverindex)

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must do equivalent concatenation of taxonomic field in source db.

-- ------------------

CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project AS

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ci.coverpercent) as totalpercentcover

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join coverindex ci on ao.coverindex_id=ci.coverindex_id

where s.shortname=current_schema and td.isoriginal='t'

and ci.coverpercent is not null

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

-- ------------------

-- 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 origina db

-- 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_intercepts_for_each_verb_taxon_in_each_plot_in_each_project AS

-- ------------------

-- 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 p.projectname, l.authorlocationcode as plotcode, 

count(distinct sub_locationevent.locationevent_id) as subplots

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

where s.shortname=current_schema

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

-- ------------------

-- 18. List of subplots codes for each plot for each project

-- DOESN'T YET WORK PROPERLY

-- ------------------

CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS

select p.projectname, 

l.authorlocationcode as plotcode, 

sub_locationevent.authoreventcode as "subplotCode"

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on sub_locationevent.location_id=l.location_id

where s.shortname=current_schema

order by p.projectname, l.authorlocationcode;