CREATE TABLE `ohrm_async_event` (
    `id` INT(4) AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `listener_class` VARCHAR(255) NULL,
    `enabled` TINYINT DEFAULT 0 NOT NULL,
    `position` INT NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_async_event_tag` (
    `id` INT(4) AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `description` text NULL,
    `enabled` TINYINT DEFAULT 0 NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_async_event_event_tag` (
    `id` INT(4) AUTO_INCREMENT,
    `event_id` INT(4) NOT NULL,
    `tag_id` INT(4) NOT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_ohrm_async_event_event_tag_event_id` FOREIGN KEY (`event_id`)
    REFERENCES `ohrm_async_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_async_event_event_tag_tag_id` FOREIGN KEY (`tag_id`)
    REFERENCES `ohrm_async_event_tag` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_data_queue_consumer` (
    `id` INT(4) AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `description` text NULL,
    `consumer_class` VARCHAR(255) NOT NULL,
    `enabled` TINYINT DEFAULT 0 NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO ohrm_config(`property`,`value`) VALUES ('data_queue_enabled','0');

INSERT INTO `ohrm_data_queue_consumer` (`name`, `description`, `consumer_class`, `enabled`) VALUES
('Audit trail queue consumer', 'This queue includes audit trail records', 'AuditTrailQueueConsumer', 1),
('Navigation queue consumer', 'This queue includes navigation related records', 'NavigationQueueConsumer', 1);

INSERT INTO `ohrm_async_event_tag` (`name`, `description`, `enabled`) VALUES
('audit_trail', 'This tag is for audit trail related events', 1),
('navigation', 'This tag is for navigation related events', 1);

INSERT INTO `ohrm_async_event` (`name`, `enabled`, `position`) VALUES
  ('user_navigation', 1,10);

SET @navigation_event_id = (SELECT id FROM `ohrm_async_event` WHERE name = 'user_navigation');
SET @navigation_tag_id = (SELECT id FROM `ohrm_async_event_tag` WHERE name = 'navigation');
INSERT INTO `ohrm_async_event_event_tag` (`event_id`, `tag_id`) VALUES
  (@navigation_event_id, @navigation_tag_id);

INSERT INTO `ohrm_async_event` (`name`, `listener_class`, `enabled`, `position`) VALUES
  ('expense.estimation_request.workflow_change', 'EstimateRequestWorkflowChangeAuditListener', 1, 1);
SET @assign_event_id = (SELECT id FROM `ohrm_async_event` WHERE name = 'expense.estimation_request.workflow_change');
SET @audit_tag_id = (SELECT id FROM `ohrm_async_event_tag` WHERE name = 'audit_trail');
INSERT INTO `ohrm_async_event_event_tag` (`event_id`, `tag_id`) VALUES
  (@assign_event_id, @audit_tag_id);

create table `ohrm_external_navigation_log` (
    `id` int(11) not null auto_increment,
    `from_url` varchar(255) null,
    `to_url` varchar(255) null,
    `user` text null,
    `ip` varchar(255) null,
    `user_agent` varchar(255) null,
    `session` varchar(255) null,
    `time` timestamp DEFAULT CURRENT_TIMESTAMP,
    `app` varchar(255) null DEFAULT 'main',
    primary key (id)
) engine=innodb default charset=utf8;
