-- START SCHEMA CREATION
CREATE TABLE IF NOT EXISTS ohrm_online_course (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
validity_length_value varchar(30),
validity_length_type  varchar(10),
duration_value varchar(30),
duration_type varchar(10),
video_id text,
is_deleted INT(2) DEFAULT 0,
created_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
added_by varchar(350),
auto_renewable INT(2) DEFAULT 1 NOT NULL,
PRIMARY KEY(id)
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_online_course_subunit (
course_id INT,
sub_unit_id INT,
PRIMARY KEY(course_id, sub_unit_id),
foreign key (course_id) references  ohrm_online_course(`id`) on delete cascade ,
foreign key (sub_unit_id) references  ohrm_subunit(`id`) on delete cascade
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_employee_job_role` (
      `jobrole_id` int(4) not null,
      `employee_id` int(4) NOT NULL,
      PRIMARY KEY (`jobrole_id`, `employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_online_course_job_title (
course_id INT,
job_title_id INT,
PRIMARY KEY(course_id, job_title_id),
foreign key (course_id) references  ohrm_online_course(`id`) on delete cascade ,
foreign key (job_title_id) references  ohrm_job_title(`id`) on delete cascade
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_online_course_employee (
`id` INT(11) NOT NULL AUTO_INCREMENT,
course_id INT,
emp_number INT,
status INT NOT NULL  DEFAULT 0,
assigned_date DATE DEFAULT NULL,
pass_mark DOUBLE,
completion_date DATE DEFAULT NULL,
PRIMARY KEY(id),
foreign key (course_id) references  ohrm_online_course(`id`) on delete cascade,
foreign key (emp_number) references  hs_hr_employee(`emp_number`) on delete cascade
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_online_course_certificate` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`),
foreign key (course_id) references  ohrm_online_course(`id`) on delete cascade
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_online_assessment` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`pass_grade` VARCHAR(10),
`time_limit_value` varchar(10) DEFAULT '0',
`time_limit_type`  varchar(10),
`attempts` INT(7) DEFAULT 0,
`question_form_id` INT (11),
`added_by` varchar(350),
PRIMARY KEY(`id`),
FOREIGN KEY(`question_form_id`) REFERENCES `ohrm_question_form`(`id`) ON DELETE SET NULL
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_online_course_assessment` (
`course_id` INT,
`assessment_id` INT(11),
PRIMARY KEY(course_id, assessment_id),
FOREIGN KEY (`course_id`) REFERENCES `ohrm_online_course`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`assessment_id`) REFERENCES `ohrm_online_assessment`(`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;


CREATE TABLE IF NOT EXISTS `ohrm_online_trainee_assessment` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`assessment_id` INT(11),
`course_emp_id` INT(11),
PRIMARY KEY(`id`),
FOREIGN KEY (`assessment_id`) REFERENCES `ohrm_online_assessment`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`course_emp_id`) REFERENCES `ohrm_online_course_employee`(`id`) ON DELETE SET NULL
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_online_trainee_assessment_attempt` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`trainee_assessment_id` INT(11),
`attempt` INT(11),
`result` decimal(6,2) default NULL,
`is_passed` INT(7) DEFAULT 0,
`is_ongoing` INT(2) DEFAULT 0,
`start_time` TIMESTAMP NULL DEFAULT NULL,
`end_time` TIMESTAMP NULL DEFAULT NULL,
`completed_date` date default NULL,
PRIMARY KEY(`id`),
FOREIGN KEY (`trainee_assessment_id`) REFERENCES `ohrm_online_trainee_assessment`(`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_online_trainee_answer` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`answer_id` INT(11),
`trainee_assessment_attempt_id` INT(11),
PRIMARY KEY(id),
FOREIGN KEY (`answer_id`) REFERENCES `ohrm_answer`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`trainee_assessment_attempt_id`) REFERENCES `ohrm_online_trainee_assessment_attempt`(`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `ohrm_online_course_link` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`type` INT(2),
`link_id` text,
`description` VARCHAR(255) DEFAULT NULL,
`resource_order` INT(11),
`course_id` INT(11) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (course_id) REFERENCES  ohrm_online_course(`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_online_employee_course_certificate (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`emp_number` INT(11),
`course_emp_id` INT(11),
`certificate_id` INT(11),
`trainee_assessment_id` INT(11),
`name` VARCHAR(255) NOT NULL,
`is_valid` INT(2),
`issued_date` date,
`expiry_date` date,
PRIMARY KEY(id),
foreign key (emp_number) references  hs_hr_employee(`emp_number`) on delete cascade,
foreign key (certificate_id) references  ohrm_online_course_certificate(`id`) on delete cascade,
foreign key(trainee_assessment_id) references `ohrm_online_trainee_assessment`(`id`) ON DELETE SET NULL,
foreign key(course_emp_id) references `ohrm_online_course_employee`(`id`) ON DELETE SET NULL
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE `ohrm_online_course_certificate_template`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`is_disabled` SMALLINT DEFAULT 0,
`body` mediumtext,
`processor_class` VARCHAR(255),
`style_sheet` mediumtext,
`page_orientation` VARCHAR(255),
`page_size` VARCHAR(255),
`margin_top` INT DEFAULT 0,
`margin_bottom` INT DEFAULT 0,
`margin_left` INT DEFAULT 0,
`margin_right` INT DEFAULT 0,
PRIMARY KEY(id)) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

-- END OF SCHEMA CREATION

-- DATA GROUP AND SCREEN PERMISSIONS

SET @training_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'training');

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

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

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('online_courses', 'Online Assessment Courses - Courses', 1, 1, 1, 1),
('online_assessments', 'Online Assessment Courses - Assessments', 1, 1, 1, 1),
('online_my_courses','Online Assessment Courses - My Assessments',1,1,1,1),
('online_course_certificates','Online Assessment Courses - Employee Course Certificates',1,1,1,1),
('training_configuration','Training - Configuration',1,0,1,0);

SET @online_courses_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'online_courses');
SET @online_assessments_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'online_assessments');
SET @online_my_courses_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'online_my_courses');
SET @training_configuration_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'training_configuration');


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, @online_courses_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @online_assessments_data_group_id, 1, 1, 1, 1, 0),
(@ess_role_id, @online_my_courses_data_group_id, 1, 1, 1, 1, 0),
(@config_admin_role_id, @training_configuration_data_group_id, 1, 0, 1, 0, 0),
(@admin_role_id, @training_configuration_data_group_id, 1, 0, 0, 0, 0),
(@supervisor_role_id, @training_configuration_data_group_id, 1, 0, 0, 0, 0),
(@ess_role_id, @training_configuration_data_group_id, 1, 0, 0, 0, 1);

-- ohrm_user_role_type_data_group
INSERT INTO `ohrm_user_role_type_data_group` ( `user_role_type_id`, `data_group_id`) VALUES
(@admin_role_type_id, @online_courses_data_group_id),
(@admin_role_type_id, @online_assessments_data_group_id);

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_role_type_id, @training_configuration_data_group_id, 1, 0, 0, 0),
(@ess_role_type_id, @training_configuration_data_group_id, 1, 0, 0, 0),
(@supervisor_role_type_id, @training_configuration_data_group_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
(@ess_role_type_id, @online_my_courses_data_group_id,1,1,0,1);

-- adding new screens

INSERT INTO `ohrm_screen` ( `name`, `module_id`, `action_url`) VALUES
( 'Online Course List',@training_module_id , 'viewOnlineCourseList'),
( 'Add Online Assessment',@training_module_id , 'addOnlineAssessment'),
( 'Online Assessment List',@training_module_id , 'viewOnlineAssessment'),
( 'Online My Courses',@training_module_id , 'viewOnlineCourseEmployee'),
( 'Training Configuration', @training_module_id, 'trainingConfiguration');

SET @online_viewcourse_screen := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewOnlineCourseList');
SET @online_add_assessment_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'addOnlineAssessment');
SET @online_view_assessment_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewOnlineAssessment');
SET @online_view_my_courses_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewOnlineCourseEmployee');
SET @training_configuration_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'trainingConfiguration');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@online_courses_data_group_id, @online_viewcourse_screen, 1),
(@online_assessments_data_group_id, @online_add_assessment_screen_id, 1),
(@online_assessments_data_group_id, @online_view_assessment_screen_id, 1);


INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`,`self`) VALUES
(@online_my_courses_data_group_id, @online_view_my_courses_screen_id, 4,1);

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(@admin_role_id, @online_viewcourse_screen, '1', '1', '1', '1'),
(@admin_role_id, @online_add_assessment_screen_id, '1', '1', '1', '1'),
(@admin_role_id, @online_view_assessment_screen_id, '1', '1', '1', '1'),
(@ess_role_id, @online_view_my_courses_screen_id, '1', '1', '1', '1'),
(@config_admin_role_id, @training_configuration_screen_id, '1', '1', '1', '1');

SET @culture_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Culture' AND parent_id IS NULL AND level = 1);
SET @training_menu_item_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Training' AND parent_id = @culture_pillar_menu_id AND level = 2);


-- adding new menu items
INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`, `has_multiple_records`) VALUES
('Online Assessment Courses', NULL, NULL, @training_menu_item_id, 3, 200, '', NULL, 1, NULL);

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

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`, `has_multiple_records`) VALUES
('Courses & Assessments', NULL, @online_viewcourse_screen, @online_assignments_menu_id, 4, 130, '', NULL, 1, NULL),
('My Assessments', NULL, @online_view_my_courses_screen_id, @online_assignments_menu_id, 4, 120, '', NULL, 1, NULL);

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`, `has_multiple_records`) VALUES
('Configuration', NULL, @training_configuration_screen_id, @training_menu_item_id, 3, 400, '', 'training.config', 1, NULL);

--
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
('pim.save_job_record', 'EmployeeJobRoleChangeAndAssignOnlineCoursesEventListner', 1, 50),
('training.update_online_course', 'OnlineTrainingEmailListener', 1, 50),
('training.update_online_course', 'OnlineTrainingCourseUpdateListener', 1, 50),
('training.reassign_online_course', 'EmployeeReAssignedOnlineCoursesEventListner', 1, 52),
('online_assign_course_for_employee_event_listener', 'EmployeeAssignedOnlineCoursesEventListner', 1, 52);



-- Assessment Questionaire Scripts

INSERT INTO `ohrm_questionnaire` (`questionnaire_name`, `is_grouping_allowed`) VALUES
('online_training', '0');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('online_assessment_form_default_header', '<span style="color: #5d5d5d; font-size: 13pt;"><ul><li style="margin-top:1px;">Please answer all questions in this assessment.</li><li style="margin-top:1px;">Any questions left unanswered will be taken as a wrongly answered question.</li><li style="margin-top:1px;">All questions are Multiple Choice Questions (MCQs) with one or many correct answers for each question.</li><li style="margin-top:1px;">Once you have completed the examination please submit your answers by clicking "Submit Test" at the bottom of the screen.</li><li style="margin-top:1px;">The Timer will show you how much time you have remaining in the examination.</li><li style="margin-top:1px;">Note that if you are unable to submit the answers within the given time frame the system will automatically submit it once the timer runs out.</li></span>'),
('online_assessment_form_default_footer', ''),
('online_assessment_form_calculator', 'OnlineBasicQuestionFormMarkCalculator');

