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

--

CREATE FUNCTION sync_darwin_core_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS darwin_core;

CREATE TABLE darwin_core AS SELECT * FROM darwin_core_view;

CREATE INDEX ON darwin_core ("institutionCode", "projectID", "plotName", "dateCollected" );

CREATE INDEX ON darwin_core ("institutionCode", "occurrenceID" );

CREATE INDEX ON darwin_core ("institutionCode", "subInstitutionCode", "collectionCode", "catalogNumber" );

CREATE INDEX ON darwin_core ("institutionCode", "catalogNumber" );

$$;

--

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

--

CREATE TABLE darwin_core (

    "institutionCode" text,

    "subInstitutionCode" text,

    "collectionCode" text,

    "catalogNumber" text,

    "occurrenceID" text,

    country text,

    "stateProvince" text,

    county text,

    "decimalLatitude" double precision,

    "decimalLongitude" double precision,

    "coordinateUncertaintyInMeters" double precision,

    geovalid boolean,

    "isNewWorld" boolean,

    "projectID" integer,

    "plotName" text,

    "elevationInMeters" double precision,

    "plotArea_ha" double precision,

    "samplingProtocol" text,

    "dateCollected" date,

    "higherPlantGroup" higher_plant_group,

    family text,

    genus text,

    "speciesBinomial" text,

    "scientificName" text,

    "scientificNameAuthorship" text,

    "speciesBinomialWithMorphospecies" text,

    "scientificNameWithMorphospecies" text,

    threatened boolean,

    "identifiedBy" text,

    "growthForm" growthform,

    cultivated boolean,

    "cultivatedBasis" text,

    "recordedBy" text,

    "recordNumber" text,

    "coverPercent" double precision,

    "diameterBreastHeight_cm" double precision,

    height_m double precision,

    tag text,

    "organismX_m" double precision,

    "organismY_m" double precision,

    "taxonOccurrenceID" text,

    "authorTaxonCode" text,

    "individualID" text,

    "authorStemCode" text

);

--

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

--

CREATE TABLE project (

    project_id integer NOT NULL,

    source_id integer NOT NULL,

    sourceaccessioncode text,

    projectname text,

    projectdescription text,

    startdate date,

    stopdate date,

    accessioncode text,

    CONSTRAINT project_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (projectname IS NOT NULL)))

);

--

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

--

CREATE TABLE sourcename (

    sourcename_id integer NOT NULL,

    source_id integer NOT NULL,

    system text,

    name text NOT NULL,

    matched_source_id integer

);

--

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

--

CREATE TABLE specimenreplicate (

    specimenreplicate_id integer NOT NULL,

    source_id integer NOT NULL,

    sourceaccessioncode text,

    plantobservation_id integer,

    institution_id integer,

    collectioncode_dwc text,

    catalognumber_dwc text,

    description text,

    specimen_id integer,

    accessioncode text,

    CONSTRAINT specimenreplicate_required_key CHECK ((((plantobservation_id IS NOT NULL) OR (sourceaccessioncode IS NOT NULL)) OR (catalognumber_dwc IS NOT NULL)))

);

--

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

--

COMMENT ON TABLE specimenreplicate IS 'A herbarium''s replicate of a specimen. Contains Darwin Core specimen data.';

--

-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN specimenreplicate.institution_id IS 'The institution (such as a museum) that the specimenreplicate is from.';

--

-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN specimenreplicate.collectioncode_dwc IS 'The code for the collection that the specimenreplicate is from.';

--

-- Name: darwin_core_view; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW darwin_core_view AS

    SELECT source.shortname AS "institutionCode", sourcename.name AS "subInstitutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", locationevent.project_id AS "projectID", COALESCE(location.sourceaccessioncode, location.authorlocationcode) AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", 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[COALESCE(accepted_taxonverbatim.genus, accepted_taxonverbatim.family), COALESCE(accepted_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) AS "speciesBinomialWithMorphospecies", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, identifiedby.fullname AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", ((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis", collector.fullname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber", _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.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", plantobservation.sourceaccessioncode AS "individualID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON ((("newWorldCountries"."isoCode")::text = iso_code_gadm."2-digit iso code"))) LEFT JOIN locationevent USING (location_id)) LEFT JOIN project USING (project_id)) LEFT JOIN method USING (method_id)) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT 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))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = accepted_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcename ON ((sourcename.sourcename_id = specimenreplicate.institution_id))) WHERE (taxondetermination.iscurrent AND (COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) IS NOT NULL));

--

-- Data for Name: darwin_core; Type: TABLE DATA; Schema: public; Owner: -

--

--

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

--

CREATE INDEX "darwin_core_institutionCode_catalogNumber_idx" ON darwin_core USING btree ("institutionCode", "catalogNumber");

--

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

--

CREATE INDEX "darwin_core_institutionCode_occurrenceID_idx" ON darwin_core USING btree ("institutionCode", "occurrenceID");

--

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

--

CREATE INDEX "darwin_core_institutionCode_projectID_plotName_dateCollecte_idx" ON darwin_core USING btree ("institutionCode", "projectID", "plotName", "dateCollected");

--

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

--

CREATE INDEX "darwin_core_institutionCode_subInstitutionCode_collectionCo_idx" ON darwin_core USING btree ("institutionCode", "subInstitutionCode", "collectionCode", "catalogNumber");

--

-- Name: project; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE project FROM PUBLIC;

REVOKE ALL ON TABLE project FROM bien;

GRANT ALL ON TABLE project TO bien;

GRANT SELECT ON TABLE project TO bien_read;

--

-- Name: sourcename; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE sourcename FROM PUBLIC;

REVOKE ALL ON TABLE sourcename FROM bien;

GRANT ALL ON TABLE sourcename TO bien;

GRANT SELECT ON TABLE sourcename TO bien_read;

--

-- Name: specimenreplicate; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE specimenreplicate FROM PUBLIC;

REVOKE ALL ON TABLE specimenreplicate FROM bien;

GRANT ALL ON TABLE specimenreplicate TO bien;

GRANT SELECT ON TABLE specimenreplicate TO bien_read;

--