create table `ohrm_interview` (
  `id`   int(13)      not null auto_increment,
  `name` varchar(100) not null,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_interview_guideline` (
  `id`        int(13) not null auto_increment,
  `guideline` mediumtext       DEFAULT '',
  PRIMARY KEY (`id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_vacancy` (
  `id`              int(13)                not null auto_increment,
  `job_title_code`  int(4)                 not null,
  `name`            varchar(100)           not null,
  `description`     MEDIUMTEXT                      default null,
  `no_of_positions` int(13)                         default null,
  `location_id`     int(4)                          default null,
  `sub_unit_id`     int(4)                          default null,
  `status`          int(4)                 not null,
  `defined_time`    datetime               not null,
  `updated_time`    datetime               not null,
  `workflow_id`     varchar(255),
  `guideline_id`    int(4)                          default null,
  `is_template`     int(4),
  `published_time`  datetime               null,
  `request_consent` TINYINT(1) DEFAULT '0' NOT NULL,
  `is_resume_required` TINYINT(1) DEFAULT '1' NOT NULL,
  `include_archived_candidate_on_dynamic_rule_filter` TINYINT(1) DEFAULT '0' NOT NULL,
  primary key (`id`),
  FOREIGN KEY (`workflow_id`) REFERENCES `ohrm_workflow` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  FOREIGN KEY (`guideline_id`) REFERENCES `ohrm_interview_guideline` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_job_vacancy_ohrm_location` FOREIGN KEY (`location_id`)
    REFERENCES `ohrm_location` (`id`)
    ON DELETE SET NULL,
  CONSTRAINT `fk_ohrm_job_vacancy_ohrm_subunit` FOREIGN KEY (`sub_unit_id`)
    REFERENCES `ohrm_subunit` (`id`)
    ON DELETE SET NULL
)
  engine = innodb
  default charset = utf8;

