-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- Begin Table Declaration
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

-- -----------------------------------------------------
-- Table `ohrm_questionnaire`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_questionnaire` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `questionnaire_name` VARCHAR(100) NOT NULL UNIQUE,
  `is_grouping_allowed`   TINYINT(1) DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB default charset=utf8;

-- ---------------------------------------------------------
-- Table `ohrm_question_group`
-- ---------------------------------------------------------
CREATE TABLE ohrm_question_group (
        `id` INT AUTO_INCREMENT, 
        `name` TEXT NOT NULL, 
        `alias` TEXT, 
        `questionnaire_id` INT NOT NULL, 
        PRIMARY KEY(`id`),
        FOREIGN KEY (`questionnaire_id`)  REFERENCES `ohrm_questionnaire` (`id`) ON UPDATE CASCADE ON DELETE CASCADE)
ENGINE = INNODB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_question_type`i
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_answer_type` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `type_name` VARCHAR(100) NOT NULL UNIQUE,
  `class` VARCHAR(100) NOT NULL,
  `close_ended` TINYINT(1)  NOT NULL DEFAULT 0,
  `filterable` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`))
ENGINE = InnoDB default charset=utf8;


-- -----------------------------------------------------
-- Table `ohrm_question_tag`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_tag` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `tag_name` VARCHAR(50),
  `questionnaire_id` INT,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB default charset=utf8;


-- -----------------------------------------------------
-- Table `ohrm_question`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `type_id` INT NOT NULL ,
  `questionnaire_id` INT NOT NULL,
  `question_name` VARCHAR(600) NOT NULL UNIQUE,
  `question_description` VARCHAR(800) NULL ,
  `options` TEXT NULL ,
  `is_default` TINYINT NULL ,
  `is_deleted` TINYINT NOT NULL DEFAULT '0',
  `form_specific` TINYINT NULL ,
  `group_id` INT DEFAULT NULL,
  `difficulty` INT DEFAULT NULL,
   `default_answer` TEXT NULL,
  `mandatory` TINYINT NULL,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`type_id` )
    REFERENCES `ohrm_answer_type` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`questionnaire_id` )
    REFERENCES `ohrm_questionnaire` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `ohrm_question_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = InnoDB default charset=utf8;


-- -----------------------------------------------------
-- Table `ohrm_question_question_tag`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_question_tag` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `tag_id` INT,
  `question_id` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`tag_id` )
    REFERENCES `ohrm_question_tag` (`id` )
    ON DELETE SET NULL,
    FOREIGN KEY (`question_id` )
    REFERENCES `ohrm_question` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB default charset=utf8;


-- -----------------------------------------------------
-- Table `ohrm_question_form`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_form` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(100) NULL ,
  `header` MEDIUMTEXT NULL ,
  `footer` MEDIUMTEXT NULL ,
  `css_file_name` VARCHAR(100) NULL ,
  PRIMARY KEY (`id`))
ENGINE = InnoDB default charset=utf8;


CREATE TABLE `ohrm_question_form_section_type` (
    `id` INT AUTO_INCREMENT,
    `name` varchar(255) NOT NULL ,
    PRIMARY KEY(`id`)
) ENGINE = INNODB default charset=utf8;

INSERT INTO `ohrm_question_form_section_type`(`id`, `name`) VALUES
(1, 'Question'),
(2, 'Attachment');

-- -----------------------------------------------------
-- Table `ohrm_question_form_section`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_form_section` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `section_name` VARCHAR(255) NOT NULL ,
  `question_form_id` INT NOT NULL ,
  `order_number` INT,
  `weight` double DEFAULT NULL,
  `parent_id` int null,
  `question_group_id` int null,
  `options` varchar(255) null,
  `comment` varchar(1000) null,
  `score` DOUBLE,
  `readonly` TINYINT(1) NOT NULL DEFAULT 0,
  `deletable` TINYINT(1) NOT NULL DEFAULT 1,
  `is_general` TINYINT(1) NOT NULL DEFAULT 0,
  `section_type_id` INT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`question_form_id` )
    REFERENCES `ohrm_question_form` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_question_form_section_ohrm_question_form_section_type` FOREIGN KEY (`section_type_id`) REFERENCES `ohrm_question_form_section_type`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    )
