SET @performance_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='performance');
SET @action_notification_type := (SELECT id
                                  FROM ohrm_notification_type
                                  where `name` = 'Manual');
SET @email_media := (SELECT id
                     FROM ohrm_notification_media
                     WHERE `name` = 'Email');

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`)
VALUES ('GOAL REMINDER', 'GoalReminder', NULL, NULL, NULL, NULL, NULL, @performance_module, '0');

INSERT INTO `ohrm_workflow_state_machine` (`id`, `workflow`, `state`, `role`, `action`, `resulting_state`, `roles_to_notify`, `screens_to_show`, `priority`)
VALUES (NULL, 'GOAL REMINDER', 'ANY', 'SYSTEM', 'Remind', 'ANY', NULL, NULL, '0');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
    ('Goal Manual Reminder Notification', 1, @action_notification_type, 'GoalManualReminderNotificationProcessor',
     '{"list_action_rights":{"change_status":false, "change_subscribers":false, "copy_templates":false }}', null, null, @performance_module, 0);

SET @notification_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` = 'GOAL REMINDER' AND `action` = 'Remind';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, NULL, 'Custom', 'Goal Owner');
SET @subscriber_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',
     'Goal Manual Reminder Notification',
     'Reminder to Update %SubjectForUpdatableGoalContent%',
     "<p>Dear %GoalOwnerName%,<br/><br/>This is a kind reminder to update the progress of your %GoalCategoryInLowerCase%(s).<br/><br/>%GoalDetailsTable%<br/><br/>Thank You,<br/>%SenderName%.<br/><br/>(This is a system-generated email, please do not reply!)<p>");

SET @template_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;
