-- ================================================
-- 会员系统 + 设备/场景归属字段 - 数据库迁移脚本 V3
-- 用途: 将移动端用户与后台管理员分离，实现设备/场景/告警的用户隔离
-- ================================================

SET NAMES utf8mb4;

-- ============================================================
-- 1. 新增 member 表（移动端用户）
-- ============================================================
CREATE TABLE IF NOT EXISTS `member` (
  `id`               BIGINT        NOT NULL COMMENT '主键ID(雪花算法)',
  `username`         VARCHAR(50)   NOT NULL DEFAULT '' COMMENT '用户名',
  `password`         VARCHAR(100)  NOT NULL DEFAULT '' COMMENT '密码(BCrypt加密)',
  `nickname`         VARCHAR(50)   NOT NULL DEFAULT '' COMMENT '昵称',
  `avatar`           VARCHAR(255)  NOT NULL DEFAULT '' COMMENT '头像URL',
  `phone`            VARCHAR(20)   NOT NULL DEFAULT '' COMMENT '手机号',
  `email`            VARCHAR(100)  NOT NULL DEFAULT '' COMMENT '邮箱',
  `gender`           TINYINT       NOT NULL DEFAULT 0 COMMENT '性别(0未知 1男 2女)',
  `birthday`         DATE          NULL     COMMENT '生日',
  `status`           TINYINT       NOT NULL DEFAULT 1 COMMENT '状态(0禁用 1正常)',
  `last_login_time`  DATETIME      NULL     COMMENT '最后登录时间',
  `last_login_ip`    VARCHAR(50)   NULL     COMMENT '最后登录IP',
  `tenant_id`        BIGINT        NOT NULL DEFAULT 0 COMMENT '租户ID',
  `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_member_username` (`username`),
  UNIQUE KEY `uk_member_phone` (`phone`),
  KEY `idx_member_tenant_id` (`tenant_id`),
  KEY `idx_member_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='移动端会员表';

-- ============================================================
-- 2. device 表添加 owner_id 字段（设备归属）
-- ============================================================
SET @exist_owner_device = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'device' AND COLUMN_NAME = 'owner_id'
);
-- 注意: MySQL PREPARE 不支持多语句，故拆分为两个独立的 PREPARE
SET @sql_dev_col = IF(@exist_owner_device = 0,
  'ALTER TABLE `device` ADD COLUMN `owner_id` BIGINT NOT NULL DEFAULT 0 COMMENT ''设备归属用户ID(member.id)'' AFTER `group_id`',
  'SELECT 1;'
);
PREPARE stmt FROM @sql_dev_col; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @exist_idx_dev = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='device' AND INDEX_NAME='idx_device_owner_id');
SET @sql_dev_idx = IF(@exist_idx_dev = 0, 'ALTER TABLE `device` ADD INDEX `idx_device_owner_id` (`owner_id`)', 'SELECT 1');
PREPARE stmt FROM @sql_dev_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 3. linkage_rule 表添加 owner_id 字段（场景归属）
-- ============================================================
SET @exist_owner_rule = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'linkage_rule' AND COLUMN_NAME = 'owner_id'
);
SET @sql_rule_col = IF(@exist_owner_rule = 0,
  'ALTER TABLE `linkage_rule` ADD COLUMN `owner_id` BIGINT NOT NULL DEFAULT 0 COMMENT ''场景归属用户ID(member.id)'' AFTER `create_by`',
  'SELECT 1;'
);
PREPARE stmt FROM @sql_rule_col; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @exist_idx_rule = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='linkage_rule' AND INDEX_NAME='idx_rule_owner_id');
SET @sql_rule_idx = IF(@exist_idx_rule = 0, 'ALTER TABLE `linkage_rule` ADD INDEX `idx_rule_owner_id` (`owner_id`)', 'SELECT 1');
PREPARE stmt FROM @sql_rule_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 4. alarm_rule 表添加 owner_id 字段
-- ============================================================
SET @exist_owner_alarm_rule = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'alarm_rule' AND COLUMN_NAME = 'owner_id'
);
SET @sql_ar_col = IF(@exist_owner_alarm_rule = 0,
  'ALTER TABLE `alarm_rule` ADD COLUMN `owner_id` BIGINT NOT NULL DEFAULT 0 COMMENT ''告警规则归属用户ID(member.id)'' AFTER `deduplication_window`',
  'SELECT 1;'
);
PREPARE stmt FROM @sql_ar_col; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @exist_idx_ar = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='alarm_rule' AND INDEX_NAME='idx_alarm_rule_owner_id');
SET @sql_ar_idx = IF(@exist_idx_ar = 0, 'ALTER TABLE `alarm_rule` ADD INDEX `idx_alarm_rule_owner_id` (`owner_id`)', 'SELECT 1');
PREPARE stmt FROM @sql_ar_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 5. alarm 表添加 owner_id 字段
-- ============================================================
SET @exist_owner_alarm = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'alarm' AND COLUMN_NAME = 'owner_id'
);
SET @sql_a = IF(@exist_owner_alarm = 0,
  'ALTER TABLE `alarm` ADD COLUMN `owner_id` BIGINT NOT NULL DEFAULT 0 COMMENT ''告警记录归属用户ID(member.id)'' AFTER `resolved_by`',
  'SELECT 1;'
);
PREPARE stmt FROM @sql_a; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @exist_idx_a = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='alarm' AND INDEX_NAME='idx_alarm_owner_id');
SET @sql_a_idx = IF(@exist_idx_a = 0, 'ALTER TABLE `alarm` ADD INDEX `idx_alarm_owner_id` (`owner_id`)', 'SELECT 1');
PREPARE stmt FROM @sql_a_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 6. 迁移现有 sys_user 中 role=user 的用户到 member 表
-- （仅迁移数据，不删除原记录，确保向后兼容）
-- ============================================================
INSERT IGNORE INTO `member` (`id`, `username`, `password`, `nickname`, `phone`, `status`,
  `tenant_id`, `create_time`, `update_time`, `deleted`)
SELECT `id`, `username`, `password`, COALESCE(`real_name`, username), COALESCE(`phone`, ''),
  CASE WHEN `status`=0 THEN 0 ELSE 1 END, COALESCE(`tenant_id`, 0),
  COALESCE(`create_time`, NOW()), COALESCE(`update_time`, NOW()), COALESCE(`deleted`, 0)
FROM `sys_user` WHERE `role` = 'user' AND `deleted` = 0;

-- 同步现有 device 的 create_by 到 owner_id
UPDATE `device` SET `owner_id` = `create_by` WHERE `owner_id` = 0 AND `create_by` > 0;

-- 同步现有 linkage_rule 的 create_by 到 owner_id  
UPDATE `linkage_rule` SET `owner_id` = `create_by` WHERE `owner_id` = 0 AND `create_by` > 0;

-- 同步现有 alarm_rule 的 create_by 到 owner_id
UPDATE `alarm_rule` SET `owner_id` = `create_by` WHERE `owner_id` = 0 AND `create_by` > 0;

-- 同步现有 alarm 的 confirmed_by 到 owner_id (取确认人)
UPDATE `alarm` SET `owner_id` = COALESCE(`confirmed_by`, `create_by`) 
WHERE `owner_id` = 0 AND (COALESCE(`confirmed_by`, 0) > 0 OR COALESCE(`create_by`, 0) > 0);

SELECT 'V3 migration completed: member table created, owner_id fields added.' AS message;