# create database if not exists ln_scene character set utf8mb4 collate utf8mb4_unicode_ci;
# use ln_scene;
# -- >>------------------------------------------------------------------------------------
drop table if exists adam_third_party;
create table adam_third_party
(
    mid        bigint unsigned auto_increment primary key,
    uid        varchar(64) default '',
    open_id    varchar(64) comment '第三方ID（NIA为56位标准Base64）',
    avatar     varchar(255) comment '第三方头像',
    nickname   varchar(100) comment '第三方昵称',
    platform   varchar(20) comment '平台名称',
    state      tinyint comment '0-INIT,1-NORMAL,2-INVALID',
    created_at datetime not null,
    updated_at datetime,
    comment    text
) engine = InnoDB comment '用户第三方账号信息';

create index idx_athird_party_uid_platform on adam_third_party (uid, platform);
create index idx_athird_party_state on adam_third_party (state);
create index idx_athird_party_open_id on adam_third_party (open_id);
# MDB.idx:uid,open_id,platform,state
-- >>------------------------------------------------------------------------------------
# drop table if exists adam_device_token;
# create table adam_device_token
# (
#     mid        int unsigned auto_increment primary key,
#     uid        varchar(64)  not null,
#     android    varchar(255) null,
#     ios        varchar(255) null,
#     ipad       varchar(255) null,
#     is_last    tinyint,
#     created_at datetime     not null,
#     updated_at datetime,
#     comment    text
# ) engine = InnoDB comment 'TOKEN';
#
# create index idx_adevice_token_uid on adam_device_token (uid);
-- >>------------------------------------------------------------------------------------
drop table if exists adam_user;
create table adam_user
(
    mid         bigint unsigned auto_increment primary key,
    uid         varchar(64) not null,
    mobile      varchar(30),
    passwd      varchar(64),
    pay_code    varchar(64),
    state       tinyint default 0 comment '1-NORMAL,2-INVALID',
    is_complete tinyint default 0 comment '完善信息进度:0-未完善,1-已完善',
    created_at  datetime(3) not null,
    updated_at  datetime(3),
    closed_at   datetime(3),
    comment     text
) ENGINE = InnoDB comment '用户';

create unique index uidx_auser_uid on adam_user (uid);
create index idx_auser_mobile on adam_user (mobile);
# MDB.idx:uid,mobile,state
-- >>------------------------------------------------------------------------------------
drop table if exists adam_user_info;
create table adam_user_info
(
    mid              bigint unsigned auto_increment primary key,
    uid              varchar(64) not null,
    nickname         varchar(100) comment '昵称',
    sex              varchar(60) comment '性别',
    birthday         date comment '生日YYYY-MM-DD',
    area             varchar(100) comment '常住地/区域',
    signature        varchar(255) comment '个性签名',
    avatar           varchar(255) comment '头像',
    background       varchar(255) comment '背景图',
    qr_code          varchar(255) comment '身份二维码',
    qr_pic           varchar(255) comment '身份二维码',
    tag_me           json comment '音乐标签',
    rong_cloud_token varchar(255),
    rong_cloud_tag   tinyint,
    comment          text
) engine = InnoDB comment '用户信息';

create unique index idx_auser_info_uid on adam_user_info (uid);
-- >>------------------------------------------------------------------------------------
drop table if exists adam_real_name;
create table adam_real_name
(
    mid          bigint unsigned auto_increment primary key,
    real_name_id varchar(64) not null,
    uid          varchar(64) not null,
    type         tinyint     not null comment '1-大陆身份证,2-港澳通行证,3-台胞证,4-护照,5-军官证',
    name         varchar(60) not null comment '真实姓名',
    id_card      varchar(30) not null comment '证件号码',
    state        tinyint     not null comment '0-INIT,1-NORMAL,2-INVALID',
    created_at   datetime(3) not null,
    updated_at   datetime(3),
    comment      text
) engine = InnoDB comment '实名信息';

