-- Общая функция возвращения всех госпитализаций и периодов пациента
CREATE OR REPLACE FUNCTION get_hospitalization_periods (p_patient_id int)
    RETURNS TABLE (
        admission_datetime timestamp,
        discharge_datetime timestamp
    )
    AS $$
BEGIN
    RETURN QUERY
    SELECT
        ha.admission_datetime,
        hd.discharge_datetime
    FROM
        hospital_admission ha
    LEFT JOIN hospital_discharge hd ON ha.hospital_admission_id = hd.hospital_admission_id
WHERE
    ha.patient_id = p_patient_id;
END;
$$
LANGUAGE plpgsql;

-- Триггер: запрещает новую госпитализацию при наличии незавершённой
CREATE OR REPLACE FUNCTION check_active_hospitalization ()
    RETURNS TRIGGER
    AS $$
DECLARE
    open_hospitalization boolean;
BEGIN
    SELECT
        EXISTS (
            SELECT
                1
            FROM
                get_hospitalization_periods (NEW.patient_id)
            WHERE
                discharge_datetime IS NULL) INTO open_hospitalization;
    IF open_hospitalization THEN
        RAISE EXCEPTION
            USING ERRCODE = 'P0001', MESSAGE = '10001: ACTIVE_HOSPITALIZATION_EXISTS';
        END IF;
        RETURN NEW;
END;
$$
LANGUAGE plpgsql;

-- Триггер: запрещает перекрытие госпитализаций
CREATE OR REPLACE FUNCTION check_hospitalization_overlap ()
    RETURNS TRIGGER
    AS $$
DECLARE
    overlap_found boolean;
BEGIN
    SELECT
        EXISTS (
            SELECT
                1
            FROM
                get_hospitalization_periods (NEW.patient_id)
            WHERE
                discharge_datetime IS NOT NULL
                AND NEW.admission_datetime < discharge_datetime
                AND NEW.admission_datetime > admission_datetime) INTO overlap_found;
    IF overlap_found THEN
        RAISE EXCEPTION
            USING ERRCODE = 'P0001', MESSAGE = '10002: HOSPITALIZATION_OVERLAP';
        END IF;
        RETURN NEW;
END;
$$
LANGUAGE plpgsql;

-- Подключение триггеров на hospital_admissions
CREATE TRIGGER trg_check_active_hospitalization
    BEFORE INSERT ON hospital_admission
    FOR EACH ROW
    EXECUTE FUNCTION check_active_hospitalization ();

CREATE TRIGGER trg_check_hospitalization_overlap
    BEFORE INSERT ON hospital_admission
    FOR EACH ROW
    EXECUTE FUNCTION check_hospitalization_overlap ();