/*
 * OrangeHRM Enterprise is a closed sourced comprehensive Human Resource Management (HRM)
 * System that captures all the essential functionalities required for any enterprise.
 * Copyright (C) 2006 OrangeHRM Inc., http://www.orangehrm.com
 *
 * OrangeHRM Inc is the owner of the patent, copyright, trade secrets, trademarks and any
 * other intellectual property rights which subsist in the Licensed Materials. OrangeHRM Inc
 * is the owner of the media / downloaded OrangeHRM Enterprise software files on which the
 * Licensed Materials are received. Title to the Licensed Materials and media shall remain
 * vested in OrangeHRM Inc. For the avoidance of doubt title and all intellectual property
 * rights to any design, new software, new protocol, new interface, enhancement, update,
 * derivative works, revised screen text or any other items that OrangeHRM Inc creates for
 * Customer shall remain vested in OrangeHRM Inc. Any rights not expressly granted herein are
 * reserved to OrangeHRM Inc.
 *
 * Please refer http://www.orangehrm.com/Files/OrangeHRM_Commercial_License.pdf for the license which includes terms and conditions on using this software.
 *
 */

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

SET @copy_preboarding_task_options :=
        (SELECT JSON_OBJECT(
                        'executorHelper', 'CopyFormRecordToWizardSessionDataExecutionHelper'
                    ));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `workflow`, `form_version_id`, `options`)
VALUES ('Copy Onboarding Forms to Employee', 'Copy Preboarding Forms to Employee', @recruitment_module_id,
        '', NULL, NULL, @copy_preboarding_task_options);

SET @copy_preboarding_forms_task_template_id := (SELECT id FROM ohrm_advanced_task_template WHERE name = 'Copy Onboarding Forms to Employee' LIMIT 1);

SET @bank_details_screen_id := (SELECT `id` FROM `ohrm_custom_field_screen` WHERE name = 'Bank_Details_240');

SET @bank_details_custom_field_section_id := (SELECT `id` FROM `ohrm_custom_field_section` WHERE `display_name` = 'Bank Details' AND `custom_field_screen_id` = @bank_details_screen_id);

SET @bank_account_holder := (SELECT id FROM ohrm_custom_field WHERE name = 'Bank Account Holder' AND custom_field_section_id = @bank_details_custom_field_section_id AND deleted = 0);
SET @bank_sort_code := (SELECT id FROM ohrm_custom_field WHERE name = 'Bank Sort Code' AND custom_field_section_id = @bank_details_custom_field_section_id AND deleted = 0);
SET @bank_account_number := (SELECT id FROM ohrm_custom_field WHERE name = 'Bank Account Number' AND custom_field_section_id = @bank_details_custom_field_section_id AND deleted = 0);
SET @bank_name := (SELECT id FROM ohrm_custom_field WHERE name = 'Bank Name' AND custom_field_section_id = @bank_details_custom_field_section_id AND deleted = 0);
SET @bank_branch := (SELECT id FROM ohrm_custom_field WHERE name = 'Bank Branch' AND custom_field_section_id = @bank_details_custom_field_section_id AND deleted = 0);

SET @custom_tab_screen_id := (SELECT id  FROM `ohrm_screen` WHERE `name` = 'Employee Pim Tabs');
SET @tab_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Bank Details'  AND screen_id = @custom_tab_screen_id AND status = 1);
SET @metadata := CONCAT('{
  "formName": "Bank Details",
  "wizardDataTemplate": {
    "data": {
      "customTabRowValues_',@tab_id,'": {
          "', @bank_account_holder, '": "${bank_account_holder}",
          "', @bank_sort_code,'": "${bank_sort_code}",
          "', @bank_account_number,'": "${bank_account_number}",
          "', @bank_name,'": "${bank_name}",
          "', @bank_branch,'": "${bank_branch}"
      }
    }
  }
}');

INSERT INTO `ohrm_advanced_task` (task_template_id, name, description, metadata)
VALUES (@copy_preboarding_forms_task_template_id, 'pre_add_candidate_as_employee',
        'task run at when candidate added as employee to copy preboarding form information to employee', @metadata);
