/*
 * MySQL script to change relation between carespell and referral details from 1:1 to 1:many
 * Paul
 * 05 Jan 2018
 * 
 */


-- add new columns from HHFT model
alter table REFERRAL_DETAILS add REFERRAL_METHOD int null;
alter table REFERRAL_DETAILS add REFERRING_TO_HOSPITAL int null;
alter table REFERRAL_DETAILS add REFERRAL_MODE int null;
alter table REFERRAL_DETAILS add item_index int;
-- review this: do we want to do this now, or rethink server to server first
alter table carespell add isExternalcs bit null;

-- create XML form values column
alter table afFormInstance add afFormInstance_xml text null;

-- server to server
alter table HOSPITALS add destination_url varchar(1024) null;
alter table HOSPITALS add hospital_password varchar(1024) null;
alter table HOSPITALS add host_name varchar(1024) null;
alter table HOSPITALS add queue_name varchar(1024) null;



-- additional constraints and indexes from hibernate tools
create index aclNameIndex on acl (acl_name);
create index formNameIndex on afFormInstance (afFormInstance_formName);
create index afObject_index1 on afObject (afObject_name, afObject_domName);
create index nl_index1 on namedList (nl_name, nl_domain);
alter table namedList add constraint name_domain unique (nl_name, nl_domain);
create index occurence_index2 on occurence (occurence_endDate, occurence_startDate);
create index occurence_index1 on occurence (occurence_resId);
create index ix_nhsNum on patient (patient_nhsNum);
create index ix_hospNum on patient (patient_hospNum);
alter table patient_notes add constraint uniqueNotes unique (note_type, patient_id);
create index principal_index1 on principal (principal_name);


alter table users add column external_ldap_user_id varchar(255) null;
-- enforce QueryBuilder foreign keys
alter table QueryBuilder MODIFY owner_id int null;

alter table QueryBuilder add constraint FK5l9edg7l3df6gw7eo7191ouy8 foreign key (owner_id) references users(user_id);

alter table queryFieldMatch MODIFY owner_id int null;
alter table queryFieldMatch MODIFY child_index int null;

alter table queryFieldMatch add constraint FKg6hct1h1nuovfldmeyrmt6431 foreign key (owner_id) references QueryBuilder(owner_id);


ALTER TABLE query_grouping CHANGE group_name groupingCodes_KEY varchar(255);  
  
ALTER TABLE pathway_form_map CHANGE pathway_form_id formsSet_KEY int;  
 
ALTER TABLE pathway_groups_map CHANGE pathway_group_id groupMap_KEY int;  

ALTER TABLE pathway_groups_map_order CHANGE orderNo groupMapOrder_KEY int;  

ALTER TABLE pathway_summaries CHANGE pathway_summary_id summaries_KEY int;  

-- run after nmdmReferralMigration code
-- populate RD pathway from carespell
update REFERRAL_DETAILS rd set rd.PATHWAY_ID = 
(select pathway_id from carespell cs where cs.carespell_id = rd.carespell_id) where rd.PATHWAY_ID is null;

-- now we have migrated the relationship, enforce the foreign key
update carespell set pathway_id=null where REFERRAL_ID is not null;

