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

CREATE FUNCTION party_datasource_id_self_ref() RETURNS trigger

    IF new.datasource_id = 0 THEN -- make self-reference

        new.datasource_id = new.party_id;

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

-- Name: COLUMN party.datasource_id; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN party.datasource_id IS 'A datasource should point to itself in this field. This will happen automatically by setting it to the special value 0.';

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

    creator_id integer,

    datasource_id integer NOT NULL,

    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], ' '::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));

    datasource_id integer NOT NULL,

    datasource_id integer NOT NULL,

    ADD CONSTRAINT project_unique UNIQUE (datasource_id, projectname);

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

CREATE UNIQUE INDEX aggregateoccurrence_unique_within_datasource ON aggregateoccurrence USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

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

CREATE UNIQUE INDEX location_unique_within_datasource_by_authorlocationcode ON location USING btree (datasource_id, (COALESCE(authorlocationcode, '\\N'::text))) WHERE (((authorlocationcode IS NOT NULL) AND (parent_id IS NULL)) AND (sourceaccessioncode IS NULL));

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

CREATE UNIQUE INDEX location_unique_within_datasource_by_sourceaccessioncode ON location USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

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

CREATE UNIQUE INDEX locationevent_unique_within_datasource ON locationevent USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX party_unique_root ON party USING btree ((COALESCE(organizationname, '\\N'::text))) WHERE (datasource_id = party_id);

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

CREATE UNIQUE INDEX party_unique_within_datasource ON party USING btree (datasource_id, (COALESCE(organizationname, '\\N'::text)), (COALESCE(surname, '\\N'::text)), (COALESCE(givenname, '\\N'::text)), (COALESCE(middlename, '\\N'::text)));

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

CREATE UNIQUE INDEX placepath_unique_within_datasource_by_code ON placepath USING btree (datasource_id, (COALESCE(placecode, '\\N'::text))) WHERE (placecode IS NOT NULL);

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

CREATE UNIQUE INDEX placepath_unique_within_datasource_by_name ON placepath USING btree (datasource_id, (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(municipality, '\\N'::text)), (COALESCE(site, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedplacename[]))) WHERE (placecode IS NULL);

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

CREATE UNIQUE INDEX plantobservation_unique_within_datasource ON plantobservation USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX project_unique_name_date ON project USING btree (datasource_id, (COALESCE(projectname, '\\N'::text)), (COALESCE(startdate, 'infinity'::timestamp with time zone))) WHERE (sourceaccessioncode IS NULL);

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

CREATE UNIQUE INDEX project_unique_within_datasource ON project USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX specimenreplicate_unique_catalognumber ON specimenreplicate USING btree (datasource_id, (COALESCE(institution_id, 2147483647)), (COALESCE(collectioncode_dwc, '\\N'::text)), (COALESCE(catalognumber_dwc, '\\N'::text)), (COALESCE(plantobservation_id, 2147483647))) WHERE ((catalognumber_dwc IS NOT NULL) AND (sourceaccessioncode IS NULL));

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

CREATE UNIQUE INDEX specimenreplicate_unique_within_datasource ON specimenreplicate USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

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

CREATE UNIQUE INDEX stemobservation_unique_within_datasource ON stemobservation USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

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

CREATE UNIQUE INDEX taxonconcept_unique_within_datasource_by_identifying_name ON taxonconcept USING btree (datasource_id, (COALESCE(identifyingtaxonomicname, '\\N'::text))) WHERE (identifyingtaxonomicname IS NOT NULL);

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

CREATE UNIQUE INDEX taxonconcept_unique_within_datasource_by_name ON taxonconcept USING btree (datasource_id, (COALESCE(creator_id, 2147483647)), (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text)), (COALESCE(morphospecies, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedtaxonname[]))) WHERE (identifyingtaxonomicname IS NULL);

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

CREATE UNIQUE INDEX taxonoccurrence_unique_within_datasource ON taxonoccurrence USING btree (datasource_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

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

CREATE TRIGGER party_datasource_id_self_ref BEFORE INSERT OR UPDATE ON party FOR EACH ROW EXECUTE PROCEDURE party_datasource_id_self_ref();

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

    ADD CONSTRAINT aggregateoccurrence_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT location_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT locationevent_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT party_currentname_id_fkey FOREIGN KEY (currentname_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT party_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT placepath_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT plantobservation_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT project_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT specimenreplicate_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT stemobservation_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

--

ALTER TABLE ONLY taxonconcept

    ADD CONSTRAINT taxonconcept_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

    ADD CONSTRAINT taxonoccurrence_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;