CREATE TABLE `ohrm_working_weekend` (
  `id` INT UNSIGNED AUTO_INCREMENT,
  `description` TEXT DEFAULT NULL,
  `date` DATE DEFAULT NULL,
  `length` INT UNSIGNED DEFAULT NULL,
  `operational_country_id` INT UNSIGNED DEFAULT NULL,
  `shift_in` time NOT NULL,
  `shift_out` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;


ALTER TABLE `ohrm_working_weekend`
ADD CONSTRAINT `fk_ohrm_working_weekend_ohrm_operational_country`
    FOREIGN KEY (`operational_country_id`)
    REFERENCES `ohrm_operational_country` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `ohrm_working_weekend_location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `working_weekend_id` int(10) unsigned NOT NULL,
  `location_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `working_weekend_id` (`working_weekend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ohrm_working_weekend_location`
  ADD CONSTRAINT `ohrm_working_weekend_location_ibfk_1` FOREIGN KEY (`working_weekend_id`) REFERENCES `ohrm_working_weekend` (`id`) ON DELETE CASCADE;

ALTER TABLE `ohrm_working_weekend_location`
  ADD CONSTRAINT `ohrm_working_weekend_location_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE;

CREATE TABLE `ohrm_audittrail_leave_working_weekend_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `action_description` TEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_leave_working_weekend_trail_idx_app_id (app_id),
  INDEX audit_leave_working_weekend_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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 WorkingWeekend List', @leave_module_id, 'viewWorkingWeekendList', 'leave_viewWorkingWeekendList'),
    ('Define WorkingWeekend', @leave_module_id, 'defineWorkingWeekend', NULL),
    ('Delete WorkingWeekend', @leave_module_id, 'deleteWorkingWeekend', NULL);

SET @view_working_weekendList_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'viewWorkingWeekendList');
SET @define_working_weekend_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'defineWorkingWeekend');
SET @delete_working_weekend_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'deleteWorkingWeekend');

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
    ('Working Weekends', @view_working_weekendList_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_working_weekendList_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @define_working_weekend_screen_id, 1, 1, 1, 1),
(@admin_user_role_id, @delete_working_weekend_screen_id, 1, 1, 1, 1) ;

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('working_weekends', 'Leave - Working Weekends', 1, 1, 1, 1);

SET @working_weekends_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'working_weekends');

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, @working_weekends_data_group_id, 1, 1, 1, 1, 0) ;

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@working_weekends_data_group_id, @view_working_weekendList_screen_id, 1),
(@working_weekends_data_group_id, @define_working_weekend_screen_id, 2),
(@working_weekends_data_group_id, @define_working_weekend_screen_id, 3),
(@working_weekends_data_group_id, @delete_working_weekend_screen_id,4),
(@working_weekends_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, @working_weekends_data_group_id);

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
( 'leave.working_weekend_add', 'WorkingWeekendAddListener', 1, 27),
( 'leave.working_weekend_delete', 'WorkingWeekendDeleteListener', 1, 28),
( 'leave.working_weekend_update', 'WorkingWeekendUpdateListener', 1, 29);


SET @audit_leave_module_id = (SELECT `id` FROM  `ohrm_audittrail_module` WHERE `name` = 'Leave');
INSERT INTO  `ohrm_audittrail_section` (`id`, `name`, `description`, `affected_entity`, `module_id`, `is_deleted`) VALUES (NULL, 'WorkingWeekend', 'Working Weekends', 'Working Weekend', @audit_leave_module_id, '0');
SET @audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'WorkingWeekend');

INSERT INTO  `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(@audittrail_section_id , 'ADD WORKINGWEEKEND', 'Add Working Weekend'),
(@audittrail_section_id , 'DELETE WORKINGWEEKEND', 'Delete Working Weekend'),
(@audittrail_section_id , 'UPDATE WORKINGWEEKEND', 'Update Working Weekend');


DELIMITER $$

DROP TRIGGER IF EXISTS `watch_insert_leave_working_weekend_trail`;$$

CREATE TRIGGER watch_insert_leave_working_weekend_trail
AFTER INSERT ON ohrm_audittrail_leave_working_weekend_trail
FOR EACH ROW BEGIN
  CALL audit_insert_to_ohrm_audittrail_all(NEW.action_time, NEW.action_owner_id, NEW.action_owner_name, NEW.action,NEW.action_description,NEW.version_id,NEW.affected_entity_id,NEW.affected_entity_name,'Working Weekends');
END;$$

DELIMITER ;
