-- =====================================================================
-- Migration 002 — multi-image galleries for hotels and places
-- =====================================================================

CREATE TABLE IF NOT EXISTS hotel_images (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id    INT UNSIGNED NOT NULL,
    image       VARCHAR(255) NOT NULL,
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_hi_hotel (hotel_id),
    CONSTRAINT fk_hi_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS place_images (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    place_id    INT UNSIGNED NOT NULL,
    image       VARCHAR(255) NOT NULL,
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_pi_place (place_id),
    CONSTRAINT fk_pi_place FOREIGN KEY (place_id) REFERENCES places(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;