create unique index idx_areal_name_id on adam_real_name (real_name_id);
create index idx_areal_name_id_card_type on adam_real_name (id_card, type);
create index idx_areal_name_uid on adam_real_name (uid);
create index idx_areal_name_state on adam_real_name (state);
# MDB.idx:uid,type,state
-- >>------------------------------------------------------------------------------------
drop table if exists adam_enters;
create table adam_enters
(
    mid        bigint unsigned auto_increment primary key,
    enters_id  varchar(64) not null,
    uid        varchar(64) not null comment '用户id',
    type       tinyint     not null default '1' comment '1-大陆身份证,2-港澳通行证,3-台胞证,4-护照,5-军官证',
    name       varchar(60) not null default '' comment '入场人名称',
    mobile     varchar(20) not null default '' comment '入场人手机号',
    id_card    varchar(30) not null default '' comment '入场人身份号码，或护照号码',
    is_default boolean     not null default false comment '是否为默认:0-否,1-是，第一个入默认1',
    state      tinyint     not null comment '状态:0-INIT,1-NORMAL,2-INVALID',
    created_at datetime(3) not null,
    updated_at datetime(3),
    deleted_at datetime(3),
    comment    text
) engine = InnoDB comment ='入场人';

create unique index uidx_aenters_id on adam_enters (enters_id);
create index idx_aenters_uid_state on adam_enters (uid, state);
# MDB.idx:uid,state
-- >>------------------------------------------------------------------------------------
drop table if exists adam_addresses;
create table adam_addresses
(
    mid          bigint unsigned auto_increment not null primary key,
    addresses_id varchar(64)                    not null,
    uid          varchar(64)                    not null,
    name         varchar(120)                   not null comment '姓名',
    phone        varchar(30)                    not null comment '手机号',
    province     varchar(60)                    not null comment '省份',
    city         varchar(60)                    not null comment '城市',
    county       varchar(60)                    not null comment '区县',
    address      text                           not null comment '详细地址',
    is_default   boolean                        not null default false comment '是否为默认:0-否,1-是,第一个默认1',
    state        tinyint                        not null comment '状态:0-INIT,1-NORMAL,2-INVALID',
    created_at   datetime(3)                    not null,
    updated_at   datetime(3),
    deleted_at   datetime(3),
    comment      text
) engine = InnoDB comment '收货地址';

create unique index uidx_aaddresses_id on adam_addresses (addresses_id);
create index idx_aaddresses_uid_state on adam_addresses (uid, state);
# MDB.idx:uid,state
-- >>------------------------------------------------------------------------------------
# drop table if exists adam_feedback;
# create table adam_feedback
# (
#     mid           int unsigned auto_increment                                  not null primary key,
#     uid           varchar(64)                                                  not null,
#     type          enum ('complaints', 'bug', 'order', 'video', 'other')        not null comment '反馈类型',
#     comment       varchar(255)                                                 not null comment '反馈内容',
#     reply_comment varchar(255)                                                 null comment '回复内容',
#     source        varchar(255)                                                 not null comment '来源终端',
#     version       varchar(255)                                                 not null comment '终端版本',
#     model         varchar(255)                              default ''         not null comment '设备',
#     status        enum ('notreply', 'replied', 'processed') default 'notreply' not null comment '状态',
#     mobile        varchar(50)                                                  not null comment '手机号',
#     admin_id      int                                       default 0          not null comment '处理人',
#     admin_name    varchar(255)                              default ''         not null comment '处理人姓名',
#     admin_time    datetime(3)                                                     null comment '处理时间',
#     created_at    datetime(3)                                                     not null,
#     updated_at    datetime(3)
# ) engine = InnoDB comment '反馈';
-- >>------------------------------------------------------------------------------------
drop table if exists adam_collection;
create table adam_collection
(
    mid        bigint unsigned auto_increment primary key,
    uid        varchar(64)       not null,
    content_id varchar(64)       not null,
    type       varchar(30)       not null comment '收藏类型:VIDEO,TICKET,SITE,ARTIST,BAND,BRAND,STYLE,GOODS,MUSIC,SONG',
    state      tinyint default 1 not null comment '1-正常,2-删除',
    created_at datetime(3)       not null,
    updated_at datetime(3),
    comment    text
) engine = InnoDB comment '收藏';

