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

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`, `image_name`) VALUES
('On/Offboarding', NULL, @onboarding_menu_id, 3, 600, NULL, NULL, 1, NULL);

CREATE TABLE ohrm_job (id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, due_date DATE, status VARCHAR(64), created_date DATETIME, type VARCHAR(64), isarchived TINYINT, `event_template_type_id` INT(11) NULL, `is_template` TINYINT(1) NOT NULL DEFAULT '0', PRIMARY KEY(id) USING BTREE) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
CREATE TABLE ohrm_job_owner (job_id INT, emp_number INT, PRIMARY KEY(job_id, emp_number)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
CREATE TABLE ohrm_job_task_type (job_id INT, task_type_id INT, PRIMARY KEY(job_id, task_type_id)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
CREATE TABLE ohrm_task_comment (id INT AUTO_INCREMENT, comment TEXT NOT NULL, created_date DATETIME, emp_number INT NOT NULL, task_id INT NOT NULL, PRIMARY KEY(`id` , `task_id`)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
CREATE TABLE ohrm_task_type (id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, description MEDIUMTEXT NOT NULL , notified_before VARCHAR(64), notified_after VARCHAR(64), created_by INT NOT NULL, job_type VARCHAR(64), is_generic TINYINT(1), owner_id INT NOT NULL, also_notify VARCHAR(255), owner_type VARCHAR(255), task_type VARCHAR(255), include_attachments TINYINT(1) DEFAULT 0 NOT NULL, is_attachment_mandatory TINYINT(1) DEFAULT 0 NOT NULL, is_attachment_sync_pim TINYINT(1) DEFAULT 0 NOT NULL, screen_to_sync INT(4) DEFAULT NULL, PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
CREATE TABLE ohrm_task (id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, description MEDIUMTEXT NOT NULL, due_date DATE, notified_before VARCHAR(64), notified_after VARCHAR(64), status VARCHAR(64), priority INT, created_date DATE, owner_num INT NOT NULL, job_id INT NOT NULL, effective_date DATE, completed_date DATE, completed_by VARCHAR(100), completed_by_id INT, also_notify VARCHAR(255), isarchived TINYINT NULL DEFAULT '1', new_hire_id INT, comment TEXT, include_attachments TINYINT(1) DEFAULT 0 NOT NULL, is_attachment_mandatory TINYINT(1) DEFAULT 0 NOT NULL, is_attachment_sync_pim TINYINT(1) DEFAULT 0 NOT NULL, screen_to_sync INT(4) DEFAULT NULL, PRIMARY KEY(`id` , `job_id` , `new_hire_id`)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

ALTER TABLE `ohrm_job_owner` ADD  CONSTRAINT `ohrm_job_owner_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `ohrm_job`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ohrm_job_owner` ADD FOREIGN KEY ( `emp_number` ) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `ohrm_job_task_type` ADD FOREIGN KEY ( `job_id` ) REFERENCES `ohrm_job` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `ohrm_job_task_type` ADD FOREIGN KEY ( `task_type_id` ) REFERENCES `ohrm_task_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;


ALTER TABLE `ohrm_task` ADD  CONSTRAINT `ohrm_task_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `ohrm_job`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ohrm_task` ADD FOREIGN KEY ( `new_hire_id` ) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE ohrm_task
    ADD FOREIGN KEY (`screen_to_sync`) REFERENCES ohrm_custom_field_screen(`id`) ON DELETE SET NULL;

ALTER TABLE `ohrm_task_comment` ADD FOREIGN KEY ( `task_id` ) REFERENCES `ohrm_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE ohrm_task_type
    ADD FOREIGN KEY (`screen_to_sync`) REFERENCES ohrm_custom_field_screen(`id`) ON DELETE SET NULL;

INSERT INTO `ohrm_user_role` (`name` ,`display_name` ,`is_assignable` ,`is_predefined`) VALUES ('OhrmJobOwner', 'Ohrm Job Owner', '0', '1');
SET @ohrmjob_owner_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role` (`name` ,`display_name` ,`is_assignable` ,`is_predefined`) VALUES ('OhrmTaskOwner', 'Ohrm Task Owner', '0', '1');
SET @ohrmtask_owner_id := (SELECT LAST_INSERT_ID());


SET @ess_role_id :=(SELECT `id` FROM `ohrm_user_role` WHERE `name`='ESS');
SET @admin_role_id :=(SELECT `id` FROM `ohrm_user_role` WHERE `name`='Admin');
SET @supervisor_role_id :=(SELECT `id` FROM `ohrm_user_role` WHERE `name`='Supervisor');

