----------------------------
--  Create 2 invalid samples 
----------------------------
insert into invalidations
(id
,pers_id_registerer
,reason)
values
(nextval('INVALIDATION_ID_SEQ')
,(select id from persons where user_id = 'system') 
,'Cell plate was contaminated!'
);

insert into samples
(
 id
,code
,saty_id
,pers_id_registerer 
,inva_id
,dbin_id)
values 
 (nextval('SAMPLE_ID_SEQ')
, 'CP-001-aa'
,(select id from sample_types where code = 'CELL_PLATE')
,(select id from persons where user_id = 'system') 
,currval('INVALIDATION_ID_SEQ')
,(select id from database_instances where code = 'SYSTEM_DEFAULT')
);

insert into invalidations
(id
,pers_id_registerer
,reason)
values
(nextval('INVALIDATION_ID_SEQ')
,(select id from persons where user_id = 'system') 
,'The cell plate was dropped in the lab!'
);

insert into samples
(
 id
,code
,saty_id
,pers_id_registerer 
,inva_id
,dbin_id
)
values 
 (nextval('SAMPLE_ID_SEQ')
, 'CP-002-aa'
,(select id from sample_types where code = 'CELL_PLATE')
,(select id from persons where user_id = 'system') 
,currval('INVALIDATION_ID_SEQ')
,(select id from database_instances where code = 'SYSTEM_DEFAULT')
);


----------------------------
--  Create 1 valid sample 
----------------------------

insert into samples
(
 id
,code
,saty_id
,pers_id_registerer 
,dbin_id
)
values 
 (nextval('SAMPLE_ID_SEQ')
, 'CP-003-aa'
,(select id from sample_types where code = 'CELL_PLATE')
,(select id from persons where user_id = 'system')
,(select id from database_instances where code = 'SYSTEM_DEFAULT') 
);


-----------------------------------------------------
--  Test if the expected 2 invalid samples are found
-----------------------------------------------------


select assert_equals('Number of invalidated Samples', 2, count(*)) 
from samples samp inner join invalidations inva
on samp.inva_id = inva.id;
