yml2213
984433f866
- 数据库与数据模型：新增 migration 000081，支持用户专属邀请码、被邀请人绑定与实名时间戳；新增邀请返利配置表与结算明细表 - 业务与结算逻辑：实现首月 60%、次月 30%、往后 15% 阶梯返利算法；买卖双向独立寻源并根据订单平台利润 50% 单边基准核算；与终态订单结算和钱包可用余额强幂等联动 - 分销站点支持：支持主站与加盟分站独立配置返利比例；跨站交易按照收号端与售卖端独立出资，资金互不侵蚀 - 前端交互与入口： - 新增专属短链注册落地页 /i/:code，自动带入并锁定邀请码，引导实名认证 - 个人中心与移动端新增邀请返利看板（专属码、推广链接、统计面板、好友记录与返利流水明细） - 管理后台新增邀请返利管理（比例配置、邀请关系与返利流水审计） - 验证方式： - 通过 repository_test.go 单元测试（阶梯区间、实名激活、跨站多方分流验证） - 通过 npm run format、npm run check 与 scripts/check.sh 全量门禁校验
77 lines
4.3 KiB
SQL
77 lines
4.3 KiB
SQL
-- +goose Up
|
|
|
|
ALTER TABLE users
|
|
ADD COLUMN invite_code VARCHAR(32) NULL COMMENT '用户专属邀请码' AFTER register_track_code,
|
|
ADD COLUMN invited_by_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '邀请人用户ID' AFTER invite_code,
|
|
ADD COLUMN invite_verified_at DATETIME NULL COMMENT '被邀请人生效实名时间' AFTER invited_by_id,
|
|
ADD UNIQUE KEY uk_users_invite_code (invite_code),
|
|
ADD KEY idx_users_invited_by_id (invited_by_id);
|
|
|
|
CREATE TABLE user_invitations (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
inviter_id BIGINT UNSIGNED NOT NULL COMMENT '邀请人用户ID',
|
|
invitee_id BIGINT UNSIGNED NOT NULL COMMENT '被邀请人用户ID',
|
|
distribution_site_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属分销站点ID (0=主站)',
|
|
invite_code VARCHAR(32) NOT NULL DEFAULT '' COMMENT '当时使用的邀请码',
|
|
status VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending=注册待实名, active=已实名生效, invalid=已失效',
|
|
verified_at DATETIME NULL COMMENT '被邀请人实名通过时间(首月计时起点)',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_user_invitations_invitee (invitee_id),
|
|
KEY idx_user_invitations_inviter (inviter_id, status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户邀请关系表';
|
|
|
|
CREATE TABLE invitation_rebate_configs (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
distribution_site_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属分销站点ID (0=主站全局默认)',
|
|
first_month_rate DECIMAL(6,4) NOT NULL DEFAULT 0.6000 COMMENT '首月返利比例 (默认0.60)',
|
|
second_month_rate DECIMAL(6,4) NOT NULL DEFAULT 0.3000 COMMENT '次月返利比例 (默认0.30)',
|
|
subsequent_rate DECIMAL(6,4) NOT NULL DEFAULT 0.1500 COMMENT '往后返利比例 (默认0.15)',
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用返利',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_invitation_rebate_configs_site (distribution_site_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邀请返利比例配置表';
|
|
|
|
INSERT INTO invitation_rebate_configs (distribution_site_id, first_month_rate, second_month_rate, subsequent_rate, enabled, created_at, updated_at)
|
|
VALUES (0, 0.6000, 0.3000, 0.1500, 1, NOW(), NOW())
|
|
ON DUPLICATE KEY UPDATE updated_at = NOW();
|
|
|
|
CREATE TABLE invitation_rebate_ledgers (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
order_id BIGINT UNSIGNED NOT NULL COMMENT '关联订单ID',
|
|
order_no VARCHAR(64) NOT NULL COMMENT '订单号',
|
|
distribution_site_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分担成本的站点ID (0=主站)',
|
|
inviter_id BIGINT UNSIGNED NOT NULL COMMENT '获得返利的用户ID',
|
|
invitee_id BIGINT UNSIGNED NOT NULL COMMENT '贡献交易的被邀请人ID',
|
|
trade_role VARCHAR(16) NOT NULL COMMENT '交易端角色: buyer=买家, seller=卖家',
|
|
order_profit_basis_cent BIGINT NOT NULL DEFAULT 0 COMMENT '订单收益基数(分)',
|
|
single_side_basis_cent BIGINT NOT NULL DEFAULT 0 COMMENT '单边收益基数(分)',
|
|
rate_applied DECIMAL(6,4) NOT NULL DEFAULT 0 COMMENT '计算时采用的返利比例',
|
|
days_since_verified INT NOT NULL DEFAULT 0 COMMENT '被邀请人实名至成交的天数',
|
|
cycle_stage VARCHAR(32) NOT NULL DEFAULT '' COMMENT '周期阶段: first_month/second_month/subsequent',
|
|
rebate_amount_cent BIGINT NOT NULL DEFAULT 0 COMMENT '实际返利金额(分)',
|
|
wallet_ledger_no VARCHAR(64) NOT NULL DEFAULT '' COMMENT '关联钱包流水号',
|
|
status VARCHAR(32) NOT NULL DEFAULT 'settled' COMMENT '状态: settled=已发放, cancelled=已取消/冲正',
|
|
settled_at DATETIME NOT NULL COMMENT '结算发放时间',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_invitation_rebate_order_role (order_id, trade_role),
|
|
KEY idx_invitation_rebate_inviter (inviter_id, settled_at),
|
|
KEY idx_invitation_rebate_site (distribution_site_id, settled_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邀请返利结算明细表';
|
|
|
|
-- +goose Down
|
|
|
|
DROP TABLE IF EXISTS invitation_rebate_ledgers;
|
|
DROP TABLE IF EXISTS invitation_rebate_configs;
|
|
DROP TABLE IF EXISTS user_invitations;
|
|
|
|
ALTER TABLE users
|
|
DROP COLUMN invite_verified_at,
|
|
DROP COLUMN invited_by_id,
|
|
DROP COLUMN invite_code;
|