UPDATE me
SET me.DateIn = exam.ExaminationDate, me.DateOut = exam2.ExaminationDate
FROM AKUZ.T_MEDICAL_EPISODE me
JOIN AKUZ.T_MEDICAL_EXAMINATION_DATA med on me.MedicalEpisodeID = med.MedicalEpisodeID
JOIN AKUZ.T_MEDICAL_EXAMINATION exam on exam.MedicalRecordID = med.MedicalExaminationMain
JOIN AKUZ.T_MEDICAL_EXAMINATION exam2 on exam2.MedicalRecordID = med.LastMedicalExamination;

INSERT INTO AKUZ.T_MEDICAL_EPISODE (MedicalEpisodeID, TNMS, SetDTClinicalDiagnosis, OutcomeRecord, AmbulanceEvent, Mkb, Diagnosis, Descr, DateIn, DateOut, CdaEpicris, VimisEpicrisis, type_)
SELECT mdc.MovementsDivisionCardID, mdc.TNMS, mdc.SetDTClinicalDiagnosis, mdc.MovementResult, m.EmergencyEvent, COALESCE(d.Mkb, 'Z00.0'), COALESCE(d.Diagnosis, ('Общий медицинский осмотр')), d.Descr, mdc.DateIn, mdc.DateOut, mdc.CdaHospitalEpicris, mdc.VimisHospitalEpicrisis,
(SELECT e.EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'MOVEMENTS_DIVISION_CARD' and e.Schema = 'AKUZ' limit 1)
FROM AKUZ.T_MOVEMENTS_DIVISION_CARD mdc
LEFT JOIN AKUZ.T_DIAGNOSIS d on d.MedicalRecordID = mdc.ClinicalDiagnosis
LEFT JOIN AKUZ.T_MOVEMENT m on m.MovementID = mdc.Movement;


CREATE TEMP table DIAG  
(
	MedicalRecordID uuid NULL
) ;


INSERT INTO DIAG (MedicalRecordID) 
(SELECT d.MedicalRecordID 
FROM AKUZ.T_DIAGNOSIS d
JOIN AKUZ.T_MOVEMENTS_DIVISION_CARD mdc on d.MedicalRecordID = mdc.ClinicalDiagnosis);

ALTER TABLE AKUZ.T_MOVEMENTS_DIVISION_CARD DROP CONSTRAINT FK_T_DIAGNOSIS_T_MOVEMENTS_DIVISION_CARD_ClinicalDiagnosis;

ALTER TABLE AKUZ.T_MOVEMENTS_DIVISION_CARD DROP COLUMN ClinicalDiagnosis;

DELETE FROM AKUZ.T_DIAGNOSIS e USING DIAG d WHERE e.MedicalRecordID = d.MedicalRecordID;

DELETE FROM AKUZ.T_AMBULANCE_CARD_RECORD e USING DIAG d WHERE e.MedicalRecordID = d.MedicalRecordID;

DELETE FROM AKUZ.T_MEDICAL_RECORD e USING DIAG d WHERE e.MedicalRecordID = d.MedicalRecordID;

drop table DIAG;


UPDATE AKUZ.T_DIVISION_SIGNIFICANT_EVENTS
SET MedicalEpisode = MovementsDivisionCard
WHERE MovementsDivisionCard is not null;

UPDATE AKUZ.T_POST_TREAT
SET MedicalEpisode = MovementsDivisionCard
WHERE MovementsDivisionCard is not null;

INSERT INTO AKUZ.T_ASSOCIATED_AND_COMPLICATION_DISEASE (AssociatedAndComplicationDiseaseID, Type, Mkb, Diagnos, Descr, DetectionDate, MedicalEpisode)
SELECT NEWID(), cld.DiagnosisType + 1, COALESCE(d.Mkb, 'Z00.0'), COALESCE(d.Diagnosis, ('Общий медицинский осмотр')), d.Descr, d.RegistrationDate, cld.MovementsDivisionCard
FROM AKUZ.T_CARD_LINK_DIAGNOSIS cld
LEFT JOIN AKUZ.T_DIAGNOSIS d on d.MedicalRecordID = cld.Diagnosis;

ALTER TABLE AKUZ.T_MOVEMENTS_DIVISION_CARD RENAME COLUMN MovementsDivisionCardID TO MedicalEpisodeID;

ALTER TABLE AKUZ.T_MOVEMENTS_DIVISION_CARD ADD CONSTRAINT FK_T_MEDICAL_EPISODE_T_MOVEMENTS_DIVISION_CARD_MedicalEpisodeID
FOREIGN KEY(MedicalEpisodeID) REFERENCES AKUZ.T_MEDICAL_EPISODE (MedicalEpisodeID);

DROP TABLE AKUZ.T_CARD_LINK_DIAGNOSIS;
select EntityID 
INTO ENTITY
from VCLib.T_ENTITIES e
where e.EntName in (
'CARD_LINK_DIAGNOSIS'
)
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;

DO
$do$
BEGIN
/****** Object:  Index IX_T_POST_TREAT_MovementsDivisionCard_Post    Script Date: 27.05.2024 15:36:19 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'AKUZ.T_POST_TREAT') AND name = N'IX_T_POST_TREAT_MovementsDivisionCard_Post') THEN

DROP INDEX IX_T_POST_TREAT_MovementsDivisionCard_Post;

/****** Object:  Index IX_T_POST_TREAT_MovementsDivisionCard_Post    Script Date: 27.05.2024 15:36:19 ******/
CREATE INDEX IX_T_POST_TREAT_MedicalEpisode_Post ON AKUZ.T_POST_TREAT
(
	MedicalEpisode ASC,
	Post ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

END IF;
END
$do$
