ylrz_saas_his_scrm/docs/租户费用模块SQL.sql

-- 租户费用模块 SQL（MVP）
-- 适用数据库：MySQL 8.x
-- 说明：
-- 1) 先执行租户扩展字段，再执行计费基础表；
-- 2) 初始化示例数据可按需执行；
-- 3) 所有金额统一使用 DECIMAL，避免浮点误差。
-- 4) 已按库类型拆分为：
--    - docs/租户费用模块SQL-master.sql（总库执行）
--    - docs/租户费用模块SQL-tenant.sql（租户库执行）

/* =========================================================
   0. 租户表扩展（tenant_info）
   ========================================================= */
ALTER TABLE tenant_info
ADD COLUMN tenant_type VARCHAR(16) NOT NULL DEFAULT 'NON_AI' COMMENT '租户类型: NON_AI/AI',
ADD COLUMN billing_mode VARCHAR(16) NOT NULL DEFAULT 'PREPAID' COMMENT '计费模式: PREPAID/POSTPAID',
ADD COLUMN fee_plan_code VARCHAR(64) DEFAULT NULL COMMENT '绑定计费方案编码',
ADD COLUMN fee_plan_version INT DEFAULT NULL COMMENT '绑定计费方案版本';

/* =========================================================
   1. 计费方案
   ========================================================= */
