mall-backend-admin/sql/mysql/建空库SQL/23_20240822.sql

ALTER TABLE distri_integral
    ADD COLUMN `consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '当前消费分';

CREATE TABLE `distri_consumption_top_up_log` (
                                                 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '充值编号',
                                                 `user_id` bigint DEFAULT NULL COMMENT '用户ID',
                                                 `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
                                                 `attachment` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '转账凭证附件',
                                                 `consumption_points` bigint NOT NULL COMMENT '消费分',
                                                 `practical_consumption_points` bigint NOT NULL COMMENT '实际消费分',
                                                 `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
                                                 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                                 `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
                                                 `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                                 PRIMARY KEY (`id`) USING BTREE,
                                                 KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='平台消费分充值记录';

CREATE TABLE `distri_consumption_change_log` (
                                                 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志编号',
                                                 `user_id` bigint DEFAULT NULL COMMENT '用户ID',
                                                 `consumption_points` bigint NOT NULL COMMENT '变动的消费分',
                                                 `after_consumption_points` bigint NOT NULL COMMENT '变动后消费分',
                                                 `practical_consumption_points` bigint DEFAULT NULL COMMENT '实际消费分',
                                                 `consumption_status` tinyint DEFAULT NULL COMMENT '消费分变动类型',
                                                 `generate_user_id` bigint DEFAULT NULL COMMENT '消费分产生对象',
                                                 `order_id` bigint DEFAULT NULL COMMENT '订单Id',
                                                 `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '订单编号',
                                                 `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
                                                 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                                 `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
                                                 `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                                 PRIMARY KEY (`id`) USING BTREE,
                                                 KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='消费分变动记录';

ALTER TABLE distri_order_percentage
    ADD COLUMN   `consumption_magnification` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '充值消费分的倍率',
	ADD COLUMN   `first_top_up_consumption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '首次充值消费分的额度',
	ADD COLUMN   `follow_up_consumption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '后续充值消费分的额度',
	ADD COLUMN   `withdraw_commission` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '提现佣金百分比',
	ADD COLUMN   `withdraw_consumption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '提现消费分百分比';

