CREATE TABLE IF NOT EXISTS ohrm_dashboard_layout_type (
     `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     `name` VARCHAR(255) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS ohrm_dashboard_layout (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT DEFAULT NULL,
    `layout_type_id` INT DEFAULT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_dashboard_layout_layout_type_id` FOREIGN KEY (`layout_type_id`) REFERENCES `ohrm_dashboard_layout_type` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS ohrm_dashboard_widget (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(1024) NOT NULL,
    `widget_class` VARCHAR(1024) NOT NULL,
    `config` TEXT
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS ohrm_dashboard_layout_widget (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `widget_id` INT NOT NULL,
    `layout_id` INT NOT NULL,
    `config` TEXT,
    `size` VARCHAR(255) NOT NULL DEFAULT 'size_d',
    `view_order` INT DEFAULT 1,
    FOREIGN KEY (`widget_id`) REFERENCES `ohrm_dashboard_widget` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`layout_id`) REFERENCES `ohrm_dashboard_layout` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO `ohrm_dashboard_layout_type`(`id`, `name`) VALUES
(1, 'Main Dashboard'),
(2, 'Employee Profile');
INSERT INTO `ohrm_dashboard_layout`(`id`, `user_id`, `layout_type_id`) VALUES
(1, null, 1),
(2, null, 2);

SET @enabledConfigKey = CONCAT('{"isEnabled":true, "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}}');
SET @disabledConfigKey = CONCAT('{"isEnabled":false, "availableTo": {"admin":false, "ess":false, "supervisor":false, "HeadOfDepartment":false}, "defaultSelectedTo": {"admin":false, "ess":false, "supervisor":false, "HeadOfDepartment":false}}');

SET @myActionWidgetConfigKey = CONCAT('{"isEnabled":true, "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true, "HiringManager":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true, "HiringManager":true},"filteringRangeLimit":{"appraisals":"-3:3","leavesToApprove":"-1:1","timesheetsToApprove":"-3:0","goalsToApprove":"-3:3","onboardingEventsToShow":"-1:1"}, "filteringWorkflowStatus":{"timesheetsToApprove":["SUBMITTED"]}}');
SET @quickFeedbackConfigKey = CONCAT('{"isEnabled":true, "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":false, "supervisor":true, "HeadOfDepartment":true}}');
SET @employeeOnLeaveConfigKey = CONCAT('{"isEnabled":true, "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true},"excludedLeaveStatuses":["REJECTED","CANCELLED"]}');
SET @leaveBalanceConfigKey = CONCAT('{"isEnabled":true, "widgetIcon": "oxd-leave-dashboard", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}}');
SET @leaveListConfigKey = CONCAT('{"isEnabled":true, "widgetIcon": "oxd-leaving", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}}');
SET @timesheetsConfigKey = CONCAT('{"isEnabled":true, "widgetIcon": "oxd-employee-time", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}}');
SET @employeeDetailsConfigKey = CONCAT('{"isEnabled":true,  "widgetIcon": "oxd-about-employee", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}}');
SET @employeeQuickAccessConfigKey = CONCAT('{"isEnabled":true,  "widgetIcon": "oxd-quick-access-icon", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "accessLinks": [

      {
         "key":"appraisals",
         "moduleName":"Performance",
         "screenName":"Appraisals",
         "dataGroup":"employee_appraisals",
         "selfdataGroup":"my_appraisals",
         "licenceModule":"performance",
         "icon":"oxd-quick-access-appraisal",
         "color":"#FDC461",
         "redirectionStrategy":"uisref",
         "state":"performance.view_appraisals_list",
         "myInfoState":"performance.view_my_appraisals"
      },
      {
         "key":"goals",
         "moduleName":"Goals",
         "screenName":"Goals",
         "dataGroup":"Goals",
         "licenceModule":"performance",
         "icon":"oxd-quick-access-goals",
         "color":"#6FD2B8",
         "redirectionStrategy":"uisref",
        "state":"performance.goals.approved",
        "myInfoState":"performance.goals.my"
      },
      {
         "key":"trackers",
         "moduleName":"Performance",
         "screenName":"Trackers",
         "licenceModule":"performanceTracker",
         "api":"performanceTrackers",
         "icon":"oxd-quick-access-tracker",
         "color":"#35B4D0",
         "redirectionStrategy":"href",
         "url":"/client/#/noncore/performanceTracker/addPerformanceTrackerLog/empNumber/_##_empNumber_##_/trackId/_##_id_##_"
      },
      {
         "key":"individualDevelopmentPlans",
         "moduleName":"Career Development",
         "screenName":"Individual Development Plans",
         "dataGroup":"individual_development_plan",
         "licenceModule":"succession & development",
         "icon":"oxd-quick-access-career",
         "color":"#6774E7",
         "redirectionStrategy":"uisref",
         "state":"succession_and_development.list_idp",
         "myInfoState":"succession_and_development.my_idp"
      },
{
         "key":"disciplinaryCases",
         "moduleName":"Discipline",
         "screenName":"Disciplinary Cases",
         "dataGroup":"disciplinary_case",
         "licenceModule":"discipline",
         "icon":"oxd-quick-access-diciplinary",
         "color":"#FE8833",
         "redirectionStrategy":"href",
         "url":"/client/#/noncore/discipline/viewDisciplinaryCases/empNumber/_##_empNumber_##_"
      },
      {
         "key":"candidateProfile",
         "moduleName":"Recruitment",
         "screenName":"Candidate Profile",
         "dataGroup":"recruitment_candidates",
         "icon":"oxd-quick-access-candidate-profile",
         "color":"#F15E93",
         "redirectionStrategy":"href",
         "url":"/client/#/recruitment/candidateProfile/candidateId/_##_candidateId_##_/vacancyId/_##_vacancyId_##_",
         "target": "_blank",
         "noRedirectBack": true
      }
]}');
SET @goalsConfigKey = CONCAT('{"isEnabled":true, "widgetIcon": "oxd-dashboard-performance", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "filterFields" : [
  {
    "name": "from",
    "type": "date",
    "label": "From"
  },
  {
    "name": "to",
    "type": "date",
    "label": "To"
  },

  {
    "name": "goals",
    "type": "switch",
    "label": "Goals"
  },
  {
    "name": "okrs",
    "type": "switch",
    "label": "OKRs"
  }
]}');
SET @trackersConfigKey = CONCAT('{"isEnabled":true, "widgetIcon": "oxd-quick-access-tracker", "availableTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}, "defaultSelectedTo": {"admin":true, "ess":true, "supervisor":true, "HeadOfDepartment":true}}');


