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

DELETE FROM `ohrm_screen` WHERE action_url IN ('viewLeaveSummaryAdvanced', 'saveLeaveEntitlementsAdvanced');

-- Insert screens
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Edit Leave Entitlement Advanced',4,'editLeaveEntitlementAdvanced');

-- Give admin access to leave config screens
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('editLeaveEntitlementAdvanced')
    AND r.`name` = 'Admin';

-- Remove addLeaveEntitlementAdvanced permission from supervisor (this was given in previous versions)
DELETE FROM ohrm_user_role_screen WHERE 
user_role_id = (SELECT id FROM ohrm_user_role r WHERE r.name = 'Supervisor') AND 
screen_id = (SELECT id FROM ohrm_screen s WHERE s.action_url = 'addLeaveEntitlementAdvanced');


-- data group screen permissions for screens which are routed 
-- viewLeaveList => viewLeaveListAdvanced
INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewLeaveList' and ns.action_url = 'viewLeaveListAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewHolidayList' and ns.action_url = 'viewHolidayListAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'defineHoliday' and ns.action_url = 'defineHolidayAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'defineWorkWeek' and ns.action_url = 'defineWorkWeekAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'leaveTypeList' and ns.action_url = 'leaveTypeListAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'defineLeaveType' and ns.action_url = 'defineLeaveTypeAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewLeaveBalanceReport' and ns.action_url = 'viewLeaveBalanceReportAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewLeaveEntitlements' and ns.action_url = 'viewLeaveEntitlementsAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'addLeaveEntitlement' and ns.action_url = 'addLeaveEntitlementAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'editLeaveEntitlement' and ns.action_url = 'editLeaveEntitlementAdvanced';

-- screens used to update leave rules (give access if user has read/update permissions for leave types)
INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'defineLeaveType' and dgs.permission in (1, 3) and
ns.action_url in ('defineLeaveTypeRule', 'defineLeaveTypeRuleWhoCanApply', 
'defineLeaveTypeRuleApplyRestriction', 'defineLeaveTypeRuleAccrualRule', 'defineLeaveTypeCarryForwardRule');

UPDATE `hs_hr_config` SET `value`  = 'AdvancedLeaveWorkSchedule' WHERE 
`key`='leave.work_schedule_implementation';
