-- Existing tables

CREATE TABLE aux_role
(
  role_id serial NOT NULL,
  rolecode character varying(30) NOT NULL,
  roledescription character varying(200),
  CONSTRAINT aux_role_pkey PRIMARY KEY (role_id )
);

CREATE TABLE reference
(
  reference_id serial NOT NULL,
  shortname character varying(250),
  fulltext text,
  referencetype character varying(250)
);

CREATE TABLE party
(
  party_id serial NOT NULL,
  salutation character varying(20),
  givenname character varying(50),
  middlename character varying(50),
  surname character varying(50),
  organizationname character varying(100)
);

CREATE TABLE plantname
(
  plantname_id serial NOT NULL,
  plantname character varying(255) NOT NULL,
  reference_id integer,
  dateentered timestamp with time zone DEFAULT now(),
  CONSTRAINT plantname_pkey PRIMARY KEY (plantname_id ),
  CONSTRAINT plantname_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE plantconcept
(
  plantconcept_id serial NOT NULL,
  plantname_id integer NOT NULL,
  reference_id integer NOT NULL,
  plantname character varying(200),
  plantcode character varying(23),
  plantdescription text,
  d_obscount integer,
  d_currentaccepted boolean,
  accessioncode character varying(255),
  CONSTRAINT plantconcept_pkey PRIMARY KEY (plantconcept_id ),
  CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id)
      REFERENCES plantname (plantname_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE namedplace
(
  namedplace_id serial NOT NULL,
  placesystem character varying(50),
  placename character varying(100) NOT NULL,
  placedescription text,
  placecode character varying(15),
  owner character varying(100),
  reference_id integer,
  d_obscount integer,
  accessioncode character varying(255),
  CONSTRAINT namedplace_pkey PRIMARY KEY (namedplace_id ),
  CONSTRAINT namedplace_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT namedplace_keys UNIQUE (placesystem , placename )
);

CREATE TABLE locationplace
(
  locationplace_id serial NOT NULL,
  location_id integer NOT NULL,
  calculated boolean,
  namedplace_id integer NOT NULL,
  CONSTRAINT locationplace_pkey PRIMARY KEY (locationplace_id ),
  CONSTRAINT locationplace_location_id FOREIGN KEY (location_id)
      REFERENCES location (location_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT locationplace_namedplace_id FOREIGN KEY (namedplace_id)
      REFERENCES namedplace (namedplace_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT locationplace_keys UNIQUE (location_id , namedplace_id )
);

CREATE TABLE project
(
  project_id serial NOT NULL,
  projectname character varying(150) NOT NULL,
  projectdescription text,
  startdate timestamp with time zone,
  stopdate timestamp with time zone,
  d_obscount integer,
  d_lastlocationaddeddate timestamp with time zone,
  accessioncode character varying(255),
  CONSTRAINT project_pkey PRIMARY KEY (project_id ),
  CONSTRAINT project_keys UNIQUE (projectname , startdate , stopdate )
);

-- New tables

CREATE TABLE location
(
  location_id serial NOT NULL,
  authorlocationcode character varying(30),
  reference_id integer,
  parent_id integer,
  reallatitude double precision,
  reallongitude double precision,
  locationaccuracy double precision,
  confidentialitystatus integer NOT NULL DEFAULT 0,
  confidentialityreason character varying(200),
  publiclatitude double precision,
  publiclongitude double precision,
  "... (truncated) ..." integer,
  accessioncode character varying(255),
  sublocationxposition double precision,
  sublocationyposition double precision,
  namedplace_id integer,
  CONSTRAINT location_pkey PRIMARY KEY (location_id ),
  CONSTRAINT location_namedplace_id FOREIGN KEY (namedplace_id)
      REFERENCES namedplace (namedplace_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT location_parent_id FOREIGN KEY (parent_id)
      REFERENCES location (location_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT location_keys_code UNIQUE (reference_id , authorlocationcode ),
  CONSTRAINT location_keys_coords UNIQUE (reference_id , reallatitude , reallongitude ),
  CONSTRAINT location_keys_subplot_code UNIQUE (parent_id , authorlocationcode ),
  CONSTRAINT location_keys_subplot_coords UNIQUE (parent_id , sublocationxposition , sublocationyposition )
);

CREATE TABLE locationevent -- VegBank's observation table.
(
  locationevent_id serial NOT NULL,
  previous_id integer,
  location_id integer NOT NULL,
  project_id integer,
  authoreventcode character varying(30),
  "... (truncated) ..." integer,
  accessioncode character varying(255),
  sourceaccessioncode character varying(100),
  CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ),
  CONSTRAINT locationevent_location_id FOREIGN KEY (location_id)
      REFERENCES location (location_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT locationevent_project_id FOREIGN KEY (project_id)
      REFERENCES project (project_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT locationevent_keys_accessioncode UNIQUE (location_id , project_id , sourceaccessioncode ),
  CONSTRAINT locationevent_keys_code UNIQUE (location_id , project_id , authoreventcode )
);

CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table.
(
  taxonoccurrence_id serial NOT NULL,
  locationevent_id integer NOT NULL,
  authorplantname character varying(255),
  reference_id integer,
  taxoninferencearea double precision,
  emb_taxonoccurrence integer,
  "... (truncated) ..." integer,
  accessioncode character varying(255),
  CONSTRAINT taxonoccurrence_pkey PRIMARY KEY (taxonoccurrence_id ),
  CONSTRAINT taxonoccurrence_locationevent_id FOREIGN KEY (locationevent_id)
      REFERENCES locationevent (locationevent_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxonoccurrence_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE aggregateoccurrence -- VegBank's taxonimportance table.
(
  aggregateoccurrence_id serial NOT NULL,
  taxonoccurrence_id integer NOT NULL,
  taxonbinmethod_id integer,
  cover double precision,
  basalarea double precision,
  biomass double precision,
  inferencearea double precision,
  stratumbase double precision,
  stratumheight double precision,
  emb_aggregateoccurrence integer,
  covercode character varying(10),
  count integer NOT NULL,
  accessioncode character varying(255),
  sourceaccessioncode character varying(100),
  CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
  CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id)
      REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ),
  CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id )
);

CREATE TABLE individualplant -- VegBank's stemcount table.
(
  individualplant_id serial NOT NULL,
  aggregateoccurrence_id integer NOT NULL,
  overallheight double precision,
  overallheightaccuracy double precision,
  emb_individualplant integer,
  authorplantcode character varying(20),
  accessioncode character varying(255),
  stemcount integer,
  sourceaccessioncode character varying(100),
  CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ),
  CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
      REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT individualplant_keys_accessioncode UNIQUE (aggregateoccurrence_id , sourceaccessioncode ),
  CONSTRAINT individualplant_keys_code UNIQUE (aggregateoccurrence_id , authorplantcode )
);

CREATE TABLE stem -- VegBank's stemlocation table.
(
  stem_id serial NOT NULL,
  individualplant_id integer NOT NULL,
  authorstemcode character varying(20),
  xposition double precision,
  yposition double precision,
  health character varying(50),
  emb_stem integer,
  diameter double precision,
  height double precision,
  heightaccuracy double precision,
  age double precision,
  accessioncode character varying(255),
  diameteraccuracy double precision,
  sourceaccessioncode character varying(100),
  CONSTRAINT stem_pkey PRIMARY KEY (stem_id ),
  CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id)
      REFERENCES individualplant (individualplant_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT stem_keys_accessioncode UNIQUE (individualplant_id , sourceaccessioncode ),
  CONSTRAINT stem_keys_code UNIQUE (individualplant_id , authorstemcode )
);

CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.
(
  specimen_id serial NOT NULL,
  reference_id integer NOT NULL,
  collectioncode_dwc character varying(255),
  catalognumber_dwc character varying(255),
  collectiondate timestamp with time zone,
  collector_id integer,
  museum_id integer,
  sourceaccessioncode character varying(100),
  accessioncode character varying(255),
  taxonoccurrence_id integer NOT NULL,
  collectornumber_dwc character varying(255),
  authorspecimencode character varying(20),
  CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ),
  CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id)
      REFERENCES party (party_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id)
      REFERENCES party (party_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimen_keys_accessioncode UNIQUE (reference_id , collectioncode_dwc , sourceaccessioncode ),
  CONSTRAINT specimen_keys_catalognumber UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ),
  CONSTRAINT specimen_keys_code UNIQUE (reference_id , collectioncode_dwc , authorspecimencode )
);

CREATE TABLE voucher
(
  voucher_id serial NOT NULL,
  taxonoccurrence_id integer NOT NULL,
  specimen_id integer NOT NULL,
  accessioncode character varying(255),
  CONSTRAINT voucher_pkey PRIMARY KEY (voucher_id ),
  CONSTRAINT voucher_specimen_id FOREIGN KEY (specimen_id)
      REFERENCES specimen (specimen_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT voucher_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT voucher_keys UNIQUE (taxonoccurrence_id , specimen_id )
);

CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table.
(
  taxondetermination_id serial NOT NULL,
  taxonoccurrence_id integer NOT NULL,
  plantconcept_id integer NOT NULL,
  party_id integer,
  role_id integer NOT NULL,
  determinationtype character varying(30),
  reference_id integer,
  originaldetermination boolean NOT NULL DEFAULT false,
  currentdetermination boolean NOT NULL DEFAULT false,
  taxonfit character varying(50),
  taxonconfidence character varying(50),
  grouptype character varying(20),
  notes text,
  notespublic boolean,
  notesmgt boolean,
  revisions boolean,
  determinationdate timestamp with time zone NOT NULL,
  emb_taxondetermination integer,
  accessioncode character varying(255),
  CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id ),
  CONSTRAINT taxondetermination_party_id FOREIGN KEY (party_id)
      REFERENCES party (party_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxondetermination_plantconcept_id FOREIGN KEY (plantconcept_id)
      REFERENCES plantconcept (plantconcept_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxondetermination_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxondetermination_role_id FOREIGN KEY (role_id)
      REFERENCES aux_role (role_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxondetermination_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE stratum
(
  stratum_id serial NOT NULL,
  locationevent_id integer NOT NULL,
  stratumtype_id integer NOT NULL,
  stratummethod_id integer,
  stratumname character varying(30),
  stratumheight double precision,
  stratumbase double precision,
  stratumcover double precision,
  stratumdescription character varying(200),
  CONSTRAINT stratum_pkey PRIMARY KEY (stratum_id ),
  CONSTRAINT stratum_locationevent_id FOREIGN KEY (locationevent_id)
      REFERENCES locationevent (locationevent_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
(
  sizeclass_id serial NOT NULL,
  mindiameter double precision,
  minheight double precision,
  maxdiameter double precision,
  maxheight double precision,
  accessioncode character varying(255),
  CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id )
);

CREATE TABLE taxonbinmethod -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
(
  taxonbinmethod_id integer NOT NULL,
  label character varying(255) NOT NULL DEFAULT ''::character varying,
  stratum_id integer,
  sizeclass_id integer,
  coverindex_id integer,
  accessioncode character varying(255),
  CONSTRAINT taxonbinmethod_pkey PRIMARY KEY (taxonbinmethod_id ),
  CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id)
      REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxonbinmethod_keys UNIQUE (label , stratum_id , sizeclass_id , coverindex_id )
);