105 lines
4.4 KiB
SQL
105 lines
4.4 KiB
SQL
CREATE TABLE IF NOT EXISTS week_star_activity_config (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
sys_origin VARCHAR(32) NOT NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
start_at DATETIME NOT NULL,
|
|
end_at DATETIME NOT NULL,
|
|
timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Riyadh',
|
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_week_star_sys_origin_window (sys_origin, start_at, end_at),
|
|
KEY idx_week_star_active_lookup (sys_origin, enabled, start_at, end_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS week_star_activity_gift_config (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
config_id BIGINT NOT NULL,
|
|
gift_id BIGINT NOT NULL,
|
|
gift_name VARCHAR(255) DEFAULT NULL,
|
|
gift_photo VARCHAR(1024) DEFAULT NULL,
|
|
gift_candy BIGINT NOT NULL DEFAULT 0,
|
|
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,
|
|
UNIQUE KEY uk_week_star_gift_sort (config_id, sort),
|
|
KEY idx_week_star_gift_config (config_id, gift_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS week_star_activity_reward_config (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
config_id BIGINT NOT NULL,
|
|
`rank` INT NOT NULL,
|
|
reward_group_id BIGINT NOT NULL,
|
|
reward_group_name 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_week_star_reward_rank (config_id, `rank`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS week_star_gift_log (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
config_id BIGINT NOT NULL,
|
|
cycle_key VARCHAR(32) NOT NULL,
|
|
track_id VARCHAR(128) NOT NULL,
|
|
send_user_id BIGINT NOT NULL,
|
|
gift_id BIGINT NOT NULL,
|
|
quantity BIGINT NOT NULL DEFAULT 0,
|
|
accept_user_size BIGINT NOT NULL DEFAULT 0,
|
|
score_gold BIGINT NOT NULL DEFAULT 0,
|
|
event_time DATETIME NOT NULL,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'SUCCESS',
|
|
raw_payload LONGTEXT 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_week_star_track_id (track_id),
|
|
KEY idx_week_star_gift_log_cycle (config_id, cycle_key),
|
|
KEY idx_week_star_gift_log_sender (send_user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS week_star_rank_snapshot (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
config_id BIGINT NOT NULL,
|
|
cycle_key VARCHAR(32) NOT NULL,
|
|
`rank` INT NOT NULL,
|
|
period_start_at DATETIME NOT NULL,
|
|
period_end_at DATETIME NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
account VARCHAR(64) DEFAULT NULL,
|
|
user_avatar VARCHAR(1024) DEFAULT NULL,
|
|
user_nickname VARCHAR(255) DEFAULT NULL,
|
|
country_code VARCHAR(32) DEFAULT NULL,
|
|
country_name VARCHAR(128) DEFAULT NULL,
|
|
score_gold 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_week_star_snapshot_rank (config_id, cycle_key, `rank`),
|
|
KEY idx_week_star_snapshot_cycle (config_id, cycle_key)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS week_star_reward_record (
|
|
id BIGINT NOT NULL PRIMARY KEY,
|
|
config_id BIGINT NOT NULL,
|
|
cycle_key VARCHAR(32) NOT NULL,
|
|
`rank` INT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
account VARCHAR(64) DEFAULT NULL,
|
|
user_avatar VARCHAR(1024) DEFAULT NULL,
|
|
user_nickname VARCHAR(255) DEFAULT NULL,
|
|
country_code VARCHAR(32) DEFAULT NULL,
|
|
country_name VARCHAR(128) DEFAULT NULL,
|
|
reward_group_id BIGINT NOT NULL,
|
|
reward_group_name VARCHAR(255) DEFAULT NULL,
|
|
score_gold BIGINT NOT NULL DEFAULT 0,
|
|
track_id BIGINT NOT NULL,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
|
retry_count INT NOT NULL DEFAULT 0,
|
|
last_error VARCHAR(1024) DEFAULT NULL,
|
|
sent_at DATETIME 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_week_star_reward_record_rank (config_id, cycle_key, `rank`),
|
|
KEY idx_week_star_reward_record_cycle (config_id, cycle_key),
|
|
KEY idx_week_star_reward_record_status (status),
|
|
KEY idx_week_star_reward_record_user (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|