--====================================
-- Add Test Data
--====================================

-------------------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table CONTROLLED_VOCABULARIES
--
--            Create the controlled vocabulary ORGANISM.
-------------------------------------------------------------------------------

insert into controlled_vocabularies
(id
,code
,description
,pers_id)
values 
(nextval('CONTROLLED_VOCABULARY_ID_SEQ')
,'ORGANISM'
,'A controlled vocabulary for organism types, e.g. HUMAN, DOG etc.'
,(select id from persons where user_id ='system')
);

-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table CONTROLLED_VOCABULARY_TERMS
--
--            Create an initial set of the terms for the 
--            controlled vocabulary ORGANISM.
-----------------------------------------------------------------------------------

insert into controlled_vocabulary_terms
(id
,code
,covo_id
,pers_id)
values 
(nextval('CVTE_ID_SEQ')
,'HUMAN'
,(select id from controlled_vocabularies where code ='ORGANISM')
,(select id from persons where user_id ='system')
);

insert into controlled_vocabulary_terms
(id
,code
,covo_id
,pers_id)
values 
(nextval('CVTE_ID_SEQ')
,'FLY'
,(select id from controlled_vocabularies where code ='ORGANISM')
,(select id from persons where user_id ='system')
);

insert into controlled_vocabulary_terms
(id
,code
,covo_id
,pers_id)
values 
(nextval('CVTE_ID_SEQ')
,'E. COLI'
,(select id from controlled_vocabularies where code ='ORGANISM')
,(select id from persons where user_id ='system')
);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table DATA_TYPES
--
--            Create the data type CONTROLLEDVOCABULARY.
-----------------------------------------------------------------------------------
--insert into data_types
--(id
--,code
--,description)
--values 
--(nextval('DATA_TYPE_ID_SEQ')
--,'CONTROLLEDVOCABULARY'
--,'Controlled Vocabulary'
--);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table PROPERTY_TYPES
--
--            Create the property type ORGANISM and associate it with the 
--            data type CONTROLLEDVOCABULARY.
-----------------------------------------------------------------------------------
insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer
,covo_id)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'ORGANISM'
,'A living system, such as animal, plant, fungus or micro-organism'
,'Organism'
,(select id from data_types where code ='CONTROLLEDVOCABULARY')
,(select id from persons where user_id ='system')
,(select id from controlled_vocabularies where code ='ORGANISM')
);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table MATERIAL_TYPE_PROPERTY_TYPES
--
--            Associate the property type ORGANISM to the material type GENE.
-----------------------------------------------------------------------------------
insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'GENE')
   ,(select id from property_types where code = 'ORGANISM')
   ,true
   ,(select id from persons where user_id ='system')
);



-----------------------------------------------------------------------------------
--  Purpose:  Insert a material of type GENE into the table MATERIALS
--
-----------------------------------------------------------------------------------
insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'RANDY'
,(select id from material_types where code = 'GENE')
,(select id from persons where user_id = 'system')
);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table MATERIAL_PROPERTIES
--
--            Property Types: GENE_SYMBOL and ORGANISM are mandatory, so insert both.
--
--            Associate the property type ORGANISM to the controlled vocabulary term
--            HUMAN of the controlled vocabulary ORGANISM.
-----------------------------------------------------------------------------------
INSERT INTO material_properties(
              id
            , mate_id
            , mtpt_id
            , value
            , registration_timestamp
            , pers_id_registerer
            , cvte_id)
    VALUES (nextval('MATERIAL_PROPERTY_ID_SEQ')
            ,(select m.id 
              from materials m inner join material_types mt 
              on m.maty_id = mt.id 
              where m.code = 'RANDY' and mt.code = 'GENE')
            , (select id 
               from material_type_property_types
               where maty_id = (select id from material_types where code = 'GENE')
               and   prty_id = (select id from property_types where code = 'GENE_SYMBOL') )
            , 'GENE_SYMBOL_RANDY'
            , now()
            , (select id from persons where user_id = 'system')
            , null
            );
            

INSERT INTO material_properties(
              id
            , mate_id
            , mtpt_id
            , value
            , registration_timestamp
            , pers_id_registerer
            , cvte_id)
    VALUES (nextval('MATERIAL_PROPERTY_ID_SEQ')
            ,(select m.id 
              from materials m inner join material_types mt 
              on m.maty_id = mt.id 
              where m.code = 'RANDY' and mt.code = 'GENE')
            , (select id 
               from material_type_property_types
               where maty_id = (select id from material_types where code = 'GENE')
               and   prty_id = (select id from property_types where code = 'ORGANISM') )
            , null
            , now()
            , (select id from persons where user_id = 'system')
            , (select id 
               from controlled_vocabulary_terms 
               where code = 'HUMAN'
               and   covo_id = (select id from controlled_vocabularies where code = 'ORGANISM') ) 
            );
            

---------------------------------------------------------------------------------------------
--  Test if the expected 1 material property of material type GENE and property type ORGANISM 
--  whose property type value is given by the controlled vocabulary term HUMAN is found.
---------------------------------------------------------------------------------------------


select assert_equals('Number of Material Properties with controlled vocabulary term HUMAN', 1, count(*)) 
from material_properties mapr 
inner join controlled_vocabulary_terms cvte
   on mapr.cvte_id = cvte.id
inner join controlled_vocabularies covo 
   on covo.id = cvte.covo_id
where  cvte.code = 'HUMAN' 
   and covo.code = 'ORGANISM';

