SET @template_id := (SELECT id
                     FROM ohrm_advanced_task_template
                     WHERE name = 'Push form data to a configured API endpoint'
                     LIMIT 1);

SET @entity_process_id := (SELECT `id`
                           FROM `ohrm_advanced_workflow_entity_process`
                           WHERE `name` = 'Qualifications'
                             and workflow_id = 'PREBOARDING_QUALIFICATIONS_PROCESS');

SET @work_experience_task_template_id := (SELECT `id`
    FROM ohrm_advanced_task_template WHERE
    `name` = 'Work Experience' AND workflow = 'PREBOARDING_WORK_EXPERIENCE_TASK');
SET @education_task_template_id := (SELECT `id`
    FROM ohrm_advanced_task_template WHERE
    `name` = 'Educations' AND workflow = 'PREBOARDING_EDUCATION_TASK');
SET @skills_task_template_id := (SELECT `id`
    FROM ohrm_advanced_task_template WHERE
    `name` = 'Skills' AND workflow = 'PREBOARDING_SKILL_TASK');
SET @language_task_template_id := (SELECT `id`
    FROM ohrm_advanced_task_template WHERE
    `name` = 'Languages' AND workflow = 'PREBOARDING_LANGUAGE_TASK');
SET @license_task_template_id := (SELECT `id`
    FROM ohrm_advanced_task_template WHERE
    `name` = 'License' AND workflow = 'PREBOARDING_LICENSE_TASK');


SET @work_experience_form_version_id := (SELECT `id`
                                 FROM ohrm_dynamic_form_version
                                 WHERE name = 'Preboarding Work Experience');
SET @education_form_version_id := (SELECT `id`
                                 FROM ohrm_dynamic_form_version
                                 WHERE name = 'Preboarding Education');
SET @skills_form_version_id := (SELECT `id`
                                 FROM ohrm_dynamic_form_version
                                 WHERE name = 'Preboarding Skills');
SET @language_form_version_id := (SELECT `id`
                                 FROM ohrm_dynamic_form_version
                                 WHERE name = 'Preboarding Language');
SET @license_form_version_id := (SELECT `id`
                                 FROM ohrm_dynamic_form_version
                                 WHERE name = 'Preboarding License');

INSERT INTO `ohrm_advanced_task` (task_template_id, name, description, metadata)
VALUES (@template_id, 'Copy Work Experience form data to Employee',
        'Copy Work Experience form data to Employee upon form approval', '{}');
INSERT INTO `ohrm_advanced_task` (task_template_id, name, description, metadata)
VALUES (@template_id, 'Copy Education form data to Employee',
        'Copy Education form data to Employee upon form approval', '{}');
INSERT INTO `ohrm_advanced_task` (task_template_id, name, description, metadata)
VALUES (@template_id, 'Copy Skills form data to Employee',
        'Copy Skills form data to Employee upon form approval', '{}');
INSERT INTO `ohrm_advanced_task` (task_template_id, name, description, metadata)
VALUES (@template_id, 'Copy Language form data to Employee',
        'Copy Language form data to Employee upon form approval', '{}');
INSERT INTO `ohrm_advanced_task` (task_template_id, name, description, metadata)
VALUES (@template_id, 'Copy License form data to Employee',
        'Copy License form data to Employee upon form approval', '{}');


SET @work_experience_metadata := CONCAT('{
  "entityProcessId": ', @entity_process_id, ',
  "taskTemplateId": ', @work_experience_task_template_id, ',
  "formVersionId": ', @work_experience_form_version_id, ',
  "dataTransfer": [
    {
      "processorClass": "PreboardingToPimMultiTabDataTransferProcessor",
      "formDataSortColumn": "name",
      "removeFieldsIfEmptyForMethod": "post",
      "get": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeWorkExperiencesEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${empNumber}"
          }
        },
        "aggregate_column": "work_experience"
      },
      "delete": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeWorkExperiencesEndpoint",
        "endpointVersion": 1,
        "uniqueRecordIdentifierInGet": "seqno",
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          }
        }
      },
      "post": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeWorkExperiencesEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          },
          "body": {
             "employer": "${company}",
             "jobtitle": "${job_title}",
             "from_date": "${from_date}",
             "to_date": "${to_date}",
             "comments": "${comment}"
          }
        }
      }
    }
  ]
}');


