-------------------------------------------------------
--  Create function to test with, namely: ASSERT_EQUALS 
-------------------------------------------------------

CREATE OR REPLACE FUNCTION assert_equals(message VARCHAR, expected BIGINT, actual BIGINT) RETURNS INTEGER AS $$
   BEGIN
      IF expected != actual THEN
         RAISE EXCEPTION '%: expected:<%> but actual:<%>', message, expected, actual;
      END IF;
      RETURN NULL;
   END
$$ LANGUAGE 'plpgsql';


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

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

insert into controlled_vocabularies
(id
,code
,description
,pers_id_registerer
,is_internal_namespace
,is_managed_internally
,dbin_id)
values 
(nextval('CONTROLLED_VOCABULARY_ID_SEQ')
,'MOOD'
,'A controlled vocabulary for emotional states, e.g. HAPPY, SAD etc.'
,(select id from persons where user_id ='system')
,'T'
,'T'
,(select id from database_instances where code = 'SYSTEM_DEFAULT')
);

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

insert into controlled_vocabulary_terms
(id
,code
,covo_id
,pers_id_registerer)
values 
(nextval('CVTE_ID_SEQ')
,'HAPPY'
,(select id from controlled_vocabularies where code ='MOOD' and is_internal_namespace = 'T')
,(select id from persons where user_id ='system')
);

insert into controlled_vocabulary_terms
(id
,code
,covo_id
,pers_id_registerer)
values 
(nextval('CVTE_ID_SEQ')
,'SAD'
,(select id from controlled_vocabularies where code ='MOOD' and is_internal_namespace = 'T')
,(select id from persons where user_id ='system')
);

insert into controlled_vocabulary_terms
(id
,code
,covo_id
,pers_id_registerer)
values 
(nextval('CVTE_ID_SEQ')
,'ANGRY'
,(select id from controlled_vocabularies where code ='MOOD' and is_internal_namespace = 'T')
,(select id from persons where user_id ='system')
);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table PROPERTY_TYPES
--
--            Create the property type MOOD and associate it with the 
--            data type CONTROLLEDVOCABULARY.
-----------------------------------------------------------------------------------
insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer
,covo_id
,is_internal_namespace
,is_managed_internally
,dbin_id)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'MOOD'
,'Emotional states, e.g. HAPPY, SAD etc.'
,'Mood'
,(select id from data_types where code ='CONTROLLEDVOCABULARY')
,(select id from persons where user_id ='system')
,(select id from controlled_vocabularies where code ='MOOD')
,'T'
,'T'
,(select id from database_instances where code = 'SYSTEM_DEFAULT')
);


INSERT INTO experiment_types(
  id
, code
, description
, dbin_id)
VALUES (
 nextval('EXPERIMENT_TYPE_ID_SEQ')
, 'ALTERED_STATES'
, 'Experiments that induce altered emotional states'
,(select id from database_instances where code = 'SYSTEM_DEFAULT')
);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table EXPERIMENT_TYPE_PROPERTY_TYPES
--
--            Associate the property type MOOD to the experiment type ALTERED_STATES.
-----------------------------------------------------------------------------------
insert into experiment_type_property_types
(   id
   ,exty_id
   ,prty_id
   ,is_mandatory
   ,pers_id_registerer
   )
values 
   (nextval('ETPT_ID_SEQ')
   ,(select id from experiment_types where code = 'ALTERED_STATES')
   ,(select id from property_types where code = 'MOOD')
   ,true
   ,(select id from persons where user_id ='system')
);



-----------------------------------------------------------------------------------
--  Purpose:  Insert a Group
-----------------------------------------------------------------------------------
INSERT INTO groups(
              id
            , code
            , dbin_id
            , pers_id_registerer)
    VALUES (  nextval('GROUP_ID_SEQ')
            , 'GROUP_RANDY'
            ,(select id from database_instances where code = 'SYSTEM_DEFAULT')
            ,(select id from persons where user_id = 'system')
            );


-----------------------------------------------------------------------------------
--  Purpose:  Insert a project
-----------------------------------------------------------------------------------
INSERT INTO projects(
              id
            , code
            , grou_id)
    VALUES (  nextval('PROJECT_ID_SEQ')
            , 'PROJECT_RANDY'
            , (select id from groups where code = 'GROUP_RANDY'));


-----------------------------------------------------------------------------------
--  Purpose:  Insert experiments of type ALTERED_STATES into the table EXPERIMENTS
--
-----------------------------------------------------------------------------------
insert into experiments
(  id
  ,code
  ,exty_id
  ,pers_id_registerer
  ,proj_id
  )
values 
(nextval('EXPERIMENT_ID_SEQ')
,'ADRIAN'
,(select id from experiment_types where code = 'ALTERED_STATES')
,(select id from persons where user_id = 'system')
,(select id from projects where code = 'PROJECT_RANDY')
);

insert into experiments
(  id
  ,code
  ,exty_id
  ,pers_id_registerer
  ,proj_id
  )
values 
(nextval('EXPERIMENT_ID_SEQ')
,'RANDY'
,(select id from experiment_types where code = 'ALTERED_STATES')
,(select id from persons where user_id = 'system')
,(select id from projects where code = 'PROJECT_RANDY')
);


-----------------------------------------------------------------------------------
--  Purpose:  Insert into the table EXPERIMENT_PROPERTIES
--
--            Property Type: MOOD is mandatory, insert it.
--
--            For Experiment: ADRIAN
--            Associate the controlled vocabulary term HAPPY 
--            For Experiment: RANDY
--            Associate the controlled vocabulary term SAD
-----------------------------------------------------------------------------------

INSERT INTO experiment_properties(
              id
            , expe_id
            , etpt_id
            , value
            , registration_timestamp
            , pers_id_registerer
            , cvte_id)
    VALUES (nextval('EXPERIMENT_PROPERTY_ID_SEQ')
            ,(select e.id 
              from experiments e inner join experiment_types et 
              on e.exty_id = et.id 
              where e.code = 'ADRIAN' and et.code = 'ALTERED_STATES')
            , (select id 
               from experiment_type_property_types
               where exty_id = (select id from experiment_types where code = 'ALTERED_STATES')
               and   prty_id = (select id from property_types where code = 'MOOD') )
            , null
            , now()
            , (select id from persons where user_id = 'system')
            , (select id 
               from controlled_vocabulary_terms 
               where code = 'HAPPY'
               and   covo_id = (select id from controlled_vocabularies where code = 'MOOD'
                                and is_internal_namespace = 'T') ) 
            );
            
INSERT INTO experiment_properties(
              id
            , expe_id
            , etpt_id
            , value
            , registration_timestamp
            , pers_id_registerer
            , cvte_id)
    VALUES (nextval('EXPERIMENT_PROPERTY_ID_SEQ')
            ,(select e.id 
              from experiments e inner join experiment_types et 
              on e.exty_id = et.id 
              where e.code = 'RANDY' and et.code = 'ALTERED_STATES')
            , (select id 
               from experiment_type_property_types
               where exty_id = (select id from experiment_types where code = 'ALTERED_STATES')
               and   prty_id = (select id from property_types where code = 'MOOD') )
            , null
            , now()
            , (select id from persons where user_id = 'system')
            , (select id 
               from controlled_vocabulary_terms 
               where code = 'SAD'
               and   covo_id = (select id from controlled_vocabularies where code = 'MOOD'
                                and is_internal_namespace = 'T') ) 
            );
