--liquibase formatted sql

--changeset 1.1:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;

--
-- Table structure for table `event_message`
--

DROP TABLE IF EXISTS `notification_has_recipient`;
DROP TABLE IF EXISTS `notification`;
DROP TABLE IF EXISTS `content`;
DROP TABLE IF EXISTS `message`;
DROP TABLE IF EXISTS `recipient`;
DROP TABLE IF EXISTS `event_notification_has_event_recipient`;
DROP TABLE IF EXISTS `event_notification`;
DROP TABLE IF EXISTS `event_content`;
DROP TABLE IF EXISTS `event_message`;
DROP TABLE IF EXISTS `event_recipient`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `type` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event_message`
--

LOCK TABLES `event_message` WRITE;
/*!40000 ALTER TABLE `event_message` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_message` ENABLE KEYS */;
UNLOCK TABLES;


--
-- Table structure for table `event_content`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(128) NOT NULL,
  `body` varchar(1024) DEFAULT NULL,
  `language` varchar(128) NOT NULL,
  `event_message_id` int(11) NOT NULL,
  PRIMARY KEY (`id`, `event_message_id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_event_content_event_message1_idx` (`event_message_id`),
  CONSTRAINT `fk_event_content_event_message1` FOREIGN KEY (`event_message_id`) REFERENCES `event_message` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event_content`
--

LOCK TABLES `event_content` WRITE;
/*!40000 ALTER TABLE `event_message` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_event_message` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `event_notification`
--


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scheduler_id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `event_type` varchar(45) DEFAULT NULL,
  `event_message_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`scheduler_id`,`event_message_id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_event_notification_scheduler1_idx` (`scheduler_id`),
  KEY `fk_event_notification_event_message1_idx` (`event_message_id`),
  CONSTRAINT `fk_event_notification_scheduler1` FOREIGN KEY (`scheduler_id`) REFERENCES `scheduler` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_event_notification_event_message1` FOREIGN KEY (`event_message_id`) REFERENCES `event_message` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;



--
-- Dumping data for table `event_notification`
--

LOCK TABLES `event_notification` WRITE;
/*!40000 ALTER TABLE `event_notification` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_notification` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `event_recipient`
--


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_recipient` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `destination` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event_recipient`
--

LOCK TABLES `event_recipient` WRITE;
/*!40000 ALTER TABLE `event_recipient` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_recipient` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `event_notification_has_event_recipient`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_notification_has_event_recipient` (
  `event_notification_id` int(11) NOT NULL,
  `event_recipient_id` int(11) NOT NULL,
  PRIMARY KEY (`event_notification_id`,`event_recipient_id`),
  KEY `fk_notification_has_recipient_notification1_idx` (`event_notification_id`),
  KEY `fk_notification_has_recipient_recipient1_idx` (`event_recipient_id`),
  CONSTRAINT `fk_notification_has_recipient_notification1` FOREIGN KEY (`event_notification_id`) REFERENCES `event_notification` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_notification_has_recipient_recipient1` FOREIGN KEY (`event_recipient_id`) REFERENCES `event_recipient` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event_notification_has_event_recipient`
--

LOCK TABLES `event_notification_has_event_recipient` WRITE;
/*!40000 ALTER TABLE `event_notification_has_event_recipient` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_notification_has_event_recipient` ENABLE KEYS */;
UNLOCK TABLES;


--changeset 1.1:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;

alter table detail add column if not exists lang varchar(3) not null default 'eng';

--changeset 1.1:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;

alter table connector add column if not exists icon  varchar(128);

--changeset 1.1:4 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE enhancement MODIFY expert_code varchar(1024);

--changeset 1.1:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE enhancement MODIFY expert_code TEXT;

--changeset 1.3:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
-- -----------------------------------------------------
-- Table `mydb`.`widget`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `widget` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `icon` VARCHAR(45) NULL,
  `tooltipTranslationKey` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`widget_setting`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `widget_setting` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `x_axis` INT NULL,
  `y_axis` INT NULL,
  `width` INT NULL,
  `height` INT NULL,
  `min_width` INT NULL,
  `min_height` INT NULL,
  `widget_id` INT NOT NULL,
  `user_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`, `widget_id`),
  INDEX `fk_widget_setting_widget_idx` (`widget_id` ASC),
  INDEX `fk_widget_setting_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_widget_setting_widget`
    FOREIGN KEY (`widget_id`)
    REFERENCES `widget` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_widget_setting_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `opencelium`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

