
-- Сопутствующие заболевания 

ALTER TABLE AKUZ.T_ACCOMPANYING_DISEASES ADD IsOsnCompDisease bit NULL;

CREATE TEMP TABLE TAB
(
	ID uuid NULL,

	MovementResult uuid NULL,
	IsOsnCompDisease bit NULL,

	Post uuid NULL,
	RecordDate timestamp NULL,
	RecordTime timestamp NULL,

	AmbulanceEvent uuid NULL,
	AmbulanceCard uuid NULL,
	PatientBase uuid NULL,

	Mkb varchar(8) NULL,
	Diagnosis text NULL,
	RegistrationDate timestamp NULL
);

INSERT INTO TAB (ID, MovementResult, IsOsnCompDisease, Post, RecordDate, RecordTime, AmbulanceEvent, AmbulanceCard, PatientBase, Mkb, Diagnosis, RegistrationDate)
SELECT uuid_generate_v4(), cd.MovementResult, cd.IsOsnCompDisease, mr.Post, mr.RecordDate, mr.RecordTime, acr.AmbulanceEvent, acr.AmbulanceCard, acr.PatientBase, COALESCE(d.Mkb, 'Z00.0'), d.Diagnosis, d.RegistrationDate
FROM AKUZ.T_COMPLICATION_DISEASE cd 
JOIN AKUZ.T_DIAGNOSIS d on cd.MedicalRecordID = d.MedicalRecordID
JOIN AKUZ.T_MEDICAL_RECORD mr on cd.MedicalRecordID = mr.MedicalRecordID
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr on cd.MedicalRecordID = acr.MedicalRecordID;

DO $$DECLARE 
	newEntityID integer := (SELECT EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'ACCOMPANYING_DISEASES' and e.[Schema] = 'AKUZ' LIMIT 1);

BEGIN
INSERT INTO AKUZ.T_MEDICAL_RECORD (MedicalRecordID, Post, type_, RecordDate, RecordTime)
	SELECT t.ID, t.Post, newEntityID, t.RecordDate, t.RecordTime
	FROM TAB t;
	
INSERT INTO AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID, AmbulanceCard, AmbulanceEvent, PostingDate, PatientBase)
	SELECT t.ID, t.AmbulanceCard, t.AmbulanceEvent, t.RegistrationDate, t.PatientBase
	FROM TAB t ;

INSERT INTO AKUZ.T_OUTCOME_OF_INSPECTION (MedicalRecordID, ActiveFindDiagnosis, MKBCode, Diagnosis) 
	SELECT t.ID, 0, t.Mkb, t.Diagnosis
	FROM TAB t;

INSERT INTO AKUZ.T_ACCOMPANYING_DISEASES (MedicalRecordID, OutcomeOfInspection, TypeDisease, IsOsnCompDisease) 
	SELECT t.ID, t.MovementResult, 0, t.IsOsnCompDisease
	FROM TAB t;

DROP TABLE TAB;



CREATE TEMP TABLE COMP  
(
	MedicalRecordID uuid NULL
) ;

INSERT INTO COMP
SELECT MedicalRecordID FROM AKUZ.T_COMPLICATION_DISEASE;


DELETE FROM AKUZ.T_COMPLICATION_DISEASE;

DELETE FROM AKUZ.T_DIAGNOSIS e USING COMP cd WHERE e.MedicalRecordID = cd.MedicalRecordID;

DELETE FROM AKUZ.T_AMBULANCE_CARD_RECORD USING COMP cd WHERE e.MedicalRecordID = cd.MedicalRecordID;

DELETE FROM AKUZ.T_MEDICAL_RECORD e USING COMP cd WHERE e.MedicalRecordID = cd.MedicalRecordID;

DROP TABLE COMP;


CREATE TEMP TABLE ENTITY
(
	EntityID uuid NULL
);

select EntityID 
INTO ENTITY
from VCLib.T_ENTITIES e
where e.EntName in (
'COMPLICATION_DISEASE'
)
and e.Schema = 'AKUZ';

Delete 
From VCLib.T_USER_COLUMNS col
	using VCLib.T_USER_CONTEXTS ctx, ENTITY e 
	where col.UserContextID = ctx.UserContextID and ctx.EntityID = e.EntityID;

Delete 
From VCLib.T_USER_CONTEXTS ctx
	using ENTITY e where  ctx.EntityID = e.EntityID;

delete 
from VCLib.T_DELETED_OBJECT del
	using ENTITY e where  del.Entities = e.EntityID;

delete 
From VCLib.T_TEMPLATE_FIELD_VALUE fv
using VCLib.T_TEMPLATE_FIELD fld, ENTITY e 
where fv.TemplateField = fld.TemplateFieldID and  fld.Entity = e.EntityID;

delete 
From VCLib.T_TEMPLATE_FIELD fld
	using ENTITY e where  fld.Entity = e.EntityID;

delete 
From VCLib.T_Report rep
	using ENTITY e where  rep.EntityID = e.EntityID;

DELETE 
FROM VCLib.T_USER_SEARCHES  us
	using ENTITY e where  us.EntityID = e.EntityID;

delete  
from VCLib.T_ENTITIES_ATTRIBUTES e
	using ENTITY ee where  e.EntityID = ee.EntityID;

delete 
from VCLib.T_ENTITIES e
	using ENTITY ee where  e.EntityID = ee.EntityID;

drop table ENTITY;

DROP TABLE AKUZ.T_COMPLICATION_DISEASE ;
END;
$$