create index idx_acollection_uid on adam_collection (uid);
create index idx_acollection_content_id on adam_collection (content_id);
create index idx_acollection_state on adam_collection (state);
# MDB.idx:uid,state,content_id
-- >>------------------------------------------------------------------------------------
drop table if exists adam_disposed;
create table adam_disposed
(
    mid        bigint unsigned auto_increment not null primary key,
    uid        varchar(64)                    not null,
    content_id int unsigned                   not null,
    type       varchar(30)                    not null comment '想去类型:TICKET',
    state      tinyint default 1              not null comment '1-正常,2-删除',
    created_at datetime(3)                    not null,
    updated_at datetime(3),
    comment    text
) engine = InnoDB comment '想去';

create index idx_adisposed_uid on adam_disposed (uid);
create index idx_adisposed_content_id on adam_disposed (content_id);
create index idx_adisposed_state on adam_disposed (state);
# MDB.idx:uid,state,content_id
-- >>------------------------------------------------------------------------------------
drop table if exists adam_member;
create table adam_member
(
    mid               bigint unsigned auto_increment primary key,
    member_id         varchar(64) comment '会员id',
    name              varchar(80),
    title             varchar(100),
    sub_title         varchar(255) NOT NULL DEFAULT '' COMMENT '副标题',
    icon              varchar(255),
    avatar            varchar(255),
    start_no          varchar(20) comment '起始会员号',
    type              tinyint comment '会员类型',
    interests_detail  text comment '权益详情',
    #     gift_pack_id     varchar(64) comment '礼包ID',
    notes             text         NOT NULL COMMENT '注意事项',
    created_at        datetime(3),
    updated_at        datetime(3),
    is_notice         tinyint comment '是否展示须知',
    notice_info       text comment '须知内容',
    notice_title      varchar(255),
    need_question     int                   default 0 comment '是否需要答题 0不需要 1需要',
    display_agreement int          NOT NULL DEFAULT '0' COMMENT '是否打开0否1是',
    state             int          NOT NULL COMMENT '状态:1-NORMAL,2-INVALID',
    limitation        int                   default 0 comment '限购数量，0不限购',
    comment           text
) engine = InnoDB comment '会员卡信息';

create unique index uidx_amember_id on adam_member (member_id);
# MDB.idx:member_id,state
-- >>------------------------------------------------------------------------------------
drop table if exists adam_member_price;
create table adam_member_price
(
    mid             bigint unsigned auto_increment primary key,
    member_price_id varchar(64) comment '会员价格id',
    member_id       varchar(64)   NOT NULL comment '会员种类ID',
    state           tinyint       NOT NULL DEFAULT '0' COMMENT '0不可用、1上线中、2已下线',
    name            varchar(80)   NOT NULL DEFAULT '' COMMENT '会员包名称',
    price           decimal(8, 2) NOT NULL DEFAULT '0.00' COMMENT '会员包价格',
    price_fixed     decimal(8, 2) comment '折扣价',
    days            int           NOT NULL DEFAULT '0' COMMENT '会员包天数',
    detail          text          NOT NULL COMMENT '会员包须知',
    created_at      datetime(3),
    updated_at      datetime(3),
    comment         text
) engine = InnoDB comment '会员卡价格';

