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);