-- certificates Expiry notification

SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');
SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @supervisor_user_role_type :=(SELECT `id` FROM `ohrm_user_role_type` WHERE `name`='Supervisor');


-- adding examination completed email notification scripts
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
('online_assessment_completed_event_listener', 'OnlineAssessmentEventListner', 1, 53);

SET @online_employee_course_report_id := (SELECT `id` FROM `ohrm_advanced_report` WHERE `unique_name` = 'ONLINE_Training_Employee_Course');

SET @report_module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'training');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
( 'View ONLINE Employee Course Report', @report_module_id, 'viewOnlineEmployeeCourseReport');

SET @online_employee_course_report_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewOnlineEmployeeCourseReport');
SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('online_employee_course_report', 'Online Assessment Courses - Online Assessment Course Employee Analytics Report', 1, 0, 0, 0);

SET @online_employee_course_report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'online_employee_course_report');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_id, @online_employee_course_report_data_group_id, 1, 0, 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
(@admin_id, @online_employee_course_report_data_group_id, 1, 0, 0, 0);

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

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_id, @online_employee_course_report_screen_id, 1, 0, 0, 0);


-- COURSE ANALYTICS REPORT START
SET @course_analytics_report_id := (SELECT `id` FROM `ohrm_advanced_report` WHERE `unique_name` = 'Course_Analytics_Report');
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
( 'View Course Analytics Report', @report_module_id, 'viewCourseAnalyticsReport');

SET @course_analytics_report_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewCourseAnalyticsReport');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('course_analytics_report', 'Online Assessment Courses - Online Assessment Analytics Report', 1, 0, 0, 0);

SET @course_analytics_report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'course_analytics_report');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_id, @course_analytics_report_data_group_id, 1, 0, 0, 0, 0),
(@supervisor_user_role, @course_analytics_report_data_group_id, 1, 0, 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
(@admin_id, @course_analytics_report_data_group_id, 1, 0, 0, 0),
(@supervisor_user_role_type, @course_analytics_report_data_group_id, 1, 0, 0, 0);

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

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_id, @course_analytics_report_screen_id, 1, 0, 0, 0),
(@supervisor_user_role, @course_analytics_report_screen_id, 1, 0, 0, 0);

-- COURSE ANALYTICS REPORT END

-- QUALIFICATIONS CERTIFICATE DATAGROUP START

SET @qualification_certificate_data_group_id = (SELECT `id` FROM ohrm_data_group WHERE `name` = 'qualification_certificates');

-- QUALIFICATIONS CERTIFICATE DATAGROUP END

SELECT id INTO @viewQualificationsScreenid FROM ohrm_screen WHERE `action_url` = 'viewQualifications';

SET @pimModuleId := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'pim');


SET @leftMenuScreenId := (SELECT id FROM ohrm_screen
    WHERE action_url = 'viewQualifications' AND module_id = @pimModuleId);

SELECT id INTO @leftMenuItemId  FROM  `ohrm_menu_item` WHERE `menu_title` = 'Qualifications' AND `menu_category` = 'pim_left_menu';

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES
(@leftMenuItemId, @qualification_certificate_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 (@admin_role_id, @qualification_certificate_data_group_id, '1', '1', '1', '1', '0'),
       (@supervisor_role_id, @qualification_certificate_data_group_id, '1', '1', '1', '1', '0'),
       (@ess_role_id, @qualification_certificate_data_group_id, '1', '1', '1', '1', '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_role_type_id, @qualification_certificate_data_group_id, 1, 0, 0, 1),
       (@supervisor_role_type_id, @qualification_certificate_data_group_id, 1, 0, 0, 1),
       (@ess_role_type_id, @qualification_certificate_data_group_id, 1, 0, 0, 1);

-- 635_to_643_upgrade

DELETE FROM ohrm_event_listener WHERE  listener_class = 'OnlineTrainingPurgeEmployeeListener';
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
    ( 'maintenance.employee_purge_end', 'OnlineTrainingPurgeEmployeeListener', '1', '90');

UPDATE ohrm_data_group SET description = REPLACE(description, 'Tranining', 'Training') WHERE description LIKE '%Online Tranining%';

ALTER TABLE `ohrm_online_course_employee` ADD COLUMN `is_deleted` INT(2) DEFAULT 0;

DELETE FROM ohrm_notification WHERE name LIKE 'Online Training%';
DELETE FROM ohrm_notification WHERE name LIKE 'Online Assessment Courses%';

SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');
SET @schedule_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Scheduled');
SET @other_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Other');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @training_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'training');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
    ('Online Assessment Courses - Course Certificate Expiry Notification', 1, @date_based_notification_type, 'OnlineCourseCertificateExpiryNotification', NULL, NULL,@training_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Course Certificate Expiry Notification sent to Subscriber',
     'Course Certificate Expiry Notification',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi,

     The course certificate for course NAME will expire in DAYSLEFT.

     Please retake the course to renew it.

     Details below.

     Course : NAME
     Expiry Date : EXPIRY_DATE

     Thank you.

     This is an automated notification. </pre>
     '
    );

-- START COURSE ASSIGNMENT

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Course Assignment', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"OnlineCourseNewAssignmentEmailProcessor"}', NULL, NULL,@training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Assignment Notification sent to Subscriber',
     'Notification: Online Assessment Course Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     Following Employees have been assigned to the course of %courseName%.

     %courceEmployees%

     Thank you.

     This is an automated notification. </pre>'
    );
SET @subscriber_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Supervisors');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @subscriber_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Assignment Notification sent to Employee',
     'Notification: Online Assessment Course Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     You have been assigned to the following course %courseName%.

     You can access the course by clicking %courseURL%.

     Please complete the course to receive your certification.

     Thank you.

     This is an automated notification. </pre>.
     ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @employee_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- END OF COURSE ASSIGNMENT

-- START JOB RECORD CHANGES NOTIFICATION

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Course Assignment On Job Record Change', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"OnlineCourseAssignmentOnJobChangeEmailProcessor"}', NULL, NULL,@training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Assignment Notification sent to Subscriber',
     'Notification: Online Assessment Course Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     %performerName% has changed the job record of %employeeName%, the related courses are now assigned.

     Thank you.

     This is an automated notification. </pre>'
    );

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Assignment Notification sent to Supervisor',
     'Notification: Online Assessment Course Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     %performerName% has changed the job record of %employeeName%, the related courses are now assigned.

     Please ensure that this person completes all the assigned courses.


     Thank you.

     This is an automated notification. </pre>.
     ');
SET @supervisor_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Supervisors');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @supervisor_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Assignment Notification sent to Employee',
     'Notification: Online Assessment Course Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     %performerName% has changed your job record, the related courses are now assigned.

     Please ensure that you complete all the assigned courses.

     Thank you.

     This is an automated notification. </pre>.
     ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @employee_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- END  JOB RECORD CHANGES NOTIFICATION

-- START COURSE CONTENT CHANGE

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Course Content Change', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"OnlineCourseContentChangeEmailProcessor"}', NULL, NULL,@training_module_id);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
    (@notification_id, @email_media);
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Content Changed Notification sent to Subscriber',
     'Notification: Online Assessment Course Content Changed',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     Please note that course certificate for %courseName% has been invalidated due to content changes.

     Thank you.

     This is an automated notification. </pre>'
    );

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Content Changed Notification sent to Employee',
     'Notification: Online Assessment Course Content Changed',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     Please note that your course certificate for %courseName% has been invalidated. You will have to retake this course in order to receive a new certificate.

     You can use the link below to directly access this course.

     %courseUrl%

     Thank you.

     This is an automated notification. </pre>.
     ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id,hidden) VALUES (@notification_id,1);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @employee_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- END  COURSE CONTENT CHANGE

-- START ASSESSMENT COMPLETION

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Assessment Completion', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"OnlineCourseAssessmentCompletionEmailProcessor"}', NULL, NULL,@training_module_id);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
    (@notification_id, @email_media);
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Courses - Assessment Completion Notification sent to Subscriber',
     'Notification: Online Assessment Completion',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     %employeeName% has completed the assessment for %courseName%

     %results_details%

     Thank you.

     This is an automated notification. </pre>'
    );

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Courses - Assessment Completion Notification sent to Employee',
     'Notification: Online Assessment Courses - Assessment Completion',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     %results_details%

     %certificate_url%

     Thank you.

     This is an automated notification. </pre>.
     ');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id,hidden) VALUES (@notification_id,1);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- END  ASSESSMENT COMPLETION

-- START COURSE RE-ASSIGNMENT

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Course Re-Assignment', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"OnlineCourseNewReAssignmentEmailProcessor"}', NULL, NULL,@training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Re-Assignment Notification sent to Subscriber',
     'Notification: Online Assessment Course Re-Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     Following Employee(s) have been re-assigned to the course(s).

     %courceEmployees%

     Thank you.

     This is an automated notification. </pre>'
    );

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Re-Assignment Notification sent to Supervisor',
     'Notification: Online Assessment Course Re-Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     Following Employee(s) have been re-assigned to the course(s).

     %courceEmployees%

     Thank you.

     This is an automated notification. </pre>.
     ');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Supervisors');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id,@template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Re-Assignment Notification sent to Employee',
     'Notification: Online Assessment Course Re-Assignment',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     You have been re-assigned to the following course(s).

       %courseNameList%

     Please complete the course(s) to receive your new certification.

     Thank you.

     This is an automated notification. </pre>
     ');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media,'{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- END OF COURSE RE-ASSIGNMENT

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Course Examination Results Notification', 1, @schedule_based_notification_type, 'CourseExaminationResultsNotification', NULL, NULL,@training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Examination Results Notification sent to Subscriber',
     'Notification: Online Assessment Course Examination Results week of %date%',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi,

  Following employees have completed the courses recently.

  %course_results_table%

  Thank you.

  This is an automated notification. </pre>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`,  `subject`, `body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Online Assessment Course Examination Results Notification sent to Supervisor',
     'Notification: Online Assessment Course Examination Results week of %date%',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %RecipientName%,

     Following employees have completed the courses recently.

     %course_results_table%

     Thank you.

     This is an automated notification. </pre>
     ');
SET @template_id := LAST_INSERT_ID();

-- Schedule on Every Friday of the week `schedule_dow`=6
INSERT INTO `ohrm_notification_subscription`
(notification_id, schedule_dow, workflow_item_id, hidden) VALUES
    (@notification_id, 6, NULL, 1);
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, address_part, parameters, type, name) VALUES
    (@email_media, 'to', '{"prevent_delete":true}', 'Custom','Supervisor');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id,template_id) SELECT
                                                                                                        s.id, @subscriber_id, @template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- END OF COURSE EXAMINATION RESULTS

-- My Courses view permission issue fix START
SET @online_my_courses_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'online_my_courses');
UPDATE `ohrm_data_group_screen` SET `permission` = 1 WHERE `data_group_id` = @online_my_courses_data_group_id;

SET @my_courses_screen_id := (SELECT `screen_id` FROM `ohrm_data_group_screen` WHERE `data_group_id` = @online_my_courses_data_group_id);
UPDATE `ohrm_user_role_screen` SET `can_read` = 1 WHERE `screen_id` = @my_courses_screen_id;
-- My Courses view permission issue fix END

