ALTER table ohrm_notification_subscriber_subscription_template
    add column `link` VARCHAR(1050) default null AFTER `body`;

INSERT INTO ohrm_notification_media (`name`) VALUES
    ('Push Notification');

SET @action_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @leave_workflow_id := (SELECT id FROM ohrm_workflow where `name`='leave');
SET @admin_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Admin');
SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @hod_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');
SET @ess_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'ESS');
SET @push_notification_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Push Notification');
SET @leave_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='leave');


SET
@push_notification_media := (SELECT `id` FROM ohrm_notification_media WHERE `name` = 'Push Notification');
SET
@leave_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` LIKE 'leave' );
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
SELECT `id`, @push_notification_media
FROM ohrm_notification
WHERE `id` not in (SELECT `id`
                 FROM `ohrm_notification`
                 WHERE `module_id` = @leave_module_id
                   AND `workflow_action` IN ('APPLY', 'ASSIGN', 'APPROVE', 'CANCEL', 'REJECT'))
  AND `module_id` = @leave_module_id
  AND `hidden` = 1;

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`, `link`)
SELECT `id`,
       @push_notification_media,
       'en_US',
       CONCAT(`name`,' Push Notification'),
       'Actions Performed on Leave Request',
       '%performerFullName% has performed multiple actions on the leave request of %assigneeFullName% for %leaveDuration% %leaveDurationUnit% (%leaveDate%).',
       '/client/#/leave/view_my_leave_list/detail/%leaveRequestId%/my'
FROM ohrm_notification
WHERE `id` not in (SELECT `id`
                 FROM `ohrm_notification`
                 WHERE `module_id` = @leave_module_id
                   AND `workflow_action` IN ('APPLY', 'ASSIGN', 'APPROVE', 'CANCEL', 'REJECT'))
  AND `module_id` = @leave_module_id
  AND `hidden` = 1;

-- Publish Document Notification
SET @notification_id := (SELECT id FROM `ohrm_notification` WHERE `name` LIKE 'Publish Document Notification');

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

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`,`link`)
VALUES ('Publish Document Push Notification Template',
        @notification_id, @push_notification_media,
        'en_US',
        "Document Update",
        'The document "%topic%" has been published.',
        '/client/#/noncore/news/viewDocumentArticle/docId/%documentId%');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@push_notification_media,'{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @notification_subscriber_id,
       @notification_template_id
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT `id`
               FROM `ohrm_notification_subscription`
               WHERE notification_id = @notification_id
                 AND workflow_item_id IN (SELECT id
                                          FROM ohrm_workflow_state_machine
                                          WHERE `workflow` = 'DOCUMENT' AND `action` = 'Publish' AND `role` = 'Admin')
);

-- Publish News Notification
SET @notification_id := (SELECT id FROM `ohrm_notification` WHERE `name` LIKE 'Publish News Notification');

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

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`,`link`)
VALUES ('Publish News Push Notification Template',
        @notification_id, @push_notification_media,
        'en_US',
        "News Update",
        'The news article "%topic%" has been published.',
        '/client/#/news/news_list/%newsId%');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'NEWS' AND `action` = 'Publish' AND `role` = 'Admin');

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@push_notification_media,'{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id)
SELECT s.id,
       @notification_subscriber_id,
       @notification_template_id
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT `id`
               FROM `ohrm_notification_subscription`
               WHERE notification_id = @notification_id
                 AND workflow_item_id IN (SELECT id
                                          FROM ohrm_workflow_state_machine
                                          WHERE `workflow` = 'NEWS' AND `action` = 'Publish' AND `role` = 'Admin')
);

SET @date_based_notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` LIKE 'Date Based');
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` LIKE 'Leave Approval Reminder' AND `type_id` = @date_based_notification_type_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`, `link`) VALUES
    (@notification_id, @push_notification_media, 'en_US',
     'Leave Approval Push Notification Reminder',
     'Leave Approval Reminder',
     'The leave request by %LeaveRequestOwnerFullName% for %leaveDuration% %leaveDurationUnit% (%leaveDate%) is pending for approval.',
     '/client/#/leave/view_leave_list/detail/%leaveRequestId%'
    );

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

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

SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');

INSERT INTO `ohrm_notification_subscriber`(`media_id`, `parameters`, `type`, `user_role_id`, `name`) VALUES
    (@push_notification_media, NULL, 'UserRole', @supervisor_user_role, NULL);

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 IN (SELECT `id`
               FROM `ohrm_notification_subscription`
               WHERE notification_id = @notification_id);