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

-- 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 datasource '''SALVIAS'''

SET enable_seqscan = off;

SET join_collapse_limit = 1; -- turn it off

-- _plots_01_count_of_projects

 SELECT count(*) AS projects

   FROM (project p

   JOIN source s ON ((p.source_id = s.source_id)))

  WHERE (s.shortname = :datasource::text);

-- _plots_02_list_of_project_names

 SELECT p.projectname AS project_name

   FROM (project p

   JOIN source s ON ((p.source_id = s.source_id)))

  WHERE (s.shortname = :datasource::text);

-- _plots_03_count_of_all_plots_in_this_source

 SELECT count(*) AS plots

   FROM (((plot l

   JOIN locationevent le ON ((l.plot_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 = :datasource::text);

-- _plots_04_count_of_plots_in_each_project_in_this_source

 SELECT p.projectname AS project_name, 

    count(DISTINCT l.plot_id) AS plots

   FROM (((plot l

   JOIN locationevent le ON ((l.plot_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 = :datasource::text)

  GROUP BY p.projectname;

-- _plots_05_list_of_plot_codes_by_project

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code

   FROM (((plot l

   JOIN locationevent le ON ((l.plot_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 = :datasource::text);

-- _plots_06_list_of_plots_with_stem_measurements

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code

   FROM ((plot

   JOIN locationevent USING (plot_id))

   LEFT JOIN project USING (project_id))

  WHERE ((plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown

   FROM ((((locationevent locationevent_1

   JOIN taxonoccurrence USING (locationevent_id))

   JOIN aggregateoccurrence USING (taxonoccurrence_id))

   JOIN plantobservation USING (aggregateoccurrence_id))

   JOIN stemobservation USING (plantobservation_id))

  WHERE ((locationevent_1.plot_id = plot.plot_id) AND ((stemobservation.sourceaccessioncode IS NOT NULL) OR (stemobservation.authorstemcode IS NOT NULL)))

 LIMIT 1)))

  ORDER BY plot.authorlocationcode;

-- _plots_06a_list_of_stems

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code, 

    stemobservation.sourceaccessioncode AS stem_id

   FROM ((((((plot

   JOIN locationevent USING (plot_id))

   LEFT JOIN project USING (project_id))

   JOIN taxonoccurrence USING (locationevent_id))

   JOIN aggregateoccurrence USING (taxonoccurrence_id))

   JOIN plantobservation USING (aggregateoccurrence_id))

   JOIN stemobservation USING (plantobservation_id))

  WHERE ((plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND ((stemobservation.sourceaccessioncode IS NOT NULL) OR (stemobservation.authorstemcode IS NOT NULL)))

  ORDER BY project.projectname, plot.authorlocationcode, stemobservation.sourceaccessioncode;

-- _plots_07_list_of_plots_which_use_counts_of_indiv_per_species

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code

   FROM ((plot

   JOIN locationevent USING (plot_id))

   LEFT JOIN project USING (project_id))

  WHERE ((plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown

   FROM ((locationevent locationevent_1

   JOIN taxonoccurrence USING (locationevent_id))

   JOIN aggregateoccurrence USING (taxonoccurrence_id))

  WHERE ((locationevent_1.plot_id = plot.plot_id) AND (aggregateoccurrence.count > 1))

 LIMIT 1)))

  ORDER BY plot.authorlocationcode;

-- _plots_08_list_of_plots_which_use_percent_cover

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code

   FROM ((plot

   JOIN locationevent USING (plot_id))

   LEFT JOIN project USING (project_id))

  WHERE ((plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown

   FROM ((locationevent locationevent_1

   JOIN taxonoccurrence USING (locationevent_id))

   JOIN aggregateoccurrence USING (taxonoccurrence_id))

  WHERE ((locationevent_1.plot_id = plot.plot_id) AND (aggregateoccurrence.cover_fraction IS NOT NULL))

 LIMIT 1)))

  ORDER BY plot.authorlocationcode;

-- _plots_09_list_of_plots_which_use_line_intercept

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code

   FROM ((plot

   JOIN locationevent USING (plot_id))

   LEFT JOIN project USING (project_id))

  WHERE ((plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown

   FROM ((locationevent locationevent_1

   JOIN taxonoccurrence USING (locationevent_id))

   JOIN aggregateoccurrence USING (taxonoccurrence_id))

  WHERE ((locationevent_1.plot_id = plot.plot_id) AND (aggregateoccurrence.linecover_m IS NOT NULL))

 LIMIT 1)))

  ORDER BY plot.authorlocationcode;

-- _plots_10_count_of_individuals_per_plot_in_each_project

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    sum(ao.count) AS individuals

   FROM ((((((project p

   JOIN source s ON ((p.source_id = s.source_id)))

   JOIN place_visit ON ((p.project_id = place_visit.project_id)))

   JOIN locationevent le ON ((place_visit.locationevent_id = le.place_visit_id)))

   JOIN plot l ON ((place_visit.location_id = l.plot_id)))

   JOIN taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id)))

   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))

  WHERE (s.shortname = :datasource::text)

  GROUP BY p.projectname, l.authorlocationcode

  ORDER BY p.projectname, l.authorlocationcode;

/*

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?

*/

-- _plots_10a_aggregate_observation_individual_counts

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    o.sourceaccessioncode AS individual_id, 

    ao.count AS individuals

   FROM ((((((project p

   JOIN source s ON ((p.source_id = s.source_id)))

   JOIN place_visit ON ((p.project_id = place_visit.project_id)))

   JOIN locationevent le ON ((place_visit.locationevent_id = le.place_visit_id)))

   JOIN plot l ON ((place_visit.location_id = l.plot_id)))

   JOIN taxonoccurrence o ON ((le.locationevent_id = o.locationevent_id)))

   JOIN aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))

  WHERE (s.shortname = :datasource::text)

  ORDER BY p.projectname, l.authorlocationcode, ao.sourceaccessioncode;

/*

Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.

*/

-- _plots_11_count_of_stems_per_plot_in_each_project

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    count(DISTINCT so.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 plot l ON ((le.location_id = l.plot_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 = :datasource::text)

  GROUP BY p.projectname, l.authorlocationcode

  ORDER BY p.projectname, l.authorlocationcode;

/*

Method: count records in stemobservation table

*/

-- _plots_12_count_of_verbatim_taxa_per_plot_in_each_project

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    count(DISTINCT btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.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 plot l ON ((le.location_id = l.plot_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 = :datasource::text) AND (td.isoriginal = true))

  GROUP BY p.projectname, l.authorlocationcode

  ORDER BY p.projectname, l.authorlocationcode;

/*

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

*/

-- _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p

 SELECT DISTINCT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.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 plot l ON ((le.location_id = l.plot_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 = :datasource::text) AND (td.isoriginal = true))

  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));

/*

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

*/

-- _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr

 SELECT DISTINCT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.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 plot l ON ((le.location_id = l.plot_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 = :datasource::text) AND (td.isoriginal = true))

  GROUP BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))

  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));

/*

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

*/

-- _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro

 SELECT DISTINCT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.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 plot l ON ((le.location_id = l.plot_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 = :datasource::text) AND (td.isoriginal = true)) AND (ci.coverpercent IS NOT NULL))

  GROUP BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))

  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));

