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','Submitted','AffectedRequestEmployee','SAVE','Submitted'),
('REQUEST','Request Info Required','AffectedRequestEmployee','SUBMIT','Approved'),
('REQUEST_WITHOUT_APPROVE','Request Info Required','AffectedRequestEmployee','SUBMIT','In Progress'),
('REQUEST','Request Info Required','AffectedRequestEmployee','SAVE','Request Info Required'),
('REQUEST_WITHOUT_APPROVE','Request Info Required','AffectedRequestEmployee','SAVE','Request Info Required');

-- 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');