privatedomain-parent/doc/ddl/modify/v1.0.sql

ALTER TABLE `privatedomain`.`user`
ADD COLUMN `head_img` varchar(255) NULL COMMENT '头像' AFTER `open_id`,
MODIFY COLUMN `nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称' AFTER `open_id`,
ADD COLUMN `gender` tinyint(2) NULL COMMENT '性别' AFTER `nick_name`,
ADD COLUMN `master_id` bigint(20) NULL COMMENT '师傅' AFTER `phone`,
ADD COLUMN `vip` tinyint(2) NULL DEFAULT 0 COMMENT 'vip' AFTER `master_id`,
ADD COLUMN `vip_expiration_time` timestamp(0) NULL COMMENT 'vip过期时间' AFTER `vip`
MODIFY COLUMN `phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '手机号' AFTER `head_img`,
ADD COLUMN `agency` tinyint(2) NULL COMMENT '代言资质' AFTER `vip_expiration_time`;




ALTER TABLE `privatedomain`.`user`
ADD COLUMN `expiration_vip_type` tinyint(2) NULL COMMENT '过期会员类型' AFTER `login_time`;

ALTER TABLE `privatedomain`.`background_image`
MODIFY COLUMN `status` tinyint(2) NULL DEFAULT NULL COMMENT '状态' AFTER `img_src`;

ALTER TABLE `privatedomain`.`private_domain`
MODIFY COLUMN `auto_change_qr` tinyint(2) NULL DEFAULT 0 COMMENT '自动更换二维码开关' AFTER `wechat_num`;

INSERT INTO `privatedomain`.`system_config`(`name`, `value`, `des`, `create_time`, `update_time`, `sn`) VALUES ('额外购买的私域价格', '79.0', NULL, '2021-01-08 10:08:30', NULL, 'EWGMSYJG');
INSERT INTO `privatedomain`.`system_config`(`name`, `value`, `des`, `create_time`, `update_time`, `sn`) VALUES ('会员可编辑的免费私域数量', '5', NULL, '2021-01-08 10:15:06', NULL, 'HYSYS');
INSERT INTO `privatedomain`.`system_config`(`name`, `value`, `des`, `create_time`, `update_time`, `sn`) VALUES ('体验会员可编辑的免费私域数', '1', NULL, '2021-01-08 10:16:00', NULL, 'TYHYSYS');


ALTER TABLE `privatedomain`.`user`
ADD COLUMN `update_time` timestamp(0) NULL COMMENT '更新时间' AFTER `expiration_vip_type`;

ALTER TABLE `privatedomain`.`orders`
ADD COLUMN `inviter_id` bigint(20) NULL COMMENT '开通vip时的邀请人' AFTER `user_id`;

ALTER TABLE `privatedomain`.`user_account`
ADD UNIQUE INDEX `user_id_index`(`user_id`) USING BTREE COMMENT '用户id唯一索引';


ALTER TABLE `privatedomain`.`orders`
ADD COLUMN `preferential_price` decimal(10, 0) NULL DEFAULT NULL COMMENT '邀请人优惠金额' AFTER `total_amount`;

ALTER TABLE `privatedomain`.`pay_content`
ADD COLUMN `immediately_reduce_price_amount` decimal(10, 2) NULL COMMENT '立减金额' AFTER `total_amount`,
ADD COLUMN `name` varchar(64) NULL COMMENT '支付类型名' AFTER `immediately_reduce_price_amount`;

