CREATE TABLE `ohrm_notifications_definition` (
    `nd_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `employee_id` INT NULL ,
    `nt_id` INT NULL ,
    `name` VARCHAR( 255 ) NOT NULL ,
    `text` TEXT NULL ,
    `subject` VARCHAR(255) NULL,
    `recipients` VARCHAR( 255 ) NOT NULL ,
    `schedule` VARCHAR( 255 ) NOT NULL ,
    `param_values` VARCHAR(255),
    `status` ENUM( 'ACTIVE', 'INACTIVE' ) NOT NULL,

    key `employee_id` (`employee_id`),
    key `nt_id` (`nt_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_notifications_type` (
    `nt_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `name` VARCHAR( 255 ) NOT NULL ,
    `module` VARCHAR( 255 )  ,
    `text` TEXT NULL,
    `subject` VARCHAR(255) NULL,
    `param` VARCHAR(255),
    `notification_class` VARCHAR( 255 ) NOT NULL ,
    `recipient_types` VARCHAR( 255 ) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `ohrm_email_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to_email` varchar(60) NOT NULL,
  `from_email` varchar(60) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `type` varchar(60) NOT NULL,
  `content_type` enum('text/html','text/plain') DEFAULT NULL,
  `body` longtext NOT NULL,
  `status` enum('STARTED','SENT','PENDING') CHARACTER SET latin1 DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

alter table `ohrm_notifications_definition`
  add constraint foreign key (`employee_id`) references `hs_hr_employee` (`emp_number`) on delete cascade,
  add constraint foreign key (`nt_id`) references `ohrm_notifications_type` (`nt_id`) on delete cascade;


INSERT INTO `ohrm_notifications_type` (`nt_id`, `name`, `module`, `text`, `subject`, `param`, `notification_class`, `recipient_types`) VALUES
(1, 'DOB Notification', 'PIM', 'Hi RECIPINTNAME ,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''s Date of Birth is getting closer and only DAYSLEFT due for the date.\r\n\r\nRegards,\r\nHR Team', 'DOB Notification', '', 'EmployeeEventNotification', 'staffOwningEvent,Admin'),
(2, 'Driving License Expiry Date', 'PIM', 'Hi RECIPINTNAME,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''s Driving License Expiry Date is getting closer and only DAYSLEFT is due for the expiry date. Please take necessary steps needed to renew it.\r\n\r\nRegards,\r\nHR Team', 'Driving License Expiry Date', '', 'EmployeeEventNotification', 'staffOwningEvent,Admin'),
(3, 'Immigration Expiry Notification', 'PIM', 'Hi RECIPINTNAME,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''s Passport/Visa Expiry Date is getting closer and only DAYSLEFT is due for the expiry date.\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', 'Immigration Expiry Notification', '', 'EmployeeEventNotification', 'staffOwningEvent,Admin'),
(4, 'Licenses Expiry Notification', 'PIM', 'Hi RECIPINTNAME,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''''s License Expiry Date is getting closer and only DAYSLEFT is due for the expiry date.\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', 'Licenses Expiry Notification', NULL, 'EmployeeEventNotification', 'staffOwningEvent,Admin'),
(5, 'Memberships Expiry Notification', 'PIM', 'Hi RECIPINTNAME,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''''s Membership Expiry Date is getting closer and only DAYSLEFT is due for the expiry date.\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', 'Memberships Expiry Notification', NULL, 'EmployeeEventNotification', 'staffOwningEvent,Admin'),
(6, 'Employee Contracts Notification', 'PIM', 'Hi RECIPINTNAME,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''s Contract Extension End Date is getting closer and only DAYSLEFT is due for the expiry date.\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', 'Employee Contracts Notification', NULL, 'EmployeeEventNotification', 'staffOwningEvent,Admin'),
(7, 'Retirement Notification', 'PIM', 'Hi RECIPINTNAME,\r\n\r\nThis is to remind that FIRSTNAME LASTNAME''s retirement date is getting closer and only DAYSLEFT are due for the retirement date. \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 ', 'Retirement Notification', '{"Year": "sfWidgetFormInputText"}', 'RetirementEventNotification', 'staffOwningEvent,Admin');

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('notification', 1);

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

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('View Notifications', @module_id, 'viewNotification');

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

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Define Notification', @module_id, 'defineNotification');

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

SET @admin_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Admin' AND `level` = 1); 

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Notifications', @view_notification_screen_id, @admin_menu_id, 2, 1000, '', 1);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @view_notification_screen_id, 1, 1, 1, 1),
(@admin_role_id, @define_notification_screen_id, 1, 1, 1, 0);

UPDATE `ohrm_menu_item` SET `uisref`='leave.notifications' WHERE `menu_title`='Notifications';