SET @widgetInsertQuery = CONCAT('INSERT INTO `ohrm_dashboard_widget`(`id`, `name`,`display_name`, `widget_class`, `config`) VALUES
(1, "my-actions-widget", "My Actions", "MyActionsDashboardWidget", ''',@myActionWidgetConfigKey,'''),
(2, "quick-access-widget", "Quick Access", "QuickAccessWidget", ''',@enabledConfigKey,'''),
(3, "employee-on-leave-widget","Employees on Leave Today", "EmployeeOnLeaveTodayDashboardWidget", ''',@employeeOnLeaveConfigKey,'''),
(4, "time-at-work-widget", "Time At Work", "TimeAtWorkWidget", ''',@enabledConfigKey,'''),
(5, "latest-news-widget", "Latest News", "LatestNewsWidget", ''',@enabledConfigKey,'''),
(6, "latest-documents-widget", "Latest Documents", "LatestDocumentsWidget", ''',@enabledConfigKey,'''),
(7, "performance-tracker-quick-add-widget", "Performance Quick Feedback", "PerformanceTrackerQuickAddWidget", ''',@quickFeedbackConfigKey,'''),
(8, "buzz-posts-widget", "Buzz Latest Posts", "BuzzLatestPostDashboardWidget", ''',@disabledConfigKey,'''),
(9, "vue-employee-details-widget", "About", "EmployeeDetailsDashboardWidget", ''',@employeeDetailsConfigKey,'''),
(10, "vue-employee-quick-access-widget", "Quick Access", "EmployeeQuickAccessWidget", ''',@employeeQuickAccessConfigKey,'''),
(11, "vue-leave-balance-widget", "Leave Balance", "LeaveBalanceDashboardWidget", ''',@leaveBalanceConfigKey,'''),
(12, "vue-leave-list-widget", "Leave List", "LeaveListDashboardWidget", ''',@leaveListConfigKey,'''),
(13, "vue-timesheets-widget", "Timesheets", "TimesheetsDashboardWidget", ''',@timesheetsConfigKey,'''),
(14, "time-at-work-widget", "Time At Work", "EmployeeTimeAtWorkWidget", ''',@enabledConfigKey,'''),
(15, "vue-goals-widget", "Goals/OKRs", "GoalsDashboardWidget", ''',@goalsConfigKey,'''),
(16, "vue-trackers-widget", "Performance Tracker Logs", "EmployeeTrackersWidget", ''',@trackersConfigKey,''')');

PREPARE statement FROM @widgetInsertQuery;
EXECUTE statement;

INSERT INTO `ohrm_dashboard_layout_widget`(`id`,`layout_id`,`widget_id`,`view_order`, `size`, `config`) VALUES
(1,1,1,1,'size_d', '{"isEnabled":null,"settings":{"showPendingLeavesToApprove":true,"showPendingTimesheetsToApprove":true,"showInterviews":true,"showTrainings":true,"showOnboardingEvents":true,"showPendingAppraisalReviews":true,"showGoalsToApprove":true,"pendingApprovalLeaveView":"summary","pendingApprovalTimesheetView":"summary","pendingApprovalGoalView":"summary","pendingAppraisalReviewView":"summary","showPendingAttendanceSheetsToApprove":true,"pendingApprovalAttendanceSheetView":"summary"}}'),
(2,1,2,2,'size_d', '{"isEnabled":null}'),
(3,1,3,3,'size_d', '{"isEnabled":null,"settings":{"onlyReportingEmployees":false}}'),
(4,1,4,4,'size_d', '{"isEnabled":null}'),
(5,1,5,5,'size_d', '{"isEnabled":null}'),
(6,1,6,6,'size_d', '{"isEnabled":null}'),
(7,1,7,7,'size_d', '{"isEnabled":null,"settings":{"feedbackPeriod":"6"}}'),
(8,1,8,8,'size_d', '{"isEnabled":null}'),
(9,2,9,1,'size_g', '{"isEnabled":null, "settings": {"showBasicInfo": true, "showJobInfo": true, "showContactInfo": true}}'),
(10,2,10,2,'size_d', '{"isEnabled":null,"settings": {"disciplinaryCases": true, "goals": true, "appraisals": true,"trackers": true,"individualDevelopmentPlans": true, "candidateProfile": true}}'),
(11,2,11,3,'size_d', '{"isEnabled":null}'),
(12,2,12,4,'size_d', '{"isEnabled":null}'),
(13,2,13,5,'size_d', '{"isEnabled":null}'),
(14,2,14,6,'size_d', '{"isEnabled":null}'),
(15,2,15,7,'size_g', '{"isEnabled":null, "settings": {"from": null,"to": null,"goals": true,"okrs": true}}'),
(16,2,16,8,'size_d', '{"isEnabled":null}');


SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('dashboard.report_change_for_update_dashboard_widget', 'ReportChangeListenerForDashboardWidgetUpdate', '1', @max_event_listener_position+1);

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');

SET @module_id := (SELECT `id` FROM ohrm_module WHERE `name` = 'dashboard');

SET @dashboard_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'Dashboard');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(1, @dashboard_screen_id),
(2, @dashboard_screen_id);

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 @employee_management_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management' AND parent_id = @people_management_pillar_menu_id AND level = 2);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`, `uisref`) VALUES
('Dashboard', @dashboard_screen_id, @employee_management_module_menu_id, 3, 1200, NULL, 1, 'dashboard');

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

INSERT INTO ohrm_home_page (`user_role_id`, `action`, `enable_class`, `priority`) VALUES
(@admin_role_id, '/client/#/dashboard', NULL, 15),
(@ess_role_id, '/client/#/dashboard', NULL, 5);

DELIMITER $$
DROP FUNCTION IF EXISTS dashboard_get_subunit_parent_id;$$

CREATE FUNCTION  dashboard_get_subunit_parent_id
(
  id INT
)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
SELECT (SELECT t2.id
               FROM ohrm_subunit t2
               WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt
               ORDER BY t2.rgt-t1.rgt ASC LIMIT 1) INTO @parent
FROM ohrm_subunit t1 WHERE t1.id = id;

RETURN @parent;

END;$$

-- grant execute on function dashboard.dashboard_get_subunit_parent_id  to 'dashboard'@'localhost';

DELIMITER ;
