water/server/plugin/saiadmin/db/20260901_add_rental_order.sql

CREATE TABLE IF NOT EXISTS `sa_rental_order` (
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `order_sn`            VARCHAR(64) NOT NULL COMMENT '订单号',
    `sub_order_sn`        VARCHAR(64) NOT NULL DEFAULT '' COMMENT '子订单号',
    `transaction_id`      VARCHAR(128) NOT NULL DEFAULT '' COMMENT '交易单号',
    `merchant_order_no`   VARCHAR(128) NOT NULL DEFAULT '' COMMENT '商户单号',
    `device_id`           INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '设备ID',
    `device_type`         INT NOT NULL DEFAULT 0 COMMENT '设备类型',
    `source`              TINYINT NOT NULL DEFAULT 1 COMMENT '来源：1微信 2支付宝',
    `pay_type`            VARCHAR(16) NOT NULL DEFAULT 'deposit' COMMENT '支付类型：deposit/free',
    `user_id`             BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '租借用户ID',
    `user_name`           VARCHAR(100) NOT NULL DEFAULT '' COMMENT '租借用户名称',
    `user_phone`          VARCHAR(32) NOT NULL DEFAULT '' COMMENT '租借用户手机号',
    `rent_merchant_id`    INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '租借商户ID',
    `return_merchant_id`  INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '归还商户ID',
    `manage_agent_id`     INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '当前管理代理ID',
    `rent_at`             DATETIME NULL COMMENT '租借时间',
    `return_at`           DATETIME NULL COMMENT '归还时间',
    `status`              TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1使用中 2完成 3超时 4租借失败 5扣款失败 6申请退款',
    `paid_amount`         DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '实付金额',
    `income_amount`       DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '收益金额',
    `refund_amount`       DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '退款金额',
    `package_description` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '套餐说明',
    `remark`              VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
    `created_by`          INT NULL, `updated_by` INT NULL,
    `create_time`         DATETIME NULL, `update_time` DATETIME NULL, `delete_time` DATETIME NULL,
    PRIMARY KEY (`id`), UNIQUE KEY `uk_rental_order_sn` (`order_sn`),
    KEY `idx_rental_order_manager_time` (`manage_agent_id`, `rent_at`),
    KEY `idx_rental_order_status` (`status`), KEY `idx_rental_order_device` (`device_id`),
    KEY `idx_rental_order_rent_merchant` (`rent_merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='租借订单';