SET @db_name = DATABASE();
|
|
CREATE TABLE IF NOT EXISTS `tcm_member_purchase_order` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
|
`app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
`user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
|
`user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户手机号快照',
|
`member_type` VARCHAR(32) NOT NULL COMMENT '会员类型：MONTH_CARD月卡 QUARTER_CARD季卡',
|
`member_days` INT NOT NULL COMMENT '会员时长天数快照',
|
`order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额快照',
|
`payment_method` VARCHAR(32) NOT NULL COMMENT '付款方式：WECHAT微信 ALIPAY支付宝',
|
`refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '退款金额',
|
`refund_remark` VARCHAR(255) DEFAULT NULL COMMENT '退款备注',
|
`status` VARCHAR(32) NOT NULL COMMENT '订单状态：WAITING_PAYMENT待支付 PAID已支付 REFUNDED已退款 CANCELLED已取消',
|
`paid_at` DATETIME DEFAULT NULL COMMENT '支付时间',
|
`refunded_at` DATETIME DEFAULT NULL COMMENT '退款时间',
|
`cancelled_at` DATETIME DEFAULT NULL COMMENT '取消时间',
|
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
|
`created_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
|
`updated_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_member_purchase_order_no` (`order_no`),
|
KEY `idx_member_purchase_order_user` (`app_user_id`),
|
KEY `idx_member_purchase_order_status` (`status`),
|
KEY `idx_member_purchase_order_member_type` (`member_type`),
|
KEY `idx_member_purchase_order_payment_method` (`payment_method`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员购买订单表';
|
|
SET @table_exists = (
|
SELECT COUNT(1)
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_member_purchase_order'
|
);
|
|
SET @sql_assert_table = IF(
|
@table_exists = 0,
|
'SELECT ''skip: tcm_member_purchase_order not exists''',
|
'SELECT 1'
|
);
|
PREPARE stmt_assert_table FROM @sql_assert_table;
|
EXECUTE stmt_assert_table;
|
DEALLOCATE PREPARE stmt_assert_table;
|
|
SET @has_refund_amount = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_member_purchase_order'
|
AND COLUMN_NAME = 'refund_amount'
|
);
|
SET @sql_add_refund_amount = IF(
|
@table_exists = 1 AND @has_refund_amount = 0,
|
'ALTER TABLE `tcm_member_purchase_order` ADD COLUMN `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT ''退款金额'' AFTER `payment_method`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_refund_amount FROM @sql_add_refund_amount;
|
EXECUTE stmt_add_refund_amount;
|
DEALLOCATE PREPARE stmt_add_refund_amount;
|
|
SET @has_refund_remark = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_member_purchase_order'
|
AND COLUMN_NAME = 'refund_remark'
|
);
|
SET @sql_add_refund_remark = IF(
|
@table_exists = 1 AND @has_refund_remark = 0,
|
'ALTER TABLE `tcm_member_purchase_order` ADD COLUMN `refund_remark` VARCHAR(255) DEFAULT NULL COMMENT ''退款备注'' AFTER `refund_amount`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_refund_remark FROM @sql_add_refund_remark;
|
EXECUTE stmt_add_refund_remark;
|
DEALLOCATE PREPARE stmt_add_refund_remark;
|
|
SET @sql_modify_status = IF(
|
@table_exists = 1,
|
'ALTER TABLE `tcm_member_purchase_order` MODIFY COLUMN `status` VARCHAR(32) NOT NULL COMMENT ''订单状态：WAITING_PAYMENT待支付 PAID已支付 REFUNDED已退款 CANCELLED已取消''',
|
'SELECT 1'
|
);
|
PREPARE stmt_modify_status FROM @sql_modify_status;
|
EXECUTE stmt_modify_status;
|
DEALLOCATE PREPARE stmt_modify_status;
|
|
SET @has_payment_method_index = (
|
SELECT COUNT(1)
|
FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_member_purchase_order'
|
AND INDEX_NAME = 'idx_member_purchase_order_payment_method'
|
);
|
SET @sql_add_payment_method_index = IF(
|
@table_exists = 1 AND @has_payment_method_index = 0,
|
'ALTER TABLE `tcm_member_purchase_order` ADD INDEX `idx_member_purchase_order_payment_method` (`payment_method`)',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_payment_method_index FROM @sql_add_payment_method_index;
|
EXECUTE stmt_add_payment_method_index;
|
DEALLOCATE PREPARE stmt_add_payment_method_index;
|
|
SET @sql_fill_refund_amount = IF(
|
@table_exists = 1,
|
'UPDATE `tcm_member_purchase_order`
|
SET `refund_amount` = `order_amount`
|
WHERE `status` = ''REFUNDED'' AND `refund_amount` IS NULL',
|
'SELECT 1'
|
);
|
PREPARE stmt_fill_refund_amount FROM @sql_fill_refund_amount;
|
EXECUTE stmt_fill_refund_amount;
|
DEALLOCATE PREPARE stmt_fill_refund_amount;
|
