CREATE OR REPLACE FUNCTION f_route_fix_arrival_departure() RETURNS TRIGGER AS $f_route_fix_arrival_departure$
DECLARE
    old_arrival_id INTEGER;
    old_departure_id INTEGER;
    new_arrival_id INTEGER;
    new_departure_id INTEGER;
BEGIN
    IF TG_OP = 'DELETE' THEN
        IF EXISTS (
            SELECT
                r.arrival_station_id
            FROM
                route.route r
            WHERE r.id = OLD.route_id
                AND r.arrival_station_id = OLD.station_id
        ) THEN
            UPDATE
                route.route r
            SET
                r.arrival_station_id = (
                    SELECT s.station_id
                    FROM route.stage s
                    WHERE s.route_id  = r.id
                    ORDER BY s.arrival ASC
                    LIMIT 1
                )
            WHERE
                r.id = OLD.ROUTE_ID;
        END IF;


        IF EXISTS (
            SELECT
                r.departure_station_id
            FROM
                route.route r
            WHERE r.id = OLD.route_id
                AND r.departure_station_id = OLD.station_id
        ) THEN
            UPDATE
                route.route r
            SET
                r.departure_station_id = (
                    SELECT s.station_id
                    FROM route.stage s
                    WHERE s.route_id  = r.id
                    ORDER BY s.departure DESC
                    LIMIT 1
                )
            WHERE
                r.id = OLD.ROUTE_ID;
        END IF;
    ELSE
        SELECT
            r.arrival_station_id, r.departure_station_id
        INTO
            old_arrival_id, old_departure_id
        FROM
            route.route r
        WHERE
            r.id = NEW.route_id;

        IF NEW.arrival < (
            SELECT s.arrival
            FROM route.stage s
            WHERE s.route_id = NEW.route_id
                AND s.station_id = NEW.station_id
        ) THEN
            new_arrival_id := NEW.station_id;
        ELSE
            new_arrival_id := old_arrival_id;
        END IF;


        IF NEW.departure > (
            SELECT s.departure
            FROM route.stage s
            WHERE s.route_id = NEW.route_id
                AND s.station_id = NEW.station_id
        ) THEN
            new_departure_id := NEW.station_id;
        ELSE
            new_departure_id := old_departure_id;
        END IF;
    END IF;

    RETURN NULL;
END;
$f_route_fix_arrival_departure$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_route_fix_arrival_departure ON route.route;
CREATE TRIGGER tr_route_fix_arrival_departure AFTER INSERT OR UPDATE OR DELETE ON route.route
    FOR EACH ROW EXECUTE FUNCTION f_route_fix_arrival_departure();