-- создание темп листа реаниматолога 
-- Tables

/***** Object:  Table [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] *****/
CREATE TABLE [AKUZ].[T_REANIMATION_TEMPERATURE_LIST]
(
    [ReanimationTemperatureListID] uniqueidentifier NOT NULL,
    [TemperatureListNew] uniqueidentifier NOT NULL,
    [Edema] bit NULL,
    [ConsciousLevel] int NULL,
    [GlasgowScaleScore] int NULL,
    [Photoreaction] bit NULL,
    [Pupils] int NULL,
    [BreathMethods] int NULL,
    [FIO2] varchar(255) NULL,
    [f] int NULL,
    [Vt] int NULL,
    [MV] int NULL,
    [Pinsp] int NULL,
    [Psup] int NULL,
    [PEEP] int NULL,
    [Breath] int NULL,
    [Hemodynamics] int NULL,
    [Rhytm] int NULL,
    [Pulse] int NOT NULL,
    [Tongue] int NOT NULL,
    [Stomach] int NOT NULL,
    [Gases] bit NULL,
    [DrainagePosition] varchar(255) NULL,
    [DrenageCondition] bit NULL,
    [DrenageDischarged] varchar(255) NULL,
    [DiuresisMethod] int NULL,
    [Diuresis] int NULL,
    [UrineVolume] int NULL,
    [UrineColor] varchar(255) NULL,
    [CriticalState] int NOT NULL
)
ON [PRIMARY]
GO

-- Alters

ALTER TABLE [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] ADD CONSTRAINT [PK_T_REANIMATION_TEMPERATURE_LIST]
PRIMARY KEY CLUSTERED ([ReanimationTemperatureListID]) ON [PRIMARY]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_REANIMATION_TEMPERATURE_LIST]') AND name = N'UQ_T_REANIMATION_TEMPERATURE_LIST_TemperatureListNew' and is_unique_constraint <> 1)
DROP INDEX [UQ_T_REANIMATION_TEMPERATURE_LIST_TemperatureListNew] ON [AKUZ].[T_REANIMATION_TEMPERATURE_LIST]
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_REANIMATION_TEMPERATURE_LIST]') AND name = N'UQ_T_REANIMATION_TEMPERATURE_LIST_TemperatureListNew' and is_unique_constraint = 1)
ALTER TABLE [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] DROP CONSTRAINT [UQ_T_REANIMATION_TEMPERATURE_LIST_TemperatureListNew]
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[AKUZ].[T_REANIMATION_TEMPERATURE_LIST]') AND name = N'TemperatureListNew_UQ')
ALTER TABLE [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] DROP COLUMN [TemperatureListNew_UQ]
GO

/*** ВНИМАНИЕ! СОЗДАЕТСЯ УНИКАЛЬНЫЙ ИНДЕКС, ПОЛЕ НЕ МОЖЕТ БЫТЬ NULLABLE ***/
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_REANIMATION_TEMPERATURE_LIST_TemperatureListNew] ON [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] ([TemperatureListNew] ASC) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] ADD CONSTRAINT [FK_T_TEMPERATURE_LIST_NEW_T_REANIMATION_TEMPERATURE_LIST_TemperatureListNew]
FOREIGN KEY([TemperatureListNew]) REFERENCES [AKUZ].[T_TEMPERATURE_LIST_NEW] ([TemperatureListNewID])
GO




-- создание MONITORED_RECORDS

CREATE TABLE [AKUZ].[T_MONITORED_RECORDS]
(
    [MonitoredRecordsID] uniqueidentifier NOT NULL,
    [MonitoringInformation] varchar(255) NULL,
    [RecordDate] datetime NULL,
    [DateOut] datetime NULL,
    [Post] int NULL,
    [SuperMonitoring] bit NULL,
    [Patient] uniqueidentifier NOT NULL,
    [type_] int NULL,
    [WasReadedByMonitoring] bit NULL,
    [SuperMonitoringPost] int NULL,
    [ActiveMonitoringReasons] int NOT NULL
)
ON [PRIMARY]
GO

-- Alters

