CREATE OR REPLACE FUNCTION score_ok(score double precision)

  RETURNS boolean AS

$BODY$

SELECT $1 >= 0.8

$BODY$

  LANGUAGE sql VOLATILE

  COST 100;

/* IMPORTANT: when changing this table's schema, you must regenerate data.sql:

$ <this_file>/../test_taxonomic_names/test_scrub

*/

CREATE TABLE tnrs

(

  "Time_submitted" timestamp with time zone NOT NULL DEFAULT now(),

  "Name_number" integer NOT NULL,

  "Name_submitted" text NOT NULL,

  "Overall_score" double precision,

  "Name_matched" text,

  "Name_matched_rank" text,

  "Name_score" double precision,

  "Name_matched_author" text,

  "Name_matched_url" text,

  "Author_matched" text,

  "Author_score" double precision,

  "Family_matched" text,

  "Family_score" double precision,

  "Name_matched_accepted_family" text,

  "Genus_matched" text,

  "Genus_score" double precision,

  "Specific_epithet_matched" text,

  "Specific_epithet_score" double precision,

  "Infraspecific_rank" text,

  "Infraspecific_epithet_matched" text,

  "Infraspecific_epithet_score" double precision,

  "Infraspecific_rank_2" text,

  "Infraspecific_epithet_2_matched" text,

  "Infraspecific_epithet_2_score" double precision,

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

  "Max_score" double precision,

  "Is_plant" boolean,

  CONSTRAINT tnrs_pkey PRIMARY KEY ("Time_submitted" , "Name_number" ),

  CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted" )

)

WITH (

  OIDS=FALSE

);

CREATE UNIQUE INDEX tnrs_score_ok

  ON tnrs

  USING btree

  ("Name_submitted" )

  WHERE score_ok("Max_score");

/* IMPORTANT: when changing this function, you must regenerate the derived cols:

UPDATE tnrs SET "Name_submitted" = "Name_submitted"

runtime: 17 min ("4992166 rows affected, 1019907 ms execution time")

*/

CREATE OR REPLACE FUNCTION tnrs_populate_fields()

  RETURNS trigger AS

$BODY$

DECLARE

    "Specific_epithet_is_plant" boolean :=

        (CASE

        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL

            OR new."Infraspecific_epithet_2_matched" IS NOT NULL

            OR new."Specific_epithet_score" >= 0.9 -- fuzzy match

            THEN true

        ELSE NULL -- ambiguous

        END);

BEGIN

    new."Accepted_scientific_name" = NULLIF(concat_ws(' '

        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")

        , new."Accepted_name"

        , new."Accepted_name_author"

    ), '');

    new."Max_score" = GREATEST(

          new."Overall_score"

        , new."Family_score"

        , new."Genus_score"

        , new."Specific_epithet_score"

    );

    new."Is_plant" = (CASE

        WHEN new."Family_score" = 1 THEN true -- exact match

        ELSE -- Family_matched IS NULL

            (CASE

            WHEN new."Genus_score" =  1    -- exact match

                THEN "Specific_epithet_is_plant"

            WHEN new."Genus_score" >= 0.85 -- fuzzy match

                THEN "Specific_epithet_is_plant"

            ELSE NULL -- ambiguous

            END)

        END);

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

CREATE TRIGGER tnrs_populate_fields

  BEFORE INSERT OR UPDATE

  ON tnrs

  FOR EACH ROW

  EXECUTE PROCEDURE tnrs_populate_fields();

CREATE OR REPLACE VIEW "MatchedTaxon" AS

SELECT

  "Time_submitted" AS "*Name_matched.Time_submitted"

, "Name_submitted" AS "concatenatedScientificName"

, "Name_matched" AS "matchedTaxonName"

, "Name_matched_rank" AS "matchedTaxonRank"

, "Name_score" AS "*Name_matched.Name_score"

, "Name_matched_author" AS "matchedScientificNameAuthorship"

, "Name_matched_url" AS "matchedScientificNameID"

, "Author_score" AS "*Name_matched.Author_score"

, "Family_score" AS "matchedFamilyConfidence_fraction"

, COALESCE("Name_matched_accepted_family", "Accepted_name_family") AS "matchedFamily"

, "Genus_matched" AS "matchedGenus"

, "Genus_score" AS "matchedGenusConfidence_fraction"

, "Specific_epithet_matched" AS "matchedSpecificEpithet"

, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"

, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"

, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"

, "Annotations" AS "identificationQualifier"

, "Unmatched_terms" AS "morphospeciesSuffix"

, "Taxonomic_status" AS "taxonomicStatus"

, "Accepted_name" AS "acceptedTaxonName"

, "Accepted_name_author" AS "acceptedScientificNameAuthorship"

, "Accepted_name_rank" AS "acceptedTaxonRank"

, "Accepted_name_url" AS "acceptedScientificNameID"

, "Accepted_name_species" AS "*Name_matched.Accepted_name_species"

, "Accepted_name_family" AS "acceptedFamily"

, "Selected" AS "*Name_matched.Selected"

, "Source" AS "*Name_matched.Source"

, "Warnings" AS "*Name_matched.Warnings"

, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"

, "Accepted_scientific_name" AS "acceptedScientificName"

, "Max_score" AS "matchedTaxonConfidence_fraction"

FROM tnrs

;

CREATE OR REPLACE VIEW "ValidMatchedTaxon" AS

SELECT *

FROM "MatchedTaxon"

WHERE score_ok("matchedTaxonConfidence_fraction")

;

CREATE OR REPLACE VIEW "AcceptedTaxon" AS

SELECT

  "Time_submitted" AS "*Accepted_name.Time_submitted"

, "Name_submitted" AS "acceptedScientificName"

, "Genus_matched" AS "acceptedGenus"

, "Specific_epithet_matched" AS "acceptedSpecificEpithet"

, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"

, "Annotations" AS "*Accepted_name.Annotations"

, "Taxonomic_status" AS "acceptedTaxonomicStatus"

, "Selected" AS "*Accepted_name.Selected"

, "Source" AS "*Accepted_name.Source"

, "Warnings" AS "*Accepted_name.Warnings"

, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"

FROM tnrs

;

CREATE OR REPLACE VIEW "ScrubbedTaxon" AS

SELECT *

FROM "ValidMatchedTaxon"

NATURAL LEFT JOIN "AcceptedTaxon"

;