SET @folder_id = (SELECT id FROM ohrm_report_folder WHERE name = 'Request Desk');
SET @report_id := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'resolution_time_report');
SET @monthly_report_id := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'resolution_time_monthly_report');

UPDATE `ohrm_advanced_report` SET `custom_url` = 'report_view_oxd({"id": "resolution_time_report", "filters": null})' WHERE `ohrm_advanced_report`.`unique_name` = 'resolution_time_report';
UPDATE `ohrm_advanced_report` SET `custom_url` = 'report_view_oxd({"id": "resolution_time_monthly_report", "filters": null})' WHERE `ohrm_advanced_report`.`unique_name` = 'resolution_time_monthly_report';

UPDATE `ohrm_advanced_report` SET `folder_id`= @folder_id WHERE `id` IN (
    @report_id, @monthly_report_id
    );

SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');

SET @requests_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='Request Desk');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('rd_resolution_time_report', 'Request Desk - Average Request Resolution Time Report', 1, 1, 1, 1),
    ('rd_resolution_time_monthly_report', 'Request Desk - Average Request Resolution Monthly Comparison Report', 1, 1, 1, 1);

SET @rd_resolution_time_report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'rd_resolution_time_report');
SET @monthly_compariosn_report_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'rd_resolution_time_monthly_report');

INSERT INTO ohrm_user_role_data_group (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self) VALUES
    (@admin_id, @rd_resolution_time_report_data_group_id, 1, 0, 0, 0, 0),
    (@admin_id, @monthly_compariosn_report_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id, can_read, can_create, can_update, can_delete) VALUES
    (1, @rd_resolution_time_report_data_group_id, 1, 0, 0, 0),
    (1, @monthly_compariosn_report_data_group_id, 1, 0, 0, 0);

INSERT INTO ohrm_screen (name, module_id, action_url) VALUES
    ( 'Average Request Resolution Time Report', @requests_module_id, 'viewAverageRequestResolutionTimeReport'),
    ( 'Average Request Resolution Monthly Comparison Report', @requests_module_id, 'viewAverageRequestResolutionMonthlyComparisonReport');

SET @rd_resolution_time_report_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewAverageRequestResolutionTimeReport');
SET @monthly_compariosn_report_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewAverageRequestResolutionMonthlyComparisonReport');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
VALUES (@admin_id, @rd_resolution_time_report_screen_id, 1, 0, 0, 0),
       (@admin_id, @monthly_compariosn_report_screen_id, 1, 0, 0, 0);

INSERT INTO ohrm_data_group_screen (data_group_id, screen_id, permission, self) VALUES
    (@rd_resolution_time_report_data_group_id,@rd_resolution_time_report_screen_id ,1 ,0),
    (@monthly_compariosn_report_data_group_id,@monthly_compariosn_report_screen_id ,1 ,0);

SET @rd_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Request Desk' and level = 2);

UPDATE `ohrm_menu_item` SET `order_hint` = '300' WHERE `ohrm_menu_item`.`menu_title` = 'Employee Requests' AND parent_id = @rd_menu_id;

UPDATE `ohrm_menu_item` SET `order_hint` = '1200' WHERE `ohrm_menu_item`.`menu_title` = 'Configure Request Types' AND parent_id = @rd_menu_id;

ALTER TABLE `ohrm_requestdesk_request_type_approvers` ADD UNIQUE (`request_type_id`);
