UPDATE `ohrm_menu_item` SET `status` = 1 WHERE `menu_title`='Email Subscriptions';

INSERT INTO `ohrm_email_notification` (`name`, `is_enable`) VALUES 
('Vacancy Publishing', 0),
('Vacancy Inactivation', 0),
('Vacancy Closure', 0);

INSERT INTO `ohrm_email` (`name`) VALUES ('vacancy.publish');
SET @vacancy_publish_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('vacancy.unpublish');
SET @vacancy_unpublish_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('vacancy.close');
SET @vacancy_close_email_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_email_processor` (`email_id`, `class_name`) VALUES 
(@vacancy_publish_email_id, 'VacancyEmailProcessor'),
(@vacancy_unpublish_email_id, 'VacancyEmailProcessor'),
(@vacancy_close_email_id, 'VacancyEmailProcessor');


INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_publish_email_id, 'en_US', null, 'Global Admin', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_publish_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_publish_email_id, 'en_US', null, 'Subscriber', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');


INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_unpublish_email_id, 'en_US', null, 'Global Admin', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_unpublish_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_unpublish_email_id, 'en_US', null, 'Subscriber', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');


INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_close_email_id, 'en_US', null, 'Global Admin', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_close_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@vacancy_close_email_id, 'en_US', null, 'Subscriber', 'Notification: %VacancyName% has been %StateName%', 
'Hi %EmployeeFirstName%,

The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

You were sent this mail, as you are a %UserRole%.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_notification` (`name`, `is_enable`) VALUES 
('Interview schedules', 0),
('Interview Passes', 0),
('Interview Failures', 0),
('Job Offers', 0),
('Offer Declinations', 0),
('Candidate Hires', 0);

INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Assigned a Vacancy');
SET @candidate_assign_vacancy_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Schedule Interview');
SET @candidate_schedule_interview_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Mark Interview Passed');
SET @candidate_mark_interview_passed_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Mark Interview Failed');
SET @candidate_mark_interview_failed_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Offer Job');
SET @candidate_offer_job_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Decline Offer');
SET @candidate_decline_offer_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Hire');
SET @candidate_hire_email_id := (SELECT LAST_INSERT_ID());


INSERT INTO `ohrm_email_processor` (`email_id`, `class_name`) VALUES 
(@candidate_assign_vacancy_email_id, 'CandidateEmailProcessor'),
(@candidate_schedule_interview_email_id, 'CandidateEmailProcessor'),
(@candidate_mark_interview_passed_email_id, 'CandidateEmailProcessor'),
(@candidate_mark_interview_failed_email_id, 'CandidateEmailProcessor'),
(@candidate_offer_job_email_id, 'CandidateEmailProcessor'),
(@candidate_decline_offer_email_id, 'CandidateEmailProcessor'),
(@candidate_hire_email_id, 'CandidateEmailProcessor');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_assign_vacancy_email_id, 'en_US', null, 'Candidate', 'Notification: Your application is received for %VacancyName%', 
'Dear %CandidateFirstName%  %CandidateLastName%

Your application for the position of  %VacancyName% was received.
Thank you for your interest in %OrganizationName%

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_schedule_interview_email_id, 'en_US', null, 'Interviewer', 'Notification: An interview has been scheduled for %VacancyName% with %CandidateFirstName% %CandidateLastName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been selected for an interview on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are an %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_schedule_interview_email_id, 'en_US', null, 'Hiring Manager', 'Notification: An interview has been scheduled for %VacancyName% with %CandidateFirstName% %CandidateLastName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been selected for an interview on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_schedule_interview_email_id, 'en_US', null, 'Subscriber', 'Notification: An interview has been scheduled for %VacancyName% with %CandidateFirstName% %CandidateLastName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been selected for an interview on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_schedule_interview_email_id, 'en_US', null, 'Candidate', 'Notification: An interview has been scheduled for %VacancyName% on %ScheduledDate%', 
'Hi %CandidateFirstName%  %CandidateLastName%,

You have been selected for an interview on %ScheduledDate%
Interview type: %InterviewType%
We expect your presence on time.

Thank you.
This is an automated notification.');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_mark_interview_passed_email_id, 'en_US', null, 'Interviewer', 'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are an %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_mark_interview_passed_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_mark_interview_passed_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_mark_interview_failed_email_id, 'en_US', null, 'Interviewer', 'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are an %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_mark_interview_failed_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_mark_interview_failed_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_offer_job_email_id, 'en_US', null, 'Regional Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_offer_job_email_id, 'en_US', null, 'Global Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_offer_job_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_offer_job_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_decline_offer_email_id, 'en_US', null, 'Regional Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_decline_offer_email_id, 'en_US', null, 'Global Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_decline_offer_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_decline_offer_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_hire_email_id, 'en_US', null, 'Regional Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_hire_email_id, 'en_US', null, 'Global Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_hire_email_id, 'en_US', null, 'Hiring Manager', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_hire_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%', 
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES 
(@candidate_hire_email_id, 'en_US', null, 'Candidate', 'Welcome: You are hired for %VacancyName%', 
'Hi %CandidateFirstName%,

It is with pleasure that we confirm that you are selected for %VacancyName%. Please contact our HR Team for further details.
We are delighted to have you as a part of the team.
Welcome to the team.

Thank you.
This is an automated notification. ')


