SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND parent_id IS NULL AND level = 1);
SET @reporting_and_analytics_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Reports and Analytics' AND `parent_id` = @people_management_pillar_menu_id);
SET @leave_report_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Leave Reports' AND `parent_id` = @reporting_and_analytics_module_menu_id);

SET @leave_module_id = (SELECT `id` FROM `ohrm_module` WHERE `name` = 'leave');
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
    ('Leave Percentage', @leave_module_id, 'viewLeavePercentageReport'),
    ('Bradford Factor Report', @leave_module_id, 'viewBradfordFactorReport');
SET @leave_percentage_report_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'viewLeavePercentageReport');
SET @bradford_factor_report_screen_id := (SELECT `id` FROM ohrm_screen WHERE `action_url` = 'viewBradfordFactorReport');
SET @view_leave_module_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View Leave Module');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
    (1, @leave_percentage_report_screen_id, 1, 1, 1, 1),
    (1, @bradford_factor_report_screen_id, 1, 1, 1, 1),
    (3, @bradford_factor_report_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('leave_percentage_report', 'Leave - Leave Percentage Report', 1, NULL, NULL, NULL),
    ('leave_bradford_factor_report', 'Leave - Leave Bradford Factor Report', 1, NULL, NULL, NULL);
SET @leave_percentage_report_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_percentage_report');
SET @leave_bradford_factor_report_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_bradford_factor_report');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (1, @leave_percentage_report_data_group_id, 1, NULL, NULL, NULL, 0),
    (1, @leave_bradford_factor_report_data_group_id, 1, NULL, NULL, NULL, 0),
    (3, @leave_bradford_factor_report_data_group_id, 1, NULL, NULL, NULL, 0);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
     (1, @leave_percentage_report_data_group_id),
     (1, @leave_bradford_factor_report_data_group_id),
     (3, @leave_bradford_factor_report_data_group_id);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
     (@leave_bradford_factor_report_data_group_id, @bradford_factor_report_screen_id, 1),
     (@leave_bradford_factor_report_data_group_id, @view_leave_module_screen_id, 1);

SET @current_years_leave_taken_by_department_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Current_Years_Leave_Taken_by_Department');
SET @leave_taken_on_each_calendar_month_over_the_years_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Leave_Taken_on_Each_Calendar_Month_Over_the_Years');
SET @all_leave_requests_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.All_Leave_Requests');
SET @leave_scheduled_in_each_month_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Leave_Scheduled_in_Each_Month');
SET @employees_on_leave_today_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employees_on_Leave_Today');
SET @leave_taken_on_each_day_of_the_week_over_time_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Leave_Taken_on_Each_Day_of_the_Week_Over_Time');
SET @employees_on_leave_this_month_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employees_on_Leave_This_Month');

INSERT INTO ohrm_report_configuration (report_id, configuration) VALUES
     (@current_years_leave_taken_by_department_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","82","301","302","300"],"selectedFilterCriteria":{"include":"1","show_only_filtered_records":true,"leave_date_dynamic_date_group_func_year_group":{"leave_date_dynamic_date_group_func_year_group":"leave_date_year_current","leave_date_year_custom":"","leave_date_year_current":"1","leave_date_year_from_field":""},"leave_status":{"5":"Taken"}},"summaryConfiguration":{"summaryId":"dynamic_chart","chartType":"pie_chart","groupByFieldId":"82","aggregateFieldId":"302","aggregateType":"sum","showSummaryTable":true,"hideDetailedData":false,"addToDashboard":true}}'),
     (@leave_taken_on_each_calendar_month_over_the_years_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","302","300_func_month","305","82","83"],"selectedFilterCriteria":{"include":"2","show_only_filtered_records":true,"leave_status":{"5":"Taken"},"sub_unit":"","location":"","employee_name":""},"summaryConfiguration":{"summaryId":"dynamic_chart","chartType":"bar_chart","groupByFieldId":"300_func_month","aggregateFieldId":"302","aggregateType":"sum","showSummaryTable":true,"hideDetailedData":true,"addToDashboard":true}}'),
     (@all_leave_requests_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","300","301","302","304","303","306","82","83","77"],"selectedFilterCriteria":{"include":"2","show_only_filtered_records":true,"leave_status":{"5":"Taken"},"employment_status":"","job_title":"","job_category":"","location":"","cost_centre":"","sub_unit":"","country":"","termination_reason":"","workshift":"","eeo_job_category":""}}'),
     (@leave_scheduled_in_each_month_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","300_func_month","300_func_year","302"],"selectedFilterCriteria":{"include":"1","show_only_filtered_records":true,"leave_status":{"4":"Scheduled"}},"summaryConfiguration":{"summaryId":"dynamic_chart","chartType":"bar_chart","groupByFieldId":["300_func_year","300_func_month"],"aggregateFieldId":"302","aggregateType":"sum","showSummaryTable":true,"hideDetailedData":false,"addToDashboard":true}}'),
     (@employees_on_leave_today_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","301","302","300","305","306"],"selectedFilterCriteria":{"include":"1","show_only_filtered_records":true,"sub_unit":"","location":"","job_title":"","country":"","leave_date_dynamic_date_group":{"leave_date_dynamic_date_group":"leave_date_date_more_today","leave_date_date_more_custom":"","leave_date_date_more_today":"1","leave_date_date_more_from_field":""}}}'),
     (@leave_taken_on_each_day_of_the_week_over_time_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","302","305","82","83","300_func_day"],"selectedFilterCriteria":{"include":"2","show_only_filtered_records":true,"leave_status":{"5":"Taken"},"sub_unit":"","location":"","employee_name":""},"summaryConfiguration":{"summaryId":"dynamic_chart","chartType":"bar_chart","groupByFieldId":"300_func_day","aggregateFieldId":"302","aggregateType":"sum","showSummaryTable":true,"hideDetailedData":false,"addToDashboard":true}}'),
     (@employees_on_leave_this_month_report, '{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["9_1","301","302","300","305","306"],"selectedFilterCriteria":{"include":"1","sub_unit":"","location":"","job_title":"","country":"","show_only_filtered_records":true,"leave_date_dynamic_date_group_func_month_group":{"leave_date_dynamic_date_group_func_month_group":"leave_date_month_current","leave_date_month_custom":"","leave_date_month_current":"1","leave_date_month_from_field":""}}}');

INSERT INTO ohrm_report_share (report_id, role_type_id) VALUES
    (@current_years_leave_taken_by_department_report, 1),
    (@leave_taken_on_each_calendar_month_over_the_years_report, 1),
    (@all_leave_requests_report, 1),
    (@leave_scheduled_in_each_month_report, 1),
    (@employees_on_leave_today_report, 1),
    (@leave_taken_on_each_day_of_the_week_over_time_report, 1),
    (@employees_on_leave_this_month_report, 1);

SET @dashboard_widget_config = CONCAT('{"isEnabled":true,"availableTo":{"admin":true},"defaultSelectedTo":{"admin":true}}');

SET @widgetInsertQuery = CONCAT('INSERT INTO `ohrm_dashboard_widget`(`name`,`display_name`, `widget_class`, `config`) VALUES
("report_chart_pim.Current_Years_Leave_Taken_by_Department", "Current Year''s Leave Taken by Department", "ReportChartDashboardWidget", ''',@dashboard_widget_config,'''),
("report_chart_pim.Leave_Taken_on_Each_Calendar_Month_Over_the_Years", "Leave Taken on Each Calendar Month Over the Years", "ReportChartDashboardWidget", ''',@dashboard_widget_config,'''),
("report_chart_pim.Leave_Scheduled_in_Each_Month", "Leave Scheduled in Each Month", "ReportChartDashboardWidget", ''',@dashboard_widget_config,'''),
("report_chart_pim.Leave_Taken_on_Each_Day_of_the_Week_Over_Time", "Leave Taken on Each Day of the Week Over Time", "ReportChartDashboardWidget", ''',@dashboard_widget_config,''')');

PREPARE statement FROM @widgetInsertQuery;
EXECUTE statement;

SET @current_years_leave_taken_by_department_widget_id := (SELECT id FROM ohrm_dashboard_widget WHERE `name` = 'report_chart_pim.Current_Years_Leave_Taken_by_Department');
SET @leave_taken_on_each_calendar_month_over_the_years_report_widget_id := (SELECT id FROM ohrm_dashboard_widget WHERE `name` = 'report_chart_pim.Leave_Taken_on_Each_Calendar_Month_Over_the_Years');
SET @leave_scheduled_in_each_month_widget_id := (SELECT id FROM ohrm_dashboard_widget WHERE `name` = 'report_chart_pim.Leave_Scheduled_in_Each_Month');
SET @leave_taken_on_each_day_of_the_week_over_time_widget_id := (SELECT id FROM ohrm_dashboard_widget WHERE `name` = 'report_chart_pim.leave_taken_on_each_day_of_the_week_over_time');

SET @max_view_order := (SELECT max(view_order) FROM ohrm_dashboard_layout_widget WHERE layout_id = 1);
INSERT INTO `ohrm_dashboard_layout_widget`(`layout_id`,`widget_id`,`view_order`, `size`, `config`) VALUES
   (1,@current_years_leave_taken_by_department_widget_id,@max_view_order+1,'size_d', CONCAT('{"isEnabled":null, "reportId":', @current_years_leave_taken_by_department_report,'}')),
   (1,@leave_taken_on_each_calendar_month_over_the_years_report_widget_id,@max_view_order+2,'size_g', CONCAT('{"isEnabled":null, "reportId":', @leave_taken_on_each_calendar_month_over_the_years_report,'}')),
   (1,@leave_scheduled_in_each_month_widget_id,@max_view_order+3,'size_g', CONCAT('{"isEnabled":null, "reportId":', @leave_scheduled_in_each_month_report,'}')),
   (1,@leave_taken_on_each_day_of_the_week_over_time_widget_id,@max_view_order+4,'size_g', CONCAT('{"isEnabled":null, "reportId":', @leave_taken_on_each_day_of_the_week_over_time_report,'}'));
