----------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table PERSONS
-----------------------------------------------------------------------

insert into persons
(id
,first_name
,last_name
,user_id
,email)
values
(nextval('PERSON_ID_SEQ')
,''
,'System User'
,'system'
,''
);

-----------------------------------------------------------------------------------
--  Purpose:  Create Controlled Vocabulary PLATE_GEOMETRY
-----------------------------------------------------------------------------------
insert into controlled_vocabularies 
       ( id
       , code
       , description
       , pers_id_registerer
       , is_managed_internally )
values  (nextval('CONTROLLED_VOCABULARY_ID_SEQ')
       , '.PLATE_GEOMETRY'
       , 'The geometry or dimensions of a plate'
       , (select id from persons where user_id ='system')
       , true);


-----------------------------------------------------------------------------------
--  Purpose:  Create Controlled Vocabulary Terms for PLATE_GEOMETRY
-----------------------------------------------------------------------------------
insert into controlled_vocabulary_terms 
       ( id
       , code
       , covo_id 
       , pers_id_registerer)
values  (nextval('CVTE_ID_SEQ')
       , '96_WELLS_8X12'
       , (select id from controlled_vocabularies where code = '.PLATE_GEOMETRY')
       , (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')
       , '384_WELLS_16X24'
       , (select id from controlled_vocabularies where code = '.PLATE_GEOMETRY')
       , (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')
       , '1536_WELLS_32X48'
       , (select id from controlled_vocabularies where code = '.PLATE_GEOMETRY')
       , (select id from persons where user_id ='system'));

-----------------------------------------------------------------------------------
--  Purpose:  Create Controlled Vocabulary STORAGE_FORMAT
-----------------------------------------------------------------------------------
insert into controlled_vocabularies 
       ( id
       , code
       , description
       , pers_id_registerer
       , is_managed_internally )
values  (nextval('CONTROLLED_VOCABULARY_ID_SEQ')
       , '.STORAGE_FORMAT'
       , 'The on-disk storage format of a data set'
       , (select id from persons where user_id ='system')
       , true);


-----------------------------------------------------------------------------------
--  Purpose:  Create Controlled Vocabulary Terms for STORAGE_FORMAT
-----------------------------------------------------------------------------------
insert into controlled_vocabulary_terms 
       ( id
       , code
       , covo_id 
       , pers_id_registerer )
values  (nextval('CVTE_ID_SEQ')
       , 'PROPRIETARY'
       , (select id from controlled_vocabularies where code = '.STORAGE_FORMAT')
       , (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')
       , 'BDS_DIRECTORY'
       , (select id from controlled_vocabularies where code = '.STORAGE_FORMAT')
       , (select id from persons where user_id ='system'));

----------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table MATERIAL_TYPES
-----------------------------------------------------------------------

insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'VIRUS'
,'Virus'
);

insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'CELL_LINE'
,'Cell Line or Cell Culture. The growing of cells under controlled conditions.'
);

insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'GENE'
,'Gene'
);
insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'OLIGO'
,'Oligo nucleotide'
);
insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'CONTROL'
,'Control of a control layout'
);

insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'BACTERIUM'
,'Bacterium'
);

insert into material_types
(id
,code
,description)
values 
(nextval('MATERIAL_TYPE_ID_SEQ')
,'COMPOUND'
,'Compound'
);

----------------------------------------------------------------------
--  Purpose:  Insert an initial set MATERIALS of type CONTROL
-----------------------------------------------------------------------

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'SCRAM'
,(select id from material_types where code = 'CONTROL')
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'GFP'
,(select id from material_types where code = 'CONTROL')
,(select id from persons where user_id = 'system')
);


