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='语音房红包每日发送计数表';