CREATE TABLE `distri_consumption_transfer_log` (
                                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志编号',
                                                   `transfer_user_id` bigint DEFAULT NULL COMMENT '转让用户ID',
                                                   `transfer_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '转让人用户名',
                                                   `consumption_points` bigint NOT NULL COMMENT '转让的消费分',
                                                   `after_transfer_consumption_points` bigint NOT NULL COMMENT '变动后消费分（余额）',
                                                   `recipient_user_id` bigint DEFAULT NULL COMMENT '接收用户ID',
                                                   `recipient_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '接收人用户名',
                                                   `recipient_points` bigint NOT NULL COMMENT '接收的消费分',
                                                   `after_recipient_consumption_points` bigint NOT NULL COMMENT '变动后消费分（余额）',
                                                   `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
                                                   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                                   `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
                                                   `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                                   PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='消费分转让记录';

ALTER TABLE distri_application_for_withdrawal
    ADD COLUMN `amount_total` bigint NOT NULL DEFAULT '0' COMMENT '提现总金额,单位:分',
	ADD COLUMN `withdraw_consumption` bigint NOT NULL DEFAULT '0' COMMENT '提现消费分,单位:分',
	ADD COLUMN `percent_template` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '计算百分比模板';


ALTER TABLE distri_application_for_withdrawal_channel
    ADD COLUMN `amount_total` bigint NOT NULL DEFAULT '0' COMMENT '提现总金额,单位:分',
	ADD COLUMN `withdraw_consumption` bigint NOT NULL DEFAULT '0' COMMENT '提现消费分,单位:分';

ALTER TABLE distri_pt_daily_withdrawal
    ADD COLUMN `amount_total` bigint NOT NULL DEFAULT '0' COMMENT '提现总金额,单位:分',
	ADD COLUMN `withdraw_consumption` bigint NOT NULL DEFAULT '0' COMMENT '提现消费分,单位:分';

ALTER TABLE trade_order
    ADD COLUMN `pay_consumption_points` bigint DEFAULT '0' COMMENT '支付消费分',
	ADD COLUMN `refund_consumption` bigint DEFAULT '0' COMMENT '退还的使用的消费分';

ALTER TABLE trade_order_item
    ADD COLUMN `pay_consumption_points` bigint DEFAULT '0' COMMENT '支付消费分',
	ADD COLUMN `refund_consumption` bigint DEFAULT '0' COMMENT '退还的使用的消费分';

ALTER TABLE distri_daily_bill
    ADD COLUMN `consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '消费分',
  ADD COLUMN `received_consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '确收消费分',
  ADD COLUMN `refund_consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '退款消费分';

ALTER TABLE distri_pt_daily_bill
    ADD COLUMN `consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '消费分',
  ADD COLUMN `received_consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '确收消费分',
  ADD COLUMN `refund_consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '退款消费分';

ALTER TABLE trade_shop_settlement
    ADD COLUMN `shop_consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '抵扣消费分';

ALTER TABLE trade_pt_settlement
    ADD COLUMN `consumption_points` bigint NOT NULL DEFAULT '0' COMMENT '抵扣消费分';

ALTER TABLE trade_after_sale
    ADD COLUMN `refund_consumption_points` bigint DEFAULT '0' COMMENT '退还的使用的消费分',
	ADD COLUMN `refund_price_total` bigint NOT NULL DEFAULT '0' COMMENT '退款总金额，单位：分',
	ADD COLUMN `refund_integral` bigint DEFAULT '0' COMMENT '退还的使用的积分';

CREATE TABLE distri_consumption_error_log (
                                              `id` bigint NOT NULL AUTO_INCREMENT COMMENT '异动日志编号',
                                              `error_users_count` int NOT NULL DEFAULT '0' COMMENT '异动用户数量',
                                              `error_consumption_points_total` bigint DEFAULT '0' COMMENT '异动消费分总额',
                                              `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
                                              `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                              `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                              `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
                                              `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                              PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='消费分异动日志记录';

CREATE TABLE distri_consumption_error_user (
                                               `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
                                               `consumption_error_log_id` bigint unsigned NOT NULL COMMENT '异动日志编号',
                                               `user_id` bigint unsigned NOT NULL COMMENT '用户编号',
                                               `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
                                               `consumption_points_total` bigint DEFAULT '0' COMMENT '累加消费分总额',
                                               `now_consumption_points_total` bigint DEFAULT '0' COMMENT '目前消费分总额',
                                               `current_consumption_points_total` bigint DEFAULT '0' COMMENT '可用消费分',
                                               `used_consumption_points_total` bigint DEFAULT '0' COMMENT '已用消费分',
                                               `transfer_consumption_points_total` bigint DEFAULT '0' COMMENT '已转让消费分',
                                               `error_consumption_points_total` bigint DEFAULT '0' COMMENT '异动消费分差额',
                                               `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
                                               `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                               `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                               `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
                                               `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                               PRIMARY KEY (`id`) USING BTREE,
                                               KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='消费分异动用户';

CREATE TABLE distri_user_top_up_consumption_points_order (
                                                             `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户充值消费分订单编号',
                                                             `no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '订单流水号',
                                                             `terminal` int NOT NULL COMMENT '订单来源终端',
                                                             `user_id` bigint unsigned NOT NULL COMMENT '用户编号',
                                                             `user_ip` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '用户 IP',
                                                             `subject` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商品标题',
                                                             `top_up_consumption_points` bigint NOT NULL COMMENT '充值消费分',
                                                             `practical_consumption_points` bigint NOT NULL COMMENT '到账消费分',
                                                             `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
                                                             `pay_order_id` bigint DEFAULT NULL COMMENT '支付订单编号',
                                                             `pay_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已支付：[0:未支付 1:已经支付过]',
                                                             `pay_time` datetime DEFAULT NULL COMMENT '订单支付时间',
                                                             `pay_channel_code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '支付成功的支付渠道',
                                                             `finish_time` datetime DEFAULT NULL COMMENT '订单完成时间',
                                                             `cancel_time` datetime DEFAULT NULL COMMENT '订单取消时间',
                                                             `pay_price` bigint NOT NULL DEFAULT '0' COMMENT '应付金额（总），单位：分',
                                                             `percent_template` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '计算百分比模板',
                                                             `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
                                                             `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                             `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                                             `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
                                                             `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                                             PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户充值消费分订单';

ALTER TABLE distri_order_percentage
    ADD COLUMN   `user_top_up_consumption_points` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户充值消费分的额度',
    ADD COLUMN `trigger_magnification_points` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '触发倍率的最低金额';