SET @education_metadata := CONCAT('{
  "entityProcessId": ', @entity_process_id, ',
  "taskTemplateId": ', @education_task_template_id, ',
  "formVersionId": ', @education_form_version_id, ',
  "dataTransfer": [
    {
      "processorClass": "PreboardingToPimMultiTabDataTransferProcessor",
      "formDataSortColumn": "name",
      "removeFieldsIfEmptyForMethod": "post",
      "get": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeEducationsEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${empNumber}"
          }
        },
        "aggregate_column": "education"
      },
      "delete": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeEducationsEndpoint",
        "endpointVersion": 1,
        "uniqueRecordIdentifierInGet": "id",
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          }
        }
      },
      "post": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeEducationsEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          },
          "body": {
             "educationId": "${level#id}",
             "institute": "${institute}",
             "major": "${major_specialization}",
             "year": "${year}",
             "score": "${gpa_score}",
             "startDate": "${start_date}",
             "endDate": "${end_date}"
          }
        }
      }
    }
  ]
}');

SET @skills_metadata := CONCAT('{
  "entityProcessId": ', @entity_process_id, ',
  "taskTemplateId": ', @skills_task_template_id, ',
  "formVersionId": ', @skills_form_version_id, ',
  "dataTransfer": [
    {
      "processorClass": "PreboardingToPimMultiTabDataTransferProcessor",
      "formDataSortColumn": "name",
      "removeFieldsIfEmptyForMethod": "post",
      "get": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeSkillsEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${empNumber}"
          }
        },
        "aggregate_column": "skill"
      },
      "delete": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeSkillsEndpoint",
        "endpointVersion": 1,
        "uniqueRecordIdentifierInGet": "id",
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          }
        }
      },
      "post": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeSkillsEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          },
          "body": {
             "skillId": "${skill#id}",
             "years_of_exp": "${years_of_experience}",
             "comments": "${comment}"
          }
        }
      }
    }
  ]
}');

SET @language_metadata := CONCAT('{
  "entityProcessId": ', @entity_process_id, ',
  "taskTemplateId": ', @language_task_template_id, ',
  "formVersionId": ', @language_form_version_id, ',
  "dataTransfer": [
    {
      "processorClass": "PreboardingToPimMultiTabDataTransferProcessor",
      "formDataSortColumn": "name",
      "removeFieldsIfEmptyForMethod": "post",
      "get": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeLanguagesEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${empNumber}"
          }
        },
        "aggregate_column": "language"
      },
      "delete": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeLanguagesEndpoint",
        "endpointVersion": 1,
        "uniqueRecordIdentifierInGet": "langId,fluency",
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          }
        }
      },
      "post": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeLanguagesEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          },
          "body": {
             "langId": "${language#id}",
             "competency": "${skill#id}",
             "fluency": "${fluency_level#id}",
             "comments": "${comment}"
          }
        }
      }
    }
  ]
}');

SET @license_metadata := CONCAT('{
  "entityProcessId": ', @entity_process_id, ',
  "taskTemplateId": ', @license_task_template_id, ',
  "formVersionId": ', @license_form_version_id, ',
  "dataTransfer": [
    {
      "processorClass": "PreboardingToPimMultiTabDataTransferProcessor",
      "formDataSortColumn": "name",
      "removeFieldsIfEmptyForMethod": "post",
      "get": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeLicensesEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${empNumber}"
          }
        },
        "aggregate_column": "license"
      },
      "delete": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeLicensesEndpoint",
        "endpointVersion": 1,
        "uniqueRecordIdentifierInGet": "licenseId",
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          }
        }
      },
      "post": {
        "endpointClass": "OrangeHRM\\\\Api\\\\Pim\\\\EmployeeLicensesEndpoint",
        "endpointVersion": 1,
        "params": {
          "request": {
            "emp_number": "${emp_number}"
          },
          "body": {
             "licenseId": "${license_type#id}",
             "licenseNo": "${license_number}",
             "licenseIssuedDate": "${issued_date}",
             "licenseExpiryDate": "${expiry_date}"
          }
        }
      }
    }
  ]
}');

