INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('succession & development', '1');
SET @succession_and_development_module_id := (SELECT `id`
                                              FROM `ohrm_module`
                                              WHERE `name` = 'succession & development');

-- 9 box matrix related changes
SET @config_admin_user_role_id := (SELECT id
                                   FROM `ohrm_user_role`
                                   WHERE `name` = 'ConfigAdmin');

SET @admin_user_role_id := (SELECT id
                            FROM `ohrm_user_role`
                            WHERE `name` = 'Admin');
SET @supervisor_user_role_id := (SELECT id
                                 FROM `ohrm_user_role`
                                 WHERE `name` = 'Supervisor');
SET @hod_user_role_id := (SELECT id
                          FROM `ohrm_user_role`
                          WHERE `name` = 'HeadOfDepartment');
SET @ess_user_role_id := (SELECT id
                          FROM `ohrm_user_role`
                          WHERE `name` = 'ESS');

SET @admin_user_role_type_id := (SELECT id
                                 FROM `ohrm_user_role_type`
                                 WHERE `name` = 'Admin');
SET @supervisor_user_role_type_id := (SELECT id
                                      FROM `ohrm_user_role_type`
                                      WHERE `name` = 'Supervisor');
SET @ess_user_role_type_id := (SELECT id
                                      FROM `ohrm_user_role_type`
                                      WHERE `name` = 'ESS');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('successionAndDevelopment9Box', @succession_and_development_module_id, 'successionAndDevelopment9Box');
SET @succession_and_development_9box_screen_id := (SELECT id
                                                   FROM ohrm_screen
                                                   WHERE `name` = 'successionAndDevelopment9Box');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  (@admin_user_role_id, @succession_and_development_9box_screen_id, 1, 1, 1, 1),
  (@supervisor_user_role_id, @succession_and_development_9box_screen_id, 1, 1, 1, 1),
  (@hod_user_role_id, @succession_and_development_9box_screen_id, 1, 1, 1, 1);

