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`.`Species_map`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `image_annotation`.`Species_map` ;

CREATE  TABLE IF NOT EXISTS `image_annotation`.`Species_map` (
  `ubio_id` INT(11) NOT NULL ,
  `po_species_id` INT(11) NOT NULL ,
  PRIMARY KEY (`ubio_id`) ,
  INDEX `fk_Species_map_1` (`po_species_id` ASC) ,
  CONSTRAINT `fk_Species_map_1`
    FOREIGN KEY (`po_species_id` )
    REFERENCES `po_beta`.`species` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


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

CREATE  TABLE IF NOT EXISTS `image_annotation`.`Annotated_term_type` (
  `annotated_term_type_id` INT(11) NOT NULL ,
  `annotated_term_type` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`annotated_term_type_id`) )
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(11) NOT NULL ,
  `annotated_term` VARCHAR(45) NULL DEFAULT NULL ,
  `annotated_term_type_id` INT(11) NULL DEFAULT NULL ,
  `po_term_id` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`annotated_term_id`) ,
  INDEX `fk_Annotated Term_1` (`annotated_term_type_id` ASC) ,
  INDEX `fk_Annotated_term_2` (`po_term_id` ASC) ,
  CONSTRAINT `fk_Annotated Term_1`
    FOREIGN KEY (`annotated_term_type_id` )
    REFERENCES `image_annotation`.`Annotated_term_type` (`annotated_term_type_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_term_2`
    FOREIGN KEY (`po_term_id` )
    REFERENCES `po_beta`.`term` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- 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(11) NOT NULL ,
  `image_path` VARCHAR(255) NULL DEFAULT NULL ,
  `curator` VARCHAR(45) NULL DEFAULT NULL ,
  `comments` VARCHAR(45) NULL DEFAULT NULL ,
  `annotated_term_id` INT(11) NULL DEFAULT NULL ,
  `ubio_id` INT(11) NULL DEFAULT NULL ,
  `ubio_name` VARCHAR(45) NULL DEFAULT NULL ,
  `species_name` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`annotated_image_id`) ,
  INDEX `fk_Annotated_image_1` (`ubio_id` ASC) ,
  INDEX `fk_Annotated_image_3` (`annotated_term_id` ASC) ,
  CONSTRAINT `fk_Annotated_image_1`
    FOREIGN KEY (`ubio_id` )
    REFERENCES `image_annotation`.`Species_map` (`ubio_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_image_3`
    FOREIGN KEY (`annotated_term_id` )
    REFERENCES `image_annotation`.`Annotated_term` (`annotated_term_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


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

CREATE  TABLE IF NOT EXISTS `image_annotation`.`Annotated_keyword` (
  `annotated_keyword_id` INT(11) NOT NULL ,
  `annotated_keyword` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`annotated_keyword_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


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

CREATE  TABLE IF NOT EXISTS `image_annotation`.`Annotated_keywordlist` (
  `annotated_image_id` INT(11) NOT NULL ,
  `annotated_keyword_id` INT(11) NOT NULL ,
  PRIMARY KEY (`annotated_image_id`, `annotated_keyword_id`) ,
  INDEX `fk_Annotated_keywordlist_1` (`annotated_image_id` ASC) ,
  INDEX `fk_Annotated_keywordlist_2` (`annotated_keyword_id` ASC) ,
  CONSTRAINT `fk_Annotated_keywordlist_1`
    FOREIGN KEY (`annotated_image_id` )
    REFERENCES `image_annotation`.`Annotated_image` (`annotated_image_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Annotated_keywordlist_2`
    FOREIGN KEY (`annotated_keyword_id` )
    REFERENCES `image_annotation`.`Annotated_keyword` (`annotated_keyword_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
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(11) NOT NULL ,
  `color` VARCHAR(45) NULL DEFAULT NULL ,
  `annotated_image_id` INT(11) NULL DEFAULT NULL ,
  `byte_mask` BLOB NULL DEFAULT NULL ,
  `coordinates` LINESTRING NULL DEFAULT NULL ,
  `annotated_term_id` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`segment_id`) ,
  INDEX `fk_Segment_2` (`annotated_term_id` ASC) ,
  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,
  CONSTRAINT `fk_Segment_2`
    FOREIGN KEY (`annotated_term_id` )
    REFERENCES `image_annotation`.`Annotated_term` (`annotated_term_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;



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