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

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

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

CREATE TABLE taxonconcept (

    taxonconcept_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    identifyingtaxonomicname text,

    canon_taxonconcept_id int(11),

    taxon_id int(11),

    taxonomicname text,

    author text,

    taxonomicnamewithauthor text,

    domain text,

    kingdom text,

    phylum text,

    class text,

    `order` text,

    family text,

    genus text,

    species text,

    subspecies text,

    variety text,

    forma text,

    cultivar text,

    morphospecies text,

    otherranks text,

    scope_id int(11),

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

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

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

-- Name: COLUMN taxonconcept.taxon_id; 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.domain; Type: COMMENT; Schema: public; Owner: -

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

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

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

-- Name: COLUMN taxonconcept.`order`; 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: COLUMN taxonconcept.subspecies; Type: COMMENT; Schema: public; Owner: -

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

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

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

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

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

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

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

CREATE TABLE taxondetermination (

    taxondetermination_id int(11) NOT NULL,

    taxonoccurrence_id int(11) NOT NULL,

    taxonconcept_id int(11) NOT NULL,

    party_id int(11),

    role text DEFAULT 'unknown' NOT NULL,

    determinationtype text,

    reference_id int(11),

    isoriginal int(1) DEFAULT false NOT NULL,

    iscurrent int(1) DEFAULT false NOT NULL,

    taxonfit text,

    taxonconfidence text,

    grouptype text,

    notes text,

    notespublic int(1),

    notesmgt int(1),

    revisions int(1),

    determinationdate timestamp NULL,

    accessioncode text

);

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

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

CREATE TABLE taxonoccurrence (

    taxonoccurrence_id int(11) NOT NULL,

    datasource_id int(11) NOT NULL,

    sourceaccessioncode text,

    locationevent_id int(11),

    authortaxoncode text,

    verbatimcollectorname text,

    growthform text,

    iscultivated int(1),

    cultivatedbasis text,

    isnative int(1),

    accessioncode text

);

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

-- Name: COLUMN taxonoccurrence.iscultivated; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonoccurrence.cultivatedbasis; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN taxonoccurrence.isnative; Type: COMMENT; Schema: public; Owner: -

    SELECT datasource.organizationname AS `dataSourceName`, taxonconcept.family, taxonconcept.genus, taxonconcept.species, COALESCE(taxonconcept.taxonomicnamewithauthor, taxonconcept.taxonomicname) AS taxon, taxonconcept.author AS `taxonAuthor`, taxonconcept.variety 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`, functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_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 USING (taxonconcept_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,

    taxonconcept_id int(11) NOT NULL,

    taxonconceptstatus text DEFAULT 'undetermined' NOT NULL,

    taxonconcept_id int(11),

    taxonconcept_id int(11) NOT NULL,

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

-- Name: taxonconcept_taxonconcept_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: -

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

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

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

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

-- Name: taxonoccurrence_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);

    ADD CONSTRAINT plantstatus_unique UNIQUE (taxonconcept_id, party_id);

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

--

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_pkey PRIMARY KEY (taxonconcept_id);

--

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

CREATE UNIQUE INDEX taxonconcept_accessioncode_index ON taxonconcept  (accessioncode);

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

-- Name: taxonconcept_unique_within_datasource_by_name; 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: taxonoccurrence_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX taxonoccurrence_accessioncode_index ON taxonoccurrence  (accessioncode);

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

CREATE INDEX taxonoccurrence_locationevent ON taxonoccurrence  (locationevent_id);

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

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

-- Name: taxonconcept_canon_taxonconcept_id_self_ref; 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: plantcorrelation_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

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

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

-- 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_canon_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

ALTER TABLE taxonconcept

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

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

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

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_scope_id_fkey FOREIGN KEY (scope_id) REFERENCES taxonscope(taxonscope_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE taxonconcept

    ADD CONSTRAINT taxonconcept_taxon_id_fkey FOREIGN KEY (taxon_id) REFERENCES taxon(taxon_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: taxonoccurrence_datasource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

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

ALTER TABLE taxonoccurrence

    ADD CONSTRAINT taxonoccurrence_locationevent_id_fkey FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;