/*

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.

*/

-- _plots_16_intercepts_for_each_verb_taxon_in_each_plot_in_each_project

 SELECT DISTINCT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon, 

    sum(ao.linecover_m) AS intercept_cm

   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 plot l ON ((le.location_id = l.plot_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 = :datasource::text) AND (td.isoriginal = true)) AND (ao.linecover_m IS NOT NULL))

  GROUP BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))

  ORDER BY p.projectname, l.authorlocationcode, btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)));

/*

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.

*/

-- _plots_17_count_of_subplots_per_plot_for_each_project

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    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 plot l ON ((le.location_id = l.plot_id)))

  WHERE (s.shortname = :datasource::text)

  GROUP BY p.projectname, l.authorlocationcode

  ORDER BY p.projectname, l.authorlocationcode;

-- _plots_18_list_of_subplots_codes_for_each_plot_for_each_project

 SELECT p.projectname AS project_name, 

    l.authorlocationcode AS plot_code, 

    sub_locationevent.authoreventcode AS subplot_code

   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 plot l ON ((sub_locationevent.location_id = l.plot_id)))

  WHERE (s.shortname = :datasource::text)

  ORDER BY p.projectname, l.authorlocationcode;

/*

DOESN''T YET WORK PROPERLY

*/

-- _plots_19_count_of_censuses_per_plot_in_each_project

 SELECT project.projectname AS project_name, 

    plot.authorlocationcode AS plot_code, 

    count(*) AS inventories

   FROM ((plot plot(location_id)

   JOIN locationevent USING (location_id))

   LEFT JOIN project USING (project_id))

  WHERE (plot.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname))

  GROUP BY project.projectname, plot.authorlocationcode

  ORDER BY project.projectname, plot.authorlocationcode;

-- _plots_20_tnrs_input_names

 SELECT DISTINCT taxonlabel.taxonomicname AS tnrs_input_name

   FROM taxonlabel

  WHERE ((taxonlabel.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL));

-- _plots_21_tnrs_output_names

SET enable_hashjoin = off;

 SELECT DISTINCT taxon_scrub.scrubbed_unique_taxon_name AS tnrs_output_name

   FROM (taxonlabel

   JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))

  WHERE ((taxonlabel.source_id = ( SELECT source_by_shortname(:datasource::text) AS source_by_shortname)) AND (taxon_scrub."concatenatedScientificName" IS NOT NULL));

SET enable_hashjoin = DEFAULT;