SET NOCOUNT ON;

CREATE TABLE EHR.EHR_GUID_INT 
(
	EhrRusRefBooksId INT NOT NULL,
	EhrRusIdGuid UNIQUEIDENTIFIER NOT NULL,
	EhrRusIdInt INT NOT NULL,
	TableName varchar(256) NOT NULL, 
	EntityName varchar(256) NOT NULL,
	CONSTRAINT IX_EHR_RUS_INT_CLUSTERED UNIQUE CLUSTERED (EhrRusIdGuid, EhrRusRefBooksId, TableName, EntityName)
)
GO


CREATE TABLE EHR.EHRS 
(
	EHRSId int IDENTITY(1,1) PRIMARY KEY,
	TableName varchar(256) UNIQUE not null,
	SchemaName varchar(3) not null,
	ColumnName varchar(256) not null,
	NewColumnName varchar(256) not null,
	PkName varchar(256) not null
)
GO

CREATE TABLE EHR.REFERENCES_ON_EHR
(
	ReferencesOnEhrId int IDENTITY(1,1) PRIMARY KEY,
	EHRSTemp INT NOT NULL,
	SchemaName varchar(16) NOT NULL,
	TableName varchar(256) NOT NULL,
	ColumnName varchar(256) NOT NULL,
	NewColumnName varchar(256) NOT NULL,
	FkName varchar(256) NOT NULL,
	IsNullable BIT NOT NULL,
	TableObjectId AS object_id(SchemaName + '.' + TableName),
	CONSTRAINT FK_EHRS FOREIGN KEY (EhrsTemp) REFERENCES EHR.EHRS(EHRSId)
)
GO

CREATE TABLE EHR.EHR_REFERENCE_INFO 
(
	PKTABLE_OWNER VARCHAR(256) NOT NULL,
	PKTABLE_NAME VARCHAR(256) NOT NULL,
	PKCOLUMN_NAME VARCHAR(256) NOT NULL,
	FKTABLE_OWNER VARCHAR(256) NOT NULL,
	FKTABLE_NAME VARCHAR(256) NOT NULL,
	FKCOLUMN_NAME VARCHAR(256) NOT NULL,
	FK_COLUMN_IS_NULLABLE BIT NOT NULL,
	FK_NAME VARCHAR(256) NOT NULL,
	PK_NAME VARCHAR(256) NOT NULL
)
GO

CREATE TABLE EHR.EHR_REF_INDEX 
(
	EhrRefIndexId int IDENTITY(1,1) PRIMARY KEY,
	TableObjectId int not null,
	IndexName varchar(256) not null,
	IndexId int not null,
	IsClustered bit not null,
	IsUnique bit not null		
)
GO

CREATE TABLE EHR.EHR_REF_INDEX_COLUMN
(
	EhrRefIndexColumnsId int IDENTITY(1,1) PRIMARY KEY,
	EhrRefIndexId int FOREIGN KEY REFERENCES EHR.EHR_REF_INDEX(EhrRefIndexId),
	Name varchar(256),
	IsIncluded bit NOT NULL,
)
GO

INSERT INTO EHR.EHRS (TableName, SchemaName, ColumnName, PkName, NewColumnName)
	SELECT 
		t.name as TableName,
		s.name as SchemaName,
		tc.name as ColumnName,
		i.name,
		CONCAT(tc.name, '_int')
	FROM 
		sys.schemas s 
		INNER JOIN sys.tables t   on s.schema_id=t.schema_id
		INNER JOIN sys.indexes i  on t.object_id=i.object_id
		INNER JOIN sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id
		INNER JOIN sys.columns tc on ic.object_id=tc.object_id and ic.column_id=tc.column_id
	WHERE 
		i.is_primary_key=1 
		and t.schema_id = schema_id('EHR')
		and t.name LIKE 'T_EHR_RUS_%'
		and i.is_primary_key = 1
GO

INSERT INTO EHR.EHR_REFERENCE_INFO 
select
    PKTABLE_OWNER       = convert(sysname,schema_name(o1.schema_id)),
    PKTABLE_NAME        = convert(sysname,o1.name),
    PKCOLUMN_NAME       = convert(sysname,c1.name),
    FKTABLE_OWNER       = convert(sysname,schema_name(o2.schema_id)),
    FKTABLE_NAME        = convert(sysname,o2.name),
    FKCOLUMN_NAME       = convert(sysname,c2.name),
	FK_COLUMN_IS_NULLABLE = convert(sysname,c2.is_nullable),
    FK_NAME             = convert(sysname,object_name(f.object_id)),
    PK_NAME             = convert(sysname,i.name)
