SELECT
    OBJECT_SCHEMA_NAME(i.object_id)                                       AS schema_name,
    OBJECT_NAME(i.object_id)                                              AS table_name,
    i.name                                                                AS index_name,
    STUFF((SELECT
               ', ' + c.name +
               CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
           FROM sys.index_columns ic
           INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
           WHERE
                 ic.object_id = i.object_id
             AND ic.index_id = i.index_id
             AND ic.is_included_column = 0
           ORDER BY ic.index_column_id
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS key_columns,
    i.is_unique,
    i.fill_factor,
    i.filter_definition,
    STUFF((SELECT
               ', ' + c.name
           FROM sys.index_columns ic
           INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
           WHERE
                 ic.object_id = i.object_id
             AND ic.index_id = i.index_id
             AND ic.is_included_column = 1
           ORDER BY ic.index_column_id
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS included_columns
INTO #TMP
FROM sys.indexes i
WHERE
      i.object_id = OBJECT_ID('AKUZ.T_ANALYSIS')
  AND i.name NOT IN ('UQ_T_ANALYSIS_AnalysisDiagnosticResultLink', 'PK_T_ANALYSIS')
ORDER BY
    i.name
GO

IF OBJECT_ID('dbo.TEMP_ANALYSIS_SGUID') IS NULL
BEGIN
	CREATE TABLE dbo.TEMP_ANALYSIS_SGUID
	(
		ID INT IDENTITY(1,1) NOT NULL,
		Guid uniqueidentifier,
		SGuid uniqueidentifier
	);
	
	CREATE SEQUENCE dbo.TempAnalysisSeq AS BIGINT START WITH 1 INCREMENT BY 1; 
	
	INSERT INTO dbo.TEMP_ANALYSIS_SGUID(Guid, SGuid)
	SELECT AnalysisID,
		   dbo.SequentialGuidFrom(
				DATEADD(MILLISECOND, NEXT VALUE FOR dbo.TempAnalysisSeq, '19000101')
			)
	FROM AKUZ.T_ANALYSIS
	OPTION (MAXDOP 0);

	
	CREATE INDEX idx_temp_analysis_sguid on dbo.TEMP_ANALYSIS_SGUID(guid) WITH (MAXDOP = 0);

END
ELSE
BEGIN
	
	;WITH CTE AS(
	SELECT 
	  A.Guid,
	  ROW_NUMBER() OVER(PARTITION BY A.Guid ORDER BY A.Guid) AS R
	FROM dbo.TEMP_ANALYSIS_SGUID A
	)
	DELETE C
	FROM CTE C
	WHERE R > 1;
	
	INSERT INTO dbo.TEMP_ANALYSIS_SGUID(Guid, SGuid)
	SELECT AnalysisID,
		   dbo.SequentialGuidFrom(
				DATEADD(MILLISECOND, NEXT VALUE FOR dbo.TempAnalysisSeq, '19000101')
			)
	FROM AKUZ.T_ANALYSIS a
	WHERE NOT EXISTS (
		SELECT 1 
		FROM dbo.TEMP_ANALYSIS_SGUID s
		WHERE s.Guid = a.AnalysisID
	)	
	OPTION (MAXDOP 0);
	
END
go

CREATE TABLE AKUZ.T_ANALYSIS_NEW
(
	[AnalysisID] uniqueidentifier NOT NULL,
    [TreeLevel] int NOT NULL,
    [Name] int NOT NULL,
    [Value] varchar(1000) NULL,
    [CalcLexem] int NULL,
    [ValidateLexem] int NULL,
    [NormativLexem] int NULL,
    [CheckBoxValue] bit NULL,
    [TemplateGuid] uniqueidentifier NOT NULL,
    [NormativText] int NULL,
    [Measurement] int NULL,
    [FormWidth] int NULL,
    [FormHeight] int NULL,
    [ColumnsConfig] int NULL,
    [ColumnCount] int NULL,
    [TabOrder] int NOT NULL,
    [ShowBorder] bit NOT NULL,
    [Date] datetime NULL,
    [MultilineText] varchar(2000) NULL,
    [CalcReadOnly] bit NOT NULL,
    [ParentID] uniqueidentifier NULL,
    [AnalysisDictionary] uniqueidentifier NULL,
    [RootParent] uniqueidentifier NULL,
    [ValueType] int NOT NULL,
    [Code] varchar(255) NULL,
    [CodeSystem] varchar(255) NULL,
    [AnalysisDiagnosticResultLink] uniqueidentifier NULL
)
GO

INSERT INTO AKUZ.T_ANALYSIS_NEW (
	[AnalysisID],
    [TreeLevel] ,
    [Name],
    [Value] ,
    [CalcLexem] ,
    [ValidateLexem] ,
    [NormativLexem] ,
    [CheckBoxValue] ,
    [TemplateGuid] ,
    [NormativText] ,
    [Measurement] ,
    [FormWidth] ,
    [FormHeight] ,
    [ColumnsConfig] ,
    [ColumnCount] ,
    [TabOrder] ,
    [ShowBorder],
    [Date] ,
    [MultilineText] ,
    [CalcReadOnly] ,
    [ParentID] ,
    [AnalysisDictionary] ,
    [RootParent] ,
    [ValueType] ,
    [Code] ,
    [CodeSystem],
    [AnalysisDiagnosticResultLink]
)
SELECT
	at.SGuid,
    a.[TreeLevel] ,
    a.[Name],
    a.[Value] ,
    a.[CalcLexem] ,
    a.[ValidateLexem] ,
    a.[NormativLexem] ,
    a.[CheckBoxValue] ,
    a.[TemplateGuid] ,
    a.[NormativText] ,
    a.[Measurement] ,
    a.[FormWidth] ,
    a.[FormHeight] ,
    a.[ColumnsConfig] ,
    a.[ColumnCount] ,
    a.[TabOrder] ,
    a.[ShowBorder],
    a.[Date] ,
    a.[MultilineText] ,
    a.[CalcReadOnly] ,
    ap1.SGuid,
    a.[AnalysisDictionary] ,
    ap2.SGuid,
    a.[ValueType] ,
    a.[Code] ,
    a.[CodeSystem],
    a.[AnalysisDiagnosticResultLink]
FROM AKUZ.T_ANALYSIS a
JOIN dbo.TEMP_ANALYSIS_SGUID at on a.AnalysisID = at.Guid
LEFT JOIN dbo.TEMP_ANALYSIS_SGUID ap1 on ap1.Guid = a.ParentID
LEFT JOIN dbo.TEMP_ANALYSIS_SGUID ap2 on ap2.Guid = a.RootParent
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [CheckAnalysisDiagnosticResultLink]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_S_ANALYSIS_NAME_T_ANALYSIS_Name]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_S_ANALYSIS_CALCLEXEM_T_ANALYSIS_CalcLexem]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_S_ANALYSIS_VALIDATELEXEM_T_ANALYSIS_ValidateLexem]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_S_ANALYSIS_NORMATIVTEXT_T_ANALYSIS_NormativText]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_S_ANALYSIS_MEASUREMENT_T_ANALYSIS_Measurement]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_S_ANALYSIS_COLUMNSCONFIG_T_ANALYSIS_ColumnsConfig]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_T_ANALYSIS_T_ANALYSIS_ParentID]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_T_ANALYSIS_DICTIONARY_T_ANALYSIS_AnalysisDictionary]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] DROP CONSTRAINT [FK_T_ANALYSIS_T_ANALYSIS_RootParent]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS_INFO_ROUTE_LINK]  DROP CONSTRAINT [FK_T_ANALYSIS_T_ANALYSIS_INFO_ROUTE_LINK_Analysis]
GO