create unique index uidx_amember_price_id on adam_member_price (member_price_id);
create index idx_amember_price_member_id on adam_member_price (member_id);
# MDB.idx:member_price_id,member_id,state
-- >>------------------------------------------------------------------------------------
drop table if exists adam_member_order;
create table adam_member_order
(
    mid             bigint unsigned auto_increment primary key,
    order_no        varchar(64),
    uid             varchar(64),
    mode            tinyint comment '购买方式:0-购买会员,1-购买会员码',
    price           decimal(8, 2) comment '原价',
    price_paid      decimal(8, 2) comment '实际支付',
    member_name     varchar(80),
    member_id       varchar(64) comment '会员id',
    member_price_id varchar(64) comment '会员价格id',
    days            int         default 0 comment '购买天数',
    state           tinyint comment '订单状态',
    member_no       varchar(20) comment '会员号',
    birthday        varchar(20) default null comment '用户生日[yyyy-MM-dd]',
    device_from     varchar(20) comment '支付终端:app,wap,js,applet',
    pay_type        varchar(20) comment '支付方式:alipay,wepay,vipcode,giftcode',
    pay_no          varchar(64) comment '支付订单',
    payment_at      datetime(3) default null comment '支付时间',
    created_at      datetime(3),
    updated_at      datetime(3),
    client_ip       varchar(50),
    source          varchar(30),
    version         varchar(30),
    comment         text
) engine = InnoDB comment '用户会员订单';

create unique index uidx_amember_order_id on adam_member_order (order_no);
# MDB.idx:order_no,uid,state,pay_no,member_id,member_price_id
-- >>------------------------------------------------------------------------------------
drop table if exists adam_user_member;
create table adam_user_member
(
    mid        bigint unsigned auto_increment primary key,
    uid        varchar(64) not null,
    member_id  varchar(64) comment '会员类型id',
    member_no  varchar(20) not null,
    state      tinyint comment '1-NORMAL,2-INVALID',
    expiry_at  datetime(3) comment '到期日期',
    created_at datetime(3),
    updated_at datetime(3),
    comment    text
) engine = InnoDB comment '用户会员信息';

create index idx_auser_member_uid on adam_user_member (uid);
create index idx_auser_member_no on adam_user_member (member_no);
# MDB.idx:uid,state,member_no
-- >>------------------------------------------------------------------------------------
drop table if exists adam_member_code;
create table adam_member_code
(
    mid             bigint unsigned auto_increment primary key,
    code            varchar(30) comment '会员码',
    type            int          default 0 comment '类型:1-购买,2-赠送(后台生成)',
    member_id       varchar(64) comment '会员ID',
    member_price_id varchar(64) comment '会员价格ID',
    member_no       varchar(20) comment '预留会员号',
    state           tinyint comment '状态[0-可用|1-已用|2-无效|3-过期｜4-失效｜5-退回]',
    created_at      datetime(3),
    updated_at      datetime(3),
    buy_order_no    varchar(255) default null comment '购买会员码订单号',
    buy_uid         varchar(255) default null comment '购买会员码用户id',
    buy_at          datetime(3)  default null comment '购买时间',
    use_order_no    varchar(64)  default null comment '消费订单号',
    use_uid         varchar(64)  default null comment '消费用户ID',
    use_at          datetime(3)  default null,
    comment         text
) engine = InnoDB comment '会员兑换码';

# create unique index uidx_amember_code_id on adam_member_code (code);
# MDB.idx:code,state
-- >>------------------------------------------------------------------------------------ |20210811用于会员改版前支持用户购买会员选礼包收货地址
drop table if exists adam_member_order_ext;
create table adam_member_order_ext
(
    mid              bigint unsigned auto_increment primary key,
    order_no         varchar(64) not null,
    express_status   tinyint comment '快递状态:0-待揽收|1-已揽收|2-在途中|3-已签收｜4-已拒收',
    express_receiver varchar(120) comment '收件人',
    express_phone    varchar(30) comment '收件人电话',
    express_address  text comment '收件人地址',
    comment          text
) engine = InnoDB comment '用户会员订单扩展信息表';

