SET @recruitment_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'recruitment');
SET @admin_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET @hiring_manager_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HiringManager');
SET @event_manager_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'EventManager');
SET @admin_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Admin');


-- Data Group For Recruitment Reports
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('recruitment_configurable_reports', 'Recruitment (ATS) - Configurable Reports', 1, 1, 1, 1);
SET @recruitment_configurable_reports_data_group_id := (SELECT LAST_INSERT_ID());

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, @recruitment_configurable_reports_data_group_id, 1, 1, 1, 1, 0),
                                                                                                                                            (@hiring_manager_id, @recruitment_configurable_reports_data_group_id, 1, 0, 0, 0, 0),
                                                                                                                                            (@event_manager_id, @recruitment_configurable_reports_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
                                                                                                                                              (@admin_user_role_type_id, @recruitment_configurable_reports_data_group_id, 1, 1, 1, 1);


SET @recruitment_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Recruitment (ATS)');
INSERT INTO ohrm_report_definition_master_data
(module, prefix, template_name, data_group, display_label, icon, chart_configurable, default_folder_id) VALUES
    ('recruitment', 'recruitment_vacancy_specific', 'RECRUITMENT_Vacancy_Specific_Candidate_Report_template', 'recruitment_configurable_reports', 'Vacancy Specific Candidate Report', NULL, 1, @recruitment_folder_id),
    ('recruitment', 'recruitment_general_overview', 'RECRUITMENT_General_Overview_Report_template', 'recruitment_configurable_reports', 'General Recruitment Overview Report', NULL, 1, @recruitment_folder_id);

SET @recruitment_vacancy_specific_template :=
    (SELECT id FROM ohrm_report_definition_master_data WHERE template_name = 'RECRUITMENT_Vacancy_Specific_Candidate_Report_template');
SET @recruitment_overview_template :=
    (SELECT id FROM ohrm_report_definition_master_data WHERE template_name = 'RECRUITMENT_General_Overview_Report_template');

SET
@admin_accessible_report_type_id := (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'Admin');
SET
@hiring_manager_accessible_report_type_id := (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'Hiring Manager');
SET
@event_manager_accessible_report_type_id := (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'Stage Coordinators/ Interviewers/ Test Coordinators');
INSERT INTO ohrm_report_definition_accessible_role_type
(definition_id, role_type_id) VALUES
                                  (@recruitment_vacancy_specific_template, @admin_accessible_report_type_id),
                                  (@recruitment_vacancy_specific_template, @hiring_manager_accessible_report_type_id),
                                  (@recruitment_vacancy_specific_template, @event_manager_accessible_report_type_id),
                                  (@recruitment_overview_template, @admin_accessible_report_type_id),
                                  (@recruitment_overview_template, @hiring_manager_accessible_report_type_id),
                                  (@recruitment_overview_template, @event_manager_accessible_report_type_id);

SET @candidate_source_canned_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'recruitment_general_overview.Candidate_Source_Report');
SET @recruitment_overview_canned_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'recruitment_general_overview.Recruitment_Overview_Report');

INSERT INTO ohrm_report_configuration (report_id, configuration) VALUES
    (@candidate_source_canned_report,'{"selectedDisplayFieldGroups":[],"selectedDisplayFields":["1","4","5","30"],"selectedFilterCriteria":{"show_only_filtered_records":true,"vacancy_name":"","source":"","date_of_application_dynamic_date_group":{"date_of_application_dynamic_date_group":"date_of_application_date_more_custom","date_of_application_date_more_custom":"","date_of_application_date_more_previous_n":{"left":"1","right":"1"},"date_of_application_date_more_today":"1","date_of_application_date_more_this_week":{"right":"2"},"date_of_application_date_more_this_month":"3","date_of_application_date_more_this_year":"4","date_of_application_date_more_next_n":{"left":"1","right":"1"},"date_of_application_date_more_from_field":""}},"summaryConfiguration":{"summaryId":"dynamic_chart","chartType":"pie_chart","groupByFieldId":"4","aggregateFieldId":"-1","aggregateType":"count","showSummaryTable":true,"hideDetailedData":false}}'),
    (@recruitment_overview_canned_report,'{"selectedDisplayFieldGroups":["1","2","30"],"selectedDisplayFields":["1","4","5","6","15","16","17","18","19","20","30","32","33"],"selectedFilterCriteria":{"show_only_filtered_records":true,"vacancy_name":"","job_title":"","location":"","source":"","current_stage":"","date_of_application_dynamic_date_group":{"date_of_application_dynamic_date_group":"date_of_application_date_more_custom","date_of_application_date_more_custom":"","date_of_application_date_more_previous_n":{"left":"1","right":"1"},"date_of_application_date_more_today":"1","date_of_application_date_more_this_week":{"right":"2"},"date_of_application_date_more_this_month":"3","date_of_application_date_more_this_year":"4","date_of_application_date_more_next_n":{"left":"1","right":"1"},"date_of_application_date_more_from_field":""}}}');


INSERT INTO ohrm_report_share (report_id, role_type_id) VALUES
                                                            (@candidate_source_canned_report, @admin_accessible_report_type_id),
                                                            (@candidate_source_canned_report, @hiring_manager_accessible_report_type_id),
                                                            (@candidate_source_canned_report, @event_manager_accessible_report_type_id),
                                                            (@recruitment_overview_canned_report, @admin_accessible_report_type_id),
                                                            (@recruitment_overview_canned_report, @hiring_manager_accessible_report_type_id),
                                                            (@recruitment_overview_canned_report, @event_manager_accessible_report_type_id);