IF OBJECT_ID(N'[AKUZ].[f_medical_examination_anamnesismorbi]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_anamnesismorbi]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_anamnesismorbi] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_anamnesismorbi]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_complaint]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_complaint]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_complaint] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_complaint]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_objectivestatus]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_objectivestatus]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_objectivestatus] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_objectivestatus]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_recomendations]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_recomendations]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_recomendations] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_recomendations]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_epidemiological]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_epidemiological]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_epidemiological] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_epidemiological]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_gynaecological]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_gynaecological]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_gynaecological] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_gynaecological]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_drugallergy]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_drugallergy]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_drugallergy] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_drugallergy]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_foodallergy]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_foodallergy]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_foodallergy] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_foodallergy]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_patientanamnesis]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_patientanamnesis]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_patientanamnesis] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_patientanamnesis]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_descr]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_descr]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_descr] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_descr]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_healing]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_healing]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_healing] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_healing]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_protocol]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_protocol]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_protocol] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_protocol]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_statuslocale]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_statuslocale]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_statuslocale] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_statuslocale]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_diagnosisjustification]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_diagnosisjustification]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_diagnosisjustification] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_diagnosisjustification]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_examination]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_examination]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_examination] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_examination]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_expertanamnesis]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_expertanamnesis]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_expertanamnesis] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_expertanamnesis]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_transfusionanamnesis]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_transfusionanamnesis]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_transfusionanamnesis] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_transfusionanamnesis]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_transferreddiseasestext]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_transferreddiseasestext]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_transferreddiseasestext] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_transferreddiseasestext]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_medicationstaken]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_medicationstaken]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_medicationstaken] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_medicationstaken]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

IF OBJECT_ID(N'[AKUZ].[f_medical_examination_decision]', N'U') IS NULL
BEGIN
    CREATE TABLE [AKUZ].[f_medical_examination_decision]
    (
        [id] uniqueidentifier NOT NULL
            CONSTRAINT [pk_f_medical_examination_decision] PRIMARY KEY,
        [value] varchar(max) NULL,
        CONSTRAINT [fk_spread_akuz_medical_examination_decision]
            FOREIGN KEY ([id]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION]
    );
END;
GO

INSERT INTO [AKUZ].[f_medical_examination_anamnesismorbi](id, [value])
SELECT m.MedicalRecordID, m.AnamnesisMorbi
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.AnamnesisMorbi IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_anamnesismorbi] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_complaint](id, [value])
SELECT m.MedicalRecordID, m.Complaint
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Complaint IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_complaint] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_objectivestatus](id, [value])
SELECT m.MedicalRecordID, m.ObjectiveStatus
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.ObjectiveStatus IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_objectivestatus] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_recomendations](id, [value])
SELECT m.MedicalRecordID, m.Recomendations
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Recomendations IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_recomendations] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_epidemiological](id, [value])
SELECT m.MedicalRecordID, m.Epidemiological
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Epidemiological IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_epidemiological] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_gynaecological](id, [value])
SELECT m.MedicalRecordID, m.Gynaecological
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Gynaecological IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_gynaecological] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_drugallergy](id, [value])
SELECT m.MedicalRecordID, m.DrugAllergy
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.DrugAllergy IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_drugallergy] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_foodallergy](id, [value])
SELECT m.MedicalRecordID, m.FoodAllergy
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.FoodAllergy IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_foodallergy] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_patientanamnesis](id, [value])
SELECT m.MedicalRecordID, m.PatientAnamnesis
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.PatientAnamnesis IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_patientanamnesis] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_descr](id, [value])
SELECT m.MedicalRecordID, m.Descr
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Descr IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_descr] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_healing](id, [value])
SELECT m.MedicalRecordID, m.Healing
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Healing IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_healing] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_protocol](id, [value])
SELECT m.MedicalRecordID, m.Protocol
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Protocol IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_protocol] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_statuslocale](id, [value])
SELECT m.MedicalRecordID, m.StatusLocale
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.StatusLocale IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_statuslocale] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_diagnosisjustification](id, [value])
SELECT m.MedicalRecordID, m.DiagnosisJustification
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.DiagnosisJustification IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_diagnosisjustification] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_examination](id, [value])
SELECT m.MedicalRecordID, m.Examination
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Examination IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_examination] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_expertanamnesis](id, [value])
SELECT m.MedicalRecordID, m.ExpertAnamnesis
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.ExpertAnamnesis IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_expertanamnesis] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_transfusionanamnesis](id, [value])
SELECT m.MedicalRecordID, m.TransfusionAnamnesis
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.TransfusionAnamnesis IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_transfusionanamnesis] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_transferreddiseasestext](id, [value])
SELECT m.MedicalRecordID, m.TransferredDiseasesText
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.TransferredDiseasesText IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_transferreddiseasestext] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_medicationstaken](id, [value])
SELECT m.MedicalRecordID, m.MedicationsTaken
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.MedicationsTaken IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_medicationstaken] f WHERE f.id = m.MedicalRecordID);
GO

INSERT INTO [AKUZ].[f_medical_examination_decision](id, [value])
SELECT m.MedicalRecordID, m.Decision
FROM [AKUZ].[T_MEDICAL_EXAMINATION] m
WHERE m.Decision IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM [AKUZ].[f_medical_examination_decision] f WHERE f.id = m.MedicalRecordID);
GO

EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'AnamnesisMorbi', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Complaint', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'ObjectiveStatus', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Recomendations', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Epidemiological', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Gynaecological', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'DrugAllergy', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'FoodAllergy', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'PatientAnamnesis', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Descr', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Healing', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Protocol', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'StatusLocale', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'DiagnosisJustification', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Examination', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'ExpertAnamnesis', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'TransfusionAnamnesis', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'TransferredDiseasesText', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'MedicationsTaken', @Command = 0;
GO
EXEC dbo.sp_AlterDropColumn @TableName = N'AKUZ.T_MEDICAL_EXAMINATION', @ColumnName = N'Decision', @Command = 0;
GO

IF OBJECT_ID(N'[AKUZ].[sp_SyncMedicalExaminationSpreadBatch]', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [AKUZ].[sp_SyncMedicalExaminationSpreadBatch];
END;
GO
