CREATE TABLE IF NOT EXISTS ohrm_preboarding_employee (
    id INT AUTO_INCREMENT,
    emp_number INT DEFAULT NULL,
    candidate_id INT(13) DEFAULT NULL,
    workflow_request_id INT(11) NULL DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    CONSTRAINT `fk_preboarding_employee_emp_number` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    CONSTRAINT `fk_preboarding_employee_candidate_id` FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_preboarding_employee_workflow_request_id` FOREIGN KEY (`workflow_request_id`) REFERENCES `ohrm_advanced_workflow_request` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_preboarding_new_hire` (
    `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_preboarding_employee_trail_idx_app_id (app_id),
    INDEX audit_preboarding_employee_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_module` (`name`, `status`, `data_group_prefix`) VALUES
    ('preboarding', 1, 'Preboarding'),
    ('onboardingPro', 1, null);
SET @preboarding_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'preboarding');

SET @talent_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Talent Management' AND `level` = 1);
SET @onboarding_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Onboarding' AND `level` = 2 AND parent_id = @talent_management_pillar_menu_id);

SET @pro_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'onboardingPro');

INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES
    ('PreboardingEventManager', 'Preboarding Event Manager', 0, 1),
    ('PreboardingNewHire', 'Preboarding New Hire', 0, 1),
    ('PreboardingReferee', 'Preboarding Referee', 0, 1);

SET @admin_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET @hiring_manager_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HiringManager');
SET @preboarding_event_manager_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'PreboardingEventManager');
SET @preboarding_new_hire_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'PreboardingNewHire');
SET @supervisor_id := (SELECT id FROM ohrm_user_role where name = 'Supervisor');
SET @ess_id := (SELECT id FROM ohrm_user_role where name = 'ESS');
SET @hod_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'HeadOfDepartment');
SET @admin_user_role_type := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
SET @supervisor_role_type := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Supervisor');
SET @ess_role_type := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'ESS');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
    ('Preboarding Employee List', @preboarding_module_id, 'preboardingEmployees', 'preboarding_preboardingEmployees');
SET @preboardingEmployees_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Preboarding Employee List');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    (@admin_user_role_id, @preboardingEmployees_screen_id , 1, 1, 1, 1),
    (@supervisor_id, @preboardingEmployees_screen_id , 1, 1, 1, 1),
    (@hod_user_role, @preboardingEmployees_screen_id , 1, 1, 1, 1),
    (@hiring_manager_user_role_id, @preboardingEmployees_screen_id , 1, 1, 1, 1),
    (@preboarding_event_manager_user_role_id, @preboardingEmployees_screen_id , 1, 1, 1, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
    ('Preboarding',@preboardingEmployees_screen_id, @onboarding_menu_id, 3, 500, '', 'preboarding.preboardingEmployees',1);

INSERT INTO `ohrm_data_group` (name, description, can_read, can_create, can_update, can_delete)
VALUES ('preboarding_new_hires', 'Preboarding - Preboarding New Hires', 1, 0, 0, 0);

SET @data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'preboarding_new_hires');