-- 643_to_646_upgrade

CREATE TABLE IF NOT EXISTS ohrm_online_course_location (
                                                           course_id INT,
                                                           location_id INT,
                                                           PRIMARY KEY(course_id, location_id),
    foreign key (course_id) references  ohrm_online_course(`id`) on delete cascade ,
    foreign key (location_id) references  ohrm_location(`id`) on delete cascade
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

-- 646_to_648_upgrade

-- Invalidate employee course certificates based on the certificate expiry date

INSERT INTO `ohrm_symfony_task` (`name`, `short_description`, `long_description`, `command`, `schedule`, `observer_class`, `prevent_overlap`, `status`, `user_interface`) VALUES
    ('Invalidate Employee Course Certificates', 'This Task Will Invalidate Employee Course Certificates Based on the Certificate Expiry Date', 'This Task Will Invalidate Employee Course Certificates Based on the Certificate Expiry Date.', 'php symfony orangehrm:OnlineExpiredCourseCertificateInvalidate', '0 1 * * *', '', 1, 0, 'executableTask');
SET @task_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_symfony_task_inputs` (`task_id`, `name`, `label`, `description`, `active`, `required`, `position`, `widget_creator`, `type`, `data_type`) VALUES
    (@task_id, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('online_training_show_summary', 0),
('otm.course_assessment_acknowledgement_required', 0);

INSERT INTO `ohrm_online_course_certificate_template` (`id`, `name`, `is_disabled`, `body`, `processor_class`, `style_sheet`, `page_orientation`, `page_size`, `margin_top`, `margin_bottom`, `margin_left`, `margin_right`)
VALUES (NULL, 'Default', '0',
        '<div class="box">
        <div id = "topDiv">
        <div id = "blockDiv">
        <span style="font-size:50px; font-weight:bold">Certificate of Completion</span>
        <br><br>
        <span style="font-size:25px"><i>This is to certify that</i></span>
        <br><br>
        <span style="font-size:30px"><b>%fullName%</b></span>
        <br/>
        <br/>
        <span style="font-size:25px"><i>has successfully completed the course</i></span>
        <br/><br/><span style="font-size:30px">%courseName%</span>
        <br/><br/>
        <span style="font-size:20px">
        </b></span><br/><br/><br/><br/>
        <span style="font-size:25px"><i>Award Date</i></span><br>
        <span style="font-size:30px">%issuedDate%</span>
        <br/><br/>
        <img src= "https://st.depositphotos.com/1575949/1859/v/950/depositphotos_18591401-stock-illustration-red-wax-seal-ribbon.jpg" height="100" width="100" style=" position: absolute; clip: rect(0px,150px,100px,0px); left:600px;"></div>
        </div>
        </div>'
           , 'CourseTemplateDefaultProcessor',
        '#footer {
            display: none;
        }

        #topDiv {
            width: 915px;
            height: 665px;
            padding: 20px;
            text-align: center;
            border: 10px solid #787878
        }

        #blockDiv {
            width: 850px;
            height: 600px;
            padding: 20px;
            text-align: center;
            border: 5px solid #787878
        }','landscape', 'letter', 5, 5, 5, 5);

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('online_training_certificate_template_name', 'Default');

-- 6514_feature_enahancment

ALTER TABLE `ohrm_online_assessment` ADD is_random_question_enabled tinyint(1) not null default 0;
ALTER TABLE `ohrm_online_assessment` ADD random_question_limit int(7) not null default 0;

-- 6515_feature_enhancement_module_changes

SET @training_module_id := (SELECT `id`
                            FROM `ohrm_module`
                            WHERE `name` = 'training');

INSERT INTO ohrm_module (name, status)
VALUES ('onlinetraining', (SELECT IFNULL((SELECT `status` FROM `ohrm_module` m WHERE `name` = 'training'), 0) AS status));

SET @online_training_module_id := (SELECT `id`
                                   FROM `ohrm_module`
                                   WHERE `name` = 'onlinetraining');

UPDATE ohrm_screen
SET module_id = @online_training_module_id
WHERE module_id = @training_module_id
  AND action_url IN ('viewOnlineCourseList', 'addOnlineAssessment', 'viewOnlineAssessment', 'viewOnlineCourseEmployee', 'viewOnlineEmployeeCourseReport',
                     'viewCourseAnalyticsReport');

UPDATE ohrm_notification
SET module_id = @online_training_module_id
WHERE module_id = @training_module_id
  AND name IN
      ('Online Assessment Courses - Course Certificate Expiry Notification', 'Online Assessment Courses - Course Assignment',
       'Online Assessment Courses - Course Assignment On Job Record Change', 'Online Assessment Courses - Course Content Change', 'Online Assessment Courses - Assessment Completion',
       'Online Assessment Courses - Course Re-Assignment', 'Online Assessment Courses - Course Examination Results Notification');

-- 6515_feature_enhancement

INSERT INTO `ohrm_config`
VALUES ('onlinetraining_show_jobrole_field', ''),
       ('onlinetraining_show_advanced_course_details', 'yes'),
       ('onlinetraining_actor_data_group_permissions',
        '{"CourseReviewer":{"9":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"10":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"11":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"12":{"canRead":true,"canCreate":false,"canUpdate":true,"canDelete":false},"13":{"canRead":true,"canCreate":false,"canUpdate":true,"canDelete":false},"14":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"15":{"canRead":true,"canCreate":false,"canUpdate":true,"canDelete":false}},"CourseApprover":{"9":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"10":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"11":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"12":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"13":{"canRead":true,"canCreate":false,"canUpdate":true,"canDelete":false},"14":{"canRead":true,"canCreate":true,"canUpdate":true,"canDelete":true},"15":{"canRead":true,"canCreate":false,"canUpdate":true,"canDelete":false}}}');

CREATE TABLE ohrm_online_trainee_course_link_view_acknowledgement
(
    id                   INT AUTO_INCREMENT,
    course_emp_id        INT      NOT NULL,
    course_link_id       INT      NOT NULL,
    viewed_at            DATETIME NULL,
    view_acknowledged_at DATETIME NULL,
    CONSTRAINT trainee_course_link_view_ack_course_emp_id_fk
        FOREIGN KEY (course_emp_id) REFERENCES ohrm_online_course_employee (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT trainee_course_link_view_ack_course_link_id_fk
        FOREIGN KEY (course_link_id) REFERENCES ohrm_online_course_link (id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (id)
) DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

ALTER TABLE `ohrm_online_course_employee`
    ADD `due_date` DATE NULL DEFAULT NULL AFTER `assigned_date`;

ALTER TABLE `ohrm_online_course`
    ADD `is_expired`                        TINYINT(1) NULL     DEFAULT '0' AFTER `is_deleted`,
    ADD `approval_required`                 TINYINT(1) NULL     DEFAULT '0' AFTER `is_expired`,
    ADD `assessment_required`               TINYINT(1) NULL     DEFAULT '0' AFTER `approval_required`,
    ADD `resource_acknowledgement_required` TINYINT(1) NOT NULL DEFAULT '0' AFTER `assessment_required`;

ALTER TABLE `ohrm_online_course_link` CHANGE `description` `description` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

-- COURSE MATERIAL ANALYTICS REPORT START
SET @admin_id := (SELECT id
                  FROM ohrm_user_role
                  where name = 'Admin');
SET @supervisor_user_role := (SELECT id
                              FROM ohrm_user_role
                              WHERE `name` = 'Supervisor');
SET @supervisor_user_role_type := (SELECT `id`
                                   FROM `ohrm_user_role_type`
                                   WHERE `name` = 'Supervisor');
SET @report_module_id := (SELECT `id`
                          FROM `ohrm_module`
                          WHERE name = 'onlinetraining');
SET @course_analytics_report_id := (SELECT `id`
                                    FROM `ohrm_advanced_report`
                                    WHERE `unique_name` = 'Course_Material_Analytics_Report');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('View Course Material Analytics Report', @report_module_id, 'viewCourseMaterialAnalyticsReport');

SET @course_analytics_report_screen_id := (SELECT id
                                           from ohrm_screen
                                           WHERE action_url = 'viewCourseMaterialAnalyticsReport');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('course_material_analytics_report', 'Online Assessment Courses - Online Assessment Course Material Analytics Report', 1, 0, 0, 0);

SET @course_analytics_report_data_group_id := (SELECT id
                                               from ohrm_data_group
                                               WHERE name = 'course_material_analytics_report');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
VALUES (@admin_id, @course_analytics_report_data_group_id, 1, 0, 0, 0, 0),
       (@supervisor_user_role, @course_analytics_report_data_group_id, 1, 0, 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 (@admin_id, @course_analytics_report_data_group_id, 1, 0, 0, 0),
       (@supervisor_user_role_type, @course_analytics_report_data_group_id, 1, 0, 0, 0);

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

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
VALUES (@admin_id, @course_analytics_report_screen_id, 1, 0, 0, 0),
       (@supervisor_user_role, @course_analytics_report_screen_id, 1, 0, 0, 0);

-- COURSE MATERIAL ANALYTICS REPORT END

SET @max_event_listener_position = (SELECT MAX(`position`)
                                    FROM ohrm_event_listener);

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`)
VALUES ('onlinetraining.event.create_online_course', 'CreateOnlineCoursesEventListener', 1, @max_event_listener_position + 1),
       ('onlinetraining.event.add_course_reviewer', 'ReviewerAssignedOnlineCoursesEventListener', 1, @max_event_listener_position + 2),
       ('onlinetraining.event.delete_course_reviewers', 'ReviewerRemoveOnlineCoursesEventListener', 1, @max_event_listener_position + 3),
       ('onlinetraining.event.add_course_approver', 'ApproverAssignedOnlineCoursesEventListener', 1, @max_event_listener_position + 4),
       ('onlinetraining.event.delete_course_approvers', 'ApproverRemoveOnlineCoursesEventListener', 1, @max_event_listener_position + 5),
       ('advanced_workflow.request_process_actor_state_change', 'ActorStateChangeOnlineCoursesEventListener', 1,
        @max_event_listener_position + 6),
       ('advanced_workflow.request_state_change', 'CourseStateChangeOnlineCoursesEventListener', 1,
        @max_event_listener_position + 7);

