qimall/addons/Redpack/install.sql

CREATE TABLE `qimall_addons_redpack`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID',
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动标题',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动描述',
  `cover` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '活动封面',
  `award` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '总奖金',
  `virtual_award` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '虚拟奖金',
  `day_award` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '每日总奖金,对应redpack_type=1',
  `grab_user_num` int(10) NOT NULL DEFAULT 0 COMMENT '已抢红包总人数',
  `grab_money` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '已抢红包总金额',
  `surplus_money` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '剩余红包总金额,redpack_type=2时候存数据',
  `data_at` int(10) NULL DEFAULT 0 COMMENT '数据更新时间',
  `time_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动时间类型[1:每日红包,2:时间段红包]',
  `start_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动开始时间',
  `end_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '活动结束时间',
  `redpack_type` tinyint(2) NOT NULL DEFAULT 0 COMMENT '红包活动类型[1:冻结到激活红包，2:增值冻结红包，3:增值激活红包]',
  `rule_values` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '规则条件的值集合',
  `auth` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '参与权限',
  `rule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '规则说明',
  `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `limit_user_level` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '会员限制',
  `created_at` int(10) NOT NULL DEFAULT 0,
  `updated_at` int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `start_time`(`start_time`) USING BTREE,
  INDEX `end_time`(`end_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '红包活动表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_goods_setting`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL COMMENT '商城ID',
  `goods_id` int(11) NOT NULL DEFAULT 0 COMMENT '商品id',
  `redpack_wallet_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '红包类型[frozen_redpack:冻结红包，redpack:激活红包]',
  `is_enable` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否开启：0:否，1是',
  `is_percent` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否百分比：0:否(固定金额)，1是',
  `give` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '赠送红包金额',
  `deduct_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '商品抵扣类型[1:单件抵扣，2:多件抵扣]',
  `deduct_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '商品抵扣金额',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id+goods_id`(`mall_id`, `goods_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '红包商品赠送抵扣设置' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_invite`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL COMMENT '商城ID',
  `rid` int(11) NOT NULL DEFAULT 0 COMMENT 'qimall_addons_redpack.id',
  `user_id` int(11) NOT NULL DEFAULT 0 COMMENT '被分享人用户id',
  `parent_id` int(11) NOT NULL DEFAULT 0 COMMENT '上级用户id',
  `is_valid` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效[0:无效，1:有效]',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `rid+user_id+parent_id`(`rid`, `user_id`, `parent_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '分享邀请好友记录' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_task`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '保留执行过那些商场[1,2,3,4]',
  `day` date NULL DEFAULT NULL COMMENT '执行时间',
  `status` int(1) NOT NULL DEFAULT 1 COMMENT '0是禁用，1是启用，-1删除',
  `created_at` int(11) NULL DEFAULT NULL,
  `updated_at` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '定时任务记录' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_user_count_day`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL COMMENT '商城ID',
  `rid` int(10) NOT NULL DEFAULT 0 COMMENT 'qimall_addons_redpack.id',
  `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '用户ID',
  `grab_num` int(10) NOT NULL DEFAULT 0 COMMENT '可抢红包次数',
  `grab_num_total` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '已抢红包总次数',
  `inviter_num` tinyint(2) NOT NULL DEFAULT 0 COMMENT '已邀请人数',
  `date` int(10) NOT NULL DEFAULT 0 COMMENT '时间，如220316',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `rid+user_id+date`(`rid`, `user_id`, `date`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户抢红包每日总计表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_user_grab`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL COMMENT '商城ID',
  `rid` int(10) NOT NULL DEFAULT 0 COMMENT 'qimall_addons_redpack.id',
  `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '用户ID',
  `grab_num` int(10) NOT NULL DEFAULT 0 COMMENT '抢红包次数',
  `grab_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '抢红包总金额',
  `grab_time` int(10) NOT NULL DEFAULT 0 COMMENT '最后抢红包时间',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id+rid+user_id`(`mall_id`, `rid`, `user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户抢红包记录表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_wallet`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城id',
  `user_id` int(11) NOT NULL DEFAULT 0 COMMENT '会员id',
  `redpack` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '激活红包',
  `frozen_redpack` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '冻结红包',
  `total_redpack` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '总激活红包',
  `total_frozen_redpack` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '总冻结红包',
  `total_use_redpack` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '已使用激活红包',
  `total_use_frozen_redpack` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '已使用冻结红包',
  `status` tinyint(1) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_uid`(`mall_id`, `user_id`) USING BTREE,
  INDEX `idx_user_id`(`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '红包钱包表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_redpack_wallet_log`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `user_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `redpack_wallet_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '红包类型[frozen_redpack:冻结红包，redpack:激活红包]',
  `change_type` char(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '操作类型：add-增加，sub-减少',
  `change_num` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '操作数量',
  `after_num` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '变动后数量',
  `desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '变动说明',
  `source_table` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '来源表',
  `source_table_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '操作来源业务id',
  `from_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '来源类型',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态，-1：失效，1：正常，2：冻结',
  `created_at` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '添加时间戳',
  `updated_at` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间戳',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_mall_user`(`mall_id`, `user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '红包钱包记录表' ROW_FORMAT = Dynamic;

INSERT INTO `qimall_crontab_task`(`name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ('红包拓客：冻结红包自动转激活红包', 'redpack/redpack/frozen-to-active', 2, '0 */30 * * * *', 0, 1, 1648107496, 1648198381);