-- MySQL Workbench Forward Engineering

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mappi-dat
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mappi-dat
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mappi-dat` DEFAULT CHARACTER SET utf8 ;
USE `mappi-dat` ;

-- -----------------------------------------------------
-- Table `mappi-dat`.`mixtureinfo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`mixtureinfo` (
  `mix_id` INT(11) NOT NULL AUTO_INCREMENT,
  `mix_date` DATE NOT NULL,
  `Madeby` VARCHAR(45) NOT NULL,
  `m_type` VARCHAR(45) NULL DEFAULT NULL,
  `extra_info` VARCHAR(45) NULL DEFAULT NULL,
  `mixplate_name` VARCHAR(45) NULL DEFAULT NULL,
  `No_art_Info` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`mix_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 10
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`art`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`art` (
  `art_id` INT(11) NOT NULL AUTO_INCREMENT,
  `a_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`art_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 163
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`art_info`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`art_info` (
  `art_id` INT(11) NOT NULL AUTO_INCREMENT,
  `art_date` DATE NULL DEFAULT NULL,
  `prepared_by` VARCHAR(45) NULL DEFAULT NULL,
  `extra_info` VARCHAR(45) NULL DEFAULT NULL,
  `ref_artid` INT(11) NOT NULL,
  PRIMARY KEY (`art_id`),
  INDEX `fk_main_art_info_art1_idx` (`ref_artid` ASC),
  CONSTRAINT `fk_main_art_info_art1`
    FOREIGN KEY (`ref_artid`)
    REFERENCES `mappi-dat`.`art` (`art_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`art_aliquot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`art_aliquot` (
  `ali_id` INT(11) NOT NULL AUTO_INCREMENT,
  `ali_name` VARCHAR(45) NULL DEFAULT NULL,
  `ali_date` DATE NULL DEFAULT NULL,
  `prepared_by` VARCHAR(45) NULL DEFAULT NULL,
  `extra_info` VARCHAR(45) NULL DEFAULT NULL,
  `ref_artinfoid` INT(11) NOT NULL,
  PRIMARY KEY (`ali_id`),
  INDEX `fk_art_info_art_main1_idx` (`ref_artinfoid` ASC),
  CONSTRAINT `fk_art_info_art_main1`
    FOREIGN KEY (`ref_artinfoid`)
    REFERENCES `mappi-dat`.`art_info` (`art_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`aliqot2mix`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`aliqot2mix` (
  `ref_aliqotid` INT(11) NOT NULL,
  `ref_mixid` INT(11) NOT NULL,
  PRIMARY KEY (`ref_aliqotid`, `ref_mixid`),
  INDEX `fk_art_info_has_MixtureInfo_MixtureInfo1_idx` (`ref_mixid` ASC),
  INDEX `fk_art_info_has_MixtureInfo_art_info1_idx` (`ref_aliqotid` ASC),
  CONSTRAINT `fk_art_info_has_MixtureInfo_MixtureInfo1`
    FOREIGN KEY (`ref_mixid`)
    REFERENCES `mappi-dat`.`mixtureinfo` (`mix_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_art_info_has_MixtureInfo_art_info1`
    FOREIGN KEY (`ref_aliqotid`)
    REFERENCES `mappi-dat`.`art_aliquot` (`ali_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`project` (
  `project_id` INT(11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`project_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`bait`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`bait` (
  `bait_id` INT(11) NOT NULL AUTO_INCREMENT,
  `b_name` VARCHAR(45) NOT NULL,
  `sequence` VARCHAR(1000) NULL DEFAULT NULL,
  PRIMARY KEY (`bait_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`molecule`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`molecule` (
  `mol_id` INT(11) NOT NULL AUTO_INCREMENT,
  `fusion_cpd_type` VARCHAR(45) NOT NULL,
  `fusion_cpd_conc` DOUBLE NOT NULL,
  `extra_info` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`mol_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`experiment_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`experiment_group` (
  `expgrp_id` INT(11) NOT NULL AUTO_INCREMENT,
  `e_name` VARCHAR(45) NOT NULL,
  `ref_projid` INT(11) NOT NULL,
  `ref_molid` INT(11) NULL DEFAULT NULL,
  `ref_baitid` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`expgrp_id`),
  INDEX `fk_Experiment_Group_Project_idx` (`ref_projid` ASC),
  INDEX `fk_Experiment_Group_molecule1_idx` (`ref_molid` ASC),
  INDEX `fk_Experiment_Group_bait1_idx` (`ref_baitid` ASC),
  CONSTRAINT `fk_Experiment_Group_Project`
    FOREIGN KEY (`ref_projid`)
    REFERENCES `mappi-dat`.`project` (`project_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Experiment_Group_bait1`
    FOREIGN KEY (`ref_baitid`)
    REFERENCES `mappi-dat`.`bait` (`bait_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Experiment_Group_molecule1`
    FOREIGN KEY (`ref_molid`)
    REFERENCES `mappi-dat`.`molecule` (`mol_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`exp_condition`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`exp_condition` (
  `condition_id` INT(11) NOT NULL AUTO_INCREMENT,
  `Stimulus_type` VARCHAR(45) NOT NULL,
  `Stimulus_conc` FLOAT NOT NULL,
  `Scanning_date` DATE NOT NULL,
  `mbu_bait_code` VARCHAR(45) NOT NULL,
  `NSreplicate` INT(11) NOT NULL,
  `Sreplicate` INT(11) NOT NULL,
  `bait_vector_type` VARCHAR(45) NOT NULL,
  `protocol_version` VARCHAR(45) NOT NULL,
  `treatment_type` VARCHAR(45) NULL DEFAULT NULL,
  `treatment_con` DOUBLE NULL DEFAULT NULL,
  `treatment_date` DATE NULL DEFAULT NULL,
  `input_date` DATE NOT NULL,
  `start_treatment` TIME NULL DEFAULT NULL,
  `end_treatment` TIME NULL DEFAULT NULL,
  `BaitTransfectDate` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`condition_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`experiments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`experiments` (
  `experiment_id` INT(11) NOT NULL AUTO_INCREMENT,
  `e_name` VARCHAR(45) NOT NULL,
  `e_quality` INT(11) NULL DEFAULT NULL,
  `ref_expgrpid` INT(11) NOT NULL,
  `ref_conditionid` INT(11) NOT NULL,
  `description` VARCHAR(200) NOT NULL,
  `experiment_type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`experiment_id`),
  INDEX `fk_Experiments_Experiment_Group1_idx` (`ref_expgrpid` ASC),
  INDEX `fk_Experiments_condition1_idx` (`ref_conditionid` ASC),
  CONSTRAINT `fk_Experiments_Experiment_Group1`
    FOREIGN KEY (`ref_expgrpid`)
    REFERENCES `mappi-dat`.`experiment_group` (`expgrp_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Experiments_condition1`
    FOREIGN KEY (`ref_conditionid`)
    REFERENCES `mappi-dat`.`exp_condition` (`condition_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`analysis_parameters`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`analysis_parameters` (
  `analysis_id` INT(11) NOT NULL AUTO_INCREMENT,
  `q_val_threshold` DOUBLE NOT NULL,
  `particle_count_filtration` INT(11) NOT NULL COMMENT 'if 0 means no filtration',
  `quartile_filtration` TINYINT(1) NOT NULL,
  `ref_expid` INT(11) NOT NULL,
  `method_name` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`analysis_id`),
  INDEX `fk_analysis_parameters_Experiments1_idx` (`ref_expid` ASC),
  CONSTRAINT `fk_analysis_parameters_Experiments1`
    FOREIGN KEY (`ref_expid`)
    REFERENCES `mappi-dat`.`experiments` (`experiment_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`art_well`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`art_well` (
  `well_id` INT(11) NOT NULL AUTO_INCREMENT,
  `w_name` VARCHAR(45) NOT NULL,
  `well1id` INT(11) NULL DEFAULT NULL,
  `well2id` INT(11) NULL DEFAULT NULL,
  `well3id` INT(11) NULL DEFAULT NULL,
  `well4id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`well_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 15553
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`art_well_info`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`art_well_info` (
  `well_info_id` INT(11) NOT NULL AUTO_INCREMENT,
  `conc` VARCHAR(45) NULL DEFAULT NULL,
  `ratio` VARCHAR(45) NULL DEFAULT NULL,
  `ref_artwellid` INT(11) NOT NULL,
  PRIMARY KEY (`well_info_id`),
  INDEX `fk_art_well_info_art_well1_idx` (`ref_artwellid` ASC),
  CONSTRAINT `fk_art_well_info_art_well1`
    FOREIGN KEY (`ref_artwellid`)
    REFERENCES `mappi-dat`.`art_well` (`well_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`art2well_info`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`art2well_info` (
  `ref_artwellid` INT(11) NOT NULL,
  `ref_artinfoid` INT(11) NOT NULL,
  PRIMARY KEY (`ref_artwellid`, `ref_artinfoid`),
  INDEX `fk_art_well_info_has_art_info_art_info1_idx` (`ref_artinfoid` ASC),
  INDEX `fk_art_well_info_has_art_info_art_well_info1_idx` (`ref_artwellid` ASC),
  CONSTRAINT `fk_art_well_info_has_art_info_art_info1`
    FOREIGN KEY (`ref_artinfoid`)
    REFERENCES `mappi-dat`.`art_info` (`art_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_art_well_info_has_art_info_art_well_info1`
    FOREIGN KEY (`ref_artwellid`)
    REFERENCES `mappi-dat`.`art_well_info` (`well_info_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`artwell_has_art`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`artwell_has_art` (
  `ref_wellid` INT(11) NOT NULL,
  `ref_artid` INT(11) NOT NULL,
  PRIMARY KEY (`ref_wellid`, `ref_artid`),
  INDEX `fk_art_well_has_art_art1_idx` (`ref_artid` ASC),
  INDEX `fk_art_well_has_art_art_well1_idx` (`ref_wellid` ASC),
  CONSTRAINT `fk_art_well_has_art_art1`
    FOREIGN KEY (`ref_artid`)
    REFERENCES `mappi-dat`.`art` (`art_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_art_well_has_art_art_well1`
    FOREIGN KEY (`ref_wellid`)
    REFERENCES `mappi-dat`.`art_well` (`well_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`preyannotationt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`preyannotationt` (
  `pan_id` INT(11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`pan_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 10
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`mixture`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`mixture` (
  `mixture_id` INT(11) NOT NULL AUTO_INCREMENT,
  `mixture_name` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`mixture_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 13
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`prey`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`prey` (
  `prey_id` INT(11) NOT NULL AUTO_INCREMENT,
  `Sequence` VARCHAR(1000) NULL DEFAULT NULL,
  `p_name` VARCHAR(45) NOT NULL,
  `ref_panid` INT(11) NULL DEFAULT NULL,
  `entrenz_name` VARCHAR(45) NOT NULL,
  `plate_nr` VARCHAR(45) NOT NULL,
  `ref_artid` INT(11) NOT NULL,
  `ref_mixtureid` INT(11) NOT NULL,
  `ref_artwellid` INT(11) NOT NULL,
  PRIMARY KEY (`prey_id`),
  INDEX `fk_prey_PreyAnnotaionT1_idx` (`ref_panid` ASC),
  INDEX `fk_prey_art1_idx` (`ref_artid` ASC),
  INDEX `fk_prey_mixture1_idx` (`ref_mixtureid` ASC),
  INDEX `fk_prey_art_well1_idx` (`ref_artwellid` ASC),
  CONSTRAINT `fk_prey_PreyAnnotaionT1`
    FOREIGN KEY (`ref_panid`)
    REFERENCES `mappi-dat`.`preyannotationt` (`pan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_prey_art1`
    FOREIGN KEY (`ref_artid`)
    REFERENCES `mappi-dat`.`art` (`art_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_prey_art_well1`
    FOREIGN KEY (`ref_artwellid`)
    REFERENCES `mappi-dat`.`art_well` (`well_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_prey_mixture1`
    FOREIGN KEY (`ref_mixtureid`)
    REFERENCES `mappi-dat`.`mixture` (`mixture_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 15553
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`aspecifics`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`aspecifics` (
  `asep_id` INT(11) NOT NULL AUTO_INCREMENT,
  `ref_preyid` INT(11) NOT NULL,
  `ref_experimentid` INT(11) NOT NULL,
  PRIMARY KEY (`asep_id`),
  INDEX `fk_aspecifics_prey1_idx` (`ref_preyid` ASC),
  INDEX `fk_aspecifics_Experiments1_idx` (`ref_experimentid` ASC),
  CONSTRAINT `fk_aspecifics_Experiments1`
    FOREIGN KEY (`ref_experimentid`)
    REFERENCES `mappi-dat`.`experiments` (`experiment_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_aspecifics_prey1`
    FOREIGN KEY (`ref_preyid`)
    REFERENCES `mappi-dat`.`prey` (`prey_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1071
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`plates`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`plates` (
  `plate_id` INT(11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR(45) NOT NULL,
  `printingDate` DATE NOT NULL,
  `ref_panid` INT(11) NULL DEFAULT NULL,
  `ref_mixinfoid` INT(11) NOT NULL,
  `prepared_by` VARCHAR(45) NULL DEFAULT NULL,
  `extra_info` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`plate_id`),
  INDEX `fk_plates_PreyAnnotationT1_idx` (`ref_panid` ASC),
  INDEX `fk_plates_MixtureInfo1_idx` (`ref_mixinfoid` ASC),
  CONSTRAINT `fk_plates_MixtureInfo1`
    FOREIGN KEY (`ref_mixinfoid`)
    REFERENCES `mappi-dat`.`mixtureinfo` (`mix_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_plates_PreyAnnotationT1`
    FOREIGN KEY (`ref_panid`)
    REFERENCES `mappi-dat`.`preyannotationt` (`pan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 10
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`exp_has_plates`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`exp_has_plates` (
  `ref_expid` INT(11) NOT NULL,
  `ref_plateid` INT(11) NOT NULL,
  PRIMARY KEY (`ref_expid`, `ref_plateid`),
  INDEX `fk_experiments_has_plates_plates1_idx` (`ref_plateid` ASC),
  INDEX `fk_experiments_has_plates_experiments1_idx` (`ref_expid` ASC),
  CONSTRAINT `fk_experiments_has_plates_experiments1`
    FOREIGN KEY (`ref_expid`)
    REFERENCES `mappi-dat`.`experiments` (`experiment_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_experiments_has_plates_plates1`
    FOREIGN KEY (`ref_plateid`)
    REFERENCES `mappi-dat`.`plates` (`plate_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest` (
  `restest_id` INT(11) NOT NULL AUTO_INCREMENT,
  `reason` VARCHAR(200) NOT NULL,
  `r_name` VARCHAR(45) NOT NULL,
  `submission_date` DATE NOT NULL,
  `retest_date` DATE NOT NULL,
  `done_by` VARCHAR(45) NULL DEFAULT NULL,
  `treatment` TINYINT(1) NOT NULL,
  `threshold` DOUBLE NOT NULL,
  `kiss` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`restest_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`experiments_has_retest`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`experiments_has_retest` (
  `ref_expid` INT(11) NOT NULL,
  `ref_retestid` INT(11) NOT NULL,
  PRIMARY KEY (`ref_retestid`, `ref_expid`),
  INDEX `fk_Experiments_has_re_test_parameters_re_test_parameters1_idx` (`ref_retestid` ASC),
  INDEX `fk_Experiments_has_re_test_parameters_Experiments1_idx` (`ref_expid` ASC),
  CONSTRAINT `fk_Experiments_has_re_test_parameters_Experiments1`
    FOREIGN KEY (`ref_expid`)
    REFERENCES `mappi-dat`.`experiments` (`experiment_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Experiments_has_re_test_parameters_re_test_parameters1`
    FOREIGN KEY (`ref_retestid`)
    REFERENCES `mappi-dat`.`retest` (`restest_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`interactors`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`interactors` (
  `idInteractors` INT(11) NOT NULL AUTO_INCREMENT,
  `ref_expgrpid` INT(11) NOT NULL,
  `ref_preyid` INT(11) NOT NULL,
  `inttype` VARCHAR(45) NOT NULL COMMENT 'control+, NA, NAc+, NAsd+, SD+control,A-specific',
  PRIMARY KEY (`idInteractors`),
  INDEX `fk_Interactors_Experiment_Group1_idx` (`ref_expgrpid` ASC),
  INDEX `fk_Interactors_protein1_idx` (`ref_preyid` ASC),
  CONSTRAINT `fk_Interactors_Experiment_Group1`
    FOREIGN KEY (`ref_expgrpid`)
    REFERENCES `mappi-dat`.`experiment_group` (`expgrp_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Interactors_protein1`
    FOREIGN KEY (`ref_preyid`)
    REFERENCES `mappi-dat`.`prey` (`prey_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 77761
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`possiblehit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`possiblehit` (
  `possiblehit_id` INT(11) NOT NULL AUTO_INCREMENT,
  `ref_interactorid` INT(11) NOT NULL,
  `fc` DOUBLE NOT NULL,
  `q_val` DOUBLE NOT NULL,
  `p_value` DOUBLE NOT NULL,
  `ref_analysis_parameterid` INT(11) NOT NULL,
  `filtrationLabel` VARCHAR(45) NOT NULL COMMENT 'get tagging from Rankprod analysis\n\ncontrol, Quartile threshold ,PC threshold, PC and Quartile threshold, - (if not filtered)\n\n',
  PRIMARY KEY (`possiblehit_id`),
  INDEX `fk_PossibleHit_Interactors2_idx` (`ref_interactorid` ASC),
  INDEX `fk_PossibleHit_analysis_parameters1_idx` (`ref_analysis_parameterid` ASC),
  CONSTRAINT `fk_PossibleHit_Interactors2`
    FOREIGN KEY (`ref_interactorid`)
    REFERENCES `mappi-dat`.`interactors` (`idInteractors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PossibleHit_analysis_parameters1`
    FOREIGN KEY (`ref_analysis_parameterid`)
    REFERENCES `mappi-dat`.`analysis_parameters` (`analysis_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 889
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`plates_has_possiblehit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`plates_has_possiblehit` (
  `ref_plateid` INT(11) NOT NULL,
  `ref_possiblehitid` INT(11) NOT NULL,
  PRIMARY KEY (`ref_plateid`, `ref_possiblehitid`),
  INDEX `fk_plates_has_PossibleHit_PossibleHit1_idx` (`ref_possiblehitid` ASC),
  INDEX `fk_plates_has_PossibleHit_plates1_idx` (`ref_plateid` ASC),
  CONSTRAINT `fk_plates_has_PossibleHit_PossibleHit1`
    FOREIGN KEY (`ref_possiblehitid`)
    REFERENCES `mappi-dat`.`possiblehit` (`possiblehit_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_plates_has_PossibleHit_plates1`
    FOREIGN KEY (`ref_plateid`)
    REFERENCES `mappi-dat`.`plates` (`plate_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`well`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`well` (
  `well_id` INT(11) NOT NULL AUTO_INCREMENT,
  `w_name` VARCHAR(45) NOT NULL COMMENT 'W1,W2,W3,W4',
  `Stimulus_type` VARCHAR(45) NOT NULL COMMENT 'NS, S',
  `ref_platid` INT(11) NOT NULL,
  PRIMARY KEY (`well_id`),
  INDEX `fk_well_plates1_idx` (`ref_platid` ASC),
  CONSTRAINT `fk_well_plates1`
    FOREIGN KEY (`ref_platid`)
    REFERENCES `mappi-dat`.`plates` (`plate_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 181
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`spots`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`spots` (
  `spot_id` INT(11) NOT NULL AUTO_INCREMENT,
  `row` INT(11) NOT NULL,
  `col` INT(11) NOT NULL,
  `block` INT(11) NOT NULL,
  `ref_wellid` INT(11) NOT NULL,
  `ref_interactorid` INT(11) NOT NULL,
  PRIMARY KEY (`spot_id`),
  INDEX `fk_spots_well1_idx` (`ref_wellid` ASC),
  INDEX `fk_spots_Interactors1_idx` (`ref_interactorid` ASC),
  CONSTRAINT `fk_spots_Interactors1`
    FOREIGN KEY (`ref_interactorid`)
    REFERENCES `mappi-dat`.`interactors` (`idInteractors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_spots_well1`
    FOREIGN KEY (`ref_wellid`)
    REFERENCES `mappi-dat`.`well` (`well_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 311041
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`quantificationval`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`quantificationval` (
  `qval_id` INT(11) NOT NULL AUTO_INCREMENT,
  `pc` INT(11) NOT NULL,
  `area_fraction` DOUBLE NOT NULL,
  `grayValMean` DOUBLE NOT NULL,
  `meanGrayValMean` DOUBLE NOT NULL,
  `meanArea` DOUBLE NOT NULL,
  `MeanIntInt` DOUBLE NOT NULL,
  `IntInt` DOUBLE NOT NULL,
  `sumIntInt` DOUBLE NULL DEFAULT NULL,
  `ref_spotid` INT(11) NOT NULL,
  `residual` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`qval_id`),
  INDEX `fk_quantificationVal_spots1_idx` (`ref_spotid` ASC),
  CONSTRAINT `fk_quantificationVal_spots1`
    FOREIGN KEY (`ref_spotid`)
    REFERENCES `mappi-dat`.`spots` (`spot_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 311041
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest_controls`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest_controls` (
  `control_id` INT(11) NOT NULL AUTO_INCREMENT,
  `uniq_name` VARCHAR(45) NOT NULL,
  `full_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`control_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest_method`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest_method` (
  `method_id` INT(11) NOT NULL AUTO_INCREMENT,
  `methodName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`method_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest_analysis_output`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest_analysis_output` (
  `pos_id` INT(11) NOT NULL AUTO_INCREMENT,
  `FC_baitNull` DOUBLE NOT NULL COMMENT 'FI_baitNull=(SB*NS)/(NSB*S)\n\nSB=average of stimulus with bait\nNSB=aeverage of no stimulus with bait\nS=average of stimulus without bait\nNS=average of non stimulus without bait ',
  `FC_preyNull` DOUBLE NOT NULL COMMENT 'FI_PreyNull=(SB*CNSB)/(NSB*CSB)\n\nSB=average of stimulus with bait\nNSB=aeverage of no stimulus with bait\nCSB=average of stimulus with bait of empty prey control\nCNSB=average of non stimulus with bait  of empt prey control',
  `tag` VARCHAR(45) NOT NULL COMMENT 'tagging of data after analysis: Aspecific,Positive,Negative,NotAnnotated(when they are not sure if it positive or negative)\n\nif (SB+/NSB+)>9:\n     if min(FI(preyNull,BaitNull))>9:\n            positive\n     else:\n             if (SB-/NSB-)>9:\n                   Aspecific\n              else:\n                    NotAnnotated\nelse:\n       negative',
  `type` VARCHAR(45) NOT NULL COMMENT 'type can take the values: control,emptypreycontrol,normal',
  `ref_preyid` INT(11) NULL DEFAULT NULL,
  `ref_methodid` INT(11) NULL DEFAULT NULL,
  `tagT` VARCHAR(45) NULL DEFAULT NULL,
  `FC_baitNullT` DOUBLE NULL DEFAULT NULL,
  `FC_preyNullT` DOUBLE NULL DEFAULT NULL,
  `ref_conid` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`pos_id`),
  INDEX `fk_retest_positives_prey1_idx` (`ref_preyid` ASC),
  INDEX `fk_retest_analysis_output_retest_method1_idx` (`ref_methodid` ASC),
  INDEX `fk_retest_analysis_output_reTestControls1_idx` (`ref_conid` ASC),
  CONSTRAINT `fk_retest_analysis_output_reTestControls1`
    FOREIGN KEY (`ref_conid`)
    REFERENCES `mappi-dat`.`retest_controls` (`control_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_retest_analysis_output_retest_method1`
    FOREIGN KEY (`ref_methodid`)
    REFERENCES `mappi-dat`.`retest_method` (`method_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_retest_positives_prey1`
    FOREIGN KEY (`ref_preyid`)
    REFERENCES `mappi-dat`.`prey` (`prey_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1133
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest_conditions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest_conditions` (
  `condition_id` INT(11) NOT NULL AUTO_INCREMENT,
  `dna_conc` DOUBLE NOT NULL,
  `dnaOD_260_280ratio` DOUBLE NOT NULL,
  `well_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`condition_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2377
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest_files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest_files` (
  `file_id` INT(11) NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(45) NOT NULL,
  `ref_retest` INT(11) NOT NULL,
  `file_type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`file_id`),
  INDEX `fk_retest_files_re_test_parameters1_idx` (`ref_retest` ASC),
  CONSTRAINT `fk_retest_files_re_test_parameters1`
    FOREIGN KEY (`ref_retest`)
    REFERENCES `mappi-dat`.`retest` (`restest_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 246
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`retest_eachcell_intensity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`retest_eachcell_intensity` (
  `arrang_id` INT(11) NOT NULL AUTO_INCREMENT,
  `wellName` VARCHAR(45) NOT NULL,
  `well_type` VARCHAR(45) NOT NULL COMMENT 'this defines if the well is NS,S,NSB+ or SB+',
  `intensity` VARCHAR(45) NOT NULL,
  `ref_fileid` INT(11) NOT NULL,
  `ref_retest_analyid` INT(11) NOT NULL,
  `ref_condid` INT(11) NOT NULL,
  PRIMARY KEY (`arrang_id`),
  INDEX `fk_retest_plate_arrangment_retest_files1_idx` (`ref_fileid` ASC),
  INDEX `fk_retest_plate_arrangment_retest_analysis_output1_idx` (`ref_retest_analyid` ASC),
  INDEX `fk_retest_eachCell_intensity_retest_conditions1_idx` (`ref_condid` ASC),
  CONSTRAINT `fk_retest_eachCell_intensity_retest_conditions1`
    FOREIGN KEY (`ref_condid`)
    REFERENCES `mappi-dat`.`retest_conditions` (`condition_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_retest_plate_arrangment_retest_analysis_output1`
    FOREIGN KEY (`ref_retest_analyid`)
    REFERENCES `mappi-dat`.`retest_analysis_output` (`pos_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_retest_plate_arrangment_retest_files1`
    FOREIGN KEY (`ref_fileid`)
    REFERENCES `mappi-dat`.`retest_files` (`file_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 13597
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mappi-dat`.`tplaterelation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappi-dat`.`tplaterelation` (
  `ref_experimentid` INT(11) NOT NULL,
  `ref_panid` INT(11) NOT NULL,
  INDEX `fk_Experiments_has_PreyAnnotaionT_PreyAnnotaionT1_idx` (`ref_panid` ASC),
  INDEX `fk_Experiments_has_PreyAnnotaionT_Experiments1_idx` (`ref_experimentid` ASC),
  CONSTRAINT `fk_Experiments_has_PreyAnnotaionT_Experiments1`
    FOREIGN KEY (`ref_experimentid`)
    REFERENCES `mappi-dat`.`experiments` (`experiment_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Experiments_has_PreyAnnotaionT_PreyAnnotaionT1`
    FOREIGN KEY (`ref_panid`)
    REFERENCES `mappi-dat`.`preyannotationt` (`pan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


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