-- VILLAFLOW CMS — Migration 009
-- Phase 4F — Rooms <-> Facilities
-- Owns: the room_facilities pivot connecting existing `rooms` (004)
-- to the existing `facilities` master list (008). Pure membership
-- table — no value/status/ordering fields, since a facility is
-- either assigned to a room or it isn't.
-- Does NOT own: facilities themselves (see 008), room profile data
-- (see 004), or any property-level facility assignment (out of
-- scope for Phase 4F — only scope='room' facilities are assignable
-- here, enforced at the application layer).

CREATE TABLE IF NOT EXISTS room_facilities (
    id            INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    room_id       INT UNSIGNED    NOT NULL,
    facility_id   INT UNSIGNED    NOT NULL,
    created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

    UNIQUE KEY uq_room_facility (room_id, facility_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;