81 lines
5.6 KiB
SQL
81 lines
5.6 KiB
SQL
CREATE TABLE IF NOT EXISTS agency_opening_cycles (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
cycle_id VARCHAR(96) NOT NULL COMMENT '代理开业周期 ID',
|
||
activity_code VARCHAR(64) NOT NULL DEFAULT 'agency_opening' COMMENT '活动编码',
|
||
title VARCHAR(128) NOT NULL DEFAULT '' COMMENT '周期标题',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'draft' COMMENT 'draft/active/disabled/settling/settled',
|
||
start_ms BIGINT NOT NULL COMMENT 'UTC epoch ms 开始，包含',
|
||
end_ms BIGINT NOT NULL COMMENT 'UTC epoch ms 结束，不包含',
|
||
min_host_count INT NOT NULL DEFAULT 10 COMMENT '申请要求的有效主播数',
|
||
max_agency_age_days INT NOT NULL DEFAULT 7 COMMENT '代理创建后允许申请的最大天数，0 表示不限制',
|
||
created_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '创建管理员',
|
||
updated_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '更新管理员',
|
||
settled_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '结算完成时间',
|
||
locked_by VARCHAR(96) NOT NULL DEFAULT '' COMMENT '结算 worker 锁',
|
||
lock_until_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, cycle_id),
|
||
KEY idx_agency_opening_cycle_current (app_code, activity_code, status, start_ms, end_ms),
|
||
KEY idx_agency_opening_cycle_due (app_code, activity_code, status, end_ms, lock_until_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='代理开业活动周期';
|
||
|
||
CREATE TABLE IF NOT EXISTS agency_opening_cycle_rewards (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
cycle_id VARCHAR(96) NOT NULL COMMENT '代理开业周期 ID',
|
||
rank_no INT NOT NULL COMMENT '档位序号',
|
||
threshold_coin_spent BIGINT NOT NULL DEFAULT 0 COMMENT '命中该档需要的代理房间流水金币',
|
||
reward_coin_amount 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, cycle_id, rank_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='代理开业流水奖励档位';
|
||
|
||
CREATE TABLE IF NOT EXISTS agency_opening_applications (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
application_id VARCHAR(96) NOT NULL COMMENT '申请 ID',
|
||
cycle_id VARCHAR(96) NOT NULL COMMENT '代理开业周期 ID',
|
||
agency_id BIGINT NOT NULL COMMENT '代理 ID',
|
||
agency_owner_user_id BIGINT NOT NULL COMMENT '代理 owner 用户 ID',
|
||
agency_name VARCHAR(128) NOT NULL DEFAULT '' COMMENT '代理名称快照',
|
||
host_count INT NOT NULL DEFAULT 0 COMMENT '申请时有效主播数快照',
|
||
agency_created_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '代理创建时间快照',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'applied' COMMENT 'applied/approved/pending/granted/failed/expired',
|
||
score_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '周期内开业流水金币',
|
||
reward_rank_no INT NOT NULL DEFAULT 0 COMMENT '结算命中的档位序号',
|
||
reward_threshold_coin_spent BIGINT NOT NULL DEFAULT 0 COMMENT '结算命中的流水阈值',
|
||
reward_coin_amount BIGINT NOT NULL DEFAULT 0 COMMENT '结算奖励金币',
|
||
wallet_command_id VARCHAR(128) NOT NULL DEFAULT '' COMMENT '钱包幂等命令',
|
||
wallet_transaction_id VARCHAR(128) NOT NULL DEFAULT '' COMMENT '钱包交易 ID',
|
||
failure_reason VARCHAR(512) NOT NULL DEFAULT '' COMMENT '失败原因',
|
||
applied_at_ms BIGINT NOT NULL COMMENT '申请时间',
|
||
approved_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '后台同意时间',
|
||
score_start_ms BIGINT NOT NULL DEFAULT 0 COMMENT '计分开始时间',
|
||
score_end_ms BIGINT NOT NULL DEFAULT 0 COMMENT '计分截止时间',
|
||
reviewed_by_admin_id BIGINT NOT NULL DEFAULT 0 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, application_id),
|
||
UNIQUE KEY uk_agency_opening_owner_once (app_code, agency_owner_user_id),
|
||
KEY idx_agency_opening_wallet_command (app_code, wallet_command_id),
|
||
KEY idx_agency_opening_application_owner (app_code, agency_owner_user_id, applied_at_ms),
|
||
KEY idx_agency_opening_agency_cycle (app_code, cycle_id, agency_id),
|
||
KEY idx_agency_opening_application_rank (app_code, cycle_id, score_coin_amount, applied_at_ms, agency_id),
|
||
KEY idx_agency_opening_application_status (app_code, cycle_id, status, updated_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='代理开业申请和结算事实';
|
||
|
||
CREATE TABLE IF NOT EXISTS agency_opening_score_events (
|
||
app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码',
|
||
source_event_id VARCHAR(128) NOT NULL COMMENT 'room-service outbox event_id',
|
||
cycle_id VARCHAR(96) NOT NULL COMMENT '代理开业周期 ID',
|
||
application_id VARCHAR(96) NOT NULL COMMENT '命中的申请 ID',
|
||
agency_id BIGINT NOT NULL COMMENT '代理 ID',
|
||
target_user_id BIGINT NOT NULL COMMENT '收礼主播用户 ID',
|
||
score_delta BIGINT NOT NULL COMMENT '本次流水金币',
|
||
occurred_at_ms BIGINT NOT NULL COMMENT '事件发生时间，UTC epoch ms',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
PRIMARY KEY (app_code, source_event_id),
|
||
KEY idx_agency_opening_score_event_application (app_code, application_id, occurred_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='代理开业流水事件幂等表';
|