playlet/sql/central/central.sql

create table admin_auth
(
    id          int auto_increment
        primary key,
    env         varchar(255) default ''        not null,
    sys         varchar(64)  default 'central' not null,
    parent_id   int                            null,
    type        int          default 0         not null comment '0:权限 1:路由菜单 2:按钮',
    hidden      int          default 0         not null comment '0:显示 1:隐藏',
    level       int          default 0         not null comment '层级，从1开始',
    sort        int          default 0         not null comment '排序，从小到大',
    name        varchar(255)                   null comment '权限名称/路由组件名称/按钮编码',
    title       varchar(255)                   null comment '显示的标题',
    icon        varchar(255)                   null comment '显示的图标',
    path        varchar(1000)                  null comment '链接的相对路径',
    redirect    varchar(1000)                  null comment '链接的绝对路径',
    component   varchar(1000)                  null comment '组件',
    keep_alive  int          default 0         not null,
    auths       varchar(1000)                  null comment '拥有的权限编码，多个用逗号分隔',
    remark      varchar(255)                   null comment '备注',
    create_time datetime                       not null,
    update_time datetime                       not null,
    status      int          default 0         not null
);

create table admin_data_auth
(
    id              int auto_increment
        primary key,
    env             varchar(255)  not null,
    sort            int default 0 not null,
    func_name       varchar(64)   null,
    func_code       varchar(64)   not null,
    page_range_desc varchar(255)  null,
    remark          varchar(255)  null comment '备注',
    create_time     datetime      not null,
    update_time     datetime      not null,
    status          int default 0 not null
);

create index env_index
    on admin_data_auth (env);

create table admin_env
(
    id          int auto_increment
        primary key,
    env         varchar(255)  not null,
    name        varchar(255)  not null,
    create_time datetime      not null,
    update_time datetime      not null,
    status      int default 0 not null
);

create table admin_organ
(
    id          int auto_increment
        primary key,
    env         varchar(255) default '' not null comment '环境',
    name        varchar(255)            not null comment '部门名称',
    parent_id   int                     null comment '父节点',
    level       int          default 0  not null comment '层级，从1开始',
    sort        int          default 0  not null comment '排序，从小到大',
    hidden      int          default 0  not null comment '0:显示 1:隐藏',
    remark      varchar(255)            null,
    create_time datetime                not null,
    update_time datetime                not null,
    status      int          default 0  not null
);

create index env_index
    on admin_organ (env);

create table admin_role
(
    id          int auto_increment
        primary key,
    env         varchar(255) default '' not null,
    name        varchar(255)            not null,
    remark      varchar(255)            null,
    create_time datetime                not null,
    update_time datetime                not null,
    status      int          default 0  not null
);

create table admin_role_ref_auth
(
    id      int auto_increment
        primary key,
    env     varchar(255) default '' not null,
    role_id int                     not null,
    auth_id int                     not null
);

create table admin_role_ref_data_auth
(
    id        int auto_increment
        primary key,
    role_id   int           not null,
    func_code varchar(64)   not null,
    auth_type int default 1 not null,
    constraint role_id_func_code_index
        unique (role_id, func_code)
);

create index role_id_index
    on admin_role_ref_data_auth (role_id);

create table admin_user
(
    id          bigint auto_increment
        primary key,
    env         varchar(255) default '' not null,
    user_type   int          default 0  not null comment '0:普通用户 1:超级管理员',
    account     varchar(255)            not null,
    password    varchar(255)            null,
    salt        varchar(255)            null,
    nickname    varchar(255)            null,
    organ_id    int                     null,
    remark      varchar(255)            null,
    create_time datetime                not null,
    update_time datetime                not null,
    status      int          default 0  not null,
    constraint account_unique_index
        unique (account)
);

create table admin_user_ref_app
(
    id      int auto_increment
        primary key,
    user_id bigint       not null,
    app_id  int          not null,
    env     varchar(255) not null
);

create table admin_user_ref_role
(
    id      int auto_increment
        primary key,
    user_id bigint       not null,
    role_id int          not null,
    env     varchar(255) not null
);

create table agreement_document
(
    id             int auto_increment
        primary key,
    name           varchar(255)  null,
    product_name   varchar(255)  not null,
    app_platform   int           not null,
    subject_name   varchar(255)  not null,
    agreement_type varchar(255)  not null,
    content        longtext      not null,
    content_md5    varchar(64)   not null,
    document_key   varchar(64)   not null,
    remark         varchar(255)  null,
    create_time    datetime      not null,
    update_time    datetime      not null,
    status         int default 0 not null,
    constraint document_key_uindex
        unique (document_key)
);

create table app
(
    id            int auto_increment
        primary key,
    env           varchar(255) default '' not null,
    name          varchar(255)            not null,
    app_platform  int                     not null,
    package_name  varchar(255)            not null,
    app_key       varchar(64)             null,
    app_secret    varchar(512)            null,
    verify_status int          default 0  not null,
    product_code  varchar(255)            null,
    project_code  varchar(255)            null,
    remark        varchar(255)            null,
    create_time   datetime                not null,
    update_time   datetime                not null,
    status        int          default 0  not null,
    constraint env_package_platform_unique
        unique (env, package_name, app_platform)
);

