108 lines
7.2 KiB
SQL
108 lines
7.2 KiB
SQL
SET NAMES utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS voice_room_red_packet_config (
|
||
id BIGINT NOT NULL COMMENT '主键ID' PRIMARY KEY,
|
||
sys_origin VARCHAR(32) NOT NULL COMMENT '系统来源',
|
||
enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用语音房红包',
|
||
daily_send_limit INT NOT NULL DEFAULT 10 COMMENT '用户每日发红包次数上限',
|
||
delay_seconds INT NOT NULL DEFAULT 300 COMMENT '延迟红包可领取等待秒数',
|
||
refund_after_seconds INT NOT NULL DEFAULT 7200 COMMENT '红包领取开始后多少秒过期退款',
|
||
timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Riyadh' COMMENT '业务时区，用于每日限额统计',
|
||
amount_options VARCHAR(255) NOT NULL DEFAULT '15000,30000,70000,100000' COMMENT '可选红包总金额列表，逗号分隔',
|
||
count_options VARCHAR(255) NOT NULL DEFAULT '5,10,20,50' COMMENT '可选红包份数列表，逗号分隔',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_voice_room_red_packet_config (sys_origin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音房红包配置表';
|
||
|
||
CREATE TABLE IF NOT EXISTS voice_room_region_country (
|
||
id BIGINT NOT NULL COMMENT '主键ID' PRIMARY KEY,
|
||
sys_origin VARCHAR(32) NOT NULL COMMENT '系统来源',
|
||
region_code VARCHAR(32) NOT NULL COMMENT '区域编码',
|
||
country_code VARCHAR(32) NOT NULL COMMENT '国家编码',
|
||
enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用该国家区域映射',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_voice_room_region_country (sys_origin, country_code),
|
||
KEY idx_voice_room_region_country_region (sys_origin, region_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音房国家区域映射表';
|
||
|
||
CREATE TABLE IF NOT EXISTS voice_room_red_packet (
|
||
id BIGINT NOT NULL COMMENT '主键ID' PRIMARY KEY,
|
||
packet_no VARCHAR(64) NOT NULL COMMENT '红包业务编号',
|
||
request_id VARCHAR(128) NOT NULL COMMENT '客户端幂等请求ID',
|
||
sys_origin VARCHAR(32) NOT NULL COMMENT '系统来源',
|
||
room_id BIGINT NOT NULL COMMENT '语音房ID',
|
||
sender_user_id BIGINT NOT NULL COMMENT '发红包用户ID',
|
||
sender_region_code VARCHAR(32) NOT NULL COMMENT '发红包用户区域编码',
|
||
packet_mode VARCHAR(16) NOT NULL COMMENT '红包模式：IMMEDIATE立即，DELAYED延迟',
|
||
amount_mode VARCHAR(16) NOT NULL DEFAULT 'RANDOM' COMMENT '金额分配模式：RANDOM随机',
|
||
total_amount BIGINT NOT NULL COMMENT '红包总金额，单位金币',
|
||
total_count INT NOT NULL COMMENT '红包总份数',
|
||
remain_amount BIGINT NOT NULL COMMENT '剩余未领取金额，单位金币',
|
||
remain_count INT NOT NULL COMMENT '剩余未领取份数',
|
||
claim_start_time DATETIME NOT NULL COMMENT '可开始领取时间',
|
||
expire_time DATETIME NOT NULL COMMENT '过期退款时间',
|
||
status VARCHAR(32) NOT NULL COMMENT '红包状态：INIT/ACTIVE/FINISHED/EXPIRED/REFUNDED/REFUND_FAILED/FAILED',
|
||
send_wallet_event_id VARCHAR(128) NOT NULL COMMENT '发红包扣款钱包事件ID',
|
||
refund_wallet_event_id VARCHAR(128) DEFAULT NULL COMMENT '退款钱包事件ID',
|
||
refund_amount BIGINT NOT NULL DEFAULT 0 COMMENT '退款金额，单位金币',
|
||
refund_status VARCHAR(32) NOT NULL DEFAULT 'NONE' COMMENT '退款状态：NONE/PENDING/SUCCESS/FAILED',
|
||
error_message VARCHAR(1024) DEFAULT NULL COMMENT '异常信息',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_voice_room_red_packet_no (packet_no),
|
||
UNIQUE KEY uk_voice_room_red_packet_request (request_id),
|
||
UNIQUE KEY uk_voice_room_red_packet_send_event (send_wallet_event_id),
|
||
KEY idx_voice_room_red_packet_room (sys_origin, room_id, create_time),
|
||
KEY idx_voice_room_red_packet_sender_day (sys_origin, sender_user_id, create_time),
|
||
KEY idx_voice_room_red_packet_expire (status, expire_time)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音房红包主表';
|
||
|
||
CREATE TABLE IF NOT EXISTS voice_room_red_packet_slice (
|
||
id BIGINT NOT NULL COMMENT '主键ID' PRIMARY KEY,
|
||
packet_id BIGINT NOT NULL COMMENT '红包主表ID',
|
||
packet_no VARCHAR(64) NOT NULL COMMENT '红包业务编号',
|
||
sort_no INT NOT NULL COMMENT '红包份额序号',
|
||
amount BIGINT NOT NULL COMMENT '份额金额，单位金币',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'UNCLAIMED' COMMENT '份额状态：UNCLAIMED/CLAIMING/CLAIMED/RELEASED',
|
||
claim_user_id BIGINT DEFAULT NULL COMMENT '领取用户ID',
|
||
claim_record_id BIGINT DEFAULT NULL COMMENT '领取记录ID',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_voice_room_red_packet_slice (packet_id, sort_no),
|
||
KEY idx_voice_room_red_packet_slice_claim (packet_id, status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音房红包份额表';
|
||
|
||
CREATE TABLE IF NOT EXISTS voice_room_red_packet_claim (
|
||
id BIGINT NOT NULL COMMENT '主键ID' PRIMARY KEY,
|
||
packet_id BIGINT NOT NULL COMMENT '红包主表ID',
|
||
packet_no VARCHAR(64) NOT NULL COMMENT '红包业务编号',
|
||
slice_id BIGINT NOT NULL COMMENT '红包份额ID',
|
||
sys_origin VARCHAR(32) NOT NULL COMMENT '系统来源',
|
||
room_id BIGINT NOT NULL COMMENT '语音房ID',
|
||
user_id BIGINT NOT NULL COMMENT '领取用户ID',
|
||
user_region_code VARCHAR(32) NOT NULL COMMENT '领取用户区域编码',
|
||
amount BIGINT NOT NULL COMMENT '领取金额，单位金币',
|
||
wallet_event_id VARCHAR(128) NOT NULL COMMENT '领取入账钱包事件ID',
|
||
wallet_status VARCHAR(32) NOT NULL COMMENT '钱包入账状态：PENDING/SUCCESS/FAILED',
|
||
error_message VARCHAR(1024) DEFAULT NULL COMMENT '异常信息',
|
||
claim_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_voice_room_red_packet_claim_user (packet_id, user_id),
|
||
UNIQUE KEY uk_voice_room_red_packet_claim_event (wallet_event_id),
|
||
KEY idx_voice_room_red_packet_claim_packet (packet_id, claim_time)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音房红包领取记录表';
|
||
|
||
CREATE TABLE IF NOT EXISTS voice_room_red_packet_daily_counter (
|
||
id BIGINT NOT NULL COMMENT '主键ID' PRIMARY KEY,
|
||
sys_origin VARCHAR(32) NOT NULL COMMENT '系统来源',
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
day_key VARCHAR(16) NOT NULL COMMENT '统计日期，格式yyyyMMdd',
|
||
send_count INT NOT NULL DEFAULT 0 COMMENT '当日已发送红包次数',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_voice_room_red_packet_daily (sys_origin, user_id, day_key)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音房红包每日发送计数表';
|