# create database if not exists ln_scene character set utf8mb4 collate utf8mb4_unicode_ci;
# use ln_scene;
# -- >>------------------------------------------------------------------------------------ |20210817会员与券改版
# 券发放管理
drop table if exists candy_mgt_coupon;
create table candy_mgt_coupon
(
    mid         bigint unsigned auto_increment primary key,
    mcoupon_id  varchar(64) not null,
    coupon_id   varchar(64) not null comment '~candy_coupon.coupon_id',
    state       tinyint comment '发放状态[0-未发放|1-已发放|2-无效|3-已取消｜9-发放中|10-会员礼包初始模版]',

#     bind_type   smallint comment '领取方式[0-用户输入兑换｜1-发放至用户｜2-积分商品]',
    event_amt   int comment '发放量',
    event_type  tinyint comment '发放类型[0-保留｜1-会员｜2-手机号|10-全体用户]',
    event_limit text comment '`发放类型`为2-手机号时发放手机号以,分隔',
    event_at    datetime(3) comment '发放时间(立即-当前时间+3分钟｜预约-点选时间)',

    operator    varchar(64) not null comment '操作人',
    created_at  datetime(3) not null,
    updated_at  datetime(3),

    comment     text
) engine InnoDB comment '券发放管理';
create index idx_candy_mgt_coupon_id on candy_mgt_coupon (mcoupon_id);


# 券基础信息
drop table if exists candy_coupon;
create table candy_coupon
(
    mid             bigint unsigned auto_increment primary key,
    coupon_id       varchar(64) not null,
    state           tinyint comment '券状态[0-INIT|1-NORMAL|2-INVALID]',

    title           varchar(50) not null comment '券标题',
    label           varchar(50) comment '标注',
    notice          varchar(200) comment '注意/须知',
    exclusive       smallint comment '专享标识[0-常规｜1-会员礼包]',
    busi_type       smallint comment '业务类别[0-全场｜1-演出｜2-商品｜3-优先购]',
    cou_type        smallint comment '券类型[1-代金券｜2-满减券｜3-兑换券｜4-折扣券|101-优先券]',
    bind_type       smallint comment '领取方式[0-用户输入兑换｜1-发放至用户｜2-积分商品｜3-发放需领取｜4-会员专属]',

    discount        decimal(8, 2) comment '折扣[8折即0.8]',
    val_face        decimal(8, 2) comment '面值',
    val_over        decimal(8, 2) comment '满减~满',
    val_minus       decimal(8, 2) comment '满减~减',

    overlay         tinyint default 0 comment '叠加限制[0-限制｜1-叠加]',
    overlay_level   tinyint default 0 comment '#同类别券叠加适用',

    validity        int comment '有效期（单位天）',
    redeem_validity int comment '兑换有效期（单位天）',
    redeem_start    datetime(3) comment '兑换开放时间',
    redeem_stop     datetime(3) comment '兑换停止时间',
    effect_at       datetime(3) comment '生效时间',
    expire_at       datetime(3) comment '过期时间',

    operator        varchar(64) not null comment '操作人',
    created_at      datetime(3) not null,
    updated_at      datetime(3),

    comment         varchar(255)
) engine = InnoDB comment '券基础信息';
create unique index uidx_candy_coupon_id on candy_coupon (coupon_id);


# 券适用规则
drop table if exists candy_coupon_rule;
create table candy_coupon_rule
(
    mid       bigint unsigned auto_increment primary key,
    crule_id  varchar(64) not null,
    coupon_id varchar(64) not null,
    use_scope smallint comment '适用范围[100-全场｜90-演出｜80-商品]',
    busi_name varchar(64) comment '适用名称',
    busi_id   varchar(64) comment '适用ID',
    state     tinyint comment '0-INIT,1-NORMAL,2-INVALID',
    comment   varchar(255)
) engine = InnoDB comment '券适用规则';
create unique index uidx_candy_coupon_rule_id on candy_coupon_rule (crule_id);


# 券码信息
drop table if exists candy_coupon_code;
create table candy_coupon_code
(
    mid           bigint unsigned auto_increment primary key,
    ccode         varchar(64) not null comment '券码',
    coupon_id     varchar(64) not null comment '~candy_coupon.coupon_id',
    state         tinyint comment '状态[0-可用|1-已用|2-无效|3-过期｜4-失效｜5-退回]',

    ucoupon_id    varchar(64) comment '~candy_user_coupon.ucoupon_id',
    redeem_uid    varchar(64) comment '兑换用户UID',
    redeem_mobile varchar(64) comment '兑换用户手机号',
    redeem_at     datetime(3) comment '兑换时间',

    redeem_start  datetime(3) comment '兑换开放时间',
    redeem_stop   datetime(3) comment '兑换停止时间',

    operator      varchar(64) comment '操作人',
    created_at    datetime(3) not null,
    updated_at    datetime(3),

    comment       varchar(255)
) engine = InnoDB comment '券码信息';
create unique index uidx_candy_coupon_code_id on candy_coupon_code (ccode, coupon_id);