from
    sys.objects o1,
    sys.objects o2,
    sys.columns c1,
    sys.columns c2,
    sys.foreign_keys f INNER JOIN
    sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) INNER JOIN
    sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
where
    o1.object_id = f.referenced_object_id and
    (o1.schema_id = schema_id('EHR')) and
	(o1.name LIKE 'T_EHR_RUS_%') and 
	-- o1.name <> 'T_EHR_RUS_REF_BOOKS' and
    o2.object_id = f.parent_object_id and
    c1.object_id = f.referenced_object_id and
    c2.object_id = f.parent_object_id and
    c1.column_id = k.referenced_column_id and
    c2.column_id = k.parent_column_id
GO

INSERT INTO EHR.REFERENCES_ON_EHR (EhrsTemp, SchemaName, TableName, ColumnName, FkName, IsNullable, NewColumnName)
	SELECT 
		et.EhrsId,
		te.FKTABLE_OWNER,
		te.FKTABLE_NAME,
		te.FKCOLUMN_NAME,
		te.FK_NAME,
		te.FK_COLUMN_IS_NULLABLE,
		CONCAT(te.FKCOLUMN_NAME, '_int')
	FROM EHR.EHRS et
	JOIN EHR.EHR_REFERENCE_INFO te on et.TableName = te.PKTABLE_NAME
GO
 
INSERT INTO EHR.EHR_REF_INDEX (IndexName, IndexId, TableObjectId, IsClustered, IsUnique)
	SELECT 
		i.name,
		i.index_id,
		i.object_id,
		CASE WHEN i.type = 1 THEN 1 ELSE 0 END,
		i.is_unique
	FROM EHR.REFERENCES_ON_EHR roet
		INNER JOIN sys.indexes i on i.object_id = roet.TableObjectId
		INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
		INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
	WHERE c.name = roet.ColumnName
	GROUP BY i.name, i.index_id, i.object_id, CASE WHEN i.type = 1 THEN 1 ELSE 0 END, i.is_unique 
GO

INSERT INTO EHR.EHR_REF_INDEX_COLUMN (EhrRefIndexId, Name, IsIncluded)
	SELECT 
		eri.EhrRefIndexId,
		COL_NAME(ic.object_id, ic.column_id),
		ic.is_included_column

	FROM sys.indexes i
	JOIN sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
	JOIN sys.tables t on t.object_id = i.object_id
	JOIN EHR.EHR_REF_INDEX eri on eri.IndexId = i.index_id 
	JOIN EHR.REFERENCES_ON_EHR roe on eri.TableObjectId = roe.TableObjectId and t.object_id = roe.TableObjectId
GO

CREATE TABLE EHR.SQL_TABLE 
	(
		Sql varchar(max),
		Id int IDENTITY(1, 1) NOT NULL,
	)
GO

--Добавление новых колонок в главную таблицу
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 'ALTER TABLE ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + ' ADD ' + QUOTENAME(et.NewColumnName) + ' INT NULL' + CHAR(13) + CHAR(13)
FROM EHR.EHRS et 
GO

-- Заполнение новых колонок у главных таблиц
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'; WITH NumberedRows AS (' + CHAR(13) + 
		'	SELECT ' + CHAR(13) + 
		'		' + QUOTENAME(et.NewColumnName) + ',' + CHAR(13) + 
		'		ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(et.ColumnName) + ') AS RowNum' + CHAR(13) + 
		'	FROM ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + CHAR(13) + 
		')' + CHAR(13) + 
		'UPDATE	NumberedRows' + CHAR(13) + 
		'SET ' + QUOTENAME(et.NewColumnName) + ' = RowNum' + CHAR(13)+ CHAR(13)
	FROM EHR.EHRS et
	ORDER BY et.EHRSId
	GO


-- Добавление колонок у ссылающихся сущностей
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 'ALTER TABLE ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + ' ADD ' + QUOTENAME(roet.NewColumnName) + ' int NULL' + CHAR(13) + CHAR(13)
FROM EHR.REFERENCES_ON_EHR roet
ORDER BY roet.EHRSTemp
GO

-- Обновление ссылающихся колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'UPDATE ' + 'u'  + CHAR(13)
		+ 'SET u.' + QUOTENAME(roet.NewColumnName) + ' = ehr.' + QUOTENAME(et.NewColumnName) + CHAR(13)
		+ 'FROM ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + ' ehr'+ CHAR(13)
		+ 'INNER JOIN ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + ' u  on ' +  'u.' + roet.ColumnName + ' = ehr.' + et.ColumnName  + CHAR(13) + CHAR(13)
	FROM EHR.REFERENCES_ON_EHR roet 
	JOIN EHR.EHRS et on et.EHRSId = roet.EHRSTemp
