CREATE TABLE `ohrm_bradford_factor_threshold` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `threshold` decimal(10,2),
  `subject` varchar(255) DEFAULT NULL,
  `template` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `threshold` (`threshold`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `ohrm_bradford_factor_empoyee_threshold` (
  `empNumber` int(9) NOT NULL,
  `threshold` int(9) NOT NULL,
  `processDate` date NOT NULL,
  `subscriptionId` int(11) NOT NULL default 0,
  PRIMARY KEY (`empNumber`,`threshold`, `subscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `ohrm_bradford_factor_threshold` (`id`, `threshold`, `subject`, `template`) VALUES
(NULL, '50', 'Minor', 'Sufficient days for a manager to show concern and advise on possible disciplinary of financial actions, should more absences occur during an identified period.'),
(NULL, '75', 'Warning', 'Sufficient days for a manager to start disciplinary action (oral warning, written warning, formal monitoring etc)'),
(NULL, '100', 'Major', 'Sufficient days for a manager to consider dismissal');


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 @pim_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='leave');

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Bradford Factor Threshold', 0, @scheduled_notification_type, 'BradfordFactorThresholdNotificationProcessor', '{"email_subscriber_schedule":true}', NULL, NULL,@pim_module );
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US',
'Bradford Factor Threshold',
'Bradford Factor Threshold (%subject%)',
'<p>Hi %subscriberName%,<br /><br />%body%<br /><br />Thank you<br />System Admin<br />(This is an auto-generated email, please do not reply!)</p>');

SET @leave_module_id = (SELECT `id` FROM  `ohrm_module` WHERE `name` = 'leave');
SET @view_leave_module_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View Leave Module');

SET @admin_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` ='Admin');
SET @ess_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` ='ESS');
SET @supervisor_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` ='Supervisor');
SET @config_admin_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` ='ConfigAdmin');



INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
    ('View  BradfordFactor Threshold', @leave_module_id, 'bradfordFactorThreshold', 'leave_bradfordFactorThreshold'),
    ('Get BradfordFactor Threshold', @leave_module_id, 'getBradfordFactorThresholdJson', NULL),
    ('Delete BradfordFactor Thresholds', @leave_module_id, 'deleteBradfordFactorThresholds', NULL);

SET @view_bradfordfactor_thresholds__screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'bradfordFactorThreshold');
SET @define_bradfordfactor_thresholds_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'getBradfordFactorThresholdJson');
SET @delete_bradfordfactor_thresholds_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'deleteBradfordFactorThresholds');

SET @compensation_pillar_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Compensation' AND `level` = 1);
SET @pto_leave_management_module_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Leave' AND `parent_id` = @compensation_pillar_menu_id);
SET @configure_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Configure' AND `parent_id` = @pto_leave_management_module_menu_id  );

SET @leave_menu_status := (SELECT `status`  FROM `ohrm_menu_item` WHERE `menu_title` LIKE 'Leave Period' limit 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
    ('Bradford Factor Threshold', @view_bradfordfactor_thresholds__screen_id, @configure_menu_id, 4, 900, NULL, 0);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role_id, @view_bradfordfactor_thresholds__screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @define_bradfordfactor_thresholds_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @delete_bradfordfactor_thresholds_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @view_bradfordfactor_thresholds__screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @define_bradfordfactor_thresholds_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @delete_bradfordfactor_thresholds_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('bradfordfactor_threshold', 'Leave - BradfordFactor Threshold', 1, 1, 1, 1);

SET @bradfordfactor_threshold_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'bradfordfactor_threshold');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_user_role_id, @bradfordfactor_threshold_data_group_id, 1, 1, 1, 1, 0),
(@config_admin_role_id, @bradfordfactor_threshold_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@bradfordfactor_threshold_data_group_id, @view_bradfordfactor_thresholds__screen_id, 1),
(@bradfordfactor_threshold_data_group_id, @define_bradfordfactor_thresholds_screen_id, 2),
(@bradfordfactor_threshold_data_group_id, @define_bradfordfactor_thresholds_screen_id, 3),
(@bradfordfactor_threshold_data_group_id, @delete_bradfordfactor_thresholds_screen_id,4),
(@bradfordfactor_threshold_data_group_id, @view_leave_module_screen_id, 1);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(1, @bradfordfactor_threshold_data_group_id);


