SET @module_id := (SELECT id FROM ohrm_module WHERE name = 'admin');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
  ('Update Directory TokenDetails', @module_id, 'updateDirectoryTokenDetails');
SET @update_screen_id := LAST_INSERT_ID();
SET @admin_id := (SELECT `id` FROM  `ohrm_user_role` WHERE  `name` LIKE  'Admin');
INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`)
  VALUES (@admin_id, @update_screen_id, '1', '1', '1', '1');

ALTER TABLE hs_hr_emp_directory_email ADD CONSTRAINT fk_hs_hr_emp_directory_email_hs_hr_employee FOREIGN KEY (  `emp_number` ) REFERENCES hs_hr_employee(  `emp_number` ) ON DELETE CASCADE;

ALTER TABLE hs_hr_emp_relation_catagory CONVERT TO CHARACTER SET utf8;
ALTER TABLE hs_hr_emp_relation_catagory MODIFY  `display_name` VARCHAR(255) NOT NULL;

SET @notification_id :=(SELECT id FROM  `ohrm_notification` WHERE `ohrm_notification`.`name` ='New starters receive a welcome email');
UPDATE  `ohrm_notification` SET  `name` = 'Employee Welcome Email', `parameters` = '{"email_subscriber_schedule":true}'
  WHERE `ohrm_notification`.`id` = @notification_id;

SET @affected_employee_template_id := (SELECT id FROM ohrm_notification_template WHERE notification_id = @notification_id
  AND subject = 'Welcome to %companyName%' AND performer_role IS NULL AND recipient_role IS NULL LIMIT 1);

INSERT INTO `ohrm_notification_template` (`id`, `notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  VALUES (NULL, @notification_id, @email_media, 'en_US', NULL, NULL, 'Welcome %firstName% to %companyName%', 'Dear %subscriberName%,<br><br>

%firstName% %lastName% is joining with us on %joiningDate%.
<br>
<br>
Regards,
<br>
The team at %companyName%
<br>
You are getting this email because you are a subscriber.
');

SET @next_template_id := (SELECT MAX(id) + 1 FROM ohrm_notification_template);

UPDATE ohrm_notification_template SET `recipient_role` = 'Affected Employee', id = @next_template_id
  WHERE id = @affected_employee_template_id;

SET @autoIncrementSql = CONCAT('ALTER TABLE ohrm_notification_template AUTO_INCREMENT = ', @next_template_id + 1);
PREPARE st FROM @autoIncrementSql;
EXECUTE st;
