privatedomain-parent/doc/ddl/table/V1.0.sql

CREATE TABLE `privatedomain`.`user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `open_id` varchar(32) NOT NULL COMMENT '微信openid',
  `phone` varchar(16) NOT NULL COMMENT '手机号',
  `nick_name` varchar(32) NOT NULL COMMENT '昵称',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  `login_time` timestamp(0) NULL COMMENT '最近登录时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10101000 COMMENT = '用户表';

CREATE TABLE `privatedomain`.`agency_info`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NULL COMMENT '用户id',
  `contacts` varchar(124) NULL COMMENT '联系人名称',
  `phone` varchar(124) NULL COMMENT '联系电话',
  `wx_num` varchar(64) NULL COMMENT '联系人微信',
  `video_account` varchar(255) NULL COMMENT '视频号名称',
  `video_account_fans` integer(10) NULL COMMENT '视频号粉丝数',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10010121 COMMENT = '代言资质需完善的信息表';

CREATE TABLE `privatedomain`.`orders`  (
  `order_no` bigint(20) NOT NULL AUTO_INCREMENT,
  `busi_order_type` varchar(20) NULL COMMENT '业务订单类型(业务方区分不同订单)',
  `busi_order_name` varchar(32) NULL COMMENT '业务名字',
  `subjects` varchar(64) NULL COMMENT '订单信息，支付标题',
  `total_amount` decimal(11, 0) NULL COMMENT '订单金额',
  `order_status` tinyint(4) NULL COMMENT '订单状态，OrderStatusEnum',
  `pay_status` tinyint(4) NULL COMMENT '支付状态，PayStatusEnum',
  `pay_no` varchar(64) NULL COMMENT '支付号',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  `update_time` timestamp(0) NULL COMMENT '更新时间',
  `user_id` bigint(20) NULL COMMENT '用户id',
  `content_id` bigint(20) NULL COMMENT '支付内容id',
  `refund_time` datetime(0) NULL COMMENT '退款时间',
  PRIMARY KEY (`order_no`),
  UNIQUE INDEX `orderNo`(`order_no`) USING BTREE,
  UNIQUE INDEX `payNo`(`pay_no`(32)) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10210013 COMMENT = '订单表';

CREATE TABLE `privatedomain`.`pay_content`  (
  `id` bigint(20) NOT NULL,
  `sn` varchar(32) NULL COMMENT '类型',
  `parent_id` bigint(20) NULL COMMENT '父类id',
  `quantity` int(10) NULL COMMENT '数量',
  `unit_price` decimal(10, 2) NULL COMMENT '原来的单价',
  `total_amount` decimal(10, 0) NULL COMMENT '总价',
  `remarks` varchar(32) NULL COMMENT '备注',
  `status` tinyint(4) NULL COMMENT '状态:0正常,1:下架',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 11110111 COMMENT = '系统配置的可以消费的数据表';

CREATE TABLE `privatedomain`.`invite_relationship`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NULL COMMENT '被邀请人id',
  `inviter_id` bigint(20) NULL COMMENT '邀请人id',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10704000 COMMENT = '邀请关系表';

CREATE TABLE `privatedomain`.`private_domain`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `type` tinyint(2) NULL COMMENT '类型:0会员,1额外购买',
  `background_id` bigint(20) NULL COMMENT '背景id',
  `qr_code_id` bigint(20) NULL COMMENT '二维码图片id',
  `content` varchar(255) NULL COMMENT '文案',
  `wechat_num` varchar(255) NULL COMMENT '微信号',
  `auto_change_qr` tinyint(2) NULL COMMENT '自动更换二维码开关',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  `update_time` timestamp(0) NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 34002100 COMMENT = '用户私域站';

CREATE TABLE `privatedomain`.`background_image`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_id` bigint(20) NULL COMMENT '用户id',
  `img_src` varchar(255) NULL COMMENT '图片地址',
  `status` varchar(255) NULL COMMENT '状态',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  `update_time` timestamp(0) NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 23001200 COMMENT = '背景图片';

CREATE TABLE `privatedomain`.`user_qr_code`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NULL COMMENT '用户id',
  `qr_code_src` varchar(255) NULL COMMENT '二维码图片地址',
  `auto_change` tinyint(2) NULL COMMENT '自动更换',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 18300000 COMMENT = '用户微信二维码';

CREATE TABLE `system_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  `value` varchar(256) DEFAULT NULL COMMENT '值',
  `des` varchar(128) DEFAULT NULL COMMENT '文字描述',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  `sn` varchar(20) DEFAULT NULL COMMENT '编码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001037 DEFAULT CHARSET=utf8mb4 COMMENT='系统初始化配置参数';

CREATE TABLE `trade_process_error` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` varchar(20) DEFAULT NULL COMMENT '订单号',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '异常发生时间',
  `error_message` varchar(2500) DEFAULT NULL COMMENT '错误信息',
  `status` tinyint(4) DEFAULT NULL COMMENT '处理状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `privatedomain`.`user_account`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NULL COMMENT '用户id',
  `balance` double(10, 0) NULL COMMENT '余额',
  `withdrawal_amount` varchar(255) NULL COMMENT '总提现金额',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 11230000 COMMENT = '用户钱包';

