CREATE OR REPLACE FUNCTION array_to_string(anyarray, text)
  RETURNS text AS
$BODY$
SELECT pg_catalog.array_to_string($1, $2)
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

CREATE TABLE tnrs
(
  "Time_submitted" timestamp with time zone,
  "Name_number" text,
  "Name_submitted" text NOT NULL,
  "Overall_score" text,
  "Name_matched" text,
  "Name_matched_rank" text,
  "Name_score" text,
  "Name_matched_author" text,
  "Name_matched_url" text,
  "Author_matched" text,
  "Author_score" text,
  "Family_matched" text,
  "Family_score" text,
  "Name_matched_accepted_family" text,
  "Genus_matched" text,
  "Genus_score" text,
  "Specific_epithet_matched" text,
  "Specific_epithet_score" text,
  "Infraspecific_rank" text,
  "Infraspecific_epithet_matched" text,
  "Infraspecific_epithet_score" text,
  "Infraspecific_rank_2" text,
  "Infraspecific_epithet_2_matched" text,
  "Infraspecific_epithet_2_score" text,
  "Annotations" text,
  "Unmatched_terms" text,
  "Taxonomic_status" text,
  "Accepted_name" text,
  "Accepted_name_author" text,
  "Accepted_name_rank" text,
  "Accepted_name_url" text,
  "Accepted_name_species" text,
  "Accepted_name_family" text,
  "Selected" text,
  "Source" text,
  "Warnings" text,
  "Accepted_name_lsid" text,
  "Accepted_scientific_name" text,
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
)
WITH (
  OIDS=FALSE
);

CREATE OR REPLACE FUNCTION tnrs_populate_accepted_scientific_name()
  RETURNS trigger AS
$BODY$
BEGIN
    new."Accepted_scientific_name" = NULLIF(array_to_string(ARRAY[
        NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
        , new."Accepted_name"
        , new."Accepted_name_author"
    ], ' '), '');
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER tnrs_populate_accepted_scientific_name
  BEFORE INSERT OR UPDATE
  ON tnrs
  FOR EACH ROW
  EXECUTE PROCEDURE tnrs_populate_accepted_scientific_name();


CREATE OR REPLACE VIEW tnrs_canon AS 
 SELECT (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Time_submitted" AS "Time_submitted", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_number" AS "Name_number", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_submitted" AS "Name_submitted", tnrs."Overall_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_matched" AS "Name_matched", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_matched_rank" AS "Name_matched_rank", tnrs."Name_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_matched_author" AS "Name_matched_author", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_matched_url" AS "Name_matched_url", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Author_matched" AS "Author_matched", tnrs."Author_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Family_matched" AS "Family_matched", tnrs."Family_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Name_matched_accepted_family" AS "Name_matched_accepted_family", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Genus_matched" AS "Genus_matched", tnrs."Genus_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Specific_epithet_matched" AS "Specific_epithet_matched", tnrs."Specific_epithet_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Infraspecific_rank" AS "Infraspecific_rank", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched", tnrs."Infraspecific_epithet_score", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Infraspecific_rank_2" AS "Infraspecific_rank_2", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched", tnrs."Infraspecific_epithet_2_score", tnrs."Annotations" AS "Annotations", tnrs."Unmatched_terms" AS "Unmatched_terms", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Taxonomic_status" AS "Taxonomic_status", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Selected" AS "Selected", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Source" AS "Source", (
        CASE
            WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
            ELSE tnrs.*
        END)."Warnings" AS "Warnings"
   FROM tnrs
   LEFT JOIN tnrs tnrs_accepted ON tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name";
COMMENT ON VIEW tnrs_canon
  IS 'The most canonicalized name output by TNRS. This will be the accepted name if available, and the matched name otherwise. In either case, the match scores are always from the matched name, not the accepted name (whose match scores should always be close to 1).';