CREATE TABLE ohrm_etl_interface_mapper (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
PRIMARY KEY(id))
ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_etl_error_log (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  `type` VARCHAR(255),
  `json_log` text,
  `date` DATETIME,
  `interface_mapper_id` INT,
   PRIMARY KEY(id),
   FOREIGN KEY ( `interface_mapper_id` ) REFERENCES `ohrm_etl_interface_mapper` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = INNODB  DEFAULT CHARSET=utf8;

-- ALTER TABLE `hs_hr_employee` ADD COLUMN `synced_time` TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE `ohrm_job_candidate` ADD `321_user_name` VARCHAR(255) NULL AFTER `contact_number` ;

SET @module_id := (SELECT id FROM ohrm_module WHERE name = 'recruitment' LIMIT 1);

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`,`module_id`, `order_no`) VALUES
('Recruitment Task', 'Recruitment Task', NULL, '1','2' , 'candidate', 'Candidate',@module_id, 4);

INSERT INTO `ohrm_vacancy_event_type`(`id`,`type`,`icon`,`workflow`,`is_interview`,`is_test`) VALUES
('10','Recruitment Task','jobOffer','Recruitment Task',0,0);

SET @max_state_id = (SELECT MAX(`state_id`)
                                    FROM ohrm_vacancy_event_type_states);
INSERT INTO `ohrm_vacancy_event_type_states`(`state_id`,`state`,`type_id`) VALUES
(@max_state_id + 1,'Initiated', '10');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('321_forms.username', 'OrangeHRM'),
('321_forms.service_entry_point', 'https://api.321forms.com/v1/'),
('321_forms.web_service_test_mode', 'No'),
('321_forms.approved_date_format', 'F, d Y H:i:s'),
('321_forms.web_service_test_yml_file', 'webservice321Test.yml'),
('321_forms.last_synced_time', '0'),
('etl.interface_map_send_error_log_notifications', 'Yes'),
('etl.interface_map_error_log_email_recipients', '');

INSERT INTO ohrm_secure_configuration (`configuration_name`, `configuration`, `updatable_from_frontend`) VALUES
('321_forms.secret_key', '4BBC5492-93F5-BB39-D7C3AEE4ED8E126B', 1),
('321_forms.company_id', '787', 1),
('321_forms.attachment_screen', 'job', 1);

INSERT INTO `ohrm_etl_interface_mapper` (`name`) VALUES
('321 Forms Import');

INSERT INTO ohrm_email(`name`) VALUES
('etl.interface_error_log');
SET @etl_error_log_email_id := (SELECT id FROM ohrm_email WHERE name = 'etl.interface_error_log');

INSERT INTO  ohrm_email_processor(`email_id`, `class_name`) VALUES
(@etl_error_log_email_id, 'ETLInterfaceErrorLogEmailProcessor');

INSERT INTO ohrm_email_template(`email_id`, `locale`, `performer_role`, `recipient_role`,`body`, `subject`) VALUES
(@etl_error_log_email_id, 'en_US', 'System', 'OrangeHRM', 'orangehrm321FormsPlugin/modules/recruitment/templates/mail/en_US/errorLog/interfaceErrorLogBody.txt', 'orangehrm321FormsPlugin/modules/recruitment/templates/mail/en_US/errorLog/interfaceErrorLogSubject.txt');

INSERT INTO `ohrm_symfony_task` (`name`, `short_description`, `long_description`, `command`, `schedule`, `observer_class`, `prevent_overlap`,`user_interface`,`status`) VALUES
('Sync 321 Forms', 'This will import 321 Forms from completed users to OrangeHRM.', 'This task is executed on a daily basis on production systems. If there is a scheduled sync process in the database this task will execute it and import data.', 'php symfony orangehrm:Sync321FormsOnboardingInformation', '0 2 * * *', '', 0,'executableTask', 1);