ylrz_saas_his_scrm/sql/voice_api_tenant_migration.sql

-- ============================================================
-- 通话接口多租户分配改造 - 数据库变更脚本
-- 执行顺序: 先建表 → ALTER → 数据迁移
-- ============================================================

-- 1. 新建表: 通话接口-租户分配关系
CREATE TABLE IF NOT EXISTS `company_voice_api_tenant` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `api_id` bigint NOT NULL COMMENT '通话接口ID',
  `company_id` bigint NOT NULL COMMENT '租户ID',
  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 0禁用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_api_company` (`api_id`, `company_id`),
  KEY `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通话接口-租户分配关系';

-- 2. 新建缺失表: 通话套餐
CREATE TABLE IF NOT EXISTS `company_voice_package` (
  `package_id` bigint NOT NULL AUTO_INCREMENT COMMENT '套餐ID',
  `package_name` varchar(100) DEFAULT NULL COMMENT '套餐名',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `times` bigint DEFAULT NULL COMMENT '时长(分)',
  `status` int DEFAULT 1 COMMENT '状态 0禁用 1正常',
  `expire_price` decimal(10,2) DEFAULT NULL COMMENT '超出后每分钟价格',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通话套餐';

-- 3. 新建缺失表: 中间号
CREATE TABLE IF NOT EXISTS `company_voice_mobile` (
  `mobile_id` bigint NOT NULL AUTO_INCREMENT COMMENT '中间号ID',
  `api_id` bigint DEFAULT NULL COMMENT '接口ID',
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
  `status` int DEFAULT 1 COMMENT '状态',
  `company_id` bigint DEFAULT NULL COMMENT '租户ID',
  `mobile_type` int DEFAULT NULL COMMENT '号码类型 1公共 2私有',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`mobile_id`),
  KEY `idx_api_id` (`api_id`),
  KEY `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中间号';

-- 4. company_voice_caller 增加 api_id 字段（坐席关联通话接口）
-- 先检查字段是否已存在
SET @dbname = DATABASE();
SET @tablename = 'company_voice_caller';
SET @columnname = 'api_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  'ALTER TABLE company_voice_caller ADD COLUMN api_id bigint DEFAULT NULL COMMENT ''通话接口ID'' AFTER company_user_id'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 添加索引（如果不存在）
SET @indexname = 'idx_caller_api_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND INDEX_NAME = @indexname) > 0,
  'SELECT 1',
  'ALTER TABLE company_voice_caller ADD INDEX idx_caller_api_id (api_id)'
));
PREPARE addIndexIfNotExists FROM @preparedStatement;
EXECUTE addIndexIfNotExists;
DEALLOCATE PREPARE addIndexIfNotExists;

-- 5. 数据迁移: 将现有的 company.voice_api_id 绑定迁移到分配关系表
INSERT IGNORE INTO company_voice_api_tenant (api_id, company_id, status, create_time)
SELECT voice_api_id, company_id, 1, NOW()
FROM company
WHERE voice_api_id IS NOT NULL;

-- 6. 数据迁移: 为现有坐席设置默认接口
UPDATE company_voice_caller c
JOIN company co ON co.company_id = c.company_id
SET c.api_id = co.voice_api_id
WHERE c.api_id IS NULL AND co.voice_api_id IS NOT NULL;