CREATE TABLE ohrm_online_course_link_acknowledgement_comment
(
    id                         INT AUTO_INCREMENT,
    process_actor_id           INT        NOT NULL,
    acknowledged_at            DATETIME   NULL,
    acknowledged               TINYINT(1) NOT NULL DEFAULT '0',
    assessment_acknowledged_at DATETIME   NULL,
    assessment_acknowledged    TINYINT(1) NOT NULL DEFAULT '0',
    submitted                  TINYINT(1) NOT NULL DEFAULT '0',
    comment                    TEXT,
    CONSTRAINT online_course_link_ack_actor_id_fk
        FOREIGN KEY (process_actor_id) REFERENCES ohrm_advanced_workflow_request_process_actor (id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (id)
) DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

ALTER TABLE `ohrm_online_course_employee`
    ADD `assign_notification_sent` TINYINT NOT NULL DEFAULT '0' AFTER `completion_date`;

ALTER TABLE `ohrm_online_course_employee`
    CHANGE `completion_date` `completion_date_time` DATETIME NULL DEFAULT NULL;

-- All courses list for ESS - Data groups menu items screens etc.

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('online_course_all_available_course_list', 'Online Assessment Courses - All Online Assessment Courses Report', 1, 0, 0, 0);

SET @otm_module_id := (SELECT `id`
                       FROM `ohrm_module`
                       WHERE `name` = 'onlinetraining');

SET @online_course_all_available_course_list_data_group_id := (SELECT `id`
                                                               FROM `ohrm_data_group`
                                                               WHERE `name` = 'online_course_all_available_course_list');

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

SET @ess_role_type_id := (SELECT `id`
                          FROM `ohrm_user_role_type`
                          WHERE `name` = 'ESS');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('Online Course - All Available Course List', @otm_module_id, 'viewOnlineCourseAllAvailableCourses');

SET @online_all_available_courses_screen_id := (SELECT `id`
                                                FROM `ohrm_screen`
                                                WHERE `action_url` = 'viewOnlineCourseAllAvailableCourses');

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

INSERT INTO `ohrm_user_role_data_group` (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self)
VALUES (@ess_role_id, @online_course_all_available_course_list_data_group_id, 1, 0, 0, 0, 0),
       (@admin_id, @online_course_all_available_course_list_data_group_id, 1, 0, 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 (@admin_id, @online_course_all_available_course_list_data_group_id, 1, 0, 0, 0);

INSERT INTO `ohrm_default_permitted_screen` (`user_role_type_id`, `screen_id`)
    VALUE (@ess_role_type_id, @online_all_available_courses_screen_id);

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES (@ess_role_id, @online_all_available_courses_screen_id, 1, 0, 0, 0),
       (@admin_id, @online_all_available_courses_screen_id, 1, 0, 0, 0);


CREATE TABLE IF NOT EXISTS ohrm_online_course_custom_field_value
(
    id                 INT AUTO_INCREMENT,
    course_id          INT NOT NULL,
    custom_field_id    INT NOT NULL,
    custom_field_value VARCHAR(1000),
    PRIMARY KEY (id),
    foreign key (course_id) references ohrm_online_course (`id`) on delete cascade,
    foreign key (custom_field_id) references ohrm_custom_field (`id`) on delete cascade
    ) DEFAULT CHARACTER SET UTF8
    ENGINE = INNODB;

-- 6515_feature_enhancement_auth_and_workflow

SET @otm_module_id := (SELECT `id`
                       FROM `ohrm_module`
                       WHERE `name` = 'onlinetraining');

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

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

INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`, `is_editable`, `deletable`)
VALUES ('CourseReviewer', 'Online Course Reviewer', 0, 1, 0, 0),
       ('CourseApprover', 'Online Course Approver', 0, 1, 0, 0);

SET @next_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 ('ONLINE_COURSE', 'Online Course', 'Online Assessment Course', 1, 2, NULL, NULL, @otm_module_id, @next_wf_order + 1),
       ('ONLINE_COURSE_PROCESS_REVIEW', 'Online Course Review Process Workflow', 'Online Course Review Process Workflow', NULL, 2, NULL, NULL, @otm_module_id,
        @next_wf_order + 2),
       ('ONLINE_COURSE_PROCESS_APPROVE', 'Online Course Approve Process Workflow', 'Online Course Approve Process Workflow', NULL, 2, NULL, NULL,
        @otm_module_id, @next_wf_order + 3),
       ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'Online Course Review Actor Workflow', 'Online Course Review Actor Workflow', NULL, 2, NULL, NULL,
        @otm_module_id, @next_wf_order + 4),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'Online Course Approve Actor Workflow', 'Online Course Approve Actor Workflow', NULL, 2, NULL, NULL,
        @otm_module_id, @next_wf_order + 5);

-- Main Course entity workflow state machine
INSERT INTO ohrm_workflow_state_machine (workflow, state, role, action, resulting_state)
VALUES ('ONLINE_COURSE', 'INITIAL', 'Admin', 'SAVE', 'CREATED'),
       ('ONLINE_COURSE', 'CREATED', 'Admin', 'SAVE', 'CREATED'),
       ('ONLINE_COURSE', 'CREATED', 'Admin', 'SUBMIT', 'PENDING REVIEW'),
       ('ONLINE_COURSE', 'PENDING REVIEW', 'SYSTEM', 'COMPLETE REVIEW PROCESS', 'PENDING APPROVAL'),
       ('ONLINE_COURSE', 'PENDING APPROVAL', 'SYSTEM', 'COMPLETE APPROVAL PROCESS', 'APPROVED'),
       ('ONLINE_COURSE', 'APPROVED', 'Admin', 'PUBLISH', 'PUBLISHED'),
       ('ONLINE_COURSE', 'PUBLISHED', 'Admin', 'PUBLISH', 'PUBLISHED'),
       ('ONLINE_COURSE', 'PUBLISHED', 'Admin', 'SAVE', 'PUBLISHED'),
       ('ONLINE_COURSE', 'PENDING APPROVAL', 'SYSTEM', 'REJECT', 'REJECTED'),
       ('ONLINE_COURSE', 'REJECTED', 'Admin', 'SAVE', 'REJECTED'),
       ('ONLINE_COURSE', 'REJECTED', 'Admin', 'SUBMIT', 'PENDING REVIEW'),
       ('ONLINE_COURSE', 'SAVED', 'Admin', 'SAVE', 'SAVED'),
       ('ONLINE_COURSE', 'SAVED', 'Admin', 'PUBLISH', 'PUBLISHED');

-- Course review process workflow state machine
INSERT INTO ohrm_workflow_state_machine (workflow, state, role, action, resulting_state)
VALUES ('ONLINE_COURSE_PROCESS_REVIEW', 'INITIAL', 'SYSTEM', 'INITIATE', 'IN PROGRESS'),
       ('ONLINE_COURSE_PROCESS_REVIEW', 'IN PROGRESS', 'SYSTEM', 'COMPLETE REVIEW', 'COMPLETED'),
       ('ONLINE_COURSE_PROCESS_REVIEW', 'COMPLETED', 'SYSTEM', 'ROLLBACK', 'INITIAL');

-- Course approval process workflow state machine
INSERT INTO ohrm_workflow_state_machine (workflow, state, role, action, resulting_state)
VALUES ('ONLINE_COURSE_PROCESS_APPROVE', 'INITIAL', 'SYSTEM', 'INITIATE', 'IN PROGRESS'),
       ('ONLINE_COURSE_PROCESS_APPROVE', 'IN PROGRESS', 'SYSTEM', 'COMPLETE APPROVAL', 'COMPLETED'),
       ('ONLINE_COURSE_PROCESS_APPROVE', 'IN PROGRESS', 'SYSTEM', 'REJECT', 'INITIAL');

-- Course review process actor workflow state machine
INSERT INTO ohrm_workflow_state_machine (workflow, state, role, action, resulting_state)
VALUES ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'INITIAL', 'CourseReviewer', 'READ', 'INITIAL'),
       ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'INITIAL', 'SYSTEM', 'INITIATE', 'INITIATED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'INITIATED', 'CourseReviewer', 'SAVE', 'INITIATED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'INITIATED', 'CourseReviewer', 'COMPLETE REVIEW', 'REVIEW COMPLETED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'REVIEW COMPLETED', 'CourseReviewer', 'READ', 'REVIEW COMPLETED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'REVIEW COMPLETED', 'SYSTEM', 'ROLLBACK', 'INITIAL');

-- Course approval process actor workflow state machine
INSERT INTO ohrm_workflow_state_machine (workflow, state, role, action, resulting_state)
VALUES ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'PENDING', 'CourseApprover', 'READ', 'PENDING'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'PENDING', 'SYSTEM', 'INITIATE', 'INITIATED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'PENDING', 'CourseApprover', 'SAVE', 'PENDING'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'INITIATED', 'CourseApprover', 'SAVE', 'INITIATED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'INITIATED', 'CourseApprover', 'APPROVE', 'APPROVED'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'INITIATED', 'CourseApprover', 'REJECT', 'PENDING'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'INITIATED', 'SYSTEM', 'ROLLBACK', 'PENDING'),
       ('ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'APPROVED', 'CourseApprover', 'READ', 'APPROVED');

INSERT INTO ohrm_advanced_workflow_entity (name, workflow_id, description, options)
VALUES ('OnlineCourse', 'ONLINE_COURSE', 'Support for Approve/Review processes for Online Course entity', '');

INSERT INTO ohrm_advanced_workflow_entity_status (entity, status_id, name, description, `sequence`, options)
VALUES ('OnlineCourse', 9, 'INITIAL', 'Course is yet to be created', 1, ''),
       ('OnlineCourse', 10, 'CREATED', 'Course is created', 2, ''),
       ('OnlineCourse', 11, 'PENDING REVIEW', 'Course is under review process.', 3, ''),
       ('OnlineCourse', 12, 'PENDING APPROVAL', 'Course is under approval process.', 4, ''),
       ('OnlineCourse', 13, 'APPROVED', 'Course is approved', 5, ''),
       ('OnlineCourse', 14, 'REJECTED', 'Course is rejected', 6, ''),
       ('OnlineCourse', 15, 'PUBLISHED', 'Course is published', 7, ''),
       ('OnlineCourse', 16, 'SAVED', 'Course is saved', 8, '');

INSERT INTO ohrm_advanced_workflow_entity_process (entity, name, workflow_id, description, options)
VALUES ('OnlineCourse', 'Review', 'ONLINE_COURSE_PROCESS_REVIEW', 'Review process for Online Course entity', ''),
       ('OnlineCourse', 'Approve', 'ONLINE_COURSE_PROCESS_APPROVE', 'Approve process for Online Course entity', '');

SET @review_process_id := (SELECT id
                           FROM ohrm_advanced_workflow_entity_process
                           WHERE entity = 'OnlineCourse'
                             AND name = 'Review');

SET @approve_process_id := (SELECT id
                            FROM ohrm_advanced_workflow_entity_process
                            WHERE entity = 'OnlineCourse'
                              AND name = 'Approve');

INSERT INTO ohrm_advanced_workflow_entity_process_status (entity_process_id, status_id, name, description, `sequence`, options)
VALUES (@review_process_id, 5, 'INITIAL', 'Initial state - before course creation', 1, ''),
       (@review_process_id, 6, 'INITIATED', 'Course created. No reviewer has started reviewing', 2, ''),
       (@review_process_id, 7, 'IN PROGRESS', 'Course is under review process. At least one reviewer has completed reviewing', 3, ''),
       (@review_process_id, 8, 'COMPLETED', 'Course is reviewed by all the reviewers', 4, ''),
       (@approve_process_id, 9, 'INITIAL', 'Initial state - before course creation', 5, ''),
       (@approve_process_id, 10, 'INITIATED', 'Course created. No approver has started approving', 6, ''),
       (@approve_process_id, 11, 'IN PROGRESS', 'Course is under approval process. At least one approver has approved', 7, ''),
       (@approve_process_id, 12, 'COMPLETED', 'Course is approved by all the approvers', 8, '');

INSERT INTO ohrm_advanced_workflow_entity_process_actor_type (entity_process_id, name, user_role, workflow_id, processor_class, description)
VALUES (@review_process_id, 'CourseReviewer', 'CourseReviewer', 'ONLINE_COURSE_PROCESS_ACTOR_REVIEWER', 'AdvancedWorkflowEmployeeActorTypeToEmployeeMapper',
        'Online course reviewer employees'),
       (@approve_process_id, 'CourseApprover', 'CourseApprover', 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER', 'AdvancedWorkflowEmployeeActorTypeToEmployeeMapper',
        'Online course reviewer employees');

SET @reviewer_actor_type_id := (SELECT id
                                FROM ohrm_advanced_workflow_entity_process_actor_type
                                WHERE entity_process_id = @review_process_id
                                  AND name = 'CourseReviewer');

SET @approver_actor_type_id := (SELECT id
                                FROM ohrm_advanced_workflow_entity_process_actor_type
                                WHERE entity_process_id = @approve_process_id
                                  AND name = 'CourseApprover');

INSERT INTO ohrm_advanced_workflow_entity_process_actor_type_status (actor_type_id, status_id, name, description, `sequence`, options)
VALUES (@reviewer_actor_type_id, 4, 'INITIAL', 'Initial state', 1, ''),
       (@reviewer_actor_type_id, 5, 'INITIATED', 'Review process is enabled', 2, ''),
       (@reviewer_actor_type_id, 6, 'REVIEW COMPLETED', 'Review completed', 3, ''),
       (@approver_actor_type_id, 7, 'PENDING', 'Initial state - Cannot approve yet.', 4, ''),
       (@approver_actor_type_id, 8, 'INITIATED', 'Approval process is enabled. All reviewers have completed reviewing', 5, ''),
       (@approver_actor_type_id, 9, 'APPROVED', 'Approved', 6, '');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('online_course_workflow', 'Online Assessment Courses - Course Approve/Review Workflow', 1, 1, 1, 1);

SET @course_workflow_data_group_id := (SELECT `id`
                                       FROM `ohrm_data_group`
                                       WHERE `name` = 'online_course_workflow');

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, @course_workflow_data_group_id, 1, 1, 1, 1, 0);

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

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('Online Course Workflow', @otm_module_id, 'onlineCourseManageWorkflow');

SET @online_course_manage_wf_screen := (SELECT `id`
                                        FROM `ohrm_screen`
                                        WHERE `action_url` = 'onlineCourseManageWorkflow');

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

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

ALTER TABLE `ohrm_online_course`
    ADD `workflow_request_id` INT(11) NULL AFTER `resource_acknowledgement_required`;

ALTER TABLE `ohrm_online_course`
    ADD CONSTRAINT `ohrm_online_course_ibfk1` FOREIGN KEY (`workflow_request_id`) REFERENCES `ohrm_advanced_workflow_request` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Configuring Course Reviewer and Approver user role
SET @course_reviewer_user_role_id := (SELECT `id`
                                      FROM ohrm_user_role
                                      WHERE `name` = 'CourseReviewer');

SET @course_approver_user_role_id := (SELECT `id`
                                      FROM ohrm_user_role
                                      WHERE `name` = 'CourseApprover');

SET @course_list_screen := (SELECT `id`
                            FROM `ohrm_screen`
                            WHERE `action_url` = 'viewOnlineCourseList');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
VALUES (@course_reviewer_user_role_id, @course_list_screen, 1, 1, 1, 1),
       (@course_approver_user_role_id, @course_list_screen, 1, 1, 1, 1);

SET @online_courses_data_group_id := (SELECT `id`
                                      FROM `ohrm_data_group`
                                      WHERE `name` = 'online_courses');
SET @online_assessments_data_group_id := (SELECT `id`
                                          FROM `ohrm_data_group`
                                          WHERE `name` = 'online_assessments');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
VALUES (@course_reviewer_user_role_id, @online_courses_data_group_id, 1, 0, 0, 0, 0),
       (@course_reviewer_user_role_id, @online_assessments_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_courses_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_assessments_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('online_course_resources', 'Online Assessment Courses - Course Resources', 1, 1, 1, 1),
       ('online_course_employees', 'Online Assessment Courses - Course Employees', 1, 1, 1, 1),
       ('online_course_resource_review', 'Online Assessment Courses - Review Resources', 1, 0, 1, 0),
       ('online_course_assessment_review', 'Online Assessment Courses - Review Assessment', 1, 0, 1, 0),
       ('online_course_my_comments', 'Online Assessment Courses - Course My Comments', 1, 1, 1, 1),
       ('online_course_reviewer_comments', 'Online Assessment Courses - Course Reviewer Comments', 1, 0, 0, 0),
       ('online_course_approver_comments', 'Online Assessment Courses - Course Approver Comments', 1, 0, 0, 0),
       ('online_course_status_details', 'Online Assessment Courses - Course Status Details', 1, 0, 0, 0),
       ('online_course_audit_trail', 'Online Assessment Courses - Audit Trail', 1, 0, 0, 0);

SET @online_course_resources_data_group_id := (SELECT `id`
                                               FROM `ohrm_data_group`
                                               WHERE `name` = 'online_course_resources');
SET @online_course_employees_data_group_id := (SELECT `id`
                                               FROM `ohrm_data_group`
                                               WHERE `name` = 'online_course_employees');
SET @online_course_resource_review_data_group_id := (SELECT `id`
                                                     FROM `ohrm_data_group`
                                                     WHERE `name` = 'online_course_resource_review');
SET @online_course_assessment_review_data_group_id := (SELECT `id`
                                                     FROM `ohrm_data_group`
                                                     WHERE `name` = 'online_course_assessment_review');

SET @online_course_my_comments_data_group_id := (SELECT `id`
                                                 FROM `ohrm_data_group`
                                                 WHERE `name` = 'online_course_my_comments');

SET @online_course_reviewer_comments_data_group_id := (SELECT `id`
                                                       FROM `ohrm_data_group`
                                                       WHERE `name` = 'online_course_reviewer_comments');

SET @online_course_approver_comments_data_group_id := (SELECT `id`
                                                       FROM `ohrm_data_group`
                                                       WHERE `name` = 'online_course_approver_comments');

SET @online_course_status_details_data_group_id := (SELECT `id`
                                                    FROM `ohrm_data_group`
                                                    WHERE `name` = 'online_course_status_details');

SET @online_course_audit_trail_data_group_id := (SELECT `id`
                                                 FROM `ohrm_data_group`
                                                 WHERE `name` = 'online_course_audit_trail');

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, @online_course_resources_data_group_id, 1, 1, 1, 1, 0),
       (@admin_role_id, @online_course_employees_data_group_id, 1, 1, 1, 1, 0),
       (@admin_role_id, @online_course_status_details_data_group_id, 1, 0, 0, 0, 0),
       (@admin_role_id, @online_course_reviewer_comments_data_group_id, 1, 0, 0, 0, 0),
       (@admin_role_id, @online_course_approver_comments_data_group_id, 1, 0, 0, 0, 0),
       (@admin_role_id, @online_course_audit_trail_data_group_id, 1, 0, 0, 0, 0),
       (@course_reviewer_user_role_id, @online_course_resources_data_group_id, 1, 0, 0, 0, 0),
       (@course_reviewer_user_role_id, @online_course_employees_data_group_id, 1, 0, 0, 0, 0),
       (@course_reviewer_user_role_id, @online_course_resource_review_data_group_id, 1, 0, 1, 0, 0),
       (@course_reviewer_user_role_id, @online_course_assessment_review_data_group_id, 1, 0, 1, 0, 0),
       (@course_reviewer_user_role_id, @online_course_my_comments_data_group_id, 1, 1, 1, 0, 0),
       (@course_approver_user_role_id, @online_course_resources_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_course_resource_review_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_course_employees_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_course_assessment_review_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_course_my_comments_data_group_id, 1, 1, 1, 0, 0),
       (@course_approver_user_role_id, @online_course_reviewer_comments_data_group_id, 1, 0, 0, 0, 0),
       (@course_approver_user_role_id, @online_course_approver_comments_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`)
VALUES (@admin_role_type_id, @online_course_resources_data_group_id),
       (@admin_role_type_id, @online_course_employees_data_group_id),
       (@admin_role_type_id, @online_course_status_details_data_group_id),
       (@admin_role_type_id, @online_course_reviewer_comments_data_group_id),
       (@admin_role_type_id, @online_course_approver_comments_data_group_id);

-- Updating the self parameter to 1 for ESS for online_my_courses data group.
SET @online_my_courses_data_group_id := (SELECT `id`
                                         FROM `ohrm_data_group`
                                         WHERE `name` = 'online_my_courses');

SET @ess_role_type_id := (SELECT `id`
                          FROM `ohrm_user_role_type`
                          WHERE `name` = 'ESS');


UPDATE `ohrm_user_role_data_group`
SET self = 1
WHERE user_role_id IN (SELECT `id` FROM `ohrm_user_role` WHERE `type_id` = @ess_role_type_id)
  AND data_group_id = @online_my_courses_data_group_id;

-- 6515_feature_enhancement_workflow_dependency

-- TC submits a newly created course. Initiate the review process and initiate the reviewers

-- 1) Review process should be initiated
SET @tc_submit_course_state_id := (SELECT id
                                   FROM ohrm_workflow_state_machine
                                   WHERE workflow = 'ONLINE_COURSE'
                                     AND state = 'CREATED'
                                     AND role = 'Admin'
                                     AND action = 'SUBMIT');

