CREATE TABLE ohrm_dynamic_filter_rule
(
    `id`            INT AUTO_INCREMENT PRIMARY KEY,
    `name`          VARCHAR(600) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_question_type
(
    `id`              INT AUTO_INCREMENT PRIMARY KEY,
    `name`            VARCHAR(100) NOT NULL,
    `processor_class` VARCHAR(100) NOT NULL
) ENGINE = INNODB
    default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_question
(
    `id`               INT AUTO_INCREMENT PRIMARY KEY,
    `question_type_id` INT NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_dynamic_filter_question_question_type` FOREIGN KEY (`question_type_id`)
        REFERENCES `ohrm_dynamic_filter_question_type` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB
    default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_condition_type
(
    `id`            INT AUTO_INCREMENT PRIMARY KEY,
    `name`          VARCHAR(100) NOT NULL,
    `display_name`  VARCHAR(100) NOT NULL
) ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_answer_type
(
    `id`              INT AUTO_INCREMENT PRIMARY KEY,
    `name`            VARCHAR(100) NOT NULL
) ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_condition
(
    `id`                INT AUTO_INCREMENT PRIMARY KEY,
    `condition_type_id` INT NOT NULL,
    `answer_type_id`    INT NOT NULL,
    CONSTRAINT `fk_dynamic_filter_condition_condition_type` FOREIGN KEY (`condition_type_id`)
        REFERENCES `ohrm_dynamic_filter_condition_type` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_dynamic_filter_condition_answer_type` FOREIGN KEY (`answer_type_id`)
        REFERENCES `ohrm_dynamic_filter_answer_type` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `unique_condition_type_answer_type` UNIQUE (`condition_type_id`, `answer_type_id`)
) ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_rule_question_mapping
(
    `id`           INT AUTO_INCREMENT PRIMARY KEY,
    `rule_id`      INT NOT NULL,
    `question_id`  INT NOT NULL,
    `condition_id` INT DEFAULT NULL,
    `value`        mediumtext DEFAULT NULL,
    CONSTRAINT `fk_dynamic_filter_rule_question_mapping_rule` FOREIGN KEY (`rule_id`)
        REFERENCES `ohrm_dynamic_filter_rule` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_dynamic_filter_rule_question_mapping_question` FOREIGN KEY (`question_id`)
        REFERENCES `ohrm_dynamic_filter_question` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_dynamic_filter_rule_question_mapping_condition` FOREIGN KEY (`condition_id`)
        REFERENCES `ohrm_dynamic_filter_condition` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `unique_question_id` UNIQUE (question_id)
) ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_rule_action
(
    `id`              INT AUTO_INCREMENT PRIMARY KEY,
    `name`          VARCHAR(255) DEFAULT NULL,
    `type`            VARCHAR(255) NOT NULL,
    `default_order`     smallint default 0,
    `processor_class` VARCHAR(100) NOT NULL
) ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_dynamic_filter_rule_action_mapping
(
    `id`        INT AUTO_INCREMENT PRIMARY KEY,
    `rule_id`   INT NOT NULL,
    `action_id` INT NOT NULL,
    `name` VARCHAR(255) DEFAULT NULL,
    `execution_order`     smallint default 0,
    CONSTRAINT `fk_dynamic_filter_rule_action_mapping_rule` FOREIGN KEY (`rule_id`)
        REFERENCES `ohrm_dynamic_filter_rule` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_dynamic_filter_rule_action_mapping_action` FOREIGN KEY (`action_id`)
        REFERENCES `ohrm_dynamic_filter_rule_action` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB
  default charset = utf8;

INSERT INTO ohrm_dynamic_filter_condition_type (`id`,`name`, `display_name`)
VALUES (1,'is','Is'),
       (2,'is_not','Is not'),
       (3,'is_empty','Is empty'),
       (4,'is_not_empty','Is not empty'),
       (5,'has_words','Has the words'),
       (6,'is_in','Is in'),
       (7,'is_not_in','Is not in'),
       (8,'is_before','Is before'),
       (9,'is_after','Is after'),
       (10,'between','Between'),
       (11,'equals','Equals'),
       (12,'not_equals','Not equals'),
       (13,'grater_than','Greater than'),
       (14,'grater_than_or_equal','Greater than or equal to'),
       (15,'less_than','Less than'),
       (16,'less_than_or_equal','Less than or equal to');
