-------------
-- Обновление RecordDate и RecordTime 
-- 2024-01-10
-------------

-- Creating temporary tables
CREATE TEMPORARY TABLE TMP_Exam (
    MedicalRecordID UUID,
    mrDate DATE,
    mrTime TIMESTAMP
);

CREATE TEMPORARY TABLE TMP_Exam_DEL (
    MedicalRecordID UUID,
    mrDate DATE,
    mrTime TIMESTAMP
);

-- Inserting data into TMP_Exam
INSERT INTO TMP_Exam
SELECT
    mr.MedicalRecordID,
    COALESCE(mrO.RecordDate, acc.PostingDate),
    COALESCE(mrO.RecordTime, acc.PostingDate)
FROM
    AKUZ.T_ACCOMPANYING_DISEASES ad
    JOIN AKUZ.T_MEDICAL_RECORD mr ON mr.MedicalRecordID = ad.MedicalRecordID
    JOIN AKUZ.T_MEDICAL_RECORD mrO ON mrO.MedicalRecordID = ad.OutcomeOfInspection
    JOIN AKUZ.T_AMBULANCE_CARD_RECORD acc ON acc.MedicalRecordID = ad.OutcomeOfInspection
WHERE
    mr.RecordDate IS NULL;

-- Creating indexes
CREATE INDEX IX_TMP_EXAM ON TMP_Exam (MedicalRecordID);
CREATE INDEX IX_TMP_EXAM_DEL ON TMP_Exam_DEL (MedicalRecordID);

DO $$
DECLARE
    C VARCHAR(100) := '';
BEGIN
    WHILE (SELECT COUNT(*) FROM TMP_Exam) > 0 LOOP
        -- Deleting and storing records in TMP_Exam_DEL
        WITH deleted_rows AS (
        DELETE FROM TMP_Exam
        WHERE MedicalRecordID IN (
            SELECT MedicalRecordID
            FROM TMP_Exam
            LIMIT 20000
            )
        RETURNING MedicalRecordID, mrDate, mrTime
        )
        INSERT INTO TMP_Exam_DEL (MedicalRecordID, mrDate, mrTime)
        SELECT MedicalRecordID, mrDate, mrTime
        FROM deleted_rows;

        -- Updating corresponding records in AKUZ.T_MEDICAL_RECORD
        UPDATE AKUZ.T_MEDICAL_RECORD mr
        SET
            RecordDate = ED.mrDate,
            RecordTime = ED.mrTime
        FROM
            TMP_Exam_DEL ED
        WHERE
            ED.MedicalRecordID = mr.MedicalRecordID;

        -- Truncating TMP_Exam_DEL
        TRUNCATE TABLE TMP_Exam_DEL;
    END LOOP;
END $$;
