INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'bp6_mode_enabled', 'false');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'pam_automate_enabled', 'false');
INSERT INTO app_configuration (id, variable, value) values (nextval('app_configuration_id_seq'), 'graph_resources', '/opt/PatientManager/');
UPDATE app_configuration SET variable = 'http://BASE_URL/PatientManager/messages/messageDisplay.seam?id=' where value = 'message_permanent_link';
SELECT pg_catalog.setval('tf_domain_id_seq', 2, true);
INSERT INTO tf_domain (id, description, keyword, name) VALUES (nextval('tf_domain_id_seq'), 'RAD', 'RAD', 'Radiology');
UPDATE validation_parameters
SET validator_type = 0
WHERE profile_oid LIKE '1.%';
UPDATE validation_parameters
SET validator_type = 1
WHERE profile_oid LIKE 'P%' OR profile_oid LIKE 'Q%' OR profile_oid LIKE 'M%';
UPDATE validation_parameters
SET domain_id = (SELECT id
                 FROM tf_domain
                 WHERE keyword = 'ITI')
WHERE (transaction_id != 8 AND transaction_id != 9);
UPDATE validation_parameters
SET domain_id = (SELECT id
                 FROM tf_domain
                 WHERE keyword = 'RAD')
WHERE (transaction_id = 8 OR transaction_id = 9);
DROP TABLE hl7_validation_parameters_affinity_domain;
UPDATE hl7_simulator_responder_configuration
SET domain_id = (SELECT id
                 FROM tf_domain
                 WHERE keyword = 'ITI');
ALTER TABLE hl7_simulator_responder_configuration DROP COLUMN affinity_domain_id;
UPDATE usage_metadata
SET action = 'PDQV3 Supplier'
WHERE action = 'PDQ Supplier';
UPDATE usage_metadata
SET keyword = 'IHE_PDQV3_PDS'
WHERE action = 'IHE_PDQ_PDS';
UPDATE usage_metadata
SET action = 'PIXV3 Manager'
WHERE action = 'PIX Manager';
UPDATE usage_metadata
SET keyword = 'IHE_PIXV3_MGR_ITI45'
WHERE action = 'IHE_PIX_MGR_ITI45';
UPDATE usage_metadata
SET keyword = 'IHE_PIXV3_MGR_ITI44'
WHERE action = 'IHE_PIX_MGR_ITI44';
UPDATE usage_metadata
SET action = 'PIXV3 Consumer'
WHERE action = 'PIX Consumer';
UPDATE usage_metadata
SET keyword = 'IHE_PIXV3_CONS'
WHERE action = 'IHE_PIX_CONS';
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-30'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'PAM PDC', 'IHE_ITI30_PDC');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-31'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'PAM PEC', 'IHE_ITI31_PEC');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-30'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'PIX Manager',
        'IHE_ITI30_PIXMgr');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-1'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE'), 'ADT Client', 'IHE_RAD1');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'RAD-12'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'ADT Client', 'IHE_RAD12');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-21'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'PDQ PDS', 'IHE_PDQ_PDS');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-22'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'PDQ PDS (VISIT)',
        'IHE_PDQ_PDS_VISIT');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-8'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE'), 'PIX Manager', 'IHE_ITI8_PIXMgr');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-9'), (SELECT id
                                                             FROM affinity_domain
                                                             WHERE keyword = 'IHE'), 'PIX Manager', 'IHE_ITI9_PIXMgr');
INSERT INTO usage_metadata (id, transaction_id, affinity_id, action, keyword)
VALUES (nextval('usage_id_seq'), (SELECT id
                                  FROM tf_transaction
                                  WHERE keyword = 'ITI-10'), (SELECT id
                                                              FROM affinity_domain
                                                              WHERE keyword = 'IHE'), 'PIX Consumer', 'IHE_ITI10_CONS');