# 用户券信息
drop table if exists candy_user_coupon;
create table candy_user_coupon
(
    mid        bigint unsigned auto_increment primary key,
    ucoupon_id varchar(64) not null,
    mcoupon_id varchar(64) comment '~candy_mgt_coupon.mcoupon_id',
    uid        varchar(64) not null comment '~adam_user.uid',
    coupon_id  varchar(64) not null comment '~candy_coupon.coupon_id',
    state      tinyint comment '用户券状态[1-可用|2-无效｜3-已过期｜5-已使用]',

    ccode      varchar(64) comment '券码~candy_coupon_code.ccode',
    bind_at    datetime(3) comment '激活时间',
    dued_at    datetime(3) comment '到期时间',
    used_at    datetime(3) comment '使用时间',
    used_for   varchar(255) comment '用于记录购买的内容',

    operator      varchar(64) comment '操作人',
    created_at    datetime(3) not null,
    updated_at    datetime(3),

    comment    varchar(255)
) engine = InnoDB comment '用户券信息';
create unique index uidx_candy_user_coupon_id on candy_user_coupon (ucoupon_id);
create index idx_candy_ucoupon_uid_state on candy_user_coupon (uid, state);


# 公有券信息
drop table if exists candy_common_coupon;
create table candy_common_coupon
(
    mid        bigint unsigned auto_increment primary key,
    ccoupon_id varchar(64) not null,
    mcoupon_id varchar(64) not null comment '~candy_mgt_coupon.mcoupon_id',
    coupon_id  varchar(64) not null comment '~candy_coupon.coupon_id',
    state      tinyint comment '公有券状态[1-可用|2-无效｜3-已过期]',
    ranged     tinyint comment '公有券范围(~candy_mgt_coupon.event_type)',

    operator    varchar(64) not null comment '操作人',
    created_at  datetime(3) not null,
    updated_at  datetime(3),
    comment    varchar(255)
) engine = InnoDB comment '公有券信息';
-- >>------------------------------------------------------------------------------------|20211125会员券发放调整
# 专享标识[0-常规｜1-会员专属]
# <p>
# 变更前：`candy_coupon.exclusive`用于根据会员有效期设置券有效期及标识用户端券分类（普通券、会员券等）；
# 变更后：
# 原`candy_coupon.exclusive`废除
# 实际发放逻辑中，原标识"是否根据会员有效期设置券有效期"的`candy_coupon.exclusive`字段改为`candy_mgt_coupon.exclusively`
# 在`candy_user_coupon`及`candy_common_coupon`中添加`exclusive`字段，发放时直接由`candy_mgt_coupon.exclusively`赋值该属性
alter table candy_mgt_coupon add exclusively smallint default 0 null comment '专享标识[0-常规｜1-会员专属]' after event_at;
alter table candy_user_coupon add exclusive smallint null comment '专享标识[0-常规｜1-会员礼包]' after coupon_id;
alter table candy_common_coupon add exclusive smallint null comment '专享标识[0-常规｜1-会员礼包]' after coupon_id;

-- >>------------------------------------------------------------------------------------|20211208券发放任务表+区分后台人为创建与系统创建标识字段
alter table candy_mgt_coupon add event_driven tinyint default 0 null comment '任务驱动[0-系统自动,1-人为创建]' after event_at;
-- >>------------------------------------------------------------------------------------|20220719券关联巡演
drop table if exists candy_coupon_relate;
create table candy_coupon_relate
(
    mid        bigint unsigned auto_increment primary key,
    coupon_id  varchar(64) not null comment '~candy_coupon.coupon_id',
    busi_id    varchar(64) comment '适用ID',
    scope      smallint comment '适用范围[1-巡演]',

    state      tinyint comment '[0-INIT|1-NORMAL|2-INVALID]',
    operator   varchar(64) not null comment '操作人',
    created_at datetime(3) not null,
    updated_at datetime(3),

    comment    text
) engine InnoDB comment '券适用关联配置';
create index idx_ccr_coupon_id on candy_coupon_relate (coupon_id);
-- >>------------------------------------------------------------------------------------
-- >>------------------------------------------------------------------------------------
-- >>------------------------------------------------------------------------------------