create table app_package
(
    id           int auto_increment
        primary key,
    name         varchar(255)  not null,
    package_name varchar(255)  not null,
    remark       varchar(255)  null,
    create_time  datetime      not null,
    update_time  datetime      not null,
    status       int default 0 not null,
    constraint project_code_unique
        unique (package_name)
);

create table auth_alipay_config
(
    id                      int auto_increment
        primary key,
    package_name            varchar(255)  not null,
    app_platform            int           not null,
    app_id                  varchar(255)  not null comment '支付宝应用id',
    app_private_key         varchar(5000) not null comment '支付宝应用私钥',
    app_public_key_path     varchar(1000) not null comment '支付宝应用公钥文件路径',
    alipay_root_cert_path   varchar(1000) not null comment '支付宝根证书文件路径',
    alipay_public_cert_path varchar(1000) not null comment '支付宝公钥证书文件路径',
    status                  int default 0 not null
)
    collate = utf8mb4_unicode_ci;

create index package_platform_index
    on auth_alipay_config (package_name, app_platform);

create table auth_apple_config
(
    id               int auto_increment
        primary key,
    package_name     varchar(255)  not null,
    app_platform     int           not null,
    team_id          varchar(255)  not null,
    client_id        varchar(255)  not null,
    kid              varchar(255)  not null,
    private_key_path varchar(255)  not null,
    status           int default 0 not null
);

create index package_platform_index
    on auth_apple_config (package_name, app_platform);

create table auth_dy_config
(
    id           int auto_increment
        primary key,
    package_name varchar(255)  not null,
    app_platform int           not null,
    appid        varchar(255)  not null,
    secret       varchar(255)  not null,
    status       int default 0 not null
);

create index idx_package_platform
    on auth_dy_config (package_name, app_platform);

create table auth_netease_config
(
    id           int auto_increment
        primary key,
    package_name varchar(255)  not null,
    app_platform int           not null,
    secret_id    varchar(255)  not null comment '产品秘钥 id',
    business_id  varchar(255)  not null comment '业务id',
    secret_key   varchar(255)  not null comment '产品私钥',
    version      varchar(255)  not null comment '接口版本号',
    status       int default 0 not null
)
    collate = utf8mb4_unicode_ci;

create index package_platform_index
    on auth_netease_config (package_name, app_platform);

create table auth_wechat_config
(
    id           int auto_increment
        primary key,
    package_name varchar(255)  not null,
    app_platform int           not null,
    appid        varchar(255)  not null comment '微信应用唯一标识',
    secret       varchar(255)  not null comment '微信应用密钥',
    token        varchar(255)  null,
    aes_key      varchar(255)  null,
    status       int default 0 not null
)
    collate = utf8mb4_unicode_ci;

create index package_platform_index
    on auth_wechat_config (package_name, app_platform);

create table blocked_device
(
    device_id        varchar(64)                      not null comment '设备id'
        primary key,
    package_name     varchar(255)                     null comment '包名',
    oaid             varchar(255) collate utf8mb4_bin null comment '国内 OAID(开放匿名设备标识)',
    aaid             varchar(255) collate utf8mb4_bin null comment '海外 AAID(安卓广告标识)',
    android_id       varchar(255) collate utf8mb4_bin null comment 'android设备标识符',
    idfa             varchar(255)                     null comment 'apple用户设备的唯一标识符',
    imei             varchar(255) collate utf8mb4_bin null comment 'IMEI(国际移动设备身份码)',
    idfv             varchar(255)                     null comment 'apple应用程序开发商的唯一标识符',
    machine_id       varchar(255)                     null comment '机器唯一标识符',
    channel_name     varchar(255)                     null comment '渠道',
    app_version_code varchar(255)                     null comment '版本编号',
    app_version_name varchar(255)                     null comment '版本名称',
    region           varchar(255)                     null comment '设备所在区域',
    loc_lng          double                           null comment '设备坐标经度',
    loc_lat          double                           null comment '设备坐标纬度',
    ip               varchar(255)                     null comment '设备所在ip',
    ip_region        varchar(255)                     null comment '设备所在ip的区域',
    create_time      datetime                         not null comment '创建时间',
    update_time      datetime                         not null comment '更新时间',
    block_level      int default 0                    not null comment '0:未屏蔽 1:已直接屏蔽 2...:间接屏蔽深度'
);

create table blocked_device_update
(
    id               bigint auto_increment
        primary key,
    device_id        varchar(64)                      not null comment '设备id',
    package_name     varchar(255)                     null comment '包名',
    oaid             varchar(255) collate utf8mb4_bin null comment '国内 OAID(开放匿名设备标识)',
    aaid             varchar(255) collate utf8mb4_bin null comment '海外 AAID(安卓广告标识)',
    android_id       varchar(255) collate utf8mb4_bin null comment 'android设备标识符',
    idfa             varchar(255)                     null comment 'apple用户设备的唯一标识符',
    imei             varchar(255) collate utf8mb4_bin null comment 'IMEI(国际移动设备身份码)',
    idfv             varchar(255)                     null comment 'apple应用程序开发商的唯一标识符',
    machine_id       varchar(255)                     null comment '机器唯一标识符',
    channel_name     varchar(255)                     null comment '渠道',
    app_version_code varchar(255)                     null comment '版本编号',
    app_version_name varchar(255)                     null comment '版本名称',
    region           varchar(255)                     null comment '设备所在区域',
    loc_lng          double                           null comment '设备坐标经度',
    loc_lat          double                           null comment '设备坐标纬度',
    ip               varchar(255)                     null comment '设备所在ip',
    ip_region        varchar(255)                     null comment '设备所在ip的区域',
    create_time      datetime                         not null comment '创建时间',
    update_time      datetime                         not null comment '更新时间',
    block_level      int default 0                    not null comment '0:未屏蔽 1:已直接屏蔽 2...:间接屏蔽深度'
);

