-- ================================================
-- 房间系统 - 数据库迁移脚本 V5
-- 用途: 家庭下的房间管理(客厅/主卧等)，设备归属于房间
-- ================================================

SET NAMES utf8mb4;

-- ============================================================
-- 1. room 表（房间）
-- ============================================================
CREATE TABLE IF NOT EXISTS `room` (
  `id`               BIGINT        NOT NULL COMMENT '主键ID(雪花算法)',
  `home_id`          BIGINT        NOT NULL DEFAULT 0 COMMENT '家庭ID(home.id)',
  `name`             VARCHAR(50)   NOT NULL DEFAULT '' COMMENT '房间名称',
  `icon`             VARCHAR(50)   NOT NULL DEFAULT '' COMMENT '房间图标标识',
  `sort_order`       INT           NOT NULL DEFAULT 0 COMMENT '排序顺序(越小越靠前)',
  `device_count`     INT           NOT NULL DEFAULT 0 COMMENT '设备数量',
  `create_time`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted`          TINYINT       NOT NULL DEFAULT 0 COMMENT '逻辑删除(0正常 1删除)',
  PRIMARY KEY (`id`),
  KEY `idx_room_home_id` (`home_id`),
  KEY `idx_room_deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='房间表';

SELECT 'V5 migration completed: room table created.' AS message;