# Menu Item
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'report');
SET @admin_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET @system_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'SYSTEM');
SET @ess_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'ESS');
SET @supervisor_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Supervisor');
SET @ess_role_type_id := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'ESS');
SET @admin_role_type_id := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
SET @system_user_id := (SELECT `id` FROM `ohrm_user` WHERE `user_role_id` = @system_user_role_id);
SET @reporting_and_analytics_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Reports and Analytics' AND `level` = 2);

UPDATE `ohrm_advanced_report` SET `created_by`= @system_user_id WHERE 1;

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
    ('OXD Reports', @module_id , 'oxdReport');

SET @view_oxd_report_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
    (@admin_role_id, @view_oxd_report_screen_id, 1, 1, 1, 1),
    (@ess_role_id, @view_oxd_report_screen_id, 1, 1, 1, 1),
    (@supervisor_role_id, @view_oxd_report_screen_id, 1, 1, 1, 1);
INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`) VALUES
                                                                                        ('Reports', @view_oxd_report_screen_id,@reporting_and_analytics_menu_id,3,100,'reports_and_analytics.catalogue',1);

INSERT INTO `ohrm_default_permitted_screen` (`user_role_type_id`, `screen_id`) VALUES (@ess_role_type_id, @view_oxd_report_screen_id);

# Leave
SET @leave_entitlement_usage_by_employee_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Leave_Entitlements_and_Usage_Report_By_Employee');
SET @leave_entitlement_usage_by_leave_type_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Leave_Entitlements_and_Usage_Report_By_LeaveType');
SET @leave_percentage_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Leave_Percentage_Report');
SET @bradford_factor_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Bradford_Factor_Report');
SET @short_term_leave_entitlement_usage_by_employee_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Short_Term_Leave_Entitlements_and_Usage_Report_By_Employee');
SET @short_term_leave_entitlement_usage_by_leave_type_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Short_Term_Leave_Entitlements_and_Usage_Report_By_LeaveType');

SET @leave_menu := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Leave' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Leave', @system_user_id, 4);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Other Reports', @system_user_id, 11);
SET @leave_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Leave');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`) VALUES (@leave_folder_id, @leave_menu );
UPDATE `ohrm_advanced_report` SET `folder_id`= @leave_folder_id WHERE `id` IN (
    @leave_entitlement_usage_by_employee_report, @leave_entitlement_usage_by_leave_type_report, @leave_percentage_report, @bradford_factor_report, @short_term_leave_entitlement_usage_by_employee_report, @short_term_leave_entitlement_usage_by_leave_type_report
);

UPDATE `ohrm_advanced_report` SET `custom_url`= 'leave.balance_report({"id": :reportId})' WHERE `id` = @leave_entitlement_usage_by_employee_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'leave.balance_report({"id": :reportId})' WHERE `id` = @leave_entitlement_usage_by_leave_type_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'leave.percentage_report({"id": ":reportId"})' WHERE `id` = @leave_percentage_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Bradford_Factor_Report", "filters": null})' WHERE `id` = @bradford_factor_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'leave.balance_report({"id": :reportId, "isShortTerm" : 1})' WHERE `id` IN (@short_term_leave_entitlement_usage_by_employee_report, @short_term_leave_entitlement_usage_by_leave_type_report);

# Attendance
SET @attendance_pay_hours_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Attendance_Pay_Hours_Report');
SET @attendance_hours_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Attendance_Hours_Report');
SET @monthly_attendance_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'monthly_attendance_report');
SET @attendance_my_records_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Attendance_My_Records_Report');
SET @attendance_export_to_csv_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'export_attendance_to_csv');

SET @attendance_menu := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Attendance' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Attendance', @system_user_id, 10);
SET @attendance_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Attendance');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@attendance_folder_id, @attendance_menu );
UPDATE `ohrm_advanced_report` SET `folder_id`= @attendance_folder_id WHERE `id` IN (
                                                                                  @attendance_hours_report,
                                                                                  @monthly_attendance_report,
                                                                                  @attendance_my_records_report,
                                                                                  @attendance_export_to_csv_report);

UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view_oxd({"id": "Attendance_Hours_Report", "filters": null})' WHERE `id` = @attendance_hours_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view_oxd({"id": "monthly_attendance_report", "filters": null})' WHERE `id` = @monthly_attendance_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= '/noncore/time/displayMyAttendanceDetailsReport' WHERE `id` = @attendance_my_records_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view_oxd({"id": "export_attendance_to_csv", "filters": null})' WHERE `id` = @attendance_export_to_csv_report;


# PIM
SET @canned_head_count_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim_canned.Head_Count_Report');
SET @canned_turnover_hiring_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim_canned.Employee_Turnover_Hiring_Report');
SET @canned_turnover_termination_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim_canned.Employee_Turnover_Termination_Report');
SET @canned_licence_expiry_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim_canned.License_Expiry_Report');
SET @canned_head_count_location_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim_canned.Head_Count_Report_1');

SET @employee_management_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Employee Management' and level = 2);
SET @employee_management_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Employee Management');
UPDATE `ohrm_report_folder` SET `added_user`= @system_user_id WHERE name = 'Employee Management';
UPDATE `ohrm_advanced_report` SET `folder_id`= @employee_management_folder_id WHERE `id` IN (
                                                                          @canned_head_count_report,
                                                                         @canned_turnover_hiring_report,
                                                                         @canned_turnover_termination_report,
                                                                         @canned_licence_expiry_report,
                                                                         @canned_head_count_location_report
    );
UPDATE `ohrm_advanced_report` SET `folder_id`= @employee_management_folder_id WHERE `unique_name` LIKE 'pim_canned.%' OR `unique_name` LIKE 'pim.%';

# Expense
SET @expense_request_and_claim_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Expense_Requests_and_Claims_Report');
SET @expense_requests_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Expense_Requests_Report');
SET @expense_claims_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Expense_Claims_Report');
SET @expense_summary_subunit_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Expense_Summary_Report_-_Based_on_Sub_Units');
SET @expense_summary_expense_type_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Expense_Summary_Report_-_Based_on_Expense_Types');
SET @expense_unclaimed_travel_req_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Unclaimed_Travel_Requests');
SET @expense_unclaimed_advance_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'expense_canned.Unclaimed_Cash_in_Advances');

SET @expense_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Travel and Expense' and level = 2);
SET @expense_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Travel and Expense');
UPDATE `ohrm_report_folder` SET `added_user`= @system_user_id WHERE name = 'Travel and Expense';
UPDATE `ohrm_advanced_report` SET `folder_id`= @expense_folder_id WHERE `id` IN (
                                                                          @expense_request_and_claim_report,
                                                                          @expense_requests_report,
                                                                          @expense_claims_report,
                                                                          @expense_summary_subunit_report,
                                                                          @expense_summary_expense_type_report,
                                                                          @expense_unclaimed_travel_req_report,
                                                                          @expense_unclaimed_advance_report
    );
UPDATE `ohrm_advanced_report` SET `folder_id`= @expense_folder_id WHERE `unique_name` LIKE 'expense_canned.%';

INSERT INTO ohrm_report_share (`report_id`, `role_type_id`) SELECT id , 1 FROM ohrm_advanced_report WHERE unique_name LIKE '%_canned.%';
SET @admin_accessible_role_type := (SELECT id FROM ohrm_report_accessible_role_type WHERE name = 'Admin');
SET @supervisor_accessible_role_type := (SELECT id FROM ohrm_report_accessible_role_type WHERE name = 'Supervisor');
SET @hod_accessible_role_type := (SELECT id FROM ohrm_report_accessible_role_type WHERE name = 'Head of Department');
SET @ess_accessible_role_type := (SELECT id FROM ohrm_report_accessible_role_type WHERE name = 'ESS');

