toktok-server/toktok-parent/doc/sql/ddl/modify/V1.0.sql

ALTER TABLE `toktok`.`user`
ADD COLUMN `gender` tinyint(3) NULL COMMENT '性别' AFTER `nick_name`;

ALTER TABLE `toktok`.`user`
MODIFY COLUMN `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户id' FIRST;

ALTER TABLE `toktok`.`account_third_wechat`
ADD COLUMN `type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '类型，见wechatTypeEnum' AFTER `unionid`;


INSERT INTO `admin_role` VALUES (1, '管理员', '第二大的权限集合体，仅次于super', 1, 1, '', '2021-01-20 11:34:02');
INSERT INTO `admin_role` VALUES (2, '开发人员', '开发人员，只能看？', 2, 1, '', '2021-01-20 11:34:02');
INSERT INTO `admin_role` VALUES (3, '运营人员', '未分配提现和订单权限', 1, 1, '', '2021-01-20 11:34:03');

INSERT INTO `toktok`.`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 `toktok`.`brewery`
ADD COLUMN `bg_color` varchar(12) NULL COMMENT '背景图主色调' AFTER `log_img`,
ADD COLUMN `detail_id` bigint(20) NULL COMMENT '详情id' AFTER `bg_color`;

ALTER TABLE `toktok`.`brewery`
ADD COLUMN `name` varchar(128) NULL COMMENT '酿酒厂名称' AFTER `id`;

ALTER TABLE `toktok`.`brewery`
DROP COLUMN `detail_id`;

ALTER TABLE `toktok`.`brewery`
ADD COLUMN `cover_img` varchar(255) NULL COMMENT '封面图' AFTER `district_id`;

ALTER TABLE `toktok`.`sku`
DROP COLUMN `product_name`,
DROP COLUMN `com_img`;

ALTER TABLE `toktok`.`product`
ADD COLUMN `brief` varchar(255) NULL COMMENT '简要描述,卖点等' AFTER `com_type_id`,
ADD COLUMN `pic` varchar(255) NULL COMMENT '商品主图' AFTER `brief`,
ADD COLUMN `imgs` varchar(255) NULL COMMENT '商品图片' AFTER `pic`;

ALTER TABLE `toktok`.`product`
CHANGE COLUMN `com_name` `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名' AFTER `id`,
CHANGE COLUMN `com_type_id` `type_id` bigint(20) NULL DEFAULT NULL COMMENT '商品类型id（tag_dictionary表）' AFTER `english_name`;

ALTER TABLE `toktok`.`sku`
CHANGE COLUMN `com_id` `product_id` bigint(20) NOT NULL COMMENT '商品id' AFTER `id`;

ALTER TABLE `toktok`.`sku`
ADD COLUMN `properties` varchar(255) NULL COMMENT '销售属性组合字符串,格式是t1:v1;t2:v2' AFTER `product_id`;

ALTER TABLE `toktok`.`product_tag_value`
CHANGE COLUMN `tag_value_id` `tag_id` bigint(20) NOT NULL COMMENT '标签id' AFTER `product_id`,
MODIFY COLUMN `product_id` bigint(20) NOT NULL COMMENT 'product_id' AFTER `id`,
ADD COLUMN `value` varchar(128) NULL COMMENT '标签值' AFTER `tag_id`;


ALTER TABLE `toktok`.`product`
CHANGE COLUMN `type_id` `type` bigint(20) NULL DEFAULT NULL COMMENT '商品类型' AFTER `english_name`;

ALTER TABLE `toktok`.`product_tag_value`
DROP COLUMN `value`,
CHANGE COLUMN `tag_id` `tag_value_id` bigint(20) NOT NULL COMMENT '标签值id' AFTER `product_id`;

ALTER TABLE `toktok`.`product`
ADD COLUMN `desc` varchar(64) NULL COMMENT '简介' AFTER `english_name`,
ADD COLUMN `cover_img_url` varchar(255) NULL COMMENT '封面图' AFTER `desc`,
ADD COLUMN `img_urls` varchar(512) NULL COMMENT '截取的详情图列表' AFTER `cover_img_url`;

INSERT INTO `toktok`.`tag_dictionary`(`id`, `parent_id`, `name`, `sn`) VALUES (10000000, 0, '风格', 'fg');
INSERT INTO `toktok`.`tag_dictionary`(`id`, `parent_id`, `name`, `sn`) VALUES (10000001, 0, '规格', 'gg');
INSERT INTO `toktok`.`tag_dictionary`(`id`, `parent_id`, `name`, `sn`) VALUES (10000002, 0, '酒厂', 'jc');