create table channel
(
    id           int auto_increment
        primary key,
    name         varchar(255)  not null,
    channel_name varchar(255)  not null,
    remark       varchar(255)  null,
    create_time  datetime      not null,
    update_time  datetime      not null,
    status       int default 0 not null,
    constraint channel_name_unique
        unique (channel_name)
);

create table complaint
(
    id           bigint auto_increment
        primary key,
    package_name varchar(255)  null,
    app_platform int           null,
    channel_name varchar(255)  null,
    ssid         varchar(64)   null,
    device_id    varchar(64)   null,
    phone        varchar(255)  null,
    reason       varchar(255)  null,
    content      varchar(1000) null,
    images       varchar(1000) null,
    create_time  datetime      not null,
    update_time  datetime      not null,
    status       int           not null
);

create table conf
(
    id           int auto_increment
        primary key,
    project_code varchar(255)  not null,
    conf_code    varchar(255)  not null,
    name         varchar(255)  not null,
    create_time  datetime      not null,
    update_time  datetime      not null,
    status       int default 0 not null
)
    collate = utf8mb4_unicode_ci;

create table conf_condition
(
    id               int auto_increment
        primary key,
    project_code     varchar(255)             not null,
    conf_code        varchar(255)             not null,
    remark           varchar(2048)            null,
    content          json                     null,
    priority         int          default 10  not null comment '数字越大优先级越高',
    package_name     varchar(255) default '*' not null comment '包名',
    app_platform     varchar(255) default '*' not null comment '应用平台',
    channel_name     varchar(255) default '*' not null comment '渠道',
    app_version_name varchar(255) default '*' not null comment '版本',
    city             varchar(255) default '*' not null comment '城市或地区',
    create_time      datetime                 not null,
    update_time      datetime                 not null,
    status           int          default 0   not null comment '0:启用 1:禁用',
    yml_content      longtext                 null,
    content_type     int                      null comment '0:json 1:yaml',
    md5              varchar(32)              null comment 'md5'
)
    collate = utf8mb4_unicode_ci;

create index project_code_conf_code_index
    on conf_condition (project_code, conf_code);

create index project_code_index
    on conf_condition (project_code);

create table device
(
    id           varchar(64)                      not null comment 'UUID'
        primary key,
    package_name varchar(255) collate utf8mb4_bin not null comment '包名',
    oaid         varchar(255) collate utf8mb4_bin null comment '国内 OAID(开放匿名设备标识)',
    aaid         varchar(255) collate utf8mb4_bin null comment '海外 AAID(安卓广告标识)',
    android_id   varchar(255) collate utf8mb4_bin null comment 'android设备标识符',
    idfa         varchar(255)                     null comment 'apple用户设备的唯一标识符',
    imei         varchar(255) collate utf8mb4_bin null comment 'IMEI(国际移动设备身份码)',
    idfv         varchar(255)                     null comment 'apple应用程序开发商的唯一标识符',
    machine_id   varchar(255)                     null comment '机器唯一标识符',
    reg_time     datetime                         not null comment '注册时间',
    open_time    datetime                         not null comment '打开时间',
    status       int default 0                    not null comment '0:启用 1:禁用'
);

create index aaid_index
    on device (aaid);

create index android_id_index
    on device (android_id);

create index idfa_index
    on device (idfa);

create index idfv_index
    on device (idfv);

create index imei_index
    on device (imei);

create index machine_id_index
    on device (machine_id);

create index oaid_index
    on device (oaid);

create index package_name_index
    on device (package_name);

create table device_block_rule
(
    id                   int auto_increment
        primary key,
    priority             int default 10 not null comment '数字越小优先级越高',
    match_type           varchar(255)   not null comment 'equals,each_equals,any_equals,geo_radius',
    match_property       varchar(255)   not null comment '多值用,分隔',
    match_value          varchar(255)   not null comment '多值用,分隔',
    block_level          int default 0  not null comment '屏蔽等级',
    max_auto_block_level int default 0  not null comment '最高继承屏蔽等级',
    created_by_rule_id   int            null comment '此规则由哪一条规则创建',
    create_time          datetime       not null comment '创建时间',
    update_time          datetime       not null comment '更新时间',
    manual               int default 0  not null comment '0:手动管理 1:自动管理',
    status               int default 0  not null comment '0:启用 1:禁用'
);

create index match_type_property_value_index
    on device_block_rule (match_type, match_property, match_value);

