ylrz_saas_his_scrm/sql/add_sms_device.sql

-- ============================================================
-- 设备管理改造 - 数据库变更脚本
-- 1. 新建 company_sms_device 表
-- 2. company_sms_card 新增 device_id + slot_index
-- 3. 数据迁移: 从 card 表提取设备数据
-- 4. company_sms_card_middleware 新增 tenant_id
-- ============================================================

-- ========== 1. 新建设备表 ==========
CREATE TABLE IF NOT EXISTS `company_sms_device` (
  `device_id`        BIGINT NOT NULL AUTO_INCREMENT COMMENT '设备ID',
  `tenant_id`        BIGINT NOT NULL COMMENT '所属租户',
  `company_user_id`  BIGINT DEFAULT NULL COMMENT '绑定销售用户ID(NULL=未分配)',
  `device_name`      VARCHAR(100) DEFAULT NULL COMMENT '设备名称',
  `imei`             VARCHAR(50) NOT NULL COMMENT 'IMEI(唯一标识)',
  `app_version`      VARCHAR(30) DEFAULT NULL COMMENT 'APP版本',
  `middleware_id`    BIGINT DEFAULT NULL COMMENT '关联中间件ID',
  `last_heartbeat`   DATETIME DEFAULT NULL COMMENT '最后心跳时间',
  `status`           TINYINT DEFAULT 0 COMMENT '0离线/1在线/2禁用',
  `remark`           VARCHAR(500) DEFAULT NULL COMMENT '备注',
  `create_time`      DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time`      DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`device_id`),
  UNIQUE KEY `uk_imei` (`imei`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_company_user_id` (`company_user_id`),
  KEY `idx_middleware_id` (`middleware_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='手机设备管理';

-- ========== 2. card 表新增 device 关联字段 ==========
-- 使用存储过程检测列是否存在再ALTER
DROP PROCEDURE IF EXISTS add_card_device_columns;
DELIMITER //
CREATE PROCEDURE add_card_device_columns()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_sms_card' AND COLUMN_NAME = 'device_id') THEN
    ALTER TABLE company_sms_card ADD COLUMN device_id BIGINT DEFAULT NULL COMMENT '所属设备ID' AFTER tenant_id;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_sms_card' AND COLUMN_NAME = 'slot_index') THEN
    ALTER TABLE company_sms_card ADD COLUMN slot_index TINYINT DEFAULT 1 COMMENT '卡槽(1或2)' AFTER device_id;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_sms_card' AND INDEX_NAME = 'idx_device_id') THEN
    ALTER TABLE company_sms_card ADD INDEX idx_device_id (device_id);
  END IF;
END //
DELIMITER ;
CALL add_card_device_columns();
DROP PROCEDURE add_card_device_columns;

-- ========== 3. 数据迁移：从 card 表提取设备 ==========
-- 按 imei 去重，每个唯一 imei 对应一台设备
INSERT INTO company_sms_device (tenant_id, device_name, imei, app_version, last_heartbeat, status, create_time)
SELECT c.tenant_id, MAX(c.device_name), c.imei, MAX(c.app_version), MAX(c.last_heartbeat), MAX(c.status), MIN(c.create_time)
FROM company_sms_card c
WHERE c.imei IS NOT NULL AND c.imei != ''
  AND NOT EXISTS (SELECT 1 FROM company_sms_device d WHERE d.imei = c.imei)
GROUP BY c.tenant_id, c.imei;

-- 回填 card.device_id
UPDATE company_sms_card c
INNER JOIN company_sms_device d ON c.imei = d.imei AND c.tenant_id = d.tenant_id
SET c.device_id = d.device_id, c.slot_index = 1
WHERE c.device_id IS NULL;

-- 同一设备下第二张卡(phone2不为空且phone1不等于phone2)的slot_index设为2
UPDATE company_sms_card c
INNER JOIN company_sms_device d ON c.device_id = d.device_id
SET c.slot_index = 2
WHERE c.device_id IS NOT NULL
  AND c.phone_2 IS NOT NULL AND c.phone_2 != ''
  AND c.phone_1 IS NOT NULL AND c.phone_1 != c.phone_2
  AND c.card_id > (
    SELECT MIN(c2.card_id) FROM (SELECT card_id, device_id FROM company_sms_card) c2
    WHERE c2.device_id = c.device_id
  );

-- ========== 4. 中间件表新增 tenant_id ==========
DROP PROCEDURE IF EXISTS add_middleware_tenant_id;
DELIMITER //
CREATE PROCEDURE add_middleware_tenant_id()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_sms_card_middleware' AND COLUMN_NAME = 'tenant_id') THEN
    ALTER TABLE company_sms_card_middleware ADD COLUMN tenant_id BIGINT DEFAULT NULL COMMENT '所属租户' AFTER api_id;
  END IF;
END //
DELIMITER ;
CALL add_middleware_tenant_id();
DROP PROCEDURE add_middleware_tenant_id;