-- Name: _taxonconcept_set_matched_concept_id(int(11), int(11), double precision); Type: FUNCTION; Schema: public; Owner: -

-- Name: _taxonconcept_set_parent_id(int(11), int(11)); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept_0_matched_concept_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept_1_matched_concept_min_fit(); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept_2_propagate_accepted_concept_id(); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept_3_parent_id_avoid_self_ref(); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE taxonconcept (

    taxonconcept_id int(11) NOT NULL,

    accepted_concept_id int(11),

    matched_concept_id int(11),

    matched_concept_fit_fraction double precision,

-- Name: TABLE taxonconcept; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.creator_id; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.sourceaccessioncode; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.creationdate; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.accepted_concept_id; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.matched_concept_id; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.matched_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.parent_id; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.taxonepithet; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.rank; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.verbatimrank; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.identifyingtaxonomicname; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.taxonomicname; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.author; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.taxonomicnamewithauthor; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.family; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.genus; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonconcept.species; Type: COMMENT; Schema: public; Owner: -

-- Name: taxonconcept_update_ancestors(taxonconcept, int(11)); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept_update_ancestors_on_insert(); Type: FUNCTION; Schema: public; Owner: -

-- Name: taxonconcept_update_ancestors_on_update(); Type: FUNCTION; Schema: public; Owner: -

    taxonconcept_id int(11) NOT NULL,

    SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonconcept.family, accepted_taxonconcept.genus, accepted_taxonconcept.species, COALESCE(accepted_taxonconcept.taxonomicnamewithauthor, accepted_taxonconcept.taxonomicname) AS taxon, accepted_taxonconcept.author AS `taxonAuthor`, accepted_taxonconcept.taxonepithet AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, functions._m2_to_ha(location.area_m2) AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, _fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonconcept datasource_taxonconcept USING (taxonconcept_id)) JOIN taxonconcept accepted_taxonconcept ON ((accepted_taxonconcept.taxonconcept_id = datasource_taxonconcept.accepted_concept_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

    taxonconcept_id int(11),

    taxonconcept_id int(11) NOT NULL,

-- Name: taxonconcept_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE taxonconcept_ancestor (

    descendant_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

-- Name: TABLE taxonconcept_ancestor; Type: COMMENT; Schema: public; Owner: -

-- Name: taxonconcept_taxonconcept_id_seq; Type: SEQUENCE; Schema: public; Owner: -

-- Name: taxonconcept_taxonconcept_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

-- Name: taxoncorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE taxoncorrelation (

    taxoncorrelation_id int(11) NOT NULL,

    taxonstatus_id int(11) NOT NULL,

    taxonconcept_id int(11) NOT NULL,

    plantconvergence text NOT NULL,

    correlationstart date NOT NULL,

    correlationstop date

);

-- Name: taxoncorrelation_taxoncorrelation_id_seq; Type: SEQUENCE; Schema: public; Owner: -

-- Name: taxoncorrelation_taxoncorrelation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

-- Name: taxondetermination_taxondetermination_id_seq; Type: SEQUENCE; Schema: public; Owner: -

-- Name: taxondetermination_taxondetermination_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

    taxonconcept_id int(11) NOT NULL,

    taxonconceptstatus text DEFAULT 'undetermined' NOT NULL,

    taxonconcept_id int(11) NOT NULL,

-- Name: taxonconcept_id; Type: DEFAULT; Schema: public; Owner: -

-- Name: taxoncorrelation_id; Type: DEFAULT; Schema: public; Owner: -

-- Name: taxondetermination_id; Type: DEFAULT; Schema: public; Owner: -

-- Name: methodtaxonclass_unique_taxonconcept_id; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

    ADD CONSTRAINT methodtaxonclass_unique_taxonconcept_id UNIQUE (method_id, taxonconcept_id);

-- Name: taxonconcept_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (descendant_id, ancestor_id);

-- Name: taxonconcept_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_pkey PRIMARY KEY (taxonconcept_id);

-- Name: taxoncorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE taxoncorrelation

    ADD CONSTRAINT taxoncorrelation_pkey PRIMARY KEY (taxoncorrelation_id);

-- Name: taxondetermination_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE taxondetermination

    ADD CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id);

    ADD CONSTRAINT taxonstatus_unique UNIQUE (taxonconcept_id, party_id);

-- Name: taxonconcept_0_unique_identifying_name; Type: INDEX; Schema: public; Owner: -; Tablespace: 

-- Name: taxonconcept_1_unique_sourceaccessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 

-- Name: taxonconcept_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX taxonconcept_accessioncode_index ON taxonconcept  (accessioncode);

-- Name: taxonconcept_ancestor_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE INDEX taxonconcept_ancestor_descendants ON taxonconcept_ancestor  (ancestor_id, descendant_id);

-- Name: taxonconcept_matched_concept_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE INDEX taxonconcept_matched_concept_id_idx ON taxonconcept  (matched_concept_id);

-- Name: taxonconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: 

-- Name: taxondetermination_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX taxondetermination_accessioncode_index ON taxondetermination  (accessioncode);

-- Name: taxondetermination_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: 

-- Name: taxonconcept_0_matched_concept_id_self_ref; Type: TRIGGER; Schema: public; Owner: -

-- Name: taxonconcept_1_matched_concept_min_fit; Type: TRIGGER; Schema: public; Owner: -

-- Name: taxonconcept_2_propagate_accepted_concept_id; Type: TRIGGER; Schema: public; Owner: -

-- Name: taxonconcept_3_parent_id_avoid_self_ref; Type: TRIGGER; Schema: public; Owner: -

-- Name: taxonconcept_update_ancestors_on_insert; Type: TRIGGER; Schema: public; Owner: -

-- Name: taxonconcept_update_ancestors_on_update; Type: TRIGGER; Schema: public; Owner: -

-- Name: methodtaxonclass_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT methodtaxonclass_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonalt_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT taxonalt_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonalt_taxondetermination_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT taxonalt_taxondetermination_id_fkey FOREIGN KEY (taxondetermination_id) REFERENCES taxondetermination(taxondetermination_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonconcept_accepted_concept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_accepted_concept_id_fkey FOREIGN KEY (accepted_concept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonconcept_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonconcept_ancestor_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonconcept_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

-- Name: taxonconcept_matched_concept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_matched_concept_id_fkey FOREIGN KEY (matched_concept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonconcept_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxoncorrelation_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

-- Name: taxoncorrelation_taxonstatus_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxoncorrelation

    ADD CONSTRAINT taxoncorrelation_taxonstatus_id_fkey FOREIGN KEY (taxonstatus_id) REFERENCES taxonstatus(taxonstatus_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxondetermination_party_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

-- Name: taxondetermination_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

-- Name: taxondetermination_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxondetermination

    ADD CONSTRAINT taxondetermination_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxondetermination_taxonoccurrence_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxondetermination

    ADD CONSTRAINT taxondetermination_taxonoccurrence_id_fkey FOREIGN KEY (taxonoccurrence_id) REFERENCES taxonoccurrence(taxonoccurrence_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonstatus_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT taxonstatus_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonusage_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -