DROP TABLE "om_dicom_message_attributesforcommandset";
DROP TABLE "om_dicom_message_om_dicom_validation_result";
DROP TABLE "om_dicom_validation_result";
DROP TABLE "om_dicom_message";
DROP TABLE "om_scp_logger";

INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'message_permanent_link', 'http://localhost:8380/OrderManager/messages/messageDisplay.seam?id=');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'NUMBER_OF_ITEMS_PER_PAGE', '20');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'hl7fhir_xml_validation_xsl_location', 'http://localhost/xsl/fhirXmlDetailedResult.xsl');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'hl7fhir_json_validation_xsl_location', 'http://localhost/xsl/fhirJsonDetailedResult.xsl');
UPDATE validation_parameters SET validator_type = 0;
UPDATE validation_parameters SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'RAD') WHERE (transaction_id IN (select id from tf_transaction where keyword LIKE 'RAD%'));
UPDATE validation_parameters SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'LAB') WHERE (transaction_id IN (select id from tf_transaction where keyword LIKE 'LAB%'));
UPDATE validation_parameters SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'EYE') WHERE (transaction_id IN (select id from tf_transaction where keyword LIKE 'EYE%'));
UPDATE validation_parameters SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'TRO') WHERE (transaction_id IN (select id from tf_transaction where keyword LIKE 'TRO%'));
update validation_parameters set domain_id = (select id from tf_domain where keyword = 'EYE') where profile_oid like '%.19%' and domain_id = (select id from tf_domain where keyword = 'RAD');
update validation_parameters set domain_id = (select id from tf_domain where keyword = 'EYE') where profile_oid = '1.3.6.1.4.12559.11.1.1.200';

INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'radiology_enabled', 'true');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'laboratory_enabled', 'true');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'eyecare_enabled', 'true');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'hl7v3_validation_xsl_location', 'http://localhost/xsl/commonValidatorDetailedResult.xsl');

SELECT pg_catalog.setval('tf_domain_id_seq', (select max(id) from tf_domain), true);
INSERT INTO tf_domain(id, description, keyword, name) values (nextval('tf_domain_id_seq'), 'Saudi Arabia eHealth Exchange', 'SeHE', 'SeHE');
SELECT pg_catalog.setval('tf_actor_id_seq', (select max(id) from tf_actor), true);
INSERT INTO tf_actor(id, description, keyword, name, can_act_as_responder) values (nextval('tf_actor_id_seq'), 'Creator', 'TRO_CREATOR', 'TRO Creator', true);
INSERT INTO tf_actor(id, description, keyword, name, can_act_as_responder) values (nextval('tf_actor_id_seq'), 'Forwarder', 'TRO_FORWARDER', 'TRO Forwarder', true);
INSERT INTO tf_actor(id, description, keyword, name, can_act_as_responder) values (nextval('tf_actor_id_seq'), 'Fulfiller', 'TRO_FULFILLER', 'TRO Fulfiller', true);
INSERT INTO tf_actor(id, description, keyword, name, can_act_as_responder) values (nextval('tf_actor_id_seq'), 'Label Broker', 'LB', 'Label Broker', true);
INSERT INTO tf_actor(id, description, keyword, name, can_act_as_responder) values (nextval('tf_actor_id_seq'), 'Label Information Provider', 'LIP', 'Label Information Provider', true);

SELECT pg_catalog.setval('tf_transaction_id_seq', (select max(id) from tf_transaction), true);
SELECT pg_catalog.setval('affinity_domain_id_seq', (select max(id) from affinity_domain), true);
INSERT INTO tf_transaction(id, description, keyword, name) values (nextval('tf_transaction_id_seq'), 'Query Modality Worklist', 'RAD-5', 'Query Modality Worklist');
INSERT INTO tf_transaction(id, description, keyword, name) values (nextval('tf_transaction_id_seq'), 'Tele-Radiology Orders', 'KSA-TRO', 'Tele-Radiology Orders');
INSERT INTO tf_transaction(id, description, keyword, name) values (nextval('tf_transaction_id_seq'), 'Label Delivery Request', 'LAB-61', 'Label Delivery Request');
INSERT INTO tf_transaction(id, description, keyword, name) values (nextval('tf_transaction_id_seq'), 'Query for Label Delivery', 'LAB-62', 'Query for Label Delivery');
INSERT INTO tf_transaction(id, description, keyword, name) values (nextval('tf_transaction_id_seq'), 'Labels & Containers delivered', 'LAB-63', 'Labels & Containers delivered');
INSERT INTO affinity_domain(id, description, keyword, label_to_display) values (nextval('affinity_domain_id_seq'), 'SeHE', 'SeHE', 'SeHE');