SET @culture_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Culture' AND parent_id IS NULL AND level = 1);
SET @career_development_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Career Development' AND level = 2 AND parent_id = @culture_pillar_menu_id);

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`)
VALUES
  (NULL, '9 Box Matrix', NULL, @succession_and_development_9box_screen_id, @career_development_menu_id, 3, 200, NULL, NULL,
         'succession_and_development.9_box_matrix_dashboard', 0, NULL);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('nine_box_matrix', 'Career Development - 9 Box Matrix', 1, 0, 0, 0);
SET @succession_and_development_9box_matrix_data_group_id := (SELECT id
                                                              FROM ohrm_data_group
                                                              WHERE name = 'nine_box_matrix');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
  (@succession_and_development_9box_matrix_data_group_id, @succession_and_development_9box_screen_id, '1');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
  (@admin_user_role_id, @succession_and_development_9box_matrix_data_group_id, 1, 0, 0, 0, 0),
  (@supervisor_user_role_id, @succession_and_development_9box_matrix_data_group_id, 1, 0, 0, 0, 0),
  (@hod_user_role_id, @succession_and_development_9box_matrix_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
  (@admin_user_role_type_id, @succession_and_development_9box_matrix_data_group_id),
  (@supervisor_user_role_type_id, @succession_and_development_9box_matrix_data_group_id);

-- 9 box matrix configuration related changes
SET @succession_and_development_module_id := (SELECT `id`
                                              FROM `ohrm_module`
                                              WHERE `name` = 'succession & development');
SET @admin_user_role_id := (SELECT id
                            FROM `ohrm_user_role`
                            WHERE `name` = 'Admin');


INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('successionAndDevelopment9BoxMatrixConfig', @succession_and_development_module_id, 'successionAndDevelopment9BoxMatrixConfig');

SET @screen_id := (SELECT id
                   FROM ohrm_screen
                   WHERE name = 'successionAndDevelopment9BoxMatrixConfig');

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
  ('Configuration', NULL, @career_development_menu_id, 3, 400, NULL, 1);
SET @succession_and_development_configuration_menu_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`)
VALUES
  (NULL, '9 Box Matrix', NULL, @screen_id, @succession_and_development_configuration_menu_id, 4, 100, NULL, NULL,
         'succession_and_development.9_box_matrix_config', '1', NULL, NULL);

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

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('nine_box_matrix_configuration', 'Career Development - 9 Box Matrix Configuration', '1', '0', '1', '0');
SET @data_group_id := (SELECT id
                       FROM ohrm_data_group
                       WHERE name = 'nine_box_matrix_configuration');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
  (@data_group_id, @screen_id, '1'),
  (@data_group_id, @screen_id, '3');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`, `restriction_id`) VALUES
  (@admin_user_role_id, @data_group_id, '1', '0', '1', '0', '0', NULL);

--  Configuration
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('succession_and_development.9box_matrix', '0');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('succession_and_development.9box_matrix_appraisal_consider_as_completed', 'COMPLETED,CLOSED');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('succession_and_development.show_potential_assessment_section_for_evaluated_employee', '0');

-- Section and Question related queries
INSERT INTO `ohrm_answer_type` (type_name, class, close_ended, filterable) values
  ('Rating Dropdown', 'RatingDropdownType', 1, 1);

SET @questionnaire_id := (SELECT id
                          FROM `ohrm_questionnaire`
                          WHERE `questionnaire_name` = 'appraisal');
SET @type_id := (SELECT id
                 FROM `ohrm_answer_type`
                 WHERE `type_name` = 'Rating Dropdown');

INSERT INTO `ohrm_question_tag` (`id`, `tag_name`, `questionnaire_id`) VALUES (NULL, 'potential_section_questions', @questionnaire_id);

INSERT INTO `ohrm_question` (`id`, `type_id`, `questionnaire_id`, `question_name`, `question_description`, `options`, `is_default`, `is_deleted`, `form_specific`, `group_id`, `difficulty`, `default_answer`, `mandatory`)
VALUES
  (NULL, @type_id, @questionnaire_id, 'question for appraisal potential section', 'What is the potential job level the individual is capable of attaining?',
         '{"values":{"1":"Limited to current role only or possible bad fit","2":"Good fit at current level, lateral move, or upward 1 level","3":"Highly capable to attain higher roles, upward mobility more than 1 level"}}',
         NULL, '0', '0', NULL, NULL, NULL, 0);

SET @question_id := (SELECT id
                     FROM `ohrm_question`
                     where question_name = 'question for appraisal potential section');
SET @tag_id := (SELECT id
                FROM `ohrm_question_tag`
                where tag_name = 'potential_section_questions');

INSERT INTO `ohrm_question_question_tag` (`id`, `tag_id`, `question_id`) VALUES (NULL, @tag_id, @question_id);

SET @question_id := (SELECT id
                     FROM `ohrm_question`
                     where question_name = 'question for appraisal potential section');
SET @final_rating_source_evaluator_group_id := (SELECT id
                                                FROM `ohrm_performance_evaluator_group`
                                                WHERE `group_name` = (SELECT value
                                                                      FROM `ohrm_config`
                                                                      WHERE `property` = 'performance.final_rating_source'));

INSERT INTO `ohrm_performance_eval_group_questions` (`appraisal_evaluator_group_id`, `question_id`)
VALUES (@final_rating_source_evaluator_group_id, @question_id);

INSERT INTO `ohrm_performance_appraisal_global_form_configuration` (`id`, `section_title`, `competency_matching_criteria`, `goal_matching_criteria`, `question_matching_criteria`, `weight`, `disabled`, `readonly`)
VALUES (NULL, 'Potential Assessment', NULL, NULL,
        'SELECT q.id FROM `ohrm_question` as q, `ohrm_question_question_tag` as qt, `ohrm_question_tag` as t WHERE q.is_deleted = 0 AND t.id = qt.tag_id AND q.id = qt.question_id AND t.tag_name = \'potential_section_questions\'',
        NULL, 1, 1),
  (NULL, 'Development', NULL,
   'SELECT DISTINCT goal.id FROM `ohrm_performance_goal` goal LEFT JOIN `ohrm_goal_purpose` purpose ON goal.`goal_purpose_id`= purpose.id LEFT JOIN ohrm_individual_development_plan_goal idpg ON (idpg.goal_id = goal.id) LEFT JOIN ohrm_individual_development_plan idp ON (idp.id = idpg.idp_id) LEFT JOIN ohrm_individual_development_plan_status idps ON (idps.state_id = idp.state_id) WHERE purpose.name = "Development" AND idps.name != "NOT INITIATED" AND idps.name IS NOT NULL AND goal.emp_number = #empNumber# AND goal.workflow_status_id = #approvedState# AND goal.due_date BETWEEN #appraisalStartDate# AND #appraisalEndDate#',
   NULL, NULL, '0', '1');

--
INSERT INTO `ohrm_performance_category` (`id`, `name`, `display_name`, `final_rating_percentage_floor_value`, `value`) VALUES
  (1, 'low', 'Low', 0, 1),
  (2, 'moderate', 'Moderate', 40, 2),
  (3, 'high', 'High', 80, 3);

INSERT INTO `ohrm_potential_category` (`id`, `name`, `display_name`, `value`) VALUES
  (1, 'low', 'Low', 1),
  (2, 'moderate', 'Moderate', 2),
  (3, 'high', 'High', 3);

INSERT INTO `ohrm_nine_box_master_data` (`id`, `name`, `display_name`, `colour`) VALUES
  (1, 'under_performer', 'Under Performer', '#9977D6'),
  (2, 'effective_employee', 'Effective Employee', '#E485B6'),
  (3, 'trusted_professional', 'Trusted Professional', '#DF6BA8'),
  (4, 'assess', 'Assess', '#FBBA4E'),
  (5, 'core_employee', 'Core Employee', '#83C25D'),
  (6, 'high_impact_contributor', 'High Impact Contributor', '#01AFD7'),
  (7, 'enigma', 'Enigma', '#00AFD7'),
  (8, 'growth_employee', 'Growth Employee', '#81C15B'),
  (9, 'star', 'Star', '#00BE9E');

SET @event_listener_max_position := (SELECT MAX(`position`)
                                     FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
  (NULL, 'performance.nine_box_matrix_configuration_update', 'NineBoxMatrixConfigurationUpdateListener', '1', @event_listener_max_position + 1);

-- 9 Box Matrix audit trail
INSERT INTO `ohrm_audittrail_module` (`id`, `name`) VALUES (NULL, 'Career Development');
SET @audittrail_module_id := (SELECT `id`
                              FROM `ohrm_audittrail_module`
                              WHERE `name` = 'Career Development');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`)
