-- Tables

-- Alters

ALTER TABLE AKUZ.T_ANALYSIS ADD "analysisdiagnosticresultlink" uuid NULL;

update AKUZ.T_ANALYSIS a
set AnalysisDiagnosticResultLink = adrl.AnalysisDiagnosticResultLinkID
from AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK adrl 
where adrl.Analysis = a.AnalysisID;


update AKUZ.T_ANALYSIS_INFO_ROUTE_LINK ai
set Analysis = null
from AKUZ.T_ANALYSIS a
where a.AnalysisID = ai.Analysis and a.ParentID is null and a.AnalysisDiagnosticResultLink is null;

update AKUZ.T_ANALYSIS a 
set ParentID = null, AnalysisDiagnosticResultLink = null
from AKUZ.T_ANALYSIS aa
where aa.AnalysisID = a.ParentID and aa.AnalysisDiagnosticResultLink is null;

delete from AKUZ.T_ANALYSIS where ParentID is null and AnalysisDiagnosticResultLink is null;

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_Analys_2383A94A
FOREIGN KEY("analysisdiagnosticresultlink") REFERENCES AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK (AnalysisDiagnosticResultLinkID);

do $$ begin
IF EXISTS(select * from pg_indexes where schemaname ilike 'AKUZ' and tablename ilike 'T_ANALYSIS' and indexname ilike 'UQ_T_ANALYSIS_AnalysisDiagnosticResultLink')
THEN
drop index AKUZ.UQ_T_ANALYSIS_AnalysisDiagnosticResultLink;
END IF;
end; $$;
ALTER TABLE AKUZ.T_ANALYSIS DROP CONSTRAINT if exists UQ_T_ANALYSIS_AnalysisDiagnosticResultLink;
ALTER TABLE AKUZ.T_ANALYSIS DROP COLUMN if exists AnalysisDiagnosticResultLink_UQ;

CREATE UNIQUE INDEX UQ_T_ANALYSIS_AnalysisDiagnosticResultLink ON AKUZ.T_ANALYSIS(AnalysisDiagnosticResultLink) where ParentID is null;

ALTER TABLE AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK DROP CONSTRAINT FK_T_ANALYSIS_T_ANALYSIS_DIAGNOSTIC_RESULT_LINK_Analysis;

ALTER TABLE AKUZ.T_ANALYSIS_DIAGNOSTIC_RESULT_LINK DROP COLUMN "analysis";

