CREATE TABLE IF NOT EXISTS ohrm_onboarding_template_location (
                                                            job_id INT NOT NULL,
                                                            location_id INT NOT NULL,
                                                            PRIMARY KEY(job_id, location_id),
    foreign key (job_id) references ohrm_job(`id`) on delete cascade,
    foreign key (location_id) references  ohrm_location(`id`) on delete cascade
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_job_participant_employee (
                                                            job_id INT NOT NULL,
                                                            emp_number INT NOT NULL,
                                                            PRIMARY KEY(job_id, emp_number),
    foreign key (job_id) references ohrm_job(`id`) on delete cascade,
    foreign key (emp_number) references  hs_hr_employee(`emp_number`) on delete cascade
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

-- Update location for existing templates

INSERT INTO ohrm_onboarding_template_location (job_id, location_id)
SELECT
    jb.id AS job_id,
    loc.id AS location_id
FROM
    ohrm_job jb,
    ohrm_location loc
WHERE
        jb.is_template = 1;

-- Update participant employees for existing events

DROP PROCEDURE IF EXISTS InsertNewHires;

DELIMITER //

CREATE PROCEDURE InsertNewHires()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE job_id INT;
    DECLARE emp_numbers TEXT;
    DECLARE emp_number INT;
    DECLARE cur CURSOR FOR
SELECT id, new_hires_ids FROM ohrm_job WHERE is_template = 0 AND new_hires_ids IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
        FETCH cur INTO job_id, emp_numbers;
        IF done THEN
            LEAVE read_loop;
END IF;

        SET emp_numbers = CONCAT(emp_numbers, ',');
        WHILE LENGTH(emp_numbers) > 0 DO
            SET emp_number = CAST(SUBSTRING_INDEX(emp_numbers, ',', 1) AS UNSIGNED);
INSERT INTO ohrm_job_participant_employee (job_id, emp_number) VALUES (job_id, emp_number);
SET emp_numbers = SUBSTRING(emp_numbers, LENGTH(emp_number) + 2);
END WHILE;
END LOOP;
CLOSE cur;
END;
//

DELIMITER ;

CALL InsertNewHires();

-- Drop unnecessary columns in ohrm_job table

ALTER TABLE `ohrm_job` DROP COLUMN location_id;
ALTER TABLE `ohrm_job` DROP COLUMN new_hires_ids;

UPDATE ohrm_task SET `status` = 'COMPLETED'  WHERE status = 'COMPLETE';

SET @onboarding_task_workflow_id := (SELECT `id` FROM `ohrm_workflow` WHERE `name`='onboardTask');

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`) VALUES
(@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMJOBOWNER', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'COMPLETED', 'OHRMJOBOWNER', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'INCOMPLETE', 'OHRMTASKOWNER', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'COMPLETED', 'OHRMTASKOWNER', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'INCOMPLETE', 'ADMIN','On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'COMPLETED', 'ADMIN', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'INCOMPLETE', 'SUPERVISOR', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'COMPLETED', 'SUPERVISOR', 'On Hold', 'ON HOLD', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'OHRMJOBOWNER', 'Incomplete', 'INCOMPLETE', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'OHRMJOBOWNER', 'Complete', 'COMPLETED', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'OHRMTASKOWNER', 'Incomplete', 'INCOMPLETE', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'OHRMTASKOWNER', 'Complete', 'COMPLETED', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'ADMIN','Incomplete', 'INCOMPLETE', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'ADMIN', 'Complete', 'COMPLETED', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'SUPERVISOR', 'Incomplete', 'INCOMPLETE', '', 0),
(@onboarding_task_workflow_id, 'ON HOLD', 'SUPERVISOR', 'Complete', 'COMPLETED', '', 0);

UPDATE ohrm_workflow_state_machine SET `state` = 'COMPLETED'  WHERE state = 'COMPLETE' AND workflow = @onboarding_task_workflow_id;
UPDATE ohrm_workflow_state_machine SET `resulting_state` = 'COMPLETED'  WHERE resulting_state = 'COMPLETE' AND workflow = @onboarding_task_workflow_id;
UPDATE ohrm_workflow_state_machine SET `action` = 'Incomplete'  WHERE `action` = 'Mark Incomplete' AND workflow = @onboarding_task_workflow_id;

-- On/Off Boarding notification subject changes

UPDATE ohrm_notification_subscriber_subscription_template SET subject = CONCAT('On/Off Boarding - ', subject)
WHERE notification_id in (select id from ohrm_notification where module_id in (select id from ohrm_module where name = 'onboarding'));

-- Enable task manipulation data group for custom supervisors

SET @onboarding_employee_task_manipulation_id := (SELECT id from ohrm_data_group where `name` = 'onboarding_employee_task_manipulation');

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

SET @supervisor_role_id :=(SELECT `id` FROM `ohrm_user_role` WHERE `name`='Supervisor');

SET @ohrmtask_owner_id :=(SELECT `id` FROM `ohrm_user_role` WHERE `name`='OhrmTaskOwner');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('onboarding_my_task_manipulation', 'On/Off Boarding - My Tasks', 1, 1, 1, 1);
SET @onboarding_my_task_manipulation_id := (SELECT id from ohrm_data_group where `name` = 'onboarding_my_task_manipulation');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (@ohrmtask_owner_id, @onboarding_my_task_manipulation_id, 1, 1, 1, 1, 0);

UPDATE `ohrm_user_role_data_group` SET can_update = 0 WHERE `data_group_id` = @onboarding_employee_task_manipulation_id AND `user_role_id` IN (@supervisor_role_id, @ohrmtask_owner_id);

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, @onboarding_my_task_manipulation_id, 1, 0, 1, 0);

-- Rename data group descriptions

Update ohrm_data_group set description = 'On/Off Boarding - Manage Events/Templates' where name = 'onboarding_job_manipulation';
Update ohrm_data_group set description = 'On/Off Boarding - View Employee Tasks' where name = 'onboarding_employee_task_manipulation';
Update ohrm_data_group set description = 'On/Off Boarding - Configure Tasks' where name = 'onboarding_task_pool_manipulation';
