204 lines
9.5 KiB
SQL
204 lines
9.5 KiB
SQL
create table lottery_activity
|
||
(
|
||
activity_id bigint auto_increment comment '活动id'
|
||
primary key,
|
||
activity_name varchar(100) null comment '活动名称',
|
||
start_time datetime null comment '开始时间',
|
||
end_time datetime null comment '结束时间',
|
||
description text null comment '活动描述',
|
||
activity_image varchar(255) null comment '活动图片url',
|
||
create_time datetime null comment '创建时间',
|
||
update_time datetime null comment '更新时间',
|
||
is_active tinyint default 1 null comment '是否启用（1-启用，0-禁用）'
|
||
)
|
||
comment '抽奖活动表' collate = utf8mb4_unicode_ci;
|
||
|
||
create table lottery_activity_award_rela
|
||
(
|
||
id bigint auto_increment comment '关联id'
|
||
primary key,
|
||
activity_id bigint not null comment '活动id',
|
||
award_id bigint not null comment '奖项id',
|
||
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
|
||
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间',
|
||
constraint uk_activity_award
|
||
unique (activity_id, award_id)
|
||
)
|
||
comment '活动奖项关联表';
|
||
|
||
create index idx_activity_id
|
||
on lottery_activity_award_rela (activity_id);
|
||
|
||
create index idx_award_id
|
||
on lottery_activity_award_rela (award_id);
|
||
|
||
create table lottery_award
|
||
(
|
||
award_id bigint auto_increment comment '奖项id'
|
||
primary key,
|
||
award_name varchar(100) not null comment '奖项名称',
|
||
award_level int default 1 null comment '奖项等级（1-一等奖，2-二等奖...）',
|
||
card_nums int default 1 null comment '每个奖项的卡片数量',
|
||
valid_days int default 7 null comment '有效天数',
|
||
probability varchar(50) default '' null comment '中奖概率（百分比）',
|
||
award_image varchar(500) default '' null comment '奖项图片URL',
|
||
description varchar(500) default '' null comment '奖项描述',
|
||
status tinyint default 1 null comment '奖项状态：0-禁用 1-启用',
|
||
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
|
||
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间'
|
||
)
|
||
comment '抽奖奖项表';
|
||
|
||
create table lottery_card
|
||
(
|
||
card_id bigint auto_increment comment '卡片id'
|
||
primary key,
|
||
card_no varchar(32) null comment '卡片编号（唯一）',
|
||
batch_no varchar(32) null comment '批次号',
|
||
award_id bigint null comment '关联的奖项id',
|
||
status tinyint default 0 null comment '卡片状态：0-未领取 1-已领取未使用 2-已使用 3-已过期',
|
||
user_id bigint null comment '领取用户id（未领取时为空）',
|
||
receive_time datetime null comment '领取时间',
|
||
expire_time datetime null comment '过期时间',
|
||
use_time datetime null comment '使用时间（兑换）',
|
||
exchange_id int null comment '兑换id(当卡片被用来兑换奖品时填入)',
|
||
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
|
||
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间',
|
||
card_name varchar(32) null comment '卡片名称'
|
||
)
|
||
comment '抽奖卡片实例表';
|
||
|
||
create index idx_award_id
|
||
on lottery_card (award_id);
|
||
|
||
create index idx_batch_no
|
||
on lottery_card (batch_no);
|
||
|
||
create index idx_exchange_id
|
||
on lottery_card (exchange_id);
|
||
|
||
create index idx_expire_time
|
||
on lottery_card (expire_time);
|
||
|
||
create index idx_status
|
||
on lottery_card (status);
|
||
|
||
create index idx_user_id
|
||
on lottery_card (user_id);
|
||
|
||
create table lottery_exchange_record
|
||
(
|
||
exchange_id bigint auto_increment comment '兑换id'
|
||
primary key,
|
||
user_id bigint null comment '用户id',
|
||
activity_id bigint null comment '抽奖活动Id',
|
||
award_id bigint null comment '奖品id',
|
||
award_name varchar(100) null comment '奖品名称',
|
||
exchange_time datetime null comment '兑换时间',
|
||
card_num int null comment '卡片消耗数量',
|
||
assert_type int null comment '兑换的资产类型',
|
||
change_money decimal(10, 2) default 0.00 null comment '兑换的资产数量',
|
||
description varchar(500) null comment '描述',
|
||
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
|
||
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间'
|
||
)
|
||
comment '兑换记录表' collate = utf8mb4_unicode_ci;
|
||
|
||
create index idx_activity_id
|
||
on lottery_exchange_record (activity_id);
|
||
|
||
create index idx_award_id
|
||
on lottery_exchange_record (award_id);
|
||
|
||
create index idx_user_id
|
||
on lottery_exchange_record (user_id);
|
||
|
||
create table lottery_points_record
|
||
(
|
||
id bigint auto_increment comment '记录id'
|
||
primary key,
|
||
user_id bigint null comment '用户id',
|
||
points int null comment '积分变动数量（正数为增加，负数为减少）',
|
||
point_type int null comment '积分类型（1-消费配送，2-兑换消耗，3-活动奖励，4-其他）',
|
||
record_id bigint null comment '抽奖记录id',
|
||
description varchar(255) null comment '积分变动描述',
|
||
create_time datetime null comment '创建时间',
|
||
update_time datetime null comment '更新时间',
|
||
current_points int null comment '当前剩余积分',
|
||
points_before int null comment '变动前积分',
|
||
points_after int null comment '变动后积分'
|
||
)
|
||
comment '积分记录表' collate = utf8mb4_unicode_ci;
|
||
|
||
create index idx_record_id
|
||
on lottery_points_record (record_id);
|
||
|
||
create index idx_user_id
|
||
on lottery_points_record (user_id);
|
||
|
||
create table lottery_points_rule
|
||
(
|
||
rule_id int auto_increment comment '规则id'
|
||
primary key,
|
||
rule_name varchar(100) null comment '规则名称',
|
||
points_type int null comment '积分类型（1-抽奖获得，2-兑换消耗，3-活动奖励）',
|
||
points_value int null comment '积分值',
|
||
description text null comment '规则描述',
|
||
is_active tinyint default 1 null comment '是否启用（1-启用，0-禁用）',
|
||
create_time datetime null comment '创建时间',
|
||
update_time datetime null comment '更新时间',
|
||
start_time datetime null comment '开始时间',
|
||
end_time datetime null comment '结束时间'
|
||
)
|
||
comment '积分规则表' collate = utf8mb4_unicode_ci;
|
||
|
||
create table lottery_probability_segment
|
||
(
|
||
segment_id bigint auto_increment comment '区间配置ID'
|
||
primary key,
|
||
activity_id bigint not null comment '活动ID',
|
||
segment_size int default 25000 not null comment '每个区间的大小',
|
||
award_level int not null comment '奖项等级（1=省,2=地级市,3=县级市,4=自治县,5=县,6=阳光普照）',
|
||
quota_per_segment int not null comment '每个区间内该等级奖项的配额',
|
||
description varchar(255) null comment '描述',
|
||
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
|
||
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间'
|
||
)
|
||
comment '概率区间配置表（防止奖项扎堆）' charset = utf8mb4;
|
||
|
||
create index idx_activity
|
||
on lottery_probability_segment (activity_id);
|
||
|
||
create index idx_activity_level
|
||
on lottery_probability_segment (activity_id, award_level);
|
||
|
||
create table lottery_record
|
||
(
|
||
record_id bigint auto_increment comment '记录id'
|
||
primary key,
|
||
activity_id bigint null comment '活动ID',
|
||
user_id bigint null comment '用户id',
|
||
award_id bigint null comment '奖项id',
|
||
batch_no varchar(64) null comment '批次号（同一次抽奖的多个结果）',
|
||
draw_time datetime null comment '抽奖时间',
|
||
is_win tinyint null comment '是否中奖（1-中奖，0-未中奖）',
|
||
points_cost int default 0 null comment '本次消耗积分',
|
||
points_gained int default 0 null comment '获得积分',
|
||
create_time datetime null comment '创建时间',
|
||
update_time datetime null comment '更新时间'
|
||
)
|
||
comment '抽奖记录表' collate = utf8mb4_unicode_ci;
|
||
|
||
create index idx_activity_user
|
||
on lottery_record (activity_id, user_id);
|
||
|
||
create index idx_award_id
|
||
on lottery_record (award_id);
|
||
|
||
create index idx_batch_no
|
||
on lottery_record (batch_no);
|
||
|
||
create index idx_user_id
|
||
on lottery_record (user_id);
|
||
|