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);