ALTER TABLE public.system_configuration ALTER COLUMN url DROP NOT NULL;
UPDATE app_configuration
SET variable = 'hl7v2_xsl_location'
WHERE variable = 'xsl_location';
UPDATE pam_movement
SET action_type = 0
WHERE status = 'INSERT';
UPDATE pam_movement
SET action_type = 1
WHERE status = 'CANCEL';
UPDATE pam_movement
SET action_type = 2
WHERE status = 'UPDATE';
ALTER TABLE pam_movement DROP COLUMN status;
ALTER TABLE pam_encounter_management_event DROP COLUMN insert_requires_new_encounter;
ALTER TABLE pam_encounter_management_event DROP COLUMN insert_requires_outpatient;
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A01';
UPDATE pam_encounter_management_event
SET selection = 4
WHERE insert_trigger_event = 'A02';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A03';
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A04';
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A05';
UPDATE pam_encounter_management_event
SET selection = 4
WHERE insert_trigger_event = 'A06';
UPDATE pam_encounter_management_event
SET selection = 4
WHERE insert_trigger_event = 'A07';
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A08';
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A40';
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A01';
UPDATE pam_encounter_management_event
SET selection = 2
WHERE insert_trigger_event = 'A14';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A15';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A16';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A22';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A21';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A44';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A54';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A09';
UPDATE pam_encounter_management_event
SET selection = 1
WHERE insert_trigger_event = 'A10';
UPDATE pam_encounter
SET status = 0
WHERE patient_status = 'ADMITTED';
UPDATE pam_encounter
SET prior_status = 0
WHERE prior_patient_status = 'ADMITTED';
UPDATE pam_encounter
SET status = 1
WHERE patient_status = 'REGISTERED';
UPDATE pam_encounter
SET prior_status = 1
WHERE prior_patient_status = 'REGISTERED';
UPDATE pam_encounter
SET status = 2
WHERE patient_status = 'DISCHARGED';
UPDATE pam_encounter
SET prior_status = 2
WHERE prior_patient_status = 'DISCHARGED';
UPDATE pam_encounter
SET status = 3
WHERE patient_status = 'VISIT_ENDED';
UPDATE pam_encounter
SET prior_status = 3
WHERE prior_patient_status = 'VISIT_ENDED';
UPDATE pam_encounter
SET status = 4
WHERE patient_status = 'PRE_ADMITTED';
UPDATE pam_encounter
SET prior_status = 4
WHERE prior_patient_status = 'PRE_ADMITTED';
UPDATE pam_encounter
SET status = 5
WHERE patient_status = 'PENDING_ADMIT';
UPDATE pam_encounter
SET prior_status = 5
WHERE prior_patient_status = 'PENDING_ADMIT';
UPDATE pam_encounter
SET status = 6
WHERE patient_status = 'PENDING_TRANSFER';
UPDATE pam_encounter
SET prior_status = 6
WHERE prior_patient_status = 'PENDING_TRANSFER';
UPDATE pam_encounter
SET status = 7
WHERE patient_status = 'PENDING_DISCHARGE';
UPDATE pam_encounter
SET prior_status = 7
WHERE prior_patient_status = 'PENDING_DISCHARGE';
UPDATE pam_encounter
SET status = 8
WHERE patient_status = 'ENCOUNTER_CANCELLED';
UPDATE pam_encounter
SET prior_status = 8
WHERE prior_patient_status = 'ENCOUNTER_CANCELLED';
ALTER TABLE pam_encounter DROP COLUMN patient_status;
ALTER TABLE pam_encounter DROP COLUMN prior_patient_status;

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_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)
  SELECT
    nextval('cmn_transaction_instance_id_seq'),
    transaction_id,
    timestamp,
    is_visible,
    simulatedactor_id,
    1,
    sut_ip,
    null,
    null,
    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 = 'ITI')
WHERE (transaction_id != 8 AND transaction_id != 9);
UPDATE cmn_transaction_instance
SET domain_id = (SELECT id
                 FROM tf_domain
                 WHERE keyword = 'RAD')
WHERE (transaction_id = 8 OR transaction_id = 9);

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;

 CREATE 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 = 'PDS'))
      THEN
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'IHE_PDQ_PDS'));
        INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                   FROM usage_metadata
                                                                                                   WHERE keyword =
                                                                                                         'IHE_PDQ_PDS_VISIT'));
      ELSEIF (sys.actor_id = (SELECT id
                              FROM tf_actor
                              WHERE keyword = 'PDC'))
        THEN
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_ITI30_PDC'));
      ELSEIF (sys.actor_id = (SELECT id
                              FROM tf_actor
                              WHERE keyword = 'PEC'))
        THEN
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_ITI31_PEC'));
      ELSEIF (sys.actor_id = (SELECT id
                              FROM tf_actor
                              WHERE keyword = 'MPI'))
        THEN
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_RAD1'));
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_RAD12'));
      ELSEIF (sys.actor_id = (SELECT id
                              FROM tf_actor
                              WHERE keyword = 'PAT_IDENTITY_CONSUMER'))
        THEN
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_ITI10_CONS'));
      ELSEIF (sys.actor_id = (SELECT id
                              FROM tf_actor
                              WHERE keyword = 'PAT_IDENTITY_X_REF_MGR'))
        THEN
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_ITI8_PIXMgr'));
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_ITI9_PIXMgr'));
          INSERT INTO sys_conf_type_usages (system_configuration_id, listusages_id) VALUES (sys.id, (SELECT id
                                                                                                     FROM usage_metadata
                                                                                                     WHERE keyword =
                                                                                                           'IHE_ITI30_PIXMgr'));
      END IF;

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

SELECT sut_usages();
ALTER TABLE system_configuration DROP COLUMN actor_id;
DROP TABLE sys_config;
DROP TABLE evscvalidationresults;
DROP TABLE hl7_message;

INSERT INTO pam_encounter_management_event (id, insert_trigger_event, cancel_trigger_event, update_trigger_event, event_name, page_id, available, selection) VALUES (nextval('pam_encounter_management_event_id_seq'), 'A54', 'A55', 'Z99', 'Change attending doctor', 'IHE', true, 1);
INSERT INTO pam_encounter_management_event (id, insert_trigger_event, cancel_trigger_event, update_trigger_event, event_name, page_id, available, selection) VALUES (nextval('pam_encounter_management_event_id_seq'), 'A21', 'A52', 'Z99', 'Leave of absence', 'IHE', true, 1);
INSERT INTO pam_encounter_management_event (id, insert_trigger_event, cancel_trigger_event, update_trigger_event, event_name, page_id, available, selection) VALUES (nextval('pam_encounter_management_event_id_seq'), 'A22', 'A53', 'Z99', 'Return from leave of absence', 'IHE', true, 1);
INSERT INTO pam_encounter_management_event (id, insert_trigger_event, cancel_trigger_event, update_trigger_event, event_name, page_id, available, selection) VALUES (nextval('pam_encounter_management_event_id_seq'), 'A44', null, null, 'Move account information', 'IHE', true, 1);

UPDATE pam_encounter SET patient_account_number = (SELECT pat.account_number FROM pam_patient AS pat WHERE pat.id = patient_id);
UPDATE pam_patient SET is_patient_dead = false;
UPDATE pam_patient SET patient_death_time = null;