
delete from cmn_message_instance_metadata;
select pg_catalog.setval('cmn_message_instance_metadata_id_seq', 1, true);
delete from pam_xcpd_deferred_transaction;
select pg_catalog.setval('pam_xcpd_deferred_transaction_id_seq', 1, true);
delete from cmn_transaction_instance;
select pg_catalog.setval('cmn_transaction_instance_id_seq', 1, true);
delete from cmn_message_instance;
select pg_catalog.setval('cmn_message_instance_id_seq', 1, true);
delete from sys_conf_type_usages where system_configuration_id in (select id from system_configuration where name not like 'SIMULATOR%');

update pat_patient set is_test_data = false where id in (select id from pam_test_data where is_shared = false);
-- in Patient Manager 9.11.2, column names have been reversed, when issue PAM-633 is fixed, this command shall be fixed, see the commented line below
delete from pam_test_data_restricted_domain where restricted_domain_id in (select id from pam_test_data where is_shared = false);
--delete from pam_test_data_restricted_domain where test_data_id in (select id from pam_test_data where is_shared = false);
delete from pam_test_data where is_shared = false;


--delete from sys_config where name not like 'SIMULATOR%';
delete from pam_sut_assigning_authorities where sut_preference_id in (select id from pam_systemundertest_preferences where system_under_test_id in (select id from system_configuration where name not like 'SIMULATOR%'));
delete from pam_systemundertest_preferences where system_under_test_id in (select id from system_configuration where name not like 'SIMULATOR%');
delete from system_configuration where name not like '%SIMULATOR%';

-- delete entries from XUA logs
delete from xua_log;

-- delete data related to the privacy of messages
delete from cmn_ip_address;
delete from cmn_company_details;

-- delete data related to patients that we do not need to keep
delete from pam_movement;
select pg_catalog.setval('pam_movement_id_seq', 1, true);
delete from pam_encounter;
select pg_catalog.setval('pam_encounter_id_seq', 1, true);
delete from pam_phone_number  where dtype = 'person_phone_number';
delete from pat_patient_address where dtype = 'person_address';
delete from pam_person;
select pg_catalog.setval('pam_person_id_seq', 1, true);
delete from pam_patient_history;
select pg_catalog.setval('pam_patient_history_id_seq', 1, true);


-- delete patients which have been created for test session but we want to keep
-- * patients which have to be loaded in SUT (actor = CONNECTATHON)
-- * patients (or patient identifiers) which are used in pre-defined queries
-- * patients which are used in tests (creator = pix_mgr or pix_src)


-- first mark the patients we want to keep after the clean update
alter table pat_patient add column keep boolean;
update pat_patient set keep = false;

update pat_patient set keep = true where is_test_data = true
                                         or actor_id = (select id from tf_actor where keyword = 'CONNECTATHON')
                                         or creator in ('pix_src', 'pix_mgr', null);
update pat_patient set keep = false where is_test_data = true and id not in (select patient_id from pam_test_data where dtype = 'patient');

delete from pam_patient_patient_identifier where patient_id in (select id from pat_patient where keep = false);

delete from pam_patient_identifier where id not in
                                         ((select patient_identifier_id from pam_test_data where dtype = 'patientIdentifier')
                                         union
                                          (select patient_identifier_id from pam_patient_patient_identifier));


delete from pat_patient_address where patient_id in (select id from pat_patient where keep = false );
delete from pam_phone_number where patient_id in (select id from pat_patient where keep = false);
delete from pat_patient where keep = false;

-- remove temporary column keep
alter table pat_patient drop column keep;

delete from pix_cross_reference where last_modifier != 'pix_mgr' and id not in (select cross_reference_id from pat_patient);

update pat_patient set still_active = true;

delete from pam_sut_assigning_authorities;
-- delete pam_hierarchic_designator collected by the tool but keep those which are owned by the tools and those which are used in test data
-- in Patient Manager 9.11.2, column names have been reversed, when issue PAM-633 is fixed, this command shall be fixed, see the commented line below
delete from pam_hierarchic_designator where
  is_tool_assigning_authority = false
  and (id not in ((select domain_id from pam_patient_identifier) union (select test_data_id from pam_test_data_restricted_domain)));
--delete from pam_hierarchic_designator where is_tool_assigning_authority = false or (id not in ((select domain_id from pam_patient_identifier) union (select restricted_domain_id from pam_test_data_restricted_domain)));