93 lines
6.5 KiB
SQL
93 lines
6.5 KiB
SQL
USE hyapp_activity;
|
||
|
||
CREATE TABLE IF NOT EXISTS invite_activity_reward_configs (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
enabled BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否启用邀请活动',
|
||
per_invite_inviter_reward_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '每邀请一人邀请人即时奖励金币',
|
||
per_invite_invitee_reward_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '每邀请一人被邀请人即时奖励金币',
|
||
updated_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '最近更新管理员',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
PRIMARY KEY (app_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请活动配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS invite_activity_reward_tiers (
|
||
tier_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '档位自增 ID',
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
reward_type VARCHAR(32) NOT NULL COMMENT 'recharge/valid_invite',
|
||
tier_code VARCHAR(64) NOT NULL COMMENT '档位编码',
|
||
tier_name VARCHAR(128) NOT NULL COMMENT '档位展示名称',
|
||
threshold_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '累计充值金币门槛',
|
||
threshold_valid_invite_count BIGINT NOT NULL DEFAULT 0 COMMENT '有效邀请人数门槛',
|
||
reward_coin_amount BIGINT NOT NULL COMMENT '奖励金币数量',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'active' COMMENT 'active/inactive',
|
||
sort_order INT NOT NULL DEFAULT 0 COMMENT '展示排序',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
PRIMARY KEY (tier_id),
|
||
UNIQUE KEY uk_invite_activity_reward_tier_code (app_code, tier_code),
|
||
KEY idx_invite_activity_reward_tier_sort (app_code, reward_type, status, sort_order)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请活动奖励档位';
|
||
|
||
CREATE TABLE IF NOT EXISTS invite_activity_reward_events (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
event_id VARCHAR(128) NOT NULL COMMENT '上游 outbox event_id',
|
||
event_type VARCHAR(64) NOT NULL COMMENT 'WalletRechargeRecorded/UserInvited/UserInviteBecameValid',
|
||
cycle_key VARCHAR(16) NOT NULL COMMENT 'UTC 自然月，如 2026-06',
|
||
invited_user_id BIGINT NOT NULL DEFAULT 0 COMMENT '被邀请人用户 ID',
|
||
inviter_user_id BIGINT NOT NULL DEFAULT 0 COMMENT '邀请人用户 ID',
|
||
transaction_id VARCHAR(128) NOT NULL DEFAULT '' COMMENT '充值钱包交易 ID',
|
||
command_id VARCHAR(128) NOT NULL DEFAULT '' COMMENT '充值钱包命令 ID',
|
||
recharge_type VARCHAR(64) NOT NULL DEFAULT '' COMMENT '充值来源',
|
||
recharge_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '本次充值金币',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'consumed' COMMENT 'consumed/skipped',
|
||
skip_reason VARCHAR(128) NOT NULL DEFAULT '' COMMENT '跳过原因',
|
||
payload_json JSON NULL COMMENT '原始上游 payload',
|
||
occurred_at_ms BIGINT NOT NULL COMMENT '事件发生时间，UTC epoch ms',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
PRIMARY KEY (app_code, event_id),
|
||
KEY idx_invite_activity_reward_event_inviter (app_code, cycle_key, inviter_user_id, occurred_at_ms),
|
||
KEY idx_invite_activity_reward_event_invited (app_code, invited_user_id, occurred_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请活动事件幂等表';
|
||
|
||
CREATE TABLE IF NOT EXISTS invite_activity_reward_progress (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
cycle_key VARCHAR(16) NOT NULL COMMENT 'UTC 自然月，如 2026-06',
|
||
user_id BIGINT NOT NULL COMMENT '邀请人用户 ID',
|
||
total_recharge_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '本月邀请用户累计充值金币',
|
||
valid_invite_count BIGINT NOT NULL DEFAULT 0 COMMENT '本月新增有效邀请人数',
|
||
claimed_reward_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '本月已领取奖励金币',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
PRIMARY KEY (app_code, cycle_key, user_id),
|
||
KEY idx_invite_activity_reward_progress_recharge (app_code, cycle_key, total_recharge_coin_amount),
|
||
KEY idx_invite_activity_reward_progress_valid (app_code, cycle_key, valid_invite_count)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请活动用户月度进度';
|
||
|
||
CREATE TABLE IF NOT EXISTS invite_activity_reward_claims (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
claim_id VARCHAR(128) NOT NULL COMMENT '领取 ID',
|
||
cycle_key VARCHAR(16) NOT NULL COMMENT 'UTC 自然月，如 2026-06',
|
||
user_id BIGINT NOT NULL COMMENT '邀请人用户 ID',
|
||
reward_type VARCHAR(32) NOT NULL COMMENT 'recharge/valid_invite/invite_inviter/invite_invitee',
|
||
command_id VARCHAR(128) NOT NULL COMMENT 'H5 领取或系统即时发奖命令 ID',
|
||
tier_id BIGINT NOT NULL COMMENT '领取档位 ID；即时邀请奖励使用对方用户 ID 做幂等维度',
|
||
tier_code VARCHAR(64) NOT NULL COMMENT '领取档位编码或即时奖励类型',
|
||
tier_name VARCHAR(128) NOT NULL COMMENT '领取档位名称或即时奖励名称',
|
||
threshold_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '累计充值金币门槛',
|
||
threshold_valid_invite_count BIGINT NOT NULL DEFAULT 0 COMMENT '有效邀请人数门槛',
|
||
reached_value BIGINT NOT NULL DEFAULT 0 COMMENT '领取时已达成值',
|
||
reward_coin_amount BIGINT NOT NULL COMMENT '本次实际发放金币',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT 'pending/granted/failed',
|
||
wallet_command_id VARCHAR(128) NOT NULL COMMENT '钱包发奖命令 ID',
|
||
wallet_transaction_id VARCHAR(128) NOT NULL DEFAULT '' COMMENT '钱包交易 ID',
|
||
failure_reason VARCHAR(512) NOT NULL DEFAULT '' COMMENT '失败原因',
|
||
granted_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '发放成功时间',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
PRIMARY KEY (app_code, claim_id),
|
||
UNIQUE KEY uk_invite_activity_reward_user_tier (app_code, cycle_key, user_id, reward_type, tier_id),
|
||
UNIQUE KEY uk_invite_activity_reward_wallet_command (app_code, wallet_command_id),
|
||
KEY idx_invite_activity_reward_claim_user (app_code, cycle_key, user_id, created_at_ms),
|
||
KEY idx_invite_activity_reward_claim_status (app_code, status, created_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请活动金币领取记录';
|