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';

CREATE SCHEMA IF NOT EXISTS `image_annotation` DEFAULT CHARACTER SET latin1 ;
USE `image_annotation` ;

-- -----------------------------------------------------
-- Table `image_annotation`.`image_source`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`image_source` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`image_source` (
  `image_source_id` INT NOT NULL AUTO_INCREMENT ,
  `source_name` VARCHAR(80) NULL ,
  `url` VARCHAR(255) NULL ,
  `contact_email` VARCHAR(80) NULL ,
  `contributor_name` VARCHAR(80) NULL ,
  PRIMARY KEY (`image_source_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `image_annotation`.`image_source_version`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`image_source_version` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`image_source_version` (
  `image_source_version_id` INT NOT NULL ,
  `image_source_id` INT NULL ,
  `source_version` VARCHAR(45) NULL ,
  `contribution_date` DATE NULL ,
  `publication_id` VARCHAR(45) NULL ,
  PRIMARY KEY (`image_source_version_id`) ,
  INDEX `fk_Image_source_version_1` (`image_source_id` ASC) ,
  CONSTRAINT `fk_Image_source_version_1`
    FOREIGN KEY (`image_source_id` )
    REFERENCES `image_annotation`.`image_source` (`image_source_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `image_annotation`.`curator`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`curator` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`curator` (
  `curator_id` INT NOT NULL AUTO_INCREMENT ,
  `firstname` VARCHAR(80) NULL ,
  `lastname` VARCHAR(80) NULL ,
  `primary_email` VARCHAR(80) NULL ,
  `alternate_email` VARCHAR(80) NULL ,
  `affiliation` VARCHAR(80) NULL ,
  PRIMARY KEY (`curator_id`) )
ENGINE = InnoDB;


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

CREATE  TABLE IF NOT EXISTS `image_annotation`.`taxon` (
  `taxon_id` INT NOT NULL AUTO_INCREMENT ,
  `species_id` VARCHAR(45) NULL ,
  `species_name` VARCHAR(80) NULL ,
  `common_name` VARCHAR(80) NULL ,
  `genus` VARCHAR(80) NULL ,
  `subspecies` VARCHAR(80) NULL ,
  PRIMARY KEY (`taxon_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `image_annotation`.`annotated_image`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`annotated_image` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`annotated_image` (
  `annotated_image_id` INT NOT NULL AUTO_INCREMENT ,
  `curator_id` INT NULL DEFAULT NULL ,
  `image_source_version_id` INT NULL ,
  `taxon_id` INT NULL DEFAULT NULL ,
  `annotated_image` LONGBLOB NULL ,
  `image_path` VARCHAR(255) NULL DEFAULT NULL ,
  `annotated_image_type` VARCHAR(45) NULL ,
  `timestamp` DATE NULL ,
  `software_name` VARCHAR(45) NULL ,
  `software_version` VARCHAR(32) NULL ,
  `comments` VARCHAR(255) NULL ,
  `ip_comment` VARCHAR(255) NULL ,
  `collection_location` VARCHAR(255) NULL ,
  `collection_date` DATE NULL ,
  `source_db_name` VARCHAR(45) NULL ,
  `source_db_id` INT(11) NULL ,
  `doi` VARCHAR(45) NULL ,
  PRIMARY KEY (`annotated_image_id`) ,
  INDEX `fk_Annotated_image_1` (`image_source_version_id` ASC) ,
  INDEX `fk_Annotated_image_3` (`curator_id` ASC) ,
  INDEX `fk_Annotated_image_2` (`taxon_id` ASC) ,
  CONSTRAINT `fk_Annotated_image_1`
    FOREIGN KEY (`image_source_version_id` )
    REFERENCES `image_annotation`.`image_source_version` (`image_source_version_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_image_3`
    FOREIGN KEY (`curator_id` )
    REFERENCES `image_annotation`.`curator` (`curator_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_image_2`
    FOREIGN KEY (`taxon_id` )
    REFERENCES `image_annotation`.`taxon` (`taxon_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `image_annotation`.`annotated_term`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`annotated_term` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`annotated_term` (
  `annotated_term_id` INT NOT NULL AUTO_INCREMENT ,
  `keyword` VARCHAR(80) NULL DEFAULT NULL ,
  `ontology_term_id` VARCHAR(80) NULL DEFAULT NULL ,
  `ontology_term_name` VARCHAR(80) NULL DEFAULT NULL ,
  `isKeyword` BIT NULL ,
  PRIMARY KEY (`annotated_term_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `image_annotation`.`segment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`segment` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`segment` (
  `segment_id` INT NOT NULL AUTO_INCREMENT ,
  `annotated_image_id` INT NULL ,
  `color` VARCHAR(40) NULL DEFAULT NULL ,
  `byte_mask` LONGBLOB NULL DEFAULT NULL ,
  `coordinates` LINESTRING NULL DEFAULT NULL ,
  PRIMARY KEY (`segment_id`) ,
  INDEX `fk_Segment_1` (`annotated_image_id` ASC) ,
  CONSTRAINT `fk_Segment_1`
    FOREIGN KEY (`annotated_image_id` )
    REFERENCES `image_annotation`.`annotated_image` (`annotated_image_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `image_annotation`.`annotated_term_image`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`annotated_term_image` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`annotated_term_image` (
  `annotated_term_image_id` INT NOT NULL AUTO_INCREMENT ,
  `annotated_term_id` INT NULL ,
  `annotated_image_id` INT NULL ,
  PRIMARY KEY (`annotated_term_image_id`) ,
  INDEX `fk_Annotated_term_image_2` (`annotated_term_id` ASC) ,
  INDEX `fk_Annotated_term_image_1` (`annotated_image_id` ASC) ,
  CONSTRAINT `fk_Annotated_term_image_2`
    FOREIGN KEY (`annotated_term_id` )
    REFERENCES `image_annotation`.`annotated_term` (`annotated_term_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_term_image_1`
    FOREIGN KEY (`annotated_image_id` )
    REFERENCES `image_annotation`.`annotated_image` (`annotated_image_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `image_annotation`.`annotated_term_segment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`annotated_term_segment` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`annotated_term_segment` (
  `annotated_term_segment_id` INT NOT NULL AUTO_INCREMENT ,
  `segment_id` INT NULL ,
  `annotated_term_id` INT NULL ,
  `trait_term_id` INT NULL ,
  PRIMARY KEY (`annotated_term_segment_id`) ,
  INDEX `fk_Annotated_term_segment_1` (`annotated_term_id` ASC) ,
  INDEX `fk_Annotated_term_segment_2` (`segment_id` ASC) ,
  INDEX `fk_Annotated_term_segment_3` (`trait_term_id` ASC) ,
  CONSTRAINT `fk_Annotated_term_segment_1`
    FOREIGN KEY (`annotated_term_id` )
    REFERENCES `image_annotation`.`annotated_term` (`annotated_term_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_term_segment_2`
    FOREIGN KEY (`segment_id` )
    REFERENCES `image_annotation`.`segment` (`segment_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_term_segment_3`
    FOREIGN KEY (`trait_term_id` )
    REFERENCES `image_annotation`.`annotated_term` (`annotated_term_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;