--         FOREIGN KEY (`parent_id`) REFERENCES `ohrm_question_form_section` ON DELETE SET NULL ON UPDATE CASCADE,
--         FOREIGN KEY (`question_group_id`) REFERENCES `ohrm_question_group` ON DELETE SET NULL ON UPDATE CASCADE)
ENGINE = InnoDB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_section_attachment` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `section_id` INT NOT NULL,
    `file_name` varchar(255) NULL,
    `file_type` varchar(255) NULL,
    `file_size` INT NULL,
    `file_content` MEDIUMBLOB NULL,
    `description` varchar(255) NULL,
    `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `added_by_user_id` INT NOT NULL,
    `added_by_employee_number` INT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_section_attachment_question_form_section` FOREIGN KEY (`section_id`) REFERENCES `ohrm_question_form_section`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_section_attachment_ohrm_user` FOREIGN KEY (`added_by_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_section_attachment_hs_hr_employee` FOREIGN KEY (`added_by_employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_question_form_attachment`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_form_attachment` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `question_form_id` INT NOT NULL ,
  `file_name` VARCHAR(255),
  `file_type` VARCHAR(255),
  `description` TEXT,
  `file_size` INT,
  `file_content` MEDIUMBLOB,
  `attachment_type`  INT,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`question_form_id` )
    REFERENCES `ohrm_question_form` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB default charset=utf8;


-- -----------------------------------------------------
-- Table `ohrm_question_form_question`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_form_question` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `question_id` INT NOT NULL ,
  `question_form_section_id` INT NULL ,
  `question_name` VARCHAR(600) NULL,
  `question_description` VARCHAR(600) NULL ,
  `options` TEXT NULL ,
  `parent_id` INT NULL ,
  `parent_answer` VARCHAR(45) NULL ,
  `order_number` INT NULL ,
  `mandatory` TINYINT NULL ,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`question_id` )
    REFERENCES `ohrm_question` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`parent_id` )
    REFERENCES `ohrm_question_form_question` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`question_form_section_id` )
    REFERENCES `ohrm_question_form_section` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_question_translation`
-- -----------------------------------------------------
CREATE TABLE `ohrm_question_translation` (
    `question_id` INT, 
    `language` VARCHAR(5), 
    `translation` VARCHAR(400), 
    PRIMARY KEY(`question_id`, `language`),
        FOREIGN KEY (`question_id` )
        REFERENCES `ohrm_question` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_form_question_translation`
