java-bbc/db/2024-08-27 附近功能.sql

USE yami_bbc;

更新附近功能，由于秒杀的表结构有较大改动，要先失效所有已创建的秒杀活动。
失效秒杀活动后，删除这两句话，直接执行该文件中的sql即可


# 平台菜单
ALTER TABLE `tz_sys_menu`
    ADD COLUMN `router_name` VARCHAR(100) DEFAULT NULL COMMENT '路由名称';

# 商家菜单
ALTER TABLE `tz_shop_menu`
    ADD COLUMN `router_name` VARCHAR(100) DEFAULT NULL COMMENT '路由名称';

# 修改权益优惠券字段类型
ALTER TABLE `tz_user_balance_coupon`
    MODIFY COLUMN `coupon_num` int(0) NULL DEFAULT NULL COMMENT '优惠券数量' AFTER `coupon_id`;


ALTER TABLE `tz_station`
    ADD COLUMN `self_pickup` tinyint DEFAULT NULL COMMENT '自提门店用途是否支持自提（0：不支持，1：支持）',
    ADD COLUMN `same_city_delivery` tinyint DEFAULT NULL COMMENT '自提门店用途是否支持同城配送（0：不支持，1：支持）',
    ADD COLUMN  `station_score` decimal(15,2) DEFAULT NULL COMMENT '自提门店评分',
    MODIFY COLUMN `pic` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '自提点主图片' AFTER `station_name`,
    ADD COLUMN `img_urls` varchar(1000) NULL COMMENT '自提点图片，多张以逗号隔开' AFTER `pic`;
# 门店默认评分5.00
update `tz_station` set station_score = 5.00;
UPDATE `tz_station` SET self_pickup = 1, same_city_delivery = 0;


TRUNCATE `tz_same_city`;
ALTER TABLE `tz_same_city`
    CHANGE COLUMN `shop_id` `station_id` bigint(0) NULL DEFAULT NULL COMMENT '门店id',
    DROP COLUMN `province_id`,
    DROP COLUMN `province`,
    DROP COLUMN `city_id`,
    DROP COLUMN `city`,
    DROP COLUMN `area_id`,
    DROP COLUMN `area`,
    DROP COLUMN `shop_address`,
    DROP INDEX `idx_shop_id`,
    ADD INDEX `idx_station_id`(`station_id`) USING BTREE,
    MODIFY COLUMN `position_ids` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '配送区域经纬度json' AFTER `station_id`;

# 区域库存添加门店配送方式
ALTER TABLE `tz_stock_point_sku`
    ADD COLUMN `self_pickup` TINYINT DEFAULT NULL COMMENT '自提门店用途是否支持自提（0：不支持，1：支持）',
    ADD COLUMN `same_city_delivery` TINYINT DEFAULT NULL COMMENT '自提门店用途是否支持同城配送（0：不支持，1：支持）';


CREATE TABLE `tz_station_prod` (
                                   `station_prod_id` bigint NOT NULL AUTO_INCREMENT COMMENT '门店商品id',
                                   `station_id` bigint DEFAULT NULL COMMENT '门店id',
                                   `prod_id` bigint DEFAULT NULL COMMENT '店铺商品id',
                                   `has_user_pick_up` int DEFAULT NULL COMMENT '是否支持自提（0不支持，1支持）',
                                   `has_city_delivery` int DEFAULT NULL COMMENT '是否支持同城（0不支持，1支持）',
                                   `sold_num` int DEFAULT '0' COMMENT '门店商品销量',
                                   `comm_num` int DEFAULT '0' COMMENT '门店商品评价数量',
                                   `lng` decimal(16,2) DEFAULT NULL COMMENT '门店所在经度',
                                   `lat` decimal(16,2) DEFAULT NULL COMMENT '门店所在纬度',
                                   `status` int DEFAULT NULL COMMENT '状态状态（-1:删除、0:商家下架、1:上架、2:违规下架、3:平台审核）',
                                   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
                                   `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                   PRIMARY KEY (`station_prod_id`),
                                   UNIQUE KEY `un_idx_station_id_prod_id` (`station_id`,`prod_id`) USING BTREE,
                                   KEY `idx_station_id` (`station_id`) USING BTREE,
                                   KEY `idx_prod_id` (`prod_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=460 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='门店商品关联数据';

ALTER TABLE `tz_prod_comm`
    ADD COLUMN `station_id` bigint NULL COMMENT '门店id' AFTER `shop_id`;

# 保存默认的首页开关配置
INSERT INTO `tz_sys_config`(`param_key`, `param_value`, `remark`)
VALUES ('HOME_PAGE_CONFIG', '{\"home\":\"1\",\"nearby\":\"1\"}', '首页开关配置');

INSERT INTO `tz_shop_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`)
VALUES (746, 742, 'stock/stowage/admin-station/add-or-update', '', 1, '', 0, 1),
       (767,203,'/order/virtual-info-log/index','virtual:verify:page', 1, '', 0, 0),
       (768,767,'', 'virtual:verify:export', 2, '', 0, 0);

