create database if not exists ln_scene character set utf8mb4 collate utf8mb4_unicode_ci;
-- >>------------------------------------------------------------------------------------
use ln_scene;

-- 支付订单表
drop TABLE if exists `dragon_orders`;
CREATE TABLE `dragon_orders`
(
    `mid`           bigint unsigned                                         NOT NULL AUTO_INCREMENT,
    `status`       tinyint                                                 NOT NULL DEFAULT '0' COMMENT '订单状态',
    `code`         varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '订单支付编号',
    `type`         varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '订单类型',
    `price`        decimal(10, 2)                                          NOT NULL COMMENT '总价格',
    `name`         varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '类别或名称',
    `detail`       varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '详情或描述',
    `order_code`   varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '调用端订单编号',
    `client_ip`    varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '用户客户端ip地址',
    `notify_url`   varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '支付完成通知回调地址',
    `payment_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci           DEFAULT NULL COMMENT '支付类型',
    `payment_id`   varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci           DEFAULT NULL COMMENT '支付的订单号',
    `payment_at`   timestamp                                               NULL     DEFAULT NULL COMMENT '支付时间',
    `finished_at`  timestamp                                               NULL     DEFAULT NULL COMMENT '结束时间',
    `created_at`   timestamp                                               NULL     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`   timestamp                                               NULL     DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at`   timestamp                                               NULL     DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`mid`),
    UNIQUE KEY `orders_code_unique` (`code`),
    UNIQUE KEY `orders_payment_id_unique` (`payment_id`),
    KEY `orders_payment_type_index` (`payment_type`),
    KEY `orders_status_index` (`status`),
    KEY `orders_type_index` (`type`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT '支付订单表';

-- 支付订单日志表
drop TABLE if exists `dragon_order_logs`;
CREATE TABLE `dragon_order_logs`
(
    `mid`           bigint unsigned                                        NOT NULL AUTO_INCREMENT,
    `order_id`     bigint                                                 NOT NULL COMMENT '订单id',
    `payment_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '支付类型',
    `content`      text CHARACTER SET utf8 COLLATE utf8_unicode_ci        NOT NULL COMMENT '支付通知内容',
    `created_at`   timestamp                                              NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`   timestamp                                              NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at`   timestamp                                              NULL DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`mid`),
    KEY `order_logs_order_id_index` (`order_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT '支付订单日志表';

-- 退款订单表
drop TABLE if exists `dragon_order_refunds`;
CREATE TABLE `dragon_order_refunds`
(
    `mid`                bigint unsigned                                         NOT NULL AUTO_INCREMENT,
    `order_id`          bigint                                                  NOT NULL COMMENT '订单id',
    `status`            tinyint                                                 NOT NULL DEFAULT '0' COMMENT '订单退款状态',
    `code`              varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '订单退款编号',
    `order_refund_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '调用端订单退款编号',
    `price`             decimal(10, 2)                                          NOT NULL COMMENT '退款价格',
    `reason`            varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '退款原因',
    `notify_url`        varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '退款完成通知回调地址',
    `refund_type`       varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '退款类型',
    `refund_id`         varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci           DEFAULT NULL COMMENT '退款的订单号',
    `refund_error`      varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci          DEFAULT NULL COMMENT '退款失败原因',
    `refund_at`         timestamp                                               NULL     DEFAULT NULL COMMENT '退款时间',
    `finished_at`       timestamp                                               NULL     DEFAULT NULL COMMENT '退款结束时间',
    `created_at`        timestamp                                               NULL     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`        timestamp                                               NULL     DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at`        timestamp                                               NULL     DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`mid`),
    UNIQUE KEY `order_refunds_code_unique` (`code`),
    UNIQUE KEY `order_refunds_order_id_order_refund_code_unique` (`order_id`, `order_refund_code`),
    KEY `order_refunds_order_id_index` (`order_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT '退款订单表';

-- 退款订单信息表
drop TABLE if exists `dragon_order_refund_logs`;
CREATE TABLE `dragon_order_refund_logs`
(
    `mid`              bigint unsigned                                        NOT NULL AUTO_INCREMENT,
    `order_id`        bigint                                                 NOT NULL COMMENT '订单id',
    `order_refund_id` bigint                                                 NOT NULL COMMENT '退款订单id',
    `refund_type`     varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '退款类型',
    `content`         text CHARACTER SET utf8 COLLATE utf8_unicode_ci        NOT NULL COMMENT '退款通知内容',
    `created_at`      timestamp                                              NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`      timestamp                                              NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at`      timestamp                                              NULL DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`mid`),
    KEY `order_refund_logs_order_id_index` (`order_id`),
    KEY `order_refund_logs_order_refund_id_index` (`order_refund_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT '退款订单信息表';
