ALTER TABLE ohrm_requestdesk_request_types ADD COLUMN `deletable` tinyint(1) not null default 1;


INSERT INTO `ohrm_requestdesk_request_types` (`template_id`, `name`, `approver_required`, `approval_levels`, `primary_owner_id`, `secondary_owner_id`, `deleted`, `default_approver_type`, `default_approver_id`, `deletable`) VALUES ('1', 'Resignation Request', '1', NULL, NULL, NULL, '0', 'supervisor', NULL, '0');
SET @resignation_request_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_config(`property`, `value`)
VALUES ('helpDesk.resignation_request_id', @resignation_request_id);

INSERT INTO  ohrm_requestdesk_custom_field_section (`request_type_id`, `display_name`, `display_order`, `is_customizable`)
VALUES (@resignation_request_id, 'Extra Custom Fields',1,1);
SET @resignation_custom_field_section_id := (SELECT LAST_INSERT_ID());

INSERT INTO  ohrm_requestdesk_custom_field (`name`, `unique_name`, `type`, `section_id`, `display_order`, `mandatory`, `deletable`,`is_default_field`)
VALUES ('Resignation Date', 'resignation_request.resignation_date',12,@resignation_custom_field_section_id,13,1,0,1);

-- Primary Owner can change Secondary Owner at any stage
INSERT INTO `ohrm_workflow_state_machine`(`workflow`, `state`, `role`, `action`, `resulting_state`) VALUES
 ('REQUEST','Submitted','RequestOwner','SAVE','Submitted'),
 ('REQUEST','Approved','RequestOwner','SAVE','Approved'),
 ('REQUEST_WITHOUT_APPROVE','In Progress','RequestOwner','SAVE','In Progress');

-- Default Approvers for any level

ALTER TABLE `ohrm_requestdesk_request_types` DROP COLUMN `default_approver_type`;
ALTER TABLE `ohrm_requestdesk_request_types` DROP FOREIGN KEY IF EXISTS `ohrm_requestdesk_request_types_ibfk_4`;
ALTER TABLE `ohrm_requestdesk_request_types` DROP COLUMN `default_approver_id`;

CREATE TABLE ohrm_requestdesk_request_type_approvers
(
 id              int auto_increment,
 request_type_id BIGINT NOT NULL,
 l1_default_approver_type varchar(50),
 l1_default_approver_id INT,
 l2_default_approver_type varchar(50),
 l2_default_approver_id INT,
 l3_default_approver_type varchar(50),
 l3_default_approver_id INT,
 l4_default_approver_type varchar(50),
 l4_default_approver_id INT,
 l5_default_approver_type varchar(50),
 l5_default_approver_id INT,
 primary key (id),
 FOREIGN KEY (`request_type_id`) REFERENCES `ohrm_requestdesk_request_types`(`id`) ON DELETE CASCADE,
 FOREIGN KEY (`l1_default_approver_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL,
 FOREIGN KEY (`l2_default_approver_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL,
 FOREIGN KEY (`l3_default_approver_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL,
 FOREIGN KEY (`l4_default_approver_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL,
 FOREIGN KEY (`l5_default_approver_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL
) engine = innodb default charset=utf8;

INSERT INTO  ohrm_requestdesk_request_type_approvers (`request_type_id`, `l1_default_approver_type`)
VALUES (@resignation_request_id, 'supervisor');

ALTER TABLE `ohrm_request` MODIFY COLUMN `secondary_owner_id` INT(7) DEFAULT NULL;
ALTER TABLE `ohrm_request` ADD CONSTRAINT FOREIGN KEY (`secondary_owner_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL;

UPDATE `ohrm_notification_subscriber_subscription_template`
SET `body`= concat('<pre style=\"font-size:12.8px;font-family:  Arial, sans-serif\">', `body`, '</pre>')
WHERE `name` LIKE '%Help desk request%' AND body NOT LIKE '<pre%';
