-- Approvers can ask for request info
INSERT INTO `ohrm_workflow_state_machine`(`workflow`, `state`, `role`, `action`, `resulting_state`) VALUES
                                                                                                        ('REQUEST_PROCESS_ACTOR_APPROVER','INITIATED','RequestApprover','REQUEST INFO','Request Info Required'),
                                                                                                        ('REQUEST','Submitted','RequestApprover','REQUEST INFO','Request Info Required'),
                                                                                                        ('REQUEST','Request Info Required','RequestApprover','REQUEST INFO','Request Info Required'),
                                                                                                        ('REQUEST','In Progress','RequestApprover','REQUEST INFO','Request Info Required');

SET @request_more_info_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request More Information');
SET @workflow_id := 'REQUEST_PROCESS_ACTOR_APPROVER';
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @requester_id := (SELECT id FROM ohrm_user_role WHERE name = 'Requester');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'REQUEST INFO' AND `role` = 'RequestApprover';

SET @requester_subscriber_id := (SELECT id FROM `ohrm_notification_subscriber` WHERE `user_role_id` = @requester_id AND `media_id`= @email_media AND `type`= 'UserRole');
SET @request_more_info_template_id := (SELECT id FROM ohrm_notification_subscriber_subscription_template WHERE `notification_id` = @request_more_info_notification_id);
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @requester_subscriber_id, @request_more_info_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Admins can ask for request info

INSERT INTO `ohrm_workflow_state_machine`(`workflow`, `state`, `role`, `action`, `resulting_state`) VALUES
                                                                                                        ('REQUEST','Submitted','Admin','REQUEST INFO','Request Info Required'),
                                                                                                        ('REQUEST','In Progress','Admin','REQUEST INFO','Request Info Required'),
                                                                                                        ('REQUEST','Request Info Required','Admin','REQUEST INFO','Request Info Required');

SET @request_workflow_id := 'REQUEST';

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @request_workflow_id AND `action` = 'REQUEST INFO' AND `role` = 'Admin';

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @requester_subscriber_id, @request_more_info_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- End of admins can ask for request info

CREATE TABLE ohrm_request_info_required
(
    id INT AUTO_INCREMENT,
    request_id INT(11) NOT NULL,
    state VARCHAR(50) NOT NULL,
    emp_number INT(7),
    requested_info VARCHAR(4000) DEFAULT NULL,
    action_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (`request_id`) REFERENCES `ohrm_request`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL
) engine = innodb DEFAULT charset=utf8;

ALTER TABLE ohrm_request DROP COLUMN requested_info;

SET @helpdesk_report_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'helpdesk_reports');
SET @request_information_report_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'request_information_report');

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id, can_read, can_create, can_update, can_delete) VALUES
                                                                                                                                (1, @helpdesk_report_data_group_id, 1, 0, 0, 0),
                                                                                                                                (1, @request_information_report_data_group_id, 1, 0, 0, 0);

-- Configure request types based on the location

CREATE TABLE IF NOT EXISTS ohrm_requestdesk_request_type_location (
                                                                      request_type_id BIGINT NOT NULL,
                                                                      location_id INT NOT NULL,
                                                                      PRIMARY KEY(request_type_id, location_id),
    FOREIGN KEY (request_type_id) REFERENCES ohrm_requestdesk_request_types(`id`) ON DELETE cascade,
    FOREIGN KEY (location_id) REFERENCES  ohrm_location(`id`) ON DELETE cascade
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

-- Update locations for existing request types

INSERT INTO ohrm_requestdesk_request_type_location (request_type_id, location_id)
SELECT
    rt.id AS request_type_id,
    loc.id AS location_id
FROM
    ohrm_requestdesk_request_types rt,
    ohrm_location loc
WHERE
        rt.id NOT IN (SELECT DISTINCT request_type_id FROM ohrm_requestdesk_request_type_location);

INSERT INTO ohrm_data_list (`name`, `meta_data`) VALUES ('requestDeskEmployeeGeneralRequisitionsList', '{"listFields":{"requestId":"requestId","requestTitle":"requestTitle","requestStatus":"requestStatus", "requestType":"requestType", "pendingWith":"pendingWith", "requestOwner":"requestOwner", "priority":"priority","submittedDate":"submittedDate","resolutionDate":"resolutionDate","requesterName":"requesterName"},"availableFields":{"requestId":"Request ID","requestTitle":"Request Title","requestStatus":"Request Status","pendingWith":"Pending With","priority":"Priority","requestType":"Request Type","requestOwner":"Primary Request Owner", "secondaryRequestOwner":" Secondary Request Owner", "subUnit":"Sub Unit","location":"Location","submittedDate":"Submitted Date","resolutionDate":"Resolution Date","requesterName":"Requester Name", "requesterEmpId":"Requester Emp ID", "costCenter":"Cost Center"},"defaultFields":["requestId","requestTitle","requestType", "requestOwner", "requestStatus","requesterName","pendingWith","priority","submittedDate","resolutionDate"],"mandatoryFields":["requestId", "requestTitle","requestType"]}');
INSERT INTO ohrm_data_list (`name`, `meta_data`) VALUES ('requestDeskMyGeneralRequisitionsList', '{"listFields":{"requestId":"requestId","requestTitle":"requestTitle","requestStatus":"requestStatus", "requestType":"requestType", "requestOwner":"requestOwner", "priority":"priority","submittedDate":"submittedDate", "pendingWith":"pendingWith", "resolutionDate":"resolutionDate"},"availableFields":{"requestId":"Request ID","requestTitle":"Request Title","requestStatus":"Request Status","pendingWith":"Pending With","priority":"Priority","requestType":"Request Type","requestOwner":"Primary Request Owner", "secondaryRequestOwner":" Secondary Request Owner","submittedDate":"Submitted Date","resolutionDate":"Resolution Date"},"defaultFields":["requestId","requestTitle","requestType","requestStatus", "requestOwner", "pendingWith","priority","submittedDate","resolutionDate"],"mandatoryFields":["requestId", "requestTitle","requestType"]}');

SET @notification_id = (SELECT `id` FROM `ohrm_notification` WHERE `name` = '(General/Hiring) Request Desk: Request More Information');

UPDATE `ohrm_notification_subscriber_subscription_template`
SET `subject` = "Request Desk: Additional Information Needed for %requestType% - %title%",`body` = '<pre style="font-size:12.8px;font-family:  Arial, sans-serif">Hi %recipientFullName%,

 Your request has been reviewed by %infoRequester%. Before we can proceed, we need further information.

 Information Needed: %requestedInfo%
 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

Please log in <a href="%link%">here</a> to update your request with further information.

 Thank you.

 This is an automated notification.</pre>'
WHERE `notification_id` = @notification_id;

ALTER TABLE ohrm_requestdesk_request_types ADD COLUMN `is_active` BOOLEAN NOT NULL DEFAULT TRUE;

SET @requestInformationReportID := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'Request_Inforamtion_Report');
SET @requestClosureReportByOwnerReportID := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'helpdesk_request_closure_by_owner');

DELETE FROM `ohrm_advanced_report` WHERE id IN (@requestInformationReportID, @requestClosureReportByOwnerReportID);

update ohrm_notification set name = '(General) Request Desk: Non Approve Request, Request More Information' where name = '(General) Request Desk: Non Approve Request Request More Information';

