LJH_Shop/niucloud/addon/mall/app/upgrade/v002/upgrade.sql

CREATE TABLE `mall_active`  (
    `active_id` int NOT NULL AUTO_INCREMENT COMMENT '活动id',
    `site_id` int NOT NULL DEFAULT 0 COMMENT '站点id',
    `active_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动名称',
    `active_desc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '活动说明',
    `active_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动类型(店铺活动，会员活动，商品活动)',
    `active_goods_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '商品活动类型（单品，独立商品，店铺整体商品）',
    `active_goods_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '参与活动商品信息',
    `active_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动类别',
    `active_class_category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动类别子分类（活动管理）',
    `relate_member` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '参与会员条件(默认全部)',
    `active_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '活动扩展信息数据',
    `start_time` int NOT NULL DEFAULT 0 COMMENT '活动开始时间',
    `end_time` int NOT NULL DEFAULT 0 COMMENT '活动结束时间',
    `active_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动状态',
    `create_time` int NOT NULL DEFAULT 0 COMMENT '添加时间',
    `update_time` int NOT NULL DEFAULT 0 COMMENT '修改时间',
    `active_order_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '活动累计金额',
    `active_order_num` int NOT NULL DEFAULT 0 COMMENT '活动累计订单数',
    `active_member_num` int NOT NULL DEFAULT 0 COMMENT '活动参与会员数',
    `active_success_num` int NOT NULL DEFAULT 0 COMMENT '活动成功参与会员数',
    PRIMARY KEY (`active_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '店铺营销活动表（整体活动）' ROW_FORMAT = Dynamic;

CREATE TABLE `mall_active_goods`  (
  `active_goods_id` int NOT NULL AUTO_INCREMENT COMMENT '活动商品id',
  `active_id` int NOT NULL DEFAULT 0 COMMENT '活动id',
  `site_id` int NOT NULL DEFAULT 0 COMMENT '站点id',
  `goods_id` int NOT NULL DEFAULT 0 COMMENT '商品id',
  `active_goods_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '商品活动类型（单品，独立商品，店铺整体商品）',
  `active_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '商品活动类别',
  `active_goods_label` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动商品标签（针对活动有标签）',
  `active_goods_category` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动商品分类（针对活动有分类）',
  `active_goods_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '活动商品信息数据',
  `active_goods_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动状态',
  `active_goods_point` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '活动商品积分（展示，搜索）',
  `active_goods_price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '活动商品价格（展示，搜索）',
  `active_goods_stock` int NOT NULL DEFAULT 0 COMMENT '活动商品库存（针对参与库存）',
  `active_goods_order_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '活动累计金额',
  `active_goods_order_num` int NOT NULL DEFAULT 0 COMMENT '活动累计订单数',
  `active_goods_member_num` int NOT NULL DEFAULT 0 COMMENT '活动参与会员数',
  `active_goods_success_num` int NOT NULL DEFAULT 0 COMMENT '活动成功参与会员数',
  PRIMARY KEY (`active_goods_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '店铺营销活动' ROW_FORMAT = Dynamic;

ALTER TABLE `mall_coupon` ADD COLUMN `join_site_ids` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '支持使用的站点';

ALTER TABLE `mall_coupon` ADD COLUMN `is_all_site_join` int NOT NULL DEFAULT 0 COMMENT '是否支持全部站点';

ALTER TABLE `mall_coupon_goods` ADD COLUMN `brand_id` int NOT NULL DEFAULT 0 COMMENT '商品品牌id';

ALTER TABLE `mall_coupon_goods` ADD COLUMN `mall_category_id` int NOT NULL DEFAULT 0 COMMENT '平台分类id';

ALTER TABLE `mall_goods` ADD COLUMN `mall_attr_id` int NOT NULL DEFAULT 0 COMMENT '平台商品参数id';

ALTER TABLE `mall_goods` ADD COLUMN `shop_attr_id` int NOT NULL DEFAULT 0 COMMENT '店铺商品参数id';

ALTER TABLE `mall_goods` ADD COLUMN `attr_format` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品参数内容，json格式';

ALTER TABLE `mall_goods` ADD COLUMN `is_discount` int NOT NULL DEFAULT 0 COMMENT '是否参与限时折扣';

ALTER TABLE `mall_goods` ADD COLUMN `member_discount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '会员等级折扣，不参与：空，会员折扣：discount，指定会员价：fixed_price';

ALTER TABLE `mall_goods` ADD COLUMN `virtual_auto_delivery` tinyint NOT NULL DEFAULT 0 COMMENT '虚拟商品是否自动发货';

ALTER TABLE `mall_goods` ADD COLUMN `virtual_receive_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'artificial' COMMENT '虚拟商品收货方式，auto：自动收货，artificial：买家确认收货，verify：到店核销';

ALTER TABLE `mall_goods` ADD COLUMN `virtual_verify_type` tinyint NOT NULL DEFAULT 0 COMMENT '虚拟商品核销有效期类型，0：不限，1：购买后几日有效，2：指定过期日期';

ALTER TABLE `mall_goods` ADD COLUMN `virtual_indate` int NOT NULL DEFAULT 0 COMMENT '虚拟到期时间';

CREATE TABLE `mall_goods_attr`  (
    `attr_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `site_id` int NOT NULL DEFAULT 0,
    `attr_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
    `attr_value_format` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `sort` int NOT NULL DEFAULT 0,
    PRIMARY KEY (`attr_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '商品参数表' ROW_FORMAT = Dynamic;

ALTER TABLE `mall_goods_sku` ADD COLUMN `member_price` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '会员价，json格式，指定会员价，数据结构为：{\"level_1\":\"10.00\",\"level_2\":\"10.00\"}';

ALTER TABLE `mall_order` ADD COLUMN `mall_discount_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '平台优惠金额';

ALTER TABLE `mall_order` ADD COLUMN `mall_coupon_money` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '平台优惠券补贴';

ALTER TABLE `mall_order` ADD COLUMN `mall_refund_coupon_money` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '返还平台优惠券补贴';

ALTER TABLE `mall_order_goods` ADD COLUMN `verify_count` int NOT NULL DEFAULT 0 COMMENT '已核销次数';

ALTER TABLE `mall_order_goods` ADD COLUMN `verify_expire_time` int NOT NULL DEFAULT 0 COMMENT '过期时间 0 为永久';

ALTER TABLE `mall_order_goods` ADD COLUMN `is_verify` int NOT NULL DEFAULT 0 COMMENT '是否需要核销';

ALTER TABLE `mall_order_goods` ADD COLUMN `mall_discount_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '平台优惠';

CREATE TABLE `mall_order_group_discount`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `site_ids` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '站点ids',
  `order_ids` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '订单ids',
  `group_id` int NOT NULL DEFAULT 0 COMMENT '订单组id',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '类型 discount 优惠，gift 赠送',
  `num` int NOT NULL DEFAULT 0 COMMENT '使用数量',
  `money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额',
  `discount_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '优惠类型',
  `discount_type_id` int NOT NULL DEFAULT 0 COMMENT '优惠类型id',
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '订单优惠说明',
  `create_time` int NOT NULL DEFAULT 0 COMMENT '创建时间',
  `status` int NOT NULL DEFAULT 1 COMMENT '状态',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单组优惠表' ROW_FORMAT = Dynamic;

ALTER TABLE `mall_order_refund` ADD COLUMN `is_refund_delivery` int NOT NULL DEFAULT 0 COMMENT '是否退运费';