SET @preboarding_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'preboarding');
SET @pro_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'onboardingPro');

-- Start: Workflow entity process configuration
SET @max_wf_order := (SELECT MAX(`order_no`) FROM `ohrm_workflow`);
INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `group_name`, `group_label`, `available_to`, `module_id`, `order_no`)
VALUES ('PREBOARDING_REFERENCES_PROCESS', 'PREBOARDING_REFERENCES_PROCESS', null,
        'preboarding', 'Preboarding', '1,3', @preboarding_module_id, @max_wf_order + 1);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `priority`)
VALUES ('PREBOARDING_REFERENCES_PROCESS', 'AWM_Initial', 'SYSTEM', 'AWM_Initialize', 'Pending', '0'),
       ('PREBOARDING_REFERENCES_PROCESS', 'Pending', 'SYSTEM', 'Complete', 'Completed', '0');

INSERT INTO `ohrm_advanced_workflow_entity_process` (`entity`, `workflow_id`, `name`, `description`, `sequence`, `options`)
VALUES ('Preboarding Employee', 'PREBOARDING_REFERENCES_PROCESS', 'References', 'Submit Professional/Academic References', 4,
        '{"icon":"oxd-referees"}');

SET @entity_process_id := (SELECT `id` FROM `ohrm_advanced_workflow_entity_process` WHERE `name` = 'References');
SET @max_entity_process_status_id := (SELECT IFNULL((SELECT IFNULL(MAX(`status_id`), 0) FROM `ohrm_advanced_workflow_entity_process_status`), 0));

INSERT INTO `ohrm_advanced_workflow_entity_process_status`
(`entity_process_id`, `status_id`, `name`, `description`, `sequence`)
VALUES (@entity_process_id, @max_entity_process_status_id + 1, 'AWM_Initial',
        'Process to be initialized by the System', '0'),
       (@entity_process_id, @max_entity_process_status_id + 2, 'Pending',
        'References are pending to be completed', '1'),
       (@entity_process_id, @max_entity_process_status_id + 3, 'Completed',
        'References are completed', '2');

-- End: Workflow entity process configuration

-- Start: "Preboarding Reference Details instructions" Task configuration

INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `group_name`, `group_label`, `available_to`, `default_available_to`, `module_id`, `order_no`)
VALUES ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', null,
        'preboarding', 'Preboarding', '1,3', '2', @preboarding_module_id, @max_wf_order + 2);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `priority`)
VALUES ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'PreboardingNewHire', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'ESS User', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'Admin', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'HiringManager', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'Supervisor', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'HeadOfDepartment', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', 'Pending', 'PreboardingEventManager', 'Read', 'Pending', '0');

SET @form_version_id := (SELECT `id` FROM `ohrm_dynamic_form_version` WHERE `name` = 'Reference Details - Instructions');

SET @task_options :=
        (SELECT JSON_OBJECT(
                        'entityProcessId', @entity_process_id,
                        'ownerUserRoles', JSON_OBJECT('Admin', JSON_OBJECT()),
                        'sharedTask', true,
                        'sharedFormData', true,
                        'generatorHelper', 'PreboardingTaskGeneratorHelper',
                        'initialStatus', 'Pending',
                        'terminalStatus', 'Pending'
                ));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `workflow`, `form_version_id`, `options`)
VALUES ('Reference Details - Instructions', 'Reference Details - Instructions for %preboardingEmployeeName%', @pro_module_id,
        '', 'PREBOARDING_REFERENCE_INSTRUCTIONS_TASK', @form_version_id, @task_options);

SET @task_template_id := (SELECT `id`
                          FROM ohrm_advanced_task_template
                          WHERE `name` = 'Reference Details - Instructions');