GO

-- Проставление required полей у новых ссылающихся колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + CHAR(13)
		+ 'ALTER COLUMN ' + QUOTENAME(roet.NewColumnName) + ' INT NOT NULL' + CHAR(13) + CHAR(13) + CHAR(13) 
	FROM EHR.REFERENCES_ON_EHR roet 
	WHERE roet.IsNullable = 0
	ORDER BY roet.EHRSTemp;
GO

-- Удаление FK
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + ' DROP CONSTRAINT ' + ROET.FkName + CHAR(13)+ CHAR(13)
	FROM EHR.REFERENCES_ON_EHR roet
	ORDER BY roet.EHRSTemp
GO

-- Удаление дефолтных ограничений
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + ' DROP CONSTRAINT ' + QUOTENAME(dc.name) + CHAR(13) + CHAR(13)
	FROM sys.default_constraints dc 
	JOIN EHR.REFERENCES_ON_EHR roet on dc.parent_object_id = roet.TableObjectId
	GROUP BY roet.SchemaName, roet.TableName, dc.Name


-- Удаление индексов для для ссылающихся колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'DROP INDEX ' + QUOTENAME(eri.IndexName) + ' ON ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName)  + CHAR(13) + CHAR(13)
	FROM EHR.REFERENCES_ON_EHR roet 
	JOIN EHR.EHR_REF_INDEX eri on roet.TableObjectId = eri.TableObjectId
	GROUP BY eri.IndexName, roet.SchemaName, roet.TableName
GO

-- Удаление старых ссылающихся на GUID колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT
		'ALTER TABLE ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + ' DROP COLUMN ' + QUOTENAME(roet.ColumnName) + CHAR(13)+ CHAR(13)
	FROM EHR.REFERENCES_ON_EHR roet
	ORDER BY roet.EHRSTemp
GO

