SET @maxId := (SELECT max(report_group_id) from ohrm_report_group);
SET @group_id := (SELECT IFNULL(@maxId, 2) + 1);

INSERT INTO `ohrm_report_group` (`report_group_id`, `name`, `core_sql`) VALUES
        (@group_id, 'vacancy_succession', 'SELECT vacancy_added_date, job_title, no_of_positions, no_of_applicants, SUM(no_of_shortlisted) AS no_of_shortlisted, SUM(no_of_hired) AS no_of_hired
FROM
(
  SELECT
  v.id,
  v.job_title_code,
  DATE(v.defined_time) AS `vacancy_added_date`,
  jt.job_title AS `job_title`,
  v.no_of_positions AS `no_of_positions`,
  (
    SELECT COUNT(*) FROM ohrm_job_candidate_vacancy AS jcv
    WHERE jcv.vacancy_id = v.id AND jcv.deleted_at IS NULL
  ) AS `no_of_applicants`,
  MAX(IF(ve.type_id = 2 OR ve.type_id = 3 OR ve.type_id = 4 OR ve.type_id = 5 OR ve.type_id = 6, 1, 0)) AS `no_of_shortlisted`,
  MAX(IF(type_id = 14, 1, 0)) AS `no_of_scheduled`,
  MAX(IF(type_id = 15, 1, 0)) AS `no_of_passed`,
  MAX(IF(type_id = 16, 1, 0)) AS `no_of_failed`,
  MAX(IF(type_id = 7, 1, 0)) AS `no_of_hired`
  FROM `ohrm_job_vacancy` v
  INNER JOIN `ohrm_job_title` AS jt
    ON v.job_title_code = jt.id
  INNER JOIN `ohrm_job_candidate_vacancy` AS jcv
    ON jcv.vacancy_id = v.id AND jcv.deleted_at IS NULL
  INNER JOIN `ohrm_candidate_vacancy_event` AS cve
    ON cve.vacancy_candidate_id = jcv.id
  INNER JOIN `ohrm_vacancy_event` AS ve
    ON ve.id = cve.event_id
  INNER JOIN `ohrm_candidate_event_state` AS ces
    ON ces.candidate_vacancy_event_id = cve.id
  WHERE ces.active = 1
  GROUP BY jcv.candidate_id
  ) AS r
WHERE whereCondition1
GROUP BY r.id
ORDER BY r.vacancy_added_date DESC
;');

SET @report_id := (SELECT IFNULL(max(report_id),0) + 1 from ohrm_report);
INSERT INTO `ohrm_report` (`report_id`, `name`, `report_group_id`, `use_filter_field`, `type`) VALUES
(@report_id, 'Vacancy Succession Report', @group_id, 1, 'ADVANCED_REPORTS');


SET @display_field_id := (SELECT IFNULL(max(display_field_id),0) + 1 from ohrm_display_field);
INSERT INTO `ohrm_display_field`
    (`display_field_id`, `report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
    (@display_field_id + 0, @group_id, 'r.vacancy_added_date', 'Vacancy Added Date', null, 'false', null, null, 'labelDate', '<xml><getter>vacancy_added_date</getter></xml>', 120, '0', 'left', false, null, '---', false, false),
    (@display_field_id + 1, @group_id, 'r.job_title', 'Job Title', null, 'false', null, null, 'label', '<xml><getter>job_title</getter></xml>', 200, '0', 'left', false, null, '---', false, false),
    (@display_field_id + 2, @group_id, 'r.no_of_positions', 'Number of Positions', null, 'false', null, null, 'label', '<xml><getter>no_of_positions</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 3, @group_id, 'r.no_of_applicants', 'Number of Applicants', null, 'false', null, null, 'label', '<xml><getter>no_of_applicants</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 4, @group_id, 'SUM(IF(r.no_of_shortlisted = 1, 1, 0))', 'Number of Shortlisted', 'no_of_shortlisted', 'false', null, null, 'label', '<xml><getter>no_of_shortlisted</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
--     (@display_field_id + 5, @group_id, 'SUM(IF(r.no_of_scheduled >= 1 AND r.no_of_passed >= 1, 1, 0))', '1st Interview Passed', 'first_interview_passed', 'false', null, null, 'label', '<xml><getter>first_interview_passed</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
--     (@display_field_id + 6, @group_id, 'SUM(IF(r.no_of_scheduled = 1 AND r.no_of_failed = 1, 1, 0))', '1st Interview Failed', 'first_interview_failed', 'false', null, null, 'label', '<xml><getter>first_interview_failed</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
--     (@display_field_id + 7, @group_id, 'SUM(IF(r.no_of_scheduled >= 2 AND r.no_of_passed >= 2, 1, 0))', '2nd Interview Passed', 'second_interview_passed', 'false', null, null, 'label', '<xml><getter>second_interview_passed</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
--     (@display_field_id + 8, @group_id, 'SUM(IF(r.no_of_scheduled >= 2 AND r.no_of_failed = 1, 1, 0))', '2nd Interview Failed', 'second_interview_failed', 'false', null, null, 'label', '<xml><getter>second_interview_failed</getter></xml>', 80, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 9, @group_id, 'SUM(IF(r.no_of_hired = 1, 1, 0))', 'Number of Hired Applicants', 'no_of_hired', 'false', null, null, 'label', '<xml><getter>no_of_hired</getter></xml>', 100, '0', 'right', false, null, '---', false, false),
--     (@display_field_id + 10, @group_id, 'r.hiring_manager_id', 'Hiring Manager ID', null, 'false', null, null, 'label', '<xml><getter>hiring_manager_id</getter></xml>', 80, '0', 'left', false, null, '---', false, true),
    (@display_field_id + 11, @group_id, 'r.job_title_code', 'Job Title Code', null, 'false', null, null, 'label', '<xml><getter>job_title_code</getter></xml>', 80, '0', 'left', false, null, '---', false, true);


SET @selected_display_id := (SELECT IFNULL(max(id),0) + 1 from ohrm_selected_display_field);
INSERT INTO `ohrm_selected_display_field` (`id`, `display_field_id`, `report_id`) VALUES
	(@selected_display_id + 0, @display_field_id, @report_id),
	(@selected_display_id + 1, @display_field_id + 1, @report_id),
	(@selected_display_id + 2, @display_field_id + 2, @report_id),
	(@selected_display_id + 3, @display_field_id + 3, @report_id),
	(@selected_display_id + 4, @display_field_id + 4, @report_id),
-- 	(@selected_display_id + 5, @display_field_id + 5, @report_id),
-- 	(@selected_display_id + 6, @display_field_id + 6, @report_id),
-- 	(@selected_display_id + 7, @display_field_id + 7, @report_id),
-- 	(@selected_display_id + 8, @display_field_id + 8, @report_id),
	(@selected_display_id + 9, @display_field_id + 9, @report_id);

SET @filter_field_id := (SELECT IFNULL(max(filter_field_id),0) + 1 from ohrm_filter_field);

INSERT INTO `ohrm_filter_field` (`filter_field_id`, `report_group_id`, `name`, `where_clause_part`, `filter_field_widget`, `condition_no`, `required`) VALUES
    (@filter_field_id + 0, @group_id, 'job_title', 'r.job_title_code', 'ohrmWidgetAllowedJobTitleList', 1, 'false'),
    (@filter_field_id + 1, @group_id, 'date_range', 'r.vacancy_added_date', 'ohrmWidgetDateRangeMaterial', 1, 'false');
--     (@filter_field_id + 2, @group_id, 'hiring_manager_id', 'r.hiring_manager_id', 'ohrmWidgetHiringManagerHiddenField', 1, 'false');

INSERT INTO `ohrm_selected_filter_field` (`report_id`, `filter_field_id`, `filter_field_order`, `value1`, `value2`, `where_condition`, `type`) VALUES
    (@report_id, @filter_field_id + 0, 1, null, null, null, 'Runtime'),
    (@report_id, @filter_field_id + 1, 2, null, null, null, 'Runtime');


SET @recruitment_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'recruitment');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('recruitment_reports', 'Recruitment (ATS) - Reports', 1, 0, 0, 0);
SET @vacancy_reports_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'recruitment_reports');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(1, @vacancy_reports_data_group_id, 1, 0, 0, 0, 0);
INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(1, @vacancy_reports_data_group_id);

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Vacancy Succession Report', @recruitment_module_id, 'displayVacancySuccessionReportCriteria', 'recruitment_displayVacancySuccessionReportCriteria');

SET @employee_vacancy_report_screen_id := (SELECT LAST_INSERT_ID());

SET @vacancy_succession_report_id := (SELECT report_id FROM ohrm_report WHERE name = 'Vacancy Succession Report');

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

SET @recruitment_module_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewRecruitmentModule');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@vacancy_reports_data_group_id, @employee_vacancy_report_screen_id, 1),
(@vacancy_reports_data_group_id, @recruitment_module_screen_id, 1);