INSERT INTO validation_parameters (id, message_type, profile_oid, actor_id, transaction_id, domain_id, validator_type) VALUES (nextval('ort_validation_parameters_id_seq'), 'OML^O33^OML_O33', '1.3.6.1.4.12559.11.1.1.136', (select id from tf_actor where keyword = 'LIP'), (select id from tf_transaction where keyword = 'LAB-61'),(select id from tf_domain where keyword = 'LAB'), 0);
INSERT INTO validation_parameters (id, message_type, profile_oid, actor_id, transaction_id, domain_id, validator_type) VALUES (nextval('ort_validation_parameters_id_seq'), 'ORL^O34^ORL_O34', '1.3.6.1.4.12559.11.1.1.134', (select id from tf_actor where keyword = 'LB'), (select id from tf_transaction where keyword = 'LAB-61'),(select id from tf_domain where keyword = 'LAB'), 0);
INSERT INTO validation_parameters (id, message_type, profile_oid, actor_id, transaction_id, domain_id, validator_type) VALUES (nextval('ort_validation_parameters_id_seq'), 'QBP^SLI^QBP_Q11', '1.3.6.1.4.12559.11.1.1.135', (select id from tf_actor where keyword = 'LB'), (select id from tf_transaction where keyword = 'LAB-62'),(select id from tf_domain where keyword = 'LAB'), 0);
INSERT INTO validation_parameters (id, message_type, profile_oid, actor_id, transaction_id, domain_id, validator_type) VALUES (nextval('ort_validation_parameters_id_seq'), 'RSP^SLI^RSP_K11', '1.3.6.1.4.12559.11.1.1.137', (select id from tf_actor where keyword = 'LIP'), (select id from tf_transaction where keyword = 'LAB-62'),(select id from tf_domain where keyword = 'LAB'), 0);
INSERT INTO validation_parameters (id, message_type, profile_oid, actor_id, transaction_id, domain_id, validator_type) VALUES (nextval('ort_validation_parameters_id_seq'), 'OML^O33^OML_O33', '1.3.6.1.4.12559.11.1.1.171', (select id from tf_actor where keyword = 'LB'), (select id from tf_transaction where keyword = 'LAB-63'),(select id from tf_domain where keyword = 'LAB'), 0);
INSERT INTO validation_parameters (id, message_type, profile_oid, actor_id, transaction_id, domain_id, validator_type) VALUES (nextval('ort_validation_parameters_id_seq'), 'ORL^O34^ORL_O34', '1.3.6.1.4.12559.11.1.1.172', (select id from tf_actor where keyword = 'LIP'), (select id from tf_transaction where keyword = 'LAB-63'),(select id from tf_domain where keyword = 'LAB'), 0);
ALTER TABLE hl7_simulator_responder_configuration DROP COLUMN affinity_domain_id;

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-2'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE_EYE'), 'Order Filler', 'EYE_RAD2');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-2'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_RAD'), 'Order Filler', 'RAD_RAD2');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-3'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_EYE'), 'Order Placer', 'EYE_RAD3');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-3'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_RAD'), 'Order Placer', 'RAD_RAD3');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-4'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_EYE'), 'Image/Report Manager', 'EYE_RAD4');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-4'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_RAD'), 'Image/Report Manager', 'RAD_RAD4');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-13'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_EYE'), 'Image/Report Manager', 'EYE_RAD13');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-13'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_RAD'), 'Image/Report Manager', 'RAD_RAD13');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-1'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Order Filler', 'LAB_OF_LAB1');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-2'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Order Filler', 'LAB_OF_LAB2');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-1'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Order Placer', 'LAB_OP_LAB1');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-2'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Order Placer', 'LAB_OP_LAB2');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-3'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Order Result Tracker', 'LAB_ORT');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-4'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Automation Manager', 'LAB_AM');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-5'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Order Filler', 'LAB_OF_LAB5');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-27'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Analyzer Manager', 'LAB_AN_MGR_LAB27');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-28'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Analyzer', 'LAB_ANALYZER');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-29'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Analyzer Manager', 'LAB_AN_MGR_LAB29');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'KSA-TRO'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'SeHE'), 'TRO Creator', 'KSA_TRO_CREATOR');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'KSA-TRO'), (SELECT id
                                                               FROM affinity_domain
                                                               WHERE keyword = 'SeHE'), 'TRO Fulfiller', 'KSA_TRO_FULFILLER');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'KSA-TRO'), (SELECT id
                                                               FROM affinity_domain
                                                               WHERE keyword = 'SeHE'), 'TRO Forwarder', 'KSA_TRO_FORWARDER');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-5'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_EYE'), 'Order Filler', 'EYE_RAD5');

INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-5'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_RAD'), 'Order Filler', 'RAD_RAD5');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-61'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Label Broker', 'LAB_LB');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-62'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE_LAB'), 'Label Information Provider', 'LAB_LAB62');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'LAB-63'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE_LAB'), 'Label Information Provider', 'LAB_LAB63');


ALTER TABLE public.system_configuration ALTER COLUMN url DROP NOT NULL;
UPDATE app_configuration SET variable = 'hl7v2_xsl_location' WHERE variable = 'xsl_location';

ALTER TABLE cmn_message_instance ADD COLUMN hl7_message_id INT;
ALTER TABLE cmn_transaction_instance ADD COLUMN hl7_message_id INT;
ALTER TABLE cmn_transaction_instance ADD COLUMN affinitydomain_id INT;
ALTER TABLE cmn_message_instance ADD COLUMN facility VARCHAR(255);
ALTER TABLE cmn_message_instance ADD COLUMN application VARCHAR(255);
ALTER TABLE cmn_message_instance ADD COLUMN request BOOLEAN;
-- insert request
INSERT INTO cmn_message_instance (id, content, facility, application, type, validation_detailed_result, validation_status, issuing_actor, hl7_message_id, request)
  SELECT
    nextval('cmn_message_instance_id_seq'),
    convert_to(sent_message_content, 'UTF8'),
    sending_facility,
    sending_application,
    sent_message_type,
    convert_to(sent_evsc_validation, 'UTF-8'),
    sent_message_status,
    sendingactor_id,
    id,
    TRUE
  FROM hl7_message;
-- insert response
INSERT INTO cmn_message_instance (id, content, facility, application, type, validation_detailed_result, validation_status, issuing_actor, hl7_message_id, request)
  SELECT
    nextval('cmn_message_instance_id_seq'),
    convert_to(received_message_content, 'UTF8'),
    receiving_facility,
    receiving_application,
    ack_message_type,
    convert_to(received_evsc_validation, 'UTF8'),
    received_message_status,
    receivingactor_id,
    id,
    FALSE
  FROM hl7_message;
-- insert transaction
INSERT INTO cmn_transaction_instance (id, transaction_id, timestamp, is_visible, simulated_actor_id, domain_id, username, request_id, response_id, hl7_message_id, affinitydomain_id)
  SELECT
    nextval('cmn_transaction_instance_id_seq'),
    transaction_id,
    timestamp,
    is_visible,
    simulatedactor_id,
    1,
    sut_ip,
    null,
    null,
    id,
    affinitydomain_id
  FROM hl7_message;
-- link messages to transaction instances
CREATE OR REPLACE FUNCTION link_messages_to_transaction()
  RETURNS VOID AS
$BODY$
DECLARE msg RECORD;
BEGIN
  FOR msg IN SELECT *
             FROM cmn_message_instance
             WHERE request = TRUE AND hl7_message_id IS NOT NULL
             LIMIT 10000
  LOOP
    UPDATE cmn_transaction_instance
    SET request_id = msg.id
    WHERE hl7_message_id = msg.hl7_message_id;
    UPDATE cmn_message_instance
    SET hl7_message_id = NULL
    WHERE id = msg.id;
  END LOOP;
  FOR msg IN SELECT *
             FROM cmn_message_instance
             WHERE request = FALSE AND hl7_message_id IS NOT NULL
             LIMIT 10000
  LOOP
    UPDATE cmn_transaction_instance
    SET response_id = msg.id
    WHERE hl7_message_id = msg.hl7_message_id;
    UPDATE cmn_message_instance
    SET hl7_message_id = NULL
    WHERE id = msg.id;
  END LOOP;
  RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION update_all()
  RETURNS VOID AS
$BODY$
BEGIN
  WHILE (SELECT count(id) > 0
         FROM cmn_message_instance
         WHERE hl7_message_id IS NOT NULL) LOOP
    PERFORM link_messages_to_transaction();
  END LOOP;
END
$BODY$
LANGUAGE 'plpgsql';
SELECT update_all();