create unique index uidx_amember_order_ext_id on adam_member_order_ext (order_no);
-- >>------------------------------------------------------------------------------------ |20210817会员与券改版
# 添加`双倍积分`开关
alter table adam_member add integral_rate decimal(8, 2) default 0 null comment '积分倍率X100' after state;
# 添加`是否开售`开关，新增开售开关字段
alter table adam_member add onsale tinyint default 0 null comment '是否开售:1-开售｜2-停售' after integral_rate;
# 添加限购逻辑，新增限购开始、结束时间字段
alter table adam_member add limitb_at datetime(3) null comment '限购开始时间' after limitation;
alter table adam_member add limite_at datetime(3) null comment '限购结束时间' after limitb_at;


# 添加会员价格字段
alter table adam_member_price modify price_fixed decimal(8, 2) null comment '购买价格';
alter table adam_member_price add price_special decimal(8, 2) null comment '特价:首次、首年优惠价' after price_fixed;

# 多档会员：权益可绑价格包（详见 db_adam_member_price_multi_package.sql）
# alter table adam_member_rights add member_price_id varchar(64) null comment '绑定价格包ID，空表示全档回退共用' after member_id;

# 会员码表调整
alter table adam_member_code modify state tinyint null comment '状态[0-可用|1-已用|2-无效]';
create index idx_amember_code_id on adam_member_code (code);
alter table adam_member_code add validity int null comment '有效期（单位天）' after state;
alter table adam_member_code add effect_at datetime(3) null comment '生效时间' after validity;
alter table adam_member_code add expire_at datetime(3) null comment '过期时间' after effect_at;
alter table adam_member_code add operator varchar(64) null comment '管理操作人' after expire_at;

# 订单
alter table adam_member_order modify state tinyint null comment '订单状态:0-未支付,1-已支付,2-已关闭,3-超时付,4-退款中,5-已退款';
alter table adam_member_order modify pay_no varchar(64) null comment '支付中心支付CODE';
alter table adam_member_order add payment_id varchar(64) null comment '支付中心三方支付ID' after pay_no;
alter table adam_member_order add area varchar(100) null comment '地区' after birthday;


