-- VILLAFLOW CMS — Migration 005
-- Phase 3 — Rooms (Room Images sub-scope)
-- Owns: multiple images per room. Physical file storage/deletion is
-- an application-layer concern (ImageService, consumed by the admin
-- UI in Phase 3C) — this table only records rows.

CREATE TABLE IF NOT EXISTS room_images (
    id            INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    room_id       INT UNSIGNED    NOT NULL,
    image_path    VARCHAR(255)    NOT NULL,
    sort_order    INT             NOT NULL DEFAULT 0,
    created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,

    KEY idx_room_images_room_sort (room_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;