CREATE TABLE IF NOT EXISTS fee_plan (
  id BIGINT NOT NULL AUTO_INCREMENT,
  plan_code VARCHAR(64) NOT NULL COMMENT '方案编码',
  plan_name VARCHAR(128) NOT NULL COMMENT '方案名称',
  version INT NOT NULL COMMENT '版本号',
  status VARCHAR(16) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT/PUBLISHED/ARCHIVED',
  effective_time DATETIME DEFAULT NULL COMMENT '生效时间',
  expire_time DATETIME DEFAULT NULL COMMENT '失效时间',
  remark VARCHAR(500) DEFAULT NULL,
  create_by VARCHAR(64) DEFAULT NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_by VARCHAR(64) DEFAULT NULL,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_plan_ver (plan_code, version),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='计费方案主表';

CREATE TABLE IF NOT EXISTS fee_plan_item (
  id BIGINT NOT NULL AUTO_INCREMENT,
  plan_code VARCHAR(64) NOT NULL,
  version INT NOT NULL,
  item_code VARCHAR(64) NOT NULL COMMENT 'FLOW_POSTPAID/CALL_OUT/CALL_IN/AI_CALL/SOP_TOKEN/AI_REPLY_TOKEN/ADD_WECHAT/OPEN_ACCOUNT_NON_AI/OPEN_ACCOUNT_AI',
  unit VARCHAR(32) NOT NULL COMMENT 'GB/MIN/TOKEN/COUNT/TIME',
  unit_price DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '单价',
  token_unit BIGINT DEFAULT NULL COMMENT 'token计费单位(如100000)',
  min_charge_unit INT DEFAULT NULL COMMENT '最小计费单位(如通话最小1分钟)',
  enabled TINYINT NOT NULL DEFAULT 1,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_plan_item (plan_code, version, item_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='计费项配置';

CREATE TABLE IF NOT EXISTS fee_plan_flow_tier (
  id BIGINT NOT NULL AUTO_INCREMENT,
  plan_code VARCHAR(64) NOT NULL,
  version INT NOT NULL,
  min_prepay_amount DECIMAL(18,2) NOT NULL COMMENT '最低预存金额(含)',
  max_prepay_amount DECIMAL(18,2) DEFAULT NULL COMMENT '最高预存金额(不含,空为无上限)',
  unit_price DECIMAL(18,6) NOT NULL COMMENT '流量单价(元/GB)',
  sort_no INT NOT NULL DEFAULT 0,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_tier (plan_code, version, min_prepay_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='流量阶梯配置';

/* =========================================================
   2. 钱包与流水
   ========================================================= */
CREATE TABLE IF NOT EXISTS tenant_wallet (
  id BIGINT NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT NOT NULL,
  balance_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '可用余额',
  frozen_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '冻结金额',
  credit_limit DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '授信额度(后付费可用)',
  total_recharge DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计充值',
  total_cost DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计消费',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_tenant_wallet (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户钱包';

CREATE TABLE IF NOT EXISTS tenant_wallet_txn (
  id BIGINT NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT NOT NULL,
  txn_no VARCHAR(64) NOT NULL,
  txn_type VARCHAR(32) NOT NULL COMMENT 'RECHARGE/CONSUME/ADJUST/REFUND',
  amount DECIMAL(18,2) NOT NULL COMMENT '消费建议记录为负数',
  balance_after DECIMAL(18,2) NOT NULL,
  biz_type VARCHAR(64) DEFAULT NULL COMMENT 'FLOW/CALL/TOKEN/ADD_WECHAT/OPEN_ACCOUNT',
  biz_id VARCHAR(64) DEFAULT NULL COMMENT '关联业务ID',
  remark VARCHAR(500) DEFAULT NULL,
  create_by VARCHAR(64) DEFAULT NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_txn_no (txn_no),
  KEY idx_tenant_time (tenant_id, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包流水';

/* =========================================================
   3. 用量事件与计费明细
   ========================================================= */
CREATE TABLE IF NOT EXISTS usage_event (
  id BIGINT NOT NULL AUTO_INCREMENT,
  event_id VARCHAR(64) NOT NULL COMMENT '幂等ID',
  tenant_id BIGINT NOT NULL,
  event_type VARCHAR(32) NOT NULL COMMENT 'FLOW/CALL/TOKEN_SOP/TOKEN_AI_REPLY/ADD_WECHAT/OPEN_ACCOUNT',
  sub_type VARCHAR(32) DEFAULT NULL COMMENT 'CALL_IN/CALL_OUT/AI_CALL 等',
  biz_id VARCHAR(64) DEFAULT NULL,
  usage_value DECIMAL(20,6) NOT NULL,
  usage_unit VARCHAR(32) NOT NULL COMMENT 'GB/MB/SECOND/TOKEN/COUNT',
  occurred_at DATETIME NOT NULL,
  ext_json JSON DEFAULT NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_event_id (event_id),
  KEY idx_tenant_type_time (tenant_id, event_type, occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用量事件';

CREATE TABLE IF NOT EXISTS billing_detail (
  id BIGINT NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT NOT NULL,
  statement_id BIGINT DEFAULT NULL,
  event_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(32) NOT NULL,
  sub_type VARCHAR(32) DEFAULT NULL,
  plan_code VARCHAR(64) NOT NULL,
  plan_version INT NOT NULL,
  unit_price DECIMAL(18,6) NOT NULL,
  usage_value DECIMAL(20,6) NOT NULL,
  charge_value DECIMAL(20,6) NOT NULL COMMENT '计费量，如向上取整后的分钟',
  amount DECIMAL(18,2) NOT NULL,
  billing_mode VARCHAR(16) NOT NULL COMMENT 'PREPAID/POSTPAID',
  occurred_at DATETIME NOT NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_event_type_sub (event_id, event_type, sub_type),
  KEY idx_tenant_time (tenant_id, occurred_at),
  KEY idx_statement (statement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='计费明细';

/* =========================================================
   4. 账单
   ========================================================= */
CREATE TABLE IF NOT EXISTS billing_statement (
  id BIGINT NOT NULL AUTO_INCREMENT,
  statement_no VARCHAR(64) NOT NULL,
  tenant_id BIGINT NOT NULL,
  period_type VARCHAR(16) NOT NULL COMMENT 'DAILY/MONTHLY',
  period_start DATETIME NOT NULL,
  period_end DATETIME NOT NULL,
  total_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  unpaid_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  status VARCHAR(16) NOT NULL DEFAULT 'INIT' COMMENT 'INIT/CONFIRMED/PAID/PARTIAL',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_statement_no (statement_no),
  KEY idx_tenant_period (tenant_id, period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账单主表';

CREATE TABLE IF NOT EXISTS billing_statement_item (
  id BIGINT NOT NULL AUTO_INCREMENT,
  statement_id BIGINT NOT NULL,
  item_code VARCHAR(64) NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  usage_value DECIMAL(20,6) NOT NULL DEFAULT 0,
  unit VARCHAR(32) DEFAULT NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_stmt_item (statement_id, item_code),
  KEY idx_statement_id (statement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账单项汇总';

/* =========================================================
   5. 初始化示例数据（可选）
   ========================================================= */
INSERT INTO fee_plan(plan_code, plan_name, version, status, effective_time, remark)
VALUES ('STANDARD', '标准计费方案', 1, 'PUBLISHED', NOW(), '默认方案')
ON DUPLICATE KEY UPDATE plan_name=VALUES(plan_name), status=VALUES(status), effective_time=VALUES(effective_time), remark=VALUES(remark);

INSERT INTO fee_plan_item(plan_code, version, item_code, unit, unit_price, min_charge_unit, token_unit, enabled) VALUES
('STANDARD',1,'FLOW_POSTPAID','GB',0.20,NULL,NULL,1),
('STANDARD',1,'CALL_OUT','MIN',0.30,1,NULL,1),
('STANDARD',1,'CALL_IN','MIN',0.20,1,NULL,1),
('STANDARD',1,'AI_CALL','MIN',0.15,1,NULL,1),
('STANDARD',1,'SOP_TOKEN','TOKEN',1.00,NULL,100000,1),
('STANDARD',1,'AI_REPLY_TOKEN','TOKEN',1.00,NULL,100000,1),
('STANDARD',1,'ADD_WECHAT','COUNT',0.50,NULL,NULL,1),
('STANDARD',1,'OPEN_ACCOUNT_NON_AI','TIME',1000.00,NULL,NULL,1),
('STANDARD',1,'OPEN_ACCOUNT_AI','TIME',3000.00,NULL,NULL,1)
ON DUPLICATE KEY UPDATE unit=VALUES(unit), unit_price=VALUES(unit_price), min_charge_unit=VALUES(min_charge_unit), token_unit=VALUES(token_unit), enabled=VALUES(enabled);

INSERT INTO fee_plan_flow_tier(plan_code, version, min_prepay_amount, max_prepay_amount, unit_price, sort_no) VALUES
('STANDARD',1,100000,200000,0.10,1),
('STANDARD',1,200000,NULL,0.08,2)
ON DUPLICATE KEY UPDATE max_prepay_amount=VALUES(max_prepay_amount), unit_price=VALUES(unit_price), sort_no=VALUES(sort_no);