-- -----------------------------------------------------
-- Dropping tables ( One time )
-- -----------------------------------------------------

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS sdnctl.CONFIG_RESOURCE_ASSIGNMENT_DATA;
DROP TABLE IF EXISTS sdnctl.CONFIG_RESOURCE;
DROP TABLE IF EXISTS sdnctl.CONFIG_RESOURCE_RELATION;
DROP TABLE IF EXISTS sdnctl.CONFIG_TRANSACTION_LOG;

SET FOREIGN_KEY_CHECKS=1;

-- -----------------------------------------------------
-- table sdnctl.CONFIG_TRANSACTION_LOG
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS sdnctl.CONFIG_TRANSACTION_LOG (
   config_transaction_log_id  VARCHAR(50) NOT NULL,
   request_id                 VARCHAR(50)   NULL          DEFAULT NULL,
   message_type               VARCHAR(100)  NULL          DEFAULT NULL,
   creation_date              DATETIME      NOT NULL      DEFAULT CURRENT_TIMESTAMP,
   message                           LONGTEXT      NULL          DEFAULT NULL,
   PRIMARY KEY PK_CONFIG_TRANSACTION_LOG (config_transaction_log_id)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- -----------------------------------------------------
-- table sdnctl.CONFIG_RESOURCE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS sdnctl.CONFIG_RESOURCE (
   config_resource_id   VARCHAR(50)         NOT NULL,
   resource_id          VARCHAR(50)   NOT NULL,
   resource_type        VARCHAR(50)   NOT NULL,
   service_template_name   VARCHAR(50)      NULL          DEFAULT NULL,
   service_template_version   VARCHAR(50)      NULL          DEFAULT NULL,
   template_name        VARCHAR(50)   NOT NULL,
   recipe_name          VARCHAR(50)   NOT NULL,
   request_id           VARCHAR(50)   NOT NULL,
   resource_data        LONGTEXT      NULL          DEFAULT NULL,
   mask_data            LONGTEXT      NULL          DEFAULT NULL,
   created_date         DATETIME      NOT NULL      DEFAULT CURRENT_TIMESTAMP,
   status               VARCHAR(20)   NOT NULL,
   updated_by           VARCHAR(50)   NOT NULL,
   PRIMARY KEY PK_CONFIG_RESOURCE (config_resource_id),
   INDEX IX_CONFIG_RESOURCE (created_date desc)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- -----------------------------------------------------
-- table sdnctl.CONFIG_RESOURCE_RELATION
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS sdnctl.CONFIG_RESOURCE_RELATION (
   config_resource_relation_id VARCHAR(50) NOT NULL,
   resource_id VARCHAR(50) NOT NULL,
   related_resource_id VARCHAR(50) NOT NULL,
   relation_type VARCHAR(50) NOT NULL DEFAULT 'children',
   created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NOT NULL,
   PRIMARY KEY PK_CONFIG_RESOURCE_RELATION (config_resource_relation_id),
   INDEX IX_CONFIG_RESOURCE_RELATION (created_date desc),
   CONSTRAINT FK_CONFIG_RESOURCE_RELATION_TO_CONFIG_RESOURCE1
   FOREIGN KEY (resource_id)
   REFERENCES sdnctl.CONFIG_RESOURCE (config_resource_id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
   CONSTRAINT FK_CONFIG_RESOURCE_RELATION_TO_CONFIG_RESOURCE2
   FOREIGN KEY (related_resource_id)
   REFERENCES sdnctl.CONFIG_RESOURCE (config_resource_id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- -----------------------------------------------------
-- table sdnctl.CONFIG_RESOURCE_ASSIGNMENT_DATA
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS sdnctl.CONFIG_RESOURCE_ASSIGNMENT_DATA (
   config_resource_assignment_data_id VARCHAR(50) NOT NULL,
   config_resource_id    VARCHAR(50) NOT NULL,
   version INT(11) NOT NULL,
   updated_date    DATETIME         NOT NULL          DEFAULT CURRENT_TIMESTAMP,
   updated_by      VARCHAR(50)    NOT NULL,   
   template_key_name     VARCHAR(50)    NOT NULL,
   resource_name   VARCHAR(50)    NOT NULL,
   data_type       VARCHAR(100)    NOT NULL,
   entry_schema    VARCHAR(100)    NULL DEFAULT NULL,
   resource_value  LONGTEXT         NOT NULL,
   source   VARCHAR(50)    NOT NULL,
   status   VARCHAR(50)    NOT NULL,
   message  LONGTEXT         NOT NULL,
   PRIMARY KEY PK_CONFIG_RESOURCE (config_resource_assignment_data_id),
   INDEX IX_CONFIG_RESOURCE (updated_date desc),
   CONSTRAINT FK_CONFIG_RESOURCE_ASSIGNMENT_DATA
   FOREIGN KEY (config_resource_id)
   REFERENCES sdnctl.CONFIG_RESOURCE (config_resource_id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;