VALUES ('9BoxMatrixConfiguration', '9 Box Matrix Configuration', '', @audittrail_module_id);
SET @performance_9_box_matrix_configuration_audit_trail_section := (SELECT `id`
                                                                    FROM `ohrm_audittrail_section`
                                                                    WHERE `name` = '9BoxMatrixConfiguration' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_9_box_matrix_configuration_audit_trail_section, 'UPDATE 9 BOX MATRIX CONFIGURATION', 'Update 9 Box Matrix Configuration');

SET @event_listener_max_position := (SELECT MAX(`position`)
                                     FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
  (NULL, 'succession_and_development.nine_box_matrix_configuration_update_for_audit', 'SuccessionAndDevelopmentNineBoxMatrixConfigurationListener', '1',
   @event_listener_max_position + 1),
  (NULL, 'performance.appraisal_submission', 'PerformanceAppraisalSubmissionListener', '1', @event_listener_max_position + 2);

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
  ('succession_and_development.performance_category_mapper', 'PercentageBasedPerformanceCategoryMapper'),
  ('succession_and_development.potential_category_mapper', 'AnswerBasedPotentialCategoryMapper'),
  ('succession_and_development.nine_box_category_mapper', 'BaseNineBoxCategoryMapper');


INSERT INTO `ohrm_individual_development_plan_status` (`state_id`, `name`, `display_name`, `consider_as_closed`) VALUES
  (0, 'NOT INITIATED', 'Not Initiated', 0),
  (1, 'INITIATED', 'Initiated', 0),
  (2, 'IN PROGRESS', 'In Progress', 0),
  (3, 'CLOSED', 'Closed', 1);

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('individualDevelopmentPlans', @succession_and_development_module_id, 'individualDevelopmentPlans');
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES ('myDevelopmentPlan', @succession_and_development_module_id, 'myDevelopmentPlan');
SET @individual_development_plans_screen_id := (SELECT id
                                                FROM ohrm_screen
                                                WHERE `name` = 'individualDevelopmentPlans');
SET @my_development_plan_screen_id := (SELECT id
                                       FROM ohrm_screen
                                       WHERE `name` = 'myDevelopmentPlan');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  (@admin_user_role_id, @individual_development_plans_screen_id, 1, 1, 1, 1),
  (@supervisor_user_role_id, @individual_development_plans_screen_id, 1, 1, 1, 1),
  (@hod_user_role_id, @individual_development_plans_screen_id, 1, 1, 1, 1);

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

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`)
VALUES
  ('Individual Development Plans', NULL, @individual_development_plans_screen_id, @career_development_menu_id, 3, 100, NULL, NULL,
    'succession_and_development.list_idp', 1, NULL);

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`)
VALUES
  ('My IDP', NULL, @my_development_plan_screen_id, @career_development_menu_id, 3, 300, NULL, NULL, 'succession_and_development.my_idp', 1,
    NULL);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
  ('individual_development_plan', 'Career Development - Individual Development Plan', 1, 1, 1, 1);