SET @system_initiates_review_process_state_id := (SELECT id
                                                  FROM ohrm_workflow_state_machine
                                                  WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                                    AND state = 'INITIAL'
                                                    AND role = 'SYSTEM'
                                                    AND action = 'INITIATE');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@tc_submit_course_state_id, @system_initiates_review_process_state_id, 'AdvancedWorkflowRequestToProcessStateChangeTrigger');

-- 2) Reviewer actors should be initiated

SET @system_initiates_review_process_state_id := (SELECT id
                                                  FROM ohrm_workflow_state_machine
                                                  WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                                    AND state = 'INITIAL'
                                                    AND role = 'SYSTEM'
                                                    AND action = 'INITIATE');

SET @system_initiates_reviewer_actors_state_id := (SELECT id
                                                   FROM ohrm_workflow_state_machine
                                                   WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_REVIEWER'
                                                     AND state = 'INITIAL'
                                                     AND role = 'SYSTEM'
                                                     AND action = 'INITIATE');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@system_initiates_review_process_state_id, @system_initiates_reviewer_actors_state_id, 'AdvancedWorkflowProcessToActorStateChangeTrigger');


-- Reviewer completes the review. Should conditionally update the status of the Review Process, Approval, Approvers and Process Course.

-- 1) Proceed the Review Process from In Progress to Completed
SET @reviewer_completes_review_state_id := (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_REVIEWER'
                                              AND state = 'INITIATED'
                                              AND role = 'CourseReviewer'
                                              AND action = 'COMPLETE REVIEW');

SET @review_process_completes_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'COMPLETE REVIEW');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@reviewer_completes_review_state_id, @review_process_completes_state_id, 'AdvancedWorkflowActorToProcessStateChangeTrigger');

-- 2) Proceed the Approval process from Initial to In Progress
SET @review_process_completes_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'COMPLETE REVIEW');

