
USE catalogdb;
SET GLOBAL local_infile=ON;
LOAD XML LOCAL INFILE '/workflows/workflow_template.xml' 
    INTO TABLE workflow 
ROWS IDENTIFIED BY '<workflow>';
SELECT @work_flow_id := MAX(id) FROM workflow ;
SELECT @bpmn_file_name := ARTIFACT_NAME from workflow where id=@work_flow_id;
UPDATE `workflow` set `body` = LOAD_FILE(CONCAT("/workflows/",@bpmn_file_name) )
where `id` = @work_flow_id;


CREATE TABLE IF NOT EXISTS temp_wfd_vnf (
    NAME VARCHAR(200) NOT NULL PRIMARY KEY,
	VERSION VARCHAR(20) NOT NULL
);
DELETE FROM temp_wfd_vnf;
LOAD XML LOCAL INFILE '/workflows/workflow_template.xml' 
    INTO TABLE temp_wfd_vnf  
ROWS IDENTIFIED BY '<vnf>';
INSERT INTO VNF_RESOURCE(MODEL_UUID,MODEL_VERSION) select NAME,VERSION from temp_wfd_vnf;
select NAME into @vnf_resource_model_uuid from temp_wfd_vnf;
INSERT INTO vnf_resource_to_workflow(VNF_RESOURCE_MODEL_UUID,workflow_id) VALUES (@vnf_resource_model_uuid, @work_flow_id);


CREATE TABLE IF NOT EXISTS temp_wfd_activity_spec_sequence(
NAME VARCHAR(200) ,
VERSION VARCHAR(200),
SEQUENCE int);
DELETE FROM temp_wfd_activity_spec_sequence;
LOAD XML LOCAL INFILE '/workflows/workflow_template.xml' 
    INTO TABLE temp_wfd_activity_spec_sequence 
ROWS IDENTIFIED BY '<activity_spec_sequence>';
INSERT INTO workflow_activity_spec_sequence( WORKFLOW_ID,ACTIVITY_SPEC_ID,SEQ_NO)
select c.id, b.id as ACTIVITY_SPEC_ID,a.sequence 
from  temp_wfd_activity_spec_sequence a, activity_spec b, workflow c
 where c.ID=@work_flow_id and a.name =b.name;
 
CREATE TABLE IF NOT EXISTS temp_wfd_activity_spec_category (
 NAME VARCHAR(200)
 );
  DELETE FROM  temp_wfd_activity_spec_category;
 LOAD XML LOCAL INFILE '/workflows/workflow_template.xml' 
    INTO TABLE temp_wfd_activity_spec_category 
ROWS IDENTIFIED BY '<activity_spec_category>';
select NAME into @activity_spec_category_name from temp_wfd_activity_spec_category;
INSERT IGNORE INTO activity_spec_to_activity_spec_categories(ACTIVITY_SPEC_ID,ACTIVITY_SPEC_CATEGORIES_ID)
select a.activity_spec_id, b.id 
from workflow_activity_spec_sequence a, 
	(select id from activity_spec_categories where name=@activity_spec_category_name) b 
where a.workflow_id =@work_flow_id;

drop table temp_wfd_vnf;
drop table temp_wfd_activity_spec_sequence;
drop table temp_wfd_activity_spec_category;