INSERT INTO `privatedomain`.`pay_content`(`id`, `sn`, `parent_id`, `quantity`, `unit_price`, `total_amount`, `immediately_reduce_price_amount`, `name`, `remarks`, `status`) VALUES (100000, 'vip', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `privatedomain`.`pay_content`(`id`, `sn`, `parent_id`, `quantity`, `unit_price`, `total_amount`, `immediately_reduce_price_amount`, `name`, `remarks`, `status`) VALUES (100001, NULL, 100000, 1, 39.00, 39, 0.00, 'VIP月卡', NULL, 0);
INSERT INTO `privatedomain`.`pay_content`(`id`, `sn`, `parent_id`, `quantity`, `unit_price`, `total_amount`, `immediately_reduce_price_amount`, `name`, `remarks`, `status`) VALUES (100002, NULL, 100000, 12, 33.25, 399, 69.00, 'VIP年卡', NULL, 0);
INSERT INTO `privatedomain`.`pay_content`(`id`, `sn`, `parent_id`, `quantity`, `unit_price`, `total_amount`, `immediately_reduce_price_amount`, `name`, `remarks`, `status`) VALUES (200000, 'extra_private_domain', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `privatedomain`.`pay_content`(`id`, `sn`, `parent_id`, `quantity`, `unit_price`, `total_amount`, `immediately_reduce_price_amount`, `name`, `remarks`, `status`) VALUES (200001, NULL, 200000, 1, 79.00, 79, 0.00, NULL, NULL, 0);


ALTER TABLE `privatedomain`.`pay_content`
MODIFY COLUMN `total_amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '总价' AFTER `unit_price`;

ALTER TABLE `privatedomain`.`orders`
MODIFY COLUMN `refund_time` timestamp(0) NULL DEFAULT NULL COMMENT '退款时间' AFTER `content_id`;

ALTER TABLE `privatedomain`.`orders`
MODIFY COLUMN `total_amount` decimal(11, 2) NULL DEFAULT NULL COMMENT '订单金额' AFTER `subjects`;

ALTER TABLE `privatedomain`.`orders`
ADD COLUMN `contact` varchar(64) NULL COMMENT '联系方式' AFTER `content_id`;

ALTER TABLE `privatedomain`.`user`
ADD COLUMN `become_agency_time` timestamp(0) NULL COMMENT '成为代理时间' AFTER `update_time`;

ALTER TABLE `privatedomain`.`user_qr_code`
ADD COLUMN `create_time` timestamp(0) NULL COMMENT '创建时间' AFTER `auto_change`;

ALTER TABLE `privatedomain`.`private_domain`
ADD COLUMN `url_key` varchar(128) NULL DEFAULT NULL COMMENT '生成链接的key' AFTER `auto_change_qr`,
ADD COLUMN `url_key_create_time` timestamp(0) NULL DEFAULT NULL COMMENT 'url_key生成时间' AFTER `url_key`;

ALTER TABLE `privatedomain`.`private_domain`
ADD COLUMN `qr_code_change_time` timestamp(0) NULL COMMENT '二维码更换时间' AFTER `update_time`;

INSERT INTO `privatedomain`.`system_config`(`name`, `value`, `des`, `create_time`, `update_time`, `sn`) VALUES ('最低提现金额', '10', NULL, '2021-01-15 19:21:34', NULL, 'ZDTXJE');

ALTER TABLE `privatedomain`.`user`
ADD COLUMN `becom_disciple_time` timestamp(0) NULL COMMENT '成为徒弟时间' AFTER `become_agency_time`;

INSERT INTO `privatedomain`.`admin_user`(`account`, `password`, `nameCn`, `nameEn`, `lockFlag`, `delFlag`, `genUid`, `createTime`) VALUES ('super', '$2a$10$EfA1I9IMzk9ecGBgT/ig9.vOPbtydB6erkn4117y0s8GI4.V70sHe', '超级管理员', 'superman', 0, 0, 0, now());

ALTER TABLE `privatedomain`.`statistical`
ADD COLUMN `update_time` timestamp(0) NULL COMMENT '更新时间' AFTER `yesterday_agency_num`;

INSERT INTO `privatedomain`.`statistical`(`yesterday_register_num`, `yesterday_vip_num`, `yesterday_agency_num`, `update_time`) VALUES (0, 0, 0, '2021-01-20 17:47:57');

ALTER TABLE `privatedomain`.`video_tutorial`
ADD COLUMN `seq` int(3) NULL COMMENT '排序' AFTER `release_time`;

INSERT INTO `privatedomain`.`official_assistant`(`nickName`, `headImg`) VALUES ('官方账号', 'https://privatedomain.oss-cn-shenzhen.aliyuncs.com/qrcodeAndBgPic/2021012539392526_80702b81e30d4a1485ca6444afa45ff7.jpg');

ALTER TABLE `privatedomain`.`private_domain`
ADD COLUMN `is_effective` tinyint(2) NULL DEFAULT 0 COMMENT '是否有效1有效，0无效' AFTER `qr_code_change_time`;





-- 2021-02-25
ALTER TABLE `privatedomain`.`user_qr_code`
ADD COLUMN `domain_id` bigint(20) NULL COMMENT '所属私域站id' AFTER `create_time`;

-- v1.1.0
ALTER TABLE `privatedomain`.`department_employee`
ADD COLUMN `is_leader_in_dept` int(10) NULL COMMENT '表示在所在的部门内是否为上级0-否；1-是' AFTER `department_id`;

ALTER TABLE `privatedomain`.`department_employee`
ADD COLUMN `order` int(10) NULL COMMENT '部门内的排序值,默认为0,数值越大排序越前面' AFTER `is_leader_in_dept`;

ALTER TABLE `privatedomain`.`employees`
DROP COLUMN `order`;

ALTER TABLE `privatedomain`.`department_employee`
ADD UNIQUE INDEX `department_employee_index`(`employee_userid`, `department_id`) COMMENT '部门id与员工用户id唯一索引';

ALTER TABLE `privatedomain`.`department`
CHANGE COLUMN `order` `order_no` int(20) NULL DEFAULT NULL COMMENT '在父部门中的次序值' AFTER `parentid`;

ALTER TABLE `privatedomain`.`department_employee`
CHANGE COLUMN `order` `order_no` int(10) NULL DEFAULT NULL COMMENT '部门内的排序值,默认为0,数值越大排序越前面' AFTER `is_leader_in_dept`;

ALTER TABLE `privatedomain`.`promote_qr_code`
CHANGE COLUMN `state_name` `channel_id` bigint(20) NULL DEFAULT NULL COMMENT '渠道标签id' AFTER `group_id`,
MODIFY COLUMN `group_id` bigint(20) NULL DEFAULT NULL COMMENT '分组id' AFTER `name`;

ALTER TABLE `privatedomain`.`promote_qr_code`
ADD COLUMN `create_time` timestamp(0) NULL COMMENT '创建时间' AFTER `state_key`;

ALTER TABLE `privatedomain`.`user`
ADD COLUMN `unionid` varchar(64) NULL COMMENT '微信unionid' AFTER `open_id`;

ALTER TABLE `privatedomain`.`clientele`
ADD UNIQUE INDEX `unionid_index`(`unionid`) COMMENT 'unionid唯一索引';

ALTER TABLE `privatedomain`.`clientele`
MODIFY COLUMN `add_way` int(4) NULL DEFAULT NULL COMMENT '该成员添加此客户的来源,具体含义详见来源定义' AFTER `state`;

ALTER TABLE `privatedomain`.`employees`
MODIFY COLUMN `main_department` bigint(20) NULL DEFAULT NULL COMMENT '主部门' AFTER `open_userid`;

ALTER TABLE `privatedomain`.`clientele`
DROP INDEX `unionid_index`,
ADD UNIQUE INDEX `unionid_follow_userid_index`(`unionid`, `follow_userid`) USING BTREE COMMENT 'unionid and follow_userid唯一索引';

ALTER TABLE `privatedomain`.`promote_qr_code`
DROP COLUMN `channel_id`;

ALTER TABLE `privatedomain`.`promote_qr_code`
ADD COLUMN `clientele_statistical` int(10) NOT NULL DEFAULT 0 COMMENT '客户统计' AFTER `name`,
ADD COLUMN `loss_of_statistical` int(10) NOT NULL DEFAULT 0 COMMENT '流失客户统计' AFTER `clientele_statistical`;

ALTER TABLE `privatedomain`.`promote_qrcode_and_employees`
ADD COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`) USING BTREE;

insert into tag(id,name,type,create_time) VALUES (10000000,'默认分组',1,now());

ALTER TABLE `privatedomain`.`clientele`
DROP INDEX `unionid_follow_userid_index`,
ADD INDEX `unionid_index`(`unionid`) USING BTREE COMMENT 'unionid_index',
ADD UNIQUE INDEX `external_userid_follow_userid_index`(`external_userid`, `follow_userid`) USING BTREE COMMENT 'follow_userid and follow_userid唯一索引';