-- Migration 009 : pivot room_activities
-- Dépend de 003 (activities) et 004 (rooms).
-- Exécuter : mysql -u valtrebbia -pC0l1briV3rt valtrebbia < dist/migrations/009_create_room_activities_pivot.sql

CREATE TABLE IF NOT EXISTS `room_activities` (
    `room_id`     INT UNSIGNED NOT NULL,
    `activity_id` INT UNSIGNED NOT NULL,
    `sort_order`  INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`room_id`, `activity_id`),
    FOREIGN KEY (`room_id`)     REFERENCES `rooms`(`id`)      ON DELETE CASCADE,
    FOREIGN KEY (`activity_id`) REFERENCES `activities`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed : les 4 activités (dans leur ordre actuel) sont associées aux 5 chambres par défaut
INSERT IGNORE INTO `room_activities` (`room_id`, `activity_id`, `sort_order`)
SELECT r.id, a.id, a.sort_order
FROM `rooms` r
CROSS JOIN `activities` a
WHERE r.is_active = 1 AND a.is_active = 1;