-- Create the Reference Details - Instructions task upon completing the Initiate Preboarding

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`,
       'WorkflowStateChangeTaskCreator',
       JSON_OBJECT(
               'task', JSON_OBJECT('templateId', @task_template_id)
       )

FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_REFERENCES_PROCESS'
  AND `state` = 'AWM_Initial'
  AND `action` = 'AWM_Initialize'
  AND `resulting_state` = 'Pending';
-- End: "Preboarding Reference Details instructions" Task configuration

-- Start: References Task configuration
INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `group_name`, `group_label`, `available_to`, `default_available_to`, `module_id`, `order_no`)
VALUES ('PREBOARDING_REFERENCES_TASK', 'PREBOARDING_REFERENCES_TASK', null,
        'preboarding', 'Preboarding', '1,3', '2', @preboarding_module_id, @max_wf_order + 2);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `priority`)
VALUES ('PREBOARDING_REFERENCES_TASK', 'Pending', 'PreboardingNewHire', 'Save','Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'PreboardingNewHire', 'Submit','Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'ESS User', 'Save','Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'ESS User', 'Submit','Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'Admin', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'HiringManager', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'Supervisor', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'HeadOfDepartment', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending', 'PreboardingEventManager', 'Read', 'Pending', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'PreboardingNewHire', 'Read','Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'ESS User', 'Read','Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'Admin', 'Read', 'Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'Admin', 'Approve', 'Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'HiringManager', 'Read', 'Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'HiringManager', 'Approve', 'Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'Supervisor', 'Read', 'Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'Supervisor', 'Approve', 'Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'HeadOfDepartment', 'Read', 'Pending Approval', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'HeadOfDepartment', 'Approve', 'Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'PreboardingEventManager', 'Approve', 'Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'Admin', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'HiringManager', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'Supervisor', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'HeadOfDepartment', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Approval', 'PreboardingEventManager', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'PreboardingNewHire', 'Read','Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'ESS User', 'Read','Pending Initiation', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'Admin', 'Initiate', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'HiringManager', 'Initiate', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'Supervisor', 'Initiate', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'HeadOfDepartment', 'Initiate', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Pending Initiation', 'PreboardingEventManager', 'Initiate', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'PreboardingNewHire', 'Read','Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'ESS User', 'Read','Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'Admin', 'Read', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'Admin', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'HiringManager', 'Read', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'HiringManager', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'Supervisor', 'Read', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'Supervisor', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'HeadOfDepartment', 'Read', 'Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'HeadOfDepartment', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'PreboardingEventManager', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'Admin', 'Upload','Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'HiringManager', 'Upload','Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'Supervisor', 'Upload','Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'HeadOfDepartment', 'Upload','Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'PreboardingEventManager', 'Upload','Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'PreboardingReferee', 'Save','Initiated', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Initiated', 'PreboardingReferee', 'Submit','Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'PreboardingNewHire', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'ESS User', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'PreboardingReferee', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'Admin', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'Admin', 'Approve', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'HiringManager', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'HiringManager', 'Approve', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'Supervisor', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'Supervisor', 'Approve', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'HeadOfDepartment', 'Read', 'Submitted', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'HeadOfDepartment', 'Approve', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'PreboardingEventManager', 'Approve', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'Admin', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'HiringManager', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'Supervisor', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'HeadOfDepartment', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Submitted', 'PreboardingEventManager', 'Reject', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'PreboardingNewHire', 'Read','Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'ESS User', 'Read','Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'PreboardingReferee', 'Read','Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'Admin', 'Read', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'HiringManager', 'Read', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'Supervisor', 'Read', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'HeadOfDepartment', 'Read', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Approved', 'PreboardingEventManager', 'Read', 'Approved', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'PreboardingNewHire', 'Read','Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'ESS User', 'Read','Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'PreboardingReferee', 'Read','Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'Admin', 'Read', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'HiringManager', 'Read', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'Supervisor', 'Read', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'HeadOfDepartment', 'Read', 'Rejected', '0'),
       ('PREBOARDING_REFERENCES_TASK', 'Rejected', 'PreboardingEventManager', 'Read', 'Rejected', '0');

SET @references_form_version_id := (SELECT `id` FROM `ohrm_dynamic_form_version` WHERE `name` = 'Preboarding References');

SET @references_task_options :=
        (SELECT JSON_OBJECT(
                'entityProcessId', @entity_process_id,
                'ownerUserRoles', JSON_OBJECT('Admin', JSON_OBJECT()),
                'sharedTask', true,
                'sharedFormData', true,
                'generatorHelper', 'FillInFormRecordsTaskGeneratorHelper',
                'extraData', JSON_OBJECT(
                    'formFillMapping', JSON_OBJECT(
                    'fieldMap', JSON_OBJECT(
                        'new_hire_name', JSON_OBJECT('fieldValueGetter','getFullName'),
                        'position_applied_for', JSON_OBJECT('fieldValueGetter','getJobTitleName')
                        )
                    )
                ),
                'initialStatus', 'Pending',
                'terminalStatus', 'Completed'
                ));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `workflow`, `form_version_id`, `options`)
VALUES ('Reference Details Submission', 'Fill-in Reference Details for %preboardingEmployeeName%', @pro_module_id,
        '', 'PREBOARDING_REFERENCES_TASK', @references_form_version_id,
        @references_task_options);

SET @references_task_template_id := (SELECT `id`
                          FROM ohrm_advanced_task_template
                          WHERE `name` = 'Reference Details Submission');

-- Create the default 2 Reference Details Submission task upon completing the the Initiate Preboarding

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`,
       'WorkflowStateChangeTaskCreator',
       JSON_OBJECT(
               'task', JSON_OBJECT('templateId', @references_task_template_id),
               'count', 2
       )
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_REFERENCES_PROCESS'
  AND `state` = 'AWM_Initial'
  AND `role` = 'SYSTEM'
  AND `action` = 'AWM_Initialize'
  AND `resulting_state` = 'Pending';

