-- pay-bridge 初始化数据库脚本
-- MySQL 8.0, utf8mb4, InnoDB
-- 金额字段统一使用 BIGINT（单位：分）

CREATE DATABASE IF NOT EXISTS pay_bridge DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pay_bridge;

-- 接入应用
CREATE TABLE IF NOT EXISTS `app` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id`     VARCHAR(32)  NOT NULL COMMENT '应用ID，下游系统鉴权凭证',
  `app_secret` VARCHAR(128) NOT NULL COMMENT '应用密钥（AES加密存储）',
  `app_name`   VARCHAR(64)  NOT NULL COMMENT '应用名称',
  `status`     TINYINT      NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
  `created_at` DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_app_id` (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接入应用';

-- 交易订单
CREATE TABLE IF NOT EXISTS `trade_order` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `trade_no`               VARCHAR(32)  NOT NULL COMMENT 'pay-bridge生成的交易号',
  `merchant_order_no`      VARCHAR(64)  NOT NULL COMMENT '下游系统的商户订单号',
  `app_id`                 VARCHAR(32)  NOT NULL COMMENT '所属应用ID',
  `channel_code`           VARCHAR(32)  NOT NULL COMMENT '支付渠道编码',
  `channel_trade_no`       VARCHAR(64)  DEFAULT NULL COMMENT '上游渠道交易号',
  `pay_method`             VARCHAR(32)  NOT NULL COMMENT '支付方式',
  `amount`                 BIGINT       NOT NULL COMMENT '订单金额（分）',
  `profit_sharing_amount`  BIGINT       NOT NULL DEFAULT 0 COMMENT '分润金额（分）',
  `service_fee_amount`     BIGINT       NOT NULL DEFAULT 0 COMMENT '服务费金额（分）',
  `subject`                VARCHAR(256) NOT NULL COMMENT '商品描述',
  `notify_url`             VARCHAR(512) NOT NULL COMMENT '下游通知地址',
  `status`                 VARCHAR(20)  NOT NULL DEFAULT 'CREATING' COMMENT '交易状态',
  `extra`                  JSON         DEFAULT NULL COMMENT '支付方式扩展参数',
  `channel_extra`          JSON         DEFAULT NULL COMMENT '渠道返回的支付凭证',
  `expire_time`            DATETIME(3)  NOT NULL COMMENT '订单过期时间',
  `pay_time`               DATETIME(3)  DEFAULT NULL COMMENT '支付成功时间',
  `created_at`             DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`             DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trade_no` (`trade_no`),
  UNIQUE KEY `uk_app_merchant_order` (`app_id`, `merchant_order_no`),
  KEY `idx_channel_trade_no` (`channel_trade_no`),
  KEY `idx_app_status_created` (`app_id`, `status`, `created_at`),
  KEY `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易订单';

-- 退款记录
CREATE TABLE IF NOT EXISTS `refund_order` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `refund_no`        VARCHAR(32)  NOT NULL COMMENT 'pay-bridge退款单号',
  `trade_no`         VARCHAR(32)  NOT NULL COMMENT '关联交易号',
  `app_id`           VARCHAR(32)  NOT NULL,
  `channel_code`     VARCHAR(32)  NOT NULL,
  `channel_refund_no` VARCHAR(64) DEFAULT NULL COMMENT '上游渠道退款单号',
  `refund_amount`    BIGINT       NOT NULL COMMENT '退款金额（分）',
  `reason`           VARCHAR(256) DEFAULT NULL COMMENT '退款原因',
  `status`           VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
  `notify_url`       VARCHAR(512) DEFAULT NULL,
  `refund_time`      DATETIME(3)  DEFAULT NULL COMMENT '退款完成时间',
  `created_at`       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_refund_no` (`refund_no`),
  KEY `idx_trade_no` (`trade_no`),
  KEY `idx_app_status` (`app_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款记录';

-- 渠道配置
CREATE TABLE IF NOT EXISTS `channel_config` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id`       VARCHAR(32)  NOT NULL COMMENT '关联应用ID',
  `channel_code` VARCHAR(32)  NOT NULL COMMENT '渠道编码',
  `merchant_id`  VARCHAR(64)  NOT NULL COMMENT '渠道商户ID',
  `api_key`      TEXT         DEFAULT NULL COMMENT 'API密钥（AES加密）',
  `private_key`  TEXT         DEFAULT NULL COMMENT 'RSA私钥（AES加密）',
  `public_key`   TEXT         DEFAULT NULL COMMENT '渠道公钥（明文）',
  `notify_url`   VARCHAR(512) NOT NULL COMMENT '上游回调接收地址',
  `sandbox`      TINYINT      NOT NULL DEFAULT 0 COMMENT '1=沙箱 0=生产',
  `extra_config` JSON         DEFAULT NULL COMMENT '渠道特有扩展配置',
  `status`       TINYINT      NOT NULL DEFAULT 1,
  `created_at`   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_app_channel` (`app_id`, `channel_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道配置';

-- 下游通知记录
CREATE TABLE IF NOT EXISTS `notify_log` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `trade_no`        VARCHAR(32)  NOT NULL COMMENT '关联交易号',
  `notify_type`     VARCHAR(20)  NOT NULL COMMENT 'PAYMENT / REFUND',
  `notify_url`      VARCHAR(512) NOT NULL,
  `status`          VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
  `retry_count`     INT          NOT NULL DEFAULT 0,
  `next_retry_time` DATETIME(3)  DEFAULT NULL,
  `last_response`   TEXT         DEFAULT NULL,
  `created_at`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trade_notify_type` (`trade_no`, `notify_type`),
  KEY `idx_status_next_retry` (`status`, `next_retry_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='下游通知记录';

-- 订单编码序列
CREATE TABLE IF NOT EXISTS `order_sequence` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id`        VARCHAR(32)  NOT NULL,
  `seq_type`      VARCHAR(20)  NOT NULL COMMENT 'TRADE / REFUND / SHARING',
  `prefix`        VARCHAR(8)   NOT NULL COMMENT '序号前缀',
  `current_value` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `step`          INT          NOT NULL DEFAULT 1,
  `updated_at`    DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_app_type` (`app_id`, `seq_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单编码序列';