----------------------------------------------------------------------
--  Purpose:  Insert an initial set MATERIALS of type VIRUS
-----------------------------------------------------------------------

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'SV40'
,(select id from material_types where code = 'VIRUS')
--,'Simian Virus 40'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'VSV'
,(select id from material_types where code = 'VIRUS') 
--,'Vesicular Stomatitis Virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'SFV'
,(select id from material_types where code = 'VIRUS')
--,'Semliki Forest Virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'ROTAV'
,(select id from material_types where code = 'VIRUS')
--,'Rotavirus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'RRV'
,(select id from material_types where code = 'VIRUS')
--,'Rhesus Rotavirus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'HSV1'
,(select id from material_types where code = 'VIRUS')
--,'Herpes Simplex Virus 1'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'EV1'
,(select id from material_types where code = 'VIRUS')
--,'Echovirus 1'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'MHV'
,(select id from material_types where code = 'VIRUS')
--,'Mouse Hepatitis Virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'FLU'
,(select id from material_types where code = 'VIRUS')
--,'Influenza A virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'AD3'
,(select id from material_types where code = 'VIRUS')
--,'Adenovirus 3'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'AD5'
,(select id from material_types where code = 'VIRUS')
--,'Adenovirus 5'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'VV'
,(select id from material_types where code = 'VIRUS')
--,'Vaccinia Virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'YFV'
,(select id from material_types where code = 'VIRUS')
--,'Yellow Fever Virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'DV'
,(select id from material_types where code = 'VIRUS')
--,'Dengue Virus 1'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'HRV2'
,(select id from material_types where code = 'VIRUS')
--,'Human respiratory virus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'RV'
,(select id from material_types where code = 'VIRUS')
--,'Rhinovirus'
,(select id from persons where user_id = 'system')
);

insert into materials
(  id
  ,code
  ,maty_id
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_ID_SEQ')
,'RSV'
,(select id from material_types where code = 'VIRUS')
--,'Respiratory Syncytial Virus'
,(select id from persons where user_id = 'system')
);


------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table DATA_TYPES
------------------------------------------------------------------

insert into data_types
(id
,code
,description)
values 
(nextval('DATA_TYPE_ID_SEQ')
,'VARCHAR'
,'Variable length character'
);

insert into data_types
(id
,code
,description)
values 
(nextval('DATA_TYPE_ID_SEQ')
,'INTEGER'
,'Integer'
);

insert into data_types
(id
,code
,description)
values 
(nextval('DATA_TYPE_ID_SEQ')
,'REAL'
,'Real number, i.e. an inexact, variable-precision numeric type'
);

insert into data_types
(id
,code
,description)
values 
(nextval('DATA_TYPE_ID_SEQ')
,'BOOLEAN'
,'An enumerated type with values True and False'
);

insert into data_types
(id
,code
,description)
values 
(nextval('DATA_TYPE_ID_SEQ')
,'TIMESTAMP'
,'Both date and time. Format: yyyy-mm-dd hh:mm:ss'
);

insert into data_types
(id
 ,code
 ,description)
 values 
 (nextval('DATA_TYPE_ID_SEQ')
 ,'CONTROLLEDVOCABULARY'
 ,'Controlled Vocabulary'
);





----------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table PROPERTY_TYPES
-----------------------------------------------------------------------

insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'DESCRIPTION'
,'A Description'
,'Description'
,(select id from data_types where code ='VARCHAR')
,(select id from persons where user_id ='system')
);

insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'GENE_SYMBOL'
,'Gene Symbol, e.g. BMP15'
,'Gene Symbol'
,(select id from data_types where code ='VARCHAR')
,(select id from persons where user_id ='system')
);

insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'NUCLEOTIDE_SEQUENCE'
,'A sequence of nucleotides'
,'Nucleotide Sequence'
,(select id from data_types where code ='VARCHAR')
,(select id from persons where user_id ='system')
);

insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'REFSEQ'
,'NCBI Reference Sequence code, applicable to sequences of type: DNA, RNA, protein'
,'RefSeq'
,(select id from data_types where code ='VARCHAR')
,(select id from persons where user_id ='system')
);

insert into property_types
(id
,code
,description
,label
,daty_id
,pers_id_registerer)
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'OFFSET'
,'Offset from the start of the sequence'
,'Offset'
,(select id from data_types where code ='INTEGER')
,(select id from persons where user_id ='system')
);

-----------------------------------------------------------------------------------
--  Purpose:  Create property type PLATE_GEOMETRY
-----------------------------------------------------------------------------------
insert into property_types
(id
,code
,description
,label
,daty_id
,covo_id
,pers_id_registerer
, is_managed_internally )
values 
(nextval('PROPERTY_TYPE_ID_SEQ')
,'.PLATE_GEOMETRY'
,'Plate Geometry'
,'Plate Geometry'
,(select id from data_types where code ='CONTROLLEDVOCABULARY')
,(select id from controlled_vocabularies where code ='.PLATE_GEOMETRY')
,(select id from persons where user_id ='system')
,true
);


