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;