-- Установка обязательныех полей в MEDICAL_EPISODE

-- Проставление Амбулаторной карты из Случая обращения
UPDATE me
SET me.AmbulanceCard = eb.AmbulanceCard
FROM AKUZ.T_MEDICAL_EPISODE me
JOIN AKUZ.T_EVENT_BASE eb ON eb.EventID = me.AmbulanceEvent
WHERE me.AmbulanceCard IS NULL;
GO

-- Проставление Амбулаторной карты и Случая обращения из Данных по госпитализации
UPDATE me
SET me.AmbulanceCard = eb.AmbulanceCard, me.AmbulanceEvent = eb.EventID
FROM AKUZ.T_MEDICAL_EPISODE me
JOIN AKUZ.T_MOVEMENTS_DIVISION_CARD mdc ON mdc.MedicalEpisodeID = me.MedicalEpisodeID
JOIN AKUZ.T_MOVEMENT m ON m.MovementID = mdc.Movement
JOIN AKUZ.T_EVENT_BASE eb ON eb.EventID = m.EmergencyEvent
WHERE me.AmbulanceCard IS NULL OR me.AmbulanceEvent IS NULL;
GO

-- Проставление Амбулаторной карты и Случая обращения из Первичного осмотра в поликлинике
UPDATE me
SET me.AmbulanceCard = eb.AmbulanceCard, me.AmbulanceEvent = eb.EventID
FROM AKUZ.T_MEDICAL_EPISODE me
JOIN AKUZ.T_MEDICAL_EXAMINATION_DATA med ON med.MedicalEpisodeID = me.MedicalEpisodeID
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = med.MedicalExaminationMain
JOIN AKUZ.T_EVENT_BASE eb ON eb.EventID = acr.AmbulanceEvent
WHERE me.AmbulanceCard IS NULL OR me.AmbulanceEvent IS NULL;
GO

-- Удаление индексов
IF EXISTS(SELECT * FROM sys.indexes i WHERE i.[object_id] = OBJECT_ID('AKUZ.T_MEDICAL_EPISODE', 'U') AND i.name = 'IX_MEDICAL_EPISODE_AmbulanceEvent')
	DROP INDEX [IX_MEDICAL_EPISODE_AmbulanceEvent] ON [AKUZ].[T_MEDICAL_EPISODE];
GO

IF EXISTS(SELECT * FROM sys.indexes i WHERE i.[object_id] = OBJECT_ID('AKUZ.T_MEDICAL_EPISODE', 'U') AND i.name = 'IX_MEDICAL_EPISODE_AmbulanceCard_OutcomeRecord_Mkb')
	DROP INDEX [IX_MEDICAL_EPISODE_AmbulanceCard_OutcomeRecord_Mkb] ON [AKUZ].[T_MEDICAL_EPISODE];
GO

-- Установка обязательности на поля
ALTER TABLE AKUZ.T_MEDICAL_EPISODE ALTER COLUMN AmbulanceCard UNIQUEIDENTIFIER NOT NULL;
GO

ALTER TABLE AKUZ.T_MEDICAL_EPISODE ALTER COLUMN AmbulanceEvent UNIQUEIDENTIFIER NOT NULL;
GO

-- Востановление индексов
CREATE NONCLUSTERED INDEX [IX_MEDICAL_EPISODE_AmbulanceEvent] ON [AKUZ].[T_MEDICAL_EPISODE] ([AmbulanceEvent]) WITH (FILLFACTOR = 95, MAXDOP = 0, SORT_IN_TEMPDB = ON)
GO

CREATE INDEX [IX_MEDICAL_EPISODE_AmbulanceCard_OutcomeRecord_Mkb] ON [AKUZ].[T_MEDICAL_EPISODE] ([AmbulanceCard], [OutcomeRecord], [Mkb])  WITH (FILLFACTOR = 100, MAXDOP = 0, SORT_IN_TEMPDB = ON);
GO