From ad5db85e540947e82c7a4d5612879b3b3ecdf459 Mon Sep 17 00:00:00 2001
From: liujie <1793218484@qq.com>
Date: 星期五, 21 八月 2026 10:38:07 +0800
Subject: [PATCH] update
---
backend/db/schema.sql | 36 +++++++++++++++++++++++++++++++++++-
1 files changed, 35 insertions(+), 1 deletions(-)
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 0296cd4..9d51089 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -120,6 +120,7 @@
`rating` DECIMAL(3,2) NOT NULL DEFAULT 0.00 COMMENT '平均评分，范围0到5',
`auto_disable_whitelisted` 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启用',
`deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
@@ -321,6 +322,9 @@
`commission_rate` DECIMAL(5,2) DEFAULT NULL COMMENT '接单时平台抽成比例快照，单位百分比',
`commission_team_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '讲解队抽成来源ID，个人导游为空',
`guide_earning_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '导游实际业绩金额快照',
+ `settlement_days` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '下单时结算周期快照，单位天',
+ `settlement_due_at` DATETIME DEFAULT NULL COMMENT '预计可分账时间',
+ `settled_at` DATETIME DEFAULT NULL COMMENT '实际分账入余额时间',
`accepted_at` DATETIME DEFAULT NULL COMMENT '导游接单时间',
`arrived_at` DATETIME DEFAULT NULL COMMENT '导游到达汇合点时间',
`service_started_at` DATETIME DEFAULT NULL COMMENT '讲解开始时间',
@@ -340,6 +344,7 @@
KEY `idx_tour_order_visitor` (`visitor_id`),
KEY `idx_tour_order_guide` (`guide_id`),
KEY `idx_tour_order_guide_earning` (`guide_id`, `status`, `completed_at`),
+ KEY `idx_tour_order_settlement` (`status`, `settled_at`, `settlement_due_at`),
KEY `idx_tour_order_scenic` (`scenic_name`),
KEY `idx_tour_order_status` (`status`),
KEY `idx_tour_order_created_at` (`created_at`),
@@ -407,7 +412,18 @@
`guide_name` VARCHAR(64) NOT NULL COMMENT '导游姓名快照',
`guide_mobile` VARCHAR(20) NOT NULL COMMENT '导游手机号快照',
`amount` DECIMAL(10,2) NOT NULL COMMENT '提现金额',
- `status` VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT '状态：PENDING、APPROVED、REJECTED',
+ `bank_card_id` INT DEFAULT NULL COMMENT '提现结算卡账户主键',
+ `bank_name` VARCHAR(64) DEFAULT NULL COMMENT '收款银行快照',
+ `card_masked` VARCHAR(32) DEFAULT NULL COMMENT '收款卡号掩码',
+ `transfer_req_seq_id` VARCHAR(32) DEFAULT NULL COMMENT '主账户划账请求流水号',
+ `transfer_req_date` CHAR(8) DEFAULT NULL COMMENT '主账户划账请求日期',
+ `transfer_hf_seq_id` VARCHAR(64) DEFAULT NULL COMMENT '主账户划账汇付全局流水号',
+ `transferred_at` DATETIME DEFAULT NULL COMMENT '主账户划账完成时间',
+ `cash_req_seq_id` VARCHAR(32) DEFAULT NULL COMMENT '导游账户取现请求流水号',
+ `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',
`audit_remark` VARCHAR(255) DEFAULT NULL COMMENT '处理备注',
`processor` VARCHAR(64) DEFAULT NULL COMMENT '后台处理人',
`applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
@@ -417,6 +433,8 @@
PRIMARY KEY (`id`),
UNIQUE KEY `uk_guide_withdrawal_no` (`withdrawal_no`),
KEY `idx_guide_withdrawal_guide` (`guide_id`),
+ KEY `idx_guide_withdrawal_guide_status` (`guide_id`, `status`),
+ KEY `idx_guide_withdrawal_payout` (`status`, `updated_at`),
KEY `idx_guide_withdrawal_status` (`status`),
KEY `idx_guide_withdrawal_applied_at` (`applied_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游提现申请表';
@@ -762,6 +780,22 @@
ALTER TABLE `guide_team` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
ALTER TABLE `guide_team_scenic_config` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
ALTER TABLE `guide_withdrawal` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
+
+CREATE TABLE IF NOT EXISTS `guide_bank_branch` (
+ `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '开户行主键',
+ `bank_name` VARCHAR(64) NOT NULL COMMENT '银行名称',
+ `branch_name` VARCHAR(128) NOT NULL COMMENT '开户行名称',
+ `branch_code` VARCHAR(32) NOT NULL COMMENT '联行号',
+ `city_code` VARCHAR(16) NOT NULL COMMENT '城市行政区划编码',
+ `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用，0停用',
+ `deleted` TINYINT NOT NULL DEFAULT 0 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_bank_branch_code` (`branch_code`),
+ KEY `idx_guide_bank_branch_city_status` (`city_code`, `status`),
+ KEY `idx_guide_bank_branch_name` (`bank_name`, `branch_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游结算卡开户行基础表';
ALTER TABLE `operation_scenic_meeting_point` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
ALTER TABLE `operation_scenic_pricing` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
ALTER TABLE `sys_admin_user_post` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
--
Gitblit v1.7.1