ALTER TABLE `toktok`.`product`
CHANGE COLUMN `desc` `des` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '简介' AFTER `english_name`;

ALTER TABLE `toktok`.`tag_value`
ADD COLUMN `seq` int(10) NULL COMMENT '排序值 正序' AFTER `tag_value`,
ADD COLUMN `create_time` timestamp(0) NULL AFTER `seq`;

ALTER TABLE `toktok`.`product`
ADD COLUMN `weimob_goods_id` bigint(20) NULL COMMENT '微盟商品id' AFTER `update_time`;

ALTER TABLE `toktok`.`product` AUTO_INCREMENT = 33702500;
ALTER TABLE `toktok`.`sku` AUTO_INCREMENT = 62582280;

ALTER TABLE `toktok`.`tag_dictionary`
MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT FIRST,
AUTO_INCREMENT = 10000002;

ALTER TABLE `toktok`.`banner`
ADD COLUMN `jump_type` tinyint(3) NOT NULL DEFAULT 1 COMMENT '跳转类型' AFTER `img_url`,
ADD COLUMN `jump_url` varchar(255) NULL COMMENT '跳转地址' AFTER `jump_type`;

INSERT INTO `toktok`.`tag_value` (`tag_id`,`tag_value`,`seq`,`create_time`) values (10000000,'果啤','1',now());
INSERT INTO `toktok`.`tag_value` (`tag_id`,`tag_value`,`seq`,`create_time`) values (10000000,'白啤','2',now());
INSERT INTO `toktok`.`tag_value` (`tag_id`,`tag_value`,`seq`,`create_time`) values (10000000,'IPA','3',now());
INSERT INTO `toktok`.`tag_value` (`tag_id`,`tag_value`,`seq`,`create_time`) values (10000000,'气泡茶','4',now());
INSERT INTO `toktok`.`tag_value` (`tag_id`,`tag_value`,`seq`,`create_time`) values (10000000,'西打','5',now());

ALTER TABLE `toktok`.`banner`
ADD COLUMN `status` tinyint(3) NULL DEFAULT 1 COMMENT '上下架状态' AFTER `jump_url`;

INSERT INTO `toktok`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '米酷店', '广州市天河区万科云城米酷B4栋107-108铺（近天河智慧城地铁B出口）', NULL, '1', now(), NULL);
INSERT INTO `toktok`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '万胜围店', '广州市海珠区琶洲新村11栋02商铺（近万胜围地铁D出口）', NULL, '1', now(), NULL);
INSERT INTO `toktok`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '杨箕店', '越秀区杨箕大街富力东山新天地F区商业街109铺', NULL, '1', now(), null);

ALTER TABLE `toktok`.`stores`
DROP COLUMN `opt_user`,
ADD COLUMN `status` tinyint(3) NOT NULL DEFAULT 1 COMMENT '上下架状态' AFTER `manager`;

ALTER TABLE `toktok`.`stores`
ADD COLUMN `opening_hours` timestamp(0) NULL COMMENT '开始营业时间' AFTER `manager`,
ADD COLUMN `closing_hours` timestamp(0) NULL COMMENT '结束营业时间' AFTER `opening_hours`;

--------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`banner`
ADD COLUMN `bg_color` varchar(12) NULL COMMENT '背景底色' AFTER `jump_url`;

ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `jump_url` varchar(512) NULL COMMENT '跳转地址' AFTER `business_end_time`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`sku`
ADD COLUMN `type` tinyint(3) NULL COMMENT '商品类型' AFTER `price`,
ADD COLUMN `target_id` bigint(20) NULL COMMENT '类型关联的目标id' AFTER `type`;


ALTER TABLE `toktok_stores`.`cont_content`
ADD COLUMN `product_id` bigint(20) NULL COMMENT '产品id' AFTER `user_id`;

ALTER TABLE `toktok_stores`.`cont_content`
MODIFY COLUMN `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户编号' AFTER `content_id`;

ALTER TABLE `toktok_stores`.`cont_content`
ADD COLUMN `like_count` int(11) NOT NULL DEFAULT 0 COMMENT '点赞数' AFTER `img_urls`;

ALTER TABLE `toktok_stores`.`cont_comment`
MODIFY COLUMN `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '评论用户编号' AFTER `parent_comment_id`,
MODIFY COLUMN `at_user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '评论@用户编号' AFTER `user_id`;

ALTER TABLE `toktok_stores`.`cont_tag`
ADD COLUMN `sort_flag` int(10) NOT NULL DEFAULT 0 COMMENT '排序' AFTER `user_id`,
ADD COLUMN `recommend` tinyint(2) NOT NULL DEFAULT 2 COMMENT '推荐标识' AFTER `sort_flag`,
ADD COLUMN `audit_desc` varchar(255) NULL COMMENT '内容审核描述' AFTER `audit_status`;

ALTER TABLE `toktok_stores`.`cont_tag`
DROP COLUMN `user_id`,
MODIFY COLUMN `tag_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标签名' AFTER `cont_tag_id`,
ADD UNIQUE INDEX `tag_name_index`(`tag_name`);

