SET @talent_management_pillar_menu_id :=(SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`='Talent Management' AND `level` = 1);
SET @onboarding_module_menu_id = 16;
INSERT INTO ohrm_menu_item (`id`, `menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`, `image_name`) VALUES
(@onboarding_module_menu_id, 'Onboarding', NULL, @talent_management_pillar_menu_id, 2, 20, NULL, NULL, 1, 'onboarding.svg');

CREATE TABLE ohrm_job (id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, due_date DATE, status VARCHAR(64), location_id INT, created_date DATETIME, type VARCHAR(64), isarchived TINYINT, new_hires_ids TEXT, PRIMARY KEY(id)) 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)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
CREATE TABLE ohrm_task_type (id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, description TEXT , notified_before VARCHAR(64), created_by INT NOT NULL, job_type VARCHAR(64), is_generic TINYINT(1), owner_id INT NOT NULL, also_notify VARCHAR(255), 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 TEXT, due_date DATE, notified_before 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, also_notify VARCHAR(255), isarchived TINYINT NULL DEFAULT '1', new_hire_id INT, comment TEXT, PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

ALTER TABLE `ohrm_job` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` , `location_id` ) ;

ALTER TABLE `ohrm_job` ADD FOREIGN KEY ( `location_id` ) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `ohrm_job_owner` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `job_id` , `emp_number` ) ;

ALTER TABLE `ohrm_job_owner` ADD 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` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` , `job_id` , `new_hire_id` ) ;

ALTER TABLE `ohrm_task` ADD 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_comment` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` , `task_id` ) ;
ALTER TABLE `ohrm_task_comment` ADD FOREIGN KEY ( `task_id` ) REFERENCES `ohrm_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;


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`) VALUES('onboardingStatus', @module_id, 'onboardingStatus');
-- SET @onboardingStatus_screen_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 @onboarding_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Onboarding' AND `level` = 2 AND parent_id = @talent_management_pillar_menu_id);

-- INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES('Onboarding Status',@onboardingStatus_screen_id, @onboarding_menu_id, 2, 100, '', 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, @onboardingStatus_screen_id , '1', '1', '1', '1');

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Task Types',@viewTaskType_screen_id, @onboarding_menu_id, 3, 500, '', 1),
('Events',@viewJobs_screen_id, @onboarding_menu_id, 3, 100, '/reset/1', 1),
('My Events',@myJobs_screen_id, @onboarding_menu_id, 3, 300, '/reset/1', 1),
('Employee Tasks',@viewEmployeeTasks_screen_id, @onboarding_menu_id, 3, 200, '/reset/1', 1),
('My Tasks',@myTasks, @onboarding_menu_id, 3, 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_user_role_screen` (`id`, `user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (NULL, @admin_role_id, @myJobs_screen_id , '1', '1', '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, @myTasks , '1', '1', '1', '1');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('onboarding_job_manipulation', 'Onboarding - Event', 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_my_job_manipulation', 'On-boarding - My Events', 1, 1, 1, 1);
-- SET @onboarding_my_job_manipulation_id := (SELECT LAST_INSERT_ID());

-- INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
-- ('onboarding_task_manipulation', 'On-boarding - Manipulation of On-boarding Task', 1, 1, 1, 1);
-- SET @onboarding_task_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', 'Onboarding - Task', 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_my_task_manipulation', 'On-boarding - My Task', 1, 1, 1, 1);
-- SET @onboarding_my_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', 'Onboarding - Task Types', 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', 'Onboarding - Reports', 1, 0, 0, 0);
SET @onboarding_report_manipulation := (SELECT LAST_INSERT_ID());


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),
-- (@ohrmjob_owner_id, @onboarding_my_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, 1, 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, 1, 0, 0),
-- (@admin_role_id, @onboarding_task_manipulation_id, 1, 1, 1, 1, 0),
-- (@ohrmjob_owner_id, @onboarding_task_manipulation_id, 1, 1, 1, 1, 0),
-- (@ohrmtask_owner_id, @onboarding_task_manipulation_id, 1, 1, 1, 1, 0),
-- (@ohrmtask_owner_id, @onboarding_my_task_manipulation_id, 1, 1, 1, 1, 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);

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', 'Onboarding - Task', '1', '3', NULL, NULL, @onboarding_module_id, 6),
('7', 'onboardJob', 'Onboarding - 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', 'COMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETE', 'OHRMJOBOWNER', 'Mark Incomplete', 'INCOMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMTASKOWNER', 'Complete', 'COMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETE', 'OHRMTASKOWNER', 'Mark Incomplete', 'INCOMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'ADMIN', 'Complete', 'COMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETE', 'ADMIN', 'Mark Incomplete', 'INCOMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'INCOMPLETE', 'SUPERVISOR', 'Complete', 'COMPLETE', '', 0),
    (@onboarding_task_workflow_id, 'COMPLETE', 'SUPERVISOR', 'Mark 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);


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'onboarding');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('New Event Notification', 1, @notification_type_id, NULL, @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('New Event Notification Template', @notification_id, @notification_media_id, 'en_US', "New Event Created - _$$_receiver_$$_", '<p>Hi _$$_receiver_$$_,<br /><br />You are now the event owner for _$$_eventName_$$_, which is due on _$$_date_$$_.<br />Please check OrangeHRM for further details.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'onboarding');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('New Task Created Notification', 1, @notification_type_id, NULL, @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('New Task Created Notification Template', @notification_id, @notification_media_id, 'en_US', "New Tasks Notification - _$$_receiver_$$_", "<p>Hi _$$_receiver_$$_,<br /><br />New tasks were assigned to you.<br />_$$_pendingTasks_$$_<br /><br />Please check OrangeHRM for more details.<br />Thank you.<br /><br />This is an automated notification.</p>");


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Scheduled');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'onboarding');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Pending Task Notification', 1, @notification_type_id, 'DummyEmailProcessor', @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Pending Task Notification Template', @notification_id, @notification_media_id, 'en_US', "Pending Tasks Notification - _$$_receiver_$$_", "Hi _$$_receiver_$$_,<br/><br/>

Pending Tasks as of: _$$_date_$$_<br/><br/>
_$$_pendingTasks_$$_
<br/>
Thank you.<br/>
<br/>
This is an automated notification.");


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Scheduled');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'onboarding');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Reminder Task Notification', 1, @notification_type_id, 'DummyEmailProcessor', @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Reminder Task Notification Template', @notification_id, @notification_media_id, 'en_US', "Reminder:  _$$_task_$$_ for _$$_newhire_$$_", "<p>Hi,<br /><br />The following task is nearing its due-date<br /><br />Event: _$$_event_$$_<br />Task: _$$_pendingTask_$$_<br />Description: _$$_description_$$_<br />Task Owner: _$$_taskowner_$$_<br />Participant: _$$_newHire_$$_<br />Due Date: _$$_dueDate_$$_<br />Status: _$$_status_$$_<br />Priority: _$$_priority_$$_<br /><br />Thank you.<br /><br />This is an automated notification.</p>");

# 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');

INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Onboarding', @system_user_id, 8);
SET @onboarding_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Onboarding');
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
    );
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;

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
('Onboarding Notification', 'Onboarding Notification', 'Onboarding Notification', 'php symfony orangehrm:OnBoardingNotification', '0 5 * * *', '', 0,'executableTask', 0, null, 0),
('Onboarding Quick Notification', 'Onboarding Quick Notification', 'Onboarding Quick Notification', 'php symfony orangehrm:OnBoardingQuickNotification', '*/15 * * * *', '', 0,'executableTask', 0, null, 0);