-- Переименование ссылающихся колонок 
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'EXEC sp_rename @objname=''' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + '.' + roet.NewColumnName + ''',' + '@newname=''' + roet.ColumnName + '''' + CHAR(13) + CHAR(13)
	FROM EHR.REFERENCES_ON_EHR roet
	ORDER BY roet.EHRSTemp
GO

-- Заполнение EHR.EHR_GUID_INT 
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'INSERT INTO EHR.EHR_GUID_INT (EhrRusRefBooksId, EhrRusIdGuid, EhrRusIdInt, TableName, EntityName)' + CHAR(13) 
		 + 'SELECT ' + CHAR(13)
		 + '  EhrRusRefBooks,' + CHAR(13)
		 + ' ' + et.ColumnName + ',' + CHAR(13)
		 + ' ' + et.NewColumnName + ','+ CHAR(13)
		 + ' ''' + et.SchemaName + '.' + et.TableName + ''',' + CHAR(13)
		 + ' ''' + et.SchemaName + '.' + SUBSTRING(et.TableName, 3, LEN(et.TableName) - 2) + '''' + CHAR(13)
		 + 'FROM ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + CHAR(13)
	FROM EHR.EHRS et 
	WHERE et.TableName <> 'T_EHR_RUS_REF_BOOKS'
	ORDER BY et.EHRSId

-- Создание новых индексов
DECLARE
	@index_name VARCHAR(MAX), 
	@index_id INT, 
	@table_object_id INT,
	@included_columns VARCHAR(MAX),
	@main_columns VARCHAR(MAX)
DECLARE curs CURSOR FOR
	SELECT eri.IndexName, eri.IndexId, roet.TableObjectId
	FROM EHR.REFERENCES_ON_EHR roet
	JOIN EHR.EHR_REF_INDEX eri on eri.TableObjectId = roet.TableObjectId
	GROUP BY roet.TableObjectId, eri.IndexName, eri.IndexId

OPEN curs 

FETCH NEXT FROM curs INTO @index_name, @index_id, @table_object_id

WHILE @@FETCH_STATUS = 0
BEGIN 
	SET @included_columns = ''
	SET @main_columns = ''

	SELECT
		@main_columns = @main_columns +
			CASE
				WHEN @main_columns = '' THEN  QUOTENAME(eric.Name)
				ELSE ', ' + QUOTENAME(eric.Name)
			END
	FROM EHR.EHR_REF_INDEX_COLUMN eric
	JOIN EHR.EHR_REF_INDEX eri on eric.EhrRefIndexId = eri.EhrRefIndexId
	WHERE eric.IsIncluded = 0 and eri.IndexId = @index_id and eri.IndexName = @index_name
	GROUP BY eric.Name

	SELECT 
		@included_columns = @included_columns + 
			CASE
				WHEN @included_columns = '' THEN  QUOTENAME(eric.Name)
				ELSE ', ' + QUOTENAME(eric.Name)
			END
	FROM EHR.EHR_REF_INDEX_COLUMN eric 
	JOIN EHR.EHR_REF_INDEX eri on eri.EhrRefIndexId = eric.EhrRefIndexId
	WHERE 
		eri.IndexName = @index_name 
		and eri.IndexId = @index_id
		and eric.IsIncluded = 1
	GROUP BY eric.Name

	INSERT INTO EHR.SQL_TABLE (Sql)
		SELECT
			'CREATE ' +
			CASE WHEN eri.IsUnique = 1 THEN 'UNIQUE ' ELSE '' END +
			CASE WHEN eri.IsClustered = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END +
			' INDEX ' + QUOTENAME(eri.IndexName) + ' ON ' + 
			QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + ' (' + @main_columns + ')' + 
			CASE WHEN @included_columns <> '' THEN ' INCLUDE (' + @included_columns + ')' 
				ELSE '' END + CHAR(13) + CHAR(13)
		FROM EHR.EHR_REF_INDEX eri
		LEFT JOIN EHR.REFERENCES_ON_EHR roet on roet.TableObjectId = eri.TableObjectId
		WHERE 
			eri.IndexName = @index_name 
			and roet.TableObjectId = @table_object_id 
		GROUP BY eri.IndexName, eri.IsUnique, eri.IsClustered, roet.TableName, roet.SchemaName
	
	FETCH NEXT FROM curs INTO @index_name, @index_id, @table_object_id
END

CLOSE curs
DEALLOCATE curs
GO

-- Удаление PK у главной таблицы
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + ' DROP CONSTRAINT ' + et.PkName + CHAR(13)+ CHAR(13)
	FROM EHR.EHRS et
	ORDER BY et.EHRSId
GO

-- Удаление колонки у главной таблицы
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + ' DROP COLUMN ' + QUOTENAME(et.ColumnName) + CHAR(13)+ CHAR(13)
	FROM EHR.EHRS et
	ORDER BY et.EHRSId
GO

-- Ренейм главных колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'EXEC sp_rename @objname=''' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + '.' + et.NewColumnName + ''',' + '@newname=''' + et.ColumnName + '''' + CHAR(13) + CHAR(13)
	FROM EHR.EHRS et
	ORDER BY et.EHRSId
GO

-- Проставление required полей у новых главных колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + CHAR(13)
		+ 'ALTER COLUMN ' + QUOTENAME(et.ColumnName) + ' INT NOT NULL' + CHAR(13) + CHAR(13)
	FROM EHR.EHRS et
	ORDER BY et.EHRSId;
GO

-- Ограничение на первичный ключ у главных колонок
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + CHAR(13) + 'ADD CONSTRAINT ' + et.PkName + ' PRIMARY KEY CLUSTERED ' + '(' + et.ColumnName + ')' + CHAR(13)+ CHAR(13) 
	FROM EHR.EHRS et
	ORDER BY et.EHRSId
GO

-- Ограничения FK на зависимых колонках
INSERT INTO EHR.SQL_TABLE (Sql)
	SELECT 
		'ALTER TABLE ' + QUOTENAME(roet.SchemaName) + '.' + QUOTENAME(roet.TableName) + CHAR(13) + 'ADD CONSTRAINT ' + roet.FkName + ' FOREIGN KEY (' + QUOTENAME(roet.ColumnName) + ')' + CHAR(13)
		+ 'REFERENCES ' + QUOTENAME(et.SchemaName) + '.' + QUOTENAME(et.TableName) + '(' + et.ColumnName + ')' + CHAR(13) + CHAR(13)
	FROM EHR.REFERENCES_ON_EHR roet
	JOIN EHR.EHRS et on et.EHRSId = roet.EHRSTemp
	ORDER BY roet.EHRSTemp;
GO

-- Инсерт в [dbo].[T_ENTITY_IDS] 
DECLARE @sql varchar(max)
DECLARE @entity_name varchar(max)
DECLARE @EHRS_id int
DECLARE curs CURSOR FOR
	SELECT	
		e.SchemaName + '.' + SUBSTRING(e.TableName, 3, LEN(e.TableName) - 2),
		e.EHRSId
	FROM EHR.EHRS e

OPEN curs
FETCH NEXT FROM curs INTO @entity_name, @EHRS_id
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = ''

	SELECT 
		@sql = 'INSERT INTO [dbo].[T_ENTITY_IDS] (EntName, FieldName, Id) ' + 
			     'SELECT ' + 
					'''' + @entity_name + ''',' + 
					'''PrimaryKey'', ' + 
					'CASE WHEN MAX(' + e.ColumnName + ') IS NULL THEN -1 ELSE MAX(' + e.ColumnName + ') + 1 END ' + 
				 'FROM ' + QUOTENAME(e.SchemaName) + '.' + QUOTENAME(e.TableName)
	FROM EHR.EHRS e
	WHERE e.EHRSId = @EHRS_id

	INSERT INTO EHR.SQL_TABLE (SQL)
	VALUES (@sql)

FETCH NEXT FROM curs INTO @entity_name, @EHRS_id
END
CLOSE curs
DEALLOCATE curs
GO


INSERT INTO EHR.SQL_TABLE(SQL)
	VALUES ('DELETE FROM  [dbo].[T_ENTITY_IDS] WHERE ID = -1')


-- [EHR].[T_ELEMENT_EHR_REPLACEMENT] - из EHR.EHR_GUID_INT необходимо заполнить OldEhr и NewEhr
CREATE TABLE EHR.ELEMENT_EHR_REPLACEMENT_IDX
(
	IndexName VARCHAR(max),
	ColumnName VARCHAR(max),
	IsUnique BIT,
	IsClustered BIT
)
GO

INSERT INTO EHR.ELEMENT_EHR_REPLACEMENT_IDX (IndexName, ColumnName, IsUnique, IsClustered)
SELECT 
	i.name as IndexName,
	c.name as ColumnName,
	i.is_unique as IsUnique,
	CASE WHEN i.type = 2 THEN 0 ELSE 1 END AS IsClustered
FROM sys.indexes i
JOIN sys.tables t on i.object_id = t.object_id
JOIN sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = i.object_id
JOIN sys.columns c on ic.column_id = c.column_id and c.object_id = i.object_id
WHERE
	i.object_id = object_id('EHR.T_ELEMENT_EHR_REPLACEMENT')
	and c.name in ('OldEhr', 'NewEhr')
GO

-- Создание и обновление колонок
INSERT INTO EHR.SQL_TABLE (SQL)
VALUES 
	('ALTER TABLE [EHR].[T_ELEMENT_EHR_REPLACEMENT] ADD OldEhr_int INT NULL'),
	('ALTER TABLE [EHR].[T_ELEMENT_EHR_REPLACEMENT] ADD NewEhr_int INT NULL'),
	(
		'UPDATE [EHR].[T_ELEMENT_EHR_REPLACEMENT]
			SET OldEhr_int = gie.EhrRusIdInt
			FROM EHR.EHR_GUID_INT gie
			JOIN [EHR].[T_EHR_REPLACEMENT] er on OldEhrRefBook = gie.EhrRusRefBooksId
			WHERE gie.EhrRusIdGuid = OldEhr'
	),
	(
		'UPDATE [EHR].[T_ELEMENT_EHR_REPLACEMENT]
			SET NewEhr_int = gie.EhrRusIdInt
			FROM EHR.EHR_GUID_INT gie
			JOIN [EHR].[T_EHR_REPLACEMENT] er on NewEhrRefBook = gie.EhrRusRefBooksId
			WHERE gie.EhrRusIdGuid = NewEhr'
	)
GO

-- Удаление индексов
INSERT INTO EHR.SQL_TABLE(SQL)
	SELECT 
		'DROP INDEX ' + QUOTENAME(eri.IndexName) + ' ON [EHR].[T_ELEMENT_EHR_REPLACEMENT]'
	FROM EHR.ELEMENT_EHR_REPLACEMENT_IDX eri
GO


-- Удаление колонок и переименование новых
INSERT INTO EHR.SQL_TABLE (SQL)
VALUES 
	('ALTER TABLE [EHR].[T_ELEMENT_EHR_REPLACEMENT] DROP COLUMN OldEhr'),
	('ALTER TABLE [EHR].[T_ELEMENT_EHR_REPLACEMENT] DROP COLUMN NewEhr'),
	('EXEC sp_rename @objname=''[EHR].[T_ELEMENT_EHR_REPLACEMENT].OldEhr_int'', @newname=''OldEhr'''),
	('EXEC sp_rename @objname=''[EHR].[T_ELEMENT_EHR_REPLACEMENT].NewEhr_int'', @newname=''NewEhr''')
