CREATE TABLE ohrm_requestdesk_request_type_template (id BIGINT AUTO_INCREMENT, name VARCHAR(150) NOT NULL,processor_class VARCHAR(150) NOT NULL,template text default NULL, approval_mandatory tinyint(1) DEFAULT 0 NOT NULL, status tinyint(1) DEFAULT 0 NOT NULL,PRIMARY KEY(id)) ENGINE = INNODB;
INSERT INTO ohrm_requestdesk_request_type_template (`name`, `processor_class`, `template`, `approval_mandatory`, `status`) VALUES
('General Request', 'RequestDeskGeneralRequestProcessor','{
  "meta": {
    "terminalHandler": "",
    "maximumApprovalLevels": 5
  },
  "formData": {
    "sections": [
      {
        "name": "Extra Custom Fields",
        "displayOrder": 1,
        "isCustomizable": true,
        "defaultFields": []
      }]

  }
}
', 0, 1);

ALTER TABLE `ohrm_vas_request_form_types` RENAME TO `ohrm_requestdesk_request_types`;


ALTER TABLE `ohrm_requestdesk_request_types` ADD COLUMN `template_id` BIGINT NOT NULL;
SET @general_template_id := (SELECT id from ohrm_requestdesk_request_type_template WHERE name = 'General Request');
UPDATE `ohrm_requestdesk_request_types` SET `template_id`=@general_template_id WHERE 1;
ALTER TABLE `ohrm_requestdesk_request_types` ADD FOREIGN KEY (`template_id`) REFERENCES `ohrm_requestdesk_request_type_template`(`id`);

ALTER TABLE `ohrm_requestdesk_request_types` ADD COLUMN `approval_levels` INT;

UPDATE `ohrm_requestdesk_request_types` SET `approval_levels` = CASE WHEN approver_required = 1 THEN 1 ELSE null END WHERE 1;

create table ohrm_requestdesk_custom_field_section (
id int auto_increment,
request_type_id BIGINT NOT NULL,
display_name varchar(255),
display_order smallint default 0,
deleted tinyint(1) default 0,
is_customizable tinyint(1) default 0,
primary key(id)
) engine = innodb default charset=utf8;
alter table `ohrm_requestdesk_custom_field_section` add constraint foreign key (`request_type_id`) references ohrm_requestdesk_request_types(`id`) on delete cascade;

ALTER TABLE `ohrm_request` ADD COLUMN `pending_with_employee_id` int(11) default null;
ALTER TABLE `ohrm_request` ADD FOREIGN KEY (`pending_with_employee_id`) REFERENCES `hs_hr_employee`(`emp_number`) on delete set null;

ALTER TABLE `ohrm_request` ADD COLUMN `workflow_request_id` int(11) default null;
ALTER TABLE `ohrm_request` ADD FOREIGN KEY (`workflow_request_id`) references `ohrm_advanced_workflow_request`(`id`) on delete set null;

ALTER TABLE `ohrm_request` ADD COLUMN `extra_details` text default NULL;

UPDATE `ohrm_screen` SET `name`='My General Request', `action_url`='viewMyGeneralRequests' WHERE `action_url` = 'viewMyRequests';
UPDATE `ohrm_screen` SET `name`='Employee General Request', `action_url`='viewEmployeeGeneralRequests' WHERE `action_url` = 'viewEmployeeRequests';

SET @my_general_requests_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewMyGeneralRequests');
SET @employee_general_requests_screen_id :=(SELECT id from ohrm_screen WHERE action_url = 'viewEmployeeGeneralRequests');

SET @request_desk_parent_menu_id := (SELECT MAX(id) from ohrm_menu_item WHERE menu_title = 'Request Desk');

UPDATE `ohrm_menu_item` SET `menu_title`='My General Requests' WHERE `menu_title` = 'My Requests' AND `level`=2;
UPDATE `ohrm_menu_item` SET `menu_title`='Employee General Requests' WHERE `menu_title` = 'Employee Requests' AND `level`=2;


INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`)
VALUES (NULL, 'Employee Requests', NULL, NULL, @request_desk_parent_menu_id, '2', '1000', NULL, NULL, NULL, '1', NULL, NULL);
SET @request_desk_employee_requests_menu_id := LAST_INSERT_ID();

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`, `has_multiple_records`)
VALUES (NULL, 'My Requests', NULL, NULL, @request_desk_parent_menu_id, '2', '1000', NULL, NULL, NULL, '1', NULL, NULL);
SET @request_desk_my_requests_menu_id := LAST_INSERT_ID();

UPDATE `ohrm_menu_item` SET `screen_id` = @my_general_requests_screen_id,`parent_id`=@request_desk_my_requests_menu_id, `level`=3, `order_hint` =800 WHERE `menu_title` = 'My General Requests';
UPDATE `ohrm_menu_item` SET `screen_id` = @employee_general_requests_screen_id,`parent_id`=@request_desk_employee_requests_menu_id, `level`=3, `order_hint` =600 WHERE `menu_title` = 'Employee General Requests';

ALTER TABLE `ohrm_vas_custom_field` RENAME TO `ohrm_requestdesk_custom_field`;

ALTER TABLE `ohrm_requestdesk_custom_field` ADD COLUMN `section_id` int not null;
ALTER TABLE `ohrm_requestdesk_custom_field` ADD COLUMN `is_default_field` tinyint(1) default 0 not null;
ALTER TABLE `ohrm_requestdesk_custom_field` ADD COLUMN `other_data` text default NULL;

UPDATE `ohrm_requestdesk_custom_field` SET `section_id`= `form_type_id`;

ALTER TABLE `ohrm_vas_custom_advance_type_map` ADD COLUMN `allow_multiple`  TINYINT(1) DEFAULT 0 NOT NULL;

-- adding location and employee advanced type
INSERT INTO `ohrm_vas_custom_advance_type_map` (`entity`, `display_name`, `id_field`, `name_field`, `allow_multiple`) VALUES ('Location', 'Location', 'id', 'name', 0), ('Employee', 'Employee Multiple Select', 'empNumber', 'firstName', 1);

DELETE FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'REQUEST';
SET @requestDeskModuleID :=(SELECT id from ohrm_module WHERE name = 'Request Desk');
DELETE FROM `ohrm_notification` WHERE `module_id` = @requestDeskModuleID;

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

DELETE FROM `ohrm_notification_subscriber` WHERE `user_role_id` = @requester_id;
DELETE FROM `ohrm_notification_subscriber` WHERE `user_role_id` = @request_owner_id;
DELETE FROM `ohrm_notification_subscriber` WHERE `user_role_id` = @request_approver_id;

SET @next_wf_order = (SELECT max(order_no)
                      FROM ohrm_workflow);
INSERT INTO `ohrm_workflow`(`id`,`name`, `label`, `available_to`, `default_available_to`, `module_id`,`order_no`) VALUES
('REQUEST_WITHOUT_APPROVE','requestDeskWithOutApprove','Request Desk Without Approval',1,2,@requestDeskModuleID,@next_wf_order+1);