SET @export_attendance_to_csv_report_id := (SELECT id FROM ohrm_advanced_report WHERE unique_name LIKE 'export_attendance_to_csv');
SET @Export_Timesheet_Data_As_CSV_report_id := (SELECT id FROM ohrm_advanced_report WHERE unique_name LIKE 'Export_Timesheet_Data_As_CSV');
INSERT INTO ohrm_report_share (`report_id`, `role_type_id`) VALUES
(@export_attendance_to_csv_report_id,@admin_accessible_role_type),
(@export_attendance_to_csv_report_id,@supervisor_accessible_role_type),
(@export_attendance_to_csv_report_id,@hod_accessible_role_type),
(@Export_Timesheet_Data_As_CSV_report_id,@admin_accessible_role_type),
(@Export_Timesheet_Data_As_CSV_report_id,@supervisor_accessible_role_type),
(@Export_Timesheet_Data_As_CSV_report_id,@hod_accessible_role_type);

# Time
SET @time_paycode_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Time_Paycode_Report');
SET @project_time_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Project_Time_Report');
SET @employee_project_time_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Employee_Project_Time_Report');
SET @export_timesheet_as_csv_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Export_Timesheet_Data_As_CSV');

SET @time_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Time Tracking' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Time Tracking', @system_user_id, 5);
SET @time_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Time Tracking');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@time_folder_id, @time_menu_id );
UPDATE `ohrm_advanced_report` SET `folder_id`= @time_folder_id WHERE `id` IN (
                                                                          @time_paycode_report,
                                                                          @project_time_report,
                                                                          @employee_project_time_report,
                                                                          @export_timesheet_as_csv_report
    );

UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Project_Time_Report", "filters": null})' WHERE `id` = @project_time_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Employee_Project_Time_Report", "filters": null})' WHERE `id` = @employee_project_time_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": "Time_Paycode_Report", "filters": null})' WHERE `id` = @time_paycode_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view_oxd({"id": ":uniqueName", "filters": null})' WHERE `id` = @export_timesheet_as_csv_report;

# Performance
SET @review_and_appraisals_progress_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Reviews and Appraisals Progress');
SET @performance_comparison_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Performance Comparison Report');
SET @employee_performance_profile := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Employee Performance Profile');
SET @employee_performance_progress_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Performance Progress Report');
SET @employee_performance_result_export := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Performance_Result_Export');

# Performance Trackers
SET @employee_performance_trackers_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employee_Performance_Trackers');

SET @employee_goals_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employee_Goals');
SET @employee_okrs_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employee_OKRs');
SET @employee_goals_and_okrs_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employee_Goals_and_OKRs');
SET @reviews_done_by_each_employee_during_specified_period_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Reviews_done_by_each_employee_during_specified_period');
SET @average_competency_score_given_by_each_evaluator_across_reviews := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Average_competency_score_given_by_each_evaluator_across_reviews');

SET @performance_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Performance' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Performance', @system_user_id, 6);
SET @performance_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Performance');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@performance_folder_id, @performance_menu_id );
UPDATE `ohrm_advanced_report` SET `folder_id`= @performance_folder_id WHERE `id` IN (
                                                                          @review_and_appraisals_progress_report,
                                                                          @performance_comparison_report,
                                                                          @employee_performance_profile,
                                                                          @employee_performance_progress_report,
                                                                          @employee_performance_result_export,
                                                                          @employee_performance_trackers_report,
                                                                          @employee_goals_report,
                                                                          @employee_okrs_report,
                                                                          @employee_goals_and_okrs_report,
                                                                          @reviews_done_by_each_employee_during_specified_period_report,
                                                                          @average_competency_score_given_by_each_evaluator_across_reviews
    );

UPDATE `ohrm_advanced_report` SET `custom_url`= '/noncore/performance/viewPerformanceReport/reportId/:reportId' WHERE `id` = @review_and_appraisals_progress_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= '/noncore/performance/viewFullComparisonReport/reportId/:reportId' WHERE `id` = @performance_comparison_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= '/noncore/performance/viewEmployeePerformanceProfile/reportId/:reportId' WHERE `id` = @employee_performance_profile;
UPDATE `ohrm_advanced_report` SET `custom_url`= '/noncore/performance/viewPerformanceProgressReport/reportId/:reportId' WHERE `id` = @employee_performance_progress_report;
UPDATE `ohrm_advanced_report` SET `custom_url`= 'performance.export_results({"reportId" ::reportId})' WHERE `id` = @employee_performance_result_export;

