INSERT INTO pat_patient (id, dtype,
  first_name,
  last_name,
  alternate_first_name,
  alternate_last_name,
  gender_code,
  country_code,
  second_name,
  alternate_second_name,
  third_name,
  alternate_third_name,
  mother_maiden_name,
  alternate_mothers_maiden_name,
  character_set,
  creation_date,
  date_of_birth,
  race_code,
  religion_code,
  creator,
  email,
  still_active,
  actor_id,
  account_number,
  dmetaphone_first_name,
  dmetaphone_last_name,
  dmetaphone_mother_maiden_name,
  cross_reference_id,
  blood_group,
  birth_order,
  last_update_facility,
  multiple_birth_indicator,
  identity_reliability_code,
  marital_status,
  is_patient_dead,
  patient_death_time,
  additional_demographics_id,
  vip_indicator, is_test_data)
  SELECT
      id, 'pam_patient',
     first_name,
     last_name,
     alternate_first_name,
     alternate_last_name,
     gender_code,
     country_code,
     second_name,
     alternate_second_name,
     third_name,
     alternate_third_name,
     mother_maiden_name,
     alternate_mothers_maiden_name,
     character_set,
     creation_date,
     date_of_birth,
     race_code,
     religion_code,
     creator,
     email,
     still_active,
     actor_id,
     account_number,
     dmetaphone_first_name,
     dmetaphone_last_name,
     dmetaphone_mother_maiden_name,
     cross_reference_id,
     blood_group,
     birth_order,
     last_update_facility,
     multiple_birth_indicator,
     identity_reliability_code,
     marital_status,
     is_patient_dead,
     patient_death_time,
     additional_demographics_id,
     vip_indicator, FALSE
     FROM pam_patient;

INSERT INTO pat_patient_address (id, address_type, is_main_address, country_code, street, street_number, zip_code, city, address_line, state, patient_id)
  SELECT
    nextval('pat_patient_address_id_seq'),
    6,
    true,
    country_code,
    street,
    NULL ,
    zip_code,
    city,
    street,
    state,
    id
  FROM pam_patient;

INSERT INTO pam_phone_number(id, value, type, is_principal, patient_id)
  SELECT
    nextval('pam_phone_number_id_seq'),
    phone_number,
    0,
    true,
    id
  FROM pam_patient where pam_patient.phone_number is not NULL  and pam_patient.phone_number != '';

SELECT pg_catalog.setval('pat_patient_id_seq', (select max(id) from pat_patient), true);

SELECT
  tc.constraint_name, tc.table_name, kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM
  information_schema.table_constraints AS tc
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='pam_patient';

ALTER TABLE pam_administrative_account DROP CONSTRAINT fk13e9e31f9a40e4b2;
ALTER TABLE pam_patient_history DROP CONSTRAINT fk4c24efb749228738;
ALTER TABLE pam_patient_history DROP CONSTRAINT fk4c24efb7a9b1b141;
ALTER TABLE pam_encounter DROP CONSTRAINT fkab1c01509a40e4b2;
ALTER TABLE pam_patient_patient_identifier DROP CONSTRAINT fka05a23209a40e4b2;

--DROP TABLE pam_patient;

INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'gazelle_sts_url', 'https://gazelle.ihe.net/gazelle-sts');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'sts_default_audience_url', 'http://ihe.connectathon.XUA/X-ServiceProvider-IHE-Connectathon');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'sts_default_username', 'valid');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'sts_default_password', 'connectathon');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'is_sequoia', 'false');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'path_to_keystore', '/opt/gazelle/cert/643.jks');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'private_key_alias', 'tomcat');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'keystore_password', 'password');
INSERT INTO app_configuration (id, variable, value) VALUES (nextval('app_configuration_id_seq'), 'private_key_password', 'password');
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Valid', 'valid', 'connectathon', 'Valid assertion with No-Authz-Consent option', true);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Invalid', 'unknownaudience', 'connectathon', 'Unknown X-Service-Provider URI', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Not yet valid', 'notyetvalid', 'connectathon', 'Assertion used before its lifetime period', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Expired', 'expired', 'connectathon', 'Assertion expired', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Unsigned', 'unsigned', 'connectathon', 'No signature element present', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Unacceptable authentication method', 'invalidauthncontext', 'connectathon', 'AuthnContextClassRef is "Internet Protocol" which "policy" says is an unacceptable authentication method', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Role not allowed', 'secondrole', 'connectathon', 'Current role (social worker) cannot provide any access', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Purpose of use not allowed', 'secondpurposeofuse', 'connectathon', 'Current purposeofuse (research) cannot provide any access', false);
INSERT INTO xua_picketlink_credentials(id, label, username, password, description, is_default) VALUES (nextval('xua_picketlink_credentials_id_seq'), 'Valid (authz consent option)', 'withauthzconsent', 'connectathon', 'Valid assertion with No-Authz-Content option', false);


-- fix database scheme by changing date into timestamp
ALTER TABLE public.pam_encounter ALTER COLUMN discharge_date TYPE TIMESTAMP USING discharge_date::TIMESTAMP;
ALTER TABLE public.pam_encounter ALTER COLUMN admit_date TYPE TIMESTAMP USING admit_date::TIMESTAMP;
ALTER TABLE public.pam_encounter ALTER COLUMN expected_admit_date TYPE TIMESTAMP USING expected_admit_date::TIMESTAMP;
ALTER TABLE public.pam_encounter ALTER COLUMN expected_discharge_date TYPE TIMESTAMP USING expected_discharge_date::TIMESTAMP;
ALTER TABLE public.pam_encounter ALTER COLUMN leave_of_absence_date TYPE TIMESTAMP USING leave_of_absence_date::TIMESTAMP;
ALTER TABLE public.pam_encounter ALTER COLUMN transfer_planned_date TYPE TIMESTAMP USING transfer_planned_date::TIMESTAMP;
