-- 目的：初始化 TourGuide 本地开发数据库及三端独立用户表。
|
-- 操作：创建 tour_guide 数据库、后台角色表、后台用户表、小程序用户表和导游用户表。
|
-- 影响范围：仅影响本地 tour_guide 数据库；重复执行不会删除已有业务数据。
|
-- 验证：执行后运行 SHOW TABLES，确认存在 sys_role、sys_admin_user、mini_user、guide_user。
|
|
CREATE DATABASE IF NOT EXISTS `tour_guide`
|
DEFAULT CHARACTER SET utf8mb4
|
COLLATE utf8mb4_unicode_ci;
|
|
USE `tour_guide`;
|
|
CREATE TABLE IF NOT EXISTS `sys_schema_migration` (
|
`version` VARCHAR(64) NOT NULL COMMENT '迁移版本，使用日期时间和功能名称标识',
|
`description` VARCHAR(255) NOT NULL COMMENT '迁移说明',
|
`applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '首次执行时间',
|
PRIMARY KEY (`version`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='数据库增量脚本执行记录';
|
|
CREATE TABLE IF NOT EXISTS `sys_role` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色主键',
|
`role_code` VARCHAR(32) NOT NULL COMMENT '角色编码，作为后台权限判断的稳定标识',
|
`role_name` VARCHAR(64) NOT NULL COMMENT '角色中文名称',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0停用，1启用',
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '角色说明',
|
`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_sys_role_code` (`role_code`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台角色表';
|
|
CREATE TABLE IF NOT EXISTS `sys_admin_user` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '后台用户主键',
|
`username` VARCHAR(32) NOT NULL COMMENT '后台登录用户名',
|
`password` VARCHAR(100) NOT NULL COMMENT 'BCrypt 加密后的登录密码',
|
`nickname` VARCHAR(64) NOT NULL COMMENT '后台显示昵称',
|
`mobile` VARCHAR(20) DEFAULT NULL COMMENT '联系电话',
|
`avatar` VARCHAR(500) DEFAULT NULL COMMENT '头像地址',
|
`role_code` VARCHAR(32) NOT NULL COMMENT '后台角色编码，对应 sys_role.role_code',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账号状态：0停用，1启用，2申请审核中',
|
`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_admin_username` (`username`),
|
KEY `idx_admin_role_code` (`role_code`),
|
KEY `idx_admin_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理后台用户表';
|
|
CREATE TABLE IF NOT EXISTS `mini_user` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '小程序用户主键',
|
`username` VARCHAR(32) NOT NULL COMMENT '小程序登录用户名，后续可替换为微信授权登录',
|
`password` VARCHAR(100) NOT NULL COMMENT 'BCrypt 加密后的登录密码',
|
`nickname` VARCHAR(64) NOT NULL COMMENT '游客昵称',
|
`mobile` VARCHAR(20) DEFAULT NULL COMMENT '游客手机号',
|
`avatar` VARCHAR(500) DEFAULT NULL COMMENT '游客头像地址',
|
`real_name` VARCHAR(64) DEFAULT NULL COMMENT '游客实名认证姓名',
|
`id_card_no` VARCHAR(18) DEFAULT NULL COMMENT '游客实名认证身份证号',
|
`openid` VARCHAR(64) DEFAULT NULL COMMENT '微信 OpenID，接入微信登录后使用',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账号状态：0停用，1启用，2入驻待审核，3已注销',
|
`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_mini_username` (`username`),
|
UNIQUE KEY `uk_mini_openid` (`openid`),
|
UNIQUE KEY `uk_mini_id_card_no` (`id_card_no`),
|
KEY `idx_mini_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游客小程序用户表';
|
|
CREATE TABLE IF NOT EXISTS `guide_team` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '讲解队主键',
|
`team_name` VARCHAR(100) NOT NULL COMMENT '讲解队名称',
|
`contact_phone` VARCHAR(20) NOT NULL COMMENT '联系人电话',
|
`operating_area` VARCHAR(500) NOT NULL COMMENT '运营景区，多个景区使用中文逗号分隔',
|
`company_name` VARCHAR(200) DEFAULT NULL COMMENT '企业名称',
|
`unified_social_credit_code` VARCHAR(32) DEFAULT NULL COMMENT '统一社会信用代码',
|
`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',
|
`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`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游讲解队表';
|
|
CREATE TABLE IF NOT EXISTS `guide_team_scenic_config` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '讲解队景区配置主键',
|
`team_id` BIGINT UNSIGNED NOT NULL COMMENT '讲解队ID',
|
`scenic_name` VARCHAR(128) NOT NULL COMMENT '景区名称',
|
`city_name` VARCHAR(64) NOT NULL COMMENT '城市名称',
|
`meeting_points` VARCHAR(500) NOT NULL COMMENT '汇合点，多个汇合点使用中文逗号分隔',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_guide_team_scenic` (`team_id`, `scenic_name`),
|
KEY `idx_guide_team_scenic_team` (`team_id`),
|
CONSTRAINT `fk_guide_team_scenic_team` FOREIGN KEY (`team_id`) REFERENCES `guide_team` (`id`) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='讲解队景区配置表';
|
|
CREATE TABLE IF NOT EXISTS `guide_user` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '导游用户主键',
|
`username` VARCHAR(32) NOT NULL COMMENT '导游端登录用户名',
|
`password` VARCHAR(100) NOT NULL COMMENT 'BCrypt 加密后的登录密码',
|
`real_name` VARCHAR(64) NOT NULL COMMENT '导游真实姓名',
|
`mobile` VARCHAR(20) DEFAULT NULL COMMENT '导游联系电话',
|
`avatar` VARCHAR(500) DEFAULT NULL COMMENT '导游头像地址',
|
`certificate_no` VARCHAR(64) DEFAULT NULL COMMENT '导游资格证编号',
|
`gender` TINYINT DEFAULT NULL COMMENT '性别：1男，2女',
|
`birth_date` DATE DEFAULT NULL COMMENT '出生日期，用于计算年龄',
|
`work_years` INT UNSIGNED DEFAULT NULL COMMENT '导游工作年限',
|
`id_card_no` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
|
`id_card_front_url` VARCHAR(500) DEFAULT NULL COMMENT '身份证正面图片地址',
|
`id_card_back_url` VARCHAR(500) DEFAULT NULL COMMENT '身份证反面图片地址',
|
`guide_certificate_url` VARCHAR(500) DEFAULT NULL COMMENT '导游证图片地址',
|
`qualification_certificate_url` VARCHAR(500) DEFAULT NULL COMMENT '导游资质证书图片地址',
|
`team_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '所属讲解队，为空表示个人导游',
|
`accepted_order_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计接单数',
|
`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是',
|
`wechat_open_id` VARCHAR(128) DEFAULT NULL COMMENT '导游收款微信 openID',
|
`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 '创建时间',
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_guide_username` (`username`),
|
UNIQUE KEY `uk_guide_certificate_no` (`certificate_no`),
|
UNIQUE KEY `uk_guide_id_card_no` (`id_card_no`),
|
KEY `idx_guide_status` (`status`),
|
KEY `idx_guide_team_id` (`team_id`),
|
KEY `idx_guide_whitelist` (`auto_disable_whitelisted`),
|
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='导游端用户表';
|
|
CREATE TABLE IF NOT EXISTS `guide_application` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '导游申请主键',
|
`application_no` VARCHAR(32) NOT NULL COMMENT '申请编号',
|
`real_name` VARCHAR(64) NOT NULL COMMENT '导游真实姓名',
|
`mobile` VARCHAR(20) NOT NULL COMMENT '申请人手机号',
|
`gender` TINYINT DEFAULT NULL COMMENT '性别：1男，2女',
|
`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 '身份证正面图片地址',
|
`id_card_back_url` VARCHAR(500) DEFAULT NULL COMMENT '身份证反面图片地址',
|
`certificate_url` VARCHAR(500) DEFAULT NULL COMMENT '导游资格证图片地址',
|
`qualification_certificate_url` VARCHAR(500) DEFAULT NULL COMMENT '导游资格证书图片地址',
|
`certificate_valid_until` DATE DEFAULT NULL COMMENT '导游证有效截止日期',
|
`bank_card_no` VARCHAR(32) DEFAULT NULL COMMENT '收款银行卡号',
|
`bank_city_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '开户城市ID',
|
`bank_city_name` VARCHAR(64) DEFAULT NULL COMMENT '开户城市名称快照',
|
`audit_status` TINYINT NOT NULL DEFAULT 0 COMMENT '审核状态：0待审核，1已通过，2已驳回',
|
`audit_remark` VARCHAR(500) DEFAULT NULL COMMENT '审核备注',
|
`auditor` VARCHAR(64) DEFAULT NULL COMMENT '审核管理员用户名',
|
`audited_at` DATETIME DEFAULT NULL COMMENT '审核时间',
|
`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_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`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游入驻申请表';
|
|
CREATE TABLE IF NOT EXISTS `guide_scenic_config` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '导游景区配置主键',
|
`guide_id` BIGINT UNSIGNED NOT NULL COMMENT '导游用户ID',
|
`scenic_name` VARCHAR(128) NOT NULL COMMENT '景区名称',
|
`city_name` VARCHAR(64) DEFAULT NULL COMMENT '城市名称',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_guide_scenic` (`guide_id`, `scenic_name`),
|
KEY `idx_guide_scenic_guide` (`guide_id`),
|
CONSTRAINT `fk_guide_scenic_user` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='个人导游景区配置表';
|
|
-- 新增时间：2026-07-28 09:49:04（Asia/Shanghai）
|
CREATE TABLE IF NOT EXISTS `operation_city` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '城市主键',
|
`city_name` VARCHAR(64) NOT NULL 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 '创建时间',
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_operation_city_name` (`city_name`),
|
KEY `idx_operation_city_status` (`status`),
|
KEY `idx_operation_city_created_at` (`created_at`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='运营城市表';
|
|
CREATE TABLE IF NOT EXISTS `operation_scenic_spot` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '景区主键',
|
`scenic_code` VARCHAR(32) NOT NULL COMMENT '景区业务编号',
|
`scenic_name` VARCHAR(128) NOT NULL COMMENT '景区名称',
|
`city_id` BIGINT UNSIGNED NOT NULL COMMENT '所属运营城市ID',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用，1启用',
|
`first_order_lead_minutes` INT UNSIGNED NOT NULL DEFAULT 15 COMMENT '第一单开启讲解提前时间，单位分钟',
|
`visitor_no_order_minutes` INT UNSIGNED NOT NULL DEFAULT 15 COMMENT '游客无法下单时间，单位分钟',
|
`unpaid_cancel_minutes` INT UNSIGNED NOT NULL DEFAULT 15 COMMENT '未支付自动取消时间，单位分钟',
|
`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_operation_scenic_code` (`scenic_code`),
|
UNIQUE KEY `uk_scenic_city_name` (`city_id`, `scenic_name`),
|
KEY `idx_scenic_status` (`status`),
|
KEY `idx_scenic_created_at` (`created_at`),
|
CONSTRAINT `fk_scenic_city` FOREIGN KEY (`city_id`) REFERENCES `operation_city` (`id`) ON DELETE RESTRICT
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='运营景区表';
|
|
CREATE TABLE IF NOT EXISTS `operation_scenic_pricing` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '价格配置主键',
|
`scenic_spot_id` BIGINT UNSIGNED NOT NULL COMMENT '景区ID',
|
`service_type` VARCHAR(32) NOT NULL COMMENT '服务类型：GROUP_QUICK、GROUP_PREMIUM、PRIVATE_QUICK、PRIVATE_PREMIUM',
|
`duration_minutes` INT UNSIGNED NOT NULL COMMENT '讲解时间，单位分钟',
|
`min_participants` INT UNSIGNED NOT NULL COMMENT '最少讲解人数',
|
`max_participants` INT UNSIGNED DEFAULT NULL COMMENT '最多讲解人数，包团为空',
|
`original_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '原价',
|
`discount_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '优惠价',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_scenic_pricing_type` (`scenic_spot_id`, `service_type`),
|
CONSTRAINT `fk_scenic_pricing_spot` FOREIGN KEY (`scenic_spot_id`) REFERENCES `operation_scenic_spot` (`id`) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='景区讲解价格配置表';
|
|
CREATE TABLE IF NOT EXISTS `operation_scenic_meeting_point` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '汇合点主键',
|
`scenic_spot_id` BIGINT UNSIGNED NOT NULL COMMENT '景区ID',
|
`point_name` VARCHAR(128) NOT NULL COMMENT '汇合点名称',
|
`longitude` DECIMAL(10,6) NOT NULL COMMENT '经度',
|
`latitude` DECIMAL(10,6) NOT NULL COMMENT '纬度',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用，1启用',
|
`is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认汇合点：0否，1是',
|
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '显示顺序',
|
`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_scenic_meeting_point_name` (`scenic_spot_id`, `point_name`),
|
KEY `idx_scenic_meeting_point_sort` (`scenic_spot_id`, `sort_order`),
|
CONSTRAINT `fk_scenic_meeting_point_spot` FOREIGN KEY (`scenic_spot_id`) REFERENCES `operation_scenic_spot` (`id`) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='景区汇合点配置表';
|
|
CREATE TABLE IF NOT EXISTS `guide_realtime_status` (
|
`guide_id` BIGINT UNSIGNED NOT NULL COMMENT '导游用户ID',
|
`scenic_spot_id` BIGINT UNSIGNED NOT NULL COMMENT '当前所在景区ID',
|
`meeting_point_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '当前等待接单的汇合点ID',
|
`longitude` DECIMAL(10,6) DEFAULT NULL COMMENT '导游当前位置经度',
|
`latitude` DECIMAL(10,6) DEFAULT NULL COMMENT '导游当前位置纬度',
|
`online_status` TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态：0离线，1在线',
|
`available_status` TINYINT NOT NULL DEFAULT 0 COMMENT '接单状态：0不可接单，1可接单',
|
`last_location_at` DATETIME DEFAULT NULL COMMENT '最后一次定位上报时间',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`guide_id`),
|
KEY `idx_guide_realtime_scenic_available` (`scenic_spot_id`, `online_status`, `available_status`),
|
KEY `idx_guide_realtime_meeting_point` (`meeting_point_id`),
|
KEY `idx_guide_realtime_location_time` (`last_location_at`),
|
CONSTRAINT `fk_guide_realtime_guide` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE CASCADE,
|
CONSTRAINT `fk_guide_realtime_scenic` FOREIGN KEY (`scenic_spot_id`) REFERENCES `operation_scenic_spot` (`id`) ON DELETE CASCADE,
|
CONSTRAINT `fk_guide_realtime_meeting_point` FOREIGN KEY (`meeting_point_id`) REFERENCES `operation_scenic_meeting_point` (`id`) ON DELETE SET NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游实时接单状态表';
|
|
CREATE TABLE IF NOT EXISTS `agreement_setting` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '协议主键',
|
`agreement_code` VARCHAR(64) NOT NULL COMMENT '协议唯一编码',
|
`title` VARCHAR(100) NOT NULL COMMENT '协议显示名称',
|
`content` LONGTEXT NOT NULL COMMENT '协议富文本内容',
|
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '显示顺序',
|
`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_agreement_setting_code` (`agreement_code`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='平台协议设置表';
|
|
INSERT INTO `agreement_setting` (`agreement_code`, `title`, `content`, `sort_order`)
|
VALUES ('VISITOR_USER_AGREEMENT', '用户协议-游客', '', 1),
|
('GUIDE_USER_AGREEMENT', '用户协议-导游', '', 2),
|
('VISITOR_PRIVACY_PERMISSION', '隐私与权限-游客', '', 3),
|
('EXPLANATION_SERVICE_NOTICE', '纯讲解服务须知协议', '', 4),
|
('EXPLANATION_SERVICE_COOPERATION', '讲解服务合作协议', '', 5),
|
('GUIDE_REWARD_PUNISHMENT', '导游奖惩机制', '', 6)
|
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`), `sort_order` = VALUES(`sort_order`);
|
|
CREATE TABLE IF NOT EXISTS `tour_order` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单主键',
|
`order_no` VARCHAR(32) NOT NULL COMMENT '业务订单编号',
|
`visitor_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '游客用户ID',
|
`visitor_nickname` VARCHAR(64) DEFAULT NULL COMMENT '下单游客昵称快照',
|
`visitor_mobile` VARCHAR(20) DEFAULT NULL COMMENT '下单游客手机号快照',
|
`scenic_name` VARCHAR(128) NOT NULL COMMENT '景区名称快照',
|
`meeting_point_name` VARCHAR(128) DEFAULT NULL COMMENT '汇合点名称快照',
|
`package_name` VARCHAR(128) DEFAULT NULL COMMENT '套餐名称快照',
|
`family_no` VARCHAR(32) DEFAULT NULL COMMENT '家庭号或同行组编号',
|
`order_type` VARCHAR(32) NOT NULL COMMENT '订单类型：GROUP_QUICK拼团快讲、GROUP_PREMIUM拼团精讲、PRIVATE_QUICK包团快讲、PRIVATE_PREMIUM包团精讲',
|
`people_count` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '出行人数',
|
`unit_price` DECIMAL(10,2) NOT NULL COMMENT '订单单价',
|
`original_price` DECIMAL(10,2) DEFAULT NULL COMMENT '商品原价',
|
`discount_price` DECIMAL(10,2) DEFAULT NULL COMMENT '商品优惠价',
|
`subtotal_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '商品小计金额',
|
`discount_type` VARCHAR(64) DEFAULT NULL COMMENT '优惠方式',
|
`discount_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '优惠金额',
|
`amount` DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
|
`payment_method` VARCHAR(32) DEFAULT NULL COMMENT '支付方式：WECHAT、ALIPAY',
|
`payment_order_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方支付流水号',
|
`payment_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '实际支付金额',
|
`payment_time` DATETIME DEFAULT NULL COMMENT '支付时间',
|
`placed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
|
`meeting_point_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '讲解路线汇合点数量',
|
`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 '讲解开始时间',
|
`completed_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_time` DATETIME DEFAULT NULL COMMENT '退款时间',
|
`refund_order_no` VARCHAR(64) DEFAULT NULL COMMENT '退款流水号',
|
`refund_method` VARCHAR(32) DEFAULT NULL COMMENT '退款方式',
|
`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_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`),
|
CONSTRAINT `fk_tour_order_visitor` FOREIGN KEY (`visitor_id`) REFERENCES `mini_user` (`id`) ON DELETE SET NULL,
|
CONSTRAINT `fk_tour_order_guide` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE SET NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游客讲解服务订单表';
|
|
CREATE TABLE IF NOT EXISTS `tour_order_review` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评价主键',
|
`review_no` VARCHAR(32) NOT NULL COMMENT '评价业务编号',
|
`order_id` BIGINT UNSIGNED NOT NULL COMMENT '关联订单主键',
|
`order_no` VARCHAR(32) NOT NULL COMMENT '订单编号快照',
|
`order_placed_at` DATETIME DEFAULT NULL COMMENT '订单下单时间快照',
|
`visitor_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '评价游客ID',
|
`visitor_nickname` VARCHAR(64) DEFAULT NULL COMMENT '游客昵称快照',
|
`visitor_mobile` VARCHAR(20) DEFAULT NULL COMMENT '游客手机号快照',
|
`guide_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '被评价导游ID',
|
`guide_name` VARCHAR(64) DEFAULT NULL COMMENT '导游姓名快照',
|
`guide_mobile` VARCHAR(20) DEFAULT NULL COMMENT '导游手机号快照',
|
`scenic_name` VARCHAR(128) DEFAULT NULL COMMENT '订单景区名称快照',
|
`score` DECIMAL(2,1) NOT NULL COMMENT '评价分数，范围1到5',
|
`content` VARCHAR(1000) DEFAULT NULL COMMENT '评价内容',
|
`reviewed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评价时间',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '评价状态：0隐藏，1显示',
|
`deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除',
|
`deleted_at` DATETIME DEFAULT NULL COMMENT '后台删除时间',
|
`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_order_review_no` (`review_no`),
|
UNIQUE KEY `uk_order_review_order` (`order_id`),
|
KEY `idx_order_review_visitor` (`visitor_id`),
|
KEY `idx_order_review_guide` (`guide_id`),
|
KEY `idx_order_review_scenic` (`scenic_name`),
|
KEY `idx_order_review_score` (`score`),
|
KEY `idx_order_review_status` (`status`),
|
KEY `idx_order_review_time` (`reviewed_at`),
|
CONSTRAINT `fk_order_review_order` FOREIGN KEY (`order_id`) REFERENCES `tour_order` (`id`) ON DELETE RESTRICT,
|
CONSTRAINT `fk_order_review_visitor` FOREIGN KEY (`visitor_id`) REFERENCES `mini_user` (`id`) ON DELETE SET NULL,
|
CONSTRAINT `fk_order_review_guide` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE SET NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单评价表';
|
|
CREATE TABLE IF NOT EXISTS `guide_withdrawal` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '提现申请主键',
|
`withdrawal_no` VARCHAR(32) NOT NULL COMMENT '提现业务编号',
|
`guide_id` BIGINT UNSIGNED NOT NULL COMMENT '申请导游主键',
|
`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',
|
`audit_remark` VARCHAR(255) DEFAULT NULL COMMENT '处理备注',
|
`processor` VARCHAR(64) DEFAULT NULL COMMENT '后台处理人',
|
`applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
`processed_at` DATETIME DEFAULT NULL COMMENT '处理时间',
|
`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_withdrawal_no` (`withdrawal_no`),
|
KEY `idx_guide_withdrawal_guide` (`guide_id`),
|
KEY `idx_guide_withdrawal_status` (`status`),
|
KEY `idx_guide_withdrawal_applied_at` (`applied_at`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游提现申请表';
|
|
INSERT IGNORE INTO `sys_schema_migration` (`version`, `description`)
|
SELECT '20260728_100213_REVIEW_MANAGEMENT', '新增订单评价表及订单、游客、导游关联'
|
WHERE EXISTS (
|
SELECT 1 FROM `information_schema`.`tables`
|
WHERE `table_schema` = DATABASE() AND `table_name` = 'tour_order_review'
|
);
|
|
CREATE TABLE IF NOT EXISTS `user_feedback` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '反馈主键',
|
`feedback_no` VARCHAR(32) NOT NULL COMMENT '反馈业务编号',
|
`feedback_type` VARCHAR(16) NOT NULL COMMENT '反馈来源：VISITOR游客，GUIDE导游',
|
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '反馈用户主键',
|
`user_name` VARCHAR(64) DEFAULT NULL COMMENT '反馈用户名称快照',
|
`user_mobile` VARCHAR(20) DEFAULT NULL COMMENT '反馈用户手机号快照',
|
`content` VARCHAR(2000) NOT NULL COMMENT '反馈内容',
|
`feedback_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '反馈时间',
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '处理状态：0未处理，1已处理',
|
`process_result` VARCHAR(1000) DEFAULT NULL COMMENT '处理结果',
|
`processed_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '后台处理人主键',
|
`processed_by_name` VARCHAR(64) DEFAULT NULL COMMENT '后台处理人账号快照',
|
`processed_at` DATETIME DEFAULT NULL COMMENT '处理时间',
|
`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_user_feedback_no` (`feedback_no`),
|
KEY `idx_user_feedback_type_status` (`feedback_type`, `status`),
|
KEY `idx_user_feedback_mobile` (`user_mobile`),
|
KEY `idx_user_feedback_time` (`feedback_at`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游客和导游反馈表';
|
|
INSERT IGNORE INTO `sys_schema_migration` (`version`, `description`)
|
SELECT '20260728_120000_FEEDBACK_MANAGEMENT', '新增游客和导游反馈管理表'
|
WHERE EXISTS (
|
SELECT 1 FROM `information_schema`.`tables`
|
WHERE `table_schema` = DATABASE() AND `table_name` = 'user_feedback'
|
);
|
|
CREATE TABLE IF NOT EXISTS `guide_auto_disable_config` (
|
`id` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '固定配置主键',
|
`order_threshold` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '团单数触发阈值',
|
`rating_threshold` DECIMAL(3,2) NOT NULL DEFAULT 3.00 COMMENT '平均评分触发阈值',
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导游自动禁用规则配置表';
|
|
INSERT INTO `guide_auto_disable_config` (`id`, `order_threshold`, `rating_threshold`)
|
VALUES (1, 1, 3.00)
|
ON DUPLICATE KEY UPDATE `id` = VALUES(`id`);
|
|
-- 内置后台管理员角色；管理员用户由后端首次启动时使用 BCrypt 动态创建。
|
INSERT INTO `sys_role` (`role_code`, `role_name`, `status`, `remark`)
|
VALUES ('ADMIN', '系统管理员', 1, '系统内置后台管理员角色')
|
ON DUPLICATE KEY UPDATE `role_name` = VALUES(`role_name`), `status` = VALUES(`status`), `remark` = VALUES(`remark`);
|
|
CREATE TABLE IF NOT EXISTS `sys_dept` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门主键',
|
`parent_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父部门主键，0表示顶级部门',
|
`ancestors` VARCHAR(500) NOT NULL DEFAULT '0' COMMENT '祖级部门路径，使用英文逗号分隔',
|
`dept_name` VARCHAR(64) NOT NULL COMMENT '部门名称',
|
`sort` INT NOT NULL DEFAULT 0 COMMENT '显示顺序，数值越小越靠前',
|
`leader` VARCHAR(64) DEFAULT NULL COMMENT '部门负责人',
|
`phone` VARCHAR(20) DEFAULT NULL COMMENT '联系电话',
|
`email` VARCHAR(100) DEFAULT NULL 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 '创建时间',
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`id`),
|
KEY `idx_dept_parent_id` (`parent_id`),
|
KEY `idx_dept_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台部门表';
|
|
CREATE TABLE IF NOT EXISTS `sys_post` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '岗位主键',
|
`post_code` VARCHAR(32) NOT NULL COMMENT '岗位编码',
|
`post_name` VARCHAR(64) NOT NULL COMMENT '岗位名称',
|
`sort` INT NOT NULL DEFAULT 0 COMMENT '显示顺序，数值越小越靠前',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0停用，1启用',
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '岗位说明',
|
`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_post_code` (`post_code`),
|
UNIQUE KEY `uk_post_name` (`post_name`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台岗位表';
|
|
CREATE TABLE IF NOT EXISTS `sys_menu` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '菜单主键',
|
`parent_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父菜单主键，0表示顶级菜单',
|
`menu_code` VARCHAR(64) NOT NULL COMMENT '菜单唯一编码',
|
`menu_name` VARCHAR(64) NOT NULL COMMENT '菜单名称',
|
`menu_type` VARCHAR(16) NOT NULL COMMENT '菜单类型：DIRECTORY目录，MENU菜单，BUTTON按钮',
|
`path` VARCHAR(200) DEFAULT NULL COMMENT '前端路由地址',
|
`component` VARCHAR(255) DEFAULT NULL COMMENT '前端组件路径',
|
`permission` VARCHAR(100) DEFAULT NULL COMMENT '后端权限标识',
|
`icon` VARCHAR(100) DEFAULT NULL COMMENT '菜单图标',
|
`visible` TINYINT NOT NULL DEFAULT 1 COMMENT '是否显示：0隐藏，1显示',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0停用，1启用',
|
`sort` INT NOT NULL DEFAULT 0 COMMENT '显示顺序，数值越小越靠前',
|
`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_menu_code` (`menu_code`),
|
KEY `idx_menu_parent_id` (`parent_id`),
|
KEY `idx_menu_permission` (`permission`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台菜单与权限表';
|
|
CREATE TABLE IF NOT EXISTS `sys_role_menu` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色菜单关系主键',
|
`role_id` BIGINT UNSIGNED NOT NULL COMMENT '角色主键',
|
`menu_id` BIGINT UNSIGNED NOT NULL COMMENT '菜单主键',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_role_menu` (`role_id`, `menu_id`),
|
KEY `idx_role_menu_menu_id` (`menu_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台角色菜单关联表';
|
|
CREATE TABLE IF NOT EXISTS `sys_admin_user_post` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户岗位关系主键',
|
`admin_user_id` BIGINT UNSIGNED NOT NULL COMMENT '后台用户主键',
|
`post_id` BIGINT UNSIGNED NOT NULL COMMENT '岗位主键',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_admin_user_post` (`admin_user_id`, `post_id`),
|
KEY `idx_user_post_post_id` (`post_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台用户岗位关联表';
|
|
CREATE TABLE IF NOT EXISTS `sys_dict_type` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '字典类型主键',
|
`dict_name` VARCHAR(100) NOT NULL COMMENT '字典名称',
|
`dict_type` VARCHAR(100) NOT NULL COMMENT '字典类型编码',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0停用，1启用',
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '字典说明',
|
`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_dict_type` (`dict_type`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台字典类型表';
|
|
CREATE TABLE IF NOT EXISTS `sys_dict_data` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '字典数据主键',
|
`dict_type` VARCHAR(100) NOT NULL COMMENT '字典类型编码，对应 sys_dict_type.dict_type',
|
`dict_label` VARCHAR(100) NOT NULL COMMENT '字典显示标签',
|
`dict_value` VARCHAR(100) NOT NULL COMMENT '字典实际值',
|
`sort` INT NOT NULL DEFAULT 0 COMMENT '显示顺序，数值越小越靠前',
|
`css_class` VARCHAR(100) DEFAULT NULL COMMENT '前端样式类名',
|
`list_class` VARCHAR(100) DEFAULT NULL COMMENT '列表回显样式',
|
`is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认值：0否，1是',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0停用，1启用',
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '字典数据说明',
|
`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_dict_type_value` (`dict_type`, `dict_value`),
|
KEY `idx_dict_data_type` (`dict_type`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台字典数据表';
|
|
CREATE TABLE IF NOT EXISTS `sys_config` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '参数配置主键',
|
`config_name` VARCHAR(100) NOT NULL COMMENT '参数名称',
|
`config_key` VARCHAR(100) NOT NULL COMMENT '参数键名',
|
`config_value` VARCHAR(1000) NOT NULL COMMENT '参数键值',
|
`config_type` TINYINT NOT NULL DEFAULT 0 COMMENT '是否系统内置：0否，1是',
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '参数说明',
|
`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_config_key` (`config_key`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台参数配置表';
|
|
CREATE TABLE IF NOT EXISTS `sys_notice` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '通知公告主键',
|
`notice_title` VARCHAR(150) NOT NULL COMMENT '通知公告标题',
|
`notice_type` VARCHAR(16) NOT NULL COMMENT '类型：NOTICE通知，ANNOUNCEMENT公告',
|
`notice_content` LONGTEXT COMMENT '通知公告内容',
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '发布状态：0草稿，1已发布',
|
`publisher` VARCHAR(64) DEFAULT NULL COMMENT '发布人名称',
|
`publish_time` DATETIME DEFAULT NULL COMMENT '发布时间',
|
`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`),
|
KEY `idx_notice_status` (`status`),
|
KEY `idx_notice_publish_time` (`publish_time`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台通知公告表';
|
|
CREATE TABLE IF NOT EXISTS `guide_notice_read` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
|
`guide_id` BIGINT UNSIGNED NOT NULL COMMENT 'Guide user id',
|
`notice_id` BIGINT UNSIGNED NOT NULL COMMENT 'System notice id',
|
`read_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Read time',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_guide_notice_read` (`guide_id`, `notice_id`),
|
KEY `idx_guide_notice_read_notice` (`notice_id`),
|
CONSTRAINT `fk_guide_notice_read_guide` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE CASCADE,
|
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 '登录用户名',
|
`client_type` VARCHAR(16) NOT NULL DEFAULT 'ADMIN' COMMENT '客户端类型：ADMIN后台，MINI小程序，GUIDE导游端',
|
`ip_address` VARCHAR(64) DEFAULT NULL COMMENT '登录 IP 地址',
|
`login_location` VARCHAR(255) DEFAULT NULL COMMENT '登录地点',
|
`browser` VARCHAR(100) DEFAULT NULL COMMENT '浏览器类型',
|
`operating_system` VARCHAR(100) DEFAULT NULL COMMENT '操作系统',
|
`status` TINYINT NOT NULL COMMENT '登录结果：0失败，1成功',
|
`message` VARCHAR(255) DEFAULT NULL COMMENT '登录结果说明',
|
`login_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间',
|
PRIMARY KEY (`id`),
|
KEY `idx_login_log_username` (`username`),
|
KEY `idx_login_log_time` (`login_time`),
|
KEY `idx_login_log_client_type` (`client_type`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统登录日志表';
|
|
CREATE TABLE IF NOT EXISTS `sys_operation_log` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '操作日志主键',
|
`module` VARCHAR(64) DEFAULT NULL COMMENT '业务模块名称',
|
`business_type` VARCHAR(32) DEFAULT NULL COMMENT '业务类型：QUERY、CREATE、UPDATE、DELETE等',
|
`method` VARCHAR(255) DEFAULT NULL COMMENT '后端方法名称',
|
`request_method` VARCHAR(16) DEFAULT NULL COMMENT 'HTTP 请求方法',
|
`operator_name` VARCHAR(64) DEFAULT NULL COMMENT '操作人用户名',
|
`request_url` VARCHAR(500) DEFAULT NULL COMMENT '请求地址',
|
`ip_address` VARCHAR(64) DEFAULT NULL COMMENT '操作 IP 地址',
|
`request_param` MEDIUMTEXT COMMENT '请求参数，敏感字段写入前必须脱敏',
|
`response_result` MEDIUMTEXT COMMENT '响应结果，敏感字段写入前必须脱敏',
|
`status` TINYINT NOT NULL COMMENT '操作结果：0失败，1成功',
|
`error_message` TEXT COMMENT '失败时的错误信息',
|
`cost_time` BIGINT UNSIGNED DEFAULT NULL COMMENT '接口耗时，单位毫秒',
|
`operation_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`),
|
KEY `idx_operation_operator` (`operator_name`),
|
KEY `idx_operation_time` (`operation_time`),
|
KEY `idx_operation_module` (`module`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台操作日志表';
|
|
-- 内置根部门和管理员岗位，为后续后台组织架构页面提供初始数据。
|
INSERT INTO `sys_dept` (`parent_id`, `ancestors`, `dept_name`, `sort`, `leader`, `status`)
|
SELECT 0, '0', 'TourGuide平台', 0, '系统管理员', 1
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_dept` WHERE `parent_id` = 0 AND `dept_name` = 'TourGuide平台');
|
|
INSERT INTO `sys_post` (`post_code`, `post_name`, `sort`, `status`, `remark`)
|
VALUES ('ADMIN', '系统管理员', 0, 1, '系统内置管理员岗位')
|
ON DUPLICATE KEY UPDATE `post_name` = VALUES(`post_name`), `status` = VALUES(`status`), `remark` = VALUES(`remark`);
|
|
-- 内置后台菜单；menu_code 用于幂等更新，后续接入用户原型时可直接调整路由和组件字段。
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `component`, `permission`, `icon`, `visible`, `status`, `sort`)
|
VALUES (0, 'SYSTEM', '系统管理', 'DIRECTORY', '/system', NULL, NULL, 'setting', 1, 1, 10)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `status` = VALUES(`status`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `component`, `permission`, `icon`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SYSTEM_ADMIN_USER', '后台账号', 'MENU', '/admin-users', 'views/AdminUsersView.vue', 'system:admin-user:list', 'user', 1, 1, 1
|
FROM `sys_menu` WHERE `menu_code` = 'SYSTEM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `component`, `permission`, `icon`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SYSTEM_MINI_USER', '小程序用户', 'MENU', '/mini-users', 'views/MiniUsersView.vue', 'system:mini-user:list', 'user-filled', 1, 1, 2
|
FROM `sys_menu` WHERE `menu_code` = 'SYSTEM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `component`, `permission`, `icon`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SYSTEM_GUIDE_USER', '导游账号', 'MENU', '/guide-users', 'views/GuideUsersView.vue', 'system:guide-user:list', 'guide', 1, 1, 3
|
FROM `sys_menu` WHERE `menu_code` = 'SYSTEM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`);
|
|
INSERT INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `remark`)
|
VALUES ('系统状态', 'sys_common_status', 1, '系统通用启用停用状态')
|
ON DUPLICATE KEY UPDATE `dict_name` = VALUES(`dict_name`), `status` = VALUES(`status`);
|
|
INSERT INTO `sys_dict_data` (`dict_type`, `dict_label`, `dict_value`, `sort`, `list_class`, `is_default`, `status`)
|
VALUES ('sys_common_status', '启用', '1', 1, 'success', 1, 1),
|
('sys_common_status', '停用', '0', 2, 'danger', 0, 1)
|
ON DUPLICATE KEY UPDATE `dict_label` = VALUES(`dict_label`), `sort` = VALUES(`sort`), `status` = VALUES(`status`);
|
|
INSERT INTO `sys_config` (`config_name`, `config_key`, `config_value`, `config_type`, `remark`)
|
VALUES ('后台系统名称', 'system.admin.title', 'TourGuide管理后台', 1, '后台页面显示的系统名称'),
|
('默认分页大小', 'system.page.default-size', '10', 1, '后台列表默认每页记录数')
|
ON DUPLICATE KEY UPDATE `config_name` = VALUES(`config_name`), `config_value` = VALUES(`config_value`), `remark` = VALUES(`remark`);
|
|
INSERT INTO `sys_config` (`config_name`, `config_key`, `config_value`, `config_type`, `remark`)
|
VALUES ('未接单自动退款时间', 'platform.order.unaccepted-auto-refund-minutes', '5', 1, '单位：分钟'),
|
('个人导游提成比例', 'platform.guide.commission-rate', '15', 1, '单位：百分比'),
|
('精准可用最低接单数', 'platform.dispatch.precise-order-count', '10', 1, '达到该接单数后参与精准派单'),
|
('精准可用最低综合评分', 'platform.dispatch.precise-rating', '4.8', 1, '达到该评分后参与精准派单'),
|
('订单结算周期', 'platform.finance.settlement-days', '1', 1, 'T+N天结算'),
|
('客服微信', 'platform.customer-service.wechat', 'TourGuide客服', 1, '平台客服微信号'),
|
('客服电话', 'platform.customer-service.phone', '400-000-0000', 1, '平台客服电话')
|
ON DUPLICATE KEY UPDATE `config_name` = VALUES(`config_name`), `config_value` = VALUES(`config_value`), `remark` = VALUES(`remark`);
|
|
-- 管理员角色默认拥有全部已启用菜单权限。
|
INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
|
SELECT `role`.`id`, `menu`.`id`
|
FROM `sys_role` AS `role`
|
CROSS JOIN `sys_menu` AS `menu`
|
WHERE `role`.`role_code` = 'ADMIN' AND `menu`.`status` = 1;
|
|
-- 后台角色权限目录。
|
UPDATE `sys_menu` SET `status` = 0 WHERE `menu_code` IN ('SYSTEM_ADMIN_USER', 'SYSTEM_MINI_USER', 'SYSTEM_GUIDE_USER');
|
|
DELETE `role_menu`
|
FROM `sys_role_menu` AS `role_menu`
|
INNER JOIN `sys_menu` AS `menu` ON `menu`.`id` = `role_menu`.`menu_id`
|
WHERE `menu`.`menu_code` IN ('SYSTEM_ADMIN_USER', 'SYSTEM_MINI_USER', 'SYSTEM_GUIDE_USER');
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'HOME', '首页', 'DIRECTORY', NULL, NULL, 1, 1, 10)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'DASHBOARD', '首页', 'MENU', '/dashboard', 'dashboard:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'HOME'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'DASHBOARD_VIEW', '查看', 'BUTTON', NULL, 'dashboard:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'DASHBOARD'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
-- 统一逻辑删除字段。技术迁移记录表 sys_schema_migration 不参与业务删除。
|
ALTER TABLE `agreement_setting` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `guide_auto_disable_config` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `guide_realtime_status` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `guide_scenic_config` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
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删除';
|
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删除';
|
ALTER TABLE `sys_config` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_dict_data` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_dict_type` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_login_log` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_menu` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_notice` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_operation_log` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_post` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_role` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `sys_role_menu` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `tour_order` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
ALTER TABLE `user_feedback` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除标记：0正常，1删除';
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'USERS', '用户管理', 'DIRECTORY', NULL, NULL, 1, 1, 20)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'VISITOR_LIST', '游客列表', 'MENU', '/visitors', 'visitor:list:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'USERS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'VISITOR_LIST_VIEW', '查看', 'BUTTON', NULL, 'visitor:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'VISITOR_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'VISITOR_LIST_EDIT', '编辑', 'BUTTON', NULL, 'visitor:list:edit', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'VISITOR_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'VISITOR_LIST_STATUS', '启用/禁用', 'BUTTON', NULL, 'visitor:list:status', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'VISITOR_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'GUIDES', '导游管理', 'DIRECTORY', NULL, NULL, 1, 1, 30)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_LIST', '导游列表', 'MENU', '/guides', 'guide:list:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'GUIDES'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_LIST_VIEW', '查看', 'BUTTON', NULL, 'guide:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_LIST_ADD', '新增', 'BUTTON', NULL, 'guide:list:add', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_LIST_EDIT', '编辑', 'BUTTON', NULL, 'guide:list:edit', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_LIST_STATUS', '启用/禁用', 'BUTTON', NULL, 'guide:list:status', 0, 1, 40 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_AUDIT', '导游审核', 'MENU', '/guide-audits', 'guide:audit:view', 1, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'GUIDES'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_AUDIT_VIEW', '查看', 'BUTTON', NULL, 'guide:audit:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_AUDIT'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_AUDIT_AUDIT', '审核', 'BUTTON', NULL, 'guide:audit:audit', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_AUDIT'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_TEAM', '讲解队管理', 'MENU', '/guide-teams', 'guide:team:view', 1, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'GUIDES'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_TEAM_VIEW', '查看', 'BUTTON', NULL, 'guide:team:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_TEAM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_TEAM_ADD', '新增', 'BUTTON', NULL, 'guide:team:add', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_TEAM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_TEAM_EDIT', '编辑', 'BUTTON', NULL, 'guide:team:edit', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_TEAM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_TEAM_STATUS', '启用/禁用', 'BUTTON', NULL, 'guide:team:status', 0, 1, 40 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_TEAM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'GUIDE_TEAM_DELETE', '删除', 'BUTTON', NULL, 'guide:team:delete', 0, 1, 50 FROM `sys_menu` WHERE `menu_code` = 'GUIDE_TEAM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'ORDERS', '订单管理', 'DIRECTORY', NULL, NULL, 1, 1, 40)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ORDER_LIST', '订单列表', 'MENU', '/orders', 'order:list:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'ORDERS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ORDER_LIST_VIEW', '查看', 'BUTTON', NULL, 'order:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'ORDER_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ORDER_LIST_REFUND', '退款', 'BUTTON', NULL, 'order:list:refund', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'ORDER_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'OPERATIONS', '运营管理', 'DIRECTORY', NULL, NULL, 1, 1, 50)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'CITY_LIST', '城市管理', 'MENU', '/operations/cities', 'city:list:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'OPERATIONS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'CITY_LIST_VIEW', '查看', 'BUTTON', NULL, 'city:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'CITY_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'CITY_LIST_ADD', '新增', 'BUTTON', NULL, 'city:list:add', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'CITY_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'CITY_LIST_STATUS', '启用/禁用', 'BUTTON', NULL, 'city:list:status', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'CITY_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SCENIC_LIST', '景区管理', 'MENU', '/operations/scenic-spots', 'scenic:list:view', 1, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'OPERATIONS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SCENIC_LIST_VIEW', '查看', 'BUTTON', NULL, 'scenic:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'SCENIC_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SCENIC_LIST_ADD', '新增', 'BUTTON', NULL, 'scenic:list:add', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'SCENIC_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SCENIC_LIST_EDIT', '编辑', 'BUTTON', NULL, 'scenic:list:edit', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'SCENIC_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SCENIC_LIST_STATUS', '启用/禁用', 'BUTTON', NULL, 'scenic:list:status', 0, 1, 40 FROM `sys_menu` WHERE `menu_code` = 'SCENIC_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'SCENIC_LIST_DELETE', '删除', 'BUTTON', NULL, 'scenic:list:delete', 0, 1, 50 FROM `sys_menu` WHERE `menu_code` = 'SCENIC_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'REVIEW_LIST', '评价管理', 'MENU', '/operations/reviews', 'review:list:view', 1, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'OPERATIONS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'REVIEW_LIST_VIEW', '查看', 'BUTTON', NULL, 'review:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'REVIEW_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'REVIEW_LIST_DELETE', '删除', 'BUTTON', NULL, 'review:list:delete', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'REVIEW_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FEEDBACK_LIST', '反馈管理', 'MENU', '/operations/feedback', 'feedback:list:view', 1, 1, 40 FROM `sys_menu` WHERE `menu_code` = 'OPERATIONS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FEEDBACK_LIST_VIEW', '查看', 'BUTTON', NULL, 'feedback:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'FEEDBACK_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FEEDBACK_LIST_EDIT', '处理', 'BUTTON', NULL, 'feedback:list:edit', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'FEEDBACK_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'PLATFORM_SETTINGS', '平台设置', 'MENU', '/operations/platform-settings', 'platform:settings:view', 1, 1, 50 FROM `sys_menu` WHERE `menu_code` = 'OPERATIONS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'PLATFORM_SETTINGS_VIEW', '查看', 'BUTTON', NULL, 'platform:settings:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'PLATFORM_SETTINGS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'PLATFORM_SETTINGS_EDIT', '编辑', 'BUTTON', NULL, 'platform:settings:edit', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'PLATFORM_SETTINGS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'AGREEMENT_SETTINGS', '协议设置', 'MENU', '/operations/agreements', 'agreement:settings:view', 1, 1, 60 FROM `sys_menu` WHERE `menu_code` = 'OPERATIONS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'AGREEMENT_SETTINGS_VIEW', '查看', 'BUTTON', NULL, 'agreement:settings:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'AGREEMENT_SETTINGS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'AGREEMENT_SETTINGS_EDIT', '编辑', 'BUTTON', NULL, 'agreement:settings:edit', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'AGREEMENT_SETTINGS'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'FINANCE', '财务管理', 'DIRECTORY', NULL, NULL, 1, 1, 60)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FINANCE_DETAIL', '财务详情', 'MENU', '/finance/details', 'finance:detail:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'FINANCE'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FINANCE_DETAIL_VIEW', '查看', 'BUTTON', NULL, 'finance:detail:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'FINANCE_DETAIL'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FINANCE_REFUND', '退款明细', 'MENU', '/finance/refunds', 'finance:refund:view', 1, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'FINANCE'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'FINANCE_REFUND_VIEW', '查看', 'BUTTON', NULL, 'finance:refund:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'FINANCE_REFUND'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'WITHDRAWAL_LIST', '提现管理', 'MENU', '/finance/withdrawals', 'withdrawal:list:view', 1, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'FINANCE'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'WITHDRAWAL_LIST_VIEW', '查看', 'BUTTON', NULL, 'withdrawal:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'WITHDRAWAL_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'WITHDRAWAL_LIST_AUDIT', '审核', 'BUTTON', NULL, 'withdrawal:list:audit', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'WITHDRAWAL_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
VALUES (0, 'SYSTEM', '系统设置', 'DIRECTORY', NULL, NULL, 1, 1, 70)
|
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `menu_type` = VALUES(`menu_type`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ROLE_LIST', '角色管理', 'MENU', '/system/roles', 'role:list:view', 1, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'SYSTEM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ROLE_LIST_VIEW', '查看', 'BUTTON', NULL, 'role:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'ROLE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ROLE_LIST_ADD', '新增', 'BUTTON', NULL, 'role:list:add', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'ROLE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ROLE_LIST_EDIT', '编辑', 'BUTTON', NULL, 'role:list:edit', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'ROLE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'ROLE_LIST_DELETE', '删除', 'BUTTON', NULL, 'role:list:delete', 0, 1, 40 FROM `sys_menu` WHERE `menu_code` = 'ROLE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST', '员工管理', 'MENU', '/system/employees', 'employee:list:view', 1, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'SYSTEM'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `path` = VALUES(`path`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST_VIEW', '查看', 'BUTTON', NULL, 'employee:list:view', 0, 1, 10 FROM `sys_menu` WHERE `menu_code` = 'EMPLOYEE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST_ADD', '新增', 'BUTTON', NULL, 'employee:list:add', 0, 1, 20 FROM `sys_menu` WHERE `menu_code` = 'EMPLOYEE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST_EDIT', '编辑', 'BUTTON', NULL, 'employee:list:edit', 0, 1, 30 FROM `sys_menu` WHERE `menu_code` = 'EMPLOYEE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST_STATUS', '启用/禁用', 'BUTTON', NULL, 'employee:list:status', 0, 1, 40 FROM `sys_menu` WHERE `menu_code` = 'EMPLOYEE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST_DELETE', '删除', 'BUTTON', NULL, 'employee:list:delete', 0, 1, 50 FROM `sys_menu` WHERE `menu_code` = 'EMPLOYEE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT INTO `sys_menu` (`parent_id`, `menu_code`, `menu_name`, `menu_type`, `path`, `permission`, `visible`, `status`, `sort`)
|
SELECT `id`, 'EMPLOYEE_LIST_RESET_PASSWORD', '重置密码', 'BUTTON', NULL, 'employee:list:reset_password', 0, 1, 60 FROM `sys_menu` WHERE `menu_code` = 'EMPLOYEE_LIST'
|
ON DUPLICATE KEY UPDATE `parent_id` = VALUES(`parent_id`), `menu_name` = VALUES(`menu_name`), `permission` = VALUES(`permission`), `status` = 1, `sort` = VALUES(`sort`);
|
|
INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
|
SELECT `role`.`id`, `menu`.`id`
|
FROM `sys_role` AS `role`
|
CROSS JOIN `sys_menu` AS `menu`
|
WHERE `role`.`role_code` = 'ADMIN' AND `menu`.`status` = 1;
|
|
INSERT IGNORE INTO `sys_schema_migration` (`version`, `description`)
|
VALUES ('20260728_150000_CURRENT_SCHEMA_BASELINE', '登记2026-07-28当前完整数据库结构基线');
|