GO



-- Создание индексов
INSERT INTO EHR.SQL_TABLE(SQL)
	SELECT
			'CREATE ' +
			CASE WHEN eri.IsUnique = 1 THEN 'UNIQUE ' ELSE '' END +
			CASE WHEN eri.IsClustered = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END +
			' INDEX ' + QUOTENAME(eri.IndexName) + ' ON [EHR].[T_ELEMENT_EHR_REPLACEMENT] (' + QUOTENAME(eri.ColumnName) + ')'
	FROM EHR.ELEMENT_EHR_REPLACEMENT_IDX eri
GO



-- EXECUTE запросов
DECLARE @SQL VARCHAR(MAX)=''
DECLARE curs CURSOR FOR
	SELECT Sql
	FROM EHR.SQL_TABLE
	order by Id

OPEN curs
FETCH NEXT FROM curs INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN

	RAISERROR (@SQL, 0, 1) WITH NOWAIT;
	
	exec (@sql)

	FETCH NEXT FROM curs INTO @SQL
END
CLOSE curs
DEALLOCATE curs
GO


IF EXISTS (SELECT SCHEMA_ID FROM SYS.SCHEMAS WHERE NAME = 'AKUZ') 
BEGIN
----------------------------------------------- ЗАМЕНА GUID НА INT В XML --------------------------------------------