SET @individual_development_plan_data_group_id := (SELECT id
                                                   FROM ohrm_data_group
                                                   WHERE name = 'individual_development_plan');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
  (@individual_development_plan_data_group_id, @individual_development_plans_screen_id, '1');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES
  (@individual_development_plan_data_group_id, @my_development_plan_screen_id, 1, 1);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
  (@admin_user_role_id, @individual_development_plan_data_group_id, 1, 1, 1, 1, 0),
  (@supervisor_user_role_id, @individual_development_plan_data_group_id, 1, 1, 1, 1, 0),
  (@hod_user_role_id, @individual_development_plan_data_group_id, 1, 1, 1, 1, 0),
  (@ess_user_role_id, @individual_development_plan_data_group_id, 1, 0, 1, 0, 1);

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, @individual_development_plan_data_group_id, 1, 1, 1, 1),
  (@supervisor_user_role_type_id, @individual_development_plan_data_group_id, 1, 1, 1, 1),
  (@ess_user_role_type_id, @individual_development_plan_data_group_id, 1, 0, 1, 0);

INSERT INTO `ohrm_individual_development_plan_goal_targeted_role` (`id`, `name`, `display_name`, `icon`) VALUES
  (1, 'current', 'Current Role', 'ohrm_idp_current_role'),
  (2, 'future', 'Future Role', 'ohrm_idp_future_role'),
  (3, 'general', 'General', 'ohrm_idp_misc_role');

# Coach user role related changes
INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`)
VALUES ('IndividualDevelopmentPlanCoach', 'Coach', '0', '1');

SET @idp_coach_user_role_id := (SELECT `id`
                                FROM ohrm_user_role
                                WHERE `name` = 'IndividualDevelopmentPlanCoach');

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

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
  (@idp_coach_user_role_id, @individual_development_plan_data_group_id, 1, 0, 1, 0, 0);

# IDP audit trail related changes

SET @audittrail_module_id := (SELECT `id`
                              FROM `ohrm_audittrail_module`
                              WHERE `name` = 'Career Development');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`)
VALUES ('IndividualDevelopmentPlan', 'Individual Development Plans', 'Employee', @audittrail_module_id);
SET @idp_audit_trail_section := (SELECT `id`
                                 FROM `ohrm_audittrail_section`
                                 WHERE `name` = 'IndividualDevelopmentPlan' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@idp_audit_trail_section, 'ADD IDP', 'Add IDP'),
  (@idp_audit_trail_section, 'UPDATE IDP', 'Update IDP'),
  (@idp_audit_trail_section, 'DELETE IDP', 'Delete IDP'),
  (@idp_audit_trail_section, 'ADD GOAL TO IDP', 'Add Goal to IDP'),
  (@idp_audit_trail_section, 'REMOVE GOAL FROM IDP', 'Remove Goal from IDP'),
  (@idp_audit_trail_section, 'INITIATE IDP', 'Initiate IDP'),
  (@idp_audit_trail_section, 'ACKNOWLEDGE IDP', 'Acknowledge IDP'),
  (@idp_audit_trail_section, 'CLOSE IDP', 'Close IDP'),
  (@idp_audit_trail_section, 'REOPEN IDP', 'Reopen IDP');