INSERT INTO `ohrm_module`(`name`, `status`) VALUES ('onboarding', '1');
SET @module_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES('viewTaskType', @module_id, 'viewTaskTypes', 'onboarding_viewTaskTypes');
SET @viewTaskType_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES('viewJobs', @module_id, 'viewJobs', 'onboarding_viewJobs');
SET @viewJobs_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES('myJobs', @module_id, 'viewMyJobs', 'onboarding_viewMyJobs');
SET @myJobs_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES('viewEmployeeTasks', @module_id, 'viewEmployeeTasks', 'onboarding_viewEmployeeTasks');
SET @viewEmployeeTasks_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES('myTasks', @module_id, 'viewMyTasks', 'onboarding_viewMyTasks');
SET @myTasks := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES('viewOnboardingReport', @module_id, 'viewOnboardingReport');
SET @view_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES('createJob', @module_id, 'createJob');
SET @create_job_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES('createOnboardingTask', @module_id, 'createOnboardingTask');
SET @create_onboarding_task_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES('defaultMenuView', @module_id, 'defaultMenuView', 'onboarding_defaultMenuView');
SET @default_view_screen_id := (SELECT LAST_INSERT_ID());

SET @onOffboarding_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'On/Offboarding' AND `level` = 3 AND parent_id = @onboarding_menu_id);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Configure Tasks',@viewTaskType_screen_id, @onOffboarding_menu_id, 4, 500, '', 1),
('Manage Events/Templates',@viewJobs_screen_id, @onOffboarding_menu_id, 4, 100, '/reset/1', 1),
('My Events',@myJobs_screen_id, @onOffboarding_menu_id, 4, 300, '/reset/1', 1),
('View Employee Tasks',@viewEmployeeTasks_screen_id, @onOffboarding_menu_id, 4, 200, '/reset/1', 1),
('My Tasks',@myTasks, @onOffboarding_menu_id, 4, 400, '/reset/1', 1);

