-- dryad-payment 支付模块数据表
-- 在主数据库 966120 中创建支付模块所需的表


-- 支付订单表
CREATE TABLE IF NOT EXISTS `pay_order` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '订单ID',
  `biz_order_id` VARCHAR(64) NOT NULL COMMENT '业务订单号',
  `amount` INT NOT NULL COMMENT '金额（分）',
  `currency` VARCHAR(8) NOT NULL DEFAULT 'CNY' COMMENT '币种',
  `channel` VARCHAR(16) NOT NULL COMMENT '支付渠道',
  `status` VARCHAR(16) NOT NULL COMMENT '订单状态',
  `subject` VARCHAR(128) NOT NULL COMMENT '订单标题',
  `description` VARCHAR(512) COMMENT '订单描述',
  `callback_url` VARCHAR(512) NOT NULL COMMENT '业务回调地址',
  `expire_at` DATETIME NOT NULL COMMENT '过期时间',
  `latest_transaction_id` BIGINT COMMENT '最新交易ID',
  `channel_trade_no` VARCHAR(64) COMMENT '渠道交易号',
  `paid_at` DATETIME COMMENT '支付成功时间',
  `version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
  `created_at` DATETIME NOT NULL COMMENT '创建时间',
  `updated_at` DATETIME NOT NULL COMMENT '更新时间',
  INDEX `idx_biz_order_id` (`biz_order_id`),
  INDEX `idx_channel_trade_no` (`channel_trade_no`),
  INDEX `idx_status` (`status`),
  INDEX `idx_expire_at` (`expire_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付订单表';

-- 支付交易流水表
CREATE TABLE IF NOT EXISTS `pay_transaction` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '交易ID',
  `order_id` BIGINT NOT NULL COMMENT '订单ID',
  `channel` VARCHAR(16) NOT NULL COMMENT '支付渠道',
  `client_type` VARCHAR(32) NOT NULL COMMENT '客户端类型',
  `status` VARCHAR(16) NOT NULL COMMENT '交易状态',
  `code_or_qr` VARCHAR(512) COMMENT '二维码内容',
  `qr_base64` TEXT COMMENT 'Base64二维码图片',
  `request_params` TEXT COMMENT '请求参数快照',
  `response_snapshot` TEXT COMMENT '响应快照',
  `channel_trade_no` VARCHAR(64) COMMENT '渠道交易号',
  `created_at` DATETIME NOT NULL COMMENT '创建时间',
  `paid_at` DATETIME COMMENT '支付完成时间',
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_channel_trade_no` (`channel_trade_no`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付交易流水表';

-- 渠道回调日志表
CREATE TABLE IF NOT EXISTS `pay_notify_log` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '日志ID',
  `channel` VARCHAR(16) NOT NULL COMMENT '支付渠道',
  `notify_id_or_serial` VARCHAR(64) NOT NULL COMMENT '渠道通知唯一标识',
  `order_id` BIGINT COMMENT '订单ID',
  `transaction_id` BIGINT COMMENT '交易ID',
  `payload` TEXT NOT NULL COMMENT '回调原始报文',
  `verified` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '签名验证是否通过',
  `processed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已处理',
  `result` VARCHAR(128) COMMENT '处理结果',
  `created_at` DATETIME NOT NULL COMMENT '接收时间',
  UNIQUE KEY `uk_channel_notify` (`channel`, `notify_id_or_serial`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道回调日志表';

-- 业务回调日志表
CREATE TABLE IF NOT EXISTS `pay_biz_callback_log` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '日志ID',
  `order_id` BIGINT NOT NULL COMMENT '订单ID',
  `transaction_id` BIGINT NOT NULL COMMENT '交易ID',
  `callback_url` VARCHAR(512) NOT NULL COMMENT '回调地址',
  `payload` TEXT NOT NULL COMMENT '回调请求体',
  `http_status` INT COMMENT 'HTTP状态码',
  `response` TEXT COMMENT '响应内容',
  `success` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否成功',
  `retry_count` INT NOT NULL DEFAULT 0 COMMENT '重试次数',
  `last_retry_at` DATETIME COMMENT '最后重试时间',
  `created_at` DATETIME NOT NULL COMMENT '创建时间',
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_success` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务回调日志表';

-- 操作审计表
CREATE TABLE IF NOT EXISTS `pay_operation_audit` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '审计ID',
  `operator` VARCHAR(64) NOT NULL COMMENT '操作人',
  `operation_type` VARCHAR(32) NOT NULL COMMENT '操作类型',
  `order_id` BIGINT COMMENT '订单ID',
  `transaction_id` BIGINT COMMENT '交易ID',
  `params` TEXT COMMENT '操作参数',
  `approved` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否通过',
  `created_at` DATETIME NOT NULL COMMENT '操作时间',
  INDEX `idx_operator` (`operator`),
  INDEX `idx_operation_type` (`operation_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作审计表';

-- 对账任务表
CREATE TABLE IF NOT EXISTS `pay_reconciliation_task` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '任务ID',
  `task_date` DATE NOT NULL COMMENT '对账日期',
  `status` VARCHAR(16) NOT NULL COMMENT '任务状态',
  `total_count` INT NOT NULL DEFAULT 0 COMMENT '总订单数',
  `success_count` INT NOT NULL DEFAULT 0 COMMENT '成功数',
  `failed_count` INT NOT NULL DEFAULT 0 COMMENT '失败数',
  `diff_count` INT NOT NULL DEFAULT 0 COMMENT '差异数',
  `fixed_count` INT NOT NULL DEFAULT 0 COMMENT '自动修复数',
  `created_at` DATETIME NOT NULL COMMENT '创建时间',
  `finished_at` DATETIME COMMENT '完成时间',
  UNIQUE KEY `uk_task_date` (`task_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账任务表';

-- 对账差异明细表
CREATE TABLE IF NOT EXISTS `pay_reconciliation_result` (
  `id` BIGINT NOT NULL PRIMARY KEY COMMENT '明细ID',
  `task_id` BIGINT NOT NULL COMMENT '任务ID',
  `order_id` BIGINT NOT NULL COMMENT '订单ID',
  `transaction_id` BIGINT COMMENT '交易ID',
  `local_status` VARCHAR(16) COMMENT '本地状态',
  `channel_status` VARCHAR(16) COMMENT '渠道状态',
  `diff_type` VARCHAR(32) NOT NULL COMMENT '差异类型',
  `fixed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已修复',
  `note` VARCHAR(512) COMMENT '备注',
  `created_at` DATETIME NOT NULL COMMENT '创建时间',
  INDEX `idx_task_id` (`task_id`),
  INDEX `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账差异明细表';