create table device_detail
(
    device_id            varchar(64)                      not null comment '设备id'
        primary key,
    package_name         varchar(255)                     null comment '包名',
    oaid                 varchar(255) collate utf8mb4_bin null comment '国内 OAID(开放匿名设备标识)',
    aaid                 varchar(255) collate utf8mb4_bin null comment '海外 AAID(安卓广告标识)',
    android_id           varchar(255) collate utf8mb4_bin null comment 'android设备标识符',
    idfa                 varchar(255)                     null comment 'apple用户设备的唯一标识符',
    imei                 varchar(255) collate utf8mb4_bin null comment 'IMEI(国际移动设备身份码)',
    idfv                 varchar(255)                     null comment 'apple应用程序开发商的唯一标识符',
    machine_id           varchar(255)                     null comment '机器唯一标识符',
    channel_name         varchar(255)                     null comment '渠道',
    tg_platform          int                              null comment '推广平台',
    present_channel_name varchar(255)                     null comment '当前渠道',
    app_platform         int                              null comment '1:安卓 2:苹果 3:移动H5 4:PCWEB',
    app_version_code     varchar(255)                     null comment '版本编号',
    app_version_name     varchar(255)                     null comment '版本名称',
    os                   varchar(64)                      null,
    brand                varchar(255)                     null comment '设备品牌',
    model                varchar(255)                     null comment '设备型号',
    os_version           varchar(255)                     null comment '设备操作系统版本',
    sim_imei0            varchar(255)                     null comment 'SIM卡0的IMEI',
    sim_imei1            varchar(255)                     null comment 'SIM卡1的IMEI',
    mac                  varchar(255)                     null comment '设备MAC地址',
    wifi_name            varchar(255)                     null comment '设备WIFI名称',
    region               varchar(255)                     null comment '设备所在区域',
    country              varchar(255)                     null comment '设备所在国家',
    city                 varchar(255)                     null comment '设备所在城市',
    loc_lng              double                           null comment '设备坐标经度',
    loc_lat              double                           null comment '设备坐标纬度',
    ip                   varchar(255)                     null comment '设备所在ip',
    ip_region            varchar(255)                     null comment '设备所在ip的区域',
    create_time          datetime                         not null comment '创建时间',
    update_time          datetime                         not null comment '更新时间',
    block_level          int default 0                    not null comment '0:未屏蔽 1:已直接屏蔽 2...:间接屏蔽深度',
    user_agent           varchar(255)                     null,
    company              varchar(64)                      null,
    advertiser_id        varchar(64)                      null,
    gid                  varchar(64)                      null,
    aid                  varchar(64)                      null,
    cid                  varchar(64)                      null,
    attributed_time      datetime                         null
);

create index create_time_index
    on device_detail (create_time desc);

create table device_detail_update
(
    id                   bigint auto_increment
        primary key,
    device_id            varchar(64)                      not null comment '设备id',
    package_name         varchar(255)                     null comment '包名',
    oaid                 varchar(255) collate utf8mb4_bin null comment '国内 OAID(开放匿名设备标识)',
    aaid                 varchar(255) collate utf8mb4_bin null comment '海外 AAID(安卓广告标识)',
    android_id           varchar(255) collate utf8mb4_bin null comment 'android设备标识符',
    idfa                 varchar(255)                     null comment 'apple用户设备的唯一标识符',
    imei                 varchar(255) collate utf8mb4_bin null comment 'IMEI(国际移动设备身份码)',
    idfv                 varchar(255)                     null comment 'apple应用程序开发商的唯一标识符',
    machine_id           varchar(255)                     null comment '机器唯一标识符',
    channel_name         varchar(255)                     null comment '渠道',
    tg_platform          int                              null comment '推广平台',
    present_channel_name varchar(255)                     null comment '当前渠道',
    app_platform         int                              null comment '1:安卓 2:苹果 3:移动H5 4:PCWEB',
    app_version_code     varchar(255)                     null comment '版本编号',
    app_version_name     varchar(255)                     null comment '版本名称',
    os                   varchar(64)                      null,
    brand                varchar(255)                     null comment '设备品牌',
    model                varchar(255)                     null comment '设备型号',
    os_version           varchar(255)                     null comment '设备操作系统版本',
    sim_imei0            varchar(255)                     null comment 'SIM卡0的IMEI',
    sim_imei1            varchar(255)                     null comment 'SIM卡1的IMEI',
    mac                  varchar(255)                     null comment '设备MAC地址',
    wifi_name            varchar(255)                     null comment '设备WIFI名称',
    region               varchar(255)                     null comment '设备所在区域',
    country              varchar(255)                     null comment '设备所在国家',
    city                 varchar(255)                     null comment '设备所在城市',
    loc_lng              double                           null comment '设备坐标经度',
    loc_lat              double                           null comment '设备坐标纬度',
    ip                   varchar(255)                     null comment '设备所在ip',
    ip_region            varchar(255)                     null comment '设备所在ip的区域',
    user_agent           varchar(255)                     null comment '设备所在城市',
    create_time          datetime                         not null comment '创建时间',
    update_time          datetime                         not null comment '更新时间',
    block_level          int default 0                    not null comment '0:未屏蔽 1:已直接屏蔽 2...:间接屏蔽深度'
);

create index device_id_index
    on device_detail_update (device_id);

