java-bbc/db/2024-11-05 门店PC端.sql

USE yami_bbc;

# 店铺账号添加门店id
ALTER TABLE `tz_shop_employee`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
DROP INDEX `username`,
ADD INDEX `username`(`username`) USING BTREE;

# tz_station中的门店账号，转移到tz_shop_employee中
INSERT INTO tz_shop_employee(shop_id, station_id, type, username, nickname, `password`, `status`, create_time)
SELECT
	shop_id, station_id, 0, account username, account nickname, `password`, 1, NOW()
FROM
	tz_station
WHERE
	`status` != -1
	AND account IS NOT NULL;

# 店铺角色添加门店id
ALTER TABLE `tz_shop_role`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 店铺钱包日志添加门店id
ALTER TABLE `tz_shop_wallet_log`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id,为0时是在店铺下单' AFTER `shop_id`;

# 公告添加门店id
ALTER TABLE `tz_notice`
ADD COLUMN `station_ids` varchar(500) NULL COMMENT '指定门店ids(为空则全部门店)' AFTER `user_ids`;

# 商家菜单添加系统类型
ALTER TABLE `tz_shop_menu`
ADD COLUMN `sys_type` tinyint(0) NOT NULL COMMENT '系统类型 1.商家端 3.门店端' AFTER `router_name`;

# 库存流水记录添加门店id
ALTER TABLE `tz_stock_bill_log`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 库存变更原因添加门店id
ALTER TABLE `tz_stock_change_reason`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 门店添加字段
ALTER TABLE `tz_station`
ADD COLUMN `custom_prod` tinyint(0) NULL DEFAULT 0 COMMENT '门店自定义商品 1.可以 0.不可以' AFTER `station_score`,
ADD COLUMN `custom_marking` tinyint(0) NULL DEFAULT 0 COMMENT '门店自定义营销活动 1.可以 0.不可以' AFTER `custom_prod`;

# 限时折扣添加字段

# 店铺账号添加门店id
ALTER TABLE `tz_shop_customer`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

ALTER TABLE `tz_order`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

ALTER TABLE `tz_order_refund`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

ALTER TABLE `tz_prod`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
    ADD COLUMN `prod_belongs` tinyint(0) NULL DEFAULT 0 COMMENT '商品归属 0.品牌商品 1.门店商品';

ALTER TABLE `tz_prod_prop`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

ALTER TABLE `tz_form`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

ALTER TABLE `tz_attach_file`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

ALTER TABLE `tz_attach_file_group`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 限时折扣添加门店id
ALTER TABLE `tz_discount`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
ADD COLUMN `suitable_station_type` tinyint(0) NULL DEFAULT 0 COMMENT '使用门店类型(0:全部门店, 1:指定门店)';

# 创建折扣-门店关联表
CREATE TABLE `tz_discount_station` (
  `discount_station_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `discount_id` bigint NULL COMMENT '折扣活动id',
  `station_id` bigint NULL COMMENT '门店id',
  PRIMARY KEY (`discount_station_id`),
  UNIQUE INDEX `discount_station_idx`(`discount_id`, `station_id`) USING BTREE
);

# 调拨订单修改
ALTER TABLE `tz_allot_order`
DROP COLUMN `sys_type`,
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 调拨订单项修改
ALTER TABLE `tz_allot_order_item`
ADD COLUMN `allot_out_count` int(0) NULL COMMENT '调拨出库数量' AFTER `allot_in_count`;

# 实物盘点修改
ALTER TABLE `tz_take_stock`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 拼团活动修改
ALTER TABLE `tz_group_activity`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
ADD COLUMN `suitable_station_type` tinyint(0) NULL DEFAULT 0 COMMENT '适用门店类型(0:全部门店 1:指定门店)' AFTER `prod_id`;


# 拼团活动-门店关联表
CREATE TABLE `tz_group_station`  (
  `group_station_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `group_activity_id` bigint NULL COMMENT '拼团活动id',
  `station_id` bigint NULL COMMENT '门店id',
  PRIMARY KEY (`group_station_id`),
  UNIQUE INDEX `group_station_idx`(`group_station_id`, `group_activity_id`) USING BTREE
);

# 秒杀活动修改
ALTER TABLE `tz_seckill`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 优惠券修改
ALTER TABLE `tz_coupon`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
ADD COLUMN `suitable_station_type` tinyint(0) NULL DEFAULT 0 COMMENT '使用门店类型(0:全部门店, 1:指定门店)' AFTER `suitable_prod_type`;

# 优惠券-门店关联表
CREATE TABLE `tz_coupon_station` (
  `coupon_station_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `coupon_id` bigint(0) NULL DEFAULT NULL COMMENT '优惠券id',
  `station_id` bigint(0) NULL DEFAULT NULL COMMENT '门店id',
  PRIMARY KEY (`coupon_station_id`) USING BTREE,
  UNIQUE INDEX `coupon_station_idx`(`coupon_id`, `station_id`) USING BTREE
);

# 赠品活动修改
ALTER TABLE `tz_giveaway`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
ADD COLUMN `suitable_station_type` tinyint(0) NULL DEFAULT 0 COMMENT '适用门店类型(0:全部门店 1:指定门店)' AFTER `prod_id`;

CREATE TABLE `tz_giveaway_station` (
    `giveaway_station_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
    `giveaway_id` BIGINT DEFAULT NULL COMMENT '赠品id',
    `station_id` BIGINT DEFAULT NULL COMMENT '门店id',
    PRIMARY KEY (`giveaway_station_id`),
    UNIQUE KEY `giveaway_station_idx` (`giveaway_id`,`station_id`) USING BTREE
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT ='赠品门店关联表';

# 分销商品活动修改
ALTER TABLE `tz_distribution_prod`
    ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 套餐修改
ALTER TABLE `tz_combo`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`,
ADD COLUMN `suitable_station_type` tinyint(1) NULL DEFAULT 0 COMMENT '使用门店类型(0:全部门店, 1:指定门店)' AFTER `status`;

# 套餐-门店关联表
CREATE TABLE `tz_combo_station` (
  `combo_station_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `combo_id` bigint NULL COMMENT '套餐id',
  `station_id` bigint NULL COMMENT '门店id',
  PRIMARY KEY (`combo_station_id`),
  UNIQUE INDEX `combo_station_idx`(`combo_id`, `station_id`) USING BTREE
);

# 直播间修改
ALTER TABLE `tz_live_room`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '门店id' AFTER `shop_id`;

# 下线事件修改
ALTER TABLE `tz_offline_handle_event`
ADD COLUMN `station_id` bigint(0) NULL DEFAULT 0 COMMENT '关联门店id' AFTER `shop_id`;