SET @other_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Other');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @recruitment_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='recruitment');
SET @hiring_manager_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'HiringManager');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
    ('Recruitment Document Notification', 1, @other_notification_type, 'RecruitmentCandidateDocumentEmailProcessor',
        '{"data_group_permissions":{"data_group":"recruitment_configuration","roles_to_exclude":"HiringManager"},"list_action_rights":{"change_status":false,"change_subscribers":true,"copy_templates":true},"extra_roles":["HiringManager"],"notifyAffectedCandidateOptionVisible":true}', null, null,@recruitment_module, 1);
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`,`is_default`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US', 1,
     'Default Template',
     '%VacancyName% Vacancy at %OrganizationName%',
     '<p>Dear %CandidateFirstName% %CandidateLastName%,</p>
<p>Thank you for taking the time to apply for the "%VacancyName%" vacancy at %OrganizationName%.</p>
<p>Please find the attached "%DocumentName%".</p>
<p>In the meantime, please do not hesitate to reach out to me by email or call me directly if you have any questions or concerns.</p>
<p>Best regards,</p>
<p>%hiringManagerEmailSignature%</p>');

SET @subscriber_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id) VALUES (@notification_id);

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, address_part, type, name) VALUES
    (@email_media,'to', 'Custom', 'Candidate');

SET @subscriber_candidate_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, address_part, type, user_role_id) VALUES
    (@email_media,'cc', 'UserRole', @hiring_manager_user_role);

SET @subscriber_hiring_manager_id := LAST_INSERT_ID();

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

SET @recruitment_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='recruitment');

SET @default_email_template_id := (SELECT `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `is_default` = 1 AND `notification_id` = @notification_id);

INSERT INTO `ohrm_document_template` ( `name`, `description`, `template_string`, `page_size`, `module_id`, `is_template_string_editable`, `notification_subscriber_subscription_template_id` ) VALUES ('Simple Job Offer Letter', 'Simple job offer document with basic job offer tokens', '<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">#organizationAddress#</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">#CurrentDate#</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">#candidateFullName#</span><br /><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">#candidateJobOfferCandidateAddress#</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">Dear #candidateFirstName#,</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">#organizationName# is delighted to offer you the position of #vacancyJobTitle# with an anticipated start date of #candidateJobOfferJoinedDate#.</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">You will report directly to #candidateJobOfferSupervisorName#, #candidateJobOfferSupervisorJobTitle#.</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">The starting annual salary for this position is #candidateJobOfferAnnualBasicPayment#.</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">Your employment with #organizationName# is on a #candidateJobOfferEmploymentStatus# basis and is subject to the successful completion of our background check and any other pre-employment screenings that we may deem necessary.</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">To accept this offer, please sign and date this letter below and return it to us by #candidateJobOfferEndDate#. If you have any questions, please do not hesitate to contact me.</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">We look forward to welcoming you to our team.</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">Sincerely,</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">#hiringManagerAndTitle#</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">______________________________________________________________________________________________________</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">&nbsp;</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">Candidate Signature: ______________________________</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">Candidate Printed Name: ______________________________</span></p>
<p><span style="font-size: 12pt; font-family: ''times new roman'', times, serif;">Date: ______________________________</span></p>', 'A4', @recruitment_module_id, 1, @default_email_template_id);

SET @recruitment_default_document_template_id := (SELECT LAST_INSERT_ID() FROM `ohrm_document_template`);

INSERT INTO `ohrm_document_template_vacancy` ( `document_template_id`, `vacancy_id` ) VALUES ( @recruitment_default_document_template_id, null );

SET @job_offer_event_type_id :=  (SELECT `id` FROM `ohrm_vacancy_event_type` WHERE `type` = 'Job Offer' );

INSERT INTO `ohrm_document_template_vacancy_event_type` ( `document_template_id`, `vacancy_event_type_id` ) VALUES (@recruitment_default_document_template_id, @job_offer_event_type_id);


