/***** Object:  Table AKUZ.T_CLARIFYING_QUESTION_ANSWER *****/
CREATE TABLE AKUZ.T_CLARIFYING_QUESTION_ANSWER
(
    "clarifyingquestionanswerid" uuid NOT NULL,
    "code" varchar(255) NOT NULL,
    "name" varchar(255) NOT NULL,
    "begindate" timestamp NOT NULL,
    "enddate" timestamp NULL,
    "clarifyingquestiondictionary" uuid NOT NULL
);
ALTER TABLE AKUZ.T_CLARIFYING_QUESTION_ANSWER ADD CONSTRAINT PK_T_CLARIFYING_QUESTION_ANSWER
PRIMARY KEY (ClarifyingQuestionAnswerID);

ALTER TABLE AKUZ.T_CLARIFYING_QUESTION_ANSWER ADD CONSTRAINT FK_T_CLARIFYING_QUESTION_DICTIONARY_T_CLARIFYING_QUEST_6D4FCA46
FOREIGN KEY("clarifyingquestiondictionary") REFERENCES AKUZ.T_CLARIFYING_QUESTION_DICTIONARY (ClarifyingQuestionDictionaryID);


ALTER TABLE AKUZ.T_ADD_CLARIFY_QUESTION ADD "clarifyingquestionanswer" uuid NULL;
ALTER TABLE AKUZ.T_ADD_CLARIFY_QUESTION ADD CONSTRAINT FK_T_CLARIFYING_QUESTION_ANSWER_T_ADD_CLARIFY_QUESTION_7EFA298E
FOREIGN KEY("clarifyingquestionanswer") REFERENCES AKUZ.T_CLARIFYING_QUESTION_ANSWER (ClarifyingQuestionAnswerID);

ALTER TABLE AKUZ.T_ADD_CLARIFY_QUESTION ADD "clarifyingquestiondictionary" uuid NULL;
ALTER TABLE AKUZ.T_ADD_CLARIFY_QUESTION ADD CONSTRAINT FK_T_CLARIFYING_QUESTION_DICTIONARY_T_ADD_CLARIFY_QUES_19F2D7FB
FOREIGN KEY("clarifyingquestiondictionary") REFERENCES AKUZ.T_CLARIFYING_QUESTION_DICTIONARY (ClarifyingQuestionDictionaryID);


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 uuid_generate_v4(), hiv.Code, hiv.Name, GETDATE(), null,
    (select ClarifyingQuestionDictionaryID 
		from akuz.T_CLARIFYING_QUESTION_DICTIONARY 
		where ClarifyingQuestionDictionaryID = '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
            );
