From 280ef5100bece9b40ac51736aef037704a52a4ca Mon Sep 17 00:00:00 2001
From: liujie <1793218484@qq.com>
Date: 星期四, 30 七月 2026 14:58:52 +0800
Subject: [PATCH] update
---
backend/db/schema.sql | 25 +++++++++++++++++++++++++
1 files changed, 25 insertions(+), 0 deletions(-)
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 43b45e3..bbc40a4 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -144,6 +144,7 @@
`age_range` VARCHAR(20) DEFAULT NULL COMMENT '年龄区间',
`work_year_range` VARCHAR(20) DEFAULT NULL COMMENT '工作年限区间',
`guide_type` VARCHAR(20) NOT NULL DEFAULT 'personal' COMMENT '导游类型：personal个人，team讲解队',
+ `team_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '讲解队ID，为空表示个人导游',
`id_card_no` VARCHAR(32) DEFAULT NULL COMMENT '身份证号',
`certificate_no` VARCHAR(64) DEFAULT NULL COMMENT '导游资格证编号',
`id_card_front_url` VARCHAR(500) DEFAULT NULL COMMENT '身份证正面图片地址',
@@ -164,6 +165,7 @@
PRIMARY KEY (`id`),
UNIQUE KEY `uk_guide_application_no` (`application_no`),
KEY `idx_guide_application_mobile` (`mobile`),
+ KEY `idx_guide_application_team` (`team_id`),
KEY `idx_guide_application_id_card` (`id_card_no`),
KEY `idx_guide_application_bank_city` (`bank_city_id`),
KEY `idx_guide_application_status_created` (`audit_status`, `created_at`)
@@ -315,6 +317,10 @@
`guide_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '接单导游ID',
`guide_name` VARCHAR(64) DEFAULT NULL COMMENT '导游姓名快照',
`guide_mobile` VARCHAR(20) DEFAULT NULL COMMENT '导游手机号快照',
+ `commission_source` VARCHAR(16) DEFAULT NULL COMMENT '抽成来源：PERSONAL个人平台配置、TEAM讲解队配置',
+ `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 '导游实际业绩金额快照',
`accepted_at` DATETIME DEFAULT NULL COMMENT '导游接单时间',
`arrived_at` DATETIME DEFAULT NULL COMMENT '导游到达汇合点时间',
`service_started_at` DATETIME DEFAULT NULL COMMENT '讲解开始时间',
@@ -331,6 +337,7 @@
UNIQUE KEY `uk_tour_order_no` (`order_no`),
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_scenic` (`scenic_name`),
KEY `idx_tour_order_status` (`status`),
KEY `idx_tour_order_created_at` (`created_at`),
@@ -591,6 +598,24 @@
CONSTRAINT `fk_guide_notice_read_notice` FOREIGN KEY (`notice_id`) REFERENCES `sys_notice` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Guide notification read records';
+CREATE TABLE IF NOT EXISTS `guide_notification` (
+ `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
+ `guide_id` BIGINT UNSIGNED NOT NULL COMMENT 'Guide user id',
+ `notification_type` VARCHAR(32) NOT NULL COMMENT 'Notification type',
+ `title` VARCHAR(100) NOT NULL COMMENT 'Notification title',
+ `content` VARCHAR(500) NOT NULL COMMENT 'Notification content',
+ `order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'Related order id',
+ `target_url` VARCHAR(255) DEFAULT NULL COMMENT 'Guide app target page',
+ `read_at` DATETIME DEFAULT NULL COMMENT 'Read time',
+ `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created time',
+ PRIMARY KEY (`id`),
+ KEY `idx_guide_notification_guide_time` (`guide_id`, `created_at`),
+ KEY `idx_guide_notification_guide_read` (`guide_id`, `read_at`),
+ KEY `idx_guide_notification_order` (`order_id`),
+ CONSTRAINT `fk_guide_notification_guide` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE CASCADE,
+ CONSTRAINT `fk_guide_notification_order` FOREIGN KEY (`order_id`) REFERENCES `tour_order` (`id`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Guide personal notifications';
+
CREATE TABLE IF NOT EXISTS `sys_login_log` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登录日志主键',
`username` VARCHAR(64) DEFAULT NULL COMMENT '登录用户名',
--
Gitblit v1.7.1