CREATE OR REPLACE VIEW "MatchedTaxon" AS

SELECT

  "Time_submitted" AS "*Name_matched.Time_submitted"

, "Name_matched" AS "concatenatedScientificName"

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

, "Name_matched_accepted_family" AS family

, "Genus_matched" AS genus

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

NATURAL LEFT JOIN "AcceptedTaxon"

;