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;