CREATE TABLE IF NOT EXISTS ohrm_requestdesk_custom_field_section_permission (
    id INT NOT NULL auto_increment,
    section_id INT NOT NULL,
    user_role_id int(10) NOT NULL,
    can_read TINYINT,
    can_update TINYINT,
    PRIMARY KEY(id),
    CONSTRAINT `fk_custom_field_section_permission_custom_field_section` FOREIGN KEY (section_id) REFERENCES ohrm_requestdesk_custom_field_section(`id`) ON DELETE cascade,
    CONSTRAINT `fk_custom_field_section_permission_user_role` FOREIGN KEY (user_role_id) REFERENCES  ohrm_user_role(`id`) ON DELETE cascade
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

INSERT INTO `ohrm_workflow_state_machine`(`workflow`, `state`, `role`, `action`, `resulting_state`) VALUES
('REQUEST_PROCESS_ACTOR_APPROVER','INITIATED','RequestApprover','SAVE','In Progress'),
('REQUEST','Submitted','RequestApprover','SAVE','In Progress'),
('REQUEST','In Progress','RequestApprover','SAVE','In Progress'),
('REQUEST','Request Info Required','RequestApprover','SAVE','In Progress');

-- Affected Request Employee User Role Changes --
INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`, `is_editable`, `deletable`)
VALUES ('AffectedRequestEmployee', 'Request Assignee', 0, 1, 0, 0);

-- Screen Permissions --
SET @main_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewRequestTrackerModule');
SET @view_request_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewRequest');
SET @my_general_requests_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewMyGeneralRequests');
SET @affected_request_employee_role_id := (SELECT id FROM ohrm_user_role where name = 'AffectedRequestEmployee');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@affected_request_employee_role_id, @main_screen_id, 1, 0, 0, 0),
(@affected_request_employee_role_id, @view_request_screen_id, 1, 1, 1, 1),
(@affected_request_employee_role_id, @my_general_requests_screen_id, 1, 1, 1, 1);

-- Workflow Permissions --
INSERT INTO `ohrm_workflow_state_machine`(`workflow`, `state`, `role`, `action`, `resulting_state`) VALUES
('REQUEST_PROCESS_ACTOR_APPROVER','INITIATED','AffectedRequestEmployee','SAVE','In Progress'),
('REQUEST_WITHOUT_APPROVE','In Progress','AffectedRequestEmployee','SAVE','In Progress'),
('REQUEST','Submitted','AffectedRequestEmployee','SAVE','In Progress'),
('REQUEST','In Progress','AffectedRequestEmployee','SAVE','In Progress'),
('REQUEST','Request Info Required','AffectedRequestEmployee','SAVE','In Progress');

-- Data group permissions --
SET @my_requests_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'my_requests');
SET @request_attachement_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'request_attachment');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@affected_request_employee_role_id, @my_requests_data_group_id, 1, 1, 1, 0, 0),
(@affected_request_employee_role_id, @request_attachement_data_group_id, 1, 1, 0, 0, 0);


SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');
SET @requester_id := (SELECT id FROM ohrm_user_role where name = 'Requester');
SET @request_owner_id := (SELECT id FROM ohrm_user_role where name = 'RequestOwner');
SET @request_approver_id := (SELECT id FROM ohrm_user_role where name = 'RequestApprover');

SET @resignation_request_id := (SELECT id FROM ohrm_requestdesk_request_types WHERE name = 'Resignation Request');
SET @resignation_request_custom_field_section_id := (SELECT id FROM ohrm_requestdesk_custom_field_section WHERE request_type_id = @resignation_request_id);

INSERT INTO `ohrm_requestdesk_custom_field_section_permission` (`section_id`, `user_role_id`, `can_read`,`can_update`) VALUES
(@resignation_request_custom_field_section_id, @admin_id, 1, 1),
(@resignation_request_custom_field_section_id, @requester_id, 1, 1),
(@resignation_request_custom_field_section_id, @request_owner_id, 1, 1),
(@resignation_request_custom_field_section_id, @request_approver_id, 1, 0);

INSERT INTO `ohrm_vas_custom_advance_type_map` (`entity`, `display_name`, `id_field`, `name_field`, `type`) VALUES ('Generic', 'Generic', 'id', 'name', 'select');

UPDATE `ohrm_requestdesk_request_type_template`
 SET `template`='{
                    "meta":{
                       "terminalHandler":"",
                       "TerminalActionDisplayName":"Update Information",
                       "maximumApprovalLevels":5,
                       "action":"webServiceCall",
                       "webServiceUrl":"employees",
                       "webServiceParam":"empNumber",
                       "httpMethod":"PATCH",
                       "redirectAfterSuccess":"pim.employees.profile.personal_details"
                    },
                    "formData":{
                       "sections":[
                          {
                             "name":"Extra Custom Fields",
                             "displayOrder":1,
                             "isCustomizable":true,
                             "defaultFields":[

                             ]
                          },
                          {
                             "name":"Personal Details",
                             "displayOrder":2,
                             "isCustomizable":true,
                             "isTemplate":true,
                             "defaultFields":[
                                {
                                   "fieldType":"Text",
                                   "name":"First Name",
                                   "otherData":{
                                      "length":30
                                   },
                                   "extraData":"",
                                   "mandatory":true,
                                   "deletable":false,
                                   "editable":false,
                                   "tokenName":"firstName",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getFirstName"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Middle Name",
                                   "otherData":{
                                      "length":30
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"middleName",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getMiddleName"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Last Name",
                                   "otherData":{
                                      "length":30
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":false,
                                   "editable":false,
                                   "tokenName":"lastName",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getLastName"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Other Id",
                                   "otherData":{
                                      "length":50
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"otherId",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getOtherId"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Main Id",
                                   "otherData":{
                                      "length":30
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"ssn",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getSsn"
                                },
                                {
                                   "fieldType":"Date",
                                   "name":"Date of Birth",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_birthday",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpBirthday"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Driving License Number",
                                   "otherData":{
                                      "length":30
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"licenseNo",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getLicenseNo"
                                },
                                {
                                   "fieldType":"Date",
                                   "name":"License Expiry Date",
                                   "otherData":{
                                      "length":10
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_dri_lice_exp_date",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpDriLiceExpDate"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Nationality",
                                   "otherData":{
                                      "entity":"Nationality",
                                      "idField":"id",
                                      "nameField":"name"
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Generic",
                                   "tokenName":"nation_code",
                                   "tokenEntityName":"",
                                   "tokenValueGetter":"generic"
                                }
                             ]
                          },
                          {
                             "name":"Job",
                             "displayOrder":3,
                             "isCustomizable":true,
                             "isTemplate":true,
                             "defaultFields":[
                                {
                                   "fieldType":"Date",
                                   "name":"Joined Date",
                                   "extraData":"",
                                   "mandatory":true,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"joined_date",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getJobRecord,getJoinedDate"
                                },
                                {
                                   "fieldType":"Date",
                                   "name":"Probation End Date",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"probation_end_date",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getJobRecord,getProbationEndDate"
                                },
                                {
                                   "fieldType":"Date",
                                   "name":"Date of Permanency",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"date_of_permanency",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getJobRecord,getDateOfPermanency"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Job Title",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Job Title",
                                   "tokenName":"job_title_id",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getJobTitleName"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Sub Unit",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":false,
                                   "editable":false,
                                   "advancedTypeMap":"Sub Unit",
                                   "tokenName":"subunit_id",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getSubDivision,getName"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Location",
                                   "extraData":"",
                                   "mandatory":true,
                                   "deletable":false,
                                   "editable":false,
                                   "advancedTypeMap":"Location",
                                   "tokenName":"location_id",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getLocationAsString"
                                },
                                {
                                   "fieldType":"Date",
                                   "name":"Contract Start Date",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"contract_start_date",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getJobRecord,getContractStartDate"
                                },
                                {
                                   "fieldType":"Date",
                                   "name":"Contract End Date",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"contract_end_date",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getJobRecord,getContractEndDate"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Employment Status",
                                   "otherData":{
                                      "entity":"EmploymentStatus",
                                      "idField":"id",
                                      "nameField":"name"
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Generic",
                                   "tokenName":"emp_status",
                                   "tokenEntityName":"",
                                   "tokenValueGetter":"generic"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Job Category",
                                   "otherData":{
                                      "entity":"JobCategory",
                                      "idField":"id",
                                      "nameField":"name"
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Generic",
                                   "tokenName":"job_category",
                                   "tokenEntityName":"",
                                   "tokenValueGetter":"generic"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Work Schedule",
                                   "otherData":{
                                      "entity":"WorkSchedule",
                                      "idField":"id",
                                      "nameField":"name"
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Generic",
                                   "tokenName":"work_schedule",
                                   "tokenEntityName":"",
                                   "tokenValueGetter":"generic"
                                }
                             ]
                          },
                          {
                             "name":"Salary",
                             "displayOrder":4,
                             "isCustomizable":true,
                             "isTemplate":true,
                             "defaultFields":[
                                {
                                   "fieldType":"Advance Type",
                                   "name":"PayGrade",
                                   "otherData":{
                                      "entity":"PayGrade",
                                      "idField":"id",
                                      "nameField":"name"
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Generic",
                                   "tokenName":"pay_grade",
                                   "tokenEntityName":"",
                                   "tokenValueGetter":"generic"
                                },
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Currency",
                                   "otherData":{
                                      "entity":"CurrencyType",
                                      "idField":"currency_id",
                                      "nameField":"currency_name"
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "advancedTypeMap":"Generic",
                                   "tokenName":"currency_id",
                                   "tokenEntityName":"",
                                   "tokenValueGetter":"generic"
                                }
                             ]
                          },
                          {
                             "name":"Salary Components",
                             "displayOrder":5,
                             "isCustomizable":true,
                             "isTemplate":true,
                             "defaultFields":[

                             ]
                          },
                          {
                             "name":"Contact Details",
                             "displayOrder":5,
                             "isCustomizable":true,
                             "isTemplate":true,
                             "defaultFields":[
                                {
                                   "fieldType":"Text",
                                   "name":"Address Street 1",
                                   "otherData":{
                                      "length":70
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"street1",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getStreet1"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Address Street 2",
                                   "otherData":{
                                      "length":70
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"street2",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getStreet2"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"City",
                                   "otherData":{
                                      "length":70
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"city",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getCity"
                                },
                                {
                                   "fieldType":"Country",
                                   "name":"Country",
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"country",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getCountryNameAsString"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"State/Province",
                                   "otherData":{
                                      "length":70
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"province",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getProvinceNameAsString"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Zip/Postal Code",
                                   "otherData":{
                                      "length":10
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_zipcode",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpZipcode"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Home Telephone",
                                   "otherData":{
                                      "length":25
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_hm_telephone",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpHmTelephone"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Mobile",
                                   "otherData":{
                                      "length":25
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_mobile",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpMobile"
                                },
                                {
                                   "fieldType":"Text",
                                   "name":"Work Telephone",
                                   "otherData":{
                                      "length":25
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_work_telephone",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpWorkTelephone"
                                },
                                {
                                   "fieldType":"Email",
                                   "name":"Work Email",
                                   "otherData":{
                                      "length":50
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":false,
                                   "editable":false,
                                   "tokenName":"emp_work_email",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpWorkEmail"
                                },
                                {
                                   "fieldType":"Email",
                                   "name":"Other Email",
                                   "otherData":{
                                      "length":50
                                   },
                                   "extraData":"",
                                   "mandatory":false,
                                   "deletable":true,
                                   "editable":false,
                                   "tokenName":"emp_oth_email",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getEmpOthEmail"
                                }
                             ]
                          },
                          {
                             "name":"Report-to",
                             "displayOrder":6,
                             "isCustomizable":true,
                             "isTemplate":true,
                             "defaultFields":[
                                {
                                   "fieldType":"Advance Type",
                                   "name":"Supervisor",
                                   "extraData":"",
                                   "mandatory":true,
                                   "deletable":false,
                                   "editable":false,
                                   "advancedTypeMap":"Employee",
                                   "tokenName":"employeeId",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"employeeId"
                                },
                                {
                                   "fieldType":"Drop Down",
                                   "name":"Reporting Method",
                                   "extraData":"Direct,Indirect",
                                   "mandatory":true,
                                   "deletable":false,
                                   "editable":false,
                                   "tokenName":"reportingMethod",
                                   "tokenEntityName":"Employee",
                                   "tokenValueGetter":"getSupervisor,getReportingMethod,getName"
                                }
                             ]
                          }
                       ]
                    }
                 }'
 WHERE `name` = 'Employee Management';