CREATE TABLE `privatedomain`.`user_account_detail`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NULL COMMENT '用户id',
  `type` tinyint(2) NULL COMMENT '类型:0代言收益,1徒弟收益,2:提现',
  `before_balance` double(10, 0) NULL COMMENT '交易钱余额',
  `trade_balance` double(10, 0) NULL COMMENT '交易金额',
  `after_balance` double(10, 0) NULL COMMENT '交易后金额',
  `order_no` bigint(20) NULL COMMENT '订单号',
  `disciple_id` bigint(20) NULL COMMENT '徒弟的id',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 12243400 COMMENT = '用户钱包明细';


CREATE TABLE `admin_permission` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `role_id` int(10) unsigned NOT NULL COMMENT '角色ID',
  `create_uid` int(10) unsigned NOT NULL COMMENT '生成此权限的用户ID',
  `module_name` varchar(32) DEFAULT '' COMMENT '模块名称PModule.pname',
  `privilege` varchar(64) DEFAULT '' COMMENT '权限信息',
  `plateform` varchar(16) NOT NULL DEFAULT '' COMMENT '子系统标识',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `apermission_userid_idx` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户权限明细';


CREATE TABLE `admin_role` (
  `role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(32) NOT NULL COMMENT '角色名称',
  `role_desc` varchar(255) NOT NULL COMMENT '角色描述',
  `create_uid` int(10) unsigned NOT NULL COMMENT '创建用户的id',
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可用',
  `plateform` varchar(8) NOT NULL DEFAULT '' COMMENT '子系统标识',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `admin_user` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `account` varchar(32) NOT NULL DEFAULT '' COMMENT '帐号',
  `password` varchar(64) NOT NULL DEFAULT '',
  `nameCn` varchar(16) NOT NULL DEFAULT '' COMMENT '中文名',
  `nameEn` varchar(16) NOT NULL DEFAULT '' COMMENT '英文名',
  `lockFlag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '锁定标识',
  `delFlag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
  `genUid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建人',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_user_acc_idx` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运营后台用户表';

CREATE TABLE `statistical` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `yesterday_register_num` int(10) DEFAULT '0' COMMENT '昨日注册人数',
  `yesterday_vip_num` int(10) DEFAULT '0' COMMENT '昨日会员人数',
  `yesterday_agency_num` int(10) DEFAULT '0' NULL COMMENT '昨日代言人数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `privatedomain`.`video_tutorial`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `des` varchar(255) NULL COMMENT '描述',
  `video_bg_pic_src` varchar(255) NULL COMMENT '视频封面图片',
  `video_src` varchar(255) NULL COMMENT '视频地址',
  `official_assistant_id` bigint(20) NULL COMMENT '官方小助手id',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  `release_time` timestamp(0) NULL COMMENT '发布时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 34006300 COMMENT = '视频教程表';

CREATE TABLE `privatedomain`.`official_assistant`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `nick_name` varchar(255) NULL COMMENT '昵称',
  `head_img` varchar(255) NULL COMMENT '头像',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 21001100 COMMENT = '官方小助手';

CREATE TABLE `user_account_with_draw_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `partner_trade_no` varchar(32) DEFAULT NULL COMMENT '微信商户订单号',
  `payment_no` varchar(64) DEFAULT NULL COMMENT '微信付款单号',
  `pay_fund_order_id` varchar(64) DEFAULT NULL COMMENT '支付资金流水号',
  `with_draw_amount` double(20,2) DEFAULT NULL COMMENT '提现金额',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `status` tinyint(2) DEFAULT NULL COMMENT '状态',
  `verify_user_id` bigint(20) DEFAULT NULL COMMENT '审核人id',
  `verify_date` timestamp NULL DEFAULT NULL COMMENT '审核时间',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注信息',
  `transfer_shortcut_url` varchar(300) DEFAULT NULL COMMENT '转账图片',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10085299 DEFAULT CHARSET=utf8mb4 COMMENT='用户账户提现详情表\n';

CREATE TABLE `with_draw_verify_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
  `operating_time` timestamp NULL DEFAULT NULL COMMENT '操作时间',
  `des` varchar(120) DEFAULT NULL COMMENT '状态说明',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11201400 DEFAULT CHARSET=utf8mb4 COMMENT='提现审核日志';

-- v1.1.0

CREATE TABLE `privatedomain`.`department`  (
    `id` bigint(20) NOT NULL COMMENT '创建的部门id',
    `name` varchar(255) NULL COMMENT '部门名称',
    `parentid` bigint(20) NULL COMMENT '父部门id。根部门为1',
    `order` int(20) NULL COMMENT '在父部门中的次序值',
    PRIMARY KEY (`id`)
) COMMENT = '企业微信部门表';

CREATE TABLE `privatedomain`.`employees`  (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `userid` varchar(64) NOT NULL COMMENT '企业微信用户id',
    `name` varchar(64) NULL COMMENT '成员名称',
    `mobile` varchar(64) NULL COMMENT '手机号码',
    `order` int(10) NULL COMMENT '部门内的排序值，默认为0',
    `position` varchar(128) NULL COMMENT '职务信息',
    `gender` tinyint(3) NULL COMMENT '0表示未定义，1表示男性，2表示女性',
    `email` varchar(128) NULL COMMENT '邮箱',
    `avatar` varchar(128) NULL COMMENT '头像url',
    `thumb_avatar` varchar(128) NULL COMMENT '头像缩略图url',
    `telephone` varchar(64) NULL COMMENT '座机',
    `alias` varchar(64) NULL COMMENT '别名',
    `extattr` varchar(128) NULL COMMENT '扩展属性',
    `status` tinyint(3) NULL COMMENT '激活状态: 1=已激活，2=已禁用，4=未激活，5=退出企业',
    `qr_code` varchar(255) NULL COMMENT '员工个人二维码，扫描可添加为外部联系人',
    `open_userid` varchar(64) NULL COMMENT '全局唯一',
    `main_department` int(10) NULL COMMENT '主部门\n',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `employee_user_id_index`(`userid`) COMMENT '企业微信用户id唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 11001100 COMMENT = '部门成员详情';

CREATE TABLE `department_employee` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `employee_userid` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '企业微信用户id',
    `department_id` bigint(20) NOT NULL COMMENT '部门id',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51230100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='企业微信员工与部门关系表';

CREATE TABLE `privatedomain`.`promote_qr_code`  (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `config_id` varchar(64) NULL COMMENT '新增联系方式的配置id',
    `qr_code` varchar(255) NULL COMMENT '二维码',
    `name` varchar(64) NULL COMMENT '二维码名称',
    `group_id` int(0) NULL COMMENT '分组id',
    `state_name` varchar(64) NULL COMMENT '渠道名称',
    `state_key` varchar(128) NULL COMMENT '渠道key(企业微信state参数传输值)',
    PRIMARY KEY (`id`)
)ENGINE = InnoDB AUTO_INCREMENT = 22301100 COMMENT = '推广引流二维码表';

CREATE TABLE `privatedomain`.`tag`  (
    `id` int(0) NULL AUTO_INCREMENT,
    `name` varchar(64) NULL COMMENT '标签名',
    `type` tinyint(3) NULL COMMENT '类型：0:引流渠道，1:引流分组',
    `create_time` timestamp(0) NULL COMMENT '创建时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 48421100 COMMENT = '标签表';

CREATE TABLE `privatedomain`.`clientele`  (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `external_userid` varchar(64) NOT NULL COMMENT '外部联系人的userid',
    `name` varchar(64) NULL COMMENT '外部联系人的名称',
    `avatar` varchar(255) NULL COMMENT '外部联系人的头像',
    `type` tinyint(3) NULL COMMENT '外部联系人的类型，1表示该外部联系人是微信用户，2表示该外部联系人是企业微信用户',
    `gender` tinyint(3) NULL COMMENT '外部联系人性别 0-未知 1-男性 2-女性',
    `unionid` varchar(64) NULL COMMENT '外部联系人在微信开放平台的唯一身份标识',
    `follow_userid` varchar(64) NULL COMMENT '添加了此外部联系人的企业成员userid',
    `state` varchar(32) NULL COMMENT '企业自定义的state参数',
    `add_way` tinyint(3) NULL COMMENT '该成员添加此客户的来源,具体含义详见来源定义',
    `createtime` timestamp(0) NULL COMMENT '该成员添加此外部联系人的时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 21212100 COMMENT = '客户详情表';


CREATE TABLE `privatedomain`.`promote_qrcode_and_employees`  (
    `promote_rq_code_id` bigint(20) NOT NULL COMMENT '推广引流二维码id',
    `employee_id` bigint(20) NOT NULL COMMENT '员工id',
    `create_time` timestamp(0) NULL COMMENT '创建时间',
    PRIMARY KEY (`promote_rq_code_id`)
) COMMENT = '推广引流活码和员工关联表';


CREATE TABLE `privatedomain`.`tag_and_promote_qrcode`  (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `tag_id` bigint(20) NOT NULL COMMENT '标签id',
    `promote_qrcode_id` bigint(20) NOT NULL COMMENT '二维码id',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '标签与企业微信推广活码关联表';