-- Trigger Reminder Task Notification

ALTER TABLE ohrm_task_type ADD notified_after varchar(64) after notified_before;

ALTER TABLE ohrm_task ADD notified_after varchar(64) after notified_before;

SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='On/Off Boarding - Reminder Task Notification');

UPDATE `ohrm_notification_subscriber_subscription_template`
SET `body` ='<p>Hi,<br /><br />%message%<br /><br />Event: %event%<br />Task: %pendingTask%<br />Description: %description%<br />Task Owner: %taskowner%<br />Participant: %newHire%<br />Due Date: %dueDate%<br />Status: %status%<br />Priority: %priority%<br /><br />Thank you.<br /><br />This is an automated notification.</p>' WHERE `notification_id` = @notification_id;

-- New Data group for on/off boarding task upload

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('onboarding_task_import_export', 'On/Off Boarding - Import or Export Tasks', 1, 1, NULL, NULL);
SET @onboarding_task_import_export_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'onboarding_task_import_export');

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES (@admin_role_id, @onboarding_task_import_export_data_group_id, 1, 1, NULL, NULL, 0);

SET @admin_role_type_id := (SELECT `id` FROM ohrm_user_role_type WHERE `name` = 'Admin');

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES (@admin_role_type_id, @onboarding_task_import_export_data_group_id);

-- Change dashboard report configuration

UPDATE `ohrm_config` SET `value` = 12 WHERE `property` = 'onboarding.dashboad_report_time_period';

-- Task Type Attachment table

CREATE TABLE IF NOT EXISTS ohrm_onboarding_task_type_attachment (
                                                                    `id`  INT AUTO_INCREMENT,
                                                                    `task_type_id` INT(4) NOT NULL,
    `eattach_desc` VARCHAR(200) DEFAULT NULL,
    `eattach_filename` VARCHAR(255) DEFAULT NULL,
    `eattach_size` INT(4) DEFAULT '0',
    `eattach_attachment` MEDIUMBLOB,
    `eattach_type` VARCHAR(200) DEFAULT NULL,
    `attached_by` INT(4) DEFAULT NULL,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP DEFAULT now(),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_onboarding_task_type_attachment_ohrm_task_type` FOREIGN KEY (`task_type_id`)
    REFERENCES `ohrm_task_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;

--  Default Task Attachment Table

CREATE TABLE IF NOT EXISTS ohrm_onboarding_tasks_default_attachment (
                                                                        `id`  INT AUTO_INCREMENT,
                                                                        `task_id` INT(4) NOT NULL,
    `eattach_desc` VARCHAR(200) DEFAULT NULL,
    `eattach_filename` VARCHAR(255) DEFAULT NULL,
    `eattach_size` INT(4) DEFAULT '0',
    `eattach_attachment` MEDIUMBLOB,
    `eattach_type` VARCHAR(200) DEFAULT NULL,
    `attached_by` INT(4) DEFAULT NULL,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP DEFAULT now(),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_onboarding_tasks_default_attachment_ohrm_task` FOREIGN KEY (`task_id`)
    REFERENCES `ohrm_task` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    ) DEFAULT CHARACTER SET UTF8 ENGINE = INNODB;