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

CREATE FUNCTION party_reference_id_self_ref() RETURNS trigger

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

        new.reference_id = new.party_id;

    reference_id integer NOT NULL,

-- Name: COLUMN taxonlabel.reference_id; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonlabel.reference_id IS 'The entity that created the taxon label.';

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    CONSTRAINT party_required_key CHECK (((organizationname IS NOT NULL) OR ((reference_id <> party_id) AND ((fullname IS NOT NULL) OR (surname IS NOT NULL)))))

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    SELECT datasource.organizationname AS "institutionCode", place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.reference_id = datasource.party_id))) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place USING (place_id)) LEFT JOIN coordinates USING (coordinates_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 taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE ((datasource.reference_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));

    reference_id integer NOT NULL,

    ADD CONSTRAINT project_unique UNIQUE (reference_id, projectname);

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

CREATE UNIQUE INDEX coordinates_unique ON coordinates USING btree (reference_id, latitude_deg, longitude_deg, (COALESCE(verbatimlatitude, '\\N'::text)), (COALESCE(verbatimlongitude, '\\N'::text)), (COALESCE(verbatimcoordinates, '\\N'::text)), (COALESCE(footprintgeometry_dwc, '\\N'::text)));

CREATE INDEX location_creator ON location USING btree (reference_id);

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

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

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

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

CREATE UNIQUE INDEX place_unique_within_creator_by_code ON place USING btree (reference_id, (COALESCE(placecode, '\\N'::text))) WHERE (placecode IS NOT NULL);

CREATE UNIQUE INDEX place_unique_within_creator_by_name ON place USING btree (reference_id, (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(coordinates_id, 2147483647))) WHERE (placecode IS NULL);

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

CREATE UNIQUE INDEX project_unique_name_date ON project USING btree (reference_id, (COALESCE(projectname, '\\N'::text)), (COALESCE(startdate, 'infinity'::date))) WHERE (sourceaccessioncode IS NULL);

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

CREATE UNIQUE INDEX specimenreplicate_unique_catalognumber ON specimenreplicate USING btree (reference_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));

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

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

CREATE UNIQUE INDEX taxonlabel_0_unique_identifying_name ON taxonlabel USING btree (reference_id, (COALESCE(taxonomicname, '\\N'::text))) WHERE (taxonomicname IS NOT NULL);

CREATE UNIQUE INDEX taxonlabel_1_unique_sourceaccessioncode ON taxonlabel USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX taxonlabel_unique ON taxonlabel USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonepithet, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), reference_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)));

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

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

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

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

    ADD CONSTRAINT aggregateoccurrence_method_id_fkey FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT aggregateoccurrence_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT coordinates_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY location

    ADD CONSTRAINT location_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

--

ALTER TABLE ONLY locationevent

    ADD CONSTRAINT locationevent_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_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_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT party_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT place_matched_place_id_fkey FOREIGN KEY (matched_place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT place_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT place_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT plantobservation_plant_id_fkey FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT plantobservation_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT project_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT specimenreplicate_institution_id_fkey FOREIGN KEY (institution_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT specimenreplicate_plantobservation_id_fkey FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT specimenreplicate_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT stemobservation_plantobservation_id_fkey FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT stemobservation_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT taxonlabel_matched_label_id_fkey FOREIGN KEY (matched_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT taxonlabel_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT taxonlabel_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

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

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

    ADD CONSTRAINT taxonoccurrence_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;