SET @db_name = DATABASE();
|
|
CREATE TABLE IF NOT EXISTS `tcm_course_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 '用户手机号快照',
|
`course_type` VARCHAR(32) NOT NULL COMMENT '课程类型：HEALTH_COURSE养生课程，可扩展',
|
`purchase_target_type` VARCHAR(32) NOT NULL COMMENT '购买对象类型：COURSE整课 COURSE_CHAPTER课程小节',
|
`course_id` BIGINT UNSIGNED NOT NULL COMMENT '课程ID',
|
`course_video_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '课程小节ID（整课购买为空）',
|
`product_name_snapshot` VARCHAR(255) NOT NULL COMMENT '商品名称快照：整课=课程名称 小节=课程名称-小节名称',
|
`course_title_snapshot` VARCHAR(128) NOT NULL COMMENT '课程名称快照',
|
`chapter_name_snapshot` VARCHAR(128) DEFAULT NULL COMMENT '小节名称快照',
|
`cover_img_snapshot` VARCHAR(255) DEFAULT 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_course_purchase_order_no` (`order_no`),
|
KEY `idx_course_purchase_order_user` (`app_user_id`),
|
KEY `idx_course_purchase_order_status` (`status`),
|
KEY `idx_course_purchase_order_course_type` (`course_type`),
|
KEY `idx_course_purchase_order_target_type` (`purchase_target_type`),
|
KEY `idx_course_purchase_order_course` (`course_id`),
|
KEY `idx_course_purchase_order_course_video` (`course_video_id`),
|
KEY `idx_course_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_course_purchase_order'
|
);
|
|
SET @sql_assert_table = IF(
|
@table_exists = 0,
|
'SELECT ''skip: tcm_course_purchase_order not exists''',
|
'SELECT 1'
|
);
|
PREPARE stmt_assert_table FROM @sql_assert_table;
|
EXECUTE stmt_assert_table;
|
DEALLOCATE PREPARE stmt_assert_table;
|