CREATE TABLE EHR.XMLSTEMP
(
	ID uniqueidentifier PRIMARY KEY, 
	_Xml varchar(max) NOT NULL,
	_Type int not null
)


CREATE TABLE Xmls 
(
	ID uniqueidentifier PRIMARY KEY, 
	EncodedXml VARCHAR(MAX) COLLATE Cyrillic_General_100_CI_AS,
	DecodedXml XML,
	DecodedXmlText varchar(max),
)

CREATE TABLE XmlsObjectsIds
(
	XmlID uniqueidentifier, 
	ObjectIdGuid uniqueidentifier,
	ObjectIdInt int DEFAULT -1
)


-------------------------AKUZ.T_HOSPITAL_DESTINATION_DICTIONARY------------------------------------------
INSERT INTO Xmls(id, EncodedXml)
SELECT 
	HospitalDestinationDictionaryID, 
	CAST(N'' AS XML ).value('xs:base64Binary(sql:column("RECIPE"))', 'VARBINARY(MAX)') as x  
FROM AKUZ.T_HOSPITAL_DESTINATION_DICTIONARY


UPDATE x
	SET 
		x.DecodedXml = CAST(REPLACE(EncodedXml,'utf-16','utf-8') AS xml),
		x.DecodedXmlText = CAST(CAST(REPLACE(EncodedXml,'utf-16','utf-8') AS xml) as varchar(max))	
FROM Xmls x


INSERT EHR.XMLSTEMP (id, _Xml, _Type)
	SELECT 
		X.ID,
		x.DecodedXmlText,
		1
	FROM Xmls x;


-- загружаем идентификаторы Smnn
INSERT INTO XmlsObjectsIds (XmlID, ObjectIdGuid)
SELECT id, cast(c.value('(Smnn/text())[1]', 'VARCHAR(100)') as uniqueidentifier) AS XmlData
FROM Xmls
	 CROSS APPLY DecodedXml.nodes('/HospitalRecipeSerialize/Components/Component') AS t(c)
WHERE c.value('(Smnn/text())[1]', 'VARCHAR(100)') is not null


-- загружаем идентификаторы EhrRusVls373linkBox
INSERT INTO XmlsObjectsIds (XmlID, ObjectIdGuid)
SELECT id, cast(c.value('(EhrRusVls373linkBox/text())[1]', 'VARCHAR(100)') as uniqueidentifier) AS XmlData
FROM Xmls
	 CROSS APPLY DecodedXml.nodes('/HospitalRecipeSerialize') AS t(c)
WHERE c.value('(EhrRusVls373linkBox/text())[1]', 'VARCHAR(100)') is not null


-- Ищем интовые идентификаторы для Smnn и EhrRusVls373linkBox
UPDATE xo
SET xo.ObjectIdInt = e.EhrRusIdInt
FROM EHR.EHR_GUID_INT e
JOIN XmlsObjectsIds xo on e.EhrRusIdGuid = xo.ObjectIdGuid
JOIN Xmls x on x.ID = xo.XmlID
WHERE e.TableName IN ('EHR.T_EHR_RUS_9A9G09E8KL', 'EHR.T_EHR_RUS_VLS373')