----------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table SAMPLE_TYPES
----------------------------------------------------------------------

insert into sample_types
(id
,code
,description)
values 
(nextval('SAMPLE_TYPE_ID_SEQ')
,'MASTER_PLATE'
,'Master Plate'
);

insert into sample_types
(id
,code
,description)
values 
(nextval('SAMPLE_TYPE_ID_SEQ')
,'DILUTION_PLATE'
,'Dilution Plate'
);

insert into sample_types
(id
,code
,description)
values 
(nextval('SAMPLE_TYPE_ID_SEQ')
,'CELL_PLATE'
,'Cell Plate'
);

insert into sample_types
(id
,code
,description)
values 
(nextval('SAMPLE_TYPE_ID_SEQ')
,'REINFECT_PLATE'
,'Re-infection Plate'
);

insert into sample_types
(id
,code
,description)
values 
(nextval('SAMPLE_TYPE_ID_SEQ')
,'CONTROL_LAYOUT'
,'Control layout'
);

------------------------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table SAMPLE_TYPE_PROPERTY_TYPES
------------------------------------------------------------------------------------

   ---------------------------------
   --  Sample Type   MASTER_PLATE
   --  Property Type PLATE_GEOMETRY   
   ---------------------------------

insert into sample_type_property_types
(   id
   ,saty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('STPT_ID_SEQ')
   ,(select id from sample_types where code = 'MASTER_PLATE')
   ,(select id from property_types where code = '.PLATE_GEOMETRY')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);

   ---------------------------------
   --  Sample Type   CONTROL_LAYOUT
   --  Property Type PLATE_GEOMETRY   
   ---------------------------------

insert into sample_type_property_types
(   id
   ,saty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('STPT_ID_SEQ')
   ,(select id from sample_types where code = 'CONTROL_LAYOUT')
   ,(select id from property_types where code = '.PLATE_GEOMETRY')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);


------------------------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table MATERIAL_TYPE_PROPERTY_TYPES
------------------------------------------------------------------------------------

----
-- Note: we rely on DESCRIPTION to be present and internally_managed for all material types!
----

   -----------------------
   --  Material Type VIRUS
   -----------------------

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'VIRUS')
   ,(select id from property_types where code = 'DESCRIPTION')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);

   -----------------------
   --  Material Type BACTERIUM
   -----------------------

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'BACTERIUM')
   ,(select id from property_types where code = 'DESCRIPTION')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);

   -----------------------
   --  Material Type COMPOUND
   -----------------------

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'COMPOUND')
   ,(select id from property_types where code = 'DESCRIPTION')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);

   -----------------------
   --  Material Type GENE
   -----------------------

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'GENE')
   ,(select id from property_types where code = 'GENE_SYMBOL')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'GENE')
   ,(select id from property_types where code = 'DESCRIPTION')
   ,false
   ,true
   ,(select id from persons where user_id ='system')
);


   -----------------------
   --  Material Type OLIGO
   -----------------------

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'OLIGO')
   ,(select id from property_types where code = 'NUCLEOTIDE_SEQUENCE')
   ,true
   ,true
   ,(select id from persons where user_id ='system')
);

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'OLIGO')
   ,(select id from property_types where code = 'OFFSET')
   ,false
   ,true
   ,(select id from persons where user_id ='system')
);

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'OLIGO')
   ,(select id from property_types where code = 'DESCRIPTION')
   ,false
   ,true
   ,(select id from persons where user_id ='system')
);

   -------------------------
   --  Material Type CONTROL
   -------------------------

insert into material_type_property_types
(   id
   ,maty_id
   ,prty_id
   ,is_mandatory
   ,is_managed_internally
   ,pers_id_registerer
   )
values 
   (nextval('MTPT_ID_SEQ')
   ,(select id from material_types where code = 'CONTROL')
   ,(select id from property_types where code = 'DESCRIPTION')
   ,false
   ,true
   ,(select id from persons where user_id ='system')
);

----------------------------------------------------------------
--  Purpose:  Insert the mandatory DESCRIPTION for the materials 
--            of type VIRUS into the table MATERIAL_PROPERTIES 
----------------------------------------------------------------

insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'SV40')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Simian Virus 40'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
   ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'VSV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Vesicular Stomatitis Virus'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
   ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'SFV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Semliki Forest Virus'
