USE catalogdb;

DELETE from service where model_uuid in ( SELECT SERVICE_MODEL_UUID
FROM service_to_resource_customizations
GROUP BY
SERVICE_MODEL_UUID, resource_model_customization_uuid
HAVING COUNT(*) > 1);
 

CREATE TABLE IF NOT EXISTS external_service_to_internal_model_mapping (
id INT(11) NOT NULL, 
SERVICE_NAME VARCHAR(200) NOT NULL,
PRODUCT_FLAVOR VARCHAR(200) NULL,
SUBSCRIPTION_SERVICE_TYPE VARCHAR(200) NOT NULL,
SERVICE_MODEL_UUID VARCHAR(200) NOT NULL, 
PRIMARY KEY (id), 
UNIQUE INDEX UK_external_service_to_internal_model_mapping
(SERVICE_NAME ASC, PRODUCT_FLAVOR ASC, SERVICE_MODEL_UUID ASC));

CREATE TABLE IF NOT EXISTS `collection_resource` (
 MODEL_UUID varchar(200) NOT NULL,
 MODEL_NAME varchar(200) NOT NULL, 
 MODEL_INVARIANT_UUID varchar(200) NOT NULL,
 MODEL_VERSION varchar(20) NOT NULL, 
 TOSCA_NODE_TYPE varchar(200) NOT NULL,
 DESCRIPTION varchar(200),  
 CREATION_TIMESTAMP datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`MODEL_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `collection_resource_customization` (
 MODEL_CUSTOMIZATION_UUID varchar(200) NOT NULL,
 MODEL_INSTANCE_NAME varchar(200) NOT NULL,
 ROLE varchar(200) NOT NULL,
 PRIMARY_TYPE varchar(200) NOT NULL, 
 FUNCTION varchar(200) NOT NULL, 
 SUBINTERFACE_NETWORK_QUANTITY INT, 
 COLLECTION_RESOURCE_TYPE varchar(200) NOT NULL,
 CREATION_TIMESTAMP datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CR_MODEL_UUID varchar(200) NOT NULL,
 PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `instance_group` (
 MODEL_UUID varchar(200) NOT NULL,
 MODEL_NAME varchar(200) NOT NULL,
 MODEL_INVARIANT_UUID varchar(200) NOT NULL,
 MODEL_VERSION varchar(20) NOT NULL,
 TOSCA_NODE_TYPE varchar(200) DEFAULT NULL,
 ROLE varchar(200) NOT NULL,
 PRIMARY_TYPE varchar(200) NOT NULL, 
 FUNCTION varchar(200) NOT NULL, 
 DESCRIPTION varchar(200),  
 CREATION_TIMESTAMP datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CR_MODEL_UUID varchar(200) NOT NULL,
 INSTANCE_GROUP_TYPE varchar(200) NOT NULL,
  PRIMARY KEY (`MODEL_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

 CREATE TABLE IF NOT EXISTS `catalogdb`.`configuration` 
 ( `MODEL_UUID` VARCHAR(200) NOT NULL, 
 `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL, 
 `MODEL_VERSION` VARCHAR(20) NOT NULL, 
 `MODEL_NAME` VARCHAR(200) NOT NULL, 
 `TOSCA_NODE_TYPE` VARCHAR(200) NOT NULL, 
 `DESCRIPTION` VARCHAR(1200) NULL, 
 `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`MODEL_UUID`)) 
 ENGINE = InnoDB AUTO_INCREMENT = 20654 
 DEFAULT CHARACTER SET = latin1;
 
 CREATE TABLE IF NOT EXISTS `catalogdb`.`service_proxy` (
 `MODEL_UUID` VARCHAR(200) NOT NULL,
 `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL,
 `MODEL_VERSION` VARCHAR(20) NOT NULL,
 `MODEL_NAME` VARCHAR(200) NOT NULL,
 `DESCRIPTION` VARCHAR(1200) NULL,
 `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`MODEL_UUID`)) 
 ENGINE = InnoDB AUTO_INCREMENT = 20654
 DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS `service_proxy_customization` (
`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL,
`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL,
`TOSCA_NODE_TYPE` VARCHAR(200) NOT NULL,
`SOURCE_SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL,
`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`SERVICE_PROXY_MODEL_UUID` VARCHAR(200) NOT NULL,
PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`),
INDEX `fk_service_proxy_customization__service_proxy1_idx` (`SERVICE_PROXY_MODEL_UUID` ASC),
INDEX `fk_service_proxy_customization__service1_idx` (`SOURCE_SERVICE_MODEL_UUID` ASC), 
CONSTRAINT`fk_spr_customization__service_proxy_resource1` 
FOREIGN KEY (`SERVICE_PROXY_MODEL_UUID`) REFERENCES `catalogdb`.`service_proxy` (`MODEL_UUID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_service_proxy_resource_customization__service1` 
FOREIGN KEY (`SOURCE_SERVICE_MODEL_UUID`) REFERENCES `catalogdb`.`service`
(`MODEL_UUID`) ON DELETE CASCADE ON UPDATE CASCADE) 
ENGINE = InnoDB
AUTO_INCREMENT = 20654 
DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS `catalogdb`.`configuration_customization` (
`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, 
`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL,
`CONFIGURATION_TYPE` VARCHAR(200) NULL,
`CONFIGURATION_ROLE` VARCHAR(200) NULL,
`CONFIGURATION_FUNCTION` VARCHAR(200) NULL,
`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`CONFIGURATION_MODEL_UUID` VARCHAR(200) NOT NULL,
`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NULL, 
`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NULL, 
PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), 
INDEX `fk_configuration_customization__configuration_idx` (`CONFIGURATION_MODEL_UUID` ASC), 
INDEX `fk_configuration_customization__service_proxy_customization_idx`
(`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` ASC), 
INDEX `fk_configuration_customization__configuration_customization_idx`
(`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` ASC), 
CONSTRAINT `fk_configuration_resource_customization__configuration_resour1`
FOREIGN KEY (`CONFIGURATION_MODEL_UUID`) REFERENCES `catalogdb`.`configuration` (`MODEL_UUID`)
ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT `fk_configuration_customization__service_proxy_customization1` FOREIGN
KEY (`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID`) REFERENCES
`catalogdb`.`service_proxy_customization` (`MODEL_CUSTOMIZATION_UUID`)
ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT
`fk_configuration_customization__configuration_customization1` FOREIGN
KEY (`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID`) REFERENCES
`catalogdb`.`configuration_customization` (`MODEL_CUSTOMIZATION_UUID`)
ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT =20654 
DEFAULT CHARACTER SET = latin1;


