--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
);

--changeset 4.8:1 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connection ADD COLUMN IF NOT EXISTS snapshot_id VARCHAR(30);

--changeset 4.8:2 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE connection ADD COLUMN IF NOT EXISTS revision INT;

--changeset 4.8:3 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
INSERT INTO widget (id, name, icon, tooltipTranslationKey) VALUES (5, 'METRICS_OVERVIEW', 'dashboard', 'Metrics Overview');

--changeset 4.8:4 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
CREATE TABLE IF NOT EXISTS password_reset_token (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT(11) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_used BOOLEAN DEFAULT FALSE,
    is_valid BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--changeset 4.8:5 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE widget_setting ADD CONSTRAINT uq_widget_setting_widget_user UNIQUE (widget_id, user_id);

--changeset 4.8:6 runOnChange:true stripComments:true splitStatements:true endDelimiter:;
INSERT IGNORE INTO widget_setting (
    x_axis,
    y_axis,
    width,
    height,
    min_width,
    min_height,
    widget_id,
    user_id
) VALUES
(0, 6, 6, 4, 6, 4, 1, 1),
(0, 2, 6, 4, 6, 4, 4, 1),
(0, 0, 12, 2, 12, 2, 5, 1),
(6, 2, 6, 4, 6, 4, 2, 1);

--changeset 4.8.1:1 stripComments:true splitStatements:true endDelimiter:;
ALTER TABLE `enhancement` DROP FOREIGN KEY `fk_enhancement_connection1`;
ALTER TABLE `enhancement` ADD CONSTRAINT `fk_enhancement_connection1` FOREIGN KEY (`connection_id`) REFERENCES `connection` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;