/***** Object:  Table [AKUZ].[T_CLARIFYING_QUESTION_ANSWER] *****/
CREATE TABLE [AKUZ].[T_CLARIFYING_QUESTION_ANSWER]
(
    [ClarifyingQuestionAnswerID] uniqueidentifier NOT NULL,
    [Code] varchar(255) NOT NULL,
    [Name] varchar(255) NOT NULL,
    [BeginDate] datetime NOT NULL,
    [EndDate] datetime NULL,
    [ClarifyingQuestionDictionary] uniqueidentifier NOT NULL
)
ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_CLARIFYING_QUESTION_ANSWER] ADD CONSTRAINT [PK_T_CLARIFYING_QUESTION_ANSWER]
PRIMARY KEY CLUSTERED ([ClarifyingQuestionAnswerID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_CLARIFYING_QUESTION_ANSWER] ADD CONSTRAINT [FK_T_CLARIFYING_QUESTION_DICTIONARY_T_CLARIFYING_QUESTION_ANSWER_ClarifyingQuestionDictionary]
FOREIGN KEY([ClarifyingQuestionDictionary]) REFERENCES [AKUZ].[T_CLARIFYING_QUESTION_DICTIONARY] ([ClarifyingQuestionDictionaryID])
GO

ALTER TABLE [AKUZ].[T_ADD_CLARIFY_QUESTION] ADD [ClarifyingQuestionAnswer] uniqueidentifier NULL
GO

ALTER TABLE [AKUZ].[T_ADD_CLARIFY_QUESTION] ADD [ClarifyingQuestionDictionary] uniqueidentifier NULL
GO

ALTER TABLE [AKUZ].[T_ADD_CLARIFY_QUESTION] ADD CONSTRAINT [FK_T_CLARIFYING_QUESTION_ANSWER_T_ADD_CLARIFY_QUESTION_ClarifyingQuestionAnswer]
FOREIGN KEY([ClarifyingQuestionAnswer]) REFERENCES [AKUZ].[T_CLARIFYING_QUESTION_ANSWER] ([ClarifyingQuestionAnswerID])
GO

ALTER TABLE [AKUZ].[T_ADD_CLARIFY_QUESTION] ADD CONSTRAINT [FK_T_CLARIFYING_QUESTION_DICTIONARY_T_ADD_CLARIFY_QUESTION_ClarifyingQuestionDictionary]
FOREIGN KEY([ClarifyingQuestionDictionary]) REFERENCES [AKUZ].[T_CLARIFYING_QUESTION_DICTIONARY] ([ClarifyingQuestionDictionaryID])
GO

INSERT INTO AKUZ.T_CLARIFYING_QUESTION_DICTIONARY (ClarifyingQuestionDictionaryID, [Name], Code, [Description], PrefillLexem, ValueType, MinValue, MaxValue, Parts, AllowNulls, CodeSystem)
     VALUES ('FF79DB31-E28B-4433-991F-343A0935995B', 'Контингент', 'CONT', null, null, 2, null, null, null, 1, null)

INSERT INTO AKUZ.T_CLARIFYING_QUESTION_ANSWER (ClarifyingQuestionAnswerID, Code, [Name], BeginDate, EndDate, ClarifyingQuestionDictionary)
 select newid(), hiv.Code, hiv.[Name], GETDATE(), null, 'FF79DB31-E28B-4433-991F-343A0935995B'
    from AKUZ.T_PATIENT_HIV_CONTINGENT hiv

insert into akuz.t_SERVICE_LINK_CLARIFY_QUESTION (MedicalService, ServiceLinkClarifyQuestionID, ServiceClarifyingQuestion)
 select medService.MedicalServiceID, NEWID(), 'FF79DB31-E28B-4433-991F-343A0935995B'
    from akuz.T_MEDICAL_SERVICE medService 
    where exists(
        select * from akuz.T_ROUTE_TO_DIAGNOSTICS routeToDiag 
            where routeToDiag.PatientHivContingent is not null
            and medService.MedicalServiceID = routeToDiag.Research
            )	