SET @work_experience_task_id := (SELECT `id`
                 FROM `ohrm_advanced_task`
                 WHERE `name` = 'Copy Work Experience form data to Employee'
                   AND `task_template_id` = @template_id);

SET @education_task_id := (SELECT `id`
                 FROM `ohrm_advanced_task`
                 WHERE `name` = 'Copy Education form data to Employee'
                   AND `task_template_id` = @template_id);

SET @skill_task_id := (SELECT `id`
                 FROM `ohrm_advanced_task`
                 WHERE `name` = 'Copy Skills form data to Employee'
                   AND `task_template_id` = @template_id);

SET @language_task_id := (SELECT `id`
                 FROM `ohrm_advanced_task`
                 WHERE `name` = 'Copy Language form data to Employee'
                   AND `task_template_id` = @template_id);

SET @license_task_id := (SELECT `id`
                 FROM `ohrm_advanced_task`
                 WHERE `name` = 'Copy License form data to Employee'
                   AND `task_template_id` = @template_id);

UPDATE `ohrm_advanced_task`
SET `metadata` = @work_experience_metadata
WHERE `id` = @work_experience_task_id;

UPDATE `ohrm_advanced_task`
SET `metadata` = @education_metadata
WHERE `id` = @education_task_id;

UPDATE `ohrm_advanced_task`
SET `metadata` = @skills_metadata
WHERE `id` = @skill_task_id;

UPDATE `ohrm_advanced_task`
SET `metadata` = @language_metadata
WHERE `id` = @language_task_id;

UPDATE `ohrm_advanced_task`
SET `metadata` = @license_metadata
WHERE `id` = @license_task_id;

-- Copy relevant information from "Work Experience" sub form to the employee profile upon approving the form

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`, 'TaskStateChangeTaskExecutorResolver', JSON_OBJECT('taskId', @work_experience_task_id)
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_WORK_EXPERIENCE_TASK'
  AND `state` = 'Submitted'
  AND `action` = 'Approve'
  AND `resulting_state` = 'Approved';

-- Copy relevant information from "Education" sub form to the employee profile upon approving the form

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`, 'TaskStateChangeTaskExecutorResolver', JSON_OBJECT('taskId', @education_task_id)
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_EDUCATION_TASK'
  AND `state` = 'Submitted'
  AND `action` = 'Approve'
  AND `resulting_state` = 'Approved';

-- Copy relevant information from "Skill" sub form to the employee profile upon approving the form

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`, 'TaskStateChangeTaskExecutorResolver', JSON_OBJECT('taskId', @skill_task_id)
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_SKILL_TASK'
  AND `state` = 'Submitted'
  AND `action` = 'Approve'
  AND `resulting_state` = 'Approved';

-- Copy relevant information from "Language" sub form to the employee profile upon approving the form

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`, 'TaskStateChangeTaskExecutorResolver', JSON_OBJECT('taskId', @language_task_id)
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_LANGUAGE_TASK'
  AND `state` = 'Submitted'
  AND `action` = 'Approve'
  AND `resulting_state` = 'Approved';

-- Copy relevant information from "License" sub form to the employee profile upon approving the form

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`, 'TaskStateChangeTaskExecutorResolver', JSON_OBJECT('taskId', @license_task_id)
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_LICENSE_TASK'
  AND `state` = 'Submitted'
  AND `action` = 'Approve'
  AND `resulting_state` = 'Approved';

