qimall/addons/ScoreExpansion/install.sql

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

CREATE TABLE `qimall_addons_score_expansion_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',
    `integral_wallet_type` varchar(20)             DEFAULT '' COMMENT '积分类型[frozen_rintegral:冻结积分，integral:激活积分]',
    `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',
    `is_deduct_percent`    tinyint(4) DEFAULT '0' COMMENT '抵扣是否百分比[0=否，1=是]',
    PRIMARY KEY (`id`) USING BTREE,
    KEY                    `mall_id+goods_id` (`mall_id`,`goods_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='积分商品赠送抵扣设置';

CREATE TABLE `qimall_addons_score_expansion_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) 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,
    KEY          `mall_id` (`mall_id`) USING BTREE,
    KEY          `rid+user_id+parent_id` (`rid`,`user_id`,`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='分享邀请好友记录';

CREATE TABLE `qimall_addons_score_expansion_log`
(
    `id`              int(11) unsigned NOT NULL AUTO_INCREMENT,
    `mall_id`         int(11) NOT NULL COMMENT '商城id',
    `user_id`         int(11) NOT NULL COMMENT '用户id',
    `point_type`      varchar(20)    NOT NULL COMMENT '积分类型',
    `from_type`       varchar(255)   NOT NULL COMMENT '来原类型',
    `point_num`       decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '积分数量',
    `order_sn`        varchar(255)   NOT NULL COMMENT '订单编号',
    `change_type`     char(12)       NOT NULL COMMENT '操作类型：add-增加，sub-减少',
    `change_num`      decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '操作数量',
    `after_num`       decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '变动后数量',
    `source_table`    varchar(255)   NOT NULL COMMENT '来源表',
    `source_table_id` int(11) NOT NULL COMMENT '操作来源业务id',
    `status`          tinyint(4) DEFAULT '1' COMMENT '状态，-1：失效，1：正常，2：冻结',
    `desc`            varchar(255)   NOT NULL COMMENT '变动说明',
    `created_at`      int(11) NOT NULL COMMENT '创建时间',
    `updated_at`      int(11) NOT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='积分拓客记录表';

CREATE TABLE `qimall_addons_score_expansion_rate_task`
(
    `id`         int(11) unsigned NOT NULL AUTO_INCREMENT,
    `order_id`   int(11) NOT NULL COMMENT '订单id',
    `is_work`    tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否执行过 1是 0否',
    `day`        varchar(255) DEFAULT NULL COMMENT '执行时间',
    `updated_at` int(11) DEFAULT NULL,
    `created_at` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='过期未评价定时任务记录表';

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

CREATE TABLE `qimall_addons_score_expansion_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_score_expansion.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,
    KEY              `mall_id` (`mall_id`) USING BTREE,
    KEY              `rid+user_id+date` (`rid`,`user_id`,`date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户抢积分每日总计表';

CREATE TABLE `qimall_addons_score_expansion_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,
    KEY          `mall_id+rid+user_id` (`mall_id`,`rid`,`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户抢积分记录表';

CREATE TABLE `qimall_addons_score_expansion_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',
    `integral`                  decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '激活积分',
    `frozen_integral`           decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '冻结积分',
    `total_integral`            decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '总激活积分',
    `total_frozen_integral`     decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '总冻结积分',
    `total_use_integral`        decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '已使用激活积分',
    `total_use_frozen_integral` decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '已使用冻结积分',
    `status`                    tinyint(1) 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,
    KEY                         `idx_uid` (`mall_id`,`user_id`) USING BTREE,
    KEY                         `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='积分钱包表';

CREATE TABLE `qimall_addons_score_expansion_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',
    `integral_wallet_type` varchar(20)    NOT NULL DEFAULT '' COMMENT '积分类型[frozen_integral:冻结积分，integral:激活积分]',
    `change_type`          char(12)       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)   NOT NULL DEFAULT '' COMMENT '变动说明',
    `source_table`         varchar(255)   NOT NULL DEFAULT '' COMMENT '来源表',
    `source_table_id`      int(11) unsigned NOT NULL DEFAULT '0' COMMENT '操作来源业务id',
    `order_no`             varchar(255)            DEFAULT NULL COMMENT '订单编号',
    `from_type`            varchar(255)   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 '更新时间戳',
    `addon_name`           varchar(64)    NOT NULL DEFAULT 'ScoreExpansion' COMMENT '来源插件名称',
    PRIMARY KEY (`id`) USING BTREE,
    KEY                    `idx_mall_user` (`mall_id`,`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='红包钱包记录表';


INSERT INTO `qimall_global_var_map` (`name`, `title`, `type`, `is_addons`, `status`, `created_at`,
                                              `updated_at`)
VALUES ('RandomRedPacket', '随机红包', 'FromTypeMap', 1, 1, 1654569888, 1654569888);


INSERT INTO `qimall_global_var_map` (`name`, `title`, `type`, `is_addons`, `status`, `created_at`,
                                              `updated_at`)
VALUES ('ScoreExpansion', '积分拓客', 'FromTypeMap', 1, 1, 1654569888, 1654569888);



INSERT INTO `qimall_crontab_task` (`name`, `command`, `type`, `rule`, `is_warning`, `status`,
                                            `created_at`, `updated_at`)
VALUES ('积分拓客：冻结积分自动转激活积分', 'score-expansion/score-expansion/frozen-to-active', 2, '0 */30 * * * *',
        0, 1, 1678448518, 1679374119);