-- VILLAFLOW CMS — Migration 007
-- Phase 3 — Rooms (Room Specs sub-scope)
-- Owns: the per-room value for each attached spec (e.g. room 5's
-- "Bed Type" = "King Bed"). Does NOT own room_facilities — that
-- pivot is deferred to Phase 4, once a Facilities master table exists.

CREATE TABLE IF NOT EXISTS room_spec_values (
    id             INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    room_id        INT UNSIGNED    NOT NULL,
    room_spec_id   INT UNSIGNED    NOT NULL,
    value          VARCHAR(150)    NOT NULL,
    sort_order     INT             NOT NULL DEFAULT 0,

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

    UNIQUE KEY uq_room_spec (room_id, room_spec_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;