create table event_meta_info
(
    id           bigint auto_increment
        primary key,
    project_code varchar(255) default '' not null,
    product_id   varchar(255)            null,
    alias        varchar(255)            not null,
    name         varchar(255)            not null,
    event_type   varchar(255)            not null,
    accept       int                     not null,
    description  varchar(255)            null,
    create_time  datetime                not null,
    update_time  datetime                not null,
    status       int          default 0  not null
);

create table event_meta_prop
(
    id           bigint auto_increment
        primary key,
    project_code varchar(255) default '' not null,
    product_id   varchar(255)            null,
    col          varchar(255)            not null,
    alias        varchar(255)            null,
    name         varchar(255)            not null,
    prop_type    varchar(255)            not null,
    value_type   varchar(255)            not null,
    accept       int                     not null,
    description  varchar(255)            null,
    create_time  datetime                not null,
    update_time  datetime                not null,
    status       int          default 0  not null,
    constraint project_col_unique_index
        unique (project_code, col)
);

create table his_conf_condition
(
    id           int auto_increment
        primary key,
    project_code varchar(255) not null,
    conf_code    varchar(255) not null,
    content      json         null,
    create_time  datetime     not null,
    update_time  datetime     not null,
    yml_content  longtext     null,
    content_type int          null comment '0:json 1:yaml',
    source_id    int          null comment 'conf_condition的id',
    md5          varchar(32)  null comment 'md5'
);

create table pay_agreement
(
    id                    bigint auto_increment
        primary key,
    external_agreement_no varchar(128) not null comment '自定义商户签约号',
    agreement_no          varchar(128) null comment '商户签约号',
    period_type           varchar(32)  not null comment '周期类型 DAY:天  MONTH:月',
    period bigint not null comment '周期数',
    execute_time          datetime     not null comment '商家发起首次扣款的时间（非签约时间，而是第一次自动扣款时间）',
    next_execute_time     datetime     null comment '下次扣款时间',
    single_amount         bigint       null comment '单次扣款最大金额',
    total_amount          bigint       null comment '周期内允许扣款的总金额',
    sign_scene            varchar(128) null comment '签约场景',
    item_id               bigint       null comment '商品id',
    item_code             varchar(255) null comment '商品编号',
    item_name             varchar(255) null comment '商品名称',
    item_amount           bigint       null comment '商品价格',
    subscription_group    varchar(255) null comment '订阅组',
    ssid                  varchar(64)  null comment 'ssid',
    device_id             varchar(64)  null comment '设备id',
    phone                 varchar(64)  null comment '电话号码',
    app_platform          int          null comment '应用平台',
    package_name          varchar(255) null comment '应用包名',
    channel_name          varchar(255) null comment '渠道',
    pay_method            int          null comment '支付方式',
    pay_platform          int          null comment '支付平台',
    project_code          varchar(255) null comment '产品编号',
    ip                    varchar(255) null comment 'ip',
    pay_config_id         int          null comment '关联pay_config',
    out_trade_no          varchar(128) null comment '当前订单号',
    status                int          null comment '状态 1创建签约 2签约成功 3解约 4扣款失败',
    create_time           datetime     null comment '创建时间',
    update_time           datetime     null comment '更新时间'
)
    comment '支付签约表';

create index idx_create_time
    on pay_agreement (create_time);

create index idx_external_agreement_no
    on pay_agreement (external_agreement_no);

create index idx_next_execute_time
    on pay_agreement (next_execute_time);

create index idx_status
    on pay_agreement (status);

create table pay_config
(
    id                          int auto_increment comment 'id'
        primary key,
    name                        varchar(255)                       null comment '名称',
    method                      int                                not null comment '1:支付宝 2:微信 3:苹果',
    merchant_id                 int                                null,
    notify_url                  varchar(255)                       null comment '回调URL，微信支付宝通用',
    agreement_notify_url        varchar(255)                       null comment '签约回调URL，支付宝',
    wx_app_id                   varchar(255)                       null comment '微信应用id',
    wx_api_version              varchar(255)                       null comment 'v2,v3',
    zfb_app_id                  varchar(255)                       null comment '支付宝应用id',
    zfb_app_private_key         varchar(5000)                      null comment '支付宝应用私钥',
    zfb_app_public_key_path     varchar(1000)                      null comment '支付宝应用公钥文件路径',
    create_time                 datetime default CURRENT_TIMESTAMP not null,
    update_time                 datetime default CURRENT_TIMESTAMP not null,
    status                      int      default 0                 not null,
    zfb_alipay_root_cert_path   varchar(1000)                      null comment '支付宝根证书',
    zfb_alipay_public_cert_path varchar(1000)                      null comment '支付宝公钥证书',
    wx_merchant_id              varchar(255)                       null comment '微信商户id',
    wx_merchant_serial_number   varchar(255)                       null comment '微信商户证书序列号',
    wx_private_key_path         varchar(1000)                      null comment '微信商户API私钥文件路径',
    wx_api_v3_key               varchar(1000)                      null comment '微信商户API V3密钥',
    wx_v2_mch_key               varchar(1000)                      null comment '微信商户V2密钥',
    wx_v2_key_path              varchar(1000)                      null comment '微信商户V2私钥路径',
    apple_shared_secret         varchar(255)                       null comment '苹果共享密钥',
    google_secret_path          varchar(255)                       null,
    dy_app_id                   varchar(100)                       null,
    dy_token                    varchar(100)                       null,
    dy_salt                     varchar(100)                       null,
    dy_secret                   varchar(100)                       null,
    dy_private_key_path         varchar(100)                       null,
    dy_public_key_path          varchar(100)                       null
)
    comment '支付配置' collate = utf8mb4_unicode_ci;