SET @event_listener_max_position := (SELECT MAX(`position`)
                                     FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
  ('succession_and_development.idp_add', 'IndividualDevelopmentPlanAddListener', '1', @event_listener_max_position + 1),
  ('succession_and_development.idp_update', 'IndividualDevelopmentPlanUpdateListener', '1', @event_listener_max_position + 2),
  ('succession_and_development.idp_delete', 'IndividualDevelopmentPlanDeleteListener', '1', @event_listener_max_position + 3),
  ('succession_and_development.idp_goal_add', 'IndividualDevelopmentPlanAddGoalListener', '1', @event_listener_max_position + 4),
  ('succession_and_development.idp_goal_remove', 'IndividualDevelopmentPlanRemoveGoalListener', '1', @event_listener_max_position + 5),
  ('succession_and_development.idp_workflow_state_change', 'IndividualDevelopmentPlanStateChangeAuditListener', '1', @event_listener_max_position + 6),
  ('succession_and_development.idp_update', 'IndividualDevelopmentPlanNotificationListener', '1', @event_listener_max_position + 7),
  ('succession_and_development.idp_add', 'IndividualDevelopmentPlanNotificationListener', '1', @event_listener_max_position + 8);

  SET @max_workflow_order_no := (SELECT MAX(`order_no`) FROM `ohrm_workflow`);

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`) VALUES
('INDIVIDUAL DEVELOPMENT PLAN', 'IndividualDevelopmentPlan', 'Succession and Development - Individual Development Plan', '1,3','2' , null, null, @succession_and_development_module_id, @max_workflow_order_no + 1);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `roles_to_notify`, `screens_to_show`, `priority`) VALUES
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIAL', 'ADMIN', 'Save', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'ADMIN', 'Update', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'ADMIN', 'Initiate', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'ADMIN', 'Update', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'ADMIN', 'Close', 'CLOSED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'CLOSED', 'ADMIN', 'Reopen', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'ESS USER', 'Acknowledge', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'ADMIN', 'Update', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'ADMIN', 'Close', 'CLOSED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIAL', 'SUPERVISOR', 'Save', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'SUPERVISOR', 'Update', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'SUPERVISOR', 'Initiate', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'SUPERVISOR', 'Update', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'SUPERVISOR', 'Update', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'IndividualDevelopmentPlanCoach', 'Update', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'IndividualDevelopmentPlanCoach', 'Initiate', 'INITIATED', '', NULL, 1),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'IndividualDevelopmentPlanCoach', 'Update', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'IndividualDevelopmentPlanCoach', 'Close', 'CLOSED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'IndividualDevelopmentPlanCoach', 'Update', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'IndividualDevelopmentPlanCoach', 'Close', 'CLOSED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'CLOSED', 'IndividualDevelopmentPlanCoach', 'Reopen', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIAL', 'HEADOFDEPARTMENT', 'Save', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'HEADOFDEPARTMENT', 'Update', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'HEADOFDEPARTMENT', 'Initiate', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'HEADOFDEPARTMENT', 'Update', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'HEADOFDEPARTMENT', 'Update', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIAL', 'MAIN-EVALUATOR', 'Save', 'NOT INITIATED', '', NULL, '0'),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIAL', 'AppraisalSupervisorReviewer', 'Save', 'NOT INITIATED', '', NULL, '0'),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'MAIN-EVALUATOR', 'Update', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'AppraisalSupervisorReviewer', 'Update', 'NOT INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'MAIN-EVALUATOR', 'Update', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'INITIATED', 'AppraisalSupervisorReviewer', 'Update', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'MAIN-EVALUATOR', 'Update', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'IN PROGRESS', 'AppraisalSupervisorReviewer', 'Update', 'IN PROGRESS', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'MAIN-EVALUATOR', 'Initiate', 'INITIATED', '', NULL, 0),
  ('INDIVIDUAL DEVELOPMENT PLAN', 'NOT INITIATED', 'AppraisalSupervisorReviewer', 'Initiate', 'INITIATED', '', NULL, 0);

  # IDP Notifications

SET @action_notification_type := (SELECT id
                                  FROM ohrm_notification_type
                                  where `name` = 'Action Based');

SET @idp_workflow_id := (SELECT id
                          FROM ohrm_workflow
                          where `name` = 'IndividualDevelopmentPlan');

SET @supervisor_user_role_id := (SELECT id
                              FROM ohrm_user_role
                              WHERE `name` = 'Supervisor');

SET @hod_user_role_id := (SELECT id
                              FROM ohrm_user_role
                              WHERE `name` = 'HeadOfDepartment');


SET @idp_coach_user_role_id := (SELECT `id`
                                FROM ohrm_user_role
                                WHERE `name` = 'IndividualDevelopmentPlanCoach');

SET @email_media := (SELECT id
                     FROM ohrm_notification_media
                     WHERE `name` = 'Email');

SET @succession_and_development_module_id := (SELECT `id`
                            FROM `ohrm_module`
                            WHERE `name` = 'succession & development');

-- Create IDP - Coach
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Individual Development Plan Creation', 1, @action_notification_type, 'IndividualDevelopmentPlanEmailProcessorWrapperEventNotification',
   '{"email_processor":"IndividualDevelopmentPlanEmailProcessor","extra_roles":["HeadOfDepartment","IndividualDevelopmentPlanCoach"]}', @idp_workflow_id, 'Save', @succession_and_development_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
  (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @idp_workflow_id AND `action` = 'Save';
SET @last_subscription_id := LAST_INSERT_ID();

# Template for Coach
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Creation Notification Sent To Coach',
   'Individual Development Plan Created',
   '<p>Dear %subscriberName%,</p>
<p>You have been added as the coach of the Individual Development Plan (IDP) of %idpOwnerName%.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();


-- Subscriber for Coach
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @idp_coach_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Coach
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;


-- Update IDP - Coach (if coach is changed)
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Individual Development Plan Update', 1, @action_notification_type, 'IndividualDevelopmentPlanEmailProcessorWrapperEventNotification',
   '{"email_processor":"IndividualDevelopmentPlanEmailProcessor","extra_roles":["HeadOfDepartment"]}', @idp_workflow_id, 'Update', @succession_and_development_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
  (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @idp_workflow_id AND `action` = 'Update';
SET @last_subscription_id := LAST_INSERT_ID();

# Template for Coach
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Update Notification Sent To New Coach',
   'Individual Development Plan Updated',
   '<p>Dear %subscriberName%,</p>
<p>You have been added as the coach of the Individual Development Plan (IDP) of %idpOwnerName%.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();

-- Subscriber for Coach
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Coach');

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Coach
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Initiate IDP - Employee
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Individual Development Plan Initiation', 1, @action_notification_type, 'IndividualDevelopmentPlanEmailProcessorWrapperEventNotification',
   '{"email_processor":"IndividualDevelopmentPlanEmailProcessor","extra_roles":["HeadOfDepartment","IndividualDevelopmentPlanCoach"]}', @idp_workflow_id, 'Initiate', @succession_and_development_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
  (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @idp_workflow_id AND `action` = 'Initiate';
SET @last_subscription_id := LAST_INSERT_ID();

# Template for Employee
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Initiation Notification Sent To Employee',
   'Individual Development Plan Initiated',
   '<p>Dear %subscriberName%,</p>
<p>An Individual Development Plan (IDP) has been created for you.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();

-- Subscriber for Employee
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Employee
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

# Template for Coach, Supervisor, HOD
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Initiation Notification Sent To Coach, Supervisor and Head of Department',
   'Individual Development Plan Initiated',
   '<p>Dear %subscriberName%,</p>
<p>The Individual Development Plan (IDP) of %idpOwnerName% has been initiated.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();

-- Subscriber for Coach
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @idp_coach_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Coach
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Subscriber for Supervisor
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @supervisor_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Supervisor
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Subscriber for HOD
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @hod_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for HOD
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Acknowledge IDP - Coach, Supervisor, HOD
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Individual Development Plan Acknowledgment', 1, @action_notification_type, 'IndividualDevelopmentPlanEmailProcessorWrapperEventNotification',
   '{"email_processor":"IndividualDevelopmentPlanEmailProcessor","extra_roles":["HeadOfDepartment","IndividualDevelopmentPlanCoach"]}', @idp_workflow_id, 'Acknowledge', @succession_and_development_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
  (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @idp_workflow_id AND `action` = 'Acknowledge';
SET @last_subscription_id := LAST_INSERT_ID();

# Template for Coach, Supervisor, HOD
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Acknowledgment Notification Sent To Coach, Supervisor and Head of Department',
   'Individual Development Plan Acknowledged',
   '<p>Dear %subscriberName%,</p>
<p>%idpOwnerName% has acknowledged the Individual Development Plan: %idpName%.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();

-- Subscriber for Coach
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @idp_coach_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Coach
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Subscriber for Supervisor
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @supervisor_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Supervisor
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Subscriber for HOD
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @hod_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for HOD
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Close IDP - Employee
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Individual Development Plan Closure', 1, @action_notification_type, 'IndividualDevelopmentPlanEmailProcessorWrapperEventNotification',
   '{"email_processor":"IndividualDevelopmentPlanEmailProcessor","extra_roles":["HeadOfDepartment","IndividualDevelopmentPlanCoach"]}', @idp_workflow_id, 'Close', @succession_and_development_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
  (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @idp_workflow_id AND `action` = 'Close';
SET @last_subscription_id := LAST_INSERT_ID();

# Template for Employee
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Closure Notification Sent To Employee',
   'Individual Development Plan Closed',
   '<p>Dear %subscriberName%,</p>
<p>Your Individual Development Plan (IDP) has been closed.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();

-- Subscriber for Employee
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Employee
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

-- Reopen IDP - Employee
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Individual Development Plan Reopening', 1, @action_notification_type, 'IndividualDevelopmentPlanEmailProcessorWrapperEventNotification',
   '{"email_processor":"IndividualDevelopmentPlanEmailProcessor","extra_roles":["HeadOfDepartment","IndividualDevelopmentPlanCoach"]}', @idp_workflow_id, 'Reopen', @succession_and_development_module_id, 0);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
  (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @idp_workflow_id AND `action` = 'Reopen';
SET @last_subscription_id := LAST_INSERT_ID();

# Template for Employee
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Individual Development Plan Reopening Notification Sent To Employee',
   'Individual Development Plan Reopened',
   '<p>Dear %subscriberName%,</p>
<p>Your Individual Development Plan (IDP): %idpName% has been reopened.</p>
<p>You can access the IDP from <a href="%url%">here</a>.</p>
<p>Thank you.<br />(This is a system-generated email, please do not reply)</p>');

SET @template_id := LAST_INSERT_ID();

-- Subscriber for Employee
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Affected Employee');

SET @subscriber_id := LAST_INSERT_ID();

-- Subscription for Employee
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id)
  SELECT
    s.id,
    @subscriber_id,
    @template_id
  FROM ohrm_notification_subscription s
  WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('development_goals', 'Career Development - Goals', 1, 1, 1, 1);

SET @development_goals_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_user_role_id, @development_goals_data_group_id, 1, 1, 1, 1, 0),
(@ess_user_role_id, @development_goals_data_group_id, 1, 0, 1, 1, 1),
(@supervisor_user_role_id, @development_goals_data_group_id, 1, 1, 1, 1, 0),
(@hod_user_role_id, @development_goals_data_group_id, 1, 1, 1, 1, 0),
(@idp_coach_user_role_id, @development_goals_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
(@admin_user_role_type_id, @development_goals_data_group_id, 1, 1, 1, 1),
(@supervisor_user_role_type_id, @development_goals_data_group_id, 1, 1, 1, 1),
(@ess_user_role_type_id, @development_goals_data_group_id, 1, 0, 1, 1);

SET @goal_list_screen_id := (SELECT s.`id` FROM `ohrm_screen` s
LEFT JOIN `ohrm_module` m ON s.`module_id` = m.`id`
WHERE m.`name`='performance'
AND s.`name`='Goal List');

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

# Giving Main Evaluator and Supervisor Evaluators permissions for IDP and Dev. Goal data group permissions.
SET @main_evaluator_user_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'MainEvaluator');
SET @supervisor_reviewer_user_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'AppraisalSupervisorReviewer');
SET @individual_development_plan_data_group_id := (SELECT `id` FROM ohrm_data_group WHERE `name` = 'individual_development_plan');
SET @development_goal_data_group_id := (SELECT `id` FROM ohrm_data_group WHERE `name` = 'development_goals');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
  (@main_evaluator_user_role_id, @individual_development_plan_data_group_id, 1, 1, 1, 0, 0),
  (@main_evaluator_user_role_id, @development_goal_data_group_id, 1, 1, 1, 1, 0),
  (@supervisor_reviewer_user_role_id, @individual_development_plan_data_group_id, 1, 1, 1, 0, 0),
  (@supervisor_reviewer_user_role_id, @development_goal_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO ohrm_data_list (`name`, `meta_data`) VALUES ('idpList', '{"listFields":{"owner":"owner","name":"name","coach":"coach","closedDate":"closedDate","status":"status"},"availableFields":{"ownerEmpId":"Employee ID","owner":"Employee","name":"IDP Name","coach":"Coach","closedDate":"Closed On","initiatedDate":"Initiated On","status":"Status"},"defaultFields":["owner","name","coach","closedDate","status"],"mandatoryFields":["owner","name","coach","closedDate","status"]}');