ALTER TABLE [AKUZ].[T_MONITORED_RECORDS] ADD CONSTRAINT [PK_T_MONITORED_RECORDS]
PRIMARY KEY CLUSTERED ([MonitoredRecordsID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_MONITORED_RECORDS] ADD CONSTRAINT [FK_T_POST_T_MONITORED_RECORDS_Post]
FOREIGN KEY([Post]) REFERENCES [AKUZ].[T_POST] ([ResourceID])
GO

ALTER TABLE [AKUZ].[T_MONITORED_RECORDS] ADD CONSTRAINT [FK_T_PATIENT_T_MONITORED_RECORDS_Patient]
FOREIGN KEY([Patient]) REFERENCES [AKUZ].[T_PATIENT] ([PatientID])
GO

ALTER TABLE [AKUZ].[T_MONITORED_RECORDS] ADD CONSTRAINT [FK_T_ENTITIES_T_MONITORED_RECORDS_type_]
FOREIGN KEY([type_]) REFERENCES [VCLib].[T_ENTITIES] ([EntityID])
GO

ALTER TABLE [AKUZ].[T_MONITORED_RECORDS] ADD CONSTRAINT [FK_T_POST_T_MONITORED_RECORDS_SuperMonitoringPost]
FOREIGN KEY([SuperMonitoringPost]) REFERENCES [AKUZ].[T_POST] ([ResourceID])
GO


declare @type int = (SELECT top 1 e.EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'MONITORED_PREGNANT_RECORDS' and e.[Schema] = 'AKUZ')


--- перенос данных 


INSERT INTO AKUZ.T_MONITORED_RECORDS(MonitoredRecordsID,
											MonitoringInformation,
											RecordDate, 
											DateOut, 
											Post, 
											Patient,
											WasReadedByMonitoring,
											ActiveMonitoringReasons,
											type_)
SELECT 
		MonitoredPregnantRecordsID, 
		(case
			when lexem is not null then lexem.InformationForDoctor
			when record.ObstetricsRisksAnketa is not null then 'По результатам расчета анкеты рисков' 
			else 'По решению специалиста адкц'
		end),
		record.RecordDate, 
		DateOut, 
		acr.Post, 
		acr.PatientBase,
		WasReadedByMonitoring,
		1,
		@type
FROM AKUZ.T_MONITORED_PREGNANT_RECORDS record
left join akuz.T_MONITORED_PREGNANT_RECORDS_LEXEM lexem
on lexem.MonitoredPregnantRecordsLexemID = record.MonitoredPregnantRecordsLexem
join akuz.T_MEDICAL_RECORD acr
on acr.MedicalRecordID = record.PregnantDispanserisation



EXEC sp_rename 'AKUZ.T_MONITORED_PREGNANT_RECORDS.MonitoredPregnantRecordsID', 'MonitoredRecordsID', 'COLUMN';

ALTER TABLE [AKUZ].[T_MONITORED_PREGNANT_RECORDS] ADD CONSTRAINT [FK_T_MONITORED_RECORDS_T_MONITORED_PREGNANT_RECORDS_MonitoredRecordsID]
FOREIGN KEY([MonitoredRecordsID]) REFERENCES [AKUZ].[T_MONITORED_RECORDS] ([MonitoredRecordsID])
GO

--- удаление колонок в MONITORED_PREGNANT_RECORDS

ALTER TABLE [AKUZ].[T_MONITORED_PREGNANT_RECORDS] DROP CONSTRAINT [FK_T_POST_T_MONITORED_PREGNANT_RECORDS_Post]
GO

ALTER TABLE [AKUZ].[T_MONITORED_PREGNANT_RECORDS] DROP COLUMN [RecordDate]
GO

ALTER TABLE [AKUZ].[T_MONITORED_PREGNANT_RECORDS] DROP COLUMN [WasReadedByMonitoring]
GO

ALTER TABLE [AKUZ].[T_MONITORED_PREGNANT_RECORDS] DROP COLUMN [DateOut]
GO

ALTER TABLE [AKUZ].[T_MONITORED_PREGNANT_RECORDS] DROP COLUMN [Post]
GO


--- создание MONITORED_REANIMATION_RECORDS

CREATE TABLE [AKUZ].[T_MONITORED_REANIMATION_RECORDS]
(
    [MonitoredRecordsID] uniqueidentifier NOT NULL,
    [ReanimationTemperatureList] uniqueidentifier NULL,
    [MovementsDivisionReanimationCard] uniqueidentifier NOT NULL
)
ON [PRIMARY]
GO

-- Alters

ALTER TABLE [AKUZ].[T_MONITORED_REANIMATION_RECORDS] ADD CONSTRAINT [PK_T_MONITORED_REANIMATION_RECORDS]
PRIMARY KEY CLUSTERED ([MonitoredRecordsID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_MONITORED_REANIMATION_RECORDS] ADD CONSTRAINT [FK_T_MONITORED_RECORDS_T_MONITORED_REANIMATION_RECORDS_MonitoredRecordsID]
FOREIGN KEY([MonitoredRecordsID]) REFERENCES [AKUZ].[T_MONITORED_RECORDS] ([MonitoredRecordsID])
GO

ALTER TABLE [AKUZ].[T_MONITORED_REANIMATION_RECORDS] ADD CONSTRAINT [FK_T_REANIMATION_TEMPERATURE_LIST_T_MONITORED_REANIMATION_RECORDS_ReanimationTemperatureList]
FOREIGN KEY([ReanimationTemperatureList]) REFERENCES [AKUZ].[T_REANIMATION_TEMPERATURE_LIST] ([ReanimationTemperatureListID])
GO

ALTER TABLE [AKUZ].[T_MONITORED_REANIMATION_RECORDS] ADD CONSTRAINT [FK_T_MOVEMENTS_DIVISION_REANIMATION_CARD_T_MONITORED_REANIMATION_RECORDS_MovementsDivisionReanimationCard]
FOREIGN KEY([MovementsDivisionReanimationCard]) REFERENCES [AKUZ].[T_MOVEMENTS_DIVISION_REANIMATION_CARD] ([MovementsDivisionCardID])
GO