,(select id from persons where user_id ='system')
);

insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'ROTAV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Rotavirus'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'RRV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Rhesus Rotavirus'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'HSV1')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Herpes Simplex Virus 1'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'EV1')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Echovirus 1'
,(select id from persons where user_id ='system')
);

insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'MHV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Mouse Hepatitis Virus'
,(select id from persons where user_id ='system')
);



insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'FLU')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Influenza A virus'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'AD3')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Adenovirus 3'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'AD5')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Adenovirus 5'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'VV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Vaccinia Virus'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'YFV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Yellow Fever Virus'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'DV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Dengue Virus 1'
,(select id from persons where user_id ='system')
);


insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'HRV2')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Human respiratory virus'
,(select id from persons where user_id ='system')
);



insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'RV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Rhinovirus'
,(select id from persons where user_id ='system')
);




insert into material_properties
(  id
  ,mate_id
  ,mtpt_id
  ,value
  ,pers_id_registerer
  )
values 
(nextval('MATERIAL_PROPERTY_ID_SEQ')
,(select mate.id from materials mate, material_types maty where maty.code = 'VIRUS' and mate.code = 'RSV')
,(select mtpt.id from material_type_property_types mtpt, material_types mt, property_types pt 
  where   mt.id   = mtpt.maty_id 
     and  pt.id   = mtpt.prty_id
     and  mt.code = 'VIRUS'
     and  pt.code = 'DESCRIPTION')
,'Respiratory Syncytial Virus'
,(select id from persons where user_id ='system')
);



-------------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table EXPERIMENT_TYPES
--------------------------------------------------------------------------

insert into experiment_types
(id
,code
,description)
values 
(nextval('EXPERIMENT_TYPE_ID_SEQ')
,'SIRNAHCS'
,'Small Interfering RNA High Content Screening'
);

----------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table PROCEDURE_TYPES
-----------------------------------------------------------------------

insert into procedure_types
(id
,code
,description
,is_data_acquisition)
values 
(nextval('PROCEDURE_TYPE_ID_SEQ')
,'UNKNOWN'
,'Unknown'
,false
);

insert into procedure_types
(id
,code
,description
,is_data_acquisition)
values 
(nextval('PROCEDURE_TYPE_ID_SEQ')
,'DATA_ACQUISITION'
,'Data Acquisition'
,true
);

insert into procedure_types
(id
,code
,description
,is_data_acquisition)
values 
(nextval('PROCEDURE_TYPE_ID_SEQ')
,'IMAGE_ANALYSIS'
,'Image Analysis'
,false
);

--------------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table OBSERVABLE_TYPES
--------------------------------------------------------------------------

insert into observable_types
(id
,code
,description)
values 
(nextval('OBSERVABLE_TYPE_ID_SEQ')
,'UNKNOWN'
,'Unknown'
);

insert into observable_types
(id
,code
,description)
values 
(nextval('OBSERVABLE_TYPE_ID_SEQ')
,'HCS_IMAGE'
,'High Content Screening Image'
);

insert into observable_types
(id
,code
,description)
values 
(nextval('OBSERVABLE_TYPE_ID_SEQ')
,'HCS_IMAGE_ANALYSIS_DATA'
,'Data derived from analysis of HCS images'
);

-------------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table FILE_FORMAT_TYPES
-------------------------------------------------------------------------

insert into file_format_types
(id
,code
,description)
values 
(nextval('FILE_FORMAT_TYPE_ID_SEQ')
,'TIFF'
,'TIFF File'
);

insert into file_format_types
(id
,code
,description)
values 
(nextval('FILE_FORMAT_TYPE_ID_SEQ')
,'3VPROPRIETARY'
,'Data Analysis 3V proprietary format'
);

insert into file_format_types
(id
,code
,description)
values 
(nextval('FILE_FORMAT_TYPE_ID_SEQ')
,'PLKPROPRIETARY'
,'Data Analysis Pelkmans group proprietary format'
);

---------------------------------------------------------------------
--  Purpose:  Insert an initial data set into the table LOCATOR_TYPES
---------------------------------------------------------------------

insert into locator_types
(id
,code
,description)
values 
(nextval('LOCATOR_TYPE_ID_SEQ')
,'RELATIVE_LOCATION'
,'Relative Location'
);