# 会员退款
drop table if exists adam_member_refund;
create table adam_member_refund
(
    mid           bigint unsigned auto_increment primary key,
    refund_no     varchar(64) comment '退款请求编号',
    order_no      varchar(64) not null comment '对应支付中心order_code',

    refund_price  decimal(8, 2) comment '退款金额',
    refund_reason varchar(200) comment '退款描述',
    renewable     tinyint comment '继续购买[1-可以｜2-不可]',
    refund_state  tinyint comment '退款状态[0-发起退款｜1-已退款｜2-关闭｜3-退款失败｜9-退款中]',
#     payment_type  varchar(30) comment '支付渠道（即UPPER(CONCAT(device_from,pay_type))）',

    repay_no      varchar(64) comment '支付中心退款编号',
    repay_reason  varchar(200) comment '支付中心退款描述',
    refund_at     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_adam_member_refund_id on adam_member_refund (refund_no);


# 添加权益管理（关联会员券）
drop table if exists adam_member_rights;
create table adam_member_rights
(
    mid        bigint unsigned auto_increment primary key,
    mrights_id varchar(64) not null,
    member_id  varchar(64) comment '会员类型id',
    state      tinyint comment '权益状态[0-INIT|1-NORMAL|2-INVALID]',
    seq_no     smallint comment '排列序号',
    title      varchar(50) not null comment '权益标题',
    sub_title  varchar(200) comment '副标题',
    label      varchar(50) comment '标注',
    cover      varchar(255) comment '图片标识',
    detail     text 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_adam_member_rights_id on adam_member_rights (mrights_id);
-- >>------------------------------------------------------------------------------------ |20210918社交相关
# 用户业务服务账号表
drop table if exists adam_user_busi_acct;
create table adam_user_busi_acct
(
    mid        bigint unsigned auto_increment primary key,
    uid        varchar(64) not null,
    busi       varchar(20) comment '业务服务平台',
    uuid       varchar(64) comment '业务服务ID',
    work       varchar(64) comment '业务服务模块',
    ppwd       varchar(64) comment '业务服务密码',
    state      tinyint comment '1-NORMAL,2-INVALID',
    created_at datetime(3) not null,
    updated_at datetime(3),
    comment    text
) engine = InnoDB comment '用户业务服务账号表';
create index idx_adam_user_busi_acct_uid on adam_user_busi_acct (uid);
-- >>------------------------------------------------------------------------------------ |20210924会员码管理
drop table if exists adam_member_code_batch;
create table adam_member_code_batch
(
    mid             bigint unsigned auto_increment primary key,
    batch_no        varchar(64) not null comment '会员码批次号',
    member_id       varchar(64) not null comment '~`adam_member.member_id`',
    member_price_id varchar(64) not null comment '~`adam_member_price.member_price_id`',
    member_name     varchar(80) comment '会员卡名称~`adam_member.name`',
    member_combo    varchar(80) comment '套餐包名称',
    gen_num         int comment '生成数量',
    use_num         int comment '使用数量',
    detail          varchar(255) comment '描述',
    validity        int comment '有效期（单位天）',
    effect_at       datetime(3) null comment '生效时间',
    expire_at       datetime(3) null comment '失效时间',

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

    comment         varchar(255)
) engine = InnoDB comment '会员码批次记录';
create index idx_adam_member_code_batch_no on adam_member_code_batch (batch_no);

alter table adam_member_code add batch_no varchar(30) null comment '~`adam_member_code_batch.batch_no`' after type;
-- >>------------------------------------------------------------------------------------ |20211011手机号地域记录
drop table if exists adam_user_mobile_locate;
create table adam_user_mobile_locate
(
    mid           bigint unsigned auto_increment primary key,
    uid           varchar(64) not null,
    mobile        varchar(30) not null comment '当前手机号',
    name          varchar(50) comment '姓名',
    id_card       varchar(50) comment '证件号',
    state         tinyint comment '0-INIT,1-NORMAL,2-INVALID',
    province      varchar(50) comment '省份',
    city          varchar(50) comment '城市',
    corp          varchar(50) comment '运营商',
    area_code     varchar(50) comment '区号',
    post_code     varchar(50) comment '邮编',
    regist_mobile varchar(50) comment '注册手机号',
    regist_addr   varchar(50) comment '注册IP',
    regist_source varchar(50) comment '注册来源',
    regist_at     datetime(3),
    latest_addr   varchar(50) comment '最近登录IP',
    latest_source varchar(50) comment '最近登录来源',
    latest_at     datetime(3),
    updated_at    datetime(3),
    comment       text
) engine = InnoDB comment '用户手机号地域信息';
create unique index uidx_auser_mobile_locate_id on adam_user_mobile_locate (uid);
-- >>------------------------------------------------------------------------------------ |20211105用户常驻地统一
alter table adam_user_mobile_locate add county varchar(50) null comment '区县' after city;
-- >>------------------------------------------------------------------------------------ |20211130收货地址扩展
# 老数据`address`长度>=255的手工处理
alter table adam_addresses add zipcode varchar(10) null comment '邮编' after phone;
alter table adam_addresses add province_id varchar(20) null comment '省份行政编码' after province;
alter table adam_addresses add city_id varchar(20) null comment '城市行政编码' after city;
alter table adam_addresses add county_id varchar(20) null comment '区县行政编码' after county;
alter table adam_addresses add town varchar(50) null comment '街道/镇' after county_id;
alter table adam_addresses add town_id varchar(20) null comment '街道行政编码' after town;
alter table adam_addresses modify address varchar(255) not null comment '详细地址';
alter table adam_addresses add full_address varchar(500) null comment '完整地址' after address;
-- >>------------------------------------------------------------------------------------ |20220323三要素实名
alter table adam_real_name add node tinyint default 2 not null comment '认证节点[2-二要素|3-三要素]' after type;
-- >>------------------------------------------------------------------------------------
-- >>------------------------------------------------------------------------------------
-- >>------------------------------------------------------------------------------------
-- >>------------------------------------------------------------------------------------
-- >>------------------------------------------------------------------------------------