-- Create a new Reference Details Submission task upon rejection of an existing referee entry

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`,
       'WorkflowStateChangeTaskCreator',
       JSON_OBJECT(
               'task', JSON_OBJECT('templateId', @references_task_template_id)
       )
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_REFERENCES_TASK'
  AND `action` = 'Reject';

-- Start: "Create Referee Login" Task configuration
INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `module_id`, `order_no`)
VALUES ('PREBOARDING_CREATE_REFEREE_LOGIN_TASK', 'PREBOARDING_CREATE_REFEREE_LOGIN_TASK',
        'Onboarding - Create Referee Login Task', @preboarding_module_id, @max_wf_order + 4);

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `priority`)
VALUES ('PREBOARDING_CREATE_REFEREE_LOGIN_TASK', 'Pending', 'SYSTEM', 'Create Referee Login', 'Completed', 0);

SET @task_options :=
        (SELECT JSON_OBJECT(
                'entityProcessId', @entity_process_id,
                'ownerUserRoles', JSON_OBJECT('SYSTEM', JSON_OBJECT()),
                'generatorHelper', 'CreateRefereeLoginTaskGeneratorHelper',
                'executorHelper', 'CreateRefereeLoginTaskExecutorHelper',
                'initialStatus', 'Pending',
                'terminalStatus', 'Completed',
                'autoExecute', true
                ));

INSERT INTO `ohrm_advanced_task_template`
(`name`, `description`, `module_id`, `mediator`, `workflow`, `options`)
VALUES ('Create Referee Login', 'Create Referee Login for candidate %preboardingEmployeeName%', @pro_module_id,
        '', 'PREBOARDING_CREATE_REFEREE_LOGIN_TASK',
        @task_options);

SET @create_referee_login_task_template_id := (SELECT `id`
                                               FROM ohrm_advanced_task_template
                                               WHERE `name` = 'Create Referee Login');

-- Create the Create Referee Login task upon approval of a reference
INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`,
       'WorkflowStateChangeTaskCreator',
       JSON_OBJECT('task', JSON_OBJECT('templateId', @create_referee_login_task_template_id))
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_REFERENCES_TASK'
  AND `state` = 'Pending Initiation'
  AND `action` = 'Initiate'
  AND `resulting_state` = 'Initiated';
