-- Directory information notification to new employees
SET @scheduled_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Scheduled');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @directory_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='directory');


INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`,`parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Welcome Email', 0, @scheduled_notification_type, 'DirectoryEmailProcessor','{ "email_subscriber_schedule": true, "auxiliaryTokens": [ { "id": "SubscriberName", "name": "SubscriberName", "display_name": "Subscriber Name", "section": "General", "tab_name": "Employee Welcome Email", "value_getters": "SubscriberNameValueGetter" }, { "id": "CompanyName", "name": "CompanyName", "display_name": "Company Name", "section": "General", "tab_name": "Employee Welcome Email", "value_getters": "CompanyNameValueGetter" }, { "id": "JoiningDate", "name": "JoiningDate", "display_name": "JoiningDate", "section": "General", "tab_name": "Employee Welcome Email", "value_getters": "JoiningDateValueGetter" }, { "id": "SupervisorNames", "name": "SupervisorNames", "display_name": "Supervisor Names", "section": "General", "tab_name": "Employee Welcome Email", "value_getters": "SupervisorNamesValueGetter" }, { "id": "Content", "name": "Content", "display_name": "Content", "section": "General", "tab_name": "Employee Welcome Email", "value_getters": "ContentValueGetter" }, { "id": "EmpPicture", "name": "EmpPicture", "display_name": "Employee Profile Picture", "section": "General", "tab_name": "Employee Welcome Email", "value_getters": "EmpProfilePictureValueGetter" } ] }', NULL, NULL,@directory_module);

SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Employee welcome email sent to Subscriber',
'Welcome %FirstName% to %CompanyName%',
'Dear %SubscriberName%,<br><br>

We are excited to introduce our newest team member, %EmployeeFullName%, who will be joining us as a %JobTitle% under the %SubUnit% department. We believe %EmployeeFullName% will be a great addition to our family and help us achieve our goals more efficiently.<br><br>
<b>Brief Overview:</b>%EmpPicture%
<br><ul><li><b>Employee ID:</b> %EmployeeId%</li><li><b>Full Name:</b> %EmployeeFullName%</li><li><b>Job Title:</b> %JobTitle%</li><li><b>Job Category:</b> %JobCategory%</li><li><b>Supervisor:</b> %SupervisorNames%</li><li><b>Cost Center:</b> %CostCenter%</li><li><b>Location:</b> %Location%</li><li><b>Work Email:</b> %WorkEmail%</li><li><b>Work Telephone:</b> %WorkTelephone%</li><li><b>Mobile:</b> %Mobile%</li><li><b>Gender:</b> %Gender%</li><li><b>EEO Race:</b> %EeoRace%</li><li><b>Previous Work Experience:</b> %WorkExperience%</li></ul><br>
Please join me in extending a warm welcome to %EmployeeFullName%. Let’s all work together to support and assist our new colleague in understanding our processes and integrating smoothly into our team.<br><br>
Feel free to reach out to %EmployeeFullName% via the contact details mentioned above, introduce yourself, and maybe schedule a short virtual coffee chat!<br><br>
Welcome aboard, %EmployeeFullName%! We look forward to achieving great things together.
<br><br>
Warm Regards!<br>
The team at  %CompanyName%
<br><br>
You are getting this email because you are a subscriber.');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Employee welcome email send to Employee',
'Welcome to %CompanyName%',
'Dear %SubscriberName%,<br><br>

    On behalf of the entire team at %CompanyName%, I would like to extend a warm welcome to you! We are delighted to have you join our family, and we look forward to working together and achieving great things.
<br>
Starting a new journey can be both exciting and overwhelming, but rest assured that we are here to support you every step of the way.<br>
Our company culture thrives on collaboration, innovation, and inclusivity, and we believe that your unique skills and experiences will be invaluable to our team.
<br><br>
%SupervisorNames%
%Content%
<br><br>
Please feel free to reach out to the HR department if you have any questions or need further information. We are here to ensure a smooth onboarding experience for you.<br>
Once again, welcome to %CompanyName%! We are excited to have you on board, and we look forward to a successful journey together.<br><br>
Warm Regards!<br>
The team at  %CompanyName%

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

-- Adding current employees records to `hs_hr_emp_directory_email' table to avoid sending email to them

INSERT INTO `hs_hr_emp_directory_email`(`emp_number`)
SELECT emp_number FROM `hs_hr_employee`;

UPDATE `hs_hr_emp_directory_email`
SET `status`=1 ;


set @directoryUpdates_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Welcome Email');

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `workflow_item_id`, `hidden`) VALUES (@directoryUpdates_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, '0');

set @notification_subscription_id := (SELECT `id` FROM `ohrm_notification_subscription` WHERE `notification_id`=@directoryUpdates_id);

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
  (@email_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) VALUES (@notification_subscription_id, @notification_subscriber_id, @template_id);






