ylrz_saas_his_scrm/sql/add_voice_pricing.sql

-- ============================================================
-- 外呼接口改造 DDL
-- 1. company_voice_api 新增 provider + cost_price
-- 2. company_voice_api_tenant 新增 price + priority + is_primary + allow_manual
-- ============================================================

-- ========== 1. voice_api 新增字段 ==========
-- provider 用存储过程检测列是否存在
DROP PROCEDURE IF EXISTS add_voice_api_columns;
DELIMITER //
CREATE PROCEDURE add_voice_api_columns()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_voice_api' AND COLUMN_NAME = 'provider') THEN
    ALTER TABLE company_voice_api ADD COLUMN provider VARCHAR(20) DEFAULT 'platform' COMMENT '服务商:platform平台/card手机卡' AFTER api_type;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_voice_api' AND COLUMN_NAME = 'cost_price') THEN
    ALTER TABLE company_voice_api ADD COLUMN cost_price DECIMAL(10,4) DEFAULT NULL COMMENT '平台成本价(元/分钟)' AFTER status;
  END IF;
END //
DELIMITER ;
CALL add_voice_api_columns();
DROP PROCEDURE add_voice_api_columns;

-- ========== 2. voice_api_tenant 新增字段 ==========
DROP PROCEDURE IF EXISTS add_voice_api_tenant_columns;
DELIMITER //
CREATE PROCEDURE add_voice_api_tenant_columns()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_voice_api_tenant' AND COLUMN_NAME = 'price') THEN
    ALTER TABLE company_voice_api_tenant ADD COLUMN price DECIMAL(10,4) DEFAULT NULL COMMENT '租户售价(元/分钟)' AFTER company_id;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_voice_api_tenant' AND COLUMN_NAME = 'priority') THEN
    ALTER TABLE company_voice_api_tenant ADD COLUMN priority INT DEFAULT 1 COMMENT '优先级(1最高)' AFTER price;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_voice_api_tenant' AND COLUMN_NAME = 'is_primary') THEN
    ALTER TABLE company_voice_api_tenant ADD COLUMN is_primary TINYINT DEFAULT 0 COMMENT '是否主线路(0否1是)' AFTER priority;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_voice_api_tenant' AND COLUMN_NAME = 'allow_manual') THEN
    ALTER TABLE company_voice_api_tenant ADD COLUMN allow_manual TINYINT DEFAULT 0 COMMENT '允许销售手动选择(0否1是)' AFTER is_primary;
  END IF;
END //
DELIMITER ;
CALL add_voice_api_tenant_columns();
DROP PROCEDURE add_voice_api_tenant_columns;