# Canned Reports and Report Catalog
SET @employee_acknowledgement_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Employee_Acknowledgment_Report');

SET @hr_administration_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'HR Administration' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('HR Administration', @system_user_id, 1);
SET @hr_administration_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'HR Administration');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@hr_administration_folder_id, @hr_administration_menu_id );
UPDATE `ohrm_advanced_report` SET `folder_id`= @hr_administration_folder_id WHERE `id` IN (
                                                                               @employee_acknowledgement_report,
                                                                               @job_salary_history_report
    );
UPDATE `ohrm_advanced_report` SET `custom_url`= 'report_view({"id": ":uniqueName", "filters": null})' WHERE `id` = @employee_acknowledgement_report;

# Recruitment
SET @vacancy_succession_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'vacancy_succession_report');
SET @vacancy_specific_recruitment_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'RECRUITMENT_Vacancy_Specific_Candidate_Report_template');
SET @recruitment_overview_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'RECRUITMENT_General_Overview_Report_template');
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');

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

SET @recruitment_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Recruitment (ATS)' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Recruitment (ATS)', @system_user_id, 3);
SET @recruitment_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Recruitment (ATS)');

INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@recruitment_folder_id, @recruitment_menu_id );

UPDATE `ohrm_advanced_report` SET `folder_id`= @recruitment_folder_id WHERE `id` IN (
                                                                                        @vacancy_succession_report,
                                                                                        @vacancy_specific_recruitment_report,
                                                                                        @recruitment_overview_report,
                                                                                        @candidate_source_canned_report,
                                                                                        @recruitment_overview_canned_report
                                                                                    );

UPDATE `ohrm_advanced_report` SET `created_by`= @system_user_id WHERE `id` IN (
                                                                                     @candidate_source_canned_report,
                                                                                     @recruitment_overview_canned_report
                                                                                );

UPDATE `ohrm_report_definition_master_data` SET `default_folder_id`= @recruitment_folder_id WHERE `id` IN (
                                                                                                            @vacancy_specific_report_template,
                                                                                                            @recruitment_overview_report_template
                                                                                                          );
UPDATE `ohrm_advanced_report` SET `custom_url`= '/noncore/recruitment/displayVacancySuccessionReportCriteria/reportId/:ohrmReportId' WHERE `id` = @vacancy_succession_report;

UPDATE `ohrm_advanced_report` SET `folder_id`= @leave_folder_id
WHERE unique_name IN ('pim.Current_Years_Leave_Taken_by_Department',
                      'pim.Leave_Taken_on_Each_Calendar_Month_Over_the_Years',
                      'pim.All_Leave_Requests',
                      'pim.Leave_Scheduled_in_Each_Month',
                      'pim.Employees_on_Leave_Today',
                      'pim.Leave_Taken_on_Each_Day_of_the_Week_Over_Time',
                      'pim.Employees_on_Leave_This_Month');

# Report Access History Table
CREATE TABLE ohrm_report_access_log (
    `id` INT(4) AUTO_INCREMENT,
    `user_id` int(10) default null,
    `report_id` INT NOT NULL ,
    `access_time` DATETIME NOT NULL ,
    `created_at` DATETIME ,
    `updated_at` DATETIME ,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_reports_access_log_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_reports_access_log_report_id` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

# Report Access History Cleanup
INSERT INTO `ohrm_cleanup_entities_config` (`entity_name`, `configuration`, `delete_days_after`, `date_configuration_getter`, `delete_strategy`)
VALUES ('ReportAccessLog', '{"rowDelete":true}', '370', 'access_time', 'BaseDataDeletionStrategyDao');

# Scheduled Reports
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
    ('Schedule Reports', @module_id , 'scheduleReports');

SET @schedule_reports_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
                                                                                                              (@admin_role_id, @schedule_reports_screen_id, 1, 1, 1, 1);
INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`) VALUES
                                                                                               ('Scheduled Reports', @schedule_reports_screen_id,@reporting_and_analytics_menu_id,3,200,'reports_and_analytics.schedule_reports',1);
