-- Migration 004 : tables chambres
-- Exécuter : mysql -u <user> -p <database> < migrations/004_create_rooms_tables.sql

CREATE TABLE IF NOT EXISTS `rooms` (
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `slug`             VARCHAR(100)  NOT NULL,
    `name`             VARCHAR(255)  NOT NULL,
    `reservit_cat_id`  INT UNSIGNED  NULL,
    `reservit_room_id` INT UNSIGNED  NULL,
    `is_active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `sort_order`       INT           NOT NULL DEFAULT 0,
    `created_at`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_rooms_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `room_translations` (
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `room_id`          INT UNSIGNED  NOT NULL,
    `locale`           VARCHAR(5)    NOT NULL,
    `summary`          TEXT          NOT NULL,
    `presentation`     TEXT          NOT NULL,
    `welcome`          VARCHAR(255)  NULL,
    `meta_title`       VARCHAR(255)  NULL,
    `meta_description` TEXT          NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_room_locale` (`room_id`, `locale`),
    FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `room_images` (
    `id`         INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `room_id`    INT UNSIGNED  NOT NULL,
    `filename`   VARCHAR(255)  NOT NULL,
    `credits`    VARCHAR(255)  NULL,
    `sort_order` INT           NOT NULL DEFAULT 0,
    `is_primary` TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `room_image_translations` (
    `image_id` INT UNSIGNED  NOT NULL,
    `locale`   VARCHAR(5)    NOT NULL,
    `alt`      VARCHAR(255)  NULL,
    `caption`  TEXT          NULL,
    PRIMARY KEY (`image_id`, `locale`),
    FOREIGN KEY (`image_id`) REFERENCES `room_images`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