INSERT INTO `ohrm_user_role_screen` (`id`, `user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(NULL, @admin_role_id, @create_job_screen_id , 1, 1, 1, 1),
(NULL, @ohrmjob_owner_id, @create_job_screen_id , 1, 1, 1, 1),
(NULL, @admin_role_id, @create_onboarding_task_screen_id , 1, 1, 1, 1),
(NULL, @ohrmjob_owner_id, @create_onboarding_task_screen_id , 1, 1, 1, 1),
(NULL, @ohrmtask_owner_id, @create_onboarding_task_screen_id , 1, 0, 1, 0),
(NULL, @admin_role_id, @viewTaskType_screen_id , 1, 1, 1, 1),
(NULL, @admin_role_id, @viewJobs_screen_id , 1, 1, 1, 1),
(NULL, @ohrmjob_owner_id, @myJobs_screen_id , 1, 1, 1, 1),
(NULL, @admin_role_id, @viewEmployeeTasks_screen_id , 1, 1, 1, 1),
(NULL, @ohrmjob_owner_id, @viewEmployeeTasks_screen_id , 1, 1, 1, 1),
(NULL, @ohrmtask_owner_id, @myTasks , 1, 1, 1, 1),
(NULL, @admin_role_id, @default_view_screen_id , 1, 1, 1, 1),
(NULL, @ohrmjob_owner_id, @default_view_screen_id , 1, 1, 1, 1),
(NULL, @ohrmtask_owner_id, @default_view_screen_id , 1, 1, 1, 1),
(NULL, @admin_role_id, @view_report_screen_id , 1, 0, 0, 0),
(NULL, @supervisor_role_id, @viewEmployeeTasks_screen_id , 1, 0, 1, 0),
(NULL, @supervisor_role_id, @create_onboarding_task_screen_id , 1, 0, 1, 0),
(NULL, @supervisor_role_id, @default_view_screen_id , 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('onboarding_job_manipulation', 'On/Offboarding - Manage Events/Templates', 1, 1, 1, 1);
SET @onboarding_job_manipulation_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('onboarding_employee_task_manipulation', 'On/Offboarding - View Employee Tasks', 1, 1, 1, 1);
SET @onboarding_employee_task_manipulation_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('onboarding_task_pool_manipulation', 'On/Offboarding - Configure Tasks', 1, 1, 1, 1);
SET @onboarding_task_pool_manipulation_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('onboarding_report_manipulation', 'On/Offboarding - Reports', 1, 0, 0, 0);
SET @onboarding_report_manipulation := (SELECT LAST_INSERT_ID());


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('onboarding_my_task_manipulation', 'On/Offboarding - My Tasks', 1, 1, 1, 1);
SET @onboarding_my_task_manipulation_id := (SELECT id from ohrm_data_group where `name` = 'onboarding_my_task_manipulation');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_role_id, @onboarding_job_manipulation_id, 1, 1, 1, 1, 0),
(@ohrmjob_owner_id, @onboarding_job_manipulation_id, 1, 0, 1, 0, 0),
(@admin_role_id, @onboarding_employee_task_manipulation_id, 1, 1, 1, 1, 0),
(@supervisor_role_id, @onboarding_employee_task_manipulation_id, 1, 0, 0, 0, 0),
(@ohrmjob_owner_id, @onboarding_employee_task_manipulation_id, 1, 1, 1, 1, 0),
(@ohrmtask_owner_id, @onboarding_employee_task_manipulation_id, 1, 0, 0, 0, 0),
(@admin_role_id, @onboarding_report_manipulation, 1, 0, 0, 0, 0),
(@admin_role_id, @onboarding_task_pool_manipulation_id, 1, 1, 1, 1, 0),
(@ohrmtask_owner_id, @onboarding_my_task_manipulation_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@onboarding_task_pool_manipulation_id,@viewTaskType_screen_id,1),
(@onboarding_task_pool_manipulation_id,@viewTaskType_screen_id,2),
(@onboarding_task_pool_manipulation_id,@viewTaskType_screen_id,3),
(@onboarding_task_pool_manipulation_id,@viewTaskType_screen_id,4),
(@onboarding_employee_task_manipulation_id,@create_onboarding_task_screen_id,1),
(@onboarding_employee_task_manipulation_id,@create_onboarding_task_screen_id,2),
(@onboarding_employee_task_manipulation_id,@create_onboarding_task_screen_id,3),
(@onboarding_employee_task_manipulation_id,@create_onboarding_task_screen_id,4),
(@onboarding_job_manipulation_id,@viewJobs_screen_id,1),
(@onboarding_job_manipulation_id,@viewJobs_screen_id,2),
(@onboarding_job_manipulation_id,@viewJobs_screen_id,3),
(@onboarding_job_manipulation_id,@viewJobs_screen_id,4),
(@onboarding_job_manipulation_id,@create_job_screen_id,1),
(@onboarding_job_manipulation_id,@create_job_screen_id,2),
(@onboarding_job_manipulation_id,@create_job_screen_id,3),
(@onboarding_job_manipulation_id,@create_job_screen_id,4),
(@onboarding_employee_task_manipulation_id,@viewEmployeeTasks_screen_id,1),
(@onboarding_employee_task_manipulation_id,@viewEmployeeTasks_screen_id,2),
(@onboarding_employee_task_manipulation_id,@viewEmployeeTasks_screen_id,3),
(@onboarding_employee_task_manipulation_id,@viewEmployeeTasks_screen_id,4),
(@onboarding_report_manipulation,@view_report_screen_id,1),
(@onboarding_task_pool_manipulation_id,@default_view_screen_id,1),
(@onboarding_job_manipulation_id,@default_view_screen_id,1),
(@onboarding_employee_task_manipulation_id,@default_view_screen_id,1),
(@onboarding_report_manipulation,@default_view_screen_id,1);


SET @onboarding_task_workflow_id := 6;
SET @onboarding_job_workflow_id := 7;

SET @onboarding_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='onboarding');
INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`) VALUES
('6', 'onboardTask', 'On/Offboarding - Task', '1', '3', NULL, NULL, @onboarding_module_id, 6),
('7', 'onboardJob', 'On/Offboarding - Event', '1', NULL, NULL, NULL, @onboarding_module_id, 7);

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMJOBOWNER', 'Complete', 'COMPLETED', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETED', 'OHRMJOBOWNER', 'Incomplete', 'INCOMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMTASKOWNER', 'Complete', 'COMPLETED', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETED', 'OHRMTASKOWNER', 'Incomplete', 'INCOMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'ADMIN', 'Complete', 'COMPLETED', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETED', 'ADMIN', 'Incomplete', 'INCOMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'SUPERVISOR', 'Complete', 'COMPLETED', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETED', 'SUPERVISOR', 'Incomplete', 'INCOMPLETE', '', 0);

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
    (@onboarding_job_workflow_id, 'CREATED', 'OhrmJobOwner', 'Activate', 'ACTIVATED', '', 0),
    (@onboarding_job_workflow_id, 'CREATED', 'ADMIN', 'Activate', 'ACTIVATED', '', 0);


UPDATE hs_hr_unique_id SET `last_id` = (SELECT MAX(id) FROM ohrm_workflow_state_machine)
WHERE `table_name` = 'ohrm_workflow_state_machine' AND `field_name` = 'id';


INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
(@module_id, @admin_role_id, 'onboarding/viewJobs', NULL, 100),
(@module_id, @supervisor_role_id, 'onboarding/viewEmployeeTasks', NULL, 50),
(@module_id, @ohrmjob_owner_id, 'onboarding/viewMyJobs', NULL, 25),
(@module_id, @ohrmtask_owner_id, 'onboarding/viewMyTasks', NULL, 10);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(1, @onboarding_job_manipulation_id),
(1, @onboarding_employee_task_manipulation_id),
(1, @onboarding_task_pool_manipulation_id),
(1, @onboarding_report_manipulation);

# OnboardingReports
SET @system_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'SYSTEM');
SET @system_user_id := (SELECT `id` FROM `ohrm_user` WHERE `user_role_id` = @system_user_role_id);

SET @employee_progress_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Employee_Progress_Report');
SET @event_progress_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Event_Progress_Report');
SET @employee_tasks_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Employee_Tasks_Report');
SET @employee_tasks_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Employee_Tasks_Report');
SET @dashboard_report_id := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'onboarding_dashboard_summary_1');

INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('On/Offboarding', @system_user_id, 8);
SET @onboarding_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'On/Offboarding');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`) VALUES (@onboarding_folder_id, @onboarding_menu_id );
UPDATE `ohrm_advanced_report` SET `folder_id`= @onboarding_folder_id WHERE `id` IN (
                                                                                    @employee_progress_report,
                                                                                    @event_progress_report,
                                                                                    @employee_tasks_report,
                                                                                    @dashboard_report_id
    );
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Event_Progress_Report", "filters": null})' WHERE `id` = @event_progress_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Employee_Progress_Report", "filters": null})' WHERE `id` = @employee_progress_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Employee_Tasks_Report", "filters": null})' WHERE `id` = @employee_tasks_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= '/#/onboarding/dashboard' WHERE `id` = @dashboard_report_id;

INSERT INTO `ohrm_symfony_task` (`name`, `short_description`, `long_description`, `command`, `schedule`, `observer_class`, `prevent_overlap`,`user_interface`,`status`,`initial_time_for_task_execution_verification`,`task_failure_notification`) VALUES
('On/Offboarding Notification', 'On/Offboarding Notification', 'On/Offboarding Notification', 'php symfony orangehrm:OnBoardingNotification', '0 5 * * *', '', 0,'executableTask', 0, null, 0),
('On/Offboarding Quick Notification', 'On/Offboarding Quick Notification', 'On/Offboarding Quick Notification', 'php symfony orangehrm:OnBoardingQuickNotification', '*/15 * * * *', '', 0,'executableTask', 0, null, 0);

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

ALTER TABLE `ohrm_job` ADD CONSTRAINT `ohrm_job_ibfk_1` FOREIGN KEY (`event_template_type_id`) REFERENCES `ohrm_event_template_type`(`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

INSERT INTO ohrm_event_template_type (  `name`, `is_active`) values
                                                                 ('Onboarding',1),
                                                                 ('Offboarding',1),
                                                                 ('Other',1);

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('pim_wizard_status_onboarding', '0') ;
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('days_to_onboarding', '15') ;
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('days_to_offboarding', '0') ;
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('onboarding.dashboad_report_time_period', 12);

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('onboarding.event_creation', 'NewOnboardingEventCreatedEventListener', '1', '1');