ALTER TABLE `toktok_stores`.`cont_content`
ADD COLUMN `anonymous` tinyint(3) NOT NULL DEFAULT 1 COMMENT '是否匿名' AFTER `like_count`;

ALTER TABLE `toktok_stores`.`cont_comment`
MODIFY COLUMN `parent_comment_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级评论编号' AFTER `content_id`;

ALTER TABLE `toktok_stores`.`user`
MODIFY COLUMN `nick_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户昵称' AFTER `head_img`,
ADD COLUMN `district_id` bigint(20) NULL COMMENT '地区id' AFTER `gender`;

ALTER TABLE `toktok_stores`.`account_third_wechat`
ADD UNIQUE INDEX `unionId_index`(`unionid`);

ALTER TABLE `toktok_stores`.`cont_content`
ADD COLUMN `ip_address` varchar(20) NULL COMMENT '发布内容的ip物理地址' AFTER `anonymous`;

ALTER TABLE `toktok_stores`.`cont_comment`
ADD COLUMN `ip_address` varchar(20) NULL COMMENT '发布内容的ip物理地址' AFTER `like_count`;


ALTER TABLE `toktok_stores`.`cont_content`
MODIFY COLUMN `ip_address` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '发布内容的ip物理地址' AFTER `anonymous`;

ALTER TABLE `toktok_stores`.`cont_comment`
MODIFY COLUMN `ip_address` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '发布内容的ip物理地址' AFTER `like_count`;

ALTER TABLE `toktok_stores`.`message`
CHANGE COLUMN `type` `src_type` tinyint(3) NOT NULL DEFAULT 1 COMMENT '消息对应的内容来源' AFTER `message_id`,
ADD COLUMN `src_id` bigint(20) NULL COMMENT '内容来源id' AFTER `src_type`,
ADD COLUMN `action` tinyint(3) NOT NULL COMMENT '动作(点赞，评论...)' AFTER `src_id`,
MODIFY COLUMN `jump_path` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '跳转地址' AFTER `content`;

ALTER TABLE `toktok_stores`.`message`
MODIFY COLUMN `action` tinyint(3) NOT NULL DEFAULT 1 COMMENT '动作(点赞，评论...)' AFTER `src_id`,
MODIFY COLUMN `user_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '用户编号' AFTER `action`,
ADD COLUMN `sender_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '发送消息的用户' AFTER `user_id`;


ALTER TABLE `toktok_stores`.`report`
ADD COLUMN `target_type` tinyint(3) NOT NULL COMMENT '目标类型' AFTER `report_type`;

ALTER TABLE `toktok_stores`.`report`
ADD COLUMN `img_urls` varchar(1024) NULL COMMENT '图片证据' AFTER `reason`;

update user set user_status = 1

ALTER TABLE `toktok_stores`.`user`
MODIFY COLUMN `user_status` tinyint(3) NOT NULL DEFAULT 1 COMMENT '用户状态' AFTER `wx_open_id`;

ALTER TABLE `toktok_stores`.`banner`
ADD COLUMN `type` tinyint(3) NOT NULL DEFAULT 1 COMMENT 'banner类型' AFTER `bg_color`;

ALTER TABLE `toktok_stores`.`cont_content`
MODIFY COLUMN `img_urls` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '图片列表' AFTER `content`;

ALTER TABLE `toktok_stores`.`report`
ADD COLUMN `audit_desc` varchar(255) NULL COMMENT '审核描述' AFTER `report_status`;

INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ( 'toktok_ios_version', '1', 'toktok商城ios应用最新版本', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ( 'toktok_android_version', '1', 'toktok商城安卓应用最新版本', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ( 'toktok_ios_min_version', '1', 'toktok商城ios应用最小版本', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ( 'toktok_android_min_version', '1', 'toktok商城安卓应用最小版本', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ( 'toktok_ios_update_url', 'http://www.baidu.com', '修复了一些已知问题', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ( 'toktok_android_update_url', 'http://www.baidu.com', '修复了一些已知问题', now(), NULL);

ALTER TABLE `toktok_stores`.`cont_content`
ADD COLUMN `view_count` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '观看数' AFTER `like_count`;