

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 VARCHAR(100), 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_template (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  notification_id int(11) NOT NULL,
  media_id int(11) DEFAULT NULL,
  locale varchar(20) DEFAULT NULL,
  is_default TINYINT NOT NULL DEFAULT 0,
  subject varchar(255) DEFAULT NULL,
  body mediumtext,
   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_subscriber_subscription_template_back_image` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `template_id` INT(11) NOT NULL,
    `unique_id` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) DEFAULT NULL,
    `type` VARCHAR(50),
    `picture` LONGBLOB,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_subscriber_subscription_template_back_image` FOREIGN KEY(`template_id`) REFERENCES `ohrm_notification_subscriber_subscription_template` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ohrm_document_template` ADD CONSTRAINT `document_template_notification_subscriber_subscription_template` FOREIGN KEY (`notification_subscriber_subscription_template_id`)
    REFERENCES `ohrm_notification_subscriber_subscription_template` (`id`)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

CREATE TABLE ohrm_notification_subscriber_subscription (
    subscription_id INT, subscriber_id INT, template_id INT DEFAULT NULL, 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,
    foreign key (`template_id`) references `ohrm_notification_subscriber_subscription_template` (`id`) on delete cascade on update cascade
) ENGINE = INNODB default charset=utf8;
-- emp id nullable
CREATE TABLE ohrm_notification_subscriber_exclusion (
    id INT(10) AUTO_INCREMENT,
    subscriber_id INT,
    emp_number int(7) default null,
    PRIMARY KEY(id),
    CONSTRAINT `ohrm_notification_subscriber_exclusion_emp_number` foreign key (`emp_number`) references `hs_hr_employee` (`emp_number`) on delete cascade on update cascade,
    CONSTRAINT `ohrm_notification_subscriber_exclusion_subscriber_id` foreign key (`subscriber_id`) references `ohrm_notification_subscriber` (`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;

INSERT INTO ohrm_notification_type (`id`,`name`) VALUES
(1, 'Action Based'),
(2, 'Date Based'),
(3, 'Scheduled'),
(4, 'Other'),
(5, 'Manual');

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`, `parameters`) VALUES
    ('DOB Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity": "Employee" ,"attribute": "emp_birthday","employeeJoinAttribute": "empNumber","dateFormat" : "MM-DD","employeeReference": "self","dateInPast": true}');
SET @notification_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',
'DOB Notification',
'DOB Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s Date of Birth(%EMP_BIRTHDAY%) is getting closer and reached in %DAYSLEFT%.<br /><br />Regards,<br />HR Team</p>');

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`, `parameters`) VALUES
    ('Driving License Expiry Date', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity": "Employee" ,"attribute": "emp_dri_lice_exp_date","employeeJoinAttribute": "empNumber" ,"dateFormat" : "YYYY-MM-DD","employeeReference": "self"}');
SET @notification_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',
'Driving License Expiry Date',
'Driving License Expiry Date',
'<p>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.<br /><br />Regards,<br />HR Team</p>');

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`, `parameters`) VALUES
    ('Immigration Expiry Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity": "EmployeeImmigrationRecord" ,"attribute": "expiryDate","employeeJoinAttribute": "emp_number","dateFormat" : "YYYY-MM-DD","employeeReference": "foreign"}');
SET @notification_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',
'Immigration Expiry Notification',
'Immigration Expiry Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s Passport/Visa Expiry Date is getting closer and reached in %DAYSLEFT%.<br />Please take necessary steps needed to renew it.<br />Details follow.<br /><br />Passport/Visa No: %NUMBER%<br />Country: %COUNTRYCODE%<br />Issued Date: %ISSUEDDATE%<br />Expiry Date: %EXPIRYDATE%<br /><br />Regards,<br />HR Team</p>');

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`, `parameters`) VALUES
    ('Licenses Expiry Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity":"EmployeeLicense","attribute":"licenseExpiryDate","employeeJoinAttribute":"emp_number","dateFormat":"YYYY-MM-DD","employeeReference":"foreign","relatedEntityList":"License"}');