-- -----------------------------------------------------
CREATE TABLE `ohrm_form_question_translation` (
    `question_id` INT, 
    `language` VARCHAR(5), 
    `translation` VARCHAR(400), 
    PRIMARY KEY(`question_id`, `language`),
        FOREIGN KEY (`question_id` )
        REFERENCES `ohrm_question_form_question` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_form_question_option_translation`
-- -----------------------------------------------------
CREATE TABLE `ohrm_form_question_option_translation` (
    `question_id` INT, 
    `language` VARCHAR(5), 
    `translation` VARCHAR(400), 
    PRIMARY KEY(`question_id`, `language`),
        FOREIGN KEY (`question_id` )
        REFERENCES `ohrm_question_form_question` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_form_section_translation`
-- -----------------------------------------------------
CREATE TABLE `ohrm_form_section_translation` (
    `section_id` INT, 
    `language` VARCHAR(5), 
    `translation` VARCHAR(100), 
    PRIMARY KEY(`section_id`, `language`),
        FOREIGN KEY (`section_id` )
        REFERENCES `ohrm_question_form_section` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_filter_rule`
-- -----------------------------------------------------
CREATE TABLE `ohrm_filter_rule` (
    `id` INT AUTO_INCREMENT, 
    `question_form_id` INT NOT NULL,
    `name` TEXT NOT NULL, 
    `where_clause` TEXT,
    `parameters` TEXT,
    PRIMARY KEY(`id`),
        FOREIGN KEY (`question_form_id` )
        REFERENCES `ohrm_question_form` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_answer_criteria`
-- -----------------------------------------------------
CREATE TABLE `ohrm_answer_criteria` (
    `rule_id` INT NOT NULL, 
    `question_form_question_id` INT NOT NULL, 
    `type_id` INT NOT NULL,
    `where_part` TEXT, 
    `answer` TEXT, 
    PRIMARY KEY(`rule_id`, `question_form_question_id`),
        FOREIGN KEY (`rule_id` )
        REFERENCES `ohrm_filter_rule` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        FOREIGN KEY (`question_form_question_id` )
        REFERENCES `ohrm_question_form_question` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        FOREIGN KEY (`type_id` )
        REFERENCES `ohrm_answer_type` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

/* 

-- -----------------------------------------------------
-- Table `ohrm_question_location`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_question_location` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `location_id` INT NOT NULL ,
  `question_id` INT NOT NULL ,
  `is_default` TINYINT NULL ,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`location_id` )
    REFERENCES `ohrm_location` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`question_id` )
    REFERENCES `ohrm_question` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

*/

-- -----------------------------------------------------
-- Table `ohrm_answer_attachment`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_answer_attachment` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `file_name` varchar(200) NULL ,
  `file_type` varchar(200) NULL ,
  `file_size` int NULL ,
  `file_content` MEDIUMBLOB NULL ,
  PRIMARY KEY (`id`))
ENGINE = InnoDB default charset=utf8;

-- -----------------------------------------------------
-- Table `ohrm_answer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ohrm_answer` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `question_form_question_id` INT NOT NULL ,
  `type` VARCHAR(45) NULL ,
  `text_answer` text NULL ,
  `number_answer` double NULL ,
  `date_answer` date NULL,
  `attachment_answer` int NULL,
  PRIMARY KEY (`id`) ,
    FOREIGN KEY (`question_form_question_id` )
    REFERENCES `ohrm_question_form_question` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`attachment_answer` )
    REFERENCES `ohrm_answer_attachment` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB default charset=utf8;

-- ----------------------------------------------------------------
-- Table ohrm_question_form_question_group
-- ----------------------------------------------------------------
CREATE TABLE `ohrm_question_form_question_group` (
    `id` INT AUTO_INCREMENT, 
    `question_form_section_id` INT NOT NULL, 
    `question_group_id` INT NOT NULL, 
    `order_number` INT DEFAULT 0, 
    PRIMARY KEY(`id`),
    FOREIGN KEY (`question_group_id`)
    REFERENCES `ohrm_question_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`question_form_section_id`)
    REFERENCES `ohrm_question_form_section` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = INNODB default charset=utf8;
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- End Table Declaration, Populate Default Values
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
ALTER TABLE `ohrm_question_form_section` ADD CONSTRAINT FOREIGN KEY (`question_group_id`) 
REFERENCES `ohrm_question_group`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `ohrm_question_form_section` ADD CONSTRAINT 
FOREIGN KEY (`parent_id`) REFERENCES `ohrm_question_form_section`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `ohrm_module`(`name`, `status`) VALUES ('questionnaire', 1);

INSERT INTO `ohrm_answer_type`(`id`, `type_name`, `class`, `close_ended`, `filterable`) VALUES
(1, 'Free text small', 'TextBoxType', 0, 1),
(2, 'Free text large', 'TextAreaType', 0, 0),
(3, 'Date', 'DateFieldType', 0, 1),
(4, 'Number', 'NumberType', 0, 1),
(5, 'Email', 'EmailType', 0, 0),
(6, 'Country', 'CountryType', 0, 0),
(7, 'Currency', 'CurrencyAnswerType', 0, 0),   
(8, 'File attachment', 'AttachmentType', 0, 0),     
(9, 'Dropdown', 'DropdownType', 1, 1),
(10, 'Radio buttons', 'RadioType', 1, 1),   
(11, 'Checkboxes', 'CheckBoxType', 1, 1);