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

CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS

SELECT COUNT(DISTINCT "SiteCode") AS plots

ON p.project_id=pm.project_id

LIMIT 10

;

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

CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS

SELECT p.project_name, COUNT(*) AS plots

ON p.project_id=pm.project_id

GROUP BY p.project_name

LIMIT 10

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

CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS

SELECT p.project_name, pm."SiteCode"

ON p.project_id=pm.project_id

LIMIT 10

;

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

CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS

SELECT DISTINCT p.project_name, pm."SiteCode"

ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"

AND po."PlotObsID"=s.plotobs_id

GROUP BY p.project_name, "SiteCode"

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

AND po."NoInd">1

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS

SELECT DISTINCT p.project_name, pm."SiteCode"

FROM projects p

AND cover_percent IS NOT NULL

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS

SELECT DISTINCT p.project_name, pm."SiteCode"

FROM projects p

AND intercept_cm IS NOT NULL

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

-- Method: Sum on NoInd (number of individuals) column.

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

-- or line-intercept methods.

-- incorrect total for plots such as Gentry plot, which count individuals

-- per species per subplot

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

SELECT p.project_name, "SiteCode" AS plotcode,

SUM("NoInd") AS individuals

FROM projects p

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

LIMIT 10

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

ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"

AND po."PlotObsID"=s.plotobs_id

GROUP BY p.project_name, "SiteCode"

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon

FROM projects p

) AS a

GROUP BY project_name, plotcode

ORDER BY project_name, plotcode

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon

FROM projects p

ORDER BY p.project_name, "SiteCode", taxon

LIMIT 10

;

))) AS taxon,

SUM("NoInd") AS individuals

FROM projects p

GROUP BY p.project_name, "SiteCode", taxon

ORDER BY p.project_name, "SiteCode", taxon

LIMIT 10

))) AS taxon,

AVG(cover_percent) AS mean_cover

FROM projects p

AND cover_percent IS NOT NULL

GROUP BY p.project_name, "SiteCode", taxon

ORDER BY p.project_name, "SiteCode", taxon

))) AS taxon,

intercept_cm

FROM projects p

AND intercept_cm IS NOT NULL

ORDER BY p.project_name, "SiteCode", taxon

LIMIT 10

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

GROUP BY project_name, pm."SiteCode"

ORDER BY project_name, pm."SiteCode"

LIMIT 10

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

AND pm."SiteCode" IS NOT NULL

ORDER BY project_name, pm."SiteCode", subplot

LIMIT 10