create table `ohrm_vacancy_template_details` (
  `id`          int          not null auto_increment,
  `name`        varchar(255) not null,
  `description` varchar(500) not null,
  `added_by`    int(13)               default null,
  `added_time`  datetime     not null,
  `vacancy_id`  int          not null,
  primary key (`id`),
  FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_vacancy_event_type` (
  `id`           int          not null auto_increment,
  `type`         varchar(255) not null,
  `icon`         varchar(255) not null,
  `workflow`     varchar(255),
  `is_interview` int(4),
  `is_test`      int(4),
  primary key (`id`),
  FOREIGN KEY (`workflow`) REFERENCES `ohrm_workflow` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_vacancy_default_workflow` (
  `id`      int not null auto_increment,
  `name`    varchar(255),
  `type_id` int(4),
  `level`   int(4),
  primary key (`id`),
  FOREIGN KEY (`type_id`) REFERENCES `ohrm_vacancy_event_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_vacancy_event_type_states` (
  `id`       int          not null auto_increment,
  `state_id` int          not null,
  `state`    varchar(255) not null,
  `type_id`  int          not null,
  primary key (`id`),
  UNIQUE (`state_id`),
  FOREIGN KEY (`type_id`) REFERENCES `ohrm_vacancy_event_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_vacancy_event` (
  `id`            int          not null auto_increment,
  `vacancy_id`    int                   default null,
  `event_title`   varchar(255) not null,
  `type_id`       int          not null,
  `threshold`     int,
  `level`         int          not null,
  `is_score_type` int(4)                default 0,
  `max_score`     int                   default 100,
  primary key (`id`),
  CONSTRAINT `fk_vacancy_event_job_vacancy`
  FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_vacancy_event_event_type`
  FOREIGN KEY (`type_id`) REFERENCES `ohrm_vacancy_event_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_interview_template` (
  `id`               int          not null auto_increment,
  `name`             varchar(255) not null,
  `description`      text,
  `creator_id`       int,
  `question_form_id` int          not null,
  `duration`         int,
  `parent_id`        int,
  `is_score_type`    int(4)                default 0,
  `max_score`        int                   default 100,
  primary key (`id`),
  FOREIGN KEY (`question_form_id`) REFERENCES `ohrm_question_form` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`creator_id`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

CREATE TABLE ohrm_test_template (
  `id`               int          not null auto_increment,
  `name`             VARCHAR(255) NOT NULL,
  `description`      text,
  `creator_id`       int,
  `question_form_id` int,
  `parent_id`        int,
  `is_score_type`    int(4)                default 0,
  `max_score`        int                   default 100,
  PRIMARY KEY (id),
  FOREIGN KEY (`question_form_id`) REFERENCES `ohrm_question_form` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`creator_id`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)
  ENGINE = INNODB
  default charset = utf8;

create table `ohrm_test_template_attachment` (
  `id`               int(13)      not null auto_increment,
  `test_template_id` int(13)      not null,
  `file_name`        varchar(200) not null,
  `file_type`        varchar(200)          default null,
  `file_size`        int(11)      not null,
  `file_content`     mediumblob,
  `attachment_type`  int(4)                default null,
  primary key (`id`),
  FOREIGN KEY (`test_template_id`) REFERENCES `ohrm_test_template` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_event_interview` (
  `id`                    int not null auto_increment,
  `event_id`              int not null,
  `interview_template_id` int,
  `test_template_id`      int,
  primary key (`id`),
  FOREIGN KEY (`event_id`) REFERENCES `ohrm_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`interview_template_id`) REFERENCES `ohrm_interview_template` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  FOREIGN KEY (`test_template_id`) REFERENCES `ohrm_test_template` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_vacancy_event_manager` (
  `id`          int not null auto_increment,
  `event_id`    int not null,
  `employee_id` int not null,
  primary key (`id`),
  FOREIGN KEY (`event_id`) REFERENCES `ohrm_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_vacancy_attachment` (
  `id`              int(13)      not null auto_increment,
  `vacancy_id`      int(13)      not null,
  `file_name`       varchar(200) not null,
  `file_type`       varchar(200)          default null,
  `file_size`       int(11)      not null,
  `file_content`    mediumblob,
  `attachment_type` int(4)                default null,
  `comment`         varchar(255)          default null,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_source`
(
    `id`                  INT NOT NULL auto_increment,
    `source_display_name` VARCHAR(255) DEFAULT NULL,
    `is_deletable`        BOOLEAN      DEFAULT 1,
    `is_editable` BOOLEAN DEFAULT 1,
    `created_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

create table `ohrm_candidate_source_url_pattern`
(
    `id`          INT NOT NULL auto_increment,
    `source_id`   INT          DEFAULT NULL,
    `url_pattern` LONGTEXT default NULL,
    `created_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_candidate_source_url_pattern` FOREIGN KEY (`source_id`) REFERENCES `ohrm_candidate_source` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

create table `ohrm_job_candidate` (
  `id`                  int(13)      not null auto_increment,
  `first_name`          varchar(30)  not null,
  `middle_name`         varchar(30)           default null,
  `last_name`           varchar(30)  not null,
  `email`               varchar(255) not null,
  `contact_number`      varchar(30)           default null,
  `status`              int(4)       not null,
  `comment`             text                  default null,
  `mode_of_application` int(4)       not null,
  `date_of_application` date         not null,
  `cv_file_id`          int(13)               default null,
  `cv_text_version`     text                  default null,
  `keywords`            varchar(255)          default null,
  `added_person`        int(13)               default null,
  `is_hired`            int(4)                default 0,
  `added_as_emp_number` int(4)                default null,
  `source_id`           int(13)               default null,
  `source_referrer`     varchar(255)          default null,
  `updated_at`          TIMESTAMP NOT NULL            DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`          DATETIME              DEFAULT NULL,
  `purged_at`           timestamp    null     default null,
  primary key (`id`),
  CONSTRAINT `fk_candidate_added_as_employee` FOREIGN KEY (`added_as_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_candidate_source_candidate` FOREIGN KEY (`source_id`) REFERENCES `ohrm_candidate_source`(`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

alter table `ohrm_user`
    add constraint `fk_ohrm_user_candidate_id` foreign key (`candidate_id`)
        references ohrm_job_candidate(`id`) on delete cascade;

create table `ohrm_candidate_comment` (
  `id`           int not null auto_increment,
  `candidate_id` int not null,
  `comment`      text,
  `added_time`   datetime     default null,
  `updated_time`   datetime     default null,
  `employee_id`  int          default null,
  `added_from_resume` TINYINT(1) default 0,
  primary key (`id`),
  FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_candidate_vacancy` (
  `id`           int(13)      not null auto_increment,
  `candidate_id` int(13)      not null,
  `vacancy_id`   int(13)               default null,
  `status`       varchar(100) not null,
  `applied_date` date         not null,
  `best_match`   TINYINT(1)            default 0,
  `deleted_at`   DATETIME              DEFAULT NULL,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_vacancy_event` (
  `id`                              int          not null auto_increment,
  `final_comment`                   text         not null,
  `final_score`                     varchar(255) not null,
  `final_status`                    varchar(255) not null,
  `scheduled_time`                  datetime              default null,
  `scheduled_time_zone`             varchar(255),
  `event_id`                        int,
  `vacancy_candidate_id`            int,
  `candidate_id`                    int(13)               not null,
  `schedule_comment`                text,
  `schedule_comment_timestamp`      datetime              default null,
  `schedule_comment_updated_time`   datetime              default null,
  `schedule_comment_added_by`       int                   default null,
  `to_be_notify`                    TINYINT(1)            DEFAULT 0,
  `final_comment_added_time`        datetime              default null,
  `final_comment_updated_time`        datetime              default null,
  `final_comment_added_employee_id` int                   default null,
  `updated_at`                      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  primary key (`id`),
  FOREIGN KEY (`event_id`) REFERENCES `ohrm_vacancy_event` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  FOREIGN KEY (`vacancy_candidate_id`) REFERENCES `ohrm_job_candidate_vacancy` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT ohrm_candidate_vacancy_event_candidate_id_fk FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_job_offer_event_detail` (
  `id`                         int not null auto_increment,
  `address_street1` varchar(100) default '',
  `address_street2` varchar(100) default '',
  `address_city` varchar(100) default '',
  `address_province` varchar(100) default '',
  `address_country_code` char(2) default NULL,
  `address_zipcode` varchar(20) default null,
  `joined_date` date default null,
  `job_offer_end_date` date default null,
  `candidate_vacancy_event_id` int not null,
  `pay_grade_id` INT,
  `currency_id` CHAR(3),
  `employment_status_id` INT DEFAULT NULL,
  `supervisor_id` int(4) DEFAULT NULL,
  primary key (`id`),
  CONSTRAINT fk_ohrm_candidate_job_offer_event_detail_event FOREIGN KEY (`candidate_vacancy_event_id`) REFERENCES `ohrm_candidate_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_candidate_job_offer_event_detail_country` FOREIGN KEY (`address_country_code`) REFERENCES `hs_hr_country` (`cou_code`)
      ON DELETE SET NULL
      ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_candidate_job_offer_event_detail_pay_grade` FOREIGN KEY (`pay_grade_id`) REFERENCES `ohrm_pay_grade` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_candidate_job_offer_event_detail_currency`FOREIGN KEY (`currency_id`) REFERENCES `hs_hr_currency_type` (`currency_id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_candidate_job_offer_event_detail_employment_status` FOREIGN KEY (`employment_status_id`) REFERENCES `ohrm_employment_status` (`id`)
      ON DELETE SET NULL
      ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_candidate_job_offer_event_detail_supervisor_employee` FOREIGN KEY (`supervisor_id`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_event_comment`
(
    `id`                         int not null auto_increment,
    `candidate_vacancy_event_id` int not null,
    `comment`                    text,
    `added_time`                 datetime default null,
    `updated_time`               datetime default null,
    `employee_id`                int      default null,
    primary key (`id`),
    FOREIGN KEY (`candidate_vacancy_event_id`) REFERENCES `ohrm_candidate_vacancy_event` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) engine = innodb
  default charset = utf8;

create table `ohrm_candidate_event_interviewer` (
  `id`                         int not null auto_increment,
  `candidate_vacancy_event_id` int not null,
  `interviewer_id`             int not null,
  primary key (`id`),
  FOREIGN KEY (`interviewer_id`) REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`candidate_vacancy_event_id`) REFERENCES `ohrm_candidate_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_event_interview` (
  `id`                         int not null auto_increment,
  `candidate_vacancy_event_id` int not null,
  `interview_template_id`      int,
  `question_form_id`           int not null,
  `comment`                    text,
  `interview_state`            varchar(20)  DEFAULT 'NOT_STARTED',
  primary key (`id`),
  FOREIGN KEY (`candidate_vacancy_event_id`) REFERENCES `ohrm_candidate_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`interview_template_id`) REFERENCES `ohrm_interview_template` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  FOREIGN KEY (`question_form_id`) REFERENCES `ohrm_question_form` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_event_test` (
  `id`                         int not null auto_increment,
  `candidate_vacancy_event_id` int not null,
  `test_template_id`           int,
  `question_form_id`           int not null,
  primary key (`id`),
  FOREIGN KEY (`candidate_vacancy_event_id`) REFERENCES `ohrm_candidate_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`test_template_id`) REFERENCES `ohrm_test_template` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  FOREIGN KEY (`question_form_id`) REFERENCES `ohrm_question_form` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;


create table `ohrm_candidate_event_state` (
  `id`                         int not null auto_increment,
  `candidate_vacancy_event_id` int not null,
  `state_id`                   int not null,
  `active`                     TINYINT(1)   default 0,
  primary key (`id`),
  FOREIGN KEY (`state_id`) REFERENCES `ohrm_vacancy_event_type_states` (`state_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`candidate_vacancy_event_id`) REFERENCES `ohrm_candidate_vacancy_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  engine = innodb
  default charset = utf8;


create table `ohrm_job_candidate_history` (
  `id`                     int(13)  not null auto_increment,
  `candidate_id`           int(13)  not null,
  `vacancy_id`             int(13)           default null,
  `candidate_vacancy_name` varchar(255)      default null,
  `interview_id`           int(13)           default null,
  `action`                 int(4)   not null,
  `performed_by`           int(13)           default null,
  `performed_date`         datetime not null,
  `note`                   text              default null,
  `interviewers`           varchar(255)      default null,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_candidate_attachment` (
  `id`              int(13)      not null auto_increment,
  `candidate_id`    int(13)      not null,
  `file_name`       varchar(200) not null,
  `file_type`       varchar(200)          default null,
  `file_size`       int(11)      not null,
  `file_content`    mediumblob,
  `attachment_type` int(4)                default null,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_interview` (
  `id`                   int(13)      not null auto_increment,
  `candidate_vacancy_id` int(13)               default null,
  `candidate_id`         int(13)               default null,
  `interview_name`       int(13)               default null,
  `interview_date`       date                  default null,
  `interview_start_time` time                  default null,
  `interview_end_time`   time                  default null,
  `interview_status`     varchar(100) not null DEFAULT 'INTERVIEW SCHEDULED',
  `note`                 text                  default null,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_interview_interviewer` (
  `interview_id`   int(13) not null,
  `interviewer_id` int(13) not null,
  primary key (`interview_id`, `interviewer_id`)
)
  engine = innodb
  default charset = utf8;

create table `ohrm_job_interview_attachment` (
  `id`              int(13)      not null auto_increment,
  `interview_id`    int(13)      not null,
  `file_name`       varchar(200) not null,
  `file_type`       varchar(200)          default null,
  `file_size`       int(11)      not null,
  `file_content`    mediumblob,
  `attachment_type` int(4)                default null,
  `comment`         varchar(255)          default null,
  primary key (`id`)
)
  engine = innodb
  default charset = utf8;

CREATE TABLE ohrm_job_vacancy_status (
  `id`     INT AUTO_INCREMENT,
  `status` SMALLINT,
  `name`   VARCHAR(100),
  `order`  INT,
  PRIMARY KEY (id)
)
  ENGINE = INNODB
  default charset = utf8;

CREATE TABLE ohrm_job_vacancy_workflow_action (
  `name`        VARCHAR(100),
  `action_text` VARCHAR(100),
  UNIQUE (`name`)
)
  ENGINE = INNODB
  default charset = utf8;

create table `ohrm_candidate_event_change_log` (
  `id`                int(13) not null auto_increment,
  `candidate_id`      int(13) not null,
  `previous_event_id` int(13) not null,
  `next_event_id`     int(13) not null,
  `updated_at`        TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  primary key (`id`),
  foreign key (candidate_id) references ohrm_job_candidate (id)
    on delete cascade,
  foreign key (previous_event_id) references ohrm_vacancy_event (id)
    on delete cascade,
  foreign key (next_event_id) references ohrm_vacancy_event (id)
    on delete cascade
)
  engine = innodb
  default charset = utf8;

create table `ohrm_candidate_generated_document`
(
    `id`       int(13) not null auto_increment,
    `generated_document_id` int(13) not null,
    `candidate_id` int(13) not null,
    primary key (`id`),
    CONSTRAINT `fk_candidate_generated_document_generated_document` FOREIGN KEY (`generated_document_id`) REFERENCES `ohrm_generated_document` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_candidate_generated_document_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate` (`id`)
        ON DELETE CASCADE
) engine = innodb
  default charset = utf8;

create table `ohrm_candidate_notification_history`
(
    `id`       int(13) not null auto_increment,
    `notification_history_id` int(13) not null,
    `candidate_id` int(13) not null,
    primary key (`id`),
    CONSTRAINT `fk_candidate_notification_notification_history` FOREIGN KEY (`notification_history_id`) REFERENCES `ohrm_notification_history` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_candidate_notification_history_job_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate` (`id`)
        ON DELETE CASCADE
) engine = innodb
  default charset = utf8;

create table `ohrm_notification_recipient_history`
(
    `id`       int(13) not null auto_increment,
    `type`          varchar(10) not null,
    `recipient_emp_number`       int default null,
    `recipient_name` varchar(200) default null,
    `recipient_email` varchar(50) not null,
    `notification_history_id` int(13)  not null,
    primary key (`id`),
    CONSTRAINT `fk_notification_recipient_history_notification_history` FOREIGN KEY (`notification_history_id`) REFERENCES `ohrm_notification_history` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_recruitment_notification_recipient_history_employee` FOREIGN KEY (`recipient_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`)
        ON DELETE CASCADE ON UPDATE CASCADE
) engine = innodb
  default charset = utf8;

create table `ohrm_notification_candidate_recipient_history`
(
    `id`       int(13) not null auto_increment,
    `type`          varchar(10) not null,
    `recipient_candidate_id`       int default null,
    `recipient_name` varchar(200) default null,
    `recipient_email` varchar(50) not null,
    `notification_history_id` int(13)  not null,
    primary key (`id`),
    CONSTRAINT `fk_notification_candidate_recipient_history` FOREIGN KEY (`notification_history_id`) REFERENCES `ohrm_notification_history` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_recruitment_notification_recipient_history_candidate` FOREIGN KEY (`recipient_candidate_id`) REFERENCES `ohrm_job_candidate` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) engine = innodb
  default charset = utf8;

alter table ohrm_job_interview
  add constraint foreign key (candidate_vacancy_id)
references ohrm_job_candidate_vacancy (id)
  on delete set null;

alter table ohrm_job_interview
  add constraint foreign key (candidate_id)
references ohrm_job_candidate (id)
  on delete cascade;

alter table ohrm_job_interview_interviewer
  add constraint foreign key (interview_id)
references ohrm_job_interview (id)
  on delete cascade;

alter table ohrm_job_interview_interviewer
  add constraint foreign key (interviewer_id)
references hs_hr_employee (emp_number)
  on delete cascade;

alter table ohrm_job_candidate_attachment
  add constraint foreign key (candidate_id)
references ohrm_job_candidate (id)
  on delete cascade;

alter table ohrm_job_vacancy_attachment
  add constraint foreign key (vacancy_id)
references ohrm_job_vacancy (id)
  on delete cascade;

alter table ohrm_job_interview_attachment
  add constraint foreign key (interview_id)
references ohrm_job_interview (id)
  on delete cascade;

alter table ohrm_job_candidate_history
  add constraint foreign key (candidate_id)
references ohrm_job_candidate (id)
  on delete cascade;

alter table ohrm_job_candidate_history
  add constraint foreign key (vacancy_id)
references ohrm_job_vacancy (id)
  on delete set null;

alter table ohrm_job_candidate_history
  add constraint foreign key (interview_id)
references ohrm_job_interview (id)
  on delete set null;

alter table ohrm_job_candidate_history
  add constraint foreign key (performed_by)
references hs_hr_employee (emp_number)
  on delete set null;

alter table ohrm_job_vacancy
  add constraint foreign key (job_title_code)
references ohrm_job_title (id)
  on delete cascade;

alter table ohrm_job_candidate
  add constraint foreign key (added_person)
references hs_hr_employee (emp_number)
  on delete set null;

alter table ohrm_job_candidate_vacancy
  add constraint `fk_job_candidate_vacancy_job_candidate` foreign key (candidate_id)
references ohrm_job_candidate (id)
  on delete cascade;

alter table ohrm_job_candidate_vacancy
  add constraint `fk_job_candidate_vacancy_job_vacancy` foreign key (vacancy_id)
references ohrm_job_vacancy (id)
  on delete set null;

INSERT INTO `hs_hr_unique_id` (last_id, table_name, field_name) VALUES
  (0, 'ohrm_job_vacancy', 'id'),
  (0, 'ohrm_job_vacancy_attachment', 'id'),
  (0, 'ohrm_job_candidate_vacancy', 'id'),
  (0, 'ohrm_job_candidate', 'id'),
  (0, 'ohrm_job_candidate_attachment', 'id'),
  (0, 'ohrm_job_candidate_history', 'id'),
  (0, 'ohrm_job_interview', 'id');

SET @max_module_id := (SELECT MAX(`id`)
                       FROM `ohrm_module`);
SET @recruitment_module_id := @max_module_id + 1;

INSERT INTO `ohrm_module` (`id`, `name`, `status`, `data_group_prefix`) VALUES
  (@recruitment_module_id, 'recruitment', 1, 'Recruitment'),
  (@max_module_id + 2, 'recruitmentApply', 1, null);

SET @max_screen_id := (SELECT MAX(`id`)
                       FROM `ohrm_screen`);

SET @candidate_list_screen_id := @max_screen_id + 1;
SET @vacancy_list_screen_id := @max_screen_id + 2;
SET @recruitment_module_screen_id := @max_screen_id + 3;
SET @add_job_vacancy_screen_id := @max_screen_id + 4;
SET @delete_job_vacancy_screen_id := @max_screen_id + 5;
SET @add_candidate_screen_id := @max_screen_id + 6;
SET @delete_candidate_screen_id := @max_screen_id + 7;
SET @manage_job_vacancy_screen_id := @max_screen_id + 8;
SET @job_posting_screen_id := @max_screen_id + 9;
SET @vacancy_application_form_screen_id := @max_screen_id + 10;
SET @recruitment_vacancy_templates_screen_id := @max_screen_id + 11;
SET @recruitment_question_pool_screen_id := @max_screen_id + 12;
SET @recruitment_interview_templates_screen_id := @max_screen_id + 13;
SET @recruitment_standard_tests_screen_id := @max_screen_id + 14;
SET @recruitment_document_template_screen_id := @max_screen_id + 15;
SET @recruitment_email_template_screen_id := @max_screen_id + 16;
SET @recruitment_candidate_sources_screen_id := @max_screen_id + 17;

INSERT INTO `ohrm_screen` (`id`, `name`, `module_id`, `action_url`, `route_name`) VALUES
  (@candidate_list_screen_id, 'Candidate List', @recruitment_module_id, 'viewCandidates', 'recruitment_viewCandidates'),
  (@vacancy_list_screen_id, 'Vacancy List', @recruitment_module_id, 'viewJobVacancy', 'recruitment_viewJobVacancy'),
  (@recruitment_module_screen_id, 'View Recruitment Module', @recruitment_module_id, 'viewRecruitmentModule', 'recruitment_viewRecruitmentModule'),
  (@add_job_vacancy_screen_id, 'Add Job Vacancy', @recruitment_module_id, 'addJobVacancy', NULL),
  (@delete_job_vacancy_screen_id, 'Delete Job Vacancy', @recruitment_module_id, 'deleteJobVacancy', NULL),
  (@add_candidate_screen_id, 'Add Candidate', @recruitment_module_id, 'addCandidate', NULL),
  (@delete_candidate_screen_id, 'Delete Candidate', @recruitment_module_id, 'deleteCandidateVacancies', NULL),
  (@manage_job_vacancy_screen_id, 'Manage Vacancy Workflow', @recruitment_module_id, 'manageVacancyWorkflow', NULL),
  (@job_posting_screen_id, 'Job Posting', @recruitment_module_id, 'jobPosting', NULL),
  (@vacancy_application_form_screen_id, 'Vacancy Application Form', @recruitment_module_id, 'vacancyApplicationForm', NULL),
  (@recruitment_vacancy_templates_screen_id, 'Vacancy Templates', @recruitment_module_id, 'vacancyTemplates', 'recruitment_vacancy_templates'),
  (@recruitment_question_pool_screen_id, 'Question Pool', @recruitment_module_id, 'questionPool', 'recruitment_question_pool'),
  (@recruitment_interview_templates_screen_id, 'Interview Templates', @recruitment_module_id, 'interviewTemplates', 'recruitment_interview_templates'),
  (@recruitment_standard_tests_screen_id, 'Standard Tests', @recruitment_module_id, 'testTemplates', 'recruitment_test_templates'),
  (@recruitment_document_template_screen_id, 'Recruitment Document Templates', @recruitment_module_id, 'recruitmentDocumentTemplates', NULL),
  (@recruitment_email_template_screen_id, 'Recruitment Email Templates', @recruitment_module_id, 'recruitmentEmailTemplates', NULL),
  (@recruitment_candidate_sources_screen_id, 'Candidate Sources', @recruitment_module_id, 'candidateSources', NULL);


INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES
  ('Interviewer', 'Interviewer', 0, 1),
  ('HiringManager', 'Hiring Manager', 0, 1),
  ('EventManager', 'Event Manager', 0, 1),
  ('CandidateUser', 'Candidate', 0, 1);

SET @interviewer_id := (SELECT `id`
                        FROM `ohrm_user_role`
                        WHERE `name` = 'Interviewer');
SET @hiring_manager_id := (SELECT `id`
                           FROM `ohrm_user_role`
                           WHERE `name` = 'HiringManager');
SET @event_manager_id := (SELECT `id`
                          FROM `ohrm_user_role`
                          WHERE `name` = 'EventManager');

SET @config_admin_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'ConfigAdmin');

SET @talent_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Talent Management' AND `level` = 1);
SET @recruitment_ats_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Recruitment (ATS)' AND `level` = 2);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`, `uisref`)
VALUES
  ('Candidates', @candidate_list_screen_id, @recruitment_ats_module_menu_id, 3, 100, NULL, 1, 'recruitment.candidates'),
  ('Vacancies', @vacancy_list_screen_id, @recruitment_ats_module_menu_id, 3, 200, NULL, 1, 'recruitment.vacancies');
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`)
VALUES
    ('Configuration', NULL, @recruitment_ats_module_menu_id, 3, 350, NULL, 1);

SET @recruitment_configuration_menu_item_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`,`status`)
VALUES
    ('Vacancy Templates', @recruitment_vacancy_templates_screen_id, @recruitment_configuration_menu_item_id, 4, 100, NULL, 'recruitment.vacancytemplates.view', 1),
    ('Question Pool', @recruitment_question_pool_screen_id, @recruitment_configuration_menu_item_id, 4, 200, NULL, NULL, 1),
    ('Interview Templates', @recruitment_interview_templates_screen_id, @recruitment_configuration_menu_item_id, 4, 300, NULL, NULL, 1),
    ('Standard Tests', @recruitment_standard_tests_screen_id, @recruitment_configuration_menu_item_id, 4, 400, NULL, 'recruitment.testtemplate', 1),
    ('Document Templates', @recruitment_document_template_screen_id, @recruitment_configuration_menu_item_id, 4, 500, NULL, 'ng_recruitment.documentTemplates', 1),
    ('Email Templates', @recruitment_email_template_screen_id, @recruitment_configuration_menu_item_id, 4, 600, NULL, 'ng_recruitment.email_templates', 1),
    ('Candidate Sources', @recruitment_candidate_sources_screen_id, @recruitment_configuration_menu_item_id, 4, 700, NULL, 'recruitment.candidateSources', 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('recruitment_vacancies', 'Recruitment (ATS) - Vacancies', 1, 1, 1, 1),
  ('recruitment_configuration', 'Recruitment (ATS) - Configuration', 1, 1, 1, 1),
  ('recruitment_candidates', 'Recruitment (ATS) - Candidates', 1, 1, 1, 1),
  ('recruitment_candidate_sources', 'Recruitment (ATS) - Candidate Sources', 1, 1, 1, 1),
  ('recruitment_candidates_profile_create_documents', 'Recruitment (ATS) - Candidates Profile - Create Documents', 1, 1, 0, 0),
  ('recruitment_candidates_profile_email_history', 'Recruitment (ATS) - Candidates Profile - Email History', 1, 0, 0, 0);

SET @vacancy_data_group_id := (SELECT `id`
                               FROM `ohrm_data_group`
                               WHERE `name` = 'recruitment_vacancies');
SET @candidate_data_group_id := (SELECT `id`
                                 FROM `ohrm_data_group`
                                 WHERE `name` = 'recruitment_candidates');
SET @configuration_data_group_id := (SELECT `id`
                                     FROM `ohrm_data_group`
                                     WHERE `name` = 'recruitment_configuration');
SET @candidate_sources_data_group_id := (SELECT `id`
                                     FROM `ohrm_data_group`
                                     WHERE `name` = 'recruitment_candidate_sources');
SET @candidates_profile_create_documents_id := (SELECT `id`
                                 FROM `ohrm_data_group`
                                 WHERE `name` = 'recruitment_candidates_profile_create_documents');
SET @candidates_profile_email_history_id := (SELECT `id`
                                 FROM `ohrm_data_group`
                                 WHERE `name` = 'recruitment_candidates_profile_email_history');

SET @admin_user_role_type_id := (SELECT `id`
                                     FROM `ohrm_user_role_type`
                                     WHERE `name` = 'Admin');
SET @config_admin_user_role_type_id := (SELECT `id`
                                     FROM `ohrm_user_role_type`
                                     WHERE `name` = 'ConfigAdmin');

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id)
VALUES (@admin_user_role_type_id, @candidate_sources_data_group_id),
       (@config_admin_user_role_type_id, @candidate_sources_data_group_id);

INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
  (@recruitment_module_id, 1, 'recruitment/viewJobVacancy', NULL, 20),
  (@recruitment_module_id, @event_manager_id, 'recruitment/viewJobVacancy', NULL, 10),
  (@recruitment_module_id, @hiring_manager_id, 'recruitment/viewJobVacancy', NULL, 5);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
  (1, @candidate_list_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @candidate_list_screen_id, 1, 1, 1, 1),
  (@event_manager_id, @candidate_list_screen_id, 1, 0, 0, 0),
  (1, @vacancy_list_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @vacancy_list_screen_id, 1, 1, 1, 1),
  (@event_manager_id, @vacancy_list_screen_id, 1, 1, 1, 1),
  (1, @recruitment_module_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @recruitment_module_screen_id, 1, 1, 1, 1),
  (@event_manager_id, @recruitment_module_screen_id, 1, 1, 1, 1),
  (1, @add_job_vacancy_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @add_job_vacancy_screen_id, 1, 1, 1, 1),
  (1, @delete_job_vacancy_screen_id, 1, 1, 1, 1),
  (1, @add_candidate_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @add_candidate_screen_id, 1, 1, 1, 1),
  (1, @delete_candidate_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @delete_candidate_screen_id, 1, 1, 1, 1),
  (1, @manage_job_vacancy_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @manage_job_vacancy_screen_id, 1, 1, 1, 1),
  (1, @job_posting_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @job_posting_screen_id, 1, 1, 1, 1),
  (1, @vacancy_application_form_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @vacancy_application_form_screen_id, 1, 1, 1, 1),
  (1, @recruitment_vacancy_templates_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @recruitment_vacancy_templates_screen_id, 1, 1, 1, 1),
  (1, @recruitment_question_pool_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @recruitment_question_pool_screen_id, 1, 1, 1, 1),
  (1, @recruitment_interview_templates_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @recruitment_interview_templates_screen_id, 1, 1, 1, 1),
  (1, @recruitment_standard_tests_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @recruitment_standard_tests_screen_id, 1, 1, 1, 1),
  (1, @recruitment_document_template_screen_id, 1, 1, 1, 1),
  (@hiring_manager_id, @recruitment_document_template_screen_id, 1, 1, 1, 1),
  (1, @recruitment_email_template_screen_id, 1, 1, 1, 1),
  (@config_admin_role_id, @recruitment_candidate_sources_screen_id, 1, 1, 1, 1),
  (1, @recruitment_candidate_sources_screen_id, 1, 1, 1, 1);

SET @admin_user_role_id := (SELECT `id`
                            FROM `ohrm_user_role`
                            WHERE `name` = 'Admin');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
VALUES
  (1, @vacancy_data_group_id, 1, 1, 1, 1, 0),
  (2, @vacancy_data_group_id, 0, 0, 0, 0, 0),
  (3, @vacancy_data_group_id, 0, 0, 0, 0, 0),
  (@hiring_manager_id, @vacancy_data_group_id, 1, 0, 1, 0, 0),
  (@event_manager_id, @vacancy_data_group_id, 1, 0, 0, 0, 0),
  (1, @candidate_data_group_id, 1, 1, 1, 1, 0),
  (@hiring_manager_id, @candidate_data_group_id, 1, 1, 1, 1, 0),
  (@event_manager_id, @candidate_data_group_id, 1, 0, 0, 0, 0),
  (1, @candidates_profile_create_documents_id, 1, 1, 0, 0, 0),
  (@hiring_manager_id, @candidates_profile_create_documents_id, 1, 1, 0, 0, 0),
  (@event_manager_id, @candidates_profile_create_documents_id, 1, 1, 0, 0, 0),
  (1, @candidates_profile_email_history_id, 1, 0, 0, 0, 0),
  (@hiring_manager_id, @candidates_profile_email_history_id, 1, 0, 0, 0, 0),
  (@event_manager_id, @candidates_profile_email_history_id, 1, 0, 0, 0, 0),
  (1, @configuration_data_group_id, 1, 1, 1, 1, 0),
  (@hiring_manager_id, @configuration_data_group_id, 1, 1, 1, 1, 0),
  (@config_admin_role_id, @candidate_sources_data_group_id, 1, 1, 1, 1, 0),
  (@admin_user_role_id, @candidate_sources_data_group_id, 1, 1, 1, 1, 0);

-- setting global vacancy permissions for user defined user roles.
-- value must be decimal representation of binary number
-- ex: 8 == 1000 => canRead = 1, canUpdate = 0, canCreate = 0, canDelete = 0
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('recruitment.global_vacancy_permissions', 8);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
  (@vacancy_data_group_id, @vacancy_list_screen_id, 1),
  (@vacancy_data_group_id, @add_job_vacancy_screen_id, 1),
  (@vacancy_data_group_id, @add_job_vacancy_screen_id, 2),
  (@vacancy_data_group_id, @add_job_vacancy_screen_id, 3),
  (@configuration_data_group_id, @add_job_vacancy_screen_id, 1),
  (@configuration_data_group_id, @add_job_vacancy_screen_id, 2),
  (@configuration_data_group_id, @add_job_vacancy_screen_id, 3),
  (@vacancy_data_group_id, @delete_job_vacancy_screen_id, 4),
  (@vacancy_data_group_id, @manage_job_vacancy_screen_id, 3),
  (@vacancy_data_group_id, @job_posting_screen_id, 3),
  (@vacancy_data_group_id, @vacancy_application_form_screen_id, 3),
  (@configuration_data_group_id, @manage_job_vacancy_screen_id, 3),
  (@configuration_data_group_id, @job_posting_screen_id, 3),
  (@configuration_data_group_id, @vacancy_application_form_screen_id, 3),
  (@candidate_data_group_id, @candidate_list_screen_id, 1),
  (@candidate_data_group_id, @add_candidate_screen_id, 1),
  (@candidate_data_group_id, @add_candidate_screen_id, 2),
  (@candidate_data_group_id, @add_candidate_screen_id, 3),
  (@candidate_data_group_id, @delete_candidate_screen_id, 4),
  (@vacancy_data_group_id, @recruitment_module_screen_id, 1),
  (@candidate_data_group_id, @recruitment_module_screen_id, 1),
  (@configuration_data_group_id, @recruitment_module_screen_id, 1),
  (@configuration_data_group_id, @recruitment_vacancy_templates_screen_id, 1),
  (@configuration_data_group_id, @recruitment_question_pool_screen_id, 1),
  (@configuration_data_group_id, @recruitment_interview_templates_screen_id, 1),
  (@configuration_data_group_id, @recruitment_standard_tests_screen_id, 1),
  (@configuration_data_group_id, @recruitment_document_template_screen_id, 1),
  (@configuration_data_group_id, @recruitment_email_template_screen_id, 1),
  (@candidate_sources_data_group_id, @recruitment_candidate_sources_screen_id, 1);

CREATE TABLE IF NOT EXISTS `ohrm_hiring_managers` (
  `vacancy_id`  int(11) NOT NULL DEFAULT '0',
  `employee_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`vacancy_id`, `employee_id`),
  KEY `ohrm_hiring_managers_ibfk_1` (`employee_id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;


ALTER TABLE `ohrm_hiring_managers`
  ADD CONSTRAINT `ohrm_hiring_managers_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`)
  ON DELETE CASCADE,
  ADD CONSTRAINT `ohrm_hiring_managers_ibfk_2` FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy` (`id`)
  ON DELETE CASCADE;

-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- Questionnaire related
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ohrm_vacancy_application_form` (
  `question_form_id` int(11) NOT NULL,
  `vacancy_id`       int(11) NOT NULL,
  `active`           tinyint NULL DEFAULT '0',
  PRIMARY KEY (`question_form_id`),
  FOREIGN KEY (`vacancy_id`)
  REFERENCES `ohrm_job_vacancy` (`id`)
    ON DELETE CASCADE,
  FOREIGN KEY (`question_form_id`)
  REFERENCES `ohrm_question_form` (`id`)
    ON DELETE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_candidate_answer` (
  `candidate_id` int(11) NOT NULL,
  `answer_id`    int(11) NOT NULL,
  PRIMARY KEY (`candidate_id`, answer_id),
  FOREIGN KEY (`candidate_id`)
  REFERENCES `ohrm_job_candidate` (`id`)
    ON DELETE CASCADE,
  FOREIGN KEY (`answer_id`)
  REFERENCES `ohrm_answer` (`id`)
    ON DELETE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_candidate_event_interviewer_answer` (
  `id`                             int NOT NULL AUTO_INCREMENT,
  `candidate_event_interviewer_id` int NOT NULL,
  `answer_id`                      int NOT NULL,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`candidate_event_interviewer_id`) REFERENCES `ohrm_candidate_event_interviewer` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`answer_id`) REFERENCES `ohrm_answer` (`id`)
    ON DELETE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE ohrm_job_candidate_social_media (
  `id`           INT AUTO_INCREMENT,
  `candidate_id` INT NOT NULL,
  `type_id`      INT NOT NULL,
  `profile_name` VARCHAR(100),
  `profile_link` TEXT,
  PRIMARY KEY (id),
  FOREIGN KEY (`type_id`) REFERENCES `ohrm_social_media_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;


CREATE TABLE ohrm_question_form_section_auto_generate_questions (
  `id`            INT AUTO_INCREMENT,
  `difficulty`    INT NOT NULL,
  `noOfQuestions` INT NOT NULL,
  `sectionId`     INT,
  PRIMARY KEY (id),
  FOREIGN KEY (`sectionId`) REFERENCES `ohrm_question_form_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

CREATE TABLE ohrm_vacancy_job_board (
  `id`               INT        AUTO_INCREMENT,
  `vacancy_id`       INT NOT NULL,
  `equest_user_id`   VARCHAR(100),
  `board_id`         INT NOT NULL,
  `board_name`       VARCHAR(100),
  `board_start_date` DATE,
  `created_date`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id`          INT        DEFAULT NULL,
  `is_expired`       TINYINT(1) DEFAULT 0,
  `is_deleted`       TINYINT(1) DEFAULT 0,
  PRIMARY KEY (id),
  FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`)
    ON DELETE SET NULL
)
  ENGINE = INNODB
  default charset = utf8;

CREATE  TABLE IF NOT EXISTS `ohrm_past_candidate_vacancy_question_section` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `candidate_vacancy_id` INT NOT NULL ,
    `section_name` VARCHAR(255) NOT NULL ,
    `order_number` INT,
    `is_general` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`) ,
    CONSTRAINT `fk_past_section_candidate_vacancy`
    FOREIGN KEY (`candidate_vacancy_id` )
    REFERENCES `ohrm_job_candidate_vacancy` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE
    )
    ENGINE = InnoDB default charset=utf8;

CREATE  TABLE IF NOT EXISTS `ohrm_past_candidate_vacancy_question` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `section_id` INT NOT NULL ,
    `type_id` INT NOT NULL ,
    `question_name` VARCHAR(600) NULL,
    `question_description` VARCHAR(600) NULL ,
    `options` TEXT NULL ,
    `mandatory` TINYINT NULL ,
    `order_number` INT NULL ,
    PRIMARY KEY (`id`) ,
    CONSTRAINT `fk_past_question_candidate_vacancy`
    FOREIGN KEY (`section_id` )
    REFERENCES `ohrm_past_candidate_vacancy_question_section` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_past_question_answer_type`
    FOREIGN KEY (`type_id` )
    REFERENCES `ohrm_answer_type` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
    ENGINE = InnoDB default charset=utf8;

CREATE  TABLE IF NOT EXISTS `ohrm_past_candidate_vacancy_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;

CREATE  TABLE IF NOT EXISTS `ohrm_past_candidate_vacancy_answer` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `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`) ,
    CONSTRAINT `fk_past_answer_past_question`
    FOREIGN KEY (`question_id` )
    REFERENCES `ohrm_past_candidate_vacancy_question` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_past_answer_past_attachment`
    FOREIGN KEY (`attachment_answer` )
    REFERENCES `ohrm_past_candidate_vacancy_answer_attachment` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
    ENGINE = InnoDB default charset=utf8;

-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- Adding vacancy questionnaire related default values
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

INSERT INTO `ohrm_questionnaire` (`id`, `questionnaire_name`, `is_grouping_allowed`)
VALUES (DEFAULT, 'vacancy', 1),
  (DEFAULT, 'interview', 1);

SET @questinnaire_id := (SELECT `id`
                         FROM `ohrm_questionnaire`
                         WHERE `questionnaire_name` = 'vacancy');
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- Adding Menu Items, Screens, Permissions, for Question realted things
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

SET @admin_role_id := (select `id`
                       from ohrm_user_role
                       where `name` = 'Admin');

SET @recruitment_module_id := (SELECT `id`
                               FROM `ohrm_module`
                               WHERE `name` = 'recruitment');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
  ('Question List', @recruitment_module_id, 'addQuestionsToPool', 'recruitment_addQuestionsToPool');

SET @question_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
  (@admin_role_id, @question_list_screen_id, 1, 1, 1, 1);


SET @view_admin_module_screen_id := (SELECT `id`
                                     FROM `ohrm_screen`
                                     WHERE `name` = 'View Admin Module' AND `module_id` = 2);

-- audit trail related stuff
INSERT INTO `ohrm_audittrail_module` (`name`) VALUES
  ('Recruitment (ATS)');
SET @recruitment_module_id := (SELECT `id`
                               FROM `ohrm_audittrail_module`
                               WHERE `name` = 'Recruitment (ATS)');
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
  ('JobVacancy', 'Job Vacancy', 'Job Vacancy', @recruitment_module_id);

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
  ('JobVacancyTemplate', 'Job Vacancy Template', 'Job Vacancy Template', @recruitment_module_id);

INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`, `is_editable`, `deletable`) VALUES
    ('HIRING MANAGER', 'Hiring Manager', 0, 1, 0, 0);

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id) VALUES
  (1, @vacancy_data_group_id),
  (1, @candidate_data_group_id),
  (1, @candidates_profile_create_documents_id),
  (1, @candidates_profile_email_history_id),
  (1, @configuration_data_group_id);

SET @recruitment_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='recruitment');
INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`)
VALUES
  ('Default Vacancy Workflow', 'Default Vacancy Workflow', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Application Received', 'Application Received', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Phone Interview', 'Phone Interview', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Panel Interview', 'Panel Interview', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('One-On-One Interview', 'One-On-One Interview', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Test', 'Test', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Hire', 'Hire', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Rejected', 'Rejected', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4),
  ('Shortlisted', 'Shortlisted', NULL, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `roles_to_notify`, `screens_to_show`, `priority`)
VALUES ('Default Vacancy Workflow', 'Application Received', 'ADMIN', 'Proceed', 'Interview', '', 0, 0),
  ('Default Vacancy Workflow', 'Interview', 'ADMIN', 'Schedule', 'Interview - Scheduled', '', 1, 0),
  ('Default Vacancy Workflow', 'Interview - Scheduled', 'ADMIN', 'Proceed', 'Hired', '', 1, 0),
  ('Panel Interview', 'Initiated', 'ADMIN', 'Schedule', 'Scheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Panel Interview', 'Initiated', 'ADMIN', 'Complete', 'Completed', '', 0, 0),
  ('Panel Interview', 'Scheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Panel Interview', 'ReScheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Panel Interview', 'Scheduled', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('Panel Interview', 'ReScheduled', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('Panel Interview', 'Initiated', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Panel Interview', 'Scheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Panel Interview', 'ReScheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Panel Interview', 'Completed', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Phone Interview', 'Initiated', 'ADMIN', 'Schedule', 'Scheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Phone Interview', 'Initiated', 'ADMIN', 'Complete', 'Completed', '', 0, 0),
  ('Phone Interview', 'Scheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Phone Interview', 'Scheduled', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('Phone Interview', 'ReScheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Phone Interview', 'ReScheduled', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('Phone Interview', 'Initiated', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Phone Interview', 'Scheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Phone Interview', 'ReScheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Phone Interview', 'Completed', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('One-On-One Interview', 'Initiated', 'ADMIN', 'Schedule', 'Scheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('One-On-One Interview', 'Initiated', 'ADMIN', 'Complete', 'Completed', '', 0, 0),
  ('One-On-One Interview', 'Scheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('One-On-One Interview', 'Scheduled', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('One-On-One Interview', 'ReScheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('One-On-One Interview', 'ReScheduled', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('One-On-One Interview', 'Initiated', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('One-On-One Interview', 'Scheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('One-On-One Interview', 'ReScheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('One-On-One Interview', 'Completed', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Test', 'Initiated', 'ADMIN', 'Schedule', 'Scheduled', 'Candidate,EventManager,HiringManager,Subscriber', 1, 0),
  ('Test', 'Initiated', 'ADMIN', 'Complete', 'Completed', '', 0, 0),
  ('Test', 'Initiated', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Test', 'Scheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0, 0),
  ('Test', 'Scheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Test', 'Scheduled', 'ADMIN', 'Complete', 'Completed', '', 0, 0),
  ('Test', 'ReScheduled', 'ADMIN', 'ReSchedule', 'ReScheduled', 'Candidate,EventManager,HiringManager,Subscriber', 0,
   0),
  ('Test', 'ReScheduled', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
  ('Test', 'ReScheduled', 'ADMIN', 'Complete', 'Completed', '', 0, 0),
  ('Test', 'Marks Updated', 'ADMIN', 'Complete', 'Completed', '', 1, 0),
  ('Test', 'Completed', 'ADMIN', 'Update Marks', 'Marks Updated', '', 0, 0),
('Application Received', 'Not Received', 'SYSTEM', 'AddCandidate', 'Application Received', 'Candidate', 1, 0);

INSERT INTO `ohrm_vacancy_event_type` (`id`, `type`, `icon`, `workflow`, `is_interview`, `is_test`)
VALUES ('1', 'Application Received', 'Paper', 'Application Received', 0, 0),
  ('2', 'Phone Interview', 'phone', 'Phone Interview', 1, 0),
  ('3', 'Panel Interview', 'jobs_icon', 'Panel Interview', 1, 0),
  ('4', 'One-On-One Interview', 'jobs_icon', 'One-On-One Interview', 1, 0),
  ('5', 'Test', 'exam', 'Test', 0, 1),
  ('6', 'Job Offer', 'Job_offer', 'Hire', 0, 0),
  ('7', 'Hire', 'jobOffer', 'Hire', 0, 0),
  ('8', 'Rejected', 'jobOffer', 'Rejected', 0, 0),
  ('9', 'Shortlisted', 'Paper', 'Shortlisted', 0, 0);

INSERT INTO `ohrm_vacancy_default_workflow` (`name`, `type_id`, `level`)
VALUES ('Application Received', 1, 1),
  ('Shortlisted', 9, 3),
  ('Interview', 3, 5),
  ('Job Offer', 6, 7),
  ('Hired', 7, 9),
  ('Rejected', 8, 11);

INSERT INTO `ohrm_vacancy_event_type_states` (`state_id`, `state`, `type_id`)
VALUES ('1', 'Initiated', '1'),
  ('2', 'Initiated', '2'),
  ('3', 'Initiated', '3'),
  ('4', 'Initiated', '4'),
  ('5', 'Initiated', '5'),
  ('6', 'Initiated', '7'),
  ('7', 'Scheduled', '1'),
  ('8', 'Scheduled', '2'),
  ('9', 'Scheduled', '3'),
  ('10', 'Scheduled', '4'),
  ('11', 'Scheduled', '5'),
  ('12', 'Marks Updated', '1'),
  ('13', 'Marks Updated', '2'),
  ('14', 'Marks Updated', '3'),
  ('15', 'Marks Updated', '4'),
  ('16', 'Marks Updated', '5'),
  ('17', 'Completed', '1'),
  ('18', 'Completed', '2'),
  ('19', 'Completed', '3'),
  ('20', 'Completed', '4'),
  ('21', 'Completed', '5'),
  ('22', 'Pending', '6'),
  ('23', 'Offered', '6'),
  ('24', 'Accepted', '6'),
  ('25', 'ReScheduled', '2'),
  ('26', 'ReScheduled', '3'),
  ('27', 'ReScheduled', '4'),
  ('28', 'ReScheduled', '5'),
  ('29', 'Initiated', '8'),
  ('30', 'Initiated', '9');

INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.default_vacancy_workflow', 'Default Vacancy Workflow');

INSERT INTO ohrm_candidate_source (`id`, `source_display_name`, `is_deletable`, `is_editable`)
VALUES (1, 'Other', 0, 0),
       (2, 'LinkedIn', 0, 1),
       (3, 'Indeed', 0, 1),
       (4, 'Glassdoor', 0, 1),
       (5, 'Monster', 0, 1),
       (6, 'ZipRecruiter', 0, 1),
       (7, 'Facebook', 0, 1),
       (8, 'X (Twitter)', 0, 1),
       (9, 'Instagram', 0, 1),
       (10, 'CaribbeanJobs.com', 0, 1);

INSERT INTO ohrm_candidate_source_url_pattern (`id`, `source_id`, `url_pattern`)
VALUES (1, 2, 'linkedin.com'),
       (2, 2, 'linkedin.cn'),
       (3, 2, 'lnkd.in'),
       (4, 2, 'oribi.io'),
       (5, 2, 'licdn.cn'),
       (6, 2, 'lnked.in'),
       (7, 2, 'linkd.in'),
       (8, 2, 'licdn.com'),
       (9, 3, 'indeed.com'),
       (10, 3, 'indeedemail.com'),
       (11, 3, 'indeedjobs.com'),
       (12, 3, 'mx.indeed.com'),
       (13, 4, 'glassdoor.com'),
       (14, 5, 'monster.com'),
       (15, 5, 'monsterjobs.com'),
       (16, 5, 'monster.net'),
       (17, 5, 'monsterindia.com'),
       (18, 6, 'ziprecruiter.com'),
       (19, 7, 'facebook.com'),
       (20, 7, 'fbcdn.net'),
       (21, 7, 'fb.com'),
       (22, 7, 'fb.gg'),
       (23, 7, 'accountkit.com'),
       (24, 7, 'facebook.net'),
       (25, 7, 'fbsbx.com'),
       (26, 7, 'atdmt.com'),
       (27, 7, 'atlassolutions.com'),
       (28, 7, 'facebookmail.com'),
       (29, 7, 'fb.watch'),
       (30, 7, 'fbwat.ch'),
       (31, 7, 'messenger.com'),
       (32, 7, 'm.me'),
       (33, 7, 'msngr.com'),
       (34, 8, 'twitter.com'),
       (35, 8, 'ads.twitter.com'),
       (36, 8, 'developer.twitter.com'),
       (37, 8, 'help.twitter.com'),
       (38, 8, 'mobile.twitter.com'),
       (39, 8, 'x.com'),
       (40, 8, 't.co'),
       (41, 9, 'instagram.com'),
       (42, 9, 'instagr.am'),
       (43, 9, 'cdinstagram.com'),
       (44, 9, 'igsonar.com'),
       (45, 9, 'threads.net'),
       (46, 9, 'threadsapp.net'),
       (47, 10, 'caribbeanjobs.com');

ALTER TABLE `ohrm_job_candidate`
  ADD `candidate_picture` MEDIUMBLOB DEFAULT NULL;

--
-- Table structure for table `ohrm_published_vacancy`
--

CREATE TABLE IF NOT EXISTS `ohrm_published_vacancy` (
  `id`             int(11) NOT NULL AUTO_INCREMENT,
  `vacancy_id`     int(11) NOT NULL,
  `published_url`  varchar(1000)    DEFAULT NULL,
  `job_identifier` varchar(1000)    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vacancy_id` (`vacancy_id`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = UTF8;

--
-- Constraints for table `ohrm_published_vacancy`
--
ALTER TABLE `ohrm_published_vacancy`
  ADD CONSTRAINT `ohrm_published_vacancy_ibfk_1` FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_published_vacancy`
  ADD `portal_name` VARCHAR(500) NULL
  AFTER `vacancy_id`;

SET @questinnaire_id := (SELECT `id`
                         FROM `ohrm_questionnaire`
                         WHERE `questionnaire_name` = 'vacancy');

INSERT INTO `ohrm_question_form` (`name`) VALUES
  ('Unstructured Interview Form');
SET @question_form_id := (SELECT `id`
                          FROM `ohrm_question_form`
                          WHERE `name` = 'Unstructured Interview Form');

INSERT INTO `ohrm_interview_template` (`name`, `description`, `question_form_id`, `duration`, `is_score_type`) VALUES
  ('Unstructured',
   'This is an interview where the interviewer can take notes. System does not provide interview questions.',
   @question_form_id, '30', 0);

INSERT INTO `ohrm_test_template` (`name`, `description`) VALUES
  ('Aptitude Test', 'Default Aptitude Test'),
  ('Personality Test', 'Default Personality Test'),
  ('Job Knowledge Test', 'Default Job Knowledge Test');

ALTER TABLE `ohrm_interview_template`
  ADD CONSTRAINT FOREIGN KEY (`parent_id`) REFERENCES `ohrm_interview_template` (`id`)
  ON DELETE SET NULL;
ALTER TABLE `ohrm_test_template`
  ADD CONSTRAINT FOREIGN KEY (`parent_id`) REFERENCES `ohrm_test_template` (`id`)
  ON DELETE SET NULL;
INSERT INTO ohrm_config (`property`, `value`) VALUES ('recruitment.candidate_social_media_fields', 'on');

INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.equest_url', 'https://native.testing.equest.com/post/');
INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.equest_soap_url', 'https://jws.testing.equest.com/soap/1.0?wsdl');
INSERT INTO ohrm_config (`property`, `value`) VALUES ('recruitment.equest_username', 'orangeHRM-user');
INSERT INTO ohrm_secure_configuration (`configuration_name`, `updatable_from_frontend`) VALUES ('recruitment.equest_password', 1);
INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.equest_return_host', 'http://chathuraorangehrm.ddns.net');
INSERT INTO ohrm_config (`property`, `value`) VALUES ('recruitment.equest_enabled', 'off');
INSERT INTO ohrm_config (`property`, `value`) VALUES ('recruitment.equest_requisition_prefix', 'ohrm');

INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.interview_question_auto_generation_class', 'DefaultQuestionAutoGenerator');
INSERT INTO ohrm_config (`property`, `value`) VALUES ('recruitment.question_for_interview_auto_generate', '20');
INSERT INTO ohrm_config (`property`, `value`) VALUES ('recruitment.interview.section.score', 'weighted_average');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('recruitment_equest', 'Recruitment (ATS) - EQUEST', 1, 1, 1, 1);

SET @equest_data_group_id := (SELECT `id`
                              FROM `ohrm_data_group`
                              WHERE `name` = 'recruitment_equest');
INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id) VALUES
  (1, @equest_data_group_id);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
VALUES
  (1, @equest_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_interview_guideline` (`guideline`) VALUES ('<p class="heading">Job Relevance is the Key Factor</p>
        <p class="body-text" style="margin: 16px;">Your interview
            questions should be designed to determine a candidate''s capability
            to perform the essential functions you have defined for the job.
            Just be sure to couch your inquiries in job-relevant language, and
            don''t make assumptions about a candidate''s ability or disability.
            For example, let''s say you are interviewing a wheelchair-bound
            candidate for an account manager position, and you have determined
            that an essential function of the job is to visit client sites.
            It''s perfectly legal to ask how the candidate would perform this
            essential function: "This job will require you to be out of the
            office meeting with clients several days per week. Can you tell me
            how you would get around?"</p>

        <p class="body-text" style="margin: 16px;">It is not OK to say
            to this same candidate, "How long have you been disabled?"</p>

        <p class="body-text" style="margin: 16px;">In other areas,
            where a disability is not visible, again you should confine your
            questions to essential job functions or workplace environment
            issues. For example, while you cannot ask a candidate if he or she
            has children or has adequate child care, you can ask about ability
            to perform the job:</p>

        <p class="body-text" style="margin: 16px;">"This job requires
            you to travel overnight about 2 days per week and to attend
            out-of-town conferences once per month. Does this travel schedule
            prevent a problem for you?"</p>

        <p class="heading">Legal and Illegal Inquiries</p>
        <p class="body-text" style="margin: 16px;">Following are some
            of the key areas that are covered by fair hiring laws. You will
            see a trend in what is legal and what is illegal - essentially,
            you cannot ask questions that will reveal information that can
            lead to bias in hiring, but you can ask questions that relate to
            job performance.</p>

        <ul
            style="list-style: disc; padding-left: 25px; font-size: 16px; font-weight: 300; line-height: 1.6em;">
            <li style="list-style-type: disc;">Affiliations: Do not ask
                about clubs, social organizations, or union membership; do ask
                about relevant professional associations.</li>
            <li style="list-style-type: disc;">Age: Do not ask a
                candidate''s age other than, "if hired," can a candidate produce
                proof that he or she is 18 years of age.</li>
            <li style="list-style-type: disc;">Alcohol or Drug Use: The
                only allowable question relating to current or past drug or
                alcohol use is, "Do you currently use illegal drugs?"</li>
            <li style="list-style-type: disc;">Criminal Record: Do not
                ask if a candidate has been arrested; you may ask if the
                candidate has ever been convicted of a crime.</li>
            <li style="list-style-type: disc;">Culture/Natural Origin:
                You may ask if the individual can, "upon hire," provide proof of
                legal right to work in the United States. You may ask about
                language fluency if it is relevant to job performance.</li>
            <li style="list-style-type: disc;">Disability: You may ask if
                candidates can perform essential job functions, with or without
                reasonable accommodation; and you may ask them to demonstrate how
                they would perform a job-related function. You may ask about
                prior attendance records. And you may require candidates to
                undergo a medical exam after an offer of employment has been
                made.</li>
            <li style="list-style-type: disc;">Marital/Family Status:
                Questions about marital status and family issues are discouraged
                except as they relate to job performance, as in the child care
                example above.</li>
            <li style="list-style-type: disc;">Personal: Avoid questions
                related to appearance, home ownership, and personal financial
                situation. Race/Color: No race-related questions are legal.</li>
            <li style="list-style-type: disc;">Religion: If Saturday or
                Sunday is a required work day, you may ask candidates if they
                will have a problem working on those days.</li>
            <li style="list-style-type: disc;">Sex: You may ask if a
                candidate has ever worked under another name. Be sure not to make
                gender-related assumptions about job capabilities.</li>
        </ul>');

SET @default_guidline_id := (SELECT LAST_INSERT_ID()
                             FROM `ohrm_interview_guideline`);

INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.default_interview_guideline_id', @default_guidline_id);

INSERT INTO ohrm_config (`property`, `value`)
VALUES ('recruitment.candidate_document_digital_sign', 'off');

SET @audittrail_module_id := (SELECT `id`
                              FROM `ohrm_audittrail_module`
                              WHERE `name` = 'Recruitment (ATS)');
SET @recruitment_candidate_audit_trail_section := (SELECT `id`
                                                   FROM `ohrm_audittrail_section`
                                                   WHERE `name` = 'JobVacancy' AND `module_id` = @audittrail_module_id);
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_audit_trail_section, 'DELETE CANDIDATES', 'Delete Candidates');
INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`)
VALUES (NULL, 'recruitment.delete_candidate', 'RecruitmentDeleteCandidateListener', '1', '84');

SET @recruitment_job_vacancy_template_section := (SELECT `id`
                                                  FROM `ohrm_audittrail_section`
                                                  WHERE `name` = 'JobVacancyTemplate' AND
                                                        `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_job_vacancy_template_section, 'ADD VACANCY TEMPLATE', 'Add Job Vacancy Template');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_job_vacancy_template_section, 'UPDATE VACANCY TEMPLATE', 'Change Job Vacancy Template');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_job_vacancy_template_section, 'DELETE VACANCY TEMPLATE', 'Delete Job Vacancy Template');

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_audit_trail_section, 'ADD JOB VACANCY', 'Add Job Vacancy');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_audit_trail_section, 'CHANGE JOB VACANCY', 'Change Job Vacancy');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_audit_trail_section, 'DELETE JOB VACANCY', 'Delete Job Vacancy');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
VALUES
  (@hiring_manager_id, @equest_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('recruitment.resume_max_upload_size_mb', '5');

-- Adding new indexes to optimize candidate search query
ALTER TABLE `ohrm_job_candidate` ADD INDEX deleted_at (`deleted_at`);
ALTER TABLE `ohrm_job_candidate` ADD INDEX date_of_application (`date_of_application`);
ALTER TABLE `ohrm_job_candidate` ADD INDEX first_name (`first_name`);
ALTER TABLE `ohrm_job_candidate` ADD INDEX middle_name (`middle_name`);
ALTER TABLE `ohrm_job_candidate` ADD INDEX last_name (`last_name`);
ALTER TABLE `ohrm_job_candidate` ADD INDEX email (`email`);

ALTER TABLE `ohrm_job_vacancy` ADD INDEX name (`name`);
ALTER TABLE `ohrm_job_vacancy` ADD INDEX status(`status`);

ALTER TABLE `ohrm_candidate_event_state` ADD INDEX active (`active`);

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_recruitment_job_candidate_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action_owner_id` varchar(6),
    `action_owner_name` VARCHAR(300),
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_recruitment_job_candidate_trail_idx_app_id (app_id),
    INDEX audit_recruitment_job_candidate_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audit_recruitment_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Recruitment (ATS)');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('Candidate', 'Candidate', 'Candidate', @audit_recruitment_module_id);

SET @candidate_audit_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'Candidate' LIMIT 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(@candidate_audit_section_id, 'ADD CANDIDATE', 'Add Candidate'),
(@candidate_audit_section_id, 'UPDATE CANDIDATE', 'Update Candidate');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
     ('recruitment.add_candidate', 'AddCandidateAuditListener', '1', @max_event_listener_position+1),
     ('recruitment.update_candidate', 'UpdateCandidateAuditListener', '1', @max_event_listener_position+2);

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_document_template_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action_owner_id` varchar(6),
    `action_owner_name` VARCHAR(300),
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description` LONGTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_document_template_trail_idx_app_id (app_id),
    INDEX audit_document_template_trail_idx_screen_name (screen_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('CandidateSource', 'Candidate Source', 'Candidate Source', @audit_recruitment_module_id);

SET @recruitment_candidate_source_audit_trail_section := (SELECT `id`
                                                   FROM `ohrm_audittrail_section`
                                                   WHERE `name` = 'CandidateSource' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_source_audit_trail_section, 'ADD CANDIDATE SOURCE', 'Add Candidate Source');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_source_audit_trail_section, 'UPDATE CANDIDATE SOURCE', 'Update Candidate Source');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@recruitment_candidate_source_audit_trail_section, 'DELETE CANDIDATE SOURCE', 'Delete Candidate Source');

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_recruitment_candidate_source_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action_owner_id` varchar(6),
    `action_owner_name` VARCHAR(300),
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description` MEDIUMTEXT,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_recruitment_candidate_source_trail_idx_app_id (app_id),
    INDEX audit_recruitment_candidate_source_trail_idx_screen_name (screen_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
            ('recruitment.add_candidate_source', 'AddCandidateSourceAuditListener', '1', @max_event_listener_position+1),
            ('recruitment.update_candidate_source', 'UpdateCandidateSourceAuditListener', '1', @max_event_listener_position+2),
            ('recruitment.delete_candidate_source', 'DeleteCandidateSourceAuditListener', '1', @max_event_listener_position+3);

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('DocumentTemplate', 'Document Template', 'Document Template', @audit_recruitment_module_id);

SET @recruitment_document_template_audit_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'DocumentTemplate' LIMIT 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
            (@recruitment_document_template_audit_section_id, 'ADD DOCUMENT TEMPLATE', 'Add Recruitment Document Template'),
            (@recruitment_document_template_audit_section_id, 'UPDATE DOCUMENT TEMPLATE', 'Update Recruitment Document Template'),
            (@recruitment_document_template_audit_section_id, 'DELETE DOCUMENT TEMPLATE', 'Delete Recruitment Document Template');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
            ('recruitment.add_document_template', 'RecruitmentAddDocumentTemplateAuditListener', '1', @max_event_listener_position+1),
            ('recruitment.update_document_template', 'RecruitmentUpdateDocumentTemplateAuditListener', '1', @max_event_listener_position+2),
            ('recruitment.delete_document_template', 'RecruitmentDeleteDocumentTemplateAuditListener', '1', @max_event_listener_position+3);

CREATE TABLE IF NOT EXISTS `ohrm_document_template_vacancy` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `document_template_id` INT NOT NULL,
    `vacancy_id` INT DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_template_vacancy_template`
    FOREIGN KEY (`document_template_id`) REFERENCES `ohrm_document_template` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_template_vacancy_vacancy`
    FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_document_template_vacancy_event` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `document_template_id` INT NOT NULL,
    `vacancy_event_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_template_vacancy_event_template`
    FOREIGN KEY (`document_template_id`) REFERENCES `ohrm_document_template` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_template_vacancy_event_vacancy_event`
    FOREIGN KEY (`vacancy_event_id`) REFERENCES `ohrm_vacancy_event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_document_template_vacancy_event_type` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `document_template_id` INT NOT NULL,
    `vacancy_event_type_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_template_vacancy_event_type_template`
    FOREIGN KEY (`document_template_id`) REFERENCES `ohrm_document_template` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_template_vacancy_event_type_vacancy_event_type`
    FOREIGN KEY (`vacancy_event_type_id`) REFERENCES `ohrm_vacancy_event_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Recruitment tokens
INSERT INTO `ohrm_token_section`(`name`, `display_name`,`menu_id`) VALUES
    ('Vacancy','Vacancy', null),
    ('Candidate','Candidate', null),
    ('JobOfferDetails', 'Job Offer Details', null);

SET @token_section_vacancy := (SELECT `id` FROM ohrm_token_section WHERE `name` = 'Vacancy');
SET @token_section_candidate := (SELECT `id` FROM ohrm_token_section WHERE `name` = 'Candidate');
SET @token_section_job_offer := (SELECT `id` FROM ohrm_token_section WHERE `name` = 'JobOfferDetails');


INSERT INTO `ohrm_token`(`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `token_section_id`) VALUES
    ('vacancyName', 'Vacancy', null, 'Vacancy', 'getName', @candidate_data_group_id, 0, 1, @token_section_vacancy),
    ('vacancyJobTitle', 'Job Title', null, 'Vacancy', 'getJobTitle,getJobTitleName', @candidate_data_group_id, 0, 1, @token_section_vacancy),
    ('vacancyLocation', 'Location', null, 'Vacancy', 'getLocation', @candidate_data_group_id, 0, 1, @token_section_vacancy),
    ('vacancySubUnit', 'Sub Unit', null, 'Vacancy', 'getSubunit', @candidate_data_group_id, 0, 1, @token_section_vacancy),
    ('candidateFirstName', 'Candidate First Name', null, 'Candidate', 'getFirstName', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidateMiddleName', 'Candidate Middle Name', null, 'Candidate', 'getMiddleName', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidateLastName', 'Candidate Last Name', null, 'Candidate', 'getLastName', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidateFullName', 'Candidate Full Name', null, 'Candidate', 'getFullName', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidateEmail', 'Candidate Email', null, 'Candidate', 'getEmail', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidateContactNumber', 'Candidate Contact Number', null, 'Candidate', 'getContactNumber', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidatePersonalDetailsKeywords', 'Keywords', null, 'Candidate', 'getKeywords', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidatePersonalDetailsComment', 'Candidates Comment', null, 'Candidate', 'getComment', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidatePersonalDetailsLinkedIn', 'LinkedIn Profile', null, 'Candidate', 'getLinkedInURL', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidatePersonalDetailsTwitter', 'Twitter Profile', null, 'Candidate', 'getTwitterURL', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('candidatePersonalDetailsFacebook', 'Facebook Profile', null, 'Candidate', 'getFacebookURL', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('dateOfApplication', 'Date of Application', null, 'Candidate', 'getDateOfApplication', @candidate_data_group_id, 1, 1, @token_section_candidate),
    ('currentStage', 'Current Stage', null, 'Candidate', 'getCurrentStage', @candidate_data_group_id, 0, 1, @token_section_candidate),
    ('documentCreator', 'Document Creator', 'DocumentCreatorTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, null),
    ('candidateJobOfferDate', 'Candidate Job Offer - Offer Date', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 1, 1, @token_section_job_offer),
    ('candidateJobOfferEndDate', 'Candidate Job Offer - Offer End Date', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 1, 1, @token_section_job_offer),
    ('candidateJobOfferJoinedDate', 'Candidate Job Offer - Joined Date', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 1, 1, @token_section_job_offer),
    ('candidateJobOfferEmploymentStatus', 'Candidate Job Offer - Employment Status', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, @token_section_job_offer),
    ('candidateJobOfferPayGrade', 'Candidate Job Offer - Pay Grade', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, @token_section_job_offer),
    ('candidateJobOfferPayGradeCurrency', 'Candidate Job Offer - Pay Grade - Currency', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, @token_section_job_offer),
    ('candidateJobOfferPayGradeRange', 'Candidate Job Offer - Pay Grade - Range', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0, @token_section_job_offer),
    ('candidateJobOfferAnnualBasicPayment', 'Candidate Job Offer - Annual Basic Payment', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0, @token_section_job_offer),
    ('candidateJobOfferGrossPay', 'Candidate Job Offer - Gross Pay', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0, @token_section_job_offer),
    ('candidateJobOfferTotalDeductions', 'Candidate Job Offer - Total Deductions', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0, @token_section_job_offer),
    ('candidateJobOfferCosttotheCompany', 'Candidate Job Offer - Cost to the Company', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0, @token_section_job_offer),
    ('candidateJobOfferTotalPayable', 'Candidate Job Offer - Total Payable', 'CandidateJobOfferSalaryTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0, @token_section_job_offer);

INSERT INTO `ohrm_token` (`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`,
                          `is_date`, `localize`, `parameters`, `token_section_id`)
VALUES ('candidateJobOfferSupervisorName', 'Candidate Job Offer - Supervisor Name', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1,null,@token_section_job_offer),
       ('candidateJobOfferSupervisorJobTitle', 'Candidate Job Offer - Supervisor Job Title', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1,null,@token_section_job_offer),
       ('candidateJobOfferCandidateAddress', 'Candidate Job Offer - Candidate Address', 'CandidateJobOfferTokenValueGenerator', null, null, @candidate_data_group_id, 0, 0,'avoidDefaultEncode=>true,type=>line_breaks',@token_section_job_offer);

SET @token_case_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'vacancyName');
SET @token_title_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'vacancyJobTitle');
SET @token_location_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'vacancyLocation');
SET @token_subunit_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'vacancySubUnit');
SET @token_first_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateFirstName');
SET @token_middle_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateMiddleName');
SET @token_last_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateLastName');
SET @token_full_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateFullName');
SET @token_email_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateEmail');
SET @token_contact_no_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateContactNumber');
SET @token_keywords_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidatePersonalDetailsKeywords');
SET @token_comment_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidatePersonalDetailsComment');
SET @token_linkedin_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidatePersonalDetailsLinkedIn');
SET @token_twitter_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidatePersonalDetailsTwitter');
SET @token_facebook_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidatePersonalDetailsFacebook');
SET @token_date_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'dateOfApplication');
SET @token_stage_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'currentStage');
SET @token_document_creator_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'documentCreator');
SET @token_job_offer_date_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferDate');
SET @token_job_offer_end_date_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferEndDate');
SET @token_job_offer_joined_date_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferJoinedDate');
SET @token_job_offer_emp_status_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferEmploymentStatus');
SET @token_job_offer_pay_grade_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferPayGrade');
SET @token_job_offer_pay_grade_currency_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferPayGradeCurrency');
SET @token_job_offer_pay_grade_range_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferPayGradeRange');
SET @token_job_offer_annual_basic_payment_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferAnnualBasicPayment');
SET @token_job_offer_gross_pay_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferGrossPay');
SET @token_job_offer_total_deduction_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferTotalDeductions');
SET @token_job_offer_cost_to_company_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferCosttotheCompany');
SET @token_job_offer_total_payable_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferTotalPayable');
SET @token_job_offer_address_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferCandidateAddress');
SET @token_job_offer_supervisor_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferSupervisorName');
SET @token_job_offer_supervisor_job_title_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'candidateJobOfferSupervisorJobTitle');

INSERT INTO `ohrm_restricted_module_token`(`token_id`, `restricted_module`) VALUES
    (@token_case_name_id, @recruitment_module_id),
    (@token_title_id, @recruitment_module_id),
    (@token_location_id, @recruitment_module_id),
    (@token_subunit_id, @recruitment_module_id),
    (@token_first_name_id, @recruitment_module_id),
    (@token_middle_name_id, @recruitment_module_id),
    (@token_full_name_id, @recruitment_module_id),
    (@token_email_id, @recruitment_module_id),
    (@token_contact_no_id, @recruitment_module_id),
    (@token_last_name_id, @recruitment_module_id),
    (@token_keywords_id, @recruitment_module_id),
    (@token_comment_id, @recruitment_module_id),
    (@token_linkedin_id, @recruitment_module_id),
    (@token_twitter_id, @recruitment_module_id),
    (@token_facebook_id, @recruitment_module_id),
    (@token_date_id, @recruitment_module_id),
    (@token_stage_id, @recruitment_module_id),
    (@token_document_creator_id, @recruitment_module_id),
    (@token_job_offer_date_id, @recruitment_module_id),
    (@token_job_offer_end_date_id, @recruitment_module_id),
    (@token_job_offer_joined_date_id, @recruitment_module_id),
    (@token_job_offer_emp_status_id, @recruitment_module_id),
    (@token_job_offer_pay_grade_id, @recruitment_module_id),
    (@token_job_offer_pay_grade_currency_id, @recruitment_module_id),
    (@token_job_offer_pay_grade_range_id, @recruitment_module_id),
    (@token_job_offer_annual_basic_payment_id, @recruitment_module_id),
    (@token_job_offer_gross_pay_id, @recruitment_module_id),
    (@token_job_offer_total_deduction_id, @recruitment_module_id),
    (@token_job_offer_cost_to_company_id, @recruitment_module_id),
    (@token_job_offer_total_payable_id, @recruitment_module_id),
    (@token_job_offer_address_id, @recruitment_module_id),
    (@token_job_offer_supervisor_name_id, @recruitment_module_id),
    (@token_job_offer_supervisor_job_title_id, @recruitment_module_id);

-- Multi Valued Tokens
INSERT INTO `ohrm_token`(`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `parameters`, `token_section_id`) VALUES
    ('hiringManagersFullName', 'Hiring Manager(s) - Full Name', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('hiringManagersFirstName', 'Hiring Manager(s) - First Name', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('hiringManagersMiddleName', 'Hiring Manager(s) - Middle Name', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('hiringManagersLastName', 'Hiring Manager(s) - Last Name', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('hiringManagerAndTitle', 'Hiring Manager Name, Job Title', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>line_breaks,hasMultiple=>true', @token_section_vacancy),
    ('hiringManagersWorkTelephone', 'Hiring Manager(s) - Work Telephone', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('hiringManagersWorkEmail', 'Hiring Manager(s) - Work Email', 'HiringManagerTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('stageCoordinatorsFirstName', 'Stage Coordinator(s) - First Name', 'VacancyStageCoordinatorTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('stageCoordinatorsMiddleName', 'Stage Coordinator(s) - Middle Name', 'VacancyStageCoordinatorTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('stageCoordinatorsLastName', 'Stage Coordinator(s) - Last Name', 'VacancyStageCoordinatorTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy),
    ('stageCoordinatorsFullName', 'Stage Coordinator(s) - Full Name', 'VacancyStageCoordinatorTokenValueGenerator', null, null, @candidate_data_group_id, 0, 1, 'avoidDefaultEncode=>true,type=>bullet_points,hasMultiple=>true', @token_section_vacancy);

SET @token_hm_full_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagersFullName');
SET @token_hm_first_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagersFirstName');
SET @token_hm_middle_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagersMiddleName');
SET @token_hm_last_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagersLastName');
SET @token_hm_job_title_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagerAndTitle');
SET @token_hm_work_telephone_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagersWorkTelephone');
SET @token_hm_work_email_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'hiringManagersWorkEmail');
SET @token_stage_coord_first_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'stageCoordinatorsFirstName');
SET @token_stage_coord_middle_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'stageCoordinatorsMiddleName');
SET @token_stage_coord_last_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'stageCoordinatorsLastName');
SET @token_stage_coord_full_name_id := (SELECT `id` FROM ohrm_token WHERE `name` = 'stageCoordinatorsFullName');

INSERT INTO `ohrm_restricted_module_token` (`token_id`, `restricted_module`) VALUES
    (@token_hm_full_name_id, @recruitment_module_id),
    (@token_hm_first_name_id, @recruitment_module_id),
    (@token_hm_middle_name_id, @recruitment_module_id),
    (@token_hm_last_name_id, @recruitment_module_id),
    (@token_hm_job_title_id, @recruitment_module_id),
    (@token_hm_work_telephone_id, @recruitment_module_id),
    (@token_hm_work_email_id, @recruitment_module_id),
    (@token_stage_coord_first_name_id, @recruitment_module_id),
    (@token_stage_coord_middle_name_id, @recruitment_module_id),
    (@token_stage_coord_last_name_id, @recruitment_module_id),
    (@token_stage_coord_full_name_id, @recruitment_module_id);

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
            ('recruitment.best_match_rule_change', 'BestMatchRuleChangeListener', '1', @max_event_listener_position+1);

INSERT INTO ohrm_data_list (`name`) VALUES ('candidateList');

INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('recruitment.apply_vacancy.attempts_for_captcha', '1');
INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('recruitment.apply_vacancy.attempts_for_block', '3');
INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('recruitment.apply_vacancy.blocked_duration', '0:01:00');
INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('recruitment.apply_vacancy.captcha.window_period', '00:10:00');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('recruitment.add_additional_fields_to_rss_feed', 'on');

SET @ohrm_data_group_recruitment_candidates_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'recruitment_candidates');
SET @ohrm_data_group_recruitment_configuration_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'recruitment_configuration');
SET @ohrm_data_group_recruitment_vacancies_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'recruitment_vacancies');

SET @ohrm_audittrail_section_candidate = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Candidate');
SET @ohrm_audittrail_section_document_template = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='DocumentTemplate');
SET @ohrm_audittrail_section_job_vacancy = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='JobVacancy');
SET @ohrm_audittrail_section_job_vacancy_template = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='JobVacancyTemplate');


INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
    (@ohrm_audittrail_section_candidate, @ohrm_data_group_recruitment_candidates_id, 0),
    (@ohrm_audittrail_section_document_template, @ohrm_data_group_recruitment_configuration_id, 0),
    (@ohrm_audittrail_section_job_vacancy, @ohrm_data_group_recruitment_vacancies_id, 0),
    (@ohrm_audittrail_section_job_vacancy_template, @ohrm_data_group_recruitment_configuration_id, 0);