-- End: "Create Referee Login" Task configuration

-- Move the employee personal info to "Completed" stage upon submitting the Reference Details

INSERT INTO `ohrm_workflow_state_change_resolver` (`state_machine_id`, `resolver`, `options`)
SELECT `id`,
       'TaskStateChangeWorkflowProcessStateChangerForProcessesWithMultipleTasks',
       JSON_OBJECT(
               'entityProcessId', @entity_process_id,
               'action', 'Complete',
               'requiredState', 'Approved',
               'requiredTaskCount', '2'
       )
FROM `ohrm_workflow_state_machine`
WHERE `workflow` = 'PREBOARDING_REFERENCES_TASK'
  AND `state` = 'Submitted'
  AND `action` = 'Approve'
  AND `resulting_state` = 'Approved';

-- End: Employee personal info configuration

-- Workflow State Data field permissions for Global Admin, HiringManager and PreboardingEventManager
SET @preboarding_references_data_group_id := (SELECT `id`
                                   FROM ohrm_data_group
                                   WHERE `name` = 'preboarding_references');

SET @referee_category := (SELECT `id`
                          FROM ohrm_data_field
                          WHERE `name` = 'referee_category'
                            AND data_group_id = @preboarding_references_data_group_id);

SET @referee_docs := (SELECT `id`
                          FROM ohrm_data_field
                          WHERE `name` = 'referee_docs'
                            AND data_group_id = @preboarding_references_data_group_id);

-- Referee Category field should be editable only when the status is Pending Initiation
INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Pending', user_role_id, @referee_category, 0, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Pending Approval', user_role_id, @referee_category, 0, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Pending Initiation', user_role_id, @referee_category, 1, 1
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Initiated', user_role_id, @referee_category, 1, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Submitted', user_role_id, @referee_category, 1, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Approved', user_role_id, @referee_category, 1, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Rejected', user_role_id, @referee_category, 0, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Pending', user_role_id, @referee_docs, 0, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Pending Approval', user_role_id, @referee_docs, 0, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Pending Initiation', user_role_id, @referee_docs, 0, 1
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Initiated', user_role_id, @referee_docs, 1, 1
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Submitted', user_role_id, @referee_docs, 1, 1
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Approved', user_role_id, @referee_docs, 1, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

INSERT INTO `ohrm_workflow_state_data_field_permission`
(`workflow_id`, `workflow_state`, `user_role_id`, `data_field_id`, `can_read`, `can_update`)
SELECT 'PREBOARDING_REFERENCES_TASK', 'Rejected', user_role_id, @referee_docs, 0, 0
FROM `ohrm_user_role_data_group` where data_group_id IN (SELECT id FROM ohrm_data_group WHERE name IN ('preboarding_references'))
                                   AND can_read = 1 AND user_role_id NOT IN (SELECT id FROM ohrm_user_role WHERE name IN ('PreboardingNewHire', 'ESS'));

SET @master_state_change_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'PREBOARDING_REFERENCES_PROCESS' AND `role` = 'SYSTEM' AND `action` = 'Complete');
SET @slave_state_change_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'PREBOARDING_EMPLOYEE_ENTITY' AND `role` = 'SYSTEM' AND `action` = 'Complete');
INSERT INTO `ohrm_workflow_state_machine_dependancy`(`master_state_change_id`, `slave_state_change_id`, `trigger_verification`, `options`)
VALUES (@master_state_change_id, @slave_state_change_id, 'AdvancedWorkflowProcessToRequestStateChangeTrigger',
        '{"validateSameStateForAllProcesses":true,"considerOnlyEnabledProcesses":true}');
