aonestar.public

Parameters

Name	Type	Mode
i_room_id	integer	IN
i_force	boolean	IN (DEFAULT false)
i_user_name	text	IN (DEFAULT NULL)

Definition

DECLARE
    v_state TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT;
    v_room_number public.t_room_number;
    v_room_status text;
    v_block record;
    v_booking_item integer;
    v_booking_items integer[];
    v_unblock_code text;
    v_unblock_message text;
    v_unblock_msg_code text;
    v_detached_registrations integer := 0;
BEGIN
    IF i_room_id IS NULL THEN
        RETURN fn_result_error('10614', sys_msg('10614', 'Please specify room ID.'));
    END IF;

CALL sp_check_endday_process();
    PERFORM sp_lock_room(i_room_id);

SELECT r.room_number, rs.status
    INTO v_room_number, v_room_status
    FROM room r
    LEFT JOIN room_status rs ON rs.room_id = r.id
    WHERE r.id = i_room_id
    FOR UPDATE OF r;

IF NOT FOUND THEN
        RETURN fn_result_error('10613', sys_msg('10613', 'Room %s not found.'), i_room_id::text);
    END IF;

-- Stabilize every reference used by the safety checks. New references to
    -- this room must also wait for the locked parent row.
    PERFORM 1 FROM registration WHERE room_id = i_room_id FOR UPDATE;
    PERFORM 1 FROM room_block WHERE room_id = i_room_id FOR UPDATE;
    PERFORM 1
    FROM booking b
    WHERE EXISTS (
        SELECT 1
        FROM room_block rb
        WHERE rb.room_id = i_room_id
          AND rb.booking_id = b.id
    )
    FOR UPDATE;

IF v_room_status = 'OCC'
       OR EXISTS (
            SELECT 1 FROM registration
            WHERE room_id = i_room_id AND status = 'I'
       ) THEN
        RETURN fn_result_error('10604', sys_msg('10604', 'Room %s is occupied.'), v_room_number::text);
    END IF;

IF EXISTS (
        SELECT 1
        FROM registration
        WHERE room_id = i_room_id
          AND status = 'O'
          AND departure = fn_system_date()
    ) THEN
        RETURN fn_result_error('10611',
            sys_msg('10611', 'Cannot delete room %s, the guest checked out today.'),
            v_room_number::text);
    END IF;

SELECT rb.arrival, rb.departure
    INTO v_block
    FROM room_block rb
    JOIN booking b ON b.id = rb.booking_id
    WHERE rb.room_id = i_room_id
      AND rb.status = 'B'
      AND NOT rb.checked_in
      AND b.status IN ('R', 'C', 'I')
    ORDER BY rb.arrival, rb.id
    LIMIT 1;

IF FOUND AND NOT coalesce(i_force, false) THEN
        RETURN fn_result_error('10612',
            sys_msg('10612', 'Room %s is blocked from %s to %s. Use force to unblock and delete it.'),
            v_room_number::text,
            to_char(v_block.arrival, 'DD/MM/YYYY'),
            to_char(v_block.departure, 'DD/MM/YYYY'));
    END IF;

SELECT array_agg(DISTINCT rb.booking_item ORDER BY rb.booking_item)
    INTO v_booking_items
    FROM room_block rb
    WHERE rb.room_id = i_room_id;

-- Mirror the legacy Name List B -> L behavior through the current domain
    -- function. It also preserves booking logs, realtime, and IPU events.
    FOR v_booking_item IN
        SELECT DISTINCT rb.booking_item
        FROM room_block rb
        WHERE rb.room_id = i_room_id
          AND NOT rb.checked_in
        ORDER BY rb.booking_item
    LOOP
        SELECT result_code, result_msg, msg_code
        INTO v_unblock_code, v_unblock_message, v_unblock_msg_code
        FROM sp_unblock_rooms(v_booking_item, ARRAY[i_room_id], i_user_name);

IF v_unblock_code = '4' THEN
            RETURN json_build_object(
                'result_code', v_unblock_code,
                'result_msg', v_unblock_message,
                'msg_code', v_unblock_msg_code,
                'result_data', NULL
            );
        END IF;
    END LOOP;

-- Delete FK children while the parent room is still visible. Their
    -- availability triggers resolve room.roomtype_id during DELETE; leaving
    -- them to ON DELETE CASCADE would resolve NULL after the parent is gone.
    DELETE FROM room_block
    WHERE room_id = i_room_id;

DELETE FROM out_of_order
    WHERE room_id = i_room_id;

UPDATE registration
    SET room_id = NULL
    WHERE room_id = i_room_id;
    GET DIAGNOSTICS v_detached_registrations = ROW_COUNT;

UPDATE room
    SET connect_rooms = array_remove(connect_rooms, i_room_id)
    WHERE id <> i_room_id
      AND i_room_id = ANY(coalesce(connect_rooms, '{}'::integer[]));

DELETE FROM ipu_roominfo
    WHERE roomno = v_room_number::text;

-- Keep the room number in the text because room_log intentionally remains
    -- after the room row is hard-deleted and can no longer be joined by ID.
    PERFORM sp_log_room(
        i_room_id,
        'Delete room',
        format(
            'Delete room %s (force=%s, detached registrations=%s, affected booking items=%s)',
            v_room_number,
            coalesce(i_force, false),
            v_detached_registrations,
            coalesce(array_to_string(v_booking_items, ','), 'none')
        ),
        i_user_name
    );

DELETE FROM room WHERE id = i_room_id;

FOREACH v_booking_item IN ARRAY coalesce(v_booking_items, '{}'::integer[])
    LOOP
        IF EXISTS (SELECT 1 FROM booking_items WHERE item_id = v_booking_item) THEN
            PERFORM sp_recalc_booking_rooms(v_booking_item);
        END IF;
    END LOOP;

RETURN fn_result_success(jsonb_build_object(
        'room_id', i_room_id,
        'room_number', v_room_number,
        'force', coalesce(i_force, false),
        'detached_registrations', v_detached_registrations,
        'affected_booking_items', coalesce(array_length(v_booking_items, 1), 0)
    ));
EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT,
        v_detail = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context,
        'public.sp_delete_roominfo',
        jsonb_build_object('room_id', i_room_id, 'force', i_force, 'user_name', i_user_name));
END