-- EMDT-584 Supporting staff migration
drop table if exists SUPPORTING_STAFFS_TEMP;
alter table SUPPORTING_STAFFS drop foreign key FKos6wn8gval5w6pg5eiiac1t94;
create table SUPPORTING_STAFFS_TEMP (SENIOR_STAFF_ID integer not null, SUPPORTING_STAFF_ID integer not null, primary key (SENIOR_STAFF_ID, SUPPORTING_STAFF_ID));
alter table SUPPORTING_STAFFS_TEMP add constraint FKnny4fvxiuvvxy7y7u378s7ya foreign key (SUPPORTING_STAFF_ID) references users (user_id);
alter table SUPPORTING_STAFFS_TEMP add constraint FKos6wn8gval5w6pg5eiiac1t94 foreign key (SENIOR_STAFF_ID) references users (user_id);
insert into SUPPORTING_STAFFS_TEMP (SENIOR_STAFF_ID, SUPPORTING_STAFF_ID) (select ssi.seniorStaffUser_id, ss.SENIOR_STAFF_ID from SENIOR_STAFF_INFO ssi join SUPPORTING_STAFFS ss on ssi.id = ss.USER_ID);
alter table SUPPORTING_STAFFS drop foreign key FKq294sdm7kt21k9o190u38y4ja;
drop table SENIOR_STAFF_INFO;
drop table SUPPORTING_STAFFS;
rename table SUPPORTING_STAFFS_TEMP to SUPPORTING_STAFFS;

alter table meeting_blog modify meeting_item_id int null;

-- bbrad package change
update IMAGE_TRANSFER_METHOD set image_transfer_interface = 'com.ardeo.emdt.imaging.bbrad.BBRadImagingInterface' where image_transfer_interface = 'com.ardeo.emdt.imaging.BBRadImagingInterface'

-- named lists
alter table namedList add nl_creationDate datetime null;
alter table namedList add nl_modifiedDate datetime null;
update namedList set nl_creationDate = NOW(), nl_modifiedDate = NOW();

-- Referral Pathway GUID
alter table referral_pathways add guid varchar(50) null;
update referral_pathways
set guid = (select UUID())
where guid is null;
alter table referral_pathways modify column guid varchar(50) not null;

-- Users GUID
alter table users add guid varchar(50) null;
update users
set guid = (select UUID())
where guid is null;
alter table users modify column guid varchar(50) not null;

-- meeting GUID
alter table meeting add guid varchar(50) null;
update meeting
set guid = (select UUID())
where guid is null;
alter table meeting modify column guid varchar(50) not null;

-- meetingItem GUID
alter table meetingItem add guid varchar(50) null;
update meetingItem
set guid = (select UUID())
where guid is null;
alter table meetingItem modify column guid varchar(50) not null;

-- PatientSummary GUID
alter table PatientSummary add guid varchar(50) null;
update PatientSummary
set guid = (select UUID())
where guid is null;
alter table PatientSummary modify column guid varchar(50) not null;

-- EMDT-611
UPDATE pathway_form_map  SET Form_name = REPLACE(Form_name, '<br>', CHAR(10)) ;
UPDATE pathway_form_map  SET Form_name = REPLACE(Form_name, '<br />', CHAR(10)) ;
UPDATE pathway_forms  SET displayName = REPLACE(displayName, '<br>', CHAR(10)) ;
UPDATE pathway_forms  SET displayName = REPLACE(displayName, '<br />', CHAR(10)) ;
UPDATE pathway_FormSummary_Map  SET name = REPLACE(name, '<br>', CHAR(10)) ;
UPDATE pathway_FormSummary_Map  SET name = REPLACE(name, '<br />', CHAR(10)) ;

-- EMDT-637
RENAME TABLE reporting_frequency TO reporting_frequency_temp;
create table reporting_frequency (activity_rep_freq_id integer not null auto_increment, activity_report_name varchar(255), preferred_weekday varchar(255), preferred_frequency integer, preferred_hour integer, preferred_minutes integer, EveryDay bit, FriDay bit, MonDay bit, SaturDay bit, SunDay bit, ThursDay bit, TuesDay bit, WendsDay bit, user_id integer, primary key (activity_rep_freq_id));
INSERT INTO reporting_frequency SELECT * FROM reporting_frequency_temp;
DROP TABLE reporting_frequency_temp;

-- EMDT-734
update afObject set formField_extRef='com.ardeo.entities.CareSpell' where formField_extRef='com.ardeo.emdt.CareSpell';
update afObject set formField_extRef='com.ardeo.entities.Patient' where formField_extRef='com.ardeo.emdt.Patient';