SET @approval_process_initiates_state_id := (SELECT id
                                             FROM ohrm_workflow_state_machine
                                             WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                               AND state = 'INITIAL'
                                               AND role = 'SYSTEM'
                                               AND action = 'INITIATE');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@review_process_completes_state_id, @approval_process_initiates_state_id, 'AdvancedWorkflowProcessToProcessStateChangeTrigger');

-- 3) Proceed the Approvers from Initial to In Progress

SET @approval_process_initiates_state_id := (SELECT id
                                             FROM ohrm_workflow_state_machine
                                             WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                               AND state = 'INITIAL'
                                               AND role = 'SYSTEM'
                                               AND action = 'INITIATE');

SET @system_initiate_approver_actors_state_id := (SELECT id
                                                  FROM ohrm_workflow_state_machine
                                                  WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER'
                                                    AND state = 'PENDING'
                                                    AND role = 'SYSTEM'
                                                    AND action = 'INITIATE');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`, `options`)
VALUES (@approval_process_initiates_state_id, @system_initiate_approver_actors_state_id, 'AdvancedWorkflowProcessToActorStateChangeTrigger',
        '{"immediateNextLevelActorOnly":true}');

-- 4) Proceed the Course from Pending Review to Pending Approval
SET @review_process_completes_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'COMPLETE REVIEW');

SET @course_to_pending_approval_state_id := (SELECT id
                                             FROM ohrm_workflow_state_machine
                                             WHERE workflow = 'ONLINE_COURSE'
                                               AND state = 'PENDING REVIEW'
                                               AND role = 'SYSTEM'
                                               AND action = 'COMPLETE REVIEW PROCESS');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@review_process_completes_state_id, @course_to_pending_approval_state_id, 'AdvancedWorkflowProcessToRequestStateChangeTrigger');

-- Approver approves. Should conditionally update the status of the next Approver, Approval Process and the Course.


-- 1) Proceed the next level approver from Initial to Initiated
SET @approver_approves_state_id := (SELECT id
                                    FROM ohrm_workflow_state_machine
                                    WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER'
                                      AND state = 'INITIATED'
                                      AND role = 'CourseApprover'
                                      AND action = 'APPROVE');

SET @next_level_approver_initiates_state_id := (SELECT id
                                                FROM ohrm_workflow_state_machine
                                                WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER'
                                                  AND state = 'PENDING'
                                                  AND role = 'SYSTEM'
                                                  AND action = 'INITIATE');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`, `options`)
VALUES (@approver_approves_state_id, @next_level_approver_initiates_state_id, 'AdvancedWorkflowActorToActorStateChangeTrigger',
        '{"immediateNextLevelActorOnly":true}');


-- 2) Proceed the Approval process from In Progress to Completed
SET @approver_approves_state_id := (SELECT id
                                    FROM ohrm_workflow_state_machine
                                    WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER'
                                      AND state = 'INITIATED'
                                      AND role = 'CourseApprover'
                                      AND action = 'APPROVE');

SET @approval_process_competes_state_id := (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                              AND state = 'IN PROGRESS'
                                              AND role = 'SYSTEM'
                                              AND action = 'COMPLETE APPROVAL');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@approver_approves_state_id, @approval_process_competes_state_id, 'AdvancedWorkflowActorToProcessStateChangeTrigger');

-- 3) Proceed the Course from Pending Approval to Approved
SET @approve_process_completes_state_id := (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                              AND state = 'IN PROGRESS'
                                              AND role = 'SYSTEM'
                                              AND action = 'COMPLETE APPROVAL');

SET @course_to_approved_state_id := (SELECT id
                                     FROM ohrm_workflow_state_machine
                                     WHERE workflow = 'ONLINE_COURSE'
                                       AND state = 'PENDING APPROVAL'
                                       AND role = 'SYSTEM'
                                       AND action = 'COMPLETE APPROVAL PROCESS');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@approve_process_completes_state_id, @course_to_approved_state_id, 'AdvancedWorkflowProcessToRequestStateChangeTrigger');

-- Any approver Rejects. Should update the status of approval process, review process, reviewers, approvers (revert any ongoing approvers to initial stage) and the course.

-- 1) Proceed the Approval Process from In Progress to Initial
SET @approver_rejects_state_id := (SELECT id
                                   FROM ohrm_workflow_state_machine
                                   WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER'
                                     AND state = 'INITIATED'
                                     AND role = 'CourseApprover'
                                     AND action = 'REJECT');

SET @approval_process_rejects_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'REJECT');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`, `options`)
VALUES (@approver_rejects_state_id, @approval_process_rejects_state_id, 'AdvancedWorkflowActorToProcessStateChangeTrigger',
        '{"validateSameStateForAllActors":false}');

-- 2) Proceed the Review process from Completed to Initial

SET @approval_process_rejects_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'REJECT');

SET @review_process_to_initial_state_id := (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                              AND state = 'COMPLETED'
                                              AND role = 'SYSTEM'
                                              AND action = 'ROLLBACK');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@approval_process_rejects_state_id, @review_process_to_initial_state_id, 'AdvancedWorkflowProcessToProcessStateChangeTrigger');

-- 3) Proceed the Reviewers from Initial to In Progress

SET @review_process_to_initial_state_id := (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                              AND state = 'COMPLETED'
                                              AND role = 'SYSTEM'
                                              AND action = 'ROLLBACK');

SET @reviewers_to_initial_state_id := (SELECT id
                                       FROM ohrm_workflow_state_machine
                                       WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_REVIEWER'
                                         AND state = 'REVIEW COMPLETED'
                                         AND role = 'SYSTEM'
                                         AND action = 'ROLLBACK');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@review_process_to_initial_state_id, @reviewers_to_initial_state_id, 'AdvancedWorkflowProcessToActorStateChangeTrigger');

-- 4) Revert the Initiated Approvers from Initiated to Initial

SET @approval_process_rejects_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'REJECT');

SET @approvers_to_initial_state_id := (SELECT id
                                       FROM ohrm_workflow_state_machine
                                       WHERE workflow = 'ONLINE_COURSE_PROCESS_ACTOR_APPROVER'
                                         AND state = 'INITIATED'
                                         AND role = 'SYSTEM'
                                         AND action = 'ROLLBACK');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@approval_process_rejects_state_id, @approvers_to_initial_state_id, 'AdvancedWorkflowProcessToActorStateChangeTrigger');

-- 5) Proceed the Course from Pending Approval to Created
SET @approval_process_rejects_state_id := (SELECT id
                                           FROM ohrm_workflow_state_machine
                                           WHERE workflow = 'ONLINE_COURSE_PROCESS_APPROVE'
                                             AND state = 'IN PROGRESS'
                                             AND role = 'SYSTEM'
                                             AND action = 'REJECT');

SET @course_to_rejected_state_id := (SELECT id
                                     FROM ohrm_workflow_state_machine
                                     WHERE workflow = 'ONLINE_COURSE'
                                       AND state = 'PENDING APPROVAL'
                                       AND role = 'SYSTEM'
                                       AND action = 'REJECT');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@approval_process_rejects_state_id, @course_to_rejected_state_id, 'AdvancedWorkflowProcessToRequestStateChangeTrigger');

-- TC submits a rejected course. Initiate the review process and initiate the reviewers

-- 1) Review process should be initiated
SET @tc_submit_course_state_id := (SELECT id
                                   FROM ohrm_workflow_state_machine
                                   WHERE workflow = 'ONLINE_COURSE'
                                     AND state = 'REJECTED'
                                     AND role = 'Admin'
                                     AND action = 'SUBMIT');

SET @system_initiates_review_process_state_id := (SELECT id
                                                  FROM ohrm_workflow_state_machine
                                                  WHERE workflow = 'ONLINE_COURSE_PROCESS_REVIEW'
                                                    AND state = 'INITIAL'
                                                    AND role = 'SYSTEM'
                                                    AND action = 'INITIATE');

INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`)
VALUES (@tc_submit_course_state_id, @system_initiates_review_process_state_id, 'AdvancedWorkflowRequestToProcessStateChangeTrigger');

-- 6515_feature_enhancement_audittrail

-- Adding audit trail related screens and menu items.
SET @otm_module_id := (SELECT `id`
                       FROM `ohrm_module`
                       WHERE `name` = 'onlinetraining');

SET @online_course_audit_trail_data_group_id := (SELECT `id`
                                                 FROM `ohrm_data_group`
                                                 WHERE `name` = 'online_course_audit_trail');

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

INSERT INTO `ohrm_audittrail_module` (`name`)
VALUES ('Training');
SET @training_module_id := (SELECT `id`
                            FROM `ohrm_audittrail_module`
                            WHERE `name` = 'Training');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`)
VALUES ('CourseDetail', 'Courses & Assessments', 'Online Course', @training_module_id),
       ('Resource', 'Resources', 'Online Course', @training_module_id),
       ('Assessment', 'Assessment', 'Online Course', @training_module_id),
       ('Reviewer', 'Reviewers', 'Online Course', @training_module_id),
       ('Approver', 'Approvers', 'Online Course', @training_module_id),
       ('CourseEmployee', 'Assessment Employees', 'Online Course', @training_module_id),
       ('MyCourse', 'My Assessments', 'Online Course', @training_module_id);
set @courseDetailId := (SELECT id
                        FROM ohrm_audittrail_section
                        WHERE name = 'CourseDetail');
set @resourceId := (SELECT id
                    FROM ohrm_audittrail_section
                    WHERE name = 'Resource');
set @assessmentId := (SELECT id
                      FROM ohrm_audittrail_section
                      WHERE name = 'Assessment');
set @reviewerId := (SELECT id
                    FROM ohrm_audittrail_section
                    WHERE name = 'Reviewer');
set @approverId := (SELECT id
                    FROM ohrm_audittrail_section
                    WHERE name = 'Approver');
set @employeeId := (SELECT id
                    FROM ohrm_audittrail_section
                    WHERE name = 'CourseEmployee');
set @myCourseId := (SELECT id
                    FROM ohrm_audittrail_section
                    WHERE name = 'MyCourse');

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@courseDetailId, 'ADD COURSE', 'Add Course'),
       (@courseDetailId, 'UPDATE COURSE', 'Update Course'),
       (@courseDetailId, 'DELETE COURSE', 'Delete Course'),
       (@courseDetailId, 'PUBLISH COURSE', 'Publish Course'),
       (@courseDetailId, 'SUBMIT COURSE', 'Submit Course'),
       (@courseDetailId, 'EXPIRE COURSE', 'Expire Course'),
       (@resourceId, 'ADD RESOURCE', 'Add Resource'),
       (@resourceId, 'UPDATE RESOURCE', 'Update Resource'),
       (@resourceId, 'DELETE RESOURCE', 'Delete Resource'),
       (@assessmentId, 'ADD ASSESSMENT', 'Add Assessment'),
       (@assessmentId, 'ADD ASSESSMENT QUESTION', 'Add Assessment Questions'),
       (@assessmentId, 'UPDATE ASSESSMENT', 'Update Assessment'),
       (@assessmentId, 'UPDATE ASSESSMENT QUESTION', 'Update Assessment Questions'),
       (@assessmentId, 'DELETE ASSESSMENT', 'Delete Assessment'),
       (@assessmentId, 'DELETE ASSESSMENT QUESTION', 'Delete Assessment Questions'),
       (@reviewerId, 'ADD REVIEWER', 'Add Reviewer'),
       (@reviewerId, 'UPDATE REVIEWER', 'Update Reviewer'),
       (@reviewerId, 'DELETE REVIEWERS', 'Delete Reviewers'),
       (@reviewerId, 'REVIEW COURSE MATERIAL', 'Review Course Material'),
       (@reviewerId, 'REVIEW COURSE ASSESSMENT', 'Review Course Assessment'),
       (@reviewerId, 'SAVE REVIEWER COMMENT', 'Save Reviewer Comment'),
       (@reviewerId, 'REVIEW COMPLETE', 'Review Complete'),
       (@approverId, 'ADD APPROVER', 'Add Approver'),
       (@approverId, 'UPDATE APPROVER', 'Update Approver'),
       (@approverId, 'DELETE APPROVERS', 'Delete Approvers'),
       (@approverId, 'UPDATE APPROVER COMMENT', 'Update Approver Comment'),
       (@approverId, 'SAVE APPROVER COMMENT', 'Save Approver Comment'),
       (@approverId, 'APPROVE COURSE', 'Approve Course'),
       (@approverId, 'REJECT COURSE', 'Reject Course'),
       (@employeeId, 'ADD EMPLOYEE', 'Add Employee'),
       (@employeeId, 'DELETE EMPLOYEE', 'Delete Employee'),
       (@myCourseId, 'VIEW COURSE', 'View Course (Trainee)'),
       (@myCourseId, 'VIEW ASSESSMENT', 'View Assessment (Trainee)');


CREATE TABLE `ohrm_audittrail_training_online_course_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(11)      NOT NULL DEFAULT '0',
    `action`               varchar(255) NOT NULL,
    `affected_entity_id`   varchar(20)  NOT NULL DEFAULT '',
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description`   VARCHAR(30000) CHARSET UTF8,
    `ip_address`           VARCHAR(100) NULL,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_training_online_course_trail_idx_app_id (app_id),
    INDEX audit_training_online_course_trail_idx_screen_name (screen_name)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

SET @event_listener_max_position := (SELECT MAX(`position`)
                                     FROM `ohrm_event_listener`);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`)