DROP TABLE AKUZ.T_ANALYSIS
GO

EXEC sp_rename 'AKUZ.T_ANALYSIS_NEW', 'T_ANALYSIS';

;with cte as (
	SELECT l.AnalysisInfoRouteLinkID, t.SGuid
	FROM AKUZ.T_ANALYSIS_INFO_ROUTE_LINK l
	JOIN dbo.TEMP_ANALYSIS_SGUID t ON t.Guid = l.Analysis
)
UPDATE l
SET Analysis = c.SGuid
FROM AKUZ.T_ANALYSIS_INFO_ROUTE_LINK l
join cte c on c.AnalysisInfoRouteLinkID = l.AnalysisInfoRouteLinkID
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [PK_T_ANALYSIS]
PRIMARY KEY CLUSTERED ([AnalysisID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_NAME_T_ANALYSIS_Name]
FOREIGN KEY([Name]) REFERENCES [AKUZ].[S_ANALYSIS_Name] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_CALCLEXEM_T_ANALYSIS_CalcLexem]
FOREIGN KEY([CalcLexem]) REFERENCES [AKUZ].[S_ANALYSIS_CalcLexem] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_VALIDATELEXEM_T_ANALYSIS_ValidateLexem]
FOREIGN KEY([ValidateLexem]) REFERENCES [AKUZ].[S_ANALYSIS_ValidateLexem] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_NORMATIVLEXEM_T_ANALYSIS_NormativLexem]
FOREIGN KEY([NormativLexem]) REFERENCES [AKUZ].[S_ANALYSIS_NormativLexem] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_NORMATIVTEXT_T_ANALYSIS_NormativText]
FOREIGN KEY([NormativText]) REFERENCES [AKUZ].[S_ANALYSIS_NormativText] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_MEASUREMENT_T_ANALYSIS_Measurement]
FOREIGN KEY([Measurement]) REFERENCES [AKUZ].[S_ANALYSIS_Measurement] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_S_ANALYSIS_COLUMNSCONFIG_T_ANALYSIS_ColumnsConfig]
FOREIGN KEY([ColumnsConfig]) REFERENCES [AKUZ].[S_ANALYSIS_ColumnsConfig] ([Id])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_T_ANALYSIS_T_ANALYSIS_ParentID]
FOREIGN KEY([ParentID]) REFERENCES [AKUZ].[T_ANALYSIS] ([AnalysisID])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_T_ANALYSIS_DICTIONARY_T_ANALYSIS_AnalysisDictionary]
FOREIGN KEY([AnalysisDictionary]) REFERENCES [AKUZ].[T_ANALYSIS_DICTIONARY] ([AnalysisDictionaryID])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_T_ANALYSIS_T_ANALYSIS_RootParent]
FOREIGN KEY([RootParent]) REFERENCES [AKUZ].[T_ANALYSIS] ([AnalysisID])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD CONSTRAINT [FK_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_T_ANALYSIS_AnalysisDiagnosticResultLink]
FOREIGN KEY([AnalysisDiagnosticResultLink]) REFERENCES [AKUZ].[T_ANALYSIS_DIAGNOSTIC_RESULT_LINK] ([AnalysisDiagnosticResultLinkID])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS_INFO_ROUTE_LINK] ADD CONSTRAINT [FK_T_ANALYSIS_T_ANALYSIS_INFO_ROUTE_LINK_Analysis]
FOREIGN KEY([Analysis]) REFERENCES [AKUZ].[T_ANALYSIS] ([AnalysisID])
GO

