CREATE TABLE ohrm_external_integration_process (
  id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  configuration MEDIUMTEXT,
  parameters MEDIUMTEXT,
  isConsumer tinyint DEFAULT '0' NOT NULL,
  enabled tinyint DEFAULT '0' NOT NULL
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_external_integration_schema_rule (
  id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  configuration MEDIUMTEXT,
  enabled tinyint DEFAULT '0' NOT NULL
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_external_integration_process_execution_history` (
    `id` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `integration_id` INT NOT NULL,
    `start_time_stamp` datetime,
    `success` TINYINT(1)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('integration_data_export', 'Integration - Data Export', 1, 0, 0, 0);

SET @integration_data_export := (SELECT id FROM ohrm_data_group WHERE name = 'integration_data_export');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)  (SELECT id, @integration_data_export , 1 ,0 , 0, 0, 0 FROM ohrm_user_role WHERE name = 'Admin');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('integration_data_import', 'Integration - Data Import', 0, 1, 0, 0);

SET @integration_data_import := (SELECT id FROM ohrm_data_group WHERE name = 'integration_data_import');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)  (SELECT id, @integration_data_import , 0 ,1 , 0, 0, 0 FROM ohrm_user_role WHERE name = 'Admin');



CREATE TABLE ohrm_integration_process_queue_consumer (
  id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  consumer_id INT(4) NOT NULL,
  integration_id INT(4) NOT NULL,
    CONSTRAINT `fk_integration_process_consumer_consumer` FOREIGN KEY (`consumer_id`)
    REFERENCES `ohrm_external_data_queue_consumer` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_integration_process_integration_consumer` FOREIGN KEY (`integration_id`)
    REFERENCES `ohrm_external_integration_process` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

SET @notification_type_id = (SELECT `id` FROM `ohrm_notification_type` WHERE `name`='Other');
SET @module_id = (SELECT `id` FROM `ohrm_module` WHERE `name`='core');

INSERT INTO `ohrm_notification`(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
('Integration Process Failure',1,@notification_type_id,NULL,NULL,NULL,NULL,@module_id,1);

SET @email_media = (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');
SET @notification_id = (SELECT `id` FROM `ohrm_notification` WHERE `name` = 'Integration Process Failure');

INSERT INTO `ohrm_notification_subscriber_subscription_template`( `name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Integration Process Failure Notification Template',@notification_id,@email_media,'en_US','Integration Process  [FAILURE] | Instance : %instanceName% | Integration Process: %integrationName%',
        '<p>OrangeHRM instance %instanceName% is facing an integration process failure%attempts% This may need your attention.</p>
<p>This is an automated notification.Please do not reply to this email.</p>');
