-- 开放API回调能力升级

SET @schema_name = DATABASE();

SET @column_exists = (
  SELECT COUNT(1)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_open_api_app'
    AND COLUMN_NAME = 'callback_url'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_open_api_app` ADD COLUMN `callback_url` VARCHAR(255) DEFAULT NULL COMMENT ''订单结果回调地址'' AFTER `allow_ip_list`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (
  SELECT COUNT(1)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_open_api_app'
    AND COLUMN_NAME = 'callback_secret'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_open_api_app` ADD COLUMN `callback_secret` VARCHAR(128) DEFAULT NULL COMMENT ''订单结果回调签名密钥'' AFTER `callback_url`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `xxk_open_api_callback_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
  `member_user_id` BIGINT NOT NULL COMMENT '会员ID',
  `order_id` BIGINT NOT NULL COMMENT '订单ID',
  `order_no` VARCHAR(64) NOT NULL COMMENT '订单号',
  `callback_url` VARCHAR(255) DEFAULT NULL COMMENT '回调地址',
  `event_type` VARCHAR(64) NOT NULL COMMENT '事件类型',
  `request_body` LONGTEXT DEFAULT NULL COMMENT '请求报文',
  `response_body` LONGTEXT DEFAULT NULL COMMENT '响应报文',
  `response_status` INT DEFAULT NULL COMMENT '响应HTTP状态码',
  `delivery_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '投递状态(PENDING/SUCCESS/FAIL/SKIPPED)',
  `error_message` VARCHAR(500) DEFAULT NULL COMMENT '错误信息',
  `attempt_no` INT NOT NULL DEFAULT 1 COMMENT '尝试次数',
  `trigger_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '触发时间',
  `finish_time` DATETIME DEFAULT NULL COMMENT '完成时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_xxk_open_api_callback_log_app_id` (`open_api_app_id`),
  KEY `idx_xxk_open_api_callback_log_order_no` (`order_no`),
  KEY `idx_xxk_open_api_callback_log_status` (`delivery_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开放API订单回调日志表';