


CREATE TABLE ohrm_notification_type (
    id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY(id)
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_media (
    id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY(id)
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification (
    id INT AUTO_INCREMENT, name VARCHAR(100), is_enable INT, type_id INT NOT NULL, generator_class VARCHAR(255), 
    parameters text, workflow_id INT, workflow_action VARCHAR(255), 
    hidden TINYINT NOT NULL DEFAULT 0, PRIMARY KEY(id),
    foreign key (`type_id`) references `ohrm_notification_type` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_subscriber (
    id INT AUTO_INCREMENT, media_id INT, address_part VARCHAR(10), parameters text, type VARCHAR(255), emp_number INT, 
    name VARCHAR(100), email VARCHAR(100), user_role_id INT, PRIMARY KEY(id),
    foreign key (`emp_number`) references `hs_hr_employee` (`emp_number`) on delete cascade on update cascade,
    foreign key (`user_role_id`) references `ohrm_user_role` (`id`) on delete cascade on update cascade,
    foreign key (`media_id`) references `ohrm_notification_media` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_subscription (
    id INT AUTO_INCREMENT, notification_id INT NOT NULL, parameters text, affected_emp_number INT, 
    notify_affected_employee TINYINT DEFAULT 0, notify_before_value INT, notify_before_unit VARCHAR(50), 
    schedule_dom INT, schedule_dow INT, schedule_lastrun DATE, workflow_item_id BIGINT, 
    hidden TINYINT NOT NULL DEFAULT 0, PRIMARY KEY(id),
    foreign key (`notification_id`) references `ohrm_notification` (`id`) on delete cascade on update cascade,
    foreign key (`affected_emp_number`) references `hs_hr_employee` (`emp_number`) on delete cascade on update cascade,
    foreign key (`workflow_item_id`) references `ohrm_workflow_state_machine` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_subscriber_subscription (
    subscription_id INT, subscriber_id INT, PRIMARY KEY(subscription_id, subscriber_id),
    foreign key (`subscription_id`) references `ohrm_notification_subscription` (`id`) on delete cascade on update cascade,
    foreign key (`subscriber_id`) references `ohrm_notification_subscriber` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_subscription_template (
    id INT AUTO_INCREMENT, subscription_id INT NOT NULL, media_id INT, locale VARCHAR(20), 
    performer_role VARCHAR(50), recipient_role VARCHAR(50), subject VARCHAR(255), body text, PRIMARY KEY(id),
    foreign key (`subscription_id`) references `ohrm_notification_subscription` (`id`) on delete cascade on update cascade,
    foreign key (`media_id`) references `ohrm_notification_media` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_supported_media (
    id INT AUTO_INCREMENT, notification_id INT, media_id INT, PRIMARY KEY(id),
    foreign key (`notification_id`) references `ohrm_notification` (`id`) on delete cascade on update cascade,
    foreign key (`media_id`) references `ohrm_notification_media` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_notification_template (
    id INT AUTO_INCREMENT, notification_id INT NOT NULL, media_id INT, locale VARCHAR(20), performer_role VARCHAR(50), 
    recipient_role VARCHAR(50), subject VARCHAR(255), body text, PRIMARY KEY(id),
    foreign key (`notification_id`) references `ohrm_notification` (`id`) on delete cascade on update cascade,
    foreign key (`media_id`) references `ohrm_notification_media` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;

INSERT INTO ohrm_notification_type (`name`) VALUES
('Action Based'),
('Date Based'),
('Scheduled'),
('Other');

INSERT INTO ohrm_notification_media (`name`) VALUES
('Email');

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 @ess_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'ESS');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

-- Event Notifications
SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');

-- DOB
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('DOB Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'DOB Notification',
'This is to remind that FIRSTNAME LASTNAME''s Date of Birth(EMP_BIRTHDAY) is getting closer and reached in DAYSLEFT.\r\n\r\nRegards,\r\nHR Team'
);

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

-- Driving License
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Driving License Expiry Date', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Driving License Expiry Date',
'This is to remind that FIRSTNAME LASTNAME''s Driving License Expiry Date(EMP_DRI_LICE_EXP_DATE) is getting closer and reached in DAYSLEFT. Please take necessary steps needed to renew it.\r\n\r\nRegards,\r\nHR Team'
);

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

-- Immigration Expiry Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Immigration Expiry Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Immigration Expiry Notification',
'This is to remind that FIRSTNAME LASTNAME''s Passport/Visa Expiry Date is getting closer and reached in DAYSLEFT.\r\nPlease take necessary steps needed to renew it.\r\nDetails follow.\r\n\r\nPassport/Visa No : NUMBER\r\nCountry : COUNTRYCODE\r\nIssued Date : ISSUEDDATE\r\nExpiry Date : EXPIRYDATE\r\n\r\nRegards,\r\nHR Team'
);

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

-- Licenses Expiry Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Licenses Expiry Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Licenses Expiry Notification',
'This is to remind that FIRSTNAME LASTNAME''''s License Expiry Date is getting closer and reached in DAYSLEFT.\r\nPlease take necessary steps needed to renew it.\r\nDetails follow.\r\n\r\nLicense Type : NAME\r\nStart Date : LICENSEISSUEDDATE\r\nEnd Date : LICENSEEXPIRYDATE\r\n\r\nRegards,\r\nHR Team'
);

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

-- Memberships Expiry Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Memberships Expiry Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Memberships Expiry Notification',
'This is to remind that FIRSTNAME LASTNAME''''s Membership Expiry Date is getting closer and reached in DAYSLEFT.\r\n\r\nPlease take necessary steps needed to renew it.\r\n\r\nDetails follow.\r\nMembership : NAME\r\nOwnership : SUBSCRIPTIONPAIDBY\r\nCommence Date : SUBSCRIPTIONCOMMENCEDATE\r\nRenewal Date : SUBSCRIPTIONRENEWALDATE\r\n\r\nRegards,\r\nHR Team'
);

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

-- Employee Contracts Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Employee Contracts Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Contracts Notification',
'This is to remind that FIRSTNAME LASTNAME''s Contract Extension End Date is getting closer and reached in DAYSLEFT.\r\nPlease take necessary steps needed to renew it.\r\nDetails follow.\r\n\r\nContract id : CONTRACT_ID\r\nContract Extension Start Date : START_DATE\r\nContract Extension End Date : END_DATE\r\n\r\nRegards,\r\nHR Team'
);

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


-- Retirement Notificiation
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Retirement Notification', 1, @date_based_notification_type, 'RetirementEventNotification', 
     '{"extra_settings":{"Retirement_Age":"sfWidgetFormInputText"}}', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Retirement Notification',
'This is to remind that FIRSTNAME LASTNAME''s retirement date is getting closer and reached in DAYSLEFT. \r\nPlease provide HR with the following: \r\nbirth certificate, letters of employments from previous institutions for Pension purposes. \r\n\r\nPlease contact HR Office for assistance.\r\n\r\nRegards,\r\nHR Team'
);

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

-- OrangeHRM License Expiry Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`, `hidden`) VALUES
    ('OrangeHRM License Expiry Notification', 1, @date_based_notification_type, 'LicenseExpiryEventNotification', NULL, NULL, 1);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'OrangeHRM Server license expiry reminder',
'This is to remind that the License to use OrangeHRM will expire on EXPIRY_DATE. Please contact OrangeHRM for an updated License..\r\n\r\nRegards,\r\nOrangeHRM '
);

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

    -- Subscribe admin's
INSERT INTO `ohrm_notification_subscription`
    (notification_id, notify_before_value, notify_before_unit) VALUES
    (@notification_id, 3, 'DAY');

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @admin_user_role);

SET @subscriber_id := LAST_INSERT_ID();

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

-- Anniversary Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Anniversary Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Anniversary Notification',
'Hi RECIPINTNAME ,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''s Anniversary Date(JOINED_DATE) is getting closer and reached in DAYSLEFT.\r\n\r\nRegards,\r\nHR Team'
);

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

-- Birthday Wishes
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Birthday Wishes', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Happy Birthday!',
'Hi RECIPINTNAME,<br><br><img src="WISHIMAGE"><br><br>Wishing you on behalf of the COMPANYNAME Team.<br><br>Regards,<br>HR Team.<br><br><img src="LOGO" align="left"><img src=OHRM align=right>'
);

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

-- Probation end date
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('Probation End Date Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Probation End Date Notification',
'This is to remind that FIRSTNAME LASTNAME''s Probation End Date is getting closer and reached in DAYSLEFT.\r\n\r\nRegards,\r\nHR Team'
);

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

-- PIM Updates
SET @scheduled_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Scheduled');

INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`) VALUES
    ('PIM Updates', 0, @scheduled_notification_type, 'PimAuditEmailProcessor', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'PIM Field Update Notification',
'The changes to pim fields from %fromDate% to %toDate% are listed below.

%pimChanges%


Thank you
System Admin    
(This is an auto-generated email, please do not reply!) ');