VALUES ('onlinetraining.add_course', 'OnlineTrainingAddCourseListener', '1', @event_listener_max_position + 1),
       ('onlinetraining.update_course', 'OnlineTrainingUpdateCourseListener', '1', @event_listener_max_position + 2),
       ('onlinetraining.delete_course', 'OnlineTrainingDeleteCourseListener', '1', @event_listener_max_position + 3),
       ('onlinetraining.event.workflow_state_change_course', 'OnlineTrainingCourseWorkflowStateChangeListener', '1', @event_listener_max_position + 4),
       ('onlinetraining.expire_course', 'OnlineTrainingExpireCourseListener', '1', @event_listener_max_position + 5),
       ('onlinetraining.add_resource', 'OnlineTrainingAddResourceListener', '1', @event_listener_max_position + 6),
       ('onlinetraining.update_resource', 'OnlineTrainingUpdateResourceListener', '1', @event_listener_max_position + 7),
       ('onlinetraining.delete_resource', 'OnlineTrainingDeleteResourceListener', '1', @event_listener_max_position + 8),
       ('onlinetraining.add_assessment', 'OnlineTrainingAddAssessmentListener', '1', @event_listener_max_position + 9),
       ('onlinetraining.update_assessment', 'OnlineTrainingUpdateAssessmentListener', '1', @event_listener_max_position + 10),
       ('onlinetraining.add_assessment_question', 'OnlineTrainingAddAssessmentQuestionListener', '1', @event_listener_max_position + 11),
       ('onlinetraining.update_assessment_question', 'OnlineTrainingUpdateAssessmentQuestionListener', '1', @event_listener_max_position + 12),
       ('onlinetraining.delete_assessment_question', 'OnlineTrainingDeleteAssessmentQuestionListener', '1', @event_listener_max_position + 13),
       ('onlinetraining.add_course_employee', 'OnlineTrainingAddCourseEmployeeListener', '1', @event_listener_max_position + 14),
       ('onlinetraining.delete_course_employee', 'OnlineTrainingDeleteCourseEmployeeListener', '1', @event_listener_max_position + 15),
       ('onlinetraining.view_course', 'OnlineTrainingViewCourseListener', '1', @event_listener_max_position + 16),
       ('onlinetraining.view_assessment', 'OnlineTrainingViewAssessmentListener', '1', @event_listener_max_position + 17),
       ('onlinetraining.event.add_course_reviewer', 'AddCourseReviewerAuditEventListener', 1, @event_listener_max_position + 18),
       ('onlinetraining.event.add_course_approver', 'AddCourseApproverAuditEventListener', 1, @event_listener_max_position + 19),
       ('onlinetraining.event.update_course_reviewer', 'UpdateCourseReviewerAuditEventListener', 1, @event_listener_max_position + 20),
       ('onlinetraining.event.update_course_approver', 'UpdateCourseApproverAuditEventListener', 1, @event_listener_max_position + 21),
       ('onlinetraining.event.delete_course_reviewers', 'DeleteCourseReviewersAuditEventListener', 1, @event_listener_max_position + 22),
       ('onlinetraining.event.delete_course_approvers', 'DeleteCourseApproversAuditEventListener', 1, @event_listener_max_position + 23),
       ('onlinetraining.event.reviewer_action_performed', 'ReviewerActionPerformedAuditEventListener', 1, @event_listener_max_position + 24),
       ('onlinetraining.event.approver_action_performed', 'ApproverActionPerformedAuditEventListener', 1, @event_listener_max_position + 25);

-- 6515_feature_enhancement_notifications

DELETE
FROM ohrm_notification
WHERE `name` IN ('Online Assessment Courses - Course Due Notification',
                 'Online Assessment Courses - Course Overdue Notification',
                 'Online Assessment Courses - Course Reviewer Assignment',
                 'Online Assessment Courses - Course Approver Assignment',
                 'Online Assessment Courses - Course Review Complete',
                 'Online Assessment Courses - Course Approve Complete',
                 'Online Assessment Courses - Course Approver Reject',
                 'Online Assessment Courses - Course Publish',
                 'Online Assessment Courses - Course Reviewer Removed',
                 'Online Assessment Courses - Course Approver Removed');

-- COURSE DUE DATE NOTIFICATION START
SET @date_based_notification_type := (SELECT id
                                      FROM ohrm_notification_type
                                      where `name` = 'Date Based');
SET @email_media := (SELECT id
                     FROM ohrm_notification_media
                     WHERE `name` = 'Email');
SET @training_module_id := (SELECT `id`
                            FROM `ohrm_module`
                            WHERE `name` = 'training');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Online Assessment Courses - Course Due Notification', 1, @date_based_notification_type, 'OnlineCourseDueNotification', NULL, NULL, @training_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Course Due Notification sent to Subscriber',
        'Course Due Notification',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %FIRSTNAME%,

        Your course - %COURSENAME% will expire in %DUEDATE%.

        Please make sure you complete it.

        You can click %COURSEURL% to directly access the course.

        Thank you.

        This is an automated notification. </pre>
        ');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Online Assessment Courses - Course Overdue Notification', 1, @date_based_notification_type, 'OnlineCourseOverdueNotification', NULL, NULL, @training_module_id,
        0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Course Overdue sent to Subscriber',
        'Course Overdue Notification',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %FIRSTNAME%,

        Your course - %COURSENAME% was expired on %DUEDATE%.

        You can click %COURSEURL% to directly access the course.

        Thank you.

        This is an automated notification. </pre>
        ');
-- COURSE DUE DATE NOTIFICATION END

-- ACTION BASED NOTIFICATION START
SET @action_notification_type := (SELECT id
                                  FROM ohrm_notification_type
                                  where `name` = 'Action Based');
SET @other_notification_type := (SELECT id
                                 FROM ohrm_notification_type
                                 where `name` = 'Other');
SET @email_media := (SELECT id
                     FROM ohrm_notification_media
                     WHERE `name` = 'Email');
SET @training_module_id := (SELECT `id`
                            FROM `ohrm_module`
                            WHERE `name` = 'training');
SET @course_review_workflow_id := (SELECT id
                                   FROM ohrm_workflow
                                   where `name` = 'Online Course Review Actor Workflow');
SET @course_approve_workflow_id := (SELECT id
                                    FROM ohrm_workflow
                                    where `name` = 'Online Course Approve Actor Workflow');
SET @course_workflow_id := (SELECT id
                            FROM ohrm_workflow
                            where `name` = 'Online Course');

SET @course_reviewer_user_role_id := (SELECT `id`
                                      FROM ohrm_user_role
                                      WHERE `name` = 'CourseReviewer');

SET @course_approver_user_role_id := (SELECT `id`
                                      FROM ohrm_user_role
                                      WHERE `name` = 'CourseApprover');

-- REVIEWER ASSIGN
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`)
VALUES ('Online Assessment Courses - Course Reviewer Assignment', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseReviewerAssignmentEmailProcessor"}', NULL, NULL, @training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Reviewer Assignment Notification sent to Employee',
        'Course Review - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

        You have been assigned to review course %courseName%.

        You can click %courseURL% to directly access the course.

        Thank you.

        This is an automated notification. </pre>.
        ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id)
VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name)
VALUES (@email_media, '{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @employee_template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;

--

-- APPROVER ASSIGN
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`)
VALUES ('Online Assessment Courses - Course Approver Assignment', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseApproverAssignmentEmailProcessor"}', NULL, NULL, @training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Approver Assignment Notification sent to Employee',
        'Course Approval - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

        You have been assigned to approve course %courseName%.

        You can click %courseURL% to directly access the course.

        Thank you.

        This is an automated notification. </pre>.
        ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id)
VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name)
VALUES (@email_media, '{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @employee_template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;

-- REVIEWER REMOVE
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`)
VALUES ('Online Assessment Courses - Course Reviewer Removed', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseReviewerRemoveEmailProcessor"}', NULL, NULL, @training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Reviewer Removed Notification sent to Reviewer',
        'Course Review - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

        You have been removed from reviewing the course %courseName%.

        Thank you.

        This is an automated notification. </pre>.
        ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id)
VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name)
VALUES (@email_media, '{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @employee_template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;

--

-- APPROVER REMOVED
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`)
VALUES ('Online Assessment Courses - Course Approver Removed', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseApproverRemoveEmailProcessor"}', NULL, NULL, @training_module_id);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Approver Removed Notification sent to Approver',
        'Course Approval - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

        You have been removed from approving the course %courseName%.

        Thank you.

        This is an automated notification. </pre>.
        ');

SET @employee_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id)
VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name)
VALUES (@email_media, '{"prevent_delete":true}', 'Custom', 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @employee_template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;


--

-- REVIEW COMPLETE
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Online Assessment Courses - Course Review Complete', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseReviewEmailProcessor"}', @course_review_workflow_id, 'Save', @training_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
SELECT @notification_id,
       id
FROM ohrm_workflow_state_machine
WHERE `workflow` = @course_review_workflow_id
  AND `role` = 'CourseReviewer'
  AND `action` = 'COMPLETE REVIEW';

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Review Completion Notification sent to Coordinator',
        'Course Review - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

       %courseName% has been reviewed by %performerFullName%.

       You can click %courseURL% to directly access the course.

       Thank you.

       This is an automated notification. </pre>.');
--

-- APPROVE COMPLETE
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Online Assessment Courses - Course Approve Complete', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseApproveEmailProcessor"}', @course_approve_workflow_id, 'Approve', @training_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
SELECT @notification_id,
       id
FROM ohrm_workflow_state_machine
WHERE `workflow` = @course_approve_workflow_id
  AND `role` = 'CourseApprover'
  AND `action` = 'APPROVE';

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Approve Notification sent to Coordinator',
        'Course Approve - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

       %courseName% has been approved by %performerFullName%.

       You can click %courseURL% to directly access the course.

       Thank you.

       This is an automated notification. </pre>.');
--

-- APPROVER REJECT
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Online Assessment Courses - Course Approver Reject', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCourseApproveEmailProcessor"}', @course_approve_workflow_id, 'Reject', @training_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
SELECT @notification_id,
       id
FROM ohrm_workflow_state_machine
WHERE `workflow` = @course_approve_workflow_id
  AND `role` = 'CourseApprover'
  AND `action` = 'REJECT';

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Reject Notification sent to Coordinator',
        'Course Reject - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

       %courseName% has been rejected by %performerFullName%.

       You can click %courseURL% to directly access the course.

       Thank you.

       This is an automated notification. </pre>.');
--

-- PUBLISH
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Online Assessment Courses - Course Publish', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"OnlineCoursePublishEmailProcessor","extra_roles":["CourseReviewer","CourseApprover"]}', @course_workflow_id, 'Publish',
        @training_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Publish Notification sent to Reviewer/Approver',
        'Course Publish - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

       %courseName% has been published.

       You can click %courseURL% to directly access the course.

       Thank you.

       This is an automated notification. </pre>.');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
SELECT @notification_id,
       id
FROM ohrm_workflow_state_machine
WHERE `workflow` = @course_workflow_id
  AND state = 'APPROVED'
  AND `action` = 'PUBLISH';

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id)
VALUES (@email_media, '{"prevent_delete":true}', 'UserRole', @course_reviewer_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id)
VALUES (@email_media, '{"prevent_delete":true}', 'UserRole', @course_approver_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;

-- Notification sent to employees newly added to the course after the most recent publishing

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`)
VALUES (@notification_id, @email_media, 'en_US',
        'Online Assessment Course Published Notification sent to Employees',
        'Course Published - %courseName%',
        '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

You have been assigned to the following course %courseName%.

Course Completion is due on %dueDate%.

You can access the course by clicking %courseURL%.

Please complete the course to receive your certification.

Thank you.

This is an automated notification. </pre>.
');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name)
VALUES (@email_media, '{"prevent_delete":true}', 'Custom', 'Non Notified Course Employees');

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
SELECT @notification_id,
       id
FROM ohrm_workflow_state_machine
WHERE `workflow` = @course_workflow_id
  AND `action` = 'PUBLISH';

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @subscriber_id,
       @template_id
FROM ohrm_notification_subscription s
WHERE s.id >= @last_subscription_id;

-- ACTION BASED NOTIFICATION END

UPDATE `ohrm_notification`
SET `is_enable` = '0'
WHERE `name` = 'Online Assessment Courses - Course Assignment';

SET @token_section_qualifications := (SELECT `id` FROM ohrm_token_section WHERE `name` = 'Qualifications');

INSERT INTO `ohrm_token` (`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`,
                          `is_date`, `localize`, `parameters`, `token_section_id`, `is_multi_attribute`)
VALUES ('Qualifications.Certificates', 'Certificates', 'QualificationsTokenValueGenerator', null, '{"Course":{"display_name":"Course","value_getter":"getOnlineCourse,getFormattedOnlineCourseName", "is_date":false},
        "Status":{"display_name":"Status","value_getter":"getFormattedStatus","is_date":false}, "Mark":{"display_name":"Mark","value_getter":"getFormattedMark","is_date":false},"NoOfAttempts":{"display_name":"No of Attempts","value_getter":"getNoOfAttempts","is_date":false},"IssuedDate":{"display_name":"Issued Date","value_getter":"getAssignedDate","is_date":true},"ExpiryDate":{"display_name":"Expiry Date","value_getter":"getDueDate","is_date":true}}',
        @qualification_certificate_data_group_id, 0, 0, 'avoidDefaultEncode=>true,type=>table,hasMultiple=>true',
        @token_section_qualifications, 1);

-- 6515_feature_enhancement_core_questionnaire_changes

ALTER TABLE `ohrm_question` CHANGE `question_description` `question_description` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `ohrm_question_form_question` CHANGE `question_description` `question_description` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

SET @ONLINE_Training_Employee_Course_report := (SELECT id
                                                FROM ohrm_advanced_report
                                                WHERE unique_name = 'ONLINE_Training_Employee_Course');
SET @Course_Analytics_Report := (SELECT id
                                 FROM ohrm_advanced_report
                                 WHERE unique_name = 'Course_Analytics_Report');
SET @Course_Material_Analytics_Report := (SELECT id
                                          FROM ohrm_advanced_report
                                          WHERE unique_name = 'Course_Material_Analytics_Report');
SET @Online_Course_All_Available_courses := (SELECT id
                                             FROM ohrm_advanced_report
                                             WHERE unique_name = 'Online_Course_All_Available_courses');
SET @training_folder_id := (SELECT id
                            FROM ohrm_report_folder
                            WHERE name = 'Training');

UPDATE `ohrm_advanced_report`
SET `folder_id`= @training_folder_id
WHERE `id` IN (
               @ONLINE_Training_Employee_Course_report,
               @Course_Analytics_Report,
               @Course_Material_Analytics_Report,
               @Online_Course_All_Available_courses
    );
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "ONLINE_Training_Employee_Course", "filters": null})'
WHERE `id` = @ONLINE_Training_Employee_Course_report;
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Course_Analytics_Report", "filters": null})'
WHERE `id` = @Course_Analytics_Report;
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Online_Course_All_Available_courses", "filters": null})'
WHERE `id` = @Online_Course_All_Available_courses;
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Course_Material_Analytics_Report", "filters": null})'
WHERE `id` = @Course_Material_Analytics_Report;

-- combine training modules

CREATE TABLE `ohrm_audittrail_online_training_course_detail_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) NOT NULL,
     `version_id` INT NOT NULL DEFAULT 0,
     `action` varchar(255) NOT NULL,
     `affected_entity_id` VARCHAR(20),
     `affected_entity_name` TEXT,
     `action_description` TEXT CHARSET UTF8,
     `app_id` INT DEFAULT NULL,
     `app_name` VARCHAR(1000) DEFAULT NULL,
     `screen_name` VARCHAR(1000) DEFAULT NULL,
     PRIMARY KEY (`id`),
     INDEX audit_online_training_course_detail_trail_idx_app_id (app_id),
     INDEX audit_online_training_course_detail_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_online_training_resource_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) NOT NULL,
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` TEXT,
    `action_description` TEXT CHARSET UTF8,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_online_training_resource_trail_idx_app_id (app_id),
    INDEX audit_online_training_resource_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_online_training_assessment_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) NOT NULL,
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` TEXT,
    `action_description` LONGTEXT CHARSET UTF8,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_online_training_assessment_trail_idx_app_id (app_id),
    INDEX audit_online_training_assessment_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_online_training_reviewer_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) NOT NULL,
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` TEXT,
    `action_description` TEXT CHARSET UTF8,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_online_training_reviewer_trail_idx_app_id (app_id),
    INDEX audit_online_training_reviewer_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_online_training_approver_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) NOT NULL,
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` TEXT,
    `action_description` TEXT CHARSET UTF8,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_online_training_approver_trail_idx_app_id (app_id),
    INDEX audit_online_training_approver_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_online_training_course_employee_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) NOT NULL,
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` TEXT,
    `action_description` TEXT CHARSET UTF8,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_online_training_course_employee_trail_idx_app_id (app_id),
    INDEX audit_online_training_course_employee_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_online_training_my_course_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) NOT NULL,
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` TEXT,
    `action_description` TEXT CHARSET UTF8,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_online_training_my_course_trail_idx_app_id (app_id),
    INDEX audit_online_training_my_course_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_course_detail`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_course_detail
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_course_detail_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name
         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_resource`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_resource
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_resource_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name
         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_assessment`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_assessment
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_assessment_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name
         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_reviewer`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_reviewer
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_reviewer_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name
         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_approver`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_approver
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_approver_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name

         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_course_employee`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_course_employee
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_course_employee_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name
         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training_my_course`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training_my_course
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;
INSERT INTO `ohrm_audittrail_online_training_my_course_trail` (
    action_time,
    action_owner_id,
    action_owner_name,
    action,
    action_description,
    version_id,
    affected_entity_id,
    affected_entity_name,
    app_id,
    app_name,
    screen_name
) VALUES (
             action_time,
             action_owner_id,
             action_owner_name,
             action,
             action_description,
             version_id,
             affected_entity_id,
             affected_entity_name,
             app_id,
             app_name,
             screen_name
         );
END;$$

DROP PROCEDURE IF EXISTS `audit_insert_to_ohrm_audittrail_online_training`;$$
CREATE PROCEDURE audit_insert_to_ohrm_audittrail_online_training
(
    IN action_time DATETIME,
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name TEXT CHARSET UTF8,
    IN action TEXT CHARSET UTF8,
    IN action_description TEXT CHARSET UTF8,
    IN version_id BIGINT,
    IN affected_entity_id VARCHAR(20) CHARSET UTF8,
    IN affected_entity_name TEXT CHARSET UTF8
)
BEGIN
SET @section := (SELECT DISTINCT ohrm_audittrail_section.name FROM ohrm_audittrail_training_online_course_trail
                 LEFT JOIN ohrm_audittrail_section_actions
                   ON (ohrm_audittrail_training_online_course_trail.action = ohrm_audittrail_section_actions.action)
                 LEFT JOIN ohrm_audittrail_section
                   ON (ohrm_audittrail_section.id = ohrm_audittrail_section_actions.section_id)
                 WHERE ohrm_audittrail_training_online_course_trail.action = action);
CASE
WHEN (SELECT @section) = 'CourseDetail' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_course_detail(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
WHEN (SELECT @section) = 'Resource' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_resource(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
WHEN (SELECT @section) = 'Assessment' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_assessment(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
WHEN (SELECT @section) = 'Reviewer' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_reviewer(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
WHEN (SELECT @section) = 'Approver' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_approver(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
WHEN (SELECT @section) = 'CourseEmployee' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_course_employee(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
WHEN (SELECT @section) = 'MyCourse' THEN
CALL audit_insert_to_ohrm_audittrail_online_training_my_course(
            action_time,
            action_owner_id,
            action_owner_name,
            action,
            action_description,
            version_id,
            affected_entity_id,
            affected_entity_name
        );
END CASE;
END;$$

DELIMITER ;

UPDATE `ohrm_module` SET data_group_prefix = 'Online Assessment Courses' WHERE `name` = 'onlinetraining';
