-- ================================================
-- 家庭系统 - 数据库迁移脚本 V4
-- 用途: 支持用户创建多个家庭(我的家/办公室等)，并可分享给其他用户
-- ================================================

SET NAMES utf8mb4;

-- ============================================================
-- 1. home 表（家庭）
-- ============================================================
CREATE TABLE IF NOT EXISTS `home` (
  `id`               BIGINT        NOT NULL COMMENT '主键ID(雪花算法)',
  `name`             VARCHAR(50)   NOT NULL DEFAULT '' COMMENT '家庭名称',
  `cover_image`      VARCHAR(255)  NOT NULL DEFAULT '' COMMENT '封面图URL',
  `address`          VARCHAR(100)  NOT NULL DEFAULT '' COMMENT '地址',
  `owner_id`         BIGINT        NOT NULL DEFAULT 0 COMMENT '创建者ID(member.id)',
  `share_code`       VARCHAR(12)   NOT NULL DEFAULT '' COMMENT '分享邀请码',
  `member_count`     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`),
  UNIQUE KEY `uk_home_share_code` (`share_code`),
  KEY `idx_home_owner_id` (`owner_id`),
  KEY `idx_home_deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='家庭表';

-- ============================================================
-- 2. home_member 表（家庭成员关系）
-- ============================================================
CREATE TABLE IF NOT EXISTS `home_member` (
  `id`               BIGINT        NOT NULL COMMENT '主键ID(雪花算法)',
  `home_id`          BIGINT        NOT NULL DEFAULT 0 COMMENT '家庭ID(home.id)',
  `member_id`        BIGINT        NOT NULL DEFAULT 0 COMMENT '成员ID(member.id)',
  `role`             VARCHAR(20)   NOT NULL DEFAULT 'member' COMMENT '角色(owner/admin/member)',
  `alias_name`       VARCHAR(50)   NOT NULL DEFAULT '' COMMENT '在该家庭的昵称',
  `invited_by`       BIGINT        NOT NULL DEFAULT 0 COMMENT '邀请人ID(member.id)',
  `status`           VARCHAR(20)   NOT NULL DEFAULT 'accepted' COMMENT '状态(pending/accepted/removed)',
  `join_time`        DATETIME      NULL     COMMENT '加入时间',
  `create_time`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_home_member` (`home_id`, `member_id`),
  KEY `idx_hm_member_id` (`member_id`),
  KEY `idx_hm_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='家庭成员关系表';

-- 为新注册的测试用户自动创建一个默认"我的家"
INSERT IGNORE INTO `home` (`id`, `name`, `owner_id`, `share_code`, `member_count`, `device_count`, `create_time`, `update_time`, `deleted`)
VALUES (1, '我的家', 1, 'DEFAULTH', 1, 0, NOW(), NOW(), 0);

INSERT IGNORE INTO `home_member` (`id`, `home_id`, `member_id`, `role`, `alias_name`, `invited_by`, `status`, `join_time`, `create_time`)
VALUES (1, 1, 1, 'owner', 'testuser', 0, 'accepted', NOW(), NOW());

SELECT 'V4 migration completed: home + home_member tables created.' AS message;