-- 目的：初始化 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',
    `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`),
    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` (
    `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是',
    `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启用',
    `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_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 '导游申请主键',
    `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 '导游实际业绩金额快照',
    `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 '讲解开始时间',
    `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、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 '退款方式',
    `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`),
    UNIQUE KEY `uk_tour_order_payment_order_no` (`payment_order_no`),
    UNIQUE KEY `uk_tour_order_refund_order_no` (`refund_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_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`),
    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_payment_attempt` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Payment attempt primary key',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Tour order primary key',
    `req_seq_id` VARCHAR(64) NOT NULL COMMENT 'Huifu request sequence',
    `req_date` CHAR(8) NOT NULL COMMENT 'Huifu request date yyyyMMdd',
    `amount` DECIMAL(10,2) NOT NULL COMMENT 'Payment attempt amount',
    `status` VARCHAR(16) NOT NULL DEFAULT 'CREATED' COMMENT 'CREATED or SUCCEEDED',
    `provider_transaction_id` VARCHAR(64) DEFAULT NULL COMMENT 'Huifu transaction id',
    `paid_at` DATETIME DEFAULT NULL COMMENT 'Payment success time',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created time',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_payment_attempt_req_seq` (`req_seq_id`),
    UNIQUE KEY `uk_order_payment_attempt_provider_tx` (`provider_transaction_id`),
    KEY `idx_order_payment_attempt_order` (`order_id`, `status`),
    CONSTRAINT `fk_order_payment_attempt_order` FOREIGN KEY (`order_id`) REFERENCES `tour_order` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tour order payment attempts';

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 '申请导游主键',
    `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 '提现金额',
    `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、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 '申请时间',
    `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_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`),
    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删除';

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删除';
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当前完整数据库结构基线');