-- Attachment table
CREATE TABLE IF NOT EXISTS ohrm_onboarding_tasks_attachment (
                                                                `id`  INT AUTO_INCREMENT,
                                                                `task_id` INT(4) NOT NULL,
    `eattach_desc` VARCHAR(200) DEFAULT NULL,
    `eattach_filename` VARCHAR(255) DEFAULT NULL,
    `eattach_size` INT(4) DEFAULT '0',
    `eattach_attachment` MEDIUMBLOB,
    `eattach_type` VARCHAR(200) DEFAULT NULL,
    `attached_by` INT(4) DEFAULT NULL,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP NOT NULL DEFAULT now(),
    `is_synced_pim` tinyint(1) DEFAULT 0 NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_onboarding_tasks_attachment_ohrm_task` FOREIGN KEY (`task_id`)
    REFERENCES `ohrm_task` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_onboarding_pim_synced_attachments (attachment_id INT(4), emp_number INT(4), eattach_id INT, PRIMARY KEY(attachment_id, emp_number,eattach_id )) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
ALTER TABLE `ohrm_onboarding_pim_synced_attachments` ADD FOREIGN KEY ( `attachment_id` ) REFERENCES `ohrm_onboarding_tasks_attachment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ohrm_onboarding_pim_synced_attachments` ADD FOREIGN KEY ( `emp_number`, `eattach_id` ) REFERENCES `hs_hr_emp_attachment` (`emp_number`, `eattach_id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS ohrm_onboarding_template_location (
                                                                 job_id INT NOT NULL,
                                                                 location_id INT NOT NULL,
                                                                 PRIMARY KEY(job_id, location_id),
    foreign key (job_id) references ohrm_job(`id`) on delete cascade,
    foreign key (location_id) references  ohrm_location(`id`) on delete cascade
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_job_participant_employee (
                                                             job_id INT NOT NULL,
                                                             emp_number INT NOT NULL,
                                                             PRIMARY KEY(job_id, emp_number),
    foreign key (job_id) references ohrm_job(`id`) on delete cascade,
    foreign key (emp_number) references  hs_hr_employee(`emp_number`) on delete cascade
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

SET @onboarding_task_workflow_id := (SELECT `id` FROM `ohrm_workflow` WHERE `name`='onboardTask');

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
                                                                                                                                   (@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMJOBOWNER', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'COMPLETED', 'OHRMJOBOWNER', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMTASKOWNER', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'COMPLETED', 'OHRMTASKOWNER', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'INCOMPLETE', 'ADMIN','On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'COMPLETED', 'ADMIN', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'INCOMPLETE', 'SUPERVISOR', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'COMPLETED', 'SUPERVISOR', 'On Hold', 'ON HOLD', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'OHRMJOBOWNER', 'Incomplete', 'INCOMPLETE', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'OHRMJOBOWNER', 'Complete', 'COMPLETED', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'OHRMTASKOWNER', 'Incomplete', 'INCOMPLETE', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'OHRMTASKOWNER', 'Complete', 'COMPLETED', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'ADMIN','Incomplete', 'INCOMPLETE', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'ADMIN', 'Complete', 'COMPLETED', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'SUPERVISOR', 'Incomplete', 'INCOMPLETE', '', 0),
                                                                                                                                   (@onboarding_task_workflow_id, 'ON HOLD', 'SUPERVISOR', 'Complete', 'COMPLETED', '', 0);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id, can_read, can_create, can_update, can_delete) VALUES (3, @onboarding_employee_task_manipulation_id, 1, 0, 0, 0);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id, can_read, can_create, can_update, can_delete) VALUES (1, @onboarding_my_task_manipulation_id, 1, 0, 1, 0);


-- New Data group for on/off boarding task upload

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('onboarding_task_import_export', 'On/Offboarding - Import or Export Tasks', 1, 1, NULL, NULL);
SET @onboarding_task_import_export_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'onboarding_task_import_export');

SET @admin_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 (@admin_role_id, @onboarding_task_import_export_data_group_id, 1, 1, NULL, NULL, 0);

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

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES (@admin_role_type_id, @onboarding_task_import_export_data_group_id);

-- Task Type Attachment table

CREATE TABLE IF NOT EXISTS ohrm_onboarding_task_type_attachment (
                                                                    `id`  INT AUTO_INCREMENT,
                                                                    `task_type_id` INT(4) NOT NULL,
    `eattach_desc` VARCHAR(200) DEFAULT NULL,
    `eattach_filename` VARCHAR(255) DEFAULT NULL,
    `eattach_size` INT(4) DEFAULT '0',
    `eattach_attachment` MEDIUMBLOB,
    `eattach_type` VARCHAR(200) DEFAULT NULL,
    `attached_by` INT(4) DEFAULT NULL,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_onboarding_task_type_attachment_ohrm_task_type` FOREIGN KEY (`task_type_id`)
    REFERENCES `ohrm_task_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

--  Default Task Attachment Table

CREATE TABLE IF NOT EXISTS ohrm_onboarding_tasks_default_attachment (
                                                                        `id`  INT AUTO_INCREMENT,
                                                                        `task_id` INT(4) NOT NULL,
    `eattach_desc` VARCHAR(200) DEFAULT NULL,
    `eattach_filename` VARCHAR(255) DEFAULT NULL,
    `eattach_size` INT(4) DEFAULT '0',
    `eattach_attachment` MEDIUMBLOB,
    `eattach_type` VARCHAR(200) DEFAULT NULL,
    `attached_by` INT(4) DEFAULT NULL,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_onboarding_tasks_default_attachment_ohrm_task` FOREIGN KEY (`task_id`)
    REFERENCES `ohrm_task` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

UPDATE `ohrm_module` SET data_group_prefix = 'On/Offboarding' WHERE `name` = 'onboarding';