create table pay_item
(
    id                 bigint auto_increment
        primary key,
    sort               int           default 0   not null comment '排序',
    code               varchar(255)              not null comment '商品编号，用于区分不同平台的相同商品',
    name               varchar(255)              not null comment '名称',
    amount             bigint                    not null comment '价格',
    original_amount    bigint        default 0   not null comment '原始价格',
    first_amount       bigint                    null comment '首次扣款金额',
    apple_goods_id     varchar(255)              null comment '苹果商品id',
    subscribable       int           default 0   not null,
    subscription_group varchar(255)              null,
    app_platform       int                       not null comment '应用平台',
    package_name       varchar(255)              not null comment '应用包名',
    channel_condition  varchar(1000) default '*' not null,
    create_time        datetime                  not null comment '创建时间',
    update_time        datetime                  not null comment '更新时间',
    status             int           default 0   not null,
    extra              json                      not null comment '额外字段json',
    remark             varchar(255)              null comment '备注'
)
    comment '商品表';

create table pay_item_channel
(
    id              bigint auto_increment
        primary key,
    item_id         bigint           not null,
    channel_name    varchar(255)     not null,
    amount          bigint           not null,
    original_amount bigint default 0 not null,
    create_time     datetime         not null,
    update_time     datetime         not null,
    status          int              not null
)
    comment '商品渠道表';

create index item_id
    on pay_item_channel (item_id);

create table pay_item_group
(
    id               bigint auto_increment
        primary key,
    priority         int          default 0         not null,
    group_code       varchar(255) default 'default' not null,
    group_name       varchar(255)                   null,
    package_name     varchar(255)                   not null comment '包名',
    app_platform     int                            not null comment '应用平台',
    channel_name     varchar(255) default '*'       not null comment '渠道',
    app_version_name varchar(255) default '*'       not null comment '版本',
    city             varchar(255) default '*'       not null comment '城市或地区',
    create_time      datetime                       not null,
    update_time      datetime                       not null,
    remark           varchar(255)                   null,
    status           int          default 0         not null comment '0:启用 1:禁用'
);

create index package_name_index
    on pay_item_group (package_name);

create table pay_item_ref_group
(
    id       bigint auto_increment
        primary key,
    group_id bigint        not null,
    item_id  bigint        not null,
    sort     int default 0 not null
);

create index group_id_index
    on pay_item_ref_group (group_id);

create table pay_merchant
(
    id                          int auto_increment comment 'id'
        primary key,
    name                        varchar(255)     null,
    method                      int              not null comment '1:支付宝 2:微信 3:苹果',
    daily_pay_limit             bigint default 0 not null,
    disabled_when_limited       int    default 0 not null,
    risk                        int    default 0 not null,
    create_time                 datetime         not null,
    update_time                 datetime         not null,
    status                      int    default 0 not null,
    zfb_alipay_root_cert_path   varchar(1000)    null comment '支付宝根证书',
    zfb_alipay_public_cert_path varchar(1000)    null comment '支付宝公钥证书',
    wx_merchant_id              varchar(255)     null comment '微信商户id',
    wx_merchant_serial_number   varchar(255)     null comment '微信商户证书序列号',
    wx_private_key_path         varchar(1000)    null comment '微信商户API私钥文件路径',
    wx_private_cert_path        varchar(1000)    null,
    wx_api_v3_key               varchar(1000)    null comment '微信商户API V3密钥',
    wx_v2_mch_key               varchar(1000)    null comment '微信商户V2密钥',
    wx_v2_key_path              varchar(1000)    null comment '微信商户V2私钥路径',
    apple_shared_secret         varchar(255)     null comment '苹果共享密钥'
);

create table pay_option
(
    id            bigint auto_increment
        primary key,
    package_name  varchar(255)  not null,
    app_platform  int           not null,
    title         varchar(255)  not null,
    pay_method    int           not null,
    pay_platform  int           not null,
    pay_config_id int           not null,
    pay_interval  int default 0 not null,
    sort          int default 0 not null,
    status        int default 0 not null,
    remark        varchar(255)  null
);

create index package_name_index
    on pay_option (package_name);

create table pay_order
(
    id           bigint auto_increment
        primary key,
    out_trade_no varchar(128) not null comment '支付订单号',
    pay_status   int          not null comment '-- 0:已创建 1:未支付 2:已支付 3:已关闭 4:已退款',
    item_id      bigint       not null comment '商品id',
    item_code    varchar(255) not null comment '商品编号',
    item_name    varchar(255) not null comment '商品名称',
    amount       bigint       not null comment '商品价格',
    ssid         varchar(64)  not null comment '用户唯一标识',
    device_id    varchar(64)  not null comment '设备id',
    phone        varchar(64)  null comment '电话号码',
    project_code varchar(255) null comment '产品编号',
    app_platform int          not null comment '应用平台',
    package_name varchar(255) not null comment '应用包名',
    channel_name varchar(255) null comment '渠道',
    ip           varchar(255) null comment 'id',
    create_time  datetime     not null comment '创建时间',
    update_time  datetime     not null comment '更新时间',
    status       int          not null,
    agreement_no varchar(128) null comment '签约协议号',
    constraint out_trade_no_index
        unique (out_trade_no)
)
    comment '订单表';

