-- VILLAFLOW CMS — Migration 004
-- Phase 3 — Rooms
-- Owns: room profile records (name, pricing, status, sort order).
-- Does NOT own: room images (see 005_create_room_images.sql), room
-- specs/values (see 006/007), facility relationships (deferred to
-- Phase 4 — Facilities does not exist yet).

CREATE TABLE IF NOT EXISTS rooms (
    id            INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(150)    NOT NULL,
    slug          VARCHAR(170)    NOT NULL,
    description   TEXT            NULL,
    price         DECIMAL(12,2)   NULL,
    price_label   VARCHAR(50)     NULL,
    status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
    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,

    UNIQUE KEY uq_rooms_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;