SET @notification_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',
'Licenses Expiry Notification',
'Licenses Expiry Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s License Expiry Date is getting closer and reached in %DAYSLEFT%.<br />Please take necessary steps needed to renew it.<br />Details follow.<br /><br />License Type: %NAME%<br />Start Date: %LICENSEISSUEDDATE%<br />End Date: %LICENSEEXPIRYDATE%<br /><br />Regards,<br />HR Team</p>');

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`, `parameters`) VALUES
    ('Memberships Expiry Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity": "EmployeeMembership" ,"attribute": "subscriptionRenewalDate","employeeJoinAttribute": "emp_number" ,"dateFormat" : "YYYY-MM-DD","employeeReference": "foreign", "relatedEntityList": "Membership"}');
SET @notification_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',
'Memberships Expiry Notification',
'Memberships Expiry Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s Membership Expiry Date is getting closer and reached in %DAYSLEFT%.<br /><br />Please take necessary steps needed to renew it.<br /><br />Details follow.<br />Membership: %NAME%<br />Ownership: %SUBSCRIPTIONPAIDBY%<br />Commence Date: %SUBSCRIPTIONCOMMENCEDATE%<br />Renewal Date: %SUBSCRIPTIONRENEWALDATE%<br /><br />Regards,<br />HR Team</p>');

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

-- End of Employment Contract Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`, `parameters`) VALUES
    ('End of Employment Contract Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL,'{"entity": "JobRecord" ,"attribute": "contract_end_date","employeeJoinAttribute": "employee_number", "dateFormat" : "YYYY-MM-DD","employeeReference": "foreign", "conditions": ["has_contract_details = 1"]}');
SET @notification_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',
'End of Employment Contract Notification',
'End of Employment Contract Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s employment contract end date is getting closer and is reached in %DAYSLEFT%.<br />Please take the necessary steps needed to renew it.<br /><br />Details:<br />Contract start date: %CONTRACT_START_DATE%<br />Contract end date: %CONTRACT_END_DATE%<br /><br />Regards,<br />HR Team</p>');

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_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Retirement Notification',
'Retirement Notification',
'<p>Hi,</p><p>This is to remind you that %FIRSTNAME% %LASTNAME%''s retirement date is getting closer and %DAYSLEFT% left.</p><p>Please provide the following documents to HR:</p><ul><li>Birth certificate</li><li>Letters of employment from previous jobs for pension purposes</li></ul><p>Please contact the HR Office for assistance.</p><p><br />Regards,<br />HR Team</p>');

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_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'OrangeHRM License Expiry Notification',
'OrangeHRM Server license expiry reminder',
'<p>This is to remind that the License to use OrangeHRM will expire on %EXPIRY_DATE%. Please contact OrangeHRM for an updated License..<br /><br />Regards,<br />OrangeHRM</p>');

SET @template_id := LAST_INSERT_ID();

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, template_id) SELECT
    s.id, @subscriber_id, @template_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`, `parameters`) VALUES
    ('Anniversary Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity": "Employee" ,"attribute": "joined_date","employeeJoinAttribute": "empNumber","dateFormat" : "MM-DD","employeeReference": "self","dateInPast": true}');
SET @notification_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',
'Anniversary Notification',
'Anniversary Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s Anniversary Date(%ANNIVERSARY_DATE%) is getting closer and reached in %DAYSLEFT%.<br /><br />Regards,<br />HR Team</p>');

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`, `parameters`) VALUES
    ('Birthday Wishes', 1, @date_based_notification_type, 'EmployeeBirthdayWishEventNotification', NULL, NULL, '{"entity": "Employee" ,"attribute": "emp_birthday","employeeJoinAttribute": "empNumber","dateFormat" : "MM-DD","employeeReference": "self","dateInPast": true}');
SET @notification_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',
'Birthday Wishes',
'Happy Birthday!',
'Hi %RECIPINTNAME%,<br>%NOTE%<br>%BirthdayImage%<br><br>Wishing you on behalf of the %COMPANYNAME% Team.<br><br>Regards,<br>HR Team.<br><br>%CompanyLogoImage%<br>%OHRMLogoImage%');

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`, `parameters`) VALUES
    ('Probation End Date Notification', 1, @date_based_notification_type, 'EmployeeEventNotification', NULL, NULL, '{"entity": "JobRecord","attribute": "probation_end_date","employeeJoinAttribute": "employee_number","dateFormat" : "YYYY-MM-DD","employeeReference": "foreign"}');
SET @notification_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',
'Probation End Date Notification',
'Probation End Date Notification',
'<p>This is to remind that %FIRSTNAME% %LASTNAME%''s Probation End Date is getting closer and reached in %DAYSLEFT%.<br /><br />Regards,<br />HR Team</p>');

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_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'PIM Updates',
'PIM Field Update Notification',
'<p>The changes to pim fields from %fromDate% to %toDate% are listed below.<br /><br />%pimChanges%<br /><br /><br />Thank you<br />System Admin<br />(This is an auto-generated email, please do not reply!)</p>');

ALTER TABLE `ohrm_notification`  ADD `module_id` INT(11) NOT NULL AFTER `workflow_action`  ;

SET @pim_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='pim');

UPDATE `ohrm_notification` SET `module_id` = @pim_module WHERE `name` IN ('DOB Notification',  'Driving License Expiry Date',  'Immigration Expiry Notification',  'Licenses Expiry Notification', 'Memberships Expiry Notification', 'End of Employment Contract Notification',  'Retirement Notification',  'OrangeHRM License Expiry Notification',  'Anniversary Notification',  'Birthday Wishes',  'Probation End Date Notification', 'PIM Updates')  ;

alter table ohrm_notification add constraint foreign key (module_id)  references  ohrm_module(id) on delete cascade;