CREATE TABLE `service_proxy_customization_to_service` (
  `SERVICE_MODEL_UUID` varchar(200) NOT NULL,
  `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL,
  PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `configuration_customization_to_service` (
  `SERVICE_MODEL_UUID` varchar(200) NOT NULL,
  `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL,
  PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `collection_resource_customization_to_service` (
  `SERVICE_MODEL_UUID` varchar(200) NOT NULL,
  `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL,
  PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `network_resource_customization_to_service` (
  `SERVICE_MODEL_UUID` varchar(200) NOT NULL,
  `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL,
  PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vnf_resource_customization_to_service` (
  `SERVICE_MODEL_UUID` varchar(200) NOT NULL,
  `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL,
  PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `allotted_resource_customization_to_service` (
  `SERVICE_MODEL_UUID` varchar(200) NOT NULL,
  `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL,
  PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;




CREATE TABLE IF NOT EXISTS `collection_network_resource_customization` (
`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL,
`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL,
`NETWORK_TECHNOLOGY` VARCHAR(45) NULL,
`NETWORK_TYPE` VARCHAR(45) NULL,
`NETWORK_ROLE` VARCHAR(200) NULL,
`NETWORK_SCOPE` VARCHAR(45) NULL,
`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`NETWORK_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL, `INSTANCE_GROUP_MODEL_UUID` VARCHAR(200) NULL,
`CRC_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, PRIMARY KEY
(`MODEL_CUSTOMIZATION_UUID`, `CRC_MODEL_CUSTOMIZATION_UUID`),
INDEX `fk_collection_net_resource_customization__network_resource1_idx`
(`NETWORK_RESOURCE_MODEL_UUID` ASC), INDEX
`fk_collection_net_resource_customization__instance_group1_idx`
(`INSTANCE_GROUP_MODEL_UUID` ASC), INDEX
`fk_col_net_res_customization__collection_res_customization_idx`
(`CRC_MODEL_CUSTOMIZATION_UUID` ASC), CONSTRAINT
`fk_collection_net_resource_customization__network_resource10` FOREIGN
KEY (`NETWORK_RESOURCE_MODEL_UUID`) REFERENCES
`catalogdb`.`network_resource` (`MODEL_UUID`) ON DELETE CASCADE ON
UPDATE CASCADE, CONSTRAINT
`fk_collection_net_resource_customization__instance_group10` FOREIGN KEY
(`INSTANCE_GROUP_MODEL_UUID`) REFERENCES `instance_group`
(`MODEL_UUID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT
`fk_collection_network_resource_customization__collection_reso1` FOREIGN
KEY (`CRC_MODEL_CUSTOMIZATION_UUID`) REFERENCES
`collection_resource_customization`
(`MODEL_CUSTOMIZATION_UUID`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE
= InnoDB DEFAULT CHARACTER SET = latin1;


CREATE TABLE IF NOT EXISTS `northbound_request_ref_lookup` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`REQUEST_SCOPE` VARCHAR(200) NOT NULL,
`MACRO_ACTION` VARCHAR(200) NOT NULL,
`ACTION` VARCHAR(200) NOT NULL,
`IS_ALACARTE` TINYINT(1) NOT NULL DEFAULT 0,
`MIN_API_VERSION` DOUBLE NOT NULL,
`MAX_API_VERSION` DOUBLE NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `UK_northbound_request_ref_lookup` (`MIN_API_VERSION` ASC, `REQUEST_SCOPE` ASC, `ACTION` ASC, `IS_ALACARTE` ASC, `MACRO_ACTION` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS `orchestration_flow_reference` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`COMPOSITE_ACTION` VARCHAR(200) NOT NULL,
`SEQ_NO` INT(11) NOT NULL,
`FLOW_NAME` VARCHAR(200) NOT NULL,
`FLOW_VERSION` DOUBLE NOT NULL,
`NB_REQ_REF_LOOKUP_ID` INT(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_orchestration_flow_reference__northbound_req_ref_look_idx` (`NB_REQ_REF_LOOKUP_ID` ASC),
UNIQUE INDEX `UK_orchestration_flow_reference` (`COMPOSITE_ACTION` ASC, `FLOW_NAME` ASC, `SEQ_NO` ASC, `NB_REQ_REF_LOOKUP_ID` ASC),
CONSTRAINT `fk_orchestration_flow_reference__northbound_request_ref_look1` 
FOREIGN KEY (`NB_REQ_REF_LOOKUP_ID`) REFERENCES `northbound_request_ref_lookup` (`id`) 
ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = InnoDB DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS `rainy_day_handler_macro` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`FLOW_NAME` VARCHAR(200) NOT NULL,
`SERVICE_TYPE` VARCHAR(200) NOT NULL,
`VNF_TYPE` VARCHAR(200) NOT NULL,
`ERROR_CODE` VARCHAR(200) NOT NULL,
`WORK_STEP` VARCHAR(200) NOT NULL,
`POLICY` VARCHAR(200) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;