--changeset 1.3:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
LOCK TABLES `widget` WRITE;
/*!40000 ALTER TABLE `event_notification_has_event_recipient` DISABLE KEYS */;
INSERT INTO `widget` VALUES (1,'CONNECTION_OVERVIEW','cable','Connection Overview'), (2,'CURRENT_SCHEDULER','date_range','Current Scheduler'), (3,'MONITORING_BOARDS','analytics','Monitoring Boards');
/*!40000 ALTER TABLE `event_notification_has_event_recipient` ENABLE KEYS */;
UNLOCK TABLES;

--changeset 2.0:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
-- -----------------------------------------------------
-- Table `opencelium`.`business_layout`
-- -----------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `bl_svg_items` ;
DROP TABLE IF EXISTS `bl_arrows` ;
DROP TABLE IF EXISTS `business_layout` ;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS `business_layout` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `connection_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`, `connection_id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_business_layout_connection_idx` (`connection_id` ASC),
  CONSTRAINT `fk_business_layout_connection`
    FOREIGN KEY (`connection_id`)
    REFERENCES `opencelium`.`connection` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `opencelium`.`bl_svg_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bl_svg_items` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `x_axis` INT NULL,
  `y_axis` INT NULL,
  `items` LONGTEXT NULL,
  `business_layout_id` INT NOT NULL,
  PRIMARY KEY (`id`, `business_layout_id`),
  INDEX `fk_svg_items_business_layout1_idx` (`business_layout_id` ASC),
  CONSTRAINT `fk_svg_items_business_layout1`
    FOREIGN KEY (`business_layout_id`)
    REFERENCES `business_layout` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `opencelium`.`bl_arrows`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bl_arrows` (
  `business_layout_id` INT NOT NULL,
  `arr_from` INT NOT NULL,
  `arr_to` INT NOT NULL,
  PRIMARY KEY (`business_layout_id`, arr_from, arr_to),
  UNIQUE KEY (`business_layout_id`,`arr_from`,`arr_to`),
  INDEX `fk_arrows_business_layout1_idx` (`business_layout_id`),
  CONSTRAINT `fk_arrows_business_layout1`
    FOREIGN KEY (`business_layout_id`)
    REFERENCES `business_layout` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

--changeset 2.0:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE detail ADD COLUMN IF NOT EXISTS repo_user varchar(200);
ALTER TABLE detail ADD COLUMN IF NOT EXISTS repo_password varchar(200);

--changeset 2.0:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE scheduler ADD COLUMN IF NOT EXISTS debug_mode tinyint(4);

--changeset 2.0:4 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
UPDATE scheduler SET debug_mode = 1;

--changeset 2.0:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connector ADD COLUMN IF NOT EXISTS ssl_cert tinyint(4);
UPDATE connector SET ssl_cert = 0;

--changeset 2.1:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connector ADD COLUMN IF NOT EXISTS timeout INT NOT NULL;

--changeset 2.1:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE detail CHANGE IF EXISTS organisation organization varchar(100);

--changeset 2.1:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE enhancement MODIFY expert_var TEXT;

--changeset 2.3:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE detail ADD COLUMN IF NOT EXISTS theme_sync tinyint(4);
UPDATE detail SET theme_sync = 0;

--changeset 3.0:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
SELECT VERSION();

--changeset 3.1:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
SELECT VERSION();

--changeset 3.1:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
UPDATE event_content SET language="en" where language="eng";

--changeset 3.1.1:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
SELECT VERSION();

--changeset 3.1.2:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
SELECT VERSION();

--changeset 3.2:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE data_aggregator (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    script LONGTEXT
);

--changeset 3.2:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE aggregator_argument (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data_aggregator_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    UNIQUE KEY unique_aggregator_argument_per_aggregator (data_aggregator_id, name),
    FOREIGN KEY (data_aggregator_id) REFERENCES data_aggregator(id)
);

--changeset 3.2:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE execution_argument (
    execution_id bigint(20) NOT NULL,
    aggregator_argument_id INT NOT NULL,
    arg_value VARCHAR(255),
    PRIMARY KEY (execution_id, aggregator_argument_id),
    FOREIGN KEY (execution_id) REFERENCES execution(id),
    FOREIGN KEY (aggregator_argument_id) REFERENCES aggregator_argument(id)
);

--changeset 3.2:4 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE data_aggregator ADD CONSTRAINT unique_name UNIQUE (name);

--changeset 3.2:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE data_aggregator ADD COLUMN is_active TINYINT(1);

--changeset 3.2:6 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE data_aggregator ALTER COLUMN is_active SET DEFAULT 1;

--changeset 3.2:7 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE webhook MODIFY COLUMN token LONGTEXT;

--changeset 3.2.1:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
SELECT VERSION();

--changeset 3.2.1:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connector CHANGE ssl_cert ssl_validation tinyint(4);

--changeset 3.2.1:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE event_recipient MODIFY COLUMN destination TEXT;

--changeset 4.0:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
SELECT VERSION();

--changeset 4.0:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table connection modify from_connector int null;

--changeset 4.0:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table connection modify to_connector int null;

--changeset 4.0:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table enhancement change name title varchar(255);

--changeset 4.0:6 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table enhancement change expert_code script text;

--changeset 4.0:7 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table enhancement change expert_var variables text;

--changeset 4.0:8 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table connection change name title varchar(128);

--changeset 4.0:9 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
create table connection_history(
    id BIGINT AUTO_INCREMENT PRIMARY KEY ,
    connection_id INT(11) NOT NULL ,
    user_id INT(11) NOT NULL,
    oc_version VARCHAR(10) NOT NULL,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    json_patch JSON,
    action  VARCHAR(45),
    FOREIGN KEY (connection_id) REFERENCES connection(id),
    FOREIGN KEY (user_id) REFERENCES user(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--changeset 4.0:10 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table enhancement change variables args text;

--changeset 4.0:11 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
alter table connection_history
    drop foreign key connection_history_ibfk_1;

alter table connection_history
    add constraint connection_history_ibfk_1
        foreign key (connection_id) references connection (id)
            on delete cascade;

--changeset 4.0:12 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    parent_category INT,
    FOREIGN KEY (parent_category) REFERENCES category(id)
);

----changeset 4.0:13 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
--ALTER TABLE connector CHANGE ssl_cert ssl_validation tinyint(4);

--changeset 4.0:13 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connection ADD COLUMN IF NOT EXISTS category_id INT NULL;

----changeset 4.0:15 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
--ALTER TABLE event_recipient MODIFY COLUMN destination TEXT;

--changeset 4.0:17 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE change_set_yml (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(20) NOT NULL,
    op VARCHAR(10) NOT NULL,
    value VARCHAR(255),
    path VARCHAR(255) NOT NULL,
    timestamp BIGINT NOT NULL,
    success bool default false
);
--changeset 4.0:18 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE secret_key_for_encoder (
    secret_key VARCHAR(1000) NOT NULL
);

--changeset 4.2:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE user
ADD COLUMN IF NOT EXISTS auth_method ENUM('LDAP', 'BASIC') NOT NULL DEFAULT 'BASIC',
ADD COLUMN IF NOT EXISTS totp_enabled BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS totp_secret_key VARCHAR(255);

--changeset 4.2:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
DROP TABLE IF EXISTS user_session;
CREATE TABLE IF NOT EXISTS user_session (
    session_id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id int(11) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP NOT NULL,
    last_accessed TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    attempts INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--changeset 4.2:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
LOCK TABLES `activity` READ, `user_session` WRITE;

INSERT INTO user_session (session_id, user_id, ip_address, user_agent, created_at, last_accessed, is_active, attempts)
SELECT
    UUID(),
    user_id,
    NULL,
    NULL,
    COALESCE(request_time, NOW()),
    COALESCE(request_time, NOW()),
    CASE WHEN is_locked = '1' THEN FALSE ELSE TRUE END,
    0
FROM activity;

UNLOCK TABLES;

--changeset 4.2:4 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
DROP TABLE IF EXISTS activity;

--changeset 4.2:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
INSERT INTO widget (id, name, icon, tooltipTranslationKey)
VALUES (4, 'SUBSCRIPTION_OVERVIEW', 'local_police', 'Subscription Overview');

--changeset 4.2:6 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
DROP TABLE IF EXISTS subscription;
DROP TABLE IF EXISTS activation_request;
DROP TABLE IF EXISTS operation_usage_history_detail;
DROP TABLE IF EXISTS operation_usage_history;
CREATE TABLE IF NOT EXISTS activation_request(
    id         VARCHAR(255) PRIMARY KEY,
    created_at TIMESTAMP    NOT NULL,
    hmac       VARCHAR(255) UNIQUE,
    ttl        INT UNSIGNED NOT NULL,
    status     ENUM ('PENDING', 'PROCESSED', 'EXPIRED') DEFAULT 'PENDING',
    active     BOOLEAN      NOT NULL,
    INDEX idx_hmac (hmac)
);

--changeset 4.2:7 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS subscription(
    id                    VARCHAR(255) PRIMARY KEY,
    subId                 VARCHAR(255) UNIQUE NOT NULL,
    license_id            VARCHAR(255) UNIQUE NOT NULL,
    created_at            TIMESTAMP    NOT NULL,
    license_key           VARCHAR(2048) NOT NULL,
    current_usage         BIGINT       NOT NULL,
    current_usage_hmac    VARCHAR(255) NOT NULL,
    active                BOOLEAN      NOT NULL,
    activation_request_id VARCHAR(255),
    FOREIGN KEY (activation_request_id) REFERENCES activation_request (id)
);

--changeset 4.2:8 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS operation_usage_history(
    id               BIGINT PRIMARY KEY AUTO_INCREMENT,
    subId            VARCHAR(255) NOT NULL,
    license_id       VARCHAR(255) NOT NULL,
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_usage      BIGINT       NOT NULL,
    connection_title VARCHAR(255) NOT NULL
);

--changeset 4.2:9 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS operation_usage_history_detail(
    id               BIGINT PRIMARY KEY AUTO_INCREMENT,
    start_date       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    operation_usage  BIGINT       NOT NULL,
    operation_usage_history_id BIGINT NOT NULL,
    FOREIGN KEY (operation_usage_history_id) REFERENCES operation_usage_history (id) ON DELETE CASCADE
);

--changeset 4.2:10 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS connection_editor_settings
(
    id                BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id           INT       NOT NULL,
    color_mode        VARCHAR(255) NOT NULL,
    process_text_size INT          NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE
);

--changeset 4.2:11 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE user CHANGE totp_enabled totp_process_completed BOOLEAN DEFAULT FALSE;

--changeset 4.2:12 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
DELETE FROM role_has_permission WHERE role_id=2 AND component_id=4;
INSERT IGNORE INTO role_has_permission (role_id,component_id,permission_id) VALUES (2,6,1),(2,6,2),(2,6,3),(2,6,4);

--changeset 4.2:13 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE operation_usage_history ADD COLUMN from_invoker VARCHAR(255) NOT NULL;
ALTER TABLE operation_usage_history ADD COLUMN to_invoker VARCHAR(255) NOT NULL;
ALTER TABLE operation_usage_history ADD COLUMN modified_at TIMESTAMP NOT NULL;

--changeset 4.2:14 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE user ADD COLUMN IF NOT EXISTS username VARCHAR(255) DEFAULT NULL;
ALTER TABLE user DROP PRIMARY KEY;
ALTER TABLE user ADD PRIMARY KEY (id);
ALTER TABLE user MODIFY email varchar(45) DEFAULT NULL;

--changeset 4.2:15 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connection ADD COLUMN IF NOT EXISTS oc_version VARCHAR(20) DEFAULT NULL;

--changeset 4.3:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS masking_rule (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rule_type ENUM ('REGEX', 'JSON_PATH', 'X_PATH') DEFAULT 'REGEX',
    expression TEXT,
    mask_pattern TEXT NOT NULL DEFAULT '[MASKED]',
    connection_id INT(11) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (connection_id) REFERENCES connection(id) ON DELETE CASCADE ON UPDATE CASCADE
);

--changeset 4.4:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE extra_ops (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    subscription_id VARCHAR(255) NOT NULL,
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,
    status ENUM('ACTIVE','EXPIRED','CONSUMED','PENDING') NOT NULL,
    current_ops_usage BIGINT NOT NULL,
    current_ops_usage_hmac TEXT NOT NULL,
    total_ops_usage BIGINT NOT NULL DEFAULT 0,
    total_ops_usage_hmac TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    generated_at BIGINT NOT NULL,
    CONSTRAINT fk_subscription
        FOREIGN KEY (subscription_id)
        REFERENCES subscription(id)
);

--changeset 4.5:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `role` MODIFY COLUMN `name` VARCHAR(255) NOT NULL;

--changeset 4.5:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `role` MODIFY COLUMN `description` VARCHAR(5000) DEFAULT NULL;

--changeset 4.5:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `scheduler` MODIFY COLUMN `title` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:4 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `detail` MODIFY COLUMN `department` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `user` MODIFY COLUMN `email` VARCHAR(255) NOT NULL;

--changeset 4.5:6 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `detail` MODIFY COLUMN `name` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:7 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `detail` MODIFY COLUMN `organization` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:8 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `detail` MODIFY COLUMN `phone_number` VARCHAR(30) DEFAULT NULL;

--changeset 4.5:9 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `detail` MODIFY COLUMN `surname` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:10 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `connection` MODIFY COLUMN `description` VARCHAR(5000) DEFAULT NULL;

--changeset 4.5:11 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `connection` MODIFY COLUMN `title` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:12 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `connector` MODIFY COLUMN `description` VARCHAR(5000) DEFAULT NULL;

--changeset 4.5:13 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `connector` MODIFY COLUMN `invoker` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:14 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `data_aggregator` MODIFY COLUMN `script` TEXT;

--changeset 4.5:15 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `enhancement` MODIFY COLUMN `script` TEXT DEFAULT NULL;

--changeset 4.5:16 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `event_content` MODIFY COLUMN `subject` VARCHAR(255) NOT NULL;

--changeset 4.5:17 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `event_content` MODIFY COLUMN `body` TEXT DEFAULT NULL;

--changeset 4.5:18 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `event_message` MODIFY COLUMN `name` VARCHAR(255) NOT NULL;

--changeset 4.5:19 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `event_notification` MODIFY COLUMN `name` VARCHAR(255) DEFAULT NULL;

--changeset 4.5:20 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE last_execution ADD COLUMN IF NOT EXISTS s_has_log tinyint(4) DEFAULT 0;
ALTER TABLE last_execution ADD COLUMN IF NOT EXISTS f_has_log tinyint(4) DEFAULT 0;

--changeset 4.5:21 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `enhancement` MODIFY COLUMN `script` TEXT DEFAULT NULL;

--changeset 4.5:22 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS invoker_sync (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoker_name TEXT NOT NULL,
    sp_invoker_file_name TEXT NOT NULL,
    invoker_content_hmac TEXT NOT NULL,
    manually_modified BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

--changeset 4.5:23 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE invoker_sync ADD COLUMN IF NOT EXISTS oc_invoker_file_name TEXT;

--changeset 4.5:24 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS online_sync_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    service VARCHAR(25) NOT NULL,
    details TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
