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

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

-- Course Coordinator Added

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('training.course_coordinator_added', 'CoordinatorAssignedTrainingCoursesEventListener', '1', '1');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`)
VALUES ('Courses-Coordinator Added', 1, @other_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
        '{"email_processor":"TrainingCourseCoordinatorAssignmentEmailProcessor"}', 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_subscription` (notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

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

        You are assigned as a coordinator for %courseName%.

        Assigned By: %assignedBy%
        Assigned On: %assigndDateTime%

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

SET @employee_template_id := LAST_INSERT_ID();

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

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;

-- Course Coordinator Removed

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('training.course_coordinator_removed', 'CoordinatorRemovedTrainingCoursesEventListener', '1', '1');

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

        Your work as the coordinator of the %courseName% has been delegated to another user.

        Thank you for taking part on the course.
        Delegated By: %assignedBy%
        Delegated On: %assigndDateTime%

        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', 'Course Coordinator');

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;