if (not exists (select * from sys.indexes i where i.object_id = object_id('AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK', 'U') and i.name = 'IX_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_Analysis')) 
	CREATE index IX_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_Analysis ON AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK(Analysis) WITH (SORT_IN_TEMPDB = ON, FILLFACTOR = 95, MAXDOP = 0);
Go

ALTER TABLE AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK DROP CONSTRAINT FK_T_ANALYSIS_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_Analysis
GO

if exists(select* from sys.indexes i where i.[object_id] = OBJECT_ID('AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK', 'U') and i.name = 'UQ_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_DiagnosticsResult')
	drop index UQ_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_DiagnosticsResult on AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK;
go
/*** ÂÍÈÌÀÍÈÅ! ÑÎÇÄÀÅÒÑß ÓÍÈÊÀËÜÍÛÉ ÈÍÄÅÊÑ, ÏÎËÅ ÍÅ ÌÎÆÅÒ ÁÛÒÜ NULLABLE ***/
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_DiagnosticsResult] ON [AKUZ].[T_ANALYSIS_DIAGNOSTIC_RESULT_LINK] ([DiagnosticsResult] ASC) ON [PRIMARY]
GO

ALTER TABLE AKUZ.T_ANALYSIS ADD AnalysisDiagnosticResultLink uniqueidentifier
GO

update a
set a.AnalysisDiagnosticResultLink = adrl.AnalysisDiagnosticResultLinkID
from AKUZ.T_ANALYSIS a
join AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK adrl on adrl.Analysis = a.AnalysisID
OPTION (MAXDOP 0)
GO


update ai
set ai.Analysis = null
from AKUZ.T_ANALYSIS_INFO_ROUTE_LINK ai
join AKUZ.T_ANALYSIS a on a.AnalysisID = ai.Analysis
where a.ParentID is null and a.AnalysisDiagnosticResultLink is null
OPTION (MAXDOP 0)

update a
set a.ParentID = null, a.AnalysisDiagnosticResultLink = null
from AKUZ.T_ANALYSIS a 
join AKUZ.T_ANALYSIS aa on aa.AnalysisID = a.RootParent
where aa.AnalysisDiagnosticResultLink is null
OPTION (MAXDOP 0)

delete from AKUZ.T_ANALYSIS where ParentID is null and AnalysisDiagnosticResultLink is null
OPTION (MAXDOP 0)

ALTER TABLE AKUZ.T_ANALYSIS ADD CONSTRAINT CheckAnalysisDiagnosticResultLink CHECK ((ParentID IS NULL AND AnalysisDiagnosticResultLink IS NOT NULL) or (ParentID IS NOT NULL AND AnalysisDiagnosticResultLink IS NULL))

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

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'AKUZ.T_ANALYSIS') AND name = N'UQ_T_ANALYSIS_AnalysisDiagnosticResultLink' and is_unique_constraint <> 1)
DROP INDEX UQ_T_ANALYSIS_AnalysisDiagnosticResultLink ON AKUZ.T_ANALYSIS
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'AKUZ.T_ANALYSIS') AND name = N'UQ_T_ANALYSIS_AnalysisDiagnosticResultLink' and is_unique_constraint = 1)
ALTER TABLE AKUZ.T_ANALYSIS DROP CONSTRAINT UQ_T_ANALYSIS_AnalysisDiagnosticResultLink
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'AKUZ.T_ANALYSIS') AND name = N'AnalysisDiagnosticResultLink_UQ')
ALTER TABLE AKUZ.T_ANALYSIS DROP COLUMN AnalysisDiagnosticResultLink_UQ
GO

/*** ÂÍÈÌÀÍÈÅ! ÑÎÇÄÀÅÒÑß ÓÍÈÊÀËÜÍÛÉ ÈÍÄÅÊÑ, ÏÎËÅ ÍÅ ÌÎÆÅÒ ÁÛÒÜ NULLABLE ***/
CREATE UNIQUE INDEX UQ_T_ANALYSIS_AnalysisDiagnosticResultLink  ON AKUZ.T_ANALYSIS (AnalysisDiagnosticResultLink ASC)
where ParentID is null
GO

if (exists (select * from sys.indexes i where i.object_id = object_id('AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK', 'U') and i.name = 'IX_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_Analysis')) 
	drop index IX_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_Analysis ON AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK;
Go

ALTER TABLE AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK DROP COLUMN Analysis
GO


CREATE INDEX IX_T_ANALYSIS_AnalysisDiagnosticResultLink ON AKUZ.T_ANALYSIS(AnalysisDiagnosticResultLink) WITH (FILLFACTOR=95, SORT_IN_TEMPDB = ON, MAXDOP = 0)
