create table if not exists ticket_package (
|
id bigint not null auto_increment,
|
package_no varchar(64) not null comment '票包编号',
|
package_name varchar(128) not null comment '票包名称',
|
merchant_id bigint default null comment '指定兑换商家ID',
|
cover_url varchar(500) default null comment '封面图',
|
talent_levels varchar(255) default null comment '适用人才卡等级，空表示全部',
|
claim_price decimal(10,2) not null default 0.00 comment '领取价',
|
per_user_limit int not null default 1 comment '单用户周期内限领数量',
|
valid_start date default null comment '有效开始日期',
|
valid_end date default null comment '有效结束日期',
|
app_name varchar(128) default null comment '指定商家APP名称',
|
app_jump_url varchar(500) default null comment '指定商家APP或H5跳转地址',
|
usage_text varchar(500) default null comment '使用说明',
|
status tinyint not null default 1 comment '状态：1启用 0停用',
|
sort_no int not null default 0,
|
created_at datetime not null default current_timestamp,
|
updated_at datetime not null default current_timestamp on update current_timestamp,
|
deleted tinyint not null default 0,
|
primary key (id),
|
unique key uk_ticket_package_no (package_no),
|
key idx_ticket_package_merchant (merchant_id),
|
key idx_ticket_package_status (status)
|
) engine=InnoDB default charset=utf8mb4 comment='票包配置';
|
|
create table if not exists ticket_package_code (
|
id bigint not null auto_increment,
|
package_id bigint not null comment '票包ID',
|
exchange_code varchar(128) not null comment '兑换码',
|
batch_no varchar(64) default null comment '导入批次号',
|
status varchar(32) not null default 'AVAILABLE' comment 'AVAILABLE可发放 LOCKED锁定 ISSUED已发放 USED已使用 VOID作废',
|
locked_by_talent_id bigint default null comment '锁定持卡人ID',
|
locked_by_card_id bigint default null comment '锁定人才卡ID',
|
locked_at datetime default null comment '锁定时间',
|
issued_claim_id bigint default null comment '发放记录ID',
|
issued_at datetime default null comment '发放时间',
|
used_at datetime default null comment '使用时间',
|
remark varchar(500) default null comment '备注',
|
created_at datetime not null default current_timestamp,
|
updated_at datetime not null default current_timestamp on update current_timestamp,
|
deleted tinyint not null default 0,
|
primary key (id),
|
unique key uk_ticket_package_code (exchange_code),
|
key idx_ticket_package_code_package_status (package_id, status),
|
key idx_ticket_package_code_batch (batch_no)
|
) engine=InnoDB default charset=utf8mb4 comment='票包兑换码码池';
|
|
create table if not exists ticket_package_claim (
|
id bigint not null auto_increment,
|
claim_no varchar(64) not null comment '领取编号',
|
package_id bigint not null comment '票包ID',
|
code_id bigint not null comment '兑换码ID',
|
merchant_id bigint default null comment '指定兑换商家ID',
|
talent_id bigint not null comment '领取人ID',
|
card_id bigint not null comment '使用人才卡ID',
|
primary_card_id bigint not null comment '主卡ID，用于共享额度统计',
|
card_no varchar(64) not null comment '人才卡号',
|
card_level varchar(64) default null comment '人才卡等级',
|
talent_name varchar(64) default null comment '领取人姓名快照',
|
talent_mobile varchar(32) default null comment '领取人手机号快照',
|
exchange_code varchar(128) not null comment '兑换码',
|
status varchar(32) not null default 'ISSUED' comment 'ISSUED已发放 USED已使用 EXPIRED已过期 CANCELLED已取消',
|
valid_end date default null comment '有效结束日期',
|
idempotency_key varchar(128) not null comment '幂等键',
|
used_at datetime default null comment '使用时间',
|
created_at datetime not null default current_timestamp,
|
updated_at datetime not null default current_timestamp on update current_timestamp,
|
deleted tinyint not null default 0,
|
primary key (id),
|
unique key uk_ticket_package_claim_no (claim_no),
|
unique key uk_ticket_package_claim_idempotency (idempotency_key),
|
unique key uk_ticket_package_claim_code (code_id),
|
key idx_ticket_package_claim_package (package_id),
|
key idx_ticket_package_claim_talent (talent_id),
|
key idx_ticket_package_claim_card_time (card_id, created_at),
|
key idx_ticket_package_claim_primary_card_time (primary_card_id, created_at),
|
key idx_ticket_package_claim_status (status)
|
) engine=InnoDB default charset=utf8mb4 comment='票包领取记录';
|