INSERT INTO ohrm_user_role_data_group (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self)
VALUES (@admin_user_role_id, @data_group_id, 1, 0, 0, 0, 0),
       (@supervisor_id, @data_group_id, 1, 0, 0, 0, 0),
       (@hod_user_role, @data_group_id, 1, 0, 0, 0, 0),
       (@hiring_manager_user_role_id, @data_group_id, 1, 0, 0, 0, 0),
       (@preboarding_event_manager_user_role_id, @data_group_id, 1, 0, 0, 0, 0),
       (@ess_id, @data_group_id, 1, 0, 0, 0, 1),
       (@preboarding_new_hire_user_role_id, @data_group_id, 1, 0, 0, 0, 1);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
    (@data_group_id,@preboardingEmployees_screen_id,1);

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id, can_read, can_create, can_update, can_delete)
VALUES (@admin_user_role_type, @data_group_id, 1, 1, 1, 1),
        (@supervisor_role_type, @data_group_id, 1, 1, 1, 1),
        (@ess_role_type, @data_group_id, 1, 1, 1, 1);

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

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`,`module_id`, `order_no`) VALUES
    ('PREBOARDING_EMPLOYEE_ENTITY', 'PREBOARDING_EMPLOYEE_ENTITY', 'Preboarding - Preboarding Employee', null, '1,3', null, null, @preboarding_module_id, @max_wf_order + 1),
    ('Preboarding', 'Preboarding', null, '1', '2', 'candidate', 'Candidate', @recruitment_module_id, 4);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `priority`)
VALUES ('PREBOARDING_EMPLOYEE_ENTITY', 'In Progress', 'HiringManager', 'SAVE', 'In Progress', '0'),
       ('PREBOARDING_EMPLOYEE_ENTITY', 'In Progress', 'Admin', 'SAVE', 'In Progress', '0'),
       ('PREBOARDING_EMPLOYEE_ENTITY', 'In Progress', 'HeadOfDepartment', 'SAVE', 'In Progress', '0'),
       ('PREBOARDING_EMPLOYEE_ENTITY', 'In Progress', 'Supervisor', 'SAVE', 'In Progress', '0'),
       ('PREBOARDING_EMPLOYEE_ENTITY', 'In Progress', 'SYSTEM', 'Complete', 'Completed', '0');

INSERT INTO `ohrm_advanced_workflow_entity` (`name`, `workflow_id`, `description`)
VALUES ('Preboarding Employee', 'PREBOARDING_EMPLOYEE_ENTITY', 'Primary workflow entity for the Preboarding Employee');

SET @max_entity_process_status_id := (SELECT GREATEST(100, IFNULL(MAX(`status_id`), 0)) FROM `ohrm_advanced_workflow_entity_status`);

INSERT INTO `ohrm_advanced_workflow_entity_status` (entity, status_id, name, description, sequence)
VALUES
    ('Preboarding Employee', @max_entity_process_status_id + 1, 'In Progress', 'Preboarding Employee is in preboarding stage', 1),
    ('Preboarding Employee', @max_entity_process_status_id + 2, 'Completed', 'Preboarding is Completed', 2);

INSERT INTO `ohrm_vacancy_event_type` (`id`, `type`, `icon`, `workflow`, `is_interview`, `is_test`)
VALUES ('20', 'Preboarding', 'jobOffer', 'Preboarding', 0, 0);

SET @preboarding_vacancy_event_type_id := (SELECT `id` FROM `ohrm_vacancy_event_type` WHERE `type` = 'Preboarding');
SET @max_vacancy_event_type_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_vacancy_event_type_state_id + 1, 'Initiated', @preboarding_vacancy_event_type_id);

INSERT INTO `ohrm_vacancy_default_workflow` (`name`, `type_id`, `level`)
VALUES ('Preboarding', @preboarding_vacancy_event_type_id, 10);

INSERT INTO `ohrm_audittrail_module` (`name`) VALUES ('Preboarding');
SET @audit_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Preboarding');
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('PreboardingNewHire', 'Preboarding New Hires', 'Preboarding New Hire', @audit_module_id);

SET @ohrm_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='PreboardingNewHire');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`datagroup_id` ,`description`) VALUES
    (@ohrm_audittrail_section_id, 'UPDATE STAGE', @data_group_id,'Update Stage'),
    (@ohrm_audittrail_section_id, 'INITIATE PREBOARDING', @data_group_id,'Initiate Preboarding');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`) VALUES
    (@ohrm_audittrail_section_id, @data_group_id, 0);

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`)
VALUES ('recruitment.workflow_change', 'CandidateMoveToPreboardingStageEventListener', 1, 0),
       ('advanced_task.state_change', 'PreboardingMailer', 1, 1),
       ('advanced_task.state_change', 'PreboardingTaskStateChangeAuditListener', 1, 2),
       ('advanced_task.form_data_save', 'PreboardingTaskFormDataSaveAuditListener', 1, 0),
       ('candidate_user_added', 'AddCandidateUserAuditListener', 1, 0),
       ('preboarding.initiate', 'InitiatePreboardingAuditListener', 1, 0);

INSERT INTO `ohrm_token_section`(`name`, `display_name`,`menu_id`) VALUES
   ('PreboardingNewHire','Preboarding New Hire', null);

SET @token_section := (SELECT `id` FROM ohrm_token_section WHERE `name` = 'PreboardingNewHire');
INSERT INTO `ohrm_token`(`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `token_section_id`) VALUES
   ('newHireFirstName', 'New Hire First Name', null, 'PreboardingEmployee', 'getFirstName', @data_group_id, 0, 0, @token_section),
   ('newHireMiddleName', 'New Hire Middle Name', null, 'PreboardingEmployee', 'getMiddleName', @data_group_id, 0, 0, @token_section),
   ('newHireLastName', 'New Hire Last Name', null, 'PreboardingEmployee', 'getLastName', @data_group_id, 0, 0, @token_section),
   ('newHireFullName', 'New Hire Full Name', null, 'PreboardingEmployee', 'getFullName', @data_group_id, 0, 0, @token_section),
   ('newHireEmail', 'New Hire Email', null, 'PreboardingEmployee', 'getEmail', @data_group_id, 0, 0, @token_section),
   ('newHireContactNumber', 'New Hire Contact Number', null, 'PreboardingEmployee', 'getContactNumber', @data_group_id, 0, 0, @token_section),
   ('newHireJobTitle', 'New Hire Job Title', null, 'PreboardingEmployee', 'getJobTitle', @data_group_id, 0, 0, @token_section);

INSERT INTO `ohrm_restricted_module_token`(`token_id`, `restricted_module`) SELECT id, @preboarding_module_id FROM ohrm_token WHERE entity_name = 'PreboardingEmployee';

INSERT INTO ohrm_config(property, `value`)
VALUES ('preboarding.initiation_window.days', '30'),
       ('preboarding.new_hire_token_validity_period.days', '14'),
       ('preboarding.request_upgrade_page_url', 'https://orangehrm.com/en/upgrade-to-preboarding');

SET @copy_preboarding_forms_to_employee_task_options :=
        (SELECT JSON_OBJECT(
                        'executorHelper', 'CopyFormRecordsToSingleRecordTabWizardSessionDataExecutionHelper'
                ));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `options`)
VALUES ('Copy Preboarding Forms to Employee', 'Copy Preboarding Forms to Employee', @pro_module_id, '',
        @copy_preboarding_forms_to_employee_task_options);

SET @copy_preboarding_task_options :=
        (SELECT JSON_OBJECT(
                    'executorHelper', 'CopyFormRecordsToMultipleRecordTabWizardSessionDataExecutionHelper'
                ));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `workflow`, `form_version_id`, `options`)
VALUES ('Copy Form data to Multiple tab', 'Copy Form data to Multiple tab', @pro_module_id,
        '', NULL, NULL, @copy_preboarding_task_options);


SET @core_module_id := (SELECT `id`
                        FROM `ohrm_module`
                        WHERE `name` = 'core');

SET @taskOptions := (SELECT JSON_OBJECT('executorHelper', 'TransferFormDataOnTaskStateChangeExecutionHelper'));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `options`)
VALUES ('Push form data to a configured API endpoint', 'Push form data to a configured API endpoint', @core_module_id,
        '', @taskOptions);