UPDATE cmn_message_instance
SET issuer = concat(facility, ' ', application)
WHERE facility NOTNULL AND application NOTNULL;
-- drop columns
ALTER TABLE cmn_message_instance DROP COLUMN hl7_message_id;
ALTER TABLE cmn_transaction_instance DROP COLUMN hl7_message_id;
ALTER TABLE cmn_message_instance DROP COLUMN facility;
ALTER TABLE cmn_message_instance DROP COLUMN application;
ALTER TABLE cmn_message_instance DROP COLUMN request;
UPDATE cmn_transaction_instance SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'RAD') WHERE (affinitydomain_id = (SELECT id from affinity_domain where keyword = 'IHE_RAD'));
UPDATE cmn_transaction_instance SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'EYE') WHERE (affinitydomain_id = (SELECT id from affinity_domain where keyword = 'IHE_EYE'));
UPDATE cmn_transaction_instance SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'LAB') WHERE (affinitydomain_id = (SELECT id from affinity_domain where keyword = 'IHE_LAB'));
UPDATE cmn_transaction_instance SET domain_id = (SELECT id FROM tf_domain WHERE keyword = 'SeHE') WHERE (affinitydomain_id = (SELECT id from affinity_domain where keyword = 'SeHE'));
ALTER  TABLE  cmn_transaction_instance DROP COLUMN affinitydomain_id;

ALTER TABLE system_configuration ADD COLUMN actor_id INT;
INSERT INTO system_configuration (id, dtype, is_available, is_public, name, owner, system_name, url, application, facility, hl7_protocol, ip_address, message_encoding, port, private_ip, charset_id, actor_id)
  SELECT
    nextval('system_configuration_id_seq'),
    'hl7v2_responder',
    active,
    shared,
    name,
    username,
    name,
    url,
    application,
    facility,
    hl7_protocol,
    ip_address,
    message_encoding,
    port,
    private_ip,
    charset_id,
    actor_id
  FROM sys_config;

INSERT INTO system_configuration (id, dtype, is_available, is_public, name, owner, system_name, ae_title, host, dicom_port, actor_id)
  SELECT
    nextval('system_configuration_id_seq'),
    'dicom_scp',
    is_active,
    is_public,
    concat(id, ' ', name),
    creator,
    concat(id, ' ', name),
    ae_title,
    host,
    to_number(port, '99G999D9S'),
    0
  FROM om_scp_configuration;

CREATE OR REPLACE FUNCTION sut_usages()
  RETURNS VOID AS
$$
DECLARE sys RECORD;
BEGIN
  FOR sys IN SELECT *
             FROM system_configuration
             WHERE dtype = 'hl7v2_responder'
  LOOP
    IF (sys.actor_id = (SELECT id
                        FROM tf_actor
                        WHERE keyword = 'OF'))
    THEN
      INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                 FROM usage_metadata
                                                                                                 WHERE keyword =
                                                                                                       'EYE_RAD2'));
      INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                 FROM usage_metadata
                                                                                                 WHERE keyword =
                                                                                                       'RAD_RAD2'));
      INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                 FROM usage_metadata
                                                                                                 WHERE keyword =
                                                                                                       'LAB_OF_LAB5'));
      INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                 FROM usage_metadata
                                                                                                 WHERE keyword =
                                                                                                       'LAB_OF_LAB1'));
      INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                 FROM usage_metadata
                                                                                                 WHERE keyword =
                                                                                                       'LAB_OF_LAB2'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'OP'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'EYE_RAD3'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'RAD_RAD3'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_OP_LAB1'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_OP_LAB2'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'ANALYZER'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_ANALYZER'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'ANALYZER_MGR'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_AN_MGR_LAB27'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_AN_MGR_LAB29'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'ORT'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_ORT'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'AM'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'LAB_AM'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'IM'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'RAD_RAD4'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'EYE_RAD4'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'RAD_RAD13'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'EYE_RAD13'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'TRO_CREATOR'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'KSA_TRO_CREATOR'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'TRO_FULFILLER'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'KSA_TRO_FULFILLER'));
    ELSEIF (sys.actor_id = (SELECT id
                            FROM tf_actor
                            WHERE keyword = 'TRO_FORWARDER'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'KSA_TRO_FORWARDER'));
    ELSEIF (sys.actor_id = 0)
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'EYE_RAD5'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'RAD_RAD5'));
    END IF;

  END LOOP;
  RETURN;
END
$$
LANGUAGE 'plpgsql';

