-- Migration 005: let an uploaded photo belong to a specific room, not just the
-- property. A photo with room_id NULL is a property/building photo.
-- MySQL syntax (not MariaDB).

ALTER TABLE property_images
    ADD COLUMN room_id INT UNSIGNED NULL AFTER property_id,
    ADD KEY idx_property_images_room (room_id),
    ADD CONSTRAINT fk_property_images_room FOREIGN KEY (room_id)
        REFERENCES rooms (id) ON DELETE CASCADE;