-- Обновляем guid На int в xml
UPDATE x
SET x.DecodedXmlText = REPLACE(x.DecodedXmlText, xo.ObjectIdGuid, e.EhrRusIdInt)
FROM XmlsObjectsIds xo 
JOIN EHR.EHR_GUID_INT e on e.EhrRusIdGuid = xo.ObjectIdGuid
JOIN Xmls x on x.ID = xo.XmlID 


-- Ставим Null для полей, которые не нашлись в EHR.EHR_GUID_INT 
UPDATE x
SET x.DecodedXmlText = 
	REPLACE
	(
		REPLACE
			(
				x.DecodedXmlText, 
				CONCAT('<Smnn>', xo.ObjectIdGuid, '</Smnn>'), 
				'<Smnn xsi:nil="true"/>'
			), 
		CONCAT('<EhrRusVls373linkBox>', xo.ObjectIdGuid, '</EhrRusVls373linkBox>'), 
		'<EhrRusVls373linkBox xsi:nil="true"/>')
FROM  XmlsObjectsIds xo 
JOIN Xmls x on x.ID = xo.XmlID 
WHERE xo.ObjectIdInt = -1


-- Обновляем xml-ки
UPDATE hd
SET hd.Recipe = x.DecodedXmlText
FROM AKUZ.T_HOSPITAL_DESTINATION_DICTIONARY hd
JOIN Xmls x on x.ID = hd.HospitalDestinationDictionaryID


-------------------------AKUZ.T_STANDART_LINK_ROUTE RouteType = 2------------------------------------------
delete from Xmls;


delete from XmlsObjectsIds;


INSERT INTO Xmls(id, EncodedXml)
SELECT 
	st.StandartLinkRouteID, 
	CAST(N'' AS XML ).value('xs:base64Binary(sql:column("RECIPE"))', 'VARBINARY(MAX)') as x  
FROM AKUZ.T_STANDART_LINK_ROUTE st
WHERE st.RouteType = 2


UPDATE x
	SET 
		x.DecodedXml = CAST(REPLACE(EncodedXml,'utf-16','utf-8') AS xml),
		x.DecodedXmlText = CAST(CAST(REPLACE(EncodedXml,'utf-16','utf-8') AS xml) AS VARCHAR(MAX))
FROM Xmls x


INSERT EHR.XMLSTEMP (id, _Xml, _Type)
	SELECT 
		X.ID,
		x.DecodedXmlText,
		2
	FROM Xmls x;


-- загружаем идентификаторы Smnn
INSERT INTO XmlsObjectsIds (XmlID, ObjectIdGuid)
SELECT id, cast(c.value('(Smnn/text())[1]', 'VARCHAR(100)') as uniqueidentifier) AS XmlData
FROM Xmls
	 CROSS APPLY DecodedXml.nodes('/HospitalRecipeSerialize/Components/Component') AS t(c)
WHERE c.value('(Smnn/text())[1]', 'VARCHAR(100)') is not null


-- загружаем идентификаторы EhrRusVls373linkBox
INSERT INTO XmlsObjectsIds (XmlID, ObjectIdGuid)
SELECT id, cast(c.value('(EhrRusVls373linkBox/text())[1]', 'VARCHAR(100)') as uniqueidentifier) AS XmlData
FROM Xmls
	 CROSS APPLY DecodedXml.nodes('/HospitalRecipeSerialize') AS t(c)
WHERE c.value('(EhrRusVls373linkBox/text())[1]', 'VARCHAR(100)') is not null


-- Ищем интовые идентификаторы для Smnn и EhrRusVls373linkBox
UPDATE xo
SET xo.ObjectIdInt = e.EhrRusIdInt
FROM EHR.EHR_GUID_INT e
JOIN XmlsObjectsIds xo on e.EhrRusIdGuid = xo.ObjectIdGuid
JOIN Xmls x on x.ID = xo.XmlID
WHERE e.TableName IN ('EHR.T_EHR_RUS_9A9G09E8KL', 'EHR.T_EHR_RUS_VLS373')


-- Обновляем guid На int в xml
UPDATE x
SET x.DecodedXmlText = REPLACE(x.DecodedXmlText, xo.ObjectIdGuid, e.EhrRusIdInt)
FROM XmlsObjectsIds xo 
JOIN EHR.EHR_GUID_INT e on e.EhrRusIdGuid = xo.ObjectIdGuid
JOIN Xmls x on x.ID = xo.XmlID 


-- Ставим Null для полей, которые не нашлись в EHR.EHR_GUID_INT 
UPDATE x
SET x.DecodedXmlText = 
	REPLACE
	(
		REPLACE
			(
				x.DecodedXmlText, 
				CONCAT('<Smnn>', xo.ObjectIdGuid, '</Smnn>'), 
				'<Smnn xsi:nil="true"/>'
			), 
		CONCAT('<EhrRusVls373linkBox>', xo.ObjectIdGuid, '</EhrRusVls373linkBox>'), 
		'<EhrRusVls373linkBox xsi:nil="true"/>')
