SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `default_schema` ;
CREATE SCHEMA IF NOT EXISTS `default_schema` ;
USE `default_schema` ;

-- -----------------------------------------------------
-- Table `default_schema`.`reference`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`reference` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`reference` (
  `reference_id` INT NOT NULL ,
  PRIMARY KEY (`reference_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `default_schema`.`taxon`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxon` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxon` (
  `taxon_id` INT NOT NULL ,
  `taxonrank` VARCHAR(45) NOT NULL ,
  `scientificname` VARCHAR(100) NOT NULL ,
  `scientificnameauthorship` VARCHAR(150) NULL ,
  `authorship_reference_id` INT NULL ,
  PRIMARY KEY (`taxon_id`) ,
  INDEX `fk_taxon_reference2` (`authorship_reference_id` ASC) ,
  CONSTRAINT `fk_taxon_reference2`
    FOREIGN KEY (`authorship_reference_id` )
    REFERENCES `default_schema`.`reference` (`reference_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `default_schema`.`taxonconcept`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonconcept` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonconcept` (
  `taxonconcept_id` INT(11) NOT NULL ,
  `taxon_id` INT NOT NULL ,
  `concept_reference_id` INT NOT NULL ,
  PRIMARY KEY (`taxonconcept_id`) ,
  INDEX `fk_taxonconcept_taxon1` (`taxon_id` ASC) ,
  INDEX `fk_taxonconcept_reference1` (`concept_reference_id` ASC) ,
  CONSTRAINT `fk_taxonconcept_taxon1`
    FOREIGN KEY (`taxon_id` )
    REFERENCES `default_schema`.`taxon` (`taxon_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxonconcept_reference1`
    FOREIGN KEY (`concept_reference_id` )
    REFERENCES `default_schema`.`reference` (`reference_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `default_schema`.`taxonverbatim`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonverbatim` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonverbatim` (
  `taxonverbatim_id` INT NOT NULL ,
  `scientificname` VARCHAR(200) NULL DEFAULT NULL ,
  `scientificnameauthorship` VARCHAR(200) NULL DEFAULT NULL ,
  `scientificnamewithauthor` VARCHAR(250) NULL DEFAULT NULL ,
  `kingdom` VARCHAR(50) NULL DEFAULT NULL ,
  `subkingdom` VARCHAR(50) NULL DEFAULT NULL ,
  `division` VARCHAR(50) NULL DEFAULT NULL ,
  `subdivision` VARCHAR(50) NULL DEFAULT NULL ,
  `class` VARCHAR(50) NULL DEFAULT NULL ,
  `subclass` VARCHAR(50) NULL DEFAULT NULL ,
  `order` VARCHAR(50) NULL DEFAULT NULL ,
  `suborder` VARCHAR(50) NULL DEFAULT NULL ,
  `family` VARCHAR(50) NULL DEFAULT NULL ,
  `subfamily` VARCHAR(50) NULL DEFAULT NULL ,
  `tribe` VARCHAR(50) NULL DEFAULT NULL ,
  `subtribe` VARCHAR(50) NULL DEFAULT NULL ,
  `genus` VARCHAR(50) NULL DEFAULT NULL ,
  `subgenus` VARCHAR(50) NULL DEFAULT NULL ,
  `section` VARCHAR(50) NULL DEFAULT NULL ,
  `subsection` VARCHAR(50) NULL DEFAULT NULL ,
  `series` VARCHAR(50) NULL DEFAULT NULL ,
  `subseries` VARCHAR(50) NULL DEFAULT NULL ,
  `species` VARCHAR(50) NULL DEFAULT NULL ,
  `subspecies` VARCHAR(50) NULL DEFAULT NULL ,
  `variety` VARCHAR(50) NULL DEFAULT NULL ,
  `subvariety` VARCHAR(50) NULL DEFAULT NULL ,
  `form` VARCHAR(50) NULL DEFAULT NULL ,
  `matched_taxonconcept_id` INT(11) NULL ,
  `taxonmatched` VARCHAR(100) NULL ,
  `taxonmatchedauthor` VARCHAR(150) NULL ,
  `unmatchedterms` VARCHAR(250) NULL ,
  `matchscore` DECIMAL(3,1) NULL ,
  `taxonmatchedacceptance` VARCHAR(45) NULL ,
  `accepted_taxonconcept_id` INT(11) NULL ,
  `acceptedtaxon` VARCHAR(100) NULL ,
  `acceptedauthor` VARCHAR(150) NULL ,
  `family_taxonconcept_id` INT(11) NULL ,
  `acceptedfamily` VARCHAR(50) NULL ,
  `acceptedgenus` VARCHAR(50) NULL ,
  `acceptedspecies` VARCHAR(45) NULL ,
  `accepted_taxonacceptance` VARCHAR(45) NULL ,
  `acceptedtaxonrank` VARCHAR(45) NULL ,
  `acceptedtaxonmorphospecies` VARCHAR(250) NULL ,
  PRIMARY KEY (`taxonverbatim_id`) ,
  INDEX `fk_taxonverbatim_taxonconcept1` (`matched_taxonconcept_id` ASC) ,
  INDEX `fk_taxonverbatim_taxonconcept2` (`accepted_taxonconcept_id` ASC) ,
  INDEX `fk_taxonverbatim_taxonconcept3` (`family_taxonconcept_id` ASC) ,
  CONSTRAINT `fk_taxonverbatim_taxonconcept1`
    FOREIGN KEY (`matched_taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxonverbatim_taxonconcept2`
    FOREIGN KEY (`accepted_taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxonverbatim_taxonconcept3`
    FOREIGN KEY (`family_taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB, 
COMMENT = '\n' ;


-- -----------------------------------------------------
-- Table `default_schema`.`taxondetermination`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxondetermination` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxondetermination` (
  `taxondetermination_id` INT(11) NOT NULL ,
  `taxonverbatim_id` INT NOT NULL ,
  `taxonoccurrence_id` INT(11) NOT NULL ,
  `determiner_party_id` INT(11) NULL DEFAULT NULL ,
  `determiner_role` VARCHAR(250) NOT NULL DEFAULT 'unknown' ,
  `determinationtype` TEXT NULL DEFAULT NULL ,
  `determination_reference_id` INT NOT NULL ,
  `isoriginal` INT(1) NOT NULL DEFAULT false ,
  `iscurrent` INT(1) NOT NULL DEFAULT false ,
  `taxonfit` TEXT NULL DEFAULT NULL ,
  `taxonconfidence` TEXT NULL DEFAULT NULL ,
  `grouptype` TEXT NULL DEFAULT NULL ,
  `notes` TEXT NULL DEFAULT NULL ,
  `notespublic` INT(1) NULL DEFAULT NULL ,
  `notesmgt` INT(1) NULL DEFAULT NULL ,
  `revisions` INT(1) NULL DEFAULT NULL ,
  `determinationdate` TIMESTAMP NULL DEFAULT NULL ,
  `taxonverbatim_id` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`taxondetermination_id`) ,
  INDEX `fk_taxondetermination_taxonverbatim1` (`taxonverbatim_id` ASC) ,
  INDEX `fk_taxondetermination_reference1` (`determination_reference_id` ASC) ,
  CONSTRAINT `fk_taxondetermination_taxonverbatim1`
    FOREIGN KEY (`taxonverbatim_id` )
    REFERENCES `default_schema`.`taxonverbatim` (`taxonverbatim_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxondetermination_reference1`
    FOREIGN KEY (`determination_reference_id` )
    REFERENCES `default_schema`.`reference` (`reference_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `default_schema`.`taxonstatus`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonstatus` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonstatus` (
  `taxonstatus_id` INT(11) NOT NULL ,
  `taxonconcept_id` INT(11) NOT NULL ,
  `party_id` INT(11) NULL DEFAULT NULL ,
  `taxonconceptstatus` VARCHAR(250) NOT NULL DEFAULT 'undetermined' ,
  `reference_id` INT(11) NULL DEFAULT NULL ,
  `taxonpartycomments` TEXT NULL DEFAULT NULL ,
  `startdate` TIMESTAMP NULL DEFAULT NULL ,
  `stopdate` TIMESTAMP NULL DEFAULT NULL ,
  PRIMARY KEY (`taxonstatus_id`) ,
  UNIQUE INDEX `taxonstatus_unique` (`taxonconcept_id` ASC, `party_id` ASC) ,
  CONSTRAINT `taxonstatus_taxonconcept_id_fkey`
    FOREIGN KEY (`taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE);


-- -----------------------------------------------------
-- Table `default_schema`.`taxoncorrelation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxoncorrelation` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxoncorrelation` (
  `taxoncorrelation_id` INT(11) NOT NULL ,
  `taxonstatus_id` INT(11) NOT NULL ,
  `taxonconcept_id` INT(11) NOT NULL ,
  `taxonconcept_id` INT(11) NOT NULL ,
  `plantconvergence` TEXT NOT NULL ,
  `correlationstart` TIMESTAMP NOT NULL ,
  `correlationstop` TIMESTAMP NULL DEFAULT NULL ,
  PRIMARY KEY (`taxoncorrelation_id`) ,
  INDEX `taxoncorrelation_taxonstatus_id_fkey` (`taxonstatus_id` ASC) ,
  INDEX `fk_taxoncorrelation_taxonconcept1` (`taxonconcept_id` ASC) ,
  CONSTRAINT `taxoncorrelation_taxonstatus_id_fkey`
    FOREIGN KEY (`taxonstatus_id` )
    REFERENCES `default_schema`.`taxonstatus` (`taxonstatus_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_taxoncorrelation_taxonconcept1`
    FOREIGN KEY (`taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `default_schema`.`taxonlineage`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonlineage` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonlineage` (
  `taxonlineage_id` INT(11) NOT NULL ,
  `childtaxonstatus_id` INT(11) NOT NULL ,
  `parenttaxonstatus_id` INT(11) NOT NULL ,
  PRIMARY KEY (`taxonlineage_id`) ,
  INDEX `taxonlineage_childtaxonstatus_id_fkey` (`childtaxonstatus_id` ASC) ,
  INDEX `taxonlineage_parenttaxonstatus_id_fkey` (`parenttaxonstatus_id` ASC) ,
  CONSTRAINT `taxonlineage_childtaxonstatus_id_fkey`
    FOREIGN KEY (`childtaxonstatus_id` )
    REFERENCES `default_schema`.`taxonstatus` (`taxonstatus_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `taxonlineage_parenttaxonstatus_id_fkey`
    FOREIGN KEY (`parenttaxonstatus_id` )
    REFERENCES `default_schema`.`taxonstatus` (`taxonstatus_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE);


-- -----------------------------------------------------
-- Table `default_schema`.`taxonclassification`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonclassification` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonclassification` (
  `taxonclassification_id` INT NOT NULL ,
  `taxonconcept_id` INT(11) NOT NULL ,
  `parent_taxonconcept_id` INT(11) NOT NULL ,
  PRIMARY KEY (`taxonclassification_id`) ,
  INDEX `fk_taxonclassification_taxonconcept1` (`taxonconcept_id` ASC) ,
  INDEX `fk_taxonclassification_taxonconcept2` (`parent_taxonconcept_id` ASC) ,
  CONSTRAINT `fk_taxonclassification_taxonconcept1`
    FOREIGN KEY (`taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxonclassification_taxonconcept2`
    FOREIGN KEY (`parent_taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `default_schema`.`taxon`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxon` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxon` (
  `taxon_id` INT NOT NULL ,
  `taxonrank` VARCHAR(45) NOT NULL ,
  `scientificname` VARCHAR(100) NOT NULL ,
  `scientificnameauthorship` VARCHAR(150) NULL ,
  `authorship_reference_id` INT NULL ,
  PRIMARY KEY (`taxon_id`) ,
  INDEX `fk_taxon_reference2` (`authorship_reference_id` ASC) ,
  CONSTRAINT `fk_taxon_reference2`
    FOREIGN KEY (`authorship_reference_id` )
    REFERENCES `default_schema`.`reference` (`reference_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `default_schema`.`taxonclassification`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonclassification` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonclassification` (
  `taxonclassification_id` INT NOT NULL ,
  `taxonconcept_id` INT(11) NOT NULL ,
  `parent_taxonconcept_id` INT(11) NOT NULL ,
  PRIMARY KEY (`taxonclassification_id`) ,
  INDEX `fk_taxonclassification_taxonconcept1` (`taxonconcept_id` ASC) ,
  INDEX `fk_taxonclassification_taxonconcept2` (`parent_taxonconcept_id` ASC) ,
  CONSTRAINT `fk_taxonclassification_taxonconcept1`
    FOREIGN KEY (`taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxonclassification_taxonconcept2`
    FOREIGN KEY (`parent_taxonconcept_id` )
    REFERENCES `default_schema`.`taxonconcept` (`taxonconcept_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `default_schema`.`taxonclassification_reference`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `default_schema`.`taxonclassification_reference` ;

CREATE  TABLE IF NOT EXISTS `default_schema`.`taxonclassification_reference` (
  `taxonclassification_reference_id` INT NOT NULL ,
  `taxonclassification_id` INT NOT NULL ,
  `reference_id` INT NOT NULL ,
  `taxonclassification_start` TIMESTAMP NULL ,
  `taxonclassification_stop` TIMESTAMP NULL ,
  PRIMARY KEY (`taxonclassification_reference_id`) ,
  INDEX `fk_taxonclassification_reference_taxonclassification1` (`taxonclassification_id` ASC) ,
  INDEX `fk_taxonclassification_reference_reference1` (`reference_id` ASC) ,
  CONSTRAINT `fk_taxonclassification_reference_taxonclassification1`
    FOREIGN KEY (`taxonclassification_id` )
    REFERENCES `default_schema`.`taxonclassification` (`taxonclassification_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_taxonclassification_reference_reference1`
    FOREIGN KEY (`reference_id` )
    REFERENCES `default_schema`.`reference` (`reference_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;