-- Existing tables

CREATE TABLE role
(
  role_id int(11) NOT NULL AUTO_INCREMENT,
  rolecode character varying(30) NOT NULL,
  roledescription character varying(200),
  CONSTRAINT role_pkey PRIMARY KEY (role_id )
);

CREATE TABLE reference
(
  reference_id int(11) NOT NULL AUTO_INCREMENT,
  shortname character varying(250),
  fulltext__ text,
  referencetype character varying(250)
);

CREATE TABLE party
(
  party_id int(11) NOT NULL AUTO_INCREMENT,
  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 int(11) NOT NULL AUTO_INCREMENT,
  plantname character varying(255) NOT NULL,
  reference_id int(11),
  dateentered timestamp NULL 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 int(11) NOT NULL AUTO_INCREMENT,
  plantname_id int(11) NOT NULL,
  reference_id int(11),
  plantcode character varying(23),
  plantdescription text,
  d_obscount int(11),
  d_currentaccepted int(1),
  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 stratummethod
(
  stratummethod_id int(11) NOT NULL AUTO_INCREMENT,
  reference_id int(11),
  stratummethodname character varying(30) NOT NULL,
  stratummethoddescription text,
  stratumassignment character varying(50),
  accessioncode character varying(255),
  CONSTRAINT stratummethod_pkey PRIMARY KEY (stratummethod_id ),
  CONSTRAINT stratummethod_reference_id FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE stratumtype
(
  stratumtype_id int(11) NOT NULL AUTO_INCREMENT,
  stratummethod_id int(11) NOT NULL,
  stratumindex character varying(10),
  stratumname character varying(30),
  stratumdescription text,
  CONSTRAINT stratumtype_pkey PRIMARY KEY (stratumtype_id ),
  CONSTRAINT stratumtype_stratummethod_id FOREIGN KEY (stratummethod_id)
      REFERENCES stratummethod (stratummethod_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE namedplace
(
  namedplace_id int(11) NOT NULL AUTO_INCREMENT,
  placesystem character varying(50),
  placename character varying(100) NOT NULL,
  placedescription text,
  placecode character varying(15),
  owner character varying(100),
  reference_id int(11),
  d_obscount int(11),
  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 int(11) NOT NULL AUTO_INCREMENT,
  location_id int(11) NOT NULL,
  calculated int(1),
  namedplace_id int(11) 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 int(11) NOT NULL AUTO_INCREMENT,
  projectname character varying(150) NOT NULL,
  projectdescription text,
  startdate timestamp NULL,
  stopdate timestamp NULL,
  d_obscount int(11),
  d_lastlocationaddeddate timestamp NULL,
  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 int(11) NOT NULL AUTO_INCREMENT,
  authorlocationcode character varying(30),
  reference_id int(11),
  parent_id int(11),
  reallatitude double precision,
  reallongitude double precision,
  locationaccuracy double precision,
  confidentialitystatus int(11) NOT NULL DEFAULT 0,
  confidentialityreason character varying(200),
  publiclatitude double precision,
  publiclongitude double precision,
  `... (truncated) ...` int(11),
  accessioncode character varying(255),
  sublocationxposition double precision,
  sublocationyposition double precision,
  namedplace_id int(11),
  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 int(11) NOT NULL AUTO_INCREMENT,
  previous_id int(11),
  location_id int(11),
  project_id int(11),
  authoreventcode character varying(30),
  `... (truncated) ...` int(11),
  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 int(11) NOT NULL AUTO_INCREMENT,
  locationevent_id int(11),
  authorplantname character varying(255),
  reference_id int(11),
  taxoninferencearea double precision,
  emb_taxonoccurrence int(11),
  `... (truncated) ...` int(11),
  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 int(11) NOT NULL AUTO_INCREMENT,
  taxonoccurrence_id int(11) NOT NULL,
  taxonbinmethod_id int(11),
  cover double precision,
  basalarea double precision,
  biomass double precision,
  inferencearea double precision,
  stratumbase double precision,
  stratumheight double precision,
  emb_aggregateoccurrence int(11),
  covercode character varying(10),
  count int(11),
  accessioncode character varying(255),
  sourceaccessioncode character varying(100),
  plantobservation_id int(11),
  CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
  CONSTRAINT aggregateoccurrence_plantobservation_id FOREIGN KEY (plantobservation_id)
      REFERENCES plantobservation (plantobservation_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  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 plant -- A physical, tagged plant.
(
  plant_id int(11) NOT NULL AUTO_INCREMENT,
  CONSTRAINT plant_pkey PRIMARY KEY (plant_id )
);

CREATE TABLE planttag
(
  planttag_id int(11) NOT NULL AUTO_INCREMENT,
  plant_id int(11) NOT NULL,
  tag character varying(255) NOT NULL,
  CONSTRAINT planttag_pkey PRIMARY KEY (planttag_id ),
  CONSTRAINT planttag_plant_id FOREIGN KEY (plant_id)
      REFERENCES plant (plant_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT planttag_keys UNIQUE (plant_id , tag )
);

CREATE TABLE plantobservation -- VegBank's stemcount table.
(
  plantobservation_id int(11) NOT NULL AUTO_INCREMENT,
  aggregateoccurrence_id int(11) NOT NULL,
  overallheight double precision,
  overallheightaccuracy double precision,
  emb_plantobservation int(11),
  authorplantcode character varying(20),
  accessioncode character varying(255),
  stemcount int(11),
  sourceaccessioncode character varying(100),
  plant_id int(11),
  CONSTRAINT plantobservation_pkey PRIMARY KEY (plantobservation_id ),
  CONSTRAINT plantobservation_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
      REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT plantobservation_plant_id FOREIGN KEY (plant_id)
      REFERENCES plant (plant_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT plantobservation_aggregateoccurrence_id_1_to_1 UNIQUE (aggregateoccurrence_id )
);

CREATE TABLE stemobservation -- VegBank's stemlocation table.
(
  stemobservation_id int(11) NOT NULL AUTO_INCREMENT,
  plantobservation_id int(11) NOT NULL,
  authorstemcode character varying(20),
  xposition double precision,
  yposition double precision,
  health character varying(50),
  emb_stemobservation int(11),
  diameter double precision,
  height double precision,
  heightaccuracy double precision,
  age double precision,
  accessioncode character varying(255),
  diameteraccuracy double precision,
  sourceaccessioncode character varying(100),
  CONSTRAINT stemobservation_pkey PRIMARY KEY (stemobservation_id ),
  CONSTRAINT stemobservation_plantobservation_id FOREIGN KEY (plantobservation_id)
      REFERENCES plantobservation (plantobservation_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT stemobservation_keys_accessioncode UNIQUE (plantobservation_id , sourceaccessioncode ),
  CONSTRAINT stemobservation_keys_code UNIQUE (plantobservation_id , authorstemcode )
);

CREATE TABLE specimen -- A physical specimen collected from a plant. Used to link replicates of the same specimen together.
(
  specimen_id int(11) NOT NULL AUTO_INCREMENT,
  CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id )
);

CREATE TABLE specimenreplicate -- A herbarium's replicate of a specimen. Contains Darwin Core specimen data.
(
  specimenreplicate_id int(11) NOT NULL AUTO_INCREMENT,
  reference_id int(11) NOT NULL,
  collectioncode_dwc character varying(255), -- The code for the collection that the specimenreplicate is from.
  catalognumber_dwc character varying(255),
  collectiondate timestamp NULL,
  museum_id int(11),
  sourceaccessioncode character varying(100),
  accessioncode character varying(255),
  taxonoccurrence_id int(11) NOT NULL,
  verbatimcollectorname character varying(255),
  collectionnumber character varying(255), -- The number of the specimenreplicate within the collection.
  specimen_id int(11),
  CONSTRAINT specimenreplicate_pkey PRIMARY KEY (specimenreplicate_id ),
  CONSTRAINT specimenreplicate_museum_id FOREIGN KEY (museum_id)
      REFERENCES party (party_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimenreplicate_reference_id_fkey FOREIGN KEY (reference_id)
      REFERENCES reference (reference_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimenreplicate_specimen_id FOREIGN KEY (specimen_id)
      REFERENCES specimen (specimen_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimenreplicate_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (reference_id , collectioncode_dwc , sourceaccessioncode ),
  CONSTRAINT specimenreplicate_keys_catalognumber UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ),
  CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (reference_id , collectioncode_dwc , collectionnumber )
);

CREATE TABLE voucher
(
  voucher_id int(11) NOT NULL AUTO_INCREMENT,
  taxonoccurrence_id int(11) NOT NULL,
  specimenreplicate_id int(11) NOT NULL,
  accessioncode character varying(255),
  CONSTRAINT voucher_pkey PRIMARY KEY (voucher_id ),
  CONSTRAINT voucher_specimenreplicate_id FOREIGN KEY (specimenreplicate_id)
      REFERENCES specimenreplicate (specimenreplicate_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 , specimenreplicate_id )
);

CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table.
(
  taxondetermination_id int(11) NOT NULL AUTO_INCREMENT,
  taxonoccurrence_id int(11) NOT NULL,
  plantconcept_id int(11) NOT NULL,
  party_id int(11),
  role_id int(11) NOT NULL,
  determinationtype character varying(30),
  reference_id int(11),
  isoriginal int(1) NOT NULL DEFAULT false,
  iscurrent int(1) NOT NULL DEFAULT false,
  taxonfit character varying(50),
  taxonconfidence character varying(50),
  grouptype character varying(20),
  notes text,
  notespublic int(1),
  notesmgt int(1),
  revisions int(1),
  determinationdate timestamp NULL,
  emb_taxondetermination int(11),
  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 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 int(11) NOT NULL AUTO_INCREMENT,
  locationevent_id int(11) NOT NULL,
  stratumtype_id int(11) NOT NULL,
  stratumheight double precision,
  stratumbase double precision,
  stratumcover double precision,
  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,
  CONSTRAINT stratum_stratumtype_id FOREIGN KEY (stratumtype_id)
      REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
(
  sizeclass_id int(11) NOT NULL AUTO_INCREMENT,
  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
(
  taxonbinmethod_id int(11) NOT NULL,
  label character varying(255),
  stratumtype_id int(11),
  sizeclass_id int(11),
  coverindex_id int(11),
  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_stratumtype_id FOREIGN KEY (stratumtype_id)
      REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);