129 lines
5.6 KiB
SQL
129 lines
5.6 KiB
SQL
CREATE TABLE IF NOT EXISTS invite_campaign_config (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
download_url VARCHAR(512) DEFAULT NULL,
|
|
share_title VARCHAR(255) DEFAULT NULL,
|
|
share_desc VARCHAR(255) DEFAULT NULL,
|
|
service_contact VARCHAR(255) DEFAULT NULL,
|
|
timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Riyadh',
|
|
anti_cheat_same_ip_once TINYINT(1) NOT NULL DEFAULT 1,
|
|
anti_cheat_same_device_once TINYINT(1) NOT NULL DEFAULT 1,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
create_user BIGINT DEFAULT NULL,
|
|
update_user BIGINT DEFAULT NULL,
|
|
UNIQUE KEY uk_invite_campaign_sys_origin (sys_origin)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_reward_rule (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
rule_type VARCHAR(32) NOT NULL,
|
|
threshold_value BIGINT NOT NULL DEFAULT 0,
|
|
gold_amount BIGINT NOT NULL DEFAULT 0,
|
|
reward_group_id BIGINT DEFAULT NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
sort INT NOT NULL DEFAULT 0,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
create_user BIGINT DEFAULT NULL,
|
|
update_user BIGINT DEFAULT NULL,
|
|
KEY idx_invite_rule_sys_type_sort (sys_origin, rule_type, sort)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_relation (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
inviter_user_id BIGINT NOT NULL,
|
|
invitee_user_id BIGINT NOT NULL,
|
|
invite_code VARCHAR(32) NOT NULL,
|
|
bind_ip VARCHAR(128) DEFAULT NULL,
|
|
device_fingerprint VARCHAR(255) DEFAULT NULL,
|
|
eligible_status VARCHAR(32) NOT NULL DEFAULT 'ELIGIBLE',
|
|
block_reason VARCHAR(255) DEFAULT NULL,
|
|
bind_time DATETIME NOT NULL,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_invite_relation_invitee (sys_origin, invitee_user_id),
|
|
KEY idx_invite_relation_sys_inviter (sys_origin, inviter_user_id),
|
|
KEY idx_invite_relation_ip (bind_ip),
|
|
KEY idx_invite_relation_device (device_fingerprint)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_invitee_progress (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
relation_id BIGINT NOT NULL,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
inviter_user_id BIGINT NOT NULL,
|
|
invitee_user_id BIGINT NOT NULL,
|
|
total_recharge_coins BIGINT NOT NULL DEFAULT 0,
|
|
valid_user TINYINT(1) NOT NULL DEFAULT 0,
|
|
valid_time DATETIME DEFAULT NULL,
|
|
rewarded_rule_ids VARCHAR(255) DEFAULT NULL,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_invitee_progress_relation (relation_id),
|
|
UNIQUE KEY uk_invitee_progress_invitee (invitee_user_id),
|
|
KEY idx_invitee_progress_inviter (inviter_user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_monthly_progress (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
inviter_user_id BIGINT NOT NULL,
|
|
month_key VARCHAR(16) NOT NULL,
|
|
invite_count BIGINT NOT NULL DEFAULT 0,
|
|
valid_user_count BIGINT NOT NULL DEFAULT 0,
|
|
total_recharge_coins BIGINT NOT NULL DEFAULT 0,
|
|
reward_gold_coins BIGINT NOT NULL DEFAULT 0,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_invite_monthly_progress (sys_origin, inviter_user_id, month_key)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_task_claim (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
inviter_user_id BIGINT NOT NULL,
|
|
month_key VARCHAR(16) NOT NULL,
|
|
rule_id BIGINT NOT NULL,
|
|
claim_time DATETIME NOT NULL,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_invite_task_claim (sys_origin, inviter_user_id, month_key, rule_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_reward_log (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
relation_id BIGINT DEFAULT NULL,
|
|
inviter_user_id BIGINT DEFAULT NULL,
|
|
invitee_user_id BIGINT DEFAULT NULL,
|
|
target_user_id BIGINT NOT NULL,
|
|
rule_id BIGINT DEFAULT NULL,
|
|
reward_scene VARCHAR(32) NOT NULL,
|
|
business_key VARCHAR(128) NOT NULL,
|
|
gold_amount BIGINT NOT NULL DEFAULT 0,
|
|
reward_group_id BIGINT DEFAULT NULL,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
|
error_message VARCHAR(255) DEFAULT NULL,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_invite_reward_business (business_key),
|
|
KEY idx_invite_reward_target (sys_origin, target_user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_campaign_recharge_event (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
order_id VARCHAR(128) NOT NULL,
|
|
inviter_user_id BIGINT DEFAULT NULL,
|
|
invitee_user_id BIGINT NOT NULL,
|
|
recharge_coins BIGINT NOT NULL DEFAULT 0,
|
|
pay_time DATETIME NOT NULL,
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_invite_recharge_order (order_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|