create index query_index
    on pay_order (package_name, create_time, pay_status, status);

create table pay_renew
(
    id                   bigint auto_increment
        primary key,
    out_trade_no         varchar(128) not null comment '原始支付订单号',
    ssid                 varchar(64)  not null comment '用户唯一标识',
    device_id            varchar(64)  not null comment '设备id',
    project_code         varchar(255) null comment '产品编号',
    item_id              bigint       not null comment '商品id',
    item_code            varchar(255) not null comment '商品编号',
    item_name            varchar(255) not null comment '商品名称',
    amount               bigint       not null comment '商品价格',
    renew_time           datetime     null comment '续订时间',
    expire_time          datetime     null comment '到期时间',
    create_time          datetime     not null comment '创建时间',
    update_time          datetime     not null comment '更新时间',
    status               int          not null,
    current_out_trade_no varchar(64)  null comment '当前支付订单号',
    order_type           int          null comment '订单类型 0原始订单 1续费订单',
    pay_method           int          null comment '支付方式 1支付宝 2微信  3苹果',
    package_name         varchar(255) null comment '包名'
)
    comment '续订表';

create index idx_current_out_trade_no
    on pay_renew (current_out_trade_no);

create index idx_out_trade_no
    on pay_renew (out_trade_no);

create index idx_query
    on pay_renew (package_name, create_time, order_type, pay_method);

create table pay_subscription
(
    id                 bigint auto_increment
        primary key,
    out_trade_no       varchar(128)  not null,
    subscription_group varchar(255)  not null,
    ssid               varchar(64)   not null,
    device_id          varchar(64)   not null,
    renew_item_id      bigint        null,
    auto_renew_status  int default 0 not null,
    expire_time        datetime      not null,
    create_time        datetime      not null,
    update_time        datetime      not null
);

create index ssid_index
    on pay_subscription (ssid);

create table pay_transfer
(
    id                    bigint auto_increment
        primary key,
    project_code          varchar(255)  null,
    package_name          varchar(255)  not null,
    channel_name          varchar(255)  null,
    device_id             varchar(64)   not null,
    ssid                  varchar(64)   not null,
    ip                    varchar(128)  null,
    amount                bigint        not null,
    remark                varchar(128)  null,
    status                int default 0 not null,
    trasfer_time          datetime      null,
    create_time           datetime      not null,
    update_time           datetime      not null,
    pay_method            int           not null,
    pay_platform          int           not null,
    pay_config_id         int           not null,
    out_transfer_no       varchar(64)   not null,
    out_transfer_batch_no varchar(64)   null,
    transfer_user_id      varchar(64)   null
);

create index out_transfer_no_index
    on pay_transfer (out_transfer_no);

create table payment
(
    id                      bigint auto_increment
        primary key,
    out_trade_no            varchar(128) not null,
    app_account_token       varchar(128) null,
    original_transaction_id varchar(64)  null,
    apple_goods_id          varchar(255) null,
    subscription_group      varchar(255) null,
    package_name            varchar(255) null,
    device_id               varchar(128) not null,
    ssid                    varchar(128) not null,
    amount                  bigint       not null,
    pay_platform            int          not null comment '1:安卓 2:苹果 3:移动web 4:pcweb(二维码)',
    pay_method              int          not null comment '1:支付宝 2:微信',
    pay_config_id           int          not null,
    status                  int          not null comment '1:未支付 2:已支付 3:已关闭 4:已退款',
    create_time             datetime     not null,
    pay_time                datetime     null,
    refund_time             datetime     null,
    update_time             datetime     not null,
    constraint out_trade_no
        unique (out_trade_no)
)
    comment '支付记录表';

create index app_account_token_index
    on payment (app_account_token);

create index device_id_index
    on payment (device_id);

create index ssid_index
    on payment (ssid);

create table product
(
    id                    int auto_increment
        primary key,
    name                  varchar(255)                     not null,
    product_code          varchar(255)                     not null,
    remark                varchar(255)                     null,
    unique_login_strategy varchar(64) default 'APP_UNIQUE' not null,
    create_time           datetime                         not null,
    update_time           datetime                         not null,
    status                int         default 0            not null,
    constraint product_code_unique
        unique (product_code)
);

create table project
(
    id                  int auto_increment
        primary key,
    name                varchar(255)  not null,
    project_code        varchar(255)  not null,
    access_key          varchar(255)  null,
    access_secret       varchar(255)  null,
    notify_url          varchar(255)  null,
    remark              varchar(255)  null,
    create_time         datetime      not null,
    update_time         datetime      not null,
    status              int default 0 not null,
    event_table_created int default 0 not null,
    constraint project_code_unique
        unique (project_code)
)
    collate = utf8mb4_unicode_ci;

