 -- Заполенение нарк после создания таблицы (предыдущий чекин по дн)
INSERT INTO AKUZ.T_NARCOLOGY_DISPANSERISATION_INIT (MedicalRecordID) 
SELECT mr.MedicalRecordID 
FROM AKUZ.T_MENTAL_DISPANSERISATION_INIT mdi 
JOIN AKUZ.T_MEDICAL_RECORD mr ON mdi.MedicalRecordID = mr.MedicalRecordID
WHERE mr.type_ = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'NARCOLOGY_DISPANSERISATION_INIT')
AND NOT EXISTS(SELECT * FROM AKUZ.T_NARCOLOGY_DISPANSERISATION_INIT ndi WHERE ndi.MedicalRecordID = mr.MedicalRecordID);


 -- Добавляем колонку MentalCheckUpStatusEnd
ALTER TABLE [AKUZ].[T_MENTAL_DISPANSERISATION_END] ADD [MentalCheckUpStatusEnd] int NOT NULL CONSTRAINT [df_MentalCheckUpStatusEnd] DEFAULT 0 WITH VALUES
GO
ALTER TABLE [AKUZ].[T_MENTAL_DISPANSERISATION_END] DROP CONSTRAINT [df_MentalCheckUpStatusEnd]
GO

-- Обновляем MentalCheckUpStatusEnd для значений CheckUpStatusEnd
UPDATE AKUZ.T_MENTAL_DISPANSERISATION_END
SET MentalCheckUpStatusEnd = CASE 
    WHEN CheckUpStatusEnd = 1 THEN 24307
    WHEN CheckUpStatusEnd = 2 THEN 24308
    WHEN CheckUpStatusEnd = 3 THEN 24309
    WHEN CheckUpStatusEnd = 4 THEN 24310
    WHEN CheckUpStatusEnd = 5 THEN 24311
    WHEN CheckUpStatusEnd = 6 THEN 24305
    WHEN CheckUpStatusEnd = 7 THEN 24312
    WHEN CheckUpStatusEnd = 8 THEN 24313
    WHEN CheckUpStatusEnd = 9 THEN 24314
    WHEN CheckUpStatusEnd = 10 THEN 24304
    WHEN CheckUpStatusEnd = 11 THEN 24306
    ELSE 24314
END;

-- Обновляем MentalCheckUpStatusEnd для значений CheckUpNarcStatusEnd
UPDATE AKUZ.T_MENTAL_DISPANSERISATION_END
SET MentalCheckUpStatusEnd = CASE 
    WHEN CheckUpNarcStatusEnd = 0 THEN 24301
    WHEN CheckUpNarcStatusEnd = 1 THEN 24302
    WHEN CheckUpNarcStatusEnd = 2 THEN 24303
    WHEN CheckUpNarcStatusEnd = 3 THEN 24305
    WHEN CheckUpNarcStatusEnd = 4 THEN 24315
    WHEN CheckUpNarcStatusEnd = 5 THEN 24304
    WHEN CheckUpNarcStatusEnd = 6 THEN 24306
    WHEN CheckUpNarcStatusEnd = 7 THEN 24316
	WHEN CheckUpNarcStatusEnd = 8 THEN 24317
    ELSE 24314
END;

 -- Добавляем foreignKey
ALTER TABLE [AKUZ].[T_MENTAL_DISPANSERISATION_END] ADD CONSTRAINT [FK_T_ENUMS_T_MENTAL_DISPANSERISATION_END_MentalCheckUpStatusEnd]
FOREIGN KEY([MentalCheckUpStatusEnd]) REFERENCES [VCLib].[T_ENUMS] ([EnumID])
GO


-- Удаляем CheckUpStatusEnd и CheckUpNarcStatusEnd

ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_END DROP COLUMN CheckUpStatusEnd;
ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_END DROP COLUMN CheckUpNarcStatusEnd