ALTER TABLE [AKUZ].[T_ANALYSIS] ADD [AnalysisDiagnosticResultLink_UQ] AS (CASE WHEN [AnalysisDiagnosticResultLink] IS NOT NULL THEN NULL ELSE [AnalysisID] END)
GO

CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_ANALYSIS_AnalysisDiagnosticResultLink] ON [AKUZ].[T_ANALYSIS] ([AnalysisDiagnosticResultLink] ASC, [AnalysisDiagnosticResultLink_UQ] ASC) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_ANALYSIS]  WITH CHECK ADD  CONSTRAINT [CheckAnalysisDiagnosticResultLink] CHECK  (([ParentID] IS NULL AND [AnalysisDiagnosticResultLink] IS NOT NULL OR [ParentID] IS NOT NULL AND [AnalysisDiagnosticResultLink] IS NULL))
GO

DECLARE @schema_name NVARCHAR(128),
        @table_name NVARCHAR(128),
        @index_name NVARCHAR(128),
        @key_columns NVARCHAR(MAX),
        @included_columns NVARCHAR(MAX),
        @is_unique BIT,
        @fill_factor INT,
        @filter_definition NVARCHAR(MAX),
        @sql NVARCHAR(MAX);

DECLARE cur CURSOR FOR
SELECT schema_name,
       table_name,
       index_name,
       key_columns,
       included_columns,
       is_unique,
       fill_factor,
       filter_definition
FROM #TMP;

OPEN cur;
FETCH NEXT FROM cur INTO @schema_name, @table_name, @index_name, @key_columns, @included_columns, @is_unique, @fill_factor, @filter_definition;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sql = 'CREATE ' + CASE WHEN @is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 'NONCLUSTERED INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + '] (' + @key_columns + ')';

	IF LEN(@included_columns) > 0
        SET @sql = @sql + ' INCLUDE (' + @included_columns + ')';

    IF @fill_factor > 0 AND @fill_factor < 100
        SET @sql = @sql + ' WITH (FILLFACTOR = ' + CAST(@fill_factor AS NVARCHAR(3)) + ')';

    IF @filter_definition IS NOT NULL AND LEN(@filter_definition) > 0
        SET @sql = @sql + ' WHERE ' + @filter_definition;

    SET @sql = @sql  + CHAR(13) + CHAR(10);

	BEGIN TRY
		EXEC (@sql)
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE()
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );
		BREAK
	END CATCH


    FETCH NEXT FROM cur INTO @schema_name, @table_name, @index_name, @key_columns, @included_columns, @is_unique, @fill_factor, @filter_definition;
END

CLOSE cur;
DEALLOCATE cur;

DROP TABLE #TMP
GO

DROP TABLE dbo.TEMP_ANALYSIS_SGUID
GO
