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='票包领取记录';