SET @admin_id := (SELECT id FROM ohrm_user_role where name = 'Admin');
SET @sysAdminId = (SELECT id FROM `ohrm_user_role` WHERE name = 'ConfigAdmin');
SET @sysAdminTypeId = (SELECT id FROM `ohrm_user_role_type` WHERE name = 'ConfigAdmin');

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('integration', 1);
SET @integration_module_id := (SELECT `id` FROM ohrm_module WHERE `name` = 'integration');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('integration', 'HR Administration - Integration', 1, 1, 1, 1);

SET @integration_data_group_id := (SELECT id from ohrm_data_group WHERE name = 'integration');

INSERT INTO ohrm_user_role_data_group (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self) VALUES
(@admin_id, @integration_data_group_id, 1, 0, 0, 0, 0),
(@sysAdminId, @integration_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO ohrm_screen (name, module_id, action_url) VALUES
( 'Integration', @integration_module_id, 'viewIntegration');

SET @integration_screen_id =(SELECT id from ohrm_screen WHERE action_url = 'viewIntegration');

SET @other_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Other' AND `level` = 1);

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`,
                              `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`,
                              `include_attachment_section`, `has_multiple_records`)
VALUES ('Integrations', NULL, @integration_screen_id, @other_pillar_menu_id, 2, 40, NULL,
        'intgeration.svg', 'vueadmin.integration', '1', NULL, NULL);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_id, @integration_screen_id, 1, 0, 0, 0),
(@sysAdminId, @integration_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_data_group_screen (data_group_id, screen_id, permission, self) VALUES
(@integration_data_group_id,@integration_screen_id ,1 ,0);


CREATE TABLE ohrm_external_vendors
(
    `id`              int(2) not null auto_increment,
    `name`            VARCHAR(255) NOT NULL,
    `description`     TEXT DEFAULT NULL,
    `vendor_logo`     BLOB,
    `processor_class` varchar(100) NOT NULL,
    `data_group_id`   INT          NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;


CREATE TABLE `ohrm_integration_flow_type` (
                                              `id` int(11) NOT NULL,
                                              `name` varchar(255) NOT NULL,
                                              `is_to_date_required` tinyint(1) NOT NULL DEFAULT 0,
                                              `enable` tinyint(1) NOT NULL DEFAULT 1,
                                              PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ohrm_integration_flow_type` (`id`, `name`, `is_to_date_required`, `enable`) VALUES
                                                                                             (1, 'Employee Data Flow', 0, 1),
                                                                                             (2, 'Leave Data Flow', 1, 1);

CREATE TABLE `ohrm_external_integration_flow_execution_history`
(
    `id`               INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `integration_id`   INT(4) NOT NULL,
    `start_time_stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `success`          TINYINT(1),
    `user_id`          INT(10) NOT NULL,
    `parameters`          TEXT NULL,
    `action`          VARCHAR(255) NULL,
    CONSTRAINT `fk_flow_execution_history_integration_id` FOREIGN KEY (`integration_id`) REFERENCES `ohrm_external_integration_flow` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_flow_execution_history_run_user` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_external_vendors` (`id`, `name`, `description`, `vendor_logo`, `processor_class`)
VALUES (NULL, 'IPS', 'Integrated Payroll Services Inc', 0x[BLOB], 'IntegrationIPSProcessor'),
       (NULL, 'Avendoo', 'Learning Management System', 0x[BLOB], 'IntegrationAvendooProcessor');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('integration.celigo_flow_url', 'https://api.integrator.io/v1/flows/%flowId%'),
('integration.get_last_runtime_on_generate', '0');


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