-- Добавление записей в AmbulanceCardRecord
INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    COALESCE(acr.AmbulanceCard, acr1.AmbulanceCard),
    i.MedicalRecordID,
    COALESCE(acr.AmbulanceEvent, acr1.AmbulanceEvent),
    mr.RecordDate,
    COALESCE(acr.Movement, acr1.Movement),
    COALESCE(acr.PatientBase, acr1.PatientBase)
FROM AKUZ.T_MENTAL_DISPANSERISATION_PRESENCE i
JOIN AKUZ.T_MEDICAL_RECORD mr ON mr.MedicalRecordId = i.MedicalRecordId
LEFT JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.MedicalExamination
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr1 ON acr1.MedicalRecordID = i.MentalDispanserisationInit;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    acr.PostingDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_DISPANSERISATION_EVENTS i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.DispanserisationInit;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.ResultDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_MANTU_RESULT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.MantuAccept;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.DateEditing,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_DISPANSERISATION_MOVENMENT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.DispanserisationInit;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.DateClose,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_MEDICAL_CERTIFICATE_CLOSE i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.MedicalCertificate;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.ResultDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_REACTIONS_ON_VACCINATION i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.InoculationAccept;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.ResultDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_BCG_RESULT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.BcgAccept;

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.DateEditing,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_MENTAL_DISPANSERISATION_MOVENMENT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.DispanserisationInit;

-- Удаление внешних ключей с MedicalRecord
ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_PRESENCE
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_MENTAL_DISPANSERISATION_PRESENCE_FBAF3905;

ALTER TABLE AKUZ.T_DISPANSERISATION_EVENTS
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_DISPANSERISATION_EVENTS_MedicalRecordID;

ALTER TABLE AKUZ.T_MANTU_RESULT
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_MANTU_RESULT_MEDICALRECORDID;

ALTER TABLE AKUZ.T_DISPANSERISATION_MOVENMENT
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_DISPANSERISATION_MOVENMENT_Medic_51A3FDD1;

ALTER TABLE AKUZ.T_MEDICAL_CERTIFICATE_CLOSE
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_MEDICAL_CERTIFICATE_CLOSE_MEDICALRECORDID;

ALTER TABLE AKUZ.T_REACTIONS_ON_VACCINATION
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_REACTIONS_ON_VACCINATION_MEDICALRECORDID;

ALTER TABLE AKUZ.T_BCG_RESULT
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_BCG_RESULT_MEDICALRECORDID;

ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_MOVENMENT
    DROP CONSTRAINT IF EXISTS FK_T_MEDICAL_RECORD_T_MENTAL_DISPANSERISATION_MOVENMEN_9A8514FA;
							  

-- Добавление внешних ключей на ambulance_card_record
ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_PRESENCE
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MENTAL_DISPANSERISATION_P_63F0F048 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_DISPANSERISATION_EVENTS
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_DISPANSERISATION_EVENTS_M_A0B9354E 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_MANTU_RESULT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MANTU_RESULT_MEDICALRECORDID 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_DISPANSERISATION_MOVENMENT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_DISPANSERISATION_MOVENMEN_E567AC6C 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_MEDICAL_CERTIFICATE_CLOSE
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MEDICAL_CERTIFICATE_CLOSE_FB98C362 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_REACTIONS_ON_VACCINATION
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_REACTIONS_ON_VACCINATION__CA632D8F 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_BCG_RESULT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_BCG_RESULT_MEDICALRECORDID 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);

ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_MOVENMENT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MENTAL_DISPANSERISATION_M_9270761E 
    FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID);
