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

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
    ('online_assessment_maximum_attempts_listener', 'OnlineAssessmentMaxAttemptsListener', 1, 54);

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Online Assessment Courses - Maximum Attempts Reached', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"OnlineCourseAssessmentMaximumAttemptsReachedEmailProcessor"}', 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 - Maximum Attempts Reached Notification sent to Subscriber',
     'Notification: Online Assessment Courses - Maximum Attempts Reached',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     This notification is to let you know that %employeeName% has reached the maximum attempts allowed for the %courseName% assessment.

     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 - Maximum Attempts Reached Notification sent to Employee',
     'Notification: Online Assessment Courses - Maximum Attempts Reached',
     '<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">Hi %recipient%,

     This notification is to let you know that you reached the maximum attempts allowed for the %courseName% assessment.

     Please contact the HR Administrator if you wish to retake the course.
     Happy learning!

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

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;