CREATE TABLE ohrm_report_schedule_frequency (
                                      `id` INT(4) AUTO_INCREMENT,
                                      name VARCHAR(100) NOT NULL UNIQUE,
                                      `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                      `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                      PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;


CREATE TABLE ohrm_report_schedule (
                                        `id` INT(10) AUTO_INCREMENT,
                                        `name` VARCHAR(100) NOT NULL UNIQUE,
                                        `start_time` DATETIME NOT NULL ,
                                        `end_time` DATETIME NOT NULL ,
                                        `timezone` VARCHAR(255) NOT NULL ,
                                        `status` tinyint(1) DEFAULT '1',
                                        `frequency` INT(4) DEFAULT NULL ,
                                        `export_file_type` tinyint(1) DEFAULT NULL ,
                                        `schedule_type` tinyint(1) DEFAULT NULL ,
                                        `ftp_path` VARCHAR(1000) DEFAULT NULL ,
                                        `connection_id` int unsigned DEFAULT NULL ,
                                        `created_by` int(10) default null,
                                        `next_run_time` TIMESTAMP NULL DEFAULT NULL,
                                        `gdpr_consent_accepted` tinyint(1) DEFAULT '0',
                                        `configurations` TEXT,
                                        `repeat_last_day_month` tinyint(1) DEFAULT '0',
                                        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                        PRIMARY KEY (`id`),
                                        CONSTRAINT `fk_ohrm_report_schedule_created_by` FOREIGN KEY (`created_by`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                        CONSTRAINT `fk_ohrm_report_schedule_frequency` FOREIGN KEY (`frequency`) REFERENCES `ohrm_report_schedule_frequency` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                        CONSTRAINT `fk_ohrm_report_schedule_timezone` FOREIGN KEY (`timezone`) REFERENCES `ohrm_standard_time_zone` (`timezone_key`) ON DELETE CASCADE ON UPDATE CASCADE,
                                        CONSTRAINT `fk_ohrm_report_schedule_file_transfer_connection` FOREIGN KEY (`connection_id`) REFERENCES `ohrm_file_transfer_connection` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_schedule_report_employee_recipient (
    `schedule_id` INT(10) NOT NULL ,
    `employee_id` INT(7) NOT NULL ,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`schedule_id`, `employee_id`),
    CONSTRAINT `fk_schedule_report_employee_recipient_schedule_id` FOREIGN KEY (`schedule_id`) REFERENCES `ohrm_report_schedule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_schedule_report_employee_recipient_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_schedule_report_email_recipient (
     `schedule_id` INT(10) NOT NULL,
     `email` VARCHAR(255) NOT NULL ,
     `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
     `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
     PRIMARY KEY (`schedule_id`, `email`),
     CONSTRAINT `fk_schedule_report_email_recipient_schedule_id` FOREIGN KEY (`schedule_id`) REFERENCES `ohrm_report_schedule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ohrm_schedule_report_email_sending_method (
    `schedule_id` INT(10) NOT NULL,
    `method` tinyint(1) NOT NULL DEFAULT '1' ,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`schedule_id`),
    CONSTRAINT `fk_schedule_report_email_sending_method_schedule_id` FOREIGN KEY (`schedule_id`) REFERENCES `ohrm_report_schedule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

CREATE TABLE ohrm_report_schedule_report (
                                        `id` INT(4) AUTO_INCREMENT,
                                        `schedule_id` int(10) default null,
                                        `report_id` INT NOT NULL ,
                                        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                        PRIMARY KEY (`id`),
                                        CONSTRAINT `fk_ohrm_report_schedule_report_schedule_id` FOREIGN KEY (`schedule_id`) REFERENCES `ohrm_report_schedule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                        CONSTRAINT `fk_ohrm_report_schedule_report_report_id` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;


INSERT INTO `ohrm_report_schedule_frequency` (`name`) VALUES
    ('Once'),
    ('Daily'),
    ('Weekly'),
    ('Monthly');

CREATE TABLE ohrm_report_schedule_queue (
                                             `id` INT(4) AUTO_INCREMENT,
                                             `schedule_id` int(10) default null,
                                             `report_id` INT NOT NULL ,
                                             `recipient_id` int(7) default null,
                                             `generate_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                             `status`  ENUM('Ready', 'Inprogress', 'Completed', 'Failed First Attempt', 'Failed Second Attempt', 'Failed'),
                                             `record_count` int(4) default null,
                                             `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                             `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                                             PRIMARY KEY (`id`),
                                             CONSTRAINT `fk_ohrm_report_schedule_queue_schedule_id` FOREIGN KEY (`schedule_id`) REFERENCES `ohrm_report_schedule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                             CONSTRAINT `fk_ohrm_report_schedule_queue_report_id` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                             CONSTRAINT `fk_ohrm_report_schedule_queue_recipient_id` FOREIGN KEY (`recipient_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

# Scheduled Reports
SET @global_admin_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('schedule_report', 'Reports and Analytics - Scheduled Reports', 1, 1, 1, 1);
SET @schedule_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 (@global_admin_id, @schedule_data_group_id, '1', '1', '1', '1', '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',@schedule_data_group_id , '1', '1', '1', '1');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
    (@schedule_data_group_id, @schedule_reports_screen_id, 1);

# Schedule Reports Task
INSERT INTO `ohrm_symfony_task` (`name`, `short_description`, `long_description`, `command`, `schedule`, `observer_class`, `prevent_overlap`,`user_interface`,`initial_time_for_task_execution_verification`,`status`, `task_failure_notification`) VALUES
    ('Execute Scheduled Reports','Generate Schedule Reports','Check for scheduled reports and generate them if needed with sending it to recipients','php symfony orangehrm:execute-schedule-reports','* * * * *','',1,'executableTask',CURRENT_TIMESTAMP,1, 1);
SET @schedule_execute_task_id := (SELECT LAST_INSERT_ID());

UPDATE `ohrm_advanced_report` SET `schedulable`= '0' WHERE `unique_name` IN ('vacancy_succession_report', 'export_attendance_to_csv', 'Export_Timesheet_Data_As_CSV', 'Leave_Entitlements_and_Usage_Report_By_Employee', 'Leave_Entitlements_and_Usage_Report_By_LeaveType', 'Time_Paycode_Report', 'Project_Time_Report', 'Employee_Project_Time_Report', 'Employee Performance Profile', 'Performance Progress Report', 'Performance_Result_Export','Performance Comparison Report','Short_Term_Leave_Entitlements_and_Usage_Report_By_Employee','Short_Term_Leave_Entitlements_and_Usage_Report_By_LeaveType');

INSERT INTO `ohrm_symfony_task_inputs` (`task_id`, `name`, `label`, `description`, `active`, `required`, `position`, `widget_creator`, `type`, `data_type`) VALUES
    (@schedule_execute_task_id, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date'),
    (@schedule_execute_task_id, '--systemTime', 'Time To Run', 'User defined system time', 1, 0, 1, 'TimeWidgetCreator', 'parameter', 'time'),
    (@schedule_execute_task_id, '--systemTimezone', 'Time Zone', 'User defined system timezone', 1, 0, 3, 'TimezoneWidgetCreator', 'parameter', '');

# Generate Async Event on Scheduled Report Generation
INSERT INTO `ohrm_async_event` (`name`, `display_name`, `group_name`, `enabled`, `position`)
VALUES ('schedule_report_generate', 'Scheduled Report Generated', 'admin.scheduled_report', 1, 10);

# Max Records Per Report
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('scheduled_reports.max_records', '50000');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('scheduled_reports.max_file_size_mb', '10');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('scheduled_reports.max_reports_per_schedule', '1');

CREATE TABLE IF NOT EXISTS `ohrm_report_schedule_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action_owner_id` varchar(6),
    `action_owner_name` VARCHAR(300),
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX ohrm_report_schedule_trail_idx_app_id (app_id),
    INDEX ohrm_report_schedule_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_module` (`name`) VALUES ('Reports and Analytics');

SET @audit_reports_and_analytics_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Reports and Analytics');

-- Report Schedule Audit sections, actions and listeners
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('ReportSchedule', 'Report Schedules', 'Report Schedule', @audit_reports_and_analytics_module_id);

SET @report_schedule_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='ReportSchedule' AND module_id = @audit_reports_and_analytics_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
    (@report_schedule_section_id, 'ADD SCHEDULE', 'Add Schedule'),
    (@report_schedule_section_id, 'UPDATE SCHEDULE', 'Update Schedule'),
    (@report_schedule_section_id, 'DELETE SCHEDULE', 'Delete Schedule');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
    ('employees_terminated', 'ReportScheduleUpdateUponEmployeeTerminationListener', 1, @max_event_listener_position + 1),
    ('scheduled_reports.add', 'ScheduleReportsListener', 1, @max_event_listener_position + 2),
    ('scheduled_reports.update', 'ScheduleReportsListener', 1, @max_event_listener_position + 3),
    ('scheduled_reports.delete', 'ScheduleReportsListener', 1, @max_event_listener_position + 4),
    ('scheduled_reports.add', 'AddReportScheduleAuditListener', 1, @max_event_listener_position + 5),
    ('scheduled_reports.update', 'UpdateReportScheduleAuditListener', 1, @max_event_listener_position + 6),
    ('scheduled_reports.delete', 'DeleteReportScheduleAuditListener', 1, @max_event_listener_position + 7);

-- Connection Audit sections, actions and listeners
CREATE TABLE IF NOT EXISTS `ohrm_file_transfer_connection_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action_owner_id` varchar(6),
    `action_owner_name` VARCHAR(300),
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX ohrm_file_transfer_connection_trail_idx_app_id (app_id),
    INDEX ohrm_file_transfer_connection_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('FileTransferConnection', 'Connections', 'Connection', @audit_reports_and_analytics_module_id);

SET @connections_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='FileTransferConnection' AND module_id = @audit_reports_and_analytics_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
    (@connections_section_id, 'ADD CONNECTION', 'Add Connection'),
    (@connections_section_id, 'UPDATE CONNECTION', 'Update Connection'),
    (@connections_section_id, 'DELETE CONNECTION', 'Delete Connection');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
    ('admin.add_connection', 'AddConnectionAuditListener', 1, @max_event_listener_position + 1),
    ('admin.update_connection', 'UpdateConnectionAuditListener', 1, @max_event_listener_position + 2),
    ('admin.delete_connection', 'DeleteConnectionAuditListener', 1, @max_event_listener_position + 3);

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_advanced_report_trail` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `action_owner_id` varchar(6),
   `action_owner_name` VARCHAR(300),
   `version_id` INT NOT NULL DEFAULT 0,
   `action` varchar(255) NOT NULL,
   `affected_entity_id` VARCHAR(20),
   `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
   `action_description` MEDIUMTEXT,
   `app_id` INT DEFAULT NULL,
   `app_name` VARCHAR(1000) DEFAULT NULL,
   `screen_name` VARCHAR(1000) DEFAULT NULL,
   `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Advanced Report Audit sections, actions and listeners
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
 ('AdvancedReport', 'Reports', 'Report', @audit_reports_and_analytics_module_id);

SET @advanced_report_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AdvancedReport' AND module_id = @audit_reports_and_analytics_module_id);
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
 (@advanced_report_section_id, 'ADD REPORT', 'Add Report'),
 (@advanced_report_section_id, 'UPDATE REPORT SETTINGS', 'Update Report Settings');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
     ('reports.add', 'AddReportAuditListener', 1, @max_event_listener_position + 1),
     ('reports.update', 'UpdateReportAuditListener', 1, @max_event_listener_position + 2);

SET @ohrm_data_group_schedule_report_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'schedule_report');
SET @ohrm_data_group_schedule_pim_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'pim_reports');

SET @ohrm_audittrail_section_report_schedule = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='ReportSchedule');
SET @ohrm_audittrail_section_advanced_report = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AdvancedReport');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
    (@ohrm_audittrail_section_advanced_report, @ohrm_data_group_schedule_pim_id, 0),
    (@ohrm_audittrail_section_report_schedule, @ohrm_data_group_schedule_report_id, 0),
    (@connections_section_id, @ohrm_data_group_schedule_report_id, 0);