SELECT sut_usages();
ALTER TABLE system_configuration DROP COLUMN actor_id;
ALTER TABLE om_user_preferences DROP COLUMN system_configuration_id;
DROP TABLE sys_config;
DROP TABLE om_scp_configuration;
DROP TABLE evscvalidationresults;
DROP TABLE hl7_message;
DROP TABLE cmn_value_set;
ALTER TABLE om_value_set RENAME TO "cmn_value_set";
SELECT pg_catalog.setval('cmn_value_set_id_seq', (select max(id) from cmn_value_set), true);
INSERT INTO cmn_value_set (id, usage, value_set_keyword, value_set_name, value_set_oid) VALUES (nextval('cmn_value_set_id_seq'), 'PID', 'RACE', 'Race', '1.3.6.1.4.1.21367.101.102');
INSERT INTO cmn_value_set (id, usage, value_set_keyword, value_set_name, value_set_oid) VALUES (nextval('cmn_value_set_id_seq'), 'PID', 'RELIGION', 'Religion', '1.3.6.1.4.1.21367.101.122');
INSERT INTO cmn_value_set (id, usage, value_set_keyword, value_set_name, value_set_oid) VALUES (nextval('cmn_value_set_id_seq'), 'PID-8', 'HL70001', 'Administrative Sex', '1.3.6.1.4.1.21367.101.101');
DROP SEQUENCE om_value_set_id_seq;
DROP SEQUENCE sys_config_id_seq;
DROP SEQUENCE hl7_message_id_seq;
DROP SEQUENCE ort_evscvalidationresults_id_seq;

insert into app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'contact_email', 'anne-gaelle.berge@ihe-europe.net');
insert into app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'contact_name', 'Anne-Gaëlle Bergé');
insert into app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'contact_title', 'Application administrator');
update cmn_transaction_instance set standard = 0;

UPDATE hl7_simulator_responder_configuration SET domain_id = (select id from tf_domain where keyword = 'RAD') where receiving_application like 'OM_RAD%';
UPDATE hl7_simulator_responder_configuration SET domain_id = (select id from tf_domain where keyword = 'EYE') where receiving_application like 'OM_EYE%';
UPDATE hl7_simulator_responder_configuration SET domain_id = (select id from tf_domain where keyword = 'LAB') where receiving_application like 'OM_LAB%';

insert into om_number_generator (id, actor_id, namespace_id, next_entity_identifier, universal_id, universal_id_type) values (nextval('om_number_generator_id_seq'), (select id from tf_actor where keyword = 'LB'), 'IHE_OM_LB', 1, '1.3.6.1.4.1.12559.11.1.2.2.4.7', 'ISO');
insert into om_number_generator (id, actor_id, namespace_id, next_entity_identifier, universal_id, universal_id_type) values (nextval('om_number_generator_id_seq'), (select id from tf_actor where keyword = 'LIP'), 'IHE_OM_LIP', 1,  '1.3.6.1.4.1.12559.11.1.2.2.4.8', 'ISO');

---create indexes
CREATE INDEX ON om_dicom_message_attributesforcommandset (om_dicom_message_id, dicom_message_id);
CREATE INDEX ON om_patient (first_name, last_name, gender_code, creation_date, creator);
CREATE INDEX ON om_encounter (visit_number, patient_id, creation_date, creator);
CREATE INDEX ON om_lab_order (filler_order_number, placer_order_number, placer_group_number, encounter_id, creator, last_changed, order_control_code, domain_id, simulated_actor_id, container_id);
CREATE INDEX ON om_specimen (filler_assigned_identifier, placer_assigned_identifier, creator, creation_date, domain_id, simulated_actor_id);
CREATE INDEX ON om_scheduled_procedure_step (requested_procedure_id, creator, last_changed, scheduled_procedure_id, domain_id, simulated_actor_id);
CREATE INDEX ON cmn_message_instance (issuing_actor, type, issuer);
CREATE INDEX ON om_dicom_message (simulated_actor_id, simulated_transaction_id, timestamp, from_host_port, to_host_port, dicom_affectedsopclassuid, dicom_commandfield);
CREATE INDEX ON om_observation (specimen_id, order_id, simulated_actor_id, domain_id);
CREATE INDEX ON om_order (filler_order_number, placer_order_number, placer_group_number, encounter_id, creator, last_changed, order_control_code, domain_id, simulated_actor_id);
CREATE INDEX ON cmn_transaction_instance (request_id, response_id, domain_id, transaction_id, timestamp, simulated_actor_id, is_visible);
CREATE INDEX ON om_container (specimen_id, simulated_actor_id, domain_id, creator, creation_date, container_identifier);
CREATE INDEX ON om_patient_identifier (patient_id, identifier);
CREATE INDEX ON om_lab_order_specimen (lab_order_id, specimen_id);
CREATE INDEX ON om_requested_procedure (order_id, last_changed, creator, simulated_actor_id, domain_id, study_instance_uid);
