From 39f6fca28e3f78abe48f08e474ec011abb990b36 Mon Sep 17 00:00:00 2001
From: liujie <1793218484@qq.com>
Date: 星期三, 09 九月 2026 10:39:21 +0800
Subject: [PATCH] update
---
backend/db/schema.sql | 21 +++++++++++++++++----
1 files changed, 17 insertions(+), 4 deletions(-)
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 9d51089..cb0fb14 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -77,12 +77,15 @@
`commission_ratio` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '平台抽成比例，单位百分比',
`wechat_open_id` VARCHAR(128) DEFAULT NULL COMMENT '微信收款openID',
`merchant_id` VARCHAR(64) DEFAULT NULL COMMENT '微信支付商户ID',
+ `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '讲解队可提现余额',
+ `collection_guide_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '收款导游ID',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0停用，1启用',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_guide_team_name` (`team_name`),
- KEY `idx_guide_team_status` (`status`)
+ KEY `idx_guide_team_status` (`status`),
+ UNIQUE KEY `uk_guide_team_collection_guide` (`collection_guide_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游讲解队表';
CREATE TABLE IF NOT EXISTS `guide_team_scenic_config` (
@@ -119,6 +122,7 @@
`current_month_order_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本月接单数',
`rating` DECIMAL(3,2) NOT NULL DEFAULT 0.00 COMMENT '平均评分，范围0到5',
`auto_disable_whitelisted` TINYINT NOT NULL DEFAULT 0 COMMENT '自动禁用白名单：0否，1是',
+ `auto_disabled` TINYINT NOT NULL DEFAULT 0 COMMENT '是否由自动禁用规则停用：0否，1是',
`wechat_open_id` VARCHAR(128) DEFAULT NULL COMMENT '导游收款微信 openID',
`balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '导游可提现余额',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账号状态：0停用，1启用',
@@ -132,9 +136,14 @@
KEY `idx_guide_status` (`status`),
KEY `idx_guide_team_id` (`team_id`),
KEY `idx_guide_whitelist` (`auto_disable_whitelisted`),
+ KEY `idx_guide_auto_disabled_status` (`auto_disabled`, `status`),
KEY `idx_guide_rating` (`rating`),
CONSTRAINT `fk_guide_user_team` FOREIGN KEY (`team_id`) REFERENCES `guide_team` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游端用户表';
+
+ALTER TABLE `guide_team`
+ ADD CONSTRAINT `fk_guide_team_collection_guide`
+ FOREIGN KEY (`collection_guide_id`) REFERENCES `guide_user` (`id`) ON DELETE SET NULL;
CREATE TABLE IF NOT EXISTS `guide_application` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '导游申请主键',
@@ -329,9 +338,11 @@
`arrived_at` DATETIME DEFAULT NULL COMMENT '导游到达汇合点时间',
`service_started_at` DATETIME DEFAULT NULL COMMENT '讲解开始时间',
`completed_at` DATETIME DEFAULT NULL COMMENT '讲解结束时间',
+ `cancelled_at` DATETIME DEFAULT NULL COMMENT '取消时间',
`status` VARCHAR(32) NOT NULL COMMENT '订单状态',
- `refund_status` VARCHAR(32) NOT NULL DEFAULT 'NONE' COMMENT '退款状态：NONE、REFUNDED',
- `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '退款金额',
+ `refund_status` VARCHAR(32) NOT NULL DEFAULT 'NONE' COMMENT '退款状态：NONE、REFUNDING、PARTIAL、REFUNDED、REFUND_FAILED',
+ `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '当前退款请求金额',
+ `refunded_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计成功退款金额',
`refund_time` DATETIME DEFAULT NULL COMMENT '退款时间',
`refund_order_no` VARCHAR(64) DEFAULT NULL COMMENT '退款流水号',
`refund_method` VARCHAR(32) DEFAULT NULL COMMENT '退款方式',
@@ -409,6 +420,7 @@
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '提现申请主键',
`withdrawal_no` VARCHAR(32) NOT NULL COMMENT '提现业务编号',
`guide_id` BIGINT UNSIGNED NOT NULL COMMENT '申请导游主键',
+ `team_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '资金归属讲解队ID，为空表示个人导游',
`guide_name` VARCHAR(64) NOT NULL COMMENT '导游姓名快照',
`guide_mobile` VARCHAR(20) NOT NULL COMMENT '导游手机号快照',
`amount` DECIMAL(10,2) NOT NULL COMMENT '提现金额',
@@ -423,7 +435,7 @@
`cash_req_date` CHAR(8) DEFAULT NULL COMMENT '导游账户取现请求日期',
`cash_hf_seq_id` VARCHAR(64) DEFAULT NULL COMMENT '导游账户取现汇付全局流水号',
`payout_message` VARCHAR(255) DEFAULT NULL COMMENT '资金处理状态说明',
- `status` VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT '状态：PENDING、TRANSFERRING、TRANSFERRED、PROCESSING、APPROVED、REJECTED',
+ `status` VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT '状态：PENDING、TRANSFERRING、TRANSFER_FAILED、TRANSFERRED、PROCESSING、APPROVED、REJECTED',
`audit_remark` VARCHAR(255) DEFAULT NULL COMMENT '处理备注',
`processor` VARCHAR(64) DEFAULT NULL COMMENT '后台处理人',
`applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
@@ -433,6 +445,7 @@
PRIMARY KEY (`id`),
UNIQUE KEY `uk_guide_withdrawal_no` (`withdrawal_no`),
KEY `idx_guide_withdrawal_guide` (`guide_id`),
+ KEY `idx_guide_withdrawal_team` (`team_id`),
KEY `idx_guide_withdrawal_guide_status` (`guide_id`, `status`),
KEY `idx_guide_withdrawal_payout` (`status`, `updated_at`),
KEY `idx_guide_withdrawal_status` (`status`),
--
Gitblit v1.7.1