/*
 * MS SQL Server script to migrate from eMDT v1.x schema to v2.x
 * Moulya
 * 13 Jan 2017
 * Paul
 * 11 July 2018
 */


use ardeo_emdt;
go

print 'Starting migration'

-- 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
-- server to server
alter table carespell add isExternalcs bit null
go

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

-- 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
go

-- additional constraints and indexes
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);

go

alter table users add external_ldap_user_id varchar(255) null;

-- enforce QueryBuilder foreign keys
alter table QueryBuilder alter column owner_id int null;
go
alter table QueryBuilder add constraint FK5l9edg7l3df6gw7eo7191ouy8 foreign key (owner_id) references users;
alter table queryFieldMatch alter column owner_id int null;
alter table queryFieldMatch alter column child_index int null;
go
alter table queryFieldMatch add constraint FKg6hct1h1nuovfldmeyrmt6431 foreign key (owner_id) references QueryBuilder;
go

EXEC sp_rename 'query_grouping.group_name', 'groupingCodes_KEY', 'COLUMN';
GO 

EXEC sp_rename 'pathway_form_map.pathway_form_id', 'formsSet_KEY', 'COLUMN';
GO 


EXEC sp_rename 'pathway_groups_map.pathway_group_id', 'groupMap_KEY', 'COLUMN';
GO 

EXEC sp_rename 'pathway_groups_map_order.orderNo', 'groupMapOrder_KEY', 'COLUMN';
GO 

EXEC sp_rename 'pathway_summaries.pathway_summary_id', 'summaries_KEY', 'COLUMN';
GO

--  EMDT-425 - remove redundant join table userRolesToRole
alter table userRolesToRole add user_id int null
go

update urr
SET urr.user_id = ur.user_id
from userRolesToRole urr
			 INNER JOIN userRoles ur ON urr.role_userRoleId = ur.userRoles_id


create table userRolesTemp (user_id integer not null, roleId integer not null, primary key (user_id, roleId))
alter table userRolesTemp add constraint FKd8tyo0yeggnxc3c03tt6xelxt foreign key (roleId) references roles (role_id)
alter table userRolesTemp add constraint FKgjefoi8bftew1y9vtbfbm8jsp foreign key (user_id) references users (user_id)
go

INSERT INTO userRolesTemp (user_id,roleId) SELECT user_id,roleId FROM userRolesToRole;

go

DROP TABLE userRolesToRole;
DROP TABLE userRoles;

go

sp_rename 'userRolesTemp', 'userRoles'

go

-- EMDT-584 Supporting staff migration
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'SUPPORTING_STAFFS_TEMP' AND TABLE_SCHEMA = 'dbo')
    drop table dbo.SUPPORTING_STAFFS_TEMP;
go
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);
go
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)
go
alter table SUPPORTING_STAFFS drop constraint [FK7BD18D9F91A68F76]
go
drop table SENIOR_STAFF_INFO
drop table SUPPORTING_STAFFS
go
EXEC sp_rename 'SUPPORTING_STAFFS_TEMP' , 'SUPPORTING_STAFFS'
go

-- meeting_blog FK
alter table meeting_blog alter column meeting_item_id int null
go

-- 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;
go
update namedList set nl_creationDate = GETDATE(), nl_modifiedDate = GETDATE();
go

-- Referral Pathway GUID
alter table referral_pathways add guid varchar(50) null
go
update referral_pathways
set guid = (select lower(newid()))
where guid is null
alter table referral_pathways alter column guid varchar(50) not null
go

-- Users GUID
alter table users add guid varchar(50) null
go
update users
set guid = (select lower(newid()))
where guid is null
alter table users alter column guid varchar(50) not null
go

-- meeting GUID
alter table meeting add guid varchar(50) null
go
update meeting
set guid = (select lower(newid()))
where guid is null
alter table meeting alter column guid varchar(50) not null
go

-- meetingItem GUID
alter table meetingItem add guid varchar(50) null
go
update meetingItem
set guid = (select lower(newid()))
where guid is null
alter table meetingItem alter column guid varchar(50) not null
go

-- PatientSummary GUID
alter table PatientSummary add guid varchar(50) null
go
update PatientSummary
set guid = (select lower(newid()))
where guid is null
alter table PatientSummary alter column guid varchar(50) not null
go

-- 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
EXEC sp_rename reporting_frequency,  reporting_frequency_temp
go
create table reporting_frequency ([activity_rep_freq_id] [int] IDENTITY(1,1) NOT NULL,
                                  [activity_report_name] [varchar](255) NULL,
                                  [preferred_weekday] [varchar](255) NULL,
                                  [preferred_frequency] [int] NULL,
                                  [preferred_hour] [int] NULL,
                                  [preferred_minutes] [int] NULL,
                                  [EveryDay] [bit] NULL,
                                  [FriDay] [bit] NULL,
                                  [MonDay] [bit] NULL,
                                  [SaturDay] [bit] NULL,
                                  [SunDay] [bit] NULL,
                                  [ThursDay] [bit] NULL,
                                  [TuesDay] [bit] NULL,
                                  [WendsDay] [bit] NULL,
                                  [user_id] [int] NULL,
                                  primary key (activity_rep_freq_id))
go
INSERT INTO reporting_frequency (activity_report_name, preferred_weekday, preferred_frequency, preferred_hour, preferred_minutes, EveryDay, FriDay, MonDay, SaturDay, SunDay, ThursDay, TuesDay, WendsDay, user_id)
SELECT activity_report_name, preferred_weekday, preferred_frequency, preferred_hour, preferred_minutes, EveryDay, FriDay, MonDay, SaturDay, SunDay, ThursDay, TuesDay, WendsDay, user_id FROM reporting_frequency_temp
go
drop table reporting_frequency_temp;
go

-- 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'

-- EMDT-599 audit log migration
alter table AUDIT_LOG add audit_user_username varchar(256) null
alter table AUDIT_LOG add audit_user_fullname varchar(512) null
go