INSERT INTO `tz_shop_menu_lang`(`menu_id`, `lang`, `name`)
VALUES (746, 0, '新增门店'),
       (746, 1, 'New store'),
       (767,'0','核销记录'),
       (767,'1','Verification record'),
       (768,'0', '导出'),
       (768,'1', 'export');;

ALTER TABLE `tz_user_collection_shop`
    ADD COLUMN `station_id` bigint NULL COMMENT '门店id' AFTER `shop_id`,
ADD INDEX `k_station_id`(`station_id`) USING BTREE,
COMMENT = '用户店铺/门店收藏记录';

ALTER TABLE `tz_shop_template`
    ADD COLUMN `page_type` int(0)  DEFAULT 0 COMMENT '页面类型（移动端装修才有的类型）：0:首页、1:附近页、2:活动专题页' AFTER `remark`;
ALTER TABLE `tz_shop_renovation`
    ADD COLUMN `page_type` int(0)  DEFAULT 0 COMMENT '页面类型（移动端装修才有的类型）：0:首页、1:附近页、2:活动专题页' AFTER `renovation_type`;

ALTER TABLE `tz_basket`
    DROP INDEX `uk_user_shop_sku_combo`,
    ADD COLUMN `station_id` bigint NULL COMMENT '门店ID' AFTER `shop_id`;

UPDATE `tz_basket` SET `station_id` = 0;

ALTER TABLE `tz_basket`
ADD UNIQUE INDEX `uk_user_shop_station_sku_combo`(`sku_id`, `user_id`, `shop_id`, `station_id`, `combo_id`, `parent_basket_id`) USING BTREE,
ADD INDEX `station_id`(`station_id`) USING BTREE;


ALTER TABLE `tz_seckill_stock_point_sku`
    ADD COLUMN `self_pickup` TINYINT DEFAULT NULL COMMENT '自提门店用途是否支持自提（0：不支持，1：支持）',
    ADD COLUMN `same_city_delivery` TINYINT DEFAULT NULL COMMENT '自提门店用途是否支持同城配送（0：不支持，1：支持）';

CREATE TABLE `tz_station_extension` (
                                        `id` bigint NOT NULL AUTO_INCREMENT COMMENT '门店扩展信息表id',
                                        `station_id` bigint DEFAULT NULL COMMENT '门店id',
                                        `collection_num` bigint DEFAULT NULL COMMENT '门店收藏数量',
                                        `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
                                        `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                        PRIMARY KEY (`id`),
                                        KEY `idx_station_id` (`station_id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='门店扩展信息表';

INSERT INTO tz_station_extension(station_id,collection_num)
SELECT station_id,0 FROM tz_station;

# 移除同城配送菜单
DELETE FROM tz_shop_menu_lang WHERE menu_id = 39;
DELETE FROM tz_shop_menu_lang WHERE menu_id IN (SELECT menu_id FROM tz_shop_menu WHERE parent_id = 39);
DELETE FROM tz_shop_menu WHERE menu_id = 39;
DELETE FROM tz_shop_menu WHERE parent_id = 39;

# 店铺钱包记录
ALTER TABLE `tz_shop_wallet_log`
    ADD COLUMN `stock_point_id` BIGINT DEFAULT NULL COMMENT '区域库存id';

# 初始化区域库存数据
UPDATE `tz_stock_point_sku` p
JOIN `tz_station` s ON s.station_id = p.stock_point_id AND p.stock_point_type = 2 AND p.self_pickup IS NULL
SET p.self_pickup = s.self_pickup, p.same_city_delivery = s.same_city_delivery;


将旧的门店数据插入到es中定时任务只需执行一次即可
USE `xxl_job`;
INSERT INTO `xxl_job_info`(`job_group`, `job_desc`, `add_time`, `update_time`, `author`, `alarm_email`, `schedule_type`, `schedule_conf`, `misfire_strategy`, `executor_route_strategy`, `executor_handler`, `executor_param`, `executor_block_strategy`, `executor_timeout`, `executor_fail_retry_count`, `glue_type`, `glue_source`, `glue_remark`, `glue_updatetime`, `child_jobid`, `trigger_status`, `trigger_last_time`, `trigger_next_time`)
VALUES
    (2, '将旧的门店数据插入到es中', '2024-06-20 14:39:40', '2024-06-20 14:39:40', 'admin', '', 'CRON', '0 * * *  * ? *', 'DO_NOTHING', 'FIRST', 'syncOldStationSaveEs', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2024-06-20 14:39:40', '', 0, 0, 0),
    (2, '计算门店评分', '2024-06-20 14:39:05', '2024-06-20 14:39:05', 'admin', '', 'CRON', '0 * * *  * ? *', 'DO_NOTHING', 'FIRST', 'computeStationScore', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2024-06-20 14:39:05', '', 0, 0, 0),
    (2, '将店铺支持自提或者同城配送的商品。保存一份门店关联商品数据到es和mysql', '2024-06-25 15:38:32', '2024-06-25 15:38:32', 'admin', '', 'CRON', '0 * * *  * ? *', 'DO_NOTHING', 'FIRST', 'synchStationSpuSaveEs', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2024-06-25 15:38:32', '', 0, 0, 0);