SET @socail_media_data_group_id := NULL;

INSERT INTO `ohrm_token`(`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`,`parameters`) VALUES
('directory.employeeFacebookContact', 'Employee Facebook Contact','CustomFacebookTokenGenerator', null, null, @socail_media_data_group_id, 0, 0,'visibility=> off,group=>employeeSocailMedia'),
('directory.employeeTwitterContact', 'Employee Twitter Contact','CustomTwitterTokenGenerator', null, null, @socail_media_data_group_id, 0, 0,'visibility=> on,group=>employeeSocailMedia'),
('directory.employeeLinkedinContact', 'Employee LinkedIn Contact','CustomLinkedinTokenGenerator', null, null, @socail_media_data_group_id, 0, 0,'visibility=> on,group=>employeeSocailMedia');


SET @first_token_id := (SELECT LAST_INSERT_ID());
SET @directory_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='directory');

INSERT INTO `ohrm_restricted_module_token`(`token_id`, `restricted_module`)
SELECT  `id`, @directory_module_id FROM `ohrm_token` WHERE `id` >= @first_token_id;


-- Directory Config Setting screen

set @module_id := (SELECT id FROM ohrm_module WHERE name = 'admin');

INSERT INTO ohrm_screen (name, module_id, action_url) VALUES
('Update Directory TokenDetails', @module_id , 'updateDirectoryTokenDetails'),
('Directory Configuration', @module_id , 'viewDirectoryConfig');

set @view_screen_id := (SELECT `id` FROM  `ohrm_screen` WHERE  `action_url` LIKE  'viewDirectoryConfig');
set @update_screen_id := (SELECT `id` FROM  `ohrm_screen` WHERE  `action_url` LIKE  'updateDirectoryTokenDetails');
set @admin_id := (SELECT `id` FROM  `ohrm_user_role` WHERE  `name` LIKE  'Admin');

INSERT INTO ohrm_menu_item ( menu_title , menu_category, screen_id, parent_id, level, order_hint , url_extras, status ) VALUES
('Directory Configuration', NULL , @view_screen_id, 23, 3, '600', null, 1);

set @user_role_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen ( `user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(@admin_id, @view_screen_id, '1', '1', '1', '1'),
(@admin_id, @update_screen_id, '1', '1', '1', '1');


-- Create `hs_hr_emp_directory_email` table

CREATE TABLE `hs_hr_emp_directory_email`(
        `emp_number` int(7) ,
        `status` tinyint(1) DEFAULT FALSE ,
        PRIMARY KEY(emp_number)
)engine=innodb default charset=utf8;

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;
-- Table structure for table `hs_hr_emp_relation_catagory`

CREATE TABLE IF NOT EXISTS `hs_hr_emp_relation_catagory` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `display_name` varchar(255) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `enable` tinyint(1) NOT NULL DEFAULT '1',
  `listing_order` int(2) NOT NULL DEFAULT '10',
  `parameters` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- Dumping data for table `hs_hr_emp_relation_catagory`

INSERT INTO `hs_hr_emp_relation_catagory` (`id`, `display_name`, `class_name`, `enable`, `listing_order`, `parameters`) VALUES
(1, 'Your reporting manager details are as below and  so please ask for them.', 'SupervisorRelation', 0, 1, '{"allow_duplicate":"no","supervisor_chain_enabled": "yes","limit":2,"sortField":"firstName"}'),
(2, 'People in your location', 'LocationBasedRelation', 0, 10, '{"allow_duplicate":"no"}'),
(3, 'We thought it would be helpful if you got to know about some of your fellow team members.', 'RecentlyJoinedRelation', 1, 10, '{"allow_duplicate":"yes","excluded_job_title":["Vice President"]}'),
(4, 'Your Subordinates', 'SubordinateRelation', 0, 10, '{"allow_duplicate":"no","include_sub_chain":"yes","sortField":"firstName"}'),
(5, 'Vice President', 'DefaultRelation', 0, 10, '{"sql":"SELECT e.emp_number FROM hs_hr_employee e WHERE e.job_title_code in (SELECT id FROM ohrm_job_title j where j.job_title=:jobTitle)","sql_para":{":jobTitle":"Vice President"}}');

-- 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}', 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_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.
');


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

Welcome to %companyName%, we are looking forward to you joining us on %joiningDate%.
%supervisorNames%
%content%

<br>
<br>
Regards, <br>
The team at  %companyName%

');


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


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}', 'Custom', 'Affected Employee');

set @notification_subscriber_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`) VALUES (@notification_subscription_id, @notification_subscriber_id);

ALTER TABLE  `ohrm_employee_social_media` ADD  `allow_share` TINYINT(2) NOT NULL DEFAULT  '0';