FROM  XmlsObjectsIds xo 
JOIN Xmls x on x.ID = xo.XmlID 
WHERE xo.ObjectIdInt = -1


-- Обновляем xml-ки
UPDATE ST
SET st.Recipe = x.DecodedXmlText
FROM AKUZ.T_STANDART_LINK_ROUTE st
JOIN Xmls x on x.ID = st.StandartLinkRouteID;


-------------------------AKUZ.T_STANDART_LINK_ROUTE RouteType = 3------------------------------------------
DELETE FROM Xmls


DELETE FROM XmlsObjectsIds


INSERT INTO Xmls(id, EncodedXml)
SELECT 
	st.StandartLinkRouteID, 
	CAST(N'' AS XML ).value('xs:base64Binary(sql:column("RECIPE"))', 'VARBINARY(MAX)') as x  
FROM AKUZ.T_STANDART_LINK_ROUTE st
WHERE st.RouteType = 3


UPDATE x
	SET 
		x.DecodedXml = CAST(REPLACE(EncodedXml,'utf-16','utf-8') AS xml),
		x.DecodedXmlText = CAST(CAST(REPLACE(EncodedXml,'utf-16','utf-8') AS xml) AS VARCHAR(MAX))
FROM Xmls x



INSERT EHR.XMLSTEMP (id, _Xml, _Type)
	SELECT 
		X.ID,
		x.DecodedXmlText,
		3
	FROM Xmls x;


-- загружаем идентификаторы RegimenID
INSERT INTO XmlsObjectsIds (XmlID, ObjectIdGuid)
SELECT id, cast(c.value('(RegimenID/text())[1]', 'VARCHAR(100)') as uniqueidentifier) AS XmlData
FROM Xmls
	 CROSS APPLY DecodedXml.nodes('/HospitalRegimenObjectSerialise') AS t(c)
where c.value('(RegimenID/text())[1]', 'VARCHAR(100)') is not null


-- Ищем интовые идентификаторы для RegimenID
UPDATE xo
SET xo.ObjectIdInt = e.EhrRusIdInt
FROM EHR.EHR_GUID_INT e
JOIN XmlsObjectsIds xo on e.EhrRusIdGuid = xo.ObjectIdGuid
JOIN Xmls x on x.ID = xo.XmlID
WHERE e.TableName = 'EHR.T_EHR_RUS_99JSKIULHD'


-- Обновляем guid На int в xml
UPDATE x
SET x.DecodedXmlText = REPLACE(x.DecodedXmlText, xo.ObjectIdGuid, e.EhrRusIdInt)
FROM XmlsObjectsIds xo 
JOIN Xmls x on x.ID = xo.XmlID 
JOIN EHR.EHR_GUID_INT e on e.EhrRusIdGuid = xo.ObjectIdGuid


-- Ставим Null для полей, которые не нашлись в EHR.EHR_GUID_INT 
UPDATE x
SET x.DecodedXmlText = 
		REPLACE
			(
				x.DecodedXmlText, 
				CONCAT('<RegimenID>', xo.ObjectIdGuid, '</RegimenID>'), 
				'<RegimenID xsi:nil="true"/>'
			)
FROM  XmlsObjectsIds xo 
JOIN Xmls x on x.ID = xo.XmlID 
WHERE xo.ObjectIdInt = -1


-- Обновляем xml-ки
UPDATE st
SET st.Recipe = x.DecodedXmlText
FROM AKUZ.T_STANDART_LINK_ROUTE st
JOIN Xmls x on x.ID = st.StandartLinkRouteID;


DROP TABLE Xmls 


DROP TABLE XmlsObjectsIds

END

-------------------------------------------------------------------------------------------

-- DROP TABLE EHR.EHR_REF_INDEX _COLUMN
-- DROP TABLE EHR.EHR_REF_INDEX 
-- DROP TABLE EHR.EHR_REFERENCE_INFO 
-- DROP TABLE EHR.REFERENCES_ON_EHR
-- DROP TABLE EHR.EHRS 
-- DROP TABLE EHR.SQL_TABLE
-- DROP TABLE EHR.EHR_GUID_INT 
-- DROP TABLE EHR.ELEMENT_EHR_REPLACEMENT_IDX
-- DROP TABLE EHR.XMLSTEMP

-------------------------------------------------------------------------------------------

SET NOCOUNT OFF