CREATE TABLE ohrm_advanced_task
(
    `id`               INT           NOT NULL AUTO_INCREMENT,
    `task_template_id` INT           NOT NULL,
    `name`             VARCHAR(1000) NOT NULL,
    `description`      MEDIUMTEXT    NULL,
    `due_date`         DATETIME      NULL,
    `status`           VARCHAR(255)  NULL,
    `metadata`         JSON          NOT NULL DEFAULT (JSON_OBJECT()),
    `is_completed`     BOOLEAN       NOT NULL DEFAULT FALSE,
    `created_user_id`  INT           NULL,
    `created_at`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`       DATETIME               DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT fk_ohrm_advanced_task_template_id FOREIGN KEY (task_template_id) REFERENCES `ohrm_advanced_task_template` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ohrm_advanced_task_created_user_id FOREIGN KEY (created_user_id) REFERENCES `ohrm_user` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE = INNODB
  DEFAULT CHARSET = UTF8;

CREATE TABLE ohrm_advanced_task_owner
(
    `id`            INT       NOT NULL AUTO_INCREMENT,
    `task_id`       INT       NOT NULL,
    `owner_user_id` INT       NOT NULL,
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`    DATETIME           DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_advanced_task_owner_task_id` FOREIGN KEY (task_id) REFERENCES ohrm_advanced_task (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_task_workflow_owner_user_id` FOREIGN KEY (owner_user_id) REFERENCES ohrm_user (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB
  DEFAULT CHARSET = UTF8;

CREATE TABLE ohrm_request_process_task
(
    `id`                 INT  NOT NULL AUTO_INCREMENT,
    `request_process_id` INT  NOT NULL,
    `task_id`            INT  NOT NULL,
    `options`            JSON NOT NULL DEFAULT (JSON_OBJECT()),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_request_process_task_request_process_id` FOREIGN KEY (`request_process_id`) REFERENCES `ohrm_advanced_workflow_request_process` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_request_process_task_task_id` FOREIGN KEY (`task_id`) REFERENCES `ohrm_advanced_task` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE ohrm_advanced_task_owner_form_record
(
    `id`             INT          NOT NULL AUTO_INCREMENT,
    `task_owner_id`  INT          NOT NULL,
    `form_record_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_advanced_task_owner_form_record_task_owner_id` FOREIGN KEY (`task_owner_id`) REFERENCES `ohrm_advanced_task_owner` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_advanced_task_owner_form_record_form_record_id` FOREIGN KEY (`form_record_id`) REFERENCES `ohrm_dynamic_form_record` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE IF NOT EXISTS ohrm_advanced_task_action_log (
     id INT(10) NOT NULL AUTO_INCREMENT,
     task_id int(11) NOT NULL,
     from_state varchar(255) default null,
     to_State varchar(255) default null,
     action varchar(255) default null,
     performed_user_id INT,
     comment varchar(255) default null,
     created_at DATETIME NOT NULL,
     updated_at DATETIME NOT NULL,
     PRIMARY KEY(id),
     CONSTRAINT `fk_advanced_task_action_log_task_id` FOREIGN KEY (`task_id`) REFERENCES `ohrm_advanced_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_advanced_task_action_log_performed_user_id` FOREIGN KEY (`performed_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;
