DROP TABLE IF EXISTS `ohrm_advanced_workflow_request_process`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_request`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity_process_actor_employee`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity_process_actor_type_status`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity_process_actor_type`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity_process_status`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity_process`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity_status`;
DROP TABLE IF EXISTS `ohrm_advanced_workflow_entity`;

CREATE TABLE `ohrm_advanced_workflow_entity`
(
    `name`        VARCHAR(255) NOT NULL,
    `workflow_id` VARCHAR(255) NULL,
    `description` VARCHAR(400) NULL,
    `options`     TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_fk1 FOREIGN KEY (`workflow_id`) REFERENCES `ohrm_workflow` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_entity_status`
(
    `id`          INT(11)      NOT NULL AUTO_INCREMENT,
    `entity`      VARCHAR(255) NOT NULL,
    `status_id`   INT(11)      NOT NULL UNIQUE,
    `name`        VARCHAR(255) NOT NULL,
    `description` VARCHAR(400) NULL,
    `sequence`    INT(3)       NULL,
    `options`     TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_status_fk1 FOREIGN KEY (`entity`) REFERENCES `ohrm_advanced_workflow_entity` (`name`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_entity_process`
(
    `id`          INT(11)      NOT NULL AUTO_INCREMENT,
    `entity`      VARCHAR(255) NOT NULL,
    `workflow_id` VARCHAR(255) NULL,
    `name`        VARCHAR(255) NOT NULL,
    `description` VARCHAR(400) NULL,
    `options`     TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_process_fk1 FOREIGN KEY (`entity`) REFERENCES `ohrm_advanced_workflow_entity` (`name`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_entity_process_fk2 FOREIGN KEY (`workflow_id`) REFERENCES `ohrm_workflow` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_entity_process_status`
(
    `id`                INT(11)      NOT NULL AUTO_INCREMENT,
    `entity_process_id` INT(11)      NOT NULL,
    `status_id`         INT(11)      NOT NULL UNIQUE,
    `name`              VARCHAR(255) NOT NULL,
    `description`       VARCHAR(400) NULL,
    `sequence`          INT(3)       NULL,
    `options`           TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_process_status_fk1 FOREIGN KEY (`entity_process_id`) REFERENCES `ohrm_advanced_workflow_entity_process` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_entity_process_actor_type`
(
    `id`                INT(11)      NOT NULL AUTO_INCREMENT,
    `entity_process_id` INT(11)      NOT NULL,
    `name`              VARCHAR(255) NOT NULL,
    `user_role`         VARCHAR(255) NULL,
    `processor_class`   VARCHAR(255) NULL,
    `workflow_id`       VARCHAR(255) NULL,
    `description`       VARCHAR(400) NULL,
    `options`           TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_type_fk1 FOREIGN KEY (`entity_process_id`) REFERENCES `ohrm_advanced_workflow_entity_process` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_type_fk2 FOREIGN KEY (`workflow_id`) REFERENCES `ohrm_workflow` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_entity_process_actor_type_status`
(
    `id`            INT(11)      NOT NULL AUTO_INCREMENT,
    `actor_type_id` INT(11)      NOT NULL,
    `status_id`     INT(11)      NOT NULL UNIQUE,
    `name`          VARCHAR(255) NOT NULL,
    `description`   VARCHAR(400) NULL,
    `sequence`      INT(3)       NULL,
    `options`       TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_type_status_fk1 FOREIGN KEY (`actor_type_id`) REFERENCES `ohrm_advanced_workflow_entity_process_actor_type` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_request`
(
    `id`        INT(11)      NOT NULL AUTO_INCREMENT,
    `entity`    VARCHAR(255) NOT NULL,
    `status_id` INT(11)      NOT NULL,
    `options`   TEXT         NULL,
    CONSTRAINT ohrm_advanced_workflow_request_fk1 FOREIGN KEY (`entity`) REFERENCES `ohrm_advanced_workflow_entity` (`name`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_request_fk2 FOREIGN KEY (`status_id`) REFERENCES `ohrm_advanced_workflow_entity_status` (`status_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_request_process`
(
    `id`                INT(11) NOT NULL AUTO_INCREMENT,
    `request_id`        INT(11) NOT NULL,
    `entity_process_id` INT(11) NOT NULL,
    `status_id`         INT(11) NOT NULL,
    `options`           TEXT    NULL,
    CONSTRAINT ohrm_advanced_workflow_request_process_fk1 FOREIGN KEY (`request_id`) REFERENCES `ohrm_advanced_workflow_request` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_request_process_fk2 FOREIGN KEY (`entity_process_id`) REFERENCES `ohrm_advanced_workflow_entity_process` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_request_process_fk3 FOREIGN KEY (`status_id`) REFERENCES `ohrm_advanced_workflow_entity_process_status` (`status_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `ohrm_advanced_workflow_request_process_actor`
(
    `id`                 INT(11) NOT NULL AUTO_INCREMENT,
    `request_process_id` INT(11) NOT NULL,
    `actor_type_id`      INT(11) NOT NULL,
    `emp_number`         INT(11) NOT NULL,
    `due_date`           DATE    NULL,
    `level`              INT(11) NULL,
    `status_id`          INT(11) NULL,
    `options`            TEXT    NULL,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_employee_fk1 FOREIGN KEY (`request_process_id`) REFERENCES `ohrm_advanced_workflow_request_process` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_employee_fk2 FOREIGN KEY (`actor_type_id`) REFERENCES `ohrm_advanced_workflow_entity_process_actor_type` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_employee_fk3 FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT ohrm_advanced_workflow_entity_process_actor_employee_fk4 FOREIGN KEY (`status_id`) REFERENCES `ohrm_advanced_workflow_entity_process_actor_type_status` (`status_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO ohrm_config (property, value)
VALUES ('awm.workflow_manager_class_name', 'AdvancedWorkflowManager');

SET @max_event_listener_position := (SELECT MAX(position)
                                     FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`)
VALUES ('advanced_workflow.request_state_change', 'AdvancedWorkflowRequestStateChangeWorkflowDependencyResolver', 1, @max_event_listener_position + 1),
       ('advanced_workflow.request_process_state_change', 'AdvancedWorkflowRequestProcessStateChangeWorkflowDependencyResolver', 1,
        @max_event_listener_position + 2),
       ('advanced_workflow.request_process_actor_state_change', 'AdvancedWorkflowRequestProcessActorStateChangeWorkflowDependencyResolver', 1,
        @max_event_listener_position + 3);

ALTER TABLE `ohrm_workflow_state_machine_dependancy`
    ADD `options` TEXT NULL AFTER `trigger_verification`;
