ALTER TABLE `hs_hr_employee`  ADD `has_work_week` TINYINT(2) NOT NULL DEFAULT '0' AFTER `added_date`;

-- EMPLOYEE WORK WEEK INSTALLATION --

CREATE TABLE `ohrm_week_days` (
`id` INT(11) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY  (`id`)
) engine=innodb DEFAULT charset=utf8;

CREATE TABLE `ohrm_day_type` (
`id` INT(11) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY  (`id`)
) engine=innodb DEFAULT charset=utf8;

INSERT INTO `ohrm_week_days` (`id`, `name`) VALUES
(1, 'monday'),
(2, 'tuesday'),
(3, 'wednesday'),
(4, 'thursday'),
(5, 'friday'),
(6, 'saturday'),
(7, 'sunday');

INSERT INTO `ohrm_day_type` (`id`, `name`) VALUES
(0, 'working day'),
(8, 'non-working day');

CREATE TABLE `ohrm_employee_work_week` (
  `emp_number` INT(11) NOT NULL,
  `day_id` INT(11) NOT NULL,
  `hours_per_day` DECIMAL(7,5) DEFAULT NULL,
  `shift_start_time` TIME DEFAULT NULL,
  `shift_end_time` TIME DEFAULT NULL,
  `lunch_start_time` TIME DEFAULT NULL,
  `lunch_end_time` TIME DEFAULT NULL,
  `day_type_id` INT (11) NOT NULL,
  PRIMARY KEY  (`emp_number`, `day_id`)
) engine=innodb DEFAULT charset=utf8;

ALTER TABLE `ohrm_employee_work_week`
ADD CONSTRAINT FOREIGN KEY (`day_id`) REFERENCES `ohrm_week_days` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
ADD CONSTRAINT FOREIGN KEY (`day_type_id`) REFERENCES `ohrm_day_type` (`id`) ON DELETE CASCADE;


SET @pim_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'pim' LIMIT 1);

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
('Employee Work Week', @pim_module_id, 'viewEmployeeWorkWeek');

SET @employee_work_week_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewEmployeeWorkWeek' LIMIT 1);

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @admin_role_type_id := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
SET @supervisor_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @supervisor_role_type_id := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Supervisor');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');
SET @ess_role_type_id := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'ESS');
SET @hod_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @employee_work_week_screen_id, 1, 0, 1, 0),
(@supervisor_role_id, @employee_work_week_screen_id, 1, 0, 1, 0),
(@ess_role_id, @employee_work_week_screen_id, 1, 0, 0, 0),
(@hod_id, @employee_work_week_screen_id, 1, 0, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('pim_employee_work_week', 'PIM - Work Week', 1, 1, 1, 1);
SET @pim_employee_work_week_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'pim_employee_work_week');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@pim_employee_work_week_data_group_id, @employee_work_week_screen_id, '1', 1);


INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(@admin_role_type_id, @pim_employee_work_week_data_group_id, 1, 0, 1, 0),
(@ess_role_type_id, @pim_employee_work_week_data_group_id, 1, 0, 0, 0),
(@supervisor_role_type_id, @pim_employee_work_week_data_group_id, 1, 0, 1, 0);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id` ,`data_group_id` ,`can_read` ,`can_create` ,`can_update` ,`can_delete` ,`self`) VALUES
(@admin_role_id, @pim_employee_work_week_data_group_id, '1', '0', '1', '0', '0'),
(@ess_role_id, @pim_employee_work_week_data_group_id, '1', '0', '0', '0', '1'),
(@supervisor_role_id, @pim_employee_work_week_data_group_id, '1', '0', '1', '0', '0'),
(@hod_id, @pim_employee_work_week_data_group_id, '1', '0', '1', '0', '0');


INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `level`, `order_hint`, `status`) VALUES
('Work Week', 'pim_left_menu', @employee_work_week_screen_id, 0, 65, 1);

SET @work_week_menu_item_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Work Week' AND `menu_category` = 'pim_left_menu');

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES
(@work_week_menu_item_id, @pim_employee_work_week_data_group_id);

SET @audit_pim_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='PIM');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('WorkWeek', 'Work Week', 'Employee', @audit_pim_module_id);


SET @workweek_audit_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'WorkWeek' LIMIT 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(@workweek_audit_section_id, 'ADD WORK WEEK DETAILS', 'Add Work Week Details'),
(@workweek_audit_section_id, 'UPDATE WORK WEEK DETAILS', 'Update Work Week Details'),
(@workweek_audit_section_id, 'DELETE WORK WEEK DETAILS', 'Delete Work Week Details');


CREATE TABLE `ohrm_audittrail_pim_work_week_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- END OF EMPLOYEE WORK WEEK INSTALLATION --


INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('pim.work_week_default_shift_start_time', '08:00'),
('pim.work_week_default_shift_end_time', '17:00'),
('pim.work_week_default_lunch_start_time', ''),
('pim.work_week_default_lunch_end_time', ''),
('pim.work_week_default_working_days', 'monday,tuesday,wednesday,thursday,friday'),
('pim.work_week_default_non_working_days', 'saturday,sunday');

-- Employee Work week installation --

UPDATE `ohrm_config` SET `value`  = 'StaticWorkWeekSchedule' WHERE
`property`='leave.work_schedule_implementation' ;

-- End of employee Work week installation --