create table promotion_advertiser
(
    advertiser_id      varchar(64)                           not null
        primary key,
    advertiser_name    varchar(255)                          null,
    advertiser_subject varchar(255)                          null,
    grant_source       varchar(64)                           null,
    rebate             double      default 1                 not null,
    remark             varchar(255)                          null,
    create_time        datetime    default CURRENT_TIMESTAMP not null,
    update_time        datetime    default CURRENT_TIMESTAMP not null,
    status             int         default 0                 not null,
    company            varchar(64)                           not null,
    app_code           varchar(64) default ''                not null,
    opt_user_id        bigint      default 0                 not null,
    auth_status        int         default 0                 not null,
    auth_time          datetime                              null,
    put_status         int         default 0                 not null,
    put_update_time    datetime                              null
);

create table promotion_attributed_count
(
    id            varchar(255)     not null
        primary key,
    advertiser_id varchar(255)     not null,
    app_code      varchar(255)     not null,
    count         bigint default 0 not null,
    constraint advertiser_app_unique
        unique (advertiser_id, app_code)
);

create index advertiser_index
    on promotion_attributed_count (advertiser_id);

create table promotion_grant_account
(
    id                   varchar(64)   not null
        primary key,
    name                 varchar(255)  null,
    company              varchar(64)   not null,
    app_id               varchar(64)   null,
    account_type         varchar(64)   null,
    user_id              varchar(64)   null,
    open_id              varchar(64)   null,
    account_id           varchar(64)   null,
    scope                varchar(255)  null,
    access_token         varchar(1000) null,
    expires_time         datetime      null,
    refresh_token        varchar(1000) null,
    refresh_expires_time datetime      null,
    auth_time            datetime      null,
    create_time          datetime      not null,
    update_time          datetime      not null,
    status               int default 0 not null
);

create table promotion_grant_app
(
    id          varchar(64)   not null
        primary key,
    name        varchar(255)  null,
    company     varchar(64)   not null,
    subject     varchar(255)  null,
    app_secret  varchar(255)  null,
    create_time datetime      not null,
    update_time datetime      not null,
    status      int default 0 not null
);

create table promotion_grant_source
(
    id                   varchar(64)   not null
        primary key,
    name                 varchar(255)  null,
    company              varchar(64)   not null,
    subject              varchar(255)  null,
    app_id               varchar(64)   null,
    app_secret           varchar(255)  null,
    account_type         varchar(64)   null,
    user_id              varchar(64)   null,
    open_id              varchar(64)   null,
    account_id           varchar(64)   null,
    scope                varchar(255)  null,
    access_token         varchar(1000) null,
    expires_time         datetime      not null,
    refresh_token        varchar(1000) null,
    refresh_expires_time datetime      not null,
    create_time          datetime      not null,
    update_time          datetime      not null,
    status               int default 0 not null
);

create table promotion_opt_app_group
(
    id            bigint auto_increment
        primary key,
    opt_user_id   bigint         not null,
    app_code_list varchar(10000) not null,
    name          varchar(255)   not null,
    remark        varchar(255)   null,
    create_time   datetime       not null,
    update_time   datetime       not null,
    status        int default 0  not null
);

create index opt_user_id_index
    on promotion_opt_app_group (opt_user_id);

create table ssid_info
(
    device_id    varchar(64) not null comment '设备id'
        primary key,
    ssid         varchar(64) not null comment '统计标识id',
    login_status int         not null comment '用户登录状态 0:未登录 1:已登录',
    user_id      varchar(64) null comment '用户id',
    tid          varchar(64) null comment 'jwt id'
);

create index ssid_index
    on ssid_info (ssid);

create index user_id_index
    on ssid_info (user_id);

create table subject
(
    id          int auto_increment
        primary key,
    name        varchar(255) not null,
    host        varchar(255) null,
    remark      varchar(255) null,
    create_time datetime     not null,
    update_time datetime     not null,
    status      int          not null,
    constraint subject_index
        unique (name)
);

create table user
(
    id                  varchar(64)                      not null
        primary key,
    ssid                varchar(64)                      null,
    package_name        varchar(255) collate utf8mb4_bin not null comment '产品包名product_package_name',
    channel_name        varchar(255) collate utf8mb4_bin null comment '渠道名称',
    phone               varchar(32)                      null comment '注册的手机号码',
    account             varchar(64)                      null comment '注册的帐号',
    password            varchar(64)                      null,
    salt                varchar(32)                      null,
    apple_user_identity varchar(64)                      null comment '苹果用户id',
    create_time         datetime                         not null comment '创建时间',
    update_time         datetime                         not null comment '更新时间',
    register_device_id  varchar(64)                      null,
    present_device_id   varchar(64)                      null,
    last_login_time     datetime                         null,
    status              int default 0                    not null comment '0:启用 1:禁用'
);

create index package_phone_status_index
    on user (package_name, phone, status);

create table user_unique_login
(
    id             bigint auto_increment
        primary key,
    user_id        varchar(64)  not null,
    product_code   varchar(255) not null,
    login_strategy varchar(64)  not null,
    login_domain   varchar(255) not null,
    tid            varchar(32)  not null,
    create_time    datetime     not null,
    update_time    datetime     not null
);

create index user_id_product_code_index
    on user_unique_login (user_id, product_code, login_strategy, login_domain);